1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
nageswarareddy

P2p tables with query 2012-08-20

P2p tables with query

  1. nageswarareddy
    Stage 1: PO Creation :
    PO_HEADERS_ALL
    select po_header_id from po_headers_all where segment1 =<po_number>;
    select * from po_headers_all where po_header_id =<po_header_id>;
    po_lines_all
    select * from po_lines_all where po_header_id =<po_header_id>;
    po_line_locations_all
    select * from po_line_locations_all where po_header_id =<po_header_id>;
    po_distributions_all
    select * from po_distributions_all where po_header_id =<po_header_id>;
    po_releases_all
    SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;
    Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
    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>);
    RCV_SHIPMENT_LINES
    select * from rcv_shipment_lines where po_header_id =<po_header_id>;
    RCV_TRANSACTIONS
    select * from rcv_transactions where po_header_id =<po_header_id>;
    RCV_ACCOUNTING_EVENTS
    SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
    (select transaction_id from rcv_transactions
    where po_header_id =<po_header_id>);
    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>);
    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>);
    MTL_MATERIAL_TRANSACTIONS
    select * from mtl_material_transactions where transaction_source_id =<po_header_id>;
    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>);
    Stage 3: Invoicing details
    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>);
    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>));
    Stage 4 : Many Time there is tie up with Project related PO
    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> );
    Stage 5 : General Ledger
    Prompt 17. GL_BC_PACKETS ..This is for encumbrances
    SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');
    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>));
    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>))