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!

Queries to Check All SL Entries for an AP Invoices

Discussion in 'Oracle Apps Technical' started by Ramji, Mar 5, 2012.

  1. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    XLA_EVENTS

    SELECT DISTINCT xe.*
    FROM ap_invoices_all ai,
    xla_events xe,
    xla.xla_transaction_entities xte
    WHERE xte.application_id = 200
    AND xte.application_id = xe.application_id
    AND ai.invoice_id = '188101'
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id
    AND xte.entity_id = xe.entity_id
    ORDER BY
    xe.entity_id,
    xe.event_number;


    XLA_AE_HEADERS

    SELECT DISTINCT xeh.*
    FROM xla_ae_headers xeh,
    ap_invoices_all ai,
    xla.xla_transaction_entities xte
    WHERE xte.application_id = 200
    AND xte.application_id = xeh.application_id
    AND ai.invoice_id = '188101'
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id
    AND xte.entity_id = xeh.entity_id
    ORDER BY
    xeh.event_id,
    xeh.ae_header_id ASC;

    XLA_AE_LINES

    SELECT DISTINCT xel.*,
    fnd_flex_ext.get_segs('SQLGL','GL#', '50577' ,xel.code_combination_id) "Account"
    FROM xla_ae_lines xel,
    xla_ae_headers xeh,
    ap_invoices_all ai,
    xla.xla_transaction_entities xte
    WHERE xte.application_id = 200
    AND xel.application_id = xeh.application_id
    AND xte.application_id = xeh.application_id
    AND ai.invoice_id = '188101'
    AND xel.ae_header_id = xeh.ae_header_id
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id
    AND xte.entity_id = xeh.entity_id
    ORDER BY
    xel.ae_header_id,
    xel.ae_line_num ASC;


    XLA_DISTRIBUTION_LINKS

    SELECT DISTINCT xdl.*
    FROM xla_distribution_links xdl,
    xla_ae_headers xeh,
    ap_invoices_all ai,
    xla.xla_transaction_entities xte
    WHERE xte.application_id = 200
    AND xdl.application_id = xeh.application_id
    AND xte.application_id = xeh.application_id
    AND ai.invoice_id = '188101'
    AND xdl.ae_header_id = xeh.ae_header_id
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id
    AND xte.entity_id = xeh.entity_id
    ORDER BY
    xdl.event_id,
    xdl.a_header_id,
    xdl.ae_line_num ASC;

    XLA_TRANSACTION_ENTITIES

    SELECT DISTINCT xte.*
    FROM ap_invoices_all ai,
    xla.xla_transaction_entities xte
    WHERE xte.application_id = 200
    AND ai.invoice_id = '188101'
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id;


    XLA_ACCOUNTING_ERRORS

    SELECT DISTINCT xae.*
    FROM ap_invoices_all ai,
    xla_events xe,
    xla.xla_transaction_entities xte,
    xla_accounting_errors xae
    WHERE xte.application_id = 200
    AND xae.application_id = xte.application_id
    AND xte.application_id = xe.application_id
    AND ai.invoice_id = '188101'
    AND xe.event_id = xae.event_id
    AND xte.entity_code = 'AP_INVOICES'
    AND xte.source_id_int_1 = ai.invoice_id
    AND xte.entity_id = xe.entity_id;
     
    Bharat likes this.