[Home] [Help]
PACKAGE BODY: APPS.PAY_US_XDO_REPORT
Source
1 PACKAGE BODY PAY_US_XDO_REPORT AS
2 /* $Header: payusxml.pkb 120.6.12010000.3 2008/08/06 06:42:21 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Description : This package prepares XML data and template
24 required for GTN Report
25
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ----------- ---------- ----- ------- -----------------------------------
30 07-DEC-2004 sgajula 115.0 Created
31 11-DEC-2004 sgajula 115.1 Changed NULL to to_char(NULL) for
32 db compliance
33 04-FEB-2005 sgajula 115.2 Added code to display summary and
34 Total Net for Last Classification
35 04-MAR-2005 rdhingra 115.3 Modified code to display correct
36 Total Net for Last classification
37 05-MAR-2005 ahanda 115.4 4222867 Changed sql which fetches full_name
38 to remove <, > and &.
39 30-SEP-2005 ahanda 115.5 4639655 Added l_countlimit to retrict the data
40 in a row in vXMLTable
41 23-NOV-2005 rdhingra 115.6 4742356 Updated value of l_countlimit
42 02-DEC-2005 rdhingra 115.7 4771769 Added CDATA to take care of special char
43 from different names. Reverted changes of
44 sql to take care of special char from full_name
45 19-JAN-2006 rdhingra 115.8 4960092 Modified cursor c_unpay_details in
46 procedure write_unpay_details to show the
47 details of unpaid payments at the lowest sort
48 level
49 07-Apr-2006 rdhingra 115.9 5148084 Removed xml PI(processing instruction) from
50 the procedure write_header.
51 Removed Procedure FETCH_RTF_BLOB
52 19-Feb-2007 saurgupt 115.10 5862861 Modified procedures WRITE_DETAIL_RECORDS and
53 WRITE_UNPAY_DETAILS. Added the condition to check
54 the length of xmlstring to avoid overflow error.
55
56
57 */
58 g_proc_name VARCHAR2(240);
59 l_sort1 VARCHAR2(10);
60 l_sort2 VARCHAR2(10);
61 l_sort3 VARCHAR2(10);
62 l_asg_flag VARCHAR2(1);
63 l_consolidation_set_id NUMBER;
64 l_payroll_id NUMBER;
65 l_gre_id NUMBER;
66 l_tot_gross_earn_class NUMBER := 0;
67 l_tot_imput_earn_class NUMBER := 0;
68 l_tot_gross_pay_class NUMBER := 0;
69 l_tot_gross_non_pay_class NUMBER := 0;
70 l_tot_vol_ded_class NUMBER := 0;
71 l_tot_invol_ded_class NUMBER := 0;
72 l_tot_ee_tax_class NUMBER := 0;
73 l_total_net_class NUMBER := 0;
74 l_tot_pre_tax_ded_class NUMBER := 0;
75 vCtr NUMBER := 0;
76 l_pact_id NUMBER;
77 l_business_group_id NUMBER;
78 l_param pay_payroll_actions.legislative_parameters%type;
79 l_start_date DATE;
80 l_end_date DATE;
81
82 PROCEDURE GET_PARAMETERS
83 (
84 p_ppa_finder IN NUMBER
85 ) IS
86
87 CURSOR c_params(c_p_ppa_finder NUMBER) IS
88 SELECT tax_unit_id,
89 attribute2,
90 to_number(attribute3),
91 to_date(attribute4,'MM/DD/YYYY'),
92 to_date(attribute5,'MM/DD/YYYY')
93 FROM pay_us_rpt_totals
94 WHERE organization_id = to_number(p_ppa_finder)
95 AND attribute1 = 'GTN';
96
97 l_proc_name VARCHAR2(100);
98
99 BEGIN
100
101 l_proc_name := g_proc_name || 'GET_PARAMETERS';
102 hr_utility.trace ('Entering '|| l_proc_name);
103
104 hr_utility.trace (' p_ppa_finder '|| p_ppa_finder );
105
106 OPEN c_params(p_ppa_finder);
107 FETCH c_params INTO l_pact_id,
108 l_param,
109 l_business_group_id,
110 l_start_date,
111 l_end_date;
112 CLOSE c_params;
113 /*
114 hr_utility.trace (' l_pact_id '|| l_pact_id );
115 hr_utility.trace (' l_param '|| l_param );
116 hr_utility.trace (' l_business_group_id '|| l_business_group_id );
117 hr_utility.trace (' l_start_date '|| l_start_date );
118 hr_utility.trace (' l_end_date '|| l_end_date );
119 */
120 l_consolidation_set_id
121 := pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',l_param);
122 l_payroll_id := pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',l_param);
123 l_gre_id := pay_paygtn_pkg.get_parameter('TRANSFER_GRE',l_param);
124 l_sort1 := pay_paygtn_pkg.get_parameter('TRANSFER_SORT1',l_param);
125 l_sort2 := pay_paygtn_pkg.get_parameter('TRANSFER_SORT2',l_param);
126 l_sort3 := pay_paygtn_pkg.get_parameter('TRANSFER_SORT3',l_param);
127 l_asg_flag := NVL(pay_paygtn_pkg.get_parameter('TRANSFER_EMP_INFO',l_param),'N');
128 /*
129 hr_utility.trace (' l_consolidation_set_id '|| l_consolidation_set_id );
130 hr_utility.trace (' l_payroll_id '|| l_payroll_id );
131 hr_utility.trace (' l_gre_id '|| l_gre_id );
132 hr_utility.trace (' l_sort1 '|| l_sort1 );
133 hr_utility.trace (' l_sort2 '|| l_sort2 );
134 hr_utility.trace (' l_sort3 '|| l_sort3 );
135 hr_utility.trace (' l_asg_flag '|| l_asg_flag );
136 */
137 hr_utility.trace ('Leaving '||l_proc_name);
138
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 hr_utility.trace ('Inside Exception WHEN NO_DATA_FOUND for '|| l_proc_name);
142 END GET_PARAMETERS;
143
144 /* This Procedure writes the header details for GTN Report*/
145 PROCEDURE WRITE_HEADER
146 IS
147 l_business_group_name hr_organization_units.name%TYPE;
148 l_payroll_name VARCHAR2(80);
149 l_print_set_payroll_name VARCHAR2(120);
150 l_consolidation_set_name VARCHAR2(80);
151 l_gre_name VARCHAR2(80);
152 l_print_sort1_name VARCHAR2(100);
153 l_print_sort2_name VARCHAR2(100);
154 l_print_sort3_name VARCHAR2(100);
155 l_proc_name VARCHAR2(100);
156
157 BEGIN
158
159 l_proc_name := g_proc_name || 'write_header';
160 hr_utility.trace ('Entering '|| l_proc_name);
161
162 l_business_group_name := hr_reports.get_business_group(l_business_group_id);
163
164 --hr_utility.trace (' l_business_group_name : '|| l_business_group_name );
165 --hr_utility.trace (' l_payroll_id : '|| l_payroll_id );
166
167 IF l_payroll_id IS NOT NULL THEN
168 SELECT distinct substr(payroll_name,1,80),
169 substr('Payroll : '||payroll_name,1,80)
170 INTO l_payroll_name,
171 l_print_set_payroll_name
172 FROM pay_payrolls_f
173 WHERE payroll_id = l_payroll_id
174 AND effective_start_date <= l_end_date
175 AND effective_end_date >= l_end_date;
176 END IF;
177
178 --hr_utility.trace (' l_payroll_name : '|| l_payroll_name );
179 --hr_utility.trace (' l_print_set_payroll_name : '|| l_print_set_payroll_name );
180 --hr_utility.trace (' l_consolidation_set_id : '|| l_consolidation_set_id );
181
182 IF l_payroll_id IS NULL AND l_consolidation_set_id IS NOT NULL THEN
183 SELECT consolidation_set_name,
184 substr('Consolidation Set : '||consolidation_set_name,1,80)
185 INTO l_consolidation_set_name,
186 l_print_set_payroll_name
187 FROM pay_consolidation_sets
188 WHERE consolidation_set_id = l_consolidation_set_id;
189 END IF;
190
191 --hr_utility.trace (' consolidation_set_name : '|| l_consolidation_set_name );
192
193 IF l_payroll_id IS NOT NULL AND l_consolidation_set_id IS NOT NULL THEN
194 SELECT consolidation_set_name,
195 substr('Consolidation Set : '||consolidation_set_name,1,80)
196 INTO l_consolidation_set_name,
197 l_print_set_payroll_name
198 FROM pay_consolidation_sets
199 WHERE consolidation_set_id = l_consolidation_set_id;
200 END IF;
201
202 --hr_utility.trace (' l_gre_id : '|| l_gre_id );
203
204 IF l_gre_id IS NOT NULL THEN
205 SELECT substr(name,1,80)
206 INTO l_gre_name
207 FROM hr_organization_units
208 WHERE organization_id = l_gre_id;
209 END IF;
210
211 --hr_utility.trace (' l_gre_name : '|| l_gre_name );
212
213 IF l_sort1 IS NOT NULL THEN
214 l_print_sort1_name := hr_general.decode_lookup('PAY_GTN_SORT',l_sort1) || ' Name';
215 END IF;
216 IF l_sort2 IS NOT NULL THEN
217 l_print_sort2_name := hr_general.decode_lookup('PAY_GTN_SORT',l_sort2) || ' Name';
218 END IF;
219 IF l_sort3 IS NOT NULL THEN
220 l_print_sort3_name := hr_general.decode_lookup('PAY_GTN_SORT',l_sort3) || ' Name';
221 END IF;
222 /*
223 hr_utility.trace (' l_sort1 : '|| l_sort1 );
224 hr_utility.trace (' l_sort2 : '|| l_sort2 );
225 hr_utility.trace (' l_sort3 : '|| l_sort3 );
226 */
227 vXMLTable.DELETE;
228 vCtr := 0;
229
230 /*Removed the xml PI(processing instruction) as the core package inserts it*/
231 vXMLTable(vCtr).xmlstring := '';
232 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
233 || '<start>';
234 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
235 || '<header>';
236 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
237 || '<bgname>'
238 || '<![CDATA[ '|| l_business_group_name || ' ]]>'
239 || '</bgname>';
240
241 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
242 || '<DateHeader>'
243 || to_char(sysdate,'DD-MON-YYYY HH24:MI')
244 || '</DateHeader>';
245 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
246 || '<StartDate>'
247 || to_char(l_start_date,'DD-MON-YYYY')
248 || '</StartDate>';
249 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
250 || '<EndDate>'
251 || to_char(l_end_date,'DD-MON-YYYY')
252 || '</EndDate>';
253 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
254 || '<greHeaderName>'
255 || '<![CDATA[ '|| l_gre_name || ' ]]>'|| '</greHeaderName>';
256 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
257 || '<PayrollHeaderName>'
258 || '<![CDATA[ '|| l_payroll_name || ' ]]>'
259 || '</PayrollHeaderName>';
260 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
261 || '<ConsolidationSetName>'
262 || '<![CDATA[ '|| l_consolidation_set_name || ' ]]>'
263 || '</ConsolidationSetName>';
264 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
265 || '<Sort1HeaderName>'
266 || '<![CDATA[ '|| l_print_sort1_name || ' ]]>'
267 || '</Sort1HeaderName>';
268 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
269 || '<Sort2HeaderName>'
270 || '<![CDATA[ '|| l_print_sort2_name || ' ]]>'
271 || '</Sort2HeaderName>';
272 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
273 || '<Sort3HeaderName>'
274 || '<![CDATA[ '|| l_print_sort3_name || ' ]]>'
275 || '</Sort3HeaderName>';
276 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
277 || '</header>';
278
279 -- hr_utility.trace (' vXMLTable(vCtr).xmlstring : '|| vXMLTable(vCtr).xmlstring );
280
281 hr_utility.trace ('Leaving '|| l_proc_name);
282
283 END WRITE_HEADER;
284
285
286 PROCEDURE WRITE_DETAIL_RECORDS
287 (
288 p_xfdf_blob OUT NOCOPY BLOB
289 ) IS
290
291 cursor c_detail_records(cp_sort1 varchar2,
292 cp_sort2 varchar2,
293 cp_sort3 varchar2,
294 cp_asg_flag varchar2,
295 cp_pact_id number)
296 IS
297 SELECT DECODE (LOWER (cp_sort1),
298 'loc', 'Location Name : ' || location_name,
299 'gre', 'GRE Name : ' || gre_name,
300 'org', 'Organization Name : ' || organization_name,
301 'GRE Name : ' || gre_name
302 ) sort1_name,
303 DECODE (LOWER (cp_sort2),
304 'loc', 'Location Name : ' || location_name,
305 'gre', 'GRE Name : ' || gre_name,
306 'org', 'Organization Name : ' || organization_name,
307 to_char(NULL)
308 ) sort2_name,
309 DECODE (LOWER (cp_sort3),
310 'loc', 'Location Name : ' || location_name,
311 'gre', 'GRE Name : ' || gre_name,
312 'org', 'Organization Name : ' || organization_name,
313 to_char(NULL)
314 )sort3_name,
315 attribute12 emp_name,
316 TO_NUMBER (attribute2) class_seq1, TO_NUMBER (attribute3) sub_class1,
317 attribute4 classification1, attribute5 element_name1,
318 SUM (value2) run_val1, to_number(SUM (value3)) run_hours1, COUNT (*) tot_count1,
319 to_char(business_group_id) person_id
320 FROM pay_us_rpt_totals
321 WHERE cp_sort3 IS NOT NULL AND tax_unit_id = cp_pact_id
322 AND attribute1 <> 'GTN'
323 AND cp_asg_flag = 'Y'
324 GROUP BY DECODE (LOWER (cp_sort1),
325 'loc', 'Location Name : ' || location_name,
326 'gre', 'GRE Name : ' || gre_name,
327 'org', 'Organization Name : ' || organization_name,
328 'GRE Name : ' || gre_name
329 ),
330 DECODE (LOWER (cp_sort2),
331 'loc', 'Location Name : ' || location_name,
332 'gre', 'GRE Name : ' || gre_name,
333 'org', 'Organization Name : ' || organization_name,
334 to_char(NULL)
335 ),
336 DECODE (LOWER (cp_sort3),
337 'loc', 'Location Name : ' || location_name,
338 'gre', 'GRE Name : ' || gre_name,
339 'org', 'Organization Name : ' || organization_name,
340 to_char(NULL)
341 ),
342 to_char(business_group_id),
343 attribute12,
344 TO_NUMBER (attribute2),
345 TO_NUMBER (attribute3),
346 attribute4,
347 attribute5
348 HAVING ( DECODE (SIGN (SUM (value2)),
349 1, SUM (value2),
350 -1, -1 * SUM (value2),
351 0
352 ) > 0
353 OR DECODE (SIGN (SUM (value3)),
354 1, SUM (value3),
355 -1, -1 * SUM (value3),
356 0
357 ) > 0
358 )
359 UNION
360 SELECT DECODE (LOWER (cp_sort1),
361 'loc', 'Location Name : ' || location_name,
362 'gre', 'GRE Name : ' || gre_name,
363 'org', 'Organization Name : ' || organization_name,
364 'GRE Name : ' || gre_name
365 ) sort1_name,
366 DECODE (LOWER (cp_sort2),
367 'loc', 'Location Name : ' || location_name,
368 'gre', 'GRE Name : ' || gre_name,
369 'org', 'Organization Name : ' || organization_name,
370 to_char(NULL)
371 ) sort2_name,
372 DECODE (LOWER (cp_sort3),
373 'loc', 'Location Name : ' || location_name,
374 'gre', 'GRE Name : ' || gre_name,
375 'org', 'Organization Name : ' || organization_name,
376 to_char(NULL)
377 ) sort3_name,
378 to_char(NULL),
379 TO_NUMBER (attribute2) class_seq1, TO_NUMBER (attribute3) sub_class1,
380 attribute4 classification1, attribute5 element_name1,
381 SUM (value2) run_val1, SUM (value3) run_hours1, COUNT (*) tot_count1,
382 to_char(NULL) person_id
383 FROM pay_us_rpt_totals
384 WHERE cp_sort3 IS NOT NULL AND tax_unit_id = cp_pact_id
385 AND attribute1 <> 'GTN'
386 GROUP BY DECODE (LOWER (cp_sort1),
387 'loc', 'Location Name : ' || location_name,
388 'gre', 'GRE Name : ' || gre_name,
389 'org', 'Organization Name : ' || organization_name,
390 'GRE Name : ' || gre_name
391 ),
392 DECODE (LOWER (cp_sort2),
393 'loc', 'Location Name : ' || location_name,
394 'gre', 'GRE Name : ' || gre_name,
395 'org', 'Organization Name : ' || organization_name,
396 to_char(NULL)
397 ),
398 DECODE (LOWER (cp_sort3),
399 'loc', 'Location Name : ' || location_name,
400 'gre', 'GRE Name : ' || gre_name,
401 'org', 'Organization Name : ' || organization_name,
402 to_char(NULL)
403 ),
404 to_char(NULL),
405 TO_NUMBER (attribute2),
406 TO_NUMBER (attribute3),
407 attribute4,
408 attribute5
409 HAVING ( DECODE (SIGN (SUM (value2)),
410 1, SUM (value2),
411 -1, -1 * SUM (value2),
412 0
413 ) > 0
414 OR DECODE (SIGN (SUM (value3)),
415 1, SUM (value3),
416 -1, -1 * SUM (value3),
417 0
418 ) > 0
419 )
420 UNION
421 SELECT DECODE (LOWER (cp_sort1),
422 'loc', 'Location Name : ' || location_name,
423 'gre', 'GRE Name : ' || gre_name,
424 'org', 'Organization Name : ' || organization_name,
425 'GRE Name : ' || gre_name
426 ) sort1_name,
427 DECODE (LOWER (cp_sort2),
428 'loc', 'Location Name : ' || location_name,
429 'gre', 'GRE Name : ' || gre_name,
430 'org', 'Organization Name : ' || organization_name,
431 to_char(NULL)
432 ) sort2_name,
433 to_char(NULL) sort3_name,to_char(NULL) emp_name, TO_NUMBER (attribute2) class_seq1,
434 TO_NUMBER (attribute3) sub_class1, attribute4 classification1,
435 attribute5 element_name1, SUM (value2) run_val1,
436 SUM (value3) run_hours1, COUNT (*) tot_count1,
437 to_char(NULL) person_id
438 FROM pay_us_rpt_totals
439 WHERE cp_sort2 IS NOT NULL AND tax_unit_id = cp_pact_id
440 AND attribute1 <> 'GTN'
441 GROUP BY DECODE (LOWER (cp_sort1),
442 'loc', 'Location Name : ' || location_name,
443 'gre', 'GRE Name : ' || gre_name,
444 'org', 'Organization Name : ' || organization_name,
445 'GRE Name : ' || gre_name
446 ),
447 DECODE (LOWER (cp_sort2),
448 'loc', 'Location Name : ' || location_name,
449 'gre', 'GRE Name : ' || gre_name,
450 'org', 'Organization Name : ' || organization_name,
451 to_char(NULL)
452 ),
453 to_char(NULL),
454 to_char(NULL),
455 TO_NUMBER (attribute2),
456 TO_NUMBER (attribute3),
457 attribute4,
458 attribute5
459 HAVING ( DECODE (SIGN (SUM (value2)),
460 1, SUM (value2),
461 -1, -1 * SUM (value2),
462 0
463 ) > 0
464 OR DECODE (SIGN (SUM (value3)),
465 1, SUM (value3),
466 -1, -1 * SUM (value3),
467 0
468 ) > 0
469 )
470 UNION
471 SELECT DECODE (LOWER (cp_sort1),
472 'loc', 'Location Name : ' || location_name,
473 'gre', 'GRE Name : ' || gre_name,
474 'org', 'Organization Name : ' || organization_name,
475 'GRE Name : ' || gre_name
476 ) sort1_name,
477 to_char(NULL) sort2_name, to_char(NULL) sort3_name,to_char(NULL) emp_name, TO_NUMBER (attribute2) class_seq1,
478 TO_NUMBER (attribute3) sub_class1, attribute4 classification1,
479 attribute5 element_name1, SUM (value2) run_val1,
480 SUM (value3) run_hours1, COUNT (*) tot_count1,
481 to_char(NULL) person_id
482 FROM pay_us_rpt_totals
483 WHERE tax_unit_id = cp_pact_id AND attribute1 <> 'GTN'
484 GROUP BY DECODE (LOWER (cp_sort1),
485 'loc', 'Location Name : ' || location_name,
486 'gre', 'GRE Name : ' || gre_name,
487 'org', 'Organization Name : ' || organization_name,
488 'GRE Name : ' || gre_name
489 ),
490 to_char(NULL),
491 to_char(NULL),
492 to_char(NULL),
493 TO_NUMBER (attribute2),
494 TO_NUMBER (attribute3),
495 attribute4,
496 attribute5
497 HAVING ( DECODE (SIGN (SUM (value2)),
498 1, SUM (value2),
499 -1, -1 * SUM (value2),
500 0
501 ) > 0
502 OR DECODE (SIGN (SUM (value3)),
503 1, SUM (value3),
504 -1, -1 * SUM (value3),
505 0
506 ) > 0
507 )
508 ORDER BY 1, 2, 3, 4, 5,6;
509
510 l_person_id NUMBER;
511 l_class_seq NUMBER;
512 l_sub_class NUMBER;
513 l_temp_count NUMBER := 0;
514 l_hours_sum NUMBER := 0;
515 l_val_sum NUMBER := 0;
516 l_count NUMBER := 0;
517 l_tot_gross_earn NUMBER := 0;
518 l_tot_imput_earn NUMBER := 0;
519 l_tot_gross_pay NUMBER := 0;
520 l_tot_gross_non_pay NUMBER := 0;
521 l_tot_vol_ded NUMBER := 0;
522 l_tot_invol_ded NUMBER := 0;
523 l_tot_ee_tax NUMBER := 0;
524 l_total_net NUMBER := 0;
525 l_tot_pre_tax_ded NUMBER := 0;
526 l_countloop NUMBER := 0;
527 l_countlimit NUMBER := 50;
528 l_sort1_name VARCHAR2(240);
529 l_sort2_name VARCHAR2(240);
530 l_sort3_name VARCHAR2(240);
531 l_class_name VARCHAR2(80);
532 l_proc_name VARCHAR2(100);
533 l_temp_print varchar2 (1500);
534 BEGIN
535
536 l_proc_name := g_proc_name || 'WRITE_DETAIL_RECORDS';
537 hr_utility.trace ('Entering '|| l_proc_name);
538 /*
539 hr_utility.trace (' l_sort1 : ' || l_sort1 );
540 hr_utility.trace (' l_sort2 : ' || l_sort2 );
541 hr_utility.trace (' l_sort3 : ' || l_sort3 );
542 hr_utility.trace (' l_asg_flag : ' || l_asg_flag );
543 hr_utility.trace (' l_pact_id : ' || l_pact_id );
544 */
545 vCtr := vCtr + 1;
546 vXMLTable(vCtr).xmlstring := '';
547 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<detailblock>';
548
549 FOR detrec IN c_detail_records(l_sort1 ,
550 l_sort2 ,
551 l_sort3 ,
552 l_asg_flag ,
553 l_pact_id
554 )
555 LOOP
556 /* Check whether it is first record.If it is first record initialize the values*/
557 -- hr_utility.trace (' Loop Start length of vXMLTable(vCtr).xmlstring : ' || length(vXMLTable(vCtr).xmlstring));
558 -- bug 5862861 :
559 if length(vXMLTable(vCtr).xmlstring) > 30000 then
560 vCtr := vCtr + 1;
561 vXMLTable(vCtr).xmlstring := '';
562 end if;
563
564 -- hr_utility.trace (' l_temp_count : ' || l_temp_count ||' , '||' row count : ' || c_detail_records%rowcount );
565
566 IF l_temp_count <> 0 THEN
567 /* The record is not the first Record */
568 /* Check whether it is new sort clause.If it is a new sort group print the Summary values
569 for Previous sort group, reset them and open new sort group*/
570 /*
571 hr_utility.trace (' l_temp_count <> 0 ' );
572 hr_utility.trace (' l_sort1_name : ' || l_sort1_name ||' , '||' detrec.sort1_name : ' || detrec.sort1_name );
573 hr_utility.trace (' l_sort2_name : ' || l_sort2_name ||' , '||' detrec.sort2_name : ' || detrec.sort2_name );
574 hr_utility.trace (' l_sort3_name : ' || l_sort3_name ||' , '||' detrec.sort3_name : ' || detrec.sort3_name );
575 hr_utility.trace (' l_person_id : ' || l_person_id ||' , '||' detrec.person_id : ' || detrec.person_id );
576 */
577
578 if (l_sort1_name = detrec.sort1_name or (l_sort1_name is NULL AND detrec.sort1_name is NULL))
579 AND (l_sort2_name = detrec.sort2_name or (l_sort2_name is NULL AND detrec.sort2_name is NULL))
580 AND (l_sort3_name = detrec.sort3_name or (l_sort3_name is NULL AND detrec.sort3_name is NULL))
581 AND (l_person_id = detrec.person_id or (l_person_id is NULL AND detrec.person_id is NULL)) THEN
582 /* Same sort Group*/
583 /* Check whether its a new classification*/
584 hr_utility.trace (' Same sort Group ' );
585 /*
586 hr_utility.trace (' l_class_seq : ' || l_class_seq ||' , '||' detrec.class_seq1 : ' || detrec.class_seq1 );
587
588 hr_utility.trace (' l_sub_class : ' || l_sub_class ||' , '||' detrec.sub_class1 : ' || detrec.sub_class1 );
589 */
590 if l_class_seq = detrec.class_seq1 and l_sub_class = detrec.sub_class1 THEN
591 NULL;
592 else -- new classification
593 /* Summary for the classification should not be displayed for Unpaid payments and Reversals,
594 so added a check*/
595 if l_class_seq not in (9,10) then
596 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotEleName>'||'Total '||l_class_name||'</TotEleName>';
597
598 hr_utility.trace (' l_class_seq not in 9,10 ' );
599 --
600 if l_hours_sum = 0 THEN
601 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||NULL||'</HourSubTot>';
602 else
603 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||l_hours_sum||'</HourSubTot>';
604 end if;
605 --
606 if l_val_sum = 0 THEN
607 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||NULL||'</ValSubTot>';
608 else
609 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||l_val_sum||'</ValSubTot>';
610 end if;
611 --
612 if l_count = 0 THEN
613 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||NULL||'</CountSubTot>';
614 else
615 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||l_count||'</CountSubTot>';
616 end if;
617 --
618 end if; --l_class_seq not in (9,10)
619 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassGroup>';
620 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassGroup>';
621 l_hours_sum := 0;
622 l_val_sum := 0;
623 l_count := 0;
624 l_class_seq := detrec.class_seq1;
625 l_sub_class := detrec.sub_class1;
626 l_class_name := detrec.classification1;
627
628 --hr_utility.trace (' l_class_name : ' || l_class_name );
629
630 -- vCtr := vCtr + 1;
631 /* if vCtr >= 1000 THEN
632 WRITE_TO_CLOB(p_xfdf_blob);
633 vCtr := 0;
634 vXMLTable.DELETE;
635 end if;*/
636 -- vXMLTable(vCtr).xmlstring := ' ';
637
638 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassName>'||detrec.classification1||'</ClassName>';
639 --hr_utility.trace (' vXMLTable(vCtr).xmlstring : ' || vXMLTable(vCtr).xmlstring );
640 /*to be deleted*/
641 --start
642 /*
643 if length(vXMLTable(vCtr).xmlstring) >= 1 then
644 hr_utility.trace (' length of vXMLTable(vCtr).xmlstring : ' || length(vXMLTable(vCtr).xmlstring) );
645 l_temp_print := substr(vXMLTable(vCtr).xmlstring , 1 , 1500);
646 hr_utility.trace (' l_temp_print : ' || l_temp_print );
647 end if;
648 */
649
650 --end
651 end if; -- new classification
652 else -- else of sort clause
653 /* For Totals By Classification Region Summary of totals should be displayed.
654 This section will calculate the same*/
655 hr_utility.trace (' else of sort ' );
656
657 if l_sort2_name is NULL and l_sort3_name is NULL THEN
658 l_tot_gross_earn_class := l_tot_gross_earn_class + l_tot_gross_earn;
659 l_tot_imput_earn_class := l_tot_imput_earn_class + l_tot_imput_earn;
660 l_tot_gross_pay_class := l_tot_gross_pay_class + l_tot_gross_pay;
661 l_tot_pre_tax_ded_class := l_tot_pre_tax_ded_class + l_tot_pre_tax_ded;
662 l_tot_gross_non_pay_class := l_tot_gross_non_pay_class + l_tot_gross_non_pay;
663 l_tot_vol_ded_class := l_tot_vol_ded_class + l_tot_vol_ded;
664 l_tot_invol_ded_class := l_tot_invol_ded_class + l_tot_invol_ded;
665 l_tot_ee_tax_class := l_tot_ee_tax_class + l_tot_ee_tax;
666 end if;
667 /*End of Calculation Section for Summary of Totals in Totals By Classification Region*/
668
669 /* Fix to show summary details of last classification in the sort group*/
670 if l_class_seq not in (9,10) then
671 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotEleName>'||'Total '||l_class_name||'</TotEleName>';
672 --
673 if l_hours_sum = 0 THEN
674 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||NULL||'</HourSubTot>';
675 else
676 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||l_hours_sum||'</HourSubTot>';
677 end if;
678 --
679 if l_val_sum = 0 THEN
680 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||NULL||'</ValSubTot>';
681 else
682 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||l_val_sum||'</ValSubTot>';
683 end if;
684 --
685 if l_count = 0 THEN
686 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||NULL||'</CountSubTot>';
687 else
688 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||l_count||'</CountSubTot>';
689 end if;
690 end if; -- l_class_seq not in (9,10)
691
692 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassGroup>';
693
694 /*This Section prints Summary Totals for Each Sort Group*/
695 l_total_net := nvl(l_tot_gross_pay,0) +
696 nvl(l_tot_gross_non_pay,0) -
697 nvl(l_tot_ee_tax,0) -
698 nvl(l_tot_vol_ded,0) -
699 nvl(l_tot_pre_tax_ded,0) -
700 nvl(l_tot_invol_ded,0);
701
702 --hr_utility.trace (' l_total_net : ' || l_total_net );
703 --hr_utility.trace (' l_tot_gross_earn : ' || l_tot_gross_earn );
704
705 --
706 if l_tot_gross_earn <> 0 THEN
707
708 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
709 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Earnings :'||'</TotGrossName>';
710 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_earn||'</TotGrossVal>';
711 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
712
713 end if;
714 --
715 if l_tot_imput_earn <> 0 THEN
716
717 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
718 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Imputed Earnings :'||'</TotGrossName>';
719 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_imput_earn||'</TotGrossVal>';
720 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
721
722 end if;
723 --
724 if l_tot_gross_pay <> 0 THEN
725
726 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
727 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Pay :'||'</TotGrossName>';
728 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_pay||'</TotGrossVal>';
729 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
730
731 end if;
732 --
733 if l_tot_pre_tax_ded <> 0 THEN
734
735 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
736 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Pre-Tax Deductions :'||'</TotGrossName>';
737 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_pre_tax_ded||'</TotGrossVal>';
738 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
739
740 end if;
741 --
742 if l_tot_gross_non_pay <> 0 THEN
743
744 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
745 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Non-Payroll Payments :'||'</TotGrossName>';
746 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_non_pay||'</TotGrossVal>';
747 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
748
749 end if;
750 --
751 if l_tot_vol_ded <> 0 THEN
752
753 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
754 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Voluntary :'||'</TotGrossName>';
755 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_vol_ded||'</TotGrossVal>';
756 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
757
758 end if;
759 --
760 if l_tot_invol_ded <> 0 THEN
761
762 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
763 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Involuntary :'||'</TotGrossName>';
764 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_invol_ded||'</TotGrossVal>';
765 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
766
767 end if;
768 --
769 if l_tot_ee_tax <> 0 THEN
770
771 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
772 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total EE Tax :'||'</TotGrossName>';
773 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_ee_tax||'</TotGrossVal>';
774 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
775
776 end if;
777 --
778
779 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
780 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Net :'||'</TotGrossName>';
781 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_total_net||'</TotGrossVal>';
782 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
783
784 /* End of Summary Totals for Sort Group*/
785 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortGroup>';
786 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortGroup>';
787
788 /* Reset Sort Group Variables an Classification Group varaibles */
789 l_hours_sum := 0;
790 l_val_sum := 0;
791 l_count := 0;
792 l_tot_gross_earn :=0;
793 l_tot_ee_tax := 0;
794 l_tot_invol_ded := 0;
795 l_tot_vol_ded :=0;
796 l_tot_gross_non_pay := 0;
797 l_tot_pre_tax_ded := 0;
798 l_tot_gross_pay := 0;
799 l_tot_imput_earn := 0;
800
801 /*Print header details for New Sort Group */
802 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort1Name>'||'<![CDATA[ '||detrec.sort1_name|| ' ]]>'||'</Sort1Name>';
803 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort2Name>'||'<![CDATA[ '||detrec.sort2_name|| ' ]]>'||'</Sort2Name>';
804 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort3Name>'||'<![CDATA[ '||detrec.sort3_name|| ' ]]>'||'</Sort3Name>';
805 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EmpName>'||'<![CDATA[ '||detrec.emp_name|| ' ]]>'||'</EmpName>';
806 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassGroup>';
807 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassName>'||detrec.classification1||'</ClassName>';
808
809 /*Initialize Sort Group Variables and Classification Group varaibles*/
810 l_sort1_name := detrec.sort1_name;
811 l_sort2_name := detrec.sort2_name;
812 l_sort3_name := detrec.sort3_name;
813 l_person_id := detrec.person_id;
814 l_class_seq := detrec.class_seq1;
815 l_sub_class := detrec.sub_class1;
816 l_class_name := detrec.classification1;
817 end if; -- end if of sort clause
818 else -- l_temp_count <> 0
819 /* This Block will be executed for First Sort Group only,so this is called only once*/
820 /*Initialize Sort Group Variables */
821 --hr_utility.trace (' in else of l_temp_count <> 0 : ' );
822
823 l_sort1_name := detrec.sort1_name;
824 l_sort2_name := detrec.sort2_name;
825 l_sort3_name := detrec.sort3_name;
826 l_person_id := detrec.person_id;
827 l_class_seq := detrec.class_seq1;
828 l_sub_class := detrec.sub_class1;
829 l_temp_count := 1;
830 /*Print header details for New Sort Group */
831 /*
832 hr_utility.trace (' in else l_temp_count : ' || l_temp_count );
833 hr_utility.trace (' in else l_sort1_name : ' || l_sort1_name ||' , '||' detrec.sort1_name : ' || detrec.sort1_name );
834 hr_utility.trace (' in else l_sort2_name : ' || l_sort2_name ||' , '||' detrec.sort2_name : ' || detrec.sort2_name );
835 hr_utility.trace (' in else l_sort3_name : ' || l_sort3_name ||' , '||' detrec.sort3_name : ' || detrec.sort3_name );
836 hr_utility.trace (' in else l_person_id : ' || l_person_id ||' , '||' detrec.person_id : ' || detrec.person_id );
837 hr_utility.trace (' in else l_class_seq : ' || l_class_seq ||' , '||' detrec.class_seq1 : ' || detrec.class_seq1 );
838 hr_utility.trace (' in else l_sub_class : ' || l_sub_class ||' , '||' detrec.sub_class1 : ' || detrec.sub_class1 );
839 */
840
841 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortGroup>';
842 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort1Name>'||'<![CDATA[ '||detrec.sort1_name|| ' ]]>'||'</Sort1Name>';
843 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort2Name>'||'<![CDATA[ '||detrec.sort2_name|| ' ]]>'||'</Sort2Name>';
844 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<Sort3Name>'||'<![CDATA[ '||detrec.sort3_name|| ' ]]>'||'</Sort3Name>';
845 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EmpName>'||'<![CDATA[ '|| detrec.emp_name|| ' ]]>'||'</EmpName>';
846
847 /*Print header details for New Classification Group */
848 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassGroup>';
849 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassName>'||detrec.classification1||'</ClassName>';
850 end if; -- l_temp_count <> 0
851
852 l_hours_sum := l_hours_sum + nvl(detrec.run_hours1,0);
853 if detrec.class_seq1 <> 10 then
854 l_val_sum := l_val_sum + nvl(detrec.run_val1,0);
855 end if;
856 if detrec.class_seq1 in (8,10,9) then
857 l_count := l_count + nvl(detrec.tot_count1,0);
858 end if;
859 /* Calculate values for Sort Group Summary Totals*/
860 if detrec.class_seq1 = '1' and detrec.classification1 <> 'Non-payroll Payments' THEN
861 l_tot_gross_earn := l_tot_gross_earn + detrec.run_val1;
862 end if;
863 if detrec.class_seq1 = '1' and detrec.classification1 = 'Imputed Earnings' THEN
864 l_tot_imput_earn := l_tot_imput_earn + detrec.run_val1;
865 end if;
866 if detrec.class_seq1 = '1' and detrec.classification1 <> 'Imputed Earnings' and
867 detrec.classification1 <> 'Non-payroll Payments' THEN
868 l_tot_gross_pay := l_tot_gross_pay + detrec.run_val1;
869 end if;
870 if detrec.class_seq1 = '4' and detrec.classification1 = 'Pre-Tax Deductions' THEN
871 l_tot_pre_tax_ded := l_tot_pre_tax_ded + detrec.run_val1;
872 end if;
873 if detrec.class_seq1 = '1' and detrec.classification1 = 'Non-payroll Payments' THEN
874 l_tot_gross_non_pay := l_tot_gross_non_pay + detrec.run_val1;
875 end if;
876 if detrec.class_seq1 = '4' and detrec.classification1 = 'Voluntary Deductions' THEN
877 l_tot_vol_ded := l_tot_vol_ded + detrec.run_val1;
878 end if;
879 if detrec.class_seq1 = '4' and detrec.classification1 = 'Involuntary Deductions' THEN
880 l_tot_invol_ded := l_tot_invol_ded + detrec.run_val1;
881 end if;
882 if detrec.class_seq1 = '2' or detrec.class_seq1 = '3' THEN
883 l_tot_ee_tax := l_tot_ee_tax + detrec.run_val1;
884 end if;
885
886 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EleGroup>';
887 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<EleName>'||'<![CDATA[ '||detrec.element_name1|| ' ]]>'||'</EleName>';
888
889 if detrec.class_seq1 in ('8','10','9') then
890 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CouVal>'||detrec.tot_count1||'</CouVal>';
891 else
892 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CouVal>'||NULL||'</CouVal>';
893 end if;
894 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourVal>'||detrec.run_hours1||'</HourVal>';
895 if detrec.class_seq1 = '10' then
896 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<RunVal>'||NULL||'</RunVal>';
897 else
898 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<RunVal>'||detrec.run_val1||'</RunVal>';
899 end if;
900
901 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</EleGroup>';
902
903 /*To restrict the increase in the number of rows in vXMLTable.
904 This can also be done after say loop has run n times.
905 */
906 l_countloop := l_countloop + 1;
907
908 --hr_utility.trace (' l_countloop : ' || l_countloop );
909 --hr_utility.trace (' l_countlimit : ' || l_countlimit );
910
911 IF l_countloop >= l_countlimit THEN
912 l_countloop := 0;
913 vCtr := vCtr + 1;
914 vXMLTable(vCtr).xmlstring := '';
915 END IF;
916
917 end LOOP;
918
919 /* For totals by classification*/
920
921 if l_sort2_name is NULL and l_sort3_name is NULL THEN
922 l_tot_gross_earn_class := l_tot_gross_earn_class + l_tot_gross_earn;
923 l_tot_imput_earn_class := l_tot_imput_earn_class + l_tot_imput_earn;
924 l_tot_gross_pay_class := l_tot_gross_pay_class + l_tot_gross_pay;
925 l_tot_pre_tax_ded_class := l_tot_pre_tax_ded_class + l_tot_pre_tax_ded;
926 l_tot_gross_non_pay_class := l_tot_gross_non_pay_class + l_tot_gross_non_pay;
927 l_tot_vol_ded_class := l_tot_vol_ded_class + l_tot_vol_ded;
928 l_tot_invol_ded_class := l_tot_invol_ded_class + l_tot_invol_ded;
929 l_tot_ee_tax_class := l_tot_ee_tax_class + l_tot_ee_tax;
930 end if;
931
932 /* End For totals by classification*/
933 if l_temp_count <> 0 THEN
934 l_total_net := nvl(l_tot_gross_pay,0) +
935 nvl(l_tot_gross_non_pay,0) -
936 nvl(l_tot_ee_tax,0) -
937 nvl(l_tot_vol_ded,0) -
938 nvl(l_tot_pre_tax_ded,0) -
939 nvl(l_tot_invol_ded,0);
940
941 /* Fix to show summary details of last classification in the detailblock */
942
943 if l_class_seq not in (9,10) then
944 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotEleName>'||'Total '||l_class_name||'</TotEleName>';
945
946 ---
947 if l_hours_sum = 0 THEN
948 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||NULL||'</HourSubTot>';
949 else
950 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<HourSubTot>'||l_hours_sum||'</HourSubTot>';
951 end if;
952 ---
953 if l_val_sum = 0 THEN
954 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||NULL||'</ValSubTot>';
955 else
956 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ValSubTot>'||l_val_sum||'</ValSubTot>';
957 end if;
958 ---
959 if l_count = 0 THEN
960 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||NULL||'</CountSubTot>';
961 else
962 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<CountSubTot>'||l_count||'</CountSubTot>';
963 end if;
964 end if; -- l_class_seq not in (9,10)
965
966 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassGroup>';
967 ---
968 if l_tot_gross_earn <> 0 THEN
969 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
970 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Earnings :'||'</TotGrossName>';
971 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_earn||'</TotGrossVal>';
972 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
973 end if;
974 ---
975 if l_tot_imput_earn <> 0 THEN
976 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
977 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Imputed Earnings :'||'</TotGrossName>';
978 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_imput_earn||'</TotGrossVal>';
979 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
980 end if;
981 ---
982 if l_tot_gross_pay <> 0 THEN
983 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
984 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Pay :'||'</TotGrossName>';
985 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_pay||'</TotGrossVal>';
986 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
987 end if;
988 ---
989 if l_tot_pre_tax_ded <> 0 THEN
990 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
991 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Pre-Tax Deductions :'||'</TotGrossName>';
992 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_pre_tax_ded||'</TotGrossVal>';
993 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
994 end if;
995 ---
996 if l_tot_gross_non_pay <> 0 THEN
997 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
998 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Gross Non-Payroll Payments :'||'</TotGrossName>';
999 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_gross_non_pay||'</TotGrossVal>';
1000 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
1001 end if;
1002 ---
1003 if l_tot_vol_ded <> 0 THEN
1004 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
1005 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Voluntary :'||'</TotGrossName>';
1006 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_vol_ded||'</TotGrossVal>';
1007 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
1008 end if;
1009 ---
1010 if l_tot_invol_ded <> 0 THEN
1011 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
1012 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Involuntary :'||'</TotGrossName>';
1013 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_invol_ded||'</TotGrossVal>';
1014 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
1015 end if;
1016 ---
1017 if l_tot_ee_tax <> 0 THEN
1018 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
1019 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total EE Tax :'||'</TotGrossName>';
1020 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_tot_ee_tax||'</TotGrossVal>';
1021 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
1022 end if;
1023 ---
1024
1025 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumGroup>';
1026 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossName>'||'Total Net :'||'</TotGrossName>';
1027 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotGrossVal>'||l_total_net||'</TotGrossVal>';
1028 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumGroup>';
1029
1030
1031 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortGroup>';
1032 end if; --l_temp_count <> 0
1033 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</detailblock>';
1034
1035 hr_utility.trace ('Leaving '|| l_proc_name);
1036
1037 END WRITE_DETAIL_RECORDS;
1038
1039 /* This Procedure writes the details of Totals By Classification Region */
1040 PROCEDURE WRITE_CLASSIF_DETAILS
1041 IS
1042
1043 CURSOR c_class_details(cp_pact_id number)
1044 IS
1045 SELECT TO_NUMBER (attribute2) CLASS, TO_NUMBER (attribute3) sub_class,
1046 attribute4 classification_r, attribute5 element_name_r,
1047 SUM (value2) run_val_r, SUM (value3) run_hours_r, COUNT(*) run_tot_r
1048 FROM pay_us_rpt_totals
1049 WHERE tax_unit_id = cp_pact_id AND attribute1 <> 'GTN'
1050 GROUP BY TO_NUMBER (attribute2),
1051 TO_NUMBER (attribute3),
1052 attribute4,
1053 attribute5
1054 HAVING ( DECODE (SIGN (SUM (value2)),
1055 1, SUM (value2),
1056 -1, -1 * SUM (value2),
1057 0
1058 ) > 0
1059 OR DECODE (SIGN (SUM (value3)),
1060 1, SUM (value3),
1061 -1, -1 * SUM (value3),
1062 0
1063 ) > 0
1064 )
1065 ORDER BY 1,2,3,4;
1066
1067 l_temp_count NUMBER := 0;
1068 l_class_id NUMBER := 0;
1069 l_sub_class_id NUMBER := 0;
1070 l_class_total NUMBER := 0;
1071 l_class_hours NUMBER := 0;
1072 l_class_count NUMBER := 0;
1073 l_countloop NUMBER := 0;
1074 l_countlimit NUMBER := 50;
1075 l_class_name_temp VARCHAR2(240);
1076 l_proc_name VARCHAR2(100);
1077
1078 BEGIN
1079 l_proc_name := g_proc_name || 'WRITE_CLASSIF_DETAILS';
1080 hr_utility.trace ('Entering '|| l_proc_name);
1081
1082 vCtr := vCtr + 1;
1083 vXMLTable(vCtr).xmlstring := ' ';
1084 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<classblock>';
1085 l_temp_count := 0;
1086
1087 for jrec in c_class_details(l_pact_id)
1088 LOOP
1089 if l_temp_count <> 0 THEN --Not the first record
1090 if l_class_id = jrec.class and l_sub_class_id = jrec.sub_class THEN
1091 NULL;
1092 else
1093 if (l_class_id not in (8,9,10) or l_class_count = 0) then
1094 l_class_count := NULL;
1095 end if;
1096 --
1097 if l_class_total = 0 or l_class_id = 10 then
1098 l_class_total := NULL;
1099 end if;
1100 --
1101 if l_class_hours = 0 then
1102 l_class_hours := NULL;
1103 end if;
1104 --
1105 if l_class_total is not NULL or l_class_hours is not NULL or l_class_count is not NULL THEN
1106 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassByName>'||l_class_name_temp||'</ClassByName>';
1107 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByRun>'||l_class_total||'</ClassEleByRun>';
1108 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByHours>'||l_class_hours||'</ClassEleByHours>';
1109 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByCount>'||l_class_count||'</ClassEleByCount>';
1110 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumByClass>';
1111 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumByClass>';
1112 end if;
1113 --
1114 l_class_total :=0;
1115 l_class_hours :=0;
1116 l_class_count :=0;
1117 l_class_id := jrec.class;
1118 l_sub_class_id := jrec.sub_class;
1119 l_class_name_temp := jrec.classification_r;
1120
1121 end if;
1122 else
1123 l_class_id := jrec.class;
1124 l_sub_class_id := jrec.sub_class;
1125 l_class_name_temp := jrec.classification_r;
1126 l_temp_count := 1;
1127 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortSumByClass>';
1128 end if;
1129
1130 l_class_total := l_class_total + nvl(jrec.run_val_r,0);
1131 l_class_hours := l_class_hours + nvl(jrec.run_hours_r,0);
1132 l_class_count := l_class_count + nvl(jrec.run_tot_r,0);
1133
1134 if jrec.class in (2,6) then
1135 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<SortEleByClass>';
1136 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleName>'||'<![CDATA[ '|| jrec.element_name_r|| ' ]]>'||'</ClassEleName>';
1137 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleCnt>'||NULL||'</ClassEleCnt>';
1138 --
1139 if jrec.run_hours_r = 0 THEN
1140 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleHrs>'||NULL||'</ClassEleHrs>';
1141 else
1142 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleHrs>'||jrec.run_hours_r||'</ClassEleHrs>';
1143 end if;
1144 --
1145 if jrec.run_tot_r = 0 THEN
1146 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleRun>'||NULL||'</ClassEleRun>';
1147 else
1148 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleRun>'||jrec.run_val_r||'</ClassEleRun>';
1149 end if;
1150 --
1151 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortEleByClass>';
1152 end if;
1153
1154 /*To restrict the increase in the number of rows in vXMLTable. This is done after say loop has run n times.*/
1155
1156 l_countloop := l_countloop + 1;
1157
1158 IF l_countloop >= l_countlimit THEN
1159 l_countloop := 0;
1160 vCtr := vCtr + 1;
1161 vXMLTable(vCtr).xmlstring := ' ';
1162 END IF;
1163 end LOOP;
1164
1165 if l_temp_count <> 0 THEN
1166 if (l_class_id not in (8,9,10) or l_class_count = 0) then
1167 l_class_count := NULL;
1168 end if;
1169 --
1170 if l_class_total = 0 or l_class_id = 10 then
1171 l_class_total := NULL;
1172 end if;
1173 --
1174 if l_class_hours = 0 then
1175 l_class_hours := NULL;
1176 end if;
1177 --
1178 if l_class_total is not NULL or l_class_hours is not NULL or l_class_count is not NULL THEN
1179 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassByName>'||l_class_name_temp||'</ClassByName>';
1180 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByRun>'||l_class_total||'</ClassEleByRun>';
1181 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByHours>'||l_class_hours||'</ClassEleByHours>';
1182 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassEleByCount>'||l_class_count||'</ClassEleByCount>';
1183 end if;
1184 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</SortSumByClass>';
1185 l_total_net_class := nvl(l_tot_gross_pay_class,0) +
1186 nvl(l_tot_gross_non_pay_class,0) -
1187 nvl(l_tot_ee_tax_class,0) -
1188 nvl(l_tot_vol_ded_class,0) -
1189 nvl(l_tot_pre_tax_ded_class,0) -
1190 nvl(l_tot_invol_ded_class,0);
1191 if l_tot_gross_earn_class <> 0 THEN
1192 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1193 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Gross Earnings :'||'</TotClassSumName>';
1194 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_gross_earn_class||'</TotClassSumVal>';
1195 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1196 end if;
1197 --
1198 if l_tot_imput_earn_class <> 0 THEN
1199 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1200 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Imputed Earnings :'||'</TotClassSumName>';
1201 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_imput_earn_class||'</TotClassSumVal>';
1202 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1203 end if;
1204 --
1205 if l_tot_gross_pay_class <> 0 THEN
1206 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1207 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Gross Pay :'||'</TotClassSumName>';
1208 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_gross_pay_class||'</TotClassSumVal>';
1209 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1210 end if;
1211 --
1212 if l_tot_pre_tax_ded_class <> 0 THEN
1213 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1214 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Pre-Tax Deductions :'||'</TotClassSumName>';
1215 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_pre_tax_ded_class||'</TotClassSumVal>';
1216 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1217 end if;
1218 --
1219 if l_tot_gross_non_pay_class <> 0 THEN
1220 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1221 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Gross Non-Payroll Payments :'||'</TotClassSumName>';
1222 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_gross_non_pay_class||'</TotClassSumVal>';
1223 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1224 end if;
1225 --
1226 if l_tot_vol_ded_class <> 0 THEN
1227 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1228 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Voluntary :'||'</TotClassSumName>';
1229 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_vol_ded_class||'</TotClassSumVal>';
1230 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1231 end if;
1232 --
1233 if l_tot_invol_ded_class <> 0 THEN
1234 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1235 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Involuntary :'||'</TotClassSumName>';
1236 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_invol_ded_class||'</TotClassSumVal>';
1237 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1238 end if;
1239 --
1240 if l_tot_ee_tax_class <> 0 THEN
1241 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1242 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total EE Tax :'||'</TotClassSumName>';
1243 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_tot_ee_tax_class||'</TotClassSumVal>';
1244 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1245 end if;
1246 --
1247
1248 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<ClassSumGroup>';
1249 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumName>'||'Total Net :'||'</TotClassSumName>';
1250 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<TotClassSumVal>'||l_total_net_class||'</TotClassSumVal>';
1251 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</ClassSumGroup>';
1252 end if;
1253
1254 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</classblock>';
1255
1256 hr_utility.trace ('Leaving '|| l_proc_name);
1257
1258 END WRITE_CLASSIF_DETAILS;
1259
1260 /****************************************************************
1261 ** This Procedure writes details of Unprocessed Pre-Payments
1262 ** and Unpaid Payments
1263 ****************************************************************/
1264 PROCEDURE WRITE_UNPAY_DETAILS
1265 (
1266 p_xfdf_blob OUT NOCOPY BLOB
1267 ) IS
1268 CURSOR c_unpay_details(cp_pact_id NUMBER,
1269 cp_sort1 VARCHAR2,
1270 cp_sort2 VARCHAR2,
1271 cp_sort3 VARCHAR2) IS
1272 SELECT to_number(attribute3) unpaid_sub_class1,
1273 decode(lower(cp_sort1),
1274 'loc','Location Name : '||location_name,
1275 'gre','GRE Name : '||gre_name ,
1276 'org','Organization Name : '||organization_name,
1277 'GRE Name : '||gre_name) unpaid_sort1_name,
1278 decode(lower(cp_sort2),
1279 'loc','Location Name : '||location_name,
1280 'gre','GRE Name : '||gre_name ,
1281 'org','Organization Name : '||organization_name,
1282 null) unpaid_sort2_name,
1283 decode(lower(cp_sort3),
1284 'loc','Location Name : '||location_name,
1285 'gre','GRE Name : '||gre_name ,
1286 'org','Organization Name : '||organization_name,
1287 null) unpaid_sort3_name,
1288 to_number(attribute2) unpaid_class_seq1,
1289 attribute4 unpaid_classification1,
1290 attribute6 full_name,
1291 attribute7 asg_no,
1292 attribute8 pymt_method_name,
1293 attribute9 account_type,
1294 attribute10 account_number,
1295 attribute11 routing_number,
1296 organization_id aaid,
1297 location_id pre_pay_id
1298 FROM pay_us_rpt_totals
1299 WHERE tax_unit_id = cp_pact_id
1300 AND attribute2 = '10'
1301 AND attribute3 in ('1','2') --Unprocessed/Unpaid Payments
1302 AND organization_id is not null
1303 AND attribute1 = 'MESG-LINE'
1304 ORDER BY 1,2,3,4,5,6,7;
1305
1306 l_temp_count NUMBER := 0;
1307 l_unpaid_class NUMBER := 0;
1308 l_countloop NUMBER := 0;
1309 l_countlimit NUMBER := 50;
1310 l_unpaid_sort1 VARCHAR2(240);
1311 l_unpaid_sort2 VARCHAR2(240);
1312 l_unpaid_sort3 VARCHAR2(240);
1313 l_unpaid_col1 VARCHAR2(30);
1314 l_unpaid_col2 VARCHAR2(30);
1315 l_unpaid_col3 VARCHAR2(30);
1316 l_unpaid_col4 VARCHAR2(30);
1317 l_unpaid_col5 VARCHAR2(30);
1318 l_unpaid_col6 VARCHAR2(30);
1319 l_unpaid_col7 VARCHAR2(30);
1320 l_proc_name VARCHAR2(100);
1321
1322 BEGIN
1323 l_proc_name := g_proc_name || 'WRITE_UNPAY_DETAILS';
1324 hr_utility.trace ('Entering '|| l_proc_name);
1325
1326 vCtr := vCtr + 1;
1327 vXMLTable(vCtr).xmlstring := '';
1328 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<unpayblock>';
1329
1330 l_temp_count := 0;
1331
1332 for krec in c_unpay_details(l_pact_id ,
1333 l_sort1,
1334 l_sort2,
1335 l_sort3)
1336 loop
1337
1338 -- bug 5862861 :
1339 if length(vXMLTable(vCtr).xmlstring) > 30000 then
1340 vCtr := vCtr + 1;
1341 vXMLTable(vCtr).xmlstring := '';
1342 end if;
1343
1344 --hr_utility.trace (' l_temp_count : ' || l_temp_count );
1345 if l_temp_count <> 0 THEN
1346 --not the first record
1347 --hr_utility.trace (' l_temp_count <> 0 ' );
1348 --hr_utility.trace (' l_unpaid_class : ' || l_unpaid_class ||' , '||' krec.unpaid_sub_class1 : ' || krec.unpaid_sub_class1 );
1349
1350 if l_unpaid_class = krec.unpaid_sub_class1 THEN
1351 /*
1352 hr_utility.trace (' l_unpaid_sort1 : ' || l_unpaid_sort1 ||' , '||' krec.unpaid_sort1_name : ' || krec.unpaid_sort1_name );
1353 hr_utility.trace (' l_unpaid_sort2 : ' || l_unpaid_sort2 ||' , '||' krec.unpaid_sort2_name : ' || krec.unpaid_sort2_name );
1354 hr_utility.trace (' l_unpaid_sort3 : ' || l_unpaid_sort3 ||' , '||' krec.unpaid_sort3_name : ' || krec.unpaid_sort3_name );
1355 */
1356 if ((l_unpaid_sort1 = krec.unpaid_sort1_name) or
1357 (l_unpaid_sort1 is NULL and krec.unpaid_sort1_name is NULL)) and
1358 ((l_unpaid_sort2 = krec.unpaid_sort2_name) or
1359 (l_unpaid_sort2 is NULL and krec.unpaid_sort2_name is NULL)) and
1360 ((l_unpaid_sort3 = krec.unpaid_sort3_name) or
1361 (l_unpaid_sort3 is NULL and krec.unpaid_sort3_name is NULL)) THEN
1362 NULL;
1363 else --new sort by
1364 hr_utility.trace (' in else of sort ');
1365
1366 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1367 || '</UnpaySort>';
1368 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1369 || '<UnpaySort>';
1370 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1371 || '<UnpaidSort1>'
1372 || 'Detail for :'
1373 || '<![CDATA[ '|| krec.unpaid_sort1_name || ' ]]>'
1374 || '</UnpaidSort1>';
1375 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1376 || '<UnpaidSort2>'
1377 || '<![CDATA[ '|| krec.unpaid_sort2_name || ' ]]>'
1378 || '</UnpaidSort2>';
1379 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1380 || '<UnpaidSort3>'
1381 || '<![CDATA[ '|| krec.unpaid_sort3_name || ' ]]>'
1382 || '</UnpaidSort3>';
1383 l_unpaid_sort1 := krec.unpaid_sort1_name;
1384 l_unpaid_sort2 := krec.unpaid_sort2_name;
1385 l_unpaid_sort3 := krec.unpaid_sort3_name;
1386 /*
1387 hr_utility.trace (' l_unpaid_sort1 : ' || l_unpaid_sort1 );
1388 hr_utility.trace (' l_unpaid_sort2 : ' || l_unpaid_sort2 );
1389 hr_utility.trace (' l_unpaid_sort3 : ' || l_unpaid_sort3 );
1390 */
1391 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1392 || '<UnpaidCol1>'
1393 || l_unpaid_col1||'</UnpaidCol1>';
1394 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1395 || '<UnpaidCol2>'
1396 || l_unpaid_col2||'</UnpaidCol2>';
1397 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1398 || '<UnpaidCol3>'
1399 || l_unpaid_col3||'</UnpaidCol3>';
1400 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1401 || '<UnpaidCol4>'
1402 || l_unpaid_col4||'</UnpaidCol4>';
1403 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1404 || '<UnpaidCol5>'
1405 || l_unpaid_col5||'</UnpaidCol5>';
1406 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1407 || '<UnpaidCol6>'
1408 || l_unpaid_col6||'</UnpaidCol6>';
1409 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1410 || '<UnpaidCol7>'
1411 || l_unpaid_col7||'</UnpaidCol7>';
1412
1413
1414 end if; --new sort by
1415 else --new classification
1416
1417 hr_utility.trace (' in else of new classification ');
1418
1419
1420 l_unpaid_class := krec.unpaid_sub_class1;
1421
1422 --hr_utility.trace (' l_unpaid_class : ' || l_unpaid_class );
1423
1424 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1425 || '</UnpaySort>';
1426 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1427 || '</UnpayClass>';
1428 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1429 || '<UnpayClass>';
1430 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1431 || '<UnpaidClassName>'
1432 || 'Detail of :'
1433 || krec.unpaid_classification1
1434 || '</UnpaidClassName>';
1435
1436 if l_unpaid_class = 1 then
1437 l_unpaid_col1 := 'Employee Name';
1438 l_unpaid_col2 := 'Assign No.';
1439 l_unpaid_col3 := 'Asg Action ID';
1440 l_unpaid_col4 := NULL;
1441 l_unpaid_col5 := NULL;
1442 l_unpaid_col6 := NULL;
1443 l_unpaid_col7 := NULL;
1444 end if;
1445
1446 if l_unpaid_class = 2 then
1447 l_unpaid_col1 := 'Employee Name';
1448 l_unpaid_col2 := 'Assign No.';
1449 l_unpaid_col3 := 'Pre-Pymnt ID';
1450 l_unpaid_col4 := 'Pymnt Method';
1451 l_unpaid_col5 := 'Account Type';
1452 l_unpaid_col6 := 'Account No.';
1453 l_unpaid_col7 := 'Routing No.';
1454 end if;
1455 /*
1456 hr_utility.trace (' l_unpaid_col1 : ' || l_unpaid_col1 );
1457 hr_utility.trace (' l_unpaid_col2 : ' || l_unpaid_col2 );
1458 hr_utility.trace (' l_unpaid_col3 : ' || l_unpaid_col3 );
1459 hr_utility.trace (' l_unpaid_col4 : ' || l_unpaid_col4 );
1460 hr_utility.trace (' l_unpaid_col5 : ' || l_unpaid_col5 );
1461 hr_utility.trace (' l_unpaid_col6 : ' || l_unpaid_col6 );
1462 hr_utility.trace (' l_unpaid_col7 : ' || l_unpaid_col7 );
1463 */
1464
1465 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<UnpaySort>';
1466 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1467 || '<UnpaidSort1>'
1468 ||'Detail for :'
1469 ||'<![CDATA[ '|| krec.unpaid_sort1_name || ' ]]>'
1470 ||'</UnpaidSort1>';
1471 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1472 || '<UnpaidSort2>'
1473 ||'<![CDATA[ '|| krec.unpaid_sort2_name || ' ]]>'
1474 ||'</UnpaidSort2>';
1475 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1476 || '<UnpaidSort3>'
1477 ||'<![CDATA[ '|| krec.unpaid_sort3_name || ' ]]>'
1478 ||'</UnpaidSort3>';
1479
1480 l_unpaid_sort1 := krec.unpaid_sort1_name;
1481 l_unpaid_sort2 := krec.unpaid_sort2_name;
1482 l_unpaid_sort3 := krec.unpaid_sort3_name;
1483 /*
1484 hr_utility.trace (' l_unpaid_sort1 : ' || l_unpaid_sort1 );
1485 hr_utility.trace (' l_unpaid_sort2 : ' || l_unpaid_sort2 );
1486 hr_utility.trace (' l_unpaid_sort3 : ' || l_unpaid_sort3 );
1487 */
1488
1489 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1490 || '<UnpaidCol1>'
1491 ||l_unpaid_col1
1492 ||'</UnpaidCol1>';
1493 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1494 || '<UnpaidCol2>'
1495 ||l_unpaid_col2
1496 ||'</UnpaidCol2>';
1497 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1498 || '<UnpaidCol3>'
1499 ||l_unpaid_col3
1500 ||'</UnpaidCol3>';
1501 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1502 || '<UnpaidCol4>'
1503 ||l_unpaid_col4
1504 ||'</UnpaidCol4>';
1505 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1506 || '<UnpaidCol5>'
1507 ||l_unpaid_col5
1508 ||'</UnpaidCol5>';
1509 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1510 || '<UnpaidCol6>'
1511 ||l_unpaid_col6
1512 ||'</UnpaidCol6>';
1513 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1514 || '<UnpaidCol7>'
1515 ||l_unpaid_col7
1516 ||'</UnpaidCol7>';
1517
1518 end if; -- new classification
1519 else --new record
1520
1521 hr_utility.trace (' in else of new record ' );
1522 l_temp_count := 1;
1523
1524 l_unpaid_class := krec.unpaid_sub_class1;
1525
1526 --hr_utility.trace (' l_unpaid_class : ' || l_unpaid_class );
1527
1528 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1529 || '<UnpayClass>';
1530 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1531 || '<UnpaidClassName>'
1532 ||'Detail of :'
1533 ||krec.unpaid_classification1
1534 ||'</UnpaidClassName>';
1535
1536 if l_unpaid_class = 1 then
1537 l_unpaid_col1 := 'Employee Name';
1538 l_unpaid_col2 := 'Assign No.';
1539 l_unpaid_col3 := 'Asg Action ID';
1540 l_unpaid_col4 := NULL;
1541 l_unpaid_col5 := NULL;
1542 l_unpaid_col6 := NULL;
1543 l_unpaid_col7 := NULL;
1544 end if;
1545
1546 if l_unpaid_class = 2 then
1547 l_unpaid_col1 := 'Employee Name';
1548 l_unpaid_col2 := 'Assign No.';
1549 l_unpaid_col3 := 'Pre-Pymnt ID';
1550 l_unpaid_col4 := 'Pymnt Method';
1551 l_unpaid_col5 := 'Account Type';
1552 l_unpaid_col6 := 'Account No.';
1553 l_unpaid_col7 := 'Routing No.';
1554 end if;
1555 /*
1556 hr_utility.trace (' l_unpaid_col1 : ' || l_unpaid_col1 );
1557 hr_utility.trace (' l_unpaid_col2 : ' || l_unpaid_col2 );
1558 hr_utility.trace (' l_unpaid_col3 : ' || l_unpaid_col3 );
1559 hr_utility.trace (' l_unpaid_col4 : ' || l_unpaid_col4 );
1560 hr_utility.trace (' l_unpaid_col5 : ' || l_unpaid_col5 );
1561 hr_utility.trace (' l_unpaid_col6 : ' || l_unpaid_col6 );
1562 hr_utility.trace (' l_unpaid_col7 : ' || l_unpaid_col7 );
1563 */
1564
1565 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<UnpaySort>';
1566 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1567 || '<UnpaidSort1>'
1568 ||'Detail for :'
1569 ||'<![CDATA[ '|| krec.unpaid_sort1_name || ' ]]>'
1570 ||'</UnpaidSort1>';
1571 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1572 || '<UnpaidSort2>'
1573 ||'<![CDATA[ '|| krec.unpaid_sort2_name || ' ]]>'
1574 ||'</UnpaidSort2>';
1575 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1576 || '<UnpaidSort3>'
1577 ||'<![CDATA[ '|| krec.unpaid_sort3_name || ' ]]>'
1578 ||'</UnpaidSort3>';
1579
1580 l_unpaid_sort1 := krec.unpaid_sort1_name;
1581 l_unpaid_sort2 := krec.unpaid_sort2_name;
1582 l_unpaid_sort3 := krec.unpaid_sort3_name;
1583 /*
1584 hr_utility.trace (' l_unpaid_sort1 : ' || l_unpaid_sort1 );
1585 hr_utility.trace (' l_unpaid_sort2 : ' || l_unpaid_sort2 );
1586 hr_utility.trace (' l_unpaid_sort3 : ' || l_unpaid_sort3 );
1587 */
1588
1589 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1590 || '<UnpaidCol1>'
1591 ||l_unpaid_col1
1592 ||'</UnpaidCol1>';
1593 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1594 || '<UnpaidCol2>'
1595 ||l_unpaid_col2||'</UnpaidCol2>';
1596 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1597 || '<UnpaidCol3>'
1598 ||l_unpaid_col3||'</UnpaidCol3>';
1599 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1600 || '<UnpaidCol4>'
1601 ||l_unpaid_col4||'</UnpaidCol4>';
1602 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1603 || '<UnpaidCol5>'
1604 ||l_unpaid_col5||'</UnpaidCol5>';
1605 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1606 || '<UnpaidCol6>'
1607 ||l_unpaid_col6||'</UnpaidCol6>';
1608 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1609 || '<UnpaidCol7>'
1610 ||l_unpaid_col7||'</UnpaidCol7>';
1611
1612 end if; --new record
1613
1614 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '<UnpayEmp>';
1615
1616 if l_unpaid_class = 1 then
1617
1618 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1619 || '<UnpaidCol1Val>'
1620 ||'<![CDATA[ '|| krec.full_name|| ' ]]>'
1621 ||'</UnpaidCol1Val>';
1622 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1623 || '<UnpaidCol2Val>'
1624 ||krec.asg_no||'</UnpaidCol2Val>';
1625 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1626 || '<UnpaidCol3Val>'
1627 ||krec.aaid||'</UnpaidCol3Val>';
1628 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1629 || '<UnpaidCol4Val>'
1630 ||NULL
1631 ||'</UnpaidCol4Val>';
1632 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1633 || '<UnpaidCol5Val>'
1634 ||NULL||'</UnpaidCol5Val>';
1635 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1636 || '<UnpaidCol6Val>'||NULL
1637 ||'</UnpaidCol6Val>';
1638 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1639 || '<UnpaidCol7Val>'||NULL
1640 ||'</UnpaidCol7Val>';
1641
1642 else
1643
1644 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1645 || '<UnpaidCol1Val>'
1646 ||'<![CDATA[ '|| krec.full_name|| ' ]]>'
1647 ||'</UnpaidCol1Val>';
1648 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1649 || '<UnpaidCol2Val>'
1650 ||krec.asg_no
1651 ||'</UnpaidCol2Val>';
1652 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1653 || '<UnpaidCol3Val>'
1654 ||krec.pre_pay_id||'</UnpaidCol3Val>';
1655 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1656 || '<UnpaidCol4Val>'
1657 ||'<![CDATA[ '|| krec.pymt_method_name|| ' ]]>'
1658 ||'</UnpaidCol4Val>';
1659 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1660 || '<UnpaidCol5Val>'
1661 ||krec.account_type
1662 ||'</UnpaidCol5Val>';
1663 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1664 || '<UnpaidCol6Val>'
1665 ||krec.account_number
1666 ||'</UnpaidCol6Val>';
1667 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring
1668 || '<UnpaidCol7Val>'
1669 ||krec.routing_number
1670 ||'</UnpaidCol7Val>';
1671
1672 end if;
1673 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</UnpayEmp>';
1674 -- vCtr := vCtr + 1;
1675 /* commented
1676 if vCtr >= 1000 then
1677 WRITE_TO_CLOB(p_xfdf_blob);
1678 vCtr := 0;
1679 vXMLTable.DELETE;
1680 end if;*/
1681 --vXMLTable(vCtr).xmlstring := ' ';
1682
1683 /*To restrict the increase in the number of rows in vXMLTable.
1684 This can also be done after say loop has run n times.
1685 */
1686
1687 --hr_utility.trace (' l_countloop : ' || l_countloop );
1688 --hr_utility.trace (' l_countlimit : ' || l_countlimit );
1689
1690 l_countloop := l_countloop + 1;
1691 IF l_countloop >= l_countlimit THEN
1692 l_countloop := 0;
1693 vCtr := vCtr + 1;
1694 vXMLTable(vCtr).xmlstring := ' ';
1695 END IF;
1696
1697 end loop;
1698 hr_utility.trace (' out of loop l_temp_count : ' || l_temp_count );
1699
1700 if l_temp_count <> 0 THEN
1701 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</UnpaySort>';
1702 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</UnpayClass>';
1703 end if;
1704
1705 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</unpayblock>';
1706 vXMLTable(vCtr).xmlstring := vXMLTable(vCtr).xmlstring || '</start>';
1707
1708 hr_utility.trace ('Leaving '|| l_proc_name);
1709
1710 END WRITE_UNPAY_DETAILS;
1711
1712 /****************************************************************************
1713 Name : POPULATE_GTN_REPORT_DATA
1714 Description : Main procedure which returns the generated XML
1715 *****************************************************************************/
1716 PROCEDURE POPULATE_GTN_REPORT_DATA
1717 (
1718 p_ppa_finder IN NUMBER
1719 ,p_xfdf_blob OUT NOCOPY BLOB
1720 ) IS
1721
1722 l_proc_name VARCHAR2(100);
1723 l_err_num number;
1724 l_err_msg varchar2(300);
1725 BEGIN
1726 l_proc_name := g_proc_name || 'POPULATE_GTN_REPORT_DATA';
1727 hr_utility.trace ('Entering '|| l_proc_name);
1728
1729 hr_utility.trace (' p_ppa_finder : '|| p_ppa_finder );
1730
1731 get_parameters(p_ppa_finder);
1732 write_header;
1733 write_detail_records(p_xfdf_blob);
1734 write_classif_details;
1735 write_unpay_details(p_xfdf_blob);
1736 write_to_clob(p_xfdf_blob);
1737
1738 BEGIN
1739 /*
1740 DELETE FROM pay_us_rpt_totals
1741 WHERE tax_unit_id = l_pact_id;*/
1742 /*Removing the commit as this procedure is now getting
1743 called from pypaygtn.pkb
1744 */
1745 --COMMIT;
1746 null;
1747 EXCEPTION
1748 WHEN OTHERS THEN
1749 l_err_num := SQLCODE;
1750 l_err_msg := substr(SQLERRM , 1 , 300 );
1751
1752 HR_UTILITY.TRACE('Inside Exception WHEN OTHERS in Procedure' || l_proc_name);
1753 HR_UTILITY.TRACE('l_err_num : ' || l_err_num );
1754 HR_UTILITY.TRACE('l_err_msg : ' || l_err_msg );
1755
1756 END;
1757
1758 hr_utility.trace ('Leaving '|| l_proc_name);
1759
1760 END POPULATE_GTN_REPORT_DATA;
1761
1762 /****************************************************************************
1763 Name : WRITE_TO_CLOB
1764 Description : Procedure to put the data in a clob
1765 *****************************************************************************/
1766 PROCEDURE WRITE_TO_CLOB
1767 (
1768 p_xfdf_blob OUT NOCOPY BLOB
1769 ) IS
1770
1771 l_xfdf_string CLOB;
1772 l_proc_name VARCHAR2(100);
1773
1774 BEGIN
1775 l_proc_name := g_proc_name || 'WRITE_TO_CLOB';
1776 hr_utility.trace ('Entering '|| l_proc_name);
1777
1778 DBMS_LOB.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1779 DBMS_LOB.open(l_xfdf_string,dbms_lob.lob_readwrite);
1780
1781 IF vXMLTable.count > 0 THEN
1782 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
1783 dbms_lob.writeAppend(l_xfdf_string,
1784 LENGTH(vXMLTable(ctr_table).xmlstring),
1785 vXMLTable(ctr_table).xmlstring );
1786 END LOOP;
1787 END IF;
1788
1789 DBMS_LOB.createtemporary(p_xfdf_blob,TRUE);
1790 clob_to_blob(l_xfdf_string,p_xfdf_blob);
1791
1792 hr_utility.trace ('Leaving '|| l_proc_name);
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 HR_UTILITY.TRACE('Inside Exception WHEN OTHERS of Procedure' || l_proc_name);
1796 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1797 HR_UTILITY.RAISE_ERROR;
1798 END WRITE_TO_CLOB;
1799
1800 /****************************************************************************
1801 Name : CLOB_TO_BLOB
1802 Description : Procedure to convert a clob value to a blob value
1803 *****************************************************************************/
1804 PROCEDURE CLOB_TO_BLOB
1805 (
1806 p_clob IN CLOB
1807 ,p_blob IN OUT NOCOPY BLOB
1808 ) IS
1809 l_raw_buffer RAW(32000);
1810 l_blob BLOB;
1811 l_offset INTEGER;
1812 l_length_clob NUMBER;
1813 l_buffer_len NUMBER := 16000; /* 7182157 */
1814 l_chunk_len NUMBER;
1815 l_varchar_buffer VARCHAR2(32000);
1816 l_proc_name VARCHAR2(100);
1817 l_raw_length NUMBER; /* 7182157 */
1818
1819 BEGIN
1820 l_proc_name := g_proc_name || 'CLOB_TO_BLOB';
1821 hr_utility.trace ('Entering '|| l_proc_name);
1822
1823 l_length_clob := dbms_lob.getlength(p_clob);
1824 l_offset := 1;
1825
1826 WHILE l_length_clob > 0
1827 LOOP
1828 hr_utility.trace('l_length_clob '|| l_length_clob);
1829
1830 IF l_length_clob < l_buffer_len THEN
1831 l_chunk_len := l_length_clob;
1832 ELSE
1833 l_chunk_len := l_buffer_len;
1834 END IF;
1835
1836 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
1837 l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
1838 l_raw_length := utl_raw.length(l_raw_buffer); /* 7182157 */
1839 /*Commented the following trace sentence
1840 This used to create issues when we were asking
1841 traces from the customers
1842 */
1843 --hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
1844 -- DBMS_LOB.writeappend(p_blob,l_chunk_len,l_raw_buffer); /* 7182157 */
1845 DBMS_LOB.writeappend(p_blob,l_raw_length,l_raw_buffer); /* 7182157 */
1846 l_offset := l_offset + l_chunk_len;
1847 l_length_clob := l_length_clob - l_chunk_len;
1848
1849 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
1850 END LOOP;
1851
1852 hr_utility.trace ('Leaving '|| l_proc_name);
1853 END CLOB_TO_BLOB;
1854
1855 BEGIN
1856 -- hr_utility.trace_on(NULL,'trc_payusxml');
1857 g_proc_name := 'PAY_US_XDO_REPORT.';
1858
1859 END PAY_US_XDO_REPORT;