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