[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_TERMINATION_PKG
Source
1 PACKAGE BODY pay_in_termination_pkg as
2 /* $Header: pyinterm.pkb 120.24 2007/09/25 11:48:57 rsaharay noship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_termination_pkg.' ;
5 g_debug BOOLEAN ;
6 g_legislation_code CONSTANT VARCHAR2(2) := 'IN';
7
8 g_assignment_id per_assignments_f.assignment_id%TYPE;
9 g_payroll_id pay_payrolls_f.payroll_id%TYPE;
10 g_hire_date per_people_f.start_date%TYPE;
11 g_notified_date per_periods_of_service.NOTIFIED_TERMINATION_DATE%TYPE;
12
13 g_notice_et CONSTANT VARCHAR2(80):= 'Notice Period Information';
14 g_notice_cn CONSTANT VARCHAR2(80):= 'Notice Period Pay';
15 g_retrenchment_et CONSTANT VARCHAR2(80):= 'Retrenchment Compensation Information';
16 g_retrenchment_cn CONSTANT VARCHAR2(80):= 'Retrenchment';
17 g_vrs_et CONSTANT VARCHAR2(80):= 'Voluntary Retirement Information';
18 g_vrs_cn CONSTANT VARCHAR2(80):= 'Voluntary Retirement';
19 g_pension_et CONSTANT VARCHAR2(80):= 'Commuted Pension Information';
20 g_pension_cn CONSTANT VARCHAR2(80):= 'Commuted Pension';
21 g_pf_et CONSTANT VARCHAR2(80):= 'PF Settlement Information';
22 g_pf_cn CONSTANT VARCHAR2(80):= 'PF Settlement';
23 g_loan_et CONSTANT VARCHAR2(80):= 'Loan Recovery';
24 g_loan_cn CONSTANT VARCHAR2(80):= 'Loan Recovery';
25 g_gratuity_et CONSTANT VARCHAR2(80):= 'Gratuity Information';
26 g_gratuity_cn CONSTANT VARCHAR2(80):= 'Gratuity';
27
28 TYPE t_input_values_rec IS RECORD
29 (input_name pay_input_values_f.name%TYPE
30 ,input_value_id pay_input_values_f.input_value_id%TYPE);
31
32 TYPE t_entry_values_rec IS RECORD
33 (entry_value pay_element_entry_values.screen_entry_value%TYPE);
34
35 TYPE t_input_values_tab IS TABLE OF t_input_values_rec
36 INDEX BY BINARY_INTEGER;
37
38 TYPE t_entry_values_tab IS TABLE OF t_entry_values_rec
39 INDEX BY BINARY_INTEGER;
40
41 --------------------------------------------------------------------------
42 -- Name : check_notice_period --
43 -- Type : Procedure --
44 -- Access : Public --
45 -- Description : Internal Proc to be called for validation --
46 -- Parameters : --
47 -- IN : p_organization_id NUMBER --
48 -- p_org_info_type_code VARCHAR2 --
49 -- p_emp_category VARCHAR2 --
50 -- p_notice_period VARCHAR2 --
51 -- p_calling_procedure VARCHAR2 --
52 -- OUT : p_message_name VARCHAR2 --
53 -- p_token_name pay_in_utils.char_tab_type --
54 -- p_token_value pay_in_utils.char_tab_type --
55 -- Change History : --
56 --------------------------------------------------------------------------
57 -- Rev# Date Userid Description --
58 --------------------------------------------------------------------------
59 -- 1.0 27-Oct-04 statkar Created this procedure --
60 -- 1.1 23-Nov-04 rpalli Modified the "check for uniqueness" --
61 -- functionality to work for updations --
62 -- Bug Fix :3951465 --
63 --------------------------------------------------------------------------
64 PROCEDURE check_notice_period
65 (p_organization_id IN NUMBER
66 ,p_org_information_id IN NUMBER
67 ,p_org_info_type_code IN VARCHAR2
68 ,p_emp_category IN VARCHAR2
69 ,p_notice_period IN VARCHAR2
70 ,p_calling_procedure IN VARCHAR2
71 ,p_message_name OUT NOCOPY VARCHAR2
72 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
73 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
74 IS
75
76 l_procedure VARCHAR2(100);
77 l_message VARCHAR2(255);
78 l_dummy VARCHAR2(1);
79
80 CURSOR c_dup_comb IS
81 SELECT 'X'
82 FROM hr_organization_information
83 WHERE organization_id = p_organization_id
84 AND org_information_context = p_org_info_type_code
85 AND org_information1 = p_emp_category
86 AND org_information_id <> NVL(p_org_information_id,0);
87
88 BEGIN
89 l_procedure := g_package ||'check_pt_frequency';
90 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
91 p_message_name := 'SUCCESS';
92 pay_in_utils.null_message(p_token_name, p_token_value);
93
94 --
95 -- Validations are as follows:
96 --
97 -- 1. Check for mandatory parameters
98 -- 2. Check for lookups
99 -- 3. Check for uniqueness
100 -- 4. Check if Start Date > End Date
101 --
102 --
103 IF p_emp_category IS NULL THEN
104 p_message_name := 'HR_7207_API_MANDATORY_ARG';
105 p_token_name(1) := 'API_NAME';
106 p_token_value(1) := p_calling_procedure;
107 p_token_name(2) := 'ARGUMENT';
108 p_token_value(2) := 'P_EMP_CATEGORY';
109 RETURN;
110 END IF;
111 pay_in_utils.set_location(g_debug,l_procedure,20);
112
113 IF p_notice_period IS NULL THEN
114 p_message_name := 'HR_7207_API_MANDATORY_ARG';
115 p_token_name(1) := 'API_NAME';
116 p_token_value(1) := p_calling_procedure;
117 p_token_name(2) := 'ARGUMENT';
118 p_token_value(2) := 'P_NOTICE_PERIOD';
119 RETURN;
120 END IF;
121 pay_in_utils.set_location(g_debug,l_procedure,30);
122
123 IF hr_general.decode_lookup('EMPLOYEE_CATG',p_emp_category) IS NULL THEN
124 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
125 p_token_name(1) := 'VALUE';
126 p_token_value(1) := p_emp_category;
127 p_token_name(2) := 'FIELD';
128 p_token_value(2) := 'P_EMP_CATEGORY';
129 RETURN;
130 END IF;
131 pay_in_utils.set_location(g_debug,l_procedure,50);
132
133 IF to_number(p_notice_period) > 999 THEN
134 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
135 p_token_name(1) := 'VALUE';
136 p_token_value(1) := p_notice_period;
137 p_token_name(2) := 'FIELD';
138 p_token_value(2) := 'P_NOTICE_PERIOD';
139 RETURN;
140 END IF;
141 pay_in_utils.set_location(g_debug,l_procedure,60);
142
143 OPEN c_dup_comb;
144 FETCH c_dup_comb
145 INTO l_dummy;
146 IF c_dup_comb%FOUND THEN
147 p_message_name := 'PER_IN_NON_UNIQUE_COMBINATION';
148 END IF;
149 CLOSE c_dup_comb;
150 pay_in_utils.set_location(g_debug,l_procedure,70);
151
152 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
153
154 END check_notice_period;
155
156 --------------------------------------------------------------------------
157 -- --
158 -- Name : CHECK_GRATUITY --
159 -- Type : PROCEDURE --
160 -- Access : Public --
161 -- Description : Procedure to validate gratuity as required for India--
162 -- Localization. --
163 -- --
164 -- Parameters : --
165 -- IN : p_element_entry_id NUMBER --
166 -- p_effective_date DATE --
167 -- p_calling_procedure VARCHAR2 --
168 -- OUT : p_message_name VARCHAR2 --
169 -- p_token_name pay_in_utils.char_tab_type--
170 -- p_token_value pay_in_utils.char_tab_type--
171 -- Change History : --
172 --
173 -- Rev# Date Userid Description --
174 --------------------------------------------------------------------------
175 -- 1.0 17-Nov-04 aaagarwa Created this procedure --
176 -- 1.1 18-Nov-04 aaagarwa Changed the message name and cursor name in
177 -- If clause.
178 --------------------------------------------------------------------------
179 PROCEDURE check_gratuity
180 (p_element_entry_id IN NUMBER
181 ,p_effective_date IN DATE
182 ,p_calling_procedure IN VARCHAR2
183 ,p_message_name OUT NOCOPY VARCHAR2
184 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
185 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
186 IS
187 /* Cursor to find the element id of the current element entry */
188 CURSOR c_element_type_id(p_element_entry_id NUMBER
189 ,p_effective_date DATE)
190 IS
191 SELECT pet.element_type_id
192 FROM pay_element_types_f pet
193 ,pay_element_entries_f pee
194 WHERE pet.element_type_id = pee.element_type_id
195 AND pee.element_entry_id = p_element_entry_id
196 AND pet.element_name = 'Gratuity Information'
197 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
198 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
199
200 /* Cursor to find the Screen entry value for Forfeiture Amount */
201 CURSOR c_gratuity_forfeiture_amount(p_element_entry_id NUMBER
202 ,p_element_type_id NUMBER
203 ,p_effective_date DATE)
204 IS
205 SELECT 'TRUE'
206 FROM pay_element_entry_values_f peev
207 ,pay_input_values_f piv
208 WHERE peev.element_entry_id = p_element_entry_id
209 AND piv.element_Type_id = p_element_type_id
210 AND peev.input_value_id = piv.input_value_id
211 AND piv.name = 'Forfeiture Amount'
212 AND peev.screen_entry_value IS NOT NULL
213 AND p_effective_date BETWEEN piv.effective_start_date
214 AND piv.effective_end_date
215 AND p_effective_date BETWEEN peev.effective_start_date
216 AND peev.effective_end_date;
217
218
219
220 /* Cursor to find the Screen entry value for Forfeiture Reason */
221 CURSOR c_gratuity_forfeiture_reason(p_element_entry_id NUMBER
222 ,p_element_type_id NUMBER
223 ,p_effective_date DATE)
224 IS
225 SELECT 'TRUE'
226 FROM pay_element_entry_values_f peev
227 ,pay_input_values_f piv
228 WHERE peev.element_entry_id = p_element_entry_id
229 AND piv.element_Type_id = p_element_type_id
230 AND peev.input_value_id = piv.input_value_id
231 AND piv.name = 'Forfeiture Reason'
232 AND peev.screen_entry_value IS NOT NULL
233 AND p_effective_date BETWEEN piv.effective_start_date
234 AND piv.effective_end_date
235 AND p_effective_date BETWEEN peev.effective_start_date
236 AND peev.effective_end_date;
237
238 l_procedure VARCHAR2(100);
239 l_element_type_id NUMBER;
240 l_dummy_amount VARCHAR2(10);
241 l_dummy_reason VARCHAR2(10);
242
243 BEGIN
244 g_debug := hr_utility.debug_enabled ;
245 l_procedure := g_package||'check_gratuity' ;
246 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
247 p_message_name := 'SUCCESS';
248 pay_in_utils.null_message(p_token_name, p_token_value);
249
250 /* Check for Gratuity Information element*/
251 OPEN c_element_type_id(p_element_entry_id,p_effective_date);
252 FETCH c_element_type_id INTO l_element_type_id;
253
254 IF l_element_type_id IS NOT NULL THEN
255 /*Element Found*/
256 OPEN c_gratuity_forfeiture_amount(p_element_entry_id,l_element_type_id,p_effective_date);
257 FETCH c_gratuity_forfeiture_amount INTO l_dummy_amount;
258 IF (c_gratuity_forfeiture_amount%FOUND and l_dummy_amount IS NOT NULL)THEN
259 /* Amount Found*/
260 OPEN c_gratuity_forfeiture_reason(p_element_entry_id,l_element_type_id,p_effective_date);
261 FETCH c_gratuity_forfeiture_reason INTO l_dummy_reason;
262 /*Find Reason*/
263 IF (c_gratuity_forfeiture_reason%NOTFOUND and l_dummy_reason IS NULL)THEN
264 /*Reason Not Found*/
265 p_message_name := 'PER_IN_FORFEITURE_REASON';
266 END IF;
267 CLOSE c_gratuity_forfeiture_reason;
268 END IF;
269 CLOSE c_gratuity_forfeiture_amount;
270 END IF;
271 CLOSE c_element_type_id;
272 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
273
274 END check_gratuity;
275
276 --------------------------------------------------------------------------
277 -- --
278 -- Name : YEARS_OF_SERVICE --
279 -- Type : FUNCTION --
280 -- Access : Private --
281 -- Description : Function to return the number of years of service --
282 -- for a terminated employee. --
283 -- --
284 -- Parameters : --
285 -- IN : p_date_start DATE --
286 -- p_act_term_date DATE --
287 -- p_flag VARCHAR2 --
288 -- --
289 -- Change History : --
290 --------------------------------------------------------------------------
291 -- Rev# Date Userid Description --
292 --------------------------------------------------------------------------
293 -- 1.0 19-OCT-04 statkar Created this function --
294 -- 1.1 28-OCT-04 statkar Modified to cater to Retrenchment --
295 -- 1.2 01-Nov-04 statkar Modified as per Testing Issues 3980777 --
296 --------------------------------------------------------------------------
297 FUNCTION years_of_service(p_start_date IN DATE
298 ,p_end_date IN DATE
299 ,p_flag IN VARCHAR2
300 )
301 RETURN NUMBER
302 IS
303 --
304
305 l_procedure VARCHAR2(100) ;
306 l_years NUMBER ;
307 l_yrs_of_service NUMBER ;
308 l_days NUMBER ;
309 l_months NUMBER;
310 l_temp_date DATE;
311
312 --
313 BEGIN
314 --
315 g_debug := hr_utility.debug_enabled ;
316 l_procedure := g_package || 'years_of_service' ;
317 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
318
319 l_years := months_between(p_end_date, p_start_date)/12 ;
320 l_yrs_of_service := trunc(l_years) ;
321 l_temp_date := add_months(p_start_date,l_yrs_of_service*12)-1;
322 l_days := p_end_date - l_temp_date;
323
324 IF g_debug THEN
325 pay_in_utils.trace('Years ',to_char(l_years));
326 pay_in_utils.trace('Yrs of Service ',to_char(l_yrs_of_service));
327 pay_in_utils.trace('Days = ',to_char(l_days));
328 END IF;
329 --
330 -- Chech IF flag is 'N', means employee is not covered
331 -- under Payment of Gratuity Act, 1972
332 --
333
334 IF p_flag = 'N' THEN
335 --
336 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
337 RETURN l_yrs_of_service ;
338 --
339 END IF ;
340
341 -- Check if the flag is 'R', this means that the employee's
342 -- Years of Service has to be calculated for Retrenchment
343 --
344 IF p_flag = 'R' THEN
345 --
346 l_months := months_between(p_end_date, l_temp_date);
347
348 IF g_debug THEN
349 pay_in_utils.trace('Temp Date ',to_char(l_temp_date,'DD-MM-YYYY'));
350 pay_in_utils.trace('Months ',to_char(l_months));
351 END IF;
352
353 IF l_months > 6 THEN
354 l_yrs_of_service := l_yrs_of_service + 1;
355 END IF;
356 RETURN l_yrs_of_service ;
357 END IF;
358
359 --
360 -- The rest of the code is for employees covered under rule,
361 -- i.e. when p_flag = Y
362 --
363 --
364 -- Check IF employee is falling between 4-5 years of service
365 --
366
367 IF l_yrs_of_service = 4 AND l_days >= 240 THEN
368 --
369 pay_in_utils.set_location(g_debug,l_procedure,30);
370 l_yrs_of_service := 5 ;
371 --
372 ELSIF l_yrs_of_service >= 5 AND l_days >= 183 THEN
373 --
374 pay_in_utils.set_location(g_debug,l_procedure,40);
375 l_yrs_of_service := l_yrs_of_service + 1;
376 --
377 END IF ;
378 pay_in_utils.set_location(g_debug,'Years of Service : '||l_yrs_of_service,50);
379 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
380 RETURN l_yrs_of_service ;
381
382 --
383 END years_of_service ;
384
385 ------------------------------------------------------------------------------------
386 -- --
387 -- Name : GET_AVERAGE_SALARY --
388 -- Type : FUNCTION --
389 -- Access : Public --
390 -- Description : Function to return average salary for a duration --
391 -- --
392 -- Parameters : --
393 -- IN : p_assignment_id NUMBER --
394 -- p_assignment_action_id NUMBER --
395 -- p_payroll_id NUMBER --
396 -- p_balance_name VARCHAR2 --
397 -- p_end_date DATE --
398 -- p_duration NUMBER --
399 -- --
400 -- Version Author Date Bug Description --
401 -- --------------------------------------------------------------------------------
402 -- 1.0 vgsriniv Created --
403 -- 1.1 rsaharay 28/02/2007 5889645 Removed cursor csr_date and csr_asg --
404 -- The asg effectivity is checked in --
405 -- the formula itself.
406 ------------------------------------------------------------------------------------
407 FUNCTION get_average_salary
408 (p_assignment_id IN NUMBER
409 ,p_assignment_action_id IN NUMBER
410 ,p_payroll_id IN NUMBER
411 ,p_balance_name IN VARCHAR2
412 ,p_end_date IN DATE
413 ,p_duration IN NUMBER
414 )
415 RETURN NUMBER
416 IS
417 l_procedure VARCHAR2(100);
418
419 l_asact_id NUMBER;
420 l_effective_date DATE;
421 l_date_earned DATE;
422 l_start_date DATE;
423 l_end_date DATE;
424 l_salary NUMBER;
425 l_days NUMBER;
426 l_days_in_month NUMBER;
427 l_bal_start_date DATE;
428 l_bal_end_date DATE;
429 l_total_salary NUMBER;
430 l_average_salary NUMBER;
431 l_total_divisor NUMBER;
432 i NUMBER;
433 l_def_bal_id NUMBER;
434
435
436 CURSOR csr_tp (p_start_date DATE, p_end_date DATE)
437 IS
438 SELECT
439 paa.assignment_action_id,
440 ppa.date_earned,
441 ppa.effective_date,
442 ptp.start_date,
443 ptp.end_date
444 FROM
445 per_time_periods ptp,
446 pay_payroll_actions ppa,
447 pay_assignment_actions paa
448 WHERE ptp.payroll_id = p_payroll_id
449 AND ptp.start_date between TRUNC(p_start_date,'MM') AND p_end_date
450 AND ptp.payroll_id = ppa.payroll_id
451 AND ptp.time_period_id = ppa.time_period_id
452 AND ppa.action_status = 'C'
453 AND ppa.action_type IN ('R','Q','I','B')
454 AND ppa.payroll_action_id = paa.payroll_action_id
455 AND paa.assignment_id = p_assignment_id
456 AND paa.source_action_id IS NULL
457 UNION
458 SELECT
459 paa.assignment_action_id,
460 ppa.date_earned,
461 ppa.effective_date,
462 ptp.start_date,
463 ptp.end_date
464 FROM
465 per_time_periods ptp,
466 pay_payroll_actions ppa,
467 pay_assignment_actions paa
468 WHERE ptp.payroll_id = p_payroll_id
469 AND ptp.payroll_id = ppa.payroll_id
470 AND ptp.time_period_id = ppa.time_period_id
471 AND ppa.payroll_action_id = paa.payroll_action_id
472 AND paa.assignment_id = p_assignment_id
473 AND paa.payroll_action_id = ppa.payroll_action_id
474 AND paa.assignment_action_id = p_assignment_action_id
475 ORDER BY 2;
476
477
478
479
480 BEGIN
481 g_debug := hr_utility.debug_enabled ;
482 l_procedure := g_package || 'get_average_salary' ;
483 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
484
485 IF g_debug THEN
486 pay_in_utils.trace('**************************************************','********************');
487 pay_in_utils.trace('Payroll ID ',to_char(p_payroll_id));
488 pay_in_utils.trace('Assignment ID ',to_char(p_assignment_id));
489 pay_in_utils.trace('AsAct ID ',to_char(p_assignment_action_id));
490 pay_in_utils.trace('Balance Name ',p_balance_name);
491 pay_in_utils.trace('End Date ',to_char(p_end_date));
492 pay_in_utils.trace('Duration ',to_char(p_duration));
493 END IF;
494
495 l_bal_end_date := p_end_date;
496 l_bal_start_date := add_months(l_bal_end_date,(-1)*p_duration)+1;
497 l_days_in_month := 30;
498 l_start_date := l_bal_start_date;
499 l_end_date := l_bal_end_date;
500
501 l_def_bal_id := pay_in_tax_utils.get_defined_balance (p_balance_name, '_ASG_PTD');
502
503 l_total_salary := 0;
504 l_average_salary := 0;
505 l_total_divisor := 10;
506
507 pay_in_utils.set_location(g_debug,l_procedure,20);
508 IF g_debug THEN
509 pay_in_utils.trace('Bal Start ',to_char(l_bal_start_date,'DD-MM-YYYY'));
510 pay_in_utils.trace('Bal End ',to_char(l_bal_end_date,'DD-MM-YYYY'));
511 pay_in_utils.trace('Def Bal ID ',to_char(l_def_bal_id));
512 END IF;
513
514
515
516 pay_in_utils.set_location(g_debug,l_procedure,30);
517
518 IF g_debug THEN
519 pay_in_utils.trace('Bal Start ',to_char(l_bal_start_date,'DD-MM-YYYY'));
520 pay_in_utils.trace('Bal End ',to_char(l_bal_end_date,'DD-MM-YYYY'));
521 pay_in_utils.trace('**************************************************','********************');
522 END IF;
523
524 i:=0;
525 OPEN csr_tp(l_bal_start_date, l_bal_end_date);
526 LOOP
527 i:=i+1;
528 FETCH csr_tp
529 INTO l_asact_id,
530 l_date_earned,
531 l_effective_date,
532 l_start_date,
533 l_end_date;
534 EXIT WHEN csr_tp%NOTFOUND;
535
536 l_days := LEAST(l_bal_end_date, l_end_date) - GREATEST(l_bal_start_date,l_start_date) + 1;
537 IF l_days = l_end_date - l_start_date + 1
538 THEN
539 l_days := l_days_in_month;
540 END IF;
541
542 l_salary := l_days/l_days_in_month *
543 pay_balance_pkg.get_value
544 (p_defined_balance_id => l_def_bal_id
545 ,p_assignment_action_id => l_asact_id
546 ,p_tax_unit_id => NULL
547 ,p_jurisdiction_code => NULL
548 ,p_source_id => NULL
549 ,p_source_text => NULL
550 ,p_tax_group => NULL
551 ,p_date_earned => NULL
552 ,p_get_rr_route => 'TRUE'
553 ,p_get_rb_route => 'FALSE');
554
555 IF g_debug THEN
556 pay_in_utils.trace('**************************************************','********************');
557 pay_in_utils.trace('AsActID : '||i||' ',to_char(l_asact_id));
558 pay_in_utils.trace('Date Earned : '||i||' ',to_char(l_date_earned));
559 pay_in_utils.trace('Date Paid : '||i||' ',to_char(l_effective_date));
560 pay_in_utils.trace('Start Date : '||i||' ',to_char(l_start_date));
561 pay_in_utils.trace('End Date : '||i||' ',to_char(l_end_date));
562 pay_in_utils.trace('Month Days : '||i||' ',to_char(l_days_in_month));
563 pay_in_utils.trace('Days : '||i||' ',to_char(l_days));
564 pay_in_utils.trace('Salary : '||i||' ',to_char(l_salary));
565 pay_in_utils.trace('**************************************************','********************');
566 END IF;
567 l_total_salary := l_total_salary + l_salary;
568
569 END LOOP;
570 CLOSE csr_tp;
571 pay_in_utils.set_location(g_debug,l_procedure,40);
572
573 l_average_salary := ROUND(l_total_salary /l_total_divisor,2);
574 IF g_debug THEN
575 pay_in_utils.trace('Tot Salary ',to_char(ROUND(l_total_salary,2)));
576 pay_in_utils.trace('Divisor ',to_char(ROUND(l_total_divisor,2)));
577 pay_in_utils.trace('Avg Salary ',to_char(ROUND(l_average_salary,2)));
578 END IF;
579 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,100);
580 RETURN l_average_salary;
581
582 END get_average_salary;
583 --------------------------------------------------------------------------
584 -- --
585 -- Name : GET_POS_DTLS --
586 -- Type : PROCEDURE --
587 -- Access : Private --
588 -- Description : Procedure to fetch ASG Details for Period of Service--
589 -- --
590 -- Parameters : --
591 -- IN : p_element_type_id NUMBER --
592 -- p_effective_date DATE --
593 -- OUT : p_input_values t_input_values_tab --
594 -- p_expected_entries NUMBER --
595 -- Change History : --
596 --------------------------------------------------------------------------
597 -- Rev# Date Userid Description --
598 --------------------------------------------------------------------------
599 -- 1.0 19-OCT-04 statkar Created this procedure --
600 -- 1.1 25-SEP-07 rsaharay Modified c_pos_dtls --
601 --------------------------------------------------------------------------
602 PROCEDURE get_pos_dtls
603 (p_period_of_service_id IN NUMBER
604 ,p_business_group_id IN NUMBER
605 ,p_effective_date IN DATE
606 )
607 IS
608 CURSOR c_pos_dtls IS
609 SELECT paf.assignment_id
610 ,paf.payroll_id
611 ,ppf.start_date
612 ,pos.leaving_reason
613 ,pos.notified_termination_date
614 FROM per_periods_of_service pos
615 ,per_assignments_f paf
616 ,per_people_f ppf
617 WHERE pos.period_of_service_id = p_period_of_service_id
618 AND pos.business_group_id = p_business_group_id
619 AND pos.period_of_service_id = paf.period_of_service_id
620 AND paf.person_id = ppf.person_id
621 AND p_effective_date BETWEEN ppf.effective_start_date
622 AND ppf.effective_end_date
623 AND p_effective_date BETWEEN paf.effective_start_date
624 AND paf.effective_end_date;
625
626 l_procedure VARCHAR2(100);
627
628 BEGIN
629 g_debug := hr_utility.debug_enabled ;
630 l_procedure := g_package ||'get_pos_dtls' ;
631 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
632
633 IF g_debug THEN
634 pay_in_utils.trace('Period of service id ',to_char(p_period_of_service_id));
635 pay_in_utils.trace('Business Group ID ',to_char(p_business_group_id));
636 pay_in_utils.trace('Effective Date ',to_char(p_effective_date, 'DD-MM-YYYY'));
637 END IF;
638
639 pay_in_utils.set_location(g_debug,l_procedure,20);
640
641 OPEN c_pos_dtls;
642 FETCH c_pos_dtls
643 INTO g_assignment_id, g_payroll_id, g_hire_date, g_leaving_reason, g_notified_date;
644 CLOSE c_pos_dtls;
645
646 pay_in_utils.set_location(g_debug,l_procedure,30);
647
648 IF g_debug THEN
649 pay_in_utils.trace('Assignment ID ',to_char(g_assignment_id));
650 pay_in_utils.trace('Payroll ID ',to_char(g_payroll_id));
651 pay_in_utils.trace('Hire Date ',to_char(g_hire_date, 'DD-MM-YYYY'));
652 pay_in_utils.trace('Leaving Reason ',g_leaving_reason);
653 pay_in_utils.trace('Notified Date ',to_char(g_notified_date,'DD-MM-YYYY'));
654 END IF;
655 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
656
657 END get_pos_dtls;
658
659 --------------------------------------------------------------------------
660 -- --
661 -- Name : GET_INPUT_VALUE_IDS --
662 -- Type : PROCEDURE --
663 -- Access : Private --
664 -- Description : Procedure to fetch Input Value details for Element --
665 -- --
666 -- Parameters : --
667 -- IN : p_element_type_id NUMBER --
668 -- p_effective_date DATE --
669 -- OUT : p_input_values t_input_values_tab --
670 -- p_expected_entries NUMBER --
671 -- Change History : --
672 --------------------------------------------------------------------------
673 -- Rev# Date Userid Description --
674 --------------------------------------------------------------------------
675 -- 1.0 19-OCT-04 statkar Created this procedure --
676 --------------------------------------------------------------------------
677 PROCEDURE get_input_value_ids
678 (p_element_type_id IN NUMBER
679 ,p_effective_date IN DATE
680 ,p_expected_entries OUT NOCOPY NUMBER
681 ,p_input_values OUT NOCOPY t_input_values_tab
682 ,p_business_group_id IN NUMBER)
683 IS
684
685 CURSOR csr_element_inputs
686 IS
687 SELECT inputs.name
688 , inputs.input_value_id
689 FROM pay_element_types_f types
690 , pay_input_values_f inputs
691 WHERE types.element_type_id = p_element_type_id
692 AND inputs.element_type_id = types.element_type_id
693 AND (inputs.legislation_code = g_legislation_code OR inputs.business_group_id = p_business_group_id)
694 AND p_effective_date BETWEEN types.effective_start_date
695 AND types.effective_end_date
696 AND p_effective_date BETWEEN inputs.effective_start_date
697 AND inputs.effective_end_date
698 ORDER BY inputs.display_sequence;
699
700 l_procedure VARCHAR2(100);
701 l_count NUMBER;
702
703 BEGIN
704 g_debug := hr_utility.debug_enabled ;
705 l_procedure := g_package ||'get_input_value_ids' ;
706
707 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
708
709 l_count := 0;
710
711 FOR rec_input_values IN csr_element_inputs
712 LOOP
713
714 l_count := l_count+1;
715 p_input_values(l_count).input_value_id := rec_input_values.input_value_id;
716 pay_in_utils.trace('Input Name: ' || rec_input_values.name, '['||rec_input_values.input_value_id||']');
717
718 END LOOP;
719
720 p_expected_entries:= l_count;
721 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure, 20);
722
723 END get_input_value_ids;
724
725 --------------------------------------------------------------------------
726 -- --
727 -- Name : GET_ENTRY_DETAILS --
728 -- Type : PROCEDURE --
729 -- Access : Private --
730 -- Description : Procedure to fetch Element Entry details for create --
731 -- --
732 -- Parameters : --
733 -- IN : p_effective_date DATE --
734 -- p_element_name VARCHAR2 --
735 -- p_assignment_id NUMBER --
736 -- p_payroll_id NUMBER --
737 -- OUT : p_element_type_id NUMBER --
738 -- p_element_link_id NUMBER --
739 -- p_object_version_number NUMBER --
740 -- p_input_values t_input_values_tab --
741 -- p_expected_entries NUMBER --
742 -- p_message_name VARCHAR2 --
743 -- p_token_name pay_in_utils.char_tab_type --
744 -- p_token_value pay_in_utils.char_tab_type --
745 -- Change History : --
746 --------------------------------------------------------------------------
747 -- Rev# Date Userid Description --
748 --------------------------------------------------------------------------
749 -- 1.0 28-Sep-06 lnagaraj Created this procedure --
750 --------------------------------------------------------------------------
751 PROCEDURE get_entry_details
752 (p_effective_date IN DATE
753 ,p_element_name IN VARCHAR2
754 ,p_element_type_id OUT NOCOPY NUMBER
755 ,p_element_link_id OUT NOCOPY NUMBER
756 ,p_input_values OUT NOCOPY t_input_values_tab
757 ,p_expected_entries OUT NOCOPY NUMBER
758 ,p_message_name OUT NOCOPY VARCHAR2
759 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
760 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
761 ,p_business_group_id IN NUMBER
762 )
763 IS
764
765 CURSOR c_element_link
766 IS
767 SELECT pel.element_link_id
768 ,pet.element_type_id
769 FROM pay_element_links_f pel,
770 pay_element_types_f pet,
771 per_assignments_f paf
772 WHERE pet.element_name = p_element_name
773 AND paf.assignment_id = g_assignment_id
774 AND pet.element_type_id = pel.element_type_id
775 AND (pel.payroll_id = g_payroll_id or pel.payroll_id IS NULL)
776 AND pel.business_group_id = paf.business_group_id
777 AND p_effective_date BETWEEN paf.effective_start_date
778 AND paf.effective_end_date
779 AND p_effective_date BETWEEN pet.effective_start_date
780 AND pet.effective_end_date
781 AND p_effective_date BETWEEN pel.effective_start_date
782 AND pel.effective_end_date;
783
784 CURSOR c_element_type_id
785 IS
786 SELECT element_type_id
787 FROM pay_element_types_f
788 WHERE (
789 legislation_code = 'IN'
790 OR
791 business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
792 )
793 AND element_name = p_element_name
794 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
795
796 l_procedure VARCHAR2(100);
797 l_link_flag VARCHAR2(100);
798
799 BEGIN
800 g_debug := hr_utility.debug_enabled ;
801 l_procedure := g_package ||'get_entry_details' ;
802 p_message_name := 'SUCCESS';
803 pay_in_utils.null_message (p_token_name, p_token_value);
804
805 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
806
807 OPEN c_element_type_id;
808 FETCH c_element_type_id INTO p_element_type_id;
809 CLOSE c_element_type_id;
810
811 l_link_flag := pay_in_utils.chk_element_link
812 (p_element_name,
813 g_assignment_id,
814 p_effective_date,
815 p_element_link_id);
816
817 IF l_link_flag <> 'SUCCESS' OR p_element_link_id IS NULL THEN
818 p_message_name := 'PER_IN_MISSING_LINK';
819 p_token_name(1) := 'ELEMENT_NAME';
820 p_token_value(1):= p_element_name;
821 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
822 RETURN;
823 END IF;
824 /*
825 OPEN c_element_link;
826 FETCH c_element_link INTO p_element_link_id
827 , p_element_type_id;
828 pay_in_utils.set_location(g_debug,l_procedure,20);
829
830 IF p_element_link_id is NULL OR c_element_link%NOTFOUND THEN
831 CLOSE c_element_link;
832 p_message_name := 'PER_IN_MISSING_LINK';
833 p_token_name(1) := 'ELEMENT_NAME';
834 p_token_value(1):= p_element_name;
835 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
836 RETURN;
837 END IF;
838 CLOSE c_element_link;
839 */
840
841 IF g_debug THEN
842 pay_in_utils.trace('Element link ID ', p_element_link_id);
843 pay_in_utils.trace('Element Type ID ', p_element_type_id);
844 END IF;
845 pay_in_utils.set_location(g_debug, l_procedure, 40);
846
847 get_input_value_ids(p_element_type_id => p_element_type_id
848 ,p_effective_date => p_effective_date
849 ,p_expected_entries => p_expected_entries
850 ,p_input_values => p_input_values
851 ,p_business_group_id => p_business_group_id);
852
853 pay_in_utils.set_location(g_debug, 'Leaving: '||l_procedure, 50);
854
855 END get_entry_details;
856
857 --------------------------------------------------------------------------
858 -- --
859 -- Name : CREATE_ENTRY --
860 -- Type : PROCEDURE --
861 -- Access : Private --
862 -- Description : Generic Procedure to create Element Entries --
863 -- --
864 -- Parameters : --
865 -- IN : p_effective_date DATE --
866 -- p_business_group_id VARCHAR2 --
867 -- p_assignment_id NUMBER --
868 -- p_payroll_id NUMBER --
869 -- p_element_name VARCHAR2 --
870 -- p_entry_values pay_in_utils.char_tab_type --
871 -- p_calling_procedure VARCHAR2 --
872 -- OUT : p_message_name VARCHAR2 --
873 -- p_token_name pay_in_utils.char_tab_type --
874 -- p_token_value pay_in_utils.char_tab_type --
875 -- Change History : --
876 --------------------------------------------------------------------------
877 -- Rev# Date Userid Description --
878 --------------------------------------------------------------------------
879 -- 1.0 28-Sep-06 lnagaraj Created this procedure --
880 --------------------------------------------------------------------------
881 PROCEDURE create_entry(p_effective_date IN DATE
882 ,p_business_group_id IN NUMBER
883 ,p_entry_values IN t_entry_values_tab
884 ,p_element_name IN VARCHAR2
885 ,p_calling_procedure IN VARCHAR2
886 ,p_message_name OUT NOCOPY VARCHAR2
887 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
888 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
889 )
890 IS
891 l_procedure VARCHAR2(100) ;
892 l_element_type_id pay_element_types_f.element_type_id%TYPE;
893 l_element_link_id pay_element_links_f.element_link_id%TYPE;
894 l_input_values t_input_values_tab;
895 l_expected_entries NUMBER;
896 l_effective_start_date DATE;
897 l_effective_end_date DATE;
898 l_element_entry_id NUMBER;
899 l_object_version_number NUMBER;
900 l_warning CHAR(10);
901
902 l_statem VARCHAR2(5000);
903 sql_cursor NUMBER;
904 l_rows NUMBER;
905
906
907 BEGIN
908
909 l_procedure := g_package || 'create_entry' ;
910 p_message_name := 'SUCCESS';
911 pay_in_utils.null_message (p_token_name, p_token_value);
912
913 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
914
915 IF g_payroll_id is null THEN
916
917 /* p_message_name := 'PER_IN_MISSING_LINK';
918 p_token_name(1) := 'ELEMENT_NAME';
919 p_token_value(1) := p_element_name;
920 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
921 */ RETURN;
922
923 END IF ;
924
925 pay_in_utils.set_location(g_debug,l_procedure,30);
926
927 get_entry_details
928 (p_effective_date => p_effective_date
929 ,p_element_name => p_element_name
930 ,p_element_type_id => l_element_type_id
931 ,p_element_link_id => l_element_link_id
932 ,p_input_values => l_input_values
933 ,p_expected_entries => l_expected_entries
934 ,p_message_name => p_message_name
935 ,p_token_name => p_token_name
936 ,p_token_value => p_token_value
937 ,p_business_group_id => p_business_group_id
938 ) ;
939
940 IF p_message_name <> 'SUCCESS' THEN
941 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
942 RETURN;
943 END IF;
944
945 pay_in_utils.set_location(g_debug,l_procedure,50);
946
947 --
948 -- Dynamic SQL Cursor
949 --
950 l_statem := NULL;
951 l_statem := l_statem||'DECLARE ';
952 l_statem := l_statem||' l_warning BOOLEAN; ';
953 l_statem := l_statem||'BEGIN ';
954 l_statem := l_statem||'pay_element_entry_api.create_element_entry';
955 l_statem := l_statem||'(p_effective_date =>'''||p_effective_date||'''';
956 l_statem := l_statem||',p_business_group_id => '||p_business_group_id;
957 l_statem := l_statem||',p_assignment_id => '||g_assignment_id;
958 l_statem := l_statem||',p_element_link_id => '||l_element_link_id;
959 l_statem := l_statem||',p_entry_type => ''E''';
960 l_statem := l_statem||',p_creator_type => ''F''';
961 l_statem := l_statem||',p_effective_start_date => :l_eff_start_date';
962 l_statem := l_statem||',p_effective_end_date => :l_eff_end_date';
963 l_statem := l_statem||',p_element_entry_id => :l_ee_id';
964 l_statem := l_statem||',p_object_version_number => :l_ovn';
965 l_statem := l_statem||',p_create_warning => l_warning';
966 l_statem := l_statem||',p_override_user_ent_chk => ''Y''';
967 pay_in_utils.set_location(g_debug,l_procedure,60);
968
969 FOR i IN 1..l_expected_entries
970 LOOP
971 l_statem := l_statem||',p_input_value_id'||i||'=> '||l_input_values(i).input_value_id;
972 IF p_entry_values(i).entry_value IS NULL THEN
973 l_statem := l_statem||',p_entry_value'||i||'=> ''''';
974 ELSE
975 l_statem := l_statem||',p_entry_value'||i||'=> '''||p_entry_values(i).entry_value||'''';
976 END IF;
977 END LOOP;
978
979 l_statem := l_statem||');';
980 l_statem := l_statem||'IF l_warning THEN :l_warn := ''TRUE''; ELSE :l_warn := ''FALSE''; END IF;';
981 l_statem := l_statem||'END;';
982 pay_in_utils.trace(substr(l_statem,1,250),1);
983 pay_in_utils.trace(substr(l_statem,251,250),2);
984 pay_in_utils.trace(substr(l_statem,501,250),3);
985 pay_in_utils.trace(substr(l_statem,751,250),4);
986
987 pay_in_utils.set_location(g_debug,l_procedure,70);
988
989 sql_cursor := dbms_sql.open_cursor;
990 pay_in_utils.set_location(g_debug,l_procedure,80);
991
992 dbms_sql.parse(sql_cursor, l_statem, dbms_sql.native);
993 pay_in_utils.set_location(g_debug,l_procedure,90);
994
995 dbms_sql.bind_variable(sql_cursor, 'l_eff_start_date', l_effective_start_date);
996 dbms_sql.bind_variable(sql_cursor, 'l_eff_end_date', l_effective_end_date);
997 dbms_sql.bind_variable(sql_cursor, 'l_ee_id', l_element_entry_id);
998 dbms_sql.bind_variable(sql_cursor, 'l_ovn', l_object_version_number);
999 dbms_sql.bind_variable_char(sql_cursor, 'l_warn', l_warning,10);
1000
1001 pay_in_utils.set_location(g_debug,l_procedure,100);
1002
1003 l_rows := dbms_sql.execute(sql_cursor);
1004 pay_in_utils.set_location(g_debug,l_procedure,110);
1005
1006 dbms_sql.close_cursor(sql_cursor);
1007
1008 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,120);
1009 --
1010 END create_entry ;
1011
1012 --------------------------------------------------------------------------
1013 -- --
1014 -- Name : CHECK_EE_EXISTS --
1015 -- Type : Procedure --
1016 -- Access : Private --
1017 -- Description : Procedure to check if EE already exists for an ET --
1018 -- --
1019 -- Parameters : --
1020 -- IN : p_element_name VARCHAR2 --
1021 -- p_assignment_id NUMBER --
1022 -- p_effective_date DATE --
1023 -- OUT : p_element_entry_id NUMBER --
1024 -- : p_start_date DATE --
1025 -- p_ee_ovn NUMBER --
1026 -- Change History : --
1027 --------------------------------------------------------------------------
1028 -- Rev# Date Userid Description --
1029 --------------------------------------------------------------------------
1030 -- 1.0 19-OCT-04 statkar Created this procedure --
1031 --------------------------------------------------------------------------
1032 FUNCTION check_ee_exists(p_element_name IN VARCHAR2
1033 ,p_assignment_id IN NUMBER
1034 ,p_effective_date IN DATE
1035 ,p_element_entry_id OUT NOCOPY NUMBER
1036 ,p_start_date OUT NOCOPY DATE
1037 ,p_ee_ovn OUT NOCOPY NUMBER)
1038 RETURN BOOLEAN
1039 IS
1040 CURSOR csr_asg_details
1041 IS
1042 SELECT asg.business_group_id
1043 ,asg.payroll_id
1044 FROM per_assignments_f asg
1045 WHERE asg.assignment_id = p_assignment_id
1046 AND asg.primary_flag = 'Y'
1047 AND p_effective_date BETWEEN asg.effective_start_date
1048 AND asg.effective_end_date ;
1049
1050 CURSOR csr_element_link (l_business_group_id IN NUMBER,
1051 l_payroll_id IN NUMBER)
1052 IS
1053 SELECT pel.element_link_id
1054 FROM pay_element_links_f pel,
1055 pay_element_types_f pet
1056 WHERE pet.element_name = p_element_name
1057 AND pet.element_type_id = pel.element_type_id
1058 AND (pel.payroll_id = l_payroll_id
1059 OR (pel.payroll_id IS NULL
1060 AND pel.link_to_all_payrolls_flag = 'Y' ) )
1061 AND pel.business_group_id = l_business_group_id
1062 AND p_effective_date BETWEEN pet.effective_start_date
1063 AND pet.effective_end_date
1064 AND p_effective_date BETWEEN pel.effective_start_date
1065 AND pel.effective_end_date ;
1066
1067
1068 CURSOR csr_element_entry (c_element_link_id IN NUMBER)
1069 IS
1070 SELECT element_entry_id
1071 ,object_version_number
1072 ,effective_start_date
1073 FROM pay_element_entries_f
1074 WHERE assignment_id = p_assignment_id
1075 AND element_link_id = c_element_link_id
1076 AND p_effective_date BETWEEN effective_start_date
1077 AND effective_end_date ;
1078
1079 l_business_group_id NUMBER;
1080 l_element_link_id NUMBER;
1081 l_payroll_id NUMBER;
1082 l_link_flag VARCHAR2(100);
1083
1084 BEGIN
1085 p_element_entry_id := NULL;
1086 p_ee_ovn := NULL;
1087 g_debug := hr_utility.debug_enabled;
1088
1089 OPEN csr_asg_details;
1090 FETCH csr_asg_details
1091 INTO l_business_group_id, l_payroll_id;
1092 CLOSE csr_asg_details;
1093
1094 IF g_debug THEN
1095 pay_in_utils.trace('Business Group ID ',l_business_group_id);
1096 pay_in_utils.trace('Payroll ID ',l_payroll_id);
1097 END IF;
1098
1099 l_link_flag := pay_in_utils.chk_element_link
1100 (p_element_name,
1101 p_assignment_id,
1102 p_effective_date,
1103 l_element_link_id);
1104
1105 IF l_link_flag <> 'SUCCESS' OR l_element_link_id IS NULL THEN
1106 RETURN FALSE;
1107 /*
1108 OPEN csr_element_link (l_business_group_id, l_payroll_id);
1109 FETCH csr_element_link INTO l_element_link_id;
1110
1111 IF csr_element_link%NOTFOUND OR l_element_link_id IS NULL THEN
1112 CLOSE csr_element_link;
1113 RETURN FALSE;
1114 */
1115 ELSE
1116 IF g_debug THEN
1117 pay_in_utils.trace('Element Link ID ',l_element_link_id);
1118 END IF;
1119
1120 -- CLOSE csr_element_link;
1121 --
1122 OPEN csr_element_entry(l_element_link_id) ;
1123 FETCH csr_element_entry INTO p_element_entry_id, p_ee_ovn, p_start_date ;
1124 IF g_debug then
1125 pay_in_utils.trace('Element Entry ID ',p_element_entry_id);
1126 END IF;
1127
1128 IF p_element_entry_id IS NULL OR csr_element_entry%NOTFOUND
1129 THEN
1130 CLOSE csr_element_entry;
1131 RETURN FALSE;
1132 END IF;
1133 END IF;
1134 RETURN TRUE;
1135 --
1136 END check_ee_exists;
1137
1138 --------------------------------------------------------------------------
1139 -- --
1140 -- Name : CHECK_EE_EXISTS --
1141 -- Type : Procedure --
1142 -- Access : Private --
1143 -- Description : Procedure to check if EE already exists for an ET --
1144 -- --
1145 -- Parameters : --
1146 -- IN : p_element_name VARCHAR2 --
1147 -- p_input_value_name VARCHAR2 --
1148 -- p_input_value VARCHAR2 --
1149 -- p_assignment_id NUMBER --
1150 -- p_effective_date DATE --
1151 -- OUT : p_element_entry_id NUMBER --
1152 -- : p_start_date DATE --
1153 -- p_ee_ovn NUMBER --
1154 -- Change History : --
1155 --------------------------------------------------------------------------
1156 -- Rev# Date Userid Description --
1157 --------------------------------------------------------------------------
1158 -- 1.0 19-OCT-04 statkar Created this procedure --
1159 --------------------------------------------------------------------------
1160 FUNCTION check_ee_exists
1161 (p_element_name IN VARCHAR2
1162 ,p_input_value_name IN VARCHAR2
1163 ,p_input_value IN VARCHAR2
1164 ,p_assignment_id IN NUMBER
1165 ,p_effective_date IN DATE
1166 ,p_element_entry_id OUT NOCOPY NUMBER
1167 ,p_start_date OUT NOCOPY DATE
1168 ,p_ee_ovn OUT NOCOPY NUMBER)
1169 RETURN BOOLEAN
1170 IS
1171 CURSOR csr_asg_details
1172 IS
1173 SELECT asg.business_group_id
1174 ,asg.payroll_id
1175 FROM per_assignments_f asg
1176 WHERE asg.assignment_id = p_assignment_id
1177 AND asg.primary_flag = 'Y'
1178 AND p_effective_date BETWEEN asg.effective_start_date
1179 AND asg.effective_end_date ;
1180
1181 CURSOR csr_element_link (l_business_group_id IN NUMBER,
1182 l_payroll_id IN NUMBER)
1183 IS
1184 SELECT pel.element_link_id
1185 FROM pay_element_links_f pel,
1186 pay_element_types_f pet
1187 WHERE pet.element_name = p_element_name
1188 AND pet.element_type_id = pel.element_type_id
1189 AND (pel.payroll_id = l_payroll_id
1190 OR (pel.payroll_id IS NULL
1191 AND pel.link_to_all_payrolls_flag = 'Y' ) )
1192 AND pel.business_group_id = l_business_group_id
1193 AND p_effective_date BETWEEN pet.effective_start_date
1194 AND pet.effective_end_date
1195 AND p_effective_date BETWEEN pel.effective_start_date
1196 AND pel.effective_end_date ;
1197
1198
1199 CURSOR csr_element_entry (c_element_link_id IN NUMBER)
1200 IS
1201 SELECT pef.element_entry_id
1202 ,pef.object_version_number
1203 ,pef.effective_start_date
1204 FROM pay_element_entries_f pef
1205 ,pay_element_entry_values_f pev
1206 ,pay_input_values_f piv
1207 WHERE pef.assignment_id = p_assignment_id
1208 AND pef.element_link_id = c_element_link_id
1209 AND pef.element_entry_id = pev.element_entry_id
1210 AND pev.input_value_id = piv.input_value_id
1211 AND piv.NAME = p_input_value_name
1212 AND pev.screen_entry_value = p_input_value
1213 AND p_effective_date BETWEEN pef.effective_start_date
1214 AND pef.effective_end_date
1215 AND p_effective_date BETWEEN pev.effective_start_date
1216 AND pev.effective_end_date
1217 AND p_effective_date BETWEEN piv.effective_start_date
1218 AND piv.effective_end_date;
1219
1220 l_business_group_id NUMBER;
1221 l_element_link_id NUMBER;
1222 l_payroll_id NUMBER;
1223 l_link_flag VARCHAR2(100);
1224
1225 BEGIN
1226 p_element_entry_id := NULL;
1227 p_ee_ovn := NULL;
1228 g_debug := hr_utility.debug_enabled;
1229
1230 OPEN csr_asg_details;
1231 FETCH csr_asg_details
1232 INTO l_business_group_id, l_payroll_id;
1233 CLOSE csr_asg_details;
1234
1235 IF g_debug THEN
1236 pay_in_utils.trace('Business Group ID ',l_business_group_id);
1237 pay_in_utils.trace('Payroll ID ',l_payroll_id);
1238 END IF;
1239
1240 l_link_flag := pay_in_utils.chk_element_link
1241 (p_element_name,
1242 p_assignment_id,
1243 p_effective_date,
1244 l_element_link_id);
1245
1246 IF l_link_flag <> 'SUCCESS' OR l_element_link_id IS NULL THEN
1247 RETURN FALSE;
1248
1249 /* OPEN csr_element_link (l_business_group_id, l_payroll_id);
1250 FETCH csr_element_link INTO l_element_link_id;
1251
1252 IF csr_element_link%NOTFOUND OR l_element_link_id IS NULL THEN
1253 CLOSE csr_element_link;
1254 RETURN FALSE;
1255 */
1256 ELSE
1257 IF g_debug THEN
1258 pay_in_utils.trace('Element Link ID ',l_element_link_id);
1259 END IF;
1260
1261 -- CLOSE csr_element_link;
1262 --
1263 OPEN csr_element_entry(l_element_link_id) ;
1264 FETCH csr_element_entry INTO p_element_entry_id, p_ee_ovn, p_start_date ;
1265 IF g_debug then
1266 pay_in_utils.trace('Element Entry ID ',p_element_entry_id);
1267 END IF;
1268
1269 IF p_element_entry_id IS NULL OR csr_element_entry%NOTFOUND
1270 THEN
1271 CLOSE csr_element_entry;
1272 RETURN FALSE;
1273 END IF;
1274 END IF;
1275 RETURN TRUE;
1276 --
1277 END check_ee_exists;
1278
1279
1280 --------------------------------------------------------------------------
1281 -- --
1282 -- Name : IS_ELEMENT_PROCESSED --
1283 -- Type : FUNCTION --
1284 -- Access : Private --
1285 -- Description : Function to return whether element is processed --
1286 -- for a terminated employee. --
1287 -- --
1288 -- Parameters : --
1289 -- IN : p_assignment_id NUMBER --
1290 -- p_element_name VARCHAR2 --
1291 -- --
1292 -- Change History : --
1293 --------------------------------------------------------------------------
1294 -- Rev# Date Userid Description --
1295 --------------------------------------------------------------------------
1296 -- 1.0 20-apr-07 rsaharay Created this function --
1297
1298 --------------------------------------------------------------------------
1299 FUNCTION is_element_processed(p_assignment_id IN NUMBER,
1300 p_element_name IN VARCHAR2
1301 )
1302 RETURN BOOLEAN
1303 IS
1304 --
1305 CURSOR csr_element_proc IS
1306 SELECT 1
1307 FROM pay_run_results prr,
1308 pay_assignment_actions paa,
1309 pay_element_types_f pet,
1310 pay_payroll_Actions ppa
1311 WHERE paa.assignment_id = p_assignment_id
1312 AND paa.assignment_action_id = prr.assignment_action_id
1313 AND paa.payroll_action_id= ppa.payroll_action_id
1314 AND ppa.action_type in('R','Q','B')
1315 AND ppa.action_status = 'C'
1316 AND paa.action_status = 'C'
1317 AND prr.element_type_id=pet.element_type_id
1318 AND pet.element_name=p_element_name;
1319
1320
1321 l_procedure VARCHAR2(100) ;
1322 l_count NUMBER ;
1323
1324
1325 --
1326 BEGIN
1327 g_debug := hr_utility.debug_enabled ;
1328 l_procedure := g_package || 'is_element_processed' ;
1329 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1330
1331 OPEN csr_element_proc;
1332 FETCH csr_element_proc INTO l_count;
1333 IF csr_element_proc%FOUND THEN
1334 pay_in_utils.set_location(g_debug,'Leaving: True'||l_procedure,60);
1335 CLOSE csr_element_proc;
1336 RETURN TRUE ;
1337 END IF ;
1338 CLOSE csr_element_proc;
1339 pay_in_utils.set_location(g_debug,'Leaving: False'||l_procedure,60);
1340 RETURN FALSE ;
1341 --
1342 END is_element_processed ;
1343
1344 --------------------------------------------------------------------------
1345 -- --
1346 -- Name : CHECK_ADVANCE_EXISTS --
1347 -- Type : Procedure --
1348 -- Access : Private --
1349 -- Description : Procedure to check if EE already exists for an ET --
1350 -- --
1351 -- Parameters : --
1352 -- IN : p_element_name VARCHAR2 --
1353 -- p_input_value_name VARCHAR2 --
1354 -- p_input_value VARCHAR2 --
1355 -- p_assignment_id NUMBER --
1356 -- p_effective_date DATE --
1357 -- OUT : p_element_entry_id NUMBER --
1358 -- : p_start_date DATE --
1359 -- p_ee_ovn NUMBER --
1360 -- Change History : --
1361 --------------------------------------------------------------------------
1362 -- Rev# Date Userid Description --
1363 --------------------------------------------------------------------------
1364 -- 1.0 19-OCT-04 statkar Created this procedure --
1365 --------------------------------------------------------------------------
1366 FUNCTION check_advance_exists
1367 (p_component_name IN VARCHAR2
1368 ,p_assignment_id IN NUMBER
1369 ,p_effective_date IN DATE
1370 ,p_element_entry_id OUT NOCOPY NUMBER
1371 ,p_start_date OUT NOCOPY DATE
1372 ,p_ee_ovn OUT NOCOPY NUMBER
1373 )
1374 RETURN BOOLEAN
1375 IS
1376 CURSOR csr_asg_details
1377 IS
1378 SELECT asg.business_group_id
1379 ,asg.payroll_id
1380 FROM per_assignments_f asg
1381 WHERE asg.assignment_id = p_assignment_id
1382 AND asg.primary_flag = 'Y'
1383 AND p_effective_date BETWEEN asg.effective_start_date
1384 AND asg.effective_end_date ;
1385
1386
1387
1388 CURSOR csr_element_entry(l_business_group_id IN NUMBER)
1389 IS
1390 SELECT pee.element_entry_id
1391 , pee.object_version_number
1392 ,pee.effective_start_date
1393 FROM pay_element_types_f pet,
1394 pay_element_classifications pec,
1395 pay_element_entries_f pee,
1396 pay_element_entry_values_f peev,
1397 pay_input_values_f piv
1398 WHERE pet.classification_id = pec.classification_id
1399 AND pec.classification_name = 'Information'
1400 AND pet.element_name LIKE '%Excess Advance'
1401 AND pet.element_type_id = piv.element_type_id
1402 AND piv.name ='Component Name'
1403 AND piv.default_value = p_component_name
1404 AND peev.input_value_id = piv.input_value_id
1405 AND peev.element_entry_id = pee.element_entry_id
1406 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1407 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1408 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1409 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1410 AND pee.assignment_id = p_assignment_id
1411 AND pee.element_type_id = pet.element_type_id
1412 AND pet.business_group_id = l_business_group_id;
1413
1414 l_business_group_id NUMBER;
1415 l_element_link_id NUMBER;
1416 l_payroll_id NUMBER;
1417
1418 BEGIN
1419 p_element_entry_id := NULL;
1420 p_ee_ovn := NULL;
1421 g_debug := hr_utility.debug_enabled;
1422
1423 OPEN csr_asg_details;
1424 FETCH csr_asg_details
1425 INTO l_business_group_id, l_payroll_id;
1426 CLOSE csr_asg_details;
1427
1428 IF g_debug THEN
1429 pay_in_utils.trace('Business Group ID ',l_business_group_id);
1430 pay_in_utils.trace('Payroll ID ',l_payroll_id);
1431 END IF;
1432
1433
1434 --
1435 OPEN csr_element_entry(l_business_group_id) ;
1436 FETCH csr_element_entry INTO p_element_entry_id, p_ee_ovn, p_start_date ;
1437 IF g_debug then
1438 pay_in_utils.trace('Element Entry ID ',p_element_entry_id);
1439 END IF;
1440
1441 IF p_element_entry_id IS NULL OR csr_element_entry%NOTFOUND
1442 THEN
1443 CLOSE csr_element_entry;
1444 RETURN FALSE;
1445 END IF;
1446 CLOSE csr_element_entry;
1447
1448 RETURN TRUE;
1449 --
1450 END check_advance_exists;
1451
1452 --------------------------------------------------------------------------
1453 -- --
1454 -- Name : GET_GRATUITY_IVS --
1455 -- Type : Procedure --
1456 -- Access : Private --
1457 -- Description : Procedure to set the IVs of Gratuity Information --
1458 -- --
1459 -- Parameters : --
1460 -- IN : p_actual_termination_date DATE --
1461 -- OUT : p_continuous_service_flag VARCHAR2 --
1462 -- : p_years_of_service NUMBER --
1463 -- p_create_ee BOOLEAN --
1464 -- Change History : --
1465 --------------------------------------------------------------------------
1466 -- Rev# Date Userid Description --
1467 --------------------------------------------------------------------------
1468 -- 1.0 19-OCT-04 statkar Created this procedure --
1469 --------------------------------------------------------------------------
1470 PROCEDURE get_gratuity_ivs
1471 (p_actual_termination_date IN DATE
1472 ,p_continuous_service_flag OUT NOCOPY VARCHAR2
1473 ,p_years_of_service OUT NOCOPY NUMBER
1474 ,p_create_ee OUT NOCOPY BOOLEAN
1475 )
1476 IS
1477 l_procedure VARCHAR2(100);
1478
1479 CURSOR csr_asg_details
1480 IS
1481 SELECT scl.segment8
1482 FROM per_assignments_f asg
1483 ,hr_soft_coding_keyflex scl
1484 WHERE asg.assignment_id = g_assignment_id
1485 AND asg.primary_flag = 'Y'
1486 AND p_actual_termination_date
1487 BETWEEN asg.effective_start_date
1488 AND asg.effective_end_date
1489 AND scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1490 AND scl.enabled_flag = 'Y' ;
1491
1492 l_e_date CONSTANT DATE := to_date('01-04-2004','DD-MM-YYYY');
1493 l_coverage_flag hr_soft_coding_keyflex.segment8%TYPE;
1494
1495 BEGIN
1496 g_debug := hr_utility.debug_enabled ;
1497 l_procedure := g_package ||'get_gratuity_ivs' ;
1498 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1499
1500 pay_in_utils.set_location(g_debug,l_procedure,20);
1501
1502 OPEN csr_asg_details;
1503 FETCH csr_asg_details
1504 INTO l_coverage_flag;
1505 CLOSE csr_asg_details;
1506
1507 pay_in_utils.set_location(g_debug,l_procedure,30);
1508
1509 IF l_coverage_flag = 'Y' AND p_actual_termination_date > l_e_date
1510 THEN
1511 pay_in_utils.set_location(g_debug,l_procedure,40);
1512 p_years_of_service := years_of_service(p_start_date => g_hire_date
1513 ,p_end_date => p_actual_termination_date
1514 ,p_flag => 'Y'
1515 ) ;
1516 p_continuous_service_flag:='Y';
1517 IF g_debug THEN
1518 pay_in_utils.trace('Years of Service ',p_years_of_service);
1519 END IF;
1520 pay_in_utils.set_location(g_debug,l_procedure,50);
1521
1522 IF g_leaving_reason IN ( 'PDD', 'PDA', 'D' ) OR p_years_of_service >=5
1523 THEN
1524 pay_in_utils.set_location(g_debug,l_procedure,60);
1525 p_create_ee := TRUE;
1526 ELSE
1527 pay_in_utils.set_location(g_debug,l_procedure,70);
1528 p_create_ee := FALSE;
1529 END IF;
1530 --
1531 END IF; --End of IF block for Covered under Gratuity and act_term_date check.
1532
1533 pay_in_utils.set_location(g_debug,'Leaving :'||l_procedure,90);
1534
1535 END get_gratuity_ivs;
1536
1537 --------------------------------------------------------------------------
1538 -- --
1539 -- Name : CREATE_GRATUITY_ENTRY --
1540 -- Type : PROCEDURE --
1541 -- Access : Public --
1542 -- Description : Procedure to handle creation of Gratuity entry for --
1543 -- terminated employee based on conditions as required --
1544 -- for India Localization. --
1545 -- --
1546 -- Parameters : --
1547 -- IN : p_period_of_service_id NUMBER --
1548 -- p_business_group_id NUMBER --
1549 -- p_actual_termination_date DATE --
1550 -- p_calling_procedure VARCHAR2 --
1551 -- OUT : p_message_name VARCHAR2 --
1552 -- p_token_name pay_in_utils.char_tab_type--
1553 -- p_token_value pay_in_utils.char_tab_type--
1554 -- Change History : --
1555 --------------------------------------------------------------------------
1556 -- Rev# Date Userid Description --
1557 --------------------------------------------------------------------------
1558 -- 1.0 19-OCT-04 statkar Created this procedure --
1559 -- 1.1 19-Nov-04 statkar 4015962 Removed Base Salary Input Value --
1560 --------------------------------------------------------------------------
1561 PROCEDURE create_gratuity_entry
1562 (p_period_of_service_id IN NUMBER
1563 ,p_business_group_id IN NUMBER
1564 ,p_actual_termination_date IN DATE
1565 ,p_calling_procedure IN VARCHAR2
1566 ,p_message_name OUT NOCOPY VARCHAR2
1567 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1568 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1569 )
1570 IS
1571 l_procedure VARCHAR2(100);
1572 l_element_name pay_element_types_f.element_name%TYPE ;
1573 l_entry_values t_entry_values_tab;
1574 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1575 l_start_date DATE;
1576 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
1577 l_ee_exists BOOLEAN;
1578 l_create_ee BOOLEAN;
1579 l_element_processed BOOLEAN;
1580
1581 BEGIN
1582 g_debug := hr_utility.debug_enabled ;
1583 l_procedure := g_package || 'create_gratuity_entry' ;
1584 p_message_name := 'SUCCESS';
1585 pay_in_utils.null_message (p_token_name, p_token_value);
1586
1587 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1588
1589 l_ee_exists := check_ee_exists
1590 (p_element_name => g_gratuity_et--l_element_name
1591 ,p_assignment_id => g_assignment_id
1592 ,p_effective_date => p_actual_termination_date
1593 ,p_element_entry_id => l_element_entry_id
1594 ,p_start_date => l_start_date
1595 ,p_ee_ovn => l_ee_ovn);
1596
1597 IF l_ee_exists THEN
1598 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
1599 RETURN;
1600 END IF;
1601
1602 l_element_processed := is_element_processed
1603 (p_assignment_id =>g_assignment_id,
1604 p_element_name =>g_gratuity_et);
1605
1606 IF l_element_processed THEN
1607 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
1608 RETURN;
1609 END IF;
1610 --
1611 -- Element Name : Gratuity Information
1612 --
1613 -- Input Value are:
1614 --
1615 -- 1. Gratuity Amount - Null
1616 -- 2. Continuous Service - Yes/No
1617 -- 3. Completed Service Years - Calculate
1618 -- 4. Forfeiture Amount - Null
1619 -- 5. Forfeiture Reason - Null
1620 -- 6. Component Name - 'Gratuity'
1621 -- 7. Salary for the Period - Null
1622 l_entry_values(1).entry_value := null;
1623 get_gratuity_ivs
1624 (p_actual_termination_date => p_actual_termination_date
1625 ,p_continuous_service_flag => l_entry_values(2).entry_value
1626 ,p_years_of_service => l_entry_values(3).entry_value
1627 ,p_create_ee => l_create_ee
1628 );
1629
1630 l_entry_values(4).entry_value := null;
1631 l_entry_values(5).entry_value := null;
1632 l_entry_values(6).entry_value := g_gratuity_cn;
1633 l_entry_values(7).entry_value := null;
1634 l_element_name := g_gratuity_et;
1635
1636 IF l_create_ee THEN
1637 pay_in_utils.set_location(g_debug,l_procedure,20);
1638 create_entry
1639 (p_effective_date => p_actual_termination_date
1640 ,p_business_group_id => p_business_group_id
1641 ,p_element_name => l_element_name
1642 ,p_entry_values => l_entry_values
1643 ,p_calling_procedure => p_calling_procedure
1644 ,p_message_name => p_message_name
1645 ,p_token_name => p_token_name
1646 ,p_token_value => p_token_value
1647 );
1648 pay_in_utils.set_location(g_debug,l_procedure,30);
1649 END IF;
1650 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1651
1652 END create_gratuity_entry;
1653
1654 --------------------------------------------------------------------------
1655 -- --
1656 -- Name : DELETE_GRATUITY_ENTRY --
1657 -- Type : Procedure --
1658 -- Access : Public --
1659 -- Description : Procedure to delete 'Gratuity Information' Entry --
1660 -- if termination is reversed for the employee. --
1661 -- --
1662 -- Parameters : --
1663 -- IN : p_period_of_service_id NUMBER --
1664 -- p_business_group_id NUMBER --
1665 -- p_actual_termination_date DATE --
1666 -- p_calling_procedure VARCHAR2 --
1667 -- OUT : p_message_name VARCHAR2 --
1668 -- p_token_name pay_in_utils.char_tab_type--
1669 -- p_token_value pay_in_utils.char_tab_type--
1670 -- Change History : --
1671 --------------------------------------------------------------------------
1672 -- Rev# Date Userid Description --
1673 --------------------------------------------------------------------------
1674 -- 1.0 19-OCT-04 statkar Created this procedure --
1675 --------------------------------------------------------------------------
1676 PROCEDURE delete_gratuity_entry
1677 (p_period_of_service_id IN NUMBER
1678 ,p_business_group_id IN NUMBER
1679 ,p_actual_termination_date IN DATE
1680 ,p_calling_procedure IN VARCHAR2
1681 ,p_message_name OUT NOCOPY VARCHAR2
1682 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1683 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1684 )
1685 IS
1686 l_procedure VARCHAR2(100);
1687 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1688 l_element_name pay_element_types_f.element_name%TYPE;
1689 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
1690 l_eff_start_date DATE;
1691 l_eff_end_date DATE;
1692 l_start_date DATE;
1693 l_delete_warning BOOLEAN;
1694
1695 BEGIN
1696 g_debug := hr_utility.debug_enabled ;
1697 l_procedure := g_package || 'delete_gratuity_entry' ;
1698 p_message_name := 'SUCCESS';
1699 pay_in_utils.null_message (p_token_name, p_token_value);
1700
1701 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1702
1703 l_element_name := g_gratuity_et;
1704
1705 IF check_ee_exists
1706 (p_element_name => l_element_name
1707 ,p_assignment_id => g_assignment_id
1708 ,p_effective_date => p_actual_termination_date
1709 ,p_element_entry_id => l_element_entry_id
1710 ,p_start_date => l_start_date
1711 ,p_ee_ovn => l_ee_ovn)
1712 THEN
1713 pay_in_utils.set_location(g_debug,l_procedure,20);
1714 IF g_debug THEN
1715 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
1716 END IF;
1717
1718 IF NOT is_element_processed
1719 (p_assignment_id =>g_assignment_id,
1720 p_element_name =>l_element_name)
1721 THEN
1722 pay_element_entry_api.delete_element_entry
1723 (p_datetrack_delete_mode => hr_api.g_delete
1724 ,p_effective_date => l_start_date
1725 ,p_element_entry_id => l_element_entry_id
1726 ,p_object_version_number => l_ee_ovn
1727 ,p_effective_start_date => l_eff_start_date
1728 ,p_effective_end_date => l_eff_end_date
1729 ,p_delete_warning => l_delete_warning
1730 ) ;
1731 END IF ;
1732 pay_in_utils.set_location(g_debug,l_procedure,30);
1733
1734 END IF;
1735 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1736
1737 END delete_gratuity_entry;
1738
1739 --------------------------------------------------------------------------
1740 -- --
1741 -- Name : GET_NOTICE_IVS --
1742 -- Type : Procedure --
1743 -- Access : Private --
1744 -- Description : Procedure to set the IVs of Notice Information --
1745 -- --
1746 -- Parameters : --
1747 -- IN : p_actual_termination_date DATE --
1748 -- OUT : p_notice_period_days NUMBER --
1749 -- Change History : --
1750 --------------------------------------------------------------------------
1751 -- Rev# Date Userid Description --
1752 --------------------------------------------------------------------------
1753 -- 1.0 19-OCT-04 statkar Created this procedure --
1754 -- 1.1. 28-Oct-04 statkar Changed the logic for notice days --
1755 -- 1.2. 28-Oct-04 rsaharay To consider the Notice Period & --
1756 -- Units mentioned in the Assignment Form --
1757 --------------------------------------------------------------------------
1758 PROCEDURE get_notice_ivs
1759 (p_actual_termination_date IN DATE
1760 ,p_business_group_id IN NUMBER
1761 ,p_notice_period_days OUT NOCOPY NUMBER
1762 )
1763 IS
1764 l_procedure VARCHAR2(100);
1765
1766 CURSOR csr_emp_catg IS
1767 SELECT NVL(paf.employee_category,'IN_DEF'),
1768 paf.notice_period,paf.notice_period_uom
1769 FROM per_assignments_f paf
1770 WHERE paf.assignment_id = g_assignment_id
1771 AND p_actual_termination_date BETWEEN paf.effective_start_date
1772 AND paf.effective_end_date;
1773
1774 CURSOR csr_np (p_emp_category IN VARCHAR2) IS
1775 SELECT hoi.org_information2
1776 FROM hr_organization_information hoi
1777 WHERE hoi.organization_id = p_business_group_id
1778 AND hoi.org_information_context = 'PER_IN_NOTICE_DF'
1779 AND hoi.org_information1 = p_emp_category;
1780
1781 l_emp_category per_assignments_f.employee_category%TYPE;
1782 l_notice_duration NUMBER;
1783 l_notice_uom VARCHAR2(2);
1784 l_shortfall NUMBER;
1785 l_message VARCHAR2(1000);
1786
1787 BEGIN
1788 g_debug := hr_utility.debug_enabled ;
1789 l_procedure := g_package ||'get_notice_ivs' ;
1790 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1791
1792 --
1793 -- Step 1. To fetch the Notice Period Days from the Assignment Form
1794 --
1795 OPEN csr_emp_catg;
1796 FETCH csr_emp_catg
1797 INTO l_emp_category,l_notice_duration,l_notice_uom;
1798 CLOSE csr_emp_catg;
1799
1800 IF g_debug THEN
1801 pay_in_utils.trace('Employee Category ',l_emp_category);
1802 END IF;
1803
1804 --
1805 -- Step 2. To fetch the Notice Period Days from the DFF
1806 --
1807 IF l_notice_duration IS NULL THEN
1808 OPEN csr_np (l_emp_category);
1809 FETCH csr_np
1810 INTO l_notice_duration;
1811
1812 IF csr_np%NOTFOUND THEN
1813 CLOSE csr_np;
1814 OPEN csr_np ('IN_DEF');
1815 FETCH csr_np INTO l_notice_duration;
1816 IF csr_np%NOTFOUND OR l_notice_duration IS NULL THEN
1817 CLOSE csr_np;
1818 l_notice_duration := 0;
1819 p_notice_period_days := 0;
1820 RETURN;
1821 END IF;
1822 CLOSE csr_np;
1823 ELSE
1824 CLOSE csr_np;
1825 END IF;
1826 END IF ;
1827
1828 IF l_notice_uom IS NULL OR l_notice_uom = 'D' THEN
1829 IF g_debug THEN
1830 pay_in_utils.trace('Notice Period Duration ',l_notice_duration);
1831 END IF;
1832 --Bug 3977010. Added +1
1833 l_shortfall := ROUND(l_notice_duration - (p_actual_termination_date - g_notified_date + 1),0);
1834 IF l_shortfall < 0 THEN
1835 p_notice_period_days := 0;
1836 ELSE
1837 p_notice_period_days := l_shortfall;
1838 END IF;
1839 ELSE
1840 p_notice_period_days := 0;
1841 END IF ;
1842 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,90);
1843
1844 END get_notice_ivs;
1845
1846 --------------------------------------------------------------------------
1847 -- --
1848 -- Name : CREATE_NOTICE_ENTRY --
1849 -- Type : PROCEDURE --
1850 -- Access : Private --
1851 -- Description : Procedure to handle creation of Gratuity entry for --
1852 -- terminated employee based on conditions as required --
1853 -- for India Localization. --
1854 -- --
1855 -- Parameters : --
1856 -- IN : p_period_of_service_id NUMBER --
1857 -- p_business_group_id NUMBER --
1858 -- p_actual_termination_date DATE --
1859 -- p_calling_procedure VARCHAR2 --
1860 -- OUT : p_message_name VARCHAR2 --
1861 -- p_token_name pay_in_utils.char_tab_type--
1862 -- p_token_value pay_in_utils.char_tab_type--
1863 -- Change History : --
1864 --------------------------------------------------------------------------
1865 -- Rev# Date Userid Description --
1866 --------------------------------------------------------------------------
1867 -- 1.0 19-OCT-04 statkar Created this procedure --
1868 --------------------------------------------------------------------------
1869 PROCEDURE create_notice_entry
1870 (p_period_of_service_id IN NUMBER
1871 ,p_business_group_id IN NUMBER
1872 ,p_actual_termination_date IN DATE
1873 ,p_calling_procedure IN VARCHAR2
1874 ,p_message_name OUT NOCOPY VARCHAR2
1875 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1876 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1877 )
1878 IS
1879 l_procedure VARCHAR2(100);
1880 l_element_name pay_element_types_f.element_name%TYPE ;
1881 l_entry_values t_entry_values_tab;
1882 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1883 l_start_date DATE;
1884 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
1885 l_ee_exists BOOLEAN;
1886 l_element_processed BOOLEAN;
1887
1888 BEGIN
1889 g_debug := hr_utility.debug_enabled ;
1890 l_procedure := g_package || 'create_notice_entry' ;
1891 p_message_name := 'SUCCESS';
1892 pay_in_utils.null_message (p_token_name, p_token_value);
1893
1894 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1895
1896 l_ee_exists := check_ee_exists
1897 (p_element_name => g_notice_et--l_element_name
1898 ,p_assignment_id => g_assignment_id
1899 ,p_effective_date => p_actual_termination_date
1900 ,p_element_entry_id => l_element_entry_id
1901 ,p_start_date => l_start_date
1902 ,p_ee_ovn => l_ee_ovn);
1903
1904 IF l_ee_exists THEN
1905 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
1906 RETURN;
1907 END IF;
1908
1909 l_element_processed := is_element_processed
1910 (p_assignment_id =>g_assignment_id,
1911 p_element_name =>g_notice_et);
1912
1913 IF l_element_processed THEN
1914 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
1915 RETURN;
1916 END IF;
1917 --
1918 -- Element Name : Notice Period Information
1919 --
1920 -- Input Value are:
1921 --
1922 -- 1. Notice Period Amount - Null
1923 -- 2. Notice From - EE
1924 -- 3. Notice Period Days - Calculate
1925 -- 4. Period Days - 30
1926 -- 5. Component Name - 'Notice Period Pay'
1927 --
1928 l_entry_values(1).entry_value := null;
1929 l_entry_values(2).entry_value := 'EE';
1930 get_notice_ivs (p_actual_termination_date => p_actual_termination_date
1931 ,p_business_group_id => p_business_group_id
1932 ,p_notice_period_days => l_entry_values(3).entry_value
1933 );
1934 l_entry_values(4).entry_value := '30';
1935 l_entry_values(5).entry_value := g_notice_cn;
1936 l_element_name := g_notice_et;
1937
1938 -- Bug 3977010
1939 IF l_entry_values(3).entry_value <= 0 THEN
1940 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1941 RETURN;
1942 END IF;
1943
1944 pay_in_utils.set_location(g_debug,l_procedure,30);
1945 create_entry
1946 (p_effective_date => p_actual_termination_date
1947 ,p_business_group_id => p_business_group_id
1948 ,p_element_name => l_element_name
1949 ,p_entry_values => l_entry_values
1950 ,p_calling_procedure => p_calling_procedure
1951 ,p_message_name => p_message_name
1952 ,p_token_name => p_token_name
1953 ,p_token_value => p_token_value
1954 );
1955
1956 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1957
1958 END create_notice_entry;
1959
1960 --------------------------------------------------------------------------
1961 -- --
1962 -- Name : DELETE_NOTICE_ENTRY --
1963 -- Type : Procedure --
1964 -- Access : Private --
1965 -- Description : Procedure to delete 'Notice Period Information' EE --
1966 -- if termination is reversed for the employee. --
1967 -- --
1968 -- Parameters : --
1969 -- IN : p_period_of_service_id NUMBER --
1970 -- p_business_group_id NUMBER --
1971 -- p_actual_termination_date DATE --
1972 -- p_calling_procedure VARCHAR2 --
1973 -- OUT : p_message_name VARCHAR2 --
1974 -- p_token_name pay_in_utils.char_tab_type--
1975 -- p_token_value pay_in_utils.char_tab_type--
1976 -- Change History : --
1977 --------------------------------------------------------------------------
1978 -- Rev# Date Userid Description --
1979 --------------------------------------------------------------------------
1980 -- 1.0 19-OCT-04 statkar Created this procedure --
1981 --------------------------------------------------------------------------
1982 PROCEDURE delete_notice_entry
1983 (p_period_of_service_id IN NUMBER
1984 ,p_business_group_id IN NUMBER
1985 ,p_actual_termination_date IN DATE
1986 ,p_calling_procedure IN VARCHAR2
1987 ,p_message_name OUT NOCOPY VARCHAR2
1988 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1989 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1990 )
1991 IS
1992 l_procedure VARCHAR2(100);
1993 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1994 l_element_name pay_element_types_f.element_name%TYPE;
1995 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
1996 l_eff_start_date DATE;
1997 l_eff_end_date DATE;
1998 l_start_date DATE;
1999 l_delete_warning BOOLEAN;
2000
2001 BEGIN
2002 g_debug := hr_utility.debug_enabled ;
2003 l_procedure := g_package || 'delete_notice_entry' ;
2004 p_message_name := 'SUCCESS';
2005 pay_in_utils.null_message (p_token_name, p_token_value);
2006
2007 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2008
2009 l_element_name := g_notice_et;
2010
2011 IF check_ee_exists
2012 (p_element_name => l_element_name
2013 ,p_assignment_id => g_assignment_id
2014 ,p_effective_date => p_actual_termination_date
2015 ,p_element_entry_id => l_element_entry_id
2016 ,p_start_date => l_start_date
2017 ,p_ee_ovn => l_ee_ovn)
2018 THEN
2019 pay_in_utils.set_location(g_debug,l_procedure,20);
2020 IF g_debug THEN
2021 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2022 END IF;
2023 IF NOT is_element_processed
2024 (p_assignment_id =>g_assignment_id,
2025 p_element_name =>l_element_name)
2026 THEN
2027 pay_element_entry_api.delete_element_entry
2028 (p_datetrack_delete_mode => hr_api.g_delete
2029 ,p_effective_date => l_start_date
2030 ,p_element_entry_id => l_element_entry_id
2031 ,p_object_version_number => l_ee_ovn
2032 ,p_effective_start_date => l_eff_start_date
2033 ,p_effective_end_date => l_eff_end_date
2034 ,p_delete_warning => l_delete_warning
2035 ) ;
2036 END IF ;
2037 pay_in_utils.set_location(g_debug,l_procedure,30);
2038
2039 END IF;
2040 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2041
2042 END delete_notice_entry;
2043 --------------------------------------------------------------------------
2044 -- --
2045 -- Name : CREATE_RETRENCMENT_ENTRY --
2046 -- Type : PROCEDURE --
2047 -- Access : Private --
2048 -- Description : Procedure to handle creation of Retrenchment EE for --
2049 -- terminated employee based on conditions as required --
2050 -- for India Localization. --
2051 -- --
2052 -- Parameters : --
2053 -- IN : p_period_of_service_id NUMBER --
2054 -- p_business_group_id NUMBER --
2055 -- p_actual_termination_date DATE --
2056 -- p_calling_procedure VARCHAR2 --
2057 -- OUT : p_message_name VARCHAR2 --
2058 -- p_token_name pay_in_utils.char_tab_type--
2059 -- p_token_value pay_in_utils.char_tab_type--
2060 --------------------------------------------------------------------------
2061 PROCEDURE create_retrenchment_entry
2062 (p_period_of_service_id IN NUMBER
2063 ,p_business_group_id IN NUMBER
2064 ,p_actual_termination_date IN DATE
2065 ,p_calling_procedure IN VARCHAR2
2066 ,p_message_name OUT NOCOPY VARCHAR2
2067 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2068 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2069 )
2070 IS
2071 l_procedure VARCHAR2(100);
2072 l_element_name pay_element_types_f.element_name%TYPE ;
2073 l_entry_values t_entry_values_tab;
2074 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2075 l_start_date DATE;
2076 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2077 l_ee_exists BOOLEAN;
2078 l_element_processed BOOLEAN;
2079
2080 BEGIN
2081 g_debug := hr_utility.debug_enabled ;
2082 l_procedure := g_package || 'create_retrenchment_entry' ;
2083 p_message_name := 'SUCCESS';
2084 pay_in_utils.null_message (p_token_name, p_token_value);
2085
2086 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2087
2088 l_ee_exists := check_ee_exists
2089 (p_element_name => g_retrenchment_et--l_element_name
2090 ,p_assignment_id => g_assignment_id
2091 ,p_effective_date => p_actual_termination_date
2092 ,p_element_entry_id => l_element_entry_id
2093 ,p_start_date => l_start_date
2094 ,p_ee_ovn => l_ee_ovn);
2095
2096 IF l_ee_exists THEN
2097 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2098 RETURN;
2099 END IF;
2100
2101 l_element_processed := is_element_processed
2102 (p_assignment_id =>g_assignment_id,
2103 p_element_name =>g_retrenchment_et);
2104
2105 IF l_element_processed THEN
2106 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2107 RETURN;
2108 END IF;
2109 --
2110 -- Element Name : Retrenchment Compensation Information
2111 --
2112 -- Input Value are:
2113 --
2114 -- 1. Taxable Amount - Null
2115 -- 2. Non Taxable Amount - Null
2116 -- 3. Component Name - 'Retrenchment'
2117 --
2118 l_entry_values(1).entry_value := null;
2119 l_entry_values(2).entry_value := null;
2120 l_entry_values(3).entry_value := g_retrenchment_cn;
2121 l_element_name := g_retrenchment_et;
2122
2123 pay_in_utils.set_location(g_debug,l_procedure,20);
2124 create_entry
2125 (p_effective_date => p_actual_termination_date
2126 ,p_business_group_id => p_business_group_id
2127 ,p_element_name => l_element_name
2128 ,p_entry_values => l_entry_values
2129 ,p_calling_procedure => p_calling_procedure
2130 ,p_message_name => p_message_name
2131 ,p_token_name => p_token_name
2132 ,p_token_value => p_token_value
2133 );
2134
2135 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2136
2137 END create_retrenchment_entry;
2138
2139 --------------------------------------------------------------------------
2140 -- --
2141 -- Name : DELETE_RETRENCMENT_ENTRY --
2142 -- Type : Procedure --
2143 -- Access : Private --
2144 -- Description : Procedure to delete 'Retrencment Information' EE --
2145 -- if termination is reversed for the employee. --
2146 -- --
2147 -- Parameters : --
2148 -- IN : p_period_of_service_id NUMBER --
2149 -- p_business_group_id NUMBER --
2150 -- p_actual_termination_date DATE --
2151 -- p_calling_procedure VARCHAR2 --
2152 -- OUT : p_message_name VARCHAR2 --
2153 -- p_token_name pay_in_utils.char_tab_type--
2154 -- p_token_value pay_in_utils.char_tab_type--
2155 -- Change History : --
2156 --------------------------------------------------------------------------
2157 -- Rev# Date Userid Description --
2158 --------------------------------------------------------------------------
2159 -- 1.0 19-OCT-04 statkar Created this procedure --
2160 --------------------------------------------------------------------------
2161 PROCEDURE delete_retrenchment_entry
2162 (p_period_of_service_id IN NUMBER
2163 ,p_business_group_id IN NUMBER
2164 ,p_actual_termination_date IN DATE
2165 ,p_calling_procedure IN VARCHAR2
2166 ,p_message_name OUT NOCOPY VARCHAR2
2167 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2168 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2169 )
2170 IS
2171 l_procedure VARCHAR2(100);
2172 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2173 l_element_name pay_element_types_f.element_name%TYPE;
2174 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2175 l_start_date DATE;
2176 l_eff_start_date DATE;
2177 l_eff_end_date DATE;
2178 l_delete_warning BOOLEAN;
2179 l_assignment_id NUMBER;
2180 l_payroll_id NUMBER;
2181 l_hire_date DATE;
2182
2183 BEGIN
2184 g_debug := hr_utility.debug_enabled ;
2185 l_procedure := g_package || 'delete_retrenchment_entry' ;
2186 p_message_name := 'SUCCESS';
2187 pay_in_utils.null_message (p_token_name, p_token_value);
2188
2189 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2190
2191 l_element_name := g_retrenchment_et;
2192
2193 IF check_ee_exists
2194 (p_element_name => l_element_name
2195 ,p_assignment_id => g_assignment_id
2196 ,p_effective_date => p_actual_termination_date
2197 ,p_element_entry_id => l_element_entry_id
2198 ,p_start_date => l_start_date
2199 ,p_ee_ovn => l_ee_ovn)
2200 THEN
2201 pay_in_utils.set_location(g_debug,l_procedure,20);
2202 IF g_debug THEN
2203 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2204 END IF;
2205 IF NOT is_element_processed
2206 (p_assignment_id =>g_assignment_id,
2207 p_element_name =>l_element_name)
2208 THEN
2209 pay_element_entry_api.delete_element_entry
2210 (p_datetrack_delete_mode => hr_api.g_delete
2211 ,p_effective_date => l_start_date
2212 ,p_element_entry_id => l_element_entry_id
2213 ,p_object_version_number => l_ee_ovn
2214 ,p_effective_start_date => l_eff_start_date
2215 ,p_effective_end_date => l_eff_end_date
2216 ,p_delete_warning => l_delete_warning
2217 ) ;
2218 END IF ;
2219 pay_in_utils.set_location(g_debug,l_procedure,30);
2220
2221 END IF;
2222 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2223
2224 END delete_retrenchment_entry;
2225
2226 --------------------------------------------------------------------------
2227 -- --
2228 -- Name : CREATE_VRS_ENTRY --
2229 -- Type : PROCEDURE --
2230 -- Access : Private --
2231 -- Description : Procedure to handle creation of VRS elem entry for --
2232 -- terminated employee based on conditions as required --
2233 -- for India Localization. --
2234 -- --
2235 -- Parameters : --
2236 -- IN : p_period_of_service_id NUMBER --
2237 -- p_business_group_id NUMBER --
2238 -- p_actual_termination_date DATE --
2239 -- p_calling_procedure VARCHAR2 --
2240 -- OUT : p_message_name VARCHAR2 --
2241 -- p_token_name pay_in_utils.char_tab_type--
2242 -- p_token_value pay_in_utils.char_tab_type--
2243 -- Change History : --
2244 --------------------------------------------------------------------------
2245 -- Rev# Date Userid Description --
2246 --------------------------------------------------------------------------
2247 -- 1.0 19-OCT-04 statkar Created this procedure --
2248 --------------------------------------------------------------------------
2249 PROCEDURE create_vrs_entry
2250 (p_period_of_service_id IN NUMBER
2251 ,p_business_group_id IN NUMBER
2252 ,p_actual_termination_date IN DATE
2253 ,p_calling_procedure IN VARCHAR2
2254 ,p_message_name OUT NOCOPY VARCHAR2
2255 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2256 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2257 )
2258 IS
2259 l_procedure VARCHAR2(100);
2260 l_element_name pay_element_types_f.element_name%TYPE ;
2261 l_entry_values t_entry_values_tab;
2262 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2263 l_start_date DATE;
2264 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2265 l_ee_exists BOOLEAN;
2266 l_element_processed BOOLEAN;
2267 l_prev_earn NUMBER;
2268 l_prev_tds NUMBER;
2269 l_prev_pt NUMBER;
2270 l_prev_ent NUMBER;
2271 l_prev_pf NUMBER;
2272 l_prev_super NUMBER;
2273 l_govt_ent_alw NUMBER;
2274 l_prev_grat NUMBER;
2275 l_leave_enc NUMBER;
2276 l_retr_pay NUMBER;
2277 l_designation VARCHAR2(100);
2278 l_annual_sal NUMBER;
2279 l_pf_number VARCHAR2(30);
2280 l_pf_estab_code VARCHAR2(15);
2281 l_epf_number VARCHAR2(30);
2282 l_emplr_class VARCHAR2(10);
2283 l_ltc_curr_block NUMBER;
2284 l_vrs_amount NUMBER;
2285 l_prev_sc NUMBER;
2286 l_prev_cess NUMBER;
2287 l_dummy NUMBER;
2288 l_prev_exemp_80gg NUMBER;
2289 l_prev_exemp_80ccd NUMBER;
2290 l_prev_med_reimburse NUMBER;
2291 l_prev_sec_and_he_cess NUMBER;
2292
2293
2294 BEGIN
2295 g_debug := hr_utility.debug_enabled ;
2296 l_procedure := g_package || 'create_vrs_entry' ;
2297 p_message_name := 'SUCCESS';
2298 pay_in_utils.null_message (p_token_name, p_token_value);
2299
2300 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2301 -- Fix for Bug 4027040 Start
2302
2303 l_prev_earn := 0;
2304 l_prev_tds := 0;
2305 l_prev_pt := 0;
2306 l_prev_ent := 0;
2307 l_prev_pf := 0;
2308 l_prev_super := 0;
2309 l_govt_ent_alw := 0;
2310 l_prev_grat := 0;
2311 l_leave_enc := 0;
2312 l_retr_pay := 0;
2313 l_designation := 'X';
2314 l_annual_sal := 0;
2315 l_pf_number := 'X';
2316 l_pf_estab_code := 'X';
2317 l_epf_number := 'X';
2318 l_emplr_class := 'X';
2319 l_ltc_curr_block := 0;
2320 l_vrs_amount := 0;
2321 l_prev_sc := 0;
2322 l_prev_cess := 0;
2323 l_dummy := 0;
2324 l_prev_exemp_80gg:=0;
2325 l_prev_exemp_80ccd:=0;
2326 l_prev_med_reimburse := 0;
2327 l_prev_sec_and_he_cess :=0;
2328
2329 l_dummy := pay_in_tax_utils.prev_emplr_details(g_assignment_id,
2330 p_actual_termination_date,
2331 l_prev_earn,
2332 l_prev_tds,
2333 l_prev_pt,
2334 l_prev_ent,
2335 l_prev_pf,
2336 l_prev_super,
2337 l_govt_ent_alw,
2338 l_prev_grat,
2339 l_leave_enc,
2340 l_retr_pay,
2341 l_designation,
2342 l_annual_sal,
2343 l_pf_number,
2344 l_pf_estab_code,
2345 l_epf_number,
2346 l_emplr_class,
2347 l_ltc_curr_block,
2348 l_vrs_amount,
2349 l_prev_sc,
2350 l_prev_cess,
2351 l_prev_exemp_80gg,
2352 l_prev_med_reimburse,
2353 l_prev_sec_and_he_cess,
2354 l_prev_exemp_80ccd);
2355 IF l_vrs_amount <> 0 THEN
2356 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2357 RETURN;
2358 END IF;
2359
2360 -- Fix for Bug 4027040 End
2361 l_ee_exists := check_ee_exists
2362 (p_element_name => g_vrs_et--l_element_name
2363 ,p_assignment_id => g_assignment_id
2364 ,p_effective_date => p_actual_termination_date
2365 ,p_element_entry_id => l_element_entry_id
2366 ,p_start_date => l_start_date
2367 ,p_ee_ovn => l_ee_ovn);
2368
2369 IF l_ee_exists THEN
2370 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2371 RETURN;
2372 END IF;
2373
2374 l_element_processed := is_element_processed
2375 (p_assignment_id =>g_assignment_id,
2376 p_element_name =>g_vrs_et);
2377
2378 IF l_element_processed THEN
2379 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2380 RETURN;
2381 END IF;
2382 --
2383 -- Element Name : Voluntary Retirement Information
2384 --
2385 -- Input Value are:
2386 --
2387 -- 1. Taxable Amount - Null
2388 -- 2. Non Taxable Amount - Null
2389 -- 3. Component Name - 'Voluntary Retirement'
2390
2391 l_entry_values(1).entry_value := null;
2392 l_entry_values(2).entry_value := null;
2393 l_entry_values(3).entry_value := g_vrs_cn;
2394 l_element_name := g_vrs_et;
2395
2396 pay_in_utils.set_location(g_debug,l_procedure,20);
2397 create_entry
2398 (p_effective_date => p_actual_termination_date
2399 ,p_business_group_id => p_business_group_id
2400 ,p_element_name => l_element_name
2401 ,p_entry_values => l_entry_values
2402 ,p_calling_procedure => p_calling_procedure
2403 ,p_message_name => p_message_name
2404 ,p_token_name => p_token_name
2405 ,p_token_value => p_token_value
2406 );
2407
2408
2409 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2410
2411 END create_vrs_entry;
2412
2413 --------------------------------------------------------------------------
2414 -- --
2415 -- Name : DELETE_VRS_ENTRY --
2416 -- Type : Procedure --
2417 -- Access : Private --
2418 -- Description : Procedure to delete 'Voluntary Retirement Information'--
2419 -- if termination is reversed for the employee. --
2420 -- --
2421 -- Parameters : --
2422 -- IN : p_period_of_service_id NUMBER --
2423 -- p_business_group_id NUMBER --
2424 -- p_actual_termination_date DATE --
2425 -- p_calling_procedure VARCHAR2 --
2426 -- OUT : p_message_name VARCHAR2 --
2427 -- p_token_name pay_in_utils.char_tab_type--
2428 -- p_token_value pay_in_utils.char_tab_type--
2429 -- Change History : --
2430 --------------------------------------------------------------------------
2431 -- Rev# Date Userid Description --
2432 --------------------------------------------------------------------------
2433 -- 1.0 19-OCT-04 statkar Created this procedure --
2434 --------------------------------------------------------------------------
2435 PROCEDURE delete_vrs_entry
2436 (p_period_of_service_id IN NUMBER
2437 ,p_business_group_id IN NUMBER
2438 ,p_actual_termination_date IN DATE
2439 ,p_calling_procedure IN VARCHAR2
2440 ,p_message_name OUT NOCOPY VARCHAR2
2441 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2442 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2443 )
2444 IS
2445 l_procedure VARCHAR2(100);
2446 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2447 l_element_name pay_element_types_f.element_name%TYPE;
2448 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2449 l_eff_start_date DATE;
2450 l_eff_end_date DATE;
2451 l_start_date DATE;
2452 l_delete_warning BOOLEAN;
2453 l_assignment_id NUMBER;
2454 l_payroll_id NUMBER;
2455 l_hire_date DATE;
2456
2457 BEGIN
2458 g_debug := hr_utility.debug_enabled ;
2459 l_procedure := g_package || 'delete_vrs_entry' ;
2460 p_message_name := 'SUCCESS';
2461 pay_in_utils.null_message (p_token_name, p_token_value);
2462
2463 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2464
2465 l_element_name := g_vrs_et;
2466
2467 IF check_ee_exists
2468 (p_element_name => l_element_name
2469 ,p_assignment_id => g_assignment_id
2470 ,p_effective_date => p_actual_termination_date
2471 ,p_element_entry_id => l_element_entry_id
2472 ,p_start_date => l_start_date
2473 ,p_ee_ovn => l_ee_ovn)
2474 THEN
2475 pay_in_utils.set_location(g_debug,l_procedure,20);
2476 IF g_debug THEN
2477 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2478 END IF;
2479
2480 IF NOT is_element_processed
2481 (p_assignment_id =>g_assignment_id,
2482 p_element_name =>l_element_name)
2483 THEN
2484 pay_element_entry_api.delete_element_entry
2485 (p_datetrack_delete_mode => hr_api.g_delete
2486 ,p_effective_date => l_start_date
2487 ,p_element_entry_id => l_element_entry_id
2488 ,p_object_version_number => l_ee_ovn
2489 ,p_effective_start_date => l_eff_start_date
2490 ,p_effective_end_date => l_eff_end_date
2491 ,p_delete_warning => l_delete_warning
2492 ) ;
2493 END IF ;
2494 pay_in_utils.set_location(g_debug,l_procedure,30);
2495
2496 END IF;
2497 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2498
2499 END delete_vrs_entry;
2500
2501 --------------------------------------------------------------------------
2502 -- --
2503 -- Name : CREATE_PENSION_ENTRY --
2504 -- Type : PROCEDURE --
2505 -- Access : Private --
2506 -- Description : Procedure to handle creation of Comm Pension EE for --
2507 -- terminated employee based on conditions as required --
2508 -- for India Localization. --
2509 -- --
2510 -- Parameters : --
2511 -- IN : p_period_of_service_id NUMBER --
2512 -- p_business_group_id NUMBER --
2513 -- p_actual_termination_date DATE --
2514 -- p_calling_procedure VARCHAR2 --
2515 -- OUT : p_message_name VARCHAR2 --
2516 -- p_token_name pay_in_utils.char_tab_type--
2517 -- p_token_value pay_in_utils.char_tab_type--
2518 -- Change History : --
2519 --------------------------------------------------------------------------
2520 -- Rev# Date Userid Description --
2521 --------------------------------------------------------------------------
2522 -- 1.0 19-OCT-04 statkar Created this procedure --
2523 --------------------------------------------------------------------------
2524 PROCEDURE create_pension_entry
2525 (p_period_of_service_id IN NUMBER
2526 ,p_business_group_id IN NUMBER
2527 ,p_actual_termination_date IN DATE
2528 ,p_calling_procedure IN VARCHAR2
2529 ,p_message_name OUT NOCOPY VARCHAR2
2530 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2531 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2532 )
2533 IS
2534 l_procedure VARCHAR2(100);
2535 l_element_name pay_element_types_f.element_name%TYPE ;
2536 l_entry_values t_entry_values_tab;
2537 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2538 l_start_date DATE;
2539 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2540 l_ee_exists BOOLEAN;
2541 l_element_processed BOOLEAN;
2542
2543 BEGIN
2544 g_debug := hr_utility.debug_enabled ;
2545 l_procedure := g_package || 'create_pension_entry' ;
2546 p_message_name := 'SUCCESS';
2547 pay_in_utils.null_message (p_token_name, p_token_value);
2548
2549 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2550
2551 l_ee_exists := check_ee_exists
2552 (p_element_name => g_pension_et--l_element_name
2553 ,p_assignment_id => g_assignment_id
2554 ,p_effective_date => p_actual_termination_date
2555 ,p_element_entry_id => l_element_entry_id
2556 ,p_start_date => l_start_date
2557 ,p_ee_ovn => l_ee_ovn);
2558
2559 IF l_ee_exists THEN
2560 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2561 RETURN;
2562 END IF;
2563
2564 l_element_processed := is_element_processed
2565 (p_assignment_id =>g_assignment_id,
2566 p_element_name =>g_pension_et);
2567
2568 IF l_element_processed THEN
2569 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2570 RETURN;
2571 END IF;
2572
2573 --
2574 -- Element Name : Commuted Pension Information
2575 --
2576 -- Input Value are:
2577 --
2578 -- 1. Commuted Pension - Null
2579 -- 2. Normal Pension - Null
2580 -- 3. Component Name - 'Commuted Pension'
2581 --
2582 l_entry_values(1).entry_value := null;
2583 l_entry_values(2).entry_value := null;
2584 l_entry_values(3).entry_value := g_pension_cn;
2585 l_element_name := g_pension_et;
2586
2587 pay_in_utils.set_location(g_debug,l_procedure,20);
2588 create_entry
2589 (p_effective_date => p_actual_termination_date
2590 ,p_business_group_id => p_business_group_id
2591 ,p_element_name => l_element_name
2592 ,p_entry_values => l_entry_values
2593 ,p_calling_procedure => p_calling_procedure
2594 ,p_message_name => p_message_name
2595 ,p_token_name => p_token_name
2596 ,p_token_value => p_token_value
2597 );
2598
2599
2600 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2601
2602 END create_pension_entry;
2603
2604 --------------------------------------------------------------------------
2605 -- --
2606 -- Name : DELETE_PENSION_ENTRY --
2607 -- Type : Procedure --
2608 -- Access : Private --
2609 -- Description : Procedure to delete 'Commuted Pension Information' EE --
2610 -- if termination is reversed for the employee. --
2611 -- --
2612 -- Parameters : --
2613 -- IN : p_period_of_service_id NUMBER --
2614 -- p_business_group_id NUMBER --
2615 -- p_actual_termination_date DATE --
2616 -- p_calling_procedure VARCHAR2 --
2617 -- OUT : p_message_name VARCHAR2 --
2618 -- p_token_name pay_in_utils.char_tab_type--
2619 -- p_token_value pay_in_utils.char_tab_type--
2620 -- Change History : --
2621 --------------------------------------------------------------------------
2622 -- Rev# Date Userid Description --
2623 --------------------------------------------------------------------------
2624 -- 1.0 19-OCT-04 statkar Created this procedure --
2625 --------------------------------------------------------------------------
2626 PROCEDURE delete_pension_entry
2627 (p_period_of_service_id IN NUMBER
2628 ,p_business_group_id IN NUMBER
2629 ,p_actual_termination_date IN DATE
2630 ,p_calling_procedure IN VARCHAR2
2631 ,p_message_name OUT NOCOPY VARCHAR2
2632 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2633 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2634 )
2635 IS
2636 l_procedure VARCHAR2(100);
2637 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2638 l_element_name pay_element_types_f.element_name%TYPE;
2639 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2640 l_eff_start_date DATE;
2641 l_eff_end_date DATE;
2642 l_start_date DATE;
2643 l_delete_warning BOOLEAN;
2644 l_assignment_id NUMBER;
2645 l_payroll_id NUMBER;
2646 l_hire_date DATE;
2647
2648 BEGIN
2649 g_debug := hr_utility.debug_enabled ;
2650 l_procedure := g_package || 'delete_pension_entry' ;
2651 p_message_name := 'SUCCESS';
2652 pay_in_utils.null_message (p_token_name, p_token_value);
2653
2654 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2655
2656 l_element_name := g_pension_et;
2657
2658 IF check_ee_exists
2659 (p_element_name => l_element_name
2660 ,p_assignment_id => g_assignment_id
2661 ,p_effective_date => p_actual_termination_date
2662 ,p_element_entry_id => l_element_entry_id
2663 ,p_start_date => l_start_date
2664 ,p_ee_ovn => l_ee_ovn)
2665 THEN
2666 pay_in_utils.set_location(g_debug,l_procedure,20);
2667 IF g_debug THEN
2668 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2669 END IF;
2670 IF NOT is_element_processed
2671 (p_assignment_id =>g_assignment_id,
2672 p_element_name =>l_element_name)
2673 THEN
2674 pay_element_entry_api.delete_element_entry
2675 (p_datetrack_delete_mode => hr_api.g_delete
2676 ,p_effective_date => l_start_date
2677 ,p_element_entry_id => l_element_entry_id
2678 ,p_object_version_number => l_ee_ovn
2679 ,p_effective_start_date => l_eff_start_date
2680 ,p_effective_end_date => l_eff_end_date
2681 ,p_delete_warning => l_delete_warning
2682 ) ;
2683 END IF ;
2684 pay_in_utils.set_location(g_debug,l_procedure,30);
2685 END IF;
2686 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2687
2688 END delete_pension_entry;
2689
2690 --------------------------------------------------------------------------
2691 -- --
2692 -- Name : create_advances_entry --
2693 -- Type : Procedure --
2694 -- Access : Public --
2695 -- Description : Procedure to delete 'Gratuity Information' Entry --
2696 -- if termination is reversed for the employee. --
2697 -- --
2698 -- Parameters : --
2699 -- IN : p_period_of_service_id NUMBER --
2700 -- p_business_group_id NUMBER --
2701 -- p_actual_termination_date DATE --
2702 -- p_calling_procedure VARCHAR2 --
2703 -- OUT : p_message_name VARCHAR2 --
2704 -- p_token_name pay_in_utils.char_tab_type--
2705 -- p_token_value pay_in_utils.char_tab_type--
2706 -- Change History : --
2707 --------------------------------------------------------------------------
2708 -- Rev# Date Userid Description --
2709 --------------------------------------------------------------------------
2710 -- 1.0 19-OCT-04 statkar Created this procedure --
2711 --------------------------------------------------------------------------
2712 PROCEDURE create_advances_entry
2713 (p_period_of_service_id IN NUMBER
2714 ,p_business_group_id IN NUMBER
2715 ,p_actual_termination_date IN DATE
2716 ,p_calling_procedure IN VARCHAR2
2717 ,p_message_name OUT NOCOPY VARCHAR2
2718 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2719 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2720 )
2721 IS
2722 l_procedure VARCHAR2(100);
2723 l_element_name pay_element_types_f.element_name%TYPE ;
2724 l_entry_values t_entry_values_tab;
2725 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2726 l_start_date DATE;
2727 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2728 l_ee_exists BOOLEAN;
2729 l_element_processed BOOLEAN;
2730 l_def_bal_id NUMBER;
2731 l_asact_id NUMBER;
2732 l_adv_element_name pay_element_types_f.element_name%TYPE ;
2733 p_element_link_id NUMBER;
2734
2735 CURSOR csr_element IS
2736 SELECT pet.element_name
2737 ,piv1.default_value
2738 ,pbt.balance_name
2739 FROM pay_element_types_f pet,
2740 pay_element_classifications pec,
2741 pay_balance_feeds_f pbf,
2742 pay_balance_types pbt,
2743 pay_input_values_f piv,
2744 pay_input_values_f piv1,
2745 pay_element_types_f pet2
2746 WHERE pet.classification_id = pec.classification_id
2747 AND pec.classification_name = 'Voluntary Deductions'
2748 AND pec.legislation_code = 'IN'
2749 AND pet.element_name LIKE '%Recover'
2750 AND pbf.input_value_id = piv.input_value_id
2751 AND pbt.balance_type_id = pbf.balance_type_id
2752 AND pbt.balance_name IN ('Outstanding Advance for Allowances',
2753 'Outstanding Advance for Earnings',
2754 'Outstanding Advance for Fringe Benefits')
2755 AND pbt.legislation_code='IN'
2756 AND piv1.element_type_id = pet.element_type_id
2757 AND piv.name = 'Pay Value'
2758 AND piv.element_type_id = pet.element_type_id
2759 AND piv1.name ='Component Name'
2760 AND p_actual_termination_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date
2761 AND p_actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2762 AND p_actual_termination_date BETWEEN piv1.effective_start_date AND piv1.effective_end_date
2763 AND p_actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2764 AND piv.business_group_id = p_business_group_id
2765 AND piv1.business_group_id = p_business_group_id
2766 AND pet.business_group_id = p_business_group_id
2767 AND pet2.business_group_id = p_business_group_id
2768 AND pet2.element_name = SUBSTR(pet.element_name,1,INSTR(pet.element_name,' Recover',-1))||'Advance'
2769 AND p_actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2770 AND EXISTS (SELECT '1'
2771 FROM pay_run_results prr,
2772 pay_assignment_actions paa,
2773 pay_payroll_Actions ppa
2774 WHERE paa.assignment_id = g_assignment_id
2775 AND paa.assignment_action_id = prr.assignment_action_id
2776 AND paa.payroll_action_id= ppa.payroll_action_id
2777 AND ppa.action_type in('R','Q','B')
2778 AND prr.element_type_id = pet2.element_type_id
2779 AND ROWNUM =1
2780 AND ppa.action_status = 'C'
2781 AND paa.action_status = 'C'
2782 AND ppa.business_group_id = p_business_group_id
2783 );
2784
2785 CURSOR csr_asact_id IS
2786 SELECT MAX(paa.assignment_action_id)
2787 FROM pay_assignment_actions paa
2788 ,pay_payroll_actions ppa
2789 WHERE paa.assignment_id = g_assignment_id
2790 AND paa.payroll_action_id = ppa.payroll_action_id
2791 AND paa.action_status = 'C'
2792 AND paa.source_action_id IS NULL
2793 AND ppa.action_type in ('R','Q')
2794 AND ppa.action_status = 'C' ;
2795
2796
2797 BEGIN
2798 g_debug := hr_utility.debug_enabled ;
2799 l_procedure := g_package || 'create_advances_entry' ;
2800 p_message_name := 'SUCCESS';
2801 pay_in_utils.null_message (p_token_name, p_token_value);
2802
2803 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2804
2805
2806
2807 FOR i in csr_element LOOP
2808
2809
2810 l_def_bal_id := pay_in_tax_utils.get_defined_balance
2811 ( p_balance_type => i.balance_name
2812 ,p_dimension_name => '_ASG_COMP_LTD');
2813
2814 OPEN csr_asact_id;
2815 FETCH csr_asact_id INTO l_asact_id;
2816 CLOSE csr_asact_id;
2817
2818 l_entry_values(1).entry_value :=
2819 pay_balance_pkg.get_value
2820 (p_defined_balance_id => l_def_bal_id
2821 ,p_assignment_action_id => l_asact_id
2822 ,p_tax_unit_id => ''
2823 ,p_jurisdiction_code => ''
2824 ,p_source_id => ''
2825 ,p_source_text => ''
2826 ,p_source_text2 => i.default_value
2827 ,p_tax_group => ''
2828 ,p_date_earned => ''
2829 ,p_get_rr_route => 'TRUE'
2830 ,p_get_rb_route => ''
2831 );
2832
2833 pay_in_utils.set_location(g_debug,l_procedure,20);
2834
2835 IF(l_entry_values(1).entry_value > 0) THEN
2836 l_entry_values(1).entry_value := 'RECOVER' ;
2837 l_entry_values(2).entry_value := i.default_value;
2838 l_element_name := substr(i.element_name,1,instr(i.element_name,' Recover',-1))||'Excess Advance';
2839
2840 pay_in_utils.set_location(g_debug,l_procedure,30);
2841
2842 l_ee_exists := check_advance_exists
2843 (p_component_name => i.default_value
2844 ,p_assignment_id => g_assignment_id
2845 ,p_effective_date => p_actual_termination_date
2846 ,p_element_entry_id => l_element_entry_id
2847 ,p_start_date => l_start_date
2848 ,p_ee_ovn => l_ee_ovn
2849 );
2850
2851 l_element_processed := is_element_processed
2852 (p_assignment_id =>g_assignment_id,
2853 p_element_name =>l_element_name);
2854
2855
2856 IF l_ee_exists OR l_element_processed THEN
2857 pay_in_utils.set_location(g_debug,l_procedure,40);
2858 ELSE
2859 pay_in_utils.set_location(g_debug,l_procedure,50);
2860 create_entry
2861 (p_effective_date => p_actual_termination_date
2862 ,p_business_group_id => p_business_group_id
2863 ,p_element_name => l_element_name
2864 ,p_entry_values => l_entry_values
2865 ,p_calling_procedure => p_calling_procedure
2866 ,p_message_name => p_message_name
2867 ,p_token_name => p_token_name
2868 ,p_token_value => p_token_value
2869 );
2870 END IF;
2871 pay_in_utils.set_location(g_debug,l_procedure,60);
2872 END IF;
2873
2874 END LOOP;
2875
2876 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
2877
2878 END create_advances_entry;
2879
2880 --------------------------------------------------------------------------
2881 -- --
2882 -- Name : delete_advances_entry --
2883 -- Type : Procedure --
2884 -- Access : Public --
2885 -- Description : Procedure to delete 'Gratuity Information' Entry --
2886 -- if termination is reversed for the employee. --
2887 -- --
2888 -- Parameters : --
2889 -- IN : p_period_of_service_id NUMBER --
2890 -- p_business_group_id NUMBER --
2891 -- p_actual_termination_date DATE --
2892 -- p_calling_procedure VARCHAR2 --
2893 -- OUT : p_message_name VARCHAR2 --
2894 -- p_token_name pay_in_utils.char_tab_type--
2895 -- p_token_value pay_in_utils.char_tab_type--
2896 -- Change History : --
2897 --------------------------------------------------------------------------
2898 -- Rev# Date Userid Description --
2899 --------------------------------------------------------------------------
2900 -- 1.0 19-OCT-04 statkar Created this procedure --
2901 --------------------------------------------------------------------------
2902 PROCEDURE delete_advances_entry
2903 (p_period_of_service_id IN NUMBER
2904 ,p_business_group_id IN NUMBER
2905 ,p_actual_termination_date IN DATE
2906 ,p_calling_procedure IN VARCHAR2
2907 ,p_message_name OUT NOCOPY VARCHAR2
2908 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2909 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2910 )
2911 IS
2912 l_procedure VARCHAR2(100);
2913 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2914 l_element_name pay_element_types_f.element_name%TYPE;
2915 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2916 l_eff_start_date DATE;
2917 l_eff_end_date DATE;
2918 l_start_date DATE;
2919 l_delete_warning BOOLEAN;
2920
2921 CURSOR csr_element IS
2922 SELECT pet.element_name
2923 FROM pay_element_types_f pet,
2924 pay_element_classifications pec
2925 WHERE pet.classification_id = pec.classification_id
2926 AND pec.classification_name = 'Information'
2927 AND pet.element_name LIKE '%Excess Advance'
2928 AND pet.business_group_id = p_business_group_id
2929 and p_actual_termination_date between pet.effective_start_date and pet.effective_end_date;
2930
2931
2932 BEGIN
2933 g_debug := hr_utility.debug_enabled ;
2934 l_procedure := g_package || 'delete_advances_entry' ;
2935 p_message_name := 'SUCCESS';
2936 pay_in_utils.null_message (p_token_name, p_token_value);
2937
2938 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2939
2940 FOR i in csr_element loop
2941
2942 IF check_ee_exists
2943 (p_element_name => i.element_name
2944 ,p_assignment_id => g_assignment_id
2945 ,p_effective_date => p_actual_termination_date
2946 ,p_element_entry_id => l_element_entry_id
2947 ,p_start_date => l_start_date
2948 ,p_ee_ovn => l_ee_ovn)
2949 THEN
2950 pay_in_utils.set_location(g_debug,l_procedure,20);
2951 IF g_debug THEN
2952 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2953 END IF;
2954 IF NOT is_element_processed
2955 (p_assignment_id =>g_assignment_id,
2956 p_element_name =>i.element_name)
2957 THEN
2958 pay_element_entry_api.delete_element_entry
2959 (p_datetrack_delete_mode => hr_api.g_delete
2960 ,p_effective_date => l_start_date
2961 ,p_element_entry_id => l_element_entry_id
2962 ,p_object_version_number => l_ee_ovn
2963 ,p_effective_start_date => l_eff_start_date
2964 ,p_effective_end_date => l_eff_end_date
2965 ,p_delete_warning => l_delete_warning
2966 ) ;
2967 END IF ;
2968 pay_in_utils.set_location(g_debug,l_procedure,30);
2969
2970 END IF;
2971 END LOOP;
2972
2973 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2974
2975 END delete_advances_entry;
2976
2977 --------------------------------------------------------------------------
2978 -- --
2979 -- Name : CREATE_PF_ENTRY --
2980 -- Type : PROCEDURE --
2981 -- Access : Private --
2982 -- Description : Procedure to handle creation of PF Settlement EE for--
2983 -- terminated employee based on conditions as required --
2984 -- for India Localization. --
2985 -- --
2986 -- Parameters : --
2987 -- IN : p_period_of_service_id NUMBER --
2988 -- p_business_group_id NUMBER --
2989 -- p_actual_termination_date DATE --
2990 -- p_calling_procedure VARCHAR2 --
2991 -- OUT : p_message_name VARCHAR2 --
2992 -- p_token_name pay_in_utils.char_tab_type--
2993 -- p_token_value pay_in_utils.char_tab_type--
2994 -- Change History : --
2995 --------------------------------------------------------------------------
2996 -- Rev# Date Userid Description --
2997 --------------------------------------------------------------------------
2998 -- 1.0 19-OCT-04 statkar Created this procedure --
2999 --------------------------------------------------------------------------
3000 PROCEDURE create_pf_entry
3001 (p_period_of_service_id IN NUMBER
3002 ,p_business_group_id IN NUMBER
3003 ,p_actual_termination_date IN DATE
3004 ,p_calling_procedure IN VARCHAR2
3005 ,p_message_name OUT NOCOPY VARCHAR2
3006 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3007 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3008 )
3009 IS
3010 l_procedure VARCHAR2(100);
3011 l_element_name pay_element_types_f.element_name%TYPE ;
3012 l_entry_values t_entry_values_tab;
3013 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3014 l_start_date DATE;
3015 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3016 l_ee_exists BOOLEAN;
3017 l_element_processed BOOLEAN;
3018
3019 BEGIN
3020 g_debug := hr_utility.debug_enabled ;
3021 l_procedure := g_package || 'create_pf_entry' ;
3022 p_message_name := 'SUCCESS';
3023 pay_in_utils.null_message (p_token_name, p_token_value);
3024
3025 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3026
3027 l_ee_exists := check_ee_exists
3028 (p_element_name => g_pf_et--l_element_name
3029 ,p_assignment_id => g_assignment_id
3030 ,p_effective_date => p_actual_termination_date
3031 ,p_element_entry_id => l_element_entry_id
3032 ,p_start_date => l_start_date
3033 ,p_ee_ovn => l_ee_ovn);
3034
3035 IF l_ee_exists THEN
3036 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3037 RETURN;
3038 END IF;
3039
3040 l_element_processed := is_element_processed
3041 (p_assignment_id =>g_assignment_id,
3042 p_element_name =>g_pf_et);
3043
3044 IF l_element_processed THEN
3045 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3046 RETURN;
3047 END IF;
3048 --
3049 -- Element Name : PF Settlement Information
3050 --
3051 -- Input Value are:
3052 --
3053 -- 1. PF Settlement Amount - Null
3054 -- 2. Continuous Service - Calculate
3055 -- 3. Employee Contribution - Null
3056 -- 4. Component Name - 'PF Settlement'
3057 --
3058 l_entry_values(1).entry_value := null;
3059 IF (months_between(p_actual_termination_date, g_hire_date)/12 ) > 5 THEN
3060 l_entry_values(2).entry_value := 'Y';
3061 ELSE
3062 l_entry_values(2).entry_value := 'N';
3063 END IF;
3064 l_entry_values(3).entry_value := null;
3065 l_entry_values(4).entry_value := g_pf_cn;
3066 l_element_name := g_pf_et;
3067
3068 pay_in_utils.set_location(g_debug,l_procedure,20);
3069 create_entry
3070 (p_effective_date => p_actual_termination_date
3071 ,p_business_group_id => p_business_group_id
3072 ,p_element_name => l_element_name
3073 ,p_entry_values => l_entry_values
3074 ,p_calling_procedure => p_calling_procedure
3075 ,p_message_name => p_message_name
3076 ,p_token_name => p_token_name
3077 ,p_token_value => p_token_value
3078 );
3079
3080 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
3081
3082 END create_pf_entry;
3083
3084 --------------------------------------------------------------------------
3085 -- --
3086 -- Name : DELETE_PF_ENTRY --
3087 -- Type : Procedure --
3088 -- Access : Private --
3089 -- Description : Procedure to delete 'PF Settlement Information' EE --
3090 -- if termination is reversed for the employee. --
3091 -- --
3092 -- Parameters : --
3093 -- IN : p_period_of_service_id NUMBER --
3094 -- p_business_group_id NUMBER --
3095 -- p_actual_termination_date DATE --
3096 -- p_calling_procedure VARCHAR2 --
3097 -- OUT : p_message_name VARCHAR2 --
3098 -- p_token_name pay_in_utils.char_tab_type--
3099 -- p_token_value pay_in_utils.char_tab_type--
3100 -- Change History : --
3101 --------------------------------------------------------------------------
3102 -- Rev# Date Userid Description --
3103 --------------------------------------------------------------------------
3104 -- 1.0 19-OCT-04 statkar Created this procedure --
3105 --------------------------------------------------------------------------
3106 PROCEDURE delete_pf_entry
3107 (p_period_of_service_id IN NUMBER
3108 ,p_business_group_id IN NUMBER
3109 ,p_actual_termination_date IN DATE
3110 ,p_calling_procedure IN VARCHAR2
3111 ,p_message_name OUT NOCOPY VARCHAR2
3112 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3113 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3114 )
3115 IS
3116 l_procedure VARCHAR2(100);
3117 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3118 l_element_name pay_element_types_f.element_name%TYPE;
3119 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3120 l_eff_start_date DATE;
3121 l_eff_end_date DATE;
3122 l_start_date DATE;
3123 l_delete_warning BOOLEAN;
3124 l_assignment_id NUMBER;
3125 l_payroll_id NUMBER;
3126 l_hire_date DATE;
3127
3128 BEGIN
3129 g_debug := hr_utility.debug_enabled ;
3130 l_procedure := g_package || 'delete_pf_entry' ;
3131 p_message_name := 'SUCCESS';
3132 pay_in_utils.null_message (p_token_name, p_token_value);
3133
3134 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3135
3136 l_element_name := g_pf_et;
3137
3138 IF check_ee_exists
3139 (p_element_name => l_element_name
3140 ,p_assignment_id => g_assignment_id
3141 ,p_effective_date => p_actual_termination_date
3142 ,p_element_entry_id => l_element_entry_id
3143 ,p_start_date => l_start_date
3144 ,p_ee_ovn => l_ee_ovn)
3145 THEN
3146 pay_in_utils.set_location(g_debug,l_procedure,20);
3147 IF g_debug THEN
3148 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
3149 END IF;
3150 IF NOT is_element_processed
3151 (p_assignment_id =>g_assignment_id,
3152 p_element_name =>l_element_name)
3153 THEN
3154 pay_element_entry_api.delete_element_entry
3155 (p_datetrack_delete_mode => hr_api.g_delete
3156 ,p_effective_date => l_start_date
3157 ,p_element_entry_id => l_element_entry_id
3158 ,p_object_version_number => l_ee_ovn
3159 ,p_effective_start_date => l_eff_start_date
3160 ,p_effective_end_date => l_eff_end_date
3161 ,p_delete_warning => l_delete_warning
3162 ) ;
3163 END IF ;
3164 pay_in_utils.set_location(g_debug,l_procedure,30);
3165
3166 END IF;
3167 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
3168
3169 END delete_pf_entry;
3170 --------------------------------------------------------------------------
3171 -- --
3172 -- Name : CREATE_LOAN_ENTRY --
3173 -- Type : PROCEDURE --
3174 -- Access : Private --
3175 -- Description : Procedure to handle creation of Loan Recovery EE for--
3176 -- terminated employee based on conditions as required --
3177 -- for India Localization. --
3178 -- --
3179 -- Parameters : --
3180 -- IN : p_period_of_service_id NUMBER --
3181 -- p_business_group_id NUMBER --
3182 -- p_actual_termination_date DATE --
3183 -- p_calling_procedure VARCHAR2 --
3184 -- OUT : p_message_name VARCHAR2 --
3185 -- p_token_name pay_in_utils.char_tab_type--
3186 -- p_token_value pay_in_utils.char_tab_type--
3187 -- Change History : --
3188 --------------------------------------------------------------------------
3189 -- Rev# Date Userid Description --
3190 --------------------------------------------------------------------------
3191 -- 1.0 19-OCT-04 statkar Created this procedure --
3192 --------------------------------------------------------------------------
3193 PROCEDURE create_loan_entry
3194 (p_period_of_service_id IN NUMBER
3195 ,p_business_group_id IN NUMBER
3196 ,p_actual_termination_date IN DATE
3197 ,p_calling_procedure IN VARCHAR2
3198 ,p_message_name OUT NOCOPY VARCHAR2
3199 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3200 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3201 )
3202 IS
3203 l_procedure VARCHAR2(100);
3204 l_element_name pay_element_types_f.element_name%TYPE ;
3205 l_entry_values t_entry_values_tab;
3206 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3207 l_start_date DATE;
3208 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3209 l_ee_exists BOOLEAN;
3210 l_element_processed BOOLEAN;
3211
3212 CURSOR c_ee_dtls IS
3213 SELECT pee.element_entry_id
3214 FROM pay_element_entries_f pee
3215 ,pay_input_values_f piv
3216 ,pay_element_entry_values_f peev
3217 WHERE pee.assignment_id = g_assignment_id
3218 AND pee.element_entry_id = peev.element_entry_id
3219 AND peev.input_value_id = piv.input_value_id
3220 AND piv.name = 'Component Name'
3221 AND peev.screen_entry_value = 'Loan at Concessional Rate'
3222 AND p_actual_termination_date BETWEEN pee.effective_start_date
3223 AND pee.effective_end_date
3224 AND p_actual_termination_date BETWEEN piv.effective_start_date
3225 AND piv.effective_end_date
3226 AND p_actual_termination_date BETWEEN peev.effective_start_date
3227 AND peev.effective_end_date;
3228
3229 l_ee_id pay_element_entries_f.element_entry_id%TYPE;
3230
3231 CURSOR c_iv_dtls (p_element_entry_id IN NUMBER
3232 , p_name IN VARCHAR2)
3233 IS
3234 SELECT peev.screen_entry_value
3235 FROM pay_element_entry_values_f peev
3236 ,pay_input_values_f piv
3237 WHERE peev.element_entry_id = p_element_entry_id
3238 AND peev.input_value_id = piv.input_value_id
3239 AND piv.NAME = p_name
3240 AND p_actual_termination_date BETWEEN peev.effective_start_date
3241 AND peev.effective_end_date
3242 AND p_actual_termination_date BETWEEN piv.effective_start_date
3243 AND piv.effective_end_date;
3244
3245 l_loan_number pay_element_entry_values_f.screen_entry_value%TYPE;
3246 l_loan_type pay_element_entry_values_f.screen_entry_value%TYPE;
3247
3248 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
3249
3250 CURSOR csr_asact_id IS
3251 SELECT max(paa.assignment_action_id)
3252 FROM pay_assignment_actions paa
3253 ,pay_payroll_actions ppa
3254 WHERE paa.assignment_id = g_assignment_id
3255 AND paa.payroll_action_id = ppa.payroll_action_id
3256 AND paa.action_status = 'C'
3257 AND paa.source_action_id IS NULL
3258 AND ppa.action_type in ('R','Q')
3259 AND ppa.action_status = 'C' ;
3260
3261 l_asact_id pay_assignment_actions.assignment_action_id%TYPE;
3262 BEGIN
3263 g_debug := hr_utility.debug_enabled ;
3264 l_procedure := g_package || 'create_loan_entry' ;
3265 p_message_name := 'SUCCESS';
3266 pay_in_utils.null_message (p_token_name, p_token_value);
3267
3268 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3269
3270 --
3271 -- Fetch the Loan Number and Loan Type for each loan
3272 --
3273 l_element_name := g_loan_et;
3274 OPEN c_ee_dtls;
3275 LOOP
3276 FETCH c_ee_dtls
3277 INTO l_ee_id;
3278 EXIT WHEN c_ee_dtls%NOTFOUND;
3279
3280 OPEN c_iv_dtls (l_ee_id, 'Loan Number');
3281 FETCH c_iv_dtls INTO l_loan_number;
3282 CLOSE c_iv_dtls;
3283
3284 OPEN c_iv_dtls (l_ee_id, 'Loan Type');
3285 FETCH c_iv_dtls INTO l_loan_type;
3286 CLOSE c_iv_dtls;
3287
3288 l_ee_exists := check_ee_exists
3289 (p_element_name => l_element_name
3290 ,p_input_value_name => 'Loan Number'
3291 ,p_input_value => l_loan_number
3292 ,p_assignment_id => g_assignment_id
3293 ,p_effective_date => p_actual_termination_date
3294 ,p_element_entry_id => l_element_entry_id
3295 ,p_start_date => l_start_date
3296 ,p_ee_ovn => l_ee_ovn);
3297
3298 IF NOT l_ee_exists THEN
3299 pay_in_utils.set_location(g_debug,l_procedure,20);
3300
3301 l_element_processed := is_element_processed
3302 (p_assignment_id =>g_assignment_id,
3303 p_element_name =>l_element_name);
3304
3305 IF l_element_processed THEN
3306 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3307 RETURN;
3308 END IF;
3309 --
3310 -- Element Name : Loan Recovery
3311 --
3312 -- Input Value are:
3313 --
3314 -- 1. Pay Value - Calculate
3315 -- 2. Loan Number - Associate
3316 -- 3. Loan Type - Associate
3317 -- 4. Component Name - 'Loan Recovery'
3318
3319 l_def_bal_id := pay_in_tax_utils.get_defined_balance
3320 ( p_balance_type => 'Maximum Outstanding Amount'
3321 ,p_dimension_name => '_ASG_SRC_LTD');
3322
3323 OPEN csr_asact_id;
3324 FETCH csr_asact_id INTO l_asact_id;
3325 CLOSE csr_asact_id;
3326
3327 l_entry_values(1).entry_value :=
3328 pay_balance_pkg.get_value
3329 (p_defined_balance_id => l_def_bal_id
3330 ,p_assignment_action_id => l_asact_id
3331 ,p_tax_unit_id => ''
3332 ,p_jurisdiction_code => ''
3333 ,p_source_id => ''
3334 ,p_source_text => l_loan_number
3335 ,p_tax_group => ''
3336 ,p_date_earned => ''
3337 ,p_get_rr_route => 'TRUE'
3338 ,p_get_rb_route => ''
3339 );
3340
3341 l_entry_values(1).entry_value := (-1)*l_entry_values(1).entry_value ;
3342 l_entry_values(2).entry_value := l_loan_number;
3343 l_entry_values(3).entry_value := l_loan_type;
3344 l_entry_values(4).entry_value := g_loan_cn;
3345
3346 pay_in_utils.set_location(g_debug,l_procedure,30);
3347 create_entry
3348 (p_effective_date => p_actual_termination_date
3349 ,p_business_group_id => p_business_group_id
3350 ,p_element_name => l_element_name
3351 ,p_entry_values => l_entry_values
3352 ,p_calling_procedure => p_calling_procedure
3353 ,p_message_name => p_message_name
3354 ,p_token_name => p_token_name
3355 ,p_token_value => p_token_value
3356 );
3357
3358 pay_in_utils.set_location(g_debug,l_procedure,40);
3359 END IF;
3360 END LOOP;
3361 CLOSE c_ee_dtls;
3362 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3363
3364 END create_loan_entry;
3365
3366 --------------------------------------------------------------------------
3367 -- --
3368 -- Name : DELETE_LOAN_ENTRY --
3369 -- Type : Procedure --
3370 -- Access : Private --
3371 -- Description : Procedure to delete 'Loan Recovery' EE --
3372 -- if termination is reversed for the employee. --
3373 -- --
3374 -- Parameters : --
3375 -- IN : p_period_of_service_id NUMBER --
3376 -- p_business_group_id NUMBER --
3377 -- p_actual_termination_date DATE --
3378 -- p_calling_procedure VARCHAR2 --
3379 -- OUT : p_message_name VARCHAR2 --
3380 -- p_token_name pay_in_utils.char_tab_type--
3381 -- p_token_value pay_in_utils.char_tab_type--
3382 -- Change History : --
3383 --------------------------------------------------------------------------
3384 -- Rev# Date Userid Description --
3385 --------------------------------------------------------------------------
3386 -- 1.0 19-OCT-04 statkar Created this procedure --
3387 --------------------------------------------------------------------------
3388 PROCEDURE delete_loan_entry
3389 (p_period_of_service_id IN NUMBER
3390 ,p_business_group_id IN NUMBER
3391 ,p_actual_termination_date IN DATE
3392 ,p_calling_procedure IN VARCHAR2
3393 ,p_message_name OUT NOCOPY VARCHAR2
3394 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3395 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3396 )
3397 IS
3398 l_procedure VARCHAR2(100);
3399 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3400 l_element_name pay_element_types_f.element_name%TYPE;
3401 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3402 l_eff_start_date DATE;
3403 l_eff_end_date DATE;
3404 l_start_date DATE;
3405 l_delete_warning BOOLEAN;
3406
3407 CURSOR c_ee_dtls IS
3408 SELECT pee.element_entry_id
3409 FROM pay_element_entries_f pee
3410 ,pay_input_values_f piv
3411 ,pay_element_entry_values_f peev
3412 WHERE pee.assignment_id = g_assignment_id
3413 AND pee.element_entry_id = peev.element_entry_id
3414 AND peev.input_value_id = piv.input_value_id
3415 AND piv.name = 'Component Name'
3416 AND peev.screen_entry_value = 'Loan at Concessional Rate'
3417 AND p_actual_termination_date BETWEEN pee.effective_start_date
3418 AND pee.effective_end_date
3419 AND p_actual_termination_date BETWEEN piv.effective_start_date
3420 AND piv.effective_end_date
3421 AND p_actual_termination_date BETWEEN peev.effective_start_date
3422 AND peev.effective_end_date;
3423
3424 l_ee_id pay_element_entries_f.element_entry_id%TYPE;
3425
3426 CURSOR c_iv_dtls (p_element_entry_id IN NUMBER
3427 , p_name IN VARCHAR2)
3428 IS
3429 SELECT peev.screen_entry_value
3430 FROM pay_element_entry_values_f peev
3431 ,pay_input_values_f piv
3432 WHERE peev.element_entry_id = p_element_entry_id
3433 AND peev.input_value_id = piv.input_value_id
3434 AND piv.NAME = p_name
3435 AND p_actual_termination_date BETWEEN peev.effective_start_date
3436 AND peev.effective_end_date
3437 AND p_actual_termination_date BETWEEN piv.effective_start_date
3438 AND piv.effective_end_date;
3439
3440 l_loan_number pay_element_entry_values_f.screen_entry_value%TYPE;
3441 l_loan_type pay_element_entry_values_f.screen_entry_value%TYPE;
3442
3443
3444 BEGIN
3445 g_debug := hr_utility.debug_enabled ;
3446 l_procedure := g_package || 'delete_loan_entry' ;
3447 p_message_name := 'SUCCESS';
3448 pay_in_utils.null_message (p_token_name, p_token_value);
3449
3450 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3451
3452 l_element_name := g_loan_et;
3453
3454 --
3455 -- Fetch the Loan Number and Loan Type for each loan
3456 --
3457 OPEN c_ee_dtls;
3458 LOOP
3459 FETCH c_ee_dtls
3460 INTO l_ee_id;
3461 EXIT WHEN c_ee_dtls%NOTFOUND;
3462
3463 OPEN c_iv_dtls (l_ee_id, 'Loan Number');
3464 FETCH c_iv_dtls INTO l_loan_number;
3465 CLOSE c_iv_dtls;
3466
3467 OPEN c_iv_dtls (l_ee_id, 'Loan Type');
3468 FETCH c_iv_dtls INTO l_loan_type;
3469 CLOSE c_iv_dtls;
3470
3471 IF check_ee_exists
3472 (p_element_name => l_element_name
3473 ,p_input_value_name => 'Loan Number'
3474 ,p_input_value => l_loan_number
3475 ,p_assignment_id => g_assignment_id
3476 ,p_effective_date => p_actual_termination_date
3477 ,p_element_entry_id => l_element_entry_id
3478 ,p_start_date => l_start_date
3479 ,p_ee_ovn => l_ee_ovn)
3480 THEN
3481 pay_in_utils.set_location(g_debug,l_procedure,20);
3482 IF g_debug THEN
3483 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
3484 END IF;
3485 IF NOT is_element_processed
3486 (p_assignment_id =>g_assignment_id,
3487 p_element_name =>l_element_name)
3488 THEN
3489 pay_element_entry_api.delete_element_entry
3490 (p_datetrack_delete_mode => hr_api.g_delete
3491 ,p_effective_date => l_start_date
3492 ,p_element_entry_id => l_element_entry_id
3493 ,p_object_version_number => l_ee_ovn
3494 ,p_effective_start_date => l_eff_start_date
3495 ,p_effective_end_date => l_eff_end_date
3496 ,p_delete_warning => l_delete_warning
3497 ) ;
3498 END IF ;
3499 pay_in_utils.set_location(g_debug,l_procedure,30);
3500
3501 END IF;
3502 END LOOP;
3503 CLOSE c_ee_dtls;
3504 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3505
3506 END delete_loan_entry;
3507
3508
3509 --------------------------------------------------------------------------
3510 -- --
3511 -- Name : CHECK_PF_LEAV_REASONS --
3512 -- Type : PROCEDURE --
3513 -- Access : Private --
3514 -- Description : Internal Proc to be called for validating the PF --
3515 -- leaving reason before termination --
3516 -- --
3517 -- Parameters : --
3518 -- IN : p_period_of_service_id NUMBER --
3519 -- p_business_group_id NUMBER --
3520 -- p_actual_termination_date DATE --
3521 -- p_assignment_id NUMBER --
3522 -- p_calling_procedure VARCHAR2 --
3523 -- OUT : p_message_name VARCHAR2 --
3524 -- p_token_name pay_in_utils.char_tab_type --
3525 -- p_token_value pay_in_utils.char_tab_type --
3526 -- Change History : --
3527 --------------------------------------------------------------------------
3528 -- Rev# Date Userid Description --
3529 --------------------------------------------------------------------------
3530 -- 1.0 28-Aug-07 sivanara Created this procedure --
3531 -- 1.1 25-Sep-07 rsaharay Modified c_emp_mon_pf_pos_dtls --
3532 --------------------------------------------------------------------------
3533 PROCEDURE check_PF_leav_reasons(p_period_of_service_id IN NUMBER
3534 ,p_business_group_id IN NUMBER
3535 ,p_actual_termination_date IN DATE
3536 ,p_assignment_id IN NUMBER
3537 ,p_calling_procedure IN VARCHAR2
3538 ,p_message_name OUT NOCOPY VARCHAR2
3539 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3540 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3541 )
3542 IS
3543 CURSOR c_emp_mon_pf_pos_dtls IS
3544 SELECT ppf.per_information15
3545 ,pos.pds_information1
3546 ,pos.pds_information2
3547 FROM per_periods_of_service pos
3548 ,per_assignments_f paf
3549 ,per_people_f ppf
3550 ,hr_soft_coding_keyflex scl
3551 WHERE pos.period_of_service_id = p_period_of_service_id
3552 AND pos.business_group_id = p_business_group_id
3553 AND paf.assignment_id = p_assignment_id
3554 AND pos.period_of_service_id = paf.period_of_service_id
3555 AND paf.person_id = ppf.person_id
3556 AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3557 AND scl.enabled_flag = 'Y'
3558 AND scl.segment2 IS NOT NULL
3559 AND ppf.per_information15 IS NOT NULL
3560 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(p_actual_termination_date,'Month-YYYY')
3561 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(p_actual_termination_date,'Month-YYYY')
3562 OR p_actual_termination_date between paf.effective_start_date and paf.effective_end_date)
3563 AND p_actual_termination_date BETWEEN ppf.effective_start_date
3564 AND ppf.effective_end_date;
3565
3566 l_procedure VARCHAR2(100);
3567 l_NSSN per_people_f.per_information15%TYPE;
3568 l_print_leav_reas VARCHAR2(50);
3569 l_efile_leav_reas VARCHAR2 (50);
3570
3571 BEGIN
3572 g_debug := hr_utility.debug_enabled ;
3573 l_procedure := g_package || 'check_PF_leav_reasons' ;
3574 p_message_name := 'SUCCESS';
3575 pay_in_utils.null_message (p_token_name, p_token_value);
3576 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3577
3578 IF g_debug THEN
3579 pay_in_utils.trace('Period of service id ',to_char(p_period_of_service_id));
3580 pay_in_utils.trace('Business Group ID ',to_char(p_business_group_id));
3581 pay_in_utils.trace('Effective Date ',to_char(p_actual_termination_date, 'DD-MM-YYYY'));
3582 pay_in_utils.trace('Assignment ID ',to_char(p_assignment_id));
3583 pay_in_utils.trace('Calling Procedure ',p_calling_procedure);
3584 END IF;
3585
3586 pay_in_utils.set_location(g_debug,l_procedure,20);
3587
3588 OPEN c_emp_mon_pf_pos_dtls;
3589 FETCH c_emp_mon_pf_pos_dtls
3590 INTO l_NSSN, l_print_leav_reas, l_efile_leav_reas;
3591 CLOSE c_emp_mon_pf_pos_dtls;
3592
3593 IF l_NSSN IS NOT NULL AND (l_print_leav_reas IS NULL OR l_efile_leav_reas IS NULL) THEN
3594 p_message_name := 'PER_IN_PF_LEAV_REASON';
3595 pay_in_utils.set_location(g_debug,l_procedure,30);
3596 END IF;
3597 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3598 END check_PF_leav_reasons;
3599
3600 --------------------------------------------------------------------------
3601 -- --
3602 -- Name : CREATE_TERMINATION_ELEMENTS --
3603 -- Type : PROCEDURE --
3604 -- Access : Public --
3605 -- Description : Procedure to handle creation of Termination EE for --
3606 -- terminated employee based on conditions as required --
3607 -- for India Localization. --
3608 -- --
3609 -- Parameters : --
3610 -- IN : p_period_of_service_id NUMBER --
3611 -- p_business_group_id NUMBER --
3612 -- p_actual_termination_date DATE --
3613 -- p_calling_procedure VARCHAR2 --
3614 -- OUT : p_message_name VARCHAR2 --
3615 -- p_token_name pay_in_utils.char_tab_type--
3616 -- p_token_value pay_in_utils.char_tab_type--
3617 -- Change History : --
3618 --------------------------------------------------------------------------
3619 -- Rev# Date Userid Description --
3620 --------------------------------------------------------------------------
3621 -- 1.0 19-OCT-04 statkar Created this procedure --
3622 -- 1.1 28-AUG-07 sivanara Added code for validation of Monthly PF --
3623 -- returns leaving reasons --
3624 --------------------------------------------------------------------------
3625 PROCEDURE create_termination_elements
3626 (p_period_of_service_id IN NUMBER
3627 ,p_business_group_id IN NUMBER
3628 ,p_actual_termination_date IN DATE
3629 ,p_calling_procedure IN VARCHAR2
3630 ,p_message_name OUT NOCOPY VARCHAR2
3631 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3632 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3633 )
3634 IS
3635 l_procedure VARCHAR2(100);
3636
3637 BEGIN
3638 g_debug := hr_utility.debug_enabled ;
3639 l_procedure := g_package || 'create_termination_elements' ;
3640 p_message_name := 'SUCCESS';
3641 pay_in_utils.null_message (p_token_name, p_token_value);
3642
3643 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3644
3645 get_pos_dtls(p_period_of_service_id => p_period_of_service_id
3646 ,p_business_group_id => p_business_group_id
3647 ,p_effective_date => p_actual_termination_date
3648 );
3649
3650 IF g_debug THEN
3651 pay_in_utils.trace('Period of service id ',to_char(p_period_of_service_id));
3652 pay_in_utils.trace('Business Group ID ',to_char(p_business_group_id));
3653 pay_in_utils.trace('Act Term Date ',to_char(p_actual_termination_date, 'DD-MM-YYYY'));
3654 pay_in_utils.trace('Assignment ID ',to_char(g_assignment_id));
3655 pay_in_utils.trace('Payroll ID ',to_char(g_payroll_id));
3656 pay_in_utils.trace('Hire Date ',to_char(g_hire_date,'DD-MM-YYYY'));
3657 pay_in_utils.trace('Notified Term Date ',to_char(g_notified_date,'DD-MM-YYYY'));
3658 END IF;
3659
3660 IF g_notified_date IS NULL THEN
3661 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3662 p_token_name(1) := 'API_NAME';
3663 p_token_value(1) := p_calling_procedure;
3664 p_token_name(2) := 'ARGUMENT';
3665 p_token_value(2) := 'Notified Termination Date';
3666 RETURN;
3667 END IF;
3668
3669 check_PF_leav_reasons(p_period_of_service_id => p_period_of_service_id
3670 ,p_business_group_id => p_business_group_id
3671 ,p_actual_termination_date => p_actual_termination_date
3672 ,p_assignment_id => g_assignment_id
3673 ,p_calling_procedure => p_calling_procedure
3674 ,p_message_name => p_message_name
3675 ,p_token_name => p_token_name
3676 ,p_token_value => p_token_value
3677 );
3678
3679 pay_in_utils.set_location(g_debug,l_procedure,15);
3680 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3681 p_message_name := 'SUCCESS';
3682 pay_in_utils.null_message (p_token_name, p_token_value);
3683
3684 create_notice_entry
3685 (p_period_of_service_id => p_period_of_service_id
3686 ,p_business_group_id => p_business_group_id
3687 ,p_actual_termination_date => p_actual_termination_date
3688 ,p_calling_procedure => p_calling_procedure
3689 ,p_message_name => p_message_name
3690 ,p_token_name => p_token_name
3691 ,p_token_value => p_token_value
3692 );
3693
3694 pay_in_utils.set_location(g_debug,l_procedure,20);
3695 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3696 p_message_name := 'SUCCESS';
3697 pay_in_utils.null_message (p_token_name, p_token_value);
3698
3699 create_retrenchment_entry
3700 (p_period_of_service_id => p_period_of_service_id
3701 ,p_business_group_id => p_business_group_id
3702 ,p_actual_termination_date => p_actual_termination_date
3703 ,p_calling_procedure => p_calling_procedure
3704 ,p_message_name => p_message_name
3705 ,p_token_name => p_token_name
3706 ,p_token_value => p_token_value
3707 );
3708
3709 pay_in_utils.set_location(g_debug,l_procedure,30);
3710 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3711 p_message_name := 'SUCCESS';
3712 pay_in_utils.null_message (p_token_name, p_token_value);
3713
3714 create_vrs_entry
3715 (p_period_of_service_id => p_period_of_service_id
3716 ,p_business_group_id => p_business_group_id
3717 ,p_actual_termination_date => p_actual_termination_date
3718 ,p_calling_procedure => p_calling_procedure
3719 ,p_message_name => p_message_name
3720 ,p_token_name => p_token_name
3721 ,p_token_value => p_token_value
3722 );
3723
3724 pay_in_utils.set_location(g_debug,l_procedure,40);
3725 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3726 p_message_name := 'SUCCESS';
3727 pay_in_utils.null_message (p_token_name, p_token_value);
3728
3729 create_pension_entry
3730 (p_period_of_service_id => p_period_of_service_id
3731 ,p_business_group_id => p_business_group_id
3732 ,p_actual_termination_date => p_actual_termination_date
3733 ,p_calling_procedure => p_calling_procedure
3734 ,p_message_name => p_message_name
3735 ,p_token_name => p_token_name
3736 ,p_token_value => p_token_value
3737 );
3738
3739 pay_in_utils.set_location(g_debug,l_procedure,50);
3740 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3741 p_message_name := 'SUCCESS';
3742 pay_in_utils.null_message (p_token_name, p_token_value);
3743
3744 create_pf_entry
3745 (p_period_of_service_id => p_period_of_service_id
3746 ,p_business_group_id => p_business_group_id
3747 ,p_actual_termination_date => p_actual_termination_date
3748 ,p_calling_procedure => p_calling_procedure
3749 ,p_message_name => p_message_name
3750 ,p_token_name => p_token_name
3751 ,p_token_value => p_token_value
3752 );
3753
3754 pay_in_utils.set_location(g_debug,l_procedure,60);
3755 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3756 p_message_name := 'SUCCESS';
3757 pay_in_utils.null_message (p_token_name, p_token_value);
3758
3759 create_loan_entry
3760 (p_period_of_service_id => p_period_of_service_id
3761 ,p_business_group_id => p_business_group_id
3762 ,p_actual_termination_date => p_actual_termination_date
3763 ,p_calling_procedure => p_calling_procedure
3764 ,p_message_name => p_message_name
3765 ,p_token_name => p_token_name
3766 ,p_token_value => p_token_value
3767 );
3768 pay_in_utils.set_location(g_debug,l_procedure,70);
3769 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3770 p_message_name := 'SUCCESS';
3771 pay_in_utils.null_message (p_token_name, p_token_value);
3772
3773 create_gratuity_entry
3774 (p_period_of_service_id => p_period_of_service_id
3775 ,p_business_group_id => p_business_group_id
3776 ,p_actual_termination_date => p_actual_termination_date
3777 ,p_calling_procedure => p_calling_procedure
3778 ,p_message_name => p_message_name
3779 ,p_token_name => p_token_name
3780 ,p_token_value => p_token_value
3781 );
3782
3783 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
3784 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3785
3786 create_advances_entry
3787 (p_period_of_service_id => p_period_of_service_id
3788 ,p_business_group_id => p_business_group_id
3789 ,p_actual_termination_date => p_actual_termination_date
3790 ,p_calling_procedure => p_calling_procedure
3791 ,p_message_name => p_message_name
3792 ,p_token_name => p_token_name
3793 ,p_token_value => p_token_value
3794 );
3795
3796 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
3797 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3798
3799
3800
3801 END create_termination_elements;
3802
3803 --------------------------------------------------------------------------
3804 -- --
3805 -- Name : DELETE_TERMINATION_ELEMENTS --
3806 -- Type : Procedure --
3807 -- Access : Public --
3808 -- Description : Procedure to delete all Termination Element entries --
3809 -- if termination is reversed for the employee. --
3810 -- --
3811 -- Parameters : --
3812 -- IN : p_period_of_service_id NUMBER --
3813 -- p_business_group_id NUMBER --
3814 -- p_actual_termination_date DATE --
3815 -- p_calling_procedure VARCHAR2 --
3816 -- OUT : p_message_name VARCHAR2 --
3817 -- p_token_name pay_in_utils.char_tab_type--
3818 -- p_token_value pay_in_utils.char_tab_type--
3819 -- Change History : --
3820 --------------------------------------------------------------------------
3821 -- Rev# Date Userid Description --
3822 --------------------------------------------------------------------------
3823 -- 1.0 19-OCT-04 statkar Created this procedure --
3824 --------------------------------------------------------------------------
3825 PROCEDURE delete_termination_elements
3826 (p_period_of_service_id IN NUMBER
3827 ,p_business_group_id IN NUMBER
3828 ,p_actual_termination_date IN DATE
3829 ,p_calling_procedure IN VARCHAR2
3830 ,p_message_name OUT NOCOPY VARCHAR2
3831 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3832 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3833 )
3834 IS
3835 l_procedure VARCHAR2(100);
3836
3837 BEGIN
3838 g_debug := hr_utility.debug_enabled ;
3839 l_procedure := g_package || 'delete_termination_elements' ;
3840 p_message_name := 'SUCCESS';
3841 pay_in_utils.null_message (p_token_name, p_token_value);
3842
3843 get_pos_dtls(p_period_of_service_id => p_period_of_service_id
3844 ,p_business_group_id => p_business_group_id
3845 ,p_effective_date => p_actual_termination_date
3846 );
3847
3848
3849 delete_notice_entry
3850 (p_period_of_service_id => p_period_of_service_id
3851 ,p_business_group_id => p_business_group_id
3852 ,p_actual_termination_date => p_actual_termination_date
3853 ,p_calling_procedure => p_calling_procedure
3854 ,p_message_name => p_message_name
3855 ,p_token_name => p_token_name
3856 ,p_token_value => p_token_value
3857 );
3858
3859 pay_in_utils.set_location(g_debug,l_procedure,20);
3860 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3861 p_message_name := 'SUCCESS';
3862 pay_in_utils.null_message (p_token_name, p_token_value);
3863
3864 delete_retrenchment_entry
3865 (p_period_of_service_id => p_period_of_service_id
3866 ,p_business_group_id => p_business_group_id
3867 ,p_actual_termination_date => p_actual_termination_date
3868 ,p_calling_procedure => p_calling_procedure
3869 ,p_message_name => p_message_name
3870 ,p_token_name => p_token_name
3871 ,p_token_value => p_token_value
3872 );
3873
3874 pay_in_utils.set_location(g_debug,l_procedure,30);
3875 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3876 p_message_name := 'SUCCESS';
3877 pay_in_utils.null_message (p_token_name, p_token_value);
3878
3879 delete_vrs_entry
3880 (p_period_of_service_id => p_period_of_service_id
3881 ,p_business_group_id => p_business_group_id
3882 ,p_actual_termination_date => p_actual_termination_date
3883 ,p_calling_procedure => p_calling_procedure
3884 ,p_message_name => p_message_name
3885 ,p_token_name => p_token_name
3886 ,p_token_value => p_token_value
3887 );
3888
3889 pay_in_utils.set_location(g_debug,l_procedure,40);
3890 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3891 p_message_name := 'SUCCESS';
3892 pay_in_utils.null_message (p_token_name, p_token_value);
3893
3894 delete_pension_entry
3895 (p_period_of_service_id => p_period_of_service_id
3896 ,p_business_group_id => p_business_group_id
3897 ,p_actual_termination_date => p_actual_termination_date
3898 ,p_calling_procedure => p_calling_procedure
3899 ,p_message_name => p_message_name
3900 ,p_token_name => p_token_name
3901 ,p_token_value => p_token_value
3902 );
3903
3904 pay_in_utils.set_location(g_debug,l_procedure,50);
3905 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3906 p_message_name := 'SUCCESS';
3907 pay_in_utils.null_message (p_token_name, p_token_value);
3908
3909 delete_pf_entry
3910 (p_period_of_service_id => p_period_of_service_id
3911 ,p_business_group_id => p_business_group_id
3912 ,p_actual_termination_date => p_actual_termination_date
3913 ,p_calling_procedure => p_calling_procedure
3914 ,p_message_name => p_message_name
3915 ,p_token_name => p_token_name
3916 ,p_token_value => p_token_value
3917 );
3918
3919 pay_in_utils.set_location(g_debug,l_procedure,60);
3920 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3921 p_message_name := 'SUCCESS';
3922 pay_in_utils.null_message (p_token_name, p_token_value);
3923
3924 delete_loan_entry
3925 (p_period_of_service_id => p_period_of_service_id
3926 ,p_business_group_id => p_business_group_id
3927 ,p_actual_termination_date => p_actual_termination_date
3928 ,p_calling_procedure => p_calling_procedure
3929 ,p_message_name => p_message_name
3930 ,p_token_name => p_token_name
3931 ,p_token_value => p_token_value
3932 );
3933
3934 pay_in_utils.set_location(g_debug,l_procedure,70);
3935 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3936 p_message_name := 'SUCCESS';
3937 pay_in_utils.null_message (p_token_name, p_token_value);
3938
3939 delete_gratuity_entry
3940 (p_period_of_service_id => p_period_of_service_id
3941 ,p_business_group_id => p_business_group_id
3942 ,p_actual_termination_date => p_actual_termination_date
3943 ,p_calling_procedure => p_calling_procedure
3944 ,p_message_name => p_message_name
3945 ,p_token_name => p_token_name
3946 ,p_token_value => p_token_value
3947 );
3948
3949 pay_in_utils.set_location(g_debug,l_procedure,80);
3950 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3951 p_message_name := 'SUCCESS';
3952 pay_in_utils.null_message (p_token_name, p_token_value);
3953
3954 delete_advances_entry
3955 (p_period_of_service_id => p_period_of_service_id
3956 ,p_business_group_id => p_business_group_id
3957 ,p_actual_termination_date => p_actual_termination_date
3958 ,p_calling_procedure => p_calling_procedure
3959 ,p_message_name => p_message_name
3960 ,p_token_name => p_token_name
3961 ,p_token_value => p_token_value
3962 );
3963
3964 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,90);
3965 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3966
3967
3968 END delete_termination_elements;
3969
3970 --------------------------------------------------------------------------
3971 -- --
3972 -- Name : get_value_on_termination --
3973 -- Type : FUNCTION --
3974 -- Access : Public --
3975 -- Description : Function to return balance value as of the --
3976 -- termination month. --
3977 -- --
3978 -- Parameters : --
3979 -- IN : p_assignment_id NUMBER --
3980 -- p_end_date DATE --
3981 -- p_balance_name VARCHAR2 --
3982 -- p_dimension_name VARCHAR2 --
3983 -- --
3984 -- Change History : --
3985 --------------------------------------------------------------------------
3986 -- Rev# Date Userid Description --
3987 --------------------------------------------------------------------------
3988 -- 1.0 06-Jan-05 lnagaraj Created this function --
3989 --------------------------------------------------------------------------
3990 FUNCTION get_value_on_termination
3991 (p_assignment_id IN NUMBER
3992 ,p_end_date IN DATE
3993 ,p_balance_name IN VARCHAR2
3994 ,p_dimension_name IN VARCHAR2
3995 )
3996 RETURN NUMBER
3997 IS
3998
3999 CURSOR c_max_asact IS
4000 SELECT MAX(paa.assignment_action_id)
4001 FROM pay_payroll_Actions ppa
4002 ,pay_assignment_actions paa
4003 WHERE paa.assignment_id =p_assignment_id
4004 AND paa.payroll_action_id = ppa.payroll_Action_id
4005 AND ppa.action_type in('R','Q')
4006 AND TRUNC(ppa.date_earned,'MM') = TRUNC(p_end_date,'MM')
4007 AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')-- Added as a part of bug fix 4774108
4008 AND paa.source_action_id IS NULL;
4009
4010
4011 l_asg_action_id NUMBER;
4012 l_def_bal_id NUMBER;
4013 l_value NUMBER;
4014 g_debug BOOLEAN;
4015 l_procedure VARCHAR2(100);
4016 g_package VARCHAR2(100);
4017
4018 BEGIN
4019 --
4020 g_debug := hr_utility.debug_enabled;
4021 l_procedure := g_package||'get_value_on_termination';
4022 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
4023
4024 l_def_bal_id := pay_in_tax_utils.get_defined_balance(p_balance_name, p_dimension_name);
4025 pay_in_utils.set_location(g_debug, l_procedure,20);
4026 IF g_debug THEN
4027 pay_in_utils.trace('l_def_bal_id ',l_def_bal_id);
4028 END IF ;
4029
4030 OPEN c_max_asact;
4031 FETCH c_max_asact INTO l_asg_action_id;
4032 CLOSE c_max_asact;
4033 pay_in_utils.set_location(g_debug, l_procedure,30);
4034 IF g_debug THEN
4035 pay_in_utils.trace('l_asg_action_id ',l_asg_action_id);
4036 END IF ;
4037
4038 l_value := pay_balance_pkg.get_value(l_def_bal_id,l_asg_action_id);
4039 pay_in_utils.set_location(g_debug, l_procedure,40);
4040 IF g_debug THEN
4041 pay_in_utils.trace('l_value ',l_value);
4042 END IF ;
4043
4044 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
4045
4046 RETURN l_value;
4047 --
4048 EXCEPTION
4049 WHEN OTHERS THEN
4050 RETURN null;
4051
4052 END get_value_on_termination;
4053
4054 --
4055
4056 END pay_in_termination_pkg;