Mtl Material Transaction Interface

                              ------Inventory Transaction Interface-------
Step 1.
CREATE  TABLE XXINV_STG1(
SUBINVENTORY_CODE VARCHAR2(10)
,TRANSACTION_DATE DATE
,TRANSACTION_QUANTITY NUMBER
,TRANSACTION_UOM VARCHAR2(3)
,PRIMARY_QUANTITY NUMBER
,SOURCE_CODE VARCHAR2(30)
,SOURCE_LINE_ID NUMBER
,source_header_id NUMBER
,process_flag NUMBER(1)
,lock_flag  NUMBER(1)
,RCV_TRANSACTION_ID NUMBER
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,CREATION_DATE DATE
,CREATED_BY NUMBER
,INVENTORY_ITEM_ID NUMBER
,ORGANIZATION_ID NUMBER
,TRANSACTION_TYPE_ID NUMBER
,TRANSACTION_ACTION_ID NUMBER
,TRANSACTION_SOURCE_TYPE_ID NUMBER
,TRANSACTION_MODE NUMBER);

Load data Into Custom Table From Flat File Using Sql Loader Or Webadi Concept

Step 2. Data File
Heading
SUBINVENTORY_CODE,TRANSACTION_DATE,TRANSACTION_QUANTITY,TRANSACTION_UOM,PRIMARY_QUANTITY,SOURCE_CODE,SOURCE_LINE_ID,source_header_id,process_flag,lock_flag,RCV_TRANSACTION_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,INVENTORY_ITEM_ID,ORGANIZATION_ID,TRANSACTION_TYPE_ID,TRANSACTION_ACTION_ID,TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_MODE
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3
Stores, ,-7,Ea,100,Inventory,90,60,1,2,,,1013497,,1013497,222981,204,32,27,13,3

Step 3. Flat File

OPTIONS(SKIP=1)
LOAD DATA
INFILE '/u01/E-BIZ/apps/apps_st/appl/inv/12.0.0/bin/XXORC_Staging_Data_File.txt'
TRUNCATE
INTO TABLE XXORC_Inv_Staging_Tab
FIELDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
(
SUBINVENTORY_CODE
,TRANSACTION_DATE  "SYSDATE"
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,LAST_UPDATE_DATE "SYSDATE"
,LAST_UPDATED_BY
,CREATION_DATE "SYSDATE"
,CREATED_BY
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE)

Step 4. Validation

CREATE OR REPLACE procedure APPS.XXORC_Proc_Stage_Int_Valid(p_errbuff out varchar2,p_retcode out number)
as
cursor XXC is
SELECT SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag ---MANDATORY
,lock_flag ---MADATORY
,RCV_TRANSACTION_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE---MANDATORY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
from XXORC_Inv_Staging_Tab;
l_inv_id number;
l_org_id number;
l_sub_inv_code varchar2(10);
l_process_flag number;
l_uom varchar2(10);
l_transaction_type_id number;
l_error_msg varchar2(50);
l_flag varchar2(50) default 'A';
begin
for XOO in XXC
loop
---start transaction date validation---
if XOO.transaction_date>sysdate then
l_flag :='E';
l_error_msg :='Transaction Date Invalid';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
--- end of transaction date---
-- start SOURCE_LINE_ID validation---
if XOO.SOURCE_LINE_ID is null then
l_flag :='E';
l_error_msg :='Source Line Id Not Be Null';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
-- end of SOURCE_LINE_ID validation---
-- start SOURCE_LINE_ID validation---
if XOO.source_header_id is null then
l_flag :='E';
l_error_msg :='Source Header Id Not Be Null';
fnd_file.put_line(fnd_file.log,'Error Raised'  ||l_error_msg);
end if;
-- end of SOURCE_LINE_ID validation---
----start inventory_item_id validation-----
begin
select inventory_item_id
into l_inv_id from
mtl_system_items_b
where inventory_item_id=XOO.INVENTORY_ITEM_ID;
exception
when others then
l_flag :='E';
l_error_msg :='inventory Item Id Is invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
---end of inventory_item_id validation-----
---start Unit Of Measure Validation--
begin
select UOM_CODE
into l_uom
from MTL_UNITS_OF_MEASURE_TL
where UOM_CODE=XOO.TRANSACTION_UOM;
exception
when others then
l_flag :='E';
l_error_msg :='UOM Is invalid';
fnd_file.put_line(fnd_file.log,'Error Occured '||l_error_msg);
end;
-- start organization validation----
begin
select organization_id
into l_org_id
from org_organization_definitions
where organization_id=XOO.ORGANIZATION_ID;
exception
when others then
l_flag :='E';
l_error_msg :='organization data invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
----end organization validation---
 --start sub inventory code validation---
begin
select  distinct(SUBINVENTORY_CODE)
into l_sub_inv_code
from mtl_onhand_quantities_detail
where SUBINVENTORY_CODE=XOO.SUBINVENTORY_CODE;
exception
when others then
l_flag :='E';
l_error_msg :='Sub Inventory Code Invalid';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
---end of subinventory code validation----
--- start process flag validation---
---end of process flag validations---
--start transaction type id validation---
begin
select transaction_type_id
into l_transaction_type_id
from mtl_transaction_types
where transaction_type_id=XOO.transaction_type_id;
exception
when others then
l_flag:='E';
l_error_msg:='Transaction type Id not matched';
fnd_file.put_line(fnd_file.log,'Error Occured'||l_error_msg);
end;
-- end of transaction type id validation----
if l_flag !='E' then
insert into
mtl_transactions_interface
(TRANSACTION_HEADER_ID
,SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY)
values
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
,l_sub_inv_code
,XOO.TRANSACTION_DATE
,XOO.TRANSACTION_QUANTITY
,l_uom
,XOO.PRIMARY_QUANTITY
,XOO.SOURCE_CODE
,XOO.SOURCE_LINE_ID
,XOO.source_header_id
,XOO.process_flag
,XOO.lock_flag
,XOO.RCV_TRANSACTION_ID
,l_inv_id
,l_org_id
,l_transaction_type_id
,XOO.TRANSACTION_ACTION_ID
,XOO.TRANSACTION_SOURCE_TYPE_ID
,XOO.TRANSACTION_MODE
,XOO.LAST_UPDATE_DATE
,XOO.LAST_UPDATED_BY
,XOO.CREATION_DATE
,XOO.CREATED_BY);
commit;
end if;
end loop;
end;

Step 5. Submit Standard Program

Go To Inventory Module Click Clt+L Then Type Lauch interface Manager
after Click Tool menu Click lauch manager
                        OR
Submit Standard Program Process transaction interface after Successful complete of Standard Program
another standard Program will execute Mtl Work load Program act as a temporary Table  Move From one interface To base table.

Other Method Single Insertion concept :

insert into mtl_transactions_interface
(SUBINVENTORY_CODE
,TRANSACTION_DATE
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,PRIMARY_QUANTITY
,SOURCE_CODE
,SOURCE_LINE_ID
,source_header_id
,process_flag
,lock_flag
,RCV_TRANSACTION_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_ACTION_ID
,TRANSACTION_SOURCE_TYPE_ID
,TRANSACTION_MODE)
values
('Mobile B',
SYSDATE,
-20,
'Ea',
100,
'Inventory',
63,
26,
1,
2,
NULL,
SYSDATE,
1013497,
SYSDATE,
1013497,
222976,
204,
32,
27,
13,
3)

commit;

select * from mtl_transactions_interface

delete from mtl_transaction_interface where

select * from mtl_material_transactions where inventory_item_id=222976

delete from mtl_transactions_interface where source_code like 'Inv%'



Any Queries Mail Me vivekexpertize@gmail.com