Revenue Allocation Query

 WITH wc1_active_tree_version AS 

(

    SELECT /*+ MATERIALIZE */ ftv.tree_version_id

    FROM fnd_tree_version ftv

    WHERE LOWER(ftv.tree_structure_code) = 'gl_acct_flex'

      AND LOWER(ftv.tree_code) = 'xxxxxxxx'

      AND LOWER(ftv.status) = 'active'

      AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ftv.effective_start_date), TRUNC(SYSDATE)) 

                              AND NVL(TRUNC(ftv.effective_end_date), TRUNC(SYSDATE))

),

GL_PERIOD_DATA AS 

(

  SELECT /*+ MATERIALIZE */ START_DATE, PERIOD_YEAR, PERIOD_NUM ,PERIOD_NAME

      FROM gl_periods

      WHERE 1=1

      AND period_set_name = 'xxxxxxxxxxx'

  and PERIOD_NUM = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

THEN 12

ELSE PERIOD_NUM-1

END    

                                                 FROM gl_periods 

                                                 where PERIOD_NAME = :p_period_name --'Feb-24'  

                                                 and period_set_name = 'xxxxxxxxxxxxxxx'

                    )

            and PERIOD_YEAR = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

                                                  then PERIOD_YEAR-1

                                                              else PERIOD_YEAR

                                                            end 

          FROM gl_periods 

  where PERIOD_NAME = :p_period_name 

  --and rownum <2

   and period_set_name = 'xxxxxxxxxxxxx')

   

)

select 

journalsource

,Area_Code

,Account

,SUM(NVL(CURR_PERIOD_BAL,0)) SUM_CUR_PERIOD_BAL

,SUM(NVL(PREV_PERIOD_BAL,0)) SUM_PREV_PERIOD_BAL

,(SUM(NVL(CURR_PERIOD_BAL,0))- SUM(NVL(PREV_PERIOD_BAL,0))) SUM_DIFF

from(

SELECT    

gjs.USER_JE_SOURCE_NAME AS journalsource,

        GSVHR.Ancestor_PK1_value || ' - ' || 

            (SELECT description

             FROM FND_FLEX_VALUES_VL ffv

             WHERE ffv.FLEX_VALUE = GSVHR.Ancestor_PK1_value  -- reference the correct field

               AND ffv.VALUE_CATEGORY = 'xxxxxxxxx') AS Area_Code,

        gcc.SEGMENT4 || ' - ' || gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.SEGMENT4) AS Account

,

        (SELECT sum(NVL(a.period_net_dr,0)-NVL(a.period_net_cr,0))

FROM gl_balances a

WHERE 1=1

and a.translated_flag is null

and a.PERIOD_NAME = :p_period_name

            and a.code_combination_id=gcc.code_combination_id

) AS CURR_PERIOD_BAL,

0 AS PREV_PERIOD_BAL

FROM GL_CODE_COMBINATIONS gcc, 

     GL_SEG_VAL_HIER_RF GSVHR,

GL_JE_HEADERS gjh, 

     GL_JE_LINES gjl, 

GL_JE_SOURCES GJS

    WHERE GCC.SEGMENT1 = GSVHR.PK1_VALUE

      AND GSVHR.DISTANCE = 3 

      AND GSVHR.TREE_CODE = 'xxxxxxxx'

  and gcc.code_combination_id=gjl.code_combination_id

  and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

      AND GJS.JE_SOURCE_NAME = gjh.je_source

      AND gjl.status = 'P'

  AND gjh.PERIOD_NAME = :p_period_name

      AND ((COALESCE(NULL, :p_journal_source) IS NULL) OR (GJS.USER_JE_SOURCE_NAME IN (:p_journal_source)))

      AND GJS.LANGUAGE = 'US' 

      AND GJL.Currency_Code = 'STAT'

      AND ((COALESCE(NULL, :p_account) IS NULL) OR (gcc.segment4 IN (:p_account)))

      AND ((COALESCE(NULL, :p_area) IS NULL) OR (gsvhr.ancestor_pk1_value IN (:p_area)))

      AND GSVHR.Ancestor_PK1_value LIKE 'K%'

      AND GSVHR.tree_version_id IN (SELECT tree_version_id FROM wc1_active_tree_version)

      AND gsvhr.tree_code = 'xxxxxxxx'

      AND gsvhr.tree_structure_code = 'GL_ACCT_FLEX'

  AND gcc.SEGMENT4 >= '900000' 

--and gd.PERIOD_NAME = :p_period_name 

UNION ALL

SELECT    

gjs.USER_JE_SOURCE_NAME AS journalsource,

        GSVHR.Ancestor_PK1_value || ' - ' || 

            (SELECT description

             FROM FND_FLEX_VALUES_VL ffv

             WHERE ffv.FLEX_VALUE = GSVHR.Ancestor_PK1_value  -- reference the correct field

               AND ffv.VALUE_CATEGORY = 'xxxxxxxxxx') AS Area_Code,

        gcc.SEGMENT4 || ' - ' || gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.SEGMENT4) AS Account

,

       0 AS CURR_PERIOD_BAL,

(

SELECT sum(NVL(a.period_net_dr,0)-NVL(a.period_net_cr,0))

FROM gl_balances a

WHERE 1=1

and a.translated_flag is null

and a.PERIOD_NUM = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

THEN 12

ELSE PERIOD_NUM-1

END    

                                                 FROM gl_periods 

                                                 where PERIOD_NAME = :p_period_name --'Feb-24'  

                                                 and period_set_name = 'xxxxxxxxx'

                    )

            and a.PERIOD_YEAR = (SELECT CASE WHEN :p_period_name LIKE 'Jan%'

                                                  then PERIOD_YEAR-1

                                                              else PERIOD_YEAR

                                                            end 

          FROM gl_periods 

  where PERIOD_NAME = :p_period_name 

 

   and period_set_name = 'xxxxxxxxx')

            and a.code_combination_id=gcc.code_combination_id

)  AS PREV_PERIOD_BAL

FROM GL_CODE_COMBINATIONS gcc, 

     GL_SEG_VAL_HIER_RF GSVHR,

GL_JE_HEADERS gjh, 

     GL_JE_LINES gjl, 

GL_JE_SOURCES GJS

    WHERE GCC.SEGMENT1 = GSVHR.PK1_VALUE

      AND GSVHR.DISTANCE = 3 

      AND GSVHR.TREE_CODE = 'xxxxxxxx'

  and gcc.code_combination_id=gjl.code_combination_id

  and gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

      AND GJS.JE_SOURCE_NAME = gjh.je_source

      AND gjl.status = 'P'

  AND gjh.PERIOD_NAME = (SELECT period_name FROM GL_PERIOD_DATA)

      AND ((COALESCE(NULL, :p_journal_source) IS NULL) OR (GJS.USER_JE_SOURCE_NAME IN (:p_journal_source)))

      AND GJS.LANGUAGE = 'US' 

      AND GJL.Currency_Code = 'STAT'

      AND ((COALESCE(NULL, :p_account) IS NULL) OR (gcc.segment4 IN (:p_account)))

      AND ((COALESCE(NULL, :p_area) IS NULL) OR (gsvhr.ancestor_pk1_value IN (:p_area)))

      AND GSVHR.Ancestor_PK1_value LIKE 'K%'

      AND GSVHR.tree_version_id IN (SELECT tree_version_id FROM wc1_active_tree_version)

      AND gsvhr.tree_code = 'xxxxxxxxx'

      AND gsvhr.tree_structure_code = 'GL_ACCT_FLEX'

  AND gcc.SEGMENT4 >= '900000' 

)

GROUP BY 

journalsource

,Area_Code

,Account

order by 

journalsource

,Area_Code

,Account