Catalogo alp_cat_qp_pricing_assignments_strategy

1. Modulo

Precios y Descuentos (QP)

---

2. Descripción

Reglas de asignación estratégica de precios basadas en acuerdos comerciales específicos.

---

3. Columnas

#	Nombre de Columna	Descripción
1	dynamic_matrix_rule_id	Identificador único de la regla dentro de la matriz dinámica de precios.
2	dynamic_matrix	Nombre de la matriz dinámica de precios configurada en Oracle.
3	dynamic_matrix_class	Nombre de la clase o categoría a la que pertenece la matriz dinámica.
4	pricing_segment	Segmento de precios asociado a la regla de asignación.
5	business_unit	Nombre de la Unidad de Negocio (Business Unit) asignada.
6	canal_ventas	Canal de ventas al que aplica la estrategia de precios.
7	subcanal_ventas	Subcanal de ventas detallado al que aplica la estrategia.
8	pricing_strategy	Nombre de la estrategia de precios asignada para esta combinación (en español).

---

4. Querys

Databricks DEV

select  qmr.dynamic_matrix_rule_id
,   qm.dynamic_matrix_name          dynamic_matrix
,   qmct.dynamic_matrix_class_name  dynamic_matrix_class
,   qmr.value_string1               pricing_segment
,   haou.name                       business_unit
,   qmr.value_string2               canal_ventas
,   qmr.value_string3               subcanal_ventas
,   qpst.name                       pricing_strategy
from        cat_master_oracle_fusion.sch_gold_layer.qp_matrices                                       qm
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_rules                                   qmr
on  qm.dynamic_matrix_id  = qmr.dynamic_matrix_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units                         haou
on  qmr.value_number2 = haou.organization_id
join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo  qpst
on  qmr.value_number5 = qpst.pricingstrategyid
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_classes_tl                              qmct
on  qm.dynamic_matrix_class_id = qmct.dynamic_matrix_class_id
where   qpst.language = 'E'
and     qmct.language = 'E'

Databricks UAT

select  qmr.dynamic_matrix_rule_id
,   qm.dynamic_matrix_name          dynamic_matrix
,   qmct.dynamic_matrix_class_name  dynamic_matrix_class
,   qmr.value_string1               pricing_segment
,   haou.name                       business_unit
,   qmr.value_string2               canal_ventas
,   qmr.value_string3               subcanal_ventas
,   qpst.name                       pricing_strategy
from        cat_master_oracle_fusion.sch_gold_layer.qp_matrices_stg                                       qm
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_rules_stg                                   qmr
on  qm.dynamic_matrix_id  = qmr.dynamic_matrix_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg                         haou
on  qmr.value_number2 = haou.organization_id
join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo_stg  qpst
on  qmr.value_number5 = qpst.pricingstrategyid
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_classes_tl_stg                              qmct
on  qm.dynamic_matrix_class_id = qmct.dynamic_matrix_class_id
where   qpst.language = 'E'
and     qmct.language = 'E'

Databricks PROD

select  qmr.dynamic_matrix_rule_id
,   qm.dynamic_matrix_name          dynamic_matrix
,   qmct.dynamic_matrix_class_name  dynamic_matrix_class
,   qmr.value_string1               pricing_segment
,   haou.name                       business_unit
,   qmr.value_string2               canal_ventas
,   qmr.value_string3               subcanal_ventas
,   qpst.name                       pricing_strategy
from        cat_master_oracle_fusion.sch_gold_layer.qp_matrices_prd                                       qm
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_rules_prd                                   qmr 
on  qm.dynamic_matrix_id  = qmr.dynamic_matrix_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd                         haou 
on  qmr.value_number2 = haou.organization_id
join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo_prd  qpst 
on  qmr.value_number5 = qpst.pricingstrategyid
join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_classes_tl_prd                              qmct 
on  qm.dynamic_matrix_class_id = qmct.dynamic_matrix_class_id
where   qpst.language = 'E'
and     qmct.language = 'E'

Oracle

SELECT  qmr.dynamic_matrix_rule_id
,   qm.dynamic_matrix_name          dynamic_matrix
,   qmct.dynamic_matrix_class_name  dynamic_matrix_class
,   qmr.value_string1               pricing_segment
,   haou.name                       business_unit
,   qmr.value_string2               canal_ventas
,   qmr.value_number3               subcanal_ventas
,   qpst.name                       pricing_strategy
FROM    qp_matrices               qm
,   qp_matrix_rules           qmr
,   qp_matrix_classes_tl      qmct
,   qp_pricing_strategies_tl  qpst
,   hr_all_organization_units haou
WHERE   qm.dynamic_matrix_id            = qmr.dynamic_matrix_id
AND     qm.dynamic_matrix_class_id      = qmct.dynamic_matrix_class_id 
AND     qmr.value_number2               = haou.organization_id(+)
AND     qmr.value_number5               = qpst.pricing_strategy_id(+)
AND     qmct.language                   = 'E'
AND     qpst.language                   = 'E'

---