Wednesday, May 11, 2016

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect