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