To reurn the PO information related to a supplier invoice. Since only one row of data is required per invoice, the SELECT statement must show the most recent PO number, the total number of related PO’s (if there are more than one). Similarly for PO lines, shipment lines, receipts.

SELECT CASE WHEN qrslt.po_more > 1 THEN

         qrslt.segment1
         ‘ (1 of ‘po_more‘)’
       ELSE
         qrslt.segment1
       END AS po_number
,      CASE WHEN qrslt.line_more > 1 THEN
         TO_CHAR(qrslt.line_num)
         ‘ (1 of ‘line_more‘)’
       ELSE
         TO_CHAR(qrslt.line_num)
       END AS line_num
,      CASE WHEN qrslt.ship_more > 1 THEN
         TO_CHAR(qrslt.shipment_num)
         ‘ (1 of ‘ship_more‘)’
       ELSE
         TO_CHAR(qrslt.shipment_num)
       END AS ship_num
,      qrslt.full_name AS requestor
,      CASE WHEN qrslt.rcpt_more > 1 THEN
         TO_CHAR(qrslt.receipt_num)
         ‘ (1 of ‘rcpt_more‘)’
       ELSE
         TO_CHAR(qrslt.receipt_num)
       END AS rcpt_num
FROM
( SELECT pha.segment1
, pla.line_num
, plla.shipment_num
, ppx.full_name
, rsh.receipt_num
, pha.creation_date po_date
, rsh.creation_date rcpt_date
, RANK() OVER
(
ORDER BY pha.creation_date ASC
) po_more
, RANK() OVER
(
PARTITION BY pha.segment1
ORDER BY pla.line_num DESC
) line_more
, RANK() OVER
(
PARTITION BY pha.segment1
, pla.line_num
ORDER BY plla.shipment_num DESC
) ship_more
, RANK() OVER
(
PARTITION BY pha.segment1
ORDER BY rsh.creation_date ASC
) rcpt_more
FROM po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
, po_distributions_all pda
, ap_invoice_distributions_all aida
, per_people_x ppx
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND plla.org_id = pla.org_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pda.po_line_id
AND pda.line_location_id = plla.line_location_id
AND pda.org_id = pla.org_id
AND aida.org_id = pda.org_id
AND aida.po_distribution_id = pda.po_distribution_id
AND aida.line_type_lookup_code = ‘ITEM’
AND ppx.person_id(+) = pha.agent_id
AND rsl.po_distribution_id(+) = pda.po_distribution_id
AND rsl.shipment_header_id = rsh.shipment_header_id(+)
AND aida.invoice_id = p_invoice_id
AND aida.org_id = p_org_id
ORDER BY pha.creation_date DESC
, pla.line_num ASC
, plla.shipment_num ASC
, rsh.creation_date DESC
) qrslt
WHERE ROWNUM = 1