1 PACKAGE body hr_legislation_elements AS
2 /* $Header: pelegele.pkb 120.8.12020000.3 2013/03/06 16:13:14 divicker ship $ */
3 --****************************************************************************
4 -- INSTALLATION PROCEDURE FOR : PAY_ELEMENT_CLASSIFICATIONS
5 --****************************************************************************
6
7 PROCEDURE install_ele_class(p_phase IN number)
8 ----------------------------------------------
9 IS
10 -- Install procedure to transfer startup element classifications into
11 -- a live account.
12
13 l_null_return varchar2(1); -- For 'select null' statements
14 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
15
16 CURSOR stu -- Selects all rows from startup entity
17 IS
18 select classification_name c_true_key
19 , rowid
20 , classification_id c_surrogate_key
21 , legislation_code c_leg_code
22 , legislation_subgroup c_leg_sgrp
23 , business_group_id
24 , description
25 , costing_debit_or_credit
26 , default_high_priority
27 , default_low_priority
28 , default_priority
29 , distributable_over_flag
30 , non_payments_flag
31 , parent_classification_id
32 , costable_flag
33 , create_by_default_flag
34 , last_update_date
35 , last_updated_by
36 , last_update_login
37 , created_by
38 , creation_date
39 , balance_initialization_flag
40 , FREQ_RULE_ENABLED
41 from hr_s_element_classifications
42 order by parent_classification_id desc;
43
44 stu_rec stu%ROWTYPE;
45
46 PROCEDURE crt_exc (exception_type IN varchar2)
47 ----------------------------------------------
48 IS
49 -- Reports any exceptions during the delivery of startup data to
50 -- FF_CONTEXTS_F
51
52 BEGIN
53 -- When the installation procedures encounter an error that cannot
54 -- be handled, an exception is raised and all work is rolled back
55 -- to the last savepoint. The installation process then continues
56 -- with the next primary key to install. The same exception will
57 -- not be raised more than once.
58
59 rollback to new_classification_name;
60
61 hr_legislation.insert_hr_stu_exceptions('pay_element_classifications'
62 , stu_rec.c_surrogate_key
63 , exception_type
64 , stu_rec.c_true_key);
65
66
67 END crt_exc;
68
69
70 PROCEDURE check_next_sequence
71 -----------------------------
72 IS
73
74 v_sequence_number number(15);
75 v_min_delivered number(15);
76 v_max_delivered number(15);
77
78
79 -- Surrogate id conflicts may arise from two scenario's:
80 -- 1. Where the newly select sequence value conflicts with values
81 -- in the STU tables.
82 -- 2. Where selected surrogate keys, from the installed tables,
83 -- conflict with other rows in the STU tables.
84 --
85 -- Both of the above scenario's are tested for.
86 -- The first is a simple match, where if a value is detected in the
87 -- STU tables and the installed tables then a conflict is detected. In
88 -- This instance all STU surrogate keys, for this table, are updated.
89 -- The second is tested for using the sequences.
90 -- If the next value from the live sequence is within the range of
91 -- delivered surrogate id's then the live sequence must be incremented.
92 -- If no action is taken, then duplicates may be introduced into the
93 -- delivered tables, and child rows may be totally invalidated.
94
95 BEGIN
96
97
98 BEGIN --check that the installed id's will not conflict
99 --with the delivered values
100
101
102 select distinct null
103 into l_null_return
104 from pay_element_classifications a
105 where exists
106 (select null
107 from hr_s_element_classifications b
108 where a.classification_id = b.classification_id
109 );
110
111 --conflict may exist
112 --update all classification_id's to remove conflict
113
114 update /*+NO_INDEX*/ hr_s_element_classifications
115 set classification_id = classification_id - 50000000
116 ,parent_classification_id = parent_classification_id - 50000000;
117
118 update /*+NO_INDEX*/ hr_s_BALANCE_CLASSIFICATIONS
119 set classification_id = classification_id - 50000000;
120
121 update /*+NO_INDEX*/ hr_s_ELEMENT_TYPES_F
122 set classification_id = classification_id - 50000000;
123
124 update /*+NO_INDEX*/ hr_s_ELE_CLASSN_RULES
125 set classification_id = classification_id - 50000000;
126
127 update /*+NO_INDEX*/ hr_s_SUB_CLASSN_RULES_F
128 set classification_id = classification_id - 50000000;
129
130 --
131 -- #346359 ensure STU_TAXABILITY_RULES classification_id is kept
132 -- in step along with the rest of the children.
133 --
134 update /*+NO_INDEX*/ hr_s_TAXABILITY_RULES
135 set classification_id = classification_id - 50000000;
136
137 update hr_s_application_ownerships
138 set key_value = key_value - 50000000
139 where key_name = 'CLASSIFICATION_ID';
140
141 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
142
143 END; --check of classification_id
144
145
146
147 select min(classification_id) - (count(*) *3)
148 , max(classification_id) + (count(*) *3)
149 into v_min_delivered
150 , v_max_delivered
151 from hr_s_element_classifications;
152
153 select pay_element_classifications_s.nextval
154 into v_sequence_number
155 from dual;
156
157 IF (v_sequence_number BETWEEN v_min_delivered AND v_max_delivered) THEN
158
159 hr_legislation.munge_sequence('PAY_ELEMENT_CLASSIFICATIONS_S',
160 v_sequence_number,
161 v_max_delivered);
162 END IF;
163
164 END check_next_sequence;
165
166 FUNCTION check_parents RETURN BOOLEAN
167 -------------------------------------
168 IS
169 -- Check if parent data is correct
170
171 BEGIN
172
173 -- This procedure is only called in phase 2. The logic to check if
174 -- a given parental foriegn key exists is split into two parts for
175 -- every foriegn key. The first select from the delivery tables.
176
177 -- If a row is founnd then the installation of the parent must have
178 -- failed, and this installation must not go ahead. If no data is
179 -- found, ie: an exception is raised, the installation is valid.
180
181 -- The second check looks for a row in the live tables. If no rows
182 -- are returned then this installation is invalid, since this means
183 -- that the parent referenced by this row is not present in the
184 -- live tables.
185
186 -- The distinct is used in case the parent is date effective and many rows
187 -- may be returned by the same parent id.
188
189
190 IF stu_rec.parent_classification_id is null THEN
191 -- No need to check parent
192 return TRUE;
193 END IF;
194
195 BEGIN
196
197 -- Start the checking against the first parent table
198
199 select distinct null
200 into l_null_return
201 from hr_s_element_classifications
202 where classification_id = stu_rec.parent_classification_id;
203
204 crt_exc('Parent classification remains in delivery tables');
205
206 -- Parent row still in startup account
207
208 return FALSE;
209
210 EXCEPTION WHEN NO_DATA_FOUND THEN
211
212 null;
213
214 END;
215
216
217 --Now check the live account
218
219 BEGIN
220
221 select null
222 into l_null_return
223 from pay_element_classifications
224 where classification_id = stu_rec.parent_classification_id;
225
226 return TRUE;
227
228 EXCEPTION WHEN NO_DATA_FOUND THEN
229
230
231 crt_exc('Parent classification not installed');
232
233 return FALSE;
234
235 END;
236
237 END check_parents;
238
239 PROCEDURE update_uid
240 --------------------
241 IS
242 -- Subprogram to update surrogate UID and all occurrences in child rows
243
244 BEGIN
245
246 BEGIN
247
248
249 select distinct classification_id
250 into l_new_surrogate_key
251 from pay_element_classifications
252 where classification_name = stu_rec.c_true_key
253 and business_group_id is null
254 and ( (legislation_code is null
255 and stu_rec.c_leg_code is null)
256 or (legislation_code = stu_rec.c_leg_code) );
257
258 EXCEPTION WHEN NO_DATA_FOUND THEN
259
260
261 select pay_element_classifications_s.nextval
262 into l_new_surrogate_key
263 from dual;
264
265 WHEN TOO_MANY_ROWS THEN
266
267 hr_legislation.hrrunprc_trace_on;
268 hr_utility.trace('sel pay_element_classifications TMR');
269
270 hr_utility.trace('classification_name ' ||
271 stu_rec.c_true_key);
272 hr_utility.trace(':lc: ' || ':' ||
273 stu_rec.c_leg_code || ':');
274 hr_legislation.hrrunprc_trace_off;
275 raise;
276 END;
277
278 -- Update all child entities
279
280 update hr_s_element_classifications
281 set classification_id = l_new_surrogate_key
282 where classification_id = stu_rec.c_surrogate_key;
283
284 update hr_s_element_classifications
285 set parent_classification_id = l_new_surrogate_key
286 where parent_classification_id = stu_rec.c_surrogate_key;
287
288 update hr_s_application_ownerships
289 set key_value = to_char(l_new_surrogate_key)
290 where key_value = to_char(stu_rec.c_surrogate_key)
291 and key_name = 'CLASSIFICATION_ID';
292
293 update hr_s_element_types_f
294 set classification_id = l_new_surrogate_key
295 where classification_id = stu_rec.c_surrogate_key;
296
297 update hr_s_balance_classifications
298 set classification_id = l_new_surrogate_key
299 where classification_id = stu_rec.c_surrogate_key;
300
301 update hr_s_sub_classn_rules_f
302 set classification_id = l_new_surrogate_key
303 where classification_id = stu_rec.c_surrogate_key;
304
305 update hr_s_ele_classn_rules
306 set classification_id = l_new_surrogate_key
307 where classification_id = stu_rec.c_surrogate_key;
308
309 update hr_s_taxability_rules
310 set classification_id = l_new_surrogate_key
311 where classification_id = stu_rec.c_surrogate_key;
312
313 END update_uid;
314
315 PROCEDURE remove (p_delivered IN varchar2)
316 ------------------------------------------
317 IS
318 -- Remove a row from either the startup tables or the installed tables
319
320 BEGIN
321
322
323 delete from hr_s_element_classifications
324 where rowid = stu_rec.rowid;
325
326 -- #334582 If the row is being removed 'undelivered' i.e. it's not
327 -- required in this installation, remove its child
328 -- hr_s_taxability_rules rows here, so we don't get a later FKEY
329 -- constraint violation.
330 --
331 if (p_delivered = 'N') then
332 delete from hr_s_taxability_rules
333 where classification_id = stu_rec.c_surrogate_key;
334 end if;
335
336 END remove;
337
338 FUNCTION valid_ownership RETURN BOOLEAN
339 ---------------------------------------
340 IS
341 -- Table hr_application_ownerships in the delivery account, which
342 -- dictate which products a piece of data is used for. If the query
343 -- returns a row then this data is required, and the function will
344 -- return true. If no rows are returned and an exception is raised,
345 -- then this row is not required and may be deleted from the delivery
346 -- tables.
347
348 -- If legislation code and subgroup code are included on the delivery
349 -- tables, a check must be made to determine if the data is defined for
350 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
351 -- installation.
352
353 -- A return code of TRUE indicates that the row is required.
354
355 -- The exception is raised within this procedure if no rows are returned
356 -- in this select statement. If no rows are returned then one of the
357 -- following is true:
358 -- 1. No ownership parameters are defined.
359 -- 2. The products, for which owning parameters are defined, are not
360 -- installed with as status of 'I'.
361 -- 3. The data is defined for a legislation subgroup that is not active.
362
363 BEGIN
364
365 IF p_phase <> 1 THEN --only perform in phase 1
366 return TRUE;
367 END IF;
368
369
370 -- If exception raised below then this row is not needed
371 if (stu_rec.c_leg_sgrp is null) then
372 select null
373 into l_null_return
374 from dual
375 where exists
376 (select null
377 from hr_s_application_ownerships a
378 , fnd_product_installations b
379 , fnd_application c
380 where a.key_name = 'CLASSIFICATION_ID'
381 and a.key_value = stu_rec.c_surrogate_key
382 and a.product_name = c.application_short_name
383 and c.application_id = b.application_id
384 and ((b.status = 'I' and c.application_short_name <> 'PQP')
385 or
386 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
387 else
388 select null
389 into l_null_return
390 from dual
391 where exists
392 (select null
393 from hr_s_application_ownerships a
394 , fnd_product_installations b
395 , fnd_application c
396 where a.key_name = 'CLASSIFICATION_ID'
397 and a.key_value = stu_rec.c_surrogate_key
398 and a.product_name = c.application_short_name
399 and c.application_id = b.application_id
400 and ((b.status = 'I' and c.application_short_name <> 'PQP')
401 or
402 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
403 and exists (select null from hr_legislation_subgroups d
404 where d.legislation_code = stu_rec.c_leg_code
405 and d.legislation_subgroup = stu_rec.c_leg_sgrp
406 and d.active_inactive_flag = 'A'
407 );
408 end if;
409
410 return TRUE; --indicates row is required
411
412 EXCEPTION WHEN NO_DATA_FOUND THEN
413
414 -- Row not needed for any installed product
415
416
417 remove ('N');
418
419 -- Indicate row not needed
420
421 return FALSE;
422
423 END valid_ownership;
424
425 PROCEDURE transfer_row
426 ----------------------
427 IS
428 -- Check if a delivered row is needed and insert into the
429 -- live tables if it is
430
431 BEGIN
432
433 BEGIN
434 -- Perform a check to see if the primary key has been created within
435 -- a visible business group. Ie: the business group is for the same
436 -- legislation as the delivered row, or the delivered row has a null
437 -- legislation. If no rows are returned then the primary key has not
438 -- already been created by a user.
439
440 select distinct null
441 into l_null_return
442 from pay_element_classifications a
443 where a.classification_name = stu_rec.c_true_key
444 and a.business_group_id is not null
445 and exists (select null from per_business_groups b
446 where b.business_group_id = a.business_group_id
447 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
448
449 crt_exc('Row already created in a business group');
450
451 -- Indicate this row is not to be transferred
452
453 return;
454
455 EXCEPTION WHEN NO_DATA_FOUND THEN
456
457 null;
458
459 END;
460
461
462 -- Now perform a check to see if this primary key has been installed
463 -- with a legislation code that would make it visible at the same time
464 -- as this row. Ie: if any legislation code is null within the set of
465 -- returned rows, then the transfer may not go ahead. If no rows are
466 -- returned then the delivered row is fine.
467 -- G1746. Add the check for business_group_id is null, otherwise the
468 -- row may be wrongly rejected because it already exists for a
469 -- specific business group in another legislation. This, though
470 -- unlikely, is permissible. RMF 05.01.95.
471
472 BEGIN
473
474 select distinct null
475 into l_null_return
476 from pay_element_classifications
477 where classification_name = stu_rec.c_true_key
478 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
479 and (legislation_code is null or stu_rec.c_leg_code is null )
480 and business_group_id is null;
481
482 crt_exc('Row already created for a visible legislation');
483
484 -- Indicate this row is not to be transferred
485
486 return;
487
488 EXCEPTION WHEN NO_DATA_FOUND THEN
489
490 null;
491
492 END;
493
494
495 -- When the procedure is called in phase 1, there is no need to
496 -- actually perform the transfer from the delivery tables into the
497 -- live. Hence if phase = 1 control is returned to the calling
498 -- procedure and the next row is returned.
499
500 -- If the procedure is called in phase 2, then the live row is updated
501 -- with the values on the delivered row.
502
503 -- The routine check_parents validates foreign key references and
504 -- ensures referential integrity. The routine checks to see if the
505 -- parents of a given row have been transfered to the live tables.
506
507 -- This may only be called in phase two since in phase one all
508 -- parent rows will remain in the delivery tables.
509
510 -- After the above checks only data that has been chanegd or is new
511 -- will be left in the delivery tables. At this stage if the row is
512 -- already present then it must be updated to ensure referential
513 -- integrity. Therefore an update will be performed and if SQL%FOUND
514 -- is FALSE an insert will be performed.
515
516 -- The last step of the transfer, in phase 2, is to delete the now
517 -- transfered row from the delivery tables.
518
519 IF p_phase = 1 THEN
520 return;
521 END IF;
522
523 IF NOT check_parents THEN
524 -- Fails of parents exist
525 return;
526 END IF;
527
528
529 update pay_element_classifications
530 set classification_name = stu_rec.c_true_key
531 , legislation_code = stu_rec.c_leg_code
532 , legislation_subgroup = stu_rec.c_leg_sgrp
533 , business_group_id = stu_rec.business_group_id
534 , description = stu_rec.description
535 , costing_debit_or_credit = stu_rec.costing_debit_or_credit
536 , default_high_priority = stu_rec.default_high_priority
537 , default_low_priority = stu_rec.default_low_priority
538 , default_priority = stu_rec.default_priority
539 , distributable_over_flag = stu_rec.distributable_over_flag
540 , non_payments_flag = stu_rec.non_payments_flag
541 , parent_classification_id = stu_rec.parent_classification_id
542 , costable_flag = stu_rec.costable_flag
543 , create_by_default_flag = stu_rec.create_by_default_flag
544 , last_update_date = stu_rec.last_update_date
545 , last_updated_by = stu_rec.last_updated_by
546 , last_update_login = stu_rec.last_update_login
547 , created_by = stu_rec.created_by
548 , creation_date = stu_rec.creation_date
549 , balance_initialization_flag = stu_rec.balance_initialization_flag
550 , FREQ_RULE_ENABLED = stu_rec.FREQ_RULE_ENABLED
551 where classification_id = stu_rec.c_surrogate_key;
552
553 IF SQL%NOTFOUND THEN
554
555 BEGIN
556 insert into pay_element_classifications
557 (classification_name
558 ,classification_id
559 ,legislation_code
560 ,legislation_subgroup
561 ,business_group_id
562 ,description
563 ,costing_debit_or_credit
564 ,default_high_priority
565 ,default_low_priority
566 ,default_priority
567 ,distributable_over_flag
568 ,non_payments_flag
569 ,parent_classification_id
570 ,costable_flag
571 ,create_by_default_flag
572 ,last_update_date
573 ,last_updated_by
574 ,last_update_login
575 ,created_by
576 ,creation_date
577 ,balance_initialization_flag
578 ,FREQ_RULE_ENABLED
579 )
580 values
581 (stu_rec.c_true_key
582 ,stu_rec.c_surrogate_key
583 ,stu_rec.c_leg_code
584 ,stu_rec.c_leg_sgrp
585 ,stu_rec.business_group_id
586 ,stu_rec.description
587 ,stu_rec.costing_debit_or_credit
588 ,stu_rec.default_high_priority
589 ,stu_rec.default_low_priority
590 ,stu_rec.default_priority
591 ,stu_rec.distributable_over_flag
592 ,stu_rec.non_payments_flag
593 ,stu_rec.parent_classification_id
594 ,stu_rec.costable_flag
595 ,stu_rec.create_by_default_flag
596 ,stu_rec.last_update_date
597 ,stu_rec.last_updated_by
598 ,stu_rec.last_update_login
599 ,stu_rec.created_by
600 ,stu_rec.creation_date
601 ,stu_rec.balance_initialization_flag
602 ,stu_rec.FREQ_RULE_ENABLED);
603 EXCEPTION WHEN OTHERS THEN
604 hr_legislation.hrrunprc_trace_on;
605 hr_utility.trace('ins pay_element_classifications');
606 hr_utility.trace('classification_name ' ||
607 stu_rec.c_true_key);
608 hr_utility.trace('classification_id ' ||
609 to_char(stu_rec.c_surrogate_key));
610 hr_utility.trace(':lc: ' || ':' ||
611 stu_rec.c_leg_code || ':');
612 hr_legislation.hrrunprc_trace_off;
613 raise;
614 END;
615
616 END IF;
617
618
619 remove ('Y');
620
621 END transfer_row;
622
623 BEGIN
624 -- This is the main loop to perform the installation logic. A cursor
625 -- is opened to control the loop, and each row returned is placed
626 -- into a record defined within the main procedure so each sub
627 -- procedure has full access to all returrned columns. For each
628 -- new row returned, a new savepoint is declared. If at any time
629 -- the row is in error a rollback is performed to the savepoint
630 -- and the next row is returned. Ownership details are checked and
631 -- if the row is required then the surrogate id is updated and the
632 -- main transfer logic is called.
633
634 IF p_phase = 1 THEN
635 check_next_sequence;
636 END IF;
637
638 FOR delivered IN stu LOOP
639
640 -- Uses main cursor stu to impilicity define a record
641
642
643 savepoint new_classification_name;
644
645 stu_rec := delivered;
646
647 IF p_phase = 2 THEN
648 l_new_Surrogate_key := stu_rec.c_surrogate_key;
649 END IF;
650
651 IF valid_ownership THEN
652
653 -- Test the row onerships for the current row
654
655
656 IF p_phase = 1 THEN
657 update_uid;
658 END IF;
659
660 transfer_row;
661
662 END IF;
663
664 END LOOP;
665
666 END install_ele_class;
667
668 --****************************************************************************
669 -- INSTALLATION PROCEDURE FOR : PAY_ELEMENT_TYPES
670 --****************************************************************************
671
672 PROCEDURE install_elements (p_phase IN NUMBER)
673 ----------------------------------------------
674 IS
675 -- Install procedure to transfer startup delivered pay_element_typess into a
676 -- live account, and remove the then delivered rows from the delivery
677 -- account.
678
679 -- This procedure is called in two phase. Only in the second phase are
680 -- details transferred into live tables. The parameter p_phase holds
681 -- the phase number.
682
683 -- The element are installed if differences are found between the delivered
684 -- rows and the installed rows. Also if there are differences between the
685 -- input values, defined for the element, in the delivered tables and
686 -- the live tables.
687
688 row_in_error exception;
689 l_current_proc varchar2(80) := 'hr_legislation.install_elements';
690 l_new_element_type_id number(15);
691 l_null_return varchar2(1);
692 l_payroll_install_status varchar2 (1);
693 l_formula_id number (15);
694 l_end_of_time date := hr_general.end_of_time;
695 l_flex_value_set_id number(10);
696
697 CURSOR c_distinct_name
698 IS
699 -- Select distinct element names. The element name can no longer be
700 -- guarenteed to be unique. This cursor selects all distinct names
701 -- for the next cusrsor to select distinct element_type_id's from.
702
703 select distinct element_name
704 from hr_s_element_types_f;
705
706 CURSOR c_distinct_element(pc_ele_name varchar2)
707 IS
708 -- Retrieve all distinct element type id's for the current element.
709 -- This row is then used to select all date effective rows for this id.
710
711 select max(effective_end_date) c_end
712 , min(effective_start_date) c_start
713 , element_type_id c_surrogate_key
714 , element_name c_true_key
715 , legislation_code
716 , legislation_subgroup
717 , nvl(new_element_type_flag, 'Y') new_element_type_flag
718 from hr_s_element_types_f
719 where element_name = pc_ele_name
720 group by element_type_id
721 , element_name
722 , legislation_code
723 , legislation_subgroup
724 , nvl(new_element_type_flag, 'Y');
725
726 CURSOR c_each_element_row(pc_element_type_id varchar2)
727 IS
728 -- Selects all date effective rows for the current true primary key
729
730 -- The primary key has already been selected using the above cursor.
731 -- This cursor accepts the primary key as a parameter and selects all
732 -- date effective rows for it.
733
734 select *
735 from hr_s_element_types_f
736 where element_type_id = pc_element_type_id;
737
738 CURSOR sub_rules(pc_element_id number)
739 IS
740 -- Retrieves sub classification rules for the current element
741
742 select *
743 from hr_s_sub_classn_rules_f
744 where element_type_id = pc_element_id;
745
746 CURSOR proc_rules(pc_element_id number)
747 IS
748 -- Retrieves the distinct id's from status rules for this element
749 -- Used for the update of uid's.
750 --
751 -- #346366. Also pull back the assignment_status_type_id, as we
752 -- could have different rules for different statuses for the
753 -- same element type.
754 --
755 select distinct status_processing_rule_id s_rule_id,
756 assignment_status_type_id,processing_rule
757 from hr_s_status_processing_rules_f
758 where element_Type_id = pc_element_id;
759
760 CURSOR all_p_rules(pc_stat_rule_id number)
761 IS
762 -- Retrieves full details of processing rules for the insertiion into
763 -- live tables.
764
765 select *
766 from hr_s_status_processing_rules_f
767 where status_processing_rule_id = pc_stat_rule_id;
768
769 CURSOR frrs(pc_stat_rule_id number)
770 IS
771 -- Retrieves formula result rules for a given status processing rule
772
773 select *
774 from hr_s_formula_result_rules_f
775 where status_processing_rule_id = pc_stat_rule_id;
776
777 CURSOR d_frrs(pc_stat_rule_id number)
778 IS
779 -- Retrieves distinct id's from formula result rules for a given status
780 -- processing rule.
781
782 select distinct formula_result_rule_id
783 from hr_s_formula_result_rules_f
784 where status_processing_rule_id = pc_stat_rule_id;
785
786 CURSOR inputs(pc_element_id number)
787 IS
788 -- Retrieve distinct input value details for this element
789
790 select distinct input_value_id
791 , name
792 , value_set_name
793 , new_input_value_flag
794 from hr_s_input_values_f
795 where element_Type_id = pc_element_id;
796
797 -- These records are defined here so all sub procedures may use the
798 -- values selected. This saves the need for all sub procedures to have
799 -- a myriad of parameters passed. The cursors are controlled in FOR
800 -- cursor LOOPs. When a row is returned the whole record is copied into
801 -- these record definitions.
802
803 r_distinct c_distinct_element%ROWTYPE;
804 r_each_row c_each_element_row%ROWTYPE;
805
806
807 PROCEDURE check_next_sequence
808 -----------------------------
809 IS
810
811 v_sequence_number number(15);
812 v_min_delivered number(15);
813 v_max_delivered number(15);
814
815 -- Surrogate id conflicts may arise from two scenario's:
816 -- 1. Where the newly select sequence value conflicts with values
817 -- in the STU tables.
818 -- 2. Where selected surrogate keys, from the installed tables,
819 -- conflict with other rows in the STU tables.
820 --
821 -- Both of the above scenario's are tested for.
822 -- The first is a simple match, where if a value is detected in the
823 -- STU tables and the installed tables then a conflict is detected. In
824 -- This instance all STU surrogate keys, for this table, are updated.
825 -- The second is tested for using the sequences.
826 -- If the next value from the live sequence is within the range of
827 -- delivered surrogate id's then the live sequence must be incremented.
828 -- If no action is taken, then duplicates may be introduced into the
829 -- delivered tables, and child rows may be totally invalidated.
830
831 BEGIN
832
833
834 BEGIN --check that the installed id's will not conflict
835 --with the delivered values
836
837
838 select distinct null
839 into l_null_return
840 from pay_element_types_f a
841 where exists
842 (select null
843 from hr_s_element_types_f b
844 where a.element_type_id = b.element_type_id
845 );
846
847 --conflict may exist
848 --update all element_type_id's to remove conflict
849
850 update /*+NO_INDEX*/ hr_s_ELEMENT_TYPES_F
851 set element_type_id = element_type_id - 50000000,
852 retro_summ_ele_id = retro_summ_ele_id - 50000000;
853
854 update /*+NO_INDEX*/ hr_s_ELEMENT_TYPE_RULES
855 set element_type_id = element_type_id - 50000000;
856
857 update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
858 set element_type_id = element_type_id - 50000000;
859
860 update /*+NO_INDEX*/ hr_s_INPUT_VALUES_F
861 set element_type_id = element_type_id - 50000000;
862
863 update /*+NO_INDEX*/ hr_s_STATUS_PROCESSING_RULES_F
864 set element_type_id = element_type_id - 50000000;
865
866 update /*+NO_INDEX*/ hr_s_SUB_CLASSN_RULES_F
867 set element_type_id = element_type_id - 50000000;
868
869 update hr_s_application_ownerships
870 set key_value = key_value - 50000000
871 where key_name = 'ELEMENT_TYPE_ID';
872
873 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
874
875 END; --check of element_type_id
876
877
878 BEGIN --Now check input_values_F
879
880
881 select distinct null
882 into l_null_return
883 from pay_input_values_f a
884 where exists
885 (select null
886 from hr_s_input_values_f b
887 where a.input_value_id = b.input_value_id
888 );
889
890 --conflict may exist
891 --update all input_value_id's to remove conflict
892
893 update /*+NO_INDEX*/ hr_s_INPUT_VALUES_F
894 set input_value_id = input_value_id - 50000000;
895
896 update /*+NO_INDEX*/ hr_s_BALANCE_FEEDS_F
897 set input_value_id = input_value_id - 50000000;
898
899 update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
900 set input_value_id = input_value_id - 50000000;
901
902 update /*+NO_INDEX*/ hr_s_balance_types
903 set input_value_id = input_value_id - 50000000;
904
905 --
906 -- #347569 Removed two lines here, which were an exact copy of
907 -- the update STU_FORMULA_RESULT_RULES_F statement immediately
908 -- above, resulting in the input_value_id being decremented
909 -- twice, thereby breaking the fkey link.
910 --
911
912 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
913
914 END; --check of input_value_id
915
916
917 BEGIN --now check status processing riles
918
919
920 select distinct null
921 into l_null_return
922 from pay_status_processing_rules_f a
923 where exists
924 (select null
925 from hr_s_status_processing_rules_f b
926 where a.status_processing_rule_id=b.status_processing_rule_id
927 );
928
929 --conflict may exist
930 --update all status_processing_rule_id's to remove conflict
931
932 update hr_s_FORMULA_RESULT_RULES_F
933 set status_processing_rule_id=status_processing_rule_id-50000000;
934
935 update hr_s_STATUS_PROCESSING_RULES_F
936 set status_processing_rule_id=status_processing_rule_id-50000000;
937
938 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
939
940 END; --check of status_processing_rule_id
941
942
943 BEGIN --now check formula result rules
944
945
946 select distinct null
947 into l_null_return
948 from pay_formula_result_rules_f a
949 where exists
950 (select null
951 from hr_s_formula_result_rules_f b
952 where a.formula_result_rule_id = b.formula_result_rule_id
953 );
954
955 --conflict may exist
956 --update all formula_result_rule_id's to remove conflict
957
958 update /*+NO_INDEX*/ hr_s_FORMULA_RESULT_RULES_F
959 set formula_result_rule_id = formula_result_rule_id - 50000000;
960
961 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
962
963 END; --check of formula_result_rule_id
964
965
966 BEGIN --now check sub classification rules
967
968
969 select distinct null
970 into l_null_return
971 from pay_sub_classification_rules_f a
972 where exists
973 (select null
974 from hr_s_sub_classn_rules_f b
975 where a.sub_classification_rule_id=b.sub_classification_rule_id
976 );
977
978 --conflict may exist
979 --update all sub_classification_rule_id's to remove conflict
980
981 update /*+NO_INDEX*/ hr_s_sub_classn_rules_f
982 set sub_classification_rule_id=sub_classification_rule_id-50000000;
983
984 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
985
986 END; --check of sub_classification_rule_id
987
988
989
990 select min(element_type_id) - (count(*) *3)
991 , max(element_type_id) + (count(*) *3)
992 into v_min_delivered
993 , v_max_delivered
994 from hr_s_element_types_f;
995
996 select pay_element_types_s.nextval
997 into v_sequence_number
998 from dual;
999
1000 WHILE v_sequence_number
1001 BETWEEN v_min_delivered AND v_max_delivered
1002 LOOP
1003
1004
1005 select pay_element_types_s.nextval
1006 into v_sequence_number
1007 from dual;
1008
1009 END LOOP;
1010
1011 --Now check input values
1012
1013
1014 select min(input_value_id) - (count(*) *3)
1015 , max(input_value_id) + (count(*) *3)
1016 into v_min_delivered
1017 , v_max_delivered
1018 from hr_s_input_values_f;
1019
1020 select pay_input_Values_s.nextval
1021 into v_sequence_number
1022 from dual;
1023
1024 IF v_sequence_number
1025 BETWEEN v_min_delivered AND v_max_delivered
1026 THEN
1027
1028 hr_legislation.munge_sequence('PAY_INPUT_VALUES_S',
1029 v_sequence_number,
1030 v_max_delivered);
1031
1032 END IF;
1033
1034 --now check status_processing_rules
1035
1036 select min(status_processing_rule_id) - (count(*) *3)
1037 , max(status_processing_rule_id) + (count(*) *3)
1038 into v_min_delivered
1039 , v_max_delivered
1040 from hr_s_status_processing_rules_f;
1041
1042 select pay_status_processing_rules_s.nextval
1043 into v_sequence_number
1044 from dual;
1045
1046 IF v_sequence_number
1047 BETWEEN v_min_delivered AND v_max_delivered
1048 THEN
1049
1050 hr_legislation.munge_sequence('PAY_STATUS_PROCESSING_RULES_S',
1051 v_sequence_number,
1052 v_max_delivered);
1053
1054 END IF;
1055
1056 --now check formula_result_rules
1057
1058 select min(formula_result_rule_id) - (count(*) *3)
1059 , max(formula_result_rule_id) + (count(*) *3)
1060 into v_min_delivered
1061 , v_max_delivered
1062 from hr_s_formula_result_rules_f;
1063
1064 select pay_formula_result_rules_s.nextval
1065 into v_sequence_number
1066 from dual;
1067
1068 IF v_sequence_number
1069 BETWEEN v_min_delivered AND v_max_delivered
1070 THEN
1071
1072 hr_legislation.munge_sequence('PAY_FORMULA_RESULT_RULES_S',
1073 v_sequence_number,
1074 v_max_delivered);
1075
1076 END IF;
1077
1078
1079 --now check sub_classification_rules_f
1080
1081 select min(sub_classification_rule_id) - (count(*) *3)
1082 , max(sub_classification_rule_id) + (count(*) *3)
1083 into v_min_delivered
1084 , v_max_delivered
1085 from hr_s_sub_classn_rules_f;
1086
1087 select pay_sub_classification_rules_s.nextval
1088 into v_sequence_number
1089 from dual;
1090
1091 IF v_sequence_number
1092 BETWEEN v_min_delivered AND v_max_delivered
1093 THEN
1094
1095 hr_legislation.munge_sequence('PAY_SUB_CLASSIFICATION_RULES_S',
1096 v_sequence_number,
1097 v_max_delivered);
1098
1099 END IF;
1100
1101 END check_next_sequence;
1102
1103
1104 PROCEDURE crt_exc (exception_type IN varchar2,rollback_to IN varchar2)
1105 ----------------------------------------------------------------------
1106 IS
1107 -- Reports any exceptions during the delivery of startup data to
1108 -- PAY_ELEMENT_TYPES
1109
1110 BEGIN
1111 -- When the installation procedures encounter an error that cannot
1112 -- be handled, an exception is raised and all work is rolled back
1113 -- to the last savepoint. The installation process then continues
1114 -- with the next primary key to install. The same exception will
1115 -- not be raised more than once.
1116
1117
1118 IF rollback_to = 'N' THEN
1119 rollback to new_element_name;
1120 ELSE
1121 rollback to new_distinct_id;
1122 END IF;
1123
1124 hr_legislation.insert_hr_stu_exceptions('pay_element_types_f'
1125 , r_distinct.c_surrogate_key
1126 , exception_type
1127 , r_distinct.c_true_key);
1128
1129
1130 END crt_exc;
1131
1132 PROCEDURE remove(v_id IN number)
1133 --------------------------------
1134 IS
1135 -- Subprogram to delete a row from the delivery tables, and all child
1136 -- application ownership rows
1137
1138 BEGIN
1139
1140 delete from hr_s_element_types_f
1141 where element_type_id = v_id;
1142
1143 delete from hr_s_sub_classn_rules_f
1144 where element_type_id = v_id;
1145
1146 delete from hr_s_input_values_f
1147 where element_type_id = v_id;
1148
1149 delete from hr_s_formula_result_rules_f a
1150 where exists
1151 (select null
1152 from hr_s_status_processing_rules_f b
1153 where b.status_processing_rule_id = a.status_processing_rule_id
1154 and b.element_type_id = v_id
1155 );
1156
1157 delete from hr_s_status_processing_rules_f
1158 where element_type_id = v_id;
1159
1160 END remove;
1161
1162 PROCEDURE update_uid
1163 --------------------
1164 IS
1165 -- Subprogram to update surrogate UID and all occurrences in child rows
1166
1167 v_new_sub_class_id number(15);
1168 v_new_input_id number(15);
1169 v_new_spr_id number(15);
1170 v_new_frr_id number(15);
1171 v_new_input_value_flag varchar2 (1);
1172 v_new_element_type_flag varchar2 (1);
1173 v_new_sub_class_rule_flag varchar2 (1);
1174 v_dummy number(15);
1175
1176 BEGIN
1177 -- See if this primary key is already installed. If so then the sorrogate
1178 -- key of the delivered row must be updated to the value in the installed
1179 -- tables. If the row is not already present then select the next value
1180 -- from the sequence. In either case all rows for this primary key must
1181 -- be updated, as must all child references to the old surrogate uid.
1182
1183 --PSEUDO
1184 -- Error if we clash on delivered data to pseudo data present
1185 IF hr_legislation.g_pseudo_enabled = 'Y' THEN
1186 BEGIN
1187 -- Check if we have a pseduo seed clash if enabled
1188 -- Error handling is done in the procedure itself
1189 hr_legislation.PSEUDO_SEED_DATA_CLASH(P_SURROGATE_KEY_NAME => r_distinct.c_true_key,
1190 P_TABLE_NAME => 'PAY_ELEMENT_TYPES_F',
1191 P_LEGISLATION_CODE => r_distinct.legislation_code);
1192 EXCEPTION WHEN OTHERS THEN
1193 raise;
1194 END;
1195 END IF;
1196
1197 BEGIN
1198 select distinct element_type_id
1199 into l_new_element_type_id
1200 from pay_element_types_f
1201 where replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
1202 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_')
1203 and business_Group_id is null
1204 and legislation_code = r_distinct.legislation_code;
1205
1206 v_new_element_type_flag := 'N';
1207
1208 EXCEPTION WHEN NO_DATA_FOUND THEN
1209
1210
1211 select pay_element_types_s.nextval
1212 into l_new_element_type_id
1213 from dual;
1214
1215 v_new_element_type_flag := 'Y';
1216
1217 WHEN TOO_MANY_ROWS THEN
1218 hr_legislation.hrrunprc_trace_on;
1219 hr_utility.trace('sel pay_element_types_f TMR');
1220 hr_utility.trace('element_name ' ||
1221 r_distinct.c_true_key);
1222 hr_utility.trace(':lc: ' || ':' ||
1223 r_distinct.legislation_code || ':');
1224 hr_legislation.hrrunprc_trace_off;
1225 raise;
1226 END;
1227
1228 update hr_s_element_types_f
1229 set element_type_id = l_new_element_type_id,
1230 new_element_type_flag = v_new_element_type_flag
1231 where element_type_id = r_distinct.c_surrogate_key;
1232
1233 update hr_s_element_types_f
1234 set retro_summ_ele_id = l_new_element_type_id
1235 where retro_summ_ele_id = r_distinct.c_surrogate_key;
1236
1237 update hr_s_element_type_rules
1238 set element_type_id = l_new_element_type_id
1239 where element_type_id = r_distinct.c_surrogate_key;
1240
1241 update hr_s_formula_result_rules_f
1242 set element_type_id = l_new_element_type_id
1243 where element_type_id = r_distinct.c_surrogate_key;
1244
1245 update hr_s_application_ownerships
1246 set key_value = to_char(l_new_element_type_id)
1247 where key_value = to_char(r_distinct.c_surrogate_key)
1248 and key_name = 'ELEMENT_TYPE_ID';
1249
1250 -- update the uid of associated input values
1251
1252 FOR i_vals IN inputs(r_distinct.c_surrogate_key) LOOP
1253
1254 --PSEUDO
1255 -- Error if we clash on delivered data to pseudo data present
1256 IF hr_legislation.g_pseudo_enabled = 'Y' THEN
1257 hr_legislation.PSEUDO_SEED_DATA_CLASH(P_SURROGATE_KEY_NAME => i_vals.name,
1258 P_TABLE_NAME => 'PAY_INPUT_VALUES_F',
1259 P_ADDL_SURROGATE_KEY_NAME1 => r_distinct.c_true_key,
1260 P_LEGISLATION_CODE => r_distinct.legislation_code);
1261 END IF;
1262
1263 BEGIN
1264 -- Test if input value already exists
1265 -- #331823. Add 'distinct' to prevent a 'too many rows'
1266 -- error if there is more than one datetracked
1267 -- version of the input values row.
1268
1269 select distinct input_value_id
1270 into v_new_input_id
1271 from pay_input_values_f
1272 where replace(ltrim(rtrim(upper(name))), ' ', '_') =
1273 replace(ltrim(rtrim(upper(i_vals.name))), ' ', '_')
1274 and business_group_id is null
1275 and element_type_id = l_new_element_Type_id;
1276
1277 v_new_input_value_flag := 'N';
1278
1279 EXCEPTION WHEN NO_DATA_FOUND THEN
1280 -- New input value, so new get new _id
1281 select pay_input_values_s.nextval
1282 into v_new_input_id
1283 from dual;
1284
1285 v_new_input_value_flag := 'Y';
1286
1287 WHEN TOO_MANY_ROWS THEN
1288 hr_legislation.hrrunprc_trace_on;
1289 hr_utility.trace('sel pay_input_values_f TMR');
1290 hr_utility.trace('iv name ' ||
1291 i_vals.name);
1292 hr_utility.trace('element_type_id ' ||
1293 to_char(l_new_element_Type_id));
1294 hr_utility.trace('element_name ' ||
1295 r_distinct.c_true_key);
1296 hr_legislation.hrrunprc_trace_off;
1297 raise;
1298 END;
1299
1300 update hr_s_input_values_f
1301 set input_value_id = v_new_input_id
1302 , element_type_id = l_new_element_type_id
1303 , new_input_value_flag = v_new_input_value_flag
1304 where input_value_id = i_vals.input_value_id;
1305
1306 update hr_s_balance_feeds_f
1307 set input_value_id = v_new_input_id,
1308 new_input_value_flag = v_new_input_value_flag
1309 where input_value_id = i_vals.input_value_id;
1310
1311 update hr_s_formula_result_rules_f
1312 set input_value_id = v_new_input_id
1313 where input_value_id = i_vals.input_value_id;
1314
1315 update hr_s_balance_types
1316 set input_value_id = v_new_input_id
1317 where input_value_id = i_vals.input_value_id;
1318
1319 END LOOP i_vals;
1320
1321 -- Update the uid of sub classification rules
1322
1323 FOR s_class IN sub_rules(r_distinct.c_surrogate_key) LOOP
1324
1325 select pay_sub_classification_rules_s.nextval
1326 into v_new_sub_class_id
1327 from dual;
1328
1329 update hr_s_sub_classn_rules_f
1330 set sub_classification_rule_id = v_new_sub_class_id
1331 , element_type_id = l_new_element_type_id
1332 where sub_classification_rule_id = s_class.sub_classification_rule_id;
1333
1334 BEGIN
1335
1336 select sub_classification_rule_id
1337 into v_dummy
1338 from hr_s_sub_classn_rules_f hscr
1339 where hscr.sub_classification_rule_id = v_new_sub_class_id
1340 and exists
1341 ( select 1
1342 from pay_sub_classification_rules_f pscr
1343 where pscr.element_type_id = hscr.element_type_id
1344 and pscr.classification_id = hscr.classification_id
1345 and nvl(pscr.business_group_id, -1) = nvl(hscr.business_group_id, -1)
1346 and nvl(pscr.legislation_code, 'X') = nvl(hscr.legislation_code, 'X')
1347 and pscr.effective_start_date = hscr.effective_start_date
1348 and pscr.effective_end_date = hscr.effective_end_date);
1349
1350 v_new_sub_class_rule_flag := 'N';
1351
1352 EXCEPTION WHEN NO_DATA_FOUND THEN
1353
1354 v_new_sub_class_rule_flag := 'Y';
1355
1356 END;
1357
1358 update hr_s_sub_classn_rules_f
1359 set new_sub_class_rule_flag = v_new_sub_class_rule_flag
1360 where sub_classification_rule_id = s_class.sub_classification_rule_id;
1361
1362 END LOOP s_class;
1363
1364 -- update the uids of status processing rules and child result rules
1365
1366 FOR sprs IN proc_rules(r_distinct.c_surrogate_key) LOOP
1367
1368 --
1369 -- #346366. Test if status processing rule already exists
1370 -- and use its id if it does, rather than always getting
1371 -- the next number from the sequence. This means that user-entered
1372 -- formula result rules are not orphaned through the parent spr's
1373 -- id being changed.
1374 --
1375 -- 2971029
1376 -- need to consider null = null case seperately else we miss existing
1377 -- data when the assignment_status_type_id is null
1378 -- Also need the select max in case datetrack data can return more
1379 -- than 1 distinct status_processing_rule_id for a given leg_code,bg,
1380 -- assignment_status_type_id and element_type_id combination.
1381 -- Guarantee return of the ID of only the live row that has the max
1382 -- EED for this combination
1383
1384 BEGIN
1385
1386 select distinct status_processing_rule_id
1387 into v_new_spr_id
1388 from pay_status_processing_rules_f spr
1389 where spr.legislation_code = r_distinct.legislation_code
1390 and spr.business_group_id is null
1391 and spr.processing_rule = sprs.processing_rule
1392 and ((spr.assignment_status_type_id is null
1393 and
1394 sprs.assignment_status_type_id is null)
1395 or
1396 (spr.assignment_status_type_id =
1397 sprs.assignment_status_type_id))
1398 and spr.effective_end_date = (select max(spr2.effective_end_date)
1399 from pay_status_processing_rules_f spr2
1400 where spr2.element_type_id = spr.element_type_id
1401 and spr2.processing_rule = spr.processing_rule
1402 and spr2.legislation_code = r_distinct.legislation_code
1403 and spr2.business_group_id is null
1404 and ((spr.assignment_status_type_id is null
1405 and spr2.assignment_status_type_id is null)
1406 or
1407 (spr.assignment_status_type_id =
1408 spr2.assignment_status_type_id)))
1409 and element_type_id = l_new_element_type_id;
1410
1411 EXCEPTION WHEN NO_DATA_FOUND THEN
1412
1413 -- New input value, so new get new _id
1414
1415 select pay_status_processing_rules_s.nextval
1416 into v_new_spr_id
1417 from dual;
1418
1419 WHEN TOO_MANY_ROWS THEN
1420 hr_legislation.hrrunprc_trace_on;
1421 hr_utility.trace('sel pay_status_processing_rules TMR');
1422
1423 hr_utility.trace('assignment_status_type_id ' ||
1424 to_char(sprs.assignment_status_type_id));
1425 hr_utility.trace(':lc: ' || ':' ||
1426 r_distinct.legislation_code || ':');
1427 hr_legislation.hrrunprc_trace_off;
1428 raise;
1429 END;
1430
1431 update hr_s_status_processing_rules_f
1432 set status_processing_rule_id = v_new_spr_id
1433 , element_type_id = l_new_element_type_id
1434 where status_processing_rule_id = sprs.s_rule_id;
1435
1436 FOR results IN d_frrs(sprs.s_rule_id) LOOP
1437
1438 select pay_formula_Result_rules_s.nextval
1439 into v_new_frr_id
1440 from dual;
1441
1442 update hr_s_formula_result_rules_f
1443 set formula_result_rule_id = v_new_frr_id
1444 , status_processing_rule_id = v_new_spr_id
1445 where formula_result_rule_id = results.formula_result_rule_id;
1446
1447 END LOOP results;
1448
1449 END LOOP sprs;
1450
1451 END update_uid;
1452
1453 PROCEDURE integrity_checks
1454 --------------------------
1455 IS
1456 -- After all rows for a primary key have been delivered, entity specific
1457 -- checks must be performed to check to validity of the data that has
1458 -- just been installed.
1459
1460 l_iv_exists boolean;
1461 l_input_value_id pay_input_values_f.input_value_id%type;
1462
1463 BEGIN
1464
1465
1466 IF r_distinct.c_end = l_end_of_time THEN
1467 null;
1468 ELSE
1469 BEGIN
1470 -- Check balance feeds
1471 select distinct null
1472 into l_null_return
1473 from pay_balance_feeds_f a
1474 , pay_input_values_f b
1475 where b.element_type_id = l_new_element_type_id
1476 and a.input_value_id = b.input_value_id
1477 and a.effective_end_Date > r_distinct.c_end
1478 and a.business_group_id is not null;
1479
1480 crt_exc('User created balance feeds exist after the new end date','I');
1481 return;
1482
1483 EXCEPTION WHEN NO_DATA_FOUND THEN
1484
1485 -- No invalid child data
1486
1487 null;
1488
1489 END;
1490
1491 -- check element links
1492
1493 BEGIN
1494
1495 select distinct null
1496 into l_null_return
1497 from pay_element_links_f
1498 where element_type_id = l_new_element_type_id
1499 and effective_end_Date > r_distinct.c_end
1500 and business_group_id is not null;
1501
1502 crt_exc('User created element links exist after the new end date','I');
1503
1504 return;
1505
1506 EXCEPTION WHEN NO_DATA_FOUND THEN
1507
1508 null;
1509
1510 END;
1511
1512 -- Check formula result rules
1513
1514 BEGIN
1515
1516 select distinct null
1517 into l_null_return
1518 from pay_status_processing_rules_f a
1519 , pay_formula_result_rules_f b
1520 where a.element_type_id = l_new_element_type_id
1521 and b.status_processing_rule_id = a.status_processing_rule_id
1522 and b.effective_end_Date > r_distinct.c_end
1523 and b.business_group_id is not null;
1524
1525 crt_exc('User created formula rules exist after the new end date','I');
1526
1527 return;
1528
1529 EXCEPTION WHEN NO_DATA_FOUND THEN
1530
1531 null;
1532
1533 END;
1534
1535 -- Check payroll run results
1536
1537 BEGIN
1538
1539 BEGIN
1540
1541 select input_value_id
1542 into l_input_value_id
1543 from pay_input_values_f
1544 where element_type_id = l_new_element_type_id
1545 and rownum = 1;
1546
1547 l_iv_exists := TRUE;
1548
1549 EXCEPTION WHEN NO_DATA_FOUND THEN
1550
1551 l_iv_exists := FALSE;
1552
1553 END;
1554
1555
1556 if l_iv_exists = TRUE then
1557
1558 select 1
1559 into l_null_return
1560 from dual
1561 where exists
1562 (select /*+ ORDERED INDEX(a PAY_RUN_RESULTS_PK)
1563 USE_NL(a b c) */ null
1564 from pay_run_result_values v
1565 , pay_run_results a
1566 , pay_assignment_actions b
1567 , pay_payroll_actions c
1568 where v.input_value_id = l_input_value_id
1569 and a.run_result_id = v.run_result_id
1570 and b.assignment_action_id = a.assignment_action_id
1571 and c.payroll_action_id = b.payroll_action_id
1572 and c.effective_date > r_distinct.c_end);
1573
1574 else
1575
1576 select 1
1577 into l_null_return
1578 from dual
1579 where exists
1580 (select null
1581 from pay_run_results a
1582 , pay_assignment_actions b
1583 , pay_payroll_actions c
1584 where a.element_Type_id = l_new_element_type_id
1585 and b.assignment_action_id = a.assignment_action_id
1586 and c.payroll_action_id = b.payroll_action_id
1587 and c.effective_date > r_distinct.c_end);
1588
1589 end if;
1590
1591 crt_exc('Run results after the new end date of the element','I');
1592
1593 return;
1594
1595 EXCEPTION WHEN NO_DATA_FOUND THEN
1596
1597 null;
1598
1599 END;
1600
1601 END IF; -- end of time
1602
1603 END integrity_checks;
1604
1605 FUNCTION check_parents RETURN BOOLEAN
1606 -------------------------------------
1607 IS
1608 -- Check the integrity of the references to parent data, before allowing
1609 -- data to be installed. No parents can exist in the startup tables, since
1610 -- this will violate constraints when the row is installed, also the
1611 -- parent uid's must exist in the installed tables already.
1612
1613 -- This function will RETURN TRUE if a parent row still exists in the
1614 -- delivery account. All statements drop through to a RETURN FALSE.
1615
1616 -- This procedure is only called in phase 2. The logic to check if
1617 -- a given parental foriegn key exists is split into two parts for
1618 -- every foriegn key. The first select from the delivery tables.
1619
1620 -- If a row is founnd then the installation of the parent must have
1621 -- failed, and this installation must not go ahead. If no data is
1622 -- found, ie: an exception is raised, the installation is valid.
1623
1624 -- The second check looks for a row in the live tables. If no rows
1625 -- are returned then this installation is invalid, since this means
1626 -- that the parent referenced by this row is not present in the
1627 -- live tables.
1628
1629 -- Return code of true indicates that all parental data is correct.
1630
1631 BEGIN
1632
1633
1634 -- Start first parent check
1635
1636 BEGIN
1637
1638 -- Check first parent does not exist in the delivery tables
1639
1640 select null
1641 into l_null_return
1642 from hr_s_element_classifications
1643 where classification_id = r_each_row.classification_id;
1644
1645
1646 crt_exc('Parent classification still exists in delivery tables','I');
1647
1648 -- Parent still exists, ignore this row
1649
1650 return FALSE;
1651
1652 EXCEPTION WHEN NO_DATA_FOUND THEN
1653
1654 null;
1655
1656 END;
1657
1658 BEGIN
1659
1660 -- Check that the parent exists in the live tables
1661
1662
1663 select null
1664 into l_null_return
1665 from pay_element_classifications
1666 where classification_id = r_each_row.classification_id;
1667
1668 EXCEPTION WHEN NO_DATA_FOUND THEN
1669
1670
1671 crt_exc('Parent classification does not exist in live tables','I');
1672
1673 return FALSE;
1674
1675 END;
1676
1677 -- Start 2nd parental check
1678
1679 --
1680 -- #292675. Only do the check on parent formulas if payroll
1681 -- is installed, otherwise don't bother.
1682 --
1683 IF r_each_row.formula_id is not null
1684 AND l_payroll_install_status = 'I' THEN
1685
1686 BEGIN
1687
1688 -- Check parent formula is not in the delivery tables
1689
1690 select distinct null
1691 into l_null_return
1692 from hr_s_formulas_f
1693 where formula_id = r_each_row.formula_id;
1694
1695 crt_exc('Parent formula remains in the startup tables','I');
1696
1697 return FALSE;
1698
1699 EXCEPTION WHEN NO_DATA_FOUND THEN
1700
1701 null;
1702
1703 END;
1704
1705 BEGIN
1706
1707 -- Check parent formula is present in the live tables
1708
1709 select distinct null
1710 into l_null_Return
1711 from ff_formulas_f
1712 where formula_id = r_each_row.formula_id;
1713
1714 EXCEPTION WHEN NO_DATA_FOUND THEN
1715
1716 crt_exc('Parent formula does not exist in live tables','I');
1717 return FALSE;
1718
1719 END;
1720
1721 END IF;
1722
1723 -- Start 3rd parental check
1724
1725 IF r_each_row.benefit_classification_id is not null THEN
1726
1727 BEGIN
1728
1729 -- Check parent ben class is not in the delivery tables
1730
1731 select null
1732 into l_null_return
1733 from hr_s_benefit_classifications
1734 where r_each_row.benefit_classification_id=benefit_classification_id;
1735
1736 crt_exc('Parent benefit class remains in startup tables','I');
1737
1738 return FALSE;
1739
1740 EXCEPTION WHEN NO_DATA_FOUND THEN
1741
1742 null;
1743
1744 END;
1745
1746 BEGIN
1747
1748 -- Check parent ben class is present in the live tables
1749
1750 select null
1751 into l_null_return
1752 from ben_benefit_classifications
1753 where r_each_row.benefit_classification_id=benefit_classification_id;
1754
1755 EXCEPTION WHEN NO_DATA_FOUND THEN
1756
1757 crt_exc('Parent benefit class not in live tables','I');
1758
1759 return FALSE;
1760
1761 END;
1762
1763 END IF;
1764
1765 -- Logic drops through to this statement
1766
1767 return TRUE;
1768
1769 END check_parents;
1770
1771 FUNCTION valid_ownership RETURN BOOLEAN
1772 ---------------------------------------
1773 IS
1774 -- Test ownership of this current row
1775 --
1776 -- Order changed as part of bugfix 555175:
1777 -- This function is split into three distinct parts.
1778 -- The first check examines if this data is actually required
1779 -- for a given install by examining the product installation
1780 -- table, and the ownership details for this row.
1781 -- The next checks to see if a row exists with the same primary
1782 -- key, for a business group that would have access to the
1783 -- delivered row. The last checks details for data created in
1784 -- other legislations, in case data is either created with a null
1785 -- legislation or the delivered row has a null legislation.
1786
1787 -- A return code of TRUE indicates that the row is required.
1788
1789 CURSOR element_clash
1790 IS
1791 -- Cursor to fetch elements with same name
1792
1793 select /*+ INDEX_FFS(pe) */ business_group_id
1794 from pay_element_types_f pe
1795 where business_group_id is not null
1796 and replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
1797 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
1798
1799
1800 BEGIN
1801
1802
1803 -- Bugfix 555175: This used to be the last of the 3 checks performed.
1804 -- Now moved to be the first - if the product which requires this row
1805 -- is not even installed then it is OK for the primary key to already
1806 -- exist in the users tables.
1807 -- The check examines the product installation table, and the
1808 -- ownership details for the delivered row. By examining these
1809 -- tables the row is either deleted or not. If the delivered row
1810 -- is 'stamped' with a legislation subgroup, then a check must be
1811 -- made to see if that subgroup is active or not. This check only
1812 -- needs to be performed in phase 1, since once this decision is
1813 -- made, it is pointless to perform this logic again.
1814
1815 -- An exception is raised if no rows are returned in this select
1816 -- statement. If no rows are returned then one of the following
1817 -- is true:
1818 -- 1. No ownership parameters are defined.
1819 -- 2. The products, for which owning parameters are defined, are
1820 -- not installed with as status of 'I'.
1821 -- 3. The data is defined for a legislation subgroup that is not
1822 -- active.
1823
1824 BEGIN
1825
1826 IF p_phase = 1 THEN
1827 --
1828 --if exception raised then this row is not needed
1829 if (r_distinct.legislation_subgroup is null) then
1830 select distinct null
1831 into l_null_Return
1832 from dual
1833 where exists (
1834 select null
1835 from hr_s_application_ownerships a
1836 , fnd_product_installations b
1837 , fnd_application c
1838 where a.key_name = 'ELEMENT_TYPE_ID'
1839 and a.key_value = r_distinct.c_surrogate_key
1840 and a.product_name = c.application_short_name
1841 and c.application_id = b.application_id
1842 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1843 or
1844 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1845 else
1846 select distinct null
1847 into l_null_Return
1848 from dual
1849 where exists (
1850 select null
1851 from hr_s_application_ownerships a
1852 , fnd_product_installations b
1853 , fnd_application c
1854 where a.key_name = 'ELEMENT_TYPE_ID'
1855 and a.key_value = r_distinct.c_surrogate_key
1856 and a.product_name = c.application_short_name
1857 and c.application_id = b.application_id
1858 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1859 or
1860 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
1861 and exists
1862 (select null
1863 from hr_legislation_subgroups d
1864 where d.legislation_code = r_distinct.legislation_code
1865 and d.legislation_subgroup =
1866 r_distinct.legislation_subgroup
1867 and d.active_inactive_flag = 'A'
1868 );
1869 end if;
1870 END IF;
1871
1872
1873 EXCEPTION WHEN NO_DATA_FOUND THEN
1874
1875 -- Row not needed for any installed product
1876
1877
1878 remove(r_distinct.c_surrogate_key);
1879
1880 -- Indicate row not needed
1881
1882 return FALSE;
1883
1884 END;
1885
1886
1887 --
1888 -- Following checks only need to be made if the element type
1889 -- is a new one and hence doesn't exist yet.
1890 -- If it does already exist theres no point looking for potential
1891 -- clashes with existing data!
1892 --
1893 if r_distinct.new_element_type_flag = 'Y' then
1894
1895 -- Perform a check to see if the primary key has been created within
1896 -- a visible business group. Ie: the business group is for the same
1897 -- legislation as the delivered row, or the delivered row has a null
1898 -- legislation. If no rows are returned then the primary key has not
1899 -- already been created by a user.
1900
1901 if r_distinct.legislation_code is null then
1902
1903 BEGIN
1904
1905
1906 select distinct null
1907 into l_null_return
1908 from pay_element_types_f a
1909 where a.business_group_id is not null
1910 and replace(ltrim(rtrim(upper(a.element_name))), ' ', '_') =
1911 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
1912
1913
1914 crt_exc('Row already created in a business group','I');
1915
1916 -- Indicate this row is not to be transferred
1917
1918 return FALSE;
1919
1920 EXCEPTION WHEN NO_DATA_FOUND THEN
1921
1922 null;
1923
1924 END;
1925
1926 else
1927
1928 for elts in element_clash loop
1929
1930 BEGIN
1931
1932
1933 select distinct null
1934 into l_null_return
1935 from per_business_groups pbg
1936 where pbg.business_group_id = elts.business_group_id
1937 and pbg.legislation_code = r_distinct.legislation_code;
1938
1939
1940 crt_exc('Row already created in a business group','I');
1941
1942 -- Indicate this row is not to be transferred
1943
1944 return FALSE;
1945
1946 EXCEPTION WHEN NO_DATA_FOUND THEN
1947
1948 null;
1949
1950 END;
1951
1952 end loop;
1953
1954 end if;
1955
1956 -- Now perform a check to see if this primary key has been installed
1957 -- with a legislation code that would make it visible at the same time
1958 -- as this row. Ie: if any legislation code is null within the set of
1959 -- returned rows, then the transfer may not go ahead. If no rows are
1960 -- returned then the delivered row is fine.
1961 -- G1746. Add the check for business_group_id is null, otherwise the
1962 -- row may be wrongly rejected because it already exists for a
1963 -- specific business group in another legislation. This, though
1964 -- unlikely, is permissible. RMF 05.01.95.
1965
1966 BEGIN
1967
1968
1969 select distinct null
1970 into l_null_return
1971 from pay_element_types_f
1972 where element_name = r_distinct.c_true_key
1973 and nvl (legislation_code, 'x') <>
1974 nvl (r_distinct.legislation_code, 'x')
1975 and (legislation_code is null
1976 or r_distinct.legislation_code is null )
1977 and business_group_id is null;
1978
1979
1980 crt_exc('Row already created for a visible legislation','I');
1981
1982 -- Indicate this row is not to be transferred
1983
1984 return FALSE;
1985
1986 EXCEPTION WHEN NO_DATA_FOUND THEN
1987
1988 return TRUE;
1989
1990 END;
1991
1992 --PSEUDO
1993 -- Check to see if this will clash with pseudo seeded data only if enabled
1994 IF hr_legislation.g_pseudo_enabled = 'Y' THEN
1995 -- PSEUDO CHECK
1996 -- Check if we have a pseudo seed clash if enabled
1997 -- Error handling is done in the procedure itself
1998 hr_legislation.PSEUDO_SEED_DATA_CLASH(P_SURROGATE_KEY_NAME => r_distinct.c_true_key,
1999 P_TABLE_NAME => 'PAY_ELEMENT_TYPES_F',
2000 P_LEGISLATION_CODE => r_distinct.legislation_code);
2001 END IF;
2002
2003 else
2004 return TRUE;
2005 end if;
2006
2007 END valid_ownership;
2008
2009 PROCEDURE delete_live_children
2010 ------------------------------
2011 IS
2012 -- Deletes rows from a live account in readiness for them to be installed
2013
2014 BEGIN
2015
2016 delete from pay_sub_classification_rules_f
2017 where element_type_id = r_distinct.c_surrogate_key
2018 and business_group_id is null;
2019
2020 delete from pay_formula_result_rules_f a
2021 where a.business_group_id is null
2022 and exists
2023 (select null
2024 from pay_status_processing_rules_f b
2025 where b.status_processing_rule_id = a.status_processing_rule_id
2026 and b.element_type_id = r_distinct.c_surrogate_key
2027 and b.business_group_id is null
2028 );
2029
2030 delete from pay_status_processing_rules_f
2031 where element_type_id = r_distinct.c_surrogate_key
2032 and business_Group_id is null;
2033
2034 delete from pay_element_types_f
2035 where element_type_id = r_distinct.c_surrogate_key
2036 and business_Group_id is null;
2037
2038 END delete_live_children;
2039
2040 FUNCTION install_inputs RETURN BOOLEAN
2041 --------------------------------------
2042 IS
2043 -- Install all associated input values for this element type
2044
2045 BEGIN
2046
2047 IF p_phase = 2 THEN
2048
2049 FOR i_values IN inputs(l_new_element_type_id) LOOP
2050
2051 delete from pay_input_values_f
2052 where business_group_id is null
2053 and input_value_id = i_values.input_value_id;
2054
2055 BEGIN
2056
2057 -- Get the correct value set id from FND_FLEX_VALUES_SETS
2058 -- to populate the HR_S_INPUT_VALUES_F.VALUE_SET_ID col with
2059
2060 select FLEX_VALUE_SET_ID
2061 into l_flex_value_set_id
2062 from fnd_flex_value_sets
2063 where FLEX_VALUE_SET_NAME = i_values.value_set_name;
2064
2065 EXCEPTION
2066 -- any exception will just use a null not break hrglobal
2067 when others then
2068 l_flex_value_set_id := null;
2069 END;
2070
2071 BEGIN
2072 insert into pay_input_values_f
2073 (input_value_id
2074 ,effective_start_date
2075 ,effective_end_date
2076 ,element_type_id
2077 ,lookup_type
2078 ,business_group_id
2079 ,legislation_code
2080 ,formula_id
2081 ,display_sequence
2082 ,generate_db_items_flag
2083 ,hot_default_flag
2084 ,mandatory_flag
2085 ,name
2086 ,uom
2087 ,default_value
2088 ,legislation_subgroup
2089 ,max_value
2090 ,min_value
2091 ,warning_or_error
2092 ,last_update_date
2093 ,last_updated_by
2094 ,last_update_login
2095 ,created_by
2096 ,creation_date
2097 ,value_set_id
2098 )
2099 select input_value_id
2100 ,effective_start_date
2101 ,effective_end_date
2102 ,element_type_id
2103 ,lookup_type
2104 ,business_group_id
2105 ,legislation_code
2106 ,formula_id
2107 ,display_sequence
2108 ,generate_db_items_flag
2109 ,hot_default_flag
2110 ,mandatory_flag
2111 ,name
2112 ,uom
2113 ,default_value
2114 ,legislation_subgroup
2115 ,max_value
2116 ,min_value
2117 ,warning_or_error
2118 ,last_update_date
2119 ,last_updated_by
2120 ,last_update_login
2121 ,created_by
2122 ,creation_date
2123 ,l_flex_value_set_id
2124 from hr_s_input_values_f
2125 where input_value_id = i_values.input_value_id;
2126 EXCEPTION WHEN OTHERS THEN
2127 hr_legislation.hrrunprc_trace_on;
2128 hr_utility.trace('ins pay_input_values_f');
2129 hr_utility.trace('iv id ' ||
2130 to_char(i_values.input_value_id));
2131 hr_utility.trace('iv name ' ||
2132 i_values.name);
2133 hr_legislation.hrrunprc_trace_off;
2134 raise;
2135 END;
2136
2137 delete from hr_s_input_values_f
2138 where input_value_id = i_values.input_value_id;
2139 --
2140 -- Bug 2888183 - need to insert balance feeds
2141 --
2142 if i_values.name = 'Pay Value' then
2143 --
2144 if i_values.new_input_value_flag = 'Y' then
2145 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
2146 end if;
2147 --
2148 hr_balance_feeds.ins_bf_pay_value
2149 (p_input_value_id => i_values.input_value_id
2150 ,p_mode => 'STARTUP'
2151 );
2152 --
2153 HRASSACT.CHECK_LATEST_BALANCES := TRUE;
2154 --
2155 end if;
2156 --
2157 END LOOP i_values;
2158
2159 ELSE -- phase 1
2160 -- If any input values remain, indicate to calling proc
2161
2162 select distinct null
2163 into l_null_Return
2164 from hr_s_input_values_f
2165 where element_type_id = l_new_element_type_id;
2166
2167 END IF;
2168
2169 -- If values exist, or in phase 2 return success
2170 return TRUE;
2171
2172 EXCEPTION WHEN NO_DATA_FOUND THEN
2173
2174 -- No input values exist no need to proceed
2175 return FALSE;
2176
2177 END install_inputs;
2178
2179 PROCEDURE name_integrity_checks(p_element_name varchar2)
2180 --------------------------------------------------------
2181 IS
2182 -- After all element type id's have been installed for a given element name
2183 -- check to see if any contention exists with those just installed.
2184
2185 BEGIN
2186
2187 select distinct null
2188 into l_null_return
2189 from pay_element_types_f a
2190 where a.business_Group_id is null
2191 and a.element_name = p_element_name
2192 and exists
2193 (select null
2194 from pay_element_types_f b
2195 where b.element_type_id <> a.element_Type_id
2196 and b.element_name = a.element_name
2197 and b.business_Group_id is null
2198 and b.legislation_code = a.legislation_code
2199 and a.effective_start_date between b.effective_start_date and
2200 b.effective_end_date
2201 );
2202
2203
2204 crt_exc('Installed element dates overlap','N');
2205
2206 EXCEPTION WHEN NO_DATA_FOUND THEN
2207
2208 null;
2209
2210 END name_integrity_checks;
2211
2212 FUNCTION install_element_rows RETURN BOOLEAN
2213 --------------------------------------------
2214 IS
2215 -- Function to insert date effective element rows for a given element type id
2216
2217 BEGIN
2218
2219 IF NOT valid_ownership THEN
2220 return FALSE;
2221 END IF;
2222
2223 IF p_phase = 1 THEN
2224
2225 update_uid;
2226
2227 return true;
2228
2229 ELSE
2230
2231 -- Phase = 2
2232
2233 --
2234 -- Find out if payroll is fully installed. If not, i.e. it's
2235 -- effectively an HR-only install, do not install the formula_id.
2236 -- It causes too many side-effects to deliver with HR.
2237 --
2238 SELECT status
2239 INTO l_payroll_install_status
2240 FROM fnd_product_installations
2241 WHERE application_id = 801;
2242
2243 delete_live_children;
2244
2245 FOR each_row IN c_each_element_row(r_distinct.c_surrogate_key) LOOP
2246
2247 r_each_row := each_row;
2248
2249 IF NOT check_parents THEN
2250 return FALSE;
2251 END IF;
2252
2253 --
2254 -- clear out the formula_id unless payroll is fully installed
2255 --
2256 if l_payroll_install_status = 'I' then
2257 l_formula_id := each_row.formula_id;
2258 else
2259 l_formula_id := NULL;
2260 end if;
2261
2262 BEGIN
2263 insert into pay_element_types_f
2264 (element_type_id
2265 ,effective_start_date
2266 ,effective_end_date
2267 ,business_group_id
2268 ,legislation_code
2269 ,input_currency_code
2270 ,output_currency_code
2271 ,classification_id
2272 ,benefit_classification_iD
2273 ,additional_entry_allowed_flag
2274 ,adjustment_only_flag
2275 ,closed_for_entry_flag
2276 ,element_name
2277 ,indirect_only_flag
2278 ,multiply_value_flag
2279 ,post_termination_rule
2280 ,process_in_run_flag
2281 ,processing_priority
2282 ,processing_type
2283 ,standard_link_flag
2284 ,formula_id
2285 ,comment_id
2286 ,description
2287 ,legislation_subgroup
2288 ,qualifying_age
2289 ,qualifying_length_of_service
2290 ,qualifying_units
2291 ,reporting_name
2292 ,third_party_pay_only_flag
2293 ,last_update_date
2294 ,last_updated_by
2295 ,last_update_login
2296 ,created_by
2297 ,creation_date
2298 ,multiple_entries_allowed_flag
2299 ,element_information_category
2300 ,element_information1
2301 ,element_information2
2302 ,element_information3
2303 ,element_information4
2304 ,element_information5
2305 ,element_information6
2306 ,element_information7
2307 ,element_information8
2308 ,element_information9
2309 ,element_information10
2310 ,element_information11
2311 ,element_information12
2312 ,element_information13
2313 ,element_information14
2314 ,element_information15
2315 ,element_information16
2316 ,element_information17
2317 ,element_information18
2318 ,element_information19
2319 ,element_information20
2320 ,iterative_flag
2321 ,iterative_formula_id
2322 ,iterative_priority
2323 ,retro_summ_ele_id
2324 ,grossup_flag
2325 ,process_mode
2326 ,proration_group_id
2327 ,proration_formula_id
2328 ,TIME_DEFINITION_TYPE
2329 ,TIME_DEFINITION_ID
2330 )
2331 values
2332 (each_row.element_type_id
2333 ,each_row.effective_start_date
2334 ,each_row.effective_end_date
2335 ,each_row.business_group_id
2336 ,each_row.legislation_code
2337 ,each_row.input_currency_code
2338 ,each_row.output_currency_code
2339 ,each_row.classification_id
2340 ,each_row.benefit_classification_iD
2341 ,each_row.additional_entry_allowed_flag
2342 ,each_row.adjustment_only_flag
2343 ,each_row.closed_for_entry_flag
2344 ,each_row.element_name
2345 ,each_row.indirect_only_flag
2346 ,each_row.multiply_value_flag
2347 ,each_row.post_termination_rule
2348 ,each_row.process_in_run_flag
2349 ,each_row.processing_priority
2350 ,each_row.processing_type
2351 ,each_row.standard_link_flag
2352 ,l_formula_id
2353 ,each_row.comment_id
2354 ,each_row.description
2355 ,each_row.legislation_subgroup
2356 ,each_row.qualifying_age
2357 ,each_row.qualifying_length_of_service
2358 ,each_row.qualifying_units
2359 ,each_row.reporting_name
2360 ,each_row.third_party_pay_only_flag
2361 ,each_row.last_update_date
2362 ,each_row.last_updated_by
2363 ,each_row.last_update_login
2364 ,each_row.created_by
2365 ,each_row.creation_date
2366 ,each_row.multiple_entries_allowed_flag
2367 ,each_row.element_information_category
2368 ,each_row.element_information1
2369 ,each_row.element_information2
2370 ,each_row.element_information3
2371 ,each_row.element_information4
2372 ,each_row.element_information5
2373 ,each_row.element_information6
2374 ,each_row.element_information7
2375 ,each_row.element_information8
2376 ,each_row.element_information9
2377 ,each_row.element_information10
2378 ,each_row.element_information11
2379 ,each_row.element_information12
2380 ,each_row.element_information13
2381 ,each_row.element_information14
2382 ,each_row.element_information15
2383 ,each_row.element_information16
2384 ,each_row.element_information17
2385 ,each_row.element_information18
2386 ,each_row.element_information19
2387 ,each_row.element_information20
2388 ,each_row.iterative_flag
2389 ,each_row.iterative_formula_id
2390 ,each_row.iterative_priority
2391 ,each_row.retro_summ_ele_id
2392 ,each_row.grossup_flag
2393 ,each_row.process_mode
2394 ,each_row.proration_group_id
2395 ,each_row.proration_formula_id
2396 ,each_row.TIME_DEFINITION_TYPE
2397 ,each_row.TIME_DEFINITION_ID
2398 );
2399 EXCEPTION WHEN OTHERS THEN
2400 hr_legislation.hrrunprc_trace_on;
2401 hr_utility.trace('ins pay_element_types_f');
2402 hr_utility.trace('element type name ' ||
2403 each_row.element_name);
2404 hr_utility.trace('element_type_id ' ||
2405 to_char(each_row.element_type_id));
2406 hr_utility.trace(':lc: ' || ':' ||
2407 each_row.legislation_code || ':');
2408 hr_legislation.hrrunprc_trace_off;
2409 raise;
2410 END;
2411
2412 END LOOP each_row;
2413
2414 IF NOT install_inputs THEN
2415 return FALSE;
2416 END IF;
2417
2418 BEGIN
2419
2420 -- Installation of sub class rules
2421
2422 FOR s_rules IN sub_rules(r_distinct.c_surrogate_key) LOOP
2423
2424 select null
2425 into l_null_return
2426 from pay_element_classifications
2427 where classification_id = s_rules.classification_id;
2428
2429 BEGIN
2430 insert into pay_sub_classification_rules_f
2431 (sub_classification_rule_id
2432 ,effective_start_date
2433 ,effective_end_date
2434 ,element_type_id
2435 ,classification_id
2436 ,business_group_id
2437 ,legislation_code
2438 ,last_update_date
2439 ,last_updated_by
2440 ,last_update_login
2441 ,created_by
2442 ,creation_date
2443 )
2444 values
2445 (s_rules.sub_classification_rule_id
2446 ,s_rules.effective_start_date
2447 ,s_rules.effective_end_date
2448 ,s_rules.element_type_id
2449 ,s_rules.classification_id
2450 ,s_rules.business_group_id
2451 ,s_rules.legislation_code
2452 ,s_rules.last_update_date
2453 ,s_rules.last_updated_by
2454 ,s_rules.last_update_login
2455 ,s_rules.created_by
2456 ,s_rules.creation_date
2457 );
2458 EXCEPTION WHEN OTHERS THEN
2459 hr_legislation.hrrunprc_trace_on;
2460 hr_utility.trace('ins pay_sub_classification_rules_f');
2461 hr_utility.trace('sub_classification_rule_id ' ||
2462 to_char(s_rules.sub_classification_rule_id));
2463 hr_utility.trace('element_type_id ' ||
2464 to_char(s_rules.element_type_id));
2465 hr_utility.trace('classification_id ' ||
2466 to_char(s_rules.classification_id));
2467 hr_utility.trace(':lc: ' || ':' ||
2468 s_rules.legislation_code || ':');
2469 hr_legislation.hrrunprc_trace_off;
2470 raise;
2471 END;
2472 --
2473 -- Bug 2888183 need to insert balances feeds
2474 --
2475 if (s_rules.new_sub_class_rule_flag = 'Y') then
2476 hr_balance_feeds.ins_bf_sub_class_rule
2477 (s_rules.sub_classification_rule_id
2478 ,'STARTUP');
2479 end if;
2480 --
2481 END LOOP s_rules;
2482
2483 EXCEPTION WHEN NO_DATA_FOUND THEN
2484
2485 crt_exc('Classifcation in sub class rules, not installed','I');
2486
2487 return FALSE;
2488
2489 END;
2490
2491 -- The installation of processing rules loops distinct rule_ids
2492 -- and for each id then installs each date effective row. Each date
2493 -- effective row is tested for parental data. When each date effective
2494 -- row has been installed, child formula result rules are installed.
2495
2496 BEGIN
2497
2498 -- Installation of status processing rules
2499
2500 FOR p_rules IN proc_rules(r_distinct.c_surrogate_key) LOOP
2501
2502 FOR all_rules IN all_p_rules(p_rules.s_rule_id) LOOP
2503
2504 BEGIN
2505 insert into pay_status_processing_rules_f
2506 (STATUS_PROCESSING_RULE_ID
2507 ,EFFECTIVE_START_DATE
2508 ,EFFECTIVE_END_DATE
2509 ,BUSINESS_GROUP_ID
2510 ,LEGISLATION_CODE
2511 ,ELEMENT_TYPE_ID
2512 ,ASSIGNMENT_STATUS_TYPE_ID
2513 ,FORMULA_ID
2514 ,PROCESSING_RULE
2515 ,COMMENT_ID
2516 ,LEGISLATION_SUBGROUP
2517 ,LAST_UPDATE_DATE
2518 ,LAST_UPDATED_BY
2519 ,LAST_UPDATE_LOGIN
2520 ,CREATED_BY
2521 ,CREATION_DATE
2522 )
2523 values
2524 (all_rules.STATUS_PROCESSING_RULE_ID
2525 ,all_rules.EFFECTIVE_START_DATE
2526 ,all_rules.EFFECTIVE_END_DATE
2527 ,all_rules.BUSINESS_GROUP_ID
2528 ,all_rules.LEGISLATION_CODE
2529 ,all_rules.ELEMENT_TYPE_ID
2530 ,all_rules.ASSIGNMENT_STATUS_TYPE_ID
2531 ,all_rules.FORMULA_ID
2532 ,all_rules.PROCESSING_RULE
2533 ,all_rules.COMMENT_ID
2534 ,all_rules.LEGISLATION_SUBGROUP
2535 ,all_rules.LAST_UPDATE_DATE
2536 ,all_rules.LAST_UPDATED_BY
2537 ,all_rules.LAST_UPDATE_LOGIN
2538 ,all_rules.CREATED_BY
2539 ,all_rules.CREATION_DATE
2540 );
2541 EXCEPTION WHEN OTHERS THEN
2542 hr_legislation.hrrunprc_trace_on;
2543 hr_utility.trace('ins pay_status_processing_rules_f');
2544 hr_utility.trace('STATUS_PROCESSING_RULE_ID ' ||
2545 to_char(all_rules.STATUS_PROCESSING_RULE_ID));
2546 hr_utility.trace('ELEMENT_TYPE_ID ' ||
2547 to_char(all_rules.element_type_id));
2548 hr_utility.trace('PROCESSING_RULE ' ||
2549 all_rules.PROCESSING_RULE);
2550 hr_utility.trace('ASSIGNMENT_STATUS_TYPE_ID ' ||
2551 to_char(all_rules.ASSIGNMENT_STATUS_TYPE_ID));
2552 hr_utility.trace(':lc: ' || ':' ||
2553 all_rules.legislation_code || ':');
2554 hr_legislation.hrrunprc_trace_off;
2555 raise;
2556 END;
2557
2558
2559 END LOOP all_rules;
2560
2561 FOR all_frrs IN frrs(p_rules.s_rule_id) LOOP
2562
2563 BEGIN
2564 insert into pay_formula_result_rules_f
2565 (FORMULA_RESULT_RULE_ID
2566 ,EFFECTIVE_START_DATE
2567 ,EFFECTIVE_END_DATE
2568 ,BUSINESS_GROUP_ID
2569 ,LEGISLATION_CODE
2570 ,STATUS_PROCESSING_RULE_ID
2571 ,RESULT_NAME
2572 ,RESULT_RULE_TYPE
2573 ,LEGISLATION_SUBGROUP
2574 ,SEVERITY_LEVEL
2575 ,INPUT_VALUE_ID
2576 ,ELEMENT_TYPE_ID
2577 ,LAST_UPDATE_DATE
2578 ,LAST_UPDATED_BY
2579 ,LAST_UPDATE_LOGIN
2580 ,CREATED_BY
2581 ,CREATION_DATE
2582 )
2583 values
2584 (all_frrs.FORMULA_RESULT_RULE_ID
2585 ,all_frrs.EFFECTIVE_START_DATE
2586 ,all_frrs.EFFECTIVE_END_DATE
2587 ,all_frrs.BUSINESS_GROUP_ID
2588 ,all_frrs.LEGISLATION_CODE
2589 ,all_frrs.STATUS_PROCESSING_RULE_ID
2590 ,all_frrs.RESULT_NAME
2591 ,all_frrs.RESULT_RULE_TYPE
2592 ,all_frrs.LEGISLATION_SUBGROUP
2593 ,all_frrs.SEVERITY_LEVEL
2594 ,all_frrs.INPUT_VALUE_ID
2595 ,all_frrs.ELEMENT_TYPE_ID
2596 ,all_frrs.LAST_UPDATE_DATE
2597 ,all_frrs.LAST_UPDATED_BY
2598 ,all_frrs.LAST_UPDATE_LOGIN
2599 ,all_frrs.CREATED_BY
2600 ,all_frrs.CREATION_DATE
2601 );
2602 EXCEPTION WHEN OTHERS THEN
2603 hr_legislation.hrrunprc_trace_on;
2604 hr_utility.trace('ins pay_formula_result_rules_f');
2605 hr_utility.trace('FORMULA_RESULT_RULE_ID ' ||
2606 to_char(all_frrs.FORMULA_RESULT_RULE_ID));
2607 hr_utility.trace('STATUS_PROCESSING_RULE_ID ' ||
2608 to_char(all_frrs.STATUS_PROCESSING_RULE_ID));
2609 hr_utility.trace('RESULT_NAME ' ||
2610 all_frrs.RESULT_NAME);
2611 hr_utility.trace(':lc: ' || ':' ||
2612 all_frrs.legislation_code || ':');
2613 hr_legislation.hrrunprc_trace_off;
2614 raise;
2615 END;
2616
2617 END LOOP all_frrs;
2618
2619 END LOOP p_rules; --end the distinct loop
2620
2621 EXCEPTION WHEN NO_DATA_FOUND THEN
2622
2623 crt_exc('Child status rules has parent data not installed','I');
2624
2625 return FALSE;
2626
2627 END;
2628
2629 remove(r_distinct.c_surrogate_key);
2630
2631 return TRUE;
2632
2633 END IF;
2634
2635 END install_element_rows;
2636
2637 BEGIN
2638 -- Two loops are used here. The main loop which select distinct primary
2639 -- key rows and an inner loop which selects all date effective rows for the
2640 -- primary key. The inner loop is only required in phase two, since only
2641 -- in phase 2 are rows actually transferred. The logic reads as follows:
2642
2643 -- - Only deal with rows which have correct ownership details and will
2644 -- not cause integrity problems (valid_ownership).
2645 -- - In Phase 1:
2646 -- - Delete delivery rows where the installed rows are identicle.
2647 -- - The UNION satement compares delivery rows to installed rows.
2648 -- If the sub query returns any rows, then the delivered
2649 -- tables and the installed tables are different.
2650 -- In Phase 2:
2651 -- - Delete from the installed tables using the surrogate id.
2652 -- - If an installed row is to be replaced, the values of
2653 -- the surrogate keys will be identicle at this stage.
2654 -- - Data will then be deleted from the delivery tables.
2655 -- - Call the installation procedure for any child tables, that
2656 -- must be installed within the same commit unit. If any
2657 -- errors occur then rollback to the last declared savepoint.
2658 -- - Check that all integrity rules are still obeyed at the end
2659 -- of the installation (integrity_checks).
2660
2661 -- An exception is used with this procedure 'row_in_error' in case an error
2662 -- is encountered from calling any function. If this is raised, then an
2663 -- exception is entered into the control tables (crt_exc();) and a rollback
2664 -- is performed.
2665
2666 IF p_phase = 1 THEN
2667 check_next_sequence;
2668 END IF;
2669
2670 FOR element_names IN c_distinct_name LOOP
2671
2672
2673 savepoint new_element_name;
2674
2675 FOR element_ids IN c_distinct_element(element_names.element_name) LOOP
2676
2677 savepoint new_distinct_id;
2678
2679 r_distinct := element_ids;
2680
2681 IF p_phase = 2 THEN
2682 l_new_element_type_id := r_distinct.c_surrogate_key;
2683 END IF;
2684
2685 -- Ensure both phases use the same value for the surrogate id
2686
2687 IF install_element_rows THEN
2688 integrity_checks;
2689 END IF;
2690
2691 END LOOP element_ids;
2692
2693 IF p_phase = 2 THEN
2694 name_integrity_checks(element_names.element_name);
2695 END IF;
2696
2697 END LOOP element_names;
2698
2699 END install_elements;
2700
2701 --****************************************************************************
2702 -- INSTALLATION PROCEDURE FOR : PAY_ELEMENT_SETS
2703 --****************************************************************************
2704
2705 PROCEDURE install_ele_sets(p_phase IN number)
2706 ---------------------------------------------
2707 IS
2708 l_null_return varchar2(1); -- For 'select null' statements
2709 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
2710
2711 CURSOR stu -- Selects all rows from startup entity
2712 IS
2713 select element_set_name c_true_key
2714 , element_set_id c_surrogate_key
2715 , legislation_code c_leg_code
2716 , element_set_type
2717 , last_update_date
2718 , last_updated_by
2719 , last_update_login
2720 , created_by
2721 , creation_date
2722 , rowid
2723 from hr_s_element_sets;
2724
2725 CURSOR child_type(ele_set_id number)
2726 IS
2727 -- Cursor to install child element type rules
2728
2729 select *
2730 from hr_s_element_type_rules
2731 where element_set_id = ele_set_id;
2732
2733 CURSOR child_class(ele_set_id number)
2734 IS
2735 -- Cursor to install child element classification rules
2736
2737 select *
2738 from hr_s_ele_classn_rules
2739 where element_set_id = ele_set_id;
2740
2741 stu_rec stu%ROWTYPE;
2742
2743
2744
2745 PROCEDURE check_next_sequence
2746 -----------------------------
2747 IS
2748
2749 v_sequence_number number(15);
2750 v_min_delivered number(15);
2751 v_max_delivered number(15);
2752
2753
2754 -- Surrogate id conflicts may arise from two scenario's:
2755 -- 1. Where the newly select sequence value conflicts with values
2756 -- in the STU tables.
2757 -- 2. Where selected surrogate keys, from the installed tables,
2758 -- conflict with other rows in the STU tables.
2759 --
2760 -- Both of the above scenario's are tested for.
2761 -- The first is a simple match, where if a value is detected in the
2762 -- STU tables and the installed tables then a conflict is detected. In
2763 -- This instance all STU surrogate keys, for this table, are updated.
2764 -- The second is tested for using the sequences.
2765 -- If the next value from the live sequence is within the range of
2766 -- delivered surrogate id's then the live sequence must be incremented.
2767 -- If no action is taken, then duplicates may be introduced into the
2768 -- delivered tables, and child rows may be totally invalidated.
2769
2770 BEGIN
2771
2772
2773 BEGIN --check that the installed id's will not conflict
2774 --with the delivered values
2775
2776
2777 select distinct null
2778 into l_null_return
2779 from pay_element_sets a
2780 where exists
2781 (select null
2782 from hr_s_element_sets b
2783 where a.element_set_id = b.element_set_id
2784 );
2785
2786 --conflict may exist
2787 --update all element_set_id's to remove conflict
2788
2789 update /*+NO_INDEX*/ hr_s_element_sets
2790 set element_set_id = element_set_id - 50000000;
2791
2792 update /*+NO_INDEX*/ hr_s_element_type_rules
2793 set element_set_id = element_set_id - 50000000;
2794
2795 update /*+NO_INDEX*/ hr_s_ele_classn_rules
2796 set element_set_id = element_set_id - 50000000;
2797
2798 update hr_s_application_ownerships
2799 set key_value = key_value - 50000000
2800 where key_name = 'ELEMENT_SET_ID';
2801
2802 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2803
2804 END; --check of element_set_id
2805
2806
2807
2808 select min(element_set_id) - (count(*) *3)
2809 , max(element_set_id) + (count(*) *3)
2810 into v_min_delivered
2811 , v_max_delivered
2812 from hr_s_element_sets;
2813
2814 select pay_element_sets_s.nextval
2815 into v_sequence_number
2816 from dual;
2817
2818 IF v_sequence_number
2819 BETWEEN v_min_delivered AND v_max_delivered
2820 THEN
2821
2822 hr_legislation.munge_sequence('PAY_ELEMENT_SETS_S',
2823 v_sequence_number,
2824 v_max_delivered);
2825
2826 END IF;
2827
2828 END check_next_sequence;
2829
2830
2831 PROCEDURE crt_exc (exception_type IN varchar2)
2832 ----------------------------------------------
2833 IS
2834 -- Reports any exceptions during the delivery of startup data to
2835 -- PAY_ELEMENT_SETS
2836
2837 BEGIN
2838 -- When the installation procedures encounter an error that cannot
2839 -- be handled, an exception is raised and all work is rolled back
2840 -- to the last savepoint. The installation process then continues
2841 -- with the next primary key to install. The same exception will
2842 -- not be raised more than once.
2843
2844
2845 rollback to new_element_set_name;
2846
2847 hr_legislation.insert_hr_stu_exceptions('pay_element_sets'
2848 , stu_rec.c_surrogate_key
2849 , exception_type
2850 , stu_rec.c_true_key);
2851
2852
2853 END crt_exc;
2854
2855 PROCEDURE update_uid
2856 --------------------
2857 IS
2858 -- Subprogram to update surrogate UID and all occurrences in child rows
2859
2860 BEGIN
2861
2862 BEGIN
2863
2864 select distinct element_set_id
2865 into l_new_surrogate_key
2866 from pay_element_sets
2867 where element_set_name = stu_rec.c_true_key
2868 and business_group_id is null
2869 and ( (legislation_code is null
2870 and stu_rec.c_leg_code is null)
2871 or (legislation_code = stu_rec.c_leg_code) );
2872
2873 EXCEPTION WHEN NO_DATA_FOUND THEN
2874
2875
2876 select pay_element_sets_s.nextval
2877 into l_new_surrogate_key
2878 from dual;
2879
2880 WHEN TOO_MANY_ROWS THEN
2881
2882 hr_legislation.hrrunprc_trace_on;
2883 hr_utility.trace('sel pay_element_sets TMR');
2884 hr_utility.trace('element_set_name ' ||
2885 stu_rec.c_true_key);
2886 hr_utility.trace(':lc: ' || ':' ||
2887 stu_rec.c_leg_code || ':');
2888 hr_legislation.hrrunprc_trace_off;
2889 raise;
2890 END;
2891
2892 --update all child entities
2893 update hr_s_element_sets
2894 set element_set_id = l_new_surrogate_key
2895 where element_set_id = stu_rec.c_surrogate_key;
2896
2897 update hr_s_application_ownerships
2898 set key_value = to_char(l_new_surrogate_key)
2899 where key_value = to_char(stu_rec.c_surrogate_key)
2900 and key_name = 'ELEMENT_SET_ID';
2901
2902 update hr_s_element_type_rules
2903 set element_set_id = l_new_surrogate_key
2904 where element_set_id = stu_rec.c_surrogate_key;
2905
2906 update hr_s_ele_classn_rules
2907 set element_set_id = l_new_surrogate_key
2908 where element_set_id = stu_rec.c_surrogate_key;
2909
2910 END update_uid;
2911
2912 PROCEDURE remove
2913 ----------------
2914 IS
2915 -- Remove a row from either the startup tables or the installed tables
2916
2917 v_number number;
2918
2919 BEGIN
2920
2921 delete from hr_s_element_type_rules
2922 where element_set_id = l_new_surrogate_key;
2923
2924 delete from hr_s_ele_classn_rules
2925 where element_set_id = l_new_surrogate_key;
2926
2927 delete from hr_s_element_sets
2928 where rowid = stu_rec.rowid;
2929
2930 END remove;
2931
2932 FUNCTION valid_ownership RETURN BOOLEAN
2933 ---------------------------------------
2934 IS
2935 -- Test ownership of this current row
2936
2937 BEGIN
2938
2939 -- This routine only operates in phase 1. Rows are present in the
2940 -- table hr_application_ownerships in the delivery account, which
2941 -- dictate which products a piece of data is used for. If the query
2942 -- returns a rowm then this data is required, and the function will
2943 -- return true. If no rows are returned and an exception is raised,
2944 -- then this row is not required and may be deleted from the delivery
2945 -- tables.
2946
2947 -- If legislation code and subgroup code are included on the delivery
2948 -- tables, a check must be made to determine if the data is defined for
2949 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
2950 -- installation.
2951
2952 -- A return code of TRUE indicates that thhe row is required.
2953
2954 -- The exception is raised within this procedure if no rows are returned
2955 -- in this select statement. If no rows are returned then one of the
2956 -- following is true:
2957 -- 1. No ownership parameters are defined.
2958 -- 2. The products, for which owning parameters are defined, are not
2959 -- installed with as status of 'I'.
2960 -- 3. The data is defined for a legislation subgroup that is not active.
2961
2962 IF p_phase <> 1 THEN -- Only perform in phase 1
2963 return TRUE;
2964 END IF;
2965
2966
2967 select null --if exception raised then this row is not needed
2968 into l_null_return
2969 from dual
2970 where exists
2971 (select null
2972 from hr_s_application_ownerships a
2973 , fnd_product_installations b
2974 , fnd_application c
2975 where a.key_name = 'ELEMENT_SET_ID'
2976 and a.key_value = stu_rec.c_surrogate_key
2977 and a.product_name = c.application_short_name
2978 and c.application_id = b.application_id
2979 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2980 or
2981 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2982
2983 return TRUE; --indicates row is required
2984
2985 EXCEPTION WHEN NO_DATA_FOUND THEN
2986
2987 -- Row not needed for any installed product
2988
2989 remove;
2990
2991 -- Indicates row not needed
2992
2993 return FALSE;
2994
2995 END valid_ownership;
2996
2997 PROCEDURE transfer_row
2998 ----------------------
2999 IS
3000 -- Check if a delivered row is needed and insert into the
3001 -- live tables if it is
3002
3003 v_inst_update date; -- Holds update details of installed row
3004
3005 BEGIN
3006
3007
3008 BEGIN
3009
3010 -- Perform a check to see if the primary key has been creeated within
3011 -- a visible business group. Ie: the business group is for the same
3012 -- legislation as the delivered row, or the delivered row has a null
3013 -- legislation. If no rows are returned then the primary key has not
3014 -- already been created by a user.
3015
3016 select distinct null
3017 into l_null_return
3018 from pay_element_sets a
3019 where a.element_set_name = stu_rec.c_true_key
3020 and a.business_group_id is not null
3021 and exists (select null from per_business_groups b
3022 where b.business_group_id = a.business_group_id
3023 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
3024
3025 crt_exc('Row already created in a business group');
3026
3027 -- Indicates this row is not to be transferred
3028
3029 return;
3030
3031 EXCEPTION WHEN NO_DATA_FOUND THEN
3032
3033 null;
3034
3035 END;
3036
3037
3038 -- Now perform a check to see if this primary key has been installed
3039 -- with a legislation code that would make it visible at the same time
3040 -- as this row. Ie: if any legislation code is null within the set of
3041 -- returned rows, then the transfer may not go ahead. If no rows are
3042 -- returned then the delivered row is fine.
3043 -- G1746. Add the check for business_group_id is null, otherwise the
3044 -- row may be wrongly rejected because it already exists for a
3045 -- specific business group in another legislation. This, though
3046 -- unlikely, is permissible. RMF 05.01.95.
3047
3048 BEGIN
3049 select distinct null
3050 into l_null_return
3051 from pay_element_sets
3052 where element_set_name = stu_rec.c_true_key
3053 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
3054 and (legislation_code is null
3055 or stu_rec.c_leg_code is null )
3056 and business_group_id is null;
3057
3058 crt_exc('Row already created for a visible legislation');
3059
3060 -- Indicates this row is not to be transferred
3061
3062 return;
3063
3064 EXCEPTION WHEN NO_DATA_FOUND THEN
3065
3066 null;
3067
3068 END;
3069
3070
3071 -- When the procedure is called in phase 1, there is no need to
3072 -- actually perform the transfer from the delivery tables into the
3073 -- live. Hence if phase = 1 control is returned to the calling
3074 -- procedure and the next row is returned.
3075
3076 -- If the procedure is called in phase 2, then the live row is updated
3077 -- with the values on the delivered row.
3078
3079 -- The routine check_parents validates foreign key references and
3080 -- ensures referential integrity. The routine checks to see if the
3081 -- parents of a given row have been transfered to the live tables.
3082
3083 -- This may only be called in phase two since in phase one all
3084 -- parent rows will remain in the delivery tables.
3085
3086 -- After the above checks only data that has been chanegd or is new
3087 -- will be left in the delivery tables. At this stage if the row is
3088 -- already present then it must be updated to ensure referential
3089 -- integrity. Therefore an update will be performed and if SQL%FOUND
3090 -- is FALSE an insert will be performed.
3091
3092 -- The last step of the transfer, in phase 2, is to delete the now
3093 -- transfered row from the delivery tables.
3094
3095 IF p_phase = 1 THEN
3096 return;
3097 END IF;
3098
3099 delete from pay_element_type_rules
3100 where element_set_id = l_new_surrogate_key;
3101
3102 delete from pay_ele_classification_rules
3103 where element_set_id = l_new_surrogate_key;
3104
3105 update pay_element_sets
3106 set element_set_type = stu_rec.element_set_type
3107 , last_update_date = stu_rec.last_update_date
3108 , last_updated_by = stu_rec.last_updated_by
3109 , last_update_login = stu_rec.last_update_login
3110 , created_by = stu_rec.created_by
3111 , creation_date = stu_rec.creation_date
3112 where element_set_id = stu_rec.c_surrogate_key;
3113
3114 IF NOT SQL%FOUND THEN
3115
3116
3117 BEGIN
3118 insert into pay_element_sets
3119 (element_set_name
3120 ,element_set_id
3121 ,legislation_code
3122 ,element_set_type
3123 ,last_update_date
3124 ,last_updated_by
3125 ,last_update_login
3126 ,created_by
3127 ,creation_date
3128 )
3129 values
3130 (stu_rec.c_true_key
3131 ,stu_rec.c_surrogate_key
3132 ,stu_rec.c_leg_code
3133 ,stu_rec.element_set_type
3134 ,stu_rec.last_update_date
3135 ,stu_rec.last_updated_by
3136 ,stu_rec.last_update_login
3137 ,stu_rec.created_by
3138 ,stu_rec.creation_date
3139 );
3140 EXCEPTION WHEN OTHERS THEN
3141 hr_legislation.hrrunprc_trace_on;
3142 hr_utility.trace('ins pay_element_sets');
3143 hr_utility.trace('element_set_name ' ||
3144 stu_rec.c_true_key);
3145 hr_utility.trace('element_set_id ' ||
3146 to_char(stu_rec.c_surrogate_key));
3147 hr_utility.trace('element_set_type ' ||
3148 stu_rec.element_set_type);
3149 hr_utility.trace(':lc: ' || ':' ||
3150 stu_rec.c_leg_code || ':');
3151 hr_legislation.hrrunprc_trace_off;
3152 raise;
3153 END;
3154
3155 END IF;
3156
3157 -- Now install all child element type rules
3158
3159
3160 FOR ele_types IN child_type(stu_rec.c_surrogate_key) LOOP
3161
3162 BEGIN
3163
3164
3165 select null
3166 into l_null_return
3167 from pay_element_types_f
3168 where element_type_id = ele_types.element_type_id;
3169
3170 BEGIN
3171 insert into pay_element_type_rules
3172 (element_type_id
3173 ,element_set_id
3174 ,include_or_exclude
3175 ,last_update_date
3176 ,last_updated_by
3177 ,last_update_login
3178 ,created_by
3179 ,creation_date)
3180 values
3181 (ele_types.element_Type_id
3182 ,ele_types.element_Set_id
3183 ,ele_types.include_or_exclude
3184 ,ele_types.last_update_date
3185 ,ele_types.last_updated_by
3186 ,ele_types.last_update_login
3187 ,ele_types.created_by
3188 ,ele_types.creation_date);
3189 EXCEPTION WHEN OTHERS THEN
3190 hr_legislation.hrrunprc_trace_on;
3191 hr_utility.trace('ins pay_element_type_rules');
3192 hr_utility.trace('element_type_id ' ||
3193 to_char(ele_types.element_Type_id));
3194 hr_utility.trace('element_set_id ' ||
3195 to_char(ele_types.element_Set_id));
3196 hr_legislation.hrrunprc_trace_off;
3197 raise;
3198 END;
3199
3200
3201 EXCEPTION WHEN NO_DATA_FOUND THEN
3202
3203 crt_exc('Parent element type not installed');
3204
3205 return;
3206
3207 END;
3208
3209 END LOOP;
3210
3211 -- Now install all classification rules
3212
3213
3214 FOR ele_class IN child_class(stu_Rec.c_surrogate_key) LOOP
3215
3216 BEGIN
3217
3218
3219 select null
3220 into l_null_return
3221 from pay_element_classifications
3222 where classification_id = ele_class.classification_id;
3223
3224 BEGIN
3225 insert into pay_ele_classification_rules
3226 (element_set_id
3227 ,classification_id
3228 ,last_update_date
3229 ,last_updated_by
3230 ,last_update_login
3231 ,created_by
3232 ,creation_date)
3233 values
3234 (ele_class.element_set_id
3235 ,ele_class.classification_id
3236 ,ele_class.last_update_date
3237 ,ele_class.last_updated_by
3238 ,ele_class.last_update_login
3239 ,ele_class.created_by
3240 ,ele_class.creation_date);
3241 EXCEPTION WHEN OTHERS THEN
3242 hr_legislation.hrrunprc_trace_on;
3243 hr_utility.trace('ins pay_ele_classification_rules');
3244 hr_utility.trace('element_set_id ' ||
3245 to_char(ele_class.element_set_id));
3246 hr_utility.trace('classification_id ' ||
3247 to_char(ele_class.classification_id));
3248 hr_legislation.hrrunprc_trace_off;
3249 raise;
3250 END;
3251
3252 EXCEPTION WHEN NO_DATA_FOUND THEN
3253
3254 crt_exc('Parent classification not installed');
3255
3256 return;
3257
3258 END;
3259
3260 END LOOP;
3261
3262
3263 remove;
3264
3265 END transfer_row;
3266
3267 BEGIN
3268
3269 -- This is the main loop to perform the installation logic. A cursor
3270 -- is opened to control the loop, and each row returned is placed
3271 -- into a record defined within the main procedure so each sub
3272 -- procedure has full access to all returrned columns. For each
3273 -- new row returned, a new savepoint is declared. If at any time
3274 -- the row is in error a rollback iss performed to the savepoint
3275 -- and the next row is returned. Ownership details are checked and
3276 -- if the row is required then the surrogate id is updated and the
3277 -- main transfer logic is called.
3278
3279 IF p_phase = 1 THEN
3280 check_next_sequence;
3281 END IF;
3282
3283 FOR delivered IN stu LOOP
3284
3285 -- Uses main cursor stu to impilicity define a record
3286
3287
3288 savepoint new_element_set_name;
3289
3290 stu_rec := delivered;
3291
3292 IF p_phase = 2 THEN
3293 l_new_surrogate_key := stu_rec.c_surrogate_key;
3294 END IF;
3295
3296 IF valid_ownership THEN
3297
3298 -- Test the row onerships for the current row
3299
3300
3301 IF p_phase = 1 THEN
3302 update_uid;
3303 END IF;
3304
3305 transfer_row;
3306
3307 END IF;
3308
3309 END LOOP;
3310
3311 END install_ele_sets;
3312
3313 --****************************************************************************
3314 -- INSTALLATION PROCEDURE FOR : PAY_USER_TABLES
3315 --****************************************************************************
3316
3317 PROCEDURE install_utables(p_phase IN number)
3318 --------------------------------------------
3319 IS
3320 -- Install procedure to transfer startup element classifications into
3321 -- a live account.
3322
3323 l_null_return varchar2(1); -- For 'select null' statements
3324 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3325
3326 CURSOR stu -- Selects all rows from startup entity
3327 IS
3328 select user_table_id c_surrogate_key
3329 , business_group_id
3330 , legislation_code c_leg_code
3331 , range_or_match
3332 , user_key_units
3333 , user_table_name c_true_key
3334 , legislation_subgroup c_leg_sgrp
3335 , last_update_date
3336 , last_updated_by
3337 , last_update_login
3338 , created_by
3339 , creation_date
3340 , rowid
3341 , user_row_title
3342 from hr_s_user_tables;
3343
3344 stu_rec stu%ROWTYPE;
3345
3346
3347 PROCEDURE check_next_sequence
3348 -----------------------------
3349 IS
3350
3351 v_sequence_number number(15);
3352 v_min_delivered number(15);
3353 v_max_delivered number(15);
3354
3355 -- Surrogate id conflicts may arise from two scenario's:
3356 -- 1. Where the newly select sequence value conflicts with values
3357 -- in the STU tables.
3358 -- 2. Where selected surrogate keys, from the installed tables,
3359 -- conflict with other rows in the STU tables.
3360 --
3361 -- Both of the above scenario's are tested for.
3362 -- The first is a simple match, where if a value is detected in the
3363 -- STU tables and the installed tables then a conflict is detected. In
3364 -- This instance all STU surrogate keys, for this table, are updated.
3365 -- The second is tested for using the sequences.
3366 -- If the next value from the live sequence is within the range of
3367 -- delivered surrogate id's then the live sequence must be incremented.
3368 -- If no action is taken, then duplicates may be introduced into the
3369 -- delivered tables, and child rows may be totally invalidated.
3370
3371 BEGIN
3372
3373
3374 BEGIN --check that the installed id's will not conflict
3375 --with the delivered values
3376
3377
3378 select distinct null
3379 into l_null_return
3380 from pay_user_tables a
3381 where exists
3382 (select null
3383 from hr_s_user_tables b
3384 where a.user_table_id = b.user_table_id
3385 );
3386
3387 --conflict may exist
3388 --update all user_table_id's to remove conflict
3389
3390 update /*+NO_INDEX*/ hr_s_user_columns
3391 set user_table_id = user_table_id - 50000000;
3392
3393 update /*+NO_INDEX*/ hr_s_user_rows_f
3394 set user_table_id = user_table_id - 50000000;
3395
3396 update /*+NO_INDEX*/ hr_s_user_tables
3397 set user_table_id = user_table_id - 50000000;
3398
3399 update hr_s_application_ownerships
3400 set key_value = key_value - 50000000
3401 where key_name = 'USER_TABLE_ID';
3402
3403 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3404
3405 END; --check of user_table_id
3406
3407
3408
3409 select min(user_table_id) - (count(*) *3)
3410 , max(user_table_id) + (count(*) *3)
3411 into v_min_delivered
3412 , v_max_delivered
3413 from hr_s_user_tables;
3414
3415 select pay_user_tables_s.nextval
3416 into v_sequence_number
3417 from dual;
3418
3419 IF v_sequence_number
3420 BETWEEN v_min_delivered AND v_max_delivered
3421 THEN
3422
3423 hr_legislation.munge_sequence('PAY_USER_TABLES_S',
3424 v_sequence_number,
3425 v_max_delivered);
3426
3427 END IF;
3428
3429 END check_next_sequence;
3430
3431
3432 PROCEDURE crt_exc (exception_type IN varchar2)
3433 ----------------------------------------------
3434 IS
3435 -- Reports any exceptions during the delivery of startup data to
3436 -- PAY_USER_TABLES
3437
3438 BEGIN
3439 -- When the installation procedures encounter an error that cannot
3440 -- be handled, an exception is raised and all work is rolled back
3441 -- to the last savepoint. The installation process then continues
3442 -- with the next primary key to install. The same exception will
3443 -- not be raised more than once.
3444
3445
3446 rollback to new_user_table_name;
3447
3448 hr_legislation.insert_hr_stu_exceptions('pay_user_tables'
3449 , stu_rec.c_surrogate_key
3450 , exception_type
3451 , stu_rec.c_true_key);
3452
3453
3454 END crt_exc;
3455
3456 PROCEDURE update_uid
3457 --------------------
3458 IS
3459 -- Subprogram to update surrogate UID and all occurrences in child rows
3460
3461 BEGIN
3462
3463
3464 BEGIN
3465
3466 select distinct user_table_id
3467 into l_new_surrogate_key
3468 from pay_user_tables
3469 where user_table_name = stu_rec.c_true_key
3470 and business_group_id is null
3471 and ( (legislation_code is null
3472 and stu_rec.c_leg_code is null)
3473 or (legislation_code = stu_rec.c_leg_code) );
3474
3475 EXCEPTION WHEN NO_DATA_FOUND THEN
3476
3477
3478 select pay_user_tables_s.nextval
3479 into l_new_surrogate_key
3480 from dual;
3481
3482 WHEN TOO_MANY_ROWS THEN
3483 hr_legislation.hrrunprc_trace_on;
3484 hr_utility.trace('sel pay_user_tables TMR');
3485 hr_utility.trace('user_table_name ' ||
3486 stu_rec.c_true_key);
3487 hr_utility.trace(':lc: ' || ':' ||
3488 stu_rec.c_leg_code || ':');
3489 hr_legislation.hrrunprc_trace_off;
3490 raise;
3491
3492 END;
3493
3494 -- Update all child entities
3495 update hr_s_user_tables
3496 set user_table_id = l_new_surrogate_key
3497 where user_table_id = stu_rec.c_surrogate_key;
3498
3499 update hr_s_application_ownerships
3500 set key_value = to_char(l_new_surrogate_key)
3501 where key_value = to_char(stu_rec.c_surrogate_key)
3502 and key_name = 'USER_TABLE_ID';
3503
3504 update hr_s_user_columns
3505 set user_table_id = l_new_surrogate_key
3506 where user_table_id = stu_rec.c_surrogate_key;
3507
3508 update hr_s_user_rows_f
3509 set user_table_id = l_new_surrogate_key
3510 where user_table_id = stu_rec.c_surrogate_key;
3511
3512 END update_uid;
3513
3514 PROCEDURE remove
3515 ----------------
3516 IS
3517 -- Remove a row from either the startup tables or the installed tables
3518
3519 BEGIN
3520
3521 delete from hr_s_user_tables
3522 where rowid = stu_rec.rowid;
3523
3524 END remove;
3525
3526 FUNCTION valid_ownership RETURN BOOLEAN
3527 IS
3528 -- Test ownership of this current row
3529
3530 BEGIN
3531
3532 -- This routine only operates in phase 1. Rows are present in the
3533 -- table hr_application_ownerships in the delivery account, which
3534 -- dictate which products a piece of data is used for. If the query
3535 -- returns a rowm then this data is required, and the function will
3536 -- return true. If no rows are returned and an exception is raised,
3537 -- then this row is not required and may be deleted from the delivery
3538 -- tables.
3539
3540 -- If legislation code and subgroup code are included on the delivery
3541 -- tables, a check must be made to determine if the data is defined for
3542 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
3543 -- installation.
3544
3545 -- A return code of TRUE indicates that thhe row is required.
3546
3547 -- The exception is raised within this procedure if no rows are returned
3548 -- in this select statement. If no rows are returned then one of the
3549 -- following is true:
3550 -- 1. No ownership parameters are defined.
3551 -- 2. The products, for which owning parameters are defined, are not
3552 -- installed with as status of 'I'.
3553 -- 3. The data is defined for a legislation subgroup that is not active.
3554
3555 IF p_phase <> 1 THEN -- Only perform in phase 1
3556 return TRUE;
3557 END IF;
3558
3559
3560 -- If exception raised below then this row is not needed
3561 if (stu_rec.c_leg_sgrp is null) then
3562 select null
3563 into l_null_return
3564 from dual
3565 where exists
3566 (select null
3567 from hr_s_application_ownerships a
3568 , fnd_product_installations b
3569 , fnd_application c
3570 where a.key_name = 'USER_TABLE_ID'
3571 and a.key_value = stu_rec.c_surrogate_key
3572 and a.product_name = c.application_short_name
3573 and c.application_id = b.application_id
3574 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3575 or
3576 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
3577 else
3578 select null
3579 into l_null_return
3580 from dual
3581 where exists
3582 (select null
3583 from hr_s_application_ownerships a
3584 , fnd_product_installations b
3585 , fnd_application c
3586 where a.key_name = 'USER_TABLE_ID'
3587 and a.key_value = stu_rec.c_surrogate_key
3588 and a.product_name = c.application_short_name
3589 and c.application_id = b.application_id
3590 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3591 or
3592 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
3593 and exists (select null from hr_legislation_subgroups d
3594 where d.legislation_code = stu_rec.c_leg_code
3595 and d.legislation_subgroup = stu_rec.c_leg_sgrp
3596 and d.active_inactive_flag = 'A'
3597 );
3598 end if;
3599
3600 return TRUE; --indicates row is required
3601
3602 EXCEPTION WHEN NO_DATA_FOUND THEN
3603
3604 -- Row not needed for any installed product
3605
3606
3607 remove;
3608
3609 -- Indicate row not needed
3610
3611 return FALSE;
3612
3613 END valid_ownership;
3614
3615 PROCEDURE transfer_row
3616 ----------------------
3617 IS
3618 -- Check if a delivered row is needed and insert into the
3619 -- live tables if it is
3620
3621 v_inst_update date; -- Holds update details of installed row
3622
3623 BEGIN
3624
3625
3626 BEGIN
3627
3628 -- Perform a check to see if the primary key has been creeated within
3629 -- a visible business group. Ie: the business group is for the same
3630 -- legislation as the delivered row, or the delivered row has a null
3631 -- legislation. If no rows are returned then the primary key has not
3632 -- already been created by a user.
3633
3634 select distinct null
3635 into l_null_return
3636 from pay_user_tables a
3637 where a.user_table_name = stu_rec.c_true_key
3638 and a.business_group_id is not null
3639 and exists (select null from per_business_groups b
3640 where b.business_group_id = a.business_group_id
3641 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
3642
3643 crt_exc('Row already created in a business group');
3644
3645 return;
3646
3647 EXCEPTION WHEN NO_DATA_FOUND THEN
3648
3649 null;
3650
3651 END;
3652
3653
3654 -- Now perform a check to see if this primary key has been installed
3655 -- with a legislation code that would make it visible at the same time
3656 -- as this row. Ie: if any legislation code is null within the set of
3657 -- returned rows, then the transfer may not go ahead. If no rows are
3658 -- returned then the delivered row is fine.
3659 -- G1746. Add the check for business_group_id is null, otherwise the
3660 -- row may be wrongly rejected because it already exists for a
3661 -- specific business group in another legislation. This, though
3662 -- unlikely, is permissible. RMF 05.01.95.
3663
3664 BEGIN
3665
3666 select distinct null
3667 into l_null_return
3668 from pay_user_tables
3669 where user_table_name = stu_rec.c_true_key
3670 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
3671 and (legislation_code is null or stu_rec.c_leg_code is null )
3672 and business_group_id is null;
3673
3674 crt_exc('Row already created for a visible legislation');
3675
3676 return;
3677
3678 EXCEPTION WHEN NO_DATA_FOUND THEN
3679
3680 null;
3681
3682 END;
3683
3684
3685 -- When the procedure is called in phase 1, there is no need to
3686 -- actually perform the transfer from the delivery tables into the
3687 -- live. Hence if phase = 1 control is returned to the calling
3688 -- procedure and the next row is returned.
3689
3690 -- If the procedure is called in phase 2, then the live row is updated
3691 -- with the values on the delivered row.
3692
3693 -- The routine check_parents validates foreign key references and
3694 -- ensures referential integrity. The routine checks to see if the
3695 -- parents of a given row have been transfered to the live tables.
3696
3697 -- This may only be called in phase two since in phase one all
3698 -- parent rows will remain in the delivery tables.
3699
3700 -- After the above checks only data that has been chanegd or is new
3701 -- will be left in the delivery tables. At this stage if the row is
3702 -- already present then it must be updated to ensure referential
3703 -- integrity. Therefore an update will be performed and if SQL%FOUND
3704 -- is FALSE an insert will be performed.
3705
3706 -- The last step of the transfer, in phase 2, is to delete the now
3707 -- transfered row from the delivery tables.
3708
3709 IF p_phase = 1 THEN
3710 return;
3711 END IF;
3712
3713
3714 update pay_user_tables
3715 set range_or_match = stu_rec.range_or_match
3716 , user_key_units = stu_rec.user_key_units
3717 , last_update_date = stu_rec.last_update_date
3718 , last_updated_by = stu_rec.last_updated_by
3719 , last_update_login = stu_rec.last_update_login
3720 , created_by = stu_rec.created_by
3721 , creation_date = stu_rec.creation_date
3722 , user_row_title = stu_rec.user_row_title
3723 where user_table_id = stu_rec.c_surrogate_key;
3724
3725 IF NOT SQL%FOUND THEN
3726
3727 BEGIN
3728 insert into pay_user_tables
3729 (user_table_id
3730 ,business_group_id
3731 ,legislation_code
3732 ,range_or_match
3733 ,user_key_units
3734 ,user_table_name
3735 ,legislation_subgroup
3736 ,last_update_date
3737 ,last_updated_by
3738 ,last_update_login
3739 ,created_by
3740 ,creation_date
3741 ,user_row_title
3742 )
3743 values
3744 (stu_rec.c_surrogate_key
3745 ,stu_rec.business_group_id
3746 ,stu_rec.c_leg_code
3747 ,stu_rec.range_or_match
3748 ,stu_rec.user_key_units
3749 ,stu_rec.c_true_key
3750 ,stu_rec.c_leg_sgrp
3751 ,stu_rec.last_update_date
3752 ,stu_rec.last_updated_by
3753 ,stu_rec.last_update_login
3754 ,stu_rec.created_by
3755 ,stu_rec.creation_date
3756 ,stu_rec.user_row_title
3757 );
3758 EXCEPTION WHEN OTHERS THEN
3759 hr_legislation.hrrunprc_trace_on;
3760 hr_utility.trace('ins pay_user_tables');
3761 hr_utility.trace('user_table_id ' ||
3762 to_char(stu_rec.c_surrogate_key));
3763 hr_utility.trace('user_table_name ' ||
3764 stu_rec.c_true_key);
3765 hr_utility.trace(':lc: ' || ':' ||
3766 stu_rec.c_leg_code || ':');
3767 hr_legislation.hrrunprc_trace_off;
3768 raise;
3769 END;
3770
3771 END IF;
3772
3773
3774 remove;
3775
3776 END transfer_row;
3777
3778 BEGIN
3779
3780 -- This is the main loop to perform the installation logic. A cursor
3781 -- is opened to control the loop, and each row returned is placed
3782 -- into a record defined within the main procedure so each sub
3783 -- procedure has full access to all returrned columns. For each
3784 -- new row returned, a new savepoint is declared. If at any time
3785 -- the row is in error a rollback iss performed to the savepoint
3786 -- and the next row is returned. Ownership details are checked and
3787 -- if the row is required then the surrogate id is updated and the
3788 -- main transfer logic is called.
3789
3790 IF p_phase = 1 THEN
3791 check_next_sequence;
3792 END IF;
3793
3794 FOR delivered IN stu LOOP
3795
3796 -- Uses main cursor stu to impilicity define a record
3797
3798
3799 savepoint new_user_table_name;
3800
3801 stu_rec := delivered;
3802
3803 IF p_phase = 2 THEN
3804 l_new_surrogate_key := stu_rec.c_surrogate_key;
3805 END IF;
3806
3807 IF valid_ownership THEN
3808
3809 -- Test the row onerships for the current row
3810
3811
3812 IF p_phase = 1 THEN
3813 update_uid;
3814 END IF;
3815
3816 transfer_row;
3817
3818 END IF;
3819
3820 END LOOP;
3821
3822 END install_utables;
3823
3824 --****************************************************************************
3825 -- INSTALLATION PROCEDURE FOR : PAY_USER_COLUMNS
3826 --****************************************************************************
3827
3828 PROCEDURE install_ucolumns (p_phase IN number)
3829 ----------------------------------------------
3830 IS
3831 -- Install procedure to transfer startup element classifications into
3832 -- a live account.
3833
3834 l_null_return varchar2(1); -- For 'select null' statements
3835 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3836
3837
3838 CURSOR stu -- Selects all rows from startup entity
3839 IS
3840 --
3841 -- #271139 - note that the user column name is not the true key on
3842 -- its own; it's only unique for the user table.
3843 -- Must use the user table id in select criteria for the true key.
3844 --
3845 select user_column_id c_surrogate_key
3846 , business_group_id
3847 , legislation_code c_leg_code
3848 , user_table_id
3849 , formula_id
3850 , user_column_name c_true_key
3851 , legislation_subgroup c_leg_sgrp
3852 , last_update_date
3853 , last_updated_by
3854 , last_update_login
3855 , created_by
3856 , creation_date
3857 , rowid
3858 from hr_s_user_columns;
3859
3860 stu_rec stu%ROWTYPE;
3861
3862
3863 PROCEDURE check_next_sequence
3864 -----------------------------
3865 IS
3866
3867 v_sequence_number number(15);
3868 v_min_delivered number(15);
3869 v_max_delivered number(15);
3870
3871 -- Surrogate id conflicts may arise from two scenario's:
3872 -- 1. Where the newly select sequence value conflicts with values
3873 -- in the STU tables.
3874 -- 2. Where selected surrogate keys, from the installed tables,
3875 -- conflict with other rows in the STU tables.
3876 --
3877 -- Both of the above scenario's are tested for.
3878 -- The first is a simple match, where if a value is detected in the
3879 -- STU tables and the installed tables then a conflict is detected. In
3880 -- This instance all STU surrogate keys, for this table, are updated.
3881 -- The second is tested for using the sequences.
3882 -- If the next value from the live sequence is within the range of
3883 -- delivered surrogate id's then the live sequence must be incremented.
3884 -- If no action is taken, then duplicates may be introduced into the
3885 -- delivered tables, and child rows may be totally invalidated.
3886
3887 BEGIN
3888
3889
3890 BEGIN --check that the installed id's will not conflict
3891 --with the delivered values
3892
3893
3894 select distinct null
3895 into l_null_return
3896 from pay_user_columns a
3897 where exists
3898 (select null
3899 from hr_s_user_columns b
3900 where a.user_column_id = b.user_column_id
3901 );
3902
3903 --conflict may exist
3904 --update all user_column_id's to remove conflict
3905
3906 update /*+NO_INDEX*/ hr_s_user_columns
3907 set user_column_id = user_column_id - 50000000;
3908
3909 update /*+NO_INDEX*/ hr_s_user_column_instances_f
3910 set user_column_id = user_column_id - 50000000;
3911
3912 update hr_s_application_ownerships
3913 set key_value = key_value - 50000000
3914 where key_name = 'USER_COLUMN_ID';
3915
3916 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3917
3918 END; --check of user_column_id
3919
3920
3921
3922 select min(user_column_id) - (count(*) *3)
3923 , max(user_column_id) + (count(*) *3)
3924 into v_min_delivered
3925 , v_max_delivered
3926 from hr_s_user_columns;
3927
3928 select pay_user_columns_s.nextval
3929 into v_sequence_number
3930 from dual;
3931
3932 IF v_sequence_number
3933 BETWEEN v_min_delivered AND v_max_delivered
3934 THEN
3935
3936 hr_legislation.munge_sequence('PAY_USER_COLUMNS_S',
3937 v_sequence_number,
3938 v_max_delivered);
3939
3940 END IF;
3941
3942 END check_next_sequence;
3943
3944
3945 PROCEDURE crt_exc (exception_type IN varchar2)
3946 ----------------------------------------------
3947 IS
3948 -- Reports any exceptions during the delivery of startup data to
3949 -- PAY_USER_COLUMNS
3950
3951 BEGIN
3952 -- When the installation procedures encounter an error that cannot
3953 -- be handled, an exception is raised and all work is rolled back
3954 -- to the last savepoint. The installation process then continues
3955 -- with the next primary key to install. The same exception will
3956 -- not be raised more than once.
3957
3958
3959 rollback to new_user_column_name;
3960
3961 hr_legislation.insert_hr_stu_exceptions('pay_user_columns'
3962 , stu_rec.c_surrogate_key
3963 , exception_type
3964 , stu_rec.c_true_key);
3965
3966
3967 END crt_exc;
3968
3969 PROCEDURE update_uid
3970 --------------------
3971 IS
3972 -- subprogram to update surrogate UID and all occurrences in child rows
3973
3974 BEGIN
3975
3976
3977 BEGIN
3978 --
3979 -- #271139 - hitting a problem because the user column name is
3980 -- not the true key on its own; it's only unique for the user table.
3981 -- Add the user table id to the select criteria.
3982 --
3983 select distinct user_column_id
3984 into l_new_surrogate_key
3985 from pay_user_columns
3986 where user_column_name = stu_rec.c_true_key
3987 and user_table_id = stu_rec.user_table_id
3988 and business_group_id is null
3989 and ( (legislation_code is null
3990 and stu_rec.c_leg_code is null)
3991 or (legislation_code = stu_rec.c_leg_code) );
3992
3993 EXCEPTION WHEN NO_DATA_FOUND THEN
3994
3995
3996 select pay_user_columns_s.nextval
3997 into l_new_surrogate_key
3998 from dual;
3999
4000 WHEN TOO_MANY_ROWS THEN
4001 hr_legislation.hrrunprc_trace_on;
4002 hr_utility.trace('sel pay_user_columns TMR');
4003 hr_utility.trace('user_column_name ' ||
4004 stu_rec.c_true_key);
4005 hr_utility.trace('user_table_id ' ||
4006 to_char(stu_rec.user_table_id));
4007 hr_utility.trace(':lc: ' || ':' ||
4008 stu_rec.c_leg_code || ':');
4009 hr_legislation.hrrunprc_trace_off;
4010 raise;
4011 END;
4012
4013 -- Update all child entities
4014
4015 update hr_s_user_columns
4016 set user_column_id = l_new_surrogate_key
4017 where user_column_id = stu_rec.c_surrogate_key;
4018
4019 update hr_s_application_ownerships
4020 set key_value = to_char(l_new_surrogate_key)
4021 where key_value = to_char(stu_rec.c_surrogate_key)
4022 and key_name = 'USER_COLUMN_ID';
4023
4024 update hr_s_user_column_instances_f
4025 set user_column_id = l_new_surrogate_key
4026 where user_column_id = stu_rec.c_surrogate_key;
4027
4028 END update_uid;
4029
4030 PROCEDURE remove
4031 ----------------
4032 IS
4033 -- Remove a row from either the startup tables or the installed tables
4034
4035 BEGIN
4036
4037 delete from hr_s_user_columns
4038 where rowid = stu_rec.rowid;
4039
4040 END remove;
4041
4042 FUNCTION valid_ownership RETURN BOOLEAN
4043 ---------------------------------------
4044 IS
4045 -- Test ownership of this current row
4046
4047 -- This routine only operates in phase 1. Rows are present in the
4048 -- table hr_application_ownerships in the delivery account, which
4049 -- dictate which products a piece of data is used for. If the query
4050 -- returns a rowm then this data is required, and the function will
4051 -- return true. If no rows are returned and an exception is raised,
4052 -- then this row is not required and may be deleted from the delivery
4053 -- tables.
4054
4055 -- If legislation code and subgroup code are included on the delivery
4056 -- tables, a check must be made to determine if the data is defined for
4057 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
4058 -- installation.
4059
4060 -- A return code of TRUE indicates that the row is required.
4061
4062 -- The exception is raised within this procedure if no rows are returned
4063 -- in this select statement. If no rows are returned then one of the
4064 -- following is true:
4065 -- 1. No ownership parameters are defined.
4066 -- 2. The products, for which owning parameters are defined, are not
4067 -- installed with as status of 'I'.
4068 -- 3. The data is defined for a legislation subgroup that is not active.
4069
4070 BEGIN
4071
4072
4073 IF p_phase <> 1 THEN
4074 return TRUE;
4075 END IF;
4076
4077
4078 -- If exception raised below hen this row is not needed
4079 if (stu_rec.c_leg_sgrp is null) then
4080 select null
4081 into l_null_return
4082 from dual
4083 where exists
4084 (select null
4085 from hr_s_application_ownerships a
4086 , fnd_product_installations b
4087 , fnd_application c
4088 where a.key_name = 'USER_COLUMN_ID'
4089 and a.key_value = stu_rec.c_surrogate_key
4090 and a.product_name = c.application_short_name
4091 and c.application_id = b.application_id
4092 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4093 or
4094 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4095 else
4096 select null
4097 into l_null_return
4098 from dual
4099 where exists
4100 (select null
4101 from hr_s_application_ownerships a
4102 , fnd_product_installations b
4103 , fnd_application c
4104 where a.key_name = 'USER_COLUMN_ID'
4105 and a.key_value = stu_rec.c_surrogate_key
4106 and a.product_name = c.application_short_name
4107 and c.application_id = b.application_id
4108 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4109 or
4110 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
4111 and exists (select null from hr_legislation_subgroups d
4112 where d.legislation_code = stu_rec.c_leg_code
4113 and d.legislation_subgroup = stu_rec.c_leg_sgrp
4114 and d.active_inactive_flag = 'A'
4115 );
4116 end if;
4117
4118 return TRUE; --indicates row is required
4119
4120 EXCEPTION WHEN NO_DATA_FOUND THEN
4121
4122 -- Row not needed for any installed product
4123
4124
4125 remove;
4126
4127 -- Indicates row not needed
4128
4129 return FALSE;
4130
4131 END valid_ownership;
4132
4133 FUNCTION check_parents RETURN BOOLEAN
4134 -------------------------------------
4135 IS
4136 -- Check if parent data is correct
4137
4138 BEGIN
4139
4140 -- This procedure is only called in phase 2. The logic to check if
4141 -- a given parental foreign key exists is split into two parts for
4142 -- every foriegn key. The first select from the delivery tables.
4143
4144 -- If a row is founnd then the installation of the parent must have
4145 -- failed, and this installation must not go ahead. If no data is
4146 -- found, ie: an exception is raised, the installation is valid.
4147
4148 -- The second check looks for a row in the live tables. If no rows
4149 -- are returned then this installation is invalid, since this means
4150 -- that the parent referenced by this row is not present in the
4151 -- live tables.
4152
4153 -- The distinct is used in case the parent is date effective and many rows
4154 -- may be returned by the same parent id.
4155
4156 -- Start with checking the parent PAY_USER_TABLES
4157
4158
4159 BEGIN
4160
4161 -- Check the tables in the delivery account
4162
4163 select distinct null
4164 into l_null_return
4165 from hr_s_user_tables
4166 where user_table_id = stu_rec.user_table_id;
4167
4168 crt_exc('Parent user table remains in delivery tables');
4169
4170 -- Parent row still in startup account
4171
4172 return FALSE;
4173
4174 EXCEPTION WHEN NO_DATA_FOUND THEN
4175
4176 -- Probably transferred?
4177
4178 null;
4179
4180 END;
4181
4182
4183 BEGIN
4184
4185 select null
4186 into l_null_return
4187 from pay_user_tables
4188 where user_table_id = stu_rec.user_table_id;
4189
4190 EXCEPTION WHEN NO_DATA_FOUND THEN
4191
4192 -- Parent not installed
4193
4194
4195 crt_exc('Parent user table not installed');
4196
4197 return FALSE;
4198
4199 END;
4200
4201 IF stu_rec.formula_id is null THEN
4202 -- No need to check parent formula
4203 return TRUE;
4204 END IF;
4205
4206 -- Now check the parent FF_FORMULAS_F
4207
4208
4209 BEGIN
4210
4211 -- Check the tables in the delivery account
4212
4213 select distinct null
4214 into l_null_return
4215 from hr_s_formulas_f
4216 where formula_id = stu_rec.formula_id;
4217
4218 crt_exc('Parent formula remains in delivery tables');
4219
4220 -- Parent row still in startup account
4221
4222 return FALSE;
4223
4224 EXCEPTION WHEN NO_DATA_FOUND THEN
4225
4226 -- Probably transferred?
4227
4228 null;
4229
4230 END;
4231
4232
4233 BEGIN
4234 select null
4235 into l_null_return
4236 from ff_Formulas_f
4237 where formula_id = stu_rec.formula_id;
4238 return TRUE;
4239
4240 EXCEPTION WHEN NO_DATA_FOUND THEN
4241
4242 -- Parent not installed
4243
4244
4245 crt_exc('Parent formula not installed');
4246
4247 return FALSE;
4248
4249 END;
4250
4251 END check_parents;
4252
4253 PROCEDURE transfer_row
4254 ----------------------
4255 IS
4256 -- Check if a delivered row is needed and insert into the
4257 -- live tables if it is
4258
4259 v_inst_update date; -- Holds update details of installed row
4260
4261 BEGIN
4262
4263
4264 BEGIN
4265
4266 -- Perform a check to see if the primary key has been created within
4267 -- a visible business group. Ie: the business group is for the same
4268 -- legislation as the delivered row, or the delivered row has a null
4269 -- legislation. If no rows are returned then the primary key has not
4270 -- already been created by a user.
4271 --
4272 -- #271139 - hitting a problem because the user column name is
4273 -- not the true key on its own; it's only unique for the user table.
4274 -- Add the user table id to the select criteria.
4275 --
4276 select distinct null
4277 into l_null_return
4278 from pay_user_columns a
4279 where a.user_table_id = stu_rec.user_table_id
4280 and a.user_column_name = stu_rec.c_true_key
4281 and a.business_group_id is not null
4282 and exists (select null from per_business_groups b
4283 where b.business_group_id = a.business_group_id
4284 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
4285
4286 crt_exc('Row already created in a business group');
4287
4288 -- Indicates this row is not to be transferred
4289
4290 return;
4291
4292 EXCEPTION WHEN NO_DATA_FOUND THEN
4293
4294 null;
4295
4296 END;
4297
4298
4299 -- Now perform a check to see if this primary key has been installed
4300 -- with a legislation code that would make it visible at the same time
4301 -- as this row. Ie: if any legislation code is null within the set of
4302 -- returned rows, then the transfer may not go ahead. If no rows are
4303 -- returned then the delivered row is fine.
4304 -- G1746. Add the check for business_group_id is null, otherwise the
4305 -- row may be wrongly rejected because it already exists for a
4306 -- specific business group in another legislation. This, though
4307 -- unlikely, is permissible. RMF 05.01.95.
4308 --
4309 -- #271139 - hitting a problem because the user column name is
4310 -- not the true key on its own; it's only unique for the user table.
4311 -- Add the user table id to the select criteria.
4312 --
4313 BEGIN
4314 select distinct null
4315 into l_null_return
4316 from pay_user_columns
4317 where user_column_name = stu_rec.c_true_key
4318 and user_table_id = stu_rec.user_table_id
4319 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
4320 and (legislation_code is null or stu_rec.c_leg_code is null )
4321 and business_group_id is null;
4322
4323 crt_exc('Row already created for a visible legislation');
4324
4325 -- Indicates this row is not to be transferred
4326
4327 return;
4328
4329 EXCEPTION WHEN NO_DATA_FOUND THEN
4330
4331 null;
4332
4333 END;
4334
4335
4336 -- When the procedure is called in phase 1, there is no need to
4337 -- actually perform the transfer from the delivery tables into the
4338 -- live. Hence if phase = 1 control is returned to the calling
4339 -- procedure and the next row is returned.
4340
4341 -- If the procedure is called in phase 2, then the live row is updated
4342 -- with the values on the delivered row.
4343
4344 -- The routine check_parents validates foreign key references and
4345 -- ensures referential integrity. The routine checks to see if the
4346 -- parents of a given row have been transfered to the live tables.
4347
4348 -- This may only be called in phase two since in phase one all
4349 -- parent rows will remain in the delivery tables.
4350
4351 -- After the above checks only data that has been chanegd or is new
4352 -- will be left in the delivery tables. At this stage if the row is
4353 -- already present then it must be updated to ensure referential
4354 -- integrity. Therefore an update will be performed and if SQL%FOUND
4355 -- is FALSE an insert will be performed.
4356
4357 -- The last step of the transfer, in phase 2, is to delete the now
4358 -- transfered row from the delivery tables.
4359
4360 IF p_phase = 1 THEN
4361 return;
4362 END IF;
4363
4364 IF NOT check_parents THEN
4365 return;
4366 END IF;
4367
4368
4369 update pay_user_columns
4370 set formula_id = stu_rec.formula_id
4371 , last_update_date = stu_rec.last_update_date
4372 , last_updated_by = stu_rec.last_updated_by
4373 , last_update_login = stu_rec.last_update_login
4374 , created_by = stu_rec.created_by
4375 , creation_date = stu_rec.creation_date
4376 where user_column_id = stu_rec.c_surrogate_key;
4377
4378 IF NOT SQL%FOUND THEN
4379
4380 BEGIN
4381 insert into pay_user_columns
4382 (user_column_id
4383 ,business_group_id
4384 ,legislation_code
4385 ,user_table_id
4386 ,formula_id
4387 ,user_column_name
4388 ,legislation_subgroup
4389 ,last_update_date
4390 ,last_updated_by
4391 ,last_update_login
4392 ,created_by
4393 ,creation_date
4394 )
4395 values
4396 (stu_rec.c_surrogate_key
4397 ,stu_rec.business_group_id
4398 ,stu_rec.c_leg_code
4399 ,stu_rec.user_table_id
4400 ,stu_rec.formula_id
4401 ,stu_rec.c_true_key
4402 ,stu_rec.c_leg_sgrp
4403 ,stu_rec.last_update_date
4404 ,stu_rec.last_updated_by
4405 ,stu_rec.last_update_login
4406 ,stu_rec.created_by
4407 ,stu_rec.creation_date
4408 );
4409 EXCEPTION WHEN OTHERS THEN
4410 hr_legislation.hrrunprc_trace_on;
4411 hr_utility.trace('ins pay_user_columns');
4412 hr_utility.trace('user_column_id ' ||
4413 to_char(stu_rec.c_surrogate_key));
4414 hr_utility.trace('user_column_name ' ||
4415 stu_rec.c_true_key);
4416 hr_utility.trace('user_table_id ' ||
4417 to_char(stu_rec.user_table_id));
4418 hr_utility.trace(':lc: ' || ':' ||
4419 stu_rec.c_leg_code || ':');
4420 hr_legislation.hrrunprc_trace_off;
4421 raise;
4422 END;
4423
4424 END IF;
4425
4426 remove;
4427
4428 END transfer_row;
4429
4430 BEGIN
4431 -- This is the main loop to perform the installation logic. A cursor
4432 -- is opened to control the loop, and each row returned is placed
4433 -- into a record defined within the main procedure so each sub
4434 -- procedure has full access to all returrned columns. For each
4435 -- new row returned, a new savepoint is declared. If at any time
4436 -- the row is in error a rollback iss performed to the savepoint
4437 -- and the next row is returned. Ownership details are checked and
4438 -- if the row is required then the surrogate id is updated and the
4439 -- main transfer logic is called.
4440
4441 IF p_phase = 1 THEN
4442 check_next_sequence;
4443 END IF;
4444
4445 FOR delivered IN stu LOOP
4446
4447 -- Uses main cursor stu to implicity define a record
4448
4449
4450 savepoint new_user_column_name;
4451
4452 stu_rec := delivered;
4453
4454 IF p_phase = 2 THEN
4455 l_new_surrogate_key := stu_rec.c_surrogate_key;
4456 END IF;
4457
4458 IF valid_ownership THEN
4459
4460 -- Test the row onerships for the current row
4461
4462
4463 IF p_phase = 1 THEN
4464 update_uid;
4465 END IF;
4466
4467 transfer_row;
4468
4469 END IF;
4470
4471 END LOOP;
4472
4473 END install_ucolumns;
4474
4475 --*******************************************************************
4476 -- OVERALL INSTALLATION PROCEDURE
4477 --*******************************************************************
4478
4479 PROCEDURE install(p_phase number)
4480 ---------------------------------
4481 IS
4482 -- Driver procedure to control the execution of all installation procedures.
4483
4484 BEGIN
4485
4486 IF p_phase = 1 OR p_phase =2 THEN
4487
4488 hr_legislation.hrrunprc_trace_on;
4489 hr_utility.trace('start install_ele_class: ' || to_char(p_phase));
4490 hr_legislation.hrrunprc_trace_off;
4491
4492 install_ele_class(p_phase); --install element classifications
4493
4494 hr_legislation.hrrunprc_trace_on;
4495 hr_utility.trace('start install_elements: ' || to_char(p_phase));
4496 hr_legislation.hrrunprc_trace_off;
4497
4498 install_elements(p_phase); --install elements,sprs,frrs,inputs
4499
4500 hr_legislation.hrrunprc_trace_on;
4501 hr_utility.trace('start install_ele_sets: ' || to_char(p_phase));
4502 hr_legislation.hrrunprc_trace_off;
4503
4504 install_ele_sets(p_phase); --install sets,type rules,class rules
4505
4506 hr_legislation.hrrunprc_trace_on;
4507 hr_utility.trace('start install_utables: ' || to_char(p_phase));
4508 hr_legislation.hrrunprc_trace_off;
4509
4510 install_utables(p_phase); --install user tables
4511
4512 hr_legislation.hrrunprc_trace_on;
4513 hr_utility.trace('start install_ucolumns: ' || to_char(p_phase));
4514 hr_legislation.hrrunprc_trace_off;
4515
4516 install_ucolumns(p_phase); --install user columns
4517
4518 END IF;
4519
4520 END install;
4521
4522 END hr_legislation_elements;
[Home] [Help]