Thursday, September 10, 2015

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

25 comments:

hadoop training in chennai said...


We have share for wonderful information.
hadoop training in chennai | informatica training in chennai

jhansi joe said...

I have read your blog, it was good to read & I am getting some useful info's through your blog keep sharing... Informatica is an ETL tools helps to transform your old business leads into new vision. Learn informatica training in Chennai from corporate professionals with very good experience in informatica tool.
Regards,

Informatica training in chennai|Best Informatica Training In Chennai

Andrew Son said...

Interesting and worth able content is discussed here. The fact about current technology is explicitly stated over here. I do agree on your thoughts on how the influencers are taking advantage over emerging technology. Thanks for sharing this in here. Keep bloging like this.

Hadoop Training Chennai | Big Data Training Chennai | hadoop training Chennai

Roshini RS said...

Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information.
Regards,
SAP course in chennai|SAP Training in Chennai | sap course in Chennai|SAP training in chennai

Amirtha rao said...

I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
Regards,

cognos Training in Chennai|cognos Training Chennai

kovalan Jayamurugan said...

Excellent post!!! Java is most popular and efficient programming language available in the market today. It helps developers to create stunning desktop/web applications loaded with stunning functionalities. Java Course in Chennai | Best JAVA Training in Chennai

Melisa said...

Nice article, I read your post form the beginning. It’s sound interesting to read. Thanks for sharing the useful blog.
Regards,
Selenium Training in Chennai

geethu said...

Hadoop is one of the best tool which is used to handle the big data in the IT industy and it is the fastest growing field in information technology.
hadoop training in Chennai | hadoop training chennai

Bay Max said...

These provided information was really so nice,thanks for giving that post and the more skills to develop after refer that post. Your articles really impressed for me,because of all information so nice.
Thanks,
Selenium training institute in Chennai | Selenium training Chennai

Jhon Abraham said...

That is very interesting; you are a very skilled blogger. I have shared your website in my social networks! A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article.
Regards,
Best Informatica Training in Chennai | Informatica Training Center in Chennai

Jhon anderson said...

For management and troubleshooting of enormous databases, corporations square measure searching for qualified and licensed dispersion's. Hadoop experts for the duty.
Regards,
Hadoop course in Chennai | Hadoop Training institutes in Chennai

Nikshitha S said...

Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
Hadoop Training Chennai | PHP Training in Chennai

Paul Miller said...

I have read your article. It is really useful for me. Start to learn Java programming because there is a massive demand for Java developers in IT sectors. It has a great scope in future.
Selenium Training in Chennai | Software Testing Training in chennai | Digital Marketing Training in Chennai

varshini devi said...

Thanks for sharing your article.It is really interesting to read and I got more information from your site.Keep posting stuff like this.
Android Training in Chennai | Java Training in chennai | SEO Training in Chennai

Roshini RS said...

I feel satisfied with your blog, you have been delivering useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging.
Regards,
PHP Training in Chennai|PHP Course in Chennai|PHP Institutes in Chennai

Melisa said...

I have read your post, it was good to read & I am getting some useful info's through your blog keep sharing...
web designing training in chennai|web design training in chennai

Devi.Angularjs said...

I have read your blog its very attractive and impressive. I like it your blog.
corporate training companies in india corporate training companies in india corporate training in chennai corporate training in chennai | corporate training institutes in chennai corporate training companies in chennai corporate training companies in chennai Angular 2 Corporate Training Angular 2 Corporate Training

Devi.Angularjs said...

Corporate Training Companies in India Corporate Training Companies in India Corporate Training in Chennai Corporate Training in Chennai Corporate Training Companies in India Corporate Training Companies in India Java Corporate Training Companies in India Java Corporate Training Companies in India Java Corporate Training Companies in Chennai

Saradha Devi said...

keep sharing
Best Selenium Training in Chennai | Android Training in Chennai | Java Training in chennai | Webdesigning Training in Chennai

Paul Miller said...

Useful post.
Thanks,
Informatica Training Chennai | Informatica Training Institutes in Chennai

geethu said...

Nice interesting information on the latest arrived technology which helped me to get update according to the recent trends.
Salesforce Training in Chennai | Salesforce Course in Chennai

for IT the said...

I have read your blog its very attractive and impressive. I like it your blog.

Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

for IT the said...

Java Training Institutes Java Training Institutes Java EE Training in Chennai Java EE Training in Chennai Java Spring Hibernate Training Institutes in Chennai J2EE Training Institutes in Chennai J2EE Training Institutes in Chennai Core Java Training Institutes in Chennai Core Java Training Institutes in Chennai

Nandhini said...

Quite a useful post, I learned some new points here. Thanks admin please keep posting updates regularly to enlighten our knowledge.
PHP Training in Chennai | PHP Course in Chennai

تاریخ اسلام said...

https://www.quester.pk/question/does-telenor-have-3g-coverage-in-choa-saidan-shah/
https://www.quester.pk/question/does-telenor-have-3g-coverage-in-chiniot/
https://www.quester.pk/question/does-telenor-have-3g-coverage-in-chichawatni/
https://www.quester.pk/question/does-telenor-have-3g-coverage-in-chenab/
https://www.quester.pk/question/does-telenor-have-3g-coverage-in-charsadda/