Mapping between GL and XLA tables

How Subledger Accounting (XLA) Tables are link to General Ledger (GL) Tables

Assumption is

GL Entry Summarization: Summarize (By GL Date or Period)

JLT – Transfer to GL: Detail

Expected behavior is: Summarize Entries at GL_JE_HEADERS and detail at GL_JE_LINES

Refer to following post as how this is determine

Transfer to GL in Detail or Summarize Mode

1. Relation between GL_JE_BATCHES and XLA_AE_HEADERS

A.1) Group_id column subledger(XLA_AE_HEADER) mapped group_id

1. Gl_je_batches.group_id => xla_ae_headers.group_id
2. 1 group id will have many xla_ae_headers rows
3. 1 group id will have 1 or many periods in xla_ae_headers
4. 1 group id will have 1 or many ledger id in xla_ae_headers
5. Ideally there shouldn’t be any rows in xla_ae_headers with null group_id
6. 1 group id and 1 period(GL Period) in xla_ae_headers will make 1 batch in GL_JE_BATCH
7. Since 1 group id in xla ae headers can have multiple periods so 1 group id in xla_ae_headers can have multiple batches in gl_je_batches

A.2) Group_id column in GL_JE_BATCHES that does NOT mapped to subledger (XLA_AE_HEADER) group_id

1. 1 group id will have multiple GL batches or je_batch_id
2. Ideally 1 group id and 1 period will make 1 journal batch but since mostly these are manual journal batches created through spreadsheet tool (Web ADI) here 1 group id, 1 period and 1 batch name in spreadsheet uploader will make one batch in GL_JE_BATCHES.
3. Group_id in GL_JE_BATCHES could be null for manually created batched through front end, please not these manually created journals are different from spreadsheet uploaded manual journals.

2. Relation between GL_JE_HEADERS with XLA_AE_HEADERS and XLA_AE_LINES

1. Here XLA_AE_HEADERS and XLA_AE_LINES will together create GL_JE_HEADER record.
2. XLA_AE_HEADERS and XLA_AE_LINES will be summarize as follows to create journal header or 1 row in GL_JE_HEADER

Source => either from XLA_AE_HEADERS or XLA_AE_LINES

Category=> Category from XLA_AE_HEADERS (Please note AP and GL category are not same)

Ledger=> either from XLA_AE_HEADERS or XLA_AE_LINES

Period=> Period from XLA_AE_HEADERS

Currency=> Currency_Code from XLA_AE_LINES

3. Relation between GL_JE_LINES with XLA_AE_HEADERS and XLA_AE_LINES

1. Here XLA_AE_HEADERS and XLA_AE_LINES will together create lines in GL_JE_LINES table.
2. XLA_AE_HEADERS and XLA_AE_LINES will be summarize as follows to create journal line or 1 row in GL_JE_LINES

Ledger=> either from XLA_AE_HEADERS or XLA_AE_LINES

Period=> Period from XLA_AE_HEADERS

GL Code => code_combination_id from XLA_AE_LINES

Entered_Dr=> Entered_Dr from XLA_AE_LINES

Entered_Cr=> Entered_Cr from XLA_AE_LINES

Accounted_Dr=> Entered_Dr from XLA_AE_LINES

Accounted_Cr=> Entered_Cr from XLA_AE_LINES

Description=> may match to description from XLA_AE_LINES if it is detailed je line type, if it is summarize then it may not match.

4. The Link between GL to SLA and reference columns in GL_IMPORT_REFERENCES

Please refer to following post for these details.

link between XLA and GL through xla_import_reference

AR Adjustments to SLA Link R12

Accounting Data Model – Adjustments

•Where postable <> ‘N’

•Status should be ‘A’

•Distributions stored in AR_DISTRIBUTIONS_ALL (source_table=ADJ)

•Debit & credit amounts should match

•Adjustments Status = ‘W’ (Waiting for Approval) to either A or R

•Adjustments with status R (Reject) should not be processed i.e. EVENT_STATUS_CODE=N

•PROCESS_STATUS_CODE=U in XLA_EVENTS

AR_ADJUSTMENTS table
Important columns in the AR_ADJUSTMENTS table include:

ADJUSTMENT_ID column

AMOUNT column

CUSTOMER_TRX_ID column

TYPE column

PAYMENT_SCHEDULE_ID column

CODE_COMBINATION_ID column

The AR_ADJUSTMENTS table stores information about invoice adjustments. Each adjustment is stored as a unique record, based on the primary key adjustment_id. The amount column stores the amount of the adjustment. Receivables uses the customer_trx_id and payment_schedule_id to link the adjustment to the adjusted transaction and to update the amount_due_remaining and amount_adjusted columns of the adjusted transaction payment schedule in the AR_PAYMENT_SCHEDULES table.

The type column stores a description of the transaction to which the adjustment applies. Valid types include:

Charges Adjustments

Freight Adjustments

Invoice Adjustments

Line Adjustments

Tax Adjustments

The code_combination_id column stores the accounting distribution associated with the adjustment transaction.

AR Reconciliation – Internal (Within AR) and External (GL) in R12

Ref: Oracle Webcast – Oracle receivables posting & Reconciliation in R12

Data flow between AR and SLA

Queries to retrieve data from XLA tables for a given AR transaction

Q1) Get the customer_trx_id for the transaction number

SELECT *
FROM ra_customer_trx_all
WHERE trx_number = &trx_number;

Q2) Get the entity_id of the transaction

SELECT *
FROM xla_transaction_entities_upg
WHERE application_id = 222
AND ledger_id = 62
AND source_id_int_1 = &customer_trx_id_from_Q1;

Q3) Get the events associated to the entity

SELECT *
FROM xla_events
WHERE application_id = 222
AND entity_id = &entity_id_from_Q2;

Q4) Get the headers associated to the event

SELECT *
FROM xla_ae_headers
WHERE application_id = 222
AND event_id in (&event_id_from_Q3);

Q5) Get the lines associated to the headers

SELECT *
FROM xla_ae_lines
WHERE application_id = 222
AND ae_header_id in (&ae_header_id_from_Q4);

Q6) Get the distributions associated to the headers

SELECT *
FROM xla_distribution_links
WHERE application_id = 222
AND ae_header_id in (&ae_header_id_from_Q4);

Data flow during Accounting:

Step 1) Create a Transaction in AR and save it. Data gets populated in xla_transaction_entities and xla_events. Review the data in xla_events and check the event_status_code for the event_id.

Step 2) Complete the Transaction in AR. Event_status_code in xla_events table moves from status ‘I’ (Incomplete) to ‘U'(Unprocessed). Process_status_code column remains ‘U’

ACTION: COMPLETE TRANSACTION:

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID
RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1
XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID

Step 3) Create Accounting for the Transaction in ‘Draft’ mode. Data would now be populated in xla_ae_headers, xla_ae_lines and xla_distribution_links. Review  the data in all the xla tables. xla_events.process_status_code and xla_ae_headers.accounting_entry_status_code are both  ‘D'(‘Draft’) at this stage.  xla_events.event_status_code remains ‘U’ and is eligible to be accounted again.

Step 4) Create accounting in ‘Final’ mode with ‘Transfer to GL’ option. Review the data in XLA tables.
            xla_events.event_Status_code –> ‘P'(Processed)
            xla_events.process_status_code –> ‘P'(Processed)
            xla_ae_headers.accounting_entry_status_code –> ‘F’ (Final)
            xla_ae_headers.gl_transfer_status_code –> ‘Y’ (Transferred to GL)
            xla_ae_lines.gl_sl_link_id –> Populated (gets populated during final accounting)

ACTION: RUN CREATE ACCOUNTING: 

RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1
XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID
XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID

ACTION: RUN TRANSFER TO GL: 

XLA_AE_LINES.GL_SL_LINK_ID = GL_JE_LINES.GL_SL_LINK_ID
XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID

The data that actually posts to the GL comes from the XLA tables and not AR. Depending on the Application Accounting Definition (AAD) rules you have defined, one row that appears in AR could become 10 rows in XLA_DISTRIBUTION_LINKS, but when the data is posted into the GL, the accounts of the same type and ccid are merged to a single entry.

For example, a transaction could have 34 gl_dist rows, but 201 rows in ar_distribution_links, but when it actually posts, consolidates to 9 rows in gl_import_references.

The main link to bind information together is the GL_SL_LINK_ID. This field exists in GL_JE_LINES, GL_IMPORT_REFERENCES and XLA_AE_LINES tables.

Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.

Ref: Doc ID 1614389.1 & 879072.1

The Link between GL to SLA and reference columns in GL

The Link between GL to SLA and reference columns in GL_IMPORT_REFERENCES

1. Since there is always a one-to-one correspondence between XLA_AE_LINES and GL_IMPORT_REFERENCES, they are used to link GL and the subledgers. 
   GL_IMPORT_REFERENCES.gl_sl_link_id => XLA_AE_LINES.gl_sl_link_id 
   GL_JE_BATCHES.group_id => XLA_AE_HEADERS.group_id

2. 6 reference columns REFERENCE5 to REFERENCE10 in GL_IMPORT_REFERENCES can be link to entity_id, event_id, ae_header_id, ae_line_num, and either accounted_cr or accounted_dr. 

REFERENCE5 => entity_id

REFERENCE6 => event_id

REFERENCE7 => ae_header_id

REFERENCE8 => ae_line_num

REFERENCE9 => accounted_cr

REFERENCE10 => accounted_dr

Q1. How many group_ids are created during the transfer process when there is a primary ledger and associated ALC/reporting ledger?

The primary ledger data and associated ALC/reporting ledger data goes into one group_id. Data from this one group_id can be split across multiple GL Batches based on other criteria, such as je_category/date/period. However, one GL Batch can only be associated to one group_id.

Q2. How many group_ids are created during transfer process when there is a primary ledger and a secondary ledger?

Two group IDs are created–One for the primary ledger and one for the secondary ledger. Two journal import processes are spawned in this case, one for each group_id. If there is failure in the journal import for either ledger, the transfer is rolled back for both ledgers.

Q3. Which process push the data into which tables?

Create Accounting : Subledger tables and xla_events -> xla_ae_headers, xla_ae_lines and xla_distribution_links
Transfer to GL: xla_ae_headers and xla_ae_lines -> gl_je_headers and gl_je_lines
Post to GL : gl_je_headers and gl_je_lines -> gl_balances
Subledger Accounting Balances Update : xla_ae_headers and xla_ae_lines -> xla_control_balances

Q4. What are the important tables for sub-ledger accounting?

xla_transaction_entities
xla_events
xla_ae_headers
xla_ae_lines
xla_distribution_links
xla_control_balances

ref: Doc ID 876190.1 & 1325133.1

Columns in XLA_AE_HEADERS and Transfer Status

What columns in XLA_AE_HEADERS indicate the Transfer Status of accounting data in subledger?

The XLA_AE_HEADERS table shows the transfer status of the accounting data, after successful transfer to the GL and Journal Import:

XLA_AE_HEADERS.gl_transfer_status_code = Y
XLA_AE_HEADERS.gl_transfer_date is not null
XLA_AE_HEADERS.group_id is not null

For 11i data upgraded to R12, the transfer status can be determined using the following

XLA_AE_HEADERS.gl_transfer_status_code = Y
XLA_AE_HEADERS.gl_transfer_date is null
XLA_AE_HEADERS.group_id is null
XLA_AE_HEADERS.upg_batch_id is not null  –> indicates 11i data

EVENT_STATUS_CODE and PROCESS_STATUS_CODE in XLA_EVENTS Table R12

EVENT_STATUS_CODE and PROCESS_STATUS_CODE in XLA_EVENTS Table

– EVENT_STATUS_CODE

– PROCESS_STATUS_CODE

Note:  If an event has event_status_code = P and process_status_code = F but is still in xla_ae_headers with GL_Transfer = No, then the event has successfully processed and just needs to be manually transferred to GL.

Manual GL Transfer can be completed via the concurrent request Transfer Journal Entries to GL – Assets (or for other modules = Transfer Journal Entries to GL), 

Ref: Doc ID 1457611.1

ACCOUNTING_ENTRY_STATUS_CODE Column In XLA_AE_HEADERS Table R12

What Does “ACCOUNTING_ENTRY_STATUS_CODE” Column Represent In “XLA_AE_HEADERS” Table?

The Column represents Accounting Status of a Transaction Event. The following describes the Values and their Descriptions:

 1. If ACCOUNTING_ENTRY_STATUS_CODE = ‘F’ then event is accounted successfully.

2. If ACCOUNTING_ENTRY_STATUS_CODE = ‘N’ then event is still not processed.

3. If ACCOUNTING_ENTRY_STATUS_CODE = ‘I’ then event is failed.

4. If ACCOUNTING_ENTRY_STATUS_CODE = ‘R’ then event is in Error.

5. If ACCOUNTING_ENTRY_STATUS_CODE = ‘D’ then event is Draft Accounting Entry.

Ref: Doc Id 1581386.1

What are the tables involved in the Transfer to GL and GL posting?

Subledger Tables 
XLA_AE_HEADERS 
XLA_AE_LINES 
XLA_DISTRIBUTION_LINKS 

a) Transfer Journal Entries to GL (XLAGLTRN) process takes the subledger journals and inserts records into the Interface Tables 

Interface Tables 
GL_INTERFACE / XLA_GLT_<groupid> 

b) Journal Import (GLLEZL) then reads from the interface table and creates records in the GL Tables 

GL Tables 
GL_JE_BATCHES 
GL_JE_HEADERS 
GL_JE_LINES 
GL_IMPORT_REFERENCES 

c) GL Posting process then posts to the GL_BALANCES table. 

Ref: Doc ID 876190.1