[Home] [Help]
PACKAGE BODY: APPS.PQH_RFT_BUS
Source
1 Package Body pqh_rft_bus as
2 /* $Header: pqrftrhi.pkb 120.3 2011/04/28 09:39:53 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rft_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ref_template_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- ref_template_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_ref_template_id(p_ref_template_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ref_template_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_rft_shd.api_updating
47 (p_ref_template_id => p_ref_template_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ref_template_id,hr_api.g_number)
52 <> pqh_rft_shd.g_old_rec.ref_template_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_rft_shd.constraint_error('PQH_REF_TEMPLATES_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ref_template_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_rft_shd.constraint_error('PQH_REF_TEMPLATES_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ref_template_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_base_template_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_ref_template_id PK
89 -- p_base_template_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_base_template_id (p_ref_template_id in number,
102 p_base_template_id in number,
103 p_reference_type_cd in varchar2,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_base_template_id';
107 l_api_updating boolean;
108 l_dummy varchar2(1);
109 l_enable_flag pqh_templates.enable_flag%type;
110 l_attribute_only_flag pqh_templates.attribute_only_flag%type;
111 l_freeze_status_cd pqh_templates.freeze_status_cd%type;
112 --
113 cursor c1 is
114 select nvl(enable_flag,hr_api.g_varchar2),
115 nvl(attribute_only_flag,hr_api.g_varchar2),
116 nvl(freeze_status_cd ,hr_api.g_varchar2)
117 from pqh_templates a
118 where a.template_id = p_base_template_id;
119 --
120 Begin
121 --
122 hr_utility.set_location('Entering:'||l_proc,5);
123 --
124 l_api_updating := pqh_rft_shd.api_updating
125 (p_ref_template_id => p_ref_template_id,
126 p_object_version_number => p_object_version_number);
127 --
128 if (l_api_updating
129 and nvl(p_base_template_id,hr_api.g_number)
130 <> nvl(pqh_rft_shd.g_old_rec.base_template_id,hr_api.g_number)
131 or not l_api_updating) then
132
133 --
134 -- check if base_template_id value exists in pqh_templates table
135 --
136 open c1;
137 --
138 fetch c1 into l_enable_flag,l_attribute_only_flag,l_freeze_status_cd;
139 -- fetch c1 into l_dummy;
140 if c1%notfound then
141 --
142 close c1;
143 --
144 -- raise error as FK does not relate to PK in pqh_templates
145 -- table.
146 --
147 pqh_rft_shd.constraint_error('PQH_REF_TEMPLATES_FK2');
148 --
149 end if;
150 --
151 close c1;
152 --
153 -- The referenced template must be enabled .
154 --
155 if l_enable_flag <> 'Y' then
156 if p_reference_type_cd = 'REFERENCE' then
157 hr_utility.set_message(8302,'PQH_RFT_NOT_ENABLED');
158 hr_utility.raise_error;
159 Else
160 hr_utility.set_message(8302,'PQH_COPY_TEM_NOT_ENABLED');
161 hr_utility.raise_error;
162 End if;
163 end if;
164 --
165 -- Can refernce only templates marked as reference templates.
166 --
167 if p_reference_type_cd = 'REFERENCE' and l_attribute_only_flag <> 'Y' then
168 hr_utility.set_message(8302,'PQH_INVALID_RFT');
169 hr_utility.raise_error;
170 end if;
171
172 end if;
173 --
174 hr_utility.set_location('Leaving:'||l_proc,10);
175 --
176 End chk_base_template_id;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |------< chk_parent_template_id >------|
180 -- ----------------------------------------------------------------------------
181 --
182 -- Description
183 -- This procedure checks that a referenced foreign key actually exists
184 -- in the referenced table.
185 --
186 -- Pre-Conditions
187 -- None.
188 --
189 -- In Parameters
190 -- p_ref_template_id PK
191 -- p_parent_template_id ID of FK column
192 -- p_object_version_number object version number
193 --
194 -- Post Success
195 -- Processing continues
196 --
197 -- Post Failure
198 -- Error raised.
199 --
200 -- Access Status
201 -- Internal table handler use only.
202 --
203 Procedure chk_parent_template_id (p_ref_template_id in number,
204 p_parent_template_id in number,
205 p_reference_type_cd in varchar2,
206 p_object_version_number in number) is
207 --
208 l_proc varchar2(72) := g_package||'chk_parent_template_id';
209 l_api_updating boolean;
210 l_dummy varchar2(1);
211 l_freeze_status_cd pqh_templates.freeze_status_cd%type;
212 l_attribute_only_flag pqh_templates.attribute_only_flag%type;
213 --
214 cursor c1 is
215 select nvl(freeze_status_cd,hr_api.g_varchar2) ,
216 nvl(attribute_only_flag,hr_api.g_varchar2)
217 from pqh_templates a
218 where a.template_id = p_parent_template_id;
219 --
220 Begin
221 --
222 hr_utility.set_location('Entering:'||l_proc,5);
223 --
224 l_api_updating := pqh_rft_shd.api_updating
225 (p_ref_template_id => p_ref_template_id,
226 p_object_version_number => p_object_version_number);
227 --
228 if (l_api_updating
229 and nvl(p_parent_template_id,hr_api.g_number)
230 <> nvl(pqh_rft_shd.g_old_rec.parent_template_id,hr_api.g_number)
231 or not l_api_updating) and
232 p_parent_template_id is not null then
233 --
234 -- check if parent_template_id value exists in pqh_templates table
235 --
236 open c1;
237 --
238 fetch c1 into l_freeze_status_cd,l_attribute_only_flag;
239 if c1%notfound then
240 --
241 close c1;
242 --
243 -- raise error as FK does not relate to PK in pqh_templates
244 -- table.
245 --
246 pqh_rft_shd.constraint_error('PQH_REF_TEMPLATES_FK1');
247 --
248 end if;
249 --
250 close c1;
251 --
252 -- If inserting reference template to a parent reference template
253 -- raise error
254 --
255 if l_attribute_only_flag = 'Y' and p_reference_type_cd = 'REFERENCE' then
256 hr_utility.set_message(8302,'PQH_PARENT_TEM_IS_ATTR_ONLY');
257 hr_utility.raise_error;
258 End if;
259 --
260 end if;
261 --
262 if l_freeze_status_cd = 'FREEZE_TEMPLATE' then
263 hr_utility.set_message(8302,'PQH_NO_ADD_REF_TO_PARENT');
264 hr_utility.raise_error;
265 end if;
266 hr_utility.set_location('Leaving:'||l_proc,10);
267 --
268 End chk_parent_template_id;
269 --
270 -- Check the compatability of the legislation code of the parent/base templates
271 --
272 Procedure chk_legislation_code (p_ref_template_id in number,
273 p_parent_template_id in number,
274 p_base_template_id in number,
275 p_object_version_number in number) is
276 --
277 l_proc varchar2(72) := g_package||'chk_legislation_code';
278 l_api_updating boolean;
279 l_dummy varchar2(1);
280 --
281 l_base_leg_code varchar2(30);
282 l_base_template_name varchar2(100);
283 l_parent_leg_code varchar2(30);
284 l_parent_template_name varchar2(100);
285 --
286 cursor c1(p_template_id number) is
287 select legislation_code, template_name
288 from pqh_templates_vl
289 where template_id = p_template_id;
290 --
291 Begin
292 --
293 hr_utility.set_location('Entering:'||l_proc,5);
294 --
295
296 l_api_updating := pqh_rft_shd.api_updating
297 (p_ref_template_id => p_ref_template_id,
298 p_object_version_number => p_object_version_number);
299 --
300 if (l_api_updating
301 and nvl(p_base_template_id,hr_api.g_number)
302 <> nvl(pqh_rft_shd.g_old_rec.base_template_id,hr_api.g_number)
303 or not l_api_updating) then
304 --
305 open c1(p_parent_template_id);
306 fetch c1 into l_parent_leg_code, l_parent_template_name;
307 close c1;
308 open c1(p_base_template_id);
309 fetch c1 into l_base_leg_code, l_base_template_name;
310 close c1;
311 --
312 if l_parent_leg_code is null then
313 if l_base_leg_code is not null then
314 hr_utility.set_message(8302,'PQH_NO_LEG_REF_TEM');
315 hr_utility.set_message_token('REF_TEMPLATE', l_base_template_name);
316 hr_utility.set_message_token('PARENT_TEMPLATE', l_parent_template_name);
317 hr_utility.raise_error;
318 end if;
319 else
320 if nvl(l_base_leg_code,l_parent_leg_code) <> l_parent_leg_code then
321 hr_utility.set_message(8302,'PQH_REF_TEM_LEG_NE_PAR_TEM');
322 hr_utility.set_message_token('REF_TEMPLATE', l_base_template_name);
323 hr_utility.set_message_token('PARENT_TEMPLATE', l_parent_template_name);
324 hr_utility.raise_error;
325 end if;
326 end if;
327 --
328 --
329 end if;
330 --
331 hr_utility.set_location('Leaving:'||l_proc,10);
332 --
333 End chk_legislation_code;
334 --
335 --
336 -- ----------------------------------------------------------------------------
337 -- |------< chk_reference_type_cd >------|
338 -- ----------------------------------------------------------------------------
339 --
340 -- Description
341 -- This procedure is used to check that the lookup value is valid.
342 --
343 -- Pre Conditions
344 -- None.
345 --
346 -- In Parameters
347 -- ref_template_id PK
348 -- reference_type_cd lookup
349 -- effective_date effective date
350 -- object_version_number Object version number of record being
351 -- inserted or updated.
352 --
353 -- Post Success
354 -- Processing continues
355 --
356 -- Post Failure
357 -- Error handled by procedure
358 --
359 -- Access Status
360 -- Internal table handler use only.
361 --
362 Procedure chk_reference_type_cd(
363 p_ref_template_id in number,
364 p_reference_type_cd in varchar2,
365 p_effective_date in date,
366 p_object_version_number in number) is
367 --
368 l_proc varchar2(72) := g_package||'chk_reference_type_cd';
369 l_api_updating boolean;
370 --
371 Begin
372 --
376 (p_ref_template_id => p_ref_template_id,
373 hr_utility.set_location('Entering:'||l_proc, 5);
374 --
375 l_api_updating := pqh_rft_shd.api_updating
377 p_object_version_number => p_object_version_number);
378 --
379 if (l_api_updating
380 and p_reference_type_cd
381 <> nvl(pqh_rft_shd.g_old_rec.reference_type_cd,hr_api.g_varchar2)
382 or not l_api_updating) then
383 --
384 -- check if value of lookup falls within lookup type.
385 --
386 if hr_api.not_exists_in_hr_lookups
387 (p_lookup_type => 'PQH_REFERENCE_TYPE',
388 p_lookup_code => p_reference_type_cd,
389 p_effective_date => p_effective_date) then
390 --
391 -- raise error as does not exist as lookup
392 --
393 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
394 hr_utility.raise_error;
395 --
396 end if;
397 --
398 end if;
399 --
400 hr_utility.set_location('Leaving:'||l_proc,10);
401 --
402 end chk_reference_type_cd;
403 --
404 --
405 -- ----------------------------------------------------------------------------
406 -- |------< chk_rft_tct >------|
407 -- ----------------------------------------------------------------------------
408 -- Description
409 -- This procedure checks if the parent template and base template have the
410 -- same transaction category id.
411 --
412 -- Pre-Conditions
413 -- None.
414 --
415 -- In Parameters
416 -- p_ref_template_id PK
417 -- p_parent_template_id ID of FK column
418 -- p_base_template_id ID of FK column
419 -- p_object_version_number object version number
420 --
421 -- Post Success
422 -- Processing continues
423 --
424 -- Post Failure
425 -- Error raised.
426 --
427 -- Access Status
428 -- Internal table handler use only.
429 Procedure chk_rft_tct (p_ref_template_id in number,
430 p_parent_template_id in number,
431 p_base_template_id in number,
432 p_object_version_number in number) is
433 --
434 l_proc varchar2(72) := g_package||'chk_rft_tct';
435 l_api_updating boolean;
436 l_dummy varchar2(1);
437 l_parent_tct pqh_templates.transaction_category_id%type;
438 l_base_tct pqh_templates.transaction_category_id%type;
439 --
440 cursor c1 is
441 select transaction_category_id
442 from pqh_templates a
443 where a.template_id = p_parent_template_id;
444
445 cursor c2 is
446 select transaction_category_id
447 from pqh_templates a
448 where a.template_id = p_base_template_id;
449 Begin
450 --
451 hr_utility.set_location('Entering:'||l_proc,5);
452 --
453 l_api_updating := pqh_rft_shd.api_updating
454 (p_ref_template_id => p_ref_template_id,
455 p_object_version_number => p_object_version_number);
456 --
457 if (l_api_updating
458 and nvl(p_parent_template_id,hr_api.g_number)
459 <> nvl(pqh_rft_shd.g_old_rec.parent_template_id,hr_api.g_number)
460 or not l_api_updating) and
461 p_parent_template_id is not null then
462 --
463 --
464 open c1;
465 fetch c1 into l_parent_tct;
466 close c1;
467
468 open c2;
469 fetch c2 into l_base_tct;
470 close c2;
471 --
472
473 if l_base_tct <> l_parent_tct then
474 hr_utility.set_message(8302,'PQH_RFT_TCT_MISMATCH');
475 hr_utility.raise_error;
476 End if;
477 end if;
478 --
479 hr_utility.set_location('Leaving:'||l_proc,10);
480 --
481 End chk_rft_tct;
482
483 -- ----------------------------------------------------------------------------
484 -- |---------------------------< insert_validate >----------------------------|
485 -- ----------------------------------------------------------------------------
486 Procedure insert_validate(p_rec in pqh_rft_shd.g_rec_type
487 ,p_effective_date in date) is
488 --
489 l_proc varchar2(72) := g_package||'insert_validate';
490 --
491 Begin
492 hr_utility.set_location('Entering:'||l_proc, 5);
493 --
494 -- Call all supporting business operations
495 --
496 chk_ref_template_id
497 (p_ref_template_id => p_rec.ref_template_id,
498 p_object_version_number => p_rec.object_version_number);
499 --
500 chk_base_template_id
501 (p_ref_template_id => p_rec.ref_template_id,
502 p_base_template_id => p_rec.base_template_id,
503 p_reference_type_cd => p_rec.reference_type_cd,
504 p_object_version_number => p_rec.object_version_number);
505 --
506 chk_parent_template_id
507 (p_ref_template_id => p_rec.ref_template_id,
508 p_parent_template_id => p_rec.parent_template_id,
509 p_reference_type_cd => p_rec.reference_type_cd,
510 p_object_version_number => p_rec.object_version_number);
511 --
512 chk_reference_type_cd
513 (p_ref_template_id => p_rec.ref_template_id,
514 p_reference_type_cd => p_rec.reference_type_cd,
515 p_effective_date => p_effective_date,
516 p_object_version_number => p_rec.object_version_number);
517 --
518 chk_rft_tct
519 (p_ref_template_id => p_rec.ref_template_id,
520 p_parent_template_id => p_rec.parent_template_id,
521 p_base_template_id => p_rec.base_template_id,
522 p_object_version_number => p_rec.object_version_number);
523 --
524 --
525 --
526 chk_legislation_code
527 (p_ref_template_id => p_rec.ref_template_id,
528 p_parent_template_id => p_rec.parent_template_id,
529 p_base_template_id => p_rec.base_template_id,
530 p_object_version_number => p_rec.object_version_number);
531 --
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533 End insert_validate;
534 --
535 -- ----------------------------------------------------------------------------
536 -- |---------------------------< update_validate >----------------------------|
537 -- ----------------------------------------------------------------------------
538 Procedure update_validate(p_rec in pqh_rft_shd.g_rec_type
539 ,p_effective_date in date) is
540 --
541 l_proc varchar2(72) := g_package||'update_validate';
542 --
543 Begin
544 hr_utility.set_location('Entering:'||l_proc, 5);
545 --
546 -- Call all supporting business operations
547 --
548 chk_ref_template_id
549 (p_ref_template_id => p_rec.ref_template_id,
550 p_object_version_number => p_rec.object_version_number);
551 --
552 chk_base_template_id
556 p_object_version_number => p_rec.object_version_number);
553 (p_ref_template_id => p_rec.ref_template_id,
554 p_base_template_id => p_rec.base_template_id,
555 p_reference_type_cd => p_rec.reference_type_cd,
557 --
558 chk_parent_template_id
559 (p_ref_template_id => p_rec.ref_template_id,
560 p_parent_template_id => p_rec.parent_template_id,
561 p_reference_type_cd => p_rec.reference_type_cd,
562 p_object_version_number => p_rec.object_version_number);
563 --
564 --
565 chk_reference_type_cd
566 (p_ref_template_id => p_rec.ref_template_id,
567 p_reference_type_cd => p_rec.reference_type_cd,
568 p_effective_date => p_effective_date,
569 p_object_version_number => p_rec.object_version_number);
570 --
571 chk_rft_tct
572 (p_ref_template_id => p_rec.ref_template_id,
573 p_parent_template_id => p_rec.parent_template_id,
574 p_base_template_id => p_rec.base_template_id,
575 p_object_version_number => p_rec.object_version_number);
576 --
577 --
578 chk_legislation_code
579 (p_ref_template_id => p_rec.ref_template_id,
580 p_parent_template_id => p_rec.parent_template_id,
581 p_base_template_id => p_rec.base_template_id,
582 p_object_version_number => p_rec.object_version_number);
583 --
584 --
585 --
586 hr_utility.set_location(' Leaving:'||l_proc, 10);
587 End update_validate;
588 --
589 -- ----------------------------------------------------------------------------
590 -- |---------------------------< delete_validate >----------------------------|
591 -- ----------------------------------------------------------------------------
592 Procedure delete_validate(p_rec in pqh_rft_shd.g_rec_type
593 ,p_effective_date in date) is
594 --
595 l_proc varchar2(72) := g_package||'delete_validate';
596 --
597 Begin
598 hr_utility.set_location('Entering:'||l_proc, 5);
599 --
600 -- Call all supporting business operations
601 --
602 hr_utility.set_location(' Leaving:'||l_proc, 10);
603 End delete_validate;
604 --
605 end pqh_rft_bus;