ECC Advanced Pricing, Pricing Qualifiers

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Data set that holds information about pricing qualifiers
Dataset Key: qp-qualifiers
Query Procedure: QP_ECC_UTIL_PVT.GET_ECC_QP_DATA_LOAD_INFO
Security Procedure: ts
Run ECC Advanced Pricing, Pricing Qualifiers and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM (
SELECT TO_CHAR(QPQ.QUALIFIER_ID) ECC_SPEC_ID , dfv.*
	,QPQ.LIST_HEADER_ID LIST_HEADER_ID
       ,QPQ.LIST_LINE_ID LIST_LINE_ID
       ,QPQ.QUALIFIER_ID
 ,DECODE(QPH.LIST_TYPE_CODE,'PRL',QPTL.NAME,'AGR',QPTL.NAME,QPTL.DESCRIPTION) NAME
       ,QPQ.QUALIFIER_GROUPING_NO
				  ,QP_STAGE.CONTEXT_NAME QUALIFIER_CONTEXT
				  ,QP_STAGE.ATTRIBUTE_NAME QUALIFIER_ATTRIBUTE
				  ,QP_STAGE.ATTRIBUTE_VALUE_NAME QUALIFIER_ATTR_VALUE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Customer Name',QP_STAGE.ATTRIBUTE_VALUE_NAME) CUSTOMER_NAME
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Customer Class',QP_STAGE.ATTRIBUTE_VALUE_NAME) CUSTOMER_CLASS
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Ship To',QP_STAGE.ATTRIBUTE_VALUE_NAME) SHIP_TO
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Bill To',QP_STAGE.ATTRIBUTE_VALUE_NAME) BILL_TO
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Party ID',QP_STAGE.ATTRIBUTE_VALUE_NAME) PARTY_ID
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Account Type',QP_STAGE.ATTRIBUTE_VALUE_NAME) ACCOUNT_TYPE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Agreement Name',QP_STAGE.ATTRIBUTE_VALUE_NAME) AGREEMENT_NAME
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Agreement Type',QP_STAGE.ATTRIBUTE_VALUE_NAME) AGREEMENT_TYPE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Order Type',QP_STAGE.ATTRIBUTE_VALUE_NAME) ORDER_TYPE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Order Date',QP_STAGE.ATTRIBUTE_VALUE_NAME) ORDER_DATE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Line Type',QP_STAGE.ATTRIBUTE_VALUE_NAME) LINE_TYPE
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Freight Terms',QP_STAGE.ATTRIBUTE_VALUE_NAME) FREIGHT_TERMS
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Payment Terms',QP_STAGE.ATTRIBUTE_VALUE_NAME) PAYMENT_TERMS
					,DECODE(QP_STAGE.ATTRIBUTE_NAME,'Shipping Method',QP_STAGE.ATTRIBUTE_VALUE_NAME) SHIPPING_METHOD
			    ,DECODE (QPQ.ACTIVE_FLAG,'Y','ACTIVE','N','INACTIVE') ACTIVE_FLAG
		,DECODE(COMPARISON_OPERATOR_CODE ,'=','EQUAL','NOT =','NOT EQUAL','BETWEEN','BETWEEN') OPERATOR_CODE
		,QUALIFIER_PRECEDENCE
		,DECODE(QUALIFIER_DATATYPE,'N','Number','C','Character','X','Date','No Datatype') QUALIFIER_DATATYPE
		 ,DECODE (QPQ.LIST_TYPE_CODE,'PRL','Price List',
					'CHARGES','Charges',
					'DLT','Discounts',
					'PRO','Promotional',
					'PML','Factors',
					'AGR','Agreement Price List',
					'SLT','Surcharge',
					'DEL','Deal') LIST_TYPE
		,QPQ.START_DATE_ACTIVE
		,QPQ.END_DATE_ACTIVE
		,QPTL.LANGUAGE
		FROM QP_QUALIFIERS QPQ, QP_ECC_STAGING_TABLE QP_STAGE , QP_LIST_HEADERS_B QPH, QP_LIST_HEADERS_TL QPTL, (select ROW_ID "'QPQ_ROW_ID'",CONTEXT_VALUE "'QPQ_CONTEXT_VALUE'",CONCATENATED_SEGMENTS "'QPQ_CONCATENATED_SEGMENTS'" from QP_QUALIFIERS_DFV) dfv
		WHERE QPQ.QUALIFIER_CONTEXT=QP_STAGE.CONEXT_CODE
		AND QP_STAGE.ATTRIBUTE_TYPE='QUALIFIER'
		AND QPQ.QUALIFIER_ATTRIBUTE=QP_STAGE.ATTRIBUTE_CODE
		AND QPQ.QUALIFIER_ATTR_VALUE=QP_STAGE.ATTRIBUTE_VALUE_ID
		AND QPQ.LIST_HEADER_ID=QPH.LIST_HEADER_ID
		AND QPH.LIST_HEADER_ID=QPTL.LIST_HEADER_ID
		AND QPH.ACTIVE_FLAG='Y'
		AND NVL(QPH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE-NVL(FND_PROFILE.Value('QP_ECC_FULL_LOAD_DAYS'),0)
		and qpq.rowid=dfv."'QPQ_ROW_ID'"(+)   AND QPTL.LANGUAGE in ('US')
				) PIVOT ( MAX(NAME) AS NAME
						FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
Blitz Report™