I have a problem here. I have done a request for move order it was stored into a custom table with the format:
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-02(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-03(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-04(Q)2500
System Reads in (O) = Move Order
System Reads in (G) = Org ID
System Reads in (I) = Item ID
System Reads in (M) = Item Code
System Reads in (L) = Lot Number
System Reads in (Q) = Quantity
A cursor has been coded as:
Expand|Select|Wrap|Line Numbers
- /* Process Successful loaded items */
- CURSOR PSUCCESS_CUR (P_MOVE_ID IN NUMBER, P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER,
- P_VEN_LOT IN VARCHAR2) IS
- SELECT
- 'Move Order Transfer' SOURCE_CODE, -- Source Code
- MTRL.HEADER_ID SOURCE_HEADER_ID, -- Source Header ID
- MTRL.LINE_ID SOURCE_LINE_ID, -- Source Line ID
- 1 PROCESS_FLAG, -- Process Flag
- 3 TRANSACTION_MODE, -- Transaction Mode
- MTRL.INVENTORY_ITEM_ID ITEM_ID, -- Inventory Item ID
- MSI.SEGMENT1 ITEM_CODE, -- Item Code
- MTRL.ORGANIZATION_ID ORG_ID, -- Org ID
- MTRL.FROM_SUBINVENTORY_CODE FROM_SUBINV, -- From Subinv
- MOF.LOCATOR_ID FROM_LOCATOR, -- From Locator
- MOF.LOT_NUMBER LOT_NUMBER, -- Lot Number
- MOF.TRANSACTION_QUANTITY TRANSACTION_QUANTITY, -- Trans Qty
- MTRL.REQUIRED_QUANTITY REQUIRED_QUANTITY, --
- MTRL.UOM_CODE UOM, -- UOM
- SYSDATE TRANSACTION_DATE, -- Transaction Date
- MOF.DATE_RECEIVED DATE_RECEIVED,
- MTRL.TRANSACTION_SOURCE_TYPE_ID TRANS_SOURCE_TYPE_ID, -- Trans Source Type ID
- MTRL.TRANSACTION_TYPE_ID TRANS_TYPE_ID, -- Trans Type ID
- MTRL.TO_SUBINVENTORY_CODE TRANS_SUB_INV, -- Trans Sub Inv
- SYSDATE LAST_UPDATE_DATE, -- Last Update Date
- FND_GLOBAL.USER_ID LAST_UPDATED_BY, -- Last Updated By
- SYSDATE CREATED_DATE, -- Created Date
- FND_GLOBAL.USER_ID CREATED_BY, -- Created By
- 'Y' FLOW_SCHEDULE, -- Flow Schedule
- 2 SCHEDULE_FLAG -- Schedule Flag
- FROM
- APPS.MTL_TXN_REQUEST_LINES MTRL,
- APPS.MTL_SYSTEM_ITEMS_B MSI,
- APPS.MTL_ONHAND_QUANTITIES_DETAIL MOF
- -- HISEM.HC_MTL_LOT_ISS_FINAL_TBL HML
- WHERE MTRL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
- AND MTRL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
- AND MTRL.TRANSACTION_TYPE_ID = 64 -- For Move Order Transfer
- AND MTRL.LINE_STATUS NOT IN (5,6) -- Not Cancelled/Closed
- /* Links to Custom Move Order
- AND HML.ITEM_CODE = MSI.SEGMENT1
- AND HML.MOVE_ORD_ID = MTRL.HEADER_ID */
- /* to get the locator information */
- AND MTRL.INVENTORY_ITEM_ID = MOF.INVENTORY_ITEM_ID
- --AND HML.VENDOR_LOT_NUM = MOF.LOT_NUMBER
- AND MOF.IS_CONSIGNED = 2
- AND MOF.SUBINVENTORY_CODE = MTRL.FROM_SUBINVENTORY_CODE
- AND MOF.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
- /* Must Not exists in MTL_Material_Transactions (Prevent Double loading) */
- AND (NOT EXISTS (SELECT NULL
- FROM APPS.MTL_MATERIAL_TRANSACTIONS MMT
- WHERE MMT.SOURCE_CODE = 'Move Order Transfer'
- AND MMT.SOURCE_LINE_ID = MTRL.LINE_ID)
- /* Must Not exists in MTL_Transactions_Interface (Prevent Double loading) */
- AND NOT EXISTS (SELECT NULL
- FROM APPS.MTL_TRANSACTIONS_INTERFACE_BAK MTI -- Later change to MTL_TRANSACTIONS_INTERFACE
- WHERE MTI.SOURCE_CODE = 'Move Order Transfer'
- AND MTI.SOURCE_LINE_ID = MTRL.LINE_ID
- AND MTI.SOURCE_HEADER_ID = MTRL.HEADER_ID)
- )
- /* Parameter Area */
- AND MTRL.HEADER_ID = P_MOVE_ID
- AND MTRL.ORGANIZATION_ID = P_ORG_ID
- AND MTRL.INVENTORY_ITEM_ID = P_ITEM_ID
- AND MOF.LOT_NUMBER = P_VEN_LOT
- ORDER BY
- MTRL.HEADER_ID,
- MSI.ORGANIZATION_ID,
- MSI.INVENTORY_ITEM_ID,
- MSI.SEGMENT1,
- MOF.LOT_NUMBER,
- MOF.DATE_RECEIVED;
However when I load the records run by the cursor, I got
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
data.
The cursor to store the parameters is
Expand|Select|Wrap|Line Numbers
- FOR D IN PSUCCESS_CUR (B.MOVE_ORD_ID, B.ORG_ID, B.ITEM_ID, B.VENDOR_LOT_NUM);
- WHILE V_INT_LOT_QTY < V_SUM_LOT_QTY
- LOOP
- --FETCH PSUCCESS_CUR INTO PSUCCESS_REC;
- V_INT_LOT_QTY := V_INT_LOT_QTY + D.TRANSACTION_QUANTITY;
- SELECT HISEM.HC_MC_ISS_SEQ.NEXTVAL
- INTO V_INTERFACE_ID
- FROM DUAL;
- INSERT INTO MTL_TRANSACTIONS_INTERFACE_BAK -- Later change to MTL_TRANSACTIONS_INTERFACE
- (TRANSACTION_HEADER_ID,
- SOURCE_CODE,
- SOURCE_LINE_ID,
- SOURCE_HEADER_ID,
- PROCESS_FLAG,
- VALIDATION_REQUIRED,
- TRANSACTION_MODE,
- TRANSACTION_INTERFACE_ID,
- INVENTORY_ITEM_ID,
- ITEM_SEGMENT1,
- ORGANIZATION_ID,
- SUBINVENTORY_CODE,
- LOCATOR_ID,
- VENDOR_LOT_NUMBER,
- TRANSACTION_QUANTITY,
- TRANSACTION_UOM,
- TRANSACTION_DATE,
- TRANSACTION_TYPE_ID,
- TRANSFER_ORGANIZATION,
- TRANSFER_SUBINVENTORY,
- LAST_UPDATE_DATE,
- LAST_UPDATED_BY,
- CREATION_DATE,
- CREATED_BY,
- FLOW_SCHEDULE,
- SCHEDULED_FLAG)
- VALUES (
- V_INTERFACE_ID,
- D.SOURCE_CODE,
- D.SOURCE_LINE_ID,
- D.SOURCE_HEADER_ID,
- D.PROCESS_FLAG,
- 1,
- D.TRANSACTION_MODE,
- V_INTERFACE_ID,
- D.ITEM_ID,
- D.ITEM_CODE,
- D.ORG_ID,
- D.FROM_SUBINV,
- D.FROM_LOCATOR,
- D.LOT_NUMBER,
- D.TRANSACTION_QUANTITY,
- D.UOM,
- D.TRANSACTION_DATE,
- D.TRANS_TYPE_ID,
- D.ORG_ID,
- D.TRANS_SUB_INV,
- SYSDATE,
- FND_GLOBAL.USER_ID,
- SYSDATE,
- FND_GLOBAL.USER_ID,
- D.FLOW_SCHEDULE,
- D.SCHEDULE_FLAG);
- INSERT INTO MTL_TRANS_LOTS_INTERFACE_BAK -- Later change to MTL_TRANSACTION_LOTS_INTERFACE
- (TRANSACTION_INTERFACE_ID,
- SOURCE_CODE,
- SOURCE_LINE_ID,
- LOT_NUMBER,
- TRANSACTION_QUANTITY,
- LAST_UPDATE_DATE,
- LAST_UPDATED_BY,
- CREATION_DATE,
- CREATED_BY)
- VALUES
- (V_INTERFACE_ID,
- D.SOURCE_CODE,
- D.SOURCE_LINE_ID,
- D.LOT_NUMBER,
- D.TRANSACTION_QUANTITY,
- SYSDATE,
- FND_GLOBAL.USER_ID,
- SYSDATE,
- FND_GLOBAL.USER_ID);
- END LOOP;
B.MOVE_ORD_ID = 336126
B.ORG_ID = 83
B.ITEM_ID = 21823
B.VENDOR_LOT_NUM =LOT-DISB2P-01
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-02(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-03(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-04(Q)2500
Any help is appreciated.
Thanks in advance.