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  (NUM_RUC END)
		        ORDER BY NUM_RUC DESC, CODSBS
			) ORDER_CODSBS,
		      CODSBS,  
			  NUM_RUC, 
			  NUMDOCTRIB,
			  CODDOC_TRDT,
			  CODDOC, 
			  FROM NUEVA_MAE_PERSONA_RCC_VPC
	) 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, 
		  NUMDOCTRIB,
		  CODDOC_TRDT,
		  CODDOC, 
	    FROM NUEVA_MAE_PERSONA_RCC_VPC
	    WHERE CODDOC NOT LIKE '' AND CODDOC <> '.' 
		--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 LENGTH(R.CODDOC) = 11 AND R.CODDOC LIKE '10%' THEN SUBSTRING(R.CODDOC, 3, 8) 
		   ELSE R.CODDOC END
	)
	WHERE 1 = 1
	AND A.FECPROCESO = '22/04/2022' -->= '23/11/2021'
	--AND A.ESTADO = 0 --- se retira
	GROUP BY A.FECPROCESO,  TRIM(A.NUMRUC)