[Home] [Help]
PACKAGE BODY: APPS.HR_CAL_BUS
Source
1 Package Body hr_cal_bus as
2 /* $Header: hrcalrhi.pkb 115.3 99/10/07 08:14:01 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_cal_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< CHK_CALENDAR_NAME >--------------------------|
12 -- ----------------------------------------------------------------------------
13 procedure CHK_CALENDAR_NAME (
14 --
15 p_calendar_name in varchar2,
16 p_calendar_id in number default null,
17 p_object_version_number in number default null) is
18 --
19 cursor csr_duplicate is
20 --
21 -- Return a row if the parameter calendar name is NOT unique
22 --
23 select 1
24 from hr_calendars
25 where upper(calendar_name) = upper (p_calendar_name)
26 and (p_calendar_id is null or calendar_id <> p_calendar_id);
27 --
28 l_proc varchar2(72) := g_package||'chk_calendar_name';
29 l_api_updating boolean;
30 l_dummy integer (1);
31 --
32 begin
33 --
34 hr_utility.trace ('Entering '||l_proc);
35 --
36 hr_api.mandatory_arg_error
37 (p_api_name => l_proc,
38 p_argument => 'calendar name',
39 p_argument_value => p_calendar_name);
40 --
41 l_api_updating := hr_cal_shd.api_updating (
42 p_calendar_id => p_calendar_id,
43 p_object_version_number => p_object_version_number);
44 --
45 if (l_api_updating and hr_cal_shd.g_old_rec.calendar_name <> p_calendar_name)
46 or (not l_api_updating) then
47 --
48 open csr_duplicate;
49 fetch csr_duplicate into l_dummy;
50 if csr_duplicate%found then
51 --
52 close csr_duplicate;
53 hr_cal_shd.constraint_error ('HR_PAT_NAME_UK');
54 --
55 else
56 --
57 close csr_duplicate;
58 --
59 end if;
60 --
61 end if;
62 --
63 hr_utility.trace ('Leaving '||l_proc);
64 --
65 end chk_calendar_name;
66 -- ----------------------------------------------------------------------------
67 -- |---------------------------< CHK_PATTERN_START_POSITION >-----------------|
68 -- ----------------------------------------------------------------------------
69 procedure CHK_PATTERN_START_POSITION (
70 --
71 p_pattern_start_position in number,
72 p_pattern_id in number) is
73 --
74 cursor csr_construction_rows is
75 --
76 -- Get the number of pattern construction rows for the pattern
77 --
78 select count (*)
79 from hr_pattern_constructions
80 where pattern_id = p_pattern_id;
81 --
82 l_construction_rows integer := null;
83 l_proc varchar2(72) := g_package||'chk_pattern_start_position';
84 --
85 begin
86 --
87 hr_utility.trace ('Entering '||l_proc);
88 --
89 hr_api.mandatory_arg_error
90 (p_api_name => l_proc,
91 p_argument => 'pattern_id',
92 p_argument_value => p_pattern_id);
93 --
94 hr_api.mandatory_arg_error
95 (p_api_name => l_proc,
96 p_argument => 'pattern_start_position',
97 p_argument_value => p_pattern_start_position);
98 --
99 open csr_construction_rows;
100 fetch csr_construction_rows into l_construction_rows;
101 --
102 if csr_construction_rows%notfound then
103 --
104 -- The pattern id must be invalid
105 --
106 close csr_construction_rows;
107 hr_cal_shd.constraint_error ('HR_CAL_PAT_FK');
108 --
109 else
110 --
111 close csr_construction_rows;
112 --
113 end if;
114 --
115 if p_pattern_start_position > l_construction_rows then
116 --
117 -- The start position is invalid
118 --
119 hr_utility.set_message(801, 'HR_51060_PAY_START_POSITION');
120 hr_utility.set_message_token ('MAXIMUM',to_char (l_construction_rows));
121 hr_utility.raise_error;
122 --
123 end if;
124 --
125 hr_utility.trace ('Leaving '||l_proc);
126 --
127 end chk_pattern_start_position;
128 -- ----------------------------------------------------------------------------
129 -- |---------------------------< CHK_CALENDAR_START_TIME >--------------------|
130 -- ----------------------------------------------------------------------------
131 procedure CHK_CALENDAR_START_TIME (
132 --
133 p_calendar_start_time in date,
134 p_pattern_start_position in number,
135 p_pattern_id in number) is
136 --
137 cursor csr_pattern_construction is
138 --
139 -- Get the denormalised construction of the pattern, breaking down
140 -- any patterns within patterns into their component bits. We need this
141 -- information in order to establish the start time and weekday of the
142 -- calendar taking into account the offset of the pattern start
143 -- position. See the business rules document hrcal.bru for a fuller
144 -- explanation.
145 --
146 select bit1.time_unit_multiplier,
147 bit1.base_time_unit,
148 con1.sequence_no,
149 0
150 from hr_pattern_constructions CON1,
151 hr_pattern_bits BIT1
152 where bit1.pattern_bit_id = con1.pattern_bit_id
153 and con1.pattern_id = p_pattern_id
154 union all
155 select bit2.time_unit_multiplier,
156 bit2.base_time_unit,
157 con2.sequence_no,
158 con3.sequence_no
159 from hr_pattern_bits BIT2,
160 hr_pattern_constructions CON2,
161 hr_pattern_constructions CON3
162 where bit2.pattern_bit_id = con3.pattern_bit_id
163 and con2.component_pattern_id = con3.pattern_id
164 and con2.pattern_id = p_pattern_id
165 order by 3,4;
166 --
167 cursor csr_pattern_details is
168 --
169 -- Get the attributes of the pattern
170 --
171 select *
172 from hr_patterns
173 where pattern_id = p_pattern_id;
174 --
175 l_proc varchar2(72) := g_package||'chk_calendar_start_time';
176 l_pattern csr_pattern_details%rowtype;
177 l_construction csr_pattern_construction%rowtype;
178 l_calendar_start_time date := p_calendar_start_time;
179 l_weekday varchar2 (3);
180 l_time varchar2 (5);
181 --
182 function TO_DAYS (
183 --
184 -- Convert user-defined time unit into days for ease of comparison and
185 -- manipulation.
186 --
187 quantity number,
188 units varchar2) return number is
189 --
190 conversion_factor number :=1;
191 --
192 begin
193 --
194 if units = 'H' then
195 conversion_factor := 24;
196 --
197 elsif units = 'W' then
198 conversion_factor := 1/7;
199 --
200 end if;
201 --
202 return (quantity / conversion_factor);
203 --
204 end to_days;
205 --
206 procedure check_parameters is
207 --
208 -- Check the main procedure parameters
209 --
210 begin
211 --
212 -- Check that the parameters are not null
213 --
214 hr_api.mandatory_arg_error
215 (p_api_name => l_proc,
216 p_argument => 'calendar_start_time',
217 p_argument_value => p_calendar_start_time);
218 --
219 hr_api.mandatory_arg_error
220 (p_api_name => l_proc,
221 p_argument => 'pattern_id',
222 p_argument_value => p_pattern_id);
223 --
224 hr_api.mandatory_arg_error
225 (p_api_name => l_proc,
226 p_argument => 'pattern_start_position',
227 p_argument_value => p_pattern_start_position);
228 --
229 end check_parameters;
230 --
231 begin
232 --
233 hr_utility.trace ('Entering '||l_proc);
234 --
235 check_parameters;
236 --
237 -- Get the pattern details
238 --
239 open csr_pattern_details;
240 fetch csr_pattern_details into l_pattern;
241 --
242 if csr_pattern_details%notfound then -- the pattern id must be invalid
243 close csr_pattern_details;
244 hr_cal_shd.constraint_error ('HR_CAL_PAT_FK');
245 else
246 close csr_pattern_details;
247 end if;
248 --
249 if p_pattern_start_position > 1 then
250 --
251 -- The start time validation must allow for the fact that, although the
252 -- pattern must start on, say, Wednesday and the calendar does not start on
253 -- a Wednesday, the calendar does not start on bit 1 of the pattern. Therefore
254 -- we must calculate where bit 1 of the pattern would start.
255 --
256 for offset in csr_pattern_construction LOOP
257 --
258 -- Set the virtual calendar start time back by the duration of the
259 -- pattern bit.
260 --
261 l_calendar_start_time := l_calendar_start_time
262 - to_days (offset.time_unit_multiplier,
263 offset.base_time_unit);
264 --
265 -- Stop when we get to the start position of the calendar
266 --
267 exit when csr_pattern_construction%rowcount = p_pattern_start_position;
268 --
269 end loop;
270 --
271 end if;
272 --
273 l_weekday := to_char (l_calendar_start_time, 'DY');
274 l_time := to_char (l_calendar_start_time, 'HH24:MI');
275 --
276 if l_weekday <> l_pattern.pattern_start_weekday then
277 --
278 -- The calendar does not start on the correct weekday
279 hr_utility.set_message (801, 'HR_51062_CAL_START_WEEKDAY');
280 hr_utility.set_message_token ('WEEKDAY',l_pattern.pattern_start_weekday);
281 hr_utility.raise_error;
282 --
283 elsif l_time <> l_pattern.pattern_start_time then
284 --
285 -- The calendar does not start at the correct time of day
286 hr_utility.set_message (801, 'HR_51063_CAL_START_TIME');
287 hr_utility.set_message_token ('START_TIME',l_pattern.pattern_start_time);
288 hr_utility.raise_error;
289 --
290 end if;
291 --
292 hr_utility.trace ('Leaving '||l_proc);
293 --
294 end chk_calendar_start_time;
295 -- ----------------------------------------------------------------------------
296 -- |---------------------------< insert_validate >----------------------------|
297 -- ----------------------------------------------------------------------------
298 Procedure insert_validate(p_rec in hr_cal_shd.g_rec_type) is
299 --
300 l_proc varchar2(72) := g_package||'insert_validate';
301 --
302 Begin
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 -- Call all supporting business operations
306 --
307 chk_pattern_start_position ( p_rec.pattern_start_position,
308 p_rec.pattern_id);
309 --
310 chk_calendar_start_time (p_calendar_start_time => p_rec.calendar_start_time,
311 p_pattern_start_position => p_rec.pattern_start_position,
312 p_pattern_id => p_rec.pattern_id);
313 --
314 chk_calendar_name (p_rec.calendar_name);
315 --
316 hr_utility.set_location(' Leaving:'||l_proc, 10);
317 --
318 End insert_validate;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |---------------------------< update_validate >----------------------------|
322 -- ----------------------------------------------------------------------------
323 Procedure update_validate(p_rec in hr_cal_shd.g_rec_type) is
324 --
325 l_proc varchar2(72) := g_package||'update_validate';
326 --
327 Begin
328 hr_utility.set_location('Entering:'||l_proc, 5);
329 --
330 -- Call all supporting business operations
331 --
332 chk_calendar_name (p_calendar_name => p_rec.calendar_name,
333 p_calendar_id => p_rec.calendar_id,
334 p_object_version_number => p_rec.object_version_number);
335 --
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 10);
338 End update_validate;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |---------------------------< delete_validate >----------------------------|
342 -- ----------------------------------------------------------------------------
343 Procedure delete_validate(p_rec in hr_cal_shd.g_rec_type) is
344 --
345 l_proc varchar2(72) := g_package||'delete_validate';
346 --
347 Begin
348 hr_utility.set_location('Entering:'||l_proc, 5);
349 --
350 -- Call all supporting business operations
351 --
352 hr_utility.set_location(' Leaving:'||l_proc, 10);
353 End delete_validate;
354 --
355 end hr_cal_bus;