select db_stat_date - max(value_date) into vdpd from htd a,gct
    where acid=vacid
    and ((a.tran_date,a.tran_id,a.part_tran_srl_num) not in (select tran_date,tran_id,part_tran_srl_num from atd where tran_date=a.tran_date AND tran_id=a.tran_id AND part_tran_srl_num=a.part_tran_srl_num ))
    and tran_sub_type in ('CI','BS','BI','NR','O')
    and (tran_particular not like 'FTR%' or tran_particular not like '%: Closure Proceeds' or tran_particular not like '%Disbursement Credit' or tran_particular not like 'REJECT:%')
    and part_tran_type = 'C'
    and a.pstd_flg='Y'
    and a.del_flg='N'
    and ((a.tran_date,a.tran_id) not in (select tran_date,tran_id from htd b,gam
       where gam.acid=b.acid
       and a.tran_date =b.tran_date
       and a.tran_id = b.tran_id
       and schm_code in ('OD218','OD220','OD221','OD222','OD223','OD224','OD225','OD226')
       and b.part_tran_type= 'D'
       and b.pstd_flg='Y' and b.del_flg='N'))
    and ((a.tran_date,a.tran_id,a.part_tran_srl_num) not in (select tran_date,tran_id,part_tran_srl_num from ocp 
       where acid=a.acid and tran_date=a.tran_date AND tran_id=a.tran_id AND part_tran_srl_num=a.part_tran_srl_num and STATUS_FLG <>'G'))
       group by db_stat_date;
    Exception
        when no_data_found then
        null;
        End;

--declare @vacid varchar(11)
--set @vacid = '61217749'

declare @vdate datetime
declare @v_date datetime
declare @vint int


set @vdate = 
(select max(a.VALUE_DATE)

from 
dbo.SRC_HTD_TABLE a
left outer join
(
select 
	tran_date
	,tran_id 
from dbo.SRC_HTD_TABLE b
,dbo.SRC_GAM gam
where gam.acid=b.acid
       and schm_code in ('OD218','OD220','OD221','OD222','OD223','OD224','OD225','OD226')
       and b.part_tran_type= 'D'
       and b.pstd_flg='Y' and b.del_flg='N'
)b on  a.tran_date =b.tran_date
        and a.tran_id = b.tran_id

left outer join
(
select
tran_id 
,tran_date
,part_tran_srl_num
from
src_atd 
) c on	a.tran_id = c.tran_id 
	and a.tran_date = c.tran_date
	and a.part_tran_srl_num = c.part_tran_srl_num

left outer join
(
select
tran_id 
,tran_date
,part_tran_srl_num
,STATUS_FLG
from
src_ocp
) d on	a.tran_id = d.tran_id 
	and a.tran_date = d.tran_date
	and a.part_tran_srl_num = d.part_tran_srl_num

where
    b.tran_id is null
and c.tran_id is null
and d.tran_id is null
and d.STATUS_FLG != 'G'
and a.tran_sub_type in ('CI','BS','BI','NR','O')
and (tran_particular not like 'FTR%' or tran_particular not like '%: Closure Proceeds' or tran_particular not like '%Disbursement Credit' or tran_particular not like 'REJECT:%')
and a.part_tran_type = 'C'
and a.pstd_flg='Y'
and a.del_flg='N'
and a.acid = @vacid
)

set @v_date = (select DB_STAT_DATE from dbo.SRC_GCT)

set @vint = (select datediff(day,@vdate,@v_date))