SP Loan

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;