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')
)
