[Home] [Help]
PACKAGE BODY: APPS.PAY_CNU_BUS1
Source
1 Package Body pay_cnu_bus1 as
2 /* $Header: pycnurhi.pkb 120.0 2005/05/29 04:04:56 appldev noship $ */
6 --
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
7 g_package varchar2(33) := ' pay_cnu_bus1.'; -- Global package name
8 --
9 -- ----------------------------------------------------------------------------
10 -- |---------------------------< chk_element_name >----------------------------|
11 -- ----------------------------------------------------------------------------
12 Procedure chk_element_name (
13 p_element_name in pay_fr_contribution_usages.element_name%TYPE
14 ) Is
15 --
16 l_proc varchar2(72) := g_package|| ' chk_element_name';
17 --
18 Begin
19 --
20 hr_utility.set_location(' Entering '||l_proc, 10);
21 --
22 hr_api.mandatory_arg_error
23 (p_api_name => l_proc
24 ,p_argument => 'p_element_name'
25 ,p_argument_value => p_element_name
26 );
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 end chk_element_name;
30 -- ----------------------------------------------------------------------------
31 -- |---------------------------< chk_contribution_usage_type >----------------|
32 -- ----------------------------------------------------------------------------
33 Procedure chk_contribution_usage_type (
34 p_effective_date in date
35 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
36 ) Is
37 --
38 l_proc varchar2(72) := g_package|| ' chk_contribution_usage_type';
39 --
40 Begin
41 --
42 hr_utility.set_location(' Entering '||l_proc, 10);
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'p_effective_date'
47 ,p_argument_value => p_effective_date
48 );
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52 ,p_argument => 'p_contribution_usage_type'
53 ,p_argument_value => p_contribution_usage_type
54 );
55 --
56 If hr_api.not_exists_in_hr_lookups (
57 p_effective_date => p_effective_date
58 ,p_lookup_type => 'FR_CONTRIBUTION_USAGE_TYPE'
59 ,p_lookup_code => p_contribution_usage_type)
60 then
61 fnd_message.set_name('PAY', 'PAY_74897_CNU_BAD_USAGE_TYPE');
62 fnd_message.raise_error;
63 end if;
64 --
65 hr_utility.set_location(' Leaving:'||l_proc, 20);
66 end chk_contribution_usage_type;
67 -- ----------------------------------------------------------------------------
68 -- |---------------------------< chk_rate_type >-------------------------------|
69 -- ----------------------------------------------------------------------------
70 Procedure chk_rate_type (
71 p_effective_date in date
72 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
73 ) Is
74 --
75 l_proc varchar2(72) := g_package|| ' chk_rate_type';
76 --
77 Begin
78 --
79 hr_utility.set_location(' Entering '||l_proc, 10);
80 --
81 hr_api.mandatory_arg_error
82 (p_api_name => l_proc
83 ,p_argument => 'p_effective_date'
84 ,p_argument_value => p_effective_date
85 );
86 --
87 If p_rate_type is not null
88 then
89 If hr_api.not_exists_in_hr_lookups (
90 p_effective_date => p_effective_date
91 ,p_lookup_type => 'FR_CONTRIBUTION_RATE_TYPE'
92 ,p_lookup_code => p_rate_type)
93 then
94 fnd_message.set_name('PAY', 'PAY_74898_CNU_BAD_RATE_TYPE');
95 fnd_message.raise_error;
96 end if;
97 end if;
98 --
99 hr_utility.set_location(' Leaving:'||l_proc, 20);
100 end chk_rate_type;
101 -- ----------------------------------------------------------------------------
102 -- |---------------------------< chk_process_type >-----------------------------|
103 -- ----------------------------------------------------------------------------
104 Procedure chk_process_type (
105 p_effective_date in date
106 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
107 ) Is
108 --
109 l_proc varchar2(72) := g_package|| ' chk_process_type';
110 --
111 Begin
112 --
113 hr_utility.set_location(' Entering '||l_proc, 10);
114 --
115 hr_api.mandatory_arg_error
116 (p_api_name => l_proc
117 ,p_argument => 'p_effective_date'
118 ,p_argument_value => p_effective_date
119 );
120 --
121 hr_api.mandatory_arg_error
122 (p_api_name => l_proc
123 ,p_argument => 'p_process_type'
124 ,p_argument_value => p_process_type
125 );
126 --
127 If hr_api.not_exists_in_hr_lookups (
128 p_effective_date => p_effective_date
129 ,p_lookup_type => 'FR_PROCESS_TYPE'
130 ,p_lookup_code => p_process_type)
131 then
132 fnd_message.set_name('PAY', 'PAY_74899_CNU_BAD_PROCESS');
133 fnd_message.raise_error;
134 end if;
135 --
136 hr_utility.set_location(' Leaving:'||l_proc, 20);
137 end chk_process_type;
138 -- ----------------------------------------------------------------------------
139 -- |---------------------------< chk_lu_group_code >--------------------------|
140 -- ----------------------------------------------------------------------------
141 Procedure chk_lu_group_code (
142 p_effective_date in date
143 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
144 ) Is
145 --
146 l_proc varchar2(72) := g_package|| ' chk_lu_group_code';
147 --
148 -- The group code must be in either FR_ELEMENT_GROUP or USER_ELEMENT_GROUP
149 --
150 Begin
151 --
152 hr_utility.set_location(' Entering '||l_proc, 10);
153 --
154 hr_api.mandatory_arg_error
155 (p_api_name => l_proc
156 ,p_argument => 'p_effective_date'
157 ,p_argument_value => p_effective_date
158 );
159 --
160 hr_api.mandatory_arg_error
161 (p_api_name => l_proc
162 ,p_argument => 'p_group_code'
163 ,p_argument_value => p_group_code
164 );
165 --
166 If hr_api.not_exists_in_hr_lookups (
167 p_effective_date => p_effective_date
168 ,p_lookup_type => 'FR_ELEMENT_GROUP'
169 ,p_lookup_code => p_group_code )
170 then
171 If hr_api.not_exists_in_hr_lookups (
172 p_effective_date => p_effective_date
173 ,p_lookup_type => 'FR_USER_ELEMENT_GROUP'
174 ,p_lookup_code => p_group_code )
175 then
176 fnd_message.set_name('PAY', 'PAY_74900_CNU_BAD_GROUP_CODE');
177 fnd_message.raise_error;
178 end if;
179 end if;
180 --
181 hr_utility.set_location(' Leaving:'||l_proc, 20);
182 end chk_lu_group_code;
183 -- ----------------------------------------------------------------------------
184 -- |---------------------------< chk_business_group_id >----------------------|
185 -- ----------------------------------------------------------------------------
186 Procedure chk_business_group_id (
187 p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
188 ) Is
189 --
190 l_proc varchar2(72) := g_package|| ' chk_business_group_id';
191 l_leg_code varchar2(30);
192 --
193 -- The BG must have a French Legislation code
194 --
195 -- This can be called from insert, as BG is a non-updateable field
196 --
197 cursor csr_leg_code is
198 select pbg.legislation_code
199 from per_business_groups pbg
200 where pbg.business_group_id = p_business_group_id;
201 --
202 Begin
203 --
204 hr_utility.set_location(' Entering '||l_proc, 10);
205 --
206 -- Only test if not null
207 --
208 If p_business_group_id is not null
209 then
210 open csr_leg_code;
211 fetch csr_leg_code into l_leg_code;
212 close csr_leg_code;
216 fnd_message.raise_error;
213 If l_leg_code <> 'FR'
214 then
215 fnd_message.set_name('PAY', 'PAY_74901_CNU_BAD_LEGISLATION');
217 end if;
218 hr_utility.set_location(' Step:'|| l_proc, 20);
219 end if;
220 --
221 hr_utility.set_location(' Leaving:'||l_proc, 30);
222 end chk_business_group_id;
223 -- ----------------------------------------------------------------------------
224 -- |---------------------------< chk_rate_category_type >---------------------|
225 -- ----------------------------------------------------------------------------
226 Procedure chk_rate_category_type (
227 p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
228 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
229 ) Is
230 --
231 l_proc varchar2(72) := g_package|| ' chk_rate_category_type';
232 --
233 Begin
234 --
235 hr_utility.set_location(' Entering '||l_proc, 10);
236 hr_api.mandatory_arg_error
237 (p_api_name => l_proc
238 ,p_argument => 'p_rate_category'
239 ,p_argument_value => p_rate_category
240 );
241 --
242 hr_utility.set_location(' Step:'|| l_proc, 20);
243 if ( (p_rate_category <> 'S')
244 and(p_rate_category <> 'W')
245 and(p_rate_category <> 'T')
246 and(p_rate_category <> 'R')
247 and(p_rate_category <> 'D')
248 and(p_rate_category <> 'C')
249
250 )
251 then
252 fnd_message.set_name('PAY', 'PAY_74902_CNU_BAD_RATE_CAT');
253 fnd_message.raise_error;
254 end if;
255 --
256 hr_utility.set_location(' Step:'|| l_proc, 30);
257 if( ( ( p_rate_category = 'W'
258 OR p_rate_category = 'T'
259 OR p_rate_category = 'R'
260 ) and p_rate_type is not null)
261 OR ( ( p_rate_category = 'S'
262 OR p_rate_category = 'D'
263 OR p_rate_category = 'C') and p_rate_type is null)
264 )
265 then
266 fnd_message.set_name('PAY', 'PAY_74903_CNU_BAD_RATE_STD');
267 fnd_message.raise_error;
268 end if;
269 hr_utility.set_location(' Leaving:'||l_proc, 40);
270 end chk_rate_category_type;
271 --
272
273 -- ----------------------------------------------------------------------------
274 -- |---------------------------< is_numeric >---------------------------------|
275 -- ----------------------------------------------------------------------------
276 function is_numeric (p_one_char in VARCHAR2) RETURN varchar2
277 is
278 l_return varchar2(1);
279 BEGIN
280 l_return := '1';
281 if ( (p_one_char <> '1')
282 and(p_one_char <> '2')
283 and(p_one_char <> '3')
284 and(p_one_char <> '4')
285 and(p_one_char <> '5')
286 and(p_one_char <> '6')
287 and(p_one_char <> '7')
288 and(p_one_char <> '8')
289 and(p_one_char <> '9')
290 and(p_one_char <> '0')
291 )
292 then
293 l_return := '0';
294 end if;
295 RETURN (l_return);
296 end is_numeric;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |---------------------------< chk_validate_code >--------------------------|
300 -- ----------------------------------------------------------------------------
301 Procedure chk_validate_code (
302 p_code in pay_fr_contribution_usages.contribution_code%TYPE
303 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
304 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
305 ) Is
306 --
307 l_proc varchar2(72) := g_package|| ' chk_validate_code';
308 l_exists varchar2(1);
309 --
310 Begin
311 --
312 -- URSSAF
313 --
314 hr_utility.set_location(' Entering '||l_proc, 10);
315 if p_contribution_type = 'URSSAF'
316 and( (substr(p_code, 1, 1) <> '1')
317 OR(substr(p_code, 2, 2) <> 'XX')
318 OR( (substr(p_code, 7, 1) <> 'A')
319 and (substr(p_code, 7, 1) <> 'D')
320 and (substr(p_code, 7, 1) <> 'P')
321 and (substr(p_code, 7, 1) <> 'T')
322 and (substr(p_code, 7, 1) <> 'C')
323 and (substr(p_code, 7, 1) <> 'N')
324 )
325 OR(is_numeric(substr(p_code, 4, 1)) <> '1')
326 OR(is_numeric(substr(p_code, 5, 1)) <> '1')
327 OR(is_numeric(substr(p_code, 6, 1)) <> '1')
328 OR(length(p_code) <> 7 and length(p_code) <> 8)
329 )
330 then
331 hr_utility.set_location(' Step:'|| l_proc, 20);
332 fnd_message.set_name('PAY', 'PAY_74904_CNU_BAD_URSSAF');
333 fnd_message.raise_error;
334 end if;
335 hr_utility.set_location(' Step:'|| l_proc, 30);
336 --
337 -- ASSEDIC
338 --
339 if p_contribution_type = 'ASSEDIC'
340 and( (substr(p_code, 1, 1) <> '2')
341 OR(substr(p_code, 2, 1) <> 'X')
342 OR( (substr(p_code, 3, 1) <> '1')
343 and (substr(p_code, 3, 1) <> '2')
344 and (substr(p_code, 3, 1) <> '3')
345 )
346 OR(substr(p_code, 4, 1) <> '0')
347 OR(is_numeric(substr(p_code, 5, 1)) <> '1')
348 OR(is_numeric(substr(p_code, 6, 1)) <> '1')
349 OR(is_numeric(substr(p_code, 7, 1)) <> '1')
350 OR(length(p_code) <> 7)
351 )
352 then
353 fnd_message.set_name('PAY', 'PAY_74905_CNU_BAD_ASSEDIC');
354 fnd_message.raise_error;
355 end if;
356 hr_utility.set_location(' Step:'|| l_proc, 50);
357 --
358 -- AGIRC
359 --
360 if p_contribution_type = 'AGIRC'
364 OR(is_numeric(substr(p_code, 7, 1)) <> '1')
361 and( (substr(p_code, 1, 1) <> '3')
362 OR(substr(p_code, 2, 4) <> 'XXXX')
363 OR(is_numeric(substr(p_code, 6, 1)) <> '1')
365 OR(length(p_code) <> 7)
366 )
367 then
368 fnd_message.set_name('PAY', 'PAY_74906_CNU_BAD_AGIRC');
369 fnd_message.raise_error;
370 end if;
371 hr_utility.set_location(' Step:'|| l_proc, 60);
372 --
373 --
374 -- ARRCO
375 --
376 if p_contribution_type = 'ARRCO'
377 and( (substr(p_code, 1, 1) <> '4')
378 OR(substr(p_code, 2, 4) <> 'XXXX' and substr(p_code, 2, 4) <> 'X260' and substr(p_code, 2, 4) <> 'X301' and substr(p_code, 2, 4) <> 'X201'and substr(p_code, 2, 4) <> 'X240'and substr(p_code, 2, 4) <> 'X203')
379 OR(is_numeric(substr(p_code, 6, 1)) <> '1' and is_numeric(substr(p_code, 6, 1)) <> '0' and is_numeric(substr(p_code, 6, 1)) <> '3')
380 OR(is_numeric(substr(p_code, 7, 1)) <> '1' and is_numeric(substr(p_code, 7, 1)) <> '2')
381 OR(length(p_code) <> 7)
382 )
383 then
384 fnd_message.set_name('PAY', 'PAY_74907_CNU_BAD_ARRCO');
385 fnd_message.raise_error;
386 end if;
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 80);
389 end chk_validate_code;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------< chk_contribution_codes >---------------------|
393 -- ----------------------------------------------------------------------------
394 Procedure chk_contribution_codes (
395 p_contribution_usage_id in pay_fr_contribution_usages.contribution_usage_id%TYPE
396 ,p_object_version_number in pay_fr_contribution_usages.object_version_number%TYPE
397 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
398 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
399 ,p_retro_contribution_code in pay_fr_contribution_usages.retro_contribution_code%TYPE
400 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
401 ) Is
402 --
403 l_proc varchar2(72) := g_package|| ' chk_contribution_codes';
404 l_exists varchar2(1);
405 l_api_updating boolean;
406 --
407 Begin
408 --
409 -- check mandatory parameters have been set
410 --
411 --
412 hr_utility.set_location(' Entering '||l_proc, 10);
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc
415 ,p_argument => 'p_contribution_type'
416 ,p_argument_value => p_contribution_type
417 );
418
419 -- main code can only be null if cont type is URSSAF
420 --
421 if p_contribution_type <> 'URSSAF' and p_contribution_code is null and p_rate_category <> 'C'
422 then
423 hr_utility.set_location(' Step:'|| l_proc, 20);
424 fnd_message.set_name('PAY', 'PAY_74908_CNU_MISSING_CODE');
425 fnd_message.raise_error;
426 end if;
427 --
428 l_api_updating := pay_cnu_shd.api_updating
429 (p_contribution_usage_id => p_contribution_usage_id
430 ,p_object_version_number => p_object_version_number
431 );
432 --
433 -- If this is an update and retro is changing (and not changing to null) check.
434 --
435 if (l_api_updating and
436 nvl(pay_cnu_shd.g_old_rec.retro_contribution_code, hr_api.g_varchar2) <>
437 nvl(p_retro_contribution_code, hr_api.g_varchar2) and p_retro_contribution_code is not null)
438 then
439 hr_utility.set_location(' Step:'|| l_proc, 30);
440 pay_cnu_bus1.chk_validate_code( p_code => p_retro_contribution_code
441 ,p_contribution_type => p_contribution_type
442 ,p_rate_category => p_rate_category);
443 end if;
444 --
445 -- If this is an insert and code is not null check.
446 --
447 if (NOT l_api_updating and p_contribution_code is not null)
448 then
449 hr_utility.set_location(' Step:'|| l_proc, 40);
450 pay_cnu_bus1.chk_validate_code( p_code => p_contribution_code
451 ,p_contribution_type => p_contribution_type
452 ,p_rate_category => p_rate_category);
453 end if;
454 hr_utility.set_location(' Step:'|| l_proc, 45);
455 --
456 -- Check retro code
457 --
458 if (NOT l_api_updating and p_retro_contribution_code is not null)
459 then
460 pay_cnu_bus1.chk_validate_code( p_code => p_retro_contribution_code
461 ,p_contribution_type => p_contribution_type
462 ,p_rate_category => p_rate_category);
463 end if;
464 hr_utility.set_location(' Leaving:'||l_proc, 50);
465 end chk_contribution_codes;
466 -- ----------------------------------------------------------------------------
467 -- |---------------------------< chk_contribution_type >----------------------|
468 -- ----------------------------------------------------------------------------
469 Procedure chk_contribution_type (
470 p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
471 ) Is
472 --
473 l_proc varchar2(72) := g_package|| ' chk_contribution_type';
474 --
475 Begin
476 --
477 -- check mandatory parameters have been set
478 --
479 --
480 hr_utility.set_location(' Entering '||l_proc, 10);
481 hr_api.mandatory_arg_error
482 (p_api_name => l_proc
483 ,p_argument => 'p_contribution_type'
484 ,p_argument_value => p_contribution_type
485 );
489 and p_contribution_type <> 'AGIRC'
486 --
487 if p_contribution_type <> 'URSSAF'
488 and p_contribution_type <> 'ASSEDIC'
490 and p_contribution_type <> 'ARRCO'
491 then
492 hr_utility.set_location(' Step:'|| l_proc, 20);
493 fnd_message.set_name('PAY', 'PAY_74909_CNU_BAD_CONT_TYPE');
494 fnd_message.raise_error;
495 end if;
496 hr_utility.set_location(' Leaving:'||l_proc, 90);
497 end chk_contribution_type;
498
499 -- ----------------------------------------------------------------------------
500 -- |---------------------------< chk_group_code >------------------------------|
501 -- ----------------------------------------------------------------------------
502 Procedure chk_group_code (
503 p_group_code in pay_fr_contribution_usages.group_code%TYPE
504 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
505 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
506 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
507 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
508 ) Is
509 --
510 l_proc varchar2(72) := g_package|| ' chk_group_code';
511 l_cu_id number;
512 --
513 -- There can only be one group_code for a combination of element_name, process_type
514 -- contribution_usage_type for :
515 -- if p_bg is null, where bg is null
516 -- if p_bg is not null, where bg = this bg, or bg is null
517 --
518 -- This can be called from insert (where ID and OVN are null)
519 -- it is not possible to update these key values, so this test
520 -- is not required during update_validate.
521 --
522 cursor csr_unique is
523 select cnu.contribution_usage_id
524 from pay_fr_contribution_usages cnu
525 where cnu.group_code <> p_group_code
526 and cnu.process_type = p_process_type
527 and cnu.element_name = p_element_name
528 and cnu.contribution_usage_type = p_contribution_usage_type
529 and ( (p_business_group_id is null)
530 or(p_business_group_id is not null
531 and ( (cnu.business_group_id = p_business_group_id )
532 or(cnu.business_group_id is null)
533 )
534 )
535 );
536 --
537 Begin
538 --
539 -- check mandatory parameters have been set
540 --
541 --
542 hr_utility.set_location(' Entering '||l_proc, 10);
543 hr_api.mandatory_arg_error
544 (p_api_name => l_proc
545 ,p_argument => 'p_group_code'
546 ,p_argument_value => p_group_code
547 );
548 --
549 hr_utility.set_location(' Step:'|| l_proc, 30);
550 hr_api.mandatory_arg_error
551 (p_api_name => l_proc
552 ,p_argument => 'p_process_type'
553 ,p_argument_value => p_process_type
554 );
555 --
556 hr_utility.set_location(' Step:'|| l_proc, 40);
557 hr_api.mandatory_arg_error
558 (p_api_name => l_proc
559 ,p_argument => 'p_element_name'
560 ,p_argument_value => p_element_name
561 );
562 --
563 hr_utility.set_location(' Step:'|| l_proc, 50);
564 hr_api.mandatory_arg_error
565 (p_api_name => l_proc
566 ,p_argument => 'p_contribution_usage_type'
567 ,p_argument_value => p_contribution_usage_type
568 );
569 --
570 -- This is an insert (not called for update or delete)
571 -- check the combination is unique
572 --
573 hr_utility.set_location(' Step:'|| l_proc, 60);
574 open csr_unique;
575 fetch csr_unique into l_cu_id;
576 if csr_unique%FOUND then
577 close csr_unique;
578 fnd_message.set_name('PAY', 'PAY_74910_CNU_CHANGING_GROUP');
579 fnd_message.raise_error;
580 else
581 close csr_unique;
582 hr_utility.set_location(' Step:'|| l_proc, 80);
583 end if;
584 --
585 hr_utility.set_location(' Leaving:'||l_proc, 90);
586 end chk_group_code;
587 -- ----------------------------------------------------------------------------
588 -- |---------------------------< chk_dates >----------------------------------|
589 -- ----------------------------------------------------------------------------
590 Procedure chk_dates (
591 p_contribution_usage_id in pay_fr_contribution_usages.contribution_usage_id%TYPE
592 ,p_object_version_number in pay_fr_contribution_usages.object_version_number%TYPE
593 ,p_date_from in pay_fr_contribution_usages.date_from%TYPE
594 ,p_date_to in pay_fr_contribution_usages.date_to%TYPE
595 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
596 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
597 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
598 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
599 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
600 ) Is
601 --
602 l_proc varchar2(72) := g_package|| ' chk_dates';
603 l_exists varchar2(1);
604 l_cu_id number;
605 l_api_updating boolean;
606 --
607 -- there cannot be a duplicate of date_from, date_to, group_code, process_type
608 -- element_name, contribution_usage_type :
609 -- if p_business_group_id is null, where BG is null
610 -- if BG is not null, where BG = P_BG, and where BG is null
611 -- covering any period in the date_from -> date_to date range.
612 -- If p_date_to is null, use eot.
613 --
614 -- This can be called from insert (where ID and OVN are null)
615 -- or
616 -- from update, as date_to may have changed.
620 select cnu.contribution_usage_id
617 -- Only test if new insert, or date_to is changing.
618 --
619 cursor csr_unique is
621 from pay_fr_contribution_usages cnu
622 where cnu.group_code = p_group_code
623 and cnu.process_type = p_process_type
624 and cnu.element_name = p_element_name
625 and cnu.contribution_usage_type = p_contribution_usage_type
626 and (nvl(p_contribution_usage_id, -1) <> cnu.contribution_usage_id )
627 and ( (p_business_group_id is null)
628 or(p_business_group_id is not null
629 and ( (cnu.business_group_id = p_business_group_id )
630 or(cnu.business_group_id is null)
631 )
632 )
633 )
634 and ( ((nvl(p_date_to, hr_api.g_eot) <= nvl(cnu.date_to, hr_api.g_eot)
635 and nvl(p_date_to, hr_api.g_eot) >= cnu.date_from))
636 OR
637 ( (p_date_from >= cnu.date_from)
638 and p_date_from <= nvl(cnu.date_to, hr_api.g_eot))
639 OR
640 ( (p_date_from <= cnu.date_from)
641 and nvl(p_date_to, hr_api.g_eot) >= nvl(cnu.date_to, hr_api.g_eot))
642 );
643 --
644 Begin
645 --
646 -- check mandatory parameters have been set
647 --
648 --
649 hr_utility.set_location(' Entering '||l_proc, 10);
650 hr_api.mandatory_arg_error
651 (p_api_name => l_proc
652 ,p_argument => 'p_date_from'
653 ,p_argument_value => p_date_from
654 );
655 --
656 hr_utility.set_location(' Step:'|| l_proc, 20);
657 hr_api.mandatory_arg_error
658 (p_api_name => l_proc
659 ,p_argument => 'p_group_code'
660 ,p_argument_value => p_group_code
661 );
662 --
663 hr_utility.set_location(' Step:'|| l_proc, 30);
664 hr_api.mandatory_arg_error
665 (p_api_name => l_proc
666 ,p_argument => 'p_process_type'
667 ,p_argument_value => p_process_type
668 );
669 --
670 hr_utility.set_location(' Step:'|| l_proc, 40);
671 hr_api.mandatory_arg_error
672 (p_api_name => l_proc
673 ,p_argument => 'p_element_name'
674 ,p_argument_value => p_element_name
675 );
676 --
677 hr_utility.set_location(' Step:'|| l_proc, 50);
678 hr_api.mandatory_arg_error
679 (p_api_name => l_proc
680 ,p_argument => 'p_contribution_usage_type'
681 ,p_argument_value => p_contribution_usage_type
682 );
683 --
684 -- if is changing or is an insert,
685 -- check the combination is unique
686 --
687 l_api_updating := pay_cnu_shd.api_updating
688 (p_contribution_usage_id => p_contribution_usage_id
689 ,p_object_version_number => p_object_version_number
690 );
691 --
692 -- Check that the date_from is before or on the date_to
693 --
694 if p_date_from > nvl(p_date_to, hr_api.g_eot) THEN
695 hr_utility.set_location(' Step:'|| l_proc, 55);
696 fnd_message.set_name('PAY', 'PAY_74911_CNU_DATE_FROM');
697 fnd_message.raise_error;
698 end if;
699 --
700 -- If the date_to is changing or if this is an insert
701 --
702 if (l_api_updating and
703 nvl(pay_cnu_shd.g_old_rec.date_to, hr_api.g_date) <>
704 nvl(p_date_to, hr_api.g_date) )
705 or (NOT l_api_updating)
706 then
707 hr_utility.set_location(' Step:'|| l_proc, 60);
708 open csr_unique;
709 fetch csr_unique into l_cu_id;
710 if csr_unique%FOUND then
711 close csr_unique;
712 hr_utility.set_location(' Step:'|| l_proc, 70);
713 fnd_message.set_name('PAY', 'PAY_74912_CNU_DUPLICATE_USAGE');
714 fnd_message.raise_error;
715 else
716 close csr_unique;
717 hr_utility.set_location(' Step:'|| l_proc, 80);
718 end if;
719 end if;
720 --
721 hr_utility.set_location(' Leaving:'||l_proc, 90);
722 end chk_dates;
723 --
724 -- ----------------------------------------------------------------------------
725 -- |---------------------------< LOAD_ROW >------------------------------------|
726 -- ----------------------------------------------------------------------------
727 Procedure load_row (
728 p_date_from in varchar2
729 ,p_date_to in varchar2
730 ,p_group_code in pay_fr_contribution_usages.group_code%TYPE
731 ,p_process_type in pay_fr_contribution_usages.process_type%TYPE
732 ,p_element_name in pay_fr_contribution_usages.element_name%TYPE
733 ,p_contribution_usage_type in pay_fr_contribution_usages.contribution_usage_type%TYPE
734 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
735 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
736 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
737 ,p_contribution_type in pay_fr_contribution_usages.contribution_type%TYPE
738 ,p_retro_contribution_code in pay_fr_contribution_usages.retro_contribution_code%TYPE
739 ,p_code_rate_id in pay_fr_contribution_usages.code_Rate_id%TYPE
740 ) is
741 --
742 l_existing_cu_id number;
743 l_existing_ovn_id number;
744 l_cu_id number;
745 l_ovn_id number;
746 l_code_Rate_id number := p_code_Rate_id;
747 l_new_date_from date := to_date(p_date_from,'DD/MM/YYYY');
748 l_new_date_to date := to_date(p_date_to, 'DD/MM/YYYY');
749 --
750 cursor csr_existing is
751 select cnu.contribution_usage_id, cnu.object_version_number
752 from pay_fr_contribution_usages cnu
753 where cnu.group_code = p_group_code
754 and cnu.process_type = p_process_type
755 and cnu.element_name = p_element_name
756 and cnu.date_from = l_new_date_from
757 and cnu.contribution_usage_type = p_contribution_usage_type
758 and cnu.business_group_id is null;
759 BEGIN
760 open csr_existing;
761 fetch csr_existing into l_existing_cu_id, l_existing_ovn_id;
762 if csr_existing%FOUND
763 then
764 close csr_existing;
765 pay_cnu_api.update_contribution_usage(
766 p_validate => FALSE
767 ,p_effective_date => l_new_date_from
768 ,p_date_to => l_new_date_to
769 ,p_retro_contribution_code => p_retro_contribution_code
770 ,p_object_version_number => l_existing_ovn_id
771 ,p_contribution_usage_id => l_existing_cu_id
772 ,p_contribution_code => p_contribution_code
773 ,p_contribution_type => p_contribution_type
774 ,p_code_rate_id => p_code_rate_id
775 );
776 else
777 close csr_existing;
778 -- This is not an update
779 -- call the create api, and allow it to adjust any
780 -- existing rows if necessary.
781 --
782 pay_cnu_api.create_contribution_usage(
783 p_validate => FALSE
784 ,p_effective_date => l_new_date_from
785 ,p_date_from => l_new_date_from
786 ,p_date_to => l_new_date_to
787 ,p_group_code => p_group_code
788 ,p_process_type => p_process_type
789 ,p_element_name => p_element_name
790 ,p_contribution_usage_type => p_contribution_usage_type
791 ,p_rate_type => p_rate_type
792 ,p_rate_category => p_rate_category
793 ,p_contribution_code => p_contribution_code
794 ,p_contribution_type => p_contribution_type
795 ,p_retro_contribution_code => p_retro_contribution_code
796 ,p_business_group_id => null
797 ,p_object_version_number => l_ovn_id
798 ,p_contribution_usage_id => l_cu_id
799 ,p_code_Rate_id => l_code_rate_id
800 );
801 end if;
802 --
803 -- do not pass back any out parameters from the API calls
804 --
805 end load_row;
806 -- ----------------------------------------------------------------------------
807 -- |---------------------------< chk_code_rate_id >----------------------------|
808 -- ----------------------------------------------------------------------------
809 Procedure chk_code_rate_id (
810 p_code_rate_id in out nocopy pay_fr_contribution_usages.code_rate_id%TYPE
811 ,p_contribution_code in pay_fr_contribution_usages.contribution_code%TYPE
812 ,p_business_group_id in pay_fr_contribution_usages.business_group_id%TYPE
813 ,p_rate_type in pay_fr_contribution_usages.rate_type%TYPE
814 ,p_rate_category in pay_fr_contribution_usages.rate_category%TYPE
815 ) Is
816 --
817 l_proc varchar2(72) := g_package|| ' chk_code_rate_id';
818 l_exists varchar2(1);
819 --
820 -- There can only be one group_code for a combination of element_name, process_type
821 -- contribution_usage_type for :
822 -- if p_bg is null, where bg is null
823 -- if p_bg is not null, where bg = this bg, or bg is null
824 --
825 -- This can be called from insert (where ID and OVN are null)
826 -- it is not possible to update these key values, so this test
827 -- is not required during update_validate.
828 --
829 cursor csr_chk_unique is
830 select null
831 from pay_fr_contribution_usages cnu
832 where cnu.contribution_code = p_contribution_code
833 and cnu.rate_type = p_rate_type
834 and cnu.code_rate_id <> p_code_rate_id
835 and nvl(cnu.business_group_id,0) = nvl(p_business_group_id,0)
836 UNION
837 select null
838 from pay_fr_contribution_usages cnu
839 where cnu.code_rate_id = p_code_rate_id
840 and nvl(cnu.business_group_id,0) = nvl(p_business_group_id,0)
841 and cnu.contribution_code = p_contribution_code
842 and cnu.rate_type <> p_rate_type;
843
844 cursor csr_get_code_rate is
845 select code_rate_id
846 from pay_fr_contribution_usages cnu
847 where cnu.contribution_code = p_contribution_code
848 and cnu.rate_type = p_rate_type
849 and cnu.business_group_id = p_business_group_id;
850
851 cursor csr_new_code_rate is
852 select nvl(max(code_rate_id),29) +1
853 from pay_fr_contribution_usages cnu
854 where cnu.contribution_code = p_contribution_code
855 and cnu.business_group_id = p_business_group_id;
856
857 --
858 Begin
859 --
860 -- check mandatory parameters have been set
861 --
862 --
863 hr_utility.set_location(' Entering '||l_proc, 10);
864 hr_api.mandatory_arg_error
865 (p_api_name => l_proc
866 ,p_argument => 'p_rate_category'
867 ,p_argument_value => p_rate_category
868 );
869 --
870 -- This is an insert (not called for update or delete)
871 --
872 -- check if code_rate_id is required
873 --
874 if p_rate_category in ('W', 'T') or p_contribution_code is null or p_rate_type is null then
875 if p_code_rate_id is not null then
876 fnd_message.set_name('PAY', 'PAY_75061_CNU_NOT_REQ_CODE_R');
877 fnd_message.raise_error;
878 end if;
879 else
880 if (p_business_group_id is null and p_code_rate_id is null)
881 or
882 (p_business_group_id is not null and p_code_rate_id is not null)
883 then
884 fnd_message.set_name('PAY', 'PAY_75062_CNU_BG_CODE_R');
885 fnd_message.raise_error;
886 end if;
887 --
888 -- seeded code rate ids must be >=0, <30
889 --
890 if p_business_group_id is null
891 and (p_code_rate_id < 0 OR p_code_rate_id >29)
892 then
893 fnd_message.set_name('PAY', 'PAY_75063_CNU_RGE_CODE_R');
894 fnd_message.raise_error;
895 end if;
896 --
897 -- get a code rate id for the business group id
898 --
899 if p_business_Group_id is not null
900 then
901 open csr_get_code_rate;
902 fetch csr_get_code_rate into p_code_rate_id;
903 close csr_get_code_rate;
904 if p_code_rate_id is null
905 then
906 open csr_new_code_rate;
907 fetch csr_new_code_rate into p_code_Rate_id;
908 close csr_new_code_rate;
909 end if;
910 end if;
911 --
912 -- For both user and seeded rows check no duplicates exist
913 --
914 open csr_chk_unique;
915 fetch csr_chk_unique into l_exists;
916 if csr_chk_unique%FOUND then
917 close csr_chk_unique;
918 fnd_message.set_name('PAY', 'PAY_75064_CNU_UNQ_CODE_R');
919 fnd_message.raise_error;
920 else
921 close csr_chk_unique;
922 end if;
923 End if;
924 hr_utility.set_location(' Leaving:'||l_proc, 90);
925 end chk_code_rate_id;
926 -------------------------------------------------------------------------------
927 end pay_cnu_bus1;