[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_SOE
Source
1 PACKAGE BODY pay_in_soe AS
2 /* $Header: pyinsoe.pkb 120.7.12010000.3 2008/09/17 15:08:35 rsaharay ship $ */
3 TYPE clob_tab_type IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
4
5 g_clob clob_tab_type;
6 g_tmp_clob CLOB;
7 g_clob_cnt NUMBER;
8 g_fetch_clob_cnt NUMBER;
9 g_chunk_size NUMBER;
10 g_business_group_id NUMBER;
11 g_package CONSTANT VARCHAR2(100) := 'pay_in_soe.';
12 g_debug BOOLEAN;
13
14 --------------------------------------------------------------------------
15 -- --
16 -- Name : GET_TEMPLATE --
17 -- Type : PROCEDURE --
18 -- Access : Public --
19 -- Description : This procedure gets the payslip template code set at--
20 -- organization level.If no template is set default --
21 -- template code is returned --
22 -- --
23 -- Parameters : --
24 -- IN : p_business_group_id NUMBER --
25 -- OUT : p_template VARCHAR2 --
26 --------------------------------------------------------------------------
27 --
28
29 PROCEDURE get_template (
30 p_business_group_id IN NUMBER
31 ,p_template OUT NOCOPY VARCHAR2
32 )
33 IS
34 --
35 CURSOR csr_payslip_info
36 IS
37 --
38 SELECT org_information7 template
39 ,org_information8 chunk_size
40 FROM hr_organization_information_v
41 WHERE organization_id = p_business_group_id
42 AND org_information_context= 'PER_IN_PRINTED_PAYSLIP';
43 --
44 l_template VARCHAR2(50);
45 l_chunk_size NUMBER;
46 l_procedure VARCHAR(100);
47 l_message VARCHAR2(250);
48
49 --
50 BEGIN
51 --
52 l_procedure := g_package || 'get_template';
53 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
54
55 IF g_debug THEN
56 pay_in_utils.trace('**************************************************','********************');
57 pay_in_utils.trace('p_business_group_id',p_business_group_id);
58 pay_in_utils.trace('**************************************************','********************');
59 END IF;
60
61
62
63 OPEN csr_payslip_info;
64 FETCH csr_payslip_info
65 INTO l_template
66 ,l_chunk_size;
67
68 -- If Organization level payslip information does not exists return default
69 -- Else return the information set
70 IF (csr_payslip_info%NOTFOUND) THEN
71 --
72 p_template := 'PAY_IN_PAYSLIP_TEMPLATE';
73 g_chunk_size := 500;
74 --
75 ELSE
76 --
77 pay_in_utils.trace('l_template ',l_template);
78 pay_in_utils.set_location(g_debug,l_procedure,20);
79 pay_in_utils.trace('g_chunk_size ',g_chunk_size);
80 pay_in_utils.set_location(g_debug,l_procedure,30);
81
82 p_template := l_template;
83 g_chunk_size := l_chunk_size;
84 --
85 END IF;
86 --
87 IF g_debug THEN
88 pay_in_utils.trace('**************************************************','********************');
89 pay_in_utils.trace('p_template',p_template);
90 pay_in_utils.trace('**************************************************','********************');
91 END IF;
92
93 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
98 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
99 pay_in_utils.trace(l_message,l_procedure);
100
101 IF csr_payslip_info%ISOPEN THEN
102 CLOSE csr_payslip_info;
103 END IF;
104 RAISE;
105 --
106 --
107 END get_template;
108
109
110 --------------------------------------------------------------------------
111 -- --
112 -- Name : APPEND_TAG --
113 -- Type : PROCEDURE --
114 -- Access : Private --
115 -- Description : This procedure appends a given tag and value to --
116 -- g_tmp_clob --
117 -- --
118 -- Parameters : --
119 -- IN : p_tag_name VARCHAR2 --
120 -- OUT : p_value VARCHAR2 --
121 --------------------------------------------------------------------------
122 --
123 PROCEDURE append_tag(p_tag_name VARCHAR2
124 ,p_value VARCHAR2)
125 IS
126 --
127 l_str VARCHAR2(400);
128 l_procedure VARCHAR(100);
129 l_message VARCHAR2(250);
130 --
131 BEGIN
132 --
133 l_procedure := g_package || 'append_tag';
134 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
135
136 IF g_debug THEN
137 pay_in_utils.trace('**************************************************','********************');
138 pay_in_utils.trace('p_tag_name',p_tag_name);
139 pay_in_utils.trace('p_value',p_value);
140 pay_in_utils.trace('**************************************************','********************');
141 END IF;
142
143 /*Bug 4070869 - Encoded the data*/
144 l_str:= '<'||p_tag_name||'>'||pay_in_utils.ENCODE_HTML_STRING(p_value)||'</'||p_tag_name||'>';
145
146 pay_in_utils.trace('l_str ',l_str);
147 pay_in_utils.set_location(g_debug,l_procedure,20);
148
149 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
150
151 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
152
153 END append_tag;
154
155 --------------------------------------------------------------------------
156 -- --
157 -- Name : OPEN_TAG --
158 -- Type : PROCEDURE --
159 -- Access : Private --
160 -- Description : This procedure appends a open tag to g_tmp_clob --
161 -- --
162 -- Parameters : --
163 -- IN : p_tag_name VARCHAR2 --
164 --------------------------------------------------------------------------
165 --
166 PROCEDURE open_tag(p_tag_name VARCHAR2)
167 IS
168 --
169 l_str VARCHAR2(250);
170 l_procedure VARCHAR(100);
171 l_message VARCHAR2(250);
172
173 --
174 BEGIN
175 --
176 l_procedure := g_package || 'open_tag';
177 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
178
179 IF g_debug THEN
180 pay_in_utils.trace ('**************************************************','********************');
181 pay_in_utils.trace ('p_tag_name',p_tag_name);
182 pay_in_utils.trace ('**************************************************','********************');
183 END IF;
184
185 l_str:= '<'||p_tag_name||'>';
186 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
187
188 pay_in_utils.trace('l_str ',l_str);
189 pay_in_utils.set_location(g_debug,l_procedure,20);
190
191 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
192
193 END open_tag;
194
195 --------------------------------------------------------------------------
196 -- --
197 -- Name : CLOSE_TAG --
198 -- Type : PROCEDURE --
199 -- Access : Private --
200 -- Description : This procedure appends a close tag to g_tmp_clob --
201 -- --
202 -- Parameters : --
203 -- IN : p_tag_name VARCHAR2 --
204 --------------------------------------------------------------------------
205 --
206 PROCEDURE close_tag(p_tag_name VARCHAR2)
207 IS
208 --
209 l_str VARCHAR2(250);
210 l_procedure VARCHAR(100);
211 l_message VARCHAR2(250);
212
213 --
214 BEGIN
215 --
216 l_procedure := g_package || 'close_tag';
217 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
218
219 IF g_debug THEN
220 pay_in_utils.trace ('**************************************************','********************');
221 pay_in_utils.trace ('p_tag_name',p_tag_name);
222 pay_in_utils.trace ('**************************************************','********************');
223 END IF;
224
225 l_str:= '</'||p_tag_name||'>';
226 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
227
228 pay_in_utils.trace('l_str ',l_str);
229 pay_in_utils.set_location(g_debug,l_procedure,20);
230
231 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
232
233 END close_tag;
234
235 --------------------------------------------------------------------------
236 -- --
237 -- Name : APPEND_ELEMENTS --
238 -- Type : PROCEDURE --
239 -- Access : Private --
240 -- Description : This procedure gets archive elements of the given --
241 -- classification name and append them to g_tmp_clob --
242 -- --
243 -- Parameters : --
244 -- IN : p_action_context_id NUMBER --
245 -- OUT : p_classification_name VARCHAR2 --
246 --------------------------------------------------------------------------
247 --
248 PROCEDURE append_elements(
249 p_action_context_id IN NUMBER
250 ,p_classification_name IN VARCHAR2
251 )
252 IS
253 --
254 CURSOR csr_elements_earnings
255 IS
256 --
257 SELECT DECODE(element_classification
258 ,'Earnings', element_reporting_name
259 ,'Paid Monetary Perquisite', SUBSTR(element_reporting_name, 0,
260 LENGTH(element_reporting_name) - 8)) ename
261 ,current_amount amt
262 FROM pay_apac_payslip_elements_v
263 WHERE action_context_id = p_action_context_id
264 AND (element_classification = p_classification_name
265 OR element_classification = 'Paid Monetary Perquisite');
266
267 CURSOR csr_elements
268 IS
269 --
270 SELECT element_reporting_name ename
271 ,current_amount amt
272 FROM pay_apac_payslip_elements_v
273 WHERE action_context_id = p_action_context_id
274 AND element_classification = p_classification_name;
275
276 l_total NUMBER;
277 l_rec_exists BOOLEAN;
278 l_classification_tag VARCHAR2(100);
279 l_procedure VARCHAR2(100);
280 l_message VARCHAR2(250);
281 --
282 BEGIN
283 --
284 l_procedure := g_package || 'append_elements';
285 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
286
287 IF g_debug THEN
288 pay_in_utils.trace ('**************************************************','********************');
289 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
290 pay_in_utils.trace ('p_classification_name',p_classification_name);
291 pay_in_utils.trace ('**************************************************','********************');
292 END IF;
293
294
295 l_total := 0;
296 l_rec_exists := FALSE;
297 IF p_classification_name = 'Employer Charges' THEN
298 --
299 l_classification_tag := 'ERCharges';
300 ELSIF p_classification_name = 'Fringe Benefits' THEN
301 --
302 l_classification_tag := 'FringeBenefits';
303
304 --
305 ELSE
306 --
307 l_classification_tag := p_classification_name;
308 --
309 END IF;
310 open_tag(l_classification_tag);
311
312 pay_in_utils.trace('l_classification_tag ',l_classification_tag);
313 pay_in_utils.set_location(g_debug,l_procedure,20);
314
315 IF (l_classification_tag = 'Earnings') THEN
316 FOR rec in csr_elements_earnings
317 LOOP
318 --
319 l_rec_exists := TRUE;
320 open_tag(l_classification_tag || 'Element');
321
322 pay_in_utils.trace('Element Name : ',rec.ename);
323 pay_in_utils.set_location(g_debug,l_procedure,30);
324 pay_in_utils.trace('This Pay : ',rec.amt);
325 pay_in_utils.set_location(g_debug,l_procedure,40);
326
327 append_tag('Description',rec.ename);
328 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
329 g_business_group_id
330 ,rec.amt));
331
332 close_tag(l_classification_tag || 'Element');
333 l_total := l_total + rec.amt;
334 --
335 END LOOP;
336
337 ELSE
338 FOR rec in csr_elements
339 LOOP
340 --
341 l_rec_exists := TRUE;
342 open_tag(l_classification_tag || 'Element');
343
344 pay_in_utils.trace('Element Name : ',rec.ename);
345 pay_in_utils.set_location(g_debug,l_procedure,30);
346 pay_in_utils.trace('This Pay : ',rec.amt);
347 pay_in_utils.set_location(g_debug,l_procedure,40);
348
349 append_tag('Description',rec.ename);
350 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
351 g_business_group_id
352 ,rec.amt));
353
354 close_tag(l_classification_tag || 'Element');
355 l_total := l_total + rec.amt;
356 --
357 END LOOP;
358 END IF;
359
360 pay_in_utils.trace('l_total : ',l_total);
361 pay_in_utils.set_location(g_debug,l_procedure,50);
362
363 IF l_rec_exists = FALSE THEN
364 --
365 open_tag(l_classification_tag|| 'Element');
366
367 append_tag('Description','No data exists');
368 append_tag('Amount','');
369
370 close_tag(l_classification_tag || 'Element');
371 --
372 END IF;
373
374 append_tag(l_classification_tag || 'Total',pay_us_employee_payslip_web.get_format_value(
375 g_business_group_id
376 ,l_total));
377
378 close_tag(l_classification_tag);
379
380
381 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
382
383 EXCEPTION
384 WHEN others THEN
388
385 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
386 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
387 pay_in_utils.trace(l_message,l_procedure);
389 IF csr_elements%ISOPEN THEN
390 CLOSE csr_elements;
391 END IF;
392 RAISE;
393 --
394 END append_elements;
395
396 --------------------------------------------------------------------------
397 -- --
398 -- Name : APPEND_NET_PAY --
399 -- Type : PROCEDURE --
400 -- Access : Private --
401 -- Description : This procedure gets net pay --
402 -- and appends them to g_tmp_clob --
403 -- --
404 -- Parameters : --
405 -- IN : p_action_context_id NUMBER --
406 --------------------------------------------------------------------------
407 --
408 PROCEDURE append_net_pay(
409 p_action_context_id IN NUMBER
410 )
411 IS
412 --
413 CURSOR csr_sum_amt(p_classification_name VARCHAR2)
414 IS
415 --
416 SELECT sum(current_amount) amt
417 FROM pay_apac_payslip_elements_v
418 WHERE action_context_id = p_action_context_id
419 AND element_classification = p_classification_name;
420
421 l_gross_earnings NUMBER;
422 l_gross_deductions NUMBER;
423 l_advances NUMBER;
424 l_fbenefits NUMBER;
425 l_mon_perks NUMBER;
426 l_net_pay NUMBER;
427 l_procedure VARCHAR2(100);
428 l_message VARCHAR2(250);
429 --
430 BEGIN
431 --
432 l_procedure := g_package || 'append_net_pay';
433 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
434
435 IF g_debug THEN
436 pay_in_utils.trace ('**************************************************','********************');
437 pay_in_utils.trace (' p_action_context_id', p_action_context_id);
438 pay_in_utils.trace ('**************************************************','********************');
439 END IF;
440
441
442 open_tag('NetPay');
443
444 OPEN csr_sum_amt('Earnings');
445 FETCH csr_sum_amt
446 INTO l_gross_earnings;
447 CLOSE csr_sum_amt;
448
449 OPEN csr_sum_amt('Advances');
450 FETCH csr_sum_amt
451 INTO l_advances;
452 CLOSE csr_sum_amt;
453
454 OPEN csr_sum_amt('Fringe Benefits');
455 FETCH csr_sum_amt
456 INTO l_fbenefits;
457 CLOSE csr_sum_amt;
458
459 OPEN csr_sum_amt('Paid Monetary Perquisite');
460 FETCH csr_sum_amt
461 INTO l_mon_perks;
462 CLOSE csr_sum_amt;
463
464 l_gross_earnings := l_gross_earnings + NVL(l_advances,0)+ NVL(l_fbenefits,0) + NVL(l_mon_perks,0);
465
466 pay_in_utils.trace('l_gross_earnings : ',l_gross_earnings);
467 pay_in_utils.set_location(g_debug,l_procedure,20);
468
469 append_tag('GrossEarnings',pay_us_employee_payslip_web.get_format_value(
470 g_business_group_id
471 ,l_gross_earnings));
472
473
474 OPEN csr_sum_amt('Deductions');
475 FETCH csr_sum_amt
476 INTO l_gross_deductions;
477 CLOSE csr_sum_amt;
478
479 l_gross_deductions := NVL(l_gross_deductions,0);
480 pay_in_utils.trace('l_gross_deductions ',l_gross_deductions);
481 pay_in_utils.set_location(g_debug,l_procedure,30);
482
483 append_tag('GrossDeductions',pay_us_employee_payslip_web.get_format_value(
484 g_business_group_id
485 ,l_gross_deductions));
486
487 l_net_pay:=l_gross_earnings - l_gross_deductions;
488
489 pay_in_utils.trace('l_net_pay ',l_net_pay);
490 pay_in_utils.set_location(g_debug,l_procedure,30);
491
492 append_tag('Pay',pay_us_employee_payslip_web.get_format_value(
493 g_business_group_id
494 ,l_net_pay));
495
496
497 close_tag('NetPay');
498
499 --
500 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
501
502 EXCEPTION
503 WHEN others THEN
504 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
505 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
506 pay_in_utils.trace(l_message,l_procedure);
507
508 IF csr_sum_amt%ISOPEN THEN
509 CLOSE csr_sum_amt;
510 END IF;
511 RAISE;
512 --
513 END append_net_pay;
514
515 --------------------------------------------------------------------------
516 -- --
517 -- Name : APPEND_BALANCES --
521 -- and appends them to g_tmp_clob --
518 -- Type : PROCEDURE --
519 -- Access : Private --
520 -- Description : This procedure gets the balances --
522 -- --
523 -- Parameters : --
524 -- IN : p_action_context_id NUMBER --
525 --------------------------------------------------------------------------
526 --
527 PROCEDURE append_balances(
528 p_action_context_id IN NUMBER
529 )
530 IS
531 --
532 CURSOR csr_balances
533 IS
534 --
535 SELECT narrative bname
536 ,ytd_amount ytd
537 FROM pay_apac_payslip_balances_v
538 WHERE action_context_id = p_action_context_id;
539
540 l_procedure VARCHAR2(100);
541 l_message VARCHAR2(250);
542 --
543 BEGIN
544 --
545 l_procedure := g_package ||'append_balances';
546 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
547
548 IF g_debug THEN
549 pay_in_utils.trace ('**************************************************','********************');
550 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
551 pay_in_utils.trace ('**************************************************','********************');
552 END IF;
553
554
555 open_tag('Balances');
556
557 FOR rec in csr_balances
558 LOOP
559 --
560 open_tag('Balance');
561
562 pay_in_utils.trace('Balance Name : ',rec.bname);
563 pay_in_utils.set_location(g_debug,l_procedure,20);
564 pay_in_utils.trace('YTD : ',rec.ytd);
565 pay_in_utils.set_location(g_debug,l_procedure,30);
566
567 append_tag('Description',rec.bname);
568 append_tag('YTD',pay_us_employee_payslip_web.get_format_value(
569 g_business_group_id
570 ,rec.ytd));
571
572 close_tag('Balance');
573 --
574 END LOOP;
575
576 close_tag('Balances');
577 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
578
579 EXCEPTION
580 WHEN others THEN
581 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
582 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
583 pay_in_utils.trace(l_message,l_procedure);
584
585 IF csr_balances%ISOPEN THEN
586 CLOSE csr_balances;
587 END IF;
588 RAISE;
589 --
590 END append_balances;
591
592 --------------------------------------------------------------------------
593 -- --
594 -- Name : APPEND_OTHER_ELEMENTS --
595 -- Type : PROCEDURE --
596 -- Access : Private --
597 -- Description : This procedure gets EMEA element --
598 -- and appends them to g_tmp_clob --
599 -- --
600 -- Parameters : --
601 -- IN : p_action_context_id NUMBER --
602 --------------------------------------------------------------------------
603 --
604 PROCEDURE append_other_elements(
605 p_action_context_id IN NUMBER
606 )
607 IS
608 --
609 CURSOR csr_other_elements
610 IS
611 --
612 SELECT narrative
613 ,amount
614 FROM pay_emea_usr_ele_action_info_v
615 WHERE action_context_id = p_action_context_id;
616
617 l_procedure VARCHAR2(100);
618 l_message VARCHAR2(250);
619 l_rec_exists BOOLEAN;
620 num_char EXCEPTION ;
621 PRAGMA EXCEPTION_INIT(num_char,-06502);
622 --
623 BEGIN
624 --
625 l_rec_exists := FALSE;
626 l_procedure := g_package ||'append_other_elements';
627 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
628
629 IF g_debug THEN
630 pay_in_utils.trace ('**************************************************','********************');
631 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
632 pay_in_utils.trace ('**************************************************','********************');
633 END IF;
634
635 open_tag('EMEAElements');
636
637 FOR rec in csr_other_elements
638 LOOP
639 --
640 l_rec_exists := TRUE;
641 open_tag('EMEAElement');
642
643
644 pay_in_utils.trace('Element Name : ',rec.narrative);
645 pay_in_utils.set_location(g_debug,l_procedure,20);
646 pay_in_utils.trace('Value : ',rec.amount);
647 pay_in_utils.set_location(g_debug,l_procedure,30);
648
652 append_tag('Value',pay_us_employee_payslip_web.get_format_value(
649 append_tag('Description',rec.narrative);
650
651 BEGIN
653 g_business_group_id
654 ,rec.amount));
655 EXCEPTION
656 WHEN num_char THEN
657 append_tag('Value',rec.amount);
658 END ;
659
660 close_tag('EMEAElement');
661 --
662 END LOOP;
663
664 IF l_rec_exists = FALSE THEN
665 --
666 open_tag('EMEAElement');
667
668 append_tag('Description','No Data Exists.');
669 append_tag('Value','');
670
671 close_tag('EMEAElement');
672 --
673 END IF;
674 close_tag('EMEAElements');
675
676 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
681 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
682 pay_in_utils.trace(l_message,l_procedure);
683
684 IF csr_other_elements%ISOPEN THEN
685 CLOSE csr_other_elements;
686 END IF;
687 RAISE;
688
689 END append_other_elements;
690
691 --------------------------------------------------------------------------
692 -- --
693 -- Name : APPEND_ACCRUALS --
694 -- Type : PROCEDURE --
695 -- Access : Private --
696 -- Description : This procedure gets accruals --
697 -- and appends them to g_tmp_clob --
698 -- --
699 -- Parameters : --
700 -- IN : p_action_context_id NUMBER --
701 --------------------------------------------------------------------------
702 --
703 PROCEDURE append_accruals(
704 p_action_context_id IN NUMBER
705 )
706 IS
707 --
708 CURSOR csr_accruals
709 IS
710 --
711 SELECT accrual_plan_name plan_name
712 ,uom
713 ,balance
714 FROM pay_apac_payslip_accruals_v
715 WHERE action_context_id = p_action_context_id;
716
717 l_procedure VARCHAR2(100);
718 l_message VARCHAR2(250);
719 l_rec_exists BOOLEAN;
720 --
721 BEGIN
722 --
723 l_procedure := g_package ||'append_accruals';
724 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
725
726 IF g_debug THEN
727 pay_in_utils.trace ('**************************************************','********************');
728 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
729 pay_in_utils.trace ('**************************************************','********************');
730 END IF;
731
732 l_rec_exists := FALSE;
733 open_tag('Accruals');
734
735 FOR rec in csr_accruals
736 LOOP
737 --
738 l_rec_exists := TRUE;
739 open_tag('AccrualPlan');
740
741 pay_in_utils.trace('Plan Name : ',rec.plan_name);
742 pay_in_utils.set_location(g_debug,l_procedure,20);
743 pay_in_utils.trace('UOM : ',rec.uom);
744 pay_in_utils.set_location(g_debug,l_procedure,30);
745 pay_in_utils.trace('Accrual Balance : ',rec.balance);
746 pay_in_utils.set_location(g_debug,l_procedure,40);
747
748 /* Bug 4218967 Changed the tag AccrBalance to Balance */
749 append_tag('PlanName',rec.plan_name);
750 append_tag('UOM',rec.uom);
751 append_tag('Balance',pay_us_employee_payslip_web.get_format_value(
752 g_business_group_id
753 ,rec.balance));
754
755 close_tag('AccrualPlan');
756 --
757 END LOOP;
758
759 IF l_rec_exists = FALSE THEN
760 --
761 open_tag('AccrualPlan');
762
763 append_tag('PlanName','No data exists.');
764 append_tag('UOM','');
765 append_tag('AccrBalance','');
766
767 close_tag('AccrualPlan');
768 --
769 END IF;
770 close_tag('Accruals');
771
772 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
773
774 EXCEPTION
775 WHEN others THEN
776 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
777 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
778 pay_in_utils.trace(l_message,l_procedure);
779
780 IF csr_accruals%ISOPEN THEN
781 CLOSE csr_accruals;
782 END IF;
783 RAISE;
784 --
785 END append_accruals;
786
787 --------------------------------------------------------------------------
788 -- --
792 -- Description : This procedure gets absences --
789 -- Name : APPEND_ABSENCES --
790 -- Type : PROCEDURE --
791 -- Access : Private --
793 -- and appends them to g_tmp_clob --
794 -- --
795 -- Parameters : --
796 -- IN : p_action_context_id NUMBER --
797 --------------------------------------------------------------------------
798 --
799 PROCEDURE append_absences(
800 p_action_context_id IN NUMBER
801 )
802 IS
803 --
804 CURSOR csr_absences
805 IS
806 --
807 SELECT absence_type absence_name
808 ,start_date
809 ,end_date
810 ,absence_value
811 FROM pay_apac_payslip_absences_v
812 WHERE action_context_id = p_action_context_id;
813
814 l_procedure VARCHAR2(100);
815 l_message VARCHAR2(250);
816 l_rec_exists BOOLEAN;
817 --
818 BEGIN
819 --
820 l_procedure := g_package || 'append_absences';
821 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
822
823 IF g_debug THEN
824 pay_in_utils.trace ('**************************************************','********************');
825 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
826 pay_in_utils.trace ('**************************************************','********************');
827 END IF;
828
829 l_rec_exists := FALSE;
830 open_tag('Absences');
831
832 FOR rec in csr_absences
833 LOOP
834 --
835 l_rec_exists := TRUE;
836 open_tag('Absence');
837
838 pay_in_utils.trace('Absence Name : ',rec.absence_name);
839 pay_in_utils.set_location(g_debug,l_procedure,20);
840 pay_in_utils.trace('Start Date : ',to_char(rec.start_date,'DD-MON-YYYY'));
841 pay_in_utils.set_location(g_debug,l_procedure,30);
842 pay_in_utils.trace('End Date : ',to_char(rec.end_date,'DD-MON-YYYY'));
843 pay_in_utils.set_location(g_debug,l_procedure,40);
844 pay_in_utils.trace('This Pay : ',rec.absence_value);
845 pay_in_utils.set_location(g_debug,l_procedure,50);
846
847 append_tag('AbsenceName',rec.absence_name);
848 append_tag('StartDate',to_char(rec.start_date,'DD-MON-YYYY'));
849 append_tag('EndDate',to_char(rec.end_date,'DD-MON-YYYY'));
850 append_tag('ThisPay',pay_us_employee_payslip_web.get_format_value(
851 g_business_group_id
852 ,rec.absence_value));
853
854 close_tag('Absence');
855 --
856 END LOOP;
857
858 IF l_rec_exists = FALSE THEN
859 --
860 open_tag('Absence');
861
862 append_tag('AbsenceName','No data exists.');
863 append_tag('StartDate','');
864 append_tag('EndDate','');
865 append_tag('ThisPay','');
866
867 close_tag('Absence');
868 --
869 END IF;
870 close_tag('Absences');
871
872 --
873 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
874
875 EXCEPTION
876 WHEN others THEN
877 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
878 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
879 pay_in_utils.trace(l_message,l_procedure);
880
881 IF csr_absences%ISOPEN THEN
882 CLOSE csr_absences;
883 END IF;
884 RAISE;
885 --
886 END append_absences;
887
888 --------------------------------------------------------------------------
889 -- --
890 -- Name : APPEND_MESSAGES --
891 -- Type : PROCEDURE --
892 -- Access : Private --
893 -- Description : This procedure gets messages --
894 -- and appends them to g_tmp_clob --
895 -- --
896 -- Parameters : --
897 -- IN : p_action_context_id NUMBER --
898 --------------------------------------------------------------------------
899 --
900 PROCEDURE append_messages(
901 p_action_context_id IN NUMBER
902 )
903 IS
904 --
905 CURSOR csr_messages
906 IS
907 --
908 SELECT DISTINCT info_type
909 ,name
910 ,value_type
911 ,value
912 FROM pay_emp_others_action_info_v
913 WHERE action_context_id = p_action_context_id
914 AND info_type = 'MESG';
915
916 l_procedure VARCHAR2(100);
917 l_message VARCHAR2(250);
921 l_procedure := g_package || 'append_messages';
918 --
919 BEGIN
920
922 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
923
924 IF g_debug THEN
925 pay_in_utils.trace('**************************************************','********************');
926 pay_in_utils.trace('p_action_context_id',p_action_context_id);
927 pay_in_utils.trace('**************************************************','********************');
928 END IF;
929
930
931 open_tag('Messages');
932 FOR rec in csr_messages
933 LOOP
934 --
935 open_tag('Mesg');
936
937 pay_in_utils.trace('Mesg : ',rec.value);
938 pay_in_utils.set_location(g_debug,l_procedure,20);
939
940 append_tag('Value',rec.value);
941
942 close_tag('Mesg');
943 --
944 END LOOP;
945 close_tag('Messages');
946
947 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
952 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
953 pay_in_utils.trace(l_message,l_procedure);
954
955 IF csr_messages%ISOPEN THEN
956 CLOSE csr_messages;
957 END IF;
958 RAISE;
959 --
960 END append_messages;
961
962 --------------------------------------------------------------------------
963 -- --
964 -- Name : APPEND_OTHER_BALANCES --
965 -- Type : PROCEDURE --
966 -- Access : Private --
967 -- Description : This procedure gets EMEA Elements --
968 -- and appends them to g_tmp_clob --
969 -- --
970 -- Parameters : --
971 -- IN : p_action_context_id NUMBER --
972 --------------------------------------------------------------------------
973 --
974 PROCEDURE append_other_balances(
975 p_action_context_id IN NUMBER
976 )
977 IS
978 --
979 CURSOR csr_other_balances
980 IS
981 --
982 SELECT narrative
983 ,value
984 FROM pay_apac_bals_action_info_v
985 WHERE action_context_id = p_action_context_id;
986
987 l_procedure VARCHAR2(100);
988 l_message VARCHAR2(250);
989 l_rec_exists BOOLEAN;
990 --
991 BEGIN
992 --
993 l_procedure := g_package || 'append_other_balances';
994 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
995
996 IF g_debug THEN
997 pay_in_utils.trace('**************************************************','********************');
998 pay_in_utils.trace('p_action_context_id',p_action_context_id);
999 pay_in_utils.trace('**************************************************','********************');
1000 END IF;
1001
1002 l_rec_exists := FALSE;
1003 open_tag('EMEABalances');
1004
1005 FOR rec in csr_other_balances
1006 LOOP
1007 --
1008 l_rec_exists := TRUE;
1009 open_tag('EMEABalance');
1010
1011 pay_in_utils.trace('Description : ',rec.narrative);
1012 pay_in_utils.set_location(g_debug,l_procedure,20);
1013 pay_in_utils.trace('Amount : ',rec.value);
1014 pay_in_utils.set_location(g_debug,l_procedure,30);
1015
1016
1017 append_tag('Description',rec.narrative);
1018 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
1019 g_business_group_id
1020 ,rec.value));
1021
1022 close_tag('EMEABalance');
1023 --
1024 END LOOP;
1025
1026 IF l_rec_exists = FALSE THEN
1027 --
1028 open_tag('EMEABalance');
1029
1030 append_tag('Description','No data exists.');
1031 append_tag('Amount','');
1032
1033 close_tag('EMEABalance');
1034 --
1035 END IF;
1036 close_tag('EMEABalances');
1037
1038 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1043 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1044 pay_in_utils.trace(l_message,l_procedure);
1045
1046 IF csr_other_balances%ISOPEN THEN
1047 CLOSE csr_other_balances;
1048 END IF;
1049 RAISE;
1050 --
1051 END append_other_balances;
1052
1053 --------------------------------------------------------------------------
1054 -- --
1055 -- Name : APPEND_PAYMENT_DETAILS --
1059 -- and appends them to g_tmp_clob --
1056 -- Type : PROCEDURE --
1057 -- Access : Private --
1058 -- Description : This procedure gets Payment Details --
1060 -- --
1061 -- Parameters : --
1062 -- IN : p_action_context_id NUMBER --
1063 --------------------------------------------------------------------------
1064 --
1065 PROCEDURE append_payment_details(
1066 p_action_context_id IN NUMBER
1067 )
1068 IS
1069 --
1070 CURSOR csr_payment_details
1071 IS
1072 --
1073 SELECT org_payment_method_name payment_method
1074 ,segment1 bank_name
1075 ,segment3 account_number
1076 ,value
1077 FROM pay_emp_net_dist_action_info_v pendv
1078 WHERE pendv.action_context_id = p_action_context_id;
1079
1080 l_procedure VARCHAR2(100);
1081 l_message VARCHAR2(250);
1082 l_rec_exists BOOLEAN;
1083 --
1084 BEGIN
1085 --
1086 l_procedure := g_package ||'append_payment_details';
1087 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1088
1089 IF g_debug THEN
1090 pay_in_utils.trace ('**************************************************','********************');
1091 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
1092 pay_in_utils.trace ('**************************************************','********************');
1093 END IF;
1094
1095 l_rec_exists := FALSE;
1096 open_tag('PaymentDetails');
1097
1098 FOR rec in csr_payment_details
1099 LOOP
1100 --
1101 l_rec_exists := TRUE;
1102 open_tag('Payment');
1103
1104 pay_in_utils.trace('Payment Method : ',rec.payment_method);
1105 pay_in_utils.set_location(g_debug,l_procedure,20);
1106 pay_in_utils.trace('Bank Name : ',rec.bank_name);
1107 pay_in_utils.set_location(g_debug,l_procedure,30);
1108 pay_in_utils.trace('Account Number : ',rec.account_number);
1109 pay_in_utils.set_location(g_debug,l_procedure,40);
1110 pay_in_utils.trace('This Pay : ',rec.value);
1111 pay_in_utils.set_location(g_debug,l_procedure,50);
1112
1113 append_tag('PaymentType',rec.payment_method);
1114 append_tag('Bank',rec.bank_name);
1115 append_tag('AccountNumber',rec.account_number);
1116 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
1117 g_business_group_id
1118 ,rec.value));
1119
1120 close_tag('Payment');
1121 --
1122 END LOOP;
1123
1124 IF l_rec_exists = FALSE THEN /*Added for bug#7383091 */
1125 --
1126 open_tag('Payment');
1127
1128 append_tag('PaymentType','No Data Exists.');
1129 append_tag('Bank','');
1130 append_tag('AccountNumber','');
1131 append_tag('Amount','');
1132
1133 close_tag('Payment');
1134 --
1135 END IF;
1136 close_tag('PaymentDetails');
1137
1138 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
1139
1140 EXCEPTION
1141 WHEN others THEN
1142 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1143 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
1144 pay_in_utils.trace(l_message,l_procedure);
1145
1146 IF csr_payment_details%ISOPEN THEN
1147 CLOSE csr_payment_details;
1148 END IF;
1149 RAISE;
1150 --
1151 END append_payment_details;
1152
1153 --------------------------------------------------------------------------
1154 -- --
1155 -- Name : FETCH_XML --
1156 -- Type : PROCEDURE --
1157 -- Access : Public --
1158 -- Description : This procedure returns the next CLOB available in --
1159 -- global CLOB array --
1160 -- --
1161 -- Parameters : --
1162 -- IN : N/A --
1163 -- OUT : p_clob CLOB --
1164 --------------------------------------------------------------------------
1165 --
1166 PROCEDURE fetch_xml (
1167 p_clob OUT NOCOPY CLOB
1168 )
1169 IS
1170 --
1171 l_procedure VARCHAR2(100);
1172 l_message VARCHAR2(250);
1173 --
1174 BEGIN
1175 --
1176
1177 l_procedure := g_package||'fetch_xml';
1178 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1179
1180
1181
1182 -- If Clobs exists return next clob else exit NULL
1186 p_clob := g_clob(g_fetch_clob_cnt);
1183 IF (g_clob_cnt <> 0 ) AND (g_fetch_clob_cnt < g_clob_cnt) THEN
1184 --
1185 g_fetch_clob_cnt := g_fetch_clob_cnt + 1;
1187 --
1188 ELSE
1189 --
1190 p_clob := null;
1191 --
1192 END IF;
1193 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1194
1195 END fetch_xml;
1196
1197 --------------------------------------------------------------------------
1198 -- --
1199 -- Name : LOAD_XML --
1200 -- Type : PROCEDURE --
1201 -- Access : Public --
1202 -- Description : This procedure makes a list of XMLs in a global --
1203 -- CLOB array --
1204 -- --
1205 -- Parameters : --
1206 -- IN : p_business_group_id NUMBER --
1207 -- p_start_date VARCHAR2 --
1208 -- p_end_date VARCHAR2 --
1209 -- p_payroll_id NUMBER --
1210 -- p_consolidation_set_id NUMBER --
1211 -- p_sort_order1 VARCHAR2 --
1212 -- p_sort_order2 VARCHAR2 --
1213 -- p_sort_order3 VARCHAR2 --
1214 -- p_sort_order4 VARCHAR2 --
1215 -- OUT : p_clob_cnt NUMBER --
1216 --------------------------------------------------------------------------
1217 --
1218 PROCEDURE load_xml (
1219 p_business_group_id IN NUMBER
1220 ,p_start_date IN VARCHAR2
1221 ,p_end_date IN VARCHAR2
1222 ,p_payroll_id IN NUMBER DEFAULT NULL
1223 ,p_consolidation_set_id IN NUMBER DEFAULT NULL
1224 ,p_sort_order1 IN VARCHAR2 DEFAULT NULL
1225 ,p_sort_order2 IN VARCHAR2 DEFAULT NULL
1226 ,p_sort_order3 IN VARCHAR2 DEFAULT NULL
1227 ,p_sort_order4 IN VARCHAR2 DEFAULT NULL
1228 ,p_clob_cnt OUT NOCOPY NUMBER
1229 )
1230 IS
1231 --
1232 --
1233 l_open_tag VARCHAR2(100);
1234 l_close_tag VARCHAR2(100);
1235 l_emp_open_tag VARCHAR2(100);
1236 l_emp_close_tag VARCHAR2(100);
1237 l_emp_cnt NUMBER;
1238 l_start_date DATE;
1239 l_end_date DATE;
1240 l_stmt VARCHAR2(5000);
1241 l_sort_by VARCHAR2(100);
1242 l_action_context_id NUMBER;
1243 l_sql_csr INTEGER;
1244 l_dummy INTEGER;
1245 l_employee_number VARCHAR2(240);
1246 l_assignment_number VARCHAR2(240);
1247 l_employer_name VARCHAR2(240);
1248 l_dob DATE;
1249 l_joining_date DATE;
1250 l_ptn VARCHAR2(240);
1251 l_pf_number VARCHAR2(240);
1252 l_esi_number VARCHAR2(240);
1253 l_emp_name VARCHAR2(240);
1254 l_pay_month VARCHAR2(240);
1255 l_er_location VARCHAR2(240);
1256 l_job VARCHAR2(240);
1257 l_position VARCHAR2(240);
1258 l_grade VARCHAR2(240);
1259 l_pan VARCHAR2(240);
1260 l_superannuation VARCHAR2(240);
1261 l_procedure VARCHAR2(100);
1262 l_message VARCHAR2(250);
1263 --
1264 BEGIN
1265 --
1266
1267 l_procedure := g_package ||'get_template';
1268 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1269
1270 IF g_debug THEN
1271 pay_in_utils.trace('**************************************************','********************');
1272 pay_in_utils.trace('p_business_group_id : ', p_business_group_id);
1273 pay_in_utils.trace('p_start_date : ', p_start_date);
1274 pay_in_utils.trace('p_end_date : ', p_end_date);
1275 pay_in_utils.trace('p_payroll_id : ', p_payroll_id);
1276 pay_in_utils.trace('p_consolidation_set_id : ', p_consolidation_set_id);
1277 pay_in_utils.trace('p_sort_order1 : ', p_sort_order1);
1278 pay_in_utils.trace('p_sort_order2 : ', p_sort_order2);
1279 pay_in_utils.trace('p_sort_order3 : ', p_sort_order3);
1280 pay_in_utils.trace('p_sort_order4 : ', p_sort_order4);
1281 pay_in_utils.trace('**************************************************','********************');
1282 END IF;
1283
1284
1285 l_start_date := fnd_date.canonical_to_date(p_start_date);
1286 l_end_date := fnd_date.canonical_to_date(p_end_date);
1287 g_business_group_id := p_business_group_id;
1288
1289 -- Construct SQL statement
1290 --
1291 l_stmt := ' SELECT piaav.assignment_action_id action_context_id
1292 ,peaiv.action_information10 employee_number
1296 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information11) joining_date
1293 ,peaiv.action_information14 assignment_number
1294 ,peaiv.action_information18 gre_name
1295 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information13) dob
1297 ,peaiv.action_information8 ptn
1298 ,peaiv.action_information24 pf_number
1299 ,peaiv.action_information6 esi_number
1300 ,peaiv.action_information1 emp_name
1301 ,peaiv.action_information23 pay_month
1302 ,peaiv.action_information30 er_location
1303 ,peaiv.action_information17 job
1304 ,peaiv.action_information19 position
1305 ,peaiv.action_information7 grade
1306 ,peaiv.action_information25 pan
1307 ,peaiv.action_information27 superannuation
1308 ,peaiv.action_information1 full_name
1309 ,peaiv.action_information30 location_name
1310 ,peaiv.action_information15 organization_name
1311 FROM pay_in_arch_actions_v piaav
1312 ,pay_action_information peaiv
1313 WHERE piaav.business_group_id = :bg_id
1314 AND piaav.effective_date BETWEEN :start_date
1315 AND :end_date
1316 AND ( :payroll_id = -1 OR :payroll_id = piaav.payroll_id)
1317 AND ( :cons_set_id = -1 OR :cons_set_id =
1318 piaav.consolidation_set_id)
1319 AND peaiv.action_context_id = piaav.assignment_action_id
1320 AND peaiv.action_context_type = ''AAP''
1321 AND peaiv.action_information_category = ''EMPLOYEE DETAILS'' ';
1322
1323 -- Construct the sort order
1324 --
1325 l_sort_by := NULL;
1326 IF p_sort_order1 IS NOT NULL THEN
1327 --
1328 l_sort_by := 'ORDER BY '||p_sort_order1;
1329
1330 IF p_sort_order2 IS NOT NULL THEN
1331 --
1332 l_sort_by := l_sort_by ||','||p_sort_order2;
1333 IF p_sort_order3 IS NOT NULL THEN
1334 --
1335 l_sort_by := l_sort_by ||','||p_sort_order3;
1336 IF p_sort_order4 IS NOT NULL THEN
1337 --
1338 l_sort_by := l_sort_by ||','||p_sort_order4;
1339 --
1340 END IF;
1341 --
1342 END IF;
1343 --
1344 END IF;
1345 --
1346 END IF;
1347
1348 l_emp_cnt := 0;
1349 l_open_tag := '<?xml version="1.0" encoding="UTF-8"?>
1350 <clob>';
1351
1352 -- Append sort order to SQL statement
1353 --
1354 l_stmt := l_stmt || l_sort_by;
1355
1356 pay_in_utils.trace('Before Open Cursor',20);
1357 l_sql_csr := dbms_sql.open_cursor;
1358
1359 pay_in_utils.set_location(g_debug,'Before parse',30);
1360 dbms_sql.parse(l_sql_csr,l_stmt,dbms_sql.native);
1361 dbms_sql.define_column(l_sql_csr,1,l_action_context_id);
1362 dbms_sql.define_column(l_sql_csr,2,l_employee_number,240);
1363 dbms_sql.define_column(l_sql_csr,3,l_assignment_number,240);
1364 dbms_sql.define_column(l_sql_csr,4,l_employer_name,240);
1365 dbms_sql.define_column(l_sql_csr,5,l_dob);
1366 dbms_sql.define_column(l_sql_csr,6,l_joining_date);
1367 dbms_sql.define_column(l_sql_csr,7,l_ptn,240);
1368 dbms_sql.define_column(l_sql_csr,8,l_pf_number,240);
1369 dbms_sql.define_column(l_sql_csr,9,l_esi_number,240);
1370 dbms_sql.define_column(l_sql_csr,10,l_emp_name,240);
1371 dbms_sql.define_column(l_sql_csr,11,l_pay_month,240);
1372 dbms_sql.define_column(l_sql_csr,12,l_er_location,240);
1373 dbms_sql.define_column(l_sql_csr,13,l_job,240);
1374 dbms_sql.define_column(l_sql_csr,14,l_position,240);
1375 dbms_sql.define_column(l_sql_csr,15,l_grade,240);
1376 dbms_sql.define_column(l_sql_csr,16,l_pan,240);
1377 dbms_sql.define_column(l_sql_csr,17,l_superannuation,240);
1378
1379 pay_in_utils.set_location(g_debug,'Before Bind',40);
1380 dbms_sql.bind_variable(l_sql_csr,':bg_id',p_business_group_id);
1381 dbms_sql.bind_variable(l_sql_csr,':start_date',l_start_date);
1382 dbms_sql.bind_variable(l_sql_csr,':end_date',l_end_date);
1383 dbms_sql.bind_variable(l_sql_csr,':payroll_id',p_payroll_id);
1384 dbms_sql.bind_variable(l_sql_csr,':cons_set_id',p_consolidation_set_id);
1385
1386 pay_in_utils.set_location(g_debug,'Before execute',50);
1387 l_dummy := dbms_sql.execute(l_sql_csr);
1388
1389 LOOP
1390 --
1391 -- Fetch next row
1392 --
1393 IF (dbms_sql.fetch_rows(l_sql_csr) <= 0) THEN
1394 --
1395 -- No More rows exist, Exit.
1396 EXIT;
1397 --
1398 END IF;
1399 dbms_sql.column_value(l_sql_csr,1,l_action_context_id);
1400 dbms_sql.column_value(l_sql_csr,2,l_employee_number);
1401 dbms_sql.column_value(l_sql_csr,3,l_assignment_number);
1402 dbms_sql.column_value(l_sql_csr,4,l_employer_name);
1403 dbms_sql.column_value(l_sql_csr,5,l_dob);
1404 dbms_sql.column_value(l_sql_csr,6,l_joining_date);
1405 dbms_sql.column_value(l_sql_csr,7,l_ptn);
1406 dbms_sql.column_value(l_sql_csr,8,l_pf_number);
1407 dbms_sql.column_value(l_sql_csr,9,l_esi_number);
1408 dbms_sql.column_value(l_sql_csr,10,l_emp_name);
1409 dbms_sql.column_value(l_sql_csr,11,l_pay_month);
1413 dbms_sql.column_value(l_sql_csr,15,l_grade);
1410 dbms_sql.column_value(l_sql_csr,12,l_er_location);
1411 dbms_sql.column_value(l_sql_csr,13,l_job);
1412 dbms_sql.column_value(l_sql_csr,14,l_position);
1414 dbms_sql.column_value(l_sql_csr,16,l_pan);
1415 dbms_sql.column_value(l_sql_csr,17,l_superannuation);
1416
1417 pay_in_utils.trace('l_action_context_id : ', l_action_context_id);
1418 pay_in_utils.set_location(g_debug,l_procedure,20);
1419 pay_in_utils.trace('l_employee_number : ', l_employee_number);
1420 pay_in_utils.set_location(g_debug,l_procedure,20);
1421 pay_in_utils.trace('l_assignment_number : ', l_assignment_number);
1422 pay_in_utils.set_location(g_debug,l_procedure,30);
1423 pay_in_utils.trace('l_dob : ', l_dob);
1424 pay_in_utils.set_location(g_debug,l_procedure,40);
1425 pay_in_utils.trace('l_joining_date : ', l_joining_date);
1426 pay_in_utils.set_location(g_debug,l_procedure,50);
1427 pay_in_utils.trace('l_ptn : ', l_ptn);
1428 pay_in_utils.set_location(g_debug,l_procedure,60);
1429 pay_in_utils.trace('l_pf_number : ', l_pf_number);
1430 pay_in_utils.set_location(g_debug,l_procedure,70);
1431 pay_in_utils.trace('l_esi_number : ', l_esi_number);
1432 pay_in_utils.set_location(g_debug,l_procedure,80);
1433 pay_in_utils.trace('l_pay_month : ', l_pay_month);
1434 pay_in_utils.set_location(g_debug,l_procedure,90);
1435 pay_in_utils.trace('l_job : ', l_job);
1436 pay_in_utils.set_location(g_debug,l_procedure,100);
1437 pay_in_utils.trace('l_position : ', l_position);
1438 pay_in_utils.set_location(g_debug,l_procedure,110);
1439 pay_in_utils.trace('l_grade : ', l_grade);
1440 pay_in_utils.set_location(g_debug,l_procedure,120);
1441 pay_in_utils.trace('l_emp_cnt : ', l_emp_cnt);
1442 pay_in_utils.set_location(g_debug,l_procedure,130);
1443
1444 IF ( l_emp_cnt = 0) OR ( l_emp_cnt >=g_chunk_size ) THEN
1445 --
1446 pay_in_utils.set_location(g_debug,'Inside If emp_cnt = 0 or emp_cnt > chunk_size',70);
1447
1448 -- If not the first employee close the previous clob
1449 -- Put the clob in the global CLOB array
1450 --
1451 IF ( l_emp_cnt <> 0) THEN
1452 --
1453 close_tag('clob');
1454 dbms_lob.close(g_tmp_clob);
1455 g_clob(g_clob_cnt):=g_tmp_clob;
1456 l_emp_cnt := 0;
1457 --
1458 END IF;
1459
1460 -- Increment global clob count
1461 --
1462 g_clob_cnt := g_clob_cnt + 1;
1463
1464 pay_in_utils.trace('g_clob_cnt : ', g_clob_cnt);
1465 pay_in_utils.set_location(g_debug,l_procedure,140);
1466
1467 -- Open a new CLOB
1468 --
1469 pay_in_utils.set_location(g_debug,'Before Create Temporary',150);
1470 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
1471
1472 pay_in_utils.set_location(g_debug,'Before Open',160);
1473 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
1474
1475 -- Append Open tags to new CLOB
1476 --
1477 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1478 --
1479 END IF;
1480
1481 pay_in_utils.set_location(g_debug,'Before employee tag',170);
1482
1483 -- Open Employee tag
1484 -- Append Details
1485 --
1486 open_tag('Employee');
1487 open_tag('PersonDetails');
1488 append_tag('EmpNumber',l_employee_number);
1489 append_tag('AssgNumber',l_assignment_number);
1490 append_tag('ERName',l_employer_name);
1491 append_tag('DOB',to_char(l_dob,'DD-MON-YYYY'));
1492 append_tag('JoiningDate',to_char(l_joining_date,'DD-MON-YYYY'));
1493 append_tag('PTN',l_ptn);
1494 append_tag('PFNumber',l_pf_number);
1495 append_tag('ESINumber',l_esi_number);
1496 append_tag('EmpName',l_emp_name);
1497 append_tag('PayMonth',l_pay_month);
1498 append_tag('ERLocation',l_er_location);
1499 append_tag('Job',l_job);
1500 append_tag('Position',l_position);
1501 append_tag('GRADE',l_grade); /* Bug 4218967 Changed the tag Grade to GRADE */
1502 append_tag('PAN',l_pan);
1503 append_tag('SuperAnnuation',l_superannuation);
1504 close_tag('PersonDetails');
1505
1506 append_elements(l_action_context_id,'Earnings');
1507 append_elements(l_action_context_id,'Deductions');
1508 append_elements(l_action_context_id,'Fringe Benefits');
1509 append_elements(l_action_context_id,'Advances');
1510 append_net_pay(l_action_context_id);
1511 append_elements(l_action_context_id,'Perquisites');
1512 append_elements(l_action_context_id,'Employer Charges');
1513 append_balances(l_action_context_id);
1514 append_payment_details(l_action_context_id);
1515 append_other_elements(l_action_context_id);
1516 append_other_balances(l_action_context_id);
1517 append_accruals(l_action_context_id);
1518 append_absences(l_action_context_id);
1519 append_messages(l_action_context_id);
1520
1521
1522 -- Close employee tag
1523 --
1524 close_tag('Employee');
1525 l_emp_cnt := l_emp_cnt + 1;
1526 pay_in_utils.set_location(g_debug,'After employee tag',180);
1527 --
1528 END LOOP;
1529
1530 pay_in_utils.set_location(g_debug,'Close cursor',190);
1531 dbms_sql.close_cursor(l_sql_csr);
1532
1533 -- Last CLOB is not yet closed
1534 -- So close it.
1535 IF ( g_clob_cnt <> 0) THEN
1536 --
1537 pay_in_utils.set_location(g_debug,'Closing last clob',200);
1538 close_tag('clob');
1539 dbms_lob.close(g_tmp_clob);
1540 g_clob(g_clob_cnt):=g_tmp_clob;
1541 --
1542
1543 END IF;
1544 p_clob_cnt := g_clob_cnt;
1545
1546 IF g_debug THEN
1547 pay_in_utils.trace('**************************************************','********************');
1548 pay_in_utils.trace('p_clob_cnt : ',p_clob_cnt);
1549 pay_in_utils.trace('**************************************************','********************');
1550 END IF;
1551
1552 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,220);
1553
1554 EXCEPTION
1555 WHEN others THEN
1556 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1557 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,230);
1558 pay_in_utils.trace(l_message,l_procedure);
1559
1560 IF dbms_sql.is_open(l_sql_csr) THEN
1561 dbms_sql.close_cursor(l_sql_csr);
1562 END IF;
1563 RAISE;
1564 --
1565 END load_xml;
1566 --
1567 BEGIN
1568 --
1569 -- Initialize Globals
1570 --
1571 g_clob_cnt := 0;
1572 g_fetch_clob_cnt := 0;
1573 g_chunk_size := 500;
1574 --
1575 END pay_in_soe;