personas_gd_proc_ACSELE_neg.sql
unknown
sql
2 years ago
146 kB
4
Indexable
--VERSION FINAL-- DECLARE FECHA_REFERENCIA_1, FECHA_REFERENCIA_2, FECHA_REFERENCIA_3 DATE; DECLARE ID_REGLA, NOMBRE_nom_tabla, NOMBRE_nom_campo STRING; DECLARE FLAG_CLIENTE STRING; DECLARE FECHA_REFERENCIA DATE; DECLARE NOMBRE_nom_fuente STRING; DECLARE TIPO_periodicidad STRING; SET FECHA_REFERENCIA = DATE("{fecha_corte}"); SET NOMBRE_nom_fuente = "ACSELE"; SET TIPO_periodicidad = 'M'; SET FECHA_REFERENCIA_1 = date(DATETIME_ADD(FECHA_REFERENCIA, INTERVAL -1 MONTH)); SET FECHA_REFERENCIA_2 = date(DATETIME_ADD(FECHA_REFERENCIA, INTERVAL -2 MONTH)); SET FECHA_REFERENCIA_3 = date(DATETIME_ADD(FECHA_REFERENCIA, INTERVAL -3 MONTH)); ---------------------------------------------------------------- ---------------------------ACSEL E------------------------------ ---------------------------------------------------------------- DELETE FROM {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos WHERE nom_fuente = 'ACSELE' AND fec_periodo_corte = FECHA_REFERENCIA AND ind_cliente="{FLAG_CLIENTE}"; ---------------------------------------------------------------- --------------------------TELEFONO------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------RTEL001------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; SET FLAG_CLIENTE = "{FLAG_CLIENTE}"; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND TEL_DOM1 IN (SELECT DISTINCT CELULAR FROM (SELECT NUMIDPER,TEL_DOM1 AS CELULAR FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE TEL_DOM1 IS NOT NULL AND DATE(T.FECCREACION)< FECHA_REFERENCIA GROUP BY NUMIDPER,TEL_DOM1) GROUP BY CELULAR HAVING COUNT(*)>3)) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL002------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,'NO VIGENTE' des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDPER ,CAST(DATE(TERC.FECCREACION) AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND DATE_DIFF(FECHA_REFERENCIA,DATE(T.FECCREACION),YEAR)> 1) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL003------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND SAFE_CAST(TEL_DOM1 AS INT64)IS NULL) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL004------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND LENGTH(TRIM(TEL_DOM1)) <> 9) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL005------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND NOT(TRIM(SAFE_CAST(TEL_DOM1 AS STRING)) LIKE '9%')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL006------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NULL ) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL007------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND (TEL_DOM1 LIKE '%666666%' OR TEL_DOM1 LIKE '%777777%' OR TEL_DOM1 LIKE '%888888%' OR TEL_DOM1 LIKE '%999999%' OR TEL_DOM1 LIKE '%555555%' OR TEL_DOM1 LIKE '%444444%' OR TEL_DOM1 LIKE '%333333%' OR TEL_DOM1 LIKE '%222222%' OR TEL_DOM1 LIKE '%111111%' OR TEL_DOM1 LIKE '%000000%')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------RTEL008------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'RTEL008'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TEL_DOM1'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,'CELULAR' AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.CELULAR AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,TEL_DOM1 AS CELULAR,T.USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TEL_DOM1 IS NOT NULL AND TRIM(SAFE_CAST(TEL_DOM1 AS STRING)) IN ('989898989', '987654321', '99999888')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ----------------------CORREO ELECTRONICO------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------REML001------------------------------ -- ---------------------------------------------------------------- -- SET ID_REGLA = 'REML001'; -- SET NOMBRE_nom_tabla = 'INT_TERCERO'; -- SET NOMBRE_nom_campo = 'EMAIL'; -- INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos -- SELECT -- ID_REGLA AS id_regla -- ,FECHA_REFERENCIA AS fec_periodo_corte -- ,RES.fecha_de_creacion AS fec_creacion -- ,NOMBRE_nom_fuente AS nom_fuente -- ,NOMBRE_nom_tabla AS nom_tabla -- ,NOMBRE_nom_campo AS nom_campo -- ,FLAG_CLIENTE AS ind_cliente -- ,RES.nom_segmento AS nom_segmento -- ,RES.ind_mayor_edad AS ind_mayor_edad -- ,RES.USUARIO AS USUMODIF -- ,NULL AS nom_unidad_organizativa -- ,NULL AS codexterno -- ,CAST(RES.NUMIDPER AS STRING) AS id_numid -- ,NULL AS id_codigo_afiliado -- ,NULL AS id_numero_documento -- ,NULL AS nom_tipo_documento -- ,CURRENT_TIMESTAMP() AS fec_ejecucion -- ,RES.des_valor_fallido AS des_valor_fallido -- ,RES.des_detalle_complemento AS des_detalle_complemento -- ,'maestro_rebotes' AS des_valor_nom_campo_fallido_padron -- ,'email' AS des_valor_nom_campo_fallido_padron -- FROM ( -- SELECT -- CLIE.des_segmentacion_growth AS nom_segmento -- ,CLIE.ADULTO as ind_mayor_edad -- ,TERC.USUMODIF AS USUARIO -- ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion -- ,TERC.EMAIL AS des_detalle_complemento -- ,TERC.NUMIDPER -- FROM -- nom_tabla principal -- (SELECT NUMIDPER,EMAIL AS EMAIL,USUMODIF,T.FECCREACION -- FROM -- `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T -- where DATE(T.FECCREACION)< FECHA_REFERENCIA -- AND EMAIL IS NOT NULL -- AND TRIM(LOWER(EMAIL)) IN (SELECT TRIM(LOWER(email)) FROM `{project_id_raw}.de__contactabilidad.maestro_rebotes`)) as TERC -- INNER JOIN --cruce con clientes -- (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO -- from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` -- where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE -- ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) -- ) AS RES; -- INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos -- SELECT -- ID_REGLA AS id_regla -- ,FECHA_REFERENCIA AS fec_periodo_corte -- ,RES.fecha_de_creacion AS fec_creacion -- ,NOMBRE_nom_fuente AS nom_fuente -- ,NOMBRE_nom_tabla AS nom_tabla -- ,NOMBRE_nom_campo AS nom_campo -- ,FLAG_CLIENTE AS ind_cliente -- ,RES.nom_segmento AS nom_segmento -- ,RES.ind_mayor_edad AS ind_mayor_edad -- ,RES.USUARIO AS USUMODIF -- ,NULL AS nom_unidad_organizativa -- ,NULL AS codexterno -- ,CAST(RES.NUMIDPER AS STRING) AS id_numid -- ,NULL AS id_codigo_afiliado -- ,NULL AS id_numero_documento -- ,NULL AS nom_tipo_documento -- ,CURRENT_TIMESTAMP() AS fec_ejecucion -- ,RES.des_valor_fallido AS des_valor_fallido -- ,RES.des_detalle_complemento AS des_detalle_complemento -- ,'maestro_rebotes' AS des_valor_nom_campo_fallido_padron -- ,'email' AS des_valor_nom_campo_fallido_padron -- FROM ( -- SELECT -- CLIE.des_segmentacion_growth AS nom_segmento -- ,CLIE.ADULTO as ind_mayor_edad -- ,TERC.USUMODIF AS USUARIO -- ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion -- ,TERC.EMAIL AS des_detalle_complemento -- ,TERC.NUMIDPER -- FROM -- nom_tabla principal -- (SELECT NUMIDPER,EMAIL AS EMAIL,USUMODIF,T.FECCREACION -- FROM -- `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T -- where DATE(T.FECCREACION)< FECHA_REFERENCIA -- AND EMAIL IS NOT NULL -- AND TRIM(LOWER(EMAIL)) IN (SELECT TRIM(LOWER(email)) FROM `{project_id_raw}.de__contactabilidad.maestro_rebotes`)) as TERC -- INNER JOIN --cruce con clientes -- (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO -- from `{project_id_anl}.anl_persona.prospecto_persona` -- where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE -- ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) -- ) AS RES; -- ---------------------------------------------------------------- -- ---------------------------REML002------------------------------ -- ---------------------------------------------------------------- -- SET ID_REGLA = 'REML002'; -- SET NOMBRE_nom_tabla = 'INT_TERCERO'; -- SET NOMBRE_nom_campo = 'EMAIL'; -- INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos -- SELECT -- ID_REGLA AS id_regla -- ,FECHA_REFERENCIA AS fec_periodo_corte -- ,RES.fecha_de_creacion AS fec_creacion -- ,NOMBRE_nom_fuente AS nom_fuente -- ,NOMBRE_nom_tabla AS nom_tabla -- ,NOMBRE_nom_campo AS nom_campo -- ,FLAG_CLIENTE AS ind_cliente -- ,RES.nom_segmento AS nom_segmento -- ,RES.ind_mayor_edad AS ind_mayor_edad -- ,RES.USUARIO AS USUMODIF -- ,NULL AS nom_unidad_organizativa -- ,NULL AS codexterno -- ,CAST(RES.NUMIDPER AS STRING) AS id_numid -- ,NULL AS id_codigo_afiliado -- ,NULL AS id_numero_documento -- ,NULL AS nom_tipo_documento -- ,CURRENT_TIMESTAMP() AS fec_ejecucion -- ,RES.des_valor_fallido AS des_valor_fallido -- ,RES.des_detalle_complemento AS des_detalle_complemento -- ,'maestro_desuscritos' AS des_valor_nom_campo_fallido_padron -- ,'email' AS des_valor_nom_campo_fallido_padron -- FROM ( -- SELECT -- CLIE.des_segmentacion_growth AS nom_segmento -- ,CLIE.ADULTO as ind_mayor_edad -- ,TERC.USUMODIF AS USUARIO -- ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion -- ,TERC.EMAIL AS des_detalle_complemento -- ,TERC.NUMIDPER -- FROM -- nom_tabla principal -- (SELECT NUMIDPER,EMAIL AS EMAIL,USUMODIF,T.FECCREACION -- FROM -- `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T -- where DATE(T.FECCREACION)< FECHA_REFERENCIA -- AND EMAIL IS NOT NULL -- AND TRIM(LOWER(EMAIL)) IN (SELECT TRIM(LOWER(email)) FROM `{project_id_raw}.de__contactabilidad.maestro_desuscritos`)) as TERC -- INNER JOIN --cruce con clientes -- (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO -- from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` -- where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE -- ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) -- ) AS RES; -- INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos -- SELECT -- ID_REGLA AS id_regla -- ,FECHA_REFERENCIA AS fec_periodo_corte -- ,RES.fecha_de_creacion AS fec_creacion -- ,NOMBRE_nom_fuente AS nom_fuente -- ,NOMBRE_nom_tabla AS nom_tabla -- ,NOMBRE_nom_campo AS nom_campo -- ,FLAG_CLIENTE AS ind_cliente -- ,RES.nom_segmento AS nom_segmento -- ,RES.ind_mayor_edad AS ind_mayor_edad -- ,RES.USUARIO AS USUMODIF -- ,NULL AS nom_unidad_organizativa -- ,NULL AS codexterno -- ,CAST(RES.NUMIDPER AS STRING) AS id_numid -- ,NULL AS id_codigo_afiliado -- ,NULL AS id_numero_documento -- ,NULL AS nom_tipo_documento -- ,CURRENT_TIMESTAMP() AS fec_ejecucion -- ,RES.des_valor_fallido AS des_valor_fallido -- ,RES.des_detalle_complemento AS des_detalle_complemento -- ,'maestro_desuscritos' AS des_valor_nom_campo_fallido_padron -- ,'email' AS des_valor_nom_campo_fallido_padron -- FROM ( -- SELECT -- CLIE.des_segmentacion_growth AS nom_segmento -- ,CLIE.ADULTO as ind_mayor_edad -- ,TERC.USUMODIF AS USUARIO -- ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion -- ,TERC.NUMIDPER -- ,TERC.EMAIL AS des_detalle_complemento -- FROM -- nom_tabla principal -- (SELECT NUMIDPER,EMAIL AS EMAIL,USUMODIF,T.FECCREACION -- FROM -- `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T -- where DATE(T.FECCREACION)< FECHA_REFERENCIA -- AND EMAIL IS NOT NULL -- AND TRIM(LOWER(EMAIL)) IN (SELECT TRIM(LOWER(email)) FROM `{project_id_raw}.de__contactabilidad.maestro_desuscritos`)) as TERC -- INNER JOIN --cruce con clientes -- (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO -- from `{project_id_anl}.anl_persona.prospecto_persona` -- where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE -- ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) -- ) AS RES; ---------------------------------------------------------------- ---------------------------REML003------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'REML003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'EMAIL'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT NUMIDPER,EMAIL,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND EMAIL IS NOT NULL AND TRIM(LOWER(EMAIL)) IN (SELECT DISTINCT TRIM(LOWER(CORREO)) FROM (SELECT NUMIDPER,EMAIL AS CORREO FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE EMAIL IS NOT NULL GROUP BY NUMIDPER,EMAIL) GROUP BY CORREO HAVING COUNT(*)>3)) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------REML004------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'REML004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'EMAIL'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT NUMIDPER,EMAIL AS EMAIL,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TRIM(EMAIL) IS NULL) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------REML005------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'REML005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'EMAIL'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT NUMIDPER,EMAIL,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND EMAIL IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(EMAIL )), R'^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+\.(?:(?:[a-zA-Z0-9-]+\.)?[a-zA-Z0-9-]+\.?[a-zA-Z0-9-]+\.)?(co|com|edu|net|info|org|biz|ism|global|gov|gob|ibm|uca|world|legal|sabmille|ar|au|ef|bo|pe|eu|it|hk|es|fr|tw|ca|cl|ch|cn|de|cs|nl|nz|mx|md|uk|br|pr|pt|us|ru|ws){1,2}$')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------REML006------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'REML006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'EMAIL'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT NUMIDPER,EMAIL,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND (TRIM(UPPER(EMAIL)) like '%@HOTMAIL%'OR TRIM(UPPER(EMAIL)) LIKE '%@GMAIL%'OR TRIM(UPPER(EMAIL)) LIKE '%@YAHOO%'OR TRIM(UPPER(EMAIL)) LIKE '%@OUTLOOK%') AND EMAIL IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(EMAIL)),R'^[a-zA-Z0-9_.-]+@(?:(?:[a-zA-Z0-9-]+\.)?[a-zA-Z]+\.)?(gmail|hotmail|yahoo|outlook)+\.(?:(?:[a-zA-Z]+\.)?[a-zA-Z]+\.)?(co|com|ar|au|ef|bo|pe|eu|it|hk|es|fr|tw|ca|cl|ch|cn|de|cs|nl|nz|mx|md|uk|br|pr|pt|us|ru|ws){1,2}$')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- ---------------------------REML007------------------------------ ---------------------------------------------------------------- SET ID_REGLA = 'REML007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'EMAIL'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.EMAIL AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT NUMIDPER,EMAIL,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND EMAIL IS NOT NULL AND NOT(REGEXP_CONTAINS(TRIM(LOWER(EMAIL)), r'^[A-Za-z0-9._%+-]{1,64}@[A-Za-z0-9.-]{1,255}$') AND CHAR_LENGTH(TRIM(EMAIL))<321)) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------NOMBRE----------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RNOM001---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,NOMTER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND NOMTER IS NULL) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM002---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,NOMTER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND (LOWER(NOMTER) LIKE '%aaa%' OR LOWER(NOMTER) LIKE '%bbb%' OR LOWER(NOMTER) LIKE '%ccc%' OR LOWER(NOMTER) LIKE '%ddd%' OR LOWER(NOMTER) LIKE '%eee%' OR LOWER(NOMTER) LIKE '%fff%' OR LOWER(NOMTER) LIKE '%ggg%' OR LOWER(NOMTER) LIKE '%hhh%' OR LOWER(NOMTER) LIKE '%iii%' OR LOWER(NOMTER) LIKE '%jjj%' OR LOWER(NOMTER) LIKE '%kkk%' OR LOWER(NOMTER) LIKE '%lll%' OR LOWER(NOMTER) LIKE '%mmm%' OR LOWER(NOMTER) LIKE '%nnn%' OR LOWER(NOMTER) LIKE '%ñññ%' OR LOWER(NOMTER) LIKE '%ooo%' OR LOWER(NOMTER) LIKE '%ppp%' OR LOWER(NOMTER) LIKE '%qqq%' OR LOWER(NOMTER) LIKE '%rrr%' OR LOWER(NOMTER) LIKE '%sss%' OR LOWER(NOMTER) LIKE '%ttt%' OR LOWER(NOMTER) LIKE '%uuu%' OR LOWER(NOMTER) LIKE '%vvv%' OR LOWER(NOMTER) LIKE '%www%' OR LOWER(NOMTER) LIKE '%xxx%' OR LOWER(NOMTER) LIKE '%yyy%' OR LOWER(NOMTER) LIKE '%zzz%')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM003---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NOMBRE_nom_tabla AS des_valor_nom_campo_fallido_padron ,'APETER' AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.APETER AS des_detalle_complemento ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,NOMTER,APETER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND TRIM(LOWER(NOMTER)) = TRIM(LOWER(APETER))) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM004---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron ,'NOMBRES' AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,PADR.NOMBRES AS des_detalle_complemento ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,NOMTER,APETER,NUMIDEDOC,USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NOMTER IS NOT NULL) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) LEFT JOIN --cruce con padron (SELECT DNI,NOMBRES FROM `{project_id_raw}.ext__padron.persona_padron_electoral` WHERE PERIODO = (SELECT MAX(PERIODO) FROM `{project_id_raw}.ext__padron.persona_padron_electoral`)) AS PADR ON PADR.DNI = TERC.NUMIDEDOC WHERE TRIM(LOWER(TERC.NOMTER)) NOT LIKE TRIM(LOWER(PADR.NOMBRES)) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM005---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NOMTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NOMTER IS NOT NULL AND LENGTH(TRIM(NOMTER))=1) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM006---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NOMTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NOMTER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(T.NOMTER)),'^[a-zA-ZÁÉÍÓÚÜÑáéíóúñüÄËÏÖäëïö\' ]{2,254}$')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM007---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NOMTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NOMTER IS NOT NULL AND LENGTH(TRIM(NOMTER))>50) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RNOM008---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RNOM008'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NOMTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NOMTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NOMTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NOMTER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(T.NOMTER)),'[aeiouyáéíóúAEIOUYÁÉÍÓÚÄËÏÖäëïöüÜ ]')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------APELLIDO PAT.---------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RAPP001---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,APETER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND APETER IS NULL) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP002---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,APETER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND (LOWER(APETER) LIKE '%aaa%' OR LOWER(APETER) LIKE '%bbb%' OR LOWER(APETER) LIKE '%ccc%' OR LOWER(APETER) LIKE '%ddd%' OR LOWER(APETER) LIKE '%eee%' OR LOWER(APETER) LIKE '%fff%' OR LOWER(APETER) LIKE '%ggg%' OR LOWER(APETER) LIKE '%hhh%' OR LOWER(APETER) LIKE '%iii%' OR LOWER(APETER) LIKE '%jjj%' OR LOWER(APETER) LIKE '%kkk%' OR LOWER(APETER) LIKE '%lll%' OR LOWER(APETER) LIKE '%mmm%' OR LOWER(APETER) LIKE '%nnn%' OR LOWER(APETER) LIKE '%ñññ%' OR LOWER(APETER) LIKE '%ooo%' OR LOWER(APETER) LIKE '%ppp%' OR LOWER(APETER) LIKE '%qqq%' OR LOWER(APETER) LIKE '%rrr%' OR LOWER(APETER) LIKE '%sss%' OR LOWER(APETER) LIKE '%ttt%' OR LOWER(APETER) LIKE '%uuu%' OR LOWER(APETER) LIKE '%vvv%' OR LOWER(APETER) LIKE '%www%' OR LOWER(APETER) LIKE '%xxx%' OR LOWER(APETER) LIKE '%yyy%' OR LOWER(APETER) LIKE '%zzz%')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP003---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron ,'APELL_PAT' AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,PADR.APELL_PAT AS des_detalle_complemento ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.NUMIDEDOC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) LEFT JOIN --cruce con padron (SELECT DNI,APELL_PAT FROM `{project_id_raw}.ext__padron.persona_padron_electoral` WHERE PERIODO = (SELECT MAX(PERIODO) FROM `{project_id_raw}.ext__padron.persona_padron_electoral`)) AS PADR ON PADR.DNI = TERC.NUMIDEDOC WHERE TRIM(LOWER(TERC.APETER)) NOT LIKE TRIM(LOWER(PADR.APELL_PAT)) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP004---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(APETER)),'^[a-zA-ZÁÉÍÓÚÜÑáéíóúñüÄËÏÖäëïö\' ]{2,254}$')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP005---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL AND LENGTH(TRIM(APETER))=1) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP006---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(T.APETER)),'^[a-zA-ZÁÉÍÓÚÜÑáéíóúñüÄËÏÖäëïö\' ]{2,254}$')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP007---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL AND LENGTH(TRIM(APETER))>50) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPP008---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPP008'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APETER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APETER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APETER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APETER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(T.APETER)),'[aeiouyáéíóúAEIOUYÁÉÍÓÚÄËÏÖäëïöüÜ ]')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------APELLIDO MAT.---------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RAPM001---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM (SELECT CONCAT(TERC.NOMBRE,' ',TERC.APEPATERNO,' ',TERC.APEMATERNO,' ',SAFE_CAST(TERC.FECNACIMIENTO AS STRING)) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.USUMODIF AS USUARIO ,CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ind_mayor_edad ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER ,T.USUMODIF,T.FECCREACION ,COALESCE(UPPER(TRIM(T.NOMTER)),'VACIO') AS NOMBRE ,COALESCE(UPPER(TRIM(T.APETER)),'VACIO') AS APEPATERNO ,COALESCE(UPPER(TRIM(T.APEMATTER)),'VACIO') AS APEMATERNO ,COALESCE(DATE(T.FECNAC),DATE('1900-01-01')) AS FECNACIMIENTO FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` AS T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, coalesce(des_segmentacion_growth,'NULL') as des_segmentacion_growth, CASE WHEN DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END AS ind_mayor_edad FROM `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` WHERE DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) -- CLIENTE NO repetidos INNER JOIN (SELECT --TERC.NUMIDPER TERC_R.NOMBRE ,TERC_R.APEPATERNO ,TERC_R.APEMATERNO ,TERC_R.FECNACIMIENTO ,CLIE_R.des_segmentacion_growth AS nom_segmento ,CLIE_R.ind_mayor_edad FROM -- nom_tabla principal (SELECT T.NUMIDPER, COALESCE(UPPER(TRIM(T.NOMTER)),'VACIO') AS NOMBRE ,COALESCE(UPPER(TRIM(T.APETER)),'VACIO') AS APEPATERNO ,COALESCE(UPPER(TRIM(T.APEMATTER)),'VACIO') AS APEMATERNO ,COALESCE(DATE(T.FECNAC),DATE('1900-01-01')) AS FECNACIMIENTO FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` AS T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA) as TERC_R INNER JOIN --cruce con clientes (SELECT cod_acselx, coalesce(des_segmentacion_growth,'NULL') as des_segmentacion_growth, CASE WHEN DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END AS ind_mayor_edad FROM `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` WHERE DATE(PERIODO) = FECHA_REFERENCIA) as CLIE_R ON TERC_R.NUMIDPER = CAST(CLIE_R.cod_acselx AS INT64) GROUP BY TERC_R.NOMBRE ,TERC_R.APEPATERNO ,TERC_R.APEMATERNO ,TERC_R.FECNACIMIENTO, CLIE_R.des_segmentacion_growth, CLIE_R.ind_mayor_edad HAVING COUNT(*) <> 1) AS RES_OK ON TERC.NOMBRE = RES_OK.NOMBRE AND TERC.APEPATERNO = RES_OK.APEPATERNO AND TERC.APEMATERNO = RES_OK.APEMATERNO AND TERC.FECNACIMIENTO = RES_OK.FECNACIMIENTO AND CLIE.des_segmentacion_growth = RES_OK.nom_segmento AND CLIE.ind_mayor_edad = RES_OK.ind_mayor_edad) AS RES; ---------------------------------------------------------------- -----------------------------RAPM002---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (SELECT T.NUMIDPER,APEMATTER,USUMODIF,T.FECCREACION FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T where DATE(T.FECCREACION)< FECHA_REFERENCIA AND (LOWER(APEMATTER) LIKE '%aaa%' OR LOWER(APEMATTER) LIKE '%bbb%' OR LOWER(APEMATTER) LIKE '%ccc%' OR LOWER(APEMATTER) LIKE '%ddd%' OR LOWER(APEMATTER) LIKE '%eee%' OR LOWER(APEMATTER) LIKE '%fff%' OR LOWER(APEMATTER) LIKE '%ggg%' OR LOWER(APEMATTER) LIKE '%hhh%' OR LOWER(APEMATTER) LIKE '%iii%' OR LOWER(APEMATTER) LIKE '%jjj%' OR LOWER(APEMATTER) LIKE '%kkk%' OR LOWER(APEMATTER) LIKE '%lll%' OR LOWER(APEMATTER) LIKE '%mmm%' OR LOWER(APEMATTER) LIKE '%nnn%' OR LOWER(APEMATTER) LIKE '%ñññ%' OR LOWER(APEMATTER) LIKE '%ooo%' OR LOWER(APEMATTER) LIKE '%ppp%' OR LOWER(APEMATTER) LIKE '%qqq%' OR LOWER(APEMATTER) LIKE '%rrr%' OR LOWER(APEMATTER) LIKE '%sss%' OR LOWER(APEMATTER) LIKE '%ttt%' OR LOWER(APEMATTER) LIKE '%uuu%' OR LOWER(APEMATTER) LIKE '%vvv%' OR LOWER(APEMATTER) LIKE '%www%' OR LOWER(APEMATTER) LIKE '%xxx%' OR LOWER(APEMATTER) LIKE '%yyy%' OR LOWER(APEMATTER) LIKE '%zzz%')) as TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPM003---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron ,'APELL_MAT' AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,PADR.APELL_MAT AS des_detalle_complemento ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APEMATTER,T.NUMIDEDOC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APEMATTER IS NOT NULL) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) LEFT JOIN --cruce con padron (SELECT DNI,APELL_MAT FROM `{project_id_raw}.ext__padron.persona_padron_electoral` WHERE PERIODO = (SELECT MAX(PERIODO) FROM `{project_id_raw}.ext__padron.persona_padron_electoral`)) AS PADR ON PADR.DNI = TERC.NUMIDEDOC WHERE TRIM(LOWER(TERC.APEMATTER)) NOT LIKE TRIM(LOWER(PADR.APELL_MAT)) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPM004---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APEMATTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APEMATTER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(APEMATTER)),'^[a-zA-ZÁÉÍÓÚÜÑáéíóúñüÄËÏÖäëïö\' ]{2,254}$')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPM005---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APEMATTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APEMATTER IS NOT NULL AND LENGTH(TRIM(APEMATTER))<=1) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPM006---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APEMATTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APEMATTER IS NOT NULL AND NOT REGEXP_CONTAINS(TRIM(LOWER(APEMATTER)),'^[a-zA-ZÁÉÍÓÚÜÑáéíóúñüÄËÏÖäëïö\' ]{2,254}$')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RAPM007---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RAPM007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'APEMATTER'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.APEMATTER AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,APEMATTER,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.APEMATTER IS NOT NULL AND LENGTH(TRIM(CAST(APEMATTER AS STRING)))>50) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ----------------------FECHA NACIMIENTO-------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RFNA001---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,T.FECNAC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NULL ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RFNA002---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,DATE(T.FECNAC) AS FECNAC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NOT NULL AND DATE(FECNAC)<'1901-01-01') AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RFNA003---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,DATE(T.FECNAC) AS FECNAC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NOT NULL AND DATE(FECNAC)>FECHA_REFERENCIA) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RFNA004---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron ,'fec_nacimiento' AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,CAST(PADR.fec_nacimiento AS STRING) AS des_detalle_complemento ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,DATE(T.FECNAC) AS FECNAC,T.USUMODIF,T.FECCREACION,T.NUMIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NOT NULL ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) LEFT JOIN --cruce con padron electoral (SELECT DNI, fec_nacimiento FROM `{project_id_raw}.ext__padron.persona_padron_electoral` WHERE PERIODO = (SELECT MAX(PERIODO) FROM `{project_id_raw}.ext__padron.persona_padron_electoral`) AND fec_nacimiento is not null) AS PADR ON TERC.NUMIDEDOC = PADR.DNI WHERE DATE(TERC.FECNAC) <> DATE(PADR.FEC_NACIMIENTO) ) AS RES; ---------------------------------------------------------------- -----------------------------RFNA005---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,DATE(T.FECNAC) AS FECNAC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NOT NULL AND DATE_DIFF(FECHA_REFERENCIA,DATE(FECNAC), YEAR)>=110) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RFNA006---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RFNA006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'FECNAC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,CAST(TERC.FECNAC AS STRING) AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,DATE(T.FECNAC) AS FECNAC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.FECNAC IS NOT NULL AND SAFE_CAST(FECNAC AS DATE) IS NULL) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- --------------------------TIPO DOC.----------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RDOC001---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC001'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TIPOIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.TIPOIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,T.USUMODIF,T.FECCREACION,T.NUMIDEDOC,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.TIPOIDEDOC IS NULL ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC002---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC002'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TIPOIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.NUMIDPER ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,TIPOIDEDOC,NUMIDEDOC,T.USUMODIF,T.FECCREACION from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.TIPOIDEDOC IS NOT NULL AND T.NUMIDPER IN (SELECT P.NUMIDPER FROM `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as P GROUP BY P.NUMIDPER,P.TIPOIDEDOC,P.NUMIDEDOC HAVING COUNT(*)<>1)) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC003---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC003'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'TIPOIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.NUMIDPER ,TERC.USUMODIF AS USUARIO ,TERC.TIPOIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,TIPOIDEDOC,T.USUMODIF,T.FECCREACION,T.NUMIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.TIPOIDEDOC IS NOT NULL AND TRIM(CAST(TIPOIDEDOC AS STRING)) NOT IN ('1','4','6','2')) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- --------------------------NRO DOC.------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -----------------------------RDOC004---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC004'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,T.USUMODIF,T.FECCREACION,T.NUMIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NULL ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC005---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC005'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='2' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]+$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC006---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC006'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='1' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]+$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC007---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC007'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='1' AND NOT ((TRIM(CAST(NUMIDEDOC AS STRING)) LIKE '10%' OR TRIM(CAST(NUMIDEDOC AS STRING)) LIKE '15%' OR TRIM(CAST(NUMIDEDOC AS STRING)) LIKE '17%' OR TRIM(CAST(NUMIDEDOC AS STRING)) LIKE '20%')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC008---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC008'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='4' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]+$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC009---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC009'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='4' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]{9,12}$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC010---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC010'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='6' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[a-z,A-Z,0-9]+$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC011---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC011'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,NULL AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='6' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[a-z,A-Z,0-9]{5,12}$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC014---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC014'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='1' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]{11}$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES; ---------------------------------------------------------------- -----------------------------RDOC015---------------------------- ---------------------------------------------------------------- SET ID_REGLA = 'RDOC015'; SET NOMBRE_nom_tabla = 'INT_TERCERO'; SET NOMBRE_nom_campo = 'NUMIDEDOC'; INSERT INTO {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos SELECT ID_REGLA AS id_regla ,FECHA_REFERENCIA AS fec_periodo_corte ,RES.fecha_de_creacion AS fec_creacion ,NOMBRE_nom_fuente AS nom_fuente ,NOMBRE_nom_tabla AS nom_tabla ,NOMBRE_nom_campo AS nom_campo ,FLAG_CLIENTE AS ind_cliente ,RES.nom_segmento AS nom_segmento ,RES.ind_mayor_edad AS ind_mayor_edad ,RES.USUARIO AS USUMODIF ,NULL AS nom_unidad_organizativa ,CAST(RES.NUMIDPER AS STRING) AS id_numid ,NULL AS nom_canal ,NULL AS nom_subcanal ,NULL AS nom_agrupador ,NULL AS id_numero_documento ,NULL AS nom_tipo_documento ,CURRENT_TIMESTAMP() AS fec_ejecucion ,RES.des_valor_fallido AS des_valor_fallido ,RES.des_detalle_complemento AS des_detalle_complemento ,NULL AS des_valor_nom_campo_fallido_padron ,NULL AS des_valor_nom_campo_fallido_padron FROM ( SELECT CLIE.des_segmentacion_growth AS nom_segmento ,CLIE.ADULTO as ind_mayor_edad ,TERC.USUMODIF AS USUARIO ,TERC.NUMIDEDOC AS des_valor_fallido,DATE(TERC.FECCREACION) AS fecha_de_creacion ,TERC.NUMIDPER ,CASE TERC.TIPOIDEDOC WHEN '0' THEN 'NO DETERMINADO' WHEN '1' THEN 'RUC' WHEN '2' THEN 'DNI' WHEN '3' THEN 'NIT' WHEN '4' THEN 'C.E' WHEN '5' THEN 'CARNET DE IDENTIDAD' WHEN '6' THEN 'PASAPORTE' WHEN '7' THEN 'LIBRETA MILITAR' WHEN '8' THEN 'DPI' WHEN '9' THEN 'CODIGO INTERNO' WHEN '10' THEN 'IDENT. MENOR DE EDAD' WHEN '11' THEN 'IDENT. EMP. EXT.' WHEN '99' THEN 'VARIOS' END AS des_detalle_complemento FROM -- nom_tabla principal (select T.NUMIDPER,NUMIDEDOC,T.USUMODIF,T.FECCREACION,T.TIPOIDEDOC from `{project_id_raw}.bdvida__app_vida.INT_TERCERO` as T WHERE DATE(T.FECCREACION)< FECHA_REFERENCIA AND T.NUMIDEDOC IS NOT NULL AND TRIM(CAST(T.TIPOIDEDOC AS STRING)) ='2' AND NOT (REGEXP_CONTAINS(TRIM(CAST(T.NUMIDEDOC AS STRING)), r'^[0-9]{8}$')) ) AS TERC INNER JOIN --cruce con clientes (SELECT cod_acselx, COALESCE(des_segmentacion_growth, 'NULL') AS des_segmentacion_growth,ind_fallecido, case when DATE_DIFF(FECHA_REFERENCIA,DATE(fec_nacimiento),YEAR) >= 18 THEN "SI" ELSE "NO" END as ADULTO from `{project_id_anl}.anl_persona.{TABLA_TIPO_PERSONA}` where DATE(PERIODO) = FECHA_REFERENCIA ) as CLIE ON TERC.NUMIDPER = CAST(CLIE.cod_acselx AS INT64) ) AS RES;
Editor is loading...