Agrupación de Precios SPPD (alp_lista_sppd_groups_prices)
1. Descripción
La función alp_lista_sppd_groups_prices actúa como una capa de agregación y consolidación analítica sobre el motor de precios alp_lista_sppd_precios. Su objetivo principal es agrupar clientes que comparten las mismas condiciones de precio final (monto neto, tipo de precio y unidad de medida) para un artículo y canal determinados.
Esta función resuelve el problema de cardinalidad en reportes masivos y cotizaciones comerciales: en lugar de devolver miles de filas idénticas cliente por cliente, comprime el resultado agrupando las cuentas separadas por comas o asignando la etiqueta especial TODO EL BLOQUE cuando el 100% de los clientes evaluados del canal tienen asignado exactamente el mismo precio.
Adicionalmente, incorpora un cruce maestro con el catálogo de artículos (alp_cat_inv_items) bajo la organización 'OMA', garantizando la persistencia de las descripciones, claves AS400 y unidades de medida incluso si un cliente o grupo no cuenta con un precio configurado (marcado explícitamente como Sin Precio).
2. Parámetros
| Parámetro | Tipo | Obligatorio | Valor por Defecto | Descripción |
|---|---|---|---|---|
p_canal | TEXT | Sí | N/A | Canal de ventas a consultar (ej. 'AUTOSERVICIO', 'DETALLE'). Filtra el universo de clientes en mv_cat_oracle_clientes. |
p_clientes | TEXT[] | No | '{TODOS}' | Arreglo de números de cuenta de clientes. Soporta:
|
p_producto | TEXT | No | NULL | Código maestro Oracle del artículo (item). Filtra la información técnica en inventarios y el precio. |
p_fecha | TEXT | No | NULL | Fecha de referencia de vigencia en formato 'YYYY-MM-DD'. |
3. Retorno
La función retorna una tabla consolidada con las siguientes columnas:
| Campo | Tipo | Descripción |
|---|---|---|
no_cliente | TEXT | Cuentas agrupadas ('100245, 100246'), cuenta individual o la leyenda 'TODO EL BLOQUE' si la totalidad de clientes comparten el precio. |
item_code | TEXT | Código identificador Oracle del artículo. |
item_code_as400 | TEXT | Código histórico u homologado en AS400 (clave_anterior). |
description | TEXT | Descripción comercial del artículo. |
price_type | TEXT | Clasificación del precio resultante: 'Precio Especial', 'Precio Normal' o 'Sin Precio'. |
udm | TEXT | Unidad de medida del artículo (primary_uom_code o UDM de lista). |
precio | NUMERIC | Precio final resultante tras la evaluación de reglas y acuerdos (nulo si es 'Sin Precio'). |
4. Flujo funcional
5. Lógica de negocio
5.1. Extracción de Metadatos de Inventario (alp_item)
Se consulta la tabla alp_cat_inv_items filtrando estrictamente por la organización comercial OMA (Maestro de Artículos Oracle Cloud) y el artículo p_producto. De aquí se aseguran los metadatos oficiales del producto:
itemclave_anteriordescriptionprimary_uom_code
5.2. Cruce y Manejo de Clientes sin Precio (alp_lista)
Se obtiene el universo de clientes activos en mv_cat_oracle_clientes para el canal p_canal y el filtro p_clientes. Se realiza un LEFT JOIN con la función base alp_lista_sppd_precios. Si un cliente no cuenta con una lista de precios asignada o folio SPPD vigente:
- Se preservan sus datos descriptivos mediante
COALESCEconalp_item. - Se asigna el valor
'Sin Precio'en la columnaprice_type. - El campo
preciopermanece comoNULL.
5.3. Agrupación y Ponderación (alp_mostrar)
Mediante agregaciones relacionales:
- Se contabiliza el total global de registros evaluados (
total_all_reg). - Se agrupan los clientes que tienen idéntico precio, tipo de precio y artículo, concatenando sus números de cuenta con
STRING_AGG(al.no_cliente::text, ', ' ORDER BY al.no_cliente::text). - Se calcula la cardinalidad de cada grupo (
total_reg).
5.4. Regla de Bloque Unificado
Para simplificar la visualización ejecutiva:
- Si todos los clientes del universo analizado comparten el mismo precio (
total_all_reg = total_reg), se sustituye la lista de números por el texto identificador'TODO EL BLOQUE'. - Si existen variaciones, se muestra la lista explícita de cuentas que integran cada precio.
6. Catálogos, Vistas y Funciones Utilizadas
| Objeto | Esquema | Tipo | Propósito Operativo |
|---|---|---|---|
alp_cat_inv_items | general_catalogs | Tabla | Maestro de artículos de inventario de Oracle Fusion (organización OMA). |
mv_cat_oracle_clientes | general_catalogs | Vista | Catálogo maestro consolidado de cuentas de clientes por canal. |
alp_lista_sppd_precios | general_catalogs | Función | Motor central de cálculo de precios y acuerdos SPPD por cliente. |
7. Consideraciones Técnicas
- Dependencia de Producto: Dado que
alp_itemrealiza un filtro directo sobreacii.item = p_producto, la consulta espera un código de producto válido. Si el parámetrop_productoes nulo o no existe en la organizaciónOMA, élCROSS JOINdescartará los registros retornando un conjunto vacío. - Homologación de Tipos de Datos: El cruce entre la vista de clientes y el motor de precios castea explícitamente
c.numero_cuenta_cliente::int = p.no_cliente, asegurando compatibilidad con los tipos de datos internos. - Reducción de Payload de Red: Al agrupar cuentas masivas en un solo registro con
STRING_AGGo'TODO EL BLOQUE', se reduce drásticamente el volumen de datos transferido hacia aplicaciones cliente o microservicios.
8. SQL
- Código Función
- Ejecución en BD
- Información a Mostrar
CREATE OR REPLACE FUNCTION general_catalogs.alp_lista_sppd_groups_prices(p_canal text, p_clientes text[] DEFAULT '{TODOS}'::text[], p_producto text DEFAULT NULL::text, p_fecha text DEFAULT NULL::text)
RETURNS TABLE(no_cliente text, item_code text, item_code_as400 text, description text, price_type text, udm text, precio numeric)
LANGUAGE plpgsql
AS $function$
begin
return query
with alp_item as
( select acii.item
, acii.clave_anterior
, acii.description
, acii.primary_uom_code
from alp_cat_inv_items acii
where acii.org_code = 'OMA'
and acii.item = p_producto)
, alp_lista as
( select c.numero_cuenta_cliente no_cliente
, coalesce(p.item_code,ai.item) item_code
, coalesce(p.item_code_as400,ai.clave_anterior) item_code_as400
, coalesce(p.description,ai.description) description
, coalesce(p.price_type,'Sin Precio') price_type
, coalesce(p.udm,ai.primary_uom_code) udm
, p.precio
from mv_cat_oracle_clientes c
left join general_catalogs.alp_lista_sppd_precios(p_canal,p_clientes,p_producto,p_fecha) p
on c.numero_cuenta_cliente::int = p.no_cliente
cross join alp_item ai
where c.desc_canal_tipo_cuenta = p_canal
and ('TODOS' = ANY(p_clientes::text[])
or 'LIST_PRICE' = ANY(p_clientes::text[])
or c.numero_cuenta_cliente::text = ANY(p_clientes::text[]))
group by c.numero_cuenta_cliente
, coalesce(p.item_code,ai.item)
, coalesce(p.item_code_as400,ai.clave_anterior)
, coalesce(p.description,ai.description)
, coalesce(p.price_type,'Sin Precio')
, coalesce(p.udm,ai.primary_uom_code)
, p.precio)
, alp_mostrar as
( select ( select count(*)
from alp_lista) total_all_reg
, count(*) total_reg
, string_agg
( al.no_cliente::text
, ', ' order by al.no_cliente::text) clientes
, al.item_code
, al.item_code_as400
, al.description
, al.price_type
, al.udm
, al.precio
from alp_lista al
group by al.item_code
, al.item_code_as400
, al.description
, al.price_type
, al.udm
, al.precio)
select case
when am.total_all_reg = 1 then am.clientes
when am.total_all_reg = am.total_reg then 'TODO EL BLOQUE'
else am.clientes
end clientes
, am.item_code
, am.item_code_as400
, am.description
, am.price_type
, am.udm
, am.precio
from alp_mostrar am
order by total_reg;
end;
$function$
;
Ejemplo 1: Consulta para una lista explícita de cuentas (DETALLE)
SELECT * FROM general_catalogs.alp_lista_sppd_groups_prices(
'DETALLE',
ARRAY['247029', '247030', '247031'],
'CRAC070',
'2026-10-01'
);
Ejemplo 2: Consulta para TODOS los clientes del canal DETALLE para el artículo CRAC070
SELECT * FROM general_catalogs.alp_lista_sppd_groups_prices(
'DETALLE',
'{TODOS}',
'CRAC070',
'2026-10-01'
);
Ejemplo 3: Consulta para todos los clientes del canal con Precio de Lista (Sin Precio SPPD)
SELECT * FROM general_catalogs.alp_lista_sppd_groups_prices(
'DETALLE',
'{LIST_PRICE}',
'CRAC070',
'2026-10-01'
);
A continuación se detallan los resultados esperados que se devuelven para diferentes ejemplos:
Ejemplo 1: Consulta para cuentas específicas (Con y sin Precio SPPD)
En este escenario, se evalúan únicamente las tres cuentas indicadas. El cliente 247031 cuenta con un acuerdo de precio especial vigente en SPPD ($15.20), mientras que los clientes 247029 y 247030 conservan el precio de lista estándar de Oracle ($18.50), agrupándose automáticamente.
| no_cliente | item_code | item_code_as400 | description | price_type | udm | precio |
|---|---|---|---|---|---|---|
| 247031 | CRAC070 | 007054 | Crema Ácida Alpura 200g | Precio Especial | PZ | 15.20 |
| 247029, 247030 | CRAC070 | 007054 | Crema Ácida Alpura 200g | Precio Normal | PZ | 18.50 |
Ejemplo 2: Consulta para TODOS los clientes del canal ({TODOS})
El motor evalúa el universo completo de clientes del canal 'DETALLE'. Como se detecta que absolutamente todos los clientes evaluados del canal tienen asignado exactamente el mismo precio de lista de Oracle (sin precios especiales particulares activos), la función optimiza el resultado agrupando a todo el universo bajo la etiqueta inteligente TODO EL BLOQUE.
| no_cliente | item_code | item_code_as400 | description | price_type | udm | precio |
|---|---|---|---|---|---|---|
| TODO EL BLOQUE | CRAC070 | 007054 | Crema Ácida Alpura 200g | Precio Normal | PZ | 18.50 |
Ejemplo 3: Consulta con Precio de Lista ({LIST_PRICE})
Al utilizar la bandera 'LIST_PRICE', el motor de cálculo omite intencionalmente cualquier acuerdo de precio especial de SPPD (ignorando la promoción del cliente 247031), reportando el precio de lista puro como 'Precio Normal'. Al unificarse el precio para todas las cuentas, el sistema vuelve a agruparlas bajo la leyenda de bloque.
| no_cliente | item_code | item_code_as400 | description | price_type | udm | precio |
|---|---|---|---|---|---|---|
| TODO EL BLOQUE | CRAC070 | 007054 | Crema Ácida Alpura 200g | Precio Normal | PZ | 18.50 |