Important SQL queries and tables related to Oracle GL
Oracle Applications
--sql to find code combinitions
SELECT code_combination_id, enabled_flag
FROM gl_code_combinations gcc
,gl_sets_of_books gsb
WHERE gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
AND segment1 = p_segment1 --pass segment details as your acct structure
AND segment2 = p_segment2
AND segment3 = p_segment3
AND segment4 = p_segment4
AND segment5 = p_segment5
AND segment6 = p_segment6
AND set_of_books_id = p_set_of_book_id; --pass set of books id
--SQL to derive gl CATEGORY NAME
SELECT *
FROM gl_je_categories gjc
WHERE UPPER (gjc.user_je_category_name) = UPPER (:p_cat_name);
--SQL to derive gl SOURCE NAME
SELECT *
FROM gl_je_sources gjs
WHERE UPPER (gjs.user_je_source_name) = UPPER (:p_source_name);
-- sql to derive gl ledger id
SELECT ledger_id
FROM gl_ledgers
WHERE UPPER (NAME) = UPPER (:name);
--SQL to check THAT PERTICULAR PERIOD IS OPEN OR NOT BY PASSING PARAMETER AS PERIOD NAME AND LEDGER ID
SELECT 'X'
FROM apps.gl_period_statuses gps,
apps.fnd_application fa,
apps.gl_periods glp
WHERE gps.period_name = glp.period_name
AND gps.closing_status IN ('O', 'F')
AND fa.application_short_name = 'SQLGL'
AND gps.application_id = fa.application_id
AND glp.period_set_name = 'JLP_GL_CALENDER' --pass period set name
AND p_transaction_date BETWEEN glp.start_date AND glp.end_date
AND gps.ledger_id = v_ledger_id; --set of books id
--To find balances of all Assets, Liabilities & Shareholders Equity accounts as of Dec '13
SELECT * FROM (
SELECT
GL.GL_CODE_COMBINATIONS.SEGMENT1 LEGAL_ENTITY,
GL.GL_CODE_COMBINATIONS.SEGMENT2 ACCOUNT,
GL.GL_CODE_COMBINATIONS.SEGMENT5 ENTRY_IND,
GL.GL_BALANCES.PERIOD_NAME, GL_CODE_COMBINATIONS.ACCOUNT_TYPE ,
SUM((NVL(BEGIN_BALANCE_DR,0)-
NVL(BEGIN_BALANCE_CR,0))+
(NVL(PERIOD_NET_DR,0)-
NVL(PERIOD_NET_CR,0))) YTD_ACTUAL_AMOUNT
FROM
GL.GL_BALANCES,
GL.GL_CODE_COMBINATIONS
WHERE GL.GL_BALANCES.CODE_COMBINATION_ID=GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
AND GL.GL_CODE_COMBINATIONS.ACCOUNT_TYPE IN ('O','L','A')
AND GL.GL_BALANCES.SET_OF_BOOKS_ID = 1
AND GL.GL_BALANCES.PERIOD_NAME = 'DEC-13'
AND GL.GL_BALANCES.CURRENCY_CODE = 'USD'
AND GL.GL_BALANCES.ACTUAL_FLAG ='A'
GROUP BY SEGMENT1, SEGMENT2, SEGMENT5, PERIOD_NAME , ACCOUNT_TYPE
ORDER BY ACCOUNT_TYPE,SEGMENT1 ) WHERE YTD_ACTUAL_AMOUNT <>0 ;
GOOD WORK,KEEP IT UP...
ReplyDeleteGOOD WORK,KEEP IT UP...
ReplyDeletethis work in r12? Dont think GL.GL_BALANCES.SET_OF_BOOKS_ID is in balances in r12 and need to relate to the SET_OF_BOOKS table?
ReplyDeleteRegards
ReplyDeleteSridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.
ReplyDeleteI feel really happy to have seen your webpage and look forward to so many more entertaining times reading here.Same as your blog i found another one Oracle Fusion Financials.Actually I was looking for the same information on internet for Oracle Financials Cloud and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.