Función alp_lista_precios

---

1. Descripción

La función general_catalogs.alp_lista_precios determina la lista de precios vigente de un cliente considerando la configuración de Pricing de Oracle Fusion.

La lógica combina dos niveles de asignación:

1. Lista de precios comercial, determinada por el canal y subcanal del cliente.
2. Lista de precios personalizada, determinada por el perfil (Customer Rating) asignado al cliente.

Cuando un producto existe en ambas listas, prevalece el precio del perfil del cliente; en caso contrario se utiliza el precio de la estrategia comercial.

---

2. Parámetros

Parámetro	Tipo	Obligatorio	Descripción
p_cliente	INTEGER	Sí	Identificador del cliente o número de cuenta.
p_fecha	TEXT	Sí	Fecha sobre la cual se desea consultar la lista de precios. Se utiliza para validar vigencias de perfiles, estrategias y listas de precios.

---

3. Retorno

La función retorna una tabla con los siguientes campos:

Campo	Tipo	Descripción
item_code	TEXT	Identificador Oracle del artículo.
item_code_as400	TEXT	Código del artículo en AS400.
description	TEXT	Descripción del artículo.
udm	TEXT	Unidad de medida.
precio	NUMERIC	Precio vigente del producto.

4. Flujo funcional

La función ejecuta el siguiente proceso:

                Cliente
                   │
                   ▼
      Obtiene Canal y Subcanal
                   │
                   ▼
     Obtiene Customer Rating
                   │
                   ▼
       Obtiene Pricing Segment
                   │
                   ▼
      Obtiene Pricing Strategy
                   │
         ┌─────────┴─────────┐
         ▼                   ▼
 Estrategia Comercial   Estrategia Perfil
         │                   │
         ▼                   ▼
 Lista Comercial      Lista Personalizada
         │                   │
         └─────────┬─────────┘
                   ▼
      Se prioriza el precio
       de la lista personalizada
                   │
                   ▼
          Resultado Final

5. Lógica de negocio

5.1. Obtención del canal comercial

Se consulta el catálogo:

general_catalogs.alp_cat_ar_clientes

para obtener:

• Canal
• Subcanal

del cliente recibido.

---

5.2. Obtención del perfil del cliente

Se consulta:

general_catalogs.alp_cat_qp_pricing_profiles

para obtener el:

• Customer Rating

vigente para la fecha solicitada.

---

5.3. Obtención del Pricing Segment

Con el Customer Rating se consulta:

general_catalogs.alp_cat_qp_pricing_segments

obteniendo el segmento de precios correspondiente.

---

5.4. Obtención del Pricing Strategy del perfil

El segmento obtenido se relaciona con:

general_catalogs.alp_cat_qp_pricing_assignments_strategy

para determinar la estrategia de precios asociada al perfil.

---

5.5. Obtención de listas del perfil

Con la estrategia anterior se consultan las listas vigentes:

• Price List
• Discount List

desde:

general_catalogs.alp_cat_qp_pricing_oracle

únicamente considerando registros:

• Aprobados
• Dentro de vigencia

---

5.6. Obtención de estrategia comercial

Utilizando:

• Canal
• Subcanal

se busca la estrategia comercial en:

general_catalogs.alp_cat_qp_pricing_assignments_strategy

La búsqueda sigue el siguiente orden:

1. Canal + Subcanal
2. Canal
3. Subcanal

---

5.7. Obtención de listas comerciales

Se obtienen:

• Lista de precios
• Lista de descuentos

desde:

general_catalogs.alp_cat_qp_pricing_oracle

respetando:

• Vigencia
• Estado aprobado
• Menor precedencia para descuentos

---

5.8. Construcción del resultado

Se consultan los artículos de:

general_catalogs.alp_cat_qp_price_lists

utilizando:

• Lista Comercial
• Lista del Perfil

Posteriormente ambas listas son combinadas mediante un LEFT JOIN.

La asignación del precio final es:

COALESCE(precio_perfil, precio_comercial)

por lo que:

• Si existe precio personalizado, éste tiene prioridad.
• Si no existe, se utiliza el precio comercial.

---

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.

---

7. SQL

Código Función

CREATE OR REPLACE FUNCTION general_catalogs.alp_lista_precios(p_cliente integer, p_fecha text DEFAULT NULL::text)
RETURNS TABLE(item_code text, item_code_as400 text, description text, udm text, precio numeric)
LANGUAGE plpgsql
AS $function$
DECLARE
v_canal     				text;
v_subcanal  				text;
v_perfil					text;
v_strategia					text;
v_perfil_segment			text;
v_perfil_strategy			text;
v_pricing_segment			text;
v_pricing_strategy			text;
v_perfil_lista_precio		text;
v_pricing_lista_precio		text;
v_perfil_lista_descuento	text;
v_pricing_lista_descuento	text;

begin
begin
    select  c.desc_canal_tipo_cuenta
       ,	c.desc_subcanal
	into    v_canal
		,	v_subcanal
	from    omnicanal.general_catalogs.alp_cat_ar_clientes	c
	where   c.identificador_registro::int = p_cliente
	or   	c.numero_cuenta_cliente::int  = p_cliente
	group by c.desc_canal_tipo_cuenta
		,	 c.desc_subcanal;
exception
	when others then
		v_canal		:=	null;
		v_subcanal	:=	null;

end;

begin
	select	p.customer_rating
	into	v_perfil
	from	omnicanal.general_catalogs.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	=		p_cliente
	or		p.party_number::int		=		p_cliente);

exception
	when others then
		v_perfil	:=	null;

end;

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;

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;

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	pd.detail_entity
	into	v_perfil_lista_descuento
	from 	general_catalogs.alp_cat_qp_pricing_oracle	pd
	where	pd.detail_entity_type	=		'Discount List'
	and		pd.status				=		'Aprobada'
	and		pd.detail_status		=		'Aprobada'
	and		pd.pricing_strategy		=		v_perfil_strategy
	and		p_fecha::timestamp		between	coalesce(pd.detail_start_date::timestamp,p_fecha::timestamp)
									and		coalesce(pd.detail_end_date::timestamp,p_fecha::timestamp)
	and		p_fecha::timestamp		between	coalesce(pd.strategy_start_date::timestamp,p_fecha::timestamp)
									and		coalesce(pd.strategy_end_date::timestamp,p_fecha::timestamp)	
	and		exists	(	select	min(spd.precedence)
						from 	general_catalogs.alp_cat_qp_pricing_oracle_v	spd
						where	spd.detail_entity_type	=		'Discount List'
						and		spd.status				=		'Aprobada'
						and		spd.detail_status		=		'Aprobada'
						and		spd.pricing_strategy	=		pd.pricing_strategy
						and		p_fecha::timestamp		between	coalesce(spd.detail_start_date::timestamp,p_fecha::timestamp)
														and		coalesce(spd.detail_end_date::timestamp,p_fecha::timestamp)
						and		p_fecha::timestamp		between	coalesce(spd.strategy_start_date::timestamp,p_fecha::timestamp)
														and		coalesce(spd.strategy_end_date::timestamp,p_fecha::timestamp)
						having 	min(spd.precedence) =	pd.precedence);

exception
	when others then
		v_perfil_lista_descuento	:=	null;

end;

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 		=	v_canal	
	and		pas1.subcanal_ventas	=	v_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	=	v_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	=	v_subcanal;
			
				exception
					when others then
						v_pricing_segment	:=	null;
						v_pricing_strategy	:=	null;
			
				end;
	
		end;

end;

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	prd.detail_entity
	into	v_pricing_lista_descuento
	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)	
	and		exists (	select	min(sprd.precedence) 
						from 	general_catalogs.alp_cat_qp_pricing_oracle	sprd
						where	sprd.detail_entity_type	=		'Discount List'
						and		sprd.status				=		'Aprobada'
						and		sprd.detail_status		=		'Aprobada'
						and		sprd.pricing_strategy	=		prd.pricing_strategy
						and		p_fecha::timestamp		between	coalesce(sprd.detail_start_date::timestamp,p_fecha::timestamp)
														and		coalesce(sprd.detail_end_date::timestamp,p_fecha::timestamp)
						and		p_fecha::timestamp		between	coalesce(sprd.strategy_start_date::timestamp,p_fecha::timestamp)
														and		coalesce(sprd.strategy_end_date::timestamp,p_fecha::timestamp)
						having 	min(sprd.precedence) =	prd.precedence);		

exception
	when others then
		v_pricing_lista_descuento	:=	null;

end;

RAISE NOTICE 'Cliente %', p_cliente;
RAISE NOTICE 'Canal %', v_canal;
RAISE NOTICE 'Subcanal %', v_subcanal;
RAISE NOTICE 'Perfil %', v_perfil;
RAISE NOTICE 'Perfil Segment %', v_perfil_segment;
RAISE NOTICE 'Perfil Strategy %', v_perfil_strategy;
RAISE NOTICE 'Perfil Lista Precios %', v_perfil_lista_precio;
RAISE NOTICE 'Perfil Lista Descuentos %', 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))
	select	al1.item_code
		,	al1.item_code_as400 
		,	al1.description 
		,	al1.udm
		,	coalesce(al2.precio,al1.precio)	precio
	from 	alp_lista1			al1
		left join	alp_lista2	al2
			on		al1.item_code	=	al2.item_code
				and	al1.udm			=	al2.udm
	order by	al1.item_code
		,		al1.udm;

END;
$function$
;

Ejecución en BD

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

Info a Mostrar

item_code	item_code_as400	description	udm	precio
10000001	000123	Leche Entera 1L	PIEZA	28.50
10000002	000124	Yogurt Natural	PIEZA	18.90
10000003	000125	Crema 450 g	PIEZA	39.00