Work Shifts
SELECT ZSSV.shift_name Shift_Name_ws,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'ZMM_SR_SHIFT_TYPE'
AND language = 'US'
AND lookup_code = ZSSV.shift_type_code) Shift_Type_ws,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'ZMM_SR_SHIFT_DETAIL_TYPE'
AND language = 'US'
AND lookup_code = ZSSV.shift_dtl_type_code) Shift_Detail_Type_ws,
(SELECT meaning
FROM fnd_lookups
WHERE lookup_code = ZSSV.availability_code and lookup_type = 'ZMM_SR_AVAILABILITY') period_type_ws,
to_char(trunc(sysdate) + ZSSV.start_time_ms_num/1000/60/24/60, 'hh24:mi') Start_Time_ws,
to_char(trunc(sysdate) + ZSSV.end_time_ms_num/1000/60/24/60, 'hh24:mi') End_Time_ws,
ZSSV.duration_ms_num/1000/60/60 Duration_ws,/*INITCAP(zSsv.duration_uom_code)*/ 'Hours' duration_uom_code,
ZSSV.short_txt code_ws,
(SELECT meaning
FROM fnd_lookups
WHERE lookup_code = ZSSV.category_code) Shift_Category_ws,
ZSSV.shift_desc Description_ws
FROM ZMM_SR_SHIFTS_VL ZSSV
WHERE (
(:P_CREATION_TYPE= 'Custom' and ZSSV.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and ZSSV.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and ZSSV.created_by = 'SEED_DATA_FROM_APPLICATION' and ZSSV. last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
