[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_TERM_RPRT_GEN_PKG
Source
1 PACKAGE BODY PAY_IN_TERM_RPRT_GEN_PKG as
2 /* $Header: pyintrpt.pkb 120.31.12020000.4 2012/11/02 12:05:22 anchhetr ship $ */
3
4 p_xml_data CLOB;
5 g_package CONSTANT VARCHAR2(100) := 'pay_in_term_rprt_gen_pkg.';
6 g_debug BOOLEAN ;
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : multiColumnar --
10 -- Type : PROCEDURE --
11 -- Access : Public --
12 -- Description : Procedure to create xml for multiple columns --
13 -- --
14 -- Parameters : --
15 -- IN : p_type VARCHAR2 --
16 -- p_data tXMLTable --
17 -- p_count NUMBER --
18 -- --
19 -- Change History : --
20 --------------------------------------------------------------------------
21 -- Rev# Date Userid Description --
22 --------------------------------------------------------------------------
23 -- 1.0 04/11/04 abhjain Created this function --
24 --------------------------------------------------------------------------
25
26 procedure multiColumnar(p_type IN VARCHAR2
27 ,p_data IN tXMLTable
28 ,p_count IN NUMBER)
29 IS
30 l_tag VARCHAR2(300);
31 l_procedure VARCHAR2(250);
32 l_message VARCHAR2(250);
33 BEGIN
34 g_debug := hr_utility.debug_enabled;
35 l_procedure := g_package ||'multiColumnar';
36 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
37 IF (g_debug)
38 THEN
39 pay_in_utils.trace('**************************************************','********************');
40 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
41 pay_in_utils.trace('p_type',p_type);
42 pay_in_utils.trace('p_count',p_count);
43 END IF;
44
45 --
46 l_tag := '<'||p_type||'>';
47 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
48
49 FOR i in 1..p_count
50 LOOP
51 --
52 IF p_data.exists(i) THEN
53 --
54 l_tag := getTag(p_data(i).Name, p_data(i).Value);
55 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
56 --
57 END IF;
58 --
59 END LOOP;
60 --
61 l_tag := '</'||p_type||'>';
62 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
63 pay_in_utils.trace('**************************************************','********************');
64 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
65 --
66 END multiColumnar;
67
68 --------------------------------------------------------------------------
69 -- --
70 -- Name : twoColumnar --
71 -- Type : PROCEDURE --
72 -- Access : Public --
73 -- Description : Procedure to create xml for two columns --
74 -- --
75 -- Parameters : --
76 -- IN : p_type VARCHAR2 --
77 -- p_data tXMLTable --
78 -- p_count NUMBER --
79 -- --
80 -- Change History : --
81 --------------------------------------------------------------------------
82 -- Rev# Date Userid Description --
83 --------------------------------------------------------------------------
84 -- 1.0 04/11/04 abhjain Created this function --
85 --------------------------------------------------------------------------
86 procedure twoColumnar(p_type IN VARCHAR2
87 ,p_data IN tXMLTable
88 ,p_count IN NUMBER)
89 IS
90 l_tag VARCHAR2(300);
91 l_procedure VARCHAR2(250);
92 l_message VARCHAR2(250);
93 BEGIN
94 g_debug := hr_utility.debug_enabled;
95 l_procedure := g_package ||'twoColumnar';
96 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
97 IF (g_debug)
98 THEN
99 pay_in_utils.trace('**************************************************','********************');
100 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
101 pay_in_utils.trace('p_type',p_type);
102 pay_in_utils.trace('p_count',p_count);
103 END IF;
104 --
105 FOR i in 1..p_count
106 LOOP
107 --
108 IF p_data.exists(i) THEN
109 --
110 -- Start Main tag
111 l_tag := '<'||p_type||'>';
112 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
113 --
114 -- Put Description tag
115 l_tag := getTag('c_description', p_data(i).Name);
116 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
117 --
118 -- Put amount tag
119 l_tag := getTag('c_amount', p_data(i).Value);
120 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
121 --
122 -- End Main tag
123 l_tag := '</'||p_type||'>';
124 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
125 --
126 END IF;
127 --
128 END LOOP;
129 pay_in_utils.trace('**************************************************','********************');
130 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
131 --
132 end twoColumnar;
133
134 --------------------------------------------------------------------------
135 -- --
136 -- Name : getTag --
137 -- Type : FUNCTION --
138 -- Access : Public --
139 -- Description : Procedure to create tags --
140 -- --
141 -- Parameters : --
142 -- IN : p_type VARCHAR2 --
143 -- p_data tXMLTable --
144 -- p_count NUMBER --
145 -- --
146 -- Change History : --
147 --------------------------------------------------------------------------
148 -- Rev# Date Userid Description --
149 --------------------------------------------------------------------------
150 -- 1.0 04/11/04 abhjain Created this function --
151 -- 1.1 22/12/04 aaagarwa Encoded HTML literals --
152 --------------------------------------------------------------------------
153 FUNCTION getTag(p_tag_name IN VARCHAR2
154 ,p_tag_value IN VARCHAR2)
155 RETURN VARCHAR2
156 IS
157 l_tag_value VARCHAR2(255);
158 l_procedure VARCHAR2(250);
159 l_message VARCHAR2(250);
160 BEGIN
161 --
162 g_debug := hr_utility.debug_enabled;
163 l_procedure := g_package ||'getTag';
164 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
165
166 l_tag_value:=nvl(pay_in_utils.encode_html_string(p_tag_value),' ');
167 --Return Tag
168 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
169 return '<'||p_tag_name||'>'||l_tag_value||'</'||p_tag_name||'>';
170 --
171 END getTag;
172
173 --------------------------------------------------------------------------
174 -- --
175 -- Name : GET_TEMPLATE --
176 -- Type : PROCEDURE --
177 -- Access : Public --
178 -- Description : This procedure gets the final settlement template --
179 -- code set at organization level.If no template is --
180 -- set default template code is returned --
181 -- --
182 -- Parameters : --
183 -- IN : p_business_group_id NUMBER --
184 -- OUT : p_template VARCHAR2 --
185 --------------------------------------------------------------------------
186 --
187
188 PROCEDURE get_template (
189 p_business_group_id IN NUMBER
190 ,p_template OUT NOCOPY VARCHAR2
191 )
192 IS
193 --
194 CURSOR csr_final_settlement_info
195 IS
196 --
197 SELECT NVL(org_information7,'PER_IN_TERM_TEMPLATE') template
198 FROM hr_organization_information_v
199 WHERE organization_id = p_business_group_id
200 AND org_information_context= 'PER_IN_STAT_SETUP_DF';
201 --
202 l_template VARCHAR2(50);
203 l_procedure VARCHAR(100);
204 l_message VARCHAR2(250);
205
206 --
207 BEGIN
208 --
209 l_procedure := g_package || 'get_template';
210 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
211
212 IF g_debug THEN
213 pay_in_utils.trace('**************************************************','********************');
214 pay_in_utils.trace('p_business_group_id',p_business_group_id);
215 pay_in_utils.trace('**************************************************','********************');
216 END IF;
217
218
219
220 OPEN csr_final_settlement_info;
221 FETCH csr_final_settlement_info
222 INTO l_template;
223
224 -- If Organization level payslip information does not exists return default
225 -- Else return the information set
226 IF (csr_final_settlement_info%NOTFOUND) THEN
227 --
228 p_template := 'PER_IN_TERM_TEMPLATE';
229 --
230 ELSE
231 --
232 pay_in_utils.trace('l_template ',l_template);
233 pay_in_utils.set_location(g_debug,l_procedure,20);
234
235 p_template := l_template;
236 --
237 END IF;
238 --
239 IF g_debug THEN
240 pay_in_utils.trace('**************************************************','********************');
241 pay_in_utils.trace('p_template',p_template);
242 pay_in_utils.trace('**************************************************','********************');
243 END IF;
244
245 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
250 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
251 pay_in_utils.trace(l_message,l_procedure);
252
253 IF csr_final_settlement_info%ISOPEN THEN
254 CLOSE csr_final_settlement_info;
255 END IF;
256 RAISE;
257 --
258 --
259 END get_template;
260
261 --------------------------------------------------------------------------
262 -- --
263 -- Name : create_xml --
264 -- Type : PROCEDURE --
265 -- Access : Public --
266 -- Description : Procedure to create tags --
267 -- --
268 -- Parameters : --
269 -- IN : p_employee_number VARCHAR2 --
270 -- p_bus_grp_id NUMBER --
271 -- OUT : l_xml_data CLOB --
272 -- --
273 -- Change History : --
274 --------------------------------------------------------------------------
275 -- Rev# Date Userid Description --
276 --------------------------------------------------------------------------
277 -- 1.0 04/11/04 abhjain Created this function --
278 -- 1.1 16/11/04 abhjain Updated after the internal review --
279 -- 1.2 06/12/07 sivanara Include code for "Advances" and --
280 -- "Fringe Benefits" --
281 --------------------------------------------------------------------------
282 PROCEDURE create_xml(p_employee_number IN VARCHAR2
283 ,p_bus_grp_id IN NUMBER
284 ,p_term_date IN VARCHAR2
285 ,l_xml_data OUT NOCOPY CLOB)
286 IS
287 --
288 --Declarion section
289 --
290 l_tag VARCHAR2(300);
291 l_count NUMBER;
292 l_total_earnings NUMBER;
293 l_total_deductions NUMBER;
294 l_total_employer_charges NUMBER;
295 l_total_perquisites NUMBER;
296 l_total_other_deductions NUMBER;
297 l_total_loan_recovery NUMBER;
298 l_total_paid NUMBER;
299 l_total_taxable NUMBER;
300 l_total_exempted NUMBER;
301 l_gross_earnings NUMBER;
302 l_leave_encashed_flag NUMBER;
303 l_gratuity_paid_flag NUMBER;
304 l_emp_dues_flag NUMBER;
305 l_net_pay NUMBER;
306 l_payment_flag NUMBER;
307 l_grat_elig_sal NUMBER;
308 l_exempted NUMBER;
309 l_paid NUMBER;
310 l_taxable NUMBER;
311 l_total_fringe_benefit NUMBER;
312 l_total_advance NUMBER;
313 l_term_date DATE ;
314
315 --
316 -- Cursor to get the Employee Personal Detials
317 CURSOR c_employee_details (p_employee_number VARCHAR2
318 ,p_bus_grp_id NUMBER
319 ,p_term_date DATE )
320 IS
321 SELECT ppf.full_name name
322 ,ppf.employee_number employee_number
323 ,to_char(ppf.date_of_birth,'dd-Mon-yyyy') dob
324 ,to_char(ppos.date_start,'dd-Mon-yyyy') doj
325 ,round((months_between(ppos.actual_termination_date
326 , ppf.date_of_birth))/12) age
327 ,hr_general.decode_lookup('LEAV_REAS'
328 ,ppos.leaving_reason) leaving_reason
329 ,to_char(ppos.actual_termination_date,'dd-Mon-yyyy') dol
330 ,trunc(months_between(ppos.actual_termination_date
331 , ppos.date_start)/12) || ' Years and '
332 || (1 + ppos.actual_termination_date - add_months(ppos.date_start
333 ,12*trunc(months_between
334 (ppos.actual_termination_date, ppos.date_start)/12))) ||' Days' los
335 ,hou.name department
336 ,org.name ORGANIZATION
337 ,loc.location_code location
338 FROM per_people_f ppf
339 ,per_assignments_f paf
340 ,per_periods_of_service ppos
341 ,hr_soft_coding_keyflex hsck
342 ,hr_organization_units hou
343 ,hr_organization_units org
344 ,hr_locations loc
345 WHERE ppf.business_group_id = p_bus_grp_id
346 AND ppf.employee_number = p_employee_number
347 AND ppf.person_id = paf.person_id
348 AND paf.location_id = loc.location_id(+)
349 AND ppos.period_of_service_id = paf.period_of_service_id
350 AND ppos.business_group_id = ppf.business_group_id
351 AND ppf.person_id = ppos.person_id
352 AND hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
353 AND hou.organization_id = hsck.segment1
354 AND org.organization_id = paf.organization_id
355 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
356 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
357 AND ppos.actual_termination_date = p_term_date;
358 --
359 -- Cursor to get the Employee Designation
360 CURSOR c_employee_designation (p_employee_number VARCHAR2
361 ,p_bus_grp_id NUMBER
362 ,p_term_date DATE )
363 IS
364 SELECT nvl(pp.name,pj.name) designation
365 FROM per_positions pp
366 ,per_jobs pj
367 ,per_people_f ppf
368 ,per_assignments_f paf
369 ,per_periods_of_service ppos
370 WHERE ppf.business_group_id = p_bus_grp_id
371 AND ppf.employee_number = p_employee_number
372 AND ppf.person_id = paf.person_id
373 AND ppos.period_of_service_id = paf.period_of_service_id
374 AND ppos.business_group_id = ppf.business_group_id
375 AND ppf.person_id = ppos.person_id
376 AND pp.position_id(+) = paf.position_id
377 AND pj.job_id(+) = paf.job_id
378 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
379 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
380 AND ppos.actual_termination_date = p_term_date
381 AND paf.effective_end_date between date_effective(+) AND nvl(date_end(+), to_date('31-12-4712', 'DD-MM-YYYY'))
382 AND paf.effective_end_date between pj.date_from(+) AND NVL(pj.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'));
383
384
385 --
386 -- Cursor to get the Run results for the following Element Classifications
387 -- 1. Earnings, Allowances
388 -- 2. Deductions, Involuntary Deductions, Involuntary Deductions, Pre Tax Deductions, Tax Deductions
389 -- 3. Employer Charges
390 -- 4. Perquisites
391 -- 5. Termination Payments (except the element 'Loan Recovery' which is handled
392 -- in a separate cursor)
393 --
394 CURSOR c_run_results(p_employee_number VARCHAR2
395 ,p_bus_grp_id NUMBER
396 ,p_max_asg_id NUMBER)
397 IS
398 SELECT nvl(pet.reporting_name, pet.element_name) description
399 ,sum(prrv.result_value) amount
400 ,pec.classification_name classification
401 ,pet.element_name elename
402 FROM per_assignments_f paf
403 ,per_people_f ppf
404 ,pay_element_types_f pet
405 ,pay_input_values_f piv
406 ,pay_assignment_actions paa
407 ,pay_run_results prr
408 ,pay_run_result_values prrv
409 ,per_periods_of_service ppos
410 ,pay_element_classifications pec
411 WHERE paf.business_group_id = p_bus_grp_id
412 AND ppf.employee_number = p_employee_number
413 AND ppf.person_id = paf.person_id
414 AND paf.period_of_service_id = ppos.period_of_service_id
415 AND ppf.person_id = ppos.person_id
416 AND paf.assignment_id = paa.assignment_id /*Added as per 7283019 */
417 AND pec.classification_name IN ('Earnings',
418 'Allowances',
419 'Deductions',
420 'Employer Charges',
421 'Perquisites',
422 'Termination Payments',
423 'Involuntary Deductions',
424 'Voluntary Deductions',
425 'Pre Tax Deductions',
426 'Tax Deductions',
427 'Advances', --Added for bug fix 6660147
428 'Fringe Benefits') --Added for bug fix 6660147
429 AND (pet.business_group_id = paf.business_group_id or pet.legislation_code = 'IN')
430 AND pec.classification_id = pet.classification_id
431 AND pec.legislation_code = 'IN'
432 AND piv.element_type_id = pet.element_type_id
433 AND piv.name = 'Pay Value'
434 AND paa.source_action_id = p_max_asg_id
435 AND prr.assignment_action_id = paa.assignment_action_id
436 AND prr.element_type_id = pet.element_type_id
437 AND pet.element_name <> 'Loan Recovery'
438 AND prr.status IN ('P', 'PA')
439 AND prr.run_result_id = prrv.run_result_id
440 AND prrv.input_value_id = piv.input_value_id
441 --AND fnd_number.canonical_to_number(prrv.result_value) <> 0)
442 AND prrv.result_value <> '0'
443 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
444 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
445 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
446 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
447 GROUP BY nvl(pet.reporting_name, pet.element_name)
448 ,pec.classification_name, pet.element_name
449 ORDER BY nvl(pet.reporting_name, pet.element_name);
450
451 --Bug 4774108 addition for SQL ID 14928511 starts
452 -- This cursor returns the maximum run assignment action id for an employee number.
453 CURSOR c_max_asg_act_id(p_employee_number VARCHAR2
454 ,p_bus_grp_id NUMBER
455 ,p_term_date DATE )
456 IS
457 SELECT max(paa.assignment_action_id)
458 FROM pay_assignment_actions paa
459 ,pay_payroll_actions ppa
460 ,per_people_f ppf
461 ,per_assignments_f paf
462 ,per_periods_of_service ppos
463 WHERE ppf.employee_number = p_employee_number
464 AND ppf.person_id = paf.person_id
465 AND paf.period_of_service_id = ppos.period_of_service_id
466 AND ppf.person_id = ppos.person_id
467 AND paf.business_group_id = ppf.business_group_id
468 AND ppf.business_group_id = ppa.business_group_id
469 AND ppa.business_group_id = p_bus_grp_id
470 AND paa.assignment_id = paf.assignment_id
471 AND ppa.payroll_id = paf.payroll_id
472 AND paa.payroll_action_id = ppa.payroll_action_id
473 AND paa.action_status = 'C'
474 AND paa.source_action_id IS NULL
475 AND ppa.action_type in ('R','Q')
476 AND ppa.action_status = 'C'
477 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
478 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
479 AND ppos.actual_termination_date = p_term_date;
480
481
482 --Bug 6772976 final Settlement report.
483 --This Cursor returns Final Settlement Report Information at Bussiness Group Level.
484 CURSOR c_final_report_info(p_bus_grp_id NUMBER)
485 IS
486 SELECT org_information6
487 FROM hr_organization_information
488 WHERE organization_id = p_bus_grp_id
489 AND org_information_context = 'PER_IN_STAT_SETUP_DF';
490
491 --This Cursor returns the Last Standard Process Date for an employee number.
492
493 CURSOR c_last_std_process_date(p_employee_number VARCHAR2
494 ,p_bus_grp_id NUMBER
495 ,p_term_date DATE )
496 IS
497 SELECT max(last_day(pos.last_standard_process_date))
498 FROM per_periods_of_service pos
499 ,per_people_f ppf
500 WHERE ppf.employee_number = p_employee_number
501 AND ppf.person_id = pos.person_id
502 AND ppf.business_group_id = p_bus_grp_id
503 AND pos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
504 AND pos.actual_termination_date = p_term_date;
505
506 --This cursor returns the maximum run assignment action id for an employee number depending on
507 --Last Standard Process Date.
508 CURSOR c_lsp_max_asg_act_id(p_employee_number VARCHAR2
509 ,p_bus_grp_id NUMBER
510 ,p_term_date DATE
511 ,p_process_date DATE)
512 IS
513 SELECT max(paa.assignment_action_id)
514 FROM pay_assignment_actions paa
515 ,pay_payroll_actions ppa
516 ,per_people_f ppf
517 ,per_assignments_f paf
518 ,per_periods_of_service ppos
519 WHERE ppf.employee_number = p_employee_number
520 AND ppf.person_id = paf.person_id
521 AND paf.period_of_service_id = ppos.period_of_service_id
522 AND ppf.person_id = ppos.person_id
523 AND paf.business_group_id = ppf.business_group_id
524 AND ppf.business_group_id = ppa.business_group_id
525 AND ppa.business_group_id = p_bus_grp_id
526 AND paa.assignment_id = paf.assignment_id
527 AND ppa.payroll_id = paf.payroll_id
528 AND paa.payroll_action_id = ppa.payroll_action_id
529 AND paa.action_status = 'C'
530 AND paa.source_action_id IS NULL
531 AND ppa.action_type in ('R','Q')
532 AND ppa.action_status = 'C'
533 AND last_day(ppa.date_earned) = p_process_date
534 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
535 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
536 AND ppos.actual_termination_date = p_term_date;
537
538 --This Cursor returns the maximum run assignment action id for an employee number
539 --for payment details .
540
541 CURSOR c_payment_max_asg_act_id(p_employee_number VARCHAR2
542 ,p_bus_grp_id NUMBER
543 ,p_term_date DATE )
544 IS
545 SELECT max(paa.assignment_action_id)
546 FROM pay_assignment_actions paa
547 ,pay_payroll_actions ppa
548 ,per_people_f ppf
549 ,per_assignments_f paf
550 ,per_periods_of_service ppos
551 WHERE ppf.employee_number = p_employee_number
552 AND ppf.person_id = paf.person_id
553 AND paf.period_of_service_id = ppos.period_of_service_id
554 AND ppf.person_id = ppos.person_id
555 AND paf.business_group_id = ppf.business_group_id
556 AND ppf.business_group_id = ppa.business_group_id
557 AND ppa.business_group_id = p_bus_grp_id
558 AND paa.assignment_id = paf.assignment_id
559 AND ppa.payroll_id = paf.payroll_id
560 AND paa.payroll_action_id = ppa.payroll_action_id
561 AND paa.action_status = 'C'
562 AND paa.source_action_id IS NOT NULL
563 AND ppa.action_type in ('R','Q')
564 AND ppa.action_status = 'C'
565 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
566 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
567 AND ppos.actual_termination_date = p_term_date;
568
569
570 --This Cursor returns the maximum run assignment action id for an employee number
571 --for payment details depending on last standard process date.
572
573 CURSOR c_lsp_payment_max_asg_act_id(p_employee_number VARCHAR2
574 ,p_bus_grp_id NUMBER
575 ,p_term_date DATE
576 ,p_process_date DATE)
577 IS
578 SELECT max(paa.assignment_action_id)
579 FROM pay_assignment_actions paa
580 ,pay_payroll_actions ppa
581 ,per_people_f ppf
582 ,per_assignments_f paf
583 ,per_periods_of_service ppos
584 WHERE ppf.employee_number = p_employee_number
585 AND ppf.person_id = paf.person_id
586 AND paf.period_of_service_id = ppos.period_of_service_id
587 AND ppf.person_id = ppos.person_id
588 AND paf.business_group_id = ppf.business_group_id
589 AND ppf.business_group_id = ppa.business_group_id
590 AND ppa.business_group_id = p_bus_grp_id
591 AND paa.assignment_id = paf.assignment_id
592 AND ppa.payroll_id = paf.payroll_id
593 AND paa.payroll_action_id = ppa.payroll_action_id
594 AND paa.action_status = 'C'
595 AND paa.source_action_id IS NOT NULL
596 AND ppa.action_type in ('R','Q')
597 AND ppa.action_status = 'C'
598 AND last_day(ppa.date_earned) = p_process_date
599 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
600 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
601 AND ppos.actual_termination_date = p_term_date;
602
603
604
605 --
606 -- Reduced the following cursor's cost from 27648 to 36.
607 --Cursor to get the Run results for the Element 'Loan Recovery'
608 CURSOR c_loan_recovery(p_employee_number VARCHAR2
609 ,p_bus_grp_id NUMBER
610 ,p_asg_action_id NUMBER)
611 IS
612 SELECT piv.name description
613 ,INITCAP(peev.screen_entry_value) loan_type
614 ,prrv.result_value amount
615 FROM per_assignments_f paf
616 ,per_people_f ppf
617 ,pay_element_types_f pet
618 ,pay_input_values_f piv
619 ,pay_element_entries_f pee
620 ,pay_element_entry_values_f peev
621 ,pay_element_classifications pec
622 ,pay_element_links_f pel
623 ,pay_assignment_actions paa
624 ,pay_payroll_actions ppa
625 ,pay_run_results prr
626 ,pay_run_result_values prrv
627 ,per_periods_of_service ppos
628 WHERE paf.business_group_id = p_bus_grp_id
629 AND ppf.employee_number = p_employee_number
630 AND ppf.person_id = paf.person_id
631 AND paf.period_of_service_id = ppos.period_of_service_id
632 AND pec.classification_name = 'Termination Payments'
633 AND pec.legislation_code = 'IN'
634 AND pec.classification_id = pet.classification_id
635 AND pee.assignment_id = paf.assignment_id
636 AND pee.element_type_id = pet.element_type_id
637 AND pet.element_name = 'Loan Recovery'
638 AND pel.element_link_id = pee.element_link_id
639 AND pel.business_group_id = paf.business_group_id
640 AND (pel.payroll_id = paf.payroll_id OR pel.link_to_all_payrolls_flag IS NOT NULL)
641 AND piv.element_type_id = pet.element_type_id
642 AND ((piv.name = 'Pay Value' ) --AND fnd_number.canonical_to_number(prrv.result_value) < 0)
643 OR piv.name = 'Loan Type')
644 AND paa.source_action_id = p_asg_action_id
645 AND prr.assignment_action_id = paa.assignment_action_id
646 AND ppa.payroll_action_id = paa.payroll_action_id
647 AND prr.element_entry_id = pee.element_entry_id
648 AND prr.element_type_id = pet.element_type_id
649 AND prr.status in ('P', 'PA')
650 AND prr.run_result_id = prrv.run_result_id
651 AND prrv.input_value_id = piv.input_value_id
652 AND peev.input_value_id =piv.input_value_id
653 AND peev.element_entry_id = pee.element_entry_id
654 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
655 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
656 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
657 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
658 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
659 AND ppa.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
660 ORDER BY pee.element_entry_id
661 ,piv.name;
662 --
663 -- Reduced the following cursor's cost from 27648 to 36.
664 --Cursor to get the Run results for the Element 'Leave Encashment Information'
665 CURSOR c_leave_encashment(p_employee_number VARCHAR2
666 ,p_bus_grp_id NUMBER
667 ,p_asg_action_id NUMBER)
668 IS
669 SELECT piv.name description
670 ,prrv.result_value amount
671 FROM per_assignments_f paf
672 ,per_people_f ppf
673 ,pay_element_types_f pet
674 ,pay_input_values_f piv
675 ,pay_element_entries_f pee
676 ,pay_element_classifications pec
677 ,pay_element_links_f pel
678 ,pay_assignment_actions paa
679 ,pay_payroll_actions ppa
680 ,pay_run_results prr
681 ,pay_run_result_values prrv
682 ,per_periods_of_service ppos
683 WHERE paf.business_group_id = p_bus_grp_id
684 AND ppf.employee_number = p_employee_number
685 AND ppf.person_id = paf.person_id
686 AND paf.period_of_service_id = ppos.period_of_service_id
687 AND pec.classification_name = 'Information'
688 AND pec.legislation_code = 'IN'
689 AND pec.classification_id = pet.classification_id
690 AND pee.assignment_id = paf.assignment_id
691 AND pee.element_type_id = pet.element_type_id
692 AND pet.element_name = 'Leave Encashment Information'
693 AND pel.element_link_id = pee.element_link_id
694 AND pel.business_group_id = paf.business_group_id
695 AND (pel.payroll_id = paf.payroll_id OR pel.link_to_all_payrolls_flag IS NOT NULL)
696 AND piv.element_type_id = pet.element_type_id
697 AND piv.name IN ('Leave Type'
698 , 'Leave Balance Days'
699 , 'Leave Adjusted Days'
700 , 'Encashment Amount')
701 AND paa.source_action_id = p_asg_action_id
702 AND prr.assignment_action_id = paa.assignment_action_id
703 AND ppa.payroll_action_id = paa.payroll_action_id
704 AND prr.element_entry_id = pee.element_entry_id
705 AND prr.element_type_id = pet.element_type_id
706 AND prr.status IN ('P', 'PA')
707 AND prr.run_result_id = prrv.run_result_id
708 AND prrv.input_value_id = piv.input_value_id
709 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
710 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
711 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
712 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
713 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
714 AND ppa.effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
715 ORDER BY pee.element_entry_id,piv.name DESC;
716 --
717 --
718 --Cursor to get the Run results for the Elements 'Gratuity Information'
719 -- 'Gratuity Payment'
720 --
721 CURSOR c_gratuity_payment(p_employee_number VARCHAR2
722 ,p_bus_grp_id NUMBER
723 ,p_max_asg_id NUMBER)
724 IS
725 SELECT prrv.result_value amount
726 ,piv.name description
727 FROM per_assignments_f paf
728 ,per_people_f ppf
729 ,pay_element_types_f pet
730 ,pay_input_values_f piv
731 ,pay_assignment_actions paa
732 ,pay_run_results prr
733 ,pay_run_result_values prrv
734 ,per_periods_of_service ppos
735 ,pay_element_classifications pec
736 WHERE paf.business_group_id = p_bus_grp_id
737 AND ppf.employee_number = p_employee_number
738 AND ppf.person_id = paf.person_id
739 AND paf.period_of_service_id = ppos.period_of_service_id
740 AND ppf.person_id = ppos.person_id
741 AND pet.element_name IN ('Gratuity Information'
742 , 'Gratuity Payment')
743 AND pec.classification_name IN ('Information',
744 'Termination Payments')
745 AND (pet.business_group_id = paf.business_group_id or pet.legislation_code = 'IN')
746 AND pec.classification_id = pet.classification_id
747 AND pec.legislation_code = 'IN'
748 AND piv.element_type_id = pet.element_type_id
749 AND piv.name IN ( 'Pay Value'
750 ,'Base Salary Used'
751 ,'Completed Service Years'
752 ,'Forfeiture Amount'
753 ,'Forfeiture Reason'
754 ,'Calculated Amount')
755 AND paa.source_action_id = p_max_asg_id
756 AND prr.assignment_action_id = paa.assignment_action_id
757 AND paa.assignment_id = paf.assignment_id
758 AND prr.element_type_id = pet.element_type_id
759 AND prr.status IN ('P', 'PA')
760 AND prr.run_result_id = prrv.run_result_id
761 AND prrv.input_value_id = piv.input_value_id
762 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
763 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
764 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
765 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
766 ORDER BY piv.name;
767 --
768 --
769 -- Cursor to get the Run results for the following Elements
770 -- for the Element Classifications Termination Payments
771 -- 1. Commuted Pension
772 -- 2. Gratuity Payment
773 -- 3. PF Settlement
774 -- 4. Leave Encashment
775 --
776 CURSOR c_employee_dues(p_employee_number VARCHAR2
777 ,p_bus_grp_id NUMBER
778 ,p_max_asg_id NUMBER)
779 IS
780 SELECT piv.name description
781 ,prrv.result_value amount
782 ,nvl(pet.reporting_name, pet.element_name) Element
783 FROM per_assignments_f paf
784 ,per_people_f ppf
785 ,pay_element_types_f pet
786 ,pay_input_values_f piv
787 ,pay_assignment_actions paa
788 ,pay_run_results prr
789 ,pay_run_result_values prrv
790 ,per_periods_of_service ppos
791 ,pay_element_classifications pec
792 WHERE paf.business_group_id = p_bus_grp_id
793 AND ppf.employee_number = p_employee_number
794 AND ppf.person_id = paf.person_id
795 AND paf.period_of_service_id = ppos.period_of_service_id
796 AND ppf.person_id = ppos.person_id
797 AND pec.classification_name = 'Termination Payments'
798 AND pet.legislation_code = 'IN'
799 AND pec.classification_id = pet.classification_id
800 AND pec.legislation_code = 'IN'
801 AND ((piv.name = 'Pay Value') --AND fnd_number.canonical_to_number(prrv.result_value) > 0
802 OR piv.name IN ('Taxable Amount'
803 , 'Non Taxable Amount'))
804 AND piv.element_type_id = pet.element_type_id
805 AND paa.source_action_id = p_max_asg_id
806 AND prr.assignment_action_id = paa.assignment_action_id
807 AND pet.element_name IN ('Commuted Pension'
808 ,'Gratuity Payment'
809 ,'PF Settlement'
810 ,'Leave Encashment')
811 AND prr.element_type_id = pet.element_type_id
812 AND prr.status IN ('P', 'PA')
813 AND paf.assignment_id = paa.assignment_id
814 AND prr.run_result_id = prrv.run_result_id
815 AND prrv.input_value_id = piv.input_value_id
816 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
817 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
818 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
819 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
820 ORDER BY pet.element_name
821 ,piv.name;
822
823 -- Cursor to get the Run results for the following Elements
824 -- for the Element Classifications Termination Payments
825 -- 1. Retrenchment Compensation Information
826 -- 2. Voluntary Retirement Information
827 --
828 CURSOR c_employee_term_dues(p_employee_number VARCHAR2
829 ,p_bus_grp_id NUMBER
830 ,p_max_asg_id NUMBER)
831 IS
832 SELECT piv.name description
833 ,prrv.result_value amount
834 ,nvl(pet.reporting_name, pet.element_name) Element
835 FROM per_assignments_f paf
836 ,per_people_f ppf
837 ,pay_element_types_f pet
838 ,pay_input_values_f piv
839 ,pay_assignment_actions paa
840 ,pay_run_results prr
841 ,pay_run_result_values prrv
842 ,per_periods_of_service ppos
843 ,pay_element_classifications pec
844 WHERE paf.business_group_id = p_bus_grp_id
845 AND ppf.employee_number = p_employee_number
846 AND ppf.person_id = paf.person_id
847 AND paf.period_of_service_id = ppos.period_of_service_id
848 AND ppf.person_id = ppos.person_id
849 AND pec.classification_name IN ('Information','Termination Payments')
850 AND pet.legislation_code = 'IN'
851 AND pec.classification_id = pet.classification_id
852 AND pec.legislation_code = 'IN'
853 AND piv.name IN ('Taxable Amount'
854 , 'Non Taxable Amount')
855 AND piv.element_type_id = pet.element_type_id
856 AND paa.source_action_id = p_max_asg_id
857 AND prr.assignment_action_id = paa.assignment_action_id
858 AND pet.element_name IN ('Retrenchment Compensation Information'
859 ,'Voluntary Retirement Information'
860 ,'Other Termination Payments')
861 AND prr.element_type_id = pet.element_type_id
862 AND prr.status IN ('P', 'PA')
863 AND paf.assignment_id = paa.assignment_id
864 AND prr.run_result_id = prrv.run_result_id
865 AND prrv.input_value_id = piv.input_value_id
866 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
867 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
868 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
869 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
870 ORDER BY prr.element_entry_id
871 ,piv.name;
872 --
873 -- Cursor to get the Run results
874 -- for the Element Classifications Termination Payments
875 -- not taken care of in the above cursor.
876 --
877 cursor c_employee_dues_user_elements(p_employee_number VARCHAR2
878 ,p_bus_grp_id NUMBER
879 ,p_max_asg_id NUMBER)
880 IS
881 SELECT sum(prrv.result_value) amount
882 ,nvl(pet.reporting_name, pet.element_name) description
883 ,pet.element_name elename
884 FROM per_assignments_f paf
885 ,per_people_f ppf
886 ,pay_element_types_f pet
887 ,pay_input_values_f piv
888 ,pay_assignment_actions paa
889 ,pay_run_results prr
890 ,pay_run_result_values prrv
891 ,per_periods_of_service ppos
892 ,pay_element_classifications pec
893 WHERE ppf.business_group_id = p_bus_grp_id
894 AND ppf.employee_number = p_employee_number
895 AND ppf.person_id = paf.person_id
896 AND paf.period_of_service_id = ppos.period_of_service_id
897 AND ppf.person_id = ppos.person_id
898 AND ppos.business_group_id = ppf.business_group_id
899 AND pec.classification_name = 'Termination Payments'
900 AND (pet.business_group_id = ppf.business_group_id OR pet.legislation_code = 'IN')
901 AND pec.classification_id = pet.classification_id
902 AND pec.legislation_code = 'IN'
903 AND piv.name = 'Pay Value'
904 -- AND fnd_number.canonical_to_number(prrv.result_value) > 0
905 AND piv.element_type_id = pet.element_type_id
906 AND paa.source_action_id = p_max_asg_id
907 AND paa.assignment_id = paf.assignment_id
908 AND prr.assignment_action_id = paa.assignment_action_id
909 AND pet.element_name NOT IN ('Commuted Pension'
910 ,'Gratuity Payment'
911 ,'PF Settlement'
912 ,'Leave Encashment'
913 ,'Other Termination Payments')
914 AND pet.element_name NOT IN (select petf.element_name
915 from pay_balance_feeds_f pbff
916 ,pay_balance_types pbt
917 ,pay_input_values_f pivf
918 ,pay_element_types_f petf
919 where pbff.balance_type_id = pbt.balance_type_id
920 and pbt.balance_name in ('Retrenchment Compensation'
921 , 'Voluntary Retirement Benefits')
922 and pbff.input_value_id = pivf.input_value_id
923 and pivf.name = 'Pay Value'
924 and petf.element_type_id = pivf.element_type_id
925 and pbt.legislation_code = 'IN')
926 AND prr.element_type_id = pet.element_type_id
927 AND prr.status IN ('P', 'PA')
928 AND prr.run_result_id = prrv.run_result_id
929 AND prrv.input_value_id = piv.input_value_id
930 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
931 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
932 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
933 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date
934 GROUP BY nvl(pet.reporting_name, pet.element_name),pet.element_name ;
935 --
936 --
937 -- Cursor to get the gross deductions. Adding up the negative run values for the
938 -- Element Classifications 1. Earnings
939 -- 2. Termination Payments
940 -- 3. Tax Deductions
941 CURSOR c_gross_deductions(p_employee_number VARCHAR2
942 ,p_bus_grp_id NUMBER
943 ,p_max_asg_id NUMBER)
944 IS
945 SELECT /*+ ORDERED */
946 SUM(ABS(fnd_number.canonical_to_number(prrv.result_value))) amount
947 FROM per_assignments_f paf
948 ,per_people_f ppf
949 ,pay_element_types_f pet
950 ,pay_input_values_f piv
951 ,pay_assignment_actions paa
952 -- ,pay_element_entries_f peef-- Added as a part of bug fix 4774108
953 ,pay_run_results prr
954 ,pay_run_result_values prrv
955 ,per_periods_of_service ppos
956 ,pay_element_classifications pec
957 WHERE paf.business_group_id = p_bus_grp_id
958 AND ppf.employee_number = p_employee_number
959 AND ppf.person_id = paf.person_id
960 AND paf.period_of_service_id = ppos.period_of_service_id
961 AND ppf.person_id = ppos.person_id
962 AND ((pec.classification_name IN ( 'Termination Payments'
963 , 'Earnings') AND fnd_number.canonical_to_number(prrv.result_value) < 0)
964 OR (pec.classification_name IN ( 'Tax Deductions') AND fnd_number.canonical_to_number(prrv.result_value) > 0))
965 AND (pet.business_group_id = paf.business_group_id OR pet.legislation_code = 'IN')
966 AND pec.classification_id = pet.classification_id
967 AND pec.legislation_code = 'IN'
968 AND piv.name = 'Pay Value'
969 AND piv.element_type_id = pet.element_type_id
970 AND paa.source_action_id = p_max_asg_id
971 AND prr.assignment_action_id = paa.assignment_action_id -- Modified as per bug 4774108
972 AND prr.element_type_id = pet.element_type_id
973 AND prr.status IN ('P', 'PA')
974 AND paf.assignment_id = paa.assignment_id
975 AND prr.run_result_id = prrv.run_result_id
976 AND prrv.input_value_id = piv.input_value_id
977 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
978 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
979 AND ppos.actual_termination_date BETWEEN pet.effective_start_date AND pet.effective_end_date
980 AND ppos.actual_termination_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
981 --
982 --
983 -- Cursor to get the _ASG_RUN balance values for the following balances
984 -- 1. Net Pay
985 CURSOR c_balance_values(p_employee_number VARCHAR2
986 ,p_bus_grp_id NUMBER
987 ,p_max_asg_id NUMBER)
988 IS
989 SELECT to_number(pay_balance_pkg.get_value(pdb.defined_balance_id
990 , paa.assignment_action_id)) amount
991 , pbt.balance_name description
992 FROM per_assignments_f paf
993 ,per_people_f ppf
994 ,pay_assignment_actions paa
995 ,per_periods_of_service ppos
996 ,pay_balance_types pbt
997 ,pay_balance_dimensions pbd
998 ,pay_defined_balances pdb
999 WHERE paf.business_group_id = p_bus_grp_id
1000 AND ppf.employee_number = p_employee_number
1001 AND ppf.person_id = paf.person_id
1002 AND paf.period_of_service_id = ppos.period_of_service_id
1003 AND ppf.person_id = ppos.person_id
1004 AND paa.source_action_id = p_max_asg_id
1005 AND paf.assignment_id = paa.assignment_id -- Modified for 4774108
1006 AND pbt.balance_name IN ('Net Pay')
1007 AND pbt.legislation_code = 'IN'
1008 AND pbd.dimension_name = '_ASG_RUN'
1009 AND pbd.legislation_code = 'IN'
1010 AND pdb.balance_type_id = pbt.balance_type_id
1011 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1012 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1013 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
1014 --
1015 --
1016 --Cursor to get the payment details
1017 CURSOR c_payment_details(p_employee_number VARCHAR2
1018 ,p_bus_grp_id NUMBER
1019 ,p_max_payment_asg_id NUMBER)
1020 IS
1021 SELECT hr_general.decode_lookup('IN_BANK',pea.segment3) bank
1022 ,hr_general.decode_lookup('IN_BANK_BRANCH',pea.segment4) branch
1023 ,pea.segment1 account_number
1024 ,ppt.payment_type_name payment_type
1025 ,ppp.value amount
1026 ,ppa.effective_date payment_date
1027 FROM pay_external_accounts pea
1028 ,pay_pre_payments ppp
1029 ,pay_org_payment_methods_f pop
1030 ,pay_personal_payment_methods_f ppm
1031 ,pay_payment_types ppt
1032 ,pay_action_interlocks pci
1033 ,pay_payroll_actions ppa
1034 ,pay_assignment_actions paa
1035 ,per_people_f ppf
1036 ,per_assignments_f paf
1037 ,per_periods_of_service ppos
1038 WHERE ppp.assignment_action_id = pci.locking_action_id
1039 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
1040 AND ppp.org_payment_method_id = pop.org_payment_method_id
1041 AND ppm.external_account_id = pea.external_account_id (+)
1042 AND pop.payment_type_id = ppt.payment_type_id
1043 AND pci.locked_action_id = paa.assignment_action_id
1044 AND ppf.business_group_id = p_bus_grp_id
1045 AND ppf.employee_number = p_employee_number
1046 AND ppf.person_id = paf.person_id
1047 AND paf.period_of_service_id = ppos.period_of_service_id
1048 AND ppos.business_group_id = ppf.business_group_id
1049 AND ppf.person_id = ppos.person_id
1050 AND paf.assignment_id = paa.assignment_id
1051 AND paa.assignment_action_id = p_max_payment_asg_id
1052 AND paa.payroll_Action_id = ppa.payroll_action_id
1053 AND ppa.effective_date BETWEEN pop.effective_start_date AND pop.effective_end_date
1054 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1055 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1056 AND ppa.effective_date BETWEEN nvl(ppm.effective_start_date,ppa.effective_date)
1057 AND nvl(ppm.effective_end_date,ppa.effective_date);
1058
1059 --
1060 --
1061 -- Cursor to get the _ASG_PMTH balance values for the following balance
1062 -- 1. Gratuity Eligible Salary
1063 --
1064 CURSOR c_gratuity_elig_sal(p_employee_number VARCHAR2
1065 ,p_bus_grp_id NUMBER
1066 ,p_max_asg_id NUMBER)
1067 IS
1068 SELECT to_number(pay_balance_pkg.get_value(pdb.defined_balance_id
1069 , paa.assignment_action_id)) amount
1070 , pbt.balance_name description
1071 FROM per_assignments_f paf
1072 ,per_people_f ppf
1073 ,pay_assignment_actions paa
1074 ,per_periods_of_service ppos
1075 ,pay_balance_types pbt
1076 ,pay_balance_dimensions pbd
1077 ,pay_defined_balances pdb
1078 WHERE paf.business_group_id = p_bus_grp_id
1079 AND ppf.employee_number = p_employee_number
1080 AND ppf.person_id = paf.person_id
1081 AND paf.period_of_service_id = ppos.period_of_service_id
1082 AND ppf.person_id = ppos.person_id
1083 AND paa.source_action_id = p_max_asg_id
1084 AND paf.assignment_id = paa.assignment_id -- Modified for 4774108
1085 AND pbt.balance_name IN ('Gratuity Eligible Salary')
1086 AND pbt.legislation_code = 'IN'
1087 AND pbd.dimension_name = '_ASG_PTD'
1088 AND pbd.legislation_code = 'IN'
1089 AND pdb.balance_type_id = pbt.balance_type_id
1090 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1091 AND ppos.actual_termination_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1092 AND ppos.actual_termination_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
1093
1094 l_asg_max_run_action_id pay_assignment_actions.assignment_action_id%TYPE;-- Bug 4774108 addition
1095 --
1096 --
1097 --
1098 l_final_report_info hr_organization_information.org_information6%TYPE; -- Bug 6772976 addition
1099 l_process_date per_periods_of_service.last_standard_process_date%TYPE;
1100 l_max_payment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1101 --
1102 l_procedure VARCHAR2(250);
1103 l_message VARCHAR2(250);
1104 BEGIN
1105
1106 g_debug := hr_utility.debug_enabled;
1107 l_procedure := g_package ||'create_xml';
1108 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1109 l_term_date := fnd_date.displayDT_to_date(p_term_date);
1110 IF (g_debug)
1111 THEN
1112 pay_in_utils.trace('**************************************************','********************');
1113 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1114 pay_in_utils.trace('p_employee_number',p_employee_number);
1115 pay_in_utils.trace('l_term_date',l_term_date);
1116 pay_in_utils.trace('p_bus_grp_id',p_bus_grp_id);
1117 END IF;
1118 --
1119 gXMLTable.DELETE;
1120 l_count := 1;
1121 --
1122 fnd_file.put_line(fnd_file.log,'Creating the XML...');
1123 dbms_lob.createtemporary(p_xml_data,FALSE,DBMS_LOB.CALL);
1124 dbms_lob.open(p_xml_data,dbms_lob.lob_readwrite);
1125 --
1126 l_tag := '<?xml version="1.0"?>';
1127 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1128 l_tag := '<TerminationDetails>';
1129 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1130 fnd_file.put_line(fnd_file.log,'Started...');
1131 --
1132 --
1133 fnd_file.put_line(fnd_file.log,'Creating XML for Employee Personal Details.');
1134
1135 --Bug 6772976 starts
1136 OPEN c_final_report_info(p_bus_grp_id);
1137 FETCH c_final_report_info into l_final_report_info;
1138 CLOSE c_final_report_info;
1139
1140 OPEN c_last_std_process_date(p_employee_number,p_bus_grp_id,l_term_date);
1141 FETCH c_last_std_process_date into l_process_date;
1142 CLOSE c_last_std_process_date;
1143
1144 --Condition to get the assignment Id depending on Last Standard process Date or as of the Latest
1145 --Payroll run
1146 IF l_final_report_info ='LAST_STANDARD_DATE' AND l_process_date IS NOT NULL
1147 THEN
1148
1149 OPEN c_lsp_max_asg_act_id(p_employee_number,p_bus_grp_id,l_term_date,l_process_date);
1150 FETCH c_lsp_max_asg_act_id into l_asg_max_run_action_id;
1151 CLOSE c_lsp_max_asg_act_id;
1152
1153 --Max Assignment Id for Payment Details
1154 OPEN c_lsp_payment_max_asg_act_id(p_employee_number,p_bus_grp_id,l_term_date,l_process_date);
1155 FETCH c_lsp_payment_max_asg_act_id into l_max_payment_action_id;
1156 CLOSE c_lsp_payment_max_asg_act_id;
1157 ELSE
1158 -- Bug 4774108 addition starts
1159 --MAx Assignment Id depending on Last Payroll run
1160 OPEN c_max_asg_act_id(p_employee_number,p_bus_grp_id,l_term_date);
1161 FETCH c_max_asg_act_id INTO l_asg_max_run_action_id;
1162 CLOSE c_max_asg_act_id;-- Bug 4774108 addition ends
1163
1164 --Max Assignment Id for Payment Deatils depending on Last Payroll Run
1165 OPEN c_payment_max_asg_act_id(p_employee_number,p_bus_grp_id,l_term_date);
1166 FETCH c_payment_max_asg_act_id into l_max_payment_action_id;
1167 CLOSE c_payment_max_asg_act_id;
1168
1169 END IF;
1170
1171 IF (g_debug)
1172 THEN
1173 pay_in_utils.trace('l_asg_max_run_action_id',l_asg_max_run_action_id);
1174 END IF;
1175
1176 FOR c_rec in c_employee_details(p_employee_number
1177 ,p_bus_grp_id
1178 ,l_term_date)
1179 LOOP
1180 --Employee Name
1181 gXMLTable(l_count).Name := 'c_employee_name';
1182 gXMLTable(l_count).Value := (c_rec.name);
1183 l_count := l_count + 1;
1184 --Employee Number
1185 gXMLTable(l_count).Name := 'c_employee_number';
1186 gXMLTable(l_count).Value := (c_rec.employee_number);
1187 l_count := l_count + 1;
1188 --Date of Birth
1189 gXMLTable(l_count).Name := 'c_date_of_birth';
1190 gXMLTable(l_count).Value := (c_rec.dob);
1191 l_count := l_count + 1;
1192 --Age
1193 gXMLTable(l_count).Name := 'c_age';
1194 gXMLTable(l_count).Value := (c_rec.age);
1195 l_count := l_count + 1;
1196 --Date of Joining
1197 gXMLTable(l_count).Name := 'c_date_of_joining';
1198 gXMLTable(l_count).Value := (c_rec.doj);
1199 l_count := l_count + 1;
1200 --Date of Leaving
1201 gXMLTable(l_count).Name := 'c_date_of_leaving';
1202 gXMLTable(l_count).Value := (c_rec.dol);
1203 l_count := l_count + 1;
1204 --Length of Service
1205 gXMLTable(l_count).Name := 'c_length_of_service';
1206 gXMLTable(l_count).Value := (c_rec.los);
1207 l_count := l_count + 1;
1208 --Reason for Leaving
1209 gXMLTable(l_count).Name := 'c_reason_for_leaving';
1210 gXMLTable(l_count).Value := (c_rec.leaving_reason);
1211 l_count := l_count + 1;
1212 --Department
1213 gXMLTable(l_count).Name := 'c_department';
1214 gXMLTable(l_count).Value := (c_rec.department);
1215 l_count := l_count + 1;
1216 --Organization
1217 gXMLTable(l_count).Name := 'c_organization';
1218 gXMLTable(l_count).Value := (c_rec.organization);
1219 l_count := l_count + 1;
1220 --Location
1221 gXMLTable(l_count).Name := 'c_location';
1222 gXMLTable(l_count).Value := (c_rec.location);
1223
1224 --
1225 --Payment Date will be populated during the payment details generation.
1226 --
1227 END LOOP;
1228 --
1229 multiColumnar('Employee_Details'
1230 ,gXMLTable
1231 ,l_count);
1232 FOR c_rec_designation in c_employee_designation(p_employee_number
1233 ,p_bus_grp_id
1234 ,l_term_date)
1235 LOOP
1236 -- For getting the Designation
1237 l_tag := getTag('c_designation', c_rec_designation.designation);
1238 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1239 END LOOP;
1240 fnd_file.put_line(fnd_file.log,'Created Employee Personal Details.');
1241 --
1242 l_total_earnings := 0;
1243 l_total_deductions := 0;
1244 l_total_employer_charges := 0;
1245 l_total_perquisites := 0;
1246 l_total_other_deductions := 0;
1247 l_total_advance := 0; -- Added for the bug fix 6660147
1248 l_total_fringe_benefit := 0; -- Added for the bug fix 6660147
1249 --
1250 fnd_file.put_line(fnd_file.log,'Creating XML for Regular Pay and Other Deductions.');
1251 --
1252 -- Following steps are carried out
1253 -- 1. The Classification of the element is checked
1254 -- 2. As per the classification, relevant colums are populated
1255 -- 3. The Amounts for a single classification are summed up
1256 -- 4. If the Summed Amount turns out to be zero ( meaning that there are no
1257 -- elements of that classification) No Data Exists. is printed.
1258 --
1259 for c_rec_run_results in c_run_results(p_employee_number
1260 ,p_bus_grp_id
1261 ,l_asg_max_run_action_id)
1262 --
1263 LOOP
1264 --
1265 l_count := 1;
1266
1267 --Description
1268 gXMLTable(l_count).Name := 'c_description';
1269 gXMLTable(l_count).Value := (c_rec_run_results.description);
1270 l_count := l_count + 1 ;
1271
1272 --Amount
1273 gXMLTable(l_count).Name := 'c_amount';
1274 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1275 ,fnd_number.canonical_to_number(c_rec_run_results.amount)); /* bug no: 14753072 */
1276 l_count := l_count + 1 ;
1277
1278 --Classification
1279 gXMLTable(l_count).Name := 'c_classification';
1280 gXMLTable(l_count).Value := (c_rec_run_results.classification);
1281 l_count := l_count + 1 ;
1282
1283 --Elementname
1284 gXMLTable(l_count).Name := 'c_elementname';
1285 gXMLTable(l_count).Value := (c_rec_run_results.elename);
1286
1287 --
1288 IF c_rec_run_results.classification = 'Earnings'
1289 or c_rec_run_results.classification = 'Allowances' THEN
1290 multiColumnar('t_earnings'
1291 ,gXMLTable
1292 ,l_count);
1293 l_total_earnings := l_total_earnings + fnd_number.canonical_to_number(c_rec_run_results.amount);
1294 --
1295 ELSIF c_rec_run_results.classification = 'Deductions'
1296 or c_rec_run_results.classification = 'Involuntary Deductions'
1297 or c_rec_run_results.classification = 'Voluntary Deductions'
1298 or c_rec_run_results.classification = 'Pre Tax Deductions'
1299 or c_rec_run_results.classification = 'Tax Deductions' THEN
1300 multiColumnar('t_deductions'
1301 ,gXMLTable
1302 ,l_count);
1303 l_total_deductions := l_total_deductions + fnd_number.canonical_to_number(c_rec_run_results.amount);
1304 --
1305 ELSIF c_rec_run_results.classification = 'Employer Charges' THEN
1306 multiColumnar('t_er_charges'
1307 ,gXMLTable
1308 ,l_count);
1309 l_total_employer_charges := l_total_employer_charges + fnd_number.canonical_to_number(c_rec_run_results.amount);
1310 --
1311 ELSIF c_rec_run_results.classification = 'Perquisites' THEN
1312 multiColumnar('t_perquisites'
1313 ,gXMLTable
1314 ,l_count);
1315 l_total_perquisites := l_total_perquisites + fnd_number.canonical_to_number(c_rec_run_results.amount);
1316 --
1317 ELSIF c_rec_run_results.classification = 'Termination Payments'
1318 AND fnd_number.canonical_to_number(c_rec_run_results.amount) < 0 THEN
1319 multiColumnar('t_other_deductions'
1320 ,gXMLTable
1321 ,l_count);
1322 l_total_other_deductions := l_total_other_deductions
1323 + (fnd_number.canonical_to_number((c_rec_run_results.amount)));
1324 -- /*Added code for bug fix 6660147*/
1325 ELSIF c_rec_run_results.classification = 'Advances' THEN
1326 multiColumnar('t_advances'
1327 ,gXMLTable
1328 ,l_count);
1329 l_total_advance := l_total_advance
1330 + abs(fnd_number.canonical_to_number((c_rec_run_results.amount)));
1331 --
1332 ELSIF c_rec_run_results.classification = 'Fringe Benefits' THEN
1333 multiColumnar('t_fringe_benefits'
1334 ,gXMLTable
1335 ,l_count);
1336 l_total_fringe_benefit := l_total_fringe_benefit
1337 + abs(fnd_number.canonical_to_number((c_rec_run_results.amount)));
1338 --
1339 END IF;
1340 --
1341 END LOOP;
1342 --
1343 -- If there is no data for any Element Classification then 'No Data Exists.' is printed
1344 --
1345 IF l_total_earnings <> 0 THEN
1346 l_tag := getTag('c_total_earnings', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1347 ,l_total_earnings));
1348 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1349 ELSE
1350 gXMLTable(1).Name := 'No Data Exists.';
1351 gXMLTable(1).Value := ' ';
1352 twoColumnar('t_earnings'
1353 ,gXMLTable
1354 ,1);
1355 END IF;
1356
1357 IF l_total_deductions <> 0 THEN
1358 l_tag := getTag('c_total_deductions', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1359 ,l_total_deductions));
1360 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1361 ELSE
1362 gXMLTable(1).Name := 'No Data Exists.';
1363 gXMLTable(1).Value := ' ';
1364 twoColumnar('t_deductions'
1365 ,gXMLTable
1366 ,1);
1367 END IF;
1368
1369 IF l_total_employer_charges <> 0 THEN
1370 l_tag := getTag('c_total_employer_charges', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1371 ,l_total_employer_charges));
1372 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1373 ELSE
1374 gXMLTable(1).Name := 'No Data Exists.';
1375 gXMLTable(1).Value := ' ';
1376 twoColumnar('t_er_charges'
1377 ,gXMLTable
1378 ,1);
1379 END IF;
1380
1381 IF l_total_perquisites <> 0 THEN
1382 l_tag := getTag('c_total_perquisites', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1383 ,l_total_perquisites));
1384 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1385 ELSE
1386 gXMLTable(1).Name := 'No Data Exists.';
1387 gXMLTable(1).Value := ' ';
1388 twoColumnar('t_perquisites'
1389 ,gXMLTable
1390 ,1);
1391 END IF;
1392
1393 IF l_total_other_deductions <> 0 THEN
1394 l_tag := getTag('c_total_other_deductions', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1395 ,(fnd_number.canonical_to_number(l_total_other_deductions))));
1396 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1397 ELSE
1398 gXMLTable(1).Name := 'No Data Exists.';
1399 gXMLTable(1).Value := ' ';
1400 twoColumnar('t_other_deductions'
1401 ,gXMLTable
1402 ,1);
1403 END IF;
1404 -- /*Added code for bug fix 6660147*/
1405 --Bugfix 66660147 start
1406 IF l_total_advance <> 0 THEN
1407 l_tag := getTag('c_total_advance', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1408 ,abs(fnd_number.canonical_to_number(l_total_advance))));
1409 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1410 ELSE
1411 gXMLTable(1).Name := 'No Data Exists.';
1412 gXMLTable(1).Value := ' ';
1413 twoColumnar('t_advances'
1414 ,gXMLTable
1415 ,1);
1416 END IF;
1417
1418 IF l_total_fringe_benefit <> 0 THEN
1419 l_tag := getTag('c_total_fringe_benefit', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1420 ,abs(fnd_number.canonical_to_number(l_total_fringe_benefit))));
1421 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1422 ELSE
1423 gXMLTable(1).Name := 'No Data Exists.';
1424 gXMLTable(1).Value := ' ';
1425 twoColumnar('t_fringe_benefits'
1426 ,gXMLTable
1427 ,1);
1428 END IF;
1429 --Bugfix 66660147 end
1430 --
1431 --
1432 fnd_file.put_line(fnd_file.log,'Created Regular Pay and Other Deductions.');
1433 --
1434 --
1435 fnd_file.put_line(fnd_file.log,'Creating XML for Loan Recovery.');
1436 --
1437 l_total_loan_recovery := 0;
1438 l_count := 1;
1439 --
1440 --
1441 -- Following steps are carried out
1442 -- 1. The Element Loan Type is checked for the input values Pay Value and Loan Type
1443 -- 2. The Amounts for a single classification are summed up
1444 -- 3. If the Summed Amount turns out to be zero ( meaning that there are no
1445 -- loans to be recovered) No Data Exists. is printed.
1446 --
1447 for c_rec_loan in c_loan_recovery(p_employee_number
1448 ,p_bus_grp_id
1449 ,l_asg_max_run_action_id)
1450 LOOP
1451 --
1452 l_count := 1;
1453 IF c_rec_loan.description = 'Loan Type' THEN
1454 --
1455 gXMLTable(l_count).Name := c_rec_loan.loan_type;
1456 --
1457 ELSIF c_rec_loan.description = 'Pay Value' AND fnd_number.canonical_to_number(c_rec_loan.amount) < 0 THEN
1458 --
1459 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1460 ,(fnd_number.canonical_to_number(c_rec_loan.amount)));
1461 l_total_loan_recovery := l_total_loan_recovery + (fnd_number.canonical_to_number(c_rec_loan.amount));
1462 l_count := l_count + 1;
1463 --
1464 END IF;
1465 --
1466 IF l_count = 2 THEN
1467 twoColumnar('t_loan_recovery'
1468 ,gXMLTable
1469 ,l_count - 1);
1470 -- l_total_loan_recovery := l_total_loan_recovery + gXMLTable(l_count-1).Value;
1471 END IF;
1472 --
1473 END LOOP;
1474 --
1475 -- If there is no data then 'No Data Exists.' is printed
1476 --
1477 IF l_total_loan_recovery <> 0 THEN
1478 l_tag := getTag('c_total_perquisites_loan_recovery', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1479 ,fnd_number.canonical_to_number(l_total_loan_recovery)));
1480 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1481 ELSE
1482 gXMLTable(1).Name := 'No Data Exists.';
1483 gXMLTable(1).Value := ' ';
1484 twoColumnar('t_loan_recovery'
1485 ,gXMLTable
1486 ,1);
1487 END IF;
1488 --
1489 fnd_file.put_line(fnd_file.log,'Created Loan Recovery.');
1490 --
1491 fnd_file.put_line(fnd_file.log,'Creating XML for Leave Encashment.');
1492 --
1493 l_count := 1;
1494 l_leave_encashed_flag := 0; /* variable to check if No data Found is to be printed */
1495 --
1496 -- Following steps are carried out
1497 -- 1. The Element Leave Encashment Information is checked for the input values
1498 -- Leave Type, Leave Balance Days, Leave Adjusted Days, Encashment Amount
1499 -- 2. If the cursor doesn't return any record then No Data Exists is printed.
1500 --
1501 for c_rec_leave_encashment in c_leave_encashment(p_employee_number
1502 ,p_bus_grp_id
1503 ,l_asg_max_run_action_id)
1504 LOOP
1505 --
1506 l_leave_encashed_flag := 1;
1507 IF c_rec_leave_encashment.description = 'Leave Type' THEN
1508 gXMLTable(l_count).Name := 'c_leave_type';
1509 gXMLTable(l_count).Value := hr_general.decode_lookup('ABSENCE_CATEGORY'
1510 ,(c_rec_leave_encashment.amount));
1511 --
1512 ELSIF c_rec_leave_encashment.description = 'Leave Balance Days' THEN
1513 gXMLTable(l_count).Name := 'c_balance_days';
1514 gXMLTable(l_count).Value := nvl((c_rec_leave_encashment.amount),'0');
1515 --
1516 ELSIF c_rec_leave_encashment.description = 'Leave Adjusted Days' THEN
1517 gXMLTable(l_count).Name := 'c_notice_period_adjusted';
1518 gXMLTable(l_count).Value := nvl((c_rec_leave_encashment.amount),'0');
1519 --
1520 ELSIF c_rec_leave_encashment.description = 'Encashment Amount' THEN
1521 gXMLTable(l_count).Name := 'c_amount';
1522 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1523 ,nvl(fnd_number.canonical_to_number(c_rec_leave_encashment.amount),0));
1524 END IF;
1525 l_count := l_count + 1;
1526 --
1527 IF l_count = 5 THEN
1528 gXMLTable(l_count).Name := 'c_remaining_leave_balance';
1529 gXMLTable(l_count).Value := to_char(fnd_number.canonical_to_number(gXMLTable(2).Value) - fnd_number.canonical_to_number(gXMLTable(3).Value)) ;
1530 multiColumnar('t_leave_encashment'
1531 ,gXMLTable
1532 ,l_count);
1533 l_count := 1;
1534 END IF;
1535 --
1536 END LOOP;
1537 --
1538 IF l_leave_encashed_flag = 0 THEN
1539 gXMLTable(1).Name := 'c_leave_type';
1540 gXMLTable(1).Value := 'No Data Exists.';
1541 multiColumnar('t_leave_encashment'
1542 ,gXMLTable
1543 ,1);
1544 END IF;
1545 --
1546 fnd_file.put_line(fnd_file.log,'Created Leave Encashment.');
1547 --
1548 fnd_file.put_line(fnd_file.log,'Creating XML for Gratuity Payment.');
1549 --
1550 l_count := 1;
1551 l_gratuity_paid_flag := 0; /* variable to check if No data Found is to be printed */
1552 l_grat_elig_sal := 0;
1553 --
1554 -- Following steps are carried out
1555 -- 1. The Elements Gratuity Information and Gratuity Payment are checked for the input values
1556 -- Base Salary, Completed Service Years, Forfeiture Amounts, Forfeiture Reason and
1557 -- Pay Value, Calculated Amount
1558 -- 2. If the cursor doesn't return any record then No Data Exists is printed.
1559 --
1560 for c_rec_gratuity_payment in c_gratuity_payment(p_employee_number
1561 ,p_bus_grp_id
1562 ,l_asg_max_run_action_id)
1563 LOOP
1564 --
1565 l_gratuity_paid_flag := 1;
1566 IF c_rec_gratuity_payment.description = 'Base Salary Used' THEN
1567 gXMLTable(l_count).Name := 'c_last_drawn_salary';
1568 IF c_rec_gratuity_payment.amount IS NULL THEN
1569 FOR c_rec_grat_elig_sal in c_gratuity_elig_sal(p_employee_number
1570 ,p_bus_grp_id
1571 ,l_asg_max_run_action_id)
1572 LOOP
1573 l_grat_elig_sal := c_rec_grat_elig_sal.amount;
1574 END LOOP;
1575 ELSE
1576 l_grat_elig_sal := fnd_number.canonical_to_number(c_rec_gratuity_payment.amount);
1577 END IF;
1578 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1579 ,(l_grat_elig_sal));
1580 --
1581 ELSIF c_rec_gratuity_payment.description = 'Completed Service Years' THEN
1582 gXMLTable(l_count).Name := 'c_completed_service_years';
1583 gXMLTable(l_count).Value := (c_rec_gratuity_payment.amount);
1584 --
1585 ELSIF c_rec_gratuity_payment.description = 'Forfeiture Amount' THEN
1586 gXMLTable(l_count).Name := 'c_forfeiture_amount';
1587 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1588 ,nvl(fnd_number.canonical_to_number(c_rec_gratuity_payment.amount),0));
1589 --
1590 ELSIF c_rec_gratuity_payment.description = 'Forfeiture Reason' THEN
1591 gXMLTable(l_count).Name := 'c_forfeiture_reason';
1592 gXMLTable(l_count).Value := hr_general.decode_lookup('IN_GRATUITY_FORFEITURE_REASON',c_rec_gratuity_payment.amount);
1593 --
1594 ELSIF c_rec_gratuity_payment.description = 'Pay Value' THEN
1595 gXMLTable(l_count).Name := 'c_gratuity_amount';
1596 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1597 ,nvl(fnd_number.canonical_to_number(c_rec_gratuity_payment.amount),0));
1598 --
1599 ELSIF c_rec_gratuity_payment.description = 'Calculated Amount' THEN
1600 gXMLTable(l_count).Name := 'c_calculated_amount';
1601 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1602 ,nvl(fnd_number.canonical_to_number(c_rec_gratuity_payment.amount),0));
1603 --
1604 END IF;
1605 --
1606 l_count := l_count + 1;
1607 --
1608 END LOOP;
1609 --
1610 IF l_gratuity_paid_flag = 0 THEN
1611 gXMLTable(1).Name := 'c_last_drawn_salary';
1612 gXMLTable(1).Value := 'No Data Exists.';
1613 multiColumnar('t_gratuity'
1614 ,gXMLTable
1615 ,1);
1616 --
1617 ELSE
1618 multiColumnar('t_gratuity'
1619 ,gXMLTable
1620 ,l_count);
1621 END IF;
1622 --
1623 fnd_file.put_line(fnd_file.log,'Created Gratuity Payment.');
1624 --
1625 fnd_file.put_line(fnd_file.log,'Creating XML for Dues to Employee.');
1626 --
1627 l_total_paid := 0;
1628 l_total_taxable := 0;
1629 l_total_exempted := 0 ;
1630 l_count := 2;
1631 l_emp_dues_flag := 0; /* variable to check if No data Found is to be printed */
1632 l_exempted := 0;
1633 l_paid := 0;
1634 l_taxable := 0;
1635 --
1636 -- Following step is carried out
1637 -- 1. The seeded Termination Payments elements are displayed here
1638 -- 2. The Amount Paid, Amount Exempted and Taxable Amount are summed
1639 --
1640 for c_rec_employee_dues in c_employee_dues(p_employee_number
1641 ,p_bus_grp_id
1642 ,l_asg_max_run_action_id
1643 )
1644 LOOP
1645 --
1646 IF l_emp_dues_flag = 0 then
1647 l_emp_dues_flag := 1;
1648 END IF;
1649 gXMLTable(1).Name := 'c_description';
1650 gXMLTable(1).Value := (c_rec_employee_dues.element);
1651 --
1652 IF c_rec_employee_dues.description = 'Non Taxable Amount' THEN
1653 gXMLTable(l_count).Name := 'c_amount_exempted';
1654 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1655 ,nvl(fnd_number.canonical_to_number(c_rec_employee_dues.amount),0));
1656 l_exempted := fnd_number.canonical_to_number(nvl(c_rec_employee_dues.amount,0));
1657 --
1658 ELSIF c_rec_employee_dues.description = 'Pay Value' AND fnd_number.canonical_to_number(NVL(c_rec_employee_dues.amount,0)) > 0 THEN
1659
1660 gXMLTable(l_count).Name := 'c_amount_paid';
1661 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1662 ,nvl(fnd_number.canonical_to_number(c_rec_employee_dues.amount),0));
1663 l_paid := fnd_number.canonical_to_number(nvl(c_rec_employee_dues.amount,0));
1664 --
1665 ELSIF c_rec_employee_dues.description = 'Taxable Amount' THEN
1666 --
1667 IF l_count = 3 THEN
1668 gXMLTable(l_count).Name := 'c_amount_paid';
1669 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id,0);
1670 l_paid := 0;
1671 l_count := l_count + 1;
1672 END IF;
1673 --
1674 gXMLTable(l_count).Name := 'c_taxable_amount';
1675 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1676 ,nvl(fnd_number.canonical_to_number(c_rec_employee_dues.amount),0));
1677 l_taxable := fnd_number.canonical_to_number(nvl(c_rec_employee_dues.amount,0));
1678 END IF;
1679 --
1680 l_count := l_count + 1;
1681 --
1682 IF l_count = 5 THEN
1683 l_count := 2;
1684 IF l_exempted = 0 AND
1685 l_paid = 0 AND
1686 l_taxable = 0 THEN
1687 if l_emp_dues_flag = 1 then
1688 l_emp_dues_flag := 0;
1689 end if;
1690 ELSE
1691 multiColumnar('t_due_to_ee'
1692 ,gXMLTable
1693 ,4);
1694 l_total_exempted := l_total_exempted + l_exempted;
1695 l_total_paid := l_total_paid + l_paid;
1696 l_total_taxable := l_total_taxable + l_taxable;
1697 l_exempted := 0;
1698 l_paid := 0;
1699 l_taxable := 0;
1700 l_emp_dues_flag := 2;
1701 END IF;
1702 --
1703 END IF;
1704 --
1705 END LOOP;
1706 --
1707 --
1708 -- Following step is carried out
1709 -- 1. The seeded Termination Information elements are displayed here
1710 -- 2. The Amount Paid, Amount Exempted and Taxable Amount are summed
1711 --
1712 l_count := 2;
1713 for c_rec_employee_term_dues in c_employee_term_dues(p_employee_number
1714 ,p_bus_grp_id
1715 ,l_asg_max_run_action_id
1716 )
1717 LOOP
1718 --
1719 l_emp_dues_flag := 1;
1720 gXMLTable(1).Name := 'c_description';
1721 gXMLTable(1).Value := (c_rec_employee_term_dues.element);
1722
1723 IF gXMLTable(1).Value = 'Retrenchment Compensation Information' THEN
1724 gXMLTable(1).Value := 'Retrenchment Compensation';
1725 ELSIF gXMLTable(1).Value = 'Voluntary Retirement Information' THEN
1726 gXMLTable(1).Value := 'Voluntary Retirement Compensation';
1727 ELSIF gXMLTable(1).Value = 'Other Termination Payments' THEN
1728 gXMLTable(1).Value := 'Other Termination Payments'; /* Bug No: 13630347 : Added ELSIF to display Other Termination Payments */
1729 END IF;
1730 --
1731 IF c_rec_employee_term_dues.description = 'Non Taxable Amount' THEN
1732 gXMLTable(l_count).Name := 'c_amount_exempted';
1733 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1734 ,nvl(fnd_number.canonical_to_number(c_rec_employee_term_dues.amount),0));
1735 l_exempted := fnd_number.canonical_to_number(nvl(c_rec_employee_term_dues.amount,0));
1736
1737 ELSIF c_rec_employee_term_dues.description = 'Taxable Amount' THEN
1738 gXMLTable(l_count).Name := 'c_taxable_amount';
1739 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1740 ,nvl(fnd_number.canonical_to_number(c_rec_employee_term_dues.amount),0));
1741 l_taxable := fnd_number.canonical_to_number(nvl(c_rec_employee_term_dues.amount,0));
1742
1743 END IF;
1744
1745 IF l_count = 3 THEN
1746 gXMLTable(4).Name := 'c_amount_paid';
1747 l_paid := l_exempted + l_taxable;
1748 gXMLTable(4).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1749 ,nvl(l_paid,0));
1750 multiColumnar('t_due_to_ee'
1751 ,gXMLTable
1752 ,4);
1753
1754 l_total_exempted := l_total_exempted + l_exempted;
1755 l_total_paid := l_total_paid + l_paid;
1756 l_total_taxable := l_total_taxable + l_taxable;
1757 l_count := 1;
1758 END IF;
1759
1760 l_count := l_count + 1;
1761
1762 END LOOP;
1763
1764 -- Following steps are carried out
1765 -- 1. The user created Termination Payments elements are displayed here
1766 -- 2. The Amount Paid, Amount Exempted and Taxable Amount are summed
1767 -- 3. If there are no does to employee, No Data Exists is printed.
1768 --
1769 for c_rec_employee_dues_user in c_employee_dues_user_elements(p_employee_number
1770 ,p_bus_grp_id
1771 ,l_asg_max_run_action_id)
1772 LOOP
1773 --
1774 IF fnd_number.canonical_to_number(NVL (c_rec_employee_dues_user.amount,0)) > 0 THEN
1775 l_emp_dues_flag := 1;
1776 gXMLTable(1).Name := 'c_description';
1777 gXMLTable(1).Value := (c_rec_employee_dues_user.description);
1778 --
1779 gXMLTable(2).Name := 'c_amount_exempted';
1780 gXMLTable(2).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id,0);
1781 --
1782 gXMLTable(3).Name := 'c_amount_paid';
1783 gXMLTable(3).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1784 ,nvl(fnd_number.canonical_to_number(c_rec_employee_dues_user.amount),0));
1785 l_total_paid := l_total_paid + fnd_number.canonical_to_number(nvl(c_rec_employee_dues_user.amount,0));
1786 --
1787 gXMLTable(4).Name := 'c_taxable_amount';
1788 gXMLTable(4).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1789 ,nvl(fnd_number.canonical_to_number(c_rec_employee_dues_user.amount),0));
1790 l_total_taxable := l_total_taxable + fnd_number.canonical_to_number(nvl(c_rec_employee_dues_user.amount,0));
1791 --
1792 gXMLTable(5).Name := 'c_elementname';
1793 gXMLTable(5).Value := (c_rec_employee_dues_user.elename);
1794 --
1795 multiColumnar('t_due_to_ee'
1796 ,gXMLTable
1797 ,5);
1798 END IF;
1799 END LOOP;
1800 --
1801 IF l_emp_dues_flag = 0 THEN
1802 gXMLTable(1).Name := 'c_description';
1803 gXMLTable(1).Value := 'No Data Exists.';
1804 multiColumnar('t_due_to_ee'
1805 ,gXMLTable
1806 ,1);
1807 ELSE
1808 l_tag := getTag('c_total_amount_paid', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1809 ,l_total_paid));
1810 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1811
1812 l_tag := getTag('c_total_taxable_amount', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1813 ,l_total_taxable));
1814 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1815
1816 l_tag := getTag('c_total_amount_exempted', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1817 ,l_total_exempted));
1818 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1819 END IF;
1820 --
1821 fnd_file.put_line(fnd_file.log,'Created Dues to Employee.');
1822 --
1823 fnd_file.put_line(fnd_file.log,'Creating XML for Net Amount Payables.');
1824 l_gross_earnings := 0;
1825 --
1826 -- Following steps are carried out
1827 -- 1. Net Amount is found through the Balance _ASG_RUN values
1828 -- 2. Gross Deductions are found by summing up the run values
1829 -- 3. Net Amount + Tax + Gross Deductions = Gross Earnings
1830 -- 4. Print the Net Amount in words.
1831 --
1832 for c_rec_gross_deductions in c_gross_deductions(p_employee_number
1833 ,p_bus_grp_id
1834 ,l_asg_max_run_action_id)
1835 LOOP
1836 --
1837 l_gross_earnings := l_gross_earnings + nvl(abs(fnd_number.canonical_to_number(c_rec_gross_deductions.amount)),0);
1838 --
1839 END LOOP;
1840
1841 l_tag := getTag('c_gross_deductions', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1842 ,l_gross_earnings));
1843 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1844
1845 --
1846 l_net_pay := 0;
1847 --
1848 for c_rec_balance_values in c_balance_values(p_employee_number
1849 ,p_bus_grp_id
1850 ,l_asg_max_run_action_id
1851 )
1852 LOOP
1853 --
1854
1855 l_net_pay := l_net_pay + fnd_number.canonical_to_number(c_rec_balance_values.amount);
1856 --
1857
1858 l_gross_earnings := l_gross_earnings + fnd_number.canonical_to_number(nvl(c_rec_balance_values.amount,0));
1859 --
1860 --
1861 END LOOP;
1862 l_net_pay := round(l_net_pay,2); /* rounded to two digits after decimal point to write amount in words */
1863 l_tag := getTag('c_net_amount', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1864 ,l_net_pay));
1865 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1866
1867 IF l_net_pay >= 0 THEN
1868 l_tag := getTag('c_net_amount_in_words', initcap(pay_in_utils.number_to_words(l_net_pay)));
1869 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1870 END IF;
1871
1872 --
1873 l_tag := getTag('c_gross_earnings', pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1874 ,l_gross_earnings));
1875 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1876 --
1877 fnd_file.put_line(fnd_file.log,'Created Net Amount Payables.');
1878 --
1879 fnd_file.put_line(fnd_file.log,'Creating XML for Payment Details.');
1880 --
1881 l_count := 1;
1882 l_payment_flag := 0;
1883 --
1884 -- Following steps are carried out
1885 -- 1. Payment Details are found out using the Pre Payments
1886 -- 2. Bank is found out by concatanating the Bank Name and Bank Branch
1887 -- 3. If Payment has not been made then Payment not done. is printed.
1888 --
1889 for c_rec_payment_details in c_payment_details(p_employee_number
1890 ,p_bus_grp_id
1891 ,l_max_payment_action_id)
1892 LOOP
1893 --
1894 l_payment_flag := 1;
1895
1896 IF l_count = 1 THEN
1897 l_tag := getTag('c_payment_date', to_char(c_rec_payment_details.payment_date,'dd-Mon-yyyy'));
1898 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1899 END IF;
1900
1901 l_count := 1;
1902 --
1903 --Payment Type
1904 gXMLTable(l_count).Name := 'c_payment_type';
1905 gXMLTable(l_count).Value := (c_rec_payment_details.payment_type);
1906 l_count := l_count + 1;
1907 --Bank
1908 gXMLTable(l_count).Name := 'c_bank';
1909 IF c_rec_payment_details.bank IS NOT NULL AND
1910 c_rec_payment_details.branch IS NOT NULL THEN
1911 gXMLTable(l_count).Value := c_rec_payment_details.bank || ', ' || c_rec_payment_details.branch;
1912 ELSE
1913 gXMLTable(l_count).Value := c_rec_payment_details.bank || ' ' || c_rec_payment_details.branch;
1914 END IF;
1915 l_count := l_count + 1;
1916 --Account Number
1917 gXMLTable(l_count).Name := 'c_account_number';
1918 gXMLTable(l_count).Value := (c_rec_payment_details.account_number);
1919 l_count := l_count + 1;
1920 --Amount
1921 gXMLTable(l_count).Name := 'c_amount';
1922 gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(p_bus_grp_id
1923 ,nvl(c_rec_payment_details.amount,0));
1924 multiColumnar('t_payment_details'
1925 ,gXMLTable
1926 ,l_count);
1927 END LOOP;
1928 --
1929 IF l_payment_flag = 0 THEN
1930 gXMLTable(1).Name := 'c_payment_type';
1931 gXMLTable(l_count).Value := 'Payment not done.';
1932 multiColumnar('t_payment_details'
1933 ,gXMLTable
1934 ,1);
1935 END IF;
1936 --
1937 fnd_file.put_line(fnd_file.log,'Created Payment Details.');
1938 --
1939 l_tag := '</TerminationDetails>';
1940 --
1941 dbms_lob.writeAppend(p_xml_data, length(l_tag), l_tag);
1942 --
1943 l_xml_data := p_xml_data;
1944 --
1945 pay_in_utils.trace('**************************************************','********************');
1946 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1947
1948 END create_xml;
1949 --
1950 end pay_in_term_rprt_gen_pkg;