VIEW: APPS.PQP_UNION_TEMPLATE_V

Source

View Text - Preformatted

SELECT petf.ELEMENT_TYPE_ID ,petf.BUSINESS_GROUP_ID ,petf.LEGISLATION_CODE ,petf.CLASSIFICATION_ID ,pec.CLASSIFICATION_NAME ,petf.PROCESSING_TYPE ,petf.ELEMENT_NAME ,petf.REPORTING_NAME ,petf.DESCRIPTION ,petei.eei_information1 ORGANIZATION_ID ,hou.name UNION_NAME ,petei.eei_information2 UNION_LEVEL_BALANCE ,petei.eei_information3 RATE_TYPE ,hrl.meaning RATE_TYPE_NAME ,petei.eei_information4 ,decode(piv1.input_value_id, null, 'N', 'Y') OVERRIDE_AMOUNT ,decode(piv2.input_value_id, null, 'N', 'Y')TAX_RELIEF ,decode(piv3.input_value_id, null, 'N', 'Y') SUPPLEMENTARY_LEVY ,petf.LAST_UPDATE_DATE ,petf.LAST_UPDATED_BY ,petf.LAST_UPDATE_LOGIN ,petf.CREATED_BY ,petf.CREATION_DATE FROM PAY_ELEMENT_TYPES_F PETF, PAY_ELEMENT_TYPE_EXTRA_INFO PETEI, HR_LOOKUPS HRL, PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_INPUT_VALUES_F PIV1, PAY_INPUT_VALUES_F PIV2, PAY_INPUT_VALUES_F PIV3, HR_ALL_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI, FND_SESSIONS SES WHERE PETF.ELEMENT_TYPE_ID = PETEI.ELEMENT_TYPE_ID AND PETEI.EEI_INFORMATION1 = TO_CHAR(HOU.ORGANIZATION_ID) AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID AND HOI.ORG_INFORMATION_CONTEXT = 'CLASS' AND HOI.ORG_INFORMATION1 = 'BARGAINING_UNIT' AND PETEI.EEI_INFORMATION3 = HRL.LOOKUP_CODE(+) AND PIV1.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID AND PIV1.NAME(+) = 'Override Amount' AND PIV2.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID AND PIV2.NAME(+) = 'Tax Relief' AND PIV3.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID AND PIV3.NAME(+) = 'Supplementary Levy' AND HRL.LOOKUP_TYPE(+) = 'PQP_RATE_TYPE' AND PETEI.INFORMATION_TYPE ='PQP_UK_UNION_INFO' AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID AND SES.SESSION_ID = USERENV('sessionid') AND SES.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV1.EFFECTIVE_START_DATE, SES.EFFECTIVE_DATE) AND NVL(PIV1.EFFECTIVE_END_DATE, SES.EFFECTIVE_DATE) AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV2.EFFECTIVE_START_DATE, SES.EFFECTIVE_DATE) AND NVL(PIV2.EFFECTIVE_END_DATE, SES.EFFECTIVE_DATE) AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV3.EFFECTIVE_START_DATE, SES.EFFECTIVE_DATE) AND NVL(PIV3.EFFECTIVE_END_DATE, SES.EFFECTIVE_DATE)

View Text - HTML Formatted

SELECT PETF.ELEMENT_TYPE_ID
, PETF.BUSINESS_GROUP_ID
, PETF.LEGISLATION_CODE
, PETF.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETF.PROCESSING_TYPE
, PETF.ELEMENT_NAME
, PETF.REPORTING_NAME
, PETF.DESCRIPTION
, PETEI.EEI_INFORMATION1 ORGANIZATION_ID
, HOU.NAME UNION_NAME
, PETEI.EEI_INFORMATION2 UNION_LEVEL_BALANCE
, PETEI.EEI_INFORMATION3 RATE_TYPE
, HRL.MEANING RATE_TYPE_NAME
, PETEI.EEI_INFORMATION4
, DECODE(PIV1.INPUT_VALUE_ID
, NULL
, 'N'
, 'Y') OVERRIDE_AMOUNT
, DECODE(PIV2.INPUT_VALUE_ID
, NULL
, 'N'
, 'Y')TAX_RELIEF
, DECODE(PIV3.INPUT_VALUE_ID
, NULL
, 'N'
, 'Y') SUPPLEMENTARY_LEVY
, PETF.LAST_UPDATE_DATE
, PETF.LAST_UPDATED_BY
, PETF.LAST_UPDATE_LOGIN
, PETF.CREATED_BY
, PETF.CREATION_DATE
FROM PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_TYPE_EXTRA_INFO PETEI
, HR_LOOKUPS HRL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI
, FND_SESSIONS SES
WHERE PETF.ELEMENT_TYPE_ID = PETEI.ELEMENT_TYPE_ID
AND PETEI.EEI_INFORMATION1 = TO_CHAR(HOU.ORGANIZATION_ID)
AND HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND HOI.ORG_INFORMATION1 = 'BARGAINING_UNIT'
AND PETEI.EEI_INFORMATION3 = HRL.LOOKUP_CODE(+)
AND PIV1.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID
AND PIV1.NAME(+) = 'OVERRIDE AMOUNT'
AND PIV2.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID
AND PIV2.NAME(+) = 'TAX RELIEF'
AND PIV3.ELEMENT_TYPE_ID(+) = PETF.ELEMENT_TYPE_ID
AND PIV3.NAME(+) = 'SUPPLEMENTARY LEVY'
AND HRL.LOOKUP_TYPE(+) = 'PQP_RATE_TYPE'
AND PETEI.INFORMATION_TYPE ='PQP_UK_UNION_INFO'
AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV1.EFFECTIVE_START_DATE
, SES.EFFECTIVE_DATE)
AND NVL(PIV1.EFFECTIVE_END_DATE
, SES.EFFECTIVE_DATE)
AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV2.EFFECTIVE_START_DATE
, SES.EFFECTIVE_DATE)
AND NVL(PIV2.EFFECTIVE_END_DATE
, SES.EFFECTIVE_DATE)
AND SES.EFFECTIVE_DATE BETWEEN NVL(PIV3.EFFECTIVE_START_DATE
, SES.EFFECTIVE_DATE)
AND NVL(PIV3.EFFECTIVE_END_DATE
, SES.EFFECTIVE_DATE)