[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_REPORTS_PKG
Source
1 PACKAGE BODY PAY_IN_REPORTS_PKG AS
2 /* $Header: pyinprpt.pkb 120.35.12010000.12 2008/09/12 10:16:43 rsaharay ship $ */
3
4 g_xml_data CLOB;
5 g_package CONSTANT VARCHAR2(100) := 'pay_in_reports_pkg.';
6 g_debug BOOLEAN ;
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : get_eit_remarks --
10 -- Type : FUNCTION --
11 -- Access : Private --
12 -- Description : This function finds the remrks for pf and esi --
13 -- --
14 -- Parameters : --
15 -- IN : p_assignment_id NUMBER --
16 -- p_report_type VARCHAR2 --
17 -- p_year VARCHAR2 --
18 -- p_mon VARCHAR2 --
19 -- RETURN : VARCHAR2 --
20 -- Change History : --
21 --------------------------------------------------------------------------
22 -- Rev# Date Userid Description --
23 --------------------------------------------------------------------------
24 -- 115.0 03-May-2005 aaagarwa Initial Version --
25 -- 115.1 18-Nov-2005 abhjain Added to_char in the cursor --
26 -- 115.1 06-Dec-2005 aaagarwa Added to_number in the cursor --
27 -- Resolved R12:D4 issues --
28 -- 115.2 11-Apr-2008 rsaharay Modified cursor c_remarks --
29 --------------------------------------------------------------------------
30 FUNCTION get_eit_remarks(p_number VARCHAR2
31 ,p_report_type VARCHAR2
32 ,p_period VARCHAR2
33 ,p_mon NUMBER
34 )
35 RETURN VARCHAR2
36 IS
37
38 CURSOR c_remarks
39 IS
40 SELECT aei_information3
41 FROM per_assignment_extra_info
42 WHERE aei_information2 = LPAD(p_mon,2,'0')
43 AND aei_information1 = p_period
44 AND information_type = DECODE(p_report_type,'PF','PER_IN_PF_REMARKS','ESI','PER_IN_ESI_REMARKS')
45 AND assignment_id IN(SELECT DISTINCT pea.assignment_id
46 FROM per_people_f pep -- Reduced cost from 2294 to 69
47 ,per_assignments_f pea-- Done this for bug 4774018
48 WHERE pep.person_id = pea.person_id
49 AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
50 AND p_number = DECODE(p_report_type,'PF'
51 ,pep.per_information8
52 ,pep.per_information9
53 )
54 );
55
56 l_remarks VARCHAR2(30);
57 l_message VARCHAR2(255);
58 l_procedure VARCHAR2(100);
59
60
61 BEGIN
62
63 l_procedure := g_package ||'get_eit_remarks';
64 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
65
66 IF g_debug THEN
67 pay_in_utils.trace('PF/ESI Number : ',p_number);
68 pay_in_utils.trace('Report Type : ',p_report_type);
69 pay_in_utils.trace('Period : ',p_period);
70 pay_in_utils.trace('Month Number : ',p_mon);
71 END IF;
72
73 OPEN c_remarks;
74 FETCH c_remarks INTO l_remarks;
75 CLOSE c_remarks;
76
77 IF g_debug THEN
78 pay_in_utils.trace('Remarks at AEI level : ',l_remarks);
79 END IF;
80
81 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
82
83 RETURN l_remarks;
84 END get_eit_remarks;
85
86 --------------------------------------------------------------------------
87 -- --
88 -- Name : INSERT_RECORD --
89 -- Type : PROCEDURE --
90 -- Access : Public --
91 -- Description : This procedure inserts a null record for PF Form 3A--
92 -- Parameters : --
93 -- IN : p_row_num NUMBER --
94 -- p_xml_data CLOB --
95 -- p_epf_org NUMBER --
96 -- p_pension_org NUMBER --
97 -- p_dli_contr NUMBER --
98 -- p_admin_chrg NUMBER --
99 -- p_edli_adm NUMBER --
100 -- p_total NUMBER --
101 -- OUT : p_xml_data CLOB --
102 -- --
103 -- Change History : --
104 --------------------------------------------------------------------------
105 -- Rev# Date Userid Description --
106 --------------------------------------------------------------------------
107 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
108 --------------------------------------------------------------------------
109 PROCEDURE insert_record(p_row_num NUMBER
110 ,p_xml_data IN OUT NOCOPY CLOB
111 ,p_epf_org NUMBER DEFAULT null
112 ,p_pension_org NUMBER DEFAULT null
113 ,p_dli_contr NUMBER DEFAULT null
114 ,p_admin_chrg NUMBER DEFAULT null
115 ,p_edli_adm NUMBER DEFAULT null
116 ,p_total NUMBER DEFAULT null
117 )
118 IS
119 l_count NUMBER;
120 l_bg_id NUMBER;
121 l_message VARCHAR2(255);
122 l_procedure VARCHAR2(100);
123
124
125 BEGIN
126 l_procedure := g_package ||'insert_record';
127 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
128
129 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
130
131 -- pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(,0));
132
133 pay_in_xml_utils.gXMLTable.delete;
134 l_count:=1;
135 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_sl';
136 pay_in_xml_utils.gXMLTable(l_count).Value := (p_row_num+1);
137 l_count := l_count + 1;
138 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_month';
139 pay_in_xml_utils.gXMLTable(l_count).Value :=
140 (to_char(add_months(to_date('01-03-2004','DD-MM-YYYY'),p_row_num),'Mon'));
141 IF pay_in_xml_utils.gXMLTable(l_count).Value='Mar' THEN
142 pay_in_xml_utils.gXMLTable(l_count).Value:=pay_in_xml_utils.gXMLTable(l_count).Value||' Paid in April';
143 END IF;
144 l_count := l_count + 1;
145 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_org';
146 pay_in_xml_utils.gXMLTable(l_count).Value :=
147 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_epf_org,0));
148 l_count := l_count + 1;
149 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_org';
150 pay_in_xml_utils.gXMLTable(l_count).Value :=
151 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pension_org,0));
152 l_count := l_count + 1;
153 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_dli_pf_org';
154 pay_in_xml_utils.gXMLTable(l_count).Value :=
155 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_dli_contr,0));
156 l_count := l_count + 1;
157 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_admin_pf';
158 pay_in_xml_utils.gXMLTable(l_count).Value :=
159 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_admin_chrg,0));
160 l_count := l_count + 1;
161 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_edli_adm';
162 pay_in_xml_utils.gXMLTable(l_count).Value :=
163 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_edli_adm,0));
164 l_count := l_count + 1;
165 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_total';
166 pay_in_xml_utils.gXMLTable(l_count).Value :=
167 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_total,0));
168 l_count := l_count + 1;
169 pay_in_xml_utils.multiColumnar('monthly',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
170 pay_in_xml_utils.gXMLTable.delete;
171
172 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
173
174 END insert_record;
175
176 --------------------------------------------------------------------------
177 -- --
178 -- Name : INSERT_NULL_RECORD --
179 -- Type : PROCEDURE --
180 -- Access : Public --
181 -- Description : This procedure inserts a null record for PF Form 6A --
182 -- Parameters : --
183 -- IN : p_month_name VARCHAR2 --
184 -- p_xml_data CLOB --
185 -- p_pf_salary_ptd VARCHAR2 --
186 -- p_epf VARCHAR2 --
187 -- p_epf_diff VARCHAR2 --
188 -- p_pension_fund VARCHAR2 --
189 -- p_absence VARCHAR2 --
190 -- p_remarks VARCHAR2 --
191 -- OUT : p_xml_data CLOB --
192 -- --
193 -- Change History : --
194 --------------------------------------------------------------------------
195 -- Rev# Date Userid Description --
196 --------------------------------------------------------------------------
197 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
198 --------------------------------------------------------------------------
199 PROCEDURE insert_null_record(p_month_name VARCHAR2
200 ,p_xml_data IN OUT NOCOPY CLOB
201 ,p_pf_salary_ptd VARCHAR2 DEFAULT NULL
202 ,p_epf VARCHAR2 DEFAULT NULL
203 ,p_epf_diff VARCHAR2 DEFAULT NULL
204 ,p_pension_fund VARCHAR2 DEFAULT NULL
205 ,p_absence VARCHAR2 DEFAULT NULL
206 ,p_remarks VARCHAR2 DEFAULT NULL )
207 IS
208 l_count NUMBER;
209 l_bg_id NUMBER;
210 l_message VARCHAR2(255);
211 l_procedure VARCHAR2(100);
212
213 BEGIN
214 l_procedure := g_package ||'insert_null_record';
215 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
216
217
218 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
219 l_count:=1;
220 --PF Salary _ASG_ORG_PTD
221 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_salary_ptd';
222 pay_in_xml_utils.gXMLTable(l_count).Value :=
223 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pf_salary_ptd,0));
224 l_count := l_count + 1;
225 --Total Employee Contr
226 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf';
227 pay_in_xml_utils.gXMLTable(l_count).Value :=
228 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_epf,0));
229 l_count := l_count + 1;
230 --Employer Contr towards PF
231 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_difference';
232 pay_in_xml_utils.gXMLTable(l_count).Value :=
233 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_epf_diff,0));
234 l_count := l_count + 1;
235 --Employer Contr towards Pension
236 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_fund';
237 pay_in_xml_utils.gXMLTable(l_count).Value :=
238 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pension_fund,0));
239 l_count := l_count + 1;
240 --Absence
241 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_absence';
242 pay_in_xml_utils.gXMLTable(l_count).Value :=
243 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_absence,0));
244 l_count := l_count + 1;
245 --Remarks
246 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
247 pay_in_xml_utils.gXMLTable(l_count).Value := (p_remarks);
248 l_count := l_count + 1;
249 --Payroll Month
250 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_month';
251 IF p_month_name='Mar'then
252 pay_in_xml_utils.gXMLTable(l_count).Value :=p_month_name||' Paid in Apr';
253 ELSE
254 pay_in_xml_utils.gXMLTable(l_count).Value :=p_month_name;
255 END IF;
256 l_count := l_count + 1;
257 pay_in_xml_utils.multiColumnar('t_month',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
258 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
259
260 END insert_null_record;
261
262 --------------------------------------------------------------------------
263 -- --
264 -- Name : INSERT_CH_RECORD --
265 -- Type : PROCEDURE --
266 -- Access : Public --
267 -- Description : This procedure inserts challan data and null for --
268 -- Pension Form 8 --
269 -- Parameters : --
270 -- IN : p_row_num NUMBER --
271 -- p_xml_data CLOB --
272 -- p_pension_org NUMBER --
273 -- p_total NUMBER --
274 -- OUT : p_xml_data CLOB --
275 -- --
276 -- Change History : --
277 --------------------------------------------------------------------------
278 -- Rev# Date Userid Description --
279 --------------------------------------------------------------------------
280 -- 115.0 11-Feb-2005 vgsriniv Initial Version --
281 --------------------------------------------------------------------------
282 PROCEDURE insert_ch_record(p_row_num IN NUMBER
283 ,p_xml_data IN OUT NOCOPY CLOB
284 ,p_pension_org IN NUMBER DEFAULT null
285 )
286 IS
287 l_count NUMBER;
288 l_bg_id NUMBER;
289 l_message VARCHAR2(255);
290 l_procedure VARCHAR2(100);
291
292 BEGIN
293 l_procedure := g_package ||'insert_ch_record';
294 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
295
296 pay_in_xml_utils.gXMLTable.DELETE;
297 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
298 l_count:=1;
299
300 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_org'||p_row_num;
301 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,p_pension_org);
302 l_count := l_count + 1;
303
304 pay_in_xml_utils.multiColumnar('monthly',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
305 pay_in_xml_utils.gXMLTable.DELETE;
306 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
307
308 END insert_ch_record;
309
310 --------------------------------------------------------------------------
311 -- --
312 -- Name : INSERT_NULL_FORM7_RECORD --
313 -- Type : PROCEDURE --
314 -- Access : Public --
315 -- Description : This procedure inserts a null record for PF Form 7 --
316 -- Parameters : --
317 -- IN : p_month_name VARCHAR2 --
318 -- p_xml_data CLOB --
319 -- p_pf_salary_ptd VARCHAR2 --
320 -- p_pension_fund VARCHAR2 --
321 -- p_absence VARCHAR2 --
322 -- p_remarks VARCHAR2 --
323 -- OUT : p_xml_data CLOB --
324 -- --
325 -- Change History : --
326 --------------------------------------------------------------------------
327 -- Rev# Date Userid Description --
328 --------------------------------------------------------------------------
329 -- 115.0 08-Mar-2005 lnagaraj Initial Version --
330 --------------------------------------------------------------------------
331 PROCEDURE insert_null_form7_record(p_month_name VARCHAR2
332 ,p_xml_data IN OUT NOCOPY CLOB
333 ,p_pf_salary_ptd VARCHAR2 DEFAULT NULL
334 ,p_pension_fund VARCHAR2 DEFAULT NULL
335 ,p_absence VARCHAR2 DEFAULT NULL
336 ,p_remarks VARCHAR2 DEFAULT NULL )
337 IS
338 l_count NUMBER;
339 l_bg_id NUMBER;
340 l_message VARCHAR2(255);
341 l_procedure VARCHAR2(100);
342
343
344 BEGIN
345
346 l_procedure := g_package ||'insert_null_form7_record';
347 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
348
349 l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
350 l_count:=1;
351 --PF Salary _ASG_ORG_PTD
352 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_salary_ptd';
353 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pf_salary_ptd,0));
354 l_count := l_count + 1;
355
356 --Employer Contr towards Pension
357 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_fund';
358 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(p_pension_fund,0));
359 l_count := l_count + 1;
360 --Absence
361 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_absence';
362 pay_in_xml_utils.gXMLTable(l_count).Value := nvl(p_absence,0);
363 l_count := l_count + 1;
364 --Remarks
365 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
366 pay_in_xml_utils.gXMLTable(l_count).Value := (p_remarks);
367 l_count := l_count + 1;
368 --Payroll Month
369 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_month';
370 IF p_month_name='Mar'then
371 pay_in_xml_utils.gXMLTable(l_count).Value :=p_month_name||' Paid in Apr';
372 ELSE
373 pay_in_xml_utils.gXMLTable(l_count).Value :=p_month_name;
374 END IF;
375
376 pay_in_xml_utils.multiColumnar('t_month',pay_in_xml_utils.gXMLTable,l_count,p_xml_data);
377 END insert_null_form7_record;
378
379 --------------------------------------------------------------------------
380 -- --
381 -- Name : CREATE_PT_FORM3_XML --
382 -- Type : PROCEDURE --
383 -- Access : Private --
384 -- Description : This procedure creates XML data for PT Form III --
385 -- --
386 -- Parameters : --
387 -- IN : p_pt_org_id VARCHAR2 --
388 -- p_frequency VARCHAR2 --
389 -- p_year VARCHAR2 --
390 -- p_period VARCHAR2 --
391 -- OUT : N/A --
392 -- --
393 -- Change History : --
394 --------------------------------------------------------------------------
395 -- Rev# Date Userid Description --
396 --------------------------------------------------------------------------
397 -- 115.0 18-May-2005 abhjain Initial Version --
398 -- --
399 --------------------------------------------------------------------------
400
401 PROCEDURE create_pt_form3_xml(p_pt_org_id VARCHAR2
402 ,p_frequency VARCHAR2
403 ,p_year VARCHAR2
404 ,p_period VARCHAR2)
405 IS
406 --DISTINCT Organization Id and Name
407 CURSOR c_distinct_org(p_period_start DATE
408 ,p_period_end DATE)
409 IS
410 SELECT DISTINCT source_id org_id
411 ,action_information9 org_name
412 FROM pay_action_information
413 ,hr_organization_units hou
414 WHERE action_information_category = 'IN_PT_ASG'
415 AND action_context_type = 'AAP'
416 AND source_id = NVL(p_pt_org_id, source_id)
417 AND jurisdiction_code = 'MH'
418 AND hou.organization_id = source_id
419 AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
420 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
421 ORDER BY action_information9 ASC;
422
423 --Select the highest payroll action id for an Org
424 CURSOR c_max_pa_action_id(p_pt_org_id NUMBER
425 ,p_period_start DATE
426 ,p_period_end DATE)
427 IS
428 SELECT MAX(pai.action_context_id)
429 FROM pay_action_information pai
430 ,pay_assignment_actions pac
431 WHERE pai.action_information_category = 'IN_PT_PAY'
432 AND pai.action_context_type = 'PA'
433 AND pai.source_id = p_pt_org_id
434 AND pac.payroll_action_id = pai.action_context_id
435 AND pac.assignment_action_id IN
436 ( SELECT action_context_id
437 FROM pay_action_information
438 WHERE action_information_category = 'IN_PT_ASG'
439 AND action_context_type = 'AAP'
440 AND action_information1 = p_year -- Bug 5231500
441 AND source_id = p_pt_org_id
442 AND TO_DATE(action_information8,'DD-MM-YY')=
443 ( SELECT MAX(TO_DATE(action_information8,'DD-MM-YY'))
444 FROM pay_action_information
445 WHERE action_information_category = 'IN_PT_ASG'
446 AND action_context_type = 'AAP'
447 AND action_information1 = p_year -- Bug 5231500
448 AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
449 AND source_id = p_pt_org_id
450 )
451 );
452
453 --Organization Details based on payroll action id found in above CURSOR
454 CURSOR c_org_details(p_payroll_action_id NUMBER
455 ,p_pt_org_id VARCHAR2)
456 IS
457 SELECT action_information7 employer_code
458 ,action_information4 registered_name
459 ,action_information6 reg_address
460 ,action_information5 rep_name
461 ,action_information9 rep_desg
462 ,action_information8 org_name
463 ,action_information3 bsrtc
464 FROM pay_action_information
465 WHERE action_information_category = 'IN_PT_PAY'
466 AND action_context_type = 'PA'
467 AND source_id = p_pt_org_id
468 AND action_context_id = p_payroll_action_id;
469
470 --Challan Information
471 CURSOR c_challan(p_pt_org_id NUMBER
472 ,p_period_start DATE
473 ,p_period_end DATE)
474 IS
475 SELECT NVL(SUM(fnd_number.canonical_to_number(org_information5)),0) challan_amt
476 ,NVL(SUM(fnd_number.canonical_to_number(org_information6)),0) interest_amt
477 ,NVL(SUM(fnd_number.canonical_to_number(org_information8)),0) excess_amt
478 FROM hr_organization_information
479 WHERE organization_id = p_pt_org_id
480 AND org_information_context = 'PER_IN_PT_CHALLAN_INFO'
481 AND ADD_MONTHS(TO_DATE('01-'|| org_information1 || SUBSTR(org_information9, 1, 4), 'DD-MM-YYYY'), 3)
482 BETWEEN p_period_start AND p_period_end;
483
484 cursor cur_get_records_number(p_source_id NUMBER
485 ,p_year VARCHAR2
486 ,p_month NUMBER)
487 IS
488 SELECT COUNT(*) count
489 ,fnd_number.canonical_to_number(pai.action_information5) rate
490 FROM pay_action_information pai
491 WHERE pai.jurisdiction_code = 'MH'
492 AND pai.source_id = p_source_id
493 AND pai.action_information_category = 'IN_PT_ASG'
494 AND pai.action_information1 = p_year
495 AND TO_NUMBER(pai.action_information2) = p_month
496 AND pai.action_context_type = 'AAP'
497 AND pai.action_information6 <> 'Yes'
498 AND pai.action_information_id = (SELECT MAX(action_information_id)
499 FROM pay_action_information
500 WHERE action_information1 = p_year
501 AND TO_NUMBER(action_information2) = p_month
502 AND assignment_id = pai.assignment_id
503 AND action_context_type = 'AAP'
504 AND action_information_category = 'IN_PT_ASG'
505 AND jurisdiction_code = 'MH'
506 AND source_id = p_source_id)
507 GROUP BY pai.action_information5
508 ORDER BY pai.action_information5;
509
510 l_bg_id NUMBER;
511 l_payroll_act_id NUMBER;
512 l_period_month NUMBER;
513 l_count_0 NUMBER;
514 -- l_count_30 NUMBER;
515 l_count_60 NUMBER;
516 l_count_120 NUMBER;
517 l_count_175 NUMBER;
518 l_count_200 NUMBER;
519 l_count_300 NUMBER;
520 l_count_200_temp NUMBER;
521 l_count_300_temp NUMBER;
522 l_slab_month_count NUMBER;
523 l_slab NUMBER;
524 -- l_tax_slab_2 NUMBER;
525 l_tax_slab_3 NUMBER;
526 l_tax_slab_4 NUMBER;
527 l_tax_slab_5 NUMBER;
528 l_tax_slab_6 NUMBER;
529 -- l_slab_2 NUMBER;
530 l_slab_3 NUMBER;
531 l_slab_4 NUMBER;
532 l_slab_5 NUMBER;
533 l_slab_6 NUMBER;
534 l_slab_6_2 NUMBER; --For 300
535 l_tax_amount NUMBER;
536 l_net_amount NUMBER;
537 l_excess_tax NUMBER;
538 l_interest_amount NUMBER;
539 l_period_year VARCHAR2(40);
540 l_sys_date_time VARCHAR2(40);
541 period_start_year VARCHAR2(40);
542 l_slab_month_tag VARCHAR2(40);
543 l_tag VARCHAR2(1000);
544 l_bsrtc_no VARCHAR2(50);
545 l_reg_address VARCHAR2(240);
546 period_start DATE;
547 period_end DATE;
548 l_date DATE;
549 l_registered_no hr_organization_information.org_information1%TYPE;
550 l_registered_name hr_organization_units.name%TYPE;
551 l_rep_name per_all_people_f.full_name%TYPE;
552 l_rep_desg per_all_positions.name%TYPE;
553 l_org_name hr_organization_units.name%TYPE;
554 l_message VARCHAR2(255);
555 l_procedure VARCHAR2(100);
556
557
558 BEGIN
559 l_procedure := g_package ||'create_pt_form3_xml';
560 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
561
562 -- l_slab_2 := 30;
563 l_slab_3 := 60;
564 l_slab_4 := 120;
565 l_slab_5 := 175;
566 l_slab_6 := 200;
567 l_slab_6_2 := 300;
568 --
569 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
570
571 fnd_file.put_line(fnd_file.log,'Creating the XML...');
572
573 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
574 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
575 --
576 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
577 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
578 l_tag := '<PT_FORM3>';
579 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
580
581 fnd_file.put_line(fnd_file.log,'Started...');
582 fnd_file.put_line(fnd_file.log,'Creating XML for PT Form III.');
583
584 --System Date:
585 l_sys_date_time := to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
586 l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
587 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
588
589 IF g_debug THEN
590 pay_in_utils.trace('Business Group id ',l_bg_id);
591 pay_in_utils.trace('System Date and Time ',l_sys_date_time);
592 END IF;
593
594
595 pay_in_utils.set_location(g_debug,l_procedure, 20);
596
597 period_start_year := SUBSTR(p_year, 1, 4);
598 IF p_frequency = 'A' THEN
599 period_start := TO_DATE('01-03-'||period_start_year, 'DD-MM-YYYY');
600 period_end := ADD_MONTHS(period_start, 12) -1;
601 ELSIF p_frequency = 'Q' THEN
602 period_start := ADD_MONTHS(TO_DATE('01-03-'||period_start_year, 'DD-MM-YYYY'), TO_NUMBER(SUBSTR(p_period, 2, 2)));
603 IF TO_NUMBER(TO_CHAR(period_start, 'YYYY')) > TO_NUMBER(period_start_year) THEN
604 period_start := ADD_MONTHS(period_start, -12);
605 END IF;
606 period_end := ADD_MONTHS(period_start, 3) -1;
607 ELSIF p_frequency = 'M' THEN
608 period_start := ADD_MONTHS(TO_DATE('01-03-'|| period_start_year, 'DD-MM-YYYY'), MOD(TO_NUMBER(p_period),12));
609 period_end := ADD_MONTHS(period_start, 1) -1;
610 END IF;
611
612 pay_in_utils.set_location(g_debug,l_procedure, 30);
613
614 IF g_debug THEN
615 pay_in_utils.trace('period_start_year ',period_start_year);
616 pay_in_utils.trace('period_start ',period_start);
617 pay_in_utils.trace('period_end ',period_end);
618 END IF;
619
620
621 FOR c_rec IN c_distinct_org (period_start
622 ,period_end)
623 LOOP
624 l_tag :='<organization>';
625 pay_in_utils.set_location(g_debug,l_procedure, 40);
626 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
627
628 IF g_debug THEN
629 pay_in_utils.trace('c_rec.org_id ',c_rec.org_id);
630 END IF;
631
632 fnd_file.put_line(fnd_file.log,'c_rec.org_id '|| c_rec.org_id);
633
634 OPEN c_max_pa_action_id(c_rec.org_id
635 ,period_start
636 ,period_end);
637 FETCH c_max_pa_action_id INTO l_payroll_act_id;
638 CLOSE c_max_pa_action_id ;
639
640 IF g_debug THEN
641 pay_in_utils.trace('Payroll Action id ',l_payroll_act_id);
642 END IF;
643
644 pay_in_utils.set_location(g_debug,l_procedure, 50);
645
646 OPEN c_org_details(l_payroll_act_id
647 ,c_rec.org_id);
648 FETCH c_org_details INTO l_registered_no
649 ,l_registered_name
650 ,l_reg_address
651 ,l_rep_name
652 ,l_rep_desg
653 ,l_org_name
654 ,l_bsrtc_no;
655 CLOSE c_org_details;
656 pay_in_utils.set_location(g_debug,l_procedure, 60);
657 -- Registered No
658 l_tag :=pay_in_xml_utils.getTag('c_registered_no', l_registered_no);
659 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
660 --Organization Name and Address
661 l_tag :=pay_in_xml_utils.getTag('c_registered_name', l_registered_name);
662 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
663 l_tag :=pay_in_xml_utils.getTag('c_registered_address', l_reg_address);
664 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
665 --Report Period
666 l_tag :=pay_in_xml_utils.getTag('c_period_from', to_char(period_start));
667 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
668 l_tag :=pay_in_xml_utils.getTag('c_period_to', to_char(period_end));
669 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
670 --Organization Rep Name and Designation
671 l_tag :=pay_in_xml_utils.getTag('c_rep_name', l_rep_name);
672 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
673 l_tag :=pay_in_xml_utils.getTag('c_rep_desg', l_rep_desg);
674 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
675 --BSRTC No
676 l_tag :=pay_in_xml_utils.getTag('c_bsrtc_no', l_bsrtc_no);
677 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
678 --Date
679 l_tag :=pay_in_xml_utils.getTag('c_date', substr(l_sys_date_time, 1, 11));
680 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
681 pay_in_utils.set_location(g_debug,l_procedure, 70);
682
683 IF g_debug THEN
684 pay_in_utils.trace('Registered No ',l_registered_no);
685 pay_in_utils.trace('Registered Name ',l_registered_name);
686 pay_in_utils.trace('Registered Address ',l_reg_address);
687 pay_in_utils.trace('BSRTC no ',l_bsrtc_no);
688 pay_in_utils.trace('Period Start ',period_start);
689 pay_in_utils.trace('Period End ',period_end);
690 END IF;
691
692 l_count_0 := 0;
693 -- l_count_30 := 0;
694 l_count_60 := 0;
695 l_count_120 := 0;
696 l_count_175 := 0;
697 l_count_200 := 0;
698 l_count_300 := 0;
699 -- l_tax_slab_2 := 0;
700 l_tax_slab_3 := 0;
701 l_tax_slab_4 := 0;
702 l_tax_slab_5 := 0;
703 l_tax_slab_6 := 0;
704 l_net_amount := 0;
705 l_tax_amount := 0;
706 l_excess_tax := 0;
707 l_interest_amount := 0;
708
709 l_date := period_start;
710
711 pay_in_utils.set_location(g_debug,l_procedure, 80);
712
713 -- Getting the records for each month in the report period
714 WHILE l_date < period_end
715 LOOP
716
717 l_slab_month_count := 0;
718 l_count_200_temp := 0;
719 l_count_300_temp := 0;
720
721 fnd_file.put_line(fnd_file.log,'l_date: '|| l_date);
722
723 l_period_month := TO_NUMBER(TO_CHAR(ADD_MONTHS(l_date, -3), 'MM'));
724
725 IF l_period_month >= 10 THEN
726 l_period_year := TO_CHAR(ADD_MONTHS(l_date, -12), 'YYYY')||'-'||TO_CHAR(l_date, 'YYYY');
727 ELSE
728 l_period_year := TO_CHAR(l_date, 'YYYY')||'-'||TO_CHAR(ADD_MONTHS(l_date, 12), 'YYYY');
729 END IF;
730
731
732 IF g_debug THEN
733 pay_in_utils.trace('l_date ',l_date);
734 pay_in_utils.trace('l_period_month ',l_period_month);
735 END IF;
736 pay_in_utils.set_location(g_debug,l_procedure, 90);
737
738 -- Getting the no. of employees in each slab
739 FOR rec_count in cur_get_records_number(c_rec.org_id
740 ,l_period_year
741 ,l_period_month)
742 LOOP
743 IF rec_count.rate = 0 THEN
744 l_count_0 := l_count_0 + rec_count.count;
745 l_slab_month_count := rec_count.count;
746 l_slab := 1;
747 /* ELSIF rec_count.rate = l_slab_2 THEN
748 l_count_30 := l_count_30 + rec_count.count;
749 l_slab_month_count := rec_count.count;
750 l_slab := 2;*/
751 ELSIF rec_count.rate = l_slab_3 THEN
752 l_count_60 := l_count_60 + rec_count.count;
753 l_slab_month_count := rec_count.count;
754 l_slab := 3;
755 ELSIF rec_count.rate = l_slab_4 THEN
756 l_count_120 := l_count_120 + rec_count.count;
757 l_slab_month_count := rec_count.count;
758 l_slab := 4;
759 ELSIF rec_count.rate = l_slab_5 THEN
760 l_count_175 := l_count_175 + rec_count.count;
761 l_slab_month_count := rec_count.count;
762 l_slab := 5;
763 ELSIF rec_count.rate = l_slab_6 THEN
764 l_count_200 := l_count_200 + rec_count.count;
765 l_count_200_temp := rec_count.count;
766 l_slab := 6;
767 ELSIF rec_count.rate = l_slab_6_2 THEN
768 l_count_300 := l_count_300 + rec_count.count;
769 l_count_300_temp := rec_count.count;
770 l_slab := 6;
771 END IF;
772
773 IF g_debug THEN
774 pay_in_utils.trace('PT slab rate ',rec_count.rate);
775 pay_in_utils.trace('Count of Records ',rec_count.count);
776 END IF;
777
778 pay_in_utils.set_location(g_debug,l_procedure, 100);
779
780 IF l_slab <> 6 THEN
781 -- Writing to the report if the slab is not the highest slab
782 -- Highest slab is written to the report once the loop is over since a sum is to be taken for 200 and 300
783 l_slab_month_tag := 'c_month' || l_period_month || '_emp_slab'|| l_slab;
784 fnd_file.put_line(fnd_file.log,'l_slab_month_tag: '|| l_slab_month_tag);
785 l_tag := pay_in_xml_utils.getTag(l_slab_month_tag, l_slab_month_count);
786 dbms_lob.writeAppend(g_xml_data, LENGTH(l_tag), l_tag);
787 END IF;
788
789 END LOOP;
790
791 IF g_debug THEN
792 pay_in_utils.trace('l_slab_month_tag ',l_slab_month_tag);
793 pay_in_utils.trace('l_count_0 ',l_count_0);
794 -- pay_in_utils.trace('l_count_30 ',l_count_30);
795 pay_in_utils.trace('l_count_60 ',l_count_60);
796 pay_in_utils.trace('l_count_120 ',l_count_120);
797 pay_in_utils.trace('l_count_175 ',l_count_175);
798 pay_in_utils.trace('l_count_200+l_count_300 ',(l_count_200 + l_count_300));
799
800 END IF;
801
802
803 pay_in_utils.set_location(g_debug,l_procedure, 110);
804
805 l_slab_month_count := l_count_300_temp + l_count_200_temp;
806
807 IF l_slab_month_count <> 0 THEN
808 -- Writing to the file for the highest slab
809 l_slab_month_tag := 'c_month' || l_period_month || '_emp_slab6';
810 l_tag := pay_in_xml_utils.getTag(l_slab_month_tag, l_slab_month_count);
811 dbms_lob.writeAppend(g_xml_data, LENGTH(l_tag), l_tag);
812 END IF;
813
814 l_date := ADD_MONTHS(l_date, 1);
815
816 END LOOP;
817 pay_in_utils.set_location(g_debug,l_procedure, 120);
818
819 -- Writing the total employees in a slab
820 l_tag := pay_in_xml_utils.getTag('c_emp_slab_1', l_count_0);
821 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
822 -- l_tag := pay_in_xml_utils.getTag('c_emp_slab_2', l_count_30);
823 -- dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
824 l_tag := pay_in_xml_utils.getTag('c_emp_slab_3', l_count_60);
825 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
826 l_tag := pay_in_xml_utils.getTag('c_emp_slab_4', l_count_120);
827 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
828 l_tag := pay_in_xml_utils.getTag('c_emp_slab_5', l_count_175);
829 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
830 l_tag := pay_in_xml_utils.getTag('c_emp_slab_6', l_count_200 + l_count_300);
831 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
832
833 -- Calculating the PT paid in each slab
834 -- l_tax_slab_2 := l_slab_2 * l_count_30;
835 l_tax_slab_3 := l_slab_3 * l_count_60;
836 l_tax_slab_4 := l_slab_4 * l_count_120;
837 l_tax_slab_5 := l_slab_5 * l_count_175;
838 l_tax_slab_6 := l_slab_6 * l_count_200 + l_slab_6_2 * l_count_300;
839
840 -- Writing to file the calculated PT paid in each slab
841 -- l_tag := pay_in_xml_utils.getTag('c_tax_slab_2', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_2,0)));
842 -- dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
843 l_tag := pay_in_xml_utils.getTag('c_tax_slab_3', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_3,0)));
844 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
845 l_tag := pay_in_xml_utils.getTag('c_tax_slab_4', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_4,0)));
846 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
847 l_tag := pay_in_xml_utils.getTag('c_tax_slab_5', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_5,0)));
848 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
849 l_tag := pay_in_xml_utils.getTag('c_tax_slab_6', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_slab_6,0)));
850 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
851
852 pay_in_utils.set_location(g_debug,l_procedure, 130);
853
854 -- Getting the actual PT paid from the challans data
855 OPEN c_challan(c_rec.org_id
856 ,period_start
857 ,period_end);
858 FETCH c_challan INTO l_tax_amount
859 ,l_interest_amount
860 ,l_excess_tax;
861 CLOSE c_challan;
862
863 IF g_debug THEN
864 pay_in_utils.trace('Tax Amount ',l_tax_amount);
865 pay_in_utils.trace('Interest Amount ',l_interest_amount);
866 pay_in_utils.trace('Excess Tax ',l_excess_tax);
867 END IF;
868
869
870 l_tag := pay_in_xml_utils.getTag('c_tax_amount', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_tax_amount,0)));
871 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
872
873 l_tag := pay_in_xml_utils.getTag('c_interest_amount', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_interest_amount,0)));
874 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
875
876 l_tag := pay_in_xml_utils.getTag('c_excess_tax', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_excess_tax,0)));
877 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
878
879 l_net_amount := l_tax_amount - l_excess_tax + l_interest_amount;
880
881 l_tag := pay_in_xml_utils.getTag('c_net_amount', pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_net_amount,0)));
882 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
883
884 l_tag := pay_in_xml_utils.getTag('c_total_amount_words', initcap(pay_in_utils.number_to_words(l_net_amount)));
885 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
886
887 l_tag :=pay_in_xml_utils.getTag('c_year', period_start_year);
888 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
889 l_tag :=pay_in_xml_utils.getTag('c_year1', SUBSTR(p_year, 6, 4));
890 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
891
892 l_tag :='</organization>';
893 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
894 pay_in_utils.set_location(g_debug,l_procedure, 140);
895 fnd_file.put_line(fnd_file.log,'Org Over');
896 END LOOP;
897 l_tag :='</PT_FORM3>';
898 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
899 fnd_file.put_line(fnd_file.log,'XML Created.');
900 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 150);
901
902 END create_pt_form3_xml;
903
904
905 --------------------------------------------------------------------------
906 -- --
907 -- Name : WRITE_TAG --
908 -- Type : PROCEDURE --
909 -- Access : Private --
910 -- Description : This procedure inserts tags into the clob variable --
911 -- Parameters : --
912 -- IN : p_tag_name VARCHAR2 --
913 -- p_tag_value VARCHAR2 --
914 -- OUT : N/A --
915 -- --
916 -- Change History : --
917 --------------------------------------------------------------------------
918 -- Rev# Date Userid Description --
919 --------------------------------------------------------------------------
920 -- 115.0 27-Jul-2005 vgsriniv Initial Version --
921 --------------------------------------------------------------------------
922
923 PROCEDURE write_tag ( p_tag_name IN VARCHAR2
924 , p_tag_value IN VARCHAR2)
925 IS
926 l_tag VARCHAR2(10000);
927 l_procedure VARCHAR2(100);
928 BEGIN
929 l_procedure := g_package ||'write_tag';
930 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
931
932 l_tag := pay_in_xml_utils.getTag( p_tag_name => p_tag_name
933 , p_tag_value => p_tag_value
934 );
935
936 dbms_lob.writeAppend(g_xml_data,length(l_tag),l_tag);
937 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
938 END write_tag;
939
940 --------------------------------------------------------------------------
941 -- --
942 -- Name : GET_LOCATION_DETAILS --
943 -- Type : FUNCTION --
944 -- Access : Private --
945 -- Description : This function gets the gre location details --
946 -- --
947 -- Parameters : --
948 -- IN : p_location_id hr_locations.location_id --
949 -- : p_concatenate VARCHAR2 --
950 -- p_field VARCHAR2 --
951 --------------------------------------------------------------------------
952 FUNCTION get_location_details ( p_location_id IN VARCHAR2
953 ,p_field IN VARCHAR2 DEFAULT NULL)
954 RETURN VARCHAR2
955 IS
956
957 CURSOR csr_add IS
958 SELECT address_line_1,
959 address_line_2,
960 address_line_3,
961 loc_information14,
962 loc_information15,
963 hr_general.decode_lookup('IN_STATES',loc_information16),
964 postal_code,
965 telephone_number_1,
966 telephone_number_2
967 FROM hr_locations
968 WHERE location_id = p_location_id;
969
970 l_add_1 hr_locations.address_line_1%TYPE;
971 l_add_2 hr_locations.address_line_2%TYPE;
972 l_add_3 hr_locations.address_line_3%TYPE;
973 l_add_4 hr_locations.loc_information14%TYPE;
974 l_add_5 hr_locations.loc_information15%TYPE;
975 l_state hr_lookups.meaning%TYPE;
976 l_pin hr_locations.postal_code%TYPE;
977 l_tel hr_locations.telephone_number_1%TYPE;
978 l_fax hr_locations.telephone_number_2%TYPE;
979 l_details VARCHAR2(1000);
980 l_message VARCHAR2(255);
981 l_procedure VARCHAR2(100);
982
983 --
984 BEGIN
985
986 g_debug := hr_utility.debug_enabled;
987 l_procedure := g_package ||'get_location_details';
988 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
989
990 OPEN csr_add;
991 FETCH csr_add INTO l_add_1, l_add_2, l_add_3, l_add_4, l_add_5, l_state, l_pin,l_tel,l_fax;
992 CLOSE csr_add;
993
994 IF p_field = 'EMPLOYER_ADDRESS1' THEN
995 l_details := l_add_1;
996 ELSIF p_field = 'EMPLOYER_ADDRESS2' THEN
997 l_details := l_add_2;
998 ELSIF p_field = 'EMPLOYER_ADDRESS3' THEN
999 l_details := l_add_3;
1000 ELSIF p_field = 'EMPLOYER_ADDRESS4' THEN
1001 l_details := l_add_4;
1002 ELSIF p_field = 'CITY' THEN
1003 l_details := l_add_5;
1004 ELSIF p_field = 'EMPLOYER_STATE' THEN
1005 l_details := l_state;
1006 ELSIF p_field = 'POSTAL_CODE' THEN
1007 l_details := l_pin;
1008 ELSIF p_field = 'TELEPHONE' THEN
1009 l_details := l_tel;
1010 ELSIF p_field = 'FAX' THEN
1011 l_details := l_fax;
1012 END IF;
1013
1014 IF g_debug THEN
1015 pay_in_utils.trace('Location id ',p_location_id);
1016 pay_in_utils.trace('Field ',p_field);
1017 pay_in_utils.trace('Address Details ',l_details);
1018 END IF;
1019
1020 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1021
1022 RETURN l_details;
1023
1024 END get_location_details;
1025
1026 --------------------------------------------------------------------------
1027 -- --
1028 -- Name : CREATE_FORM27A_XML --
1029 -- Type : PROCEDURE --
1030 -- Access : Public --
1031 -- Description : This procedure creates XML data for Form 27A --
1032 -- --
1033 -- Parameters : --
1034 -- IN : p_gre_org_id VARCHAR2 --
1035 -- p_assess_year VARCHAR2 --
1036 -- OUT : N/A --
1037 -- --
1038 -- Change History : --
1039 --------------------------------------------------------------------------
1040 -- Rev# Date Userid Description --
1041 --------------------------------------------------------------------------
1042 -- 115.0 27-Jul-2005 vgsriniv Initial Version --
1043 --------------------------------------------------------------------------
1044
1045 PROCEDURE create_form27A_xml(p_gre_org_id IN VARCHAR2
1046 ,p_assess_year IN VARCHAR2)
1047 IS
1048
1049 CURSOR csr_org_max_action_context_id
1050 IS
1051 SELECT MAX(pai.action_context_id)
1052 FROM pay_action_information pai
1053 WHERE pai.action_information_category = 'IN_EOY_ORG'
1054 AND pai.Action_information1 = p_gre_org_id
1055 AND pai.action_information3 = p_assess_year
1056 AND pai.action_context_type = 'PA';
1057
1058
1059 CURSOR csr_emplr_details(p_action_context_id IN NUMBER)
1060 IS
1061 SELECT pai.action_information4
1062 , pai.action_information2
1063 , pai.action_information8
1064 , pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS1')
1065 , pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS2')
1066 , pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS3')
1067 , pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_ADDRESS4')
1068 , pay_in_reports_pkg.get_location_details(pai.action_information7,'CITY')
1069 , pay_in_reports_pkg.get_location_details(pai.action_information7,'EMPLOYER_STATE')
1070 , pay_in_reports_pkg.get_location_details(pai.action_information7,'POSTAL_CODE')
1071 , pay_in_reports_pkg.get_location_details(pai.action_information7,'TELEPHONE')
1072 , pay_in_reports_pkg.get_location_details(pai.action_information7,'FAX')
1073 , pai.action_information11
1074 , pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS1')
1075 , pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS2')
1076 , pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS3')
1077 , pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_ADDRESS4')
1078 , pay_in_reports_pkg.get_location_details(pai.action_information16,'CITY')
1079 , pay_in_reports_pkg.get_location_details(pai.action_information16,'EMPLOYER_STATE')
1080 , pay_in_reports_pkg.get_location_details(pai.action_information16,'POSTAL_CODE')
1081 , pai.action_information17
1082 , pai.action_information18
1083 , pai.action_information13
1084 FROM pay_action_information pai
1085 , pay_payroll_actions ppa
1086 WHERE pai.action_information_category = 'IN_EOY_ORG'
1087 AND pai.action_context_type = 'PA'
1088 AND pai.action_information1 = p_gre_org_id
1089 AND pai.action_information3 = p_assess_year
1090 AND pai.action_context_id = p_action_context_id
1091 AND ppa.action_type='X'
1092 AND ppa.action_status = 'C'
1093 AND ppa.report_type='IN_EOY_ARCHIVE'
1094 AND ppa.report_qualifier = 'IN'
1095 AND ppa.payroll_action_id = pai.action_context_id;
1096
1097 CURSOR csr_tot_emp_cnt
1098 IS
1099 SELECT COUNT(*)
1100 FROM pay_action_information
1101 WHERE action_information_category = 'IN_EOY_PERSON'
1102 AND action_context_type = 'AAP'
1103 AND action_information2 = p_assess_year
1104 AND action_information3 = p_gre_org_id
1105 AND action_context_id IN ( SELECT MAX(action_context_id)
1106 FROM pay_action_information pai
1107 ,pay_assignment_actions paa
1108 ,per_assignments_f asg
1109 WHERE pai.action_information_category = 'IN_EOY_PERSON'
1110 AND pai.action_context_type = 'AAP'
1111 AND pai.action_information2 = p_assess_year
1112 AND pai.action_information3 = p_gre_org_id
1113 AND pai.assignment_id = asg.assignment_id
1114 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1115 AND pai.source_id = paa.assignment_action_id
1116 GROUP BY pai.action_information1,pai.action_information17 );
1117
1118
1119 CURSOR csr_tax_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
1120 IS
1121 SELECT NVL(SUM(fnd_number.canonical_to_number(action_information2)),0)
1122 FROM pay_action_information
1123 WHERE action_information_category = 'IN_EOY_ASG_SAL'
1124 AND action_context_type = 'AAP'
1125 AND action_information1 = p_balance
1126 AND action_context_id = p_action_context_id
1127 AND source_id = p_source_id;
1128
1129 CURSOR csr_get_max_cont_id IS
1130 SELECT MAX(pai.action_context_id) action_cont_id
1131 ,source_id sour_id
1132 FROM pay_action_information pai
1133 ,pay_assignment_actions paa
1134 ,per_assignments_f asg
1135 WHERE pai.action_information_category = 'IN_EOY_PERSON'
1136 AND pai.action_information3 = p_gre_org_id
1137 AND pai.action_information2 = p_assess_year
1138 AND pai.assignment_id = asg.assignment_id
1139 AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1140 AND pai.action_context_type = 'AAP'
1141 AND pai.source_id = paa.assignment_action_id
1142 GROUP BY pai.action_information1,pai.action_information17,source_id;
1143
1144
1145 l_tag VARCHAR2(2000);
1146 l_sys_date_time VARCHAR2(30);
1147 l_sys_date VARCHAR2(30);
1148 l_org_max_action_id NUMBER;
1149 l_tan VARCHAR2(20);
1150 l_pan VARCHAR2(20);
1151 l_leg_name VARCHAR2(80);
1152 l_add1 VARCHAR2(100);
1153 l_add2 VARCHAR2(100);
1154 l_add3 VARCHAR2(100);
1155 l_add4 VARCHAR2(100);
1156 l_add5 VARCHAR2(100);
1157 l_state VARCHAR2(100);
1158 l_pin VARCHAR2(100);
1159 l_tel VARCHAR2(100);
1160 l_fax VARCHAR2(100);
1161 l_rep_name VARCHAR2(100);
1162 l_rep_add1 VARCHAR2(100);
1163 l_rep_add2 VARCHAR2(100);
1164 l_rep_add3 VARCHAR2(100);
1165 l_rep_add4 VARCHAR2(100);
1166 l_rep_add5 VARCHAR2(100);
1167 l_rep_state VARCHAR2(100);
1168 l_rep_pin VARCHAR2(100);
1169 l_rep_tel VARCHAR2(100);
1170 l_rep_fax VARCHAR2(100);
1171 l_rep_desg VARCHAR2(100);
1172 l_year VARCHAR2(9);
1173 l_tot_emp NUMBER;
1174 l_tot_income NUMBER:=0;
1175 l_it_td NUMBER;
1176 l_sc_td NUMBER;
1177 l_ec_td NUMBER;
1178 l_tds NUMBER:=0;
1179 l_bg_id NUMBER;
1180 l_itd NUMBER:=0;
1181 l_inc NUMBER:=0;
1182 l_message VARCHAR2(255);
1183 l_procedure VARCHAR2(100);
1184
1185
1186
1187 BEGIN
1188
1189 g_debug := hr_utility.debug_enabled;
1190 l_procedure := g_package ||'create_form27A_xml';
1191 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1192
1193 IF g_debug THEN
1194 pay_in_utils.trace('GRE id ',p_gre_org_id);
1195 pay_in_utils.trace('Assessment year ',p_assess_year);
1196 END IF;
1197
1198
1199 pay_in_xml_utils.gXMLTable.DELETE;
1200 --
1201 fnd_file.put_line(fnd_file.log,'Creating the XML...');
1202 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
1203 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
1204 --
1205 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
1206 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1207
1208 l_tag := '<FORM27A>';
1209 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1210 fnd_file.put_line(fnd_file.log,'Started...');
1211
1212 pay_in_utils.set_location(g_debug,l_procedure, 20);
1213 --
1214 fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1215 l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1216 l_sys_date := TO_CHAR(SYSDATE,'DD-Mon-YYYY');
1217 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
1218 --System Date
1219 l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1220 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1221
1222 pay_in_utils.set_location(g_debug,l_procedure, 30);
1223
1224 OPEN csr_org_max_action_context_id;
1225 FETCH csr_org_max_action_context_id INTO l_org_max_action_id;
1226 CLOSE csr_org_max_action_context_id;
1227
1228 OPEN csr_emplr_details(l_org_max_action_id);
1229 FETCH csr_emplr_details INTO l_tan,l_pan,l_leg_name,l_add1,l_add2,l_add3,l_add4,l_add5,
1230 l_state,l_pin,l_tel,l_fax,l_rep_name,l_rep_add1,l_rep_add2,
1231 l_rep_add3,l_rep_add4,l_rep_add5,l_rep_state,l_rep_pin,
1232 l_rep_tel,l_rep_fax,l_rep_desg;
1233 CLOSE csr_emplr_details;
1234
1235
1236 OPEN csr_tot_emp_cnt;
1237 FETCH csr_tot_emp_cnt INTO l_tot_emp;
1238 CLOSE csr_tot_emp_cnt;
1239
1240 pay_in_utils.set_location(g_debug,l_procedure, 40);
1241
1242 FOR i IN csr_get_max_cont_id
1243 LOOP
1244 pay_in_utils.set_location(g_debug,l_procedure, 50);
1245 OPEN csr_tax_details('Income Tax Deduction',i.action_cont_id,i.sour_id);
1246 FETCH csr_tax_details INTO l_itd;
1247 CLOSE csr_tax_details;
1248
1249 l_tds := l_tds + l_itd;
1250
1251 OPEN csr_tax_details('F16 Total Income',i.action_cont_id,i.sour_id);
1252 FETCH csr_tax_details INTO l_inc;
1253 CLOSE csr_tax_details;
1254
1255 l_tot_income := l_tot_income + l_inc;
1256
1257 END LOOP;
1258
1259 l_year := (SUBSTR(p_assess_year,1,4)||'-'||SUBSTR(p_assess_year,8));
1260
1261 WRITE_TAG('C_TAN',l_tan);
1262 WRITE_TAG('C_PAN',l_pan);
1263 WRITE_TAG('C_LEG_NAME',l_leg_name);
1264 WRITE_TAG('C_ADD1',l_add1);
1265 WRITE_TAG('C_ADD2',l_add2);
1266 WRITE_TAG('C_ADD3',l_add3);
1267 WRITE_TAG('C_ADD4',l_add4);
1268 WRITE_TAG('C_ADD5',l_add5);
1269 WRITE_TAG('C_STATE',l_state);
1270 WRITE_TAG('C_PIN',l_pin);
1271 WRITE_TAG('C_TEL',l_tel);
1272 WRITE_TAG('C_FAX',l_fax);
1273
1274 WRITE_TAG('C_REP_TAN',l_tan);
1275 WRITE_TAG('C_REP_ADD1',l_rep_add1);
1276 WRITE_TAG('C_REP_ADD2',l_rep_add2);
1277 WRITE_TAG('C_REP_ADD3',l_rep_add3);
1278 WRITE_TAG('C_REP_ADD4',l_rep_add4);
1279 WRITE_TAG('C_REP_ADD5',l_rep_add5);
1280 WRITE_TAG('C_REP_STATE',l_rep_state);
1281 WRITE_TAG('C_REP_PIN',l_rep_pin);
1282 WRITE_TAG('C_REP_TEL',l_rep_teL);
1283 WRITE_TAG('C_REP_FAX',l_rep_fax);
1284
1285 WRITE_TAG('C_TOT_AMT_PAID',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_tot_income));
1286 WRITE_TAG('C_TDS',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_tds));
1287 WRITE_TAG('C_TOT_EMP',l_tot_emp);
1288
1289 WRITE_TAG('C_ASSESS_YEAR',l_year);
1290 WRITE_TAG('C_REP_NAME',l_rep_name);
1291 WRITE_TAG('C_CITY',l_rep_add5);
1292 WRITE_TAG('C_SYS_DATE',l_sys_date);
1293 WRITE_TAG('C_SYS_DATE_TIME',l_sys_date_time);
1294 WRITE_TAG('C_REP_DESG',l_rep_desg);
1295
1296
1297 l_tag := '</FORM27A>';
1298 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1299
1300 fnd_file.put_line(fnd_file.log,'XML Created.');
1301 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
1302
1303
1304 END create_form27A_xml;
1305
1306
1307 --------------------------------------------------------------------------
1308 -- --
1309 -- Name : INIT_CODE --
1310 -- Type : PROCEDURE --
1311 -- Access : Public --
1312 -- Description : This procedure calls procedure for PF Form3A or PF --
1313 -- Form6A depending on the report type parameter --
1314 -- Parameters : --
1315 -- IN : p_contribution_period VARCHAR2 --
1316 -- p_report_type VARCHAR2 --
1317 -- p_pf_org_id VARCHAR2 --
1318 -- p_pf_number VARCHAR2 --
1319 -- p_template_appl VARCHAR2 --
1320 -- p_template_code VARCHAR2 --
1321 -- p_number_of_copies VARCHAR2 --
1322 -- OUT : N/A --
1323 -- --
1324 -- Change History : --
1325 --------------------------------------------------------------------------
1326 -- Rev# Date Userid Description --
1327 --------------------------------------------------------------------------
1328 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
1329 -- 115.1 08-Mar-2005 lnagaraj Added for Form 7 --
1330 --------------------------------------------------------------------------
1331 PROCEDURE init_code
1332 (p_pf_org_id IN VARCHAR2 DEFAULT NULL
1333 ,p_pf_number IN VARCHAR2 DEFAULT NULL
1334 ,p_pension_number IN VARCHAR2 DEFAULT NULL
1335 ,p_contribution_period IN VARCHAR2 DEFAULT NULL
1336 ,p_form_type IN VARCHAR2
1337 ,p_employee_type IN VARCHAR2 DEFAULT NULL
1338 ,p_esi_org_id IN VARCHAR2 DEFAULT NULL
1339 ,p_esi_coverage IN VARCHAR2 DEFAULT NULL
1340 ,p_sysdate IN DATE DEFAULT NULL
1341 ,p_template_name IN VARCHAR2
1342 ,p_xml OUT NOCOPY CLOB
1343 ,p_pt_org_id IN VARCHAR2 DEFAULT NULL
1344 ,p_frequency IN VARCHAR2 DEFAULT NULL
1345 ,p_year IN VARCHAR2 DEFAULT NULL
1346 ,p_period IN VARCHAR2 DEFAULT NULL
1347 ,p_gre_org_id IN VARCHAR2 DEFAULT NULL
1348 ,p_assess_year IN VARCHAR2 DEFAULT NULL)
1349 IS
1350 l_effective_start_date DATE;
1351 l_effective_end_date DATE;
1352 l_contribution_period VARCHAR2(40);
1353 l_message VARCHAR2(255);
1354 l_procedure VARCHAR2(100);
1355
1356 BEGIN
1357 --
1358 g_debug := hr_utility.debug_enabled;
1359 l_procedure := g_package ||'init_code';
1360 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1361
1362 IF p_form_type <> 'ESI6' and p_form_type <> 'PT3' AND p_form_type <> 'FORM27A' THEN
1363 l_effective_start_date:=to_date(('01-03-'||substr(p_contribution_period,1,4)),'DD-MM-YYYY');
1364 l_effective_end_date:= to_date(('01-03-'||substr(p_contribution_period,6)),'DD-MM-YYYY')-1;
1365 ELSE
1366 l_contribution_period := hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',p_contribution_period);
1367 END IF;
1368
1369 IF g_debug THEN
1370 pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1371 pay_in_utils.trace('PF Number ',p_pf_number);
1372 pay_in_utils.trace('Employee Type ',p_employee_type);
1373 pay_in_utils.trace('Contribution Period ',p_contribution_period);
1374 pay_in_utils.trace('Effective Start Date',l_effective_start_date);
1375 pay_in_utils.trace('Effective End Date ',l_effective_end_date);
1376 pay_in_utils.trace('Pension Number ',p_pension_number);
1377 pay_in_utils.trace('ESI Org ID ',p_esi_org_id);
1378 pay_in_utils.trace('ESI Coverage ',p_esi_coverage);
1379 pay_in_utils.trace('Contribution Period ',l_contribution_period);
1380 pay_in_utils.trace('Session Date ',p_sysdate);
1381 pay_in_utils.trace('PT Org ID ',p_pt_org_id);
1382 pay_in_utils.trace('PT Frequency ',p_frequency);
1383 pay_in_utils.trace('PT Year ',p_year);
1384 pay_in_utils.trace('Period ',p_period);
1385 END IF;
1386
1387
1388
1389
1390 IF p_form_type = 'FORM3A' THEN
1391 --
1392 pay_in_utils.set_location(g_debug,l_procedure, 20);
1393 create_form3a_xml(p_pf_org_id => p_pf_org_id
1394 ,p_pf_number => p_pf_number
1395 ,p_employee_type=> p_employee_type
1396 ,p_contribution_period=> p_contribution_period
1397 ,p_effective_start_date => l_effective_start_date
1398 ,p_effective_end_date => l_effective_end_date
1399 );
1400 ELSIF p_form_type = 'FORM6A' THEN
1401 --
1402 pay_in_utils.set_location(g_debug,l_procedure, 30);
1403 create_form6a_xml(p_pf_org_id => p_pf_org_id
1404 ,p_effective_start_date => l_effective_start_date
1405 ,p_effective_end_date => l_effective_end_date
1406 ,p_contribution_period=>p_contribution_period
1407 );
1408 ELSIF p_form_type = 'FORM8' THEN
1409 --
1410 pay_in_utils.set_location(g_debug,l_procedure, 40);
1411 create_form8_xml(p_pf_org_id => p_pf_org_id
1412 ,p_contribution_period => p_contribution_period
1413 ,p_effective_start_date => l_effective_start_date
1414 ,p_effective_end_date => l_effective_end_date
1415 );
1416 ELSIF p_form_type ='FORM7' THEN
1417 --
1418 pay_in_utils.set_location(g_debug,l_procedure, 50);
1419 create_form7_xml(p_pf_org_id => p_pf_org_id
1420 ,p_pension_number => p_pension_number
1421 ,p_employee_type => p_employee_type
1422 ,p_contribution_period => p_contribution_period
1423 ,p_effective_start_date => l_effective_start_date
1424 ,p_effective_end_date => l_effective_end_date
1425 );
1426 ELSIF p_form_type ='ESI6' THEN
1427 --
1428 pay_in_utils.set_location(g_debug,l_procedure, 60);
1429 create_esi_xml(p_esi_org_id => p_esi_org_id
1430 ,p_contribution_period => l_contribution_period
1431 ,p_esi_coverage => p_esi_coverage
1432 ,p_sysdate => p_sysdate);
1433
1434 ELSIF p_form_type ='PT3' THEN
1435 --
1436 pay_in_utils.set_location(g_debug,l_procedure, 70);
1437 create_pt_form3_xml(p_pt_org_id => p_pt_org_id
1438 ,p_frequency => p_frequency
1439 ,p_year => p_year
1440 ,p_period => p_period);
1441
1442 ELSIF p_form_type='FORM27A' THEN
1443 pay_in_utils.set_location(g_debug,l_procedure, 80);
1444 create_form27A_xml(P_GRE_ORG_ID => P_GRE_ORG_ID
1445 ,P_ASSESS_YEAR => P_ASSESS_YEAR);
1446
1447 ELSE
1448 null;
1449 END IF;
1450
1451
1452 p_xml := g_xml_data;
1453 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 90);
1454
1455
1456 END init_code;
1457
1458
1459 --------------------------------------------------------------------------
1460 -- --
1461 -- Name : CREATE_FORM8_XML --
1462 -- Type : PROCEDURE --
1463 -- Access : Public --
1464 -- Description : This procedure creates XML data for Pension Form 8 --
1465 -- --
1466 -- Parameters : --
1467 -- IN : p_pf_org_id VARCHAR2 --
1468 -- p_contribution_period VARCHAR2 --
1469 -- p_effective_start_date DATE --
1470 -- p_effective_end_date DATE --
1471 -- OUT : N/A --
1472 -- --
1473 -- Change History : --
1474 --------------------------------------------------------------------------
1475 -- Rev# Date Userid Description --
1476 --------------------------------------------------------------------------
1477 -- 115.0 11-Feb-2005 vgsriniv Initial Version --
1478 -- 115.1 31-Mar-2005 aaagarwa Added the join for BG id --
1479 -- 115.2 01-Apr-2005 lnagaraj Removed c_pf_org_summation_details --
1480 -- Modified c_asg_summation_details --
1481 -- Changes for excluded employee --
1482 --------------------------------------------------------------------------
1483 PROCEDURE create_form8_xml(p_pf_org_id IN VARCHAR2
1484 ,p_contribution_period IN VARCHAR2
1485 ,p_effective_start_date IN DATE
1486 ,p_effective_end_date IN DATE)
1487 IS
1488
1489 CURSOR c_pf_org_id(p_pf_org_id VARCHAR2
1490 ,p_contribution_period VARCHAR2
1491 ,p_effective_start_date DATE
1492 ,p_effective_end_date DATE
1493 )
1494 IS
1495 SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496 ,paa_pay.action_information3 --PF Org Reg Name
1497 ,paa_pay.action_information5 --Address
1498 ,paa_pay.action_information6 --Code
1499 ,paa_pay.action_information8 --PF Org Name
1500 FROM pay_action_information paa_asg
1501 ,pay_action_information paa_pay
1502 ,pay_assignment_actions paa
1503 ,hr_organization_units hou
1504 WHERE paa_asg.action_information_category = 'IN_PF_ASG'
1505 AND paa_pay.action_information_category = 'IN_PF_PAY'
1506 AND paa_asg.ACTION_CONTEXT_TYPE = 'AAP'
1507 AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
1508 AND paa.assignment_action_id = paa_asg.action_context_id
1509 AND paa.payroll_action_id = paa_pay.action_context_id
1510 AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
1511 AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
1512 AND paa_asg.action_information2 = paa_pay.action_information2
1513 AND paa_asg.action_information15 IS NOT NULL
1514 AND paa_asg.action_information1 = p_contribution_period
1515 AND paa_pay.action_information1 = p_contribution_period
1516 AND hou.organization_id=paa_pay.action_information2
1517 AND hou.organization_id=paa_asg.action_information2
1518 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
1519 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1520 ORDER BY paa_pay.action_information8 ASC;
1521
1522 CURSOR c_assignment_id(p_pf_org_id VARCHAR2
1523 ,p_contribution_period VARCHAR2
1524 ,p_effective_start_date DATE
1525 ,p_effective_end_date DATE)
1526 IS
1527 SELECT DISTINCT action_information15,assignment_id
1528 FROM pay_action_information
1529 WHERE action_information_category = 'IN_PF_ASG'
1530 AND action_information2 = p_pf_org_id --PF Organization ID
1531 AND action_information1 = p_contribution_period
1532 AND action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1533 AND assignment_id is not null
1534 ORDER BY action_information15 ASC;
1535
1536 /* Bugfix 4253674 and 4270904 Start*/
1537
1538 /*Find the global value as on Financial year start */
1539 CURSOR csr_global_value(p_name VARCHAR2) IS
1540 SELECT fnd_number.canonical_to_number(glb.global_value)
1541 FROM ff_globals_f glb
1542 WHERE glb.global_name = p_name
1543 AND glb.LEGISLATION_CODE ='IN'
1544 AND p_effective_end_date BETWEEN glb.effective_start_date and glb.effective_end_date;
1545
1546 /* Check if an employee is excluded or not */
1547 CURSOR csr_not_excluded_employee(p_pf_org_id VARCHAR2
1548 ,p_contribution_period VARCHAR2
1549 ,p_pension_number VARCHAR2)
1550 IS
1551 SELECT '1' status
1552 FROM pay_action_information pai
1553 WHERE pai.action_information_category ='IN_PF_ASG'
1554 AND pai.action_information1=p_contribution_period
1555 AND pai.action_information2 = p_pf_org_id
1556 AND pai.action_information15 = p_pension_number
1557 AND NVL(pai.action_information18,'0') = '0'
1558 AND ROWNUM <2;
1559
1560
1561 /*Modified for Bug 5647738 */
1562 CURSOR c_asg_summation_details(p_pf_org_id VARCHAR2
1563 ,p_contribution_period VARCHAR2
1564 ,p_pension_number VARCHAR2
1565 ,p_pf_salary_ceiling NUMBER)
1566 IS
1567 SELECT
1568 SUM(fnd_number.canonical_to_number(pai_mas.action_information10)) pension
1569 FROM pay_action_information pai_mas
1570 where pai_mas.action_information_category ='IN_PF_ASG'
1571 and pai_mas.action_information1 = p_contribution_period
1572 and pai_mas.action_information2 = p_pf_org_id
1573 and pai_mas.action_information15 = p_pension_number
1574 and pai_mas.action_information_id in (SELECT MAX(pai1.action_information_id)
1575 FROM pay_action_information pai1
1576 WHERE pai1.action_information1 = p_contribution_period
1577 AND pai1.action_information2 = p_pf_org_id
1578 AND pai1.action_information15 = p_pension_number
1579 GROUP BY TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
1580 );
1581
1582 Cursor c_asg_details(p_pf_org_id VARCHAR2
1583 ,p_pf_number VARCHAR2
1584 ,p_effective_start_date DATE
1585 ,p_effective_end_date DATE
1586 )
1587 IS
1588 SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
1589 FROM pay_action_information
1590 WHERE action_information_category='IN_PF_ASG'
1591 AND action_information3 =p_pf_number
1592 AND action_information2 =p_pf_org_id --PF Organization ID
1593 AND action_information1 = p_contribution_period -- Bug 5231500
1594 AND action_information13 BETWEEN p_effective_start_date --Payroll Date
1595 AND p_effective_end_date
1596 ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') ASC;
1597
1598 CURSOR c_pf_employer(p_pf_org_id VARCHAR2
1599 ,p_effective_start_date DATE
1600 ,p_effective_end_date DATE)
1601 IS
1602 SELECT fnd_number.canonical_to_number(org_information2) mon --Month Number
1603 ,SUM(fnd_number.canonical_to_number(org_information5)) pension --Pension Fund Contributions A/c No.10
1604 FROM hr_organization_information
1605 WHERE organization_id = p_pf_org_id
1606 AND ORG_INFORMATION_CONTEXT = 'PER_IN_PF_CHALLAN_INFO'
1607 AND org_information1=TO_CHAR(p_effective_start_date,'YYYY')||'-'||TO_CHAR(p_effective_end_date,'YYYY')
1608 GROUP BY org_information2
1609 ORDER BY org_information2 ASC;
1610
1611 CURSOR c_emp_name(p_pf_org_id VARCHAR2
1612 ,p_pension_number VARCHAR2
1613 ,p_effective_start_date DATE
1614 ,p_effective_end_date DATE
1615 )
1616 IS
1617 SELECT action_information4 --Full Name
1618 FROM pay_action_information
1619 WHERE action_information_category='IN_PF_ASG'
1620 AND action_information1 = p_contribution_period -- Bug 5231500
1621 AND action_information2 = p_pf_org_id
1622 AND action_information15 = p_pension_number
1623 AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
1624 ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
1625
1626 CURSOR c_rep_name(p_contribution_period VARCHAR2
1627 ,p_pf_org_id VARCHAR2
1628 ,p_effective_start_date DATE
1629 ,p_effective_end_date DATE
1630 )
1631 IS
1632 SELECT paa_pay.action_information4 rep_name
1633 FROM pay_action_information paa_asg
1634 ,pay_action_information paa_pay
1635 ,pay_assignment_actions paa
1636 WHERE paa_asg.action_information_category='IN_PF_ASG'
1637 AND paa_pay.action_information_category='IN_PF_PAY'
1638 AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1639 AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1640 AND paa.assignment_action_id=paa_asg.action_context_id
1641 AND paa.payroll_action_id=paa_pay.action_context_id
1642 AND paa_pay.action_information7 = 'EXEM'
1643 AND paa_asg.action_information1=p_contribution_period
1644 AND paa_pay.action_information1=p_contribution_period
1645 AND paa_pay.action_information2=p_pf_org_id
1646 AND paa_asg.action_information2=p_pf_org_id
1647 AND paa_asg.action_information1=paa_pay.action_information1
1648 AND paa_asg.action_information2=paa_pay.action_information2
1649 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1650 ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
1651
1652 /*Added for Bug 5647738*/
1653 cursor c_monthly_contributions( p_pf_org_id NUMBER
1654 , p_pension_no varchar2)
1655 IS
1656 SELECT fnd_number.canonical_to_number(pai.action_information7) pf_wages
1657 ,pai.action_information13 date_earned
1658 FROM pay_action_information pai
1659 WHERE pai.action_information_category ='IN_PF_ASG'
1660 AND pai.action_information1 = p_contribution_period
1661 AND pai.action_information2 = p_pf_org_id
1662 AND pai.action_information15 = p_pension_no
1663 AND pai.assignment_id IS NOT NULL
1664 GROUP BY pai.action_information13
1665 ,pai.action_information7
1666 ,pai.action_information10
1667 ,pai.action_information11
1668 ,pai.action_information17
1669 ,pai.action_information18
1670 ,pai.action_information_id
1671 ,pai.assignment_id
1672 HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
1673 FROM pay_Action_information pai1
1674 WHERE pai1.action_information_category ='IN_PF_ASG'
1675 AND pai1.action_information1 = p_contribution_period
1676 AND pai1.action_information2 = p_pf_org_id
1677 AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
1678 AND pai1.action_information15 = p_pension_no
1679 )
1680 ORDER BY TO_DATE(pai.action_information13,'DD-MM-YY'), pai.action_information_id desc;
1681
1682
1683 l_count NUMBER;
1684 l_tag VARCHAR2(2000);
1685 l_rate NUMBER;
1686 l_employee_no NUMBER;
1687 l_remarks VARCHAR2(2000);
1688 l_remarks_dummy VARCHAR2(2000);
1689 l_row_count NUMBER;
1690 l_epf_total NUMBER;
1691 l_pension_total NUMBER;
1692 l_dli_total NUMBER;
1693 l_admn_total NUMBER;
1694 l_edli_adm_total NUMBER;
1695 l_summation NUMBER;
1696 l_sys_date_time VARCHAR2(30);
1697 l_bg_id NUMBER;
1698 l_salary_ceiling NUMBER;
1699 l_org_pf_ytd NUMBER;
1700 l_org_pension_ytd NUMBER;
1701 l_payroll_mon NUMBER;
1702 l_mon NUMBER;
1703 l_message VARCHAR2(255);
1704 l_procedure VARCHAR2(100);
1705 l_cont_type VARCHAR2(15) := 'DEFAULT';
1706 l_contribution_type VARCHAR2(15):= 'DEFAULT';
1707 l_cont_type_date_earned VARCHAR(20):= 'DEFAULT';
1708 l_pf_ceiling_type varchar2(50);
1709
1710 date_earned DATE;
1711 l_employee_name per_all_people_f.full_name%TYPE;
1712 asg_sum NUMBER := 0;
1713
1714
1715 BEGIN
1716 g_debug := hr_utility.debug_enabled;
1717 l_procedure := g_package ||'create_form8_xml';
1718 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1719
1720
1721 pay_in_xml_utils.gXMLTable.DELETE;
1722 --
1723 fnd_file.put_line(fnd_file.log,'Creating the XML...');
1724 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
1725 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
1726 --
1727 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
1728 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1729
1730 l_tag := '<FORM8>';
1731 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1732 fnd_file.put_line(fnd_file.log,'Started...');
1733 --
1734 fnd_file.put_line(fnd_file.log,'Creating XML for Employer Details.');
1735 l_sys_date_time:=TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1736 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
1737 --System Date
1738 l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
1739 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1740
1741 pay_in_utils.set_location(g_debug,l_procedure, 20);
1742
1743 FOR c_rec IN c_pf_org_id(p_pf_org_id
1744 ,p_contribution_period
1745 ,p_effective_start_date
1746 ,p_effective_end_date)
1747 LOOP
1748 pay_in_utils.set_location(g_debug,l_procedure, 30);
1749 l_tag := '<organization>';
1750 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1751
1752 IF g_debug THEN
1753 pay_in_utils.trace('PF Organization id ',p_pf_org_id);
1754 pay_in_utils.trace('Contribution Period ',p_contribution_period);
1755 END IF;
1756
1757 l_count:=1;
1758 --PF Org Reg Name
1759 --PF Org Reg Name Made in BLOCK
1760 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_org_name';
1761 pay_in_xml_utils.gXMLTable(l_count).Value := upper(c_rec.action_information3);
1762 l_count := l_count + 1;
1763 --Address
1764 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_address';
1765 pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information5);
1766 l_count := l_count + 1;
1767 --Code
1768 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_code';
1769 pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information6);
1770 l_count := l_count + 1;
1771 --Starting Year
1772 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_start_year';
1773 pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_start_date,'YYYY'));
1774 l_count := l_count + 1;
1775 --Ending Year
1776 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_end_year';
1777 pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_end_date,'YYYY'));
1778 l_count := l_count + 1;
1779
1780 pay_in_xml_utils.multiColumnar('org',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
1781
1782 l_employee_no:=1;
1783 l_org_pf_ytd := 0;
1784 l_org_pension_ytd :=0;
1785 pay_in_utils.set_location(g_debug,l_procedure, 40);
1786
1787 FOR assignment_rec IN c_assignment_id(c_rec.action_information2
1788 ,p_contribution_period
1789 ,p_effective_start_date
1790 ,p_effective_end_date)
1791 LOOP
1792 pay_in_utils.set_location(g_debug,l_procedure, 50);
1793
1794 IF g_debug THEN
1795 pay_in_utils.trace('Pension Number ',assignment_rec.action_information15);
1796 pay_in_utils.trace('PF Organization ID ',c_rec.action_information2);
1797 END IF;
1798
1799 FOR chk_excluded_employee IN csr_not_excluded_employee(c_rec.action_information2
1800 ,p_contribution_period
1801 ,assignment_rec.action_information15)
1802 LOOP
1803 pay_in_utils.set_location(g_debug,l_procedure, 60);
1804 -- Display only when employee status is not excluded
1805 IF chk_excluded_employee.status ='1' THEN
1806 OPEN c_emp_name(c_rec.action_information2
1807 ,assignment_rec.action_information15
1808 ,p_effective_start_date
1809 ,p_effective_end_date );
1810
1811 FETCH c_emp_name INTO l_employee_name;
1812 CLOSE c_emp_name;
1813
1814 --Getting Contribution Type as of Date Earned (Bug 5647738)
1815 asg_sum := 0;
1816 l_cont_type_date_earned := 'DEFAULT';
1817 FOR c_mon_contr IN c_monthly_contributions(c_rec.action_information2
1818 ,assignment_rec.action_information15)
1819 LOOP
1820
1821 l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(assignment_rec.assignment_id
1822 ,l_bg_id
1823 ,c_mon_contr.date_earned
1824 ,'segment12');
1825 IF(l_cont_type_date_earned = '0')THEN
1826 l_cont_type_date_earned := 'DEFAULT';
1827 END IF;
1828 /*To get the ceiling limit depending on Disability information #7225734*/
1829 l_pf_ceiling_type := get_disability_details(assignment_rec.assignment_id,c_mon_contr.date_earned);
1830
1831 OPEN csr_global_value(l_pf_ceiling_type);
1832 FETCH csr_global_value INTO l_salary_ceiling ;
1833 CLOSE csr_global_value;
1834
1835 IF l_cont_type_date_earned = 'FULL_CAP' or l_cont_type_date_earned = 'DEFAULT' THEN
1836 asg_sum := asg_sum + LEAST(l_salary_ceiling,c_mon_contr.pf_wages);
1837 ELSE
1838 asg_sum := asg_sum + c_mon_contr.pf_wages;
1839 END IF;
1840
1841 END LOOP;
1842 FOR child_asg_rec IN c_asg_summation_details(c_rec.action_information2
1843 ,p_contribution_period
1844 ,assignment_rec.action_information15
1845 ,l_salary_ceiling)
1846 LOOP
1847 pay_in_utils.set_location(g_debug,l_procedure, 70);
1848 l_count:=1;
1849 --Sl No.
1850 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_sl_no';
1851 pay_in_xml_utils.gXMLTable(l_count).Value := (l_employee_no);
1852 l_employee_no := l_employee_no + 1;
1853 l_count := l_count + 1;
1854 --Pension Number
1855 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_account_no';
1856 pay_in_xml_utils.gXMLTable(l_count).Value := (assignment_rec.action_information15);
1857 l_count := l_count + 1;
1858 --Full Name
1859 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_name';
1860 pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_name ;
1861
1862 l_count := l_count + 1;
1863 IF g_debug THEN
1864 pay_in_utils.trace('Employee Name ',pay_in_xml_utils.gXMLTable(l_count).Value);
1865 END IF;
1866
1867
1868 pay_in_utils.set_location(g_debug,l_procedure, 80);
1869
1870 l_org_pf_ytd := l_org_pf_ytd + asg_sum ;
1871 l_org_pension_ytd := l_org_pension_ytd + child_asg_rec.pension;
1872
1873 --Annual Wages
1874 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf';
1875 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,NVL(asg_sum,0));
1876 l_count := l_count + 1;
1877 --Pension YTD
1878 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension';
1879 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.pension,0));
1880 l_count := l_count + 1;
1881
1882 IF g_debug THEN
1883 pay_in_utils.trace('PF Wages ',asg_sum);
1884 pay_in_utils.trace('Pension Amt ',child_asg_rec.pension);
1885 END IF;
1886
1887 l_remarks := NULL;
1888 l_remarks_dummy := NULL;
1889
1890 FOR c_rec_child IN c_asg_details(c_rec.action_information2 --PF Org ID
1891 ,assignment_rec.action_information15
1892 ,p_effective_start_date
1893 ,p_effective_end_date)
1894 LOOP
1895 pay_in_utils.set_location(g_debug,l_procedure, 90);
1896 l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
1897 IF (l_payroll_mon <4 ) THEN
1898 l_mon := l_payroll_mon + 9;
1899 ELSE
1900 l_mon := l_payroll_mon - 3;
1901 END IF;
1902 l_remarks_dummy := get_eit_remarks(assignment_rec.action_information15
1903 ,'PF'
1904 ,p_contribution_period
1905 ,l_mon
1906 );
1907
1908 IF l_remarks IS NOT NULL THEN
1909 IF l_remarks_dummy IS NOT NULL THEN
1910 l_remarks:=l_remarks||fnd_global.local_chr(10)||l_remarks_dummy;
1911 END IF;
1912 ELSE
1913 l_remarks:=l_remarks_dummy;
1914 END IF;
1915 END LOOP;
1916
1917
1918 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
1919 pay_in_xml_utils.gXMLTable(l_count).Value := (l_remarks);
1920 l_count := l_count + 1;
1921 pay_in_xml_utils.multiColumnar('details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
1922
1923 END LOOP;
1924 END IF;
1925 END LOOP;
1926 END LOOP;
1927
1928 pay_in_utils.set_location(g_debug,l_procedure, 100);
1929 pay_in_xml_utils.gXMLTable.delete;
1930
1931 l_count:=1;
1932 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
1933 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,NVL(l_org_pf_ytd,0));
1934 l_count := l_count + 1;
1935 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_ytd';
1936 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,NVL(l_org_pension_ytd,0));
1937 l_count := l_count + 1;
1938 pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
1939
1940 IF g_debug THEN
1941 pay_in_utils.trace('Total PF Wages ',l_org_pf_ytd);
1942 pay_in_utils.trace('Total Pension Amt ',l_org_pension_ytd);
1943 END IF;
1944
1945
1946 pay_in_xml_utils.gXMLTable.delete;
1947
1948 l_count:=1;
1949 l_epf_total:=0;
1950 l_pension_total:=0;
1951 l_dli_total:=0;
1952 l_admn_total:=0;
1953 l_edli_adm_total:=0;
1954 l_summation:=0;
1955
1956 pay_in_utils.set_location(g_debug,l_procedure, 110);
1957
1958 FOR c_pf_org_child_rec IN c_pf_employer(c_rec.action_information2 --PF Org ID
1959 ,p_effective_start_date
1960 ,p_effective_end_date)
1961 LOOP
1962
1963 pay_in_utils.set_location(g_debug,l_procedure, 120);
1964
1965 IF g_debug THEN
1966 pay_in_utils.trace('Month Number ', c_pf_org_child_rec.mon);
1967 pay_in_utils.trace('Pension ', c_pf_org_child_rec.pension);
1968 END IF;
1969
1970 IF (c_pf_employer%ROWCOUNT=1) THEN
1971 IF c_pf_org_child_rec.mon>1 THEN
1972 FOR i IN 1..(c_pf_org_child_rec.mon-1)
1973 LOOP
1974 insert_ch_record(i,g_xml_data);
1975 l_row_count:=l_row_count + 1;
1976 END LOOP;
1977 END IF;
1978 END IF;
1979
1980 insert_ch_record(c_pf_org_child_rec.mon
1981 ,g_xml_data
1982 ,c_pf_org_child_rec.pension);
1983
1984 l_row_count:=c_pf_org_child_rec.mon;
1985 l_pension_total:=l_pension_total+c_pf_org_child_rec.pension;
1986
1987 END LOOP;
1988
1989 l_row_count:=NVL(l_row_count,0);
1990 IF l_row_count<12 THEN
1991 FOR i IN l_row_count+1..12 LOOP
1992 insert_ch_record(i,g_xml_data);
1993 END LOOP;
1994 END IF;
1995
1996 pay_in_xml_utils.gXMLTable.delete;
1997
1998 l_count:=1;
1999 --Pension Fund Total
2000 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_org_total';
2001 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,l_pension_total);
2002 l_count := l_count + 1;
2003
2004 --No of employees in Form 8
2005 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
2006 pay_in_xml_utils.gXMLTable(l_count).Value := l_employee_no - 1;
2007 l_count := l_count + 1;
2008
2009 pay_in_utils.set_location(g_debug,l_procedure, 130);
2010
2011 --Employer Representative Signature
2012 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_employer';
2013 OPEN c_rep_name(p_contribution_period
2014 ,c_rec.action_information2
2015 ,p_effective_start_date
2016 ,p_effective_end_date);
2017 FETCH c_rep_name INTO pay_in_xml_utils.gXMLTable(l_count).Value;
2018 CLOSE c_rep_name;
2019 l_count := l_count + 1;
2020 pay_in_xml_utils.multiColumnar('pf_org_sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2021 /* Ending Starts here*/
2022 l_tag := '</organization>';
2023 dbms_lob.writeAppend(g_xml_data, LENGTH(l_tag), l_tag);
2024 pay_in_xml_utils.gXMLTable.DELETE;
2025 l_row_count:=NULL;
2026 pay_in_utils.set_location(g_debug,l_procedure, 140);
2027 END LOOP;
2028
2029 pay_in_utils.set_location(g_debug,l_procedure, 150);
2030
2031 l_tag := '</FORM8>';
2032 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2033
2034 fnd_file.put_line(fnd_file.log,'XML Created.');
2035 -- INSERT INTO temp_clob VALUES (g_xml_data);
2036
2037 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 160);
2038
2039
2040 END create_form8_xml;
2041
2042 /*Added for 7225734 */
2043 --------------------------------------------------------------------------
2044 -- --
2045 -- Name : get_disability_details --
2046 -- Type : FUNCTION --
2047 -- Access : Public --
2048 -- Description : This Function returns the PF wage ceiling limit --
2049 -- depending on whether the disabled employee --
2050 -- has met all the sucessfull criteria or not. --
2051 -- --
2052 -- Parameters : --
2053 -- IN : p_assignment_id VARCHAR2 --
2054 -- p_earn_date DATE --
2055 -- OUT : N/A --
2056 -- --
2057 -- Change History : --
2058 --------------------------------------------------------------------------
2059 -- Rev# Date Userid Description --
2060 --------------------------------------------------------------------------
2061 -- 115.0 12-Aug-2008 mdubasi Initial Version --
2062 --------------------------------------------------------------------------
2063 FUNCTION get_disability_details( p_assignment_id IN NUMBER
2064 ,p_earn_date IN DATE )
2065 RETURN varchar2
2066 IS
2067
2068 /*Cursor to get the disable proof details */
2069 CURSOR c_disable_proof is
2070 SELECT pdf.dis_information1
2071 FROM per_disabilities_f pdf,
2072 per_assignments_f paf
2073 WHERE paf.assignment_id = p_assignment_id
2074 AND paf.person_id = pdf.person_id
2075 AND p_earn_date BETWEEN paf.effective_start_date AND paf.effective_end_date
2076 AND p_earn_date BETWEEN pdf.effective_start_date AND pdf.effective_end_date;
2077
2078
2079 /*Cursor to get the employer classification details */
2080 CURSOR c_emplr_class is
2081 SELECT target.org_information3
2082 FROM per_assignments_f assign,
2083 hr_soft_coding_keyflex scl,
2084 hr_organization_information target
2085 WHERE assign.assignment_id = p_assignment_id
2086 AND p_earn_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
2087 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2088 AND scl.segment1 = target.organization_id
2089 AND target.org_information_context = 'PER_IN_INCOME_TAX_DF';
2090
2091 /*cursor to get the hire date details */
2092 CURSOR c_hire_date is
2093 SELECT service.date_start
2094 FROM per_assignments_f assign,
2095 per_periods_of_service service
2096 WHERE p_earn_date BETWEEN ASSIGN.effective_start_date AND assign.effective_end_date
2097 AND assign.assignment_id = p_assignment_id
2098 AND service.period_of_service_id (+)= assign.period_of_service_id;
2099
2100 l_disable_proof per_disabilities_f.dis_information1%TYPE;
2101 l_emplr_class hr_organization_information.org_information3%TYPE;
2102 l_hire_date date;
2103 l_ceiling_type varchar2(50);
2104
2105 --
2106 BEGIN
2107
2108 OPEN c_disable_proof;
2109 FETCH c_disable_proof INTO l_disable_proof;
2110 CLOSE c_disable_proof;
2111
2112 OPEN c_emplr_class;
2113 FETCH c_emplr_class INTO l_emplr_class;
2114 CLOSE c_emplr_class;
2115
2116 OPEN c_hire_date;
2117 FETCH c_hire_date INTO l_hire_date;
2118 CLOSE c_hire_date;
2119
2120 IF (l_disable_proof = 'Y' AND l_hire_date >= to_date('01/04/2008','dd/mm/yyyy')
2121 AND (l_emplr_class = 'NSCG' OR l_emplr_class = 'FIRM' OR l_emplr_class = 'OTHR')) THEN
2122 l_ceiling_type := 'IN_PF_DISABLED_SALARY_CEILING';
2123 ELSE
2124 l_ceiling_type := 'IN_PF_SALARY_CEILING';
2125 END IF;
2126
2127 RETURN l_ceiling_type;
2128 END get_disability_details;
2129
2130
2131 --------------------------------------------------------------------------
2132 -- --
2133 -- Name : employee_type --
2134 -- Type : FUNCTION --
2135 -- Access : Public --
2136 -- Description : This function checks whether the employee type of --
2137 -- current assignment is same as in the Concurrent --
2138 -- Program Parameter or not. --
2139 -- Parameters : --
2140 -- IN : p_assignment_id NUMBER --
2141 -- p_employee_type VARCHAR2 --
2142 -- p_effective_start_date DATE --
2143 -- p_effective_end_date DATE --
2144 -- OUT : N/A --
2145 -- --
2146 -- Change History : --
2147 --------------------------------------------------------------------------
2148 -- Rev# Date Userid Description --
2149 --------------------------------------------------------------------------
2150 -- 115.0 21-Feb-2005 aaagarwa Initial Version --
2151 -- 115.1 05-Mar-2005 aaagarwa Changed Handling of termination --
2152 -- 115.2 06-Mar-2005 aaagarwa Live data access removed --
2153 --------------------------------------------------------------------------
2154 FUNCTION employee_type(p_pf_number VARCHAR2
2155 ,p_employee_type VARCHAR2
2156 ,p_effective_start_date DATE
2157 ,p_effective_end_date DATE
2158 ,p_cp_pf_org_id VARCHAR2 DEFAULT NULL
2159 ,p_pf_org_id VARCHAR2 DEFAULT NULL
2160 ,p_status OUT NOCOPY VARCHAR2)
2161 RETURN BOOLEAN
2162 IS
2163
2164
2165 CURSOR c_transfer_check
2166 IS
2167 SELECT action_information2
2168 FROM pay_action_information paa
2169 WHERE paa.action_information_category='IN_PF_ASG'
2170 AND paa.action_context_type='AAP'
2171 AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2172 AND paa.action_information3=p_pf_number
2173 GROUP BY action_information2;
2174
2175 CURSOR c_transfer_status(pf_org_id VARCHAR2)
2176 IS
2177 SELECT 1
2178 FROM pay_action_information paa
2179 WHERE paa.action_information_category='IN_PF_ASG'
2180 AND paa.action_context_type='AAP'
2181 AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2182 AND paa.action_information3=p_pf_number
2183 and paa.action_information2=p_pf_org_id
2184 and TO_DATE(paa.action_information13,'DD-MM-YY')=
2185 (
2186 SELECT MAX(to_date(action_information13,'DD-MM-YY'))
2187 FROM pay_action_information paa
2188 WHERE paa.action_information_category='IN_PF_ASG'
2189 AND paa.action_context_type='AAP'
2190 AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2191 AND paa.action_information3=p_pf_number
2192 );
2193
2194 CURSOR c_person_id
2195 IS
2196 SELECT DISTINCT person_id
2197 FROM per_people_f
2198 WHERE per_information8 = p_pf_number
2199 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
2200
2201 CURSOR c_termination_check(p_person_id NUMBER)
2202 IS
2203 select '1'
2204 from per_periods_of_service
2205 where actual_termination_date between p_effective_start_date and p_effective_end_date
2206 and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
2207 FROM per_periods_of_service
2208 WHERE person_id = p_person_id
2209 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2210 )
2211 and person_id = p_person_id;
2212
2213 CURSOR c_last_pay_count
2214 IS
2215 SELECT COUNT(action_information2)
2216 FROM pay_action_information
2217 WHERE action_information_category ='IN_PF_ASG'
2218 AND action_information3=p_pf_number
2219 AND action_context_type='AAP'
2220 AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2221 AND TO_DATE(action_information13,'DD-MM-YY')=
2222 (
2223 SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2224 FROM pay_action_information paa
2225 WHERE paa.action_information_category='IN_PF_ASG'
2226 AND paa.action_context_type='AAP'
2227 AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2228 AND paa.action_information3=p_pf_number
2229 );
2230
2231 CURSOR c_last_pay_date
2232 IS
2233 SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2234 FROM pay_action_information paa
2235 WHERE paa.action_information_category='IN_PF_ASG'
2236 AND paa.action_context_type='AAP'
2237 AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2238 AND paa.action_information3=p_pf_number;
2239
2240 CURSOR c_final_check(p_pf_org_id NUMBER
2241 ,p_payroll_date DATE)
2242 IS
2243 SELECT 1 -- Modified for bug 4774108
2244 FROM per_assignments_f pea
2245 ,hr_soft_coding_keyflex hrscf
2246 ,per_people_f pep
2247 WHERE pea.person_id = pep.person_id
2248 AND pep.per_information8 = p_pf_number
2249 AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2250 AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
2251 AND hrscf.segment2=p_pf_org_id
2252 AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
2253 AND TO_DATE(TO_CHAR(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY')
2254 AND p_payroll_date BETWEEN TO_DATE(TO_CHAR(pep.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
2255 AND TO_DATE(TO_CHAR(pep.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
2256
2257 l_org_count NUMBER;
2258 l_reason VARCHAR2(3);
2259 l_pay_date DATE;
2260 l_person_id NUMBER;
2261 l_message VARCHAR2(255);
2262 l_procedure VARCHAR2(100);
2263
2264 BEGIN
2265
2266 g_debug := hr_utility.debug_enabled;
2267 l_procedure := g_package ||'employee_type';
2268 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2269
2270 IF g_debug THEN
2271 pay_in_utils.trace('**************************************************','********************');
2272 pay_in_utils.trace('PF Number ',p_pf_number);
2273 pay_in_utils.trace('Employee Type ',p_employee_type);
2274 pay_in_utils.trace('Effective Start Date ',p_effective_start_date);
2275 pay_in_utils.trace('Effective End Date ',p_effective_end_date);
2276 pay_in_utils.trace('CP PF Org id ',p_cp_pf_org_id);
2277 pay_in_utils.trace('PF Org id ',p_pf_org_id);
2278 pay_in_utils.trace('**************************************************','********************');
2279 END IF;
2280
2281 l_org_count:=0;
2282
2283 OPEN c_person_id;
2284 FETCH c_person_id INTO l_person_id;
2285 CLOSE c_person_id;
2286
2287 IF p_employee_type = 'TRANSFERRED' THEN
2288 pay_in_utils.set_location(g_debug,l_procedure, 20);
2289 /*This is for finding the Organization Change Count*/
2290 FOR c_rec IN c_transfer_check
2291 LOOP
2292 l_org_count:=l_org_count+1;
2293 END LOOP;
2294 pay_in_utils.set_location(g_debug,l_procedure, 20);
2295
2296
2297 IF l_org_count <2 THEN /*This means there were'nt any changes*/
2298 pay_in_utils.set_location(g_debug,l_procedure, 30);
2299 p_status:='CURRENT';
2300 RETURN FALSE;
2301 ELSIF p_cp_pf_org_id IS NOT NULL THEN /*There were some Organizational Changes*/
2302 pay_in_utils.set_location(g_debug,l_procedure, 40);
2303 OPEN c_transfer_status(p_cp_pf_org_id);
2304 FETCH c_transfer_status INTO l_reason;
2305 CLOSE c_transfer_status;
2306
2307
2308 IF l_reason IS NULL THEN --Thats when the last payroll archived didnt have the p_cp_pf_org_id
2309 pay_in_utils.set_location(g_debug,l_procedure, 50);
2310 p_status:='TRANSFERRED';
2311 RETURN TRUE;
2312 ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2313 --there can be multiple records. So we have to check for records count
2314 pay_in_utils.set_location(g_debug,l_procedure, 60);
2315 OPEN c_last_pay_count;
2316 FETCH c_last_pay_count INTO l_org_count;
2317 CLOSE c_last_pay_count;
2318 IF l_org_count = 1 THEN --This org was present at last payroll action
2319 pay_in_utils.set_location(g_debug,l_procedure, 70);
2320 p_status:='CURRENT';
2321 RETURN FALSE;
2322 ELSE
2323 pay_in_utils.set_location(g_debug,l_procedure, 80);
2324 l_reason:=NULL;
2325 OPEN c_last_pay_date;
2326 FETCH c_last_pay_date INTO l_pay_date;
2327 CLOSE c_last_pay_date; --Find the last archived payroll date
2328
2329 OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
2330 FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2331 CLOSE c_final_check;
2332 IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2333 pay_in_utils.set_location(g_debug,l_procedure, 90);
2334 p_status:='TRANSFERRED';
2335 RETURN TRUE;
2336 ELSE
2337 pay_in_utils.set_location(g_debug,l_procedure, 100);
2338 p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2339 RETURN FALSE;
2340 END IF;
2341 END IF;--End for l_org_count
2342 END IF;--End for l_reason being null
2343
2344 ELSE
2345 pay_in_utils.set_location(g_debug,l_procedure, 110);
2346 l_reason:=NULL;
2347 OPEN c_transfer_status(p_pf_org_id);
2348 FETCH c_transfer_status INTO l_reason;
2349 CLOSE c_transfer_status;
2350
2351 IF l_reason IS NULL THEN --Thats when the last payroll archived had the p_pf_org_id
2352 pay_in_utils.set_location(g_debug,l_procedure, 120);
2353 p_status:='TRANSFERRED';
2354 RETURN TRUE;
2355 ELSE --This means that employee is still attached to that PF org. Now in the last archived data
2356 --there can be multiple records. So we have to check for records count
2357 pay_in_utils.set_location(g_debug,l_procedure, 130);
2358 OPEN c_last_pay_count;
2359 FETCH c_last_pay_count INTO l_org_count;
2360 CLOSE c_last_pay_count;
2361 IF l_org_count < 2 THEN --This org was present at last payroll action
2362 pay_in_utils.set_location(g_debug,l_procedure, 140);
2363 p_status:='CURRENT';
2364 RETURN FALSE;
2365 ELSE
2366 pay_in_utils.set_location(g_debug,l_procedure, 150);
2367 l_reason:=NULL;
2368 OPEN c_last_pay_date;
2369 FETCH c_last_pay_date INTO l_pay_date;
2370 CLOSE c_last_pay_date; --Find the last archived payroll date
2371
2372 OPEN c_final_check(p_pf_org_id,l_pay_date);
2373 FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2374 CLOSE c_final_check;
2375
2376 IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2377 pay_in_utils.set_location(g_debug,l_procedure, 160);
2378 p_status:='TRANSFERRED';
2379 RETURN TRUE;
2380 ELSE
2381 pay_in_utils.set_location(g_debug,l_procedure, 170);
2382 p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2383 RETURN FALSE;
2384 END IF;
2385 END IF;
2386
2387 END IF;
2388 END IF;
2389 ELSIF p_employee_type = 'TERMINATED' THEN
2390 pay_in_utils.set_location(g_debug,l_procedure, 180);
2391 OPEN c_termination_check(l_person_id);
2392 FETCH c_termination_check INTO l_reason;
2393 CLOSE c_termination_check;
2394
2395
2396 IF l_reason IS NULL THEN
2397 pay_in_utils.set_location(g_debug,l_procedure, 190);
2398 RETURN FALSE;
2399 ELSE
2400 RETURN TRUE;
2401 END IF;
2402
2403 ELSIF p_employee_type = 'CURRENT' THEN
2404 pay_in_utils.set_location(g_debug,l_procedure, 200);
2405 OPEN c_termination_check(l_person_id);
2406 FETCH c_termination_check INTO l_reason;
2407 CLOSE c_termination_check;
2408
2409
2410 IF l_reason IS NULL THEN
2411 --The employee is not terminated, So find whether he is in that org or not
2412 pay_in_utils.set_location(g_debug,l_procedure, 210);
2413 IF p_cp_pf_org_id IS NOT NULL THEN
2414 OPEN c_transfer_status(p_cp_pf_org_id);
2415 FETCH c_transfer_status INTO l_reason;
2416 CLOSE c_transfer_status;
2417
2418 pay_in_utils.set_location(g_debug,l_procedure, 220);
2419
2420 IF l_reason IS NULL THEN
2421 pay_in_utils.set_location(g_debug,l_procedure, 230);
2422 p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2423 RETURN FALSE; -- Bug 4033745
2424 ELSE
2425 --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
2426 --So first checking for them.
2427 pay_in_utils.set_location(g_debug,l_procedure, 240);
2428 OPEN c_last_pay_count;
2429 FETCH c_last_pay_count INTO l_org_count;
2430 CLOSE c_last_pay_count;
2431
2432 IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
2433 pay_in_utils.set_location(g_debug,l_procedure, 250);
2434 p_status:='CURRENT';
2435 RETURN TRUE;
2436 ELSE
2437 pay_in_utils.set_location(g_debug,l_procedure, 260);
2438 l_reason:=NULL;
2439 OPEN c_last_pay_date;
2440 FETCH c_last_pay_date INTO l_pay_date;
2441 CLOSE c_last_pay_date; --Find the last archived payroll date
2442 OPEN c_final_check(p_cp_pf_org_id,l_pay_date);
2443 FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2444 CLOSE c_final_check;
2445 IF l_reason IS NULL THEN --p_cp_pf_org_id is not in SCL
2446 pay_in_utils.set_location(g_debug,l_procedure, 270);
2447 p_status:='TRANSFERRED';
2448 RETURN FALSE;
2449 ELSE
2450 pay_in_utils.set_location(g_debug,l_procedure, 280);
2451 p_status:='CURRENT';--p_cp_pf_org_id is in SCl
2452 RETURN TRUE;
2453 END IF;
2454 END IF;
2455 END IF;
2456 ELSE
2457 pay_in_utils.set_location(g_debug,l_procedure, 290);
2458 OPEN c_transfer_status(p_pf_org_id);
2459 FETCH c_transfer_status INTO l_reason;
2460 CLOSE c_transfer_status;
2461
2462 IF l_reason IS NULL THEN
2463 pay_in_utils.set_location(g_debug,l_procedure, 300);
2464 p_status:='TRANSFERRED'; --This is because on last day the pf org is not as parameter passed
2465 RETURN FALSE; -- Bug 4033745
2466 ELSE
2467 pay_in_utils.set_location(g_debug,l_procedure, 310);
2468 --This means that assignment is still having p_cp_pf_org_id. But there may be multiple records
2469 --So first checking for them.
2470 OPEN c_last_pay_count;
2471 FETCH c_last_pay_count INTO l_org_count;
2472 CLOSE c_last_pay_count;
2473 IF l_org_count = 1 THEN --This org was present at last payroll action as there were only 1 rec
2474 pay_in_utils.set_location(g_debug,l_procedure, 320);
2475 p_status:='CURRENT';
2476 RETURN TRUE;
2477 ELSE
2478 pay_in_utils.set_location(g_debug,l_procedure, 330);
2479 l_reason:=NULL;
2480 OPEN c_last_pay_date;
2481 FETCH c_last_pay_date INTO l_pay_date;
2482 CLOSE c_last_pay_date; --Find the last archived payroll date
2483
2484 OPEN c_final_check(p_pf_org_id,l_pay_date);
2485 FETCH c_final_check INTO l_reason;--Find the presence of c_pf_org_id in SCL
2486 CLOSE c_final_check;
2487 IF l_reason IS NULL THEN --p_pf_org_id is not in SCL
2488 pay_in_utils.set_location(g_debug,l_procedure, 330);
2489 p_status:='TRANSFERRED';
2490 RETURN FALSE;
2491 ELSE
2492 pay_in_utils.set_location(g_debug,l_procedure, 340);
2493 p_status:='CURRENT';--p_pf_org_id is in SCl
2494 RETURN TRUE;
2495 END IF;
2496 END IF;
2497 END IF;
2498 END IF;
2499 ELSE
2500 pay_in_utils.set_location(g_debug,l_procedure, 350);
2501 RETURN FALSE;
2502 END IF;
2503
2504 ELSE
2505 pay_in_utils.set_location(g_debug,l_procedure, 360);
2506 RETURN TRUE;
2507 END IF;
2508
2509 END;
2510
2511 --------------------------------------------------------------------------
2512 -- --
2513 -- Name : CREATE_FORM6A_XML --
2514 -- Type : PROCEDURE --
2515 -- Access : Public --
2516 -- Description : This procedure creates XML data for PF Form 6A --
2517 -- --
2518 -- Parameters : --
2519 -- IN : p_pf_org_id VARCHAR2 --
2520 -- p_effective_start_date DATE --
2521 -- p_effective_end_date DATE --
2522 -- OUT : N/A --
2523 -- --
2524 -- Change History : --
2525 --------------------------------------------------------------------------
2526 -- Rev# Date Userid Description --
2527 --------------------------------------------------------------------------
2528 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
2529 -- 115.1 21-Mar-2005 aaagarwa Modified for handling process separate-
2530 -- run. --
2531 -- 115.2 31-Mar-2005 aaagarwa Added the join for BG id --
2532 --------------------------------------------------------------------------
2533
2534 PROCEDURE create_form6a_xml(p_pf_org_id VARCHAR2
2535 ,p_effective_start_date DATE
2536 ,p_effective_end_date DATE
2537 ,p_contribution_period VARCHAR2)
2538 IS
2539 CURSOR c_pf_org_id(p_pf_org_id VARCHAR2
2540 ,p_effective_start_date DATE
2541 ,p_effective_end_date DATE
2542 ,p_contribution_period VARCHAR2
2543 )
2544 IS
2545 SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546 ,paa_pay.action_information3 --PF Org Reg Name
2547 ,paa_pay.action_information5 --Address
2548 ,paa_pay.action_information6 --Code
2549 ,paa_pay.action_information8 --PF Org Name
2550 FROM pay_action_information paa_asg
2551 ,pay_action_information paa_pay
2552 ,pay_assignment_actions paa
2553 ,hr_organization_units hou
2554 WHERE paa_asg.action_information_category='IN_PF_ASG'
2555 AND paa_pay.action_information_category='IN_PF_PAY'
2556 AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
2557 AND paa_pay.ACTION_CONTEXT_TYPE='PA'
2558 AND paa.assignment_action_id=paa_asg.action_context_id
2559 AND paa.payroll_action_id=paa_pay.action_context_id
2560 AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2561 AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
2562 AND paa_asg.action_information2 = paa_pay.action_information2
2563 AND paa_pay.action_information7 NOT IN ('EXEM')
2564 AND paa_asg.action_information3 IS NOT NULL
2565 AND paa_asg.action_information1=p_contribution_period
2566 AND paa_pay.action_information1=p_contribution_period
2567 AND hou.organization_id=paa_pay.action_information2
2568 AND hou.organization_id=paa_asg.action_information2
2569 AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
2570 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2571 ORDER BY paa_pay.action_information8 ASC;
2572
2573 CURSOR c_assignment_id(p_pf_org_id VARCHAR2
2574 ,p_effective_start_date DATE
2575 ,p_effective_end_date DATE
2576 ,p_contribution_period VARCHAR2
2577 )
2578 IS
2579 SELECT DISTINCT action_information3
2580 FROM pay_action_information
2581 WHERE action_information_category='IN_PF_ASG'
2582 AND action_information2 =p_pf_org_id --PF Organization ID
2583 AND action_information1 =p_contribution_period
2584 AND action_information13 BETWEEN p_effective_start_date --Payroll Date
2585 AND p_effective_end_date
2586 AND action_information3 IS NOT NULL
2587 ORDER BY action_information3 ASC;
2588
2589 CURSOR c_asg_summation_details(p_pf_org_id VARCHAR2
2590 ,p_pf_number VARCHAR2
2591 ,p_effective_start_date DATE
2592 ,p_effective_end_date DATE
2593 )
2594 IS
2595 SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
2596 ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
2597 ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
2598 ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
2599 FROM pay_action_information
2600 WHERE action_information2 = p_pf_org_id
2601 AND action_information3 = p_pf_number
2602 AND action_information1 = p_contribution_period
2603 AND action_information_id IN(
2604 SELECT MAX(action_information_id)
2605 FROM pay_action_information
2606 WHERE action_information2 = p_pf_org_id
2607 AND action_information3 = p_pf_number
2608 AND action_information1 = p_contribution_period
2609 GROUP BY TO_DATE('01'||substr(action_information13,3),'DD-MM-YYYY'))
2610 AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date
2611 AND p_effective_end_date;
2612
2613 CURSOR c_asg_details(p_pf_org_id VARCHAR2
2614 ,p_pf_number VARCHAR2
2615 ,p_effective_start_date DATE
2616 ,p_effective_end_date DATE
2617 )
2618 IS
2619 SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
2620 FROM pay_action_information
2621 WHERE action_information_category='IN_PF_ASG'
2622 AND action_information3 =p_pf_number
2623 AND action_information2 =p_pf_org_id --PF Organization ID
2624 AND action_information1 = p_contribution_period -- Bug 5231500
2625 AND action_information13 BETWEEN p_effective_start_date --Payroll Date
2626 AND p_effective_end_date
2627 ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') ASC;
2628
2629 CURSOR c_vol_rate(p_pf_org_id VARCHAR2
2630 ,p_pf_number VARCHAR2
2631 ,p_effective_start_date DATE
2632 ,p_effective_end_date DATE
2633 )
2634 IS
2635 SELECT action_information6 --Voluntary Contribution Rate
2636 ,TO_DATE(action_information13,'DD-MM-YYYY')
2637 FROM pay_action_information
2638 WHERE action_information_category='IN_PF_ASG'
2639 AND action_information3 = p_pf_number
2640 AND action_information2 = p_pf_org_id --PF Organization ID
2641 AND action_information1 = p_contribution_period -- Bug 5231500
2642 AND action_information13 BETWEEN p_effective_start_date --Payroll Date
2643 AND p_effective_end_date
2644 ORDER BY TO_DATE(action_information13,'DD-MM-YYYY') DESC;
2645
2646 CURSOR c_number_of_contr(p_pf_org_id VARCHAR2
2647 ,p_effective_start_date DATE
2648 ,p_effective_end_date DATE
2649 )
2650 IS
2651 SELECT count(DISTINCT action_information3)--assignment_id)
2652 FROM pay_action_information
2653 WHERE action_information_category='IN_PF_ASG'
2654 AND to_number(action_information6)>0
2655 AND action_information13 BETWEEN p_effective_start_date
2656 AND p_effective_end_date
2657 AND action_information1 = p_contribution_period -- Bug 5231500
2658 AND action_information2=p_pf_org_id;
2659
2660 CURSOR c_pf_employer(p_pf_org_id VARCHAR2
2661 ,p_effective_start_date DATE
2662 ,p_effective_end_date DATE
2663 ,i NUMBER
2664 )
2665 IS
2666 SELECT
2667 TO_NUMBER(org_information2) mon --Month Number
2668 ,SUM(fnd_number.canonical_to_number(org_information3)+fnd_number.canonical_to_number(org_information4)) epf
2669 ,SUM(fnd_number.canonical_to_number(org_information5)) pension --Pension Fund Contributions A/c No.10
2670 ,SUM(fnd_number.canonical_to_number(org_information6)) dli --DLI Contribution A/c No.21
2671 ,SUM(fnd_number.canonical_to_number(org_information7)) edli --EDLI ADM. Charges A/c No.22
2672 ,SUM(fnd_number.canonical_to_number(org_information8)) admin --Adm. Charges A/c No.2
2673 ,SUM(fnd_number.canonical_to_number(org_information6)+fnd_number.canonical_to_number(org_information7)+fnd_number.canonical_to_number(org_information8)) agg
2674 FROM hr_organization_information
2675 WHERE organization_id=p_pf_org_id
2676 AND ORG_INFORMATION_CONTEXT='PER_IN_PF_CHALLAN_INFO'
2677 AND org_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2678 AND org_information2=to_number(TO_CHAR(TO_DATE('01-03-2004','DD-MM-YYYY'),'MM'))+decode(i,0,9,-3+i)
2679 GROUP BY org_information2
2680 ORDER BY org_information2 ASC;
2681
2682
2683 CURSOR c_name(p_pf_org_id VARCHAR2
2684 ,p_pf_number VARCHAR2
2685 ,p_effective_start_date DATE
2686 ,p_effective_end_date DATE
2687 )
2688 IS
2689 SELECT action_information4 --Full Name
2690 ,assignment_id
2691 FROM pay_action_information
2692 WHERE action_information_category='IN_PF_ASG'
2693 AND action_information2 =p_pf_org_id --PF Organization ID
2694 AND action_information3 =p_pf_number --PF Number
2695 AND action_information1 = p_contribution_period -- Bug 5231500
2696 AND to_date(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
2697 ORDER BY to_date(action_information13,'DD-MM-YY') DESC;
2698
2699 --Cursor for employer Representative Signature
2700 CURSOR c_rep_name(p_contribution_period VARCHAR2
2701 ,p_pf_org_id VARCHAR2
2702 ,p_effective_start_date DATE
2703 ,p_effective_end_date DATE
2704 )
2705 IS
2706 SELECT paa_pay.action_information4 rep_name
2707 FROM pay_action_information paa_asg
2708 ,pay_action_information paa_pay
2709 ,pay_assignment_actions paa
2710 WHERE paa_asg.action_information_category ='IN_PF_ASG'
2711 AND paa_pay.action_information_category ='IN_PF_PAY'
2712 AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
2713 AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
2714 AND paa.assignment_action_id = paa_asg.action_context_id
2715 AND paa.payroll_action_id = paa_pay.action_context_id
2716 AND paa_pay.action_information7 NOT IN ('EXEM')
2717 AND paa_asg.action_information3 IS NOT NULL
2718 AND paa_asg.action_information1 = p_contribution_period
2719 AND paa_pay.action_information1 = p_contribution_period
2720 AND paa_pay.action_information2 = p_pf_org_id
2721 AND paa_asg.action_information2 = p_pf_org_id
2722 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2723 ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
2724
2725 l_count NUMBER;
2726 l_tag VARCHAR2(2000);
2727 l_rate NUMBER;
2728 l_employee_no NUMBER;
2729 l_remarks VARCHAR2(2000);
2730 l_remarks_dummy VARCHAR2(2000);
2731 l_row_count NUMBER;
2732 l_epf_total NUMBER;
2733 l_pension_total NUMBER;
2734 l_dli_total NUMBER;
2735 l_admn_total NUMBER;
2736 l_edli_adm_total NUMBER;
2737 l_summation NUMBER;
2738 l_epf NUMBER;
2739 l_pension NUMBER;
2740 l_dli NUMBER;
2741 l_admn NUMBER;
2742 l_edli_adm NUMBER;
2743 l_summ NUMBER;
2744 l_sys_date_time VARCHAR2(30);
2745 i NUMBER;
2746 l_bg_id NUMBER;
2747 l_org_pf_ytd NUMBER;
2748 l_org_employer NUMBER;
2749 l_org_employee NUMBER;
2750 l_org_pension NUMBER;
2751 l_payroll_mon NUMBER;
2752 l_length NUMBER;
2753 l_date DATE;
2754 l_mon NUMBER;
2755 l_asg_id NUMBER;
2756 l_message VARCHAR2(255);
2757 l_procedure VARCHAR2(100);
2758
2759
2760 BEGIN
2761 pay_in_xml_utils.gXMLTable.DELETE;
2762
2763 g_debug := hr_utility.debug_enabled;
2764 l_procedure := g_package ||'create_form6a_xml';
2765 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2766
2767 --
2768 fnd_file.put_line(fnd_file.log,'Creating the XML...');
2769 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
2770 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
2771 --
2772 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
2773
2774 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2775 l_tag := '<FORM6A>';
2776 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2777 fnd_file.put_line(fnd_file.log,'Started...');
2778 --
2779 fnd_file.put_line(fnd_file.log,'Creating XML for Employee Personal Details.');
2780 l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
2781 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
2782 --System Date
2783 l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
2784 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2785
2786 pay_in_utils.set_location(g_debug,l_procedure, 20);
2787
2788 FOR c_rec IN c_pf_org_id(p_pf_org_id
2789 ,p_effective_start_date
2790 ,p_effective_end_date
2791 ,p_contribution_period)
2792 LOOP
2793 pay_in_utils.set_location(g_debug,l_procedure, 30);
2794 l_org_pf_ytd := 0 ;
2795 l_org_employer := 0 ;
2796 l_org_employee := 0 ;
2797 l_org_pension := 0 ;
2798
2799 l_tag := '<organization>';
2800 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
2801 l_count:=1;
2802 --PF Org Reg Name
2803 --PF Org Name made in BLOCK
2804 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_org_name';
2805 pay_in_xml_utils.gXMLTable(l_count).Value := upper(c_rec.action_information3);
2806 l_count := l_count + 1;
2807 --Address
2808 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_address';
2809 pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information5);
2810 l_count := l_count + 1;
2811 --Code
2812 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_code';
2813 pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information6);
2814 l_count := l_count + 1;
2815
2816 IF g_debug THEN
2817 pay_in_utils.trace('PF Organization ',c_rec.action_information3);
2818 END IF;
2819
2820 --Statuory Rate of Contribution
2821 SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
2822 FROM FF_GLOBALS_F
2823 WHERE GLOBAL_NAME ='IN_EMPLOYEE_PF_PERCENT'
2824 AND LEGISLATION_CODE='IN'
2825 AND p_effective_start_date BETWEEN effective_start_date AND p_effective_end_date;
2826
2827 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_rate';
2828 pay_in_xml_utils.gXMLTable(l_count).Value := (l_rate);
2829 l_count := l_count + 1;
2830 --Starting Year
2831 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_start_year';
2832 pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_start_date,'YYYY'));
2833 l_count := l_count + 1;
2834 --Ending Year
2835 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_end_year';
2836 pay_in_xml_utils.gXMLTable(l_count).Value := (to_char(p_effective_end_date,'YYYY'));
2837 l_count := l_count + 1;
2838
2839 pay_in_utils.set_location(g_debug,l_procedure, 30);
2840
2841 --Number of people making Voluntary Contributions in that financial Year
2842 OPEN c_number_of_contr(c_rec.action_information2
2843 ,p_effective_start_date
2844 ,p_effective_end_date);
2845 FETCH c_number_of_contr INTO l_employee_no;
2846 CLOSE c_number_of_contr;
2847 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_number';
2848 pay_in_xml_utils.gXMLTable(l_count).Value := (l_employee_no);
2849 l_count := l_count + 1;
2850 pay_in_xml_utils.multiColumnar('org',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2851 l_employee_no:=1;
2852
2853 pay_in_utils.set_location(g_debug,l_procedure, 40);
2854
2855 FOR assignment_rec IN c_assignment_id(c_rec.action_information2
2856 ,p_effective_start_date
2857 ,p_effective_end_date
2858 ,p_contribution_period)
2859 LOOP
2860 pay_in_utils.set_location(g_debug,l_procedure, 50);
2861
2862 IF g_debug THEN
2863 pay_in_utils.trace('PF Number ',assignment_rec.action_information3);
2864 END IF;
2865
2866 FOR child_asg_rec IN c_asg_summation_details(c_rec.action_information2
2867 ,assignment_rec.action_information3
2868 ,p_effective_start_date
2869 ,p_effective_end_date)
2870 LOOP
2871
2872 l_count:=1;
2873 --Sl No.
2874 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_sl_no';
2875 pay_in_xml_utils.gXMLTable(l_count).Value := (l_employee_no);
2876 l_employee_no := l_employee_no + 1;
2877 l_count := l_count + 1;
2878 --PF Number
2879 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_account_no';
2880 pay_in_xml_utils.gXMLTable(l_count).Value := (assignment_rec.action_information3);
2881 l_count := l_count + 1;
2882 --Full Name
2883 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_name';
2884 OPEN c_name(c_rec.action_information2
2885 ,assignment_rec.action_information3
2886 ,p_effective_start_date
2887 ,p_effective_end_date );
2888
2889 FETCH c_name INTO pay_in_xml_utils.gXMLTable(l_count).Value,l_asg_id;
2890 CLOSE c_name;
2891 l_count := l_count + 1;
2892 --Annual Wages
2893 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf';
2894 pay_in_xml_utils.gXMLTable(l_count).Value :=
2895 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.pf_ytd,0));
2896 l_count := l_count + 1;
2897 --Total Employee PF Contribution YTD
2898 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf';
2899 pay_in_xml_utils.gXMLTable(l_count).Value :=
2900 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.employee,0));
2901 l_count := l_count + 1;
2902 --Employer Contribution Towards PF YTD
2903 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_diff';
2904 pay_in_xml_utils.gXMLTable(l_count).Value :=
2905 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.employer,0));
2906 l_count := l_count + 1;
2907 --Pension YTD
2908 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension';
2909 pay_in_xml_utils.gXMLTable(l_count).Value :=
2910 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(child_asg_rec.pension,0));
2911 l_count := l_count + 1;
2912
2913 l_org_pf_ytd := l_org_pf_ytd + child_asg_rec.pf_ytd;
2914 l_org_employer := l_org_employer + child_asg_rec.employer;
2915 l_org_employee := l_org_employee + child_asg_rec.employee;
2916 l_org_pension := l_org_pension + child_asg_rec.pension;
2917
2918 l_remarks := NULL;
2919 l_remarks_dummy := NULL;
2920
2921 pay_in_utils.set_location(g_debug,l_procedure, 50);
2922
2923 For c_rec_child IN c_asg_details(c_rec.action_information2 --PF Org ID
2924 ,assignment_rec.action_information3
2925 ,p_effective_start_date
2926 ,p_effective_end_date)
2927 LOOP
2928 pay_in_utils.set_location(g_debug,l_procedure, 60);
2929 l_payroll_mon := TO_NUMBER(TO_CHAR(c_rec_child.mon,'MM'));
2930 IF (l_payroll_mon <4 ) THEN
2931 l_mon := l_payroll_mon + 9;
2932 ELSE
2933 l_mon := l_payroll_mon - 3;
2934 END IF;
2935 l_remarks_dummy := get_eit_remarks(assignment_rec.action_information3--assignment_rec.assignment_id
2936 ,'PF'
2937 ,p_contribution_period
2938 ,l_mon
2939 );
2940
2941 IF l_remarks IS NOT NULL THEN
2942 IF l_remarks_dummy IS NOT NULL THEN
2943 l_remarks:=l_remarks||fnd_global.local_chr(10)||l_remarks_dummy;
2944 END IF;
2945 ELSE
2946 l_remarks:=l_remarks_dummy;
2947 END IF;
2948 END LOOP;
2949
2950 pay_in_utils.set_location(g_debug,l_procedure, 70);
2951
2952 OPEN c_vol_rate(c_rec.action_information2 --PF Org ID
2953 ,assignment_rec.action_information3
2954 ,p_effective_start_date
2955 ,p_effective_end_date
2956 );
2957 FETCH c_vol_rate INTO l_rate,l_date;
2958 CLOSE c_vol_rate;
2959
2960 l_rate:=to_number(l_rate,999.99);
2961 --VOLUNTARY RATE of Contribution
2962 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_voluntary_rate';
2963 pay_in_xml_utils.gXMLTable(l_count).Value := (l_rate);
2964 l_count := l_count + 1;
2965 --Pension YTD
2966 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_remarks';
2967 pay_in_xml_utils.gXMLTable(l_count).Value := (l_remarks);
2968 l_count := l_count + 1;
2969 pay_in_xml_utils.multiColumnar('details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2970 pay_in_utils.set_location(g_debug,l_procedure, 80);
2971 END LOOP;
2972 END LOOP;
2973 pay_in_utils.set_location(g_debug,l_procedure, 90);
2974 pay_in_xml_utils.gXMLTable.delete;
2975 l_count:=1;
2976 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_wage_ytd';
2977 pay_in_xml_utils.gXMLTable(l_count).Value :=
2978 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_org_pf_ytd,0));
2979 l_count := l_count + 1;
2980 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_ytd';
2981 pay_in_xml_utils.gXMLTable(l_count).Value :=
2982 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_org_employee,0));
2983 l_count := l_count + 1;
2984 pay_in_xml_utils.gXMLTable(l_count).Name := 'diff_ytd';
2985 pay_in_xml_utils.gXMLTable(l_count).Value :=
2986 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_org_employer,0));
2987 l_count := l_count + 1;
2988 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_ytd';
2989 pay_in_xml_utils.gXMLTable(l_count).Value :=
2990 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(l_org_pension,0));
2991 l_count := l_count + 1;
2992 pay_in_xml_utils.multiColumnar('sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
2993
2994 pay_in_xml_utils.gXMLTable.delete;
2995 l_count:=1;
2996 l_epf_total:=0;
2997 l_pension_total:=0;
2998 l_dli_total:=0;
2999 l_admn_total:=0;
3000 l_edli_adm_total:=0;
3001 l_summation:=0;
3002
3003 i := 0;
3004 pay_in_utils.set_location(g_debug,l_procedure, 200);
3005 WHILE i<12
3006 LOOP
3007 l_epf :=0;
3008 l_pension :=0;
3009 l_dli :=0;
3010 l_admn :=0;
3011 l_edli_adm:=0;
3012 l_summ :=0;
3013 FOR c_pf_org_child_rec IN c_pf_employer(c_rec.action_information2 --PF Org ID
3014 ,p_effective_start_date
3015 ,p_effective_end_date
3016 ,i)
3017 LOOP
3018 pay_in_utils.set_location(g_debug,l_procedure, 210);
3019 l_epf :=c_pf_org_child_rec.epf;
3020 l_pension :=c_pf_org_child_rec.pension;
3021 l_dli :=c_pf_org_child_rec.dli;
3022 l_admn :=c_pf_org_child_rec.admin;
3023 l_edli_adm :=c_pf_org_child_rec.edli;
3024 l_summ :=c_pf_org_child_rec.agg;
3025
3026 l_epf_total:=l_epf_total+l_epf;
3027 l_pension_total:=l_pension_total+l_pension;
3028 l_dli_total:=l_dli_total+l_dli;
3029 l_admn_total:=l_admn_total+l_admn;
3030 l_edli_adm_total:=l_edli_adm_total+l_edli_adm;
3031 l_summation:=l_summation+l_summ;
3032
3033 END LOOP;
3034 insert_record(i
3035 ,g_xml_data
3036 ,l_epf
3037 ,l_pension
3038 ,l_dli
3039 ,l_admn
3040 ,l_edli_adm
3041 ,l_summ);
3042 i:=i+1;
3043 END LOOP;
3044 pay_in_utils.set_location(g_debug,l_procedure, 220);
3045 pay_in_xml_utils.gXMLTable.delete;
3046 l_count:=1;
3047 --EPF Contributions Total
3048 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_epf_org_total';
3049 pay_in_xml_utils.gXMLTable(l_count).Value :=
3050 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_epf_total);
3051 l_count := l_count + 1;
3052 --Pension Fund Total
3053 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_org_total';
3054 pay_in_xml_utils.gXMLTable(l_count).Value :=
3055 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_pension_total);
3056 l_count := l_count + 1;
3057 --DLI Contribution Total
3058 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_dli_pf_org_total';
3059 pay_in_xml_utils.gXMLTable(l_count).Value :=
3060 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_dli_total);
3061 l_count := l_count + 1;
3062 --Adm.Charges Total
3063 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_admin_pf_total';
3064 pay_in_xml_utils.gXMLTable(l_count).Value :=
3065 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_admn_total);
3066 l_count := l_count + 1;
3067 --EDLI ADm.Charges Total
3068 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_edli_org_total';
3069 pay_in_xml_utils.gXMLTable(l_count).Value :=
3070 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_edli_adm_total);
3071 l_count := l_count + 1;
3072 --Col 5,6,7 Summation Aggregate
3073 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_total_annual';
3074 pay_in_xml_utils.gXMLTable(l_count).Value :=
3075 pay_us_employee_payslip_web.get_format_value(l_bg_id,l_summation);
3076 l_count := l_count + 1;
3077 --No of employees in Form 6A
3078 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_emp_no';
3079
3080 pay_in_utils.set_location(g_debug,l_procedure, 230);
3081
3082 SELECT COUNT(DISTINCT action_information3)
3083 INTO pay_in_xml_utils.gXMLTable(l_count).Value
3084 FROM pay_action_information
3085 WHERE action_information_category='IN_PF_ASG'
3086 AND action_information1 = p_contribution_period -- Bug 5231500
3087 AND action_information2 = c_rec.action_information2 --PF Organization ID
3088 AND action_information3 IS NOT NULL
3089 AND action_information13 BETWEEN p_effective_start_date --Payroll Date
3090 AND p_effective_end_date;
3091
3092 l_count := l_count + 1;
3093 --Employer Representative Signature
3094 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_employer';
3095 OPEN c_rep_name(p_contribution_period
3096 ,c_rec.action_information2
3097 ,p_effective_start_date
3098 ,p_effective_end_date);
3099 FETCH c_rep_name INTO pay_in_xml_utils.gXMLTable(l_count).Value;
3100 CLOSE c_rep_name;
3101 pay_in_utils.set_location(g_debug,l_procedure, 240);
3102 l_count := l_count + 1;
3103 pay_in_xml_utils.multiColumnar('pf_org_sum',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3104 /* Ending Starts here*/
3105 l_tag := '</organization>';
3106 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3107 pay_in_xml_utils.gXMLTable.delete;
3108 l_row_count:=null;
3109 END LOOP;
3110 pay_in_utils.set_location(g_debug,l_procedure, 240);
3111 l_tag := '</FORM6A>';
3112 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3113 fnd_file.put_line(fnd_file.log,'XML Created.');
3114
3115 END create_form6a_xml;
3116
3117 --------------------------------------------------------------------------
3118 -- --
3119 -- Name : CREATE_FORM3A_XML --
3120 -- Type : PROCEDURE --
3121 -- Access : Public --
3122 -- Description : This procedure creates XML data for PF Form3A --
3123 -- --
3124 -- Parameters : --
3125 -- IN : p_pf_org_id VARCHAR2 --
3126 -- p_pf_number VARCHAR2 --
3127 -- p_effective_start_date DATE --
3128 -- p_effective_end_date DATE --
3129 -- OUT : N/A --
3130 -- --
3131 -- Change History : --
3132 --------------------------------------------------------------------------
3133 -- Rev# Date Userid Description --
3134 --------------------------------------------------------------------------
3135 -- 115.0 01-Jan-2005 aaagarwa Initial Version --
3136 -- 115.1 21-Mar-2005 aaagarwa Modified for handling process separate-
3137 -- run. --
3138 -- 115.2 31-Mar-2005 aaagarwa Added the join for BG id --
3139 --------------------------------------------------------------------------
3140 PROCEDURE create_form3a_xml(p_pf_org_id VARCHAR2
3141 ,p_pf_number VARCHAR2
3142 ,p_contribution_period VARCHAR2
3143 ,p_employee_type VARCHAR2
3144 ,p_effective_start_date DATE
3145 ,p_effective_end_date DATE)
3146 IS
3147
3148 CURSOR c_distinct_org_id(p_pf_org_id VARCHAR2
3149 ,p_pf_number VARCHAR2
3150 ,p_contribution_period VARCHAR2
3151 ,p_employee_type VARCHAR2
3152 ,p_effective_start_date DATE
3153 ,p_effective_end_date DATE
3154 )
3155 IS
3156 SELECT DISTINCT paa_asg.action_information14 --PF Org Name
3157 ,paa_asg.action_information3 pf_num --PF Number
3158 ,paa_asg.action_information2 pf_org_id --PF Org ID
3159 FROM pay_action_information paa_asg
3160 ,pay_action_information paa_pay
3161 ,pay_assignment_actions paa
3162 ,hr_organization_units hou
3163 WHERE paa_asg.action_information_category='IN_PF_ASG'
3164 AND paa_pay.action_information_category='IN_PF_PAY'
3165 AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3166 AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3167 AND paa.assignment_action_id=paa_asg.action_context_id
3168 AND paa.payroll_action_id=paa_pay.action_context_id
3169 AND paa_pay.action_information7 NOT IN ('EXEM')
3170 AND paa_asg.action_information3 IS NOT NULL
3171 AND paa_asg.action_information1=p_contribution_period
3172 AND paa_pay.action_information1=p_contribution_period
3173 AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3174 AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175 AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176 AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177 AND paa_asg.action_information2 = paa_pay.action_information2
3178 AND hou.organization_id=paa_pay.action_information2
3179 AND hou.organization_id=paa_asg.action_information2
3180 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3181 ORDER BY paa_asg.action_information14,paa_asg.action_information3 asc;
3182
3183 CURSOR c_asg_id(p_pf_org_id VARCHAR2
3184 ,p_pf_number VARCHAR2
3185 ,p_effective_start_date DATE
3186 ,p_effective_end_date DATE
3187 ,p_employee_type VARCHAR2
3188 ,p_contribution_period VARCHAR2
3189 ,p_cp_pf_org_id VARCHAR2
3190 ,p_status VARCHAR2
3191 )
3192 IS
3193 SELECT DISTINCT paa_pay.action_information5 --Address
3194 ,paa_pay.action_information3 reg --Registered Name
3195 FROM pay_action_information paa_asg
3196 ,pay_action_information paa_pay
3197 ,pay_assignment_actions paa
3198 WHERE paa_asg.action_information_category='IN_PF_ASG'
3199 AND paa_pay.action_information_category='IN_PF_PAY'
3200 AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3201 AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3202 AND paa.assignment_action_id=paa_asg.action_context_id
3203 AND paa.payroll_action_id=paa_pay.action_context_id
3204 AND paa_pay.action_information7 NOT IN ('EXEM')
3205 AND paa_asg.action_information3 IS NOT NULL
3206 AND paa_asg.action_information1 = p_contribution_period
3207 AND paa_pay.action_information1 = p_contribution_period
3208 AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3209 AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3210 AND paa_asg.action_information3 = p_pf_number --PF Number
3211 AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3212 AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3213 AND nvl(p_status,-1) LIKE DECODE(p_employee_type,'CURRENT','CURRENT','TRANSFERRED','TRANSFERRED',-1)
3214 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date;
3215
3216 CURSOR c_name_fath_hus_name(p_pf_org_id VARCHAR2
3217 ,p_pf_number VARCHAR2
3218 ,p_effective_start_date DATE
3219 ,p_effective_end_date DATE
3220 ,p_contribution_period VARCHAR2
3221 )
3222 IS
3223 SELECT action_information4 --Full Name
3224 ,action_information5 --Father/Husband Name
3225 ,action_information13 --Payroll Date
3226 ,action_information6 --Voluntary Contribution Rate
3227 FROM pay_action_information
3228 WHERE action_information_category='IN_PF_ASG'
3229 AND action_information1 =p_contribution_period --PF Contribution Period
3230 AND action_information2 =p_pf_org_id --PF Organization ID
3231 AND action_information3 =p_pf_number --PF Number
3232 AND TO_DATE(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date AND p_effective_end_date
3233 ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
3234
3235
3236 CURSOR c_asg_details(p_pf_org_id VARCHAR2
3237 ,p_pf_number VARCHAR2
3238 ,p_effective_start_date DATE
3239 ,p_effective_end_date DATE
3240 ,p_contribution_period VARCHAR2
3241 ,p_mon_number NUMBER
3242 )
3243 IS
3244 SELECT paa_asg.action_information7 --PF Salary
3245 ,paa_asg.action_information8 --Total Employee Contr
3246 ,paa_asg.action_information9 --Employer Contr towards PF
3247 ,paa_asg.action_information10 --Employer Contr towards Pension
3248 ,paa_asg.action_information11 --Absence
3249 -- ,paa_asg.action_information12 --Remarks
3250 ,paa_pay.action_information4 --PF Rep Name
3251 ,paa_asg.action_information13 --Payroll Month
3252 ,paa_asg.assignment_id --Assignment ID
3253 FROM pay_action_information paa_asg
3254 ,pay_action_information paa_pay
3255 ,pay_assignment_actions paa
3256 WHERE paa_asg.action_information_category='IN_PF_ASG'
3257 AND paa_pay.action_information_category='IN_PF_PAY'
3258 AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3259 AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3260 AND paa.assignment_action_id=paa_asg.action_context_id
3261 AND paa.payroll_action_id=paa_pay.action_context_id
3262 AND paa_pay.action_information7 NOT IN ('EXEM')
3263 AND paa_asg.action_information3 IS NOT NULL
3264 AND paa_asg.action_information1=p_contribution_period
3265 AND paa_pay.action_information1=p_contribution_period
3266 AND paa_asg.action_information2=p_pf_org_id
3267 AND paa_asg.action_information3=p_pf_number
3268 AND paa_pay.action_information2=p_pf_org_id
3269 AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number
3270 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3271 ORDER BY to_date(paa_asg.action_information13,'DD-MM-YYYY'), fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
3272
3273 CURSOR c_asg_summation_details(p_pf_org_id VARCHAR2
3274 ,p_pf_number VARCHAR2
3275 ,p_effective_start_date DATE
3276 ,p_effective_end_date DATE
3277 ,p_contribution_period VARCHAR2
3278 )
3279 IS
3280 SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
3281 ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
3282 ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
3283 ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
3284 ,SUM(fnd_number.canonical_to_number(action_information8) + fnd_number.canonical_to_number(action_information9)) total --Total Employee Employer Contr
3285 FROM pay_action_information
3286 WHERE action_information2 = p_pf_org_id
3287 AND action_information3 = p_pf_number
3288 AND action_information1 = p_contribution_period
3289 AND action_information_id IN(
3290 SELECT MAX(action_information_id)
3291 FROM pay_action_information
3292 WHERE action_information2 = p_pf_org_id
3293 AND action_information3 = p_pf_number
3294 AND action_information1 = p_contribution_period
3295 GROUP BY to_date('01'||substr(action_information13,3),'DD-MM-YYYY'))
3296 AND to_date(action_information13,'DD-MM-YY') BETWEEN p_effective_start_date
3297 AND p_effective_end_date;
3298
3299 l_count NUMBER;
3300 l_tag VARCHAR2(240);
3301 l_voluntary_contribution_rate VARCHAR2(10);
3302 l_pf_rep_name VARCHAR2(240);
3303 l_month NUMBER;
3304 l_row_count NUMBER;
3305 l_month_name VARCHAR2(25);
3306 l_rate VARCHAR2(10);
3307 l_name VARCHAR2(240);
3308 l_fath_name VARCHAR2(240);
3309 l_date DATE;
3310 l_sys_date_time VARCHAR2(30);
3311 l_bg_id NUMBER;
3312 l_pf_salary_ptd VARCHAR2(200);
3313 l_epf VARCHAR2(200);
3314 l_epf_diff VARCHAR2(200);
3315 l_pension_fund VARCHAR2(200);
3316 l_absence VARCHAR2(200);
3317 l_remarks VARCHAR2(200);
3318 l_cp_pf_org_id VARCHAR2(20);
3319 l_status VARCHAR2(20);
3320 l_mon NUMBER;
3321 l_message VARCHAR2(255);
3322 l_procedure VARCHAR2(100);
3323
3324 BEGIN
3325 g_debug := hr_utility.debug_enabled;
3326 l_procedure := g_package ||'create_form3a_xml';
3327 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3328
3329 pay_in_xml_utils.gXMLTable.DELETE;
3330 --
3331 fnd_file.put_line(fnd_file.log,'Creating the XML...');
3332 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
3333 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
3334 --
3335 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
3336
3337 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3338 l_tag := '<FORM3A>';
3339 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3340 fnd_file.put_line(fnd_file.log,'Started...');
3341 --
3342 fnd_file.put_line(fnd_file.log,'Creating XML for Employee Personal Details.');
3343 l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
3344 --System Date
3345 l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
3346 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3347 l_cp_pf_org_id := p_pf_org_id;
3348
3349 pay_in_utils.set_location(g_debug,l_procedure, 20);
3350
3351 FOR c_org IN c_distinct_org_id (p_pf_org_id
3352 ,p_pf_number
3353 ,p_contribution_period
3354 ,p_employee_type
3355 ,p_effective_start_date
3356 ,p_effective_end_date
3357 )
3358 LOOP
3359 pay_in_utils.set_location(g_debug,l_procedure, 30);
3360
3361 IF g_debug THEN
3362 pay_in_utils.trace('p_effective_start_date ',p_effective_start_date);
3363 pay_in_utils.trace('p_effective_end_date ',p_effective_end_date);
3364 pay_in_utils.trace('p_employee_type ',p_employee_type);
3365 pay_in_utils.trace('c_org.pf_org_id ',c_org.pf_org_id);
3366 END IF;
3367
3368
3369 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
3370 l_status:=NULL;
3371 IF employee_type(c_org.pf_num
3372 ,p_employee_type
3373 ,p_effective_start_date
3374 ,p_effective_end_date
3375 ,l_cp_pf_org_id
3376 ,c_org.pf_org_id
3377 ,l_status)
3378 THEN
3379 pay_in_utils.set_location(g_debug,l_procedure, 40);
3380
3381 IF g_debug THEN
3382 pay_in_utils.trace('Status ',l_status);
3383 pay_in_utils.trace('Cp PF org id ',l_cp_pf_org_id);
3384 pay_in_utils.trace('PF org id ',c_org.pf_org_id);
3385 pay_in_utils.trace('PF Num ',c_org.pf_num );
3386 pay_in_utils.trace('p_employee_type ',p_employee_type );
3387 END IF;
3388
3389 FOR c_rec IN c_asg_id(c_org.pf_org_id --PF Org ID
3390 ,c_org.pf_num --PF Number
3391 ,p_effective_start_date
3392 ,p_effective_end_date
3393 ,p_employee_type
3394 ,p_contribution_period
3395 ,l_cp_pf_org_id
3396 ,l_status)
3397 LOOP
3398 pay_in_utils.set_location(g_debug,l_procedure, 50);
3399 OPEN c_name_fath_hus_name(c_org.pf_org_id --PF Org ID
3400 ,c_org.pf_num --PF Number
3401 ,p_effective_start_date
3402 ,p_effective_end_date
3403 ,p_contribution_period);
3404 FETCH c_name_fath_hus_name INTO l_name,l_fath_name,l_date,l_voluntary_contribution_rate;
3405 CLOSE c_name_fath_hus_name;
3406 l_count:=1;
3407 --PF Number
3408 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_number';
3409 pay_in_xml_utils.gXMLTable(l_count).Value := (c_org.pf_num);
3410 l_count := l_count + 1;
3411 --Employee name made in BLOCK
3412 --Full Name
3413 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_full_name';
3414 pay_in_xml_utils.gXMLTable(l_count).Value := upper(l_name);
3415 l_count := l_count + 1;
3416 --Father/Husband Name
3417 --Father/Husband name made in BLOCK
3418 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_fath_hus_name';
3419 pay_in_xml_utils.gXMLTable(l_count).Value := upper(l_fath_name);
3420 l_count := l_count + 1;
3421 --PF Org Reg Name
3422 --PF Org Registered name made in BLOCK
3423 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_registered_name';
3424 pay_in_xml_utils.gXMLTable(l_count).Value := upper(c_rec.reg);
3425 l_count := l_count + 1;
3426 --Address
3427 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_address';
3428 pay_in_xml_utils.gXMLTable(l_count).Value := (c_rec.action_information5);
3429 l_count := l_count + 1;
3430 l_tag := '<employee>';
3431 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3432 pay_in_xml_utils.multiColumnar('Details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
3433 l_count:=1;
3434 l_row_count:=0;
3435 --Individual Month Record Determination
3436 FOR i IN 3..12
3437 LOOP
3438 pay_in_utils.set_location(g_debug,l_procedure, 60);
3439 l_pf_salary_ptd :=0;
3440 l_epf :=0;
3441 l_epf_diff :=0;
3442 l_pension_fund :=0;
3443 l_absence :=0;
3444 l_remarks :=NULL;
3445
3446 FOR c_rec_child IN c_asg_details( c_org.pf_org_id --PF Org ID
3447 ,c_org.pf_num --PF Number
3448 ,p_effective_start_date
3449 ,p_effective_end_date
3450 ,p_contribution_period
3451 ,i)
3452 LOOP
3453 pay_in_utils.set_location(g_debug,l_procedure, 70);
3454 l_pf_salary_ptd :=c_rec_child.action_information7;
3455 l_epf :=c_rec_child.action_information8;
3456 l_epf_diff :=c_rec_child.action_information9;
3457 l_pension_fund :=c_rec_child.action_information10;
3458 l_absence :=c_rec_child.action_information11;
3459 -- l_remarks :=c_rec_child.action_information12;
3460 IF (i = 3) THEN
3461 l_mon := 12;
3462 ELSE
3463 l_mon := (i-3);
3464 END IF;
3465 l_remarks := get_eit_remarks(c_org.pf_num
3466 ,'PF'
3467 ,p_contribution_period
3468 ,l_mon
3469 );
3470
3471 IF c_rec_child.action_information4 IS NOT NULL THEN
3472 l_pf_rep_name := c_rec_child.action_information4;
3473 END IF;
3474
3475 END LOOP;
3476 insert_null_record(to_char(add_months(to_date('01-12-2003','DD-MM-YYYY'),i),'Mon')
3477 ,g_xml_data
3478 ,l_pf_salary_ptd
3479 ,l_epf
3480 ,l_epf_diff
3481 ,l_pension_fund
3482 ,l_absence
3483 ,l_remarks);
3484
3485 END LOOP;
3486 pay_in_utils.set_location(g_debug,l_procedure, 80);
3487
3488 FOR i IN 1..2
3489 LOOP
3490 l_pf_salary_ptd :=0;
3491 l_epf :=0;
3492 l_epf_diff :=0;
3493 l_pension_fund :=0;
3494 l_absence :=0;
3495 l_remarks :=NULL;
3496 pay_in_utils.set_location(g_debug,l_procedure, 90);
3497
3498 FOR c_rec_child IN c_asg_details(c_org.pf_org_id --PF Org ID
3499 ,c_org.pf_num --PF Number
3500 ,p_effective_start_date
3501 ,p_effective_end_date
3502 ,p_contribution_period
3503 ,i) LOOP
3504 l_pf_salary_ptd :=c_rec_child.action_information7;
3505 l_epf :=c_rec_child.action_information8;
3506 l_epf_diff :=c_rec_child.action_information9;
3507 l_pension_fund :=c_rec_child.action_information10;
3508 l_absence :=c_rec_child.action_information11;
3509 -- l_remarks :=c_rec_child.action_information12;
3510 l_remarks :=get_eit_remarks(c_org.pf_num
3511 ,'PF'
3512 ,p_contribution_period
3513 ,(i+9)
3514 );
3515
3516
3517 IF c_rec_child.action_information4 IS NOT NULL THEN
3518 l_pf_rep_name := c_rec_child.action_information4;
3519 END IF;
3520
3521 END LOOP;
3522 insert_null_record(TO_CHAR(ADD_MONTHS(TO_DATE('01-12-2003','DD-MM-YYYY'),i),'Mon')
3523 ,g_xml_data
3524 ,l_pf_salary_ptd
3525 ,l_epf
3526 ,l_epf_diff
3527 ,l_pension_fund
3528 ,l_absence
3529 ,l_remarks);
3530
3531 END LOOP;
3532
3533 pay_in_utils.set_location(g_debug,l_procedure, 100);
3534 --Voluntary Higher Contr Rate
3535 l_tag :=pay_in_xml_utils.getTag('c_voluntary_rate',to_number(l_voluntary_contribution_rate,999.99));
3536 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3537 --Employer Representative name
3538 l_tag :=pay_in_xml_utils.getTag('c_rep_name',l_pf_rep_name);
3539 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3540 FOR c_sum IN c_asg_summation_details(/*c_master.assignment_id --Assignment ID
3541 ,*/c_org.pf_org_id --PF Org ID
3542 ,c_org.pf_num --PF Number
3543 ,p_effective_start_date
3544 ,p_effective_end_date
3545 ,p_contribution_period)
3546 LOOP
3547 --PF Salary Annual Value
3548 l_tag :=pay_in_xml_utils.getTag('c_pf_salary_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.pf_ytd));
3549 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3550 -- Employee Total Contribution
3551 l_tag :=pay_in_xml_utils.getTag('c_epf_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.employee));
3552 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3553 -- Employer Total Contribution
3554 l_tag :=pay_in_xml_utils.getTag('c_epf_difference_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.employer));
3555 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3556 -- Pension
3557 l_tag :=pay_in_xml_utils.getTag('c_pension_fund_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.pension));
3558 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3559 --Total Employee Employer Contr
3560 l_tag :=pay_in_xml_utils.getTag('c_employer_employee_ytd',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_sum.total));
3561 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3562 END LOOP;
3563 pay_in_utils.set_location(g_debug,l_procedure, 110);
3564 --Current Date
3565 l_tag :=pay_in_xml_utils.getTag('c_current_date',to_char(SYSDATE,'DD-Mon-YYYY'));
3566 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3567 --Starting Year
3568 l_tag :=pay_in_xml_utils.getTag('c_start_year',to_char(p_effective_start_date,'YYYY'));
3569 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3570 --Ending Year
3571 l_tag :=pay_in_xml_utils.getTag('c_end_year',to_char(p_effective_end_date,'YYYY'));
3572 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3573 --Statuory Rate of Contribution
3574 SELECT ROUND(fnd_number.canonical_to_number(GLOBAL_VALUE)*100,2) INTO l_rate
3575 FROM FF_GLOBALS_F
3576 WHERE GLOBAL_NAME ='IN_EMPLOYEE_PF_PERCENT'
3577 AND LEGISLATION_CODE='IN'
3578 AND p_effective_start_date BETWEEN effective_start_date AND p_effective_end_date;
3579
3580 l_tag :=pay_in_xml_utils.getTag('c_stat_rate',to_number(l_rate,99.99));
3581 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3582 l_tag := '</employee>';
3583 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3584 pay_in_utils.set_location(g_debug,l_procedure, 120);
3585 END LOOP;
3586 END IF;
3587 END LOOP;
3588 --
3589 pay_in_utils.set_location(g_debug,l_procedure, 130);
3590 l_tag := '</FORM3A>';
3591 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3592 fnd_file.put_line(fnd_file.log,'XML Created.');
3593 END create_form3a_xml;
3594
3595
3596 --------------------------------------------------------------------------
3597 -- --
3598 -- Name : CREATE_FORM7_XML --
3599 -- Type : PROCEDURE --
3600 -- Access : Public --
3601 -- Description : This procedure creates XML data for Pension Form7 --
3602 -- --
3603 -- Parameters : --
3604 -- IN : p_pf_org_id VARCHAR2 --
3605 -- p_pension_number VARCHAR2 --
3606 -- p_employee_type VARCHAR2 --
3607 -- p_contribution_period VARCHAR2 --
3608 -- p_effective_start_date DATE --
3609 -- p_effective_end_date DATE --
3610 -- OUT : N/A --
3611 -- --
3612 -- Change History : --
3613 --------------------------------------------------------------------------
3614 -- Rev# Date Userid Description --
3615 --------------------------------------------------------------------------
3616 -- 115.0 08-Mar-2005 lnagaraj Initial Version --
3617 -- 115.1 09-Mar-2005 lnagaraj Removed unwanted comments --
3618 -- Modified Cursor csr_pf_org_details --
3619 -- and csr_month_contributions --
3620 -- 115.2 01-Apr-2005 lnagaraj Added csr_not_excluded_employee --
3621 --------------------------------------------------------------------------
3622 PROCEDURE create_form7_xml(p_pf_org_id VARCHAR2
3623 ,p_pension_number VARCHAR2
3624 ,p_employee_type VARCHAR2
3625 ,p_contribution_period VARCHAR2
3626 ,p_effective_start_date DATE
3627 ,p_effective_end_date DATE)
3628 IS
3629
3630 /* Cursor to find out the list of Archived Exempted PF Organizations */
3631 CURSOR csr_exempted_pf_orglist
3632 IS
3633 SELECT hou.organization_id orgid
3634 FROM hr_all_organization_units hou
3635 ,hr_organization_information hoi
3636 WHERE hou.organization_id = hoi.organization_id
3637 AND hoi.org_information_context ='PER_IN_PF_DF'
3638 AND hou.organization_id like nvl(p_pf_org_id,'%')
3639 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3640 AND EXISTS (SELECT 1
3641 FROM pay_action_information pai
3642 WHERE pai.action_information_category ='IN_PF_PAY'
3643 AND pai.action_information1 = p_contribution_period --Contribution period
3644 AND pai.action_information2 = hou.organization_id -- Org ID
3645 AND pai.action_information7 = 'EXEM' -- PF Org Classification
3646 AND ROWNUM <2)
3647 ORDER BY hou.name;
3648
3649 /* This finds the latest PF Organization data,in the contribution period from the archive table.All org level details will be found here .This will fire once foR EACH row returned by csr_exempted_pf_orglist*/
3650 CURSOR csr_pf_org_details (p_pf_org_id number)
3651 IS
3652 SELECT paa_pay.action_information3 Registered_Name
3653 ,paa_pay.action_information4 Rep_name
3654 ,paa_pay.action_information5 Address
3655 ,paa_pay.action_information6 Code
3656 FROM pay_action_information paa_asg
3657 ,pay_action_information paa_pay
3658 ,pay_assignment_actions paa
3659 WHERE paa_asg.action_information_category='IN_PF_ASG'
3660 AND paa_pay.action_information_category='IN_PF_PAY'
3661 AND paa_asg.action_context_type='AAP'
3662 AND paa_pay.action_context_type='PA'
3663 AND paa.assignment_action_id=paa_asg.action_context_id
3664 AND paa.payroll_action_id=paa_pay.action_context_id
3665 AND paa_pay.action_information7 = 'EXEM'
3666 AND paa_asg.action_information1=p_contribution_period
3667 AND paa_pay.action_information1=p_contribution_period
3668 AND paa_pay.action_information2=p_pf_org_id
3669 AND paa_asg.action_information2=p_pf_org_id
3670 AND paa_asg.action_information1=paa_pay.action_information1
3671 AND paa_asg.action_information2=paa_pay.action_information2
3672 AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3673 ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
3674
3675
3676
3677 /* Find the distinct pension number,assignment id combination for the given PF Org in the contribution period.Report will be generated for each record returned by the cursor. Rehire cases will not be handled */
3678 CURSOR csr_pension_number(l_pf_org_id NUMBER)
3679 IS
3680 SELECT DISTINCT pai.action_information15 pension_number
3681 FROM pay_action_information pai
3682 WHERE pai.action_information_category ='IN_PF_ASG'
3683 AND pai.action_information1 = p_contribution_period
3684 AND pai.action_information2 = l_pf_org_id
3685 AND pai.action_information15 IS NOT NULL
3686 AND pai.action_information15 LIKE NVL(p_pension_number,'%')
3687 ORDER BY pai.action_information15;
3688
3689 /* Cursor to find excluded employee status*/
3690 CURSOR csr_not_excluded_employee(p_pf_org_id VARCHAR2
3691 -- ,p_assignment_id NUMBER
3692 ,p_contribution_period VARCHAR2
3693 ,p_pension_number VARCHAR2)
3694 IS
3695 SELECT 'X' status
3696 FROM pay_action_information pai
3697 WHERE pai.action_information_category ='IN_PF_ASG'
3698 AND pai.action_information1=p_contribution_period
3699 AND pai.action_information2 = p_pf_org_id
3700 AND pai.action_information15 = p_pension_number
3701 AND NVL(pai.action_information18,'0') = '0'
3702 AND ROWNUM <2;
3703
3704 /*Find the Employee's name ,father's /husband's name and Hire Date of the employee.These
3705 data can change, though very rarely.Hire date is not static and may change in case of rehire
3706 or transfer.Latest data for the pension number in the given contribution period is retreived*/
3707
3708 CURSOR csr_employee_details(l_pension_number varchar2
3709 ,p_pf_org_id VARCHAR2
3710 )
3711 IS
3712 SELECT pai.action_information4 Emp_name
3713 ,pai.action_information5 Fath_husb_name
3714 ,pai.action_information16 Hire_date
3715 FROM pay_action_information pai
3716 WHERE pai.action_information_category ='IN_PF_ASG'
3717 AND pai.action_information1 = p_contribution_period
3718 AND pai.action_information2 = p_pf_org_id
3719 AND pai.action_information15 = l_pension_number
3720 ORDER BY TO_DATE(action_information13,'DD-MM-YY') DESC;
3721
3722
3723 /* This cursor gives the month-wise PF Wages,pension Contribution,Remarks,Absence,PF Contribution Salary*/
3724
3725 CURSOR csr_month_contributions(p_pf_org_id NUMBER
3726 ,p_pension_number VARCHAR2)
3727 IS
3728 SELECT pai.action_information7 pf_wages
3729 ,pai.action_information10 pension
3730 ,pai.action_information11 absence
3731 ,pai.action_information13 date_earned
3732 ,pai.action_information17 Contribution_sal
3733 ,pai.action_information18 Excluded_employee_status
3734 ,pai.action_information_id action_information_id
3735 ,pai.assignment_id assignment_id
3736 FROM pay_action_information pai
3737 WHERE pai.action_information_category ='IN_PF_ASG'
3738 AND pai.action_information1 = p_contribution_period
3739 AND pai.action_information2 = p_pf_org_id
3740 AND pai.action_information15 = p_pension_number
3741 AND pai.assignment_id IS NOT NULL
3742 GROUP BY pai.action_information13
3743 ,pai.action_information7
3744 ,pai.action_information10
3745 ,pai.action_information11
3746 ,pai.action_information17
3747 ,pai.action_information18
3748 ,pai.action_information_id
3749 ,pai.assignment_id
3750 HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
3751 FROM pay_Action_information pai1
3752 WHERE pai1.action_information_category ='IN_PF_ASG'
3753 AND pai1.action_information1 = p_contribution_period
3754 AND pai1.action_information2 = p_pf_org_id
3755 AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
3756 AND pai1.action_information15 = p_pension_number
3757 )
3758 ORDER BY TO_DATE(pai.action_information13,'DD-MM-YY'), pai.action_information_id desc;
3759
3760
3761 /* No need of a separate cursor to find annual values as this wont work when multiple records are present for an employee in a single month for a single Organization*/
3762
3763 /*Find the global value as on Financial year start */
3764 CURSOR csr_global_value(p_name VARCHAR2) IS
3765 SELECT fnd_number.canonical_to_number(glb.global_value)
3766 FROM ff_globals_f glb
3767 WHERE glb.global_name = p_name
3768 AND glb.LEGISLATION_CODE ='IN'
3769 AND p_effective_end_date BETWEEN glb.effective_start_date and glb.effective_end_date;
3770
3771
3772
3773 l_reg_name hr_organization_information.org_information1%TYPE;
3774 l_rep_name per_all_people_f.full_name%TYPE;
3775 l_org_address pay_action_information.action_information1%TYPE;
3776 --In the Rarest case of location adddress exceeding 240 characters nothing can be done
3777 l_est_code hr_organization_information.org_information1%TYPE;
3778
3779 g_org_XMLTable pay_in_xml_utils.tXMLTable;
3780
3781 l_employee_name per_all_people_f.full_name%TYPE;
3782 l_fath_husb_name per_all_people_f.full_name%TYPE;
3783 l_hire_date DATE;
3784 l_pension_number per_all_people_f.per_information13%TYPE;
3785
3786 l_bg_id NUMBER;
3787 p_cp_pf_org_id NUMBER;
3788 p_status VARCHAR2(30);
3789
3790 l_salary_ceiling NUMBER;
3791 l_eps_percent NUMBER;
3792 l_month_pf_wages NUMBER;
3793 l_total_pf_wages NUMBER;
3794 l_total_pension NUMBER;
3795 l_salary_exceed_ceiling VARCHAR2(5);
3796 l_excluded_employee_status varchar2(1);
3797
3798 l_org_count NUMBER;
3799 l_count NUMBER;
3800 l_row_count NUMBER;
3801 l_tag VARCHAR2(240);
3802 l_month NUMBER;
3803 l_month_name VARCHAR2(25);
3804 l_sys_date_time VARCHAR2(30);
3805 l_mon NUMBER;
3806 l_message VARCHAR2(255);
3807 l_procedure VARCHAR2(100);
3808 l_contribution_type VARCHAR2(20);
3809 l_cont_type VARCHAR2(20);
3810 l_cont_type_date_earned VARCHAR(20);
3811 l_pf_ceiling_type VARCHAR2(50);
3812
3813 BEGIN
3814 g_debug := hr_utility.debug_enabled;
3815 l_procedure := g_package ||'create_form7_xml';
3816 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3817
3818 pay_in_xml_utils.gXMLTable.DELETE;
3819
3820 --
3821 fnd_file.put_line(fnd_file.log,'Creating the XML...');
3822 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
3823 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
3824 --
3825 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
3826 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3827
3828 l_tag := '<FORM7>';
3829 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3830 fnd_file.put_line(fnd_file.log,'Started...');
3831 --
3832
3833 fnd_file.put_line(fnd_file.log,'Creating XML for Form7.');
3834
3835 --System Date
3836 l_sys_date_time := to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
3837 l_tag :=pay_in_xml_utils.getTag('c_current_date_in_hh_mm_ss',l_sys_date_time);
3838 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3839
3840
3841 OPEN csr_global_value('IN_EPS_PERCENT');
3842 FETCH csr_global_value INTO l_eps_percent ;
3843 CLOSE csr_global_value;
3844
3845 l_eps_percent := ROUND((l_eps_percent*100),2);
3846
3847 --
3848 -- Get the list of all exempted archived PF Organizations
3849 --
3850 p_cp_pf_org_id := p_pf_org_id;
3851 l_bg_id:= fnd_profile.value('PER_BUSINESS_GROUP_ID');
3852
3853
3854 fnd_file.put_line(fnd_file.log,'Start of Exempted Org list.');
3855 pay_in_utils.set_location(g_debug,l_procedure, 20);
3856
3857 FOR c_pf_orglist IN csr_exempted_pf_orglist LOOP
3858 --
3859 -- Get details in sequence for each of the above organization and store it in a PLSQL table . This will be
3860 -- used by each record returned by the inner query .i.e for each pension number
3861 -- that is attached to this PF org
3862 g_org_XMLTable.delete;
3863 pay_in_utils.set_location(g_debug,l_procedure, 30);
3864 OPEN csr_pf_org_details(c_pf_orglist.orgid);
3865 FETCH csr_pf_org_details INTO l_reg_name,l_rep_name,l_org_address,l_est_code;
3866 CLOSE csr_pf_org_details;
3867
3868 l_org_count :=1;
3869
3870 --PF Org Reg Name
3871 --PF Org Reg Name made in BLOCK
3872 g_org_XMLTable(l_org_count).Name := 'c_registered_name';
3873 g_org_XMLTable(l_org_count).Value := upper(l_reg_name);
3874 l_org_count := l_org_count + 1;
3875 --Employer Representative Name
3876 g_org_XMLTable(l_org_count).Name := 'c_employer';
3877 g_org_XMLTable(l_org_count).Value := l_rep_name;
3878 l_org_count := l_org_count + 1;
3879 --PF Establishment Code
3880 g_org_XMLTable(l_org_count).Name := 'c_code';
3881 g_org_XMLTable(l_org_count).Value := l_est_code;
3882 l_org_count := l_org_count + 1;
3883 --Address
3884 g_org_XMLTable(l_org_count).Name := 'c_address';
3885 g_org_XMLTable(l_org_count).Value := l_org_address;
3886 l_org_count := l_org_count +1;
3887 --
3888 -- Org Level Details End
3889 --
3890 fnd_file.put_line(fnd_file.log,'Start of all Pension number list.');
3891 FOR c_master_rec IN csr_pension_number(c_pf_orglist.orgid) LOOP
3892 -- Repeat this for all distinct (pension number,assignment id combinations . Rehire cases will not be
3893 -- considered.
3894 -- fnd_file.put_line(fnd_file.log,'Get details for the assignment id.'||c_master_rec.asg_id);
3895 pay_in_utils.set_location(g_debug,l_procedure, 40);
3896 IF (employee_type(c_master_rec.pension_number
3897 ,p_employee_type
3898 ,p_effective_start_date
3899 ,p_effective_end_date
3900 ,p_cp_pf_org_id -- Conc Program parameter
3901 ,c_pf_orglist.orgid
3902 ,p_status))
3903 THEN
3904 pay_in_utils.set_location(g_debug,l_procedure, 50);
3905 fnd_file.put_line(fnd_file.log,'Inside the Employee Type Check');
3906 OPEN csr_not_excluded_employee(c_pf_orglist.orgid
3907 ,p_contribution_period
3908 ,c_master_rec.pension_number);
3909 FETCH csr_not_excluded_employee INTO l_excluded_employee_status;
3910 CLOSE csr_not_excluded_employee;
3911
3912 IF (l_excluded_employee_status = 'X') THEN
3913 pay_in_utils.set_location(g_debug,l_procedure, 60);
3914 OPEN csr_employee_details(c_master_rec.pension_number,c_pf_orglist.orgid);
3915 FETCH csr_employee_details INTO l_employee_name,l_fath_husb_name,l_hire_date;
3916 CLOSE csr_employee_details;
3917
3918 l_tag := '<employee>';
3919 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
3920
3921
3922 -- Reset these variables for each pension number
3923 l_count :=1;
3924 l_row_count:=0;
3925 l_salary_exceed_ceiling :='No';
3926 l_total_pf_wages :=0;
3927 l_total_pension :=0;
3928 l_month_pf_wages :=0;
3929 l_excluded_employee_status :='';
3930 l_contribution_type := 'DEFAULT';
3931 l_cont_type := 'DEFAULT';
3932 l_cont_type_date_earned := 'DEFAULT';
3933
3934
3935 FOR c_rec_child IN csr_month_contributions(c_pf_orglist.orgid
3936 ,c_master_rec.pension_number
3937 )
3938 LOOP
3939 pay_in_utils.set_location(g_debug,l_procedure, 70);
3940 l_month := MONTHS_BETWEEN(TRUNC(to_date(c_rec_child.date_earned,'DD-MM-YY'),'MM')
3941 ,p_effective_start_date);
3942 --
3943 -- Add null records in case no records exists
3944 --
3945 l_cont_type_date_earned := 'DEFAULT';
3946 IF l_cont_type = 'DEFAULT' THEN
3947 l_cont_type :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
3948 ,l_bg_id
3949 ,GREATEST(l_hire_date,TO_DATE ('1-Apr-'||SUBSTR(p_contribution_period,1,4),'DD-MM-YY'))
3950 ,'segment12');
3951 IF(l_cont_type = '0')THEN
3952 l_cont_type := 'DEFAULT';
3953 END IF;
3954 END IF;
3955
3956 IF l_cont_type_date_earned = 'DEFAULT' THEN
3957 l_cont_type_date_earned :=pay_in_utils.get_scl_segment_on_date(c_rec_child.assignment_id
3958 ,l_bg_id
3959 ,c_rec_child.date_earned
3960 ,'segment12');
3961 IF(l_cont_type_date_earned = '0')THEN
3962 l_cont_type_date_earned := 'DEFAULT';
3963 END IF;
3964 END IF ;
3965
3966 IF (l_contribution_type ='DEFAULT') THEN
3967 IF (l_cont_type = 'DEFAULT') THEN
3968 l_contribution_type :=l_cont_type_date_earned;
3969 IF(l_contribution_type ='CAP_CAP' OR l_contribution_type ='FULL_CAP' OR l_contribution_type ='DEFAULT')THEN
3970 l_salary_exceed_ceiling := 'No';
3971 ELSE
3972 l_salary_exceed_ceiling := 'Yes';
3973 END IF;
3974 ELSE
3975 l_contribution_type :=l_cont_type;
3976 IF(l_cont_type = 'CAP_CAP' OR l_cont_type = 'FULL_CAP') THEN
3977 l_salary_exceed_ceiling := 'No';
3978 ELSE
3979 l_salary_exceed_ceiling := 'Yes';
3980 END IF;
3981 END IF;
3982 END IF;
3983
3984 IF (l_row_count < l_month) THEN
3985 --
3986 FOR i IN l_row_count+1..l_month LOOP
3987 l_row_count:=l_row_count+1;
3988 insert_null_form7_record(to_char(add_months(to_date('01-02-2004','DD-MM-YYYY'),i),'Mon'),g_xml_data);
3989 END LOOP;
3990 --
3991 END IF;
3992 IF l_row_count = l_month THEN
3993 --
3994 pay_in_utils.set_location(g_debug,l_procedure, 80);
3995 l_month_name:=c_rec_child.date_earned;
3996
3997 l_row_count:=l_row_count+1;
3998 l_month_pf_wages := TO_NUMBER(c_rec_child.pf_wages);
3999
4000 /*To get the Wage ceiling depending on disability details #7225734*/
4001 l_pf_ceiling_type := get_disability_details(c_rec_child.assignment_id,c_rec_child.date_earned);
4002
4003 OPEN csr_global_value(l_pf_ceiling_type);
4004 FETCH csr_global_value INTO l_salary_ceiling ;
4005 CLOSE csr_global_value;
4006
4007 --Added condition for Bug 5647738
4008 IF l_cont_type_date_earned = 'FULL_CAP' or l_cont_type_date_earned = 'DEFAULT' THEN
4009 l_month_pf_wages := LEAST(l_month_pf_wages,l_salary_ceiling);
4010 END IF;
4011
4012 l_mon := to_number(to_char(to_date(c_rec_child.date_earned,'DD-MM-YY'),'MM'));
4013
4014 IF (l_mon <4) THEN
4015 l_mon := l_mon + 9;
4016 ELSE
4017 l_mon := l_mon -3;
4018 END IF;
4019
4020 insert_null_form7_record(to_char(to_date(l_month_name,'DD-MM-YYYY'),'Mon')
4021 ,g_xml_data
4022 ,l_month_pf_wages
4023 ,c_rec_child.pension
4024 ,c_rec_child.absence
4025 ,get_eit_remarks(c_master_rec.pension_number,'PF',p_contribution_period,l_mon)
4026 );
4027
4028
4029 l_total_pf_wages := l_total_pf_wages + l_month_pf_wages;
4030 l_total_pension := l_total_pension + c_rec_child.pension;
4031
4032
4033
4034
4035
4036 --
4037 END IF;
4038 --
4039 END LOOP;
4040 fnd_file.put_line(fnd_file.log,'Monthwise details found for the pension number');
4041
4042
4043 IF l_row_count < 12 THEN
4044 FOR i IN 1..(12-l_row_count) LOOP
4045 insert_null_form7_record(to_char(add_months(to_date(l_month_name,'DD-MM-YYYY'),i),'Mon')
4046 ,g_xml_data);
4047 END LOOP;
4048 END IF;
4049
4050 -- Pension Number
4051 l_count:=1;
4052 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_number';
4053 pay_in_xml_utils.gXMLTable(l_count).Value := (c_master_rec.pension_number);
4054 l_count := l_count + 1;
4055
4056 --Employee Name made in BLOCK
4057 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_full_name';
4058 pay_in_xml_utils.gXMLTable(l_count).Value := upper(l_employee_name);
4059 l_count := l_count + 1;
4060
4061
4062 -- Father/Husband Name
4063 -- Father/Husband Name made in BLOCK
4064 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_fath_hus_name';
4065 pay_in_xml_utils.gXMLTable(l_count).Value := upper(l_fath_husb_name);
4066 l_count := l_count + 1;
4067
4068 -- Yes_or_no
4069 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_yes_no';
4070 pay_in_xml_utils.gXMLTable(l_count).Value := l_salary_exceed_ceiling;
4071 l_count := l_count + 1;
4072
4073 -- Pension Rate
4074 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_rate';
4075 pay_in_xml_utils.gXMLTable(l_count).Value := to_char(l_eps_percent);
4076 l_count := l_count + 1;
4077
4078 -- Salary Ceiling
4079 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_ceiling';
4080 pay_in_xml_utils.gXMLTable(l_count).Value := to_char(l_salary_ceiling);
4081 l_count := l_count + 1;
4082
4083 -- Annual PF Wages
4084 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pf_salary_ytd';
4085 pay_in_xml_utils.gXMLTable(l_count).Value := pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(to_char(l_total_pf_wages),0));
4086 l_count := l_count + 1;
4087 -- Annual Pension Contr
4088 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_pension_fund_ytd';
4089 pay_in_xml_utils.gXMLTable(l_count).Value :=
4090 pay_us_employee_payslip_web.get_format_value(l_bg_id,nvl(to_char(l_total_pension),0));
4091 l_count := l_count + 1;
4092
4093 -- Hire Date
4094 pay_in_xml_utils.gXMLTable(l_count).Name := 'c_hire_date';
4095 pay_in_xml_utils.gXMLTable(l_count).Value := to_char(l_hire_date,'DD-Mon-YYYY');
4096
4097 pay_in_xml_utils.multiColumnar('Details',pay_in_xml_utils.gXMLTable,l_count,g_xml_data);
4098 pay_in_xml_utils.multiColumnar('Organization',g_org_XMLTable,l_org_count,g_xml_data);
4099
4100 --Current Date
4101 l_tag :=pay_in_xml_utils.getTag('c_current_date',to_char(sysdate,'DD-Mon-YYYY'));
4102 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4103
4104 --Starting Year
4105 l_tag :=pay_in_xml_utils.getTag('c_start_year',to_char(p_effective_start_date,'YYYY'));
4106 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4107 --Ending Year
4108 l_tag :=pay_in_xml_utils.getTag('c_end_year',to_char(p_effective_end_date,'YYYY'));
4109 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4110
4111 l_tag := '</employee>';
4112 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4113 END IF;-- Excluded employee status end
4114 END IF; -- Employee Type Check End
4115
4116 END LOOP; -- End each pension number
4117 pay_in_utils.set_location(g_debug,l_procedure, 90);
4118 fnd_file.put_line(fnd_file.log,'XML Created for a PF Org.Moving onto next');
4119 END LOOP; -- End Each Organization
4120
4121 l_tag := '</FORM7>';
4122 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4123 fnd_file.put_line(fnd_file.log,'XML Created.');
4124 END create_form7_xml;
4125 --------------------------------------------------------------------------
4126 -- --
4127 -- Name : ESI_EMPLOYEE_TYPE --
4128 -- Type : PROCEDURE --
4129 -- Access : Private --
4130 -- Description : This procedure determines whether an assignment has --
4131 -- to be processed or not --
4132 -- Parameters : --
4133 -- IN : p_assignment_id NUMBER --
4134 -- p_org_id VARCHAR2 --
4135 -- p_contribution_period VARCHAR2 --
4136 -- p_sysdate DATE --
4137 -- p_esi_coverage VARCHAR2 --
4138 -- OUT : N/A --
4139 -- --
4140 -- Change History : --
4141 --------------------------------------------------------------------------
4142 -- Rev# Date Userid Description --
4143 --------------------------------------------------------------------------
4144 -- 115.0 10-Mar-2005 aaagarwa Initial Version --
4145 -- 115.1 24-Mar-2005 aaagarwa Report will be generated for all the --
4146 -- employees, if launched after a contri--
4147 -- -bution period end. --
4148 --------------------------------------------------------------------------
4149 FUNCTION esi_employee_type(--p_assignment_id NUMBER
4150 p_insurance_no VARCHAR2
4151 ,p_org_id VARCHAR2
4152 ,p_contribution_period VARCHAR2
4153 ,p_sysdate DATE
4154 ,p_esi_coverage VARCHAR2
4155 )
4156 RETURN BOOLEAN
4157 IS
4158 CURSOR c_transfer_check
4159 IS
4160 SELECT action_information2
4161 FROM pay_action_information paa
4162 WHERE paa.action_information_category='IN_ESI_ASG'
4163 AND paa.action_context_type='AAP'
4164 AND paa.action_information1=p_contribution_period
4165 AND paa.action_information3=p_insurance_no
4166 GROUP BY action_information2;
4167
4168 CURSOR c_transfer_status(p_esi_org_id VARCHAR2)
4169 IS
4170 SELECT 1
4171 FROM pay_action_information paa
4172 WHERE paa.action_information_category='IN_ESI_ASG'
4173 AND paa.action_context_type='AAP'
4174 AND paa.action_information1=p_contribution_period
4175 AND paa.action_information3=p_insurance_no
4176 and paa.action_information2=p_esi_org_id
4177 and to_date(paa.action_information11,'DD-MM-YY')=
4178 (
4179 SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4180 FROM pay_action_information paa
4181 WHERE paa.action_information_category='IN_ESI_ASG'
4182 AND paa.action_context_type='AAP'
4183 AND paa.action_information1=p_contribution_period
4184 AND paa.action_information3=p_insurance_no
4185 );
4186
4187 CURSOR c_last_pay_count
4188 IS
4189 SELECT count(action_information2)
4190 FROM pay_action_information
4191 WHERE action_information_category ='IN_ESI_ASG'
4192 AND action_information3=p_insurance_no
4193 AND action_context_type='AAP'
4194 AND action_information1=p_contribution_period
4195 AND to_date(action_information11,'DD-MM-YY')=
4196 (
4197 SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4198 FROM pay_action_information paa
4199 WHERE paa.action_information_category='IN_ESI_ASG'
4200 AND paa.action_context_type='AAP'
4201 AND paa.action_information1=p_contribution_period
4202 AND paa.action_information3=p_insurance_no
4203 );
4204
4205 CURSOR c_last_pay_date
4206 IS
4207 SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4208 FROM pay_action_information paa
4209 WHERE paa.action_information_category='IN_ESI_ASG'
4210 AND paa.action_context_type='AAP'
4211 AND paa.action_information1=p_contribution_period
4212 AND paa.action_information3=p_insurance_no;
4213
4214 CURSOR c_final_check(p_esi_org_id NUMBER
4215 ,p_payroll_date DATE)
4216 IS
4217 SELECT 1
4218 FROM per_assignments_f pea--Modified for bug 4774108
4219 ,per_people_f pep
4220 ,hr_soft_coding_keyflex hrscf
4221 WHERE pea.person_id = pep.person_id
4222 AND pep.per_information9 = p_insurance_no
4223 AND pep.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4224 AND pea.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
4225 AND hrscf.segment4=p_esi_org_id
4226 AND p_payroll_date BETWEEN to_date(to_char(pea.effective_start_date,'DD-MM-YY'),'DD-MM-YY')
4227 AND to_date(to_char(pea.effective_end_date,'DD-MM-YY'),'DD-MM-YY');
4228
4229
4230 CURSOR c_person_id
4231 IS
4232 SELECT DISTINCT person_id
4233 FROM per_people_f
4234 WHERE per_information8 = p_insurance_no
4235 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
4236
4237 CURSOR c_termination_check(p_person_id NUMBER
4238 ,p_effective_start_date DATE
4239 ,p_effective_end_date DATE)
4240 IS
4241 select nvl(actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'))
4242 from per_periods_of_service
4243 where actual_termination_date between p_effective_start_date and p_effective_end_date
4244 and date_start = (SELECT max(to_date(date_start,'DD-MM-YY'))
4245 FROM per_periods_of_service
4246 WHERE person_id = p_person_id
4247 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4248 )
4249 and person_id = p_person_id;
4250
4251 --Cursor to find date of death
4252 CURSOR c_death_date(p_start_date DATE
4253 ,p_end_date DATE)
4254 IS
4255 SELECT '1'
4256 FROM per_people_f
4257 WHERE person_id = (select distinct person_id
4258 from per_people_f
4259 where per_information9 = p_insurance_no
4260 and business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
4261 AND date_of_death BETWEEN p_start_date AND p_end_date;
4262
4263 --Find Exemption status
4264 CURSOR c_esi_exemption(p_payroll_date DATE)
4265 IS
4266 SELECT SUBSTR(action_information9,1,1)
4267 FROM pay_action_information
4268 WHERE action_information_category='IN_ESI_ASG'
4269 AND action_information3=p_insurance_no
4270 AND action_information2=p_org_id
4271 AND action_information1=p_contribution_period -- Bug 5231500
4272 AND to_date(action_information11,'DD-MM-YY')=p_payroll_date;
4273
4274 l_org_count NUMBER;
4275 l_reason VARCHAR2(3);
4276 l_term_date DATE;
4277 l_pay_date DATE;
4278 l_start_date DATE;
4279 l_end_date DATE;
4280 l_exem_status VARCHAR2(2);
4281 l_person_id NUMBER;
4282 l_message VARCHAR2(255);
4283 l_procedure VARCHAR2(100);
4284
4285 BEGIN
4286
4287 g_debug := hr_utility.debug_enabled;
4288 l_procedure := g_package ||'esi_employee_type';
4289 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4290
4291 OPEN c_person_id;
4292 FETCH c_person_id INTO l_person_id;
4293 CLOSE c_person_id;
4294
4295 --Checking for organization count
4296 l_org_count:= 0;
4297 l_reason := NULL;
4298 l_start_date :=to_date('01-'||SUBSTR(p_contribution_period,1,8),'DD-MM-YYYY');
4299 l_end_date :=add_months(to_date('01-'||SUBSTR(p_contribution_period,11),'DD-MM-YYYY'),1)-1;
4300
4301 pay_in_utils.set_location(g_debug,l_procedure, 20);
4302
4303 IF g_debug THEN
4304 pay_in_utils.trace('l_start_date ',l_start_date);
4305 pay_in_utils.trace('l_end_date ',l_end_date);
4306 END IF;
4307
4308 --Find the last payroll date
4309 OPEN c_last_pay_date;
4310 FETCH c_last_pay_date INTO l_pay_date;
4311 CLOSE c_last_pay_date;
4312
4313 --Find the Exemption Status from ESI at last payroll date
4314 OPEN c_esi_exemption(l_pay_date);
4315 FETCH c_esi_exemption INTO l_exem_status;
4316 CLOSE c_esi_exemption;
4317
4318 IF g_debug THEN
4319 pay_in_utils.trace('Exemption status ',l_exem_status);
4320 END IF;
4321
4322
4323 --Find date of death.
4324 OPEN c_death_date(l_start_date,l_end_date);
4325 FETCH c_death_date INTO l_reason;
4326 CLOSE c_death_date;
4327 IF l_reason IS NOT NULL THEN
4328 IF l_exem_status <> 'Y' THEN
4329 l_exem_status := 'Y';
4330 END IF;
4331 END IF;
4332 l_reason := NULL;
4333 --Termination check
4334
4335 pay_in_utils.set_location(g_debug,l_procedure, 30);
4336
4337 OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
4338 FETCH c_termination_check INTO l_term_date;
4339 CLOSE c_termination_check;
4340 IF l_term_date BETWEEN l_start_date AND l_end_date THEN
4341 IF l_exem_status <> 'Y' THEN
4342 l_exem_status := 'Y';
4343 END IF;
4344 END IF;
4345
4346
4347 IF g_debug THEN
4348 pay_in_utils.trace('Reason ',l_reason);
4349 pay_in_utils.trace('Termination ',l_term_date);
4350 pay_in_utils.trace('Sysdate ',p_sysdate);
4351 pay_in_utils.trace('p_esi_coverage ',p_esi_coverage);
4352 END IF;
4353
4354 --If sysdate is greater than end date of contribution period then data for all active, terminated
4355 --and transferred employee should be there.(Provided 3rd Pmt is left blank).
4356 IF p_sysdate > l_end_date THEN
4357 IF p_esi_coverage = '-1' THEN
4358 --This means its left blank. So we have to display the data for all employees
4359 RETURN TRUE;
4360 ELSIF p_esi_coverage = 'Y' THEN
4361 IF l_exem_status = 'Y' THEN
4362 RETURN TRUE;
4363 ELSE
4364 RETURN FALSE;
4365 END IF;
4366 ELSIF p_esi_coverage = 'N' THEN
4367 IF l_exem_status = 'Y' THEN
4368 RETURN FALSE;
4369 ELSIF l_exem_status = 'N' THEN
4370 RETURN TRUE;
4371 END IF;
4372 END IF;
4373 END IF;
4374 /*For the case when p_syadate is between start and end dates, we need to check further*/
4375
4376 IF p_esi_coverage = '-1' THEN
4377 l_exem_status:='-1';
4378 END IF;
4379 pay_in_utils.set_location(g_debug,l_procedure, 40);
4380
4381 --Checking for termination
4382 OPEN c_termination_check(l_person_id,l_start_date,l_end_date);
4383 FETCH c_termination_check INTO l_term_date;
4384 CLOSE c_termination_check;
4385
4386 IF (l_term_date BETWEEN l_start_date AND l_end_date) THEN
4387 IF p_esi_coverage = l_exem_status THEN /*True will be returned based on the 3rd parameter*/
4388 RETURN TRUE;-- Since the employee has a termination in the contribution period
4389 ELSE
4390 RETURN FALSE;
4391 END IF;
4392 END IF;
4393 pay_in_utils.set_location(g_debug,l_procedure, 50);
4394 --Checking for death
4395 OPEN c_death_date(l_start_date,l_end_date);
4396 FETCH c_death_date INTO l_reason;
4397 CLOSE c_death_date;
4398
4399 IF l_reason IS NOT NULL THEN
4400 IF p_esi_coverage = l_exem_status THEN /*True will be returned based on the 3rd parameter*/
4401 RETURN TRUE;-- Since the employee has expired in the contribution period
4402 ELSE
4403 RETURN FALSE;
4404 END IF;
4405 END IF;
4406 pay_in_utils.set_location(g_debug,l_procedure, 60);
4407
4408 FOR c_rec IN c_transfer_check
4409 LOOP
4410 l_org_count:=l_org_count+1;
4411 END LOOP;
4412 IF l_org_count <2 THEN /*This means there were'nt any changes, i.e the employee was there in the org thru out*/
4413 RETURN FALSE;
4414 END IF;
4415 pay_in_utils.set_location(g_debug,l_procedure, 70);
4416
4417 IF g_debug THEN
4418 pay_in_utils.trace('l_org_count ',l_org_count);
4419 pay_in_utils.trace('Org ID ',p_org_id);
4420 END IF;
4421
4422
4423 IF p_org_id IS NOT NULL THEN
4424 pay_in_utils.set_location(g_debug,l_procedure, 80);
4425 --Now checking for transfer cases.
4426 --The following cursor checks for the presence of p_org_id in the archived data
4427 OPEN c_transfer_status(p_org_id);
4428 FETCH c_transfer_status INTO l_reason;
4429 CLOSE c_transfer_status;
4430 -- hr_utility.set_location('Checking asg '||p_assignment_id||' for termination',7);
4431 IF l_reason IS NULL THEN -- This means that employee is no longer attached to that p_org_id
4432 pay_in_utils.set_location(g_debug,l_procedure, 90);
4433 IF l_exem_status=p_esi_coverage THEN
4434 RETURN TRUE; -- Hence we can safely return true and process that assignment.
4435 ELSE
4436 RETURN FALSE;
4437 END IF;
4438 ELSE
4439 pay_in_utils.set_location(g_debug,l_procedure, 100);
4440 l_reason := NULL;
4441 l_org_count := 0;
4442 -- hr_utility.set_location('Asg '||p_assignment_id||' not terminated. Checking for org counts',7);
4443 --Here we know that in last archived record employee was attached to p_org_id.
4444 --But there may be multiple records for last archival process.
4445 --So checking for no of records of last payroll.
4446 OPEN c_last_pay_count;
4447 FETCH c_last_pay_count INTO l_org_count;
4448 CLOSE c_last_pay_count ;
4449 -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count,7);
4450 IF l_org_count < 2 THEN
4451 --This means there is only one record and employee is attached to it only.
4452 --Hence he is not a separted employee. So returning false
4453 -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and is thus active emp',7);
4454 pay_in_utils.set_location(g_debug,l_procedure, 110);
4455 RETURN FALSE;
4456 ELSE
4457 -- hr_utility.set_location('Asg '||p_assignment_id||' has an org count of '||l_org_count||' and might be transferred',7);
4458 --Employee has multiple records for the last run.
4459 --Now we have to make sure that for which one he is transferred.
4460 --For this we are using the last payroll date and then we shall find the SCL Information.
4461 --Find the last payroll date
4462 pay_in_utils.set_location(g_debug,l_procedure, 120);
4463 OPEN c_last_pay_date;
4464 FETCH c_last_pay_date INTO l_pay_date;
4465 CLOSE c_last_pay_date;
4466
4467 hr_utility.set_location('Last Payroll Date'||l_pay_date,12);
4468
4469 OPEN c_final_check(p_org_id,l_pay_date);
4470 FETCH c_final_check INTO l_reason;
4471 CLOSE c_final_check ;
4472
4473 IF l_reason IS NULL AND l_exem_status=p_esi_coverage THEN
4474 pay_in_utils.set_location(g_debug,l_procedure, 130);
4475 --This means that SCL Information no longer has p_org_id.
4476 --Thus the employee has a transfer and we'll return true.
4477 RETURN TRUE ;
4478 ELSIF l_reason IS NOT NULL AND l_exem_status=p_esi_coverage THEN
4479 pay_in_utils.set_location(g_debug,l_procedure, 140);
4480 OPEN c_esi_exemption(l_pay_date);
4481 FETCH c_esi_exemption INTO l_exem_status;
4482 CLOSE c_esi_exemption;
4483 IF l_exem_status = 'Y' THEN
4484 RETURN TRUE;
4485 ELSE RETURN FALSE;
4486 END IF;
4487 ELSE
4488 pay_in_utils.set_location(g_debug,l_procedure, 150);
4489 RETURN FALSE;
4490 END IF;
4491 END IF;
4492 END IF;
4493 END IF;
4494 END esi_employee_type;
4495
4496 --------------------------------------------------------------------------
4497 -- --
4498 -- Name : CREATE_ESI_XML -
4499 -- Type : PROCEDURE --
4500 -- Access : Public --
4501 -- Description : This procedure creates XML filef ro ESI Form 6 --
4502 -- Parameters : --
4503 -- IN : p_esi_org_id VARCHAR2 --
4504 -- p_contribution_period VARCHAR2 --
4505 -- p_sysdate DATE --
4506 -- p_esi_coverage VARCHAR2 --
4507 -- OUT : N/A --
4508 -- --
4509 -- Change History : --
4510 --------------------------------------------------------------------------
4511 -- Rev# Date Userid Description --
4512 --------------------------------------------------------------------------
4513 -- 115.0 10-Mar-2005 aaagarwa Initial Version --
4514 -- 115.1 24-Mar-2005 aaagarwa Modified for remarks handling --
4515 -- 115.2 31-Mar-2005 aaagarwa Added the join for BG id --
4516 -- 115.3 16-Oct-2006 rpalli Bug 5599977: Added conversion factor --
4517 -- fnd_date.canonical_to_number in --
4518 -- cursors c_challan,c_asg_details --
4519 -- 115.4 05-Nov-2007 rsaharay Modified Cursor c_org_contr_days --
4520 -- to calculate No of Days for ESI Wage --
4521 -- correctly in case of Rehire before --
4522 -- FPD --
4523 -- 115.5 04-Sep-2008 rsaharay Modified for Form 5 --
4524 --------------------------------------------------------------------------
4525 PROCEDURE create_esi_xml(p_esi_org_id IN VARCHAR2 DEFAULT NULL
4526 ,p_contribution_period IN VARCHAR2
4527 ,p_esi_coverage IN VARCHAR2 DEFAULT NULL
4528 ,p_sysdate IN DATE DEFAULT NULL
4529 )
4530 IS
4531
4532 --DISTINCT Organization Id and Name
4533 CURSOR c_distinct_org
4534 IS
4535 SELECT DISTINCT action_information2 org_id
4536 ,action_information8 org_name
4537 FROM pay_action_information
4538 ,hr_organization_units hou
4539 WHERE action_information_category='IN_ESI_PAY'
4540 AND action_context_type='PA'
4541 AND action_information1=p_contribution_period
4542 AND action_information2 LIKE nvl(p_esi_org_id,'%')
4543 AND hou.organization_id=action_information2
4544 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4545 ORDER BY action_information8 asc;
4546
4547 --Select the highest payroll action id for an Org
4548 CURSOR c_max_pa_action_id(p_esi_org_id VARCHAR2)
4549 IS
4550 SELECT max(pai.action_context_id)
4551 FROM pay_action_information pai
4552 ,pay_assignment_actions pac
4553 WHERE pai.action_information_category='IN_ESI_PAY'
4554 AND pai.action_context_type='PA'
4555 AND pai.action_information1=p_contribution_period
4556 AND pai.action_information2=p_esi_org_id
4557 AND pac.payroll_action_id=pai.action_context_id
4558 AND pac.assignment_action_id in
4559 ( SELECT action_context_id
4560 FROM pay_action_information
4561 WHERE action_information_category='IN_ESI_ASG'
4562 AND action_context_type='AAP'
4563 AND action_information1=p_contribution_period
4564 AND action_information2=p_esi_org_id
4565 AND action_information11=
4566 ( SELECT max(to_date(action_information11,'DD-MM-YY'))
4567 FROM pay_action_information
4568 WHERE action_information_category='IN_ESI_ASG'
4569 AND action_context_type='AAP'
4570 AND action_information1=p_contribution_period
4571 AND action_information2=p_esi_org_id
4572 )
4573 );
4574
4575 --Organization Details based on payroll action id found in above CURSOR
4576 CURSOR c_org_details(p_payroll_action_id number
4577 ,p_esi_org_id VARCHAR2)
4578 IS
4579 SELECT action_information6 employer_code
4580 ,action_information3 registered_name
4581 ,action_information5 reg_address
4582 ,action_information4 rep_name
4583 ,action_information9 rep_desg
4584 ,action_information7 rep_addr
4585 ,action_information8 org_name
4586 FROM pay_action_information
4587 WHERE action_information_category='IN_ESI_PAY'
4588 AND action_context_type = 'PA'
4589 AND action_information1 = p_contribution_period
4590 AND action_information2 = p_esi_org_id
4591 AND action_context_id = p_payroll_action_id;
4592
4593 --DISTINCT Assignment Ids --Later to be changed
4594 CURSOR c_asg_id(p_esi_org_id VARCHAR2)
4595 IS
4596 SELECT DISTINCT action_information3 insurance_no
4597 FROM pay_action_information
4598 WHERE action_information_category='IN_ESI_ASG'
4599 AND action_context_type='AAP'
4600 AND action_information1=p_contribution_period
4601 AND action_information2=p_esi_org_id
4602 ORDER BY TO_NUMBER(action_information3) ASC;
4603
4604 CURSOR c_max_date(/*p_assignment_id NUMBER
4605 ,*/p_org_id NUMBER
4606 ,p_insurance_no VARCHAR2
4607 )
4608 IS
4609 SELECT max(to_date(action_information11,'DD-MM-YY')) maxdate
4610 FROM pay_action_information
4611 WHERE action_information_category='IN_ESI_ASG'
4612 AND action_context_type='AAP'
4613 AND action_information1=p_contribution_period
4614 AND action_information2=p_org_id
4615 AND action_information3=p_insurance_no
4616 GROUP BY assignment_id
4617 ORDER BY 1;
4618
4619 --Employee Details at the last archived payroll
4620 CURSOR c_asg_details(p_insurance_no VARCHAR2
4621 ,p_esi_org_id VARCHAR2
4622 ,p_payroll_date DATE)
4623 IS
4624 SELECT action_information4 full_name
4625 ,fnd_number.canonical_to_number(action_information6) actual_salary
4626 ,fnd_number.canonical_to_number(action_information7) employee_contr
4627 ,fnd_number.canonical_to_number(action_information8) employer_contr
4628 ,action_information9 esi_coverage
4629 ,action_information11 payroll_date
4630 FROM pay_action_information
4631 WHERE action_information_category='IN_ESI_ASG'
4632 AND action_context_type='AAP'
4633 AND action_information1=p_contribution_period
4634 AND action_information2=p_esi_org_id
4635 AND action_information3=p_insurance_no
4636 AND to_date(action_information11,'DD-MM-YY')=p_payroll_date
4637 ORDER BY to_date(action_information11,'DD-MM-YYYY') DESC ,fnd_number.canonical_to_number(action_information6)DESC;
4638
4639 --Cursor to find the absence details
4640 CURSOR c_absence(p_insurance_no VARCHAR2
4641 ,p_esi_org_id VARCHAR2)
4642 IS
4643 SELECT sum(nvl(action_information5,0)) absence
4644 FROM pay_action_information
4645 WHERE action_information_category='IN_ESI_ASG'
4646 AND action_context_type='AAP'
4647 AND action_information1=p_contribution_period
4648 AND action_information2=p_esi_org_id
4649 AND action_information3=p_insurance_no;
4650
4651 CURSOR c_person_id(p_insurance_no VARCHAR2)
4652 IS
4653 SELECT DISTINCT person_id
4654 FROM per_people_f
4655 WHERE per_information9 = p_insurance_no
4656 AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID');
4657 /*Bug 4282074*/
4658 CURSOR c_org_contr_days(p_esi_org_id VARCHAR2
4659 ,p_person_id NUMBER
4660 ,p_contr_start VARCHAR2
4661 ,p_contr_end VARCHAR2)
4662 IS
4663 SELECT TO_NUMBER
4664 (
4665 LEAST
4666 (NVL(pps.actual_termination_date,TO_DATE('31-12-4712','DD-MM-YYYY')),(ADD_MONTHS(TO_DATE('01-'||p_contr_end,'DD-MM-YYYY'),1)-1)
4667 )
4668 -
4669 GREATEST
4670 ( pps.date_start,TO_DATE('01-'||p_contr_start,'DD-MM-YYYY'))
4671 ) + 1 days
4672 FROM per_periods_of_service pps
4673 WHERE pps.period_of_service_id IN
4674 (SELECT asg.period_of_service_id
4675 FROM hr_organization_units hoi
4676 ,hr_soft_coding_keyflex scf
4677 ,per_all_assignments_f asg
4678 WHERE asg.person_id = p_person_id
4679 AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
4680 AND hoi.organization_id = scf.segment4
4681 AND hoi.organization_id = p_esi_org_id
4682 AND asg.primary_flag = 'Y' );
4683
4684 /*Bug 4282074*/
4685 CURSOR c_remarks(p_insurance_no VARCHAR2
4686 ,p_esi_org_id VARCHAR2)
4687 IS
4688 SELECT DISTINCT TO_DATE(action_information11,'DD-MM-YYYY') mon
4689 FROM pay_action_information
4690 WHERE action_information_category='IN_ESI_ASG'
4691 AND action_context_type='AAP'
4692 AND action_information1=p_contribution_period
4693 AND action_information2=p_esi_org_id
4694 AND action_information3=p_insurance_no
4695 ORDER BY TO_DATE(action_information11,'DD-MM-YYYY') ASC;
4696
4697 --Challan Information
4698 CURSOR c_challan(p_esi_org_id VARCHAR2)
4699 IS
4700 SELECT fnd_date.CANONICAL_TO_DATE(org_information2) challan_date
4701 ,fnd_number.canonical_to_number(org_information3) challan_amt
4702 ,org_information5 challan_bank_code
4703 ,org_information6 challan_branch_code
4704 ,hr_general.decode_lookup('IN_ESI_BANKS',org_information5) challan_bank
4705 ,hr_general.decode_lookup('IN_CALENDAR_MONTH',org_information7) month
4706 FROM hr_organization_information
4707 WHERE organization_id=p_esi_org_id
4708 AND ORG_INFORMATION_CONTEXT='PER_IN_ESI_CHALLAN_INFO'
4709 AND hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',ORG_INFORMATION1)=p_contribution_period
4710 ORDER BY fnd_date.CANONICAL_TO_DATE(org_information2) ASC;
4711
4712 --Declaration Information
4713 CURSOR c_declaration(p_esi_org_id VARCHAR2)
4714 IS
4715 SELECT
4716 TO_NUMBER(org_information1) declaration_forms
4717 ,TO_NUMBER(org_information2) tic
4718 ,TO_NUMBER(org_information3) pic_received
4719 ,TO_NUMBER(org_information4) pic_distributed
4720 ,TO_NUMBER(org_information5) accidents_reported
4721 ,TO_NUMBER(org_information6) direct_covered_employees
4722 ,org_information14 direct_covered_wages
4723 ,TO_NUMBER(org_information7) direct_not_covered_employees
4724 ,org_information8 direct_not_covered_wages
4725 ,TO_NUMBER(org_information9) immediate_employer_covered
4726 ,org_information10 wages_immediate_emplr_covered
4727 ,TO_NUMBER(org_information11) immediate_emplr_not_covered
4728 ,org_information12 wages_immd_emplr_not_covered
4729 FROM hr_organization_information
4730 WHERE organization_id = p_esi_org_id
4731 AND org_information_context = 'PER_IN_ESI_FORM5'
4732 AND hr_general.decode_lookup('IN_ESI_CONTRIB_PERIOD',org_information13) = p_contribution_period;
4733
4734
4735 --Elements computing the ESI Base Salary
4736 CURSOR c_elements
4737 IS
4738 SELECT
4739 NVL(pet.reporting_name, pet.element_name) element_name
4740 FROM
4741 pay_element_types_f pet,
4742 pay_input_values_f piv,
4743 pay_balance_feeds_f pbf,
4744 pay_balance_types pbt
4745 WHERE
4746 pbf.input_value_id = piv.input_value_id
4747 AND piv.element_type_id = pet.element_type_id
4748 AND pbt.balance_type_id = pbf.balance_type_id
4749 AND pbt.balance_name ='ESI Computation Salary'
4750 AND pet.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4751 AND p_sysdate BETWEEN pet.effective_start_date AND pet.effective_end_date
4752 AND p_sysdate BETWEEN piv.effective_start_date AND piv.effective_end_date
4753 AND p_sysdate BETWEEN pbf.effective_start_date AND pbf.effective_end_date;
4754
4755 --Cursor to get the Bank Branch Details
4756 CURSOR c_branch_dtls(p_bank_code VARCHAR2
4757 ,p_branch_code VARCHAR2 )
4758 IS
4759 SELECT hoi.org_information3 branch_name
4760 ,hoi.org_information4 branch_add
4761 FROM hr_organization_units hou
4762 ,hr_organization_information hoi
4763 WHERE hoi.organization_id = hou.organization_id
4764 AND hoi.org_information_context = 'PER_IN_ESI_BANK_BRANCH_DTLS'
4765 AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4766 AND hoi.org_information1 = p_bank_code
4767 AND hoi.org_information2 = p_branch_code ;
4768
4769
4770
4771 l_absence VARCHAR2(20);
4772 l_actual_salary VARCHAR2(20);
4773 l_employee_contr VARCHAR2(20);
4774 l_employer_contr VARCHAR2(20);
4775 l_esi_coverage VARCHAR2(10);
4776 l_remarks VARCHAR2(1000);
4777 l_remarks_dummy VARCHAR2(1000);
4778 l_payroll_date VARCHAR2(30);
4779 l_sys_date_time VARCHAR2(40);
4780 l_contr_start VARCHAR2(25);
4781 l_contr_end VARCHAR2(25);
4782 l_count NUMBER;
4783 l_days NUMBER;--VARCHAR2(50);
4784 l_avg_daily_wage NUMBER;
4785 l_employer_total NUMBER;
4786 l_employee_total NUMBER;
4787 l_challan_tot NUMBER;
4788 l_days_tot NUMBER;
4789 l_wages_tot NUMBER;
4790 l_con_tot NUMBER;
4791 l_xml_data CLOB;
4792 flag BOOLEAN;
4793 l_bg_id NUMBER;
4794 l_payroll DATE;
4795 l_tag VARCHAR2(1000);
4796 l_payroll_act_id NUMBER;
4797 l_employer_code hr_organization_information.org_information1%TYPE;
4798 l_registered_name hr_organization_units.name%TYPE;
4799 l_reg_address VARCHAR2(240);
4800 l_rep_name per_all_people_f.full_name%TYPE;
4801 l_rep_desg per_all_positions.name%TYPE;
4802 l_rep_addr VARCHAR2(240);
4803 l_org_name hr_organization_units.name%TYPE;
4804 l_person_id NUMBER;
4805 l_insurance_no per_all_people_f.per_information9%TYPE;
4806 l_full_name per_all_people_f.full_name%TYPE;
4807 l_payroll_mon NUMBER;
4808 l_length NUMBER;
4809 l_mon NUMBER;
4810 l_message VARCHAR2(255);
4811 l_procedure VARCHAR2(100);
4812 l_bank_branch_name VARCHAR2(200);
4813 l_bank_branch_address VARCHAR2(200);
4814 l_declaration_forms NUMBER;
4815 l_tic NUMBER;
4816 l_pic_received NUMBER;
4817 l_pic_distributed NUMBER;
4818 l_accidents_reported NUMBER;
4819 l_direct_covered_employees NUMBER;
4820 l_direct_not_covered_employees NUMBER;
4821 l_direct_not_covered_wages VARCHAR2(15);
4822 l_direct_covered_wages VARCHAR2(15);
4823 l_immediate_employer_covered NUMBER;
4824 l_wages_immediate_emplr_cover VARCHAR2(15);
4825 l_immediate_emplr_not_covered NUMBER;
4826 l_wages_immd_emplr_not_covered VARCHAR2(15);
4827
4828
4829
4830 BEGIN
4831 --
4832 g_debug := hr_utility.debug_enabled;
4833 l_procedure := g_package ||'create_esi_xml';
4834 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4835
4836 pay_in_utils.trace('p_esi_org_id',p_esi_org_id);
4837 pay_in_utils.trace('p_contribution_period',p_contribution_period);
4838 pay_in_utils.trace('p_esi_coverage',p_esi_coverage);
4839 pay_in_utils.trace('p_sysdate',p_sysdate);
4840
4841 l_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
4842 fnd_file.put_line(fnd_file.log,'Creating the XML...');
4843 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
4844 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
4845 --
4846 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
4847 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4848 l_tag := '<ESI6>';
4849 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4850 fnd_file.put_line(fnd_file.log,'Started...');
4851 --
4852 fnd_file.put_line(fnd_file.log,'Creating XML for ESI Form 6.');
4853 l_sys_date_time:=to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
4854 --System Date:
4855 l_tag :=pay_in_xml_utils.getTag('c_sys_date_in_hh_mm_ss',l_sys_date_time);
4856 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
4857 pay_in_utils.set_location(g_debug,l_procedure, 20);
4858
4859 FOR c_rec IN c_distinct_org
4860 LOOP
4861
4862 flag := TRUE;
4863 OPEN c_max_pa_action_id(c_rec.org_id);
4864 FETCH c_max_pa_action_id INTO l_payroll_act_id;
4865 CLOSE c_max_pa_action_id;
4866
4867 OPEN c_org_details(l_payroll_act_id,c_rec.org_id);
4868 FETCH c_org_details INTO l_employer_code
4869 ,l_registered_name
4870 ,l_reg_address
4871 ,l_rep_name
4872 ,l_rep_desg
4873 ,l_rep_addr
4874 ,l_org_name;
4875 CLOSE c_org_details;
4876 --Starting Writing the data for this org on to the local clob varaible
4877 dbms_lob.createtemporary(l_xml_data,FALSE,DBMS_LOB.CALL);
4878 dbms_lob.open(l_xml_data,dbms_lob.lob_readwrite);
4879 l_tag :='<organization>';
4880 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4881 l_tag :=pay_in_xml_utils.getTag('c_employer_code',l_employer_code);
4882 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4883 --Organization Name made in BLOCK
4884 l_tag :=pay_in_xml_utils.getTag('c_registered_name',upper(l_registered_name));
4885 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4886 l_tag :=pay_in_xml_utils.getTag('c_address',l_reg_address);
4887 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4888 --Organization Rep Name made in BLOCK
4889 l_tag :=pay_in_xml_utils.getTag('c_rep_name',upper(l_rep_name));
4890 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4891 l_tag :=pay_in_xml_utils.getTag('c_rep_desg',l_rep_desg);
4892 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4893 l_tag :=pay_in_xml_utils.getTag('c_rep_address',l_rep_addr);
4894 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4895 l_tag :=pay_in_xml_utils.getTag('c_local_office',l_org_name);
4896 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4897
4898 --Organization Specific Data written on the CLOB. Finding Contribution Periods
4899 l_contr_start := substr(p_contribution_period,1,8);
4900 l_contr_end := substr(p_contribution_period,11);
4901 l_tag :=pay_in_xml_utils.getTag('c_contr_start',l_contr_start);
4902 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4903 l_tag :=pay_in_xml_utils.getTag('c_contr_end',l_contr_end);
4904 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
4905 --Details of Contribution Period written to the CLOB.Finding Employee Details
4906 l_count := 1;
4907 l_employer_total := 0;
4908 l_employee_total := 0;
4909 l_days_tot := 0;
4910 l_wages_tot := 0;
4911 l_con_tot := 0;
4912
4913
4914 pay_in_utils.set_location(g_debug,l_procedure, 30);
4915
4916 FOR c_chd_asg IN c_asg_id(c_rec.org_id)
4917 LOOP
4918 -- l_asg_id := c_chd_asg.assignment_id;
4919 l_insurance_no := c_chd_asg.insurance_no;
4920
4921 -- check the assignment
4922 pay_in_utils.set_location(g_debug,l_procedure, 40);
4923 l_esi_coverage := NVL(p_esi_coverage,'-1');
4924 IF esi_employee_type(l_insurance_no,c_rec.org_id,p_contribution_period,p_sysdate,l_esi_coverage)
4925 THEN
4926 pay_in_utils.set_location(g_debug,l_procedure, 50);
4927
4928 IF g_debug THEN
4929 pay_in_utils.trace('ESI Insurance Number',l_insurance_no);
4930 END IF;
4931
4932 l_esi_coverage := NULL;
4933 --Assignment Details
4934 --Find the max payroll date for each assignment for given insurance number
4935 l_actual_salary := 0;
4936 l_employee_contr := 0;
4937 l_employer_contr := 0;
4938
4939 FOR c_date IN c_max_date(c_rec.org_id,l_insurance_no)
4940 LOOP
4941 IF g_debug THEN
4942 pay_in_utils.trace('ESI Form payroll max date',c_date.maxdate);
4943 pay_in_utils.trace('ESI Exemption',p_esi_coverage);
4944 END IF;
4945
4946 pay_in_utils.set_location(g_debug,l_procedure, 60);
4947 FOR child_asg IN c_asg_details(l_insurance_no,c_rec.org_id,c_date.maxdate)
4948 LOOP
4949 pay_in_utils.set_location(g_debug,l_procedure, 70);
4950 l_full_name := child_asg.full_name;
4951 l_actual_salary := l_actual_salary + child_asg.actual_salary;
4952 l_employee_contr := l_employee_contr + child_asg.employee_contr;
4953 l_employer_contr := l_employer_contr + child_asg.employer_contr;
4954 SELECT decode(child_asg.esi_coverage,'Yes','No','No','Yes')
4955 INTO l_esi_coverage
4956 FROM dual;
4957 EXIT;
4958 END LOOP;
4959 END LOOP;
4960 pay_in_utils.set_location(g_debug,l_procedure, 80);
4961 OPEN c_absence(l_insurance_no,c_rec.org_id);
4962 FETCH c_absence INTO l_absence;
4963 CLOSE c_absence;
4964
4965 l_actual_salary := nvl(l_actual_salary,0);
4966 l_employee_contr:= nvl(l_employee_contr,0);
4967 l_employer_contr:= nvl(l_employer_contr,0);
4968
4969 --Computation of the days and average salary.
4970 /*Bug 4282074*/
4971 l_days := 0;
4972 OPEN c_person_id(l_insurance_no);
4973 FETCH c_person_id INTO l_person_id;
4974 CLOSE c_person_id;
4975
4976 FOR c_record IN c_org_contr_days(c_rec.org_id,l_person_id,l_contr_start,l_contr_end)
4977 LOOP
4978 IF c_record.days > 0 THEN
4979 l_days := l_days + c_record.days ;
4980 END IF;
4981 END LOOP;
4982
4983 l_days := l_days - to_number(l_absence);
4984 /*Bug 4282074*/
4985 l_remarks := NULL;
4986 IF l_days <> 0 THEN
4987 l_avg_daily_wage := round((l_actual_salary/to_number(l_days)),2);
4988 ELSE
4989 l_avg_daily_wage := 0;
4990 END IF;
4991
4992 l_remarks_dummy := NULL;
4993
4994 pay_in_utils.set_location(g_debug,l_procedure, 90);
4995
4996 FOR c_rem IN c_remarks(l_insurance_no,c_rec.org_id)
4997 LOOP
4998 l_payroll_mon := TO_NUMBER(TO_CHAR(c_rem.mon,'MM'));
4999 IF (l_payroll_mon <4 )THEN
5000 l_mon := l_payroll_mon + 9;
5001 ELSE
5002 l_mon := l_payroll_mon - 3;
5003 END IF;
5004 l_remarks_dummy :=
5005 get_eit_remarks
5006 (
5007 l_insurance_no
5008 ,'ESI'
5009 ,substr(l_contr_start,5,4)||'-'||to_char(to_number(substr(l_contr_start,5,4))+1)
5010 ,l_mon
5011 );
5012 IF l_remarks IS NOT NULL THEN
5013 IF l_remarks_dummy IS NOT NULL THEN
5014 l_remarks:=l_remarks||fnd_global.local_chr(10)||l_remarks_dummy;
5015 END IF;
5016 ELSE
5017 l_remarks:=l_remarks_dummy;
5018 END IF;
5019
5020 END LOOP;
5021
5022 l_employer_total := l_employer_total + nvl(l_employer_contr,0);
5023 l_employee_total := l_employee_total + nvl(l_employee_contr,0);
5024
5025 IF flag THEN
5026 dbms_lob.Append(g_xml_data,l_xml_data);
5027 flag := FALSE;
5028 END IF;
5029 l_tag :='<emp>';
5030 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5031 l_tag :=pay_in_xml_utils.getTag('c_sl_no',l_count);
5032 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5033 l_tag :=pay_in_xml_utils.getTag('c_insurance_number',l_insurance_no);
5034 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5035 l_tag :=pay_in_xml_utils.getTag('c_full_name',l_full_name);
5036 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5037 l_tag :=pay_in_xml_utils.getTag('c_wages',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_actual_salary));
5038 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5039 l_tag :=pay_in_xml_utils.getTag('c_employee_contr',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_employee_contr));
5040 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5041 l_tag :=pay_in_xml_utils.getTag('c_esi_coverage',l_esi_coverage);
5042 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5043 l_tag :=pay_in_xml_utils.getTag('c_remarks',l_remarks);
5044 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5045 l_tag :=pay_in_xml_utils.getTag('c_days',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_days));
5046 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5047 l_tag :=pay_in_xml_utils.getTag('c_avg_daily_wages',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_avg_daily_wage));
5048 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5049 l_tag :='</emp>';
5050 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5051 l_count := l_count + 1;
5052 l_con_tot := l_con_tot + nvl(l_employee_contr,0);
5053 l_wages_tot := l_wages_tot + nvl(l_actual_salary,0);
5054 l_days_tot := l_days_tot + nvl(l_days,0);
5055 END IF;
5056 END LOOP;
5057
5058 l_tag :=pay_in_xml_utils.getTag('c_total_con',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_con_tot));
5059 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5060 l_tag :=pay_in_xml_utils.getTag('c_total_wages',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_wages_tot));
5061 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5062 l_tag :=pay_in_xml_utils.getTag('c_total_days',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_days_tot));
5063 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5064
5065 dbms_lob.close(l_xml_data);
5066 dbms_lob.createtemporary(l_xml_data,FALSE,DBMS_LOB.CALL);
5067 dbms_lob.open(l_xml_data,dbms_lob.lob_readwrite);
5068 --Employer Share, Employee Share and Total Contribution
5069 l_tag :=pay_in_xml_utils.getTag('c_employer_share',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_employer_total));
5070 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5071 l_tag :=pay_in_xml_utils.getTag('c_employee_share',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_employee_total));
5072 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5073 l_tag :=pay_in_xml_utils.getTag('c_total',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_employee_total + l_employer_total));
5074 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5075 --Challan Details
5076 l_count := 1;
5077 l_challan_tot := 0;
5078 pay_in_utils.set_location(g_debug,l_procedure, 100);
5079 FOR c_challan_rec IN c_challan(c_rec.org_id)
5080 LOOP
5081
5082 OPEN c_branch_dtls(c_challan_rec.challan_bank_code, c_challan_rec.challan_branch_code) ;
5083 FETCH c_branch_dtls INTO l_bank_branch_name, l_bank_branch_address;
5084 CLOSE c_branch_dtls;
5085
5086
5087 l_tag :='<challan>';
5088 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5089 l_tag :=pay_in_xml_utils.getTag('c_sl',l_count);
5090 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5091 l_tag :=pay_in_xml_utils.getTag('c_month',c_challan_rec.month);
5092 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5093 l_tag :=pay_in_xml_utils.getTag('c_challan_date',c_challan_rec.challan_date);
5094 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5095 l_tag :=pay_in_xml_utils.getTag('c_challan_paid',pay_us_employee_payslip_web.get_format_value(l_bg_id,c_challan_rec.challan_amt));
5096 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5097 l_tag :=pay_in_xml_utils.getTag('c_challan_bank',c_challan_rec.challan_bank);
5098 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5099 l_tag :=pay_in_xml_utils.getTag('c_challan_branch',l_bank_branch_name);
5100 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5101 l_challan_tot := l_challan_tot + nvl(c_challan_rec.challan_amt,0);
5102 l_count := l_count + 1;
5103 l_tag :='</challan>';
5104 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5105 END LOOP;
5106 l_tag :=pay_in_xml_utils.getTag('c_challan_total',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_challan_tot));
5107 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5108
5109
5110 --Declaration Details
5111 OPEN c_declaration(c_rec.org_id);
5112 FETCH c_declaration INTO l_declaration_forms
5113 ,l_tic
5114 ,l_pic_received
5115 ,l_pic_distributed
5116 ,l_accidents_reported
5117 ,l_direct_covered_employees
5118 ,l_direct_covered_wages
5119 ,l_direct_not_covered_employees
5120 ,l_direct_not_covered_wages
5121 ,l_immediate_employer_covered
5122 ,l_wages_immediate_emplr_cover
5123 ,l_immediate_emplr_not_covered
5124 ,l_wages_immd_emplr_not_covered ;
5125 CLOSE c_declaration ;
5126
5127 l_tag := '<declaration>';
5128 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5129 l_tag :=pay_in_xml_utils.getTag('c_declaration_forms',l_declaration_forms);
5130 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5131 l_tag := pay_in_xml_utils.getTag('c_tic',l_tic);
5132 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5133 l_tag := pay_in_xml_utils.getTag('c_pic_received',l_pic_received);
5134 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5135 l_tag := pay_in_xml_utils.getTag('c_pic_distributed',l_pic_distributed);
5136 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5137 l_tag := pay_in_xml_utils.getTag('c_accidents_reported',l_accidents_reported);
5138 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5139 l_tag := pay_in_xml_utils.getTag('c_direct_covered_employees',l_direct_covered_employees);
5140 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5141 l_tag := pay_in_xml_utils.getTag('c_direct_covered_wages',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_direct_covered_wages));
5142 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5143 l_tag := pay_in_xml_utils.getTag('c_direct_not_covered_employees',l_direct_not_covered_employees);
5144 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5145 l_tag := pay_in_xml_utils.getTag('c_direct_not_covered_wages',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_direct_not_covered_wages));
5146 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5147 l_tag := pay_in_xml_utils.getTag('c_immediate_employer_covered',l_immediate_employer_covered);
5148 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5149 l_tag := pay_in_xml_utils.getTag('c_wages_immediate_emplr_cover',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_wages_immediate_emplr_cover));
5150 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5151 l_tag := pay_in_xml_utils.getTag('c_immediate_emplr_not_covered',l_immediate_emplr_not_covered);
5152 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5153 l_tag := pay_in_xml_utils.getTag('c_wages_immd_emplr_not_covered',pay_us_employee_payslip_web.get_format_value(l_bg_id,l_wages_immd_emplr_not_covered));
5154 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5155 l_tag := '</declaration>';
5156 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5157
5158
5159
5160 --Element Details computing the Base Salary for ESI Calculation.
5161 l_count := 1;
5162 FOR rec_elements IN c_elements
5163 LOOP
5164 l_tag := '<elements>';
5165 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5166 l_tag :=pay_in_xml_utils.getTag('c_sl_no',l_count);
5167 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5168 l_count := l_count + 1;
5169 l_tag := pay_in_xml_utils.getTag('c_elements',rec_elements.element_name);
5170 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5171 l_tag := '</elements>';
5172 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5173 END LOOP ;
5174
5175
5176 l_tag :='</organization>';
5177 dbms_lob.writeAppend(l_xml_data, length(l_tag), l_tag);
5178 IF flag = FALSE THEN
5179 dbms_lob.Append(g_xml_data,l_xml_data);
5180 END IF;
5181 dbms_lob.close(l_xml_data);
5182 pay_in_utils.set_location(g_debug,l_procedure, 110);
5183 END LOOP;
5184 l_tag :='</ESI6>';
5185 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
5186
5187 fnd_file.put_line(fnd_file.log,'XML Created.');
5188 END create_esi_xml;
5189
5190 END pay_in_reports_pkg;