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