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

hr_assignment_api.update_emp_asg in HRMS

create table xx_gosi_update
( emp_no varchar2(240)
,gosi  varchar2(240)
,status varchar2(240)
)

Below is the script for update the assignment (I am updating annuities field)
======================================================================

DECLARE
   -- Local Variables
   -- -----------------------
   lc_dt_ud_mode           VARCHAR2(100)    := NULL;
   ln_assignment_id       NUMBER                  := 1336;
   ln_supervisor_id        NUMBER                  := 2;
   ln_object_number       NUMBER                  := 5;
   ln_people_group_id  NUMBER                  :=null; -- 1;
   p_effective_date date;
   p_object_version_number number;
   v_assignment_id number;
 
   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                                 BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id       HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   ln_comment_id                             PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning        BOOLEAN;

 -- Out Variables for Update Employee Assgment Criteria
 -- -------------------------------------------------------------------------------
 ln_special_ceiling_step_id                    PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                                          VARCHAR2(30);
 ld_effective_start_date                             PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 ld_effective_end_date                              PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning   BOOLEAN;
 lb_other_manager_warning                  BOOLEAN;
 lb_spp_delete_warning                          BOOLEAN;
 lc_entries_changed_warning                VARCHAR2(30);
 lb_tax_district_changed_warn             BOOLEAN;

 cursor c1 is

SELECT a.rowid,pas.effective_start_date, pas.object_version_number,pas.assignment_id,decode(a.GOSI,'Yes','Y','No','N')gosi,a.emp_no,a.status
        --INTO p_effective_date, p_object_version_number,v_assignment_id
        FROM per_all_assignments_f pas
        ,xxdfc_gosi_update a
       WHERE pas.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR')
         --AND pas.assignment_id in(77,913);
         and a.EMP_NO=pas.ASSIGNMENT_NUMBER
         --and a.emp_no in('S00455','S00641')
         ;

 begin
 for rec in c1
 loop


 hr_assignment_api.update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => sysdate, --TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode         => 'CORRECTION', --lc_dt_ud_mode,
  p_assignment_id                            => rec.assignment_id, --ln_assignment_id,
  --p_supervisor_id                              => NULL,
  --p_change_reason                           => NULL,
  --p_manager_flag                              => 'N',
  --p_bargaining_unit_code              => NULL,
  --p_labour_union_member_flag   => NULL,
  p_segment1                                       => 81, --DFC Business Group
  p_segment3                                       => rec.gosi, --'Y',
  --p_normal_hours                              => 8,
  --p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => rec.object_version_number, --ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );
 COMMIT;
 --v_assignment_id :=null;
 dbms_output.put_line('Hi-'||rec.object_version_number||'-Assignment_id-'||rec.assignment_id);
 update xx_gosi_update set status='S-'||rec.emp_no where rowid=rec.rowid;
 commit;
     end loop;                      
 --dbms_output.put_line('Hi-'||ln_object_number);
    EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
                      --end loop;                      

END;

hr_person_api.update_person in HRMS

CREATE OR REPLACE FORCE VIEW apps.xxhr_people_assignment_v (person_id,
                                                            assignment_id,
                                                            assignment_number,
                                                            employee_number,
                                                            employee_name,
                                                            date_of_joining,
                                                            ORGANIZATION,
                                                            organization_id,
                                                            email_address,
                                                            POSITION,
                                                            grade,
                                                            job,
                                                            age,
                                                            national_identifier,
                                                            dob,
                                                            title,
                                                            sex,
                                                            marital_status,
                                                            religion,
                                                            nationality,
                                                            employee_group,
                                                            payroll_id,
                                                            user_person_type,
                                                            business_group_id,
                                                            effective_start_date,
                                                            effective_end_date,
                                                            overtime_yes_no
                                                           )
AS
   (SELECT per.person_id, asg.assignment_id, asg.assignment_number,
           per.employee_number, per.full_name employee_name,
           per.original_date_of_hire date_of_joining, org.NAME ORGANIZATION,
           org.organization_id, per.email_address, pos.NAME POSITION,
           gra.NAME grade, job.NAME job,
           TRUNC ((SYSDATE - per.date_of_birth) / 365) age,
           per.national_identifier, per.date_of_birth dob,
           hr_general.decode_lookup ('TITLE', per.title) title,
           hr_general.decode_lookup ('SEX', per.sex) sex,
           hr_general.decode_lookup ('MAR_STATUS',
                                     per.marital_status
                                    ) marital_status,
           hr_general.decode_lookup ('SA_RELIGION',
                                     per.per_information7
                                    ) religion,
           hr_general.decode_lookup ('NATIONALITY',
                                     per.nationality
                                    ) nationality,
           grp.group_name employee_group, asg.payroll_id,
           ptp.user_person_type, per.business_group_id,
           per.effective_start_date, per.effective_end_date,
           grp.segment4 overtime_yes_no
      FROM per_all_people_f per,
           per_person_types ptp,
           per_all_assignments_f asg,
           hr_organization_units_v org,
           per_jobs job,
           per_positions pos,
           per_grades gra,
           pay_people_groups grp
     WHERE 1 = 1
       AND per.person_type_id = ptp.person_type_id
       --AND ptp.user_person_type IN ('Employee', 'Contract Employee')--Dev
       AND ptp.user_person_type IN ('Employee', 'Contract')              --UAT
       AND TRUNC (SYSDATE) BETWEEN per.effective_start_date
                               AND per.effective_end_date
       AND per.current_employee_flag = 'Y'
       AND per.person_id = asg.person_id
       AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date
                               AND asg.effective_end_date
       AND asg.organization_id = org.organization_id
       AND asg.position_id = pos.position_id(+)
       AND asg.job_id = job.job_id(+)
       AND asg.grade_id = gra.grade_id(+)
       AND asg.people_group_id = grp.people_group_id(+));

============================================================

Below is the script for Update the Person i..e in Correction mode not update mode.

DECLARE  

   -- Local Variables  

   -- -----------------------  

    ln_object_version_number       PER_ALL_PEOPLE_F.OBJECT_VERSION_NUMBER%TYPE ;-- := 7;  

    lc_dt_ud_mode                 VARCHAR2(100)                                                                                     := NULL;  

    ln_assignment_id              PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE; --          := 33564;  

    lc_employee_number            PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE; --    := 'PRAJ_01';  

   

   -- Out Variables for Find Date Track Mode API  

   -- ----------------------------------------------------------------  

    lb_correction                  BOOLEAN;  

    lb_update                     BOOLEAN;  

    lb_update_override            BOOLEAN;   

    lb_update_change_insert       BOOLEAN;

   -- Out Variables for Update Employee API  

   -- -----------------------------------------------------------  

    ld_effective_start_date       DATE;  

    ld_effective_end_date         DATE;  

    lc_full_name                  PER_ALL_PEOPLE_F.FULL_NAME%TYPE;  

    ln_comment_id                 PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;   

    lb_name_combination_warning   BOOLEAN;  

    lb_assign_payroll_warning     BOOLEAN;  

    lb_orig_hire_warning          BOOLEAN;

    cursor c1 is

    

            select --* 

        B.OBJECT_VERSION_NUMBER,A.*

        from XXHR_PEOPLE_ASSIGNMENT_V A

        --,per_all_assignments_f B

        ,per_all_people_f B

        WHERE 1=1

        AND A.PERSON_ID=B.PERSON_ID

        AND B.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR')

        and trunc(sysdate) between b.effective_start_date and b.effective_end_date

        --AND A.EMPLOYEE_NUMBER IN('E01549','E00007')

        --AND A.EMPLOYEE_NUMBER IN('E01577','S01112')

        order by a.person_id

        ;

BEGIN

for rec in c1

 loop

  

-- Update Employee API 

    -- ---------------------------------   

    hr_person_api.update_person 

    (       -- Input Data Elements 

            -- ------------------------------ 

            p_effective_date                => sysdate, ---TO_DATE('29-JUN-2011'), 

            p_datetrack_update_mode         => 'CORRECTION', 

            p_person_id                     => REC.PERSON_ID,

            p_per_information2              => NULL, --To Update Granfather is null 

            --p_middle_names                => 'TEST', 

            --p_marital_status              => 'M', 

            -- Output Data Elements 

            -- ---------------------------------- 

           p_employee_number                => rec.employee_number, 

           p_object_version_number          => REC.object_version_number, 

           p_effective_start_date           => ld_effective_start_date, 

           p_effective_end_date             => ld_effective_end_date, 

           p_full_name                      => lc_full_name, 

           p_comment_id                     => ln_comment_id, 

           p_name_combination_warning       => lb_name_combination_warning, 

           p_assign_payroll_warning         => lb_assign_payroll_warning, 

           p_orig_hire_warning              => lb_orig_hire_warning 

    ); 

  

   COMMIT;

    dbms_output.put_line('uPDATED FOR-'||rec.object_version_number||'-Employee No-'||rec.employee_number); 

 --update xx_gosi_update set status='S-'||rec.emp_no where rowid=rec.rowid;

 commit;

     end loop;                       

     EXCEPTION 

         WHEN OTHERS THEN 

                      ROLLBACK; 

                      dbms_output.put_line(SQLERRM); 

END;