Accounting Details for a PO, Associated Receipts and Invoices

Script

REM ==================================================================

REM NAME: PO_Details.sql
REM PURPOSE: To get accounting details for a PO,associated receipts and invoices
REM BUG: 6492480
REM PRODUCT: Cost
REM PRODUCT VERSIONS: 11.5
REM PLATFORM: Generic
REM PARAMETERS: po_number
REM ==================================================================
REM
REM ===================================================================
REM CHANGE HISTORY:
REM 11-OCT-2007 sbhati 1.0 Created
REM 12-OCT-2007 dnorman Templated
REM 15-OCT-2007 dnorman 2.0 Added prompts to make script automatically runable
REM
REM ===================================================================

PROMPT Get po_header_id from po_number

select po_header_id
from po_headers_all
where segment1 ='&po_number';

/*-----------------------------------------------------------------
Section : A.
Data from PO tables
-------------------------------------------------------------------*/

Prompt 1. po_headers_all
select * from po_headers_all
where po_header_id =&&po_header_id;

Prompt 2. po_lines_all
select * from po_lines_all
where po_header_id =&&po_header_id;

Prompt 3. po_line_locations_all
select * from po_line_locations_all
where po_header_id =&&po_header_id;

Prompt 4. po_distributions_all
select * from po_distributions_all
where po_header_id =&&po_header_id;

Prompt 5. po_releases_all
SELECT * FROM po_releases_all
WHERE po_header_id =&&po_header_id;

/*-----------------------------------------------------------------
Section : B.
Data from Receving tables and inventory tables
-------------------------------------------------------------------*/

Prompt 6. rcv_shipment_headers
select * from rcv_shipment_headers
where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =&&po_header_id );

Prompt 7. rcv_shipment_lines
select * from rcv_shipment_lines
where po_header_id =&&po_header_id;

Prompt 8. rcv_transactions
select * from rcv_transactions
where po_header_id =&&po_header_id;

Prompt 9. rcv_Accounting_Events from 11i10 Onwards
SELECT * FROM rcv_Accounting_Events
WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id);

Prompt 10. rcv_receiving_sub_ledger
select * from rcv_receiving_sub_ledger
where rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id);

Prompt 11. rcv_sub_ledger_details
select * from rcv_sub_ledger_details
where rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id);

Prompt 12. mtl_material_transactions
select * from mtl_material_transactions
where transaction_source_id = &&po_header_id;

Prompt 13. mtl_transaction_accounts
select * from mtl_transaction_accounts
where transaction_id in
( select transaction_id from mtl_material_transactions
where transaction_source_id = &&po_header_id );

/*-----------------------------------------------------------------
Section : C.
Invoicing details.
Note : Pls provide the details of following queries if invoicing
data only if the issue is related to the calculation of
Accrual amount or issue with the data in POXXRVDR,POXPORRA
(Any Accrual reports)
-------------------------------------------------------------------*/

Prompt 14. ap_invoice_distributions_all
select * from ap_invoice_distributions_all
where po_distribution_id in
( select po_distribution_id from po_distributions_all
where po_header_id =&&po_header_id );

Prompt 15. ap_invoices_all
select * from ap_invoices_all
where invoice_id in
(select invoice_id from ap_invoice_distributions_all
where po_distribution_id in
( select po_distribution_id from po_distributions_all
where po_header_id =&&po_header_id ));

/*-----------------------------------------------------------------
Section : D.
Projects data
Note : Pls provide details if issue is related to the cost
transferred to projects for Inventory and receiving.
-------------------------------------------------------------------*/
Prompt 16. pa_expenditure_items_all
select *
from pa_expenditure_items_all peia
where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = &&po_header_id );

-- one more query required from RRSL to PA

/*-----------------------------------------------------------------
Section : E.
Encumbrances Data
Note : Pls provide details if issue is related to the Encumbrance
Amount mismatch for the PO.
-------------------------------------------------------------------*/
Prompt 17. gl_bc_packets
SELECT *
FROM gl_bc_packets
WHERE reference2 IN ('&&po_header_id');

/*-----------------------------------------------------------------
Section : F.
GL data
Note : Pls furnish following details only if issue is related
to the GL Transfer of receiving transactions.
-----------------------------------------------------------------*/
Prompt 18. GL_INTERFACE
SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id));

Prompt 19. gl_import_references
SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =&&po_header_id));