Oracle 11i Vs R12 Sub Ledger Changes

Part1: SLA Overview

SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below.  As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger.

    

·         Oracle Subledger Accounting SLA is a new product that comes with the applications release R12. In many ways, it is seen as a service. That is all the Sub ledger applications like AP, AR, PA, FA, Cash Management etc use the SLA modules to create Journals.

·         Oracle Subledger Accounting (SLA) replaces the 11i Oracle Global Accounting engine, which is used for conforming to the European accounting standards.

·         Oracle SLA is a new rule-based centralized accounting engine, in the sense that all the applications can use this module and the account combinations are decided by the pre-defined rules.

·          In 11i, each product had its own accounting engine and they posted transactions according to each individual product. In R12, there is a single accounting engine across all the products. Complete accounting is maintained for every appropriate event, and all subledger entries are fully balanced and detailed. As an e.g.

See the following in 11i and R12 (where you won’t find accounting method field in R12 while you find the same in 11i).

Payables => setups => Options > payables options

Receivables => setups => system => system options 

·         Oracle Subledger Accounting module (SLA) goes under the product code XLA. That is all the objects corresponding to SLA are stored in the XLA database schema at the database level. 

In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc.

In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D combination will be passed to Oracle General Ledger via the SLA. And before you wonder... Yes, SLA module has its own set of tables to capture these accounting entries.

Please see the image below

In the image above, SLA passes to GL whatever value is fed by the Subledger, in this case A.B.C.D

There are cases where SLA module can alter or massage the CCID or code combination that is generated in Subledger before transferring the same to Oracle General Leger. This scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is customized, then the A.B.C.D code combination of Payables can be passed to Oracle General Ledger via the SLA as A.B1.C1.D instead.

The most important application however of the SLA is its ability to create shadow journals that contain different values or differing credit/debit entries for the transactions. This is the main reason why SLA module was invented/designed.  Let us take an example. For company "Vision Operation UK", it might have operations in France, which is "Vision Operation France". With UK being the parent company, the French company has to do accounting journals in formats that can be reported as per French legislation and also as per UK legislation. For example, in France an inventory item is accounted as expense, whereas in UK the inventory item is accounted as an asset. In the example below, A.B.C.D is never passed to the GL. Instead to GL A.B1.C1.D is passed to the Primary Ledger FRANCE and A2.B2.C.D is passed to the Secondary Ledger UK. The picture below shows the role that SLA plays in such scenario.

The above are main usages of Subledger accounting.

Why SLA and what does it do?

Oracle Sub Ledger accounting (SLA) is accounting hub in Oracle Application Release 12 (R12). It is used to derive all attributes required to account a transaction in Oracle General Ledger. In R12, SLA is used to derive the very basic accounting attributes like entered amount, accounted amount, Date, Currency code etc and the complex attributes like Ledger, Code Combination ID, Periods etc. After deriving these accounting attributes the transactions are then interfaced to GL from SLA. Thus in R12 no sub ledgers (AP, PO, PA etc) interfaces the transactions directly to GL, but all the transactions are interfaced to GL in following 2 steps:

1. Sub ledgers interface the data to SLA.
2. SLA derives the accounting information and interfaces the data to GL. 

SLA gives the flexibility to manage the entire accounting rule at one place, which acts as a single source of truth for GL. 

You need to submit these concurrent programs

1.      Accounting Program :- This creates Journals and generates output detailing the Transfer/Error details of events depending on input Parameters

2.      Journal Import :-  This transfers Journals to GL. May also post depending on Input Parameters

3.      Subledger Accounting Balances Update

Note: There is no separate responsibility to access SLA setup or the view the transactions generated by SLA. Rather we can access SLA setup and review accounted transactions with extended menus attached to each sub ledger module. 

Menu to access the SLA accounted transactions. 


Menu to access the SLA Setup. 

How does SLA works? 

1. Register sub ledger transactions in SLA.
After validating / approving / costing the transaction in the respective module, the sub ledger calls SLA API to create a reference of the validated transaction in SLA. This reference is known as EVENT. Events are created by calling the public API “xla_events_pub_pkg.create_events” provided by SLA. It is up to the sub ledgers on how to call the API. For example Oracle Projects call this API from concurrent program “PRC: Generate Cost Accounting Events” and Oracle Payables calls this API while user creates accounting for the Invoice.

While calling xla_events_pub_pkg.create_events, oracle passes a unique id and event class (Will discuss in next step). Unique ID can be an invoice_id or a po_distribution_id or an expenditure_item_id etc. As soon as the sub ledger generates event in SLA, SLA returns unique event_id. This event_id will then act as a reference to all the accounting entries generated by the SLA. Once event is successfully created in SLA, means that the transaction is registered in SLA for accounting.

2. How does SLA understand whether unique id is invoice_id or a po_distribution_id or an expenditure_item_id as SLA uses same table to store all the identifier?
In Step 1 we discussed that while creating the event we also need to pass event class. This event class is used to distinguish between the types of transaction passed for processing. To understand this better we will go thru the seeded oracle information. 

Navigation:
Responsibility: Payables, Vision Services (USA) 

Menu: Setup > Subledger Accounting > Accounting Methods Builder > Events > Event Modal This screen shows the hierarchical structure of different transactions that can be interfaced to SLA. Because the above screen shot is from Oracle Payables responsibility thus it shows only the payables related transactions. In the entity screen we see only those transactions that can be interfaced to the GL.

Identifiers are the unique ID that is passed to SLA from sub ledgers. Per the screenshot Oracle is passing INVOICE_ID for entity ‘AP_INVOICES’. “Identifier Column” field under Identifier window tells what column in SLA table should store INVOICE_ID. The identifier columns that can be used are SOURCE_ID_INT_1 to 4, SOURCE_ID_DATE_1 to 4, SOURCE_ID_CHAR_1 to 4 these values and columns are present in table XLA_TRANSACTION_ENTITIES.

Event Class window displays the different kind of invoice transactions that can be interfaced to GL. This level of hierarchy is known as Event class, which is further classified into Event Types. In PA we have different event types like Labor Cost, Misc Cost, Usage Cost, Supplier Cost etc. Further we could classify Supplier Cost as Expense Report and Invoices as Oracle Projects can interfaces only these 2 transactions from AP. 

Ø  Mapping between Entity and the Unique id for the entity is stored in the table XLA_ENTITY_ID_MAPPINGS:-  

Column Name	Description
APPLICATION_ID
ENTITY_CODE	Entity Code for transactions
TRANSACTION_ID_COL_NAME_1	Column name of source  table to be mapped
SOURCE_ID_COL_NAME_1	Column name of  XLA_TRANSACTION_ENTITIES that map with source column

Ø  The Above Transaction related data stores in table XLA_TRANSACTION_ENTITIES:- 

This table contains the Event Class details for the transaction. An Event Class can be AP_INVOICES, AP_PAYMENTS, ADJUSTMENTS etc
 

Column Name	Description
ENTITY_ID	Entity Id for entity code
APPLICATION_ID
LEGAL_ENTITY_ID
ENTITY_CODE	Event Class for transactions
SOURCE_ID_INT_1	Source is for the transaction for e.g. for Event Class Invoice it is invoice id, for Payment its check_id etc.

Ø  After calling the api “xla_events_pub_pkg.create_events”  to create event for the transactions Event information is stores in XLA_AE_EVENTS Table

Column Name	Description
EVENT_ID	Unique identifier for an event
APPLICATION_ID
EVENT_TYPE_CODE	Event Type for an Event Class
ENTITY_ID	Entity Id for the particular  Event Class

3. Based on the identifiers and event class, how SLA creates accounting lines?
After registering the event in SLA, we can create accounting entries by running executable XDODTEXE. This executable is provided by SLA and is used by all the sub ledgers with different concurrent program names. Around 160 concurrent programs are uses the same executable for example in Projects it is used with name “PRC: Create Accounting”. This executable does the following:
a. Gather information from base tables in sub ledgers.
b. Derive the accounting attributes based on the data fetched from sub ledgers.
c. Derive code combination id based on the business rules.
d. Create journal lines based on the seeded Journal definition.
e. Create lines in XLA_AE_HEADERS and XLA_AE_LINES.

Ø  XLA_AE_HEADERS:-  This table contains the Subledger Accounting Header Information for an Event

Column Name	Description
AE_HEADER_ID	Unique Identifier for header
APPLICATION_ID
LEDGER_ID
EVENT_ID	Event  identifier for Event Type
ENTITY_ID	Entity Id for the particular  Event Class
EVENT_TYPE_CODE	Event Type for an Event Class

Ø  XLA_AE_LINES:-  This table contains the Line level details for the transaction.

Column Name	Description
AE_HEADER_ID	Header Identifier for an event
AE_LINE_NUM	Line Identifier for header
APPLICATION_ID
CODE_COMBINATION_ID	CC ID for the transactions
GL_SL_LINK_ID	Identifier used for maintaining audit trail upon transfer of the accounting entry line to GL
SOURCE_TABLE	Contains Event class tables and populate if SLA transfer mode is ‘Details’
SOURCE_ID	Contains the Identifier for SOURCE table e.g Entity Class Payment SOURCE_TABLE can be AP_INVOICE and SOURCE_ID is INVOICE_ID. And  for Entity Class Invoice Source table can be AP_INVOICE_DISTRIBUTIONS and SOURCE_ID will be INVOICE_DISTRIBUTION_ID

Entities, Event Classes and Event Types

It is important to understand the variables within SLA engine which influence whether an accounting entry needs to be generated for a specific event within subledger like Payables or Receivables. For example in Procurement, there may be a need to generate accounting whenever a Purchase Order is encumbered. In case of SLA, the activity of "Encumbrance" against a Purchase Order is known as Event Type.

Likewise when a Payables Invoice is validated, then you may want to create an accounting entry. In this case the "Invoice Validation" is an "Event Type". And your accounting rules for Invoice Validation will be attached against this specific "Event Type".

For Payables, an INVOICE transaction and PAYMENT transactions are known as Entities within SLA.

Entities can be subdivided into various "Event Classes", for example Credit Memo, Debit Memo, Expense Reports, Invoices etc.
Further to this, against the Event classes we define Event Types, for example, whenever your Invoice is validated or cancelled or adjusted, you may want some specific accounts in the General Ledger to be impacted. Event types are therefore the types/list of events against transactions which you wish to account for in General Ledger.

Each entity is identified by unique identifier or primary key from the underlying tables.

Part 2: Journal Line Definition

The Journal Line Definition "defines" how the entire journal is built. To create any journal, one of the key things is to get the CCID or the code combination of segments. SLA needs to know where this CCID will be coming from.  You also need to know whether this CCID will be debit or this CCID will go into credit. Therefore you not just require the CCID, but you also need to decide whether a specific CCID will be debited or credited. In SLA, the "Journal Line Type" will specify whether the accounting entry is credit or debit. Also, you can then "attach something called an ADR to this Journal Line Type". The ADR returns the final code combination. Therefore Journal Line type will leverage the JLT+ADR to know which CCID is crediting and which CCID is debiting in the journal.

For each and every application there is a combination of event class and event type. Depending upon the combination of event class and event type the accounting gets triggered. The standard SLA out of the box from Oracle meets your requirement by 90%. For example you can fetch the standard accounting from payables or receivables options. However where these standards seeded accounting does not suffice, you can go and modify SLA to meet your business needs.

There is something called as Journal Entry Description. When a transaction is transferred as a journal, then every journal has credit/debit and description. The journal has description at header and also at line level. The JED allows you to generate the description of the Journal at both header and line level. For example you may want Customer Name or Customer Number in the journal description for a journal that is initiated from Oracle Receivables module. Using JED in SLA you can build header or line level descriptions.

The image below describes the end result journal that is produced by SLA

In JLT Journal Line Type, you can specify whether the entry is for credit or debit side. The Journal Line Type also provides options to do accounting for Gain/Loss of Foreign currency transactions. Further to that you can specify if SLA should merge the journal lines that have same CCID.

ADR - We specify how the account combination must be generated. We tell the system how we want the CCID should be built and transferred to the general ledger. You can either transfer the standard account as calculated within Subledger (AP or AR or PA etc) or the account generated from Subledger can be modified or replaced via ADR configuration within SLA.

Further to this, when defining ADR, you can specify the conditions under which a specific segment or CCID is returned. These conditions are like IF Conditions.

It is good to remember that the "Journal Line Definition=JED+JLT+ADR"

This is visible from the screenshot as shown below

You will notice that two "Journal Line Types" have been attached to this Journal Line Definition. The first journal line type assignment creates a credit line in the journal and the second journal line type assignment creates a debit line in the journal.

By now you would have understood the significance of Journal Line Definition. However you might be wondering how this Journal Line Definition gets associated with a Subledger transaction. For example, how does Oracle E-Business Suite decide which specific Journal Line Definition should be used when a specific event takes place against an invoice in Oracle Payables. In other words, how will SLA decide how the Journal will be constructed when an invoice is validated within Payables.

How the Journal that gets constructed using Journal Line Definition?

As seen in the image above, the Application Accounting Definitions [AAD] is attached to one or more Journal Line definition [JLD].

Effectively it means that AAD = JLD for an Event Class+ Event Type combination = ADR+JED+JLT for an Event Class+ Event Type combination

As a thumb rule, you must remember that each Application Accounting Definition [AAD] belongs to a module. Therefore if you have one Ledger implemented [ 11i Set Of Books] and two modules implemented like AP and AR, then you will have 2 AAD's defined, i.e. one for Payables and another for Receivables.

In the above picture only one Event Class of Invoice is used in AAD. However in reality you will have more than one event classes like Invoices, Payments etc associated with a AAD for Payables module.

Seeded Application Accounting Definitions are provided for each module out of the box by Oracle. However if the existing definitions do not meet your business requirements, then you can copy the existing AAD's to a custom AAD, and then make alterations to the custom AAD, which means creating custom JLT, custom JED and custom ADR as appropriate. It is important to remember that you must create a custom copy of an existing SLA component before making modifications.

In the next article you will see Subledger Accounting Method.

Part 3: Subledger Accounting Methods

As we have seen in the Part 2 of SLA, the Application Accounting definition is used to decide two things
a. When a specific event within Subledger example Payables or Receivables becomes eligible for Accounting
b. How the journal is constructed.

However, each Primary Ledger[ 11i equivalent of primary set of book] and also each secondary ledger should be able to generate Journals as per their respective legislator requirements for all the modules implemented. This is where "Subledger Accounting Method" [SLAM] comes into the play. If you recollect from previous article, Application Accounting Definition is connected to only one module like Payables or Receivables etc. However a Ledger[11i SOB equivalent] needs accounting entries to be processed across many modules. Hence SLAM provides an umbrella to join accounting entries from various modules so that they can be channeled through to Oracle General Ledger. In other words a SLAM is a collection of accounting definitions for various modules in Oracle Apps. A SLAM is then attached to the Ledger [11i equivalent of Set Of Books].

Therefore the flow of accounting entries appears as shown below





The flow represented in simple equation appears as below
Ledger Defined in GL-->SLAM-->AAD--> [Event Class and Event Type]-->Liability Lines-->[JED+JLT+ADR]
Ledger Defined in GL-->SLAM-->AAD--> [Event Class and Event Type]-->Expense Lines-->[JED+JLT+ADR]


In order to assign the SLAM to a ledger, go to the General Ledger Super User responsibility and click on menu as shown below.






Click on update against the ledger



Attach the SLAM to this ledger



You can also modify the SLA accounting options using the two screenshots as shown below

Overall SLA Diagram

The overall flow of the SLA can therefore be depicted as shown in image below

Overall, when you create new definitions in SLA, you can follow the bottom up model.

The parallel flow on top, as shown in image below is to ensure both a Credit and Debit line gets created for a Journal.

Part 4: Practice Hands On

Creating Journal Line Definition

However to create a Journal Line Definition, we need to create the following
1. Journal Entry Description for journal line description
2. Journal Line Type to mainly define credit or debit
3. Account Derivation Rules for CCID used in journal line

Therefore typically, two set of JED,JLT and ADR’s are required, with one set each for Credit line, and the other set for the debit line.
In this article, we will create these three components.

Go to a subledger like Payables and within the SLA menu as shown below, you can open the Journal Entry Description screen. Click on New to create a new JED.

Create a new Journal Entry Description named BSH_JED. We will use this for generating the Journal Line Description for both the credit and the debit lines.

The journal line description can be constructed by clicking on Details button. The description of the journal line can be a static text or dynamic text based on database sources within SLA or the combination of the two

Here we are using the Supplier Name to construct the journal line description.

Save your task.

In addition to the dynamic journal line text, SLA allows you to put conditions.
For example
IF CONDITION1=TRUE, then Journal Line Description should be abcd
ELSE IF CONDITION2=TRUE, then Journal Line Description should be defg

Click on condition button to define the condition

After defining the Journal Entry description, now we can create Journal Line Type named BSH_JLT_CREDIT for the credit line of the journal.

Fill the details as below and Specify the Transfer to GL in Summary or Detail mode. Also specify Merge, as explain in image below

Use the Payables setup option as source to build condition for JLT

In this example, we want to build a condition for “Journal Line Type” eligibility depending upon whether in the Payables Options Screen has Automatic Offset Method is set to None or Balancing or Account. In the above condition, we have placed an OR condition.

Setup > Options > Payables Options

Now we can create Line type for Accounting Class Item Expense as well

You can for example also build a condition based on Invoice Distribution type as shown below.

Next we need to define the Account Derivation Rule

Here we are creating an account derivation rule for the credit line.

An ADR can either return a full CCID or a specific segment. The values can be sourced either statically or from existing seeded dynamic sources in SLA. These seeded sources are mapped to database tables

We are stating that this specific ADR named ANIL_LIABILITY_ADR will return a constant value in company segment

In the above image you will see that the value for Company segment can be derived from the corresponding value set.

As below, it is also possible for the specific ADR to return a full segment combination. In fact you can specify conditions within an ADR. When CONDITION1=TRUE then segment combination a.b.c.d is returned or when CONDITION2=TRUE then d.e.f.g combination is returned for your account.

For the debit side, we are saying that the Account segment must always be 7450

Finally we create a Journal Line Definition.

Here everything hangs together, JLTJLD, ADR=Journal Line Definition

As shown below, we are saying that the CCID for the Credit Line of the journal will be calculated from ANIL_LIABILITY_ADR

Save you Work

And the CCID for the debit line will be calculated by the CCID value in Invoice Distribution line, with the specific segment from Account segment being replaced as per ANIL_EXPENSE_ADR.

For example, if the CCID in AP_INVOICE_DISTRIBUTION equates A.B.C.D.E.F then your journal line debit entry will be A.B.7450.D.E.F

Add one more line assignment for tax line as shown below in the picture

Create Application Accounting Definition - AAD

The purpose of AAD in SLA is to dictate which "Journal Line Definition" must be used when a specific event takes place against a specific type of transaction in a specific module like Payables or Receivables. If you recollect, the "Journal Line Definition" definition creates a Credit Line and the Debit Line of a Journal.

Oracle ships out of the box an AAD for every simply module/application that uses SLA.
Hence for each application like AP,AR,PA,PO etc there will exist an existing AAD in the Subledger Modules. However, for this example we will create a new AAD for Payables.

In the previous article you created a Journal Line Definition that is responsible for constructing a Journal. However, in AAD screen you will specify when the Journal Line Definition will be used. In this case, as per the image below, we are stating that journal line definition ANIL_JLD should be used for creating journal whenever any event occurs against an Invoice in Payables.

You can also click on "Header Assignment" button in AAD to attached "Journal Entry Description" [JED] which dictates how the Journal Header description will be constructed. If you recollect, in Journal Entry Description, we concatenate static text and dynamic content from SLA Sources[mapped to DB columns or pl/sql functions] so as to construct a description for Journal Line or Journal header.

In this article we have seen that AAD is created for each module. However, in any implementation there is a need to perform accounting for all the modules. "Vision Operation UK" might be running Payables and Receivables and also Project Accounting. Hence we need to create a SLAM-Subledger Accounting Method.

Using AAD we specify the Journal creation rules per module. In SLAM we specify how the Journals must be built for the entire organization "Vision Operation UK" across Payables and Receivables and Project Accounting. The company "Vision Operation UK " will have a legal entity in UK, and hence the SLAM will be attached to the UK Legal Entity.

Create Subledger Accounting Method

In previous article we have seen that in SLA, the "Application Accounting Definition" is created for each module in EBusiness Suite. However, in any implementation there is a need to perform accounting across various different modules. For example, a company named "Vision Operation UK" might be running Payables and Receivables and also Project Accounting. Hence we need to create a SLAM [Subledger Accounting Method] that will take care of generating the Accounting journal lines for each of the module.  Hence a SLAM is nothing but a grouping of all the AAD's possibly for a given chart of account.

Using AAD we specify the Journal creation rules per module. In SLAM we specify the applications/modules for which the Journals must be built for the entire organization such as "Vision Operation UK" across Payables and Receivables and Project Accounting. The decision of whether the journal must be created is delegated to the AAD. As for how the journal is constructed and how the accounts are derived is delegated to the Journal Line Definition.

The company such as "Vision Operation UK" will have a legal entity in UK, and hence the SLAM will be attached to the UK Legal Entity.

In the image below we are creating a SLAM named ANIL_SLAM, and attaching the AAD named ANIL_PAYABLES. This is a simplistic example, because in reality you will have the AAD's of other applications like Receivables, Project Accounting, Fixed Assets etc attached to the SLAM as well.

In the above image, click on Accounting Setups, and here you can attach the SLAM to a Ledger. To remind you, in R12, the Ledger is equivalent of set of books in 11i.

Click on Finish

Testing and explanation of SLA Configuration

In the SLA articles Part 1 to Part 3, we understood the basic concepts of Subledger Accounting.
In the SLA articles Part 4 , we configured SLA for Payables as an example.

In this article, we will test the configuration to see the results of the configuration performed in Part 4 of the SLA articles. We will also explain the results of the test.

Our SLA setup was done for Payables; hence we will create an Invoice in Payables and check the accounting entries to reconcile those against our SLA setup.

If you recollect, we created an AAD named BSH_PAYBLES which is attached to Oracle Payables module. This AAD will invoke JLD named BSH_JLD to build the journal lines when an event takes place against the Payables invoice.

In the image below we are creating an Invoice from Payables responsibility and ensuring its invoice line distribution account is 01-000-2210-0000-000.
Next we click on Actions button and validate the invoice and create accounting entries.

 

Now check the accounting generated by clicking on menu title Reports||View Accounting as shown in the image below.

You will notice that the Debit entry of 1000 is charged to 01-110-7450-0000-000 instead of being charged to 01-110-6100-0000-000 in the Invoice Distribution Line of Payables. Please note that the third segments value of 6100 from Invoice Distribution has been replaced by 7450 from ADR named BSH_EXPENSE_ADR as shown below.  The complete logic is described after this image.

Now, let us revisit how the credit line in the journal has been built. Typically Code Combination for the credit/liability accounting entry of the invoice line is picked from Payables Options setup of the operating unit. However in this case, in SLA we have hard coded the credit account to be 01.000.2220.0000.000. This was done by defining ANIL_LIABILIY_ADR as shown below. In this case the ADR returns the complete CCID because the Output Type radio button is set to Flexfield.

Hence, the SLA has generated the same Credit entry CCID for the invoice, as shown in image below
Needless to say that the ANIL_LIABILITY_ADR was attached to the Journal Line Type=ANIL_JLT_CREDIT.

SLA Data Flow

Data Flow For AP

For AP major Entities are Invoice, Payment etc. For the Invoice line accounting data flow is given below

SELECT *

FROM   ap_invoices_all

WHERE  invoice_id = 10101

Note : For entity_code 'AP_INVOICES' source_id_int_1 is invoice_id

SELECT entity_id

FROM   xla.xla_transaction_entities

WHERE  source_id_int_1 = 10101

AND    entity_code = 'AP_INVOICES'

SELECT event_id

FROM   xla_events

WHERE  entity_id = 388729

SELECT ae_header_id

FROM   xla_ae_headers

WHERE  event_id = 13105

SELECT *

FROM   xla_ae_lines

WHERE  ae_header_id = 5624396

For transaction details on Distribution level we can use the table XLA_DISTRIBUTION_LINKS

SELECT *

FROM   xla_distribution_links

WHERE  ae_header_id = 5624396

Complete join for invoice accounting

--R12 invoice_accouning

SELECT xal.*

FROM   ap_invoices_all ai

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

WHERE  ai.invoice_id = xte.source_id_int_1

AND    entity_code = 'AP_INVOICES'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    ai.invoice_id = 10101

For transaction details on distribution level we can also join the table XLA_DISTRIBUTION_LINKS

--R12 invoice_accouning at distribution level

SELECT xdl.*

FROM   ap_invoices_all ai

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

     , xla_distribution_links xdl

WHERE  ai.invoice_id = xte.source_id_int_1

AND    entity_code = 'AP_INVOICES'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    xah.application_id = xdl.application_id

AND    xe.event_id = xdl.event_id

AND    xah.ae_header_id = xah.ae_header_id

AND    xal.ae_line_num = xdl.ae_line_num

      AND    ai.invoice_id = 1267361

In case of Invoice Payment Accounting entity_code of xla_transaction_entities is ‘AP_PAYMNETS’ and source_id_int_1 is check_id

--R12 invoice payment accounting

SELECT xal.*

FROM   ap_invoice_payments_all api

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

WHERE  api.check_id = xte.source_id_int_1

AND    entity_code = 'AP_PAYMENTS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    api.invoice_id = 10101

--R12 invoice payment accounting at distribution level

SELECT xdl.*

FROM   ap_invoice_payments_all api

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

     , xla_distribution_links xdl

WHERE  api.check_id = xte.source_id_int_1

AND    entity_code = 'AP_PAYMENTS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    xah.application_id = xdl.application_id

AND    xe.event_id = xdl.event_id

AND    xah.ae_header_id = xah.ae_header_id

AND    xal.ae_line_num = xdl.ae_line_num

AND    api.invoice_id = 10101

Above query will give the accounting line records for all the payments paid by check_id used for mentioned invoice(10101).

Note: Above query will give one  accounting  line for all invoice payment paid by single check if in SAL Accounting Method GL Transfer Mode is defined as ‘Summary’.If transfer mode defined as ‘Details’ then we can see one line for each invoice payment and the source_id  column of xla_ae_lines contains the invoice_id of that line

Data Flow For AR

For the AR Cash Receipts accounting column source_id_int_1 of xla_transaction_entities contains cash_receipt_id And entity_code should be ‘RECEIPTS’

--R12 Reciept accounting

SELECT xal.*

FROM    ar_cash_receipts_all acr

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

WHERE  acr.cash_receipt_id = xte.source_id_int_1

AND    entity_code = 'RECEIPTS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    acr.cash_receipt_id = 1173

--R12 Receipt accounting at distribution level

SELECT xdl.*

FROM   ar_cash_receipts_all acr

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

     , xla_distribution_links xdl

WHERE  acr.cash_receipt_id = xte.source_id_int_1

AND    entity_code = 'RECEIPTS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    xah.application_id = xdl.application_id

AND    xe.event_id = xdl.event_id

AND    xah.ae_header_id = xah.ae_header_id

AND    xal.ae_line_num = xdl.ae_line_num

      AND    acr.cash_receipt_id = 1173

For the AR Transaction accounting column source_id_int_1 of xla_transaction_entities contains customer_trx_id And entity_code should be ‘TRANSACTIONS’

--R12 Transactions accounting

SELECT xal.*

FROM   ra_customer_trx_all rcx

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

WHERE   xte.source_id_int_1 = rcx.customer_trx_id

AND    entity_code = 'TRANSACTIONS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    rcx.customer_trx_id  = 4690

           

--R12 Transactions accounting at distribution level

SELECT xdl.*

FROM   ra_customer_trx_all rcx

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

     , xla_distribution_links xdl

WHERE  xte.source_id_int_1 = rcx.customer_trx_id

AND    entity_code = 'TRANSACTIONS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    xah.application_id = xdl.application_id

AND    xe.event_id = xdl.event_id

AND    xah.ae_header_id = xah.ae_header_id

AND    xal.ae_line_num = xdl.ae_line_num

AND    rcx.customer_trx_id  = 4690

For the AR Adjustments accounting column source_id_int_1 of xla_transaction_entities contains adjustment_id And entity_code should be 'ADJUSTMENTS'

--R12 AR Adjustments accounting

SELECT xal.*

FROM   ar_adjustments_all adj

     , xla.xla_transaction_entities xte

     , xla_events xe

     , xla_ae_headers xah

     , xla_ae_lines xal

WHERE  xte.source_id_int_1 = adj.adjustment_id

AND    entity_code = 'ADJUSTMENTS'

AND    xte.entity_id = xe.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xah.application_id = xe.application_id

AND    xah.ae_header_id = xal.ae_header_id

AND    xah.application_id = xal.application_id

AND    adj.adjustment_id = 1070

GL Information

SELECT jl.*

FROM    gl_je_lines jl

       , gl_je_headers jh

       , gl_je_batches jb

       , gl_je_sources_tl js

       , gl_je_categories_tl jc

       , gl_import_references ir

       , xla_ae_lines ael

       , gl_code_combinations glcc

  WHERE  jl.status || '' = 'P'

  AND    jl.code_combination_id = glcc.code_combination_id

  AND    jh.status = 'P'

  AND    jh.actual_flag = 'A'

  AND    jh.je_header_id = jl.je_header_id + 0

  AND    jb.je_batch_id = jh.je_batch_id + 0

  AND    jb.average_journal_flag = 'N'

  AND    js.je_source_name = jh.je_source

  AND    jc.je_category_name = jh.je_category

  AND    ir.je_header_id(+) = jl.je_header_id

  AND    ir.je_line_num(+) = jl.je_line_num

  AND    ael.gl_sl_link_id(+) = ir.gl_sl_link_id