[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_UMFR
Source
1 PACKAGE BODY PAY_FI_UMFR AS
2 /* $Header: pyfiumfr.pkb 120.5 2007/06/20 05:38:54 psingla noship $ */
3 function get_archive_payroll_action_id(p_payroll_action_id in number)
4 return number
5 is
6 l_payroll_action_id number;
7 BEGIN
8
9 IF p_payroll_action_id IS NULL THEN
10
11 BEGIN
12
13 SELECT payroll_action_id
14 INTO l_payroll_action_id
15 FROM pay_payroll_actions ppa,
16 fnd_conc_req_summary_v fcrs,
17 fnd_conc_req_summary_v fcrs1
18 WHERE fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
19 AND fcrs.priority_request_id = fcrs1.priority_request_id
20 AND ppa.request_id between fcrs1.request_id and fcrs.request_id
21 AND ppa.request_id = fcrs1.request_id;
22
23 EXCEPTION
24 WHEN others THEN
25 NULL;
26 END ;
27
28 ELSE
29
30 l_payroll_action_id :=p_payroll_action_id;
31
32 END IF;
33 return l_payroll_action_id;
34 end;
35 PROCEDURE get_data (
36 p_business_group_id in varchar2,
37 p_payroll_action_id IN VARCHAR2,
38 p_template_name IN VARCHAR2,
39 p_xml OUT NOCOPY CLOB
40 )
41 IS
42 /* Start of declaration*/
43
44 -- Variables needed for the report
45 l_sum NUMBER;
46 l_counter NUMBER := 1;
47 l_payroll_action_id pay_action_information.action_information1%TYPE;
48
49 cursor first_level(csr_v_pa_id IN VARCHAR2) is
50 select ACTION_INFORMATION2 TRADE_UNION_NAME, ACTION_INFORMATION4 LEGAL_EMPLOYER_NAME
51 from pay_action_information pai where
52 pai.action_information_category = 'EMEA REPORT DETAILS'
53 AND pai.action_context_type = 'PA'
54 AND PAI.ACTION_CONTEXT_ID=csr_v_pa_id;
55
56 first_level_rec first_level%rowtype;
57 cursor legal_header(csr_v_pa_id IN VARCHAR2) is
58 select ACTION_INFORMATION2 TRADE_UNION_NAME, ACTION_INFORMATION4 LEGAL_EMPLOYER_NAME
59 from pay_action_information pai where
60 pai.action_information_category = 'EMEA REPORT DETAILS'
61 AND pai.action_context_type = 'PA'
62 AND PAI.ACTION_CONTEXT_ID=csr_v_pa_id;
63 legal_header_rec legal_header%rowtype;
64
65
66 cursor local_header(csr_v_pa_id IN VARCHAR2) is
67 select ACTION_INFORMATION2 TRADE_UNION_NAME, ACTION_INFORMATION4 LEGAL_EMPLOYER_NAME
68 from pay_action_information pai where
69 pai.action_information_category = 'EMEA REPORT DETAILS'
70 AND pai.action_context_type = 'PA'
71 AND PAI.ACTION_CONTEXT_ID=csr_v_pa_id;
72 local_header_rec legal_header%rowtype;
73
74 CURSOR csr_umfr_data (csr_v_pa_id IN VARCHAR2)
75 IS
76 SELECT pai.ACTION_INFORMATION2 TRADE_UNION_NAME, pai.ACTION_INFORMATION4 LEGAL_EMPLOYER_NAME,
77 null record_code ,
78 pai_le.action_information5 y_number,
79 pai_lu.action_information5
80 y_number_spare,
81 pai_tu.action_information6 accounting_id, NULL accounting_id_spare,
82 pai_tu.action_information5
83 trade_union_number,
84 pai_lu.action_information4 local_unit_name,
85 pai_lu.action_information6 local_unit_number,
86 pai_per.action_information4
87 employee_pin,
88 pai_per.action_information5 employee_name,
89 fnd_date.canonical_to_date(pai_per.action_information6)
90 membership_start_date,
91 fnd_date.canonical_to_date(pai_per.action_information7)
92 membership_end_date, '1' currency,
93 pai_per.action_information9
94 SIGN, pai_per.action_information8 amount,
95 '00' reason, to_char(fnd_date.canonical_to_date(pai.action_information9),'RRRR') tax_year, NULL sequence_number
96 FROM pay_assignment_actions asgact, -- pay_payroll_actions payact,
97 pay_action_information pai,
98 pay_action_information pai_per,
99 pay_action_information pai_lu,
100 pay_action_information pai_le,
101 pay_action_information pai_tu
102 WHERE asgact.payroll_action_id = csr_v_pa_id
103 AND pai_per.action_context_type = 'AAP'
104 AND pai_per.action_information1 = 'PYFIUMFR'
105 AND pai_per.action_information_category = 'EMEA REPORT INFORMATION'
106 AND asgact.assignment_action_id = pai_per.action_context_id
107 AND pai_lu.action_information3 = pai_per.action_information3
108 AND pai_lu.action_information2 = 'LU'
109 AND pai_lu.action_information1 = 'PYFIUMFR'
110 AND pai_lu.action_information_category = 'EMEA REPORT INFORMATION'
111 AND pai_lu.action_context_type = 'PA'
112 AND pai_lu.action_context_id = csr_v_pa_id
113 AND pai_le.action_information2 = 'LE'
114 AND pai_le.action_information1 = 'PYFIUMFR'
115 AND pai_le.action_information_category = 'EMEA REPORT INFORMATION'
116 AND pai_le.action_context_type = 'PA'
117 AND pai_le.action_context_id = csr_v_pa_id
118 AND pai_tu.action_information2 = 'TU'
119 AND pai_tu.action_information1 = 'PYFIUMFR'
120 AND pai_tu.action_information_category = 'EMEA REPORT INFORMATION'
121 AND pai_tu.action_context_type = 'PA'
122 AND pai_tu.action_context_id = csr_v_pa_id
123 and pai.action_information_category = 'EMEA REPORT DETAILS'
124 AND pai.action_context_type = 'PA'
125 AND PAI.ACTION_CONTEXT_ID=csr_v_pa_id
126 order by pai.ACTION_INFORMATION2,pai.ACTION_INFORMATION4 ,pai_lu.action_information4 ;
127
128 umfr_rep csr_umfr_data%ROWTYPE;
129 L_TOTAL NUMBER :=0;
130 l_trade_union_name varchar2(2000):='XXX';
131 l_legal_employer_name varchar2(2000):= 'XXX';
132 l_local_unit_name varchar2(2000):= 'XXX';
133 L_LOCAL_TOTAL NUMBER :=0;
134 BEGIN
135 hr_utility.set_location ('Entered Procedure GETDATA', 10);
136
137
138 fnd_file.put_line (
139 fnd_file.LOG, 'payroll_action_id '||l_payroll_action_id
140 );
141
142 l_payroll_action_id:=get_archive_payroll_action_id( p_payroll_action_id);
143
144 fnd_file.put_line (
145 fnd_file.LOG, 'payroll_action_id '||l_payroll_action_id
146 );
147
148 FOR umfr_rep IN csr_umfr_data (l_payroll_action_id)
149 LOOP
150 fnd_file.put_line (
151 fnd_file.LOG, 'TRADE_UNION_NAME1 '||umfr_rep.trade_union_name
152 );
153
154
155 if l_trade_union_name <> umfr_rep.trade_union_name THEN
156 l_trade_union_name:=umfr_rep.trade_union_name;
157 gplsqltable (l_counter).tagname := 'TRADE_UNION_NAME';
158 gplsqltable (l_counter).tagvalue := pay_fi_general.xml_parser(TO_CHAR (umfr_rep.trade_union_name));
159 fnd_file.put_line (
160 fnd_file.LOG, 'TRADE_UNION_NAME '||gplsqltable (l_counter).tagvalue
161 );
162
163 l_counter := l_counter
164 + 1;
165
166
167 gplsqltable (l_counter).tagname := 'LEGAL_EMPLOYER_NAME';
168 gplsqltable (l_counter).tagvalue := pay_fi_general.xml_parser(TO_CHAR (umfr_rep.legal_employer_name));
169
170 l_counter := l_counter
171 + 1;
172 END IF;
173 if l_local_unit_name <> umfr_rep.local_unit_name THEN
174 if l_local_unit_name <> 'XXX' then
175 gplsqltable (l_counter).tagname := 'LOCAL_TOTAL';
176 gplsqltable (l_counter).tagvalue := TO_CHAR(L_LOCAL_TOTAL);
177 l_counter := l_counter
178 + 1;
179
180 gplsqltable (l_counter).tagname := 'LU';
181 gplsqltable (l_counter).tagvalue := 'END';
182
183 l_counter := l_counter
184 + 1;
185 end if;
186
187 l_local_unit_name:=umfr_rep.local_unit_name;
188 gplsqltable (l_counter).tagname := 'LU';
189 gplsqltable (l_counter).tagvalue := 'START';
190
191 l_counter := l_counter
192 + 1;
193
194 L_LOCAL_TOTAL:=0;
195 gplsqltable (l_counter).tagname := 'LOCAL_UNIT_NAME';
196 gplsqltable (l_counter).tagvalue := pay_fi_general.xml_parser(TO_CHAR (umfr_rep.local_unit_name));
197
198 l_counter := l_counter
199 + 1;
200 end if;
201 gplsqltable (l_counter).tagname := 'START';
202 gplsqltable (l_counter).tagvalue := 'START';
203
204 l_counter := l_counter
205 + 1;
206
207 gplsqltable (l_counter).tagname := 'RECORD_CODE';
208 gplsqltable (l_counter).tagvalue :=
209 TO_CHAR (umfr_rep.record_code);
210 l_counter := l_counter
211 + 1;
212 gplsqltable (l_counter).tagname := 'Y_NUMBER';
213 gplsqltable (l_counter).tagvalue :=
214 TO_CHAR (umfr_rep.y_number);
215 l_counter := l_counter
216 + 1;
217 gplsqltable (l_counter).tagname := 'Y_NUMBER_SPARE';
218 gplsqltable (l_counter).tagvalue :=
219 TO_CHAR (umfr_rep.y_number_spare);
220 l_counter := l_counter
221 + 1;
222 gplsqltable (l_counter).tagname := 'ACCOUNTING_ID';
223 gplsqltable (l_counter).tagvalue :=
224 TO_CHAR (umfr_rep.accounting_id);
225 l_counter := l_counter
226 + 1;
227 gplsqltable (l_counter).tagname := 'ACCOUNTING_ID_SPARE';
228 gplsqltable (l_counter).tagvalue :=
229 TO_CHAR (umfr_rep.accounting_id_spare);
230 l_counter := l_counter
231 + 1;
232 gplsqltable (l_counter).tagname := 'TRADE_UNION_NUMBER';
233 gplsqltable (l_counter).tagvalue :=
234 TO_CHAR (umfr_rep.trade_union_number);
235 l_counter := l_counter
236 + 1;
237 gplsqltable (l_counter).tagname := 'LOCAL_UNIT_NUMBER';
238 gplsqltable (l_counter).tagvalue :=
239 TO_CHAR (umfr_rep.local_unit_number);
240 l_counter := l_counter
241 + 1;
242 gplsqltable (l_counter).tagname := 'EMPLOYEE_PIN';
243 gplsqltable (l_counter).tagvalue :=
244 TO_CHAR (umfr_rep.employee_pin);
245 l_counter := l_counter
246 + 1;
247 gplsqltable (l_counter).tagname := 'EMPLOYEE_NAME';
248 gplsqltable (l_counter).tagvalue :=
249 pay_fi_general.xml_parser( TO_CHAR (umfr_rep.employee_name));
250 l_counter := l_counter
251 + 1;
252
253 /* Add the fields from Membership start date to Tax_year */
254
255 gplsqltable (l_counter).tagname := 'MEMBERSHIP_START';
256 gplsqltable (l_counter).tagvalue :=
257 umfr_rep.membership_start_date;
258 l_counter := l_counter
259 + 1;
260 gplsqltable (l_counter).tagname := 'MEMBERSHIP_END';
261 gplsqltable (l_counter).tagvalue :=
262 umfr_rep.membership_end_date;
263 l_counter := l_counter
264 + 1;
265
266
267 gplsqltable (l_counter).tagname := 'MEMBERSHIP_START_PDF';
268 gplsqltable (l_counter).tagvalue :=
269 to_char(umfr_rep.membership_start_date,'DD-MON-YYYY');
270 l_counter := l_counter
271 + 1;
272 gplsqltable (l_counter).tagname := 'MEMBERSHIP_END_PDF';
273 gplsqltable (l_counter).tagvalue :=
274 to_char(umfr_rep.membership_end_date,'DD-MON-YYYY');
275 l_counter := l_counter
276 + 1;
277 gplsqltable (l_counter).tagname := 'CURRENCY';
278 gplsqltable (l_counter).tagvalue := TO_CHAR (umfr_rep.currency);
279 l_counter := l_counter
280 + 1;
281 gplsqltable (l_counter).tagname := 'SIGN';
282 gplsqltable (l_counter).tagvalue := TO_CHAR (umfr_rep.SIGN);
283 l_counter := l_counter
284 + 1;
285
286 gplsqltable (l_counter).tagname := 'AMOUNT';
287 gplsqltable (l_counter).tagvalue :=
288 fnd_number.canonical_to_number(umfr_rep.amount);
289 l_local_total:= l_local_total+fnd_number.canonical_to_number(umfr_rep.amount);
290 l_total:=l_total+ fnd_number.canonical_to_number(umfr_rep.amount);
291
292
293 l_counter := l_counter
294 + 1;
295
296
297 gplsqltable (l_counter).tagname := 'REASON';
298 gplsqltable (l_counter).tagvalue := pay_fi_general.xml_parser(TO_CHAR (umfr_rep.reason));
299 l_counter := l_counter
300 + 1;
301 gplsqltable (l_counter).tagname := 'TAX_YEAR';
302 gplsqltable (l_counter).tagvalue := to_char(umfr_rep.tax_year);
303
304 l_counter := l_counter
305 + 1;
306 gplsqltable (l_counter).tagname := 'END';
307 gplsqltable (l_counter).tagvalue := 'END';
308
309 l_counter := l_counter
310 + 1;
311
312
313
314 END LOOP;
315 gplsqltable (l_counter).tagname := 'LOCAL_TOTAL';
316 gplsqltable (l_counter).tagvalue := L_LOCAL_TOTAL;
317 -- TO_CHAR(NVL(L_LOCAL_TOTAL,0) ,'999G999G990D99' );
318 -- TO_CHAR(NVL(FND_NUMBER.canonical_to_number (L_LOCAL_TOTAL),0) ,'999G999G990D99' );
319
320 l_counter := l_counter
321 + 1;
322
323 gplsqltable (l_counter).tagname := 'TOTAL';
324 gplsqltable (l_counter).tagvalue := L_TOTAL;
325 -- TO_CHAR(NVL(L_TOTAL,0) ,'999G999G990D99' );
326 -- TO_CHAR(NVL(FND_NUMBER.canonical_to_number(L_TOTAL),0) ,'999G999G990D99' );
327
328 writetoclob (p_xml);
329 -- fnd_file.put_line ( fnd_file.LOG, p_xml );
330
331
332
333
334 END get_data;
335
336
337 -----------------------------------------------------------------------------------------------------------------
338 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
339 IS
340 l_xfdf_string CLOB;
341 l_str1 VARCHAR2 (1000);
342 l_str2 VARCHAR2 (20);
343 l_str3 VARCHAR2 (20);
344 l_str4 VARCHAR2 (20);
345 l_str5 VARCHAR2 (20);
346 l_str6 VARCHAR2 (30);
347 l_str7 VARCHAR2 (1000);
348 l_str8 VARCHAR2 (240);
349 l_str9 VARCHAR2 (240);
350 l_str10 VARCHAR2 (20);
351 l_str11 VARCHAR2 (20);
352 l_total_start VARCHAR2 (20);
353 l_total_end VARCHAR2 (20);
354 l_strlustart VARCHAR2 (20);
355 l_strluend VARCHAR2 (20);
356
357 current_index PLS_INTEGER;
358 L_COUNTER PLS_INTEGER;
359 l_IANA_charset VARCHAR2 (50);
360 BEGIN
361 l_IANA_charset :=hr_fi_utility.get_IANA_charset ;
362 hr_utility.set_location ('Entering WritetoCLOB ', 70);
363 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
364 l_str2 := '<';
365 l_str3 := '>';
366 l_str4 := '</';
367 l_str5 := '>';
368 l_str6 := '</ROOT>';
369 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
370 l_str10 := '<UFMR>';
371 l_str11 := '</UFMR>';
372 l_total_start := '<TOTAL>';
373 l_total_end := '</TOTAL>';
374 l_strlustart := '<LU>';
375 l_strluend := '</LU>';
376
377
378 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
379 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
380 current_index := 0;
381
382 IF gplsqltable.COUNT > 0
383 THEN
384
385 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
386 /* l_str8 := gplsqltable (1).tagname;
387 l_str9 := gplsqltable (1).tagvalue;
388
389 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
390 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
391 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
392 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
393 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
394 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
395 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
396 l_str8 := gplsqltable (2).tagname;
397 l_str9 := gplsqltable (2).tagvalue;
398
399
400 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
401 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
402 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
403 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
404 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
405 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
406 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
407 */
408
409 fnd_file.put_line ( fnd_file.LOG, gplsqltable.COUNT );
410
411 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
412
413 LOOP
414 fnd_file.put_line ( fnd_file.LOG, l_xfdf_string );
415 l_str8 := gplsqltable (table_counter).tagname;
416 l_str9 := gplsqltable (table_counter).tagvalue;
417 IF l_str8 = 'END' THEN
418 fnd_file.put_line ( fnd_file.LOG, l_xfdf_string );
419 DBMS_LOB.writeappend (
420 l_xfdf_string,
421 LENGTH (l_str11),
422 l_str11
423 );
424 fnd_file.put_line ( fnd_file.LOG, l_xfdf_string );
425 ELSIF l_str8 = 'LU' THEN
426 fnd_file.put_line ( fnd_file.LOG, l_xfdf_string );
427 IF l_str9 ='START' THEN
428 DBMS_LOB.writeappend (
429 l_xfdf_string,
430 LENGTH (l_strlustart), l_strlustart );
431 ELSIF l_str9 ='END' THEN
432 DBMS_LOB.writeappend (
433 l_xfdf_string,
434 LENGTH (l_strluend), l_strluend );
435 END IF;
436
437 ELSIF l_str8 = 'TOTAL' THEN
438
439
440 IF gplsqltable.COUNT > 3 then
441 DBMS_LOB.writeappend (
442 l_xfdf_string,
443 LENGTH (l_strluend), l_strluend );
444
445
446 end if;
447
448 fnd_file.put_line ( fnd_file.LOG, l_xfdf_string );
449 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_total_start), l_total_start);
450 DBMS_LOB.writeappend (
451 l_xfdf_string,
452 LENGTH (l_str9),
453 l_str9
454 );
455
456 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_total_end), l_total_end);
457
458
459 ELSIF l_str8 = 'START' THEN
460 DBMS_LOB.writeappend (
461 l_xfdf_string,
462 LENGTH (l_str10), l_str10 );
463 ELSIF l_str9 IS NOT NULL THEN
464
465 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
466 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
467 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
468 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
469 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
470 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
471 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
472 ELSE
473 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
474 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
475 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
476 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
477 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
478 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
479
480 END IF;
481
482 END LOOP;
483
484
485
486 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
487 ELSE
488 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
489 END IF;
490 p_xfdf_clob := l_xfdf_string;
491 hr_utility.set_location ('Leaving WritetoCLOB ', 70);
492
493
494
495 hr_utility.set_location ('Leaving WritetoCLOB ', 70);
496 /* EXCEPTION
497 WHEN OTHERS
498 THEN
499 hr_utility.TRACE ( 'sqlerrm '
500 || SQLERRM);
501 hr_utility.raise_error;*/
502 END writetoclob;
503 -------------------------------------------------------------------------------------------------------------------------
504 END PAY_FI_UMFR;