[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_DIM_MAG
Source
1 PACKAGE BODY PAY_MX_DIM_MAG AS
2 /* $Header: paymxdimmag.pkb 120.4 2008/04/17 11:12:30 swamukhe noship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : pay_mx_dim_mag
9 Package File Name : paymxdimmag.pkb
10
11 Description : Used for DIM Interface Extract
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ----------------------------------
18 vpandya 28-Aug-2006 115.0 Initial Version
19 vpandya 07-Sep-2006 115.1 Changed generate_xml:
20 Print 0,1 or 2 for Union Worker
21 flag. Removed EMPLOYEE tag.
22 vpandya 15-Sep-2006 115.2 Changed generate_xml:
23 Added Subsidy Proportion Used.
24 Removed upper from names as it
25 should be printed as it is.
26 vpandya 26-Sep-2006 115.3 5564163 Changed generate_xml:
27 Using RATE_1991_IND and
28 RATE_FISCAL_YEAR_IND from view.
29 Removed logic to get these
30 indicator from this package.
31 nragavar 31-Oct_2006 115.5 5581574 modified to return total earnings
32 subject/exempt properly.
33 vmehta 13-feb-2007 115.6 modified range_cursor to use
34 to_number around serial_number.
35 nragavar 11-Sep-2007 115.7 5916021 Modified to display field 114
36 correctly.
37 nragavar 12-Sep-2007 115.8 Missed out changes fro ISR Calculated
38 nragavar 14-Sep-2007 115.9 6415826 modified to display EMPR_STOCK_OPTION_PLAN
39 correctly
40 ==========================================================================*/
41
42 --
43 -- Global Variables
44 --
45
46
47 dim_xml_tbl xml_tbl;
48
49 g_proc_name VARCHAR2(240);
50 g_debug BOOLEAN;
51 g_document_type VARCHAR2(50);
52 gd_effective_date DATE;
53 gn_business_group_id NUMBER;
54 gn_legal_er_id NUMBER;
55
56 gn_success_fail NUMBER;
57 gn_sep_bal NUMBER;
58 gn_ass_bal NUMBER;
59 gn_emp_bal NUMBER;
60
61 EOL VARCHAR2(5);
62
63 /****************************************************************************
64 Name : HR_UTILITY_TRACE
65 Description : This procedure prints debug messages.
66 *****************************************************************************/
67 PROCEDURE hr_utility_trace ( P_TRC_DATA VARCHAR2) AS
68 BEGIN
69 IF g_debug THEN
70 hr_utility.trace(p_trc_data);
71 END IF;
72 END hr_utility_trace;
73
74
75 /****************************************************************************
76 Name : PRINT_BLOB
77 Description : This procedure prints contents of BLOB passed as parameter.
78 *****************************************************************************/
79
80 PROCEDURE print_blob(p_blob BLOB) IS
81 BEGIN
82 IF g_debug THEN
83 pay_ac_utility.print_lob(p_blob);
84 END IF;
85 END print_blob;
86
87
88 /****************************************************************************
89 Name : WRITE_TO_MAGTAPE_LOB
90 Description : This procedure appends passed BLOB parameter to
91 pay_mag_tape.g_blob_value
92 *****************************************************************************/
93
94 PROCEDURE write_to_magtape_lob(p_blob BLOB) IS
95 BEGIN
96 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
97 pay_core_files.write_to_magtape_lob (p_blob);
98 END IF;
99 END write_to_magtape_lob;
100
101
102 /****************************************************************************
103 Name : WRITE_TO_MAGTAPE_LOB
104 Description : This procedure appends passed varchar2 parameter to
105 pay_mag_tape.g_blob_value
106 *****************************************************************************/
107
108 PROCEDURE write_to_magtape_lob(p_data VARCHAR2) IS
109 BEGIN
110 pay_core_files.write_to_magtape_lob (p_data);
111 END write_to_magtape_lob;
112
113
114 /****************************************************************************
115 Name : POPULATE_XML_TABLE
116 Description : This procedure creates a table that uses for XML creation.
117 *****************************************************************************/
118 PROCEDURE populate_xml_table( name IN VARCHAR2
119 ,value IN VARCHAR2
120 ,type IN VARCHAR2 ) IS
121 ln_index NUMBER;
122
123 BEGIN
124
125 IF type = 'SEP_BAL' THEN
126
127 IF value < 0 THEN
128 gn_success_fail := -1;
129 ELSIF value > 0 THEN
130 gn_sep_bal := 1;
131 END IF;
132
133 ELSIF type = 'ASS_BAL' THEN
134
135 IF value < 0 THEN
136 gn_success_fail := -1;
137 ELSIF value > 0 THEN
138 gn_ass_bal := 1;
139 END IF;
140
141 ELSIF type = 'EMP_BAL' THEN
142
143 IF value < 0 THEN
144 gn_success_fail := -1;
145 ELSIF value > 0 THEN
146 gn_emp_bal := 1;
147 END IF;
148
149 ELSIF type = 'SUMM_BAL' THEN
150
151 IF value < 0 THEN
152 gn_success_fail := -1;
153 END IF;
154
155 END IF;
156
157 ln_index := dim_xml_tbl.COUNT;
158
159 dim_xml_tbl(ln_index).name := name;
160 dim_xml_tbl(ln_index).value := value;
161
162 END populate_xml_table;
163
164 /****************************************************************************
165 Name : LOAD_XML_INTERNAL
166 Description : This procedure loads the global XML cache.
167 Parameters : P_NODE_TYPE This parameter can take one of these
168 values: -
169 1. CS - This signifies that string contained
170 in P_NODE parameter is start of
171 container node. P_DATA parameter is
172 ignored in this mode.
173 2. CE - This signifies that string
174 contained in P_NODE parameter is
175 end of container node. P_DATA
176 parameter is ignored in this mode.
177 3. D - This signifies that string
178 contained in P_NODE parameter is
179 data node and P_DATA carries actual
180 data to be contained by tag
181 specified by P_NODE parameter.
182
183 P_NODE Name of XML tag, or, application column
184 name of flex segment.
185
186 P_DATA Data to be contained by tag specified by
187 P_NODE parameter. P_DATA is not used unless
188 P_NODE_TYPE = D.
189 *****************************************************************************/
190 PROCEDURE load_xml_internal ( p_node_type VARCHAR2
191 ,p_node VARCHAR2
192 ,p_data VARCHAR2) IS
193 l_proc_name VARCHAR2(100);
194 l_data VARCHAR2(240);
195 l_xml VARCHAR2(240);
196
197 BEGIN
198 l_proc_name := g_proc_name || 'LOAD_XML_INTERNAL';
199 hr_utility_trace ('Entering '||l_proc_name);
200
201 IF p_node_type = 'CS' THEN
202
203 l_xml := '<'||p_node||'>'||EOL;
204
205 ELSIF p_node_type = 'CE' THEN
206
207 l_xml := '</'||p_node||'>'||EOL;
208
209 ELSIF p_node_type = 'D' THEN
210
211 /* Handle special charaters in data */
212 l_data := REPLACE (p_data, '&', '&');
213 l_data := REPLACE (l_data, '>', '>');
214 l_data := REPLACE (l_data, '<', '<');
215 l_data := REPLACE (l_data, '''', ''');
216 l_data := REPLACE (l_data, '"', '"');
217 l_xml := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
218
219 END IF;
220
221 write_to_magtape_lob (l_xml);
222
223 hr_utility_trace ('Leaving '||l_proc_name);
224
225 END load_xml_internal;
226
227 /****************************************************************************
228 Name : GET_PAYROLL_ACTION_INFO
229 Description : This procedure fetches payroll action level information.
230 *****************************************************************************/
231 PROCEDURE get_payroll_action_info(p_payroll_action_id IN NUMBER
232 ,p_end_date OUT NOCOPY DATE
233 ,p_business_group_id OUT NOCOPY NUMBER
234 ,p_legal_employer_id OUT NOCOPY NUMBER
235 )
236 IS
237 CURSOR c_payroll_Action_info (cp_payroll_action_id IN NUMBER) IS
238 SELECT effective_date,
239 business_group_id,
240 pay_mx_utility.get_legi_param_val( 'LEGAL_EMPLOYER'
241 ,legislative_parameters)
242 FROM pay_payroll_actions
243 WHERE payroll_action_id = cp_payroll_action_id;
244
245 ld_end_date DATE;
246 ln_business_group_id NUMBER;
247 ln_asg_set_id NUMBER;
248 ln_legal_er_id NUMBER;
249 lv_procedure_name VARCHAR2(100);
250
251 lv_error_message VARCHAR2(200);
252 ln_step NUMBER;
253
254 BEGIN
255
256 lv_procedure_name := g_proc_name ||'.get_payroll_action_info';
257
258 hr_utility.set_location(lv_procedure_name, 10);
259
260 ln_step := 1;
261
262 OPEN c_payroll_action_info(p_payroll_action_id);
263 FETCH c_payroll_action_info INTO ld_end_date
264 ,ln_business_group_id
265 ,ln_legal_er_id;
266 CLOSE c_payroll_action_info;
267
268 ln_step := 2;
269 hr_utility.set_location(lv_procedure_name, 30);
270
271 p_end_date := TRUNC(ld_end_date,'Y');
272 p_business_group_id := ln_business_group_id;
273 p_legal_employer_id := ln_legal_er_id;
274
275 hr_utility.set_location(lv_procedure_name, 50);
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 lv_error_message := 'Error at step ' || ln_step || ' IN ' ||
280 lv_procedure_name;
281
282 hr_utility.trace(lv_error_message || '-' || SQLERRM);
283
284 lv_error_message :=
285 pay_emp_action_arch.set_error_message(lv_error_message);
286
287 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
288 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
289 hr_utility.raise_error;
290
291 END get_payroll_action_info;
292
293 /****************************************************************************
294 Name : RANGE_CURSOR
295 Description : This procedure prepares range of persons to be processed.
296 *****************************************************************************/
297 PROCEDURE range_cursor ( P_PAYROLL_ACTION_ID NUMBER
298 ,P_SQLSTR OUT NOCOPY VARCHAR2 ) AS
299
300 l_proc_name varchar2(100);
301
302 BEGIN
303 l_proc_name := g_proc_name || 'RANGE_CURSOR';
304
305 hr_utility_trace ('Entering '||l_proc_name);
306
307 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
308
309 get_payroll_action_info (p_payroll_action_id
310 ,gd_effective_date
311 ,gn_business_group_id
312 ,gn_legal_er_id);
313
314 hr_utility_trace ('gd_effective_date = '|| gd_effective_date);
315 hr_utility_trace ('gn_business_group_id = '|| gn_business_group_id);
316 hr_utility_trace ('gn_legal_er_id = '|| gn_legal_er_id);
317
318 p_sqlstr := '
319 SELECT DISTINCT to_number(paa_arch.serial_number)
320 FROM pay_assignment_actions paa_arch
321 ,pay_payroll_actions ppa_arch
322 WHERE ppa_arch.business_group_id = '|| gn_business_group_id ||'
323 AND ppa_arch.report_type = ''MX_YREND_ARCHIVE''
324 AND ppa_arch.report_qualifier = ''MX''
325 AND ppa_arch.report_category = ''ARCHIVE''
326 AND pay_mx_utility.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',
327 ppa_arch.legislative_parameters) = '||gn_legal_er_id||'
328 AND TRUNC(ppa_arch.effective_date,''Y'') =
329 fnd_date.canonical_to_date('''||
330 fnd_date.date_to_canonical(gd_effective_date)||''')
331 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
332 AND paa_arch.action_status = ''C''
333 AND :p_payroll_action_id = '||p_payroll_action_id||'
334 ORDER BY 1';
335
336 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
337 hr_utility_trace ('Leaving '||l_proc_name);
338
339 END range_cursor;
340
341
342 /****************************************************************************
343 Name : ACTION_CREATION
344 Description : This procedure creates assignment actions for DIM magnetic
345 tape process.
346 *****************************************************************************/
347 PROCEDURE action_creation ( p_payroll_action_id NUMBER,
348 p_start_person_id NUMBER,
349 p_end_person_id NUMBER,
350 p_chunk NUMBER) AS
351
352 CURSOR c_arch_asg ( cp_business_group_id NUMBER
353 ,cp_legal_er_id NUMBER
354 ,cp_effective_date DATE
355 ,cp_start_person_id NUMBER
356 ,cp_end_person_id NUMBER) IS
357 SELECT paa_arch.assignment_action_id
358 ,paa_arch.assignment_id
359 ,paa_arch.serial_number person_id
360 ,ppa_arch.payroll_action_id
361 FROM pay_assignment_actions paa_arch,
362 pay_payroll_actions ppa_arch
363 WHERE ppa_arch.business_group_id = cp_business_group_id
364 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
365 AND ppa_arch.report_qualifier = 'MX'
366 AND ppa_arch.report_category = 'ARCHIVE'
367 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
368 ppa_arch.legislative_parameters) = cp_legal_er_id
369 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
370 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
371 AND paa_arch.action_status = 'C'
372 AND paa_arch.serial_number BETWEEN cp_start_person_id
373 AND cp_end_person_id
374 ORDER BY paa_arch.serial_number,
375 paa_arch.assignment_id;
376
377 CURSOR c_arch_asg_range ( cp_business_group_id NUMBER
378 ,cp_legal_er_id NUMBER
379 ,cp_effective_date DATE
380 ,cp_chunk NUMBER
381 ,cp_payroll_action_id NUMBER) IS
382 SELECT paa_arch.assignment_action_id
383 ,paa_arch.assignment_id
384 ,paa_arch.serial_number person_id
385 ,ppa_arch.payroll_action_id
386 FROM pay_assignment_actions paa_arch,
387 pay_payroll_actions ppa_arch,
388 pay_population_ranges ppr
389 WHERE ppa_arch.business_group_id = cp_business_group_id
390 AND ppa_arch.report_type = 'MX_YREND_ARCHIVE'
391 AND ppa_arch.report_qualifier = 'MX'
392 AND ppa_arch.report_category = 'ARCHIVE'
393 AND pay_mx_utility.get_parameter('TRANSFER_LEGAL_EMPLOYER',
394 ppa_arch.legislative_parameters) = cp_legal_er_id
395 AND TRUNC(ppa_arch.effective_date,'Y') = TRUNC(cp_effective_date,'Y')
396 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
397 AND paa_arch.action_status = 'C'
398 AND paa_arch.serial_number = ppr.person_id
399 AND ppr.chunk_number = cp_chunk
400 AND ppr.payroll_action_id = cp_payroll_action_id
401 ORDER BY paa_arch.serial_number,
402 paa_arch.assignment_id;
403
404 l_proc_name varchar2(100);
405 lv_future_magtape_exists varchar2(1);
406 lb_range_person_on boolean;
407 ln_person_id number;
408 ln_prev_arch_pact_id number;
409 ln_arch_pact_id number;
410 ln_prev_person_id number;
411 ln_prev_asg_id number;
412 ln_mag_asg_act_id number;
413 ln_assignment_id number;
414 ln_arch_act_id number;
415 ln_asg_count number;
416 BEGIN
417 l_proc_name := g_proc_name || 'ACTION_CREATION';
418 hr_utility_trace ('Entering '||l_proc_name);
419 hr_utility_trace ('Parameters ....');
420 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
421 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
422 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
423 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
424
425 ln_prev_person_id := -1;
426 ln_prev_asg_id := -1;
427 ln_prev_arch_pact_id := -1;
428
429 IF gn_legal_er_id IS NULL THEN
430
431 get_payroll_action_info (p_payroll_action_id
432 ,gd_effective_date
433 ,gn_business_group_id
434 ,gn_legal_er_id);
435
436 END IF;
437
438 ln_asg_count := 0;
439
440 lb_range_person_on := pay_ac_utility.range_person_on(
441 p_report_type => 'DIM_MAG'
442 ,p_report_format => 'DIM_MAG'
443 ,p_report_qualifier => 'DIM_MAG'
444 ,p_report_category => 'RT');
445
446 IF lb_range_person_on THEN
447
448 hr_utility_trace ('Person ranges are ON');
449
450 OPEN c_arch_asg_range( gn_business_group_id
451 ,gn_legal_er_id
452 ,gd_effective_date
453 ,p_chunk
454 ,p_payroll_action_id);
455
456 ELSE
457
458 hr_utility_trace ('Person ranges are OFF');
459
460 OPEN c_arch_asg( gn_business_group_id
461 ,gn_legal_er_id
462 ,gd_effective_date
463 ,p_start_person_id
464 ,p_end_person_id);
465
466 END IF;
467
468 LOOP
469 IF lb_range_person_on THEN
470 FETCH c_arch_asg_range INTO ln_arch_act_id,
471 ln_assignment_id,
472 ln_person_id,
473 ln_arch_pact_id;
474 EXIT WHEN c_arch_asg_range%NOTFOUND;
475 ELSE
476 FETCH c_arch_asg INTO ln_arch_act_id,
477 ln_assignment_id,
478 ln_person_id,
479 ln_arch_pact_id;
480 EXIT WHEN c_arch_asg%NOTFOUND;
481 END IF;
482
483 ln_asg_count := ln_asg_count + 1;
484
485 hr_utility_trace ('-------------');
486 hr_utility_trace('Current archiver asg action = '||ln_arch_act_id);
487 hr_utility_trace('Current person = '||ln_person_id);
488 hr_utility_trace('Previous person = '||ln_prev_person_id);
489
490 IF (ln_person_id <> ln_prev_person_id) THEN
491
492 SELECT pay_assignment_actions_s.nextval
493 INTO ln_mag_asg_act_id
494 FROM dual;
495
496 hr_utility_trace('Creating magtape assignment action '||
497 ln_mag_asg_act_id);
498
499 hr_nonrun_asact.insact(ln_mag_asg_act_id
500 ,ln_assignment_id
501 ,p_payroll_action_id
502 ,p_chunk
503 ,gn_legal_er_id
504 ,null
505 ,'U'
506 ,null);
507
508 -- insert an interlock to this action
509 hr_utility.trace('Locking Action in IF = ' || ln_mag_asg_act_id);
510 hr_utility.trace('Locked Action in IF = ' || ln_arch_act_id);
511
512 hr_nonrun_asact.insint(ln_mag_asg_act_id,
513 ln_arch_act_id);
514
515 ELSE
516
517 -- insert an interlock to this action
518 hr_utility.trace('Locking Action in ELSE = ' || ln_mag_asg_act_id);
519 hr_utility.trace('Locked Action in ELSE = ' || ln_arch_act_id);
520
521 hr_nonrun_asact.insint(ln_mag_asg_act_id,
522 ln_arch_act_id);
523
524 END IF;
525
526 ln_prev_person_id := ln_person_id;
527
528 END LOOP;
529
530 hr_utility_trace(ln_asg_count || ' archiver actions processed in chunk '||
531 p_chunk);
532
533 IF lb_range_person_on THEN
534 CLOSE c_arch_asg_range;
535 ELSE
536 CLOSE c_arch_asg;
537 END IF;
538
539 hr_utility_trace ('Leaving '||l_proc_name);
540
541 END action_creation;
542
543 /****************************************************************************
544 Name : GENERATE_XML
545 Description : This procedure fetches archived data, converts it to XML
546 format and appends to pay_mag_tape.g_blob_value.
547 *****************************************************************************/
548 PROCEDURE generate_xml AS
549
550 CURSOR c_dim_rec (cp_assignment_action_id number,p_format varchar2) IS
551 SELECT dim.person_id PERSON_ID
552 ,to_char(fnd_date.canonical_to_date(start_month),'mm') START_MONTH
553 ,to_char(fnd_date.canonical_to_date(end_month),'mm') END_MONTH
554 ,replace(RFC_ID,'-','') RFC_ID
555 ,CURP
556 ,ltrim(rtrim(PATERNAL_LAST_NAME)) PATERNAL_LAST_NAME
557 ,ltrim(rtrim(MATERNAL_LAST_NAME)) MATERNAL_LAST_NAME
558 ,ltrim(rtrim(NAMES)) NAMES
559 ,decode(ECONOMIC_ZONE, 'A', '01',
560 'B', '02', 'C', '03', '0') ECONOMIC_ZONE
561 ,decode(ANNUAL_TAX_CALC_FLAG, 'Y', '1',
562 'N', '2' , '0') ANNUAL_TAX_CALC_FLAG
563 ,RATE_FISCAL_YEAR_IND
564 ,RATE_1991_IND
565 ,to_char(FND_NUMBER.canonical_to_number(nvl(TAX_SUBSIDY_PCT,'0')), p_format)
566 TAX_SUBSIDY_PCT
567 ,decode(UNION_WORKER_FLAG, 'Y', 1, 'N', 2, 0) UNION_WORKER_FLAG
568 ,0 ASSIMILATED_TO_SALARY_IND
569 ,STATE_ID
570 ,replace(OTHER_ER_RFC1,'-','') OTHER_ER_RFC1
571 ,replace(OTHER_ER_RFC2,'-','') OTHER_ER_RFC2
572 ,replace(OTHER_ER_RFC3,'-','') OTHER_ER_RFC3
573 ,replace(OTHER_ER_RFC4,'-','') OTHER_ER_RFC4
574 ,replace(OTHER_ER_RFC5,'-','') OTHER_ER_RFC5
575 ,replace(OTHER_ER_RFC6,'-','') OTHER_ER_RFC6
576 ,replace(OTHER_ER_RFC7,'-','') OTHER_ER_RFC7
577 ,replace(OTHER_ER_RFC8,'-','') OTHER_ER_RFC8
578 ,replace(OTHER_ER_RFC9,'-','') OTHER_ER_RFC9
579 ,replace(OTHER_ER_RFC10,'-','') OTHER_ER_RFC10
580 ,0 SEP_EARNINGS
581 ,0 ASSIMILATED_SALARIES
582 ,0 ER_PAYMENT_TO_EE
583 ,RET_EARNINGS_IN_PART_PYMNT
584 ,RET_DAILY_EARNINGS_IN_PYMNT
585 ,RET_PERIOD_EARNINGS
586 ,RET_EARNINGS_IN_ONE_PYMNT
587 ,RET_EARNINGS_DAYS
588 ,RET_EXEMPT_EARNINGS
589 ,RET_TAXABLE_EARNINGS
590 ,RET_CUMULATIVE_EARNINGS
591 ,RET_NON_CUMULATIVE_EARNINGS
592 ,ISR_WITHHELD_FOR_RET_EARNINGS
593 ,AMENDS
594 ,NVL(SENIORITY,0) SENIORITY
595 ,ISR_EXEMPT_FOR_AMENDS
596 ,ISR_SUBJECT_FOR_AMENDS
597 ,LAST_MTH_ORD_SAL
598 ,LAST_MTH_ORD_SAL_WITHHELD
599 ,NON_CUMULATIVE_AMENDS
600 ,ISR_WITHHELD_FOR_AMENDS
601 ,ASSIMILATED_EARNINGS
602 ,ISR_WITHHELD_FOR_ASSI_EARNINGS
603 ,decode(STK_OPTIONS_VESTING_VALUE,0,0,1) EMPR_STOCK_OPTION_PLAN
604 ,STK_OPTIONS_VESTING_VALUE
605 ,STK_OPTIONS_GRANT_PRICE
606 ,STK_OPTIONS_CUML_INCOME
607 ,STK_OPTIONS_TAX_WITHHELD
608 ,ISR_SUBJECT_FOR_FIXED_EARNINGS
609 ,ISR_EXEMPT_FOR_FIXED_EARNINGS
610 ,ISR_SUBJECT_FOR_XMAS_BONUS
611 ,ISR_EXEMPT_FOR_XMAS_BONUS
612 ,ISR_SUBJECT_FOR_TRAVEL_EXP
613 ,ISR_EXEMPT_FOR_TRAVEL_EXP
614 ,ISR_SUBJECT_FOR_OVERTIME
615 ,ISR_EXEMPT_FOR_OVERTIME
616 ,ISR_SUBJECT_FOR_VAC_PREMIUM
617 ,ISR_EXEMPT_FOR_VAC_PREMIUM
618 ,ISR_SUBJECT_FOR_DOM_PREMIUM
619 ,ISR_EXEMPT_FOR_DOM_PREMIUM
620 ,ISR_SUBJECT_FOR_PROFIT_SHARING
621 ,ISR_EXEMPT_FOR_PROFIT_SHARING
622 ,ISR_SUBJECT_FOR_HEALTHCARE_REI
623 ,ISR_EXEMPT_FOR_HEALTHCARE_REI
624 ,ISR_SUBJECT_FOR_SAVINGS_FUND
625 ,ISR_EXEMPT_FOR_SAVINGS_FUND
626 ,ISR_SUBJECT_FOR_SAVINGS_BOX
627 ,ISR_EXEMPT_FOR_SAVINGS_BOX
628 ,ISR_SUBJECT_FOR_PANTRY_COUPONS
629 ,ISR_EXEMPT_FOR_PANTRY_COUPONS
630 ,ISR_SUBJECT_FOR_FUNERAL_AID
631 ,ISR_EXEMPT_FOR_FUNERAL_AID
632 ,ISR_SUBJECT_FOR_WR_PD_BY_ER
633 ,ISR_EXEMPT_FOR_WR_PD_BY_ER
634 ,ISR_SUBJECT_FOR_PUN_INCENTIVE
635 ,ISR_EXEMPT_FOR_PUN_INCENTIVE
636 ,ISR_SUBJECT_FOR_LIFE_INS_PRE
637 ,ISR_EXEMPT_FOR_LIFE_INS_PRE
638 ,ISR_SUBJECT_FOR_MAJOR_MED_INS
639 ,ISR_EXEMPT_FOR_MAJOR_MED_INS
640 ,ISR_SUBJECT_FOR_REST_COUPONS
641 ,ISR_EXEMPT_FOR_REST_COUPONS
642 ,ISR_SUBJECT_FOR_GAS_COUPONS
643 ,ISR_EXEMPT_FOR_GAS_COUPONS
644 ,ISR_SUBJECT_FOR_UNI_COUPONS
645 ,ISR_EXEMPT_FOR_UNI_COUPONS
646 ,ISR_SUBJECT_FOR_RENTAL_AID
647 ,ISR_EXEMPT_FOR_RENTAL_AID
648 ,ISR_SUBJECT_FOR_EDU_AID
649 ,ISR_EXEMPT_FOR_EDU_AID
650 ,ISR_SUBJECT_FOR_GLASSES_AID
651 ,ISR_EXEMPT_FOR_GLASSES_AID
652 ,ISR_SUBJECT_FOR_TRANS_AID
653 ,ISR_EXEMPT_FOR_TRANS_AID
654 ,ISR_SUBJECT_FOR_UNION_PD_BY_ER
655 ,ISR_EXEMPT_FOR_UNION_PD_BY_ER
656 ,ISR_SUBJECT_FOR_DISAB_SUBSIDY
657 ,ISR_EXEMPT_FOR_DISAB_SUBSIDY
658 ,ISR_SUBJECT_FOR_CHILD_SCHOLAR
659 ,ISR_EXEMPT_FOR_CHILD_SCHOLAR
660 ,NVL(PREV_ER_EARNINGS,0) PREV_ER_EARNINGS
661 ,NVL(PREV_ER_EXEMPT_EARNINGS,0) PREV_ER_EXEMPT_EARNINGS
662 ,ISR_SUBJECT_OTHER_INCOME
663 ,ISR_EXEMPT_OTHER_INCOME
664 ,TOTAL_SUBJECT_EARNINGS
665 ,TOTAL_EXEMPT_EARNINGS
666 ,TAX_WITHHELD_IN_FISCAL_YEAR
667 ,NVL(PREV_ER_ISR_WITHHELD,0) PREV_ER_ISR_WITHHELD
668 --,CURRENT_FY_ARREARS
669 ,decode( sign (decode( ANNUAL_TAX_CALC_FLAG , 'Y',NVL(CURRENT_FY_ARREARS,0), 0))
670 ,-1,(decode( ANNUAL_TAX_CALC_FLAG , 'Y',NVL(CURRENT_FY_ARREARS,0), 0))* -1,0)
671 CURRENT_FY_ARREARS
672 ,PREV_FY_ARREARS
673 ,CREDIT_TO_SALARY
674 ,CREDIT_TO_SALARY_PAID
675 ,SOCIAL_FORESIGHT_EARNINGS
676 ,ISR_EXEMPT_FOR_SOC_FORESIGHT
677 ,nvl(TOTAL_SUBJECT_EARNINGS,0)+nvl(TOTAL_EXEMPT_EARNINGS,0) SUM_SAL_WAGES_EARNINGS
678 ,EMPLOYEE_STATE_TAX_WITHHELD LOCAL_TAX_AMT_EARN_SAL_WAGES
679 ,0 AMT_SUBSIDY_EMPT_IN_FY
680 ,0 AMT_SUBSIDY_INCOME_PAID_EMP_FY
681 ,decode(ANNUAL_TAX_CALC_FLAG,'Y',ISR_CALCULATED,0) ISR_CALCULATED
682 ,ISR_CREDITABLE_SUBSIDY
683 ,ISR_NON_CREDITABLE_SUBSIDY
684 ,ISR_ON_CUMULATIVE_EARNINGS
685 ,ISR_ON_NON_CUMULATIVE_EARNINGS
686 ,0 ISR_SUBSIDY_EMPT_PAID_TO_EMP
687 ,0 ISR_SUBSIDY_INC_PAID_EMP
688 ,trunc(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0')),0) TAX_SUBSIDY_PCT_I
689 ,rpad(replace(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0'))-
690 trunc(FND_NUMBER.canonical_to_number(NVL(tax_subsidy_pct,'0')),0),'.',''),4,0)
691 TAX_SUBSIDY_PCT_D
692 ,to_char(FND_NUMBER.canonical_to_number(nvl(SUBSIDY_PORTION_APPLIED,'0')),p_format)
693 SUBSIDY_PORTION_APPLIED
694 ,trunc(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0')),0)
695 SUBSIDY_PORTION_APPLIED_I
696 ,rpad(replace(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0'))-
697 trunc(FND_NUMBER.canonical_to_number(NVL(subsidy_portion_applied,'0')),0),'.',''),4,0)
698 SUBSIDY_PORTION_APPLIED_D
699 ,TOT_EARNING_ASSI_CONCEPTS
700 ,EMPLOYEE_STATE_TAX_WITHHELD
701 ,TOT_EXEMPT_EARNINGS
702 ,TOT_NON_CUMULATIVE_EARNINGS
703 ,TOT_CUMULATIVE_EARNINGS
704 ,CREDITABLE_SUBSIDY_FRACTIONIII
705 ,CREDITABLE_SUBSIDY_FRACTIONIV
706 ,TAX_ON_INCOME_FISCAL_YEAR
707 ,ISR_TAX_WITHHELD
708 ,(nvl(TOTAL_SUBJECT_EARNINGS,0) + nvl(TOTAL_EXEMPT_EARNINGS,0)) TOTAL_EARNINGS
709 ,replace(ER_RFC_ID,'-','') ER_RFC_ID
710 ,UPPER(ER_LEGAL_NAME) ER_LEGAL_NAME
711 ,UPPER(ER_LEGAL_REP_NAMES) ER_LEGAL_REP_NAMES
712 ,replace(ER_LEGAL_REP_RFC_ID,'-','') ER_LEGAL_REP_RFC_ID
713 ,ER_LEGAL_REP_CURP
714 ,NVL(ER_TAX_SUBSIDY_PCT,'0') ER_TAX_SUBSIDY_PCT
715 ,FISCAL_YEAR_REPORTING
716 ,ltrim(rtrim(PATERNAL_LAST_NAME)) ||' '
717 ||ltrim(rtrim(MATERNAL_LAST_NAME)) ||' '
718 ||ltrim(rtrim(NAMES)) FULL_NAME
719 FROM pay_mx_isr_tax_format37_v dim
720 ,pay_assignment_actions paa
721 ,pay_action_interlocks pai
722 WHERE dim.payroll_action_id = paa.payroll_action_id
723 AND dim.person_id = to_number(paa.serial_number)
724 AND paa.assignment_action_id = pai.locked_action_id
725 AND pai.locking_action_id = cp_assignment_action_id
726 ORDER BY effective_date DESC;
727
728 l_proc_name varchar2(100);
729 l_xml BLOB;
730 lb_person_processed boolean;
731
732 ln_assignment_action_id NUMBER;
733 ln_person_id NUMBER;
734 ln_business_group_id NUMBER;
735 ld_effective_date DATE;
736 lv_ann_tax_calc_type VARCHAR2(240);
737 ln_anntaxadj_asgactid NUMBER;
738 ln_input_value_id NUMBER;
739 lv_anntaxadj_article VARCHAR2(240);
740 p_format VARCHAR2(60);
741 decimal_char VARCHAR2(3);
742
743 dim c_dim_rec%ROWTYPE;
744 prev_dim c_dim_rec%ROWTYPE;
745
746 BEGIN
747
748 l_proc_name := g_proc_name || 'GENERATE_XML';
749 hr_utility_trace ('Entering '||l_proc_name);
750
751 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
752 ('TRANSFER_ACT_ID');
753
754 hr_utility_trace ('Fetching transactions for magtape asg action '||
755 ln_assignment_action_id);
756 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
757 p_format := '0'||decimal_char||'9999';
758 hr_utility_trace('decimal_char '||decimal_char);
759 hr_utility_trace('p_format '||p_format);
760 dim_xml_tbl.DELETE;
761
762 gn_success_fail := 0;
763 gn_sep_bal := 0;
764 gn_ass_bal := 0;
765 gn_emp_bal := 0;
766
767 ln_person_id := -1;
768
769 SELECT fnd_global.local_chr(13) || fnd_global.local_chr(10)
770 INTO EOL
771 FROM dual;
772
773
774 OPEN c_dim_rec(ln_assignment_action_id,p_format);
775
776 LOOP
777
778 FETCH c_dim_rec INTO dim;
779 EXIT WHEN c_dim_rec%NOTFOUND;
780
781 IF ln_person_id = -1 THEN
782 prev_dim := dim;
783 ELSE
784 prev_dim.start_month := dim.start_month;
785 prev_dim.RET_EARNINGS_IN_PART_PYMNT :=
786 prev_dim.RET_EARNINGS_IN_PART_PYMNT +
787 dim.RET_EARNINGS_IN_PART_PYMNT;
788 prev_dim.RET_DAILY_EARNINGS_IN_PYMNT :=
789 prev_dim.RET_DAILY_EARNINGS_IN_PYMNT+
790 dim.RET_DAILY_EARNINGS_IN_PYMNT;
791 prev_dim.RET_PERIOD_EARNINGS := prev_dim.RET_PERIOD_EARNINGS +
792 dim.RET_PERIOD_EARNINGS;
793 prev_dim.RET_EARNINGS_IN_ONE_PYMNT :=
794 prev_dim.RET_EARNINGS_IN_ONE_PYMNT+
795 dim.RET_EARNINGS_IN_ONE_PYMNT;
796 prev_dim.RET_EARNINGS_DAYS := prev_dim.RET_EARNINGS_DAYS +
797 dim.RET_EARNINGS_DAYS;
798 prev_dim.RET_EXEMPT_EARNINGS := prev_dim.RET_EXEMPT_EARNINGS +
799 dim.RET_EXEMPT_EARNINGS;
800 prev_dim.RET_TAXABLE_EARNINGS := prev_dim.RET_TAXABLE_EARNINGS +
801 dim.RET_TAXABLE_EARNINGS;
802 prev_dim.RET_CUMULATIVE_EARNINGS := prev_dim.RET_CUMULATIVE_EARNINGS +
803 dim.RET_CUMULATIVE_EARNINGS;
804 prev_dim.RET_NON_CUMULATIVE_EARNINGS :=
805 prev_dim.RET_NON_CUMULATIVE_EARNINGS +
806 dim.RET_NON_CUMULATIVE_EARNINGS;
807 prev_dim.ISR_WITHHELD_FOR_RET_EARNINGS :=
808 prev_dim.ISR_WITHHELD_FOR_RET_EARNINGS +
809 dim.ISR_WITHHELD_FOR_RET_EARNINGS;
810 prev_dim.AMENDS := prev_dim.AMENDS + prev_dim.AMENDS;
811 prev_dim.SENIORITY := prev_dim.SENIORITY + dim.SENIORITY;
812 prev_dim.ISR_EXEMPT_FOR_AMENDS := prev_dim.ISR_EXEMPT_FOR_AMENDS +
813 dim.ISR_EXEMPT_FOR_AMENDS;
814 prev_dim.ISR_SUBJECT_FOR_AMENDS := prev_dim.ISR_SUBJECT_FOR_AMENDS +
815 dim.ISR_SUBJECT_FOR_AMENDS;
816 prev_dim.LAST_MTH_ORD_SAL := prev_dim.LAST_MTH_ORD_SAL +
817 dim.LAST_MTH_ORD_SAL;
818 prev_dim.LAST_MTH_ORD_SAL_WITHHELD :=
819 prev_dim.LAST_MTH_ORD_SAL_WITHHELD +
820 dim.LAST_MTH_ORD_SAL_WITHHELD;
821 prev_dim.NON_CUMULATIVE_AMENDS := prev_dim.NON_CUMULATIVE_AMENDS +
822 dim.NON_CUMULATIVE_AMENDS;
823 prev_dim.ISR_WITHHELD_FOR_AMENDS := prev_dim.ISR_WITHHELD_FOR_AMENDS +
824 dim.ISR_WITHHELD_FOR_AMENDS;
825 prev_dim.ASSIMILATED_EARNINGS := prev_dim.ASSIMILATED_EARNINGS +
826 dim.ASSIMILATED_EARNINGS;
827 prev_dim.ISR_WITHHELD_FOR_ASSI_EARNINGS :=
828 prev_dim.ISR_WITHHELD_FOR_ASSI_EARNINGS +
829 dim.ISR_WITHHELD_FOR_ASSI_EARNINGS;
830 if dim.STK_OPTIONS_VESTING_VALUE <> 0 then
831 prev_dim.EMPR_STOCK_OPTION_PLAN := 1;
832 End if;
833 Prev_dim.STK_OPTIONS_VESTING_VALUE := Prev_dim.STK_OPTIONS_VESTING_VALUE +
834 dim.STK_OPTIONS_VESTING_VALUE;
835 prev_dim.STK_OPTIONS_GRANT_PRICE := prev_dim.STK_OPTIONS_GRANT_PRICE +
836 dim.STK_OPTIONS_GRANT_PRICE;
837 prev_dim.STK_OPTIONS_CUML_INCOME := prev_dim.STK_OPTIONS_CUML_INCOME +
838 dim.STK_OPTIONS_CUML_INCOME ;
839 prev_dim.STK_OPTIONS_TAX_WITHHELD := prev_dim.STK_OPTIONS_TAX_WITHHELD +
840 dim.STK_OPTIONS_TAX_WITHHELD;
841 prev_dim.ISR_SUBJECT_FOR_FIXED_EARNINGS :=
842 prev_dim.ISR_SUBJECT_FOR_FIXED_EARNINGS +
843 dim.ISR_SUBJECT_FOR_FIXED_EARNINGS;
844 prev_dim.ISR_EXEMPT_FOR_FIXED_EARNINGS :=
845 prev_dim.ISR_EXEMPT_FOR_FIXED_EARNINGS +
846 dim.ISR_EXEMPT_FOR_FIXED_EARNINGS;
847 prev_dim.ISR_SUBJECT_FOR_XMAS_BONUS :=
848 prev_dim.ISR_SUBJECT_FOR_XMAS_BONUS +
849 dim.ISR_SUBJECT_FOR_XMAS_BONUS;
850 prev_dim.ISR_EXEMPT_FOR_XMAS_BONUS :=
851 prev_dim.ISR_EXEMPT_FOR_XMAS_BONUS +
852 dim.ISR_EXEMPT_FOR_XMAS_BONUS;
853 prev_dim.ISR_SUBJECT_FOR_TRAVEL_EXP :=
854 prev_dim.ISR_SUBJECT_FOR_TRAVEL_EXP +
855 dim.ISR_SUBJECT_FOR_TRAVEL_EXP;
856 prev_dim.ISR_EXEMPT_FOR_TRAVEL_EXP :=
857 prev_dim.ISR_EXEMPT_FOR_TRAVEL_EXP +
858 dim.ISR_EXEMPT_FOR_TRAVEL_EXP;
859 prev_dim.ISR_SUBJECT_FOR_OVERTIME :=
860 prev_dim.ISR_SUBJECT_FOR_OVERTIME +
861 dim.ISR_SUBJECT_FOR_OVERTIME;
862 prev_dim.ISR_EXEMPT_FOR_OVERTIME :=
863 prev_dim.ISR_EXEMPT_FOR_OVERTIME +
864 dim.ISR_EXEMPT_FOR_OVERTIME;
865 prev_dim.ISR_SUBJECT_FOR_VAC_PREMIUM :=
866 prev_dim.ISR_SUBJECT_FOR_VAC_PREMIUM +
867 dim.ISR_SUBJECT_FOR_VAC_PREMIUM;
868 prev_dim.ISR_EXEMPT_FOR_VAC_PREMIUM :=
869 prev_dim.ISR_EXEMPT_FOR_VAC_PREMIUM +
870 dim.ISR_EXEMPT_FOR_VAC_PREMIUM;
871 prev_dim.ISR_SUBJECT_FOR_DOM_PREMIUM :=
872 prev_dim.ISR_SUBJECT_FOR_DOM_PREMIUM +
873 dim.ISR_SUBJECT_FOR_DOM_PREMIUM;
874 prev_dim.ISR_EXEMPT_FOR_DOM_PREMIUM :=
875 prev_dim.ISR_EXEMPT_FOR_DOM_PREMIUM +
876 dim.ISR_EXEMPT_FOR_DOM_PREMIUM;
877 prev_dim.ISR_SUBJECT_FOR_PROFIT_SHARING :=
878 prev_dim.ISR_SUBJECT_FOR_PROFIT_SHARING +
879 dim.ISR_SUBJECT_FOR_PROFIT_SHARING;
880 prev_dim.ISR_EXEMPT_FOR_PROFIT_SHARING :=
881 prev_dim.ISR_EXEMPT_FOR_PROFIT_SHARING +
882 dim.ISR_EXEMPT_FOR_PROFIT_SHARING;
883 prev_dim.ISR_SUBJECT_FOR_HEALTHCARE_REI :=
884 prev_dim.ISR_SUBJECT_FOR_HEALTHCARE_REI +
885 dim.ISR_SUBJECT_FOR_HEALTHCARE_REI;
886 prev_dim.ISR_EXEMPT_FOR_HEALTHCARE_REI :=
887 prev_dim.ISR_EXEMPT_FOR_HEALTHCARE_REI +
888 dim.ISR_EXEMPT_FOR_HEALTHCARE_REI;
889 prev_dim.ISR_SUBJECT_FOR_SAVINGS_FUND :=
890 prev_dim.ISR_SUBJECT_FOR_SAVINGS_FUND +
891 dim.ISR_SUBJECT_FOR_SAVINGS_FUND;
892 prev_dim.ISR_EXEMPT_FOR_SAVINGS_FUND :=
893 prev_dim.ISR_EXEMPT_FOR_SAVINGS_FUND +
894 dim.ISR_EXEMPT_FOR_SAVINGS_FUND;
895 prev_dim.ISR_SUBJECT_FOR_SAVINGS_BOX :=
896 prev_dim.ISR_SUBJECT_FOR_SAVINGS_BOX +
897 dim.ISR_SUBJECT_FOR_SAVINGS_BOX;
898 prev_dim.ISR_EXEMPT_FOR_SAVINGS_BOX:=
899 prev_dim.ISR_EXEMPT_FOR_SAVINGS_BOX+
900 dim.ISR_EXEMPT_FOR_SAVINGS_BOX;
901 prev_dim.ISR_SUBJECT_FOR_PANTRY_COUPONS:=
902 prev_dim.ISR_SUBJECT_FOR_PANTRY_COUPONS+
903 dim.ISR_SUBJECT_FOR_PANTRY_COUPONS;
904 prev_dim.ISR_EXEMPT_FOR_PANTRY_COUPONS:=
905 prev_dim.ISR_EXEMPT_FOR_PANTRY_COUPONS+
906 dim.ISR_EXEMPT_FOR_PANTRY_COUPONS;
907 prev_dim.ISR_SUBJECT_FOR_FUNERAL_AID:=
908 prev_dim.ISR_SUBJECT_FOR_FUNERAL_AID+
909 dim.ISR_SUBJECT_FOR_FUNERAL_AID;
910 prev_dim.ISR_EXEMPT_FOR_FUNERAL_AID:=
911 prev_dim.ISR_EXEMPT_FOR_FUNERAL_AID+
912 dim.ISR_EXEMPT_FOR_FUNERAL_AID;
913 prev_dim.ISR_SUBJECT_FOR_WR_PD_BY_ER:=
914 prev_dim.ISR_SUBJECT_FOR_WR_PD_BY_ER+
915 dim.ISR_SUBJECT_FOR_WR_PD_BY_ER;
916 prev_dim.ISR_EXEMPT_FOR_WR_PD_BY_ER:=
917 prev_dim.ISR_EXEMPT_FOR_WR_PD_BY_ER+
918 dim.ISR_EXEMPT_FOR_WR_PD_BY_ER;
919 prev_dim.ISR_SUBJECT_FOR_PUN_INCENTIVE:=
920 prev_dim.ISR_SUBJECT_FOR_PUN_INCENTIVE+
921 dim.ISR_SUBJECT_FOR_PUN_INCENTIVE;
922 prev_dim.ISR_EXEMPT_FOR_PUN_INCENTIVE:=
923 prev_dim.ISR_EXEMPT_FOR_PUN_INCENTIVE+
924 dim.ISR_EXEMPT_FOR_PUN_INCENTIVE;
925 prev_dim.ISR_SUBJECT_FOR_LIFE_INS_PRE:=
926 prev_dim.ISR_SUBJECT_FOR_LIFE_INS_PRE+
927 dim.ISR_SUBJECT_FOR_LIFE_INS_PRE;
928 prev_dim.ISR_EXEMPT_FOR_LIFE_INS_PRE:=
929 prev_dim.ISR_EXEMPT_FOR_LIFE_INS_PRE+
930 dim.ISR_EXEMPT_FOR_LIFE_INS_PRE;
931 prev_dim.ISR_SUBJECT_FOR_MAJOR_MED_INS:=
932 prev_dim.ISR_SUBJECT_FOR_MAJOR_MED_INS+
933 dim.ISR_SUBJECT_FOR_MAJOR_MED_INS;
934 prev_dim.ISR_EXEMPT_FOR_MAJOR_MED_INS:=
935 prev_dim.ISR_EXEMPT_FOR_MAJOR_MED_INS+
936 dim.ISR_EXEMPT_FOR_MAJOR_MED_INS;
937 prev_dim.ISR_SUBJECT_FOR_REST_COUPONS:=
938 prev_dim.ISR_SUBJECT_FOR_REST_COUPONS+
939 dim.ISR_SUBJECT_FOR_REST_COUPONS;
940 prev_dim.ISR_EXEMPT_FOR_REST_COUPONS:=
941 prev_dim.ISR_EXEMPT_FOR_REST_COUPONS+
942 dim.ISR_EXEMPT_FOR_REST_COUPONS;
943 prev_dim.ISR_SUBJECT_FOR_GAS_COUPONS:=
944 prev_dim.ISR_SUBJECT_FOR_GAS_COUPONS+
945 dim.ISR_SUBJECT_FOR_GAS_COUPONS;
946 prev_dim.ISR_EXEMPT_FOR_GAS_COUPONS:=
947 prev_dim.ISR_EXEMPT_FOR_GAS_COUPONS+
948 dim.ISR_EXEMPT_FOR_GAS_COUPONS;
949 prev_dim.ISR_SUBJECT_FOR_UNI_COUPONS:=
950 prev_dim.ISR_SUBJECT_FOR_UNI_COUPONS+
951 dim.ISR_SUBJECT_FOR_UNI_COUPONS;
952 prev_dim.ISR_EXEMPT_FOR_UNI_COUPONS:=
953 prev_dim.ISR_EXEMPT_FOR_UNI_COUPONS+
954 dim.ISR_EXEMPT_FOR_UNI_COUPONS;
955 prev_dim.ISR_SUBJECT_FOR_RENTAL_AID:=
956 prev_dim.ISR_SUBJECT_FOR_RENTAL_AID+
957 dim.ISR_SUBJECT_FOR_RENTAL_AID;
958 prev_dim.ISR_EXEMPT_FOR_RENTAL_AID:=
959 prev_dim.ISR_EXEMPT_FOR_RENTAL_AID+
960 dim.ISR_EXEMPT_FOR_RENTAL_AID;
961 prev_dim.ISR_SUBJECT_FOR_EDU_AID:=
962 prev_dim.ISR_SUBJECT_FOR_EDU_AID+
963 dim.ISR_SUBJECT_FOR_EDU_AID;
964 prev_dim.ISR_EXEMPT_FOR_EDU_AID:=
965 prev_dim.ISR_EXEMPT_FOR_EDU_AID+
966 dim.ISR_EXEMPT_FOR_EDU_AID;
967 prev_dim.ISR_SUBJECT_FOR_GLASSES_AID:=
968 prev_dim.ISR_SUBJECT_FOR_GLASSES_AID+
969 dim.ISR_SUBJECT_FOR_GLASSES_AID;
970 prev_dim.ISR_EXEMPT_FOR_GLASSES_AID:=
971 prev_dim.ISR_EXEMPT_FOR_GLASSES_AID+
972 dim.ISR_EXEMPT_FOR_GLASSES_AID;
973 prev_dim.ISR_SUBJECT_FOR_TRANS_AID:=
974 prev_dim.ISR_SUBJECT_FOR_TRANS_AID+
975 dim.ISR_SUBJECT_FOR_TRANS_AID;
976 prev_dim.ISR_EXEMPT_FOR_TRANS_AID:=
977 prev_dim.ISR_EXEMPT_FOR_TRANS_AID+
978 dim.ISR_EXEMPT_FOR_TRANS_AID;
979 prev_dim.ISR_SUBJECT_FOR_UNION_PD_BY_ER:=
980 prev_dim.ISR_SUBJECT_FOR_UNION_PD_BY_ER+
981 dim.ISR_SUBJECT_FOR_UNION_PD_BY_ER;
982 prev_dim.ISR_EXEMPT_FOR_UNION_PD_BY_ER:=
983 prev_dim.ISR_EXEMPT_FOR_UNION_PD_BY_ER+
984 dim.ISR_EXEMPT_FOR_UNION_PD_BY_ER;
985 prev_dim.ISR_SUBJECT_FOR_DISAB_SUBSIDY:=
986 prev_dim.ISR_SUBJECT_FOR_DISAB_SUBSIDY+
987 dim.ISR_SUBJECT_FOR_DISAB_SUBSIDY;
988 prev_dim.ISR_EXEMPT_FOR_DISAB_SUBSIDY:=
989 prev_dim.ISR_EXEMPT_FOR_DISAB_SUBSIDY+
990 dim.ISR_EXEMPT_FOR_DISAB_SUBSIDY;
991 prev_dim.ISR_SUBJECT_FOR_CHILD_SCHOLAR:=
992 prev_dim.ISR_SUBJECT_FOR_CHILD_SCHOLAR+
993 dim.ISR_SUBJECT_FOR_CHILD_SCHOLAR;
994 prev_dim.ISR_EXEMPT_FOR_CHILD_SCHOLAR:=
995 prev_dim.ISR_EXEMPT_FOR_CHILD_SCHOLAR+
996 dim.ISR_EXEMPT_FOR_CHILD_SCHOLAR;
997 prev_dim.PREV_ER_EARNINGS:=
998 prev_dim.PREV_ER_EARNINGS+
999 dim.PREV_ER_EARNINGS;
1000 prev_dim.PREV_ER_EXEMPT_EARNINGS:=
1001 prev_dim.PREV_ER_EXEMPT_EARNINGS+
1002 dim.PREV_ER_EXEMPT_EARNINGS;
1003 prev_dim.ISR_SUBJECT_OTHER_INCOME:=
1004 prev_dim.ISR_SUBJECT_OTHER_INCOME+
1005 dim.ISR_SUBJECT_OTHER_INCOME;
1006 prev_dim.ISR_EXEMPT_OTHER_INCOME:=
1007 prev_dim.ISR_EXEMPT_OTHER_INCOME+
1008 dim.ISR_EXEMPT_OTHER_INCOME;
1009 prev_dim.TOTAL_SUBJECT_EARNINGS:=
1010 prev_dim.TOTAL_SUBJECT_EARNINGS+
1011 dim.TOTAL_SUBJECT_EARNINGS;
1012 prev_dim.TOTAL_EXEMPT_EARNINGS:=
1013 prev_dim.TOTAL_EXEMPT_EARNINGS+
1014 dim.TOTAL_EXEMPT_EARNINGS;
1015 prev_dim.TAX_WITHHELD_IN_FISCAL_YEAR:=
1016 prev_dim.TAX_WITHHELD_IN_FISCAL_YEAR+
1017 dim.TAX_WITHHELD_IN_FISCAL_YEAR;
1018 prev_dim.PREV_ER_ISR_WITHHELD:=
1019 prev_dim.PREV_ER_ISR_WITHHELD+
1020 dim.PREV_ER_ISR_WITHHELD;
1021 prev_dim.CURRENT_FY_ARREARS:=
1022 prev_dim.CURRENT_FY_ARREARS+
1023 dim.CURRENT_FY_ARREARS;
1024 prev_dim.PREV_FY_ARREARS:=
1025 prev_dim.PREV_FY_ARREARS+
1026 dim.PREV_FY_ARREARS;
1027 prev_dim.CREDIT_TO_SALARY:=
1028 prev_dim.CREDIT_TO_SALARY+
1029 dim.CREDIT_TO_SALARY;
1030 prev_dim.CREDIT_TO_SALARY_PAID:=
1031 prev_dim.CREDIT_TO_SALARY_PAID+
1032 dim.CREDIT_TO_SALARY_PAID;
1033 prev_dim.SOCIAL_FORESIGHT_EARNINGS:=
1034 prev_dim.SOCIAL_FORESIGHT_EARNINGS+
1035 dim.SOCIAL_FORESIGHT_EARNINGS;
1036 prev_dim.ISR_EXEMPT_FOR_SOC_FORESIGHT:=
1037 prev_dim.ISR_EXEMPT_FOR_SOC_FORESIGHT+
1038 dim.ISR_EXEMPT_FOR_SOC_FORESIGHT;
1039 prev_dim.SUM_SAL_WAGES_EARNINGS:=
1040 prev_dim.SUM_SAL_WAGES_EARNINGS +
1041 dim.SUM_SAL_WAGES_EARNINGS;
1042 prev_dim.LOCAL_TAX_AMT_EARN_SAL_WAGES:=
1043 prev_dim.LOCAL_TAX_AMT_EARN_SAL_WAGES +
1044 dim.LOCAL_TAX_AMT_EARN_SAL_WAGES;
1045 prev_dim.AMT_SUBSIDY_EMPT_IN_FY:=
1046 prev_dim.AMT_SUBSIDY_EMPT_IN_FY +
1047 dim.AMT_SUBSIDY_EMPT_IN_FY;
1048 prev_dim.AMT_SUBSIDY_INCOME_PAID_EMP_FY:=
1049 prev_dim.AMT_SUBSIDY_INCOME_PAID_EMP_FY +
1050 dim.AMT_SUBSIDY_INCOME_PAID_EMP_FY;
1051 prev_dim.ISR_CALCULATED:=
1052 prev_dim.ISR_CALCULATED+
1053 dim.ISR_CALCULATED;
1054 prev_dim.ISR_CREDITABLE_SUBSIDY:=
1055 prev_dim.ISR_CREDITABLE_SUBSIDY+
1056 dim.ISR_CREDITABLE_SUBSIDY;
1057 prev_dim.ISR_NON_CREDITABLE_SUBSIDY:=
1058 prev_dim.ISR_NON_CREDITABLE_SUBSIDY+
1059 dim.ISR_NON_CREDITABLE_SUBSIDY;
1060 prev_dim.ISR_ON_CUMULATIVE_EARNINGS:=
1061 prev_dim.ISR_ON_CUMULATIVE_EARNINGS+
1062 dim.ISR_ON_CUMULATIVE_EARNINGS;
1063 prev_dim.ISR_ON_NON_CUMULATIVE_EARNINGS:=
1064 prev_dim.ISR_ON_NON_CUMULATIVE_EARNINGS+
1065 dim.ISR_ON_NON_CUMULATIVE_EARNINGS;
1066 prev_dim.ISR_SUBSIDY_EMPT_PAID_TO_EMP:=
1067 prev_dim.ISR_SUBSIDY_EMPT_PAID_TO_EMP +
1068 dim.ISR_SUBSIDY_EMPT_PAID_TO_EMP;
1069 prev_dim.ISR_SUBSIDY_INC_PAID_EMP:=
1070 prev_dim.ISR_SUBSIDY_INC_PAID_EMP +
1071 dim.ISR_SUBSIDY_INC_PAID_EMP;
1072 prev_dim.TOT_EARNING_ASSI_CONCEPTS := prev_dim.TOT_EARNING_ASSI_CONCEPTS +
1073 dim.TOT_EARNING_ASSI_CONCEPTS;
1074 prev_dim.EMPLOYEE_STATE_TAX_WITHHELD := prev_dim.EMPLOYEE_STATE_TAX_WITHHELD +
1075 dim.EMPLOYEE_STATE_TAX_WITHHELD ;
1076 prev_dim.TOT_EXEMPT_EARNINGS := prev_dim.TOT_EXEMPT_EARNINGS +
1077 dim.TOT_EXEMPT_EARNINGS ;
1078 prev_dim.TOT_NON_CUMULATIVE_EARNINGS := prev_dim.TOT_NON_CUMULATIVE_EARNINGS +
1079 dim.TOT_NON_CUMULATIVE_EARNINGS ;
1080 prev_dim.TOT_CUMULATIVE_EARNINGS := prev_dim.TOT_CUMULATIVE_EARNINGS +
1081 dim.TOT_CUMULATIVE_EARNINGS ;
1082 prev_dim.CREDITABLE_SUBSIDY_FRACTIONIII := prev_dim.CREDITABLE_SUBSIDY_FRACTIONIII +
1083 dim.CREDITABLE_SUBSIDY_FRACTIONIII ;
1084 prev_dim.CREDITABLE_SUBSIDY_FRACTIONIV := prev_dim.CREDITABLE_SUBSIDY_FRACTIONIV +
1085 prev_dim.CREDITABLE_SUBSIDY_FRACTIONIV ;
1086 prev_dim.TAX_ON_INCOME_FISCAL_YEAR := prev_dim.TAX_ON_INCOME_FISCAL_YEAR +
1087 dim.TAX_ON_INCOME_FISCAL_YEAR;
1088 prev_dim.ISR_TAX_WITHHELD := prev_dim.ISR_TAX_WITHHELD +
1089 dim.ISR_TAX_WITHHELD;
1090 prev_dim.TOTAL_EARNINGS := prev_dim.TOTAL_EARNINGS +
1091 dim.TOTAL_EARNINGS;
1092
1093 END IF;
1094
1095 ln_person_id := dim.person_id;
1096
1097 END LOOP;
1098 CLOSE c_dim_rec;
1099
1100 populate_xml_table('PERSON_ID', prev_dim.PERSON_ID,'TEXT');
1101 populate_xml_table('START_MONTH', prev_dim.START_MONTH,'TEXT');
1102 populate_xml_table('END_MONTH', prev_dim.END_MONTH,'TEXT');
1103 populate_xml_table('RFC_ID', prev_dim.RFC_ID,'TEXT');
1104 populate_xml_table('CURP', prev_dim.CURP,'TEXT');
1105 populate_xml_table('PATERNAL_LAST_NAME',prev_dim.PATERNAL_LAST_NAME,'TEXT');
1106 populate_xml_table('MATERNAL_LAST_NAME',prev_dim.MATERNAL_LAST_NAME,'TEXT');
1107 populate_xml_table('NAMES', prev_dim.NAMES,'TEXT');
1108 populate_xml_table('ECONOMIC_ZONE', prev_dim.ECONOMIC_ZONE,'TEXT');
1109 populate_xml_table('ANNUAL_TAX_CALC_FLAG',
1110 prev_dim.ANNUAL_TAX_CALC_FLAG,'TEXT');
1111 populate_xml_table('RATE_FISCAL_YEAR_IND',
1112 prev_dim.RATE_FISCAL_YEAR_IND,'TEXT');
1113 populate_xml_table('RATE_1991_IND', prev_dim.RATE_1991_IND,'TEXT');
1114
1115 IF ( prev_dim.RATE_1991_IND = '0' AND prev_dim.RATE_FISCAL_YEAR_IND = '0' )
1116 THEN
1117 populate_xml_table('SUBSIDY_PROPORTION_USED', '0.0000', 'TEXT');
1118 ELSE
1119 IF prev_dim.PREV_ER_EARNINGS <> 0 THEN
1120 populate_xml_table('SUBSIDY_PROPORTION_USED',
1121 prev_dim.SUBSIDY_PORTION_APPLIED,'TEXT');
1122 ELSE
1123 populate_xml_table('SUBSIDY_PROPORTION_USED',
1124 prev_dim.TAX_SUBSIDY_PCT,'TEXT');
1125 END IF;
1126 END IF;
1127
1128 populate_xml_table('UNION_WORKER_FLAG', prev_dim.UNION_WORKER_FLAG,'TEXT');
1129 populate_xml_table('ASSIMILATED_TO_SALARY_IND',
1130 prev_dim.ASSIMILATED_TO_SALARY_IND,'TEXT');
1131 populate_xml_table('STATE_ID', prev_dim.STATE_ID,'TEXT');
1132 populate_xml_table('OTHER_ER_RFC1', prev_dim.OTHER_ER_RFC1,'TEXT');
1133 populate_xml_table('OTHER_ER_RFC2', prev_dim.OTHER_ER_RFC2,'TEXT');
1134 populate_xml_table('OTHER_ER_RFC3', prev_dim.OTHER_ER_RFC3,'TEXT');
1135 populate_xml_table('OTHER_ER_RFC4', prev_dim.OTHER_ER_RFC4,'TEXT');
1136 populate_xml_table('OTHER_ER_RFC5', prev_dim.OTHER_ER_RFC5,'TEXT');
1137 populate_xml_table('OTHER_ER_RFC6', prev_dim.OTHER_ER_RFC6,'TEXT');
1138 populate_xml_table('OTHER_ER_RFC7', prev_dim.OTHER_ER_RFC7,'TEXT');
1139 populate_xml_table('OTHER_ER_RFC8', prev_dim.OTHER_ER_RFC8,'TEXT');
1140 populate_xml_table('OTHER_ER_RFC9', prev_dim.OTHER_ER_RFC9,'TEXT');
1141 populate_xml_table('OTHER_ER_RFC10', prev_dim.OTHER_ER_RFC10,'TEXT');
1142 populate_xml_table('SEP_EARNINGS', prev_dim.SEP_EARNINGS,'TEXT');
1143 populate_xml_table('ASSIMILATED_SALARIES',
1144 prev_dim.ASSIMILATED_SALARIES,'TEXT');
1145 populate_xml_table('ER_PAYMENT_TO_EE', prev_dim.ER_PAYMENT_TO_EE,'TEXT');
1146 populate_xml_table('RET_EARNINGS_IN_PART_PYMNT',
1147 prev_dim.RET_EARNINGS_IN_PART_PYMNT , 'SEP_BAL');
1148 populate_xml_table('RET_DAILY_EARNINGS_IN_PYMNT',
1149 prev_dim.RET_DAILY_EARNINGS_IN_PYMNT , 'SEP_BAL');
1150 populate_xml_table('RET_PERIOD_EARNINGS',
1151 prev_dim.RET_PERIOD_EARNINGS , 'SEP_BAL');
1152 populate_xml_table('RET_EARNINGS_IN_ONE_PYMNT',
1153 prev_dim.RET_EARNINGS_IN_ONE_PYMNT , 'SEP_BAL');
1154 populate_xml_table('RET_EARNINGS_DAYS',
1155 prev_dim.RET_EARNINGS_DAYS , 'SEP_BAL');
1156 populate_xml_table('RET_EXEMPT_EARNINGS',
1157 prev_dim.RET_EXEMPT_EARNINGS , 'SEP_BAL');
1158 populate_xml_table('RET_TAXABLE_EARNINGS',
1159 prev_dim.RET_TAXABLE_EARNINGS , 'SEP_BAL');
1160 populate_xml_table('RET_CUMULATIVE_EARNINGS',
1161 prev_dim.RET_CUMULATIVE_EARNINGS , 'SEP_BAL');
1162 populate_xml_table('RET_NON_CUMULATIVE_EARNINGS',
1163 prev_dim.RET_NON_CUMULATIVE_EARNINGS , 'SEP_BAL');
1164 populate_xml_table('ISR_WITHHELD_FOR_RET_EARNINGS',
1165 prev_dim.ISR_WITHHELD_FOR_RET_EARNINGS , 'SEP_BAL');
1166 populate_xml_table('AMENDS',
1167 prev_dim.AMENDS , 'SEP_BAL');
1168 populate_xml_table('SENIORITY',
1169 prev_dim.SENIORITY , 'SEP_BAL');
1170 populate_xml_table('ISR_EXEMPT_FOR_AMENDS',
1171 prev_dim.ISR_EXEMPT_FOR_AMENDS , 'SEP_BAL');
1172 populate_xml_table('ISR_SUBJECT_FOR_AMENDS',
1173 prev_dim.ISR_SUBJECT_FOR_AMENDS , 'SEP_BAL');
1174 populate_xml_table('LAST_MTH_ORD_SAL',
1175 prev_dim.LAST_MTH_ORD_SAL , 'SEP_BAL');
1176 populate_xml_table('LAST_MTH_ORD_SAL_WITHHELD',
1177 prev_dim.LAST_MTH_ORD_SAL_WITHHELD , 'SEP_BAL');
1178 populate_xml_table('NON_CUMULATIVE_AMENDS',
1179 prev_dim.NON_CUMULATIVE_AMENDS , 'SEP_BAL');
1180 populate_xml_table('ISR_WITHHELD_FOR_AMENDS',
1181 prev_dim.ISR_WITHHELD_FOR_AMENDS , 'SEP_BAL');
1182 populate_xml_table('ASSIMILATED_EARNINGS',
1183 prev_dim.ASSIMILATED_EARNINGS, 'ASS_BAL');
1184 populate_xml_table('ISR_WITHHELD_FOR_ASSI_EARNINGS',
1185 prev_dim.ISR_WITHHELD_FOR_ASSI_EARNINGS, 'ASS_BAL');
1186 populate_xml_table('EMPR_STOCK_OPTION_PLAN',
1187 prev_dim.EMPR_STOCK_OPTION_PLAN, 'ASS_BAL');
1188 populate_xml_table('STK_OPTIONS_VESTING_VALUE',
1189 prev_dim.STK_OPTIONS_VESTING_VALUE, 'ASS_BAL');
1190 populate_xml_table('STK_OPTIONS_GRANT_PRICE',
1191 prev_dim.STK_OPTIONS_GRANT_PRICE, 'ASS_BAL');
1192 populate_xml_table('STK_OPTIONS_CUML_INCOME',
1193 prev_dim.STK_OPTIONS_CUML_INCOME, 'ASS_BAL');
1194 populate_xml_table('STK_OPTIONS_TAX_WITHHELD',
1195 prev_dim.STK_OPTIONS_TAX_WITHHELD, 'ASS_BAL');
1196 populate_xml_table('ISR_SUBJECT_FOR_FIXED_EARNINGS',
1197 prev_dim.ISR_SUBJECT_FOR_FIXED_EARNINGS, 'EMP_BAL');
1198 populate_xml_table('ISR_EXEMPT_FOR_FIXED_EARNINGS',
1199 prev_dim.ISR_EXEMPT_FOR_FIXED_EARNINGS, 'EMP_BAL');
1200 populate_xml_table('ISR_SUBJECT_FOR_XMAS_BONUS',
1201 prev_dim.ISR_SUBJECT_FOR_XMAS_BONUS, 'EMP_BAL');
1202 populate_xml_table('ISR_EXEMPT_FOR_XMAS_BONUS',
1203 prev_dim.ISR_EXEMPT_FOR_XMAS_BONUS, 'EMP_BAL');
1204 populate_xml_table('ISR_SUBJECT_FOR_TRAVEL_EXP',
1205 prev_dim.ISR_SUBJECT_FOR_TRAVEL_EXP, 'EMP_BAL');
1206 populate_xml_table('ISR_EXEMPT_FOR_TRAVEL_EXP',
1207 prev_dim.ISR_EXEMPT_FOR_TRAVEL_EXP, 'EMP_BAL');
1208 populate_xml_table('ISR_SUBJECT_FOR_OVERTIME',
1209 prev_dim.ISR_SUBJECT_FOR_OVERTIME, 'EMP_BAL');
1210 populate_xml_table('ISR_EXEMPT_FOR_OVERTIME',
1211 prev_dim.ISR_EXEMPT_FOR_OVERTIME, 'EMP_BAL');
1212 populate_xml_table('ISR_SUBJECT_FOR_VAC_PREMIUM',
1213 prev_dim.ISR_SUBJECT_FOR_VAC_PREMIUM, 'EMP_BAL');
1214 populate_xml_table('ISR_EXEMPT_FOR_VAC_PREMIUM',
1215 prev_dim.ISR_EXEMPT_FOR_VAC_PREMIUM, 'EMP_BAL');
1216 populate_xml_table('ISR_SUBJECT_FOR_DOM_PREMIUM',
1217 prev_dim.ISR_SUBJECT_FOR_DOM_PREMIUM, 'EMP_BAL');
1218 populate_xml_table('ISR_EXEMPT_FOR_DOM_PREMIUM',
1219 prev_dim.ISR_EXEMPT_FOR_DOM_PREMIUM, 'EMP_BAL');
1220 populate_xml_table('ISR_SUBJECT_FOR_PROFIT_SHARING',
1221 prev_dim.ISR_SUBJECT_FOR_PROFIT_SHARING, 'EMP_BAL');
1222 populate_xml_table('ISR_EXEMPT_FOR_PROFIT_SHARING',
1223 prev_dim.ISR_EXEMPT_FOR_PROFIT_SHARING, 'EMP_BAL');
1224 populate_xml_table('ISR_SUBJECT_FOR_HEALTHCARE_REI',
1225 prev_dim.ISR_SUBJECT_FOR_HEALTHCARE_REI, 'EMP_BAL');
1226 populate_xml_table('ISR_EXEMPT_FOR_HEALTHCARE_REI',
1227 prev_dim.ISR_EXEMPT_FOR_HEALTHCARE_REI, 'EMP_BAL');
1228 populate_xml_table('ISR_SUBJECT_FOR_SAVINGS_FUND',
1229 prev_dim.ISR_SUBJECT_FOR_SAVINGS_FUND, 'EMP_BAL');
1230 populate_xml_table('ISR_EXEMPT_FOR_SAVINGS_FUND',
1231 prev_dim.ISR_EXEMPT_FOR_SAVINGS_FUND, 'EMP_BAL');
1232 populate_xml_table('ISR_SUBJECT_FOR_SAVINGS_BOX',
1233 prev_dim.ISR_SUBJECT_FOR_SAVINGS_BOX, 'EMP_BAL');
1234 populate_xml_table('ISR_EXEMPT_FOR_SAVINGS_BOX',
1235 prev_dim.ISR_EXEMPT_FOR_SAVINGS_BOX, 'EMP_BAL');
1236 populate_xml_table('ISR_SUBJECT_FOR_PANTRY_COUPONS',
1237 prev_dim.ISR_SUBJECT_FOR_PANTRY_COUPONS, 'EMP_BAL');
1238 populate_xml_table('ISR_EXEMPT_FOR_PANTRY_COUPONS',
1239 prev_dim.ISR_EXEMPT_FOR_PANTRY_COUPONS, 'EMP_BAL');
1240 populate_xml_table('ISR_SUBJECT_FOR_FUNERAL_AID',
1241 prev_dim.ISR_SUBJECT_FOR_FUNERAL_AID, 'EMP_BAL');
1242 populate_xml_table('ISR_EXEMPT_FOR_FUNERAL_AID',
1243 prev_dim.ISR_EXEMPT_FOR_FUNERAL_AID, 'EMP_BAL');
1244 populate_xml_table('ISR_SUBJECT_FOR_WR_PD_BY_ER',
1245 prev_dim.ISR_SUBJECT_FOR_WR_PD_BY_ER, 'EMP_BAL');
1246 populate_xml_table('ISR_EXEMPT_FOR_WR_PD_BY_ER',
1247 prev_dim.ISR_EXEMPT_FOR_WR_PD_BY_ER, 'EMP_BAL');
1248 populate_xml_table('ISR_SUBJECT_FOR_PUN_INCENTIVE',
1249 prev_dim.ISR_SUBJECT_FOR_PUN_INCENTIVE, 'EMP_BAL');
1250 populate_xml_table('ISR_EXEMPT_FOR_PUN_INCENTIVE',
1251 prev_dim.ISR_EXEMPT_FOR_PUN_INCENTIVE, 'EMP_BAL');
1252 populate_xml_table('ISR_SUBJECT_FOR_LIFE_INS_PRE',
1253 prev_dim.ISR_SUBJECT_FOR_LIFE_INS_PRE, 'EMP_BAL');
1254 populate_xml_table('ISR_EXEMPT_FOR_LIFE_INS_PRE',
1255 prev_dim.ISR_EXEMPT_FOR_LIFE_INS_PRE, 'EMP_BAL');
1256 populate_xml_table('ISR_SUBJECT_FOR_MAJOR_MED_INS',
1257 prev_dim.ISR_SUBJECT_FOR_MAJOR_MED_INS, 'EMP_BAL');
1258 populate_xml_table('ISR_EXEMPT_FOR_MAJOR_MED_INS',
1259 prev_dim.ISR_EXEMPT_FOR_MAJOR_MED_INS, 'EMP_BAL');
1260 populate_xml_table('ISR_SUBJECT_FOR_REST_COUPONS',
1261 prev_dim.ISR_SUBJECT_FOR_REST_COUPONS, 'EMP_BAL');
1262 populate_xml_table('ISR_EXEMPT_FOR_REST_COUPONS',
1263 prev_dim.ISR_EXEMPT_FOR_REST_COUPONS, 'EMP_BAL');
1264 populate_xml_table('ISR_SUBJECT_FOR_GAS_COUPONS',
1265 prev_dim.ISR_SUBJECT_FOR_GAS_COUPONS, 'EMP_BAL');
1266 populate_xml_table('ISR_EXEMPT_FOR_GAS_COUPONS',
1267 prev_dim.ISR_EXEMPT_FOR_GAS_COUPONS, 'EMP_BAL');
1268 populate_xml_table('ISR_SUBJECT_FOR_UNI_COUPONS',
1269 prev_dim.ISR_SUBJECT_FOR_UNI_COUPONS, 'EMP_BAL');
1270 populate_xml_table('ISR_EXEMPT_FOR_UNI_COUPONS',
1271 prev_dim.ISR_EXEMPT_FOR_UNI_COUPONS, 'EMP_BAL');
1272 populate_xml_table('ISR_SUBJECT_FOR_RENTAL_AID',
1273 prev_dim.ISR_SUBJECT_FOR_RENTAL_AID, 'EMP_BAL');
1274 populate_xml_table('ISR_EXEMPT_FOR_RENTAL_AID',
1275 prev_dim.ISR_EXEMPT_FOR_RENTAL_AID, 'EMP_BAL');
1276 populate_xml_table('ISR_SUBJECT_FOR_EDU_AID',
1277 prev_dim.ISR_SUBJECT_FOR_EDU_AID, 'EMP_BAL');
1278 populate_xml_table('ISR_EXEMPT_FOR_EDU_AID',
1279 prev_dim.ISR_EXEMPT_FOR_EDU_AID, 'EMP_BAL');
1280 populate_xml_table('ISR_SUBJECT_FOR_GLASSES_AID',
1281 prev_dim.ISR_SUBJECT_FOR_GLASSES_AID, 'EMP_BAL');
1282 populate_xml_table('ISR_EXEMPT_FOR_GLASSES_AID',
1283 prev_dim.ISR_EXEMPT_FOR_GLASSES_AID, 'EMP_BAL');
1284 populate_xml_table('ISR_SUBJECT_FOR_TRANS_AID',
1285 prev_dim.ISR_SUBJECT_FOR_TRANS_AID, 'EMP_BAL');
1286 populate_xml_table('ISR_EXEMPT_FOR_TRANS_AID',
1287 prev_dim.ISR_EXEMPT_FOR_TRANS_AID, 'EMP_BAL');
1288 populate_xml_table('ISR_SUBJECT_FOR_UNION_PD_BY_ER',
1289 prev_dim.ISR_SUBJECT_FOR_UNION_PD_BY_ER, 'EMP_BAL');
1290 populate_xml_table('ISR_EXEMPT_FOR_UNION_PD_BY_ER',
1291 prev_dim.ISR_EXEMPT_FOR_UNION_PD_BY_ER, 'EMP_BAL');
1292 populate_xml_table('ISR_SUBJECT_FOR_DISAB_SUBSIDY',
1293 prev_dim.ISR_SUBJECT_FOR_DISAB_SUBSIDY, 'EMP_BAL');
1294 populate_xml_table('ISR_EXEMPT_FOR_DISAB_SUBSIDY',
1295 prev_dim.ISR_EXEMPT_FOR_DISAB_SUBSIDY, 'EMP_BAL');
1296 populate_xml_table('ISR_SUBJECT_FOR_CHILD_SCHOLAR',
1297 prev_dim.ISR_SUBJECT_FOR_CHILD_SCHOLAR, 'EMP_BAL');
1298 populate_xml_table('ISR_EXEMPT_FOR_CHILD_SCHOLAR',
1299 prev_dim.ISR_EXEMPT_FOR_CHILD_SCHOLAR, 'EMP_BAL');
1300 populate_xml_table('PREV_ER_EARNINGS',
1301 prev_dim.PREV_ER_EARNINGS, 'EMP_BAL');
1302 populate_xml_table('PREV_ER_EXEMPT_EARNINGS',
1303 prev_dim.PREV_ER_EXEMPT_EARNINGS, 'EMP_BAL');
1304 populate_xml_table('ISR_SUBJECT_OTHER_INCOME',
1305 prev_dim.ISR_SUBJECT_OTHER_INCOME, 'EMP_BAL');
1306 populate_xml_table('ISR_EXEMPT_OTHER_INCOME',
1307 prev_dim.ISR_EXEMPT_OTHER_INCOME, 'EMP_BAL');
1308 populate_xml_table('TOTAL_SUBJECT_EARNINGS',
1309 prev_dim.TOTAL_SUBJECT_EARNINGS, 'EMP_BAL');
1310 populate_xml_table('TOTAL_EXEMPT_EARNINGS',
1311 prev_dim.TOTAL_EXEMPT_EARNINGS, 'EMP_BAL');
1312 populate_xml_table('TAX_WITHHELD_IN_FISCAL_YEAR',
1313 prev_dim.TAX_WITHHELD_IN_FISCAL_YEAR, 'EMP_BAL');
1314 populate_xml_table('PREV_ER_ISR_WITHHELD',
1315 prev_dim.PREV_ER_ISR_WITHHELD, 'EMP_BAL');
1316 populate_xml_table('CURRENT_FY_ARREARS',
1317 prev_dim.CURRENT_FY_ARREARS, 'EMP_BAL');
1318 populate_xml_table('PREV_FY_ARREARS',
1319 prev_dim.PREV_FY_ARREARS, 'EMP_BAL');
1320 populate_xml_table('CREDIT_TO_SALARY',
1321 prev_dim.CREDIT_TO_SALARY, 'EMP_BAL');
1322 populate_xml_table('CREDIT_TO_SALARY_PAID',
1323 prev_dim.CREDIT_TO_SALARY_PAID, 'EMP_BAL');
1324 populate_xml_table('SOCIAL_FORESIGHT_EARNINGS',
1325 prev_dim.SOCIAL_FORESIGHT_EARNINGS, 'EMP_BAL');
1326 populate_xml_table('ISR_EXEMPT_FOR_SOC_FORESIGHT',
1327 prev_dim.ISR_EXEMPT_FOR_SOC_FORESIGHT, 'EMP_BAL');
1328 populate_xml_table('SUM_SAL_WAGES_EARNINGS',
1329 prev_dim.SUM_SAL_WAGES_EARNINGS, 'EMP_BAL');
1330 populate_xml_table('AMT_SUBSIDY_INCOME_PAID_EMP_FY ',
1331 prev_dim.AMT_SUBSIDY_INCOME_PAID_EMP_FY, 'EMP_BAL');
1332 populate_xml_table('LOCAL_TAX_AMT_EARN_SAL_WAGES',
1333 prev_dim.LOCAL_TAX_AMT_EARN_SAL_WAGES, 'EMP_BAL');
1334 populate_xml_table('AMT_SUBSIDY_EMPT_IN_FY',
1335 prev_dim.AMT_SUBSIDY_EMPT_IN_FY, 'EMP_BAL');
1336 populate_xml_table('ISR_LOCAL_TAX_AMT_EARN_SAL_WAGES',
1337 prev_dim.LOCAL_TAX_AMT_EARN_SAL_WAGES, 'SUMM_BAL');
1338 populate_xml_table('ISR_CALCULATED', prev_dim.ISR_CALCULATED, 'SUMM_BAL');
1339 populate_xml_table('ISR_CREDITABLE_SUBSIDY',
1340 prev_dim.ISR_CREDITABLE_SUBSIDY, 'SUMM_BAL');
1341 populate_xml_table('ISR_NON_CREDITABLE_SUBSIDY',
1342 prev_dim.ISR_NON_CREDITABLE_SUBSIDY, 'SUMM_BAL');
1343 populate_xml_table('ISR_ON_CUMULATIVE_EARNINGS',
1344 prev_dim.ISR_ON_CUMULATIVE_EARNINGS, 'SUMM_BAL');
1345 populate_xml_table('ISR_ON_NON_CUMULATIVE_EARNINGS',
1346 prev_dim.ISR_ON_NON_CUMULATIVE_EARNINGS, 'SUMM_BAL');
1347 populate_xml_table('ISR_SUBSIDY_EMPT_PAID_TO_EMP',
1348 prev_dim.ISR_SUBSIDY_EMPT_PAID_TO_EMP, 'SUMM_BAL');
1349 populate_xml_table('ISR_SUBSIDY_INC_PAID_EMP',
1350 prev_dim.ISR_SUBSIDY_INC_PAID_EMP, 'SUMM_BAL');
1351 populate_xml_table('TAX_SUBSIDY_PCT', prev_dim.TAX_SUBSIDY_PCT,'TEXT');
1352 populate_xml_table('TAX_SUBSIDY_PCT_I', prev_dim.TAX_SUBSIDY_PCT_I, 'TEXT');
1353 populate_xml_table('TAX_SUBSIDY_PCT_D', prev_dim.TAX_SUBSIDY_PCT_D, 'TEXT');
1354 populate_xml_table('SUBSIDY_PORTION_APPLIED',
1355 prev_dim.SUBSIDY_PORTION_APPLIED, 'TEXT');
1356 populate_xml_table('SUBSIDY_PORTION_APPLIED_I',
1357 prev_dim.SUBSIDY_PORTION_APPLIED_I, 'TEXT');
1358 populate_xml_table('SUBSIDY_PORTION_APPLIED_D',
1359 prev_dim.SUBSIDY_PORTION_APPLIED_D, 'TEXT');
1360 populate_xml_table('TOT_EARNING_ASSI_CONCEPTS',
1361 prev_dim.TOT_EARNING_ASSI_CONCEPTS, 'TEXT');
1362 populate_xml_table('EMPLOYEE_STATE_TAX_WITHHELD',
1363 prev_dim.EMPLOYEE_STATE_TAX_WITHHELD, 'TEXT');
1364 populate_xml_table('TOT_EXEMPT_EARNINGS',
1365 prev_dim.TOT_EXEMPT_EARNINGS, 'TEXT');
1366 populate_xml_table('TOT_NON_CUMULATIVE_EARNINGS',
1367 prev_dim.TOT_NON_CUMULATIVE_EARNINGS, 'TEXT');
1368 populate_xml_table('TOT_CUMULATIVE_EARNINGS',
1369 prev_dim.TOT_CUMULATIVE_EARNINGS, 'TEXT');
1370 populate_xml_table('CREDITABLE_SUBSIDY_FRACTIONIII',
1371 prev_dim.CREDITABLE_SUBSIDY_FRACTIONIII, 'TEXT');
1372 populate_xml_table('CREDITABLE_SUBSIDY_FRACTIONIV',
1373 prev_dim.CREDITABLE_SUBSIDY_FRACTIONIV, 'TEXT');
1374 populate_xml_table('TAX_ON_INCOME_FISCAL_YEAR',
1375 prev_dim.TAX_ON_INCOME_FISCAL_YEAR, 'TEXT');
1376 populate_xml_table('ISR_TAX_WITHHELD',
1377 prev_dim.ISR_TAX_WITHHELD, 'TEXT');
1378 populate_xml_table('TOTAL_EARNINGS', prev_dim.TOTAL_EARNINGS, 'TEXT');
1379 populate_xml_table('ER_RFC_ID', prev_dim.ER_RFC_ID, 'TEXT');
1380 populate_xml_table('ER_LEGAL_NAME', prev_dim.ER_LEGAL_NAME, 'TEXT');
1381 populate_xml_table('ER_LEGAL_REP_NAMES',
1382 prev_dim.ER_LEGAL_REP_NAMES, 'TEXT');
1383 populate_xml_table('ER_LEGAL_REP_RFC_ID',
1384 prev_dim.ER_LEGAL_REP_RFC_ID, 'TEXT');
1385 populate_xml_table('ER_LEGAL_REP_CURP', prev_dim.ER_LEGAL_REP_CURP, 'TEXT');
1386 populate_xml_table('ER_TAX_SUBSIDY_PCT',
1387 prev_dim.ER_TAX_SUBSIDY_PCT, 'TEXT');
1388 populate_xml_table('FISCAL_YEAR_REPORTING',
1389 prev_dim.FISCAL_YEAR_REPORTING, 'TEXT');
1390 populate_xml_table('FULL_NAME', prev_dim.FULL_NAME, 'TEXT');
1391
1392 IF ( ( gn_sep_bal + gn_ass_bal + gn_emp_bal ) > 0 ) THEN
1393
1394 FOR i IN dim_xml_tbl.FIRST..dim_xml_tbl.LAST LOOP
1395
1396 IF ( dim_xml_tbl(i).name = 'SEP_EARNINGS' AND
1397 gn_sep_bal > 0 ) THEN
1398
1399 dim_xml_tbl(i).value := '1';
1400
1401 ELSIF ( dim_xml_tbl(i).name = 'ASSIMILATED_SALARIES' AND
1402 gn_ass_bal > 0 ) THEN
1403
1404 dim_xml_tbl(i).value := '1';
1405
1406 ELSIF ( dim_xml_tbl(i).name = 'ER_PAYMENT_TO_EE' AND
1407 gn_emp_bal > 0 ) THEN
1408
1409 dim_xml_tbl(i).value := '1';
1410
1411 END IF;
1412
1413 END LOOP;
1414
1415 END IF;
1416
1417 IF gn_success_fail = 0 THEN
1418 load_xml_internal('CS','SUCCESS',NULL);
1419 ELSE
1420 load_xml_internal('CS','FAIL',NULL);
1421 END IF;
1422
1423 --load_xml_internal('CS','EMPLOYEE',NULL);
1424
1425 FOR i IN dim_xml_tbl.FIRST..dim_xml_tbl.LAST LOOP
1426
1427 load_xml_internal('D', dim_xml_tbl(i).name, dim_xml_tbl(i).value);
1428
1429 END LOOP;
1430
1431 --load_xml_internal('CE','EMPLOYEE',NULL);
1432
1433 IF gn_success_fail = 0 THEN
1434 load_xml_internal('CE','SUCCESS',NULL);
1435 ELSE
1436 load_xml_internal('CE','FAIL',NULL);
1437 END IF;
1438
1439 hr_utility_trace ('Leaving '||l_proc_name);
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 hr_utility_trace (SQLERRM);
1443 RAISE;
1444 END GENERATE_XML;
1445
1446
1447 /****************************************************************************
1448 Name : GENERATE_XML_HEADER
1449 Description : This procedure generates XML header information and appends to
1450 pay_mag_tape.g_blob_value.
1451 *****************************************************************************/
1452 PROCEDURE generate_xml_header AS
1453 l_proc_name varchar2(100);
1454 lv_buf varchar2(2000);
1455 BEGIN
1456 l_proc_name := g_proc_name || 'GENERATE_XML_HEADER';
1457 hr_utility_trace ('Entering '||l_proc_name);
1458
1459 hr_utility_trace ('Root XML tag = '||
1460 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
1461
1462 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
1463
1464 write_to_magtape_lob (lv_buf);
1465
1466 hr_utility_trace ('Leaving '||l_proc_name);
1467 END generate_xml_header;
1468
1469
1470 /****************************************************************************
1471 Name : GENERATE_XML_FOOTER
1472 Description : This procedure generates XML information for GRE and the final
1473 closing tag. Final result is appended to
1474 pay_mag_tape.g_blob_value.
1475 *****************************************************************************/
1476 PROCEDURE generate_xml_footer AS
1477
1478 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
1479 ln_pact_id number;
1480 l_xml BLOB;
1481 l_proc_name varchar2(100);
1482 ln_chars number;
1483 ln_offset number;
1484 lv_buf varchar2(8000);
1485 lr_xml RAW (32767);
1486 ln_amt number;
1487 BEGIN
1488 l_proc_name := g_proc_name || 'GENERATE_XML_FOOTER';
1489 hr_utility_trace ('Entering '||l_proc_name);
1490
1491 lv_buf := '</' ||
1492 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
1493 2);
1494
1495 write_to_magtape_lob (lv_buf);
1496
1497
1498 hr_utility_trace ('Leaving '||l_proc_name);
1499 END generate_xml_footer;
1500
1501 BEGIN
1502 --hr_utility.trace_on(null, 'PAYMXDIM');
1503 g_proc_name := 'PAY_MX_DIM_MAG.';
1504 g_debug := hr_utility.debug_enabled;
1505 g_document_type := 'MX_DIM_MAG';
1506 END PAY_MX_DIM_MAG;