Paste code

------ Universo SF ------

drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF as (
SELECT 
		DISTINCT
		a.Periodo_val,
		a.cod_sbs_val,
		a.tipo_producto_rcc_desc,
		A.Flg_Lin_Mercado,
		a.NombreEmpresaFinanc_Desc,
		a.Empresa_CD,
		PRODUCTO_RCC_DESC,
		SITUACION_RCC_DESC,
		SALDO_AJUSTADO_AMT
from E_DW_VIEWS.V_FACT_VPC_IRCC_SALDO_AJUST a 
	where  (A.Flg_Lin_Mercado=&#039;S&#039; or a.tipo_producto_rcc_desc in (&#039;REACTIVA&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;,&#039;GARANTIAS&#039;)) 
	and PERIODO_VAL >= &#039;202201&#039; 
	and SITUACION_RCC_DESC  IN (&#039;VIGENTE&#039;, &#039;VENCIDO&#039;) AND PRODUCTO_RCC_DESC IN (&#039;FACTORING&#039;, &#039;DESCUENTOS&#039;)
) WITH DATA PRIMARY INDEX (cod_sbs_val);

-- SACANDO EL RUC DE LAS PERSONAS
SELECT PERIODO_VAL, COUNT(1) FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF GROUP BY PERIODO_VAL; 
---RCC PERSONA

DROP TABLE DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA
as
(
SELECT A.CODSBS,
    A.TIPPERSONA,
    CASE
        WHEN A.CODDOC = &#039;.&#039; THEN &#039;99&#039;
        ELSE A.TIPDOC
    END AS TIPDOC,
    CASE
        WHEN A.CODDOC = &#039;.&#039; THEN A.CODSBS
        ELSE A.CODDOC
    END AS CODDOC,
    A.CODUNICOCLI_F AS CODUNICOCLI,
    A.NOMBRE_COMPLETO,
    NVL(A.APELLIDO_PATERNO, &#039;.&#039;) AS APELLIDO_PATERNO,
    NVL(A.APELLIDO_MATERNO, &#039;.&#039;) AS APELLIDO_MATERNO,
    NVL(A.PRIMER_NOMBRE, &#039;.&#039;) AS PRIMER_NOMBRE,
    NVL(A.SEGUNDO_NOMBRE, &#039;.&#039;) AS SEGUNDO_NOMBRE,
    A.TIP_DOC,
    A.NUM_DOC,
    A.NUM_RUC,
    A.FECACTUALIZACIONTABLA,
    A.TIP_DOC_TRIB,
    A.NUM_DOC_TRIB
FROM (
        SELECT A.*,
            RTRIM(
                NVL(A.APELLIDO_PATERNO, &#039;&#039;) || &#039; &#039; || NVL(A.APELLIDO_MATERNO, &#039;&#039;) || &#039; &#039; || NVL(A.PRIMER_NOMBRE, &#039;&#039;) || &#039; &#039; || NVL(A.SEGUNDO_NOMBRE, &#039;&#039;)
            ) AS NOMBRE_COMPLETO,
            CASE
                WHEN NVL(A.TIPDOC, &#039;&#039;) <> &#039;2&#039; THEN A.TIPDOC
                WHEN A.TIPDOC = &#039;2&#039;
                AND LENGTH(A.CODDOC) = 11
                AND A.CODDOC LIKE &#039;10%&#039; THEN &#039;1&#039;
                WHEN A.TIPDOC = &#039;2&#039;
                AND LENGTH(A.CODDOC) = 11
                AND A.CODDOC LIKE ANY (&#039;15%&#039;, &#039;17%&#039;, &#039;20%&#039;) THEN &#039;.&#039;
                ELSE A.TIPDOC
            END AS TIP_DOC,
            CASE
                WHEN NVL(A.TIPDOC, &#039;&#039;) <> &#039;2&#039; THEN A.CODDOC
                WHEN A.TIPDOC = &#039;2&#039;
                AND LENGTH(A.CODDOC) = 11
                AND A.CODDOC LIKE &#039;10%&#039; THEN SUBSTRING(A.CODDOC, 3, 8)
                WHEN A.TIPDOC = &#039;2&#039;
                AND LENGTH(A.CODDOC) = 11
                AND A.CODDOC LIKE ANY (&#039;15%&#039;, &#039;17%&#039;, &#039;20%&#039;) THEN &#039;.&#039;
                ELSE A.CODDOC
            END AS NUM_DOC,
            CASE
                WHEN A.TIPDOC = &#039;2&#039;
                AND LENGTH(A.CODDOC) = 11
                AND A.CODDOC LIKE ANY (&#039;10%&#039;, &#039;15%&#039;, &#039;17%&#039;, &#039;20%&#039;) THEN A.CODDOC
                WHEN A.TIPDOC = &#039;1&#039; THEN COALESCE(B.NUMRUC, R.RUC15, &#039;.&#039;)
                ELSE &#039;.&#039;
            END AS NUM_RUC,
            COALESCE (
                A.CODUNICOCLI,
                CASE
                    WHEN LENGTH(TRIM(C.CUC_NUM)) = 10 THEN TRIM(C.CUC_NUM)
                    ELSE NULL
                END,
                CASE
                    WHEN LENGTH(TRIM(D.CUC_NUM)) = 10 THEN TRIM(D.CUC_NUM)
                    ELSE NULL
                END
            ) AS CODUNICOCLI_F,
            COALESCE (
                CASE
                    WHEN A.CODUNICOCLI IS NOT NULL THEN CURRENT_DATE()
                    ELSE NULL
                END,
                CASE
                    WHEN LENGTH(TRIM(C.CUC_NUM)) = 10 THEN C.APERTURA_CUC_FC
                    ELSE NULL
                END,
                CASE
                    WHEN LENGTH(TRIM(D.CUC_NUM)) = 10 THEN D.APERTURA_CUC_FC
                    ELSE NULL
                END
            ) AS APERTURA_CUC_FC
        FROM (
                SELECT A.CODSBS,
                    NVL(A.TIPPERSONA, 0) AS TIPPERSONA,
                    CAST(
                        NULLIF(
                            CASE
                                WHEN A.TIPDOCTRIB = 3
                                AND LENGTH(A.NUMDOCTRIB) = 11
                                AND (A.NUMDOCTRIB LIKE ANY (&#039;10%&#039;, &#039;15%&#039;, &#039;17%&#039;, &#039;20%&#039;)) THEN 2
                                ELSE A.TIPDOCTRIB
                            END,
                            -1
                        ) AS VARCHAR(1)
                    ) AS TIP_DOC_TRIB,
                    NULLIF(A.NUMDOCTRIB, &#039;.&#039;) AS NUM_DOC_TRIB,
                    CASE
                        WHEN A.TIPPERSONA IN (1, 3, 4) THEN CASE
                            WHEN A.TIPDOC = &#039;2&#039;
                            AND A.CODDOC = A.CODDOC_TRDT
                            AND A.TIPDOC_TRDT IS NOT NULL THEN A.TIPDOC_TRDT
                            ELSE A.TIPDOC
                        END
                        ELSE CASE
                            WHEN A.TIPDOCTRIB = 3
                            AND LENGTH(A.NUMDOCTRIB) = 11
                            AND (A.NUMDOCTRIB LIKE ANY (&#039;10%&#039;, &#039;15%&#039;, &#039;17%&#039;, &#039;20%&#039;)) THEN &#039;2&#039;
                            ELSE CAST(A.TIPDOCTRIB AS VARCHAR(2))
                        END
                    END AS TIPDOC,
                    CASE
                        WHEN A.TIPPERSONA IN (1, 3, 4) THEN A.CODDOC
                        ELSE A.NUMDOCTRIB
                    END AS CODDOC,
                    CASE
                        WHEN LENGTH(TRIM(A.CODUNICOCLI)) = 10 THEN TRIM(A.CODUNICOCLI)
                        ELSE NULL
                    END AS CODUNICOCLI,
                    CAST(
                        UPPER(TRIM(NULLIF(OREPLACE(A.APPATERNO, &#039;"&#039;, &#039;&#039;), &#039;.&#039;))) AS VARCHAR(120)
                    ) AS APELLIDO_PATERNO,
                    CAST(
                        UPPER(TRIM(NULLIF(OREPLACE(A.APMATERNO, &#039;"&#039;, &#039;&#039;), &#039;.&#039;))) AS VARCHAR(40)
                    ) AS APELLIDO_MATERNO,
                    CAST(
                        UPPER(TRIM(NULLIF(OREPLACE(A.PRIMERNBR, &#039;"&#039;, &#039;&#039;), &#039;.&#039;))) AS VARCHAR(40)
                    ) AS PRIMER_NOMBRE,
                    CAST(
                        UPPER(TRIM(NULLIF(OREPLACE(A.SEGUNDONBR, &#039;"&#039;, &#039;&#039;), &#039;.&#039;))) AS VARCHAR(40)
                    ) AS SEGUNDO_NOMBRE,
                    A.FECACTUALIZACIONTABLA
                FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC A
            ) A
            LEFT JOIN E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT B ON A.TIPDOC = &#039;1&#039;
            AND SUBSTRING(B.NUMRUC, 1, 2) = &#039;10&#039;
            AND A.CODDOC = SUBSTRING(B.NUMRUC, 3, 8)
            LEFT JOIN DLAB_PROGRAMADATOSVPC.DE_CLIENTE_RUC15 R ON A.TIPDOC = R.TIPDOC
            AND A.CODDOC = R.CODDOC
            LEFT JOIN E_DW_VIEWS.V_SCO_LCL_MAEPERSONA C ON A.CODSBS = C.CLIENTE_SBS_CD
            LEFT JOIN E_DW_VIEWS.V_SCO_LCL_MAEPERSONA D ON A.TIPDOC = D.COD_TIPO_DOCUMENTO
            AND A.CODDOC = D.COD_DOCUMENTO
    ) A QUALIFY ROW_NUMBER() OVER(
        PARTITION BY A.CODSBS
        ORDER BY A.APERTURA_CUC_FC DESC
    ) = 1
)
WITH DATA
PRIMARY INDEX(CODSBS);

---

drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG as (
SELECT Periodo_val,Cod_SBS_Val,
		MAX(CASE WHEN TIPO_PRODUCTO_RCC_DESC IN (&#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;) AND SITUACION_RCC_DESC IN (&#039;VIGENTE&#039;,&#039;VENCIDO&#039;) AND PRODUCTO_RCC_DESC=&#039;FACTORING&#039; then 1
	 	 	 WHEN TIPO_PRODUCTO_RCC_DESC IN (&#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;) AND SITUACION_RCC_DESC IN (&#039;VIGENTE&#039;,&#039;VENCIDO&#039;) AND PRODUCTO_RCC_DESC= &#039;DESCUENTOS&#039; THEN 1 ELSE 0 END) AS TARGET_SF 

--max(CASE WHEN TIPO_PRODUCTO_RCC_DESC IN (&#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;) AND SITUACION_RCC_DESC=&#039;VIGENTE&#039; AND NOMBREEMPRESAFINANC_DESC =&#039;IBK&#039;	AND PRODUCTO_RCC_DESC=&#039;FACTORING&#039; THEN 1 ELSE 0 END) AS flg_FACTORING_IBK_VIGENTE
FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF A
group by Periodo_val,Cod_SBS_Val
)WITH DATA
PRIMARY INDEX(Cod_SBS_Val);

--

--- SACANDO EL RUC DE MIS CLIENTES sf 
drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC as (
SELECT * FROM (SELECT A.* , 
		B.NUM_RUC, 
		C.KEY_VALUE
FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG A 
LEFT JOIN DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA B
LEFT JOIN E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA C ON B.NUM_RUC = C.NRO_DOC
ON A.Cod_SBS_Val = B.CODSBS) A WHERE KEY_VALUE IS NOT NULL
)
WITH DATA
PRIMARY INDEX(Cod_SBS_Val);


SELECT Periodo_val, SUM(TARGET_SF), COUNT(1) FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC GROUP BY Periodo_val;

SELECT * FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC

SELECT * FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC ;

DEL DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA;