I am trying to see the PO number for parts on a Distro Order, i have the below SQL but it is retuning both closed and open PO’s is there a way for it to only show the open PO’s. Thank you
(SELECT listagg(order_NO, '^') WITHIN GROUP (ORDER BY 1) FROM (SELECT ORDER_NO FROM &AO.PURCHASE_REQ_LINE_ALL WHERE part_no = DISTRIBUTION_ORDER.PART_NO))
Add below condition to filter out closed/cancelled ones?
and &AO.purchase_order_api.Get_State(order_no) not in ('Cancelled','Closed')
Which PO’s do you want to see? I guess it is not the PO on the DO since that already exists in field PO_ORDER_NO in view DISTRIBUTION_ORDER.
Is it a list of other open PO’s you want I would have used view PURCHASE_ORDER_LINE_PART (Purchase Order Line View for part lines) instead of PURCHASE_REQ_LINE_ALL (purchase requisition line view) and the condition asanka had:
objstate not in ('Cancelled','Closed')
Another thing, do you have several sites? Then you need to add this to only show PO’s for the DO demand site:
contract = DISTRIBUTION_ORDER.demand_site
I was trying to see if there were any PR or PO’s open for the Part on the DO, i was able to get it to work using the below, thank you everyone for your assistance
(CASE WHEN (SELECT listagg(order_NO,'^') WITHIN GROUP (ORDER BY 1) FROM (SELECT ORDER_NO FROM &AO.PURCHASE_REQ_LINE_ALL WHERE part_no = DISTRIBUTION_ORDER.PART_NO)) NOT IN 'Released' THEN 'YES' ELSE 'NO' END)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
