[Home] [Help]
Skip to content
PACKAGE BODY: APPS.HR_NL_EXTRA_ASG_RULES
Source
4 --Sets the Global - glo_payroll_id
1 PACKAGE BODY hr_nl_extra_asg_rules AS
2 /* $Header: penlexar.pkb 120.2.12020000.3 2012/07/05 02:56:31 amnaraya ship $ */
3
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 );
111 /*Fetch the Db Values for the Assignment if Update_emp_asg
108 */
109
110 IF p_assignment_id IS NOT NULL THEN
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
256 hr_utility.raise_error;
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');
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');
381 hr_utility.raise_error;
382 --
383 END IF;
384 CLOSE csr_lookup_code_exists;
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
508 l_date_ending := oth_details_rec.date_ending ;
509 END IF;
510
511
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 --14084074
637 FUNCTION validate_employer_change
638 ( p_assignment_id IN NUMBER
639 ,p_establishment_id IN NUMBER
640 ,p_business_group_id IN NUMBER
641 ,p_effective_date IN DATE
642 ) RETURN BOOLEAN IS
643
644 CURSOR csr_asg_action_exists (p_assignment_id NUMBER) IS
645 SELECT 'Y'
646 FROM dual
647 WHERE EXISTS
648 (
649 SELECT 1
650 FROM pay_assignment_actions
651 WHERE assignment_id = p_assignment_id
652 );
653
654 CURSOR csr_get_asg_details(p_assignment_id NUMBER
655 ,p_effective_date DATE) IS
656 SELECT paa.establishment_id, paa.payroll_id
657 FROM per_all_assignments_f paa
658 WHERE paa.assignment_id = p_assignment_id
659 AND p_effective_date BETWEEN paa.effective_start_date
660 AND paa.effective_end_date;
661
662 CURSOR csr_get_emp_chg_flag(p_business_group_id NUMBER) IS
663 SELECT hoi.org_information8
664 FROM hr_organization_information hoi
665 WHERE hoi.organization_id = p_business_group_id
666 AND hoi.org_information_context = 'NL_BG_INFO';
667
668 CURSOR csr_get_payroll_employer(p_payroll_id NUMBER
669 ,p_effective_date DATE) IS
670 SELECT prl_information1 employer
671 FROM pay_payrolls_f ppf
672 WHERE ppf.payroll_id = p_payroll_id
673 AND p_effective_date BETWEEN ppf.effective_start_date
674 AND ppf.effective_end_date;
675
676 l_asg_action_exists VARCHAR2(1);
677 l_asg_details csr_get_asg_details%ROWTYPE;
678 l_old_employer per_all_assignments_f.establishment_id%TYPE;
679 l_new_employer per_all_assignments_f.establishment_id%TYPE;
680 l_emp_chg_flag VARCHAR2(1);
681
682 BEGIN
683 --hr_utility.trace('Entering validate_employer_change');
684
685 OPEN csr_get_emp_chg_flag(p_business_group_id);
686 FETCH csr_get_emp_chg_flag into l_emp_chg_flag;
687 CLOSE csr_get_emp_chg_flag;
688
689 IF nvl(l_emp_chg_flag,'Y') = 'N' THEN
690
691 l_new_employer := p_establishment_id;
692
693 --hr_utility.trace('New Employer '||l_new_employer);
694
695 OPEN csr_asg_action_exists(p_assignment_id);
696 FETCH csr_asg_action_exists into l_asg_action_exists;
697 CLOSE csr_asg_action_exists;
698
699 IF nvl(l_asg_action_exists,'N') = 'Y' THEN
700
701 OPEN csr_get_asg_details(p_assignment_id,p_effective_date);
702 FETCH csr_get_asg_details INTO l_asg_details;
703 CLOSE csr_get_asg_details;
704
705 --hr_utility.trace('Old Employer '||l_asg_details.establishment_id);
706 --hr_utility.trace('Payroll Id '||l_asg_details.payroll_id);
707
708 IF l_asg_details.establishment_id IS NULL AND l_asg_details.payroll_id IS NOT NULL THEN
709 OPEN csr_get_payroll_employer(l_asg_details.payroll_id,p_effective_date);
710 FETCH csr_get_payroll_employer INTO l_old_employer;
711 CLOSE csr_get_payroll_employer;
712 ELSE
713 l_old_employer := l_asg_details.establishment_id;
714 END IF;
715 --hr_utility.trace('Old Employer '||l_old_employer);
716 IF NVL(l_old_employer,0) = NVL(l_new_employer,0) THEN
717 RETURN TRUE;
718 ELSE
719 RETURN FALSE;
720 END IF;
721 END IF;
722 END IF;
723
724 --hr_utility.trace('Leaving validate_employer_change');
725
726 RETURN TRUE;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 hr_utility.set_location('Error raised in validate_employer_change ',9);
731 hr_utility.trace('Error in validate_employer_change : '||sqlerrm);
732 RETURN NULL;
733 END validate_employer_change;
734 --14084074
735
736 PROCEDURE extra_assignment_checks
737 ( p_person_id IN NUMBER
738 ,p_payroll_id IN NUMBER
739 ,p_effective_date IN DATE
740 ,p_frequency IN VARCHAR2
741 ,p_normal_hours IN NUMBER
742 ,p_scl_segment1 IN VARCHAR2
746 ,p_scl_segment5 IN VARCHAR2
743 ,p_scl_segment2 IN VARCHAR2
744 ,p_scl_segment3 IN VARCHAR2
745 ,p_scl_segment4 IN VARCHAR2
747 ,p_scl_segment6 IN VARCHAR2
748 ,p_scl_segment7 IN VARCHAR2
749 ,p_scl_segment8 IN VARCHAR2
750 ,p_scl_segment9 IN VARCHAR2
751 ,p_scl_segment10 IN VARCHAR2
752 ,p_scl_segment11 IN VARCHAR2
753 ,p_scl_segment12 IN VARCHAR2
754 ,p_scl_segment13 IN VARCHAR2
755 ,p_scl_segment14 IN VARCHAR2
756 ,p_scl_segment15 IN VARCHAR2
757 ,p_scl_segment16 IN VARCHAR2
758 ,p_scl_segment17 IN VARCHAR2
759 ,p_scl_segment18 IN VARCHAR2
760 ,p_scl_segment19 IN VARCHAR2
761 ,p_scl_segment20 IN VARCHAR2
762 ,p_scl_segment21 IN VARCHAR2
763 ,p_scl_segment22 IN VARCHAR2
764 ,p_scl_segment23 IN VARCHAR2
765 ,p_scl_segment24 IN VARCHAR2
766 ,p_scl_segment25 IN VARCHAR2
767 ,p_scl_segment26 IN VARCHAR2
768 ,p_scl_segment27 IN VARCHAR2
769 ,p_scl_segment28 IN VARCHAR2
770 ,p_scl_segment29 IN VARCHAR2
771 ,p_scl_segment30 IN VARCHAR2
772 )IS
773
774
775 l_commencing_date DATE;
776 l_date_ending DATE;
777 l_frequency varchar2(50);
778 l_percentage number;
779 l_ind_perc number;
780 l_ind_working_hrs number;
781
782 BEGIN
783 --
784 hr_utility.trace('Entering extra_assignment_checks');
785 --
786 -- Added for GSI Bug 5472781
787 --
788 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
789 --
790 /*
791 hr_utility.trace('p_normal_hours '||p_normal_hours );
792 hr_utility.trace('p_frequency '||p_frequency );
793 hr_utility.trace('p_payroll_id '||p_payroll_id );
794 hr_utility.trace('p_scl_segment1 '||p_scl_segment1 );
795 hr_utility.trace('p_scl_segment2 '||p_scl_segment2 );
796 hr_utility.trace('p_scl_segment3 '||p_scl_segment3 );
797 hr_utility.trace('p_scl_segment4 '||p_scl_segment4 );
798 hr_utility.trace('p_scl_segment5 '||p_scl_segment5 );
799 hr_utility.trace('p_scl_segment6 '||p_scl_segment6 );
800 hr_utility.trace('p_scl_segment7 '||p_scl_segment7 );
801 hr_utility.trace('p_scl_segment8 '||p_scl_segment8 );
802 hr_utility.trace('p_scl_segment9 '||p_scl_segment9 );
803 hr_utility.trace('p_scl_segment10 '||p_scl_segment10 );
804 hr_utility.trace('p_scl_segment11 '||p_scl_segment11 );
805 hr_utility.trace('p_scl_segment12 '||p_scl_segment12 );
806 hr_utility.trace('p_scl_segment13 '||p_scl_segment13 );
807 hr_utility.trace('p_scl_segment14 '||p_scl_segment14 );
808 hr_utility.trace('p_scl_segment15 '||p_scl_segment15 );
809 hr_utility.trace('p_scl_segment16 '||p_scl_segment16 );
810 hr_utility.trace('p_scl_segment17 '||p_scl_segment17 );
811 hr_utility.trace('p_scl_segment18 '||p_scl_segment18 );
812 hr_utility.trace('p_scl_segment19 '||p_scl_segment19 );
813 hr_utility.trace('p_scl_segment20 '||p_scl_segment20 );
814 hr_utility.trace('p_scl_segment21 '||p_scl_segment21 );
815 hr_utility.trace('p_scl_segment22 '||p_scl_segment22 );
816 hr_utility.trace('p_scl_segment23 '||p_scl_segment23 );
817 hr_utility.trace('p_scl_segment24 '||p_scl_segment24 );
818 hr_utility.trace('p_scl_segment25 '||p_scl_segment25 );
819 hr_utility.trace('p_scl_segment26 '||p_scl_segment26 );
820 hr_utility.trace('p_scl_segment27 '||p_scl_segment27 );
821 hr_utility.trace('p_scl_segment28 '||p_scl_segment28 );
822 hr_utility.trace('p_scl_segment29 '||p_scl_segment29 );
823 hr_utility.trace('p_scl_segment30 '||p_scl_segment30 );
824 */
825 l_frequency:=p_frequency;
826
827 IF p_scl_segment28 IS NULL then
828 l_ind_working_hrs := NULL;
829 ELSIF p_scl_segment28 <> hr_api.g_varchar2 then
830 l_ind_working_hrs:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment28),2);
831 ELSE
832 l_ind_working_hrs := hr_api.g_number;
833 END if;
834
835 validate_tax_code_combinations(p_person_id
836 ,null ,p_payroll_id ,p_effective_date
837 ,p_scl_segment11,p_scl_segment4,p_scl_segment7 ,p_scl_segment9);
838
839
840
841 validate_spl_indicators(p_scl_segment10,null,p_effective_date);
842
843
844
845 IF p_scl_segment21 IS NULL then
846 l_commencing_date := NULL;
847 ELSIF p_scl_segment21 <> hr_api.g_varchar2 then
848 l_commencing_date:=FND_DATE.CANONICAL_TO_DATE(p_scl_segment21);
849 ELSE
850 l_commencing_date := hr_api.g_date;
851 END if;
852
853 IF p_scl_segment23 IS NULL then
854 l_date_ending := NULL;
855 ELSIF p_scl_segment23 <> hr_api.g_varchar2 then
856 l_date_ending:=FND_DATE.CANONICAL_TO_DATE(p_scl_segment23);
857 ELSE
858 l_date_ending := hr_api.g_date;
859 END if;
860
861 IF p_scl_segment29 IS NULL then
862 l_percentage := NULL;
863 ELSIF p_scl_segment29 <> hr_api.g_varchar2 then
864 l_percentage:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment29),2);
865 ELSE
866 l_percentage := hr_api.g_number;
867 END if;
868
869 IF p_scl_segment20 IS NULL then
870 l_ind_perc := NULL;
871 ELSIF p_scl_segment20 <> hr_api.g_varchar2 then
872 l_ind_perc := FND_NUMBER.CANONICAL_TO_NUMBER(p_scl_segment20);
873 ELSE
874 l_ind_perc := hr_api.g_number;
875 END if;
876
877
878 other_validations(null ,p_effective_date
879 ,l_commencing_date ,l_date_ending
880 ,p_scl_segment2 ,p_scl_segment3
881 ,l_ind_working_hrs ,l_ind_perc
882 ,l_percentage ,l_frequency ,p_normal_hours);
883 --
884 END IF;
885 --
886 hr_utility.trace('Leaving extra_assignment_checks');
890
887 --
888 END extra_assignment_checks;
889
891 PROCEDURE extra_assignment_checks1
892 ( p_assignment_id IN NUMBER
893 ,p_effective_date IN DATE
894 ,p_frequency IN VARCHAR2
895 ,p_normal_hours IN NUMBER
896 ,p_segment1 IN VARCHAR2
897 ,p_segment2 IN VARCHAR2
898 ,p_segment3 IN VARCHAR2
899 ,p_segment4 IN VARCHAR2
900 ,p_segment5 IN VARCHAR2
901 ,p_segment6 IN VARCHAR2
902 ,p_segment7 IN VARCHAR2
903 ,p_segment8 IN VARCHAR2
904 ,p_segment9 IN VARCHAR2
905 ,p_segment10 IN VARCHAR2
906 ,p_segment11 IN VARCHAR2
907 ,p_segment12 IN VARCHAR2
908 ,p_segment13 IN VARCHAR2
909 ,p_segment14 IN VARCHAR2
910 ,p_segment15 IN VARCHAR2
911 ,p_segment16 IN VARCHAR2
912 ,p_segment17 IN VARCHAR2
913 ,p_segment18 IN VARCHAR2
914 ,p_segment19 IN VARCHAR2
915 ,p_segment20 IN VARCHAR2
916 ,p_segment21 IN VARCHAR2
917 ,p_segment22 IN VARCHAR2
918 ,p_segment23 IN VARCHAR2
919 ,p_segment24 IN VARCHAR2
920 ,p_segment25 IN VARCHAR2
921 ,p_segment26 IN VARCHAR2
922 ,p_segment27 IN VARCHAR2
923 ,p_segment28 IN VARCHAR2
924 ,p_segment29 IN VARCHAR2
925 ,p_segment30 IN VARCHAR2
926 ,p_establishment_id IN NUMBER
927 )IS
928
929
930 CURSOR csr_get_date_details (
931 p_assignment_id NUMBER
932 ,p_effective_date DATE) IS
933 SELECT scl.segment21 commencing_date
934 ,scl.segment23 date_ending
935 FROM per_all_assignments_f asg
936 ,hr_soft_coding_keyflex scl
937 WHERE asg.assignment_id = p_assignment_id
938 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
939 AND p_effective_date
940 BETWEEN asg.effective_start_date AND asg.effective_end_date;
941
942 date_details_rec csr_get_date_details%ROWTYPE;
943
944 cursor csr_asg is
945 select payroll_id,person_id,business_group_id
946 from per_all_assignments_f PAA
947 where assignment_id=p_assignment_id and
948 p_effective_date between PAA.effective_start_date and PAA.effective_end_date;
949
950 cursor csr_freq is
951 select frequency
952 from per_all_assignments_f PAA
953 where assignment_id=p_assignment_id and
954 p_effective_date between PAA.effective_start_date and PAA.effective_end_date;
955
956
957 v_csr_asg csr_asg%rowtype;
958
959 l_commencing_date DATE;
960 l_date_ending varchar2(50);
961 l_frequency varchar2(50);
962 l_percentage number;
963 l_ind_perc number;
964 l_ind_working_hrs number;
965
966 BEGIN
967 --
968 hr_utility.trace('Entering extra_assignment_checks1');
969 --
970 -- Added for GSI Bug 5472781
971 --
972 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
973 --
974 /*
975 hr_utility.trace('p_normal_hours '||p_normal_hours );
976 hr_utility.trace('p_frequency '||p_frequency );
977 hr_utility.trace('p_segment1 '||p_segment1 );
978 hr_utility.trace('p_segment2 '||p_segment2 );
979 hr_utility.trace('p_segment3 '||p_segment3 );
980 hr_utility.trace('p_segment4 '||p_segment4 );
981 hr_utility.trace('p_segment5 '||p_segment5 );
982 hr_utility.trace('p_segment6 '||p_segment6 );
983 hr_utility.trace('p_segment7 '||p_segment7 );
984 hr_utility.trace('p_segment8 '||p_segment8 );
985 hr_utility.trace('p_segment9 '||p_segment9 );
986 hr_utility.trace('p_segment10 '||p_segment10 );
987 hr_utility.trace('p_segment11 '||p_segment11 );
988 hr_utility.trace('p_segment12 '||p_segment12 );
992 hr_utility.trace('p_segment16 '||p_segment16 );
989 hr_utility.trace('p_segment13 '||p_segment13 );
990 hr_utility.trace('p_segment14 '||p_segment14 );
991 hr_utility.trace('p_segment15 '||p_segment15 );
993 hr_utility.trace('p_segment17 '||p_segment17 );
994 hr_utility.trace('p_segment18 '||p_segment18 );
995 hr_utility.trace('p_segment19 '||p_segment19 );
996 hr_utility.trace('p_segment20 '||p_segment20 );
997 hr_utility.trace('p_segment21 '||p_segment21 );
998 hr_utility.trace('p_segment22 '||p_segment22 );
999 hr_utility.trace('p_segment23 '||p_segment23 );
1000 hr_utility.trace('p_segment24 '||p_segment24 );
1001 hr_utility.trace('p_segment25 '||p_segment25 );
1002 hr_utility.trace('p_segment26 '||p_segment26 );
1003 hr_utility.trace('p_segment27 '||p_segment27 );
1004 hr_utility.trace('p_segment28 '||p_segment28 );
1005 hr_utility.trace('p_segment29 '||p_segment29 );
1006 hr_utility.trace('p_segment30 '||p_segment30 );
1007 */
1008 open csr_asg;
1009 fetch csr_asg into v_csr_asg;
1010 close csr_asg;
1011
1012 IF v_csr_asg.payroll_id is NULL THEN
1013 v_csr_asg.payroll_id := glo_payroll_id;
1014 END IF;
1015 l_frequency:=p_frequency;
1016 IF p_frequency IS NULL then
1017 l_frequency := NULL;
1018 ELSIF p_frequency =hr_api.g_varchar2 then
1019 open csr_freq;
1020 fetch csr_freq into l_frequency ;
1021 close csr_freq;
1022 END if;
1023
1024 validate_tax_code_combinations(v_csr_asg.person_id ,p_assignment_id
1025 ,v_csr_asg.payroll_id ,p_effective_date
1026 ,p_segment11 ,p_segment4 ,p_segment7 ,p_segment9);
1027
1028 validate_spl_indicators(p_segment10,p_assignment_id,p_effective_date);
1029
1030 IF p_segment21 = hr_api.g_varchar2 THEN
1031 open csr_get_date_details(p_assignment_id,p_effective_date);
1032 hr_utility.trace('DATE1');
1033 fetch csr_get_date_details into date_details_rec;
1034 l_commencing_date := fnd_date.canonical_to_date(date_details_rec.commencing_date);
1035 close csr_get_date_details;
1036 ELSE
1037 l_commencing_date := fnd_date.canonical_to_date(p_segment21);
1038 END IF;
1039
1040 IF p_segment23 = hr_api.g_varchar2 THEN
1041 open csr_get_date_details(p_assignment_id,p_effective_date);
1042 fetch csr_get_date_details into date_details_rec;
1043 l_date_ending := fnd_date.canonical_to_date(date_details_rec.date_ending);
1044 close csr_get_date_details;
1045 ELSE
1046 l_date_ending := fnd_date.canonical_to_date(p_segment23);
1047 END IF;
1048
1049 IF p_segment28 IS NULL THEN
1050 l_ind_working_hrs := NULL;
1051 ELSIF p_segment28 <> hr_api.g_varchar2 then
1052 l_ind_working_hrs:=round(FND_NUMBER.CANONICAL_TO_NUMBER(p_segment28),2);
1053 ELSE
1054 l_ind_working_hrs := hr_api.g_number;
1055 END if;
1056
1057 IF p_segment29 IS NULL THEN
1058 l_percentage := NULL;
1059 ELSIF p_segment29 <> hr_api.g_varchar2 THEN
1060 l_percentage := FND_NUMBER.CANONICAL_TO_NUMBER(p_segment29);
1061 ELSE
1062 l_percentage := hr_api.g_number;
1063 END IF;
1064
1065 IF p_segment20 IS NULL THEN
1066 l_ind_perc := NULL;
1067 ELSIF p_segment20 <> hr_api.g_varchar2 THEN
1068 l_ind_perc := FND_NUMBER.CANONICAL_TO_NUMBER(p_segment20);
1069 ELSE
1070 l_ind_perc := hr_api.g_number;
1071 END IF;
1072
1073
1074 other_validations(p_assignment_id
1075 ,p_effective_date,l_commencing_date ,l_date_ending
1076 ,p_segment2 ,p_segment3
1077 ,l_ind_working_hrs ,l_ind_perc
1078 ,l_percentage ,l_frequency,p_normal_hours);
1079
1080 --14084074
1081 IF p_establishment_id <> hr_api.g_number THEN
1082 IF NOT validate_employer_change(p_assignment_id,p_establishment_id,v_csr_asg.business_group_id,p_effective_date) THEN
1083 hr_utility.set_message(800, 'HR_373479_NL_EMPLOYER_CHG');
1084 hr_utility.raise_error;
1085 END IF;
1086 END IF;
1087 --14084074
1088
1089 glo_payroll_id := NULL;
1090 --
1091 END IF;
1092 --
1093 hr_utility.trace('Leaving extra_assignment_checks');
1094 --
1095 END extra_assignment_checks1;
1096
1097 END hr_nl_extra_asg_rules;