ECC Advanced Pricing, Pricing Lines

select
x.*
from
(
SELECT * FROM (  select qpll.* , dfv.* from ( SELECT  qpl.rowid row_id,TO_CHAR (QPL.LIST_HEADER_ID)|| '-'|| TO_CHAR(QPL.LIST_LINE_ID) ECC_SPEC_ID
,QPL.LIST_HEADER_ID LIST_HEADER_ID
 ,DECODE(QPH.LIST_TYPE_CODE,'PRL',QPTL.NAME,'AGR',QPTL.NAME,QPTL.DESCRIPTION) NAME
,QPTL.LANGUAGE
,QPL.LIST_LINE_ID
,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'LIST_LINE_TYPE_CODE' AND QPLTC.LOOKUP_CODE = QPL.LIST_LINE_TYPE_CODE) LIST_LINE_TYPE_CODE
,QPL.START_DATE_ACTIVE
,QPL.END_DATE_ACTIVE
,DECODE(QPL.AUTOMATIC_FLAG,'Y','Yes','N','No') AUTOMATIC_FLAG
,DECODE(QPH.LIST_TYPE_CODE,'PRL',NULL,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'MODIFIER_LEVEL_CODE' AND QPLTC.LOOKUP_CODE = QPL.MODIFIER_LEVEL_CODE)) MODIFIER_LEVEL_CODE
, nvl(QPL.PRICE_BY_FORMULA_ID,QPL.GENERATE_USING_FORMULA_ID) PRICE_BY_FORMULA_ID
,QPL.LIST_PRICE
,DECODE(QPL.PRIMARY_UOM_FLAG,'Y','Yes','N','No') PRIMARY_UOM_FLAG
,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'ARITHMETIC_OPERATOR' AND QPLTC.LOOKUP_CODE = QPL.ARITHMETIC_OPERATOR) ARITHMETIC_OPERATOR
,QPL.OPERAND
,DECODE(QPL.OVERRIDE_FLAG,'Y','Yes','N','No') OVERRIDE_FLAG
,QPL.LIST_LINE_NO
,DECODE(QPL.ACCRUAL_FLAG,'Y','Yes','N','No') ACCRUAL_FLAG
,QPL.INCOMPATIBILITY_GRP_CODE
,QPL.PRODUCT_PRECEDENCE
,(SELECT NAME FROM QP_PRICING_PHASES WHERE PRICING_PHASE_ID = QPL.PRICING_PHASE_ID) PRICING_PHASE
,(SELECT NAME FROM QP_PRICE_FORMULAS_VL QPF WHERE QPF.PRICE_FORMULA_ID =  nvl(QPL.PRICE_BY_FORMULA_ID,QPL.GENERATE_USING_FORMULA_ID)) PRICE_BY_FORMULA
,DECODE (QPA.PRODUCT_ATTRIBUTE
               ,'PRICING_ATTRIBUTE1'
               ,'Item Number'
               ,'PRICING_ATTRIBUTE2'
               ,'Item Category'
			   , 'All Items') Product_attribute
,(CASE WHEN QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1' THEN (SELECT MTLV.CONCATENATED_SEGMENTS FROM MTL_SYSTEM_ITEMS_B_KFV
MTLV WHERE MTLV.INVENTORY_ITEM_ID = QPA.PRODUCT_ATTR_VALUE AND ROWNUM <2 )
					ELSE (CASE WHEN QPA.PRODUCT_ATTRIBUTE ='PRICING_ATTRIBUTE2' THEN (SELECT QPIC.CATEGORY_NAME FROM QP_ITEM_CATEGORIES_V QPIC WHERE QPIC.CATEGORY_ID = QPA.PRODUCT_ATTR_VALUE AND ROWNUM <2) ELSE 'All' END) END) PRODUCT_ATTR_VALUE
,QPL.LAST_UPDATE_DATE
,DECODE(QPL.LIMIT_EXISTS_FLAG,'Y','Yes','N','No') LIMIT_EXISTS_FLAG
,QPA.PRODUCT_UOM_CODE
FROM QP_LIST_LINES QPL , QP_PRICING_ATTRIBUTES QPA, QP_LIST_HEADERS_ALL_B QPH, QP_LIST_HEADERS_TL QPTL
where QPL.LIST_HEADER_ID=QPA.LIST_HEADER_ID
AND QPH.LIST_HEADER_ID=QPTL.LIST_HEADER_ID
AND QPL.LIST_LINE_ID=QPA.LIST_LINE_ID
and (( QPA.ROWID = (    SELECT  QPPR.ROWID
                        FROM    QP_PRICING_ATTRIBUTES QPPR
                        WHERE   QPA.LIST_LINE_ID = LIST_LINE_ID
						AND     QPA.LIST_HEADER_ID = LIST_HEADER_ID
                        AND     PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
                        AND     PRODUCT_ATTRIBUTE in ('PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE3')
												AND ROWNUM < 2
                        )
	))
AND QPH.LIST_HEADER_ID=QPL.LIST_HEADER_ID
AND QPH.LIST_TYPE_CODE <> 'PML'
AND QPH.ACTIVE_FLAG='Y'  UNION ALL  SELECT /*+ index(qpl QP_LIST_LINES_N3) */ qpl.rowid row_id, TO_CHAR (QPL.LIST_HEADER_ID)|| '-'|| TO_CHAR(QPL.LIST_LINE_ID) ECC_SPEC_ID
,QPL.LIST_HEADER_ID LIST_HEADER_ID
 ,DECODE(QPH.LIST_TYPE_CODE,'PRL',QPTL.NAME,'AGR',QPTL.NAME,QPTL.DESCRIPTION) NAME
,QPTL.LANGUAGE
,QPL.LIST_LINE_ID
,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'LIST_LINE_TYPE_CODE' AND QPLTC.LOOKUP_CODE = QPL.LIST_LINE_TYPE_CODE) LIST_LINE_TYPE_CODE
,QPL.START_DATE_ACTIVE
,QPL.END_DATE_ACTIVE
,DECODE(QPL.AUTOMATIC_FLAG,'Y','Yes','N','No') AUTOMATIC_FLAG
,DECODE(QPH.LIST_TYPE_CODE,'PRL',NULL,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'MODIFIER_LEVEL_CODE' AND QPLTC.LOOKUP_CODE = QPL.MODIFIER_LEVEL_CODE)) MODIFIER_LEVEL_CODE
, nvl(QPL.PRICE_BY_FORMULA_ID,QPL.GENERATE_USING_FORMULA_ID) PRICE_BY_FORMULA_ID
,QPL.LIST_PRICE
,DECODE(QPL.PRIMARY_UOM_FLAG,'Y','Yes','N','No') PRIMARY_UOM_FLAG
,(SELECT MEANING FROM QP_LOOKUPS QPLTC WHERE QPLTC.LOOKUP_TYPE = 'ARITHMETIC_OPERATOR' AND QPLTC.LOOKUP_CODE = QPL.ARITHMETIC_OPERATOR) ARITHMETIC_OPERATOR
,QPL.OPERAND
,DECODE(QPL.OVERRIDE_FLAG,'Y','Yes','N','No') OVERRIDE_FLAG
,QPL.LIST_LINE_NO
,DECODE(QPL.ACCRUAL_FLAG,'Y','Yes','N','No') ACCRUAL_FLAG
,QPL.INCOMPATIBILITY_GRP_CODE
,QPL.PRODUCT_PRECEDENCE
,(SELECT NAME FROM QP_PRICING_PHASES WHERE PRICING_PHASE_ID = QPL.PRICING_PHASE_ID) PRICING_PHASE
,(SELECT NAME FROM QP_PRICE_FORMULAS_VL QPF WHERE QPF.PRICE_FORMULA_ID =  nvl(QPL.PRICE_BY_FORMULA_ID,QPL.GENERATE_USING_FORMULA_ID)) PRICE_BY_FORMULA
,NULL Product_attribute
,NULL PRODUCT_ATTR_VALUE
,QPL.LAST_UPDATE_DATE
,DECODE(QPL.LIMIT_EXISTS_FLAG,'Y','Yes','N','No') LIMIT_EXISTS_FLAG
,QPL.PRODUCT_UOM_CODE
FROM QP_LIST_LINES QPL ,QP_LIST_HEADERS_ALL_B QPH,QP_LIST_HEADERS_TL QPTL
where QPH.LIST_HEADER_ID=QPL.LIST_HEADER_ID
and QPH.LIST_HEADER_ID=QPTL.LIST_HEADER_ID
AND QPH.LIST_TYPE_CODE <> 'PML'
AND QPH.ACTIVE_FLAG='Y'
AND QPL.QUALIFICATION_IND In (0,2,8,10)
and QPL.LIST_LINE_TYPE_CODE not in ('PLL')  )QPLL , (select ROW_ID "'QPL_ROW_ID'",CONTEXT_VALUE "'QPL_CONTEXT_VALUE'"
,CONCATENATED_SEGMENTS "'QPL_CONCATENATED_SEGMENTS'" from QP_LIST_LINES_DFV) dfv where qpll.row_id = dfv."'QPL_ROW_ID'"(+)  AND LANGUAGE in ('US')
				) PIVOT ( MAX(NAME) AS NAME
						FOR LANGUAGE IN ('US' "US"))
) x
where
2=2