[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_SOE
Source
1 PACKAGE BODY pay_in_soe AS
2 /* $Header: pyinsoe.pkb 120.21.12020000.3 2013/03/11 09:59:32 anchhetr 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 l_str1 VARCHAR2(400);
131 --
132 BEGIN
133 --
134 l_procedure := g_package || 'append_tag';
135 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
136
137 IF g_debug THEN
138 pay_in_utils.trace('**************************************************','********************');
139 pay_in_utils.trace('p_tag_name',p_tag_name);
140 pay_in_utils.trace('p_value',p_value);
141 pay_in_utils.trace('**************************************************','********************');
142 END IF;
143
144 /*Bug 14699505 - Replace invalid characters. Only characters between ascii 32 and 126 allowed*/
145 l_str1:=regexp_replace(p_value, '[^ -~]', '?');
146 pay_in_utils.trace('l_str1 ',l_str1);
147 IF(l_str1 <> p_value) THEN
148 fnd_file.put_line(fnd_file.log,'Invalid Character found in:'
149 ||p_tag_name||' -->'|| p_value);
150 fnd_file.put_line(fnd_file.log,'Invalid Character replaced with ?');
151 END IF;
152
153 /*Bug 4070869 - Encoded the data*/
154 l_str:= '<'||p_tag_name||'>'||pay_in_utils.ENCODE_HTML_STRING(l_str1)||'</'||p_tag_name||'>';
155
156 pay_in_utils.trace('l_str ',l_str);
157 pay_in_utils.set_location(g_debug,l_procedure,20);
158
159 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
160
161 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
162
163 END append_tag;
164
165 --------------------------------------------------------------------------
166 -- --
167 -- Name : OPEN_TAG --
168 -- Type : PROCEDURE --
169 -- Access : Private --
170 -- Description : This procedure appends a open tag to g_tmp_clob --
171 -- --
172 -- Parameters : --
173 -- IN : p_tag_name VARCHAR2 --
174 --------------------------------------------------------------------------
175 --
176 PROCEDURE open_tag(p_tag_name VARCHAR2)
177 IS
178 --
179 l_str VARCHAR2(250);
180 l_procedure VARCHAR(100);
181 l_message VARCHAR2(250);
182
183 --
184 BEGIN
185 --
186 l_procedure := g_package || 'open_tag';
187 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
188
189 IF g_debug THEN
190 pay_in_utils.trace ('**************************************************','********************');
191 pay_in_utils.trace ('p_tag_name',p_tag_name);
192 pay_in_utils.trace ('**************************************************','********************');
193 END IF;
194
195 l_str:= '<'||p_tag_name||'>';
196 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
197
198 pay_in_utils.trace('l_str ',l_str);
199 pay_in_utils.set_location(g_debug,l_procedure,20);
200
201 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
202
203 END open_tag;
204
205 --------------------------------------------------------------------------
206 -- --
207 -- Name : CLOSE_TAG --
208 -- Type : PROCEDURE --
209 -- Access : Private --
210 -- Description : This procedure appends a close tag to g_tmp_clob --
211 -- --
212 -- Parameters : --
213 -- IN : p_tag_name VARCHAR2 --
214 --------------------------------------------------------------------------
215 --
216 PROCEDURE close_tag(p_tag_name VARCHAR2)
217 IS
218 --
219 l_str VARCHAR2(250);
220 l_procedure VARCHAR(100);
221 l_message VARCHAR2(250);
222
223 --
224 BEGIN
225 --
226 l_procedure := g_package || 'close_tag';
227 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
228
229 IF g_debug THEN
230 pay_in_utils.trace ('**************************************************','********************');
231 pay_in_utils.trace ('p_tag_name',p_tag_name);
232 pay_in_utils.trace ('**************************************************','********************');
233 END IF;
234
235 l_str:= '</'||p_tag_name||'>';
236 dbms_lob.writeAppend(g_tmp_clob,length(l_str),l_str);
237
238 pay_in_utils.trace('l_str ',l_str);
239 pay_in_utils.set_location(g_debug,l_procedure,20);
240
241 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
242
243 END close_tag;
244
245 --------------------------------------------------------------------------
246 -- --
247 -- Name : SUBMIT_REQ_XML_BURST --
248 -- Type : PROCEDURE --
249 -- Access : Public --
250 -- Description : This function submits the CP XDOBURSTREP to burst --
251 -- XML --
252 -- --
253 -- Parameters : --
254 -- IN : p_request_id NUMBER --
255 --------------------------------------------------------------------------
256 --
257 PROCEDURE submit_req_xml_burst(p_request_id IN NUMBER)
258 IS
259 --
260 l_req_id NUMBER := 0;
261 l_set_layout BOOLEAN;
262 l_procedure VARCHAR2(100);
263 l_message VARCHAR2(250);
264 l_product_release VARCHAR2(50);
265 --
266 BEGIN
267 --
268 l_procedure := g_package || 'submit_req_xml_burst';
269 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
270
271 IF g_debug THEN
272 pay_in_utils.trace('**************************************************','********************');
273 pay_in_utils.trace('p_request_id',TO_CHAR(p_request_id));
274 pay_in_utils.trace('**************************************************','********************');
275 END IF;
276
277 UPDATE fnd_concurrent_requests
278 SET output_file_type = 'XML'
279 WHERE request_id = p_request_id;
280
281 COMMIT ;
282
283 SELECT substr(p.product_version,1,2) INTO l_product_release
284 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
285 WHERE a.application_id = p.application_id
286 AND a.application_id = t.application_id
287 AND t.language = Userenv ('LANG')
288 AND Substr (a.application_short_name, 1, 5) = 'PAY';
289
290 l_set_layout := fnd_request.add_layout('XDO','BURST_STATUS_REPORT','en','US','PDF');
291
292 /* The CP definition for 11i and R12 is different. So pass correct number of parameters
293 for 11i and R12 accordingly */
294 IF TO_NUMBER(l_product_release) = 11 THEN
295 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOBURSTREP',NULL,NULL,FALSE,p_request_id,'N');
296 ELSE
297 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOBURSTREP',NULL,NULL,FALSE,'Y',p_request_id,'N');
298 END IF;
299
300
301 END submit_req_xml_burst;
302
303 --------------------------------------------------------------------------
304 -- --
305 -- Name : GET_ADDRESS_DETAILS --
306 -- Type : FUNCTION --
307 -- Access : Private --
308 -- Description : This procedure gets the employee address details --
309 -- --
310 -- Parameters : --
311 -- IN : p_location_id per_addresses.address_id --
312 -- : p_concatenate VARCHAR2 --
313 -- p_field VARCHAR2 --
314 --------------------------------------------------------------------------
315 FUNCTION get_address_details ( p_address_id IN per_addresses.address_id%TYPE
316 , p_concatenate IN VARCHAR2 DEFAULT 'N'
317 , p_field IN VARCHAR2 DEFAULT NULL
318 )
319 RETURN VARCHAR2
320 IS
321
322 CURSOR csr_get_address_details
323 IS
324 SELECT pad.address_line1
325 , pad.address_line2
326 , pad.address_line3
327 , pad.add_information13
328 , pad.add_information14
329 , hr_general.decode_lookup('IN_STATES',pad.add_information15)
330 , hr_general.decode_lookup('PER_US_COUNTRY_CODE',pad.country)
331 , pad.postal_code
332 FROM per_addresses pad
333 WHERE pad.address_id = p_address_id;
334
335 l_procedure VARCHAR2(100);
336 l_location_address1 hr_locations.address_line_1%TYPE;
337 l_location_address2 hr_locations.address_line_2%TYPE;
338 l_location_address3 hr_locations.address_line_3%TYPE;
339 l_location_address4 hr_locations.loc_information14%TYPE;
340 l_location_city hr_locations.loc_information15%TYPE;
341 l_location_state hr_locations.loc_information16%TYPE;
342 l_location_country hr_locations.country%TYPE;
343 l_location_zipcode hr_locations.postal_code%TYPE;
344 l_details VARCHAR2(1000);
345
346 BEGIN
347 g_debug := hr_utility.debug_enabled;
348 l_procedure := g_package ||'get_location_details';
349 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
350
351 IF g_debug THEN
352 pay_in_utils.trace('**************************************************','********************');
353 pay_in_utils.trace('p_address_id ',p_address_id );
354 pay_in_utils.trace('p_concatenate',p_concatenate);
355 pay_in_utils.trace('p_field' ,p_field );
356 pay_in_utils.trace('**************************************************','********************');
357 END IF;
358
359 OPEN csr_get_address_details;
360 FETCH csr_get_address_details
361 INTO l_location_address1
362 , l_location_address2
363 , l_location_address3
364 , l_location_address4
365 , l_location_city
366 , l_location_state
367 , l_location_country
368 , l_location_zipcode;
369 CLOSE csr_get_address_details;
370
371 IF p_concatenate = 'Y' THEN
372 SELECT l_location_address1 || DECODE(l_location_address1,NULL,NULL,',' || fnd_global.local_chr(10)) ||
373 l_location_address2 || DECODE(l_location_address2,NULL,NULL,',' || fnd_global.local_chr(10)) ||
374 l_location_address3 || DECODE(l_location_address3,NULL,NULL,',' || fnd_global.local_chr(10)) ||
375 l_location_address4 || DECODE(l_location_address4,NULL,NULL,',' || fnd_global.local_chr(10)) ||
376 l_location_city || DECODE(l_location_city ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
377 l_location_state || DECODE(l_location_state ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
378 l_location_zipcode || DECODE(l_location_zipcode ,NULL,NULL,',' || fnd_global.local_chr(10)) ||
379 l_location_country
380 INTO l_details
381 FROM DUAL;
382
383 ELSIF p_field = 'ADDRESS1' THEN
384 l_details := l_location_address1;
385 ELSIF p_field = 'ADDRESS2' THEN
386 l_details := l_location_address2;
387 ELSIF p_field = 'ADDRESS3' THEN
388 l_details := l_location_address3;
389 ELSIF p_field = 'ADDRESS4' THEN
390 l_details := l_location_address4;
391 ELSIF p_field = 'CITY' THEN
392 l_details := l_location_city;
393 ELSIF p_field = 'STATE' THEN
394 l_details := l_location_state;
395 ELSIF p_field = 'POSTAL_CODE' THEN
396 l_details := l_location_zipcode;
397 ELSIF p_field = 'COUNTRY' THEN
398 l_details := l_location_country;
399 END IF;
400
401 l_details :=RTRIM(l_details,','||fnd_global.local_chr(10));
402
403
404
405 IF g_debug THEN
406 pay_in_utils.trace('l_details',l_details);
407 END IF;
408
409 pay_in_utils.set_location(g_debug,'LEAVING: '||l_procedure,20);
410 RETURN l_details;
411
412 END get_address_details;
413
414 --------------------------------------------------------------------------
415 -- --
416 -- Name : GET_EMP_ADDRESS --
417 -- Type : FUNCTION --
418 -- Access : Public --
419 -- Description : This function returns Emp Address --
420 -- --
421 -- Parameters : --
422 -- IN : p_person_id NUMBER --
423 -- IN : p_date DATE --
424 --------------------------------------------------------------------------
425 --
426 FUNCTION get_emp_address(p_person_id NUMBER
427 ,p_date DATE )
428 RETURN VARCHAR2 IS
429 --
430 l_procedure VARCHAR2(100);
431 l_message VARCHAR2(250);
432 l_emp_addr VARCHAR2(500) := NULL ;
433 l_emp_addr_id per_addresses.address_id%TYPE ;
434
435 CURSOR c_employee_address
436 IS
437 SELECT pa.address_id
438 FROM per_addresses pa
439 WHERE pa.person_id = p_person_id
440 AND pa.address_type = DECODE(pa.address_type,'IN_P','IN_P','IN_C')
441 AND TO_DATE(p_date) BETWEEN pa.date_from AND nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'))
442 ORDER BY address_type DESC;
443
444 --
445 BEGIN
446 --
447 l_procedure := g_package || 'get_emp_address';
448 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
449
450 IF g_debug THEN
451 pay_in_utils.trace('**************************************************','********************');
452 pay_in_utils.trace('p_person_id',TO_CHAR(p_person_id));
453 pay_in_utils.trace('p_date ',TO_CHAR(p_date));
454 pay_in_utils.trace('**************************************************','********************');
455 END IF;
456
457 OPEN c_employee_address;
458 FETCH c_employee_address INTO l_emp_addr_id ;
459 CLOSE c_employee_address ;
460
461
462 l_emp_addr := get_address_details(l_emp_addr_id,'Y','NULL');
463
464 pay_in_utils.set_location(g_debug,'l_emp_addr: '||l_emp_addr,10);
465 RETURN l_emp_addr;
466
467 END get_emp_address;
468
469 --------------------------------------------------------------------------
470 -- --
471 -- Name : GET_EMP_EMAIL --
472 -- Type : FUNCTION --
473 -- Access : Public --
474 -- Description : This function returns Employee Email Id --
475 -- --
476 -- Parameters : --
477 -- IN : p_assign_action_id NUMBER --
478 --------------------------------------------------------------------------
479
480 FUNCTION get_emp_email(p_assign_action_id NUMBER)
481 RETURN VARCHAR2 IS
482 l_procedure VARCHAR2(100);
483 l_message VARCHAR2(250);
484 l_emp_email_id per_people_f.email_address%TYPE;
485
486 CURSOR c_emp_email_id
487 IS
488 SELECT pai.action_information1
489 FROM pay_action_information pai
490 WHERE action_information_category = 'IN_EMPLOYEE_DETAILS'
491 AND action_context_id = p_assign_action_id;
492
493 --
494 BEGIN
495 --
496 l_procedure := g_package || 'get_emp_email';
497 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
498
499 IF g_debug THEN
500 pay_in_utils.trace('**************************************************','********************');
501 pay_in_utils.trace('p_assign_action_id',TO_CHAR(p_assign_action_id));
502 pay_in_utils.trace('**************************************************','********************');
503 END IF;
504
505 OPEN c_emp_email_id;
506 FETCH c_emp_email_id INTO l_emp_email_id ;
507
508 IF c_emp_email_id%NOTFOUND THEN
509 l_emp_email_id := '';
510 pay_in_utils.set_location(g_debug,l_procedure,20);
511 END IF;
512 CLOSE c_emp_email_id;
513
514 RETURN l_emp_email_id;
515
516 END get_emp_email;
517
518
519
520 --------------------------------------------------------------------------
521 -- --
522 -- Name : APPEND_ELEMENTS --
523 -- Type : PROCEDURE --
524 -- Access : Private --
525 -- Description : This procedure gets archive elements of the given --
526 -- classification name and append them to g_tmp_clob --
527 -- --
528 -- Parameters : --
529 -- IN : p_action_context_id NUMBER --
530 -- OUT : p_classification_name VARCHAR2 --
531 --------------------------------------------------------------------------
532 --
533 PROCEDURE append_elements(
534 p_action_context_id IN NUMBER
535 ,p_classification_name IN VARCHAR2
536 )
537 IS
538 --
539 CURSOR csr_elements_earnings
540 IS
541 --
542 SELECT DECODE(element_classification
543 ,'Earnings', element_reporting_name
544 ,'Paid Monetary Perquisite', SUBSTR(element_reporting_name, 0,
545 LENGTH(element_reporting_name) - 8)) ename
546 ,current_amount amt
547 FROM pay_apac_payslip_elements_v
548 WHERE action_context_id = p_action_context_id
549 AND (element_classification = p_classification_name
550 OR element_classification = 'Paid Monetary Perquisite');
551
552 CURSOR csr_elements
553 IS
554 --
555 SELECT element_reporting_name ename
556 ,current_amount amt
557 FROM pay_apac_payslip_elements_v
558 WHERE action_context_id = p_action_context_id
559 AND element_classification = p_classification_name;
560
561 l_total NUMBER;
562 l_rec_exists BOOLEAN;
563 l_classification_tag VARCHAR2(100);
564 l_procedure VARCHAR2(100);
565 l_message VARCHAR2(250);
566 --
567 BEGIN
568 --
569 l_procedure := g_package || 'append_elements';
570 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
571
572 IF g_debug THEN
573 pay_in_utils.trace ('**************************************************','********************');
574 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
575 pay_in_utils.trace ('p_classification_name',p_classification_name);
576 pay_in_utils.trace ('**************************************************','********************');
577 END IF;
578
579
580 l_total := 0;
581 l_rec_exists := FALSE;
582 IF p_classification_name = 'Employer Charges' THEN
583 --
584 l_classification_tag := 'ERCharges';
585 ELSIF p_classification_name = 'Fringe Benefits' THEN
586 --
587 l_classification_tag := 'FringeBenefits';
588
589 --
590 ELSE
591 --
592 l_classification_tag := p_classification_name;
593 --
594 END IF;
595 open_tag(l_classification_tag);
596
597 pay_in_utils.trace('l_classification_tag ',l_classification_tag);
598 pay_in_utils.set_location(g_debug,l_procedure,20);
599
600 IF (l_classification_tag = 'Earnings') THEN
601 FOR rec in csr_elements_earnings
602 LOOP
603 --
604 l_rec_exists := TRUE;
605 open_tag(l_classification_tag || 'Element');
606
607 pay_in_utils.trace('Element Name : ',rec.ename);
608 pay_in_utils.set_location(g_debug,l_procedure,30);
609 pay_in_utils.trace('This Pay : ',rec.amt);
610 pay_in_utils.set_location(g_debug,l_procedure,40);
611
612 append_tag('Description',rec.ename);
613 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
614 g_business_group_id
615 ,rec.amt));
616
617 close_tag(l_classification_tag || 'Element');
618 l_total := l_total + rec.amt;
619 --
620 END LOOP;
621
622 ELSE
623 FOR rec in csr_elements
624 LOOP
625 --
626 l_rec_exists := TRUE;
627 open_tag(l_classification_tag || 'Element');
628
629 pay_in_utils.trace('Element Name : ',rec.ename);
630 pay_in_utils.set_location(g_debug,l_procedure,30);
631 pay_in_utils.trace('This Pay : ',rec.amt);
632 pay_in_utils.set_location(g_debug,l_procedure,40);
633
634 append_tag('Description',rec.ename);
635 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
636 g_business_group_id
637 ,rec.amt));
638
639 close_tag(l_classification_tag || 'Element');
640 l_total := l_total + rec.amt;
641 --
642 END LOOP;
643 END IF;
644
645 pay_in_utils.trace('l_total : ',l_total);
646 pay_in_utils.set_location(g_debug,l_procedure,50);
647
648 IF l_rec_exists = FALSE THEN
649 --
650 open_tag(l_classification_tag|| 'Element');
651
652 append_tag('Description','No data exists');
653 append_tag('Amount','');
654
655 close_tag(l_classification_tag || 'Element');
656 --
657 END IF;
658
659 append_tag(l_classification_tag || 'Total',pay_us_employee_payslip_web.get_format_value(
660 g_business_group_id
661 ,l_total));
662
663 close_tag(l_classification_tag);
664
665
666 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
667
668 EXCEPTION
669 WHEN others THEN
670 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
671 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
672 pay_in_utils.trace(l_message,l_procedure);
673
674 IF csr_elements%ISOPEN THEN
675 CLOSE csr_elements;
676 END IF;
677 RAISE;
678 --
679 END append_elements;
680
681 --------------------------------------------------------------------------
682 -- --
683 -- Name : APPEND_NET_PAY --
684 -- Type : PROCEDURE --
685 -- Access : Private --
686 -- Description : This procedure gets net pay --
687 -- and appends them to g_tmp_clob --
688 -- --
689 -- Parameters : --
690 -- IN : p_action_context_id NUMBER --
691 --------------------------------------------------------------------------
692 --
693 PROCEDURE append_net_pay(
694 p_action_context_id IN NUMBER
695 )
696 IS
697 --
698 CURSOR csr_sum_amt(p_classification_name VARCHAR2)
699 IS
700 --
701 SELECT sum(current_amount) amt
702 FROM pay_apac_payslip_elements_v
703 WHERE action_context_id = p_action_context_id
704 AND element_classification = p_classification_name;
705
706 l_gross_earnings NUMBER;
707 l_gross_deductions NUMBER;
708 l_advances NUMBER;
709 l_fbenefits NUMBER;
710 l_mon_perks NUMBER;
711 l_net_pay NUMBER;
712 l_procedure VARCHAR2(100);
713 l_message VARCHAR2(250);
714 --
715 BEGIN
716 --
717 l_procedure := g_package || 'append_net_pay';
718 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
719
720 IF g_debug THEN
721 pay_in_utils.trace ('**************************************************','********************');
722 pay_in_utils.trace (' p_action_context_id', p_action_context_id);
723 pay_in_utils.trace ('**************************************************','********************');
724 END IF;
725
726
727 open_tag('NetPay');
728
729 OPEN csr_sum_amt('Earnings');
730 FETCH csr_sum_amt
731 INTO l_gross_earnings;
732 CLOSE csr_sum_amt;
733
734 OPEN csr_sum_amt('Advances');
735 FETCH csr_sum_amt
736 INTO l_advances;
737 CLOSE csr_sum_amt;
738
739 OPEN csr_sum_amt('Fringe Benefits');
740 FETCH csr_sum_amt
741 INTO l_fbenefits;
742 CLOSE csr_sum_amt;
743
744 OPEN csr_sum_amt('Paid Monetary Perquisite');
745 FETCH csr_sum_amt
746 INTO l_mon_perks;
747 CLOSE csr_sum_amt;
748
749 l_gross_earnings := l_gross_earnings + NVL(l_advances,0)+ NVL(l_fbenefits,0) + NVL(l_mon_perks,0);
750
751 pay_in_utils.trace('l_gross_earnings : ',l_gross_earnings);
752 pay_in_utils.set_location(g_debug,l_procedure,20);
753
754 append_tag('GrossEarnings',pay_us_employee_payslip_web.get_format_value(
755 g_business_group_id
756 ,l_gross_earnings));
757
758
759 OPEN csr_sum_amt('Deductions');
760 FETCH csr_sum_amt
761 INTO l_gross_deductions;
762 CLOSE csr_sum_amt;
763
764 l_gross_deductions := NVL(l_gross_deductions,0);
765 pay_in_utils.trace('l_gross_deductions ',l_gross_deductions);
766 pay_in_utils.set_location(g_debug,l_procedure,30);
767
768 append_tag('GrossDeductions',pay_us_employee_payslip_web.get_format_value(
769 g_business_group_id
770 ,l_gross_deductions));
771
772 l_net_pay:=l_gross_earnings - l_gross_deductions;
773
774 pay_in_utils.trace('l_net_pay ',l_net_pay);
775 pay_in_utils.set_location(g_debug,l_procedure,30);
776
777 append_tag('Pay',pay_us_employee_payslip_web.get_format_value(
778 g_business_group_id
779 ,l_net_pay));
780
781
782 close_tag('NetPay');
783
784 --
785 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
786
787 EXCEPTION
788 WHEN others THEN
789 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
790 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
791 pay_in_utils.trace(l_message,l_procedure);
792
793 IF csr_sum_amt%ISOPEN THEN
794 CLOSE csr_sum_amt;
795 END IF;
796 RAISE;
797 --
798 END append_net_pay;
799
800 --------------------------------------------------------------------------
801 -- --
802 -- Name : APPEND_BALANCES --
803 -- Type : PROCEDURE --
804 -- Access : Private --
805 -- Description : This procedure gets the balances --
806 -- and appends them to g_tmp_clob --
807 -- --
808 -- Parameters : --
809 -- IN : p_action_context_id NUMBER --
810 --------------------------------------------------------------------------
811 --
812 PROCEDURE append_balances(
813 p_action_context_id IN NUMBER
814 )
815 IS
816 --
817 CURSOR csr_balances
818 IS
819 --
820 SELECT narrative bname
821 ,ytd_amount ytd
822 FROM pay_apac_payslip_balances_v
823 WHERE action_context_id = p_action_context_id;
824
825 l_procedure VARCHAR2(100);
826 l_message VARCHAR2(250);
827 --
828 BEGIN
829 --
830 l_procedure := g_package ||'append_balances';
831 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
832
833 IF g_debug THEN
834 pay_in_utils.trace ('**************************************************','********************');
835 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
836 pay_in_utils.trace ('**************************************************','********************');
837 END IF;
838
839
840 open_tag('Balances');
841
842 FOR rec in csr_balances
843 LOOP
844 --
845 open_tag('Balance');
846
847 pay_in_utils.trace('Balance Name : ',rec.bname);
848 pay_in_utils.set_location(g_debug,l_procedure,20);
849 pay_in_utils.trace('YTD : ',rec.ytd);
850 pay_in_utils.set_location(g_debug,l_procedure,30);
851
852 append_tag('Description',rec.bname);
853 append_tag('YTD',pay_us_employee_payslip_web.get_format_value(
854 g_business_group_id
855 ,rec.ytd));
856
857 close_tag('Balance');
858 --
859 END LOOP;
860
861 close_tag('Balances');
862 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
863
864 EXCEPTION
865 WHEN others THEN
866 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
867 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
868 pay_in_utils.trace(l_message,l_procedure);
869
870 IF csr_balances%ISOPEN THEN
871 CLOSE csr_balances;
872 END IF;
873 RAISE;
874 --
875 END append_balances;
876
877 --------------------------------------------------------------------------
878 -- --
879 -- Name : APPEND_OTHER_ELEMENTS --
880 -- Type : PROCEDURE --
881 -- Access : Private --
882 -- Description : This procedure gets EMEA element --
883 -- and appends them to g_tmp_clob --
884 -- --
885 -- Parameters : --
886 -- IN : p_action_context_id NUMBER --
887 --------------------------------------------------------------------------
888 --
889 PROCEDURE append_other_elements(
890 p_action_context_id IN NUMBER
891 )
892 IS
893 --
894 CURSOR csr_other_elements
895 IS
896 --
897 SELECT narrative
898 ,amount
899 FROM pay_emea_usr_ele_action_info_v
900 WHERE action_context_id = p_action_context_id;
901
902 l_procedure VARCHAR2(100);
903 l_message VARCHAR2(250);
904 l_rec_exists BOOLEAN;
905 num_char EXCEPTION ;
906 PRAGMA EXCEPTION_INIT(num_char,-06502);
907 --
908 BEGIN
909 --
910 l_rec_exists := FALSE;
911 l_procedure := g_package ||'append_other_elements';
912 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
913
914 IF g_debug THEN
915 pay_in_utils.trace ('**************************************************','********************');
916 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
917 pay_in_utils.trace ('**************************************************','********************');
918 END IF;
919
920 open_tag('EMEAElements');
921
922 FOR rec in csr_other_elements
923 LOOP
924 --
925 l_rec_exists := TRUE;
926 open_tag('EMEAElement');
927
928
929 pay_in_utils.trace('Element Name : ',rec.narrative);
930 pay_in_utils.set_location(g_debug,l_procedure,20);
931 pay_in_utils.trace('Value : ',rec.amount);
932 pay_in_utils.set_location(g_debug,l_procedure,30);
933
934 append_tag('Description',rec.narrative);
935
936 BEGIN
937 append_tag('Value',pay_us_employee_payslip_web.get_format_value(
938 g_business_group_id
939 ,rec.amount));
940 EXCEPTION
941 WHEN num_char THEN
942 append_tag('Value',rec.amount);
943 END ;
944
945 close_tag('EMEAElement');
946 --
947 END LOOP;
948
949 IF l_rec_exists = FALSE THEN
950 --
951 open_tag('EMEAElement');
952
953 append_tag('Description','No Data Exists.');
954 append_tag('Value','');
955
956 close_tag('EMEAElement');
957 --
958 END IF;
959 close_tag('EMEAElements');
960
961 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
966 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
967 pay_in_utils.trace(l_message,l_procedure);
968
969 IF csr_other_elements%ISOPEN THEN
970 CLOSE csr_other_elements;
971 END IF;
972 RAISE;
973
974 END append_other_elements;
975
976 --------------------------------------------------------------------------
977 -- --
978 -- Name : APPEND_ACCRUALS --
979 -- Type : PROCEDURE --
980 -- Access : Private --
981 -- Description : This procedure gets accruals --
982 -- and appends them to g_tmp_clob --
983 -- --
984 -- Parameters : --
985 -- IN : p_action_context_id NUMBER --
986 --------------------------------------------------------------------------
987 --
988 PROCEDURE append_accruals(
989 p_action_context_id IN NUMBER
990 )
991 IS
992 --
993 CURSOR csr_accruals
994 IS
995 --
996 SELECT accrual_plan_name plan_name
997 ,uom
998 ,balance
999 FROM pay_apac_payslip_accruals_v
1000 WHERE action_context_id = p_action_context_id;
1001
1002 l_procedure VARCHAR2(100);
1003 l_message VARCHAR2(250);
1004 l_rec_exists BOOLEAN;
1005 --
1006 BEGIN
1007 --
1008 l_procedure := g_package ||'append_accruals';
1009 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1010
1011 IF g_debug THEN
1012 pay_in_utils.trace ('**************************************************','********************');
1013 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
1014 pay_in_utils.trace ('**************************************************','********************');
1015 END IF;
1016
1017 l_rec_exists := FALSE;
1018 open_tag('Accruals');
1019
1020 FOR rec in csr_accruals
1021 LOOP
1022 --
1023 l_rec_exists := TRUE;
1024 open_tag('AccrualPlan');
1025
1026 pay_in_utils.trace('Plan Name : ',rec.plan_name);
1027 pay_in_utils.set_location(g_debug,l_procedure,20);
1028 pay_in_utils.trace('UOM : ',rec.uom);
1029 pay_in_utils.set_location(g_debug,l_procedure,30);
1030 pay_in_utils.trace('Accrual Balance : ',rec.balance);
1031 pay_in_utils.set_location(g_debug,l_procedure,40);
1032
1033 /* Bug 4218967 Changed the tag AccrBalance to Balance */
1034 append_tag('PlanName',rec.plan_name);
1035 append_tag('UOM',rec.uom);
1036 append_tag('Balance',pay_us_employee_payslip_web.get_format_value(
1037 g_business_group_id
1038 ,rec.balance));
1039
1040 close_tag('AccrualPlan');
1041 --
1042 END LOOP;
1043
1044 IF l_rec_exists = FALSE THEN
1045 --
1046 open_tag('AccrualPlan');
1047
1048 append_tag('PlanName','No data exists.');
1049 append_tag('UOM','');
1050 append_tag('AccrBalance','');
1051
1052 close_tag('AccrualPlan');
1053 --
1054 END IF;
1055 close_tag('Accruals');
1056
1057 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
1058
1059 EXCEPTION
1060 WHEN others THEN
1061 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1062 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
1063 pay_in_utils.trace(l_message,l_procedure);
1064
1065 IF csr_accruals%ISOPEN THEN
1066 CLOSE csr_accruals;
1067 END IF;
1068 RAISE;
1069 --
1070 END append_accruals;
1071
1072 --------------------------------------------------------------------------
1073 -- --
1074 -- Name : APPEND_ABSENCES --
1075 -- Type : PROCEDURE --
1076 -- Access : Private --
1077 -- Description : This procedure gets absences --
1078 -- and appends them to g_tmp_clob --
1079 -- --
1080 -- Parameters : --
1081 -- IN : p_action_context_id NUMBER --
1082 --------------------------------------------------------------------------
1083 --
1084 PROCEDURE append_absences(
1085 p_action_context_id IN NUMBER
1086 )
1087 IS
1088 --
1089 CURSOR csr_absences
1090 IS
1091 --
1092 SELECT absence_type absence_name
1093 ,start_date
1094 ,end_date
1095 ,absence_value
1096 FROM pay_apac_payslip_absences_v
1097 WHERE action_context_id = p_action_context_id;
1098
1099 l_procedure VARCHAR2(100);
1100 l_message VARCHAR2(250);
1101 l_rec_exists BOOLEAN;
1102 --
1103 BEGIN
1104 --
1105 l_procedure := g_package || 'append_absences';
1106 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1107
1108 IF g_debug THEN
1109 pay_in_utils.trace ('**************************************************','********************');
1110 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
1111 pay_in_utils.trace ('**************************************************','********************');
1112 END IF;
1113
1114 l_rec_exists := FALSE;
1115 open_tag('Absences');
1116
1117 FOR rec in csr_absences
1118 LOOP
1119 --
1120 l_rec_exists := TRUE;
1121 open_tag('Absence');
1122
1123 pay_in_utils.trace('Absence Name : ',rec.absence_name);
1124 pay_in_utils.set_location(g_debug,l_procedure,20);
1125 pay_in_utils.trace('Start Date : ',to_char(rec.start_date,'DD-MON-YYYY'));
1126 pay_in_utils.set_location(g_debug,l_procedure,30);
1127 pay_in_utils.trace('End Date : ',to_char(rec.end_date,'DD-MON-YYYY'));
1128 pay_in_utils.set_location(g_debug,l_procedure,40);
1129 pay_in_utils.trace('This Pay : ',rec.absence_value);
1130 pay_in_utils.set_location(g_debug,l_procedure,50);
1131
1132 append_tag('AbsenceName',rec.absence_name);
1133 append_tag('StartDate',to_char(rec.start_date,'DD-MON-YYYY'));
1134 append_tag('EndDate',to_char(rec.end_date,'DD-MON-YYYY'));
1135 append_tag('ThisPay',pay_us_employee_payslip_web.get_format_value(
1136 g_business_group_id
1137 ,rec.absence_value));
1138
1139 close_tag('Absence');
1140 --
1141 END LOOP;
1142
1143 IF l_rec_exists = FALSE THEN
1144 --
1145 open_tag('Absence');
1146
1147 append_tag('AbsenceName','No data exists.');
1148 append_tag('StartDate','');
1149 append_tag('EndDate','');
1150 append_tag('ThisPay','');
1151
1152 close_tag('Absence');
1153 --
1154 END IF;
1155 close_tag('Absences');
1156
1157 --
1158 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
1159
1160 EXCEPTION
1161 WHEN others THEN
1162 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1163 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
1164 pay_in_utils.trace(l_message,l_procedure);
1165
1166 IF csr_absences%ISOPEN THEN
1167 CLOSE csr_absences;
1168 END IF;
1169 RAISE;
1170 --
1171 END append_absences;
1172
1173 --------------------------------------------------------------------------
1174 -- --
1175 -- Name : APPEND_MESSAGES --
1176 -- Type : PROCEDURE --
1177 -- Access : Private --
1178 -- Description : This procedure gets messages --
1179 -- and appends them to g_tmp_clob --
1180 -- --
1181 -- Parameters : --
1182 -- IN : p_action_context_id NUMBER --
1183 --------------------------------------------------------------------------
1184 --
1185 PROCEDURE append_messages(
1186 p_action_context_id IN NUMBER
1187 )
1188 IS
1189 --
1190 CURSOR csr_messages
1191 IS
1192 --
1193
1194 SELECT pai.action_information6 value
1195 FROM pay_assignment_actions paa, pay_action_information pai
1196 WHERE paa.assignment_action_id = p_action_context_id
1197 AND paa.payroll_action_id = pai.action_context_id
1198 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
1199 AND pai.action_information2 = 'MESG'
1200 AND pai.jurisdiction_code IS NOT NULL
1201 UNION
1202 SELECT pai.action_information6 value
1203 FROM pay_action_information pai,pay_assignment_actions paa
1204 WHERE paa.assignment_action_id = p_action_context_id
1205 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
1206 AND pai.action_information2 = 'MESG'
1207 AND paa.payroll_action_id = pai.action_context_id
1208 AND paa.assignment_id = nvl(pai.assignment_id,paa.assignment_id)
1209 AND EXISTS
1210 (SELECT ppa1.pay_advice_message
1211 FROM pay_assignment_actions paa1,pay_action_interlocks intl,pay_payroll_actions ppa1
1212 WHERE intl.locking_action_id = paa.assignment_action_id
1213 AND intl.locked_action_id = paa1.assignment_action_id
1214 AND paa1.payroll_action_id = ppa1.payroll_action_id
1215 AND ppa1.pay_advice_message IS NOT NULL
1216 AND ppa1.action_type IN('R','Q'));
1217
1218 l_procedure VARCHAR2(100);
1219 l_message VARCHAR2(250);
1220 --
1221 BEGIN
1222
1223 l_procedure := g_package || 'append_messages';
1224 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1225
1226 IF g_debug THEN
1227 pay_in_utils.trace('**************************************************','********************');
1228 pay_in_utils.trace('p_action_context_id',p_action_context_id);
1229 pay_in_utils.trace('**************************************************','********************');
1230 END IF;
1231
1232
1233 open_tag('Messages');
1234 FOR rec in csr_messages
1235 LOOP
1236 --
1237 open_tag('Mesg');
1238
1239 pay_in_utils.trace('Mesg : ',rec.value);
1240 pay_in_utils.set_location(g_debug,l_procedure,20);
1241
1242 append_tag('Value',rec.value);
1243
1244 close_tag('Mesg');
1245 --
1246 END LOOP;
1247 close_tag('Messages');
1248
1249 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1250
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1254 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1255 pay_in_utils.trace(l_message,l_procedure);
1256
1257 IF csr_messages%ISOPEN THEN
1258 CLOSE csr_messages;
1259 END IF;
1260 RAISE;
1261 --
1262 END append_messages;
1263
1264 --------------------------------------------------------------------------
1265 -- --
1266 -- Name : APPEND_OTHER_BALANCES --
1267 -- Type : PROCEDURE --
1268 -- Access : Private --
1269 -- Description : This procedure gets EMEA Elements --
1270 -- and appends them to g_tmp_clob --
1271 -- --
1272 -- Parameters : --
1273 -- IN : p_action_context_id NUMBER --
1274 --------------------------------------------------------------------------
1275 --
1276 PROCEDURE append_other_balances(
1277 p_action_context_id IN NUMBER
1278 )
1279 IS
1280 --
1281 CURSOR csr_other_balances
1282 IS
1283 --
1284 SELECT narrative
1285 ,value
1286 FROM pay_apac_bals_action_info_v
1287 WHERE action_context_id = p_action_context_id;
1288
1289 l_procedure VARCHAR2(100);
1290 l_message VARCHAR2(250);
1291 l_rec_exists BOOLEAN;
1292 --
1293 BEGIN
1294 --
1295 l_procedure := g_package || 'append_other_balances';
1296 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1297
1298 IF g_debug THEN
1299 pay_in_utils.trace('**************************************************','********************');
1300 pay_in_utils.trace('p_action_context_id',p_action_context_id);
1301 pay_in_utils.trace('**************************************************','********************');
1302 END IF;
1303
1304 l_rec_exists := FALSE;
1305 open_tag('EMEABalances');
1306
1307 FOR rec in csr_other_balances
1308 LOOP
1309 --
1310 l_rec_exists := TRUE;
1311 open_tag('EMEABalance');
1312
1313 pay_in_utils.trace('Description : ',rec.narrative);
1314 pay_in_utils.set_location(g_debug,l_procedure,20);
1315 pay_in_utils.trace('Amount : ',rec.value);
1316 pay_in_utils.set_location(g_debug,l_procedure,30);
1317
1318
1319 append_tag('Description',rec.narrative);
1320 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
1321 g_business_group_id
1322 ,rec.value));
1323
1324 close_tag('EMEABalance');
1325 --
1326 END LOOP;
1327
1328 IF l_rec_exists = FALSE THEN
1329 --
1330 open_tag('EMEABalance');
1331
1332 append_tag('Description','No data exists.');
1333 append_tag('Amount','');
1334
1335 close_tag('EMEABalance');
1336 --
1337 END IF;
1338 close_tag('EMEABalances');
1339
1340 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1345 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,30);
1346 pay_in_utils.trace(l_message,l_procedure);
1347
1348 IF csr_other_balances%ISOPEN THEN
1349 CLOSE csr_other_balances;
1350 END IF;
1351 RAISE;
1352 --
1353 END append_other_balances;
1354
1355 --------------------------------------------------------------------------
1356 -- --
1357 -- Name : APPEND_PAYMENT_DETAILS --
1358 -- Type : PROCEDURE --
1359 -- Access : Private --
1360 -- Description : This procedure gets Payment Details --
1361 -- and appends them to g_tmp_clob --
1362 -- --
1363 -- Parameters : --
1364 -- IN : p_action_context_id NUMBER --
1365 --------------------------------------------------------------------------
1366 --
1367 PROCEDURE append_payment_details(
1368 p_action_context_id IN NUMBER
1369 )
1370 IS
1371 --
1372 CURSOR csr_payment_details
1373 IS
1374 --
1375 SELECT org_payment_method_name payment_method
1376 ,segment1 bank_name
1377 ,segment3 account_number
1378 ,value
1379 FROM pay_emp_net_dist_action_info_v pendv
1380 WHERE pendv.action_context_id = p_action_context_id;
1381
1382 l_procedure VARCHAR2(100);
1383 l_message VARCHAR2(250);
1384 l_rec_exists BOOLEAN;
1385 --
1386 BEGIN
1387 --
1388 l_procedure := g_package ||'append_payment_details';
1389 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1390
1391 IF g_debug THEN
1392 pay_in_utils.trace ('**************************************************','********************');
1393 pay_in_utils.trace ('p_action_context_id',p_action_context_id);
1394 pay_in_utils.trace ('**************************************************','********************');
1395 END IF;
1396
1397 l_rec_exists := FALSE;
1398 open_tag('PaymentDetails');
1399
1400 FOR rec in csr_payment_details
1401 LOOP
1402 --
1403 l_rec_exists := TRUE;
1404 open_tag('Payment');
1405
1406 pay_in_utils.trace('Payment Method : ',rec.payment_method);
1407 pay_in_utils.set_location(g_debug,l_procedure,20);
1408 pay_in_utils.trace('Bank Name : ',rec.bank_name);
1409 pay_in_utils.set_location(g_debug,l_procedure,30);
1410 pay_in_utils.trace('Account Number : ',rec.account_number);
1411 pay_in_utils.set_location(g_debug,l_procedure,40);
1412 pay_in_utils.trace('This Pay : ',rec.value);
1413 pay_in_utils.set_location(g_debug,l_procedure,50);
1414
1415 append_tag('PaymentType',rec.payment_method);
1416 append_tag('Bank',rec.bank_name);
1417 append_tag('AccountNumber',rec.account_number);
1418 append_tag('Amount',pay_us_employee_payslip_web.get_format_value(
1419 g_business_group_id
1420 ,rec.value));
1421
1422 close_tag('Payment');
1423 --
1424 END LOOP;
1425
1426 IF l_rec_exists = FALSE THEN /*Added for bug#7383091 */
1427 --
1428 open_tag('Payment');
1429
1430 append_tag('PaymentType','No Data Exists.');
1431 append_tag('Bank','');
1432 append_tag('AccountNumber','');
1433 append_tag('Amount','');
1434
1435 close_tag('Payment');
1436 --
1437 END IF;
1438 close_tag('PaymentDetails');
1439
1440 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
1441
1442 EXCEPTION
1443 WHEN others THEN
1444 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1445 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
1446 pay_in_utils.trace(l_message,l_procedure);
1447
1448 IF csr_payment_details%ISOPEN THEN
1449 CLOSE csr_payment_details;
1450 END IF;
1451 RAISE;
1452 --
1453 END append_payment_details;
1454
1455 --------------------------------------------------------------------------
1456 -- --
1457 -- Name : FETCH_XML --
1458 -- Type : PROCEDURE --
1459 -- Access : Public --
1460 -- Description : This procedure returns the next CLOB available in --
1461 -- global CLOB array --
1462 -- --
1463 -- Parameters : --
1464 -- IN : N/A --
1465 -- OUT : p_clob CLOB --
1466 --------------------------------------------------------------------------
1467 --
1468 PROCEDURE fetch_xml (
1469 p_clob OUT NOCOPY CLOB
1470 )
1471 IS
1472 --
1473 l_procedure VARCHAR2(100);
1474 l_message VARCHAR2(250);
1475 --
1476 BEGIN
1477 --
1478
1479 l_procedure := g_package||'fetch_xml';
1480 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1481
1482
1483
1484 -- If Clobs exists return next clob else exit NULL
1485 IF (g_clob_cnt <> 0 ) AND (g_fetch_clob_cnt < g_clob_cnt) THEN
1486 --
1487 g_fetch_clob_cnt := g_fetch_clob_cnt + 1;
1488 p_clob := g_clob(g_fetch_clob_cnt);
1489 --
1490 ELSE
1491 --
1492 p_clob := null;
1493 --
1494 END IF;
1495 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1496
1497 END fetch_xml;
1498
1499 --------------------------------------------------------------------------
1500 -- --
1501 -- Name : LOAD_XML --
1502 -- Type : PROCEDURE --
1503 -- Access : Public --
1504 -- Description : This procedure makes a list of XMLs in a global --
1505 -- CLOB array --
1506 -- --
1507 -- Parameters : --
1508 -- IN : p_business_group_id NUMBER --
1509 -- p_start_date VARCHAR2 --
1510 -- p_end_date VARCHAR2 --
1511 -- p_payroll_id NUMBER --
1512 -- p_consolidation_set_id NUMBER --
1513 -- p_assignment_set_id NUMBER --
1514 -- p_employee_number NUMBER --
1515 -- p_sort_order1 VARCHAR2 --
1516 -- p_sort_order2 VARCHAR2 --
1517 -- p_sort_order3 VARCHAR2 --
1518 -- p_sort_order4 VARCHAR2 --
1519 -- OUT : p_clob_cnt NUMBER --
1520 --------------------------------------------------------------------------
1521 --
1522 PROCEDURE load_xml (
1523 p_business_group_id IN NUMBER
1524 ,p_start_date IN VARCHAR2
1525 ,p_end_date IN VARCHAR2
1526 ,p_payroll_id IN NUMBER DEFAULT NULL
1527 ,p_consolidation_set_id IN NUMBER DEFAULT NULL
1528 ,p_assignment_set_id IN NUMBER DEFAULT NULL
1529 ,p_employee_number IN NUMBER DEFAULT NULL
1530 ,p_sort_order1 IN VARCHAR2 DEFAULT NULL
1531 ,p_sort_order2 IN VARCHAR2 DEFAULT NULL
1532 ,p_sort_order3 IN VARCHAR2 DEFAULT NULL
1533 ,p_sort_order4 IN VARCHAR2 DEFAULT NULL
1534 ,p_clob_cnt OUT NOCOPY NUMBER
1535 )
1536 IS
1537 --
1538 --
1539 l_open_tag VARCHAR2(100);
1540 l_close_tag VARCHAR2(100);
1541 l_emp_open_tag VARCHAR2(100);
1542 l_emp_close_tag VARCHAR2(100);
1543 l_emp_cnt NUMBER;
1544 l_start_date DATE;
1545 l_end_date DATE;
1546 l_stmt VARCHAR2(5000);
1547 l_sort_by VARCHAR2(100);
1548 l_action_context_id NUMBER;
1549 l_sql_csr INTEGER;
1550 l_dummy INTEGER;
1551 l_employee_number VARCHAR2(240);
1552 l_assignment_number VARCHAR2(240);
1553 l_employer_name VARCHAR2(240);
1554 l_dob DATE;
1555 l_joining_date DATE;
1556 l_ptn VARCHAR2(240);
1557 l_pf_number VARCHAR2(240);
1558 l_esi_number VARCHAR2(240);
1559 l_emp_name VARCHAR2(240);
1560 l_pay_month VARCHAR2(240);
1561 l_er_location VARCHAR2(240);
1562 l_email_addr VARCHAR2(240);
1563 l_emp_addr VARCHAR2(500);
1564 l_job VARCHAR2(240);
1565 l_position VARCHAR2(240);
1566 l_grade VARCHAR2(240);
1567 l_pan VARCHAR2(240);
1568 l_superannuation VARCHAR2(240);
1569 l_procedure VARCHAR2(100);
1570 l_message VARCHAR2(250);
1571
1572 --
1573 BEGIN
1574 --
1575
1576 l_procedure := g_package ||'get_template';
1577 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1578
1579 IF g_debug THEN
1580 pay_in_utils.trace('**************************************************','********************');
1581 pay_in_utils.trace('p_business_group_id : ', p_business_group_id);
1582 pay_in_utils.trace('p_start_date : ', p_start_date);
1583 pay_in_utils.trace('p_end_date : ', p_end_date);
1584 pay_in_utils.trace('p_payroll_id : ', p_payroll_id);
1585 pay_in_utils.trace('p_consolidation_set_id : ', p_consolidation_set_id);
1586 pay_in_utils.trace('p_assignment_set_id : ', p_assignment_set_id);
1587 pay_in_utils.trace('p_employee_number : ', p_employee_number);
1588 pay_in_utils.trace('p_sort_order1 : ', p_sort_order1);
1589 pay_in_utils.trace('p_sort_order2 : ', p_sort_order2);
1590 pay_in_utils.trace('p_sort_order3 : ', p_sort_order3);
1591 pay_in_utils.trace('p_sort_order4 : ', p_sort_order4);
1592 pay_in_utils.trace('**************************************************','********************');
1593 END IF;
1594
1595
1596 l_start_date := fnd_date.canonical_to_date(p_start_date);
1597 l_end_date := fnd_date.canonical_to_date(p_end_date);
1598 g_business_group_id := p_business_group_id;
1599
1600 -- Construct SQL statement
1601 --
1602 l_stmt := ' SELECT piaav.assignment_action_id action_context_id
1603 ,peaiv.action_information10 employee_number
1604 ,peaiv.action_information14 assignment_number
1605 ,peaiv.action_information18 gre_name
1606 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information13) dob
1607 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information11) joining_date
1608 ,peaiv.action_information8 ptn
1609 ,peaiv.action_information24 pf_number
1610 ,peaiv.action_information6 esi_number
1611 ,peaiv.action_information1 emp_name
1612 ,pay_in_soe.get_emp_email(peaiv.action_context_id) email_addr
1613 ,pay_in_soe.get_emp_address(paf.person_id,:end_date) emp_addr
1614 ,peaiv.action_information23 pay_month
1615 ,peaiv.action_information30 er_location
1616 ,peaiv.action_information17 job
1617 ,peaiv.action_information19 position
1618 ,peaiv.action_information7 grade
1619 ,peaiv.action_information25 pan
1620 ,peaiv.action_information27 superannuation
1621 ,peaiv.action_information1 full_name
1622 ,peaiv.action_information30 location_name
1623 ,peaiv.action_information15 organization_name
1624 FROM pay_in_arch_actions_v piaav
1625 ,pay_action_information peaiv
1626 ,pay_assignment_actions paa
1627 ,per_all_assignments_f paf
1628 WHERE piaav.business_group_id = :bg_id
1629 AND piaav.business_group_id = paf.business_group_id
1630 AND piaav.effective_date BETWEEN :start_date
1631 AND :end_date
1632 AND ( :payroll_id = -1 OR :payroll_id = piaav.payroll_id)
1633 AND ( :cons_set_id = -1 OR :cons_set_id =
1634 piaav.consolidation_set_id)
1635 AND DECODE (:assg_set_id,-1,''Y'',
1636 DECODE (hr_assignment_set.ASSIGNMENT_IN_SET(:assg_set_id,paf.assignment_id),''Y'',''Y'',''N'')) = ''Y''
1637 AND ( :person_id = -1 OR paf.person_id = :person_id)
1638 AND paf.assignment_id = paa.assignment_id
1639 AND peaiv.action_context_id = piaav.assignment_action_id
1640 AND peaiv.action_context_type = ''AAP''
1641 AND peaiv.action_information_category = ''EMPLOYEE DETAILS''
1642 AND paa.assignment_action_id = peaiv.action_context_id
1643 AND (TO_CHAR(paf.effective_start_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
1644 OR TO_CHAR(paf.effective_end_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
1645 OR :end_date between paf.effective_start_date and paf.effective_end_date)';
1646
1647
1648 -- Construct the sort order
1649 --
1650 l_sort_by := NULL;
1651 IF p_sort_order1 IS NOT NULL THEN
1652 --
1653 l_sort_by := 'ORDER BY '||p_sort_order1;
1654
1655 IF p_sort_order2 IS NOT NULL THEN
1656 --
1657 l_sort_by := l_sort_by ||','||p_sort_order2;
1658 IF p_sort_order3 IS NOT NULL THEN
1659 --
1660 l_sort_by := l_sort_by ||','||p_sort_order3;
1661 IF p_sort_order4 IS NOT NULL THEN
1662 --
1663 l_sort_by := l_sort_by ||','||p_sort_order4;
1664 --
1665 END IF;
1666 --
1667 END IF;
1668 --
1669 END IF;
1670 --
1671 END IF;
1672
1673 l_emp_cnt := 0;
1674 l_open_tag := '<?xml version="1.0" encoding="UTF-8"?>
1675 <clob>';
1676
1677 -- Append sort order to SQL statement
1678 --
1679 l_stmt := l_stmt || l_sort_by;
1680
1681 pay_in_utils.trace('Before Open Cursor',20);
1682 l_sql_csr := dbms_sql.open_cursor;
1683
1684 pay_in_utils.set_location(g_debug,'Before parse',30);
1685 dbms_sql.parse(l_sql_csr,l_stmt,dbms_sql.native);
1686 dbms_sql.define_column(l_sql_csr,1,l_action_context_id);
1687 dbms_sql.define_column(l_sql_csr,2,l_employee_number,240);
1688 dbms_sql.define_column(l_sql_csr,3,l_assignment_number,240);
1689 dbms_sql.define_column(l_sql_csr,4,l_employer_name,240);
1690 dbms_sql.define_column(l_sql_csr,5,l_dob);
1691 dbms_sql.define_column(l_sql_csr,6,l_joining_date);
1692 dbms_sql.define_column(l_sql_csr,7,l_ptn,240);
1693 dbms_sql.define_column(l_sql_csr,8,l_pf_number,240);
1694 dbms_sql.define_column(l_sql_csr,9,l_esi_number,240);
1695 dbms_sql.define_column(l_sql_csr,10,l_emp_name,240);
1696 dbms_sql.define_column(l_sql_csr,11,l_email_addr,240);
1697 dbms_sql.define_column(l_sql_csr,12,l_emp_addr,500);
1698 dbms_sql.define_column(l_sql_csr,13,l_pay_month,240);
1699 dbms_sql.define_column(l_sql_csr,14,l_er_location,240);
1700 dbms_sql.define_column(l_sql_csr,15,l_job,240);
1701 dbms_sql.define_column(l_sql_csr,16,l_position,240);
1702 dbms_sql.define_column(l_sql_csr,17,l_grade,240);
1703 dbms_sql.define_column(l_sql_csr,18,l_pan,240);
1704 dbms_sql.define_column(l_sql_csr,19,l_superannuation,240);
1705
1706 pay_in_utils.set_location(g_debug,'Before Bind',40);
1707 dbms_sql.bind_variable(l_sql_csr,':bg_id',p_business_group_id);
1708 dbms_sql.bind_variable(l_sql_csr,':start_date',l_start_date);
1709 dbms_sql.bind_variable(l_sql_csr,':end_date',l_end_date);
1710 dbms_sql.bind_variable(l_sql_csr,':payroll_id',p_payroll_id);
1711 dbms_sql.bind_variable(l_sql_csr,':cons_set_id',p_consolidation_set_id);
1712 dbms_sql.bind_variable(l_sql_csr,':assg_set_id',p_assignment_set_id);
1713 dbms_sql.bind_variable(l_sql_csr,':person_id',p_employee_number);
1714
1715
1716 pay_in_utils.set_location(g_debug,'Before execute',50);
1717 l_dummy := dbms_sql.execute(l_sql_csr);
1718
1719 LOOP
1720 --
1721 -- Fetch next row
1722 --
1723 IF (dbms_sql.fetch_rows(l_sql_csr) <= 0) THEN
1724 --
1725 -- No More rows exist, Exit.
1726 EXIT;
1727 --
1728 END IF;
1729 dbms_sql.column_value(l_sql_csr,1,l_action_context_id);
1730 dbms_sql.column_value(l_sql_csr,2,l_employee_number);
1731 dbms_sql.column_value(l_sql_csr,3,l_assignment_number);
1732 dbms_sql.column_value(l_sql_csr,4,l_employer_name);
1733 dbms_sql.column_value(l_sql_csr,5,l_dob);
1734 dbms_sql.column_value(l_sql_csr,6,l_joining_date);
1735 dbms_sql.column_value(l_sql_csr,7,l_ptn);
1736 dbms_sql.column_value(l_sql_csr,8,l_pf_number);
1737 dbms_sql.column_value(l_sql_csr,9,l_esi_number);
1738 dbms_sql.column_value(l_sql_csr,10,l_emp_name);
1739 dbms_sql.column_value(l_sql_csr,11,l_email_addr);
1740 dbms_sql.column_value(l_sql_csr,12,l_emp_addr);
1741 dbms_sql.column_value(l_sql_csr,13,l_pay_month);
1742 dbms_sql.column_value(l_sql_csr,14,l_er_location);
1743 dbms_sql.column_value(l_sql_csr,15,l_job);
1744 dbms_sql.column_value(l_sql_csr,16,l_position);
1745 dbms_sql.column_value(l_sql_csr,17,l_grade);
1746 dbms_sql.column_value(l_sql_csr,18,l_pan);
1747 dbms_sql.column_value(l_sql_csr,19,l_superannuation);
1748
1749 pay_in_utils.trace('l_action_context_id : ', l_action_context_id);
1750 pay_in_utils.set_location(g_debug,l_procedure,20);
1751 pay_in_utils.trace('l_employee_number : ', l_employee_number);
1752 pay_in_utils.set_location(g_debug,l_procedure,20);
1753 pay_in_utils.trace('l_assignment_number : ', l_assignment_number);
1754 pay_in_utils.set_location(g_debug,l_procedure,30);
1755 pay_in_utils.trace('l_dob : ', l_dob);
1756 pay_in_utils.set_location(g_debug,l_procedure,40);
1757 pay_in_utils.trace('l_joining_date : ', l_joining_date);
1758 pay_in_utils.set_location(g_debug,l_procedure,50);
1759 pay_in_utils.trace('l_ptn : ', l_ptn);
1760 pay_in_utils.set_location(g_debug,l_procedure,60);
1761 pay_in_utils.trace('l_pf_number : ', l_pf_number);
1762 pay_in_utils.set_location(g_debug,l_procedure,70);
1763 pay_in_utils.trace('l_esi_number : ', l_esi_number);
1764 pay_in_utils.set_location(g_debug,l_procedure,80);
1765 pay_in_utils.trace('l_pay_month : ', l_pay_month);
1766 pay_in_utils.set_location(g_debug,l_procedure,90);
1767 pay_in_utils.trace('l_job : ', l_job);
1768 pay_in_utils.set_location(g_debug,l_procedure,100);
1769 pay_in_utils.trace('l_position : ', l_position);
1770 pay_in_utils.set_location(g_debug,l_procedure,110);
1771 pay_in_utils.trace('l_grade : ', l_grade);
1772 pay_in_utils.set_location(g_debug,l_procedure,120);
1773 pay_in_utils.trace('l_emp_cnt : ', l_emp_cnt);
1774 pay_in_utils.set_location(g_debug,l_procedure,130);
1775
1776 IF ( l_emp_cnt = 0) OR ( l_emp_cnt >=g_chunk_size ) THEN
1777 --
1778 pay_in_utils.set_location(g_debug,'Inside If emp_cnt = 0 or emp_cnt > chunk_size',70);
1779
1780 -- If not the first employee close the previous clob
1781 -- Put the clob in the global CLOB array
1782 --
1783 IF ( l_emp_cnt <> 0) THEN
1784 --
1785 close_tag('clob');
1786 dbms_lob.close(g_tmp_clob);
1787 g_clob(g_clob_cnt):=g_tmp_clob;
1788 l_emp_cnt := 0;
1789 --
1790 END IF;
1791
1792 -- Increment global clob count
1793 --
1794 g_clob_cnt := g_clob_cnt + 1;
1795
1796 pay_in_utils.trace('g_clob_cnt : ', g_clob_cnt);
1797 pay_in_utils.set_location(g_debug,l_procedure,140);
1798
1799 -- Open a new CLOB
1800 --
1801 pay_in_utils.set_location(g_debug,'Before Create Temporary',150);
1802 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
1803
1804 pay_in_utils.set_location(g_debug,'Before Open',160);
1805 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
1806
1807 -- Append Open tags to new CLOB
1808 --
1809 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
1810 --
1811 END IF;
1812
1813 pay_in_utils.set_location(g_debug,'Before employee tag',170);
1814
1815 -- Open Employee tag
1816 -- Append Details
1817 --
1818 open_tag('Employee');
1819 open_tag('PersonDetails');
1820 append_tag('EmpNumber',l_employee_number);
1821 append_tag('AssgNumber',l_assignment_number);
1822 append_tag('ERName',l_employer_name);
1823 append_tag('DOB',to_char(l_dob,'DD-MON-YYYY'));
1824 append_tag('JoiningDate',to_char(l_joining_date,'DD-MON-YYYY'));
1825 append_tag('PTN',l_ptn);
1826 append_tag('PFNumber',l_pf_number);
1827 append_tag('ESINumber',l_esi_number);
1828 append_tag('EmpName',l_emp_name);
1829 append_tag('EmailAddr',l_email_addr);
1830 append_tag('EmpAddr',l_emp_addr);
1831 append_tag('PayMonth',l_pay_month);
1832 append_tag('ERLocation',l_er_location);
1833 append_tag('Job',l_job);
1834 append_tag('Position',l_position);
1835 append_tag('GRADE',l_grade); /* Bug 4218967 Changed the tag Grade to GRADE */
1836 append_tag('PAN',l_pan);
1837 append_tag('SuperAnnuation',l_superannuation);
1838 close_tag('PersonDetails');
1839
1840 append_elements(l_action_context_id,'Earnings');
1841 append_elements(l_action_context_id,'Deductions');
1842 append_elements(l_action_context_id,'Fringe Benefits');
1843 append_elements(l_action_context_id,'Advances');
1844 append_net_pay(l_action_context_id);
1845 append_elements(l_action_context_id,'Perquisites');
1846 append_elements(l_action_context_id,'Employer Charges');
1847 append_balances(l_action_context_id);
1848 append_payment_details(l_action_context_id);
1849 append_other_elements(l_action_context_id);
1850 append_other_balances(l_action_context_id);
1851 append_accruals(l_action_context_id);
1852 append_absences(l_action_context_id);
1853 append_messages(l_action_context_id);
1854
1855
1856 -- Close employee tag
1857 --
1858 close_tag('Employee');
1859 l_emp_cnt := l_emp_cnt + 1;
1860 pay_in_utils.set_location(g_debug,'After employee tag',180);
1861 --
1862 END LOOP;
1863
1864 pay_in_utils.set_location(g_debug,'Close cursor',190);
1865 dbms_sql.close_cursor(l_sql_csr);
1866
1867 -- Last CLOB is not yet closed
1868 -- So close it.
1869 IF ( g_clob_cnt <> 0) THEN
1870 --
1871 pay_in_utils.set_location(g_debug,'Closing last clob',200);
1872 close_tag('clob');
1873 dbms_lob.close(g_tmp_clob);
1874 g_clob(g_clob_cnt):=g_tmp_clob;
1875 --
1876
1877 END IF;
1878 p_clob_cnt := g_clob_cnt;
1879
1880 IF g_debug THEN
1881 pay_in_utils.trace('**************************************************','********************');
1882 pay_in_utils.trace('p_clob_cnt : ',p_clob_cnt);
1883 pay_in_utils.trace('**************************************************','********************');
1884 END IF;
1885
1886
1887 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,220);
1888
1889 EXCEPTION
1890 WHEN others THEN
1891 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1892 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,230);
1893 pay_in_utils.trace(l_message,l_procedure);
1894
1895 IF dbms_sql.is_open(l_sql_csr) THEN
1896 dbms_sql.close_cursor(l_sql_csr);
1897 END IF;
1898 RAISE;
1899 --
1900 END load_xml;
1901
1902 --------------------------------------------------------------------------
1903 -- --
1904 -- Name : LOAD_XML_BURST --
1905 -- Type : PROCEDURE --
1906 -- Access : Public --
1907 -- Description : This procedure makes a list of XMLs in a global --
1908 -- CLOB for xml burst --
1909 -- --
1910 -- Parameters : --
1911 -- IN : p_business_group_id NUMBER --
1912 -- p_start_date VARCHAR2 --
1913 -- p_end_date VARCHAR2 --
1914 -- p_payroll_id NUMBER --
1915 -- p_consolidation_set_id NUMBER --
1916 -- p_assignment_set_id NUMBER --
1917 -- p_employee_number NUMBER --
1918 -- p_sort_order1 VARCHAR2 --
1919 -- p_sort_order2 VARCHAR2 --
1920 -- p_sort_order3 VARCHAR2 --
1921 -- p_sort_order4 VARCHAR2 --
1922 -- OUT : p_xml CLOB --
1923 --------------------------------------------------------------------------
1924
1925 PROCEDURE load_xml_burst (
1926 p_business_group_id IN NUMBER
1927 ,p_start_date IN VARCHAR2
1928 ,p_end_date IN VARCHAR2
1929 ,p_payroll_id IN NUMBER DEFAULT NULL
1930 ,p_consolidation_set_id IN NUMBER DEFAULT NULL
1931 ,p_assignment_set_id IN NUMBER DEFAULT NULL
1932 ,p_employee_number IN NUMBER DEFAULT NULL
1933 ,p_sort_order1 IN VARCHAR2 DEFAULT NULL
1934 ,p_sort_order2 IN VARCHAR2 DEFAULT NULL
1935 ,p_sort_order3 IN VARCHAR2 DEFAULT NULL
1936 ,p_sort_order4 IN VARCHAR2 DEFAULT NULL
1937 ,p_xml OUT NOCOPY CLOB
1938 )
1939 IS
1940 --
1941 --
1942 l_open_tag VARCHAR2(100);
1943 l_close_tag VARCHAR2(100);
1944 l_emp_open_tag VARCHAR2(100);
1945 l_emp_close_tag VARCHAR2(100);
1946 l_emp_cnt NUMBER;
1947 l_start_date DATE;
1948 l_end_date DATE;
1949 l_stmt VARCHAR2(5000);
1950 l_sort_by VARCHAR2(100);
1951 l_action_context_id NUMBER;
1952 l_sql_csr INTEGER;
1953 l_dummy INTEGER;
1954 l_employee_number VARCHAR2(240);
1955 l_assignment_number VARCHAR2(240);
1956 l_employer_name VARCHAR2(240);
1957 l_dob DATE;
1958 l_joining_date DATE;
1959 l_ptn VARCHAR2(240);
1960 l_pf_number VARCHAR2(240);
1961 l_esi_number VARCHAR2(240);
1962 l_emp_name VARCHAR2(240);
1963 l_email_addr VARCHAR2(240);
1964 l_emp_addr VARCHAR2(500);
1965 l_pay_month VARCHAR2(240);
1966 l_er_location VARCHAR2(240);
1967 l_job VARCHAR2(240);
1968 l_position VARCHAR2(240);
1969 l_grade VARCHAR2(240);
1970 l_pan VARCHAR2(240);
1971 l_superannuation VARCHAR2(240);
1972 l_procedure VARCHAR2(100);
1973 l_message VARCHAR2(250);
1974 --
1975 BEGIN
1976 --
1977
1978 l_procedure := g_package ||'load_xml_burst';
1979 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1980
1981 IF g_debug THEN
1982 pay_in_utils.trace('**************************************************','********************');
1983 pay_in_utils.trace('p_business_group_id : ', p_business_group_id);
1984 pay_in_utils.trace('p_start_date : ', p_start_date);
1985 pay_in_utils.trace('p_end_date : ', p_end_date);
1986 pay_in_utils.trace('p_payroll_id : ', p_payroll_id);
1987 pay_in_utils.trace('p_consolidation_set_id : ', p_consolidation_set_id);
1988 pay_in_utils.trace('p_assignment_set_id : ', p_assignment_set_id);
1989 pay_in_utils.trace('p_employee_number : ', p_employee_number);
1990 pay_in_utils.trace('p_sort_order1 : ', p_sort_order1);
1991 pay_in_utils.trace('p_sort_order2 : ', p_sort_order2);
1992 pay_in_utils.trace('p_sort_order3 : ', p_sort_order3);
1993 pay_in_utils.trace('p_sort_order4 : ', p_sort_order4);
1994 pay_in_utils.trace('**************************************************','********************');
1995 END IF;
1996
1997
1998 l_start_date := fnd_date.canonical_to_date(p_start_date);
1999 l_end_date := fnd_date.canonical_to_date(p_end_date);
2000 g_business_group_id := p_business_group_id;
2001
2002 -- Construct SQL statement
2003 --
2004 l_stmt := ' SELECT piaav.assignment_action_id action_context_id
2005 ,peaiv.action_information10 employee_number
2006 ,peaiv.action_information14 assignment_number
2007 ,peaiv.action_information18 gre_name
2008 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information13) dob
2009 ,pay_us_employee_payslip_web.format_to_date(peaiv.action_information11) joining_date
2010 ,peaiv.action_information8 ptn
2011 ,peaiv.action_information24 pf_number
2012 ,peaiv.action_information6 esi_number
2013 ,peaiv.action_information1 emp_name
2014 ,pay_in_soe.get_emp_email(peaiv.action_context_id) email_addr
2015 ,pay_in_soe.get_emp_address(paf.person_id,:end_date) emp_addr
2016 ,peaiv.action_information23 pay_month
2017 ,peaiv.action_information30 er_location
2018 ,peaiv.action_information17 job
2019 ,peaiv.action_information19 position
2020 ,peaiv.action_information7 grade
2021 ,peaiv.action_information25 pan
2022 ,peaiv.action_information27 superannuation
2023 ,peaiv.action_information1 full_name
2024 ,peaiv.action_information30 location_name
2025 ,peaiv.action_information15 organization_name
2026 FROM pay_in_arch_actions_v piaav
2027 ,pay_action_information peaiv
2028 ,pay_assignment_actions paa
2029 ,per_all_assignments_f paf
2030 WHERE piaav.business_group_id = :bg_id
2031 AND piaav.business_group_id = paf.business_group_id
2032 AND piaav.effective_date BETWEEN :start_date
2033 AND :end_date
2034 AND ( :payroll_id = -1 OR :payroll_id = piaav.payroll_id)
2035 AND ( :cons_set_id = -1 OR :cons_set_id =
2036 piaav.consolidation_set_id)
2037 AND DECODE (:assg_set_id,-1,''Y'',
2038 DECODE (hr_assignment_set.ASSIGNMENT_IN_SET(:assg_set_id,paf.assignment_id),''Y'',''Y'',''N'')) = ''Y''
2039 AND ( :person_id = -1 OR paf.person_id = :person_id)
2040 AND paf.assignment_id = paa.assignment_id
2041 AND peaiv.action_context_id = piaav.assignment_action_id
2042 AND EXISTS (SELECT 1
2043 FROM pay_action_information pai
2044 WHERE pai.action_information_category = ''IN_EMPLOYEE_DETAILS''
2045 AND pai.action_context_id = peaiv.action_context_id
2046 AND pai.action_information1 IS NOT NULL)
2047 AND peaiv.action_context_type = ''AAP''
2048 AND peaiv.action_information_category = ''EMPLOYEE DETAILS''
2049 AND paa.assignment_action_id = peaiv.action_context_id
2050 AND (TO_CHAR(paf.effective_start_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
2051 OR TO_CHAR(paf.effective_end_date,''Month-YYYY'')=to_char(:end_date,''Month-YYYY'')
2052 OR :end_date between paf.effective_start_date and paf.effective_end_date)';
2053
2054
2055 -- Construct the sort order
2056 --
2057 l_sort_by := NULL;
2058 IF p_sort_order1 IS NOT NULL THEN
2059 --
2060 l_sort_by := 'ORDER BY '||p_sort_order1;
2061
2062 IF p_sort_order2 IS NOT NULL THEN
2063 --
2064 l_sort_by := l_sort_by ||','||p_sort_order2;
2065 IF p_sort_order3 IS NOT NULL THEN
2066 --
2067 l_sort_by := l_sort_by ||','||p_sort_order3;
2068 IF p_sort_order4 IS NOT NULL THEN
2069 --
2070 l_sort_by := l_sort_by ||','||p_sort_order4;
2071 --
2072 END IF;
2073 --
2074 END IF;
2075 --
2076 END IF;
2077 --
2078 END IF;
2079
2080 l_emp_cnt := 0;
2081 l_open_tag := '<?xml version="1.0" encoding="UTF-8"?>';
2082 -- Append sort order to SQL statement
2083 --
2084 l_stmt := l_stmt || l_sort_by;
2085
2086 pay_in_utils.trace('Before Open Cursor',20);
2087 l_sql_csr := dbms_sql.open_cursor;
2088
2089 pay_in_utils.set_location(g_debug,'Before parse',30);
2090 dbms_sql.parse(l_sql_csr,l_stmt,dbms_sql.native);
2091 dbms_sql.define_column(l_sql_csr,1,l_action_context_id);
2092 dbms_sql.define_column(l_sql_csr,2,l_employee_number,240);
2093 dbms_sql.define_column(l_sql_csr,3,l_assignment_number,240);
2094 dbms_sql.define_column(l_sql_csr,4,l_employer_name,240);
2095 dbms_sql.define_column(l_sql_csr,5,l_dob);
2096 dbms_sql.define_column(l_sql_csr,6,l_joining_date);
2097 dbms_sql.define_column(l_sql_csr,7,l_ptn,240);
2098 dbms_sql.define_column(l_sql_csr,8,l_pf_number,240);
2099 dbms_sql.define_column(l_sql_csr,9,l_esi_number,240);
2100 dbms_sql.define_column(l_sql_csr,10,l_emp_name,240);
2101 dbms_sql.define_column(l_sql_csr,11,l_email_addr,240);
2102 dbms_sql.define_column(l_sql_csr,12,l_emp_addr,500);
2103 dbms_sql.define_column(l_sql_csr,13,l_pay_month,240);
2104 dbms_sql.define_column(l_sql_csr,14,l_er_location,240);
2105 dbms_sql.define_column(l_sql_csr,15,l_job,240);
2106 dbms_sql.define_column(l_sql_csr,16,l_position,240);
2107 dbms_sql.define_column(l_sql_csr,17,l_grade,240);
2108 dbms_sql.define_column(l_sql_csr,18,l_pan,240);
2109 dbms_sql.define_column(l_sql_csr,19,l_superannuation,240);
2110
2111 pay_in_utils.set_location(g_debug,'Before Bind',40);
2112 dbms_sql.bind_variable(l_sql_csr,':bg_id',p_business_group_id);
2113 dbms_sql.bind_variable(l_sql_csr,':start_date',l_start_date);
2114 dbms_sql.bind_variable(l_sql_csr,':end_date',l_end_date);
2115 dbms_sql.bind_variable(l_sql_csr,':payroll_id',p_payroll_id);
2116 dbms_sql.bind_variable(l_sql_csr,':cons_set_id',p_consolidation_set_id);
2117 dbms_sql.bind_variable(l_sql_csr,':assg_set_id',p_assignment_set_id);
2118 dbms_sql.bind_variable(l_sql_csr,':person_id',p_employee_number);
2119
2120
2121 pay_in_utils.set_location(g_debug,'Before execute',50);
2122 l_dummy := dbms_sql.execute(l_sql_csr);
2123
2124 dbms_lob.createtemporary(g_tmp_clob,FALSE,DBMS_LOB.CALL);
2125 dbms_lob.open(g_tmp_clob,dbms_lob.lob_readwrite);
2126 dbms_lob.writeAppend(g_tmp_clob,length(l_open_tag),l_open_tag);
2127 open_tag('IN_SOE');
2128
2129 LOOP
2130 --
2131 -- Fetch next row
2132 --
2133 IF (dbms_sql.fetch_rows(l_sql_csr) <= 0) THEN
2134 --
2135 -- No More rows exist, Exit.
2136 EXIT;
2137 --
2138 END IF;
2139 dbms_sql.column_value(l_sql_csr,1,l_action_context_id);
2140 dbms_sql.column_value(l_sql_csr,2,l_employee_number);
2141 dbms_sql.column_value(l_sql_csr,3,l_assignment_number);
2142 dbms_sql.column_value(l_sql_csr,4,l_employer_name);
2143 dbms_sql.column_value(l_sql_csr,5,l_dob);
2144 dbms_sql.column_value(l_sql_csr,6,l_joining_date);
2145 dbms_sql.column_value(l_sql_csr,7,l_ptn);
2146 dbms_sql.column_value(l_sql_csr,8,l_pf_number);
2147 dbms_sql.column_value(l_sql_csr,9,l_esi_number);
2148 dbms_sql.column_value(l_sql_csr,10,l_emp_name);
2149 dbms_sql.column_value(l_sql_csr,11,l_email_addr);
2150 dbms_sql.column_value(l_sql_csr,12,l_emp_addr);
2151 dbms_sql.column_value(l_sql_csr,13,l_pay_month);
2152 dbms_sql.column_value(l_sql_csr,14,l_er_location);
2153 dbms_sql.column_value(l_sql_csr,15,l_job);
2154 dbms_sql.column_value(l_sql_csr,16,l_position);
2155 dbms_sql.column_value(l_sql_csr,17,l_grade);
2156 dbms_sql.column_value(l_sql_csr,18,l_pan);
2157 dbms_sql.column_value(l_sql_csr,19,l_superannuation);
2158
2159 pay_in_utils.trace('l_action_context_id : ', l_action_context_id);
2160 pay_in_utils.set_location(g_debug,l_procedure,20);
2161 pay_in_utils.trace('l_employee_number : ', l_employee_number);
2162 pay_in_utils.set_location(g_debug,l_procedure,20);
2163 pay_in_utils.trace('l_assignment_number : ', l_assignment_number);
2164 pay_in_utils.set_location(g_debug,l_procedure,30);
2165 pay_in_utils.trace('l_dob : ', l_dob);
2166 pay_in_utils.set_location(g_debug,l_procedure,40);
2167 pay_in_utils.trace('l_joining_date : ', l_joining_date);
2168 pay_in_utils.set_location(g_debug,l_procedure,50);
2169 pay_in_utils.trace('l_ptn : ', l_ptn);
2170 pay_in_utils.set_location(g_debug,l_procedure,60);
2171 pay_in_utils.trace('l_pf_number : ', l_pf_number);
2172 pay_in_utils.set_location(g_debug,l_procedure,70);
2173 pay_in_utils.trace('l_esi_number : ', l_esi_number);
2174 pay_in_utils.set_location(g_debug,l_procedure,80);
2175 pay_in_utils.trace('l_pay_month : ', l_pay_month);
2176 pay_in_utils.set_location(g_debug,l_procedure,90);
2177 pay_in_utils.trace('l_job : ', l_job);
2178 pay_in_utils.set_location(g_debug,l_procedure,100);
2179 pay_in_utils.trace('l_position : ', l_position);
2180 pay_in_utils.set_location(g_debug,l_procedure,110);
2181 pay_in_utils.trace('l_grade : ', l_grade);
2182 pay_in_utils.set_location(g_debug,l_procedure,120);
2183 pay_in_utils.trace('l_emp_cnt : ', l_emp_cnt);
2184 pay_in_utils.set_location(g_debug,l_procedure,130);
2185
2186 pay_in_utils.set_location(g_debug,'Before employee tag',170);
2187
2188
2189 -- Open Employee tag
2190 -- Append Details
2191 --
2192
2193 open_tag('Employee');
2194 open_tag('PersonDetails');
2195 append_tag('EmpNumber',l_employee_number);
2196 append_tag('AssgNumber',l_assignment_number);
2197 append_tag('ERName',l_employer_name);
2198 append_tag('DOB',to_char(l_dob,'DD-MON-YYYY'));
2199 append_tag('JoiningDate',to_char(l_joining_date,'DD-MON-YYYY'));
2200 append_tag('PTN',l_ptn);
2201 append_tag('PFNumber',l_pf_number);
2202 append_tag('ESINumber',l_esi_number);
2203 append_tag('EmpName',l_emp_name);
2204 append_tag('EmailAddr',l_email_addr);
2205 append_tag('EmpAddr',l_emp_addr);
2206 append_tag('PayMonth',l_pay_month);
2207 append_tag('ERLocation',l_er_location);
2208 append_tag('Job',l_job);
2209 append_tag('Position',l_position);
2210 append_tag('GRADE',l_grade); /* Bug 4218967 Changed the tag Grade to GRADE */
2211 append_tag('PAN',l_pan);
2212 append_tag('SuperAnnuation',l_superannuation);
2213 close_tag('PersonDetails');
2214
2215 append_elements(l_action_context_id,'Earnings');
2216 append_elements(l_action_context_id,'Deductions');
2217 append_elements(l_action_context_id,'Fringe Benefits');
2218 append_elements(l_action_context_id,'Advances');
2219 append_net_pay(l_action_context_id);
2220 append_elements(l_action_context_id,'Perquisites');
2221 append_elements(l_action_context_id,'Employer Charges');
2222 append_balances(l_action_context_id);
2223 append_payment_details(l_action_context_id);
2224 append_other_elements(l_action_context_id);
2225 append_other_balances(l_action_context_id);
2226 append_accruals(l_action_context_id);
2227 append_absences(l_action_context_id);
2228 append_messages(l_action_context_id);
2229
2230
2231 -- Close employee tag
2232 --
2233 close_tag('Employee');
2234
2235 /* l_emp_cnt := l_emp_cnt + 1;*/
2236 pay_in_utils.set_location(g_debug,'After employee tag',180);
2237 --
2238 END LOOP;
2239 close_tag('IN_SOE');
2240 pay_in_utils.set_location(g_debug,'Close cursor',190);
2241 dbms_sql.close_cursor(l_sql_csr);
2242
2243
2244
2245 dbms_lob.close(g_tmp_clob);
2246 p_xml := g_tmp_clob;
2247 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,220);
2248
2249 EXCEPTION
2250 WHEN others THEN
2251 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2252 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,230);
2253 pay_in_utils.trace(l_message,l_procedure);
2254
2255 IF dbms_sql.is_open(l_sql_csr) THEN
2256 dbms_sql.close_cursor(l_sql_csr);
2257 END IF;
2258 RAISE;
2259 --
2260 END load_xml_burst;
2261
2262
2263
2264 --
2265 BEGIN
2266 --
2267 -- Initialize Globals
2268 --
2269 g_clob_cnt := 0;
2270 g_fetch_clob_cnt := 0;
2271 g_chunk_size := 500;
2272 --
2273 END pay_in_soe;