Item on hand quantity , Lot and Serial No Interfaces

TO MIGRATE ITEM ON HAND QUANTITY , LOT and SERIAL NO
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Interface Tables are :-
------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface


How to run the Import Pgm :
-------------------------------
Inventory Super User ->
Oracle Inventory ->
Setup ->
Transactions ->
Interface Manager ->
SubInventory ->
Material Transaction ->
Tool from Menu ->
Launch Manager ->
Process Transaction Interface
-----------------------------------
If error is occurring go to Transactions -> Open Transaction Interface -> Error -> See error
After correcting the error apply the below code
/* update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3); */
----------------------------------------------------------------

The script to migrate legacy data into interface tables
-----------------------------------------------------

CREATE OR REPLACE procedure xxx_onhand_qty_update
as

x number:=null;
l_err_msg varchar2(2000);
l_row_status varchar2(3);
l_inventory_item_id number:=null;
l_organization_id number;
l_count number:=null;
l_inventory_location_id number := null;

cursor c_onhand is
select *
from xxx_tab_onhand_stg
where nvl(row_status,'N') = 'N'
order by from_serial;

begin

for c_onhand_rec in c_onhand
loop
l_row_status := 'Y';

begin
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code;
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No Organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;

select count(*) into l_count
from mtl_system_items_b
where upper(segment1) =upper(c_onhand_rec.item_name)
and organization_id =l_organization_id
and inventory_item_status_code='Active';

if l_count=0 then

update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No item existing in Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count>1 then

update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Multiple items existing Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count=1 then

select inventory_item_id into l_inventory_item_id
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id
and inventory_item_status_code='Active';

if l_inventory_item_id is not null then

update xxx_tab_onhand_stg
set transaction_uom=(select distinct primary_uom_code
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name))
,inventory_item_id=(select distinct(inventory_item_id)
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id)
,dist_account=(select code_combination_id
from gl_code_combinations
where segment4 = 1327108)
,userid= (select user_id
from fnd_user
where user_name = 'XXXX')
,transaction_interface_id=mtl_material_transactions_s.nextval
,transaction_type_id = (select transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt')
,row_status='Y'
,organization_id = (select organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code)
where item_name = c_onhand_rec.item_name;
commit;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Inventory Item Id is not Existing'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;

else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Item Name or Organization ID is not Valid'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
commit;

begin
select inventory_location_id
into l_inventory_location_id
from mtl_item_locations
where segment1 = c_onhand_rec.locator
and subinventory_code = trim(c_onhand_rec.sub_inventory_code);
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Invalid Locator'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;

if l_row_status = 'Y' then

--On hand quantity Migration
begin

insert into mtl_transactions_interface
(
transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,lock_flag
,flow_schedule
,scheduled_flag
,transaction_header_id
,inventory_item_id
,transaction_interface_id
,subinventory_code
,distribution_account_id
,transaction_cost
,locator_id
,transaction_reference
)
select
transaction_type_id
,transaction_uom
,'30-DEC-2007'
,organization_id
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,3
,1
,transaction_interface_id
,transaction_interface_id
,'inv'
,2
,'Y'
,2
,transaction_interface_id
,inventory_item_id
,transaction_interface_id
,sub_inventory_code
,dist_account
,transaction_cost
, l_inventory_location_id
,'Migration'
from xxx_tab_onhand_stg
where upper(item_name)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transactions_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
---- END
-----ON HAND QUANTITY LOTS MIGRATION
begin

insert into mtl_transaction_lots_interface
(
transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_code
,last_update_login
,product_transaction_id
,primary_quantity
,lot_expiration_date)
select transaction_interface_id
,lot_number
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,'inv'
,userid
,transaction_interface_id
,transaction_quantity
, expiry_date
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transaction_lots_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;

end;
--- END
---- SERIAL NUMBER MIGRATION -----------
begin

insert into mtl_serial_numbers_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
select
transaction_interface_id,
sysdate,
userid,
sysdate,
userid,
userid,
from_serial,
to_serial,
'inv',
transaction_interface_id
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to MTL_SERIAL_NUMBERS_INTERFACE : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;

end if;
end loop;


END xxx_onhand_qty_update;
/

Item with Item Category Interface

This script will help to migrate Item Master with Item Category details
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

CREATE OR REPLACE PROCEDURE XXX_Item_Master_Org_Api AS

l_err_flag varchar2(3);
l_err_msg varchar2(2500);
l_count number (2);
l_category_set_id number (20);
l_category_id number (20);
l_org_id number (4);
l_template_name varchar2(250);
l_uom varchar2(20);
l_item_type varchar2(20);
l_organization_id number (10);

--CURSOR TO FETCH THE DATA FROM STAGING TABLE
cursor c1 is
select *
from xxx_item_master_stg
where nvl(verify_flag,'N') = 'N' ;

begin

for c_rec in c1 loop

begin
--- initialize the variables
l_err_flag :='Y';
l_err_msg := null;

---*********** MASTER ORGANIZATION VALIDATION
begin
select organization_id
into l_organization_id
from org_organization_definitions
where upper(organization_name) = upper(trim('XXX ITEM MASTER'));
exception
when others then
l_err_flag := 'Y';
l_err_msg := 'INVALID ORGANIZATION' ;
end ;

---*********** ITEM TYPE VALIDATION
begin
select lookup_code
into l_item_type
from fnd_lookup_values
where lookup_type = 'ITEM_TYPE'
and upper(meaning) = trim(upper(c_rec.item_type));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID ITEM TYPE' ;
end ;

---*************** ITEM CODE VALIDATION

begin
l_count := 0;
select count(*)
into l_count
from mtl_system_items_b
where upper(trim(segment1)) = upper(trim(c_rec.item_code))
and organization_id = l_organization_id;
if l_count > 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'ITEM ALREADY EXISTING' ;
end if;

----*********** VALIDATE THE DESCRIPTION
if trim(c_rec.description) is null then
l_err_flag := 'N';
l_err_msg := l_err_msg || 'INVALID DESCRIPTION';
end if;
end;


--- ITEM TEMPLATE VALIDATION

begin
select template_name
into l_template_name
from mtl_item_templates
where upper(trim(template_name)) = upper(trim(c_rec.template_name));
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID TEMPLATE NAME';
end ;
end;

--- PRIMARY UOM VALIDATION
begin
select unit_of_measure
into l_uom
from mtl_units_of_measure
where upper(trim(uom_code)) = upper(trim(c_rec.uom)); ---eg litre, numbers
exception
when others then
l_err_flag := 'N';
l_err_msg := l_err_msg||'INVALID UOM';
end;

--- ITEM CATEGORY VALIDATION
begin
select category_id
into l_category_id
from mtl_categories_v
where segment1 =trim(c_rec.cat_segment1)
and segment2 = trim(c_rec.cat_segment2) ;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY';
end;

---************** ITEM CATEGORY SET VALIDATION
begin
select category_set_id
into l_category_set_id
from mtl_category_sets_tl
where category_set_name = trim(c_rec.cat_set_name);
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY SET';
end;


--- ITEM CATEGORY COMBINATION VALIDATION
begin
l_count := 0;
select count(*)
into l_count
from mtl_category_set_valid_cats_v
where category_set_id = l_category_set_id
and category_id = l_category_id ;
if l_count = 0 then
l_err_flag := 'N';
l_err_msg :=l_err_msg || 'INVALID CATEGORY COMBINATION' ;
end if;
exception
when others then
l_err_flag := 'N';
l_err_msg :=l_err_msg||'INVALID CATEGORY COMBINATION';
end;



---- IF ALL VALIDATIONS ARE CORRECT, THEN INSERT DATA INTO INTERFACE TABLE
---- THEN BY USING IMPORT ITEM CONCURRENT PGM, IMPORT ITEMS FROM INTERFACE TABLE INTO BASE TABLE

if l_err_flag='Y' then
begin

insert into mtl_system_items_interface
(
item_type
,process_flag
,segment1
,description
,primary_unit_of_measure
,set_process_id
,template_name
,organization_id
,transaction_type
)
values
(
l_item_type
,1
,c_rec.item_code
,c_rec.description
,l_uom
,3
,l_template_name
,l_organization_id
,'CREATE'
);


insert into mtl_item_categories_interface
(
item_number
,category_set_id
,category_id
,process_flag
,organization_id
,set_process_id
,transaction_type
)
values
(
c_rec.item_code
,l_category_set_id
,l_category_id
,1
,l_organization_id
,3
,'CREATE'
);

update xxx_item_master_stg
set verify_flag = 'Y',
err_msg = null
where item_code = c_rec.item_code ;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;
end;

commit;

else

update xxx_item_master_stg
set verify_flag = 'N'
,err_msg = l_err_msg
where item_code = c_rec.item_code ;

end if;
commit;
end loop;

commit;

exception
when others then
dbms_output.put_line(sqlerrm);

end xxx_item_master_org_api;
/

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

Purchase Order Interface

PURCHASE ORDER INTERFACES
**********************************

Interface Tables :-
-----------------
po_headers_interface
po_lines_interface


Import Concurrent Program :- Import Standard Purchase Orders


Staging Table :-
--------------
CREATE TABLE XXX_PURCHASE_ORDER_STG
(
LEGACY_PONUM NUMBER(20),
CURRENCY_CODE VARCHAR2(15 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE),
VENDOR_SITE_CODE VARCHAR2(15 BYTE),
BILL_TO VARCHAR2(60 BYTE),
SHIP_TO VARCHAR2(60 BYTE),
STATUS VARCHAR2(40 BYTE),
AGENT_NAME VARCHAR2(100 BYTE),
ITEM VARCHAR2(100 BYTE),
LINE_NUM NUMBER(3),
UNIT_PRICE NUMBER(10,2),
QUANTITY NUMBER(10),
NEED_BY_DATE DATE,
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1 BYTE),
H_ERROR_MESSAGE VARCHAR2(2500 BYTE),
L_ERROR_MESSAGE VARCHAR2(2500 BYTE)
)


PL/SQL Script to upload data from staging table to interface table
------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE xxx_po_api
AS

---To Import data from Interface to Base Tables - Import Standard Purchase Orders
-- please do the following: to see the errors
-- Run the program - Purchasing Interface Errors Report
-- choose parameter : PO_DOCS_OPEN_INTERFACE
-- po_interface_errors


l_currency_code fnd_currencies_vl.currency_code%type;
l_verify_flag Char(1);
l_error_message varchar2(5000);
l_vendor_id po_vendors.vendor_id%type;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_ship_to hr_locations.location_id%type;
l_bill_to hr_locations.location_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_legacy_ponum number(20):=0;
l_batch_id number(3);


CURSOR C_PO_HEADER IS
select distinct legacy_ponum,
currency_code,
vendor_name,
vendor_site_code,
ship_to,
bill_to,
status
from xxx_purchase_order_stg;

CURSOR C_PO_LINES(l_legacy_ponum NUMBER) IS
select *
from xxx_purchase_order_stg
where trim(legacy_ponum) = trim(l_legacy_ponum);



BEGIN



FOR H1 IN C_PO_HEADER
LOOP


l_verify_flag := 'Y' ;
l_error_message := NULL;

BEGIN

select currency_code
into l_currency_code
from fnd_currencies_vl
where enabled_flag = 'Y'
and currency_flag = 'Y'
and upper(currency_code) = upper(trim(H1.currency_code));

EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Currency Code is not Valid...';

END;


BEGIN

select vendor_id
into l_vendor_id
from po_vendors
where upper(vendor_name) = upper(trim(H1.vendor_name)) ;

EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor is not Existing...';

END;


BEGIN

select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where vendor_id = l_vendor_id
and vendor_site_code = upper(trim(H1.vendor_site_code)) ;

EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor Site is not Existing...';

END;


BEGIN

select location_id
into l_ship_to
from hr_locations
where location_code = upper(trim(H1.ship_to));

EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Ship To Location is not Existing...';

END;


BEGIN

select location_id
into l_bill_to
from hr_locations
where location_code = upper(trim(H1.bill_to));

EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Bill To Location is not Existing...';

END;


If H1.status = 'Approved' then
l_batch_id := 100 ;
elsif H1.status = 'Incomplete' then
l_batch_id := 101 ;
else
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Status is not valid...';
end if;

l_legacy_ponum := trim(H1.legacy_ponum) ;


IF l_verify_flag <> 'N' THEN

insert into po_headers_interface
(interface_header_id,
batch_id,
action,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id
)
values
(po_headers_interface_s.nextval,
l_batch_id,
'ORIGINAL',
'STANDARD',
l_currency_code,
5479,
l_vendor_id,
l_vendor_site_id,
l_ship_to,
l_bill_to );

update xxx_purchase_order_stg
set h_verify_flag = 'Y'
where legacy_ponum = l_legacy_ponum;

COMMIT;


FOR L1 IN C_PO_LINES(l_legacy_ponum)
LOOP


BEGIN

select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where segment1||'.'||segment2||'.'||segment3||'.'||segment4=L1.item
and organization_id = (select inventory_organization_id
from hr_locations
where location_id = l_ship_to ) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Inventory Item is not Existing...';

END;

IF L1.unit_price IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Unit Price is not Existing...';
ELSIF L1.quantity IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Quantity is not Existing...';
ELSIF L1.need_by_date IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Need By Date is not Existing...';
END IF;


IF l_verify_flag <> 'N' THEN

insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
line_num,
item_id,
unit_price,
quantity,
Need_By_Date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
L1.line_num,
l_inventory_item_id,
L1.unit_price,
L1.quantity,
L1.need_by_date);


update xxx_purchase_order_stg
set l_verify_flag = 'Y'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;


ELSE

update xxx_purchase_order_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;

END IF;

COMMIT;

END LOOP;

ELSE

update xxx_purchase_order_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where legacy_ponum = H1.legacy_ponum;

END IF;

COMMIT;

END LOOP;

end xxx_po_api;
/

Bom Interfaces

BILL OF MATERIAL INTERFACE
****************************
Interface tables are :-
----------------------------
BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE

---MTL_INTERFACE_ERRORS(Error Message table)

Import Porgram :-
Bill and Routing Interface (Here parameter import routings will be no)
---------------------------------------------------------------------------------
BOM Sharing
--------------
Bill of material created for one organization can be share with other organizations.
Steps 1. Create the master bom and its inventory components
2. Create the same bom for other organizations with additional two fields i.e Common_Organization_Id and Common_Assembly_Item_Id. Here common_organization_id will be the master organization id and common assembly item id will be the master assembly item id(same assembly item id of sub org also).
If you are running Bill and Routing Interface for master and sub organization bom at the same time, there is possibility of throwing error if the master organization id is greater than sub organization id.
So better to run the Master bom first then run the child organizations.
--------------------------------------------------------------------------------

create the following staging table
CREATE TABLE XXX_BOM_BILL_MTLS_STG
(
ITEM_NAME VARCHAR2(50),
REVISION_NO CHAR(5),
ITEM_SEQ_BOM_COMP NUMBER(5),
OPERATION_SEQUENCE NUMBER(5),
BOM_COMPONENT VARCHAR2(50),
QUANTIY NUMBER(10,2),
YIELD_FACTOR NUMBER(10,2),
SUPPLY_TYPE VARCHAR2(10),
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(3000)
)
-------------------------------------------------------------------------------------
The script to upload data into interface tables
CREATE OR REPLACE procedure xxx_bom_bill_mtls_api
(errbuf varchar2,retcode varchar2) as
l_verify_flag char(1);
l_error_message varchar2(2500);
l_organization_id number(15);
l_inventory_item_id number(15);
l_component_item_id number(15);
l_bom_exists number(15);
l_wip_supply_type number(3);

CURSOR C_HEADER IS
select distinct item_name,revision_no
from xxx_bom_bill_mtls_stg
where nvl(h_verify_flag,'N') = 'N';

CURSOR C_LINES (p_item_name varchar2)IS
select *
from xxx_bom_bill_mtls_stg
where item_name = p_item_name
order by item_name,item_seq_bom_comp;

BEGIN

FOR C_BOM IN C_HEADER
LOOP

l_verify_flag := 'Y';
l_error_message := null;
l_bom_exists := null;

BEGIN
select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Main Store';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Organization is not valid';
END;

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

BEGIN
select assembly_item_id
into l_bom_exists
from BOM_BILL_OF_MATERIALS_V
where organization_id = l_organization_id
and assembly_item_id = l_inventory_item_id ;
if l_bom_exists >0 then
l_verify_flag := 'N';
l_error_message := l_error_message'Item already existing';
end if;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

IF l_verify_flag <> 'N' then
savepoint A;

BEGIN
insert into
BOM_BILL_OF_MTLS_INTERFACE
(
ASSEMBLY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE
,REVISION
,ASSEMBLY_TYPE
,PROCESS_FLAG
,ATTRIBUTE1
)
VALUES
(
l_inventory_item_id
,l_organization_id
,'CREATE'
,trim(nvl(c_bom.revision_no,0))
,1 ---1) Manufacturing, 2) Engineering
,1
,1
) ;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'Y'
where item_name = c_bom.item_name;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := SQLERRM;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name;
goto next_bom;
END;

FOR C_COMP IN C_LINES(c_bom.item_name)
LOOP

BEGIN
select inventory_item_id
into l_component_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and upper(segment1'.'segment2'.'segment3'.'segment4)
=upper(trim(C_COMP.bom_component));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Bom Component is not valid';
END;

BEGIN
If c_comp.supply_type = 'Phantom' then
l_wip_supply_type := 6;
else
l_wip_supply_type := 1;
end if;
END;

IF l_verify_flag <> 'N' then

BEGIN
insert into
bom_inventory_comps_interface
(
assembly_item_id
,process_flag
,transaction_type
,component_item_id
,component_sequence_id
,item_num
,operation_seq_num
,organization_id
,effectivity_date
,component_quantity
,component_yield_factor
,attribute6
,attribute9
,wip_supply_type
)
VALUES
(
l_inventory_item_id
,1
,'CREATE'
,l_component_item_id
,bom_inventory_components_s.nextval
,trim(c_comp.item_seq_bom_comp)
,trim(C_COMP.operation_sequence)
,l_organization_id
,sysdate
,trim(c_comp.quantiy)
,trim(c_comp.yield_factor)
,1
,1
,l_wip_supply_type
);

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'Y'
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
rollback to savepoint A;

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom ;
END;

ELSE
rollback to savepoint A;
update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom;
END IF;

END LOOP;

ELSE
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name ;

END IF;
<
COMMIT;
END LOOP;
end xxx_bom_bill_mtls_api;
/
---------------------------------------------------------------------------------------

Routing Interface

ROUTING INTERFACE
*****************************
Steps to do :-
1. Data receiving in Data Template
2. Create the staging table
3. Upload data from excel template to staging table
4. Create the below script and execute it(data from staging to interface table with custom validations)
5. go to bills of material responsibility and run the "Bill and Routing Interface"
6. parameter :-All Organizations - applicable only if you are running for multiple organizations
Import Routings - Yes
Import Bills of Material -No
Delete Processed Rows - Yes (imported records will delete successfully)

to view the errors
-------------------
SELECT process_flag,
err. transaction_id,
error_message,
message_name
FROM mtl_interface_errors err,
bom_op_resources_interface bom
WHERE err.transaction_id = bom.transaction_id
***********************************************
Routing Interface Tables are :-
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_OP_RESOURCES_INTERFACE

View Error :- MTL_INTERFACE_ERRORS

Routing Import Program is :- Bill and Routing Interface
Here Bill Import will be No

Data Template
--------------
item_name,
routing_revision_no,
operation_seq_num,
department,
resource_seq_num,
total_time,
schedule_sequence,
assigned_units,
scheduling_flag

staging table
------------
create table xxx_bom_op_routing_stg
(
item_name varchar2(30),
revision_no number(2),
operation_sequence number(3),
department varchar2(30),
resource_sequence number(3),
resource_code varchar2(20),
usage_rate number(10,2),
assigned_units number(5),
schedule_flag varchar2(5),
schedule_seq_num number(2),
rout_verify_flag varchar2(1),
seq_verify_flag varchar2(1),
res_verify_flag varchar2(1),
error_message varchar2(2500)
)
----------------------------------------------------

CREATE OR REPLACE procedure xxx_bom_routing_api
is

l_organization_id number(5);
l_rout_verify_flag CHAR(1);
l_seq_verify_flag CHAR(1);
l_res_verify_flag CHAR(1);
l_error_message varchar2(2500);
l_inventory_item_id number(20);
l_department_id number(10);
l_resource_id number(10);
l_department_code varchar2(20);
l_resource_code varchar2(20);
l_schedule_flag number(3);
l_res_uom varchar(10);
l_link_resource number(10);

---Routing Cursor
cursor c_rout is
Select distinct item_name,revision_no
from xxx_bom_op_routing_stg;

---Operation Sequence Cursor
cursor c_seq (p_item_name varchar2) is
select distinct item_name,revision_no,operation_sequence,department
from xxx_bom_op_routing_stg
where item_name = p_item_name
order by operation_sequence;

---Resource Cursor
cursor c_res (p_item_name varchar2,p_operation_sequence number) is
select *
from xxx_bom_op_routing_stg
where item_name = p_item_name
and operation_sequence = p_operation_sequence
order by operation_sequence,resource_sequence;

begin

for rout1 in c_rout
loop

l_rout_verify_flag := 'Y';
l_seq_verify_flag := 'Y';
l_res_verify_flag := 'Y';
l_error_message := null;

begin
select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Spares Store';
exception
when others then
l_rout_verify_flag := 'N';
l_error_message := 'Organization is not Valid';
end;

begin
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4) = trim(upper(rout1.item_name))
and organization_id = l_organization_id;
exception
when others then
l_rout_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not Valid';
end;

IF l_rout_verify_flag <> 'N' then

savepoint a1;

begin

insert into bom_op_routings_interface
(
assembly_item_id,
process_revision,
process_flag,
transaction_type,
routing_type,
organization_id
)
values
(
l_inventory_item_id,
Decode(rout1.revision_no,0,null,rout1.revision_no),
1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE',
1,----1) manufacturing, 2) engineering
l_organization_id
);

update xxx_bom_op_routing_stg
set rout_verify_flag='Y'
where item_name = ROUT1.item_name ;

exception

when others then
l_error_message := sqlerrm;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N',
error_message = l_error_message
where item_name = rout1.item_name;

goto next_rout;
end;

for seq1 in c_seq (rout1.item_name)
loop

begin
select department_id,department_code
into l_department_id,l_department_code
from bom_departments
where upper(department_code) = upper(trim(seq1.department))
and organization_id = l_organization_id ;
exception
when others then
l_error_message := l_error_message||'Department Code is not valid';
l_seq_verify_flag := 'N';
end;

if trim(seq1.operation_sequence) is null then
l_error_message := l_error_message||'Operation sequence should not be null';
l_seq_verify_flag := 'N';
end if;

if l_seq_verify_flag <> 'N' then

begin

insert into bom_op_sequences_interface
(
assembly_item_id,
operation_seq_num,
department_id,
department_code,
process_flag,
transaction_type,
organization_id,
effectivity_date
)
values
(
l_inventory_item_id,
trim(seq1.operation_sequence),
l_department_id,
l_department_code,
1,
'CREATE',
l_organization_id,
sysdate
);

update xxx_bom_op_routing_stg
set seq_verify_flag = 'Y'
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;

exception
when others then
rollback to a1;
l_error_message := 'Op Sequence Error.'||SQLERRM;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = SEQ1.item_name
and operation_sequence = SEQ1.operation_sequence;

update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name ;

goto next_rout;
end;

for res1 in c_res (seq1.item_name,seq1.operation_sequence)
loop

begin
select resource_id,resource_code,unit_of_measure
into l_resource_id,l_resource_code,l_res_uom
from bom_resources
where upper(resource_code) = trim(upper(res1.resource_code))
and organization_id = l_organization_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not valid';
end;

begin
select resource_id
into l_link_resource
from bom_department_resources_v
where organization_id = l_organization_id
and department_id = l_department_id
and resource_id = l_resource_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not Linked with Department';
end;

if ((res1.resource_sequence is null) or (res1.usage_rate is null) or (res1.assigned_units is null)) then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource Seq or usage rate or assigned units is null';
end if;

If (upper(trim(res1.schedule_flag)) = 'YES' and l_res_uom <> 'HR') Then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'This Resource cannot be Schedule';
elsif upper(trim(res1.schedule_flag)) = 'YES'then
l_schedule_flag := 1 ;
else
l_schedule_flag := null;
end if;

IF l_res_verify_flag <> 'N' then

begin

insert into bom_op_resources_interface
(
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount, --basis_type ,
assigned_units,--schedule_flag,autocharge_type,
assembly_item_id,
operation_seq_num, --operation_sequence_id,
process_flag,
transaction_type,
effectivity_date,
organization_id ,
schedule_flag,
schedule_seq_num
)
values
(
trim(res1.resource_sequence),
l_resource_id,
l_resource_code,
trim(res1.usage_rate), --1,
trim(res1.assigned_units), -- 1, 2,
l_inventory_item_id,
trim(seq1.operation_sequence), --2346216 ,
1,
'CREATE',
sysdate,
l_organization_id,
l_schedule_flag,
trim(res1.schedule_seq_num)
);

update xxx_bom_op_routing_stg
set res_verify_flag = 'Y'
where item_name = RES1.item_name
and department = res1.department
and resource_code = res1.resource_code;

exception
when others then
rollback to a1;
l_error_message := l_error_message||'Op Resource Sequence Error.'||sqlerrm;
update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = RES1.item_name
and department = RES1.department
and resource_code = RES1.resource_code;

update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = RES1.item_name
and department = RES1.department;

update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;

goto next_rout;
end;

else

rollback to a1;

update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = res1.item_name
and department = res1.department
and resource_code = res1.resource_code;

update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = res1.item_name
and department = res1.department;

update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;

goto next_rout;
end if; --if_res_verify_flag

end loop ;

else
rollback to savepoint a1;

update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;

update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name;

goto next_rout;

end if; --if_seq_verify_flag

end loop ;

else

update xxx_bom_op_routing_stg
set rout_verify_flag = 'N',
error_message = l_error_message
where item_name = ROUT1.item_name;

end if; ---if_rout_verify_flag

next_rout --(This will be in << >>)
commit;
end loop ;

END xxx_bom_routing_api ;
/

WIP Resource Transaction Interface

RESOURCE TRANSACTION INTERFACE
*****************************************

Interface Table :- wip_cost_txn_interface

Import Porgram :- The concurrent pgm automatically will execute when you insert data into above table. The conc program is Actual Cost Worker

PL/SQL Script to Upload Data into Interface Table
---------------------------------------------------

CREATE OR REPLACE procedure xxx_resource_txn_int_api (p_organization_id number,
p_wip_entity_id number,
p_op_seq varchar2,
p_resource_code varchar2,
p_start_time varchar2,
p_end_time varchar2,
p_employee_num varchar2,
errbuf out varchar2,
rectcode out varchar2) as
----Variable Declarations

l_wip_entity_id wip_entities.wip_entity_id%type;
l_organization_id wip_entities.organization_id%type;
l_wip_entity_name wip_entities.wip_entity_name%type;
l_entity_type wip_entities.entity_type%type;
l_primary_item_id wip_entities.primary_item_id%type;
l_organization_code org_organization_definitions.organization_code%type;

l_operation_sequence_id bom_operation_sequences.operation_sequence_id%type;
l_operation_seq_num bom_operation_sequences.operation_seq_num%type;
l_department_id bom_departments.department_id%type;
l_department_code bom_departments.department_code%type;

l_resource_seq_num bom_operation_resources.resource_seq_num%type;
l_resource_id bom_operation_resources.resource_id%type;
l_standard_rate_flag bom_operation_resources.standard_rate_flag%type;
l_usage_rate_or_amount bom_operation_resources.usage_rate_or_amount%type;
l_basis_type bom_operation_resources.basis_type%type;
l_autocharge_type bom_operation_resources.autocharge_type%type;
l_resource_code bom_resources.resource_code%type;
l_resource_type bom_resources.resource_type%type;

l_employee_id number(10);
l_employee_num varchar2(20);

l_verify_flag char(1) :='Y';
l_error_message varchar2(2500);
l_acct_period_id org_acct_periods_v.acct_period_id%type;
v_quantity number(10,2);

begin

fnd_file.put_line(fnd_file.log,'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' Resource Transaction Interface ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');


l_verify_flag := 'Y' ;


BEGIN
select wip.wip_entity_id,
wip.organization_id,
wip.wip_entity_name,
wip.entity_type,
wip.primary_item_id ,
ood.organization_code
into l_wip_entity_id,
l_organization_id,
l_wip_entity_name,
l_entity_type,
l_primary_item_id,
l_organization_code
from wip_entities wip,
org_organization_definitions ood
where wip.organization_id = ood.organization_id
and wip.wip_entity_id = p_wip_entity_id
and wip.organization_id = p_organization_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Job Name Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Job Name Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Job has some unidentified errors...';
END;


BEGIN

select operation_sequence_id,
operation_seq_num,
department_id,
department_code
into l_operation_sequence_id,
l_operation_seq_num,
l_department_id,
l_department_code
from wip_operations_v
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = to_number(p_op_seq);


/*select bos.operation_sequence_id,
bos.operation_seq_num,
bos.department_id,
bd.department_code
into l_operation_sequence_id,
l_operation_seq_num,
l_department_id,
l_department_code
from bom_operation_sequences bos,
bom_operational_routings bor,
bom_departments bd
where bor.routing_sequence_id = bos.routing_sequence_id
and bos.department_id = bd.department_id
and bor.organization_id = bd.organization_id
and bor.organization_id = l_organization_id
and bor.assembly_item_id = l_primary_item_id
and bos.operation_seq_num = 10 ; */

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Sequence Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message ||' Operation Sequence Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Operation Sequence has some unidentified errors...';

END;


BEGIN

select resource_seq_num,
resource_id,
standard_rate_flag,
usage_rate_or_amount,
basis_type,
autocharge_type,
resource_code,
resource_type
INTO l_resource_seq_num,
l_resource_id,
l_standard_rate_flag,
l_usage_rate_or_amount,
l_basis_type,
l_autocharge_type,
l_resource_code,
l_resource_type
from WIP_OPERATION_RESOURCES_V
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num
and resource_code = p_resource_code ;

/*from bom_operation_resources bor,
bom_resources br
where bor.resource_id = br.resource_id
and operation_sequence_id = l_operation_sequence_id
and br.organization_id = l_organization_id
and bor.resource_seq_num = 1; */

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Resources Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Operation Resources Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Operation Resources has some unidentified errors...';

END;


BEGIN

select acct_period_id
into l_acct_period_id
from org_acct_periods_v
where sysdate between start_date and end_date
and organization_id = l_organization_id ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Account Period Id Not Found...';
WHEN TOO_MANY_ROWS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||' Account Period Is More than One...';
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message:= l_error_message||' Account Period has some unidentified errors...';

END;

BEGIN
Select person_id,employee_number
into l_employee_id, l_employee_num
from per_all_people_f
where employee_number = p_employee_num
and trunc(sysdate) between effective_start_date and nvl(effective_end_date,sysdate+1);
Exception
when others then
l_employee_id := null;
l_employee_num := null;
null;
END;


BEGIN

select (to_number(to_date(p_end_time,'DD-MON-RRRR HH:MI:SS PM') -
to_date(p_start_time,'DD-MON-RRRR HH:MI:SS PM')) * 24)
into v_quantity
from dual;


IF l_verify_flag <> 'N' then

BEGIN

INSERT INTO wip_cost_txn_interface
(last_update_date, last_updated_by, creation_date, created_by,
process_phase, process_status, transaction_type,
organization_id, organization_code, wip_entity_id,
wip_entity_name, entity_type, primary_item_id, transaction_date,
ACCT_PERIOD_ID, operation_seq_num, resource_seq_num,
department_id, department_code, resource_id, resource_code,
resource_type, usage_rate_or_amount, basis_type,
autocharge_type, standard_rate_flag, TRANSACTION_QUANTITY,
TRANSACTION_UOM, PRIMARY_QUANTITY, PRIMARY_UOM,
activity_id, activity_name, employee_id, employee_num
)
VALUES (SYSDATE, 6437, SYSDATE, 6437,
2, 1, 1,
l_organization_id, l_organization_code, l_wip_entity_id,
l_wip_entity_name, l_entity_type, l_primary_item_id, SYSDATE,
l_acct_period_id, l_operation_seq_num, l_resource_seq_num,
l_department_id, l_department_code, l_resource_id, l_resource_code,
l_resource_type, l_usage_rate_or_amount, l_basis_type,
l_autocharge_type, l_standard_rate_flag, v_quantity,
'HR', v_quantity, 'HR',
1, 'Run', l_employee_id, l_employee_num
);


fnd_file.put_line(fnd_file.log,'Data Migrated into Interface table successfully');
COMMIT;

EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
fnd_file.put_line(fnd_file.log,'Data not Migrated successfully...'||l_error_message);
END;

else

fnd_file.put_line(fnd_file.log,l_error_message);

end if;

END LOOP;

fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ');
fnd_file.put_line(fnd_file.log,' ');
fnd_file.put_line(fnd_file.log,' ');

end xxx_resource_txn_int_api ;
/

WIP Move Transaction Interface

WIP MOVE TRANSACTION INTERFACE
*****************************************

Interface Table :-

WIP_MOVE_TXN_INTERFACE

Concurrent Program to Import data :- Move Transaction Report

Basic Insert Statement
-----------------------

INSERT INTO WIP_MOVE_TXN_INTERFACE
(
LAST_UPDATE_DATE,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY_NAME,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_CODE,
WIP_ENTITY_NAME,
TRANSACTION_DATE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_TYPE,
FM_OPERATION_SEQ_NUM,
FM_INTRAOPERATION_STEP_TYPE,
TO_OPERATION_SEQ_NUM,
TO_INTRAOPERATION_STEP_TYPE
)
VALUES
(
SYSDATE, --LAST_UPDATE_DATE
'KPMG', --LAST_UPDATED_BY_NAME
SYSDATE , --CREATION_DATE
'KPMG', --CREATED_BY_NAME
1, /*1. Move Validation 2. Move Processing 3. Operation Backflush Setup.
You should always load 1 (Move Validation)*/
1, /*1. Pending 2. Running 3. Error You should always load 1 (Pending)*/
'MST', --ORGANIZATION_CODE
'624054', --WIP_ENTITY_NAME
SYSDATE, --TRANSACTION_DATE
1, --TRANSACTION_QUANTITY
'PCS', --TRANSACTION_UOM
2, --TRANSACTION_TYPE /* 1. Move 2. Move Completion 3. Move Return */
10, --FM_OPERATION_SEQ_NUM
2, --FM_INTRAOPERATION_STEP_TYPE /* 1. Queue,2.Run 3.To move,4.Reject, */
10, --TO_OPERATION_SEQ_NUM
3); --TO_INTRAOPERATION_STEP_TYPE /* 1. Queue,2.Run 3.To move,4.Reject, */

WIP Job Interfaces

WIP JOB INTERFACES
************************

Interface Tables for WIP Jobs are :-
------------------------------------
wip_job_schedule_interface
wip_job_dtls_interface

How to Import data from Interface Tables to Base Tables
------------------------------------------------------------
Navigation path: WIP - discrete - import jobs and schedules

Concurrent Program to import data :- WIP Mass Load
group_id = 1 (Same group id as in Interface Table)

Error table :- WIP_INTERFACE_ERRORS


Two types of job creation is there
---------------------------------
1. Create a new job with existing Bills and Routings
2. Create a new job with new Operations, new Resources and new Components


1. New Job with existing Bills and Routings( Here we need to create only job with the assebly item already defined in bom and routing. )
---------------------------------------------------------------------------------------------

INSERT INTO wip_job_schedule_interface
(
organization_code
, primary_item_id
, job_name
, start_quantity
, net_quantity
, first_unit_start_date
, class_code
, status_type
, COMPLETION_SUBINVENTORY
, group_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
'MST' -- organization_id
,973708 -- primary_item_id
,WIP_JOB_NUMBER_S.nextval -- job_name
,2 -- start_quantity
,2 -- Net Quantity
,to_date('09-MAY-2008','DD-MON-RRRR') --first_unit_start_date
,'Discrete' --class_code
,3 --status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
,'ASSY' --COMPLETION_SUBINVENTORY
,10 -- group_id
,1 -- load_type
/*
1 Create Standard Discrete Job
2 Create Pending Repetitive Schedule
3 Update Standard or Non-Standard Discrete Job
4 Create Non-Standard Discrete Job
*/
,2 -- process_phase 2 Validation 3 Explosion 4 Complete 5 Creation
,1 -- process_status 1 Pending 2 Running 3 Error 4 Complete 5 Warning
,6437 -- created_by
, SYSDATE -- creation_date
,6437 -- last_updated_by
, SYSDATE -- last_update_date
);


2. Create a new job with new Operations, new Resources and new Components
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Load Type will be differenct in details interface table

a) Create Job

INSERT INTO wip_job_schedule_interface
(
organization_code
, primary_item_id
, job_name
, start_quantity
, net_quantity
, FIRST_UNIT_START_DATE
, LAST_UNIT_COMPLETION_DATE
, CLASS_CODE
, COMPLETION_SUBINVENTORY
, group_id
, header_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
'XXX' -- organization_id
,944397 -- primary_item_id
,WIP_JOB_NUMBER_S.nextval -- job_name
,2 -- start_quantity
,2 -- Net Quantity
,to_date('09-MAY-2008','DD-MON-RRRR') --FIRST_UNIT_START_DATE
,sysdate --LAST_UNIT_COMPLETION_DATE
,'Nonstandard' --CLASS_CODE
,'FG' --COMPLETION_SUBINVENTORY
,1 -- group_id
,3530 -- header_id
,4 -- load_type
,2 -- process_phase
,1 -- process_status
,6157 -- created_by
,SYSDATE -- creation_date
,6157 -- last_updated_by
,SYSDATE -- last_update_date
)


b) Create Component

INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, department_id
, inventory_item_id_new
, wip_supply_type
, date_required
, quantity_per_assembly
, required_quantity
, quantity_issued
--, mrp_net_flag
--, description
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,507 --department_id
,963166 --inventory_item_id_new
,7 --wip_supply_type
,sysdate + 2 --date_required
,1 --quantity_per_assembly
,2 --required_quantity
,1 --Quantity_Issued
--,1 --mrp_net_flag
--,'Sample comp' --description
,32060 --group_id
,3530 --parent_header_id
,2 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157 --last_updated_by
, SYSDATE )

c) Operation Sequence

INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, department_id
, first_unit_start_date
, first_unit_completion_date
, last_unit_start_date
, last_unit_completion_date
, minimum_transfer_quantity
, count_point_type
, BACKFLUSH_FLAG
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,507 --department_id
,sysdate --First_Unit_Start_Date
,sysdate+2 --first_unit_comletion_date
,sysdate --last_unit_start_date
,sysdate + 3 --last_unit_completion_date
,1 --minimum_transfer_quantity
,1 -- count_point_type
,1 --BACKFLUSH_FLAG
,32060 --group_id
,3530 --parent_header_id
,3 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157
, SYSDATE )

c) Resource

INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, resource_seq_num
, resource_id_new
, required_quantity
, assigned_units
, applied_resource_units
, usage_rate_or_amount
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,1 --resource_seq_num
,5075 --resource_id_new
,10 --required_quantity
,1 --assigned_units
,15 --applier_resource_units
,5 --usage_rate_or_amount
,32060 --group_id
,3530 --parent_header_id
,1 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157 --last_updated_by
, SYSDATE )

AR Invoice Interface

AR INVOICE INTERFACE
************************

Interface Tables are :-
-----------------------
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all

Concurrent Program to import data from interface tables :-
------------------------------------------------------------
Autoinvoice Import Program

Script to Migrate data from Staging table to Interface Table
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE xxx_ar_invoice_api (errbuf out varchar2, rectcode out varchar2)
AS

l_org_id hr_operating_units.organization_id%type;
l_sob_id hr_operating_units.set_of_books_id%type;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%type;
l_gl_id_rev ra_cust_trx_types_all.gl_id_rev%type;
l_cust_trx_type_name ra_cust_trx_types_all.name%type;
l_currency_code fnd_currencies.currency_code%type;
l_term_id ra_terms_tl.term_id%type;
l_term_name ra_terms_tl.name%type;
l_address_id hz_cust_acct_sites_all.cust_acct_site_id%type;
l_customer_id hz_cust_accounts.cust_account_id%type;
l_verify_flag char(1);
l_error_message varchar2(2500);


BEGIN

BEGIN
SELECT organization_id, SET_OF_BOOKS_ID
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Operating Unit...';

END;


BEGIN
SELECT cust_trx_type_id,name,gl_id_rev
INTO l_cust_trx_type_id,l_cust_trx_type_name, l_gl_id_rev
FROM ra_cust_trx_types_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id
AND name = 'xxx-Spares-Inv';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Invoice Type...';
END;


BEGIN
select currency_code
into l_currency_code
from fnd_currencies
where currency_code = 'INR';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Currency Code...';
END;


BEGIN
SELECT term_id,name
into l_term_id,l_term_name
FROM ra_terms_tl
WHERE upper(name) = upper('IMMEDIATE');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Terms Name...';
END;


BEGIN
SELECT DISTINCT HCAS.cust_acct_site_id,HCA.cust_account_id
INTO l_address_id,l_customer_id
FROM hz_parties HP
,hz_party_sites HPS
,hz_cust_accounts HCA
,hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCA.party_id = HP.party_id
AND HP.party_id = HPS.party_id
AND HCA.cust_account_id = HCAS.cust_account_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = 'BILL_TO'
AND HCSU.primary_flag = 'Y'
AND upper (ltrim (rtrim (HP.party_name))) = upper (ltrim (rtrim ('Customer Name')))
AND HCAs.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Customer Name...';
END;


INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
CUST_TRX_TYPE_ID,
cust_trx_type_name,
TRX_DATE,
GL_DATE,
CURRENCY_CODE,
term_id,
term_name,
orig_system_bill_customer_id,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
orig_system_bill_address_id,
ORIG_SYSTEM_BILL_ADDRESS_REF,
--orig_system_ship_customer_id,
--orig_system_ship_address_id,
orig_system_sold_customer_id,
QUANTITY,
--unit_selling_price
AMOUNT,
DESCRIPTION,
conversion_type,
conversion_rate,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
org_id
)
Values
(
RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
'Invoice Migration',
'LINE',
l_cust_trx_type_id,
l_cust_trx_type_name,
sysdate,
sysdate,
l_currency_code,
l_term_id,
l_term_name,
l_customer_id,
l_customer_id,
l_address_id,
l_address_id,
--85222,
--87978,
l_customer_id,
1,
--40000
4000,
'Test Invoice2 12JUN08',
'User',
1,
'Invoice Migration',
'RINV_000000002',
l_org_id
);



INSERT INTO ra_interface_distributions_all
(
INTERFACE_LINE_ID
,account_class
,amount
,code_combination_id
,percent
,interface_line_context
,interface_line_attribute1
,org_id
)
VALUES
(
RA_CUSTOMER_TRX_LINES_S.CURRVAL,
'REV'
,4000
,l_gl_id_rev
,100
,'Invoice Migration',
'RINV_000000002',
l_org_id
);

Commit;
END xxx_ar_invoice_api;
/

Employee As Supplier Migration

EMPLOYEE AS SUPPLIER
***************************

Staging Table :-

CREATE TABLE XXX_AP_EMP_SUP_STG
(
VENDOR_NAME VARCHAR2(240),
VENDOR_TYPE VARCHAR2(10),
EMPLOYEE_NAME VARCHAR2(240),
EMPLOYEE_NUMBER NUMBER(10),
VENDOR_SITE VARCHAR2(15),
CITY VARCHAR2(25),
ZIP VARCHAR2(10),
PAYMENT_TERMS VARCHAR2(20),
PAY_GROUP VARCHAR2(10),
LIABILITY_PURCHASE VARCHAR2(40),
PREPAYMENT VARCHAR2(40),
H_VERIFY_FLAG CHAR(1),
L_VERIFY_FLAG CHAR(1 ),
ERROR_MESSAGE VARCHAR2(3000)
)


Run the following script to upload data from staging table to interface table


CREATE OR REPLACE PROCEDURE xxx_EMP_SUP_CREATE_PRC AS

L_ERROR_MESSAGE VARCHAR2(2000);
L_VENDOR_TYPE VARCHAR2(40);
L_TERM_ID NUMBER(10);
L_LOCATION_ID NUMBER(10);
L_VERIFY_FLAG CHAR(1);
L_ORG_ID NUMBER(10);
L_VENDOR_SITE_CODE VARCHAR2(15);
L_PAY_CODE_COMBINATION_ID NUMBER(10);
L_PREPAY_CODE_COMBINATION_ID NUMBER(10);
L_EMP_SUPP_EXIST NUMBER(10);


CURSOR C_EMP IS
SELECT PAPF.PERSON_ID,
TES.VENDOR_TYPE,
TES.EMPLOYEE_NUMBER,
TES.PAYMENT_TERMS,
tES.VENDOR_NAME,
TES.LIABILITY_PURCHASE,
TES.PREPAYMENT,
TES.PAY_GROUP
FROM xxx_AP_EMP_SUP_STG TES,
PER_ALL_PEOPLE_F PAPF
WHERE TO_CHAR(TES.EMPLOYEE_NUMBER) = PAPF.EMPLOYEE_NUMBER
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND NVL(H_VERIFY_FLAG,'N') = 'N';


CURSOR C_EMP_SITE (P_EMPLOYEE_NUMBER VARCHAR2)IS
SELECT *
FROM xxx_AP_EMP_SUP_STG
WHERE EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER ;

BEGIN

<>
FOR C1 IN C_EMP LOOP

L_VERIFY_FLAG := 'Y' ;
L_ERROR_MESSAGE := NULL;
L_EMP_SUPP_EXIST := NULL;

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 := L_ERROR_MESSAGE||'Operating Unit is Invalid';
END;


BEGIN
SELECT LOCATION_ID
INTO L_LOCATION_ID
FROM HR_LOCATIONS
WHERE LOCATION_CODE = 'xxx Main Store Location';
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Location is Not Valid';
END;


BEGIN
SELECT EMPLOYEE_ID
INTO L_EMP_SUPP_EXIST
FROM PO_VENDORS
WHERE VENDOR_TYPE_LOOKUP_CODE = 'EMPLOYEE'
AND EMPLOYEE_ID = C1.PERSON_ID ;
IF L_EMP_SUPP_EXIST > 0 THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := 'SUPPLIER ALREADY EXISTING FOR THIS EMPLOYEE';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


BEGIN
SELECT TERM_ID
INTO L_TERM_ID
FROM AP_TERMS
WHERE UPPER(NAME) = UPPER(TRIM(C1.PAYMENT_TERMS)) ;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Payment Term is not valid';
END;


BEGIN
SELECT LOOKUP_CODE
INTO L_VENDOR_TYPE
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE(+) = 'VENDOR TYPE'
AND UPPER(LOOKUP_CODE) = UPPER(TRIM(C1.VENDOR_TYPE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Vendor Type Lookup Code not existing';
END;


BEGIN
SELECT CODE_COMBINATION_ID
INTO L_PAY_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4||'.'||
SEGMENT5||'.'||SEGMENT6 = C1.LIABILITY_PURCHASE ;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Accounts Pay Code Combination is Not Valid';
END;


BEGIN
SELECT CODE_COMBINATION_ID
INTO L_PREPAY_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4||'.'||
SEGMENT5||'.'||SEGMENT6=C1.PREPAYMENT;
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'Pre-Pay Code Combination is Not Valid';
END;


IF L_VERIFY_FLAG <> 'N' THEN

BEGIN
INSERT INTO AP.AP_SUPPLIERS_INT
(
VENDOR_INTERFACE_ID
,VENDOR_NAME
,VENDOR_NAME_ALT
,VENDOR_TYPE_LOOKUP_CODE
,EMPLOYEE_ID
,TERMS_ID
,BILL_TO_LOCATION_ID
,SHIP_TO_LOCATION_ID
,RECEIVING_ROUTING_ID
,INSPECTION_REQUIRED_FLAG
,RECEIPT_REQUIRED_FLAG
,PAY_GROUP_LOOKUP_CODE
)
VALUES (
AP_SUPPLIERS_INT_S.NEXTVAL
,TRIM(C1.VENDOR_NAME)
,TRIM(C1.VENDOR_NAME)
,L_VENDOR_TYPE
,C1.PERSON_ID
,l_term_id
,l_location_id
,l_location_id
,1
,'N'
,'Y'
,C1.PAY_GROUP
) ;


UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'Y'
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER ;

EXCEPTION

WHEN OTHERS THEN
L_ERROR_MESSAGE := SQLERRM ;
L_VERIFY_FLAG := 'N';
UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER;
GOTO NEXT_SUPP;

END ;


<>
FOR L1 IN C_EMP_SITE(C1.EMPLOYEE_NUMBER)
LOOP

L_VENDOR_SITE_CODE := NULL;



BEGIN
SELECT VENDOR_SITE_CODE
INTO L_VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL A,
PO_VENDORS B
WHERE ORG_ID = L_ORG_ID
AND UPPER(VENDOR_SITE_CODE) = TRIM(UPPER(L1.VENDOR_SITE))
AND A.VENDOR_ID = B.VENDOR_ID
AND UPPER(B.VENDOR_NAME) = TRIM(UPPER(L1.VENDOR_NAME)) ;
IF L_VENDOR_SITE_CODE IS NOT NULL THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE ||'Vendor Site is already existing';
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
END;


IF L_VERIFY_FLAG <> 'N' then

BEGIN

INSERT INTO AP.AP_SUPPLIER_SITES_INT
(
VENDOR_INTERFACE_ID,
VENDOR_SITE_CODE,
VENDOR_SITE_CODE_ALT,
CITY,
ZIP,
ORG_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE10,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
HOLD_UNMATCHED_INVOICES_FLAG,
ACCTS_PAY_CODE_COMBINATION_ID,
PREPAY_CODE_COMBINATION_ID
)
VALUES (
AP_SUPPLIERS_INT_S.CURRVAL,
TRIM(L1.VENDOR_SITE),
TRIM(L1.VENDOR_SITE),
TRIM(L1.CITY),
TRIM(L1.ZIP),
L_ORG_ID,
'Baan Supplier Code',
TRIM(L1.BAAN_CODE),
L_LOCATION_ID,
L_LOCATION_ID,
'N',
L_PAY_CODE_COMBINATION_ID,
L_PREPAY_CODE_COMBINATION_ID
) ;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'Y'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

EXCEPTION
WHEN OTHERS THEN

ROLLBACK TO SAVEPOINT A;

L_ERROR_MESSAGE := SQLERRM;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER;

GOTO NEXT_SUPP;

END;


ELSE

ROLLBACK TO SAVEPOINT A;

UPDATE xxx_AP_EMP_SUP_STG
SET L_VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER
AND VENDOR_SITE = L1.VENDOR_SITE;

UPDATE xxx_AP_EMP_SUP_STG
SET H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = L1.EMPLOYEE_NUMBER;

GOTO NEXT_SUPP;

END IF;

END LOOP SUPP_SITE;


ELSE

UPDATE xxx_AP_EMP_SUP_STG
SET ERROR_MESSAGE = L_ERROR_MESSAGE,
H_VERIFY_FLAG = 'N'
WHERE EMPLOYEE_NUMBER = C1.EMPLOYEE_NUMBER;


END IF;


<>

COMMIT;
END LOOP supp_head;

END xxx_EMP_SUP_CREATE_PRC;
/

Supplier Additional Information Migration

SUPPLIER ADDITIONAL INFORMATION
******************************************

There is no interface tables to upload supplier additional information. Only we can do is directly migrate data into addinfo tables.
Two tables are there in vendor additional info
a) JA_IN_PO_VENDOR_SITES
b) JA_IN_VENDOR_TDS_INFO_HDR

When we create addinfo for vendors, we have to create one null site information for every vendors. To migrate data into vendor addinfo pls follow the following steps

Steps
------

1. Data Template for AddInfo :-

VENDOR_NAME
VENDOR_SITE
EXCISE_DUTY_RANGE
EXCISE_DUTY_DIVISION
EXCISE_DUTY_REG_NO
CST_REG_NO
ST_REG_NO
VAT_REG_NO
SERVICE_TAX_REGNO
PAN_NO TDS_TAX_NAME
TDS_SECTION
TDS_VENDOR_TYPE
VERIFY_FLAG
ERROR_MESSAGE
NVL_SITE_VERIFY_FLAG

2. Create staging table :-

CREATE TABLE XXX_VENDOR_ADDINFO_STG
(
VENDOR_NAME VARCHAR2(100),
VENDOR_SITE VARCHAR2(15),
EXCISE_DUTY_RANGE VARCHAR2(50),
EXCISE_DUTY_DIVISION VARCHAR2(50),
EXCISE_DUTY_REG_NO VARCHAR2(50),
CST_REG_NO VARCHAR2(50),
ST_REG_NO VARCHAR2(50),
VAT_REG_NO VARCHAR2(50),
SERVICE_TAX_REGNO VARCHAR2(50),
PAN_NO VARCHAR2(30),
TDS_TAX_NAME VARCHAR2(150),
TDS_SECTION VARCHAR2(50),
TDS_VENDOR_TYPE VARCHAR2(50),
VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(2500),
NVL_SITE_VERIFY_FLAG CHAR(1)
)

3. Upload data from excel to staging table by Toad, Sql Loder

4. Run the following script to migrate data from staging table to base tables

CREATE OR REPLACE procedure xxx_vendor_addinfo_prc
as
l_verify_flag char(1);
l_error_message varchar2(2500);
l_vendor_id number(10);
l_vendor_site_id number(10);
l_org_id number(10);
l_user_id number(10);
l_vendor_type varchar2(50);
l_tax_id number(10);
l_section_type varchar2(25);
l_section_code varchar2(30);
l_tds_vendor_type varchar2(50);
l_cnt_nvl_site number(3);
l_cnt_site number(3);

cursor c_addinfo is
select *
from xxx_vendor_addinfo_stg
where verify_flag = 'N' ;

begin


for c1 in c_addinfo
loop

l_verify_flag := 'Y';
l_error_message := null;
l_cnt_nvl_site := 0;
l_cnt_site := 0;


BEGIN
Select organization_id
into l_org_id
from hr_operating_units
where name like 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Operating Unit is 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 not Valid...';
END;


BEGIN
select vendor_id,vendor_type_lookup_code
into l_vendor_id, l_vendor_type
from po_vendors
where upper(vendor_name) = trim(upper(c1.vendor_name));
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Name is not valid...';
END;


BEGIN
select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where upper(vendor_site_code) = trim(upper(c1.vendor_site))
and vendor_id = l_vendor_id
and org_id = l_org_id;
EXCEPTION
WHEN OTHERS Then
l_verify_flag := 'N';
l_error_message := l_error_message||'Vendor Site not valid...';
END;


BEGIN
select tax_id
into l_tax_id
from ja_in_tax_codes
where upper(tax_name) = trim(upper(c1.tds_tax_name))
and org_id = l_org_id
and nvl(end_date,sysdate) >= sysdate;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'TDS Tax is not Valid...';
END;


BEGIN
select distinct section_type,
section_code,
vendor_type_lookup_code
into l_section_type,
l_section_code,
l_tds_vendor_type
from JAI_AP_TDS_THHOLD_HDRS
where upper(section_code) = upper(trim(c1.tds_section))
and upper(vendor_type_lookup_code) = upper(trim(c1.tds_vendor_type))
and exception_setup_flag = 'N' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message||'Tds Section or TDS Vendor Type is not valid...';
END;


BEGIN
select count(*)
into l_cnt_site
from JA_IN_PO_VENDOR_SITES
where vendor_site_id = l_vendor_site_id
and vendor_id = l_vendor_id ;
IF l_cnt_site > 0 then
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Site already existing in JA_IN_PO_VENDOR_SITES...';
l_cnt_site := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;

BEGIN
select count(*)
into l_cnt_site
from JA_IN_VENDOR_TDS_INFO_HDR
where vendor_site_id = l_vendor_site_id
and vendor_id = l_vendor_id ;
IF l_cnt_site > 0 then
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Site already existing in JA_IN_VENDOR_TDS_INFO_HDR...';
END IF;
EXCEPTION
WHEN OTHERS THEN
l_cnt_site := 0;
END;


IF l_verify_flag <> 'N' THEN


BEGIN
select count(*)
into l_cnt_nvl_site
from JA_IN_PO_VENDOR_SITES
where vendor_site_id = 0
and vendor_id = l_vendor_id ;
EXCEPTION
WHEN OTHERS THEN
l_cnt_nvl_site := 0;
END;


IF l_cnt_nvl_site = 0 then


BEGIN

insert into JA_IN_PO_VENDOR_SITES
(
vendor_id
,vendor_site_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,excise_duty_range
,excise_duty_division
,excise_duty_reg_no
,cst_reg_no
,st_reg_no
,vat_reg_no
,service_tax_regno
)
values
(
l_vendor_id
,0
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.cst_reg_no)
,trim(c1.st_reg_no)
,trim(c1.vat_reg_no)
,trim(c1.service_tax_regno)
) ;
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM ;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
END;

IF l_verify_flag <> 'N' THEN

BEGIN

insert into JA_IN_VENDOR_TDS_INFO_HDR
(
vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
)
values
(
l_vendor_id
,0
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,''
,''
,'Y'
,l_tds_vendor_type
) ;

UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set nvl_site_verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;

END;

END IF;

END IF;



BEGIN

insert into JA_IN_PO_VENDOR_SITES
(
vendor_id
,vendor_site_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,approved_invoice_flag
,excise_duty_range
,excise_duty_division
,excise_duty_reg_no
,cst_reg_no
,st_reg_no
,vat_reg_no
,service_tax_regno
)
values
(
l_vendor_id
,l_vendor_site_id
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,'Y'
,trim(c1.excise_duty_range)
,trim(c1.excise_duty_division)
,trim(c1.excise_duty_reg_no)
,trim(c1.cst_reg_no)
,trim(c1.st_reg_no)
,trim(c1.vat_reg_no)
,trim(c1.service_tax_regno)
) ;

UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;

END;


BEGIN

insert into JA_IN_VENDOR_TDS_INFO_HDR
(
tax_id
,vendor_id
,vendor_site_id
,pan_no
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,section_type
,section_code
,confirm_pan_flag
,tds_vendor_type_lookup_code
)
values
(
l_tax_id
,l_vendor_id
,l_vendor_site_id
,trim(c1.pan_no)
,sysdate
,l_user_id
,sysdate
,l_user_id
,-1
,l_section_type
,l_section_code
,'Y'
,l_tds_vendor_type
) ;

UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'Y'
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
l_verify_flag := 'N';
UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;

END;



ELSE


UPDATE xxx_vendor_addinfo_stg
set verify_flag = 'N',
error_message = l_error_message
where vendor_name = c1.vendor_name
and vendor_site = c1.vendor_site;


END IF;

commit;

END LOOP;

end xxx_vendor_addinfo_prc;
/