1 Package Body hr_exu_bus as
2 /* $Header: hrexurhi.pkb 115.1 99/07/17 05:35:46 porting ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_exu_bus.'; -- Global package name
9 --
10 --
11 -- Business Validation Rules
12 --
13 -- --------------------------------------------------------------------------
14 -- |---------------------------< Check_No_ID_Conflict >---------------------|
15 -- --------------------------------------------------------------------------
16 --
17 -- PUBLIC
18 -- Description:
19 -- Check that there is no conflict on insert.i.e. that either
20 -- Calendar ID or Calendar Usage ID are entered on Insert and not
21 -- both.
22 --
23 Procedure Check_No_ID_Conflict (p_calendar_id in varchar2,
24 p_calendar_usage_id in varchar2) is
25
26 l_proc varchar2(72) := g_package||'Check_No_ID_Conflict';
27
28 cursor c1 is
29 select c.rowid
30 from hr_calendars c
31 where c.calendar_id = p_calendar_id;
32
33 c1_rec c1%ROWTYPE;
34
35 cursor c2 is
36 select u.rowid
37 from hr_calendar_usages u
38 where u.calendar_usage_id = p_calendar_usage_id;
39
40 c2_rec c2%ROWTYPE;
41
42 BEGIN
43 --
44 -- First check that both id's have not been entered
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 if p_calendar_usage_id is not null and p_calendar_id is not null then
48 -- *** NEW_MESSAGE_REQUIRED ***
49 fnd_message.set_name('PAY','EXCEP_USAGE_FK_CONFLICT');
50 fnd_message.raise_error;
51 elsif p_calendar_usage_id is null and p_calendar_id is null then
52 -- *** NEW_MESSAGE_REQUIRED ***
53 fnd_message.set_name('PAY','NO_FK_ENTERED');
54 fnd_message.raise_error;
55 end if;
56 --
57 -- No conflict now check that the FK entered is valid
58 --
59 if p_calendar_id is not null then
60 hr_utility.set_location(l_proc, 10);
61 open c1;
62 fetch c1 into c1_rec;
63 if c1%NOTFOUND then
64 -- *** NEW_MESSAGE_REQUIRED ***
65 fnd_message.set_name('PAY', 'INVALID CALENDAR_ID');
66 fnd_message.raise_error;
67 end if;
68 close c1;
69 else
70 hr_utility.set_location(l_proc, 15);
71 open c2;
72 fetch c2 into c2_rec;
73 if c2%NOTFOUND then
74 -- *** NEW_MESSAGE_REQUIRED ***
75 fnd_message.set_name('PAY','INVALID_CALENDAR_USAGE_ID');
76 fnd_message.raise_error;
77 end if;
78 close c2;
79 end if;
80 hr_utility.set_location(' Leaving:'||l_proc, 20);
81 END Check_No_ID_Conflict;
82 --
83 --
84 -- ------------------------------------------------------------------------
85 -- |-------------------------< Cal_Use_For_SSP >--------------------------|
86 -- ------------------------------------------------------------------------
87 --
88 -- PRIVATE
89 -- Description:
90 -- Boolean Function to determine wheteher the Calendar Usage is for
91 -- an SSP pattern purpose.
92 --
93 Function Cal_Use_For_SSP (p_calendar_usage_id in number)
94 return boolean is
95
96 l_proc varchar2(72) := g_package||'Cal_Use_For_SSP';
97
98 cursor c1 is
99 select '1'
100 from hr_calendar_usages hcu,
101 hr_calendars hc,
102 hr_patterns hp,
103 hr_pattern_purposes hpp
104 where hcu.calendar_usage_id = p_calendar_usage_id
105 and hcu.calendar_id = hc.calendar_id
106 and hc.pattern_id = hp.pattern_id
107 and hp.pattern_id = hpp.pattern_id
108 and hpp.pattern_purpose = 'SSP';
109
110 c1_rec c1%ROWTYPE;
111 BEGIN
112 hr_utility.set_location('Entering:'||l_proc, 5);
113 open c1;
114 fetch c1 into c1_rec;
115 if c1%NOTFOUND then
116 return(FALSE);
117 else
118 return(TRUE);
119 end if;
120 close c1;
121 hr_utility.set_location(' Leaving:'||l_proc, 10);
122 END Cal_Use_For_SSP;
123 --
124 --
125 -- ------------------------------------------------------------------------
126 -- |------------------------< Excep_is_Qual_Non_Qual >--------------------|
127 -- ------------------------------------------------------------------------
128 --
129 -- PRIVATE
130 -- Description:
131 -- Boolean Function to determine whether the exception used consists
132 -- only of availability values of 'QUALIFYING' and 'NON QUALIFYING'
133 --
134 Function Excep_is_Qual_Non_Qual (p_exception_id in number)
135 return boolean is
136
137 l_proc varchar2(72) := g_package||'Excep_is_Qual_Non_Qual';
138
139 cursor c2 is
140 select '1'
141 from hr_pattern_constructions hpc1,
142 hr_pattern_exceptions hpe1
143 where hpc1.pattern_id = hpe1.pattern_id
144 and hpe1.exception_id = p_exception_id
145 and not ( hpc1.availability = 'QUALIFYING'
146 or hpc1.availability = 'NON QUALIFYING')
147 UNION ALL
148 select '1'
149 from hr_pattern_constructions hpc2,
150 hr_pattern_constructions hpc3,
151 hr_pattern_exceptions hpe2
152 where hpc2.component_pattern_id = hpc3.pattern_id
153 and hpc2.pattern_id = hpe2.pattern_id
154 and hpe2.exception_id = p_exception_id
155 and not ( hpc2.availability = 'QUALIFYING'
156 or hpc2.availability = 'NON QUALIFYING');
157
158 c2_rec c2%ROWTYPE;
159
160 BEGIN
161 hr_utility.set_location('Entering:'||l_proc, 5);
162 open c2;
163 fetch c2 into c2_rec;
164 if c2%FOUND then
165 return(FALSE);
166 else
167 return(TRUE);
168 end if;
169 hr_utility.set_location(' Leaving:'||l_proc, 10);
170 END Excep_is_Qual_Non_Qual;
171 --
172 -- ------------------------------------------------------------------------
173 -- |---------------------------< Check_Exception_ID >---------------------|
174 -- ------------------------------------------------------------------------
175 --
176 -- PUBLIC
177 -- Description:
178 -- Ensure that a valid exception_id from hr_pattern_exceptions is
179 -- entered.
180 -- SSP_specific Rules:
181 -- ------------------
182 -- If the Calendar_usage_id is not null and the calendar usage is for
183 -- an SSP pattern purpose, then the exception_id must be for an
184 -- exception which consists only of availability values
185 -- `QUALIFYING' and 'NON QUALIFYING'
186 --
187 Procedure Check_Exception_Id (p_calendar_usage_id in number,
188 p_exception_id in number) is
189
190 l_proc varchar2(72) := g_package||'Check_Exception_ID';
191
192 BEGIN
193 hr_utility.set_location('Entering:'||l_proc, 5);
194
195 if p_calendar_usage_id is not null and
196 cal_use_for_ssp(p_calendar_usage_id) then
197 if excep_is_qual_non_qual(p_exception_id) then
198 null;
199 else
200 -- *** NEW_MESSAGE_REQUIRED ***
201 fnd_message.set_name('PAY','INVALID_EXCEPTION_ID');
202 fnd_message.raise_error;
203 end if;
204 end if;
205 hr_utility.set_location(' Leaving:'||l_proc, 10);
206 END Check_Exception_ID;
207 --
208 -- ------------------------------------------------------------------------
209 -- |---------------------------< Check_Unique >---------------------------|
210 -- ------------------------------------------------------------------------
211 --
212 -- PUBLIC
213 -- Description:
214 -- A pattern exception may not be applied to a calendar_id if another
215 -- pattern exception overlaps it in time ia already applied to the
216 -- calendar_id. However, if the first pattern is applied to a calendar_id
217 -- then a overlapping exception_pattern may be applied to a calendar_usage_id
218 -- and vice-versa.
219 --
220 Procedure Check_Unique (p_calendar_id in number,
221 p_calendar_usage_id in number,
222 p_exception_id in number) is
223
224 l_proc varchar2(72) := g_package||'Check_Unique';
225
226 cursor c1 is
227 select exp.exception_start_time start_time,
228 exp.exception_end_time end_time
229 from hr_pattern_exceptions exp
230 where exp.exception_id = p_exception_id;
231
232 c1_rec c1%ROWTYPE;
233
234 cursor c2 is
235 select '1'
236 from hr_exception_usages u,
237 hr_pattern_exceptions e
238 where u.calendar_id = p_calendar_id
239 and u.exception_id = e.exception_id
240 and e.exception_start_time <= c1_rec.end_time
241 and e.exception_end_time >= c1_rec.start_time
242 UNION ALL
243 select '1'
244 from hr_exception_usages u,
245 hr_pattern_exceptions e
246 where u.calendar_usage_id = p_calendar_usage_id
247 and u.exception_id = e.exception_id
248 and e.exception_start_time <= c1_rec.end_time
249 and e.exception_end_time >= c1_rec.start_time;
250
251 c2_rec c2%ROWTYPE;
252
253 BEGIN
254 hr_utility.set_location('Entering:'||l_proc, 5);
255 --
256 -- First get the start and end times of the pattern exception that
257 -- we wish to insert
258 --
259 open c1;
260 fetch c1 into c1_rec;
261 close c1;
262 --
263 -- Now see if there are any records returned by cursor2. If there
264 -- are, we have a failure.
265 --
266 hr_utility.set_location(l_proc, 10);
267 open c2;
268 fetch c2 into c2_rec;
269 if c2%FOUND then
270 close c2;
271 -- *** NEW_MESSAGE_REQUIRED ***
272 fnd_message.set_name('PAY','OVERLAPPING_EXCEPTIONS_ERROR');
273 fnd_message.raise_error;
274 end if;
275 hr_utility.set_location(' Leaving:'||l_proc, 20);
276 END Check_Unique;
277 --
278 -- ----------------------------------------------------------------------------
279 -- |---------------------------< insert_validate >----------------------------|
280 -- ----------------------------------------------------------------------------
281 Procedure insert_validate(p_rec in hr_exu_shd.g_rec_type) is
282 --
283 l_proc varchar2(72) := g_package||'insert_validate';
284 --
285 Begin
286 hr_utility.set_location('Entering:'||l_proc, 5);
287 --
288 -- Call all supporting business operations
289 --
290 -- Check that the Mandatory Column Exception_Id has been entered
291 --
292 hr_api.mandatory_arg_error (p_api_name => l_proc,
293 p_argument => 'Exception_Id',
294 p_argument_value => p_rec.exception_id);
295
296 hr_utility.set_location(l_proc, 10);
297
298 hr_exu_bus.Check_No_ID_Conflict(p_rec.calendar_id,
299 p_rec.calendar_usage_id);
300
301 hr_utility.set_location(l_proc, 15);
302 --
303 hr_exu_bus.Check_Exception_Id (p_rec.calendar_usage_id,
304 p_rec.exception_id);
305 --
306 hr_utility.set_location(l_proc, 20);
307 --
308 hr_exu_bus.Check_Unique (p_rec.calendar_id,
309 p_rec.calendar_usage_id,
310 p_rec.exception_id);
311 --
312 hr_utility.set_location(' Leaving:'||l_proc, 20);
313 End insert_validate;
314 --
315 -- ----------------------------------------------------------------------------
316 -- |---------------------------< update_validate >----------------------------|
317 -- ----------------------------------------------------------------------------
318 Procedure update_validate(p_rec in hr_exu_shd.g_rec_type) is
319 --
320 l_proc varchar2(72) := g_package||'update_validate';
321 --
322 Begin
323 hr_utility.set_location('Entering:'||l_proc, 5);
324 --
325 -- Call all supporting business operations
326 --
327 /* if (hr_exu_shd.api_updating
328 ( p_exception_usage_id => p_rec.exception_usage_id,
329 p_object_version_number => p_rec.object_version_number) and
330 nvl(p_rec.calendar_id, hr_exu.g_number) <>
331 nvl(hr_exu_shd.g_old_rec.calendar_id,hr_api.g_number)) then
332 (p_api_name => l_proc,
333 p_argument => 'calendar_id');
334 end if;
335
336 if (hr_exu_shd.api_updating
337 ( p_exception_usage_id => p_rec.exception_usage_id,
338 p_object_version_number => p_rec.object_version_number) and
339 nvl(p_rec.calendar_usage_id,hr_api.g_number) <>
340 nvl(hr_exu_shd.g_old_rec.calendar_usage_id,hr_api.g_varchar2)) then
341 (p_api_name => l_proc,
342 p_argument => 'calendar_usage_id');
343 end if;
344
345 if (hr_exu_shd.api_updating
346 ( p_exception_usage_id => p_rec.exception_usage_id,
347 p_object_version_number => p_rec.object_version_number) and
348 p_rec.exception_id <> hr_exu_shd.g_old_rec.exception_id) then
349 (p_api_name => l_proc,
350 p_argument => 'exception_id');
351 end if;
352 */
353 --
354 --
355 hr_utility.set_location(' Leaving:'||l_proc, 10);
356 End update_validate;
357 --
358 -- ----------------------------------------------------------------------------
359 -- |---------------------------< delete_validate >----------------------------|
360 -- ----------------------------------------------------------------------------
361 Procedure delete_validate(p_rec in hr_exu_shd.g_rec_type) is
362 --
363 l_proc varchar2(72) := g_package||'delete_validate';
364 --
365 Begin
366 hr_utility.set_location('Entering:'||l_proc, 5);
367 --
368 -- Call all supporting business operations
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End delete_validate;
372 --
373 end hr_exu_bus;
[Home] [Help]