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
	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 NUM_RUC 
		        ORDER BY NUM_RUC DESC, CODSBS
			) ORDER_CODSBS,
		      CODSBS, 
			  NUM_RUC,
			  NUM_DOC_TRIB,
			  CODDOC,
			  CODUNICOCLI
			  FROM D_DW_VIEWS.V_MST_VPC_PERSONA
			  WHERE NUM_RUC IS NOT NULL
			        AND NUM_RUC <> '.' AND NUM_RUC NOT LIKE ''
	) S
	ON A.NUMRUC = S.NUM_RUC
	LEFT JOIN (
	    SELECT ROW_NUMBER() OVER (PARTITION BY
	        (CASE WHEN LENGTH(CODDOC) = 11 AND CODDOC LIKE '10%' THEN SUBSTRING(CODDOC, 3, 8) ELSE CODDOC END)
	        ORDER BY CODDOC DESC, TIPDOC ASC, CODSBS
	    ) RRLL_CODSBS,
	      CODSBS, 
		  NUM_RUC,
		  NUM_DOC_TRIB,
		  CODDOC
	    FROM D_DW_VIEWS.V_MST_VPC_PERSONA
	    WHERE CODDOC NOT LIKE '' AND CODDOC <> '.'
		      AND CODDOC IS NOT NULL 
			  AND CODDOC <> '00000000'
	) 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 LENGTH(R.CODDOC) = 11 AND R.CODDOC LIKE '10%' THEN SUBSTRING(R.CODDOC, 3, 8)
		        ELSE R.CODDOC END
	)
	--WHERE 1 = 1
	GROUP BY A.FECPROCESO,  TRIM(A.NUMRUC)