I had to test order import process and i needed to create multiple orders for multiple items. In our test system there was not enough item quantity available, so i used following script to add item qunatity as 'Miscellaneous receipt'. This script worked fine for me. All the records added to material transaction interface, will get picked by next execution of concurrent program 'Process transaction interface'. If this program is not scheduled to run then it can be executed using 'Inventory' Responsibility and select Setup -> Transaction Managers. Select 'Material transaction' and then hit Tools -> Launch Manager. It will start 'Process transaction interface'.
DECLARE
--
l_code_loc VARCHAR2(90);
l_org_id MTL_SYSTEM_ITEMS_B.organization_id % TYPE;
l_locator_id MTL_ITEM_LOCATIONS.inventory_location_id % TYPE;
l_trx_type_id MTL_TRANSACTION_TYPES.transaction_type_id % TYPE;
l_gl_acct MTL_SECONDARY_INVENTORIES.material_account % TYPE;
l_sub_inventory MTL_SECONDARY_INVENTORIES.secondary_inventory_name % TYPE;
l_sub_inv MTL_SECONDARY_INVENTORIES.secondary_inventory_name % TYPE;
--
-- This cursor get list of item for qunatity addition. This
-- query could be modified to select just a single item too
--
CURSOR c_items IS
SELECT inventory_item_id, segment1 , segment2 , segment3
FROM mtl_system_items_b a
, vvinv_styles b
WHERE a.segment1 LIKE &seq1
AND segment2 LIKE &seg2
AND segment3 LIKE &seg3
AND a.default_shipping_org = l_org_id
AND a.organization_id = l_org_id
AND a.customer_order_enabled_flag = 'Y'
AND a.segment1 = b.style_code
AND b.advanced_order_flag = 'N'
AND ROWNUM = 1
;
BEGIN
l_org_id := &org_id ;
l_sub_inv := &sub_inv;
--
--
l_code_loc := 'Get transaction type id';
SELECT transaction_type_id
INTO l_trx_type_id
FROM mtl_transaction_types
WHERE transaction_type_name = 'Miscellaneous receipt'
;
--
-- Get inventory name and account
--
l_code_loc := 'Get inventory name';
SELECT secondary_inventory_name, material_account
INTO l_sub_inventory
, l_gl_acct
FROM MTL_SECONDARY_INVENTORIES
WHERE secondary_inventory_name = l_sub_inv
;
--
-- Get Item location
--
l_code_loc := 'Get item location';
SELECT inventory_location_id
INTO l_locator_id
FROM mtl_item_locations
WHERE subinventory_code = l_sub_inventory
AND disable_date IS NULL
AND enabled_flag = 'Y'
AND ROWNUM = 1
;
--
-- Loop through item cursor and add record for each item
--
l_code_loc := 'For Loop';
FOR l_rec IN c_items LOOP
INSERT INTO mtl_transactions_interface
( source_code
, source_line_id
, source_header_id
, process_flag
, transaction_mode
, last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_item_id
, organization_id
, transaction_quantity
, transaction_uom
, transaction_date
, subinventory_code
, locator_id
, transaction_type_id
, distribution_account_id
)
VALUES ( 'Manual' --SOURCE_CODE,
, 11 --SOURCE_LINE_ID,
, 11 --SOURCE_HEADER_ID,
, 1 --PROCESS_FLAG,
, 3 --TRANSACTION_MODE,
, SYSDATE --LAST_UPDATE_DATE,
, -1 --LAST_UPDATED_BY,
, SYSDATE --CREATION_DATE,
, -1 --CREATED_BY,
, l_rec.inventory_item_id --INVENTORY_ITEM_ID,
, l_org_id --ORGANIZATION_ID,
, 25 --TRANSACTION_QUANTITY,
, 'EA' --TRANSACTION_UOM,
, SYSDATE --TRANSACTION_DATE,
, l_sub_inventory --SUBINVENTORY_CODE,
, l_locator_id --LOCATOR_ID,
, l_trx_type_id --TRANSACTION_TYPE_ID
, l_gl_acct
) ;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Encountered at ' || l_code_loc);
dbms_output.put_line('Error :'||SQLERRM);
END;
Posts
