[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_TERMINATION_PKG
Source
1 PACKAGE BODY pay_in_termination_pkg as
2 /* $Header: pyinterm.pkb 120.24.12010000.4 2010/03/12 07:30:41 mdubasi ship $ */
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 ,pos.date_start
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_zap
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_zap
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_zap
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 l_prev_cghs_exemp_80d NUMBER;
2293
2294
2295 BEGIN
2296 g_debug := hr_utility.debug_enabled ;
2297 l_procedure := g_package || 'create_vrs_entry' ;
2298 p_message_name := 'SUCCESS';
2299 pay_in_utils.null_message (p_token_name, p_token_value);
2300
2301 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2302 -- Fix for Bug 4027040 Start
2303
2304 l_prev_earn := 0;
2305 l_prev_tds := 0;
2306 l_prev_pt := 0;
2307 l_prev_ent := 0;
2308 l_prev_pf := 0;
2309 l_prev_super := 0;
2310 l_govt_ent_alw := 0;
2311 l_prev_grat := 0;
2312 l_leave_enc := 0;
2313 l_retr_pay := 0;
2314 l_designation := 'X';
2315 l_annual_sal := 0;
2316 l_pf_number := 'X';
2317 l_pf_estab_code := 'X';
2318 l_epf_number := 'X';
2319 l_emplr_class := 'X';
2320 l_ltc_curr_block := 0;
2321 l_vrs_amount := 0;
2322 l_prev_sc := 0;
2323 l_prev_cess := 0;
2324 l_dummy := 0;
2325 l_prev_exemp_80gg:=0;
2326 l_prev_exemp_80ccd:=0;
2327 l_prev_med_reimburse := 0;
2328 l_prev_sec_and_he_cess :=0;
2329 l_prev_cghs_exemp_80d := 0;
2330
2331 l_dummy := pay_in_tax_utils.prev_emplr_details(g_assignment_id,
2332 p_actual_termination_date,
2333 l_prev_earn,
2334 l_prev_tds,
2335 l_prev_pt,
2336 l_prev_ent,
2337 l_prev_pf,
2338 l_prev_super,
2339 l_govt_ent_alw,
2340 l_prev_grat,
2341 l_leave_enc,
2342 l_retr_pay,
2343 l_designation,
2344 l_annual_sal,
2345 l_pf_number,
2346 l_pf_estab_code,
2347 l_epf_number,
2348 l_emplr_class,
2349 l_ltc_curr_block,
2350 l_vrs_amount,
2351 l_prev_sc,
2352 l_prev_cess,
2353 l_prev_exemp_80gg,
2354 l_prev_med_reimburse,
2355 l_prev_sec_and_he_cess,
2356 l_prev_exemp_80ccd,
2357 l_prev_cghs_exemp_80d);
2358 IF l_vrs_amount <> 0 THEN
2359 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2360 RETURN;
2361 END IF;
2362
2363 -- Fix for Bug 4027040 End
2364 l_ee_exists := check_ee_exists
2365 (p_element_name => g_vrs_et--l_element_name
2366 ,p_assignment_id => g_assignment_id
2367 ,p_effective_date => p_actual_termination_date
2368 ,p_element_entry_id => l_element_entry_id
2369 ,p_start_date => l_start_date
2370 ,p_ee_ovn => l_ee_ovn);
2371
2372 IF l_ee_exists THEN
2373 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2374 RETURN;
2375 END IF;
2376
2377 l_element_processed := is_element_processed
2378 (p_assignment_id =>g_assignment_id,
2379 p_element_name =>g_vrs_et);
2380
2381 IF l_element_processed THEN
2382 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2383 RETURN;
2384 END IF;
2385 --
2386 -- Element Name : Voluntary Retirement Information
2387 --
2388 -- Input Value are:
2389 --
2390 -- 1. Taxable Amount - Null
2391 -- 2. Non Taxable Amount - Null
2392 -- 3. Component Name - 'Voluntary Retirement'
2393
2394 l_entry_values(1).entry_value := null;
2395 l_entry_values(2).entry_value := null;
2396 l_entry_values(3).entry_value := g_vrs_cn;
2397 l_element_name := g_vrs_et;
2398
2399 pay_in_utils.set_location(g_debug,l_procedure,20);
2400 create_entry
2401 (p_effective_date => p_actual_termination_date
2402 ,p_business_group_id => p_business_group_id
2403 ,p_element_name => l_element_name
2404 ,p_entry_values => l_entry_values
2405 ,p_calling_procedure => p_calling_procedure
2406 ,p_message_name => p_message_name
2407 ,p_token_name => p_token_name
2408 ,p_token_value => p_token_value
2409 );
2410
2411
2412 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2413
2414 END create_vrs_entry;
2415
2416 --------------------------------------------------------------------------
2417 -- --
2418 -- Name : DELETE_VRS_ENTRY --
2419 -- Type : Procedure --
2420 -- Access : Private --
2421 -- Description : Procedure to delete 'Voluntary Retirement Information'--
2422 -- if termination is reversed for the employee. --
2423 -- --
2424 -- Parameters : --
2425 -- IN : p_period_of_service_id NUMBER --
2426 -- p_business_group_id NUMBER --
2427 -- p_actual_termination_date DATE --
2428 -- p_calling_procedure VARCHAR2 --
2429 -- OUT : p_message_name VARCHAR2 --
2430 -- p_token_name pay_in_utils.char_tab_type--
2431 -- p_token_value pay_in_utils.char_tab_type--
2432 -- Change History : --
2433 --------------------------------------------------------------------------
2434 -- Rev# Date Userid Description --
2435 --------------------------------------------------------------------------
2436 -- 1.0 19-OCT-04 statkar Created this procedure --
2437 --------------------------------------------------------------------------
2438 PROCEDURE delete_vrs_entry
2439 (p_period_of_service_id IN NUMBER
2440 ,p_business_group_id IN NUMBER
2441 ,p_actual_termination_date IN DATE
2442 ,p_calling_procedure IN VARCHAR2
2443 ,p_message_name OUT NOCOPY VARCHAR2
2444 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2445 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2446 )
2447 IS
2448 l_procedure VARCHAR2(100);
2449 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2450 l_element_name pay_element_types_f.element_name%TYPE;
2451 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2452 l_eff_start_date DATE;
2453 l_eff_end_date DATE;
2454 l_start_date DATE;
2455 l_delete_warning BOOLEAN;
2456 l_assignment_id NUMBER;
2457 l_payroll_id NUMBER;
2458 l_hire_date DATE;
2459
2460 BEGIN
2461 g_debug := hr_utility.debug_enabled ;
2462 l_procedure := g_package || 'delete_vrs_entry' ;
2463 p_message_name := 'SUCCESS';
2464 pay_in_utils.null_message (p_token_name, p_token_value);
2465
2466 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2467
2468 l_element_name := g_vrs_et;
2469
2470 IF check_ee_exists
2471 (p_element_name => l_element_name
2472 ,p_assignment_id => g_assignment_id
2473 ,p_effective_date => p_actual_termination_date
2474 ,p_element_entry_id => l_element_entry_id
2475 ,p_start_date => l_start_date
2476 ,p_ee_ovn => l_ee_ovn)
2477 THEN
2478 pay_in_utils.set_location(g_debug,l_procedure,20);
2479 IF g_debug THEN
2480 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2481 END IF;
2482
2483 IF NOT is_element_processed
2484 (p_assignment_id =>g_assignment_id,
2485 p_element_name =>l_element_name)
2486 THEN
2487 pay_element_entry_api.delete_element_entry
2488 (p_datetrack_delete_mode => hr_api.g_zap
2489 ,p_effective_date => l_start_date
2490 ,p_element_entry_id => l_element_entry_id
2491 ,p_object_version_number => l_ee_ovn
2492 ,p_effective_start_date => l_eff_start_date
2493 ,p_effective_end_date => l_eff_end_date
2494 ,p_delete_warning => l_delete_warning
2495 ) ;
2496 END IF ;
2497 pay_in_utils.set_location(g_debug,l_procedure,30);
2498
2499 END IF;
2500 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2501
2502 END delete_vrs_entry;
2503
2504 --------------------------------------------------------------------------
2505 -- --
2506 -- Name : CREATE_PENSION_ENTRY --
2507 -- Type : PROCEDURE --
2508 -- Access : Private --
2509 -- Description : Procedure to handle creation of Comm Pension EE for --
2510 -- terminated employee based on conditions as required --
2511 -- for India Localization. --
2512 -- --
2513 -- Parameters : --
2514 -- IN : p_period_of_service_id NUMBER --
2515 -- p_business_group_id NUMBER --
2516 -- p_actual_termination_date DATE --
2517 -- p_calling_procedure VARCHAR2 --
2518 -- OUT : p_message_name VARCHAR2 --
2519 -- p_token_name pay_in_utils.char_tab_type--
2520 -- p_token_value pay_in_utils.char_tab_type--
2521 -- Change History : --
2522 --------------------------------------------------------------------------
2523 -- Rev# Date Userid Description --
2524 --------------------------------------------------------------------------
2525 -- 1.0 19-OCT-04 statkar Created this procedure --
2526 --------------------------------------------------------------------------
2527 PROCEDURE create_pension_entry
2528 (p_period_of_service_id IN NUMBER
2529 ,p_business_group_id IN NUMBER
2530 ,p_actual_termination_date IN DATE
2531 ,p_calling_procedure IN VARCHAR2
2532 ,p_message_name OUT NOCOPY VARCHAR2
2533 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2534 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2535 )
2536 IS
2537 l_procedure VARCHAR2(100);
2538 l_element_name pay_element_types_f.element_name%TYPE ;
2539 l_entry_values t_entry_values_tab;
2540 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2541 l_start_date DATE;
2542 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2543 l_ee_exists BOOLEAN;
2544 l_element_processed BOOLEAN;
2545
2546 BEGIN
2547 g_debug := hr_utility.debug_enabled ;
2548 l_procedure := g_package || 'create_pension_entry' ;
2549 p_message_name := 'SUCCESS';
2550 pay_in_utils.null_message (p_token_name, p_token_value);
2551
2552 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2553
2554 l_ee_exists := check_ee_exists
2555 (p_element_name => g_pension_et--l_element_name
2556 ,p_assignment_id => g_assignment_id
2557 ,p_effective_date => p_actual_termination_date
2558 ,p_element_entry_id => l_element_entry_id
2559 ,p_start_date => l_start_date
2560 ,p_ee_ovn => l_ee_ovn);
2561
2562 IF l_ee_exists THEN
2563 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2564 RETURN;
2565 END IF;
2566
2567 l_element_processed := is_element_processed
2568 (p_assignment_id =>g_assignment_id,
2569 p_element_name =>g_pension_et);
2570
2571 IF l_element_processed THEN
2572 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
2573 RETURN;
2574 END IF;
2575
2576 --
2577 -- Element Name : Commuted Pension Information
2578 --
2579 -- Input Value are:
2580 --
2581 -- 1. Commuted Pension - Null
2582 -- 2. Normal Pension - Null
2583 -- 3. Component Name - 'Commuted Pension'
2584 --
2585 l_entry_values(1).entry_value := null;
2586 l_entry_values(2).entry_value := null;
2587 l_entry_values(3).entry_value := g_pension_cn;
2588 l_element_name := g_pension_et;
2589
2590 pay_in_utils.set_location(g_debug,l_procedure,20);
2591 create_entry
2592 (p_effective_date => p_actual_termination_date
2593 ,p_business_group_id => p_business_group_id
2594 ,p_element_name => l_element_name
2595 ,p_entry_values => l_entry_values
2596 ,p_calling_procedure => p_calling_procedure
2597 ,p_message_name => p_message_name
2598 ,p_token_name => p_token_name
2599 ,p_token_value => p_token_value
2600 );
2601
2602
2603 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2604
2605 END create_pension_entry;
2606
2607 --------------------------------------------------------------------------
2608 -- --
2609 -- Name : DELETE_PENSION_ENTRY --
2610 -- Type : Procedure --
2611 -- Access : Private --
2612 -- Description : Procedure to delete 'Commuted Pension Information' EE --
2613 -- if termination is reversed for the employee. --
2614 -- --
2615 -- Parameters : --
2616 -- IN : p_period_of_service_id NUMBER --
2617 -- p_business_group_id NUMBER --
2618 -- p_actual_termination_date DATE --
2619 -- p_calling_procedure VARCHAR2 --
2620 -- OUT : p_message_name VARCHAR2 --
2621 -- p_token_name pay_in_utils.char_tab_type--
2622 -- p_token_value pay_in_utils.char_tab_type--
2623 -- Change History : --
2624 --------------------------------------------------------------------------
2625 -- Rev# Date Userid Description --
2626 --------------------------------------------------------------------------
2627 -- 1.0 19-OCT-04 statkar Created this procedure --
2628 --------------------------------------------------------------------------
2629 PROCEDURE delete_pension_entry
2630 (p_period_of_service_id IN NUMBER
2631 ,p_business_group_id IN NUMBER
2632 ,p_actual_termination_date IN DATE
2633 ,p_calling_procedure IN VARCHAR2
2634 ,p_message_name OUT NOCOPY VARCHAR2
2635 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2636 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2637 )
2638 IS
2639 l_procedure VARCHAR2(100);
2640 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2641 l_element_name pay_element_types_f.element_name%TYPE;
2642 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2643 l_eff_start_date DATE;
2644 l_eff_end_date DATE;
2645 l_start_date DATE;
2646 l_delete_warning BOOLEAN;
2647 l_assignment_id NUMBER;
2648 l_payroll_id NUMBER;
2649 l_hire_date DATE;
2650
2651 BEGIN
2652 g_debug := hr_utility.debug_enabled ;
2653 l_procedure := g_package || 'delete_pension_entry' ;
2654 p_message_name := 'SUCCESS';
2655 pay_in_utils.null_message (p_token_name, p_token_value);
2656
2657 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2658
2659 l_element_name := g_pension_et;
2660
2661 IF check_ee_exists
2662 (p_element_name => l_element_name
2663 ,p_assignment_id => g_assignment_id
2664 ,p_effective_date => p_actual_termination_date
2665 ,p_element_entry_id => l_element_entry_id
2666 ,p_start_date => l_start_date
2667 ,p_ee_ovn => l_ee_ovn)
2668 THEN
2669 pay_in_utils.set_location(g_debug,l_procedure,20);
2670 IF g_debug THEN
2671 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2672 END IF;
2673 IF NOT is_element_processed
2674 (p_assignment_id =>g_assignment_id,
2675 p_element_name =>l_element_name)
2676 THEN
2677 pay_element_entry_api.delete_element_entry
2678 (p_datetrack_delete_mode => hr_api.g_zap
2679 ,p_effective_date => l_start_date
2680 ,p_element_entry_id => l_element_entry_id
2681 ,p_object_version_number => l_ee_ovn
2682 ,p_effective_start_date => l_eff_start_date
2683 ,p_effective_end_date => l_eff_end_date
2684 ,p_delete_warning => l_delete_warning
2685 ) ;
2686 END IF ;
2687 pay_in_utils.set_location(g_debug,l_procedure,30);
2688 END IF;
2689 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2690
2691 END delete_pension_entry;
2692
2693 --------------------------------------------------------------------------
2694 -- --
2695 -- Name : create_advances_entry --
2696 -- Type : Procedure --
2697 -- Access : Public --
2698 -- Description : Procedure to delete 'Gratuity Information' Entry --
2699 -- if termination is reversed for the employee. --
2700 -- --
2701 -- Parameters : --
2702 -- IN : p_period_of_service_id NUMBER --
2703 -- p_business_group_id NUMBER --
2704 -- p_actual_termination_date DATE --
2705 -- p_calling_procedure VARCHAR2 --
2706 -- OUT : p_message_name VARCHAR2 --
2707 -- p_token_name pay_in_utils.char_tab_type--
2708 -- p_token_value pay_in_utils.char_tab_type--
2709 -- Change History : --
2710 --------------------------------------------------------------------------
2711 -- Rev# Date Userid Description --
2712 --------------------------------------------------------------------------
2713 -- 1.0 19-OCT-04 statkar Created this procedure --
2714 --------------------------------------------------------------------------
2715 PROCEDURE create_advances_entry
2716 (p_period_of_service_id IN NUMBER
2717 ,p_business_group_id IN NUMBER
2718 ,p_actual_termination_date IN DATE
2719 ,p_calling_procedure IN VARCHAR2
2720 ,p_message_name OUT NOCOPY VARCHAR2
2721 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2722 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2723 )
2724 IS
2725 l_procedure VARCHAR2(100);
2726 l_element_name pay_element_types_f.element_name%TYPE ;
2727 l_entry_values t_entry_values_tab;
2728 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2729 l_start_date DATE;
2730 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2731 l_ee_exists BOOLEAN;
2732 l_element_processed BOOLEAN;
2733 l_def_bal_id NUMBER;
2734 l_asact_id NUMBER;
2735 l_adv_element_name pay_element_types_f.element_name%TYPE ;
2736 p_element_link_id NUMBER;
2737
2738 CURSOR csr_element IS
2739 SELECT pet.element_name
2740 ,piv1.default_value
2741 ,pbt.balance_name
2742 FROM pay_element_types_f pet,
2743 pay_element_classifications pec,
2744 pay_balance_feeds_f pbf,
2745 pay_balance_types pbt,
2746 pay_input_values_f piv,
2747 pay_input_values_f piv1,
2748 pay_element_types_f pet2
2749 WHERE pet.classification_id = pec.classification_id
2750 AND pec.classification_name = 'Voluntary Deductions'
2751 AND pec.legislation_code = 'IN'
2752 AND pet.element_name LIKE '%Recover'
2753 AND pbf.input_value_id = piv.input_value_id
2754 AND pbt.balance_type_id = pbf.balance_type_id
2755 AND pbt.balance_name IN ('Outstanding Advance for Allowances',
2756 'Outstanding Advance for Earnings',
2757 'Outstanding Advance for Fringe Benefits')
2758 AND pbt.legislation_code='IN'
2759 AND piv1.element_type_id = pet.element_type_id
2760 AND piv.name = 'Pay Value'
2761 AND piv.element_type_id = pet.element_type_id
2762 AND piv1.name ='Component Name'
2763 AND p_actual_termination_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date
2764 AND p_actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2765 AND p_actual_termination_date BETWEEN piv1.effective_start_date AND piv1.effective_end_date
2766 AND p_actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2767 AND piv.business_group_id = p_business_group_id
2768 AND piv1.business_group_id = p_business_group_id
2769 AND pet.business_group_id = p_business_group_id
2770 AND pet2.business_group_id = p_business_group_id
2771 AND pet2.element_name = SUBSTR(pet.element_name,1,INSTR(pet.element_name,' Recover',-1))||'Advance'
2772 AND p_actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2773 AND EXISTS (SELECT '1'
2774 FROM pay_run_results prr,
2775 pay_assignment_actions paa,
2776 pay_payroll_Actions ppa
2777 WHERE paa.assignment_id = g_assignment_id
2778 AND paa.assignment_action_id = prr.assignment_action_id
2779 AND paa.payroll_action_id= ppa.payroll_action_id
2780 AND ppa.action_type in('R','Q','B')
2781 AND prr.element_type_id = pet2.element_type_id
2782 AND ROWNUM =1
2783 AND ppa.action_status = 'C'
2784 AND paa.action_status = 'C'
2785 AND ppa.business_group_id = p_business_group_id
2786 );
2787
2788 CURSOR csr_asact_id IS
2789 SELECT MAX(paa.assignment_action_id)
2790 FROM pay_assignment_actions paa
2791 ,pay_payroll_actions ppa
2792 WHERE paa.assignment_id = g_assignment_id
2793 AND paa.payroll_action_id = ppa.payroll_action_id
2794 AND paa.action_status = 'C'
2795 AND paa.source_action_id IS NULL
2796 AND ppa.action_type in ('R','Q')
2797 AND ppa.action_status = 'C' ;
2798
2799
2800 BEGIN
2801 g_debug := hr_utility.debug_enabled ;
2802 l_procedure := g_package || 'create_advances_entry' ;
2803 p_message_name := 'SUCCESS';
2804 pay_in_utils.null_message (p_token_name, p_token_value);
2805
2806 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2807
2808
2809
2810 FOR i in csr_element LOOP
2811
2812
2813 l_def_bal_id := pay_in_tax_utils.get_defined_balance
2814 ( p_balance_type => i.balance_name
2815 ,p_dimension_name => '_ASG_COMP_LTD');
2816
2817 OPEN csr_asact_id;
2818 FETCH csr_asact_id INTO l_asact_id;
2819 CLOSE csr_asact_id;
2820
2821 l_entry_values(1).entry_value :=
2822 pay_balance_pkg.get_value
2823 (p_defined_balance_id => l_def_bal_id
2824 ,p_assignment_action_id => l_asact_id
2825 ,p_tax_unit_id => ''
2826 ,p_jurisdiction_code => ''
2827 ,p_source_id => ''
2828 ,p_source_text => ''
2829 ,p_source_text2 => i.default_value
2830 ,p_tax_group => ''
2831 ,p_date_earned => ''
2832 ,p_get_rr_route => 'TRUE'
2833 ,p_get_rb_route => ''
2834 );
2835
2836 pay_in_utils.set_location(g_debug,l_procedure,20);
2837
2838 IF(l_entry_values(1).entry_value > 0) THEN
2839 l_entry_values(1).entry_value := 'RECOVER' ;
2840 l_entry_values(2).entry_value := i.default_value;
2841 l_element_name := substr(i.element_name,1,instr(i.element_name,' Recover',-1))||'Excess Advance';
2842
2843 pay_in_utils.set_location(g_debug,l_procedure,30);
2844
2845 l_ee_exists := check_advance_exists
2846 (p_component_name => i.default_value
2847 ,p_assignment_id => g_assignment_id
2848 ,p_effective_date => p_actual_termination_date
2849 ,p_element_entry_id => l_element_entry_id
2850 ,p_start_date => l_start_date
2851 ,p_ee_ovn => l_ee_ovn
2852 );
2853
2854 l_element_processed := is_element_processed
2855 (p_assignment_id =>g_assignment_id,
2856 p_element_name =>l_element_name);
2857
2858
2859 IF l_ee_exists OR l_element_processed THEN
2860 pay_in_utils.set_location(g_debug,l_procedure,40);
2861 ELSE
2862 pay_in_utils.set_location(g_debug,l_procedure,50);
2863 create_entry
2864 (p_effective_date => p_actual_termination_date
2865 ,p_business_group_id => p_business_group_id
2866 ,p_element_name => l_element_name
2867 ,p_entry_values => l_entry_values
2868 ,p_calling_procedure => p_calling_procedure
2869 ,p_message_name => p_message_name
2870 ,p_token_name => p_token_name
2871 ,p_token_value => p_token_value
2872 );
2873 END IF;
2874 pay_in_utils.set_location(g_debug,l_procedure,60);
2875 END IF;
2876
2877 END LOOP;
2878
2879 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
2880
2881 END create_advances_entry;
2882
2883 --------------------------------------------------------------------------
2884 -- --
2885 -- Name : delete_advances_entry --
2886 -- Type : Procedure --
2887 -- Access : Public --
2888 -- Description : Procedure to delete 'Gratuity Information' Entry --
2889 -- if termination is reversed for the employee. --
2890 -- --
2891 -- Parameters : --
2892 -- IN : p_period_of_service_id NUMBER --
2893 -- p_business_group_id NUMBER --
2894 -- p_actual_termination_date DATE --
2895 -- p_calling_procedure VARCHAR2 --
2896 -- OUT : p_message_name VARCHAR2 --
2897 -- p_token_name pay_in_utils.char_tab_type--
2898 -- p_token_value pay_in_utils.char_tab_type--
2899 -- Change History : --
2900 --------------------------------------------------------------------------
2901 -- Rev# Date Userid Description --
2902 --------------------------------------------------------------------------
2903 -- 1.0 19-OCT-04 statkar Created this procedure --
2904 --------------------------------------------------------------------------
2905 PROCEDURE delete_advances_entry
2906 (p_period_of_service_id IN NUMBER
2907 ,p_business_group_id IN NUMBER
2908 ,p_actual_termination_date IN DATE
2909 ,p_calling_procedure IN VARCHAR2
2910 ,p_message_name OUT NOCOPY VARCHAR2
2911 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2912 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2913 )
2914 IS
2915 l_procedure VARCHAR2(100);
2916 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2917 l_element_name pay_element_types_f.element_name%TYPE;
2918 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
2919 l_eff_start_date DATE;
2920 l_eff_end_date DATE;
2921 l_start_date DATE;
2922 l_delete_warning BOOLEAN;
2923
2924 CURSOR csr_element IS
2925 SELECT pet.element_name
2926 FROM pay_element_types_f pet,
2927 pay_element_classifications pec
2928 WHERE pet.classification_id = pec.classification_id
2929 AND pec.classification_name = 'Information'
2930 AND pet.element_name LIKE '%Excess Advance'
2931 AND pet.business_group_id = p_business_group_id
2932 and p_actual_termination_date between pet.effective_start_date and pet.effective_end_date;
2933
2934
2935 BEGIN
2936 g_debug := hr_utility.debug_enabled ;
2937 l_procedure := g_package || 'delete_advances_entry' ;
2938 p_message_name := 'SUCCESS';
2939 pay_in_utils.null_message (p_token_name, p_token_value);
2940
2941 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2942
2943 FOR i in csr_element loop
2944
2945 IF check_ee_exists
2946 (p_element_name => i.element_name
2947 ,p_assignment_id => g_assignment_id
2948 ,p_effective_date => p_actual_termination_date
2949 ,p_element_entry_id => l_element_entry_id
2950 ,p_start_date => l_start_date
2951 ,p_ee_ovn => l_ee_ovn)
2952 THEN
2953 pay_in_utils.set_location(g_debug,l_procedure,20);
2954 IF g_debug THEN
2955 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
2956 END IF;
2957 IF NOT is_element_processed
2958 (p_assignment_id =>g_assignment_id,
2959 p_element_name =>i.element_name)
2960 THEN
2961 pay_element_entry_api.delete_element_entry
2962 (p_datetrack_delete_mode => hr_api.g_zap
2963 ,p_effective_date => l_start_date
2964 ,p_element_entry_id => l_element_entry_id
2965 ,p_object_version_number => l_ee_ovn
2966 ,p_effective_start_date => l_eff_start_date
2967 ,p_effective_end_date => l_eff_end_date
2968 ,p_delete_warning => l_delete_warning
2969 ) ;
2970 END IF ;
2971 pay_in_utils.set_location(g_debug,l_procedure,30);
2972
2973 END IF;
2974 END LOOP;
2975
2976 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2977
2978 END delete_advances_entry;
2979
2980 --------------------------------------------------------------------------
2981 -- --
2982 -- Name : CREATE_PF_ENTRY --
2983 -- Type : PROCEDURE --
2984 -- Access : Private --
2985 -- Description : Procedure to handle creation of PF Settlement EE for--
2986 -- terminated employee based on conditions as required --
2987 -- for India Localization. --
2988 -- --
2989 -- Parameters : --
2990 -- IN : p_period_of_service_id NUMBER --
2991 -- p_business_group_id NUMBER --
2992 -- p_actual_termination_date DATE --
2993 -- p_calling_procedure VARCHAR2 --
2994 -- OUT : p_message_name VARCHAR2 --
2995 -- p_token_name pay_in_utils.char_tab_type--
2996 -- p_token_value pay_in_utils.char_tab_type--
2997 -- Change History : --
2998 --------------------------------------------------------------------------
2999 -- Rev# Date Userid Description --
3000 --------------------------------------------------------------------------
3001 -- 1.0 19-OCT-04 statkar Created this procedure --
3002 --------------------------------------------------------------------------
3003 PROCEDURE create_pf_entry
3004 (p_period_of_service_id IN NUMBER
3005 ,p_business_group_id IN NUMBER
3006 ,p_actual_termination_date IN DATE
3007 ,p_calling_procedure IN VARCHAR2
3008 ,p_message_name OUT NOCOPY VARCHAR2
3009 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3010 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3011 )
3012 IS
3013 l_procedure VARCHAR2(100);
3014 l_element_name pay_element_types_f.element_name%TYPE ;
3015 l_entry_values t_entry_values_tab;
3016 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3017 l_start_date DATE;
3018 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3019 l_ee_exists BOOLEAN;
3020 l_element_processed BOOLEAN;
3021
3022 BEGIN
3023 g_debug := hr_utility.debug_enabled ;
3024 l_procedure := g_package || 'create_pf_entry' ;
3025 p_message_name := 'SUCCESS';
3026 pay_in_utils.null_message (p_token_name, p_token_value);
3027
3028 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3029
3030 l_ee_exists := check_ee_exists
3031 (p_element_name => g_pf_et--l_element_name
3032 ,p_assignment_id => g_assignment_id
3033 ,p_effective_date => p_actual_termination_date
3034 ,p_element_entry_id => l_element_entry_id
3035 ,p_start_date => l_start_date
3036 ,p_ee_ovn => l_ee_ovn);
3037
3038 IF l_ee_exists THEN
3039 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3040 RETURN;
3041 END IF;
3042
3043 l_element_processed := is_element_processed
3044 (p_assignment_id =>g_assignment_id,
3045 p_element_name =>g_pf_et);
3046
3047 IF l_element_processed THEN
3048 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3049 RETURN;
3050 END IF;
3051 --
3052 -- Element Name : PF Settlement Information
3053 --
3054 -- Input Value are:
3055 --
3056 -- 1. PF Settlement Amount - Null
3057 -- 2. Continuous Service - Calculate
3058 -- 3. Employee Contribution - Null
3059 -- 4. Component Name - 'PF Settlement'
3060 --
3061 l_entry_values(1).entry_value := null;
3062 IF (months_between(p_actual_termination_date, g_hire_date)/12 ) > 5 THEN
3063 l_entry_values(2).entry_value := 'Y';
3064 ELSE
3065 l_entry_values(2).entry_value := 'N';
3066 END IF;
3067 l_entry_values(3).entry_value := null;
3068 l_entry_values(4).entry_value := g_pf_cn;
3069 l_element_name := g_pf_et;
3070
3071 pay_in_utils.set_location(g_debug,l_procedure,20);
3072 create_entry
3073 (p_effective_date => p_actual_termination_date
3074 ,p_business_group_id => p_business_group_id
3075 ,p_element_name => l_element_name
3076 ,p_entry_values => l_entry_values
3077 ,p_calling_procedure => p_calling_procedure
3078 ,p_message_name => p_message_name
3079 ,p_token_name => p_token_name
3080 ,p_token_value => p_token_value
3081 );
3082
3083 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
3084
3085 END create_pf_entry;
3086
3087 --------------------------------------------------------------------------
3088 -- --
3089 -- Name : DELETE_PF_ENTRY --
3090 -- Type : Procedure --
3091 -- Access : Private --
3092 -- Description : Procedure to delete 'PF Settlement Information' EE --
3093 -- if termination is reversed for the employee. --
3094 -- --
3095 -- Parameters : --
3096 -- IN : p_period_of_service_id NUMBER --
3097 -- p_business_group_id NUMBER --
3098 -- p_actual_termination_date DATE --
3099 -- p_calling_procedure VARCHAR2 --
3100 -- OUT : p_message_name VARCHAR2 --
3101 -- p_token_name pay_in_utils.char_tab_type--
3102 -- p_token_value pay_in_utils.char_tab_type--
3103 -- Change History : --
3104 --------------------------------------------------------------------------
3105 -- Rev# Date Userid Description --
3106 --------------------------------------------------------------------------
3107 -- 1.0 19-OCT-04 statkar Created this procedure --
3108 --------------------------------------------------------------------------
3109 PROCEDURE delete_pf_entry
3110 (p_period_of_service_id IN NUMBER
3111 ,p_business_group_id IN NUMBER
3112 ,p_actual_termination_date IN DATE
3113 ,p_calling_procedure IN VARCHAR2
3114 ,p_message_name OUT NOCOPY VARCHAR2
3115 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3116 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3117 )
3118 IS
3119 l_procedure VARCHAR2(100);
3120 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3121 l_element_name pay_element_types_f.element_name%TYPE;
3122 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3123 l_eff_start_date DATE;
3124 l_eff_end_date DATE;
3125 l_start_date DATE;
3126 l_delete_warning BOOLEAN;
3127 l_assignment_id NUMBER;
3128 l_payroll_id NUMBER;
3129 l_hire_date DATE;
3130
3131 BEGIN
3132 g_debug := hr_utility.debug_enabled ;
3133 l_procedure := g_package || 'delete_pf_entry' ;
3134 p_message_name := 'SUCCESS';
3135 pay_in_utils.null_message (p_token_name, p_token_value);
3136
3137 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3138
3139 l_element_name := g_pf_et;
3140
3141 IF check_ee_exists
3142 (p_element_name => l_element_name
3143 ,p_assignment_id => g_assignment_id
3144 ,p_effective_date => p_actual_termination_date
3145 ,p_element_entry_id => l_element_entry_id
3146 ,p_start_date => l_start_date
3147 ,p_ee_ovn => l_ee_ovn)
3148 THEN
3149 pay_in_utils.set_location(g_debug,l_procedure,20);
3150 IF g_debug THEN
3151 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
3152 END IF;
3153 IF NOT is_element_processed
3154 (p_assignment_id =>g_assignment_id,
3155 p_element_name =>l_element_name)
3156 THEN
3157 pay_element_entry_api.delete_element_entry
3158 (p_datetrack_delete_mode => hr_api.g_zap
3159 ,p_effective_date => l_start_date
3160 ,p_element_entry_id => l_element_entry_id
3161 ,p_object_version_number => l_ee_ovn
3162 ,p_effective_start_date => l_eff_start_date
3163 ,p_effective_end_date => l_eff_end_date
3164 ,p_delete_warning => l_delete_warning
3165 ) ;
3166 END IF ;
3167 pay_in_utils.set_location(g_debug,l_procedure,30);
3168
3169 END IF;
3170 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,40);
3171
3172 END delete_pf_entry;
3173 --------------------------------------------------------------------------
3174 -- --
3175 -- Name : CREATE_LOAN_ENTRY --
3176 -- Type : PROCEDURE --
3177 -- Access : Private --
3178 -- Description : Procedure to handle creation of Loan Recovery EE for--
3179 -- terminated employee based on conditions as required --
3180 -- for India Localization. --
3181 -- --
3182 -- Parameters : --
3183 -- IN : p_period_of_service_id NUMBER --
3184 -- p_business_group_id NUMBER --
3185 -- p_actual_termination_date DATE --
3186 -- p_calling_procedure VARCHAR2 --
3187 -- OUT : p_message_name VARCHAR2 --
3188 -- p_token_name pay_in_utils.char_tab_type--
3189 -- p_token_value pay_in_utils.char_tab_type--
3190 -- Change History : --
3191 --------------------------------------------------------------------------
3192 -- Rev# Date Userid Description --
3193 --------------------------------------------------------------------------
3194 -- 1.0 19-OCT-04 statkar Created this procedure --
3195 --------------------------------------------------------------------------
3196 PROCEDURE create_loan_entry
3197 (p_period_of_service_id IN NUMBER
3198 ,p_business_group_id IN NUMBER
3199 ,p_actual_termination_date IN DATE
3200 ,p_calling_procedure IN VARCHAR2
3201 ,p_message_name OUT NOCOPY VARCHAR2
3202 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3203 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3204 )
3205 IS
3206 l_procedure VARCHAR2(100);
3207 l_element_name pay_element_types_f.element_name%TYPE ;
3208 l_entry_values t_entry_values_tab;
3209 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3210 l_start_date DATE;
3211 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3212 l_ee_exists BOOLEAN;
3213 l_element_processed BOOLEAN;
3214
3215 CURSOR c_ee_dtls IS
3216 SELECT pee.element_entry_id
3217 FROM pay_element_entries_f pee
3218 ,pay_input_values_f piv
3219 ,pay_element_entry_values_f peev
3220 WHERE pee.assignment_id = g_assignment_id
3221 AND pee.element_entry_id = peev.element_entry_id
3222 AND peev.input_value_id = piv.input_value_id
3223 AND piv.name = 'Component Name'
3224 AND peev.screen_entry_value = 'Loan at Concessional Rate'
3225 AND p_actual_termination_date BETWEEN pee.effective_start_date
3226 AND pee.effective_end_date
3227 AND p_actual_termination_date BETWEEN piv.effective_start_date
3228 AND piv.effective_end_date
3229 AND p_actual_termination_date BETWEEN peev.effective_start_date
3230 AND peev.effective_end_date;
3231
3232 l_ee_id pay_element_entries_f.element_entry_id%TYPE;
3233
3234 CURSOR c_iv_dtls (p_element_entry_id IN NUMBER
3235 , p_name IN VARCHAR2)
3236 IS
3237 SELECT peev.screen_entry_value
3238 FROM pay_element_entry_values_f peev
3239 ,pay_input_values_f piv
3240 WHERE peev.element_entry_id = p_element_entry_id
3241 AND peev.input_value_id = piv.input_value_id
3242 AND piv.NAME = p_name
3243 AND p_actual_termination_date BETWEEN peev.effective_start_date
3244 AND peev.effective_end_date
3245 AND p_actual_termination_date BETWEEN piv.effective_start_date
3246 AND piv.effective_end_date;
3247
3248 l_loan_number pay_element_entry_values_f.screen_entry_value%TYPE;
3249 l_loan_type pay_element_entry_values_f.screen_entry_value%TYPE;
3250
3251 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
3252
3253 CURSOR csr_asact_id IS
3254 SELECT max(paa.assignment_action_id)
3255 FROM pay_assignment_actions paa
3256 ,pay_payroll_actions ppa
3257 WHERE paa.assignment_id = g_assignment_id
3258 AND paa.payroll_action_id = ppa.payroll_action_id
3259 AND paa.action_status = 'C'
3260 AND paa.source_action_id IS NULL
3261 AND ppa.action_type in ('R','Q')
3262 AND ppa.action_status = 'C' ;
3263
3264 l_asact_id pay_assignment_actions.assignment_action_id%TYPE;
3265 BEGIN
3266 g_debug := hr_utility.debug_enabled ;
3267 l_procedure := g_package || 'create_loan_entry' ;
3268 p_message_name := 'SUCCESS';
3269 pay_in_utils.null_message (p_token_name, p_token_value);
3270
3271 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3272
3273 --
3274 -- Fetch the Loan Number and Loan Type for each loan
3275 --
3276 l_element_name := g_loan_et;
3277 OPEN c_ee_dtls;
3278 LOOP
3279 FETCH c_ee_dtls
3280 INTO l_ee_id;
3281 EXIT WHEN c_ee_dtls%NOTFOUND;
3282
3283 OPEN c_iv_dtls (l_ee_id, 'Loan Number');
3284 FETCH c_iv_dtls INTO l_loan_number;
3285 CLOSE c_iv_dtls;
3286
3287 OPEN c_iv_dtls (l_ee_id, 'Loan Type');
3288 FETCH c_iv_dtls INTO l_loan_type;
3289 CLOSE c_iv_dtls;
3290
3291 l_ee_exists := check_ee_exists
3292 (p_element_name => l_element_name
3293 ,p_input_value_name => 'Loan Number'
3294 ,p_input_value => l_loan_number
3295 ,p_assignment_id => g_assignment_id
3296 ,p_effective_date => p_actual_termination_date
3297 ,p_element_entry_id => l_element_entry_id
3298 ,p_start_date => l_start_date
3299 ,p_ee_ovn => l_ee_ovn);
3300
3301 IF NOT l_ee_exists THEN
3302 pay_in_utils.set_location(g_debug,l_procedure,20);
3303
3304 l_element_processed := is_element_processed
3305 (p_assignment_id =>g_assignment_id,
3306 p_element_name =>l_element_name);
3307
3308 IF l_element_processed THEN
3309 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,15);
3310 RETURN;
3311 END IF;
3312 --
3313 -- Element Name : Loan Recovery
3314 --
3315 -- Input Value are:
3316 --
3317 -- 1. Pay Value - Calculate
3318 -- 2. Loan Number - Associate
3319 -- 3. Loan Type - Associate
3320 -- 4. Component Name - 'Loan Recovery'
3321
3322 l_def_bal_id := pay_in_tax_utils.get_defined_balance
3323 ( p_balance_type => 'Maximum Outstanding Amount'
3324 ,p_dimension_name => '_ASG_SRC_LTD');
3325
3326 OPEN csr_asact_id;
3327 FETCH csr_asact_id INTO l_asact_id;
3328 CLOSE csr_asact_id;
3329
3330 l_entry_values(1).entry_value :=
3331 pay_balance_pkg.get_value
3332 (p_defined_balance_id => l_def_bal_id
3333 ,p_assignment_action_id => l_asact_id
3334 ,p_tax_unit_id => ''
3335 ,p_jurisdiction_code => ''
3336 ,p_source_id => ''
3337 ,p_source_text => l_loan_number
3338 ,p_tax_group => ''
3339 ,p_date_earned => ''
3340 ,p_get_rr_route => 'TRUE'
3341 ,p_get_rb_route => ''
3342 );
3343
3344 l_entry_values(1).entry_value := (-1)*l_entry_values(1).entry_value ;
3345 l_entry_values(2).entry_value := l_loan_number;
3346 l_entry_values(3).entry_value := l_loan_type;
3347 l_entry_values(4).entry_value := g_loan_cn;
3348
3349 pay_in_utils.set_location(g_debug,l_procedure,30);
3350 create_entry
3351 (p_effective_date => p_actual_termination_date
3352 ,p_business_group_id => p_business_group_id
3353 ,p_element_name => l_element_name
3354 ,p_entry_values => l_entry_values
3355 ,p_calling_procedure => p_calling_procedure
3356 ,p_message_name => p_message_name
3357 ,p_token_name => p_token_name
3358 ,p_token_value => p_token_value
3359 );
3360
3361 pay_in_utils.set_location(g_debug,l_procedure,40);
3362 END IF;
3363 END LOOP;
3364 CLOSE c_ee_dtls;
3365 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3366
3367 END create_loan_entry;
3368
3369 --------------------------------------------------------------------------
3370 -- --
3371 -- Name : DELETE_LOAN_ENTRY --
3372 -- Type : Procedure --
3373 -- Access : Private --
3374 -- Description : Procedure to delete 'Loan Recovery' EE --
3375 -- if termination is reversed for the employee. --
3376 -- --
3377 -- Parameters : --
3378 -- IN : p_period_of_service_id NUMBER --
3379 -- p_business_group_id NUMBER --
3380 -- p_actual_termination_date DATE --
3381 -- p_calling_procedure VARCHAR2 --
3382 -- OUT : p_message_name VARCHAR2 --
3383 -- p_token_name pay_in_utils.char_tab_type--
3384 -- p_token_value pay_in_utils.char_tab_type--
3385 -- Change History : --
3386 --------------------------------------------------------------------------
3387 -- Rev# Date Userid Description --
3388 --------------------------------------------------------------------------
3389 -- 1.0 19-OCT-04 statkar Created this procedure --
3390 --------------------------------------------------------------------------
3391 PROCEDURE delete_loan_entry
3392 (p_period_of_service_id IN NUMBER
3393 ,p_business_group_id IN NUMBER
3394 ,p_actual_termination_date IN DATE
3395 ,p_calling_procedure IN VARCHAR2
3396 ,p_message_name OUT NOCOPY VARCHAR2
3397 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3398 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3399 )
3400 IS
3401 l_procedure VARCHAR2(100);
3402 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
3403 l_element_name pay_element_types_f.element_name%TYPE;
3404 l_ee_ovn pay_element_entries_f.object_version_number%TYPE;
3405 l_eff_start_date DATE;
3406 l_eff_end_date DATE;
3407 l_start_date DATE;
3408 l_delete_warning BOOLEAN;
3409
3410 CURSOR c_ee_dtls IS
3411 SELECT pee.element_entry_id
3412 FROM pay_element_entries_f pee
3413 ,pay_input_values_f piv
3414 ,pay_element_entry_values_f peev
3415 WHERE pee.assignment_id = g_assignment_id
3416 AND pee.element_entry_id = peev.element_entry_id
3417 AND peev.input_value_id = piv.input_value_id
3418 AND piv.name = 'Component Name'
3419 AND peev.screen_entry_value = 'Loan at Concessional Rate'
3420 AND p_actual_termination_date BETWEEN pee.effective_start_date
3421 AND pee.effective_end_date
3422 AND p_actual_termination_date BETWEEN piv.effective_start_date
3423 AND piv.effective_end_date
3424 AND p_actual_termination_date BETWEEN peev.effective_start_date
3425 AND peev.effective_end_date;
3426
3427 l_ee_id pay_element_entries_f.element_entry_id%TYPE;
3428
3429 CURSOR c_iv_dtls (p_element_entry_id IN NUMBER
3430 , p_name IN VARCHAR2)
3431 IS
3432 SELECT peev.screen_entry_value
3433 FROM pay_element_entry_values_f peev
3434 ,pay_input_values_f piv
3435 WHERE peev.element_entry_id = p_element_entry_id
3436 AND peev.input_value_id = piv.input_value_id
3437 AND piv.NAME = p_name
3438 AND p_actual_termination_date BETWEEN peev.effective_start_date
3439 AND peev.effective_end_date
3440 AND p_actual_termination_date BETWEEN piv.effective_start_date
3441 AND piv.effective_end_date;
3442
3443 l_loan_number pay_element_entry_values_f.screen_entry_value%TYPE;
3444 l_loan_type pay_element_entry_values_f.screen_entry_value%TYPE;
3445
3446
3447 BEGIN
3448 g_debug := hr_utility.debug_enabled ;
3449 l_procedure := g_package || 'delete_loan_entry' ;
3450 p_message_name := 'SUCCESS';
3451 pay_in_utils.null_message (p_token_name, p_token_value);
3452
3453 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3454
3455 l_element_name := g_loan_et;
3456
3457 --
3458 -- Fetch the Loan Number and Loan Type for each loan
3459 --
3460 OPEN c_ee_dtls;
3461 LOOP
3462 FETCH c_ee_dtls
3463 INTO l_ee_id;
3464 EXIT WHEN c_ee_dtls%NOTFOUND;
3465
3466 OPEN c_iv_dtls (l_ee_id, 'Loan Number');
3467 FETCH c_iv_dtls INTO l_loan_number;
3468 CLOSE c_iv_dtls;
3469
3470 OPEN c_iv_dtls (l_ee_id, 'Loan Type');
3471 FETCH c_iv_dtls INTO l_loan_type;
3472 CLOSE c_iv_dtls;
3473
3474 IF check_ee_exists
3475 (p_element_name => l_element_name
3476 ,p_input_value_name => 'Loan Number'
3477 ,p_input_value => l_loan_number
3478 ,p_assignment_id => g_assignment_id
3479 ,p_effective_date => p_actual_termination_date
3480 ,p_element_entry_id => l_element_entry_id
3481 ,p_start_date => l_start_date
3482 ,p_ee_ovn => l_ee_ovn)
3483 THEN
3484 pay_in_utils.set_location(g_debug,l_procedure,20);
3485 IF g_debug THEN
3486 pay_in_utils.trace('Element Entry ID ',to_char(l_element_entry_id));
3487 END IF;
3488 IF NOT is_element_processed
3489 (p_assignment_id =>g_assignment_id,
3490 p_element_name =>l_element_name)
3491 THEN
3492 pay_element_entry_api.delete_element_entry
3493 (p_datetrack_delete_mode => hr_api.g_zap
3494 ,p_effective_date => l_start_date
3495 ,p_element_entry_id => l_element_entry_id
3496 ,p_object_version_number => l_ee_ovn
3497 ,p_effective_start_date => l_eff_start_date
3498 ,p_effective_end_date => l_eff_end_date
3499 ,p_delete_warning => l_delete_warning
3500 ) ;
3501 END IF ;
3502 pay_in_utils.set_location(g_debug,l_procedure,30);
3503
3504 END IF;
3505 END LOOP;
3506 CLOSE c_ee_dtls;
3507 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3508
3509 END delete_loan_entry;
3510
3511
3512 --------------------------------------------------------------------------
3513 -- --
3514 -- Name : CHECK_PF_LEAV_REASONS --
3515 -- Type : PROCEDURE --
3516 -- Access : Private --
3517 -- Description : Internal Proc to be called for validating the PF --
3518 -- leaving reason before termination --
3519 -- --
3520 -- Parameters : --
3521 -- IN : p_period_of_service_id NUMBER --
3522 -- p_business_group_id NUMBER --
3523 -- p_actual_termination_date DATE --
3524 -- p_assignment_id NUMBER --
3525 -- p_calling_procedure VARCHAR2 --
3526 -- OUT : p_message_name VARCHAR2 --
3527 -- p_token_name pay_in_utils.char_tab_type --
3528 -- p_token_value pay_in_utils.char_tab_type --
3529 -- Change History : --
3530 --------------------------------------------------------------------------
3531 -- Rev# Date Userid Description --
3532 --------------------------------------------------------------------------
3533 -- 1.0 28-Aug-07 sivanara Created this procedure --
3534 -- 1.1 25-Sep-07 rsaharay Modified c_emp_mon_pf_pos_dtls --
3535 --------------------------------------------------------------------------
3536 PROCEDURE check_PF_leav_reasons(p_period_of_service_id IN NUMBER
3537 ,p_business_group_id IN NUMBER
3538 ,p_actual_termination_date IN DATE
3539 ,p_assignment_id IN NUMBER
3540 ,p_calling_procedure IN VARCHAR2
3541 ,p_message_name OUT NOCOPY VARCHAR2
3542 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3543 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3544 )
3545 IS
3546 CURSOR c_emp_mon_pf_pos_dtls IS
3547 SELECT ppf.per_information15
3548 ,pos.pds_information1
3549 ,pos.pds_information2
3550 FROM per_periods_of_service pos
3551 ,per_assignments_f paf
3552 ,per_people_f ppf
3553 ,hr_soft_coding_keyflex scl
3554 WHERE pos.period_of_service_id = p_period_of_service_id
3555 AND pos.business_group_id = p_business_group_id
3556 AND paf.assignment_id = p_assignment_id
3557 AND pos.period_of_service_id = paf.period_of_service_id
3558 AND paf.person_id = ppf.person_id
3559 AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3560 AND scl.enabled_flag = 'Y'
3561 AND scl.segment2 IS NOT NULL
3562 AND ppf.per_information15 IS NOT NULL
3563 AND (to_char(paf.effective_start_date,'Month-YYYY')=to_char(p_actual_termination_date,'Month-YYYY')
3564 OR to_char(paf.effective_end_date,'Month-YYYY')=to_char(p_actual_termination_date,'Month-YYYY')
3565 OR p_actual_termination_date between paf.effective_start_date and paf.effective_end_date)
3566 AND p_actual_termination_date BETWEEN ppf.effective_start_date
3567 AND ppf.effective_end_date;
3568
3569 l_procedure VARCHAR2(100);
3570 l_NSSN per_people_f.per_information15%TYPE;
3571 l_print_leav_reas VARCHAR2(50);
3572 l_efile_leav_reas VARCHAR2 (50);
3573
3574 BEGIN
3575 g_debug := hr_utility.debug_enabled ;
3576 l_procedure := g_package || 'check_PF_leav_reasons' ;
3577 p_message_name := 'SUCCESS';
3578 pay_in_utils.null_message (p_token_name, p_token_value);
3579 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3580
3581 IF g_debug THEN
3582 pay_in_utils.trace('Period of service id ',to_char(p_period_of_service_id));
3583 pay_in_utils.trace('Business Group ID ',to_char(p_business_group_id));
3584 pay_in_utils.trace('Effective Date ',to_char(p_actual_termination_date, 'DD-MM-YYYY'));
3585 pay_in_utils.trace('Assignment ID ',to_char(p_assignment_id));
3586 pay_in_utils.trace('Calling Procedure ',p_calling_procedure);
3587 END IF;
3588
3589 pay_in_utils.set_location(g_debug,l_procedure,20);
3590
3591 OPEN c_emp_mon_pf_pos_dtls;
3592 FETCH c_emp_mon_pf_pos_dtls
3593 INTO l_NSSN, l_print_leav_reas, l_efile_leav_reas;
3594 CLOSE c_emp_mon_pf_pos_dtls;
3595
3596 IF l_NSSN IS NOT NULL AND (l_print_leav_reas IS NULL OR l_efile_leav_reas IS NULL) THEN
3597 p_message_name := 'PER_IN_PF_LEAV_REASON';
3598 pay_in_utils.set_location(g_debug,l_procedure,30);
3599 END IF;
3600 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3601 END check_PF_leav_reasons;
3602
3603 --------------------------------------------------------------------------
3604 -- --
3605 -- Name : CREATE_TERMINATION_ELEMENTS --
3606 -- Type : PROCEDURE --
3607 -- Access : Public --
3608 -- Description : Procedure to handle creation of Termination EE for --
3609 -- terminated employee based on conditions as required --
3610 -- for India Localization. --
3611 -- --
3612 -- Parameters : --
3613 -- IN : p_period_of_service_id NUMBER --
3614 -- p_business_group_id NUMBER --
3615 -- p_actual_termination_date DATE --
3616 -- p_calling_procedure VARCHAR2 --
3617 -- OUT : p_message_name VARCHAR2 --
3618 -- p_token_name pay_in_utils.char_tab_type--
3619 -- p_token_value pay_in_utils.char_tab_type--
3620 -- Change History : --
3621 --------------------------------------------------------------------------
3622 -- Rev# Date Userid Description --
3623 --------------------------------------------------------------------------
3624 -- 1.0 19-OCT-04 statkar Created this procedure --
3625 -- 1.1 28-AUG-07 sivanara Added code for validation of Monthly PF --
3626 -- returns leaving reasons --
3627 --------------------------------------------------------------------------
3628 PROCEDURE create_termination_elements
3629 (p_period_of_service_id IN NUMBER
3630 ,p_business_group_id IN NUMBER
3631 ,p_actual_termination_date IN DATE
3632 ,p_calling_procedure IN VARCHAR2
3633 ,p_message_name OUT NOCOPY VARCHAR2
3634 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3635 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3636 )
3637 IS
3638 l_procedure VARCHAR2(100);
3639
3640 BEGIN
3641 g_debug := hr_utility.debug_enabled ;
3642 l_procedure := g_package || 'create_termination_elements' ;
3643 p_message_name := 'SUCCESS';
3644 pay_in_utils.null_message (p_token_name, p_token_value);
3645
3646 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3647
3648 get_pos_dtls(p_period_of_service_id => p_period_of_service_id
3649 ,p_business_group_id => p_business_group_id
3650 ,p_effective_date => p_actual_termination_date
3651 );
3652
3653 IF g_debug THEN
3654 pay_in_utils.trace('Period of service id ',to_char(p_period_of_service_id));
3655 pay_in_utils.trace('Business Group ID ',to_char(p_business_group_id));
3656 pay_in_utils.trace('Act Term Date ',to_char(p_actual_termination_date, 'DD-MM-YYYY'));
3657 pay_in_utils.trace('Assignment ID ',to_char(g_assignment_id));
3658 pay_in_utils.trace('Payroll ID ',to_char(g_payroll_id));
3659 pay_in_utils.trace('Hire Date ',to_char(g_hire_date,'DD-MM-YYYY'));
3660 pay_in_utils.trace('Notified Term Date ',to_char(g_notified_date,'DD-MM-YYYY'));
3661 END IF;
3662
3663 IF g_notified_date IS NULL THEN
3664 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3665 p_token_name(1) := 'API_NAME';
3666 p_token_value(1) := p_calling_procedure;
3667 p_token_name(2) := 'ARGUMENT';
3668 p_token_value(2) := 'Notified Termination Date';
3669 RETURN;
3670 END IF;
3671
3672 check_PF_leav_reasons(p_period_of_service_id => p_period_of_service_id
3673 ,p_business_group_id => p_business_group_id
3674 ,p_actual_termination_date => p_actual_termination_date
3675 ,p_assignment_id => g_assignment_id
3676 ,p_calling_procedure => p_calling_procedure
3677 ,p_message_name => p_message_name
3678 ,p_token_name => p_token_name
3679 ,p_token_value => p_token_value
3680 );
3681
3682 pay_in_utils.set_location(g_debug,l_procedure,15);
3683 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3684 p_message_name := 'SUCCESS';
3685 pay_in_utils.null_message (p_token_name, p_token_value);
3686
3687 create_notice_entry
3688 (p_period_of_service_id => p_period_of_service_id
3689 ,p_business_group_id => p_business_group_id
3690 ,p_actual_termination_date => p_actual_termination_date
3691 ,p_calling_procedure => p_calling_procedure
3692 ,p_message_name => p_message_name
3693 ,p_token_name => p_token_name
3694 ,p_token_value => p_token_value
3695 );
3696
3697 pay_in_utils.set_location(g_debug,l_procedure,20);
3698 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3699 p_message_name := 'SUCCESS';
3700 pay_in_utils.null_message (p_token_name, p_token_value);
3701
3702 create_retrenchment_entry
3703 (p_period_of_service_id => p_period_of_service_id
3704 ,p_business_group_id => p_business_group_id
3705 ,p_actual_termination_date => p_actual_termination_date
3706 ,p_calling_procedure => p_calling_procedure
3707 ,p_message_name => p_message_name
3708 ,p_token_name => p_token_name
3709 ,p_token_value => p_token_value
3710 );
3711
3712 pay_in_utils.set_location(g_debug,l_procedure,30);
3713 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3714 p_message_name := 'SUCCESS';
3715 pay_in_utils.null_message (p_token_name, p_token_value);
3716
3717 create_vrs_entry
3718 (p_period_of_service_id => p_period_of_service_id
3719 ,p_business_group_id => p_business_group_id
3720 ,p_actual_termination_date => p_actual_termination_date
3721 ,p_calling_procedure => p_calling_procedure
3722 ,p_message_name => p_message_name
3723 ,p_token_name => p_token_name
3724 ,p_token_value => p_token_value
3725 );
3726
3727 pay_in_utils.set_location(g_debug,l_procedure,40);
3728 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3729 p_message_name := 'SUCCESS';
3730 pay_in_utils.null_message (p_token_name, p_token_value);
3731
3732 create_pension_entry
3733 (p_period_of_service_id => p_period_of_service_id
3734 ,p_business_group_id => p_business_group_id
3735 ,p_actual_termination_date => p_actual_termination_date
3736 ,p_calling_procedure => p_calling_procedure
3737 ,p_message_name => p_message_name
3738 ,p_token_name => p_token_name
3739 ,p_token_value => p_token_value
3740 );
3741
3742 pay_in_utils.set_location(g_debug,l_procedure,50);
3743 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3744 p_message_name := 'SUCCESS';
3745 pay_in_utils.null_message (p_token_name, p_token_value);
3746
3747 create_pf_entry
3748 (p_period_of_service_id => p_period_of_service_id
3749 ,p_business_group_id => p_business_group_id
3750 ,p_actual_termination_date => p_actual_termination_date
3751 ,p_calling_procedure => p_calling_procedure
3752 ,p_message_name => p_message_name
3753 ,p_token_name => p_token_name
3754 ,p_token_value => p_token_value
3755 );
3756
3757 pay_in_utils.set_location(g_debug,l_procedure,60);
3758 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3759 p_message_name := 'SUCCESS';
3760 pay_in_utils.null_message (p_token_name, p_token_value);
3761
3762 create_loan_entry
3763 (p_period_of_service_id => p_period_of_service_id
3764 ,p_business_group_id => p_business_group_id
3765 ,p_actual_termination_date => p_actual_termination_date
3766 ,p_calling_procedure => p_calling_procedure
3767 ,p_message_name => p_message_name
3768 ,p_token_name => p_token_name
3769 ,p_token_value => p_token_value
3770 );
3771 pay_in_utils.set_location(g_debug,l_procedure,70);
3772 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3773 p_message_name := 'SUCCESS';
3774 pay_in_utils.null_message (p_token_name, p_token_value);
3775
3776 create_gratuity_entry
3777 (p_period_of_service_id => p_period_of_service_id
3778 ,p_business_group_id => p_business_group_id
3779 ,p_actual_termination_date => p_actual_termination_date
3780 ,p_calling_procedure => p_calling_procedure
3781 ,p_message_name => p_message_name
3782 ,p_token_name => p_token_name
3783 ,p_token_value => p_token_value
3784 );
3785
3786 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
3787 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3788
3789 create_advances_entry
3790 (p_period_of_service_id => p_period_of_service_id
3791 ,p_business_group_id => p_business_group_id
3792 ,p_actual_termination_date => p_actual_termination_date
3793 ,p_calling_procedure => p_calling_procedure
3794 ,p_message_name => p_message_name
3795 ,p_token_name => p_token_name
3796 ,p_token_value => p_token_value
3797 );
3798
3799 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
3800 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3801
3802
3803
3804 END create_termination_elements;
3805
3806 --------------------------------------------------------------------------
3807 -- --
3808 -- Name : DELETE_TERMINATION_ELEMENTS --
3809 -- Type : Procedure --
3810 -- Access : Public --
3811 -- Description : Procedure to delete all Termination Element entries --
3812 -- if termination is reversed for the employee. --
3813 -- --
3814 -- Parameters : --
3815 -- IN : p_period_of_service_id NUMBER --
3816 -- p_business_group_id NUMBER --
3817 -- p_actual_termination_date DATE --
3818 -- p_calling_procedure VARCHAR2 --
3819 -- OUT : p_message_name VARCHAR2 --
3820 -- p_token_name pay_in_utils.char_tab_type--
3821 -- p_token_value pay_in_utils.char_tab_type--
3822 -- Change History : --
3823 --------------------------------------------------------------------------
3824 -- Rev# Date Userid Description --
3825 --------------------------------------------------------------------------
3826 -- 1.0 19-OCT-04 statkar Created this procedure --
3827 --------------------------------------------------------------------------
3828 PROCEDURE delete_termination_elements
3829 (p_period_of_service_id IN NUMBER
3830 ,p_business_group_id IN NUMBER
3831 ,p_actual_termination_date IN DATE
3832 ,p_calling_procedure IN VARCHAR2
3833 ,p_message_name OUT NOCOPY VARCHAR2
3834 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
3835 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
3836 )
3837 IS
3838 l_procedure VARCHAR2(100);
3839
3840 BEGIN
3841 g_debug := hr_utility.debug_enabled ;
3842 l_procedure := g_package || 'delete_termination_elements' ;
3843 p_message_name := 'SUCCESS';
3844 pay_in_utils.null_message (p_token_name, p_token_value);
3845
3846 get_pos_dtls(p_period_of_service_id => p_period_of_service_id
3847 ,p_business_group_id => p_business_group_id
3848 ,p_effective_date => p_actual_termination_date
3849 );
3850
3851
3852 delete_notice_entry
3853 (p_period_of_service_id => p_period_of_service_id
3854 ,p_business_group_id => p_business_group_id
3855 ,p_actual_termination_date => p_actual_termination_date
3856 ,p_calling_procedure => p_calling_procedure
3857 ,p_message_name => p_message_name
3858 ,p_token_name => p_token_name
3859 ,p_token_value => p_token_value
3860 );
3861
3862 pay_in_utils.set_location(g_debug,l_procedure,20);
3863 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3864 p_message_name := 'SUCCESS';
3865 pay_in_utils.null_message (p_token_name, p_token_value);
3866
3867 delete_retrenchment_entry
3868 (p_period_of_service_id => p_period_of_service_id
3869 ,p_business_group_id => p_business_group_id
3870 ,p_actual_termination_date => p_actual_termination_date
3871 ,p_calling_procedure => p_calling_procedure
3872 ,p_message_name => p_message_name
3873 ,p_token_name => p_token_name
3874 ,p_token_value => p_token_value
3875 );
3876
3877 pay_in_utils.set_location(g_debug,l_procedure,30);
3878 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3879 p_message_name := 'SUCCESS';
3880 pay_in_utils.null_message (p_token_name, p_token_value);
3881
3882 delete_vrs_entry
3883 (p_period_of_service_id => p_period_of_service_id
3884 ,p_business_group_id => p_business_group_id
3885 ,p_actual_termination_date => p_actual_termination_date
3886 ,p_calling_procedure => p_calling_procedure
3887 ,p_message_name => p_message_name
3888 ,p_token_name => p_token_name
3889 ,p_token_value => p_token_value
3890 );
3891
3892 pay_in_utils.set_location(g_debug,l_procedure,40);
3893 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3894 p_message_name := 'SUCCESS';
3895 pay_in_utils.null_message (p_token_name, p_token_value);
3896
3897 delete_pension_entry
3898 (p_period_of_service_id => p_period_of_service_id
3899 ,p_business_group_id => p_business_group_id
3900 ,p_actual_termination_date => p_actual_termination_date
3901 ,p_calling_procedure => p_calling_procedure
3902 ,p_message_name => p_message_name
3903 ,p_token_name => p_token_name
3904 ,p_token_value => p_token_value
3905 );
3906
3907 pay_in_utils.set_location(g_debug,l_procedure,50);
3908 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3909 p_message_name := 'SUCCESS';
3910 pay_in_utils.null_message (p_token_name, p_token_value);
3911
3912 delete_pf_entry
3913 (p_period_of_service_id => p_period_of_service_id
3914 ,p_business_group_id => p_business_group_id
3915 ,p_actual_termination_date => p_actual_termination_date
3916 ,p_calling_procedure => p_calling_procedure
3917 ,p_message_name => p_message_name
3918 ,p_token_name => p_token_name
3919 ,p_token_value => p_token_value
3920 );
3921
3922 pay_in_utils.set_location(g_debug,l_procedure,60);
3923 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3924 p_message_name := 'SUCCESS';
3925 pay_in_utils.null_message (p_token_name, p_token_value);
3926
3927 delete_loan_entry
3928 (p_period_of_service_id => p_period_of_service_id
3929 ,p_business_group_id => p_business_group_id
3930 ,p_actual_termination_date => p_actual_termination_date
3931 ,p_calling_procedure => p_calling_procedure
3932 ,p_message_name => p_message_name
3933 ,p_token_name => p_token_name
3934 ,p_token_value => p_token_value
3935 );
3936
3937 pay_in_utils.set_location(g_debug,l_procedure,70);
3938 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3939 p_message_name := 'SUCCESS';
3940 pay_in_utils.null_message (p_token_name, p_token_value);
3941
3942 delete_gratuity_entry
3943 (p_period_of_service_id => p_period_of_service_id
3944 ,p_business_group_id => p_business_group_id
3945 ,p_actual_termination_date => p_actual_termination_date
3946 ,p_calling_procedure => p_calling_procedure
3947 ,p_message_name => p_message_name
3948 ,p_token_name => p_token_name
3949 ,p_token_value => p_token_value
3950 );
3951
3952 pay_in_utils.set_location(g_debug,l_procedure,80);
3953 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3954 p_message_name := 'SUCCESS';
3955 pay_in_utils.null_message (p_token_name, p_token_value);
3956
3957 delete_advances_entry
3958 (p_period_of_service_id => p_period_of_service_id
3959 ,p_business_group_id => p_business_group_id
3960 ,p_actual_termination_date => p_actual_termination_date
3961 ,p_calling_procedure => p_calling_procedure
3962 ,p_message_name => p_message_name
3963 ,p_token_name => p_token_name
3964 ,p_token_value => p_token_value
3965 );
3966
3967 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,90);
3968 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3969
3970
3971 END delete_termination_elements;
3972
3973 --------------------------------------------------------------------------
3974 -- --
3975 -- Name : get_value_on_termination --
3976 -- Type : FUNCTION --
3977 -- Access : Public --
3978 -- Description : Function to return balance value as of the --
3979 -- termination month. --
3980 -- --
3981 -- Parameters : --
3982 -- IN : p_assignment_id NUMBER --
3983 -- p_end_date DATE --
3984 -- p_balance_name VARCHAR2 --
3985 -- p_dimension_name VARCHAR2 --
3986 -- --
3987 -- Change History : --
3988 --------------------------------------------------------------------------
3989 -- Rev# Date Userid Description --
3990 --------------------------------------------------------------------------
3991 -- 1.0 06-Jan-05 lnagaraj Created this function --
3992 --------------------------------------------------------------------------
3993 FUNCTION get_value_on_termination
3994 (p_assignment_id IN NUMBER
3995 ,p_end_date IN DATE
3996 ,p_balance_name IN VARCHAR2
3997 ,p_dimension_name IN VARCHAR2
3998 )
3999 RETURN NUMBER
4000 IS
4001
4002 CURSOR c_max_asact IS
4003 SELECT MAX(paa.assignment_action_id)
4004 FROM pay_payroll_Actions ppa
4005 ,pay_assignment_actions paa
4006 WHERE paa.assignment_id =p_assignment_id
4007 AND paa.payroll_action_id = ppa.payroll_Action_id
4008 AND ppa.action_type in('R','Q')
4009 AND TRUNC(ppa.date_earned,'MM') = TRUNC(p_end_date,'MM')
4010 AND ppa.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')-- Added as a part of bug fix 4774108
4011 AND paa.source_action_id IS NULL;
4012
4013
4014 l_asg_action_id NUMBER;
4015 l_def_bal_id NUMBER;
4016 l_value NUMBER;
4017 g_debug BOOLEAN;
4018 l_procedure VARCHAR2(100);
4019 g_package VARCHAR2(100);
4020
4021 BEGIN
4022 --
4023 g_debug := hr_utility.debug_enabled;
4024 l_procedure := g_package||'get_value_on_termination';
4025 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
4026
4027 l_def_bal_id := pay_in_tax_utils.get_defined_balance(p_balance_name, p_dimension_name);
4028 pay_in_utils.set_location(g_debug, l_procedure,20);
4029 IF g_debug THEN
4030 pay_in_utils.trace('l_def_bal_id ',l_def_bal_id);
4031 END IF ;
4032
4033 OPEN c_max_asact;
4034 FETCH c_max_asact INTO l_asg_action_id;
4035 CLOSE c_max_asact;
4036 pay_in_utils.set_location(g_debug, l_procedure,30);
4037 IF g_debug THEN
4038 pay_in_utils.trace('l_asg_action_id ',l_asg_action_id);
4039 END IF ;
4040
4041 l_value := pay_balance_pkg.get_value(l_def_bal_id,l_asg_action_id);
4042 pay_in_utils.set_location(g_debug, l_procedure,40);
4043 IF g_debug THEN
4044 pay_in_utils.trace('l_value ',l_value);
4045 END IF ;
4046
4047 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
4048
4049 RETURN l_value;
4050 --
4051 EXCEPTION
4052 WHEN OTHERS THEN
4053 RETURN null;
4054
4055 END get_value_on_termination;
4056
4057 --
4058
4059 END pay_in_termination_pkg;