1. Get unlimited access to documents by upgrading to Premium Membership.
  2. We are not accepting new members currently. If you would like to join, please contact us.
    Dismiss Notice
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>))