-
-

Eligiblity Profile

BENEFITS

Eligibility Profiles

SELECT ZSPV.Pattern_name name_wp, ZSPV.pattern_desc description_wp, ZSPV.length_days_num Length_in_days_wp, ZSPD.day_start_num start_day_wp, ZSPD.day_stop_num end_day_wp, ZSSV.shift_name shift_name_wp, ZSSV.shift_type_code shift_type_wp, (SELECT meaning FROM fnd_lookups WHERE lookup_code = ZSSV.availability_code and lookup_type = 'ZMM_SR_AVAILABILITY') shift_period_type_wp, (SELECT meaning FROM fnd_lookups WHERE lookup_code = ZSSV.category_code) Shift_Category_wp, ZSSV.shift_desc Shift_Description_wp, ZSSV.start_time_ms_num start_time_ms_num_wp, decode(ZSSV.start_time_ms_num,null,null,SUBSTR(to_char((ZSSV.start_time_ms_num/1000/60/60),'00.00') , 0, INSTR(to_char((ZSSV.start_time_ms_num/1000/60/60),'00.00'), '.', 1, 1)-1)||' :'|| to_char((SUBSTR(to_char((ZSSV.start_time_ms_num/1000/60/60),'00.00'), INSTR(to_char((ZSSV.start_time_ms_num/1000/60/60),'00.00'), '.', 1, 1),length(to_char((ZSSV.start_time_ms_num/1000/60/60),'00.0'))) *60),'00')) Start_Time_wp, decode(ZSSV.end_time_ms_num,null,null,SUBSTR(to_char((ZSSV.end_time_ms_num/1000/60/60),'00.00') , 0, INSTR(to_char((ZSSV.end_time_ms_num/1000/60/60),'00.00'), '.', 1, 1)-1)||' :'|| to_char((SUBSTR(to_char((ZSSV.end_time_ms_num/1000/60/60),'00.00'), INSTR(to_char((ZSSV.end_time_ms_num/1000/60/60),'00.00'), '.', 1, 1),length(to_char((ZSSV.end_time_ms_num/1000/60/60),'00.0'))) *60),'00')) End_Time_wp, ZSSV.duration_ms_num/1000/60/60||' Hours' Duration_wp FROM ZMM_SR_PATTERNS_VL ZSPV ,ZMM_SR_PATTERN_DTLS ZSPD ,ZMM_SR_SHIFTS_VL ZSSV WHERE ZSPV.pattern_id = ZSPD.pattern_id AND ZSPD.child_shift_id = ZSSV.shift_id AND ( (:P_CREATION_TYPE= 'Custom' and ZSPV.created_by <> 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded' and ZSPV.created_by = 'SEED_DATA_FROM_APPLICATION' ) OR (:P_CREATION_TYPE = 'Seeded Modified' and ZSPV.created_by = 'SEED_DATA_FROM_APPLICATION' and ZSPV. last_updated_by <> 'SEED_DATA_FROM_APPLICATION') OR (:P_CREATION_TYPE = 'ALL') )