Skip to main content

Catalogo alp_cat_qp_discount_list

1. Modulo​

Precios y Descuentos (QP)

2. Descripción​

Listados de descuentos comerciales autorizados, reglas de bonificación y promociones.

3. Columnas​

#Nombre de ColumnaDescripción
1discount_list_idIdentificador único concatenado (list | item | uom | date | account).
2discount_listNombre de la lista de descuentos.
3descript_discount_listDescripción de la lista de descuentos.
4line_typeTipo de línea de pedido (ORA_QP_ORDER_LINE_TYPES).
5itemNúmero del artículo.
6item_descriptionDescripción del artículo.
7pricing_uomUnidad de medida para el precio.
8item_levelNivel del ítem (ORA_QP_ITEM_LEVELS).
9rule_nameNombre de la regla de precios.
10rule_typeTipo de regla (ORA_QP_PRICING_RULE_TYPES).
11rule_start_dateFecha de inicio de vigencia de la regla.
12rule_end_dateFecha de fin de vigencia de la regla.
13price_typeCódigo del tipo de precio.
14charge_typeCódigo del tipo de cargo.
15charge_subtypeCódigo del subtipo de cargo.
16cuenta_clienteNúmero de cuenta del cliente.
17adjustment_typeTipo de ajuste aplicado.
18adjustment_amountMonto del ajuste.

4. Querys​

with alp_descuentos as
( select  qdlt.name                                         discount_list
    ,   qdlt.description                                  descript_discount_list
    ,   oqolt.meaning                                     line_type
    ,   esib.itembasepeoitemnumber                        item
    ,   esit.itemtranslationpeodescription                item_description        
    ,   qdli.pricing_uom_code                             pricing_uom
    ,   oqil.meaning                                      item_level
    ,   qptt.name                                         rule_name
    ,   oqprt.meaning                                     rule_type
    ,   qptb.start_date                                   rule_start_date
    ,   qptb.end_date                                     rule_end_date
    ,   qptb.price_type_code                              price_type
    ,   qptb.charge_type_code                             charge_type
    ,   qptb.charge_subtype_code                          charge_subtype
    ,   hca.accountnumber                                 cuenta_cliente
    ,   qmr.value_string2                                 adjustment_type
    ,   qmr.value_number3                                 adjustment_amount
    ,   row_number()
        over  ( partition by  qdlt.name 
                    ,         esib.itembasepeoitemnumber  
                    ,         qdli.pricing_uom_code
                    ,         qptb.start_date
                    ,         hca.accountnumber
                    ,         qmr.value_string2
                order by  qmr.dynamic_matrix_rule_id)     reg
from        cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_tl                        qdlt
  join      cat_master_oracle_fusion.sch_gold_layer.qp_discount_list_items                      qdli
    on  qdlt.discount_list_id = qdli.discount_list_id
  join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo                 esib
    on  qdli.item_id  = esib.itembasepeoinventoryitemid
  join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo      esit
    on    esib.itembasepeoorganizationid  = esit.itemtranslationpeoorganizationid
      and esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
      and esit.itemtranslationpeolanguage = 'E'
  join      cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo  iop
    on  esib.itembasepeoorganizationid  = iop.organizationid
  join      cat_master_oracle_fusion.sch_gold_layer.qp_pricing_terms_b                          qptb
    on  qdli.discount_list_item_id  = qptb.parent_entity_key_column1
  join      cat_master_oracle_fusion.sch_gold_layer.qp_pricing_terms_tl                         qptt
    on    qptb.pricing_term_id  = qptt.pricing_term_id
      and qptt.language         = 'E'
  join      cat_master_oracle_fusion.sch_gold_layer.qp_matrix_rules                             qmr
    on  qptb.attribute_pricing_matrix_id  = qmr.dynamic_matrix_id 
  left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo       oqolt
    on    qdli.line_type_code     = oqolt.lookupcode
      and oqolt.lookuptype        = 'ORA_QP_ORDER_LINE_TYPES' 
      and oqolt.language          = 'E'
      and oqolt.viewapplicationid = 0
  left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo       oqil
    on    qdli.item_level_code    = oqil.lookupcode
      and oqil.lookuptype         = 'ORA_QP_ITEM_LEVELS' 
      and oqil.language           = 'E'
      and oqil.viewapplicationid  = 0
  left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo       oqprt
    on    qptb.pricing_rule_type_code = oqprt.lookupcode
      and oqprt.lookuptype            = 'ORA_QP_PRICING_RULE_TYPES' 
      and oqprt.language              = 'E'
      and oqprt.viewapplicationid     = 0
  left join cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountextractpvo        hca
    on  qmr.value_number1 = hca.custaccountid
where   iop.organizationcode            =   'OMA'
and     qdlt.language                   =   'E')
select  CONCAT_WS('|', discount_list, item, pricing_uom, rule_start_date, cuenta_cliente) discount_list_id
,   discount_list
,   descript_discount_list
,   line_type
,   item
,   item_description
,   pricing_uom
,   item_level
,   rule_name
,   rule_type
,   rule_start_date
,   rule_end_date
,   price_type
,   charge_type
,   charge_subtype
,   cuenta_cliente
,   adjustment_type
,   adjustment_amount
from    alp_descuentos
where   reg = 1