SP Loan Prepayment Rate

		DROP TABLE IF EXISTS #TempResultLoan_random;
		CREATE TABLE #TempResultLoan_random (
			totalEarlyPrepayment FLOAT,
			totalOutstanding FLOAT,
			sMM FLOAT,
			componentCode VARCHAR(20)
		);

		DROP TABLE IF EXISTS #TempRepricingGap_random;
		CREATE TABLE #TempRepricingGap_random (
			sisaPokokPSAKValue DECIMAL(32,2),
			imbalanAkanDiterimaValue DECIMAL(32,2),
			smm FLOAT,
			code VARCHAR(20),
			jenisValuta VARCHAR(20)
		);
		 INSERT INTO #TempResultLoan_random (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 #TempQuery_random;
		 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 #TempQuery_random
		 FROM [ddm].[masterPembiayaan_close_20230630] pembiayaanClose
		 LEFT OUTER JOIN [ddm].[masterPembiayaan_BSI_20230531] pembiayaanLastBSI on pembiayaanLastBSI.noRekening = pembiayaanClose.noRekening
		 LEFT OUTER JOIN ##LoanTyperandom loan on loan.ProductKey = pembiayaanLastBSI.kodeProduk  
		 WHERE LOWER(loan.MappingIRRBB) = 'sesuai jangka waktu'
		   AND pembiayaanClose.pelunasDipercepat = 'Y'
		   AND YEAR(CAST(pembiayaanClose.CloseDate AS DATE)) = 2023 and FORMAT(MONTH(CAST(pembiayaanClose.CloseDate AS DATE)), '00') = 06
		 ;
		   
		   
		 DROP TABLE IF EXISTS #TempQuerySecond_random;
		 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 #TempQuerySecond_random
		 FROM [ddm].[masterPembiayaan_BSI_20230531] pembiayaanBSI
		 LEFT OUTER JOIN ##LoanTyperandom loan on loan.ProductKey = pembiayaanBSI.kodeProduk  
		 WHERE LOWER(loan.MappingIRRBB) = 'sesuai jangka waktu'
		 ;
		With xTempResult AS (
			SELECT 
				CAST(resultThisMonth AS float) totalEarlyPrepayment,
				CAST(resultLastMonth AS float) totalOutstanding,
				CASE WHEN resultLastMonth = 0 THEN 0
				ELSE
					CAST(resultThisMonth AS float) / CAST(resultLastMonth AS float)
				END AS sMM
				, pembiayaanBSICode componentCode
			FROM (
				SELECT
					SUM(COALESCE(A.sisaPokokPSAK,0) - COALESCE(A.imbalanAkanDiterima,0)) as resultThisMonth,
					SUM(COALESCE(B.sisaPokokPSAK,0) - COALESCE(B.imbalanAkanDiterima,0)) as resultLastMonth,
					A.code pembiayaanBSICode, B.code pembiayaanBSILastCode
				FROM #TempQuery_random A
				LEFT OUTER JOIN #TempQuerySecond_random B on A.code = B.code
				GROUP BY A.code, B.code
			) A
		) 

		UPDATE #TempResultLoan_random
		SET totalEarlyPrepayment = xTempResult.totalEarlyPrepayment,
			totalOutstanding = xTempResult.totalOutstanding,
			sMM = xTempResult.sMM
		FROM xTempResult
		WHERE #TempResultLoan_random.componentCode = xTempResult.componentCode
		;

		Select * from #TempResultLoan_random;

		--SELECT smm AS SMM FROM #TempRepricingGap_random
		DROP TABLE IF EXISTS #TempResultLoan_random;
		DROP TABLE IF EXISTS #TempRepricingGap_random;
		DROP TABLE IF EXISTS #TempQuery_random;
		DROP TABLE IF EXISTS #TempQuerySecond_random;