View: MSD_SR_PRICE_LIST_V

Product:	MSD - Demand Planning
Description:	This source view gathers the price list information defined.
Implementation/DBA Data:	APPS.MSD_SR_PRICE_LIST_V

View Text

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(QPLL.OPERAND) PRICE
, QPLL.PRODUCT_PRECEDENCE PRIORITY
, QPLH.SOURCE_SYSTEM_CODE
FROM QP_LIST_HEADERS_VL QPLH
, QP_LIST_LINES QPLL
, QP_PRICING_ATTRIBUTES QPPA
, QP_PRICE_REQ_SOURCES_V QPPRS
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_ATTRIBUTE='PRICING_ATTRIBUTE1'
AND QPPRS.REQUEST_TYPE_CODE='MSD'
AND QPPRS.SOURCE_SYSTEM_CODE=QPLH.SOURCE_SYSTEM_CODE 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
, QPLH.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED INDEX(QPPA QP_PRICING_ATTRIBUTES_N3) INDEX(MIC MTL_ITEM_CATEGORIES_N3) */ 7 ORGANIZATION_LVL_ID
, MIC.ORGANIZATION_ID 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(QPLL.OPERAND) PRICE
, QPLL.PRODUCT_PRECEDENCE PRIORITY
, QPLH.SOURCE_SYSTEM_CODE
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
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 MSI.ATO_FORECAST_CONTROL IN (1
, 2)
AND QPPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE2'
AND QPPRS.REQUEST_TYPE_CODE='MSD'
AND QPPRS.SOURCE_SYSTEM_CODE=QPLH.SOURCE_SYSTEM_CODE GROUP BY MIC.ORGANIZATION_ID
, 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
, QPLH.SOURCE_SYSTEM_CODE

Columns