[Home] [Help]
PACKAGE BODY: APPS.PQH_FYN_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body pqh_fyn_bus as
2 /* $Header: pqfynrhi.pkb 115.6 2002/12/06 18:06:27 rpasapul ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_fyn_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_fyi_notified_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 -- fyi_notified_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_fyi_notified_id(p_fyi_notified_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_fyi_notified_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_fyn_shd.api_updating
47 (p_fyi_notified_id => p_fyi_notified_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_fyi_notified_id,hr_api.g_number)
52 <> pqh_fyn_shd.g_old_rec.fyi_notified_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_fyn_shd.constraint_error('PQH_FYI_NOTIFY_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_fyi_notified_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_fyn_shd.constraint_error('PQH_FYI_NOTIFY_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_fyi_notified_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_transaction_category_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_fyi_notified_id PK
89 -- p_transaction_category_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
96 -- Error raised.
93 -- Processing continues
94 --
95 -- Post Failure
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_transaction_category_id (p_fyi_notified_id in number,
102 p_transaction_category_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_transaction_categories a
112 where a.transaction_category_id = p_transaction_category_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_fyn_shd.api_updating
119 (p_fyi_notified_id => p_fyi_notified_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_transaction_category_id,hr_api.g_number)
124 <> nvl(pqh_fyn_shd.g_old_rec.transaction_category_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if transaction_category_id value exists in pqh_transaction_categories 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_transaction_categories
137 -- table.
138 --
139 pqh_fyn_shd.constraint_error('PQH_FYI_NOTIFY_FK1');
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_transaction_category_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_notified_type_cd >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure is used to check that the lookup value is valid.
157 --
158 -- Pre Conditions
159 -- None.
160 --
161 -- In Parameters
162 -- fyi_notified_id PK of record being inserted or updated.
163 -- notified_type_cd Value of lookup code.
164 -- effective_date effective date
165 -- object_version_number Object version number of record being
166 -- inserted or updated.
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error handled by procedure
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_notified_type_cd(p_fyi_notified_id in number,
178 p_notified_type_cd in varchar2,
179 p_effective_date in date,
180 p_object_version_number in number) is
181 --
182 l_proc varchar2(72) := g_package||'chk_notified_type_cd';
183 l_api_updating boolean;
184 --
185 Begin
186 --
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
192 --
189 l_api_updating := pqh_fyn_shd.api_updating
190 (p_fyi_notified_id => p_fyi_notified_id,
191 p_object_version_number => p_object_version_number);
193 if (l_api_updating
194 and p_notified_type_cd
195 <> nvl(pqh_fyn_shd.g_old_rec.notified_type_cd,hr_api.g_varchar2)
196 or not l_api_updating) then
197 --
198 -- check if value of lookup falls within lookup type.
199 --
200 hr_utility.set_location('Type Cd: '||p_notified_type_cd, 10);
201 --
202 if hr_api.not_exists_in_hr_lookups
203 (p_lookup_type => 'PQH_NOTIFIED_TYPE_CD',
204 p_lookup_code => p_notified_type_cd,
205 p_effective_date => p_effective_date) then
206 --
207 -- raise error as does not exist as lookup
208 --
209 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
210 hr_utility.raise_error;
211 --
212 end if;
213 --
214 end if;
215 --
216 hr_utility.set_location('Leaving:'||l_proc,10);
217 --
218 end chk_notified_type_cd;
219 --
220 -- ----------------------------------------------------------------------------
221 -- |------< chk_notification_event_cd >------|
222 -- ----------------------------------------------------------------------------
223 --
224 -- Description
225 -- This procedure is used to check that the lookup value is valid.
226 --
227 -- Pre Conditions
228 -- None.
229 --
230 -- In Parameters
231 -- fyi_notified_id PK of record being inserted or updated.
232 -- notification_event_cd Value of lookup code.
233 -- effective_date effective date
234 -- object_version_number Object version number of record being
235 -- inserted or updated.
236 --
237 -- Post Success
238 -- Processing continues
239 --
240 -- Post Failure
241 -- Error handled by procedure
242 --
243 -- Access Status
244 -- Internal table handler use only.
245 --
246 Procedure chk_notification_event_cd(p_fyi_notified_id in number,
247 p_notification_event_cd in varchar2,
248 p_effective_date in date,
249 p_object_version_number in number) is
250 --
251 l_proc varchar2(72) := g_package||'chk_notification_event_cd';
252 l_api_updating boolean;
253 --
254 Begin
255 --
256 hr_utility.set_location('Entering:'||l_proc, 5);
257 --
258 l_api_updating := pqh_fyn_shd.api_updating
259 (p_fyi_notified_id => p_fyi_notified_id,
260 p_object_version_number => p_object_version_number);
261 --
262 if (l_api_updating
263 and p_notification_event_cd
264 <> nvl(pqh_fyn_shd.g_old_rec.notification_event_cd,hr_api.g_varchar2)
265 or not l_api_updating) then
266 --
267 -- check if value of lookup falls within lookup type.
268 --
269 --
270 if hr_api.not_exists_in_hr_lookups
271 (p_lookup_type => 'PQH_NOTIFICATION_EVENT_CD',
272 p_lookup_code => p_notification_event_cd,
273 p_effective_date => p_effective_date) then
274 --
275 -- raise error as does not exist as lookup
276 --
277 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
278 hr_utility.raise_error;
279 --
280 end if;
281 --
282 end if;
283 --
284 hr_utility.set_location('Leaving:'||l_proc,10);
285 --
286 end chk_notification_event_cd;
287 --
288 -- ----------------------------------------------------------------------------
289 -- |------< chk_notified_name >------|
290 -- ----------------------------------------------------------------------------
291 --
292 -- Description
293 -- This procedure is used to check that the lookup value is valid.
294 --
295 -- Pre Conditions
296 -- None.
297 --
298 -- In Parameters
299 -- fyi_notified_id PK of record being inserted or updated.
300 -- notification_event_cd Value of lookup code.
301 -- effective_date effective date
302 -- object_version_number Object version number of record being
303 -- inserted or updated.
304 --
305 -- Post Success
306 -- Processing continues
307 --
308 -- Post Failure
309 -- Error handled by procedure
310 --
311 -- Access Status
312 -- Internal table handler use only.
313 --
314 Procedure chk_notified_name(p_fyi_notified_id in number,
315 p_notified_name in varchar2,
316 p_notified_type_cd in varchar2,
317 p_effective_date in date,
318 p_object_version_number in number) is
319 --
320 l_proc varchar2(72) := g_package||'chk_notified_name';
321 l_api_updating boolean;
325 select null
322 l_dummy varchar2(1);
323 --
324 cursor c1 is
326 from wf_roles r
327 where r.name = p_notified_name;
328 --
329 Begin
330 --
331 hr_utility.set_location('Entering:'||l_proc, 5);
332 --
333 if p_notified_type_cd = 'OTHERS' THEN
334
335 --
336 -- check if notified_name exists in wf_roles table
337 --
338 open c1;
339 --
340 fetch c1 into l_dummy;
341 if c1%notfound then
342 --
343 close c1;
344 --
345 -- raise error
346 --
347 hr_utility.set_message(8302,'PQH_INVALID_NOTIFIED_NAME');
348 hr_utility.raise_error;
349 --
350 end if;
351 --
352 close c1;
353 --
354
355 else
356
357 --
358 -- notified_name should be null if p_notified_type_cd <> OTHERS
359 --
360 if p_notified_name is not null then
361 --
362 -- raise error
363 --
364 hr_utility.set_message(8302,'PQH_INVALID_NOTIFIED_NAME');
365 hr_utility.raise_error;
366 --
367
368 end if;
369
370 end if;
371 --
372 --
373 hr_utility.set_location('Leaving:'||l_proc,10);
374 --
375 end chk_notified_name;
376 --
377 -- ----------------------------------------------------------------------------
378 -- |---------------------------< insert_validate >----------------------------|
379 -- ----------------------------------------------------------------------------
380 Procedure insert_validate(p_rec in pqh_fyn_shd.g_rec_type
381 ,p_effective_date in date) is
382 --
383 l_proc varchar2(72) := g_package||'insert_validate';
384 --
385 Begin
386 hr_utility.set_location('Entering:'||l_proc, 5);
387 --
388 -- Call all supporting business operations
389 --
390 chk_fyi_notified_id
391 (p_fyi_notified_id => p_rec.fyi_notified_id,
392 p_object_version_number => p_rec.object_version_number);
393 --
394 chk_transaction_category_id
395 (p_fyi_notified_id => p_rec.fyi_notified_id,
396 p_transaction_category_id => p_rec.transaction_category_id,
397 p_object_version_number => p_rec.object_version_number);
398 --
399 chk_notified_type_cd
400 (p_fyi_notified_id => p_rec.fyi_notified_id,
401 p_notified_type_cd => p_rec.notified_type_cd,
402 p_effective_date => p_effective_date,
403 p_object_version_number => p_rec.object_version_number);
404 --
405 chk_notification_event_cd
406 (p_fyi_notified_id => p_rec.fyi_notified_id,
407 p_notification_event_cd => p_rec.notification_event_cd,
408 p_effective_date => p_effective_date,
409 p_object_version_number => p_rec.object_version_number);
410 --
411 chk_notified_name
412 (p_fyi_notified_id => p_rec.fyi_notified_id,
413 p_notified_name => p_rec.notified_name,
414 p_notified_type_cd => p_rec.notified_type_cd,
415 p_effective_date => p_effective_date,
416 p_object_version_number => p_rec.object_version_number);
417 --
418 --
419 --
420 hr_utility.set_location(' Leaving:'||l_proc, 10);
421 End insert_validate;
422 --
423 -- ----------------------------------------------------------------------------
424 -- |---------------------------< update_validate >----------------------------|
425 -- ----------------------------------------------------------------------------
426 Procedure update_validate(p_rec in pqh_fyn_shd.g_rec_type
427 ,p_effective_date in date) is
428 --
429 l_proc varchar2(72) := g_package||'update_validate';
430 --
431 Begin
432 hr_utility.set_location('Entering:'||l_proc, 5);
433 --
434 -- Call all supporting business operations
435 --
436 chk_fyi_notified_id
437 (p_fyi_notified_id => p_rec.fyi_notified_id,
438 p_object_version_number => p_rec.object_version_number);
439 --
440 chk_transaction_category_id
441 (p_fyi_notified_id => p_rec.fyi_notified_id,
442 p_transaction_category_id => p_rec.transaction_category_id,
443 p_object_version_number => p_rec.object_version_number);
444 --
445 chk_notified_type_cd
446 (p_fyi_notified_id => p_rec.fyi_notified_id,
447 p_notified_type_cd => p_rec.notified_type_cd,
448 p_effective_date => p_effective_date,
449 p_object_version_number => p_rec.object_version_number);
450 --
451 chk_notification_event_cd
452 (p_fyi_notified_id => p_rec.fyi_notified_id,
453 p_notification_event_cd => p_rec.notification_event_cd,
454 p_effective_date => p_effective_date,
455 p_object_version_number => p_rec.object_version_number);
456 --
457 chk_notified_name
458 (p_fyi_notified_id => p_rec.fyi_notified_id,
459 p_notified_name => p_rec.notified_name,
460 p_notified_type_cd => p_rec.notified_type_cd,
461 p_effective_date => p_effective_date,
462 p_object_version_number => p_rec.object_version_number);
463 --
464 --
465 --
466 hr_utility.set_location(' Leaving:'||l_proc, 10);
467 End update_validate;
468 --
469 -- ----------------------------------------------------------------------------
470 -- |---------------------------< delete_validate >----------------------------|
471 -- ----------------------------------------------------------------------------
472 Procedure delete_validate(p_rec in pqh_fyn_shd.g_rec_type
476 --
473 ,p_effective_date in date) is
474 --
475 l_proc varchar2(72) := g_package||'delete_validate';
477 Begin
478 hr_utility.set_location('Entering:'||l_proc, 5);
479 --
480 -- Call all supporting business operations
481 --
482 hr_utility.set_location(' Leaving:'||l_proc, 10);
483 End delete_validate;
484 --
485 end pqh_fyn_bus;