4 -- Setup Globals
1 package body pay_core_utils as
2 /* $Header: pycorutl.pkb 120.36 2011/12/15 07:24:22 panumala ship $ */
3 --
5 --
6 g_pkg varchar2(30) := 'pay_core_utils';
7 g_traces BOOLEAN := hr_utility.debug_enabled;
8
9 type varchar_1_tbl is table of varchar2(1) index by binary_integer;
10 type varchar_50_tbl is table of varchar2(50) index by binary_integer;
14 type message_token is record
11 type varchar_240_tbl is table of varchar2(240) index by binary_integer;
12 type number_tbl is table of number index by binary_integer;
13 --
15 (
16 token_name varchar_50_tbl,
17 token_value varchar_240_tbl,
18 sz number
19 );
20 --
21 type message_stack_type is record
22 (
23 message_name varchar_50_tbl,
24 message_txt varchar_240_tbl,
25 applid number_tbl,
26 token_str number_tbl,
27 token_end number_tbl,
28 sz number,
29 message_level varchar_1_tbl
30 );
31 --
32 g_message_stack message_stack_type;
33 g_message_tokens message_token;
34 --
35 g_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
36 g_leg_code pay_legislation_contexts.legislation_code%type := null;
37 --
38 g_process_path varchar2(2000);
39 g_asg_action_id pay_assignment_actions.assignment_action_id%type;
40 --
41 -- Legislative context IV names cache. used in get_leg_context_iv_name.
42 --
43 type t_leg_context_iv_rec is record
44 (legislation_code per_business_groups.legislation_code%type
45 ,non_oracle_local varchar2(30)
46 ,jurisdiction_iv pay_input_values_f.name%type
47 ,source_id_iv pay_input_values_f.name%type
48 ,source_text_iv pay_input_values_f.name%type
49 ,source_number_iv pay_input_values_f.name%type
50 ,source_number2_iv pay_input_values_f.name%type
51 ,source_text2_iv pay_input_values_f.name%type
52 ,organization_id_iv pay_input_values_f.name%type
53 );
54 --
55 g_leg_context_iv_rec t_leg_context_iv_rec;
56 --
57 -- Caches for get_entry_end_date
58 --
59 type t_proration_group_id is table of pay_element_types_f.proration_group_id%type
60 index by binary_integer;
61 type t_payroll_action_id is table of pay_payroll_actions.payroll_action_id%type
62 index by binary_integer;
63 type t_date is table of date
64 index by binary_integer;
65 g_proration_group_id t_proration_group_id;
66 g_payroll_action_id t_payroll_action_id;
67 g_end_date t_date;
68
69 --
70 ------------------------------ get_parameter -------------------------------
71 /* Name : get_parameter
72 Purpose : This simply returns the value of a specified parameter in
73 a parameter list based of the parameter name.
74 Arguments :
75 Notes :
76 */
77 function get_parameter(name in varchar2,
78 parameter_list varchar2) return varchar2
79 is
80 start_ptr number;
81 end_ptr number;
82 token_val pay_payroll_actions.legislative_parameters%type;
83 par_value pay_payroll_actions.legislative_parameters%type;
84 separator varchar2(1) := ' ';
85 begin
86 --
87 token_val := name||'=';
88 --
89 start_ptr := instr(parameter_list, token_val) + length(token_val);
90 --
91 -- Support of delimiter character '|' to allow spaces in a legislative parameter
92 --
93 if substr(parameter_list, start_ptr, 1) = '|' then
94 separator := '|';
95 start_ptr := start_ptr + length('|');
96 end if;
97 --
98 end_ptr := instr(parameter_list, separator, start_ptr);
99 --
100 /* if there is no spaces use then length of the string */
101 if end_ptr = 0 then
102 end_ptr := length(parameter_list)+1;
103 end if;
104 --
105 /* Did we find the token */
106 if instr(parameter_list, token_val) = 0 then
107 par_value := NULL;
108 else
109 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
110 end if;
111 --
112 return par_value;
113 --
114 end get_parameter;
115 --
116
117 ------------------------------ remove_parameter -------------------------------
118 /* Name : remove_parameter
119 Purpose : This simply removes the value (and token name) of a specified
120 parameter in a parameter list.
121 Arguments :
122 Notes :
123 */
124 function remove_parameter(p_name in varchar2,
125 p_parameter_list varchar2) return varchar2
126 is
127 --
128
129 start_val number; /* Start pos of token value */
130 start_space number; /* Pos of space before token */
131 end_space number; /* Pos of space after value */
132 full_ptr number; /* Full length of orig string */
133
134
135 token_val pay_payroll_actions.legislative_parameters%type;
136 l_parameter_list pay_payroll_actions.legislative_parameters%type;
137
138 begin
139 --
140 token_val := p_name||'=';
141 --
142 full_ptr := length(p_parameter_list)+1;
143 start_val := instr(p_parameter_list, token_val) + length(token_val);
144 end_space := instr(p_parameter_list, ' ', start_val);
145 start_space := instr(p_parameter_list, ' ', start_val - full_ptr, 1);
146 --
147
148 /* if there is no spaces after token then use the full length of the string */
149 if end_space = 0 then
150 end_space := full_ptr;
151 end if;
152 --
153 /* Did we find the token, if so remove it and its value */
154 if instr(p_parameter_list, token_val) = 0 then
155 l_parameter_list := p_parameter_list;
156 else
157 l_parameter_list :=
158 substr(p_parameter_list, 0,start_space-1)||
159 substr(p_parameter_list, end_space,full_ptr - end_space);
160 end if;
161
162 --return(to_char(start_space)||'xxx');
163 return(l_parameter_list);
164
165 end;
169 Purpose : This returns the cached business group id or returns the
166 --
167 --------------------------- get_business_group -------------------------------
168 /* Name : get_business_group
170 value returned from the supplied select statement.
171 Arguments :
172 Notes :
173 */
174 function get_business_group (p_statement varchar2) return number
175 is
176 sql_cur number;
177 ignore number;
178 l_element_entry_id number;
179 l_assignment_id number;
180 business_group_id number;
181
182 begin
183 g_traces := hr_utility.debug_enabled;
184
185 if (g_traces) then
186 hr_utility.trace('pay_core_utils.get_business_group p_statement:'||p_statement);
187 end if;
188
189 if (g_cache_business_group) then
190
191 business_group_id := get_dyt_business_group(p_statement);
192 -- bug 9700168 use execute immediate to pick up plsql statements
193 else if instr(upper(p_statement),'BEGIN') > 0 then
194 EXECUTE IMMEDIATE ('declare '||p_statement) using out business_group_id;
195
196 else
197
198 sql_cur := dbms_sql.open_cursor;
199 --
200 -- Added by exjones
201 begin
202 dbms_sql.parse(sql_cur,
203 p_statement,
204 dbms_sql.v7);
205 exception
206 when others then
207 hr_utility.trace('pay_core_utils.get_business_group post dbms_sql.parse exception');
208 dbms_sql.close_cursor(sql_cur);
209 return NULL;
210 end;
211 --
212 dbms_sql.define_column(sql_cur, 1, business_group_id);
213 ignore := dbms_sql.execute(sql_cur);
214 if dbms_sql.fetch_rows(sql_cur) > 0 then
215 dbms_sql.column_value(sql_cur, 1, business_group_id);
216 else
217 business_group_id := null;
218 end if;
219 dbms_sql.close_cursor(sql_cur);
220 end if;
221 end if;
222 --
223 if (g_traces) then
224 hr_utility.trace('pay_core_utils.get_business_group id: '||business_group_id);
225 end if;
226 return business_group_id;
227 --
228 exception
229 when others then
230 if dbms_sql.is_open(sql_cur) then
231 dbms_sql.close_cursor(sql_cur);
232 end if;
233 raise;
234 end get_business_group;
235 --
236 --------------------------- get_business_group_withbind -------------------------------
237 /* Name : get_business_group_withbind
238 Purpose : This returns the cached business group id or returns the
239 value returned from the supplied select statement.
240 Arguments :
241 Notes :
242 */
243 function get_business_group_withbind (p_statement varchar2, p_bindvar number) return number
244 is
245 -- bug 13075414 overload to pass bind variable so reduce hard parsing
246 business_group_id number;
247
248 begin
249 g_traces := hr_utility.debug_enabled;
250
251 if (g_traces) then
252 hr_utility.trace('pay_core_utils.get_business_group_withbind p_statement:'||p_statement||
253 ' p_bindvar:'||to_char(p_bindvar));
254 end if;
255
256 if (g_cache_business_group) then
257
258 business_group_id := get_dyt_business_group(p_statement, p_bindvar);
259 else
260 EXECUTE IMMEDIATE (p_statement) into business_group_id using p_bindvar;
261
262 end if;
263 --
264 if (g_traces) then
265 hr_utility.trace('pay_core_utils.get_business_group_withbind id: '||business_group_id);
266 end if;
267
268 return business_group_id;
269 --
270 end get_business_group_withbind;
271 --------------------------- get_dyt_business_group---------------------------
272 /* Name : get_dyt_business_group
273 Purpose : This returns the cached business group id or returns the
274 value returned from the supplied select statement.
275 This should be used only from the dynamic triggers and only
276 when its required to use the cached business group.
277 (Do not use unless you are very sure)
278 Arguments :
279 Notes :
280 */
281 function get_dyt_business_group (p_statement varchar2) return number
282 is
283 sql_cur number;
284 ignore number;
285 business_group_id number;
286 begin
287 --
288 if (g_business_group_id is null) then
289
290 -- bug 9700168 use execute immediate to pick up plsql statements
291 if instr(upper(p_statement),'BEGIN') > 0 then
292 EXECUTE IMMEDIATE ('declare '||p_statement) using out business_group_id;
293
294 else
295
296 sql_cur := dbms_sql.open_cursor;
297 --
298 -- Added by exjones
299 begin
300 dbms_sql.parse(sql_cur,
301 p_statement,
302 dbms_sql.v7);
303 exception
304 when others then
305 dbms_sql.close_cursor(sql_cur);
306 return NULL;
307 end;
308 --
309 dbms_sql.define_column(sql_cur, 1, business_group_id);
310 ignore := dbms_sql.execute(sql_cur);
311 if dbms_sql.fetch_rows(sql_cur) > 0 then
312 dbms_sql.column_value(sql_cur, 1, business_group_id);
313 else
314 business_group_id := null;
315 end if;
316 dbms_sql.close_cursor(sql_cur);
317
318 end if;
322 end if;
319
320 g_business_group_id := business_group_id;
321
323 --
324 if (g_traces) then
325 hr_utility.trace('pay_core_utils.get_dyt_business_group id:'||to_char(business_group_id)||
326 ' g_bg_id:'||to_char(g_business_group_id));
327 end if;
328 return g_business_group_id;
329 --
330 exception
331 when others then
332 if dbms_sql.is_open(sql_cur) then
333 dbms_sql.close_cursor(sql_cur);
334 end if;
335 raise;
336 end get_dyt_business_group;
337 --
338 --------------------------- get_dyt_business_group---------------------------
339 /* Name : get_dyt_business_group
340 Purpose : This returns the cached business group id or returns the
341 value returned from the supplied select statement.
342 This should be used only from the dynamic triggers and only
343 when its required to use the cached business group.
344 (Do not use unless you are very sure)
345 Arguments :
346 Notes :
347 */
348 function get_dyt_business_group (p_statement varchar2, p_bindvar number) return number
349 is
350 sql_cur number;
351 ignore number;
352 business_group_id number;
353 begin
354 --
355 if (g_business_group_id is null) then
356
357 EXECUTE IMMEDIATE (p_statement) into business_group_id using p_bindvar;
358
359 g_business_group_id := business_group_id;
360
361 end if;
362 --
363 if (g_traces) then
364 hr_utility.trace('pay_core_utils.get_dyt_business_group id:'||to_char(business_group_id)||
365 ' g_bg_id:'||to_char(g_business_group_id));
366 end if;
367 return g_business_group_id;
368 --
369 end get_dyt_business_group;
370 --------------------------- get_legislation_code -------------------------------
371 /* Name : get_legislation_code
372 Purpose : This returns the cached legislation code if it is for the passed
373 business group. Otherwise derives the legislation code of the
374 supplied business group.
375 Arguments :
376 Notes :
377 */
378 function get_legislation_code (p_bg_id number) return varchar2
379 is
380 begin
381
382 if (p_bg_id is not null) then
383 if (g_legislation_code is null or
384 g_business_group_id is null or
385 (g_business_group_id is not null and
386 g_business_group_id <> p_bg_id)) then
387 select legislation_code
388 into g_legislation_code
389 from per_business_groups_perf
390 where business_group_id = p_bg_id;
391 --
392 g_business_group_id := p_bg_id;
393 end if;
394 end if;
395 --
396 if (g_traces) then
397 hr_utility.trace('pay_core_utils.get_legislation_code '||g_legislation_code);
398 end if;
399 return g_legislation_code;
400 --
401 end get_legislation_code;
402 --
403 --------------------------- reset_cached_values -------------------------------
404 /* Name : reset_cached_values
405 Purpose : This resets the cached value for business_group and legislation
406 code.
407 Arguments :
408 Notes :
409 */
410 procedure reset_cached_values
411 is
412 begin
413 hr_utility.set_location('pay_core_utils.reset_cached_values', 10);
414 --
415 g_business_group_id := null;
416 g_legislation_code := null;
417 --
418 hr_utility.set_location('pay_core_utils.reset_cached_values', 20);
419 --
420 end reset_cached_values;
421 --
422 -------------------------- get_time_definition ----------------------------
423 /* Name : get_time_definition
424 Purpose : This procedure is use to get the time definition for
425 Elements that require the dynamic option.
426 Arguments :
427 Notes :
428 */
429 procedure get_time_definition(p_element_entry in number,
430 p_asg_act_id in number,
431 p_time_def_id out nocopy number)
432 is
433 --
434 l_leg_code per_business_groups_perf.legislation_code%type;
435 l_bus_grp_id per_business_groups_perf.business_group_id%type;
436 l_asg_id per_all_assignments_f.assignment_id%type;
437 statem varchar2(2000); -- used with dynamic pl/sql
438 sql_cursor integer;
439 l_rows integer;
440 --
441 begin
442 --
443 select paa.assignment_id,
444 ppa.business_group_id,
445 pbg.legislation_code
446 into l_asg_id,
447 l_bus_grp_id,
448 l_leg_code
449 from pay_assignment_actions paa,
450 pay_payroll_actions ppa,
451 per_business_groups_perf pbg
452 where paa.assignment_action_id = p_asg_act_id
453 and ppa.business_group_id = pbg.business_group_id
454 and paa.payroll_action_id = ppa.payroll_action_id;
455 --
456 statem :=
457 'begin
458 pay_'||l_leg_code||'_rules.get_time_def_for_entry(
459 :element_entry_id,
460 :assignment_id,
461 :assignment_action_id,
462 :business_group_id,
463 :time_def_id);
464 end;
465 ';
466 --
467 sql_cursor := dbms_sql.open_cursor;
468 --
469 dbms_sql.parse(sql_cursor, statem, dbms_sql.v7);
470 --
471 --
472 dbms_sql.bind_variable(sql_cursor, 'element_entry_id', p_element_entry);
473 --
477 --
474 dbms_sql.bind_variable(sql_cursor, 'assignment_id', l_asg_id);
475 --
476 dbms_sql.bind_variable(sql_cursor, 'assignment_action_id', p_asg_act_id);
478 dbms_sql.bind_variable(sql_cursor, 'business_group_id', l_bus_grp_id);
479 --
480 dbms_sql.bind_variable(sql_cursor, 'time_def_id', p_time_def_id);
481 --
482 l_rows := dbms_sql.execute (sql_cursor);
483 --
484 if (l_rows = 1) then
485 dbms_sql.variable_value(sql_cursor, 'time_def_id',
486 p_time_def_id);
487 dbms_sql.close_cursor(sql_cursor);
488 --
489 else
490 p_time_def_id := null;
491 dbms_sql.close_cursor(sql_cursor);
492 end if;
493 --
494 end get_time_definition;
495 --
496 --
497 -------------------------- get_time_period_start ---------------------------
498 /* Name : get_time_period_start
499 Purpose : This procedure simple returns the period start date for
500 the element entry fetch.
501 Arguments :
502 Notes :
503 */
504 function get_time_period_start(p_payroll_action_id in number
505 ) return date
506 is
507 l_start_date date;
508 begin
509 --
510 select ptp.start_date
511 into l_start_date
512 from per_time_periods ptp,
513 pay_payroll_actions ppa
514 where ppa.payroll_action_id = p_payroll_action_id
515 and ppa.payroll_id = ptp.payroll_id
516 and ppa.date_earned between ptp.start_date
517 and ptp.end_date;
518 --
519 return l_start_date;
520 --
521 end get_time_period_start;
522 --
523 --
524 -------------------------- get_entry_end_date ---------------------------
525 /* Name : get_entry_end_date
526 Purpose : This function returns the miminum end date for an
527 element type in a prorated run.
528 Used in the element entry fetch.
529 Arguments :
530 Notes :
531 */
532
533 function get_entry_end_date(p_element_type_id in number,
534 p_payroll_action_id in number,
535 p_assignment_action_id in number,
536 p_date_earned in date ) return date
537 is
538 l_proration_group_id number;
539 l_time_definition_type varchar2(1);
540 begin
541 --
542 if (g_proration_group_id.exists(p_element_type_id) = FALSE or
543 g_payroll_action_id(p_element_type_id) <> p_payroll_action_id) then
544
545 -- load element type info into cache
546
547 select nvl(proration_group_id, -1), nvl(time_definition_type, 'N')
548 into l_proration_group_id, l_time_definition_type
549 from pay_element_types_f
550 where element_type_id = p_element_type_id
551 and p_date_earned between effective_start_date
552 and effective_end_date;
553
554 g_proration_group_id(p_element_type_id) := l_proration_group_id;
555 g_payroll_action_id(p_element_type_id) := p_payroll_action_id;
556 --
557 if (l_proration_group_id <> -1) then
558 g_end_date(p_element_type_id) := pay_interpreter_pkg.prorate_start_date
559 (p_assignment_action_id, l_proration_group_id);
560 elsif (l_time_definition_type <> 'N') then
561 g_end_date(p_element_type_id) := pay_core_utils.get_time_period_start
562 (p_payroll_action_id);
563 else
564 g_end_date(p_element_type_id) := p_date_earned;
565 end if;
566 end if;
567 --
568 return g_end_date(p_element_type_id);
569 --
570 end get_entry_end_date;
571
572 --
573 ------------------------------ get_prorated_dates -------------------------------
574 /* Name : get_prorated_dates
575 Purpose : This procedure calls the process event interpreter to
576 determine datetracked changes.
577 Arguments :
578 Notes :
579 */
580 procedure get_prorated_dates(p_ee_id in number,
581 p_asg_act_id in number,
582 p_time_def_type in varchar2,
583 p_time_def_id in out nocopy number,
584 p_date_array out nocopy char_array,
585 p_type_array out nocopy char_array
586 )
587 is
588 l_dt_internal pay_interpreter_pkg.t_proration_dates_table_type;
589 l_ty_internal pay_interpreter_pkg.t_proration_type_table_type;
590 l_det_internal pay_interpreter_pkg.t_detailed_output_table_type;
591 l_time_def_id number;
592 tmp_num number;
593 begin
594 --
595 pay_proc_logging.PY_ENTRY('pay_core_utils.get_prorated_dates');
596 --
597 pay_proc_logging.PY_LOG_TXT(pay_proc_logging.PY_ELEMETY,
598 'EE_ID = '||p_ee_id||' AA_ID = '||p_asg_act_id);
599 --
600 if (p_time_def_type = 'G') then
601 --
602 get_time_definition(p_ee_id, p_asg_act_id, p_time_def_id);
603 l_time_def_id := p_time_def_id;
604 --
605 elsif (p_time_def_type = 'S') then
606 --
607 l_time_def_id := p_time_def_id;
608 --
609 else
610 --
611 l_time_def_id := null;
612 --
613 end if;
614 --
615 pay_interpreter_pkg.get_prorated_dates
616 (
617 p_element_entry_id => p_ee_id,
618 p_assignment_action_id => p_asg_act_id,
619 p_time_definition_id => l_time_def_id,
623 );
620 t_detailed_output => l_det_internal,
621 t_proration_dates => l_dt_internal,
622 t_proration_type => l_ty_internal
624 --
625 for cnt in 1..l_dt_internal.count loop
626 --
627 p_date_array(cnt) := to_char(l_dt_internal(cnt), 'YYYY/MM/DD HH24:MI:SS');
628 p_type_array(cnt) := l_ty_internal(cnt);
629 --
630 pay_proc_logging.PY_LOG_TXT(pay_proc_logging.PY_ELEMETY,
631 'Prorate Date '||p_date_array(cnt)||' Type '||p_type_array(cnt));
632 --
633 end loop;
634 --
635 pay_proc_logging.PY_EXIT('pay_core_utils.get_prorated_dates');
636 --
637 end get_prorated_dates;
638 --
639 ------------------------------ set_prorate_dates -------------------------------
640 /* Name : set_prorate_dates
641 Purpose : This procedure calls is passed a set of dates and then
642 determines the next combination of dates to use in proration.
643 Arguments :
644 Notes :
645 */
646 procedure set_prorate_dates(p_et_id in number,
647 p_asg_act_id in number,
648 p_date_array in char_array,
649 p_type_array in char_array,
650 p_arr_cnt in number,
651 p_prd_end out nocopy varchar2,
652 p_start_date out nocopy varchar2,
653 p_end_date out nocopy varchar2
654 )
655 is
656 l_date_earned date;
657 l_prd_end_date date;
658 l_prd_start_date date;
659 l_prorate_grp_id number;
660 begin
661 --
662 select ppa.date_earned,
663 ptp.end_date,
664 ptp.start_date,
665 pet.proration_group_id
666 into l_date_earned,
667 l_prd_end_date,
668 l_prd_start_date,
669 l_prorate_grp_id
670 from pay_payroll_actions ppa,
671 pay_assignment_actions paa,
672 per_time_periods ptp,
673 pay_element_types_f pet
674 where paa.assignment_action_id = p_asg_act_id
675 and paa.payroll_action_id = ppa.payroll_action_id
676 and pet.element_type_id = p_et_id
677 and ppa.date_earned between pet.effective_start_date
678 and pet.effective_end_date
679 and ppa.date_earned between ptp.start_date
680 and ptp.end_date
681 and ptp.payroll_id = ppa.payroll_id;
682 --
683 -- Is the entry being prorated, if so then
684 -- get the proration start date,
685 -- otherwise it must be allocating
686 if (l_prorate_grp_id is not null) then
687 l_prd_start_date := pay_interpreter_pkg.prorate_start_date
688 (p_asg_act_id,
689 l_prorate_grp_id);
690 end if;
691 --
692 /* Remember we only deal with updates */
693 if (p_arr_cnt = 1) then
694 p_start_date := to_char(l_prd_start_date, 'YYYY/MM/DD HH24:MI:SS');
695 else
696 p_start_date := p_date_array(p_arr_cnt -1);
697 end if;
698
699 p_end_date := to_char(to_date(p_date_array(p_arr_cnt),
700 'YYYY/MM/DD HH24:MI:SS') -1,
701 'YYYY/MM/DD HH24:MI:SS');
702 --
703 p_prd_end := to_char(l_prd_end_date, 'YYYY/MM/DD HH24:MI:SS');
704 --
705 end set_prorate_dates;
706 --
707 ------------------------------ get_rr_id -------------------------------
708 /* Name : get_rr_id
709 Purpose : This procedure Retrieves a set number of new RR ids.
710 Arguments :
711 Notes :
712 Ths procedure is used by the C code to reduce the number
713 of network trips.
714 */
715 procedure get_rr_id( p_rr_id_list out nocopy varchar2
716 )
717 is
718 rr_id number;
719 begin
720 --
721 select pay_run_results_s.nextval
722 into rr_id
723 from sys.dual;
724 --
725 p_rr_id_list := rr_id;
726 --
727 end get_rr_id;
728 --
729 ------------------------------ get_aa_id -------------------------------
730 /* Name : get_aa_id
731 Purpose : This procedure Retrieves a set number of new AA ids.
732 Arguments :
733 Notes :
734 Ths procedure is used by the C code to reduce the number
735 of network trips.
736 */
737 procedure get_aa_id( p_aa_id_list out nocopy varchar2
738 )
739 is
740 aa_id number;
741 begin
742 --
743 select pay_assignment_actions_s.nextval
744 into aa_id
745 from sys.dual;
746 --
747 p_aa_id_list := aa_id;
748 --
749 end get_aa_id;
750 --
751 ------------------------------ get_rb_id -------------------------------
752 /* Name : get_rb_id
753 Purpose : This procedure Retrieves a set number of new RB ids.
754 Arguments :
755 Notes :
756 Ths procedure is used by the C code to reduce the number
757 of network trips.
758 */
759 procedure get_rb_id( p_rb_id_list out nocopy varchar2
760 )
761 is
762 rb_id number;
763 begin
764 --
765 select pay_run_balances_s.nextval
766 into rb_id
767 from sys.dual;
768 --
769 p_rb_id_list := rb_id;
770 --
771 end get_rb_id;
772 --
773 --------------------------- push_message -------------------------------
777 Notes :
774 /* Name : push_message
775 Purpose : This places a message on the error stack.
776 Arguments :
778 */
779 procedure push_message(p_applid in number,
780 p_msg_name in varchar2,
781 p_level in varchar2
782 )
783 is
784 begin
785 push_message(p_applid,p_msg_name,null,p_level);
786 end;
787
788 procedure push_message(p_applid in number,
789 p_msg_name in varchar2,
790 p_msg_txt in varchar2,
791 p_level in varchar2
792 )
793 is
794 begin
795 hr_utility.set_location('pay_core_utils.push_message', 10);
796 --
797 g_message_stack.sz := g_message_stack.sz + 1;
798 g_message_stack.message_name(g_message_stack.sz) := p_msg_name;
799 g_message_stack.message_txt(g_message_stack.sz) := p_msg_txt;
800 g_message_stack.applid(g_message_stack.sz) := p_applid;
801 g_message_stack.token_str(g_message_stack.sz) := null;
802 g_message_stack.token_end(g_message_stack.sz) := null;
803
804 if (p_level='W' or p_level='I') then
805 g_message_stack.message_level(g_message_stack.sz) := p_level;
806 else
807 g_message_stack.message_level(g_message_stack.sz) := 'F';
808 end if;
809 --
810 hr_utility.set_location('pay_core_utils.push_message', 20);
811 --
812 end push_message;
813 --
814 --------------------------- push_token -------------------------------
815 /* Name : push_token
816 Purpose : This places a message on the error stack.
817 Arguments :
818 Notes :
819 */
820 procedure push_token(
821 p_tok_name in varchar2,
822 p_tok_value in varchar2
823 )
824 is
825 begin
826 hr_utility.set_location('pay_core_utils.push_token', 10);
827 --
828 g_message_tokens.sz := g_message_tokens.sz + 1;
829 g_message_tokens.token_name(g_message_tokens.sz) := p_tok_name;
830 g_message_tokens.token_value(g_message_tokens.sz) := p_tok_value;
831 --
832 -- Now set the message pointers
833 if (g_message_stack.token_str(g_message_stack.sz) is null) then
834 g_message_stack.token_str(g_message_stack.sz) := g_message_tokens.sz;
835 end if;
836 g_message_stack.token_end(g_message_stack.sz) := g_message_tokens.sz;
837 --
838 hr_utility.set_location('pay_core_utils.push_token', 20);
839 --
840 end push_token;
841 --
842 --------------------------- pop_message -------------------------------
843 /* Name : pop_message
844 Purpose : This removes a message from the stack.
845 Arguments :
846 Notes :
847 */
848 procedure pop_message(
849 p_msg_text out nocopy varchar2
850 )
851 is
852 l_sev_level varchar2(1);
853 begin
854 pop_message(p_msg_text,l_sev_level);
855 end pop_message;
856
857 procedure pop_message(
858 p_msg_text out nocopy varchar2,
859 p_sev_level out nocopy varchar2
860 )
861 is
862 begin
863 hr_utility.set_location('pay_core_utils.pop_message', 10);
864 --
865 if (g_message_stack.sz = 0) then
866 p_msg_text := null;
867 p_sev_level := null;
868 return;
869 end if;
870 --
871 if (g_message_stack.message_name(g_message_stack.sz) is NULL)
872 then
873 p_msg_text := g_message_stack.message_txt(g_message_stack.sz);
874 else
875 hr_utility.set_message(g_message_stack.applid(g_message_stack.sz),
876 g_message_stack.message_name(g_message_stack.sz));
877 --
878 if (g_message_stack.token_str(g_message_stack.sz) is not null) then
879 --
880 for tok_cnt in g_message_stack.token_str(g_message_stack.sz) ..
881 g_message_stack.token_end(g_message_stack.sz) loop
882 --
883 hr_utility.set_message_token(g_message_tokens.token_name(tok_cnt),
884 g_message_tokens.token_value(tok_cnt));
885 --
886 end loop;
887 --
888 end if;
889 --
890 p_msg_text := hr_utility.get_message;
891 end if;
892 p_sev_level := g_message_stack.message_level(g_message_stack.sz);
893 g_message_tokens.sz := g_message_stack.token_str(g_message_stack.sz) - 1;
894 g_message_stack.sz := g_message_stack.sz - 1;
895 --
896 hr_utility.set_locatIon('pay_core_utils.pop_message', 20);
897 --
898 end pop_message;
899
900 procedure mesg_stack_error_hdlr(p_pactid in number)
901 is
902 l_msg_text varchar2(240);
903 l_sev_level varchar2(1);
904 l_found boolean := TRUE;
905 begin
906
907 while (l_found = TRUE) loop
908 if (g_message_stack.sz = 0)
909 then
910 l_found := FALSE;
911 else
912
913 pop_message(l_msg_text, l_sev_level);
914
915 insert into pay_message_lines
916 (
917 LINE_SEQUENCE,
918 PAYROLL_ID,
919 MESSAGE_LEVEL,
920 SOURCE_ID,
921 SOURCE_TYPE,
922 LINE_TEXT
923 )
924 values
925 (
926 pay_message_lines_s.nextval,
927 null,
928 l_sev_level,
929 p_pactid,
930 'P',
931 substr(l_msg_text,0,240)
932 );
933
934 end if;
938 --
935 end loop;
936
937 end;
939 --------------------------- get_pp_action_id -------------------------------
940 /* Name : get_pp_action_id
941 Purpose : This gets the prepayment assignment action (use in the
942 payment route)
943 Arguments :
944 Notes :
945 */
946 function get_pp_action_id(p_action_type in varchar2,
947 p_action_id in number) return number
948 is
949 l_action_id number;
950 begin
951 --
952 if (p_action_type in ('P', 'U')) then
953 l_action_id := p_action_id;
954 elsif (p_action_type in ('R', 'Q')) then
955 --
956 -- Always return the master prepayment action.
957 --
958 select INTLK.locking_action_id
959 into l_action_id
960 from pay_action_interlocks INTLK,
961 pay_assignment_actions paa,
962 pay_payroll_actions ppa
963 where INTLK.locked_action_id = p_action_id
964 and INTLK.locking_action_id = paa.assignment_action_id
965 and paa.payroll_action_id = ppa.payroll_action_id
966 and ppa.action_type in ('P', 'U')
967 and paa.source_action_id is null;
968 --
969 else
970 l_action_id := null;
971 end if;
972 --
973 return l_action_id;
974 --
975 end get_pp_action_id;
976 --
977 --------------------------- include_action_in_payment -------------------------------
978 /* Name : include_action_in_payment
979 Purpose : This function decides whether a Payroll run should be included in
980 the payment route.
981 Arguments :
982 Notes :
983 This function is called from the payments route, after all the
984 interlock joining has been done. It's purpose is to further
985 qualify which runs should be included in the balance.
986
987 If the balance has been called with a Prepayment assignment
988 action, then all interlocked runs qualify.
989
990 If the balance is called with a Payroll Run action, not all
991 runs qualify. If the calling action is of run method Normal,
992 Process Separate or Separate Payment, then only that calling
993 action qualifies.
994
995 However, if the balance is called with a Payroll Run action
996 with a Run Method of Cumulative, all the child actions qualify
997 with the exception of Separate Payment run Methods.
998 */
999 function include_action_in_payment(p_calling_action_type in varchar2,
1000 p_calling_action_id in number,
1001 p_run_action_id in number
1002 ) return varchar2
1003 is
1004 --
1005 l_include varchar2(5);
1006 l_run_method pay_run_types_f.run_method%type;
1007 l_child_run_method pay_run_types_f.run_method%type;
1008 l_found boolean;
1009 --
1010 procedure get_action_component(p_cur_action_id in number,
1011 p_srch_action_id in number,
1012 p_found in out nocopy boolean,
1013 p_run_type out nocopy varchar2)
1014 is
1015 --
1016 cursor get_actions (p_action in number)
1017 is
1018 select paa.assignment_action_id,
1019 nvl(prt.run_method, 'N'),
1020 paa.start_date
1021 from pay_assignment_actions paa,
1022 pay_run_types_f prt
1023 where paa.source_action_id = p_action
1024 and paa.run_type_id = prt.run_type_id (+);
1025 --
1026 l_child_action pay_assignment_actions.assignment_action_id%type;
1027 l_run_method pay_run_types.run_method%type;
1028 l_start_date pay_assignment_actions.start_date%type;
1029 --
1030 begin
1031 --
1032 open get_actions(p_cur_action_id);
1033 while (p_found = FALSE) loop
1034 fetch get_actions into l_child_action, l_run_method, l_start_date;
1035 exit when get_actions%notfound;
1036 --
1037 if (l_child_action = p_srch_action_id) then
1038 p_found := TRUE;
1039 p_run_type := l_run_method;
1040 else
1041 if (l_run_method = 'C' or l_start_date is not null) then
1042 get_action_component(l_child_action,
1043 p_srch_action_id,
1044 p_found,
1045 p_run_type
1046 );
1047 end if;
1048 end if;
1049 end loop;
1050 --
1051 close get_actions;
1052 --
1053 end get_action_component;
1054 --
1055 begin
1056 --
1057 l_include := 'N';
1058 if (p_calling_action_type in ('P', 'U')) then
1059 l_include := 'Y';
1060 elsif (p_calling_action_type in ('R', 'Q')) then
1061 --
1062 select nvl(nvl(prt_aa.run_method, prt_pa.run_method), 'N')
1063 into l_run_method
1064 from pay_run_types_f prt_aa,
1065 pay_run_types_f prt_pa,
1066 pay_assignment_actions paa,
1067 pay_payroll_actions ppa
1068 where paa.assignment_action_id = p_calling_action_id
1069 and paa.payroll_action_id = ppa.payroll_action_id
1070 and paa.run_type_id = prt_aa.run_type_id (+)
1071 and ppa.effective_date between nvl(prt_aa.effective_start_date, ppa.effective_date)
1075 and nvl(prt_pa.effective_end_date, ppa.effective_date);
1072 and nvl(prt_aa.effective_end_date, ppa.effective_date)
1073 and ppa.run_type_id = prt_pa.run_type_id (+)
1074 and ppa.effective_date between nvl(prt_pa.effective_start_date, ppa.effective_date)
1076 --
1077 -- OK, if its a cumulative we need to do a bit of work
1078 if (l_run_method = 'C') then
1079 --
1080 l_found := FALSE;
1081 --
1082 get_action_component(p_calling_action_id,
1083 p_run_action_id,
1084 l_found,
1085 l_child_run_method
1086 );
1087 --
1088 -- Only include the run if its found and not a Separate Payment.
1089 --
1090 if (l_found = TRUE and l_child_run_method <> 'S') then
1091 l_include := 'Y';
1092 end if;
1093 --
1094 else
1095 if (p_calling_action_id = p_run_action_id) then
1096 l_include := 'Y';
1097 end if;
1098 end if;
1099 --
1100 end if;
1101 --
1102 return l_include;
1103 --
1104 end include_action_in_payment;
1105 --
1106 --
1107 --------------------------- set_pap_group_id ---------------------------
1108 /* Name : set_pap_group_id
1109 Purpose : Sets the pay action parameter group id
1110 Arguments :
1111 Notes :
1112 */
1113 procedure set_pap_group_id(
1114 p_pap_group_id in number
1115 )
1116 is
1117 begin
1118 hr_utility.set_location('pay_core_utils.set_pap_group_id', 10);
1119 --
1120 pay_core_utils.pay_action_parameter_group_id := p_pap_group_id;
1121 --
1122 hr_utility.set_location('pay_core_utils.set_pap_group_id', 20);
1123 --
1124 end set_pap_group_id;
1125 --
1126 --------------------------- get_pap_group_id -------------------------------
1127 /* Name : get_pap_group_id
1128 Purpose : This returns the cached pay action parameter group id
1129 Arguments :
1130 Notes :
1131 */
1132 function get_pap_group_id return number
1133 is
1134 begin
1135 return pay_core_utils.pay_action_parameter_group_id;
1136 --
1137 end get_pap_group_id;
1138 --
1139 --------------------------- get_action_parameter ------------------------
1140 /* Name : get_action_parameter
1141 Purpose : This returns the action_parameter value of a action
1142 parameter.
1143 Arguments :
1144 Notes :
1145 */
1146 procedure get_action_parameter(p_para_name in varchar2,
1147 p_para_value out nocopy varchar2,
1148 p_found out nocopy boolean
1149 )
1150 is
1151 begin
1152 --
1153 select parameter_value
1154 into p_para_value
1155 from pay_action_parameters
1156 where parameter_name = p_para_name
1157 or parameter_name=REPLACE(p_para_name,' ','_')
1158 or parameter_name=REPLACE(p_para_name,'_',' ');
1159 --
1160 p_found := TRUE;
1161 --
1162 exception
1163 when others then
1164 p_found := FALSE;
1165 --
1166 end get_action_parameter;
1167 --
1168 --------------------------- get_report_f_parameter ------------------------
1169 /* Name : get_report_f_parameter
1170 Purpose : This returns the parameter value of a report format
1171 parameter for a particular run.
1172 Arguments :
1173 Notes :
1174 */
1175 procedure get_report_f_parameter(
1176 p_payroll_action_id in number,
1177 p_para_name in varchar2,
1178 p_para_value out nocopy varchar2,
1179 p_found out nocopy boolean
1180 )
1181 is
1182 begin
1183 --
1184 select parameter_value
1185 into p_para_value
1186 from pay_report_format_parameters prfp,
1187 pay_report_format_mappings_f prfm,
1188 pay_payroll_actions ppa
1189 where ppa.payroll_action_id = p_payroll_action_id
1190 and ppa.report_type = prfm.report_type
1191 and ppa.report_qualifier = prfm.report_qualifier
1192 and ppa.report_category = prfm.report_category
1193 and prfm.report_format_mapping_id = prfp.report_format_mapping_id
1194 and ppa.effective_date between prfm.effective_start_date
1195 and prfm.effective_end_date
1196 and ( parameter_name = p_para_name
1197 or parameter_name=REPLACE(p_para_name,' ','_')
1198 or parameter_name=REPLACE(p_para_name,'_',' ')
1199 );
1200 --
1201 p_found := TRUE;
1202 --
1203 exception
1204 when others then
1205 p_found := FALSE;
1206 --
1207 end get_report_f_parameter;
1208 --
1209 --------------------------- get_legislation_rule ------------------------
1210 /* Name : get_legislation_rule
1211 Purpose : This returns the legislation rule for a legislation.
1212 Arguments :
1213 Notes :
1214 */
1215 procedure get_legislation_rule(p_legrul_name in varchar2,
1216 p_legislation in varchar2,
1217 p_legrul_value out nocopy varchar2,
1218 p_found out nocopy boolean
1219 )
1220 is
1221 begin
1222 --
1223 select rule_mode
1224 into p_legrul_value
1228 --
1225 from pay_legislation_rules
1226 where rule_type = p_legrul_name
1227 and legislation_code = p_legislation;
1229 p_found := TRUE;
1230 --
1231 exception
1232 when no_data_found then
1233 /*Bug 10212578 */
1234
1235 p_found := FALSE;
1236
1237 if substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2' then
1238
1239 if p_legrul_name = 'ADJUSTMENT_EE_SOURCE' then
1240 p_legrul_value := 'T';
1241 p_found := TRUE;
1242 elsif p_legrul_name in ('ADVANCED_RETRO','BAL_ADJ_LAT_BAL','BAL_CATEGORY_MANDATORY',
1243 'PAY_ACTION_PARAMETER_GROUPS','RETROELEMENT_CHECK',
1244 'RR_SPARSE','SAVE_ASG_RUN_BAL',
1245 'SAVE_RUN_BAL','SKIP_TERMINATED_ASG','SUPPRESS_INSIG_INDIRECTS') then
1246 p_legrul_value := 'Y';
1247 p_found := TRUE;
1248 elsif p_legrul_name = 'RETRO_DELETE' then
1249 p_legrul_value := 'N';
1250 p_found := TRUE;
1251 end if;
1252
1253 end if;
1254 --
1255 --
1256 end get_legislation_rule;
1257 --
1258 --------------------------- approved_context ----------------------
1259 /* Name : approved_context
1260 Purpose : This procedure is used be Core Payroll in order
1261 to track the legislative contexts that
1262 can be used by Oracle Localisations.
1263 Arguments :
1264 Notes :
1265 */
1266 procedure approved_context(
1267 p_legislation in varchar2,
1268 p_context_name in varchar2,
1269 p_approved out nocopy boolean,
1270 p_iv_name in out nocopy varchar2,
1271 p_found in out nocopy boolean
1272 )
1273 is
1274 l_approved boolean;
1275 begin
1276 --
1277 l_approved := FALSE;
1278 --
1279 if (p_context_name = 'ORGANIZATION_ID') then
1280 l_approved := TRUE;
1281 else
1282 if ((p_legislation = 'BF')
1283 or
1284 ( p_legislation = 'US'
1285 and p_context_name in ('JURISDICTION_CODE')
1286 )
1287 or
1288 ( p_legislation = 'CA'
1289 and p_context_name in ('JURISDICTION_CODE', 'SOURCE_ID')
1290 )
1291 or
1292 ( p_legislation = 'CN'
1293 and p_context_name in ('JURISDICTION_CODE')
1294 )
1295 or
1296 ( p_legislation = 'MX'
1297 and p_context_name in ('JURISDICTION_CODE')
1298 )
1299 or
1300 ( p_legislation = 'KR'
1301 and p_context_name in ('SOURCE_TEXT')
1302 )
1303 or
1304 ( p_legislation = 'SA'
1305 and p_context_name in ('SOURCE_TEXT', 'SOURCE_NUMBER', 'SOURCE_TEXT2')
1306 )
1307 or
1308 ( p_legislation = 'GB'
1309 and p_context_name in ('SOURCE_TEXT')
1310 )
1311 or
1312 ( p_legislation = 'NL'
1313 and p_context_name in ('SOURCE_TEXT', 'SOURCE_TEXT2')
1314 )
1315 or
1316 ( p_legislation = 'IN'
1317 and p_context_name in ('JURISDICTION_CODE', 'SOURCE_TEXT', 'SOURCE_TEXT2', 'SOURCE_ID')
1318 )
1319 or
1320 ( p_legislation = 'IE'
1321 and p_context_name in ('SOURCE_TEXT')
1322 )
1323 or
1324 ( p_legislation = 'FR'
1325 and p_context_name in ('SOURCE_TEXT', 'SOURCE_ID', 'SOURCE_TEXT2', 'SOURCE_NUMBER')
1326 )
1327 or
1328 ( p_legislation = 'HK'
1329 and p_context_name in ('SOURCE_ID')
1330 )
1331 or
1332 ( p_legislation = 'ES'
1333 and p_context_name in ('SOURCE_TEXT', 'SOURCE_TEXT2', 'SOURCE_NUMBER', 'SOURCE_NUMBER2')
1334 )
1335 or
1336 ( p_legislation = 'FI'
1337 and p_context_name in ('SOURCE_TEXT', 'SOURCE_TEXT2')
1338 )
1339 or
1340 ( p_legislation = 'NO'
1341 and p_context_name in ('JURISDICTION_CODE', 'SOURCE_TEXT', 'SOURCE_TEXT2')
1342 )
1343 or
1344 ( p_legislation = 'PL'
1345 and p_context_name in ('SOURCE_TEXT', 'SOURCE_TEXT2')
1346 )
1347 or
1348 ( p_legislation = 'ZA'
1349 and p_context_name in ('SOURCE_TEXT', 'SOURCE_NUMBER')
1350 )
1351 or
1352 ( p_legislation = 'SE'
1353 and p_context_name in ('SOURCE_TEXT')
1354 )
1355 ) then
1356 --
1357 l_approved := TRUE;
1358 --
1359 end if;
1360 end if;
1361 --
1362 if (l_approved = FALSE
1363 and p_context_name = 'JURISDICTION_CODE') then
1364 --
1365 p_iv_name := 'NuLL';
1366 p_found := TRUE;
1367 --
1368 end if;
1369 --
1370 p_approved := l_approved;
1371 --
1372 end approved_context;
1373 --
1374 --------------------------- unset_context_iv_cache ----------------------
1375 /* Name : unset_context_iv_cache
1376 Purpose : This procedure unsets the context_iv_cache
1377 Used by test harness.
1378 Arguments :
1379 Notes :
1380 */
1381 procedure unset_context_iv_cache
1382 is
1383 begin
1384 --
1385 g_leg_context_iv_rec.legislation_code := null;
1389 g_leg_context_iv_rec.source_text_iv := null;
1386 g_leg_context_iv_rec.non_oracle_local := null;
1387 g_leg_context_iv_rec.jurisdiction_iv := null;
1388 g_leg_context_iv_rec.source_id_iv := null;
1390 g_leg_context_iv_rec.source_number_iv := null;
1391 g_leg_context_iv_rec.source_number2_iv:= null;
1392 g_leg_context_iv_rec.source_text2_iv := null;
1393 g_leg_context_iv_rec.organization_id_iv := null;
1394 --
1395 end unset_context_iv_cache;
1396 --
1397 --------------------------- get_leg_context_iv_name ----------------------
1398 /* Name : get_leg_context_iv_name
1399 Purpose : This returns the name of the input value to be used for
1400 a context
1401 Arguments :
1402 Notes :
1403 */
1404 procedure get_leg_context_iv_name(p_context_name in varchar2,
1405 p_legislation in varchar2,
1406 p_inp_val_name out nocopy varchar2,
1407 p_found out nocopy boolean
1408 )
1409 is
1410 l_iv_name pay_input_values_f.name%type;
1411 l_inp_val_name pay_input_values_f.name%type;
1412 l_found boolean;
1413 l_approved boolean;
1414 --
1415 cursor csr_leg_contexts is
1416 select fc.context_name
1417 ,plc.input_value_name
1418 ,decode(fc.context_name
1419 ,'JURISDICTION_CODE' ,'JURISDICTION_IV'
1420 ,'SOURCE_ID' ,'SOURCE_IV'
1421 ,'SOURCE_TEXT' ,'SOURCE_TEXT_IV'
1422 ,'SOURCE_TEXT2' ,'SOURCE_TEXT2_IV'
1423 ,'SOURCE_NUMBER' ,'SOURCE_NUMBER_IV'
1424 ,null
1425 ) rule_type
1426 from pay_legislation_contexts plc,
1427 ff_contexts fc
1428 where plc.legislation_code(+) = p_legislation
1429 and plc.context_id (+) = fc.context_id
1430 and fc.context_name in
1431 ('JURISDICTION_CODE'
1432 ,'SOURCE_ID'
1433 ,'SOURCE_TEXT'
1434 ,'SOURCE_TEXT2'
1435 ,'SOURCE_NUMBER'
1436 ,'SOURCE_NUMBER2'
1437 ,'ORGANIZATION_ID');
1438 --
1439 begin
1440 --
1441 -- Check if cache exists for this legislation code.
1442 --
1443 if (g_leg_context_iv_rec.legislation_code = p_legislation
1444 and g_leg_context_iv_rec.legislation_code is not null) then
1445 --
1446 -- Cache already exists, do nothing.
1447 --
1448 null;
1449 --
1450 else
1451 --
1452 -- Retrieve the context definitions and set the global cache.
1453 --
1454
1455 -- set legislation code
1456 g_leg_context_iv_rec.legislation_code := p_legislation;
1457 --
1458 get_legislation_rule('NON_ORACLE_LOC',
1459 p_legislation,
1460 g_leg_context_iv_rec.non_oracle_local,
1461 l_found
1462 );
1463 if (l_found = FALSE) then
1464 g_leg_context_iv_rec.non_oracle_local := 'N';
1465 end if;
1466
1467 for l_rec in csr_leg_contexts loop
1468
1469 l_iv_name := null;
1470
1471 if l_rec.input_value_name is not null then
1472 --
1473 l_iv_name := l_rec.input_value_name;
1474 else
1475 --
1476 -- No row in pay_legislation_contexts
1477 -- Thus see if there is a leg rule
1478 -- Derive the name from legislation rule.
1479 --
1480 if (l_rec.rule_type is not null) then
1481 get_legislation_rule(l_rec.rule_type,
1482 p_legislation,
1483 l_iv_name,
1484 l_found
1485 );
1486 end if;
1487 end if;
1488 --
1489 -- Set the global record cache.
1490 --
1491 if (l_rec.context_name = 'JURISDICTION_CODE') then
1492 --
1493 g_leg_context_iv_rec.jurisdiction_iv := l_iv_name;
1494 --
1495 elsif (l_rec.context_name = 'SOURCE_ID') then
1496 --
1497 g_leg_context_iv_rec.source_id_iv := l_iv_name;
1498 --
1499 elsif (l_rec.context_name = 'SOURCE_TEXT') then
1500 --
1501 g_leg_context_iv_rec.source_text_iv := l_iv_name;
1502 --
1503 elsif (l_rec.context_name = 'SOURCE_TEXT2') then
1504 --
1505 g_leg_context_iv_rec.source_text2_iv := l_iv_name;
1506 --
1507 elsif (l_rec.context_name = 'SOURCE_NUMBER') then
1508 --
1509 g_leg_context_iv_rec.source_number_iv := l_iv_name;
1510 --
1511 elsif (l_rec.context_name = 'SOURCE_NUMBER2') then
1512 --
1513 g_leg_context_iv_rec.source_number2_iv := l_iv_name;
1514 --
1515 elsif (l_rec.context_name = 'ORGANIZATION_ID') then
1516 --
1517 g_leg_context_iv_rec.organization_id_iv := l_iv_name;
1518 end if;
1519 end loop;
1520 --
1521 end if;
1522 --
1523 -- Set out variables.
1524 --
1525 if (p_context_name = 'JURISDICTION_CODE') then
1526 --
1527 l_inp_val_name := g_leg_context_iv_rec.jurisdiction_iv;
1528 --
1529 elsif (p_context_name = 'SOURCE_ID') then
1530 --
1531 l_inp_val_name := g_leg_context_iv_rec.source_id_iv;
1532 --
1533 elsif (p_context_name = 'SOURCE_TEXT') then
1534 --
1535 l_inp_val_name := g_leg_context_iv_rec.source_text_iv;
1536 --
1540 --
1537 elsif (p_context_name = 'SOURCE_TEXT2') then
1538 --
1539 l_inp_val_name := g_leg_context_iv_rec.source_text2_iv;
1541 elsif (p_context_name = 'SOURCE_NUMBER') then
1542 --
1543 l_inp_val_name := g_leg_context_iv_rec.source_number_iv;
1544 --
1545 elsif (p_context_name = 'SOURCE_NUMBER2') then
1546 --
1547 l_inp_val_name := g_leg_context_iv_rec.source_number2_iv;
1548 --
1549 elsif (p_context_name = 'ORGANIZATION_ID') then
1550 --
1551 l_inp_val_name := g_leg_context_iv_rec.organization_id_iv;
1552 --
1553 else
1554 --
1555 l_inp_val_name := null;
1556 --
1557 end if;
1558 --
1559 l_found := (l_inp_val_name is not null);
1560 --
1561 if (l_found = TRUE
1562 and g_leg_context_iv_rec.non_oracle_local = 'N') then
1563 --
1564 approved_context(
1565 p_legislation => p_legislation,
1566 p_context_name => p_context_name,
1567 p_approved => l_approved,
1568 p_iv_name => l_inp_val_name,
1569 p_found => l_found
1570 );
1571 --
1572 if (l_approved = FALSE
1573 and p_context_name <> 'JURISDICTION_CODE') then
1574 --
1575 pay_core_utils.assert_condition('pay_core_utils.get_leg_context_iv_name:1',
1576 1 = 2);
1577 --
1578 end if;
1579 --
1580 end if;
1581 --
1582 p_found := l_found;
1583 p_inp_val_name := l_inp_val_name;
1584 --
1585 end get_leg_context_iv_name;
1586 --
1587 --------------------------- get_dynamic_contexts ----------------------
1588 /* Name : get_dynamic_contexts
1589 Purpose : This returns a table containg the dynamic contexts.
1590 Arguments :
1591 Notes :
1592 */
1593 procedure get_dynamic_contexts(p_business_group_id in number,
1594 p_context_list out nocopy t_contexts_tab
1595 )
1596 is
1597 cursor get_contexts (p_bus_grp in number) is
1598 select fc.context_name,
1599 pbg.legislation_code
1600 from ff_contexts fc,
1601 per_business_groups_perf pbg
1602 where pbg.business_group_id = p_bus_grp
1603 and fc.context_name in ('JURISDICTION_CODE',
1604 'SOURCE_ID',
1605 'SOURCE_TEXT',
1606 'SOURCE_NUMBER',
1607 'SOURCE_TEXT2',
1608 'SOURCE_NUMBER2',
1609 'ORGANIZATION_ID');
1610 --
1611 l_inp_val_name pay_input_values_f.name%type;
1612 l_default boolean;
1613 l_plsql varchar2(60);
1614 --
1615 l_legrul_value pay_legislation_rules.rule_mode%type;
1616 l_found boolean;
1617 l_cnt number;
1618 --
1619 begin
1620 --
1621 p_context_list.delete;
1622 --
1623 for cxtrec in get_contexts(p_business_group_id) loop
1624 --
1625 l_inp_val_name := null;
1626 --
1627 get_leg_context_iv_name(cxtrec.context_name,
1628 cxtrec.legislation_code,
1629 l_inp_val_name,
1630 l_found
1631 );
1632 --
1633 --
1634 -- Default the input value name if needed
1635 --
1636 if (l_found = FALSE) then
1637 if (cxtrec.context_name = 'JURISDICTION_CODE') then
1638 l_inp_val_name := 'Jurisdiction';
1639 end if;
1640 end if;
1641 --
1642 -- OK do we have a context that this legislation
1643 -- uses.
1644 if (l_inp_val_name is not null) then
1645 --
1646 l_default := FALSE;
1647 l_plsql := null;
1648 --
1649 if (cxtrec.context_name = 'JURISDICTION_CODE') then
1650 --
1651 get_legislation_rule('DEFAULT_JURISDICTION',
1652 cxtrec.legislation_code,
1653 l_legrul_value,
1654 l_found
1655 );
1656 --
1657 if (l_found = FALSE) then
1658 l_legrul_value := 'N';
1659 end if;
1660 --
1661 if (l_legrul_value = 'Y') then
1662 l_default := TRUE;
1663 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_default_jurisdiction';
1664 end if;
1665 --
1666 elsif (cxtrec.context_name = 'SOURCE_ID') then
1667 --
1668 l_default := TRUE;
1669 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_source_context';
1670 --
1671 elsif (cxtrec.context_name = 'SOURCE_TEXT') then
1672 --
1673 l_default := TRUE;
1674 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_source_text_context';
1675 --
1676 elsif (cxtrec.context_name = 'SOURCE_TEXT2') then
1677 --
1678 l_default := TRUE;
1679 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_source_text2_context';
1680 --
1681 elsif (cxtrec.context_name = 'SOURCE_NUMBER') then
1682 --
1683 l_default := TRUE;
1684 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_source_number_context';
1685 --
1686 elsif (cxtrec.context_name = 'SOURCE_NUMBER2') then
1687 --
1688 l_default := TRUE;
1689 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_source_number2_context';
1690 --
1691 elsif (cxtrec.context_name = 'ORGANIZATION_ID') then
1692 --
1696 end if;
1693 l_default := TRUE;
1694 l_plsql := 'pay_'||cxtrec.legislation_code||'_rules.get_third_party_org_context';
1695 --
1697 --
1698 -- Add new entry onto list
1699 l_cnt := p_context_list.count+1;
1700 p_context_list(l_cnt).context_name := cxtrec.context_name;
1701 p_context_list(l_cnt).is_context_def := l_default;
1702 p_context_list(l_cnt).input_value_name := l_inp_val_name;
1703 p_context_list(l_cnt).default_plsql := l_plsql;
1704 --
1705 end if;
1706 --
1707 end loop;
1708 --
1709 end get_dynamic_contexts;
1710 --
1711 --------------------------- check_ctx_set -------------------------------
1712 /* Name : check_ctx_set
1713 Purpose : this returns 'Y' if
1714 - no context input value exists on the entry
1715 - or context input value exists on the entry
1716 and matches p_context_value.
1717 */
1718 function check_ctx_set (p_ee_id in number,
1719 p_context_name in varchar2,
1720 p_context_value in varchar2
1721 ) return varchar2
1722 is
1723 --
1724 l_exists number;
1725 l_ivid number;
1726 --
1727 begin
1728 --
1729 select distinct piv.input_value_id
1730 into l_ivid
1731 from pay_input_values_f piv,
1732 pay_element_entry_values_f peev
1733 where peev.element_entry_id = p_ee_id
1734 and peev.input_value_id = piv.input_value_id
1735 and piv.name = p_context_name;
1736
1737 select count(*)
1738 into l_exists
1739 from pay_element_entry_values_f peev
1740 where peev.element_entry_id = p_ee_id
1741 and peev.input_value_id = l_ivid
1742 and peev.screen_entry_value = p_context_value;
1743 --
1744 if (l_exists > 0) then
1745 return 'Y';
1746 else
1747 return 'N';
1748 end if;
1749 --
1750 exception
1751 when no_data_found then
1752 return 'Y';
1753 --
1754 end;
1755
1756 procedure assert_condition (p_location in varchar2,
1757 p_condition in boolean) is
1758 --
1759 -- Checks that assumptions made within pl/sql code are true. Use to check the
1760 -- parameters to a pl/sql function or procedure before processing. If the
1761 -- assumption made by a procedure (eg p_parameter is not null) is not true
1762 -- then an error is raised to prevent processing from continuing.
1763 --
1764 begin
1765 --
1766 if not p_condition
1767 then
1768 hr_utility.set_message(801, 'HR_6882_HRPROC_ASSERT') ;
1769 hr_utility.set_message_token('LOCATION', p_location);
1770 hr_utility.raise_error ;
1771 end if;
1772 --
1773 end assert_condition;
1774 --
1775 --
1776 --------------------------- get_proc_sep_trigger -------------------------------
1777 /* Name : get_proc_sep_trigger
1778 Purpose : returns the element entry id that caused a process
1779 separately/separate payment run type to process
1780 */
1781 function get_proc_sep_trigger(p_asg_action_id in number)
1782 return number
1783 is
1784 --
1785 cursor c_procsep (p_aa_id number,
1786 p_eff_date date)
1787 is
1788 select prr.source_id
1789 from pay_run_results prr,
1790 pay_element_types_f pet
1791 where prr.assignment_action_id = p_aa_id
1792 and prr.source_type = 'E'
1793 and prr.element_type_id = pet.element_type_id
1794 and nvl(pet.process_mode, 'N') in ('P', 'S')
1795 and prr.entry_type not in ('A', 'R')
1796 and p_eff_date between pet.effective_start_date
1797 and pet.effective_end_date
1798 order by decode (prr.status,
1799 'P', 1,
1800 'B', 2,
1801 3),
1802 decode (prr.entry_type,
1803 'S', 1,
1804 2);
1805 --
1806 l_run_meth pay_run_types_f.run_method%type;
1807 l_eff_date pay_payroll_actions.effective_date%type;
1808 l_ee_id pay_run_results.source_id%type;
1809 --
1810 begin
1811 --
1812 select nvl(prt.run_method, 'N'),
1813 ppa.effective_date
1814 into l_run_meth,
1815 l_eff_date
1816 from pay_run_types_f prt,
1817 pay_assignment_actions paa,
1818 pay_payroll_actions ppa
1819 where paa.assignment_action_id = p_asg_action_id
1820 and ppa.payroll_action_id = paa.payroll_action_id
1821 and nvl(paa.run_type_id, -999) = prt.run_type_id (+)
1822 and ppa.effective_date
1823 between nvl(prt.effective_start_date, ppa.effective_date)
1824 and nvl(prt.effective_end_date, ppa.effective_date);
1825 --
1826 if (l_run_meth not in ('P', 'S')) then
1827 return null;
1828 else
1829 open c_procsep(p_asg_action_id, l_eff_date);
1830 fetch c_procsep into l_ee_id;
1831 close c_procsep;
1832 return l_ee_id;
1833 end if;
1834 --
1835 end;
1836 --
1837 --------------------------- get_process_path -------------------------------
1838 /* Name : get_process_path
1839 Purpose : returns the processing path given an assignment action
1840 */
1841 procedure get_process_path(p_asg_action_id in number,
1842 p_process_path in out nocopy varchar2)
1843 is
1844 --
1845 l_run_type_id pay_assignment_actions.run_type_id%type;
1846 l_src_id pay_assignment_actions.source_action_id%type;
1847 l_ee_trigger pay_element_entries_f.element_entry_id%type;
1851 --
1848 l_start_date varchar2(30);
1849 l_end_date varchar2(30);
1850 begin
1852 select run_type_id,
1853 source_action_id,
1854 to_char(start_date, 'YYYY/MM/DD HH24:MI:SS'),
1855 to_char(end_date, 'YYYY/MM/DD HH24:MI:SS')
1856 into l_run_type_id,
1857 l_src_id,
1858 l_start_date,
1859 l_end_date
1860 from pay_assignment_actions
1861 where assignment_action_id = p_asg_action_id;
1862 --
1863 l_ee_trigger := get_proc_sep_trigger(p_asg_action_id);
1864 --
1865 if (l_ee_trigger is null) then
1866 --
1867 p_process_path := to_char(l_run_type_id)||'.'||p_process_path;
1868 --
1869 else
1870 --
1871 p_process_path := to_char(l_run_type_id)||'('||
1872 to_char(l_ee_trigger)||').'||p_process_path;
1873 --
1874 end if;
1875 --
1876 if ((l_start_date is not null) and (l_end_date is not null))
1877 then
1878 p_process_path := '[' || l_start_date || ']' ||
1879 '[' || l_end_date || ']' || p_process_path;
1880 end if;
1881 --
1882 if (l_src_id is not null) then
1883 get_process_path(l_src_id , p_process_path);
1884 end if;
1885 --
1886 end get_process_path;
1887 --
1888 function get_process_path(p_asg_action_id in number)
1889 return varchar2
1890 is
1891 l_process_path varchar2(2000);
1892 begin
1893 --
1894 if (g_asg_action_id is not null and
1895 p_asg_action_id = g_asg_action_id) then
1896 l_process_path := g_process_path;
1897 else
1898 l_process_path := '';
1899 get_process_path(p_asg_action_id, l_process_path);
1900 g_asg_action_id := p_asg_action_id;
1901 g_process_path := l_process_path;
1902 end if;
1903 --
1904 return l_process_path;
1905 --
1906 end get_process_path;
1907 --
1908 --
1909 function get_sql_cursor(p_statement in varchar2,
1910 p_sql_cur out nocopy number) return boolean
1911 is
1912 l_sql_cur number;
1913 l_cnt number;
1914 l_found boolean := FALSE;
1915 begin
1916 --
1917 for l_cnt in 1..g_sql_cursors.count loop
1918 if p_statement = g_sql_cursors(l_cnt).statement then
1919 l_sql_cur := g_sql_cursors(l_cnt).sql_cur;
1920 l_found := TRUE;
1921 exit;
1922 end if;
1923 end loop;
1924 --
1925 if not l_found then
1926 begin
1927 l_sql_cur := dbms_sql.open_cursor;
1928 dbms_sql.parse(l_sql_cur,
1929 p_statement,
1930 dbms_sql.v7);
1931 l_found := TRUE;
1932 --
1933 l_cnt := g_sql_cursors.count + 1;
1934 g_sql_cursors(l_cnt).statement := p_statement;
1935 g_sql_cursors(l_cnt).sql_cur := l_sql_cur;
1936 --
1937 exception
1938 when others then
1939 if dbms_sql.is_open(l_sql_cur) then
1940 dbms_sql.close_cursor(l_sql_cur);
1941 end if;
1942 end;
1943 --
1944 end if;
1945 --
1946 if l_found then
1947 p_sql_cur := l_sql_cur;
1948 end if;
1949 --
1950 return l_found;
1951 --
1952 end get_sql_cursor;
1953 --
1954 procedure close_all_sql_cursors
1955 is
1956 l_cnt number;
1957 begin
1958 --
1959 for l_cnt in 1..g_sql_cursors.count loop
1960 if dbms_sql.is_open(g_sql_cursors(l_cnt).sql_cur) then
1961 dbms_sql.close_cursor(g_sql_cursors(l_cnt).sql_cur);
1962 end if;
1963 end loop;
1964 --
1965 g_sql_cursors.delete;
1966 --
1967 end close_all_sql_cursors;
1968 --
1969 procedure close_sql_cursor(p_sql_cur number)
1970 is
1971 l_cnt number;
1972 begin
1973 --
1974 for l_cnt in 1..g_sql_cursors.count loop
1975 if g_sql_cursors(l_cnt).sql_cur = p_sql_cur then
1976 --
1977 if dbms_sql.is_open(g_sql_cursors(l_cnt).sql_cur) then
1978 dbms_sql.close_cursor(g_sql_cursors(l_cnt).sql_cur);
1979 end if;
1980 g_sql_cursors.delete(l_cnt);
1981 --
1982 end if;
1983 end loop;
1984 --
1985 end close_sql_cursor;
1986 --
1987 --------------------------- get_upgrade_status -------------------------------
1988 /* Name : get_upgrade_status
1989 Purpose : returns the upgrade status (Y or N) for a specified
1990 upgrade.
1991 */
1992 procedure get_upgrade_status(p_bus_grp_id in number,
1993 p_short_name in varchar2,
1994 p_status out nocopy varchar2,
1995 p_raise_error in boolean default TRUE)
1996 is
1997 --
1998 l_upgrade_definition_id pay_upgrade_definitions.upgrade_definition_id%type;
1999 l_legislation_code pay_upgrade_definitions.legislation_code%type;
2000 l_upgrade_level pay_upgrade_definitions.upgrade_level%type;
2001 l_failure_point pay_upgrade_definitions.failure_point%type;
2002 l_legislatively_enabled pay_upgrade_definitions.legislatively_enabled%type;
2003 l_bg_leg_code per_business_groups.legislation_code%type;
2004 l_status pay_upgrade_status.status%type;
2005 l_upgrade_status pay_upgrade_status.status%type;
2006 l_check_upgrade boolean;
2007 l_dummy number;
2008 --
2009 begin
2010 --
2011 begin
2012 select pud.upgrade_definition_id,
2013 pud.legislation_code,
2014 pud.upgrade_level,
2015 pud.failure_point,
2016 pud.legislatively_enabled
2020 l_failure_point,
2017 into l_upgrade_definition_id,
2018 l_legislation_code,
2019 l_upgrade_level,
2021 l_legislatively_enabled
2022 from pay_upgrade_definitions pud
2023 where pud.short_name = p_short_name;
2024 --
2025 -- Bugfix 3494732
2026 -- Only fetch the legislation_code when a non-null
2027 -- bg id has been passed in.
2028 if p_bus_grp_id is not null then
2029 --
2030 select pbg.legislation_code
2031 into l_bg_leg_code
2032 from per_business_groups_perf pbg
2033 where pbg.business_group_id = p_bus_grp_id;
2034 --
2035 end if;
2036 --
2037
2038 /* Bug 13358562 , Enable Sparse Matrix by default
2039 if the release is 12.2 then return the l_status as 'Y'
2040 */
2041 if substr(fnd_release.release_name,1,2) = '12' and substr(fnd_release.release_name,4,1) >= '2'
2042 and p_short_name ='ENABLE_RR_SPARSE' then
2043 l_status := 'Y';
2044
2045 /* If the legislation codes do not match
2046 then the upgrade is not applicable to the
2047 BG. Therefore return N.
2048 */
2049 elsif (l_legislation_code is not null
2050 and l_legislation_code <> l_bg_leg_code) then
2051 --
2052 l_status := 'N';
2053 --
2054 else
2055 --
2056 /* Is this a globally defined upgrade (Core) that
2057 needs to be switched on by a legislation
2058 */
2059 l_check_upgrade := TRUE;
2060 if (l_legislation_code is null and
2061 l_legislatively_enabled = 'Y') then
2062 --
2063 l_check_upgrade := FALSE;
2064 --
2065 begin
2066 select 1
2067 into l_dummy
2068 from pay_upgrade_legislations pul
2069 where pul.upgrade_definition_id = l_upgrade_definition_id
2070 and pul.legislation_code = l_bg_leg_code;
2071 --
2072 l_check_upgrade := TRUE;
2073 --
2074 exception
2075 when no_data_found then
2076 l_check_upgrade := FALSE;
2077 end;
2078 --
2079 end if;
2080 --
2081 if (l_check_upgrade = FALSE) then
2082 --
2083 l_status := 'N';
2084 --
2085 else
2086 --
2087 /* Now for the different types of upgrades workout
2088 if the upgrade has been done
2089 */
2090 if (l_upgrade_level = 'B') then
2091 --
2092 begin
2093 --
2094 select pus.status
2095 into l_upgrade_status
2096 from pay_upgrade_status pus
2097 where pus.upgrade_definition_id = l_upgrade_definition_id
2098 and pus.business_group_id = p_bus_grp_id;
2099 --
2100 if (l_upgrade_status = 'C') then
2101 l_status := 'Y';
2102 elsif ((( l_upgrade_status = 'P'
2103 and l_failure_point = 'P')
2104 or l_failure_point = 'A')
2105 and p_raise_error) then
2106 --
2107 pay_core_utils.assert_condition(
2108 'pay_core_utils.get_upgrade_status:1',
2109 1 = 2);
2110 --
2111 else
2112 l_status := 'N';
2113 end if;
2114 --
2115 exception
2116 when no_data_found then
2117 if (( l_failure_point = 'A')
2118 and p_raise_error) then
2119 --
2120 pay_core_utils.assert_condition(
2121 'pay_core_utils.get_upgrade_status:1',
2122 1 = 2);
2123 --
2124 else
2125 l_status := 'N';
2126 end if;
2127 end ;
2128 --
2129 elsif (l_upgrade_level = 'L') then
2130 --
2131 begin
2132 --
2133 select pus.status
2134 into l_upgrade_status
2135 from pay_upgrade_status pus
2136 where pus.upgrade_definition_id = l_upgrade_definition_id
2137 and pus.legislation_code = l_bg_leg_code;
2138 --
2139 if (l_upgrade_status = 'C') then
2140 l_status := 'Y';
2141 elsif ((( l_upgrade_status = 'P'
2142 and l_failure_point = 'P' )
2143 or l_failure_point = 'A')
2144 and p_raise_error) then
2145 --
2146 pay_core_utils.assert_condition(
2147 'pay_core_utils.get_upgrade_status:1',
2148 1 = 2);
2149 --
2150 else
2151 l_status := 'N';
2152 end if;
2153 --
2154 exception
2155 when no_data_found then
2156 if ((l_failure_point = 'A')
2157 and p_raise_error) then
2158 --
2159 pay_core_utils.assert_condition(
2160 'pay_core_utils.get_upgrade_status:1',
2161 1 = 2);
2162 --
2163 else
2164 l_status := 'N';
2165 end if;
2166 end ;
2167 --
2168 elsif (l_upgrade_level = 'G') then
2169 --
2170 begin
2171 --
2172 select pus.status
2173 into l_upgrade_status
2177 and pus.business_group_id is null;
2174 from pay_upgrade_status pus
2175 where pus.upgrade_definition_id = l_upgrade_definition_id
2176 and pus.legislation_code is null
2178 --
2179 if (l_upgrade_status = 'C') then
2180 l_status := 'Y';
2181 elsif ((( l_upgrade_status = 'P'
2182 and l_failure_point = 'P' )
2183 or l_failure_point = 'A')
2184 and p_raise_error) then
2185 --
2186 pay_core_utils.assert_condition(
2187 'pay_core_utils.get_upgrade_status:1',
2188 1 = 2);
2189 --
2190 else
2191 l_status := 'N';
2192 end if;
2193 --
2194 exception
2195 when no_data_found then
2196 if (( l_failure_point = 'A')
2197 and p_raise_error) then
2198 --
2199 pay_core_utils.assert_condition(
2200 'pay_core_utils.get_upgrade_status:1',
2201 1 = 2);
2202 --
2203 else
2204 l_status := 'N';
2205 end if;
2206 end ;
2207 --
2208 else
2209 --
2210 /* Force an assertion */
2211 pay_core_utils.assert_condition('pay_core_utils.get_upgrade_status:2',
2212 1 = 2);
2213 end if;
2214 end if;
2215 --
2216 end if;
2217 --
2218 exception
2219 when no_data_found then
2220 l_status := 'N';
2221 end;
2222 --
2223 p_status := l_status;
2224 --
2225 end get_upgrade_status;
2226 --
2227 --------------------------- get_upgrade_status -------------------------------
2228 /* Name : get_upgrade_status
2229 Purpose : returns the upgrade status (Y or N) for a specified
2230 upgrade.
2231 */
2232 function get_upgrade_status(
2233 p_bus_grp_id in number,
2234 p_short_name in varchar2,
2235 p_raise_error in varchar2 default 'TRUE') return varchar2
2236 is
2237 l_status pay_upgrade_status.status%type;
2238 begin
2239 get_upgrade_status(
2240 p_bus_grp_id => p_bus_grp_id,
2241 p_short_name => p_short_name,
2242 p_status => l_status,
2243 p_raise_error => (nvl(upper(p_raise_error), 'TRUE') = 'TRUE'));
2244 --
2245 return l_status;
2246 end get_upgrade_status;
2247 --
2248 function getprl(p_pactid in number) return varchar2
2249 is
2250 l_payroll_name varchar2(80);
2251
2252 begin
2253
2254 select ppf.payroll_name
2255 into l_payroll_name
2256 from pay_payroll_actions ppa,
2257 pay_payrolls_f ppf
2258 where ppa.payroll_action_id=p_pactid
2259 and nvl(ppa.payroll_id,-9999)=ppf.payroll_id
2260 and ppa.effective_date between ppf.effective_start_date and effective_end_date;
2261
2262 return l_payroll_name;
2263
2264 exception
2265 when no_data_found then
2266 return null;
2267
2268 end getprl;
2269
2270 --------------------------- get_context_iv_name -------------------------------
2271 /* Name : get_context_iv_name
2272 Purpose : returns context input value name for an
2273 assignment action id
2274 */
2275
2276 Function get_context_iv_name (p_asg_act_id in number,
2277 p_context in varchar2) return varchar2
2278 is
2279 --
2280 l_context_iv_name pay_legislation_contexts.input_value_name%type;
2281 l_found boolean;
2282 --
2283 begin
2284 --
2285 if (g_leg_code is null) then
2286 select pbg.legislation_code
2287 into g_leg_code
2288 from pay_assignment_actions paa,
2289 pay_payroll_actions ppa,
2290 per_business_groups_perf pbg
2291 where paa.assignment_action_id = p_asg_act_id
2292 and ppa.business_group_id = pbg.business_group_id
2293 and paa.payroll_action_id = ppa.payroll_action_id;
2294 end if;
2295 --
2296 get_leg_context_iv_name(p_context_name => p_context,
2297 p_legislation => g_leg_code,
2298 p_inp_val_name => l_context_iv_name,
2299 p_found => l_found
2300 );
2301 --
2302 return l_context_iv_name;
2303 --
2304 end get_context_iv_name;
2305 --
2306 function is_element_included (p_element_type_id in number,
2307 p_run_type_id in number,
2308 p_effective_date in date,
2309 p_business_group_id in number,
2310 p_legislation in varchar2,
2311 p_label in varchar) return varchar2
2312 is
2313 --
2314 l_class_inc varchar2(1);
2315 l_et_inc varchar2(1);
2316 l_rt_inc varchar2(1);
2317 is_element_inc varchar2(1);
2318 --
2319 cursor get_class_inc(p_element_type_id in number,
2320 p_run_type_id in number,
2321 p_effective_date in date,
2322 p_business_group_id in number,
2323 p_legislation in varchar2)
2324 is
2325 SELECT nvl(INCLUSION_FLAG, 'Y')
2326 FROM PAY_ELEMENT_CLASS_USAGES_F pecu,
2327 PAY_ELEMENT_TYPES_F pet,
2331 AND pec.classification_id = pet.classification_id
2328 PAY_ELEMENT_CLASSIFICATIONS pec
2329 WHERE pet.element_type_id = p_element_type_id
2330 AND pet.classification_id = pecu.classification_id
2332 -- Only checking for primary classifications here
2333 -- Will also need to check for sub classifications exclusions
2334 AND pec.PARENT_CLASSIFICATION_ID is null
2335 AND pecu.run_type_id = p_run_type_id
2336 AND (pecu.business_group_id = p_business_group_id
2337 OR (pecu.business_group_id is null
2338 AND pecu.legislation_code = p_legislation)
2339 OR (pecu.business_group_id is null and pecu.legislation_code is null))
2340 AND (pec.business_group_id = p_business_group_id
2341 OR (pec.business_group_id is null
2342 AND pec.legislation_code = p_legislation)
2343 OR (pec.business_group_id is null and pec.legislation_code is null))
2344 AND (pet.business_group_id = p_business_group_id
2345 OR (pet.business_group_id is null
2346 AND pet.legislation_code = p_legislation)
2347 OR (pet.business_group_id is null and pet.legislation_code is null))
2348 AND p_effective_date between pet.effective_start_date
2349 and pet.effective_end_date
2350 AND p_effective_date between pecu.effective_start_date
2351 and pecu.effective_end_date;
2352
2353
2354 cursor get_runtype_inc(p_run_type_id in number,
2355 p_effective_date in date,
2356 p_business_group_id in number,
2357 p_legislation in varchar2)
2358 is
2359 SELECT nvl(INCLUSION_FLAG, 'Y')
2360 FROM PAY_ELEMENT_CLASS_USAGES_F pecu
2361 WHERE pecu.run_type_id = p_run_type_id
2362 AND (pecu.business_group_id = p_business_group_id
2363 OR (pecu.business_group_id is null
2364 AND pecu.legislation_code = p_legislation)
2365 OR (pecu.business_group_id is null and pecu.legislation_code is null))
2366 AND p_effective_date between pecu.effective_start_date
2367 and pecu.effective_end_date;
2368
2369
2370 --
2371 cursor get_element_inc(p_element_type_id in number,
2372 p_run_type_id in number,
2373 p_effective_date in date,
2374 p_business_group_id in number,
2375 p_legislation in varchar2)
2376 is
2377 SELECT INCLUSION_FLAG
2378 FROM pay_element_type_usages_f
2379 WHERE element_type_id = p_element_type_id
2380 AND run_type_id = p_run_type_id
2381 AND nvl(usage_type, 'I') = 'I'
2382 AND (business_group_id = p_business_group_id
2383 OR (business_group_id is null
2384 AND legislation_code = p_legislation)
2385 OR (business_group_id is null and legislation_code is null))
2386 AND p_effective_date between effective_start_date
2387 and effective_end_date;
2388 --
2389 begin
2390 --
2391 --
2392 --
2393
2394
2395
2396 /* exlude if excluded at class or et level,
2397 or when no label if no usages exist at class but some exist at rt level */
2398
2399 /* include if included at class level and no usage defined at et
2400 or
2401 if label, no usage defined at class level, or et
2402 or
2403 if no label, no usage defined at class or runtype or et */
2404
2405 is_element_inc := 'Y';
2406
2407 open get_element_inc(p_element_type_id,
2408 p_run_type_id,
2409 p_effective_date,
2410 p_business_group_id,
2411 p_legislation);
2412 fetch get_element_inc into l_et_inc;
2413
2414
2415 if (get_element_inc%found and l_et_inc = 'N') then
2416 close get_element_inc;
2417 is_element_inc := 'N';
2418 else
2419
2420 close get_element_inc;
2421
2422 open get_class_inc(p_element_type_id,
2423 p_run_type_id,
2424 p_effective_date,
2425 p_business_group_id,
2426 p_legislation);
2427 fetch get_class_inc into l_class_inc;
2428
2429 if (get_class_inc%found and l_class_inc = 'N') then
2430 close get_class_inc;
2431 is_element_inc := 'N';
2432 else
2433
2434 if (p_label is null and get_class_inc%notfound) then
2435
2436 open get_runtype_inc( p_run_type_id,
2437 p_effective_date,
2438 p_business_group_id,
2439 p_legislation);
2440 fetch get_runtype_inc into l_rt_inc;
2441
2442 if (get_runtype_inc%found) then
2443 is_element_inc := 'N';
2444 end if;
2445 close get_runtype_inc;
2446
2447 end if;
2448 close get_class_inc;
2449 end if;
2450 end if;
2451 --
2452 --
2453 return is_element_inc;
2454 --
2455 end is_element_included;
2456 --
2457 /* Bug 11708644, PAYROLL/PROJECTS INTEGRATION FOR 12.2 */
2458 PROCEDURE update_prj_flag ( p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
2459 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE ,
2460 p_flag IN VARCHAR2,
2461 p_status OUT NOCOPY varchar2
2462 ) IS
2463 lv_proc VARCHAR2(30) := 'update_prj_flag';
2464 v_input_value_id pay_input_values_f.input_value_id%TYPE := null;
2465 --
2466 CURSOR search_input_value_id (
2467 v_element_type_id pay_element_types_f.element_type_id%TYPE
2468 ) is
2469 SELECT DISTINCT input_value_id
2470 FROM pay_input_values_f
2471 WHERE element_type_id=v_element_type_id
2472 AND NAME = 'Pay Value';
2473 --
2474 BEGIN
2475 --
2476 if (g_traces) then
2477 hr_utility.trace('Entered pay_core_utils '|| lv_proc);
2478 end if;
2479 --
2480 OPEN search_input_value_id(p_element_type_id);
2481 FETCH search_input_value_id INTO v_input_value_id;
2482 --
2483 if search_input_value_id%notfound THEN
2484 close search_input_value_id;
2485 p_status := 'NO_PAY_VALUE_FOUND';
2486 if (g_traces) then
2487 hr_utility.trace('No Pay Value for the element: '|| p_element_type_id);
2488 end if;
2489 end if;
2490 CLOSE search_input_value_id;
2491 --
2492 UPDATE pay_costs SET transfered_to_prj = p_flag
2493 WHERE input_value_id = v_input_value_id
2494 AND assignment_action_id = p_assignment_action_id;
2495 --
2496 IF SQL%ROWCOUNT > 0 THEN
2497 p_status := 'Success';
2498 END IF ;
2499 --
2500 hr_utility.trace('Leaving pay_core_utils '|| lv_proc);
2501 --
2502 EXCEPTION
2503 WHEN OTHERS THEN
2504 p_status := 'Error';
2505 raise;
2506 END;
2507 --
2508 begin
2509 g_business_group_id := null;
2510 g_cache_business_group := FALSE;
2511 g_legislation_code := null;
2512 g_message_stack.sz := 0;
2513 g_message_tokens.sz := 0;
2514 g_process_path := null;
2515 g_asg_action_id := null;
2516 end pay_core_utils;
[Home] [Help]