[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_FORMAT_2D
Source
1 PACKAGE BODY PAY_MX_FORMAT_2D AS
2 /* $Header: paymxformat2d.pkb 120.0.12000000.1 2007/02/22 16:25:13 vmehta noship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : pay_mx_format_2d
9 Package File Name : paymxformat2d.pkb
10
11 Description : Used for FORMAT2D Interface Extract
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ----------------------------------
18 nragavar 09-Nov-2006 115.0 Initial Version
19 vpandya 17-Nov-2006 115.1 Changed c_min_wage cursor.
20 vpandya 28-Nov-2006 115.2 5685714 Changed cursor c_format2d_rec for
21 TOTAL_DAYS_WORKED
22 vpandya 05-Dec-2006 115.3 5699267 Changed generate_xml_footer:
23 Removed condition to print null if
24 value is zero. Now it will print
25 zero.
26 vpandya 05-Dec-2006 115.4 5699267 Changed generate_xml_footer:
27 Added NVL to all columns to print
28 0 if level is zero.
29 vpandya 10-Dec-2006 115.5 5704405 Changed action_creation to change
30 order by clause so that asg act
31 getting created appropriately.
32 Changed generate_xml: assigning
33 person id to ln_person_id.
34 ==========================================================================*/
35
36 --
37 -- Global Variables
38 --
39
40
41 format2d_xml_tbl xml_tbl;
42
43 g_proc_name VARCHAR2(240);
44 g_debug BOOLEAN;
45 g_document_type VARCHAR2(50);
46 gd_effective_date DATE;
47 gn_business_group_id NUMBER;
48 gn_legal_er_id NUMBER;
49
50 gn_success_fail NUMBER;
51 gn_sep_bal NUMBER;
52 gn_ass_bal NUMBER;
53 gn_emp_bal NUMBER;
54
55 EOL VARCHAR2(5);
56
57 /****************************************************************************
58 Name : HR_UTILITY_TRACE
59 Description : This procedure prints debug messages.
60 *****************************************************************************/
61 PROCEDURE hr_utility_trace ( P_TRC_DATA VARCHAR2) AS
62 BEGIN
63 IF g_debug THEN
64 hr_utility.trace(p_trc_data);
65 END IF;
66 END hr_utility_trace;
67
68
69 /****************************************************************************
70 Name : PRINT_BLOB
71 Description : This procedure prints contents of BLOB passed as parameter.
72 *****************************************************************************/
73
74 PROCEDURE print_blob(p_blob BLOB) IS
75 BEGIN
76 IF g_debug THEN
77 pay_ac_utility.print_lob(p_blob);
78 END IF;
79 END print_blob;
80
81
82 /****************************************************************************
83 Name : WRITE_TO_MAGTAPE_LOB
84 Description : This procedure appends passed BLOB parameter to
85 pay_mag_tape.g_blob_value
86 *****************************************************************************/
87
88 PROCEDURE write_to_magtape_lob(p_blob BLOB) IS
89 BEGIN
90 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
91 pay_core_files.write_to_magtape_lob (p_blob);
92 END IF;
93 END write_to_magtape_lob;
94
95
96 /****************************************************************************
97 Name : WRITE_TO_MAGTAPE_LOB
98 Description : This procedure appends passed varchar2 parameter to
99 pay_mag_tape.g_blob_value
100 *****************************************************************************/
101
102 PROCEDURE write_to_magtape_lob(p_data VARCHAR2) IS
103 BEGIN
104 pay_core_files.write_to_magtape_lob (p_data);
105 END write_to_magtape_lob;
106
107
108 /****************************************************************************
109 Name : POPULATE_XML_TABLE
110 Description : This procedure creates a table that uses for XML creation.
111 *****************************************************************************/
112 PROCEDURE populate_xml_table( name IN VARCHAR2
113 ,value IN VARCHAR2
114 ,type IN VARCHAR2 ) IS
115 ln_index NUMBER;
116
117 BEGIN
118 /*
119 IF type = 'SEP_BAL' THEN
120
121 IF value < 0 THEN
122 gn_success_fail := -1;
123 ELSIF value > 0 THEN
124 gn_sep_bal := 1;
125 END IF;
126
127 ELSIF type = 'ASS_BAL' THEN
128
129 IF value < 0 THEN
130 gn_success_fail := -1;
131 ELSIF value > 0 THEN
132 gn_ass_bal := 1;
133 END IF;
134
135 ELSIF type = 'EMP_BAL' THEN
136
137 IF value < 0 THEN
138 gn_success_fail := -1;
139 ELSIF value > 0 THEN
140 gn_emp_bal := 1;
141 END IF;
142
143 ELSIF type = 'SUMM_BAL' THEN
144
145 IF value < 0 THEN
146 gn_success_fail := -1;
147 END IF;
148
149 END IF;
150 */
151 ln_index := format2d_xml_tbl.COUNT;
152
153 format2d_xml_tbl(ln_index).name := name;
154 format2d_xml_tbl(ln_index).value := value;
155
156 END populate_xml_table;
157
158 /****************************************************************************
159 Name : LOAD_XML_INTERNAL
160 Description : This procedure loads the global XML cache.
161 Parameters : P_NODE_TYPE This parameter can take one of these
162 values: -
163 1. CS - This signifies that string contained
164 in P_NODE parameter is start of
165 container node. P_DATA parameter is
166 ignored in this mode.
167 2. CE - This signifies that string
168 contained in P_NODE parameter is
169 end of container node. P_DATA
170 parameter is ignored in this mode.
171 3. D - This signifies that string
172 contained in P_NODE parameter is
173 data node and P_DATA carries actual
174 data to be contained by tag
175 specified by P_NODE parameter.
176
177 P_NODE Name of XML tag, or, application column
178 name of flex segment.
179
180 P_DATA Data to be contained by tag specified by
181 P_NODE parameter. P_DATA is not used unless
182 P_NODE_TYPE = D.
183 *****************************************************************************/
184 PROCEDURE load_xml_internal ( p_node_type VARCHAR2
185 ,p_node VARCHAR2
186 ,p_data VARCHAR2) IS
187 l_proc_name VARCHAR2(100);
188 l_data VARCHAR2(240);
189 l_xml VARCHAR2(240);
190
191 BEGIN
192 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
193 hr_utility_trace ('Entering '||l_proc_name);
194
195 IF p_node_type = 'CS' THEN
196
197 l_xml := '<'||p_node||'>'||EOL;
198
199 ELSIF p_node_type = 'CE' THEN
200
201 l_xml := '</'||p_node||'>'||EOL;
202
203 ELSIF p_node_type = 'D' THEN
204
205 /* Handle special charaters in data */
206 l_data := REPLACE (p_data, '&', '&');
207 l_data := REPLACE (l_data, '>', '>');
208 l_data := REPLACE (l_data, '<', '<');
209 l_data := REPLACE (l_data, '''', ''');
210 l_data := REPLACE (l_data, '"', '"');
211 l_xml := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
212
213 END IF;
214
215 write_to_magtape_lob (l_xml);
216
217 hr_utility_trace ('Leaving '||l_proc_name);
218
219 END load_xml_internal;
220
221 /****************************************************************************
222 Name : GET_PAYROLL_ACTION_INFO
223 Description : This procedure fetches payroll action level information.
224 *****************************************************************************/
225 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
226 ,p_end_date OUT NOCOPY DATE
227 ,p_business_group_id OUT NOCOPY NUMBER
228 ,p_legal_employer_id OUT NOCOPY NUMBER
229 )
230 IS
231 CURSOR c_payroll_Action_info (cp_payroll_action_id IN NUMBER) IS
232 SELECT effective_date,
233 business_group_id,
234 pay_mx_utility.get_legi_param_val( 'LEGAL_EMPLOYER'
235 ,legislative_parameters)
236 FROM pay_payroll_actions
237 WHERE payroll_action_id = cp_payroll_action_id;
238
239 ld_end_date DATE;
240 ln_business_group_id NUMBER;
241 ln_asg_set_id NUMBER;
242 ln_legal_er_id NUMBER;
243 lv_procedure_name VARCHAR2(100);
244
245 lv_error_message VARCHAR2(200);
246 ln_step NUMBER;
247
248 BEGIN
249
250 lv_procedure_name := g_proc_name ||'.get_payroll_action_info';
251
252 hr_utility.set_location(lv_procedure_name, 10);
253
254 ln_step := 1;
255
256 OPEN c_payroll_action_info(p_payroll_action_id);
257 FETCH c_payroll_action_info INTO ld_end_date
258 ,ln_business_group_id
259 ,ln_legal_er_id;
260 CLOSE c_payroll_action_info;
261
262 ln_step := 2;
263 hr_utility.set_location(lv_procedure_name, 30);
264
265 p_end_date := TRUNC(ld_end_date,'Y');
266 p_business_group_id := ln_business_group_id;
267 p_legal_employer_id := ln_legal_er_id;
268
269 hr_utility.set_location(lv_procedure_name, 50);
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
274 lv_procedure_name;
275
276 hr_utility.trace(lv_error_message || '-' || SQLERRM);
277
278 lv_error_message :=
279 pay_emp_action_arch.set_error_message(lv_error_message);
280
281 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
282 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
283 hr_utility.raise_error;
284
285 END get_payroll_action_info;
286
287 /****************************************************************************
288 Name : RANGE_CURSOR
289 Description : This procedure prepares range of persons to be processed.
290 *****************************************************************************/
291 PROCEDURE range_cursor ( P_PAYROLL_ACTION_ID NUMBER
292 ,P_SQLSTR OUT NOCOPY VARCHAR2 ) AS
293
294 l_proc_name varchar2(100);
295
296 BEGIN
297 l_proc_name := g_proc_name || 'RANGE_CURSOR';
298
299 hr_utility_trace ('Entering '||l_proc_name);
300
301 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
302
303 get_payroll_action_info (p_payroll_action_id
304 ,gd_effective_date
305 ,gn_business_group_id
306 ,gn_legal_er_id);
307
308 hr_utility_trace ('gd_effective_date = '|| gd_effective_date);
309 hr_utility_trace ('gn_business_group_id = '|| gn_business_group_id);
310 hr_utility_trace ('gn_legal_er_id = '|| gn_legal_er_id);
311
312 p_sqlstr := '
313 SELECT DISTINCT paa_arch.serial_number
314 FROM pay_assignment_actions paa_arch
315 ,pay_payroll_actions ppa_arch
316 WHERE ppa_arch.business_group_id = '|| gn_business_group_id ||'
317 AND ppa_arch.report_type = ''MX_YREND_ARCHIVE''
318 AND ppa_arch.report_qualifier = ''MX''
319 AND ppa_arch.report_category = ''ARCHIVE''
320 AND pay_mx_utility.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',
321 ppa_arch.legislative_parameters) = '||gn_legal_er_id||'
322 AND TRUNC(ppa_arch.effective_date,''Y'') =
323 fnd_date.canonical_to_date('''||
324 fnd_date.date_to_canonical(gd_effective_date)||''')
325 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
326 AND paa_arch.action_status = ''C''
327 AND :p_payroll_action_id = '||p_payroll_action_id||'
328 ORDER BY serial_number';
329
330 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
331 hr_utility_trace ('Leaving '||l_proc_name);
332
333 END range_cursor;
334
335
336 /****************************************************************************
337 Name : ACTION_CREATION
338 Description : This procedure creates assignment actions for
339 Format-2D process.
340 *****************************************************************************/
341 PROCEDURE action_creation ( p_payroll_action_id NUMBER,
342 p_start_person_id NUMBER,
343 p_end_person_id NUMBER,
344 p_chunk NUMBER) AS
345
346 CURSOR c_arch_asg ( cp_business_group_id NUMBER
347 ,cp_legal_er_id NUMBER
348 ,cp_effective_date DATE
349 ,cp_start_person_id NUMBER
350 ,cp_end_person_id NUMBER) IS
351 SELECT paa_arch.assignment_action_id
352 ,paa_arch.assignment_id
353 ,paa_arch.serial_number person_id
354 ,ppa_arch.payroll_action_id
355 FROM pay_assignment_actions paa_arch,
356 pay_payroll_actions ppa_arch
357 WHERE ppa_arch.business_group_id = cp_business_group_id
358 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
359 AND ppa_arch.report_qualifier = 'MX'
360 AND ppa_arch.report_category = 'ARCHIVE'
361 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
362 ppa_arch.legislative_parameters) = cp_legal_er_id
363 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
364 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
365 AND paa_arch.action_status = 'C'
366 AND paa_arch.serial_number BETWEEN cp_start_person_id
367 AND cp_end_person_id
368 ORDER BY paa_arch.serial_number,
369 paa_arch.assignment_action_id desc;
370
371 CURSOR c_arch_asg_range ( cp_business_group_id NUMBER
372 ,cp_legal_er_id NUMBER
373 ,cp_effective_date DATE
374 ,cp_chunk NUMBER
375 ,cp_payroll_action_id NUMBER) IS
376 SELECT paa_arch.assignment_action_id
377 ,paa_arch.assignment_id
378 ,paa_arch.serial_number person_id
379 ,ppa_arch.payroll_action_id
380 FROM pay_assignment_actions paa_arch,
381 pay_payroll_actions ppa_arch,
382 pay_population_ranges ppr
383 WHERE ppa_arch.business_group_id = cp_business_group_id
384 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
385 AND ppa_arch.report_qualifier = 'MX'
386 AND ppa_arch.report_category = 'ARCHIVE'
387 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
388 ppa_arch.legislative_parameters) = cp_legal_er_id
389 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
390 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
391 AND paa_arch.action_status = 'C'
392 AND paa_arch.serial_number = ppr.person_id
393 AND ppr.chunk_number = cp_chunk
394 AND ppr.payroll_action_id = cp_payroll_action_id
395 ORDER BY paa_arch.serial_number,
396 paa_arch.assignment_action_id desc;
397
398 l_proc_name varchar2(100);
399 lv_future_magtape_exists varchar2(1);
400 lb_range_person_on boolean;
401 ln_person_id number;
402 ln_prev_arch_pact_id number;
403 ln_arch_pact_id number;
404 ln_prev_person_id number;
405 ln_prev_asg_id number;
406 ln_mag_asg_act_id number;
407 ln_assignment_id number;
408 ln_arch_act_id number;
409 ln_asg_count number;
410 BEGIN
411 l_proc_name := g_proc_name || 'ACTION_CREATION';
412
413 hr_utility_trace ('Entering '||l_proc_name);
414 hr_utility_trace ('Parameters ....');
415 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
416 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
417 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
418 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
419
420 ln_prev_person_id := -1;
421 ln_prev_asg_id := -1;
422 ln_prev_arch_pact_id := -1;
423
424 IF gn_legal_er_id IS NULL THEN
425
426 get_payroll_action_info (p_payroll_action_id
427 ,gd_effective_date
428 ,gn_business_group_id
429 ,gn_legal_er_id);
430
431 END IF;
432
433 ln_asg_count := 0;
434
435 lb_range_person_on := pay_ac_utility.range_person_on(
436 p_report_type => 'FORMAT2D_MAG'
437 ,p_report_format => 'FORMAT2D_MAG'
438 ,p_report_qualifier => 'FORMAT2D_MAG'
439 ,p_report_category => 'RT');
440
441 IF lb_range_person_on THEN
442
443 hr_utility_trace ('Person ranges are ON');
444
445 OPEN c_arch_asg_range( gn_business_group_id
446 ,gn_legal_er_id
447 ,gd_effective_date
448 ,p_chunk
449 ,p_payroll_action_id);
450
451 ELSE
452
453 hr_utility_trace ('Person ranges are OFF');
454
455 OPEN c_arch_asg( gn_business_group_id
456 ,gn_legal_er_id
457 ,gd_effective_date
458 ,p_start_person_id
459 ,p_end_person_id);
460
461 END IF;
462
463 LOOP
464 IF lb_range_person_on THEN
465 FETCH c_arch_asg_range INTO ln_arch_act_id,
466 ln_assignment_id,
467 ln_person_id,
468 ln_arch_pact_id;
469 EXIT WHEN c_arch_asg_range%NOTFOUND;
470 ELSE
471 FETCH c_arch_asg INTO ln_arch_act_id,
472 ln_assignment_id,
473 ln_person_id,
474 ln_arch_pact_id;
475 EXIT WHEN c_arch_asg%NOTFOUND;
476 END IF;
477
478 ln_asg_count := ln_asg_count + 1;
479
480 hr_utility_trace ('-------------');
481 hr_utility_trace('Current archiver asg action = '||ln_arch_act_id);
482 hr_utility_trace('Current person = '||ln_person_id);
483 hr_utility_trace('Previous person = '||ln_prev_person_id);
484
485 IF (ln_person_id <> ln_prev_person_id) THEN
486
487 SELECT pay_assignment_actions_s.nextval
488 INTO ln_mag_asg_act_id
489 FROM dual;
490
491 hr_utility_trace('Creating magtape assignment action '||
492 ln_mag_asg_act_id);
493
494 hr_nonrun_asact.insact(ln_mag_asg_act_id
495 ,ln_assignment_id
496 ,p_payroll_action_id
497 ,p_chunk
498 ,gn_legal_er_id
499 ,null
500 ,'U'
501 ,null);
502
503 -- insert an interlock to this action
504 hr_utility.trace('Locking Action in IF = ' || ln_mag_asg_act_id);
505 hr_utility.trace('Locked Action in IF = ' || ln_arch_act_id);
506
507 hr_nonrun_asact.insint(ln_mag_asg_act_id,
508 ln_arch_act_id);
509
510 ELSE
511
512 -- insert an interlock to this action
513 hr_utility.trace('Locking Action in ELSE = ' || ln_mag_asg_act_id);
514 hr_utility.trace('Locked Action in ELSE = ' || ln_arch_act_id);
515
516 hr_nonrun_asact.insint(ln_mag_asg_act_id,
517 ln_arch_act_id);
518
519 END IF;
520
521 ln_prev_person_id := ln_person_id;
522
523 END LOOP;
524
525 hr_utility_trace(ln_asg_count || ' archiver actions processed in chunk '||
526 p_chunk);
527
528 IF lb_range_person_on THEN
529 CLOSE c_arch_asg_range;
530 ELSE
531 CLOSE c_arch_asg;
532 END IF;
533
534 hr_utility_trace ('Leaving '||l_proc_name);
535
536 END action_creation;
537
538 /****************************************************************************
539 Name : GENERATE_XML
540 Description : This procedure fetches archived data, converts it to XML
541 format and appends to pay_mag_tape.g_blob_value.
542 *****************************************************************************/
543 PROCEDURE generate_xml AS
544
545 CURSOR c_format2d_rec (cp_assignment_action_id number) IS
546 SELECT format2d.person_id PERSON_ID
547 ,to_char(fnd_date.canonical_to_date(start_month),'mm') START_MONTH
548 ,to_char(fnd_date.canonical_to_date(end_month),'mm') END_MONTH
549 ,replace(ER_RFC_ID,'-','') ER_RFC_ID
550 ,CURP
551 ,ltrim(rtrim(PATERNAL_LAST_NAME)) PATERNAL_LAST_NAME
552 ,ltrim(rtrim(MATERNAL_LAST_NAME)) MATERNAL_LAST_NAME
553 ,ltrim(rtrim(NAMES)) NAMES
554 ,ltrim(rtrim(PATERNAL_LAST_NAME))||' '||
555 ltrim(rtrim(MATERNAL_LAST_NAME))||' '||
556 ltrim(rtrim(NAMES)) NAME
557 ,0 WAGE_LEVEL
558 ,(fnd_date.canonical_to_date(end_month) -
559 fnd_date.canonical_to_date(start_month)) + 1 TOTAL_DAYS_WORKED
560 ,(ISR_SUBJECT_FOR_FIXED_EARNINGS +
561 ISR_EXEMPT_FOR_FIXED_EARNINGS) SAL_WAGES
562 ,nvl(ISR_SUBJECT_FOR_OVERTIME,0) +
563 nvl(ISR_EXEMPT_FOR_OVERTIME,0) OVERTIME
564 ,nvl(ISR_SUBJECT_FOR_PROFIT_SHARING,0) +
565 nvl(ISR_EXEMPT_FOR_PROFIT_SHARING,0) PROFIT_SHARING
566 ,nvl(ISR_SUBJECT_FOR_XMAS_BONUS,0) +
567 nvl(ISR_EXEMPT_FOR_XMAS_BONUS,0) CHRISTMAS_BONUS
568 ,nvl(ISR_SUBJECT_FOR_VAC_PREMIUM,0) +
569 nvl(ISR_EXEMPT_FOR_VAC_PREMIUM,0) VACATION_PREMIUM
570 ,nvl(ISR_SUBJECT_FOR_SAVINGS_FUND,0) +
571 nvl(ISR_EXEMPT_FOR_SAVINGS_FUND,0) SAVING_FUND
572 ,AID_FOR_PANTRY_AND_FOOD
573 ,nvl(ISR_SUBJECT_FOR_TRANS_AID,0) +
574 nvl(ISR_EXEMPT_FOR_TRANS_AID,0) TRANSPORTATION_AID
575 ,0 OTHER_EARNINGS
576 ,nvl(TOTAL_SUBJECT_EARNINGS,0)+
577 nvl(TOTAL_EXEMPT_EARNINGS,0) TOTAL_EARNINGS
578 FROM pay_mx_isr_tax_format37_v format2d
579 ,pay_assignment_actions paa
580 ,pay_action_interlocks pai
581 WHERE format2d.payroll_action_id = paa.payroll_action_id
582 AND format2d.person_id = to_number(paa.serial_number)
583 AND paa.assignment_action_id = pai.locked_action_id
584 AND pai.locking_action_id = cp_assignment_action_id
585 ORDER BY effective_date DESC;
586
587 CURSOR c_min_wage( cp_effective_date IN DATE ) IS
588 select legislation_info2
589 from PAY_MX_LEGISLATION_INFO_F
590 where LEGISLATION_INFO_TYPE = 'MX Minimum Wage Information'
591 and effective_start_date = cp_effective_date
592 and legislation_info1 = 'GMW';
593
594 CURSOR c_pact_id (cp_assignment_action_id NUMBER) IS
595 SELECT paa.payroll_action_id
596 FROM pay_assignment_actions paa
597 WHERE paa.assignment_action_id = cp_assignment_action_id;
598
599 l_proc_name varchar2(100);
600 l_xml BLOB;
601 lb_person_processed boolean;
602
603 ln_assignment_action_id NUMBER;
604 ln_person_id NUMBER;
605 ln_business_group_id NUMBER;
606 ln_payroll_action_id NUMBER;
607
608 format2d c_format2d_rec%ROWTYPE;
609 prev_format2d c_format2d_rec%ROWTYPE;
610 ln_min_wage NUMBER;
611 ln_avg_daily_sal NUMBER;
612 lv_level VARCHAR2(10);
613 ln_count NUMBER;
614 ln_session_id NUMBER;
615
616 BEGIN
617 l_proc_name := g_proc_name || 'GENERATE_XML';
618 hr_utility_trace ('Entering '||l_proc_name);
619
620 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
621 ('TRANSFER_ACT_ID');
622
623 hr_utility_trace ('Fetching transactions for magtape asg action '||
624 ln_assignment_action_id);
625
626 format2d_xml_tbl.DELETE;
627
628 gn_success_fail := 0;
629 gn_sep_bal := 0;
630 gn_ass_bal := 0;
631 gn_emp_bal := 0;
632 ln_avg_daily_sal := 0;
633 lv_level := '0';
634
635 ln_person_id := -1;
636
637 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
638 INTO EOL
639 FROM dual;
640
641 OPEN c_pact_id( ln_assignment_action_id );
642 FETCH c_pact_id INTO ln_payroll_action_id;
643 CLOSE c_pact_id;
644
645 IF gn_legal_er_id IS NULL THEN
646
647 get_payroll_action_info (ln_payroll_action_id
648 ,gd_effective_date
649 ,gn_business_group_id
650 ,gn_legal_er_id);
651
652 END IF;
653
654 OPEN c_format2d_rec(ln_assignment_action_id);
655
656 LOOP
657
658 FETCH c_format2d_rec INTO format2d;
659 EXIT WHEN c_format2d_rec%NOTFOUND;
660
661 IF ln_person_id = -1 THEN
662 prev_format2d := format2d;
663 ln_person_id := format2d.person_id;
664 ELSE
665
666 if format2d.total_earnings <> format2d.profit_sharing then
667
668 prev_format2d.TOTAL_DAYS_WORKED :=
669 prev_format2d.TOTAL_DAYS_WORKED +
670 format2d.TOTAL_DAYS_WORKED;
671 end if;
672
673 prev_format2d.SAL_WAGES := prev_format2d.SAL_WAGES +
674 format2d.SAL_WAGES;
675 prev_format2d.OVERTIME := prev_format2d.OVERTIME +
676 format2d.OVERTIME;
677 prev_format2d.PROFIT_SHARING := prev_format2d.PROFIT_SHARING +
678 format2d.PROFIT_SHARING;
679 prev_format2d.CHRISTMAS_BONUS := prev_format2d.CHRISTMAS_BONUS +
680 format2d.CHRISTMAS_BONUS;
681 prev_format2d.VACATION_PREMIUM := prev_format2d.VACATION_PREMIUM +
682 format2d.VACATION_PREMIUM;
683 prev_format2d.SAVING_FUND := prev_format2d.SAVING_FUND +
684 format2d.SAVING_FUND;
685 prev_format2d.AID_FOR_PANTRY_AND_FOOD :=
686 prev_format2d.AID_FOR_PANTRY_AND_FOOD +
687 format2d.AID_FOR_PANTRY_AND_FOOD;
688 prev_format2d.TRANSPORTATION_AID :=
689 prev_format2d.TRANSPORTATION_AID +
690 format2d.TRANSPORTATION_AID;
691 prev_format2d.OTHER_EARNINGS := prev_format2d.OTHER_EARNINGS +
692 format2d.OTHER_EARNINGS;
693 prev_format2d.TOTAL_EARNINGS := prev_format2d.TOTAL_EARNINGS +
694 format2d.TOTAL_EARNINGS;
695
696 END IF;
697
698 END LOOP;
699 CLOSE c_format2d_rec;
700
701 OPEN c_min_wage(gd_effective_date);
702 FETCH c_min_wage INTO ln_min_wage;
703 CLOSE c_min_wage;
704
705 if prev_format2d.TOTAL_DAYS_WORKED <> 0 THEN
706 ln_avg_daily_sal := prev_format2d.TOTAL_EARNINGS /
707 prev_format2d.TOTAL_DAYS_WORKED;
708 else
709 ln_avg_daily_sal := prev_format2d.TOTAL_EARNINGS;
710 end if;
711
712 if ln_avg_daily_sal > ln_min_wage * 10 THEN
713 lv_level := '5';
714 elsif ln_avg_daily_sal > ln_min_wage * 5 THEN
715 lv_level := '4';
716 elsif ln_avg_daily_sal > ln_min_wage * 3 THEN
717 lv_level := '3';
718 elsif ln_avg_daily_sal > ln_min_wage THEN
719 lv_level := '2';
720 else
721 lv_level := '1';
722 end if;
723
724 prev_format2d.OTHER_EARNINGS := prev_format2d.TOTAL_EARNINGS -
725 (prev_format2d.SAL_WAGES +
726 prev_format2d.OVERTIME +
727 prev_format2d.PROFIT_SHARING +
728 prev_format2d.CHRISTMAS_BONUS +
729 prev_format2d.VACATION_PREMIUM +
730 prev_format2d.SAVING_FUND +
731 prev_format2d.AID_FOR_PANTRY_AND_FOOD +
732 prev_format2d.TRANSPORTATION_AID
733 );
734
735
736 --populate_xml_table('RFC_ID', prev_format2d.ER_RFC_ID,'TEXT');
737 populate_xml_table('CURP', prev_format2d.CURP,'TEXT');
738 populate_xml_table('NAME', prev_format2d.NAME,'TEXT');
739 populate_xml_table('LEVEL', lv_level,'TEXT');
740 populate_xml_table('TOTAL_DAYS_WORKED',
741 prev_format2d.TOTAL_DAYS_WORKED,'TEXT');
742 populate_xml_table('SAL_WAGES',
743 prev_format2d.SAL_WAGES,'TEXT');
744 populate_xml_table('OVERTIME',
745 prev_format2d.OVERTIME,'TEXT');
746 populate_xml_table('PROFIT_SHARING',
747 prev_format2d.PROFIT_SHARING,'TEXT');
748 populate_xml_table('CHRISTMAS_BONUS',
749 prev_format2d.CHRISTMAS_BONUS,'TEXT');
750 populate_xml_table('VACATION_PREMIUM',
751 prev_format2d.VACATION_PREMIUM,'TEXT');
752 populate_xml_table('SAVING_FUND',
753 prev_format2d.SAVING_FUND,'TEXT');
754 populate_xml_table('AID_FOR_PANTRY_AND_FOOD',
755 prev_format2d.AID_FOR_PANTRY_AND_FOOD,'TEXT');
756 populate_xml_table('TRANSPORTATION_AID',
757 prev_format2d.TRANSPORTATION_AID,'TEXT');
758 populate_xml_table('OTHER_EARNINGS',
759 prev_format2d.OTHER_EARNINGS,'TEXT');
760 populate_xml_table('TOTAL_EARNINGS',
761 prev_format2d.TOTAL_EARNINGS,'TEXT');
762
763
764 load_xml_internal('CS','FORMAT_2D',NULL);
765
766 FOR i IN format2d_xml_tbl.FIRST..format2d_xml_tbl.LAST LOOP
767
768 load_xml_internal('D',format2d_xml_tbl(i).name,format2d_xml_tbl(i).value);
769
770 END LOOP;
771
772 load_xml_internal('CE','FORMAT_2D',NULL);
773
774 ln_session_id := USERENV('sessionid');
775
776 SELECT COUNT(*)
777 INTO ln_count
778 FROM pay_us_rpt_totals
779 WHERE tax_unit_id = ln_payroll_action_id
780 AND session_id = ln_session_id;
781
782
783 IF ln_count = 0 THEN
784
785 INSERT INTO pay_us_rpt_totals ( session_id
786 ,business_group_id
787 ,tax_unit_id
788 ,organization_name )
789 VALUES ( ln_session_id
790 ,gn_business_group_id
791 ,ln_payroll_action_id
792 ,prev_format2d.er_rfc_id );
793
794 END IF;
795
796 IF lv_level = '1' THEN
797
798 UPDATE pay_us_rpt_totals
799 SET value1 = NVL(value1,0) + 1 -- No. of Employees
800 ,value6 = NVL(value6,0) + prev_format2d.SAL_WAGES
801 ,value11 = NVL(value11,0) + prev_format2d.OVERTIME
802 ,value16 = NVL(value16,0) + prev_format2d.PROFIT_SHARING
803 ,value21 = NVL(value21,0) + prev_format2d.CHRISTMAS_BONUS
804 ,value26 = NVL(value26,0) + prev_format2d.VACATION_PREMIUM
805 ,attribute1 = NVL(attribute1,'0') + prev_format2d.SAVING_FUND
806 ,attribute6 = NVL(attribute6,'0') +
807 prev_format2d.AID_FOR_PANTRY_AND_FOOD
808 ,attribute11 = NVL(attribute11,'0') +
809 prev_format2d.TRANSPORTATION_AID
810 ,attribute16 = NVL(attribute16,'0') +
811 prev_format2d.OTHER_EARNINGS
812 ,attribute21 = NVL(attribute21,'0') +
813 prev_format2d.TOTAL_EARNINGS
814 WHERE tax_unit_id = ln_payroll_action_id
815 AND session_id = ln_session_id;
816
817 ELSIF lv_level = '2' THEN
818
819 UPDATE pay_us_rpt_totals
820 SET value2 = NVL(value2,0) + 1 -- No. of Employees
821 ,value7 = NVL(value7,0) + prev_format2d.SAL_WAGES
822 ,value12 = NVL(value12,0) + prev_format2d.OVERTIME
823 ,value17 = NVL(value17,0) + prev_format2d.PROFIT_SHARING
824 ,value22 = NVL(value22,0) + prev_format2d.CHRISTMAS_BONUS
825 ,value27 = NVL(value27,0) + prev_format2d.VACATION_PREMIUM
826 ,attribute2 = NVL(attribute2,'0') + prev_format2d.SAVING_FUND
827 ,attribute7 = NVL(attribute7,'0') +
828 prev_format2d.AID_FOR_PANTRY_AND_FOOD
829 ,attribute12 = NVL(attribute12,'0') +
830 prev_format2d.TRANSPORTATION_AID
831 ,attribute17 = NVL(attribute17,'0') +
832 prev_format2d.OTHER_EARNINGS
833 ,attribute22 = NVL(attribute22,'0') +
834 prev_format2d.TOTAL_EARNINGS
835 WHERE tax_unit_id = ln_payroll_action_id
836 AND session_id = ln_session_id;
837
838
839 ELSIF lv_level = '3' THEN
840
841 UPDATE pay_us_rpt_totals
842 SET value3 = NVL(value3,0) + 1 -- No. of Employees
843 ,value8 = NVL(value8,0) + prev_format2d.SAL_WAGES
844 ,value13 = NVL(value13,0) + prev_format2d.OVERTIME
845 ,value18 = NVL(value18,0) + prev_format2d.PROFIT_SHARING
846 ,value23 = NVL(value23,0) + prev_format2d.CHRISTMAS_BONUS
847 ,value28 = NVL(value28,0) + prev_format2d.VACATION_PREMIUM
848 ,attribute3 = NVL(attribute3,'0') + prev_format2d.SAVING_FUND
849 ,attribute8 = NVL(attribute8,'0') +
850 prev_format2d.AID_FOR_PANTRY_AND_FOOD
851 ,attribute13 = NVL(attribute13,'0') +
852 prev_format2d.TRANSPORTATION_AID
853 ,attribute18 = NVL(attribute18,'0') +
854 prev_format2d.OTHER_EARNINGS
855 ,attribute23 = NVL(attribute23,'0') +
856 prev_format2d.TOTAL_EARNINGS
857 WHERE tax_unit_id = ln_payroll_action_id
858 AND session_id = ln_session_id;
859
860
861 ELSIF lv_level = '4' THEN
862
863 UPDATE pay_us_rpt_totals
864 SET value4 = NVL(value4,0) + 1 -- No. of Employees
865 ,value9 = NVL(value9,0) + prev_format2d.SAL_WAGES
866 ,value14 = NVL(value14,0) + prev_format2d.OVERTIME
867 ,value19 = NVL(value19,0) + prev_format2d.PROFIT_SHARING
868 ,value24 = NVL(value24,0) + prev_format2d.CHRISTMAS_BONUS
869 ,value29 = NVL(value29,0) + prev_format2d.VACATION_PREMIUM
870 ,attribute4 = NVL(attribute4,'0') + prev_format2d.SAVING_FUND
871 ,attribute9 = NVL(attribute9,'0') +
872 prev_format2d.AID_FOR_PANTRY_AND_FOOD
873 ,attribute14 = NVL(attribute14,'0') +
874 prev_format2d.TRANSPORTATION_AID
875 ,attribute19 = NVL(attribute19,'0') +
876 prev_format2d.OTHER_EARNINGS
877 ,attribute24 = NVL(attribute24,'0') +
878 prev_format2d.TOTAL_EARNINGS
879 WHERE tax_unit_id = ln_payroll_action_id
880 AND session_id = ln_session_id;
881
882
883 ELSIF lv_level = '5' THEN
884
885 UPDATE pay_us_rpt_totals
886 SET value5 = NVL(value5,0) + 1 -- No. of Employees
887 ,value10 = NVL(value10,0) + prev_format2d.SAL_WAGES
888 ,value15 = NVL(value15,0) + prev_format2d.OVERTIME
889 ,value20 = NVL(value20,0) + prev_format2d.PROFIT_SHARING
890 ,value25 = NVL(value25,0) + prev_format2d.CHRISTMAS_BONUS
891 ,value30 = NVL(value30,0) + prev_format2d.VACATION_PREMIUM
892 ,attribute5 = NVL(attribute5,'0') + prev_format2d.SAVING_FUND
893 ,attribute10 = NVL(attribute10,'0') +
894 prev_format2d.AID_FOR_PANTRY_AND_FOOD
895 ,attribute15 = NVL(attribute15,'0') +
896 prev_format2d.TRANSPORTATION_AID
897 ,attribute20 = NVL(attribute20,'0') +
898 prev_format2d.OTHER_EARNINGS
899 ,attribute25 = NVL(attribute25,'0') +
900 prev_format2d.TOTAL_EARNINGS
901 WHERE tax_unit_id = ln_payroll_action_id
902 AND session_id = ln_session_id;
903
904
905 END IF;
906
907 hr_utility_trace ('Leaving '||l_proc_name);
908
909 /*EXCEPTION
910 WHEN OTHERS THEN
911 hr_utility_trace (SQLERRM);
912 RAISE; */
913 END GENERATE_XML;
914
915
916 /****************************************************************************
917 Name : GENERATE_XML_HEADER
918 Description : This procedure generates XML header information and appends to
919 pay_mag_tape.g_blob_value.
920 *****************************************************************************/
921 PROCEDURE generate_xml_header AS
922 l_proc_name varchar2(100);
923 lv_buf varchar2(2000);
924 BEGIN
925 l_proc_name := g_proc_name || 'GENERATE_XML_HEADER';
926 hr_utility_trace ('Entering '||l_proc_name);
927
928 hr_utility_trace ('Root XML tag = '||
929 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
930
931 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
932
933 write_to_magtape_lob (lv_buf);
934
935 hr_utility_trace ('Leaving '||l_proc_name);
936 END generate_xml_header;
937
938
939 /****************************************************************************
940 Name : GENERATE_XML_FOOTER
941 Description : This procedure generates XML information for GRE and the final
942 closing tag. Final result is appended to
943 pay_mag_tape.g_blob_value.
944 *****************************************************************************/
945 PROCEDURE generate_xml_footer AS
946
947 CURSOR c_format_2d_totals ( cp_payroll_action_id IN NUMBER) IS
948 select organization_name RFC_ID
949 ,NVL(SUM(value1),0) NO_OF_EMPLOYEES_280042
950 ,NVL(SUM(value2),0) NO_OF_EMPLOYEES_280043
951 ,NVL(SUM(value3),0) NO_OF_EMPLOYEES_280044
952 ,NVL(SUM(value4),0) NO_OF_EMPLOYEES_280045
953 ,NVL(SUM(value5),0) NO_OF_EMPLOYEES_280046
954 ,NVL(SUM(value6),0) SAL_WAGES_280047
955 ,NVL(SUM(value7),0) SAL_WAGES_280057
956 ,NVL(SUM(value8),0) SAL_WAGES_280067
957 ,NVL(SUM(value9),0) SAL_WAGES_280077
958 ,NVL(SUM(value10),0) SAL_WAGES_280087
959 ,NVL(SUM(value11),0) OVERTIME_280048
960 ,NVL(SUM(value12),0) OVERTIME_280058
961 ,NVL(SUM(value13),0) OVERTIME_280068
962 ,NVL(SUM(value14),0) OVERTIME_280078
963 ,NVL(SUM(value15),0) OVERTIME_280088
964 ,NVL(SUM(value16),0) PROFIT_SHARING_280049
965 ,NVL(SUM(value17),0) PROFIT_SHARING_280059
966 ,NVL(SUM(value18),0) PROFIT_SHARING_280069
967 ,NVL(SUM(value19),0) PROFIT_SHARING_280079
968 ,NVL(SUM(value20),0) PROFIT_SHARING_280089
969 ,NVL(SUM(value21),0) CHRISTMAS_BONUS_280050
970 ,NVL(SUM(value22),0) CHRISTMAS_BONUS_280060
971 ,NVL(SUM(value23),0) CHRISTMAS_BONUS_280070
972 ,NVL(SUM(value24),0) CHRISTMAS_BONUS_280080
973 ,NVL(SUM(value25),0) CHRISTMAS_BONUS_280090
974 ,NVL(SUM(value26),0) VACATION_PREMIUM_280051
975 ,NVL(SUM(value27),0) VACATION_PREMIUM_280061
976 ,NVL(SUM(value28),0) VACATION_PREMIUM_280071
977 ,NVL(SUM(value29),0) VACATION_PREMIUM_280081
978 ,NVL(SUM(value30),0) VACATION_PREMIUM_280091
979 ,NVL(SUM(attribute1),0) SAVING_FUND_280052
980 ,NVL(SUM(attribute2),0) SAVING_FUND_280062
981 ,NVL(SUM(attribute3),0) SAVING_FUND_280072
982 ,NVL(SUM(attribute4),0) SAVING_FUND_280082
983 ,NVL(SUM(attribute5),0) SAVING_FUND_280092
984 ,NVL(SUM(attribute6),0) AID_FOR_PANTRY_AND_FOOD_280053
985 ,NVL(SUM(attribute7),0) AID_FOR_PANTRY_AND_FOOD_280063
986 ,NVL(SUM(attribute8),0) AID_FOR_PANTRY_AND_FOOD_280073
987 ,NVL(SUM(attribute9),0) AID_FOR_PANTRY_AND_FOOD_280083
988 ,NVL(SUM(attribute10),0) AID_FOR_PANTRY_AND_FOOD_280093
989 ,NVL(SUM(attribute11),0) TRANSPORTATION_AID_280054
990 ,NVL(SUM(attribute12),0) TRANSPORTATION_AID_280064
991 ,NVL(SUM(attribute13),0) TRANSPORTATION_AID_280074
992 ,NVL(SUM(attribute14),0) TRANSPORTATION_AID_280084
993 ,NVL(SUM(attribute15),0) TRANSPORTATION_AID_280094
994 ,NVL(SUM(attribute16),0) OTHER_EARNINGS_280055
995 ,NVL(SUM(attribute17),0) OTHER_EARNINGS_280065
996 ,NVL(SUM(attribute18),0) OTHER_EARNINGS_280075
997 ,NVL(SUM(attribute19),0) OTHER_EARNINGS_280085
998 ,NVL(SUM(attribute20),0) OTHER_EARNINGS_280095
999 ,NVL(SUM(attribute21),0) TOTAL_EARNINGS_280056
1000 ,NVL(SUM(attribute22),0) TOTAL_EARNINGS_280066
1001 ,NVL(SUM(attribute23),0) TOTAL_EARNINGS_280076
1002 ,NVL(SUM(attribute24),0) TOTAL_EARNINGS_280086
1003 ,NVL(SUM(attribute25),0) TOTAL_EARNINGS_280096
1004 FROM pay_us_rpt_totals
1005 WHERE tax_unit_id = cp_payroll_action_id
1006 GROUP by organization_name;
1007
1008 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
1009 ln_payroll_action_id NUMBER;
1010 l_xml BLOB;
1011 l_proc_name VARCHAR2(100);
1012 ln_chars NUMBER;
1013 ln_offset NUMBER;
1014 lv_buf VARCHAR2(8000);
1015 lr_xml RAW (32767);
1016 ln_amt NUMBER;
1017
1018 f2d_tot c_format_2d_totals%ROWTYPE;
1019
1020 BEGIN
1021
1022 l_proc_name := g_proc_name || 'GENERATE_XML_FOOTER';
1023
1024 hr_utility_trace ('Entering '||l_proc_name);
1025
1026 format2d_xml_tbl.DELETE;
1027
1028 ln_payroll_action_id := pay_magtape_generic.get_parameter_value(
1029 'TRANSFER_PAYROLL_ACTION_ID');
1030
1031 OPEN c_format_2d_totals( ln_payroll_action_id );
1032 FETCH c_format_2d_totals INTO f2d_tot;
1033 CLOSE c_format_2d_totals;
1034
1035 --populate_xml....for all 56 fields
1036
1037 populate_xml_table('RFC_ID', f2d_tot.RFC_ID,'TEXT');
1038 populate_xml_table('NO_OF_EMPLOYEES_280042',
1039 f2d_tot.NO_OF_EMPLOYEES_280042,'TEXT');
1040 populate_xml_table('NO_OF_EMPLOYEES_280043',
1041 f2d_tot.NO_OF_EMPLOYEES_280043,'TEXT');
1042 populate_xml_table('NO_OF_EMPLOYEES_280044',
1043 f2d_tot.NO_OF_EMPLOYEES_280044,'TEXT');
1044 populate_xml_table('NO_OF_EMPLOYEES_280045',
1045 f2d_tot.NO_OF_EMPLOYEES_280045,'TEXT');
1046 populate_xml_table('NO_OF_EMPLOYEES_280046',
1047 f2d_tot.NO_OF_EMPLOYEES_280046,'TEXT');
1048 populate_xml_table('SAL_WAGES_280047', f2d_tot.SAL_WAGES_280047, 'TEXT');
1049 populate_xml_table('SAL_WAGES_280057', f2d_tot.SAL_WAGES_280057, 'TEXT');
1050 populate_xml_table('SAL_WAGES_280067', f2d_tot.SAL_WAGES_280067,'TEXT');
1051 populate_xml_table('SAL_WAGES_280077', f2d_tot.SAL_WAGES_280077,'TEXT');
1052 populate_xml_table('SAL_WAGES_280087', f2d_tot.SAL_WAGES_280087,'TEXT');
1053 populate_xml_table('OVERTIME_280048', f2d_tot.OVERTIME_280048,'TEXT');
1054 populate_xml_table('OVERTIME_280058', f2d_tot.OVERTIME_280058,'TEXT');
1055 populate_xml_table('OVERTIME_280068', f2d_tot.OVERTIME_280068,'TEXT');
1056 populate_xml_table('OVERTIME_280078', f2d_tot.OVERTIME_280078,'TEXT');
1057 populate_xml_table('OVERTIME_280088', f2d_tot.OVERTIME_280088,'TEXT');
1058 populate_xml_table('PROFIT_SHARING_280049',
1059 f2d_tot.PROFIT_SHARING_280049,'TEXT');
1060 populate_xml_table('PROFIT_SHARING_280059',
1061 f2d_tot.PROFIT_SHARING_280059,'TEXT');
1062 populate_xml_table('PROFIT_SHARING_280069',
1063 f2d_tot.PROFIT_SHARING_280069,'TEXT');
1064 populate_xml_table('PROFIT_SHARING_280079',
1065 f2d_tot.PROFIT_SHARING_280079,'TEXT');
1066 populate_xml_table('PROFIT_SHARING_280089',
1067 f2d_tot.PROFIT_SHARING_280089,'TEXT');
1068 populate_xml_table('CHRISTMAS_BONUS_280050',
1069 f2d_tot.CHRISTMAS_BONUS_280050,'TEXT');
1070 populate_xml_table('CHRISTMAS_BONUS_280060',
1071 f2d_tot.CHRISTMAS_BONUS_280060,'TEXT');
1072 populate_xml_table('CHRISTMAS_BONUS_280070',
1073 f2d_tot.CHRISTMAS_BONUS_280070,'TEXT');
1074 populate_xml_table('CHRISTMAS_BONUS_280080',
1075 f2d_tot.CHRISTMAS_BONUS_280080,'TEXT');
1076 populate_xml_table('CHRISTMAS_BONUS_280090',
1077 f2d_tot.CHRISTMAS_BONUS_280090,'TEXT');
1078 populate_xml_table('VACATION_PREMIUM_280051',
1079 f2d_tot.VACATION_PREMIUM_280051,'TEXT');
1080 populate_xml_table('VACATION_PREMIUM_280061',
1081 f2d_tot.VACATION_PREMIUM_280061,'TEXT');
1082 populate_xml_table('VACATION_PREMIUM_280071',
1083 f2d_tot.VACATION_PREMIUM_280071,'TEXT');
1084 populate_xml_table('VACATION_PREMIUM_280081',
1085 f2d_tot.VACATION_PREMIUM_280081,'TEXT');
1086 populate_xml_table('VACATION_PREMIUM_280091',
1087 f2d_tot.VACATION_PREMIUM_280091,'TEXT');
1088 populate_xml_table('SAVING_FUND_280052',
1089 f2d_tot.SAVING_FUND_280052,'TEXT');
1090 populate_xml_table('SAVING_FUND_280062',
1091 f2d_tot.SAVING_FUND_280062,'TEXT');
1092 populate_xml_table('SAVING_FUND_280072',
1093 f2d_tot.SAVING_FUND_280072,'TEXT');
1094 populate_xml_table('SAVING_FUND_280082',
1095 f2d_tot.SAVING_FUND_280082,'TEXT');
1096 populate_xml_table('SAVING_FUND_280092',
1097 f2d_tot.SAVING_FUND_280092,'TEXT');
1098 populate_xml_table('AID_FOR_PANTRY_AND_FOOD_280053',
1099 f2d_tot.AID_FOR_PANTRY_AND_FOOD_280053,'TEXT');
1100 populate_xml_table('AID_FOR_PANTRY_AND_FOOD_280063',
1101 f2d_tot.AID_FOR_PANTRY_AND_FOOD_280063,'TEXT');
1102 populate_xml_table('AID_FOR_PANTRY_AND_FOOD_280073',
1103 f2d_tot.AID_FOR_PANTRY_AND_FOOD_280073,'TEXT');
1104 populate_xml_table('AID_FOR_PANTRY_AND_FOOD_280083',
1105 f2d_tot.AID_FOR_PANTRY_AND_FOOD_280083,'TEXT');
1106 populate_xml_table('AID_FOR_PANTRY_AND_FOOD_280093',
1107 f2d_tot.AID_FOR_PANTRY_AND_FOOD_280093,'TEXT');
1108 populate_xml_table('TRANSPORTATION_AID_280054',
1109 f2d_tot.TRANSPORTATION_AID_280054,'TEXT');
1110 populate_xml_table('TRANSPORTATION_AID_280064',
1111 f2d_tot.TRANSPORTATION_AID_280064,'TEXT');
1112 populate_xml_table('TRANSPORTATION_AID_280074',
1113 f2d_tot.TRANSPORTATION_AID_280074,'TEXT');
1114 populate_xml_table('TRANSPORTATION_AID_280084',
1115 f2d_tot.TRANSPORTATION_AID_280084,'TEXT');
1116 populate_xml_table('TRANSPORTATION_AID_280094',
1117 f2d_tot.TRANSPORTATION_AID_280094,'TEXT');
1118 populate_xml_table('OTHER_EARNINGS_280055',
1119 f2d_tot.OTHER_EARNINGS_280055,'TEXT');
1120 populate_xml_table('OTHER_EARNINGS_280065',
1121 f2d_tot.OTHER_EARNINGS_280065,'TEXT');
1122 populate_xml_table('OTHER_EARNINGS_280075',
1123 f2d_tot.OTHER_EARNINGS_280075,'TEXT');
1124 populate_xml_table('OTHER_EARNINGS_280085',
1125 f2d_tot.OTHER_EARNINGS_280085,'TEXT');
1126 populate_xml_table('OTHER_EARNINGS_280095',
1127 f2d_tot.OTHER_EARNINGS_280095,'TEXT');
1128 populate_xml_table('TOTAL_EARNINGS_280056',
1129 f2d_tot.TOTAL_EARNINGS_280056,'TEXT');
1130 populate_xml_table('TOTAL_EARNINGS_280066',
1131 f2d_tot.TOTAL_EARNINGS_280066,'TEXT');
1132 populate_xml_table('TOTAL_EARNINGS_280076',
1133 f2d_tot.TOTAL_EARNINGS_280076,'TEXT');
1134 populate_xml_table('TOTAL_EARNINGS_280086',
1135 f2d_tot.TOTAL_EARNINGS_280086,'TEXT');
1136 populate_xml_table('TOTAL_EARNINGS_280096',
1137 f2d_tot.TOTAL_EARNINGS_280096,'TEXT');
1138
1139 load_xml_internal('CS','FORMAT_2D_TOTAL',NULL);
1140
1141 FOR i IN format2d_xml_tbl.FIRST..format2d_xml_tbl.LAST LOOP
1142
1143 load_xml_internal('D',format2d_xml_tbl(i).name,format2d_xml_tbl(i).value);
1144
1145 END LOOP;
1146
1147 load_xml_internal('CE','FORMAT_2D_TOTAL',NULL);
1148
1149 lv_buf := '</' ||
1150 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
1151 2);
1152
1153 write_to_magtape_lob (lv_buf);
1154
1155 DELETE FROM pay_us_rpt_totals
1156 WHERE tax_unit_id = ln_payroll_action_id;
1157
1158
1159 hr_utility_trace ('Leaving '||l_proc_name);
1160 END generate_xml_footer;
1161
1162 BEGIN
1163 --hr_utility.trace_on(null, 'PAYMX2D');
1164 g_proc_name := 'PAY_MX_FORMAT_2D.';
1165 g_debug := hr_utility.debug_enabled;
1166 g_document_type := 'MX_FORMAT2D_MAG';
1167 END PAY_MX_FORMAT_2D;