CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10goracle-sqldeveloper
oubinghose
oubinghose

Reputation: 51

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.

Upvotes: 0

Views: 4399

Answers (2)

oubinghose
oubinghose

Reputation: 51

Modified script using LAST_VALUE analytic function

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, LAST_PO_PRICE
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,
A.LAST_PO_PRICE 
FROM 
(
SELECT DISTINCT LAST_VALUE(RCV.PO_UNIT_PRICE) over (partition by RCV.ORGANIZATION_ID order by trunc(RCV.TRANSACTION_DATE) ) LAST_PO_PRICE, PLA.ITEM_ID,RCV.ORGANIZATION_ID, RCV.VENDOR_ID
FROM RCV_TRANSACTIONS RCV, PO_LINES_ALL PLA
WHERE PLA.PO_LINE_ID = RCV.PO_LINE_ID
ORDER BY PLA.ITEM_ID
)A,
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))
/* Condition when no item description exist */
--AND (PLA.ITEM_DESCRIPTION = NVL(:p_item_description, PLA.ITEM_DESCRIPTION) OR PLA.ITEM_DESCRIPTION IS NULL)
--AND PLA.ITEM_DESCRIPTION = NVL(:p_item_description, PLA.ITEM_DESCRIPTION) 
AND A.ITEM_ID = PLA.ITEM_ID
AND A.ORGANIZATION_ID = RCV.ORGANIZATION_ID
AND A.VENDOR_ID = RCV.VENDOR_ID
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.ORGANIZATION_ID = RCV.ORGANIZATION_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, PO_UNIT_PRICE, TRANSACTION_DATE, LAST_PO_PRICE
ORDER BY INVENTORY_ORG,ITEM_DESCRIPTION,VENDOR_NAME,SEGMENT1, SEGMENT2, SEGMENT3

Upvotes: 0

Анатолий Предеин
Анатолий Предеин

Reputation: 571

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) ) ...

Upvotes: 1

Related Questions