Untitled

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('2022-11-01');
SET NOMBRE_nom_fuente = "RIMAC SALUD";
SET TIPO_periodicidad = 'M';

--DELETE FROM {project_id_trsv}.trsv_procesos_calidad.dash_personas_gob_detalle_registros_negativos
--WHERE nom_fuente = NOMBRE_nom_fuente AND fec_periodo_corte = FECHA_REFERENCIA AND ind_cliente="{FLAG_CLIENTE}";

----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------CLIENTES-----------------------------------------------
----------------------------------------------------------------------------------------------------------------
SET FLAG_CLIENTE = "TRUE";

--------------------------------------------------------------------------------------------
-----------------------------------NOMBRE---------------------------------------------------
--------------------------------------------------------------------------------------------

----------------------------------------------------------------
-----------------------------RNOM004----------------------------
----------------------------------------------------------------
SET ID_REGLA  = 'RNOM004';
SET NOMBRE_nom_tabla = 'AFILIADO';
SET NOMBRE_nom_campo = 'PRIMER_NOMBRE/SEGUNDO_NOMBRE';

--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 nom_usuario_modificacion
  ,NULL AS nom_unidad_organizativa
  ,CAST(RES.COD_AFILIADO AS STRING) AS COD_AFILIADO
  ,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
  ,SAFE_CAST(RES.des_valor_fallido AS STRING) AS des_valor_fallido
  ,RES.des_detalle_complemento
  ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron
  ,'NOMBRES' AS des_valor_nom_campo_fallido_padron

	FROM (
	  SELECT
	  AFIL.COD_AFILIADO, DATE(FEC_CREACION) AS fecha_de_creacion
	  ,CLIE.des_segmentacion_growth AS nom_segmento
	  ,CLIE.ind_mayor_edad 
	  ,AFIL.COD_USUARIO_UPDATE AS USUARIO
	  ,AFIL.des_valor_fallido
	  ,AFIL.des_detalle_complemento
	   FROM -- nom_tabla principal
				  (SELECT T.FEC_CREACION,T.COD_AFILIADO,T.PRIMER_NOMBRE AS des_valor_fallido, PADR.NOMBRES AS des_detalle_complemento, T.NRO_DOCUMENTO, T.COD_USUARIO_UPDATE
					FROM 
						`rs-prd-dlk-dd-rawzone-a40f.bdrsa__app_eps.AFILIADO` AS T
					LEFT JOIN --cruce con padron
						(SELECT DNI,NOMBRES FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`
						WHERE PERIODO = (SELECT MAX(PERIODO) FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`)) AS PADR
					ON PADR.DNI = T.NRO_DOCUMENTO
						WHERE DATE(T.FEC_CREACION)< FECHA_REFERENCIA
						
						AND T.PRIMER_NOMBRE IS NOT NULL
						and concat(upper(trim(T.PRIMER_NOMBRE)),' ', upper(trim(T.SEGUNDO_NOMBRE)) ) <> upper(trim(PADR.NOMBRES))
						) AS AFIL
	  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 `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona`
				  WHERE DATE(PERIODO) = FECHA_REFERENCIA) as CLIE
		ON AFIL.COD_AFILIADO = CAST(CLIE.cod_acselx AS INT64) ) AS RES;


--------------------------------------------------------------------------------------------
-----------------------------------APELLIDO MAT.--------------------------------------------
--------------------------------------------------------------------------------------------

----------------------------------------------------------------
-----------------------------RAPM003----------------------------
----------------------------------------------------------------
SET ID_REGLA  = 'RAPM003';
SET NOMBRE_nom_tabla = 'AFILIADO';
SET NOMBRE_nom_campo = 'APELLIDO_MATERNO';

--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 nom_usuario_modificacion
  ,NULL AS nom_unidad_organizativa
  ,CAST(RES.COD_AFILIADO AS STRING) AS COD_AFILIADO
  ,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
  ,SAFE_CAST(RES.des_valor_fallido AS STRING) AS des_valor_fallido
  ,RES.des_detalle_complemento
  ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron
  ,'APELL_MAT' AS des_valor_nom_campo_fallido_padron

	FROM (
	  SELECT
	  AFIL.COD_AFILIADO, DATE(FEC_CREACION) AS fecha_de_creacion
	  ,CLIE.des_segmentacion_growth AS nom_segmento
	  ,CLIE.ind_mayor_edad 
	  ,AFIL.COD_USUARIO_UPDATE AS USUARIO
	  ,AFIL.des_valor_fallido
	  ,AFIL.des_detalle_complemento
	   FROM -- nom_tabla principal
				  (SELECT T.FEC_CREACION,T.COD_AFILIADO,T.APELLIDO_MATERNO AS des_valor_fallido, T.COD_USUARIO_UPDATE, PADR.APELL_MAT AS des_detalle_complemento
					FROM `rs-prd-dlk-dd-rawzone-a40f.bdrsa__app_eps.AFILIADO` AS T
					LEFT JOIN --cruce con padron
					 (SELECT DNI,APELL_MAT FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`
					  WHERE PERIODO = (SELECT MAX(PERIODO) FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`)) AS PADR
					ON PADR.DNI = T.NRO_DOCUMENTO
					WHERE DATE(T.FEC_CREACION)< FECHA_REFERENCIA
					
					AND T.APELLIDO_MATERNO IS NOT NULL
					AND UPPER(TRIM(T.APELLIDO_MATERNO)) <> UPPER(TRIM(PADR.APELL_MAT))
					) AS AFIL
	  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 `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona`
				  WHERE DATE(PERIODO) = FECHA_REFERENCIA) as CLIE
		ON AFIL.COD_AFILIADO = CAST(CLIE.cod_acselx AS INT64) ) AS RES;


--------------------------------------------------------------------------------------------
-----------------------------------APELLIDO PAT.--------------------------------------------
--------------------------------------------------------------------------------------------

----------------------------------------------------------------
-----------------------------RAPP003----------------------------
----------------------------------------------------------------
SET ID_REGLA  = 'RAPP003';
SET NOMBRE_nom_tabla = 'AFILIADO';
SET NOMBRE_nom_campo = 'APELLIDO_PATERNO';


--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 nom_usuario_modificacion
  ,NULL AS nom_unidad_organizativa
  ,CAST(RES.COD_AFILIADO AS STRING) AS COD_AFILIADO
  ,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
  ,SAFE_CAST(RES.des_valor_fallido AS STRING) AS des_valor_fallido
  ,RES.des_detalle_complemento
  ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron
  ,'APELL_PAT' AS des_valor_nom_campo_fallido_padron

	FROM (
	  SELECT
	  AFIL.COD_AFILIADO, DATE(FEC_CREACION) AS fecha_de_creacion
	  ,CLIE.des_segmentacion_growth AS nom_segmento
	  ,CLIE.ind_mayor_edad 
	  ,AFIL.COD_USUARIO_UPDATE AS USUARIO
	  ,AFIL.des_valor_fallido
	  ,AFIL.des_detalle_complemento
	   FROM -- nom_tabla principal
				  (SELECT T.FEC_CREACION,T.COD_AFILIADO,T.APELLIDO_PATERNO AS des_valor_fallido, T.COD_USUARIO_UPDATE, PADR.APELL_PAT AS des_detalle_complemento
					FROM `rs-prd-dlk-dd-rawzone-a40f.bdrsa__app_eps.AFILIADO` AS T
					LEFT JOIN --cruce con padron
					 (SELECT DNI,APELL_PAT FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`
					  WHERE PERIODO = (SELECT MAX(PERIODO) FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`)) AS PADR
					ON PADR.DNI = T.NRO_DOCUMENTO
						WHERE DATE(T.FEC_CREACION)< FECHA_REFERENCIA
						
						AND T.APELLIDO_PATERNO IS NOT NULL
						AND UPPER(TRIM(T.APELLIDO_PATERNO)) <> UPPER(TRIM(PADR.APELL_PAT))
						) AS AFIL
	  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 `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona`
				  WHERE DATE(PERIODO) = FECHA_REFERENCIA) as CLIE
		ON AFIL.COD_AFILIADO = CAST(CLIE.cod_acselx AS INT64) ) AS RES;


--------------------------------------------------------------------------------------------
-----------------------------------FECHA_NACIMIENTO-----------------------------------------
--------------------------------------------------------------------------------------------

----------------------------------------------------------------
-----------------------------RFNA004----------------------------
----------------------------------------------------------------
SET ID_REGLA  = 'RFNA004';
SET NOMBRE_nom_tabla = 'AFILIADO';
SET NOMBRE_nom_campo = 'FEC_NACIMIENTO';

--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 nom_usuario_modificacion
  ,NULL AS nom_unidad_organizativa
  ,CAST(RES.COD_AFILIADO AS STRING) AS COD_AFILIADO
  ,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
  ,SAFE_CAST(RES.des_valor_fallido AS STRING) AS des_valor_fallido
  ,RES.des_detalle_complemento
  ,'persona_padron_electoral' AS des_valor_nom_campo_fallido_padron
  ,'fec_nacimiento' AS des_valor_nom_campo_fallido_padron

	FROM (
	  SELECT
	  AFIL.COD_AFILIADO, DATE(FEC_CREACION) AS fecha_de_creacion
	  ,CLIE.des_segmentacion_growth AS nom_segmento
	  ,CLIE.ind_mayor_edad 
	  ,AFIL.COD_USUARIO_UPDATE AS USUARIO
	  ,AFIL.des_valor_fallido
	  ,AFIL.des_detalle_complemento
	   FROM -- nom_tabla principal
				  (SELECT T.FEC_CREACION,T.COD_AFILIADO, T.NRO_DOCUMENTO, CAST(T.FEC_NACIMIENTO AS STRING) AS des_valor_fallido, T.COD_USUARIO_UPDATE
					,CAST(safe_cast(PADR.fec_nacimiento as timestamp) AS STRING) AS des_detalle_complemento
						FROM `rs-prd-dlk-dd-rawzone-a40f.bdrsa__app_eps.AFILIADO` AS T 
						
						LEFT JOIN --cruce con padron
						 (SELECT DNI,fec_nacimiento FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`
						  WHERE PERIODO = (SELECT MAX(PERIODO) FROM `rs-prd-dlk-dd-rawzone-a40f.ext__padron.persona_padron_electoral`)) AS PADR
						ON PADR.DNI = T.NRO_DOCUMENTO
						WHERE DATE(T.FEC_CREACION)< FECHA_REFERENCIA
						AND T.FEC_NACIMIENTO IS NOT NULL
						AND DATE(T.FEC_NACIMIENTO) <>  DATE(safe_cast(PADR.fec_nacimiento as timestamp))
						) AS AFIL
	  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 `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona`
				  WHERE DATE(PERIODO) = FECHA_REFERENCIA) as CLIE
		ON AFIL.COD_AFILIADO = CAST(CLIE.cod_acselx AS INT64) ) AS RES;