Catalogo alp_cat_qp_pricing_oracle
1. Modulo
Precios y Descuentos (QP)
2. Descripción
Motor de datos de precios nativos sincronizados desde el módulo de Advanced Pricing de Oracle.
3. Columnas
| # | Nombre de Columna | Descripción |
|---|---|---|
| 1 | pricing_strategy | Nombre descriptivo de la estrategia de precios en Oracle Fusion. |
| 2 | pricing_strategy_description | Descripción detallada del propósito o alcance de la estrategia de precios. |
| 3 | business_unit | Unidad de Negocio (Business Unit) que posee y administra la estrategia. |
| 4 | status | Estado de aprobación de la estrategia (ej. Activo, Borrador, etc.). |
| 5 | default_currency | Código de la moneda por defecto configurada para la estrategia (ej. MXN, USD). |
| 6 | default_gl_conversion_type | Tipo de conversión de Contabilidad General (GL) por defecto para el tipo de cambio diario. |
| 7 | objective | Objetivo de negocio asignado a la estrategia de precios (ej. Maximize Profit). |
| 8 | start_date | Fecha de inicio de vigencia de la cabecera de la estrategia de precios. |
| 9 | end_date | Fecha de fin de vigencia de la cabecera de la estrategia de precios. |
| 10 | allow_price_list_override | Indicador (Flag) que define si se permite omitir o sobreescribir la lista de precios. |
| 11 | allow_currency_override | Indicador (Flag) que define si se permite omitir o sobreescribir la moneda configurada. |
| 12 | detail_entity_type | Categoría de la entidad detallada adjunta (ej. 'Price List' o 'Discount List'). |
| 13 | detail_entity | Nombre de la entidad detallada específica (Lista de Precios o de Descuentos). |
| 14 | detail_entity_description | Descripción de la entidad detallada específica. |
| 15 | detail_entity_business_unit | Unidad de Negocio a la que pertenece la entidad detallada específica. |
| 16 | detail_entity_currency | Moneda transaccional configurada para la entidad detallada. |
| 17 | detail_status | Estado de aprobación específico de la entidad detallada. |
| 18 | detail_start_date | Fecha de inicio de vigencia de la entidad detallada. |
| 19 | detail_end_date | Fecha de fin de vigencia de la entidad detallada. |
| 20 | strategy_start_date | Fecha de inicio de la relación entre la estrategia y la entidad detallada. |
| 21 | strategy_end_date | Fecha de fin de la relación entre la estrategia y la entidad detallada. |
| 22 | precedence | Nivel de precedencia/prioridad asignado a esta regla de detalle dentro de la estrategia. |
4. Querys
- Databricks DEV
- Databricks UAT
- Databricks PROD
- Oracle
with alp_detail_entity as
( select 'SEGMENT_PRICE_LIST' detail_entity_type_code
, 'Price List' detail_entity_type
, qplt.price_list_id detail_entity_id
, qplt.name detail_entity
, qplt.description detail_entity_description
, haou.name detail_entity_business_unit
, qpli.currencycode detail_entity_currency
, flvt.meaning detail_status
, qpli.startdate detail_start_date
, qpli.enddate detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_price_lists_tl qplt
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricelistheaderbaseextractpvo qpli
on qplt.price_list_id = qpli.pricelistid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units haou
on qpli.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo flvt
on qpli.statuscode = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qplt.language = 'E'
union all
select 'DISCOUNT_LIST' detail_entity_type_code
, 'Discount List' detail_entity_type
, qdlt.discount_list_id detail_entity_id
, qdlt.name detail_entity
, qdlt.description detail_entity_description
, haou.name detail_entity_business_unit
, qdlab.currency_code detail_entity_currency
, flvt.meaning detail_status
, qdlab.start_date detail_start_date
, qdlab.end_date detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_tl qdlt
join cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_all_b qdlab
on qdlt.discount_list_id = qdlab.discount_list_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units haou
on qdlab.org_id = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo flvt
on qdlab.status_code = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qdlt.language = 'E')
select qpst.name pricing_strategy
, qpst.description pricing_strategy_description
, haou.name business_unit
, flvoqps.meaning status
, qpsab.defaultcurrencycode default_currency
, gdct.dailyconversiontypeuserconversiontype default_gl_conversion_type
, flvomp.meaning objective
, qpsab.startdate start_date
, qpsab.enddate end_date
, qpsab.allowpricelistoverrideflag allow_price_list_override
, qpsab.allowcurrencyoverrideflag allow_currency_override
, ade.detail_entity_type
, ade.detail_entity
, ade.detail_entity_description
, ade.detail_entity_business_unit
, ade.detail_entity_currency
, ade.detail_status
, ade.detail_start_date
, ade.detail_end_date
, qpsd.start_date strategy_start_date
, qpsd.end_date strategy_end_date
, qpsd.precedence
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo qpst
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategybaseextractpvo qpsab
on qpst.pricingstrategyid = qpsab.pricingstrategyid
join cat_master_oracle_fusion.sch_gold_layer.qp_pricing_strategy_details qpsd
on qpsab.pricingstrategyid = qpsd.pricing_strategy_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units haou
on qpsab.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo flvoqps
on qpsab.statuscode = flvoqps.lookupcode
and flvoqps.language = 'E'
and flvoqps.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_dailyconversiontypeextractpvo gdct
on qpsab.defaultglconvtypecode = gdct.dailyconversiontypeconversiontype
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo flvomp
on qpsab.objectivecode = flvomp.lookupcode
and flvomp.language = 'E'
and flvomp.lookuptype = 'ORA_QP_STRATEGY_OBJECTIVES'
join alp_detail_entity ade
on qpsd.detail_entity_type_code = ade.detail_entity_type_code
and qpsd.detail_entity_id = ade.detail_entity_id
where qpst.language = 'E'
with alp_detail_entity as
( select 'SEGMENT_PRICE_LIST' detail_entity_type_code
, 'Price List' detail_entity_type
, qplt.price_list_id detail_entity_id
, qplt.name detail_entity
, qplt.description detail_entity_description
, haou.name detail_entity_business_unit
, qpli.currencycode detail_entity_currency
, flvt.meaning detail_status
, qpli.startdate detail_start_date
, qpli.enddate detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_price_lists_tl_stg qplt
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricelistheaderbaseextractpvo_stg qpli
on qplt.price_list_id = qpli.pricelistid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on qpli.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvt
on qpli.statuscode = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qplt.language = 'E'
union all
select 'DISCOUNT_LIST' detail_entity_type_code
, 'Discount List' detail_entity_type
, qdlt.discount_list_id detail_entity_id
, qdlt.name detail_entity
, qdlt.description detail_entity_description
, haou.name detail_entity_business_unit
, qdlab.currency_code detail_entity_currency
, flvt.meaning detail_status
, qdlab.start_date detail_start_date
, qdlab.end_date detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_tl_stg qdlt
join cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_all_b_stg qdlab
on qdlt.discount_list_id = qdlab.discount_list_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on qdlab.org_id = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvt
on qdlab.status_code = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qdlt.language = 'E')
select qpst.name pricing_strategy
, qpst.description pricing_strategy_description
, haou.name business_unit
, flvoqps.meaning status
, qpsab.defaultcurrencycode default_currency
, gdct.dailyconversiontypeuserconversiontype default_gl_conversion_type
, flvomp.meaning objective
, qpsab.startdate start_date
, qpsab.enddate end_date
, qpsab.allowpricelistoverrideflag allow_price_list_override
, qpsab.allowcurrencyoverrideflag allow_currency_override
, ade.detail_entity_type
, ade.detail_entity
, ade.detail_entity_description
, ade.detail_entity_business_unit
, ade.detail_entity_currency
, ade.detail_status
, ade.detail_start_date
, ade.detail_end_date
, qpsd.start_date strategy_start_date
, qpsd.end_date strategy_end_date
, qpsd.precedence
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo_stg qpst
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategybaseextractpvo_stg qpsab
on qpst.pricingstrategyid = qpsab.pricingstrategyid
join cat_master_oracle_fusion.sch_gold_layer.qp_pricing_strategy_details_stg qpsd
on qpsab.pricingstrategyid = qpsd.pricing_strategy_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on qpsab.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvoqps
on qpsab.statuscode = flvoqps.lookupcode
and flvoqps.language = 'E'
and flvoqps.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_dailyconversiontypeextractpvo_stg gdct
on qpsab.defaultglconvtypecode = gdct.dailyconversiontypeconversiontype
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvomp
on qpsab.objectivecode = flvomp.lookupcode
and flvomp.language = 'E'
and flvomp.lookuptype = 'ORA_QP_STRATEGY_OBJECTIVES'
join alp_detail_entity ade
on qpsd.detail_entity_type_code = ade.detail_entity_type_code
and qpsd.detail_entity_id = ade.detail_entity_id
where qpst.language = 'E'
with alp_detail_entity as
( select 'SEGMENT_PRICE_LIST' detail_entity_type_code
, 'Price List' detail_entity_type
, qplt.price_list_id detail_entity_id
, qplt.name detail_entity
, qplt.description detail_entity_description
, haou.name detail_entity_business_unit
, qpli.currencycode detail_entity_currency
, flvt.meaning detail_status
, qpli.startdate detail_start_date
, qpli.enddate detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_price_lists_tl_prd qplt
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricelistheaderbaseextractpvo_prd qpli
on qplt.price_list_id = qpli.pricelistid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on qpli.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvt
on qpli.statuscode = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qplt.language = 'E'
union all
select 'DISCOUNT_LIST' detail_entity_type_code
, 'Discount List' detail_entity_type
, qdlt.discount_list_id detail_entity_id
, qdlt.name detail_entity
, qdlt.description detail_entity_description
, haou.name detail_entity_business_unit
, qdlab.currency_code detail_entity_currency
, flvt.meaning detail_status
, qdlab.start_date detail_start_date
, qdlab.end_date detail_end_date
from cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_tl_prd qdlt
join cat_master_oracle_fusion.sch_gold_layer.qp_discount_lists_all_b_prd qdlab
on qdlt.discount_list_id = qdlab.discount_list_id
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on qdlab.org_id = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvt
on qdlab.status_code = flvt.lookupcode
and flvt.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
and flvt.language = 'E'
where qdlt.language = 'E')
select qpst.name pricing_strategy
, qpst.description pricing_strategy_description
, haou.name business_unit
, flvoqps.meaning status
, qpsab.defaultcurrencycode default_currency
, gdct.dailyconversiontypeuserconversiontype default_gl_conversion_type
, flvomp.meaning objective
, qpsab.startdate start_date
, qpsab.enddate end_date
, qpsab.allowpricelistoverrideflag allow_price_list_override
, qpsab.allowcurrencyoverrideflag allow_currency_override
, ade.detail_entity_type
, ade.detail_entity
, ade.detail_entity_description
, ade.detail_entity_business_unit
, ade.detail_entity_currency
, ade.detail_status
, ade.detail_start_date
, ade.detail_end_date
, qpsd.start_date strategy_start_date
, qpsd.end_date strategy_end_date
, qpsd.precedence
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategytranslationextractpvo_prd qpst
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_qp_pricingstrategybaseextractpvo_prd qpsab
on qpst.pricingstrategyid = qpsab.pricingstrategyid
join cat_master_oracle_fusion.sch_gold_layer.qp_pricing_strategy_details_prd qpsd
on qpsab.pricingstrategyid = qpsd.pricing_strategy_id
left join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on qpsab.orgid = haou.organization_id
join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvoqps
on qpsab.statuscode = flvoqps.lookupcode
and flvoqps.language = 'E'
and flvoqps.lookuptype = 'ORA_QP_APPROVAL_STATUSES'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_gl_dailyconversiontypeextractpvo_prd gdct
on qpsab.defaultglconvtypecode = gdct.dailyconversiontypeconversiontype
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvomp
on qpsab.objectivecode = flvomp.lookupcode
and flvomp.language = 'E'
and flvomp.lookuptype = 'ORA_QP_STRATEGY_OBJECTIVES'
join alp_detail_entity ade
on qpsd.detail_entity_type_code = ade.detail_entity_type_code
and qpsd.detail_entity_id = ade.detail_entity_id
where qpst.language = 'E'
WITH alp_detail_entity AS
( SELECT 'SEGMENT_PRICE_LIST' detail_entity_type_code
, 'Price List' detail_entity_type
, qplt.price_list_id detail_entity_id
, qplt.name detail_entity
, qplt.description detail_entity_description
, haou.name detail_entity_business_unit
, qpli.currency_code detail_entity_currency
, flv.meaning detail_status
, qpli.start_date detail_start_date
, qpli.end_date detail_end_date
FROM qp_price_lists_tl qplt
, qp_price_lists_all_b qpli
, hr_all_organization_units haou
, fnd_lookup_values flv
WHERE qplt.price_list_id = qpli.price_list_id
AND qpli.org_id = haou.organization_id
AND qpli.status_code = flv.lookup_code
AND flv.language = 'E'
AND qplt.language = 'E'
AND flv.lookup_type = 'ORA_QP_APPROVAL_STATUSES'
UNION ALL
SELECT 'DISCOUNT_LIST' detail_entity_type_code
, 'Discount List' detail_entity_type
, qdlt.discount_list_id detail_entity_id
, qdlt.name detail_entity
, qdlt.description detail_entity_description
, haou.name detail_entity_business_unit
, qdlab.currency_code detail_entity_currency
, flv.meaning detail_status
, qdlab.start_date detail_start_date
, qdlab.end_date detail_end_date
FROM qp_discount_lists_tl qdlt
, qp_discount_lists_all_b qdlab
, hr_all_organization_units haou
, fnd_lookup_values flv
WHERE qdlt.discount_list_id = qdlab.discount_list_id
AND qdlab.org_id = haou.organization_id
AND qdlab.status_code = flv.lookup_code
AND flv.language = 'E'
AND qdlt.language = 'E'
AND flv.lookup_type = 'ORA_QP_APPROVAL_STATUSES')
SELECT qpst.name "Pricing Strategy"
, qpst.description "Pricing Strategy Description"
, haou.name "Business Unit"
, flvoqps.meaning "Status"
, qpsab.default_currency_code "Default Currency"
, gdct.user_conversion_type "Default GL Conversion Type"
, flvomp.meaning "Objective"
, qpsab.start_date "Start Date"
, qpsab.end_date "End Date"
, qpsab.allow_price_list_override_flag "Allow price list override"
, qpsab.allow_currency_override_flag "Allow currency override"
, ade.detail_entity_type "Detail Entity Type"
, ade.detail_entity "Detail Entity List Name"
, ade.detail_entity_description "Detail Entity List Description"
, ade.detail_entity_business_unit "Detail Entity List Business Unit"
, ade.detail_entity_currency "Detail Entity List Currency"
, ade.detail_status "Detail Entity List Status"
, ade.detail_start_date "Detail Entity List Start Date"
, ade.detail_end_date "Detail Entity List End Date"
, qpsd.start_date "Strategy Start Date"
, qpsd.end_date "Strategy End Date"
, qpsd.precedence "Precedence"
FROM qp_pricing_strategies_tl qpst
, qp_pricing_strategies_all_b qpsab
, qp_pricing_strategy_details qpsd
, hr_all_organization_units haou
, fnd_lookup_values flvoqps
, gl_daily_conversion_types gdct
, fnd_lookup_values flvomp
, alp_detail_entity xx|
WHERE qpst.pricing_strategy_id = qpsab.pricing_strategy_id
AND qpsab.pricing_strategy_id = qpsd.pricing_strategy_id
AND qpsab.org_id = haou.organization_id(+)
AND qpsab.status_code = flvoqps.lookup_code
AND qpsab.default_gl_conv_type_code = gdct.conversion_type(+)
AND qpsab.objective_code = flvomp.lookup_code(+)
AND qpsd.detail_entity_type_code = ade.detail_entity_type_code
AND qpsd.detail_entity_id = ade.detail_entity_id
AND qpst.language = 'E'
AND flvoqps.language = 'E'
AND flvomp.language(+) = 'E'
AND flvoqps.lookup_type = 'ORA_QP_APPROVAL_STATUSES'
AND flvomp.lookup_type(+) = 'ORA_QP_STRATEGY_OBJECTIVES'