PURCHASE ORDER RECEIVING AND NOT PAID REPORT

PURCHASE ORDER RECEIVING AND NOT PAID REPORT

SELECT   por.segment1  reqnum,pol.po_LINE_id,
     
      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,APS.AMOUNT_REMAINING
                                                                
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_all      pra,
 rcv_transactions     rcv,
 AP_INVOICES_ALL AP,
 AP_INVOICE_DISTRIBUTIONS_ALL APD,
 AP_PAYMENT_SCHEDULES_ALL APS

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=pll.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 rcv.po_header_id=poh.PO_HEADER_ID
and rcv.po_distribution_id=podl.po_distribution_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')
And rcv.transaction_type='RECEIVE'
AND AP.INVOICE_ID=APD.INVOICE_ID
AND APD.PO_DISTRIBUTION_ID=podl.PO_DISTRIBUTION_ID
AND AP.INVOICE_ID=APS.INVOICE_ID
AND APS.AMOUNT_REMAINING>0       
UNION ALL
SELECT por.segment1  reqnum,pol.po_LINE_id,
 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,APS.AMOUNT_REMAINING

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,
AP_INVOICES_ALL AP,
 AP_INVOICE_DISTRIBUTIONS_ALL APD,
 AP_PAYMENT_SCHEDULES_ALL APS

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=pll.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 rcv.po_header_id=poh.PO_HEADER_ID
and rcv.po_distribution_id=podl.po_distribution_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')
And rcv.transaction_type='RECEIVE'
AND AP.INVOICE_ID=APD.INVOICE_ID
AND APD.PO_DISTRIBUTION_ID=podl.PO_DISTRIBUTION_ID
AND AP.INVOICE_ID=APS.INVOICE_ID
AND APS.AMOUNT_REMAINING>0