[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_PF_REPORTS
Source
1 PACKAGE BODY pay_in_pf_reports AS
2 /* $Header: pyinmpfr.pkb 120.0.12010000.2 2009/01/08 06:21:21 mdubasi ship $ */
3
4 ----------------------------------------------------------------------+
5 -- global variables
6 ----------------------------------------------------------------------+
7
8 g_xml_data CLOB;
9 g_package CONSTANT VARCHAR2(100) := 'pay_in_pf_reports.';
10 g_template VARCHAR2(100);
11 g_debug BOOLEAN ;
12 g_bg_id NUMBER;
13
14 --------------------------------------------------------------------------
15 -- --
16 -- Name : REPLACE_COMMA --
17 -- Type : FUNCTION --
18 -- Access : Public --
19 -- Description : This procedure formats the Data for the EFile --
20 -- --
21 -- --
22 -- Parameters : --
23 -- IN : p_string VARCHAR2 --
24 -- OUT : N/A --
25 -- RETURN TYPE : VARCHAR2 --
26 -- --
27 -- Change History : --
28 --------------------------------------------------------------------------
29 -- Rev# Date Userid Description --
30 --------------------------------------------------------------------------
31 -- 115.0 01-Aug-2007 rsaharay Initial Version --
32 --------------------------------------------------------------------------
33 FUNCTION replace_comma(p_string IN VARCHAR2)
34 RETURN VARCHAR2
35 IS
36 l_procedure VARCHAR2(100);
37 l_string PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE ;
38 BEGIN
39
40 g_debug := hr_utility.debug_enabled;
41 l_procedure := g_package ||'replace_comma';
42 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
43
44 IF g_template = 'PAYINPFEF'
45 THEN
46 l_string := REPLACE(p_string,',',' ');
47 ELSE
48 l_string := p_string ;
49 END IF ;
50 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
51
52 RETURN l_string;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
57 RAISE;
58 END replace_comma;
59
60 --------------------------------------------------------------------------
61 -- --
62 -- Name : FORMAT_NUMBER --
63 -- Type : FUNCTION --
64 -- Access : Public --
65 -- Description : This procedure formats the Data for the Printed --
66 -- Copy --
67 -- --
68 -- --
69 -- Parameters : --
70 -- IN : p_number NUMBER --
71 -- OUT : N/A --
72 -- RETURN TYPE : VARCHAR2 --
73 -- --
74 -- Change History : --
75 --------------------------------------------------------------------------
76 -- Rev# Date Userid Description --
77 --------------------------------------------------------------------------
78 -- 115.0 01-Aug-2007 rsaharay Initial Version --
79 --------------------------------------------------------------------------
80 FUNCTION format_number(p_number IN NUMBER )
81 RETURN VARCHAR2
82 IS
83 l_procedure VARCHAR2(100);
84 BEGIN
85
86 g_debug := hr_utility.debug_enabled;
87 l_procedure := g_package ||'format_number';
88 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
89
90 IF g_template = 'PAYINPFPC'
91 THEN
92 RETURN pay_us_employee_payslip_web.get_format_value(g_bg_id,p_number) ;
93 ELSE
94 RETURN p_number;
95 END IF ;
96
97 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
102 RAISE;
103 END format_number;
104
105 --------------------------------------------------------------------------
106 -- --
107 -- Name : CREATE_LEVEL2_3_XML --
108 -- Type : PROCEDURE --
109 -- Access : Public --
110 -- Description : This procedure creates the level 2 and 3 Data --
111 -- It generates the Employee Data i,e Personal Details,--
112 -- Employee Level Balances. It gets data from --
113 -- PAY_ACTION_INFORMATION --
114 -- Context : IN_PF_PERSON_DTLS --
115 -- IN_PF_SALARY --
116 -- --
117 -- Parameters : --
118 -- IN : p_business_number VARCHAR2 --
119 -- p_pf_arc_ref_no VARCHAR2 --
120 -- p_action_context_id VARCHAR2 --
121 -- p_year NUMBER --
122 -- p_month NUMBER --
123 -- p_nssn VARCHAR2 --
124 -- p_sort_by VARCHAR2 --
125 -- OUT : p_total_ncp NUMBER --
126 -- --
127 -- Change History : --
128 --------------------------------------------------------------------------
129 -- Rev# Date Userid Description --
130 --------------------------------------------------------------------------
131 -- 115.0 01-Aug-2007 rsaharay Initial Version --
132 -- 115.1 16-Oct-2007 sivanara For bug6504522 changed code to efile --
133 -- Leaving reason --
134 -- 115.2 01-Nov-2007 rsaharay Modified for Bug#6603726 --
138 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
135 --------------------------------------------------------------------------
136 PROCEDURE create_level2_3_xml( p_business_number IN VARCHAR2 DEFAULT NULL
137 ,p_pf_arc_ref_no IN VARCHAR2 DEFAULT NULL
139 ,p_year IN NUMBER DEFAULT NULL
140 ,p_month IN NUMBER DEFAULT NULL
141 ,p_nssn IN VARCHAR2 DEFAULT NULL
142 ,p_sort_by IN VARCHAR2 DEFAULT NULL
143 ,p_total_ncp OUT NOCOPY NUMBER )
144 IS
145 l_sys_date_time VARCHAR2(40);
146 l_tag VARCHAR2(1000);
147 l_procedure VARCHAR2(100);
148 l_hire_dd NUMBER ;
149 l_term_dd NUMBER ;
150 l_term_efile PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE ;
151 l_term_print_copy PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE ;
152 l_count NUMBER ;
153
154 l_total_ncp NUMBER ;
155 l_total_pf_wages NUMBER ;
156 l_total_epf NUMBER ;
157 l_epf_admin NUMBER ;
158 l_eps_con NUMBER ;
159 l_edli_con NUMBER ;
160 l_edli_admin NUMBER ;
161 l_epf_inspection NUMBER ;
162 l_edli_inspection NUMBER ;
163 l_rec_overpay NUMBER ;
164 l_penal_int NUMBER ;
165 l_interest_on_sec NUMBER ;
166 l_legal_charges NUMBER ;
167 l_pup NUMBER ;
168 l_penalty NUMBER ;
169
170
171
172
173
174
175 CURSOR csr_assignment
176 IS
177 SELECT DISTINCT
178 action_information4 pf_no
179 ,action_information5 nssn
180 ,action_information6 hire_date
181 ,action_information7 emp_type
182 ,action_information8 term_date
183 ,action_information9 report_term_reason
184 ,action_information10 efile_term_reason
185 ,action_information11 eps
186 ,action_information13 pf_class
187 ,action_context_id assignment_action_id
188 FROM pay_action_information
189 WHERE action_information2 = p_pf_arc_ref_no
190 AND action_information1 = p_business_number
191 AND action_information3 = p_month||p_year
192 AND action_information_category = 'IN_PF_PERSON_DTLS'
193 AND action_context_type = 'AAP'
194 AND DECODE(action_information5,NULL,'NO_NSSN','NSSN') LIKE DECODE(p_nssn,'ALL','%',p_nssn)
195 ORDER BY (DECODE(p_sort_by,'NSSN',action_information5,'DOJ',action_information6));
196
197
198 /*Picked up based on the PF Classification Type
199 An employee will have multiple rows in the report if he is present in multiple PF Orgs of
200 different classifications under a Business Number.*/
201 CURSOR csr_assignment_bal(p_action_context_id VARCHAR2 ,p_pf_class varchar2)
202 IS
203 SELECT action_information3 bal_name
204 ,SUM (fnd_number.canonical_to_number(action_information4)) bal_value
205 FROM pay_action_information pai
206 WHERE action_context_id = p_action_context_id
207 AND action_information1 = p_business_number
208 AND action_information_category = 'IN_PF_SALARY'
209 AND action_context_type = 'AAP'
210 AND action_information5 = p_pf_class
211 GROUP BY action_information3;
212
213
214
215
216 CURSOR csr_challan
217 IS
218 SELECT SUM(fnd_number.canonical_to_number(action_information21)) Interest_on_Sec
219 ,SUM(fnd_number.canonical_to_number(action_information22)) Legal_Charges
220 ,SUM(fnd_number.canonical_to_number(action_information23)) Penalty
221 FROM (SELECT DISTINCT
222 action_information21
223 ,action_information22
224 ,action_information23
225 ,action_information8
226 FROM pay_action_information
227 WHERE action_context_id = p_action_context_id
228 AND action_information1 = p_business_number
229 AND action_information_category = 'IN_PF_CHALLAN'
230 AND action_context_type = 'PA');
231
232 CURSOR csr_pup_total_amount
233 IS
234 SELECT SUM(fnd_number.canonical_to_number(action_information7)) Payment_under_Protest
235 FROM (SELECT DISTINCT
236 action_information7
237 ,action_information8
238 FROM pay_action_information
239 WHERE action_context_id = p_action_context_id
240 AND action_information1 = p_business_number
241 AND action_information_category = 'IN_PF_CHALLAN'
242 AND action_context_type = 'PA'
243 AND action_information20 = 'Y');
244
245
246 BEGIN
247 g_debug := hr_utility.debug_enabled;
248 l_procedure := g_package ||'create_level2_3_xml';
249 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
250
251 l_total_pf_wages := 0;
252 l_total_epf := 0;
253 l_epf_admin := 0;
254 l_eps_con := 0;
255 l_edli_con := 0;
256 l_edli_admin := 0;
257 l_epf_inspection := 0;
258 l_edli_inspection := 0;
259 l_rec_overpay := 0;
260 l_penal_int := 0;
264 l_penalty := 0;
261 l_interest_on_sec := 0;
262 l_legal_charges := 0;
263 l_total_ncp := 0;
265 l_pup := 0;
266 l_count := 0;
267
268
269
270 l_tag := '<Level2>';
271 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
272
273
274 FOR rec_assignment IN csr_assignment
275 LOOP
276
277 l_count := l_count + 1 ;
278 IF l_count < 21
279 THEN
280 l_tag := '<EMPLOYEE>'; --Employee Tag for the employees in Main Sheet
281 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
282 ELSE
283 l_tag := '<EMPLOYEE_CONTD>'; --Employee Tag for the employees in Continous Sheet
284 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
285 END IF ;
286
287 l_tag := pay_in_xml_utils.getTag('c_2_sl_no',l_count);
288 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
289
290 l_tag := pay_in_xml_utils.getTag('c_2_pf_no',rec_assignment.pf_no);
291 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
292
293 l_tag := pay_in_xml_utils.getTag('c_2_nssn',rec_assignment.nssn);
294 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
295
296 /*
297 Incase of Hire Date and Term Date will show the DD value only if the Hire/Term Month is the
298 same as the reporting month.
299 */
300
301 IF TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(rec_assignment.hire_date),'MMYYYY')) = p_month||p_year
302 THEN
303 l_hire_dd := TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(rec_assignment.hire_date),'DD'));
304 ELSE
305 l_hire_dd := NULL ;
306 END IF ;
307
308 IF TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(rec_assignment.term_date),'MMYYYY')) = p_month||p_year
309 THEN
310 l_term_dd := TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(rec_assignment.term_date),'DD'));
311 l_term_efile := rec_assignment.efile_term_reason ;
312 l_term_print_copy := rec_assignment.report_term_reason ;
313 ELSE
314 l_term_dd := NULL ;
315 l_term_efile := NULL ;
316 l_term_print_copy := NULL ;
317 END IF ;
318
319 l_tag := pay_in_xml_utils.getTag('c_2_hiredate',l_hire_dd);
320 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
321
322 l_tag := pay_in_xml_utils.getTag('c_2_emp_type',rec_assignment.emp_type);
323 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
324
325 l_tag := pay_in_xml_utils.getTag('c_2_term_date',l_term_dd);
326 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
327
328 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_report',l_term_print_copy);
329 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
330
331 /*Added following code for bug6504522*/
332 IF l_term_efile = 'SUP_ANN' THEN
333 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_efile','1');
334 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
335 ELSIF l_term_efile = 'RETIRE' THEN
336 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_efile','2');
337 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
338 ELSIF l_term_efile = 'DEATH' THEN
339 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_efile','3');
340 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
341 ELSIF l_term_efile = 'PMT_DISABLE' OR
342 l_term_efile = 'RESIGN' THEN
343 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_efile','5');
344 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
345 ELSIF l_term_efile = 'RETRENCH' THEN
346 l_tag := pay_in_xml_utils.getTag('c_2_term_reason_efile','6');
347 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
348 END IF ;
349
350 IF rec_assignment.eps = 'FULL_FULL' THEN
351 l_tag := pay_in_xml_utils.getTag('c_2_eps','Y');
352 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
353 ELSE
354 l_tag := pay_in_xml_utils.getTag('c_2_eps','N');
355 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
356 END IF ;
357
358
359 /*EXEM -> Exempted PF Organization
360 UEX -> UnExempted PF Organization
361 */
362
363 FOR rec_assignment_bal IN csr_assignment_bal(rec_assignment.assignment_action_id,rec_assignment.pf_class)
364 LOOP
365 IF rec_assignment_bal.bal_name = 'Employee Statutory PF Contribution' AND rec_assignment.pf_class <> 'EXEM'
366 THEN
367
368 l_tag := pay_in_xml_utils.getTag('c_2_emp_pf_con',format_number(rec_assignment_bal.bal_value));
369 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
370 ELSIF rec_assignment_bal.bal_name = 'Employee Voluntary PF Contribution' AND rec_assignment.pf_class <> 'EXEM'
371 THEN
372 l_tag := pay_in_xml_utils.getTag('c_2_emp_vol_pf',format_number(rec_assignment_bal.bal_value));
373 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
374 ELSIF rec_assignment_bal.bal_name = 'Employer PF Contribution' AND rec_assignment.pf_class <> 'EXEM'
375 THEN
376 l_tag := pay_in_xml_utils.getTag('c_2_emplr_pf_con',format_number(rec_assignment_bal.bal_value));
377 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
378 ELSIF rec_assignment_bal.bal_name = 'Refund of Advance Employee PF Share' AND rec_assignment.pf_class <> 'EXEM'
379 THEN
383 THEN
380 l_tag := pay_in_xml_utils.getTag('c_2_ref_adv_emp',format_number(rec_assignment_bal.bal_value));
381 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
382 ELSIF rec_assignment_bal.bal_name = 'Refund of Advance Employer PF Share' AND rec_assignment.pf_class <> 'EXEM'
384 l_tag := pay_in_xml_utils.getTag('c_2_ref_adv_emplr',format_number(rec_assignment_bal.bal_value));
385 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
386 ELSIF rec_assignment_bal.bal_name = 'Non Contributory Period'
387 THEN
388 l_tag := pay_in_xml_utils.getTag('c_2_non_con_per',rec_assignment_bal.bal_value);
389 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
390 l_total_ncp := l_total_ncp + rec_assignment_bal.bal_value ;
391 ELSIF rec_assignment_bal.bal_name = 'EPS Contribution'
392 THEN
393 l_tag := pay_in_xml_utils.getTag('c_2_eps_con',format_number(rec_assignment_bal.bal_value));
394 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
395 ELSIF rec_assignment_bal.bal_name = 'PF Actual Salary'
396 THEN
397 l_tag := pay_in_xml_utils.getTag('c_2_pf_wages',format_number(rec_assignment_bal.bal_value));
398 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
399 ELSIF rec_assignment_bal.bal_name = 'Recovery of Over Payment of Employee PF Share'
400 THEN
401 l_tag := pay_in_xml_utils.getTag('c_2_rec_overpay_emp',format_number(rec_assignment_bal.bal_value));
402 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
403 ELSIF rec_assignment_bal.bal_name = 'Recovery of Over Payment of Employer PF Share'
404 THEN
405 l_tag := pay_in_xml_utils.getTag('c_2_rec_overpay_emplr',format_number(rec_assignment_bal.bal_value));
406 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
407 ELSIF rec_assignment_bal.bal_name = 'Penalty Interest on Refund of Employee PF Share'
408 THEN
409 l_tag := pay_in_xml_utils.getTag('c_2_ref_penalty_emp',format_number(rec_assignment_bal.bal_value));
410 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
411 ELSIF rec_assignment_bal.bal_name = 'Penalty Interest on Refund of Employer PF Share'
412 THEN
413 l_tag := pay_in_xml_utils.getTag('c_2_ref_penalty_emplr',format_number(rec_assignment_bal.bal_value));
414 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
415
416 ELSIF rec_assignment_bal.bal_name = 'Voluntary PF Percent'
417 THEN
418 l_tag := pay_in_xml_utils.getTag('c_2_vol_pf_percent',rec_assignment_bal.bal_value);
419 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
420 END IF ;
421
422
423
424 --Level 3 Starts
425
426 /* We are not summing up the Balances for the Level 3 in cursor as this would be reported based on PF Org Classification
427 */
428
429 IF rec_assignment_bal.bal_name = 'PF Actual Salary'
430 THEN
431 l_total_pf_wages := l_total_pf_wages + rec_assignment_bal.bal_value;
432
433 ELSIF rec_assignment_bal.bal_name IN ('Employer PF Contribution'
434 ,'Employee Statutory PF Contribution'
435 ,'Employee Voluntary PF Contribution'
436 ,'Refund of Advance Employee PF Share'
437 ,'Refund of Advance Employer PF Share') AND rec_assignment.pf_class <> 'EXEM'
438 THEN
439 l_total_epf := l_total_epf + rec_assignment_bal.bal_value;
440
441 ELSIF rec_assignment_bal.bal_name = 'Employer EDLI Administrative Charges' AND rec_assignment.pf_class <> 'EXEM'
442 THEN
443 l_edli_admin := l_edli_admin + rec_assignment_bal.bal_value;
444
445 ELSIF rec_assignment_bal.bal_name = 'Employer EDLI Inspection Charges' AND rec_assignment.pf_class <> 'UEX'
446 THEN
447 l_edli_inspection := l_edli_inspection + rec_assignment_bal.bal_value;
448
449 ELSIF rec_assignment_bal.bal_name = 'Employer PF Administrative Charges'
450 THEN
451 l_epf_admin := l_epf_admin + rec_assignment_bal.bal_value;
452
453 ELSIF rec_assignment_bal.bal_name = 'EPS Contribution'
454 THEN
455 l_eps_con := l_eps_con + rec_assignment_bal.bal_value;
456
457 ELSIF rec_assignment_bal.bal_name = 'EDLI Contribution'
458 THEN
459 l_edli_con := l_edli_con + rec_assignment_bal.bal_value;
460
461 ELSIF rec_assignment_bal.bal_name = 'Employer PF Inspection Charges'
462 THEN
463 l_epf_inspection := l_epf_inspection + rec_assignment_bal.bal_value;
464
465 ELSIF rec_assignment_bal.bal_name IN ('Recovery of Over Payment of Employee PF Share'
466 ,'Recovery of Over Payment of Employer PF Share')
467 THEN
468 l_rec_overpay := l_rec_overpay + rec_assignment_bal.bal_value;
469
470 ELSIF rec_assignment_bal.bal_name IN ('Penalty Interest on Refund of Employee PF Share'
471 ,'Penalty Interest on Refund of Employer PF Share')
472 THEN
473 l_penal_int := l_penal_int + rec_assignment_bal.bal_value;
474
475 END IF ;
476
477 END LOOP ;
478
479 IF l_count < 21
480 THEN
481 l_tag := '</EMPLOYEE>';
482 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
483 ELSE
484 l_tag := '</EMPLOYEE_CONTD>';
485 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
486 END IF ;
487
488 END LOOP ;
489
490
491 l_tag := '</Level2>';
495
492 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
493
494 p_total_ncp := l_total_ncp;
496 l_tag := '<Level3>';
497 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
498
499 l_tag := pay_in_xml_utils.getTag('c_3_total_pf_wages',format_number(l_total_pf_wages));
500 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
501
502 l_tag := pay_in_xml_utils.getTag('c_3_total_epf',format_number(l_total_epf));
503 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
504
505 l_tag := pay_in_xml_utils.getTag('c_3_epf_admin',format_number(l_epf_admin));
506 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
507
508 l_tag := pay_in_xml_utils.getTag('c_3_eps_con',format_number(l_eps_con));
509 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
510
511 l_tag := pay_in_xml_utils.getTag('c_3_edli_con',format_number(l_edli_con));
512 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
513
514 l_tag := pay_in_xml_utils.getTag('c_3_edli_admin',format_number(l_edli_admin));
515 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
516
517 l_tag := pay_in_xml_utils.getTag('c_3_epf_inspection',format_number(l_epf_inspection));
518 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
519
520 l_tag := pay_in_xml_utils.getTag('c_3_edli_inspection',format_number(l_edli_inspection));
521 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
522
523 l_tag := pay_in_xml_utils.getTag('c_3_rec_overpay',format_number(l_rec_overpay));
524 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
525
526 l_tag := pay_in_xml_utils.getTag('c_3_penal_int',format_number(l_penal_int));
527 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
528
529 OPEN csr_challan;
530 FETCH csr_challan INTO l_interest_on_sec,l_legal_charges,l_penalty;
531 CLOSE csr_challan;
532
533 OPEN csr_pup_total_amount ;
534 FETCH csr_pup_total_amount INTO l_pup;
535 CLOSE csr_pup_total_amount;
536
537 l_tag := pay_in_xml_utils.getTag('c_3_int_on_sec',format_number(l_interest_on_sec));
538 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
539
540 l_tag := pay_in_xml_utils.getTag('c_3_legal_charges',format_number(l_legal_charges));
541 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
542
543 l_tag := pay_in_xml_utils.getTag('c_3_penalty',format_number(l_penalty));
544 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
545
546 l_tag := pay_in_xml_utils.getTag('c_3_pup',format_number(l_pup));
547 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
548
549 l_tag := '</Level3>';
550 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
551
552 -- Level3 Ends.
553
554 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
555
556 EXCEPTION
557
558 WHEN OTHERS THEN
559 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
560 RAISE;
561
562 END create_level2_3_xml;
563
564 --------------------------------------------------------------------------
565 -- --
566 -- Name : CREATE_LEVEL4_XML --
567 -- Type : PROCEDURE --
568 -- Access : Public --
569 -- Description : This procedure creates the level 4 Data --
570 -- It generates the Challan Data.It gets data from --
571 -- PAY_ACTION_INFORMATION --
572 -- Context : IN_PF_CHALLAN --
573 -- --
574 -- --
575 -- Parameters : --
576 -- IN : p_business_number VARCHAR2 --
577 -- p_action_context_id VARCHAR2 --
578 -- p_year NUMBER --
579 -- p_month NUMBER --
580 -- OUT : N/A --
581 -- --
582 -- Change History : --
583 --------------------------------------------------------------------------
584 -- Rev# Date Userid Description --
585 --------------------------------------------------------------------------
586 -- 115.0 01-Aug-2007 rsaharay Initial Version --
587 --------------------------------------------------------------------------
588 PROCEDURE create_level4_xml( p_business_number IN VARCHAR2 DEFAULT NULL
589 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
590 ,p_year IN NUMBER DEFAULT NULL
591 ,p_month IN NUMBER DEFAULT NULL)
592 IS
593 l_sys_date_time VARCHAR2(40);
594 l_tag VARCHAR2(1000);
595 l_procedure VARCHAR2(100) ;
596
597 l_bank_name FND_COMMON_LOOKUPS.MEANING%TYPE ;
598 l_base_branch_name FND_COMMON_LOOKUPS.MEANING%TYPE ;
599
600 CURSOR csr_challan
601 IS
602 SELECT DISTINCT
606 ,action_information6 Cheque_DD_Dep_Date
603 action_information2 Payment_Type
604 ,action_information4 Cheque_DD_No
605 ,action_information5 Cheque_DD_Date
607 ,fnd_number.canonical_to_number(action_information7) Amount
608 ,action_information8 Challan_Ref
609 ,action_information9 Bank_Code
610 ,hr_general.decode_lookup('IN_PF_BANKS',action_information9) Bank_Name
611 ,action_information11 Branch_Code
612 ,action_information12 Branch_Name
613 ,action_information13 Branch_Add
614 ,action_information14 Dep_Bank_Code
615 ,action_information15 Dep_Branch_Code
616 ,action_information16 Dep_Branch_Name
617 ,action_information17 Dep_Branch_Address
618 ,action_information18 Base_Branch_Code
619 ,hr_general.decode_lookup('IN_PF_BASE_BRANCH',action_information18) Base_Branch_Name
620 FROM pay_action_information
621 WHERE action_context_id = p_action_context_id
622 AND action_information1 = p_business_number
623 AND action_information_category = 'IN_PF_CHALLAN'
624 AND action_context_type = 'PA';
625
626
627
628
629 BEGIN
630
631 g_debug := hr_utility.debug_enabled;
632 l_procedure := g_package ||'create_level4_xml';
633 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
634
635 l_tag := '<Level4>';
636 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
637
638 FOR rec_challan IN csr_challan
639 LOOP
640
641 l_tag := '<CHALLAN>';
642 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
643
644 l_tag := pay_in_xml_utils.getTag('c_4_payment_type',rec_challan.Payment_Type);
645 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
646
647 l_tag := pay_in_xml_utils.getTag('c_4_cheque_dd_no',rec_challan.Cheque_DD_No);
648 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
649
650 l_tag := pay_in_xml_utils.getTag('c_4_cheque_dd_date',TO_CHAR(fnd_date.canonical_to_date(rec_challan.Cheque_DD_Date),'DD-MM-YYYY'));
651 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
652
653 l_tag := pay_in_xml_utils.getTag('c_4_cheque_dd_dep_date',TO_CHAR(fnd_date.canonical_to_date(rec_challan.Cheque_DD_Dep_Date),'DD-MM-YYYY'));
654 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
655
656 l_tag := pay_in_xml_utils.getTag('c_4_amount',format_number(rec_challan.Amount));
657 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
658
659 l_tag := pay_in_xml_utils.getTag('c_4_bank_code',replace_comma(rec_challan.Bank_Code));
660 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
661
662 l_tag := pay_in_xml_utils.getTag('c_4_bank_name',replace_comma(rec_challan.Bank_Name));
663 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
664
665 l_tag := pay_in_xml_utils.getTag('c_4_branch_code',replace_comma(rec_challan.Branch_Code));
666 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
667
668 l_tag := pay_in_xml_utils.getTag('c_4_branch_name',replace_comma(rec_challan.Branch_Name));
669 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
670
671 l_tag := pay_in_xml_utils.getTag('c_4_branch_add',replace_comma(rec_challan.Branch_Add));
672 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
673
674 l_tag := pay_in_xml_utils.getTag('c_4_challan_ref',rec_challan.Challan_Ref);
675 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
676
677 l_tag := pay_in_xml_utils.getTag('c_4_dep_branch_code',replace_comma(rec_challan.Dep_Branch_Code));
678 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
679
680 l_tag := pay_in_xml_utils.getTag('c_4_base_branch',replace_comma(rec_challan.Base_Branch_Code));
681 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
682
683 l_tag := pay_in_xml_utils.getTag('c_4_base_branch_name',replace_comma(rec_challan.Base_Branch_Name));
684 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
685
686 l_tag := '</CHALLAN>';
687 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
688
689 END LOOP ;
690
691 l_tag := '</Level4>';
692 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
693
694 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
695
696 EXCEPTION
697
698 WHEN OTHERS THEN
699 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
700 RAISE;
701
702 END create_level4_xml ;
703
704
705
706 --------------------------------------------------------------------------
707 -- --
708 -- Name : CREATE_LEVEL5_XML --
712 -- It generates the 14B Data.It gets data from --
709 -- Type : PROCEDURE --
710 -- Access : Public --
711 -- Description : This procedure creates the level 5 Data --
713 -- PAY_ACTION_INFORMATION --
714 -- Context : IN_PF_CHALLAN --
715 -- IN_PF_14B --
716 -- --
717 -- Parameters : --
718 -- IN : p_business_number VARCHAR2 --
719 -- p_action_context_id VARCHAR2 --
720 -- OUT : N/A --
721 -- --
722 -- Change History : --
723 --------------------------------------------------------------------------
724 -- Rev# Date Userid Description --
725 --------------------------------------------------------------------------
726 -- 115.0 01-Aug-2007 rsaharay Initial Version --
727 --------------------------------------------------------------------------
728 PROCEDURE create_level5_xml( p_business_number IN VARCHAR2 DEFAULT NULL
729 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
730 )
731 IS
732 l_sys_date_time VARCHAR2(40);
733 l_tag VARCHAR2(1000);
734 l_procedure VARCHAR2(100) ;
735
736
737 CURSOR csr_challan
738 IS
739 SELECT DISTINCT
740 action_information8 Challan_Ref
741 FROM pay_action_information
742 WHERE action_context_id = p_action_context_id
743 AND action_information1 = p_business_number
744 AND action_information_category = 'IN_PF_CHALLAN'
745 AND action_context_type = 'PA';
746
747 CURSOR csr_14B(p_challan VARCHAR2)
748 IS
749 SELECT DISTINCT
750 action_information1 Prev_Mth
751 ,action_information2 Prev_Yr
752 ,action_information3 Challan_Ref
753 ,fnd_number.canonical_to_number(action_information4) Penal_Damages_Due
754 ,fnd_number.canonical_to_number(action_information5) EPS_Penal_Damages
755 ,fnd_number.canonical_to_number(action_information6) EDLI_Penal_Damages
756 ,fnd_number.canonical_to_number(action_information7) EPF_Penal_Damages
757 ,fnd_number.canonical_to_number(action_information8) Edli_Admin
758 FROM pay_action_information
759 WHERE action_context_id = p_action_context_id
760 AND action_information3 = p_challan
761 AND action_information_category = 'IN_PF_14B'
762 AND action_context_type = 'PA';
763
764
765 BEGIN
766
767 g_debug := hr_utility.debug_enabled;
768 l_procedure := g_package ||'create_level5_xml';
769 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
770
771 l_tag := '<Level5>';
772 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
773
774 FOR rec_challan IN csr_challan
775 LOOP
776 FOR rec_14B IN csr_14B(rec_challan.Challan_Ref)
777 LOOP
778 l_tag := '<Damages14B>';
779 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
780
781 l_tag := pay_in_xml_utils.getTag('c_5_prev_mth',rec_14B.Prev_Mth);
782 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
783
784 l_tag := pay_in_xml_utils.getTag('c_5_prev_yr',rec_14B.Prev_Yr);
785 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
786
787 l_tag := pay_in_xml_utils.getTag('c_5_epf_penal_damages',format_number(rec_14B.Penal_Damages_Due));
788 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
789
790 l_tag := pay_in_xml_utils.getTag('c_5_eps_penal_damages',format_number(rec_14B.EPS_Penal_Damages));
791 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
792
793 l_tag := pay_in_xml_utils.getTag('c_5_edli_penal_damages',format_number(rec_14B.EDLI_Penal_Damages));
794 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
795
796 l_tag := pay_in_xml_utils.getTag('c_5_epf_admin',format_number(rec_14B.EPF_Penal_Damages));
797 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
798
799 l_tag := pay_in_xml_utils.getTag('c_5_edli_admin',format_number(rec_14B.Edli_Admin));
800 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
801
802 l_tag := '</Damages14B>';
803 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
804 END LOOP ;
805 END LOOP ;
806
807 l_tag := '</Level5>';
808 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
809
810
811 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
812
813 EXCEPTION
814
815 WHEN OTHERS THEN
816 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
817 RAISE;
818 END create_level5_xml ;
819
820
821 --------------------------------------------------------------------------
822 -- --
826 -- Description : This procedure creates the level 6 Data --
823 -- Name : CREATE_LEVEL6_XML --
824 -- Type : PROCEDURE --
825 -- Access : Public --
827 -- It generates the 7Q Data.It gets data from --
828 -- PAY_ACTION_INFORMATION --
829 -- Context : IN_PF_CHALLAN --
830 -- IN_PF_7Q --
831 -- --
832 -- Parameters : --
833 -- IN : p_business_number VARCHAR2 --
834 -- p_action_context_id VARCHAR2 --
835 -- OUT : N/A --
836 -- --
837 -- Change History : --
838 --------------------------------------------------------------------------
839 -- Rev# Date Userid Description --
840 --------------------------------------------------------------------------
841 -- 115.0 01-Aug-2007 rsaharay Initial Version --
842 --------------------------------------------------------------------------
843 PROCEDURE create_level6_xml( p_business_number IN VARCHAR2 DEFAULT NULL
844 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
845 )
846 IS
847 l_sys_date_time VARCHAR2(40);
848 l_tag VARCHAR2(1000);
849 l_procedure VARCHAR2(100) ;
850
851
852 CURSOR csr_challan
853 IS
854 SELECT DISTINCT
855 action_information8 Challan_Ref
856 FROM pay_action_information
857 WHERE action_context_id = p_action_context_id
858 AND action_information1 = p_business_number
859 AND action_information_category = 'IN_PF_CHALLAN'
860 AND action_context_type = 'PA';
861
862 CURSOR csr_7Q(p_challan VARCHAR2)
863 IS
864 SELECT DISTINCT
865 action_information1 Due_Mth
866 ,action_information2 Due_Yr
867 ,action_information3 Challan_Ref
868 ,fnd_number.canonical_to_number(action_information4) EPF_Damages
869 ,fnd_number.canonical_to_number(action_information5) EPS_Damages
870 ,fnd_number.canonical_to_number(action_information6) EDLI_Damages
871 ,fnd_number.canonical_to_number(action_information7) EPF_Admin
872 ,fnd_number.canonical_to_number(action_information8) EDLI_Admin
873 FROM pay_action_information
874 WHERE action_context_id = p_action_context_id
875 AND action_information3 = p_challan
876 AND action_information_category = 'IN_PF_7Q'
877 AND action_context_type = 'PA';
878
879
880 BEGIN
881
882 g_debug := hr_utility.debug_enabled;
883 l_procedure := g_package ||'create_level6_xml';
884 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
885
886
887 l_tag := '<Level6>';
888 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
889
890 FOR rec_challan IN csr_challan
891 LOOP
892 FOR rec_7Q IN csr_7Q(rec_challan.Challan_Ref)
893 LOOP
894 l_tag := '<Damages7Q>';
895 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
896
897 l_tag := pay_in_xml_utils.getTag('c_6_prev_mth',rec_7Q.Due_Mth);
898 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
899
900 l_tag := pay_in_xml_utils.getTag('c_6_prev_yr',rec_7Q.Due_Yr);
901 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
902
903 l_tag := pay_in_xml_utils.getTag('c_6_epf_penal_damages',format_number(rec_7Q.EPF_Damages));
904 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
905
906 l_tag := pay_in_xml_utils.getTag('c_6_eps_penal_damages',format_number(rec_7Q.EPS_Damages));
907 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
908
909 l_tag := pay_in_xml_utils.getTag('c_6_edli_penal_damages',format_number(rec_7Q.EDLI_Damages));
910 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
911
912 l_tag := pay_in_xml_utils.getTag('c_6_epf_admin',format_number(rec_7Q.EPF_Admin));
913 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
914
915 l_tag := pay_in_xml_utils.getTag('c_6_edli_admin',format_number(rec_7Q.Edli_Admin));
916 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
917
918 l_tag := '</Damages7Q>';
919 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
920 END LOOP ;
921 END LOOP ;
922
923 l_tag := '</Level6>';
924 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
925
926
927 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
928
929 EXCEPTION
930
931 WHEN OTHERS THEN
932 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
933 RAISE;
934
935 END create_level6_xml ;
936
937
938 --------------------------------------------------------------------------
942 -- Access : Public --
939 -- --
940 -- Name : CREATE_LEVEL7_XML --
941 -- Type : PROCEDURE --
943 -- Description : This procedure creates the level 7 Data --
944 -- It generates the Misc Data.It gets data from --
945 -- PAY_ACTION_INFORMATION --
946 -- Context : IN_PF_CHALLAN --
947 -- IN_PF_MISC --
948 -- --
949 -- Parameters : --
950 -- IN : p_business_number VARCHAR2 --
951 -- p_action_context_id VARCHAR2 --
952 -- OUT : N/A --
953 -- --
954 -- Change History : --
955 --------------------------------------------------------------------------
956 -- Rev# Date Userid Description --
957 --------------------------------------------------------------------------
958 -- 115.0 01-Aug-2007 rsaharay Initial Version --
959 --------------------------------------------------------------------------
960 PROCEDURE create_level7_xml( p_business_number IN VARCHAR2 DEFAULT NULL
961 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
962 )
963 IS
964 l_sys_date_time VARCHAR2(40);
965 l_tag VARCHAR2(1000);
966 l_procedure VARCHAR2(100) ;
967
968
969 CURSOR csr_challan
970 IS
971 SELECT DISTINCT
972 action_information8 Challan_Ref
973 FROM pay_action_information
974 WHERE action_context_id = p_action_context_id
975 AND action_information1 = p_business_number
976 AND action_information_category = 'IN_PF_CHALLAN'
977 AND action_context_type = 'PA';
978
979 CURSOR csr_misc(p_challan VARCHAR2)
980 IS
981 SELECT DISTINCT
982 fnd_number.canonical_to_number(action_information2) EPF_Misc_Pay
983 ,action_information3 EPF_Rem
984 ,fnd_number.canonical_to_number(action_information4) EPS_Misc_Pay
985 ,action_information5 EPS_Rem
986 ,fnd_number.canonical_to_number(action_information6) EDLI_Misc_Pay
987 ,action_information7 EDLI_Rem
988 ,fnd_number.canonical_to_number(action_information8) EPF_Admin
989 ,action_information9 EPF_Admin_Rem
990 ,fnd_number.canonical_to_number(action_information10) EDLI_Admin
991 ,action_information11 EDLI_Admin_Rem
992 FROM pay_action_information
993 WHERE action_context_id = p_action_context_id
994 AND action_information1 = p_challan
995 AND action_information_category = 'IN_PF_MISC'
996 AND action_context_type = 'PA';
997
998
999 BEGIN
1000
1001 g_debug := hr_utility.debug_enabled;
1002 l_procedure := g_package ||'create_level7_xml';
1003 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1004
1005
1006 l_tag := '<Level7>';
1007 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1008
1009 FOR rec_challan IN csr_challan
1010 LOOP
1011 FOR rec_misc IN csr_misc(rec_challan.Challan_Ref)
1012 LOOP
1013 l_tag := '<Miscellaneous>';
1014 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1015
1016 l_tag := pay_in_xml_utils.getTag('c_7_epf_pay',format_number(rec_misc.EPF_Misc_Pay));
1017 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1018
1019 l_tag := pay_in_xml_utils.getTag('c_7_epf_rem',replace_comma(rec_misc.EPF_Rem));
1020 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1021
1022 l_tag := pay_in_xml_utils.getTag('c_7_eps_pay',format_number(rec_misc.EPS_Misc_Pay));
1023 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1024
1025 l_tag := pay_in_xml_utils.getTag('c_7_eps_rem',replace_comma(rec_misc.EPS_Rem));
1026 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1027
1028 l_tag := pay_in_xml_utils.getTag('c_7_edli_pay',format_number(rec_misc.EDLI_Misc_Pay));
1029 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1030
1031 l_tag := pay_in_xml_utils.getTag('c_7_edli_rem',replace_comma(rec_misc.EDLI_Rem));
1032 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1033
1034 l_tag := pay_in_xml_utils.getTag('c_7_epf_admin',format_number(rec_misc.EPF_Admin));
1035 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1036
1037 l_tag := pay_in_xml_utils.getTag('c_7_epf_admin_rem',replace_comma(rec_misc.EPF_Admin_Rem));
1038 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1039
1040 l_tag := pay_in_xml_utils.getTag('c_7_edli_admin',format_number(rec_misc.EDLI_Admin));
1041 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1042
1043 l_tag := pay_in_xml_utils.getTag('c_7_edli_admin_rem',replace_comma(rec_misc.EDLI_Admin_Rem));
1044 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1045
1046 l_tag := '</Miscellaneous>';
1047 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1048 END LOOP ;
1049 END LOOP ;
1050
1051 l_tag := '</Level7>';
1055 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,20);
1052 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1053
1054
1056
1057 EXCEPTION
1058
1059 WHEN OTHERS THEN
1060 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
1061 RAISE;
1062
1063 END create_level7_xml ;
1064
1065 --------------------------------------------------------------------------
1066 -- --
1067 -- Name : CREATE_LEVEL8_XML --
1068 -- Type : PROCEDURE --
1069 -- Access : Public --
1070 -- Description : This procedure creates the level 8 Data --
1071 -- It generates the data for the Representative --
1072 -- Details,Filer License Number --
1073 -- Gets the data from PAY_ACTION_INFORMATION --
1074 -- Context : IN_PF_ORG --
1075 -- Gets the data from HR_ORGANIZATION_INFORMATION --
1076 -- Context : PER_IN_COMPANY_FILER_INFO --
1077 -- --
1078 -- --
1079 -- Parameters : --
1080 -- IN : p_business_number VARCHAR2 --
1081 -- p_action_context_id VARCHAR2 --
1082 -- p_total_ncp NUMBER --
1083 -- p_filer_license_no VARCHAR2 --
1084 -- OUT : N/A --
1085 -- --
1086 -- Change History : --
1087 --------------------------------------------------------------------------
1088 -- Rev# Date Userid Description --
1089 --------------------------------------------------------------------------
1090 -- 115.0 01-Aug-2007 rsaharay Initial Version --
1091 --------------------------------------------------------------------------
1092 PROCEDURE create_level8_xml(p_business_number IN VARCHAR2 DEFAULT NULL
1093 ,p_action_context_id IN VARCHAR2 DEFAULT NULL
1094 ,p_total_ncp IN NUMBER DEFAULT NULL
1095 ,p_filer_license_no IN VARCHAR2 DEFAULT NULL
1096 )
1097 IS
1098
1099 l_sys_date VARCHAR2(40);
1100 l_tag VARCHAR2(1000);
1101 l_procedure VARCHAR2(100) ;
1102 l_filer_license_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1103
1104 CURSOR csr_filer_id
1105 IS
1106 SELECT org_information1
1107 FROM hr_organization_information
1108 WHERE org_information2 = p_filer_license_no
1109 AND org_information_context = 'PER_IN_COMPANY_FILER_INFO';
1110
1111 CURSOR csr_rep_details
1112 IS
1113 SELECT DISTINCT
1114 action_information4 rep_name
1115 ,action_information5 rep_pos
1116 FROM pay_action_information
1117 WHERE action_information_category = 'IN_PF_ORG'
1118 AND action_context_type = 'PA'
1119 AND action_context_id = p_action_context_id
1120 AND action_information1 = p_business_number ;
1121
1122 BEGIN
1123 g_debug := hr_utility.debug_enabled;
1124 l_procedure := g_package ||'create_level8_xml';
1125 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1126
1127
1128 OPEN csr_filer_id ;
1129 FETCH csr_filer_id INTO l_filer_license_id ;
1130 CLOSE csr_filer_id ;
1131
1132
1133 l_tag := '<Level8>';
1134 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1135
1136 l_sys_date:=to_char(SYSDATE,'DD-MM-YYYY');
1137 --System Date:
1138 l_tag :=pay_in_xml_utils.getTag('c_8_sys_date',l_sys_date);
1139 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1140
1141 l_tag :=pay_in_xml_utils.getTag('c_8_filer_license_id',l_filer_license_id);
1142 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1143
1144 l_tag :=pay_in_xml_utils.getTag('c_8_filer_license_no',p_filer_license_no);
1145 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1146
1147 l_tag := pay_in_xml_utils.getTag('c_8_total_ncp',p_total_ncp);
1148 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1149
1150 FOR rec_rep_details IN csr_rep_details
1151 LOOP
1152
1153 l_tag := '<REPRESENTATIVE_DTLS>';
1154 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1155
1156 l_tag := pay_in_xml_utils.getTag('c_8_rep_name',rec_rep_details.rep_name);
1157 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1158
1159 l_tag := pay_in_xml_utils.getTag('c_8_rep_pos',replace_comma(rec_rep_details.rep_pos));
1160 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1161
1162 l_tag := '</REPRESENTATIVE_DTLS>';
1163 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1164
1165 END LOOP ;
1166
1167 l_tag := '</Level8>';
1168 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1169
1170
1171 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1172
1173 EXCEPTION
1174
1175 WHEN OTHERS THEN
1179 END create_level8_xml ;
1176 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
1177 RAISE;
1178
1180
1181 --------------------------------------------------------------------------
1182 -- --
1183 -- Name : CREATE_LEVEL1_XML --
1184 -- Type : PROCEDURE --
1185 -- Access : Public --
1186 -- Description : This procedure creates the level 1 Data --
1187 -- It generates the data related to Business Number --
1188 -- Gets the data from PAY_ACTION_INFORMATION --
1189 -- Context : IN_PF_BUSINESS_NUMBER --
1190 -- Calls create_level2_3_xml --
1191 -- create_level4_xml --
1192 -- create_level5_xml --
1193 -- create_level6_xml --
1194 -- create_level7_xml --
1195 -- create_level8_xml --
1196 -- --
1197 -- Parameters : --
1198 -- IN : p_business_number VARCHAR2 --
1199 -- p_pf_arc_ref_no VARCHAR2 --
1200 -- p_year NUMBER --
1201 -- p_month NUMBER --
1202 -- p_filer_license_no VARCHAR2 --
1203 -- p_nssn VARCHAR2 --
1204 -- p_sort_by VARCHAR2 --
1205 -- OUT : N/A --
1206 -- --
1207 -- Change History : --
1208 --------------------------------------------------------------------------
1212 --------------------------------------------------------------------------
1209 -- Rev# Date Userid Description --
1210 --------------------------------------------------------------------------
1211 -- 115.0 01-Aug-2007 rsaharay Initial Version --
1213 PROCEDURE create_level1_xml( p_business_number IN VARCHAR2 DEFAULT NULL
1214 ,p_pf_arc_ref_no IN VARCHAR2 DEFAULT NULL
1215 ,p_year IN NUMBER DEFAULT NULL
1216 ,p_month IN NUMBER DEFAULT NULL
1217 ,p_filer_license_no IN VARCHAR2 DEFAULT NULL
1218 ,p_nssn IN VARCHAR2 DEFAULT NULL
1219 ,p_sort_by IN VARCHAR2 DEFAULT NULL)
1220 IS
1221 l_sys_date_time VARCHAR2(40);
1222 l_tag VARCHAR2(1000);
1223 l_procedure VARCHAR2(100);
1224
1225 l_pf_org PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1226 l_ref_no PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1227 l_mth_yr PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1228 l_year PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1229 l_month PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1230 l_return_type PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1231 l_reg_comp_name PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1232 l_business_no PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1233 l_classification PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1234 l_representative_name PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1235 l_representative_desig PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1236 l_action_context_id pay_action_information.action_context_id%TYPE;
1237 l_pup PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1238
1239
1240 l_total_ncp NUMBER ;
1241
1242 CURSOR csr_pf_org
1243 IS
1244 SELECT DISTINCT
1245 action_information1 business_no
1246 ,action_information2 ref_no
1247 ,action_information3 mth_yr
1248 ,action_information4 return_type
1249 ,action_information5 reg_comp_name
1250 ,action_context_id action_context_id
1251 FROM pay_action_information
1252 WHERE action_information2 = p_pf_arc_ref_no
1256 AND action_context_type = 'PA';
1253 AND action_information1 = p_business_number
1254 AND action_information3 = p_month||p_year
1255 AND action_information_category = 'IN_PF_BUSINESS_NUMBER'
1257
1258 CURSOR csr_challan(p_action_context_id VARCHAR2)
1259 IS
1260 SELECT DISTINCT
1261 action_information20 pup
1262 FROM pay_action_information pai,
1263 pay_payroll_actions ppa
1264 WHERE pai.action_context_id = ppa.payroll_action_id
1265 AND ppa.action_type = 'X'
1266 AND ppa.action_status = 'C'
1267 AND ppa.report_type ='IN_PF_ARCHIVE'
1268 AND pai.action_context_id = p_action_context_id
1269 AND pai.action_information1 = p_business_number
1270 AND pai.action_information_category = 'IN_PF_CHALLAN'
1271 AND pai.action_context_type = 'PA';
1272
1273
1274
1275 BEGIN
1276 g_debug := hr_utility.debug_enabled;
1277 l_procedure := g_package ||'create_level1_xml';
1278 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1279
1280
1281 l_tag := '<Level1>';
1282 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1283 --
1284 l_sys_date_time:= TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1285 --System Date:
1286 l_tag :=pay_in_xml_utils.getTag('c_sys_date',l_sys_date_time);
1287 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1288
1289
1290 OPEN csr_pf_org;
1291 FETCH csr_pf_org INTO l_business_no
1292 ,l_ref_no
1293 ,l_mth_yr
1294 ,l_return_type
1295 ,l_reg_comp_name
1296 ,l_action_context_id ;
1297 CLOSE csr_pf_org ;
1298
1299 l_year := SUBSTR(TRIM(l_mth_yr) , (LENGTH(TRIM(l_mth_yr)) - 4)+1);
1300 l_month := SUBSTR(TRIM(l_mth_yr) , 1 , (LENGTH(TRIM(l_mth_yr)) - 4));
1301
1302 l_tag := pay_in_xml_utils.getTag('c_1_business_no',l_business_no);
1303 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1304
1305 l_tag := pay_in_xml_utils.getTag('c_1_name_of_comp',replace_comma(l_reg_comp_name));
1306 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1307
1308 l_tag := pay_in_xml_utils.getTag('c_1_month',LPAD(l_month,2,'0'));
1309 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1310
1311 l_tag := pay_in_xml_utils.getTag('c_1_year',l_year);
1312 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1313
1314 l_tag := pay_in_xml_utils.getTag('c_1_return_type',l_return_type);
1315 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1316
1317 l_pup := 'N';
1318 FOR rec_challan IN csr_challan(l_action_context_id)
1319 LOOP
1320 IF rec_challan.pup = 'Y'
1321 THEN
1322 l_pup := 'Y';
1323 EXIT ;
1324 END IF ;
1325 END LOOP ;
1326
1327 l_tag := pay_in_xml_utils.getTag('c_1_pup',l_pup);
1328 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1329
1330 pay_in_utils.set_location(g_debug,'Calling : create_level2_3_xml',1);
1331 create_level2_3_xml(p_business_number => p_business_number
1332 ,p_pf_arc_ref_no => p_pf_arc_ref_no
1333 ,p_action_context_id => l_action_context_id
1334 ,p_year => p_year
1335 ,p_month => p_month
1336 ,p_nssn => p_nssn
1337 ,p_sort_by => p_sort_by
1338 ,p_total_ncp => l_total_ncp
1339 );
1340 pay_in_utils.set_location(g_debug,'Calling : create_level4_xml',2);
1341 create_level4_xml(p_business_number => p_business_number
1342 ,p_action_context_id => l_action_context_id
1343 ,p_year => p_year
1344 ,p_month => p_month
1345 );
1346
1347 pay_in_utils.set_location(g_debug,'Calling : create_level5_xml',3);
1348 create_level5_xml(p_business_number => p_business_number
1349 ,p_action_context_id => l_action_context_id
1350 );
1351
1352 pay_in_utils.set_location(g_debug,'Calling : create_level6_xml',4);
1353 create_level6_xml(p_business_number => p_business_number
1357 pay_in_utils.set_location(g_debug,'Calling : create_level7_xml',5);
1354 ,p_action_context_id => l_action_context_id
1355 );
1356
1358 create_level7_xml(p_business_number => p_business_number
1359 ,p_action_context_id => l_action_context_id
1360 );
1361
1362 pay_in_utils.set_location(g_debug,'Calling : create_level8_xml',6);
1363 create_level8_xml(p_business_number => p_business_number
1364 ,p_action_context_id => l_action_context_id
1365 ,p_total_ncp => l_total_ncp
1366 ,p_filer_license_no => p_filer_license_no
1367 );
1368
1369 l_tag := '</Level1>';
1370 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1371
1372
1373
1374 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1375 EXCEPTION
1376
1377 WHEN OTHERS THEN
1378 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
1379 RAISE;
1380
1381 END create_level1_xml;
1382
1383 --------------------------------------------------------------------------
1384 -- --
1385 -- Name : INIT_CODE --
1386 -- Type : PROCEDURE --
1387 -- Access : Public --
1388 -- Description : This procedure calls procedure for PF Monthly --
1389 -- Reorts and EFile depending on the report type --
1390 -- parameter --
1391 -- April is archived as '04' and not '01' --
1392 -- Parameters : --
1393 -- IN : p_pf_business_no VARCHAR2 --
1394 -- p_pf_arc_ref_no VARCHAR2 --
1395 -- p_template_name VARCHAR2 --
1396 -- p_return_type VARCHAR2 --
1397 -- p_year VARCHAR2 --
1398 -- p_month VARCHAR2 --
1399 -- p_filer_license_no VARCHAR2 --
1400 -- p_nssn VARCHAR2 --
1401 -- p_sort_by VARCHAR2 --
1402 -- OUT : p_xml CLOB --
1403 -- --
1404 -- Change History : --
1405 --------------------------------------------------------------------------
1406 -- Rev# Date Userid Description --
1407 --------------------------------------------------------------------------
1408 -- 115.0 01-AUG-2007 rsaharay Initial Version --
1409 -- 115.1 24-OCT-2007 rsaharay Modified for Currency Period --
1410 -- 115.2 08-Jan-2009 mdubasi Modified the cursor csr_pf_bus_no
1411 --------------------------------------------------------------------------
1412 PROCEDURE init_code(p_pf_business_no IN VARCHAR2 DEFAULT NULL
1413 ,p_pf_arc_ref_no IN VARCHAR2 DEFAULT NULL
1414 ,p_template_name IN VARCHAR2
1415 ,p_xml OUT NOCOPY CLOB
1416 ,p_return_type IN VARCHAR2 DEFAULT NULL
1417 ,p_year IN VARCHAR2 DEFAULT NULL
1418 ,p_month IN VARCHAR2 DEFAULT NULL
1419 ,p_filer_license_no IN VARCHAR2 DEFAULT NULL
1420 ,p_nssn IN VARCHAR2 DEFAULT NULL
1421 ,p_sort_by IN VARCHAR2 DEFAULT NULL)
1422 IS
1423 l_effective_start_date DATE;
1424 l_effective_end_date DATE;
1425 l_message VARCHAR2(255);
1426 l_procedure VARCHAR2(100);
1427 l_tag VARCHAR2(1000);
1428
1429 l_pf_org PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE;
1430 l_year NUMBER(4);
1431 l_month NUMBER(2);
1432
1433 CURSOR csr_pf_bus_no(p_yr_mth VARCHAR2)
1434 IS
1435 SELECT DISTINCT
1436 pai.action_information1 bus_no
1437 FROM pay_action_information pai,
1438 pay_payroll_actions ppa
1439 WHERE pai.action_context_id = ppa.payroll_action_id
1440 AND ppa.action_type = 'X'
1441 AND ppa.action_status = 'C'
1442 AND ppa.report_type ='IN_PF_ARCHIVE'
1443 AND pai.action_context_type = 'PA'
1444 AND pai.action_information_category = 'IN_PF_BUSINESS_NUMBER'
1445 AND pai.action_information1 = NVL(p_pf_business_no,pai.action_information1)
1446 AND pai.action_information3 = p_yr_mth
1447 AND pai.action_information2 = p_pf_arc_ref_no
1448 AND ppa.business_group_id = g_bg_id;
1449
1450 BEGIN
1451 --
1452 g_debug := hr_utility.debug_enabled;
1453 l_procedure := g_package ||'init_code';
1454 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1455
1456 g_template := p_template_name ;
1457
1458 g_bg_id:=fnd_profile.value('PER_BUSINESS_GROUP_ID');
1459
1460 IF g_debug THEN
1461 pay_in_utils.trace('p_pf_business_no ',p_pf_business_no );
1462 pay_in_utils.trace('p_pf_arc_ref_no ',p_pf_arc_ref_no );
1463 pay_in_utils.trace('p_return_type ',p_return_type );
1464 pay_in_utils.trace('p_year ',p_year );
1465 pay_in_utils.trace('p_month ',p_month );
1466 pay_in_utils.trace('p_filer_license_no ',p_filer_license_no);
1467 pay_in_utils.trace('p_nssn ',p_nssn );
1468 pay_in_utils.trace('p_sort_by ',p_sort_by );
1469 pay_in_utils.trace('g_bg_id ',g_bg_id );
1470 END IF;
1471
1472 l_month := TO_NUMBER(p_month) + 3 ;
1473 l_year := SUBSTR(p_year,1,4);
1474 IF l_month > 12 THEN
1475 l_month := l_month - 12 ;
1476 l_year := TO_NUMBER(l_year) + 1;
1477 END IF ;
1478
1479 IF l_month = 3 THEN
1480 l_year := l_year - 1 ;
1481 END IF ;
1482
1483 dbms_lob.createtemporary(g_xml_data,FALSE,DBMS_LOB.CALL);
1484 dbms_lob.open(g_xml_data,dbms_lob.lob_readwrite);
1485 --
1486 l_tag :='<?xml version="1.0" encoding="UTF-8"?>';
1487 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1488
1489 l_tag := '<PFData_BusinessNo>';
1490 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1491
1492 FOR rec_pf_bus_no IN csr_pf_bus_no(l_month||l_year)
1493 LOOP
1494 create_level1_xml(p_business_number => rec_pf_bus_no.bus_no
1495 ,p_pf_arc_ref_no => p_pf_arc_ref_no
1496 ,p_year => l_year
1497 ,p_month => l_month
1498 ,p_filer_license_no => p_filer_license_no
1499 ,p_nssn => p_nssn
1500 ,p_sort_by => p_sort_by
1501 );
1502
1503
1504 END LOOP ;
1505
1506
1507 l_tag := '</PFData_BusinessNo>';
1508 dbms_lob.writeAppend(g_xml_data, length(l_tag), l_tag);
1509
1510
1511 p_xml := g_xml_data;
1512
1513 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
1514
1515 EXCEPTION
1516
1517 WHEN OTHERS THEN
1518 pay_in_utils.set_location(g_debug,'Error in : '||l_procedure,30);
1519 RAISE;
1520
1521
1522 END init_code;
1523
1524
1525 END ;