-- 班次基礎資料（組織，班次編號，班次名稱，周工作天數，周放假天數，上班時間，下班時間 ）
select shifts.calendar_code,shifts.shift_num,shifts.description ,
workday.days_on,workday.days_off
, LPAD(trunc(times.from_time/3600),2,'0')||':'|| LPAD(trunc( mod(times.from_time,3600)/60 ),2,'0') as from_times
, LPAD(trunc(times.to_time/3600),2,'0')||':'|| LPAD(trunc( mod(times.to_time,3600)/60 ),2,'0' ) as to_times
from apps.BOM_CALENDAR_SHIFTS shifts
, apps.BOM_WORKDAY_PATTERNS workday
, apps.BOM_SHIFT_TIMES Times
where shifts.calendar_code = workday.calendar_code
and shifts.shift_num = workday.shift_num
and shifts.calendar_code = times.calendar_code
and shifts.shift_num =times.shift_num ;[@more@]

-- WORKDAY CALENDAR
select * from apps.BOM_CALENDARS ; -- calendar_code

-- Shifts 富相電子 。（班次名稱，編號 ）
select * from apps.BOM_CALENDAR_SHIFTS; -- calendar_code , shift_num (有重複)

-- Shifts Workday patterns （一周工作天數與放假天數 ）
select * from apps.BOM_WORKDAY_PATTERNS ; -- calendar_code , shift_num

-- Shift Time （上班 起止 時間）
select * from apps.BOM_SHIFT_TIMES ; -- calendar_code , shift_num

/* //班次基礎資料 code SQLScript/ */




-- 將 秒鐘 轉換成時間 小時 ：分鐘：秒
select trunc(30682/3600),trunc(mod(30682,3600)/60),mod(30682,60) from dual ;
select trunc(3672/3600),trunc(mod(3672,3600)/60),mod(3672,60) from dual ;



select trunc(1073/3600),trunc(mod(1073,3600)/60),mod(1073,60) from dual ;

select trunc(1073/3600)||'小時'||trunc((1073-trunc(1073/3600))/60)||'分'||mod(1073,60)||'秒' from dual;


select replace( 'JACK and JUE', 'J', 'BL') "Change " From dual;


Select LPAD('12345', 8, '0') 左邊補零, RPAD('12345', 8, '0') 右邊補零 From Dual ;
-- 班次基礎資料（組織，班次編號，班次名稱，周工作天數，周放假天數，上班時間，下班時間 ）-- 140 MS
select shifts.calendar_code,shifts.shift_num,shifts.description ,
workday.days_on,workday.days_off
, LPAD(trunc(times.from_time/3600),2,'0')||':'|| LPAD(trunc( mod(times.from_time,3600)/60 ),2,'0') as from_times
, LPAD(trunc(times.to_time/3600),2,'0')||':'|| LPAD(trunc( mod(times.to_time,3600)/60 ),2,'0' ) as to_times
from apps.BOM_CALENDAR_SHIFTS shifts
LEFT JOIN apps.BOM_WORKDAY_PATTERNS workday
ON shifts.calendar_code = workday.calendar_code AND shifts.shift_num = workday.shift_num
LEFT JOIN apps.BOM_SHIFT_TIMES Times
ON shifts.calendar_code = times.calendar_code and shifts.shift_num =times.shift_num
Order by shifts.calendar_code,shifts.shift_num ;