Query for PR PO Receipt Invoice Payments in Orecle EBS R12

--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

Adding Resp Via API / Deleting Executable and concurrent Program

 

 DECLARE

   lv_user_name        VARCHAR2 (20) := 'SENTHIL';

   lv_req_resp_key     VARCHAR2 (50); -- := 'APPLICATION_DEVELOPER';

   lv_description      VARCHAR2 (100) := 'Via script added';

   lv_req_resp_name    VARCHAR2 (200);

   lv_appl_shrt_name   VARCHAR2 (20);

   lv_appl_name        VARCHAR2 (50);

   lv_resp_key         VARCHAR2 (50);

  

cursor c1 is

    

    SELECT fav.application_short_name,

          fav.application_name,

          upper(frv.responsibility_name) responsibility_name,

          frv.responsibility_key

     --INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

     FROM fnd_application_vl fav, fnd_responsibility_vl frv

    WHERE frv.application_id = fav.application_id

    and upper(frv.responsibility_name) in

    (

     'FUNCTIONAL ADMINISTRATOR'

     ,'AP PAYABLES SUPER USER'   

    );

BEGIN

    DBMS_OUTPUT.put_line ('Below Responsibility Added for this user '|| lv_user_name);

    DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

    for rec in c1 

   loop

   /*SELECT fav.application_short_name,

          fav.application_name,

          frv.responsibility_name

     INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

     FROM fnd_application_vl fav, fnd_responsibility_vl frv

    WHERE frv.application_id = fav.application_id

      AND frv.responsibility_key = lv_req_resp_key; */

   fnd_user_pkg.addresp (username         => lv_user_name,

                         resp_app         => rec.application_short_name, --lv_appl_shrt_name,

                         resp_key         => rec.responsibility_key , --lv_req_resp_key,

                         security_group   => 'STANDARD',

                         description      => lv_description,

                         start_date       => SYSDATE,

                         end_date         => NULL

                                              );

   COMMIT;

  

   DBMS_OUTPUT.put_line (rec.responsibility_name);

 end loop;

  DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

      ROLLBACK;

END;

/* Deleting Executable and concurrent Program*/

SET SERVEROUTPUT ON;
 
DECLARE
   l_prog_short_name   VARCHAR2 (240);
   l_exec_short_name   VARCHAR2 (240);
   l_appl_full_name    VARCHAR2 (240);
   l_appl_short_name   VARCHAR2 (240);
   l_del_prog_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
   l_del_exec_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
   --
   -- set concurrent program and executable short name
   --
   l_prog_short_name := 'XX_TEST_CP';             -- Concurrent program short name
   l_exec_short_name := 'XX_TEST_EXEC';           -- Executable short name
   l_appl_full_name := 'XX Custom Application';   -- Application full name
   l_appl_short_name := 'XXCUST';                 -- Application Short name
 
   --
   -- Check if the program exists. if found, delete the program
   --
   IF     fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
      AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
   THEN
 
      IF l_del_prog_flag = 'Y'
	  THEN
      --
      --API call to delete Concurrent Program
      --
      fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
	  --
	  END IF;
	  --
      IF l_del_exec_flag = 'Y'
	  THEN
      --
      --API call to delete Executable
      --
      fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
	  --
	  END IF;
      COMMIT;
	  --
      DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
      DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
   --
   -- if the program does not exist in the system
   --
   ELSE
      DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;

/* Delete XML Publisher Definition and Template*/

SET SERVEROUTPUT ON

 

DECLARE

   -- Change the following two parameters

   var_templateCode    VARCHAR2 (100) := 'XX_US_BEN_NOT_EXP_ELEC';     -- Template Code

   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.

BEGIN

   FOR RS

      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,

                 T1.DATA_SOURCE_CODE,

                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME

            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2

           WHERE T1.TEMPLATE_CODE = var_templateCode

                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)

   LOOP

      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

 

      DELETE FROM XDO_LOBS

            WHERE     LOB_CODE = var_templateCode

                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

 

      DELETE FROM XDO_CONFIG_VALUES

            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND TEMPLATE_CODE = var_templateCode

                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                  AND CONFIG_LEVEL = 50;

 

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

 

      IF boo_deleteDataDef

      THEN

         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,

                                            RS.DATA_SOURCE_CODE);

 

         DELETE FROM XDO_LOBS

               WHERE LOB_CODE = RS.DATA_SOURCE_CODE

                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND LOB_TYPE IN

                            ('XML_SCHEMA',

                             'DATA_TEMPLATE',

                             'XML_SAMPLE',

                             'BURSTING_FILE');

 

         DELETE FROM XDO_CONFIG_VALUES

               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                     AND CONFIG_LEVEL = 30;

 

         DBMS_OUTPUT.PUT_LINE (

            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');

      END IF;

   END LOOP;

 

   DBMS_OUTPUT.PUT_LINE (

      'Issue a COMMIT to make the changes or ROLLBACK to revert.');

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

      DBMS_OUTPUT.PUT_LINE (

         'Unable to delete XML Publisher Template ' || var_templateCode);

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));

END;

Query to find Responsibility for the Concurrent Program

 SELECT distinct frt.responsibility_name,

               frg.request_group_name,

               frg.description,fcpt.user_concurrent_program_name

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

 --AND   fcp.concurrent_program_name = <shortname>

 AND   fcpt.user_concurrent_program_name LIKE '%XXX%Pre%%%'

 and frt.responsibility_name like '%XX_PO%'

Query for item onhand quantity & Lot wise query

 

=============Query for item onhand quantity===================

SELECT  msi.segment1 item_name,mq.subinventory_code, b.SEGMENT1||'.'||b.SEGMENT2||'.'||b.SEGMENT3||'.'||b.SEGMENT4 locator,
SUM (mq.transaction_quantity) on_hand,msi.INVENTORY_ITEM_ID,msi.organization_id
    FROM apps.org_organization_definitions ood,
         apps.mtl_onhand_quantities mq,
         apps.mtl_system_items_b msi,
         mtl_item_locations b
   WHERE 1 = 1
     AND mq.organization_id = msi.organization_id
     AND ood.organization_id(+) = msi.organization_id
     AND mq.inventory_item_id = msi.inventory_item_id
     AND mq.locator_ID = b.Inventory_location_id
     --AND mq.inventory_item_id = b.Inventory_item_id
     --and mq.organization_id = b.Organization_id
     AND msi.segment1 = nvl(:P_ITEM_CODE,msi.segment1)
     and msi.organization_id=nvl(:p_organization_id,msi.organization_id)  
     and mq.subinventory_code=nvl(:p_subinvcode,mq.subinventory_code)
GROUP BY msi.segment1, ood.organization_name, mq.subinventory_code,msi.organization_id,msi.INVENTORY_ITEM_ID,b.SEGMENT1,b.SEGMENT2,b.SEGMENT3,b.SEGMENT4

============================Lotwise Query===============================

select --*
 moqd.ORGANIZATION_ID
,OOD.ORGANIZATION_NAME
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1 item
,SUM (moqd.primary_transaction_quantity) on_hand
,SUM (moqd.secondary_transaction_quantity) secondary_onhand
,msib.PRIMARY_UOM_CODE
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,(SELECT EXPIRATION_DATE FROM MTL_LOT_NUMBERS WHERE LOT_NUMBER=moqd.LOT_NUMBER
AND inventory_item_id =MOQD.inventory_item_id AND organization_id =MOQD.organization_id )LOT_EXPIRY_DATE
FROM
 mtl_onhand_quantities_detail moqd
,mtl_system_items_b msib
,ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE 1 = 1
     --AND moqd.organization_id = 143
     /*AND moqd.subinventory_code = 'R DRY HALA'
     AND moqd.inventory_item_id = 9095
     AND moqd.owning_organization_id = 143
     AND moqd.planning_organization_id = 143*/
     AND moqd.INVENTORY_ITEM_ID=msib.INVENTORY_ITEM_ID
     AND moqd.organization_id=msib.organization_id
     AND OOD.organization_id=msib.organization_id
group by
moqd.ORGANIZATION_ID
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1  
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,msib.PRIMARY_UOM_CODE
,OOD.ORGANIZATION_NAME
,MOQD.inventory_item_id

=================Using API=====================================

create or replace function XXMADN_GET_OHQTY(
p_inv_item_id in varchar2,
p_org_id number,
p_qty_type          IN VARCHAR2
)
return number is

   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
   L_QTY NUMBER;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       msib.inventory_item_id = p_inv_item_id
            AND msib.organization_id = mp.organization_id
            AND msib.organization_id =p_org_id; -- :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number    => 1.0,
      p_init_msg_lst          => 'F',
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data,
      p_organization_id       => v_organization_id,
      p_inventory_item_id     => v_item_id,
      p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control   => FALSE,
      p_is_lot_control        => v_lot_control_code,
      p_is_serial_control     => v_serial_control_code,
      p_revision              => NULL,                          -- p_revision,
      p_lot_number            => NULL,                        -- p_lot_number,
      p_lot_expiration_date   => SYSDATE,
      p_subinventory_code     => NULL,                 -- p_subinventory_code,
      p_locator_id            => NULL,                        -- p_locator_id,
      p_onhand_source         => 3,
      x_qoh                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                  => v_rqoh,         --reservable quantity on-hand
      x_qr                    => v_qr,
      x_qs                    => v_qs,
      x_att                   => v_att,               -- available to transact
      x_atr                   => v_atr                 -- available to reserve
   );

 IF
p_qty_type='OHQ' THEN --On Hand qty
L_QTY :=v_qoh; --v_QuantityOnhand;
ELSE IF
p_qty_type='ATR' THEN --Available to Reserve
L_QTY :=v_atr;
ELSE IF
p_qty_type='ATT' THEN --Available to Transact
L_QTY :=v_att;
END IF;
END IF;
END IF;

 return L_QTY;
 --return v_atr;

   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END XXMADN_GET_OHQTY;

select XXMADN_GET_OHQTY(183645,1404,'OHQ')from dual