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

转载于:https://www.cnblogs.com/benio/archive/2012/06/14/2549842.html