VIEW: APPS.MSD_SR_PRICE_LIST_V

Source

View Text - Preformatted

SELECT /*+ Ordered index(qppa QP_PRICING_ATTRIBUTES_N3) */ 29 ORGANIZATION_LVL_ID, -2 ORGANIZATION_LVL_PK, 1 PRODUCT_LVL_ID, to_number(qppa.PRODUCT_ATTR_VALUE) PRODUCT_LVL_PK, 33 SALESCHANNEL_LVL_ID, -5 SALESCHANNEL_LVL_PK, 32 SALES_REP_LVL_ID, -4 SALES_REP_LVL_PK, 30 GEOGRAPHY_LVL_ID, -3 GEOGRAPHY_LVL_PK, 0 USER_DEFINED1_LVL_ID, Null USER_DEFINED1_LVL_PK, 0 USER_DEFINED2_LVL_ID, Null USER_DEFINED2_LVL_PK, qplh.Name PRICE_LIST_NAME, greatest(nvl(qpll.start_date_active,qplh.start_date_active), nvl(qplh.start_date_active,qpll.start_date_active)) START_DATE, Least(nvl(qpll.End_date_active,qplh.End_date_active), nvl(qplh.End_date_active,qpll.End_date_active)) End_DATE, AVG(round(msd_sr_util.convert_global_amt(qplh.currency_code, nvl(least(nvl(qpll.end_date_active, qplh.end_date_active), nvl(qplh.end_date_active, qpll.end_date_active)), sysdate)) * ( qpll.Operand / msd_sr_util.uom_conv(qppa.product_uom_code, msi.inventory_item_id)), nvl(-(qplh.rounding_factor),2))) PRICE, qpll.PRODUCT_PRECEDENCE PRIORITY, qpll.PRIMARY_UOM_FLAG, qppa.PRODUCT_UOM_CODE, qplh.source_system_code, 40 DEMAND_CLASS_LVL_ID, -6 SR_DEMAND_CLASS_LVL_PK FROM qp_list_headers_vl qplh, qp_list_lines qpll, qp_pricing_attributes qppa, qp_price_req_sources_v qpprs, mtl_system_items msi, msd_setup_parameters msp, msd_setup_parameters morg, msd_setup_parameters msp2 WHERE qplh.list_type_code='PRL' and qplh.active_flag='Y' and qplh.list_header_id=qpll.list_header_id and qpll.list_line_type_code='PLL' and qpll.arithmetic_operator='UNIT_PRICE' and qpll.list_line_id=qppa.list_line_id and qpprs.request_type_code='MSD' and qpprs.source_system_code=qplh.source_system_code and qppa.PRICING_ATTRIBUTE_CONTEXT is null and qppa.product_attribute_context='ITEM' and qppa.product_attribute='PRICING_ATTRIBUTE1' and qppa.PRODUCT_ATTR_VALUE = msi.inventory_item_id and morg.parameter_name = 'MSD_MASTER_ORG' and morg.parameter_value = msi.organization_id and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(msi.organization_id, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(msi.organization_id, msi.inventory_item_id, 2) ), 1), decode( nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2 ), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 group by 29, to_number(qppa.PRODUCT_ATTR_VALUE), qplh.Name, greatest(nvl(qpll.start_date_active,qplh.start_date_active), nvl(qplh.start_date_active,qpll.start_date_active)), Least(nvl(qpll.End_date_active,qplh.End_date_active), nvl(qplh.End_date_active,qpll.End_date_active)), qpll.PRODUCT_PRECEDENCE, qpll.PRIMARY_UOM_FLAG, qppa.PRODUCT_UOM_CODE, qplh.source_system_code union all select /*+ Ordered index(qppa QP_PRICING_ATTRIBUTES_N3) index(mic MTL_ITEM_CATEGORIES_N3) */ 29 ORGANIZATION_LVL_ID, -2 ORGANIZATION_LVL_PK, 1 PRODUCT_LVL_ID, nvl(mic.inventory_item_id,qppa.PRODUCT_ATTR_VALUE) PRODUCT_LVL_PK, 33 SALESCHANNEL_LVL_ID, -5 SALESCHANNEL_LVL_PK, 32 SALES_REP_LVL_ID, -4 SALES_REP_LVL_PK, 30 GEOGRAPHY_LVL_ID, -3 GEOGRAPHY_LVL_PK, 0 USER_DEFINED1_LVL_ID, Null USER_DEFINED1_LVL_PK, 0 USER_DEFINED2_LVL_ID, Null USER_DEFINED2_LVL_PK, qplh.Name PRICE_LIST_NAME, greatest(nvl(qpll.start_date_active,qplh.start_date_active), nvl(qplh.start_date_active,qpll.start_date_active)) START_DATE, Least(nvl(qpll.End_date_active,qplh.End_date_active), nvl(qplh.End_date_active,qpll.End_date_active)) End_DATE, AVG(round(msd_sr_util.convert_global_amt(qplh.currency_code, nvl(least(nvl(qpll.end_date_active, qplh.end_date_active), nvl(qplh.end_date_active, qpll.end_date_active)), sysdate)) * ( qpll.Operand / msd_sr_util.uom_conv(qppa.product_uom_code, msi.inventory_item_id)),nvl(-(qplh.rounding_factor),2))) PRICE, qpll.PRODUCT_PRECEDENCE PRIORITY, qpll.PRIMARY_UOM_FLAG, qppa.PRODUCT_UOM_CODE, qplh.source_system_code, 40 DEMAND_CLASS_LVL_ID, -6 SR_DEMAND_CLASS_LVL_PK FROM qp_list_headers_vl qplh, qp_list_lines qpll, qp_pricing_attributes qppa, mtl_item_categories mic, mtl_system_items msi, qp_price_req_sources_v qpprs, msd_setup_parameters msp2, msd_setup_parameters msp, msd_setup_parameters morg, msd_app_instance_orgs vorg WHERE qplh.list_type_code='PRL' and qplh.active_flag='Y' and qplh.list_header_id=qpll.list_header_id and qpll.list_line_type_code='PLL' and qpll.arithmetic_operator='UNIT_PRICE' and qpll.list_line_id=qppa.list_line_id and qppa.PRICING_ATTRIBUTE_CONTEXT is null and qppa.PRODUCT_ATTRIBUTE_CONTEXT='ITEM' and qppa.PRODUCT_ATTR_VALUE = mic.CATEGORY_ID and msi.inventory_item_id = mic.inventory_item_id and msi.organization_id = mic.organization_id and qppa.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE2' and qpprs.request_type_code='MSD' and qpprs.source_system_code=qplh.source_system_code and msp.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and msp2.parameter_name = 'MSD_PLANNING_PERCENTAGE' and morg.parameter_name = 'MSD_MASTER_ORG' and morg.parameter_value = msi.organization_id and decode( nvl(msp.parameter_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value, '1'), '4', msd_sr_util.is_product_family_forecastable(msi.organization_id, msi.inventory_item_id, 1), msd_sr_util.is_product_family_forecastable(msi.organization_id, msi.inventory_item_id, 2) ), 1), decode( nvl(msi.ato_forecast_control,3), 3, decode(nvl(msp2.parameter_value,'1'), '4', MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(msi.inventory_item_id ), 2), 1) ) = 1 and decode( nvl(msp2.parameter_value, '1') , '3', decode( msi.bom_item_type, 2, 2, 1), 1) = 1 and msi.organization_id = vorg.organization_id group by nvl(mic.inventory_item_id,qppa.PRODUCT_ATTR_VALUE), qplh.Name, greatest(nvl(qpll.start_date_active,qplh.start_date_active), nvl(qplh.start_date_active,qpll.start_date_active)), Least(nvl(qpll.End_date_active,qplh.End_date_active), nvl(qplh.End_date_active,qpll.End_date_active)), qpll.PRODUCT_PRECEDENCE, qpll.PRIMARY_UOM_FLAG, qppa.PRODUCT_UOM_CODE, qplh.source_system_code

View Text - HTML Formatted

SELECT /*+ ORDERED INDEX(QPPA QP_PRICING_ATTRIBUTES_N3) */ 29 ORGANIZATION_LVL_ID
, -2 ORGANIZATION_LVL_PK
, 1 PRODUCT_LVL_ID
, TO_NUMBER(QPPA.PRODUCT_ATTR_VALUE) PRODUCT_LVL_PK
, 33 SALESCHANNEL_LVL_ID
, -5 SALESCHANNEL_LVL_PK
, 32 SALES_REP_LVL_ID
, -4 SALES_REP_LVL_PK
, 30 GEOGRAPHY_LVL_ID
, -3 GEOGRAPHY_LVL_PK
, 0 USER_DEFINED1_LVL_ID
, NULL USER_DEFINED1_LVL_PK
, 0 USER_DEFINED2_LVL_ID
, NULL USER_DEFINED2_LVL_PK
, QPLH.NAME PRICE_LIST_NAME
, GREATEST(NVL(QPLL.START_DATE_ACTIVE
, QPLH.START_DATE_ACTIVE)
, NVL(QPLH.START_DATE_ACTIVE
, QPLL.START_DATE_ACTIVE)) START_DATE
, LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE)) END_DATE
, AVG(ROUND(MSD_SR_UTIL.CONVERT_GLOBAL_AMT(QPLH.CURRENCY_CODE
, NVL(LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE))
, SYSDATE)) * ( QPLL.OPERAND / MSD_SR_UTIL.UOM_CONV(QPPA.PRODUCT_UOM_CODE
, MSI.INVENTORY_ITEM_ID))
, NVL(-(QPLH.ROUNDING_FACTOR)
, 2))) PRICE
, QPLL.PRODUCT_PRECEDENCE PRIORITY
, QPLL.PRIMARY_UOM_FLAG
, QPPA.PRODUCT_UOM_CODE
, QPLH.SOURCE_SYSTEM_CODE
, 40 DEMAND_CLASS_LVL_ID
, -6 SR_DEMAND_CLASS_LVL_PK
FROM QP_LIST_HEADERS_VL QPLH
, QP_LIST_LINES QPLL
, QP_PRICING_ATTRIBUTES QPPA
, QP_PRICE_REQ_SOURCES_V QPPRS
, MTL_SYSTEM_ITEMS MSI
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MSP2
WHERE QPLH.LIST_TYPE_CODE='PRL'
AND QPLH.ACTIVE_FLAG='Y'
AND QPLH.LIST_HEADER_ID=QPLL.LIST_HEADER_ID
AND QPLL.LIST_LINE_TYPE_CODE='PLL'
AND QPLL.ARITHMETIC_OPERATOR='UNIT_PRICE'
AND QPLL.LIST_LINE_ID=QPPA.LIST_LINE_ID
AND QPPRS.REQUEST_TYPE_CODE='MSD'
AND QPPRS.SOURCE_SYSTEM_CODE=QPLH.SOURCE_SYSTEM_CODE
AND QPPA.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND QPPA.PRODUCT_ATTRIBUTE_CONTEXT='ITEM'
AND QPPA.PRODUCT_ATTRIBUTE='PRICING_ATTRIBUTE1'
AND QPPA.PRODUCT_ATTR_VALUE = MSI.INVENTORY_ITEM_ID
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND MORG.PARAMETER_VALUE = MSI.ORGANIZATION_ID
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2 )
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1 GROUP BY 29
, TO_NUMBER(QPPA.PRODUCT_ATTR_VALUE)
, QPLH.NAME
, GREATEST(NVL(QPLL.START_DATE_ACTIVE
, QPLH.START_DATE_ACTIVE)
, NVL(QPLH.START_DATE_ACTIVE
, QPLL.START_DATE_ACTIVE))
, LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE))
, QPLL.PRODUCT_PRECEDENCE
, QPLL.PRIMARY_UOM_FLAG
, QPPA.PRODUCT_UOM_CODE
, QPLH.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED INDEX(QPPA QP_PRICING_ATTRIBUTES_N3) INDEX(MIC MTL_ITEM_CATEGORIES_N3) */ 29 ORGANIZATION_LVL_ID
, -2 ORGANIZATION_LVL_PK
, 1 PRODUCT_LVL_ID
, NVL(MIC.INVENTORY_ITEM_ID
, QPPA.PRODUCT_ATTR_VALUE) PRODUCT_LVL_PK
, 33 SALESCHANNEL_LVL_ID
, -5 SALESCHANNEL_LVL_PK
, 32 SALES_REP_LVL_ID
, -4 SALES_REP_LVL_PK
, 30 GEOGRAPHY_LVL_ID
, -3 GEOGRAPHY_LVL_PK
, 0 USER_DEFINED1_LVL_ID
, NULL USER_DEFINED1_LVL_PK
, 0 USER_DEFINED2_LVL_ID
, NULL USER_DEFINED2_LVL_PK
, QPLH.NAME PRICE_LIST_NAME
, GREATEST(NVL(QPLL.START_DATE_ACTIVE
, QPLH.START_DATE_ACTIVE)
, NVL(QPLH.START_DATE_ACTIVE
, QPLL.START_DATE_ACTIVE)) START_DATE
, LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE)) END_DATE
, AVG(ROUND(MSD_SR_UTIL.CONVERT_GLOBAL_AMT(QPLH.CURRENCY_CODE
, NVL(LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE))
, SYSDATE)) * ( QPLL.OPERAND / MSD_SR_UTIL.UOM_CONV(QPPA.PRODUCT_UOM_CODE
, MSI.INVENTORY_ITEM_ID))
, NVL(-(QPLH.ROUNDING_FACTOR)
, 2))) PRICE
, QPLL.PRODUCT_PRECEDENCE PRIORITY
, QPLL.PRIMARY_UOM_FLAG
, QPPA.PRODUCT_UOM_CODE
, QPLH.SOURCE_SYSTEM_CODE
, 40 DEMAND_CLASS_LVL_ID
, -6 SR_DEMAND_CLASS_LVL_PK
FROM QP_LIST_HEADERS_VL QPLH
, QP_LIST_LINES QPLL
, QP_PRICING_ATTRIBUTES QPPA
, MTL_ITEM_CATEGORIES MIC
, MTL_SYSTEM_ITEMS MSI
, QP_PRICE_REQ_SOURCES_V QPPRS
, MSD_SETUP_PARAMETERS MSP2
, MSD_SETUP_PARAMETERS MSP
, MSD_SETUP_PARAMETERS MORG
, MSD_APP_INSTANCE_ORGS VORG
WHERE QPLH.LIST_TYPE_CODE='PRL'
AND QPLH.ACTIVE_FLAG='Y'
AND QPLH.LIST_HEADER_ID=QPLL.LIST_HEADER_ID
AND QPLL.LIST_LINE_TYPE_CODE='PLL'
AND QPLL.ARITHMETIC_OPERATOR='UNIT_PRICE'
AND QPLL.LIST_LINE_ID=QPPA.LIST_LINE_ID
AND QPPA.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND QPPA.PRODUCT_ATTRIBUTE_CONTEXT='ITEM'
AND QPPA.PRODUCT_ATTR_VALUE = MIC.CATEGORY_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND QPPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE2'
AND QPPRS.REQUEST_TYPE_CODE='MSD'
AND QPPRS.SOURCE_SYSTEM_CODE=QPLH.SOURCE_SYSTEM_CODE
AND MSP.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND MSP2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND MORG.PARAMETER_VALUE = MSI.ORGANIZATION_ID
AND DECODE( NVL(MSP.PARAMETER_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, 1)
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, 2) )
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(MSP2.PARAMETER_VALUE
, '1')
, '4'
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL(MSI.INVENTORY_ITEM_ID )
, 2)
, 1) ) = 1
AND DECODE( NVL(MSP2.PARAMETER_VALUE
, '1')
, '3'
, DECODE( MSI.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND MSI.ORGANIZATION_ID = VORG.ORGANIZATION_ID GROUP BY NVL(MIC.INVENTORY_ITEM_ID
, QPPA.PRODUCT_ATTR_VALUE)
, QPLH.NAME
, GREATEST(NVL(QPLL.START_DATE_ACTIVE
, QPLH.START_DATE_ACTIVE)
, NVL(QPLH.START_DATE_ACTIVE
, QPLL.START_DATE_ACTIVE))
, LEAST(NVL(QPLL.END_DATE_ACTIVE
, QPLH.END_DATE_ACTIVE)
, NVL(QPLH.END_DATE_ACTIVE
, QPLL.END_DATE_ACTIVE))
, QPLL.PRODUCT_PRECEDENCE
, QPLL.PRIMARY_UOM_FLAG
, QPPA.PRODUCT_UOM_CODE
, QPLH.SOURCE_SYSTEM_CODE