1 PACKAGE BODY WSH_CALENDAR_ACTIONS AS
2 /* $Header: WSHCAACB.pls 120.1 2005/08/12 14:21:48 sperera noship $ */
3 -- standard global constants
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CALENDAR_ACTIONS';
5 -------------------------------------------------------------------------------------------
6 -- Start of comments
7 -- API name : Get_Shift_Times
8 -- Type : public
9 -- Function : get the earliest shift times from
10 -- calendars greater than a given date/time
11 -- for a given location
12 -- Version : Initial version 1.0
13 -- Parameters : IN: p_location_id: Location for which we need the
14 -- shift times.
15 -- p_date : Date for which we need the
16 -- shift times.
17 -- : OUT: x_from_time : The start time of the earliest
18 -- shift that ends after the given
19 -- date time
20 -- : x_to_time : The end time of the earliest
21 -- shift that ends after the given
22 -- date time
23 -- Notes : It is possible that a shift extends past midnight.
24 -- In this case the x_to_time will be less than the x_from_time.
25 -- If there are no shifts remaing after the date/time or
26 -- Calendar not defined for the location, NULL will be
27 -- returned for both the out dates.
28 -- End of comments
29 -- ------------------------------------------------------------------------------------------
30
31
32 Procedure Get_Shift_Times(p_location_id IN NUMBER,
33 p_date IN DATE,
34 x_from_time OUT NOCOPY NUMBER,
35 x_to_time OUT NOCOPY NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2) IS
37
38
39 -- This cursor will get the earliest shift end time today greater than the given time for
40 -- that calendar.
41
42 CURSOR get_shift_end_time_today(p_location_id in number, p_date in date) IS
43 SELECT BSH.from_time, BSH.to_time
44 FROM BOM_CALENDAR_SHIFTS BCA, BOM_SHIFT_TIMES BSH,
45 BOM_SHIFT_DATES BDT, WSH_CALENDAR_ASSIGNMENTS WCA
46 WHERE WCA.LOCATION_ID = p_location_id and
47 BCA.CALENDAR_CODE=WCA.CALENDAR_CODE and
48 BSH.CALENDAR_CODE=WCA.CALENDAR_CODE and
49 BCA.SHIFT_NUM = BSH.SHIFT_NUM and
50 BDT.CALENDAR_CODE = WCA.CALENDAR_CODE and
51 BDT.SHIFT_NUM = BCA.SHIFT_NUM and
52 BDT.EXCEPTION_SET_ID = -1 and
53 to_char(BDT.SHIFT_DATE, 'YYYY/MM/DD') = to_char(p_date, 'YYYY/MM/DD') and
54 BSH.TO_TIME > BSH.FROM_TIME and
55 BSH.TO_TIME > to_number(to_char(p_date, 'SSSSS'))
56 ORDER BY BSH.to_time ASC;
57
58
59
60 -- This cursor will get the earliest shift end time from the given time tomorrow for
61 -- that calendar. This will be used when a shift may begin today, but
62 -- end tomorrow.
63 CURSOR get_shift_end_time_tomorrow(p_location_id in number, p_date in date) IS
64 SELECT BSH.from_time, BSH.to_time
65 FROM BOM_CALENDAR_SHIFTS BCA, BOM_SHIFT_TIMES BSH,
66 BOM_SHIFT_DATES BDT, WSH_CALENDAR_ASSIGNMENTS WCA
67 WHERE WCA.LOCATION_ID = p_location_id and
68 BCA.CALENDAR_CODE=WCA.CALENDAR_CODE and
69 BSH.CALENDAR_CODE=WCA.CALENDAR_CODE and
70 BCA.SHIFT_NUM = BSH.SHIFT_NUM and
71 BDT.CALENDAR_CODE = WCA.CALENDAR_CODE and
72 BDT.SHIFT_NUM = BCA.SHIFT_NUM and
73 BDT.EXCEPTION_SET_ID = -1 and
74 to_char(BDT.SHIFT_DATE, 'YYYY/MM/DD')
75 = to_char(p_date, 'YYYY/MM/DD') and
76 BSH.TO_TIME < BSH.FROM_TIME
77 ORDER BY BSH.to_time ASC;
78
79 --
80 l_debug_on BOOLEAN;
81 --
82 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Shift_Times';
83
84 BEGIN
85
86 --
87 -- Debug Statements
88 --
89 --
90 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
91 --
92 IF l_debug_on IS NULL
93 THEN
94 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
95 END IF;
96 --
97 IF l_debug_on THEN
98 --
99 WSH_DEBUG_SV.push(l_module_name);
100 WSH_DEBUG_SV.log(l_module_name,'p_date',p_date);
101 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
102 --
103 END IF;
104
105 x_return_status := wsh_util_core.g_ret_sts_success;
106 x_from_time := NULL;
107 x_to_time := NULL;
108
109 OPEN get_shift_end_time_today(p_location_id, p_date);
110 FETCH get_shift_end_time_today
111 INTO x_from_time, x_to_time;
112 IF get_shift_end_time_today%FOUND THEN
113 CLOSE get_shift_end_time_today;
114 ELSE
115 CLOSE get_shift_end_time_today;
116 OPEN get_shift_end_time_tomorrow(p_location_id, p_date);
117 FETCH get_shift_end_time_tomorrow
118 INTO x_from_time, x_to_time;
119 CLOSE get_shift_end_time_tomorrow;
120 END IF;
121
122 IF l_debug_on THEN
123 --
124 WSH_DEBUG_SV.log(l_module_name,'x_from_time',x_from_time);
125 WSH_DEBUG_SV.log(l_module_name,'x_to_time',x_to_time);
126 WSH_DEBUG_SV.pop(l_module_name);
127 --
128 END IF;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
133 wsh_util_core.default_handler('WSH_U_UTIL.Get_Shift_Times',l_module_name);
134 --
135 IF l_debug_on THEN
136 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
137 END IF;
138
139
140 END Get_Shift_Times;
141
142 END WSH_CALENDAR_ACTIONS;
143
[Home] [Help]