[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_RULES
Source
1 PACKAGE BODY pay_ip_rules AS
2 /* $Header: payiprules.pkb 120.0.12020000.1 2012/08/30 08:18:07 schowl noship $ */
3 --
4
5
6 g_proc_name varchar2(100);
7 g_debug BOOLEAN;
8
9 /****************************************************************************
10 Name : HR_UTILITY_TRACE
11 Description : This procedure prints debug messages.
12 *****************************************************************************/
13 PROCEDURE HR_UTILITY_TRACE
14 (
15 P_TRC_DATA varchar2
16 ) AS
17 BEGIN
18 IF g_debug THEN
19 hr_utility.trace(p_trc_data);
20 END IF;
21 END HR_UTILITY_TRACE;
22
23
24 /****************************************************************************
25 Name : LOAD_XML
26 Description : This procedure loads the global XML cache.
27 *****************************************************************************/
28 PROCEDURE LOAD_XML (
29 P_XML varchar2
30 ) AS
31 l_proc_name varchar2(100);
32 l_data pay_action_information.action_information1%type;
33
34 BEGIN
35 l_proc_name := g_proc_name || 'LOAD_XML';
36 hr_utility_trace ('Entering '||l_proc_name);
37 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()):=
38 p_xml;
39 hr_utility_trace ('Leaving '||l_proc_name);
40 END LOAD_XML;
41
42
43 /****************************************************************************
44 Name : PREPARE_XML
45 Description : This procedure prepares the XML to be loaded in XML cache.
46 *****************************************************************************/
47 PROCEDURE PREPARE_XML (
48 P_NODE_TYPE varchar2,
49 P_NODE varchar2,
50 P_DATA varchar2
51 ) AS
52 l_proc_name varchar2(100);
53 l_data pay_action_information.action_information1%type;
54
55 BEGIN
56 l_proc_name := g_proc_name || 'PREPARE_XML';
57 hr_utility_trace ('Entering '||l_proc_name);
58
59 IF p_node_type = 'CS' THEN
60 load_xml ('<'||p_node||'>');
61 ELSIF p_node_type = 'CE' THEN
62 load_xml ('</'||p_node||'>');
63 ELSIF p_node_type = 'D' THEN
64 /* Handle special charaters in data */
65 l_data := REPLACE (p_data, '&', '&');
66 l_data := REPLACE (l_data, '>', '>');
67 l_data := REPLACE (l_data, '<', '<');
68 l_data := REPLACE (l_data, '''', ''');
69 l_data := REPLACE (l_data, '"', '"');
70 load_xml ('<'||p_node||'>'||l_data||'</'||p_node||'>');
71 END IF;
72
73 hr_utility_trace ('Leaving '||l_proc_name);
74 END PREPARE_XML;
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 PROCEDURE add_custom_xml
169 (p_assignment_action_id IN NUMBER,
170 p_action_information_category IN VARCHAR2,
171 p_document_type IN VARCHAR2) as
172
173 CURSOR c_get_bus_grp_id(p_tax_unit_id IN NUMBER) IS
174 SELECT hou.business_group_id
175 FROM hr_organization_units hou
176 WHERE hou.organization_id = p_tax_unit_id;
177
178 CURSOR c_get_bus_group_id(cp_assignment_action_id IN NUMBER) IS
179 SELECT paaf.business_group_id
180 FROM per_all_assignments_f paaf, pay_assignment_actions paa
181 WHERE paa.assignment_action_id = cp_assignment_action_id
182 AND paa.assignment_id = paaf.assignment_id
183 AND sysdate BETWEEN paaf.effective_start_date
184 AND paaf.effective_end_date;
185
186 CURSOR get_tax_unit_id(p_asg_action_id IN NUMBER) IS
187 SELECT tax_unit_id
188 FROM pay_assignment_actions
189 WHERE assignment_action_id = p_asg_action_id;
190
191 CURSOR c_get_employer_information(p_org_id IN NUMBER) IS
192 SELECT hou.name
193 ,hou.location_id
194 FROM hr_organization_units hou
195 ,hr_organization_information hoi
196 WHERE hou.organization_id = p_org_id
197 AND hoi.organization_id = hou.organization_id
198 AND hoi.org_information1 = 'HR_LEGAL';
199
200 CURSOR c_get_org_info(cp_assignment_action_id IN NUMBER) IS
201 SELECT DISTINCT hou.name
202 ,hou.location_id
203 FROM hr_organization_units hou
204 ,hr_organization_information hoi
205 ,per_all_assignments_f paaf
206 ,pay_assignment_actions paa
207 WHERE paa.assignment_action_id = cp_assignment_action_id
208 AND paa.assignment_id = paaf.assignment_id
209 AND hou.organization_id = paaf.organization_id
210 AND hoi.organization_id = hou.organization_id;
211
212 CURSOR c_get_employer_address(p_location_id IN NUMBER) IS
213 SELECT location.address_line_1,
214 location.address_line_2, location.address_line_3,
218 WHERE location.location_id = p_location_id;
215 location.town_or_city, location.postal_code,
216 location.country,location.region_2
217 FROM hr_locations location
219
220 CURSOR c_get_ee_information(p_asg_action_id number) IS
221 SELECT ppf.per_information1,
222 ppf.first_name,
223 ppf.last_name,
224 ppf.middle_names,
225 ppf.order_name,
226 ppf.full_name,
227 paf.payroll_id
228 FROM pay_assignment_actions paa,
229 pay_payroll_actions ppa,
230 per_assignments_f paf,
231 per_people_f ppf
232 WHERE paa.assignment_action_id = p_asg_action_id
233 AND paa.payroll_action_id = ppa.payroll_action_id
234 AND paf.assignment_id = paa.assignment_id
235 AND ppa.effective_date BETWEEN paf.effective_start_date
236 AND paf.effective_end_date
237 AND paf.person_id = ppf.person_id
238 AND ppa.effective_date BETWEEN ppf.effective_start_date
239 AND ppf.effective_end_date;
240
241 CURSOR c_get_payroll_name(p_payroll_id number) IS
242 SELECT payroll_name
243 FROM pay_payrolls_f
244 where payroll_id = p_payroll_id;
245
246 CURSOR c_get_userdefined_proc(cp_business_group_id IN NUMBER,
247 cp_legislation_code IN VARCHAR2) is
248 SELECT nvl(ORG_INFORMATION2,NULL) FROM
249 hr_organization_information
250 WHERE organization_id = cp_business_group_id
251 AND ORG_INFORMATION_CONTEXT = cp_legislation_code||'_REPORTING_PREFERENCES';
252
253 CURSOR c_get_legislation_code (cp_business_group IN NUMBER) IS
254 SELECT org_information9
255 FROM hr_organization_information
256 WHERE org_information_context = 'Business Group Information'
257 AND organization_id = cp_business_group;
258
259 CURSOR c_get_employer_info(cp_payroll_action_id IN NUMBER) IS
260 SELECT ppa.business_group_id, hoi.org_information9
261 FROM hr_organization_information hoi, pay_payroll_actions ppa
262 WHERE hoi.org_information_context = 'Business Group Information'
263 AND hoi.organization_id = ppa.business_group_id
264 AND ppa.payroll_action_id = cp_payroll_action_id;
265
266 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
267
268
269 ln_tax_unit_id number;
270 ln_bus_grp_id number;
271 lv_legislation_code varchar2(3);
272 lv_org_name hr_organization_units.name%TYPE;
273 ln_location_id hr_organization_units.location_id%TYPE;
274 lv_address_line1 hr_locations.address_line_1%TYPE;
275 lv_address_line2 hr_locations.address_line_2%TYPE;
276 lv_address_line3 hr_locations.address_line_3%TYPE;
277 lv_town_or_city hr_locations.town_or_city%TYPE;
278 lv_postal_code hr_locations.postal_code%TYPE;
279 lv_country hr_locations.country%TYPE;
280 lv_state hr_locations.region_2%TYPE;
281 lv_maternal_last_name per_people_f.per_information1%TYPE;
282 lv_first_name per_people_f.first_name%TYPE;
283 lv_last_name per_people_f.last_name%TYPE;
284 lv_middle_names per_people_f.middle_names%TYPE;
285 lv_order_name per_people_f.order_name%TYPE;
286 lv_full_name per_people_f.full_name%TYPE;
287 lv_payroll_id pay_payrolls_f.payroll_id%TYPE;
288 lv_payroll_name pay_payrolls_f.payroll_name%TYPE;
289 ln_per_pay_method number;
290 lv_account_type varchar2(2);
291 l_org_payment_method_id
292 pay_personal_payment_methods_f.org_payment_method_id%TYPE;
293
294 ln_legal_employer_id number;
295 l_employer_xml char_tab;
296 ld_effective_date date;
297 lv_gre_name pay_action_information.action_information1%type;
298 lv_trans_gre_name pay_action_information.action_information1%type;
299 lv_paternal_last_name pay_action_information.action_information1%type;
300 lv_employee_name pay_action_information.action_information1%type;
301 lv_location_id pay_action_information.action_information1%type;
302 l_proc_name varchar2(100);
303 lv_userdefined_proc_er varchar2(150);
304 lv_userdefined_proc_ee varchar2(150);
305
306 FUNCTION get_param_val (p_param_name varchar2) return varchar2 is
307 BEGIN
308 FOR cntr in pay_payroll_xml_extract_pkg.g_custom_params.first()..
309 pay_payroll_xml_extract_pkg.g_custom_params.last()
310 LOOP
311 IF pay_payroll_xml_extract_pkg.g_custom_params(cntr).parameter_name
312 = p_param_name
313 THEN
314 hr_utility_trace('Custom Params : '||
315 pay_payroll_xml_extract_pkg.g_custom_params(cntr).parameter_name);
316
317 hr_utility_trace('Custom Params Value : '||
318 pay_payroll_xml_extract_pkg.g_custom_params(cntr).
319 parameter_value);
320
321 RETURN pay_payroll_xml_extract_pkg.g_custom_params(cntr).
322 parameter_value;
323 END IF;
324
325 END LOOP;
326
327 RETURN NULL;
328 END;
329
330 BEGIN
331 l_proc_name := g_proc_name || 'ADD_CUSTOM_XML';
332 hr_utility_trace('Entering '||l_proc_name);
333 hr_utility_trace('p_assignment_action_id '|| p_assignment_action_id);
334 hr_utility_trace('p_action_information_category '||
335 p_action_information_category);
336 hr_utility_trace('p_document_type '|| p_document_type);
337
338 /* For Direct Deposit */
339
340 IF (p_document_type = 'DEPOSIT_XML') AND
341 (p_assignment_action_id IS NOT NULL) AND
342 (get_param_val('p_xml_level') = 'ER') THEN
343
344 hr_utility.trace('Entered pay_ip_rules.add_custom _xml Employer section');
345
346 OPEN c_get_employer_info(p_assignment_action_id);
347 FETCH c_get_employer_info INTO ln_bus_grp_id, lv_legislation_code;
348 CLOSE c_get_employer_info;
349
350 OPEN c_get_userdefined_proc(ln_bus_grp_id, lv_legislation_code);
351 FETCH c_get_userdefined_proc INTO lv_userdefined_proc_er;
352 CLOSE c_get_userdefined_proc;
353
354 hr_utility.trace('lv_userdefined_proc_er '|| lv_userdefined_proc_er);
355
356 IF lv_userdefined_proc_er IS NOT NULL THEN
357 EXECUTE IMMEDIATE 'BEGIN '||lv_userdefined_proc_er||'(:1,:2,:3); END;'
358 USING IN p_assignment_action_id,'DIRECT_DEPOSIT','DEPOSIT_HEADER';
359 END IF;
360
361 IF lv_userdefined_proc_er IS NOT NULL THEN
362 EXECUTE IMMEDIATE 'BEGIN '||lv_userdefined_proc_er||'(:1,:2,:3); END;'
363 USING IN p_assignment_action_id,'DIRECT_DEPOSIT','';
364 END IF;
365
366 END IF;
367
368 IF (p_document_type = 'DEPOSIT_XML') AND
369 (p_assignment_action_id IS NOT NULL) AND
370 (get_param_val('p_xml_level') = 'EE') THEN
371
372 hr_utility.trace('Entered pay_ip_rules.add_custom _xml Employee section');
373
374 OPEN get_tax_unit_id(p_assignment_action_id);
375 FETCH get_tax_unit_id INTO ln_tax_unit_id;
376 CLOSE get_tax_unit_id;
377
378 hr_utility_trace('ln_tax_unit_id '|| ln_tax_unit_id);
379
380 IF ln_tax_unit_id IS NOT NULL THEN
381 OPEN c_get_bus_grp_id(ln_tax_unit_id);
382 FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
383 CLOSE c_get_bus_grp_id;
384 ELSE
385 OPEN c_get_bus_group_id(p_assignment_action_id);
386 FETCH c_get_bus_group_id INTO ln_bus_grp_id;
387 CLOSE c_get_bus_group_id;
388 END IF;
389
390 hr_utility_trace('ln_bus_grp_id '|| ln_bus_grp_id);
391
392 OPEN c_get_legislation_code(ln_bus_grp_id);
393 FETCH c_get_legislation_code INTO lv_legislation_code;
394 CLOSE c_get_legislation_code;
395
396 hr_utility.trace('lv_legislation_code '|| lv_legislation_code);
397
398 OPEN c_get_userdefined_proc(ln_bus_grp_id, lv_legislation_code);
399 FETCH c_get_userdefined_proc INTO lv_userdefined_proc_ee;
400 CLOSE c_get_userdefined_proc;
401
402 hr_utility.trace('lv_userdefined_proc_ee '|| lv_userdefined_proc_ee);
403
404 IF ln_tax_unit_id IS NOT NULL THEN
405 OPEN c_get_employer_information(ln_tax_unit_id);
406 FETCH c_get_employer_information INTO lv_org_name, ln_location_id;
407 CLOSE c_get_employer_information;
408 ELSE
409 OPEN c_get_org_info(p_assignment_action_id);
410 FETCH c_get_org_info INTO lv_org_name, ln_location_id;
411 CLOSE c_get_org_info;
412 END IF;
413
414 OPEN c_get_employer_address(ln_location_id);
415 FETCH c_get_employer_address INTO lv_address_line1
416 ,lv_address_line2
417 ,lv_address_line3
418 ,lv_town_or_city
419 ,lv_postal_code
420 ,lv_country
421 ,lv_state;
422 CLOSE c_get_employer_address;
423
424 OPEN c_get_ee_information (p_assignment_action_id);
425 FETCH c_get_ee_information INTO
426 lv_maternal_last_name,
427 lv_first_name,
428 lv_last_name,
429 lv_middle_names,
430 lv_order_name,
431 lv_full_name,
432 lv_payroll_id;
433 CLOSE c_get_ee_information;
434
435 OPEN c_get_payroll_name (lv_payroll_id);
436 FETCH c_get_payroll_name INTO lv_payroll_name;
437 CLOSE c_get_payroll_name;
438
439 ln_per_pay_method :=
440 pay_magtape_generic.get_parameter_value(
441 'TRANSFER_PERSONAL_PAY_METH');
442
443 ld_effective_date :=
444 fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value(
445 'TRANSFER_EFFECTIVE_DATE'));
446
447 hr_utility_trace('lv_org_name '|| lv_org_name);
448 hr_utility_trace('lv_address_line1 '|| lv_address_line1);
449 hr_utility_trace('lv_address_line2 '|| lv_address_line2);
450 hr_utility_trace('lv_address_line3 '|| lv_address_line3);
451 hr_utility_trace('lv_town_or_city '|| lv_town_or_city);
452 hr_utility_trace('lv_postal_code '|| lv_postal_code);
453 hr_utility_trace('lv_country '|| lv_country);
454 hr_utility_trace('lv_state '|| lv_state);
455
456 hr_utility.trace('***Employee Information***');
457 hr_utility_trace('lv_maternal_last_name '|| lv_maternal_last_name);
458 hr_utility_trace('lv_first_name '|| lv_first_name);
459 hr_utility_trace('lv_last_name '|| lv_last_name);
460 hr_utility_trace('lv_middle_names '|| lv_middle_names);
461 hr_utility_trace('lv_order_name '|| lv_order_name);
462 hr_utility_trace('lv_full_name '|| lv_full_name);
463 hr_utility_trace('lv_payroll_id '|| lv_payroll_id);
464 hr_utility_trace('lv_payroll_name '|| lv_payroll_name);
465
466 pay_payroll_xml_extract_pkg.load_xml_data('D','EMPLOYER_NAME',
467 lv_org_name);
468 pay_payroll_xml_extract_pkg.load_xml_data('D','EMPLOYER_NAME_REPORTING',
469 replace(strip_spl_chars (lv_org_name),'/','N'));
470 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE1',
471 lv_address_line1);
472 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE2',
473 lv_address_line2);
474 pay_payroll_xml_extract_pkg.load_xml_data('D','ADDRESS_LINE3',
475 lv_address_line3);
476 pay_payroll_xml_extract_pkg.load_xml_data('D','CITY',lv_town_or_city);
477 pay_payroll_xml_extract_pkg.load_xml_data('D','STATE',lv_state);
478 pay_payroll_xml_extract_pkg.load_xml_data('D','COUNTRY',lv_country);
479 pay_payroll_xml_extract_pkg.load_xml_data('D','POSTAL_CODE',
480 lv_postal_code);
481 pay_payroll_xml_extract_pkg.load_xml_data('D','ORDER_NAME_REPORTING',
482 replace(strip_spl_chars (lv_order_name),'/','N'));
483
484
485 IF lv_userdefined_proc_ee IS NOT NULL THEN
486 EXECUTE IMMEDIATE 'BEGIN '||lv_userdefined_proc_ee||'(:1,:2,:3); END;'
487 USING IN p_assignment_action_id,'DIRECT_DEPOSIT','DEPOSIT_DETAILS';
488 END IF;
489
490 IF lv_userdefined_proc_ee IS NOT NULL THEN
491 EXECUTE IMMEDIATE 'BEGIN '||lv_userdefined_proc_ee||'(:1,:2,:3); END;'
492 USING IN p_assignment_action_id,'DIRECT_DEPOSIT','';
493 END IF;
494 END IF;
495
496 hr_utility_trace('Leaving '||l_proc_name);
497 EXCEPTION
498 WHEN OTHERS THEN
499 hr_utility_trace (SQLERRM);
500 RAISE;
501 end;
502
503 BEGIN
504 g_proc_name := 'PAY_IP_RULES.';
505 g_debug := hr_utility.debug_enabled;
506 END pay_ip_rules;