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)63006 5120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-02(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-03(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(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: -
/* 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;
-
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)63006 5120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-01(Q)2500
data.
The cursor to store the parameters is -
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;
-
Where parameters
B.MOVE_ORD_ID = 336126
B.ORG_ID = 83
B.ITEM_ID = 21823
B.VENDOR_LOT_NU M =LOT-DISB2P-01
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-02(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-03(Q)2500
(O)336126(G)83( I)21823(M)63006 5120(L)LOT-DISB2P-04(Q)2500
Any help is appreciated.
Thanks in advance.
1  5773 
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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 a looping example using cursors on the
net, but I don't know how to declare a read only cursor.
Note: my objective is to simulate an array of field names
for creating an xml doc/string (to use with OpenXML
function). So I created a small table...
|
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
conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_batch
------------------------------------------------------------
80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30
80 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:30
45 |dcp_webu|DCP |5967 |75 |669 |11/16/2004...
|
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.
|
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:
CREATE PROCEDURE AIM.UpdateArchiveRetrievalStatus ()
------------------------------------------------------------------------
-- SQL Stored Procedure
-- April 13, 2004
-- Anthony Robinson
-- Updates Status of ArchiveRetrieval. If...
|
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. From the log it looks
like the problem happens when 2 threads insert 1 record each in the
same table and then try to aquire a NS (Next Key Share)
lock on the record inserterd by the other thread.
Thanks
Rohit
| | |
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 (in
another DB).
In the insert trigger on Table3, a series of checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is...
|
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
DECLARE @intPeriod smallint
DECLARE @strTekst varchar(50)
|
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 criteria and at the bottom of this list i have a
button that inserts all these records to a´nother table in the database. So
long everything's ok.
BUT, at the top of this list I have a textarea that the user can write down
some text to be put...
|
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 calculate visits. however, i have no experience with coding cursors and very little experience with both loops and t-sql.
in the cursor, it gets the data from a table that contains the IIS log (used logparser to transfer the logs to sql server). it...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
