--sql to find code combinations
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 PARTICULAR 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
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 ;
