ECC Advanced Pricing, Pricing Lines

Description
Categories: Enterprise Command Center
Columns: Row Id, Ecc Spec Id, List Header Id, List Line Id, List Line Type Code, Start Date Active, End Date Active, Automatic Flag, Modifier Level Code, Price By Formula Id ...
Imported from Enterprise Command Center
Description: Data set that holds information about pricing lines
Dataset Key: qp-lines
Query Procedure: QP_ECC_UTIL_PVT.GET_ECC_QP_DATA_LOAD_INFO
Security Procedure: qp_ecc_datasecurity_pkg_pub
select
x.*
from
(
SELECT * FROM (  select qpll.* , dfv.* from ( SELECT  /*+ index(qph QP_LIST_HEADERS_B_N7) */ 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_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(qph QP_LIST_HEADERS_B_N7) */  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_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