Important SQL queries and tables related to Oracle GL

Oracle Applications

--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 ;