Thursday, July 17, 2008

Sales Order Interfaces

SALES ORDER INTERFACES
*****************************

Interface Tables are :-
-----------------------
a) oe_headers_iface_all
b) oe_lines_iface_all

Import Order Program
-----------------------
Order Management
-> Oracle Order Management
-> Orders, Returns
-> Import Orders
-> Order Import Request

To upload the price even if list price existing :-
---------------------------------------------
calculate_price_flag = 'N', then populate unit_selling_price and unit_list_price through line interface table

To re-run error records :-
-----------------------------
Once the error rectified, error_flag and request_id should be null in header as well as line tables.
Then again submit the import concurrent program.

Staging tables
-------------
:- For Sales Order header
-------------------------
create table xxx_so_header_stg
(
legacy_so_num varchar2(15 byte),
customer_name varchar2(150 byte),
customer_no varchar2(10 byte),
bill_to varchar2(5 byte),
ship_to varchar2(5 byte),
transaction_type varchar2(30 byte),
customer_po_no varchar2(100 byte),
order_date date,
price_list varchar2(20 byte),
payment_terms varchar2(50 byte),
warehouse varchar2(5 byte),
verify_flag char(1 byte),
error_message varchar2(3000 byte),
no_lines varchar2(1 byte)
)

:- For Sales Order Lines
------------------------
create table xxx_so_lines_stg
(
legacy_so_num varchar2(15 byte),
line_num number,
item_code varchar2(50 byte),
uom varchar2(10 byte),
line_type varchar2(30 byte),
qty number(10),
price_unit number(10,2),
request_date date,
scheduled_ship_date date,
verify_flag char(1 byte),
error_message varchar2(3000 byte),
no_header varchar2(1 byte)
)


PL/SQL Script to migrate data from legacy to interface
-------------------------------------------------------
CREATE OR REPLACE PROCEDURE xxx_so_api
IS

l_price_list qp_list_headers_tl.list_header_id%type;
l_verify_flag char(1);
l_error_message varchar2(3000);
l_term_id ra_terms_tl.term_id%type;
l_order_type oe_transaction_types_tl.transaction_type_id%type;
l_order_source_id oe_order_sources.order_source_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_organization_id org_organization_definitions.organization_id%type;
l_uom_code mtl_units_of_measure_vl.uom_code%type;
l_line_type_id oe_transaction_types_tl.transaction_type_id%type;
l_currency_code FND_CURRENCIES_VL.currency_code%type;
l_customer_id ra_customers.customer_id%type;
l_invoice_to_orgid oe_invoice_to_orgs_v.organization_id%type;
l_shipto_org_id oe_ship_to_orgs_v.organization_id%type;
l_org_id hr_operating_units.organization_id%type;
l_user_id fnd_user.user_id%type;
l_ship_to_org_id number(10);
l_bill_to_org_id number(10);


cursor c_header
is
select *
from xxx_so_header_stg
where nvl(verify_flag,'N') = 'N' ;

cursor c_lines(p_legacy_so_num varchar2)
is select *
from xxx_so_lines_stg
where legacy_so_num = p_legacy_so_num
and nvl(verify_flag,'N') = 'N';

BEGIN

for h1 in c_header
loop
l_error_message := null;
l_verify_flag := 'Y';

BEGIN
select organization_id
into l_org_id
from hr_operating_units
where name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Operating Unit is Invalid';
END;


BEGIN
select list_header_id
into l_price_list
from qp_list_headers_tl
where upper(name) = upper('xxx SPARES 08-09');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Price List Is not valid';
END;


BEGIN
select term_id
into l_term_id
from ra_terms_tl
where upper(name) = upper(trim(h1.payment_terms));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Payment Term ID is not Valid';
END;


BEGIN /* Order Type */
select transaction_type_id
into l_order_type
from oe_transaction_types_tl
where upper(name) = trim(upper(h1.transaction_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Order Transaction Type is not Valid';
END;


BEGIN
select order_source_id
into l_order_source_id
from OE_ORDER_SOURCES
where NAME = 'Online' ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Order Source is not Valid';
END;


BEGIN
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = trim(h1.warehouse);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Ware House is not Existing';
END;


BEGIN
select customer_id
into l_customer_id --Sold To OrgID
from ra_customers
where upper(customer_name) = trim(upper(h1.customer_name))
and status = 'A';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Customer Name not Valid';
END;


BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = 'KPMG' ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'User Name is Invalid';
END;

begin
select ship_su.site_use_id
into l_ship_to_org_id
from hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps
where ship_su.org_id = l_org_id
and ship_su.org_id = ship_cas.org_id
and ship_su.location = trim(h1.ship_to)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_ps.party_site_id in (select party_site_id
from hz_party_sites
where party_id = (select party_id
from ra_customers
where customer_id = l_customer_id
and status = 'A'));
exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message||'Invalid Ship To';
end;

begin
select ship_su.site_use_id
into l_bill_to_org_id
from hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
hz_party_sites ship_ps
where ship_su.org_id = l_org_id
and ship_su.org_id = ship_cas.org_id
and ship_su.location = trim(h1.bill_to)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_ps.party_site_id in (select party_site_id
from hz_party_sites
where party_id = (select party_id
from ra_customers
where customer_id = l_customer_id
and status = 'A'));
exception
when others then
l_verify_flag := 'N';
l_error_message := l_error_message||'Invalid Bill To';
end;

if l_verify_flag <> 'N' then

begin

savepoint a;

insert into oe_headers_iface_all
(
order_source_id
,orig_sys_document_ref
,org_id
,sold_from_org_id
,ship_from_org_id
,ordered_date
,order_type_id
,sold_to_org_id
,payment_term_id
,operation_code
,created_by
,creation_date
,last_updated_by
,last_update_date
,customer_po_number
,price_list_id
,context
,attribute19
,ship_to_org_id
,invoice_to_org_id
)
values
(
l_order_source_id --order_source_id
,oe_order_headers_s.nextval --orig_sys_document_ref
,l_org_id --org_id
,l_org_id --sold_from_org_id
,l_organization_id --ship_from_org_id
,trim(h1.order_date) --ordered_date
,l_order_type --order_type_id
,l_customer_id --sold_to_org_id
,l_term_id --payment_term_id
,'CREATE' --operation_code
,l_user_id --created_by
,sysdate --creation_date
,l_user_id --last_updated_by
,sysdate --last_update_date
,null --customer_po_number
,l_price_list --price_list_id
,'xxx Legacy SO Number'
,trim(h1.legacy_so_num)
,l_ship_to_org_id
,l_bill_to_org_id
);

update xxx_so_header_stg
set verify_flag = 'Y'
where customer_name = h1.customer_name
and legacy_so_num = h1.legacy_so_num;

exception
when others then
l_error_message := sqlerrm;
l_verify_flag := 'N';
update xxx_so_header_stg
set verify_flag = 'N',
error_message = 'Header error'||l_error_message
where customer_name = h1.customer_name
and legacy_so_num = h1.legacy_so_num;

goto next_so;
end;

------------------Line Details ---------------------

for l1 in c_lines (h1.legacy_so_num)
loop

BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4) = trim(upper(l1.item_code))
and organization_id = l_organization_id ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not existing';
END;


BEGIN
select uom_code
into l_uom_code
from mtl_units_of_measure_vl
where uom_code = trim(upper(l1.uom));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'UOM is Not Valid';
END;


BEGIN
select transaction_type_id
into l_line_type_id
from oe_transaction_types_tl
where name = trim(upper(l1.line_type));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Line Type is Not Valid';
END;

if l_verify_flag <> 'N' then

begin

insert into oe_lines_iface_all
(
order_source_id
,orig_sys_document_ref
,orig_sys_line_ref
,line_number
,inventory_item_id
,ordered_quantity
,ship_from_org_id
,org_id
,pricing_quantity
,unit_selling_price
,unit_list_price
,price_list_id
,payment_term_id
,schedule_ship_date
,request_date
,created_by
,creation_date
,last_updated_by
,last_update_date
,line_type_id
,calculate_price_flag
)
Values
(
l_order_source_id --order_source_id
,oe_order_headers_s.currval --orig_sys_document_ref
,oe_order_lines_s.nextval --orig_sys_line_ref
,trim(l1.line_num) --line_number
,l_inventory_item_id --inventory_item_id
,trim(l1.qty) --ordered_quantity
,l_organization_id --ship_from_org_id
,l_org_id --ship_from_org_id
,trim(l1.qty) --pricing_quantity
,trim(l1.price_unit) --unit_selling_price
,trim(l1.price_unit) --unit_list_price
,l_price_list --price_list_id
,l_term_id --payment_term_id
,to_date(l1.scheduled_ship_date,'DD-MON-RRRR') --schedule_ship_date
,to_date(l1.request_date,'DD-MON-RRRR') --request_date
,l_user_id --created_by
,sysdate --creation_date
,l_user_id --last_updated_by
,sysdate --last_update_date
,l_line_type_id --line_type_id
,'N'
);

update xxx_so_lines_stg
set verify_flag = 'Y'
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;

exception
when others then
rollback to a;
l_error_message := sqlerrm;
update xxx_so_lines_stg
set verify_flag = 'N',
error_message = 'Line error'||l_error_message
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;

update xxx_so_header_stg
set verify_flag = 'N'
where legacy_so_num = l1.legacy_so_num;

goto next_so;
end;

else
rollback to a;
update xxx_so_lines_stg
set verify_flag = 'N',
error_message = l_error_message
where legacy_so_num = l1.legacy_so_num
and item_code = l1.item_code;

update xxx_so_header_stg
set verify_flag = 'N'
where legacy_so_num = l1.legacy_so_num;

goto next_so;
end if;

end loop ;

else

update xxx_so_header_stg
set verify_flag = 'N',
error_message = l_error_message
where legacy_so_num = h1.legacy_so_num;

end if;

next_so --(This code will be inside << >>)
commit;
end loop;

END xxx_so_api;
/

8 comments:

MuBeEn LoDhi said...

Mind Blowing.....!
I implement your coding and got success.


Thanks

jemima joseph said...

can u tell me the values to be inserted in to stagging table?

preeda said...

Oracle Form or Sql Loader

Unknown said...

Please keep control file for the reference
It will be helpful to the beginners

Unknown said...

a. mahesh ur r8

Anonymous said...

Superb code presentation!!
Thanks Alot!

Unknown said...

Can you tell me how to check the sales order

Anonymous said...

Hi,

We are attempting to perform import from our legendary system into Oracle Order Management module using 'Order Import' process.

However there are many Sales Order records which are already 'closed'. Is there a way to run the order import process for closed order line using operation code 'insert'?