[Home] [Help]
PACKAGE BODY: APPS.PER_CN_ORG_INFO_LEG_HOOK
Source
1 PACKAGE BODY per_cn_org_info_leg_hook AS
2 /* $Header: pecnlhoi.pkb 120.4.12010000.5 2008/08/06 09:07:14 ubhat ship $ */
3
4 g_package VARCHAR2(25);
5 g_debug BOOLEAN;
6 g_token_name hr_cn_api.char_tab_type;
7 g_token_value hr_cn_api.char_tab_type;
8 g_message_name VARCHAR2(30);
9
10 --------------------------------------------------------------------------
11 -- Name : CHECK_RECORD_EXISTS --
12 -- Type : Procedure --
13 -- Access : Private --
14 -- Description : Procedure is the driver procedure for the validation--
15 -- of the Organizaition Information data for the --
16 -- context IN_CONTRACTOR_INFO. --
17 -- Parameters : --
18 -- IN : p_org_information_id IN NUMBER --
19 -- p_org_info_type_code IN VARCHAR2 --
20 -- p_org_information1 IN VARCHAR2 --
21 -- p_org_information2 IN VARCHAR2 --
22 -- p_org_information3 IN VARCHAR2 --
23 -- p_org_information4 IN VARCHAR2 --
24 -- p_org_information5 IN VARCHAR2 --
25 --------------------------------------------------------------------------
26
27 PROCEDURE check_record_exists(p_org_information_id IN NUMBER
28 ,p_organization_id IN NUMBER
29 ,p_org_info_type_code IN VARCHAR2
30 ,p_org_information1 IN VARCHAR2
31 ,p_org_information2 IN VARCHAR2
32 ,p_org_information3 IN VARCHAR2
33 ,p_org_information4 IN VARCHAR2
34 ,p_effective_start_date IN DATE
35 ,p_effective_end_date IN DATE
36 )
37 AS
38
39 l_rec_count NUMBER;
40 l_fut_date DATE;
41
42 CURSOR csr_check_cont_base_setup
43 IS
44 SELECT count(org_information_id)
45 FROM hr_organization_information
46 WHERE org_information_context= 'PER_CONT_AREA_CONT_BASE_CN'
47 AND org_information1 = p_org_information1
48 AND org_information2 = p_org_information2
49 AND nvl(org_information3,'X') = nvl(p_org_information3,'X')
50 AND p_effective_start_date BETWEEN fnd_date.canonical_to_date(org_information15)
51 AND fnd_date.canonical_to_date(NVL(org_information16,'4712/12/31 00:00:00'))
52 AND organization_id = p_organization_id
53 AND (org_information_id <> p_org_information_id
54 OR p_org_information_id IS NULL);
55
56
57 CURSOR csr_check_phf_si_rates_setup
58 IS
59 SELECT count(org_information_id)
60 FROM hr_organization_information
61 WHERE org_information_context='PER_CONT_AREA_PHF_SI_RATES_CN'
62 AND org_information1 = p_org_information1
63 AND nvl(org_information2,'X') = nvl(p_org_information2,'X')
64 AND org_information3 = p_org_information3
65 AND nvl(org_information9,'X') = nvl(p_org_information4,'X')
66 AND p_effective_start_date BETWEEN fnd_date.canonical_to_date(org_information10)
67 AND fnd_date.canonical_to_date(NVL(org_information11,'4712/12/31 00:00:00'))
68 AND organization_id = p_organization_id
69 AND (org_information_id <> p_org_information_id
70 OR p_org_information_id IS NULL) ;
71
72 CURSOR csr_future_rec_cont_base
73 IS
74 SELECT min(fnd_date.canonical_to_date(org_information15))
75 FROM hr_organization_information
76 WHERE org_information1 = p_org_information1
77 AND org_information2 = p_org_information2
78 AND nvl(org_information3,'X') = nvl(p_org_information3,'X')
79 AND org_information_context='PER_CONT_AREA_CONT_BASE_CN'
80 AND NVL(org_information_id,-1) <> NVL(p_org_information_id,-1)
81 AND fnd_date.canonical_to_date(org_information15) > p_effective_start_date
82 AND organization_id = p_organization_id
83 AND (org_information_id <> p_org_information_id
84 OR p_org_information_id IS NULL);
85
86 CURSOR csr_future_rec_phfsi_base
87 IS
88 SELECT min(fnd_date.canonical_to_date(org_information10))
89 FROM hr_organization_information
90 WHERE org_information1 = p_org_information1
91 AND NVL(org_information2,'X') = NVL(p_org_information2,'X')
92 AND nvl(org_information3,'X') = nvl(p_org_information3,'X')
93 AND org_information_context='PER_CONT_AREA_PHF_SI_RATES_CN'
94 AND NVL(org_information_id,-1) <> NVL(p_org_information_id,-1)
95 AND fnd_date.canonical_to_date(org_information10) > p_effective_start_date
96 AND organization_id = p_organization_id
97 AND (org_information_id <> p_org_information_id
98 OR p_org_information_id IS NULL);
99
100
101 l_procedure VARCHAR2(100);
102
103 BEGIN
104
105 l_procedure := g_package || 'check_record_exists';
106 g_debug := hr_utility.debug_enabled;
107 g_debug := TRUE;
108
109 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
110
111 IF p_org_info_type_code = 'PER_CONT_AREA_CONT_BASE_CN' THEN
112
113 IF g_debug THEN
114 hr_utility.trace('Checking contribution base setup');
115 END IF;
116
117 OPEN csr_check_cont_base_setup;
118 FETCH csr_check_cont_base_setup INTO l_rec_count;
119 CLOSE csr_check_cont_base_setup;
120
121 OPEN csr_future_rec_cont_base;
122 FETCH csr_future_rec_cont_base INTO l_fut_date;
123 CLOSE csr_future_rec_cont_base;
124
125 IF g_debug THEN
126 hr_utility.trace('Ended Checking contribution base setup');
127 END IF;
128
129 ELSIF p_org_info_type_code ='PER_CONT_AREA_PHF_SI_RATES_CN' THEN
130
131 IF g_debug THEN
132 hr_utility.trace('Checking PHF/SI rates setup');
133 END IF;
134
135 OPEN csr_check_phf_si_rates_setup;
136 FETCH csr_check_phf_si_rates_setup INTO l_rec_count;
137 CLOSE csr_check_phf_si_rates_setup;
138
139 OPEN csr_future_rec_phfsi_base;
140 FETCH csr_future_rec_phfsi_base INTO l_fut_date;
141 CLOSE csr_future_rec_phfsi_base;
142
143 IF g_debug THEN
144 hr_utility.trace('Ended Checking PHF/SI Rates setup');
145 END IF;
146
147 END IF;
148
149 IF l_rec_count > 0 THEN
150
151 IF g_debug THEN
152 hr_utility.trace('Duplicate Record Exists');
153 END IF;
154
155 hr_utility.set_message(800,'PER_7901_SYS_DUPLICATE_RECORDS');
156 hr_utility.raise_error;
157
158 ELSIF l_fut_date IS NOT NULL THEN
159
160 IF p_effective_end_date >= l_fut_date OR p_effective_end_date IS NULL THEN
161
162 IF g_debug THEN
163 hr_utility.trace('Duplicate Record Exists');
164 END IF;
165
166 hr_utility.set_message(800,'PER_7901_SYS_DUPLICATE_RECORDS');
167 END IF;
168 hr_utility.raise_error;
169 END IF;
170
171 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,30);
172
173 END check_record_exists;
174 --------------------------------------------------------------------------
175 -- Name : check_term_stat_info --
176 -- Type : Procedure --
177 -- Access : Internal --
178 -- Description : Check for validity of data in the Termination Statutory Information --
179 -- Parameters : --
180 -- IN : p_effective_start_date IN DATE --
181 -- p_effective_end_date IN DATE --
182 -- p_organization_id IN NUMBER --
183 -- p_org_info_type_code IN VARCHAR2 --
184 -- p_org_information_id IN NUMBER --
185 --------------------------------------------------------------------------
186 PROCEDURE check_term_stat_info (p_org_information_id IN NUMBER
187 ,p_organization_id IN NUMBER
188 ,p_org_info_type_code IN VARCHAR2
189 ,p_effective_start_date IN DATE
190 ,p_effective_end_date IN DATE)
191
192 IS
193
194 CURSOR c_term_info_setup
195 IS
196 SELECT COUNT (org_information_id)
197 FROM hr_organization_information hoi
198 WHERE hoi.organization_id = p_organization_id
199 AND hoi.org_information_context = p_org_info_type_code
200 AND p_effective_start_date BETWEEN
201 fnd_date.canonical_to_date(NVL(hoi.org_information3,'1900/01/01 00:00:00'))
202 AND fnd_date.canonical_to_date(NVL(hoi.org_information4,'4712/12/31 00:00:00'))
203 AND (org_information_id <>p_org_information_id OR p_org_information_id IS NULL);
204
205
206 CURSOR c_fut_term_info_setup
207 IS
208 SELECT min(fnd_date.canonical_to_date(org_information3))
209 FROM hr_organization_information
210 WHERE org_information_context= p_org_info_type_code
211 AND NVL(org_information_id,-1) <> NVL(p_org_information_id,-1)
212 AND fnd_date.canonical_to_date(NVL(org_information3,'1900/01/01 00:00:00')) > p_effective_start_date
213 AND organization_id = p_organization_id
214 AND (org_information_id <> p_org_information_id
215 OR p_org_information_id IS NULL);
216
217 l_rec_count NUMBER;
218 l_procedure VARCHAR2(100);
219 l_fut_date DATE;
220
221 BEGIN
222
223 l_procedure := g_package || 'check_term_stat_info';
224 g_debug := hr_utility.debug_enabled;
225 g_debug := TRUE;
226
227
228 OPEN c_term_info_setup;
229 FETCH c_term_info_setup INTO l_rec_count;
230 CLOSE c_term_info_setup;
231
232 OPEN c_fut_term_info_setup;
233 FETCH c_fut_term_info_setup INTO l_fut_date;
234 CLOSE c_fut_term_info_setup;
235
236 hr_cn_api.set_location(g_debug,' l_rec_count : '|| l_rec_count, 20);
237
238 IF l_rec_count > 0 THEN
239 hr_utility.set_message(800,'PER_7901_SYS_DUPLICATE_RECORDS');
240 hr_utility.raise_error;
241 ELSIF l_fut_date IS NOT NULL THEN
242 IF p_effective_end_date >= l_fut_date OR p_effective_end_date IS NULL THEN
243 hr_utility.set_message(800,'PER_7901_SYS_DUPLICATE_RECORDS');
244 hr_utility.raise_error;
245 END IF;
246
247 END IF;
248
249 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,30);
250
251 END check_term_stat_info;
252
253 -------------------------------------------------------------------------
254 -- --
255 -- Name : check_single_record --
256 -- Type : Procedure --
257 -- Access : Public --
258 -- Description : This procedure checks if any duplicate record of a --
259 -- particular Tax area exists for the context --
260 -- PER_PHF_STAT_INFO_CN --
261 -- Parameters : --
262 -- IN : p_org_information_id IN NUMBER --
263 -- p_organization_id IN NUMBER --
264 -- p_org_info_type_code IN VARCHAR2 --
265 -- p_org_information1 IN VARCHAR2 --
266 -------------------------------------------------------------------------
267 PROCEDURE check_single_record (
268 p_org_information_id IN NUMBER
269 ,p_organization_id IN NUMBER
270 ,p_org_info_type_code IN VARCHAR2
271 ,p_org_information1 IN VARCHAR2)
272 IS
273
274 CURSOR c_phf_high_limit_exempt_setup
275 IS
276 SELECT COUNT (org_information_id) -- Y/N
277 FROM hr_organization_information hoi
278 WHERE hoi.organization_id = p_organization_id
279 AND hoi.org_information_context = 'PER_PHF_STAT_INFO_CN'
280 AND hoi.org_information1 = p_org_information1
281 AND (org_information_id <>p_org_information_id OR p_org_information_id IS NULL);
282
283 l_rec_count NUMBER;
284 l_procedure VARCHAR2(100);
285
286 BEGIN
287 l_procedure := g_package || 'check_single_record';
288 g_debug := hr_utility.debug_enabled;
289 g_debug := TRUE;
290
291 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
292
293 IF p_org_info_type_code = 'PER_PHF_STAT_INFO_CN' THEN
294 IF g_debug THEN
295 hr_utility.trace(' =======================================================');
296 hr_utility.trace(' . p_org_information_id : '||p_org_information_id);
297 hr_utility.trace(' . p_organization_id : '||p_organization_id );
298 hr_utility.trace(' . p_org_info_type_code : '||p_org_info_type_code);
299 hr_utility.trace(' . p_org_information1 : '||p_org_information1);
300 hr_utility.trace(' =======================================================');
301 END IF;
302
303 OPEN c_phf_high_limit_exempt_setup;
304 FETCH c_phf_high_limit_exempt_setup INTO l_rec_count;
305 CLOSE c_phf_high_limit_exempt_setup;
306 hr_cn_api.set_location(g_debug,' l_rec_count : '|| l_rec_count, 20);
307 END IF;
308
309 IF l_rec_count > 0 THEN
310 IF g_debug THEN
311 hr_utility.trace('=========================================================');
312 hr_utility.trace('message_name'||'PER_7901_SYS_DUPLICATE_RECORDS');
313 hr_utility.trace('=========================================================');
314 END IF;
315 hr_utility.set_message(800,'PER_7901_SYS_DUPLICATE_RECORDS');
316 hr_utility.raise_error;
317 END IF;
318
319 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,30);
320
321 END check_single_record;
322
323
324
325 -------------------------------------------------------------------------
326 -- --
327 -- Name : validate_date --
328 -- Type : Procedure --
329 -- Access : Public --
330 -- Description : This procedure checks if the effective end date is --
331 -- greater than or equal to effective start date . --
332 -- Parameters : --
333 -- IN : p_effective_start_date IN DATE --
334 -- p_effective_end_date IN DATE --
335 -------------------------------------------------------------------------
336 PROCEDURE validate_date(p_effective_start_date IN DATE,
337 p_effective_end_date IN DATE)
338 IS
339 l_procedure VARCHAR2(50);
340 E_INVALID_FORMAT_ERR EXCEPTION;
341 BEGIN
342
343 l_procedure := g_package || 'validate_date';
344 g_debug := hr_utility.debug_enabled;
345
346 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
347
348 IF p_effective_end_date IS NOT NULL THEN
352 RAISE E_INVALID_FORMAT_ERR;
349 hr_cn_api.set_location(g_debug,l_procedure,20);
350
351 IF p_effective_end_date < p_effective_start_date THEN
353 END IF;
354
355 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,30);
356
357 END IF;
358
359 EXCEPTION
360 WHEN E_INVALID_FORMAT_ERR THEN
361 hr_utility.set_message(800,'PER_CN_INCORRECT_DATES');
362 hr_cn_api.set_location(g_debug,l_procedure,40);
363 hr_utility.raise_error;
364
365 END validate_date;
366
367
368 --------------------------------------------------------------------------
369 -- Name : check_cn_org_info_internal --
370 -- Type : Procedure --
371 -- Access : Private --
372 -- Description : Internal Proc to be called from the Org Info Hook --
373 -- Parameters : --
374 -- IN : p_effective_date IN DATE --
375 -- p_organization_id IN NUMBER --
376 -- p_org_info_type_code IN VARCHAR2 --
377 -- p_org_information1..20 IN VARCHAR2 --
378 --------------------------------------------------------------------------
379 PROCEDURE check_cn_org_internal
380 (p_org_info_type_code IN VARCHAR2 --Organization Information type
381 ,p_org_information_id IN NUMBER --Organization Information ID
382 ,p_organization_id IN NUMBER --Organization ID
383 ,p_org_information1 IN VARCHAR2
384 ,p_org_information2 IN VARCHAR2
385 ,p_org_information3 IN VARCHAR2
386 ,p_org_information4 IN VARCHAR2
387 ,p_org_information5 IN VARCHAR2
388 ,p_org_information6 IN VARCHAR2
389 ,p_org_information7 IN VARCHAR2
390 ,p_org_information8 IN VARCHAR2
391 ,p_org_information9 IN VARCHAR2
392 ,p_org_information10 IN VARCHAR2
393 ,p_org_information11 IN VARCHAR2
394 ,p_org_information12 IN VARCHAR2
395 ,p_org_information13 IN VARCHAR2
396 ,p_org_information14 IN VARCHAR2
397 ,p_org_information15 IN VARCHAR2
398 ,p_org_information16 IN VARCHAR2
399 ,p_org_information17 IN VARCHAR2
400 ,p_org_information18 IN VARCHAR2
401 ,p_org_information19 IN VARCHAR2
402 ,p_org_information20 IN VARCHAR2
403 ,p_message_name OUT NOCOPY VARCHAR2
404 ,p_token_name OUT NOCOPY hr_cn_api.char_tab_type
405 ,p_token_value OUT NOCOPY hr_cn_api.char_tab_type
406 )
407 IS
408
409 l_procedure VARCHAR2(100);
410 l_start_date DATE;
411 l_end_date DATE;
412
413 BEGIN
414
415 l_procedure := g_package || 'check_cn_org_internal';
416 g_debug := hr_utility.debug_enabled;
417
418 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
419
420
421 -- commented the code for bug 6828199
422 /* IF p_org_info_type_code = 'PER_PHF_STAT_INFO_CN' THEN
423 check_single_record(p_org_information_id => p_org_information_id
424 ,p_organization_id => p_organization_id
425 ,p_org_info_type_code => p_org_info_type_code
426 ,p_org_information1 => p_org_information1
427 );
428 END IF;
429 */
430
431 /* Changes for Bug 6943573 start */
432 IF p_org_info_type_code = 'PER_TERM_STAT_INFO_CN' THEN
433
434 l_start_date := fnd_date.canonical_to_date(NVL(p_org_information3,'1900/01/01 00:00:00'));
435 l_end_date := fnd_date.canonical_to_date(NVL(p_org_information4,'4712/12/31 00:00:00'));
436 validate_date(l_start_date,l_end_date);
437
438 --call a new procedure to check if any records are overlapping or if any duplicate records are
439 --existing
440
441 check_term_stat_info(p_org_information_id => p_org_information_id
442 ,p_organization_id => p_organization_id
443 ,p_org_info_type_code => 'PER_TERM_STAT_INFO_CN'
444 ,p_effective_start_date => l_start_date
445 ,p_effective_end_date => l_end_date
446 );
447
448 ELSIF p_org_info_type_code = 'PER_TERM_GRE_INFO_CN' THEN
449
450 l_start_date := fnd_date.canonical_to_date(NVL(p_org_information3,'1900/01/01 00:00:00'));
451 l_end_date := fnd_date.canonical_to_date(NVL(p_org_information4,'4712/12/31 00:00:00'));
452 validate_date(l_start_date,l_end_date);
453
454 --call a new procedure to check if any records are overlapping or if any duplicate records are
455 --existing
456
457
458 check_term_stat_info(p_org_information_id => p_org_information_id
459 ,p_organization_id => p_organization_id
460 ,p_org_info_type_code => 'PER_TERM_GRE_INFO_CN'
461 ,p_effective_start_date => l_start_date
462 ,p_effective_end_date => l_end_date
463 );
464
465 /* Changes for bug 6943573 end */
466 ELSIF p_org_info_type_code = 'PER_CONT_AREA_CONT_BASE_CN' THEN
470 END IF;
467
468 IF g_debug THEN
469 hr_utility.trace('Validating Effective Start Date and Effective End Date');
471
472 l_start_date := fnd_date.canonical_to_date(p_org_information15);
473 l_end_date := fnd_date.canonical_to_date(NVL(p_org_information16,'4712/12/31 00:00:00'));
474 validate_date(l_start_date
475 ,l_end_date);
476
477 IF g_debug THEN
478 hr_utility.trace('Checking for Duplicate Record');
479 END IF;
480
481 check_record_exists(p_org_information_id => p_org_information_id
482 ,p_organization_id => p_organization_id
483 ,p_org_info_type_code => p_org_info_type_code
484 ,p_org_information1 => p_org_information1
485 ,p_org_information2 => p_org_information2
486 ,p_org_information3 => p_org_information3
487 ,p_org_information4 => NULL
488 ,p_effective_start_date => l_start_date
489 ,p_effective_end_date => l_end_date
490 );
491
492 IF g_debug THEN
493 hr_utility.trace('Calling pay_cn_deductions.check_cont_base_setup');
494 END IF;
495
496 pay_cn_deductions.check_cont_base_setup
497 (p_organization_id => p_organization_id
498 ,p_contribution_area => p_org_information1
499 ,p_phf_si_type => p_org_information2
500 ,p_hukou_type => p_org_information3
501 ,p_ee_cont_base_method => p_org_information4
502 ,p_er_cont_base_method => p_org_information5
503 ,p_low_limit_method => p_org_information6
504 ,p_low_limit_amount => fnd_number.canonical_to_number(p_org_information7)
505 ,p_high_limit_method => p_org_information8
506 ,p_high_limit_amount => fnd_number.canonical_to_number(p_org_information9)
507 ,p_switch_periodicity => p_org_information10
508 ,p_switch_month => p_org_information11
509 ,p_rounding_method => p_org_information12
510 ,p_lowest_avg_salary => fnd_number.canonical_to_number(p_org_information13)
511 ,p_average_salary => fnd_number.canonical_to_number(p_org_information14)
512 ,p_ee_fixed_amount => fnd_number.canonical_to_number(p_org_information17)
513 ,p_er_fixed_amount => fnd_number.canonical_to_number(p_org_information18)
514 ,p_effective_start_date => l_start_date
515 ,p_effective_end_date => l_end_date
516 ,p_message_name => g_message_name
517 ,p_token_name => g_token_name
518 ,p_token_value => g_token_value
519 );
520
521 ELSIF p_org_info_type_code = 'PER_CONT_AREA_PHF_SI_RATES_CN' THEN
522
523 IF g_debug THEN
524 hr_utility.trace('Validating Effective Start Date and Effective End Date');
525 END IF;
526
527 l_start_date := fnd_date.canonical_to_date(p_org_information10);
528 l_end_date := fnd_date.canonical_to_date(p_org_information11);
529 validate_date(l_start_date
530 ,l_end_date);
531
532 IF g_debug THEN
533 hr_utility.trace('Checking for Duplicate Record');
534 END IF;
535
536 check_record_exists(p_org_information_id => p_org_information_id
537 ,p_organization_id => p_organization_id
538 ,p_org_info_type_code => p_org_info_type_code
539 ,p_org_information1 => p_org_information1
540 ,p_org_information2 => p_org_information2
541 ,p_org_information3 => p_org_information3
542 ,p_org_information4 => p_org_information9
543 ,p_effective_start_date => l_start_date
544 ,p_effective_end_date => l_end_date
545 );
546
547 IF g_debug THEN
548 hr_utility.trace('Calling pay_cn_deductions.check_phf_si_rates_setup');
549 END IF;
550
551 pay_cn_deductions.check_phf_si_rates_setup
552 (p_organization_id => p_organization_id
553 ,p_contribution_area => p_org_information1
554 ,p_organization => p_org_information2
555 ,p_phf_si_type => p_org_information3
556 ,p_hukou_type => p_org_information9
557 ,p_effective_start_date => l_start_date
558 ,p_effective_end_date => l_end_date
559 ,p_message_name => g_message_name
560 ,p_token_name => g_token_name
561 ,p_token_value => g_token_value
562 );
563
564 END IF;
565 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,10);
566
567 END check_cn_org_internal;
568
569 --------------------------------------------------------------------------
570 -- Name : check_cn_org_info_type_create --
571 -- Type : Procedure --
572 -- Access : Public --
573 -- Description : Main Procedure to be called from the Org Info Hook --
574 -- Parameters : --
575 -- IN : p_effective_date IN DATE --
576 -- p_organization_id IN NUMBER --
577 -- p_org_info_type_code IN VARCHAR2 --
578 -- p_org_information1..20 IN VARCHAR2 --
579 --------------------------------------------------------------------------
580 PROCEDURE check_cn_org_info_type_create
581 (p_org_info_type_code IN VARCHAR2 --Organization Information type
582 ,p_organization_id IN NUMBER --Organization ID
583 ,p_org_information1 IN VARCHAR2
584 ,p_org_information2 IN VARCHAR2
585 ,p_org_information3 IN VARCHAR2
586 ,p_org_information4 IN VARCHAR2
587 ,p_org_information5 IN VARCHAR2
588 ,p_org_information6 IN VARCHAR2
589 ,p_org_information7 IN VARCHAR2
590 ,p_org_information8 IN VARCHAR2
591 ,p_org_information9 IN VARCHAR2
592 ,p_org_information10 IN VARCHAR2
593 ,p_org_information11 IN VARCHAR2
594 ,p_org_information12 IN VARCHAR2
595 ,p_org_information13 IN VARCHAR2
596 ,p_org_information14 IN VARCHAR2
597 ,p_org_information15 IN VARCHAR2
598 ,p_org_information16 IN VARCHAR2
599 ,p_org_information17 IN VARCHAR2
600 ,p_org_information18 IN VARCHAR2
601 ,p_org_information19 IN VARCHAR2
602 ,p_org_information20 IN VARCHAR2
603 )
604 IS
605
606 l_procedure VARCHAR2(100);
607
608 BEGIN
609 l_procedure := g_package || 'check_cn_org_info_type_create';
610 g_debug := hr_utility.debug_enabled;
611
612 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
613
614 -- Call check_cn_org_internal
615 check_cn_org_internal
616 (p_org_info_type_code => p_org_info_type_code
617 ,p_org_information_id => NULL
618 ,p_organization_id => p_organization_id
619 ,p_org_information1 => p_org_information1
620 ,p_org_information2 => p_org_information2
621 ,p_org_information3 => p_org_information3
622 ,p_org_information4 => p_org_information4
623 ,p_org_information5 => p_org_information5
624 ,p_org_information6 => p_org_information6
625 ,p_org_information7 => p_org_information7
626 ,p_org_information8 => p_org_information8
627 ,p_org_information9 => p_org_information9
628 ,p_org_information10 => p_org_information10
629 ,p_org_information11 => p_org_information11
630 ,p_org_information12 => p_org_information12
631 ,p_org_information13 => p_org_information13
632 ,p_org_information14 => p_org_information14
633 ,p_org_information15 => p_org_information15
634 ,p_org_information16 => p_org_information16
635 ,p_org_information17 => p_org_information17
636 ,p_org_information18 => p_org_information18
637 ,p_org_information19 => p_org_information19
638 ,p_org_information20 => p_org_information20
639 ,p_message_name => g_message_name
640 ,p_token_name => g_token_name
641 ,p_token_value => g_token_value
642 );
643
644 hr_cn_api.set_location(g_debug,l_procedure,20);
645
646 IF g_debug THEN
647 hr_utility.trace('Message => ' || g_message_name);
648 END IF;
649
650 hr_cn_api.raise_message(800
651 ,g_message_name
652 ,g_token_name
653 ,g_token_value);
654
655 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,10);
656
657 END CHECK_CN_ORG_INFO_TYPE_CREATE;
658
659 --------------------------------------------------------------------------
660 -- Name : check_cn_org_info_type_update --
661 -- Type : Procedure --
662 -- Access : Public --
663 -- Description : Main Procedure to be called from the Org Info Hook --
664 -- Parameters : --
665 -- IN : p_effective_date IN DATE --
666 -- p_organization_id IN NUMBER --
667 -- p_org_info_type_code IN VARCHAR2 --
668 -- p_org_information1..20 IN VARCHAR2 --
669 --------------------------------------------------------------------------
670 PROCEDURE CHECK_CN_ORG_INFO_TYPE_UPDATE
671 (p_org_info_type_code IN VARCHAR2 --Organization Information type
672 ,p_org_information_id IN NUMBER --Organization Information ID
673 ,p_org_information1 IN VARCHAR2
674 ,p_org_information2 IN VARCHAR2
675 ,p_org_information3 IN VARCHAR2
676 ,p_org_information4 IN VARCHAR2
677 ,p_org_information5 IN VARCHAR2
678 ,p_org_information6 IN VARCHAR2
679 ,p_org_information7 IN VARCHAR2
680 ,p_org_information8 IN VARCHAR2
681 ,p_org_information9 IN VARCHAR2
682 ,p_org_information10 IN VARCHAR2
683 ,p_org_information11 IN VARCHAR2
684 ,p_org_information12 IN VARCHAR2
685 ,p_org_information13 IN VARCHAR2
686 ,p_org_information14 IN VARCHAR2
687 ,p_org_information15 IN VARCHAR2
688 ,p_org_information16 IN VARCHAR2
689 ,p_org_information17 IN VARCHAR2
690 ,p_org_information18 IN VARCHAR2
691 ,p_org_information19 IN VARCHAR2
692 ,p_org_information20 IN VARCHAR2
693 )
694 IS
695 l_organization_id NUMBER;
696 l_procedure VARCHAR2(100);
697 l_org_information1 VARCHAR2(150);
698 l_org_information2 VARCHAR2(150);
699 l_org_information3 VARCHAR2(150);
700 l_org_information4 VARCHAR2(150);
701 l_org_information5 VARCHAR2(150);
702 l_org_information6 VARCHAR2(150);
703 l_org_information7 VARCHAR2(150);
704 l_org_information8 VARCHAR2(150);
705 l_org_information9 VARCHAR2(150);
706 l_org_information10 VARCHAR2(150);
707 l_org_information11 VARCHAR2(150);
708 l_org_information12 VARCHAR2(150);
709 l_org_information13 VARCHAR2(150);
710 l_org_information14 VARCHAR2(150);
711 l_org_information15 VARCHAR2(150);
712 l_org_information16 VARCHAR2(150);
713 l_org_information17 VARCHAR2(150);
714 l_org_information18 VARCHAR2(150);
715 l_org_information19 VARCHAR2(150);
716 l_org_information20 VARCHAR2(150);
717
718 CURSOR csr_org_info
719 IS
720 SELECT organization_id
721 ,org_information1
722 ,org_information2
723 ,org_information3
724 ,org_information4
725 ,org_information5
726 ,org_information6
727 ,org_information7
728 ,org_information8
729 ,org_information9
730 ,org_information10
731 ,org_information11
732 ,org_information12
733 ,org_information13
734 ,org_information14
735 ,org_information15
736 ,org_information16
737 ,org_information17
738 ,org_information18
739 ,org_information19
740 ,org_information20
741 FROM hr_organization_information
742 WHERE org_information_id = p_org_information_id;
743
744 BEGIN
745
746 l_procedure := g_package || 'check_cn_org_info_type_update';
747 g_debug := hr_utility.debug_enabled;
748
749 hr_cn_api.set_location(g_debug,'Entering ' || l_procedure,10);
750
751 OPEN csr_org_info;
752 FETCH csr_org_info
753 INTO l_organization_id
754 ,l_org_information1
755 ,l_org_information2
756 ,l_org_information3
757 ,l_org_information4
758 ,l_org_information5
759 ,l_org_information6
760 ,l_org_information7
761 ,l_org_information8
762 ,l_org_information9
763 ,l_org_information10
764 ,l_org_information11
765 ,l_org_information12
766 ,l_org_information13
767 ,l_org_information14
768 ,l_org_information15
769 ,l_org_information16
770 ,l_org_information17
771 ,l_org_information18
772 ,l_org_information19
773 ,l_org_information20 ;
774 CLOSE csr_org_info;
775
776 IF NVL(p_org_information1,'X') <> hr_api.g_varchar2 THEN
777 l_org_information1 := p_org_information1;
778 END IF;
779 IF NVL(p_org_information2,'X') <> hr_api.g_varchar2 THEN
780 l_org_information2 := p_org_information2;
781 END IF;
782 IF NVL(p_org_information3,'X') <> hr_api.g_varchar2 THEN
783 l_org_information3 := p_org_information3;
784 END IF;
785 IF NVL(p_org_information4,'X') <> hr_api.g_varchar2 THEN
786 l_org_information4 := p_org_information4;
787 END IF;
788 IF NVL(p_org_information5,'X') <> hr_api.g_varchar2 THEN
789 l_org_information5 := p_org_information5;
790 END IF;
791 IF NVL(p_org_information6,'X') <> hr_api.g_varchar2 THEN
792 l_org_information6 := p_org_information6;
793 END IF;
794 IF NVL(p_org_information7,'X') <> hr_api.g_varchar2 THEN
795 l_org_information7 := p_org_information7;
796 END IF;
797 IF NVL(p_org_information8,'X') <> hr_api.g_varchar2 THEN
798 l_org_information8 := p_org_information8;
799 END IF;
800 IF NVL(p_org_information9,'X') <> hr_api.g_varchar2 THEN
801 l_org_information9 := p_org_information9;
802 END IF;
803 IF NVL(p_org_information10,'X') <> hr_api.g_varchar2 THEN
804 l_org_information10 := p_org_information10;
805 END IF;
806 IF NVL(p_org_information11,'X') <> hr_api.g_varchar2 THEN
807 l_org_information11 := p_org_information11;
808 END IF;
809 IF NVL(p_org_information12,'X') <> hr_api.g_varchar2 THEN
810 l_org_information12 := p_org_information12;
811 END IF;
812 IF NVL(p_org_information13,'X') <> hr_api.g_varchar2 THEN
813 l_org_information13 := p_org_information13;
814 END IF;
815 IF NVL(p_org_information14,'X') <> hr_api.g_varchar2 THEN
816 l_org_information14 := p_org_information14;
817 END IF;
818 IF NVL(p_org_information15,'X') <> hr_api.g_varchar2 THEN
819 l_org_information15 := p_org_information15;
820 END IF;
821 IF NVL(p_org_information16,'X') <> hr_api.g_varchar2 THEN
822 l_org_information16 := p_org_information16;
823 END IF;
824 IF NVL(p_org_information17,'X') <> hr_api.g_varchar2 THEN
825 l_org_information17 := p_org_information17;
826 END IF;
827 IF NVL(p_org_information18,'X') <> hr_api.g_varchar2 THEN
828 l_org_information18 := p_org_information18;
829 END IF;
830 IF NVL(p_org_information19,'X') <> hr_api.g_varchar2 THEN
831 l_org_information19 := p_org_information19;
832 END IF;
833 IF NVL(p_org_information20,'X') <> hr_api.g_varchar2 THEN
834 l_org_information20 := p_org_information20;
835 END IF;
836
837 -- Call check_cn_org_internal
838 check_cn_org_internal
839 (p_org_info_type_code => p_org_info_type_code
840 ,p_org_information_id => p_org_information_id
841 ,p_organization_id => l_organization_id
842 ,p_org_information1 => l_org_information1
843 ,p_org_information2 => l_org_information2
844 ,p_org_information3 => l_org_information3
845 ,p_org_information4 => l_org_information4
846 ,p_org_information5 => l_org_information5
847 ,p_org_information6 => l_org_information6
848 ,p_org_information7 => l_org_information7
849 ,p_org_information8 => l_org_information8
850 ,p_org_information9 => l_org_information9
851 ,p_org_information10 => l_org_information10
852 ,p_org_information11 => l_org_information11
853 ,p_org_information12 => l_org_information12
854 ,p_org_information13 => l_org_information13
855 ,p_org_information14 => l_org_information14
856 ,p_org_information15 => l_org_information15
857 ,p_org_information16 => l_org_information16
858 ,p_org_information17 => l_org_information17
859 ,p_org_information18 => l_org_information18
860 ,p_org_information19 => l_org_information19
861 ,p_org_information20 => l_org_information20
862 ,p_message_name => g_message_name
863 ,p_token_name => g_token_name
864 ,p_token_value => g_token_value
865 );
866
867 hr_cn_api.set_location(g_debug,l_procedure,20);
868
869 hr_cn_api.raise_message(800
870 ,g_message_name
871 ,g_token_name
872 ,g_token_value);
873
874 hr_cn_api.set_location(g_debug,'Leaving ' || l_procedure,30);
875
876 END CHECK_CN_ORG_INFO_TYPE_UPDATE;
877
878 BEGIN
879
880 g_package := 'per_cn_org_info_leg_hook.';
881
882 END per_cn_org_info_leg_hook;