Oracle Apps R12 Subledger Accounting Tables and joins

XLA Table joins
GL_JE_BATCHES (je_batch_id)                        => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                      => GL_JE_LINES (je_header_id)
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 (application_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)

Transaction Entity Codes and ids
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_EVENTS:
SELECT *  FROM xla_events xe
WHERE xe.application_id = 222
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = 10066)

XLA_AE_HEADERS:
SELECT *  FROM xla_ae_headers xah  WHERE xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = 10066)

XLA_AE_LINES:
SELECT xal.*  FROM xla_ae_lines xal, xla_ae_headers xah  WHERE xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = 10066)

XLA_DISTRIBUTION_LINES:
SELECT xdl.*  FROM xla_distribution_links xdl, xla_ae_headers xah  WHERE xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = 10066)


XLA_AE_HEADER status Columns
 Table stores important status Information
GL Transfer Status Code (GL_TRANSFER_STATUS_CODE)
The GL transfer status code of the sub-ledger journal entry has one of the following values:
Not transferred
Selected to transfer
Transferred
When a sub-ledger journal entry is created and completed, the value of this column is Not transferred. The Transfer to GL process updates this column when the subledger journal entry is transferred to General Ledger.

Subledger Journal Entry Status Code (ACCOUNTING_ENTRY_STATUS_CODE)
A subledger journal entry can have a status of Draft, Final, Incomplete, Invalid or Invalid Related Entry.
Draft: The entry status is set to Draft when the following conditions are met:
Final: The entry status is set to Final when the following conditions are met:
Incomplete: The entry status is set to Incomplete when the following condition is met:
Invalid Related Entry: The entry status is set to Invalid Related Entry when the following condition is met:

The subledger journal entry is valid, but one or more of the other subledger journal entries associated with the same accounting event are invalid.


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

Oracle Apps Inventory Accounts: Inventory Accounts and their Description

Material:
An asset account that tracks material cost. For average costing, this account holds your inventory and intransit values. Once you perform transactions, you cannot change this account.

Material Overhead:  An asset account that tracks material overhead cost.

Resource:     An asset account that tracks resource cost.

Overhead:    An asset account that tracks resource and outside processing overheads.

Outside processing:     An asset account that tracks outside processing cost.

Expense:     The expense account used when tracking a non-asset item.

Sales:     The profit and loss (income statement) account that tracks the default revenue account.

Cost of Goods Sold:
 The profit and loss (income statement) account that tracks the default cost of goods sold account.

Purchase Price Variance:   The variance account used to record differences between purchase order price and standard cost. This account is not used with the average cost method.

Inventory A/P Accrual:   
The liability account that represents all inventory purchase order receipts not matched in Accounts Payable, such as the uninvoiced receipts account.

Invoice Price Variance:  
The variance account used to record differences between purchase order price and invoice price. This account is used by Accounts Payable to record invoice price variance.

Encumbrance:     An expense account used to recognize the reservation of funds when a purchase order is approved.

Average Cost Variance:   
Under average costing with negative quantity balances, this account represents the inventory valuation error caused by issuing your inventory before your receipts.

Inter-Organization Transfer Accounts
You define default inter-organization transfer accounts in the Organization Parameters
window. These accounts are defaulted when you set up shipping information in the
Inter-Organization Shipping Networks window.

Transfer Credit
The default general ledger account used to collect transfer charges when this
organization is the shipping organization. This is usually an expense account.

Purchase Price Variance
The default general ledger account used to collect the purchase price variance for
inter-organization receipts into standard cost organizations. This is usually an expense
account.

Payable
The default general ledger account used as an inter-organization clearing account when
this organization is the receiving organization. This is usually a liability account.

Receivable
The default general ledger account used as an inter-organization clearing account when
this organization is the shipping organization. This is usually an asset account.

Intransit Inventory
The default general ledger account used to hold intransit inventory value. This is
usually an asset account. For average cost organizations, this account is the default

material account.