4 -- INSTALLATION PROCEDURE FOR : FF_CONTEXTS_F
1 PACKAGE body ff_data_dict AS
2 /* $Header: peffdict.pkb 120.17.12020000.5 2013/03/20 16:49:20 divicker ship $ */
3 --****************************************************************************
5 --****************************************************************************
6
7 procedure disable_ffuebru_trig is
8 statem varchar2(256);
9 sql_cur number;
10 ignore number;
11 begin
12 statem := 'alter trigger ff_user_entities_bru disable';
13 sql_cur := dbms_sql.open_cursor;
14 dbms_sql.parse(sql_cur,
15 statem,
16 dbms_sql.v7);
17 ignore := dbms_sql.execute(sql_cur);
18 dbms_sql.close_cursor(sql_cur);
19 end;
20
21 procedure enable_ffuebru_trig is
22 statem varchar2(256);
23 sql_cur number;
24 ignore number;
25 begin
26 statem := 'alter trigger ff_user_entities_bru enable';
27 sql_cur := dbms_sql.open_cursor;
28 dbms_sql.parse(sql_cur,
29 statem,
30 dbms_sql.v7);
31 ignore := dbms_sql.execute(sql_cur);
32 dbms_sql.close_cursor(sql_cur);
33 end;
34
35 procedure dis_cont_calc_trigger is
36 statem varchar2(256);
37 sql_cur number;
38 ignore number;
39 begin
40 statem := 'alter trigger FFGLOBALSF_9501D_DYT disable';
41 sql_cur := dbms_sql.open_cursor;
42 dbms_sql.parse(sql_cur,
43 statem,
44 dbms_sql.v7);
45 ignore := dbms_sql.execute(sql_cur);
46 dbms_sql.close_cursor(sql_cur);
47 statem := 'alter trigger FFGLOBALSF_9502I_DYT disable';
48 sql_cur := dbms_sql.open_cursor;
49 dbms_sql.parse(sql_cur,
50 statem,
51 dbms_sql.v7);
52 ignore := dbms_sql.execute(sql_cur);
53 dbms_sql.close_cursor(sql_cur);
54 statem := 'alter trigger FFGLOBALSF_9503U_DYT disable';
55 sql_cur := dbms_sql.open_cursor;
56 dbms_sql.parse(sql_cur,
57 statem,
58 dbms_sql.v7);
59 ignore := dbms_sql.execute(sql_cur);
60 dbms_sql.close_cursor(sql_cur);
61 end;
62
63 procedure ena_cont_calc_trigger is
64 statem varchar2(256);
65 sql_cur number;
66 ignore number;
67 begin
68 statem := 'alter trigger FFGLOBALSF_9501D_DYT enable';
69 sql_cur := dbms_sql.open_cursor;
70 dbms_sql.parse(sql_cur,
71 statem,
72 dbms_sql.v7);
73 ignore := dbms_sql.execute(sql_cur);
74 dbms_sql.close_cursor(sql_cur);
75 statem := 'alter trigger FFGLOBALSF_9502I_DYT enable';
76 sql_cur := dbms_sql.open_cursor;
77 dbms_sql.parse(sql_cur,
78 statem,
79 dbms_sql.v7);
80 ignore := dbms_sql.execute(sql_cur);
81 dbms_sql.close_cursor(sql_cur);
82 statem := 'alter trigger FFGLOBALSF_9503U_DYT enable';
86 dbms_sql.v7);
83 sql_cur := dbms_sql.open_cursor;
84 dbms_sql.parse(sql_cur,
85 statem,
87 ignore := dbms_sql.execute(sql_cur);
88 dbms_sql.close_cursor(sql_cur);
89 end;
90
91
92 PROCEDURE install_ffc (p_phase IN number)
93 -----------------------------------------
94 IS
95 -- Install delivered formula contexts into the live tables. This delivery
96 -- procedure maintains referential integrity with all children of formula
97 -- contetxs. It must be executed before any other formula dictionary
98 -- routine.
99
100 l_null_return varchar2(1); -- for 'select null' statements
101 l_new_surrogate_key number(15); -- new surrogate key for delivery row
102
103 CURSOR stu -- selects all rows from startup entity
104 IS
105 select context_name c_true_key
106 , rowid
107 , context_level
108 , data_type
109 , context_id c_surrogate_key
110 from hr_s_contexts;
111
112
113 stu_rec stu%ROWTYPE; -- Record for above SELECT
114
115 PROCEDURE crt_exc (exception_type IN varchar2)
116 ----------------------------------------------
117 IS
118 -- Reports any exceptions during the delivery of startup data to
119 -- FF_CONTEXTS_F
120 BEGIN
121 -- When the installation procedures encounter an error that cannot
122 -- be handled, an exception is raised and all work is rolled back
123 -- to the last savepoint. The installation process then continues
124 -- with the next primary key to install. The same exception will
125 -- not be raised more than once.
126
127 rollback to new_context_name;
128
129 hr_legislation.insert_hr_stu_exceptions('ff_contexts'
130 , stu_rec.c_surrogate_key
131 , exception_type
132 , stu_rec.c_true_key);
133
134
135 END crt_exc;
136
137 PROCEDURE check_next_sequence
138 -----------------------------
139 IS
140
141 v_sequence_number number(9);
142 v_min_delivered number(9);
143 v_max_delivered number(9);
144
145 -- Surrogate id conflicts may arise from two scenario's:
146 -- 1. Where the newly select sequence value conflicts with values
147 -- in the STU tables.
148 -- 2. Where selected surrogate keys, from the installed tables,
149 -- conflict with other rows in the STU tables.
150 --
151 -- Both of the above scenario's are tested for.
152 -- The first is a simple match, where if a value is detected in the
153 -- STU tables and the installed tables then a conflict is detected. In
154 -- This instance all STU surrogate keys, for this table, are updated.
155 -- The second is tested for using the sequences.
156 -- If the next value from the live sequence is within the range of
157 -- delivered surrogate id's then the live sequence must be incremented.
158 -- If no action is taken, then duplicates may be introduced into the
159 -- delivered tables, and child rows may be totally invalidated.
160
161 BEGIN
162
163
164 BEGIN --check that the installde routes will not conflict
165 --with the delivered values
166
167
168 select distinct null
169 into l_null_return
170 from ff_contexts a
171 where exists
172 (select null
173 from hr_s_contexts b
174 where a.context_id = b.context_id
175 );
176
177 --conflict may exist
178 --update all context_id's to remove conflict
179
180 update /*+NO_INDEX*/ hr_s_contexts
181 set context_id = context_id - 50000000;
182
183 update /*+NO_INDEX*/ hr_s_route_context_usages
184 set context_id = context_id - 50000000;
185
186 update /*+NO_INDEX*/ hr_s_ftype_context_usages
187 set context_id = context_id - 50000000;
188
189 update /*+NO_INDEX*/ hr_s_function_context_usages
190 set context_id = context_id - 50000000;
191
192 update hr_s_application_ownerships
193 set key_value = key_value - 50000000
194 where key_name = 'CONTEXT_ID';
195
196 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
197
198 END; --check of context_id
199
200
201
202 select min(context_id) - (count(*) *3)
203 , max(context_id) + (count(*) *3)
204 into v_min_delivered
205 , v_max_delivered
206 from hr_s_contexts;
207
208 select ff_contexts_s.nextval
209 into v_sequence_number
210 from dual;
211
212 IF v_sequence_number
213 BETWEEN v_min_delivered AND v_max_delivered THEN
214
215 hr_legislation.munge_sequence('FF_CONTEXTS_S',
216 v_sequence_number,
217 v_max_delivered);
218
219 END IF;
220
221 END check_next_sequence;
222
223 PROCEDURE update_uid
224 --------------------
225 IS
226 -- Subprogram to update surrogate UID and all occurrences in child rows
227
228 r_count NUMBER;
229
230 BEGIN
231
232
233 BEGIN
234
235 select distinct context_id
236 into l_new_surrogate_key
237 from ff_contexts
238 where context_name = stu_rec.c_true_key;
239
240 EXCEPTION WHEN NO_DATA_FOUND THEN
241
242
243 select ff_contexts_s.nextval
244 into l_new_surrogate_key
245 from dual;
246
247 END;
248
249 -- Update all child entities
250
251 update hr_s_contexts
255 update hr_s_application_ownerships
252 set context_id = l_new_surrogate_key
253 where context_id = stu_rec.c_surrogate_key;
254
256 set key_value = to_char(l_new_surrogate_key)
257 where key_value = to_char(stu_rec.c_surrogate_key)
258 and key_name = 'CONTEXT_ID';
259
260 update hr_s_ftype_context_usages
261 set context_id = l_new_surrogate_key
262 where context_id = stu_rec.c_surrogate_key;
263
264 update hr_s_route_context_usages
265 set context_id = l_new_surrogate_key
266 where context_id = stu_rec.c_surrogate_key;
267
268 update hr_s_function_context_usages
269 set context_id = l_new_surrogate_key
270 where context_id = stu_rec.c_surrogate_key;
271
272 END update_uid;
273
274 PROCEDURE remove
275 ----------------
276 IS
277 -- Remove a row from the startup/delivered tables
278
279 BEGIN
280
281 delete from hr_s_contexts
282 where rowid = stu_rec.rowid;
283
284
285 IF p_phase = 2 THEN
286 l_new_surrogate_key := stu_rec.c_surrogate_key;
287 END IF;
288
289 delete from hr_stu_exceptions
290 where surrogate_id = stu_rec.c_surrogate_key
291 and table_name = 'FF_CONTEXTS';
292
293 END remove;
294
295 FUNCTION valid_ownership RETURN BOOLEAN
296 ---------------------------------------
297 IS
298 -- Test ownership of this current row
299
300 BEGIN
301 -- This routine only operates in phase 1. Rows are present in the
302 -- table hr_application_ownerships in the delivery account, which
303 -- dictate which products a piece of data is used for. If the query
304 -- returns a row, then this data is required, and the function will
305 -- return true. If no rows are returned and an exception is raised,
306 -- then this row is not required and may be deleted from the delivery
307 -- tables.
308
309 -- If legislation code and subgroup code are included on the delivery
310 -- tables, a check must be made to determine if the data is defined for
311 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
312 -- installation.
313
314 -- A return code of TRUE indicates that the row is required.
315
316 -- The exception is raised within this procedure if no rows are returned
317 -- in this select statement. If no rows are returned then one of the
318 -- following is true:
319 -- 1. No ownership parameters are defined.
320 -- 2. The products, for which owning parameters are defined, are not
321 -- installed with as status of 'I'.
322 -- 3. The data is defined for a legislation subgroup that is not active.
323
324 IF p_phase <> 1 THEN --only perform in phase 1
325 return TRUE;
326 END IF;
327
328 select null --if exception raised then this row is not needed
329 into l_null_return
330 from dual
331 where exists (
332 select null
333 from hr_s_application_ownerships a
334 , fnd_product_installations b
335 , fnd_application c
336 where a.key_name = 'CONTEXT_ID'
337 and a.key_value = l_new_surrogate_key
338 and a.product_name = c.application_short_name
339 and c.application_id = b.application_id
340 and ((b.status = 'I' and c.application_short_name <> 'PQP')
341 or
342 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
343
344 return TRUE; --indicates row is required
345
346 EXCEPTION WHEN NO_DATA_FOUND THEN
347
348 -- Row not needed for any installed product
349
350 remove;
351
352 return FALSE; --indicates row not needed
353
354 END valid_ownership;
355
356 PROCEDURE transfer_row
357 ----------------------
358 IS
359 -- Check if a delivered row is needed and insert into the
360 -- live tables if it is.
361
362 BEGIN
363
364 -- A simplistic installation. If the context name already exists
365 -- in the live tables then no exception is raised and the row is
366 -- delete from the delivery tables. If the context name does not
367 -- exist then an exception is raised. The actual insert only occurs
368 -- in phase 2 and after the insert has been performed the delivered
369 -- row must be deleted.
370
371 select null
372 into l_null_return
373 from ff_contexts
374 where context_name = stu_rec.c_true_key;
375
376
377 -- Row not required so delete
378
379 remove;
380
381 EXCEPTION WHEN NO_DATA_FOUND THEN
382
383 -- Row not present in installed tables
384
385
386 -- No inserts in phase 1
387
388 IF p_phase = 1 THEN
389 return;
390 END IF;
391
392 BEGIN
393 insert into ff_contexts
394 (context_id
395 ,context_level
396 ,context_name
397 ,data_type
398 )
399 values
400 (stu_rec.c_surrogate_key
401 ,stu_rec.context_level
402 ,stu_rec.c_true_key
403 ,stu_rec.data_type);
404 EXCEPTION WHEN OTHERS THEN
405 hr_legislation.hrrunprc_trace_on;
406 hr_utility.trace('ins ff_contexts');
407 hr_utility.trace('context_id ' ||
408 to_char(stu_rec.c_surrogate_key));
412 to_char(stu_rec.context_level));
409 hr_utility.trace('context_name ' ||
410 stu_rec.c_true_key);
411 hr_utility.trace('context_level ' ||
413 hr_utility.trace('datatype ' ||
414 stu_rec.data_type);
415 hr_legislation.hrrunprc_trace_off;
416 raise;
417 END;
418
419
420 -- Delete delivered row now it has been installed
421
422 remove;
423
424 END transfer_row;
425
426 BEGIN
427
428 -- This is the main loop to perform the installation logic. A cursor
429 -- is opened to control the loop, and each row returned is placed
430 -- into a record defined within the main procedure so each sub
431 -- procedure has full access to all returrned columns. For each
432 -- new row returned, a new savepoint is declared. If at any time
433 -- the row is in error a rollback is performed to the savepoint
434 -- and the next row is returned. Ownership details are checked and
435 -- if the row is required then the surrogate id is updated and the
436 -- main transfer logic is called.
437
438 IF p_phase = 1 THEN
439 check_next_sequence;
440 END IF;
441
442 FOR delivered IN stu LOOP
443
444 -- Uses main cursor stu to impilicity define a record
445
446
447 savepoint new_context_name;
448
449 -- Make all cursor columns available to all procedures
450
451 stu_rec := delivered;
452
453 IF p_phase = 1 THEN update_uid; END IF;
454
455 IF valid_ownership THEN
456
457 -- Test the row onerships for the current row
458
459
460 transfer_row;
461
462 END IF;
463
464 END LOOP;
465
466 END install_ffc;
467
468 --****************************************************************************
469 -- INSTALLATION PROCEDURE FOR : FF_FORMULA_TYPES_S
470 --****************************************************************************
471
472 PROCEDURE install_fft (p_phase IN number)
473 -----------------------------------------
474 IS
475 -- Install startup formula types into a live account. The procedure compares
476 -- delivered types to live types. If different the delivered types will be
477 -- installed.
478
479 l_null_return varchar2(1); -- For 'select null' statements
480 l_new_surrogate_key number(15); -- New surrogate key for delivery row
481 l_old_surrogate_key number(15);
482 l_number_of_ftcu number;
483
484 CURSOR stu -- Selects all rows from startup entity
485 IS
486 select formula_type_name c_true_key
487 , rowid
488 , formula_type_id c_surrogate_key
489 , type_description
490 , last_update_date
491 , last_updated_by
492 , last_update_login
493 , created_by
494 , creation_date
495 from hr_s_formula_types;
496
497 CURSOR ftcu (p_ftype_id number) -- Cursor for install context usages
498 IS
499 select *
500 from hr_s_ftype_context_usages
501 where formula_type_id = p_ftype_id;
502
503 stu_rec stu%ROWTYPE; -- Record definition for cursor select
504
505
506 PROCEDURE crt_exc (exception_type IN varchar2)
507 ----------------------------------------------
508 IS
509 -- Reports any exceptions during the delivery of startup data to
510 -- FF_FORMULA_TYPES_S
511
512 BEGIN
513
514 -- When the installation procedures encounter an error that cannot
515 -- be handled, an exception is raised and all work is rolled back
516 -- to the last savepoint. The installation process then continues
517 -- with the next primary key to install. The same exception will
518 -- not be raised more than once.
519
520
521 rollback to new_formula_type_name;
522
523 hr_legislation.insert_hr_stu_exceptions('ff_formula_types'
524 , stu_rec.c_surrogate_key
525 , exception_type
526 , stu_rec.c_true_key);
527
528
529 END crt_exc;
530
531 PROCEDURE check_next_sequence
532 -----------------------------
533 IS
534 CURSOR c_fft1 IS
535 select distinct null
536 from ff_formula_types a
537 where exists
538 (select null
539 from hr_s_formula_types b
540 where a.formula_type_id = b.formula_type_id
541 );
542 --
543 v_sequence_number number(9);
544 v_min_delivered number(9);
545 v_max_delivered number(9);
546
547 -- Surrogate id conflicts may arise from two scenario's:
548 -- 1. Where the newly select sequence value conflicts with values
549 -- in the STU tables.
550 -- 2. Where selected surrogate keys, from the installed tables,
551 -- conflict with other rows in the STU tables.
552 --
553 -- Both of the above scenario's are tested for.
554 -- The first is a simple match, where if a value is detected in the
555 -- STU tables and the installed tables then a conflict is detected. In
556 -- This instance all STU surrogate keys, for this table, are updated.
557 -- The second is tested for using the sequences.
558 -- If the next value from the live sequence is within the range of
559 -- delivered surrogate id's then the live sequence must be incremented.
563
560 -- If no action is taken, then duplicates may be introduced into the
561 -- delivered tables, and child rows may be totally invalidated.
562 -- This procedure will check three sequences
564 BEGIN
565
566
567 BEGIN --check that the installde routes will not conflict
568 --with the delivered values
569
570 --
571 open c_fft1;
572 fetch c_fft1 into l_null_return;
573 IF c_fft1%NOTFOUND OR c_fft1%NOTFOUND IS NULL THEN
574 RAISE NO_DATA_FOUND;
575 END IF;
576 close c_fft1;
577 --
578 --conflict may exist
579 --update all formula_type_id's to remove conflict
580
581 update hr_s_formula_types
582 set formula_type_id = formula_type_id - 50000000;
583
584 update hr_s_formulas_f
585 set formula_type_id = formula_type_id - 50000000;
586
587 update hr_s_ftype_context_usages
588 set formula_type_id = formula_type_id - 50000000;
589
590 update hr_s_qp_reports
591 set formula_type_id = formula_type_id - 50000000;
592
593 update hr_s_application_ownerships
594 set key_value = key_value - 50000000
595 where key_name = 'FORMULA_TYPE_ID';
596
597 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
598
599 END; --check of formula_type_id
600
601
602
603 select min(formula_type_id) - (count(*) *3)
604 , max(formula_type_id) + (count(*) *3)
605 into v_min_delivered
606 , v_max_delivered
607 from hr_s_formula_types;
608
609 select ff_formula_types_s.nextval
610 into v_sequence_number
611 from dual;
612
613 IF v_sequence_number
614 BETWEEN v_min_delivered AND v_max_delivered THEN
615
616 hr_legislation.munge_sequence('FF_FORMULA_TYPES_S',
617 v_sequence_number,
618 v_max_delivered);
619
620 END IF;
621
622 END check_next_sequence;
623
624 PROCEDURE update_uid
625 --------------------
626 IS
627 -- Subprogram to update surrogate UID and all occurrences in child rows
628
629 BEGIN
630
631
632 BEGIN
633
634 select distinct formula_type_id
635 into l_new_surrogate_key
636 from ff_formula_types
637 where formula_type_name = stu_rec.c_true_key;
638
639 EXCEPTION WHEN NO_DATA_FOUND THEN
640
641 select ff_formula_types_s.nextval
642 into l_new_surrogate_key
643 from dual;
644
645 WHEN TOO_MANY_ROWS THEN
646 hr_legislation.hrrunprc_trace_on;
647 hr_utility.trace('multiple ftype ' || stu_rec.c_true_key);
648 hr_legislation.hrrunprc_trace_off;
649 raise;
650 END;
651
652 -- Update all child entities
653
654 update hr_s_formula_types
655 set formula_type_id = l_new_surrogate_key
656 where formula_type_id = stu_rec.c_surrogate_key;
657
658 update hr_s_application_ownerships
659 set key_value = to_char(l_new_surrogate_key)
660 where key_value = to_char(stu_rec.c_surrogate_key)
661 and key_name = 'FORMULA_TYPE_ID';
662
663 update hr_s_formulas_f
664 set formula_type_id = l_new_surrogate_key
665 where formula_type_id = stu_rec.c_surrogate_key;
666
667 update hr_s_ftype_context_usages
668 set formula_type_id = l_new_surrogate_key
669 where formula_type_id = stu_rec.c_surrogate_key;
670
671 update hr_s_qp_reports
672 set formula_type_id = l_new_surrogate_key
673 where formula_type_id = stu_rec.c_surrogate_key;
674
675 END update_uid;
676
677 PROCEDURE remove
678 ----------------
679 IS
680 -- Remove a row from the startup/delivered tables
681
682 BEGIN
683
684 delete from hr_s_formula_types
685 where rowid = stu_rec.rowid;
686
687 delete from hr_s_ftype_context_usages
688 where formula_type_id = l_new_surrogate_key;
689
690 END remove;
691
692 FUNCTION valid_ownership RETURN BOOLEAN
693 ---------------------------------------
694 IS
695 --
696 CURSOR c_fft2 IS
697 select null --if exception raised then this row is not needed
698 from dual
699 where exists (
700 select null
701 from hr_s_application_ownerships a
702 , fnd_product_installations b
703 , fnd_application c
704 where a.key_name = 'FORMULA_TYPE_ID'
705 and a.key_value = l_new_surrogate_key
706 and a.product_name = c.application_short_name
707 and c.application_id = b.application_id
708 and ((b.status = 'I' and c.application_short_name <> 'PQP')
709 or
710 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
711 --
712 -- If for a given formula_type_id, there is a difference of any sort between
713 -- what is on the live table to what is being delivered then we must
714 -- invalidate and recreate the usages which will invalidate potentially
715 -- many formulae. This if there is no mismatch between delivered and
716 -- live data then we will remove the formula type from the hr_s tables
720 select 1
717 -- so eleiminating the need for formula invalidation.
718 -- new version. Only need for live to not be a superset of HR_S
719 CURSOR c_fft3 IS
721 from dual
722 where exists
723 (select hfcu.context_id
724 from hr_s_ftype_context_usages hfcu
725 where hfcu.formula_type_id = l_new_surrogate_key
726 MINUS
727 select fcu.context_id
728 from ff_ftype_context_usages fcu
729 where fcu.formula_type_id = l_new_surrogate_key);
730 --
731 BEGIN
732 -- This routine only operates in phase 1. Rows are present in the
733 -- table hr_application_ownerships in the delivery account, which
734 -- dictate which products a piece of data is used for. If the query
735 -- returns a row, then this data is required, and the function will
736 -- return true. If no rows are returned and an exception is raised,
737 -- then this row is not required and may be deleted from the delivery
738 -- tables.
739
740 -- If legislation code and subgroup code are included on the delivery
741 -- tables, a check must be made to determine if the data is defined for
742 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
743 -- installation.
744
745 -- A return code of TRUE indicates that the row is required.
746
747 -- The exception is raised within this procedure if no rows are returned
748 -- in this select statement. If no rows are returned then one of the
749 -- following is true:
750 -- 1. No ownership parameters are defined.
751 -- 2. The products, for which owning parameters are defined, are not
752 -- installed with as status of 'I'.
753 -- 3. The data is defined for a legislation subgroup that is not active.
754
755 IF p_phase <> 1 THEN -- Only perform in phase 1
756 return TRUE;
757 END IF;
758 --
759 BEGIN
760 open c_fft2;
761 fetch c_fft2 into l_null_return;
762 IF c_fft2%NOTFOUND THEN
763 close c_fft2;
764 RAISE NO_DATA_FOUND;
765 END IF;
766 close c_fft2;
767 --
768 EXCEPTION WHEN NO_DATA_FOUND THEN
769
770 -- Row not needed for any installed product
771 remove;
772 return FALSE; -- Indicates row not needed
773
774 END;
775
776 BEGIN
777
778 -- Now check to see if we have to reinstall this row by checking
779 -- if the ftype usages all match. If any don't then we must reinstall
780 -- the formula_type and its usages which will mean potentially having
781 -- to recompile many formulae
782 IF p_phase <> 1 THEN
783 return TRUE;
784 END IF;
785
786 -- check if any diffs between ftcus in delivered and live
787 -- this cursor doesnt cater when there are no ftcus so check for
788 -- this first
789
790 select count(*)
791 into l_number_of_ftcu
792 from hr_s_ftype_context_usages
793 where formula_type_id = l_new_surrogate_key;
794
795 if l_number_of_ftcu <> 0 then
796 open c_fft3;
797 fetch c_fft3 into l_null_return;
798 IF c_fft3%NOTFOUND THEN
799 close c_fft3;
800 remove; -- everything between live and delivered matches
801 END IF;
802 close c_fft3;
803 end if;
804 --
805 EXCEPTION WHEN OTHERS THEN
806 null;
807 END;
808
809 return TRUE;
810
811 END valid_ownership;
812
813 PROCEDURE transfer_row
814 ----------------------
815 IS
816
817 CURSOR c_fft5 (usages_context_id number) IS
818 select null
819 from ff_contexts
820 where context_id = usages_context_id;
821 --
822
823 BEGIN
824
825 update ff_formula_types
826 set type_description = stu_rec.type_description
827 , last_update_date = stu_rec.last_update_date
828 , last_updated_by = stu_rec.last_updated_by
829 , last_update_login = stu_rec.last_update_login
830 , created_by = stu_rec.created_by
831 , creation_date = stu_rec.creation_date
832 where formula_type_name = stu_rec.c_true_key;
833
834 IF SQL%NOTFOUND THEN
835
836 -- This formula type does not exist
837
838 BEGIN
839 insert into ff_formula_types
840 (formula_type_name
841 ,formula_type_id
842 ,type_description
843 ,last_update_date
844 ,last_updated_by
845 ,last_update_login
846 ,created_by
847 ,creation_date
848 )
849 values
850 (stu_rec.c_true_key
851 ,stu_rec.c_surrogate_key
852 ,stu_rec.type_description
853 ,stu_rec.last_update_date
854 ,stu_rec.last_updated_by
855 ,stu_rec.last_update_login
856 ,stu_rec.created_by
857 ,stu_rec.creation_date
858 );
859 EXCEPTION WHEN OTHERS THEN
860 hr_legislation.hrrunprc_trace_on;
861 hr_utility.trace('ins ff_formula_types');
862 hr_utility.trace('formula_type_id ' ||
863 to_char(stu_rec.c_surrogate_key));
864 hr_utility.trace('formula_type_name ' ||
865 stu_rec.c_true_key);
866 hr_legislation.hrrunprc_trace_off;
867 raise;
868 END;
869
870
871 END IF;
872
873 -- Now loop for all context usages to install. First a check must be made
877 -- to allow any errors with parent contexts to be highlighted in pahse 1.
874 -- to see if the context referenced in this row is installed in the live
875 -- tables. If not then the transfer for this whole formula type must not
876 -- proceed. The actual insert statement is within a phase value check
878 -- the last final delete will only occur in phase 2, since only in phase 2
879 -- will the row have been transferred.
880 -- All the live context usages must be deleted first
881
882 FOR usages IN ftcu(l_new_surrogate_key) LOOP
883
884 BEGIN
885
886 delete ff_compiled_info_f f
887 where f.formula_id in (
888 select distinct a.formula_id
889 from ff_formulas_f a,
890 ff_fdi_usages_f b,
891 ff_contexts c
892 where a.formula_type_id = stu_rec.c_surrogate_key
893 and a.formula_id = b.formula_id
894 and b.item_name = upper(c.context_name)
895 and c.context_id = usages.context_id
896 and b.usage = 'U');
897
898 delete ff_fdi_usages_f f
899 where f.formula_id in (
900 select distinct a.formula_id
901 from ff_formulas_f a,
902 ff_fdi_usages_f b,
903 ff_contexts c
904 where a.formula_type_id = stu_rec.c_surrogate_key
905 and a.formula_id = b.formula_id
906 and b.item_name = upper(c.context_name)
907 and c.context_id = usages.context_id
908 and b.usage = 'U');
909
910 delete from ff_ftype_context_usages
911 where formula_type_id = stu_rec.c_surrogate_key
912 and context_id=usages.context_id;
913
914 open c_fft5 (usages.context_id);
915 fetch c_fft5 into l_null_return;
916 IF c_fft5%NOTFOUND OR c_fft5%NOTFOUND IS NULL THEN
917 RAISE NO_DATA_FOUND;
918 END IF;
919 close c_fft5;
920
921 insert into ff_ftype_context_usages
922 (formula_type_id
923 ,context_id)
924 values
925 (usages.formula_type_id
926 ,usages.context_id);
927
928 EXCEPTION WHEN NO_DATA_FOUND THEN
929
930 -- Parent context not present
931
932
933 crt_exc('Context referenced by child usage is not present');
934
935 -- Get next formula type to install
936
937 return;
938
939 WHEN OTHERS THEN
940 hr_legislation.hrrunprc_trace_on;
941 hr_utility.trace('ins ff_ftype_context_usages');
942 hr_utility.trace('formula_type_id ' ||
943 to_char(usages.formula_type_id));
944 hr_utility.trace('context_id ' ||
945 to_char(usages.context_id));
946 hr_utility.trace('formula_type_name ' ||
947 stu_rec.c_true_key);
948 hr_legislation.hrrunprc_trace_off;
949 raise;
950 END;
951
952 END LOOP usages;
953
954
955 -- Delete delivered row now it has been installed
956
957 remove;
958
959 END transfer_row;
960
961 BEGIN
962
963 -- This is the main loop to perform the installation logic. A cursor
964 -- is opened to control the loop, and each row returned is placed
965 -- into a record defined within the main procedure so each sub
966 -- procedure has full access to all returrned columns. For each
967 -- new row returned, a new savepoint is declared. If at any time
968 -- the row is in error a rollback iss performed to the savepoint
969 -- and the next row is returned. Ownership details are checked and
970 -- if the row is required then the surrogate id is updated and the
971 -- main transfer logic is called.
972
973 IF p_phase = 1 THEN
974 check_next_sequence;
975 END IF;
976
977 FOR delivered IN stu LOOP
978
979 -- Uses main cursor stu to impilicity define a record
980
981
982 savepoint new_formula_type_name;
983
984 -- Make all cursor columns available to all procedures
985
986 stu_rec := delivered;
987
988 IF p_phase = 1 THEN
989 update_uid;
990 END IF;
991
992 IF p_phase = 2 THEN
993 l_new_surrogate_key := stu_rec.c_surrogate_key;
994 END IF;
995
996 IF valid_ownership THEN
997
998 -- Test the row onerships for the current row
999
1000
1001 if p_phase = 2 THEN transfer_row; end if;
1002
1003 END IF;
1004
1005 END LOOP;
1006
1007 END install_fft;
1008
1009 --****************************************************************************
1010 -- INSTALLATION PROCEDURE FOR : FF_FORMULAS_F
1011 --****************************************************************************
1012
1013 PROCEDURE install_formulas (p_phase IN NUMBER)
1014 ----------------------------------------------
1015 IS
1016 -- Install procedure to transfer startup delivered ff_formulass into a
1017 -- live account, and remove the then delivered rows from the delivery
1018 -- account.
1019 -- This procedure is called in two phase. Only in the second phase are
1020 -- details transferred into live tables. The parameter p_phase holds
1021 -- the phase number.
1022
1026 l_current_proc varchar2(80) := 'hr_legislation.install_formulas';
1023 row_in_error exception;
1024 v_formula_text clob;
1025 vlive_formula_text clob;
1027 l_new_formula_id number(15);
1028 l_null_return varchar2(1);
1029
1030 CURSOR c_distinct
1031 IS
1032 -- select statement used for the main loop. Each row return is used
1033 -- as the commit unit, since each true primary key may have many date
1034 -- effective rows for it.
1035 -- The selected primary key is then passed into the second driving
1036 -- cursor statement as a parameter, and all date effective rows for
1037 -- this primary key are then selected.
1038
1039 select max(effective_end_date) c_end
1040 , formula_id c_surrogate_key
1041 , formula_type_id
1042 , formula_name c_true_key
1043 , legislation_code
1044 from hr_s_formulas_f
1045 group by formula_id
1046 , formula_type_id
1047 , formula_name
1048 , legislation_code;
1049
1050 CURSOR c_each_row (pc_formula_id varchar2)
1051 IS
1052 -- Selects all date effective rows for the current true primary key
1053 -- The primary key has already been selected using the above cursor.
1054 -- This cursor accepts the primary key as a parameter and selects all
1055 -- date effective rows for it.
1056
1057 select *
1058 from hr_s_formulas_f
1059 where formula_id = pc_formula_id;
1060
1061 -- These records are defined here so all sub procedures may use the
1062 -- values selected. This saves the need for all sub procedures to have
1063 -- a myriad of parameters passed. The cursors are controlled in FOR
1064 -- cursor LOOPs. When a row is returned the whole record is copied into
1065 -- these record definitions.
1066
1067 r_distinct c_distinct%ROWTYPE;
1068 r_each_row c_each_row%ROWTYPE;
1069 l_dummy varchar2(1);
1070
1071 PROCEDURE crt_exc (exception_type IN varchar2)
1072 ----------------------------------------------
1073 IS
1074 -- Reports any exceptions during the delivery of startup data to
1075 -- FF_FORMULAS_F
1076 BEGIN
1077 -- When the installation procedures encounter an error that cannot
1078 -- be handled, an exception is raised and all work is rolled back
1079 -- to the last savepoint. The installation process then continues
1080 -- with the next primary key to install. The same exception will
1081 -- not be raised more than once.
1082
1083 rollback to new_formula_name;
1084
1085 hr_legislation.insert_hr_stu_exceptions('ff_formulas_f'
1086 , r_distinct.c_surrogate_key
1087 , exception_type
1088 , r_distinct.c_true_key);
1089
1090
1091 END crt_exc;
1092
1093 PROCEDURE remove (v_id IN number)
1094 ---------------------------------
1095 IS
1096 -- Subprogram to delete a row from the delivery tables, and all child
1097 -- application ownership rows
1098
1099 BEGIN
1100
1101
1102 delete from hr_s_formulas_f
1103 where formula_id = v_id;
1104
1105 END remove;
1106
1107 PROCEDURE check_next_sequence
1108 -----------------------------
1109 IS
1110
1111 v_sequence_number number(9);
1112 v_min_delivered number(9);
1113 v_max_delivered number(9);
1114
1115 -- Surrogate id conflicts may arise from two scenario's:
1116 -- 1. Where the newly select sequence value conflicts with values
1117 -- in the STU tables.
1118 -- 2. Where selected surrogate keys, from the installed tables,
1119 -- conflict with other rows in the STU tables.
1120 --
1121 -- Both of the above scenario's are tested for.
1122 -- The first is a simple match, where if a value is detected in the
1123 -- STU tables and the installed tables then a conflict is detected. In
1124 -- This instance all STU surrogate keys, for this table, are updated.
1125 -- The second is tested for using the sequences.
1126 -- If the next value from the live sequence is within the range of
1127 -- delivered surrogate id's then the live sequence must be incremented.
1128 -- If no action is taken, then duplicates may be introduced into the
1129 -- delivered tables, and child rows may be totally invalidated.
1130
1131 BEGIN
1132
1133
1134 BEGIN --check that the installed id's will not conflict
1135 --with the delivered values
1136
1137
1138 select distinct null
1139 into l_null_return
1140 from ff_formulas_f a
1141 where exists
1142 (select null
1143 from hr_s_formulas_f b
1144 where a.formula_id = b.formula_id
1145 );
1146
1147 --conflict may exist
1148 --update all formula_id's to remove conflict
1149
1150 update /*+NO_INDEX*/ hr_s_formulas_f
1151 set formula_id = formula_id - 50000000;
1152
1153 update /*+NO_INDEX*/ hr_s_qp_reports
1154 set formula_id = formula_id - 50000000;
1155
1156 update /*+NO_INDEX*/ hr_s_element_types_f
1157 set formula_id = formula_id - 50000000;
1158
1159 update /*+NO_INDEX*/ hr_s_element_types_f
1160 set iterative_formula_id = iterative_formula_id - 50000000;
1161
1162 update /*+NO_INDEX*/ hr_s_element_types_f
1163 set proration_formula_id = proration_formula_id - 50000000;
1164
1165 update /*+NO_INDEX*/ hr_s_input_values_f
1166 set formula_id = formula_id - 50000000;
1167
1168 update /*+NO_INDEX*/ hr_s_status_processing_rules_f
1169 set formula_id = formula_id - 50000000;
1170
1171 update /*+NO_INDEX*/ hr_s_user_columns
1172 set formula_id = formula_id - 50000000;
1173
1177
1174 update hr_s_application_ownerships
1175 set key_value = key_value - 50000000
1176 where key_name = 'FORMULA_ID';
1178 update /*+NO_INDEX*/ hr_s_magnetic_records
1179 set formula_id = formula_id - 50000000;
1180
1181 update hr_s_legislation_rules
1182 set rule_mode =
1183 to_char(fnd_number.canonical_to_number(rule_mode) - 50000000)
1184 where rule_type = 'LEGISLATION_CHECK_FORMULA';
1185
1186 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1187
1188 END; --check of formula_id
1189
1190
1191 select min(formula_id) - (count(*) *3)
1192 , max(formula_id) + (count(*) *3)
1193 into v_min_delivered
1194 , v_max_delivered
1195 from hr_s_formulas_f;
1196
1197 select ff_formulas_s.nextval
1198 into v_sequence_number
1199 from dual;
1200
1201 IF v_sequence_number
1202 BETWEEN v_min_delivered AND v_max_delivered THEN
1203
1204 hr_legislation.munge_sequence('FF_FORMULAS_S',
1205 v_sequence_number,
1206 v_max_delivered);
1207 END IF;
1208
1209 END check_next_sequence;
1210
1211 PROCEDURE update_uid
1212 --------------------
1213 IS
1214 -- Subprogram to update surrogate UID and all occurrences in child rows
1215
1216 -- See if this primary key is already installed. If so then the sorrogate
1217 -- key of the delivered row must be updated to the value in the installed
1218 -- tables. If the row is not already present then select the next value
1219 -- from the sequence. In either case all rows for this primary key must
1220 -- be updated, as must all child references to the old surrogate uid.
1221
1222 BEGIN
1223
1224
1225 BEGIN
1226
1227 select distinct formula_id
1228 into l_new_formula_id
1229 from ff_formulas_f
1230 where formula_name = r_distinct.c_true_key
1231 and formula_type_id = r_distinct.formula_type_id
1232 and business_Group_id is null
1233 and ((legislation_code is NULL and r_distinct.legislation_code is NULL)
1234 or (r_distinct.legislation_code=legislation_code));
1235
1236
1237 EXCEPTION WHEN NO_DATA_FOUND THEN
1238
1239
1240 select ff_formulas_s.nextval
1241 into l_new_formula_id
1242 from dual;
1243
1244 WHEN TOO_MANY_ROWS THEN
1245
1246 hr_legislation.hrrunprc_trace_on;
1247 hr_utility.trace('sel ff_formulas_f TMR');
1248 hr_utility.trace('formula_name ' ||
1249 r_distinct.c_true_key);
1250 hr_utility.trace('formula_type_id ' ||
1251 to_char(r_distinct.formula_type_id));
1252 hr_utility.trace(':lc: ' || ':' ||
1253 r_distinct.legislation_code || ':');
1254 hr_legislation.hrrunprc_trace_off;
1255 raise;
1256 END;
1257
1258 update hr_s_formulas_f
1259 set formula_id = l_new_formula_id
1260 where formula_id = r_distinct.c_surrogate_key;
1261
1262 update hr_s_application_ownerships
1263 set key_value = to_char(l_new_formula_id)
1264 where key_value = to_char(r_distinct.c_surrogate_key)
1265 and key_name = 'FORMULA_ID';
1266
1267 update hr_s_qp_reports
1268 set formula_id = l_new_formula_id
1269 where formula_id = r_distinct.c_surrogate_key;
1270
1271 update hr_s_element_Types_f
1272 set formula_id = l_new_formula_id
1273 where formula_id = r_distinct.c_surrogate_key;
1274
1275 update hr_s_element_Types_f
1276 set iterative_formula_id = l_new_formula_id
1277 where iterative_formula_id = r_distinct.c_surrogate_key;
1278
1279 update hr_s_element_Types_f
1280 set proration_formula_id = l_new_formula_id
1281 where proration_formula_id = r_distinct.c_surrogate_key;
1282
1283 update hr_s_input_values_f
1284 set formula_id = l_new_formula_id
1285 where formula_id = r_distinct.c_surrogate_key;
1286
1287 update hr_s_status_processing_rules_f
1288 set formula_id = l_new_formula_id
1289 where formula_id = r_distinct.c_surrogate_key;
1290
1291 update hr_s_user_columns
1292 set formula_id = l_new_formula_id
1293 where formula_id = r_distinct.c_surrogate_key;
1294
1295 update hr_s_magnetic_records
1296 set formula_id = l_new_formula_id
1297 where formula_id = r_distinct.c_surrogate_key;
1298
1299 update hr_s_legislation_rules
1300 set rule_mode = to_char(l_new_formula_id)
1301 where rule_mode = to_char(r_distinct.c_surrogate_key)
1302 and rule_type = 'LEGISLATION_CHECK_FORMULA';
1303
1304
1305 END update_uid;
1306
1307 PROCEDURE validity_checks
1308 -------------------------
1309 IS
1310 -- After all rows for a primary key have been delivered, entity specific
1311 -- checks must be performed to check to validity of the data that has
1312 -- just been installed.
1313
1314 BEGIN
1315
1316
1317 IF p_phase =2 THEN
1318 l_new_formula_id := r_distinct.c_surrogate_key;
1319 END IF;
1320
1321 -- Start child check 1
1322
1323 BEGIN
1324
1325 -- Check input values first
1326
1327 select distinct null
1328 into l_null_return
1329 from pay_input_values_f
1333
1330 where effective_end_date > r_distinct.c_end
1331 and formula_id = l_new_formula_id
1332 and business_group_id is not null;
1334
1335 crt_exc('User created input value exists after the new end date');
1336
1337 return;
1338
1339 EXCEPTION WHEN NO_DATA_FOUND THEN
1340
1341 null;
1342
1343 END;
1344
1345 BEGIN
1346
1347 -- Check status processing rules now
1348
1349 select distinct null
1350 into l_null_return
1351 from pay_status_processing_rules_f
1352 where effective_end_date > r_distinct.c_end
1353 and formula_id = l_new_formula_id
1354 and business_group_id is not null;
1355
1356
1357 crt_exc('User created process rule exists after the new end date');
1358
1359 return;
1360
1361 EXCEPTION WHEN NO_DATA_FOUND THEN
1362
1363 null;
1364
1365 END;
1366
1367 END validity_checks;
1368
1369 FUNCTION check_parents RETURN BOOLEAN
1370 -------------------------------------
1371 IS
1372 -- Check the integrity of the references to parent data, before allowing
1373 -- data to be installed. No parents can exist in the startup tables, since
1374 -- this will violate constraints when the row is installed, also the
1375 -- parent uid's must exist in the installed tables already.
1376
1377 -- This function will RETURN TRUE if a parent row still exists in the
1378 -- delivery account. All statements drop through to a RETURN FALSE.
1379
1380 -- This procedure is only called in phase 2. The logic to check if
1381 -- a given parental foriegn key exists is split into two parts for
1382 -- every foriegn key. The first select from the delivery tables.
1383
1384 -- If a row is founnd then the installation of the parent must have
1385 -- failed, and this installation must not go ahead. If no data is
1386 -- found, ie: an exception is raised, the installation is valid.
1387
1388 -- The second check looks for a row in the live tables. If no rows
1389 -- are returned then this installation is invalid, since this means
1390 -- that the parent referenced by this row is not present in the
1391 -- live tables.
1392
1393 -- Return code of true indicates that all parental data is correct.
1394
1395 BEGIN
1396
1397
1398 -- Start first parent check
1399
1400 BEGIN
1401
1402 -- Check first parent does not exist in the delivery tables
1403
1404 select distinct null
1405 into l_NULL_RETURN
1406 from hr_s_formula_types
1407 where formula_type_id = r_each_row.formula_type_id;
1408
1409
1410 crt_exc('Parent formula type still exists in delivery tables');
1411
1412 -- Parent still exists, ignore this row
1413
1414 return FALSE;
1415
1416 EXCEPTION WHEN NO_DATA_FOUND THEN
1417
1418 null;
1419
1420 END;
1421
1422 BEGIN
1423
1424 -- Check that the parent exists in the live tables
1425
1426
1427 select null
1428 into l_null_return
1429 from ff_formula_types
1430 where formula_type_id = r_each_row.formula_type_id;
1431
1432 EXCEPTION WHEN NO_DATA_FOUND THEN
1433
1434
1435 crt_exc('Parent formula type does not exist in live tables');
1436
1437 return FALSE;
1438
1439 END;
1440
1441 -- Logic drops through to this statement
1442
1443 return TRUE;
1444
1445 END check_parents;
1446
1447 FUNCTION valid_ownership RETURN BOOLEAN
1448 ---------------------------------------
1449 IS
1450 -- Test ownership of this current row
1451
1452 BEGIN
1453
1454 -- This function is split into three distinct parts. The first
1455 -- checks to see if a row exists with the same primary key, for a
1456 -- business group that would have access to the delivered row. The
1457 -- second checks details for data created in other legislations,
1458 -- in case data is either created with a null legislation or the
1459 -- delivered row has a null legislation. The last check examines
1460 -- if this data is actually required for a given install by examining
1461 -- the product installation table, and the ownership details for
1462 -- this row.
1463
1464 -- A return code of TRUE indicates that the row is required.
1465
1466
1467 BEGIN
1468 -- Perform a check to see if the primary key has been creeated within
1469 -- a visible business group. Ie: the business group is for the same
1470 -- legislation as the delivered row, or the delivered row has a null
1471 -- legislation. If no rows are returned then the primary key has not
1472 -- already been created by a user.
1473
1474 -- The formula must be created for the same formula type.
1475
1476
1477 select distinct null
1478 into l_null_return
1479 from ff_formulas_f a
1480 where a.formula_name = r_distinct.c_true_key
1481 and a.formula_type_id = r_distinct.formula_type_id
1482 and a.business_group_id is not null
1483 and exists (select null from per_business_groups b
1484 where b.business_group_id = a.business_group_id
1488 crt_exc('Row already created in a business group');
1485 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
1486
1487
1489
1490 -- Indicates this row is not to be transferred
1491
1492 return FALSE;
1493
1494 EXCEPTION WHEN NO_DATA_FOUND THEN
1495
1496 null;
1497
1498 END;
1499
1500 -- Now perform a check to see if this primary key has been installed
1501 -- with a legislation code that would make it visible at the same time
1502 -- as this row. Ie: if any legislation code is null within the set of
1503 -- returned rows, then the transfer may not go ahead. If no rows are
1504 -- returned then the delivered row is fine.
1505
1506 -- The formula must be created within the same formula type.
1507
1508 BEGIN
1509
1510
1511 select distinct null
1512 into l_null_return
1513 from ff_formulas_f
1514 where formula_name = r_distinct.c_true_key
1515 and formula_type_id = r_distinct.formula_type_id
1516 and legislation_code <> r_distinct.legislation_code
1517 and (legislation_code is null or
1518 r_distinct.legislation_code is null );
1519
1520
1521 crt_exc('Row already created for a visible legislation');
1522
1523 --indicates this row is not to be transferred
1524
1525 return FALSE;
1526
1527 EXCEPTION WHEN NO_DATA_FOUND THEN
1528
1529 null;
1530
1531 END;
1532
1533 -- PSEUDO
1534 -- Error if we clash on delivered data to pseudo data present
1535 IF hr_legislation.g_pseudo_enabled = 'Y' THEN
1536 BEGIN
1537 -- Check if we have a pseudo seed clash if enabled
1538 -- Error handling is done in the procedure itself
1539 hr_legislation.PSEUDO_SEED_DATA_CLASH(P_SURROGATE_KEY_NAME => r_distinct.c_true_key,
1540 P_TABLE_NAME => 'FF_FORMULAS_F',
1541 P_LEGISLATION_CODE => r_distinct.legislation_code);
1542 EXCEPTION WHEN OTHERS THEN
1543 return FALSE;
1544 END;
1545 END IF;
1546
1547 -- The last check examines the product installation table, and the
1548 -- ownership details for the delivered row. By examining these
1549 -- tables the row is either deleted or not. If the delivered row
1550 -- is 'stamped' with a legislation subgroup, then a check must be
1551 -- made to see if that subgroup is active or not. This check only
1552 -- needs to be performed in phase 1, since once this decision is
1553 -- made, it is pontless to perform this logic again.
1554
1555 -- The exception is raised within this procedure if no rows are returned
1556 -- in this select statement. If no rows are returned then one of the
1557 -- following is true:
1558 -- 1. No ownership parameters are defined.
1559 -- 2. The products, for which owning parameters are defined, are not
1560 -- installed with as status of 'I'.
1561 -- 3. The data is defined for a legislation subgroup that is not active.
1562
1563 IF p_phase <> 1 THEN
1564 return TRUE;
1565 END IF;
1566
1567
1568 select null --if exception raised then this row is not needed
1569 into l_null_return
1570 from dual
1571 where exists (select null
1572 from hr_s_application_ownerships a
1573 , fnd_product_installations b
1574 , fnd_application c
1575 where a.key_name = 'FORMULA_ID'
1576 and a.key_value = r_distinct.c_surrogate_key
1577 and a.product_name = c.application_short_name
1578 and c.application_id = b.application_id
1579 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1580 or
1581 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1582
1583
1584 -- Indicates row is required
1585
1586 return TRUE;
1587
1588 EXCEPTION WHEN NO_DATA_FOUND THEN
1589
1590 -- Row not needed for any installed product
1591
1592
1593 remove(r_distinct.c_surrogate_key);
1594
1595 -- Indicate row not needed
1596
1597 return FALSE;
1598
1599 END valid_ownership;
1600
1601 function formula_changed(form_id in number) return boolean is
1602 cursor all_rows(f_id in number) is
1603 select effective_start_date
1604 from hr_s_formulas_f
1605 where formula_id = f_id;
1606 begin
1607 -- First check on existence
1608
1609 BEGIN
1610 select null
1611 into l_dummy
1612 from ff_formulas_f
1613 where formula_id = form_id;
1614 EXCEPTION when others then
1615 return TRUE;
1616 END;
1617
1618 -- Check long column for all
1619 for r_all_rows in all_rows(form_id)
1620 loop
1621 BEGIN
1622 select formula_text
1623 into v_formula_text
1624 from hr_s_formulas_f
1625 where formula_id = form_id
1626 and effective_start_date = r_all_rows.effective_start_date;
1627
1628 select formula_text
1629 into vlive_formula_text
1630 from ff_formulas_f
1631 where formula_id = form_id
1632 and effective_start_date = r_all_rows.effective_start_date;
1633
1634 -- First check if non stub formula diff from live
1635 if (v_formula_text is not null and
1636 vlive_formula_text is not null and
1637 v_formula_text <> vlive_formula_text) then
1638 return TRUE;
1639 end if;
1640
1641 EXCEPTION when others then
1642 -- not all DT rows in hr_s match to live
1643 -- or if live version does not exist we need to transfer
1644 return TRUE;
1645 END;
1646
1647 end loop;
1648
1649 -- Now check the rest of the row's columns
1650 -- not imported by a later hdt
1651 begin
1652 select null
1653 into l_dummy
1654 from dual
1655 where not exists
1656 ((
1657 select effective_start_date,
1658 effective_end_date,
1662 and formula_type_id = r_distinct.formula_type_id
1659 description
1660 from hr_s_formulas_f
1661 where formula_id = form_id
1663 MINUS
1664 select effective_start_date,
1665 effective_end_date,
1666 description
1667 from ff_formulas_f
1668 where formula_id = form_id
1669 and formula_type_id = r_distinct.formula_type_id
1670 )
1671 UNION
1672 (
1673 select effective_start_date,
1674 effective_end_date,
1675 description
1676 from ff_formulas_f
1677 where formula_id = form_id
1678 and formula_type_id = r_distinct.formula_type_id
1679 MINUS
1680 select effective_start_date,
1681 effective_end_date,
1682 description
1686 ));
1683 from hr_s_formulas_f
1684 where formula_id = form_id
1685 and formula_type_id = r_distinct.formula_type_id
1687 -- if we get a row and not the exception then identical
1688 return FALSE;
1689 exception
1690 -- otherwise there is a diff
1691 when no_data_found then
1692 return TRUE;
1693 end;
1694 end formula_changed;
1695
1696 BEGIN
1697
1698 -- Two loops are used here. The main loop which select distinct primary
1699 -- key rows and an inner loop which selects all date effective rows for the
1700 -- primary key. The inner loop is only required in phase 2, since only
1701 -- in phase 2 are rows actually transferred. The logic reads as follows:
1702
1703 -- - Only deal with rows which have correct ownership details and will
1704 -- not cause integrity problems (valid_ownership).
1705
1706 -- - In Phase 1:
1707 -- - Delete delivery rows where the installed rows are identicle.
1708 -- - The UNION satement compares delivery rows to installed rows.
1709 -- If the sub query returns any rows, then the delivered
1710 -- tables and the installed tables are different.
1711
1712 -- In Phase 2:
1713 -- - Delete from the installed tables using the surrogate id.
1714 -- - If an installed row is to be replaced, the values of
1715 -- the surrogate keys will be identicle at this stage.
1716 -- - Data will then be deleted from the delivery tables.
1717 -- - Call the installation procedure for any child tables, that
1718 -- must be installed within the same commit unit. If any
1719 -- errors occur then rollback to the last declared savepoint.
1720 -- - Check that all integrity rules are still obeyed at the end
1721 -- of the installation (validity_checks).
1722
1723 -- An exception is used with this procedure 'row_in_error' in case an error
1724 -- is encountered from calling any function. If this is raised, then an
1725 -- exception is entered into the control tables (crt_exc();) and a rollback
1726 -- is performed.
1727
1728 IF p_phase = 1 THEN
1729 check_next_sequence;
1730 END IF;
1731
1732 FOR formula_names IN c_distinct LOOP
1733
1734
1735 savepoint new_formula_name;
1736
1737 r_distinct := formula_names;
1738
1739 BEGIN
1740
1741
1742 IF valid_ownership THEN
1743
1744 -- This row is wanted
1745
1746
1747 IF p_phase = 1 THEN
1748
1749
1750 -- Get new surrogate id and update child references
1751
1752 update_uid;
1753
1754 ELSE
1755
1756 -- Phase = 2
1757
1758 -- Now Ids are matched Check if formula definition is
1759 -- actually required
1760 if not formula_changed(r_distinct.c_surrogate_key) then
1761 remove(r_distinct.c_surrogate_key);
1762 else
1763
1764 delete from ff_fdi_usages_f
1765 where formula_id = r_distinct.c_surrogate_key;
1766
1767 delete from ff_compiled_info_f
1768 where formula_id = r_distinct.c_surrogate_key;
1769
1770 delete from ff_formulas_f
1771 where formula_id = r_distinct.c_surrogate_key;
1772
1773 FOR each_row IN c_each_row(r_distinct.c_surrogate_key) LOOP
1774
1775
1776 r_each_row := each_row;
1777
1778 IF NOT check_parents THEN
1779 RAISE row_in_error;
1780 END IF;
1781
1782 BEGIN
1783 insert into ff_formulas_f
1784 (formula_id
1785 ,effective_start_date
1786 ,effective_end_date
1787 ,business_group_id
1788 ,legislation_code
1789 ,formula_type_id
1790 ,formula_name
1791 ,description
1792 ,formula_text
1793 ,sticky_flag
1794 ,compile_flag
1795 ,last_update_date
1796 ,last_updated_by
1797 ,last_update_login
1798 ,created_by
1799 ,creation_date
1800 )
1801 values
1802 (r_each_row.formula_id
1803 ,r_each_row.effective_start_date
1804 ,r_each_row.effective_end_date
1805 ,r_each_row.business_group_id
1806 ,r_each_row.legislation_code
1807 ,r_each_row.formula_type_id
1808 ,r_each_row.formula_name
1809 ,r_each_row.description
1810 ,r_each_row.formula_text
1811 ,r_each_row.sticky_flag
1812 ,r_each_row.compile_flag
1813 ,r_each_row.last_update_date
1814 ,r_each_row.last_updated_by
1815 ,r_each_row.last_update_login
1816 ,r_each_row.created_by
1817 ,r_each_row.creation_date
1818 );
1822 hr_utility.trace('formula_name ' ||
1819 EXCEPTION WHEN OTHERS THEN
1820 hr_legislation.hrrunprc_trace_on;
1821 hr_utility.trace('ins ff_formulas_f');
1823 r_each_row.formula_name);
1824 hr_utility.trace('formula_id ' ||
1825 to_char(r_each_row.formula_id));
1826 hr_utility.trace('formula_type_id ' ||
1827 to_char(r_each_row.formula_type_id));
1828 hr_utility.trace(':lc: ' || ':' ||
1829 r_each_row.legislation_code || ':');
1830 hr_legislation.hrrunprc_trace_off;
1831 raise;
1832 END;
1833
1834 remove(r_distinct.c_surrogate_key);
1835
1836 END LOOP each_row;
1837
1838 validity_checks;
1839
1840 -- This will cause a rollback if error occurs
1841
1842 END IF;
1843
1844 end if; -- need the row
1845
1846
1847 END IF;
1848
1849 EXCEPTION WHEN row_in_error THEN
1850
1851 -- Already rolled back
1852
1853 null;
1854
1855 END;
1856
1857 END LOOP formula_names;
1858
1859 END install_formulas;
1860
1861 --****************************************************************************
1862 -- INSTALLATION PROCEDURE FOR : FF_ROUTES
1863 --****************************************************************************
1864
1865 PROCEDURE install_routes (p_phase IN number)
1866 ------------------------
1867 IS
1868 -- Procedure install routes/usages/entities/parameters/parameter values.
1869 -- The main driving installation cursor runs from hr_s_routes. All child
1870 -- rows are installed as cursors driven by the current route_id. The user
1871 -- entities then have child rows installed in a similar way.
1872 -- If the route has not changed, ie:does not need to be installed, it may
1873 -- still remain in the startup tables if there are child user entities to
1874 -- install. When a route is installed, the child rows of parameters and
1875 -- context usages are fully refreshed. Child user entities are stil treated
1876 -- as only being installed if required. Database ietms and parameter values
1877 -- are fully refreshed if the parent user entity is required to be installed.
1878
1879 l_null_return varchar2(1); -- For 'select null' statements
1880 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
1881 l_skip_route_upd varchar2(1);
1882
1883 string varchar2(80);
1884 l_route_id number;
1885 l_route_name varchar2(2000);
1886 l_new_route BOOLEAN;
1887
1888 CURSOR c_all_159260
1889 IS
1890 select route_id route_id,
1891 route_name route_name
1892 from hr_s_routes
1893 where last_update_login = 159260;
1894
1895 CURSOR stu -- Selects all rows from startup entity
1896 IS
1897 select route_id c_surrogate_key
1898 ,route_name c_true_key
1899 ,user_defined_flag
1900 ,description
1901 ,text
1902 ,nvl(last_update_date,to_date('01-01-0001','DD-MM-YYYY')) last_update_date
1903 ,last_updated_by
1904 ,last_update_login
1905 ,created_by
1906 ,creation_date
1907 ,optimizer_hint
1908 ,rowid
1909 from hr_s_routes;
1910
1911 CURSOR user_entity (r_id IN number) -- To install all user entities
1912 IS -- for a given route
1913 select *
1914 from hr_s_user_entities
1915 where route_id = r_id;
1916
1917 CURSOR usage (r_id IN number) -- To install context usages
1918 IS -- for a given route
1919 select distinct *
1920 from hr_s_route_context_usages
1921 where route_id = r_id;
1922
1923 CURSOR parameter (r_id IN number) -- To install all route parameters
1924 IS -- for a given route
1925 select ROUTE_PARAMETER_ID
1926 , ROUTE_ID
1927 , DATA_TYPE
1928 , PARAMETER_NAME
1929 , SEQUENCE_NO
1930 , rowid
1931 from hr_s_route_parameters
1932 where route_id = r_id;
1933
1934 CURSOR parameter_value (ue_id IN number)
1935 IS
1936 -- To install parameter values for a given user entity
1937
1938 select *
1939 from hr_s_route_parameter_values
1940 where user_entity_id = ue_id;
1941
1942 CURSOR db_item (ue_id IN number)
1943 IS
1944 -- Cursor to install database items for a given user entity
1945
1946 select *
1947 from hr_s_database_items
1948 where user_entity_id = ue_id;
1949
1950 stu_rec stu%ROWTYPE;
1951
1952 PROCEDURE crt_exc (exception_type IN varchar2)
1953 ----------------------------------------------
1954 IS
1955 -- Reports any exceptions during the delivery of startup data to
1956 -- FF_ROUTES
1957
1958 BEGIN
1959 -- When the installation procedures encounter an error that cannot
1960 -- be handled, an exception is raised and all work is rolled back
1961 -- to the last savepoint. The installation process then continues
1962 -- with the next primary key to install. The same exception will
1963 -- not be raised more than once.
1964
1965
1966 rollback to new_route_name;
1967
1968 hr_legislation.insert_hr_stu_exceptions('ff_routes'
1969 , stu_rec.c_surrogate_key
1970 , exception_type
1971 , stu_rec.c_true_key);
1972
1973
1974 END crt_exc;
1975
1976 PROCEDURE check_id_conflicts
1977 ----------------------------
1978 IS
1979 -- Surrogate id conflicts may arise from two scenario's:
1980 -- 1. Where the newly select sequence value conflicts with values
1981 -- in the STU tables.
1982 -- 2. Where selected surrogate keys, from the installed tables,
1983 -- conflict with other rows in the STU tables.
1984 --
1985 -- Both of the above scenario's are tested for.
1986 -- The first is a simple match, where if a value is detected in the
1987 -- STU tables and the installed tables then a conflict is detected. In
1988 -- This instance all STU surrogate keys, for this table, are updated.
1989 -- Three tables are tested:
1990 -- 1. ff_routes
1991 -- 2. ff_user_entities
1992 -- 3. ff_route_parameters
1993 -- The second is tested for using the sequences.
1994 -- If the next value from the live sequence is within the range of
1995 -- delivered surrogate id's then the live sequence must be incremented.
1996 -- If no action is taken, then duplicates may be introduced into the
1997 -- delivered tables, and child rows may be totally invalidated.
2001 -- 3. ff_route_parameters_s
1998 -- This procedure will check three sequences
1999 -- 1. ff_routes_S
2000 -- 2. ff_user_entities_s
2002
2003 v_sequence_number number(9);
2004 v_min_delivered number(9);
2005 v_max_delivered number(9);
2006 --
2007 cursor get_ff_routes is
2008 select distinct null
2009 from ff_routes a
2010 where exists
2011 (select null
2012 from hr_s_routes b
2013 where a.route_id = b.route_id
2014 );
2015
2016 cursor get_ff_route_parameters is
2017 select distinct null
2018 from ff_route_parameters a
2019 where exists
2023 );
2020 (select null
2021 from hr_s_route_parameters b
2022 where b.route_parameter_id = a.route_parameter_id
2024
2025 cursor get_ff_user_entities is
2026 select distinct null
2027 from ff_user_entities a
2028 where exists
2029 (select null
2030 from hr_s_user_entities b
2031 where a.user_entity_id = b.user_entity_id
2032 );
2033 --
2034 BEGIN
2035
2036 -- Start with check against ff_routes
2037
2038
2039 BEGIN --check that the installde routes will not conflict
2040 --with the delivered values
2041
2042 --
2043 open get_ff_routes;
2044 fetch get_ff_routes into l_null_return;
2045 IF get_ff_routes%NOTFOUND OR get_ff_routes%NOTFOUND IS NULL THEN
2046 RAISE NO_DATA_FOUND;
2047 END IF;
2048 close get_ff_routes;
2049 --
2050 --conflict may exist
2051 --update all route_id's to remove conflict
2052
2053 update /*+NO_INDEX*/ hr_s_routes
2054 set route_id = route_id - 50000000;
2055
2056 update hr_s_application_ownerships
2057 set key_value = key_value - 50000000
2058 where key_name = 'ROUTE_ID';
2059
2060 update /*+NO_INDEX*/ hr_s_balance_dimensions
2061 set route_id = route_id - 50000000;
2062
2063 update /*+NO_INDEX*/ hr_s_dimension_routes
2064 set route_id = route_id - 50000000;
2065
2066 update /*+NO_INDEX*/ hr_s_route_context_usages
2067 set route_id = route_id - 50000000;
2068
2069 update /*+NO_INDEX*/ hr_s_user_entities
2070 set route_id = route_id - 50000000;
2071
2072 update /*+NO_INDEX*/ hr_s_route_parameters
2073 set route_id = route_id - 50000000;
2074
2075 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2076
2077 END; --check of route_id
2078
2079
2080 BEGIN --check that no conflict exists within the route
2081 --parameter id's
2082
2083 --
2084 open get_ff_route_parameters;
2085 fetch get_ff_route_parameters into l_null_return;
2086 IF get_ff_route_parameters%NOTFOUND
2087 OR get_ff_route_parameters%NOTFOUND IS NULL THEN
2088 RAISE NO_DATA_FOUND;
2089 END IF;
2090 close get_ff_route_parameters;
2091 --
2092 --Conflict exists, so update the stu values of the parameter id
2093
2094 update hr_s_route_parameters
2095 set route_parameter_id = route_parameter_id -50000000;
2096
2097 update hr_s_route_parameter_values
2098 set route_parameter_id = route_parameter_id -50000000;
2099
2100 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2101
2102 END; --check of route_parameter_id
2103
2104
2105
2106 BEGIN --check that no conflict exists with the id's of the installed
2107 --user entities, compared with the values of delivered UE's
2108
2109 --
2110 open get_ff_user_entities;
2111 fetch get_ff_user_entities into l_null_return;
2112 IF get_ff_user_entities%NOTFOUND OR get_ff_user_entities%NOTFOUND IS NULL THEN
2113 RAISE NO_DATA_FOUND;
2114 END IF;
2115 close get_ff_user_entities;
2116 --
2117 --conflict exists, so update the stu values of user_entity_id
2118
2119 update /*+NO_INDEX*/ hr_s_user_entities
2120 set user_entity_id = user_entity_id -50000000;
2121
2122 update /*+NO_INDEX*/ hr_s_database_items
2123 set user_entity_id = user_entity_id -50000000;
2124
2125 update /*+NO_INDEX*/ hr_s_route_parameter_values
2126 set user_entity_id = user_entity_id -50000000;
2127
2128 update /*+NO_INDEX*/ hr_s_report_format_items_f
2129 set user_entity_id = user_entity_id -50000000;
2130
2131 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2132
2133 END; --check of user_entity_id
2134
2135
2136 select min(route_id) - (count(*) *3)
2137 , max(route_id) + (count(*) *3)
2138 into v_min_delivered
2139 , v_max_delivered
2140 from hr_s_routes;
2141
2142 select ff_routes_s.nextval
2143 into v_sequence_number
2144 from dual;
2145
2146 IF v_sequence_number
2147 BETWEEN v_min_delivered AND v_max_delivered THEN
2148
2149 hr_legislation.munge_sequence('FF_ROUTES_S',
2150 v_sequence_number,
2151 v_max_delivered);
2152
2153 END IF;
2154
2155 -- Now check ff_user_entities
2156
2157
2158 select min(user_entity_id) - (count(*) *3)
2159 , max(user_entity_id) + (count(*) *3)
2160 into v_min_delivered
2161 , v_max_delivered
2162 from hr_s_user_entities;
2163
2164 select ff_user_entities_s.nextval
2165 into v_sequence_number
2166 from dual;
2167
2168 IF v_sequence_number
2169 BETWEEN v_min_delivered AND v_max_delivered THEN
2170
2171 hr_legislation.munge_sequence('FF_USER_ENTITIES_S',
2172 v_sequence_number,
2173 v_max_delivered);
2174
2175 END IF;
2176
2177 -- Now check ff_route_parameters
2178
2179
2183 , v_max_delivered
2180 select min(route_parameter_id) - (count(*) *3)
2181 , max(route_parameter_id) + (count(*) *3)
2182 into v_min_delivered
2184 from hr_s_route_parameters;
2185
2186 select ff_route_parameters_s.nextval
2187 into v_sequence_number
2188 from dual;
2189
2190 IF v_sequence_number
2191 BETWEEN v_min_delivered AND v_max_delivered THEN
2192
2193 hr_legislation.munge_sequence('FF_ROUTE_PARAMETERS_S',
2194 v_sequence_number,
2195 v_max_delivered);
2196
2197 END IF;
2198
2199 END check_id_conflicts;
2200
2201 PROCEDURE update_uid
2202 --------------------
2203 IS
2204 -- Subprogram to update surrogate UID and all occurrences in child rows
2205
2206 l_new_parameter_id number(9);
2207 l_new_entity_id number(9);
2208
2209 cursor c_form3(p_route_id number) is
2210 select distinct ffu.formula_id fid
2211 from ff_fdi_usages_f ffu
2212 where ffu.item_name in (select fdbi.user_name
2213 from ff_database_items fdbi,
2214 ff_user_entities fue
2215 where fdbi.user_entity_id = fue.user_entity_id
2216 and fue.route_id = p_route_id);
2217
2218 BEGIN
2219
2220 l_new_surrogate_key := null;
2221
2222 BEGIN
2223
2224 select distinct route_id
2225 into l_new_surrogate_key
2226 from ff_routes
2227 where route_name = stu_rec.c_true_key
2228 and user_defined_flag = 'N';
2229
2230 EXCEPTION WHEN NO_DATA_FOUND THEN
2231
2232
2233 select ff_routes_s.nextval
2234 into l_new_surrogate_key
2235 from dual;
2236
2237 WHEN TOO_MANY_ROWS THEN
2238 hr_legislation.hrrunprc_trace_on;
2239 hr_utility.trace('sel route ff_routes TMR');
2240 hr_utility.trace('route_name ' ||
2241 stu_rec.c_true_key);
2242 hr_legislation.hrrunprc_trace_off;
2243 raise;
2244 END;
2245
2246
2247 -- Update all child entities
2248
2249
2250 update hr_s_routes
2251 set route_id = l_new_surrogate_key
2252 where rowid = stu_rec.rowid;
2253
2254 update hr_s_application_ownerships
2255 set key_value = to_char(l_new_surrogate_key)
2256 where key_value = to_char(stu_rec.c_surrogate_key)
2257 and key_name = 'ROUTE_ID';
2258
2259 update hr_s_balance_dimensions
2260 set route_id = l_new_surrogate_key
2261 where route_id = stu_rec.c_Surrogate_key;
2262
2263 update hr_s_dimension_routes
2264 set route_id = l_new_surrogate_key
2265 where route_id = stu_rec.c_Surrogate_key;
2266
2267 update hr_s_route_context_usages
2268 set route_id = l_new_surrogate_key
2269 where route_id = stu_rec.c_Surrogate_key;
2270
2271
2272 FOR delivered_params IN parameter(stu_Rec.c_Surrogate_key) LOOP
2273
2274 BEGIN --select of new surrogate id
2275
2276
2277 select route_parameter_id
2278 into l_new_parameter_id
2279 from ff_route_parameters
2280 where sequence_no = delivered_params.sequence_no
2281 and parameter_name = delivered_params.parameter_name
2282 and route_id = l_new_surrogate_key;
2283
2284 EXCEPTION WHEN NO_DATA_FOUND THEN
2285
2289 so as to avoid any constraint violations */
2286 /* As this could be a change to either the sequence ordering
2287 or the parameter naming (or both) be safe and just delete
2288 all parameters for this route and reimport them from scratch
2290
2291 /* bug 5501644 */
2292 for r_form3 in c_form3(l_new_surrogate_key) loop
2293 delete ff_fdi_usages_f where formula_id = r_form3.fid;
2294 delete ff_compiled_info_f where formula_id = r_form3.fid;
2295 end loop;
2296
2297 delete ff_route_parameter_values
2298 where route_parameter_id in (
2299 select route_parameter_id
2300 from ff_route_parameters
2301 where route_id = l_new_surrogate_key);
2302
2303 delete ff_route_parameters
2304 where route_id = l_new_surrogate_key;
2305
2306 select ff_route_parameters_s.nextval
2307 into l_new_parameter_id
2308 from dual;
2309
2310 WHEN TOO_MANY_ROWS THEN
2311
2312 hr_legislation.hrrunprc_trace_on;
2313 hr_utility.trace('sel ff_route_parameters TMR');
2314 hr_utility.trace('parameter_name ' ||
2315 delivered_params.parameter_name);
2316 hr_utility.trace('route_id ' ||
2317 to_char(l_new_surrogate_key));
2318 hr_utility.trace('route_name ' ||
2319 stu_rec.c_true_key);
2320 hr_utility.trace('sequence_no ' ||
2321 to_char(delivered_params.sequence_no));
2322 hr_legislation.hrrunprc_trace_off;
2323 raise;
2324
2325 END; --select of new surrogate id
2326
2327 update hr_s_route_parameters
2328 set route_id = l_new_surrogate_key
2329 , route_parameter_id = l_new_parameter_id
2330 where route_parameter_id = delivered_params.route_parameter_id;
2331
2332 update hr_s_route_parameter_values
2333 set route_parameter_id = l_new_parameter_id
2334 where route_parameter_id = delivered_params.route_parameter_id;
2335
2336 END LOOP delivered_params;
2337
2338 FOR delivered_entities IN user_entity(stu_Rec.c_Surrogate_key) LOOP
2339
2340 -- PSEUDO
2341 -- Error if we clash on delivered data to pseudo data present
2342 IF hr_legislation.g_pseudo_enabled = 'Y' THEN
2343 -- Check if we have a pseduo seed clash if enabled
2344 -- Error handling is done in the procedure itself
2345 hr_legislation.PSEUDO_SEED_DATA_CLASH(P_SURROGATE_KEY_NAME => delivered_entities.user_entity_name,
2346 P_TABLE_NAME => 'FF_USER_ENTITIES',
2347 P_LEGISLATION_CODE => delivered_entities.legislation_code);
2348 END IF;
2349
2350 BEGIN
2351
2352 select user_entity_id
2353 into l_new_entity_id
2354 from ff_user_entities
2355 where user_entity_name = delivered_entities.user_entity_name
2356 and nvl(legislation_code,'X') = nvl(delivered_entities.legislation_code,'X')
2357 and route_id = l_new_surrogate_key;
2358
2359 EXCEPTION WHEN NO_DATA_FOUND THEN
2360
2361 select ff_user_entities_s.nextval
2362 into l_new_entity_id
2363 from dual;
2364
2365 WHEN TOO_MANY_ROWS THEN
2366 hr_legislation.hrrunprc_trace_on;
2367 hr_utility.trace('sel ff_user_entities TMR');
2368 hr_utility.trace('user_entity_name ' ||
2369 delivered_entities.user_entity_name);
2370 hr_utility.trace('route_id ' ||
2371 to_char(l_new_surrogate_key));
2372 hr_utility.trace('route_name ' ||
2373 stu_rec.c_true_key);
2374 hr_utility.trace(':lc: ' || ':' ||
2375 delivered_entities.legislation_code || ':');
2376 hr_legislation.hrrunprc_trace_off;
2377 raise;
2378 END;
2379
2380 update hr_s_user_entities
2381 set user_entity_id = l_new_entity_id
2382 , route_id = l_new_surrogate_key
2383 where user_entity_id = delivered_entities.user_entity_id;
2384
2385 update hr_s_database_items
2386 set user_entity_id = l_new_entity_id
2387 where user_entity_id = delivered_entities.user_entity_id;
2388
2389 update hr_s_route_parameter_values
2390 set user_entity_id = l_new_entity_id
2391 where user_entity_id = delivered_entities.user_entity_id;
2392
2393 update hr_s_report_format_items_f
2394 set user_entity_id = l_new_entity_id
2395 where user_entity_id = delivered_entities.user_entity_id;
2396
2397 END LOOP;
2398
2399 END update_uid;
2400
2401 PROCEDURE remove (v_route_id IN number)
2402 ---------------------------------------
2403 IS
2404 -- Remove a row from the startup tables
2405
2406 BEGIN
2407
2408 delete from hr_s_database_items a
2409 where a.user_entity_id in
2410 (select b.user_entity_id
2411 from hr_s_user_entities b
2412 where b.route_id = v_route_id
2413 );
2414
2415 delete from hr_s_route_parameter_values a
2416 where a.user_entity_id in
2417 (select b.user_entity_id
2418 from hr_s_user_entities b
2419 where b.route_id = v_route_id
2420 );
2421
2422 delete from hr_s_user_entities
2423 where route_id = v_route_id;
2424
2425 delete from hr_s_route_context_usages
2426 where route_id = v_route_id;
2427
2428 delete from hr_s_route_parameters
2429 where route_id = v_route_id;
2430
2431 delete from hr_s_routes
2432 where route_id = v_route_id;
2433
2434 END remove;
2435
2436 FUNCTION valid_ownership RETURN BOOLEAN
2437 ---------------------------------------
2438 IS
2439 -- Test ownership of this current row
2440 cursor get_application_ownerships is
2441 select null
2442 from dual
2443 where exists
2444 (select null
2445 from hr_s_application_ownerships a
2446 , fnd_product_installations b
2447 , fnd_application c
2448 where a.key_name = 'ROUTE_ID'
2449 and a.key_value = stu_rec.c_surrogate_key
2450 and a.product_name = c.application_short_name
2451 and c.application_id = b.application_id
2452 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2453 or
2454 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2455 --
2456 BEGIN
2457
2458 -- This routine only operates in phase 1. Rows are present in the
2459 -- table hr_application_ownerships in the delivery account, which
2463 -- then this row is not required and may be deleted from the delivery
2460 -- dictate which products a piece of data is used for. If the query
2461 -- returns a row then this data is required, and the function will
2462 -- return true. If no rows are returned and an exception is raised,
2464 -- tables.
2465
2466 -- If legislation code and subgroup code are included on the delivery
2467 -- tables, a check must be made to determine if the data is defined for
2468 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
2469 -- installation.
2470
2471 -- A return code of TRUE indicates that the row is required.
2472
2473 -- The exception is raised within this procedure if no rows are returned
2474 -- in this select statement. If no rows are returned then one of the
2475 -- following is true:
2476 -- 1. No ownership parameters are defined.
2477 -- 2. The products, for which owning parameters are defined, are not
2478 -- installed with as status of 'I'.
2479 -- 3. The data is defined for a legislation subgroup that is not active.
2480
2481 IF p_phase <> 1 THEN -- Only perform in phase 1
2482 return TRUE;
2483 END IF;
2484
2485
2486 -- If exception raised below then this row is not needed
2487 --
2488 open get_application_ownerships;
2489 fetch get_application_ownerships into l_null_return;
2490 IF get_application_ownerships%NOTFOUND OR get_application_ownerships%NOTFOUND IS NULL THEN
2491 RAISE NO_DATA_FOUND;
2492 END IF;
2493 close get_application_ownerships;
2494 --
2495 -- Indicate row is required
2496
2497 return TRUE;
2498
2499 EXCEPTION WHEN NO_DATA_FOUND THEN
2500
2501 -- Row not needed for any installed product
2502
2503 remove(stu_Rec.c_surrogate_key);
2504
2505 -- Indicate row not needed
2506
2507 return FALSE;
2508
2509 END valid_ownership;
2510
2511
2512 FUNCTION route_changed (p_route_id IN number,
2513 p_new_route OUT nocopy boolean) RETURN BOOLEAN
2514 ------------------------------------------------------------
2515 IS
2516 -- Function to test if the current route is different to the one
2517 -- installed. If the route is different the function will return true.
2518 --
2519 -- Changed the comparison rules so that the routes are now only
2520 -- flagged as different if the route text itself has changed. In the
2521 -- past the routes were also considered to be different if the last
2522 -- update dates were differed. Unfortunately this was the case whenever
2523 -- a dump file was recreated, even though the route text was unchanged.
2524 -- This led to all routes being trashed and recreated on the target
2525 -- account, db items being lost etc. RMF 26.09.95.
2531 v_route_text clob; -- Used to select the installed route text
2526 --
2527 -- Optimizer hint now can trigger update
2528 --
2529 -- from 12.1 also verify route is not a stub (description prefix)
2530
2532 v_optimizer_hint ff_routes.optimizer_hint%type;
2533 v_route_description ff_routes.description%type;
2534 v_last_update date; -- Used to select the installed last update
2535
2536 BEGIN
2537
2538 IF l_skip_route_upd = 'Y' THEN
2539 -- We have set PAP to skip route changes
2540 return FALSE;
2541 END IF;
2542
2543 select text, optimizer_hint , description
2544 into v_route_text, v_optimizer_hint, v_route_description
2545 from ff_routes
2546 where route_id = p_route_id;
2547
2548 IF (( substr(fnd_release.release_name,1,4) = '12.2' ) or
2549 ( substr(fnd_release.release_name,1,4) = '12.1' )) and
2550 substr(v_route_description,1,4) = 'STUB' then
2551 -- bug 10277405 from 12.2, 12.1 don't transfer stub routes
2552 return FALSE;
2553 END IF;
2554
2555 IF v_route_text = stu_rec.text AND
2556 nvl(v_optimizer_hint, 'nohint') =
2557 nvl(stu_rec.optimizer_hint, 'nohint') THEN
2558 -- Route text and hint is identical
2559 return FALSE;
2560 END IF;
2561
2562 p_new_route := FALSE;
2563 hr_legislation.hrrunprc_trace_on;
2564 hr_utility.trace('route diff: ' || to_char(l_new_surrogate_key) || ' '
2565 || stu_rec.c_true_key);
2566 hr_legislation.hrrunprc_trace_off;
2567
2568 return TRUE; --delivered route is defferent
2569
2570 EXCEPTION WHEN NO_DATA_FOUND THEN
2571 -- The route is not installed
2572 p_new_route := TRUE;
2573 hr_legislation.hrrunprc_trace_on;
2574 hr_utility.trace('new route: ' || to_char(l_new_surrogate_key) || ' '
2575 || stu_rec.c_true_key);
2576 hr_legislation.hrrunprc_trace_off;
2577 return TRUE;
2578
2579 END;
2580
2581 FUNCTION valid_to_insert RETURN BOOLEAN
2582 ---------------------------------------
2583 IS
2584 -- Check to see if the route can be installed.
2585 --
2586 l_number number(9);
2587 --
2588
2589 BEGIN
2590 -- Test to see if the route has been created already by a user. The
2591 -- function will return true if the route is okay to be installed.
2592
2593 --
2594 select count(*)
2595 into l_number
2596 from ff_routes a
2597 where a.route_name = stu_rec.c_true_key
2598 and a.user_defined_flag = 'Y';
2599 --
2600 if l_number = 0 or l_number is null then
2601 return TRUE;
2602 else
2603 --This Route name is an existing User-Defined route
2604 --So cannot be delivered.
2605 crt_exc('FF_Routes row already created by a user');
2606 return FALSE;--indicates this row is not to be transferred
2607 end if;
2608
2609 EXCEPTION WHEN NO_DATA_FOUND THEN
2610
2611
2612 -- No user created row exists for this primary key
2613
2614 return TRUE;
2615
2616 END valid_to_insert;
2617
2618 FUNCTION user_entity_changed (v_user_entity_id IN number) RETURN BOOLEAN
2619 ------------------------------------------------------------------------
2620 IS
2621 -- Check to see if the current user entity differs from the install one
2622 -- TRUE is returned if the user entity differs
2623
2624 ue_name1 varchar2(240);
2625 ue_name2 varchar2(240);
2626
2627 BEGIN
2628
2629 select null
2630 into l_null_return
2631 from dual
2632 where exists
2633 ((select
2634 BUSINESS_GROUP_ID,
2635 LEGISLATION_CODE,
2636 ROUTE_ID,
2637 NOTFOUND_ALLOWED_FLAG,
2638 USER_ENTITY_NAME,
2639 CREATOR_ID,
2640 CREATOR_TYPE,
2641 ENTITY_DESCRIPTION
2642 from hr_s_user_entities
2643 where user_entity_id = v_user_entity_id
2644 MINUS
2645 select
2646 BUSINESS_GROUP_ID,
2647 LEGISLATION_CODE,
2648 ROUTE_ID,
2649 NOTFOUND_ALLOWED_FLAG,
2650 USER_ENTITY_NAME,
2651 CREATOR_ID,
2652 CREATOR_TYPE,
2653 ENTITY_DESCRIPTION
2654 from ff_user_entities
2655 where user_entity_id = v_user_entity_id
2656 )
2657 UNION
2658 (select
2659 BUSINESS_GROUP_ID,
2660 LEGISLATION_CODE,
2661 ROUTE_ID,
2662 NOTFOUND_ALLOWED_FLAG,
2663 USER_ENTITY_NAME,
2664 CREATOR_ID,
2665 CREATOR_TYPE,
2666 ENTITY_DESCRIPTION
2667 from ff_user_entities
2668 where user_entity_id = v_user_entity_id
2669 MINUS
2670 select
2671 BUSINESS_GROUP_ID,
2672 LEGISLATION_CODE,
2673 ROUTE_ID,
2674 NOTFOUND_ALLOWED_FLAG,
2675 USER_ENTITY_NAME,
2676 CREATOR_ID,
2677 CREATOR_TYPE,
2678 ENTITY_DESCRIPTION
2679 from hr_s_user_entities
2680 where user_entity_id = v_user_entity_id
2681 ))
2682 or exists
2683 (select user_name,
2684 data_type,
2685 definition_text,
2686 null_allowed_flag,
2687 description
2688 from hr_s_database_items
2689 where user_entity_id = v_user_entity_id
2690 MINUS
2691 select user_name,
2692 data_type,
2693 definition_text,
2694 null_allowed_flag,
2695 description
2696 from ff_database_items
2697 where user_entity_id = v_user_entity_id)
2698 or exists
2699 (select value
2700 from hr_s_route_parameter_values
2701 where user_entity_id = v_user_entity_id
2702 MINUS
2703 select value
2704 from ff_route_parameter_values
2708 begin
2705 where user_entity_id = v_user_entity_id);
2706
2707 -- Show that this user entity differs from the install one
2709 select user_entity_name
2710 into ue_name1
2711 from ff_user_entities
2712 where user_entity_id = v_user_entity_id;
2713 select user_entity_name
2714 into ue_name2
2715 from hr_s_user_entities
2716 where user_entity_id = v_user_entity_id;
2717 -- hr_legislation.hrrunprc_trace_on;
2718 -- hr_utility.trace('ue chg: ' || to_char(v_user_entity_id) || ' '
2719 -- || ue_name1 || ':' || ue_name2);
2720 -- hr_legislation.hrrunprc_trace_off;
2721 exception when others then null;
2722 end;
2723
2724 return TRUE;
2725
2726 EXCEPTION WHEN NO_DATA_FOUND THEN
2727
2728
2729 return FALSE;
2730
2731 END user_entity_changed;
2732
2733 FUNCTION install_user_entity (v_route_id IN number) RETURN BOOLEAN
2734 ------------------------------------------------------------------
2735 IS
2736 -- Logic to insert the user entity and all children. If called in pahse one
2737 -- TRUE is returned as soon as a user entity is found that has to be installed.
2738 -- If no user entities are to be installed then FALSE is returned.
2739
2740 cursor c_form(p_ue_id number) is
2741 select distinct fue.formula_id fid
2742 from ff_fdi_usages_f fue
2743 where fue.item_name in (select fdbi.user_name
2744 from ff_database_items fdbi
2745 where fdbi.user_entity_id = p_ue_id);
2746
2747 BEGIN
2748
2749 FOR all_user_entities IN user_entity(v_route_id) LOOP
2750
2751
2752 IF user_entity_changed(all_user_entities.user_entity_id) THEN
2753
2754 IF p_phase = 1 THEN
2755 return TRUE;
2756 END IF;
2757
2758 -- delete all formula usages, compiled info that may be
2759 -- affected by this dbi
2760 for r_form in c_form(all_user_entities.user_entity_id) loop
2761 delete ff_fdi_usages_f where formula_id = r_form.fid;
2762 delete ff_compiled_info_f where formula_id = r_form.fid;
2763 end loop;
2764
2765 update ff_user_entities
2766 set business_group_id = all_user_entities.business_group_id
2767 ,legislation_code = all_user_entities.legislation_code
2768 ,route_id = all_user_entities.route_id
2769 ,notfound_allowed_flag = all_user_entities.notfound_allowed_flag
2770 ,user_entity_name = all_user_entities.user_entity_name
2771 ,creator_id = all_user_entities.creator_id
2772 ,creator_type = all_user_entities.creator_type
2773 ,entity_description = all_user_entities.entity_description
2774 ,last_update_date = all_user_entities.last_update_date
2775 ,last_updated_by = all_user_entities.last_updated_by
2776 ,last_update_login = all_user_entities.last_update_login
2777 ,created_by = all_user_entities.created_by
2778 ,creation_date = all_user_entities.creation_date
2779 where user_entity_id = all_user_entities.user_entity_id;
2780
2781 IF SQL%NOTFOUND THEN
2782
2783 BEGIN
2784 insert into ff_user_entities
2785 (user_entity_id
2786 ,business_group_id
2787 ,legislation_code
2788 ,route_id
2789 ,notfound_allowed_flag
2790 ,user_entity_name
2791 ,creator_id
2792 ,creator_type
2793 ,entity_description
2794 ,last_update_date
2795 ,last_updated_by
2796 ,last_update_login
2797 ,created_by
2798 ,creation_date
2799 )
2800 values
2801 (all_user_entities.user_entity_id
2802 ,all_user_entities.business_group_id
2803 ,all_user_entities.legislation_code
2804 ,all_user_entities.route_id
2805 ,all_user_entities.notfound_allowed_flag
2806 ,all_user_entities.user_entity_name
2807 ,all_user_entities.creator_id
2808 ,all_user_entities.creator_type
2809 ,all_user_entities.entity_description
2810 ,all_user_entities.last_update_date
2811 ,all_user_entities.last_updated_by
2812 ,all_user_entities.last_update_login
2813 ,all_user_entities.created_by
2814 ,all_user_entities.creation_date
2815 );
2816
2817 EXCEPTION WHEN OTHERS THEN
2818 hr_legislation.hrrunprc_trace_on;
2819 hr_utility.trace('ins ff_user_entities');
2820 hr_utility.trace('user_entity_name ' ||
2821 all_user_entities.user_entity_name);
2822 hr_utility.trace('user_entity_id ' ||
2823 to_char(all_user_entities.user_entity_id));
2824 hr_utility.trace('route_id ' ||
2825 to_char(all_user_entities.route_id));
2826 hr_utility.trace('route_name ' ||
2827 stu_rec.c_true_key);
2828 hr_utility.trace('creator_id ' ||
2829 to_char(all_user_entities.creator_id));
2830 hr_utility.trace('creator_type ' ||
2831 all_user_entities.creator_type);
2832 hr_utility.trace(':lc: ' || ':' ||
2833 all_user_entities.legislation_code || ':');
2834 hr_legislation.hrrunprc_trace_off;
2835 raise;
2836 END;
2837 END IF;
2838
2839 FOR all_db_items IN
2840 db_item(all_user_entities.user_entity_id)
2841 LOOP
2842
2843 update ff_database_items
2844 set data_type = all_db_items.data_type
2845 ,definition_text = all_db_items.definition_text
2846 ,null_allowed_flag = all_db_items.null_allowed_flag
2847 ,description = all_db_items.description
2848 ,last_update_date = all_db_items.last_update_date
2849 ,last_updated_by = all_db_items.last_updated_by
2850 ,last_update_login = all_db_items.last_update_login
2851 ,created_by = all_db_items.created_by
2852 ,creation_date = all_db_items.creation_date
2853 where user_name = all_db_items.user_name
2854 and user_entity_id = all_db_items.user_entity_id;
2855
2856 IF SQL%NOTFOUND THEN
2857
2858 BEGIN
2859 insert into ff_database_items
2863 ,definition_text
2860 (user_name
2861 ,user_entity_id
2862 ,data_type
2864 ,null_allowed_flag
2865 ,description
2866 ,last_update_date
2867 ,last_updated_by
2868 ,last_update_login
2869 ,created_by
2870 ,creation_date
2871 )
2872 VALUES
2873 (all_db_items.user_name
2874 ,all_db_items.user_entity_id
2875 ,all_db_items.data_type
2876 ,all_db_items.definition_text
2877 ,all_db_items.null_allowed_flag
2878 ,all_db_items.description
2879 ,all_db_items.last_update_date
2880 ,all_db_items.last_updated_by
2881 ,all_db_items.last_update_login
2882 ,all_db_items.created_by
2883 ,all_db_items.creation_date
2884 );
2885 EXCEPTION WHEN OTHERS THEN
2886 hr_legislation.hrrunprc_trace_on;
2887 hr_utility.trace('ins ff_database_items');
2888 hr_utility.trace('dbi user_name ' ||
2889 all_db_items.user_name);
2890 hr_utility.trace('user_entity_id ' ||
2891 to_char(all_db_items.user_entity_id));
2892 hr_utility.trace('user_entity_name ' ||
2893 all_user_entities.user_entity_name);
2894 hr_utility.trace('route_id ' ||
2895 to_char(all_user_entities.route_id));
2896 hr_utility.trace('route_name ' ||
2897 stu_rec.c_true_key);
2898 hr_utility.trace('creator_id ' ||
2899 to_char(all_user_entities.creator_id));
2900 hr_utility.trace('creator_type ' ||
2901 all_user_entities.creator_type);
2902 hr_legislation.hrrunprc_trace_off;
2903 raise;
2904 END;
2905
2906 END IF;
2907
2908 END LOOP all_db_items;
2909
2910 FOR pvalues IN
2911 parameter_value(all_user_entities.user_entity_id)
2912 LOOP
2913
2914 BEGIN
2915
2916 update ff_route_parameter_values
2917 set value = pvalues.value
2918 ,last_update_date = pvalues.last_update_date
2919 ,last_updated_by = pvalues.last_updated_by
2920 ,last_update_login = pvalues.last_update_login
2921 ,created_by = pvalues.created_by
2922 ,creation_date = pvalues.creation_date
2923 where route_parameter_id = pvalues.route_parameter_id
2924 and user_entity_id = pvalues.user_entity_id;
2925
2926 IF SQL%NOTFOUND THEN
2927
2928 BEGIN
2929 insert into ff_route_parameter_values
2930 (route_parameter_id
2931 ,user_entity_id
2932 ,value
2933 ,last_update_date
2934 ,last_updated_by
2935 ,last_update_login
2936 ,created_by
2937 ,creation_date
2938 )
2939 VALUES
2940 (pvalues.route_parameter_id
2941 ,pvalues.user_entity_id
2942 ,pvalues.value
2943 ,pvalues.last_update_date
2944 ,pvalues.last_updated_by
2945 ,pvalues.last_update_login
2946 ,pvalues.created_by
2947 ,pvalues.creation_date
2948 );
2949 EXCEPTION WHEN OTHERS THEN
2950 hr_legislation.hrrunprc_trace_on;
2951 hr_utility.trace('ins ff_route_parameter_values');
2952 hr_utility.trace('value ' ||
2953 pvalues.value);
2954 hr_utility.trace('route_parameter_id ' ||
2955 to_char(pvalues.route_parameter_id));
2956 hr_utility.trace('user_entity_id ' ||
2957 to_char(pvalues.user_entity_id));
2958 hr_utility.trace('user_entity_name ' ||
2959 all_user_entities.user_entity_name);
2960 hr_utility.trace('route_id ' ||
2961 to_char(all_user_entities.route_id));
2962 hr_utility.trace('route_name ' ||
2963 stu_rec.c_true_key);
2964 hr_utility.trace('creator_id ' ||
2965 to_char(all_user_entities.creator_id));
2966 hr_utility.trace('creator_type ' ||
2967 all_user_entities.creator_type);
2968 hr_legislation.hrrunprc_trace_off;
2969 raise;
2970 END;
2971
2972 END IF;
2973
2974 END;
2975
2976 END LOOP pvalues;
2977
2978
2979 END IF;
2980
2981 END LOOP all_user_entities;
2982
2983 IF p_phase = 1 THEN
2984 return FALSE;
2985 ELSE
2986 return TRUE;
2987 END IF;
2988
2989 END install_user_entity;
2990
2991 PROCEDURE delete_route_form_usage
2992 ---------------------------------
2993 IS
2994
2995 cursor c_form2(p_route_id number) is
2996 select /*+ LEADING (FUE,FDBI) */
2997 distinct formula_id fid
2998 from
2999 ff_user_entities fue,
3000 ff_database_items fdbi,
3001 ff_fdi_usages_f fdi
3002 where fdi.item_name = fdbi.user_name
3003 and fdbi.user_entity_id = fue.user_entity_id
3004 and fue.route_id = p_route_id;
3005
3006 BEGIN
3007 -- delete all formula usages, compiled info that may be
3008 -- affected by this dbi
3009 for r_form2 in c_form2(stu_rec.c_surrogate_key) loop
3010
3011 delete ff_fdi_usages_f where formula_id = r_form2.fid;
3012 delete ff_compiled_info_f where formula_id = r_form2.fid;
3013 end loop;
3014
3015 END delete_route_form_usage;
3016
3017 PROCEDURE insert_route
3018 ----------------------
3019 IS
3020 -- Logic to insert or update a route, depending upon whether it exists
3021 -- already in the live tables
3022
3023 BEGIN
3024
3025 update ff_routes
3026 set user_defined_flag = stu_rec.user_defined_flag
3027 , description = stu_Rec.description
3028 , text = stu_rec.text
3029 , last_update_date = stu_rec.last_update_date
3030 , last_updated_by = stu_rec.last_updated_by
3031 , last_update_login = stu_rec.last_update_login
3032 , created_by = stu_rec.created_by
3033 , creation_date = stu_rec.creation_date
3034 , optimizer_hint = stu_rec.optimizer_hint
3035 where route_id = stu_rec.c_Surrogate_key;
3036
3037 IF SQL%NOTFOUND THEN
3038
3039 BEGIN
3040 insert into ff_Routes
3041 (route_id
3042 ,route_name
3043 ,user_defined_flag
3044 ,description
3045 ,text
3046 ,last_update_date
3047 ,last_updated_by
3048 ,last_update_login
3049 ,created_by
3050 ,creation_date
3051 ,optimizer_hint
3052 )
3053 values
3054 (stu_rec.c_surrogate_key
3055 ,stu_rec.c_true_key
3056 ,stu_rec.user_defined_flag
3057 ,stu_rec.description
3058 ,stu_rec.text
3059 ,stu_rec.last_update_date
3060 ,stu_rec.last_updated_by
3061 ,stu_rec.last_update_login
3062 ,stu_rec.created_by
3063 ,stu_rec.creation_date
3064 ,stu_rec.optimizer_hint
3065 );
3066 EXCEPTION WHEN OTHERS THEN
3067 hr_legislation.hrrunprc_trace_on;
3068 hr_utility.trace('ins ff_routes');
3069 hr_utility.trace('route_name ' ||
3070 stu_rec.c_true_key);
3071 hr_utility.trace('route_id ' ||
3072 to_char(stu_rec.c_surrogate_key));
3073 hr_legislation.hrrunprc_trace_off;
3074 raise;
3075 END;
3076
3077 END IF;
3078
3079 END insert_route;
3080
3081 PROCEDURE transfer_row
3082 ----------------------
3083 IS
3084 -- Procedure to transfer a route from the delivery tables
3085 cursor c_ff_contexts_null (c_context_id in number) is
3086 select null
3087 from ff_contexts
3088 where context_id = c_context_id;
3089 --
3090 cursor c_ffrp_null (c_route_parameter_id in number) is
3091 select null
3092 from ff_route_parameters
3093 where route_parameter_id = c_route_parameter_id;
3094 --
3095 cursor c_ff_rcu_pop (c_route_id in number,
3096 c_sequence_no in number,
3097 c_context_id in number) is
3098 select distinct null
3099 from ff_route_context_usages
3100 where route_id = c_route_id
3101 and sequence_no = c_sequence_no
3102 and context_id = c_context_id;
3103 --
3104 BEGIN
3105 --
3106 IF p_phase = 1 THEN
3107 IF route_changed(l_new_surrogate_key, l_new_route) THEN
3108 IF NOT valid_to_insert THEN
3109 return;
3110 END IF;
3111 ELSE
3112 -- Route has not changed, check user entities
3113
3114 IF install_user_entity(l_new_surrogate_key) THEN
3115 null;
3116 ELSE
3117 -- No user entities to install
3118 remove(l_new_surrogate_key);
3119 return;
3120 END IF;
3121 END IF;
3122 ELSE
3123 -- Phase = 2
3124 IF route_changed(stu_rec.c_surrogate_key, l_new_route) THEN
3125 --
3126 IF NOT valid_to_insert THEN
3127 return;
3128 END IF;
3129 delete_route_form_usage;
3130 insert_route;
3131 --
3132 -- Ensure we rebuild all balance user entities and associated
3133 -- items in rebuild ele input bal for a changed route
3134 --
3135 IF NOT l_new_route THEN
3139 END IF;
3136 delete from ff_user_entities
3137 where creator_type in ('B', 'RB')
3138 and route_id = stu_rec.c_surrogate_key;
3140 --
3141 -- Changing or inserting route so delete live ctx usages
3142 -- for this route
3143 --
3144 delete ff_route_context_usages
3145 where route_id = stu_rec.c_surrogate_key;
3146 --
3147 -- Now install route ctx usages
3148 --
3149 FOR context_usages IN usage(stu_rec.c_surrogate_key) LOOP
3150
3151 BEGIN
3152 --
3153 open c_ff_contexts_null (context_usages.context_id);
3154 fetch c_ff_contexts_null into l_null_return;
3155 IF c_ff_contexts_null%NOTFOUND OR c_ff_contexts_null%NOTFOUND IS NULL THEN
3156 close c_ff_contexts_null;
3157 RAISE NO_DATA_FOUND;
3158 END IF;
3159 close c_ff_contexts_null;
3160 --
3161 BEGIN
3162 insert into ff_route_context_usages
3163 (route_id
3164 ,context_id
3165 ,sequence_no
3166 )
3167 values
3168 (context_usages.route_id
3169 ,context_usages.context_id
3170 ,context_usages.sequence_no
3171 );
3172 EXCEPTION WHEN OTHERS THEN
3173 hr_legislation.hrrunprc_trace_on;
3174 hr_utility.trace('ins ff_route_context_usages');
3175 hr_utility.trace('route_id ' ||
3176 to_char(context_usages.route_id));
3177 hr_utility.trace('route_name ' ||
3178 stu_rec.c_true_key);
3179 hr_utility.trace('context_id ' ||
3180 to_char(context_usages.context_id));
3181 hr_utility.trace('sequence_no ' ||
3182 to_char(context_usages.sequence_no));
3183 hr_legislation.hrrunprc_trace_off;
3184 raise;
3185 END;
3186
3187 EXCEPTION WHEN NO_DATA_FOUND THEN
3188
3189 crt_exc('Parent context not installed');
3190 return;
3191 END;
3192 END LOOP;
3193
3194 FOR r_hrsrp in parameter (stu_rec.c_surrogate_key) LOOP
3195
3196 BEGIN
3197
3198 open c_ffrp_null (r_hrsrp.ROUTE_PARAMETER_ID);
3199 fetch c_ffrp_null into l_null_return;
3200 IF c_ffrp_null%NOTFOUND OR c_ffrp_null%NOTFOUND IS NULL THEN
3201 close c_ffrp_null;
3202 RAISE NO_DATA_FOUND;
3203 END IF;
3204 close c_ffrp_null;
3205
3206 update ff_route_parameters
3207 set ROUTE_ID = stu_rec.c_surrogate_key
3208 ,DATA_TYPE = r_hrsrp.DATA_TYPE
3209 ,PARAMETER_NAME = r_hrsrp.PARAMETER_NAME
3210 ,SEQUENCE_NO = r_hrsrp.SEQUENCE_NO
3211 where ROUTE_PARAMETER_ID = r_hrsrp.ROUTE_PARAMETER_ID;
3212
3213 EXCEPTION WHEN NO_DATA_FOUND THEN
3214
3215 BEGIN
3216 insert into ff_route_parameters
3217 (ROUTE_PARAMETER_ID
3218 ,ROUTE_ID
3219 ,DATA_TYPE
3220 ,PARAMETER_NAME
3221 ,SEQUENCE_NO
3222 )
3223 values
3224 (r_hrsrp.ROUTE_PARAMETER_ID,
3225 stu_rec.c_surrogate_key,
3226 r_hrsrp.DATA_TYPE,
3227 r_hrsrp.PARAMETER_NAME,
3228 r_hrsrp.SEQUENCE_NO);
3229 EXCEPTION WHEN OTHERS THEN
3230 hr_legislation.hrrunprc_trace_on;
3231 hr_utility.trace('ins ff_route_parameters');
3232 hr_utility.trace('PARAMETER_NAME ' ||
3233 r_hrsrp.PARAMETER_NAME);
3234 hr_utility.trace('SEQUENCE_NO ' ||
3235 to_char(r_hrsrp.SEQUENCE_NO));
3236 hr_utility.trace('ROUTE_ID ' ||
3237 to_char(stu_rec.c_surrogate_key));
3238 hr_utility.trace('route_name ' ||
3239 stu_rec.c_true_key);
3240 hr_utility.trace('ROUTE_PARAMETER_ID ' ||
3241 to_char(r_hrsrp.ROUTE_PARAMETER_ID));
3242 hr_legislation.hrrunprc_trace_off;
3243 raise;
3244 END;
3245
3246 END;
3247
3248 END LOOP;
3249
3250 END IF;
3251 --
3252 IF NOT install_user_entity(stu_rec.c_surrogate_key) THEN
3253 return;
3254 END IF;
3255 remove(stu_rec.c_surrogate_key);
3256 END IF;
3257
3258 END transfer_row;
3259
3260 BEGIN
3261 -- This is the main loop to perform the installation logic. A cursor
3262 -- is opened to control the loop, and each row returned is placed
3263 -- into a record defined within the main procedure so each sub
3264 -- procedure has full access to all returrned columns. For each
3265 -- new row returned, a new savepoint is declared. If at any time
3266 -- the row is in error a rollback iss performed to the savepoint
3267 -- and the next row is returned. Ownership details are checked and
3268 -- if the row is required then the surrogate id is updated and the
3269 -- main transfer logic is called.
3270
3271 -- Need to disable the UE constraint so we can update user entities
3272 -- outside of the main transfer process so we dont lose our savepoints
3273 -- via implicit commits
3274
3275 -- disable_ffuebru_trig;
3276
3277 l_skip_route_upd := 'N';
3278
3279 BEGIN
3280 select 'Y'
3281 into l_skip_route_upd
3282 from pay_patch_status
3283 where patch_name = 'HRGLOBAL_DBG_NRCOD';
3284 EXCEPTION WHEN OTHERS THEN
3285 l_skip_route_upd := 'N';
3286 END;
3287
3288 IF p_phase = 1 THEN
3289 check_id_conflicts; --attempt to detect and remove any possible
3290 --conflicts with surrogate id's that are being
3291 --delivered.
3292
3293 END IF;
3294
3295 FOR delivered IN stu LOOP
3296
3297 -- This uses main cursor stu to impilicity define a record
3298
3299
3300 savepoint new_route_name;
3301
3302 stu_rec := delivered;
3303
3304 IF p_phase = 2 THEN
3305 l_new_surrogate_key := stu_rec.c_surrogate_key;
3306 END IF;
3307
3308 IF valid_ownership THEN
3309
3310 -- Test the row onerships for the current row
3311
3312
3313 IF p_phase = 1 THEN
3314 update_uid;
3315 END IF;
3316
3317 transfer_row;
3318
3319 END IF;
3320
3321 END LOOP delivered;
3322
3323 -- enable_ffuebru_trig;
3324
3325 END install_routes;
3326
3327
3328 --****************************************************************************
3329 -- INSTALLATION PROCEDURE FOR : FF_FUNCTIONS
3330 --****************************************************************************
3331
3332 PROCEDURE install_functions(p_phase IN number)
3333 ----------------------------------------------
3334 IS
3335 -- Install procedure to transfer startup element classifications into
3336 -- a live account.
3337
3338 -- The installation of functions is controlled by a main cursor which
3339 -- selects distinct function names from the startup tables.
3340
3341 -- For each of these function names if the installed functions differ to the
3342 -- delivered ones, all installed functions of this name will refreshed.
3343
3344 l_null_return varchar2(1); -- For 'select null' statements
3345 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3346
3347 CURSOR stu -- Selects all rows from startup entity
3348 IS
3349 select distinct name
3350 , legislation_code c_leg_code
3351 from hr_s_functions;
3352
3353 CURSOR distinct_function(f_name IN varchar2, c_leg_code IN varchar2)
3354 IS
3355 -- Cursor to select distinct functions
3356
3357 select *
3358 from hr_s_functions
3359 where name = f_name
3360 and nvl(legislation_code, 'X') = nvl(c_leg_code, 'X');
3361
3362 CURSOR usages(f_id IN number)
3363 IS
3364 -- Cursor to install child context usages
3365
3366 select *
3367 from hr_s_function_context_usages
3368 where function_id = f_id;
3369
3370 stu_rec stu%ROWTYPE;
3371
3372 PROCEDURE crt_exc (exception_type IN varchar2)
3373 ----------------------------------------------
3374 IS
3375 -- Reports any exceptions during the delivery of startup data to
3376 -- FF_FUNCTIONS
3377
3378 BEGIN
3379 -- When the installation procedures encounter an error that cannot
3380 -- be handled, an exception is raised and all work is rolled back
3381 -- to the last savepoint. The installation process then continues
3382 -- with the next primary key to install. The same exception will
3383 -- not be raised more than once.
3384
3385 -- The exception text is set to a composite value because this exception
3386 -- is raised against a function name, not a function id. Consequently
3387 -- the surrogate id is set to a value of 0.
3388
3389
3390 rollback to new_function_name;
3391
3392 hr_legislation.insert_hr_stu_exceptions('ff_functions'
3393 , 0
3394 , exception_type
3395 , stu_rec.name);
3396
3397
3398 END crt_exc;
3399
3400 PROCEDURE remove(target varchar2)
3401 ---------------------------------
3402 IS
3403 -- Remove a row from either the startup tables or the installed tables
3404
3405 BEGIN
3406 -- This procedure either deletes from the delivered account,
3407 -- parameter of 'D', or from the live account, parameter of 'I'.
3408
3409 -- If the delivered details are being deleted the explicit deletes
3410 -- from all child tables are required, since the cascade constraint
3411 -- will not be delivered with these tables.
3412
3413 -- When deleting from the live account, the cascade delete can
3414 -- be relied upon.
3415
3416 IF target = 'D' THEN
3417
3418 delete from hr_s_function_context_usages a
3419 where exists
3420 (select null
3421 from hr_s_functions b
3422 where b.function_id = a.function_id
3423 and b.name = stu_rec.name
3424 and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
3425 );
3426
3427 delete from hr_s_function_parameters a
3428 where exists
3429 (select null
3430 from hr_s_functions b
3431 where b.function_id = a.function_id
3432 and b.name = stu_rec.name
3433 and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
3434 );
3435
3436
3437 delete from hr_s_functions
3438 where name = stu_rec.name
3439 and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
3440
3441 ELSE
3442
3443 -- Delete from live account using the cascade delete
3444
3445
3446 delete from ff_functions
3447 where name = stu_rec.name
3448 and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
3449
3450 END IF;
3451
3452 END remove;
3453
3454 PROCEDURE insert_delivered
3455 --------------------------
3456 IS
3457 -- Check if a delivered row is needed and insert into the
3458 -- live tables if it is
3459
3460 v_inst_update date; -- Holds update details of installed row
3461
3462 BEGIN
3463
3464
3465 BEGIN
3466
3467 -- Perform a check to see if the primary key has been created within
3468 -- a visible business group. Ie: the business group is for the same
3469 -- legislation as the delivered row, or the delivered row has a null
3470 -- legislation. If no rows are returned then the primary key has not
3471 -- already been created by a user.
3472
3473 select distinct null
3474 into l_null_return
3475 from ff_functions a
3476 where a.name = stu_rec.name
3477 and a.business_group_id is not null
3478 and exists (select null from per_business_groups b
3479 where b.business_group_id = a.business_group_id
3480 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
3481
3482 crt_exc('Row already created in a business group');
3483
3484 -- Indicate this row is not to be transferred
3485
3486 return;
3487
3488 EXCEPTION WHEN NO_DATA_FOUND THEN
3489
3490 null;
3491
3492 END;
3493
3494
3495 -- Now perform a check to see if this primary key has been installed
3496 -- with a legislation code that would make it visible at the same time
3497 -- as this row. Ie: if any legislation code is null within the set of
3498 -- returned rows, then the transfer may not go ahead. If no rows are
3499 -- returned then the delivered row is fine.
3500
3501 BEGIN
3502 select distinct null
3503 into l_null_return
3504 from ff_functions
3505 where name = stu_rec.name
3506 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
3507 and (
3508 legislation_code is null
3509 or stu_rec.c_leg_code is null
3510 );
3511
3512 crt_exc('Row already created for a visible legislation');
3513
3514 -- Indicates this row is not to be transferred
3515
3516 return;
3517
3518 EXCEPTION WHEN NO_DATA_FOUND THEN
3519
3520 null;
3521
3522 END;
3523
3524
3525 -- This procedure is only called in phase 2. All matching live
3526 -- functions will be be deleted and replaced with the delivered
3527 -- rows.
3528
3529 -- The routine check_parents validates foreign key references and
3530 -- ensures referential integrity. The routine checks to see if the
3531 -- parents of a given row have been transfered to the live tables.
3532
3533 -- This may only be called in phase two since in phase one all
3534 -- parent rows will remain in the delivery tables.
3535
3536 -- After the above checks only data that has been chanegd or is new
3537 -- will be left in the delivery tables.
3538
3539 -- The last step of this transfer, in phase 2, is to delete the now
3540 -- transfered row from the delivery tables.
3541
3542 -- Before the update/insert goes ahead, ensure all child rows
3543 -- are removed so the refrsh of child rows is simple.
3544
3545 remove('I');
3546
3547 FOR each_func IN distinct_function(stu_rec.name, stu_rec.c_leg_code) LOOP
3548
3549
3550 select ff_functions_s.nextval
3551 into l_new_surrogate_key
3552 from dual;
3553
3554
3555 BEGIN
3556 insert into ff_functions
3557 (function_id
3558 ,business_group_id
3559 ,legislation_code
3560 ,class
3561 ,name
3562 ,alias_name
3563 ,data_type
3564 ,definition
3565 ,last_update_date
3566 ,last_updated_by
3567 ,last_update_login
3568 ,created_by
3569 ,creation_date
3570 )
3571 values (l_new_surrogate_key
3572 ,null
3573 ,each_func.legislation_code
3574 ,each_func.class
3575 ,each_func.name
3576 ,each_func.alias_name
3577 ,each_func.data_type
3578 ,each_func.definition
3579 ,each_func.last_update_date
3580 ,each_func.last_updated_by
3581 ,each_func.last_update_login
3582 ,each_func.created_by
3583 ,each_func.creation_date
3584 );
3585
3586
3587 EXCEPTION WHEN OTHERS THEN
3588 hr_legislation.hrrunprc_trace_on;
3589 hr_utility.trace('ins ff_functions');
3590 hr_utility.trace('function name ' ||
3591 each_func.name);
3592 hr_utility.trace('function_id ' ||
3593 to_char(l_new_surrogate_key));
3594 hr_utility.trace(':lc: ' || ':' ||
3595 each_func.legislation_code || ':');
3596 hr_legislation.hrrunprc_trace_off;
3597 raise;
3598 END;
3599
3600 BEGIN
3601 insert into ff_function_parameters
3602 (function_id
3603 ,sequence_number
3604 ,class
3605 ,continuing_parameter
3606 ,data_type
3607 ,name
3608 ,optional
3609 )
3610 select l_new_surrogate_key
3611 , sequence_number
3612 , class
3613 , continuing_parameter
3614 , data_type
3615 , name
3616 , optional
3617 from hr_s_function_parameters
3618 where function_id = each_func.function_id;
3619 EXCEPTION WHEN OTHERS THEN
3620 hr_legislation.hrrunprc_trace_on;
3621 hr_utility.trace('ins ff_function_parameters');
3622 hr_utility.trace('function_id ' ||
3623 to_char(each_func.function_id));
3624 hr_utility.trace('function name ' ||
3625 each_func.name);
3626 hr_utility.trace(':lc: ' ||
3627 each_func.legislation_code || ':');
3628 hr_legislation.hrrunprc_trace_off;
3629 raise;
3630 END;
3631
3632 FOR child_usages IN usages(each_func.function_id) LOOP
3633
3634 BEGIN
3635
3636
3637 select null
3638 into l_null_return
3639 from ff_contexts
3640 where context_id = child_usages.context_id;
3641
3642 insert into ff_function_context_usages
3643 (function_id
3644 ,sequence_number
3645 ,context_id
3646 )
3647 values
3648 (l_new_surrogate_key
3649 ,child_usages.sequence_number
3650 ,child_usages.context_id
3651 );
3652
3653 EXCEPTION WHEN NO_DATA_FOUND THEN
3654
3655
3656 crt_exc('Context referenced by child usage is not present');
3657
3658 return;
3659
3660 WHEN OTHERS THEN
3661 hr_legislation.hrrunprc_trace_on;
3662 hr_utility.trace('ins ff_function_context_usages');
3663 hr_utility.trace('function_id ' ||
3664 to_char(l_new_surrogate_key));
3665 hr_utility.trace('function name ' ||
3666 each_func.name);
3667 hr_utility.trace(':lc: ' ||
3668 each_func.legislation_code || ':');
3669 hr_utility.trace('sequence_number ' ||
3670 to_char(child_usages.sequence_number));
3671 hr_utility.trace('context_id ' ||
3672 to_char(child_usages.context_id));
3673 hr_legislation.hrrunprc_trace_off;
3674 raise;
3675
3676 END;
3677
3678 END LOOP child_usages;
3679
3680 END LOOP each_func;
3681
3682
3683 remove('D');
3684
3685 END insert_delivered;
3686
3687 BEGIN
3688 -- This is the main loop to perform the installation logic. A cursor
3689 -- is opened to control the loop, and each row returned is placed
3690 -- into a record defined within the main procedure so each sub
3691 -- procedure has full access to all returrned columns. For each
3692 -- new row returned, a new savepoint is declared. If at any time
3693 -- the row is in error a rollback is performed to the savepoint
3694 -- and the next row is returned.
3695
3696 -- In phase 1 the only logic is to check if the function needs to be
3697 -- installed. If the function needs to be installed, it will be left
3698 -- in the delivery tables. If not then it will be deleted.
3699
3700 -- The surrogate id will be created/set in phase 2.
3701
3702 -- In phase 2 all installed functions of the name stu_rec.name will be
3703 -- deleted from the live account. All delivered functions/usages/parameters
3704 -- will be then inserted. At this point a new function id will be allocated.
3705
3706 FOR delivered IN stu LOOP
3707
3708 -- Uses main cursor stu to impilicity define a record
3709
3710
3711 savepoint new_function_name;
3712
3713 stu_rec := delivered;
3714
3715
3716 IF p_phase = 2 THEN
3717 insert_delivered;
3718 END IF;
3719
3720 END LOOP;
3721
3722 END install_functions;
3723
3724 --****************************************************************************
3725 -- INSTALLATION PROCEDURE FOR : FF_QP_REPORTS
3726 --****************************************************************************
3727
3728 PROCEDURE install_qpreports(p_phase IN number)
3729 ----------------------------------------------
3730 IS
3731 -- Install procedure to transfer startup QuickPaint reports into
3732 -- a live account.
3733
3734 l_null_return varchar2(1); -- For 'select null' statements
3735 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3736
3737 CURSOR stu -- Selects all rows from startup entity
3738 IS
3739
3740 select qp_report_id c_surrogate_key
3741 , formula_id
3742 , formula_type_id
3743 , qp_report_name c_true_key
3744 , business_group_id
3745 , legislation_code c_leg_code
3746 , qp_altered_formula
3747 , qp_description
3748 , qp_text
3749 , last_update_date
3750 , last_updated_by
3751 , last_update_login
3752 , created_by
3753 , creation_date
3754 , rowid
3755 from hr_s_qp_reports;
3756
3757 stu_rec stu%ROWTYPE;
3758
3759 PROCEDURE crt_exc(exception_type IN varchar2)
3760 ---------------------------------------------
3761 IS
3762 -- If an exception has been detected meaning that the delivered row may
3763 -- not be installed, then it must be reported
3764
3765 BEGIN
3766
3767 -- When the installation procedures encounter an error that cannot
3768 -- be handled, an exception is raised and all work is rolled back
3769 -- to the last savepoint. The installation process then continues
3770 -- with the next primary key to install. The same exception will
3771 -- not be raised more than once.
3772
3773
3774 rollback to new_qp_report_name;
3775
3776 hr_legislation.insert_hr_stu_exceptions('ff_qp_reports'
3777 , stu_rec.c_surrogate_key
3778 , exception_type
3779 , stu_rec.c_true_key);
3780
3781
3782 END crt_exc;
3783
3784 PROCEDURE check_next_sequence
3785 -----------------------------
3786 IS
3787
3788 v_sequence_number number(9);
3789 v_min_delivered number(9);
3790 v_max_delivered number(9);
3791
3792 -- Surrogate id conflicts may arise from two scenario's:
3793 -- 1. Where the newly select sequence value conflicts with values
3794 -- in the STU tables.
3795 -- 2. Where selected surrogate keys, from the installed tables,
3796 -- conflict with other rows in the STU tables.
3797 --
3798 -- Both of the above scenario's are tested for.
3799 -- The first is a simple match, where if a value is detected in the
3800 -- STU tables and the installed tables then a conflict is detected. In
3801 -- This instance all STU surrogate keys, for this table, are updated.
3802 -- The second is tested for using the sequences.
3803 -- If the next value from the live sequence is within the range of
3804 -- delivered surrogate id's then the live sequence must be incremented.
3805 -- If no action is taken, then duplicates may be introduced into the
3806 -- delivered tables, and child rows may be totally invalidated.
3807
3808 BEGIN
3809
3810
3811 BEGIN --check that the installed id's will not conflict
3812 --with the delivered values
3813
3814
3815 select distinct null
3816 into l_null_return
3817 from ff_qp_reports a
3818 where exists
3819 (select null
3820 from hr_s_qp_reports b
3821 where a.qp_report_id = b.qp_report_id
3822 );
3823
3824 --conflict may exist
3825 --update all qp_report_id's to remove conflict
3826
3827 update hr_s_qp_reports
3828 set qp_report_id = qp_report_id - 50000000;
3829
3830 update hr_s_application_ownerships
3831 set key_value = key_value - 50000000
3832 where key_name = 'QP_REPORT_ID';
3833
3834 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3835
3836 END; --check of qp_report_id
3837
3838
3839 select min(qp_report_id) - (count(*) *3)
3840 , max(qp_report_id) + (count(*) *3)
3841 into v_min_delivered
3842 , v_max_delivered
3843 from hr_s_qp_reports;
3844
3845 select ff_qp_reports_s.nextval
3846 into v_sequence_number
3847 from dual;
3848
3849 IF v_sequence_number
3850 BETWEEN v_min_delivered AND v_max_delivered THEN
3851
3852 hr_legislation.munge_sequence('FF_QP_REPORTS_S',
3853 v_sequence_number,
3854 v_max_delivered);
3855
3856 END IF;
3857
3858 END check_next_sequence;
3859
3860 PROCEDURE update_uid
3861 --------------------
3862 IS
3863 -- Update surrogate UID and all occurrences in child rows
3864
3865 BEGIN
3866
3867
3868 BEGIN
3869
3870 select distinct qp_report_id
3871 into l_new_surrogate_key
3872 from ff_qp_reports
3873 where qp_report_name = stu_rec.c_true_key
3874 and business_group_id is null
3875 and ( (legislation_code is null and stu_rec.c_leg_code is null)
3876 or (legislation_code = stu_rec.c_leg_code) );
3877
3878 EXCEPTION WHEN NO_DATA_FOUND THEN
3879
3880 select ff_qp_reports_s.nextval
3881 into l_new_surrogate_key
3882 from dual;
3883
3884 END;
3885
3886 -- Update all child entities
3887
3888 update hr_s_qp_reports
3889 set qp_report_id = l_new_surrogate_key
3890 where qp_report_id = stu_rec.c_surrogate_key;
3891
3892 update hr_s_application_ownerships
3893 set key_value = to_char(l_new_surrogate_key)
3894 where key_value = to_char(stu_rec.c_surrogate_key)
3895 and key_name = 'QP_REPORT_ID';
3896
3897 END update_uid;
3898
3899 PROCEDURE remove
3900 ----------------
3901 IS
3902 -- Remove a row from either the startup tables or the installed tables
3903
3904 BEGIN
3905
3906 delete from hr_s_qp_reports
3907 where rowid = stu_rec.rowid;
3908
3909 END remove;
3910
3911 FUNCTION valid_ownership RETURN BOOLEAN
3912 ---------------------------------------
3913 IS
3914 -- Test ownership of this current row
3915
3916 -- This routine only operates in phase 1. Rows are present in the
3917 -- table hr_application_ownerships in the delivery account, which
3918 -- dictate which products a piece of data is used for. If the query
3919 -- returns a row then this data is required, and the function will
3920 -- return true. If no rows are returned and an exception is raised,
3921 -- then this row is not required and may be deleted from the delivery
3922 -- tables.
3923
3924 -- If legislation code and subgroup code are included on the delivery
3925 -- tables, a check must be made to determine if the data is defined for
3926 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
3927 -- installation.
3928
3929 -- A return code of TRUE indicates that thhe row is required.
3930
3931 -- The exception is raised within this procedure if no rows are returned
3932 -- in this select statement. If no rows are returned then one of the
3933 -- following is true:
3934 -- 1. No ownership parameters are defined.
3935 -- 2. The products, for which owning parameters are defined, are not
3936 -- installed with as status of 'I'.
3937 -- 3. The data is defined for a legislation subgroup that is not active.
3938
3939 BEGIN
3940
3941
3942 IF p_phase <> 1 THEN -- Only perform in phase 1
3943 return TRUE;
3944 END IF;
3945
3946
3947 -- If exception raised below then this row is not needed
3948 -- get rid of subgrp table, not even using it!
3949 select null
3950 into l_null_return
3951 from dual
3952 where exists
3953 (select null
3954 from hr_s_application_ownerships a
3955 , fnd_product_installations b
3956 , fnd_application c
3957 where a.key_name = 'QP_REPORT_ID'
3958 and a.key_value = stu_rec.c_surrogate_key
3959 and a.product_name = c.application_short_name
3960 and c.application_id = b.application_id
3961 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3962 or
3963 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
3964
3965 -- Indicates row is required
3966
3967 return TRUE;
3968
3969 EXCEPTION WHEN NO_DATA_FOUND THEN
3970
3971 -- Row not needed for any installed product
3972
3973
3974 remove;
3975
3976 -- Indicate row not needed
3977
3978 return FALSE;
3979
3983 -------------------------------------
3980 END valid_ownership;
3981
3982 FUNCTION check_parents RETURN BOOLEAN
3984 IS
3985 -- Check if parent data is correct
3986
3987 -- This procedure is only called in phase 2. The logic to check if
3988 -- a given parental foriegn key exists is split into two parts for
3989 -- every foriegn key. The first select from the delivery tables.
3990
3991 -- If a row is founnd then the installation of the parent must have
3992 -- failed, and this installation must not go ahead. If no data is
3993 -- found, ie: an exception is raised, the installation is valid.
3994
3995 -- The second check looks for a row in the live tables. If no rows
3996 -- are returned then this installation is invalid, since this means
3997 -- that the parent referenced by this row is not present in the
3998 -- live tables.
3999
4000 -- The distinct is used in case the parent is date effective and many rows
4001 -- may be returned by the same parent id.
4002
4003 BEGIN
4004
4005 -- Start parent checking against formula types
4006
4007
4008 BEGIN
4009
4010 -- Checking the delivery account
4011
4012 select distinct null
4013 into l_null_return
4014 from hr_s_formula_types
4015 where formula_type_id = stu_rec.formula_type_id;
4016
4017 crt_exc('Parent formula type remains in delivery tables');
4018
4019 -- Parent row still in startup account
4020
4021 return FALSE;
4022
4023 EXCEPTION WHEN NO_DATA_FOUND THEN
4024
4025 null;
4026
4027 END;
4028
4029
4030 BEGIN
4031
4032 -- Checking the installed account
4033
4034 select null
4035 into l_null_return
4036 from ff_formula_types
4037 where formula_type_id = stu_rec.formula_type_id;
4038
4039 -- Drop down to second parent check
4040
4041 EXCEPTION WHEN NO_DATA_FOUND THEN
4042
4043
4044 crt_exc('Parent formula type not installed');
4045
4046 return FALSE;
4047
4048 END;
4049
4050 -- Start parent checking against formulas
4051
4052
4053 BEGIN
4054
4055 -- Checking the delivery account
4056
4057 select distinct null
4058 into l_null_return
4059 from hr_s_formulas_f
4060 where formula_id = stu_rec.formula_id;
4061
4062 crt_exc('Parent formula remains in delivery tables');
4063
4064 -- Parent row still in startup account
4065
4066 return FALSE;
4067
4068 EXCEPTION WHEN NO_DATA_FOUND THEN
4069
4070 null;
4071
4072 END;
4073
4074
4075 BEGIN
4076
4077 -- Checking the installed account
4078
4079 select distinct null
4080 into l_null_return
4081 from ff_formulas_f
4082 where formula_id = stu_rec.formula_id;
4083
4084 return TRUE;
4085
4086 EXCEPTION WHEN NO_DATA_FOUND THEN
4087
4088
4089 crt_exc('Parent formula not installed');
4090
4091 return FALSE;
4092
4093 END;
4094
4095 END check_parents;
4096
4097 PROCEDURE transfer_row
4098 ----------------------
4099 IS
4100 -- Check if a delivered row is needed and insert into the
4101 -- live tables if it is
4102
4103 v_inst_update date; -- Holds update details of installed row
4104
4105 BEGIN
4106
4107
4108 BEGIN
4109
4110 -- Perform a check to see if the primary key has been created
4111 -- within a visible business group. Ie: the business group
4112 -- is for the same legislation as the delivered row, or the
4113 -- delivered row has a null legislation. If no rows are
4114 -- returned then the primary key has not already been
4115 -- created by a user.
4116
4117 select distinct null
4118 into l_null_return
4119 from ff_qp_reports a
4120 where a.qp_report_name = stu_rec.c_true_key
4121 and a.business_group_id is not null
4122 and exists (select null from per_business_groups b
4123 where b.business_group_id = a.business_group_id
4124 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
4125
4126 crt_exc('Row already created in a business group');
4127
4128 -- Indicate this row is not to be transferred
4129
4130 return;
4131
4132 EXCEPTION WHEN NO_DATA_FOUND THEN
4133
4134 null;
4135
4136 END;
4137
4138
4139 -- Now perform a check to see if this primary key has been installed
4140 -- with a legislation code that would make it visible at the same time
4141 -- as this row. Ie: if any legislation code is null within the set of
4142 -- returned rows, then the transfer may not go ahead. If no rows are
4143 -- returned then the delivered row is fine.
4144
4145 BEGIN
4146
4147 select distinct null
4148 into l_null_return
4149 from ff_qp_reports
4150 where qp_report_name = stu_rec.c_true_key
4151 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
4152 and (legislation_code is null
4153 or stu_rec.c_leg_code is null );
4154
4155 crt_exc('Row already created for a visible legislation');
4156
4157 -- Indicate this row is not to be transferred
4158
4159 return;
4160
4161 EXCEPTION WHEN NO_DATA_FOUND THEN
4162
4163 null;
4164
4165 END;
4166
4167
4168 -- When the procedure is called in phase 1, there is no need to
4169 -- actually perform the transfer from the delivery tables into the
4170 -- live. Hence if phase = 1 control is returned to the calling
4171 -- procedure and the next row is returned.
4172
4173 -- If the procedure is called in phase 2, then the live row is updated
4174 -- with the values on the delivered row.
4175
4176 -- The routine check_parents validates foreign key references and
4177 -- ensures referential integrity. The routine checks to see if the
4178 -- parents of a given row have been transfered to the live tables.
4179
4180 -- This may only be called in phase two since in phase one all
4181 -- parent rows will remain in the delivery tables.
4182
4183 -- After the above checks only data that has been chanegd or is new
4184 -- will be left in the delivery tables. At this stage if the row is
4185 -- already present then it must be updated to ensure referential
4186 -- integrity. Therefore an update will be performed and if SQL%FOUND
4187 -- is FALSE an insert will be performed.
4188
4189 -- The last step of the transfer, in phase 2, is to delete the now
4190 -- transfered row from the delivery tables.
4191
4192 IF p_phase = 1 THEN
4193 return;
4194 END IF;
4195
4196 IF NOT check_parents THEN
4197 -- Fails because parents exist
4198 return;
4199 END IF;
4200
4201
4202 update ff_qp_reports
4203 set formula_id = stu_rec.formula_id
4204 , formula_type_id = stu_rec.formula_type_id
4205 , qp_report_name = to_char(stu_rec.c_surrogate_key)
4206 , business_group_id = null
4207 , legislation_code = stu_rec.c_leg_code
4208 , qp_altered_formula = stu_rec.qp_altered_formula
4209 , qp_description = stu_rec.qp_description
4210 , qp_text = stu_rec.qp_text
4211 , last_update_date = stu_rec.last_update_date
4212 , last_updated_by = stu_rec.last_updated_by
4213 , last_update_login = stu_rec.last_update_login
4214 , created_by = stu_rec.created_by
4215 , creation_date = stu_rec.creation_date
4216 where qp_report_id = stu_rec.c_surrogate_key;
4217
4218 IF NOT SQL%FOUND THEN
4219
4220
4221 insert into ff_qp_reports
4222 (qp_report_id
4223 ,formula_id
4224 ,formula_type_id
4225 ,qp_report_name
4226 ,business_group_id
4227 ,legislation_code
4228 ,qp_altered_formula
4229 ,qp_description
4230 ,qp_text
4231 ,last_update_date
4232 ,last_updated_by
4233 ,last_update_login
4234 ,created_by
4235 ,creation_date
4236 )
4237 values
4238 (stu_rec.c_surrogate_key
4239 ,stu_rec.formula_id
4240 ,stu_rec.formula_type_id
4241 ,stu_rec.c_true_key
4242 ,null
4243 ,stu_rec.c_leg_code
4244 ,stu_rec.qp_altered_formula
4245 ,stu_rec.qp_description
4246 ,stu_rec.qp_text
4247 ,stu_rec.last_update_date
4248 ,stu_rec.last_updated_by
4249 ,stu_rec.last_update_login
4250 ,stu_rec.created_bY
4251 ,stu_rec.creation_date
4252 );
4253
4254 END IF;
4255
4256
4257 remove;
4258
4259 END transfer_row;
4260
4261 BEGIN
4262 -- This is the main loop to perform the installation logic. A cursor
4263 -- is opened to control the loop, and each row returned is placed
4264 -- into a record defined within the main procedure so each sub
4265 -- procedure has full access to all returrned columns. For each
4266 -- new row returned, a new savepoint is declared. If at any time
4267 -- the row is in error a rollback iss performed to the savepoint
4268 -- and the next row is returned. Ownership details are checked and
4269 -- if the row is required then the surrogate id is updated and the
4270 -- main transfer logic is called.
4271
4272 IF p_phase = 1 THEN
4273 check_next_sequence;
4274 END IF;
4275
4276 FOR delivered IN stu LOOP
4277
4278 -- Uses main cursor stu to impilicity define a record
4279
4280
4281 savepoint new_qp_report_name;
4282
4283 stu_rec := delivered;
4284
4285 IF p_phase = 2 THEN
4286 l_new_surrogate_key := stu_rec.c_surrogate_key;
4287 END IF;
4288
4289 IF valid_ownership THEN
4290
4291 -- Test the row ownerships for the current row
4292
4293
4294 IF p_phase = 1 THEN
4295 update_uid;
4296 END IF;
4297
4298 transfer_row;
4299
4300 END IF;
4301
4302 END LOOP;
4303
4304 END install_qpreports;
4305
4306 --****************************************************************************
4307 -- INSTALLATION PROCEDURE FOR : FF_GLOBALS_S
4308 --****************************************************************************
4309
4310 PROCEDURE install_globals(p_phase IN NUMBER)
4311 --------------------------------------------
4312 IS
4313 -- Install procedure to transfer startup delivered globals into a
4314 -- live account, and remove the then delivered rows from the delivery
4315 -- account.
4316
4317 -- This procedure is called in two phase. Only in the second phase are
4318 -- details transferred into live tables. The parameter p_phase holds
4319 -- the phase number.
4320
4321 row_in_error exception;
4322 l_current_proc varchar2(80) := 'hr_legislation.install_globals';
4323 l_new_global_id number(15);
4324 l_null_return varchar2(1);
4325 status varchar2(10);
4326
4327 CURSOR c_distinct
4328 IS
4329 -- Select statement used for the main loop. Each row return is used
4330 -- as the commit unit, since each true primary key may have many date
4331 -- effective rows for it.
4332
4333 -- The selected primary key is then passed into the second driving
4334 -- cursor statement as a parameter, and all date effective rows for
4335 -- this primary key are then selected.
4336
4337 select max(effective_end_date) c_end
4338 , global_id c_surrogate_key
4339 , global_name c_true_key
4340 , legislation_code
4341 from hr_s_globals_f
4342 group by global_id
4343 , global_name
4344 , legislation_code;
4345
4346 CURSOR c_each_row(pc_global_id varchar2)
4347 IS
4348 -- Selects all date effective rows for the current true primary key
4349 -- The primary key has already been selected using the above cursor.
4350 -- This cursor accepts the primary key as a parameter and selects all
4351 -- date effective rows for it.
4352
4353 select *
4354 from hr_s_globals_f
4355 where global_id = pc_global_id;
4356
4357 -- These records are defined here so all sub procedures may use the
4358 -- values selected. This saves the need for all sub procedures to have
4359 -- a myriad of parameters passed. The cursors are controlled in FOR
4360 -- cursor LOOPs. When a row is returned the whole record is copied into
4361 -- these record definitions.
4362
4363 CURSOR c_global_ad(p_global_id number)
4364 IS
4365 -- This cursor is used when deleting rows from ff_globals_f.
4366 -- FF_GLOBAL_F has an after delete trigger that removes user_entities
4367 -- that have a creator_id = the global_id of the row being removed.
4368 -- When deleting these UE, the BRD UE trigger potentially deletes
4369 -- database items. This trigger invalidates all formulae that can be
4370 -- affected by these dbi removals.
4371 select distinct ffu.formula_id fid
4372 from ff_fdi_usages_f ffu
4373 where ffu.item_name in (select fdbi.user_name
4374 from ff_database_items fdbi,
4375 ff_user_entities ffue
4376 where fdbi.user_entity_id = ffue.user_entity_id
4377 and ffue.creator_id = p_global_id
4378 and ffue.creator_type = 'S');
4379
4380 r_distinct c_distinct%ROWTYPE;
4381 r_each_row c_each_row%ROWTYPE;
4382
4383 PROCEDURE remove (v_id IN number)
4384 ---------------------------------
4385 IS
4386 -- Subprogram to delete a row from the delivery tables, and all child
4390
4387 -- application ownership rows
4388
4389 BEGIN
4391
4392 delete from hr_s_globals_f
4393 where global_id = v_id;
4394
4395 END remove;
4396
4397 PROCEDURE check_next_sequence
4398 -----------------------------
4399 IS
4400
4401 v_sequence_number number(9);
4402 v_min_delivered number(9);
4403 v_max_delivered number(9);
4404
4405 -- Surrogate id conflicts may arise from two scenario's:
4406 -- 1. Where the newly select sequence value conflicts with values
4407 -- in the STU tables.
4408 -- 2. Where selected surrogate keys, from the installed tables,
4409 -- conflict with other rows in the STU tables.
4410 --
4411 -- Both of the above scenario's are tested for.
4412 -- The first is a simple match, where if a value is detected in the
4413 -- STU tables and the installed tables then a conflict is detected. In
4414 -- This instance all STU surrogate keys, for this table, are updated.
4415 -- The second is tested for using the sequences.
4416 -- If the next value from the live sequence is within the range of
4417 -- delivered surrogate id's then the live sequence must be incremented.
4418 -- If no action is taken, then duplicates may be introduced into the
4419 -- delivered tables, and child rows may be totally invalidated.
4420
4421 BEGIN
4422
4423
4424 BEGIN --check that the installed id's will not conflict
4425 --with the delivered values
4426
4427
4428 select distinct null
4429 into l_null_return
4430 from ff_globals_f a
4431 where exists
4432 (select null
4433 from hr_s_globals_f b
4434 where a.global_id = b.global_id
4435 );
4436
4437 --conflict may exist
4438 --update all global_id's to remove conflict
4439
4440 update /*+NO_INDEX*/ hr_s_globals_f
4441 set global_id = global_id - 50000000;
4442
4443 update hr_s_application_ownerships
4444 set key_value = key_value - 50000000
4445 where key_name = 'GLOBAL_ID';
4446
4447 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4448
4449 END; --check of global_id
4450
4451
4452 select min(global_id) - (count(*) *3)
4453 , max(global_id) + (count(*) *3)
4454 into v_min_delivered
4455 , v_max_delivered
4456 from hr_s_globals_f;
4457
4458 select ff_globals_s.nextval
4459 into v_sequence_number
4460 from dual;
4461
4462 IF v_sequence_number
4463 BETWEEN v_min_delivered AND v_max_delivered THEN
4464
4465 hr_legislation.munge_sequence('FF_GLOBALS_S',
4466 v_sequence_number,
4467 v_max_delivered);
4468 END IF;
4469
4470 END check_next_sequence;
4471
4472 PROCEDURE update_uid
4473 --------------------
4474 IS
4475 -- Subprogram to update surrogate UID and all occurrences in child rows
4476
4477 BEGIN
4478
4479 -- See if this primary key is already installed. If so then the sorrogate
4480 -- key of the delivered row must be updated to the value in the installed
4481 -- tables. If the row is not already present then select the next value
4482 -- from the sequence. In either case all rows for this primary key must
4483 -- be updated, as must all child references to the old surrogate uid.
4484
4485
4486 BEGIN
4487
4488 select distinct global_id
4489 into l_new_global_id
4490 from ff_globals_f
4491 where global_name = r_distinct.c_true_key
4492 and business_Group_id is null
4493 and ((legislation_code is NULL and r_distinct.legislation_code is NULL)
4494 or (r_distinct.legislation_code=legislation_code));
4495
4496
4497 EXCEPTION WHEN NO_DATA_FOUND THEN
4498
4499
4500 select ff_globals_s.nextval
4501 into l_new_global_id
4502 from dual;
4503
4504 WHEN TOO_MANY_ROWS THEN
4505 hr_legislation.hrrunprc_trace_on;
4506 hr_utility.trace('sel ff_globals_f TMR');
4507 hr_utility.trace('global_name ' ||
4508 r_distinct.c_true_key);
4509 hr_utility.trace(':lc: ' || ':' ||
4510 r_distinct.legislation_code || ':');
4511 hr_legislation.hrrunprc_trace_off;
4512 raise;
4513 END;
4514
4515 update hr_s_globals_f
4516 set global_id = l_new_global_id
4517 where global_id = r_distinct.c_surrogate_key;
4518
4519 update hr_s_application_ownerships
4520 set key_value = to_char(l_new_global_id)
4521 where key_value = to_char(r_distinct.c_surrogate_key)
4522 and key_name = 'GLOBAL_ID';
4523
4524 END update_uid;
4525
4526 PROCEDURE crt_exc(exception_type IN varchar2)
4527 ---------------------------------------------
4528 IS
4529 -- If an exception has been detected meaning that the delivered row may
4530 -- not be installed, then it must be reported
4531
4532 BEGIN
4533
4534 -- When the installation procedures encounter an error that cannot
4535 -- be handled, an exception is raised and all work is rolled back
4536 -- to the last savepoint. The installation process then continues
4537 -- with the next primary key to install. The same exception will
4538 -- not be raised more than once.
4539
4540 rollback to new_global_name;
4541
4542 hr_legislation.insert_hr_stu_exceptions('ff_globals_f'
4543 , r_distinct.c_surrogate_key
4544 , exception_type
4545 , r_distinct.c_true_key);
4546
4547
4548 END crt_exc;
4549
4550 -- ----------------------------------
4551 -- ----------------------------------
4552 FUNCTION valid_ownership RETURN BOOLEAN
4553 ---------------------------------------
4554 IS
4555 -- Test ownership of this current row
4556
4557 -- This function is split into three distinct parts. The first
4558 -- checks to see if a row exists with the same primary key, for a
4559 -- business group that would have access to the delivered row. The
4560 -- second checks details for data created in other legislations,
4561 -- in case data is either created with a null legislation or the
4562 -- delivered row has a null legislation. The last check examines
4563 -- if this data is actually required for a given install by examining
4564 -- the product installation table, and the ownership details for
4565 -- this row.
4566
4567 -- A return code of TRUE indicates that the row is required.
4568 cursor get_ff_globals is
4569 select distinct null
4570 from ff_globals_f a
4571 where a.global_name = r_distinct.c_true_key
4572 and a.business_group_id is not null
4573 and exists (select null from per_business_groups b
4574 where b.business_group_id = a.business_group_id
4575 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
4576
4577 --
4578 cursor c_valid_ownership is
4579 select distinct null
4580 from ff_globals_f
4581 where global_name = r_distinct.c_true_key
4582 and nvl(legislation_code,'X')<>nvl(r_distinct.legislation_code,'X')
4583 and (legislation_code is null
4584 or r_distinct.legislation_code is null );
4585 --
4586 BEGIN
4587 BEGIN
4588 -- Perform a check to see if the primary key has been
4589 -- created within a visible business group. Ie: the
4590 -- business group is for the same legislation as the
4591 -- delivered row, or the delivered row has a null
4592 -- legislation. If no rows are returned then the primary
4593 -- key has not already been created by a user.
4594
4595 open get_ff_globals;
4596 fetch get_ff_globals into l_null_return;
4597 IF get_ff_globals%NOTFOUND OR get_ff_globals%NOTFOUND IS NULL THEN
4598 RAISE NO_DATA_FOUND;
4599 END IF;
4600 close get_ff_globals;
4601 --
4602 crt_exc('Row already created in a business group');
4603 -- Indicate this row is not to be transferred
4604 return FALSE;
4605
4606 EXCEPTION WHEN NO_DATA_FOUND THEN
4607 null;
4608 --
4609 END;
4610
4611 -- Now perform a check to see if this primary key has been installed
4612 -- with a legislation code that would make it visible at the same time
4613 -- as this row. Ie: if any legislation code is null within the set of
4614 -- returned rows, then the transfer may not go ahead. If no rows are
4615 -- returned then the delivered row is fine.
4616
4617 BEGIN
4618 --
4619 open c_valid_ownership;
4620 fetch c_valid_ownership into l_null_return;
4621 IF c_valid_ownership%NOTFOUND OR c_valid_ownership%NOTFOUND IS NULL THEN
4622 RAISE NO_DATA_FOUND;
4623 END IF;
4624 close c_valid_ownership;
4625 --
4626 crt_exc('Row already created for a visible legislation');
4627 return FALSE; --indicates this row is not to be transferred
4628
4629 EXCEPTION WHEN NO_DATA_FOUND THEN
4630 null;
4631 END;
4632
4633 -- The last check examines the product installation table, and the
4634 -- ownership details for the delivered row. By examining these
4635 -- tables the row is either deleted or not. If the delivered row
4636 -- is 'stamped' with a legislation subgroup, then a chweck must be
4637 -- made to see if that subgroup is active or not. This check only
4638 -- needs to be performed in phase 1, since once this decision is
4639 -- made, it is pontless to perform this logic again.
4640 -- in this select statement. If no rows are returned then one of the
4641 -- following is true:
4642 -- 1. No ownership parameters are defined.
4643 -- 2. The products, for which owning parameters are defined, are not
4644 -- installed with as status of 'I'.
4645 -- 3. The data is defined for a legislation subgroup that is not active.
4646
4647 IF p_phase <> 1 THEN return TRUE; END IF;
4648
4649
4653 where exists (select null from hr_s_application_ownerships a
4650 select null
4651 into l_null_return
4652 from dual
4654 , fnd_product_installations b
4655 , fnd_application c
4656 where a.key_name = 'GLOBAL_ID'
4657 and a.key_value = r_distinct.c_surrogate_key
4658 and a.product_name = c.application_short_name
4659 and c.application_id = b.application_id
4660 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4661 or
4662 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4663
4664
4665 -- Indicate row is required
4666
4667 return TRUE;
4668
4669 EXCEPTION WHEN NO_DATA_FOUND THEN
4670
4671 -- Row not needed for any installed product
4672
4673
4674 remove(r_distinct.c_surrogate_key);
4675
4676 -- Indicate row not needed
4677
4678 return FALSE;
4679
4680 END valid_ownership;
4681
4682 BEGIN
4683 -- Two loops are used here. The main loop which select distinct primary
4684 -- key rows and an inner loop which selects all date effective rows for the
4685 -- primary key. The inner loop is only required in phase two, since only
4686 -- in phase 2 are rows actually transferred. The logic reads as follows:
4687
4688 -- - Only deal with rows which have correct ownership details and will
4689 -- not cause integrity problems (valid_ownership).
4690
4691 -- - In Phase 1:
4692 -- - Delete delivery rows where the installed rows are identicle.
4693 -- - The UNION satement compares delivery rows to installed rows.
4694 -- If the sub query returns any rows, then the delivered
4695 -- tables and the installed tables are different.
4696 -- - In Phase 2:
4697 -- - Delete from the installed tables using the surrogate id.
4698 -- - If an installed row is to be replaced, the values of
4702 -- must be installed within the same commit unit. If any
4699 -- the surrogate keys will be identicle at this stage.
4700 -- - Data will then be deleted from the delivery tables.
4701 -- - Call the installation procedure for any child tables, that
4703 -- errors occur then rollback to the last declared savepoint.
4704 -- - Check that all integrity rules are still obeyed at the end
4705 -- of the installation (validity_checks).
4706
4707 -- An exception is used with this procedure 'row_in_error' in case an error
4708 -- is encountered from calling any function. If this is raised, then an
4709 -- exception is entered into the control tables (crt_exc();) and a rollback
4710 -- is performed.
4711
4712 -- disable dynamic cont calc trigger
4713 -- dis_cont_calc_trigger;
4714
4715 IF p_phase = 1 THEN
4716 check_next_sequence;
4717 END IF;
4718
4719 FOR primary_keys IN c_distinct LOOP
4720
4721 r_distinct := primary_keys;
4722
4723
4724 savepoint new_global_name;
4725
4726 BEGIN
4727
4728 IF valid_ownership THEN
4729
4730 -- This row is wanted
4731
4732
4733 IF p_phase = 1 THEN
4734
4735
4736 -- Get new surrogate id and update child references
4737
4738 update_uid;
4739
4740 delete from hr_s_globals_f
4741 where global_id = l_new_global_id
4742 and not exists
4743 ((select
4744 EFFECTIVE_START_DATE,
4745 EFFECTIVE_END_DATE,
4746 BUSINESS_GROUP_ID,
4747 LEGISLATION_CODE,
4748 DATA_TYPE,
4749 GLOBAL_NAME,
4750 GLOBAL_DESCRIPTION,
4751 GLOBAL_VALUE
4752 from hr_s_globals_f
4753 where global_id = l_new_global_id
4754 MINUS
4755 select
4756 EFFECTIVE_START_DATE,
4757 EFFECTIVE_END_DATE,
4758 BUSINESS_GROUP_ID,
4759 LEGISLATION_CODE,
4760 DATA_TYPE,
4761 GLOBAL_NAME,
4762 GLOBAL_DESCRIPTION,
4763 GLOBAL_VALUE
4764 from ff_globals_f
4765 where global_id = l_new_global_id
4766 )
4767 UNION
4768 (select
4769 EFFECTIVE_START_DATE,
4770 EFFECTIVE_END_DATE,
4771 BUSINESS_GROUP_ID,
4772 LEGISLATION_CODE,
4773 DATA_TYPE,
4774 GLOBAL_NAME,
4775 GLOBAL_DESCRIPTION,
4776 GLOBAL_VALUE
4777 from ff_globals_f
4778 where global_id = l_new_global_id
4779 MINUS
4780 select
4781 EFFECTIVE_START_DATE,
4782 EFFECTIVE_END_DATE,
4783 BUSINESS_GROUP_ID,
4784 LEGISLATION_CODE,
4785 DATA_TYPE,
4786 GLOBAL_NAME,
4787 GLOBAL_DESCRIPTION,
4788 GLOBAL_VALUE
4789 from hr_s_globals_f
4790 where global_id = l_new_global_id
4791 ))
4792 and exists (select distinct null
4793 from ff_user_entities u,
4794 ff_database_items d,
4795 ff_route_parameters rp,
4796 ff_route_parameter_values rpv
4797 where u.user_entity_name = global_name || '_GLOBAL_UE'
4798 and u.user_entity_id = rpv.user_entity_id
4799 and d.user_entity_id = u.user_entity_id
4800 and rpv.route_parameter_id = rp.route_parameter_id
4801 and rpv.value = to_char(l_new_global_id));
4802
4803 ELSE
4804
4805 -- Phase = 2
4806
4807
4808 for r_global in c_global_ad(r_distinct.c_surrogate_key)
4809 loop
4810 delete ff_fdi_usages_f where formula_id = r_global.fid;
4811 delete ff_compiled_info_f where formula_id = r_global.fid;
4812 end loop;
4813
4814 -- Delete ff_route_parameter_values
4815 -- associated with this global bug 3744555
4816 -- and let them get recreated by the global triggers
4817 delete ff_route_parameter_values
4818 where user_entity_id = (select user_entity_id
4819 from ff_user_entities
4820 where user_entity_name = r_distinct.c_true_key || '_GLOBAL_UE');
4821
4822 delete from ff_globals_f
4823 where global_id = r_distinct.c_surrogate_key;
4824
4825 FOR each_row IN c_each_row(r_distinct.c_surrogate_key)
4826 LOOP
4827
4828 r_each_row := each_row;
4829
4830
4831 BEGIN
4832 insert into ff_globals_f
4833 (GLOBAL_ID
4834 ,EFFECTIVE_START_DATE
4835 ,EFFECTIVE_END_DATE
4836 ,BUSINESS_GROUP_ID
4837 ,LEGISLATION_CODE
4838 ,DATA_TYPE
4839 ,GLOBAL_NAME
4840 ,GLOBAL_DESCRIPTION
4841 ,GLOBAL_VALUE
4842 ,LAST_UPDATE_DATE
4843 ,LAST_UPDATED_BY
4844 ,LAST_UPDATE_LOGIN
4845 ,CREATED_BY
4846 ,CREATION_DATE)
4847 values
4848 (r_each_row.GLOBAL_ID
4849 ,r_each_row.EFFECTIVE_START_DATE
4850 ,r_each_row.EFFECTIVE_END_DATE
4851 ,r_each_row.BUSINESS_GROUP_ID
4852 ,r_each_row.LEGISLATION_CODE
4853 ,r_each_row.DATA_TYPE
4854 ,r_each_row.GLOBAL_NAME
4855 ,r_each_row.GLOBAL_DESCRIPTION
4856 ,r_each_row.GLOBAL_VALUE
4857 ,r_each_row.LAST_UPDATE_DATE
4858 ,r_each_row.LAST_UPDATED_BY
4859 ,r_each_row.LAST_UPDATE_LOGIN
4860 ,r_each_row.CREATED_BY
4861 ,r_each_row.CREATION_DATE);
4862
4863 EXCEPTION WHEN OTHERS THEN
4864 hr_legislation.hrrunprc_trace_on;
4865 hr_utility.trace('ins ff_globals_f');
4866 hr_utility.trace('GLOBAL_NAME ' ||
4867 r_each_row.GLOBAL_NAME);
4868 hr_utility.trace('GLOBAL_ID ' ||
4869 to_char(r_each_row.GLOBAL_ID));
4870 hr_utility.trace('GLOBAL_VALUE ' ||
4871 r_each_row.GLOBAL_VALUE);
4872 hr_utility.trace(':lc: ' || ':' ||
4873 r_each_row.legislation_code || ':');
4874 hr_legislation.hrrunprc_trace_off;
4875 raise;
4876 END;
4877 END LOOP each_row;
4878
4879 remove(r_distinct.c_surrogate_key);
4880
4881 END IF;
4882
4883 END IF;
4884
4885 EXCEPTION WHEN row_in_error THEN
4886 rollback to new_global_name;
4887 END;
4888
4889 END LOOP primary_keys;
4890
4891 -- ena_cont_calc_trigger;
4892
4893 END install_globals;
4894
4895 --****************************************************************************
4896 -- INSTALLATION PROCEDURE FOR ALL FF DELIVERY
4897 --****************************************************************************
4898
4899 PROCEDURE install (p_phase number)
4900 ----------------------------------
4901 IS
4902 -- Driver procedure to execute all formula installation procedures.
4903 core_selected NUMBER;
4904
4905 BEGIN
4906 hr_legislation.hrrunprc_trace_on;
4907 hr_utility.trace('start ff_data_dict.install: ' || to_char(p_phase));
4908 hr_legislation.hrrunprc_trace_off;
4909
4910 IF p_phase <> 1 and p_phase <>2 THEN
4911 return;
4912 END IF;
4913
4914 IF p_phase = 2 THEN
4915
4916 select count(*)
4917 into core_selected
4918 from hr_legislation_installations
4919 where legislation_code is null
4920 and action in ('I', 'U', 'F');
4921
4922 END IF;
4923 hr_legislation.hrrunprc_trace_on;
4924 hr_utility.trace('start install_ffc: ' || to_char(p_phase));
4925 hr_legislation.hrrunprc_trace_off;
4926
4927 install_ffc(p_phase); --install ff_contexts
4928 hr_legislation.hrrunprc_trace_on;
4929 hr_utility.trace('start install_fft: ' || to_char(p_phase));
4930 hr_legislation.hrrunprc_trace_off;
4931
4932 install_fft(p_phase); --install ff_Formula_types
4933
4934 hr_legislation.hrrunprc_trace_on;
4935 hr_utility.trace('start install_formulas: ' || to_char(p_phase));
4936 hr_legislation.hrrunprc_trace_off;
4937
4938 install_formulas(p_phase); --install formulas
4939
4940 hr_legislation.hrrunprc_trace_on;
4941 hr_utility.trace('start install_routes: ' || to_char(p_phase));
4942 hr_legislation.hrrunprc_trace_off;
4943
4944 install_routes(p_phase); --install routes,entities,db items
4945
4946 hr_legislation.hrrunprc_trace_on;
4947 hr_utility.trace('start install_functions: ' || to_char(p_phase));
4948 hr_legislation.hrrunprc_trace_off;
4949
4950 install_functions(p_phase); --install functions, context usages etc
4951
4952 hr_legislation.hrrunprc_trace_on;
4953 hr_utility.trace('start install_qpreports: ' || to_char(p_phase));
4954 hr_legislation.hrrunprc_trace_off;
4955
4956 install_qpreports(p_phase); --install quickpaint reports
4957
4958 hr_legislation.hrrunprc_trace_on;
4959 hr_utility.trace('start install_globals: ' || to_char(p_phase));
4960 hr_legislation.hrrunprc_trace_off;
4961
4962 install_globals(p_phase); --install globals
4963
4964 hr_legislation.hrrunprc_trace_on;
4965 hr_utility.trace('exit ff_data_dict.install: ' || to_char(p_phase));
4966 hr_legislation.hrrunprc_trace_off;
4967
4968 END install;
4969
4970 END ff_data_dict;
[Home] [Help]