Catalogo alp_cat_qp_pricing_profiles

1. Modulo

Precios y Descuentos (QP)

---

2. Descripción

Perfiles comerciales de clientes asignados para la segmentación y cálculo dinámico de precios.

---

3. Columnas

#	Nombre de Columna	Descripción
1	perfil_id	Identificador único concatenado (customer_pricing_profile_id | partynumber | accountnumber).
2	account_number	Número de cuenta de facturación del cliente en Oracle.
3	party_number	Número único de registro de la entidad (Party Number) en el maestro de clientes.
4	customer_name	Nombre comercial o razón social del cliente.
5	revenue_potential	Código del potencial de ingresos estimado que representa el cliente.
6	cost_to_serve	Código del costo estimado para atender o servir a este cliente.
7	customer_value	Código que clasifica el valor estratégico del cliente para la empresa.
8	customer_size	Código que determina la clasificación por tamaño del cliente.
9	customer_rating	Calificación o nivel de crédito / confiabilidad asignado al cliente.
10	start_date	Fecha de inicio de vigencia de la asignación del perfil de precios.
11	end_date	Fecha de fin de vigencia de la asignación del perfil de precios.
12	partyid	Identificador interno único (ID de la entidad) en la tabla maestra de clientes.

---

4. Querys

Databricks DEV

select  CONCAT_WS('|',qcpp.customer_pricing_profile_id,hp.partynumber,hca.accountnumber)  perfil_id
,   hca.accountnumber           account_number
,   hp.partynumber              party_number
,   hp.partyname                customer_name
,   qcpp.revenue_potential_code revenue_potential
,   qcpp.cost_to_serve_code     cost_to_serve
,   qcpp.customer_value_code    customer_value
,   qcpp.customer_size_code     customer_size
,   qcpp.customer_rating_code   customer_rating
,   qcpp.start_date
,   qcpp.end_date
,   hp.partyid
from          cat_master_oracle_fusion.sch_gold_layer.qp_cust_pricing_profiles              qcpp
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo            hp
on  qcpp.customer_party_id  = hp.partyid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo  hca
on  hp.partyid  = hca.partyid

Databricks UAT

select  CONCAT_WS('|',qcpp.customer_pricing_profile_id,hp.partynumber,hca.accountnumber)  perfil_id
,   hca.accountnumber           account_number
,   hp.partynumber              party_number
,   hp.partyname                customer_name
,   qcpp.revenue_potential_code revenue_potential
,   qcpp.cost_to_serve_code     cost_to_serve
,   qcpp.customer_value_code    customer_value
,   qcpp.customer_size_code     customer_size
,   qcpp.customer_rating_code   customer_rating
,   qcpp.start_date
,   qcpp.end_date
,   hp.partyid
from          cat_master_oracle_fusion.sch_gold_layer.qp_cust_pricing_profiles_stg              qcpp
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo_stg            hp
on  qcpp.customer_party_id  = hp.partyid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo_stg  hca
on  hp.partyid  = hca.partyid

Databricks PROD

select  CONCAT_WS('|',qcpp.customer_pricing_profile_id,hp.partynumber,hca.accountnumber)  perfil_id
,   hca.accountnumber           account_number
,   hp.partynumber              party_number
,   hp.partyname                customer_name
,   qcpp.revenue_potential_code revenue_potential
,   qcpp.cost_to_serve_code     cost_to_serve
,   qcpp.customer_value_code    customer_value
,   qcpp.customer_size_code     customer_size
,   qcpp.customer_rating_code   customer_rating
,   qcpp.start_date
,   qcpp.end_date
,   hp.partyid
from          cat_master_oracle_fusion.sch_gold_layer.qp_cust_pricing_profiles_prd              qcpp
join        cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_partyextractpvo_prd            hp 
on  qcpp.customer_party_id  = hp.partyid
left join   cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo_prd  hca 
on  hp.partyid  = hca.partyid

Oracle

select  qcpp.customer_pricing_profile_id
    ||  '|'
    ||  hp.party_number
    ||  '|'
    ||  hca.account_number                perfil_id
,   hca.account_number                    account_number
,   hp.party_number                       party_number
,   hp.party_name                         customer_name
,   qcpp.revenue_potential_code           revenue_potential
,   qcpp.cost_to_serve_code               cost_to_serve
,   qcpp.customer_value_code              customer_value
,   qcpp.customer_size_code               customer_size
,   qcpp.customer_rating_code             customer_rating
,   qcpp.start_date
,   qcpp.end_date
,   hp.party_id
from  qp_cust_pricing_profiles  qcpp
, hz_parties                hp
, hz_cust_accounts          hca
where qcpp.customer_party_id  = hp.party_id
and   hp.party_id             = hca.party_id

---