Oracle Inventory Interface – Misc Receipt for Consignment

Oracle R12 Inventory Module had given a feature for consignment item. In Oracle implementation, on June 2013, there was a customer which had material transaction for consignment item by interface. Which is the interface was, Oracle, as Back End Application, had to received report or query from Front End Application.

Customer had consignment inventory, and Oracle assigned it with owning organization data. The interface was needed to receipt consignment material transaction data, which were given by Front End Application. This requirement could be covered with Oracle Open Interface feature. It has used script to insert data to MTL_TRANSACTIONS_INTERFACE table, and then run Material Transactions Manager.

I have used custom script for Issue Return transaction, which is in Oracle Standard Transaction Type was Miscellaneous Receipt.

These were the script :

/*This Script was tried in Oracle APPS 12.1.3*/

DECLARE
l_trx_type_id NUMBER;
l_trx_act_id NUMBER;
l_trx_source_id NUMBER;
l_org_id NUMBER;
l_trx_type_name VARCHAR2(100);
l_subinventory_code VARCHAR2(10);
l_owning_org_id NUMBER;

BEGIN

l_subinventory_code := 'PH1'; --subinventory code

SELECT UPPER(mtt.transaction_type_name),mtt.transaction_type_id,
       mtt.transaction_action_id,mtt.transaction_source_type_id
  INTO l_trx_type_name,l_trx_type_id,l_trx_act_id,l_trx_source_id
  FROM mtl_transaction_types mtt
 WHERE UPPER(mtt.transaction_type_name) =  'ISSUE RETURN'; 
--this transaction type was define by functional 
--with an action as same as Miscellaneous Receipt

SELECT organization_id 
  INTO l_org_id   
  FROM org_organization_definitions
 WHERE organization_code = 'PIF'; --organization code

SELECT vendor_site_id
  INTO l_owning_org_id
  FROM po_approved_supplier_list
 WHERE vendor_id IN (SELECT vendor_id
                       FROM po_vendors
                      WHERE vendor_name LIKE 'ABETAMA%');
--Vendor name could be replaced based on requirement
                      
INSERT INTO mtl_transactions_interface(
transaction_header_id
,source_code
,source_header_id
,source_line_id
,process_flag
,transaction_mode
,lock_flag
,last_update_date
,last_updated_by
,creation_date
,created_by
,inventory_item_id
,item_segment1
,organization_id
,transaction_quantity
,primary_quantity
,transaction_uom
,transaction_date
,acct_period_id
,subinventory_code
,transaction_source_type_id
,transaction_action_id
,transaction_type_id
,distribution_account_id
,owning_organization_id
,owning_tp_type --Need for Consignment Item
,xfr_owning_organization_id
,final_completion_flag  
)
SELECT 
mtl_material_transactions_s.nextval  --transaction_header_id,
,'INVENTORY'  --source_code,
,-1 --mtl_material_transactions_s.nextval  --source_header_id,
,-1 --mtl_material_transactions_s.nextval  --source_line_id,
,1  --process_flag,
,3  --transaction_mode,
,2  --lock_flag,
,sysdate  --last_update_date,
,1067  --last_updated_by (could filled by user_id)
,sysdate  --creation_date,
,1067  --created_by (could filled by user_id)
,6026  --inventory_item_id,
,'002' --item_segment1 (itemcode)
,l_org_id  --organization_id,
,5  --transaction_quantity,
,5  --primary_quantity,
,'PCS' --transaction_uom_code,
,sysdate  --transaction_date,
,1002  --acct_period_id,
,l_subinventory_code  --subinventory_code,
,l_trx_source_id  --transaction_source_type_id,
,l_trx_act_id  --transaction_action_id,
,l_trx_type_id  --transaction_type_id,
,475214  --distribution_account_id,
,l_owning_org_id  --owning_organization_id, 
,1  --owning_tp_type
,l_org_id  --xfr_owning_organization_id
,'N' --final_completion_flag
FROM dual;
COMMIT;

END;

Thanks. I hope this was useful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s