Skip to main content

Posts

Showing posts with the label Sub ledger to Reconciliation

Mapping between GL and XLA tables

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 Relation between GL_JE_BATCHES and XLA_AE_HEADERS A.1) Group_id column subledger(XLA_AE_HEADER) mapped group_id Gl_je_batches.group_id => xla_ae_headers.group_id 1 group id will have many xla_ae_headers rows 1 group id will have 1 or many periods in xla_ae_headers 1 group id will have 1 or many ledger id in xla_ae_headers Ideally there shouldn’t be any rows in xla_ae_headers with null group_id 1 group id and 1 period(GL Period) in xla_ae_headers will make 1 batch in GL_JE_BATCH 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...

AR Adjustments to SLA Link R12

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 colum...

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

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

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 fl...

The Link between GL to SLA and reference columns in GL

The Link between GL to SLA and reference columns in GL_IMPORT_REFERENCES 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 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 Batch...

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

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