-- INSERT: complex CTE chain with ROW_NUMBER and many JOINs
INSERT /*+ APPEND */ INTO RPT.FCT_CUSTOMER_SEGMENT_STG (
		DT_LOAD,
		NAME_CUSTOMER,
		NUM_REGISTRATION,
		TAX_ID,
		ID_CUSTOMER,
		IS_SMB_FLAG,
		AMT_ALL_AGR_RESTSUM,
		AMT_MAX_ALL_AGRSUM_HIST,
		AMT_SMB_REVENUE,
		AMT_REVENUE,
		AMT_REVENUE_THREE,
		DT_REVENUE,
		DT_REVENUE_THREE,
		DT_SMB_REVENUE,
		AMT_DEF_SUM_P,
		LOAN_NUM,
		LOAN_ID,
		NUM_ACC_BAL2,
		IS_LOAN_FLAG,
		AMT_DEF_SUM_E,
		SEGMENT_ABS_SEGMENT,
		VALUE_PROJECT_WORTH_LVL,
		VALUE_PROJECT_PHASE,
		CODE_PRODUCT,
		NAME_ECON_PROG,
		SEGMENT_CLASS,
		SEGMENT_SUBCLASS,
		SEGMENT_SEGMENT,
		RISK_SEGMENT_BORROWER,
		REGULATORY_CLASS_SUBCLASS,
		REGULATORY_CLASS_SUBCLASS_CODE,
		LOAN_TYPE_NAME,
		ATTRIBUTE_C,
		ID_PROJECT,
		ID_SYSTEM,
		DTTM_UPDATE,
		DTTM_INSERT,
		ID_PROCESS
	)
	WITH MAX_ALL_AGRSUM AS (
		SELECT
			TAX_ID,
			MAX(AMT_ALL_AGR_RESTSUM) AMT_MAX_ALL_AGRSUM_HIST
		FROM RPT.FCT_CUSTOMER_SEGMENT
		WHERE DT_LOAD < p_load_date
		  AND TAX_ID IS NOT NULL
		GROUP BY
			TAX_ID
	), SEGMENT_1 AS (
		SELECT
			 JP.CUSTOMER_NAME AS NAME_CUSTOMER
			,JP.NUM_REGISTRATION
			,JP.TAX_ID
			,JP.ID_CUSTOMER
			,JP.IS_SMB_FLAG
			,JP.ID_PROJECT
			,NVL(SUM(CASE WHEN JP.TAX_ID IS NOT NULL THEN NVL(AGR.AMT_DEF_SUM_E, 0) + NVL(AGR.AMT_DEF_SUM_P, 0) ELSE NULL END) OVER (PARTITION BY JP.TAX_ID),
				 SUM(NVL(AGR.AMT_DEF_SUM_E, 0) + NVL(AGR.AMT_DEF_SUM_P, 0)) OVER (PARTITION BY JP.ID_CUSTOMER)) AS AMT_ALL_AGR_RESTSUM
			,NVL(MH.AMT_MAX_ALL_AGRSUM_HIST, 0) AMT_MAX_ALL_AGRSUM_HIST
			,SLV.AMT_SMB_REVENUE
			,SLV.AMT_REVENUE
			,SLV.AMT_REVENUE_THREE
			,SLV.DT_REVENUE
			,SLV.DT_REVENUE_THREE
			,SLV.DT_SMB_REVENUE
			,AGR.AMT_DEF_SUM_P
			,AGR.LOAN_NUM
			,AGR.LOAN_ID
			,AGR.NUM_ACC_BAL2
			,AGR.DEBT_FLAG
			,AGR.IS_LOAN_FLAG
			,AGR.AMT_DEF_SUM_E
			,AGR.LOAN_TYPE_NAME
		FROM STG.STG_CUST_SEG_LOAN AGR
		INNER JOIN STG.STG_CUST_SEG_CUSTOMER JP ON JP.ID_CUSTOMER = AGR.ID_CUSTOMER
		LEFT JOIN STG.STG_CUST_SEG_REVENUE SLV ON SLV.ID_SUBJECT = AGR.ID_CUSTOMER
		LEFT JOIN MAX_ALL_AGRSUM MH ON MH.TAX_ID = JP.TAX_ID
	), PREVIOUS_RISK_SEGMENT AS (
		SELECT DISTINCT
			TAX_ID,
			RISK_SEGMENT_BORROWER
		FROM RPT.FCT_CUSTOMER_SEGMENT PREVIOUS
		WHERE PREVIOUS.DT_LOAD = p_prev_load_date
		  AND RISK_SEGMENT_BORROWER IN ('SEG-A1', 'SEG-A2', 'SEG-A3')
	), SEGMENT_2 AS (
		SELECT
			 S1.LOAN_ID
			,S1.LOAN_NUM
			,S1.ID_CUSTOMER
			,S1.TAX_ID
			,S1.AMT_DEF_SUM_E
			,S1.AMT_DEF_SUM_P
			,CASE
				WHEN ADC.ID_DEAL IS NOT NULL THEN 'SEG-B'
				ELSE NULL
			 END AS SEGMENT_ABS_SEGMENT
			,'SEG' AS SEGMENT_CLASS
			,CASE
				  WHEN S1.DEBT_FLAG = 1 THEN 'SEG-X'
				  WHEN ADC.ID_DEAL IS NOT NULL THEN 'SEG-B'
				  ELSE 'SEG-A'
			  END AS SEGMENT_SUBCLASS
			,CASE
				WHEN S1.DEBT_FLAG = 1 THEN 'SEG-X'
				WHEN ADC.CODE_CL = 'CL_TYPE_B1' THEN 'SEG-B1'
				WHEN ADC.CODE_CL = 'CL_TYPE_B2' THEN 'SEG-B2'
				WHEN ADC.CODE_CL = 'CL_TYPE_B3' THEN 'SEG-B3'
				WHEN ADC.CODE_CL = 'CL_TYPE_B4' THEN 'SEG-B4'
				WHEN ADC.CODE_CL = 'CL_TYPE_B5' THEN 'SEG-B5'
				WHEN PREVIOUS.RISK_SEGMENT_BORROWER = 'SEG-A1' THEN
					CASE
						WHEN S1.AMT_REVENUE_THREE >= p_threshold_l * p_coef_down OR (NVL(S1.AMT_REVENUE_THREE, 0) = 0 AND S1.AMT_SMB_REVENUE >= p_threshold_l * p_coef_down) THEN 'SEG-A1'
						WHEN S1.AMT_REVENUE >= p_threshold_m * p_coef_down OR S1.AMT_ALL_AGR_RESTSUM >= p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST >= p_threshold_amt THEN 'SEG-A2'
						WHEN S1.AMT_REVENUE < p_threshold_m * p_coef_down OR S1.AMT_ALL_AGR_RESTSUM < p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST < p_threshold_amt THEN 'SEG-A3'
					END
				WHEN PREVIOUS.RISK_SEGMENT_BORROWER = 'SEG-A2' THEN
					CASE
						WHEN S1.AMT_REVENUE_THREE >= p_threshold_l * p_coef_up OR (NVL(S1.AMT_REVENUE_THREE, 0) = 0 AND S1.AMT_SMB_REVENUE >= p_threshold_l * p_coef_up) THEN 'SEG-A1'
						WHEN S1.AMT_REVENUE >= p_threshold_m * p_coef_down OR S1.AMT_ALL_AGR_RESTSUM >= p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST >= p_threshold_amt THEN 'SEG-A2'
						WHEN S1.AMT_REVENUE < p_threshold_m * p_coef_down OR S1.AMT_ALL_AGR_RESTSUM < p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST < p_threshold_amt THEN 'SEG-A3'
					END
				WHEN PREVIOUS.RISK_SEGMENT_BORROWER = 'SEG-A3' THEN
					CASE
						WHEN S1.AMT_REVENUE_THREE >= p_threshold_l * p_coef_up OR (NVL(S1.AMT_REVENUE_THREE, 0) = 0 AND S1.AMT_SMB_REVENUE >= p_threshold_l * p_coef_up) THEN 'SEG-A1'
						WHEN S1.AMT_REVENUE >= p_threshold_m * p_coef_up OR S1.AMT_ALL_AGR_RESTSUM >= p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST >= p_threshold_amt THEN 'SEG-A2'
						WHEN S1.AMT_REVENUE < p_threshold_m * p_coef_up OR S1.AMT_ALL_AGR_RESTSUM < p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST < p_threshold_amt THEN 'SEG-A3'
					END
				WHEN S1.AMT_REVENUE_THREE >= p_threshold_l OR (NVL(S1.AMT_REVENUE_THREE, 0) = 0 AND S1.AMT_SMB_REVENUE >= p_threshold_l) THEN 'SEG-A1'
				WHEN S1.AMT_REVENUE >= p_threshold_m OR S1.AMT_ALL_AGR_RESTSUM >= p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST >= p_threshold_amt THEN 'SEG-A2'
				WHEN S1.AMT_REVENUE < p_threshold_m OR S1.AMT_ALL_AGR_RESTSUM < p_threshold_amt OR S1.AMT_MAX_ALL_AGRSUM_HIST < p_threshold_amt THEN 'SEG-A3'
		 	 END AS SEGMENT_SEGMENT
		FROM SEGMENT_1 S1
		LEFT JOIN STG.STG_CUST_SEG_DEAL_ATTR ADC ON S1.LOAN_ID = ADC.ID_DEAL
																				   AND ADC.TYPE = 1
		LEFT JOIN PREVIOUS_RISK_SEGMENT PREVIOUS ON PREVIOUS.TAX_ID = S1.TAX_ID
	), CTE_RISK_SEGMENT_BORROWER AS (
		SELECT
			CURR.TAX_ID,
			CASE
				WHEN PREVIOUS.RISK_SEGMENT_BORROWER IN  ('SEG-A1', 'SEG-A2') AND CURR.RISK_SEGMENT_BORROWER = 'SEG-A3' THEN PREVIOUS.RISK_SEGMENT_BORROWER
				ELSE CURR.RISK_SEGMENT_BORROWER
			END AS RISK_SEGMENT_BORROWER
		FROM (
			SELECT
				TAX_ID,
				RISK_SEGMENT_BORROWER,
				ROW_NUMBER() OVER(PARTITION BY TAX_ID ORDER BY AMT_DEF_SUM DESC) RN
			FROM (
				SELECT
					S.TAX_ID,
					S.SEGMENT_SEGMENT AS RISK_SEGMENT_BORROWER,
					SUM(NVL(S.AMT_DEF_SUM_E, 0) + NVL(S.AMT_DEF_SUM_P, 0)) AS AMT_DEF_SUM
				FROM SEGMENT_2 S
				WHERE SEGMENT_SEGMENT != 'SEG-X'
				GROUP BY
					S.TAX_ID,
					S.SEGMENT_SEGMENT
			)
		) CURR
		LEFT JOIN PREVIOUS_RISK_SEGMENT PREVIOUS ON PREVIOUS.TAX_ID = CURR.TAX_ID
		WHERE RN = 1
	), SEGMENT_3 AS (
		SELECT
			 S2.LOAN_ID
			,S2.LOAN_NUM
			,S2.TAX_ID
			,S2.SEGMENT_ABS_SEGMENT
			,S2.SEGMENT_CLASS
			,S2.SEGMENT_SUBCLASS
			,S2.SEGMENT_SEGMENT
			,CASE
				WHEN SEGMENT_SUBCLASS = 'SEG-X' THEN 'SEG-X'
				ELSE RSB.RISK_SEGMENT_BORROWER
			 END AS RISK_SEGMENT_BORROWER
		FROM SEGMENT_2 S2
			LEFT JOIN CTE_RISK_SEGMENT_BORROWER RSB ON S2.TAX_ID = RSB.TAX_ID
		GROUP BY
			 S2.LOAN_ID
			,S2.LOAN_NUM
			,S2.TAX_ID
			,S2.SEGMENT_ABS_SEGMENT
			,S2.SEGMENT_CLASS
			,S2.SEGMENT_SUBCLASS
			,S2.SEGMENT_SEGMENT
			,CASE
				WHEN SEGMENT_SUBCLASS = 'SEG-X' THEN 'SEG-X'
				ELSE RSB.RISK_SEGMENT_BORROWER
			 END
	), SEGMENT_4 AS (
		SELECT
			 S1.NAME_CUSTOMER
			,S1.NUM_REGISTRATION
			,S1.TAX_ID
			,S1.ID_CUSTOMER
			,S1.IS_SMB_FLAG
			,S1.AMT_ALL_AGR_RESTSUM
			,AMT_MAX_ALL_AGRSUM_HIST
			,S1.AMT_SMB_REVENUE
			,S1.AMT_REVENUE
			,S1.AMT_REVENUE_THREE
			,S1.DT_REVENUE
			,S1.DT_REVENUE_THREE
			,S1.DT_SMB_REVENUE
			,S1.AMT_DEF_SUM_P
			,S1.LOAN_NUM
			,S1.LOAN_ID
			,S1.NUM_ACC_BAL2
			,S1.IS_LOAN_FLAG
			,S1.AMT_DEF_SUM_E
			,S3.SEGMENT_ABS_SEGMENT
			,S3.SEGMENT_CLASS
			,S3.SEGMENT_SUBCLASS
			,CASE
				WHEN S1.IS_LOAN_FLAG = 'Y'
				 AND S1.IS_SMB_FLAG = 'Y'
				 AND S1.AMT_ALL_AGR_RESTSUM <= 70000000
				 AND S3.SEGMENT_SEGMENT ='SEG-A3'
				 	THEN 'SEG-C'
				ELSE S3.SEGMENT_SEGMENT
			 END AS SEGMENT_SEGMENT
			,CASE
				WHEN S1.IS_LOAN_FLAG = 'Y'
				 AND S1.IS_SMB_FLAG = 'Y'
				 AND S1.AMT_ALL_AGR_RESTSUM <= 70000000
				 AND S3.SEGMENT_SEGMENT ='SEG-A3'
				 	THEN 'SEG-C'
			 	ELSE S3.RISK_SEGMENT_BORROWER
		 	 END AS RISK_SEGMENT_BORROWER
			,S1.LOAN_TYPE_NAME
			,S1.ID_PROJECT
		FROM SEGMENT_1 S1
		LEFT JOIN SEGMENT_3 S3 ON S1.LOAN_ID = S3.LOAN_ID
	)
	SELECT
		 p_load_date AS DT_LOAD
		,S4.NAME_CUSTOMER
		,S4.NUM_REGISTRATION
		,S4.TAX_ID
		,S4.ID_CUSTOMER
		,S4.IS_SMB_FLAG
		,S4.AMT_ALL_AGR_RESTSUM
		,S4.AMT_MAX_ALL_AGRSUM_HIST
		,S4.AMT_SMB_REVENUE
		,S4.AMT_REVENUE
		,S4.AMT_REVENUE_THREE
		,S4.DT_REVENUE
		,S4.DT_REVENUE_THREE
		,S4.DT_SMB_REVENUE
		,S4.AMT_DEF_SUM_P
		,S4.LOAN_NUM
		,S4.LOAN_ID
		,S4.NUM_ACC_BAL2
		,S4.IS_LOAN_FLAG
		,S4.AMT_DEF_SUM_E
		,S4.SEGMENT_ABS_SEGMENT
		,PWL.CODE_CL AS VALUE_PROJECT_WORTH_LVL
		,PP.CODE_CL AS VALUE_PROJECT_PHASE
		,NULL AS CODE_PRODUCT
		,EP.CODE_CL AS NAME_ECON_PROG
		,S4.SEGMENT_CLASS
		,S4.SEGMENT_SUBCLASS
		,S4.SEGMENT_SEGMENT
		,S4.RISK_SEGMENT_BORROWER
		,CASE WHEN S4.RISK_SEGMENT_BORROWER IN ('SEG-A3', 'SEG-A2', 'SEG-A1') AND S4.IS_SMB_FLAG = 'N' THEN 'A1'
			  WHEN S4.RISK_SEGMENT_BORROWER IN ('SEG-A3', 'SEG-A2', 'SEG-A1') AND S4.IS_SMB_FLAG = 'Y' THEN 'A2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B1' AND S4.IS_SMB_FLAG = 'N' THEN 'B1'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B1' AND S4.IS_SMB_FLAG = 'Y' THEN 'B2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B2' AND S4.IS_SMB_FLAG = 'N' THEN 'C1'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B2' AND S4.IS_SMB_FLAG = 'Y' THEN 'C2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B3' AND S4.IS_SMB_FLAG = 'N' THEN 'D1'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B3' AND S4.IS_SMB_FLAG = 'Y' THEN 'D2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B4' AND S4.IS_SMB_FLAG = 'N' THEN 'E1'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B4' AND S4.IS_SMB_FLAG = 'Y' THEN 'E2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B5' AND S4.IS_SMB_FLAG = 'N' THEN 'F1'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-B5' AND S4.IS_SMB_FLAG = 'Y' THEN 'F2'
			  WHEN S4.SEGMENT_SUBCLASS = 'SEG-X' AND S4.IS_SMB_FLAG = 'N' THEN 'G1'
			  WHEN S4.SEGMENT_SUBCLASS = 'SEG-X' AND S4.IS_SMB_FLAG = 'Y' THEN 'G2'
			  WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-C' THEN 'H2'
		 END AS REGULATORY_CLASS_SUBCLASS
		,CASE
			WHEN S4.RISK_SEGMENT_BORROWER = 'SEG-C' THEN '2'
			ELSE '1'
		 END AS REGULATORY_CLASS_SUBCLASS_CODE
		,S4.LOAN_TYPE_NAME
		,'C' AS ATTRIBUTE_C
		,S4.ID_PROJECT
		,1 AS ID_SYSTEM
		,SYSDATE AS DTTM_UPDATE
		,SYSDATE AS DTTM_INSERT
		,p_process_id AS ID_PROCESS
	FROM SEGMENT_4 S4
	LEFT JOIN STG.STG_CUST_SEG_DEAL_ATTR PWL ON S4.LOAN_ID = PWL.ID_DEAL
																			   AND PWL.TYPE = 2
	LEFT JOIN STG.STG_CUST_SEG_DEAL_ATTR PP ON S4.LOAN_ID = PP.ID_DEAL
																			  AND PP.TYPE = 3
	LEFT JOIN STG.STG_CUST_SEG_DEAL_ATTR EP ON S4.LOAN_ID = EP.ID_DEAL
																			  AND EP.TYPE = 4;
