[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_CWCR
Source
1 PACKAGE BODY PAY_SE_CWCR AS
2 /* $Header: pysecwcr.pkb 120.0.12010000.2 2008/08/06 08:17:25 ubhat ship $ */
3
4
5 PROCEDURE get_digit_breakup(
6 p_number IN NUMBER,
7 p_digit1 OUT NOCOPY NUMBER,
8 p_digit2 OUT NOCOPY NUMBER,
9 p_digit3 OUT NOCOPY NUMBER,
10 p_digit4 OUT NOCOPY NUMBER,
11 p_digit5 OUT NOCOPY NUMBER,
12 p_digit6 OUT NOCOPY NUMBER,
13 p_digit7 OUT NOCOPY NUMBER,
14 p_digit8 OUT NOCOPY NUMBER,
15 p_digit9 OUT NOCOPY NUMBER,
16 p_digit10 OUT NOCOPY NUMBER
17 )
18 IS
19
20 TYPE digits IS
21 TABLE OF NUMBER
22 INDEX BY BINARY_INTEGER;
23 l_digit digits;
24 l_count NUMBER :=1;
25 l_number number(10);
26 BEGIN
27 l_number:=floor(p_number);
28 FOR I in 1..10 loop
29 l_digit(I):=null;
30 END loop;
31
32 WHILE l_number >= 1 LOOP
33
34 SELECT mod(l_number,10) INTO l_digit(l_count) from dual;
35 l_number:=floor(l_number/10);
36 l_count:=l_count+1;
37 END LOOP;
38
39 SELECT floor(l_number) INTO l_digit(l_number) from dual;
40 p_digit1:=l_digit(1);
41 p_digit2:=l_digit(2);
42 p_digit3:=l_digit(3);
43 p_digit4:=l_digit(4);
44 p_digit5:=l_digit(5);
45 p_digit6:=l_digit(6);
46 p_digit7:=l_digit(7);
47 p_digit8:=l_digit(8);
48 p_digit9:=l_digit(9);
49 p_digit10:=l_digit(10);
50 END get_digit_breakup;
51
52
53
54 PROCEDURE GET_DATA (
55 p_business_group_id IN NUMBER,
56 p_payroll_action_id IN VARCHAR2 ,
57 p_template_name IN VARCHAR2,
58 p_xml OUT NOCOPY CLOB
59 )
60
61 IS
62
63 /* Start of declaration*/
64
65 -- Variables needed for the report
66 l_counter number := 0;
67 l_payroll_action_id PAY_ACTION_INFORMATION. ACTION_INFORMATION1%TYPE;
68 l_digit1 NUMBER(1);
69 l_digit2 NUMBER(1);
70 l_digit3 NUMBER(1);
71 l_digit4 NUMBER(1);
72 l_digit5 NUMBER(1);
73 l_digit6 NUMBER(1);
74 l_digit7 NUMBER(1);
75 l_digit8 NUMBER(1);
76 l_digit9 NUMBER(1);
77 l_digit10 NUMBER(1);
78 l_person_number VARCHAR2(20);
79
80
81 /* End of declaration*/
82
83 /* Cursors */
84
85 Cursor csr_cwcr_header_rpt(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE)
86 IS
87 SELECT *
88 FROM PAY_ACTION_INFORMATION
89 WHERE ACTION_CONTEXT_TYPE = 'PA'
90 AND ACTION_CONTEXT_ID = csr_v_pa_id
91 AND ACTION_INFORMATION_CATEGORY='EMEA REPORT DETAILS'
92 AND ACTION_INFORMATION1='PYSECWCA';
93
94
95 Cursor csr_cwcr_b1(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE )
96 IS
97 SELECT *
98 FROM PAY_ACTION_INFORMATION
99 WHERE ACTION_CONTEXT_TYPE = 'PA'
100 AND ACTION_CONTEXT_ID = csr_v_pa_id
101 AND ACTION_INFORMATION1='PYSECWCA'
102 AND ACTION_INFORMATION2='CWC1';
103
104 rg_cwcr_b1 csr_cwcr_b1%rowtype;
105
106 Cursor csr_cwcr_b2(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE )
107 IS
108 SELECT *
109 FROM PAY_ACTION_INFORMATION
110 WHERE ACTION_CONTEXT_TYPE = 'PA'
111 AND ACTION_CONTEXT_ID = csr_v_pa_id
112 AND ACTION_INFORMATION1='PYSECWCA'
113 AND ACTION_INFORMATION2='CWC2'
114 ORDER BY ACTION_INFORMATION10 asc , ACTION_INFORMATION3 asc ;
115
116 Cursor csr_cwcr_b3(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month NUMBER, csr_v_year NUMBER )
117 IS
118 SELECT *
119 FROM PAY_ACTION_INFORMATION
120 WHERE ACTION_CONTEXT_TYPE = 'PA'
121 AND ACTION_CONTEXT_ID = csr_v_pa_id
122 AND ACTION_INFORMATION1='PYSECWCA'
123 AND ACTION_INFORMATION2='CWC3'
124 AND ACTION_INFORMATION3 = csr_v_month
125 AND ACTION_INFORMATION10 = csr_v_year ;
126
127 Cursor csr_cwcr_b4(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month NUMBER, csr_v_year NUMBER )
128 IS
129 SELECT *
130 FROM PAY_ACTION_INFORMATION
131 WHERE ACTION_CONTEXT_TYPE = 'PA'
132 AND ACTION_CONTEXT_ID = csr_v_pa_id
133 AND ACTION_INFORMATION1='PYSECWCA'
134 AND ACTION_INFORMATION2='CWC4'
135 AND ACTION_INFORMATION3 = csr_v_month
136 AND ACTION_INFORMATION10 = csr_v_year ;
137
138 Cursor csr_cwcr_b5(csr_v_pa_id PAY_ACTION_INFORMATION.ACTION_INFORMATION1%TYPE, csr_v_month NUMBER, csr_v_year NUMBER )
139 IS
140 SELECT *
141 FROM PAY_ACTION_INFORMATION
142 WHERE ACTION_CONTEXT_TYPE = 'PA'
143 AND ACTION_CONTEXT_ID = csr_v_pa_id
144 AND ACTION_INFORMATION1='PYSECWCA'
145 AND ACTION_INFORMATION2='CWC5'
146 AND ACTION_INFORMATION3 = csr_v_month
147 AND ACTION_INFORMATION10 = csr_v_year ;
148
149 /* End of Cursors */
150
151 BEGIN
152
153 IF p_payroll_action_id IS NULL THEN
154
155 BEGIN
156
157 SELECT payroll_action_id
158 INTO l_payroll_action_id
159 FROM pay_payroll_actions ppa,
160 fnd_conc_req_summary_v fcrs,
161 fnd_conc_req_summary_v fcrs1
162 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
163 AND fcrs.priority_request_id = fcrs1.priority_request_id
164 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
165 AND ppa.request_id = fcrs1.request_id;
166
167 EXCEPTION
168 WHEN others THEN
169 NULL;
170 END ;
171
172 ELSE
173
174 l_payroll_action_id :=p_payroll_action_id;
175
176 END IF;
177
178 hr_utility.set_location('Entered Procedure GETDATA',10);
179
180 FOR rg_cwcr_header_rpt IN csr_cwcr_header_rpt( l_payroll_action_id)
181 LOOP
182
183
184 OPEN csr_cwcr_b1( l_payroll_action_id);
185 FETCH csr_cwcr_b1 INTO rg_cwcr_b1;
186 CLOSE csr_cwcr_b1;
187
188 gtagdata(l_counter).TagName := 'PERSON';
189 gtagdata(l_counter).TagValue := 'PERSON';
190 l_counter := l_counter + 1;
191
192 --gtagdata(l_counter).TagName := 'EMP_NUM';
193 --gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information3;
194 --l_counter := l_counter + 1;
195
196
197 l_person_number := replace(TO_CHAR(rg_cwcr_b1.action_information3),'-','');
198 ---------------------------------------------------------------------------------------------------------------
199 --New Format of Person Number (of Ten Digits)
200 ---------------------------------------------------------------------------------------------------------------
201 --add_tag_value ('PERSON_NUMBER', lr_wtc_person1.person_number);
202
203 get_digit_breakup(FND_NUMBER.CANONICAL_TO_NUMBER(l_person_number),l_digit1,l_digit2,l_digit3,l_digit4,l_digit5,l_digit6,l_digit7,l_digit8,l_digit9,l_digit10);
204 gtagdata (l_counter).tagname := 'PN1';
205 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit1);
206 l_counter := l_counter
207 + 1;
208 gtagdata (l_counter).tagname := 'PN2';
209 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit2);
210 l_counter := l_counter
211 + 1;
212 gtagdata (l_counter).tagname := 'PN3';
213 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit3);
214 l_counter := l_counter
215 + 1;
216 gtagdata (l_counter).tagname := 'PN4';
217 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit4);
218 l_counter := l_counter
219 + 1;
220 gtagdata (l_counter).tagname := 'PN5';
221 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit5);
222 l_counter := l_counter
223 + 1;
224 gtagdata (l_counter).tagname := 'PN6';
225 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit6);
226 l_counter := l_counter
227 + 1;
228 gtagdata (l_counter).tagname := 'PN7';
229 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit7);
230 l_counter := l_counter
231 + 1;
232 gtagdata (l_counter).tagname := 'PN8';
233 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit8);
234 l_counter := l_counter
235 + 1;
236 gtagdata (l_counter).tagname := 'PN9';
237 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit9);
238 l_counter := l_counter
239 + 1;
240 gtagdata (l_counter).tagname := 'PN10';
241 gtagdata (l_counter).tagvalue := TO_CHAR (l_digit10);
242 l_counter := l_counter
243 + 1;
244 ------------------------------------------------------------------------------------------------------------------
245
246
247
248
249 gtagdata(l_counter).TagName := 'EMP_LNAME';
250 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information4;
251 l_counter := l_counter + 1;
252
253 gtagdata(l_counter).TagName := 'EMP_FNAME';
254 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information5;
255 l_counter := l_counter + 1;
256
257 FOR rg_cwcr_b2 IN csr_cwcr_b2( l_payroll_action_id)
258 LOOP
259
260
261 gtagdata(l_counter).TagName := 'B1';
262 gtagdata(l_counter).TagValue := 'B1';
263 l_counter := l_counter + 1;
264
265 /* gtagdata(l_counter).TagName := 'YR';
266 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information10;
267 l_counter := l_counter + 1;
268
269 gtagdata(l_counter).TagName := 'MTH';
270 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information3;
271 l_counter := l_counter + 1;
272 */
273
274
275 gtagdata(l_counter).TagName := 'YRMTH'; -- EOY 2008
276 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information10||rg_cwcr_b2.action_information3;
277 l_counter := l_counter + 1;
278
279 gtagdata(l_counter).TagName := 'DAYS_WORKED';
280 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information12;
281 l_counter := l_counter + 1;
282
283 gtagdata(l_counter).TagName := 'AH';
284 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information9;
285 l_counter := l_counter + 1;
286
287
288 gtagdata(l_counter).TagName := 'AHW';
289 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information4;
290 l_counter := l_counter + 1;
291
292 gtagdata(l_counter).TagName := 'OH';
293 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information7;
294 l_counter := l_counter + 1;
295
296 gtagdata(l_counter).TagName := 'OHW';
297 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information8;
298 l_counter := l_counter + 1;
299
300 gtagdata(l_counter).TagName := 'RDH';
301 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information5;
302 l_counter := l_counter + 1;
303
304 gtagdata(l_counter).TagName := 'RDHW';
305 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information6;
306 l_counter := l_counter + 1;
307
308 gtagdata(l_counter).TagName := 'RDMW';
309 gtagdata(l_counter).TagValue := rg_cwcr_b2.action_information11;
310 l_counter := l_counter + 1;
311
312
313 gtagdata(l_counter).TagName := 'B2';
314 gtagdata(l_counter).TagValue := 'B2';
315 l_counter := l_counter + 1;
316
317 FOR rg_cwcr_b3 IN csr_cwcr_b3( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
318 LOOP
319
320
321 gtagdata(l_counter).TagName := 'OCK';
322 gtagdata(l_counter).TagValue := rg_cwcr_b3.action_information4;
323 l_counter := l_counter + 1;
324
325 gtagdata(l_counter).TagName := 'OCA';
326 gtagdata(l_counter).TagValue := rg_cwcr_b3.action_information5;
327 l_counter := l_counter + 1;
328
329
330
331
332 END LOOP;
333
334 gtagdata(l_counter).TagName := 'B2';
335 gtagdata(l_counter).TagValue := 'B2_END';
336 l_counter := l_counter + 1;
337
338 gtagdata(l_counter).TagName := 'B3';
339 gtagdata(l_counter).TagValue := 'B3';
340 l_counter := l_counter + 1;
341
342
343 FOR rg_cwcr_b4 IN csr_cwcr_b4( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
344 LOOP
345
346 gtagdata(l_counter).TagName := 'OVK';
347 gtagdata(l_counter).TagValue := rg_cwcr_b4.action_information4;
348 l_counter := l_counter + 1;
349
350 gtagdata(l_counter).TagName := 'OVA';
351 gtagdata(l_counter).TagValue := rg_cwcr_b4.action_information5;
352 l_counter := l_counter + 1;
353
354
355
356
357 END LOOP;
358
359 gtagdata(l_counter).TagName := 'B3';
360 gtagdata(l_counter).TagValue := 'B3_END';
361 l_counter := l_counter + 1;
362
363 gtagdata(l_counter).TagName := 'B4';
364 gtagdata(l_counter).TagValue := 'B4';
365 l_counter := l_counter + 1;
366
367 FOR rg_cwcr_b5 IN csr_cwcr_b5( l_payroll_action_id, rg_cwcr_b2.action_information3, rg_cwcr_b2.action_information10 )
368 LOOP
369
370
371 gtagdata(l_counter).TagName := 'ADK';
372 gtagdata(l_counter).TagValue := rg_cwcr_b5.action_information4;
373 l_counter := l_counter + 1;
374
375 gtagdata(l_counter).TagName := 'ADA';
376 gtagdata(l_counter).TagValue := rg_cwcr_b5.action_information5;
377 l_counter := l_counter + 1;
378
379
380
381 END LOOP;
382
383 gtagdata(l_counter).TagName := 'B4';
384 gtagdata(l_counter).TagValue := 'B4_END';
385 l_counter := l_counter + 1;
386
387
391
388 gtagdata(l_counter).TagName := 'B1';
389 gtagdata(l_counter).TagValue := 'B1_END';
390 l_counter := l_counter + 1;
392
393 END LOOP;
394
395
396 gtagdata(l_counter).TagName := 'EMP_NAME';
397 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information6;
398 l_counter := l_counter + 1;
399
400 gtagdata(l_counter).TagName := 'ORG_NUMBER';
401 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information7;
402 l_counter := l_counter + 1;
403
404 gtagdata(l_counter).TagName := 'EMP_ADDR1';
405 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information9;
406 l_counter := l_counter + 1;
407
408 gtagdata(l_counter).TagName := 'EMP_ADDR2';
409 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information10;
410 l_counter := l_counter + 1;
411
412
413 gtagdata(l_counter).TagName := 'EMP_ADDR3';
414 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information11;
415 l_counter := l_counter + 1;
416
417 gtagdata(l_counter).TagName := 'TOWN_CITY';
418 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information13;
419 l_counter := l_counter + 1;
420
421 gtagdata(l_counter).TagName := 'POSTAL_CODE';
422 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information12;
423 l_counter := l_counter + 1;
424
425 gtagdata(l_counter).TagName := 'PHONE_NUMBER';
426 gtagdata(l_counter).TagValue := rg_cwcr_b1.action_information17;
427 l_counter := l_counter + 1;
428
429 gtagdata(l_counter).TagName := 'PERSON';
430 gtagdata(l_counter).TagValue := 'PERSON_END';
431 l_counter := l_counter + 1;
432
433 END LOOP;
434
435 hr_utility.set_location('After populating pl/sql table',30);
436
437
438 WritetoCLOB (p_xml );
439
440
441 END GET_DATA;
442
443 -----------------------------------------------------------------------------------------------------------------
444 PROCEDURE WritetoCLOB(p_xfdf_clob out nocopy CLOB) is
445 l_xfdf_string clob;
446 l_str1 varchar2(1000);
447 l_str2 varchar2(20);
448 l_str3 varchar2(20);
449 l_str4 varchar2(20);
450 l_str5 varchar2(20);
451 l_str6 varchar2(30);
452 l_str7 varchar2(1000);
453 l_str8 varchar2(240);
454 l_str9 varchar2(240);
455 l_str10 varchar2(20);
456 l_str11 varchar2(20);
457
458 current_index pls_integer;
459 l_IANA_charset VARCHAR2 (50);
460
461 BEGIN
462 l_IANA_charset :=hr_se_utility.get_IANA_charset ;
463 hr_utility.set_location('Entering WritetoCLOB ',70);
464 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>' ;
465 l_str2 := '<';
466 l_str3 := '>';
467 l_str4 := '</';
468 l_str5 := '>';
469 l_str6 := '</ROOT>';
470 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
471 l_str10 := '<PERSON>';
472 l_str11 := '</PERSON>';
473
474
475 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
476 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
477
478 current_index := 0;
479
480 IF gtagdata.count > 0 THEN
481
482 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
483
484
485 FOR table_counter IN gtagdata.FIRST .. gtagdata.LAST LOOP
486
487 l_str8 := gtagdata(table_counter).TagName;
488
489 l_str9 := gtagdata(table_counter).TagValue;
490
491
492
493
494 IF l_str9 IN ('PERSON' ,'PERSON_END','B1',
495 'B1_END','B2','B2_END','B3','B3_END','B4','B4_END') THEN
496
497 IF l_str9 IN ('PERSON' ,'B1','B2','B3','B4') THEN
498 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
499 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
500 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
501 ELSE
502 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
503 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
504 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
505 END IF;
506
507 ELSE
508
509 if l_str9 is not null then
510
511 l_str9 := '<![CDATA[' || l_str9 || ']]>';
512
513 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
514 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
515 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
516 dbms_lob.writeAppend(l_xfdf_string, length(l_str9), l_str9);
517 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
518 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
519 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
520 else
521
522 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
523 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
524 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
525 dbms_lob.writeAppend(l_xfdf_string, length(l_str4), l_str4);
526 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
527 dbms_lob.writeAppend(l_xfdf_string, length(l_str5), l_str5);
528
529 end if;
530
531 END IF;
532
533 END LOOP;
534
535 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6 );
536
537 ELSE
538 dbms_lob.writeAppend(l_xfdf_string, length(l_str7), l_str7 );
539 END IF;
540
541 p_xfdf_clob := l_xfdf_string;
542
543 hr_utility.set_location('Leaving WritetoCLOB ',40);
544
545 EXCEPTION
546 WHEN OTHERS then
547 HR_UTILITY.TRACE('sqlerrm ' || SQLERRM);
548 HR_UTILITY.RAISE_ERROR;
549 END WritetoCLOB;
550 -------------------------------------------------------------------------------------------------------------------------
551
552 END PAY_SE_CWCR;