[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_MX_RULES
Source
1 PACKAGE BODY pay_mx_rules AS
2 /* $Header: pymxrule.pkb 120.14 2012/01/19 10:41:26 rpahune ship $ */
3 --
4
5 g_proc_name varchar2(100);
6 g_debug BOOLEAN;
7
8 /****************************************************************************
9 Name : HR_UTILITY_TRACE
10 Description : This procedure prints debug messages.
11 *****************************************************************************/
12 PROCEDURE HR_UTILITY_TRACE
13 (
14 P_TRC_DATA varchar2
15 ) AS
16 BEGIN
17 IF g_debug THEN
18 hr_utility.trace(p_trc_data);
19 END IF;
20 END HR_UTILITY_TRACE;
21
22
23 /****************************************************************************
24 Name : LOAD_XML
25 Description : This procedure loads the global XML cache.
26 *****************************************************************************/
27 PROCEDURE LOAD_XML (
28 P_XML varchar2
29 ) AS
30 l_proc_name varchar2(100);
31 l_data pay_action_information.action_information1%type;
32
33 BEGIN
34 l_proc_name := g_proc_name || 'LOAD_XML';
35 hr_utility_trace ('Entering '||l_proc_name);
36 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()):=
37 p_xml;
38 hr_utility_trace ('Leaving '||l_proc_name);
39 END LOAD_XML;
40
41
42 /****************************************************************************
43 Name : PREPARE_XML
44 Description : This procedure prepares the XML to be loaded in XML cache.
45 *****************************************************************************/
46 PROCEDURE PREPARE_XML (
47 P_NODE_TYPE varchar2,
48 P_NODE varchar2,
49 P_DATA varchar2
50 ) AS
51 l_proc_name varchar2(100);
52 l_data pay_action_information.action_information1%type;
53
54 BEGIN
55 l_proc_name := g_proc_name || 'PREPARE_XML';
56 hr_utility_trace ('Entering '||l_proc_name);
57
58 IF p_node_type = 'CS' THEN
59 load_xml ('<'||p_node||'>');
60 ELSIF p_node_type = 'CE' THEN
61 load_xml ('</'||p_node||'>');
62 ELSIF p_node_type = 'D' THEN
63 /* Handle special charaters in data */
64 l_data := REPLACE (p_data, '&', '&');
65 l_data := REPLACE (l_data, '>', '>');
66 l_data := REPLACE (l_data, '<', '<');
67 l_data := REPLACE (l_data, '''', ''');
68 l_data := REPLACE (l_data, '"', '"');
69 load_xml ('<'||p_node||'>'||l_data||'</'||p_node||'>');
70 END IF;
71
72 hr_utility_trace ('Leaving '||l_proc_name);
73 END PREPARE_XML;
74
75
76 /****************************************************************************
77 Name : STRIP_SPL_CHARS
78 Description : This function converts special characters into equivalent
79 ASCII characters.
80 *****************************************************************************/
81 FUNCTION STRIP_SPL_CHARS ( P_IN_STRING IN VARCHAR2)
82 RETURN VARCHAR2 AS
83
84 CURSOR get_repl_char (cp_hex_code varchar2) IS
85 SELECT tag
86 FROM fnd_lookup_values
87 WHERE lookup_type = 'MX_SS_SPL_CHARS'
88 AND lookup_code = cp_hex_code;
89
90 l_proc_name varchar2(100);
91 lv_db_charset varchar2(50);
92 lv_repl_char varchar2(10);
93 lv_curr_char varchar2(10);
94 lv_hex_code varchar2(10);
95 lv_conv_string varchar2(32000);
96 lv_return varchar2(32000);
97
98
99 BEGIN
100 l_proc_name := g_proc_name || 'STRIP_SPL_CHARS';
101 hr_utility_trace ('Entering '||l_proc_name);
102 hr_utility_trace ('p_in_string = '||p_in_string);
103
104 lv_db_charset := SUBSTR(USERENV('LANGUAGE'),
105 INSTR(USERENV('LANGUAGE'), '.') + 1);
106
107 hr_utility_trace ('lv_db_charset = '||lv_db_charset);
108
109 IF lv_db_charset = 'WE8ISO8859P1' THEN
110 lv_conv_string := upper(p_in_string);
111 ELSE
112 lv_conv_string := CONVERT(p_in_string, 'UTF8', lv_db_charset);
113 END IF;
114
115 FOR cntr IN 1..NVL(length (lv_conv_string),0) LOOP
116 lv_repl_char := 'NONE';
117
118 lv_curr_char := SUBSTR(lv_conv_string, cntr, 1);
119 hr_utility_trace ('Current char = "'|| lv_curr_char ||'"');
120
121 IF lv_db_charset <> 'WE8ISO8859P1' THEN
122 lv_hex_code := RAWTOHEX (UTL_RAW.cast_to_RAW(lv_curr_char));
123 hr_utility_trace ('Hex Code = '|| lv_hex_code);
124
125 OPEN get_repl_char (lv_hex_code);
126 FETCH get_repl_char INTO lv_repl_char;
127 CLOSE get_repl_char;
128 ELSE
129 lv_repl_char := ASCII(lv_curr_char);
130 hr_utility_trace('lv_repl_char '||lv_repl_char);
131
132 IF lv_repl_char = 193 THEN
133 lv_repl_char := 'A';
134 ELSIF lv_repl_char = 201 THEN
135 lv_repl_char := 'E';
136 ELSIF lv_repl_char = 205 THEN
137 lv_repl_char := 'I';
138 ELSIF lv_repl_char = 209 THEN -- This is for N
139 lv_repl_char := '/';
140 ELSIF lv_repl_char = 211 THEN
141 lv_repl_char := 'O';
142 ELSIF lv_repl_char = 218 THEN
143 lv_repl_char := 'U';
144 ELSE
145 lv_repl_char := lv_curr_char;
146 END IF;
147
148 END IF;
149
150 IF lv_repl_char = 'NONE' THEN
151 hr_utility_trace ('Character "' || lv_curr_char ||'" not replaced');
152 lv_return := lv_return || lv_curr_char;
153 ELSE
154 hr_utility_trace ('Character "' ||lv_curr_char ||'" replaced by ' ||
155 lv_repl_char);
156 lv_return := lv_return || lv_repl_char;
157 END IF;
158 hr_utility_trace ('-------------');
159 END LOOP;
160
161 lv_return := UPPER(CONVERT(lv_return, lv_db_charset, 'UTF8'));
162
163 hr_utility_trace ('lv_return = '||lv_return);
164 hr_utility_trace ('Leaving '||l_proc_name);
165 RETURN (lv_return);
166 END STRIP_SPL_CHARS;
167
168
169 PROCEDURE get_main_tax_unit_id(p_assignment_id IN NUMBER,
170 p_effective_date IN DATE,
171 p_tax_unit_id IN OUT NOCOPY NUMBER) IS
172
173
174 CURSOR csr_get_asg_details IS
175 SELECT location_id,
176 soft_coding_keyflex_id,
177 business_group_id
178 FROM per_all_assignments_f
179 WHERE assignment_id = p_assignment_id
180 AND p_effective_date BETWEEN effective_start_date
181 AND effective_end_date;
182
183 CURSOR csr_get_asg_details_ptu IS -- Bug:9753792
184 SELECT location_id,
185 soft_coding_keyflex_id,
186 business_group_id
187 FROM per_all_assignments_f
188 WHERE assignment_id = p_assignment_id
189 AND effective_start_date <= g_ptu_end_date
190 AND effective_end_date >= g_ptu_start_date;
191
192 l_hsck_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
193 l_location_id per_all_assignments_f.location_id%TYPE;
194 l_bg_id per_all_assignments_f.business_group_id%TYPE;
195 l_ppa_eff_date DATE;
196 l_ambiguous_flag BOOLEAN;
197 l_missing_flag BOOLEAN;
198
199 BEGIN
200 --
201 p_tax_unit_id := NULL;
202
203 IF g_ptu_process THEN -- Bug:9753792
204
205 OPEN csr_get_asg_details_ptu;
206 FETCH csr_get_asg_details_ptu INTO l_location_id, l_hsck_id, l_bg_id;
207 CLOSE csr_get_asg_details_ptu;
208
209 ELSE
210
211 OPEN csr_get_asg_details;
212 FETCH csr_get_asg_details INTO l_location_id, l_hsck_id, l_bg_id;
213 CLOSE csr_get_asg_details;
214
215 END IF;
216
217 p_tax_unit_id := nvl( hr_mx_utility.get_GRE_from_scl(l_hsck_id),
218
219 hr_mx_utility.get_gre_from_location(l_location_id,
220 l_bg_id,
221 p_effective_date,
222 l_ambiguous_flag,
223 l_missing_flag )
224 );
225
226 --
227 END get_main_tax_unit_id;
228
229 --
230 PROCEDURE get_default_jurisdiction( p_asg_act_id IN NUMBER
231 ,p_ee_id IN NUMBER
232 ,p_jurisdiction IN OUT NOCOPY VARCHAR2)
233 IS
234
235 CURSOR csr_get_jd (cp_assignment_action_id NUMBER) IS
236 SELECT hl.region_1
237 FROM hr_locations_all hl
238 ,per_all_assignments_f paf
239 ,pay_assignment_actions paa
240 ,fnd_sessions fs
241 WHERE paa.assignment_action_id = cp_assignment_action_id
242 AND fs.session_id = userenv('sessionid')
243 AND paf.assignment_id = paa.assignment_id
244 AND fs.effective_date BETWEEN paf.effective_start_date
245 AND paf.effective_end_date
246 AND hl.location_id = paf.location_id;
247
248 BEGIN
249
250 OPEN csr_get_jd(p_asg_act_id);
251 FETCH csr_get_jd INTO p_jurisdiction;
252 CLOSE csr_get_jd;
253
254 END get_default_jurisdiction;
255
256 --
257
258
259 FUNCTION element_template_pre_process( p_rec in PAY_ELE_TMPLT_OBJ )
260 RETURN PAY_ELE_TMPLT_OBJ IS
261
262 l_rec PAY_ELE_TMPLT_OBJ;
263 BEGIN
264 l_rec := p_rec;
265
266 hr_utility_trace('Entering pay_mx_rules.element_template_pre_process');
267
268 hr_utility_trace('Legislation Code '||l_rec.legislation_code);
269
270 IF ( instr( p_rec.element_classification, 'Earnings' ) > 0 OR
271 p_rec.element_classification = 'Amends' ) THEN
272
273 l_rec.process_mode := 'S';
274
275 IF p_rec.element_classification = 'Employer Liabilities' THEN
276
277 l_rec.configuration_information8 := 'Y';
278
279 ELSE
280
281 l_rec.configuration_information8 := 'N';
282
283 END IF;
284
285 END IF;
286
287
288 IF ( instr( p_rec.element_classification, 'Deduction' ) > 0 ) THEN
289
290 IF p_rec.configuration_information1 in ( 'A', 'APD' ) THEN
291
292 l_rec.configuration_information2 := 'Y';
293
294 ELSE
295
296 l_rec.configuration_information2 := 'N';
297
298 END IF;
299
300 END IF;
301
302 hr_utility_trace('Leaving pay_mx_rules.element_template_pre_process');
303
304 RETURN l_rec;
305
306 END element_template_pre_process;
307
308 PROCEDURE element_template_post_process( p_element_template_id in NUMBER )
309 IS
310
311 TYPE varchar_tab IS TABLE OF VARCHAR2(240)
312 INDEX BY BINARY_INTEGER;
313
314 CURSOR c_tmplt_info ( cp_element_template_id NUMBER ) IS
315 SELECT *
316 FROM pay_element_templates
317 WHERE template_id = cp_element_template_id
318 AND template_type = 'U';
319
320 CURSOR get_busgrp_info ( cp_business_group_id NUMBER ) IS
321 SELECT legislation_code
322 FROM per_business_groups
323 WHERE business_group_id = cp_business_group_id
324 AND organization_id = cp_business_group_id;
325
326 CURSOR get_classification_name( cp_template_id NUMBER ) IS
327 SELECT classification_name
328 FROM pay_shadow_element_types
329 WHERE template_id = cp_template_id;
330
331 CURSOR get_element_type_id( cp_business_group_id NUMBER
332 ,cp_element_name VARCHAR2 ) IS
333 SELECT element_type_id
334 FROM pay_element_types_f
335 WHERE business_group_id = cp_business_group_id
336 AND element_name = cp_element_name;
337
338 /*CURSOR get_core_element_type_id (cp_template_id NUMBER,
339 cp_shadow_element_type_id NUMBER) IS
340 SELECT core_object_id
341 FROM pay_template_core_objects
342 WHERE template_id = cp_template_id
343 AND shadow_object_id = cp_shadow_element_type_id
344 AND core_object_type = 'ET';*/
345
346 CURSOR get_sub_classifications (cp_element_type_id NUMBER) IS
347 SELECT pec.classification_name
348 FROM pay_element_classifications pec,
349 pay_sub_classification_rules pscr
350 WHERE pec.classification_id = pscr.classification_id
351 AND pscr.element_type_id = cp_element_type_id
352 AND pscr.business_group_id IS NOT NULL
353 AND pscr.legislation_code IS NULL
354 AND pec.parent_classification_id IS NOT NULL;
355
356 l_tmplt pay_element_templates%ROWTYPE;
357
358 lv_legislation_code VARCHAR2(100);
359 lv_classification_name VARCHAR2(240);
360 lv_context VARCHAR2(240);
361 ln_element_type_id NUMBER;
362
363 l_element_type_extra_info_id NUMBER;
364 lv_sub_classification_name
365 pay_element_classifications.classification_name%type;
366 l_object_version_number NUMBER;
367 ln_rate_type_count NUMBER;
368 l_rate_type varchar_tab;
369 l_rate_desc varchar_tab;
370 lv_eei_info6 VARCHAR2(240);
371 lv_eei_info10 VARCHAR2(240);
372 lv_eei_info11 VARCHAR2(240);
373 lv_eei_info13 VARCHAR2(240);
374
375 BEGIN
376
377 hr_utility_trace('Entering pay_mx_rules.element_template_post_process');
378
379 hr_utility_trace('p_element_template_id '|| p_element_template_id );
380
381 OPEN c_tmplt_info( p_element_template_id );
382 FETCH c_tmplt_info INTO l_tmplt;
383 CLOSE c_tmplt_info;
384
385 OPEN get_busgrp_info( l_tmplt.business_group_id );
386 FETCH get_busgrp_info INTO lv_legislation_code;
387 CLOSE get_busgrp_info;
388
389 OPEN get_classification_name( l_tmplt.template_id );
390 FETCH get_classification_name INTO lv_classification_name;
391 CLOSE get_classification_name;
392
393 OPEN get_element_type_id( l_tmplt.business_group_id
394 ,l_tmplt.base_name );
395 FETCH get_element_type_id INTO ln_element_type_id;
396 CLOSE get_element_type_id;
397
398 lv_context := lv_legislation_code || '_' || upper(lv_classification_name);
399
400 IF l_tmplt.template_name = 'Days X Rate' THEN
401
402 UPDATE pay_element_types_f
403 SET element_information_category = lv_context
404 ,element_information1 = l_tmplt.preference_information8
405 WHERE element_type_id = ln_element_type_id;
406
407 ELSIF instr( l_tmplt.template_name, 'Deduction' ) > 0 THEN
408
409 UPDATE pay_element_types_f
410 SET element_information_category = lv_context
411 ,element_information1 = l_tmplt.configuration_information1
412 WHERE element_type_id = ln_element_type_id;
413
414 /* If an element is deduction element and INFONAVIT is Yes
415 we need to create an extra info type Deduction Processing where
416 Type of Deduction should be INFONAVIT */
417
418 IF l_tmplt.configuration_information4 = 'Y' THEN
419
420 pay_element_extra_info_api.create_element_extra_info
421 (p_validate => FALSE
422 ,p_element_type_id => ln_element_type_id
423 ,p_information_type => 'MX_DEDUCTION_PROCESSING'
424 ,p_eei_information_category => 'MX_DEDUCTION_PROCESSING'
425 ,p_eei_information1 => 'INFONAVIT'
426 ,p_element_type_extra_info_id => l_element_type_extra_info_id
427 ,p_object_version_number => l_object_version_number
428 );
429
430 END IF;
431
432 END IF;
433
434 IF ( instr( lv_classification_name, 'Earnings' ) > 0 OR
435 lv_classification_name = 'Amends' OR
436 lv_classification_name = 'Employer Liabilities' ) THEN
437
438 IF ( l_tmplt.preference_information3 is NOT NULL)
439 THEN
440 lv_eei_info6 := NULL;
441 lv_eei_info11 := NULL;
442 lv_eei_info10 := NULL;
443 IF ( l_tmplt.preference_information4 = 'IV' ) THEN
444 lv_eei_info13 := 'Y';
445 ELSE
446 lv_eei_info13 := 'N';
447 lv_eei_info11 := 'X';
448 lv_eei_info10 := 'Y';
449 IF (l_tmplt.preference_information4 IN ('RT', 'EN')) THEN
450 lv_eei_info6 := 'X';
451 END IF;
452 END IF;
453
454 pay_element_extra_info_api.create_element_extra_info
455 (p_validate => FALSE
456 ,p_element_type_id => ln_element_type_id
457 ,p_information_type => 'PQP_UK_ELEMENT_ATTRIBUTION'
458 ,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
459 ,p_eei_information1 => l_tmplt.preference_information3
460 ,p_eei_information2 => l_tmplt.preference_information4
461 ,p_eei_information3 => l_tmplt.preference_information5
462 ,p_eei_information4 => 'H'
463 ,p_eei_information5 => 'N'
464 ,p_eei_information6 => lv_eei_info6
465 ,p_eei_information7 => l_tmplt.preference_information6
466 ,p_eei_information8 => l_tmplt.preference_information7
467 ,p_eei_information10 => lv_eei_info10
468 ,p_eei_information11 => lv_eei_info11
469 ,p_eei_information12 => 'Y'
470 ,p_eei_information13 => lv_eei_info13
471 ,p_eei_information14 => 'N'
472 ,p_element_type_extra_info_id => l_element_type_extra_info_id
473 ,p_object_version_number => l_object_version_number
474 );
475
476 END IF;
477
478 ln_rate_type_count := 0;
479 IF ( l_tmplt.preference_information1 = 'Y' ) THEN
480 ln_rate_type_count := ln_rate_type_count + 1;
481 l_rate_type(ln_rate_type_count) := 'MX_BASE';
482 l_rate_desc(ln_rate_type_count) :=
483 'The Base Pay (MX_BASE) rate type will include this element'||
484 ' in its rate calculation.';
485 END IF;
486
487 IF ( l_tmplt.preference_information2 = 'F' ) THEN
488 ln_rate_type_count := ln_rate_type_count + 1;
489 l_rate_type(ln_rate_type_count) := 'MX_IDWF';
490 l_rate_desc(ln_rate_type_count) :=
491 'The Fixed IDW (MX_IDWF) rate type will include this element'||
492 ' in its rate calculation.';
493 ELSIF ( l_tmplt.preference_information2 = 'V' ) THEN
494 ln_rate_type_count := ln_rate_type_count + 1;
495 l_rate_type(ln_rate_type_count) := 'MX_IDWV';
496 l_rate_desc(ln_rate_type_count) :=
497 'The Variable IDW(MX_IDWV) rate type will include this element'||
498 ' in its rate calculation.';
499 END IF;
500
501 FOR i in 1..l_rate_type.COUNT
502 LOOP
503 pay_element_extra_info_api.create_element_extra_info
504 (p_validate => FALSE
505 ,p_element_type_id => ln_element_type_id
506 ,p_information_type => 'PQP_UK_RATE_TYPE'
507 ,p_eei_information_category => 'PQP_UK_RATE_TYPE'
508 ,p_eei_information1 => l_rate_type(i)
509 ,p_eei_information2 => l_rate_desc(i)
510 ,p_element_type_extra_info_id => l_element_type_extra_info_id
511 ,p_object_version_number => l_object_version_number
512 );
513 hr_utility_trace('Created PQP_UK_RATE_TYPE:'||l_rate_type(i));
514 END LOOP;
515 END IF;
516
517 /* If the created element belongs to social foresight or Employer
518 Contributions to Savings Fund then update element's processing
519 priority to 4490 and 4480 respectively. This will ensure that
520 such earnings elements are processesed just before MEXICO_TAX
521 (whose processing priority is 4500). */
522
523 IF lv_classification_name in ('Supplemental Earnings',
524 'Imputed Earnings') THEN
525 OPEN get_sub_classifications (ln_element_type_id);
526 LOOP
527 FETCH get_sub_classifications INTO lv_sub_classification_name;
528 EXIT WHEN get_sub_classifications%NOTFOUND;
529
530 IF lv_sub_classification_name IN
531 ('Supplemental Earnings:Social Foresight Earnings',
532 'Imputed Earnings:Social Foresight Earnings') THEN
533 UPDATE pay_element_types_f
534 SET processing_priority = 4490
535 WHERE element_type_id = ln_element_type_id;
536 EXIT;
537 END IF;
538
539 IF lv_sub_classification_name =
540 'Employer Contribution to Savings Fund' THEN
541 UPDATE pay_element_types_f
542 SET processing_priority = 4480
543 WHERE element_type_id = ln_element_type_id;
544 EXIT;
545 END IF;
546 END LOOP;
547 CLOSE get_sub_classifications;
548 END IF;
549
550 hr_utility_trace('Leaving pay_mx_rules.element_template_post_process');
551
552 END element_template_post_process;
553
554 PROCEDURE add_custom_xml
555 (p_assignment_action_id number,
556 p_action_information_category varchar2,
557 p_document_type varchar2) as
558
559 CURSOR c_get_bus_grp_id(p_tax_unit_id number) IS
560 SELECT hou.business_group_id
561 FROM hr_organization_units hou
562 WHERE hou.organization_id = p_tax_unit_id;
563
564 CURSOR get_tax_unit_id(p_asg_action_id number) IS
565 SELECT tax_unit_id
566 FROM pay_assignment_actions
567 WHERE assignment_action_id = p_asg_action_id;
568
569 CURSOR c_get_employer_information(p_org_id number) IS
570 SELECT hoi.org_information1
571 ,hou.location_id
572 FROM hr_organization_units hou
573 ,hr_organization_information hoi
574 WHERE hou.organization_id = p_org_id
575 AND hoi.organization_id = hou.organization_id
576 AND hoi.org_information_context = 'MX_TAX_REGISTRATION';
577
578 CURSOR c_get_employer_address(p_location_id number) IS
579 SELECT location.address_line_1,
580 location.address_line_2, location.address_line_3,
581 location.town_or_city, location.postal_code,
582 location.country,location.region_2
583 FROM hr_locations location
584 WHERE location.location_id = p_location_id;
585
586 CURSOR c_get_ee_information(p_asg_action_id number) IS
587 SELECT ppf.per_information1,
588 ppf.first_name,
589 ppf.last_name,
590 ppf.middle_names,
591 ppf.order_name,
592 ppf.full_name,
593 paf.payroll_id
594 FROM pay_assignment_actions paa,
595 pay_payroll_actions ppa,
596 per_assignments_f paf,
597 per_people_f ppf
598 WHERE paa.assignment_action_id = p_asg_action_id
599 AND paa.payroll_action_id = ppa.payroll_action_id
600 AND paf.assignment_id = paa.assignment_id
601 AND ppa.effective_date BETWEEN paf.effective_start_date
602 AND paf.effective_end_date
603 AND paf.person_id = ppf.person_id
604 AND ppa.effective_date BETWEEN ppf.effective_start_date
605 AND ppf.effective_end_date;
606
607 CURSOR c_get_payroll_name(p_payroll_id number) IS
608 SELECT payroll_name
609 FROM pay_payrolls_f
610 where payroll_id = p_payroll_id;
611
612
613 CURSOR get_account_type(p_per_pay_method NUMBER,
614 p_effective_date DATE) IS
615 SELECT decode(segment4
616 ,'CHECK','01'
617 ,'MASTER','02'
618 ,'DEBIT','03'
619 ,'SAVINGS','04'
620 ,'OTHER','05'
621 ,substr(segment4,1,2)) segment4
622 ,org_payment_method_id
623 FROM pay_personal_payment_methods_f pppm,
624 pay_external_accounts pea
625 WHERE --pppm.assignment_id = p_assignment_id
626 pppm.personal_payment_method_id = p_per_pay_method
627 AND pppm.external_account_id = pea.external_account_id
628 AND p_effective_date between pppm.EFFECTIVE_START_DATE
629 and pppm.EFFECTIVE_END_DATE;
630
631 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
632
633
634 ln_tax_unit_id number;
635 ln_bus_grp_id number;
636 lv_org_name hr_organization_units.name%TYPE;
637 ln_location_id hr_organization_units.location_id%TYPE;
638 lv_address_line1 hr_locations.address_line_1%TYPE;
639 lv_address_line2 hr_locations.address_line_2%TYPE;
640 lv_address_line3 hr_locations.address_line_3%TYPE;
641 lv_town_or_city hr_locations.town_or_city%TYPE;
642 lv_postal_code hr_locations.postal_code%TYPE;
643 lv_country hr_locations.country%TYPE;
644 lv_state hr_locations.region_2%TYPE;
645 lv_maternal_last_name per_people_f.per_information1%TYPE;
646 lv_first_name per_people_f.first_name%TYPE;
647 lv_last_name per_people_f.last_name%TYPE;
648 lv_middle_names per_people_f.middle_names%TYPE;
649 lv_order_name per_people_f.order_name%TYPE;
650 lv_full_name per_people_f.full_name%TYPE;
651 lv_payroll_id pay_payrolls_f.payroll_id%TYPE;
652 lv_payroll_name pay_payrolls_f.payroll_name%TYPE;
653 ln_per_pay_method number;
654 lv_account_type varchar2(2);
655 l_org_payment_method_id
656 pay_personal_payment_methods_f.org_payment_method_id%TYPE;
657
658 ln_legal_employer_id number;
659 l_employer_xml char_tab;
660 ld_effective_date date;
661 lv_gre_name pay_action_information.action_information1%type;
662 lv_trans_gre_name pay_action_information.action_information1%type;
663 lv_paternal_last_name pay_action_information.action_information1%type;
664 lv_employee_name pay_action_information.action_information1%type;
665 lv_location_id pay_action_information.action_information1%type;
666 l_proc_name varchar2(100);
667 --l_xml CLOB;
668
669 CURSOR get_archived_info (cp_action_info_id number) IS
670 SELECT effective_date,
671 action_information2,
672 action_information4,
673 action_information5,
674 action_information6,
675 action_information7,
676 action_information12
677 FROM pay_action_information
678 WHERE action_information_id = cp_action_info_id;
679
680 FUNCTION get_param_val (p_param_name varchar2) return varchar2 is
681 BEGIN
682 FOR cntr in pay_payroll_xml_extract_pkg.g_custom_params.first()..
683 pay_payroll_xml_extract_pkg.g_custom_params.last()
684 LOOP
685 IF pay_payroll_xml_extract_pkg.g_custom_params(cntr).parameter_name
686 = p_param_name
687 THEN
688 hr_utility_trace('Custom Params : '||
689 pay_payroll_xml_extract_pkg.g_custom_params(cntr).parameter_name);
690
691 hr_utility_trace('Custom Params Value : '||
692 pay_payroll_xml_extract_pkg.g_custom_params(cntr).
693 parameter_value);
694
695 RETURN pay_payroll_xml_extract_pkg.g_custom_params(cntr).
696 parameter_value;
697 END IF;
698
699 END LOOP;
700
701 RETURN NULL;
702 END;
703
704 BEGIN
705 l_proc_name := g_proc_name || 'ADD_CUSTOM_XML';
706 hr_utility_trace('Entering '||l_proc_name);
707 hr_utility_trace('p_assignment_action_id '|| p_assignment_action_id);
708 hr_utility_trace('p_action_information_category '||
709 p_action_information_category);
710 hr_utility_trace('p_document_type '|| p_document_type);
711
712 /* For Direct Deposit */
713
714 IF (p_document_type = 'DEPOSIT_XML') AND
715 (p_assignment_action_id IS NOT NULL) AND
716 (get_param_val('p_xml_level') = 'ER') THEN
717
718 NULL;
719
720 END IF;
721
722 IF (p_document_type = 'DEPOSIT_XML') AND
723 (p_assignment_action_id IS NOT NULL) AND
724 (get_param_val('p_xml_level') = 'EE') THEN
725
726 OPEN get_tax_unit_id(p_assignment_action_id);
727 FETCH get_tax_unit_id INTO ln_tax_unit_id;
728 CLOSE get_tax_unit_id;
729
730 hr_utility_trace('ln_tax_unit_id '|| ln_tax_unit_id);
731
732 OPEN c_get_bus_grp_id(ln_tax_unit_id);
733 FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
734 CLOSE c_get_bus_grp_id;
735
736 hr_utility_trace('ln_bus_grp_id '|| ln_bus_grp_id);
737
738 ln_legal_employer_id :=
739 hr_mx_utility.get_legal_employer(ln_bus_grp_id, ln_tax_unit_id);
740 hr_utility_trace('ln_legal_employer_id '|| ln_legal_employer_id);
741
742
743 OPEN c_get_employer_information(ln_legal_employer_id);
744 FETCH c_get_employer_information INTO lv_org_name
745 ,ln_location_id;
746 CLOSE c_get_employer_information;
747
748 OPEN c_get_employer_address(ln_location_id);
749 FETCH c_get_employer_address INTO lv_address_line1
750 ,lv_address_line2
751 ,lv_address_line3
752 ,lv_town_or_city
753 ,lv_postal_code
754 ,lv_country
755 ,lv_state;
756 CLOSE c_get_employer_address;
757
758 OPEN c_get_ee_information (p_assignment_action_id);
759 FETCH c_get_ee_information INTO
760 lv_maternal_last_name,
761 lv_first_name,
762 lv_last_name,
763 lv_middle_names,
764 lv_order_name,
765 lv_full_name,
766 lv_payroll_id;
767 CLOSE c_get_ee_information;
768
769 OPEN c_get_payroll_name (lv_payroll_id);
770 FETCH c_get_payroll_name INTO lv_payroll_name;
771 CLOSE c_get_payroll_name;
772
773 ln_per_pay_method :=
774 pay_magtape_generic.get_parameter_value(
775 'TRANSFER_PERSONAL_PAY_METH');
776
777 ld_effective_date :=
778 fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value(
779 'TRANSFER_EFFECTIVE_DATE'));
780
781 OPEN get_account_type(ln_per_pay_method,ld_effective_date);
782 FETCH get_account_type INTO lv_account_type
783 ,l_org_payment_method_id;
784 CLOSE get_account_type;
785
786 hr_utility_trace('lv_org_name '|| lv_org_name);
787 hr_utility_trace('lv_address_line1 '|| lv_address_line1);
788 hr_utility_trace('lv_address_line2 '|| lv_address_line2);
789 hr_utility_trace('lv_address_line3 '|| lv_address_line3);
790 hr_utility_trace('lv_town_or_city '|| lv_town_or_city);
791 hr_utility_trace('lv_postal_code '|| lv_postal_code);
792 hr_utility_trace('lv_country '|| lv_country);
793 hr_utility_trace('lv_state '|| lv_state);
794 hr_utility_trace('lv_maternal_last_name '|| lv_maternal_last_name);
795
796 pay_payroll_xml_extract_pkg.load_xml_data('D','EMPLOYER_NAME',
797 lv_org_name);
798 pay_payroll_xml_extract_pkg.load_xml_data('D','EMPLOYER_NAME_REPORTING',
799 replace(strip_spl_chars (lv_org_name),'/','N'));
800 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE1',
801 lv_address_line1);
802 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE2',
803 lv_address_line2);
804 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE3',
805 lv_address_line3);
806 pay_payroll_xml_extract_pkg.load_xml_data('D','CITY',lv_town_or_city);
807 pay_payroll_xml_extract_pkg.load_xml_data('D','STATE',lv_state);
808 pay_payroll_xml_extract_pkg.load_xml_data('D','COUNTRY',lv_country);
809 pay_payroll_xml_extract_pkg.load_xml_data('D','POSTAL_CODE',
810 lv_postal_code);
811 pay_payroll_xml_extract_pkg.load_xml_data('D','MATERNAL_LAST_NAME',
812 lv_maternal_last_name);
813 pay_payroll_xml_extract_pkg.load_xml_data('D',
814 'MATERNAL_LAST_NAME_REPORTING',
815 replace(strip_spl_chars (lv_maternal_last_name),'/','N'));
816
817 pay_payroll_xml_extract_pkg.load_xml_data('D','FIRST_NAME_REPORTING',
818 replace(strip_spl_chars (lv_first_name),'/','N'));
819 pay_payroll_xml_extract_pkg.load_xml_data('D','LAST_NAME_REPORTING',
820 replace(strip_spl_chars (lv_last_name),'/','N'));
821 pay_payroll_xml_extract_pkg.load_xml_data('D','MIDDLE_NAMES_REPORTING',
822 replace(strip_spl_chars (lv_middle_names),'/','N'));
823 pay_payroll_xml_extract_pkg.load_xml_data('D','ORDER_NAME_REPORTING',
824 replace(strip_spl_chars (lv_order_name),'/','N'));
825 pay_payroll_xml_extract_pkg.load_xml_data('D','FULL_NAME_REPORTING',
826 replace(strip_spl_chars (lv_full_name),'/','N'));
827 pay_payroll_xml_extract_pkg.load_xml_data('D','PAYROLL_NAME_REPORTING',
828 replace(strip_spl_chars (lv_payroll_name),'/','N'));
829 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE_REPORTING',
830 lv_account_type);
831 END IF;
832
833 /* Custom XML for SUA Interface Extract and Social Security Affiliation
834 Report. */
835 IF p_document_type IN ('MX_SUA_MAG', 'MX_SS_AFFL') THEN
836 IF p_action_information_category IS NOT NULL THEN
837 OPEN get_archived_info (get_param_val ('action_information_id'));
838 FETCH get_archived_info INTO ld_effective_date,
839 lv_gre_name,
840 lv_trans_gre_name,
841 lv_paternal_last_name,
842 lv_maternal_last_name,
843 lv_employee_name,
844 lv_location_id;
845 CLOSE get_archived_info;
846
847 IF p_action_information_category = 'MX SS GRE INFORMATION' THEN
848 prepare_xml('D',
849 'GRE_NAME_REPORTING',
850 strip_spl_chars (lv_gre_name ));
851 prepare_xml('D',
852 'TRANSMITTER_GRE_NAME_REPORTING',
853 strip_spl_chars (lv_trans_gre_name ));
854
855 ELSIF p_action_information_category = 'MX SS PERSON INFORMATION'
856 THEN
857
858 prepare_xml('D',
859 'PATERNAL_LAST_NAME_REPORTING',
860 strip_spl_chars (lv_paternal_last_name ));
861 prepare_xml('D',
862 'MATERNAL_LAST_NAME_REPORTING',
863 strip_spl_chars (lv_maternal_last_name ));
864 prepare_xml('D',
865 'EMPLOYEE_NAME_REPORTING',
866 strip_spl_chars (lv_employee_name ));
867
868 hr_utility_trace ('Translating location_id ...');
869 prepare_xml('D',
870 'LOCATION_ID_REPORTING',
871 strip_spl_chars (lv_location_id ));
872
873 END IF;
874 END IF;
875 END IF;
876
877 hr_utility_trace('Leaving '||l_proc_name);
878 EXCEPTION
879 WHEN OTHERS THEN
880 hr_utility_trace (SQLERRM);
881 RAISE;
882 end;
883 BEGIN
884 g_proc_name := 'PAY_MX_RULES.';
885 g_debug := hr_utility.debug_enabled;
886 END pay_mx_rules;
887