Inventory Opening Balance Interface (On Hand Qty Interface) Continue to Item Create

--- Create a Temp Table
-- Upload data from user xls to temp table
-- run a cursor to plae data from temp table
REM +=======================================================================+
REM | |
REM | |
REM +=======================================================================+
REM | FILENAME |
REM | |
REM | |
REM | DESCRIPTION |
REM | This script has been created to Uplaod Inventory opening balances |
REM | from legacy system to Oracle 11i/R12 inventory |
REM | |
REM | 1. Update User XLS data into an TEMP table |
REM | 2. Run a cursor to move data from temp table to pre int table |
REM | 3. Run a plsql block to migrate data from |
REM | pre_interface_table to oracle std interface table |
REM | 4. Launch Transaction manager from Inventory |
REM +=======================================================================+



CREATE TABLE APPS.AEO_ITM_LOAD_TMP
(
PART_CODE VARCHAR2(240 BYTE),
DIVISION VARCHAR2(3 BYTE),
PARTS_NAME VARCHAR2(240 BYTE),
STOCK_RECD NUMBER,
BALANCE NUMBER,
ISSUE_DATE DATE,
UNIT_PRICE NUMBER,
BRAND VARCHAR2(240 BYTE),
PRODUCT VARCHAR2(240 BYTE),
MODEL_NO VARCHAR2(240 BYTE)
)

COMMIT;

-- Onhand 22 @ rate of 50 , and sysdate-50 is last issue date

INSERT INTO AEO_ITM_LOAD_TMP
VALUES ('RARDE00093',
777,
'RAR Switch - Ignition',
22,
22,
SYSDATE - 50,
50,
'Daewoo ',
NULL,
NULL)

SELECT * FROM APPS.AEO_ITM_LOAD_TMP

------------------- Upload XLS data in Above created Table ------------------------

-- Create a pre interface table

CREATE TABLE 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)
)


DELETE FROM UETDT.UET_INV_OPENING_BALANCES_INT

COMMIT;

-- Move data from TMP table to pre interface table with validations

DECLARE
N NUMBER;

CURSOR C1
IS
SELECT * FROM AEO_ITM_LOAD_TMP;
BEGIN
FOR REC IN C1
LOOP
INSERT INTO UETDT.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 (REC.DIVISION,
426, ----- INVENTORY_ORG_CODE
'ELECTRONIC',
REC.PART_CODE,
'PCE',
REC.BALANCE,
REC.UNIT_PRICE,
REC.BALANCE * REC.UNIT_PRICE,
'SPARES',
NULL,
NULL,
REC.ISSUE_DATE,
REC.ISSUE_DATE,
REC.BRAND,
'N');

COMMIT;

dbms_output.put_line('Started populating Inventory Opening Balances at TEMP Table : '|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));

END LOOP;

END;


SELECT * FROM UETDT.UET_INV_OPENING_BALANCES_INT

------------------------ Populating Inventory Opening Balances -------------------------------

-- Material acount is Receipt account not COGS

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,
53322 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 = 426 --342,343
AND MSI.ORGANIZATION_ID = MSIF.ORGANIZATION_ID
AND LTRIM(RTRIM(X.DIVISION_CODE))='777'
AND X.INVENTORY_ORG_CODE='426'
AND MSIF.LOCATOR_TYPE = 5
AND X.QTY > 0
AND X.ITEM_CODE !='15AMPS-PLUGS';

-- 46789 COGS
/* 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,
'02-AUG-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;


COMMIT;

SELECT * FROM MTL_TRANSACTIONS_INTERFACE WHERE SOURCE_CODE = '1'


Go to inventory Setup > Transactions > Interface Managers > Run Material transaction ( Launch )