Sincronización de catálogos generales hacia Alpura One
Este documento describe la arquitectura, estructura de datos y el modelo de sincronización para los catálogos operativos del sistema Alpura 9 en la base de datos Omnicanal, partiendo desde los catálogos generales replicados de Oracle Cloud.
1. Objetivo
Implementar un mecanismo de sincronización entre los catálogos generales almacenados en Omnicanal, dentro del esquema general_catalogs, y las tablas de catálogos utilizadas por Alpura 9, ubicadas en el esquema alpura_one.
La sincronización permite mantener actualizada la información de los catálogos de Alpura 9 a partir de los datos centralizados en general_catalogs, evitando duplicar procesos de extracción y transformación que ya se encuentran implementados en la arquitectura de réplica de Oracle Cloud hacia Databricks y Omnicanal.
El mecanismo utiliza triggers de PostgreSQL para propagar los cambios en los catálogos de origen hacia sus respectivas tablas destino.
Adicionalmente, se cuenta con un proceso programado mediante pg_cron para actualizar la vigencia de los valores de los catálogos Flexfields, considerando sus fechas de inicio y fin de vigencia.
2. Arquitectura de sincronización
El flujo inicia a partir de la información previamente cargada en general_catalogs.
Desde este esquema se ejecutan tres mecanismos de sincronización independientes, responsables de mantener actualizados los catálogos correspondientes en alpura_one.
Los procesos contemplados son:
| Proceso | Esquema origen | Esquema destino | Descripción |
|---|---|---|---|
| Sincronización de almacenes | general_catalogs | alpura_one | Sincroniza los almacenes habilitados para Alpura One. |
| Sincronización de artículos | general_catalogs | alpura_one | Sincroniza la información de los artículos y sus atributos. |
| Sincronización de Flexfields | general_catalogs | alpura_one | Sincroniza los valores de los catálogos configurados mediante Flexfields. |
Cada proceso utiliza mecanismos de PostgreSQL para propagar los cambios hacia las tablas destino correspondientes.
3. Diagrama de Flujo
4. Sincronizaciones
4.1. Almacenes
Origen: general_catalogs.alp_cat_inv_almacenes
Destino: alpura_one.alp_one_cat_almacenes
Este proceso permite sincronizar la información de los almacenes disponibles en el catálogo general hacia la estructura utilizada por Alpura One.
Consideraciones de funcionamiento:
- Se identifican los almacenes que tienen habilitado el indicador
manufacture_flag = 'Y'. - Se insertan los nuevos registros que cumplen con la condición establecida.
- Se actualizan los registros existentes cuando se presentan cambios en la información.
- Cuando un almacén deja de cumplir con la condición de manufactura, se actualiza su indicador correspondiente en el catálogo destino a
N, sin eliminar físicamente el registro. - Se conserva la identificación del almacén mediante
organization_id, correspondiente al campoorg_iden la tabla destino.
La sincronización se implementa mediante la función: general_catalogs.fn_sync_alp_one_cat_almacenes()
4.1.1. Objetos Almacenes
- alp_one_cat_almacenes
- Función
- Trigger
| Campo | Tipo de dato | Descripción |
|---|---|---|
legal_entity_id | numeric(18) | Identificador de la entidad legal a la que pertenece el almacén. |
business_unit_id | numeric(18) | Identificador de la unidad de negocio asociada al almacén. |
org_id | PK / numeric(18) | Identificador único de la organización o almacén. Se utiliza como clave única del registro. |
legal_entity | text | Nombre de la entidad legal asociada. |
legal_entity_identifier | text | Identificador o código de la entidad legal. |
business_unit | text | Nombre de la unidad de negocio. |
org_code | text | Código de identificación del almacén. |
org_name | text | Nombre descriptivo del almacén. |
dn_activo | bool | Indicador que determina si el almacén se encuentra activo para su utilización en Alpura One. |
dn_usuario_creador | varchar(200) | Usuario responsable de la creación del registro. |
dd_fecha_creacion | timestamptz | Fecha y hora de creación del registro. |
dn_usuario_modificador | varchar(200) | Usuario responsable de la última modificación del registro. |
dd_fecha_modificacion | timestamptz | Fecha y hora de la última modificación del registro. |
CREATE OR REPLACE FUNCTION general_catalogs.fn_sync_alp_one_cat_almacenes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.manufacture_flag = 'Y' THEN
INSERT INTO alpura_one.alp_one_cat_almacenes (
legal_entity_id,
business_unit_id,
org_id,
legal_entity,
legal_entity_identifier,
business_unit,
org_code,
org_name,
dn_activo,
dn_usuario_creador,
dd_fecha_creacion
)
VALUES (
NEW.legal_entity_id,
NEW.business_unit_id,
NEW.organization_id,
NEW.legal_entity,
NEW.legal_entity_identifier,
NEW.business_unit,
NEW.organization_code,
NEW.organization_name,
TRUE,
'Oracle Cloud',
CURRENT_TIMESTAMP
)
ON CONFLICT (org_id)
DO UPDATE SET
legal_entity_id = EXCLUDED.legal_entity_id,
business_unit_id = EXCLUDED.business_unit_id,
legal_entity = EXCLUDED.legal_entity,
legal_entity_identifier = EXCLUDED.legal_entity_identifier,
business_unit = EXCLUDED.business_unit,
org_code = EXCLUDED.org_code,
org_name = EXCLUDED.org_name,
dn_activo = TRUE,
dn_usuario_modificador = 'Oracle Cloud',
dd_fecha_modificacion = CURRENT_TIMESTAMP;
ELSE
UPDATE alpura_one.alp_one_cat_almacenes
SET
dn_activo = FALSE,
dn_usuario_modificador = 'Oracle Cloud',
dd_fecha_modificacion = CURRENT_TIMESTAMP
WHERE org_id = NEW.organization_id;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_sync_alp_one_cat_almacenes
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_inv_almacenes
FOR EACH ROW
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_almacenes();
4.2. Artículos
Origen: general_catalogs.alp_cat_inv_items.
Destino: alpura_one.alp_one_cat_articulos
Este proceso permite mantener disponible en Alpura 9 la información general de los artículos, incluyendo sus atributos y unidades de conversión.
Dentro de la transformación de datos se consideran los campos de cantidades y equivalencias que deben manejarse como valores numéricos enteros.
Los atributos de cantidades que requieren conversión contemplan:
cantidad_estandarcajas_por_camacajas_por_tarimacamas_por_tarimapiezas_por_cajapiezas_por_tarima
Los valores que no representan cantidades numéricas válidas se normalizan a 0, evitando errores de conversión durante la sincronización.
4.2.1. Objetos Almacenes
- alp_one_cat_articulos
- Función
- Trigger
| Campo | Tipo de dato | Descripción |
|---|---|---|
item_id | PK / numeric(18) | Identificador único del artículo en el sistema origen. |
org_id | PK / numeric(18) | Identificador de la organización a la que pertenece el artículo. |
item_code | text | Código del artículo. |
item_code_400 | text | Código alternativo del artículo de 400 caracteres o formato relacionado. |
item | text | Descripción o nombre del artículo. |
tipo_de_producto | text | Tipo o clasificación del producto. |
vida_de_anaquel | numeric(10) | Vida de anaquel del producto. |
area | text | Área asociada al artículo. |
primary_uom_code | text | Código de la unidad de medida principal. |
primary_uom | text | Descripción de la unidad de medida principal. |
cantidad_estandar | int4 | Cantidad estándar definida para el artículo. |
cajas_por_cama | int4 | Número de cajas por cama de tarima. |
cajas_por_tarima | int4 | Número de cajas por tarima. |
camas_por_tarima | int4 | Número de camas por tarima. |
piezas_por_caja | int4 | Número de piezas contenidas en una caja. |
piezas_por_tarima | int4 | Número de piezas contenidas en una tarima. |
tipo_de_tarima | text | Tipo de tarima utilizado para el artículo. |
sku | text | SKU del artículo. |
sku_caja | text | SKU correspondiente a la presentación de caja. |
sku_tarima | text | SKU correspondiente a la presentación de tarima. |
sku_charola | text | SKU correspondiente a la presentación de charola. |
manufactura_cost | float8 | Costo de manufactura del artículo. |
manufactura_uom_code | text | Código de la unidad de medida asociada al costo de manufactura. |
manufactura_currency | text | Moneda en la que se expresa el costo de manufactura. |
nombre_estructura | text | Nombre de la estructura asociada al artículo. |
nombre_proceso | text | Nombre del proceso de manufactura asociado al artículo. |
dn_activo | bool | Indica si el registro se encuentra activo. |
dn_usuario_creador | varchar(200) | Usuario que creó el registro. |
dd_fecha_creacion | timestamptz | Fecha y hora de creación del registro. Por defecto utiliza CURRENT_TIMESTAMP. |
dn_usuario_modificador | varchar(200) | Usuario que realizó la última modificación. |
dd_fecha_modificacion | timestamptz | Fecha y hora de la última modificación. |
CREATE OR REPLACE FUNCTION general_catalogs.fn_sync_alp_one_cat_articulos()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.item_class = 'PTE'
AND EXISTS (
SELECT 1
FROM general_catalogs.alp_cat_inv_almacenes acia
WHERE acia.organization_id = NEW.org_id
AND acia.manufacture_flag = 'Y'
)
THEN
INSERT INTO alpura_one.alp_one_cat_articulos (
item_id,
org_id,
item_code,
item_code_400,
item,
tipo_de_producto,
vida_de_anaquel,
area,
primary_uom_code,
primary_uom,
cantidad_estandar,
cajas_por_cama,
cajas_por_tarima,
camas_por_tarima,
piezas_por_caja,
piezas_por_tarima,
tipo_de_tarima,
sku,
sku_caja,
sku_tarima,
sku_charola,
manufactura_cost,
manufactura_uom_code,
manufactura_currency,
nombre_estructura,
nombre_proceso,
dn_activo,
dn_usuario_creador,
dd_fecha_creacion
)
VALUES (
NEW.item_id,
NEW.org_id,
NEW.item,
NEW.clave_anterior,
NEW.description,
NEW.tipo_de_producto,
NEW.vida_de_anaquel,
NEW.area_impresion_etiqueta,
NEW.primary_uom_code,
NEW.primary_uom,
-- Conversiones a INTEGER
CASE
WHEN NEW.cantidad_estandar::TEXT ~ '^[0-9]+$'
THEN NEW.cantidad_estandar::INTEGER
ELSE 0
END,
CASE
WHEN NEW.cajas_por_cama::TEXT ~ '^[0-9]+$'
THEN NEW.cajas_por_cama::INTEGER
ELSE 0
END,
CASE
WHEN NEW.cajas_por_tarima::TEXT ~ '^[0-9]+$'
THEN NEW.cajas_por_tarima::INTEGER
ELSE 0
END,
CASE
WHEN NEW.camas_por_tarima::TEXT ~ '^[0-9]+$'
THEN NEW.camas_por_tarima::INTEGER
ELSE 0
END,
CASE
WHEN NEW.piezas_por_caja::TEXT ~ '^[0-9]+$'
THEN NEW.piezas_por_caja::INTEGER
ELSE 0
END,
CASE
WHEN NEW.piezas_por_tarima::TEXT ~ '^[0-9]+$'
THEN NEW.piezas_por_tarima::INTEGER
ELSE 0
END,
NEW.tipo_de_tarima,
NEW.sku,
NEW.sku_caja,
NEW.sku_tarima,
NEW.sku_charola,
NEW.manufactura_cost,
NEW.manufactura_uom_code,
NEW.manufactura_currency,
NEW.nombre_estructura,
NEW.nombre_proceso,
CASE
WHEN NEW.status = 'Activo' THEN TRUE
ELSE FALSE
END,
'Oracle Cloud',
CURRENT_TIMESTAMP
)
ON CONFLICT (item_id, org_id)
DO UPDATE SET
item_code = EXCLUDED.item_code,
item_code_400 = EXCLUDED.item_code_400,
item = EXCLUDED.item,
tipo_de_producto = EXCLUDED.tipo_de_producto,
vida_de_anaquel = EXCLUDED.vida_de_anaquel,
area = EXCLUDED.area,
primary_uom_code = EXCLUDED.primary_uom_code,
primary_uom = EXCLUDED.primary_uom,
cantidad_estandar = EXCLUDED.cantidad_estandar,
cajas_por_cama = EXCLUDED.cajas_por_cama,
cajas_por_tarima = EXCLUDED.cajas_por_tarima,
camas_por_tarima = EXCLUDED.camas_por_tarima,
piezas_por_caja = EXCLUDED.piezas_por_caja,
piezas_por_tarima = EXCLUDED.piezas_por_tarima,
tipo_de_tarima = EXCLUDED.tipo_de_tarima,
sku = EXCLUDED.sku,
sku_caja = EXCLUDED.sku_caja,
sku_tarima = EXCLUDED.sku_tarima,
sku_charola = EXCLUDED.sku_charola,
manufactura_cost = EXCLUDED.manufactura_cost,
manufactura_uom_code = EXCLUDED.manufactura_uom_code,
manufactura_currency = EXCLUDED.manufactura_currency,
nombre_estructura = EXCLUDED.nombre_estructura,
nombre_proceso = EXCLUDED.nombre_proceso,
dn_activo = EXCLUDED.dn_activo,
dn_usuario_modificador = 'Oracle Cloud',
dd_fecha_modificacion = CURRENT_TIMESTAMP;
ELSE
UPDATE alpura_one.alp_one_cat_articulos
SET
dn_activo = FALSE,
dn_usuario_modificador = 'Oracle Cloud',
dd_fecha_modificacion = CURRENT_TIMESTAMP
WHERE item_id = NEW.item_id
AND org_id = NEW.org_id;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_sync_alp_one_cat_articulos
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_inv_items
FOR EACH ROW
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_articulos();
4.3. Sincronización de catálogos Flexfields
Origen: general_catalogs.alp_cat_fnd_flex_fields
Destino: Esquema alpura_one, mediante las tablas correspondientes a cada catálogo.
Este proceso permite integrar los valores configurados en Oracle Fusion mediante Flexfields hacia las estructuras de catálogos utilizadas por Alpura One.
La sincronización utiliza una función parametrizada que identifica el catálogo destino y realiza la operación correspondiente.
Función de sincronización: general_catalogs.fn_sync_alp_one_cat_flexs()
La función utiliza el parámetro TG_ARGV[0] para identificar la tabla destino y realizar la sincronización de los registros.
4.3.1. Mapeo de catálogos
| Flexfield de origen | Tabla destino |
|---|---|
ALP_AREAS | alp_one_cat_areas |
ALP_MFG_DECISION | alp_one_cat_mfg_decisiones |
ALP_MFG_ORIGEN | alp_one_cat_mfg_origenes |
ALP_ORIGEN | alp_one_cat_origenes |
Nombre compuesto de equipos | alp_one_cat_equipos |
Tipo de orden | alp_one_cat_tipo_orden |
4.3.2. Transformación de información
Durante la sincronización se realiza el mapeo de los atributos del catálogo general hacia la estructura de Alpura One:
| Campo origen | Campo destino | Descripción |
|---|---|---|
flex_value_id | id | Identificador del valor del catálogo. |
value | codigo | Código del valor del catálogo. |
description | descripcion | Descripción del valor. |
enabled_flag y fechas de vigencia | dn_activo | Indicador de actividad del registro. |
El indicador dn_activo se determina considerando que el valor esté habilitado y que la fecha actual se encuentre dentro de su periodo de vigencia.
La sincronización contempla operaciones de inserción y actualización mediante la llave de identificación id. No se realiza la eliminación física de registros del catálogo destino.
4.3.3. Objetos Flex Fields
- alp_one_cat_articulos
- Función
- Triggers
- FlexField
ALP_AREAS
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identifica el área dentro del catálogo. |
descripcion | text | Descripción o nombre del área. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
- FlexField
ALP_MFG_DECISION
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identifica de los MFG decisiones. |
descripcion | text | Descripción o nombre del MFG decisiones. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
- FlexField
ALP_MFG_ORIGEN
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identifica de los MFG origenes. |
descripcion | text | Descripción o nombre de los MFG origenes. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
- FlexField
ALP_ORIGEN
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identifica de los origenes. |
descripcion | text | Descripción o nombre de los origenes. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
- FlexField
Nombre compuesto de equipos
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identifica los nombres compuesto de equipos. |
descripcion | text | Descripción del nombre compuesto de los equipos. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
- FlexField
Tipo de orden
| Campo | Tipo de dato | Descripción |
|---|---|---|
id | PK / int8 | Llave primaria del catálogo. |
codigo | text | Código que identificación de los Tipos de Datos. |
descripcion | text | Descripción o nombre de los Tipos de Datos. |
dn_activo | bool | Indica si el área se encuentra activa (TRUE) o inactiva (FALSE). |
CREATE OR REPLACE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_tabla_destino TEXT := TG_ARGV[0];
BEGIN
EXECUTE FORMAT(
'INSERT INTO alpura_one.%I
(id, codigo, descripcion, dn_activo)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id)
DO UPDATE SET
codigo = EXCLUDED.codigo,
descripcion = EXCLUDED.descripcion,
dn_activo = EXCLUDED.dn_activo',
v_tabla_destino
)
USING
NEW.flex_value_id,
NEW.value,
NEW.description,
(
NEW.enabled_flag = 'Y'
AND CURRENT_DATE BETWEEN
COALESCE(NEW.start_date, CURRENT_DATE)
AND COALESCE(NEW.end_date, CURRENT_DATE)
);
RETURN NEW;
END;
$$;
- FlexField
ALP_AREAS
CREATE TRIGGER trg_sync_alp_one_cat_areas
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'ALP_AREAS')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_areas'
);
- FlexField
ALP_MFG_DECISION
CREATE TRIGGER trg_sync_alp_one_cat_mfg_decisiones
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'ALP_MFG_DECISION')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_mfg_decisiones'
);
- FlexField
ALP_MFG_ORIGEN
CREATE TRIGGER trg_sync_alp_one_cat_mfg_origenes
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'ALP_MFG_ORIGEN')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_mfg_origenes'
);
- FlexField
ALP_ORIGEN
CREATE TRIGGER trg_sync_alp_one_cat_origenes
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'ALP_ORIGEN')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_origenes'
);
- FlexField
Nombre compuesto de equipos
CREATE TRIGGER trg_sync_alp_one_cat_equipos
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'Nombre compuesto de equipos')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_equipos'
);
- FlexField
Tipo de orden
CREATE TRIGGER trg_sync_alp_one_cat_tipo_orden
AFTER INSERT OR UPDATE
ON general_catalogs.alp_cat_fnd_flex_fields
FOR EACH ROW
WHEN (NEW.flex_value_set_name = 'Tipo de orden')
EXECUTE FUNCTION general_catalogs.fn_sync_alp_one_cat_flexs(
'alp_one_cat_tipo_orden'
);
4.4. Actualización automática de vigencia de catálogos
Además de la sincronización mediante triggers, se implementa un proceso programado para actualizar el estado de vigencia de los catálogos de Alpura One.
Este mecanismo es necesario porque la vigencia de un registro puede cambiar únicamente por el transcurso del tiempo, sin que exista una inserción o actualización en el catálogo general de origen.
Función ejecutada: alpura_one.fn_actualizar_vigencia_catalogos()
Programación mediante pg_cron:
| Parámetro | Configuración |
|---|---|
| Nombre del Job | alpura-one-vigencia-catalogos |
| Frecuencia | Diaria |
| Hora de ejecución | 12:05 |
| Zona horaria | Hora de México |
| Motor de programación | pg_cron |
La función revisa las fechas de vigencia de los registros y actualiza el indicador de actividad (dn_activo) de los catálogos correspondientes.
Con esto se garantiza que los valores de los catálogos reflejen su vigencia actual, incluso cuando no se produzcan cambios en los registros de origen.
5. Consideraciones técnicas
- La base de datos
omnicanalcentraliza tanto los catálogos generales como los catálogos utilizados por Alpura One. - El esquema
general_catalogsfunciona como origen de información para los procesos de sincronización. - El esquema
alpura_onecontiene las estructuras de datos utilizadas directamente por la aplicación. - Los
triggerspermiten propagar los cambios de los catálogos de origen sin requerir una ejecución manual de los procesos. - La sincronización contempla inserciones y actualizaciones; no se considera la eliminación física de los registros como mecanismo de depuración.
- La actualización de vigencia de
FlexFieldsse ejecuta de forma independiente mediantepg_cron.
6. Resultado esperado
Al finalizar la implementación, Alpura One dispone de catálogos sincronizados a partir de la información centralizada en general_catalogs.
La arquitectura permite mantener separados los procesos de réplica de información desde Oracle Cloud y los procesos de integración interna de Omnicanal, facilitando el mantenimiento, la trazabilidad y la incorporación de nuevos catálogos.
Flujo de integración: general_catalogs → Triggers general_catalogs → alpura_one
Adicionalmente: pg_cron → alpura_one.fn_actualizar_vigencia_catalogos()