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