View: PQP_OSP_TEMPLATE_V

Product:	PQP - Public Sector Payroll
Description:	View of all absence information elements created by the OSP element template.
Implementation/DBA Data:	APPS.PQP_OSP_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 SCH_CAL_TYPE
, PETEI.EEI_INFORMATION4 SCH_CAL_DURATION
, PETEI.EEI_INFORMATION5 SCH_CAL_UOM
, FND_DATE.CANONICAL_TO_DATE(PETEI.EEI_INFORMATION6) SCH_CAL_START_DATE
, FND_DATE.CANONICAL_TO_DATE(PETEI.EEI_INFORMATION7) SCH_CAL_END_DATE
, PETEI.EEI_INFORMATION8 ABS_DAYS
, PETEI.EEI_INFORMATION9 ABS_ENT_SICKLEAVES_UDT_ID
, PUT1.USER_TABLE_NAME ABS_ENT_SICK_LEAVES
, PETEI.EEI_INFORMATION10 ABS_ENT_HOLIDAYS_UDT_ID
, PUT2.USER_TABLE_NAME ABS_ENT_HOLIDAYS
, PETEI.EEI_INFORMATION11 ABS_DAILYRATE_CALC
, PETEI.EEI_INFORMATION12 ABS_DAILYRATE_CALC_PERIOD
, PETEI.EEI_INFORMATION13 ABS_DAILYRATE_CALC_DURATION
, UPPER(PETEI.EEI_INFORMATION19) ELEMENT_TYPE
, PETEI.EEI_INFORMATION15 ABS_PAY_INFO_SRC_COMP
, HRL.MEANING ABS_PAY_INFO_SRC_COMP_NAME
, PETEI.EEI_INFORMATION16 PRIMARY_ABS_INFO_ELE_FLAG
, PETEI.EEI_INFORMATION17 ABS_DEFAULT_WORKPATTERN
, PETEI.EEI_INFORMATION26 ABS_OVERLAP_RULE
, PETEI.EEI_INFORMATION18 ABS_TYPES_LIST_NAME
, PETF.LAST_UPDATE_DATE
, PETF.LAST_UPDATED_BY
, PETF.LAST_UPDATE_LOGIN
, PETF.CREATED_BY
, PETF.CREATION_DATE
, PETEI.EEI_INFORMATION20 SCH_CAL_DURATION_SEC
, PETEI.EEI_INFORMATION21 SCH_CAL_UOM_SEC
, DECODE(UPPER(PETEI.EEI_INFORMATION30)
, 'S'
, 'SICKNESS'
, INITCAP(PETEI.EEI_INFORMATION30)) ABSENCE_CATEGORY
, PETEI.EEI_INFORMATION22 ENTITLEMENT_PRORATION
, PETEI.EEI_INFORMATION23 ABSENCE_SCHEDULE_WORKPATTERN
, PETEI.EEI_INFORMATION24 PLAN_TYPES_EXTEND_PERIOD
, PETEI.EEI_INFORMATION14 FT_ROUND_CONFIG
, PETEI.EEI_INFORMATION25 PT_ROUND_CONFIG
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_INFORMATION15 = HRL.LOOKUP_CODE(+)
AND HRL.LOOKUP_TYPE(+) = 'PQP_RATE_TYPE'
AND UPPER(PETEI.EEI_INFORMATION19) = 'ABSENCE INFO'
AND PETEI.INFORMATION_TYPE ='PQP_GB_OSP_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_INFORMATION9)
AND PUT2.USER_TABLE_ID(+) = TO_NUMBER(PETEI.EEI_INFORMATION10)
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