SP Loan
unknown
plain_text
a year ago
6.7 kB
5
Indexable
USE [bsi] GO /****** Object: StoredProcedure [mrms].[IRRBB_Loan_Prepayment_Rate] Script Date: 26/02/2024 14:33:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*di Uncomment jika ingin create CP */ --===================================== ALTER PROCEDURE [mrms].[IRRBB_Loan_Prepayment_Rate] @reportDate VARCHAR(10), @randomString VARCHAR(max) AS --===================================== /*dihapus jika ingin create SP, hanya untuk testing hasil keluarannya*/ --=============================================================================== --DECLARE @reportDate VARCHAR(10) = '20230630'; --DECLARE @randomString VARCHAR(max) = 'random'; --============================================================================== BEGIN declare @sql varchar (max) =''; declare @lastMonthDate varchar(max) = FORMAT([mrms].[EOMLastDayWork](DATEADD(DAY, -1, CAST(substring(@reportDate, 1, 6) + '01' AS DATE))), 'yyyyMMdd'); declare @yearReportDate varchar(4) = substring(@reportDate, 1, 4); declare @monthReportDate varchar(2) = substring(@reportDate, 5, 2); declare @resultTable varchar(max) = '#TempResultLoan_'+@randomString; declare @masterTable varchar(max) = '#TempQuery_'+@randomString; declare @masterLastMonthTable varchar(max) = '#TempQuerySecond_'+@randomString; set @sql = ' DROP TABLE IF EXISTS '+@resultTable+'; CREATE TABLE '+@resultTable+' ( totalEarlyPrepayment NUMERIC(32,2), totalOutstanding NUMERIC(32,2), sMM FLOAT, componentCode VARCHAR(20) );'; set @sql = @sql + ' INSERT INTO '+@resultTable+' (totalEarlyPrepayment, totalOutstanding, sMM, componentCode) VALUES (cast(0 as float), cast(0 as float), cast(0 as float), ''PMGNR_IDR''), (cast(0 as float), cast(0 as float), cast(0 as float), ''PMMNR_IDR''), (cast(0 as float), cast(0 as float), cast(0 as float), ''PMPNR_IDR''), (cast(0 as float), cast(0 as float), cast(0 as float), ''PMMICNR_IDR''), (cast(0 as float), cast(0 as float), cast(0 as float), ''PMLNR_V''), (cast(0 as float), cast(0 as float), cast(0 as float), ''PMLNR_IDR'') ; --NON REVIEWABLE-- DROP TABLE IF EXISTS '+@masterTable+'; SELECT pembiayaanLastBSI.sisaPokokPSAK, pembiayaanLastBSI.imbalanAkanDiterima, pembiayaanLastBSI.kodeProduk, pembiayaanLastBSI.noRekening, pembiayaanLastBSI.jenisValuta, case when LOWER(pembiayaanClose.jenisValuta) = ''idr'' and LOWER(pembiayaanClose.segmenBSI) = ''consumer'' and LOWER(pembiayaanClose.namaProdukBSI) IN (''griya'', ''griya flpp'', ''griya berkah'') then ''PMGNR_IDR'' when LOWER(pembiayaanClose.jenisValuta) = ''idr'' and LOWER(pembiayaanClose.segmenBSI) = ''consumer'' and LOWER(pembiayaanClose.namaProdukBSI) IN (''mitraguna'', ''mitraguna & other'', ''multiguna secure'') then ''PMMNR_IDR'' when LOWER(pembiayaanClose.jenisValuta) = ''idr'' and LOWER(pembiayaanClose.segmenBSI) = ''consumer'' and LOWER(pembiayaanClose.namaProdukBSI) IN (''pensiun'', ''pensiun berkah'', ''pensiunan asabri'', ''pensiunan asn'', ''pensiunan berkah'', ''pensiunan bumn'') then ''PMPNR_IDR'' when LOWER(pembiayaanClose.jenisValuta) = ''idr'' and LOWER(pembiayaanClose.segmenBSI) = ''mikro'' then ''PMMICNR_IDR'' when LOWER(pembiayaanClose.jenisValuta) <> ''idr'' then ''PMLNR_V'' else ''PMLNR_IDR'' END AS code INTO '+@masterTable+' FROM [ddm].[masterPembiayaan_close_'+@reportDate+'] pembiayaanClose LEFT OUTER JOIN [ddm].[masterPembiayaan_BSI_'+@lastMonthDate+'] pembiayaanLastBSI on pembiayaanLastBSI.noRekening = pembiayaanClose.noRekening LEFT OUTER JOIN ##LoanType'+@randomString+' loan on loan.ProductKey = pembiayaanLastBSI.kodeProduk WHERE LOWER(loan.MappingIRRBB) = ''sesuai jangka waktu'' AND pembiayaanClose.pelunasDipercepat = ''Y'' AND format(pembiayaanClose.CloseDate, ''yyyyMM'') = SUBSTRING('''+@reportDate+''', 1, 6) ; DROP TABLE IF EXISTS '+@masterLastMonthTable+'; SELECT pembiayaanBSI.sisaPokokPSAK, pembiayaanBSI.imbalanAkanDiterima, pembiayaanBSI.kodeProduk, pembiayaanBSI.noRekening, pembiayaanBSI.jenisValuta, case when LOWER(pembiayaanBSI.jenisValuta) = ''idr'' and LOWER(pembiayaanBSI.segmenBSI) = ''consumer'' and LOWER(pembiayaanBSI.namaProdukBSI) IN (''griya'', ''griya flpp'', ''griya berkah'') then ''PMGNR_IDR'' when LOWER(pembiayaanBSI.jenisValuta) = ''idr'' and LOWER(pembiayaanBSI.segmenBSI) = ''consumer'' and LOWER(pembiayaanBSI.namaProdukBSI) IN (''mitraguna'', ''mitraguna & other'', ''multiguna secure'') then ''PMMNR_IDR'' when LOWER(pembiayaanBSI.jenisValuta) = ''idr'' and LOWER(pembiayaanBSI.segmenBSI) = ''consumer'' and LOWER(pembiayaanBSI.namaProdukBSI) IN (''pensiun'', ''pensiun berkah'', ''pensiunan asabri'', ''pensiunan asn'', ''pensiunan berkah'', ''pensiunan bumn'') then ''PMPNR_IDR'' when LOWER(pembiayaanBSI.jenisValuta) = ''idr'' and LOWER(pembiayaanBSI.segmenBSI) = ''mikro'' then ''PMMICNR_IDR'' when LOWER(pembiayaanBSI.jenisValuta) <> ''idr'' then ''PMLNR_V'' else ''PMLNR_IDR'' END AS code INTO '+@masterLastMonthTable+' FROM [ddm].[masterPembiayaan_BSI_'+@lastMonthDate+'] pembiayaanBSI LEFT OUTER JOIN ##LoanType'+@randomString+' loan on loan.ProductKey = pembiayaanBSI.kodeProduk WHERE LOWER(loan.MappingIRRBB) = ''sesuai jangka waktu'' ;'; set @sql = @sql + ' With xTblAmount AS ( Select SUM(ISNULL(sisaPokokPSAK,0) - ISNULL(imbalanAkanDiterima,0)) as sisaPokokPSAKImbalan, code FROM '+@masterTable+' Group By code ) UPDATE '+@resultTable+' SET totalEarlyPrepayment = sisaPokokPSAKImbalan From xTblAmount Where '+@resultTable+'.componentCode = xTblAmount.code ; With xTblAmount AS ( Select SUM(ISNULL(sisaPokokPSAK,0) - ISNULL(imbalanAkanDiterima,0)) as sisaPokokPSAKImbalan, code FROM '+@masterLastMonthTable+' Group By code ) UPDATE '+@resultTable+' SET totalOutstanding = sisaPokokPSAKImbalan From xTblAmount Where '+@resultTable+'.componentCode = xTblAmount.code ; UPDATE '+@resultTable+' SET sMM = CASE WHEN totalOutstanding = 0 THEN 0 ELSE CAST(totalEarlyPrepayment AS float) / CAST(totalOutstanding AS float) * 100 END ; Select * from '+@resultTable+'; DROP TABLE IF EXISTS '+@resultTable+'; DROP TABLE IF EXISTS '+@masterTable+'; DROP TABLE IF EXISTS '+@masterLastMonthTable+'; '; --print(@sql) --select(@sql) --jika ingin lihat query sql nya exec (@sql) END;
Editor is loading...
Leave a Comment