1 package body pay_ae_general as
2 /* $Header: pyaegenr.pkb 120.8.12020000.3 2013/02/07 11:56:51 bkeshary ship $ */
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 ------------------------------------------------------------------------
84 -- Function GET_PERSON_NATIONALITY
85 -- This function returns the person's nationality
86 ------------------------------------------------------------------------
87 function get_person_nationality
88 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
89 p_date_earned IN Date) return varchar2
90 is
91 l_nationality hr_lookups.meaning%type;
92 l_nationality_person per_all_people_f.nationality%type;
93 begin
94
95 -- hr_utility.trace_on(null,'bpk1');
96 -- hr_utility.set_location('Entering: ' , 10);
97
98 Select person_id
99 Into l_person_id
100 From PER_ALL_ASSIGNMENTS_F
101 Where ASSIGNMENT_ID = p_assignment_id
102 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
103 hr_utility.set_location('l_person_id: '||l_person_id , 10);
104
105 Select PER_INFORMATION18
106 Into l_nationality_person
107 From PER_ALL_PEOPLE_F
108 Where PERSON_ID = l_person_id
109 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
110
111 hr_utility.set_location('l_nationality_person: '||l_nationality_person , 10);
112
113 l_nationality := hr_general.decode_lookup('AE_NATIONALITY',l_nationality_person);
114 hr_utility.set_location('l_nationality: '||l_nationality , 10);
115
116 RETURN l_nationality;
117 END get_person_nationality;
118 -----------------------------------------------------------------------
119
120 ------------------------------------------------------------------------
121 -- Function GET_SECTOR
122 ------------------------------------------------------------------------
123 function get_sector (p_tax_unit_id IN NUMBER) return varchar2
124 IS
125 l_sector varchar2(1);
126 CURSOR GET_AE_SECTOR (l_tax_unit_id number) IS
127 SELECT org_information6
128 FROM hr_organization_information
129 WHERE organization_id = l_tax_unit_id
130 AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
131 begin
132 l_sector := null;
133 OPEN get_ae_sector (p_tax_unit_id);
134 FETCH get_ae_sector INTO l_sector;
135 CLOSE get_ae_sector;
136 If l_sector is null THEN
137 return 'N';
138 Else
139 return l_sector;
140 End If;
141 end get_sector;
142 ------------------------------------------------------------------------
143 -- Function GET_MESSAGE
144 -- This function is used to obtain a message.
145 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
146 -- If you want to set the value of a token called ELEMENT to Social Ins
147 -- the token parameter would be 'ELEMENT:Social Ins.'
148 ------------------------------------------------------------------------
149 function get_message
150 (p_product in varchar2
151 ,p_message_name in varchar2
152 ,p_token1 in varchar2 default null
153 ,p_token2 in varchar2 default null
154 ,p_token3 in varchar2 default null) return varchar2
155 is
156 l_message varchar2(2000);
157 l_token_name varchar2(20);
158 l_token_value varchar2(80);
159 l_colon_position number;
160 l_proc varchar2(72) ;
161 --
162 begin
163 g_package := 'pay_ae_general';
164 l_proc := g_package||'.get_message';
165 --
166 hr_utility.set_location('Entered '||l_proc,5);
167 hr_utility.set_location('. Message Name: '||p_message_name,40);
168 fnd_message.set_name(p_product, p_message_name);
169 if p_token1 is not null then
170 -- Obtain token 1 name and value
171 l_colon_position := instr(p_token1,':');
172 l_token_name := substr(p_token1,1,l_colon_position-1);
173 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
174 fnd_message.set_token(l_token_name, l_token_value);
175 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
176 end if;
177 if p_token2 is not null then
178 -- Obtain token 2 name and value
179 l_colon_position := instr(p_token2,':');
180 l_token_name := substr(p_token2,1,l_colon_position-1);
181 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
182 fnd_message.set_token(l_token_name, l_token_value);
183 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
184 end if;
185 if p_token3 is not null then
186 -- Obtain token 3 name and value
187 l_colon_position := instr(p_token3,':');
188 l_token_name := substr(p_token3,1,l_colon_position-1);
189 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
190 fnd_message.set_token(l_token_name, l_token_value);
191 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
192 end if;
193 l_message := substr(fnd_message.get,1,254);
194 hr_utility.set_location('leaving '||l_proc,100);
195 return l_message;
196 end get_message;
197 ------------------------------------------------------------------------
198 -- Function GET_TABLE_BANDS
199 -- This function is used to obtain User table's high and low values.
200 ------------------------------------------------------------------------
201 function get_table_bands
202 (p_Date_Earned IN DATE
203 ,p_table_name in varchar2
204 ,p_return_type in varchar2) return number
205 is
206 CURSOR csr_get_user_table_id(l_table_name varchar2) IS
207 SELECT user_table_id
208 FROM pay_user_tables
209 WHERE legislation_code='AE'
210 AND UPPER(user_table_name) = UPPER(l_table_name);
211 CURSOR csr_get_min_low (l_user_table_id NUMBER, l_effective_date DATE) IS
212 SELECT MIN(to_number(row_low_range_or_name))
213 FROM pay_user_rows_f
214 WHERE user_table_id = l_user_table_id
215 AND legislation_code = 'AE'
216 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
217 CURSOR csr_get_min_high (l_user_table_id number ,l_effective_date DATE ) IS
218 SELECT MIN(to_number(row_high_range))
219 FROM pay_user_rows_f
220 WHERE user_table_id = l_user_table_id
221 AND legislation_code = 'AE'
222 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
223 CURSOR csr_get_max_high (l_user_table_id number, l_effective_date DATE) IS
224 SELECT MAX(to_number(row_high_range))
225 FROM pay_user_rows_f
226 WHERE user_table_id = l_user_table_id
227 AND legislation_code = 'AE'
228 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
229 l_ret_val number(15,3);
230 l_table_id number(9);
231 l_proc varchar2(72) ;
232 --
233 begin
234 g_package := 'pay_ae_general';
235 l_proc := g_package||'.get_table_bands';
236 --
237 hr_utility.set_location('Entered '||l_proc,5);
238 -- Get the User Table ID
239 OPEN csr_get_user_table_id(p_table_name);
240 FETCH csr_get_user_table_id INTO l_table_id;
241 CLOSE csr_get_user_table_id;
242 --
243 IF p_return_type = 'MIN_LOW' THEN
244 OPEN csr_get_min_low (l_table_id, p_date_earned);
245 FETCH csr_get_min_low INTO l_ret_val;
246 CLOSE csr_get_min_low;
247 ELSIF p_return_type = 'MIN_HIGH' THEN
248 OPEN csr_get_min_high (l_table_id, p_date_earned);
249 FETCH csr_get_min_high INTO l_ret_val;
250 CLOSE csr_get_min_high;
251 ELSIF p_return_type = 'MAX_HIGH' THEN
252 OPEN csr_get_max_high (l_table_id, p_date_earned);
253 FETCH csr_get_max_high INTO l_ret_val;
254 CLOSE csr_get_max_high;
255 END IF;
256 return l_ret_val;
257 end get_table_bands;
258 -----------------------------------------------------------------------
259 -- Functions for EFT file
260 -----------------------------------------------------------------------
261 -----------------------------------------------------------------------------
262 -- GET_PARAMETER used in SQL to decode legislative parameters
263 -----------------------------------------------------------------------------
264 FUNCTION get_parameter(
265 p_parameter_string IN VARCHAR2
266 ,p_token IN VARCHAR2
267 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
268 IS
269 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
270 l_start_pos NUMBER;
271 l_delimiter varchar2(1):=' ';
272 l_proc VARCHAR2(60):= g_package||' get parameter ';
273 BEGIN
274 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
275 IF l_start_pos = 0 THEN
276 l_delimiter := '|';
277 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
278 end if;
279 IF l_start_pos <> 0 THEN
280 l_start_pos := l_start_pos + length(p_token||'=');
281 l_parameter := substr(p_parameter_string,
282 l_start_pos,
283 instr(p_parameter_string||' ',
284 ',',l_start_pos)
285 - l_start_pos);
286 IF p_segment_number IS NOT NULL THEN
287 l_parameter := ':'||l_parameter||':';
288 l_parameter := substr(l_parameter,
289 instr(l_parameter,':',1,p_segment_number)+1,
290 instr(l_parameter,':',1,p_segment_number+1) -1
291 - instr(l_parameter,':',1,p_segment_number));
292 END IF;
293 END IF;
294 RETURN l_parameter;
295 END get_parameter;
296 --
297 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
298 ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
299 CURSOR get_multiple_assgts IS
300 SELECT count(DISTINCT paf.assignment_id)
301 FROM per_all_assignments_f paf
302 ,per_assignment_status_types pas
303 WHERE paf.assignment_type = 'E'
304 AND paf.PERSON_ID = p_person_id
305 AND p_effective_date between effective_start_date and effective_end_date
306 AND paf.assignment_status_type_id = pas.assignment_status_type_id
307 AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
308 l_count NUMBER :=0;
309 BEGIN
310 OPEN get_multiple_assgts;
311 FETCH get_multiple_assgts INTO l_count;
312 CLOSE get_multiple_assgts;
313 IF l_count > 1 THEN
314 RETURN 'Y';
315 ELSE
316 RETURN 'N';
317 END IF;
318 END chk_multiple_assignments;
319 -----
320 function get_count RETURN NUMBER as
321 l_count NUMBER(15);
322 CURSOR CSR_AE_EFT_COUNT IS
323 SELECT COUNT(*)
324 FROM per_assignments_f paf
325 ,per_people_f pef
326 ,pay_pre_payments ppp
327 ,pay_assignment_actions paa
328 ,pay_payroll_actions ppa
329 WHERE paa.payroll_action_id =
330 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
331 AND paa.pre_payment_id = ppp.pre_payment_id
332 AND paa.payroll_action_id = ppa.payroll_action_id
333 AND paa.assignment_id = paf.assignment_id
334 AND paf.person_id = pef.person_id
335 AND ppp.value <> 0
336 AND ppa.effective_date BETWEEN paf.effective_start_date
337 AND paf.effective_end_date
338 AND ppa.effective_date BETWEEN pef.effective_start_date
339 AND pef.effective_end_date;
340 BEGIN
341 open CSR_AE_EFT_COUNT;
342 fetch CSR_AE_EFT_COUNT into l_count;
343 close CSR_AE_EFT_COUNT;
344 return l_count;
345 END get_count;
346 ---------
347 function get_total_sum return number as
348 l_total_sum pay_pre_payments.value%type;
349 CURSOR CSR_AE_EFT_SUM is
350 SELECT SUM(ppp.value)
351 FROM per_assignments_f paf
352 ,per_people_f pef
353 ,pay_pre_payments ppp
354 ,pay_assignment_actions paa
355 ,pay_payroll_actions ppa
356 WHERE paa.payroll_action_id =
357 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
358 AND paa.pre_payment_id = ppp.pre_payment_id
359 AND paa.payroll_action_id = ppa.payroll_action_id
360 AND paa.assignment_id = paf.assignment_id
361 AND paf.person_id = pef.person_id
362 AND ppp.value <> 0
363 AND ppa.effective_date BETWEEN paf.effective_start_date
364 AND paf.effective_end_date
365 AND ppa.effective_date BETWEEN pef.effective_start_date
366 AND pef.effective_end_date;
367 BEGIN
368 open CSR_AE_EFT_SUM;
369 fetch CSR_AE_EFT_SUM into l_total_sum;
370 close CSR_AE_EFT_SUM;
371 return l_total_sum;
372 END get_total_sum;
373 --------
374 ---------
375 function get_credit_sum return number as
376 l_credit_sum pay_pre_payments.value%type;
377 CURSOR CSR_AE_EFT_SUM is
378 SELECT SUM(ppp.value)
379 FROM per_assignments_f paf
380 ,per_people_f pef
381 ,pay_pre_payments ppp
382 ,pay_assignment_actions paa
383 ,pay_payroll_actions ppa
384 WHERE paa.payroll_action_id =
385 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
386 AND paa.pre_payment_id = ppp.pre_payment_id
387 AND paa.payroll_action_id = ppa.payroll_action_id
388 AND paa.assignment_id = paf.assignment_id
389 AND paf.person_id = pef.person_id
390 AND ppp.value > 0
391 AND ppa.effective_date BETWEEN paf.effective_start_date
392 AND paf.effective_end_date
393 AND ppa.effective_date BETWEEN pef.effective_start_date
394 AND pef.effective_end_date;
395 BEGIN
396 open CSR_AE_EFT_SUM;
397 fetch CSR_AE_EFT_SUM into l_credit_sum;
398 close CSR_AE_EFT_SUM;
399 return l_credit_sum;
400 END get_credit_sum;
401 --------
402 ---------
403 function get_debit_sum return number as
404 l_debit_sum pay_pre_payments.value%type;
405 CURSOR CSR_AE_EFT_SUM is
406 SELECT SUM(ppp.value)
407 FROM per_assignments_f paf
408 ,per_people_f pef
409 ,pay_pre_payments ppp
410 ,pay_assignment_actions paa
411 ,pay_payroll_actions ppa
412 WHERE paa.payroll_action_id =
413 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
414 AND paa.pre_payment_id = ppp.pre_payment_id
415 AND paa.payroll_action_id = ppa.payroll_action_id
416 AND paa.assignment_id = paf.assignment_id
417 AND paf.person_id = pef.person_id
418 AND ppp.value < 0
419 AND ppa.effective_date BETWEEN paf.effective_start_date
420 AND paf.effective_end_date
421 AND ppa.effective_date BETWEEN pef.effective_start_date
422 AND pef.effective_end_date;
423 BEGIN
424 open CSR_AE_EFT_SUM;
425 fetch CSR_AE_EFT_SUM into l_debit_sum;
426 close CSR_AE_EFT_SUM;
427 return l_debit_sum;
428 END get_debit_sum;
429 --------
430 --------
431 function chk_tran_code (p_value IN VARCHAR2) return VARCHAR2 as
432 l_flag varchar2(1) := null;
433 BEGIN
434 If p_value <> 0 then
435 If p_value < 0 then
436 l_flag := 'N';
437 elsif p_value > 0 then
438 l_flag := 'Y';
439 end if;
440 End if;
441 Return nvl(l_flag,' ');
442 End chk_tran_code;
443 -----------
444 --------
445 ------------------------------------------------------------------------
446 -- Function get_contract
447 ------------------------------------------------------------------------
448 FUNCTION get_contract
449 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
450 p_date_earned IN Date)
451 RETURN VARCHAR2 IS
452 l_contract VARCHAR2(30);
453 BEGIN
454 BEGIN
455 SELECT cont.type
456 INTO l_contract
457 FROM per_contracts_f cont
458 ,per_all_assignments_f asg
459 WHERE asg.assignment_id = p_assignment_id
460 AND asg.contract_id = cont.contract_id
461 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
462 AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
463 EXCEPTION
464 WHEN OTHERS THEN
465 l_contract := 'N';
466 END;
467 RETURN l_contract;
468 END get_contract;
469 --------
470
471 --------------------------------------------------------------------------------------
472
473 -- Function get_probation_period
474 --------------------------------------------------------------------------------------
475
476 FUNCTION get_probation_period
477 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
478 p_date_earned IN Date)
479 RETURN date IS
480 l_date_probation_end date;
481 BEGIN
482 BEGIN
483 SELECT DATE_PROBATION_END
484 INTO l_date_probation_end
485 FROM per_all_assignments_f asg
486 WHERE asg.assignment_id = p_assignment_id
487 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date;
488 EXCEPTION
489 WHEN OTHERS THEN
490 null;
491 END;
492 RETURN l_date_probation_end;
493 END get_probation_period;
494
495 -------------------------------------------------------------------------------------
496 -------
497
498 ------------------------------------------------------------------------
499 -- Function get_contract_expiry_status
500 ------------------------------------------------------------------------
501 FUNCTION get_contract_expiry_status
502 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
503 p_date_earned IN Date)
504 RETURN VARCHAR2 IS
505 l_contract VARCHAR2(30);
506 l_expiry_date DATE;
507 l_expiry_status VARCHAR2(10);
508 BEGIN
509 l_expiry_status := 'N';
510 BEGIN
511
512 SELECT cont.type, fnd_date.canonical_to_date(cont.ctr_information2)
513 INTO l_contract, l_expiry_date
514 FROM per_contracts_f cont
515 ,per_all_assignments_f asg
516 WHERE asg.assignment_id = p_assignment_id
517 AND asg.contract_id = cont.contract_id
518 AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
519 AND p_date_earned BETWEEN cont.effective_start_date AND cont.effective_end_date;
520
521 IF l_expiry_date IS NOT NULL THEN
522 IF l_contract = 'FIXED_CONTRACT' AND TRUNC(l_expiry_date,'MM') <= TRUNC(p_date_earned,'MM') THEN
523 l_expiry_status := 'Y';
524 END IF;
525 END IF;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 l_expiry_status := 'N';
530 END;
531 RETURN l_expiry_status;
532 END get_contract_expiry_status;
533 --------
534
535 ------------------------------------------------------------------------
536 -- Function get_termination_initiator
537 ------------------------------------------------------------------------
538 function get_termination_initiator
539 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
540 p_date_earned IN Date)
541 RETURN VARCHAR2 IS
542 l_leav_reason per_periods_of_service.leaving_reason%TYPE;
543 l_initiator VARCHAR2(20);
544 BEGIN
545 l_initiator :='EE';
546 BEGIN
547 SELECT pos.leaving_reason
548 INTO l_leav_reason
549 FROM per_all_assignments_f assign
550 ,per_periods_of_service pos
551 WHERE p_date_earned BETWEEN assign.effective_start_date AND assign.effective_end_date
552 AND assign.assignment_id = p_assignment_id
553 AND assign.period_of_service_id = pos.period_of_service_id;
554 SELECT NVL(i.value,'EE')
555 INTO l_initiator
556 FROM pay_user_column_instances_f i
557 ,pay_user_rows_f r
558 ,pay_user_columns c
559 ,pay_user_tables t
560 WHERE ((i.legislation_code = 'AE' AND i.business_group_id IS NULL) OR
561 (i.legislation_code IS NULL AND i.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
562 AND ((r.legislation_code = 'AE' AND r.business_group_id IS NULL) OR
563 (r.legislation_code IS NULL AND r.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')))
564 AND c.legislation_code = 'AE'
565 AND t.legislation_code = 'AE'
566 AND UPPER(t.user_table_name) = UPPER('AE_TERMINATION_INITIATOR')
567 AND t.user_table_id = r.user_table_id
568 AND t.user_table_id = c.user_table_id
569 AND r.row_low_range_or_name = l_leav_reason
570 AND r.user_row_id = i.user_row_id
571 AND UPPER(c.user_column_name) = UPPER('INITIATOR')
572 AND c.user_column_id = i.user_column_id
573 AND p_date_earned BETWEEN r.effective_start_date AND r.effective_end_date
574 AND p_date_earned BETWEEN i.effective_start_date AND i.effective_end_date;
575 EXCEPTION
576 WHEN OTHERS THEN
577 l_initiator := 'EE';
578 END;
579 RETURN l_initiator;
580 END;
581
582 ------------------------------------------------------------------------
583 -- Function user_gratuity_formula_exists
584 ------------------------------------------------------------------------
585 FUNCTION user_gratuity_formula_exists
586 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
587 p_date_earned IN Date)
588 RETURN VARCHAR2 IS
589 cursor csr_get_formula_id is
590 select HOI2.org_information1
591 from hr_organization_units HOU
592 ,hr_organization_information HOI1
593 ,hr_organization_information HOI2
594 ,hr_soft_coding_keyflex HSCK
595 ,per_all_assignments_f PAA
596 where HOU.business_group_id = PAA.business_group_id
597 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
598 to_date('4712/12/31','YYYY/MM/DD'))
599 and HOU.organization_id = HOI1.organization_id
600 and HOI1.org_information_context = 'CLASS'
601 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
602 and HOI1.organization_id = HOI2.organization_id
603 and PAA.assignment_id = p_assignment_id
604 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
605 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
606 /*and HSCK.id_flex_num = 20
607 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
608 and hsck.segment1 = hou.organization_id
609 and HOI2.org_information_context = 'AE_GRATUITY_REF_FORMULA';
610 rec_get_formula_id csr_get_formula_id%ROWTYPE;
611 l_formula_id NUMBER;
612 l_indicator NUMBER;
613 BEGIN
614 OPEN csr_get_formula_id;
615 FETCH csr_get_formula_id INTO rec_get_formula_id;
616 l_formula_id := rec_get_formula_id.org_information1;
617 CLOSE csr_get_formula_id;
618 IF l_formula_id IS NULL THEN
619 l_indicator := 0;
620 ELSE
621 l_indicator := 1;
622 END IF;
623 RETURN l_indicator;
624 END;
625
626 PROCEDURE run_formula(p_formula_id IN NUMBER
627 ,p_effective_date IN DATE
628 ,p_inputs IN ff_exec.inputs_t
629 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
630 l_inputs ff_exec.inputs_t;
631 l_outputs ff_exec.outputs_t;
632 BEGIN
633 hr_utility.set_location('--In Formula ',20);
634 --
635 -- Initialize the formula
636 --
637 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
638 --
639 hr_utility.trace('after ff_exec');
640 -- Set up the input values
641 --
642 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
643 FOR i IN l_inputs.first..l_inputs.last LOOP
644 FOR j IN p_inputs.first..p_inputs.last LOOP
645 IF l_inputs(i).name = p_inputs(j).name THEN
646 l_inputs(i).value := p_inputs(j).value;
647 exit;
648 END IF;
649 END LOOP;
650 END LOOP;
651 END IF;
652 --
653 -- Run the formula
654 --
655 hr_utility.trace('about to exec');
656 ff_exec.run_formula(l_inputs,l_outputs);
657 --
658 -- Populate the output table
659 --
660 IF l_outputs.count > 0 and p_inputs.count > 0 then
661 FOR i IN l_outputs.first..l_outputs.last LOOP
662 FOR j IN p_outputs.first..p_outputs.last LOOP
663 IF l_outputs(i).name = p_outputs(j).name THEN
664 p_outputs(j).value := l_outputs(i).value;
665 exit;
666 END IF;
667 END LOOP;
668 END LOOP;
669 END IF;
670 EXCEPTION
671 /*WHEN hr_formula_error THEN
672 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
673 fnd_message.set_token('1', g_formula_name);
674 fnd_message.raise_error;*/
675 WHEN OTHERS THEN
676 raise;
677 --
678 END run_formula;
679
680 function run_gratuity_formula
681 (p_assignment_id IN NUMBER
682 ,p_date_earned IN DATE
683 ,p_business_group_id IN NUMBER
684 ,p_payroll_id IN NUMBER
685 ,p_payroll_action_id IN NUMBER
686 ,p_assignment_action_id IN NUMBER
687 ,p_tax_unit_id IN NUMBER
688 ,p_element_entry_id IN NUMBER
689 ,p_element_type_id IN NUMBER
690 ,p_original_entry_id IN NUMBER
691 ,p_monthly_gratuity OUT NOCOPY NUMBER
692 ,p_paid_gratuity OUT NOCOPY NUMBER
693 )
694 return NUMBER is
695 cursor csr_get_formula_id is
696 select HOI2.org_information2
697 from hr_organization_units HOU
698 ,hr_organization_information HOI1
699 ,hr_organization_information HOI2
700 ,hr_soft_coding_keyflex HSCK
701 ,per_all_assignments_f PAA
702 where HOU.business_group_id = PAA.business_group_id
703 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
704 to_date('4712/12/31','YYYY/MM/DD'))
705 and HOU.organization_id = HOI1.organization_id
706 and HOI1.org_information_context = 'CLASS'
707 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
708 and HOI1.organization_id = HOI2.organization_id
709 and PAA.assignment_id = p_assignment_id
710 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
711 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
712 /*and HSCK.id_flex_num = 20
713 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
714 and hsck.segment1 = hou.organization_id
715 and HOI2.org_information_context = 'AE_REFERENCE_FF';
716 l_formula_id NUMBER;
717 l_inputs ff_exec.inputs_t;
718 l_outputs ff_exec.outputs_t;
719 l_value NUMBER;
720 l_indicator NUMBER;
721 i NUMBER;
722 begin
723 l_indicator := 0;
724 i := 0;
725 open csr_get_formula_id;
726 fetch csr_get_formula_id into l_formula_id;
727 close csr_get_formula_id;
728 l_inputs(1).name := 'ASSIGNMENT_ID';
729 l_inputs(1).value := p_assignment_id;
730 l_inputs(2).name := 'DATE_EARNED';
731 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
732 l_inputs(3).name := 'BUSINESS_GROUP_ID';
733 l_inputs(3).value := p_business_group_id;
734 l_inputs(4).name := 'PAYROLL_ID';
735 l_inputs(4).value := p_payroll_id;
736 l_inputs(5).name := 'PAYROLL_ACTION_ID';
737 l_inputs(5).value := p_payroll_action_id;
738 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
739 l_inputs(6).value := p_assignment_action_id;
740 l_inputs(7).name := 'TAX_UNIT_ID';
741 l_inputs(7).value := p_tax_unit_id;
742 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
743 l_inputs(8).value := p_element_entry_id;
744 l_inputs(9).name := 'ELEMENT_TYPE_ID';
745 l_inputs(9).value := p_element_type_id;
746 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
747 l_inputs(10).value := p_original_entry_id;
748 l_outputs(1).name := 'MONTHLY_GRATUITY';
749 l_outputs(2).name := 'PAID_GRATUITY';
750 if l_formula_id is not null then
751 run_formula (l_formula_id
752 ,p_date_earned
753 ,l_inputs
754 ,l_outputs);
755 i := l_outputs.first;
756 --p_accrued_gratuity := NVL(l_outputs(i).value,0);
757 p_monthly_gratuity := NVL(l_outputs(i).value,0);
758 p_paid_gratuity := NVL(l_outputs(i+1).value,0);
759 l_indicator := 1;
760 else
761 l_indicator := 0;
762 end if;
763 RETURN l_indicator;
764
765 end run_gratuity_formula;
766
767 function run_gratuity_salary_formula
768 (p_assignment_id IN NUMBER
769 ,p_date_earned IN DATE
770 ,p_business_group_id IN NUMBER
771 ,p_payroll_id IN NUMBER
772 ,p_payroll_action_id IN NUMBER
773 ,p_assignment_action_id IN NUMBER
774 ,p_tax_unit_id IN NUMBER
775 ,p_element_entry_id IN NUMBER
776 ,p_element_type_id IN NUMBER
777 ,p_original_entry_id IN NUMBER
778
779 )
780 return NUMBER is
781 cursor csr_get_formula_id is
782 select HOI2.org_information3
783 from hr_organization_units HOU
784 ,hr_organization_information HOI1
785 ,hr_organization_information HOI2
786 ,hr_soft_coding_keyflex HSCK
787 ,per_all_assignments_f PAA
788 where HOU.business_group_id = PAA.business_group_id
789 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
790 to_date('4712/12/31','YYYY/MM/DD'))
791 and HOU.organization_id = HOI1.organization_id
792 and HOI1.org_information_context = 'CLASS'
793 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
794 and HOI1.organization_id = HOI2.organization_id
795 and PAA.assignment_id = p_assignment_id
796 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
797 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
798 /*and HSCK.id_flex_num = 20
799 and decode(HSCK.id_flex_num,20,to_number(HSCK.segment1),-9999) = HOU.organization_id*/
800 and hsck.segment1 = hou.organization_id
801 and HOI2.org_information_context = 'AE_REFERENCE_FF';
802 l_formula_id NUMBER;
803 l_inputs ff_exec.inputs_t;
804 l_outputs ff_exec.outputs_t;
805 l_value NUMBER;
806 l_monthly_salary NUMBER;
807 i NUMBER;
808 begin
809 l_monthly_salary := 0;
810 i := 0;
811 open csr_get_formula_id;
812 fetch csr_get_formula_id into l_formula_id;
813 close csr_get_formula_id;
814 l_inputs(1).name := 'ASSIGNMENT_ID';
815 l_inputs(1).value := p_assignment_id;
816 l_inputs(2).name := 'DATE_EARNED';
817 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
818 l_inputs(3).name := 'BUSINESS_GROUP_ID';
819 l_inputs(3).value := p_business_group_id;
820 l_inputs(4).name := 'PAYROLL_ID';
821 l_inputs(4).value := p_payroll_id;
822 l_inputs(5).name := 'PAYROLL_ACTION_ID';
823 l_inputs(5).value := p_payroll_action_id;
824 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
825 l_inputs(6).value := p_assignment_action_id;
826 l_inputs(7).name := 'TAX_UNIT_ID';
827 l_inputs(7).value := p_tax_unit_id;
828 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
829 l_inputs(8).value := p_element_entry_id;
830 l_inputs(9).name := 'ELEMENT_TYPE_ID';
831 l_inputs(9).value := p_element_type_id;
832 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
833 l_inputs(10).value := p_original_entry_id;
834
835
836 l_outputs(1).name := 'MONTHLY_SALARY';
837 if l_formula_id is not null then
838 run_formula (l_formula_id
839 ,p_date_earned
840 ,l_inputs
841 ,l_outputs);
842 i := l_outputs.first;
843 l_monthly_salary := NVL(l_outputs(i).value,0);
844 end if;
845 RETURN l_monthly_salary;
846
847 end run_gratuity_salary_formula;
848
849 ------------------------------------------------------------------------
850 -- Function get_unauth_absence
851 -- Function for fetching unauthorised absences
852 ------------------------------------------------------------------------
853 FUNCTION get_unauth_absence
854 (p_assignment_id IN NUMBER
855 ,p_date_earned IN DATE
856 ,p_business_group_id IN NUMBER
857 --,p_period_start_date IN VARCHAR2
858 --,p_period_end_date IN VARCHAR2
859 )
860 RETURN NUMBER IS
861 l_days NUMBER;
862 BEGIN
863 l_days := 0;
864 /*OPEN csr_get_abs_days;
865 FETCH csr_get_abs_days INTO l_days;
866 CLOSE csr_get_abs_days;*/
867
868
869 SELECT SUM(paa.absence_days) --(NVL(paa.absence_days, (paa.DATE_END - paa.DATE_START))
870 INTO l_days
871 FROM per_absence_attendances paa
872 ,per_absence_attendance_types paat
873 ,per_all_assignments_f asg
874 WHERE paat.absence_category ='UL'
875 AND paat.business_group_id = paa.business_group_id
876 AND paat.business_group_id = p_business_group_id
877 AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
878 AND paa.person_id = asg.person_id
879 AND asg.assignment_id = p_assignment_id
880 AND TRUNC(p_date_earned) BETWEEN asg.effective_start_date AND asg.effective_end_date
881 AND TRUNC(p_date_earned) >= TRUNC(paa.date_end,'MM') ;
882
883 RETURN NVL(l_days,0);
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 l_days := 0;
888 RETURN l_days;
889
890 END get_unauth_absence;
891
892 ------------------------------------------------------------------------
893 -- Function get_gratuity_basis
894 -- Function for fetching gratuity basis
895 ------------------------------------------------------------------------
896 FUNCTION get_gratuity_basis
897 (p_assignment_id IN NUMBER
898 ,p_date_earned IN DATE
899 )
900 RETURN VARCHAR2 IS
901 CURSOR csr_get_gratuity_basis IS
902 select NVL(HOI2.org_information1,'X')
903 from hr_organization_units HOU
904 ,hr_organization_information HOI1
905 ,hr_organization_information HOI2
906 ,hr_soft_coding_keyflex HSCK
907 ,per_all_assignments_f PAA
908 where HOU.business_group_id = PAA.business_group_id
909 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
910 to_date('4712/12/31','YYYY/MM/DD'))
911 and HOU.organization_id = HOI1.organization_id
912 and HOI1.org_information_context = 'CLASS'
913 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
914 and HOI1.organization_id = HOI2.organization_id
915 and PAA.assignment_id = p_assignment_id
916 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
917 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
918 and hsck.segment1 = hou.organization_id
919 and HOI2.org_information_context = 'AE_GRATUITY_DETAILS';
920 l_basis VARCHAR2(80);
921 BEGIN
922 l_basis := 'X';
923
924 OPEN csr_get_gratuity_basis;
925 FETCH csr_get_gratuity_basis INTO l_basis;
926 CLOSE csr_get_gratuity_basis;
927 RETURN l_basis;
928
929 END get_gratuity_basis;
930
931 ------------------------------------------------------------------------
932 -- Function for returning contributory wage of employees over 50 years
933 ------------------------------------------------------------------------
934 FUNCTION get_cont_wage_emp_50 (
935 p_assignment_action_id IN NUMBER
936 ,p_assignment_id IN NUMBER
937 ,p_date_earned IN DATE
938 ,p_pct_value IN NUMBER
939 ,p_subject_to_gosi IN NUMBER)
940 RETURN NUMBER AS
941
942 CURSOR csr_get_def_bal_id (p_def_bal_name IN VARCHAR2) IS
943 SELECT u.creator_id
944 FROM ff_user_entities u,
945 ff_database_items d
946 WHERE d.user_name = p_def_bal_name
947 AND u.user_entity_id = d.user_entity_id
948 AND u.legislation_code = 'AE'
949 AND u.business_group_id is null
950 AND u.creator_type = 'B';
951
952 CURSOR csr_get_assact_id IS
953 SELECT paa.assignment_action_id
954 ,ppa.date_earned
955 FROM pay_assignment_actions paa
956 ,pay_payroll_actions ppa
957 ,pay_run_results prr
958 ,pay_element_types_f pet
959 WHERE paa.assignment_id = p_assignment_id
960 AND paa.assignment_action_id = prr.assignment_action_id
961 AND paa.payroll_action_id = ppa.payroll_action_id
962 AND paa.action_status = 'C'
963 AND ppa.action_status = 'C'
964 AND ppa.action_type in ('R','Q')
965 AND prr.element_type_id = pet.element_type_id
966 AND pet.element_name ='GOSI'
967 AND p_date_earned between pet.effective_start_date and pet.effective_end_date
968 AND prr.status = 'P'
969 AND paa.assignment_action_id < p_assignment_action_id
970 --AND ppa.date_earned >= ADD_MONTHS(TRUNC(p_date_earned,'YYYY'),-12)
971 ORDER BY paa.assignment_action_id DESC;
972
973 rec_get_assact_id csr_get_assact_id%ROWTYPE;
974
975 l_prev_salary NUMBER;
976 l_subject_gosi_id NUMBER;
977 l_old_assact_id NUMBER;
978 l_prev_assact_id NUMBER;
979 l_ref_earnings_id NUMBER;
980 l_diff_exist NUMBER;
981 l_diff_salary NUMBER;
982 l_old_wage NUMBER;
983 l_c_wage_1 NUMBER;
984 l_c_wage_2 NUMBER;
985 l_old_date_earned DATE;
986
987 BEGIN
988 hr_utility.set_location('Entered pay_ae_general',10);
989 l_diff_exist := 0;
990 l_ref_earnings_id := NULL;
991 OPEN csr_get_def_bal_id( 'CONTRIBUTORY_SALARY_ASG_YTD');
992 FETCH csr_get_def_bal_id INTO l_ref_earnings_id;
993 hr_utility.set_location('l_ref_earnings_id'|| l_ref_earnings_id,10);
994 CLOSE csr_get_def_bal_id;
995
996 l_subject_gosi_id := NULL;
997 OPEN csr_get_def_bal_id( 'SUBJECT_TO_SOCIAL_INSURANCE_ASG_RUN');
998 FETCH csr_get_def_bal_id INTO l_subject_gosi_id;
999 hr_utility.set_location('l_subject_gosi_id'|| l_subject_gosi_id,10);
1000 CLOSE csr_get_def_bal_id;
1001
1002 l_old_assact_id := NULL;
1003
1004 /*check if the earlier run was in previous year and get the assignment action_id*/
1005 OPEN csr_get_assact_id;
1006 LOOP
1007 FETCH csr_get_assact_id INTO rec_get_assact_id;
1008 EXIT WHEN csr_get_assact_id%NOTFOUND;
1009 hr_utility.set_location('rec_get_assact_id.assignment_action_id'||rec_get_assact_id.assignment_action_id,10);
1010 IF l_old_assact_id IS NULL THEN
1011 IF TRUNC(rec_get_assact_id.date_earned,'YYYY') < TRUNC(p_date_earned, 'YYYY') THEN
1012 hr_utility.set_location('rec_get_assact_id.assignment_action_id'||rec_get_assact_id.assignment_action_id,20);
1013 l_old_assact_id := rec_get_assact_id.assignment_action_id;
1014 l_old_date_earned := rec_get_assact_id.date_earned;
1015 l_old_wage := pay_balance_pkg.get_value(l_ref_earnings_id,l_old_assact_id);
1016 hr_utility.set_location('l_old_assact_id'|| l_old_assact_id,10);
1017 hr_utility.set_location('l_old_date_earned'|| l_old_date_earned,10);
1018 hr_utility.set_location('l_old_wage'|| l_old_wage,10);
1019 ELSE
1020 /*Exit if the earlier run is in the same year. This indicates there is no need to calculate contributory wage*/
1021 EXIT;
1022 END IF;
1023 END IF;
1024 /*l_prev_salary := pay_balance_pkg.get_value(l_subject_gosi_id,rec_get_assact_id.assignment_action_id);*/
1025 /*Check if there is any salary change in the previous year*/
1026 IF TRUNC(l_old_date_earned ,'YYYY') = TRUNC(rec_get_assact_id.date_earned,'YYYY') THEN
1027 /*Bug No6976224*/
1028 /*IF l_prev_salary <> p_subject_to_gosi THEN*/
1029 hr_utility.set_location('l_old_wage'|| l_old_wage,10);
1030 hr_utility.set_location('p_subject_to_gosi'|| p_subject_to_gosi,10);
1031 IF l_old_wage <> p_subject_to_gosi THEN
1032 l_diff_exist := 1;
1033 EXIT;
1034 END IF;
1035 END IF;
1036
1037 END LOOP;
1038 CLOSE csr_get_assact_id;
1039
1040 IF (l_diff_exist = 1) AND (l_old_assact_id IS NOT NULL) THEN
1041 /*Calculate contributory wage if there is any change*/
1042 hr_utility.set_location('p_subject_to_gosi'|| p_subject_to_gosi,10);
1043 hr_utility.set_location('l_prev_salary'|| l_prev_salary,10);
1044 l_diff_salary := p_subject_to_gosi - l_prev_salary;
1045 hr_utility.set_location('l_diff_salary'|| l_diff_salary,10);
1046 /*Bug No 6976224*/
1047 /*l_c_wage_1 := ((p_pct_value/100) * (l_diff_salary)) + l_old_wage;*/
1048 l_c_wage_1 := ((p_pct_value/100) * (l_old_wage)) + l_old_wage;
1049 l_c_wage_2 := p_subject_to_gosi;
1050 hr_utility.set_location('l_c_wage_1'|| l_c_wage_1,10);
1051 hr_utility.set_location('l_c_wage_2'|| l_c_wage_2,10);
1052
1053 RETURN (LEAST(l_c_wage_1, l_c_wage_2));
1054 END IF;
1055
1056 RETURN(nvl(l_old_wage,0));
1057
1058 END get_cont_wage_emp_50;
1059
1060 end pay_ae_general;
[Home] [Help]