Psst.. new poll here.
[email protected] web/email now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as SQL by registered user vvillacorta ( 5 months ago )
------ 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='S' or a.tipo_producto_rcc_desc in ('REACTIVA','FAE','INMOBILIARIO','GARANTIAS'))
and PERIODO_VAL >= '202201'
and SITUACION_RCC_DESC IN ('VIGENTE', 'VENCIDO') AND PRODUCTO_RCC_DESC IN ('FACTORING', 'DESCUENTOS')
) 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 = '.' THEN '99'
ELSE A.TIPDOC
END AS TIPDOC,
CASE
WHEN A.CODDOC = '.' THEN A.CODSBS
ELSE A.CODDOC
END AS CODDOC,
A.CODUNICOCLI_F AS CODUNICOCLI,
A.NOMBRE_COMPLETO,
NVL(A.APELLIDO_PATERNO, '.') AS APELLIDO_PATERNO,
NVL(A.APELLIDO_MATERNO, '.') AS APELLIDO_MATERNO,
NVL(A.PRIMER_NOMBRE, '.') AS PRIMER_NOMBRE,
NVL(A.SEGUNDO_NOMBRE, '.') 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, '') || ' ' || NVL(A.APELLIDO_MATERNO, '') || ' ' || NVL(A.PRIMER_NOMBRE, '') || ' ' || NVL(A.SEGUNDO_NOMBRE, '')
) AS NOMBRE_COMPLETO,
CASE
WHEN NVL(A.TIPDOC, '') <> '2' THEN A.TIPDOC
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE '10%' THEN '1'
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('15%', '17%', '20%') THEN '.'
ELSE A.TIPDOC
END AS TIP_DOC,
CASE
WHEN NVL(A.TIPDOC, '') <> '2' THEN A.CODDOC
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE '10%' THEN SUBSTRING(A.CODDOC, 3, 8)
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('15%', '17%', '20%') THEN '.'
ELSE A.CODDOC
END AS NUM_DOC,
CASE
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('10%', '15%', '17%', '20%') THEN A.CODDOC
WHEN A.TIPDOC = '1' THEN COALESCE(B.NUMRUC, R.RUC15, '.')
ELSE '.'
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 ('10%', '15%', '17%', '20%')) THEN 2
ELSE A.TIPDOCTRIB
END,
-1
) AS VARCHAR(1)
) AS TIP_DOC_TRIB,
NULLIF(A.NUMDOCTRIB, '.') AS NUM_DOC_TRIB,
CASE
WHEN A.TIPPERSONA IN (1, 3, 4) THEN CASE
WHEN A.TIPDOC = '2'
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 ('10%', '15%', '17%', '20%')) THEN '2'
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, '"', ''), '.'))) AS VARCHAR(120)
) AS APELLIDO_PATERNO,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.APMATERNO, '"', ''), '.'))) AS VARCHAR(40)
) AS APELLIDO_MATERNO,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.PRIMERNBR, '"', ''), '.'))) AS VARCHAR(40)
) AS PRIMER_NOMBRE,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.SEGUNDONBR, '"', ''), '.'))) 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 = '1'
AND SUBSTRING(B.NUMRUC, 1, 2) = '10'
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 ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC IN ('VIGENTE','VENCIDO') AND PRODUCTO_RCC_DESC='FACTORING' then 1
WHEN TIPO_PRODUCTO_RCC_DESC IN ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC IN ('VIGENTE','VENCIDO') AND PRODUCTO_RCC_DESC= 'DESCUENTOS' THEN 1 ELSE 0 END) AS TARGET_SF
--max(CASE WHEN TIPO_PRODUCTO_RCC_DESC IN ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC='VIGENTE' AND NOMBREEMPRESAFINANC_DESC ='IBK' AND PRODUCTO_RCC_DESC='FACTORING' 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;
Revise this Paste