ECC Advanced Pricing, Pricing Headers

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, 'Qph Row Id', 'Qph Context Value', 'Qph Concatenated Segments', List Header Id, Active Flag, Status, List Type Code, Start Date Active, End Date Active ...
Imported from Enterprise Command Center
Description: 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
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_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
Parameter Name SQL text Validation
Operating Unit
x.orig_org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV