Skip to main content

Lista de Precios Calculados (alp_lista_precios)


1. Descripción​

La función alp_lista_sppd_precios calcula el precio neto vigente de un artículo para uno o varios clientes dentro de un canal de ventas específico en PostgreSQL. Integra de forma nativa las listas maestras de precios y estrategias comerciales de Oracle Fusion Pricing con los folios de acuerdos de Precios Especiales SPPD (alp_sppd_folio_rework).

A diferencia de versiones anteriores basadas en cursor imperativo (FOR ... LOOP), la arquitectura actual procesa el cálculo en un único pase relacional basado en CTEs (Common Table Expressions). Esto optimiza drásticamente el tiempo de ejecución y la escalabilidad cuando se consulta el universo completo de clientes del canal.

La lógica evalúa cuatro niveles para determinar el precio final:

  1. Precio por Perfil de Cliente (alp_perfil): Se busca coincidencia directa entre la cuenta del cliente y el customer_rating asociado a su estrategia de precios.
  2. Precio Comercial por Canal y Subcanal (alp_canal_subcanal): Si el cliente no tiene un perfil específico, se asigna la lista comercial ligada a su combinación exacta de canal y subcanal.
  3. Precio Comercial por Subcanal (alp_subcanal): Si no hay regla de canal + subcanal, se busca la regla residual asignada únicamente al subcanal de venta.

Sobre el precio base obtenido de Oracle Pricing, se sobreponen los folios aprobados de SPPD:

El resultado clasifica además el tipo de precio aplicado en Precio Especial o Precio Normal, filtrando por defecto sobre la unidad de medida pieza (PZ).


2. Parámetros​

ParámetroTipoObligatorioValor por DefectoDescripción
p_canalTEXTSíN/ACanal de ventas a consultar (ej. 'AUTOSERVICIO', 'DETALLE'). Filtra clientes y acuerdos SPPD.
p_clientesTEXT[]No'{TODOS}'Arreglo de números de cuenta de clientes. Soporta además banderas especiales:
  • 'TODOS': Evalúa a todos los clientes del canal.
  • 'LIST_PRICE': Evalúa clientes retornando estrictamente el precio de lista (anula SPPD).
  • ARRAY['100245', '100246']: Lista explícita de cuentas.
p_productoTEXTNoNULLCódigo Oracle del artículo (item_code). Filtra la lista de precios y los folios SPPD.
p_fechaTEXTNoNULLFecha de referencia para validar la vigencia de listas, perfiles y folios SPPD (formato 'YYYY-MM-DD').

3. Retorno​

La función retorna una tabla con las siguientes columnas:

CampoTipoDescripción
no_clienteINTEGERNúmero de cuenta o identificador del cliente evaluado.
item_codeTEXTCódigo identificador Oracle del artículo.
item_code_as400TEXTCódigo homologado del artículo en AS400.
descriptionTEXTDescripción comercial del artículo.
price_typeTEXTOrigen del precio aplicado: 'Precio Especial' o 'Precio Normal'.
udmTEXTUnidad de medida del precio (evaluado en 'PZ').
precioNUMERICPrecio final resultante tras la evaluación de precedencia.

4. Flujo funcional​

La función ejecuta el siguiente proceso:

5. Lógica de negocio​

5.1. Extracción Unificada de Precios Oracle (alp_qp_precios)​

Se filtran y preparan todos los artículos correspondientes a p_producto en unidad de medida 'PZ', asegurando que:

  • La lista tenga tipo 'Price List' y estatus 'Aprobada'.
  • La fecha de evaluación p_fecha caiga dentro del rango de vigencia de la lista (detail_start_date / detail_end_date) y de la estrategia (strategy_start_date / strategy_end_date).
  • Se enlaza la asignación de estrategia (alp_cat_qp_pricing_assignments_strategy) y opcionalmente el perfil del cliente (alp_cat_qp_pricing_profiles) mediante LEFT JOIN.

5.2. Filtrado de Universo de Clientes (alp_clientes)​

Filtra sobre alp_cat_ar_clientes con base en el canal p_canal:

  • Si p_clientes contiene 'TODOS' o 'LIST_PRICE', se incluyen todos los clientes del canal agrupados por cuenta, canal y subcanal.
  • Si se proporcionan cuentas específicas, se evalúa numero_cuenta_cliente::text = ANY(p_clientes::text[]).

5.3. Cascada de Precios Base (Perfil -> Canal/Subcanal -> Subcanal)​

Para evitar duplicidades y garantizar la prioridad de listas:

  1. alp_perfil: Clientes con precio derivado de su cuenta y perfil individual.
  2. alp_canal_subcanal: Clientes que no tuvieron precio en alp_perfil, asignándoles la lista por canal + subcanal.
  3. alp_subcanal: Clientes residuales que no existieron ni en alp_perfil ni en alp_canal_subcanal, asignándoles la lista por subcanal.

5.4. Acuerdos Especiales SPPD (alp_sppd)​

Se desanidan mediante CROSS JOIN LATERAL los clientes de la estructura JSON de alp_sppd_folio_rework:

  • Filtra folios aprobados (id_estatus_folio IN (1, 6)), no cancelados (is_cancelation = false), vigentes a p_fecha y correspondientes al producto p_producto.
  • Mediante ROW_NUMBER() OVER (PARTITION BY product_code ORDER BY id DESC) se asegura el folio más reciente (reg = 1).

5.5. Prioridad y Regla de Bandera 'LIST_PRICE'​

  • Si en p_clientes se especifica 'LIST_PRICE', el sistema omite intencionalmente cualquier precio promocional de SPPD y reporta el precio estándar de lista como 'Precio Normal'.
  • Si no está presente dicha bandera, se prioriza el acuerdo de SPPD general (TODOS), luego el de cliente particular, y finalmente el precio base de lista.

6. Catálogos y Tablas Utilizadas​

Catálogo / TablaEsquemaPropósito Operativo
alp_cat_ar_clientesgeneral_catalogsCatálogo maestro de clientes, identificando número de cuenta, canal y subcanal de venta.
alp_cat_qp_price_listsgeneral_catalogsDetalle de precios vigentes por lista, artículo y unidad de medida.
alp_cat_qp_pricing_oraclegeneral_catalogsCabecera y estatus de listas de precios y descuentos de Oracle Pricing.
alp_cat_qp_pricing_assignments_strategygeneral_catalogsMatriz de asignación de estrategias de precios por segmento, canal y subcanal.
alp_cat_qp_pricing_profilesgeneral_catalogsPerfiles y ratings individuales asignados a las cuentas de clientes.
alp_sppd_folio_reworksppdFolios y acuerdos comerciales de Precios Especiales SPPD.

7. Consideraciones Técnicas​

  • Eliminación de Bucles: La función ejecuta un único plan de ejecución declarativo en PostgreSQL (RETURN QUERY WITH ...), eliminando variables escalares intermedias, bloques condicionales FOR ... LOOP y sentencias RAISE NOTICE.
  • Filtro Estricto de Producto y UDM: Para optimizar los índices de las tablas de listas de precios, la función requiere p_producto y restringe la evaluación a piezas (udm = 'PZ').
  • Manejo de Nulos en Fechas: Las fechas de fin de vigencia en las listas de precios y estrategias que sean nulas se interpretan como abiertas mediante COALESCE(fecha_fin, p_fecha::timestamp).

8. SQL​

CREATE OR REPLACE FUNCTION general_catalogs.alp_lista_sppd_precios(
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 integer,
item_code text,
item_code_as400 text,
description text,
price_type text,
udm text,
precio numeric
)
LANGUAGE plpgsql
AS \$function\$
declare
v_cliente int;
v_perfil text;
v_perfil_segment text;
v_perfil_strategy text;
v_pricing_segment text;
v_pricing_strategy text;
v_perfil_lista_precio text;
v_perfil_lista_descuento text;
v_pricing_lista_precio text;
v_pricing_lista_descuento text;

v_clientes jsonb[];

c RECORD;

begin
if exists ( select 1
from unnest(p_clientes) x(cliente)
where UPPER(x.cliente) = 'TODOS') then

select array_agg(distinct jsonb_build_object('cliente', numero_cuenta_cliente::int,'subcanal', desc_subcanal))
into v_clientes
from alp_cat_ar_clientes
where desc_canal_tipo_cuenta = p_canal;
else
select array_agg(distinct jsonb_build_object('cliente', numero_cuenta_cliente::int,'subcanal', desc_subcanal))
into v_clientes
from alp_cat_ar_clientes
where desc_canal_tipo_cuenta = p_canal
and (identificador_registro::int = any(array( select cliente::int
from unnest(p_clientes) as x(cliente)))
or numero_cuenta_cliente::int = any(array( select cliente::int
from unnest(p_clientes) as x(cliente))));
end if;

RAISE NOTICE 'Clientes %', v_clientes;

for c in
select *
from jsonb_to_recordset(to_jsonb(v_clientes)) as x(cliente int,subcanal text)
loop
v_perfil := null;
v_perfil_segment := null;
v_perfil_strategy := null;
v_perfil_lista_precio := null;
v_perfil_lista_descuento := null;
v_pricing_segment := null;
v_pricing_strategy := null;
v_pricing_lista_precio := null;
v_pricing_lista_descuento := null;

begin
select p.customer_rating
into v_perfil
from alp_cat_qp_pricing_profiles p
where p_fecha::timestamp between coalesce(p.start_date::timestamp,p_fecha::timestamp)
and coalesce(p.end_date::timestamp,p_fecha::timestamp)
and p.account_number::int = c.cliente;
exception
when others then
v_perfil := null;
end;

if v_perfil is not null then
begin
select ps.pricing_segment
into v_perfil_segment
from omnicanal.general_catalogs.alp_cat_qp_pricing_segments ps
where ps.customer_rating = v_perfil
and p_fecha::timestamp between coalesce(ps.start_date::timestamp,p_fecha::timestamp)
and coalesce(ps.end_date::timestamp,p_fecha::timestamp);
exception
when others then
v_perfil_segment := null;
end;

if v_perfil_segment is not null then
begin
select pst.pricing_strategy
into v_perfil_strategy
from omnicanal.general_catalogs.alp_cat_qp_pricing_assignments_strategy pst
where pst.pricing_segment = v_perfil_segment;
exception
when others then
v_perfil_strategy := null;
end;

if v_perfil_strategy is not null then
begin
select pl.detail_entity
into v_perfil_lista_precio
from general_catalogs.alp_cat_qp_pricing_oracle pl
where pl.detail_entity_type = 'Price List'
and pl.status = 'Aprobada'
and pl.detail_status = 'Aprobada'
and pl.pricing_strategy = v_perfil_strategy
and p_fecha::timestamp between coalesce(pl.detail_start_date::timestamp,p_fecha::timestamp)
and coalesce(pl.detail_end_date::timestamp,p_fecha::timestamp)
and p_fecha::timestamp between coalesce(pl.strategy_start_date::timestamp,p_fecha::timestamp)
and coalesce(pl.strategy_end_date::timestamp,p_fecha::timestamp);
exception
when others then
v_perfil_lista_precio := null;
end;

begin
select detail_entity
into v_perfil_lista_descuento
from ( select prd.detail_entity
, row_number()
over ( partition by prd.pricing_strategy
order by prd.detail_start_date) reg
from general_catalogs.alp_cat_qp_pricing_oracle prd
where prd.detail_entity_type = 'Discount List'
and prd.status = 'Aprobada'
and prd.detail_status = 'Aprobada'
and prd.pricing_strategy = v_perfil_strategy
and p_fecha::timestamp between coalesce(prd.detail_start_date::timestamp,p_fecha::timestamp)
and coalesce(prd.detail_end_date::timestamp,p_fecha::timestamp)
and p_fecha::timestamp between coalesce(prd.strategy_start_date::timestamp,p_fecha::timestamp)
and coalesce(prd.strategy_end_date::timestamp,p_fecha::timestamp))
where reg = 1;
exception
when others then
v_perfil_lista_descuento := null;
end;
end if;
end if;
end if;

begin
select pas1.pricing_segment
, pas1.pricing_strategy
into v_pricing_segment
, v_pricing_strategy
from omnicanal.general_catalogs.alp_cat_qp_pricing_assignments_strategy pas1
where pas1.canal_ventas = p_canal
and pas1.subcanal_ventas = c.subcanal;
exception
when others then
begin
select pas2.pricing_segment
, pas2.pricing_strategy
into v_pricing_segment
, v_pricing_strategy
from omnicanal.general_catalogs.alp_cat_qp_pricing_assignments_strategy pas2
where pas2.canal_ventas = p_canal;
exception
when others then
begin
select pas3.pricing_segment
, pas3.pricing_strategy
into v_pricing_segment
, v_pricing_strategy
from omnicanal.general_catalogs.alp_cat_qp_pricing_assignments_strategy pas3
where pas3.subcanal_ventas = c.subcanal;
exception
when others then
v_pricing_segment := null;
v_pricing_strategy := null;
end;
end;
end;

if v_pricing_strategy is not null then
begin
select pl.detail_entity
into v_pricing_lista_precio
from general_catalogs.alp_cat_qp_pricing_oracle pl
where pl.detail_entity_type = 'Price List'
and pl.status = 'Aprobada'
and pl.detail_status = 'Aprobada'
and pl.pricing_strategy = v_pricing_strategy
and p_fecha::timestamp between coalesce(pl.detail_start_date::timestamp,p_fecha::timestamp)
and coalesce(pl.detail_end_date::timestamp,p_fecha::timestamp)
and p_fecha::timestamp between coalesce(pl.strategy_start_date::timestamp,p_fecha::timestamp)
and coalesce(pl.strategy_end_date::timestamp,p_fecha::timestamp);
exception
when others then
v_pricing_lista_precio := null;
end;

begin
select detail_entity
into v_pricing_lista_descuento
from ( select prd.detail_entity
, row_number()
over ( partition by prd.pricing_strategy
order by prd.detail_start_date) reg
from general_catalogs.alp_cat_qp_pricing_oracle prd
where prd.detail_entity_type = 'Discount List'
and prd.status = 'Aprobada'
and prd.detail_status = 'Aprobada'
and prd.pricing_strategy = v_pricing_strategy
and p_fecha::timestamp between coalesce(prd.detail_start_date::timestamp,p_fecha::timestamp)
and coalesce(prd.detail_end_date::timestamp,p_fecha::timestamp)
and p_fecha::timestamp between coalesce(prd.strategy_start_date::timestamp,p_fecha::timestamp)
and coalesce(prd.strategy_end_date::timestamp,p_fecha::timestamp))
where reg = 1;
exception
when others then
v_pricing_lista_descuento := null;
end;
end if;

raise notice 'Cliente: %', c.cliente;
raise notice 'Subcanal: %', c.subcanal;
raise notice 'Perfil: %', v_perfil;
raise notice 'Perfil Segmento: %', v_perfil_segment;
raise notice 'Perfil Estrategia: %', v_perfil_strategy;
raise notice 'Perfil Lista de Precio: %', v_perfil_lista_precio;
raise notice 'Perfil Lista Descuento: %', v_perfil_lista_descuento;
raise notice 'Pricing Segment %', v_pricing_segment;
raise notice 'Pricing Strategy %', v_pricing_strategy;
raise notice 'Pricing Lista Precio %', v_pricing_lista_precio;
raise notice 'Pricing Lista Descuentos %', v_pricing_lista_descuento;

return query
with alp_lista1 as
( select l1.item_code
, l1.item_code_as400
, l1.description
, l1.udm
, l1.precio::numeric precio
from general_catalogs.alp_cat_qp_price_lists l1
where l1.lista_precios = v_pricing_lista_precio
and p_fecha::timestamp between l1.fecha_inicio::timestamp
and COALESCE(l1.fecha_fin::date,p_fecha::timestamp))
, alp_lista2 as
( select l2.item_code
, l2.item_code_as400
, l2.description
, l2.udm
, l2.precio::numeric precio
from general_catalogs.alp_cat_qp_price_lists l2
where l2.lista_precios = v_perfil_lista_precio
and p_fecha::timestamp between l2.fecha_inicio::timestamp
and COALESCE(l2.fecha_fin::date,p_fecha::timestamp))
, alp_sppd as
( select r.id
, client.customer ->> 'clientNumber' client_number
, split_part(client.customer ->> 'channel',' - ',1) channel_code
, split_part(client.customer ->> 'channel', ' - ',2) channel
, client.customer #>> '{products,0,keyProducts}' product_code
, (client.customer #>> '{products,0,specialPrice}')::numeric special_price
from sppd.alp_sppd_folio_rework r
cross join lateral jsonb_array_elements(r.customers) client(customer)
where r.id_estatus_folio in (1,6)
and coalesce(is_cancelation,false) = false
and p_fecha::timestamp between to_date(r.inicio_vigencia,'DD-MM-YYYY')
and to_date(r.fin_vigencia,'DD-MM-YYYY'))
, alp_sppd_sp_todos as
( select row_number()
over ( partition by product_code
order by id desc) reg
, product_code
, special_price
from alp_sppd
where client_number = 'TODOS'
and channel = p_canal
and product_code = p_producto)
, alp_sppd_sp_client as
( select row_number()
over ( partition by product_code
order by id desc) reg
, product_code
, special_price
from alp_sppd
where channel = p_canal
and client_number = c.cliente::varchar
and product_code = p_producto)
select c.cliente no_cliente
, al1.item_code
, al1.item_code_as400
, al1.description
, case
when assc.special_price is not null then 'Precio Especial'
when asst.special_price is not null then 'Precio Especial'
else 'Precio Normal'
end price_type
, al1.udm
, coalesce(al2.precio,assc.special_price,asst.special_price,al1.precio) precio
from alp_lista1 al1
left join alp_lista2 al2
on al1.item_code = al2.item_code
and al1.udm = al2.udm
left join alp_sppd_sp_client assc
on al1.item_code = assc.product_code
and assc.reg = 1
left join alp_sppd_sp_todos asst
on al1.item_code = asst.product_code
and asst.reg = 1
where al1.item_code = p_producto
and al1.udm = 'PZ'
order by al1.item_code
, al1.udm;
end loop;
end;
\$function\$;