VIEW: APPS.PAY_ELEMENT_SET_MEMBERS

Source

View Text - Preformatted

SELECT PES1.ELEMENT_SET_ID , PET1.ELEMENT_TYPE_ID , PET1.ELEMENT_NAME , PET1.CLASSIFICATION_ID , PET1.PROCESSING_TYPE , PET1.BUSINESS_GROUP_ID , PET1.LEGISLATION_CODE , ECLTL.CLASSIFICATION_NAME , HL.MEANING PROCESSING_TYPE_MEANING FROM PAY_ELEMENT_TYPES PET1, PAY_ELE_CLASSIFICATION_RULES PECR1, PAY_ELEMENT_SETS PES1, PAY_ELEMENT_CLASSIFICATIONS ECL, PAY_ELEMENT_CLASSIFICATIONS_TL ECLTL, HR_LOOKUPS HL WHERE ECL.Classification_id = ECLTL.classification_id AND ECLTL.LANGUAGE = userenv('LANG') AND PET1.CLASSIFICATION_ID = PECR1.CLASSIFICATION_ID AND PECR1.ELEMENT_SET_ID = PES1.ELEMENT_SET_ID AND ECL.CLASSIFICATION_ID = PET1.CLASSIFICATION_ID AND HL.LOOKUP_TYPE = 'PROCESSING_TYPE' AND HL.LOOKUP_CODE = PET1.PROCESSING_TYPE AND NOT EXISTS (SELECT 1 FROM PAY_ELEMENT_TYPE_RULES PETR1 WHERE PETR1.ELEMENT_SET_ID = PES1.ELEMENT_SET_ID AND PETR1.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID AND PETR1.INCLUDE_OR_EXCLUDE = 'E') AND ((PES1.ELEMENT_SET_TYPE = 'D' AND EXISTS (SELECT 1 FROM HR_LOOKUPS HRL, PAY_INPUT_VALUES_F PIV WHERE PIV.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID AND PIV.UOM = 'M' AND HRL.APPLICATION_ID IN (800, 801) AND HRL.LOOKUP_TYPE = 'NAME_TRANSLATIONS' AND HRL.LOOKUP_CODE = 'PAY VALUE' AND 'Pay Value' = PIV.NAME)) OR PES1.ELEMENT_SET_TYPE IN ('R','C')) UNION SELECT PES1.ELEMENT_SET_ID, PET2.ELEMENT_TYPE_ID, PET2.ELEMENT_NAME, PET2.CLASSIFICATION_ID, PET2.PROCESSING_TYPE, PET2.BUSINESS_GROUP_ID, PET2.LEGISLATION_CODE, ECL2TL.CLASSIFICATION_NAME, HL2.MEANING PROCESSING_TYPE_MEANING FROM PAY_ELEMENT_TYPES PET2, PAY_ELEMENT_TYPE_RULES PETR2, PAY_ELEMENT_SETS PES1, PAY_ELEMENT_CLASSIFICATIONS ECL2, PAY_ELEMENT_CLASSIFICATIONS_TL ECL2TL, HR_LOOKUPS HL2 WHERE ECL2.Classification_id = ECL2TL.classification_id AND ECL2TL.LANGUAGE = userenv('LANG') AND PETR2.INCLUDE_OR_EXCLUDE = 'I' AND PES1.ELEMENT_SET_ID = PETR2.ELEMENT_SET_ID AND PET2.ELEMENT_TYPE_ID = PETR2.ELEMENT_TYPE_ID AND ECL2.CLASSIFICATION_ID = PET2.CLASSIFICATION_ID AND HL2.LOOKUP_TYPE = 'PROCESSING_TYPE' AND HL2.LOOKUP_CODE = PET2.PROCESSING_TYPE

View Text - HTML Formatted

SELECT PES1.ELEMENT_SET_ID
, PET1.ELEMENT_TYPE_ID
, PET1.ELEMENT_NAME
, PET1.CLASSIFICATION_ID
, PET1.PROCESSING_TYPE
, PET1.BUSINESS_GROUP_ID
, PET1.LEGISLATION_CODE
, ECLTL.CLASSIFICATION_NAME
, HL.MEANING PROCESSING_TYPE_MEANING
FROM PAY_ELEMENT_TYPES PET1
, PAY_ELE_CLASSIFICATION_RULES PECR1
, PAY_ELEMENT_SETS PES1
, PAY_ELEMENT_CLASSIFICATIONS ECL
, PAY_ELEMENT_CLASSIFICATIONS_TL ECLTL
, HR_LOOKUPS HL
WHERE ECL.CLASSIFICATION_ID = ECLTL.CLASSIFICATION_ID
AND ECLTL.LANGUAGE = USERENV('LANG')
AND PET1.CLASSIFICATION_ID = PECR1.CLASSIFICATION_ID
AND PECR1.ELEMENT_SET_ID = PES1.ELEMENT_SET_ID
AND ECL.CLASSIFICATION_ID = PET1.CLASSIFICATION_ID
AND HL.LOOKUP_TYPE = 'PROCESSING_TYPE'
AND HL.LOOKUP_CODE = PET1.PROCESSING_TYPE
AND NOT EXISTS (SELECT 1
FROM PAY_ELEMENT_TYPE_RULES PETR1
WHERE PETR1.ELEMENT_SET_ID = PES1.ELEMENT_SET_ID
AND PETR1.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PETR1.INCLUDE_OR_EXCLUDE = 'E')
AND ((PES1.ELEMENT_SET_TYPE = 'D'
AND EXISTS (SELECT 1
FROM HR_LOOKUPS HRL
, PAY_INPUT_VALUES_F PIV
WHERE PIV.ELEMENT_TYPE_ID = PET1.ELEMENT_TYPE_ID
AND PIV.UOM = 'M'
AND HRL.APPLICATION_ID IN (800
, 801)
AND HRL.LOOKUP_TYPE = 'NAME_TRANSLATIONS'
AND HRL.LOOKUP_CODE = 'PAY VALUE'
AND 'PAY VALUE' = PIV.NAME)) OR PES1.ELEMENT_SET_TYPE IN ('R'
, 'C')) UNION SELECT PES1.ELEMENT_SET_ID
, PET2.ELEMENT_TYPE_ID
, PET2.ELEMENT_NAME
, PET2.CLASSIFICATION_ID
, PET2.PROCESSING_TYPE
, PET2.BUSINESS_GROUP_ID
, PET2.LEGISLATION_CODE
, ECL2TL.CLASSIFICATION_NAME
, HL2.MEANING PROCESSING_TYPE_MEANING
FROM PAY_ELEMENT_TYPES PET2
, PAY_ELEMENT_TYPE_RULES PETR2
, PAY_ELEMENT_SETS PES1
, PAY_ELEMENT_CLASSIFICATIONS ECL2
, PAY_ELEMENT_CLASSIFICATIONS_TL ECL2TL
, HR_LOOKUPS HL2
WHERE ECL2.CLASSIFICATION_ID = ECL2TL.CLASSIFICATION_ID
AND ECL2TL.LANGUAGE = USERENV('LANG')
AND PETR2.INCLUDE_OR_EXCLUDE = 'I'
AND PES1.ELEMENT_SET_ID = PETR2.ELEMENT_SET_ID
AND PET2.ELEMENT_TYPE_ID = PETR2.ELEMENT_TYPE_ID
AND ECL2.CLASSIFICATION_ID = PET2.CLASSIFICATION_ID
AND HL2.LOOKUP_TYPE = 'PROCESSING_TYPE'
AND HL2.LOOKUP_CODE = PET2.PROCESSING_TYPE