ECC Advanced Pricing, Pricing Headers

Description

Categories: Enterprise Command Center

Imported from Enterprise Command Center
Data set that holds information about pricing headers
Dataset Key: qp-headers
Query Procedure: QP_ECC_UTIL_PVT.GET_ECC_QP_DATA_LOAD_INFO
Security Procedure: qp_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

SQL 65 lines

select
x.*
from
(
SELECT * FROM (
		SELECT  TO_CHAR (QLB.LIST_HEADER_ID) ECC_SPEC_ID , DFV.*
	   ,QLB.LIST_HEADER_ID
	   ,DECODE(QLB.LIST_TYPE_CODE,'PRL',QLT.NAME,'AGR',QLT.NAME,QLT.DESCRIPTION) NAME
	   ,DECODE(QLB.LIST_TYPE_CODE,'PRL',NULL,'AGR',NULL,QLT.NAME) MODIFIER_NUMBER
		 ,DECODE(QLB.ACTIVE_FLAG,'Y','Yes','N','No') ACTIVE_FLAG
	   ,DECODE(QLB.ACTIVE_FLAG,'Y','Active','N','Inactive') STATUS
       ,QLB.LIST_TYPE_CODE
       ,QLB.START_DATE_ACTIVE
       ,QLB.END_DATE_ACTIVE
			 ,DECODE (QLB.LIST_TYPE_CODE,'PRL','Price List',
																'CHARGES','Charges',
																'DLT','Discounts',
																'PRO','Promotional',
																'PML','Factors',
																'AGR','Agreement Price List',
																'SLT','Surcharge',
																'DEL','Deal') LIST_TYPE
				,DECODE (QLB.LIST_TYPE_CODE,'PRL','Price List',
																'CHARGES','Modifier',
																'DLT','Modifier',
																'PRO','Modifier',
																'PML','Factors',
																'AGR','Agreement Price List',
																'SLT','Modifier',
																'DEL','Modifier') ENTITY_TYPE
				,DECODE (QLB.LIST_TYPE_CODE,'PRL','Y',
																'CHARGES','N',
																'DLT','Y',
																'PRO','Y',
																'PML','N',
																'AGR','N',
																'SLT','Y',
																'DEL','Y') UPDATE_ALLOWED
				,QLB.LAST_UPDATE_DATE
				,QLB.ORIG_ORG_ID
				,(SELECT HOL.NAME FROM	HR_ALL_ORGANIZATION_UNITS_TL HOL WHERE HOL.ORGANIZATION_ID = QLB.ORIG_ORG_ID AND HOL.LANGUAGE=QLT.LANGUAGE) OPERATING_UNIT
				,DECODE(QLB.GLOBAL_FLAG,'Y','Yes','N','No') GLOBAL_FLAG
				,QLB.SOURCE_SYSTEM_CODE
				,QLB.PTE_CODE
				,QLB.CURRENCY_CODE
				,DECODE(QLB.AUTOMATIC_FLAG,'Y','Yes','N','No') AUTOMATIC_FLAG
				,QLB.ROUNDING_FACTOR
				,DECODE(QLB.LIST_TYPE_CODE,'PRL',QLT.DESCRIPTION,'AGR',QLT.DESCRIPTION,QLB.COMMENTS) DESCRIPTION
				,DECODE(QLB.GSA_INDICATOR,'Y','Yes','N','No') GSA_INDICATOR
				,DECODE(QLB.ASK_FOR_FLAG,'Y','Yes','N','No') ASK_FOR_FLAG
				,QLB.CURRENCY_HEADER_ID
				, (SELECT QCL.NAME FROM QP_CURRENCY_LISTS_TL QCL WHERE QCL.CURRENCY_HEADER_ID = QLB.CURRENCY_HEADER_ID AND QCL.LANGUAGE=QLT.LANGUAGE) CURRENCY_HEADER
				,DECODE(QLB.LIMIT_EXISTS_FLAG,'Y','Yes','N','No') LIMIT_EXISTS_FLAG
				,QLT.LANGUAGE
FROM    QP_LIST_HEADERS_TL QLT
      , QP_LIST_HEADERS_ALL_B QLB , (select ROW_ID "'QPH_ROW_ID'",CONTEXT_VALUE "'QPH_CONTEXT_VALUE'"
,CONCATENATED_SEGMENTS "'QPH_CONCATENATED_SEGMENTS'" from QP_LIST_HEADERS_B_DFV) DFV
WHERE  QLB.LIST_HEADER_ID = QLT.LIST_HEADER_ID
AND QLB.LIST_TYPE_CODE <> 'PML'
AND QLB.ACTIVE_FLAG='Y' and QLB.ROWID=DFV."'QPH_ROW_ID'"(+)  AND QLT.LANGUAGE in ('US')
				) PIVOT ( MAX(NAME) AS NAME,
						MAX(DESCRIPTION) AS DESCRIPTION , MAX(MODIFIER_NUMBER) AS MODIFIER_NUMBER, MAX(OPERATING_UNIT) AS OPERATING_UNIT, MAX(CURRENCY_HEADER) AS CURRENCY_HEADER FOR LANGUAGE IN ('US' "US"))
) x
where
2=2

select x.* from ( SELECT * FROM ( SELECT TO_CHAR (QLB.LIST_HEADER_ID) ECC_SPEC_ID , DFV.* ,QLB.LIST_HEADER_ID ,DECODE(QLB.LIST_TYPE_CODE,'PRL',QLT.NAME,'AGR',QLT.NAME,QLT.DESCRIPTION) NAME ,DECODE(QLB.LIST_TYPE_CODE,'PRL',NULL,'AGR',NULL,QLT.NAME) MODIFIER_NUMBER ,DECODE(QLB.ACTIVE_FLAG,'Y','Yes','N','No') ACTIVE_FLAG ,DECODE(QLB.ACTIVE_FLAG,'Y','Active','N','Inactive') STATUS ,QLB.LIST_TYPE_CODE ,QLB.START_DATE_ACTIVE ,QLB.END_DATE_ACTIVE ,DECODE (QLB.LIST_TYPE_CODE,'PRL','Price List', 'CHARGES','Charges', 'DLT','Discounts', 'PRO','Promotional', 'PML','Factors', 'AGR','Agreement Price List', 'SLT','Surcharge', 'DEL','Deal') LIST_TYPE ,DECODE (QLB.LIST_TYPE_CODE,'PRL','Price List', 'CHARGES','Modifier', 'DLT','Modifier', 'PRO','Modifier', 'PML','Factors', 'AGR','Agreement Price List', 'SLT','Modifier', 'DEL','Modifier') ENTITY_TYPE ,DECODE (QLB.LIST_TYPE_CODE,'PRL','Y', 'CHARGES','N', 'DLT','Y', 'PRO','Y', 'PML','N', 'AGR','N', 'SLT','Y', 'DEL','Y') UPDATE_ALLOWED ,QLB.LAST_UPDATE_DATE ,QLB.ORIG_ORG_ID ,(SELECT HOL.NAME FROM HR_ALL_ORGANIZATION_UNITS_TL HOL WHERE HOL.ORGANIZATION_ID = QLB.ORIG_ORG_ID AND HOL.LANGUAGE=QLT.LANGUAGE) OPERATING_UNIT ,DECODE(QLB.GLOBAL_FLAG,'Y','Yes','N','No') GLOBAL_FLAG ,QLB.SOURCE_SYSTEM_CODE ,QLB.PTE_CODE ,QLB.CURRENCY_CODE ,DECODE(QLB.AUTOMATIC_FLAG,'Y','Yes','N','No') AUTOMATIC_FLAG ,QLB.ROUNDING_FACTOR ,DECODE(QLB.LIST_TYPE_CODE,'PRL',QLT.DESCRIPTION,'AGR',QLT.DESCRIPTION,QLB.COMMENTS) DESCRIPTION ,DECODE(QLB.GSA_INDICATOR,'Y','Yes','N','No') GSA_INDICATOR ,DECODE(QLB.ASK_FOR_FLAG,'Y','Yes','N','No') ASK_FOR_FLAG ,QLB.CURRENCY_HEADER_ID , (SELECT QCL.NAME FROM QP_CURRENCY_LISTS_TL QCL WHERE QCL.CURRENCY_HEADER_ID = QLB.CURRENCY_HEADER_ID AND QCL.LANGUAGE=QLT.LANGUAGE) CURRENCY_HEADER ,DECODE(QLB.LIMIT_EXISTS_FLAG,'Y','Yes','N','No') LIMIT_EXISTS_FLAG ,QLT.LANGUAGE FROM QP_LIST_HEADERS_TL QLT , QP_LIST_HEADERS_ALL_B QLB , (select ROW_ID "'QPH_ROW_ID'",CONTEXT_VALUE "'QPH_CONTEXT_VALUE'" ,CONCATENATED_SEGMENTS "'QPH_CONCATENATED_SEGMENTS'" from QP_LIST_HEADERS_B_DFV) DFV WHERE QLB.LIST_HEADER_ID = QLT.LIST_HEADER_ID AND QLB.LIST_TYPE_CODE <> 'PML' AND QLB.ACTIVE_FLAG='Y' and QLB.ROWID=DFV."'QPH_ROW_ID'"(+) AND QLT.LANGUAGE in ('US') ) PIVOT ( MAX(NAME) AS NAME, MAX(DESCRIPTION) AS DESCRIPTION , MAX(MODIFIER_NUMBER) AS MODIFIER_NUMBER, MAX(OPERATING_UNIT) AS OPERATING_UNIT, MAX(CURRENCY_HEADER) AS CURRENCY_HEADER FOR LANGUAGE IN ('US' "US")) ) x where 2=2

Description

SQL 65 lines

Find out more

Features

Download

FAQ