1 Package Body pqp_aat_bus as
2 /* $Header: pqaatrhi.pkb 120.2.12010000.2 2008/08/05 13:56:35 ubhat ship $ */
3 --
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7 --
8 g_package varchar2(33) := ' pqp_aat_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_assignment_attribute_id number default null;
15 --
16 -- --------------------------------------------------------------------------+
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- --------------------------------------------------------------------------+
19 --
20 Procedure set_security_group_id
21 (p_assignment_attribute_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pqp_assignment_attributes_f aat
30 where aat.assignment_attribute_id = p_assignment_attribute_id
31 and pbg.business_group_id = aat.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'assignment_attribute_id'
47 ,p_argument_value => p_assignment_attribute_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- --------------------------------------------------------------------------+
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- --------------------------------------------------------------------------+
78 --
79 Function return_legislation_code
80 (p_assignment_attribute_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pqp_assignment_attributes_f aat
90 where aat.assignment_attribute_id = p_assignment_attribute_id
91 and pbg.business_group_id = aat.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'assignment_attribute_id'
107 ,p_argument_value => p_assignment_attribute_id
108 );
109 --
110 if ( nvl(pqp_aat_bus.g_assignment_attribute_id, hr_api.g_number)
111 = p_assignment_attribute_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pqp_aat_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pqp_aat_bus.g_assignment_attribute_id:= p_assignment_attribute_id;
142 pqp_aat_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ---------------------------------------------------------------------------+
149 -- |-----------------------------< chk_ddf >----------------------------------|
150 -- ---------------------------------------------------------------------------+
151 --
152 -- Description:
153 -- Validates all the Developer Descriptive Flexfield values.
154 --
155 -- Prerequisites:
156 -- All other columns have been validated. Must be called as the
157 -- last step from insert_validate and update_validate.
158 --
159 -- In Arguments:
160 -- p_rec
161 --
162 -- Post Success:
163 -- If the Developer Descriptive Flexfield structure column and data values
164 -- are all valid this procedure will end normally and processing will
165 -- continue.
166 --
167 -- Post Failure:
168 -- If the Developer Descriptive Flexfield structure column value or any of
169 -- the data values are invalid then an application error is raised as
170 -- a PL/SQL exception.
171 --
172 -- Access Status:
173 -- Internal Row Handler Use Only.
174 --
175 -- ---------------------------------------------------------------------------+
176 procedure chk_ddf
177 (p_rec in pqp_aat_shd.g_rec_type
178 ) is
179 --
180 l_proc varchar2(72) := g_package || 'chk_ddf';
181 --
182 begin
183 hr_utility.set_location('Entering:'||l_proc,10);
184 --
185 if ((p_rec.assignment_attribute_id is not null) and (
186 nvl(pqp_aat_shd.g_old_rec.aat_information_category, hr_api.g_varchar2) <>
187 nvl(p_rec.aat_information_category, hr_api.g_varchar2) or
188 nvl(pqp_aat_shd.g_old_rec.aat_information1, hr_api.g_varchar2) <>
189 nvl(p_rec.aat_information1, hr_api.g_varchar2) or
190 nvl(pqp_aat_shd.g_old_rec.aat_information2, hr_api.g_varchar2) <>
191 nvl(p_rec.aat_information2, hr_api.g_varchar2) or
192 nvl(pqp_aat_shd.g_old_rec.aat_information3, hr_api.g_varchar2) <>
193 nvl(p_rec.aat_information3, hr_api.g_varchar2) or
194 nvl(pqp_aat_shd.g_old_rec.aat_information4, hr_api.g_varchar2) <>
195 nvl(p_rec.aat_information4, hr_api.g_varchar2) or
196 nvl(pqp_aat_shd.g_old_rec.aat_information5, hr_api.g_varchar2) <>
197 nvl(p_rec.aat_information5, hr_api.g_varchar2) or
198 nvl(pqp_aat_shd.g_old_rec.aat_information6, hr_api.g_varchar2) <>
199 nvl(p_rec.aat_information6, hr_api.g_varchar2) or
200 nvl(pqp_aat_shd.g_old_rec.aat_information7, hr_api.g_varchar2) <>
201 nvl(p_rec.aat_information7, hr_api.g_varchar2) or
202 nvl(pqp_aat_shd.g_old_rec.aat_information8, hr_api.g_varchar2) <>
203 nvl(p_rec.aat_information8, hr_api.g_varchar2) or
204 nvl(pqp_aat_shd.g_old_rec.aat_information9, hr_api.g_varchar2) <>
205 nvl(p_rec.aat_information9, hr_api.g_varchar2) or
206 nvl(pqp_aat_shd.g_old_rec.aat_information10, hr_api.g_varchar2) <>
207 nvl(p_rec.aat_information10, hr_api.g_varchar2) or
208 nvl(pqp_aat_shd.g_old_rec.aat_information11, hr_api.g_varchar2) <>
209 nvl(p_rec.aat_information11, hr_api.g_varchar2) or
210 nvl(pqp_aat_shd.g_old_rec.aat_information12, hr_api.g_varchar2) <>
211 nvl(p_rec.aat_information12, hr_api.g_varchar2) or
212 nvl(pqp_aat_shd.g_old_rec.aat_information13, hr_api.g_varchar2) <>
213 nvl(p_rec.aat_information13, hr_api.g_varchar2) or
214 nvl(pqp_aat_shd.g_old_rec.aat_information14, hr_api.g_varchar2) <>
215 nvl(p_rec.aat_information14, hr_api.g_varchar2) or
216 nvl(pqp_aat_shd.g_old_rec.aat_information15, hr_api.g_varchar2) <>
217 nvl(p_rec.aat_information15, hr_api.g_varchar2) or
218 nvl(pqp_aat_shd.g_old_rec.aat_information16, hr_api.g_varchar2) <>
219 nvl(p_rec.aat_information16, hr_api.g_varchar2) or
220 nvl(pqp_aat_shd.g_old_rec.aat_information17, hr_api.g_varchar2) <>
221 nvl(p_rec.aat_information17, hr_api.g_varchar2) or
222 nvl(pqp_aat_shd.g_old_rec.aat_information18, hr_api.g_varchar2) <>
223 nvl(p_rec.aat_information18, hr_api.g_varchar2) or
224 nvl(pqp_aat_shd.g_old_rec.aat_information19, hr_api.g_varchar2) <>
225 nvl(p_rec.aat_information19, hr_api.g_varchar2) or
226 nvl(pqp_aat_shd.g_old_rec.aat_information20, hr_api.g_varchar2) <>
227 nvl(p_rec.aat_information20, hr_api.g_varchar2) ))
228 or (p_rec.assignment_attribute_id is null) then
229 --
230 -- Only execute the validation if absolutely necessary:
231 -- a) During update, the structure column value or any
232 -- of the attribute values have actually changed.
233 -- b) During insert.
234 --
235 hr_dflex_utility.ins_or_upd_descflex_attribs
236 (p_appl_short_name => 'PQP'
237 ,p_descflex_name => 'Extra Details Of Service DDF'
238 ,p_attribute_category => p_rec.aat_information_category
239 ,p_attribute1_name => 'AAT_INFORMATION1'
240 ,p_attribute1_value => p_rec.aat_information1
241 ,p_attribute2_name => 'AAT_INFORMATION2'
242 ,p_attribute2_value => p_rec.aat_information2
243 ,p_attribute3_name => 'AAT_INFORMATION3'
244 ,p_attribute3_value => p_rec.aat_information3
245 ,p_attribute4_name => 'AAT_INFORMATION4'
246 ,p_attribute4_value => p_rec.aat_information4
247 ,p_attribute5_name => 'AAT_INFORMATION5'
248 ,p_attribute5_value => p_rec.aat_information5
249 ,p_attribute6_name => 'AAT_INFORMATION6'
250 ,p_attribute6_value => p_rec.aat_information6
251 ,p_attribute7_name => 'AAT_INFORMATION7'
252 ,p_attribute7_value => p_rec.aat_information7
253 ,p_attribute8_name => 'AAT_INFORMATION8'
254 ,p_attribute8_value => p_rec.aat_information8
255 ,p_attribute9_name => 'AAT_INFORMATION9'
256 ,p_attribute9_value => p_rec.aat_information9
257 ,p_attribute10_name => 'AAT_INFORMATION10'
258 ,p_attribute10_value => p_rec.aat_information10
259 ,p_attribute11_name => 'AAT_INFORMATION11'
260 ,p_attribute11_value => p_rec.aat_information11
261 ,p_attribute12_name => 'AAT_INFORMATION12'
262 ,p_attribute12_value => p_rec.aat_information12
263 ,p_attribute13_name => 'AAT_INFORMATION13'
264 ,p_attribute13_value => p_rec.aat_information13
265 ,p_attribute14_name => 'AAT_INFORMATION14'
266 ,p_attribute14_value => p_rec.aat_information14
267 ,p_attribute15_name => 'AAT_INFORMATION15'
268 ,p_attribute15_value => p_rec.aat_information15
269 ,p_attribute16_name => 'AAT_INFORMATION16'
270 ,p_attribute16_value => p_rec.aat_information16
271 ,p_attribute17_name => 'AAT_INFORMATION17'
272 ,p_attribute17_value => p_rec.aat_information17
273 ,p_attribute18_name => 'AAT_INFORMATION18'
274 ,p_attribute18_value => p_rec.aat_information18
275 ,p_attribute19_name => 'AAT_INFORMATION19'
276 ,p_attribute19_value => p_rec.aat_information19
277 ,p_attribute20_name => 'AAT_INFORMATION20'
278 ,p_attribute20_value => p_rec.aat_information20
279 );
280 end if;
281 --
282 hr_utility.set_location(' Leaving:'||l_proc,20);
283 end chk_ddf;
284 --
285 -- ---------------------------------------------------------------------------+
286 -- |------------------------------< chk_df >----------------------------------|
287 -- ---------------------------------------------------------------------------+
288 --
289 -- Description:
290 -- Validates all the Descriptive Flexfield values.
291 --
292 -- Prerequisites:
293 -- All other columns have been validated. Must be called as the
294 -- last step from insert_validate and update_validate.
295 --
296 -- In Arguments:
297 -- p_rec
298 --
299 -- Post Success:
300 -- If the Descriptive Flexfield structure column and data values are
301 -- all valid this procedure will end normally and processing will
302 -- continue.
303 --
304 -- Post Failure:
305 -- If the Descriptive Flexfield structure column value or any of
306 -- the data values are invalid then an application error is raised as
307 -- a PL/SQL exception.
308 --
309 -- Access Status:
310 -- Internal Row Handler Use Only.
311 --
312 -- ---------------------------------------------------------------------------+
313 procedure chk_df
314 (p_rec in pqp_aat_shd.g_rec_type
315 ) is
316 --
317 l_proc varchar2(72) := g_package || 'chk_df';
318 --
319 begin
320 hr_utility.set_location('Entering:'||l_proc,10);
321 --
322 if ((p_rec.assignment_attribute_id is not null) and (
323 nvl(pqp_aat_shd.g_old_rec.assignment_attribute_id, hr_api.g_number) <>
324 nvl(p_rec.assignment_attribute_id, hr_api.g_number) or
325 nvl(pqp_aat_shd.g_old_rec.aat_attribute_category, hr_api.g_varchar2) <>
326 nvl(p_rec.aat_attribute_category, hr_api.g_varchar2) or
327 nvl(pqp_aat_shd.g_old_rec.aat_attribute1, hr_api.g_varchar2) <>
328 nvl(p_rec.aat_attribute1, hr_api.g_varchar2) or
329 nvl(pqp_aat_shd.g_old_rec.aat_attribute2, hr_api.g_varchar2) <>
330 nvl(p_rec.aat_attribute2, hr_api.g_varchar2) or
331 nvl(pqp_aat_shd.g_old_rec.aat_attribute3, hr_api.g_varchar2) <>
332 nvl(p_rec.aat_attribute3, hr_api.g_varchar2) or
333 nvl(pqp_aat_shd.g_old_rec.aat_attribute4, hr_api.g_varchar2) <>
334 nvl(p_rec.aat_attribute4, hr_api.g_varchar2) or
335 nvl(pqp_aat_shd.g_old_rec.aat_attribute5, hr_api.g_varchar2) <>
336 nvl(p_rec.aat_attribute5, hr_api.g_varchar2) or
337 nvl(pqp_aat_shd.g_old_rec.aat_attribute6, hr_api.g_varchar2) <>
338 nvl(p_rec.aat_attribute6, hr_api.g_varchar2) or
339 nvl(pqp_aat_shd.g_old_rec.aat_attribute7, hr_api.g_varchar2) <>
340 nvl(p_rec.aat_attribute7, hr_api.g_varchar2) or
341 nvl(pqp_aat_shd.g_old_rec.aat_attribute8, hr_api.g_varchar2) <>
342 nvl(p_rec.aat_attribute8, hr_api.g_varchar2) or
343 nvl(pqp_aat_shd.g_old_rec.aat_attribute9, hr_api.g_varchar2) <>
344 nvl(p_rec.aat_attribute9, hr_api.g_varchar2) or
345 nvl(pqp_aat_shd.g_old_rec.aat_attribute10, hr_api.g_varchar2) <>
346 nvl(p_rec.aat_attribute10, hr_api.g_varchar2) or
347 nvl(pqp_aat_shd.g_old_rec.aat_attribute11, hr_api.g_varchar2) <>
348 nvl(p_rec.aat_attribute11, hr_api.g_varchar2) or
349 nvl(pqp_aat_shd.g_old_rec.aat_attribute12, hr_api.g_varchar2) <>
350 nvl(p_rec.aat_attribute12, hr_api.g_varchar2) or
351 nvl(pqp_aat_shd.g_old_rec.aat_attribute13, hr_api.g_varchar2) <>
352 nvl(p_rec.aat_attribute13, hr_api.g_varchar2) or
353 nvl(pqp_aat_shd.g_old_rec.aat_attribute14, hr_api.g_varchar2) <>
354 nvl(p_rec.aat_attribute14, hr_api.g_varchar2) or
355 nvl(pqp_aat_shd.g_old_rec.aat_attribute15, hr_api.g_varchar2) <>
356 nvl(p_rec.aat_attribute15, hr_api.g_varchar2) or
357 nvl(pqp_aat_shd.g_old_rec.aat_attribute16, hr_api.g_varchar2) <>
358 nvl(p_rec.aat_attribute16, hr_api.g_varchar2) or
359 nvl(pqp_aat_shd.g_old_rec.aat_attribute17, hr_api.g_varchar2) <>
360 nvl(p_rec.aat_attribute17, hr_api.g_varchar2) or
361 nvl(pqp_aat_shd.g_old_rec.aat_attribute18, hr_api.g_varchar2) <>
362 nvl(p_rec.aat_attribute18, hr_api.g_varchar2) or
363 nvl(pqp_aat_shd.g_old_rec.aat_attribute19, hr_api.g_varchar2) <>
364 nvl(p_rec.aat_attribute19, hr_api.g_varchar2) or
365 nvl(pqp_aat_shd.g_old_rec.aat_attribute20, hr_api.g_varchar2) <>
366 nvl(p_rec.aat_attribute20, hr_api.g_varchar2) ))
367 or (p_rec.assignment_attribute_id is null) then
368 --
369 -- Only execute the validation if absolutely necessary:
370 -- a) During update, the structure column value or any
371 -- of the attribute values have actually changed.
372 -- b) During insert.
373 --
374 hr_dflex_utility.ins_or_upd_descflex_attribs
375 (p_appl_short_name => 'PQP'
376 ,p_descflex_name => 'Extra Details Of Service DF'
377 ,p_attribute_category => p_rec.aat_attribute_category
378 ,p_attribute1_name => 'AAT_ATTRIBUTE1'
379 ,p_attribute1_value => p_rec.aat_attribute1
380 ,p_attribute2_name => 'AAT_ATTRIBUTE2'
381 ,p_attribute2_value => p_rec.aat_attribute2
382 ,p_attribute3_name => 'AAT_ATTRIBUTE3'
383 ,p_attribute3_value => p_rec.aat_attribute3
384 ,p_attribute4_name => 'AAT_ATTRIBUTE4'
385 ,p_attribute4_value => p_rec.aat_attribute4
386 ,p_attribute5_name => 'AAT_ATTRIBUTE5'
387 ,p_attribute5_value => p_rec.aat_attribute5
388 ,p_attribute6_name => 'AAT_ATTRIBUTE6'
389 ,p_attribute6_value => p_rec.aat_attribute6
390 ,p_attribute7_name => 'AAT_ATTRIBUTE7'
391 ,p_attribute7_value => p_rec.aat_attribute7
392 ,p_attribute8_name => 'AAT_ATTRIBUTE8'
393 ,p_attribute8_value => p_rec.aat_attribute8
394 ,p_attribute9_name => 'AAT_ATTRIBUTE9'
395 ,p_attribute9_value => p_rec.aat_attribute9
396 ,p_attribute10_name => 'AAT_ATTRIBUTE10'
397 ,p_attribute10_value => p_rec.aat_attribute10
398 ,p_attribute11_name => 'AAT_ATTRIBUTE11'
399 ,p_attribute11_value => p_rec.aat_attribute11
400 ,p_attribute12_name => 'AAT_ATTRIBUTE12'
401 ,p_attribute12_value => p_rec.aat_attribute12
402 ,p_attribute13_name => 'AAT_ATTRIBUTE13'
403 ,p_attribute13_value => p_rec.aat_attribute13
404 ,p_attribute14_name => 'AAT_ATTRIBUTE14'
405 ,p_attribute14_value => p_rec.aat_attribute14
406 ,p_attribute15_name => 'AAT_ATTRIBUTE15'
407 ,p_attribute15_value => p_rec.aat_attribute15
408 ,p_attribute16_name => 'AAT_ATTRIBUTE16'
409 ,p_attribute16_value => p_rec.aat_attribute16
410 ,p_attribute17_name => 'AAT_ATTRIBUTE17'
411 ,p_attribute17_value => p_rec.aat_attribute17
412 ,p_attribute18_name => 'AAT_ATTRIBUTE18'
413 ,p_attribute18_value => p_rec.aat_attribute18
414 ,p_attribute19_name => 'AAT_ATTRIBUTE19'
415 ,p_attribute19_value => p_rec.aat_attribute19
416 ,p_attribute20_name => 'AAT_ATTRIBUTE20'
417 ,p_attribute20_value => p_rec.aat_attribute20
418 );
419 end if;
420 --
421 hr_utility.set_location(' Leaving:'||l_proc,20);
422 end chk_df;
423 --
424 -- ---------------------------------------------------------------------------+
425 -- |-----------------------< chk_non_updateable_args >------------------------|
426 -- ---------------------------------------------------------------------------+
427 -- {Start Of Comments}
428 --
429 -- Description:
430 -- This procedure is used to ensure that non updateable attributes have
431 -- not been updated. If an attribute has been updated an error is generated.
432 --
433 -- Pre Conditions:
434 -- g_old_rec has been populated with details of the values currently in
435 -- the database.
436 --
437 -- In Arguments:
438 -- p_rec has been populated with the updated values the user would like the
439 -- record set to.
440 --
441 -- Post Success:
442 -- Processing continues if all the non updateable attributes have not
443 -- changed.
444 --
445 -- Post Failure:
446 -- An application error is raised if any of the non updatable attributes
447 -- have been altered.
448 --
449 -- {End Of Comments}
450 -- ---------------------------------------------------------------------------+
451 Procedure chk_non_updateable_args
452 (p_effective_date in date
453 ,p_rec in pqp_aat_shd.g_rec_type
454 ) IS
455 --
456 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
457 l_error EXCEPTION;
458 l_argument varchar2(30);
459 --
460 Begin
461 --
462 -- Only proceed with the validation if a row exists for the current
463 -- record in the HR Schema.
464 --
465 IF NOT pqp_aat_shd.api_updating
466 (p_assignment_attribute_id => p_rec.assignment_attribute_id
467 ,p_effective_date => p_effective_date
468 ,p_object_version_number => p_rec.object_version_number
469 ) THEN
470 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
471 fnd_message.set_token('PROCEDURE ', l_proc);
472 fnd_message.set_token('STEP ', '5');
473 fnd_message.raise_error;
474 END IF;
475 --
476 --
477 EXCEPTION
478 WHEN l_error THEN
479 hr_api.argument_changed_error
480 (p_api_name => l_proc
481 ,p_argument => l_argument);
482 WHEN OTHERS THEN
483 RAISE;
484 End chk_non_updateable_args;
485 --
486 -- ---------------------------------------------------------------------------+
487 -- |-----------------------< chk_work_pattern_cols >------------------------|
488 -- ---------------------------------------------------------------------------+
489 -- {Start Of Comments}
490 --
491 -- Description:
492 -- This procedure is used to ensure that if one of the work pattern columns
493 -- is entered, then the other one is not left NULL.
494 -- Added for bugfix 2651375
495 --
496 -- {End Of Comments}
497 -- ---------------------------------------------------------------------------+
498 Procedure chk_work_pattern_cols
499 (p_work_pattern in varchar2
500 ,p_start_day in varchar2
501 ) IS
502 --
503 l_proc varchar2(72) := g_package || 'chk_work_pattern_cols';
504 --
505 Begin
506 --
507 hr_utility.set_location(' Entering: '|| l_proc, 5);
508
509 if p_work_pattern is not null
510 or
511 p_start_day is not null then
512 --
513 hr_api.mandatory_arg_error
514 (p_api_name => l_proc
515 ,p_argument => 'Work_Pattern'
516 ,p_argument_value => p_work_pattern
517 );
518
519 hr_api.mandatory_arg_error
520 (p_api_name => l_proc
521 ,p_argument => 'Start_Day'
522 ,p_argument_value => p_start_day
523 );
524 --
525 end if;
526
527 hr_utility.set_location(' Leaving:'|| l_proc, 10);
528 --
529 end chk_work_pattern_cols;
530 --
531 -- ---------------------------------------------------------------------------+
532 -- |-----------------------< chk_private_company_car >------------------------|
533 -- ---------------------------------------------------------------------------+
534 -- {Start Of Comments}
535 --
536 -- Description:
537 -- This procedure is used to ensure that an assignment does not have both a
538 -- private and a company car.
539 --
540 -- {End Of Comments}
541 -- ---------------------------------------------------------------------------+
542 Procedure chk_private_company_car
543 (p_primary_company_car in number,
544 p_secondary_company_car in number,
545 p_private_car in number
546 ) IS
547 --
548 l_proc varchar2(72) := g_package || 'chk_private_company_car';
549 --
550 Begin
551 --
552 hr_utility.set_location(' Entering: '|| l_proc, 5);
553
554 if (p_private_car is not null) and
555 (p_primary_company_car is not null or
556 p_secondary_company_car is not null) then
557 --
558 hr_utility.set_message(8303, 'PQP_230519_INVALID_OWNERSHIPS');
559 hr_utility.raise_error;
560 --
561 end if;
562
563 hr_utility.set_location(' Leaving:'|| l_proc, 10);
564 --
565 end chk_private_company_car;
566 --
567 -- ---------------------------------------------------------------------------+
568 -- |-----------------------< chk_primary_exists >-----------------------------|
569 -- ---------------------------------------------------------------------------+
570 -- {Start Of Comments}
571 --
572 -- Description:
573 -- This procedure is used to ensure that an assignment does not have a
574 -- secondary company car without a primary company car.
575 --
576 -- {End Of Comments}
577 -- ---------------------------------------------------------------------------+
578 Procedure chk_primary_exists
579 (p_primary_company_car in number,
580 p_secondary_company_car in number
581 ) IS
582 --
583 l_proc varchar2(72) := g_package || 'chk_primary_exists';
584 --
585 Begin
586 --
587 hr_utility.set_location(' Entering: '|| l_proc, 5);
588
589 if p_primary_company_car is null and
590 p_secondary_company_car is not null then
591 --
592 hr_utility.set_message(8303, 'PQP_230525_PRIMARY_CAR_NULL');
593 hr_utility.raise_error;
594 --
595 end if;
596
597 hr_utility.set_location(' Leaving: '|| l_proc, 10);
598 --
599 end chk_primary_exists;
600 --
601 -- ---------------------------------------------------------------------------+
602 -- |-----------------------< chk_prim_sec_duplicate >-------------------------|
603 -- ---------------------------------------------------------------------------+
604 -- {Start Of Comments}
605 --
606 -- Description:
607 -- This procedure is used to ensure that the same car is not assigned as
608 -- both primary and secondary car to the same person
609 --
610 -- {End Of Comments}
611 -- ---------------------------------------------------------------------------+
612 Procedure chk_prim_sec_duplicate
613 (p_primary_company_car in number,
614 p_secondary_company_car in number
615 ) IS
616 --
617 l_proc varchar2(72) := g_package || 'chk_prim_sec_duplicate';
618 --
619 Begin
620 --
621 hr_utility.set_location(' Entering: '|| l_proc, 5);
622
623 if p_primary_company_car = p_secondary_company_car then
624 --
625 hr_utility.set_message(8303, 'PQP_230524_PRI_SEC_CAR_MATCH');
626 hr_utility.raise_error;
627 --
628 end if;
629
630 hr_utility.set_location(' Leaving: '|| l_proc, 10);
631 --
632 end chk_prim_sec_duplicate;
633 --
634 -- ---------------------------------------------------------------------------+
635 -- |-----------------------< chk_company_car_duplicate_asg >------------------|
636 -- ---------------------------------------------------------------------------+
637 -- {Start Of Comments}
638 --
639 -- Description:
640 -- This procedure is used to ensure that the same car is not given to more
641 -- than one assignment at a time.
642 --
643 -- {End Of Comments}
644 -- ---------------------------------------------------------------------------+
645 Procedure chk_company_car_duplicate_asg
646 (p_primary_company_car in number,
647 p_secondary_company_car in number,
648 p_assignment_id in number,
649 p_validation_start_date in date,
650 p_validation_end_date in date
651 ) IS
652 --
653 l_proc varchar2(72) := g_package || 'chk_company_car_duplicate_asg';
654 l_result number;
655
656 cursor csr_asg_duplicate is
657 select 1
658 from pqp_assignment_attributes_f
659 where (assignment_id > p_assignment_id OR assignment_id < p_assignment_id) -- for bug 6871534
660 and (primary_company_car in (p_primary_company_car, p_secondary_company_car) or
661 secondary_company_car in (p_primary_company_car, p_secondary_company_car))
662 and p_validation_start_date <= effective_end_date
663 and p_validation_end_date >= effective_start_date;
664
665 --
666 Begin
667 --
668 hr_utility.set_location(' Entering: '|| l_proc, 5);
669
670 open csr_asg_duplicate;
671 fetch csr_asg_duplicate into l_result;
672
673 if csr_asg_duplicate%found then
674 --
675 close csr_asg_duplicate;
676
677 hr_utility.set_message(8303, 'PQP_230526_VEH_ASG_DUP');
678 hr_utility.raise_error;
679 --
680 else
681 --
682 close csr_asg_duplicate;
683 --
684 end if;
685
686 hr_utility.set_location(' Leaving: '|| l_proc, 10);
687 --
688 end chk_company_car_duplicate_asg;
689 --
690 --
691 -- ---------------------------------------------------------------------------+
692 -- |-----------------------< chk_asg_overlap >--------------------------------|
693 -- ---------------------------------------------------------------------------+
694 -- {Start Of Comments}
695 --
696 -- Description:
697 -- This procedure is used to ensure that one assignment cannot have
698 -- more than one date overlapping record in the assignment attribute table
699 --
700 -- {End Of Comments}
701 -- ---------------------------------------------------------------------------+
702 Procedure chk_asg_overlap
703 (p_assignment_attribute_id in number,
704 p_assignment_id in number,
705 p_validation_start_date in date,
706 p_validation_end_date in date
707 ) IS
708 --
709 l_proc varchar2(72) := g_package || 'chk_asg_overlap';
710 l_result number;
711
712 cursor csr_asg_duplicate is
713 select 1
714 from pqp_assignment_attributes_f
715 where assignment_id = p_assignment_id
716 and assignment_attribute_id <> nvl(p_assignment_attribute_id, -1)
717 and p_validation_start_date <= effective_end_date
718 and p_validation_end_date >= effective_start_date;
719
720 Begin
721 --
722 hr_utility.set_location(' Entering: '|| l_proc, 5);
723
724 open csr_asg_duplicate;
725 fetch csr_asg_duplicate into l_result;
726
727 if csr_asg_duplicate%found then
728 --
729 close csr_asg_duplicate;
730
731 hr_utility.set_message(8303, 'PQP_230528_ASG_ATTR_DUP');
732 hr_utility.raise_error;
733 --
734 else
735 --
736 close csr_asg_duplicate;
737 --
738 end if;
739
740 hr_utility.set_location(' Leaving: '|| l_proc, 10);
741 --
742 end chk_asg_overlap;
743 --
744 --
745 -- ---------------------------------------------------------------------------+
746 -- |-----------------------< chk_table_exists >-------------------------------|
747 -- ---------------------------------------------------------------------------+
748 -- {Start Of Comments}
749 --
750 -- Description:
751 -- This procedure is used to ensure that the Rates table ( Comp/Private)
752 -- are valid for that BG.
753 --
754 -- {End Of Comments}
755 -- ---------------------------------------------------------------------------+
756 Procedure chk_table_exists
757 (p_rates_table_id in number,
758 p_business_group_id in number
759 ) IS
760 --
761 l_proc varchar2(72) := g_package || ' chk_table_exists';
762 l_dummy varchar2(1);
763
764 CURSOR cur_tbl IS
765 SELECT 'x'
766 FROM pay_user_tables
767 WHERE user_table_id = p_rates_table_id
768 AND business_group_id = p_business_group_id;
769 --
770 Begin
771 --
772 hr_utility.set_location(' Entering: '|| l_proc, 5);
773
774 OPEN cur_tbl;
775 FETCH cur_tbl INTO l_dummy;
776 IF cur_tbl%NOTFOUND THEN
777 -- Error
778 CLOSE cur_tbl;
779 fnd_message.set_name('PQP', 'PQP_230527_RATES_TABLE_INVALID');
780 fnd_message.raise_error;
781 END IF;
782
783 CLOSE cur_tbl;
784
785 hr_utility.set_location(' Leaving: '|| l_proc, 10);
786 --
787 end chk_table_exists;
788
789 --
790 -- ---------------------------------------------------------------------------+
791 -- |-----------------------< chk_tp_is_teacher >------------------------------|
792 -- ---------------------------------------------------------------------------+
793 -- {Start Of Comments}
794 --
795 -- Description:
796 -- This procedure is used to validate tp_is_teacher against
797 -- HR_LOOKUP.LOOKUP_CODE where LOOKUP_TYPE is 'PQP_GB_TEACHER_JOB_STATUS'.
798 --
799 -- Pre Conditions:
800 --
801 -- In Arguments:
802 -- p_effective_date
803 -- p_tp_is_teacher
804 --
805 -- Post Success:
806 -- Processing continues
807 --
808 -- Post Failure:
809 -- An application error will be raised and processing is
810 -- terminated
811 --
812 -- {End Of Comments}
813 -- ---------------------------------------------------------------------------+
814 Procedure chk_tp_is_teacher
815 (p_assignment_attribute_id in number
816 ,p_tp_is_teacher in varchar2
817 ,p_effective_date in date
818 ,p_validation_start_date in date
819 ,p_validation_end_date in date
820 ) IS
821 --
822 l_proc varchar2(72) := g_package || 'chk_tp_is_teacher';
823 --
824 Begin
825 --
826 hr_utility.set_location(' Entering: '|| l_proc, 10);
827
828 if (((p_assignment_attribute_id is not null) and
829 nvl(pqp_aat_shd.g_old_rec.tp_is_teacher,
830 hr_api.g_varchar2) <> nvl(p_tp_is_teacher,
831 hr_api.g_varchar2))
832 or
833 (p_assignment_attribute_id is null)) then
834
835 hr_utility.set_location(l_proc, 20);
836 --
837 if p_tp_is_teacher is not null then
838 if hr_api.not_exists_in_dt_hr_lookups
839 (p_effective_date => p_effective_date
840 ,p_validation_start_date => p_validation_start_date
841 ,p_validation_end_date => p_validation_end_date
842 ,p_lookup_type => 'PQP_GB_TEACHER_JOB_STATUS'
843 ,p_lookup_code => p_tp_is_teacher
844 ) then
845
846 -- Invalid Job Status
847 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
848 fnd_message.set_token('COLUMN_NAME', 'TP_IS_TEACHER');
849 fnd_message.raise_error;
850 end if;
851 hr_utility.set_location(l_proc, 30);
852 end if;
853 end if;
854 hr_utility.set_location(' Leaving: '|| l_proc, 40);
855 --
856 end chk_tp_is_teacher;
857 --
858 -- ---------------------------------------------------------------------------+
859 -- |----------< chk_tp_headteacher_grp_code >------------------------------|
860 -- ---------------------------------------------------------------------------+
861 -- {Start Of Comments}
862 --
863 -- Description:
864 -- This procedure is used to validate tp_headteacher_grp_code against
865 -- the condition that it should be a valid numeric code with length equals to 2.
866 --
867 -- Pre Conditions:
868 --
869 -- In Arguments:
870 -- p_assignment_attribute_id
871 -- p_tp_is_teacher
872 --
873 --
874 -- Post Success:
875 -- Processing continues
876 --
877 -- Post Failure:
878 -- An application warning will be raised and processing is
879 -- terminated
880 --
881 -- {End Of Comments}
882 -- ---------------------------------------------------------------------------+
883
884 Procedure chk_tp_headteacher_grp_code
885 (p_assignment_attribute_id in number
886 ,p_tp_is_teacher in varchar2
887 ,p_tp_headteacher_grp_code in number
888 ) IS
889 --
890 l_proc varchar2(72) := g_package || 'chk_tp_headteacher_grp_code';
891 --
892 Begin
893 --
894 hr_utility.set_location(' Entering: '|| l_proc, 10);
895
896 if (((p_assignment_attribute_id is not null) and
897 nvl(pqp_aat_shd.g_old_rec.tp_is_teacher,
898 hr_api.g_varchar2) <> nvl(p_tp_is_teacher,
899 hr_api.g_varchar2))
900 or
901 (p_assignment_attribute_id is null)) then
902
903 hr_utility.set_location(l_proc, 20);
904 --
905 --115.19 changed the range from 0 to 99 to 1 to 99
906 --for the HeadTeacher Group Code as length of the numeric code is 2
907 -- and it cannot be possibly '00'
908 if p_tp_is_teacher is not null then
909 if ((p_tp_headteacher_grp_code IS NOT NULL) and ( p_tp_headteacher_grp_code NOT BETWEEN 1 and 99 )) THEN
910 -- Invalid headteacher group code
911 fnd_message.set_name( 'PQP','PQP_230204_TP_INVALID_GRP_CODE');
912 fnd_message.set_token('COLUMN_NAME', 'TP_HEADTEACHER_GRP_CODE' );
913 fnd_message.raise_error;
914 end if;
915 hr_utility.set_location(l_proc, 30);
916 end if;
917 end if;
918 hr_utility.set_location(' Leaving: '|| l_proc, 40);
919 --
920 end chk_tp_headteacher_grp_code;
921 --
922
923
924 -- ---------------------------------------------------------------------------+
925 -- |-----------------------< chk_tp_elected_pension >-------------------------|
926 -- ---------------------------------------------------------------------------+
927 -- {Start Of Comments}
928 --
929 -- Description:
930 -- This procedure is used to validate tp_elected_pension against
931 -- HR_LOOKUP.LOOKUP_CODE where LOOKUP_TYPE is 'YES_NO'.
932 --
933 -- Pre Conditions:
934 --
935 -- In Arguments:
936 -- p_effective_date
937 -- p_tp_elected_pension
938 --
939 -- Post Success:
940 -- Processing continues
941 --
942 -- Post Failure:
943 -- An application error will be raised and processing is
944 -- terminated
945 --
946 -- {End Of Comments}
947 -- ---------------------------------------------------------------------------+
948 Procedure chk_tp_elected_pension
949 (p_assignment_attribute_id in number
950 ,p_tp_elected_pension in varchar2
951 ,p_effective_date in date
952 ,p_validation_start_date in date
953 ,p_validation_end_date in date
954 ) IS
955 --
956 l_proc varchar2(72) := g_package || 'chk_tp_elected_pension';
957 --
958 Begin
959 --
960 hr_utility.set_location(' Entering: '|| l_proc, 10);
961
962 if (((p_assignment_attribute_id is not null) and
963 nvl(pqp_aat_shd.g_old_rec.tp_elected_pension,
964 hr_api.g_varchar2) <> nvl(p_tp_elected_pension,
965 hr_api.g_varchar2))
966 or
967 (p_assignment_attribute_id is null)) then
968
969 hr_utility.set_location(l_proc, 20);
970 --
971 if p_tp_elected_pension is not null then
972 if hr_api.not_exists_in_dt_hr_lookups
973 (p_effective_date => p_effective_date
974 ,p_validation_start_date => p_validation_start_date
975 ,p_validation_end_date => p_validation_end_date
976 ,p_lookup_type => 'YES_NO'
977 ,p_lookup_code => p_tp_elected_pension
978 ) then
979
980 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
981 fnd_message.set_token('COLUMN_NAME', 'TP_ELECTED_PENSION');
982 fnd_message.raise_error;
983
984 end if;
985 hr_utility.set_location(l_proc, 30);
986 end if;
987 end if;
988
989 hr_utility.set_location(' Leaving: '|| l_proc, 40);
990 --
991 end chk_tp_elected_pension;
992 --
993 -- ---------------------------------------------------------------------------+
994 -- |-----------------------< chk_tp_safeguarded_grade >-----------------------|
995 -- ---------------------------------------------------------------------------+
996 -- {Start Of Comments}
997 --
998 -- Description:
999 -- This procedure is used to validate tp_safeguarded_grade against
1000 -- the format ANN where A is upper case aplha and N is number.
1001 --
1002 -- Pre Conditions:
1003 --
1004 -- In Arguments:
1005 -- p_tp_safeguarded_grade
1006 -- p_tp_safeguarded_grade_id
1007 --
1008 -- Post Success:
1009 -- Processing continues
1010 --
1011 -- Post Failure:
1012 -- An application error will be raised and processing is
1013 -- terminated
1014 --
1015 -- {End Of Comments}
1016 -- ---------------------------------------------------------------------------+
1017 Procedure chk_tp_safeguarded_grade
1018 (p_assignment_attribute_id in number
1019 ,p_tp_safeguarded_grade in varchar2
1020 ,p_tp_safeguarded_grade_id in number
1021 ,p_effective_date in date
1022 ,p_validation_start_date in date
1023 ,p_validation_end_date in date
1024 ) IS
1025 --
1026 Cursor ChkFormat is
1027 Select 'Y'
1028 From dual
1029 Where length(nvl(p_tp_safeguarded_grade,'x')) <= 3
1030 and ascii( substr(p_tp_safeguarded_grade,1,1)) between 65 and 90
1031 and (-- Either both 2nd and 3rd chars are alpha
1032 (ascii( substr(p_tp_safeguarded_grade,2,1)) between 65 and 90
1033 AND
1034 ascii( substr(p_tp_safeguarded_grade,3,1)) between 65 and 90
1035 )
1036 OR -- 2nd and 3rd chars r both numbers
1037 (ascii( substr(p_tp_safeguarded_grade,2,1)) between 48 and 57
1038 AND
1039 ascii( substr(p_tp_safeguarded_grade,3,1)) between 48 and 57
1040 )
1041 );
1042 --
1043 Cursor ChkGradeValid is
1044 Select 'Y'
1045 From per_grades pg
1046 Where pg.grade_id = p_tp_safeguarded_grade_id;
1047 --
1048 l_proc varchar2(72) := g_package || 'chk_tp_safeguarded_grade';
1049 l_FormatValid char(1) := 'N';
1050 l_GradeValid char(1) := 'N';
1051 --
1052 Begin
1053 --
1054 hr_utility.set_location(' Entering: '|| l_proc, 10);
1055
1056 if (((p_assignment_attribute_id is not null) and
1057 nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_grade,
1058 hr_api.g_varchar2) <> nvl(p_tp_safeguarded_grade,
1059 hr_api.g_varchar2))
1060 or
1061 (p_assignment_attribute_id is null)) then
1062
1063 hr_utility.set_location(l_proc, 20);
1064 --
1065 if p_tp_safeguarded_grade is not null then
1066 --
1067 begin
1068 open ChkFormat;
1069 fetch ChkFormat into l_FormatValid;
1070 close ChkFormat;
1071 exception
1072 when others then
1073 l_FormatValid := 'N';
1074 end;
1075 --
1076 if l_FormatValid = 'N' then
1077 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
1078 fnd_message.set_token('COLUMN_NAME', 'TP_SAFEGUARDED_GRADE');
1079 fnd_message.raise_error;
1080 else -- l_FormatValid = 'Y'
1081 -- Validate the grade
1082 begin
1083 open ChkGradeValid;
1084 fetch ChkGradeValid into l_GradeValid;
1085 close ChkGradeValid;
1086 exception
1087 when others then
1088 l_GradeValid := 'N';
1089 end;
1090 --
1091 if l_GradeValid = 'N' then
1092 fnd_message.set_name('PAY', 'PQP_230573_INVALID_GRADE');
1093 fnd_message.raise_error;
1094 end if;
1095 end if; -- l_FormatValid = 'N'
1096 hr_utility.set_location(l_proc, 30);
1097 end if; -- p_tp_safeguarded_grade is not null
1098 end if; -- (((p_assignment_attribute_id is not null) and
1099
1100 hr_utility.set_location(' Leaving: '|| l_proc, 40);
1101 --
1102 end chk_tp_safeguarded_grade;
1103 --
1104 -- ---------------------------------------------------------------------------+
1105 -- |-----------------------< chk_job_status_change >--------------------------|
1106 -- ---------------------------------------------------------------------------+
1107 -- {Start Of Comments}
1108 --
1109 -- Description:
1110 -- This procedure is used to check if the job status change is valid.
1111 -- The rules governing the job status change are defined in the
1112 -- design document.
1113 --
1114 -- Pre Conditions:
1115 --
1116 -- In Arguments:
1117 -- New Job Status
1118 -- Old Job Status
1119 -- Assignment Id
1120 -- Effective Date
1121 -- Datetrack Mode
1122 --
1123 -- Post Success:
1124 -- Processing continues
1125 --
1126 -- Post Failure:
1127 -- An application error will be raised and processing is
1128 -- terminated
1129 --
1130 -- {End Of Comments}
1131 -- ---------------------------------------------------------------------------+
1132 Procedure chk_job_status_change
1133 (p_new_job_status in varchar2
1134 ,p_old_job_status in varchar2
1135 ,p_assignment_id in number
1136 ,p_effective_date in date
1137 ,p_datetrack_mode in varchar2
1138 ) IS
1139
1140 -- Cursor Declaration
1141
1142 Cursor C_TCHR_TTR6_History Is
1143 Select '1'
1144 From pqp_assignment_attributes_f
1145 Where assignment_id = p_assignment_id
1146 and effective_end_date < p_effective_date
1147 and ( tp_is_teacher = 'TCHR'
1148 or tp_is_teacher = 'TTR6');
1149
1150
1151 Cursor C_TTR6_Future Is
1152 Select '1'
1153 From pqp_assignment_attributes_f
1154 Where assignment_id = p_assignment_id
1155 and effective_start_date > p_effective_date
1156 and tp_is_teacher = 'TTR6';
1157
1158 -- Local Variable Declaration
1159 l_proc varchar2(72) := g_package || 'chk_job_status_change';
1160
1161 l_Temp varchar2(1);
1162 l_TCHR_TTR6_history boolean := FALSE;
1163 l_TTR6_future boolean := FALSE;
1164 --
1165 Begin
1166 --
1167 hr_utility.set_location(' Entering: '|| l_proc, 5);
1168 --
1169
1170 -- Check only if tp_is_teacher has changed and is not null
1171 if (nvl(p_old_job_status,hr_api.g_varchar2) <>
1172 nvl(p_new_job_status,hr_api.g_varchar2)
1173 ) and
1174 (p_new_job_status is not null)
1175 then -- 0
1176
1177 /* Evaluate history and future flags. These flag values can be
1178 re-used in UPDATE mode checks.
1179 */
1180 --
1181 -- Check 'TCHR' and 'TTR6' history
1182 --
1183 open C_TCHR_TTR6_History;
1184 fetch C_TCHR_TTR6_History into l_Temp;
1185 if C_TCHR_TTR6_History%FOUND then
1186 l_TCHR_TTR6_history := TRUE;
1187 end if;
1188 close C_TCHR_TTR6_History;
1189 --
1190 -- Check 'TTR6' future
1191 --
1192 open C_TTR6_Future;
1193 fetch C_TTR6_Future into l_Temp;
1194 if C_TTR6_Future%FOUND then
1195 l_TTR6_future := TRUE;
1196 end if;
1197 close C_TTR6_Future;
1198
1199 -- Mode : CORRECTION -------------------------------------------+
1200 if p_datetrack_mode = hr_api.g_correction then
1201 -- 1
1202
1203 /* For new = 'TTR6'
1204 Rules Handled : 2 and 6 Refer design doc for rules table */
1205 if p_new_job_status = 'TTR6' then
1206 -- 2
1207
1208 if l_TCHR_TTR6_History then
1209 -- 3
1210 pqp_aat_shd.constraint_error
1211 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1212 end if; -- 3
1213
1214 if l_TTR6_Future then
1215 -- 4
1216 pqp_aat_shd.constraint_error
1217 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1218 end if; -- 4
1219 --
1220 end if; -- 2
1221
1222 /* For old = 'NONT' and new = 'TCHR'
1223 Rules Handled : 1. Refer design doc for rules table */
1224 if p_old_job_status = 'NONT' and p_new_job_status = 'TCHR' then
1225 -- 5
1226 if l_TTR6_Future then
1227 -- 6
1228 pqp_aat_shd.constraint_error
1229 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1230 end if; -- 6
1231
1232 end if; -- 5
1233
1234 end if; -- 1
1235
1236
1237 -- Mode : UPDATE -------------------------------------------+
1238 /* For old = 'TCHR' and new = 'TTR6'
1239 Rules Handled : 8(Golder Rule) Refer design doc for rules table */
1240 if (p_datetrack_mode = hr_api.g_update or
1241 p_datetrack_mode = hr_api.g_update_override or
1242 p_datetrack_mode = hr_api.g_update_change_insert) then
1243 -- 7
1244 if p_old_job_status = 'TCHR' and p_new_job_status = 'TTR6' then
1245 -- 8
1246 pqp_aat_shd.constraint_error
1247 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1248 end if; -- 8
1249
1250 end if; -- 7
1251
1252 if (p_datetrack_mode = hr_api.g_update or
1253 p_datetrack_mode = hr_api.g_update_change_insert) then
1254 -- 11
1255
1256 /* For old = 'NONT' and (new = 'TTR6' or 'TCHR')
1257 Rules Handled : 3 and 4(partly) Refer design doc for rules table */
1258 if p_old_job_status = 'NONT' and
1259 (p_new_job_status = 'TCHR' or p_new_job_status = 'TTR6') then
1260 -- 12
1261
1262 if l_TTR6_Future then
1263 -- 13
1264 pqp_aat_shd.constraint_error
1265 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1266 end if; -- 13
1267
1268 /* For old = 'NONT' and new = 'TTR6'
1269 Rules Handled : 4(remaining part) Refer design doc for rules table */
1270 if p_new_job_status = 'TTR6' and l_TCHR_TTR6_History then
1271 -- 14
1272 pqp_aat_shd.constraint_error
1273 (p_constraint_name => 'PQP_INVALID_JOB_STATUS');
1274 end if; -- 14
1275
1276 end if; -- 12
1277
1278 end if; -- If 11
1279
1280 end if; -- 0
1281 --
1282 hr_utility.set_location(' Leaving: '|| l_proc, 10);
1283 --
1284 end chk_job_status_change;
1285 --
1286 -- ---------------------------------------------------------------------------+
1287 -- |-----------------------< chk_tp_col_dependencies >------------------------|
1288 -- ---------------------------------------------------------------------------+
1289 -- {Start Of Comments}
1290 --
1291 -- Description:
1292 -- This procedure is used to validate the following 2 rules :
1293 -- 1) If Job Status is not null then Elected pension flag should
1294 -- be not null as well.
1295 -- 2) If either Elected pension flag or safeguarded grade are
1296 -- not null then job status should be not null as well.
1297 --
1298 -- Pre Conditions:
1299 --
1300 -- In Arguments:
1301 -- Job Status
1302 -- Elected Pension Flag
1303 -- Safeguarded Grade
1304 --
1305 -- Post Success:
1306 -- Processing continues
1307 --
1308 -- Post Failure:
1309 -- An application error will be raised and processing is
1310 -- terminated
1311 --
1312 -- {End Of Comments}
1313 -- ---------------------------------------------------------------------------+
1314 Procedure chk_tp_col_dependencies
1315 (p_tp_is_teacher in varchar2
1316 ,p_tp_elected_pension in varchar2
1317 ,p_tp_safeguarded_grade in varchar2
1318 ,p_tp_fast_track in varchar2
1319 ) IS
1320
1321 -- Local Variable Declaration
1322 l_proc varchar2(72) := g_package || 'chk_job_status_change';
1323
1324 --
1325 Begin
1326 --
1327 hr_utility.set_location(' Entering: '|| l_proc, 10);
1328 --
1329 -- Rule 1
1330 if p_tp_is_teacher is not null then
1331 if p_tp_elected_pension is null then
1332 fnd_message.set_name('PQP', 'PQP_230563_PENSION_FLAG_NULL');
1333 fnd_message.raise_error;
1334 end if;
1335 end if;
1336 hr_utility.set_location(l_proc, 20);
1337
1338 -- Rule 2
1339 /* BUG # 2215296 :
1340 Removed following condition from if statement below as
1341 elected pension flag is being set to 'N' when it is null.
1342 (p_tp_elected_pension is not null) or
1343 */
1344 if (p_tp_safeguarded_grade is not null) then
1345 if p_tp_is_teacher is null then
1346 fnd_message.set_name('PQP', 'PQP_230562_JOB_STATUS_NULL');
1347 fnd_message.raise_error;
1348 end if;
1349 end if;
1350
1351 -- Rule 3
1352 if p_tp_fast_track = 'Y' and p_tp_safeguarded_grade is not null then
1353 fnd_message.set_name('PQP', 'PQP_230574_CAREER_COMBI_ERR');
1354 fnd_message.raise_error;
1355 end if;
1356
1357
1358 hr_utility.set_location(' Leaving: '|| l_proc, 30);
1359 --
1360 end chk_tp_col_dependencies;
1361 --
1362 -- ---------------------------------------------------------------------------+
1363 -- |-----------------------< chk_tp_safeguarded_rate >------------------------|
1364 -- ---------------------------------------------------------------------------+
1365 -- {Start Of Comments}
1366 --
1367 -- Description:
1368 -- This procedure is used to validate tp_safeguarded_rate_type and
1369 -- tp_safeguarded_rate_id against table pay_rates
1370 --
1371 -- Pre Conditions:
1372 --
1373 -- In Arguments:
1374 -- p_tp_safeguarded_rate_type
1375 -- p_tp_safeguarded_rate_id
1376 --
1377 -- Post Success:
1378 -- Processing continues
1379 --
1380 -- Post Failure:
1381 -- An application error will be raised and processing is
1382 -- terminated
1383 --
1384 -- {End Of Comments}
1385 -- ---------------------------------------------------------------------------+
1386 Procedure chk_tp_safeguarded_rate
1387 (p_assignment_attribute_id in number
1388 ,p_tp_safeguarded_grade in varchar2
1389 ,p_tp_safeguarded_rate_type in varchar2
1390 ,p_tp_safeguarded_rate_id in number
1391 ) IS
1392 --
1393 Cursor ChkRate is
1394 Select 'Y'
1395 From pay_rates pr
1396 Where pr.rate_type = p_tp_safeguarded_rate_type
1397 and pr.rate_id = p_tp_safeguarded_rate_id;
1398 --
1399 l_proc varchar2(72) := g_package || 'chk_tp_safeguarded_rate';
1400 l_RateValid char(1) := 'N';
1401 --
1402 Begin
1403 --
1404 hr_utility.set_location(' Entering: '|| l_proc, 10);
1405
1406
1407 if (((p_assignment_attribute_id is not null) and
1408 (nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_rate_type,hr_api.g_varchar2)
1409 <> nvl(p_tp_safeguarded_rate_type,hr_api.g_varchar2)
1410 )
1411 or
1412 (nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_rate_id,hr_api.g_number)
1413 <> nvl(p_tp_safeguarded_rate_id,hr_api.g_number)
1414 )
1415 or
1416 (nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_grade,hr_api.g_varchar2)
1417 <> nvl(p_tp_safeguarded_grade,hr_api.g_varchar2)
1418 )
1419 )
1420 or
1421 (p_assignment_attribute_id is null)
1422 ) then
1423
1424 hr_utility.set_location(l_proc, 20);
1425 --
1426
1427 if ((p_tp_safeguarded_rate_type = 'G' and p_tp_safeguarded_grade IS NULL ) or
1428 (p_tp_safeguarded_rate_type = 'SP' and p_tp_safeguarded_grade IS NULL ) or
1429 (p_tp_safeguarded_rate_type = 'SN' and p_tp_safeguarded_grade IS NOT NULL )) then
1430
1431 --raise error message;
1432 fnd_message.set_name('PQP', 'PQP_230207_RATE_GRADE_COMB_ERR');
1433 fnd_message.raise_error;
1434
1435 end if;
1436
1437
1438
1439 if (p_tp_safeguarded_rate_id is not null
1440 or
1441 p_tp_safeguarded_rate_type is not null
1442 )
1443 and
1444 --added SN in the following condition to take care for the condition Safeguarded and No Rate requirement
1445 nvl(p_tp_safeguarded_rate_type,'AbXy') not in ('G','SP','SN') then
1446 -- invalid rate type selected
1447 fnd_message.set_name('PQP', 'PQP_230568_INVALID_RATE_TYPE');
1448 fnd_message.raise_error;
1449 else
1450 if p_tp_safeguarded_rate_id is not null then
1451
1452 begin
1453 open ChkRate;
1454 fetch ChkRate into l_RateValid;
1455 close ChkRate;
1456 exception
1457 when others then
1458 l_RateValid := 'N';
1459 end;
1460
1461 if l_RateValid = 'N' then
1462
1463 -- invalid grade or scale rate selected
1464 fnd_message.set_name('PQP', 'PQP_230569_INVALID_PAY_RATE');
1465 fnd_message.raise_error;
1466
1467 end if;
1468 hr_utility.set_location(l_proc, 30);
1469 -- the else part is commented out as it is no longer required to check if the rate id is null
1470 --even if the p_safeguarded_rate_type is null because p_safeguarded_rate_type can now be null even if the rate id is null.i.e.
1471 --salary not safeguarded at all.If the salary is safeguarded the Default value for the p_safeguarded_rate_type is SN in which
1472 --case there will not be any value for grade or rate name.
1473 /*
1474 else -- p_tp_safeguarded_rate_id is null
1475 --if p_tp_safeguarded_grade is not null then
1476 -- invalid grade or scale rate selected
1477 -- fnd_message.set_name('PQP', 'PQP_230569_INVALID_PAY_RATE');
1478 -- fnd_message.raise_error;
1479 --end if;
1480 */
1481 end if; -- p_tp_safeguarded_rate_id is not null
1482
1483 end if; -- p_tp_safeguarded_rate_type is not null ...
1484 end if;
1485
1486
1487 hr_utility.set_location(' Leaving: '|| l_proc, 40);
1488 --
1489 end chk_tp_safeguarded_rate;
1490 --
1491 -- ---------------------------------------------------------------------------+
1492 -- |-----------------------< chk_tp_spinal_point >----------------------------|
1493 -- ---------------------------------------------------------------------------+
1494 -- {Start Of Comments}
1495 --
1496 -- Description:
1497 -- This procedure is used to validate tp_safeguarded_spinal_point_id
1498 --
1499 -- Pre Conditions:
1500 --
1501 -- In Arguments:
1502 -- p_tp_safeguarded_rate_type
1503 -- p_tp_safeguarded_spinal_point_id
1504 --
1505 -- Post Success:
1506 -- Processing continues
1507 --
1508 -- Post Failure:
1509 -- An application error will be raised and processing is
1510 -- terminated
1511 --
1512 -- {End Of Comments}
1513 -- ---------------------------------------------------------------------------+
1514 Procedure chk_tp_spinal_point
1515 (p_assignment_attribute_id in number
1516 ,p_tp_safeguarded_grade in varchar2
1517 ,p_tp_safeguarded_rate_type in varchar2
1518 ,p_tp_spinal_point_id in number
1519 ) IS
1520 --
1521 Cursor ChkSpinalPoint is
1522 Select 'Y'
1523 From per_spinal_points psp
1524 Where psp.spinal_point_id = p_tp_spinal_point_id;
1525 --
1526 l_proc varchar2(72) := g_package || 'chk_tp_spinal_point';
1527 l_SPValid char(1) := 'N';
1528 --
1529 Begin
1530 --
1531 hr_utility.set_location(' Entering: '|| l_proc, 10);
1532
1533 if (((p_assignment_attribute_id is not null) and
1534 nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_spinal_point_id,hr_api.g_number)
1535 <> nvl(p_tp_spinal_point_id,hr_api.g_number))
1536 or
1537 (p_assignment_attribute_id is null)
1538 ) then
1539
1540 hr_utility.set_location(l_proc, 20);
1541 --
1542 if p_tp_spinal_point_id is not null then
1543
1544 begin
1545 open ChkSpinalPoint;
1546 fetch ChkSpinalPoint into l_SPValid;
1547 close ChkSpinalPoint;
1548 exception
1549 when others then
1550 l_SPValid := 'N';
1551 end;
1552
1553 if l_SPValid = 'N' then
1554
1555 -- invalid spinal point selected
1556 fnd_message.set_name('PQP', 'PQP_230570_INVALID_SPINAL_PNT');
1557 fnd_message.raise_error;
1558
1559 end if; -- l_SPValid = 'N'
1560
1561 hr_utility.set_location(l_proc, 30);
1562
1563 else -- p_tp_spinal_point_id is null
1564 if nvl(p_tp_safeguarded_rate_type,'AbXy') = 'SP' and
1565 p_tp_safeguarded_grade is not null then
1566
1567 -- spinal point id must be supplied if rate type is SP
1568 fnd_message.set_name('PQP', 'PQP_230571_SPINAL_POINT_MUST');
1569 fnd_message.raise_error;
1570
1571 end if; -- tp_safeguarded_rate_type = 'SP'
1572 end if; -- p_tp_spinal_point_id is not null
1573 end if; -- (((p_assignment_attribute_id is not null) and...
1574
1575 hr_utility.set_location(' Leaving: '|| l_proc, 40);
1576 --
1577 end chk_tp_spinal_point;
1578 --
1579 -- ---------------------------------------------------------------------------+
1580 -- |-----------------------< chk_tp_grade_spine >-----------------------------|
1581 -- ---------------------------------------------------------------------------+
1582 -- {Start Of Comments}
1583 --
1584 -- Description:
1585 -- This procedure is used to check if tp_safeguarded_spinal_point_id is
1586 -- valid for the given grade and pay scale.
1587 --
1588 -- Pre Conditions:
1589 --
1590 -- In Arguments:
1591 -- p_tp_safeguarded_grade
1592 -- p_tp_safeguarded_grade_id
1593 -- p_tp_safeguarded_rate_type
1594 -- p_tp_safeguarded_rate_id
1595 -- p_tp_spinal_point_id
1596 --
1597 -- Post Success:
1598 -- Processing continues
1599 --
1600 -- Post Failure:
1601 -- An application error will be raised and processing is
1602 -- terminated
1603 --
1604 -- {End Of Comments}
1605 -- ---------------------------------------------------------------------------+
1606 Procedure chk_tp_grade_spine
1607 (p_assignment_attribute_id in number
1608 ,p_tp_safeguarded_grade in varchar2
1609 ,p_tp_safeguarded_grade_id in number
1610 ,p_tp_safeguarded_rate_type in varchar2
1611 ,p_tp_safeguarded_rate_id in number
1612 ,p_tp_spinal_point_id in number
1613 ,p_validation_start_date in date
1614 ,p_validation_end_date in date
1615 ) IS
1616 --
1617 Cursor ChkGradeSpine is
1618 Select 'Y'
1619 From pay_rates pr
1620 ,per_grade_spines_f pgs
1621 ,per_spinal_point_steps_f psps
1622 Where pr.parent_spine_id = pgs.parent_spine_id
1623 and psps.grade_spine_id = pgs.grade_spine_id
1624 and pgs.grade_id = p_tp_safeguarded_grade_id
1625 and pr.rate_id = p_tp_safeguarded_rate_id
1626 and psps.spinal_point_id = p_tp_spinal_point_id
1627 and p_validation_start_date <= psps.effective_end_date
1628 and p_validation_end_date >= psps.effective_start_date;
1629
1630
1631 --
1632 l_proc varchar2(72) := g_package || 'chk_tp_grade_spine';
1633 l_GSValid char(1) := 'N';
1634 --
1635 Begin
1636 --
1637 hr_utility.set_location(' Entering: '|| l_proc, 10);
1638
1639 if (((p_assignment_attribute_id is not null)
1640 and
1641 (nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_spinal_point_id,hr_api.g_number)
1642 <> nvl(p_tp_spinal_point_id,hr_api.g_number)
1643 or
1644 nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_rate_id,hr_api.g_number)
1645 <> nvl(p_tp_safeguarded_rate_id,hr_api.g_number)
1646 or
1647 nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_rate_type,hr_api.g_varchar2)
1648 <> nvl(p_tp_safeguarded_rate_type,hr_api.g_varchar2)
1649 or
1650 nvl(pqp_aat_shd.g_old_rec.tp_safeguarded_grade,hr_api.g_varchar2)
1651 <> nvl(p_tp_safeguarded_grade,hr_api.g_varchar2)
1652 )
1653 )
1654 or
1655 (p_assignment_attribute_id is null)
1656 ) then
1657
1658 hr_utility.set_location(l_proc, 20);
1659 --
1660 if ((p_tp_spinal_point_id is not null) or
1661 (p_tp_safeguarded_rate_id is not null) or
1662 (p_tp_safeguarded_grade is not null)
1663 -- or (p_tp_safeguarded_rate_type is not null)
1664 )
1665 and
1666 p_tp_safeguarded_rate_type = 'SP' then
1667
1668 -- Now check if the spinal point is valid for this Scale Rate.
1669 begin
1670 open ChkGradeSpine;
1671 fetch ChkGradeSpine into l_GSValid;
1672 close ChkGradeSpine;
1673 exception
1674 when others then
1675 l_GSValid := 'N';
1676 end;
1677
1678 if l_GSValid = 'N' then
1679 -- spinal point is not valid for this grade and scale rate
1680 fnd_message.set_name('PQP', 'PQP_230572_INVALID_GRADE_SPINE');
1681 fnd_message.raise_error;
1682 end if;
1683
1684 hr_utility.set_location(l_proc, 30);
1685 end if; -- (p_tp_spinal_point_id is not null) or
1686 end if; -- (((p_assignment_attribute_id is not null) and...
1687
1688 hr_utility.set_location(' Leaving: '|| l_proc, 40);
1689 --
1690 end chk_tp_grade_spine;
1691 --
1692 -- ---------------------------------------------------------------------------+
1693 -- |-----------------------< chk_tp_fast_track >------------------------------|
1694 -- ---------------------------------------------------------------------------+
1695 -- {Start Of Comments}
1696 --
1697 -- Description:
1698 -- This procedure is used to validate tp_fast_track against
1699 -- HR_LOOKUP.LOOKUP_CODE where LOOKUP_TYPE is 'YES_NO'.
1700 --
1701 -- Pre Conditions:
1702 --
1703 -- In Arguments:
1704 -- p_effective_date
1705 -- p_tp_fast_track
1706 --
1707 -- Post Success:
1708 -- Processing continues
1709 --
1710 -- Post Failure:
1711 -- An application error will be raised and processing is
1712 -- terminated
1713 --
1714 -- {End Of Comments}
1715 -- ---------------------------------------------------------------------------+
1716 Procedure chk_tp_fast_track
1717 (p_assignment_attribute_id in number
1718 ,p_tp_fast_track in varchar2
1719 ,p_effective_date in date
1720 ,p_validation_start_date in date
1721 ,p_validation_end_date in date
1722 ) IS
1723 --
1724 l_proc varchar2(72) := g_package || 'chk_tp_fast_track';
1725 --
1726 Begin
1727 --
1728 hr_utility.set_location(' Entering: '|| l_proc, 10);
1729
1730 if (((p_assignment_attribute_id is not null) and
1731 nvl(pqp_aat_shd.g_old_rec.tp_fast_track,
1732 hr_api.g_varchar2) <> nvl(p_tp_fast_track,
1733 hr_api.g_varchar2))
1734 or
1735 (p_assignment_attribute_id is null)) then
1736
1737 hr_utility.set_location(l_proc, 20);
1738 --
1739 if p_tp_fast_track is not null then
1740 if hr_api.not_exists_in_dt_hr_lookups
1741 (p_effective_date => p_effective_date
1742 ,p_validation_start_date => p_validation_start_date
1743 ,p_validation_end_date => p_validation_end_date
1744 ,p_lookup_type => 'YES_NO'
1745 ,p_lookup_code => p_tp_fast_track
1746 ) then
1747
1748 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
1749 fnd_message.set_token('COLUMN_NAME', 'TP_FAST_TRACK');
1750 fnd_message.raise_error;
1751
1752 end if;
1753 hr_utility.set_location(l_proc, 30);
1754 end if;
1755 end if;
1756
1757 hr_utility.set_location(' Leaving: '|| l_proc, 40);
1758 --
1759 end chk_tp_fast_track;
1760 --
1761 --
1762 -- ---------------------------------------------------------------------------+
1763 -- |--------------------------< dt_update_validate >--------------------------|
1764 -- ---------------------------------------------------------------------------+
1765 -- {Start Of Comments}
1766 --
1767 -- Description:
1768 -- This procedure is used for referential integrity of datetracked
1769 -- parent entities when a datetrack update operation is taking place
1770 -- and where there is no cascading of update defined for this entity.
1771 --
1772 -- Prerequisites:
1773 -- This procedure is called from the update_validate.
1774 --
1775 -- In Parameters:
1776 --
1777 -- Post Success:
1778 -- Processing continues.
1779 --
1780 -- Post Failure:
1781 --
1782 -- Developer Implementation Notes:
1783 -- This procedure should not need maintenance unless the HR Schema model
1784 -- changes.
1785 --
1786 -- Access Status:
1787 -- Internal Row Handler Use Only.
1788 --
1789 -- {End Of Comments}
1790 -- ---------------------------------------------------------------------------+
1791 Procedure dt_update_validate
1792 (p_datetrack_mode in varchar2
1793 ,p_validation_start_date in date
1794 ,p_validation_end_date in date
1795 ) Is
1796 --
1797 l_proc varchar2(72) := g_package||'dt_update_validate';
1798 l_integrity_error Exception;
1799 l_table_name all_tables.table_name%TYPE;
1800 --
1801 Begin
1802 --
1803 -- Ensure that the p_datetrack_mode argument is not null
1804 --
1805 hr_api.mandatory_arg_error
1806 (p_api_name => l_proc
1807 ,p_argument => 'datetrack_mode'
1808 ,p_argument_value => p_datetrack_mode
1809 );
1810 --
1811 -- Mode will be valid, as this is checked at the start of the upd.
1812 --
1813 -- Ensure the arguments are not null
1814 --
1815 hr_api.mandatory_arg_error
1816 (p_api_name => l_proc
1817 ,p_argument => 'validation_start_date'
1818 ,p_argument_value => p_validation_start_date
1819 );
1820 --
1821 hr_api.mandatory_arg_error
1822 (p_api_name => l_proc
1823 ,p_argument => 'validation_end_date'
1824 ,p_argument_value => p_validation_end_date
1825 );
1826 --
1827 --
1828 --
1829 Exception
1830 When l_integrity_error Then
1831 --
1832 -- A referential integrity check was violated therefore
1833 -- we must error
1834 --
1835 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
1836 fnd_message.set_token('TABLE_NAME', l_table_name);
1837 fnd_message.raise_error;
1838 When Others Then
1839 --
1840 -- An unhandled or unexpected error has occurred which
1841 -- we must report
1842 --
1843 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1844 fnd_message.set_token('PROCEDURE', l_proc);
1845 fnd_message.set_token('STEP','15');
1846 fnd_message.raise_error;
1847 End dt_update_validate;
1848 --
1849 -- ---------------------------------------------------------------------------+
1850 -- |--------------------------< dt_delete_validate >--------------------------|
1851 -- ---------------------------------------------------------------------------+
1852 -- {Start Of Comments}
1853 --
1854 -- Description:
1855 -- This procedure is used for referential integrity of datetracked
1856 -- child entities when either a datetrack DELETE or ZAP is in operation
1857 -- and where there is no cascading of delete defined for this entity.
1858 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1859 -- datetracked child rows exist between the validation start and end
1860 -- dates.
1861 --
1862 -- Prerequisites:
1863 -- This procedure is called from the delete_validate.
1864 --
1865 -- In Parameters:
1866 --
1867 -- Post Success:
1868 -- Processing continues.
1869 --
1870 -- Post Failure:
1871 -- If a row exists by determining the returning Boolean value from the
1872 -- generic dt_api.rows_exist function then we must supply an error via
1873 -- the use of the local exception handler l_rows_exist.
1874 --
1875 -- Developer Implementation Notes:
1876 -- This procedure should not need maintenance unless the HR Schema model
1877 -- changes.
1878 --
1879 -- Access Status:
1880 -- Internal Row Handler Use Only.
1881 --
1882 -- {End Of Comments}
1883 -- ---------------------------------------------------------------------------+
1884 Procedure dt_delete_validate
1885 (p_assignment_attribute_id in number
1886 ,p_datetrack_mode in varchar2
1887 ,p_validation_start_date in date
1888 ,p_validation_end_date in date
1889 ) Is
1890 --
1891 l_proc varchar2(72) := g_package||'dt_delete_validate';
1892 l_rows_exist Exception;
1893 l_table_name all_tables.table_name%TYPE;
1894 --
1895 Begin
1896 --
1897 -- Ensure that the p_datetrack_mode argument is not null
1898 --
1899 hr_api.mandatory_arg_error
1900 (p_api_name => l_proc
1901 ,p_argument => 'datetrack_mode'
1902 ,p_argument_value => p_datetrack_mode
1903 );
1904 --
1905 -- Only perform the validation if the datetrack mode is either
1906 -- DELETE or ZAP
1907 --
1908 If (p_datetrack_mode = hr_api.g_delete or
1909 p_datetrack_mode = hr_api.g_zap) then
1910 --
1911 --
1912 -- Ensure the arguments are not null
1913 --
1914 hr_api.mandatory_arg_error
1915 (p_api_name => l_proc
1916 ,p_argument => 'validation_start_date'
1917 ,p_argument_value => p_validation_start_date
1918 );
1919 --
1920 hr_api.mandatory_arg_error
1921 (p_api_name => l_proc
1922 ,p_argument => 'validation_end_date'
1923 ,p_argument_value => p_validation_end_date
1924 );
1925 --
1926 hr_api.mandatory_arg_error
1927 (p_api_name => l_proc
1928 ,p_argument => 'assignment_attribute_id'
1929 ,p_argument_value => p_assignment_attribute_id
1930 );
1931 --
1932 --
1933 --
1934 End If;
1935 --
1936 Exception
1937 When l_rows_exist Then
1938 --
1939 -- A referential integrity check was violated therefore
1940 -- we must error
1941 --
1942 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
1943 fnd_message.set_token('TABLE_NAME', l_table_name);
1944 fnd_message.raise_error;
1945 When Others Then
1946 --
1947 -- An unhandled or unexpected error has occurred which
1948 -- we must report
1949 --
1950 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1951 fnd_message.set_token('PROCEDURE', l_proc);
1952 fnd_message.set_token('STEP','15');
1953 fnd_message.raise_error;
1954 --
1955 End dt_delete_validate;
1956 --
1957 -- ---------------------------------------------------------------------------+
1958 -- |---------------------------< insert_validate >----------------------------|
1959 -- ---------------------------------------------------------------------------+
1960 Procedure insert_validate
1961 (p_rec in pqp_aat_shd.g_rec_type
1962 ,p_effective_date in date
1963 ,p_datetrack_mode in varchar2
1964 ,p_validation_start_date in date
1965 ,p_validation_end_date in date
1966 ) is
1967 --
1968 l_proc varchar2(72) := g_package||'insert_validate';
1969 --
1970 Begin
1971 hr_utility.set_location('Entering:'||l_proc, 5);
1972 --
1973 -- Call all supporting business operations
1974 --
1975 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1976 --
1977 --
1978 pqp_aat_bus.chk_ddf(p_rec);
1979 --
1980 pqp_aat_bus.chk_df(p_rec);
1981 --
1982
1983 --
1984 -- Added for Bugfix 2651375
1985 -- Check that if one of the work pattern cols is entered, then the
1986 -- other one is not left NULL
1987 --
1988 chk_work_pattern_cols
1989 (p_work_pattern => p_rec.work_pattern
1990 ,p_start_day => p_rec.start_day
1991 );
1992
1993 --
1994 -- Check that both company and private vehicles have not
1995 -- been entered.
1996 --
1997 chk_private_company_car(
1998 p_primary_company_car => p_rec.primary_company_car,
1999 p_secondary_company_car => p_rec.secondary_company_car,
2000 p_private_car => p_rec.private_car
2001 );
2002
2003 --
2004 -- Check the same car is not both the primary and secondary car
2005 --
2006 chk_prim_sec_duplicate(
2007 p_primary_company_car => p_rec.primary_company_car,
2008 p_secondary_company_car => p_rec.secondary_company_car
2009 );
2010
2011 --
2012 -- Check that primary car exists if secondary has been entered
2013 --
2014 chk_primary_exists(
2015 p_primary_company_car => p_rec.primary_company_car,
2016 p_secondary_company_car => p_rec.secondary_company_car
2017 );
2018
2019 --
2020 -- Check the same car has not been assigned to multiple people
2021 --
2022 IF p_rec.primary_company_car IS NOT NULL AND p_rec.secondary_company_car IS NOT NULL THEN -- for bug 6871534
2023 chk_company_car_duplicate_asg(
2024 p_primary_company_car => p_rec.primary_company_car,
2025 p_secondary_company_car => p_rec.secondary_company_car,
2026 p_assignment_id => p_rec.assignment_id,
2027 p_validation_start_date => p_validation_start_date,
2028 p_validation_end_date => p_validation_end_date
2029 );
2030 End IF;
2031
2032 --
2033 -- Check that there are not date overlapping records
2034 -- for the same assignment
2035 --
2036 chk_asg_overlap(
2037 p_assignment_attribute_id => p_rec.assignment_attribute_id,
2038 p_assignment_id => p_rec.assignment_id,
2039 p_validation_start_date => p_validation_start_date,
2040 p_validation_end_date => p_validation_end_date
2041 );
2042
2043 --
2044 -- Check if the rates table exist in PAY_USER_TABLES
2045 --
2046 IF p_rec.company_car_rates_table_id IS NOT NULL THEN
2047 chk_table_exists(
2048 p_rates_table_id => p_rec.company_car_rates_table_id
2049 ,p_business_group_id => p_rec.business_group_id);
2050 END IF;
2051
2052 IF p_rec.private_car_rates_table_id IS NOT NULL THEN
2053 chk_table_exists(
2054 p_rates_table_id => p_rec.private_car_rates_table_id
2055 ,p_business_group_id => p_rec.business_group_id);
2056 END IF;
2057
2058 --
2059 -- Check that the teacher flag has a value of either Y or N
2060 --
2061 chk_tp_is_teacher
2062 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2063 ,p_tp_is_teacher => p_rec.tp_is_teacher
2064 ,p_effective_date => p_effective_date
2065 ,p_validation_start_date => p_validation_start_date
2066 ,p_validation_end_date => p_validation_end_date
2067 );
2068
2069
2070 -- added the following check procedure to check if the head teacher grp code is valid or not
2071 chk_tp_headteacher_grp_code
2072 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2073 ,p_tp_is_teacher => p_rec.tp_is_teacher
2074 ,p_tp_headteacher_grp_code => p_rec.tp_headteacher_grp_code
2075 );
2076
2077 --
2078 -- Check that the elected pension flag has a value of either Y or N
2079 --
2080 chk_tp_elected_pension
2081 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2082 ,p_tp_elected_pension => p_rec.tp_elected_pension
2083 ,p_effective_date => p_effective_date
2084 ,p_validation_start_date => p_validation_start_date
2085 ,p_validation_end_date => p_validation_end_date
2086 );
2087 --
2088 -- Check that the fast track flag has a value of either Y or N
2089 --
2090 chk_tp_fast_track
2091 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2092 ,p_tp_fast_track => p_rec.tp_fast_track
2093 ,p_effective_date => p_effective_date
2094 ,p_validation_start_date => p_validation_start_date
2095 ,p_validation_end_date => p_validation_end_date
2096 );
2097 --
2098 chk_tp_safeguarded_grade
2099 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2100 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2101 ,p_tp_safeguarded_grade_id => p_rec.tp_safeguarded_grade_id
2102 ,p_effective_date => p_effective_date
2103 ,p_validation_start_date => p_validation_start_date
2104 ,p_validation_end_date => p_validation_end_date
2105 );
2106 --
2107 -- Check that interdependencies of TP columns are valid.
2108 --
2109 chk_tp_col_dependencies
2110 (p_tp_is_teacher => p_rec.tp_is_teacher
2111 ,p_tp_elected_pension => p_rec.tp_elected_pension
2112 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2113 ,p_tp_fast_track => p_rec.tp_fast_track
2114 );
2115 --
2116 -- Check that grade or scale rate is valid
2117 --
2118 chk_tp_safeguarded_rate
2119 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2120 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2121 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2122 ,p_tp_safeguarded_rate_id => p_rec.tp_safeguarded_rate_id
2123 );
2124 --
2125 -- Check that the spinal point is valid
2126 --
2127 chk_tp_spinal_point
2128 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2129 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2130 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2131 ,p_tp_spinal_point_id => p_rec.tp_safeguarded_spinal_point_id
2132 );
2133 --
2134 -- Check that the spinal point is valid for the selected pay scale and grade
2135 --
2136 chk_tp_grade_spine
2137 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2138 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2139 ,p_tp_safeguarded_grade_id => p_rec.tp_safeguarded_grade_id
2140 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2141 ,p_tp_safeguarded_rate_id => p_rec.tp_safeguarded_rate_id
2142 ,p_tp_spinal_point_id => p_rec.tp_safeguarded_spinal_point_id
2143 ,p_validation_start_date => p_validation_start_date
2144 ,p_validation_end_date => p_validation_end_date
2145 );
2146 --
2147 hr_utility.set_location(' Leaving:'||l_proc, 10);
2148 End insert_validate;
2149 --
2150 -- ---------------------------------------------------------------------------+
2151 -- |---------------------------< update_validate >----------------------------|
2152 -- ---------------------------------------------------------------------------+
2153 Procedure update_validate
2154 (p_rec in pqp_aat_shd.g_rec_type
2155 ,p_effective_date in date
2156 ,p_datetrack_mode in varchar2
2157 ,p_validation_start_date in date
2158 ,p_validation_end_date in date
2159 ) is
2160 --
2161 l_proc varchar2(72) := g_package||'update_validate';
2162 --
2163 Begin
2164 hr_utility.set_location('Entering:'||l_proc, 5);
2165 --
2166 -- Call all supporting business operations
2167 --
2168 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2169 --
2170 -- Call the datetrack update integrity operation
2171 --
2172 dt_update_validate
2173 (p_datetrack_mode => p_datetrack_mode
2174 ,p_validation_start_date => p_validation_start_date
2175 ,p_validation_end_date => p_validation_end_date
2176 );
2177 --
2178 chk_non_updateable_args
2179 (p_effective_date => p_effective_date
2180 ,p_rec => p_rec
2181 );
2182 --
2183 --
2184 pqp_aat_bus.chk_ddf(p_rec);
2185 --
2186 pqp_aat_bus.chk_df(p_rec);
2187
2188 --
2189 -- Added for Bugfix 2651375
2190 -- Check that if one of the work pattern cols is entered, then the
2191 -- other one is not left NULL
2192 --
2193 chk_work_pattern_cols
2194 (p_work_pattern => p_rec.work_pattern
2195 ,p_start_day => p_rec.start_day
2196 );
2197
2198 --
2199 -- Check that both company and private vehicles have not
2200 -- been entered.
2201 --
2202 chk_private_company_car(
2203 p_primary_company_car => p_rec.primary_company_car,
2204 p_secondary_company_car => p_rec.secondary_company_car,
2205 p_private_car => p_rec.private_car
2206 );
2207
2208 --
2209 -- Check the same car is not both the primary and secondary car
2210 --
2211 chk_prim_sec_duplicate(
2212 p_primary_company_car => p_rec.primary_company_car,
2213 p_secondary_company_car => p_rec.secondary_company_car
2214 );
2215
2216 --
2217 -- Check that primary car exists if secondary has been entered
2218 --
2219 chk_primary_exists(
2220 p_primary_company_car => p_rec.primary_company_car,
2221 p_secondary_company_car => p_rec.secondary_company_car
2222 );
2223
2224 --
2225 -- Check the same car has not been assigned to multiple people
2226 --
2227 IF p_rec.primary_company_car IS NOT NULL AND p_rec.secondary_company_car IS NOT NULL THEN -- for bug 6871534
2228 chk_company_car_duplicate_asg(
2229 p_primary_company_car => p_rec.primary_company_car,
2230 p_secondary_company_car => p_rec.secondary_company_car,
2231 p_assignment_id => p_rec.assignment_id,
2232 p_validation_start_date => p_validation_start_date,
2233 p_validation_end_date => p_validation_end_date
2234 );
2235 END IF;
2236 --
2237 -- Check that there are not date overlapping records
2238 -- for the same assignment
2239 --
2240 chk_asg_overlap(
2241 p_assignment_attribute_id => p_rec.assignment_attribute_id,
2242 p_assignment_id => p_rec.assignment_id,
2243 p_validation_start_date => p_validation_start_date,
2244 p_validation_end_date => p_validation_end_date
2245 );
2246
2247 --
2248 -- Check if the rates table exist in PAY_USER_TABLES
2249 --
2250 IF p_rec.company_car_rates_table_id IS NOT NULL THEN
2251 chk_table_exists(
2252 p_rates_table_id => p_rec.company_car_rates_table_id
2253 ,p_business_group_id => p_rec.business_group_id);
2254 END IF;
2255
2256 IF p_rec.private_car_rates_table_id IS NOT NULL THEN
2257 chk_table_exists(
2258 p_rates_table_id => p_rec.private_car_rates_table_id
2259 ,p_business_group_id => p_rec.business_group_id);
2260 END IF;
2261
2262 --
2263 -- Check that the teacher flag has a value of either Y or N
2264 --
2265 chk_tp_is_teacher
2266 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2267 ,p_tp_is_teacher => p_rec.tp_is_teacher
2268 ,p_effective_date => p_effective_date
2269 ,p_validation_start_date => p_validation_start_date
2270 ,p_validation_end_date => p_validation_end_date
2271 );
2272
2273 --
2274
2275
2276 -- added the following check procedure to check if the head teacher grp code is valid or not
2277
2278 chk_tp_headteacher_grp_code
2279 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2280 ,p_tp_is_teacher => p_rec.tp_is_teacher
2281 ,p_tp_headteacher_grp_code => p_rec.tp_headteacher_grp_code
2282 );
2283 -- Check that the teacher flag OR job status change is valid
2284 --
2285
2286 chk_job_status_change
2287 (p_new_job_status => p_rec.tp_is_teacher
2288 ,p_old_job_status => pqp_aat_shd.g_old_rec.tp_is_teacher
2289 ,p_assignment_id => p_rec.assignment_id
2290 ,p_effective_date => p_effective_date
2291 ,p_datetrack_mode => p_datetrack_mode
2292 );
2293
2294 --
2295 -- Check that the elected pension flag has a value of either Y or N
2296 --
2297 chk_tp_elected_pension
2298 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2299 ,p_tp_elected_pension => p_rec.tp_elected_pension
2300 ,p_effective_date => p_effective_date
2301 ,p_validation_start_date => p_validation_start_date
2302 ,p_validation_end_date => p_validation_end_date
2303 );
2304 --
2305 -- Check that the fast track flag has a value of either Y or N
2306 --
2307 chk_tp_fast_track
2308 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2309 ,p_tp_fast_track => p_rec.tp_fast_track
2310 ,p_effective_date => p_effective_date
2311 ,p_validation_start_date => p_validation_start_date
2312 ,p_validation_end_date => p_validation_end_date
2313 );
2314 --
2315 -- Check that the safeguarded grade is of the correct format
2316 --
2317 chk_tp_safeguarded_grade
2318 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2319 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2320 ,p_tp_safeguarded_grade_id => p_rec.tp_safeguarded_grade_id
2321 ,p_effective_date => p_effective_date
2322 ,p_validation_start_date => p_validation_start_date
2323 ,p_validation_end_date => p_validation_end_date
2324 );
2325 --
2326 -- Check that interdependencies of TP columns are valid.
2327 --
2328 chk_tp_col_dependencies
2329 (p_tp_is_teacher => p_rec.tp_is_teacher
2330 ,p_tp_elected_pension => p_rec.tp_elected_pension
2331 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2332 ,p_tp_fast_track => p_rec.tp_fast_track
2333 );
2334 --
2335 -- Check that grade or scale rate is valid
2336 --
2337 chk_tp_safeguarded_rate
2338 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2339 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2340 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2341 ,p_tp_safeguarded_rate_id => p_rec.tp_safeguarded_rate_id
2342 );
2343 --
2344 -- Check that the spinal point is valid
2345 --
2346 chk_tp_spinal_point
2347 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2348 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2349 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2350 ,p_tp_spinal_point_id => p_rec.tp_safeguarded_spinal_point_id
2351 );
2352 --
2353 -- Check that the spinal point is valid for the selected pay scale and grade
2354 --
2355 chk_tp_grade_spine
2356 (p_assignment_attribute_id => p_rec.assignment_attribute_id
2357 ,p_tp_safeguarded_grade => p_rec.tp_safeguarded_grade
2358 ,p_tp_safeguarded_grade_id => p_rec.tp_safeguarded_grade_id
2359 ,p_tp_safeguarded_rate_type => p_rec.tp_safeguarded_rate_type
2360 ,p_tp_safeguarded_rate_id => p_rec.tp_safeguarded_rate_id
2361 ,p_tp_spinal_point_id => p_rec.tp_safeguarded_spinal_point_id
2362 ,p_validation_start_date => p_validation_start_date
2363 ,p_validation_end_date => p_validation_end_date
2364 );
2365 --
2366 hr_utility.set_location(' Leaving:'||l_proc, 10);
2367 End update_validate;
2368 --
2369 -- ---------------------------------------------------------------------------+
2370 -- |---------------------------< delete_validate >----------------------------|
2371 -- ---------------------------------------------------------------------------+
2372 Procedure delete_validate
2373 (p_rec in pqp_aat_shd.g_rec_type
2374 ,p_effective_date in date
2375 ,p_datetrack_mode in varchar2
2376 ,p_validation_start_date in date
2377 ,p_validation_end_date in date
2378 ) is
2379 --
2380 l_proc varchar2(72) := g_package||'delete_validate';
2381 l_asg number;
2382
2383 cursor c_asg is
2384 select assignment_id
2385 from pqp_assignment_attributes_f
2386 where assignment_attribute_id = p_rec.assignment_attribute_id
2387 and p_effective_date between effective_start_date
2388 and effective_end_date;
2389
2390 --
2391 Begin
2392 hr_utility.set_location('Entering:'||l_proc, 5);
2393 --
2394 -- Call all supporting business operations
2395 --
2396 dt_delete_validate
2397 (p_datetrack_mode => p_datetrack_mode
2398 ,p_validation_start_date => p_validation_start_date
2399 ,p_validation_end_date => p_validation_end_date
2400 ,p_assignment_attribute_id => p_rec.assignment_attribute_id
2401 );
2402 --
2403
2404 --
2405 -- Check that there are not date overlapping records
2406 -- for the same assignment
2407 --
2408
2409 if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
2410 --
2411 open c_asg;
2412 fetch c_asg into l_asg;
2413 close c_asg;
2414
2415 chk_asg_overlap(
2416 p_assignment_attribute_id => p_rec.assignment_attribute_id,
2417 p_assignment_id => l_asg,
2418 p_validation_start_date => p_validation_start_date,
2419 p_validation_end_date => p_validation_end_date
2420 );
2421 --
2422 end if;
2423
2424 hr_utility.set_location(' Leaving:'||l_proc, 10);
2425 End delete_validate;
2426 --
2427 end pqp_aat_bus;
[Home] [Help]