oubinghose oubinghose - 3 months ago 8
SQL Question

Derive Last PO Price in SQL Query (Oracle)

I am developing a script to get a list items by vendor and organization unit with their respective minimum, maximum, average and last price for a specific period of time (transaction date). The last price is based on the latest transaction date.

I am having some issues to calculate the last price based on the script I developed. I tried to

(1) Add an inner query to look for the maximum transaction date for an item, organization and vendor

(2) Based on the output do a join with the outer query and join on the item id, org id and vendor id.

But I am not getting the expected result.

Below is the query without the inner query to derive the last price.

SELECT NAME INVENTORY_ORG,SEGMENT1 CATEGORY_SEGMENT_1, SEGMENT2 CATEGORY_SEGMENT2, SEGMENT3 CATEGORY_SEGMENT_3,ITEM_DESCRIPTION ARTICLE, TRANSACTION_TYPE TYPE, VENDOR_NAME SUPPLIER,UNIT_OF_MEASURE UNIT,SUM(QUANTITY) QUANTITY_PURCHASED, AVG(PO_UNIT_PRICE) AVERAGE_PRICE, MIN(PO_UNIT_PRICE)MIN_PRICE, MAX(PO_UNIT_PRICE)MAX_PRICE, SUM(QUANTITY) * AVG(PO_UNIT_PRICE) NET_AMOUNT, SUM(VAT_AMOUNT) VAT_AMOUNT,((SUM(QUANTITY) * AVG(PO_UNIT_PRICE)) + SUM(VAT_AMOUNT)) GROSS_AMOUNT
FROM
(SELECT RCV.TRANSACTION_ID, MC.SEGMENT1, MC.SEGMENT2, MC.SEGMENT3,RCV.TRANSACTION_TYPE, RCV.TRANSACTION_DATE, PLA.ITEM_DESCRIPTION,RCV.QUANTITY, RCV.UNIT_OF_MEASURE, RCV.PO_UNIT_PRICE, RCV.ORGANIZATION_ID, HOU.NAME, APS.VENDOR_NAME,
case
WHEN msi.taxable_flag = 'N' then 0
ELSE (nvl(zrb.PERCENTAGE_RATE,0)/100) * PV.Unit_Price*RCV.quantity end as VAT_AMOUNT
FROM
RCV_TRANSACTIONS RCV, PO_LINES_ALL PLA, HR_ORGANIZATION_UNITS HOU, AP_SUPPLIERS APS, PO_LINES_REF_V PV, MTL_SYSTEM_ITEMS MSI,ZX_RATES_B ZRB, MTL_ITEM_CATEGORIES MIC, MTL_CATEGORIES MC
WHERE TRUNC(RCV.TRANSACTION_DATE) >= NVL(:p_transaction_date_from, TRUNC(RCV.TRANSACTION_DATE))
AND TRUNC(RCV.TRANSACTION_DATE) <= NVL(:p_transaction_date_to, TRUNC(RCV.TRANSACTION_DATE))
AND PLA.ITEM_DESCRIPTION = NVL(:p_item_description, PLA.ITEM_DESCRIPTION)
AND RCV.ORGANIZATION_ID = NVL(:p_org_id,RCV.ORGANIZATION_ID)
AND APS.VENDOR_NAME = NVL(:p_supplier_name, APS.VENDOR_NAME)
AND RCV.PO_LINE_ID= PV.PO_LINE_ID
AND PLA.PO_LINE_ID(+) = RCV.PO_LINE_ID
AND HOU.ORGANIZATION_ID = RCV.ORGANIZATION_ID
and MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
and MSI.ORGANIZATION_ID = RCV.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND APS.VENDOR_ID(+) = RCV.VENDOR_ID
and ZRB.TAX_RATE_CODE (+)= msi.tax_code
AND RCV.TRANSACTION_TYPE = 'RECEIVE'
AND MIC.CATEGORY_SET_ID = '1')
GROUP BY TRANSACTION_TYPE, ITEM_DESCRIPTION, UNIT_OF_MEASURE, NAME, VENDOR_NAME, SEGMENT1, SEGMENT2, SEGMENT3
ORDER BY INVENTORY_ORG,ITEM_DESCRIPTION,VENDOR_NAME,SEGMENT1, SEGMENT2, SEGMENT3


The PO Unit Price is from the table RCV_Transactions. We get the Item ID from PO_Lines_all table.

Grateful if anyone can assist me and give me some tips on the best way to get the last price based on the above query.

Answer

you can use last_value function (oracle analytic function) see how https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions073.htm

SELECT LAST_VALUE(PO_UNIT_PRICE) over (order by trunc(RCV.TRANSACTION_DATE) ) ...

or if you need partitioned you can do this

SELECT LAST_VALUE(PO_UNIT_PRICE) over (partition by INVENTORY_ORG order by trunc(RCV.TRANSACTION_DATE) ) ...