Skip to main content

Reporte Conciliación Clientes y Artículos (Oracle vs AS400)

1. Visión General del Proceso​

El reporte de conciliación implementado en Databricks tiene como objetivo identificar, clasificar y auditar discrepancias de datos maestros entre los catálogos de Oracle Cloud ERP (módulos AR, CDM e INV) y el sistema transaccional IBM AS400 (tablas MCTEN y MPRO replicadas en la capa Silver del Data Lake).

El pipeline realiza la extracción, normalización, cruces lógicos mediante PySpark, consolidación de métricas de negocio, compresión de reportes de discrepancias en memoria RAM (formato ZIP/Base64) y notificación automática a través del API de mensajería corporativa.


2. Estructura de los Componentes y Notebooks​

El proceso se encuentra desacoplado en dos capas principales:

1. Comparativa de Catalogos Clientes vs AS400 (Notebook Orquestador y Persistencia)​

  • Invoca las utilerías base (%run ./Utilities y %run ./UtilitiesCatAs400).
  • Define y valida las tablas de destino y auditoría en PostgreSQL (admin_catalogos, tablas de log de ejecución).
  • Controla esquemas relacionales, tipos de datos, creación de Primary Keys e inserción de métricas de auditoría.
  • Detona la función principal de ejecución: main_comparacion_clientes("gold").

Main Comparacion Clientes

2. UtilitiesCatAs400 (Notebook de Lógica de Negocio y Motor Spark)​

  • Contiene la función maestra main_comparacion_clientes(source).
  • Implementa la conciliación interna y externa de clientes (compara_clientes).
  • Implementa la conciliación de artículos de alta velocidad (compara_articulos).
  • Obtiene Token de autentificacion para api de notificacion (obtener_access_token).
  • Gestiona el motor de compresión y codificación binaria (df_to_base64_zip).
  • Gestiona la seguridad OAuth2 con Keycloak y el consumo de la API de notificaciones (enviar_correo_consolidado_api).
  • Obtiene configuraciones alojadas en ctrl_cnfig (obtiene_ctrl_config)

UtilitiesCatAs400​

3. Detalle Paso a Paso del Proceso de Comparación​

Paso 1: Configuración Inicial y Recuperación de Metadatos​

La función main_comparacion_clientes(source) arranca estableciendo la marca de tiempo inicial y consultando la configuración dinámica de tablas mediante get_catalogs(...):

  • Catálogos locales Oracle (Spark Gold): alp_cat_ar_clientes_v, alp_cat_cdm_clientes_v, alp_cat_inv_items_v.
  • Catálogos fuente AS400 (Spark Silver): as4_mcten (clientes) y as4_mpro (artículos).

Se realiza una validación condicional que garantiza la disponibilidad de las rutas físicas/esquemas (database_name, schema_name, catalog_name) antes de proceder.


Paso 2: Conciliación del Catálogo de Clientes (compara_clientes)​

La lógica de clientes aborda tanto la disparidad entre módulos internos de Oracle como el desalineamiento respecto a AS400.

2.1. Normalización y Limpieza de Atributos Clave​

Para permitir comparaciones deterministas entre sistemas heterogéneos, se aplican transformaciones sobre los campos clave:

  • no_cliente (cuenta_clean): Se aplica trim() y conversión a string.
  • no_sucursal (sucursal_clean): Se normaliza con relleno de ceros a la izquierda a longitud fija de 3 caracteres mediante lpad(trim(col), 3, '0').
  • Llave Unificada (key_link): Generada mediante concat_ws('||', no_cliente, no_sucursal). Esta llave compuesta asegura unicidad a nivel sucursal.

2.2. Conciliación Interna Oracle Cloud (AR vs. CDM)​

En Oracle Cloud conviven registros de clientes en el módulo financiero (AR - Accounts Receivable) y en el módulo de gestión (CDM - Customer Data Management):

  1. Se realiza un Full Outer Join entre AR y CDM utilizando como condición de enlace: party_id AND no_cliente AND no_sucursal
  2. Se clasifica cada registro resultante con el campo estado_oracle:
    • COINCIDENTE: Presente simultáneamente en AR y CDM con misma llave.
    • AR: Registro que existe únicamente en AR.
    • CDM: Registro que existe únicamente en CDM.
  3. Se unifican los datos descriptivos (cliente, rfc, deposito_cedis) utilizando la función coalesce(ar_col, cdm_col) para dar prioridad a AR cuando exista coincidencia.
  4. Se aíslan las discrepancias internas de Oracle (registros cuyo estado es únicamente AR o CDM) para incluirlas en el reporte de auditoría.
  5. Se genera el Maestro Consolidado de Oracle (df_maestro_spark) y se conserva en caché (StorageLevel.MEMORY_AND_DISK).

2.3. Preparación del Universo AS400 (as4_mcten) y Regla de Bajas​

  1. Lectura de la réplica cat_master_alpura.sch_silver_layer.as4_mcten.
  2. Extracción de llave key_link usando numcte y numsuc (con lpad a 3 posiciones).
  3. Construcción del RFC completo concatenando las tres secciones de AS400: rfc = coalesce(rfcc_ti, '') + coalesce(rfcc_tf, '') + coalesce(rfcc_th, '')
  4. Regla de Negocio para Bajas: Se detecta el estado operativo mediante el campo fecbaj: es_baja = (fecbaj != null) AND (fecbaj != 0)
  5. Segmentación de AS400:
    • df_as400_total_keys: Llaves únicas globales para validación de existencia general.
    • df_as400_activos: Subconjunto donde es_baja == False.
    • df_as400_bajas: Subconjunto de registros dados de baja (origen_discrepancia = 'Baja AS400').

2.4. Cruces Lógicos (Left-Anti Joins)​

  1. Faltan en AS400: df_maestro_spark con left_anti contra df_as400_total_keys. Si un cliente está dado de baja en AS400, no se considera faltante.
  2. Faltan en Oracle: df_as400_activos con left_anti contra df_maestro_spark. Solo se exigen en Oracle los clientes activos de AS400.
  3. Consolidado final: Unión por nombre de todas las diferencias (no_en_as400, no_en_spark, bajas_as400 y discrepancias internas AR/CDM).

Paso 3: Conciliación del Catálogo de Artículos (compara_articulos)​

3.1. Filtros de Negocio en Oracle​

  1. Filtro estricto: clave_anterior.isNotNull(), org_code == 'OMA' y item_class == 'PTE' (Productos Terminados).
  2. Llave de cruce: key_link = trim(clave_anterior.cast("string")).

3.2. Extracción de AS400 (as4_mpro)​

  1. Filtro cvepro.isNotNull().
  2. Llave de cruce: key_link = trim(cvepro.cast("string")).

3.3. Optimización con Broadcast Left-Anti Join​

Dado el tamaño controlado de ambos catálogos (menor a 10,000 registros), se envían las llaves como variables de difusión para evitar el shuffle en la red de Spark:

keys_as400 = F.broadcast(df_as400.select("key_link"))
keys_spark = F.broadcast(df_items_spark.select("key_link"))

# Faltan en AS400
no_en_as400 = df_items_spark.join(keys_as400, on="key_link", how="left_anti")

# Faltan en Oracle
no_en_spark = df_as400.join(keys_spark, on="key_link", how="left_anti")

Paso 4: Generación y Compresión In-Memory (df_to_base64_zip)​

  1. Conversión de DataFrames a Pandas (.toPandas()) y exportación a bytes CSV en codificación UTF-8.
  2. Generación del archivo .zip en memoria RAM utilizando io.BytesIO() y zipfile.ZipFile con compression=zipfile.ZIP_DEFLATED y compresslevel=9 (máxima compresión).
  3. Codificación del buffer a cadena Base64 para su inclusión en el payload JSON.

Paso 5: Autenticación y Notificación por Correo​

5.1. Regla de Umbral de Tamaño (menor a 5 MB)​

Para evitar el rechazo por parte del gateway API (HTTP 413 / HTTP 400):

tam_bytes = (len(base64_str) * 3) / 4

  • Si supera los 5,242,880 bytes (5.00 MB): Se retira el archivo adjunto del payload y se incluye un aviso en el cuerpo del correo: <span style={{color: 'orange'}}>Excedió límite de 5MB - Consultar en Databricks</span>.
  • Si no lo supera: Se adjunta como Discrepancias_*.zip.

5.2. Autenticación y Disparo API​

  • Token Server (Keycloak): https://hub-login.alpura.com/auth/realms/Alpura/protocol/openid-connect/token (OAuth2 Password Grant).
  • Gateway de Notificaciones: https://apw-alpura.com:8243/rcn/v1/api/v1/notification/message consumiendo el id de la plantilla corporativa alp_rep_conciliacion_databricks que se configura en la tabla template del schema Notifications.

5.3. Templeate para envío de notificación​

{
"message": {
"body": {
"content": "<html><head><style>body{font-family:Arial,sans-serif;color:#333333;}.header{background-color:#002f6c;color:white;padding:15px;text-align:center;}.container{padding:20px;}.card{background-color:#f8f9fa;border-left:5px solid #002f6c;padding:15px;margin-bottom:20px;}.card-art{border-left-color:#4a148c;}table{width:100%;border-collapse:collapse;margin-top:10px;}th,td{border:1px solid #ddd;padding:8px;text-align:left;}th{background-color:#f2f2f2;}</style></head><body><div class=\"header\"><h2>Reporte Integral de Conciliación: Clientes y Artículos (Oracle vs AS400)</h2></div><div class=\"container\"><p>Estimado equipo,</p><p>Se ha completado el análisis de conciliación entre Oracle y AS400. Si se detectaron discrepancias en los catálogos, los detalles se encontrarán <strong>adjuntos a este correo</strong>.</p><div class=\"card\"><h3>👥 Catálogo de Clientes (Oracle vs AS400)</h3><table><tr><th>Métrica</th><th>Valor</th></tr><tr><td>Total Clientes en Oracle (Maestro Consolidado)</td><td><strong>{{total_clientes_oracle}}</strong></td></tr><tr><td>├─ Clientes leídos en módulo AR</td><td>{{total_clientes_ar}}</td></tr><tr><td>├─ Clientes leídos en módulo CDM</td><td>{{total_clientes_cdm}}</td></tr><tr><td>├─ Coincidentes en AR y CDM (Sincronizados)</td><td>{{clientes_coincidentes_ar_cdm}}</td></tr><tr><td>├─ Discrepancia: Solo existen en AR (Sin CDM)</td><td style=\"color:#d32f2f;\">{{clientes_solo_en_ar}}</td></tr><tr><td>└─ Discrepancia: Solo existen en CDM (Sin AR)</td><td style=\"color:#d32f2f;\">{{clientes_solo_en_cdm}}</td></tr><tr><td>Total Clientes en AS400 (Universo Total)</td><td><strong>{{total_clientes_as400_universo}}</strong></td></tr><tr><td>├─ Clientes Activos en AS400</td><td>{{total_clientes_as400}}</td></tr><tr><td>└─ Clientes Dados de Baja en AS400</td><td style=\"color:#d32f2f;\">{{total_clientes_as400_bajas}}</td></tr><tr><td>Faltantes en AS400 (Existen en Oracle - Excluye Bajas)</td><td style=\"color:red;\"><strong>{{faltantes_clientes_as400}}</strong></td></tr><tr><td>Faltantes en Oracle (Existen en AS400 Activos)</td><td style=\"color:red;\"><strong>{{faltantes_clientes_oracle}}</strong></td></tr><tr><td>Estado del Reporte</td><td>{{estado_reporte_clientes}}</td></tr></table></div><div class=\"card card-art\"><h3 style=\"color:#4a148c;margin-top:0;\">📦 Catálogo de Artículos (Oracle vs AS400)</h3><table><tr><th>Métrica</th><th>Valor</th></tr><tr><td>Total Artículos en Oracle</td><td>{{total_articulos_oracle}}</td></tr><tr><td>Total Artículos en AS400</td><td>{{total_articulos_as400}}</td></tr><tr><td>Faltantes en AS400 (Existen en Oracle)</td><td style=\"color:red;\"><strong>{{faltantes_articulos_as400}}</strong></td></tr><tr><td>Faltantes en Oracle (Existen en AS400)</td><td style=\"color:red;\"><strong>{{faltantes_articulos_oracle}}</strong></td></tr><tr><td>Estado del Reporte</td><td>{{estado_reporte_articulos}}</td></tr></table></div><hr><p style=\"font-size:11px;color:#777;\">Reporte generado automáticamente desde Databricks.</p></div></body></html>",
"contentType": "HTML"
},
"subject": "Reporte Integral de Conciliación: Clientes y Artículos (Oracle vs AS400)"
},
"saveToSentItems": true
}

🗄️ Configuración de Plantillas en la Base de Datos (PostgreSQL)​

Para que el sistema de notificaciones de Databricks sea dinámico, las plantillas de correo no se guardan en el código, sino que se administran desde la tabla centralizada notifications.template en nuestra base de datos PostgreSQL.

Esto permite modificar el diseño visual o el asunto del correo sin necesidad de volver a desplegar código en el pipeline.


📥 Registro de la Plantilla de Conciliación (INSERT)​

Para dar de alta la plantilla del Reporte Integral de Conciliación en la base de datos, se debe ejecutar la siguiente sentencia INSERT.

Nota: El payload JSON ha sido debidamente adaptado para cumplir con la sintaxis de escape requerida por PostgreSQL dentro de un bloque de texto.

INSERT INTO notifications."template" (
"name",
"type",
json_body,
dn_activo,
dn_usuario_creador
) VALUES (
'alp_rep_conciliacion_databricks',
'EMAIL',
'{"message": {"body": {"content": "<html><head><style>body{font-family:Arial,sans-serif;color:#333333;}.header{background-color:#002f6c;color:white;padding:15px;text-align:center;}.container{padding:20px;}.card{background-color:#f8f9fa;border-left:5px solid #002f6c;padding:15px;margin-bottom:20px;}.card-art{border-left-color:#4a148c;}table{width:100%;border-collapse:collapse;margin-top:10px;}th,td{border:1px solid #ddd;padding:8px;text-align:left;}th{background-color:#f2f2f2;}</style></head><body><div class=\"header\"><h2>Reporte Integral de Conciliación: Clientes y Artículos (Oracle vs AS400)</h2></div><div class=\"container\"><p>Estimado equipo,</p><p>Se ha completado el análisis de conciliación entre Oracle y AS400. Si se detectaron discrepancias en los catálogos, los detalles se encontrarán <strong>adjuntos a este correo</strong>.</p><div class=\"card\"><h3 style=\"color:#002f6c;margin-top:0;\">👥 Catálogo de Clientes (Oracle vs AS400)</h3><table><tr><th>Métrica</th><th>Valor</th></tr><tr><td>Total Clientes en Oracle (Maestro Consolidado)</td><td><strong>{{total_clientes_oracle}}</strong></td></tr><tr><td>├─ Clientes leídos en módulo AR</td><td>{{total_clientes_ar}}</td></tr><tr><td>├─ Clientes leídos en módulo CDM</td><td>{{total_clientes_cdm}}</td></tr><tr><td>├─ Coincidentes en AR y CDM (Sincronizados)</td><td>{{clientes_coincidentes_ar_cdm}}</td></tr><tr><td>├─ Discrepancia: Solo existen en AR (Sin CDM)</td><td style=\"color:#d32f2f;\">{{clientes_solo_en_ar}}</td></tr><tr><td>└─ Discrepancia: Solo existen en CDM (Sin AR)</td><td style=\"color:#d32f2f;\">{{clientes_solo_en_cdm}}</td></tr><tr><td>Total Clientes en AS400 (Universo Total)</td><td><strong>{{total_clientes_as400_universo}}</strong></td></tr><tr><td>├─ Clientes Activos en AS400</td><td>{{total_clientes_as400}}</td></tr><tr><td>└─ Clientes Dados de Baja en AS400</td><td style=\"color:#d32f2f;\">{{total_clientes_as400_bajas}}</td></tr><tr><td>Faltantes en AS400 (Existen en Oracle - Excluye Bajas)</td><td style=\"color:red;\"><strong>{{faltantes_clientes_as400}}</strong></td></tr><tr><td>Faltantes en Oracle (Existen en AS400 Activos)</td><td style=\"color:red;\"><strong>{{faltantes_clientes_oracle}}</strong></td></tr><tr><td>Estado del Reporte</td><td>{{estado_reporte_clientes}}</td></tr></table></div><div class=\"card card-art\"><h3 style=\"color:#4a148c;margin-top:0;\">📦 Catálogo de Artículos (Oracle vs AS400)</h3><table><tr><th>Métrica</th><th>Valor</th></tr><tr><td>Total Artículos en Oracle</td><td>{{total_articulos_oracle}}</td></tr><tr><td>Total Artículos en AS400</td><td>{{total_articulos_as400}}</td></tr><tr><td>Faltantes en AS400 (Existen en Oracle)</td><td style=\"color:red;\"><strong>{{faltantes_articulos_as400}}</strong></td></tr><tr><td>Faltantes en Oracle (Existen en AS400)</td><td style=\"color:red;\"><strong>{{faltantes_articulos_oracle}}</strong></td></tr><tr><td>Estado del Reporte</td><td>{{estado_reporte_articulos}}</td></tr></table></div><hr><p style=\"font-size:11px;color:#777;\">Reporte generado automáticamente desde Databricks.</p></div></body></html>", "contentType": "HTML"}, "subject": "Reporte Integral de Conciliación: Clientes y Artículos (Oracle vs AS400)"}, "saveToSentItems": true}'::jsonb,
true,
'1'
);

4. Gestión de Correos de destinatarios​

Los correos se obtienen de forma dinámica desde la tabla de control en Databricks:

  • Tabla de control: cat_master_aplicaciones.catalogos.ctrl_config
  • Criterio de búsqueda: Filtro por proceso alp_rep_conciliacion_databricks y clave destinatarios.

⚙️ Administración de Cambios​

Para agregar, eliminar o modificar los correos de los responsables que reciben esta notificación, únicamente se debe actualizar el campo valor en el registro correspondiente de la tabla de configuraciones (ingresando los correos separados por comas).

El pipeline de Databricks leerá y procesará los nuevos destinatarios de manera automática en su siguiente ejecución, sin necesidad de modificar ni volver a publicar cambios en el código del notebook.

# --- Ejemplo de uso en el módulo de notificaciones ---
proceso_id = "alp_rep_conciliacion_databricks"
clave_param = "destinatarios"

# 1. Recuperar string de destinatarios
destinatarios_raw = obtiene_ctrl_config(proceso_id, clave_param)

# 2. Parsear a lista limpia (quitando espacios en blanco accidentales)
lista_destinatarios = [email.strip() for email in destinatarios_raw.split(",") if email.strip()]

Control configuraciones​

5. Orquestación y Automatización (Databricks Workflows)​

El proceso se ejecuta de forma desatendida mediante un Job programado en Databricks Workflows, gestionado mediante databricks-sdk:

⏱️ Programación y Triggering​

El Job se encuentra configurado para ejecutarse de manera periódica para asegurar que los catálogos estén sincronizados antes del inicio de la operación diaria.

  • Frecuencia: Ejecución diaria de lunes a viernes a las 09:00 AM (Hora de la CDMX).
  • Expresión Cron: 0 0 9 ? * MON-FRI *
  • Timeout de Seguridad: Configurado a 45 minutos para evitar colgados infinitos en caso de latencia de red con la réplica de AS400 o el API Gateway.

🎛️ Configuración de la Tarea en Databricks Workflows​

La tarea de orquestación para la conciliación de catálogos maestros de Alpura está configurada dentro de la plataforma de Databricks bajo los siguientes parámetros:

Detalles de la Tarea (Task Details)​

ParámetroValor ConfiguradoDescripción
Task nameComparacion_catalogosNombre identificador de la tarea dentro del Workflow.
TypeNotebookTipo de recurso de cómputo a ejecutar.
SourceWorkspaceOrigen del archivo en el Workspace de Databricks.
Path/Shared/Desarrollo/Catalogos_Maestros/Comparativa de Catalogos Clientes vs AS400Ruta absoluta al Notebook orquestador.
ClusterMultitask_Shared_CatalogosCluster compartido asignado para la ejecución.

Parámetros de Entrada (Parameters)​

Clave (Key)Valor (Value)Descripción
sourcegoldDefine que la extracción se realizará desde las vistas de la capa Gold de Oracle Cloud.

Job Reporte Conciliacion Config


6. Matriz de Clasificación de Discrepancias​

Esta matriz define el catálogo, la etiqueta de discrepancia asignada, la regla lógica aplicada en Spark y el impacto directo que representa para la operación de Alpura:

CatálogoOrigen o EtiquetaCondición Técnica (Regla Spark)Causa e Impacto en Negocio
ClientesFalta en AS400Llave en Maestro Oracle pero ausente en todo AS400.Cliente creado en Oracle Cloud no sincronizado hacia AS400.
ClientesFalta en OracleLlave en clientes activos de AS400 (fecbaj == 0) pero ausente en Oracle.Cliente activo en AS400 pendiente de migración a Cloud.
ClientesBaja AS400Registro en AS400 con fecbaj > 0.Registro desactivado en AS400; se valida para evitar operaciones erróneas.
ClientesAR (Interno)Presente en AR y no en CDM.Cliente con cuenta por cobrar contable sin perfil CDM completo.
ClientesCDM (Interno)Presente en CDM y no en AR.Prospecto o cuenta en CDM sin configuración de cuenta AR activa.
ArtículosFalta en AS400Artículo Oracle (OMA + PTE) sin coincidencia en cvepro de AS400.Producto nuevo en Oracle no dado de alta en sistema transaccional legacy.
ArtículosFalta en OracleCódigo cvepro de AS400 no asignado como clave_anterior en Oracle.Artículo activo en AS400 pendiente de homologación en Cloud.

7. Métricas de Ejecución Típica (Ejemplo)​

Los siguientes paneles reflejan los resultados reales arrojados por el sistema de conciliación en el correo automático de notificación, detallando la volumetría por catálogo, el desglose de inconsistencias y el control de adjuntos.


📊 Resultados Generales de la Comparación​

Ejemplo correo pt 1 Ejemplo correo pt 2