1 PACKAGE body ff_data_dict AS
2 /* $Header: peffdict.pkb 120.4.12000000.2 2007/02/08 11:18:04 divicker ship $ */
3 --****************************************************************************
4 -- INSTALLATION PROCEDURE FOR : FF_CONTEXTS_F
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';
83 sql_cur := dbms_sql.open_cursor;
84 dbms_sql.parse(sql_cur,
85 statem,
86 dbms_sql.v7);
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
252 set context_id = l_new_surrogate_key
253 where context_id = stu_rec.c_surrogate_key;
254
255 update hr_s_application_ownerships
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));
409 hr_utility.trace('context_name ' ||
410 stu_rec.c_true_key);
411 hr_utility.trace('context_level ' ||
412 to_char(stu_rec.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
542 --
539 from hr_s_formula_types b
540 where a.formula_type_id = b.formula_type_id
541 );
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.
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
563
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
690 END remove;
687 delete from hr_s_ftype_context_usages
688 where formula_type_id = l_new_surrogate_key;
689
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
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
720 select 1
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
826 set type_description = stu_rec.type_description
823 BEGIN
824
825 update ff_formula_types
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
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
877 -- to allow any errors with parent contexts to be highlighted in pahse 1.
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;
952 END LOOP usages;
949 raise;
950 END;
951
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
1023 row_in_error exception;
1024 v_formula_text long;
1025 vlive_formula_text long;
1026 l_current_proc varchar2(80) := 'hr_legislation.install_formulas';
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
1093 PROCEDURE remove (v_id IN number)
1090
1091 END crt_exc;
1092
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
1174 update hr_s_application_ownerships
1175 set key_value = key_value - 50000000
1176 where key_name = 'FORMULA_ID';
1177
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)
1237 EXCEPTION WHEN NO_DATA_FOUND THEN
1234 or (r_distinct.legislation_code=legislation_code));
1235
1236
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
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;
1333
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
1385 -- failed, and this installation must not go ahead. If no data is
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
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
1485 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
1486
1487
1488 crt_exc('Row already created in a business group');
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 -- The last check examines the product installation table, and the
1537 -- made to see if that subgroup is active or not. This check only
1534 -- ownership details for the delivered row. By examining these
1535 -- tables the row is either deleted or not. If the delivered row
1536 -- is 'stamped' with a legislation subgroup, then a check must be
1538 -- needs to be performed in phase 1, since once this decision is
1539 -- made, it is pontless to perform this logic again.
1540
1541 -- The exception is raised within this procedure if no rows are returned
1542 -- in this select statement. If no rows are returned then one of the
1543 -- following is true:
1544 -- 1. No ownership parameters are defined.
1545 -- 2. The products, for which owning parameters are defined, are not
1546 -- installed with as status of 'I'.
1547 -- 3. The data is defined for a legislation subgroup that is not active.
1548
1549 IF p_phase <> 1 THEN
1550 return TRUE;
1551 END IF;
1552
1553
1554 select null --if exception raised then this row is not needed
1555 into l_null_return
1556 from dual
1557 where exists (select null
1558 from hr_s_application_ownerships a
1559 , fnd_product_installations b
1560 , fnd_application c
1561 where a.key_name = 'FORMULA_ID'
1562 and a.key_value = r_distinct.c_surrogate_key
1563 and a.product_name = c.application_short_name
1564 and c.application_id = b.application_id
1565 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1566 or
1567 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1568
1569
1570 -- Indicates row is required
1571
1572 return TRUE;
1573
1574 EXCEPTION WHEN NO_DATA_FOUND THEN
1575
1576 -- Row not needed for any installed product
1577
1578
1579 remove(r_distinct.c_surrogate_key);
1580
1581 -- Indicate row not needed
1582
1583 return FALSE;
1584
1585 END valid_ownership;
1586
1587 function formula_changed(form_id in number) return boolean is
1588 cursor all_rows(f_id in number) is
1589 select effective_start_date
1590 from hr_s_formulas_f
1591 where formula_id = f_id;
1592 begin
1593 -- First check on existence
1594
1595 BEGIN
1596 select null
1597 into l_dummy
1598 from ff_formulas_f
1599 where formula_id = form_id;
1600 EXCEPTION when others then
1601 return TRUE;
1602 END;
1603
1604 -- Check long column for all
1605 for r_all_rows in all_rows(form_id)
1606 loop
1607 BEGIN
1608 select formula_text
1609 into v_formula_text
1610 from hr_s_formulas_f
1611 where formula_id = form_id
1612 and effective_start_date = r_all_rows.effective_start_date;
1613
1614 select formula_text
1615 into vlive_formula_text
1616 from ff_formulas_f
1617 where formula_id = form_id
1618 and effective_start_date = r_all_rows.effective_start_date;
1619
1620 -- First check if non stub formula diff from live
1621 if (v_formula_text is not null and
1622 vlive_formula_text is not null and
1623 v_formula_text <> vlive_formula_text) then
1624 return TRUE;
1625 end if;
1626
1627 EXCEPTION when others then
1628 -- not all DT rows in hr_s match to live
1629 -- or if live version does not exist we need to transfer
1630 return TRUE;
1631 END;
1632
1633 end loop;
1634
1635 -- Now check the rest of the row's columns
1636 -- not imported by a later hdt
1637 begin
1638 select null
1639 into l_dummy
1640 from dual
1641 where not exists
1642 ((
1643 select effective_start_date,
1644 effective_end_date,
1645 description
1646 from hr_s_formulas_f
1647 where formula_id = form_id
1648 and formula_type_id = r_distinct.formula_type_id
1649 MINUS
1650 select effective_start_date,
1651 effective_end_date,
1652 description
1653 from ff_formulas_f
1654 where formula_id = form_id
1655 and formula_type_id = r_distinct.formula_type_id
1656 )
1657 UNION
1658 (
1659 select effective_start_date,
1663 where formula_id = form_id
1660 effective_end_date,
1661 description
1662 from ff_formulas_f
1664 and formula_type_id = r_distinct.formula_type_id
1665 MINUS
1666 select effective_start_date,
1667 effective_end_date,
1668 description
1669 from hr_s_formulas_f
1670 where formula_id = form_id
1671 and formula_type_id = r_distinct.formula_type_id
1672 ));
1673 -- if we get a row and not the exception then identical
1674 return FALSE;
1675 exception
1676 -- otherwise there is a diff
1677 when no_data_found then
1678 return TRUE;
1679 end;
1680 end formula_changed;
1681
1682 BEGIN
1683
1684 -- Two loops are used here. The main loop which select distinct primary
1685 -- key rows and an inner loop which selects all date effective rows for the
1686 -- primary key. The inner loop is only required in phase 2, since only
1687 -- in phase 2 are rows actually transferred. The logic reads as follows:
1688
1689 -- - Only deal with rows which have correct ownership details and will
1690 -- not cause integrity problems (valid_ownership).
1691
1692 -- - In Phase 1:
1693 -- - Delete delivery rows where the installed rows are identicle.
1694 -- - The UNION satement compares delivery rows to installed rows.
1695 -- If the sub query returns any rows, then the delivered
1696 -- tables and the installed tables are different.
1697
1698 -- In Phase 2:
1699 -- - Delete from the installed tables using the surrogate id.
1700 -- - If an installed row is to be replaced, the values of
1701 -- the surrogate keys will be identicle at this stage.
1702 -- - Data will then be deleted from the delivery tables.
1703 -- - Call the installation procedure for any child tables, that
1704 -- must be installed within the same commit unit. If any
1705 -- errors occur then rollback to the last declared savepoint.
1706 -- - Check that all integrity rules are still obeyed at the end
1707 -- of the installation (validity_checks).
1708
1709 -- An exception is used with this procedure 'row_in_error' in case an error
1710 -- is encountered from calling any function. If this is raised, then an
1711 -- exception is entered into the control tables (crt_exc();) and a rollback
1712 -- is performed.
1713
1714 IF p_phase = 1 THEN
1715 check_next_sequence;
1716 END IF;
1717
1718 FOR formula_names IN c_distinct LOOP
1719
1720
1721 savepoint new_formula_name;
1722
1723 r_distinct := formula_names;
1724
1725 BEGIN
1726
1727
1728 IF valid_ownership THEN
1729
1730 -- This row is wanted
1731
1732
1733 IF p_phase = 1 THEN
1734
1735
1736 -- Get new surrogate id and update child references
1737
1738 update_uid;
1739
1740 ELSE
1741
1742 -- Phase = 2
1743
1744 -- Now Ids are matched Check if formula definition is
1745 -- actually required
1746 if not formula_changed(r_distinct.c_surrogate_key) then
1747 remove(r_distinct.c_surrogate_key);
1748 else
1749
1750 delete from ff_fdi_usages_f
1751 where formula_id = r_distinct.c_surrogate_key;
1752
1753 delete from ff_compiled_info_f
1754 where formula_id = r_distinct.c_surrogate_key;
1755
1756 delete from ff_formulas_f
1757 where formula_id = r_distinct.c_surrogate_key;
1758
1759 FOR each_row IN c_each_row(r_distinct.c_surrogate_key) LOOP
1760
1761
1762 r_each_row := each_row;
1763
1764 IF NOT check_parents THEN
1765 RAISE row_in_error;
1766 END IF;
1767
1768 BEGIN
1769 insert into ff_formulas_f
1770 (formula_id
1771 ,effective_start_date
1772 ,effective_end_date
1773 ,business_group_id
1774 ,legislation_code
1775 ,formula_type_id
1776 ,formula_name
1777 ,description
1778 ,formula_text
1779 ,sticky_flag
1780 ,compile_flag
1781 ,last_update_date
1782 ,last_updated_by
1783 ,last_update_login
1784 ,created_by
1785 ,creation_date
1786 )
1787 values
1788 (r_each_row.formula_id
1789 ,r_each_row.effective_start_date
1790 ,r_each_row.effective_end_date
1794 ,r_each_row.formula_name
1791 ,r_each_row.business_group_id
1792 ,r_each_row.legislation_code
1793 ,r_each_row.formula_type_id
1795 ,r_each_row.description
1796 ,r_each_row.formula_text
1797 ,r_each_row.sticky_flag
1798 ,r_each_row.compile_flag
1799 ,r_each_row.last_update_date
1800 ,r_each_row.last_updated_by
1801 ,r_each_row.last_update_login
1802 ,r_each_row.created_by
1803 ,r_each_row.creation_date
1804 );
1805 EXCEPTION WHEN OTHERS THEN
1806 hr_legislation.hrrunprc_trace_on;
1807 hr_utility.trace('ins ff_formulas_f');
1808 hr_utility.trace('formula_name ' ||
1809 r_each_row.formula_name);
1810 hr_utility.trace('formula_id ' ||
1811 to_char(r_each_row.formula_id));
1812 hr_utility.trace('formula_type_id ' ||
1813 to_char(r_each_row.formula_type_id));
1814 hr_utility.trace(':lc: ' || ':' ||
1815 r_each_row.legislation_code || ':');
1816 hr_legislation.hrrunprc_trace_off;
1817 raise;
1818 END;
1819
1820 remove(r_distinct.c_surrogate_key);
1821
1822 END LOOP each_row;
1823
1824 validity_checks;
1825
1826 -- This will cause a rollback if error occurs
1827
1828 END IF;
1829
1830 end if; -- need the row
1831
1832
1833 END IF;
1834
1835 EXCEPTION WHEN row_in_error THEN
1836
1837 -- Already rolled back
1838
1839 null;
1840
1841 END;
1842
1843 END LOOP formula_names;
1844
1845 END install_formulas;
1846
1847 --****************************************************************************
1848 -- INSTALLATION PROCEDURE FOR : FF_ROUTES
1849 --****************************************************************************
1850
1851 PROCEDURE install_routes (p_phase IN number)
1852 ------------------------
1853 IS
1854 -- Procedure install routes/usages/entities/parameters/parameter values.
1855 -- The main driving installation cursor runs from hr_s_routes. All child
1856 -- rows are installed as cursors driven by the current route_id. The user
1857 -- entities then have child rows installed in a similar way.
1858 -- If the route has not changed, ie:does not need to be installed, it may
1859 -- still remain in the startup tables if there are child user entities to
1860 -- install. When a route is installed, the child rows of parameters and
1861 -- context usages are fully refreshed. Child user entities are stil treated
1862 -- as only being installed if required. Database ietms and parameter values
1863 -- are fully refreshed if the parent user entity is required to be installed.
1864
1865 l_null_return varchar2(1); -- For 'select null' statements
1866 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
1867 string varchar2(80);
1868 l_route_id number;
1869 l_route_name varchar2(2000);
1870 l_new_route BOOLEAN;
1871
1872 CURSOR c_all_159260
1873 IS
1874 select route_id route_id,
1875 route_name route_name
1876 from hr_s_routes
1877 where last_update_login = 159260;
1878
1879 CURSOR stu -- Selects all rows from startup entity
1880 IS
1881 select route_id c_surrogate_key
1882 ,route_name c_true_key
1883 ,user_defined_flag
1884 ,description
1885 ,text
1886 ,nvl(last_update_date,to_date('01-01-0001','DD-MM-YYYY')) last_update_date
1887 ,last_updated_by
1888 ,last_update_login
1889 ,created_by
1890 ,creation_date
1891 ,optimizer_hint
1892 ,rowid
1893 from hr_s_routes;
1894
1895 CURSOR user_entity (r_id IN number) -- To install all user entities
1896 IS -- for a given route
1897 select *
1898 from hr_s_user_entities
1899 where route_id = r_id;
1900
1901 CURSOR usage (r_id IN number) -- To install context usages
1902 IS -- for a given route
1903 select distinct *
1904 from hr_s_route_context_usages
1905 where route_id = r_id;
1906
1907 CURSOR parameter (r_id IN number) -- To install all route parameters
1908 IS -- for a given route
1909 select ROUTE_PARAMETER_ID
1910 , ROUTE_ID
1911 , DATA_TYPE
1912 , PARAMETER_NAME
1913 , SEQUENCE_NO
1914 , rowid
1915 from hr_s_route_parameters
1916 where route_id = r_id;
1917
1918 CURSOR parameter_value (ue_id IN number)
1919 IS
1920 -- To install parameter values for a given user entity
1921
1922 select *
1923 from hr_s_route_parameter_values
1924 where user_entity_id = ue_id;
1925
1926 CURSOR db_item (ue_id IN number)
1927 IS
1928 -- Cursor to install database items for a given user entity
1929
1930 select *
1934 stu_rec stu%ROWTYPE;
1931 from hr_s_database_items
1932 where user_entity_id = ue_id;
1933
1935
1936 PROCEDURE crt_exc (exception_type IN varchar2)
1937 ----------------------------------------------
1938 IS
1939 -- Reports any exceptions during the delivery of startup data to
1940 -- FF_ROUTES
1941
1942 BEGIN
1943 -- When the installation procedures encounter an error that cannot
1944 -- be handled, an exception is raised and all work is rolled back
1945 -- to the last savepoint. The installation process then continues
1946 -- with the next primary key to install. The same exception will
1947 -- not be raised more than once.
1948
1949
1950 rollback to new_route_name;
1951
1952 hr_legislation.insert_hr_stu_exceptions('ff_routes'
1953 , stu_rec.c_surrogate_key
1954 , exception_type
1955 , stu_rec.c_true_key);
1956
1957
1958 END crt_exc;
1959
1960 PROCEDURE check_id_conflicts
1961 ----------------------------
1962 IS
1963 -- Surrogate id conflicts may arise from two scenario's:
1964 -- 1. Where the newly select sequence value conflicts with values
1965 -- in the STU tables.
1966 -- 2. Where selected surrogate keys, from the installed tables,
1967 -- conflict with other rows in the STU tables.
1968 --
1969 -- Both of the above scenario's are tested for.
1970 -- The first is a simple match, where if a value is detected in the
1971 -- STU tables and the installed tables then a conflict is detected. In
1972 -- This instance all STU surrogate keys, for this table, are updated.
1973 -- Three tables are tested:
1974 -- 1. ff_routes
1975 -- 2. ff_user_entities
1976 -- 3. ff_route_parameters
1977 -- The second is tested for using the sequences.
1978 -- If the next value from the live sequence is within the range of
1979 -- delivered surrogate id's then the live sequence must be incremented.
1980 -- If no action is taken, then duplicates may be introduced into the
1981 -- delivered tables, and child rows may be totally invalidated.
1982 -- This procedure will check three sequences
1983 -- 1. ff_routes_S
1984 -- 2. ff_user_entities_s
1985 -- 3. ff_route_parameters_s
1986
1987 v_sequence_number number(9);
1988 v_min_delivered number(9);
1989 v_max_delivered number(9);
1990 --
1991 cursor get_ff_routes is
1992 select distinct null
1993 from ff_routes a
1994 where exists
1995 (select null
1996 from hr_s_routes b
1997 where a.route_id = b.route_id
1998 );
1999
2000 cursor get_ff_route_parameters is
2001 select distinct null
2002 from ff_route_parameters a
2003 where exists
2004 (select null
2005 from hr_s_route_parameters b
2006 where b.route_parameter_id = a.route_parameter_id
2007 );
2008
2009 cursor get_ff_user_entities is
2010 select distinct null
2011 from ff_user_entities a
2012 where exists
2013 (select null
2014 from hr_s_user_entities b
2015 where a.user_entity_id = b.user_entity_id
2016 );
2017 --
2018 BEGIN
2019
2020 -- Start with check against ff_routes
2021
2022
2023 BEGIN --check that the installde routes will not conflict
2024 --with the delivered values
2025
2026 --
2027 open get_ff_routes;
2028 fetch get_ff_routes into l_null_return;
2029 IF get_ff_routes%NOTFOUND OR get_ff_routes%NOTFOUND IS NULL THEN
2030 RAISE NO_DATA_FOUND;
2031 END IF;
2032 close get_ff_routes;
2033 --
2034 --conflict may exist
2035 --update all route_id's to remove conflict
2036
2037 update /*+NO_INDEX*/ hr_s_routes
2038 set route_id = route_id - 50000000;
2039
2040 update hr_s_application_ownerships
2041 set key_value = key_value - 50000000
2042 where key_name = 'ROUTE_ID';
2043
2044 update /*+NO_INDEX*/ hr_s_balance_dimensions
2045 set route_id = route_id - 50000000;
2046
2047 update /*+NO_INDEX*/ hr_s_dimension_routes
2048 set route_id = route_id - 50000000;
2049
2050 update /*+NO_INDEX*/ hr_s_route_context_usages
2051 set route_id = route_id - 50000000;
2052
2053 update /*+NO_INDEX*/ hr_s_user_entities
2054 set route_id = route_id - 50000000;
2055
2056 update /*+NO_INDEX*/ hr_s_route_parameters
2057 set route_id = route_id - 50000000;
2058
2059 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2060
2061 END; --check of route_id
2062
2063
2064 BEGIN --check that no conflict exists within the route
2065 --parameter id's
2066
2067 --
2068 open get_ff_route_parameters;
2069 fetch get_ff_route_parameters into l_null_return;
2070 IF get_ff_route_parameters%NOTFOUND
2074 close get_ff_route_parameters;
2071 OR get_ff_route_parameters%NOTFOUND IS NULL THEN
2072 RAISE NO_DATA_FOUND;
2073 END IF;
2075 --
2076 --Conflict exists, so update the stu values of the parameter id
2077
2078 update hr_s_route_parameters
2079 set route_parameter_id = route_parameter_id -50000000;
2080
2081 update hr_s_route_parameter_values
2082 set route_parameter_id = route_parameter_id -50000000;
2083
2084 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2085
2086 END; --check of route_parameter_id
2087
2088
2089
2090 BEGIN --check that no conflict exists with the id's of the installed
2091 --user entities, compared with the values of delivered UE's
2092
2093 --
2094 open get_ff_user_entities;
2095 fetch get_ff_user_entities into l_null_return;
2096 IF get_ff_user_entities%NOTFOUND OR get_ff_user_entities%NOTFOUND IS NULL THEN
2097 RAISE NO_DATA_FOUND;
2098 END IF;
2099 close get_ff_user_entities;
2100 --
2101 --conflict exists, so update the stu values of user_entity_id
2102
2103 update /*+NO_INDEX*/ hr_s_user_entities
2104 set user_entity_id = user_entity_id -50000000;
2105
2106 update /*+NO_INDEX*/ hr_s_database_items
2107 set user_entity_id = user_entity_id -50000000;
2108
2109 update /*+NO_INDEX*/ hr_s_route_parameter_values
2110 set user_entity_id = user_entity_id -50000000;
2111
2112 update /*+NO_INDEX*/ hr_s_report_format_items_f
2113 set user_entity_id = user_entity_id -50000000;
2114
2115 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
2116
2117 END; --check of user_entity_id
2118
2119
2120 select min(route_id) - (count(*) *3)
2121 , max(route_id) + (count(*) *3)
2122 into v_min_delivered
2123 , v_max_delivered
2124 from hr_s_routes;
2125
2126 select ff_routes_s.nextval
2127 into v_sequence_number
2128 from dual;
2129
2130 IF v_sequence_number
2131 BETWEEN v_min_delivered AND v_max_delivered THEN
2132
2133 hr_legislation.munge_sequence('FF_ROUTES_S',
2134 v_sequence_number,
2135 v_max_delivered);
2136
2137 END IF;
2138
2139 -- Now check ff_user_entities
2140
2141
2142 select min(user_entity_id) - (count(*) *3)
2143 , max(user_entity_id) + (count(*) *3)
2144 into v_min_delivered
2145 , v_max_delivered
2146 from hr_s_user_entities;
2147
2148 select ff_user_entities_s.nextval
2149 into v_sequence_number
2150 from dual;
2151
2152 IF v_sequence_number
2153 BETWEEN v_min_delivered AND v_max_delivered THEN
2154
2155 hr_legislation.munge_sequence('FF_USER_ENTITIES_S',
2156 v_sequence_number,
2157 v_max_delivered);
2158
2159 END IF;
2160
2161 -- Now check ff_route_parameters
2162
2163
2164 select min(route_parameter_id) - (count(*) *3)
2165 , max(route_parameter_id) + (count(*) *3)
2166 into v_min_delivered
2167 , v_max_delivered
2168 from hr_s_route_parameters;
2169
2170 select ff_route_parameters_s.nextval
2171 into v_sequence_number
2172 from dual;
2173
2174 IF v_sequence_number
2175 BETWEEN v_min_delivered AND v_max_delivered THEN
2176
2177 hr_legislation.munge_sequence('FF_ROUTE_PARAMETERS_S',
2178 v_sequence_number,
2179 v_max_delivered);
2180
2181 END IF;
2182
2183 END check_id_conflicts;
2184
2185 PROCEDURE update_uid
2186 --------------------
2187 IS
2188 -- Subprogram to update surrogate UID and all occurrences in child rows
2189
2190 l_new_parameter_id number(9);
2191 l_new_entity_id number(9);
2192
2193 cursor c_form3(p_route_id number) is
2194 select distinct ffu.formula_id fid
2195 from ff_fdi_usages_f ffu
2196 where ffu.item_name in (select fdbi.user_name
2197 from ff_database_items fdbi,
2198 ff_user_entities fue
2199 where fdbi.user_entity_id = fue.user_entity_id
2200 and fue.route_id = p_route_id);
2201
2202 BEGIN
2203
2204 l_new_surrogate_key := null;
2205
2206 BEGIN
2207
2208 select distinct route_id
2209 into l_new_surrogate_key
2210 from ff_routes
2211 where route_name = stu_rec.c_true_key
2212 and user_defined_flag = 'N';
2213
2214 EXCEPTION WHEN NO_DATA_FOUND THEN
2215
2216
2217 select ff_routes_s.nextval
2218 into l_new_surrogate_key
2219 from dual;
2220
2221 WHEN TOO_MANY_ROWS THEN
2222 hr_legislation.hrrunprc_trace_on;
2226 hr_legislation.hrrunprc_trace_off;
2223 hr_utility.trace('sel route ff_routes TMR');
2224 hr_utility.trace('route_name ' ||
2225 stu_rec.c_true_key);
2227 raise;
2228 END;
2229
2230
2231 -- Update all child entities
2232
2233
2234 update hr_s_routes
2235 set route_id = l_new_surrogate_key
2236 where rowid = stu_rec.rowid;
2237
2238 update hr_s_application_ownerships
2239 set key_value = to_char(l_new_surrogate_key)
2240 where key_value = to_char(stu_rec.c_surrogate_key)
2241 and key_name = 'ROUTE_ID';
2242
2243 update hr_s_balance_dimensions
2244 set route_id = l_new_surrogate_key
2245 where route_id = stu_rec.c_Surrogate_key;
2246
2247 update hr_s_dimension_routes
2248 set route_id = l_new_surrogate_key
2249 where route_id = stu_rec.c_Surrogate_key;
2250
2251 update hr_s_route_context_usages
2252 set route_id = l_new_surrogate_key
2253 where route_id = stu_rec.c_Surrogate_key;
2254
2255
2256 FOR delivered_params IN parameter(stu_Rec.c_Surrogate_key) LOOP
2257
2258 BEGIN --select of new surrogate id
2259
2260
2261 select route_parameter_id
2262 into l_new_parameter_id
2263 from ff_route_parameters
2264 where sequence_no = delivered_params.sequence_no
2265 and parameter_name = delivered_params.parameter_name
2266 and route_id = l_new_surrogate_key;
2267
2268 EXCEPTION WHEN NO_DATA_FOUND THEN
2269
2270 /* As this could be a change to either the sequence ordering
2271 or the parameter naming (or both) be safe and just delete
2272 all parameters for this route and reimport them from scratch
2273 so as to avoid any constraint violations */
2274
2275 /* bug 5501644 */
2276 for r_form3 in c_form3(l_new_surrogate_key) loop
2277 delete ff_fdi_usages_f where formula_id = r_form3.fid;
2278 delete ff_compiled_info_f where formula_id = r_form3.fid;
2279 end loop;
2280
2281 delete ff_route_parameter_values
2282 where route_parameter_id in (
2283 select route_parameter_id
2284 from ff_route_parameters
2285 where route_id = l_new_surrogate_key);
2286
2287 delete ff_route_parameters
2288 where route_id = l_new_surrogate_key;
2289
2290 select ff_route_parameters_s.nextval
2291 into l_new_parameter_id
2292 from dual;
2293
2294 WHEN TOO_MANY_ROWS THEN
2295
2296 hr_legislation.hrrunprc_trace_on;
2297 hr_utility.trace('sel ff_route_parameters TMR');
2298 hr_utility.trace('parameter_name ' ||
2299 delivered_params.parameter_name);
2300 hr_utility.trace('route_id ' ||
2301 to_char(l_new_surrogate_key));
2302 hr_utility.trace('route_name ' ||
2303 stu_rec.c_true_key);
2304 hr_utility.trace('sequence_no ' ||
2305 to_char(delivered_params.sequence_no));
2306 hr_legislation.hrrunprc_trace_off;
2307 raise;
2308
2309 END; --select of new surrogate id
2310
2311 update hr_s_route_parameters
2312 set route_id = l_new_surrogate_key
2313 , route_parameter_id = l_new_parameter_id
2314 where route_parameter_id = delivered_params.route_parameter_id;
2315
2316 update hr_s_route_parameter_values
2317 set route_parameter_id = l_new_parameter_id
2318 where route_parameter_id = delivered_params.route_parameter_id;
2319
2320 END LOOP delivered_params;
2321
2322 FOR delivered_entities IN user_entity(stu_Rec.c_Surrogate_key) LOOP
2323
2324 BEGIN
2325
2326 select user_entity_id
2327 into l_new_entity_id
2328 from ff_user_entities
2329 where user_entity_name = delivered_entities.user_entity_name
2330 and nvl(legislation_code,'X') = nvl(delivered_entities.legislation_code,'X')
2331 and route_id = l_new_surrogate_key;
2332
2333 EXCEPTION WHEN NO_DATA_FOUND THEN
2334
2335 select ff_user_entities_s.nextval
2336 into l_new_entity_id
2337 from dual;
2338
2339 WHEN TOO_MANY_ROWS THEN
2340 hr_legislation.hrrunprc_trace_on;
2341 hr_utility.trace('sel ff_user_entities TMR');
2342 hr_utility.trace('user_entity_name ' ||
2343 delivered_entities.user_entity_name);
2344 hr_utility.trace('route_id ' ||
2345 to_char(l_new_surrogate_key));
2346 hr_utility.trace('route_name ' ||
2347 stu_rec.c_true_key);
2348 hr_utility.trace(':lc: ' || ':' ||
2349 delivered_entities.legislation_code || ':');
2353
2350 hr_legislation.hrrunprc_trace_off;
2351 raise;
2352 END;
2354 update hr_s_user_entities
2355 set user_entity_id = l_new_entity_id
2356 , route_id = l_new_surrogate_key
2357 where user_entity_id = delivered_entities.user_entity_id;
2358
2359 update hr_s_database_items
2360 set user_entity_id = l_new_entity_id
2361 where user_entity_id = delivered_entities.user_entity_id;
2362
2363 update hr_s_route_parameter_values
2364 set user_entity_id = l_new_entity_id
2365 where user_entity_id = delivered_entities.user_entity_id;
2366
2367 update hr_s_report_format_items_f
2368 set user_entity_id = l_new_entity_id
2369 where user_entity_id = delivered_entities.user_entity_id;
2370
2371 END LOOP;
2372
2373 END update_uid;
2374
2375 PROCEDURE remove (v_route_id IN number)
2376 ---------------------------------------
2377 IS
2378 -- Remove a row from the startup tables
2379
2380 BEGIN
2381
2382 delete from hr_s_database_items a
2383 where a.user_entity_id in
2384 (select b.user_entity_id
2385 from hr_s_user_entities b
2386 where b.route_id = v_route_id
2387 );
2388
2389 delete from hr_s_route_parameter_values a
2390 where a.user_entity_id in
2391 (select b.user_entity_id
2392 from hr_s_user_entities b
2393 where b.route_id = v_route_id
2394 );
2395
2396 delete from hr_s_user_entities
2397 where route_id = v_route_id;
2398
2399 delete from hr_s_route_context_usages
2400 where route_id = v_route_id;
2401
2402 delete from hr_s_route_parameters
2403 where route_id = v_route_id;
2404
2405 delete from hr_s_routes
2406 where route_id = v_route_id;
2407
2408 END remove;
2409
2410 FUNCTION valid_ownership RETURN BOOLEAN
2411 ---------------------------------------
2412 IS
2413 -- Test ownership of this current row
2414 cursor get_application_ownerships is
2415 select null
2416 from dual
2417 where exists
2418 (select null
2419 from hr_s_application_ownerships a
2420 , fnd_product_installations b
2421 , fnd_application c
2422 where a.key_name = 'ROUTE_ID'
2423 and a.key_value = stu_rec.c_surrogate_key
2424 and a.product_name = c.application_short_name
2425 and c.application_id = b.application_id
2426 and ((b.status = 'I' and c.application_short_name <> 'PQP')
2427 or
2428 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
2429 --
2430 BEGIN
2431
2432 -- This routine only operates in phase 1. Rows are present in the
2433 -- table hr_application_ownerships in the delivery account, which
2434 -- dictate which products a piece of data is used for. If the query
2435 -- returns a row then this data is required, and the function will
2436 -- return true. If no rows are returned and an exception is raised,
2437 -- then this row is not required and may be deleted from the delivery
2438 -- tables.
2439
2440 -- If legislation code and subgroup code are included on the delivery
2441 -- tables, a check must be made to determine if the data is defined for
2442 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
2443 -- installation.
2444
2445 -- A return code of TRUE indicates that the row is required.
2446
2447 -- The exception is raised within this procedure if no rows are returned
2448 -- in this select statement. If no rows are returned then one of the
2449 -- following is true:
2450 -- 1. No ownership parameters are defined.
2451 -- 2. The products, for which owning parameters are defined, are not
2452 -- installed with as status of 'I'.
2453 -- 3. The data is defined for a legislation subgroup that is not active.
2454
2455 IF p_phase <> 1 THEN -- Only perform in phase 1
2456 return TRUE;
2457 END IF;
2458
2459
2460 -- If exception raised below then this row is not needed
2461 --
2462 open get_application_ownerships;
2463 fetch get_application_ownerships into l_null_return;
2464 IF get_application_ownerships%NOTFOUND OR get_application_ownerships%NOTFOUND IS NULL THEN
2465 RAISE NO_DATA_FOUND;
2466 END IF;
2467 close get_application_ownerships;
2468 --
2469 -- Indicate row is required
2470
2471 return TRUE;
2472
2473 EXCEPTION WHEN NO_DATA_FOUND THEN
2474
2475 -- Row not needed for any installed product
2476
2477 remove(stu_Rec.c_surrogate_key);
2478
2479 -- Indicate row not needed
2480
2481 return FALSE;
2482
2483 END valid_ownership;
2484
2485
2486 FUNCTION route_changed (p_route_id IN number,
2487 p_new_route OUT nocopy boolean) RETURN BOOLEAN
2488 ------------------------------------------------------------
2489 IS
2493 -- Changed the comparison rules so that the routes are now only
2490 -- Function to test if the current route is different to the one
2491 -- installed. If the route is different the function will return true.
2492 --
2494 -- flagged as different if the route text itself has changed. In the
2495 -- past the routes were also considered to be different if the last
2496 -- update dates were differed. Unfortunately this was the case whenever
2497 -- a dump file was recreated, even though the route text was unchanged.
2498 -- This led to all routes being trashed and recreated on the target
2499 -- account, db items being lost etc. RMF 26.09.95.
2500 --
2501 -- Optimizer hint now can trigger update
2502
2503 v_route_text long; -- Used to select the installed route text
2504 v_optimizer_hint ff_routes.optimizer_hint%type;
2505 v_last_update date; -- Used to select the installed last update
2506
2507 BEGIN
2508
2509 select text, optimizer_hint
2510 into v_route_text, v_optimizer_hint
2511 from ff_routes
2512 where route_id = p_route_id;
2513
2514 IF v_route_text = stu_rec.text AND
2515 nvl(v_optimizer_hint, 'nohint') =
2516 nvl(stu_rec.optimizer_hint, 'nohint') THEN
2517 -- Route text and hint is identical
2518 return FALSE;
2519 END IF;
2520
2521 p_new_route := FALSE;
2522 -- hr_legislation.hrrunprc_trace_on;
2523 -- hr_utility.trace('route diff: ' || to_char(l_new_surrogate_key) || ' '
2524 -- || stu_rec.c_true_key);
2525 -- hr_legislation.hrrunprc_trace_off;
2526
2527 return TRUE; --delivered route is defferent
2528
2529 EXCEPTION WHEN NO_DATA_FOUND THEN
2530 -- The route is not installed
2531 p_new_route := TRUE;
2532 -- hr_legislation.hrrunprc_trace_on;
2533 -- hr_utility.trace('new route: ' || to_char(l_new_surrogate_key) || ' '
2534 -- || stu_rec.c_true_key);
2535 -- hr_legislation.hrrunprc_trace_off;
2536 return TRUE;
2537
2538 END;
2539
2540 FUNCTION valid_to_insert RETURN BOOLEAN
2541 ---------------------------------------
2542 IS
2543 -- Check to see if the route can be installed.
2544 --
2545 l_number number(9);
2546 --
2547
2548 BEGIN
2549 -- Test to see if the route has been created already by a user. The
2550 -- function will return true if the route is okay to be installed.
2551
2552 --
2553 select count(*)
2554 into l_number
2555 from ff_routes a
2556 where a.route_name = stu_rec.c_true_key
2557 and a.user_defined_flag = 'Y';
2558 --
2559 if l_number = 0 or l_number is null then
2560 return TRUE;
2561 else
2562 --This Route name is an existing User-Defined route
2563 --So cannot be delivered.
2564 crt_exc('FF_Routes row already created by a user');
2565 return FALSE;--indicates this row is not to be transferred
2566 end if;
2567
2568 EXCEPTION WHEN NO_DATA_FOUND THEN
2569
2570
2571 -- No user created row exists for this primary key
2572
2573 return TRUE;
2574
2575 END valid_to_insert;
2576
2577 FUNCTION user_entity_changed (v_user_entity_id IN number) RETURN BOOLEAN
2578 ------------------------------------------------------------------------
2579 IS
2580 -- Check to see if the current user entity differs from the install one
2581 -- TRUE is returned if the user entity differs
2582
2583 ue_name1 varchar2(240);
2584 ue_name2 varchar2(240);
2585
2586 BEGIN
2587
2588 select null
2589 into l_null_return
2590 from dual
2591 where exists
2592 ((select
2593 BUSINESS_GROUP_ID,
2594 LEGISLATION_CODE,
2595 ROUTE_ID,
2596 NOTFOUND_ALLOWED_FLAG,
2597 USER_ENTITY_NAME,
2598 CREATOR_ID,
2599 CREATOR_TYPE,
2600 ENTITY_DESCRIPTION
2601 from hr_s_user_entities
2602 where user_entity_id = v_user_entity_id
2603 MINUS
2604 select
2605 BUSINESS_GROUP_ID,
2606 LEGISLATION_CODE,
2607 ROUTE_ID,
2608 NOTFOUND_ALLOWED_FLAG,
2609 USER_ENTITY_NAME,
2610 CREATOR_ID,
2611 CREATOR_TYPE,
2612 ENTITY_DESCRIPTION
2613 from ff_user_entities
2614 where user_entity_id = v_user_entity_id
2615 )
2616 UNION
2617 (select
2618 BUSINESS_GROUP_ID,
2619 LEGISLATION_CODE,
2620 ROUTE_ID,
2621 NOTFOUND_ALLOWED_FLAG,
2622 USER_ENTITY_NAME,
2623 CREATOR_ID,
2624 CREATOR_TYPE,
2625 ENTITY_DESCRIPTION
2626 from ff_user_entities
2627 where user_entity_id = v_user_entity_id
2628 MINUS
2629 select
2633 NOTFOUND_ALLOWED_FLAG,
2630 BUSINESS_GROUP_ID,
2631 LEGISLATION_CODE,
2632 ROUTE_ID,
2634 USER_ENTITY_NAME,
2635 CREATOR_ID,
2636 CREATOR_TYPE,
2637 ENTITY_DESCRIPTION
2638 from hr_s_user_entities
2639 where user_entity_id = v_user_entity_id
2640 ))
2641 or exists
2642 (select user_name,
2643 data_type,
2644 definition_text,
2645 null_allowed_flag,
2646 description
2647 from hr_s_database_items
2648 where user_entity_id = v_user_entity_id
2649 MINUS
2650 select user_name,
2651 data_type,
2652 definition_text,
2653 null_allowed_flag,
2654 description
2655 from ff_database_items
2656 where user_entity_id = v_user_entity_id)
2657 or exists
2658 (select value
2659 from hr_s_route_parameter_values
2660 where user_entity_id = v_user_entity_id
2661 MINUS
2662 select value
2663 from ff_route_parameter_values
2664 where user_entity_id = v_user_entity_id);
2665
2666 -- Show that this user entity differs from the install one
2667 begin
2668 select user_entity_name
2669 into ue_name1
2670 from ff_user_entities
2671 where user_entity_id = v_user_entity_id;
2672 select user_entity_name
2673 into ue_name2
2674 from hr_s_user_entities
2675 where user_entity_id = v_user_entity_id;
2676 -- hr_legislation.hrrunprc_trace_on;
2677 -- hr_utility.trace('ue chg: ' || to_char(v_user_entity_id) || ' '
2678 -- || ue_name1 || ':' || ue_name2);
2679 -- hr_legislation.hrrunprc_trace_off;
2680 exception when others then null;
2681 end;
2682
2683 return TRUE;
2684
2685 EXCEPTION WHEN NO_DATA_FOUND THEN
2686
2687
2688 return FALSE;
2689
2690 END user_entity_changed;
2691
2692 FUNCTION install_user_entity (v_route_id IN number) RETURN BOOLEAN
2693 ------------------------------------------------------------------
2694 IS
2695 -- Logic to insert the user entity and all children. If called in pahse one
2696 -- TRUE is returned as soon as a user entity is found that has to be installed.
2697 -- If no user entities are to be installed then FALSE is returned.
2698
2699 cursor c_form(p_ue_id number) is
2700 select distinct fue.formula_id fid
2701 from ff_fdi_usages_f fue
2702 where fue.item_name in (select fdbi.user_name
2703 from ff_database_items fdbi
2704 where fdbi.user_entity_id = p_ue_id);
2705
2706 BEGIN
2707
2708 FOR all_user_entities IN user_entity(v_route_id) LOOP
2709
2710
2711 IF user_entity_changed(all_user_entities.user_entity_id) THEN
2712
2713 IF p_phase = 1 THEN
2714 return TRUE;
2715 END IF;
2716
2717 -- delete all formula usages, compiled info that may be
2718 -- affected by this dbi
2719 for r_form in c_form(all_user_entities.user_entity_id) loop
2720 delete ff_fdi_usages_f where formula_id = r_form.fid;
2721 delete ff_compiled_info_f where formula_id = r_form.fid;
2722 end loop;
2723
2724 update ff_user_entities
2725 set business_group_id = all_user_entities.business_group_id
2726 ,legislation_code = all_user_entities.legislation_code
2727 ,route_id = all_user_entities.route_id
2728 ,notfound_allowed_flag = all_user_entities.notfound_allowed_flag
2729 ,user_entity_name = all_user_entities.user_entity_name
2730 ,creator_id = all_user_entities.creator_id
2731 ,creator_type = all_user_entities.creator_type
2732 ,entity_description = all_user_entities.entity_description
2733 ,last_update_date = all_user_entities.last_update_date
2734 ,last_updated_by = all_user_entities.last_updated_by
2735 ,last_update_login = all_user_entities.last_update_login
2736 ,created_by = all_user_entities.created_by
2737 ,creation_date = all_user_entities.creation_date
2738 where user_entity_id = all_user_entities.user_entity_id;
2739
2740 IF SQL%NOTFOUND THEN
2741
2742 BEGIN
2743 insert into ff_user_entities
2744 (user_entity_id
2745 ,business_group_id
2746 ,legislation_code
2747 ,route_id
2748 ,notfound_allowed_flag
2749 ,user_entity_name
2750 ,creator_id
2751 ,creator_type
2752 ,entity_description
2753 ,last_update_date
2754 ,last_updated_by
2755 ,last_update_login
2756 ,created_by
2757 ,creation_date
2758 )
2762 ,all_user_entities.legislation_code
2759 values
2760 (all_user_entities.user_entity_id
2761 ,all_user_entities.business_group_id
2763 ,all_user_entities.route_id
2764 ,all_user_entities.notfound_allowed_flag
2765 ,all_user_entities.user_entity_name
2766 ,all_user_entities.creator_id
2767 ,all_user_entities.creator_type
2768 ,all_user_entities.entity_description
2769 ,all_user_entities.last_update_date
2770 ,all_user_entities.last_updated_by
2771 ,all_user_entities.last_update_login
2772 ,all_user_entities.created_by
2773 ,all_user_entities.creation_date
2774 );
2775
2776 EXCEPTION WHEN OTHERS THEN
2777 hr_legislation.hrrunprc_trace_on;
2778 hr_utility.trace('ins ff_user_entities');
2779 hr_utility.trace('user_entity_name ' ||
2780 all_user_entities.user_entity_name);
2781 hr_utility.trace('user_entity_id ' ||
2782 to_char(all_user_entities.user_entity_id));
2783 hr_utility.trace('route_id ' ||
2784 to_char(all_user_entities.route_id));
2785 hr_utility.trace('route_name ' ||
2786 stu_rec.c_true_key);
2787 hr_utility.trace('creator_id ' ||
2788 to_char(all_user_entities.creator_id));
2789 hr_utility.trace('creator_type ' ||
2790 all_user_entities.creator_type);
2791 hr_utility.trace(':lc: ' || ':' ||
2792 all_user_entities.legislation_code || ':');
2793 hr_legislation.hrrunprc_trace_off;
2794 raise;
2795 END;
2796 END IF;
2797
2798 FOR all_db_items IN
2799 db_item(all_user_entities.user_entity_id)
2800 LOOP
2801
2802 update ff_database_items
2803 set data_type = all_db_items.data_type
2804 ,definition_text = all_db_items.definition_text
2805 ,null_allowed_flag = all_db_items.null_allowed_flag
2806 ,description = all_db_items.description
2807 ,last_update_date = all_db_items.last_update_date
2808 ,last_updated_by = all_db_items.last_updated_by
2809 ,last_update_login = all_db_items.last_update_login
2810 ,created_by = all_db_items.created_by
2811 ,creation_date = all_db_items.creation_date
2812 where user_name = all_db_items.user_name
2813 and user_entity_id = all_db_items.user_entity_id;
2814
2815 IF SQL%NOTFOUND THEN
2816
2817 BEGIN
2818 insert into ff_database_items
2819 (user_name
2820 ,user_entity_id
2821 ,data_type
2822 ,definition_text
2823 ,null_allowed_flag
2824 ,description
2825 ,last_update_date
2826 ,last_updated_by
2827 ,last_update_login
2828 ,created_by
2829 ,creation_date
2830 )
2831 VALUES
2832 (all_db_items.user_name
2833 ,all_db_items.user_entity_id
2834 ,all_db_items.data_type
2835 ,all_db_items.definition_text
2836 ,all_db_items.null_allowed_flag
2837 ,all_db_items.description
2838 ,all_db_items.last_update_date
2839 ,all_db_items.last_updated_by
2840 ,all_db_items.last_update_login
2841 ,all_db_items.created_by
2842 ,all_db_items.creation_date
2843 );
2844 EXCEPTION WHEN OTHERS THEN
2845 hr_legislation.hrrunprc_trace_on;
2846 hr_utility.trace('ins ff_database_items');
2847 hr_utility.trace('dbi user_name ' ||
2848 all_db_items.user_name);
2849 hr_utility.trace('user_entity_id ' ||
2850 to_char(all_db_items.user_entity_id));
2851 hr_utility.trace('user_entity_name ' ||
2852 all_user_entities.user_entity_name);
2853 hr_utility.trace('route_id ' ||
2854 to_char(all_user_entities.route_id));
2855 hr_utility.trace('route_name ' ||
2856 stu_rec.c_true_key);
2857 hr_utility.trace('creator_id ' ||
2858 to_char(all_user_entities.creator_id));
2859 hr_utility.trace('creator_type ' ||
2860 all_user_entities.creator_type);
2861 hr_legislation.hrrunprc_trace_off;
2862 raise;
2863 END;
2864
2865 END IF;
2866
2867 END LOOP all_db_items;
2868
2869 FOR pvalues IN
2870 parameter_value(all_user_entities.user_entity_id)
2871 LOOP
2872
2873 BEGIN
2874
2875 update ff_route_parameter_values
2876 set value = pvalues.value
2880 ,created_by = pvalues.created_by
2877 ,last_update_date = pvalues.last_update_date
2878 ,last_updated_by = pvalues.last_updated_by
2879 ,last_update_login = pvalues.last_update_login
2881 ,creation_date = pvalues.creation_date
2882 where route_parameter_id = pvalues.route_parameter_id
2883 and user_entity_id = pvalues.user_entity_id;
2884
2885 IF SQL%NOTFOUND THEN
2886
2887 BEGIN
2888 insert into ff_route_parameter_values
2889 (route_parameter_id
2890 ,user_entity_id
2891 ,value
2892 ,last_update_date
2893 ,last_updated_by
2894 ,last_update_login
2895 ,created_by
2896 ,creation_date
2897 )
2898 VALUES
2899 (pvalues.route_parameter_id
2900 ,pvalues.user_entity_id
2901 ,pvalues.value
2902 ,pvalues.last_update_date
2903 ,pvalues.last_updated_by
2904 ,pvalues.last_update_login
2905 ,pvalues.created_by
2906 ,pvalues.creation_date
2907 );
2908 EXCEPTION WHEN OTHERS THEN
2909 hr_legislation.hrrunprc_trace_on;
2910 hr_utility.trace('ins ff_route_parameter_values');
2911 hr_utility.trace('value ' ||
2912 pvalues.value);
2913 hr_utility.trace('route_parameter_id ' ||
2914 to_char(pvalues.route_parameter_id));
2915 hr_utility.trace('user_entity_id ' ||
2916 to_char(pvalues.user_entity_id));
2917 hr_utility.trace('user_entity_name ' ||
2918 all_user_entities.user_entity_name);
2919 hr_utility.trace('route_id ' ||
2920 to_char(all_user_entities.route_id));
2921 hr_utility.trace('route_name ' ||
2922 stu_rec.c_true_key);
2923 hr_utility.trace('creator_id ' ||
2924 to_char(all_user_entities.creator_id));
2925 hr_utility.trace('creator_type ' ||
2926 all_user_entities.creator_type);
2927 hr_legislation.hrrunprc_trace_off;
2928 raise;
2929 END;
2930
2931 END IF;
2932
2933 END;
2934
2935 END LOOP pvalues;
2936
2937
2938 END IF;
2939
2940 END LOOP all_user_entities;
2941
2942 IF p_phase = 1 THEN
2943 return FALSE;
2944 ELSE
2945 return TRUE;
2946 END IF;
2947
2948 END install_user_entity;
2949
2950 PROCEDURE delete_route_form_usage
2951 ---------------------------------
2952 IS
2953
2954 cursor c_form2(p_route_id number) is
2955 select /*+ INDEX(ffu FF_FDI_USAGES_F_N50)*/
2956 distinct ffu.formula_id fid
2957 from ff_fdi_usages_f ffu
2958 where ffu.item_name in (select fdbi.user_name
2959 from ff_database_items fdbi,
2960 ff_user_entities fue,
2961 ff_routes fr
2962 where fdbi.user_entity_id = fue.user_entity_id
2963 and fue.route_id = fr.route_id
2964 and fr.route_id = p_route_id);
2965
2966 BEGIN
2967 -- delete all formula usages, compiled info that may be
2968 -- affected by this dbi
2969 for r_form2 in c_form2(stu_rec.c_surrogate_key) loop
2970
2971 delete ff_fdi_usages_f where formula_id = r_form2.fid;
2972 delete ff_compiled_info_f where formula_id = r_form2.fid;
2973 end loop;
2974
2975 END delete_route_form_usage;
2976
2977 PROCEDURE insert_route
2978 ----------------------
2979 IS
2980 -- Logic to insert or update a route, depending upon whether it exists
2981 -- already in the live tables
2982
2983 BEGIN
2984
2985 update ff_routes
2986 set user_defined_flag = stu_rec.user_defined_flag
2987 , description = stu_Rec.description
2988 , text = stu_rec.text
2989 , last_update_date = stu_rec.last_update_date
2990 , last_updated_by = stu_rec.last_updated_by
2991 , last_update_login = stu_rec.last_update_login
2992 , created_by = stu_rec.created_by
2993 , creation_date = stu_rec.creation_date
2994 , optimizer_hint = stu_rec.optimizer_hint
2995 where route_id = stu_rec.c_Surrogate_key;
2996
2997 IF SQL%NOTFOUND THEN
2998
2999 BEGIN
3000 insert into ff_Routes
3001 (route_id
3002 ,route_name
3003 ,user_defined_flag
3004 ,description
3005 ,text
3006 ,last_update_date
3007 ,last_updated_by
3008 ,last_update_login
3009 ,created_by
3010 ,creation_date
3011 ,optimizer_hint
3012 )
3013 values
3014 (stu_rec.c_surrogate_key
3015 ,stu_rec.c_true_key
3019 ,stu_rec.last_update_date
3016 ,stu_rec.user_defined_flag
3017 ,stu_rec.description
3018 ,stu_rec.text
3020 ,stu_rec.last_updated_by
3021 ,stu_rec.last_update_login
3022 ,stu_rec.created_by
3023 ,stu_rec.creation_date
3024 ,stu_rec.optimizer_hint
3025 );
3026 EXCEPTION WHEN OTHERS THEN
3027 hr_legislation.hrrunprc_trace_on;
3028 hr_utility.trace('ins ff_routes');
3029 hr_utility.trace('route_name ' ||
3030 stu_rec.c_true_key);
3031 hr_utility.trace('route_id ' ||
3032 to_char(stu_rec.c_surrogate_key));
3033 hr_legislation.hrrunprc_trace_off;
3034 raise;
3035 END;
3036
3037 END IF;
3038
3039 END insert_route;
3040
3041 PROCEDURE transfer_row
3042 ----------------------
3043 IS
3044 -- Procedure to transfer a route from the delivery tables
3045 cursor c_ff_contexts_null (c_context_id in number) is
3046 select null
3047 from ff_contexts
3048 where context_id = c_context_id;
3049 --
3050 cursor c_ffrp_null (c_route_parameter_id in number) is
3051 select null
3052 from ff_route_parameters
3053 where route_parameter_id = c_route_parameter_id;
3054 --
3055 cursor c_ff_rcu_pop (c_route_id in number,
3056 c_sequence_no in number,
3057 c_context_id in number) is
3058 select distinct null
3059 from ff_route_context_usages
3060 where route_id = c_route_id
3061 and sequence_no = c_sequence_no
3062 and context_id = c_context_id;
3063 --
3064 BEGIN
3065 --
3066 IF p_phase = 1 THEN
3067 IF route_changed(l_new_surrogate_key, l_new_route) THEN
3068 IF NOT valid_to_insert THEN
3069 return;
3070 END IF;
3071 ELSE
3072 -- Route has not changed, check user entities
3073
3074 IF install_user_entity(l_new_surrogate_key) THEN
3075 null;
3076 ELSE
3077 -- No user entities to install
3078 remove(l_new_surrogate_key);
3079 return;
3080 END IF;
3081 END IF;
3082 ELSE
3083 -- Phase = 2
3084 IF route_changed(stu_rec.c_surrogate_key, l_new_route) THEN
3085 --
3086 IF NOT valid_to_insert THEN
3087 return;
3088 END IF;
3089 delete_route_form_usage;
3090 insert_route;
3091 --
3092 -- Ensure we rebuild all balance user entities and associated
3093 -- items in rebuild ele input bal for a changed route
3094 --
3095 IF NOT l_new_route THEN
3096 delete from ff_user_entities
3097 where creator_type in ('B', 'RB')
3098 and route_id = stu_rec.c_surrogate_key;
3099 END IF;
3100 --
3101 -- Changing or inserting route so delete live ctx usages
3102 -- for this route
3103 --
3104 delete ff_route_context_usages
3105 where route_id = stu_rec.c_surrogate_key;
3106 --
3107 -- Now install route ctx usages
3108 --
3109 FOR context_usages IN usage(stu_rec.c_surrogate_key) LOOP
3110
3111 BEGIN
3112 --
3113 open c_ff_contexts_null (context_usages.context_id);
3114 fetch c_ff_contexts_null into l_null_return;
3115 IF c_ff_contexts_null%NOTFOUND OR c_ff_contexts_null%NOTFOUND IS NULL THEN
3116 close c_ff_contexts_null;
3117 RAISE NO_DATA_FOUND;
3118 END IF;
3119 close c_ff_contexts_null;
3120 --
3121 BEGIN
3122 insert into ff_route_context_usages
3123 (route_id
3124 ,context_id
3125 ,sequence_no
3126 )
3127 values
3128 (context_usages.route_id
3129 ,context_usages.context_id
3130 ,context_usages.sequence_no
3131 );
3132 EXCEPTION WHEN OTHERS THEN
3133 hr_legislation.hrrunprc_trace_on;
3134 hr_utility.trace('ins ff_route_context_usages');
3135 hr_utility.trace('route_id ' ||
3136 to_char(context_usages.route_id));
3137 hr_utility.trace('route_name ' ||
3138 stu_rec.c_true_key);
3139 hr_utility.trace('context_id ' ||
3140 to_char(context_usages.context_id));
3141 hr_utility.trace('sequence_no ' ||
3142 to_char(context_usages.sequence_no));
3146
3143 hr_legislation.hrrunprc_trace_off;
3144 raise;
3145 END;
3147 EXCEPTION WHEN NO_DATA_FOUND THEN
3148
3149 crt_exc('Parent context not installed');
3150 return;
3151 END;
3152 END LOOP;
3153
3154 FOR r_hrsrp in parameter (stu_rec.c_surrogate_key) LOOP
3155
3156 BEGIN
3157
3158 open c_ffrp_null (r_hrsrp.ROUTE_PARAMETER_ID);
3159 fetch c_ffrp_null into l_null_return;
3160 IF c_ffrp_null%NOTFOUND OR c_ffrp_null%NOTFOUND IS NULL THEN
3161 close c_ffrp_null;
3162 RAISE NO_DATA_FOUND;
3163 END IF;
3164 close c_ffrp_null;
3165
3166 update ff_route_parameters
3167 set ROUTE_ID = stu_rec.c_surrogate_key
3168 ,DATA_TYPE = r_hrsrp.DATA_TYPE
3169 ,PARAMETER_NAME = r_hrsrp.PARAMETER_NAME
3170 ,SEQUENCE_NO = r_hrsrp.SEQUENCE_NO
3171 where ROUTE_PARAMETER_ID = r_hrsrp.ROUTE_PARAMETER_ID;
3172
3173 EXCEPTION WHEN NO_DATA_FOUND THEN
3174
3175 BEGIN
3176 insert into ff_route_parameters
3177 (ROUTE_PARAMETER_ID
3178 ,ROUTE_ID
3179 ,DATA_TYPE
3180 ,PARAMETER_NAME
3181 ,SEQUENCE_NO
3182 )
3183 values
3184 (r_hrsrp.ROUTE_PARAMETER_ID,
3185 stu_rec.c_surrogate_key,
3186 r_hrsrp.DATA_TYPE,
3187 r_hrsrp.PARAMETER_NAME,
3188 r_hrsrp.SEQUENCE_NO);
3189 EXCEPTION WHEN OTHERS THEN
3190 hr_legislation.hrrunprc_trace_on;
3191 hr_utility.trace('ins ff_route_parameters');
3192 hr_utility.trace('PARAMETER_NAME ' ||
3193 r_hrsrp.PARAMETER_NAME);
3194 hr_utility.trace('SEQUENCE_NO ' ||
3195 to_char(r_hrsrp.SEQUENCE_NO));
3196 hr_utility.trace('ROUTE_ID ' ||
3197 to_char(stu_rec.c_surrogate_key));
3198 hr_utility.trace('route_name ' ||
3199 stu_rec.c_true_key);
3200 hr_utility.trace('ROUTE_PARAMETER_ID ' ||
3201 to_char(r_hrsrp.ROUTE_PARAMETER_ID));
3202 hr_legislation.hrrunprc_trace_off;
3203 raise;
3204 END;
3205
3206 END;
3207
3208 END LOOP;
3209
3210 END IF;
3211 --
3212 IF NOT install_user_entity(stu_rec.c_surrogate_key) THEN
3213 return;
3214 END IF;
3215 remove(stu_rec.c_surrogate_key);
3216 END IF;
3217
3218 END transfer_row;
3219
3220 BEGIN
3221 -- This is the main loop to perform the installation logic. A cursor
3222 -- is opened to control the loop, and each row returned is placed
3223 -- into a record defined within the main procedure so each sub
3224 -- procedure has full access to all returrned columns. For each
3225 -- new row returned, a new savepoint is declared. If at any time
3226 -- the row is in error a rollback iss performed to the savepoint
3227 -- and the next row is returned. Ownership details are checked and
3228 -- if the row is required then the surrogate id is updated and the
3229 -- main transfer logic is called.
3230
3231 -- Need to disable the UE constraint so we can update user entities
3232 -- outside of the main transfer process so we dont lose our savepoints
3233 -- via implicit commits
3234
3235 -- disable_ffuebru_trig;
3236
3237 IF p_phase = 1 THEN
3238 check_id_conflicts; --attempt to detect and remove any possible
3239 --conflicts with surrogate id's that are being
3240 --delivered.
3241
3242 END IF;
3243
3244 FOR delivered IN stu LOOP
3245
3246 -- This uses main cursor stu to impilicity define a record
3247
3248
3249 savepoint new_route_name;
3250
3251 stu_rec := delivered;
3252
3253 IF p_phase = 2 THEN
3254 l_new_surrogate_key := stu_rec.c_surrogate_key;
3255 END IF;
3256
3257 IF valid_ownership THEN
3258
3259 -- Test the row onerships for the current row
3260
3261
3262 IF p_phase = 1 THEN
3263 update_uid;
3264 END IF;
3265
3266 transfer_row;
3267
3268 END IF;
3269
3270 END LOOP delivered;
3271
3272 -- enable_ffuebru_trig;
3273
3274 END install_routes;
3275
3276
3277 --****************************************************************************
3278 -- INSTALLATION PROCEDURE FOR : FF_FUNCTIONS
3279 --****************************************************************************
3280
3281 PROCEDURE install_functions(p_phase IN number)
3282 ----------------------------------------------
3283 IS
3287 -- The installation of functions is controlled by a main cursor which
3284 -- Install procedure to transfer startup element classifications into
3285 -- a live account.
3286
3288 -- selects distinct function names from the startup tables.
3289
3290 -- For each of these function names if the installed functions differ to the
3291 -- delivered ones, all installed functions of this name will refreshed.
3292
3293 l_null_return varchar2(1); -- For 'select null' statements
3294 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3295
3296 CURSOR stu -- Selects all rows from startup entity
3297 IS
3298 select distinct name
3299 , legislation_code c_leg_code
3300 from hr_s_functions;
3301
3302 CURSOR distinct_function(f_name IN varchar2, c_leg_code IN varchar2)
3303 IS
3304 -- Cursor to select distinct functions
3305
3306 select *
3307 from hr_s_functions
3308 where name = f_name
3309 and nvl(legislation_code, 'X') = nvl(c_leg_code, 'X');
3310
3311 CURSOR usages(f_id IN number)
3312 IS
3313 -- Cursor to install child context usages
3314
3315 select *
3316 from hr_s_function_context_usages
3317 where function_id = f_id;
3318
3319 stu_rec stu%ROWTYPE;
3320
3321 PROCEDURE crt_exc (exception_type IN varchar2)
3322 ----------------------------------------------
3323 IS
3324 -- Reports any exceptions during the delivery of startup data to
3325 -- FF_FUNCTIONS
3326
3327 BEGIN
3328 -- When the installation procedures encounter an error that cannot
3329 -- be handled, an exception is raised and all work is rolled back
3330 -- to the last savepoint. The installation process then continues
3331 -- with the next primary key to install. The same exception will
3332 -- not be raised more than once.
3333
3334 -- The exception text is set to a composite value because this exception
3335 -- is raised against a function name, not a function id. Consequently
3336 -- the surrogate id is set to a value of 0.
3337
3338
3339 rollback to new_function_name;
3340
3341 hr_legislation.insert_hr_stu_exceptions('ff_functions'
3342 , 0
3343 , exception_type
3344 , stu_rec.name);
3345
3346
3347 END crt_exc;
3348
3349 PROCEDURE remove(target varchar2)
3350 ---------------------------------
3351 IS
3352 -- Remove a row from either the startup tables or the installed tables
3353
3354 BEGIN
3355 -- This procedure either deletes from the delivered account,
3356 -- parameter of 'D', or from the live account, parameter of 'I'.
3357
3358 -- If the delivered details are being deleted the explicit deletes
3359 -- from all child tables are required, since the cascade constraint
3360 -- will not be delivered with these tables.
3361
3362 -- When deleting from the live account, the cascade delete can
3363 -- be relied upon.
3364
3365 IF target = 'D' THEN
3366
3367 delete from hr_s_function_context_usages a
3368 where exists
3369 (select null
3370 from hr_s_functions b
3371 where b.function_id = a.function_id
3372 and b.name = stu_rec.name
3373 and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
3374 );
3375
3376 delete from hr_s_function_parameters a
3377 where exists
3378 (select null
3379 from hr_s_functions b
3380 where b.function_id = a.function_id
3381 and b.name = stu_rec.name
3382 and nvl(b.legislation_code,'X')=nvl(stu_rec.c_leg_code,'X')
3383 );
3384
3385
3386 delete from hr_s_functions
3387 where name = stu_rec.name
3388 and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
3389
3390 ELSE
3391
3392 -- Delete from live account using the cascade delete
3393
3394
3395 delete from ff_functions
3396 where name = stu_rec.name
3397 and nvl(legislation_code,'X')=nvl(stu_rec.c_leg_code,'X');
3398
3399 END IF;
3400
3401 END remove;
3402
3403 PROCEDURE insert_delivered
3404 --------------------------
3405 IS
3406 -- Check if a delivered row is needed and insert into the
3407 -- live tables if it is
3408
3409 v_inst_update date; -- Holds update details of installed row
3410
3411 BEGIN
3412
3413
3414 BEGIN
3415
3416 -- Perform a check to see if the primary key has been created within
3417 -- a visible business group. Ie: the business group is for the same
3418 -- legislation as the delivered row, or the delivered row has a null
3419 -- legislation. If no rows are returned then the primary key has not
3420 -- already been created by a user.
3421
3422 select distinct null
3423 into l_null_return
3424 from ff_functions a
3425 where a.name = stu_rec.name
3426 and a.business_group_id is not null
3427 and exists (select null from per_business_groups b
3428 where b.business_group_id = a.business_group_id
3432
3429 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
3430
3431 crt_exc('Row already created in a business group');
3433 -- Indicate this row is not to be transferred
3434
3435 return;
3436
3437 EXCEPTION WHEN NO_DATA_FOUND THEN
3438
3439 null;
3440
3441 END;
3442
3443
3444 -- Now perform a check to see if this primary key has been installed
3445 -- with a legislation code that would make it visible at the same time
3446 -- as this row. Ie: if any legislation code is null within the set of
3447 -- returned rows, then the transfer may not go ahead. If no rows are
3448 -- returned then the delivered row is fine.
3449
3450 BEGIN
3451 select distinct null
3452 into l_null_return
3453 from ff_functions
3454 where name = stu_rec.name
3455 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
3456 and (
3457 legislation_code is null
3458 or stu_rec.c_leg_code is null
3459 );
3460
3461 crt_exc('Row already created for a visible legislation');
3462
3463 -- Indicates this row is not to be transferred
3464
3465 return;
3466
3467 EXCEPTION WHEN NO_DATA_FOUND THEN
3468
3469 null;
3470
3471 END;
3472
3473
3474 -- This procedure is only called in phase 2. All matching live
3475 -- functions will be be deleted and replaced with the delivered
3476 -- rows.
3477
3478 -- The routine check_parents validates foreign key references and
3479 -- ensures referential integrity. The routine checks to see if the
3480 -- parents of a given row have been transfered to the live tables.
3481
3482 -- This may only be called in phase two since in phase one all
3483 -- parent rows will remain in the delivery tables.
3484
3485 -- After the above checks only data that has been chanegd or is new
3486 -- will be left in the delivery tables.
3487
3488 -- The last step of this transfer, in phase 2, is to delete the now
3489 -- transfered row from the delivery tables.
3490
3491 -- Before the update/insert goes ahead, ensure all child rows
3492 -- are removed so the refrsh of child rows is simple.
3493
3494 remove('I');
3495
3496 FOR each_func IN distinct_function(stu_rec.name, stu_rec.c_leg_code) LOOP
3497
3498
3499 select ff_functions_s.nextval
3500 into l_new_surrogate_key
3501 from dual;
3502
3503
3504 BEGIN
3505 insert into ff_functions
3506 (function_id
3507 ,business_group_id
3508 ,legislation_code
3509 ,class
3510 ,name
3511 ,alias_name
3512 ,data_type
3513 ,definition
3514 ,last_update_date
3515 ,last_updated_by
3516 ,last_update_login
3517 ,created_by
3518 ,creation_date
3519 )
3520 values (l_new_surrogate_key
3521 ,null
3522 ,each_func.legislation_code
3523 ,each_func.class
3524 ,each_func.name
3525 ,each_func.alias_name
3526 ,each_func.data_type
3527 ,each_func.definition
3528 ,each_func.last_update_date
3529 ,each_func.last_updated_by
3530 ,each_func.last_update_login
3531 ,each_func.created_by
3532 ,each_func.creation_date
3533 );
3534
3535
3536 EXCEPTION WHEN OTHERS THEN
3537 hr_legislation.hrrunprc_trace_on;
3538 hr_utility.trace('ins ff_functions');
3539 hr_utility.trace('function name ' ||
3540 each_func.name);
3541 hr_utility.trace('function_id ' ||
3542 to_char(l_new_surrogate_key));
3543 hr_utility.trace(':lc: ' || ':' ||
3544 each_func.legislation_code || ':');
3545 hr_legislation.hrrunprc_trace_off;
3546 raise;
3547 END;
3548
3549 BEGIN
3550 insert into ff_function_parameters
3551 (function_id
3552 ,sequence_number
3553 ,class
3554 ,continuing_parameter
3555 ,data_type
3556 ,name
3557 ,optional
3558 )
3559 select l_new_surrogate_key
3560 , sequence_number
3561 , class
3562 , continuing_parameter
3563 , data_type
3564 , name
3565 , optional
3566 from hr_s_function_parameters
3567 where function_id = each_func.function_id;
3568 EXCEPTION WHEN OTHERS THEN
3569 hr_legislation.hrrunprc_trace_on;
3570 hr_utility.trace('ins ff_function_parameters');
3571 hr_utility.trace('function_id ' ||
3572 to_char(each_func.function_id));
3573 hr_utility.trace('function name ' ||
3574 each_func.name);
3578 raise;
3575 hr_utility.trace(':lc: ' ||
3576 each_func.legislation_code || ':');
3577 hr_legislation.hrrunprc_trace_off;
3579 END;
3580
3581 FOR child_usages IN usages(each_func.function_id) LOOP
3582
3583 BEGIN
3584
3585
3586 select null
3587 into l_null_return
3588 from ff_contexts
3589 where context_id = child_usages.context_id;
3590
3591 insert into ff_function_context_usages
3592 (function_id
3593 ,sequence_number
3594 ,context_id
3595 )
3596 values
3597 (l_new_surrogate_key
3598 ,child_usages.sequence_number
3599 ,child_usages.context_id
3600 );
3601
3602 EXCEPTION WHEN NO_DATA_FOUND THEN
3603
3604
3605 crt_exc('Context referenced by child usage is not present');
3606
3607 return;
3608
3609 WHEN OTHERS THEN
3610 hr_legislation.hrrunprc_trace_on;
3611 hr_utility.trace('ins ff_function_context_usages');
3612 hr_utility.trace('function_id ' ||
3613 to_char(l_new_surrogate_key));
3614 hr_utility.trace('function name ' ||
3615 each_func.name);
3616 hr_utility.trace(':lc: ' ||
3617 each_func.legislation_code || ':');
3618 hr_utility.trace('sequence_number ' ||
3619 to_char(child_usages.sequence_number));
3620 hr_utility.trace('context_id ' ||
3621 to_char(child_usages.context_id));
3622 hr_legislation.hrrunprc_trace_off;
3623 raise;
3624
3625 END;
3626
3627 END LOOP child_usages;
3628
3629 END LOOP each_func;
3630
3631
3632 remove('D');
3633
3634 END insert_delivered;
3635
3636 BEGIN
3637 -- This is the main loop to perform the installation logic. A cursor
3638 -- is opened to control the loop, and each row returned is placed
3639 -- into a record defined within the main procedure so each sub
3640 -- procedure has full access to all returrned columns. For each
3641 -- new row returned, a new savepoint is declared. If at any time
3642 -- the row is in error a rollback is performed to the savepoint
3643 -- and the next row is returned.
3644
3645 -- In phase 1 the only logic is to check if the function needs to be
3646 -- installed. If the function needs to be installed, it will be left
3647 -- in the delivery tables. If not then it will be deleted.
3648
3649 -- The surrogate id will be created/set in phase 2.
3650
3651 -- In phase 2 all installed functions of the name stu_rec.name will be
3652 -- deleted from the live account. All delivered functions/usages/parameters
3653 -- will be then inserted. At this point a new function id will be allocated.
3654
3655 FOR delivered IN stu LOOP
3656
3657 -- Uses main cursor stu to impilicity define a record
3658
3659
3660 savepoint new_function_name;
3661
3662 stu_rec := delivered;
3663
3664
3665 IF p_phase = 2 THEN
3666 insert_delivered;
3667 END IF;
3668
3669 END LOOP;
3670
3671 END install_functions;
3672
3673 --****************************************************************************
3674 -- INSTALLATION PROCEDURE FOR : FF_QP_REPORTS
3675 --****************************************************************************
3676
3677 PROCEDURE install_qpreports(p_phase IN number)
3678 ----------------------------------------------
3679 IS
3680 -- Install procedure to transfer startup QuickPaint reports into
3681 -- a live account.
3682
3683 l_null_return varchar2(1); -- For 'select null' statements
3684 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
3685
3686 CURSOR stu -- Selects all rows from startup entity
3687 IS
3688
3689 select qp_report_id c_surrogate_key
3690 , formula_id
3691 , formula_type_id
3692 , qp_report_name c_true_key
3693 , business_group_id
3694 , legislation_code c_leg_code
3695 , qp_altered_formula
3696 , qp_description
3697 , qp_text
3698 , last_update_date
3699 , last_updated_by
3700 , last_update_login
3701 , created_by
3702 , creation_date
3703 , rowid
3704 from hr_s_qp_reports;
3705
3706 stu_rec stu%ROWTYPE;
3707
3708 PROCEDURE crt_exc(exception_type IN varchar2)
3709 ---------------------------------------------
3710 IS
3711 -- If an exception has been detected meaning that the delivered row may
3712 -- not be installed, then it must be reported
3713
3714 BEGIN
3715
3716 -- When the installation procedures encounter an error that cannot
3717 -- be handled, an exception is raised and all work is rolled back
3718 -- to the last savepoint. The installation process then continues
3722
3719 -- with the next primary key to install. The same exception will
3720 -- not be raised more than once.
3721
3723 rollback to new_qp_report_name;
3724
3725 hr_legislation.insert_hr_stu_exceptions('ff_qp_reports'
3726 , stu_rec.c_surrogate_key
3727 , exception_type
3728 , stu_rec.c_true_key);
3729
3730
3731 END crt_exc;
3732
3733 PROCEDURE check_next_sequence
3734 -----------------------------
3735 IS
3736
3737 v_sequence_number number(9);
3738 v_min_delivered number(9);
3739 v_max_delivered number(9);
3740
3741 -- Surrogate id conflicts may arise from two scenario's:
3742 -- 1. Where the newly select sequence value conflicts with values
3743 -- in the STU tables.
3744 -- 2. Where selected surrogate keys, from the installed tables,
3745 -- conflict with other rows in the STU tables.
3746 --
3747 -- Both of the above scenario's are tested for.
3748 -- The first is a simple match, where if a value is detected in the
3749 -- STU tables and the installed tables then a conflict is detected. In
3750 -- This instance all STU surrogate keys, for this table, are updated.
3751 -- The second is tested for using the sequences.
3752 -- If the next value from the live sequence is within the range of
3753 -- delivered surrogate id's then the live sequence must be incremented.
3754 -- If no action is taken, then duplicates may be introduced into the
3755 -- delivered tables, and child rows may be totally invalidated.
3756
3757 BEGIN
3758
3759
3760 BEGIN --check that the installed id's will not conflict
3761 --with the delivered values
3762
3763
3764 select distinct null
3765 into l_null_return
3766 from ff_qp_reports a
3767 where exists
3768 (select null
3769 from hr_s_qp_reports b
3770 where a.qp_report_id = b.qp_report_id
3771 );
3772
3773 --conflict may exist
3774 --update all qp_report_id's to remove conflict
3775
3776 update hr_s_qp_reports
3777 set qp_report_id = qp_report_id - 50000000;
3778
3779 update hr_s_application_ownerships
3780 set key_value = key_value - 50000000
3781 where key_name = 'QP_REPORT_ID';
3782
3783 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
3784
3785 END; --check of qp_report_id
3786
3787
3788 select min(qp_report_id) - (count(*) *3)
3789 , max(qp_report_id) + (count(*) *3)
3790 into v_min_delivered
3791 , v_max_delivered
3792 from hr_s_qp_reports;
3793
3794 select ff_qp_reports_s.nextval
3795 into v_sequence_number
3796 from dual;
3797
3798 IF v_sequence_number
3799 BETWEEN v_min_delivered AND v_max_delivered THEN
3800
3801 hr_legislation.munge_sequence('FF_QP_REPORTS_S',
3802 v_sequence_number,
3803 v_max_delivered);
3804
3805 END IF;
3806
3807 END check_next_sequence;
3808
3809 PROCEDURE update_uid
3810 --------------------
3811 IS
3812 -- Update surrogate UID and all occurrences in child rows
3813
3814 BEGIN
3815
3816
3817 BEGIN
3818
3819 select distinct qp_report_id
3820 into l_new_surrogate_key
3821 from ff_qp_reports
3822 where qp_report_name = stu_rec.c_true_key
3823 and business_group_id is null
3824 and ( (legislation_code is null and stu_rec.c_leg_code is null)
3825 or (legislation_code = stu_rec.c_leg_code) );
3826
3827 EXCEPTION WHEN NO_DATA_FOUND THEN
3828
3829 select ff_qp_reports_s.nextval
3830 into l_new_surrogate_key
3831 from dual;
3832
3833 END;
3834
3835 -- Update all child entities
3836
3837 update hr_s_qp_reports
3838 set qp_report_id = l_new_surrogate_key
3839 where qp_report_id = stu_rec.c_surrogate_key;
3840
3841 update hr_s_application_ownerships
3842 set key_value = to_char(l_new_surrogate_key)
3843 where key_value = to_char(stu_rec.c_surrogate_key)
3844 and key_name = 'QP_REPORT_ID';
3845
3846 END update_uid;
3847
3848 PROCEDURE remove
3849 ----------------
3850 IS
3851 -- Remove a row from either the startup tables or the installed tables
3852
3853 BEGIN
3854
3855 delete from hr_s_qp_reports
3856 where rowid = stu_rec.rowid;
3857
3858 END remove;
3859
3860 FUNCTION valid_ownership RETURN BOOLEAN
3861 ---------------------------------------
3862 IS
3863 -- Test ownership of this current row
3864
3865 -- This routine only operates in phase 1. Rows are present in the
3866 -- table hr_application_ownerships in the delivery account, which
3867 -- dictate which products a piece of data is used for. If the query
3868 -- returns a row then this data is required, and the function will
3869 -- return true. If no rows are returned and an exception is raised,
3870 -- then this row is not required and may be deleted from the delivery
3871 -- tables.
3872
3876 -- installation.
3873 -- If legislation code and subgroup code are included on the delivery
3874 -- tables, a check must be made to determine if the data is defined for
3875 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
3877
3878 -- A return code of TRUE indicates that thhe row is required.
3879
3880 -- The exception is raised within this procedure if no rows are returned
3881 -- in this select statement. If no rows are returned then one of the
3882 -- following is true:
3883 -- 1. No ownership parameters are defined.
3884 -- 2. The products, for which owning parameters are defined, are not
3885 -- installed with as status of 'I'.
3886 -- 3. The data is defined for a legislation subgroup that is not active.
3887
3888 BEGIN
3889
3890
3891 IF p_phase <> 1 THEN -- Only perform in phase 1
3892 return TRUE;
3893 END IF;
3894
3895
3896 -- If exception raised below then this row is not needed
3897 -- get rid of subgrp table, not even using it!
3898 select null
3899 into l_null_return
3900 from dual
3901 where exists
3902 (select null
3903 from hr_s_application_ownerships a
3904 , fnd_product_installations b
3905 , fnd_application c
3906 where a.key_name = 'QP_REPORT_ID'
3907 and a.key_value = stu_rec.c_surrogate_key
3908 and a.product_name = c.application_short_name
3909 and c.application_id = b.application_id
3910 and ((b.status = 'I' and c.application_short_name <> 'PQP')
3911 or
3912 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
3913
3914 -- Indicates row is required
3915
3916 return TRUE;
3917
3918 EXCEPTION WHEN NO_DATA_FOUND THEN
3919
3920 -- Row not needed for any installed product
3921
3922
3923 remove;
3924
3925 -- Indicate row not needed
3926
3927 return FALSE;
3928
3929 END valid_ownership;
3930
3931 FUNCTION check_parents RETURN BOOLEAN
3932 -------------------------------------
3933 IS
3934 -- Check if parent data is correct
3935
3936 -- This procedure is only called in phase 2. The logic to check if
3937 -- a given parental foriegn key exists is split into two parts for
3938 -- every foriegn key. The first select from the delivery tables.
3939
3940 -- If a row is founnd then the installation of the parent must have
3941 -- failed, and this installation must not go ahead. If no data is
3942 -- found, ie: an exception is raised, the installation is valid.
3943
3944 -- The second check looks for a row in the live tables. If no rows
3945 -- are returned then this installation is invalid, since this means
3946 -- that the parent referenced by this row is not present in the
3947 -- live tables.
3948
3949 -- The distinct is used in case the parent is date effective and many rows
3950 -- may be returned by the same parent id.
3951
3952 BEGIN
3953
3954 -- Start parent checking against formula types
3955
3956
3957 BEGIN
3958
3959 -- Checking the delivery account
3960
3961 select distinct null
3962 into l_null_return
3963 from hr_s_formula_types
3964 where formula_type_id = stu_rec.formula_type_id;
3965
3966 crt_exc('Parent formula type remains in delivery tables');
3967
3968 -- Parent row still in startup account
3969
3970 return FALSE;
3971
3972 EXCEPTION WHEN NO_DATA_FOUND THEN
3973
3974 null;
3975
3976 END;
3977
3978
3979 BEGIN
3980
3981 -- Checking the installed account
3982
3983 select null
3984 into l_null_return
3985 from ff_formula_types
3986 where formula_type_id = stu_rec.formula_type_id;
3987
3988 -- Drop down to second parent check
3989
3990 EXCEPTION WHEN NO_DATA_FOUND THEN
3991
3992
3993 crt_exc('Parent formula type not installed');
3994
3995 return FALSE;
3996
3997 END;
3998
3999 -- Start parent checking against formulas
4000
4001
4002 BEGIN
4003
4004 -- Checking the delivery account
4005
4006 select distinct null
4007 into l_null_return
4008 from hr_s_formulas_f
4009 where formula_id = stu_rec.formula_id;
4010
4011 crt_exc('Parent formula remains in delivery tables');
4012
4013 -- Parent row still in startup account
4014
4015 return FALSE;
4016
4017 EXCEPTION WHEN NO_DATA_FOUND THEN
4018
4019 null;
4020
4021 END;
4022
4023
4024 BEGIN
4025
4026 -- Checking the installed account
4027
4028 select distinct null
4029 into l_null_return
4030 from ff_formulas_f
4031 where formula_id = stu_rec.formula_id;
4032
4033 return TRUE;
4034
4035 EXCEPTION WHEN NO_DATA_FOUND THEN
4036
4037
4038 crt_exc('Parent formula not installed');
4039
4043
4040 return FALSE;
4041
4042 END;
4044 END check_parents;
4045
4046 PROCEDURE transfer_row
4047 ----------------------
4048 IS
4049 -- Check if a delivered row is needed and insert into the
4050 -- live tables if it is
4051
4052 v_inst_update date; -- Holds update details of installed row
4053
4054 BEGIN
4055
4056
4057 BEGIN
4058
4059 -- Perform a check to see if the primary key has been created
4060 -- within a visible business group. Ie: the business group
4061 -- is for the same legislation as the delivered row, or the
4062 -- delivered row has a null legislation. If no rows are
4063 -- returned then the primary key has not already been
4064 -- created by a user.
4065
4066 select distinct null
4067 into l_null_return
4068 from ff_qp_reports a
4069 where a.qp_report_name = stu_rec.c_true_key
4070 and a.business_group_id is not null
4071 and exists (select null from per_business_groups b
4072 where b.business_group_id = a.business_group_id
4073 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
4074
4075 crt_exc('Row already created in a business group');
4076
4077 -- Indicate this row is not to be transferred
4078
4079 return;
4080
4081 EXCEPTION WHEN NO_DATA_FOUND THEN
4082
4083 null;
4084
4085 END;
4086
4087
4088 -- Now perform a check to see if this primary key has been installed
4089 -- with a legislation code that would make it visible at the same time
4090 -- as this row. Ie: if any legislation code is null within the set of
4091 -- returned rows, then the transfer may not go ahead. If no rows are
4092 -- returned then the delivered row is fine.
4093
4094 BEGIN
4095
4096 select distinct null
4097 into l_null_return
4098 from ff_qp_reports
4099 where qp_report_name = stu_rec.c_true_key
4100 and nvl(legislation_code,'X') <> nvl(stu_rec.c_leg_code,'X')
4101 and (legislation_code is null
4102 or stu_rec.c_leg_code is null );
4103
4104 crt_exc('Row already created for a visible legislation');
4105
4106 -- Indicate this row is not to be transferred
4107
4108 return;
4109
4110 EXCEPTION WHEN NO_DATA_FOUND THEN
4111
4112 null;
4113
4114 END;
4115
4116
4117 -- When the procedure is called in phase 1, there is no need to
4118 -- actually perform the transfer from the delivery tables into the
4119 -- live. Hence if phase = 1 control is returned to the calling
4120 -- procedure and the next row is returned.
4121
4122 -- If the procedure is called in phase 2, then the live row is updated
4123 -- with the values on the delivered row.
4124
4125 -- The routine check_parents validates foreign key references and
4126 -- ensures referential integrity. The routine checks to see if the
4127 -- parents of a given row have been transfered to the live tables.
4128
4129 -- This may only be called in phase two since in phase one all
4130 -- parent rows will remain in the delivery tables.
4131
4132 -- After the above checks only data that has been chanegd or is new
4133 -- will be left in the delivery tables. At this stage if the row is
4134 -- already present then it must be updated to ensure referential
4135 -- integrity. Therefore an update will be performed and if SQL%FOUND
4136 -- is FALSE an insert will be performed.
4137
4138 -- The last step of the transfer, in phase 2, is to delete the now
4139 -- transfered row from the delivery tables.
4140
4141 IF p_phase = 1 THEN
4142 return;
4143 END IF;
4144
4145 IF NOT check_parents THEN
4146 -- Fails because parents exist
4147 return;
4148 END IF;
4149
4150
4151 update ff_qp_reports
4152 set formula_id = stu_rec.formula_id
4153 , formula_type_id = stu_rec.formula_type_id
4154 , qp_report_name = to_char(stu_rec.c_surrogate_key)
4155 , business_group_id = null
4156 , legislation_code = stu_rec.c_leg_code
4157 , qp_altered_formula = stu_rec.qp_altered_formula
4158 , qp_description = stu_rec.qp_description
4159 , qp_text = stu_rec.qp_text
4160 , last_update_date = stu_rec.last_update_date
4161 , last_updated_by = stu_rec.last_updated_by
4162 , last_update_login = stu_rec.last_update_login
4163 , created_by = stu_rec.created_by
4164 , creation_date = stu_rec.creation_date
4165 where qp_report_id = stu_rec.c_surrogate_key;
4166
4167 IF NOT SQL%FOUND THEN
4168
4169
4170 insert into ff_qp_reports
4171 (qp_report_id
4172 ,formula_id
4173 ,formula_type_id
4174 ,qp_report_name
4175 ,business_group_id
4176 ,legislation_code
4177 ,qp_altered_formula
4178 ,qp_description
4179 ,qp_text
4180 ,last_update_date
4181 ,last_updated_by
4182 ,last_update_login
4183 ,created_by
4184 ,creation_date
4185 )
4186 values
4190 ,stu_rec.c_true_key
4187 (stu_rec.c_surrogate_key
4188 ,stu_rec.formula_id
4189 ,stu_rec.formula_type_id
4191 ,null
4192 ,stu_rec.c_leg_code
4193 ,stu_rec.qp_altered_formula
4194 ,stu_rec.qp_description
4195 ,stu_rec.qp_text
4196 ,stu_rec.last_update_date
4197 ,stu_rec.last_updated_by
4198 ,stu_rec.last_update_login
4199 ,stu_rec.created_bY
4200 ,stu_rec.creation_date
4201 );
4202
4203 END IF;
4204
4205
4206 remove;
4207
4208 END transfer_row;
4209
4210 BEGIN
4211 -- This is the main loop to perform the installation logic. A cursor
4212 -- is opened to control the loop, and each row returned is placed
4213 -- into a record defined within the main procedure so each sub
4214 -- procedure has full access to all returrned columns. For each
4215 -- new row returned, a new savepoint is declared. If at any time
4216 -- the row is in error a rollback iss performed to the savepoint
4217 -- and the next row is returned. Ownership details are checked and
4218 -- if the row is required then the surrogate id is updated and the
4219 -- main transfer logic is called.
4220
4221 IF p_phase = 1 THEN
4222 check_next_sequence;
4223 END IF;
4224
4225 FOR delivered IN stu LOOP
4226
4227 -- Uses main cursor stu to impilicity define a record
4228
4229
4230 savepoint new_qp_report_name;
4231
4232 stu_rec := delivered;
4233
4234 IF p_phase = 2 THEN
4235 l_new_surrogate_key := stu_rec.c_surrogate_key;
4236 END IF;
4237
4238 IF valid_ownership THEN
4239
4240 -- Test the row ownerships for the current row
4241
4242
4243 IF p_phase = 1 THEN
4244 update_uid;
4245 END IF;
4246
4247 transfer_row;
4248
4249 END IF;
4250
4251 END LOOP;
4252
4253 END install_qpreports;
4254
4255 --****************************************************************************
4256 -- INSTALLATION PROCEDURE FOR : FF_GLOBALS_S
4257 --****************************************************************************
4258
4259 PROCEDURE install_globals(p_phase IN NUMBER)
4260 --------------------------------------------
4261 IS
4262 -- Install procedure to transfer startup delivered globals into a
4263 -- live account, and remove the then delivered rows from the delivery
4264 -- account.
4265
4266 -- This procedure is called in two phase. Only in the second phase are
4267 -- details transferred into live tables. The parameter p_phase holds
4268 -- the phase number.
4269
4270 row_in_error exception;
4271 l_current_proc varchar2(80) := 'hr_legislation.install_globals';
4272 l_new_global_id number(15);
4273 l_null_return varchar2(1);
4274 status varchar2(10);
4275
4276 CURSOR c_distinct
4277 IS
4278 -- Select statement used for the main loop. Each row return is used
4279 -- as the commit unit, since each true primary key may have many date
4280 -- effective rows for it.
4281
4282 -- The selected primary key is then passed into the second driving
4283 -- cursor statement as a parameter, and all date effective rows for
4284 -- this primary key are then selected.
4285
4286 select max(effective_end_date) c_end
4287 , global_id c_surrogate_key
4288 , global_name c_true_key
4289 , legislation_code
4290 from hr_s_globals_f
4291 group by global_id
4292 , global_name
4293 , legislation_code;
4294
4295 CURSOR c_each_row(pc_global_id varchar2)
4296 IS
4297 -- Selects all date effective rows for the current true primary key
4298 -- The primary key has already been selected using the above cursor.
4299 -- This cursor accepts the primary key as a parameter and selects all
4300 -- date effective rows for it.
4301
4302 select *
4303 from hr_s_globals_f
4304 where global_id = pc_global_id;
4305
4306 -- These records are defined here so all sub procedures may use the
4307 -- values selected. This saves the need for all sub procedures to have
4308 -- a myriad of parameters passed. The cursors are controlled in FOR
4309 -- cursor LOOPs. When a row is returned the whole record is copied into
4310 -- these record definitions.
4311
4312 CURSOR c_global_ad(p_global_id number)
4313 IS
4314 -- This cursor is used when deleting rows from ff_globals_f.
4315 -- FF_GLOBAL_F has an after delete trigger that removes user_entities
4316 -- that have a creator_id = the global_id of the row being removed.
4317 -- When deleting these UE, the BRD UE trigger potentially deletes
4318 -- database items. This trigger invalidates all formulae that can be
4319 -- affected by these dbi removals.
4320 select distinct ffu.formula_id fid
4321 from ff_fdi_usages_f ffu
4322 where ffu.item_name in (select fdbi.user_name
4323 from ff_database_items fdbi,
4324 ff_user_entities ffue
4325 where fdbi.user_entity_id = ffue.user_entity_id
4326 and ffue.creator_id = p_global_id
4327 and ffue.creator_type = 'S');
4328
4332 PROCEDURE remove (v_id IN number)
4329 r_distinct c_distinct%ROWTYPE;
4330 r_each_row c_each_row%ROWTYPE;
4331
4333 ---------------------------------
4334 IS
4335 -- Subprogram to delete a row from the delivery tables, and all child
4336 -- application ownership rows
4337
4338 BEGIN
4339
4340
4341 delete from hr_s_globals_f
4342 where global_id = v_id;
4343
4344 END remove;
4345
4346 PROCEDURE check_next_sequence
4347 -----------------------------
4348 IS
4349
4350 v_sequence_number number(9);
4351 v_min_delivered number(9);
4352 v_max_delivered number(9);
4353
4354 -- Surrogate id conflicts may arise from two scenario's:
4355 -- 1. Where the newly select sequence value conflicts with values
4356 -- in the STU tables.
4357 -- 2. Where selected surrogate keys, from the installed tables,
4358 -- conflict with other rows in the STU tables.
4359 --
4360 -- Both of the above scenario's are tested for.
4361 -- The first is a simple match, where if a value is detected in the
4362 -- STU tables and the installed tables then a conflict is detected. In
4363 -- This instance all STU surrogate keys, for this table, are updated.
4364 -- The second is tested for using the sequences.
4365 -- If the next value from the live sequence is within the range of
4366 -- delivered surrogate id's then the live sequence must be incremented.
4367 -- If no action is taken, then duplicates may be introduced into the
4368 -- delivered tables, and child rows may be totally invalidated.
4369
4370 BEGIN
4371
4372
4373 BEGIN --check that the installed id's will not conflict
4374 --with the delivered values
4375
4376
4377 select distinct null
4378 into l_null_return
4379 from ff_globals_f a
4380 where exists
4381 (select null
4382 from hr_s_globals_f b
4383 where a.global_id = b.global_id
4384 );
4385
4386 --conflict may exist
4387 --update all global_id's to remove conflict
4388
4389 update /*+NO_INDEX*/ hr_s_globals_f
4390 set global_id = global_id - 50000000;
4391
4392 update hr_s_application_ownerships
4393 set key_value = key_value - 50000000
4394 where key_name = 'GLOBAL_ID';
4395
4396 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4397
4398 END; --check of global_id
4399
4400
4401 select min(global_id) - (count(*) *3)
4402 , max(global_id) + (count(*) *3)
4403 into v_min_delivered
4404 , v_max_delivered
4405 from hr_s_globals_f;
4406
4407 select ff_globals_s.nextval
4408 into v_sequence_number
4409 from dual;
4410
4411 IF v_sequence_number
4412 BETWEEN v_min_delivered AND v_max_delivered THEN
4413
4414 hr_legislation.munge_sequence('FF_GLOBALS_S',
4415 v_sequence_number,
4416 v_max_delivered);
4417 END IF;
4418
4419 END check_next_sequence;
4420
4421 PROCEDURE update_uid
4422 --------------------
4423 IS
4424 -- Subprogram to update surrogate UID and all occurrences in child rows
4425
4426 BEGIN
4427
4428 -- See if this primary key is already installed. If so then the sorrogate
4429 -- key of the delivered row must be updated to the value in the installed
4430 -- tables. If the row is not already present then select the next value
4431 -- from the sequence. In either case all rows for this primary key must
4432 -- be updated, as must all child references to the old surrogate uid.
4433
4434
4435 BEGIN
4436
4437 select distinct global_id
4438 into l_new_global_id
4439 from ff_globals_f
4440 where global_name = r_distinct.c_true_key
4441 and business_Group_id is null
4442 and ((legislation_code is NULL and r_distinct.legislation_code is NULL)
4443 or (r_distinct.legislation_code=legislation_code));
4444
4445
4446 EXCEPTION WHEN NO_DATA_FOUND THEN
4447
4448
4449 select ff_globals_s.nextval
4450 into l_new_global_id
4451 from dual;
4452
4453 WHEN TOO_MANY_ROWS THEN
4454 hr_legislation.hrrunprc_trace_on;
4455 hr_utility.trace('sel ff_globals_f TMR');
4456 hr_utility.trace('global_name ' ||
4457 r_distinct.c_true_key);
4458 hr_utility.trace(':lc: ' || ':' ||
4459 r_distinct.legislation_code || ':');
4460 hr_legislation.hrrunprc_trace_off;
4461 raise;
4462 END;
4463
4464 update hr_s_globals_f
4465 set global_id = l_new_global_id
4466 where global_id = r_distinct.c_surrogate_key;
4467
4468 update hr_s_application_ownerships
4469 set key_value = to_char(l_new_global_id)
4470 where key_value = to_char(r_distinct.c_surrogate_key)
4471 and key_name = 'GLOBAL_ID';
4472
4473 END update_uid;
4474
4475 PROCEDURE crt_exc(exception_type IN varchar2)
4476 ---------------------------------------------
4480
4477 IS
4478 -- If an exception has been detected meaning that the delivered row may
4479 -- not be installed, then it must be reported
4481 BEGIN
4482
4483 -- When the installation procedures encounter an error that cannot
4484 -- be handled, an exception is raised and all work is rolled back
4485 -- to the last savepoint. The installation process then continues
4486 -- with the next primary key to install. The same exception will
4487 -- not be raised more than once.
4488
4489 rollback to new_global_name;
4490
4491 hr_legislation.insert_hr_stu_exceptions('ff_globals_f'
4492 , r_distinct.c_surrogate_key
4493 , exception_type
4494 , r_distinct.c_true_key);
4495
4496
4497 END crt_exc;
4498
4499 -- ----------------------------------
4500 -- ----------------------------------
4501 FUNCTION valid_ownership RETURN BOOLEAN
4502 ---------------------------------------
4503 IS
4504 -- Test ownership of this current row
4505
4506 -- This function is split into three distinct parts. The first
4507 -- checks to see if a row exists with the same primary key, for a
4508 -- business group that would have access to the delivered row. The
4509 -- second checks details for data created in other legislations,
4510 -- in case data is either created with a null legislation or the
4511 -- delivered row has a null legislation. The last check examines
4512 -- if this data is actually required for a given install by examining
4513 -- the product installation table, and the ownership details for
4514 -- this row.
4515
4516 -- A return code of TRUE indicates that the row is required.
4517 cursor get_ff_globals is
4518 select distinct null
4519 from ff_globals_f a
4520 where a.global_name = r_distinct.c_true_key
4521 and a.business_group_id is not null
4522 and exists (select null from per_business_groups b
4523 where b.business_group_id = a.business_group_id
4524 and b.legislation_code = nvl(r_distinct.legislation_code,b.legislation_code));
4525
4526 --
4527 cursor c_valid_ownership is
4528 select distinct null
4529 from ff_globals_f
4530 where global_name = r_distinct.c_true_key
4531 and nvl(legislation_code,'X')<>nvl(r_distinct.legislation_code,'X')
4532 and (legislation_code is null
4533 or r_distinct.legislation_code is null );
4534 --
4535 BEGIN
4536 BEGIN
4537 -- Perform a check to see if the primary key has been
4538 -- created within a visible business group. Ie: the
4539 -- business group is for the same legislation as the
4540 -- delivered row, or the delivered row has a null
4541 -- legislation. If no rows are returned then the primary
4542 -- key has not already been created by a user.
4543
4544 open get_ff_globals;
4545 fetch get_ff_globals into l_null_return;
4546 IF get_ff_globals%NOTFOUND OR get_ff_globals%NOTFOUND IS NULL THEN
4547 RAISE NO_DATA_FOUND;
4548 END IF;
4549 close get_ff_globals;
4550 --
4551 crt_exc('Row already created in a business group');
4552 -- Indicate this row is not to be transferred
4553 return FALSE;
4554
4555 EXCEPTION WHEN NO_DATA_FOUND THEN
4556 null;
4557 --
4558 END;
4559
4560 -- Now perform a check to see if this primary key has been installed
4561 -- with a legislation code that would make it visible at the same time
4562 -- as this row. Ie: if any legislation code is null within the set of
4563 -- returned rows, then the transfer may not go ahead. If no rows are
4564 -- returned then the delivered row is fine.
4565
4566 BEGIN
4567 --
4568 open c_valid_ownership;
4569 fetch c_valid_ownership into l_null_return;
4570 IF c_valid_ownership%NOTFOUND OR c_valid_ownership%NOTFOUND IS NULL THEN
4571 RAISE NO_DATA_FOUND;
4572 END IF;
4573 close c_valid_ownership;
4574 --
4575 crt_exc('Row already created for a visible legislation');
4576 return FALSE; --indicates this row is not to be transferred
4577
4578 EXCEPTION WHEN NO_DATA_FOUND THEN
4579 null;
4580 END;
4581
4582 -- The last check examines the product installation table, and the
4583 -- ownership details for the delivered row. By examining these
4584 -- tables the row is either deleted or not. If the delivered row
4585 -- is 'stamped' with a legislation subgroup, then a chweck must be
4586 -- made to see if that subgroup is active or not. This check only
4587 -- needs to be performed in phase 1, since once this decision is
4588 -- made, it is pontless to perform this logic again.
4589 -- in this select statement. If no rows are returned then one of the
4590 -- following is true:
4591 -- 1. No ownership parameters are defined.
4592 -- 2. The products, for which owning parameters are defined, are not
4593 -- installed with as status of 'I'.
4594 -- 3. The data is defined for a legislation subgroup that is not active.
4595
4596 IF p_phase <> 1 THEN return TRUE; END IF;
4597
4598
4602 where exists (select null from hr_s_application_ownerships a
4599 select null
4600 into l_null_return
4601 from dual
4603 , fnd_product_installations b
4604 , fnd_application c
4605 where a.key_name = 'GLOBAL_ID'
4606 and a.key_value = r_distinct.c_surrogate_key
4607 and a.product_name = c.application_short_name
4608 and c.application_id = b.application_id
4609 and ((b.status = 'I' and c.application_short_name <> 'PQP')
4610 or
4611 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
4612
4613
4614 -- Indicate row is required
4615
4616 return TRUE;
4617
4618 EXCEPTION WHEN NO_DATA_FOUND THEN
4619
4620 -- Row not needed for any installed product
4621
4622
4623 remove(r_distinct.c_surrogate_key);
4624
4625 -- Indicate row not needed
4626
4627 return FALSE;
4628
4629 END valid_ownership;
4630
4631 BEGIN
4632 -- Two loops are used here. The main loop which select distinct primary
4633 -- key rows and an inner loop which selects all date effective rows for the
4634 -- primary key. The inner loop is only required in phase two, since only
4635 -- in phase 2 are rows actually transferred. The logic reads as follows:
4636
4637 -- - Only deal with rows which have correct ownership details and will
4638 -- not cause integrity problems (valid_ownership).
4639
4640 -- - In Phase 1:
4641 -- - Delete delivery rows where the installed rows are identicle.
4642 -- - The UNION satement compares delivery rows to installed rows.
4643 -- If the sub query returns any rows, then the delivered
4644 -- tables and the installed tables are different.
4645 -- - In Phase 2:
4646 -- - Delete from the installed tables using the surrogate id.
4647 -- - If an installed row is to be replaced, the values of
4648 -- the surrogate keys will be identicle at this stage.
4649 -- - Data will then be deleted from the delivery tables.
4650 -- - Call the installation procedure for any child tables, that
4651 -- must be installed within the same commit unit. If any
4652 -- errors occur then rollback to the last declared savepoint.
4653 -- - Check that all integrity rules are still obeyed at the end
4654 -- of the installation (validity_checks).
4655
4656 -- An exception is used with this procedure 'row_in_error' in case an error
4657 -- is encountered from calling any function. If this is raised, then an
4658 -- exception is entered into the control tables (crt_exc();) and a rollback
4659 -- is performed.
4660
4661 -- disable dynamic cont calc trigger
4662 -- dis_cont_calc_trigger;
4663
4664 IF p_phase = 1 THEN
4665 check_next_sequence;
4666 END IF;
4667
4668 FOR primary_keys IN c_distinct LOOP
4669
4670 r_distinct := primary_keys;
4671
4672
4673 savepoint new_global_name;
4674
4675 BEGIN
4676
4677 IF valid_ownership THEN
4678
4682 IF p_phase = 1 THEN
4679 -- This row is wanted
4680
4681
4683
4684
4685 -- Get new surrogate id and update child references
4686
4687 update_uid;
4688
4689 delete from hr_s_globals_f
4690 where global_id = l_new_global_id
4691 and not exists
4692 ((select
4693 EFFECTIVE_START_DATE,
4694 EFFECTIVE_END_DATE,
4695 BUSINESS_GROUP_ID,
4696 LEGISLATION_CODE,
4697 DATA_TYPE,
4698 GLOBAL_NAME,
4699 GLOBAL_DESCRIPTION,
4700 GLOBAL_VALUE
4701 from hr_s_globals_f
4702 where global_id = l_new_global_id
4703 MINUS
4704 select
4705 EFFECTIVE_START_DATE,
4706 EFFECTIVE_END_DATE,
4707 BUSINESS_GROUP_ID,
4708 LEGISLATION_CODE,
4709 DATA_TYPE,
4710 GLOBAL_NAME,
4711 GLOBAL_DESCRIPTION,
4712 GLOBAL_VALUE
4713 from ff_globals_f
4714 where global_id = l_new_global_id
4715 )
4716 UNION
4717 (select
4718 EFFECTIVE_START_DATE,
4719 EFFECTIVE_END_DATE,
4720 BUSINESS_GROUP_ID,
4721 LEGISLATION_CODE,
4722 DATA_TYPE,
4723 GLOBAL_NAME,
4724 GLOBAL_DESCRIPTION,
4725 GLOBAL_VALUE
4726 from ff_globals_f
4727 where global_id = l_new_global_id
4728 MINUS
4729 select
4730 EFFECTIVE_START_DATE,
4731 EFFECTIVE_END_DATE,
4732 BUSINESS_GROUP_ID,
4733 LEGISLATION_CODE,
4734 DATA_TYPE,
4735 GLOBAL_NAME,
4736 GLOBAL_DESCRIPTION,
4737 GLOBAL_VALUE
4738 from hr_s_globals_f
4739 where global_id = l_new_global_id
4740 ))
4741 and exists (select distinct null
4742 from ff_user_entities u,
4743 ff_database_items d,
4744 ff_route_parameters rp,
4745 ff_route_parameter_values rpv
4746 where u.user_entity_name = global_name || '_GLOBAL_UE'
4747 and u.user_entity_id = rpv.user_entity_id
4748 and d.user_entity_id = u.user_entity_id
4749 and rpv.route_parameter_id = rp.route_parameter_id
4750 and rpv.value = to_char(l_new_global_id));
4751
4752 ELSE
4753
4754 -- Phase = 2
4755
4756
4757 for r_global in c_global_ad(r_distinct.c_surrogate_key)
4758 loop
4759 delete ff_fdi_usages_f where formula_id = r_global.fid;
4760 delete ff_compiled_info_f where formula_id = r_global.fid;
4761 end loop;
4762
4763 -- Delete ff_route_parameter_values
4764 -- associated with this global bug 3744555
4765 -- and let them get recreated by the global triggers
4766 delete ff_route_parameter_values
4767 where user_entity_id = (select user_entity_id
4768 from ff_user_entities
4769 where user_entity_name = r_distinct.c_true_key || '_GLOBAL_UE');
4770
4771 delete from ff_globals_f
4772 where global_id = r_distinct.c_surrogate_key;
4773
4774 FOR each_row IN c_each_row(r_distinct.c_surrogate_key)
4775 LOOP
4776
4777 r_each_row := each_row;
4778
4779
4780 BEGIN
4781 insert into ff_globals_f
4782 (GLOBAL_ID
4783 ,EFFECTIVE_START_DATE
4784 ,EFFECTIVE_END_DATE
4785 ,BUSINESS_GROUP_ID
4786 ,LEGISLATION_CODE
4787 ,DATA_TYPE
4788 ,GLOBAL_NAME
4789 ,GLOBAL_DESCRIPTION
4790 ,GLOBAL_VALUE
4791 ,LAST_UPDATE_DATE
4792 ,LAST_UPDATED_BY
4793 ,LAST_UPDATE_LOGIN
4794 ,CREATED_BY
4795 ,CREATION_DATE)
4796 values
4797 (r_each_row.GLOBAL_ID
4798 ,r_each_row.EFFECTIVE_START_DATE
4799 ,r_each_row.EFFECTIVE_END_DATE
4800 ,r_each_row.BUSINESS_GROUP_ID
4801 ,r_each_row.LEGISLATION_CODE
4802 ,r_each_row.DATA_TYPE
4803 ,r_each_row.GLOBAL_NAME
4804 ,r_each_row.GLOBAL_DESCRIPTION
4805 ,r_each_row.GLOBAL_VALUE
4806 ,r_each_row.LAST_UPDATE_DATE
4810 ,r_each_row.CREATION_DATE);
4807 ,r_each_row.LAST_UPDATED_BY
4808 ,r_each_row.LAST_UPDATE_LOGIN
4809 ,r_each_row.CREATED_BY
4811
4812 EXCEPTION WHEN OTHERS THEN
4813 hr_legislation.hrrunprc_trace_on;
4814 hr_utility.trace('ins ff_globals_f');
4815 hr_utility.trace('GLOBAL_NAME ' ||
4816 r_each_row.GLOBAL_NAME);
4820 r_each_row.GLOBAL_VALUE);
4817 hr_utility.trace('GLOBAL_ID ' ||
4818 to_char(r_each_row.GLOBAL_ID));
4819 hr_utility.trace('GLOBAL_VALUE ' ||
4821 hr_utility.trace(':lc: ' || ':' ||
4822 r_each_row.legislation_code || ':');
4823 hr_legislation.hrrunprc_trace_off;
4824 raise;
4825 END;
4826 END LOOP each_row;
4827
4828 remove(r_distinct.c_surrogate_key);
4829
4830 END IF;
4831
4832 END IF;
4833
4834 EXCEPTION WHEN row_in_error THEN
4835 rollback to new_global_name;
4836 END;
4837
4838 END LOOP primary_keys;
4839
4840 -- ena_cont_calc_trigger;
4841
4842 END install_globals;
4843
4844 --****************************************************************************
4845 -- INSTALLATION PROCEDURE FOR ALL FF DELIVERY
4846 --****************************************************************************
4847
4848 PROCEDURE install (p_phase number)
4849 ----------------------------------
4850 IS
4851 -- Driver procedure to execute all formula installation procedures.
4852 core_selected NUMBER;
4853
4854 BEGIN
4855 hr_legislation.hrrunprc_trace_on;
4856 hr_utility.trace('start ff_data_dict.install: ' || to_char(p_phase));
4857 hr_legislation.hrrunprc_trace_off;
4858
4859 IF p_phase <> 1 and p_phase <>2 THEN
4860 return;
4861 END IF;
4862
4863 IF p_phase = 2 THEN
4864
4865 select count(*)
4866 into core_selected
4867 from hr_legislation_installations
4868 where legislation_code is null
4869 and action in ('I', 'U', 'F');
4870
4871 END IF;
4872 hr_legislation.hrrunprc_trace_on;
4873 hr_utility.trace('start install_ffc: ' || to_char(p_phase));
4874 hr_legislation.hrrunprc_trace_off;
4875
4876 install_ffc(p_phase); --install ff_contexts
4877 hr_legislation.hrrunprc_trace_on;
4878 hr_utility.trace('start install_fft: ' || to_char(p_phase));
4879 hr_legislation.hrrunprc_trace_off;
4880
4881 install_fft(p_phase); --install ff_Formula_types
4882
4883 hr_legislation.hrrunprc_trace_on;
4884 hr_utility.trace('start install_formulas: ' || to_char(p_phase));
4885 hr_legislation.hrrunprc_trace_off;
4886
4887 install_formulas(p_phase); --install formulas
4888
4889 hr_legislation.hrrunprc_trace_on;
4890 hr_utility.trace('start install_routes: ' || to_char(p_phase));
4891 hr_legislation.hrrunprc_trace_off;
4892
4893 install_routes(p_phase); --install routes,entities,db items
4894
4895 hr_legislation.hrrunprc_trace_on;
4896 hr_utility.trace('start install_functions: ' || to_char(p_phase));
4897 hr_legislation.hrrunprc_trace_off;
4898
4899 install_functions(p_phase); --install functions, context usages etc
4900
4901 hr_legislation.hrrunprc_trace_on;
4902 hr_utility.trace('start install_qpreports: ' || to_char(p_phase));
4903 hr_legislation.hrrunprc_trace_off;
4904
4905 install_qpreports(p_phase); --install quickpaint reports
4906
4907 hr_legislation.hrrunprc_trace_on;
4908 hr_utility.trace('start install_globals: ' || to_char(p_phase));
4909 hr_legislation.hrrunprc_trace_off;
4910
4911 install_globals(p_phase); --install globals
4912
4913 hr_legislation.hrrunprc_trace_on;
4914 hr_utility.trace('exit ff_data_dict.install: ' || to_char(p_phase));
4915 hr_legislation.hrrunprc_trace_off;
4916
4917 END install;
4918
4919 END ff_data_dict;
[Home] [Help]