Interface to process Issues and Receipts of Items

CREATING A FLAT FILE AS DATA TO BE TRANSFERRED FROM EXTERNAL
=========================================================
WAREHOUSE MANAGEMENT SOFTWARE:
================================

CREATE OR REPLACE PROCEDURE EXT_MNG(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
CURSOR CUR_EXT_MNG IS SELECT WDD.SOURCE_CODE,
WDD.SOURCE_LINE_ID,
WDD.SOURCE_HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.LAST_UPDATED_BY,
MMT.CREATION_DATE,
MMT.CREATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
WSH_DELIVERY_DETAILS WDD,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID=WDD.INVENTORY_ITEM_ID(+)
AND MSI.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MSI.SEGMENT1='SRI';
R_ID UTL_FILE.FILE_TYPE;
BEGIN
R_ID:=UTL_FILE.FOPEN('C:\TEMP','EXT_MGT_SW.TXT','W');
FOR REC_CUR_EXT_MNG IN CUR_EXT_MNG LOOP
UTL_FILE.PUT_LINE(R_ID,REC_CUR_EXT_MNG.SOURCE_CODE || ',' ||
REC_CUR_EXT_MNG.SOURCE_LINE_ID || ',' ||
REC_CUR_EXT_MNG.SOURCE_HEADER_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_MODE || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATE_DATE || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATED_BY || ',' ||
REC_CUR_EXT_MNG.CREATION_DATE || ',' ||
REC_CUR_EXT_MNG.CREATED_BY || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATE_LOGIN || ',' ||
REC_CUR_EXT_MNG.INVENTORY_ITEM_ID || ',' ||
REC_CUR_EXT_MNG.SEGMENT1 ||','||
REC_CUR_EXT_MNG.ORGANIZATION_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_QUANTITY || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_UOM || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_DATE || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_TYPE_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_ACTION_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_TYPE_ID ||','||
REC_CUR_EXT_MNG.TRANSACTION_TYPE_NAME ||','||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_TYPE_NAME ||','||
REC_CUR_EXT_MNG.SECONDARY_INVENTORY_NAME || ',' ||
REC_CUR_EXT_MNG.ACTUAL_COST);
END LOOP;
UTL_FILE.FCLOSE(R_ID);
END EXT_MNG;


===================================================

LOAD DATA INTO STAGING TABLE:
============================

LOAD DATA
INFILE 'E:\RAJASEKHAR\RAJA\PROJECT\ITEM ISSUES AND RECEIPTS\EXT_MGT_SW.TXT'
INSERT INTO TABLE XX_ITEM_ISS_REC
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
TRANSACTION_MODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SEGMENT1,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_TYPE_NAME,
TRANSACTION_SOURCE_TYPE_NAME,
SECONDARY_INVENTORY_NAME,
ACTUAL_COST);
=======================================================

=========================================================================
CREATING STAGING TABLE:
=======================
CREATE TABLE XX_ITEM_ISS_REC
(TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_ID NUMBER,
SOURCE_CODE VARCHAR2(30),
SOURCE_LINE_ID NUMBER,
SOURCE_HEADER_ID NUMBER,
TRANSACTION_MODE NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
INVENTORY_ITEM_ID NUMBER,
SEGMENT1 VARCHAR2(40),
ORGANIZATION_ID NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(3),
TRANSACTION_DATE DATE,
TRANSACTION_SOURCE_ID NUMBER,
TRANSACTION_SOURCE_TYPE_ID NUMBER,
TRANSACTION_ACTION_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_TYPE_NAME VARCHAR2(80),
TRANSACTION_SOURCE_TYPE_NAME VARCHAR2(30),
SECONDARY_INVENTORY_NAME VARCHAR2(10),
ACTUAL_COST NUMBER,
STATUS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(1000));

========================================================================

SELECT MMT.SOURCE_CODE,
MMT.SOURCE_LINE_ID,
OOH.HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.CREATED_BY,
MMT.CREATION_DATE,
MMT.LAST_UPDATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MMT.SOURCE_LINE_ID=OOL.LINE_ID
AND OOH.HEADER_ID=OOL.HEADER_ID
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MMT.SOURCE_CODE IS NOT NULL
AND MMT.SOURCE_LINE_ID='196027'


=====================================================

CREATE OR REPLACE PROCEDURE P1(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
I NUMBER:=1;
CURSOR CUR_STG IS
SELECT MMT.TRANSACTION_ID,
MMT.SOURCE_CODE,
MMT.SOURCE_LINE_ID,
OOH.HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.LAST_UPDATED_BY,
MMT.CREATION_DATE,
MMT.CREATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MMT.SOURCE_LINE_ID=OOL.LINE_ID
AND OOH.HEADER_ID=OOL.HEADER_ID
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MMT.SOURCE_CODE IS NOT NULL
AND MMT.SOURCE_LINE_ID='196027';
-- AND MMT.SOURCE_CODE='RCV'
BEGIN
DELETE XX_ITEM_ISS_REC;
COMMIT;
FOR REC_CUR_STG IN CUR_STG
LOOP
INSERT INTO XX_ITEM_ISS_REC
(TRANSACTION_INTERFACE_ID,
TRANSACTION_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
TRANSACTION_MODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SEGMENT1,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_TYPE_NAME,
TRANSACTION_SOURCE_TYPE_NAME,
SECONDARY_INVENTORY_NAME,
ACTUAL_COST,
STATUS_FLAG)
VALUES
(I,
REC_CUR_STG.TRANSACTION_ID,
REC_CUR_STG.SOURCE_CODE,
REC_CUR_STG.SOURCE_LINE_ID,
REC_CUR_STG.HEADER_ID,
REC_CUR_STG.TRANSACTION_MODE,
REC_CUR_STG.LAST_UPDATE_DATE,
REC_CUR_STG.LAST_UPDATED_BY,
REC_CUR_STG.CREATION_DATE,
REC_CUR_STG.CREATED_BY,
REC_CUR_STG.LAST_UPDATE_LOGIN,
REC_CUR_STG.INVENTORY_ITEM_ID,
REC_CUR_STG.SEGMENT1,
REC_CUR_STG.ORGANIZATION_ID,
REC_CUR_STG.TRANSACTION_QUANTITY,
REC_CUR_STG.TRANSACTION_UOM,
REC_CUR_STG.TRANSACTION_DATE,
REC_CUR_STG.TRANSACTION_SOURCE_ID,
REC_CUR_STG.TRANSACTION_SOURCE_TYPE_ID,
REC_CUR_STG.TRANSACTION_ACTION_ID,
REC_CUR_STG.TRANSACTION_TYPE_ID,
REC_CUR_STG.TRANSACTION_TYPE_NAME,
REC_CUR_STG.TRANSACTION_SOURCE_TYPE_NAME,
REC_CUR_STG.SECONDARY_INVENTORY_NAME,
REC_CUR_STG.ACTUAL_COST,
'N');
I:=I+1;
COMMIT;
END LOOP;
COMMIT;
END P1;


==============================================================================

CREATE OR REPLACE PROCEDURE P2(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
L_TRANSACTION_ID MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE;
L_SOURCE_CODE MTL_MATERIAL_TRANSACTIONS.SOURCE_CODE%TYPE;
L_SOURCE_LINE_ID MTL_MATERIAL_TRANSACTIONS.SOURCE_LINE_ID%TYPE;
L_SOURCE_HEADER_ID OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE;
L_TRANSACTION_MODE MTL_MATERIAL_TRANSACTIONS.TRANSACTION_MODE%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_TRANSACTION_UOM MTL_MATERIAL_TRANSACTIONS.TRANSACTION_UOM%TYPE;
L_TRANSACTION_SOURCE_ID MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID%TYPE;
L_TRANSACTION_SOURCE_TYPE_ID MTL_TXN_SOURCE_TYPES.TRANSACTION_SOURCE_TYPE_ID%TYPE;
L_TRANSACTION_ACTION_ID MTL_TRANSACTION_TYPES.TRANSACTION_ACTION_ID%TYPE;
L_TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_ID%TYPE;
L_TRANSACTION_TYPE_NAME MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_NAME%TYPE;
L_TRANSACTION_SOURCE_TYPE_NAME MTL_TXN_SOURCE_TYPES.TRANSACTION_SOURCE_TYPE_NAME%TYPE;
L_SECONDARY_INVENTORY_NAME MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE;
L_ORGANIZATION_ID1 MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VAL_ISS_RCP
IS
SELECT A.*,ROWID
FROM XX_ITEM_ISS_REC A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VAL_ISS_RCP IN CUR_VAL_ISS_RCP
LOOP
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
BEGIN
SELECT SOURCE_CODE
INTO L_SOURCE_CODE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE SOURCE_CODE=REC_CUR_VAL_ISS_RCP.SOURCE_CODE
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_CODE=L_SOURCE_CODE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE CODE DOES NOT EXIST';
END;
BEGIN
SELECT SOURCE_LINE_ID
INTO L_SOURCE_LINE_ID
FROM MTL_MATERIAL_TRANSACTIONS
WHERE SOURCE_LINE_ID=REC_CUR_VAL_ISS_RCP.SOURCE_LINE_ID
AND SOURCE_CODE=REC_CUR_VAL_ISS_RCP.SOURCE_CODE;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_LINE_ID=L_SOURCE_LINE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE LINE ID DOES NOT EXIST';
END;
BEGIN
SELECT HEADER_ID
INTO L_SOURCE_HEADER_ID
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID=REC_CUR_VAL_ISS_RCP.SOURCE_HEADER_ID;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_HEADER_ID=L_SOURCE_HEADER_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE LINE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_MODE
INTO L_TRANSACTION_MODE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_MODE=REC_CUR_VAL_ISS_RCP.TRANSACTION_MODE
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_MODE=L_TRANSACTION_MODE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION MODE DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID =REC_CUR_VAL_ISS_RCP.ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR ORGANIZATION ID';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_VAL_ISS_RCP.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT SEGMENT1
INTO L_SEGMENT1
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1=REC_CUR_VAL_ISS_RCP.SEGMENT1
AND INVENTORY_ITEM_ID=REC_CUR_VAL_ISS_RCP.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET SEGMENT1=L_SEGMENT1
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SEGMENT1 DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_UOM
INTO L_TRANSACTION_UOM
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_UOM=REC_CUR_VAL_ISS_RCP.TRANSACTION_UOM
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_UOM=L_TRANSACTION_UOM
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION UOM DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_ID
INTO L_TRANSACTION_SOURCE_ID
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_ID
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_ID=L_TRANSACTION_SOURCE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_TYPE_ID
INTO L_TRANSACTION_SOURCE_TYPE_ID
FROM MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_TYPE_ID=L_TRANSACTION_SOURCE_TYPE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE TYPE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_ACTION_ID
INTO L_TRANSACTION_ACTION_ID
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_ACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ACTION_ID
AND TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_ACTION_ID=L_TRANSACTION_ACTION_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION ACTION ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_TYPE_ID
INTO L_TRANSACTION_TYPE_ID
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_TYPE_ID=L_TRANSACTION_TYPE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION TYPE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_TYPE_NAME
INTO L_TRANSACTION_TYPE_NAME
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_NAME=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_NAME
AND TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_TYPE_NAME=L_TRANSACTION_TYPE_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION TYPE NAME DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_TYPE_NAME
INTO L_TRANSACTION_SOURCE_TYPE_NAME
FROM MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_NAME=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_TYPE_NAME;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_TYPE_NAME=L_TRANSACTION_SOURCE_TYPE_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE TYPE NAME DOES NOT EXIST';
END;
BEGIN
SELECT SECONDARY_INVENTORY_NAME
INTO L_SECONDARY_INVENTORY_NAME
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME=REC_CUR_VAL_ISS_RCP.SECONDARY_INVENTORY_NAME
AND ORGANIZATION_ID = REC_CUR_VAL_ISS_RCP.ORGANIZATION_ID ;
UPDATE XX_ITEM_ISS_REC
SET SECONDARY_INVENTORY_NAME=L_SECONDARY_INVENTORY_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SECONDARY INVENTORY NAME DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
ELSE
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
END IF;
END LOOP;
END P2;

=============================================================================
XX_ISS_RCP_LOAD
=============================================================================

CREATE OR REPLACE PROCEDURE P3(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
L_CREATED_BY NUMBER;
L_UPDATED_BY NUMBER;
L_CREATION_DATE DATE := SYSDATE;
L_LAST_UPDATE_DATE DATE := SYSDATE;
L_USER NUMBER;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_REC NUMBER := 0;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_ISS_RCP_LOAD
IS
SELECT ROWID,A.*
FROM XX_ITEM_ISS_REC A
WHERE STATUS_FLAG='P';
BEGIN
SELECT USER_ID
INTO L_USER
FROM FND_USER
WHERE UPPER (USER_NAME) = 'OPERATIONS';
DELETE MTL_TRANSACTIONS_INTERFACE;
COMMIT;
FOR REC_CUR_ISS_RCP_LOAD IN CUR_ISS_RCP_LOAD
LOOP
L_ERROR_MESSAGE :=NULL;
BEGIN
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,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
SUBINVENTORY_CODE,
REQUIRED_FLAG
)
VALUES
( REC_CUR_ISS_RCP_LOAD.SOURCE_CODE,
OE_ORDER_LINES_S.NEXTVAL,
OE_ORDER_HEADERS_S.NEXTVAL,
'1',
'3',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
'-1',
REC_CUR_ISS_RCP_LOAD.INVENTORY_ITEM_ID,
REC_CUR_ISS_RCP_LOAD.ORGANIZATION_ID,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_QUANTITY,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_UOM,
SYSDATE,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_TYPE_ID,
NULL,
REC_CUR_ISS_RCP_LOAD.SECONDARY_INVENTORY_NAME,
'Y'
);
EXCEPTION
WHEN OTHERS
THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE || ' LOAD ERROR' || '-' || SQLERRM;
END;
IF L_ERROR_MESSAGE IS NOT NULL
THEN
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG = 'E',
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_ISS_RCP_LOAD.ROWID;
ELSE
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG='P'
WHERE ROWID=REC_CUR_ISS_RCP_LOAD.ROWID;
END IF;
COMMIT;
L_REC:=L_REC+1;
END LOOP;
END P3;

========================================================================

Go to

setup - Transactions - Interface Managers

from Tools menu - click "Launch Manager"
then automatically import program runs.