Query for PR PO Receipt Invoice Payments

--PR and PO (Standard)

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_header_id = pla.po_header_id and po_line_id = pla.po_line_id)

order by 3 desc;

--PR, PO (Standard) and Receipts

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and  exists

                (select 1

                from ap_invoice_lines_all

                where 1=1

                and po_line_id = pla.po_line_id

                and po_header_id = pla.po_header_id)

order by 3 desc

---PR, PO (Standard), Receipts and Invoice

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

          rt.transaction_id,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt,

        ap_invoice_lines_all aila,

        ap_invoices_all aia

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and  prha.requisition_header_id= 48955358

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

order by 3 desc;

---PR, PO (Standard), Receipts, Invoice and Payments

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

          rt.transaction_id,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

--and  prha.requisition_header_id= 48955358

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

order by 3 desc;

--PR, PO (Blanket) and Releases

select pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

        (select sum(nvl(quantity,0)*nvl(price_override,0))

        from po_line_locations_all plla

        where 1=1

        and plla.po_release_id = pra.po_release_id) release_value,        

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

--and pra.po_release_id = 17622990

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_release_id = pra.po_release_id)

and not exists  (select 1 from ap_invoice_lines_all  where 1=1 and po_release_id = pra.po_release_id);

begin mo_global.set_policy_context('S',125); end;

--PR, PO (Blanket), Releases and Receipts

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and not exists  (select 1 from ap_invoice_lines_all  where 1=1 and po_release_id = pra.po_release_id)

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

--- PR, PO (Blanket), Releases, Receipts and Invoices

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          aila.line_number invoice_line_no,

          aila.amount,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date        

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        ap_invoice_lines_all aila,

        ap_invoices_all aia           

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and aila.po_release_id = pra.po_release_id

and aila.po_line_location_id = plla.line_location_id

and aila.invoice_id = aia.invoice_id

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

--- PR, PO (Blanket), Releases, Receipts, Invoices and Payments

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          aila.line_number invoice_line_no,

          aila.amount,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date ,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

         aca.amount payment_value                    

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca                    

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and aila.po_release_id = pra.po_release_id

and aila.po_line_location_id = plla.line_location_id

and aila.invoice_id = aia.invoice_id

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

/* Only PR */

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and not exists  (select 1 from po_distributions_all pda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* Only PO */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

and not exists (select 1 from rcv_transactions where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

and not exists (select 1 from ap_invoice_lines_all where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

order by 3 desc

/* PO and Receipts */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id       

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt       

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

and not exists (select 1 from ap_invoice_lines_all where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

order by 3 desc

/* PO, Receipts and Invoices */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id ,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,                   

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount              

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt ,

        ap_invoice_lines_all aila,

        ap_invoices_all aia             

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* PO, Receipt, Invoice and Payments */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id ,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,                   

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount ,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value                       

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt ,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca                     

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* Invoices Only */

select distinct aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          nvl(aia.amount_paid,0) amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount

from ap_invoices_all aia,

        ap_invoice_lines_all aila

where 1=1

and aia.invoice_id = aila.invoice_id

and trunc(aia.creation_date) >= trunc(sysdate)-365

and aila.po_line_id is null

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

order by 5 desc

/* Invoice and Payments */

select distinct aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          nvl(aia.amount_paid,0) amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value         

from ap_invoices_all aia,

        ap_invoice_lines_all aila,

        ap_invoice_payments_all aipa,

        ap_checks_all aca           

where 1=1

and aia.invoice_id = aila.invoice_id

and trunc(aia.creation_date) >= trunc(sysdate)-365

and aila.po_line_id is null

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

order by 5 desc