View: PQP_OMP_TEMPLATE_V

Product:	PQP - Public Sector Payroll
Description:	View of all absence information elements created by the OMP element template.
Implementation/DBA Data:	APPS.PQP_OMP_TEMPLATE_V

View Text

SELECT PETF.ELEMENT_TYPE_ID
, PETF.BUSINESS_GROUP_ID
, PETF.LEGISLATION_CODE
, PETF.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETF.PROCESSING_TYPE
, PET.BASE_NAME
, PETF.ELEMENT_NAME
, PETF.REPORTING_NAME
, PETF.DESCRIPTION
, PETF.PROCESSING_PRIORITY
, PETF.EFFECTIVE_START_DATE
, PETF.EFFECTIVE_END_DATE
, PETEI.EEI_INFORMATION1 PLAN_ID
, BPL.NAME PLAN_NAME
, PETEI.EEI_INFORMATION2 PLAN_DESCRIPTION
, PETEI.EEI_INFORMATION3 LOS_CALC
, PETEI.EEI_INFORMATION4 LOS_DURATION
, PETEI.EEI_INFORMATION5 LOS_UOM
, PETEI.EEI_INFORMATION6 AVGEARN_DURATION
, PETEI.EEI_INFORMATION7 AVGEARN_UOM
, PETEI.EEI_INFORMATION8 AVGEARN_BALNAME
, PETEI.EEI_INFORMATION9 ABS_DAYS
, PETEI.EEI_INFORMATION11 ABS_ENT_MATLEAVES_UDT_ID
, PUT1.USER_TABLE_NAME ABS_ENT_MATERNITY_LEAVES
, PETEI.EEI_INFORMATION12 ABS_ENT_HOLIDAYS_UDT_ID
, PUT2.USER_TABLE_NAME ABS_ENT_HOLIDAYS
, PETEI.EEI_INFORMATION13 ABS_DAILYRATE_CALC
, PETEI.EEI_INFORMATION14 ABS_DAILYRATE_CALC_PERIOD
, PETEI.EEI_INFORMATION15 ABS_DAILYRATE_CALC_DURATION
, PETEI.EEI_INFORMATION16 ABS_PAY_INFO_SRC_COMP
, HRL.MEANING ABS_PAY_INFO_SRC_COMP_NAME
, PETEI.EEI_INFORMATION17 PRIMARY_ABS_INFO_ELE_FLAG
, PETEI.EEI_INFORMATION18 ABS_DEFAULT_WORKPATTERN
, UPPER(PETEI.EEI_INFORMATION19) ELEMENT_TYPE
, PETEI.EEI_INFORMATION20 ABS_TYPES_LIST_NAME
, PETF.LAST_UPDATE_DATE
, PETF.LAST_UPDATED_BY
, PETF.LAST_UPDATE_LOGIN
, PETF.CREATED_BY
, PETF.CREATION_DATE
, DECODE(UPPER(PETEI.EEI_INFORMATION30)
, 'M'
, 'MATERNITY'
, INITCAP(PETEI.EEI_INFORMATION30))
FROM PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_TYPE_EXTRA_INFO PETEI
, HR_LOOKUPS HRL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, FND_SESSIONS SES
, PAY_USER_TABLES PUT1
, PAY_USER_TABLES PUT2
, PAY_ELEMENT_TEMPLATES PET
, PAY_TEMPLATE_CORE_OBJECTS PTCO
, BEN_PL_F BPL
, BEN_PL_TYP_F BPTY
WHERE PETF.ELEMENT_TYPE_ID = PETEI.ELEMENT_TYPE_ID
AND PETEI.EEI_INFORMATION16 = HRL.LOOKUP_CODE(+)
AND HRL.LOOKUP_TYPE(+) = 'PQP_RATE_TYPE'
AND UPPER(PETEI.EEI_INFORMATION19) = 'ABSENCE INFO'
AND PETEI.INFORMATION_TYPE ='PQP_GB_OMP_ABSENCE_PLAN_INFO'
AND PEC.CLASSIFICATION_ID = PETF.CLASSIFICATION_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND PUT1.USER_TABLE_ID(+) = TO_NUMBER(PETEI.EEI_INFORMATION11)
AND PUT2.USER_TABLE_ID(+) = TO_NUMBER(PETEI.EEI_INFORMATION12)
AND PTCO.CORE_OBJECT_ID = PETF.ELEMENT_TYPE_ID
AND PTCO.TEMPLATE_ID = PET.TEMPLATE_ID
AND PTCO.CORE_OBJECT_TYPE = 'ET'
AND SES.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE
AND BPL.PL_TYP_ID = BPTY.PL_TYP_ID
AND SES.EFFECTIVE_DATE BETWEEN BPL.EFFECTIVE_START_DATE
AND BPL.EFFECTIVE_END_DATE
AND BPTY.OPT_TYP_CD = 'ABS'
AND PETEI.EEI_INFORMATION1 = FND_NUMBER.NUMBER_TO_CANONICAL(BPL.PL_ID)
AND SES.EFFECTIVE_DATE BETWEEN BPTY.EFFECTIVE_START_DATE
AND BPTY.EFFECTIVE_END_DATE

Columns