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;
/