/*    Date Updated:    04/19/2005
    Updated by:        Loren Cook
    Description:    Selects Cash Balances by Fund with Fund Title for a given Period.
    Parameters:        Enter Period in the DEFINE statement below.
    Format:            Mmm-YY (example: Mar-05)
*/

ACCEPT Period PROMPT 'Enter the PERIOD (Format: Mon-YY, June-30-YY)'
ACCEPT StartUnit PROMPT 'Enter the first UNIT in your range (format: #####)'
ACCEPT EndUnit PROMPT 'Enter the last UNIT in your range (format: #####)'

SELECT

  cc.segment1 as "Fund",
  cc.segment2 as "Unit",
  cc.segment3 as "Budget Purpose",
  descript.DESCRIPTION as "Fund Title",
  gl.gl_balances.period_name as "Period",
  sum(gl.gl_balances.begin_balance_DR - gl.gl_balances.begin_balance_CR
   + gl.gl_balances.period_net_DR - gl.gl_balances.period_net_CR) as "Balance"

FROM
      gl.gl_balances,
      gl.gl_code_combinations cc,
      APPLSYS.FND_FLEX_VALUE_SETS s,
      APPLSYS.FND_FLEX_VALUES v,
      APPLSYS.FND_FLEX_VALUES_TL Descript

WHERE

    gl.gl_balances.code_combination_id = cc.code_combination_id
    and s.flex_value_set_id = v.flex_value_set_id
    and v.flex_value_id = descript.flex_value_id
    and v.flex_value = cc.segment1
    and  s.flex_value_set_id = '1002349'

    and  cc.segment2 between '&StartUnit' and '&EndUnit'
    and  gl.gl_balances.actual_flag = 'A'
    and cc.segment1 != '2010000'
    and  cc.segment7 between '11000' and '11999'
    and  gl.gl_balances.period_name = '&Period'

    and gl.gl_balances.ledger_id = '1'

group by
      cc.segment1, descript.DESCRIPTION, cc.segment2, cc.segment3, gl.gl_balances.period_name

order by
      cc.segment1,cc.segment2, cc.segment3
