Skip to main content

We have an inventory history quick report where we want to pull 4 columns from the latest PO Receipt for a contract/part.

I’ve coded a solution however it hangs when I run the report. I’m guessing there’s a better way to code a subquery that pulls 4 columns of the latest date PO receipt.


( SELECT  max(voucher_date_ref) 
    FROM INVOICE_LEDGER_ITEM_SU_QRY
     where po_ref_number = ivpp.order_no
    group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC
    FETCH NEXT 1 ROWS ONLY) as voucher_date,
( SELECT  max(voucher_no_ref) 
    FROM INVOICE_LEDGER_ITEM_SU_QRY
     where po_ref_number = ivpp.order_no
    group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC
    FETCH NEXT 1 ROWS ONLY) as voucher_#,
( SELECT  max(invoice_date) 
    FROM INVOICE_LEDGER_ITEM_SU_QRY
     where po_ref_number = ivpp.order_no
    group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC
    FETCH NEXT 1 ROWS ONLY) as invoice_date,
( SELECT  max(ledger_item_id) 
    FROM INVOICE_LEDGER_ITEM_SU_QRY
     where po_ref_number = ivpp.order_no
    group by invoice_date HAVING MAX(invoice_date) >= to_date('1/1/2020', 'MM/DD/YYYY') order by invoice_date DESC
    FETCH NEXT 1 ROWS ONLY) as invoice_#,
...
from ifsinfo.RW_INV_VALUE_PART_PO ivpp    (this is an IAL)

left join INVENTORY_TRANSACTION_HIST2 	    ith2
    on ivpp.order_no = ith2.source_ref1 and ivpp.part_no = ith2.part_no  and transaction_code = 'ARRIVAL'

To further complicate this, the IAL has it’s own set of subqueries that do the same thing over a different table:

select 
ivpse.contract as Contract,
ip.second_commodity as Comm_2,
ivpse.part_no as Part_No,
ip.description as Part_Desc,
ip.accounting_group as GLPT,
ivpse.STAT_YEAR_NO as Year,
ivpse.STAT_PERIOD_NO as Period,
ivpse.quantity as On_Hand_Qty,
ROUND(ip.estimated_material_cost,4) as Std_Cost,
ROUND(ivpse.quantity * ivpse.unit_cost,2)  as Std_Ext_$,
(SELECT  MAX(order_no)
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY ) as Order_No,

(SELECT  MAX(TRUNC(arrival_date)) 
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY ) as Rec_Date,

(SELECT  MAX(inv_qty_arrived)  , MAX(receipt_no)
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY ) as Buy_Qty_Due,

(SELECT  MAX(receipt_no)
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY ) as Buy_Unit_Price,

( SELECT MAX(receipt_no * inv_qty_arrived) as PO_Order_Cost
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
FETCH FIRST 1 ROWS ONLY ) as PO_Order_Cost,

(SELECT  MAX(conv_factor) 
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY ) as Convr_Fctr,

( SELECT MAX(ROUND(receipt_no / nullif(conv_factor,0),2)) 
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
FETCH FIRST 1 ROWS ONLY )  as Mat_Cost_at_Actual,

(SELECT  MAX(buy_unit_meas) 
    FROM REGA1APP.PURCHASE_RECEIPT_NEW prn
    where  prn.part_no = ivpse.part_no and prn.contract = ivpse.contract
    group by arrival_date HAVING MAX(arrival_date) >= to_date('1/1/2019', 'MM/DD/YYYY')  order by arrival_date DESC
    FETCH NEXT 1 ROWS ONLY )  as Purch_UOM,

ip.unit_meas as Inv_UOM

from REGA1APP.INVENTORY_VALUE_PART_SUM_EXT ivpse

left join REGA1APP.INVENTORY_PART ip
    on ivpse.part_No = ip.part_No and ivpse.contract = ip.contract

where ivpse.quantity <> 0
and ip.type_code_db in ('3','4')

results:

 

Would I create 2 new IAL’s that would be the subqueries that would return just the latest receipt date row for each contract/part?

Terms & ConditionsCookie settings