1 PACKAGE body hr_legislation_elements AS
2 /* $Header: pelegele.pkb 120.8.12000000.1 2007/01/21 23:59:27 appldev 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
1184 BEGIN
1185 select distinct element_type_id
1186 into l_new_element_type_id
1187 from pay_element_types_f
1188 where replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
1189 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_')
1190 and business_Group_id is null
1191 and legislation_code = r_distinct.legislation_code;
1192
1193 v_new_element_type_flag := 'N';
1194
1195 EXCEPTION WHEN NO_DATA_FOUND THEN
1196
1197
1198 select pay_element_types_s.nextval
1199 into l_new_element_type_id
1200 from dual;
1201
1202 v_new_element_type_flag := 'Y';
1203
1204 WHEN TOO_MANY_ROWS THEN
1205 hr_legislation.hrrunprc_trace_on;
1206 hr_utility.trace('sel pay_element_types_f TMR');
1207 hr_utility.trace('element_name ' ||
1208 r_distinct.c_true_key);
1209 hr_utility.trace(':lc: ' || ':' ||
1210 r_distinct.legislation_code || ':');
1211 hr_legislation.hrrunprc_trace_off;
1212 raise;
1213 END;
1214
1215 update hr_s_element_types_f
1216 set element_type_id = l_new_element_type_id,
1217 new_element_type_flag = v_new_element_type_flag
1218 where element_type_id = r_distinct.c_surrogate_key;
1219
1220 update hr_s_element_types_f
1221 set retro_summ_ele_id = l_new_element_type_id
1222 where retro_summ_ele_id = r_distinct.c_surrogate_key;
1223
1224 update hr_s_element_type_rules
1225 set element_type_id = l_new_element_type_id
1226 where element_type_id = r_distinct.c_surrogate_key;
1227
1228 update hr_s_formula_result_rules_f
1229 set element_type_id = l_new_element_type_id
1230 where element_type_id = r_distinct.c_surrogate_key;
1231
1232 update hr_s_application_ownerships
1233 set key_value = to_char(l_new_element_type_id)
1234 where key_value = to_char(r_distinct.c_surrogate_key)
1235 and key_name = 'ELEMENT_TYPE_ID';
1236
1237 -- update the uid of associated input values
1238
1239 FOR i_vals IN inputs(r_distinct.c_surrogate_key) LOOP
1240
1241
1242 BEGIN
1243 -- Test if input value already exists
1244 -- #331823. Add 'distinct' to prevent a 'too many rows'
1245 -- error if there is more than one datetracked
1246 -- version of the input values row.
1247
1248 select distinct input_value_id
1249 into v_new_input_id
1250 from pay_input_values_f
1251 where replace(ltrim(rtrim(upper(name))), ' ', '_') =
1252 replace(ltrim(rtrim(upper(i_vals.name))), ' ', '_')
1253 and business_group_id is null
1254 and element_type_id = l_new_element_Type_id;
1255
1256 v_new_input_value_flag := 'N';
1257
1258 EXCEPTION WHEN NO_DATA_FOUND THEN
1259 -- New input value, so new get new _id
1260 select pay_input_values_s.nextval
1261 into v_new_input_id
1262 from dual;
1263
1264 v_new_input_value_flag := 'Y';
1265
1266 WHEN TOO_MANY_ROWS THEN
1267 hr_legislation.hrrunprc_trace_on;
1268 hr_utility.trace('sel pay_input_values_f TMR');
1269 hr_utility.trace('iv name ' ||
1270 i_vals.name);
1271 hr_utility.trace('element_type_id ' ||
1272 to_char(l_new_element_Type_id));
1273 hr_utility.trace('element_name ' ||
1274 r_distinct.c_true_key);
1275 hr_legislation.hrrunprc_trace_off;
1276 raise;
1277 END;
1278
1279 update hr_s_input_values_f
1280 set input_value_id = v_new_input_id
1281 , element_type_id = l_new_element_type_id
1282 , new_input_value_flag = v_new_input_value_flag
1283 where input_value_id = i_vals.input_value_id;
1284
1285 update hr_s_balance_feeds_f
1286 set input_value_id = v_new_input_id,
1287 new_input_value_flag = v_new_input_value_flag
1288 where input_value_id = i_vals.input_value_id;
1289
1290 update hr_s_formula_result_rules_f
1291 set input_value_id = v_new_input_id
1292 where input_value_id = i_vals.input_value_id;
1293
1294 update hr_s_balance_types
1295 set input_value_id = v_new_input_id
1296 where input_value_id = i_vals.input_value_id;
1297
1298 END LOOP i_vals;
1299
1300 -- Update the uid of sub classification rules
1301
1302 FOR s_class IN sub_rules(r_distinct.c_surrogate_key) LOOP
1303
1304 select pay_sub_classification_rules_s.nextval
1305 into v_new_sub_class_id
1306 from dual;
1307
1308 update hr_s_sub_classn_rules_f
1309 set sub_classification_rule_id = v_new_sub_class_id
1310 , element_type_id = l_new_element_type_id
1311 where sub_classification_rule_id = s_class.sub_classification_rule_id;
1312
1313 BEGIN
1314
1315 select sub_classification_rule_id
1316 into v_dummy
1317 from hr_s_sub_classn_rules_f hscr
1318 where hscr.sub_classification_rule_id = v_new_sub_class_id
1319 and exists
1320 ( select 1
1321 from pay_sub_classification_rules_f pscr
1322 where pscr.element_type_id = hscr.element_type_id
1323 and pscr.classification_id = hscr.classification_id
1324 and nvl(pscr.business_group_id, -1) = nvl(hscr.business_group_id, -1)
1325 and nvl(pscr.legislation_code, 'X') = nvl(hscr.legislation_code, 'X')
1326 and pscr.effective_start_date = hscr.effective_start_date
1327 and pscr.effective_end_date = hscr.effective_end_date);
1328
1329 v_new_sub_class_rule_flag := 'N';
1330
1331 EXCEPTION WHEN NO_DATA_FOUND THEN
1332
1333 v_new_sub_class_rule_flag := 'Y';
1334
1335 END;
1336
1337 update hr_s_sub_classn_rules_f
1338 set new_sub_class_rule_flag = v_new_sub_class_rule_flag
1339 where sub_classification_rule_id = s_class.sub_classification_rule_id;
1340
1341 END LOOP s_class;
1342
1343 -- update the uids of status processing rules and child result rules
1344
1345 FOR sprs IN proc_rules(r_distinct.c_surrogate_key) LOOP
1346
1347 --
1348 -- #346366. Test if status processing rule already exists
1349 -- and use its id if it does, rather than always getting
1350 -- the next number from the sequence. This means that user-entered
1351 -- formula result rules are not orphaned through the parent spr's
1352 -- id being changed.
1353 --
1354 -- 2971029
1355 -- need to consider null = null case seperately else we miss existing
1356 -- data when the assignment_status_type_id is null
1357 -- Also need the select max in case datetrack data can return more
1358 -- than 1 distinct status_processing_rule_id for a given leg_code,bg,
1359 -- assignment_status_type_id and element_type_id combination.
1360 -- Guarantee return of the ID of only the live row that has the max
1361 -- EED for this combination
1362
1363 BEGIN
1364
1365 select distinct status_processing_rule_id
1366 into v_new_spr_id
1367 from pay_status_processing_rules_f spr
1368 where spr.legislation_code = r_distinct.legislation_code
1369 and spr.business_group_id is null
1370 and spr.processing_rule = sprs.processing_rule
1371 and ((spr.assignment_status_type_id is null
1372 and
1373 sprs.assignment_status_type_id is null)
1374 or
1375 (spr.assignment_status_type_id =
1376 sprs.assignment_status_type_id))
1377 and spr.effective_end_date = (select max(spr2.effective_end_date)
1378 from pay_status_processing_rules_f spr2
1379 where spr2.element_type_id = spr.element_type_id
1380 and spr2.processing_rule = spr.processing_rule
1381 and spr2.legislation_code = r_distinct.legislation_code
1382 and spr2.business_group_id is null
1383 and ((spr.assignment_status_type_id is null
1384 and spr2.assignment_status_type_id is null)
1385 or
1386 (spr.assignment_status_type_id =
1387 spr2.assignment_status_type_id)))
1388 and element_type_id = l_new_element_type_id;
1389
1390 EXCEPTION WHEN NO_DATA_FOUND THEN
1391
1392 -- New input value, so new get new _id
1393
1394 select pay_status_processing_rules_s.nextval
1395 into v_new_spr_id
1396 from dual;
1397
1398 WHEN TOO_MANY_ROWS THEN
1399 hr_legislation.hrrunprc_trace_on;
1400 hr_utility.trace('sel pay_status_processing_rules TMR');
1401
1402 hr_utility.trace('assignment_status_type_id ' ||
1403 to_char(sprs.assignment_status_type_id));
1404 hr_utility.trace(':lc: ' || ':' ||
1405 r_distinct.legislation_code || ':');
1406 hr_legislation.hrrunprc_trace_off;
1407 raise;
1408 END;
1409
1410 update hr_s_status_processing_rules_f
1411 set status_processing_rule_id = v_new_spr_id
1412 , element_type_id = l_new_element_type_id
1413 where status_processing_rule_id = sprs.s_rule_id;
1414
1415 FOR results IN d_frrs(sprs.s_rule_id) LOOP
1416
1417 select pay_formula_Result_rules_s.nextval
1418 into v_new_frr_id
1419 from dual;
1420
1421 update hr_s_formula_result_rules_f
1422 set formula_result_rule_id = v_new_frr_id
1423 , status_processing_rule_id = v_new_spr_id
1424 where formula_result_rule_id = results.formula_result_rule_id;
1425
1426 END LOOP results;
1427
1428 END LOOP sprs;
1429
1430 END update_uid;
1431
1432 PROCEDURE integrity_checks
1433 --------------------------
1434 IS
1435 -- After all rows for a primary key have been delivered, entity specific
1436 -- checks must be performed to check to validity of the data that has
1437 -- just been installed.
1438
1439 l_iv_exists boolean;
1440 l_input_value_id pay_input_values_f.input_value_id%type;
1441
1442 BEGIN
1443
1444
1445 IF r_distinct.c_end = l_end_of_time THEN
1446 null;
1447 ELSE
1448 BEGIN
1449 -- Check balance feeds
1450 select distinct null
1451 into l_null_return
1452 from pay_balance_feeds_f a
1453 , pay_input_values_f b
1454 where b.element_type_id = l_new_element_type_id
1455 and a.input_value_id = b.input_value_id
1456 and a.effective_end_Date > r_distinct.c_end
1457 and a.business_group_id is not null;
1458
1459 crt_exc('User created balance feeds exist after the new end date','I');
1460 return;
1461
1462 EXCEPTION WHEN NO_DATA_FOUND THEN
1463
1464 -- No invalid child data
1465
1466 null;
1467
1468 END;
1469
1470 -- check element links
1471
1472 BEGIN
1473
1474 select distinct null
1475 into l_null_return
1476 from pay_element_links_f
1477 where element_type_id = l_new_element_type_id
1478 and effective_end_Date > r_distinct.c_end
1479 and business_group_id is not null;
1480
1481 crt_exc('User created element links exist after the new end date','I');
1482
1483 return;
1484
1485 EXCEPTION WHEN NO_DATA_FOUND THEN
1486
1487 null;
1488
1489 END;
1490
1491 -- Check formula result rules
1492
1493 BEGIN
1494
1495 select distinct null
1496 into l_null_return
1497 from pay_status_processing_rules_f a
1498 , pay_formula_result_rules_f b
1499 where a.element_type_id = l_new_element_type_id
1500 and b.status_processing_rule_id = a.status_processing_rule_id
1501 and b.effective_end_Date > r_distinct.c_end
1502 and b.business_group_id is not null;
1503
1504 crt_exc('User created formula rules exist after the new end date','I');
1505
1506 return;
1507
1508 EXCEPTION WHEN NO_DATA_FOUND THEN
1509
1510 null;
1511
1512 END;
1513
1514 -- Check payroll run results
1515
1516 BEGIN
1517
1518 BEGIN
1519
1520 select input_value_id
1521 into l_input_value_id
1522 from pay_input_values_f
1523 where element_type_id = l_new_element_type_id
1524 and rownum = 1;
1525
1526 l_iv_exists := TRUE;
1527
1528 EXCEPTION WHEN NO_DATA_FOUND THEN
1529
1530 l_iv_exists := FALSE;
1531
1532 END;
1533
1534
1535 if l_iv_exists = TRUE then
1536
1537 select 1
1538 into l_null_return
1539 from dual
1540 where exists
1541 (select /*+ ORDERED INDEX(a PAY_RUN_RESULTS_PK)
1542 USE_NL(a b c) */ null
1543 from pay_run_result_values v
1544 , pay_run_results a
1545 , pay_assignment_actions b
1546 , pay_payroll_actions c
1547 where v.input_value_id = l_input_value_id
1548 and a.run_result_id = v.run_result_id
1549 and b.assignment_action_id = a.assignment_action_id
1550 and c.payroll_action_id = b.payroll_action_id
1551 and c.effective_date > r_distinct.c_end);
1552
1553 else
1554
1555 select 1
1556 into l_null_return
1557 from dual
1558 where exists
1559 (select null
1560 from pay_run_results a
1561 , pay_assignment_actions b
1562 , pay_payroll_actions c
1563 where a.element_Type_id = l_new_element_type_id
1564 and b.assignment_action_id = a.assignment_action_id
1565 and c.payroll_action_id = b.payroll_action_id
1566 and c.effective_date > r_distinct.c_end);
1567
1568 end if;
1569
1570 crt_exc('Run results after the new end date of the element','I');
1571
1572 return;
1573
1574 EXCEPTION WHEN NO_DATA_FOUND THEN
1575
1576 null;
1577
1578 END;
1579
1580 END IF; -- end of time
1581
1582 END integrity_checks;
1583
1584 FUNCTION check_parents RETURN BOOLEAN
1585 -------------------------------------
1586 IS
1587 -- Check the integrity of the references to parent data, before allowing
1588 -- data to be installed. No parents can exist in the startup tables, since
1589 -- this will violate constraints when the row is installed, also the
1590 -- parent uid's must exist in the installed tables already.
1591
1592 -- This function will RETURN TRUE if a parent row still exists in the
1593 -- delivery account. All statements drop through to a RETURN FALSE.
1594
1595 -- This procedure is only called in phase 2. The logic to check if
1596 -- a given parental foriegn key exists is split into two parts for
1597 -- every foriegn key. The first select from the delivery tables.
1598
1599 -- If a row is founnd then the installation of the parent must have
1600 -- failed, and this installation must not go ahead. If no data is
1601 -- found, ie: an exception is raised, the installation is valid.
1602
1603 -- The second check looks for a row in the live tables. If no rows
1604 -- are returned then this installation is invalid, since this means
1605 -- that the parent referenced by this row is not present in the
1606 -- live tables.
1607
1608 -- Return code of true indicates that all parental data is correct.
1609
1610 BEGIN
1611
1612
1613 -- Start first parent check
1614
1615 BEGIN
1616
1617 -- Check first parent does not exist in the delivery tables
1618
1619 select null
1620 into l_null_return
1621 from hr_s_element_classifications
1622 where classification_id = r_each_row.classification_id;
1623
1624
1625 crt_exc('Parent classification still exists in delivery tables','I');
1626
1627 -- Parent still exists, ignore this row
1628
1629 return FALSE;
1630
1631 EXCEPTION WHEN NO_DATA_FOUND THEN
1632
1633 null;
1634
1635 END;
1636
1637 BEGIN
1638
1639 -- Check that the parent exists in the live tables
1640
1641
1642 select null
1643 into l_null_return
1644 from pay_element_classifications
1645 where classification_id = r_each_row.classification_id;
1646
1647 EXCEPTION WHEN NO_DATA_FOUND THEN
1648
1649
1650 crt_exc('Parent classification does not exist in live tables','I');
1651
1652 return FALSE;
1653
1654 END;
1655
1656 -- Start 2nd parental check
1657
1658 --
1659 -- #292675. Only do the check on parent formulas if payroll
1660 -- is installed, otherwise don't bother.
1661 --
1662 IF r_each_row.formula_id is not null
1663 AND l_payroll_install_status = 'I' THEN
1664
1665 BEGIN
1666
1667 -- Check parent formula is not in the delivery tables
1668
1669 select distinct null
1670 into l_null_return
1671 from hr_s_formulas_f
1672 where formula_id = r_each_row.formula_id;
1673
1674 crt_exc('Parent formula remains in the startup tables','I');
1675
1676 return FALSE;
1677
1678 EXCEPTION WHEN NO_DATA_FOUND THEN
1679
1680 null;
1681
1682 END;
1683
1684 BEGIN
1685
1686 -- Check parent formula is present in the live tables
1687
1688 select distinct null
1689 into l_null_Return
1690 from ff_formulas_f
1691 where formula_id = r_each_row.formula_id;
1692
1693 EXCEPTION WHEN NO_DATA_FOUND THEN
1694
1695 crt_exc('Parent formula does not exist in live tables','I');
1696 return FALSE;
1697
1698 END;
1699
1700 END IF;
1701
1702 -- Start 3rd parental check
1703
1704 IF r_each_row.benefit_classification_id is not null THEN
1705
1706 BEGIN
1707
1708 -- Check parent ben class is not in the delivery tables
1709
1710 select null
1711 into l_null_return
1712 from hr_s_benefit_classifications
1713 where r_each_row.benefit_classification_id=benefit_classification_id;
1714
1715 crt_exc('Parent benefit class remains in startup tables','I');
1716
1717 return FALSE;
1718
1719 EXCEPTION WHEN NO_DATA_FOUND THEN
1720
1721 null;
1722
1723 END;
1724
1725 BEGIN
1726
1727 -- Check parent ben class is present in the live tables
1728
1729 select null
1730 into l_null_return
1731 from ben_benefit_classifications
1732 where r_each_row.benefit_classification_id=benefit_classification_id;
1733
1734 EXCEPTION WHEN NO_DATA_FOUND THEN
1735
1736 crt_exc('Parent benefit class not in live tables','I');
1737
1738 return FALSE;
1739
1740 END;
1741
1742 END IF;
1743
1744 -- Logic drops through to this statement
1745
1746 return TRUE;
1747
1748 END check_parents;
1749
1750 FUNCTION valid_ownership RETURN BOOLEAN
1751 ---------------------------------------
1752 IS
1753 -- Test ownership of this current row
1754 --
1755 -- Order changed as part of bugfix 555175:
1756 -- This function is split into three distinct parts.
1757 -- The first check examines if this data is actually required
1758 -- for a given install by examining the product installation
1759 -- table, and the ownership details for this row.
1760 -- The next checks to see if a row exists with the same primary
1761 -- key, for a business group that would have access to the
1762 -- delivered row. The last checks details for data created in
1763 -- other legislations, in case data is either created with a null
1764 -- legislation or the delivered row has a null legislation.
1765
1766 -- A return code of TRUE indicates that the row is required.
1767
1768 CURSOR element_clash
1769 IS
1770 -- Cursor to fetch elements with same name
1771
1772 select /*+ INDEX_FFS(pe) */ business_group_id
1773 from pay_element_types_f pe
1774 where business_group_id is not null
1775 and replace(ltrim(rtrim(upper(element_name))), ' ', '_') =
1776 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
1777
1778
1779 BEGIN
1780
1781
1782 -- Bugfix 555175: This used to be the last of the 3 checks performed.
1783 -- Now moved to be the first - if the product which requires this row
1784 -- is not even installed then it is OK for the primary key to already
1785 -- exist in the users tables.
1786 -- The check examines the product installation table, and the
1787 -- ownership details for the delivered row. By examining these
1788 -- tables the row is either deleted or not. If the delivered row
1789 -- is 'stamped' with a legislation subgroup, then a check must be
1790 -- made to see if that subgroup is active or not. This check only
1791 -- needs to be performed in phase 1, since once this decision is
1792 -- made, it is pointless to perform this logic again.
1793
1794 -- An exception is raised if no rows are returned in this select
1795 -- statement. If no rows are returned then one of the following
1796 -- is true:
1797 -- 1. No ownership parameters are defined.
1798 -- 2. The products, for which owning parameters are defined, are
1799 -- not installed with as status of 'I'.
1800 -- 3. The data is defined for a legislation subgroup that is not
1801 -- active.
1802
1803 BEGIN
1804
1805 IF p_phase = 1 THEN
1806 --
1807 --if exception raised then this row is not needed
1808 if (r_distinct.legislation_subgroup is null) then
1809 select distinct null
1810 into l_null_Return
1811 from dual
1812 where exists (
1813 select null
1814 from hr_s_application_ownerships a
1815 , fnd_product_installations b
1816 , fnd_application c
1817 where a.key_name = 'ELEMENT_TYPE_ID'
1818 and a.key_value = r_distinct.c_surrogate_key
1819 and a.product_name = c.application_short_name
1820 and c.application_id = b.application_id
1821 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1822 or
1823 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1824 else
1825 select distinct null
1826 into l_null_Return
1827 from dual
1828 where exists (
1829 select null
1830 from hr_s_application_ownerships a
1831 , fnd_product_installations b
1832 , fnd_application c
1833 where a.key_name = 'ELEMENT_TYPE_ID'
1834 and a.key_value = r_distinct.c_surrogate_key
1835 and a.product_name = c.application_short_name
1836 and c.application_id = b.application_id
1837 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1838 or
1839 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
1840 and exists
1841 (select null
1842 from hr_legislation_subgroups d
1843 where d.legislation_code = r_distinct.legislation_code
1844 and d.legislation_subgroup =
1845 r_distinct.legislation_subgroup
1846 and d.active_inactive_flag = 'A'
1847 );
1848 end if;
1849 END IF;
1850
1851
1852 EXCEPTION WHEN NO_DATA_FOUND THEN
1853
1854 -- Row not needed for any installed product
1855
1856
1857 remove(r_distinct.c_surrogate_key);
1858
1859 -- Indicate row not needed
1860
1861 return FALSE;
1862
1863 END;
1864
1865
1866 --
1867 -- Following checks only need to be made if the element type
1868 -- is a new one and hence doesn't exist yet.
1869 -- If it does already exist theres no point looking for potential
1870 -- clashes with existing data!
1871 --
1872 if r_distinct.new_element_type_flag = 'Y' then
1873
1874 -- Perform a check to see if the primary key has been created within
1875 -- a visible business group. Ie: the business group is for the same
1876 -- legislation as the delivered row, or the delivered row has a null
1877 -- legislation. If no rows are returned then the primary key has not
1878 -- already been created by a user.
1879
1880 if r_distinct.legislation_code is null then
1881
1882 BEGIN
1883
1884
1885 select distinct null
1886 into l_null_return
1887 from pay_element_types_f a
1888 where a.business_group_id is not null
1889 and replace(ltrim(rtrim(upper(a.element_name))), ' ', '_') =
1890 replace(ltrim(rtrim(upper(r_distinct.c_true_key))), ' ', '_');
1891
1892
1893 crt_exc('Row already created in a business group','I');
1894
1895 -- Indicate this row is not to be transferred
1896
1897 return FALSE;
1898
1899 EXCEPTION WHEN NO_DATA_FOUND THEN
1900
1901 null;
1902
1903 END;
1904
1905 else
1906
1907 for elts in element_clash loop
1908
1909 BEGIN
1910
1911
1912 select distinct null
1913 into l_null_return
1914 from per_business_groups pbg
1915 where pbg.business_group_id = elts.business_group_id
1916 and pbg.legislation_code = r_distinct.legislation_code;
1917
1918
1919 crt_exc('Row already created in a business group','I');
1920
1921 -- Indicate this row is not to be transferred
1922
1923 return FALSE;
1924
1925 EXCEPTION WHEN NO_DATA_FOUND THEN
1926
1927 null;
1928
1929 END;
1930
1931 end loop;
1932
1933 end if;
1934
1935 -- Now perform a check to see if this primary key has been installed
1936 -- with a legislation code that would make it visible at the same time
1937 -- as this row. Ie: if any legislation code is null within the set of
1938 -- returned rows, then the transfer may not go ahead. If no rows are
1939 -- returned then the delivered row is fine.
1940 -- G1746. Add the check for business_group_id is null, otherwise the
1941 -- row may be wrongly rejected because it already exists for a
1942 -- specific business group in another legislation. This, though
1943 -- unlikely, is permissible. RMF 05.01.95.
1944
1945 BEGIN
1946
1947
1948 select distinct null
1949 into l_null_return
1950 from pay_element_types_f
1951 where element_name = r_distinct.c_true_key
1952 and nvl (legislation_code, 'x') <>
1953 nvl (r_distinct.legislation_code, 'x')
1954 and (legislation_code is null
1955 or r_distinct.legislation_code is null )
1956 and business_group_id is null;
1957
1958
1959 crt_exc('Row already created for a visible legislation','I');
1960
1961 -- Indicate this row is not to be transferred
1962
1963 return FALSE;
1964
1965 EXCEPTION WHEN NO_DATA_FOUND THEN
1966
1967 return TRUE;
1968
1969 END;
1970
1971 else
1972 return TRUE;
1973 end if;
1974
1975 END valid_ownership;
1976
1977 PROCEDURE delete_live_children
1978 ------------------------------
1979 IS
1980 -- Deletes rows from a live account in readiness for them to be installed
1981
1982 BEGIN
1983
1984 delete from pay_sub_classification_rules_f
1985 where element_type_id = r_distinct.c_surrogate_key
1986 and business_group_id is null;
1987
1988 delete from pay_formula_result_rules_f a
1989 where a.business_group_id is null
1990 and exists
1991 (select null
1992 from pay_status_processing_rules_f b
1993 where b.status_processing_rule_id = a.status_processing_rule_id
1994 and b.element_type_id = r_distinct.c_surrogate_key
1995 and b.business_group_id is null
1996 );
1997
1998 delete from pay_status_processing_rules_f
1999 where element_type_id = r_distinct.c_surrogate_key
2000 and business_Group_id is null;
2001
2002 delete from pay_element_types_f
2003 where element_type_id = r_distinct.c_surrogate_key
2004 and business_Group_id is null;
2005
2006 END delete_live_children;
2007
2008 FUNCTION install_inputs RETURN BOOLEAN
2009 --------------------------------------
2010 IS
2011 -- Install all associated input values for this element type
2012
2013 BEGIN
2014
2015 IF p_phase = 2 THEN
2016
2017 FOR i_values IN inputs(l_new_element_type_id) LOOP
2018
2019 delete from pay_input_values_f
2020 where business_group_id is null
2021 and input_value_id = i_values.input_value_id;
2022
2023 BEGIN
2024
2025 -- Get the correct value set id from FND_FLEX_VALUES_SETS
2026 -- to populate the HR_S_INPUT_VALUES_F.VALUE_SET_ID col with
2027
2028 select FLEX_VALUE_SET_ID
2029 into l_flex_value_set_id
2030 from fnd_flex_value_sets
2031 where FLEX_VALUE_SET_NAME = i_values.value_set_name;
2032
2033 EXCEPTION
2034 -- any exception will just use a null not break hrglobal
2035 when others then
2036 l_flex_value_set_id := null;
2037 END;
2038
2039 BEGIN
2040 insert into pay_input_values_f
2041 (input_value_id
2042 ,effective_start_date
2043 ,effective_end_date
2044 ,element_type_id
2045 ,lookup_type
2046 ,business_group_id
2047 ,legislation_code
2048 ,formula_id
2049 ,display_sequence
2050 ,generate_db_items_flag
2051 ,hot_default_flag
2052 ,mandatory_flag
2053 ,name
2054 ,uom
2055 ,default_value
2056 ,legislation_subgroup
2057 ,max_value
2058 ,min_value
2059 ,warning_or_error
2060 ,last_update_date
2061 ,last_updated_by
2062 ,last_update_login
2063 ,created_by
2064 ,creation_date
2065 ,value_set_id
2066 )
2067 select input_value_id
2068 ,effective_start_date
2069 ,effective_end_date
2070 ,element_type_id
2071 ,lookup_type
2072 ,business_group_id
2073 ,legislation_code
2074 ,formula_id
2075 ,display_sequence
2076 ,generate_db_items_flag
2077 ,hot_default_flag
2078 ,mandatory_flag
2079 ,name
2080 ,uom
2081 ,default_value
2082 ,legislation_subgroup
2083 ,max_value
2084 ,min_value
2085 ,warning_or_error
2086 ,last_update_date
2087 ,last_updated_by
2088 ,last_update_login
2089 ,created_by
2090 ,creation_date
2091 ,l_flex_value_set_id
2092 from hr_s_input_values_f
2093 where input_value_id = i_values.input_value_id;
2094 EXCEPTION WHEN OTHERS THEN
2095 hr_legislation.hrrunprc_trace_on;
2096 hr_utility.trace('ins pay_input_values_f');
2097 hr_utility.trace('iv id ' ||
2098 to_char(i_values.input_value_id));
2099 hr_utility.trace('iv name ' ||
2100 i_values.name);
2101 hr_legislation.hrrunprc_trace_off;
2102 raise;
2103 END;
2104
2105 delete from hr_s_input_values_f
2106 where input_value_id = i_values.input_value_id;
2107 --
2108 -- Bug 2888183 - need to insert balance feeds
2109 --
2110 if i_values.name = 'Pay Value' then
2111 --
2112 if i_values.new_input_value_flag = 'Y' then
2113 HRASSACT.CHECK_LATEST_BALANCES := FALSE;
2114 end if;
2115 --
2116 hr_balance_feeds.ins_bf_pay_value
2117 (p_input_value_id => i_values.input_value_id
2118 ,p_mode => 'STARTUP'
2119 );
2120 --
2121 HRASSACT.CHECK_LATEST_BALANCES := TRUE;
2122 --
2123 end if;
2124 --
2125 END LOOP i_values;
2126
2127 ELSE -- phase 1
2128 -- If any input values remain, indicate to calling proc
2129
2130 select distinct null
2131 into l_null_Return
2132 from hr_s_input_values_f
2133 where element_type_id = l_new_element_type_id;
2134
2135 END IF;
2136
2137 -- If values exist, or in phase 2 return success
2138 return TRUE;
2139
2140 EXCEPTION WHEN NO_DATA_FOUND THEN
2141
2142 -- No input values exist no need to proceed
2143 return FALSE;
2144
2145 END install_inputs;
2146
2147 PROCEDURE name_integrity_checks(p_element_name varchar2)
2148 --------------------------------------------------------
2149 IS
2150 -- After all element type id's have been installed for a given element name
2151 -- check to see if any contention exists with those just installed.
2152
2153 BEGIN
2154
2155 select distinct null
2156 into l_null_return
2157 from pay_element_types_f a
2158 where a.business_Group_id is null
2159 and a.element_name = p_element_name
2160 and exists
2161 (select null
2162 from pay_element_types_f b
2163 where b.element_type_id <> a.element_Type_id
2164 and b.element_name = a.element_name
2165 and b.business_Group_id is null
2166 and b.legislation_code = a.legislation_code
2167 and a.effective_start_date between b.effective_start_date and
2168 b.effective_end_date
2169 );
2170
2171
2172 crt_exc('Installed element dates overlap','N');
2173
2174 EXCEPTION WHEN NO_DATA_FOUND THEN
2175
2176 null;
2177
2178 END name_integrity_checks;
2179
2180 FUNCTION install_element_rows RETURN BOOLEAN
2181 --------------------------------------------
2182 IS
2183 -- Function to insert date effective element rows for a given element type id
2184
2185 BEGIN
2186
2187 IF NOT valid_ownership THEN
2188 return FALSE;
2189 END IF;
2190
2191 IF p_phase = 1 THEN
2192
2193 update_uid;
2194
2195 return true;
2196
2197 ELSE
2198
2199 -- Phase = 2
2200
2201 --
2202 -- Find out if payroll is fully installed. If not, i.e. it's
2203 -- effectively an HR-only install, do not install the formula_id.
2204 -- It causes too many side-effects to deliver with HR.
2205 --
2206 SELECT status
2207 INTO l_payroll_install_status
2208 FROM fnd_product_installations
2209 WHERE application_id = 801;
2210
2211 delete_live_children;
2212
2213 FOR each_row IN c_each_element_row(r_distinct.c_surrogate_key) LOOP
2214
2215 r_each_row := each_row;
2216
2217 IF NOT check_parents THEN
2218 return FALSE;
2219 END IF;
2220
2221 --
2222 -- clear out the formula_id unless payroll is fully installed
2223 --
2224 if l_payroll_install_status = 'I' then
2225 l_formula_id := each_row.formula_id;
2226 else
2227 l_formula_id := NULL;
2228 end if;
2229
2230 BEGIN
2231 insert into pay_element_types_f
2232 (element_type_id
2233 ,effective_start_date
2234 ,effective_end_date
2235 ,business_group_id
2236 ,legislation_code
2237 ,input_currency_code
2238 ,output_currency_code
2239 ,classification_id
2240 ,benefit_classification_iD
2241 ,additional_entry_allowed_flag
2242 ,adjustment_only_flag
2243 ,closed_for_entry_flag
2244 ,element_name
2245 ,indirect_only_flag
2246 ,multiply_value_flag
2247 ,post_termination_rule
2248 ,process_in_run_flag
2249 ,processing_priority
2250 ,processing_type
2251 ,standard_link_flag
2252 ,formula_id
2253 ,comment_id
2254 ,description
2255 ,legislation_subgroup
2256 ,qualifying_age
2257 ,qualifying_length_of_service
2258 ,qualifying_units
2259 ,reporting_name
2260 ,third_party_pay_only_flag
2261 ,last_update_date
2262 ,last_updated_by
2263 ,last_update_login
2264 ,created_by
2265 ,creation_date
2266 ,multiple_entries_allowed_flag
2267 ,element_information_category
2268 ,element_information1
2269 ,element_information2
2270 ,element_information3
2271 ,element_information4
2272 ,element_information5
2273 ,element_information6
2274 ,element_information7
2275 ,element_information8
2276 ,element_information9
2277 ,element_information10
2278 ,element_information11
2279 ,element_information12
2280 ,element_information13
2281 ,element_information14
2282 ,element_information15
2283 ,element_information16
2284 ,element_information17
2285 ,element_information18
2286 ,element_information19
2287 ,element_information20
2288 ,iterative_flag
2289 ,iterative_formula_id
2290 ,iterative_priority
2291 ,retro_summ_ele_id
2292 ,grossup_flag
2293 ,process_mode
2294 ,proration_group_id
2295 ,proration_formula_id
2296 ,TIME_DEFINITION_TYPE
2297 ,TIME_DEFINITION_ID
2298 )
2299 values
2300 (each_row.element_type_id
2301 ,each_row.effective_start_date
2302 ,each_row.effective_end_date
2303 ,each_row.business_group_id
2304 ,each_row.legislation_code
2305 ,each_row.input_currency_code
2306 ,each_row.output_currency_code
2307 ,each_row.classification_id
2308 ,each_row.benefit_classification_iD
2309 ,each_row.additional_entry_allowed_flag
2310 ,each_row.adjustment_only_flag
2311 ,each_row.closed_for_entry_flag
2312 ,each_row.element_name
2313 ,each_row.indirect_only_flag
2314 ,each_row.multiply_value_flag
2315 ,each_row.post_termination_rule
2316 ,each_row.process_in_run_flag
2317 ,each_row.processing_priority
2318 ,each_row.processing_type
2319 ,each_row.standard_link_flag
2320 ,l_formula_id
2321 ,each_row.comment_id
2322 ,each_row.description
2323 ,each_row.legislation_subgroup
2324 ,each_row.qualifying_age
2325 ,each_row.qualifying_length_of_service
2326 ,each_row.qualifying_units
2327 ,each_row.reporting_name
2328 ,each_row.third_party_pay_only_flag
2329 ,each_row.last_update_date
2330 ,each_row.last_updated_by
2331 ,each_row.last_update_login
2332 ,each_row.created_by
2333 ,each_row.creation_date
2334 ,each_row.multiple_entries_allowed_flag
2335 ,each_row.element_information_category
2336 ,each_row.element_information1
2337 ,each_row.element_information2
2338 ,each_row.element_information3
2339 ,each_row.element_information4
2340 ,each_row.element_information5
2341 ,each_row.element_information6
2342 ,each_row.element_information7
2343 ,each_row.element_information8
2344 ,each_row.element_information9
2345 ,each_row.element_information10
2346 ,each_row.element_information11
2347 ,each_row.element_information12
2348 ,each_row.element_information13
2349 ,each_row.element_information14
2350 ,each_row.element_information15
2351 ,each_row.element_information16
2352 ,each_row.element_information17
2353 ,each_row.element_information18
2354 ,each_row.element_information19
2355 ,each_row.element_information20
2356 ,each_row.iterative_flag
2357 ,each_row.iterative_formula_id
2358 ,each_row.iterative_priority
2359 ,each_row.retro_summ_ele_id
2360 ,each_row.grossup_flag
2361 ,each_row.process_mode
2362 ,each_row.proration_group_id
2363 ,each_row.proration_formula_id
2364 ,each_row.TIME_DEFINITION_TYPE
2365 ,each_row.TIME_DEFINITION_ID
2366 );
2367 EXCEPTION WHEN OTHERS THEN
2368 hr_legislation.hrrunprc_trace_on;
2369 hr_utility.trace('ins pay_element_types_f');
2370 hr_utility.trace('element type name ' ||
2371 each_row.element_name);
2372 hr_utility.trace('element_type_id ' ||
2373 to_char(each_row.element_type_id));
2374 hr_utility.trace(':lc: ' || ':' ||
2375 each_row.legislation_code || ':');
2376 hr_legislation.hrrunprc_trace_off;
2377 raise;
2378 END;
2379
2380 END LOOP each_row;
2381
2382 IF NOT install_inputs THEN
2383 return FALSE;
2384 END IF;
2385
2386 BEGIN
2387
2388 -- Installation of sub class rules
2389
2390 FOR s_rules IN sub_rules(r_distinct.c_surrogate_key) LOOP
2391
2392 select null
2393 into l_null_return
2394 from pay_element_classifications
2395 where classification_id = s_rules.classification_id;
2396
2397 BEGIN
2398 insert into pay_sub_classification_rules_f
2399 (sub_classification_rule_id
2400 ,effective_start_date
2401 ,effective_end_date
2402 ,element_type_id
2403 ,classification_id
2404 ,business_group_id
2405 ,legislation_code
2406 ,last_update_date
2407 ,last_updated_by
2408 ,last_update_login
2409 ,created_by
2410 ,creation_date
2411 )
2412 values
2413 (s_rules.sub_classification_rule_id
2414 ,s_rules.effective_start_date
2415 ,s_rules.effective_end_date
2416 ,s_rules.element_type_id
2417 ,s_rules.classification_id
2418 ,s_rules.business_group_id
2419 ,s_rules.legislation_code
2420 ,s_rules.last_update_date
2421 ,s_rules.last_updated_by
2422 ,s_rules.last_update_login
2423 ,s_rules.created_by
2424 ,s_rules.creation_date
2425 );
2426 EXCEPTION WHEN OTHERS THEN
2427 hr_legislation.hrrunprc_trace_on;
2428 hr_utility.trace('ins pay_sub_classification_rules_f');
2429 hr_utility.trace('sub_classification_rule_id ' ||
2430 to_char(s_rules.sub_classification_rule_id));
2431 hr_utility.trace('element_type_id ' ||
2432 to_char(s_rules.element_type_id));
2433 hr_utility.trace('classification_id ' ||
2434 to_char(s_rules.classification_id));
2435 hr_utility.trace(':lc: ' || ':' ||
2436 s_rules.legislation_code || ':');
2437 hr_legislation.hrrunprc_trace_off;
2438 raise;
2439 END;
2440 --
2441 -- Bug 2888183 need to insert balances feeds
2442 --
2443 if (s_rules.new_sub_class_rule_flag = 'Y') then
2444 hr_balance_feeds.ins_bf_sub_class_rule
2445 (s_rules.sub_classification_rule_id
2446 ,'STARTUP');
2447 end if;
2448 --
2449 END LOOP s_rules;
2450
2451 EXCEPTION WHEN NO_DATA_FOUND THEN
2452
2453 crt_exc('Classifcation in sub class rules, not installed','I');
2454
2455 return FALSE;
2456
2457 END;
2458
2459 -- The installation of processing rules loops distinct rule_ids
2460 -- and for each id then installs each date effective row. Each date
2461 -- effective row is tested for parental data. When each date effective
2462 -- row has been installed, child formula result rules are installed.
2463
2464 BEGIN
2465
2466 -- Installation of status processing rules
2467
2468 FOR p_rules IN proc_rules(r_distinct.c_surrogate_key) LOOP
2469
2470 FOR all_rules IN all_p_rules(p_rules.s_rule_id) LOOP
2471
2472 BEGIN
2473 insert into pay_status_processing_rules_f
2474 (STATUS_PROCESSING_RULE_ID
2475 ,EFFECTIVE_START_DATE
2476 ,EFFECTIVE_END_DATE
2477 ,BUSINESS_GROUP_ID
2478 ,LEGISLATION_CODE
2479 ,ELEMENT_TYPE_ID
2480 ,ASSIGNMENT_STATUS_TYPE_ID
2481 ,FORMULA_ID
2482 ,PROCESSING_RULE
2483 ,COMMENT_ID
2484 ,LEGISLATION_SUBGROUP
2485 ,LAST_UPDATE_DATE
2486 ,LAST_UPDATED_BY
2487 ,LAST_UPDATE_LOGIN
2488 ,CREATED_BY
2489 ,CREATION_DATE
2490 )
2491 values
2492 (all_rules.STATUS_PROCESSING_RULE_ID
2493 ,all_rules.EFFECTIVE_START_DATE
2494 ,all_rules.EFFECTIVE_END_DATE
2495 ,all_rules.BUSINESS_GROUP_ID
2496 ,all_rules.LEGISLATION_CODE
2497 ,all_rules.ELEMENT_TYPE_ID
2498 ,all_rules.ASSIGNMENT_STATUS_TYPE_ID
2499 ,all_rules.FORMULA_ID
2500 ,all_rules.PROCESSING_RULE
2501 ,all_rules.COMMENT_ID
2502 ,all_rules.LEGISLATION_SUBGROUP
2503 ,all_rules.LAST_UPDATE_DATE
2504 ,all_rules.LAST_UPDATED_BY
2505 ,all_rules.LAST_UPDATE_LOGIN
2506 ,all_rules.CREATED_BY
2507 ,all_rules.CREATION_DATE
2508 );
2509 EXCEPTION WHEN OTHERS THEN
2510 hr_legislation.hrrunprc_trace_on;
2511 hr_utility.trace('ins pay_status_processing_rules_f');
2512 hr_utility.trace('STATUS_PROCESSING_RULE_ID ' ||
2513 to_char(all_rules.STATUS_PROCESSING_RULE_ID));
2514 hr_utility.trace('ELEMENT_TYPE_ID ' ||
2515 to_char(all_rules.element_type_id));
2516 hr_utility.trace('PROCESSING_RULE ' ||
2517 all_rules.PROCESSING_RULE);
2518 hr_utility.trace('ASSIGNMENT_STATUS_TYPE_ID ' ||
2519 to_char(all_rules.ASSIGNMENT_STATUS_TYPE_ID));
2520 hr_utility.trace(':lc: ' || ':' ||
2521 all_rules.legislation_code || ':');
2522 hr_legislation.hrrunprc_trace_off;
2523 raise;
2524 END;
2525
2526
2527 END LOOP all_rules;
2528
2529 FOR all_frrs IN frrs(p_rules.s_rule_id) LOOP
2530
2531 BEGIN
2532 insert into pay_formula_result_rules_f
2533 (FORMULA_RESULT_RULE_ID
2534 ,EFFECTIVE_START_DATE
2535 ,EFFECTIVE_END_DATE
2536 ,BUSINESS_GROUP_ID
2537 ,LEGISLATION_CODE
2538 ,STATUS_PROCESSING_RULE_ID
2539 ,RESULT_NAME
2540 ,RESULT_RULE_TYPE
2541 ,LEGISLATION_SUBGROUP
2542 ,SEVERITY_LEVEL
2543 ,INPUT_VALUE_ID
2544 ,ELEMENT_TYPE_ID
2545 ,LAST_UPDATE_DATE
2546 ,LAST_UPDATED_BY
2547 ,LAST_UPDATE_LOGIN
2548 ,CREATED_BY
2549 ,CREATION_DATE
2550 )
2551 values
2552 (all_frrs.FORMULA_RESULT_RULE_ID
2553 ,all_frrs.EFFECTIVE_START_DATE
2554 ,all_frrs.EFFECTIVE_END_DATE
2555 ,all_frrs.BUSINESS_GROUP_ID
2556 ,all_frrs.LEGISLATION_CODE
2557 ,all_frrs.STATUS_PROCESSING_RULE_ID
2558 ,all_frrs.RESULT_NAME
2559 ,all_frrs.RESULT_RULE_TYPE
2560 ,all_frrs.LEGISLATION_SUBGROUP
2561 ,all_frrs.SEVERITY_LEVEL
2562 ,all_frrs.INPUT_VALUE_ID
2563 ,all_frrs.ELEMENT_TYPE_ID
2564 ,all_frrs.LAST_UPDATE_DATE
2565 ,all_frrs.LAST_UPDATED_BY
2566 ,all_frrs.LAST_UPDATE_LOGIN
2567 ,all_frrs.CREATED_BY
2568 ,all_frrs.CREATION_DATE
2569 );
2570 EXCEPTION WHEN OTHERS THEN
2571 hr_legislation.hrrunprc_trace_on;
2572 hr_utility.trace('ins pay_formula_result_rules_f');
2573 hr_utility.trace('FORMULA_RESULT_RULE_ID ' ||
2574 to_char(all_frrs.FORMULA_RESULT_RULE_ID));
2575 hr_utility.trace('STATUS_PROCESSING_RULE_ID ' ||
2576 to_char(all_frrs.STATUS_PROCESSING_RULE_ID));
2577 hr_utility.trace('RESULT_NAME ' ||
2578 all_frrs.RESULT_NAME);
2579 hr_utility.trace(':lc: ' || ':' ||
2580 all_frrs.legislation_code || ':');
2581 hr_legislation.hrrunprc_trace_off;
2582 raise;
2583 END;
2584
2585 END LOOP all_frrs;
2586
2587 END LOOP p_rules; --end the distinct loop
2588
2589 EXCEPTION WHEN NO_DATA_FOUND THEN
2590
2591 crt_exc('Child status rules has parent data not installed','I');
2592
2593 return FALSE;
2594
2595 END;
2596
2597 remove(r_distinct.c_surrogate_key);
2598
2599 return TRUE;
2600
2601 END IF;
2602
2603 END install_element_rows;
2604
2605 BEGIN
2606 -- Two loops are used here. The main loop which select distinct primary
2607 -- key rows and an inner loop which selects all date effective rows for the
2608 -- primary key. The inner loop is only required in phase two, since only
2609 -- in phase 2 are rows actually transferred. The logic reads as follows:
2610
2611 -- - Only deal with rows which have correct ownership details and will
2612 -- not cause integrity problems (valid_ownership).
2613 -- - In Phase 1:
2614 -- - Delete delivery rows where the installed rows are identicle.
2615 -- - The UNION satement compares delivery rows to installed rows.
2616 -- If the sub query returns any rows, then the delivered
2617 -- tables and the installed tables are different.
2618 -- In Phase 2:
2619 -- - Delete from the installed tables using the surrogate id.
2620 -- - If an installed row is to be replaced, the values of
2621 -- the surrogate keys will be identicle at this stage.
2622 -- - Data will then be deleted from the delivery tables.
2623 -- - Call the installation procedure for any child tables, that
2624 -- must be installed within the same commit unit. If any
2625 -- errors occur then rollback to the last declared savepoint.
2626 -- - Check that all integrity rules are still obeyed at the end
2627 -- of the installation (integrity_checks).
2628
2629 -- An exception is used with this procedure 'row_in_error' in case an error
2630 -- is encountered from calling any function. If this is raised, then an
2631 -- exception is entered into the control tables (crt_exc();) and a rollback
2632 -- is performed.
2633
2634 IF p_phase = 1 THEN
2635 check_next_sequence;
2636 END IF;
2637
2638 FOR element_names IN c_distinct_name LOOP
2639
2640
2641 savepoint new_element_name;
2642
2643 FOR element_ids IN c_distinct_element(element_names.element_name) LOOP
2644
2645 savepoint new_distinct_id;
2646
2647 r_distinct := element_ids;
2648
2649 IF p_phase = 2 THEN
2650 l_new_element_type_id := r_distinct.c_surrogate_key;
2651 END IF;
2652
2653 -- Ensure both phases use the same value for the surrogate id
2654
2655 IF install_element_rows THEN
2656 integrity_checks;
2657 END IF;
2658
2659 END LOOP element_ids;
2660
2661 IF p_phase = 2 THEN
2662 name_integrity_checks(element_names.element_name);
2663 END IF;
2664
2665 END LOOP element_names;
2666
2667 END install_elements;
2668
2669 --****************************************************************************
2670 -- INSTALLATION PROCEDURE FOR : PAY_ELEMENT_SETS
2671 --****************************************************************************
2672
2673 PROCEDURE install_ele_sets(p_phase IN number)
2674 ---------------------------------------------
2675 IS
2676 l_null_return varchar2(1); -- For 'select null' statements
2677 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
2678
2679 CURSOR stu -- Selects all rows from startup entity
2680 IS
2681 select element_set_name c_true_key
2682 , element_set_id c_surrogate_key
2683 , legislation_code c_leg_code
2684 , element_set_type
2685 , last_update_date
2686 , last_updated_by
2687 , last_update_login
2688 , created_by
2689 , creation_date
2690 , rowid
2691 from hr_s_element_sets;
2692
2693 CURSOR child_type(ele_set_id number)
2694 IS
2695 -- Cursor to install child element type rules
2696
2697 select *
2698 from hr_s_element_type_rules
2699 where element_set_id = ele_set_id;
2700
2701 CURSOR child_class(ele_set_id number)
2702 IS
2703 -- Cursor to install child element classification rules
2704
2705 select *
2706 from hr_s_ele_classn_rules
2707 where element_set_id = ele_set_id;
2708
2709 stu_rec stu%ROWTYPE;
2710
2711
2712
2713 PROCEDURE check_next_sequence
2714 -----------------------------
2715 IS
2716
2717 v_sequence_number number(15);
2718 v_min_delivered number(15);
2719 v_max_delivered number(15);
2720
2721
2722 -- Surrogate id conflicts may arise from two scenario's:
2723 -- 1. Where the newly select sequence value conflicts with values
2724 -- in the STU tables.
2725 -- 2. Where selected surrogate keys, from the installed tables,
2726 -- conflict with other rows in the STU tables.
2727 --
2728 -- Both of the above scenario's are tested for.
2729 -- The first is a simple match, where if a value is detected in the
2730 -- STU tables and the installed tables then a conflict is detected. In
2731 -- This instance all STU surrogate keys, for this table, are updated.
2732 -- The second is tested for using the sequences.
2733 -- If the next value from the live sequence is within the range of
2734 -- delivered surrogate id's then the live sequence must be incremented.
2735 -- If no action is taken, then duplicates may be introduced into the
2736 -- delivered tables, and child rows may be totally invalidated.
2737
2738 BEGIN
2739
2740
2741 BEGIN --check that the installed id's will not conflict
2742 --with the delivered values
2743
2744
2745 select distinct null
2746 into l_null_return
2747 from pay_element_sets a
2748 where exists
2749 (select null
2750 from hr_s_element_sets b
2751 where a.element_set_id = b.element_set_id
2752 );
2753
2754 --conflict may exist
2755 --update all element_set_id's to remove conflict
2756
2757 update /*+NO_INDEX*/ hr_s_element_sets
2758 set element_set_id = element_set_id - 50000000;
2759
2760 update /*+NO_INDEX*/ hr_s_element_type_rules
2761 set element_set_id = element_set_id - 50000000;
2762
2763 update /*+NO_INDEX*/ hr_s_ele_classn_rules
2764 set element_set_id = element_set_id - 50000000;
2765
2766 update hr_s_application_ownerships
2767 set key_value = key_value - 50000000
2768 where key_name = 'ELEMENT_SET_ID';
2769
2770 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2771
2772 END; --check of element_set_id
2773
2774
2775
2776 select min(element_set_id) - (count(*) *3)
2777 , max(element_set_id) + (count(*) *3)
2778 into v_min_delivered
2779 , v_max_delivered
2780 from hr_s_element_sets;
2781
2782 select pay_element_sets_s.nextval
2783 into v_sequence_number
2784 from dual;
2785
2786 IF v_sequence_number
2787 BETWEEN v_min_delivered AND v_max_delivered
2788 THEN
2789
2790 hr_legislation.munge_sequence('PAY_ELEMENT_SETS_S',
2791 v_sequence_number,
2792 v_max_delivered);
2793
2794 END IF;
2795
2796 END check_next_sequence;
2797
2798
2799 PROCEDURE crt_exc (exception_type IN varchar2)
2800 ----------------------------------------------
2801 IS
2802 -- Reports any exceptions during the delivery of startup data to
2803 -- PAY_ELEMENT_SETS
2804
2805 BEGIN
2806 -- When the installation procedures encounter an error that cannot
2807 -- be handled, an exception is raised and all work is rolled back
2808 -- to the last savepoint. The installation process then continues
2809 -- with the next primary key to install. The same exception will
2810 -- not be raised more than once.
2811
2812
2813 rollback to new_element_set_name;
2814
2815 hr_legislation.insert_hr_stu_exceptions('pay_element_sets'
2816 , stu_rec.c_surrogate_key
2817 , exception_type
2818 , stu_rec.c_true_key);
2819
2820
2821 END crt_exc;
2822
2823 PROCEDURE update_uid
2824 --------------------
2825 IS
2826 -- Subprogram to update surrogate UID and all occurrences in child rows
2827
2828 BEGIN
2829
2830 BEGIN
2831
2832 select distinct element_set_id
2833 into l_new_surrogate_key
2834 from pay_element_sets
2835 where element_set_name = stu_rec.c_true_key
2836 and business_group_id is null
2837 and ( (legislation_code is null
2838 and stu_rec.c_leg_code is null)
2839 or (legislation_code = stu_rec.c_leg_code) );
2840
2841 EXCEPTION WHEN NO_DATA_FOUND THEN
2842
2843
2844 select pay_element_sets_s.nextval
2845 into l_new_surrogate_key
2846 from dual;
2847
2848 WHEN TOO_MANY_ROWS THEN
2849
2850 hr_legislation.hrrunprc_trace_on;
2851 hr_utility.trace('sel pay_element_sets TMR');
2852 hr_utility.trace('element_set_name ' ||
2853 stu_rec.c_true_key);
2854 hr_utility.trace(':lc: ' || ':' ||
2855 stu_rec.c_leg_code || ':');
2856 hr_legislation.hrrunprc_trace_off;
2857 raise;
2858 END;
2859
2860 --update all child entities
2861 update hr_s_element_sets
2862 set element_set_id = l_new_surrogate_key
2863 where element_set_id = stu_rec.c_surrogate_key;
2864
2865 update hr_s_application_ownerships
2866 set key_value = to_char(l_new_surrogate_key)
2867 where key_value = to_char(stu_rec.c_surrogate_key)
2868 and key_name = 'ELEMENT_SET_ID';
2869
2870 update hr_s_element_type_rules
2871 set element_set_id = l_new_surrogate_key
2872 where element_set_id = stu_rec.c_surrogate_key;
2873
2874 update hr_s_ele_classn_rules
2875 set element_set_id = l_new_surrogate_key
2876 where element_set_id = stu_rec.c_surrogate_key;
2877
2878 END update_uid;
2879
2880 PROCEDURE remove
2881 ----------------
2882 IS
2883 -- Remove a row from either the startup tables or the installed tables
2884
2885 v_number number;
2886
2887 BEGIN
2888
2889 delete from hr_s_element_type_rules
2890 where element_set_id = l_new_surrogate_key;
2891
2892 delete from hr_s_ele_classn_rules
2893 where element_set_id = l_new_surrogate_key;
2894
2895 delete from hr_s_element_sets
2896 where rowid = stu_rec.rowid;
2897
2898 END remove;
2899
2900 FUNCTION valid_ownership RETURN BOOLEAN
2901 ---------------------------------------
2902 IS
2903 -- Test ownership of this current row
2904
2905 BEGIN
2906
2907 -- This routine only operates in phase 1. Rows are present in the
2908 -- table hr_application_ownerships in the delivery account, which
2909 -- dictate which products a piece of data is used for. If the query
2910 -- returns a rowm then this data is required, and the function will
2911 -- return true. If no rows are returned and an exception is raised,
2912 -- then this row is not required and may be deleted from the delivery
2913 -- tables.
2914
2915 -- If legislation code and subgroup code are included on the delivery
2916 -- tables, a check must be made to determine if the data is defined for
2917 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
2918 -- installation.
2919
2920 -- A return code of TRUE indicates that thhe row is required.
2921
2922 -- The exception is raised within this procedure if no rows are returned
2923 -- in this select statement. If no rows are returned then one of the
2924 -- following is true:
2925 -- 1. No ownership parameters are defined.
2926 -- 2. The products, for which owning parameters are defined, are not
2927 -- installed with as status of 'I'.
2928 -- 3. The data is defined for a legislation subgroup that is not active.
2929
2930 IF p_phase <> 1 THEN -- Only perform in phase 1
2931 return TRUE;
2932 END IF;
2933
2934
2935 select null --if exception raised then this row is not needed
2936 into l_null_return
2937 from dual
2938 where exists
2939 (select null
2940 from hr_s_application_ownerships a
2941 , fnd_product_installations b
2942 , fnd_application c
2943 where a.key_name = 'ELEMENT_SET_ID'
2944 and a.key_value = stu_rec.c_surrogate_key
2945 and a.product_name = c.application_short_name
2946 and c.application_id = b.application_id
2947 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2948 or
2949 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2950
2951 return TRUE; --indicates row is required
2952
2953 EXCEPTION WHEN NO_DATA_FOUND THEN
2954
2955 -- Row not needed for any installed product
2956
2957 remove;
2958
2959 -- Indicates row not needed
2960
2961 return FALSE;
2962
2963 END valid_ownership;
2964
2965 PROCEDURE transfer_row
2966 ----------------------
2967 IS
2968 -- Check if a delivered row is needed and insert into the
2969 -- live tables if it is
2970
2971 v_inst_update date; -- Holds update details of installed row
2972
2973 BEGIN
2974
2975
2976 BEGIN
2977
2978 -- Perform a check to see if the primary key has been creeated within
2979 -- a visible business group. Ie: the business group is for the same
2980 -- legislation as the delivered row, or the delivered row has a null
2981 -- legislation. If no rows are returned then the primary key has not
2982 -- already been created by a user.
2983
2984 select distinct null
2985 into l_null_return
2986 from pay_element_sets a
2987 where a.element_set_name = stu_rec.c_true_key
2988 and a.business_group_id is not null
2989 and exists (select null from per_business_groups b
2990 where b.business_group_id = a.business_group_id
2991 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
2992
2993 crt_exc('Row already created in a business group');
2994
2995 -- Indicates this row is not to be transferred
2996
2997 return;
2998
2999 EXCEPTION WHEN NO_DATA_FOUND THEN
3000
3001 null;
3002
3003 END;
3004
3005
3006 -- Now perform a check to see if this primary key has been installed
3007 -- with a legislation code that would make it visible at the same time
3008 -- as this row. Ie: if any legislation code is null within the set of
3009 -- returned rows, then the transfer may not go ahead. If no rows are
3010 -- returned then the delivered row is fine.
3011 -- G1746. Add the check for business_group_id is null, otherwise the
3012 -- row may be wrongly rejected because it already exists for a
3013 -- specific business group in another legislation. This, though
3014 -- unlikely, is permissible. RMF 05.01.95.
3015
3016 BEGIN
3017 select distinct null
3018 into l_null_return
3019 from pay_element_sets
3020 where element_set_name = stu_rec.c_true_key
3021 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
3022 and (legislation_code is null
3023 or stu_rec.c_leg_code is null )
3024 and business_group_id is null;
3025
3026 crt_exc('Row already created for a visible legislation');
3027
3028 -- Indicates this row is not to be transferred
3029
3030 return;
3031
3032 EXCEPTION WHEN NO_DATA_FOUND THEN
3033
3034 null;
3035
3036 END;
3037
3038
3039 -- When the procedure is called in phase 1, there is no need to
3040 -- actually perform the transfer from the delivery tables into the
3041 -- live. Hence if phase = 1 control is returned to the calling
3042 -- procedure and the next row is returned.
3043
3044 -- If the procedure is called in phase 2, then the live row is updated
3045 -- with the values on the delivered row.
3046
3047 -- The routine check_parents validates foreign key references and
3048 -- ensures referential integrity. The routine checks to see if the
3049 -- parents of a given row have been transfered to the live tables.
3050
3051 -- This may only be called in phase two since in phase one all
3052 -- parent rows will remain in the delivery tables.
3053
3054 -- After the above checks only data that has been chanegd or is new
3055 -- will be left in the delivery tables. At this stage if the row is
3056 -- already present then it must be updated to ensure referential
3057 -- integrity. Therefore an update will be performed and if SQL%FOUND
3058 -- is FALSE an insert will be performed.
3059
3060 -- The last step of the transfer, in phase 2, is to delete the now
3061 -- transfered row from the delivery tables.
3062
3063 IF p_phase = 1 THEN
3064 return;
3065 END IF;
3066
3067 delete from pay_element_type_rules
3068 where element_set_id = l_new_surrogate_key;
3069
3070 delete from pay_ele_classification_rules
3071 where element_set_id = l_new_surrogate_key;
3072
3073 update pay_element_sets
3074 set element_set_type = stu_rec.element_set_type
3075 , last_update_date = stu_rec.last_update_date
3076 , last_updated_by = stu_rec.last_updated_by
3077 , last_update_login = stu_rec.last_update_login
3078 , created_by = stu_rec.created_by
3079 , creation_date = stu_rec.creation_date
3080 where element_set_id = stu_rec.c_surrogate_key;
3081
3082 IF NOT SQL%FOUND THEN
3083
3084
3085 BEGIN
3086 insert into pay_element_sets
3087 (element_set_name
3088 ,element_set_id
3089 ,legislation_code
3090 ,element_set_type
3091 ,last_update_date
3092 ,last_updated_by
3093 ,last_update_login
3094 ,created_by
3095 ,creation_date
3096 )
3097 values
3098 (stu_rec.c_true_key
3099 ,stu_rec.c_surrogate_key
3100 ,stu_rec.c_leg_code
3101 ,stu_rec.element_set_type
3102 ,stu_rec.last_update_date
3103 ,stu_rec.last_updated_by
3104 ,stu_rec.last_update_login
3105 ,stu_rec.created_by
3106 ,stu_rec.creation_date
3107 );
3108 EXCEPTION WHEN OTHERS THEN
3109 hr_legislation.hrrunprc_trace_on;
3110 hr_utility.trace('ins pay_element_sets');
3111 hr_utility.trace('element_set_name ' ||
3112 stu_rec.c_true_key);
3113 hr_utility.trace('element_set_id ' ||
3114 to_char(stu_rec.c_surrogate_key));
3115 hr_utility.trace('element_set_type ' ||
3116 stu_rec.element_set_type);
3117 hr_utility.trace(':lc: ' || ':' ||
3118 stu_rec.c_leg_code || ':');
3119 hr_legislation.hrrunprc_trace_off;
3120 raise;
3121 END;
3122
3123 END IF;
3124
3125 -- Now install all child element type rules
3126
3127
3128 FOR ele_types IN child_type(stu_rec.c_surrogate_key) LOOP
3129
3130 BEGIN
3131
3132
3133 select null
3134 into l_null_return
3135 from pay_element_types_f
3136 where element_type_id = ele_types.element_type_id;
3137
3138 BEGIN
3139 insert into pay_element_type_rules
3140 (element_type_id
3141 ,element_set_id
3142 ,include_or_exclude
3143 ,last_update_date
3144 ,last_updated_by
3145 ,last_update_login
3146 ,created_by
3147 ,creation_date)
3148 values
3149 (ele_types.element_Type_id
3150 ,ele_types.element_Set_id
3151 ,ele_types.include_or_exclude
3152 ,ele_types.last_update_date
3153 ,ele_types.last_updated_by
3154 ,ele_types.last_update_login
3155 ,ele_types.created_by
3156 ,ele_types.creation_date);
3157 EXCEPTION WHEN OTHERS THEN
3158 hr_legislation.hrrunprc_trace_on;
3159 hr_utility.trace('ins pay_element_type_rules');
3160 hr_utility.trace('element_type_id ' ||
3161 to_char(ele_types.element_Type_id));
3162 hr_utility.trace('element_set_id ' ||
3163 to_char(ele_types.element_Set_id));
3164 hr_legislation.hrrunprc_trace_off;
3165 raise;
3166 END;
3167
3168
3169 EXCEPTION WHEN NO_DATA_FOUND THEN
3170
3171 crt_exc('Parent element type not installed');
3172
3173 return;
3174
3175 END;
3176
3177 END LOOP;
3178
3179 -- Now install all classification rules
3180
3181
3182 FOR ele_class IN child_class(stu_Rec.c_surrogate_key) LOOP
3183
3184 BEGIN
3185
3186
3187 select null
3188 into l_null_return
3189 from pay_element_classifications
3190 where classification_id = ele_class.classification_id;
3191
3192 BEGIN
3193 insert into pay_ele_classification_rules
3194 (element_set_id
3195 ,classification_id
3196 ,last_update_date
3197 ,last_updated_by
3198 ,last_update_login
3199 ,created_by
3200 ,creation_date)
3201 values
3202 (ele_class.element_set_id
3203 ,ele_class.classification_id
3204 ,ele_class.last_update_date
3205 ,ele_class.last_updated_by
3206 ,ele_class.last_update_login
3207 ,ele_class.created_by
3208 ,ele_class.creation_date);
3209 EXCEPTION WHEN OTHERS THEN
3210 hr_legislation.hrrunprc_trace_on;
3211 hr_utility.trace('ins pay_ele_classification_rules');
3212 hr_utility.trace('element_set_id ' ||
3213 to_char(ele_class.element_set_id));
3214 hr_utility.trace('classification_id ' ||
3215 to_char(ele_class.classification_id));
3216 hr_legislation.hrrunprc_trace_off;
3217 raise;
3218 END;
3219
3220 EXCEPTION WHEN NO_DATA_FOUND THEN
3221
3222 crt_exc('Parent classification not installed');
3223
3224 return;
3225
3226 END;
3227
3228 END LOOP;
3229
3230
3231 remove;
3232
3233 END transfer_row;
3234
3235 BEGIN
3236
3237 -- This is the main loop to perform the installation logic. A cursor
3238 -- is opened to control the loop, and each row returned is placed
3239 -- into a record defined within the main procedure so each sub
3240 -- procedure has full access to all returrned columns. For each
3241 -- new row returned, a new savepoint is declared. If at any time
3242 -- the row is in error a rollback iss performed to the savepoint
3243 -- and the next row is returned. Ownership details are checked and
3244 -- if the row is required then the surrogate id is updated and the
3245 -- main transfer logic is called.
3246
3247 IF p_phase = 1 THEN
3248 check_next_sequence;
3249 END IF;
3250
3251 FOR delivered IN stu LOOP
3252
3253 -- Uses main cursor stu to impilicity define a record
3254
3255
3256 savepoint new_element_set_name;
3257
3258 stu_rec := delivered;
3259
3260 IF p_phase = 2 THEN
3261 l_new_surrogate_key := stu_rec.c_surrogate_key;
3262 END IF;
3263
3264 IF valid_ownership THEN
3265
3266 -- Test the row onerships for the current row
3267
3268
3269 IF p_phase = 1 THEN
3270 update_uid;
3271 END IF;
3272
3273 transfer_row;
3274
3275 END IF;
3276
3277 END LOOP;
3278
3279 END install_ele_sets;
3280
3281 --****************************************************************************
3282 -- INSTALLATION PROCEDURE FOR : PAY_USER_TABLES
3283 --****************************************************************************
3284
3285 PROCEDURE install_utables(p_phase IN number)
3286 --------------------------------------------
3287 IS
3288 -- Install procedure to transfer startup element classifications into
3289 -- a live account.
3290
3291 l_null_return varchar2(1); -- For 'select null' statements
3292 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3293
3294 CURSOR stu -- Selects all rows from startup entity
3295 IS
3296 select user_table_id c_surrogate_key
3297 , business_group_id
3298 , legislation_code c_leg_code
3299 , range_or_match
3300 , user_key_units
3301 , user_table_name c_true_key
3302 , legislation_subgroup c_leg_sgrp
3303 , last_update_date
3304 , last_updated_by
3305 , last_update_login
3306 , created_by
3307 , creation_date
3308 , rowid
3309 , user_row_title
3310 from hr_s_user_tables;
3311
3312 stu_rec stu%ROWTYPE;
3313
3314
3315 PROCEDURE check_next_sequence
3316 -----------------------------
3317 IS
3318
3319 v_sequence_number number(15);
3320 v_min_delivered number(15);
3321 v_max_delivered number(15);
3322
3323 -- Surrogate id conflicts may arise from two scenario's:
3324 -- 1. Where the newly select sequence value conflicts with values
3325 -- in the STU tables.
3326 -- 2. Where selected surrogate keys, from the installed tables,
3327 -- conflict with other rows in the STU tables.
3328 --
3329 -- Both of the above scenario's are tested for.
3330 -- The first is a simple match, where if a value is detected in the
3331 -- STU tables and the installed tables then a conflict is detected. In
3332 -- This instance all STU surrogate keys, for this table, are updated.
3333 -- The second is tested for using the sequences.
3334 -- If the next value from the live sequence is within the range of
3335 -- delivered surrogate id's then the live sequence must be incremented.
3336 -- If no action is taken, then duplicates may be introduced into the
3337 -- delivered tables, and child rows may be totally invalidated.
3338
3339 BEGIN
3340
3341
3342 BEGIN --check that the installed id's will not conflict
3343 --with the delivered values
3344
3345
3346 select distinct null
3347 into l_null_return
3348 from pay_user_tables a
3349 where exists
3350 (select null
3351 from hr_s_user_tables b
3352 where a.user_table_id = b.user_table_id
3353 );
3354
3355 --conflict may exist
3356 --update all user_table_id's to remove conflict
3357
3358 update /*+NO_INDEX*/ hr_s_user_columns
3359 set user_table_id = user_table_id - 50000000;
3360
3361 update /*+NO_INDEX*/ hr_s_user_rows_f
3362 set user_table_id = user_table_id - 50000000;
3363
3364 update /*+NO_INDEX*/ hr_s_user_tables
3365 set user_table_id = user_table_id - 50000000;
3366
3367 update hr_s_application_ownerships
3368 set key_value = key_value - 50000000
3369 where key_name = 'USER_TABLE_ID';
3370
3371 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3372
3373 END; --check of user_table_id
3374
3375
3376
3377 select min(user_table_id) - (count(*) *3)
3378 , max(user_table_id) + (count(*) *3)
3379 into v_min_delivered
3380 , v_max_delivered
3381 from hr_s_user_tables;
3382
3383 select pay_user_tables_s.nextval
3384 into v_sequence_number
3385 from dual;
3386
3387 IF v_sequence_number
3388 BETWEEN v_min_delivered AND v_max_delivered
3389 THEN
3390
3391 hr_legislation.munge_sequence('PAY_USER_TABLES_S',
3392 v_sequence_number,
3393 v_max_delivered);
3394
3395 END IF;
3396
3397 END check_next_sequence;
3398
3399
3400 PROCEDURE crt_exc (exception_type IN varchar2)
3401 ----------------------------------------------
3402 IS
3403 -- Reports any exceptions during the delivery of startup data to
3404 -- PAY_USER_TABLES
3405
3406 BEGIN
3407 -- When the installation procedures encounter an error that cannot
3408 -- be handled, an exception is raised and all work is rolled back
3409 -- to the last savepoint. The installation process then continues
3410 -- with the next primary key to install. The same exception will
3411 -- not be raised more than once.
3412
3413
3414 rollback to new_user_table_name;
3415
3416 hr_legislation.insert_hr_stu_exceptions('pay_user_tables'
3417 , stu_rec.c_surrogate_key
3418 , exception_type
3419 , stu_rec.c_true_key);
3420
3421
3422 END crt_exc;
3423
3424 PROCEDURE update_uid
3425 --------------------
3426 IS
3427 -- Subprogram to update surrogate UID and all occurrences in child rows
3428
3429 BEGIN
3430
3431
3432 BEGIN
3433
3434 select distinct user_table_id
3435 into l_new_surrogate_key
3436 from pay_user_tables
3437 where user_table_name = stu_rec.c_true_key
3438 and business_group_id is null
3439 and ( (legislation_code is null
3440 and stu_rec.c_leg_code is null)
3441 or (legislation_code = stu_rec.c_leg_code) );
3442
3443 EXCEPTION WHEN NO_DATA_FOUND THEN
3444
3445
3446 select pay_user_tables_s.nextval
3447 into l_new_surrogate_key
3448 from dual;
3449
3450 WHEN TOO_MANY_ROWS THEN
3451 hr_legislation.hrrunprc_trace_on;
3452 hr_utility.trace('sel pay_user_tables TMR');
3453 hr_utility.trace('user_table_name ' ||
3454 stu_rec.c_true_key);
3455 hr_utility.trace(':lc: ' || ':' ||
3456 stu_rec.c_leg_code || ':');
3457 hr_legislation.hrrunprc_trace_off;
3458 raise;
3459
3460 END;
3461
3462 -- Update all child entities
3463 update hr_s_user_tables
3464 set user_table_id = l_new_surrogate_key
3465 where user_table_id = stu_rec.c_surrogate_key;
3466
3467 update hr_s_application_ownerships
3468 set key_value = to_char(l_new_surrogate_key)
3469 where key_value = to_char(stu_rec.c_surrogate_key)
3470 and key_name = 'USER_TABLE_ID';
3471
3472 update hr_s_user_columns
3473 set user_table_id = l_new_surrogate_key
3474 where user_table_id = stu_rec.c_surrogate_key;
3475
3476 update hr_s_user_rows_f
3477 set user_table_id = l_new_surrogate_key
3478 where user_table_id = stu_rec.c_surrogate_key;
3479
3480 END update_uid;
3481
3482 PROCEDURE remove
3483 ----------------
3484 IS
3485 -- Remove a row from either the startup tables or the installed tables
3486
3487 BEGIN
3488
3489 delete from hr_s_user_tables
3490 where rowid = stu_rec.rowid;
3491
3492 END remove;
3493
3494 FUNCTION valid_ownership RETURN BOOLEAN
3495 IS
3496 -- Test ownership of this current row
3497
3498 BEGIN
3499
3500 -- This routine only operates in phase 1. Rows are present in the
3501 -- table hr_application_ownerships in the delivery account, which
3502 -- dictate which products a piece of data is used for. If the query
3503 -- returns a rowm then this data is required, and the function will
3504 -- return true. If no rows are returned and an exception is raised,
3505 -- then this row is not required and may be deleted from the delivery
3506 -- tables.
3507
3508 -- If legislation code and subgroup code are included on the delivery
3509 -- tables, a check must be made to determine if the data is defined for
3510 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
3511 -- installation.
3512
3513 -- A return code of TRUE indicates that thhe row is required.
3514
3515 -- The exception is raised within this procedure if no rows are returned
3516 -- in this select statement. If no rows are returned then one of the
3517 -- following is true:
3518 -- 1. No ownership parameters are defined.
3519 -- 2. The products, for which owning parameters are defined, are not
3520 -- installed with as status of 'I'.
3521 -- 3. The data is defined for a legislation subgroup that is not active.
3522
3523 IF p_phase <> 1 THEN -- Only perform in phase 1
3524 return TRUE;
3525 END IF;
3526
3527
3528 -- If exception raised below then this row is not needed
3529 if (stu_rec.c_leg_sgrp is null) then
3530 select null
3531 into l_null_return
3532 from dual
3533 where exists
3534 (select null
3535 from hr_s_application_ownerships a
3536 , fnd_product_installations b
3537 , fnd_application c
3538 where a.key_name = 'USER_TABLE_ID'
3539 and a.key_value = stu_rec.c_surrogate_key
3540 and a.product_name = c.application_short_name
3541 and c.application_id = b.application_id
3542 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3543 or
3544 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
3545 else
3546 select null
3547 into l_null_return
3548 from dual
3549 where exists
3550 (select null
3551 from hr_s_application_ownerships a
3552 , fnd_product_installations b
3553 , fnd_application c
3554 where a.key_name = 'USER_TABLE_ID'
3555 and a.key_value = stu_rec.c_surrogate_key
3556 and a.product_name = c.application_short_name
3557 and c.application_id = b.application_id
3558 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3559 or
3560 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
3561 and exists (select null from hr_legislation_subgroups d
3562 where d.legislation_code = stu_rec.c_leg_code
3563 and d.legislation_subgroup = stu_rec.c_leg_sgrp
3564 and d.active_inactive_flag = 'A'
3565 );
3566 end if;
3567
3568 return TRUE; --indicates row is required
3569
3570 EXCEPTION WHEN NO_DATA_FOUND THEN
3571
3572 -- Row not needed for any installed product
3573
3574
3575 remove;
3576
3577 -- Indicate row not needed
3578
3579 return FALSE;
3580
3581 END valid_ownership;
3582
3583 PROCEDURE transfer_row
3584 ----------------------
3585 IS
3586 -- Check if a delivered row is needed and insert into the
3587 -- live tables if it is
3588
3589 v_inst_update date; -- Holds update details of installed row
3590
3591 BEGIN
3592
3593
3594 BEGIN
3595
3596 -- Perform a check to see if the primary key has been creeated within
3597 -- a visible business group. Ie: the business group is for the same
3598 -- legislation as the delivered row, or the delivered row has a null
3599 -- legislation. If no rows are returned then the primary key has not
3600 -- already been created by a user.
3601
3602 select distinct null
3603 into l_null_return
3604 from pay_user_tables a
3605 where a.user_table_name = stu_rec.c_true_key
3606 and a.business_group_id is not null
3607 and exists (select null from per_business_groups b
3608 where b.business_group_id = a.business_group_id
3609 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
3610
3611 crt_exc('Row already created in a business group');
3612
3613 return;
3614
3615 EXCEPTION WHEN NO_DATA_FOUND THEN
3616
3617 null;
3618
3619 END;
3620
3621
3622 -- Now perform a check to see if this primary key has been installed
3623 -- with a legislation code that would make it visible at the same time
3624 -- as this row. Ie: if any legislation code is null within the set of
3625 -- returned rows, then the transfer may not go ahead. If no rows are
3626 -- returned then the delivered row is fine.
3627 -- G1746. Add the check for business_group_id is null, otherwise the
3628 -- row may be wrongly rejected because it already exists for a
3629 -- specific business group in another legislation. This, though
3630 -- unlikely, is permissible. RMF 05.01.95.
3631
3632 BEGIN
3633
3634 select distinct null
3635 into l_null_return
3636 from pay_user_tables
3637 where user_table_name = stu_rec.c_true_key
3638 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
3639 and (legislation_code is null or stu_rec.c_leg_code is null )
3640 and business_group_id is null;
3641
3642 crt_exc('Row already created for a visible legislation');
3643
3644 return;
3645
3646 EXCEPTION WHEN NO_DATA_FOUND THEN
3647
3648 null;
3649
3650 END;
3651
3652
3653 -- When the procedure is called in phase 1, there is no need to
3654 -- actually perform the transfer from the delivery tables into the
3655 -- live. Hence if phase = 1 control is returned to the calling
3656 -- procedure and the next row is returned.
3657
3658 -- If the procedure is called in phase 2, then the live row is updated
3659 -- with the values on the delivered row.
3660
3661 -- The routine check_parents validates foreign key references and
3662 -- ensures referential integrity. The routine checks to see if the
3663 -- parents of a given row have been transfered to the live tables.
3664
3665 -- This may only be called in phase two since in phase one all
3666 -- parent rows will remain in the delivery tables.
3667
3668 -- After the above checks only data that has been chanegd or is new
3669 -- will be left in the delivery tables. At this stage if the row is
3670 -- already present then it must be updated to ensure referential
3671 -- integrity. Therefore an update will be performed and if SQL%FOUND
3672 -- is FALSE an insert will be performed.
3673
3674 -- The last step of the transfer, in phase 2, is to delete the now
3675 -- transfered row from the delivery tables.
3676
3677 IF p_phase = 1 THEN
3678 return;
3679 END IF;
3680
3681
3682 update pay_user_tables
3683 set range_or_match = stu_rec.range_or_match
3684 , user_key_units = stu_rec.user_key_units
3685 , last_update_date = stu_rec.last_update_date
3686 , last_updated_by = stu_rec.last_updated_by
3687 , last_update_login = stu_rec.last_update_login
3688 , created_by = stu_rec.created_by
3689 , creation_date = stu_rec.creation_date
3690 , user_row_title = stu_rec.user_row_title
3691 where user_table_id = stu_rec.c_surrogate_key;
3692
3693 IF NOT SQL%FOUND THEN
3694
3695 BEGIN
3696 insert into pay_user_tables
3697 (user_table_id
3698 ,business_group_id
3699 ,legislation_code
3700 ,range_or_match
3701 ,user_key_units
3702 ,user_table_name
3703 ,legislation_subgroup
3704 ,last_update_date
3705 ,last_updated_by
3706 ,last_update_login
3707 ,created_by
3708 ,creation_date
3709 ,user_row_title
3710 )
3711 values
3712 (stu_rec.c_surrogate_key
3713 ,stu_rec.business_group_id
3714 ,stu_rec.c_leg_code
3715 ,stu_rec.range_or_match
3716 ,stu_rec.user_key_units
3717 ,stu_rec.c_true_key
3718 ,stu_rec.c_leg_sgrp
3719 ,stu_rec.last_update_date
3720 ,stu_rec.last_updated_by
3721 ,stu_rec.last_update_login
3722 ,stu_rec.created_by
3723 ,stu_rec.creation_date
3724 ,stu_rec.user_row_title
3725 );
3726 EXCEPTION WHEN OTHERS THEN
3727 hr_legislation.hrrunprc_trace_on;
3728 hr_utility.trace('ins pay_user_tables');
3729 hr_utility.trace('user_table_id ' ||
3730 to_char(stu_rec.c_surrogate_key));
3731 hr_utility.trace('user_table_name ' ||
3732 stu_rec.c_true_key);
3733 hr_utility.trace(':lc: ' || ':' ||
3734 stu_rec.c_leg_code || ':');
3735 hr_legislation.hrrunprc_trace_off;
3736 raise;
3737 END;
3738
3739 END IF;
3740
3741
3742 remove;
3743
3744 END transfer_row;
3745
3746 BEGIN
3747
3748 -- This is the main loop to perform the installation logic. A cursor
3749 -- is opened to control the loop, and each row returned is placed
3750 -- into a record defined within the main procedure so each sub
3751 -- procedure has full access to all returrned columns. For each
3752 -- new row returned, a new savepoint is declared. If at any time
3753 -- the row is in error a rollback iss performed to the savepoint
3754 -- and the next row is returned. Ownership details are checked and
3755 -- if the row is required then the surrogate id is updated and the
3756 -- main transfer logic is called.
3757
3758 IF p_phase = 1 THEN
3759 check_next_sequence;
3760 END IF;
3761
3762 FOR delivered IN stu LOOP
3763
3764 -- Uses main cursor stu to impilicity define a record
3765
3766
3767 savepoint new_user_table_name;
3768
3769 stu_rec := delivered;
3770
3771 IF p_phase = 2 THEN
3772 l_new_surrogate_key := stu_rec.c_surrogate_key;
3773 END IF;
3774
3775 IF valid_ownership THEN
3776
3777 -- Test the row onerships for the current row
3778
3779
3780 IF p_phase = 1 THEN
3781 update_uid;
3782 END IF;
3783
3784 transfer_row;
3785
3786 END IF;
3787
3788 END LOOP;
3789
3790 END install_utables;
3791
3792 --****************************************************************************
3793 -- INSTALLATION PROCEDURE FOR : PAY_USER_COLUMNS
3794 --****************************************************************************
3795
3796 PROCEDURE install_ucolumns (p_phase IN number)
3797 ----------------------------------------------
3798 IS
3799 -- Install procedure to transfer startup element classifications into
3800 -- a live account.
3801
3802 l_null_return varchar2(1); -- For 'select null' statements
3803 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3804
3805
3806 CURSOR stu -- Selects all rows from startup entity
3807 IS
3808 --
3809 -- #271139 - note that the user column name is not the true key on
3810 -- its own; it's only unique for the user table.
3811 -- Must use the user table id in select criteria for the true key.
3812 --
3813 select user_column_id c_surrogate_key
3814 , business_group_id
3815 , legislation_code c_leg_code
3816 , user_table_id
3817 , formula_id
3818 , user_column_name c_true_key
3819 , legislation_subgroup c_leg_sgrp
3820 , last_update_date
3821 , last_updated_by
3822 , last_update_login
3823 , created_by
3824 , creation_date
3825 , rowid
3826 from hr_s_user_columns;
3827
3828 stu_rec stu%ROWTYPE;
3829
3830
3831 PROCEDURE check_next_sequence
3832 -----------------------------
3833 IS
3834
3835 v_sequence_number number(15);
3836 v_min_delivered number(15);
3837 v_max_delivered number(15);
3838
3839 -- Surrogate id conflicts may arise from two scenario's:
3840 -- 1. Where the newly select sequence value conflicts with values
3841 -- in the STU tables.
3842 -- 2. Where selected surrogate keys, from the installed tables,
3843 -- conflict with other rows in the STU tables.
3844 --
3845 -- Both of the above scenario's are tested for.
3846 -- The first is a simple match, where if a value is detected in the
3847 -- STU tables and the installed tables then a conflict is detected. In
3848 -- This instance all STU surrogate keys, for this table, are updated.
3849 -- The second is tested for using the sequences.
3850 -- If the next value from the live sequence is within the range of
3851 -- delivered surrogate id's then the live sequence must be incremented.
3852 -- If no action is taken, then duplicates may be introduced into the
3853 -- delivered tables, and child rows may be totally invalidated.
3854
3855 BEGIN
3856
3857
3858 BEGIN --check that the installed id's will not conflict
3859 --with the delivered values
3860
3861
3862 select distinct null
3863 into l_null_return
3864 from pay_user_columns a
3865 where exists
3866 (select null
3867 from hr_s_user_columns b
3868 where a.user_column_id = b.user_column_id
3869 );
3870
3871 --conflict may exist
3872 --update all user_column_id's to remove conflict
3873
3874 update /*+NO_INDEX*/ hr_s_user_columns
3875 set user_column_id = user_column_id - 50000000;
3876
3877 update /*+NO_INDEX*/ hr_s_user_column_instances_f
3878 set user_column_id = user_column_id - 50000000;
3879
3880 update hr_s_application_ownerships
3881 set key_value = key_value - 50000000
3882 where key_name = 'USER_COLUMN_ID';
3883
3884 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3885
3886 END; --check of user_column_id
3887
3888
3889
3890 select min(user_column_id) - (count(*) *3)
3891 , max(user_column_id) + (count(*) *3)
3892 into v_min_delivered
3893 , v_max_delivered
3894 from hr_s_user_columns;
3895
3896 select pay_user_columns_s.nextval
3897 into v_sequence_number
3898 from dual;
3899
3900 IF v_sequence_number
3901 BETWEEN v_min_delivered AND v_max_delivered
3902 THEN
3903
3904 hr_legislation.munge_sequence('PAY_USER_COLUMNS_S',
3905 v_sequence_number,
3906 v_max_delivered);
3907
3908 END IF;
3909
3910 END check_next_sequence;
3911
3912
3913 PROCEDURE crt_exc (exception_type IN varchar2)
3914 ----------------------------------------------
3915 IS
3916 -- Reports any exceptions during the delivery of startup data to
3917 -- PAY_USER_COLUMNS
3918
3919 BEGIN
3920 -- When the installation procedures encounter an error that cannot
3921 -- be handled, an exception is raised and all work is rolled back
3922 -- to the last savepoint. The installation process then continues
3923 -- with the next primary key to install. The same exception will
3924 -- not be raised more than once.
3925
3926
3927 rollback to new_user_column_name;
3928
3929 hr_legislation.insert_hr_stu_exceptions('pay_user_columns'
3930 , stu_rec.c_surrogate_key
3931 , exception_type
3932 , stu_rec.c_true_key);
3933
3934
3935 END crt_exc;
3936
3937 PROCEDURE update_uid
3938 --------------------
3939 IS
3940 -- subprogram to update surrogate UID and all occurrences in child rows
3941
3942 BEGIN
3943
3944
3945 BEGIN
3946 --
3947 -- #271139 - hitting a problem because the user column name is
3948 -- not the true key on its own; it's only unique for the user table.
3949 -- Add the user table id to the select criteria.
3950 --
3951 select distinct user_column_id
3952 into l_new_surrogate_key
3953 from pay_user_columns
3954 where user_column_name = stu_rec.c_true_key
3955 and user_table_id = stu_rec.user_table_id
3956 and business_group_id is null
3957 and ( (legislation_code is null
3958 and stu_rec.c_leg_code is null)
3959 or (legislation_code = stu_rec.c_leg_code) );
3960
3961 EXCEPTION WHEN NO_DATA_FOUND THEN
3962
3963
3964 select pay_user_columns_s.nextval
3965 into l_new_surrogate_key
3966 from dual;
3967
3968 WHEN TOO_MANY_ROWS THEN
3969 hr_legislation.hrrunprc_trace_on;
3970 hr_utility.trace('sel pay_user_columns TMR');
3971 hr_utility.trace('user_column_name ' ||
3972 stu_rec.c_true_key);
3973 hr_utility.trace('user_table_id ' ||
3974 to_char(stu_rec.user_table_id));
3975 hr_utility.trace(':lc: ' || ':' ||
3976 stu_rec.c_leg_code || ':');
3977 hr_legislation.hrrunprc_trace_off;
3978 raise;
3979 END;
3980
3981 -- Update all child entities
3982
3983 update hr_s_user_columns
3984 set user_column_id = l_new_surrogate_key
3985 where user_column_id = stu_rec.c_surrogate_key;
3986
3987 update hr_s_application_ownerships
3988 set key_value = to_char(l_new_surrogate_key)
3989 where key_value = to_char(stu_rec.c_surrogate_key)
3990 and key_name = 'USER_COLUMN_ID';
3991
3992 update hr_s_user_column_instances_f
3993 set user_column_id = l_new_surrogate_key
3994 where user_column_id = stu_rec.c_surrogate_key;
3995
3996 END update_uid;
3997
3998 PROCEDURE remove
3999 ----------------
4000 IS
4001 -- Remove a row from either the startup tables or the installed tables
4002
4003 BEGIN
4004
4005 delete from hr_s_user_columns
4006 where rowid = stu_rec.rowid;
4007
4008 END remove;
4009
4010 FUNCTION valid_ownership RETURN BOOLEAN
4011 ---------------------------------------
4012 IS
4013 -- Test ownership of this current row
4014
4015 -- This routine only operates in phase 1. Rows are present in the
4016 -- table hr_application_ownerships in the delivery account, which
4017 -- dictate which products a piece of data is used for. If the query
4018 -- returns a rowm then this data is required, and the function will
4019 -- return true. If no rows are returned and an exception is raised,
4020 -- then this row is not required and may be deleted from the delivery
4021 -- tables.
4022
4023 -- If legislation code and subgroup code are included on the delivery
4024 -- tables, a check must be made to determine if the data is defined for
4025 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
4026 -- installation.
4027
4028 -- A return code of TRUE indicates that the row is required.
4029
4030 -- The exception is raised within this procedure if no rows are returned
4031 -- in this select statement. If no rows are returned then one of the
4032 -- following is true:
4033 -- 1. No ownership parameters are defined.
4034 -- 2. The products, for which owning parameters are defined, are not
4035 -- installed with as status of 'I'.
4036 -- 3. The data is defined for a legislation subgroup that is not active.
4037
4038 BEGIN
4039
4040
4041 IF p_phase <> 1 THEN
4042 return TRUE;
4043 END IF;
4044
4045
4046 -- If exception raised below hen this row is not needed
4047 if (stu_rec.c_leg_sgrp is null) then
4048 select null
4049 into l_null_return
4050 from dual
4051 where exists
4052 (select null
4053 from hr_s_application_ownerships a
4054 , fnd_product_installations b
4055 , fnd_application c
4056 where a.key_name = 'USER_COLUMN_ID'
4057 and a.key_value = stu_rec.c_surrogate_key
4058 and a.product_name = c.application_short_name
4059 and c.application_id = b.application_id
4060 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4061 or
4062 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4063 else
4064 select null
4065 into l_null_return
4066 from dual
4067 where exists
4068 (select null
4069 from hr_s_application_ownerships a
4070 , fnd_product_installations b
4071 , fnd_application c
4072 where a.key_name = 'USER_COLUMN_ID'
4073 and a.key_value = stu_rec.c_surrogate_key
4074 and a.product_name = c.application_short_name
4075 and c.application_id = b.application_id
4076 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4077 or
4078 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
4079 and exists (select null from hr_legislation_subgroups d
4080 where d.legislation_code = stu_rec.c_leg_code
4081 and d.legislation_subgroup = stu_rec.c_leg_sgrp
4082 and d.active_inactive_flag = 'A'
4083 );
4084 end if;
4085
4086 return TRUE; --indicates row is required
4087
4088 EXCEPTION WHEN NO_DATA_FOUND THEN
4089
4090 -- Row not needed for any installed product
4091
4092
4093 remove;
4094
4095 -- Indicates row not needed
4096
4097 return FALSE;
4098
4099 END valid_ownership;
4100
4101 FUNCTION check_parents RETURN BOOLEAN
4102 -------------------------------------
4103 IS
4104 -- Check if parent data is correct
4105
4106 BEGIN
4107
4108 -- This procedure is only called in phase 2. The logic to check if
4109 -- a given parental foreign key exists is split into two parts for
4110 -- every foriegn key. The first select from the delivery tables.
4111
4112 -- If a row is founnd then the installation of the parent must have
4113 -- failed, and this installation must not go ahead. If no data is
4114 -- found, ie: an exception is raised, the installation is valid.
4115
4116 -- The second check looks for a row in the live tables. If no rows
4117 -- are returned then this installation is invalid, since this means
4118 -- that the parent referenced by this row is not present in the
4119 -- live tables.
4120
4121 -- The distinct is used in case the parent is date effective and many rows
4122 -- may be returned by the same parent id.
4123
4124 -- Start with checking the parent PAY_USER_TABLES
4125
4126
4127 BEGIN
4128
4129 -- Check the tables in the delivery account
4130
4131 select distinct null
4132 into l_null_return
4133 from hr_s_user_tables
4134 where user_table_id = stu_rec.user_table_id;
4135
4136 crt_exc('Parent user table remains in delivery tables');
4137
4138 -- Parent row still in startup account
4139
4140 return FALSE;
4141
4142 EXCEPTION WHEN NO_DATA_FOUND THEN
4143
4144 -- Probably transferred?
4145
4146 null;
4147
4148 END;
4149
4150
4151 BEGIN
4152
4153 select null
4154 into l_null_return
4155 from pay_user_tables
4156 where user_table_id = stu_rec.user_table_id;
4157
4158 EXCEPTION WHEN NO_DATA_FOUND THEN
4159
4160 -- Parent not installed
4161
4162
4163 crt_exc('Parent user table not installed');
4164
4165 return FALSE;
4166
4167 END;
4168
4169 IF stu_rec.formula_id is null THEN
4170 -- No need to check parent formula
4171 return TRUE;
4172 END IF;
4173
4174 -- Now check the parent FF_FORMULAS_F
4175
4176
4177 BEGIN
4178
4179 -- Check the tables in the delivery account
4180
4181 select distinct null
4182 into l_null_return
4183 from hr_s_formulas_f
4184 where formula_id = stu_rec.formula_id;
4185
4186 crt_exc('Parent formula remains in delivery tables');
4187
4188 -- Parent row still in startup account
4189
4190 return FALSE;
4191
4192 EXCEPTION WHEN NO_DATA_FOUND THEN
4193
4194 -- Probably transferred?
4195
4196 null;
4197
4198 END;
4199
4200
4201 BEGIN
4202 select null
4203 into l_null_return
4204 from ff_Formulas_f
4205 where formula_id = stu_rec.formula_id;
4206 return TRUE;
4207
4208 EXCEPTION WHEN NO_DATA_FOUND THEN
4209
4210 -- Parent not installed
4211
4212
4213 crt_exc('Parent formula not installed');
4214
4215 return FALSE;
4216
4217 END;
4218
4219 END check_parents;
4220
4221 PROCEDURE transfer_row
4222 ----------------------
4223 IS
4224 -- Check if a delivered row is needed and insert into the
4225 -- live tables if it is
4226
4227 v_inst_update date; -- Holds update details of installed row
4228
4229 BEGIN
4230
4231
4232 BEGIN
4233
4234 -- Perform a check to see if the primary key has been created within
4235 -- a visible business group. Ie: the business group is for the same
4236 -- legislation as the delivered row, or the delivered row has a null
4237 -- legislation. If no rows are returned then the primary key has not
4238 -- already been created by a user.
4239 --
4240 -- #271139 - hitting a problem because the user column name is
4241 -- not the true key on its own; it's only unique for the user table.
4242 -- Add the user table id to the select criteria.
4243 --
4244 select distinct null
4245 into l_null_return
4246 from pay_user_columns a
4247 where a.user_table_id = stu_rec.user_table_id
4248 and a.user_column_name = stu_rec.c_true_key
4249 and a.business_group_id is not null
4250 and exists (select null from per_business_groups b
4251 where b.business_group_id = a.business_group_id
4252 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
4253
4254 crt_exc('Row already created in a business group');
4255
4256 -- Indicates this row is not to be transferred
4257
4258 return;
4259
4260 EXCEPTION WHEN NO_DATA_FOUND THEN
4261
4262 null;
4263
4264 END;
4265
4266
4267 -- Now perform a check to see if this primary key has been installed
4268 -- with a legislation code that would make it visible at the same time
4269 -- as this row. Ie: if any legislation code is null within the set of
4270 -- returned rows, then the transfer may not go ahead. If no rows are
4271 -- returned then the delivered row is fine.
4272 -- G1746. Add the check for business_group_id is null, otherwise the
4273 -- row may be wrongly rejected because it already exists for a
4274 -- specific business group in another legislation. This, though
4275 -- unlikely, is permissible. RMF 05.01.95.
4276 --
4277 -- #271139 - hitting a problem because the user column name is
4278 -- not the true key on its own; it's only unique for the user table.
4279 -- Add the user table id to the select criteria.
4280 --
4281 BEGIN
4282 select distinct null
4283 into l_null_return
4284 from pay_user_columns
4285 where user_column_name = stu_rec.c_true_key
4286 and user_table_id = stu_rec.user_table_id
4287 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
4288 and (legislation_code is null or stu_rec.c_leg_code is null )
4289 and business_group_id is null;
4290
4291 crt_exc('Row already created for a visible legislation');
4292
4293 -- Indicates this row is not to be transferred
4294
4295 return;
4296
4297 EXCEPTION WHEN NO_DATA_FOUND THEN
4298
4299 null;
4300
4301 END;
4302
4303
4304 -- When the procedure is called in phase 1, there is no need to
4305 -- actually perform the transfer from the delivery tables into the
4306 -- live. Hence if phase = 1 control is returned to the calling
4307 -- procedure and the next row is returned.
4308
4309 -- If the procedure is called in phase 2, then the live row is updated
4310 -- with the values on the delivered row.
4311
4312 -- The routine check_parents validates foreign key references and
4313 -- ensures referential integrity. The routine checks to see if the
4314 -- parents of a given row have been transfered to the live tables.
4315
4316 -- This may only be called in phase two since in phase one all
4317 -- parent rows will remain in the delivery tables.
4318
4319 -- After the above checks only data that has been chanegd or is new
4320 -- will be left in the delivery tables. At this stage if the row is
4321 -- already present then it must be updated to ensure referential
4322 -- integrity. Therefore an update will be performed and if SQL%FOUND
4323 -- is FALSE an insert will be performed.
4324
4325 -- The last step of the transfer, in phase 2, is to delete the now
4326 -- transfered row from the delivery tables.
4327
4328 IF p_phase = 1 THEN
4329 return;
4330 END IF;
4331
4332 IF NOT check_parents THEN
4333 return;
4334 END IF;
4335
4336
4337 update pay_user_columns
4338 set formula_id = stu_rec.formula_id
4339 , last_update_date = stu_rec.last_update_date
4340 , last_updated_by = stu_rec.last_updated_by
4341 , last_update_login = stu_rec.last_update_login
4342 , created_by = stu_rec.created_by
4343 , creation_date = stu_rec.creation_date
4344 where user_column_id = stu_rec.c_surrogate_key;
4345
4346 IF NOT SQL%FOUND THEN
4347
4348 BEGIN
4349 insert into pay_user_columns
4350 (user_column_id
4351 ,business_group_id
4352 ,legislation_code
4353 ,user_table_id
4354 ,formula_id
4355 ,user_column_name
4356 ,legislation_subgroup
4357 ,last_update_date
4358 ,last_updated_by
4359 ,last_update_login
4360 ,created_by
4361 ,creation_date
4362 )
4363 values
4364 (stu_rec.c_surrogate_key
4365 ,stu_rec.business_group_id
4366 ,stu_rec.c_leg_code
4367 ,stu_rec.user_table_id
4368 ,stu_rec.formula_id
4369 ,stu_rec.c_true_key
4370 ,stu_rec.c_leg_sgrp
4371 ,stu_rec.last_update_date
4372 ,stu_rec.last_updated_by
4373 ,stu_rec.last_update_login
4374 ,stu_rec.created_by
4375 ,stu_rec.creation_date
4376 );
4377 EXCEPTION WHEN OTHERS THEN
4378 hr_legislation.hrrunprc_trace_on;
4379 hr_utility.trace('ins pay_user_columns');
4380 hr_utility.trace('user_column_id ' ||
4381 to_char(stu_rec.c_surrogate_key));
4382 hr_utility.trace('user_column_name ' ||
4383 stu_rec.c_true_key);
4384 hr_utility.trace('user_table_id ' ||
4385 to_char(stu_rec.user_table_id));
4386 hr_utility.trace(':lc: ' || ':' ||
4387 stu_rec.c_leg_code || ':');
4388 hr_legislation.hrrunprc_trace_off;
4389 raise;
4390 END;
4391
4392 END IF;
4393
4394 remove;
4395
4396 END transfer_row;
4397
4398 BEGIN
4399 -- This is the main loop to perform the installation logic. A cursor
4400 -- is opened to control the loop, and each row returned is placed
4401 -- into a record defined within the main procedure so each sub
4402 -- procedure has full access to all returrned columns. For each
4403 -- new row returned, a new savepoint is declared. If at any time
4404 -- the row is in error a rollback iss performed to the savepoint
4405 -- and the next row is returned. Ownership details are checked and
4406 -- if the row is required then the surrogate id is updated and the
4407 -- main transfer logic is called.
4408
4409 IF p_phase = 1 THEN
4410 check_next_sequence;
4411 END IF;
4412
4413 FOR delivered IN stu LOOP
4414
4415 -- Uses main cursor stu to implicity define a record
4416
4417
4418 savepoint new_user_column_name;
4419
4420 stu_rec := delivered;
4421
4422 IF p_phase = 2 THEN
4423 l_new_surrogate_key := stu_rec.c_surrogate_key;
4424 END IF;
4425
4426 IF valid_ownership THEN
4427
4428 -- Test the row onerships for the current row
4429
4430
4431 IF p_phase = 1 THEN
4432 update_uid;
4433 END IF;
4434
4435 transfer_row;
4436
4437 END IF;
4438
4439 END LOOP;
4440
4441 END install_ucolumns;
4442
4443 --*******************************************************************
4444 -- OVERALL INSTALLATION PROCEDURE
4445 --*******************************************************************
4446
4447 PROCEDURE install(p_phase number)
4448 ---------------------------------
4449 IS
4450 -- Driver procedure to control the execution of all installation procedures.
4451
4452 BEGIN
4453
4454 IF p_phase = 1 OR p_phase =2 THEN
4455
4456 hr_legislation.hrrunprc_trace_on;
4457 hr_utility.trace('start install_ele_class: ' || to_char(p_phase));
4458 hr_legislation.hrrunprc_trace_off;
4459
4460 install_ele_class(p_phase); --install element classifications
4461
4462 hr_legislation.hrrunprc_trace_on;
4463 hr_utility.trace('start install_elements: ' || to_char(p_phase));
4464 hr_legislation.hrrunprc_trace_off;
4465
4466 install_elements(p_phase); --install elements,sprs,frrs,inputs
4467
4468 hr_legislation.hrrunprc_trace_on;
4469 hr_utility.trace('start install_ele_sets: ' || to_char(p_phase));
4470 hr_legislation.hrrunprc_trace_off;
4471
4472 install_ele_sets(p_phase); --install sets,type rules,class rules
4473
4474 hr_legislation.hrrunprc_trace_on;
4475 hr_utility.trace('start install_utables: ' || to_char(p_phase));
4476 hr_legislation.hrrunprc_trace_off;
4477
4478 install_utables(p_phase); --install user tables
4479
4480 hr_legislation.hrrunprc_trace_on;
4481 hr_utility.trace('start install_ucolumns: ' || to_char(p_phase));
4482 hr_legislation.hrrunprc_trace_off;
4483
4484 install_ucolumns(p_phase); --install user columns
4485
4486 END IF;
4487
4488 END install;
4489
4490 END hr_legislation_elements;