Oracle Apps General Ledger Tables: Oracle GL Tables and Common GL Queries

Below are Common Oracle Apps GL Tables

GL_SETS_OF_BOOKS
Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

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

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_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 inthis table.

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.

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.

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_CODE_COMBINATIONS
Stores valid account combinations foreach 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 ordetail budgeting is allowed, and others.

Common Oracle Apps General Ledger (GL Queries)
--Journal header summary
SELECT sob.SHORT_NAME"Book"
,   gjh.STATUS
,   gjh.POSTED_DATE
,   gjh.CREATION_DATE
,   gls.user_je_source_name"Source"
,   glc.user_je_category_name"Category"
,   gjh.PERIOD_NAME"Period"
,   gjb.NAME"Batch Name"
,   gjh.NAME"Journal Name"
,   gjh.currency_code"Currency"
FROM GL_JE_BATCHES gjb, GL_JE_HEADERS gjh,GL_SETS_OF_BOOKS sob,
GL_JE_SOURCES gls, GL_JE_CATEGORIES glc
WHERE gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.SET_OF_BOOKS_ID = sob.set_of_books_id
AND gls.JE_SOURCE_NAME = gjh.JE_SOURCE
AND  glc.JE_CATEGORY_NAME = gjh.JE_CATEGORY
--and gjh.NAME = 'QUV-DECLARATION TVA 11/04'    -- JOURNAL NAME  
--and gls.user_je_source_name like '%Mass%'  -- JOURNAL SOURCE
--AND glc.user_je_category_name= 'Adjustment'  -- JOURNAL CATEGORY
--and gjh.PERIOD_NAME in ('MAY-06')    -- JOURNAL PERIOD
and (trunc(gjh.creation_date) >= to_date('01/07/2002','dd/mm/yyyy')
or trunc(gjh.posted_date) >= to_date('01/07/2002','dd/mm/yyyy'))
--and substr(sob.short_name,1,2) in ('DE')
order by 1,2 desc,3,4,5,7

--Interface Details
SELECT SOB.SHORT_NAME "BOOK"
,    GLI.SET_OF_BOOKS_ID "SOB ID"
, TRUNC(GLI.ACCOUNTING_DATE) "GL DATE"
, GLI.CURRENCY_CODE "CUR"
, GLI.USER_JE_CATEGORY_NAME "JE CATEGOTY"
, GLI.USER_JE_SOURCE_NAME "JE SOURCE"
, GLI.ENTERED_DR "ENT DR"
, GLI.ENTERED_CR "ENT CR"
, GLI.ACCOUNTED_DR "ACC DR"
, GLI.ACCOUNTED_CR "ACC CR"
, GLI.SEGMENT1||'.'||GLI.SEGMENT2||'.'||GLI.SEGMENT3||'.'||GLI.SEGMENT4||'.'||GLI.SEGMENT5
||'.'||GLI.SEGMENT6||'.'||GLI.SEGMENT7||'.'||GLI.SEGMENT8||'.'||GLI.SEGMENT9||'.'||GLI.SEGMENT10 "ACCOUNT COMB."
, GLI.REFERENCE1 "REF 1"
, GLI.REFERENCE2 "REF 2"
, GLI.REFERENCE4 "REF 4"
, GLI.REFERENCE7 "REF 7"
, GLI.REFERENCE10 "REF 10"
, GLI.WARNING_CODE
, GLI.STATUS_DESCRIPTION
, GLI.STATUS
--SELECT GLI.REFERENCE10 "REF 10"
--SELECT DISTINCT GLI.SEGMENT4--,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID 
--AND GLI.WARNING_CODE IS NOT NULL
--AND GLI.STATUS <> 'P'
and GLI.USER_JE_SOURCE_NAME = 'Payables'
--and trunc(GLI.DATE_CREATED) > '01-DEC-2005'
--and GLI.CURRENCY_CODE  = 'GBP'
--and (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
-- or GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
--and GLI.USER_JE_CATEGORY_NAME = 'Bill'
--and substr(SOB.SHORT_NAME,1,2) in ('BE')
--and GLI.SEGMENT3 = '8181'
--AND GLI.STATUS_DESCRIPTION IS NOT NULL
order by 3

--Journal line details

SELECT SOB.SHORT_NAME

,   SOB.NAME

,   GJH.NAME

,   GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9 "ACCOUNT"

,   GJH.CURRENCY_CODE

,   SUM(GJL.ACCOUNTED_DR)"DR"

,   SUM(GJL.ACCOUNTED_CR)"CR"

,   SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"

,   GJL.PERIOD_NAME

FROM GL_JE_LINES GJL

, GL_JE_HEADERS GJH

, GL_CODE_COMBINATIONS GCC

, GL_SETS_OF_BOOKS SOB

WHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID

AND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID

AND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID

AND GJL.PERIOD_NAME = 'JUL-03'

--AND SOB.SHORT_NAME = 'GBMAN'

--AND GJH.NAME LIKE '%PPL%'

--AND GCC.SEGMENT1 = '85'

--AND GCC.SEGMENT2 = '70'

--AND GCC.SEGMENT3 = '0000'

--AND GCC.SEGMENT4 = '88165'

--AND GJH.STATUS = 'P'

--AND GJL.EFFECTIVE_DATE >= TO_DATE('06/04/2002','DD/MM/YYYY')

--AND GJL.EFFECTIVE_DATE <= TO_DATE('30/11/2002','DD/MM/YYYY')

GROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME

, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9

,GJH.CURRENCY_CODE, GJL.PERIOD_NAME