SELECT 
	    A.FECPROCESO, TRIM(A.NUMRUC) NUMRUC, 
		MAX(S.CODUNICOCLI) cod_unico_val,
		MAX(CASE WHEN S.ORDER_CODSBS = 1 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_1,
		MAX(CASE WHEN S.ORDER_CODSBS = 2 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_2,
		MAX(CASE WHEN S.ORDER_CODSBS = 3 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_3,
		MAX(CASE WHEN S.ORDER_CODSBS = 4 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_4,
		MAX(CASE WHEN S.ORDER_CODSBS = 5 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_5,
		MAX(CASE WHEN S.ORDER_CODSBS = 6 THEN S.CODSBS ELSE NULL END ) cod_sbs_empresa_6,
	    MAX(CASE WHEN R.RRLL_CODSBS = 1 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_1,
		MAX(CASE WHEN R.RRLL_CODSBS = 2 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_2,
		MAX(CASE WHEN R.RRLL_CODSBS = 3 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_3,
		MAX(CASE WHEN R.RRLL_CODSBS = 4 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_4,
		MAX(CASE WHEN R.RRLL_CODSBS = 5 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_5,
		MAX(CASE WHEN R.RRLL_CODSBS = 6 THEN R.CODSBS ELSE NULL END ) cod_sbs_rrll_6

    --SELECT *
	FROM E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT_HIST A
	LEFT JOIN E_DW_VIEWS.V_REP_LEGALES_SUNAT_HIST C
	ON A.numruc = C.numruc
    AND A.fecproceso = C.fecproceso
	LEFT JOIN (
		    SELECT ROW_NUMBER() OVER (PARTITION BY  (
			    CASE WHEN NUMDOCTRIB LIKE '.' OR  NUMDOCTRIB LIKE '' OR NUMDOCTRIB IS NULL THEN NUMDOCTRIB_TRDT ELSE NUMDOCTRIB END)
		        ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS
			) ORDER_CODSBS,
		      CODSBS,  
			  NUMDOCTRIB_TRDT, 
			  NUMDOCTRIB,
			  CODDOC_TRDT,
			  CODDOC, 
			  CODUNICOCLI
			  FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
			  --where NUMDOCTRIB = '10463839773' OR NUMDOCTRIB_TRDT = '10463839773'
		) S
		ON A.NUMRUC = (CASE WHEN S.NUMDOCTRIB LIKE '.' OR  S.NUMDOCTRIB LIKE '' OR S.NUMDOCTRIB IS NULL THEN S.NUMDOCTRIB_TRDT ELSE S.NUMDOCTRIB END)
		LEFT JOIN (
		    SELECT ROW_NUMBER() OVER (PARTITION BY (
		        CASE WHEN CODDOC LIKE '.' OR  CODDOC LIKE '' OR CODDOC IS NULL THEN CODDOC_TRDT ELSE CODDOC END)
		        ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS
		    ) RRLL_CODSBS,
		     CODSBS,  
		     NUMDOCTRIB_TRDT, 
		    NUMDOCTRIB, 
		    CODDOC_TRDT, 
		    CODDOC, 
		    CODUNICOCLI
		    FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
		    WHERE CODDOC NOT LIKE '.' OR CODDOC_TRDT NOT LIKE '.' 
			--LENGTH(CODDOC) = 8 OR LENGTH(CODDOC_TRDT) = 8
		) R
		ON (
			    CASE WHEN C.NUMRUC IS NOT NULL AND C.ESTADO = 0 THEN  C.CODDOC 
		            WHEN LENGTH(A.NUMRUC) = 11 AND A.NUMRUC LIKE '10%' THEN SUBSTRING(A.NUMRUC, 3, 8) 
					ELSE 'X' END
		) =  (CASE WHEN R.CODDOC LIKE '.' OR  R.CODDOC LIKE '' OR R.CODDOC IS NULL THEN R.CODDOC_TRDT ELSE R.CODDOC END)
		WHERE 1 = 1
		AND A.FECPROCESO >= '23/11/2021'
		--AND A.ESTADO = 0 --- se retira
		GROUP BY A.FECPROCESO,  TRIM(A.NUMRUC)