Friday, July 25, 2008

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

11 comments:

Anonymous said...

Nice blog, I really like it. Thanks for the information. So from the looks of it, your sub inventory code is the same as the organization id (or same concept anyway, not same id/code value). Is this typical? What if sub inventory is broken down per org. Where is this concept stored/configured in EBS?

Anonymous said...

How does the transaction header id get populated if you want to run a batch of transactions through at once/concurrently? Is there a common sequence that should be referenced? If so, what is the name of this sequence?

wl52972k said...

Hi,

if we just want to load Serial Number to oracle application. how can we achieve it?? Looking forward ur reply!Thanks!

Kalyan Reddy said...

Hi Snatosh,


Error_code: Transaction processs error
Error_Explanation: Invalid status in STD_GRP
in MTL_TRANSACTIONS_INTERFACE while load on hand quantities


Can you please give me an about this if you have any idea about this.....?

Thanks in Advance

Scott Thomson said...

Thanks for this blog Santosh!

Your code has saved me days worth of effort to complete a similar task of loading open inventory balances for lotted and non-lotted products.

مع نفسى said...

Hello ,, thanks so much for the very usiful code ,,
i had an error but i didn't know how to see it ,, i don't know how to follow ur path to see interfce manager's erroes , may you or some one calify please

Anonymous said...

can u please give xxx_tab_onhand_stg table column details.

Eripps said...

Hi Snatosh,

How are you? I hope fine. I have an issue similar to Kalyan Reddy has, the same error:

Error_code: Transaction processs error
Error_Explanation: Invalid status in STD_GRP

Could you please advice?

Thanks in advance...

Samrat said...

can you provide the script for customer detail

Prasanta said...

Nice blogs. Its very useful as well as effective.

Can you please post some blogs for Work Flow customization?

Prasanta

Anonymous said...

Hi
can u please give xxx_tab_onhand_stg table column details and also the control file to load into the
xxx_tab_onhand_stg table from the excel sheet.

You can send it to my emailid below.
gvishnu24@gmail.com

Thanks for your help!!!

Vishnu