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