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

434 comments:

«Oldest   ‹Older   401 – 434 of 434
reena said...

branch-operations-manager-resume-samples
business-executive-resume-samples
business-owner-resume-samples
business-to-business-sales-resume-sample-sales-resumes
cad-design-engineer-resume-samples
call-centre-jobs-resume-sample
ca-resume-samples-chartered-accountant-resume-format
cassandra-database-administrator-resume
category/accountant-resume
category/admin-resume

reena said...

branch-operations-manager-resume-samples
business-executive-resume-samples
business-owner-resume-samples
business-to-business-sales-resume-sample-sales-resumes
cad-design-engineer-resume-samples
call-centre-jobs-resume-sample
ca-resume-samples-chartered-accountant-resume-format
cassandra-database-administrator-resume
category/accountant-resume
category/admin-resume

shalini said...

nice....
category/advocate-resume
category/agriculture-forestry-fishing
category/android-developer-resume
category/assistant-professor-resume
category/chartered-accountant-resume
category/database-resume
category/design-engineer-resume
category/developer-resume
category/engineer-resume
category/entrepreneur-and-financial-services-resume

Praisy Margret said...

good..nice..
assistant-director-resume-format
assistant-director-resume-sample
assistant-professor-resume-sample
back-office-executive-resume-samples
bank-branch-manager-resume-samples
basketball-coach-resume-sample-coach-resumes
bca-fresher-resume-sample
best-general-manager-resume-example
bpo-resume-freshers-sample
bpo-resume-samples-for-freshers

shalini said...

nice....
category/advocate-resume
category/agriculture-forestry-fishing
category/android-developer-resume
category/assistant-professor-resume
category/chartered-accountant-resume
category/database-resume
category/design-engineer-resume
category/developer-resume
category/engineer-resume
category/entrepreneur-and-financial-services-resume

Praisy Margret said...

good..nice..
assistant-director-resume-format
assistant-director-resume-sample
assistant-professor-resume-sample
back-office-executive-resume-samples
bank-branch-manager-resume-samples
basketball-coach-resume-sample-coach-resumes
bca-fresher-resume-sample
best-general-manager-resume-example
bpo-resume-freshers-sample
bpo-resume-samples-for-freshers

reena said...

good ....nice...
resume/category/software-testing-resume
resume/category/sslc-resume
resume/category/storekeeper-resume
resume/category/stylist-resume
resume/category/teachers-resume
resume/category/technical-architect-resume
resume/category/web-developer-resume
cics-system-programmer-resume-example
resume/cisco-network-engineer-resume
resume/cisco-network-engineer-resume-sample


reena said...

good ....nice...
resume/category/software-testing-resume
resume/category/sslc-resume
resume/category/storekeeper-resume
resume/category/stylist-resume
resume/category/teachers-resume
resume/category/technical-architect-resume
resume/category/web-developer-resume
cics-system-programmer-resume-example
resume/cisco-network-engineer-resume
resume/cisco-network-engineer-resume-sample


shalini said...

good.....nice..
category/maintenance-resume
category/manager-resume
category/mechanical-engineering-resume
category/network-engineer-resume
category/officer-resume
category/operations-resume
category/process-associate-resume
category/quality-control-resumes
category/software-engineer-resume

shalini said...

good.....nice..
category/maintenance-resume
category/manager-resume
category/mechanical-engineering-resume
category/network-engineer-resume
category/officer-resume
category/operations-resume
category/process-associate-resume
category/quality-control-resumes
category/software-engineer-resume

Praisy Margret said...

good... nice... very useful..
assistant-director-resume-format
director-resume-sample
assistant-professor-resume-sample
back-office-executive-resume-samples
bank-branch-manager-resume-samples
basketball-coach-resume-sample-coach-resumes
bca-fresher-resume-sample
best-general-manager-resume-example
bpo-resume-freshers-sample
bpo-resume-samples-for-freshers

Praisy Margret said...

good... nice... very useful..
assistant-director-resume-format
director-resume-sample
assistant-professor-resume-sample
back-office-executive-resume-samples
bank-branch-manager-resume-samples
basketball-coach-resume-sample-coach-resumes
bca-fresher-resume-sample
best-general-manager-resume-example
bpo-resume-freshers-sample
bpo-resume-samples-for-freshers

sibiselvan said...

it is best blogs ....

civil-engineer-resume-format
client-service-executive-resume-sample
cognos-developer-resume-samples
college-lecturer-resume
college-lecturer-resume-sample
commercial-assistant-resume-sample
compliance-officer-resume-samples
computer-teacher-resume-format
computer-teacher-resume-sample
cordova-developer-resume-sample

reena said...



film-director-resume
finance-and-accounting-manager-resume-samples
finance-director-resume-examples
fire-safety-officer-resume-sample
fleet-maintenance-manager-resume-samples
format-for-resume-writing
fresher-computer-engineers-resume-sample
fresher-hr-resume-sample
fresher-hr-resume-sample-2
fresher-lecturer-resume


reena said...



film-director-resume
finance-and-accounting-manager-resume-samples
finance-director-resume-examples
fire-safety-officer-resume-sample
fleet-maintenance-manager-resume-samples
format-for-resume-writing
fresher-computer-engineers-resume-sample
fresher-hr-resume-sample
fresher-hr-resume-sample-2
fresher-lecturer-resume


uma said...



Interesting post. This is really helpful for me. I like it. Thanks for sharing.
Nice and informative article. Thanks for sharing such nice article, keep on updating.
Best Computer Service in Chennai | Best Computer Service in Kovilambakkam

uma said...




Awesome blog. Your articles really impressed for me, because of all information so nice and unique...
Best Paper Plates Shop in Chennai | Best Paper Plates Shop in Kovilambakkam

Smith said...

Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

Smith said...

Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

Village Talkies said...

Best Corporate Video Production Company in Bangalore and top Explainer Video Company in Bangalore , 3d, 2d Animation Video Makers in Chennai
Great article, Charles. Thanks for it.

cara menggugurkan kandungan dan mempercepat haid said...

I thank you for the information and articles you provided

uma said...

Awesome blog. Your articles really impressed for me, because of all information so nice and unique...
Best Paper Plates Shop in Chennai | Best Paper Plates Shop in thirukkalukundram

Tanika Co Valda said...

Great Article
Cloud Computing Projects


Networking Projects

Final Year Projects for CSE


JavaScript Training in Chennai

JavaScript Training in Chennai

The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

Radley Co Tad said...

Great Article
big data projects for cse final year students


Java Training in Chennai



Final Year Projects for CSE



Java Training in Chennai

Sowmiya R said...

wonderful blog. Very interesting to read this blog.I would like to thank you for the efforts you had made for writing this awesome blog. This is good information and really helpful for the people who need information about this.Thank you for sharing any good knowledge and thanks for fantastic efforts.

oracle training in chennai

oracle training institute in chennai

oracle training in bangalore

oracle training in hyderabad

oracle training

hadoop training in chennai

hadoop training in bangalore


adrian awadis said...

Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Supply Chain Management Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Manufacturing . By attending Oracle Fusion Financials .

cara menggugurkan kandungan said...

cool, please guidance so that I can create a blog like yours
cara melancarkan haid
aktivitas penyebab keguguran
tanda tanda keguguran
manfaat dan bahaya buah nanas
tanda tanda kehamilan
cara membaca hasil usg
hamil muda
cara mengatasi keputihan
pengaruh kista saat hamil

maurya said...

This is really a good article. Thanks for taking the time to provide us some useful and exclusive information. Keep sharing new blogs.
selenium automation testing jobs
is data science a good career
common grammar mistakes in english
common grammatical errors in english
node js developer interview questions
pega interview questions for experienced

Anonymous said...

Thanks for sharing
Click Now

Click Now

Click Now

Click Now

Click Now

Click Now

Click Now

Click Now

Click Now
for more information

Roksy said...
This comment has been removed by the author.
Oureducation said...

Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision

Best IAS Coaching in India

cara menggugurkan kandungan said...

thank you for the information provided, we are waiting for the next info

Mohsin Raza said...

It helped me, Thanks :)

Technologie said...

Die Technologie entwickelt sich schneller denn je und schneller als Sie denken. Diese aufkommende Technologie wird unsere Lebensweise verändern

«Oldest ‹Older   401 – 434 of 434   Newer› Newest»