Home » Applications » Oracle Fusion Apps & E-Business Suite » PO: SQL Script for Each Item's Last Receive Date
PO: SQL Script for Each Item's Last Receive Date [message #95529] Mon, 16 February 2004 12:16 Go to next message
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 Go to previous messageGo to next message
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
Re: PO: SQL Script for Each Item's Last Receive Date [message #95577 is a reply to message #95576] Tue, 02 March 2004 10:04 Go to previous message
Eric G.
Messages: 32
Registered: November 2003
Member
Thanks for the suggestion. Your script would work for 1 inventory item ID. Perhaps I can create a function and then call that function from my view.
Previous Topic: WIP: Defaulting the Resource Usage Rate on a Routing
Next Topic: Combining Two General Ledger Applications
Goto Forum:
  


Current Time: Mon Mar 04 16:15:49 CST 2024