Untitled
unknown
plain_text
2 years ago
18 kB
11
Indexable
USE [BSI]
GO
/****** Object: StoredProcedure [mrms].[lcr_Equation_NasabahUMKMStabil_Daily] Script Date: 12/19/2023 10:45:04 AM ******/
DROP PROCEDURE [mrms].[lcr_Equation_NasabahUMKMStabil_Daily]
GO
/****** Object: StoredProcedure [mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily] Script Date: 12/19/2023 10:45:04 AM ******/
DROP PROCEDURE [mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily]
GO
/****** Object: StoredProcedure [mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily] Script Date: 12/19/2023 10:45:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Febrian Alfandi
-- Create date: 25 May 2023
-- Description: Persamaan nasabah UMKM
-- =============================================
CREATE PROCEDURE [mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily]
-- Add the parameters for the stored procedure here
@reportDate varchar (10),
@filterDinamic varchar (max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sqlResult varchar (max) =''
--#region filter update
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[POKOK_AWAL]','isnull(c.[POKOK_AWAL],0)')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[SUM_POKOK_AWAL]','isnull([src].[sum_pencairanPokok],0)')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[SUM_POKOK_AWAL]','a')
set @filterDinamic = replace (@filterDinamic,'[EDW.DS_CUSTOMER_MASTER]','a')
set @filterDinamic = replace (@filterDinamic,'[EDW.DEPOSITS]','b')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN]','c')
set @filterDinamic = replace (@filterDinamic,'[EDW.CIF_DPK_DIVISI]','y')
set @filterDinamic = replace (@filterDinamic,'inputLPS','b.CCY')
declare @parentFilter varchar (max) =''
declare @Filter varchar (max)=''
if ((select count(*) FROM STRING_SPLIT(@filterDinamic, ';')) =2)
begin
select top 1 @parentFilter = value FROM STRING_SPLIT(@filterDinamic, ';')
set @Filter = replace(replace(@filterDinamic,@parentFilter,''),';','')
end
else
begin
select @Filter = value FROM STRING_SPLIT(@filterDinamic, ';')
end
--#endregion
--#region main query
declare @SupportTable varchar (max)='
create table #cifdivisi (CIF_NO varchar(100),DIVISI_CD varchar(100))
insert into #cifdivisi
select distinct CIF_NO,DIVISI_CD from [mis].[EDW.CIF_DPK_DIVISI] b with (nolock)
create table #CIFKodeBU (CIF_NO varchar (100),kodeBUCount int)
insert into #CIFKodeBU
select CIF_NO,count(distinct DIVISI_CD) kodeBUCount from #cifdivisi group by CIF_NO
create table #PencairanPokok (CIF_NO varchar (100),pencairan decimal (32,2))
insert into #PencairanPokok
select CIF_NO,sum([POKOK_AWAL]) pencairan from [mis].[EDW.LOAN] with (nolock) group by CIF_NO
create table #CIPembayaranDivisi (CIF_NO varchar (100),sum_pencairanPokok decimal (32,2))
insert into #CIPembayaranDivisi
select distinct cust.CUSTOMER_CODE,isnull(b.pencairan,0)
from [mis].[EDW.DS_CUSTOMER_MASTER] cust with (nolock)
inner join [mis].[EDW.DEPOSITS] a with (nolock)
on cust.CUSTOMER_CODE = a.CIF_NO and cust.CUST_TYPE = ''c'' and a.SALDO_AKHIR >=0
inner join #cifdivisi y
on cust.CUSTOMER_CODE =y.CIF_NO
left join #PencairanPokok b on cust.CUSTOMER_CODE =b.CIF_NO
'
declare @rawCIF varchar (max) ='
create table #CIFUMKM_Pembiayaan (CIF_NO varchar(100),CountDivisi int,idPembiayaan bigint)
insert into #CIFUMKM_Pembiayaan
select src.CIF_NO,count(distinct y.DIVISI_CD),min(c.id) from #CIPembayaranDivisi src with (nolock)
inner join [mis].[EDW.DEPOSITS] a with (nolock)
on src.CIF_NO = a.CIF_NO and a.PRODUCT_CD not like ''2%'' and a.SALDO_AKHIR >=0
inner join [mis].[EDW.DS_CUSTOMER_MASTER] cust with (nolock)
on cust.CUSTOMER_CODE = a.CIF_NO and cust.CUST_TYPE = ''c''
and upper(cust.NAME_1) not like ''%VENTURA%''
and upper(cust.NAME_1) not like ''%KJKS%''
and upper(cust.NAME_1) not like ''%KSP%''
and upper(cust.NAME_1) not like ''%BMT%''
and upper(cust.NAME_1) not like ''%GADAI%''
and upper(cust.NAME_1) not like ''%DANA PENSIUN%''
and upper(cust.NAME_1) not like ''%DAPEN%''
and upper(cust.NAME_1) not like ''%ASURANSI%''
and upper(cust.NAME_1) not like ''%REKSADANA%''
and upper(cust.NAME_1) not like ''%FINANCE%''
and upper(cust.NAME_1) not like ''%SEKURITAS%''
and upper(cust.NAME_1) not like ''%BANK%''
and upper(cust.NAME_1) not like ''%BPR%''
inner join #cifdivisi y
on a.CIF_NO =y.CIF_NO
left join [mis].[EDW.LOAN] C with (nolock)on src.CIF_NO =C.CIF_NO
where (1=1) {parent}
group by src.CIF_NO
create table #CIFUMKM_Divisi (CIF_NO varchar(100))
insert into #CIFUMKM_Divisi
select a.CIF_NO from #CIFUMKM_Pembiayaan a
inner join #CIFKodeBU b
on a.cif_no = b.CIF_NO and a.CountDivisi =b.kodeBUCount
where idPembiayaan is not null
'
set @rawCIF = replace(@rawCIF,'{parent}',case when @parentFilter <> '' then 'and '+ @parentFilter else '' end )
declare @CIFNoAkun varchar (max) ='
delete from #CIFUMKM_Divisi
insert into #CIFUMKM_Divisi
select distinct a.CIF_NO from #CIFUMKM_Pembiayaan a
inner join #CIFKodeBU c
on a.cif_no = c.CIF_NO and a.CountDivisi =c.kodeBUCount
left join (
select a.CIF_NO from #CIFUMKM_Pembiayaan a
inner join #CIFKodeBU b
on a.cif_no = b.CIF_NO
inner join #cifdivisi c on c.CIF_NO =a.CIF_NO
where idPembiayaan is null and left (c.DIVISI_CD,2) in(''RD'',''FH'')
) b
on a.CIF_NO =b.CIF_NO
where idPembiayaan is null and b.CIF_NO is null '
declare @rawQueryNoAcount varchar (max) ='
SELECT B.id,b.CIF_NO,b.CCY,max(b.SALDO_AKHIR) total
from #CIPembayaranDivisi src with (nolock)
inner join #CIFUMKM_Divisi srcUMKM with (nolock) on src.CIF_NO = srcUMKM.CIF_NO
inner join [mis].[EDW.DEPOSITS] b with (nolock)
on src.cif_no = b.CIF_NO
inner join [mis].[EDW.DS_CUSTOMER_MASTER] a with (nolock)
on a.CUSTOMER_CODE = b.CIF_NO
inner join #cifdivisi y
on b.CIF_NO =y.CIF_NO
left join [mis].[EDW.LOAN] C with (nolock)
on b.CIF_NO =C.CIF_NO
where a.CUST_TYPE = ''c'' and b.PRODUCT_CD not like ''2%''
and c.CIF_NO is null and b.saldo_akhir >=0 and left(y.[DIVISI_CD],2) not in (''RD'',''FH'')
and upper(a.NAME_1) not like ''%VENTURA%''
and upper(a.NAME_1) not like ''%KJKS%''
and upper(a.NAME_1) not like ''%KSP%''
and upper(a.NAME_1) not like ''%BMT%''
and upper(a.NAME_1) not like ''%GADAI%''
and upper(a.NAME_1) not like ''%DANA PENSIUN%''
and upper(a.NAME_1) not like ''%DAPEN%''
and upper(a.NAME_1) not like ''%ASURANSI%''
and upper(a.NAME_1) not like ''%REKSADANA%''
and upper(a.NAME_1) not like ''%FINANCE%''
and upper(a.NAME_1) not like ''%SEKURITAS%''
and upper(a.NAME_1) not like ''%BANK%''
and upper(a.NAME_1) not like ''%BPR%'' {parent}
group by b.id,b.CCY,b.CIF_NO'
declare @rawQuery varchar (max) ='
SELECT B.id,b.CIF_NO,b.CCY,max(b.SALDO_AKHIR) total
from #CIPembayaranDivisi src with (nolock)
inner join #CIFUMKM_Divisi srcUMKM with (nolock) on src.CIF_NO = srcUMKM.CIF_NO
inner join [mis].[EDW.DEPOSITS] b with (nolock)
on src.cif_no = b.CIF_NO
inner join [mis].[EDW.DS_CUSTOMER_MASTER] a with (nolock)
on a.CUSTOMER_CODE = b.CIF_NO
inner join #cifdivisi y
on b.CIF_NO =y.CIF_NO
left join [mis].[EDW.LOAN] C with (nolock)
on b.CIF_NO =C.CIF_NO
where a.CUST_TYPE = ''c'' and b.PRODUCT_CD not like ''2%''
and c.CIF_NO is not null and b.SALDO_AKHIR>=0
and upper(a.NAME_1) not like ''%VENTURA%''
and upper(a.NAME_1) not like ''%KJKS%''
and upper(a.NAME_1) not like ''%KSP%''
and upper(a.NAME_1) not like ''%BMT%''
and upper(a.NAME_1) not like ''%GADAI%''
and upper(a.NAME_1) not like ''%DANA PENSIUN%''
and upper(a.NAME_1) not like ''%DAPEN%''
and upper(a.NAME_1) not like ''%ASURANSI%''
and upper(a.NAME_1) not like ''%REKSADANA%''
and upper(a.NAME_1) not like ''%FINANCE%''
and upper(a.NAME_1) not like ''%SEKURITAS%''
and upper(a.NAME_1) not like ''%BANK%''
and upper(a.NAME_1) not like ''%BPR%'' {parent}
{children}
group by B.id,b.CIF_NO,b.CCY'
set @rawQuery = replace(@rawQuery,'{parent}',case when @parentFilter <> '' then 'and '+ @parentFilter else '' end )
set @rawQueryNoAcount = replace(@rawQueryNoAcount,'{parent}',case when @parentFilter <> '' then 'and '+ @parentFilter else '' end )
declare @mainQuery varchar (max)=' select @IDR += case when CCY =''IDR'' then sum(total) else 0 end,@USD += case when CCY = ''USD'' then sum(total) else 0 end,
@Total += sum(total) from ('+@rawQuery+')b group by b.CCY'
declare @mainQueryNoLoanAccount varchar (max)=' select @IDR += case when CCY =''IDR'' then sum(total) else 0 end,@USD += case when CCY = ''USD'' then sum(total) else 0 end,
@Total += sum(total) from ('+@rawQueryNoAcount+')b group by b.CCY'
--#endregion
if @Filter like '%SUM_END_SALDO_AKHIR%'
begin
set @sqlResult += [dbo].[summarySaldoFilter] (@Filter ,@mainQuery ,@rawQuery,'SUM_END_SALDO_AKHIR','umkm' )
end
else
begin
set @mainQuery = replace(@mainQuery,'{children}',case when @Filter <> '' then 'and '+ @Filter else '' end )
set @sqlResult += @mainQuery
end
declare @variable varchar(100) ='declare @IDR decimal (18,2) =0,@USD decimal (18,2) =0,@Total decimal (18,2) =0'
set @sqlResult =@SupportTable + @rawCIF + @sqlResult
set @sqlResult = @variable + @sqlResult + @CIFNoAkun + @mainQueryNoLoanAccount
set @sqlResult = replace(@sqlResult,'EDW.DS_CUSTOMER_MASTER','EDW.DS_CUSTOMER_MASTER_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.DEPOSITS','EDW.DEPOSITS_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.LOAN','EDW.LOAN_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.CIF_DPK_DIVISI','EDW.CIF_DPK_DIVISI_'+@reportDate)
set @sqlResult += ' select isnull(@IDR,0) union all select isnull(@USD,0) union all select isnull(@TOTAL,0) '
print (@sqlResult)
exec (@sqlResult)
END
/*
declare @reportDate varchar (10) = '20230529'
declare @filterDinamic varchar (max) = ''
exec [mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily] @reportDate,@filterDinamic
exec sp_executesql N'[mrms].[lcr_Equation_NasabahUMKMTidakStabil_Daily] @p0,@p1
',N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'20230529',@p1=N'([EDW.LOAN].[SUM_POKOK_AWAL] <= 500000000 AND [EDW.CIF_DPK_DIVISI].[DIVISI_CD] in (''BBG'',''BB1G'',''BB2G'',''MBG'',''BBG B TO B'',''BBG B TO C'') );(( LOG_SUM_END_SALDO_AKHIR_START [EDW.DEPOSITS].[NISBAH_AKHIR] * 4 < case when inputLPS = ''IDR'' then 5 else 6 end AND LOG_SUM_END_SALDO_AKHIR_END SUM_SUM_END_SALDO_AKHIR_START > 200000000000SUM_SUM_END_SALDO_AKHIR_END ) OR [EDW.DEPOSITS].[NISBAH_AKHIR] * 4 >= case when inputLPS = ''IDR'' then 5 else 6 end )'
go
*/
GO
/****** Object: StoredProcedure [mrms].[lcr_Equation_NasabahUMKMStabil_Daily] Script Date: 12/19/2023 10:45:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Febrian Alfandi
-- Create date: 25 May 2023
-- Description: Persamaan nasabah UMKM
-- =============================================
CREATE PROCEDURE [mrms].[lcr_Equation_NasabahUMKMStabil_Daily]
-- Add the parameters for the stored procedure here
@reportDate varchar (10),
@filterDinamic varchar (max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sqlResult varchar (max) =''
--#region filter update
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[POKOK_AWAL]','isnull(c.[POKOK_AWAL],0)')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[SUM_POKOK_AWAL]','isnull([src].[sum_pencairanPokok],0)')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN].[SUM_POKOK_AWAL]','a')
set @filterDinamic = replace (@filterDinamic,'[EDW.DS_CUSTOMER_MASTER]','a')
set @filterDinamic = replace (@filterDinamic,'[EDW.DEPOSITS]','b')
set @filterDinamic = replace (@filterDinamic,'[EDW.LOAN]','c')
set @filterDinamic = replace (@filterDinamic,'[EDW.CIF_DPK_DIVISI]','y')
set @filterDinamic = replace (@filterDinamic,'inputLPS','b.CCY')
declare @parentFilter varchar (max) =''
declare @Filter varchar (max)=''
if ((select count(*) FROM STRING_SPLIT(@filterDinamic, ';')) =2)
begin
select top 1 @parentFilter = value FROM STRING_SPLIT(@filterDinamic, ';')
set @Filter = replace(replace(@filterDinamic,@parentFilter,''),';','')
end
else
begin
select @Filter = value FROM STRING_SPLIT(@filterDinamic, ';')
end
--#endregion
declare @SupportTable varchar (max)='
create table #cifdivisi (CIF_NO varchar(100),DIVISI_CD varchar(100))
insert into #cifdivisi
select distinct CIF_NO,DIVISI_CD from [mis].[EDW.CIF_DPK_DIVISI] b with (nolock)
create table #CIFKodeBU (CIF_NO varchar (100),kodeBUCount int)
insert into #CIFKodeBU
select CIF_NO,count(distinct DIVISI_CD) kodeBUCount from #cifdivisi group by CIF_NO
create table #PencairanPokok (CIF_NO varchar (100),pencairan decimal (32,2))
insert into #PencairanPokok
select CIF_NO,sum([POKOK_AWAL]) pencairan from [mis].[EDW.LOAN] with (nolock) group by CIF_NO
create table #CIPembayaranDivisi (CIF_NO varchar (100),sum_pencairanPokok decimal (32,2))
insert into #CIPembayaranDivisi
select distinct cust.CUSTOMER_CODE,isnull(b.pencairan,0)
from [mis].[EDW.DS_CUSTOMER_MASTER] cust with (nolock)
inner join [mis].[EDW.DEPOSITS] a with (nolock)
on cust.CUSTOMER_CODE = a.CIF_NO and cust.CUST_TYPE = ''c'' and a.SALDO_AKHIR >=0
inner join #cifdivisi y
on cust.CUSTOMER_CODE =y.CIF_NO
left join #PencairanPokok b on cust.CUSTOMER_CODE =b.CIF_NO
'
--#region main query
declare @rawCIF varchar (max) ='
create table #CIFUMKM_Pembiayaan (CIF_NO varchar(100),CountDivisi int,idPembiayaan bigint)
insert into #CIFUMKM_Pembiayaan
select src.CIF_NO,count(distinct y.DIVISI_CD),min(c.id) from #CIPembayaranDivisi src with (nolock)
inner join [mis].[EDW.DEPOSITS] a with (nolock)
on src.CIF_NO = a.CIF_NO and a.PRODUCT_CD not like ''2%'' and a.SALDO_AKHIR >=0
inner join [mis].[EDW.DS_CUSTOMER_MASTER] cust with (nolock)
on cust.CUSTOMER_CODE = a.CIF_NO and cust.CUST_TYPE = ''c''
and upper(cust.NAME_1) not like ''%VENTURA%''
and upper(cust.NAME_1) not like ''%KJKS%''
and upper(cust.NAME_1) not like ''%KSP%''
and upper(cust.NAME_1) not like ''%BMT%''
and upper(cust.NAME_1) not like ''%GADAI%''
and upper(cust.NAME_1) not like ''%DANA PENSIUN%''
and upper(cust.NAME_1) not like ''%DAPEN%''
and upper(cust.NAME_1) not like ''%ASURANSI%''
and upper(cust.NAME_1) not like ''%REKSADANA%''
and upper(cust.NAME_1) not like ''%FINANCE%''
and upper(cust.NAME_1) not like ''%SEKURITAS%''
and upper(cust.NAME_1) not like ''%BANK%''
and upper(cust.NAME_1) not like ''%BPR%''
inner join #cifdivisi y
on a.CIF_NO =y.CIF_NO
left join [mis].[EDW.LOAN] C with (nolock)on src.CIF_NO =C.CIF_NO
where (1=1) {parent}
group by src.CIF_NO
create table #CIFUMKM_Divisi (CIF_NO varchar(100))
insert into #CIFUMKM_Divisi
select a.CIF_NO from #CIFUMKM_Pembiayaan a
inner join #CIFKodeBU b
on a.cif_no = b.CIF_NO and a.CountDivisi =b.kodeBUCount
where idPembiayaan is not null
'
set @rawCIF = replace(@rawCIF,'{parent}',case when @parentFilter <> '' then 'and '+ @parentFilter else '' end )
declare @rawQuery varchar (max) ='
SELECT B.id,b.CIF_NO,b.CCY,max(b.SALDO_AKHIR) total
from #CIPembayaranDivisi src with (nolock)
inner join #CIFUMKM_Divisi srcUMKM with (nolock) on src.CIF_NO = srcUMKM.CIF_NO
inner join [mis].[EDW.DEPOSITS] b with (nolock)
on src.cif_no = b.CIF_NO
inner join [mis].[EDW.DS_CUSTOMER_MASTER] a with (nolock)
on a.CUSTOMER_CODE = b.CIF_NO
inner join #cifdivisi y
on b.CIF_NO =y.CIF_NO
left join [mis].[EDW.LOAN] C with (nolock)
on b.CIF_NO =C.CIF_NO
where a.CUST_TYPE = ''c'' and b.PRODUCT_CD not like ''2%''
and c.CIF_NO is not null and b.SALDO_AKHIR>=0
and upper(a.NAME_1) not like ''%VENTURA%''
and upper(a.NAME_1) not like ''%KJKS%''
and upper(a.NAME_1) not like ''%KSP%''
and upper(a.NAME_1) not like ''%BMT%''
and upper(a.NAME_1) not like ''%GADAI%''
and upper(a.NAME_1) not like ''%DANA PENSIUN%''
and upper(a.NAME_1) not like ''%DAPEN%''
and upper(a.NAME_1) not like ''%ASURANSI%''
and upper(a.NAME_1) not like ''%REKSADANA%''
and upper(a.NAME_1) not like ''%FINANCE%''
and upper(a.NAME_1) not like ''%SEKURITAS%''
and upper(a.NAME_1) not like ''%BANK%''
and upper(a.NAME_1) not like ''%BPR%'' {parent}
{children}
group by B.id,b.CIF_NO,b.CCY'
set @rawQuery = replace(@rawQuery,'{parent}',case when @parentFilter <> '' then 'and '+ @parentFilter else '' end )
declare @mainQuery varchar (max)=' select @IDR += case when CCY =''IDR'' then sum(total) else 0 end,@USD += case when CCY = ''USD'' then sum(total) else 0 end,
@Total += sum(total) from ('+@rawQuery+')b group by b.CCY'
--#endregion
if @Filter like '%SUM_END_SALDO_AKHIR%'
begin
set @sqlResult += [dbo].[summarySaldoFilter] (@Filter ,@mainQuery ,@rawQuery,'SUM_END_SALDO_AKHIR','umkm' )
end
else
begin
set @mainQuery = replace(@mainQuery,'{children}',case when @Filter <> '' then 'and '+ @Filter else '' end )
set @sqlResult += @mainQuery
end
declare @variable varchar(100) ='declare @IDR decimal (18,2) =0,@USD decimal (18,2) =0,@Total decimal (18,2) =0'
set @sqlResult =@SupportTable + @rawCIF + @sqlResult
set @sqlResult = @variable + @sqlResult
set @sqlResult = replace(@sqlResult,'EDW.DS_CUSTOMER_MASTER','EDW.DS_CUSTOMER_MASTER_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.DEPOSITS','EDW.DEPOSITS_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.LOAN','EDW.LOAN_'+@reportDate)
set @sqlResult = replace(@sqlResult,'EDW.CIF_DPK_DIVISI','EDW.CIF_DPK_DIVISI_'+@reportDate)
set @sqlResult += ' select isnull(@IDR,0) union all select isnull(@USD,0) union all select isnull(@TOTAL,0) '
print (@sqlResult)
exec (@sqlResult)
END
/*
declare @reportDate varchar (10) = '20230529'
declare @filterDinamic varchar (max) = ''
exec [mrms].[lcr_Equation_NasabahUMKMStabil_Daily_TESTING] @reportDate,@filterDinamic
*/
GO
Editor is loading...
Leave a Comment