[Home] [Help]
PACKAGE BODY: APPS.PQH_CET_BUS
Source
1 Package Body pqh_cet_bus as
2 /* $Header: pqcetrhi.pkb 120.2 2005/10/01 10:56:44 scnair noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_cet_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_copy_entity_txn_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 -- copy_entity_txn_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_copy_entity_txn_id(p_copy_entity_txn_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_copy_entity_txn_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_cet_shd.api_updating
47 (p_copy_entity_txn_id => p_copy_entity_txn_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_copy_entity_txn_id,hr_api.g_number)
52 <> pqh_cet_shd.g_old_rec.copy_entity_txn_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_cet_shd.constraint_error('PQH_COPY_ENTITY_TXNS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_copy_entity_txn_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_cet_shd.constraint_error('PQH_COPY_ENTITY_TXNS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_copy_entity_txn_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_any_completed_target---|
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_copy_entity_txn_id PK
89 -- p_txn_category_attribute_idf 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_completed_target_err (p_copy_entity_txn_id in number) is
102 --
103 l_proc varchar2(72) := g_package||'chk_completed_target_err';
104 l_api_updating boolean;
105 l_dummy varchar2(1);
106 --
107 Begin
108 --
109 hr_utility.set_location('Entering:'||l_proc,5);
110 --
111 if pqh_cet_bus.chk_completed_target(p_copy_entity_txn_id) then
112 --
113 -- raise error as atleast one target record has been successfully completed
114 --
115 pqh_cet_shd.constraint_error('PQH_COMPLETED_TARGET');
116 end if;
117 --
118 hr_utility.set_location('Leaving:'||l_proc,10);
119 --
120 End chk_completed_target_err;
121 --
122 function chk_completed_target(p_copy_entity_txn_id in number) return boolean is
123 --
124 l_proc varchar2(72) := g_package||'chk_completed_target';
125 l_api_updating boolean;
126 l_dummy varchar2(1);
127 --
128 cursor c1 is
129 select null
130 from pqh_copy_entity_results a
131 where a.copy_entity_txn_id = p_copy_entity_txn_id
132 and a.result_type_cd = 'TARGET'
133 and a.status = 'COMPLETED' ;
134 --
135 Begin
136 --
137 hr_utility.set_location('Entering:'||l_proc,5);
138 --
139 if p_copy_entity_txn_id is not null then
140 --
141 open c1;
142 --
143 fetch c1 into l_dummy;
144 if c1%found then
145 --
146 close c1;
147 --
148 -- return true as atleast one target record has been found successfully completed
149 --
150 return (TRUE);
151 --
152 end if;
153 --
154 close c1;
155 --
156 end if;
157 --
158 return (FALSE);
159 --
160 hr_utility.set_location('Leaving:'||l_proc,10);
161 --
162 End chk_completed_target;
163 --
164 -- ----------------------------------------------------------------------------
165 -- |------< chk_txn_category_attribute_id---|
166 -- ----------------------------------------------------------------------------
167 --
168 -- Description
169 -- This procedure checks that a referenced foreign key actually exists
170 -- in the referenced table.
171 --
172 -- Pre-Conditions
173 -- None.
174 --
175 -- In Parameters
176 -- p_copy_entity_txn_id PK
177 -- p_txn_category_attribute_idf FK column
178 -- p_object_version_number object version number
179 --
180 -- Post Success
181 -- Processing continues
182 --
183 -- Post Failure
184 -- Error raised.
185 --
186 -- Access Status
187 -- Internal table handler use only.
188 --
189 Procedure chk_txn_category_attribute_id (p_copy_entity_txn_id in number,
190 p_txn_category_attribute_id in number,
191 p_object_version_number in number) is
192 --
193 l_proc varchar2(72) := g_package||'chk_txn_category_attribute_id';
194 l_api_updating boolean;
195 l_dummy varchar2(1);
196 --
197 cursor c1 is
198 select null
199 from pqh_txn_category_attributes a
200 where a.txn_category_attribute_id = p_txn_category_attribute_id;
201 --
202 Begin
203 --
204 hr_utility.set_location('Entering:'||l_proc,5);
205 --
206 l_api_updating := pqh_cet_shd.api_updating
207 (p_copy_entity_txn_id => p_copy_entity_txn_id,
208 p_object_version_number => p_object_version_number);
209 --
210 if (l_api_updating
211 and nvl(p_txn_category_attribute_id,hr_api.g_number)
212 <> nvl(pqh_cet_shd.g_old_rec.txn_category_attribute_id,hr_api.g_number)
213 or not l_api_updating) then
214 --
215 -- check if txn_category_attribute_id value exists in pqh_special_attributes table
216 --
217 open c1;
218 --
219 fetch c1 into l_dummy;
220 if c1%notfound then
221 --
222 close c1;
223 --
224 -- raise error as FK does not relate to PK in pqh_special_attributes
225 -- table.
226 --
227 pqh_cet_shd.constraint_error('PQH_COPY_ENTITY_TXNS_FK2');
228 --
229 end if;
230 --
231 close c1;
232 --
233 end if;
234 --
235 hr_utility.set_location('Leaving:'||l_proc,10);
236 --
237 End chk_txn_category_attribute_id;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |------< chk_context_business_group_id---|
241 -- ----------------------------------------------------------------------------
242 --
243 -- Description
244 -- This procedure checks that a referenced foreign key actually exists
245 -- in the referenced table.
246 --
247 -- Pre-Conditions
248 -- None.
249 --
250 -- In Parameters
251 -- p_copy_entity_txn_id PK
252 -- p_context_business_group_idf FK column
253 -- p_object_version_number object version number
254 --
255 -- Post Success
256 -- Processing continues
257 --
258 -- Post Failure
259 -- Error raised.
260 --
261 -- Access Status
262 -- Internal table handler use only.
263 --
264 Procedure chk_context_business_group_id (p_copy_entity_txn_id in number,
265 p_context_business_group_id in number,
266 p_object_version_number in number) is
267 --
268 l_proc varchar2(72) := g_package||'chk_context_business_group_id';
269 l_api_updating boolean;
270 l_dummy varchar2(1);
271 --
272 cursor c1 is
273 select null
274 from per_business_groups a
275 where a.business_group_id = p_context_business_group_id;
276 --
277 Begin
278 --
279 hr_utility.set_location('Entering:'||l_proc,5);
280 --
281 l_api_updating := pqh_cet_shd.api_updating
282 (p_copy_entity_txn_id => p_copy_entity_txn_id,
283 p_object_version_number => p_object_version_number);
284 --
285 if (l_api_updating
286 and nvl(p_context_business_group_id,hr_api.g_number)
287 <> nvl(pqh_cet_shd.g_old_rec.context_business_group_id,hr_api.g_number)
288 or not l_api_updating) then
289 --
290 -- check if context_business_group_id value exists in per_business_groups table
291 --
292 open c1;
293 --
294 fetch c1 into l_dummy;
295 if c1%notfound then
296 --
297 close c1;
298 --
299 -- raise error as FK does not relate to PK in per_business_groups
300 -- table.
301 --
302 pqh_cet_shd.constraint_error('PQH_COPY_ENTITY_TXNS_FK2');
303 --
304 end if;
305 --
306 close c1;
307 --
308 end if;
309 --
310 hr_utility.set_location('Leaving:'||l_proc,10);
311 --
312 End chk_context_business_group_id;
313 --
314 -- ----------------------------------------------------------------------------
315 -- |------< chk_transaction_category_id >------|
316 -- ----------------------------------------------------------------------------
317 --
318 -- Description
319 -- This procedure checks that a referenced foreign key actually exists
320 -- in the referenced table.
321 --
322 -- Pre-Conditions
323 -- None.
324 --
325 -- In Parameters
326 -- p_copy_entity_txn_id PK
327 -- p_transaction_category_id ID of FK column
328 -- p_object_version_number object version number
329 --
330 -- Post Success
331 -- Processing continues
332 --
333 -- Post Failure
334 -- Error raised.
335 --
336 -- Access Status
337 -- Internal table handler use only.
338 --
342 --
339 Procedure chk_transaction_category_id (p_copy_entity_txn_id in number,
340 p_transaction_category_id in number,
341 p_object_version_number in number) is
343 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
344 l_api_updating boolean;
345 l_dummy varchar2(1);
346 --
347 cursor c1 is
348 select null
349 from pqh_transaction_categories a
350 where a.transaction_category_id = p_transaction_category_id;
351 --
352 Begin
353 --
354 hr_utility.set_location('Entering:'||l_proc,5);
355 --
356 l_api_updating := pqh_cet_shd.api_updating
357 (p_copy_entity_txn_id => p_copy_entity_txn_id,
358 p_object_version_number => p_object_version_number);
359 --
360 if (l_api_updating
361 and nvl(p_transaction_category_id,hr_api.g_number)
362 <> nvl(pqh_cet_shd.g_old_rec.transaction_category_id,hr_api.g_number)
363 or not l_api_updating) then
364 --
365 -- check if transaction_category_id value exists in pqh_transaction_categories table
366 --
367 open c1;
368 --
369 fetch c1 into l_dummy;
370 if c1%notfound then
371 --
372 close c1;
373 --
374 -- raise error as FK does not relate to PK in pqh_transaction_categories
375 -- table.
376 --
377 pqh_cet_shd.constraint_error('PQH_COPY_ENTITY_TXNS_FK1');
378 --
379 end if;
380 --
381 close c1;
382 --
383 end if;
384 --
385 hr_utility.set_location('Leaving:'||l_proc,10);
386 --
387 End chk_transaction_category_id;
388 --
389 -- ----------------------------------------------------------------------------
390 -- |------< chk_replacement_type_cd >------|
391 -- ----------------------------------------------------------------------------
392 --
393 -- Description
394 -- This procedure is used to check that the lookup value is valid.
395 --
396 -- Pre Conditions
397 -- None.
398 --
399 -- In Parameters
400 -- copy_entity_txn_id PK of record being inserted or updated.
401 -- replacement_type_cd Value of lookup code.
402 -- effective_date effective date
403 -- object_version_number Object version number of record being
404 -- inserted or updated.
405 --
406 -- Post Success
407 -- Processing continues
408 --
409 -- Post Failure
410 -- Error handled by procedure
411 --
412 -- Access Status
413 -- Internal table handler use only.
414 --
415 Procedure chk_replacement_type_cd(p_copy_entity_txn_id in number,
416 p_replacement_type_cd in varchar2,
417 p_effective_date in date,
418 p_object_version_number in number) is
419 --
420 l_proc varchar2(72) := g_package||'chk_replacement_type_cd';
421 l_api_updating boolean;
422 --
423 Begin
424 --
425 hr_utility.set_location('Entering:'||l_proc, 5);
426 --
427 l_api_updating := pqh_cet_shd.api_updating
428 (p_copy_entity_txn_id => p_copy_entity_txn_id,
429 p_object_version_number => p_object_version_number);
430 --
431 if (l_api_updating
432 and p_replacement_type_cd
433 <> nvl(pqh_cet_shd.g_old_rec.replacement_type_cd,hr_api.g_varchar2)
434 or not l_api_updating)
435 and p_replacement_type_cd is not null then
436 --
437 -- check if value of lookup falls within lookup type.
438 --
439 if hr_api.not_exists_in_hr_lookups
440 (p_lookup_type => 'PQH_GEN_REPL_TYPE',
441 p_lookup_code => p_replacement_type_cd,
442 p_effective_date => p_effective_date) then
443 --
444 -- raise error as does not exist as lookup
445 --
446 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
447 hr_utility.raise_error;
448 --
449 end if;
450 --
451 end if;
452 --
453 hr_utility.set_location('Leaving:'||l_proc,10);
454 --
455 end chk_replacement_type_cd;
456 --
457 -- ----------------------------------------------------------------------------
458 -- |---------------------------< insert_validate >----------------------------|
459 -- ----------------------------------------------------------------------------
460 Procedure insert_validate(p_rec in pqh_cet_shd.g_rec_type
461 ,p_effective_date in date) is
462 --
463 l_proc varchar2(72) := g_package||'insert_validate';
464 --
465 Begin
466 hr_utility.set_location('Entering:'||l_proc, 5);
467 --
468 -- Call all supporting business operations
469 --
470 chk_copy_entity_txn_id
471 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
472 p_object_version_number => p_rec.object_version_number);
473 --
474 chk_transaction_category_id
475 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
476 p_transaction_category_id => p_rec.transaction_category_id,
477 p_object_version_number => p_rec.object_version_number);
478 --
479 chk_replacement_type_cd
480 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
481 p_replacement_type_cd => p_rec.replacement_type_cd,
482 p_effective_date => p_effective_date,
483 p_object_version_number => p_rec.object_version_number);
484 --
485 --
486 --
487 hr_utility.set_location(' Leaving:'||l_proc, 10);
488 End insert_validate;
489 --
490 -- ----------------------------------------------------------------------------
491 -- |---------------------------< update_validate >----------------------------|
492 -- ----------------------------------------------------------------------------
493 Procedure update_validate(p_rec in pqh_cet_shd.g_rec_type
494 ,p_effective_date in date) is
495 --
496 l_proc varchar2(72) := g_package||'update_validate';
497 --
498 Begin
499 hr_utility.set_location('Entering:'||l_proc, 5);
500 --
501 -- Call all supporting business operations
502 --
503 chk_copy_entity_txn_id
504 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
505 p_object_version_number => p_rec.object_version_number);
506 --
507 chk_transaction_category_id
508 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
509 p_transaction_category_id => p_rec.transaction_category_id,
510 p_object_version_number => p_rec.object_version_number);
511 --
512 chk_replacement_type_cd
513 (p_copy_entity_txn_id => p_rec.copy_entity_txn_id,
514 p_replacement_type_cd => p_rec.replacement_type_cd,
515 p_effective_date => p_effective_date,
516 p_object_version_number => p_rec.object_version_number);
517 --
518 --
519 --
520 hr_utility.set_location(' Leaving:'||l_proc, 10);
521 End update_validate;
522 --
523 -- ----------------------------------------------------------------------------
524 -- |---------------------------< delete_validate >----------------------------|
525 -- ----------------------------------------------------------------------------
526 Procedure delete_validate(p_rec in pqh_cet_shd.g_rec_type
527 ,p_effective_date in date) is
528 --
529 l_proc varchar2(72) := g_package||'delete_validate';
530 --
531 Begin
532 hr_utility.set_location('Entering:'||l_proc, 5);
533 --
534 -- Call all supporting business operations
535 --
536 hr_utility.set_location(' Leaving:'||l_proc, 10);
537 End delete_validate;
538 --
539 end pqh_cet_bus;