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