~ Tiez Notes ~

select aa.thn_jual, aa.productcode, aa.productdesc, sum(aa.cusosarreal) as osarreal, sum(aa.jatuhtempo) as jatuhtempo, sum(aa.bln6) as bln6,
sum(aa.bln12) as bln12, sum(aa.bln18) as bln18, sum(aa.bln24) as bln24, sum(aa.bln30) as bln30, sum(aa.bln36) as bln36,
sum(aa.bln42) as bln42, sum(aa.bln48) as bln48
from
(select a.norek, a.nopin, a.realisasidate, datepart(year, a.realisasidate) thn_jual, a.agingday, a.productcode,
(select productdesc from genproduct where productcode = a.productcode) as productdesc, a.cusosarreal,
(case when a.agingday > 0 then a.cusosarreal else 0 end) jatuhtempo,
(case when a.agingday <= 0 then a.cusosarreal else 0 end) nonjatuhtempo,
dateadd(month, -1, dateadd(month, a.tenor, a.realisasidate)) enddate,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 0 and 6 then a.cusosarreal else 0 end) bln6,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 7 and 12 then a.cusosarreal else 0 end) bln12,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 13 and 18 then a.cusosarreal else 0 end) bln18,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 19 and 24 then a.cusosarreal else 0 end) bln24,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 25 and 30 then a.cusosarreal else 0 end) bln30,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 31 and 36 then a.cusosarreal else 0 end) bln36,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 37 and 42 then a.cusosarreal else 0 end) bln42,
(case when agingday <=0 and (a.tenor – a.tenorpaid) between 43 and 48 then a.cusosarreal else 0 end) bln48,
(case when agingday <=0 and (a.tenor – a.tenorpaid) > 48 then a.cusosarreal end) lbh48
from rptfinancore a with(nolock)
where a.closetype = ’0′
) aa
group by aa.thn_jual, aa.productcode, aa.productdesc
order by aa.thn_jual, aa.productcode

CREATE procedure sp_rptFPDSurveyAll (
@v_branchid varchar(4),
@v_kodefpd varchar(1),
@v_tgl smalldatetime
)
AS
Begin
set transaction isolation level read uncommitted
begin transaction
declare @v_dateawal smalldatetime
declare @v_dateakhir smalldatetime
declare @v_bln varchar(2)
declare @v_tglakhir varchar(4)
declare @v_blnakhir varchar(2)
declare @v_thnakhir varchar(2)
declare @v_tgl_akhir smalldatetime
if @v_kodefpd = ’1′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -1, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else if @v_kodefpd = ’2′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -2, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’3′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -3, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’4′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -4, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl) end
else if @v_kodefpd = ’5′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -5, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else if @v_kodefpd = ’6′
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -6, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
else
begin
set @v_bln = datepart(mm, @v_tgl)
set @v_dateawal = convert(smalldatetime, (dateadd(month, -6, (convert(varchar, @v_bln) + ‘/1/’ + convert(varchar, year(@v_tgl))))))
set @v_dateakhir = convert(smalldatetime, @v_tgl)
end
if @v_branchid = ‘-1′
select aa.branchid, aa.branchname, aa.surveyorname, sum(mapReal) as mapReal, sum(mapTgk) as mapTgk,
sum(TB) as TB
from
(select ownergroupid as branchid, dbo.f_getbranch(ownergroupid) as branchname,
suveyorname as surveyorname, count(norek+nopin) mapReal, 0 as mapTgk, 0 as TB
from vp_report5
where realizationdate between @v_dateawal and @v_dateakhir
group by ownergroupid, suveyorname
union all
select branchid, dbo.f_getbranch(branchid) as branchname, surveyorname, 0 as mapReal, count(norek+nopin) mapTgk, 0 as TB
from rptfinancore
where agingday > 0 and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, surveyorname
union all
select branchid, branchname, surveyorname, 0 as mapReal, 0 as mapTgk, count(norek+nopin) TB
from rptfinancore with(nolock)
where reposesdate is not null and moveardate is not null
and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, branchname, surveyorname
) aa
group by aa.branchid, aa.branchname, aa.surveyorname
else
select aa.branchid, aa.branchname, aa.surveyorname, sum(mapReal) as mapReal, sum(mapTgk) as mapTgk,
sum(TB) as TB
from
(select ownergroupid as branchid, dbo.f_getbranch(ownergroupid) as branchname,
suveyorname as surveyorname, count(norek+nopin) mapReal, 0 as mapTgk, 0 as TB
from vp_report5
where ownergroupid = @v_branchid and realizationdate between @v_dateawal and @v_dateakhir
group by ownergroupid, suveyorname
union all
select branchid, dbo.f_getbranch(branchid) as branchname, surveyorname, 0 as mapReal, count(norek+nopin) mapTgk, 0 as TB
from rptfinancore
where agingday > 0 and branchid = @v_branchid and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, surveyorname
union all
select branchid, branchname, surveyorname, 0 as mapReal, 0 as mapTgk, count(norek+nopin) TB
from rptfinancore with(nolock)
where reposesdate is not null and moveardate is not null
and branchid = @v_branchid and realisasidate between @v_dateawal and @v_dateakhir
group by branchid, branchname, surveyorname
) aa
group by aa.branchid, aa.branchname, aa.surveyorname
commit transaction
end
GO

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

CREATE VIEW dbo.v_col998_khusus
AS
SELECT b.regionalid, b.regional, b.zoneid, b.zonename, a.branchid, dbo.f_getBranch(a.branchid) AS branchname, a.norek, a.nopin, a.cicildate, a.angsuran,
0 AS indoor, 0 AS outdoor, 0 AS inod, 0 AS reversal, 0 AS bank, 0 AS unknown, 0 AS vtlancar, 0 AS Vt90_in, 0 AS Vt90up_in, 0 AS Vt90_od,
0 AS Vt90UP_od, 0 AS tagih, 0 AS denda
FROM dbo.tmpPJT_Khusus a INNER JOIN
dbo.genRegional b ON b.branchid = a.branchid

CREATE VIEW dbo.v_col998
AS
SELECT dbo.genRegional.regionalid, dbo.genRegional.regional, dbo.genRegional.zoneid, dbo.genRegional.zonename,
dbo.prfUser.BranchId, dbo.cshTxn.TxnDate,
(SELECT minCicildate
FROM v_rptFinancore_totNotPaid
WHERE norek = corPayment.norek AND nopin = corPayment.nopin) AS CicilDate, dbo.cshTxn.ValueDate, dbo.cshTxn.NoRek, dbo.cshTxn.NoPin,
dbo.corPayment.CshBranchId, dbo.genBranch.BranchName, dbo.cshFuncCode.InOut, dbo.corPayment.KuitansiNo,
dbo.cshTxn.FuncType, dbo.corPayment.Amount, dbo.cshTxn.TxnType, dbo.cshFuncCode.FuncCode, dbo.corPayment.CollectFee,
(SELECT sum(isnull(angsuran, 0))
FROM tmppjt_khusus
WHERE branchid = dbo.prfUser.BranchId AND norek = dbo.corPayment.norek AND nopin = dbo.corPayment.nopin) AS angsuran
FROM dbo.corPayment WITH (nolock) INNER JOIN
dbo.corAccount WITH (NOLOCK) ON dbo.corPayment.NoRek = dbo.corAccount.NoRek AND
dbo.corPayment.NoPin = dbo.corAccount.NoPin LEFT OUTER JOIN
dbo.prfUser WITH (NOLOCK) LEFT OUTER JOIN
dbo.cshTxn WITH (NOLOCK) ON dbo.prfUser.UserId = dbo.cshTxn.AuthorId LEFT OUTER JOIN
dbo.cshFuncCode WITH (NOLOCK) ON dbo.cshTxn.FuncCode = dbo.cshFuncCode.FuncCode ON
dbo.corPayment.TxnId = dbo.cshTxn.TxnId LEFT OUTER JOIN
dbo.genBranch WITH (NOLOCK) ON dbo.prfUser.BranchId = dbo.genBranch.BranchId INNER JOIN
dbo.genRegional ON dbo.genRegional.branchid = dbo.prfUser.BranchId
WHERE (dbo.cshTxn.NoRek IS NOT NULL)