1 package body pay_fr_rules as
2 /* $Header: pyfrrule.pkb 115.11 2004/01/12 07:44:46 aparkes noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993,1994. All rights reserved
5 --
6 Name : pay_fr_rules
7 --
8 Change List
9 -----------
10 Date Name Vers Description
11 ----------- ---------- ----- -------------------------------------------
12 01-FEB-2001 asnell 115.0 Created.
13 09-MAY-2001 jrhodes 115.1 added missing join to assignment_id
14 03-AUG-2001 jrhodes 115.2 Corrected join to element link
15 10-OCT-2002 srjadhav 115.4 Added procedure get_dynamic_org_meth
16 17-OCT-2002 srjadhav 115.5 Changed message numbers
17 25-NOV-2002 asnell 115.6 added nocopy to parms
18 10-APR-2003 aparkes 115.7 2898674 added get_multi_tax_unit_pay_flag
19 11-NOV-2003 aparkes 115.8 Added retro context override hook
20 08-JAN-2004 aparkes 115.9 Added get_source_text2_context (for bug
21 3360253), get_source_number_context and
22 raise_null_context_input following bug
23 3305989.
24 12-JAN-2004 aparkes 115.10 Use hr_utility.raise_error in above proc
25 115.11 Corrected trace in get_source_number_context
26 */
27 --
28 --
29 -- private globals used for caching in get_dynamic_org_meth
30 TYPE g_org_meth_map_rec IS RECORD (
31 estab_id pay_assignment_actions.tax_unit_id%TYPE,
32 gen_org_paymeth_id pay_org_payment_methods_f.ORG_PAYMENT_METHOD_ID%TYPE,
33 new_org_paymeth_id pay_org_payment_methods_f.ORG_PAYMENT_METHOD_ID%TYPE,
34 err_name fnd_new_messages.MESSAGE_NAME%TYPE,
35 opm_name_token varchar2(80),
36 org_name_token varchar2(60),
37 org_type_token varchar2(20));
38 TYPE g_org_meth_map_typ IS TABLE OF g_org_meth_map_rec
39 Index by BINARY_INTEGER;
40 g_org_meth_map_tbl g_org_meth_map_typ;
41 g_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
42 g_estab_id pay_assignment_actions.tax_unit_id%TYPE;
43 -- end of private globals used for caching in get_dynamic_org_meth
44
45
46 procedure raise_null_context_input(p_ee_id number,p_cxt_name varchar2)
47 is
48 l_element_name pay_element_types_f.element_name%type;
49 l_assignment_id pay_element_entries_f.assignment_id%type;
50 l_input_name pay_legislation_contexts.input_value_name%type;
51
52 cursor csr_get_element_info is
53 select et.element_name, entry.assignment_id
54 from pay_element_entries entry,
55 pay_element_links el,
56 pay_element_types_f_tl et
57 where entry.element_entry_id = p_ee_id
58 and et.element_type_id = el.element_type_id
59 and el.element_link_id = entry.element_link_id
60 and et.language = userenv('lang');
61
62 cursor csr_get_input_name is
63 select plc.input_value_name
64 from ff_contexts ffc,
65 pay_legislation_contexts plc
66 where ffc.context_name = p_cxt_name
67 and ffc.context_id = plc.context_id
68 and plc.legislation_code = 'FR';
69 begin
70 open csr_get_element_info;
71 fetch csr_get_element_info into l_element_name, l_assignment_id;
72 close csr_get_element_info;
73 open csr_get_input_name;
74 fetch csr_get_input_name into l_input_name;
75 close csr_get_input_name;
76 hr_utility.trace('error PAY_75084_NULL_CONTEXT_INPUT raised. Direct Ele:'
77 ||l_element_name||' Input:'||l_input_name);
78 hr_utility.set_message(801,'PAY_75084_NULL_CONTEXT_INPUT');
79 hr_utility.set_message_token('INPUT_NAME',l_input_name);
80 hr_utility.set_message_token('ELEMENT',l_element_name);
81 hr_utility.raise_error;
82 end raise_null_context_input;
83
84 procedure get_source_text_context(p_asg_act_id number,
85 p_ee_id number,
86 p_source_text in out nocopy varchar2)
87 is
88 -- the statutory deductions element holds an input to record the
89 -- process_type. The statutory deductions formula fires PLSQL to
90 -- record that process type as a plsql global (it remains constant for
91 -- any run).
92 -- set that global as the default source_text context
93 -- if its null fetch the process_type from the statutory deduction
94 -- element entry
95
96 cursor csr_process_type_entry_value is
97 select SCREEN_ENTRY_VALUE from
98 pay_assignment_actions aa,
99 pay_payroll_actions pa,
100 pay_element_entries_f ee,
101 pay_element_entry_values_f eev,
102 pay_input_values_f iv,
103 pay_element_types_f et,
104 pay_element_links_f el
105 where aa.assignment_action_id = p_asg_act_id
106 and pa.payroll_action_id = aa.payroll_action_id
107 and aa.assignment_id = ee.assignment_id
108 and iv.input_value_id = eev.input_value_id
109 and el.element_link_id = ee.element_link_id
110 and ee.element_entry_id = eev.element_entry_id
111 and et.element_name = 'FR_STATUTORY_DEDUCTIONS'
112 and iv.name = 'Process_Type'
113 and el.element_type_id = et.element_type_id
114 and pa.date_earned between
115 et.effective_start_date and et.effective_end_date
116 and pa.date_earned between
117 iv.effective_start_date and iv.effective_end_date
118 and pa.date_earned between
119 el.effective_start_date and el.effective_end_date
120 and pa.date_earned between
121 ee.effective_start_date and ee.effective_end_date
122 and pa.date_earned between
123 eev.effective_start_date and eev.effective_end_date;
124
125 begin
126 hr_utility.set_location('PAY_FR_RULES.get_source_text_context',1);
127
128 if pay_fr_general.g_process_type is not null
129 then p_source_text := pay_fr_general.g_process_type;
130 hr_utility.set_location('PAY_FR_RULES.get_source_text_context',2);
131
132 else
133 hr_utility.set_location('PAY_FR_RULES.get_source_text_context',3);
134 open csr_process_type_entry_value;
135 fetch csr_process_type_entry_value into p_source_text;
136 close csr_process_type_entry_value;
137
138 end if;
139 hr_utility.set_location('PAY_FR_RULES.get_source_text_context='||
140 p_source_text,4);
141
142 end get_source_text_context;
143
144 procedure get_source_text2_context(p_asg_act_id number,
145 p_ee_id number,
146 p_source_text2 in out nocopy varchar2) is
147 begin
148 -- contribution_code should never be null so error if this
149 -- defaulting procedure if invoked
150 hr_utility.set_location('Entered get_source_text2_context. p_asg_act_id('
151 ||to_char(p_asg_act_id)||') p_ee_id('
152 ||to_char(p_ee_id)||')',5);
153 raise_null_context_input(p_ee_id,'SOURCE_TEXT2');
154 hr_utility.set_location('Leaving get_source_text2_context.',10);
155 end get_source_text2_context;
156
157 procedure get_source_context(p_asg_act_id number,
158 p_ee_id number,
159 p_source_id in out nocopy varchar2) is
160 begin
161 -- contribution_usage_id should never be null so error if this
162 -- defaulting procedure is invoked
163
164 hr_utility.set_location('Entered get_source_context. p_asg_act_id('||
165 to_char(p_asg_act_id)||') p_ee_id('||
166 to_char(p_ee_id)||')',5);
167 p_source_id := '-99';
168 raise_null_context_input(p_ee_id,'SOURCE_ID');
169 hr_utility.set_location('Leaving get_source_context.',10);
170
171 end get_source_context;
172
173 procedure get_source_number_context(p_asg_act_id number,
174 p_ee_id number,
175 p_source_number in out nocopy varchar2)
176 is
177 begin
178 -- Rate should never be null so error if this
179 -- defaulting procedure if invoked
180 hr_utility.set_location('Entered get_source_number_context. p_asg_act_id('
181 ||to_char(p_asg_act_id)||') p_ee_id('
182 ||to_char(p_ee_id)||')',5);
183 raise_null_context_input(p_ee_id,'SOURCE_NUMBER');
184 hr_utility.set_location('Leaving get_source_number_context.',10);
185 end get_source_number_context;
186
187 PROCEDURE get_dynamic_org_meth
188 (p_assignment_action_id in number
189 ,p_effective_date in date
190 ,p_org_meth in number -- org meth with no bank account
191 ,p_org_method_id out nocopy number) -- replacement org meth
192 IS
193 l_gen_org_method_id_chr varchar2(60);
194 l_map_tbl_ind BINARY_INTEGER;
195 l_dummy_opm_id pay_org_payment_methods_f.ORG_PAYMENT_METHOD_ID%TYPE;
196 l_company_id hr_all_organization_units.ORGANIZATION_ID%TYPE;
197 --
198 cursor csr_get_estab is
199 select tax_unit_id
200 from pay_assignment_actions
201 where assignment_action_id = p_assignment_action_id;
202 --
203 cursor csr_get_company is
204 select fnd_number.canonical_to_number(hoi.ORG_INFORMATION1)
205 from hr_organization_information hoi
206 where hoi.organization_id = g_estab_id
207 AND hoi.org_information_context = 'FR_ESTAB_INFO';
208 --
209 cursor csr_check_opm_effective(p_opm_id in number, p_date in date) is
210 select ORG_PAYMENT_METHOD_ID
211 from pay_org_payment_methods_f
212 where ORG_PAYMENT_METHOD_ID = p_opm_id
213 and p_date between effective_start_date and effective_end_date;
214 --
215 PROCEDURE cache_tokens(p_opm_id in number, p_org_id in number) is
216 cursor csr_get_opm_name is
217 select substrb(ORG_PAYMENT_METHOD_NAME,1,80)
218 from pay_org_payment_methods_f_tl
219 where ORG_PAYMENT_METHOD_ID = p_opm_id
220 and language = userenv('LANG');
221 --
222 cursor csr_get_org_info is
223 select substrb(NAME,1,60), substrb(hrl.meaning,1,20)
224 from hr_all_organization_units_tl org,
225 hr_organization_information ori,
226 hr_lookups hrl
227 where org.ORGANIZATION_ID = p_org_id
228 and org.language = userenv('LANG')
229 and org.ORGANIZATION_ID = ori.ORGANIZATION_ID
230 and ori.org_information_context = 'CLASS'
231 and ori.ORG_INFORMATION1 = hrl.lookup_code
232 and hrl.lookup_type = 'ORG_CLASS';
233 BEGIN
234 open csr_get_opm_name;
235 fetch csr_get_opm_name into g_org_meth_map_tbl(l_map_tbl_ind).opm_name_token;
236 close csr_get_opm_name;
237 open csr_get_org_info;
238 fetch csr_get_org_info into g_org_meth_map_tbl(l_map_tbl_ind).org_name_token,
239 g_org_meth_map_tbl(l_map_tbl_ind).org_type_token;
240 close csr_get_org_info;
241 END cache_tokens;
242 --
243 BEGIN
244 l_gen_org_method_id_chr := fnd_number.number_to_canonical(p_org_meth);
245 --
246 if g_assignment_action_id is null or
247 g_assignment_action_id <> p_assignment_action_id
248 then
249 open csr_get_estab;
250 fetch csr_get_estab into g_estab_id;
251 close csr_get_estab;
252 g_assignment_action_id := p_assignment_action_id;
253 end if;
254 --
255 l_map_tbl_ind := DBMS_UTILITY.get_hash_value(g_estab_id||p_org_meth,1,1048576); -- (2^20)
256 if not g_org_meth_map_tbl.exists(l_map_tbl_ind)
257 or g_org_meth_map_tbl(l_map_tbl_ind).estab_id <> g_estab_id
258 or g_org_meth_map_tbl(l_map_tbl_ind).gen_org_paymeth_id <> p_org_meth
259 then
260 -- cache index not used, or user key doesn't match, so prime cache
261 g_org_meth_map_tbl(l_map_tbl_ind).estab_id := g_estab_id;
262 g_org_meth_map_tbl(l_map_tbl_ind).gen_org_paymeth_id := p_org_meth;
263 g_org_meth_map_tbl(l_map_tbl_ind).new_org_paymeth_id := null;
264 g_org_meth_map_tbl(l_map_tbl_ind).err_name := null;
265 -- get replacement org paymeth from estab
266 BEGIN
267 SELECT fnd_number.canonical_to_number(hoi.ORG_INFORMATION1)
268 INTO p_org_method_id
269 FROM hr_organization_information hoi,
270 hr_all_organization_units org
271 WHERE hoi.organization_id = g_estab_id
272 AND hoi.ORG_INFORMATION2 = l_gen_org_method_id_chr
273 AND hoi.org_information_context = 'FR_DYN_PAYMETH_MAPPING_INFO'
274 AND hoi.organization_id = org.organization_id
275 AND p_effective_date between org.date_from
276 AND nvl(org.date_to, hr_general.end_of_time);
277 --
278 open csr_check_opm_effective(p_org_method_id,p_effective_date);
279 fetch csr_check_opm_effective into l_dummy_opm_id;
280 if csr_check_opm_effective%NOTFOUND then
281 g_org_meth_map_tbl(l_map_tbl_ind).err_name := 'PAY_75035_OPM_NOT_EFFECTIVE';
282 cache_tokens(p_org_method_id, g_estab_id);
283 else
284 g_org_meth_map_tbl(l_map_tbl_ind).new_org_paymeth_id := p_org_method_id;
285 end if;
286 close csr_check_opm_effective;
287 EXCEPTION
288 WHEN TOO_MANY_ROWS then
289 g_org_meth_map_tbl(l_map_tbl_ind).err_name := 'PAY_75036_TOO_MANY_GEN_OPMS';
290 cache_tokens(p_org_meth, g_estab_id);
291 WHEN NO_DATA_FOUND then
292 -- get replacement org paymeth from company
293 BEGIN
294 Open csr_get_company;
295 Fetch csr_get_company into l_company_id;
296 Close csr_get_company;
297
298 SELECT fnd_number.canonical_to_number(hoi.ORG_INFORMATION1)
299 INTO p_org_method_id
300 FROM hr_organization_information hoi,
301 hr_all_organization_units org
302 WHERE hoi.organization_id = l_company_id
303 AND hoi.ORG_INFORMATION2 = l_gen_org_method_id_chr
304 AND hoi.org_information_context = 'FR_DYN_PAYMETH_MAPPING_INFO'
305 AND hoi.organization_id = org.organization_id
306 AND p_effective_date between org.date_from
307 AND nvl(org.date_to, hr_general.end_of_time);
308 --
309 open csr_check_opm_effective(p_org_method_id, p_effective_date);
310 fetch csr_check_opm_effective into l_dummy_opm_id;
311 if csr_check_opm_effective%NOTFOUND then
312 g_org_meth_map_tbl(l_map_tbl_ind).err_name := 'PAY_75035_OPM_NOT_EFFECTIVE';
313 cache_tokens(p_org_method_id, l_company_id);
314 else
315 g_org_meth_map_tbl(l_map_tbl_ind).new_org_paymeth_id := p_org_method_id;
316 end if;
317 close csr_check_opm_effective;
318 EXCEPTION
319 WHEN TOO_MANY_ROWS then
320 g_org_meth_map_tbl(l_map_tbl_ind).err_name := 'PAY_75036_TOO_MANY_GEN_OPMS';
321 cache_tokens(p_org_meth, l_company_id);
322 WHEN NO_DATA_FOUND then
323 g_org_meth_map_tbl(l_map_tbl_ind).err_name := 'PAY_75034_NO_SPEC_OPM';
324 cache_tokens(p_org_meth, g_estab_id);
325 END; -- Company Block
326 END; -- Estab block
327 END IF; -- Cache matching
328 -- determine appropriate result from cache.
329
330
331 IF g_org_meth_map_tbl(l_map_tbl_ind).new_org_paymeth_id IS NOT NULL
332 THEN
333 p_org_method_id :=
334 g_org_meth_map_tbl(l_map_tbl_ind).new_org_paymeth_id;
335 ELSIF g_org_meth_map_tbl(l_map_tbl_ind).err_name = 'PAY_75034_NO_SPEC_OPM'
336 THEN
337 hr_utility.set_message(801, 'PAY_75034_NO_SPEC_OPM');
338 hr_utility.set_message_token('OPM_NAME',g_org_meth_map_tbl(l_map_tbl_ind).opm_name_token);
339 hr_utility.set_message_token('ESTAB_NAME',g_org_meth_map_tbl(l_map_tbl_ind).org_name_token);
340 hr_utility.raise_error;
341 ELSIF g_org_meth_map_tbl(l_map_tbl_ind).err_name = 'PAY_75035_OPM_NOT_EFFECTIVE'
342 THEN
343 hr_utility.set_message(801,'PAY_75035_OPM_NOT_EFFECTIVE');
344 hr_utility.set_message_token('OPM_NAME', g_org_meth_map_tbl(l_map_tbl_ind).opm_name_token);
345 hr_utility.set_message_token('ORG_CLASS', g_org_meth_map_tbl(l_map_tbl_ind).org_type_token);
346 hr_utility.set_message_token('ORG_NAME', g_org_meth_map_tbl(l_map_tbl_ind).org_name_token);
347 hr_utility.raise_error;
348 ELSIF g_org_meth_map_tbl(l_map_tbl_ind).err_name = 'PAY_75036_TOO_MANY_GEN_OPMS'
349 THEN
350 hr_utility.set_message(801, 'PAY_75036_TOO_MANY_GEN_OPMS');
351 hr_utility.set_message_token('OPM_NAME', g_org_meth_map_tbl(l_map_tbl_ind).opm_name_token);
352 hr_utility.set_message_token('ORG_CLASS', g_org_meth_map_tbl(l_map_tbl_ind).org_type_token);
353 hr_utility.set_message_token('ORG_NAME', g_org_meth_map_tbl(l_map_tbl_ind).org_name_token);
354 hr_utility.raise_error;
355 END IF;
356 END get_dynamic_org_meth;
357
358 procedure get_multi_tax_unit_pay_flag(p_bus_grp in number,
359 p_mtup_flag in out nocopy varchar2)
360 is
361 begin
362 null;
363 end get_multi_tax_unit_pay_flag;
364 --
365 procedure retro_context_override(p_element_entry_id in number,
366 p_context_name in varchar2,
367 p_context_value in varchar2,
368 p_replacement_value out nocopy varchar2)
369 is
370 cursor csr_matching_entry is
371 select epd1.source_element_type_id,
372 max(decode(piv.name,'Contribution_Code',eev1.screen_entry_value)) cc1,
373 max(decode(piv.name,'Contribution_Code',eev2.screen_entry_value)) cc2,
374 max(decode(piv.name,'Process_Type',eev1.screen_entry_value)) pt1,
375 max(decode(piv.name,'Process_Type',eev2.screen_entry_value)) pt2,
376 max(decode(piv.name,'Contribution_Usage_ID',eev1.screen_entry_value)) cui1,
377 max(decode(piv.name,'Contribution_Usage_ID',eev2.screen_entry_value)) cui2,
378 max(decode(piv.name,'Rate Type',eev1.screen_entry_value)) rt1,
379 max(decode(piv.name,'Rate Type',eev2.screen_entry_value)) rt2
380 from pay_element_entries_f pee1,
381 pay_element_entries_f pee2,
382 pay_entry_process_details epd1,
383 pay_entry_process_details epd2,
384 pay_element_entry_values_f eev1,
385 pay_element_entry_values_f eev2,
386 pay_input_values_f piv
387 where pee1.element_entry_id = p_element_entry_id
388 and pee2.element_link_id = pee1.element_link_id
389 and pee2.assignment_id = pee1.assignment_id
390 and pee2.effective_start_date = pee1.effective_start_date
391 and pee2.effective_end_date = pee1.effective_end_date
392 and pee2.creator_id = pee1.creator_id
393 and pee2.creator_type in ('EE','RR')
394 and pee2.element_entry_id <> pee1.element_entry_id
395 and epd1.element_entry_id = pee1.element_entry_id
396 and epd2.element_entry_id = pee2.element_entry_id
397 and epd2.retro_component_id = epd1.retro_component_id
398 and epd2.process_path = epd1.process_path
399 and epd2.source_asg_action_id = epd1.source_asg_action_id
400 and epd2.source_element_type_id = epd1.source_element_type_id
401 and (epd1.tax_unit_id is null or
402 epd2.tax_unit_id = epd1.tax_unit_id)
403 and epd2.source_entry_id = epd1.source_entry_id
404 and eev1.element_entry_id = pee1.element_entry_id
405 and eev2.element_entry_id = pee2.element_entry_id
406 and eev1.input_value_id = eev2.input_value_id
407 and piv.input_value_id = eev2.input_value_id
408 and piv.name in ('Contribution_Code','Process_Type',
409 'Contribution_Usage_ID','Rate Type')
410 group by epd1.source_element_type_id
411 having max(decode(piv.name,'Contribution_Code',eev1.screen_entry_value))
412 = max(decode(piv.name,'Contribution_Code',eev2.screen_entry_value))
413 and max(decode(piv.name,'Process_Type',eev1.screen_entry_value))
414 = max(decode(piv.name,'Process_Type',eev2.screen_entry_value))
415 and max(decode(piv.name,'Contribution_Usage_ID',eev1.screen_entry_value))
416 = max(decode(piv.name,'Contribution_Usage_ID',eev2.screen_entry_value))
417 and nvl(max(decode(piv.name,'Rate Type',eev1.screen_entry_value)),' ')
418 = nvl(max(decode(piv.name,'Rate Type',eev2.screen_entry_value)),' ');
419 --
420 cursor csr_retro_code_by_id(p_cu_id in number) is
421 select retro_contribution_code,contribution_type
422 from pay_fr_contribution_usages
423 where contribution_usage_id = p_cu_id
424 and retro_contribution_code <> contribution_code;
425 --
426 rec_matching_entry csr_matching_entry%ROWTYPE;
427 l_retro_code pay_fr_contribution_usages.retro_contribution_code%TYPE;
428 l_contribution_type pay_fr_contribution_usages.contribution_type%TYPE;
429 begin
430 if p_context_name <> 'SOURCE_TEXT2'
431 or nvl(p_context_value,' ') = ' '
432 then
433 -- Not the Contribution_code context or context is null/space
434 -- so return value unchanged
435 p_replacement_value := p_context_value;
436 else
437 open csr_matching_entry;
438 fetch csr_matching_entry into rec_matching_entry;
439 if csr_matching_entry%NOTFOUND then
440 -- No matching entry so return value unchanged.
441 p_replacement_value := p_context_value;
442 close csr_matching_entry;
443 else
444 close csr_matching_entry;
445 -- Matching entry, only the rate must have changed so replace value
446 open csr_retro_code_by_id(
447 fnd_number.canonical_to_number(rec_matching_entry.cui1));
448 fetch csr_retro_code_by_id into l_retro_code,l_contribution_type;
449 if csr_retro_code_by_id%NOTFOUND then
450 -- retro_contribution_code not different or contribution_code
451 -- not held on pay_fr_contribution_usages
452 -- so return value unchanged
453 p_replacement_value := p_context_value;
454 else
455 IF l_contribution_type = 'URSSAF' THEN
456 p_replacement_value := substr(rec_matching_entry.cc1,1,3) ||
457 substr(l_retro_code,4,4);
458 ELSIF l_contribution_type = 'ASSEDIC' THEN
459 p_replacement_value := substr(rec_matching_entry.cc1,1,2) ||
460 substr(l_retro_code,3,5);
461 ELSIF l_contribution_type = 'AGIRC' THEN
462 p_replacement_value := substr(rec_matching_entry.cc1,1,5) ||
463 substr(l_retro_code,6,2);
464 ELSE /* Must be ARRCO */
465 p_replacement_value := substr(rec_matching_entry.cc1,1,5) ||
466 substr(l_retro_code,6,2);
467 END IF;
468 end if; -- csr_retro_code_by_id%NOTFOUND
469 close csr_retro_code_by_id;
470 end if; -- No matching entry
471 end if; -- Not the Contribution_code context
472 end retro_context_override;
473 end pay_fr_rules;
[Home] [Help]