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