Oracle Query for AP to GL Transfer

AP Invoice distributions to GL transfer

    SELECT
        aia.vendor_id, aid.invoice_distribution_id,
        aia.invoice_num,
        aia.invoice_date,
        aia.creation_date,
        aid.accounting_date GL_Date,
        aid.PERIOD_NAME,
        gl.segment3 Account,
        gl.segment4 Subaccount,
        aila.po_header_id, aila.po_line_id, aila.po_release_id,aila.po_line_location_id, ACCOUNTING_LINE_CODE,
        xdl.UNROUNDED_ACCOUNTED_CR, xdl.UNROUNDED_ACCOUNTED_DR,
        aid.amount
        + NVL ((SELECT SUM (amount)
            FROM apps.ap_self_assessed_tax_dist_all
            WHERE charge_applicable_to_dist_id =aid.invoice_distribution_id),0) AP_invoice_line_amount
    FROM apps.ap_invoices_all aia,
        apps.ap_invoice_lines_all aila,
        apps.ap_invoice_distributions_all aid,
        apps.GL_CODE_COMBINATIONS gl,
        apps.XLA_DISTRIBUTION_LINKS xdl,
        apps.XLA_AE_LINES xal,
        apps.GL_IMPORT_REFERENCES GIR,
        apps.GL_JE_HEADERS GJH
    WHERE aila.invoice_id = aia.invoice_id
        and aila.line_number = aid.invoice_line_number
         and aid.period_name = '01-2019'
         and aia.invoice_num = '6017455-6-1-37-16430206'
        AND aid.invoice_id = aia.invoice_id
        AND xal.code_combination_id = gl.code_combination_id
--        AND gl.segment3 = '01101'
        AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
        AND xdl.source_distribution_type = 'AP_INV_DIST'
        AND xdl.ae_header_id = xal.ae_header_id
        AND xdl.ae_line_num = xal.ae_line_num
        AND xdl.event_id = aid.accounting_event_id
        AND gir.gl_sl_link_id = xal.gl_sl_link_id
        AND gir.je_header_id = gjh.je_header_id
       

AP Payment distributions to GL transfer

    SELECT 

        aia.vendor_id, aid.invoice_distribution_id,

        aia.invoice_num,

        aia.invoice_date,

        aia.creation_date,

        ACCOUNTING_LINE_CODE,

        gl.concatenated_segments,

        gl.segment1 corp,

        gl.segment2 cost_center,

        gl.segment3 Account,

        gl.segment4 Subaccount,

        xdl.UNROUNDED_ACCOUNTED_CR, xdl.UNROUNDED_ACCOUNTED_DR

    FROM apps.ap_invoices_all aia, ap_invoice_payments_all aipa, 

        apps.AP_PAYMENT_HIST_DISTS aid, 

        apps.GL_CODE_COMBINATIONS_kfv gl,

        apps.XLA_DISTRIBUTION_LINKS xdl,

        apps.XLA_AE_LINES xal,

        apps.GL_IMPORT_REFERENCES GIR,

        apps.GL_JE_HEADERS GJH

    WHERE aipa.invoice_id = aia.invoice_id

         and aia.invoice_num = '6017455-6-1-37-16430206'

        AND aipa.invoice_payment_id = aid.invoice_payment_id 

        AND xal.code_combination_id = gl.code_combination_id

--        AND gl.segment3 = '01101'

        AND xdl.source_distribution_id_num_1 = aid.payment_hist_dist_id 

        AND xdl.source_distribution_type = 'AP_PMT_DIST'

        AND xdl.ae_header_id = xal.ae_header_id

        AND xdl.ae_line_num = xal.ae_line_num

        AND xdl.event_id = aid.accounting_event_id

        AND gir.gl_sl_link_id = xal.gl_sl_link_id

        AND gir.je_header_id = gjh.je_header_id

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

'TRANSACTIONS' or

'RECEIPTS' or

'ADJUSTMENTS' or

'PURCHASE_ORDER' or

'AP_INVOICES' or

'AP_PAYMENTS' or

'MTL_ACCOUNTING_EVENTS' or

'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =

'INVOICE_ID' or

'CHECK_ID' or

'TRX_NUMBER'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = 'AP_PMT_DIST'

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

---------------

xdl.source_distribution_type = 'AP_INV_DIST'

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

---------------

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

---------------

xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

---------------

xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

---------------

xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

---------------

xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Queries:

In this post, we will check the Data related to the   Payable INVOICE ( Invoice_id = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

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        = '166014'

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        = '166014'

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        = '166014'

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        = '166014'

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        = '166014'

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        = '166014'

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;