Search This Blog

Sunday, March 10, 2024

Query GL Apps R12 Difference in two Years group by Months

 select * from (

  select code_combination_id, PERIOD_NUM, nvl(a3.ENTERED_YEAR_TO_DATE,0) - nvl(a4.ENTERED_YEAR_TO_DATE,0) amount from (

    SELECT bal.code_combination_id,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

             ENTERED_YEAR_TO_DATE

             ,PERIOD_NUM

             ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

    FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

   WHERE     (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

         AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

         AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

         AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

         AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

         AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL) 

         AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

         AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)  

         AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

         AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

         AND bal.LEDGER_ID = gl.LEDGER_ID

         AND cc.CHART_OF_ACCOUNTS_ID = 50320

         AND bal.LEDGER_ID = 2091

         AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD' 

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM  XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD)

GROUP BY 

          PERIOD_NUM,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

          ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

  ) A3

  full join (

    SELECT bal.code_combination_id,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

             ENTERED_YEAR_TO_DATE

             ,PERIOD_NUM

             ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

)code_comb_desc,

(SELECT kfv.concatenated_segments

FROM gl_code_combinations_kfv kfv

WHERE code_combination_id = bal.code_combination_id)segments

    FROM GL_CODE_COMBINATIONS cc, GL_BALANCES bal, GL_LEDGERS gl

   WHERE     (cc.SEGMENT1 >= :P_From_Sec OR :P_From_Sec IS NULL)

         AND (cc.SEGMENT1 <= :P_To_Sec OR :P_To_Sec IS NULL)

         AND (cc.SEGMENT2 >= :P_From_Dep OR :P_From_Dep IS NULL)

         AND (cc.SEGMENT2 <= :P_To_Dep OR :P_To_Dep IS NULL)

         AND (cc.SEGMENT3 >= :P_From_Acc1 OR :P_From_Acc1 IS NULL)

         AND (cc.SEGMENT3 <= :P_To_Acc1 OR :P_To_Acc1 IS NULL) 

         AND (cc.SEGMENT4 >= :P_From_Sub_Acc OR :P_From_Sub_Acc IS NULL)

         AND (cc.SEGMENT4 <= :P_To_Sub_Acc OR :P_To_Sub_Acc IS NULL)  

         AND bal.ACTUAL_FLAG = NVL ( :P_ACTUAL_FLAG, 'A')

         AND bal.CODE_COMBINATION_ID = cc.CODE_COMBINATION_ID

         AND bal.LEDGER_ID = gl.LEDGER_ID

         AND cc.CHART_OF_ACCOUNTS_ID = 50320

         AND bal.LEDGER_ID = 2091

         AND cc.TEMPLATE_ID IS NULL

AND :P_YTD_PTD = 'YTD' 

AND PERIOD_NAME IN (SELECT XXI.PERIOD_NAME FROM  XX_I_GL_PERIODS_I XXI WHERE PERIOD_ID BETWEEN :P_FROM_PERIOD_2 AND :P_TO_PERIOD_2)

GROUP BY 

          PERIOD_NUM,

         (  bal.begin_balance_dr_beq

          - bal.begin_balance_cr_beq

          + bal.period_net_dr_beq

          - bal.period_net_cr_beq)

          ,apps.gl_flexfields_pkg.get_concat_description

(cc.chart_of_accounts_id,

bal.code_combination_id

),bal.code_combination_id

  ) A4

  using(code_combination_id, PERIOD_NUM)

)

pivot (

  SUM(AMOUNT) as YTD_DIFF

  for PERIOD_NUM in (

    '1' as Jan, '2' as Feb, '3' as Mar,

    '4' as Apr, '5' as May, '6' as Jun,

    '7' as Jul, '8' as Aug, '9' as Sep,

    '10' as Oct, '11' as Nov, '12' as Dec, '13' as Adj

  )

);

No comments:

Post a Comment