Sunday, December 6, 2015

Oracle EBS Financials Tables Link Between Sub ledger details and GL

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_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_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_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_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_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