personas_gd_proc_ACSELE_neg.sql
unknown
sql
2 years ago
146 kB
5
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...