Purchase Order raised that have been received

 Purchase Order  raised that have been received

SELECT   por.segment1  reqnum,
     
      poh.segment1                                            PO_Number

,      pov.vendor_name vendor_name
,      nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0)         Promised_date
,rcv.transaction_date received_date
                                                                
FROM   po_distributions_all podl,
po_requisition_headers_all por,
po_requisition_lines_all poll,
po_req_distributions_all pod,
po_line_locations_all            pll
,      po_lines_all                     pol
,      po_headers_all                poh
,      ap_suppliers                   pov,
 rcv_transactions     rcv

WHERE   por.REQUISITION_HEADER_ID=poll.REQUISITION_HEADER_ID
and poll.REQUISITION_LINE_ID=pod.REQUISITION_LINE_ID
and pol.PO_HEADER_ID=poh.PO_HEADER_ID
--and pll.PO_LINE_ID=poll.PO_LINE_ID
--and pll.PO_HEADER_ID=poh.PO_HEADER_ID
and podl.PO_LINE_ID=pll.PO_LINE_ID
and pod.DISTRIBUTION_ID=podl.REQ_DISTRIBUTION_ID 
and pll.po_line_id               = pol.po_line_id
AND    pol.po_header_id             = poh.po_header_id

AND    poh.vendor_id                = pov.vendor_id
and rcv.po_line_id=pol.po_line_id
and rcv.po_line_location_id=pll.line_location_id

AND    exists (select 'receiving transaction for this PO' from rcv_transactions rct where rct.transaction_type = 'RECEIVE' and rct.po_header_id = poh.po_header_id
and rct.po_line_id = pol.po_line_id
and rct.po_line_location_id = pll.line_location_id)

--and      poh.vendor_id = :Parent_vendor_id

AND    pll.shipment_type = 'STANDARD'
AND    poh.type_lookup_code = 'STANDARD'

           
AND    pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')         
UNION ALL
SELECT por.segment1  reqnum,
 poh.segment1||'-'||por.release_num  PO_Number
 ,pov.vendor_name vendor_name
 ,nvl(pll.promised_date,pll.need_by_date) - nvl(pll.days_early_receipt_allowed,0) Promised_date                             
 , rcv.TRANSACTION_DATE received_date

FROM po_distributions_all podl,
po_requisition_headers_all por,
po_requisition_lines_all poll,
po_req_distributions_all pod,  
po_line_locations_all            pll
 ,      po_lines_all                    pol
,      po_headers_all                   poh
,     ap_suppliers                   pov
,      po_releases                  por ,
rcv_transactions                    rcv

WHERE  por.REQUISITION_HEADER_ID=poll.REQUISITION_HEADER_ID
and poll.REQUISITION_LINE_ID=pod.REQUISITION_LINE_ID
and pol.PO_HEADER_ID=poh.PO_HEADER_ID
--and pll.PO_LINE_ID=poll.PO_LINE_ID
--and pll.PO_HEADER_ID=poh.PO_HEADER_ID
and podl.PO_LINE_ID=pll.PO_LINE_ID
and pod.DISTRIBUTION_ID=podl.REQ_DISTRIBUTION_ID 
and pll.po_line_id               = pol.po_line_id
AND    pol.po_header_id             = poh.po_header_id

AND    poh.vendor_id                = pov.vendor_id
and rcv.po_line_id=pol.po_line_id
and rcv.po_line_location_id=pll.line_location_id

AND    exists (select 'receiving transaction for this PO' from rcv_transactions rct where rct.transaction_type = 'RECEIVE' and rct.po_header_id = poh.po_header_id
and    rct.po_line_id = pol.po_line_id
and    rct.po_line_location_id = pll.line_location_id)

AND    pll.shipment_type in ( 'BLANKET','SCHEDULED')
AND    poh.type_lookup_code in ( 'BLANKET','PLANNED')


AND    pol.order_type_lookup_code IN ('QUANTITY', 'AMOUNT')