ACCEPT bp PROMPT 'Enter the BUDGET PURPOSE (format: ######)'
ACCEPT Period PROMPT 'Enter the PERIOD (format: Mon-YY, June-30-YY)'



/*	Created by:	Loren Cook
	Creation Date:  10/26/07
	Description:	Pulls cash balances by object code for a given period by BP.
*/

select
  gl_code_combinations.segment8 as "Object",
  descript.DESCRIPTION as "Object Description",
  gl.gl_balances.period_name as "Period",
  sum(gl_balances.begin_balance_DR) - sum(gl_balances.begin_balance_CR)
   + sum(gl_balances.period_net_DR) - sum(gl_balances.period_net_CR) as "Balance"

from  gl.gl_balances, gl.gl_code_combinations,
	APPLSYS.FND_FLEX_VALUE_SETS sets,
      APPLSYS.FND_FLEX_VALUES value,
	  APPLSYS.FND_FLEX_VALUES_TL Descript

WHERE gl_balances.code_combination_id = gl_code_combinations.code_combination_id
  	and sets.flex_value_set_id = value.flex_value_set_id
    and value.flex_value_id = descript.flex_value_id
    and value.flex_value = gl.gl_code_combinations.segment8
    and sets.flex_value_set_id = '1002356'

  and  gl.gl_balances.period_name = '&Period'
  and  gl_balances.actual_flag = 'A'
  and  gl_code_combinations.segment3 = '&BP'
  and  gl_code_combinations.segment7 between '11000' and '11999'
  and  gl_code_combinations.segment8 <> 'BCSH'
  and gl.gl_balances.ledger_id = '1'

group by gl_code_combinations.segment8, descript.DESCRIPTION, gl.gl_balances.period_name

having
	  sum(gl_balances.begin_balance_DR) - sum(gl_balances.begin_balance_CR)
	  + sum(gl_balances.period_net_DR) - sum(gl_balances.period_net_CR) <> '0'


order by gl_code_combinations.segment8
