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 ./Utilitiesy%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").

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)
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) yas4_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 aplicatrim()y conversión astring.no_sucursal(sucursal_clean): Se normaliza con relleno de ceros a la izquierda a longitud fija de 3 caracteres mediantelpad(trim(col), 3, '0').- Llave Unificada (
key_link): Generada medianteconcat_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):
- Se realiza un Full Outer Join entre AR y CDM utilizando como condición de enlace:
party_id AND no_cliente AND no_sucursal - 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.
- Se unifican los datos descriptivos (
cliente,rfc,deposito_cedis) utilizando la funcióncoalesce(ar_col, cdm_col)para dar prioridad a AR cuando exista coincidencia. - Se aíslan las discrepancias internas de Oracle (registros cuyo estado es únicamente
ARoCDM) para incluirlas en el reporte de auditoría. - 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
- Lectura de la réplica
cat_master_alpura.sch_silver_layer.as4_mcten. - Extracción de llave
key_linkusandonumcteynumsuc(conlpada 3 posiciones). - Construcción del RFC completo concatenando las tres secciones de AS400:
rfc = coalesce(rfcc_ti, '') + coalesce(rfcc_tf, '') + coalesce(rfcc_th, '') - Regla de Negocio para Bajas: Se detecta el estado operativo mediante el campo
fecbaj:es_baja = (fecbaj != null) AND (fecbaj != 0) - Segmentación de AS400:
df_as400_total_keys: Llaves únicas globales para validación de existencia general.df_as400_activos: Subconjunto dondees_baja == False.df_as400_bajas: Subconjunto de registros dados de baja (origen_discrepancia = 'Baja AS400').
2.4. Cruces Lógicos (Left-Anti Joins)
- Faltan en AS400:
df_maestro_sparkconleft_anticontradf_as400_total_keys. Si un cliente está dado de baja en AS400, no se considera faltante. - Faltan en Oracle:
df_as400_activosconleft_anticontradf_maestro_spark. Solo se exigen en Oracle los clientes activos de AS400. - Consolidado final: Unión por nombre de todas las diferencias (
no_en_as400,no_en_spark,bajas_as400y discrepancias internas AR/CDM).
Paso 3: Conciliación del Catálogo de Artículos (compara_articulos)
3.1. Filtros de Negocio en Oracle
- Filtro estricto:
clave_anterior.isNotNull(),org_code == 'OMA'yitem_class == 'PTE'(Productos Terminados). - Llave de cruce:
key_link = trim(clave_anterior.cast("string")).
3.2. Extracción de AS400 (as4_mpro)
- Filtro
cvepro.isNotNull(). - 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)
- Conversión de DataFrames a Pandas (
.toPandas()) y exportación a bytes CSV en codificaciónUTF-8. - Generación del archivo
.zipen memoria RAM utilizandoio.BytesIO()yzipfile.ZipFileconcompression=zipfile.ZIP_DEFLATEDycompresslevel=9(máxima compresión). - Codificación del buffer a cadena
Base64para 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/messageconsumiendo el id de la plantilla corporativaalp_rep_conciliacion_databricksque se configura en la tablatemplatedel schemaNotifications.
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_databricksy clavedestinatarios.
⚙️ 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()]
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ámetro | Valor Configurado | Descripción |
|---|---|---|
| Task name | Comparacion_catalogos | Nombre identificador de la tarea dentro del Workflow. |
| Type | Notebook | Tipo de recurso de cómputo a ejecutar. |
| Source | Workspace | Origen del archivo en el Workspace de Databricks. |
| Path | /Shared/Desarrollo/Catalogos_Maestros/Comparativa de Catalogos Clientes vs AS400 | Ruta absoluta al Notebook orquestador. |
| Cluster | Multitask_Shared_Catalogos | Cluster compartido asignado para la ejecución. |
Parámetros de Entrada (Parameters)
| Clave (Key) | Valor (Value) | Descripción |
|---|---|---|
source | gold | Define que la extracción se realizará desde las vistas de la capa Gold de Oracle Cloud. |

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álogo | Origen o Etiqueta | Condición Técnica (Regla Spark) | Causa e Impacto en Negocio |
|---|---|---|---|
| Clientes | Falta en AS400 | Llave en Maestro Oracle pero ausente en todo AS400. | Cliente creado en Oracle Cloud no sincronizado hacia AS400. |
| Clientes | Falta en Oracle | Llave en clientes activos de AS400 (fecbaj == 0) pero ausente en Oracle. | Cliente activo en AS400 pendiente de migración a Cloud. |
| Clientes | Baja AS400 | Registro en AS400 con fecbaj > 0. | Registro desactivado en AS400; se valida para evitar operaciones erróneas. |
| Clientes | AR (Interno) | Presente en AR y no en CDM. | Cliente con cuenta por cobrar contable sin perfil CDM completo. |
| Clientes | CDM (Interno) | Presente en CDM y no en AR. | Prospecto o cuenta en CDM sin configuración de cuenta AR activa. |
| Artículos | Falta en AS400 | Artículo Oracle (OMA + PTE) sin coincidencia en cvepro de AS400. | Producto nuevo en Oracle no dado de alta en sistema transaccional legacy. |
| Artículos | Falta en Oracle | Có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
