Inventory Opening Balance Interface (On Hand Qty Interface Part - II An Example )

/* Create a preinterface Table */

CREATE TABLE UETDT.UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)


/* Insert data into Pre-Interface Table
Note : Item is already created , assigned and Item sub inventory also added to item */
INSERT INTO UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (251,
707,
'ELECTRONICS',
'RAR GEMS', -- item already created
'EA',
21, -- Qty
0,
0,
'OUTLET', -- Locator
NULL,
SYSDATE,
SYSDATE - 10,
SYSDATE - 11,
0,
'Y');

COMMIT;


SELECT * FROM UET_INV_OPENING_BALANCES_INT

/* Create Custom Sequences to be used for interface header */

CREATE SEQUENCE APPS.XUET_TRAN_SOURCE_LINE_IDS_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER


CREATE SEQUENCE APPS.XUET_TRAN_INTERFACE_ID_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER

/* Procedure Starts */
----
SET SERVEROUTPUT ON SIZE 1000000;
----

/* Variables Declaration */

DECLARE
l_transaction_interface_id NUMBER;
l_item_count NUMBER := 0;
l_exp_count NUMBER := 0;

CURSOR item_c
IS
SELECT x.item_code old_item_code, x.qty quantity, x.unit_cost COST, x.last_issue_date,
x.last_receipt_date, x.last_supplier_no, x.item_code, x.expiry_date last_exp_date,
x.VALUE, msi.organization_id, msi.restrict_locators_code,
msif.secondary_inventory_name subinventory_code,
get_locator_id(msi.organization_id, msi.inventory_item_id, x.stock_locator) locator_id,
30598 material_account, msi.primary_uom_code, msi.inventory_item_id,
p.default_cost_group_id cost_group_id
FROM uetdt.uet_inv_opening_balances_int x,
mtl_system_items msi,
mtl_secondary_inventories_fk_v msif,
mtl_parameters p
WHERE LTRIM (RTRIM (item_code)) = msi.segment1
AND msi.organization_id = p.organization_id (+)
AND msi.organization_id = 343 --342,343
AND msi.organization_id = msif.organization_id
AND ltrim(rtrim(x.division_CODE))='251'
AND x.inventory_org_code='707'
AND msif.locator_type = 5
AND x.qty > 0;

/* Material account is hard coded which is COGS attached to item. */

/* Main Program */
BEGIN
DBMS_OUTPUT.put_line ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);

FOR r IN item_c
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO l_transaction_interface_id
FROM DUAL;

BEGIN
/* Inserting into Main transaction table */
INSERT INTO mtl_transactions_interface
(source_code, source_line_id,
source_header_id, cost_group_id, process_flag, transaction_mode,
transaction_interface_id, inventory_item_id, organization_id,
subinventory_code, transaction_quantity, transaction_uom,
transaction_date, transaction_source_id, transaction_type_id,
transaction_cost, transaction_reference, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, attribute12, attribute13,
attribute14, attribute15, locator_id
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, r.cost_group_id, 1, 3,
l_transaction_interface_id, r.inventory_item_id, r.organization_id,
r.subinventory_code, TO_NUMBER (r.quantity), r.primary_uom_code,
'25-JUL-2011', r.material_account, 40,
TO_NUMBER (r.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, r.last_exp_date, r.last_issue_date,
r.last_receipt_date, r.last_supplier_no, r.locator_id
);

-- COMMIT;

/* Updates the Process flag to 'Y' */
UPDATE uetdt.uet_inv_opening_balances_int
SET x_ob_imported = 'Y'
WHERE item_code = r.item_code AND qty = r.quantity AND unit_cost = r.COST
AND VALUE = r.VALUE;

-- COMMIT;
l_item_count := l_item_count + 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Exception..');
END;
END LOOP;

DBMS_OUTPUT.put_line ('Number of normal items processed ' || l_item_count);
DBMS_OUTPUT.put_line ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;



SELECT * FROM MTL_TRANSACTIONS_INTERFACE

/* Go to Inventory > Setups > Transaction > Interface Manager
select Material transaction manager > Tools Launch Manager
In case of Error
Inventory > Transactions > Transaction Open Interface Query for ERROR
Select Submit > correct error field
resubmit Material transaction manager */

Possible Errors - Wrong Distribution Account , Invalid Sub inventory , Not a valid Inventory open period found .