| PO: SQL Script for Each Item's Last Receive Date [message #95529] |
Mon, 16 February 2004 12:16  |
Eric G.
Messages: 32
Registered: November 2003
|
Member |
|
|
Does anyone have any useful SQL script or function to derive an item's last receive date and qty received within an operating unit? I wanted to take an existing item master query and do an outer join to pull in each item's last receive date & qty, if any. First I created the view below to identify each item's last receive date. But since this is SELECT DISTINCT query, I cannot pull in the quantity from each item's last receipt (MAX(TRUNC(RCV.TRANSACTION_DATE).
CREATE OR REPLACE FORCE VIEW FIRST_VIEW (ITEM_ID, ITEM_NUMBER, LAST_RECEIVE_DATE)
AS SELECT DISTINCT PO.ITEM_ID,PO.ITEM_NUMBER, MAX(TRUNC(RCV.TRANSACTION_DATE))
FROM APPS.POFG_RECEIVING_TRANSACTIONS RCV, APPS.POFG_PO_LINES <st1:place>PO</st1:place>
WHERE RCV.OPERATING_UNIT_ID = 32 AND RCV.TRANSACTION_TYPE = 'Receive' AND RCV.PO_LINE_ID = PO.PO_LINE_ID AND RCV.OPERATING_UNIT_ID = PO.OPERATING_UNIT_ID AND PO.ITEM_NUMBER IS NOT NULL AND PO.LINE_TYPE = 'Inventory'
GROUP BY PO.ITEM_ID, PO.ITEM_NUMBER
So to pull in the quantity received on the last receive date, I could create a second view that queries the first view either thru joins or using a subquery. Surprisingly, the 2nd view with joins below was faster than using subqueries. But even the 2nd view below is extremely slow against 1,700 distinct item numbers on 10,500 records in the PO_LINES_ALL table.
CREATE OR REPLACE FORCE VIEW SECOND_VIEW (ITEM_ID, ITEM_NUMBER, LAST_RECEIVE_DATE, QUANTITY) AS
SELECT DISTINCT PO.ITEM_ID, PO.ITEM_NUMBER, MAX.LAST_RECEIVE_DATE, SUM(RCV.TRANSACTION_QUANTITY) QTY
FROM APPS.POFG_RECEIVING_TRANSACTIONS RCV, APPS.POFG_PO_LINES <st1:place>PO</st1:place>, FIRST_VIEW MAX
WHERE RCV.OPERATING_UNIT_ID = 32 AND RCV.TRANSACTION_TYPE = 'Receive' AND RCV.PO_LINE_ID = PO.PO_LINE_ID AND RCV.OPERATING_UNIT_ID = PO.OPERATING_UNIT_ID AND PO.ITEM_NUMBER IS NOT NULL AND PO.LINE_TYPE = 'Inventory' AND PO.ITEM_ID = MAX.ITEM_ID AND TRUNC(RCV.TRANSACTION_DATE) = MAX.LAST_RECEIVE_DATE
GROUP BY PO.ITEM_ID, PO.ITEM_NUMBER, MAX.LAST_RECEIVE_DATE;
Any suggestions are appreciated. I can e-mail you these scripts above directly.
|
|
|
|
| Re: PO: SQL Script for Each Item's Last Receive Date [message #95576 is a reply to message #95529] |
Tue, 02 March 2004 09:46  |
Magnar Johannessen
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
Can You use this SQL Query?
SELECT MAX(rcv.transaction_date)
,mmt_.inventory_item_id
,msi.segment1 item_number ----????????????
,rcv.organization_id
,rcv.quantity
FROM mtl_system_items_b msi
,mtl_material_transactions mmt_
,rcv_transactions rcv
WHERE rcv.destination_context = 'INVENTORY'
AND rcv.transaction_id = mmt_.rcv_transaction_id
AND mmt_.source_code = 'RCV'
AND mmt_.Inventory_Item_Id = $$$$$$$$$$
AND mmt_.Organization_Id = 32 -- OPERATING_UNIT_ID???????????
AND mmt_.transaction_date IN (SELECT MAX(mmt.transaction_date)
FROM mtl_material_transactions mmt
WHERE mmt.source_code = 'RCV'
AND mmt.Inventory_Item_Id = $$$$$$$$
AND mmt.Organization_Id = 32 -- OPERATING_UNIT_ID???????????
GROUP BY mmt.Organization_Id )
AND (mmt_.inventory_item_id = msi.inventory_item_id AND mmt_.Organization_Id = msi.Organization_Id)
GROUP BY mmt_.inventory_item_id
,msi.segment1
,rcv.organization_id
,rcv.quantity
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
