APPS.CE_INTEREST_CALC SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 10

select
  ACCOUNT_ID
from CE_CASHPOOL_SUB_ACCTS SA
where
  SA.TYPE IN ('CONC', 'ACCT', 'NEST')
AND  SA.CASHPOOL_ID = p_cashpool_id
order by ACCOUNT_ID;

Line: 25

select
  FROM_DATE
, TO_DATE
, VALUE_DATED_BALANCE
, nvl(INTEREST_RATE,0)
, INTEREST_CALC_DETAIL_ID
from CE_INT_CALC_DETAILS_GT
where INTEREST_SCHEDULE_ID = p_interest_schedule_id
--and BANK_ACCOUNT_ID 	   = p_bank_account_id
and INTEREST_ACCT_TYPE 	   = p_interest_acct_type
and CASHPOOL_ID 	   = p_cashpool_id
UNION ALL
select
  FROM_DATE
, TO_DATE
, VALUE_DATED_BALANCE
, nvl(INTEREST_RATE,0)
, INTEREST_CALC_DETAIL_ID
from CE_INT_CALC_DETAILS_GT
where INTEREST_SCHEDULE_ID = p_interest_schedule_id
and BANK_ACCOUNT_ID 	   = p_bank_account_id
and INTEREST_ACCT_TYPE 	   = p_interest_acct_type
and CASHPOOL_ID 	  is null
order by from_date;

Line: 57

select from_date
, rownum
, INTEREST_CALC_DETAIL_ID
from CE_INT_CALC_DETAILS_GT
where
  INTEREST_SCHEDULE_ID  = p_interest_schedule_id
--and BANK_ACCOUNT_ID 	= p_bank_account_id
and INTEREST_ACCT_TYPE 	= p_interest_acct_type
and CASHPOOL_ID 	= p_cashpool_id
UNION ALL
select from_date
, rownum
, INTEREST_CALC_DETAIL_ID
from CE_INT_CALC_DETAILS_GT
where
  INTEREST_SCHEDULE_ID  = p_interest_schedule_id
and BANK_ACCOUNT_ID 	= p_bank_account_id
and INTEREST_ACCT_TYPE 	= p_interest_acct_type
and CASHPOOL_ID is null
order by from_date desc
;

Line: 85

select from_date
, rownum
, INTEREST_CALC_DETAIL_ID
from CE_INT_CALC_DETAILS_GT
where
  INTEREST_SCHEDULE_ID  = p_interest_schedule_id
and BANK_ACCOUNT_ID 	= p_bank_account_id
and INTEREST_ACCT_TYPE 	= p_interest_acct_type
and CASHPOOL_ID is null
order by from_date desc
;

Line: 103

 select
   BAB.BANK_ACCOUNT_ID
 , max(BAB.BALANCE_DATE)   BALANCE_DATE_FROM
 , BAB.VALUE_DATED_BALANCE
 , IBR.BALANCE_RANGE_ID
 , (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
	and ir.effective_date =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= p_from_date --'31-DEC-2003' --max(BAB.BALANCE_DATE)
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		)
  ) INTEREST_RATES
 , 'Y' FIRST_ROW
 , rownum
 from
   CE_BANK_ACCT_BALANCES BAB
 --, CE_BANK_ACCOUNTS	BA
 , CE_INTEREST_SCHEDULES  cIS
 , CE_INTEREST_BAL_RANGES	IBR
 WHERE
      cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
  and cIS.INTEREST_SCHEDULE_ID =  p_interest_schedule_id --10002
  --AND  cIS.INTEREST_SCHEDULE_ID =  ba.INTEREST_SCHEDULE_ID
  --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
  AND BAB.BALANCE_DATE <= p_from_date --'31-DEC-2003'
  and BAB.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
  and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
  AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
  group by
  BAB.BANK_ACCOUNT_ID
  , BAB.BALANCE_DATE
  , BAB.VALUE_DATED_BALANCE
  , IBR.BALANCE_RANGE_ID
  , rownum
  having max(BAB.BALANCE_DATE) =  (select max(bab2.balance_date)
					from CE_BANK_ACCT_BALANCES BAB2
					where BAb2.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
					and BAB2.BALANCE_DATE <= p_from_date) --'01-May-2004')
 UNION ALL
 select
   BAB.BANK_ACCOUNT_ID
 , BAB.BALANCE_DATE   BALANCE_DATE_FROM
 , BAB.VALUE_DATED_BALANCE
 , IBR.BALANCE_RANGE_ID
 , (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
	and ir.effective_date =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= BAB.BALANCE_DATE
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		)
  ) INTEREST_RATES
 , 'N' FIRST_ROW
 , rownum
 from
   CE_BANK_ACCT_BALANCES BAB
 --, CE_BANK_ACCOUNTS	BA
 , CE_INTEREST_SCHEDULES  cIS
 , CE_INTEREST_BAL_RANGES	IBR
 WHERE
     cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
 and cIS.INTEREST_SCHEDULE_ID =   p_interest_schedule_id --10002
 --AND cIS.INTEREST_SCHEDULE_ID =  ba.INTEREST_SCHEDULE_ID
 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
 AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
 AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
 AND BAB.BALANCE_DATE <> p_from_date --'31-DEC-2003'
 and BAB.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE )
 and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
 order by  BALANCE_DATE_FROM
 ;

Line: 188

 select distinct
   tmp2.BANK_ACCOUNT_ID
 , IR.EFFECTIVE_DATE
 , tmp2.VALUE_DATED_BALANCE
/* , (select distinct tmp.VALUE_DATED_BALANCE
	from CE_INT_CALC_DETAILS_GT tmp
	where tmp.bank_account_id = bab.bank_account_id
	and tmp.balance_range_id = ir.balance_range_id
	and tmp.INTEREST_SCHEDULE_ID = cis.interest_schedule_id
	and tmp.interest_acct_type = p_interest_acct_type
	and tmp.from_date =
		(select max(tmp2.from_date)
		from CE_INT_CALC_DETAILS_GT tmp2
		where tmp2.from_date < ir.effective_date -- tmp.from_date
		and tmp.bank_account_id = tmp2.bank_account_id
		and tmp2.interest_acct_type = p_interest_acct_type
		)
	)  VALUE_DATED_BALANCE */
 , IR.BALANCE_RANGE_ID
 , IR.INTEREST_RATE
 from
   --CE_BANK_ACCT_BALANCES 	BAB
   CE_INT_CALC_DETAILS_GT 	tmp2  --, CE_BANK_ACCOUNTS		BA
 , CE_INTEREST_SCHEDULES  	CIS
 , CE_INTEREST_BAL_RANGES	IBR
 , CE_INTEREST_RATES		IR
 WHERE
    CIS.INTEREST_SCHEDULE_ID 	=  IBR.INTEREST_SCHEDULE_ID
 AND IBR.BALANCE_RANGE_ID 	= IR.BALANCE_RANGE_ID
 AND IR.EFFECTIVE_DATE 		>= p_from_date --'31-DEC-2003'
 AND IR.EFFECTIVE_DATE 		<=  p_to_date  -- '31-JAN-2004'
 and tmp2.VALUE_DATED_BALANCE 	>=  nvl(IBR.FROM_BALANCE_AMOUNT , tmp2.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 and tmp2.VALUE_DATED_BALANCE 	<= nvl(IBR.TO_BALANCE_AMOUNT, tmp2.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and BAB.VALUE_DATED_BALANCE 	>=  nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and BAB.VALUE_DATED_BALANCE 	<= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and p_int_calc_balance 	>=  nvl(IBR.FROM_BALANCE_AMOUNT , p_int_calc_balance )
 --and p_int_calc_balance 	<= nvl(IBR.TO_BALANCE_AMOUNT,p_int_calc_balance )
 --AND BA.BANK_ACCOUNT_ID 	= BAB.BANK_ACCOUNT_ID
 --AND cIS.INTEREST_SCHEDULE_ID =  BA.INTEREST_SCHEDULE_ID
 AND cIS.INTEREST_SCHEDULE_ID 	= p_interest_schedule_id --10002
 --AND BAB.BALANCE_DATE 	>= p_from_date --'31-DEC-2003'
 --AND BAB.BALANCE_DATE 	<= p_to_date --'31-JAN-2004'
 --AND BAB.BANK_ACCOUNT_ID 	= p_bank_account_id  --10001
 AND tmp2.from_DATE 		>=  p_from_date  --'31-DEC-2003'
 AND nvl(tmp2.to_DATE, p_to_date) <= p_to_date --'31-JAN-2004'
 AND tmp2.BANK_ACCOUNT_ID 	= p_bank_account_id  --10001
 AND IR.BALANCE_RANGE_ID 	= tmp2.BALANCE_RANGE_ID
 and tmp2.interest_acct_type 	= p_interest_acct_type
 and  cIS.INTEREST_SCHEDULE_ID 	=  tmp2.INTEREST_SCHEDULE_ID
 and  IR.EFFECTIVE_DATE 	> tmp2.from_date
 and  tmp2.from_date =	(select max(tmp.from_date)
		from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date < ir.effective_date
		and tmp.bank_account_id = tmp2.bank_account_id
		and tmp.interest_acct_type = p_interest_acct_type
		)
 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date = IR.EFFECTIVE_DATE
		and tmp.bank_account_id = tmp2.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		)
 and exists (select tmp.balance_range_id from CE_INT_CALC_DETAILS_GT tmp
		where tmp.balance_range_id = IR.BALANCE_RANGE_ID
		and tmp.bank_account_id = tmp2.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		)
 ;

Line: 265

 select ir.interest_rate,
 tmp.from_date,
 tmp.INTEREST_CALC_DETAIL_ID
 from
   CE_INTEREST_BAL_RANGES	IBR
 , ce_interest_rates   ir
 , CE_INT_CALC_DETAILS_GT tmp
 where
   ibr.balance_range_id = ir.balance_range_id
 and nvl(ibr.TO_BALANCE_AMOUNT, tmp.VALUE_DATED_BALANCE) <= tmp.VALUE_DATED_BALANCE
 and tmp.interest_rate is null
 and tmp.interest_schedule_id = p_interest_schedule_id --10741
 and tmp.bank_account_id = p_bank_account_id  --16000
 and tmp.interest_schedule_id = ibr.interest_schedule_id
 and ir.effective_date <= tmp.from_date
 and ir.effective_date = (select max(ir.effective_date)
			  from
			    CE_INTEREST_BAL_RANGES	IBR
			  , ce_interest_rates   ir
			  , CE_INT_CALC_DETAILS_GT tmp2
			  where
			    ibr.balance_range_id = ir.balance_range_id
			  and nvl(ibr.TO_BALANCE_AMOUNT, tmp2.VALUE_DATED_BALANCE) <= tmp.VALUE_DATED_BALANCE
			  and tmp2.interest_rate is null
			  and tmp2.interest_schedule_id = 10741
			  and tmp2.bank_account_id = 16000
			  and tmp2.interest_schedule_id = ibr.interest_schedule_id
			  and ir.effective_date <= tmp2.from_date
			  and tmp2.from_date = tmp.from_date
			 )
 ;

Line: 301

 select
  nvl(FROM_BALANCE_AMOUNT, l_amount),
  nvl(TO_BALANCE_AMOUNT, 0),
  ir.interest_rate
 from ce_interest_bal_ranges  ibr
 , ce_interest_rates  ir
 where ibr.INTEREST_SCHEDULE_ID =  p_interest_schedule_id
 and  ibr.TO_BALANCE_AMOUNT >=  l_amount
 and  ibr.TO_BALANCE_AMOUNT <= 0
 and ibr.balance_range_id = ir.balance_range_id
  and ir.effective_date=
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID)
;

Line: 318

/*  select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
   from XTR_INTEREST_RATE_RANGES
   where REF_CODE = l_acct
   and PARTY_CODE = l_bank_code
-- and PARTY_CODE = l_setoff_party
   and CURRENCY = L_CURRENCY
   and MAX_AMT >= l_amount
   and MIN_AMT <0
   and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
                              from XTR_INTEREST_RATE_RANGES
                              where REF_CODE = l_acct
                              and PARTY_CODE = l_bank_code
                            --and PARTY_CODE = l_setoff_party
                              and CURRENCY = L_CURRENCY
                              and MAX_AMT >= l_amount
                              and MIN_AMT <0
                              and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
   order by MAX_AMT desc;

Line: 343

 select
  nvl(FROM_BALANCE_AMOUNT, -1),
  nvl(TO_BALANCE_AMOUNT, l_amount),
 /*  (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
        and ir.effective_date =
                (select max(ir2.effective_date)
                 from CE_INTEREST_RATES  IR2
                 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
                 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
                )
  ) INTEREST_RATES*/
 ir.interest_rate
 from ce_interest_bal_ranges  ibr
,    ce_interest_rates  ir
 where ibr.INTEREST_SCHEDULE_ID = p_interest_schedule_id
 and  ibr.FROM_BALANCE_AMOUNT <=  l_amount
 and  ibr.FROM_BALANCE_AMOUNT >= 0
 and ibr.balance_range_id = ir.balance_range_id
 and ir.effective_date=
                (select max(ir2.effective_date)
                 from CE_INTEREST_RATES  IR2
                 where ir2.effective_date <= p_balance_date_from --'31-DEC-2003' --max(BAB.BALANCE_DATE)
                 and ir2.balance_range_id = ir.BALANCE_RANGE_ID)
;

Line: 370

/*  select MIN_AMT,MAX_AMT,nvl(INTEREST_RATE,0)
   from XTR_INTEREST_RATE_RANGES
   where REF_CODE = l_acct
   and PARTY_CODE = l_bank_code
-- and PARTY_CODE = l_setoff_party
   and CURRENCY = L_CURRENCY
   and MIN_AMT <= l_amount
   and MAX_AMT >= 0
   and EFFECTIVE_FROM_DATE =(select max(EFFECTIVE_FROM_DATE)
                              from XTR_INTEREST_RATE_RANGES
                              where REF_CODE = l_acct
                              and PARTY_CODE = l_bank_code
                           -- and PARTY_CODE = l_setoff_party
                              and CURRENCY = L_CURRENCY
                              and MIN_AMT <= l_amount
                              and MAX_AMT >= 0
                              and EFFECTIVE_FROM_DATE<= L_BALANCE_DATE)
   order by MIN_AMT desc;

Line: 401

select
  tmp.INTEREST_CALC_DETAIL_ID
--, tmp.VALUE_DATED_BALANCE
, IBR.BALANCE_RANGE_ID
--, IR.INTEREST_RATE
 , (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
	and ir.effective_date =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= tmp.from_date --p_from_date --max(BAB.BALANCE_DATE)
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		)
  ) INTEREST_RATES
from CE_INT_CALC_DETAILS_GT	tmp
 , CE_INTEREST_BAL_RANGES	IBR
-- , CE_INTEREST_RATES		IR
where tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id
 and  tmp.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
-- AND IBR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID
 and tmp.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT , tmp.VALUE_DATED_BALANCE )
 and tmp.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, tmp.VALUE_DATED_BALANCE )
-- and IR.EFFECTIVE_DATE 	>= p_from_date --'31-DEC-2003'
-- AND IR.EFFECTIVE_DATE 	<=  p_to_date  -- '31-JAN-2004'
 and tmp.BANK_ACCOUNT_ID 	= p_bank_account_id
 and tmp.INTEREST_ACCT_TYPE 	= p_interest_acct_type
 and tmp.cashpool_id 		= p_cashpool_id
 order by tmp.from_date;

Line: 438

 select
   BAB.BANK_ACCOUNT_ID
 , max(BAB.BALANCE_DATE)   BALANCE_DATE_FROM
 , ce_bal_util.get_pool_balance(p_cashpool_id, p_from_date) VALUE_DATED_BALANCE --BAB.VALUE_DATED_BALANCE
 , NULL BALANCE_RANGE_ID --IBR.BALANCE_RANGE_ID
 , NULL INTEREST_RATES
 /*, (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
	and ir.effective_date =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= p_from_date --'31-DEC-2003' --max(BAB.BALANCE_DATE)
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		)
  ) INTEREST_RATES*/
 , 'Y' FIRST_ROW
 , rownum
 from
   CE_BANK_ACCT_BALANCES BAB
 --, CE_BANK_ACCOUNTS	BA
 , CE_INTEREST_SCHEDULES  cIS
 --, CE_INTEREST_BAL_RANGES	IBR
 WHERE
      --cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
   cIS.INTEREST_SCHEDULE_ID =  p_interest_schedule_id --10002
  --AND  cIS.INTEREST_SCHEDULE_ID =  ba.INTEREST_SCHEDULE_ID
  --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
  AND BAB.BALANCE_DATE <= p_from_date --'31-DEC-2003'
  --and BAB.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
  --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
  AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
  and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date = p_from_date --max(BAB.BALANCE_DATE)
		--and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		and tmp.cashpool_id = p_cashpool_id
		)
  group by
  BAB.BANK_ACCOUNT_ID
  , BAB.BALANCE_DATE
  , BAB.VALUE_DATED_BALANCE
  --, IBR.BALANCE_RANGE_ID
  , rownum
  having max(BAB.BALANCE_DATE) =  (select max(bab2.balance_date)
					from CE_BANK_ACCT_BALANCES BAB2
					where BAb2.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
					and BAB2.BALANCE_DATE <= p_from_date) --'01-May-2004')
 UNION ALL
 select
   BAB.BANK_ACCOUNT_ID
 , BAB.BALANCE_DATE   --BALANCE_DATE_FROM
 , ce_bal_util.get_pool_balance(p_cashpool_id, BAB.BALANCE_DATE) --VALUE_DATED_BALANCE --BAB.VALUE_DATED_BALANCE
 , NULL BALANCE_RANGE_ID --IBR.BALANCE_RANGE_ID
 , NULL INTEREST_RATES
/* , (select distinct ir.interest_rate
   from  CE_INTEREST_RATES  IR
   where ir.balance_range_id = IBR.BALANCE_RANGE_ID
	and ir.effective_date =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= BAB.BALANCE_DATE
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		)
  ) INTEREST_RATES*/
 , 'N' FIRST_ROW
 , rownum
 from
   CE_BANK_ACCT_BALANCES BAB
 --, CE_BANK_ACCOUNTS	BA
 , CE_INTEREST_SCHEDULES  cIS
 --, CE_INTEREST_BAL_RANGES	IBR
 WHERE
     --cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
  cIS.INTEREST_SCHEDULE_ID =   p_interest_schedule_id --10002
 --AND cIS.INTEREST_SCHEDULE_ID =  ba.INTEREST_SCHEDULE_ID
 --and BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
 AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
 AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
 AND BAB.BALANCE_DATE <> p_from_date --'31-DEC-2003'
 --and BAB.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE )
 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE )
 AND BAB.BANK_ACCOUNT_ID = p_bank_account_id --10001
 and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date = BAB.BALANCE_DATE
		--and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		and tmp.cashpool_id = p_cashpool_id
		)
 order by  BALANCE_DATE_FROM
 ;

Line: 543

 select distinct
   tmpx.BANK_ACCOUNT_ID
 , IR.EFFECTIVE_DATE
 , tmpx.VALUE_DATED_BALANCE
 /* , (select distinct tmp.VALUE_DATED_BALANCE
	from CE_INT_CALC_DETAILS_GT tmp
	where tmp.bank_account_id = tmpx.bank_account_id
	and tmp.balance_range_id = ir.balance_range_id
	and tmp.INTEREST_SCHEDULE_ID = cis.interest_schedule_id
	and tmp.interest_acct_type = p_interest_acct_type
	and tmp.cashpool_id = p_cashpool_id
	and tmp.from_date =
		(select max(tmp2.from_date)
		from CE_INT_CALC_DETAILS_GT tmp2
		where tmp2.from_date < ir.effective_date -- tmp.from_date
		and tmp.bank_account_id = tmp2.bank_account_id
		and tmp2.interest_acct_type = p_interest_acct_type
		and tmp2.cashpool_id = p_cashpool_id
		)
	)  VALUE_DATED_BALANCE*/
 , IR.BALANCE_RANGE_ID
 , IR.INTEREST_RATE
 from
  -- CE_BANK_ACCT_BALANCES 	BAB
 --, CE_BANK_ACCOUNTS		BA
  CE_INTEREST_SCHEDULES  	cIS
 , CE_INTEREST_BAL_RANGES	IBR
 , CE_INTEREST_RATES		IR
 , CE_INT_CALC_DETAILS_GT tmpx
 WHERE
    cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
 AND IBR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID
 and  IR.EFFECTIVE_DATE >= p_from_date --'31-DEC-2003'
 AND IR.EFFECTIVE_DATE <=  p_to_date  -- '31-JAN-2004'
 and tmpx.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT , tmpx.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 and tmpx.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, tmpx.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and BAB.VALUE_DATED_BALANCE >=  nvl(IBR.FROM_BALANCE_AMOUNT , BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and BAB.VALUE_DATED_BALANCE <= nvl(IBR.TO_BALANCE_AMOUNT, BAB.VALUE_DATED_BALANCE ) --y_int_calc_balance1
 --and p_int_calc_balance >=  nvl(IBR.FROM_BALANCE_AMOUNT , p_int_calc_balance )
 --and p_int_calc_balance <= nvl(IBR.TO_BALANCE_AMOUNT,p_int_calc_balance )
 --AND BA.BANK_ACCOUNT_ID = BAB.BANK_ACCOUNT_ID
 --AND cIS.INTEREST_SCHEDULE_ID =  BA.INTEREST_SCHEDULE_ID
 AND cIS.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
 --AND BAB.BALANCE_DATE >= p_from_date --'31-DEC-2003'
 --AND BAB.BALANCE_DATE <= p_to_date --'31-JAN-2004'
 --AND BAB.BANK_ACCOUNT_ID = p_bank_account_id  --10001
 AND tmpx.BANK_ACCOUNT_ID = p_bank_account_id  --10001
 AND tmpx.from_DATE 		>=  p_from_date  --'31-DEC-2003'
 AND nvl(tmpx.to_DATE, p_to_date) <= p_to_date --'31-JAN-2004'
 AND IR.BALANCE_RANGE_ID 	= tmpx.BALANCE_RANGE_ID
 and tmpx.interest_acct_type 	= p_interest_acct_type
 and  CIS.INTEREST_SCHEDULE_ID 	=  tmpx.INTEREST_SCHEDULE_ID
 and  IR.EFFECTIVE_DATE 	> tmpx.from_date
 and  tmpx.from_date =	(select max(tmp.from_date)
		from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date < ir.effective_date
		and tmp.bank_account_id = tmpx.bank_account_id
		and tmp.interest_acct_type = p_interest_acct_type
		)
and not exists (select tmp.from_date from CE_INT_CALC_DETAILS_GT tmp
		where tmp.from_date = IR.EFFECTIVE_DATE
		and tmp.bank_account_id = tmpx.BANK_ACCOUNT_ID
		--and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		)
 and exists (select tmp.balance_range_id from CE_INT_CALC_DETAILS_GT tmp
		where tmp.balance_range_id = IR.BALANCE_RANGE_ID
		and tmp.bank_account_id = tmpx.BANK_ACCOUNT_ID
		--and tmp.bank_account_id = BAB.BANK_ACCOUNT_ID
		and tmp.from_date>=p_from_date --'31-DEC-2003'
		and tmp.from_date<=p_to_date --'31-JAN-2004'
		and tmp.INTEREST_SCHEDULE_ID = p_interest_schedule_id --10002
		and tmp.interest_acct_type = p_interest_acct_type
		)
 ;

Line: 674

select decode( mod(dDate, 4), 0,
          decode( mod(dDate, 400), 0, 'TRUE',
             decode( mod(dDate, 100), 0, 'FALSE', 'TRUE')
          ), 'FALSE'
       )
into IsLeapYear
from   dual;

Line: 716

       select  ir.interest_rate
       into   p_interest_rate
       from
         CE_BANK_ACCOUNTS	BA
       , CE_INTEREST_SCHEDULES  cIS
       , CE_INTEREST_BAL_RANGES	IBR
       , CE_INTEREST_RATES  IR
       WHERE
          BA.BANK_ACCOUNT_ID = p_bank_account_id --10001
        AND cIS.INTEREST_SCHEDULE_ID =  BA.INTEREST_SCHEDULE_ID
        AND cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
        and IBR.FROM_BALANCE_AMOUNT  <= p_balance_amount
	and IBR.FROM_BALANCE_AMOUNT > 0
        and nvl(IBR.TO_BALANCE_AMOUNT, p_balance_amount ) >= p_balance_amount
        and ir.balance_range_id = IBR.BALANCE_RANGE_ID
        and ir.effective_date  =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= p_balance_date --'31-DEC-2003'
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		) ;

Line: 738

       select  ir.interest_rate
       into   p_interest_rate
       from
         CE_BANK_ACCOUNTS	BA
       , CE_INTEREST_SCHEDULES  cIS
       , CE_INTEREST_BAL_RANGES	IBR
       , CE_INTEREST_RATES  IR
       WHERE
          BA.BANK_ACCOUNT_ID = p_bank_account_id --10001
        AND cIS.INTEREST_SCHEDULE_ID =  BA.INTEREST_SCHEDULE_ID
        AND cIS.INTEREST_SCHEDULE_ID =  IBR.INTEREST_SCHEDULE_ID
        and nvl(IBR.FROM_BALANCE_AMOUNT, p_balance_amount ) <= p_balance_amount
        and IBR.TO_BALANCE_AMOUNT >= p_balance_amount
        and IBR.TO_BALANCE_AMOUNT <= 0
        and ir.balance_range_id = IBR.BALANCE_RANGE_ID
        and ir.effective_date  =
		(select max(ir2.effective_date)
		 from CE_INTEREST_RATES  IR2
		 where ir2.effective_date <= p_balance_date --'31-DEC-2003'
		 and ir2.balance_range_id = ir.BALANCE_RANGE_ID
		) ;

Line: 785

|      delete_schedule_account	                                        |
|                                                                       |
|  CALLED BY                                                            |
|      int_cal_detail_main                                              |
|                                                                       |
|  DESCRIPTION                                                          |
|      Delete accounts from 						|
|        CE_INT_CALC_DETAILS_GT		                        |
 --------------------------------------------------------------------- */
PROCEDURE  delete_schedule_account( p_interest_schedule_id 	number,
				    p_bank_account_id 		number,
 				    p_interest_acct_type 	varchar2,
				    p_cashpool_id  	   number
				    )  IS
 x_balance_date_from 	DATE;

Line: 818

  	 cep_standard.debug('>> CE_INTEREST_CALC.delete_schedule_account');

Line: 825

  	 cep_standard.debug('delete p_interest_acct_type BANK_ACCOUNT');

Line: 827

    DELETE CE_INT_CALC_DETAILS_GT
    WHERE INTEREST_SCHEDULE_ID 	= p_interest_schedule_id
    and  BANK_ACCOUNT_ID	= p_bank_account_id
    AND INTEREST_ACCT_TYPE 	= p_interest_acct_type;

Line: 833

  	 cep_standard.debug('delete p_interest_acct_type NOTIONAL');

Line: 835

    DELETE CE_INT_CALC_DETAILS_GT
    WHERE INTEREST_SCHEDULE_ID 	= p_interest_schedule_id
    --and  BANK_ACCOUNT_ID	= p_bank_account_id
    AND CASHPOOL_ID 		= p_cashpool_id
    AND INTEREST_ACCT_TYPE 	= p_interest_acct_type;

Line: 844

  	 cep_standard.debug('<< CE_INTEREST_CALC.delete_schedule_account');

Line: 850

    	cep_standard.debug('EXCEPTION:  CE_INTEREST_CALC.delete_schedule_account');

Line: 853

    FND_MESSAGE.Set_Token('PROCEDURE', 'CE_INTEREST_CALC.delete_schedule_account');

Line: 855

END delete_schedule_account;

Line: 865

|      Get Interest Calculated balances information and insert in       |
|        CE_INT_CALC_DETAILS_GT		                        |
 --------------------------------------------------------------------- */
PROCEDURE  get_balance_info(  p_from_date 		date,
				p_to_date 		date,
				p_interest_schedule_id 	number,
				p_bank_account_id 	number,
 				p_interest_acct_type 	varchar2,
				p_cashpool_id  		number,
				p_row_count OUT NOCOPY  NUMBER)  IS
 x_balance_date_from 	DATE;

Line: 939

	 insert into CE_INT_CALC_DETAILS_GT
		 (INTEREST_CALC_DETAIL_ID,
		  INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID,  FROM_DATE, TO_DATE,
		  VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
		  BALANCE_RANGE_ID, RECORD_FROM,
		  INTEREST_ACCT_TYPE, CASHPOOL_ID,
		  LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,  CREATED_BY,
		  LAST_UPDATE_LOGIN)
	 values
		( CE_INT_CALC_DETAILS_GT_S.nextval,
		  p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
		  y_int_calc_balance, null, null, y_interest_rate,
		  y_balance_range_id, y_record_from,
		  p_interest_acct_type, p_cashpool_id,
		  sysdate, -1, sysdate, -1, null);

Line: 956

  	 cep_standard.debug('insert into CE_INT_CALC_DETAILS_GT completed  ');

Line: 988

|      Get Interest Calculated balances information and insert in       |
|        CE_INT_CALC_DETAILS_GT		                        |
 --------------------------------------------------------------------- */
PROCEDURE  get_balance_pool_info(  p_from_date 		date,
				p_to_date 		date,
				p_interest_schedule_id 	number,
				p_bank_account_id 	number,
 				p_interest_acct_type 	varchar2,
				p_cashpool_id  		number,
				p_row_count OUT NOCOPY  NUMBER)  IS
 x_balance_date_from 	DATE;

Line: 1063

	 insert into CE_INT_CALC_DETAILS_GT
		 (INTEREST_CALC_DETAIL_ID,
		  INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID,  FROM_DATE, TO_DATE,
		  VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
		  BALANCE_RANGE_ID, RECORD_FROM,
		  INTEREST_ACCT_TYPE, CASHPOOL_ID,
		  LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,  CREATED_BY,
		  LAST_UPDATE_LOGIN)
	 values
		( CE_INT_CALC_DETAILS_GT_S.nextval,
		  p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
		  y_int_calc_balance, null, null, y_interest_rate,
		  y_balance_range_id, y_record_from,
		  p_interest_acct_type, p_cashpool_id,
		  sysdate, -1, sysdate, -1, null);

Line: 1080

  	 cep_standard.debug('insert into CE_INT_CALC_DETAILS_GT completed  ');

Line: 1190

	 insert into CE_INT_CALC_DETAILS_GT
		 (INTEREST_CALC_DETAIL_ID,
		  INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID,  FROM_DATE, TO_DATE,
		  VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
		  BALANCE_RANGE_ID, RECORD_FROM,
		  INTEREST_ACCT_TYPE, CASHPOOL_ID,
		  LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,  CREATED_BY,
		  LAST_UPDATE_LOGIN)
	 values
		( CE_INT_CALC_DETAILS_GT_S.nextval,
		  p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
		  y_int_calc_balance, null, null, y_interest_rate,
		  y_balance_range_id, y_record_from,
		  p_interest_acct_type, p_cashpool_id,
		  sysdate, -1, sysdate, -1, null);

Line: 1316

	 insert into CE_INT_CALC_DETAILS_GT
		 (INTEREST_CALC_DETAIL_ID,
		  INTEREST_SCHEDULE_ID, BANK_ACCOUNT_ID,  FROM_DATE, TO_DATE,
		  VALUE_DATED_BALANCE, NUMBER_OF_DAYS , INTEREST_AMOUNT,INTEREST_RATE,
		  BALANCE_RANGE_ID, RECORD_FROM,
		  INTEREST_ACCT_TYPE, CASHPOOL_ID,
		  LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,  CREATED_BY,
		  LAST_UPDATE_LOGIN)
	 values
		( CE_INT_CALC_DETAILS_GT_S.nextval,
		  p_interest_schedule_id, p_bank_account_id, y_balance_date_from, null,
		  y_int_calc_balance, null, null, y_interest_rate,
		  y_balance_range_id, y_record_from,
		  p_interest_acct_type, p_cashpool_id,
		  sysdate, -1, sysdate, -1, null);

Line: 1415

	update CE_INT_CALC_DETAILS_GT
	  set INTEREST_RATE = y_interest_rate,
	  LAST_UPDATE_DATE = sysdate
        where   from_date =  y_balance_date_from
	and bank_account_id =  p_bank_account_id
  	and INTEREST_SCHEDULE_ID = p_interest_schedule_id
	and interest_calc_detail_id = y_interest_calc_detail_id;

Line: 1506

      update CE_INT_CALC_DETAILS_GT
      set BALANCE_RANGE_ID 	= y_balance_range_id,
	  INTEREST_RATE 	= y_interest_rate
      where
	  bank_account_id 	=  p_bank_account_id
  	and INTEREST_ACCT_TYPE 		= p_interest_acct_type
  	and cashpool_id 		= p_cashpool_id
	and interest_calc_detail_id 	= y_interest_calc_detail_id;

Line: 1608

      update CE_INT_CALC_DETAILS_GT
      set to_date =  y_end_date,
	  LAST_UPDATE_DATE = sysdate
      where   from_date =  x_balance_date_from
	--and bank_account_id =  p_bank_account_id
  	and INTEREST_SCHEDULE_ID = p_interest_schedule_id
	and interest_calc_detail_id = y_interest_calc_detail_id;

Line: 1700

      update CE_INT_CALC_DETAILS_GT
      set INTEREST_RATE = p_interest_rate
      where interest_calc_detail_id = y_interest_calc_detail_id;

Line: 1803

  select DAY_COUNT_BASIS,
    INTEREST_INCLUDES,
    INTEREST_ROUNDING,
    BASIS,
    CURRENCY_CODE
  INTO
     x_day_count_basis ,
     x_interest_includes ,
     x_interest_rounding,
     x_basis,
     x_currency_code
  from CE_INTEREST_SCHEDULES
  WHERE INTEREST_SCHEDULE_ID = p_interest_schedule_id;

Line: 1823

  select min(BALANCE_DATE) ,  max(BALANCE_DATE)
  INTO  x_min_balance_date , x_max_balance_date
  from  ce_bank_acct_balances
  where bank_account_id = p_bank_account_id;

Line: 1828

  SELECT MIN(FROM_BALANCE_AMOUNT)
  INTO x_add_min_pre_amt
  FROM ce_interest_bal_ranges
  WHERE   INTEREST_SCHEDULE_ID = p_interest_schedule_id
  AND FROM_BALANCE_AMOUNT > 0;

Line: 2161

        update CE_INT_CALC_DETAILS_GT
        set interest_amount  	= x_interest_amount_round,
	  NUMBER_OF_DAYS   	= x_days,
	  interest_rate 	= x_new_interest_rate,
	  LAST_UPDATE_DATE 	= sysdate
        where
	  from_date 		   = x_balance_date_from
	  --and bank_account_id 	   =  p_bank_account_id
  	  and INTEREST_SCHEDULE_ID = p_interest_schedule_id
	  and interest_calc_detail_id  = x_interest_calc_detail_id ;

Line: 2172

        update CE_INT_CALC_DETAILS_GT
        set interest_amount  	= x_interest_amount_round,
	  NUMBER_OF_DAYS   	= x_days,
	  LAST_UPDATE_DATE 	= sysdate
        where
	  from_date 		   =  x_balance_date_from
	  --and bank_account_id 	   =  p_bank_account_id
  	  and INTEREST_SCHEDULE_ID = p_interest_schedule_id
	  and interest_calc_detail_id  = x_interest_calc_detail_id ;

Line: 2262

	SELECT INTEREST_SCHEDULE_ID
	INTO P_INTEREST_SCHEDULE_ID
	FROM  CE_BANK_ACCOUNTS
	WHERE BANK_ACCOUNT_ID = p_bank_account_id;

Line: 2270

      	  delete_schedule_account(  p_interest_schedule_id ,
				p_bank_account_id,
				p_interest_acct_type,
				p_cashpool_id
			     );

Line: 2303

	   select sum(INTEREST_AMOUNT)
	   into p_interest_amount
	   from CE_INT_CALC_DETAILS_GT
	   where INTEREST_SCHEDULE_ID	= p_interest_schedule_id
	   and BANK_ACCOUNT_ID 	   	= p_bank_account_id
	   and INTEREST_ACCT_TYPE 	= p_interest_acct_type
	   and FROM_DATE  		>= p_from_date
	   and TO_DATE			<= p_to_date
	   and CASHPOOL_ID 	  is null;

Line: 2408

      delete_schedule_account(  p_interest_schedule_id ,
				p_bank_account_id,
				p_interest_acct_type,
				 p_cashpool_id
			     );

Line: 2434

      delete_schedule_account( p_interest_schedule_id ,
				 x_bank_account_id,
				 p_interest_acct_type,
				 p_cashpool_id
				     );

Line: 2497

      select count(*) into p_num_of_range
      from CE_INT_CALC_DETAILS_GT
      where
	  INTEREST_SCHEDULE_ID  = p_interest_schedule_id
	and BANK_ACCOUNT_ID 	= p_bank_account_id
	and INTEREST_ACCT_TYPE 	= p_interest_acct_type
	and CASHPOOL_ID is null;

Line: 2506

	delete CE_INT_CALC_DETAILS_GT
	where
	  INTEREST_SCHEDULE_ID  = p_interest_schedule_id
	and BANK_ACCOUNT_ID 	= p_bank_account_id
	and INTEREST_ACCT_TYPE 	= p_interest_acct_type
	and CASHPOOL_ID is null
	and FROM_DATE = 	p_to_date ;

Line: 2517

      select count(*) into p_num_of_range
      from CE_INT_CALC_DETAILS_GT
      where   INTEREST_SCHEDULE_ID  = p_interest_schedule_id
      --and BANK_ACCOUNT_ID 	= p_bank_account_id
      and INTEREST_ACCT_TYPE 	= p_interest_acct_type
      and CASHPOOL_ID 	= p_cashpool_id;

Line: 2525

	delete CE_INT_CALC_DETAILS_GT
        where   INTEREST_SCHEDULE_ID  = p_interest_schedule_id
        --and BANK_ACCOUNT_ID 	= p_bank_account_id
        and INTEREST_ACCT_TYPE 	= p_interest_acct_type
        and CASHPOOL_ID 	= p_cashpool_id
	and FROM_DATE = 	p_to_date ;