[Home] [Help]
68: CURSOR get_day_cur (c_calendar_id VARCHAR2 , c_cal_date DATE) IS
69: SELECT 1 FROM dual
70: WHERE EXISTS
71: (SELECT 1
72: FROM BOM_shift_dates sd, bom_shift_times sht
73: WHERE sd.calendar_code = sht.calendar_code
74: AND sd.shift_date = trunc(c_cal_date)
75: AND sd.shift_num = sht.shift_num
76: AND sd.calendar_code = c_calendar_id
238: decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time)) duration,
239: st.to_time to_time
240: FROM bom_calendars cal,
241: bom_shift_dates sd,
242: bom_shift_times st
243: WHERE cal.calendar_code = c_calendar_code
244: AND sd.calendar_code = cal.calendar_code
245: AND st.calendar_code = sd.calendar_code
246: AND sd.shift_num = st.shift_num
258: decode(sign(st.to_time - st.from_time),1,(st.to_time - st.from_time),0,0,((86400 - st.from_time)+ st.from_time)) duration,
259: st.to_time to_time
260: FROM bom_calendars cal,
261: bom_shift_dates sd,
262: bom_shift_times st
263: WHERE cal.calendar_code = c_calendar_code
264: AND sd.calendar_code = cal.calendar_code
265: AND st.calendar_code = sd.calendar_code
266: AND sd.shift_num = st.shift_num
733: SELECT sd.shift_date calendar_date,
734: decode(SUM(decode(sd.seq_num,NULL,0,1)),0,0,1) l_work_day
735: FROM bom_calendars cal,
736: bom_shift_dates sd,
737: bom_shift_times st
738: WHERE cal.calendar_code = c_calendar_code
739: AND sd.calendar_code = cal.calendar_code
740: AND st.calendar_code = sd.calendar_code
741: AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
871: SELECT sd.shift_date calendar_date,
872: SUM((st.to_time - st.from_time)/3600) duration
873: FROM bom_calendars cal,
874: bom_shift_dates sd,
875: bom_shift_times st
876: WHERE cal.calendar_code = c_calendar_code
877: AND sd.calendar_code = cal.calendar_code
878: AND st.calendar_code = sd.calendar_code
879: AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
983:
984: CURSOR shopday_dtls_cur (c_calendar_code VARCHAR2,
985: c_shopday_no NUMBER) IS
986: SELECT shift_num,from_time,to_time
987: FROM bom_shift_times
988: WHERE calendar_code = c_calendar_code
989: AND shift_num = c_shopday_no
990: ORDER BY from_time ;
991:
991:
992: CURSOR Cur_shop_day (c_calendar_code VARCHAR2,
993: c_shopday_no NUMBER) IS
994: SELECT COUNT(*)
995: FROM bom_shift_times
996: WHERE calendar_code = c_calendar_code
997: AND shift_num = c_shopday_no;
998:
999: i INTEGER := 0 ;
1319: CURSOR get_datetime_cur (c_calendar_code VARCHAR2, c_cal_date DATE) IS
1320: SELECT 1
1321: FROM bom_calendars cal,
1322: bom_shift_dates sd,
1323: bom_shift_times st
1324: WHERE cal.calendar_code = c_calendar_code
1325: AND sd.calendar_code = cal.calendar_code
1326: AND st.calendar_code = sd.calendar_code
1327: AND sd.shift_num = st.shift_num
1492: WHERE EXISTS (
1493: select 'x'
1494: FROM bom_calendars bd,
1495: bom_shift_dates sd,
1496: bom_shift_times st
1497: WHERE bd.calendar_code = p_calendar_id
1498: AND sd.calendar_code = bd.calendar_code
1499: AND sd.calendar_code= st.calendar_code
1500: AND sd.shift_num = st.shift_num
1510: CURSOR get_NextDatetime_cur (p_calendar_id VARCHAR2 , p_cal_date DATE) IS
1511: SELECT min (sd.shift_date + (st.from_time/86400))
1512: FROM bom_calendars bd,
1513: bom_shift_dates sd,
1514: bom_shift_times st
1515: WHERE bd.calendar_code = p_calendar_id
1516: AND sd.calendar_code = bd.calendar_code
1517: AND sd.calendar_code= st.calendar_code
1518: AND sd.shift_num = st.shift_num
1529: + (st.to_time- st.from_time) - 86400),(st.from_time + (st.to_time- st.from_time)))/86400 ) )
1530: )
1531: FROM bom_calendars bd,
1532: bom_shift_dates sd,
1533: bom_shift_times st
1534: WHERE bd.calendar_code = p_calendar_id
1535: AND sd.calendar_code = bd.calendar_code
1536: AND sd.calendar_code= st.calendar_code
1537: AND sd.shift_num = st.shift_num