[Home] [Help]
PACKAGE BODY: APPS.HR_NL_EXTRA_ASG_RULES
Source
1 PACKAGE BODY hr_nl_extra_asg_rules AS
2 /* $Header: penlexar.pkb 120.0.12000000.2 2007/02/28 10:55:01 spendhar ship $ */
3
4 --Sets the Global - glo_payroll_id
5 --For Tax Code Validations to be performed when API is being called implictly from
6 --People Management Templates - Enter Employee
7 --Accepts the Payroll ID entered in the Payroll Field on the
8 --Enter Employee Template and sets the global for usage in
9 --extra_assignment_checks1 Procedure.
10
11 PROCEDURE set_payroll_id(p_payroll_id IN NUMBER) IS
12 BEGIN
13 glo_payroll_id := p_payroll_id;
14 END set_payroll_id;
15 --
16 --
17 --------------------------------------------------------------------------------
18 -- Tax Code Validations
19 --------------------------------------------------------------------------------
20 -- Tax Code - SEGMENT11
21 -- Tax Reductions Apply - SEGMENT4
22 -- Labour Tax Reduction - SEGMENT7
23 -- Additional Sr Tax Reduction - SEGMENT9
24 --
25 PROCEDURE validate_tax_code_combinations
26 (p_person_id IN NUMBER
27 ,p_assignment_id IN NUMBER
28 ,p_payroll_id IN NUMBER
29 ,p_effective_date IN DATE
30 ,p_tax_code IN VARCHAR2
31 ,p_tax_red_apply IN VARCHAR2
32 ,p_labour_tax_apply IN VARCHAR2
33 ,p_add_sr_tax_apply IN VARCHAR2) IS
34 --
35 l_period_type pay_payrolls_f.period_type%TYPE;
36 l_period_code number;
37 p_period_type VARCHAR2(80);
38 l_1_digit VARCHAR2(1);
39 l_2_digit VARCHAR2(1);
40 l_3_digit VARCHAR2(1);
41 l_valid BOOLEAN;
42 l_mar_status per_all_people_f.marital_status%TYPE;
43 l_lookup_desc hr_lookups.meaning%TYPE;
44 l_age NUMBER;
45 l_tax_code VARCHAR2(60);
46 l_tax_red_apply VARCHAR2(60);
47 l_labour_tax_apply VARCHAR2(60);
48 l_add_sr_tax_apply VARCHAR2(60);
49
50 --
51 CURSOR get_payroll_period_type(p_payroll_id NUMBER
52 ,p_effective_date DATE) IS
53 SELECT pp.period_type
54 FROM pay_payrolls_f pp
55 WHERE pp.payroll_id=p_payroll_id
56 AND p_effective_date BETWEEN pp.effective_start_date
57 AND pp.effective_end_date;
58 --
59 CURSOR csr_get_marital_status(p_person_id NUMBER
60 ,p_effective_date DATE) IS
61 SELECT marital_status
62 FROM per_all_people_f
63 WHERE person_id = p_person_id
64 AND p_effective_date BETWEEN effective_start_date
65 AND effective_end_date;
66 --
67 CURSOR csr_get_lookup_desc(p_lookup_code VARCHAR2) IS
68 SELECT UPPER(description)
69 FROM hr_lookups
70 WHERE lookup_type ='MAR_STATUS'
71 AND lookup_code = p_lookup_code;
72
73 CURSOR csr_get_asg_tax_details (p_assignment_id NUMBER
74 ,p_effective_date DATE) IS
75 SELECT scl.segment11 tax_code
76 ,scl.segment4 tax_red
77 ,scl.segment7 labour_tax
78 ,scl.segment9 add_sr_tax
79 FROM per_all_assignments_f asg
80 ,hr_soft_coding_keyflex scl
81 WHERE asg.assignment_id = p_assignment_id
82 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
83 AND p_effective_date
84 BETWEEN asg.effective_start_date AND asg.effective_end_date;
85
86 tax_details_rec csr_get_asg_tax_details%ROWTYPE;
87 --
88 BEGIN
89 hr_utility.trace('Entering validate_tax_code_combinations');
90 /*
91 hr_utility.trace('p_payroll_id '||p_payroll_id );
92 hr_utility.trace('p_effective_date '||p_effective_date );
93 hr_utility.trace('p_tax_code '||p_tax_code );
94 hr_utility.trace('p_tax_red_apply '||p_tax_red_apply );
95 hr_utility.trace('p_labour_tax_apply '||p_labour_tax_apply );
96 hr_utility.trace('p_add_sr_tax_apply '||p_add_sr_tax_apply );
97 */
98 l_tax_code := NVL(p_tax_code,'940');
99 l_tax_red_apply := NVL(p_tax_red_apply,'N');
100 l_labour_tax_apply := NVL(p_labour_tax_apply,'N');
101 l_add_sr_tax_apply := NVL(p_add_sr_tax_apply,'N');
102
103 /*
104 hr_utility.trace('l_tax_code '||l_tax_code );
105 hr_utility.trace('l_tax_red_apply '||l_tax_red_apply );
106 hr_utility.trace('l_labour_tax_apply '||l_labour_tax_apply );
107 hr_utility.trace('l_add_sr_tax_apply '||l_add_sr_tax_apply );
108 */
109
110 IF p_assignment_id IS NOT NULL THEN
111 /*Fetch the Db Values for the Assignment if Update_emp_asg
112 is being called */
113
114 OPEN csr_get_asg_tax_details(p_assignment_id,p_effective_date);
115 FETCH csr_get_asg_tax_details INTO tax_details_rec;
116 /*
117 hr_utility.trace('tax_details_rec.tax_code '||tax_details_rec.tax_code );
118 hr_utility.trace('tax_details_rec.tax_red '||tax_details_rec.tax_red );
119 hr_utility.trace('tax_details_rec.labour_tax '||tax_details_rec.labour_tax );
120 hr_utility.trace('tax_details_rec.add_sr_tax '||tax_details_rec.add_sr_tax );
121 */
122 IF p_tax_code= hr_api.g_varchar2 THEN
123 l_tax_code := NVL(tax_details_rec.tax_code,'940') ;
124 END IF;
125 IF p_tax_red_apply= hr_api.g_varchar2 THEN
126 l_tax_red_apply := NVL(tax_details_rec.tax_red,'N') ;
127 END IF;
128 IF p_labour_tax_apply= hr_api.g_varchar2 THEN
129 l_labour_tax_apply := NVL(tax_details_rec.labour_tax,'N') ;
130 END IF;
131 IF p_add_sr_tax_apply= hr_api.g_varchar2 THEN
132 l_add_sr_tax_apply := NVL(tax_details_rec.add_sr_tax,'N') ;
133 END IF;
134 END IF;
135 /*
136 hr_utility.trace('p_payroll_id '||p_payroll_id );
137 hr_utility.trace('l_tax_code '||l_tax_code );
138 hr_utility.trace('l_tax_red_apply '||l_tax_red_apply );
139 hr_utility.trace('l_labour_tax_apply '||l_labour_tax_apply );
140 hr_utility.trace('l_add_sr_tax_apply '||l_add_sr_tax_apply );
141 */
142 IF p_payroll_id IS NULL AND l_tax_code <> '940' THEN
143 -- Message Text - "You cannot enter tax information because this
144 -- assignment does not have a payroll attached to it."
145 hr_utility.set_message(800, 'HR_NL_PAYROLL_IS_NULL');
146 hr_utility.raise_error;
147 --
148 ELSE
149 --
150 -- Tax code validations
151 --
152 IF l_labour_tax_apply = 'Y' THEN
153 IF l_tax_red_apply = 'N' THEN
154 -- Message Text- "The Labour Tax Reduction indicator is
155 -- applicable only when Tax Reduction indicator is applicable."
156 -- hr_utility.trace('IN');
157 hr_utility.set_message(800, 'HR_NL_LTR_NA_FOR_TR_NA');
158 hr_utility.raise_error;
159 END IF;
160 END IF;
161 IF l_add_sr_tax_apply = 'Y' THEN
162 IF (l_tax_red_apply = 'N') THEN
163 -- Message Text - "Additional Senior Tax indicator is applicable
164 -- only if Tax Reduction is applicable."
165
166 hr_utility.set_message(800, 'HR_STR_NA_IF_LTR_AND_TR_NA');
167 hr_utility.raise_error;
168 END IF;
169 END IF;
170 --
171 -- hr_utility.trace('INSIDE TAX VALIDATION THREE');
172
173 -- Get Payroll Period Type
174 OPEN get_payroll_period_type(p_payroll_id,p_effective_date);
175 FETCH get_payroll_period_type INTO l_period_type;
176 CLOSE get_payroll_period_type;
177 --
178
179 --hr_utility.trace('INSIDE TAX VALIDATION FOUR');
180
181 -- Get Payroll Period Type Code
182 pay_nl_tax_pkg.get_period_type_code(l_period_type
183 ,p_period_type
184 ,l_period_code);
185
186 --l_tax_code :='940';
187 --hr_utility.trace('INSIDE TAX VALIDATION FIVE'||l_period_code||l_tax_code);
188
189 -- Validate Tax Code
190 pay_nl_tax_pkg.chk_tax_code(l_tax_code
191 ,l_period_code
192 ,l_1_digit
193 ,l_2_digit
194 ,l_3_digit
195 ,l_valid);
196 --
197 IF l_valid THEN
198 --
199 IF l_2_digit = '2' AND l_labour_tax_apply = 'Y' THEN
200 -- Message Text
201 -- "The Labour Tax Reduction indicator is applicable to the
202 -- white tax table only. Please select the white tax table
203 -- if the employee is eligible for a labour tax reduction."
204 hr_utility.set_message(800, 'HR_NL_LTR_NA_FOR_GREEN_TABLE');
205 hr_utility.raise_error;
206 END IF;
207 --
208 IF l_2_digit = '1' AND l_add_sr_tax_apply = 'Y' THEN
209 -- Message Text -
210 --"The Additional Senior Tax Reduction indicator is applicable
211 -- to the green tax table only. Please select the green tax
212 -- table if the employee is eligible for an additional
213 -- senior tax reduction."
214 hr_utility.set_message(800, 'HR_NL_ASTR_NA_FOR_WHITE_TABLE');
215 hr_utility.raise_error;
216 END IF;
217 --
218 IF l_2_digit= '1' AND (l_1_digit = '6' OR l_1_digit = '7') THEN
219 -- Message Text -
220 -- "The tax code is invalid, the taxation types 6 and 7 are
221 -- applicable for the green tax table only. Please enter a
222 -- valid tax code."
223 hr_utility.set_message(800, 'HR_NL_TAXATION_TYPES_NA');
224 hr_utility.raise_error;
225 END IF;
226 --
227 OPEN csr_get_marital_status(p_person_id,p_effective_date);
228 FETCH csr_get_marital_status INTO l_mar_status;
229 CLOSE csr_get_marital_status;
230 --
231 OPEN csr_get_lookup_desc(l_mar_status);
232 FETCH csr_get_lookup_desc INTO l_lookup_desc;
233 CLOSE csr_get_lookup_desc;
234 --
235 IF (l_mar_status = 'M' OR INSTR(l_lookup_desc,'NOT SINGLE') >0)
236 AND l_add_sr_tax_apply = 'Y' THEN
237 -- Message Text -
238 -- "The Additional Senior Tax Reduction indicator is only
239 -- applicable for employees who have a status of single."
240 hr_utility.set_message(800, 'HR_NL_ASTR_APPLIES_FOR_SINGLE');
241 hr_utility.raise_error;
242 END IF;
243 --
244 l_age := pay_nl_tax_pkg.check_age_payroll_period
245 (p_person_id
246 ,p_payroll_id
247 ,p_effective_date);
248 --
249 IF l_tax_code = '227' AND l_age < 65 THEN
250 -- Message Text -
251 -- "The tax code 227 is not applicable for employees who are
252 -- under 65 years old. Please enter a valid tax code
253 -- for the assignment."
254 hr_utility.set_message(800, 'HR_NL_TAX_CODE_227_NA');
255 hr_utility.set_message_token('TAX_CODE', '227');
256 hr_utility.raise_error;
257 ELSIF l_tax_code = '228' AND l_age >= 65 THEN
258 -- Message Text -
259 -- "The tax code 228 is not applicable for employees who are
260 -- 65 years old or over. Please enter a valid tax code
261 -- for the assignment."
262 hr_utility.set_message(801, 'HR_NL_TAX_CODE_228_NA');
263 hr_utility.set_message_token('TAX_CODE', '228');
264 hr_utility.raise_error;
265 END IF;
266 IF l_add_sr_tax_apply = 'Y' AND l_age < 65 THEN
267 -- Message Text -
268 -- "Additional Senior Tax Reduction indicator is not applicable
269 -- for employees who are under 65 years old."
270
271 hr_utility.set_message(800, 'HR_NL_ASTR_NA_FOR_AGE_LT_65');
272 hr_utility.raise_error;
273 END IF;
274 --
275 --
276 --
277 ELSE-- Tax code invalid
278 -- Message Text -
279 -- "This is an invalid Tax Code. Please enter the correct code."
280 hr_utility.set_message(800, 'HR_NL_TAX_CODE_INVALID');
281 hr_utility.raise_error;
282 END IF;
283 --
284 END IF;
285
286 --
287 END validate_tax_code_combinations;
288 --
289 --------------------------------------------------------------------------------
290 -- Validate Special Indicators
291 --------------------------------------------------------------------------------
292 -- Special Indicators - SEGMENT10
293 --
294 PROCEDURE validate_spl_indicators(p_special_indicators IN VARCHAR2
295 ,p_assignment_id number
296 ,p_effective_date DATE ) IS
297 --
298 l_set BOOLEAN;
299 l_special_indicators1 VARCHAR2(60);
300 l_special_indicators varchar2(60);
301 l_exists VARCHAR2(30);
302 --
303 TYPE special_indicators_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
304
305 l_spl_indicator special_indicators_tab;
306 --
307 CURSOR csr_lookup_code_exists(p_lookup_type VARCHAR2
308 ,p_lookup_code VARCHAR2) IS
309 SELECT '1'
310 FROM hr_lookups
311 WHERE lookup_code = p_lookup_code
312 AND lookup_type = p_lookup_type;
313
314 --
315
316 CURSOR csr_get_spl_indicator_details (p_assignment_id NUMBER
317 ,p_effective_date DATE) IS
318 SELECT scl.segment10 special_indicator
319 FROM per_all_assignments_f asg
320 ,hr_soft_coding_keyflex scl
321 WHERE asg.assignment_id = p_assignment_id
322 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
323 AND p_effective_date
324 BETWEEN asg.effective_start_date AND asg.effective_end_date;
325
326 l_special_indicators2 csr_get_spl_indicator_details%ROWTYPE;
327
328 BEGIN
329 hr_utility.trace('Entering validate_spl_indicators');
330 --
331 IF p_special_indicators = hr_api.g_varchar2 THEN
332 open csr_get_spl_indicator_details(p_assignment_id,p_effective_date);
333 fetch csr_get_spl_indicator_details into l_special_indicators2;
334 l_special_indicators := l_special_indicators2.special_indicator;
335 close csr_get_spl_indicator_details;
336 ELSE
337 l_special_indicators := p_special_indicators;
338 END IF;
339
340 IF l_special_indicators IS NOT NULL THEN
341 --
342 IF mod(length(l_special_indicators),2) <> 0 THEN
343 -- Message Text -
344 -- "A special indicator value is invalid."
345
346 hr_utility.set_message(800, 'HR_NL_INVALID_FIELD');
347 hr_utility.set_message_token('FIELD', 'Special Indicator');
348
349 hr_utility.raise_error;
350 --
351 ELSE
352 --
353 pay_nl_tax_pkg.get_spl_inds(l_special_indicators
354 ,l_spl_indicator(1)
355 ,l_spl_indicator(2)
356 ,l_spl_indicator(3)
357 ,l_spl_indicator(4)
358 ,l_spl_indicator(5)
359 ,l_spl_indicator(6)
360 ,l_spl_indicator(7)
361 ,l_spl_indicator(8)
362 ,l_spl_indicator(9)
363 ,l_spl_indicator(10)
364 ,l_spl_indicator(11)
365 ,l_spl_indicator(12)
366 ,l_spl_indicator(13));
367 --
368 -- Validate special indicators
369 --
370 FOR i IN 1..l_spl_indicator.COUNT LOOP
371 IF l_spl_indicator(i) IS NOT NULL THEN
372 OPEN csr_lookup_code_exists('NL_SPECIAL_INDICATORS'
373 ,l_spl_indicator(i));
374 FETCH csr_lookup_code_exists INTO l_exists;
375 IF csr_lookup_code_exists%NOTFOUND THEN
376 CLOSE csr_lookup_code_exists;
377 -- Message Text -
378 -- "A special indicator value is invalid."
379 hr_utility.set_message(800, 'HR_NL_INVALID_FIELD');
380 hr_utility.set_message_token('FIELD', 'Special Indicator');
384 CLOSE csr_lookup_code_exists;
381 hr_utility.raise_error;
382 --
383 END IF;
385 END IF;
386 END LOOP;
387 --
388 pay_nl_tax_pkg.set_spl_inds(l_spl_indicator(1)
389 ,l_spl_indicator(2)
390 ,l_spl_indicator(3)
391 ,l_spl_indicator(4)
392 ,l_spl_indicator(5)
393 ,l_spl_indicator(6)
394 ,l_spl_indicator(7)
395 ,l_spl_indicator(8)
396 ,l_spl_indicator(9)
397 ,l_spl_indicator(10)
398 ,l_spl_indicator(11)
399 ,l_spl_indicator(12)
400 ,l_spl_indicator(13)
401 ,l_set
402 ,l_special_indicators1);
403 IF l_set THEN
404 -- Message Text -
405 -- "You have already selected this special indicator for
406 -- the employee."
407 hr_utility.set_message(800, 'HR_NL_SPL_INDICATOR_SET');
408 hr_utility.raise_error;
409 END IF;
410 --
411 END IF;
412 --
413 END IF;
414 --
415 hr_utility.trace('Leaving validate_spl_indicators');
416
417 END validate_spl_indicators;
418 --
419 --------------------------------------------------------------------------------
420 -- Other Validations
421 --------------------------------------------------------------------------------
422 -- Commencing From /To - SEGMENT21 and SEGMENT23
423 -- Employment Type - SEGMENT2
424 -- Employment Sub Type - SEGMENT3
425 --
426 PROCEDURE other_validations(p_assignment_id IN NUMBER
427 ,p_effective_date IN DATE
428 ,p_commencing_from IN DATE
429 ,p_date_ending IN DATE
430 ,p_employment_type IN VARCHAR2
431 ,p_employment_subtype IN VARCHAR2
432 ,p_ind_working_hrs IN NUMBER
433 ,p_ind_perc IN NUMBER
434 ,p_percentage IN NUMBER
435 ,p_frequency IN VARCHAR2
436 ,p_normal_hours IN NUMBER) IS
437 --
438 CURSOR csr_lookup_code_exists(p_lookup_type VARCHAR2
439 ,p_lookup_code VARCHAR2) IS
440 SELECT '1'
441 FROM hr_lookups
442 WHERE lookup_code = p_lookup_code
443 AND lookup_type = p_lookup_type;
444 --
445 l_lookup_type hr_lookups.lookup_type%TYPE;
446 l_exists VARCHAR2(30);
447
448
449 CURSOR csr_get_asg_oth_details (p_assignment_id NUMBER
450 ,p_effective_date DATE) IS
451 SELECT scl.segment2 emp_type
452 ,scl.segment3 emp_sub_type
453 ,scl.segment21 commencing_from
454 ,scl.segment23 date_ending
455 FROM per_all_assignments_f asg
456 ,hr_soft_coding_keyflex scl
457 WHERE asg.assignment_id = p_assignment_id
458 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
459 AND p_effective_date
460 BETWEEN asg.effective_start_date AND asg.effective_end_date;
461
462 oth_details_rec csr_get_asg_oth_details%ROWTYPE;
463 --
464 l_emp_type varchar2(60);
465 l_emp_sub_type varchar2(60);
466 l_commencing_from date;
467 l_date_ending date;
468 --
469
470 BEGIN
471 hr_utility.trace('Entering other_validations');
472 /*
473 hr_utility.trace(' p_effective_date '||p_effective_date);
474 hr_utility.trace(' p_commencing_from '||p_commencing_from);
475 hr_utility.trace(' p_date_ending '||p_date_ending);
476 hr_utility.trace(' p_employment_type '||p_employment_type);
477 hr_utility.trace(' p_employment_subtype '||p_employment_subtype);
478 hr_utility.trace(' p_ind_working_hrs '||p_ind_working_hrs);
479 hr_utility.trace(' p_ind_perc '||p_ind_perc);
480 hr_utility.trace(' p_percentage '||p_percentage);
481 hr_utility.trace(' p_frequency '||p_frequency);
482 hr_utility.trace(' p_normal_hours '||p_normal_hours);
483 */
484
485 l_emp_type := p_employment_type;
486 l_emp_sub_type := p_employment_subtype;
487 l_commencing_from:= p_commencing_from;
488 l_date_ending:= p_date_ending;
489
490
491 IF p_assignment_id IS NOT NULL THEN
492 /*Fetch the Db Values for the Assignment if Update_emp_asg
493 is being called */
494
495 OPEN csr_get_asg_oth_details(p_assignment_id,p_effective_date);
496 FETCH csr_get_asg_oth_details INTO oth_details_rec;
497 IF csr_get_asg_oth_details%FOUND THEN
498 IF p_employment_type= hr_api.g_varchar2 THEN
499 l_emp_type := oth_details_rec.emp_type ;
500 END IF;
501 IF p_employment_subtype= hr_api.g_varchar2 THEN
502 l_emp_sub_type := oth_details_rec.emp_sub_type ;
503 END IF;
504 IF p_commencing_from= hr_api.g_date THEN
505 l_commencing_from := oth_details_rec.commencing_from ;
506 END IF;
507 IF p_date_ending= hr_api.g_date THEN
511
508 l_date_ending := oth_details_rec.date_ending ;
509 END IF;
510
512 ELSE
513 l_emp_type:=NULL;
514 l_emp_sub_type:=NULL;
515 l_commencing_from:=NULL;
516 l_date_ending:=NULL;
517 END IF;
518
519 END IF;
520 --
521 IF l_emp_type IS NOT NULL AND l_emp_sub_type IS NOT NULL THEN
522 --
523 IF l_emp_type = 'RE' THEN
524 l_lookup_type := 'NL_REAL_EMPLOYMENT_SUBTYPES';
525 ELSIF l_emp_type = 'FE' THEN
526 l_lookup_type := 'NL_FICT_EMPLOYMENT_SUBTYPES';
527 ELSIF l_emp_type = 'PE' THEN
528 l_lookup_type := 'NL_PREV_EMPLOYMENT_SUBTYPES';
529 END IF;
530 --
531 OPEN csr_lookup_code_exists(l_lookup_type
532 ,l_emp_sub_type);
533 FETCH csr_lookup_code_exists INTO l_exists;
534 IF csr_lookup_code_exists%NOTFOUND THEN
535 --
536 --CLOSE csr_lookup_code_exists;
537 -- Message Text
538 -- 'Employment Sub Type is invalid. Please enter a valid value.'
539
540 hr_utility.set_message(800, 'HR_NL_INVALID_FIELD');
541 hr_utility.set_message_token('FIELD', 'Employment Sub Type');
542 hr_utility.raise_error;
543 --
544 END IF;
545 CLOSE csr_lookup_code_exists;
546 --
547 ELSIF l_emp_type IS NULL AND l_emp_sub_type IS NOT NULL THEN
548 -- Message Text -
549 -- "You are attempting to save this record without submitting all of the
550 -- mandatory information. Please enter a value in Employment Type."
551 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
552 hr_utility.set_message_token('FIELD', 'Employment Type');
553 hr_utility.raise_error;
554 --
555 END IF;
556 --
557
558 --
559 --
560 IF l_commencing_from IS NOT NULL AND l_date_ending IS NOT NULL THEN
561 IF l_commencing_from > l_date_ending THEN
562 -- Message Text -
563 -- "The Commencing From date cannot be later than the Date Ending
564 -- date. Please enter a Commencing From date earlier than the
565 -- Date Ending date."
566 hr_utility.set_message(800, 'HR_NL_INVALID_DATE');
567 hr_utility.raise_error;
568 END IF;
569 END IF;
570
571 -- Message Text -
572 -- "You are attempting to save this record without submitting all of the
573 -- mandatory information. Please enter a value in Frequency."
574
575 IF ((p_ind_working_hrs <> hr_api.g_number OR p_normal_hours <> hr_api.g_number)
576 AND p_frequency IS null) THEN
577 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
578 hr_utility.set_message_token('FIELD', 'Frequency');
579 hr_utility.raise_error;
580 END IF;
581
582
583 IF (p_frequency = 'D' AND
584 (p_ind_working_hrs > fnd_number.canonical_to_number('24') OR
585 p_normal_hours > fnd_number.canonical_to_number('24'))) THEN
586 hr_utility.set_message(800, 'HR_NL_INCORRECT_FREQUENCY');
587 hr_utility.raise_error;
588 END IF;
589
590 IF (p_frequency = 'W' AND
591 (p_ind_working_hrs > fnd_number.canonical_to_number('99.99') OR
592 p_normal_hours > fnd_number.canonical_to_number('99.99'))) THEN
593 hr_utility.set_message(800, 'HR_NL_INCORRECT_FREQUENCY');
594 hr_utility.raise_error;
595 END IF;
596
597
598 IF (p_frequency = 'M' AND
599 (p_ind_working_hrs > fnd_number.canonical_to_number('744') OR
600 p_normal_hours > fnd_number.canonical_to_number('744'))) THEN
601 hr_utility.set_message(800, 'HR_NL_INCORRECT_FREQUENCY');
602 hr_utility.raise_error;
603 END IF;
604
605
606 IF (p_frequency = 'Y' AND
607 (p_ind_working_hrs > fnd_number.canonical_to_number('8784') OR
608 p_normal_hours > fnd_number.canonical_to_number('8784'))) THEN
609 hr_utility.set_message(800, 'HR_NL_INCORRECT_FREQUENCY');
610 hr_utility.raise_error;
611 END IF;
612
613 IF (p_ind_perc <> hr_api.g_number) THEN
614 IF (p_ind_perc < fnd_number.canonical_to_number('0.00') OR
615 p_ind_perc > fnd_number.canonical_to_number('100.00')) THEN
616 hr_utility.set_message(800, 'HR_NL_INVALID_PERCENT');
617 hr_utility.raise_error;
618 END IF;
619 END IF;
620
621 IF (p_percentage <> hr_api.g_number) THEN
622 IF (p_percentage < fnd_number.canonical_to_number('0.00') OR
623 p_percentage > fnd_number.canonical_to_number('9999.9999')) THEN
624 hr_utility.set_message(800, 'HR_NL_REALNUMBER_INVALID');
625 hr_utility.set_message_token('MINIMUM', '0.00');
626 hr_utility.set_message_token('MAXIMUM', '9999.9999');
627 hr_utility.set_message_token('PRECISION', '4');
628 hr_utility.raise_error;
629 END IF;
630 END IF;
631 hr_utility.trace('Leaving other_validations');
632
633 --
634 END other_validations;
635
636 PROCEDURE extra_assignment_checks
637 ( p_person_id IN NUMBER
638 ,p_payroll_id IN NUMBER
639 ,p_effective_date IN DATE
640 ,p_frequency IN VARCHAR2
641 ,p_normal_hours IN NUMBER
642 ,p_scl_segment1 IN VARCHAR2
643 ,p_scl_segment2 IN VARCHAR2
647 ,p_scl_segment6 IN VARCHAR2
644 ,p_scl_segment3 IN VARCHAR2
645 ,p_scl_segment4 IN VARCHAR2
646 ,p_scl_segment5 IN VARCHAR2
648 ,p_scl_segment7 IN VARCHAR2
649 ,p_scl_segment8 IN VARCHAR2
650 ,p_scl_segment9 IN VARCHAR2
651 ,p_scl_segment10 IN VARCHAR2
652 ,p_scl_segment11 IN VARCHAR2
653 ,p_scl_segment12 IN VARCHAR2
654 ,p_scl_segment13 IN VARCHAR2
655 ,p_scl_segment14 IN VARCHAR2
656 ,p_scl_segment15 IN VARCHAR2
657 ,p_scl_segment16 IN VARCHAR2
658 ,p_scl_segment17 IN VARCHAR2
659 ,p_scl_segment18 IN VARCHAR2
660 ,p_scl_segment19 IN VARCHAR2
661 ,p_scl_segment20 IN VARCHAR2
662 ,p_scl_segment21 IN VARCHAR2
663 ,p_scl_segment22 IN VARCHAR2
664 ,p_scl_segment23 IN VARCHAR2
665 ,p_scl_segment24 IN VARCHAR2
666 ,p_scl_segment25 IN VARCHAR2
667 ,p_scl_segment26 IN VARCHAR2
668 ,p_scl_segment27 IN VARCHAR2
669 ,p_scl_segment28 IN VARCHAR2
670 ,p_scl_segment29 IN VARCHAR2
671 ,p_scl_segment30 IN VARCHAR2
672 )IS
673
674
675 l_commencing_date DATE;
676 l_date_ending DATE;
677 l_frequency varchar2(50);
678 l_percentage number;
679 l_ind_perc number;
680 l_ind_working_hrs number;
681
682 BEGIN
683 --
684 hr_utility.trace('Entering extra_assignment_checks');
685 --
686 -- Added for GSI Bug 5472781
687 --
688 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
689 --
690 /*
691 hr_utility.trace('p_normal_hours '||p_normal_hours );
692 hr_utility.trace('p_frequency '||p_frequency );
693 hr_utility.trace('p_payroll_id '||p_payroll_id );
694 hr_utility.trace('p_scl_segment1 '||p_scl_segment1 );
695 hr_utility.trace('p_scl_segment2 '||p_scl_segment2 );
696 hr_utility.trace('p_scl_segment3 '||p_scl_segment3 );
697 hr_utility.trace('p_scl_segment4 '||p_scl_segment4 );
698 hr_utility.trace('p_scl_segment5 '||p_scl_segment5 );
699 hr_utility.trace('p_scl_segment6 '||p_scl_segment6 );
700 hr_utility.trace('p_scl_segment7 '||p_scl_segment7 );
701 hr_utility.trace('p_scl_segment8 '||p_scl_segment8 );
702 hr_utility.trace('p_scl_segment9 '||p_scl_segment9 );
703 hr_utility.trace('p_scl_segment10 '||p_scl_segment10 );
704 hr_utility.trace('p_scl_segment11 '||p_scl_segment11 );
705 hr_utility.trace('p_scl_segment12 '||p_scl_segment12 );
706 hr_utility.trace('p_scl_segment13 '||p_scl_segment13 );
707 hr_utility.trace('p_scl_segment14 '||p_scl_segment14 );
708 hr_utility.trace('p_scl_segment15 '||p_scl_segment15 );
709 hr_utility.trace('p_scl_segment16 '||p_scl_segment16 );
710 hr_utility.trace('p_scl_segment17 '||p_scl_segment17 );
711 hr_utility.trace('p_scl_segment18 '||p_scl_segment18 );
712 hr_utility.trace('p_scl_segment19 '||p_scl_segment19 );
713 hr_utility.trace('p_scl_segment20 '||p_scl_segment20 );
714 hr_utility.trace('p_scl_segment21 '||p_scl_segment21 );
715 hr_utility.trace('p_scl_segment22 '||p_scl_segment22 );
716 hr_utility.trace('p_scl_segment23 '||p_scl_segment23 );
717 hr_utility.trace('p_scl_segment24 '||p_scl_segment24 );
718 hr_utility.trace('p_scl_segment25 '||p_scl_segment25 );
719 hr_utility.trace('p_scl_segment26 '||p_scl_segment26 );
720 hr_utility.trace('p_scl_segment27 '||p_scl_segment27 );
721 hr_utility.trace('p_scl_segment28 '||p_scl_segment28 );
722 hr_utility.trace('p_scl_segment29 '||p_scl_segment29 );
723 hr_utility.trace('p_scl_segment30 '||p_scl_segment30 );
724 */
725 l_frequency:=p_frequency;
726
727 IF p_scl_segment28 IS NULL then
728 l_ind_working_hrs := NULL;
729 ELSIF p_scl_segment28 <> hr_api.g_varchar2 then
730 l_ind_working_hrs:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment28),2);
731 ELSE
732 l_ind_working_hrs := hr_api.g_number;
733 END if;
734
735 validate_tax_code_combinations(p_person_id
736 ,null ,p_payroll_id ,p_effective_date
737 ,p_scl_segment11,p_scl_segment4,p_scl_segment7 ,p_scl_segment9);
738
739
740
741 validate_spl_indicators(p_scl_segment10,null,p_effective_date);
742
743
744
745 IF p_scl_segment21 IS NULL then
746 l_commencing_date := NULL;
747 ELSIF p_scl_segment21 <> hr_api.g_varchar2 then
748 l_commencing_date:=FND_DATE.CANONICAL_TO_DATE(p_scl_segment21);
749 ELSE
750 l_commencing_date := hr_api.g_date;
751 END if;
752
753 IF p_scl_segment23 IS NULL then
754 l_date_ending := NULL;
755 ELSIF p_scl_segment23 <> hr_api.g_varchar2 then
756 l_date_ending:=FND_DATE.CANONICAL_TO_DATE(p_scl_segment23);
757 ELSE
758 l_date_ending := hr_api.g_date;
759 END if;
760
761 IF p_scl_segment29 IS NULL then
762 l_percentage := NULL;
763 ELSIF p_scl_segment29 <> hr_api.g_varchar2 then
764 l_percentage:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment29),2);
765 ELSE
766 l_percentage := hr_api.g_number;
767 END if;
768
769 IF p_scl_segment20 IS NULL then
770 l_ind_perc := NULL;
771 ELSIF p_scl_segment20 <> hr_api.g_varchar2 then
772 l_ind_perc := FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment20);
773 ELSE
777
774 l_ind_perc := hr_api.g_number;
775 END if;
776
778 other_validations(null ,p_effective_date
779 ,l_commencing_date ,l_date_ending
780 ,p_scl_segment2 ,p_scl_segment3
781 ,l_ind_working_hrs ,l_ind_perc
782 ,l_percentage ,l_frequency ,p_normal_hours);
783 --
784 END IF;
785 --
786 hr_utility.trace('Leaving extra_assignment_checks');
787 --
788 END extra_assignment_checks;
789
790
791 PROCEDURE extra_assignment_checks1
792 ( p_assignment_id IN NUMBER
793 ,p_effective_date IN DATE
794 ,p_frequency IN VARCHAR2
795 ,p_normal_hours IN NUMBER
796 ,p_segment1 IN VARCHAR2
797 ,p_segment2 IN VARCHAR2
798 ,p_segment3 IN VARCHAR2
799 ,p_segment4 IN VARCHAR2
800 ,p_segment5 IN VARCHAR2
801 ,p_segment6 IN VARCHAR2
802 ,p_segment7 IN VARCHAR2
803 ,p_segment8 IN VARCHAR2
804 ,p_segment9 IN VARCHAR2
805 ,p_segment10 IN VARCHAR2
806 ,p_segment11 IN VARCHAR2
807 ,p_segment12 IN VARCHAR2
808 ,p_segment13 IN VARCHAR2
809 ,p_segment14 IN VARCHAR2
810 ,p_segment15 IN VARCHAR2
811 ,p_segment16 IN VARCHAR2
812 ,p_segment17 IN VARCHAR2
813 ,p_segment18 IN VARCHAR2
814 ,p_segment19 IN VARCHAR2
815 ,p_segment20 IN VARCHAR2
816 ,p_segment21 IN VARCHAR2
817 ,p_segment22 IN VARCHAR2
818 ,p_segment23 IN VARCHAR2
819 ,p_segment24 IN VARCHAR2
820 ,p_segment25 IN VARCHAR2
821 ,p_segment26 IN VARCHAR2
822 ,p_segment27 IN VARCHAR2
823 ,p_segment28 IN VARCHAR2
824 ,p_segment29 IN VARCHAR2
825 ,p_segment30 IN VARCHAR2
826 )IS
827
828
829 CURSOR csr_get_date_details (
830 p_assignment_id NUMBER
831 ,p_effective_date DATE) IS
832 SELECT scl.segment21 commencing_date
833 ,scl.segment23 date_ending
834 FROM per_all_assignments_f asg
835 ,hr_soft_coding_keyflex scl
836 WHERE asg.assignment_id = p_assignment_id
837 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
838 AND p_effective_date
839 BETWEEN asg.effective_start_date AND asg.effective_end_date;
840
841 date_details_rec csr_get_date_details%ROWTYPE;
842
843 cursor csr_asg is
844 select payroll_id,person_id
845 from per_all_assignments_f PAA
846 where assignment_id=p_assignment_id and
847 p_effective_date between PAA.effective_start_date and PAA.effective_end_date;
848
849 cursor csr_freq is
850 select frequency
851 from per_all_assignments_f PAA
852 where assignment_id=p_assignment_id and
853 p_effective_date between PAA.effective_start_date and PAA.effective_end_date;
854
855
856 v_csr_asg csr_asg%rowtype;
857
858 l_commencing_date DATE;
859 l_date_ending varchar2(50);
860 l_frequency varchar2(50);
861 l_percentage number;
862 l_ind_perc number;
863 l_ind_working_hrs number;
864
865 BEGIN
866 --
867 hr_utility.trace('Entering extra_assignment_checks1');
868 --
869 -- Added for GSI Bug 5472781
870 --
871 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
872 --
873 /*
874 hr_utility.trace('p_normal_hours '||p_normal_hours );
875 hr_utility.trace('p_frequency '||p_frequency );
876 hr_utility.trace('p_segment1 '||p_segment1 );
877 hr_utility.trace('p_segment2 '||p_segment2 );
878 hr_utility.trace('p_segment3 '||p_segment3 );
879 hr_utility.trace('p_segment4 '||p_segment4 );
880 hr_utility.trace('p_segment5 '||p_segment5 );
881 hr_utility.trace('p_segment6 '||p_segment6 );
882 hr_utility.trace('p_segment7 '||p_segment7 );
883 hr_utility.trace('p_segment8 '||p_segment8 );
884 hr_utility.trace('p_segment9 '||p_segment9 );
885 hr_utility.trace('p_segment10 '||p_segment10 );
886 hr_utility.trace('p_segment11 '||p_segment11 );
887 hr_utility.trace('p_segment12 '||p_segment12 );
888 hr_utility.trace('p_segment13 '||p_segment13 );
889 hr_utility.trace('p_segment14 '||p_segment14 );
890 hr_utility.trace('p_segment15 '||p_segment15 );
891 hr_utility.trace('p_segment16 '||p_segment16 );
892 hr_utility.trace('p_segment17 '||p_segment17 );
893 hr_utility.trace('p_segment18 '||p_segment18 );
894 hr_utility.trace('p_segment19 '||p_segment19 );
895 hr_utility.trace('p_segment20 '||p_segment20 );
896 hr_utility.trace('p_segment21 '||p_segment21 );
897 hr_utility.trace('p_segment22 '||p_segment22 );
898 hr_utility.trace('p_segment23 '||p_segment23 );
899 hr_utility.trace('p_segment24 '||p_segment24 );
900 hr_utility.trace('p_segment25 '||p_segment25 );
901 hr_utility.trace('p_segment26 '||p_segment26 );
902 hr_utility.trace('p_segment27 '||p_segment27 );
903 hr_utility.trace('p_segment28 '||p_segment28 );
904 hr_utility.trace('p_segment29 '||p_segment29 );
905 hr_utility.trace('p_segment30 '||p_segment30 );
906 */
907 open csr_asg;
908 fetch csr_asg into v_csr_asg;
909 close csr_asg;
910
911 IF v_csr_asg.payroll_id is NULL THEN
912 v_csr_asg.payroll_id := glo_payroll_id;
913 END IF;
914 l_frequency:=p_frequency;
915 IF p_frequency IS NULL then
916 l_frequency := NULL;
917 ELSIF p_frequency =hr_api.g_varchar2 then
918 open csr_freq;
919 fetch csr_freq into l_frequency ;
920 close csr_freq;
921 END if;
922
923 validate_tax_code_combinations(v_csr_asg.person_id ,p_assignment_id
924 ,v_csr_asg.payroll_id ,p_effective_date
925 ,p_segment11 ,p_segment4 ,p_segment7 ,p_segment9);
926
927 validate_spl_indicators(p_segment10,p_assignment_id,p_effective_date);
928
929 IF p_segment21 = hr_api.g_varchar2 THEN
930 open csr_get_date_details(p_assignment_id,p_effective_date);
931 hr_utility.trace('DATE1');
932 fetch csr_get_date_details into date_details_rec;
933 l_commencing_date := fnd_date.canonical_to_date(date_details_rec.commencing_date);
934 close csr_get_date_details;
935 ELSE
936 l_commencing_date := fnd_date.canonical_to_date(p_segment21);
937 END IF;
938
939 IF p_segment23 = hr_api.g_varchar2 THEN
940 open csr_get_date_details(p_assignment_id,p_effective_date);
941 fetch csr_get_date_details into date_details_rec;
942 l_date_ending := fnd_date.canonical_to_date(date_details_rec.date_ending);
943 close csr_get_date_details;
944 ELSE
945 l_date_ending := fnd_date.canonical_to_date(p_segment23);
946 END IF;
947
948 IF p_segment28 IS NULL THEN
949 l_ind_working_hrs := NULL;
950 ELSIF p_segment28 <> hr_api.g_varchar2 then
951 l_ind_working_hrs:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_segment28),2);
952 ELSE
953 l_ind_working_hrs := hr_api.g_number;
954 END if;
955
956 IF p_segment29 IS NULL THEN
957 l_percentage := NULL;
958 ELSIF p_segment29 <> hr_api.g_varchar2 THEN
959 l_percentage := FND_NUMBER.CANONICAL_TO_NUMBER(p_segment29);
960 ELSE
961 l_percentage := hr_api.g_number;
962 END IF;
963
964 IF p_segment20 IS NULL THEN
965 l_ind_perc := NULL;
966 ELSIF p_segment20 <> hr_api.g_varchar2 THEN
967 l_ind_perc := FND_NUMBER.CANONICAL_TO_NUMBER(p_segment20);
968 ELSE
969 l_ind_perc := hr_api.g_number;
970 END IF;
971
972
973 other_validations(p_assignment_id
974 ,p_effective_date,l_commencing_date ,l_date_ending
975 ,p_segment2 ,p_segment3
976 ,l_ind_working_hrs ,l_ind_perc
977 ,l_percentage ,l_frequency,p_normal_hours);
978
979 glo_payroll_id := NULL;
980 --
981 END IF;
982 --
983 hr_utility.trace('Leaving extra_assignment_checks');
984 --
985 END extra_assignment_checks1;
986
987 END hr_nl_extra_asg_rules;