This content has been marked as final. Show 3 replies
I am not sure what you are looking for.
If you are looking for POs that have not been received at the dock, simply query from po_line_locations_inq_v and use the quantity_received and quantity columns.
Make sure you skip closed or cancelled POs.
If you are looking for those POs that been received but not delivered, you need to use rcv_transactions.
If you want to continue using your current query, try adding this line at the end of your query
HAVING (pla.quantity - rsl.quantity_received) != 0
Hope this helps,
thanks for ur guidance
based on ur advice
i ve modified the query to include quantity_received inorder to get the PO'S that are pending to receive
is it right to include closed code=OPEN
SELECT DISTINCT pha.segment1 po_num,pla.line_num, plla.quantity qty_ord ,plla.quantity_received,plla.quantity_accepted,(plla.quantity-plla.quantity_received)pending_qty, pha.approved_date,TO_CHAR(pha.approved_date,'YYYY')YEARS, msi.segment1,msi.description FROM po_headers_all pha ,po_lines_all pla ,po_line_locations_all plla ,mtl_system_items msi WHERE pha.po_header_id =pla.po_header_id AND pha.po_header_id=plla.po_header_id AND pla.po_line_id=plla.po_line_id AND pha.type_lookup_code='STANDARD' AND PHA.CLOSED_CODE='OPEN' AND plla.quantity > plla.quantity_received and plla.quantity_cancelled=0 and plla.quantity_rejected=0 AND msi.inventory_item_id=pla.item_id
should any other changes be done
thanking in advance
Edited by: makdutakdu on Oct 5, 2011 7:26 AM
I assume that you are looking for open POs because closed /canceled POs cannot be received.1 person found this helpful
Make sure you put NVL around quantity_received, quantity_cancelled, quantity_rejected.
Hope this helps,