Saturday, June 5, 2010

Subledger Accounting (SLA) in R12



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_EVENTS xae
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.entity_id = xte.entity_id
xae.entity_id = xte.entity_id
xah.event_id = xlae.event_id
xah.ae_header_id = xdl.ae_header_id
xah.ae_line_num = xdl.ae_line_num
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 = xal.application_id
xal.application_id = xte.application_id
xte.application_id = xdl.application_id
xae.application_id = xte.application_id
xah.application_id = xae.application_id
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
'CUSTOMER_TRX_ID' or
'TRANSACTION_ID'

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
Posted by at

19 comments:

  1. AnonymousAugust 27, 2010 at 7:48 PM

    Hi,

    I have got error note , while doing the online create accounting for the payments from Payment workbench in Accounts payables r12.

    This is the note:
    --------------------------------------------
    "APP-XLA-95103: An internal error occurred. Please
    inform your system administrator or support representative that:


    An internal error has occurred in the program
    xla_events_pkg.ger_entity_id. No event exits
    for the document represented by the source information "
    --------------------------------------------

    Please help out .

    Thanks,
    ANR.

    ReplyDelete
  2. Amit ChintawarDecember 9, 2010 at 2:15 AM

    Extremely Sorry for the late comment...

    This error usually comes for cancelled invoices with no distribution. Oracle has marked it as a bug and still working on it. You can Ignore the error message because Canceled Invoices with no distributions do not prevent closing and do not require accounting.

    For more details, Refer to Metalink note- 458979.1

    Thanks
    Amit

    ReplyDelete
  3. AnonymousJanuary 13, 2011 at 7:01 AM

    Hi Amit.

    How to purge Draft accounting data in sla table?

    Thanks
    Casper

    ReplyDelete
  4. AnonymousMay 28, 2011 at 6:28 AM

    I'm seeing Inv_sub_ledger_id value populated in mtl_transaction_accounts, but could not find records in xla_distribution_links

    ReplyDelete
  5. AnonymousOctober 24, 2011 at 3:43 AM

    hi: when i transferred to gl tables from asset, there are transactions like "additions" or "depreciation" that appear in ststaus "P" what is the meaning this?, this transaction not appear in gl tables, why?, thanks. Brenda.

    ReplyDelete
  6. Amit ChintawarOctober 24, 2011 at 1:12 PM

    Status P means your accounting is Final. Have you run Transfer to GL program. Also check,
    xla_ae_headers.accounting_entry_status_code is F, xla_events.process_status_code is P and event_status_code is P

    ReplyDelete
  7. AnonymousOctober 25, 2011 at 4:56 PM

    xte.source_id_int_1 =
    'INVOICE_ID' or
    'CHECK_ID' or
    'TRX_NUMBER'

    are sure about 'TRX_NUMBER'
    may be CUSTOMER_TRX_ID?

    ReplyDelete
  8. Amit ChintawarOctober 25, 2011 at 10:54 PM

    Yes correct, thanks for the input...i have modified the post now...


    Amit

    ReplyDelete
  9. AnonymousOctober 30, 2011 at 1:55 AM

    Hi Amit,

    while joing AR/XLA/GL , I'm using (XLA headers & Lines) but not sure about Distribution links table and Transaction Entities table.

    ReplyDelete
  10. AnonymousOctober 30, 2011 at 1:57 AM

    As asked above on XLA (Dist links & Transaction Entities) table, what is difference b/w two tables, which table is used to join GL

    Tim.

    ReplyDelete
  11. Amit ChintawarOctober 31, 2011 at 8:45 AM

    XLA_DISTRIBUTION_LINKS stores the link between transactions and subledger journal entry lines. There is a one-to-many relationship between the subledger lines and distribution links. The transaction is stored in the columns UNROUNDED_ENTERED_DR, UNROUNDED_ENTERED_CR, UNROUNDED_ACCOUNTED_CR & UNROUNDED_ACCOUNTED_DR.

    If xla_distribution_links table is confusing, use xla_events table with below joins

    gl_je_lines (je_header_id, je_line_num) -> gl_import_references (je_header_id, je_line_num)

    gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)

    xla_ae_lines (applicaiton_id, ae_header_id) -> xla_ae_headers (application_id, ae_header_id)

    xla_ae_headers (application_id, event_id) -> xla_events (application_id, event_id)

    xla_events (application_id, entity_id) -> xla.xla_transaction_entities (application_id, entity_id)

    ReplyDelete
  12. Fumitaka HayashiJanuary 27, 2012 at 9:37 PM

    Hi Amit-san,

    Thanks for sharing this information！ This information is very timely and useful for me！

    ReplyDelete
  13. PremonicMarch 26, 2012 at 11:50 PM

    There is small mistake in the join conditions. Could you check once again

    xah.ae_line_num = xdl.ae_line_num

    I dont think you will have AE_LINE_NUM column in XLA_AE_HEADERS. I think it is XLA_AE_LINES. Might be type. See if you can correct it.

    ReplyDelete
  14. Mohamed TarekSeptember 9, 2012 at 10:37 AM

    Hi,
    I need to add 2 Parameters(Vendor - currency) to Account Analysis Report can u provide me with steps

    ReplyDelete
  15. AnonymousNovember 24, 2012 at 7:26 PM

    Hi,

    How the PO related tables linked to XLA tables?
    Please suggest me......

    Thanks,

    ReplyDelete
  16. AhmedSeptember 3, 2013 at 5:53 PM

    Hi,
    I need to create a Prepayment Balance report using
    XLA_DISTRIBUTION_LINK,
    XLA_AE_LINES,
    XLA_AE_HEADERS,
    AP_INVOICES_ALL,
    AP_SUPPLIERS,
    GL_CODE_COMBINATIONS.

    The output should be Prepayment Balances grouped by AP_SUPPLIERS.VENDOR_NAME, AP_INVOICES_ALL.INVOICE_NUM.

    I linked AP_INVOICES_ALL to XLA_DISTRIBUTIONS using invoice_id and applied_to_source_id_num_1 respectively. But, the output was wrong and I was advised to use ALLOC_TO_SOURCE_ID_NUM_1. What is the difference? Why would I use it?

    Below is the query I wrote (which doesn't work).

    SELECT aps.vendor_name VENDOR_NAME,
    invoice.invoice_num,
    SUM(AEL.ACCOUNTED_CR) - SUM(AEL.ACCOUNTED_DR) BALANCE
    FROM xla.xla_distribution_links xld,
    xla.xla_ae_headers aeh,
    xla.xla_ae_lines ael,
    ap.ap_invoice_distributions_all dist,
    ap.ap_invoices_all invoice,
    AP.AP_SUPPLIERS aps,
    GL.GL_CODE_COMBINATIONS codecomb
    WHERE XLD.APPLIED_TO_SOURCE_ID_NUM_1 = INVOICE.INVOICE_ID
    AND AEH.AE_HEADER_ID = XLD.AE_HEADER_ID
    AND AEL.AE_LINE_NUM = XLD.AE_LINE_NUM
    AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
    AND invoice.vendor_id = aps.vendor_id
    AND CODECOMB.CODE_COMBINATION_ID = DIST.DIST_CODE_COMBINATION_ID
    AND CODECOMB.SEGMENT1 like '13%' --account segment
    --AND INVOICE.INVOICE_NUM = '30'
    --AND xld.APPLICATION_ID = 200
    AND AEH.BALANCE_TYPE_CODE like 'A%'
    -- AND aps.vendor_name like '%company_name%'
    GROUP BY aps.vendor_name,
    invoice.invoice_num
    HAVING( SUM(AEL.ACCOUNTED_CR) - SUM(AEL.ACCOUNTED_DR) )<>0

    ReplyDelete
  17. AhmedSeptember 3, 2013 at 5:54 PM

    And this is the one that does.

    SELECT SUP.VENDOR_NAME,
    API.INVOICE_NUM,
    SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) "BALANCE"
    FROM xla.xla_ae_lines AEL,
    xla.xla_ae_headers AEH,
    ---------------------------------------------------------
    (SELECT XDL.AE_HEADER_ID,
    XDL.AE_LINE_NUM,
    XDL.APPLIED_TO_SOURCE_ID_NUM_1,
    XDL.ALLOC_TO_SOURCE_ID_NUM_1,
    APSI.INVOICE_NUM "ALLOC_TO_INVOICE_NUM",
    XDL.EVENT_CLASS_CODE
    FROM APPS.XLA_DISTRIBUTION_LINKS XDL, AP.AP_INVOICES_ALL APSI
    WHERE XDL.APPLICATION_ID = 200
    AND XDL.EVENT_CLASS_CODE IN
    ('PREPAYMENTS', 'PREPAYMENT APPLICATIONS')
    AND XDL.ROUNDING_CLASS_CODE = 'PREPAID_EXPENSE'
    AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = APSI.INVOICE_ID
    GROUP BY XDL.AE_HEADER_ID,
    XDL.AE_LINE_NUM,
    XDL.APPLIED_TO_SOURCE_ID_NUM_1,
    XDL.ALLOC_TO_SOURCE_ID_NUM_1,
    APSI.INVOICE_NUM,
    XDL.EVENT_CLASS_CODE
    ) dl, --------------VERY IMPORTANT---------------
    -----------------------------------------------------------
    GL.GL_CODE_COMBINATIONS CC,
    AP.AP_SUPPLIERS SUP,
    AP.AP_INVOICES_ALL API
    WHERE aeh.balance_type_code = 'A'
    AND aeh.ae_header_id = ael.ae_header_id
    ---hh
    AND aeh.ae_header_id = DL.ae_header_id
    AND ael.ae_line_num = DL.ae_line_num
    --
    AND ael.ledger_id = 2022
    AND ael.code_combination_id = CC.CODE_COMBINATION_ID
    AND DL.APPLIED_TO_SOURCE_ID_NUM_1 = API.INVOICE_ID --------------VERY IMPORTANT-----------------
    AND CC.SEGMENT1 = '131061'
    AND AEL.PARTY_ID = SUP.VENDOR_ID
    AND SUP.VENDOR_NAME = 'BETEKO GROUP MMC'
    AND TRUNC (AEH.ACCOUNTING_DATE) <= TO_DATE ('31-DEC-2012', 'DD-MON-YYYY')
    GROUP BY API.INVOICE_NUM, SUP.VENDOR_NAME
    HAVING SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) <> 0

    Here, they used a different method. Two tables, AP_INVOICES_ALL and XLA_DISTRIBUTION_LINKS are first joined using ALLOC_TO_SOURCE_ID_NUM_1 in the inline query. Afterwards, in the main query, APPLIED_TO_SOURCE_ID_NUM is used.

    Could you please explain the problem here?

    ReplyDelete
  18. AnonymousSeptember 27, 2013 at 7:38 PM

    My client a Health insurance company would like to connection it business System directly via SLA without using any any standars subledger. Anyone has alreday implemented that?

    ReplyDelete
  19. AnonymousOctober 15, 2016 at 3:00 AM

    If the source is Projects how would PJB_REV_DISTRIBUTIONS and PJC_COST_DIST_LINES_ALL each join to the SLA tables?

    ReplyDelete

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)