Oracle EBS Main Tables :
GL: GL_JE_HEADERS, GL_JE_LINES,
GL_CODE_COMBINATIONS,
GL_IMPORT_REFERENCES,
GL_JE_BATCHES,
GL_BALANCES,
GL_LEDGERS,
GL_DAILY_RATES,
GL_PERIOD_STATUSES
SLA: XLA_AE_HEADERS,
XLA_AE_LINES,
XLA_EVENTS,
XLA_TRANSACTION_ENTITIES,
XLA_DISTRIBUTION_LINKS
AP: AP_INVOICES_ALL,
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL,
AP_EXPENSE_REPORT_HEADERS_ALL , AP_EXPENSE_REPORT_LINES_ALL,
AP_SUPPLIERS,
AP_PAYMENT_SCHEDULES_ALL
PO:
PO_DISTRIBUTIONS ALL, PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, po_requisition_headers_all,
po_requisition_lines_all
AR:
RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
AR_CUSTOMERS,
RA_CUST_TRX_TYPES
AR_PAYMENT_SCHEUDLES_ALL,
RA_BATCH_SOURCES_ALL
ra_interface_lines_all,
ra_interface_errors_all, ra_salesrep_territories,
jtf_rs_salesreps,
ra_territories
TCA:
HZ_PARTIES,
HZ_PARTY_SITES,
HZ_LOC_ASSIGNMENTS,
HZ_LOCATIONS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HR: HR_ALL_ORGANIZATION_UNITS, HR_EMPLOYEES
FND_ID_FLEX_STRUCTURES: This stores structure
data of Key Flexfield.
FND_FLEX_VALUES_VL (FLEX VALUE)
Link are :
JE_HEADER_ID - link with header and lines table and gl header id
link with headers and gl import reference.
CODE_COMBINATION_ID - link with GL lines CCID with GL CCID
JE_LINE_NUM – link with GL lines and GL import references
GL_SL_LINK_ID and GL_SL_LINK_TABLE – link with XLA_AE_LINES and
GL Import references
APPLICATION_ID - link for SLA- XLA_AE_LINES and XLA_AE_HEADERS
INVOICE_ID - link with AP_INVOICES_ALL, AP_INVOICE_LINES_ALL and
AP_INVOICE_DISTRIBUTIONS_ALL
EVENT_ID (XLA_AE_HEADERS) with
ACCOUNTING_EVENT_ID (AP_INVOICE_DISTRIBUTIONS_All)
DISTRIBUTION_ID: line between AP_INVOICE_DISTRIBUTIONS_ALL AND PO_DISTRIBUTIONS_ALL
Query:
SELECT DISTINCT GJH.PERIOD_NAME, TRUNC (GJH.CREATION_DATE)
GL_DATE,
GJH.JE_SOURCE, GJH.JE_CATEGORY, GJH.CURRENCY_CODE,
GJL.DESCRIPTION, GJL.REFERENCE_5, GJL.REFERENCE_10,
GJL.ACCOUNTED_CR, GJL.ACCOUNTED_DR, GJL.EFFECTIVE_DATE,
GJL.REFERENCE_1, GCC.SEGMENT1, GCC.SEGMENT2, GCC.SEGMENT3,
GCC.SEGMENT4, GCC.SEGMENT5, GCC.SEGMENT6, GCC.SEGMENT7,
AERLA.JUSTIFICATION, AERLA.PROJECT_NUMBER,
AERLA.EXPENDITURE_ITEM_DATE, AERLA.RECEIPT_CURRENCY_CODE
FROM APPS.GL_JE_HEADERS
GJH,
APPS.GL_JE_LINES
GJL,
APPS.GL_CODE_COMBINATIONS GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.AP_INVOICES_ALL AIA,
APPS.AP_INVOICE_LINES_ALL
AILA,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
APPS.AP_EXPENSE_REPORT_HEADERS_ALL AERHA,
APPS.AP_EXPENSE_REPORT_LINES_ALL AERLA
--APPS.XLA_EVENTS XE,
--XLA.XLA_TRANSACTION_ENTITIES XTE,
--APPS.XLA_DISTRIBUTION_LINKS XDL,
WHERE GJH.JE_HEADER_ID =
GJL.JE_HEADER_ID
AND
GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID =
GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM =
GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID
= XAL.GL_SL_LINK_ID
AND
GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND XAL.APPLICATION_ID
= XAH.APPLICATION_ID
AND AIA.INVOICE_ID =
AILA.INVOICE_ID
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER =
AIDA.DISTRIBUTION_LINE_NUMBER
AND
AERHA.REPORT_HEADER_ID = AERLA.REPORT_HEADER_ID
AND AIA.INVOICE_ID =
AERHA.VOUCHNO
AND
AIDA.INVOICE_LINE_NUMBER = AERLA.DISTRIBUTION_LINE_NUMBER
AND
AIDA.ACCOUNTING_EVENT_ID = XAH.EVENT_ID
AND XAL.AE_HEADER_ID =
XAH.AE_HEADER_ID
AND (
GJH.JE_SOURCE = 'Payables'
AND GJH.JE_CATEGORY = 'Purchase Invoices'
)
AND GJH.CURRENCY_CODE
= 'INR'
--AND XAH.EVENT_ID=XE.EVENT_ID
--AND XAH.APPLICATION_ID=XE.APPLICATION_ID
--AND XE.ENTITY_ID=XTE.ENTITY_ID
--AND XE.APPLICATION_ID=XTE.APPLICATION_ID
--AND XTE.SOURCE_ID_INT_1=AIA.INVOICE_ID
--AND XAH.APPLICATION_ID=XTE.APPLICATION_ID
--AND XAH.ENTITY_ID=XTE.ENTITY_ID
--AND XDL.APPLICATION_ID=XAH.APPLICATION_ID
--AND
AIDA.INVOICE_DISTRIBUTION_ID=XDL.SOURCE_DISTRIBUTION_ID_NUM_1
--AND XDL.AE_HEADER_ID=XAH.AE_HEADER_ID
--AND XE.EVENT_ID=AIDA.ACCOUNTING_EVENT_ID
--AND AERHA.INVOICE_NUM LIKE '%12345%'
--AND GJH.PERIOD_NAME = 'AUG-15';
Data Model between AR and SLA
Oracle GL Important Tables:
FND_ID_FLEX_STRUCTURES
This table stores structure data
about key flexfields.
A structure is the entire
definition of all columns and meaning of the key flexfield. Structure
definitions for the accounting flexfield are stored in this table in record
where ID_FLEX_CODE=GL#.
The accounting key flexfield can
have multiple structures, each identified by a unique combination of
ID_FLEX_CODE and ID_FLEX_NUM.
Major Columns - Application_ID,
ID_FLEX_CODE, ID_FLEX_NUM
CONCATENATED_SEGMENT_DELIMITER
CROSS_SEGMENT_VALIDATION_FLAG
DYNAMIC_INSERTS_ALLOWED_FLAG
ENABLED_fLAG
FREEZE_FLEX_DEFINITION_FLAG
FREEZE_STRUCTURED_HIER_FLAG
STRUCTURE_VIEW_NAME
Journal Entry Tables
The major tables storing journal
entries related information are as follows:
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES,
GL_CODE_COMBINATIONS
GL_BALANCES
GL_JE_BATCHES:
This table stores details of
journal entry batches. A batch is group of journal entries.
In General Ledger, Journal
entries are posted in Batches.
Each row in this table includes
the batch name, status, running total debits and credits for all entries in the
batch and other information.
Major Columns
JE_BATCH_ID, NAME, STATUS :
POSTED or Unposted, POSTED_DATE
.........
GL_JE_HEADERS:
This table stores journal entry
information. There is a one to many relationship between journal entry batches
and journal entries.
Each row in this table includes
the associated , batch id, journal entry name and description, and other
information about the journal entry.
Major Columns
JE_HEADERS_ID, JE_CATEGORY, JE_SOURCE,
STATUS, PERIOD_NAME
GL_JE_LINES:
This table stores journal entry
lines. There is a one to man relationship between journal entries headers and
journal entry lines. This table contains the information about the account and
amount for each line of the entry.
Major Columns
JE_HEADERS_ID, JE_LINE_NUM, CODE_COMBINATION_ID,
PERIOD_NAME
EFFECTIVE_DATE, STATUS
GL_JE_BATCHES
GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.
GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.
GL_JE_HEADERS
GL_JE_HEADERS stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.
GL_JE_HEADERS stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.
GL_JE_LINES
GL_JE_LINES stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.
GL_JE_LINES stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.
GL_BALANCES:
This table stores net debit and net credit information for detail and summary accounts.
This information is stored actual, budget, and encumbrance amounts. It also stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
GL_BALANCES stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.
An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.
For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance.Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.
Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is not used.
GL_BALANCES is populated by the system during the GL Post and Summary Account template definition.
It is used for standard account inquires and for Financial Statement Generator(FSG) reporting.
This table stores activity rather than the actual balance.
Use the following formula to calculate the period to date balance:
(PERIOD_NET_DR- PERIOD_NE_CR)= PERIOD TO DATE BALANCE
Use this formula to calculate the year to date balance
(BEGIN_BALANCE_DR-BEGIN_BALANCE_CR)+ (PERIOD_NET_DR-PERIOD_NET_CR)=YEAR-TO-DATE BALANCE.
Major Columns:
SET_OF_BOOKS_ID. CODE_COMBINATION_ID, CURRENCY_CODE. PERIOD_NAME
ACTUAL_FLAG, ENCUMBRANCE_TYPE_ID, TRANSLATED_FLAG
PERIOD_NET_DR, PERIOD_NET_CR, BEGIN_BALANCE_DR
BEGIN_BALANCE_CR
GL_CODE_COMBINATIONS:
This table stores valid account combinations for each accounting flex field structure in Oracle General Ledger.
Associated with each account are certain codes and flags, including whether the account is enabled and whether detail posting or detail budgeting is allowed.
This table references the CODE_COMBINATION_ID which is the foreign key used by Oracle Applications to track accounting entries.
Major Columns
CODE_COMBINATION_ID, CHART_OF_ACCOUNTS_ID, ACCOUNT_TYPE
ENABLED_FLAG,
SEGMENT1... SEGMENT20.
GL_CODE_COMBINATIONS stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting or detail budgeting is allowed, and others.
Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order.
The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.
GL_IMPORT_REFERENCES
GL_IMPORT_REFERENCES stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.
GL_IMPORT_REFERENCES stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.
GL_PERIODS
GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows in this table.
GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows in this table.
GL_DAILY_RATES
GL_DAILY_RATES stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table.
It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of Dec 1, 2015, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of Dec 1, 2015.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table
GL_DAILY_RATES stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table.
It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of Dec 1, 2015, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of Dec 1, 2015.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table
