~ Tiez Notes ~

CREATE procedure sp_col998ALL_Tis (
@v_branch varchar(4)
)
as
begin
Declare @vtgl smalldatetime
–select top 1 @vtgl = tglproses from rptFinancore with(nolock)
Declare @vtgl2 smalldatetime
Declare @v_dateakhirkusus2 smalldatetime
Declare @v_dateawalkusus smalldatetime
Declare @v_dateakhirkusus smalldatetime
Declare @v_dateawal smalldatetime
Declare @v_dateakhir smalldatetime
set @vtgl = dbo.f_getappldate()
set @v_dateakhir = @vtgl
set @v_dateawal = convert(varchar(2), month(@v_dateakhir)) + ‘/1/’ + convert(varchar(4), year(@v_dateakhir))
if day(@vtgl) >= 17
begin
set @vtgl2 = dateadd(month,+1,convert(datetime,@vtgl))
set @v_dateawalkusus = convert(varchar(2), month(@vtgl)) + ‘/17/’ + convert(varchar(4), year(@vtgl))
set @v_dateakhirkusus = convert(varchar(2), month(@vtgl2)) + ‘/16/’ + convert(varchar(4), year(@vtgl2))
set @v_dateawal = convert(varchar(2), month(@vtgl)) + ‘/17/’ + convert(varchar(4), year(@vtgl))
set @v_dateakhir = convert(varchar(2), month(@vtgl)) + ‘/’+ convert(varchar(2), day(@vtgl))+’/’ + convert(varchar(4), year(@vtgl))
end
else
begin
set @vtgl2 = dateadd(month,-1,convert(datetime,@vtgl))
set @v_dateawalkusus = convert(varchar(2), month(@vtgl2)) + ‘/17/’ + convert(varchar(4), year(@vtgl2 ))
set @v_dateakhirkusus = convert(varchar(2), month(@vtgl)) + ‘/16/’ + convert(varchar(4), year(@vtgl))
set @v_dateawal = convert(varchar(2), month(@vtgl2)) + ‘/17/’ + convert(varchar(4), year(@vtgl2))
set @v_dateakhir = convert(varchar(2), month(@vtgl)) + ‘/’+ convert(varchar(2), day(@vtgl))+’/’ + convert(varchar(4), year(@vtgl))
end
set nocount on
if @v_branch = ‘-1′
select aa.regionalid, aa.regional, aa.zoneid, aa.zonename, aa.branchid, aa.branchname, aa.norek, aa.nopin, aa.cicildate,
aa.angsuran, aa.indoor, aa.outdoor, (aa.indoor1+aa.outdoor1+aa.bank1+aa.unknown1-aa.reversal1) as inod,
aa.reversal, aa.bank, aa.unknown,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) <= 0 then indoor else 0 end) as Vtlancar,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) between 1 and 90 then indoor else 0 end) as Vt90_in,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) > 90 then indoor else 0 end) as Vt90UP_in,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) <= 90 then outdoor else 0 end) as Vt90_od,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) > 90 then outdoor else 0 end) as Vt90UP_od,
tagih,denda, @v_dateakhir as vtgl,@v_dateawal as vtgl0
from
(select a.regionalid, a.regional, a.zoneid, a.zonename, a.branchid, a.branchname, a.norek, a.nopin, a.txndate, a.valuedate,
(case when (a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘I’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘PD’ and a.FuncType <> ‘ET’) then a.Amount else 0 end) as indoor,
(case when ((a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘O’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘TU’ and a.FuncType <> ‘PD’
and a.txntype <> ’2′ and a.txntype <> ’3′and a.txntype <> ’4′ and a.txntype <> ’7′)) then a.Amount else 0 end) as outdoor,
(case when (a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘I’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘PD’ and a.FuncType <> ‘ET’) and a.txndate = @v_dateakhir then a.Amount else 0 end) as indoor1,
(case when ((a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘O’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘TU’ and a.FuncType <> ‘PD’
and a.txntype <> ’2′ and a.txntype <> ’3′and a.txntype <> ’4′ and a.txntype <> ’7′)) and a.txndate = @v_dateakhir then a.Amount else 0 end) as outdoor1,
isnull((case when a.txntype = ’7′ and a.FuncType = ‘PA’ then a.Amount else 0 end ),0) as unknown,
isnull((case when a.txntype = ’7′ and a.FuncType = ‘PA’ and a.txndate = @v_dateakhir then a.Amount else 0 end),0) as unknown1,
(case when a.FuncCode in (‘PA2′,’PA3′,’PA4′,’PA10′) then a.Amount else 0 end ) as bank,
(case when (left(a.FuncCode,2) = ‘PA’ and a.FuncType = ‘RE’) then a.Amount else 0 end ) as reversal,
(case when a.FuncCode in (‘PA2′,’PA3′,’PA4′,’PA10′) and a.txndate = @v_dateakhir then a.Amount else 0 end ) as bank1,
(case when (left(a.FuncCode,2) = ‘PA’ and a.FuncType = ‘RE’) and a.txndate = @v_dateakhir then a.Amount else 0 end) as reversal1,
(case when (a.TxnType = ’1′ and a.InOut = ‘I’ and a.FuncType <> ‘RE’) then CollectFee else 0 end) as tagih,
(case when (a.TxnType = ’1′ and a.InOut = ‘I’ and a.FuncType =’PD’) then a.Amount else 0 end) as denda,
ISNULL((select top 1 coraccountdetail.cicildate from coraccountdetail where coraccountdetail.norek = a.norek and coraccountdetail.nopin = a.nopin and
coraccountdetail.paiddate = a.valuedate), a.txndate ) as cicildate,0 as angsuran
from v_col998 a with(nolock)
where a.txndate between @v_dateawal and @v_dateakhir and a.NoRek is not null
–and a.branchid = ’3001′
–order by a.norek, a.nopin
) aa
union all
select a.regionalid, a.regional, a.zoneid, a.zonename, a.branchid, a.branchname, a.norek, a.nopin, a.cicildate,
a.angsuran, a.indoor, a.outdoor, a.inod, a.reversal, a.bank, a.unknown, a.vtlancar, a.vt90_in, a.vt90up_in,
a.vt90_od, a.vt90up_od, a.tagih, a.denda, @v_dateakhir as vtgl, @v_dateawal as vtgl0
from v_col998_khusus a with(nolock)
–where a.branchid = ’3001′
else
select aa.regionalid, aa.regional, aa.zoneid, aa.zonename, aa.branchid, aa.branchname, aa.norek, aa.nopin, aa.cicildate,
aa.angsuran, aa.indoor, aa.outdoor, (aa.indoor1+aa.outdoor1+aa.bank1+aa.unknown1-aa.reversal1) as inod,
aa.reversal, aa.bank, aa.unknown,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) <= 0 then indoor else 0 end) as Vtlancar,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) between 1 and 90 then indoor else 0 end) as Vt90_in,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) > 90 then indoor else 0 end) as Vt90UP_in,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) <= 90 then outdoor else 0 end) as Vt90_od,
(case when (DATEDIFF(day, aa.cicildate, aa.valuedate)) > 90 then outdoor else 0 end) as Vt90UP_od,
tagih,denda, @v_dateakhir as vtgl,@v_dateawal as vtgl0
from
(select a.regionalid, a.regional, a.zoneid, a.zonename, a.branchid, a.branchname, a.norek, a.nopin, a.txndate, a.valuedate,
(case when (a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘I’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘PD’ and a.FuncType <> ‘ET’) then a.Amount else 0 end) as indoor,
(case when ((a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘O’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘TU’ and a.FuncType <> ‘PD’
and a.txntype <> ’2′ and a.txntype <> ’3′and a.txntype <> ’4′ and a.txntype <> ’7′)) then a.Amount else 0 end) as outdoor,
(case when (a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘I’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘PD’ and a.FuncType <> ‘ET’) and a.txndate = @v_dateakhir then a.Amount else 0 end) as indoor1,
(case when ((a.InOut = ‘I’ and left(ISNULL(a.KuitansiNo, ‘O’),1) = ‘O’ and a.FuncType <> ‘RE’ and a.FuncType <> ‘TU’ and a.FuncType <> ‘PD’
and a.txntype <> ’2′ and a.txntype <> ’3′and a.txntype <> ’4′ and a.txntype <> ’7′)) and a.txndate = @v_dateakhir then a.Amount else 0 end) as outdoor1,
isnull((case when a.txntype = ’7′ and a.FuncType = ‘PA’ then a.Amount else 0 end ),0) as unknown,
isnull((case when a.txntype = ’7′ and a.FuncType = ‘PA’ and a.txndate = @v_dateakhir then a.Amount else 0 end),0) as unknown1,
(case when a.FuncCode in (‘PA2′,’PA3′,’PA4′,’PA10′) then a.Amount else 0 end ) as bank,
(case when (left(a.FuncCode,2) = ‘PA’ and a.FuncType = ‘RE’) then a.Amount else 0 end ) as reversal,
(case when a.FuncCode in (‘PA2′,’PA3′,’PA4′,’PA10′) and a.txndate = @v_dateakhir then a.Amount else 0 end ) as bank1,
(case when (left(a.FuncCode,2) = ‘PA’ and a.FuncType = ‘RE’) and a.txndate = @v_dateakhir then a.Amount else 0 end) as reversal1,
(case when (a.TxnType = ’1′ and a.InOut = ‘I’ and a.FuncType <> ‘RE’) then CollectFee else 0 end) as tagih,
(case when (a.TxnType = ’1′ and a.InOut = ‘I’ and a.FuncType =’PD’) then a.Amount else 0 end) as denda,
ISNULL((select top 1 coraccountdetail.cicildate from coraccountdetail where coraccountdetail.norek = a.norek and coraccountdetail.nopin = a.nopin and
coraccountdetail.paiddate = a.valuedate), a.txndate ) as cicildate,0 as angsuran
from v_col998 a with(nolock)
where a.txndate between @v_dateawal and @v_dateakhir and a.NoRek is not null
and a.branchid = @v_branch
–order by a.norek, a.nopin
) aa
union all
select a.regionalid, a.regional, a.zoneid, a.zonename, a.branchid, a.branchname, a.norek, a.nopin, a.cicildate,
a.angsuran, a.indoor, a.outdoor, a.inod, a.reversal, a.bank, a.unknown, a.vtlancar, a.vt90_in, a.vt90up_in,
a.vt90_od, a.vt90up_od, a.tagih, a.denda, @v_dateakhir as vtgl, @v_dateawal as vtgl0
from v_col998_khusus a with(nolock)
where a.branchid = @v_branch
set nocount off
end
GO