VIEW: APPS.PQP_OSP_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 ,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

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
, 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