Query to Get Actuals Vs Budget Values in Oracle General Ledger

SELECT  

  gb.period_name Period_Name,

  (select max(hou.name) from hr_operating_units hou where hou.set_of_books_id=gb.ledger_id)"Organization Name",

  gb.period_year Period_Year,

  gb.actual_flag,

  gb.period_Num Period_Month,

 

  gcc.segment1 "Region",

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014150

  and GCC.SEGMENT1=ffv.flex_value)as "Region Name",

 

  gcc.segment2 "Dartepment",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014168

  and GCC.SEGMENT2=ffv.flex_value)as "Department Name",

 

  gcc.segment3 "Account",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014170

  and GCC.SEGMENT3=ffv.flex_value)as "Account Name",

 

  gcc.segment4 "Intercompany",

  (select max(fft.description) from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID=1014150

  and GCC.SEGMENT4=ffv.flex_value)as "Intercompany Name",

 

  gcc.segment5 "Thematic Area",

  gcc.segment6 "Activity",

  gcc.segment7 "Project",

  --gcc.segment8  "Donor",

  gcc.segment9  "Location",

  gcc.segment10  "Contact",

  A1.DESCRIPTION "Region_Desc",

  A2.DESCRIPTION "Department_Desc",

  --A3.DESCRIPTION "Acc_Desc",

  A4.DESCRIPTION "Intercompany_Desc",

  A5.DESCRIPTION "Thematic_Desc",

  A6.DESCRIPTION "Activity_Desc",

  A7.DESCRIPTION "project_Desc",

  --A8.DESCRIPTION "Donor",

  A9.DESCRIPTION "Location_Desc",

  A10.DESCRIPTION "Contact_Desc",

  gb.currency_code Main_Currency,

 

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID = '1014154'

  and GCC.SEGMENT9=ffv.flex_value) "State",

 

  (select fft.description from fnd_flex_values_tl fft

  join fnd_flex_values ffv on

  fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID

  where ffv.FLEX_VALUE_SET_ID = '1014150'

  and GCC.SEGMENT1=ffv.flex_value) "Company",

 

  ( SUM (gb.BEGIN_BALANCE_DR) - SUM (gb.BEGIN_BALANCE_CR)) Begin_Balance,

  SUM (gb.period_net_dr) Period_Debit,

  SUM (gb.period_net_cr) Period_Credit,

  (SUM (gb.period_net_dr) - SUM (gb.period_net_cr)) Period_Activity,

  (( SUM (gb.BEGIN_BALANCE_DR) - SUM (gb.BEGIN_BALANCE_CR)) + (SUM (gb.period_net_dr) - SUM (gb.period_net_cr))) Closing_Balance

 

FROM

  gl_balances gb,

  gl_code_combinations gcc,

  fnd_flex_values_tl A1,

  fnd_flex_values_tl A2,

  --fnd_flex_values_tl A3,

  fnd_flex_values_tl A4,

  fnd_flex_values_tl A5,

  fnd_flex_values_tl A6,

  fnd_flex_values_tl A7,

  --fnd_flex_values_tl A8,

  fnd_flex_values_tl A9,

  fnd_flex_values_tl A10,

  fnd_flex_values B1,

  fnd_flex_values B2,

  --fnd_flex_values B3,

  fnd_flex_values B4,

  fnd_flex_values B5,

  fnd_flex_values B6,

  fnd_flex_values B7,

  --fnd_flex_values B8,

  fnd_flex_values B9,

  fnd_flex_values B10

WHERE 1=1

  AND b.LEDGER_ID = '1'

  AND gb.currency_code = 'USD'

  AND gb.actual_flag <> 'E'

  AND gb.code_combination_id = gcc.code_combination_id

  and b1.FLEX_VALUE_SET_ID ='1014150'

  and b2.FLEX_VALUE_SET_ID ='1014168'

  --and b3.FLEX_VALUE_SET_ID ='1014149'

  and b4.FLEX_VALUE_SET_ID ='1014150'

  and b5.FLEX_VALUE_SET_ID ='1014187'

  and b6.FLEX_VALUE_SET_ID ='1014154'

  and b7.FLEX_VALUE_SET_ID ='1014154'

  --and b8.FLEX_VALUE_SET_ID ='1014153'

  and b9.FLEX_VALUE_SET_ID ='1014154'

  and b10.FLEX_VALUE_SET_ID ='1014756'

  and  A1.FLEX_VALUE_ID = B1.FLEX_VALUE_ID

  and  A2.FLEX_VALUE_ID = B2.FLEX_VALUE_ID

  --and  A3.FLEX_VALUE_ID = B3.FLEX_VALUE_ID

  and  A4.FLEX_VALUE_ID = B4.FLEX_VALUE_ID

  and  A5.FLEX_VALUE_ID = B5.FLEX_VALUE_ID

  and  A6.FLEX_VALUE_ID = B6.FLEX_VALUE_ID

  and  A7.FLEX_VALUE_ID = B7.FLEX_VALUE_ID

  --and  A8.FLEX_VALUE_ID = B8.FLEX_VALUE_ID

  and  A9.FLEX_VALUE_ID = B9.FLEX_VALUE_ID

  and  A10.FLEX_VALUE_ID = B10.FLEX_VALUE_ID

  and B1.FLEX_VALUE = gcc.segment1

  and B2.FLEX_VALUE = gcc.segment2

  --and B3.FLEX_VALUE = gcc.segment3

  and B4.FLEX_VALUE = gcc.segment4

  and B5.FLEX_VALUE = gcc.segment5

  and B6.FLEX_VALUE = gcc.segment6

  and B7.FLEX_VALUE = gcc.segment7

  --and B8.FLEX_VALUE = gcc.segment8

  and B9.FLEX_VALUE = gcc.segment9

  and B10.FLEX_VALUE = gcc.segment10

  and gcc.segment1  in (@p_Acc_Seg1)

  and gcc.segment2  in ( @p_Acc_Seg2)

  --and gcc.segment3 in  (:p_Acc_Seg3)

  and gcc.segment4  in ( @p_Acc_Seg4)

  AND gb.period_name  in (@p_Period)

 

GROUP BY gb.period_name,

         gb.period_year,

         gb.PERIOD_NUM,

         gb.ledger_id,       

         gcc.chart_of_accounts_id,

         gb.actual_flag,

         gcc.segment1,

         gcc.segment2,

         --gcc.segment3,

         gcc.segment4,

         gcc.segment5,

         gcc.segment6,

         gcc.segment7 ,

         --gcc.segment8  ,

         gcc.segment9 ,

         gcc.segment10  ,

         gcc.code_combination_id,

         A1.DESCRIPTION,

         A2.DESCRIPTION,

         --A3.DESCRIPTION,

         A4.DESCRIPTION,

         A5.DESCRIPTION,

         A6.DESCRIPTION,

         A7.DESCRIPTION ,

         --A8.DESCRIPTION ,

         A9.DESCRIPTION ,

         A10.DESCRIPTION,

         gb.currency_code

         

Order by gb.PERIOD_YEAR, gb.PERIOD_NUM