473,320 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Loop problem, Cursor could not read the next record

1
Hi All,

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
  1. /* Process Successful loaded items */
  2. CURSOR PSUCCESS_CUR (P_MOVE_ID IN NUMBER, P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER, 
  3.                      P_VEN_LOT IN VARCHAR2) IS
  4.   SELECT  
  5.           'Move Order Transfer'               SOURCE_CODE,                  -- Source Code
  6.              MTRL.HEADER_ID                      SOURCE_HEADER_ID,             -- Source Header ID
  7.                 MTRL.LINE_ID                        SOURCE_LINE_ID,               -- Source Line ID
  8.                 1                                   PROCESS_FLAG,                 -- Process Flag
  9.                 3                                   TRANSACTION_MODE,             -- Transaction Mode
  10.                 MTRL.INVENTORY_ITEM_ID              ITEM_ID,                      -- Inventory Item ID
  11.                 MSI.SEGMENT1                        ITEM_CODE,                    -- Item Code
  12.                 MTRL.ORGANIZATION_ID                ORG_ID,                       -- Org ID
  13.                 MTRL.FROM_SUBINVENTORY_CODE         FROM_SUBINV,                  -- From Subinv
  14.        MOF.LOCATOR_ID                      FROM_LOCATOR,                 -- From Locator
  15.           MOF.LOT_NUMBER                      LOT_NUMBER,                   -- Lot Number
  16.                 MOF.TRANSACTION_QUANTITY            TRANSACTION_QUANTITY,         -- Trans Qty
  17.                 MTRL.REQUIRED_QUANTITY              REQUIRED_QUANTITY,            --                
  18.                 MTRL.UOM_CODE                       UOM,                          -- UOM
  19.                 SYSDATE                             TRANSACTION_DATE,             -- Transaction Date
  20.                 MOF.DATE_RECEIVED                   DATE_RECEIVED,                         
  21.                 MTRL.TRANSACTION_SOURCE_TYPE_ID     TRANS_SOURCE_TYPE_ID,         -- Trans Source Type ID
  22.                 MTRL.TRANSACTION_TYPE_ID            TRANS_TYPE_ID,                -- Trans Type ID
  23.                 MTRL.TO_SUBINVENTORY_CODE           TRANS_SUB_INV,                -- Trans Sub Inv
  24.                 SYSDATE                             LAST_UPDATE_DATE,             -- Last Update Date
  25.        FND_GLOBAL.USER_ID                  LAST_UPDATED_BY,              -- Last Updated By
  26.        SYSDATE                             CREATED_DATE,                 -- Created Date
  27.        FND_GLOBAL.USER_ID                  CREATED_BY,                   -- Created By
  28.                 'Y'                                 FLOW_SCHEDULE,                -- Flow Schedule
  29.                  2                                  SCHEDULE_FLAG                 -- Schedule Flag
  30.         FROM
  31.           APPS.MTL_TXN_REQUEST_LINES          MTRL,
  32.                 APPS.MTL_SYSTEM_ITEMS_B             MSI,
  33.                 APPS.MTL_ONHAND_QUANTITIES_DETAIL   MOF  
  34. --                HISEM.HC_MTL_LOT_ISS_FINAL_TBL      HML 
  35.         WHERE  MTRL.INVENTORY_ITEM_ID   = MSI.INVENTORY_ITEM_ID
  36.         AND    MTRL.ORGANIZATION_ID     = MSI.ORGANIZATION_ID
  37.         AND    MTRL.TRANSACTION_TYPE_ID = 64 -- For Move Order Transfer
  38.         AND    MTRL.LINE_STATUS NOT IN (5,6) -- Not Cancelled/Closed
  39.         /* Links to Custom Move Order  
  40.   AND    HML.ITEM_CODE            = MSI.SEGMENT1
  41.         AND    HML.MOVE_ORD_ID          = MTRL.HEADER_ID */
  42.         /* to get the locator information */
  43.   AND    MTRL.INVENTORY_ITEM_ID   = MOF.INVENTORY_ITEM_ID 
  44. --AND    HML.VENDOR_LOT_NUM       = MOF.LOT_NUMBER 
  45.   AND    MOF.IS_CONSIGNED         = 2 
  46.   AND    MOF.SUBINVENTORY_CODE    = MTRL.FROM_SUBINVENTORY_CODE
  47.         AND    MOF.ORGANIZATION_ID      = MTRL.ORGANIZATION_ID
  48.         /* Must Not exists in MTL_Material_Transactions (Prevent Double loading) */ 
  49.         AND   (NOT EXISTS (SELECT NULL
  50.                            FROM   APPS.MTL_MATERIAL_TRANSACTIONS   MMT
  51.                                                                                     WHERE  MMT.SOURCE_CODE      = 'Move Order Transfer'
  52.                                                                                     AND    MMT.SOURCE_LINE_ID   = MTRL.LINE_ID) 
  53.   /* Must Not exists in MTL_Transactions_Interface (Prevent Double loading) */  
  54.         AND    NOT EXISTS (SELECT NULL
  55.                            FROM   APPS.MTL_TRANSACTIONS_INTERFACE_BAK  MTI  -- Later change to MTL_TRANSACTIONS_INTERFACE 
  56.                                                                                     WHERE  MTI.SOURCE_CODE      = 'Move Order Transfer'
  57.                                                                                     AND    MTI.SOURCE_LINE_ID   = MTRL.LINE_ID
  58.                                                                                     AND    MTI.SOURCE_HEADER_ID = MTRL.HEADER_ID)
  59.                                  ) 
  60.         /* Parameter Area */
  61.         AND MTRL.HEADER_ID         = P_MOVE_ID  
  62.         AND MTRL.ORGANIZATION_ID   = P_ORG_ID
  63.         AND MTRL.INVENTORY_ITEM_ID = P_ITEM_ID
  64.         AND MOF.LOT_NUMBER         = P_VEN_LOT     
  65.         ORDER BY
  66.           MTRL.HEADER_ID,
  67.                 MSI.ORGANIZATION_ID,
  68.                 MSI.INVENTORY_ITEM_ID,
  69.                 MSI.SEGMENT1,
  70.           MOF.LOT_NUMBER,                                                                         
  71.           MOF.DATE_RECEIVED;
  72.  
When I run the script step-by-step it display all the record records that I want.
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
  1.       FOR D IN PSUCCESS_CUR (B.MOVE_ORD_ID, B.ORG_ID, B.ITEM_ID, B.VENDOR_LOT_NUM);
  2.               WHILE V_INT_LOT_QTY < V_SUM_LOT_QTY     
  3.                           LOOP 
  4.                         --FETCH PSUCCESS_CUR INTO PSUCCESS_REC;
  5.  
  6.            V_INT_LOT_QTY := V_INT_LOT_QTY + D.TRANSACTION_QUANTITY;
  7.  
  8.  
  9.                                    SELECT HISEM.HC_MC_ISS_SEQ.NEXTVAL
  10.                                    INTO   V_INTERFACE_ID
  11.                                       FROM   DUAL;
  12.  
  13.                                    INSERT INTO MTL_TRANSACTIONS_INTERFACE_BAK  -- Later change to MTL_TRANSACTIONS_INTERFACE 
  14.                                        (TRANSACTION_HEADER_ID,
  15.                                      SOURCE_CODE,
  16.                                                     SOURCE_LINE_ID,
  17.                                            SOURCE_HEADER_ID,
  18.                                            PROCESS_FLAG,
  19.                                            VALIDATION_REQUIRED,
  20.                                            TRANSACTION_MODE,
  21.                                            TRANSACTION_INTERFACE_ID,
  22.                                            INVENTORY_ITEM_ID,
  23.                                                     ITEM_SEGMENT1,
  24.                                            ORGANIZATION_ID,
  25.                                            SUBINVENTORY_CODE,
  26.                                            LOCATOR_ID,
  27.                                                     VENDOR_LOT_NUMBER,
  28.                                            TRANSACTION_QUANTITY,
  29.                                            TRANSACTION_UOM,
  30.                                            TRANSACTION_DATE,
  31.                                            TRANSACTION_TYPE_ID,
  32.                                            TRANSFER_ORGANIZATION,
  33.                                                     TRANSFER_SUBINVENTORY,
  34.                                            LAST_UPDATE_DATE,
  35.                                            LAST_UPDATED_BY,
  36.                                            CREATION_DATE,
  37.                                            CREATED_BY,
  38.                                            FLOW_SCHEDULE,
  39.                                            SCHEDULED_FLAG)
  40.                                    VALUES (
  41.                                   V_INTERFACE_ID,
  42.                                      D.SOURCE_CODE,
  43.                                                     D.SOURCE_LINE_ID,
  44.                                   D.SOURCE_HEADER_ID,
  45.                                            D.PROCESS_FLAG,
  46.                                            1,
  47.                                            D.TRANSACTION_MODE,
  48.                                            V_INTERFACE_ID,
  49.                                            D.ITEM_ID,
  50.                                                     D.ITEM_CODE,
  51.                                            D.ORG_ID,
  52.                                            D.FROM_SUBINV,
  53.                                            D.FROM_LOCATOR,
  54.                                                     D.LOT_NUMBER,
  55.                                            D.TRANSACTION_QUANTITY,
  56.                                            D.UOM,
  57.                                            D.TRANSACTION_DATE,
  58.                                            D.TRANS_TYPE_ID,
  59.                                            D.ORG_ID,
  60.                                                     D.TRANS_SUB_INV,
  61.                                            SYSDATE,
  62.                                            FND_GLOBAL.USER_ID,
  63.                                            SYSDATE,
  64.                                            FND_GLOBAL.USER_ID,
  65.                                            D.FLOW_SCHEDULE,
  66.                                            D.SCHEDULE_FLAG);
  67.  
  68.  
  69.                                    INSERT INTO MTL_TRANS_LOTS_INTERFACE_BAK  -- Later change to MTL_TRANSACTION_LOTS_INTERFACE
  70.                                                 (TRANSACTION_INTERFACE_ID,
  71.                                                     SOURCE_CODE,
  72.                                                     SOURCE_LINE_ID,
  73.                                                     LOT_NUMBER,
  74.                                                     TRANSACTION_QUANTITY,
  75.                                                     LAST_UPDATE_DATE,
  76.                                                     LAST_UPDATED_BY,
  77.                                                     CREATION_DATE,
  78.                                                     CREATED_BY)
  79.                                    VALUES
  80.                                              (V_INTERFACE_ID,
  81.                                            D.SOURCE_CODE,
  82.                                                     D.SOURCE_LINE_ID,
  83.                                            D.LOT_NUMBER,
  84.                                            D.TRANSACTION_QUANTITY,
  85.                                            SYSDATE,
  86.                                            FND_GLOBAL.USER_ID,
  87.                                            SYSDATE,
  88.                                            FND_GLOBAL.USER_ID);
  89.                     END LOOP;
  90.  
Where parameters
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.
Nov 30 '07 #1
1 5742
amitpatel66
2,367 Expert 2GB
Have you hardcoded the values in WHERE condition? Your WHERE condition for the pararmeters shows that. In that case the other records will not be fetched.
Nov 6 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Ed | last post by:
Hello, I posted a question about looping with Select in a While loop, a few days ago. Repliers to my post advised me that a Cursor would be much better (thanks all for your replies). I found...
3
by: r rk | last post by:
I am trying to write a utility/query to get a report from a table. Below is the some values in the table: table name: dba_daily_resource_usage_v1...
5
by: !TG | last post by:
I currently use Do while loop, but I'd rather use a For Loop though I have never gotten the hang of them. Would some one please be so kind as to show me how to loop through a recordset.
0
by: Anthony Robinson | last post by:
I have a stored procedure that needs to loop through a record set, evaluate value, then either perform an operation or move tro the next record - depending on some criteria. Here's the procedure:...
1
by: Rohit Raghuwanshi | last post by:
Hello all, we are running a delphi application with DB2 V8.01 which is causing deadlocks when rows are being inserted into a table. Attaching the Event Monitor Log (DEADLOCKS WITH DETAILS) here....
1
by: Søren Larsen | last post by:
In a stored procedure (SP1) I am looping through a cursor with records from Table1. Each record in the cursor is inserted into Table2. Insert trigger on Table2 is inserting the record into Table3...
2
by: Chris Zopers | last post by:
Hello, I've created a stored procedure that loops through a cursor, with the following example code: DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods DECLARE @intYear smallint...
16
by: Jen | last post by:
Hi. I have this problem that I think should be easy but have been struggling with this for days. I have a list based on a recordset from a database. This list consists of records meeting a certain...
1
by: mrdude | last post by:
i am working on a web analysis project. i am having difficulty with counting hits and visits (a group of hits without a 30 minute break between hits). i decided to build a cursor in order to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.