Lista de Precios Calculados (alp_lista_precios)

---

1. Descripción

La función general_catalogs.alp_lista_sppd_precios determina el precio vigente de un producto para uno o varios clientes dentro de un canal de ventas específico, integrando la jerarquía de precios de Oracle Fusion Pricing y el sistema de Precios Especiales SPPD (alp_sppd_folio_rework).

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

1. Lista de precios personalizada (Perfil): Asignada según el Customer Rating del cliente.
2. Precio especial SPPD por cliente: Folios aprobados y vigentes asignados directamente a la cuenta del cliente.
3. Precio especial SPPD general: Folios aprobados y vigentes asignados a nivel canal (clientNumber = 'TODOS').
4. Lista de precios comercial: Determinada por la combinación de canal y subcanal de venta.

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ámetro	Tipo	Obligatorio	Valor por Defecto	Descripción
p_canal	TEXT	Sí	N/A	Canal de ventas a consultar (ej. 'AUTOSERVICIO', 'DETALLE'). Filtra clientes y acuerdos SPPD.
p_clientes	TEXT[]	No	'{TODOS}'	Arreglo de identificadores o números de cuenta de clientes. Si contiene 'TODOS', evalúa la totalidad de clientes del canal.
p_producto	TEXT	No	NULL	Código Oracle del artículo (item_code). Filtra la lista de precios y los folios SPPD.
p_fecha	TEXT	No	NULL	Fecha 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:

Campo	Tipo	Descripción
no_cliente	INTEGER	Número de cuenta o identificador del cliente evaluado.
item_code	TEXT	Código identificador Oracle del artículo.
item_code_as400	TEXT	Código homologado del artículo en AS400.
description	TEXT	Descripción comercial del artículo.
price_type	TEXT	Origen del precio aplicado: 'Precio Especial' o 'Precio Normal'.
udm	TEXT	Unidad de medida del precio (evaluado en 'PZ').
precio	NUMERIC	Precio final resultante tras la evaluación de precedencia.

4. Flujo funcional

La función ejecuta el siguiente proceso:

Parámetros: Canal, Clientes, Producto, Fecha
                                    │
                                    ▼
                     Filtro de Clientes del Canal
                     (Todos o Lista de Cuentas)
                                    │
                         [ Ciclo por Cliente ]
                                    │
       ┌────────────────────────────┼────────────────────────────┐
       ▼                            ▼                            ▼
 [Perfil Cliente]           [Estrategia Comercial]       [Precios Especiales SPPD]
       │                            │                            │
 Customer Rating              Canal / Subcanal            Folios Estatus 1 o 6
       │                            │                            │
 Pricing Segment              Pricing Strategy           ┌───────┴───────┐
       │                            │                    ▼               ▼
 Pricing Strategy                   │               Por Cliente     Canal TODOS
       │                            │                    │               │
  Lista Perfil                Lista Comercial       SPPD Cliente    SPPD TODOS
 (alp_lista2)                  (alp_lista1)              │               │
       │                            │                    │               │
       └────────────────────┬───────┴────────────────────┴───────────────┘
                            ▼
              Evaluación de Precedencia (COALESCE):
                 1. Precio Lista Perfil
                 2. Precio Especial SPPD (Cliente)
                 3. Precio Especial SPPD (TODOS)
                 4. Precio Lista Comercial
                            │
                            ▼
           Clasificación price_type y Resultado

5. Lógica de negocio

5. Lógica de negocio

5.1. Selección del universo de clientes

La función evalúa el parámetro p_clientes al iniciar:

• Si el arreglo contiene la palabra 'TODOS', extrae todas las combinaciones distintas de numero_cuenta_cliente y desc_subcanal directamente de la tabla maestro alp_cat_ar_clientes filtrando por el canal indicado en p_canal.
• Si recibe una lista específica de cuentas, busca coincidencias en identificador_registro o numero_cuenta_cliente dentro del canal de ventas proporcionado.

---

5.2. Obtención del perfil del cliente (Customer Rating)

Por cada cliente dentro del ciclo, la función intenta obtener el perfil de precios en Oracle:

• Consulta la tabla alp_cat_qp_pricing_profiles para extraer la columna customer_rating vigente en la fecha dada en p_fecha.

---

5.3. Obtención del Pricing Segment del perfil

• Con el customer_rating obtenido en el paso anterior, consulta la tabla alp_cat_qp_pricing_segments para traducir la calificación al segmento de precios correspondiente.

---

5.4. Obtención del Pricing Strategy del perfil

• Relaciona el segmento de precios con la tabla alp_cat_qp_pricing_assignments_strategy para extraer la estrategia de precios (pricing_strategy) que rige el perfil del cliente.

---

5.5. Obtención de la lista de precios del perfil

• Con la estrategia del perfil, se busca en la tabla maestro alp_cat_qp_pricing_oracle la entidad de tipo 'Price List' en estado "Aprobada" y que cumpla con los rangos de vigencia respecto a p_fecha. El resultado se asigna a la variable v_perfil_lista_precio.

---

5.6. Obtención de la estrategia de precios comercial

En paralelo, si el cliente no posee un perfil preferencial, o para estructurar la lista base, se busca la estrategia comercial en alp_cat_qp_pricing_assignments_strategy usando el canal y subcanal bajo el siguiente orden de prioridad en cascada:

1. Coincidencia por Canal + Subcanal.
2. Coincidencia únicamente por Canal.
3. Coincidencia únicamente por Subcanal.

---

5.7. Obtención de la lista de precios comercial

• Con la estrategia de precios comercial localizada (v_pricing_strategy), se consulta de nuevo alp_cat_qp_pricing_oracle para extraer el nombre de la lista de precios aprobada y vigente, asignándolo a v_pricing_lista_precio.

---

5.8. Búsqueda de Acuerdos Especiales (SPPD)

La función busca dentro de la tabla sppd.alp_sppd_folio_rework acuerdos especiales vigentes de precios (con estatus 1 o 6 y que no estén cancelados):

• Por cliente: Valida si existe un precio especial configurado específicamente para el número de cuenta del cliente (client_number = c.cliente::varchar).
• General (TODOS): Valida si existe un acuerdo global aplicable a todos los clientes del canal (client_number = 'TODOS').

---

5.9. Resolución y asignación del precio final

Se generan internamente las subconsultas alp_lista1 (artículos en lista comercial) y alp_lista2 (artículos en lista de perfil).

El precio devuelto corresponde a la evaluación jerárquica no nula de los resultados obtenidos (COALESCE), aplicando la siguiente prioridad de negocio:

COALESCE(al2.precio, assc.special_price, asst.special_price, al1.precio)
al2.precio: Precio de la lista de precios del perfil del cliente (Máxima prioridad).

assc.special_price: Precio especial de SPPD asignado específicamente a la cuenta de ese cliente.

asst.special_price: Precio especial de SPPD a nivel canal general (TODOS).

al1.precio: Precio de la lista de precios comercial estándar (Menor prioridad).

La etiqueta price_type se asigna dinámicamente como 'Precio Especial' si se seleccionó alguna de las ofertas vigentes de SPPD, de lo contrario se reporta como 'Precio Normal'.

---

6. Catálogos utilizados

Catálogo	Propósito
alp_cat_ar_clientes	Obtiene canal y subcanal del cliente.
alp_cat_qp_pricing_profiles	Obtiene el Customer Rating del cliente.
alp_cat_qp_pricing_segments	Convierte el Customer Rating en Pricing Segment.
alp_cat_qp_pricing_assignments_strategy	Obtiene la estrategia de precios.
alp_cat_qp_pricing_oracle	Obtiene listas de precios y descuentos vigentes.
alp_cat_qp_pricing_oracle_v	Determina la lista de descuento con mayor prioridad (menor precedencia).
alp_cat_qp_price_lists	Contiene el detalle de artículos y precios.

---

Consideraciones

• Todas las consultas validan la vigencia mediante p_fecha.
• Si algún dato intermedio no existe, la función continúa su ejecución asignando valores NULL.
• Se consideran únicamente listas y estrategias con estado Aprobada.
• Para las listas de descuento se selecciona únicamente la de menor precedencia.
• La función contiene sentencias RAISE NOTICE para facilitar la depuración durante la ejecución.

---

6. Catálogos y Tablas Utilizadas

Catálogo / Tabla	Esquema	Propósito
alp_cat_ar_clientes	general_catalogs	Catálogo maestro de clientes, canales y subcanales de venta.
alp_cat_qp_pricing_profiles	general_catalogs	Perfiles y ratings de clientes en Oracle Pricing.
alp_cat_qp_pricing_segments	general_catalogs	Segmentación de precios ligada al rating de cliente.
alp_cat_qp_pricing_assignments_strategy	general_catalogs	Matriz de asignación de estrategias por segmento, canal y subcanal.
alp_cat_qp_pricing_oracle	general_catalogs	Listas de precios y descuentos de Oracle Pricing (aprobadas y vigentes).
alp_cat_qp_price_lists	general_catalogs	Detalle de precios vigentes por artículo y unidad de medida.
alp_sppd_folio_rework	sppd	Folios y registros de acuerdos de Precios Especiales SPPD.

---

7. Consideraciones Técnicas

• Unidad de Medida Única: La consulta final filtra de manera estricta por la condición de negocio al1.udm = 'PZ' (Pieza).
• Filtro de Artículo Obligatorio: Es indispensable proporcionar un código de producto en p_producto para retornar registros, ya que la condición final evalúa al1.item_code = p_producto.

---

8. SQL

Código Función

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\$;

Ejecución en BD

-- Ejemplo 1: Consulta para clientes específicos

SELECT *
FROM general_catalogs.alp_lista_sppd_precios(
    'AUTOSERVICIO',
    ARRAY['100245', '100246'],
    '10000001',
    '2026-07-21'
);```

-- Ejemplo 2: Consulta para TODOS los clientes del canal
```sql
SELECT *
FROM general_catalogs.alp_lista_sppd_precios(
    'AUTOSERVICIO',
    '{TODOS}',
    '10000001',
    '2026-07-21'```
);    


```sql
SELECT *
FROM general_catalogs.alp_lista_precios(
    100245,
    '2026-07-21'
);

Info a Mostrar

no_cliente	item_code	item_code_as400	description	price_type	udm	precio
100245	10000001	000123	Leche Entera 1L	Precio Especial	PZ	24.50
100246	10000001	000123	Leche Entera 1L	Precio Normal	PZ	28.50