1 package body hr_elements as
2 /* $Header: pyelemnt.pkb 120.1.12010000.4 2010/01/06 10:37:22 sivanara ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 --
5 /*
6 NAME
7 chk_element_name
8 DESCRIPTION
9 Checks element name for duplication.
10 */
11 --
12 PROCEDURE chk_element_name(p_element_name in varchar2,
13 p_element_type_id in number,
14 p_val_start_date in date,
15 p_val_end_date in date,
16 p_business_group_id in number,
17 p_legislation_code in varchar2) is
18 --
19 v_validation_check varchar(2) := 'Y';
20 begin
21 -- if the business group is null then we should make sure that the
22 -- element name is checked by legislation code.
23 --
24 begin
25 --
26 select 'N'
27 into v_validation_check
28 from sys.dual
29 where exists (select 1
30 from pay_element_types_f
31 where upper(p_element_name) = upper(element_name)
32 and (p_element_type_id <> element_type_id
33 or p_element_type_id is null)
34 and (p_business_group_id = business_group_id + 0
35 or (business_group_id is null
36 and (p_legislation_code = legislation_code))));
37 --
38 exception
39 when NO_DATA_FOUND then NULL;
40 end;
41 --
42 if v_validation_check = 'N' then
43 --
44 hr_utility.set_message(801,'PAY_6137_ELEMENT_DUP_NAME');
45 hr_utility.raise_error;
46 --
47 end if;
48 --
49 end chk_element_name;
50 --
51 /*
52 NAME
53 chk_reporting_name
54 DESCRIPTION
55 Checks reporting name for duplication. Will only be called if reporting
56 name is not null.
57 THIS CHECK IS NO LONGER VALID AS WE NOW SUPPORT DUPLICATE REPORTING NAMES
58 */
59 --
60 PROCEDURE chk_reporting_name(p_reporting_name in varchar2,
61 p_element_type_id in number,
62 p_val_start_date in date,
63 p_val_end_date in date,
64 p_business_group_id in number,
65 p_legislation_code in varchar2) is
66 --
67 v_validation_check varchar(2) := 'Y';
68 begin
69 -- if the business group is null then we should make sure that the
70 -- reporting name is checked by legislation
71 null;
72 --
73 -- begin
74 --
75 -- select 'N'
76 -- into v_validation_check
77 -- from sys.dual
78 -- where exists (select 1
79 -- from pay_element_types_f
80 -- where upper(p_reporting_name) = upper(reporting_name)
81 -- and (p_element_type_id <> element_type_id
82 -- or p_element_type_id is null)
83 -- and (p_business_group_id = business_group_id + 0
84 -- or (p_business_group_id is null
85 -- and (p_legislation_code = legislation_code))));
86 --
87 -- exception
88 -- when NO_DATA_FOUND then NULL;
89 -- end;
90 --
91 -- if v_validation_check = 'N' then
92 --
93 -- hr_utility.set_message(801,'PAY_6138_ELEMENT_DUP_REP_NAME');
94 -- hr_utility.raise_error;
95 --
96 -- end if;
97 --
98 end chk_reporting_name;
99 --
100 /*
101 NAME
102 chk_element_type
103 DESCRIPTION
104 Checks attributes of element type according to business rules
105 */
106 --
107 PROCEDURE chk_element_type(p_element_name in varchar2,
108 p_element_type_id in number,
109 p_val_start_date in date,
110 p_val_end_date in date,
111 p_reporting_name in varchar2,
112 p_rowid in varchar2,
113 p_recurring_flag in varchar2,
114 p_standard_flag in varchar2,
115 p_scndry_ent_allwd_flag in varchar2,
116 p_process_in_run_flag in varchar2,
117 p_indirect_only_flag in varchar2,
118 p_adjustment_only_flag in varchar2,
119 p_multiply_value_flag in varchar2,
120 p_classification_type in varchar2,
121 p_output_currency_code in varchar2,
122 p_input_currency_code in varchar2,
123 p_business_group_id in number,
124 p_legislation_code in varchar2,
125 p_bus_grp_currency_code in varchar2) is
126 --
127 v_validation_check varchar2(1);
128 --
129 begin
130 --
131 v_validation_check := 'Y';
132 --
133 --
134 if g_debug then
135 hr_utility.set_location('hr_elements.chk_element_type', 1);
136 end if;
137 -- Check element name
138 hr_elements.chk_element_name(p_element_name,
139 p_element_type_id,
140 p_val_start_date,
141 p_val_end_date,
142 p_business_group_id,
143 p_legislation_code);
144 --
145 -- Make sure that the reporting name is unique when it is specified
146 --
147 -- if p_reporting_name is not null then
148 -- hr_elements.chk_reporting_name(p_reporting_name,
149 -- p_element_type_id,
150 -- p_val_start_date,
151 -- p_val_end_date,
152 -- p_business_group_id,
153 -- p_legislation_code);
154 -- end if;
155 --
156 --
157 -- For a nonrecurring element the Standard Flag and the Secondary Entry Flag
158 -- cannot be set to 'Y'
159 if p_recurring_flag = 'N' then
160 --
161 if p_standard_flag = 'Y' then
162 --
163 hr_utility.set_message(801,'PAY_6140_ELEMENT_NO_STANDARD');
164 hr_utility.raise_error;
165 --
166 -- **** 30.49 ***** Column removed from table.
167 -- elsif p_supplemental_run_flag = 'Y' then
168 --
169 -- hr_utility.set_message(801,'PAY_6141_ELEMENT_NO_SUP_RUN');
170 -- hr_utility.raise_error;
171 --
172 elsif p_scndry_ent_allwd_flag = 'Y' then
173 --
174 hr_utility.set_message(801,'PAY_6142_ELEMENT_NO_ADD_ENTRY');
175 hr_utility.raise_error;
176 --
177 end if;
178 --
179 end if;
180 -- For recurring elements the indirect results flag and the adjustment
181 -- only flag must be 'N'
182 --
183 If p_recurring_flag = 'R' and p_indirect_only_flag = 'Y' then
184 --
185 hr_utility.set_message(801,'PAY_6707_ELEMENT_NO_REC_IND');
186 hr_utility.raise_error;
187 --
188 elsif p_recurring_flag = 'R' and p_adjustment_only_flag = 'Y' then
189 --
190 hr_utility.set_message(801,'PAY_6712_ELEMENT_NO_REC_ADJ');
191 hr_utility.raise_error;
192 --
193 end if;
194 --
195 -- For a personnel element the Indirect Only Flag and the Adjustment Only
196 -- Flag cannot be set to 'Y'.
197 if p_process_in_run_flag = 'N' then
198 --
199 if p_indirect_only_flag = 'Y' then
200 --
201 hr_utility.set_message(801,'PAY_6143_ELEMENT_NO_INDIRECT');
202 hr_utility.raise_error;
203 --
204 elsif p_adjustment_only_flag = 'Y' then
205 --
206 hr_utility.set_message(801,'PAY_6144_ELEMENT_NO_ADJUST');
207 hr_utility.raise_error;
208 --
209 end if;
210 --
211 end if;
212 --
213 -- If the elements classification is of a payments type then the
214 -- output currency of the element must match that of the business group
215 -- if a payments type is specified then both currencies must be populated
216 -- For non payments types both currencies can be null but not just one of
217 -- them
218 if (p_classification_type = 'N') then
219 if (p_bus_grp_currency_code <> p_output_currency_code) then
220 --
221 hr_utility.set_message(801,'PAY_6145_ELEMENT_OUTPUT_CURR');
222 hr_utility.raise_error;
223 --
224 elsif (p_input_currency_code is null) then
225 --
226 hr_utility.set_message(801,'PAY_6585_ELEMENT_CURRENCY_MAN');
227 hr_utility.raise_error;
228 --
229 end if;
230 else -- if the classification is a non payments type
231 if (p_input_currency_code is null and p_output_currency_code is not null)
232 or (p_output_currency_code is null and p_input_Currency_Code is not null)
233 then
234 --
235 hr_utility.set_message(801,'PAY_6585_ELEMENT_CURRENCY_MAN');
236 hr_utility.raise_error;
237 --
238 end if;
239 end if;
240 --
241 -- If the adjustment only flag is set to 'Y' then the multiply value flag
242 -- must be 'N'.
243 if p_adjustment_only_flag = 'Y' and p_multiply_value_flag = 'Y' then
244 --
245 hr_utility.set_message(801,'PAY_6904_ELEMENT_NO_AD_AND_MUL');
246 hr_utility.raise_error;
247 --
248 end if;
249 --
250 end chk_element_type;
251 --
252 /*
253 NAME
254 chk_upd_element_type
255 DESCRIPTION
256 Checks that the attributes of element type are allowed to be updated.
257 NOTES
258 Does not test for attributes which cannot be updated.
259 These are element_name and classification id.
260 */
261 --
262 PROCEDURE chk_upd_element_type(p_update_mode in varchar2,
263 p_val_start_date in date,
264 p_val_end_date in date,
265 p_element_type_id in number,
266 p_business_group_id in number,
267 p_old_name in varchar2,
268 p_name in varchar2,
269 p_old_process_in_run_flag in varchar2,
270 p_process_in_run_flag in varchar2,
271 p_old_input_currency in varchar2,
272 p_input_currency in varchar2,
273 p_old_output_currency in varchar2,
274 p_output_currency in varchar2,
275 p_old_standard_link_flag in varchar2,
276 p_standard_link_flag in varchar2,
277 p_old_adjustment_only_flag in varchar2,
278 p_adjustment_only_flag in varchar2,
279 p_old_indirect_only_flag in varchar2,
280 p_indirect_only_flag in varchar2,
281 p_old_scndry_ent_allwd_flag in varchar2,
282 p_scndry_ent_allwd_flag in varchar2,
283 p_old_post_termination_rule in varchar2,
284 p_post_termination_rule in varchar2,
285 p_old_processing_priority in number,
286 p_processing_priority in number) is
287 --
288 v_validation_check varchar2(1) := 'Y';
289 l_no_process_update varchar2(1) := 'N';
290 --
291 begin
292 g_debug := hr_utility.debug_enabled;
293 --
294 -- Classification, Adjustment only flag, Indirect only flag,
295 -- Secondary entries allowed flag and Post termination rule cannot
296 -- be changed if there are any element links for the element.
297 if (p_old_standard_link_flag <> p_standard_link_flag or
298 p_old_adjustment_only_flag <> p_adjustment_only_flag or
299 p_old_indirect_only_flag <> p_indirect_only_flag or
300 p_old_scndry_ent_allwd_flag <> p_scndry_ent_allwd_flag or
301 p_old_post_termination_rule <> p_post_termination_rule or
302 p_old_process_in_run_flag <> p_process_in_run_flag) then
303 --
304 -- Check to see if any element links exist over the validation period.
305 begin
306 --
307 select 'N'
308 into v_validation_check
309 from sys.dual
310 where exists (select 1
311 from pay_element_links_f el
312 where el.element_type_id = p_element_type_id
313 and el.effective_start_date <= p_val_end_date
314 and el.effective_end_date >= p_val_start_date);
315 --
316 if g_debug then
317 hr_utility.set_location('hr_elements.chk_upd_element_type', 1);
318 end if;
319 --
320 exception
321 when NO_DATA_FOUND then NULL;
322 end;
323 --
324 if v_validation_check = 'N' then
325 --
326 hr_utility.set_message(801,'PAY_6147_ELEMENT_LINK_UPDATE');
327 hr_utility.raise_error;
328 --
329 end if;
330 --
331 -- The these fields can only be corrected and only if there is
332 -- only one record for the element type.
333 --
334 if (p_update_mode <> 'CORRECTION') then
335 hr_utility.set_message(801,'PAY_6460_ELEMENT_NO_PROC_CORR');
336 hr_utility.raise_error;
337 else
338 -- We need to check to see if the correction will last for the lifetime
339 -- of the element type
340 --
341 begin
342 --
343 select 'Y'
344 into l_no_process_update
345 from sys.dual
346 where p_val_start_date =
347 (select min(effective_start_date)
348 from pay_element_types_f
349 where element_type_id = p_element_type_id)
350 and p_val_end_date =
351 (select max(effective_end_date)
352 from pay_element_types_f
353 where element_type_id = p_element_type_id);
354 --
355 exception
356 when NO_DATA_FOUND then NULL;
357 end;
358 --
359 if (l_no_process_update = 'N') then
360 hr_utility.set_message(801,'PAY_6460_ELEMENT_NO_PROC_CORR');
361 hr_utility.raise_error;
362 end if;
363 --
364 end if;
365 --
366 end if;
367 --
368 --
369 -- The name can only be corrected and only if there is
370 -- only one record for the element type.
371 --
372 if (p_old_name <> p_name ) then
373 --
374 -- The name can only be updated if the record is a user type record
375 -- This means that the legislation code is entered and the business group
376 -- id is null.
377 --
378 if (p_business_group_id is null) then
379 --
380 hr_utility.set_message(801,'PAY_6624_ELEMENT_NO_NAME_UPD');
381 hr_utility.raise_error;
382 --
383 end if;
384 --
385 if (p_update_mode <> 'CORRECTION') then
386 hr_utility.set_message(801,'PAY_6727_ELEMENT_NO_UPD_NAME');
387 hr_utility.raise_error;
388 else
389 -- We need to check to see if the correction will last for the lifetime
390 -- of the element type
391 --
392 begin
393 --
394 select 'Y'
395 into l_no_process_update
396 from sys.dual
397 where p_val_start_date =
398 (select min(effective_start_date)
399 from pay_element_types_f
400 where element_type_id = p_element_type_id)
401 and p_val_end_date =
402 (select max(effective_end_date)
403 from pay_element_types_f
404 where element_type_id = p_element_type_id);
405 --
406 exception
407 when NO_DATA_FOUND then NULL;
408 end;
409 --
410 if (l_no_process_update = 'N') then
411 hr_utility.set_message(801,'PAY_6727_ELEMENT_NO_UPD_NAME');
412 hr_utility.raise_error;
413 end if;
414 --
415 end if;
416 end if;
417 --
418 -- Checks to see if change in processing priority will result in a
419 -- formula result rule with an input value that has a higher priority
420 -- than the element that feeds it. */
421 if p_old_processing_priority <> p_processing_priority and
422 hr_elements.element_priority_ok(
423 p_element_type_id,
424 p_processing_priority,
425 p_val_start_date,
426 p_val_end_date) = FALSE then
427 --
428 hr_utility.set_message(801,'PAY_6149_ELEMENT_PRIORITY_UPD');
429 hr_utility.raise_error;
430 --
431 end if;
432 --
433 -- Indirect only, process in run and termination processing rule can
434 -- only be updated if there are no run results for the element and
435 -- There exist no formula result rules where this element is the subject
436 -- of indirect results.
437 --
438 if (p_old_indirect_only_flag <> p_indirect_only_flag or
439 p_old_post_termination_rule <> p_post_termination_rule or
440 p_old_process_in_run_flag <> p_process_in_run_flag) then
441
442 begin
443 --
444 select 'N'
445 into v_validation_check
446 from sys.dual
447 where exists
448 (select 1
449 from pay_formula_result_rules_f frr,
450 pay_input_values_f iv
451 where p_element_type_id = iv.element_type_id
452 and iv.input_value_id = frr.input_value_id
453 and frr.effective_start_date <= p_val_end_date
454 and frr.effective_end_date >= p_val_start_date);
455 --
456 exception
457 when NO_DATA_FOUND then null;
458 end;
459 --
460 if v_validation_check = 'N' then
461 --
462 hr_utility.set_message(801,'PAY_6912_ELEMENT_NO_FRR_UPD');
463 hr_utility.raise_error;
464 --
465 end if;
466 --
467 begin
468 --
469 select 'N'
470 into v_validation_check
471 from sys.dual
472 where exists
473 (select 1
474 from pay_run_results rr,
475 pay_assignment_actions aa,
476 pay_payroll_actions pa
477 where p_element_type_id = rr.element_type_id
478 and aa.assignment_action_id = rr.assignment_action_id
479 and aa.payroll_action_id = pa.payroll_action_id
480 and pa.effective_date between
481 p_val_start_date and p_val_end_date);
482 --
483 exception
484 when NO_DATA_FOUND then null;
485 end;
486 --
487 if v_validation_check = 'N' then
488 --
489 hr_utility.set_message(801,'PAY_6909_ELEMENT_NO_UPD_RR');
490 hr_utility.raise_error;
491 --
492 end if;
493 --
494 end if;
495 --
496 end chk_upd_element_type;
497 --
498 /*
499 NAME
500 element_priority_ok
501 DESCRIPTION
502 should be called on any sitation where the processing priority of the element
503 can change. This is on update and on next change delete.
504 */
505 --
506 FUNCTION element_priority_ok(p_element_type_id number,
507 p_processing_priority number,
508 p_val_start_date date,
509 p_val_end_date date)
510 return boolean is
511 --
512 v_validation_check varchar2(1) := 'Y';
513 --
514 begin
515 g_debug := hr_utility.debug_enabled;
516 --
517 -- Check from status processing rule end
518 begin
519 --
520 select 'N'
521 into v_validation_check
522 from sys.dual
523 where exists (select 1
524 from pay_status_processing_rules_f spr,
525 pay_formula_result_rules_f fr,
526 pay_input_values_f iv,
527 pay_element_types_f et
528 where spr.element_type_id = p_element_type_id
529 and fr.result_rule_type = 'I'
530 and spr.status_processing_rule_id =
531 fr.status_processing_rule_id
532 and fr.input_value_id = iv.input_value_id
533 and iv.element_type_id = et.element_type_id
534 and et.processing_priority <= p_processing_priority
535 and spr.effective_start_date <= p_val_end_date
536 and spr.effective_end_date >= p_val_start_date
537 and fr.effective_start_date <= p_val_end_date
538 and fr.effective_end_date >= p_val_start_date);
539 --
540 if g_debug then
541 hr_utility.set_location('hr_elements.chk_upd_element_type', 3);
542 end if;
543 --
544 exception
545 when NO_DATA_FOUND then NULL;
546 end;
547 --
548 -- Do not bother with second part of check if first has already failed
549 if v_validation_check = 'N' then
550 --
551 -- Checking from formula result end
552 begin
553 --
554 select 'N'
555 into v_validation_check
556 from sys.dual
557 where exists(select 1
558 from pay_status_processing_rules_f spr,
559 pay_formula_result_rules_f fr,
560 pay_input_values_f iv,
561 pay_element_types_f et
562 where fr.input_value_id = iv.input_value_id
563 and fr.result_rule_type = 'I'
564 and iv.element_type_id = p_element_type_id
565 and fr.status_processing_rule_id =
566 spr.status_processing_rule_id
567 and spr.element_type_id = et.element_type_id
568 and et.processing_priority >= p_processing_priority
569 and fr.effective_end_date >= p_val_start_date
570 and fr.effective_start_date <= p_val_end_date
571 and spr.effective_start_date <= p_val_end_date
572 and spr.effective_end_date >= p_val_start_date);
573 --
574 if g_debug then
575 hr_utility.set_location('hr_elements.chk_upd_element_type', 4);
576 end if;
577 --
578 exception
579 when OTHERS then NULL;
580 end;
581 --
582 end if;
583 --
584 return (v_validation_check = 'Y');
585 --
586 end element_priority_ok;
587 --
588 /*
589 NAME
590 chk_del_element_type
591 DESCRIPTION
592 Checks that the element can be deleted. This is either complete delete or
593 Date effective delete.
594 NOTES
595 This procedure disallows delete for any element with element links.
596 */
597 --
598 PROCEDURE chk_del_element_type(p_mode in varchar2,
599 p_element_type_id in number,
600 p_processing_priority in number,
601 p_session_date in date,
602 p_val_start_date in date,
603 p_val_end_date in date) is
604 --
605 l_processing_priority number;
606 v_validation_check varchar2(1);
607 v_run_results_exist varchar2(1) := 'N';
608 v_next_record_found varchar2(1) := 'N';
609 v_element_rules_exist varchar2(1) := 'N';
610 --
611 -- Cursor to select all input values for the element type during the validation
612 -- period
613 --
614 CURSOR c_find_input_values(p_element_type_id number,
615 p_val_start_date date,
616 p_val_end_date date) is
617 select input_value_id
618 from pay_input_values_f
619 where p_element_type_id = element_type_id
620 And effective_end_date >= p_val_start_date
621 and effective_start_date <= p_val_end_date;
622 --
623 begin
624 g_debug := hr_utility.debug_enabled;
625 --
626 v_validation_check := 'Y';
627 --
628 if g_debug then
629 hr_utility.set_location('hr_elements.chk_del_element_type', 1);
630 end if;
631 --
632 -- Check to see if any element links exist over the validation period
633 begin
634 --
635 select 'N'
636 into v_validation_check
637 from sys.dual
638 where exists (select 1
639 from pay_element_links_f el
640 where el.element_type_id = p_element_type_id
641 and el.effective_end_date >= p_val_start_date
642 and el.effective_start_date <= p_val_end_date);
643 exception
644 when NO_DATA_FOUND then NULL;
645 end;
646 --
647 if v_validation_check = 'N' then
648 --
649 hr_utility.set_message(801,'PAY_6155_ELEMENT_NO_DEL_LINK');
650 hr_utility.raise_error;
651 --
652 end if;
653 --
654 if g_debug then
655 hr_utility.set_location('hr_elements.chk_del_element_type', 2);
656 end if;
657 --
658 -- We need to check the input values. Input values can be deleted but not
659 -- if any of the conditions regarding input value deletion are broached
660 for iv_rec in c_find_input_values(p_element_type_id,
661 p_val_start_date,
662 p_val_end_date) loop
663 --
664 if g_debug then
665 hr_utility.trace(to_char(iv_rec.input_value_id));
666 end if;
667 hr_input_values.chk_del_input_values(p_mode,
668 p_val_start_date,
669 p_val_end_date,
670 iv_rec.input_value_id);
671 end loop;
672 --
673 -- We cannot delete any element types if there are run results for them.
674 -- The effective date of run results is found from the payroll actions table.
675 --
676 if g_debug then
677 hr_utility.set_location('hr_elements.chk_del_element_type', 3);
678 end if;
679 --
680 begin
681 --
682 select 'Y'
683 into v_run_results_exist
684 from sys.dual
685 where exists
686 (select 1
687 from pay_run_results rr,
688 pay_assignment_actions aa,
689 pay_payroll_actions pa
690 where p_element_type_id = rr.element_type_id
691 and aa.assignment_action_id = rr.assignment_action_id
692 and pa.payroll_action_id = aa.payroll_action_id
693 and pa.effective_date between
694 p_val_start_date and p_val_end_date);
695 --
696 exception
697 when NO_DATA_FOUND then NULL;
698 end;
699 --
700 if v_run_results_exist = 'Y' then
701 --
702 hr_utility.set_message(801,'PAY_6242_ELEMENTS_NO_DEL_RR');
703 hr_utility.raise_error;
704 --
705 end if;
706 --
707 -- Check to see if element is being used in an element set. This only need
708 -- to be done if the delete mode is Zap as element type rules are not
709 -- Date effective
710 if p_mode = 'ZAP' then
711 --
712 begin
713 --
714 select 'Y'
715 into v_element_rules_exist
716 from sys.dual
717 where exists
718 (select 1
719 from pay_element_type_rules
720 where element_type_id = p_element_type_id);
721 --
722 exception
723 when NO_DATA_FOUND then null;
724 end;
725 --
726 if v_element_rules_exist = 'Y' then
727 --
728 hr_utility.set_message(801,'PAY_6713_ELEMENT_NO_DEL_RULE');
729 hr_utility.raise_error;
730 --
731 end if;
732 end if;
733 --
734 -- If the delete is a next change delete then we need to check whether any
735 -- change in priority or extension of the element will result in the element
736 -- becoming invalid.
737 --
738 if p_mode = 'DELETE_NEXT_CHANGE' and
739 hr_elements.element_priority_ok(p_element_type_id,
740 p_processing_priority,
741 p_val_start_date,
742 p_val_end_date) = FALSE then
743 --
744 hr_utility.set_message(801,'PAY_6914_ELEMENT_PRI_NCD');
745 hr_utility.raise_error;
746 --
747 end if;
748 --
749 end chk_del_element_type;
750 --
751 /*
752 NAME
753 ins_input_value
754 DESCRIPTION
755 inserts a pay value for an element type and a balance feed for the pay value.
756 This procedure calls balances.ins_balance_feed.
757 NOTES
758 */
759 --
760 PROCEDURE ins_input_value(p_element_type_id in number,
761 p_legislation_code in varchar2,
762 p_business_group_id in number,
763 p_classification_id in number,
764 p_val_start_date in date,
765 p_val_end_date in date,
766 p_startup_mode in varchar2) is
767
768 v_input_value_id number(15);
769 l_pay_value_name varchar2(80);
770 l_business_group_id number(9);
771 c_user_id number;
772 c_login_id number;
773 --
774 l_check_latest_balances boolean;
775 --
776 begin
777 g_debug := hr_utility.debug_enabled;
778 --
779 --
780 if g_debug then
781 hr_utility.set_location('hr_elements.ins_input_value', 1);
782 end if;
783 --
784 -- Obtain sequence number for input value
785 select pay_input_values_s.nextval
786 into v_input_value_id
787 from sys.dual;
788 --
789 --
790 if g_debug then
791 hr_utility.set_location('hr_elements.ins_input_value', 2);
792 end if;
793 --
794 -- Obtain Pay value name from hr_lookups
795 l_pay_value_name := hr_input_values.get_pay_value_name
796 (p_legislation_code);
797 --
798 c_user_id := fnd_global.user_id;
799 c_login_id := fnd_global.login_id;
800 --
801 if g_debug then
802 hr_utility.set_location('hr_elements.ins_input_value', 3);
803 end if;
804 --
805 -- Create PAY_VALUE for element type.
806 insert into pay_input_values_f
807 (input_value_id,
808 effective_start_date,
809 effective_end_date,
810 element_type_id,
811 display_sequence,
812 generate_db_items_flag,
813 hot_default_flag,
814 mandatory_flag,
815 name,
816 uom,
817 last_update_date,
818 last_updated_by,
819 last_update_login,
820 created_by,
821 creation_date,
822 business_group_id,
823 legislation_code,
824 legislation_subgroup)
825 select
826 v_input_value_id,
827 p_val_start_date,
828 p_val_end_date,
829 et.element_type_id,
830 1,
831 'Y',
832 'N',
833 'N',
834 'Pay Value',
835 'M',
836 et.last_update_date,
837 et.last_updated_by,
838 et.last_update_login,
839 et.created_by,
840 et.creation_date,
841 et.business_group_id,
842 et.legislation_code,
843 et.legislation_subgroup
844 from pay_element_types_f et
845 where et.element_type_id = p_element_type_id
846 and et.effective_start_date = p_val_start_date;
847 --
848 if SQL%NOTFOUND then
849 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
850 hr_utility.set_message_token('PROCEDURE','ins_input_value');
851 hr_utility.set_message_token('STEP','1');
852 hr_utility.raise_error;
853 end if;
854 --
855 insert into PAY_INPUT_VALUES_F_TL (
856 INPUT_VALUE_ID,
857 NAME,
858 LAST_UPDATE_DATE,
859 LAST_UPDATED_BY,
860 LAST_UPDATE_LOGIN,
861 CREATED_BY,
862 CREATION_DATE,
863 LANGUAGE,
864 SOURCE_LANG
865 ) select
866 v_input_value_id,
867 l_pay_value_name,
868 sysdate,
869 c_user_id,
870 c_user_id,
871 c_login_id,
872 sysdate,
873 L.LANGUAGE_CODE,
874 userenv('LANG')
875 from FND_LANGUAGES L
876 where L.INSTALLED_FLAG in ('I', 'B')
877 and not exists
878 (select NULL
879 from PAY_INPUT_VALUES_F_TL T
880 where T.INPUT_VALUE_ID = v_input_value_id
881 and T.LANGUAGE = L.LANGUAGE_CODE);
882 --
883 if g_debug then
884 hr_utility.set_location('hr_elements.ins_input_value', 4);
885 end if;
886 --
887 -- if this record is in startup or generic mode then insert some ownerships
888 -- for it
889 if p_startup_mode <> 'USER' then
890 --
891 hr_elements.ins_ownerships
892 ('INPUT_VALUE_ID',
893 v_input_value_id,
894 p_element_type_id);
895 --
896 end if;
897 --
898 --
899 -- Set global to avoid looking for invalidated latest balances
900 -- ie can't be any as no run result values for this new input value
901 --
902 l_check_latest_balances := HRASSACT.CHECK_LATEST_BALANCES;
903 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
904 --
905 -- Create any balance feeds that may be required ie. for any balances which
906 -- are fed by the same classification as the element.
907 hr_balances.ins_balance_feed('INS_PAY_PAY_VALUE',
908 v_input_value_id,
909 NULL,
910 p_classification_id,
911 NULL,
912 NULL,
913 NULL,
914 NULL,
915 p_val_start_date,
916 p_business_group_id,
917 p_legislation_code,
918 p_startup_mode);
919 --
920 -- Reset global
921 --
922 HRASSACT.CHECK_LATEST_BALANCES := l_check_latest_balances;
923 --
924 -- Create any balance feeds that may be required ie. for any balances which
925 --
926 -- The insertion of rows into application ownerships for startup data
927 -- is handled by an insert trigger on pay_input_values_f
928 --
929 -- Create database items
930 --
931 hrdyndbi.create_input_value_dict(v_input_value_id,
932 p_val_start_date);
933 --
934 end ins_input_value;
935 --
936 --
937 /*
938 NAME
939 ins_sub_classification_rules
940 DESCRIPTION
941 This procedure will create a sub_classification_rule for each
942 sub_classification that has the create_by_default_flag set to 'Y',,
943 It will then call hr_balances.ins_balance_feed to create the balance feeds.
944 */
945 --
946 PROCEDURE ins_sub_classification_rules(
947 p_element_type_id in number,
948 p_legislation_code in varchar2,
949 p_business_group_id in number,
950 p_classification_id in number,
951 p_val_start_date in date,
952 p_val_end_date in date,
953 p_startup_mode in varchar2) is
954 --
955 -- Cursor to get classifications by business_group
956 --
957 CURSOR get_sub_classifications(p_classification_id number) IS
958 select classification_id
959 from pay_element_classifications
960 where parent_classification_id = p_classification_id
961 and nvl(business_group_id, nvl(p_business_group_id, 0)) = nvl(p_business_group_id, 0)
962 and nvl(legislation_code, nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ')
963 and create_by_default_flag = 'Y'
964 for update;
965 --
966 -- Cursor to ensure identical sub classification rule does not already exist
967 --
968 CURSOR csr_chk_scr_exists(p_start_date date,
969 p_end_date date,
970 p_element_type_id number,
971 p_classification number,
972 p_business_group_id number,
973 p_legislation_code varchar2) IS
974 select 'X'
975 from pay_sub_classification_rules_f
976 where effective_start_date = p_start_date
977 and effective_end_date = p_end_date
978 and element_type_id = p_element_type_id
979 and classification_id = p_classification
980 and nvl(business_group_id, nvl(p_business_group_id, 0)) = nvl(p_business_group_id, 0)
981 and nvl(legislation_code, nvl(p_legislation_code, ' ')) = nvl(p_legislation_code, ' ');
982 --
983 l_sub_classification_rule_id number;
984 l_dummy varchar2(1);
985 l_legislation_code varchar2(30) := null;
986 --
987 begin
988 g_debug := hr_utility.debug_enabled;
989 --
990 if g_debug then
991 hr_utility.set_location('hr_elements.ins_sub_class_rule', 1);
992 end if;
993 --
994 for subcr_rec in get_sub_classifications(p_classification_id) loop
995 --
996 open csr_chk_scr_exists(p_val_start_date
997 ,p_val_end_date
998 ,p_element_type_id
999 ,subcr_rec.classification_id
1000 ,p_business_group_id
1001 ,p_legislation_code);
1002 fetch csr_chk_scr_exists into l_dummy;
1003 if csr_chk_scr_exists%notfound then
1004 --
1005 -- Close cursor and continue with insert as no duplicate row exists
1006 --
1007 close csr_chk_scr_exists;
1008 --
1009 -- Do not insert legislation code for user rows.
1010 --
1011 if p_business_group_id is null then
1012 l_legislation_code := p_legislation_code;
1013 end if;
1014 --
1015 select pay_sub_classification_rules_s.nextval
1016 into l_sub_classification_rule_id
1017 from dual;
1018 --
1019 -- Insert sub_classification rule.
1020 --
1021 insert into pay_sub_classification_rules_f
1022 (SUB_CLASSIFICATION_RULE_ID
1023 ,EFFECTIVE_START_DATE
1024 ,EFFECTIVE_END_DATE
1025 ,ELEMENT_TYPE_ID
1026 ,CLASSIFICATION_ID
1027 ,BUSINESS_GROUP_ID
1028 ,LEGISLATION_CODE)
1029 values
1030 (l_sub_classification_rule_id
1031 ,p_val_start_date
1032 ,p_val_end_date
1033 ,p_element_type_id
1034 ,subcr_rec.classification_id
1035 ,p_business_group_id
1036 ,l_legislation_code);
1037 --
1038 if SQL%NOTFOUND then
1039 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1040 hr_utility.set_message_token('PROCEDURE','ins_sub_classification_rule');
1041 hr_utility.set_message_token('STEP','1');
1042 hr_utility.raise_error;
1043 end if;
1044 --
1045 -- if this record is in startup or generic mode then insert some ownerships
1046 -- for it
1047 if p_startup_mode <> 'USER' then
1048 --
1049 hr_elements.ins_ownerships
1050 ('SUB_CLASSIFICATION_RULE_ID'
1051 ,l_sub_classification_rule_id
1052 ,p_element_type_id);
1053 --
1054 end if;
1055 --
1056 -- Create balance feeds for this rule
1057 --
1058 hr_balances.ins_balance_feed
1059 ('INS_SUB_CLASS_RULE'
1060 ,NULL
1061 ,NULL
1062 ,NULL
1063 ,NULL
1064 ,l_sub_classification_rule_id
1065 ,NULL
1066 ,NULL
1067 ,p_val_start_date
1068 ,p_business_group_id
1069 ,p_legislation_code
1070 ,p_startup_mode);
1071 --
1072 else
1073 close csr_chk_scr_exists;
1074 end if;
1075 --
1076 end loop;
1077 --
1078 end ins_sub_classification_rules;
1079 --
1080 --
1081 /*
1082 NAME
1083 ins_3p_element_type
1084 DESCRIPTION
1085 Based on the process in run flag this will call the insert input value
1086 and the insert status processing rules procedures.
1087 */
1088 --
1089 PROCEDURE ins_3p_element_type(p_element_type_id in number,
1090 p_process_in_run_flag in varchar2,
1091 p_legislation_code in varchar2,
1092 p_business_group_id in number,
1093 p_classification_id in number,
1094 p_non_payments_flag in varchar,
1095 p_val_start_date in date,
1096 p_val_end_date in date,
1097 p_startup_mode in varchar2) is
1098 --
1099 begin
1100 g_debug := hr_utility.debug_enabled;
1101 --
1102 -- Only create the default status processing rule and PAY_VALUE if the
1103 -- element is to be used by the payroll run.
1104 if p_process_in_run_flag = 'Y' and p_non_payments_flag = 'N' then
1105 --
1106 -- Create PAY_VALUE
1107 hr_elements.ins_input_value(p_element_type_id,
1108 p_legislation_code,
1109 p_business_group_id,
1110 p_classification_id,
1111 p_val_start_date,
1112 p_val_end_date,
1113 p_startup_mode);
1114 --
1115 end if;
1116 -- Create sub_classification_rules
1117 hr_elements.ins_sub_classification_rules(
1118 p_element_type_id,
1119 p_legislation_code,
1120 p_business_group_id,
1121 p_classification_id,
1122 p_val_start_date,
1123 p_val_end_date,
1124 p_startup_mode);
1125 --
1126 -- Insert database item
1127 hrdyndbi.create_element_type_dict(
1128 p_element_type_id,
1129 p_val_start_date);
1130 --
1131 end ins_3p_element_type;
1132 --
1133 --
1134 /*
1135 NAME
1136 del_formula_result_rules
1137 DESCRIPTION
1138 This procedure deletes any formula result rules in existence for the element.
1139 It is only called from del_status_processing_rules.
1140 */
1141 --
1142 PROCEDURE del_formula_result_rules(
1143 p_status_processing_rule_id in number,
1144 p_delete_mode in varchar2,
1145 p_val_session_date in date,
1146 p_val_start_date in date,
1147 p_val_end_date in date,
1148 p_startup_mode in varchar2) is
1149 --
1150 begin
1151 g_debug := hr_utility.debug_enabled;
1152 --
1153 if g_debug then
1154 hr_utility.set_location('hr_elements.del_formula_result_rules', 1);
1155 end if;
1156 --
1157 if p_delete_mode = 'ZAP' then
1158 --
1159 if p_startup_mode <> 'USER' then
1160 --
1161 delete from hr_application_ownerships ao
1162 where key_name = 'FORMULA_RESULT_RULE_ID'
1163 and exists
1164 (select 1
1165 from pay_formula_result_rules_f frr
1166 where frr.status_processing_rule_id =
1167 p_status_processing_rule_id
1168 and ao.key_value = to_char(frr.formula_result_rule_id));
1169 --
1170 end if;
1171 --
1172 delete from pay_formula_result_rules_f
1173 where status_processing_rule_id = p_status_processing_rule_id;
1174 --
1175 elsif p_delete_mode = 'DELETE' then
1176 --
1177 if g_debug then
1178 hr_utility.set_location('hr_elements.del_formula_result_rules', 2);
1179 end if;
1180 --
1181 -- delete all future records
1182 delete from pay_formula_result_rules_f
1183 where status_processing_rule_id = p_status_processing_rule_id
1184 and effective_start_date > p_val_session_date;
1185 --
1186 -- update current records so that the end date is the session date
1187 update pay_formula_result_rules_f
1188 set effective_end_date = p_val_session_date
1189 where status_processing_rule_id = p_status_processing_rule_id
1190 and p_val_session_date between
1191 effective_start_date and effective_end_date;
1192 --
1193 end if;
1194 -- DELETE NEXT CHANGE has no effect
1195 -- FUTURE CHANGE DELETE is not allowed
1196 --
1197 end del_formula_result_rules;
1198 --
1199 /*
1200 NAME
1201 del_status_processing_ruleS
1202 DESCRIPTION
1203 This procedure deletes any status processing rules for this element and
1204 calls a function to delete any formula result rules.
1205 NOTES
1206 Element types cannot be subject to a future change delete. They can be subject
1207 to a next change delete but, in the case of status processing rules, this
1208 does not cause the records to 'open up' if we are on the final record. A
1209 warning will appear in the form telling the users that this is the case.
1210 */
1211 PROCEDURE del_status_processing_rules(
1212 p_element_type_id in number,
1213 p_delete_mode in varchar2,
1214 p_val_session_date in date,
1215 p_val_start_date in date,
1216 p_val_end_date in date,
1217 p_startup_mode in varchar2) is
1218 --
1219 -- Cursor select all valid sprs for the element and locks these rows
1220 --
1221 CURSOR get_sprs (p_element_type_id number,
1222 p_val_start_date date,
1223 p_val_end_date date) is
1224 select status_processing_rule_id,
1225 effective_start_date,
1226 effective_end_date
1227 from pay_status_processing_rules_f
1228 where p_element_type_id = element_type_id
1229 and effective_start_date <= p_val_end_date
1230 and effective_end_date >= p_val_start_date
1231 for update;
1232 --
1233 begin
1234 g_debug := hr_utility.debug_enabled;
1235 --
1236 if g_debug then
1237 hr_utility.set_location('hr_elements.del_status_processing_rules', 1);
1238 end if;
1239 --
1240 for spr_rec in get_sprs(p_element_type_id,
1241 p_val_start_date,
1242 p_val_end_date) loop
1243 --
1244 del_formula_result_rules(
1245 spr_rec.status_processing_rule_id,
1246 p_delete_mode,
1247 p_val_session_date,
1248 spr_rec.effective_start_date,
1249 spr_rec.effective_end_date,
1250 p_startup_mode);
1251 end loop;
1252 --
1253 if p_delete_mode = 'ZAP' then
1254 --
1255 if p_startup_mode <> 'USER' then
1256 --
1257 delete from hr_application_ownerships ao
1258 where ao.key_name = 'STATUS_PROCESSING_RULE_ID'
1259 and exists
1260 (select 1
1261 from pay_status_processing_rules_f spr
1262 where spr.element_type_id = p_element_type_id
1263 and ao.key_value =
1264 to_char(spr.status_processing_rule_id));
1265 end if;
1266 --
1267 delete from pay_status_processing_rules_f
1268 where element_type_id = p_element_type_id;
1269 --
1270 elsif p_delete_mode = 'DELETE' then
1271 --
1272 if g_debug then
1273 hr_utility.set_location('hr_elements.del_status_processing_rules', 2);
1274 end if;
1275 --
1276 -- delete all future records
1277 delete from pay_status_processing_rules_f
1278 where element_type_id = p_element_type_id
1279 and effective_start_date > p_val_session_date;
1280 --
1281 -- update current records so that the end date is the session date
1282 update pay_status_processing_rules_f
1283 set effective_end_date = p_val_session_date
1284 where element_type_id = p_element_type_id
1285 and p_val_session_date between
1286 effective_start_date and effective_end_date;
1287 --
1288 end if;
1289 -- DELETE NEXT CHANGE has no effect
1290 -- FUTURE CHANGE DELETE is not allowed
1291 --
1292 end del_status_processing_rules;
1293 /*
1294 NAME
1295 del_sub_classification_rules
1296 DESCRIPTION
1297 This procedure deletes any existing sub_classification_rules and any
1298 related balance feeds.
1299 NOTES
1300 Element types cannot be subject to a future change delete. They can, however,
1301 be subject to a next change delete and this is handled in the code. This
1302 procedure relies on the hr_input_values.del_3p_input_values being called
1303 in the same commit unit as this will tidy up the balance feeds that may have
1304 been created by the sub_classification rules.
1305 */
1306 --
1307 PROCEDURE del_sub_classification_rules(
1308 p_element_type_id in number,
1309 p_delete_mode in varchar2,
1310 p_val_session_date in date,
1311 p_val_start_date in date,
1312 p_val_end_date in date,
1313 p_startup_mode in varchar2) is
1314 --
1315 v_end_of_time date;
1316 begin
1317 g_debug := hr_utility.debug_enabled;
1318 --
1319 if g_debug then
1320 hr_utility.set_location('hr_elements.del_sub_classification_rules', 1);
1321 end if;
1322 --
1323 -- Delete the sub_classification_rules. The balance_feeds will have already
1324 -- been deleted as part of the delete input values procedure
1325 --
1326 if p_delete_mode = 'ZAP' then
1327 --
1328 if p_startup_mode <> 'USER' then
1329 --
1330 delete from hr_application_ownerships ao
1331 where ao.key_name = 'SUB_CLASSIFICATION_RULE_ID'
1332 and exists
1333 (select 1
1334 from pay_sub_classification_rules_f scr
1335 where scr.element_type_id = p_element_type_id
1336 and ao.key_value =
1337 to_char(scr.sub_classification_rule_id));
1338 --
1339 end if;
1340 --
1341 delete from pay_sub_classification_rules_f
1342 where element_type_id = p_element_type_id;
1343 --
1344 elsif p_delete_mode = 'DELETE' then
1345 --
1346 --
1347 if g_debug then
1348 hr_utility.set_location('hr_elements.del_sub_classification_rules', 2);
1349 end if;
1350 --
1351 -- delete all future records
1352 delete from pay_sub_classification_rules_f
1353 where element_type_id = p_element_type_id
1354 and effective_start_date > p_val_session_date;
1355 --
1356 -- update current records so that the end date is the session date
1357 update pay_sub_classification_rules_f
1358 set effective_end_date = p_val_session_date
1359 where element_type_id = p_element_type_id
1360 and p_val_session_date between
1361 effective_start_date and effective_end_date;
1362 --
1363 -- DELETE_NEXT_CHANGE will not cause the sub_classification rules to extend.
1364 --
1365 end if;
1366 end del_sub_classification_rules;
1367 --
1368 /*
1369 NAME
1370 upd_3p_element_type
1371 DESCRIPTION
1372 This procedure does third party processing necessary on update. Currenctly
1373 this only consists of deleting and recreating the database items
1374 */
1375 PROCEDURE upd_3p_element_type(p_element_type_id in number,
1376 p_val_start_date in date,
1377 p_old_name in varchar2,
1378 p_name in varchar2) is
1379 --
1380 begin
1381 --
1382 if p_old_name <> p_name then
1383 --
1384 hrdyndbi.delete_element_type_dict(p_element_type_id);
1385 --
1386 hrdyndbi.create_element_type_dict(p_element_type_id,
1387 p_val_start_date);
1388 --
1389 end if;
1390 end upd_3p_element_type;
1391 --
1392 /*
1393 NAME
1394 del_3p_element_type
1395 DESCRIPTION
1396 This procedure does the necessary cascade deletes when an element type is
1397 deleted. This affects the following tables: Input values, status processing
1398 rules and formula result rules.
1399 NOTES
1400 Element types cannot be subject to a future change delete. They can, however,
1401 be subject to a next change delete and this is handled in the code.
1402 */
1403 PROCEDURE del_3p_element_type(p_element_type_id in number,
1404 p_delete_mode in varchar2,
1405 p_val_session_date in date,
1406 p_val_start_date in date,
1407 p_val_end_date in date,
1408 p_startup_mode in varchar2) is
1409 --
1410 v_end_of_time date;
1411 l_on_final_record varchar2(1) := 'N';
1412 l_input_value_id number;
1413 --
1414 -- Cursor to determine which input value records need to be included for
1415 -- cascade delete.
1416 cursor c_input_value(p_element_type_id number,
1417 p_val_start_date date,
1418 p_val_end_date date) is
1419 select iv.input_value_id input_value_id,
1420 iv.generate_db_items_flag db_items_flag
1421 from pay_input_values_f iv
1422 where p_element_type_id = iv.element_type_id
1423 and iv.effective_start_date <= p_val_end_date
1424 and iv.effective_end_date >= p_val_start_date
1425 for update;
1426 --
1427 begin
1428 g_debug := hr_utility.debug_enabled;
1429 --
1430 --
1431 if g_debug then
1432 hr_utility.set_location('hr_elements.del_3p_element_type', 1);
1433 end if;
1434 --
1435 -- Select all input values.
1436 -- Perform all 3p deletes for these input values then delete the input values
1437 for iv_rec in c_input_value(p_element_type_id,
1438 p_val_start_date,
1439 p_val_end_date) loop
1440 --
1441 hr_input_values.del_3p_input_values(p_delete_mode,
1442 iv_rec.input_value_id,
1443 iv_rec.db_items_flag,
1444 p_val_end_date,
1445 p_val_session_date,
1446 p_startup_mode);
1447 --
1448 end loop;
1449 --
1450 if g_debug then
1451 hr_utility.set_location('hr_elements.del_3p_element_type', 2);
1452 end if;
1453 --
1454 --
1455 if p_delete_mode = 'ZAP' then
1456 --
1457 if p_startup_mode <> 'USER' then
1458 --
1459 delete from hr_application_ownerships ao
1460 where ao.key_name = 'INPUT_VALUE_ID'
1461 and exists
1462 (select 1
1463 from pay_input_values_f iv
1464 where iv.element_type_id = p_element_type_id
1465 and ao.key_value = to_char(iv.input_value_id));
1466 --
1467 end if;
1468 --
1469 delete from pay_input_values_f
1470 where element_type_id = p_element_type_id;
1471 --
1472 elsif p_delete_mode = 'DELETE' then
1473 --
1474 --
1475 if g_debug then
1476 hr_utility.set_location('hr_elements.del_3p_element_type', 3);
1477 end if;
1478 --
1479 -- delete all future records
1480 delete from pay_input_values_f
1481 where element_type_id = p_element_type_id
1482 and effective_start_date > p_val_session_date;
1483 --
1484 -- update current records so that the end date is the session date
1485 update pay_input_values_f
1486 set effective_end_date = p_val_session_date
1487 where element_type_id = p_element_type_id
1488 and p_val_session_date between
1489 effective_start_date and effective_end_date;
1490 --
1491 -- DELETE_NEXT_CHANGE will only affect the input value records if we are on
1492 -- The final record of the element type. In this case the final input value
1493 -- records will need to be extended to the end of time.
1494 elsif p_delete_mode = 'DELETE_NEXT_CHANGE' then
1495 --
1496 --
1497 if g_debug then
1498 hr_utility.set_location('hr_elements.del_3p_element_type', 4);
1499 end if;
1500 --
1501 begin
1502 --
1503 select 'Y'
1504 into l_on_final_record
1505 from pay_element_types_f et1
1506 where p_element_type_id = et1.element_type_id
1507 and p_val_session_date between
1508 et1.effective_start_date and et1.effective_end_date
1509 and et1.effective_end_date =
1510 (select max(et2.effective_end_date)
1511 from pay_element_types_f et2
1512 where p_element_type_id = et2.element_type_id);
1513 --
1514 exception
1515 when NO_DATA_FOUND then NULL;
1516 end;
1517 --
1518 if l_on_final_record = 'Y' then
1519 --
1520 --
1521 if g_debug then
1522 hr_utility.set_location('hr_elements.del_3p_element_type', 5);
1523 end if;
1524 --
1525 v_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
1526 --
1527 update pay_input_values_f iv1
1528 set iv1.effective_end_date = v_end_of_time
1529 where (iv1.input_value_id, iv1.effective_end_date) =
1530 (select iv2.input_value_id, max(iv2.effective_end_date)
1531 from pay_input_values_f iv2
1532 where iv2.element_type_id = p_element_type_id
1533 group by iv2.input_value_id);
1534 --
1535 end if;
1536 --
1537 -- No 'FUTURE_CHANGE_DELETE' allowed.
1538 --
1539 end if;
1540 --
1541 hr_elements.del_sub_classification_rules(
1542 p_element_type_id,
1543 p_delete_mode,
1544 p_val_session_date,
1545 p_val_start_date,
1546 p_val_end_date,
1547 p_startup_mode);
1548 --
1549 hr_elements.del_status_processing_rules(
1550 p_element_type_id,
1551 p_delete_mode,
1552 p_val_session_date,
1553 p_val_start_date,
1554 p_val_end_date,
1555 p_startup_mode);
1556 --
1557 if p_delete_mode = 'ZAP' then
1558 --
1559 -- We need to clear down the database items
1560 --
1561 hrdyndbi.delete_element_type_dict(p_element_type_id);
1562 --
1563 end if;
1564 end del_3p_element_type;
1565 --
1566 /*
1567 NAME
1568 ins_ownerships
1569 DESCRIPTION
1570 This procedure will insert product ownerships for any startup or generic
1571 record
1572 */
1573 PROCEDURE ins_ownerships(p_key_name varchar2,
1574 p_key_value number,
1575 p_element_type_id number) is
1576 --
1577 l_session_id number;
1578 --
1579 begin
1580 g_debug := hr_utility.debug_enabled;
1581 --
1582 if g_debug then
1583 hr_utility.set_location('ins_ownerships', 1);
1584 end if;
1585 --
1586 insert into hr_application_ownerships
1587 (key_name,
1588 key_value,
1589 product_name)
1590 select p_key_name,
1591 p_key_value,
1592 ao.product_name
1593 from hr_application_ownerships ao
1594 where ao.key_name = 'ELEMENT_TYPE_ID'
1595 and ao.key_value = p_element_type_id;
1596 --
1597 if SQL%NOTFOUND then
1598 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1599 hr_utility.set_message_token('PROCEDURE', 'ins_ownerships');
1600 hr_utility.set_message_token('STEP', '1');
1601 hr_utility.raise_error;
1602 end if;
1603 --
1604
1605 end ins_ownerships;
1606
1607 PROCEDURE check_element_freq ( p_payroll_id IN NUMBER,
1608 p_bg_id IN NUMBER,
1609 p_pay_action_id IN NUMBER,
1610 p_passed_date IN DATE,
1611 p_ele_type_id IN NUMBER,
1612 p_whole_period_only IN VARCHAR2,
1613 p_skip_element OUT NOCOPY VARCHAR2) IS
1614
1615 v_number_per_fy NUMBER(3);
1616 v_run_number NUMBER(3);
1617 v_freq_rule_exists NUMBER(3);
1618 v_period_end_date DATE;
1619 v_period_start_date DATE;
1620 v_rule_mode pay_legislation_rules.rule_mode%type;
1621 v_rule_date_code VARCHAR2(30);
1622 --
1623 BEGIN
1624 --
1625 -- The default value for p_whole_period_only should be 'D'. However if the some
1626 -- customers wants to use the fixed version then they
1627 -- can use the whole period by setting the
1628 -- "FREQ_RULE_WHOLE_PERIOD".
1629 --
1630 --
1631 g_debug := hr_utility.debug_enabled;
1632 p_skip_element := 'N';
1633 --
1634 -- See if freq rule even comes into play here:
1635 --
1636 if g_debug then
1637 hr_utility.set_location('check_element_freq', 45);
1638 end if;
1639 SELECT COUNT(0)
1640 INTO v_freq_rule_exists
1641 FROM pay_ele_payroll_freq_rules EPF
1642 WHERE element_type_id = p_ele_type_id
1643 AND payroll_id = p_payroll_id
1644 AND business_group_id + 0 = p_bg_id;
1645
1646 IF v_freq_rule_exists = 0 THEN
1647 p_skip_element:='N';
1648 RETURN;
1649 END IF;
1650
1651 SELECT NVL(rule_date_code,'E')
1652 INTO v_rule_date_code
1653 FROM pay_ele_payroll_freq_rules
1654 WHERE element_type_id = p_ele_type_id
1655 AND payroll_id = p_payroll_id;
1656
1657 --
1658 -- If we're here, then maybe freq rule will affect processing...
1659 -- Get payroll period type.number per fiscal year.
1660 --
1661 SELECT end_date, start_date
1662 INTO v_period_end_date,
1663 v_period_start_date
1664 FROM per_time_periods
1665 WHERE p_passed_date BETWEEN start_date AND end_date
1666 AND payroll_id = p_payroll_id;
1667
1668 SELECT TPT.number_per_fiscal_year
1669 INTO v_number_per_fy
1670 FROM per_time_period_types TPT,
1671 pay_payrolls_f PRL
1672 WHERE TPT.period_type = PRL.period_type
1673 AND PRL.business_group_id + 0 = p_bg_id
1674 AND p_passed_date BETWEEN prl.effective_start_date and prl.effective_end_date
1675 AND PRL.payroll_id = p_payroll_id;
1676 --
1677 -- Get period number in Month or Year according to number per fiscal year.
1678 -- ...into v_run_number...
1679 -- What we NEED is the actual PERIOD # w/in Month or Year.
1680 if g_debug then
1681 hr_utility.trace('v_number_per_fy='||to_char(v_number_per_fy));
1682 end if;
1683
1684 IF v_number_per_fy < 12 THEN
1685 if g_debug then
1686 hr_utility.set_location('check_element_freq', 20);
1687 hr_utility.trace('v_period_end_date='||to_char(v_period_end_date,'YYYY/MM/DD'));
1688 hr_utility.trace('v_period_start_date='||to_char(v_period_start_date,'YYYY/MM/DD'));
1689 hr_utility.trace('p_passed_date='||to_char(p_passed_date,'YYYY/MM/DD'));
1690 end if;
1691 --
1692 --Added for fix 9183831
1693 IF v_rule_date_code = 'F' THEN
1694 /*The pay_action_parameter value is mainly used for controlling the effective_date period number calc*/
1695 if p_whole_period_only in ('D','N','R') then
1696 SELECT COUNT(0)
1697 INTO v_run_number
1698 FROM per_time_periods PTP
1699 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1700 BETWEEN TRUNC(v_period_end_date,'YEAR')
1701 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1702 AND PTP.payroll_id = p_payroll_id;
1703 ELSE
1704 /*If the parameter is set we use the period start date of the passed date_paid date*/
1705 SELECT COUNT(0)
1706 INTO v_run_number
1707 FROM per_time_periods PTP
1708 WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1709 BETWEEN TRUNC(v_period_start_date,'YEAR')
1710 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1711 AND PTP.payroll_id = p_payroll_id;
1712 end if;
1713 ELSIF v_rule_date_code = 'E' THEN
1714 /*The period number will be calculated by taking of the last date of the passed date_earned date*/
1715 SELECT COUNT(0)
1716 INTO v_run_number
1717 FROM per_time_periods PTP
1718 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1719 BETWEEN TRUNC(v_period_end_date,'YEAR')
1720 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1721 AND PTP.payroll_id = p_payroll_id;
1722 ELSE
1723 /*If the rule_date_code is set as date_paid we use payment Date, this is done by adding the look_value to
1724 PAY_FRULE_DATES orPAY_US_FRULE_DATES, mostly the value will be 'R' */
1725 SELECT COUNT(0)
1726 INTO v_run_number
1727 FROM per_time_periods PTP
1728 WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1729 BETWEEN TRUNC(p_passed_date,'YEAR')
1730 AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1731 AND PTP.payroll_id = p_payroll_id;
1732 END IF;
1733 /* if v_rule_date_code in ('E','F') then
1734 if p_whole_period_only in ('D','N','R') then
1735 SELECT COUNT(0)
1736 INTO v_run_number
1737 FROM per_time_periods PTP
1738 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1739 BETWEEN TRUNC(v_period_end_date,'YEAR')
1740 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1741 AND PTP.payroll_id = p_payroll_id;
1742 else
1743 SELECT COUNT(0)
1744 INTO v_run_number
1745 FROM per_time_periods PTP
1746 WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1747 BETWEEN TRUNC(v_period_start_date,'YEAR')
1748 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1749 AND PTP.payroll_id = p_payroll_id;
1750 end if;
1751 else
1752 SELECT COUNT(0)
1753 INTO v_run_number
1754 FROM per_time_periods PTP
1755 WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1756 BETWEEN TRUNC(p_passed_date,'YEAR')
1757 AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1758 AND PTP.payroll_id = p_payroll_id;
1759 end if;*/
1760 --
1761 ELSIF v_number_per_fy > 13 THEN
1762 if g_debug then
1763 hr_utility.set_location('check_element_freq', 30);
1764 hr_utility.trace('v_period_end_date='||to_char(v_period_end_date,'YYYY/MM/DD'));
1765 hr_utility.trace('v_period_start_date='||to_char(v_period_start_date,'YYYY/MM/DD'));
1766 hr_utility.trace('p_passed_date='||to_char(p_passed_date,'YYYY/MM/DD'));
1767 end if;
1768
1769 --Added for fix 9183831
1770 IF v_rule_date_code = 'F' THEN
1771 IF p_whole_period_only IN ('D','N','R') THEN
1772 /*The pay_action_parameter value is mainly used for controlling the effective_date period number calc*/
1773 SELECT COUNT(0)
1774 INTO v_run_number
1775 FROM per_time_periods PTP
1776 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1777 BETWEEN TRUNC(v_period_end_date, 'MONTH')
1778 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1779 AND PTP.payroll_id = p_payroll_id;
1780 ELSE
1781 /*If the parameter is set we use the period start date of the passed date_paid date*/
1782 SELECT COUNT(0)
1783 INTO v_run_number
1784 FROM per_time_periods PTP
1785 WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1786 BETWEEN TRUNC(v_period_start_date, 'MONTH')
1787 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1788 AND PTP.payroll_id = p_payroll_id;
1789 END IF;
1790 ELSIF v_rule_date_code = 'E' THEN
1791 /*The period number will be calculated by taking of the last date of the passed date_earned date*/
1792 SELECT COUNT(0)
1793 INTO v_run_number
1794 FROM per_time_periods PTP
1795 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1796 BETWEEN TRUNC(v_period_end_date, 'MONTH')
1797 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1798 AND PTP.payroll_id = p_payroll_id;
1799 ELSE
1800
1801 /*If the rule_date_code is set as date_paid we use payment Date, this is done by adding the look_value to
1802 PAY_FRULE_DATES orPAY_US_FRULE_DATES, mostly the value will be 'R' */
1803 SELECT COUNT(0)
1804 INTO v_run_number
1805 FROM per_time_periods PTP
1806 WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1807 BETWEEN TRUNC(p_passed_date, 'MONTH')
1808 AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1809 AND PTP.payroll_id = p_payroll_id;
1810 END IF;
1811
1812 --
1813 /*if v_rule_date_code in ('E','F') then
1814 if p_whole_period_only in ('D','N','R') then
1815 SELECT COUNT(0)
1816 INTO v_run_number
1817 FROM per_time_periods PTP
1818 WHERE to_date(to_char(PTP.end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1819 BETWEEN TRUNC(v_period_end_date, 'MONTH')
1820 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1821 AND PTP.payroll_id = p_payroll_id;
1822 else
1823 SELECT COUNT(0)
1824 INTO v_run_number
1825 FROM per_time_periods PTP
1826 WHERE to_date(to_char(PTP.start_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1827 BETWEEN TRUNC(v_period_start_date, 'MONTH')
1828 AND to_date(to_char(v_period_end_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1829 AND PTP.payroll_id = p_payroll_id;
1830 end if;
1831 else
1832 SELECT COUNT(0)
1833 INTO v_run_number
1834 FROM per_time_periods PTP
1835 WHERE to_date(to_char(PTP.regular_payment_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1836 BETWEEN TRUNC(p_passed_date, 'MONTH')
1837 AND to_date(to_char(p_passed_date,'YYYY/MM/DD'),'YYYY/MM/DD')
1838 AND PTP.payroll_id = p_payroll_id;
1839 end if;*/
1840 --
1841 ELSIF v_number_per_fy = 12 or v_number_per_fy = 13 THEN
1842 if g_debug then
1843 hr_utility.set_location('check_element_freq', 40);
1844 end if;
1845 p_skip_element := 'N';
1846 RETURN ;
1847 END IF;
1848
1849 --
1850 -- Check frequency rule:
1851 -- If none exists, then process!
1852 --
1853 if g_debug then
1854 hr_utility.trace('v_run_number='||to_char(v_run_number));
1855 hr_utility.set_location('check_element_freq', 50);
1856 end if;
1857 SELECT 'N'
1858 INTO p_skip_element
1859 FROM pay_ele_payroll_freq_rules EPF,
1860 pay_freq_rule_periods FRP
1861 WHERE FRP.period_no_in_reset_period = v_run_number
1862 AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
1863 AND EPF.business_group_id + 0 = p_bg_id
1864 AND EPF.payroll_id = p_payroll_id
1865 AND EPF.element_type_id = p_ele_type_id;
1866
1867 RETURN;
1868
1869 EXCEPTION
1870 WHEN NO_DATA_FOUND THEN
1871 if g_debug then
1872 hr_utility.set_location('check_element_freq', 60);
1873 end if;
1874 p_skip_element := 'Y';
1875 RETURN;
1876
1877 END check_element_freq;
1878
1879 PROCEDURE check_element_freq ( p_payroll_id IN NUMBER,
1880 p_bg_id IN NUMBER,
1881 p_pay_action_id IN NUMBER,
1882 p_date_earned IN DATE,
1883 p_ele_type_id IN NUMBER,
1884 p_skip_element OUT NOCOPY VARCHAR2) IS
1885 --
1886 cursor csr_action_parameter is
1887 select pap.parameter_value
1888 from pay_action_parameters pap
1889 where pap.parameter_name = 'FREQ_RULE_WHOLE_PERIOD';
1890 --
1891 l_whole_period varchar2(1);
1892 --
1893 cursor csr_regular_payment_date is
1894 select pte.regular_payment_date
1895 from per_time_periods pte
1896 where pte.payroll_id = p_payroll_id
1897 and p_date_earned between
1898 pte.start_date and pte.end_date;
1899 --
1900 l_date_earned date;
1901 --
1902 BEGIN
1903 --
1904 open csr_action_parameter;
1905 fetch csr_action_parameter into l_whole_period;
1906 if (csr_action_parameter%notfound
1907 or (l_whole_period <> 'Y' and
1908 l_whole_period <> 'N' and
1909 l_whole_period <> 'R'))then
1910 --
1911 l_whole_period := 'D';
1912 --
1913 end if;
1914 close csr_action_parameter;
1915 --
1916 open csr_regular_payment_date;
1917 fetch csr_regular_payment_date into l_date_earned;
1918 if csr_regular_payment_date%notfound then
1919 l_date_earned := p_date_earned;
1920 end if;
1921 close csr_regular_payment_date;
1922 if g_debug then
1923 hr_utility.trace('l_whole_period='||l_whole_period);
1924 hr_utility.trace('p_ele_type_id='||p_ele_type_id);
1925 hr_utility.set_location('check_element_freq', 10);
1926 end if;
1927 --
1928 check_element_freq ( p_payroll_id => p_payroll_id,
1929 p_bg_id => p_bg_id,
1930 p_pay_action_id => p_pay_action_id,
1931 p_passed_date => l_date_earned,
1932 p_ele_type_id => p_ele_type_id,
1933 p_whole_period_only => l_whole_period,
1934 p_skip_element => p_skip_element);
1935 --
1936 END check_element_freq;
1937
1938 end hr_elements;
[Home] [Help]