1 package body pay_ae_general as
2 /* $Header: pyaegenr.pkb 120.6 2005/11/10 03:05:54 abppradh noship $ */
3 g_package varchar2(30);
4 l_organization_id hr_all_organization_units.organization_id%type;
5 l_person_id per_all_people_f.person_id%type;
6 l_nationality_cd hr_lookups.meaning%type;
7 l_nationality_person per_all_people_f.nationality%type;
8 ------------------------------------------------------------------------
9 -- Function LOCAL_NATNATIONALITY_NOT_DEFINED
10 -- This function return NOTEXISTS If the value for HR: Local Nationality
11 -- Profile has not been defined else it retuns EXISTS.
12 ------------------------------------------------------------------------
13 function local_nationality_not_defined return varchar2
14 is
15 begin
16 BEGIN
17 l_organization_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
18 Select Org_Information1
19 Into l_nationality_cd
20 From HR_ORGANIZATION_INFORMATION
21 Where ORG_INFORMATION_CONTEXT = 'AE_BG_DETAILS'
22 And ORGANIZATION_ID = l_organization_id;
23 EXCEPTION
24 WHEN no_data_found Then
25 Null;
26 END;
27 if l_nationality_cd is null then
28 return 'NOTEXISTS';
29 else
30 return 'EXISTS';
31 end if;
32 end local_nationality_not_defined;
33 ------------------------------------------------------------------------
34 -- Function LOCAL_NATIONALITY_MATCHES
35 -- This function return NOMATCH If the value for HR: Local Nationality
36 -- Profile does not match with the person's nationality else it retuns MATCH.
37 ------------------------------------------------------------------------
38 function local_nationality_matches
39 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
40 p_date_earned IN Date)
41 return varchar2
42 is
43 begin
44 BEGIN
45 Select person_id
46 Into l_person_id
47 From PER_ALL_ASSIGNMENTS_F
48 Where ASSIGNMENT_ID = p_assignment_id
49 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
50 EXCEPTION
51 WHEN no_data_found Then
52 Null;
53 END;
54 BEGIN
55 Select per_information18
56 Into l_nationality_person
57 From PER_ALL_PEOPLE_F
58 Where PERSON_ID = l_person_id
59 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
60 EXCEPTION
61 WHEN no_data_found Then
62 Null;
63 END;
64 if l_nationality_cd = l_nationality_person then
65 return 'MATCH';
66 else
67 return 'NOMATCH';
68 end if;
69 end local_nationality_matches;
70 ------------------------------------------------------------------------
71 -- Function GET_LOCAL_NATIONALITY
72 -- This function is used to obtain a the local nationality defined at
73 -- the Business Group Level.
74 ------------------------------------------------------------------------
75 function get_local_nationality return varchar2
76 is
77 l_nationality hr_lookups.meaning%type;
78 begin
79 l_nationality := hr_general.decode_lookup('AE_NATIONALITY',l_nationality_cd);
80 RETURN l_nationality;
81 END get_local_nationality;
82 ------------------------------------------------------------------------
83 -- Function GET_SECTOR
84 ------------------------------------------------------------------------
85 function get_sector (p_tax_unit_id IN NUMBER) return varchar2
86 IS
87 l_sector varchar2(1);
88 CURSOR GET_AE_SECTOR (l_tax_unit_id number) IS
89 SELECT org_information6
90 FROM hr_organization_information
91 WHERE organization_id = l_tax_unit_id
92 AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
93 begin
94 l_sector := null;
95 OPEN get_ae_sector (p_tax_unit_id);
96 FETCH get_ae_sector INTO l_sector;
97 CLOSE get_ae_sector;
98 If l_sector is null THEN
99 return 'N';
100 Else
101 return l_sector;
102 End If;
103 end get_sector;
104 ------------------------------------------------------------------------
105 -- Function GET_MESSAGE
106 -- This function is used to obtain a message.
107 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
108 -- If you want to set the value of a token called ELEMENT to Social Ins
109 -- the token parameter would be 'ELEMENT:Social Ins.'
110 ------------------------------------------------------------------------
111 function get_message
112 (p_product in varchar2
113 ,p_message_name in varchar2
114 ,p_token1 in varchar2 default null
115 ,p_token2 in varchar2 default null
116 ,p_token3 in varchar2 default null) return varchar2
117 is
118 l_message varchar2(2000);
119 l_token_name varchar2(20);
120 l_token_value varchar2(80);
121 l_colon_position number;
122 l_proc varchar2(72) ;
123 --
124 begin
125 g_package := 'pay_ae_general';
126 l_proc := g_package||'.get_message';
127 --
128 hr_utility.set_location('Entered '||l_proc,5);
129 hr_utility.set_location('. Message Name: '||p_message_name,40);
130 fnd_message.set_name(p_product, p_message_name);
131 if p_token1 is not null then
132 -- Obtain token 1 name and value
133 l_colon_position := instr(p_token1,':');
134 l_token_name := substr(p_token1,1,l_colon_position-1);
135 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
136 fnd_message.set_token(l_token_name, l_token_value);
137 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
138 end if;
139 if p_token2 is not null then
140 -- Obtain token 2 name and value
141 l_colon_position := instr(p_token2,':');
142 l_token_name := substr(p_token2,1,l_colon_position-1);
143 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
144 fnd_message.set_token(l_token_name, l_token_value);
145 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
146 end if;
147 if p_token3 is not null then
148 -- Obtain token 3 name and value
149 l_colon_position := instr(p_token3,':');
150 l_token_name := substr(p_token3,1,l_colon_position-1);
151 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
152 fnd_message.set_token(l_token_name, l_token_value);
153 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
154 end if;
155 l_message := substr(fnd_message.get,1,254);
156 hr_utility.set_location('leaving '||l_proc,100);
157 return l_message;
158 end get_message;
159 ------------------------------------------------------------------------
160 -- Function GET_TABLE_BANDS
161 -- This function is used to obtain User table's high and low values.
162 ------------------------------------------------------------------------
163 function get_table_bands
164 (p_Date_Earned IN DATE
165 ,p_table_name in varchar2
166 ,p_return_type in varchar2) return number
167 is
168 CURSOR csr_get_user_table_id(l_table_name varchar2) IS
169 SELECT user_table_id
170 FROM pay_user_tables
171 WHERE legislation_code='AE'
172 AND UPPER(user_table_name) = UPPER(l_table_name);
173 CURSOR csr_get_min_low (l_user_table_id NUMBER, l_effective_date DATE) IS
174 SELECT MIN(to_number(row_low_range_or_name))
175 FROM pay_user_rows_f
176 WHERE user_table_id = l_user_table_id
177 AND legislation_code = 'AE'
178 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
179 CURSOR csr_get_min_high (l_user_table_id number ,l_effective_date DATE ) IS
180 SELECT MIN(to_number(row_high_range))
181 FROM pay_user_rows_f
182 WHERE user_table_id = l_user_table_id
183 AND legislation_code = 'AE'
184 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
185 CURSOR csr_get_max_high (l_user_table_id number, l_effective_date DATE) IS
186 SELECT MAX(to_number(row_high_range))
187 FROM pay_user_rows_f
188 WHERE user_table_id = l_user_table_id
189 AND legislation_code = 'AE'
190 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
191 l_ret_val number(15,3);
192 l_table_id number(9);
193 l_proc varchar2(72) ;
194 --
195 begin
196 g_package := 'pay_ae_general';
197 l_proc := g_package||'.get_table_bands';
198 --
199 hr_utility.set_location('Entered '||l_proc,5);
200 -- Get the User Table ID
201 OPEN csr_get_user_table_id(p_table_name);
202 FETCH csr_get_user_table_id INTO l_table_id;
203 CLOSE csr_get_user_table_id;
204 --
205 IF p_return_type = 'MIN_LOW' THEN
206 OPEN csr_get_min_low (l_table_id, p_date_earned);
207 FETCH csr_get_min_low INTO l_ret_val;
208 CLOSE csr_get_min_low;
209 ELSIF p_return_type = 'MIN_HIGH' THEN
210 OPEN csr_get_min_high (l_table_id, p_date_earned);
211 FETCH csr_get_min_high INTO l_ret_val;
212 CLOSE csr_get_min_high;
213 ELSIF p_return_type = 'MAX_HIGH' THEN
214 OPEN csr_get_max_high (l_table_id, p_date_earned);
215 FETCH csr_get_max_high INTO l_ret_val;
216 CLOSE csr_get_max_high;
217 END IF;
218 return l_ret_val;
219 end get_table_bands;
220 -----------------------------------------------------------------------
221 -- Functions for EFT file
222 -----------------------------------------------------------------------
223 -----------------------------------------------------------------------------
224 -- GET_PARAMETER used in SQL to decode legislative parameters
225 -----------------------------------------------------------------------------
226 FUNCTION get_parameter(
227 p_parameter_string IN VARCHAR2
228 ,p_token IN VARCHAR2
229 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
230 IS
231 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
232 l_start_pos NUMBER;
233 l_delimiter varchar2(1):=' ';
234 l_proc VARCHAR2(60):= g_package||' get parameter ';
235 BEGIN
236 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
237 IF l_start_pos = 0 THEN
238 l_delimiter := '|';
239 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
240 end if;
241 IF l_start_pos <> 0 THEN
242 l_start_pos := l_start_pos + length(p_token||'=');
243 l_parameter := substr(p_parameter_string,
244 l_start_pos,
245 instr(p_parameter_string||' ',
246 ',',l_start_pos)
247 - l_start_pos);
248 IF p_segment_number IS NOT NULL THEN
249 l_parameter := ':'||l_parameter||':';
250 l_parameter := substr(l_parameter,
251 instr(l_parameter,':',1,p_segment_number)+1,
252 instr(l_parameter,':',1,p_segment_number+1) -1
253 - instr(l_parameter,':',1,p_segment_number));
254 END IF;
255 END IF;
256 RETURN l_parameter;
257 END get_parameter;
258 --
259 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
260 ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
261 CURSOR get_multiple_assgts IS
262 SELECT count(DISTINCT paf.assignment_id)
263 FROM per_all_assignments_f paf
264 ,per_assignment_status_types pas
265 WHERE paf.assignment_type = 'E'
266 AND paf.PERSON_ID = p_person_id
267 AND p_effective_date between effective_start_date and effective_end_date
268 AND paf.assignment_status_type_id = pas.assignment_status_type_id
269 AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
270 l_count NUMBER :=0;
271 BEGIN
272 OPEN get_multiple_assgts;
273 FETCH get_multiple_assgts INTO l_count;
274 CLOSE get_multiple_assgts;
275 IF l_count > 1 THEN
276 RETURN 'Y';
277 ELSE
278 RETURN 'N';
279 END IF;
280 END chk_multiple_assignments;
281 -----
282 function get_count RETURN NUMBER as
283 l_count NUMBER(15);
284 CURSOR CSR_AE_EFT_COUNT IS
285 SELECT COUNT(*)
286 FROM per_assignments_f paf
287 ,per_people_f pef
288 ,pay_pre_payments ppp
289 ,pay_assignment_actions paa
290 ,pay_payroll_actions ppa
291 WHERE paa.payroll_action_id =
292 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
293 AND paa.pre_payment_id = ppp.pre_payment_id
294 AND paa.payroll_action_id = ppa.payroll_action_id
295 AND paa.assignment_id = paf.assignment_id
296 AND paf.person_id = pef.person_id
297 AND ppp.value <> 0
298 AND ppa.effective_date BETWEEN paf.effective_start_date
299 AND paf.effective_end_date
300 AND ppa.effective_date BETWEEN pef.effective_start_date
301 AND pef.effective_end_date;
302 BEGIN
303 open CSR_AE_EFT_COUNT;
304 fetch CSR_AE_EFT_COUNT into l_count;
305 close CSR_AE_EFT_COUNT;
306 return l_count;
307 END get_count;
308 ---------
309 function get_total_sum return number as
310 l_total_sum pay_pre_payments.value%type;
311 CURSOR CSR_AE_EFT_SUM is
312 SELECT SUM(ppp.value)
313 FROM per_assignments_f paf
314 ,per_people_f pef
315 ,pay_pre_payments ppp
316 ,pay_assignment_actions paa
317 ,pay_payroll_actions ppa
318 WHERE paa.payroll_action_id =
319 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
320 AND paa.pre_payment_id = ppp.pre_payment_id
321 AND paa.payroll_action_id = ppa.payroll_action_id
322 AND paa.assignment_id = paf.assignment_id
323 AND paf.person_id = pef.person_id
324 AND ppp.value <> 0
325 AND ppa.effective_date BETWEEN paf.effective_start_date
326 AND paf.effective_end_date
327 AND ppa.effective_date BETWEEN pef.effective_start_date
328 AND pef.effective_end_date;
329 BEGIN
330 open CSR_AE_EFT_SUM;
331 fetch CSR_AE_EFT_SUM into l_total_sum;
332 close CSR_AE_EFT_SUM;
333 return l_total_sum;
334 END get_total_sum;
335 --------
336 ---------
337 function get_credit_sum return number as
338 l_credit_sum pay_pre_payments.value%type;
339 CURSOR CSR_AE_EFT_SUM is
340 SELECT SUM(ppp.value)
341 FROM per_assignments_f paf
342 ,per_people_f pef
343 ,pay_pre_payments ppp
344 ,pay_assignment_actions paa
345 ,pay_payroll_actions ppa
346 WHERE paa.payroll_action_id =
347 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
348 AND paa.pre_payment_id = ppp.pre_payment_id
349 AND paa.payroll_action_id = ppa.payroll_action_id
350 AND paa.assignment_id = paf.assignment_id
351 AND paf.person_id = pef.person_id
352 AND ppp.value > 0
353 AND ppa.effective_date BETWEEN paf.effective_start_date
354 AND paf.effective_end_date
355 AND ppa.effective_date BETWEEN pef.effective_start_date
356 AND pef.effective_end_date;
357 BEGIN
358 open CSR_AE_EFT_SUM;
362 END get_credit_sum;
359 fetch CSR_AE_EFT_SUM into l_credit_sum;
360 close CSR_AE_EFT_SUM;
361 return l_credit_sum;
363 --------
364 ---------
365 function get_debit_sum return number as
366 l_debit_sum pay_pre_payments.value%type;
367 CURSOR CSR_AE_EFT_SUM is
368 SELECT SUM(ppp.value)
369 FROM per_assignments_f paf
370 ,per_people_f pef
371 ,pay_pre_payments ppp
372 ,pay_assignment_actions paa
373 ,pay_payroll_actions ppa
374 WHERE paa.payroll_action_id =
375 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
376 AND paa.pre_payment_id = ppp.pre_payment_id
377 AND paa.payroll_action_id = ppa.payroll_action_id
378 AND paa.assignment_id = paf.assignment_id
379 AND paf.person_id = pef.person_id
380 AND ppp.value < 0
381 AND ppa.effective_date BETWEEN paf.effective_start_date
382 AND paf.effective_end_date
383 AND ppa.effective_date BETWEEN pef.effective_start_date
384 AND pef.effective_end_date;
385 BEGIN
386 open CSR_AE_EFT_SUM;
387 fetch CSR_AE_EFT_SUM into l_debit_sum;
388 close CSR_AE_EFT_SUM;
389 return l_debit_sum;
390 END get_debit_sum;
391 --------
392 --------
393 function chk_tran_code (p_value IN VARCHAR2) return VARCHAR2 as
394 l_flag varchar2(1) := null;
395 BEGIN
396 If p_value <> 0 then
397 If p_value < 0 then
398 l_flag := 'N';
399 elsif p_value > 0 then
400 l_flag := 'Y';
401 end if;
402 End if;
403 Return nvl(l_flag,' ');
404 End chk_tran_code;
405 -----------
406 --------
407 ------------------------------------------------------------------------
408 -- Function get_contract
409 ------------------------------------------------------------------------
410 FUNCTION get_contract
411 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
412 p_date_earned IN Date)
413 RETURN VARCHAR2 IS
414 l_contract VARCHAR2(30);
415 BEGIN
416 BEGIN
417 SELECT cont.type
418 INTO l_contract
419 FROM per_contracts_f cont
420 ,per_all_assignments_f asg
421 WHERE asg.assignment_id = p_assignment_id
422 AND asg.contract_id = cont.contract_id
423 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
424 AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
425 EXCEPTION
426 WHEN OTHERS THEN
427 l_contract := 'N';
428 END;
429 RETURN l_contract;
430 END get_contract;
431 --------
432 ------------------------------------------------------------------------
433 -- Function get_contract_expiry_status
434 ------------------------------------------------------------------------
435 FUNCTION get_contract_expiry_status
436 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
437 p_date_earned IN Date)
438 RETURN VARCHAR2 IS
439 l_contract VARCHAR2(30);
440 l_expiry_date DATE;
441 l_expiry_status VARCHAR2(10);
442 BEGIN
443 l_expiry_status := 'N';
444 BEGIN
445
446 SELECT cont.type, fnd_date.canonical_to_date(cont.ctr_information2)
447 INTO l_contract, l_expiry_date
448 FROM per_contracts_f cont
449 ,per_all_assignments_f asg
450 WHERE asg.assignment_id = p_assignment_id
451 AND asg.contract_id = cont.contract_id
452 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
453 AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
454
455 IF l_expiry_date IS NOT NULL THEN
456 IF l_contract = 'FIXED_CONTRACT' AND TRUNC(l_expiry_date,'MM') <= TRUNC(p_date_earned,'MM') THEN
457 l_expiry_status := 'Y';
458 END IF;
459 END IF;
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 l_expiry_status := 'N';
464 END;
465 RETURN l_expiry_status;
466 END get_contract_expiry_status;
467 --------
468
469 ------------------------------------------------------------------------
470 -- Function get_termination_initiator
471 ------------------------------------------------------------------------
472 function get_termination_initiator
473 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
474 p_date_earned IN Date)
475 RETURN VARCHAR2 IS
476 l_leav_reason per_periods_of_service.leaving_reason%TYPE;
477 l_initiator VARCHAR2(20);
478 BEGIN
479 l_initiator :='EE';
480 BEGIN
481 SELECT pos.leaving_reason
482 INTO l_leav_reason
483 FROM per_all_assignments_f assign
484 ,per_periods_of_service pos
485 WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
486 AND assign.assignment_id = p_assignment_id
487 AND assign.period_of_service_id = pos.period_of_service_id;
488 SELECT NVL(i.value,'EE')
489 INTO l_initiator
490 FROM pay_user_column_instances_f i
491 ,pay_user_rows_f r
492 ,pay_user_columns c
493 ,pay_user_tables t
494 WHERE ((i.legislation_code = 'AE' AND i.business_group_id IS NULL) OR
495 (i.legislation_code IS NULL AND i.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
496 AND ((r.legislation_code = 'AE' AND r.business_group_id IS NULL) OR
500 AND UPPER(t.user_table_name) = UPPER('AE_TERMINATION_INITIATOR')
497 (r.legislation_code IS NULL AND r.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
498 AND c.legislation_code = 'AE'
499 AND t.legislation_code = 'AE'
501 AND t.user_table_id = r.user_table_id
502 AND t.user_table_id = c.user_table_id
503 AND r.row_low_range_or_name = l_leav_reason
504 AND r.user_row_id = i.user_row_id
505 AND UPPER(c.user_column_name) = UPPER('INITIATOR')
506 AND c.user_column_id = i.user_column_id
507 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
508 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
509 EXCEPTION
510 WHEN OTHERS THEN
511 l_initiator := 'EE';
512 END;
513 RETURN l_initiator;
514 END;
515
516 ------------------------------------------------------------------------
517 -- Function user_gratuity_formula_exists
518 ------------------------------------------------------------------------
519 FUNCTION user_gratuity_formula_exists
520 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
521 p_date_earned IN Date)
522 RETURN VARCHAR2 IS
523 cursor csr_get_formula_id is
524 select HOI2.org_information1
525 from hr_organization_units HOU
526 ,hr_organization_information HOI1
527 ,hr_organization_information HOI2
528 ,hr_soft_coding_keyflex HSCK
529 ,per_all_assignments_f PAA
530 where HOU.business_group_id = PAA.business_group_id
531 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
532 to_date('4712/12/31','YYYY/MM/DD'))
533 and HOU.organization_id = HOI1.organization_id
534 and HOI1.org_information_context = 'CLASS'
535 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
536 and HOI1.organization_id = HOI2.organization_id
537 and PAA.assignment_id = p_assignment_id
538 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
539 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
540 /*and HSCK.id_flex_num = 20
541 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
542 and hsck.segment1 = hou.organization_id
543 and HOI2.org_information_context = 'AE_GRATUITY_REF_FORMULA';
544 rec_get_formula_id csr_get_formula_id%ROWTYPE;
545 l_formula_id NUMBER;
546 l_indicator NUMBER;
547 BEGIN
548 OPEN csr_get_formula_id;
549 FETCH csr_get_formula_id INTO rec_get_formula_id;
550 l_formula_id := rec_get_formula_id.org_information1;
551 CLOSE csr_get_formula_id;
552 IF l_formula_id IS NULL THEN
553 l_indicator := 0;
554 ELSE
555 l_indicator := 1;
556 END IF;
557 RETURN l_indicator;
558 END;
559
560 PROCEDURE run_formula(p_formula_id IN NUMBER
561 ,p_effective_date IN DATE
562 ,p_inputs IN ff_exec.inputs_t
563 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
564 l_inputs ff_exec.inputs_t;
565 l_outputs ff_exec.outputs_t;
566 BEGIN
567 hr_utility.set_location('--In Formula ',20);
568 --
569 -- Initialize the formula
570 --
571 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
572 --
573 hr_utility.trace('after ff_exec');
574 -- Set up the input values
575 --
576 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
577 FOR i IN l_inputs.first..l_inputs.last LOOP
578 FOR j IN p_inputs.first..p_inputs.last LOOP
579 IF l_inputs(i).name = p_inputs(j).name THEN
580 l_inputs(i).value := p_inputs(j).value;
581 exit;
582 END IF;
583 END LOOP;
584 END LOOP;
585 END IF;
586 --
587 -- Run the formula
588 --
589 hr_utility.trace('about to exec');
590 ff_exec.run_formula(l_inputs,l_outputs);
591 --
592 -- Populate the output table
593 --
594 IF l_outputs.count > 0 and p_inputs.count > 0 then
595 FOR i IN l_outputs.first..l_outputs.last LOOP
596 FOR j IN p_outputs.first..p_outputs.last LOOP
597 IF l_outputs(i).name = p_outputs(j).name THEN
598 p_outputs(j).value := l_outputs(i).value;
599 exit;
600 END IF;
601 END LOOP;
602 END LOOP;
603 END IF;
604 EXCEPTION
605 /*WHEN hr_formula_error THEN
606 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
607 fnd_message.set_token('1', g_formula_name);
608 fnd_message.raise_error;*/
609 WHEN OTHERS THEN
610 raise;
611 --
612 END run_formula;
613
614 function run_gratuity_formula
615 (p_assignment_id IN NUMBER
616 ,p_date_earned IN DATE
617 ,p_business_group_id IN NUMBER
618 ,p_payroll_id IN NUMBER
619 ,p_payroll_action_id IN NUMBER
620 ,p_assignment_action_id IN NUMBER
621 ,p_tax_unit_id IN NUMBER
622 ,p_element_entry_id IN NUMBER
623 ,p_element_type_id IN NUMBER
624 ,p_original_entry_id IN NUMBER
625 ,p_monthly_gratuity OUT NOCOPY NUMBER
626 ,p_paid_gratuity OUT NOCOPY NUMBER
627 )
628 return NUMBER is
629 cursor csr_get_formula_id is
630 select HOI2.org_information2
631 from hr_organization_units HOU
632 ,hr_organization_information HOI1
633 ,hr_organization_information HOI2
634 ,hr_soft_coding_keyflex HSCK
635 ,per_all_assignments_f PAA
636 where HOU.business_group_id = PAA.business_group_id
640 and HOI1.org_information_context = 'CLASS'
637 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
638 to_date('4712/12/31','YYYY/MM/DD'))
639 and HOU.organization_id = HOI1.organization_id
641 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
642 and HOI1.organization_id = HOI2.organization_id
643 and PAA.assignment_id = p_assignment_id
644 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
645 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
646 /*and HSCK.id_flex_num = 20
647 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
648 and hsck.segment1 = hou.organization_id
649 and HOI2.org_information_context = 'AE_REFERENCE_FF';
650 l_formula_id NUMBER;
651 l_inputs ff_exec.inputs_t;
652 l_outputs ff_exec.outputs_t;
653 l_value NUMBER;
654 l_indicator NUMBER;
655 i NUMBER;
656 begin
657 l_indicator := 0;
658 i := 0;
659 open csr_get_formula_id;
660 fetch csr_get_formula_id into l_formula_id;
661 close csr_get_formula_id;
662 l_inputs(1).name := 'ASSIGNMENT_ID';
663 l_inputs(1).value := p_assignment_id;
664 l_inputs(2).name := 'DATE_EARNED';
665 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
666 l_inputs(3).name := 'BUSINESS_GROUP_ID';
667 l_inputs(3).value := p_business_group_id;
668 l_inputs(4).name := 'PAYROLL_ID';
669 l_inputs(4).value := p_payroll_id;
670 l_inputs(5).name := 'PAYROLL_ACTION_ID';
671 l_inputs(5).value := p_payroll_action_id;
672 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
673 l_inputs(6).value := p_assignment_action_id;
674 l_inputs(7).name := 'TAX_UNIT_ID';
675 l_inputs(7).value := p_tax_unit_id;
676 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
677 l_inputs(8).value := p_element_entry_id;
678 l_inputs(9).name := 'ELEMENT_TYPE_ID';
679 l_inputs(9).value := p_element_type_id;
680 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
681 l_inputs(10).value := p_original_entry_id;
682 l_outputs(1).name := 'MONTHLY_GRATUITY';
683 l_outputs(2).name := 'PAID_GRATUITY';
684 if l_formula_id is not null then
685 run_formula (l_formula_id
686 ,p_date_earned
687 ,l_inputs
688 ,l_outputs);
689 i := l_outputs.first;
690 --p_accrued_gratuity := NVL(l_outputs(i).value,0);
691 p_monthly_gratuity := NVL(l_outputs(i).value,0);
692 p_paid_gratuity := NVL(l_outputs(i+1).value,0);
693 l_indicator := 1;
694 else
695 l_indicator := 0;
696 end if;
697 RETURN l_indicator;
698
699 end run_gratuity_formula;
700
701 function run_gratuity_salary_formula
702 (p_assignment_id IN NUMBER
703 ,p_date_earned IN DATE
704 ,p_business_group_id IN NUMBER
705 ,p_payroll_id IN NUMBER
706 ,p_payroll_action_id IN NUMBER
707 ,p_assignment_action_id IN NUMBER
708 ,p_tax_unit_id IN NUMBER
709 ,p_element_entry_id IN NUMBER
710 ,p_element_type_id IN NUMBER
711 ,p_original_entry_id IN NUMBER
712
713 )
714 return NUMBER is
715 cursor csr_get_formula_id is
716 select HOI2.org_information3
717 from hr_organization_units HOU
718 ,hr_organization_information HOI1
719 ,hr_organization_information HOI2
720 ,hr_soft_coding_keyflex HSCK
721 ,per_all_assignments_f PAA
722 where HOU.business_group_id = PAA.business_group_id
723 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
724 to_date('4712/12/31','YYYY/MM/DD'))
725 and HOU.organization_id = HOI1.organization_id
726 and HOI1.org_information_context = 'CLASS'
727 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
728 and HOI1.organization_id = HOI2.organization_id
729 and PAA.assignment_id = p_assignment_id
730 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
731 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
732 /*and HSCK.id_flex_num = 20
733 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
734 and hsck.segment1 = hou.organization_id
735 and HOI2.org_information_context = 'AE_REFERENCE_FF';
736 l_formula_id NUMBER;
737 l_inputs ff_exec.inputs_t;
738 l_outputs ff_exec.outputs_t;
739 l_value NUMBER;
740 l_monthly_salary NUMBER;
741 i NUMBER;
742 begin
743 l_monthly_salary := 0;
744 i := 0;
745 open csr_get_formula_id;
746 fetch csr_get_formula_id into l_formula_id;
747 close csr_get_formula_id;
748 l_inputs(1).name := 'ASSIGNMENT_ID';
749 l_inputs(1).value := p_assignment_id;
750 l_inputs(2).name := 'DATE_EARNED';
751 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
752 l_inputs(3).name := 'BUSINESS_GROUP_ID';
753 l_inputs(3).value := p_business_group_id;
754 l_inputs(4).name := 'PAYROLL_ID';
755 l_inputs(4).value := p_payroll_id;
756 l_inputs(5).name := 'PAYROLL_ACTION_ID';
757 l_inputs(5).value := p_payroll_action_id;
758 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
759 l_inputs(6).value := p_assignment_action_id;
760 l_inputs(7).name := 'TAX_UNIT_ID';
761 l_inputs(7).value := p_tax_unit_id;
762 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
763 l_inputs(8).value := p_element_entry_id;
764 l_inputs(9).name := 'ELEMENT_TYPE_ID';
765 l_inputs(9).value := p_element_type_id;
766 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
770 l_outputs(1).name := 'MONTHLY_SALARY';
767 l_inputs(10).value := p_original_entry_id;
768
769
771 if l_formula_id is not null then
772 run_formula (l_formula_id
773 ,p_date_earned
774 ,l_inputs
775 ,l_outputs);
776 i := l_outputs.first;
777 l_monthly_salary := NVL(l_outputs(i).value,0);
778 end if;
779 RETURN l_monthly_salary;
780
781 end run_gratuity_salary_formula;
782
783 ------------------------------------------------------------------------
784 -- Function get_unauth_absence
785 -- Function for fetching unauthorised absences
786 ------------------------------------------------------------------------
787 FUNCTION get_unauth_absence
788 (p_assignment_id IN NUMBER
789 ,p_date_earned IN DATE
790 ,p_business_group_id IN NUMBER
791 --,p_period_start_date IN VARCHAR2
792 --,p_period_end_date IN VARCHAR2
793 )
794 RETURN NUMBER IS
795 l_days NUMBER;
796 BEGIN
797 l_days := 0;
798 /*OPEN csr_get_abs_days;
799 FETCH csr_get_abs_days INTO l_days;
800 CLOSE csr_get_abs_days;*/
801
802
803 SELECT SUM(paa.absence_days) --(NVL(paa.absence_days, (paa.DATE_END - paa.DATE_START))
804 INTO l_days
805 FROM per_absence_attendances paa
806 ,per_absence_attendance_types paat
807 ,per_all_assignments_f asg
808 WHERE paat.absence_category ='UL'
809 AND paat.business_group_id = paa.business_group_id
810 AND paat.business_group_id = p_business_group_id
811 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
812 AND paa.person_id = asg.person_id
813 AND asg.assignment_id = p_assignment_id
814 AND TRUNC(p_date_earned) BETWEEN asg.effective_start_date AND asg.effective_end_date
815 AND TRUNC(p_date_earned) >= TRUNC(paa.date_end,'MM') ;
816
817 RETURN NVL(l_days,0);
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 l_days := 0;
822 RETURN l_days;
823
824 END get_unauth_absence;
825
826 ------------------------------------------------------------------------
827 -- Function get_gratuity_basis
828 -- Function for fetching gratuity basis
829 ------------------------------------------------------------------------
830 FUNCTION get_gratuity_basis
831 (p_assignment_id IN NUMBER
832 ,p_date_earned IN DATE
833 )
834 RETURN VARCHAR2 IS
835 CURSOR csr_get_gratuity_basis IS
836 select NVL(HOI2.org_information1,'X')
837 from hr_organization_units HOU
838 ,hr_organization_information HOI1
839 ,hr_organization_information HOI2
840 ,hr_soft_coding_keyflex HSCK
841 ,per_all_assignments_f PAA
842 where HOU.business_group_id = PAA.business_group_id
843 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
844 to_date('4712/12/31','YYYY/MM/DD'))
845 and HOU.organization_id = HOI1.organization_id
846 and HOI1.org_information_context = 'CLASS'
847 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
848 and HOI1.organization_id = HOI2.organization_id
849 and PAA.assignment_id = p_assignment_id
850 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
851 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
852 and hsck.segment1 = hou.organization_id
853 and HOI2.org_information_context = 'AE_GRATUITY_DETAILS';
854 l_basis VARCHAR2(80);
855 BEGIN
856 l_basis := 'X';
857
858 OPEN csr_get_gratuity_basis;
859 FETCH csr_get_gratuity_basis INTO l_basis;
860 CLOSE csr_get_gratuity_basis;
861 RETURN l_basis;
862
863 END get_gratuity_basis;
864
865 end pay_ae_general;
[Home] [Help]