1 PACKAGE body hr_legislation_benefits AS
2 /* $Header: pelegben.pkb 120.2.12000000.1 2007/01/21 23:59:22 appldev ship $ */
3 --
4 -- Copyright (c) Oracle Corporation 1991, 1992, 1993. All rights reserved.
5 --
6 --
7 -- NAME
8 -- pelegben.pkb
9 --
10 -- DESCRIPTION
11 -- Procedures required to deliver startup data for
12 -- COBRA Qualifying Events
13 -- Benefit Classifications
14 -- Valid Dependent Types
15 --
16 -- MODIFIED
17 -- 80.1 J.Rhodes 07-10-1993 - Created
18 -- 80.2 I.Carline 15-11-1993 - Debugged for US Bechtel delivery
19 -- 80.3 Rod Fine 16-12-1993 - Put AS on same line as CREATE stmt
20 -- to workaround export WWBUG #178613.
21 --
22 -- 70.1 Ian Carline 06-Jun-1994 - per 7.0 merged with 8.0
23 -- rewrite.
24 -- 70.2 Ian Carline 07-Jun-1994 - Added check_next_sequence logic
25 -- 70.3 Ian Carline 09-Jun-1994 - Extended check_next_sequence logic.
26 -- 70.4 Rod Fine 19-Sep-1994 - Removed the unnecessary cartesian
27 -- product join on all selects to get
28 -- the starting point for the sequence
29 -- number - improves performance.
30 -- 70.6 Rod Fine 23-Nov-1994 - Suppressed index on business_group_id
31 -- 70.6 M. Stewart 23-Sep-1996 - Updated table names from STU_ to HR_S_
32 -- 70.7 Tim Eyres 02-01-1997 - Moved arcs header to directly after
33 -- 'create or replace' line
34 -- Fix to bug 434902
35 -- 70.9 Tim Eyres 02-01-1997 Correction to version number
36 -- 110.1 mstewart 23-07-1997 Removed show error and select from
37 -- user errors statements (R11)
38 -- (R10 version # 70.10)
39 -- 11.5.4 tmathers 07/20/99 Made v_seq_number and others size
40 -- 15, as 9 creates numeric overflow.
41 -- 115.5 T.Battoo 08-Feb-2000 changed crt_exc so calls
42 -- hr_legislation.insert_hr_stu_exceptions
43 -- 115.6 D.Vickers May 2001 Support for parallel hrglobal and
44 -- better debugging into HR_STU_EXCEPTIONS
45 -- 115.7 D.Vickers 14-Jun-2001 Bug fix 1803867
46 -- 115.8 D.Vickers 18-Jun-2001 to_chars on hr_s_app_ownerships
47 -- 115.9 D.Vickers 25-OCT-2001 performance-use temp HR_S indexes
48 -- 115.10 D.Vickers 21-NOV-2001 del hr_s_app_ownerships commented
49 -- 115.11 D.VIckers 18-NOV-2002 added check_next_sequence call to
50 -- benefit_classifications
51 -- 115.12 DVickers 12-DEC-2002 added check_next_sequence call to bc
52 -- 115.13 DVickers 14-MAR-2003 explicit hrsao.key_value conversion
53 -- 115.14 DVickers 24-FEB-2005 Trace improved
54 -- 115.15 DVickers 12-MAY-2005 remove auto trace in exceptions
55 -- 115.16 DVickers 10-AUG-2005 debug switch
56 -- 115.17 divicker 21-NOV-2005 short term fix for 4728513 - make
57 -- update_uid use 50000000
58 ---------------------------------------------------------------------------
59
60 --****************************************************************************
61 -- INSTALLATION PROCEDURE FOR : PER_COBRA_QFYING_EVENTS
62 --****************************************************************************
63
64
65 PROCEDURE install_cobra_qfying_events(p_phase IN NUMBER)
66 --------------------------------------------------------
67 IS
68 -- Install procedure to transfer startup delivered per_cobra_qfying_events_f
69 -- into a live account, and remove the then delivered rows from the delivery
70 -- account.
71 -- This procedure is called in two phase. Only in the second phase are
72 -- details transferred into live tables. The parameter p_phase holds
73 -- the phase number.
74
75 row_in_error exception;
76 l_current_proc varchar2(80) := 'hr_legislation.install_cobra_qfying_events';
77 l_new_cqe_id number(15);
78 l_null_return varchar2(1);
79
80 CURSOR c_distinct
81 IS
82 -- Select statement used for the main loop. Each row return is used
83 -- as the commit unit, since each true primary key may have many date
84 -- effective rows for it.
85
86 -- The selected primary key is then passed into the second driving
87 -- cursor statement as a parameter, and all date effective rows for
88 -- this primary key are then selected.
89
90 select max(effective_end_date) c_end
91 , qualifying_event_id c_surrogate_key
92 , qualifying_event c_true_key
93 , legislation_code c_leg_code
94 , legislation_subgroup c_leg_sgrp
95 from hr_s_cobra_qfying_events_f
96 group by qualifying_event_id
97 , qualifying_event
98 , legislation_code
99 , legislation_subgroup;
100
101 CURSOR c_each_row (pc_qualifying_event_id varchar2)
102 IS
103 -- selects all date effective rows for the current true primary key
104 -- The primary key has already been selected using the above cursor.
105 -- This cursor accepts the primary key as a parameter and selects all
106 -- date effective rows for it.
107
108 select *
109 from hr_s_cobra_qfying_events_f
110 where qualifying_event_id = pc_qualifying_event_id;
111
112 -- These records are defined here so all sub procedures may use the
113 -- values selected. This saves the need for all sub procedures to have
114 -- a myriad of parameters passed. The cursors are controlled in FOR
115 -- cursor LOOPs. When a row is returned the whole record is copied into
116 -- these record definitions.
117
118 r_distinct c_distinct%ROWTYPE;
119 r_each_row c_each_row%ROWTYPE;
120
121
122 PROCEDURE check_next_sequence
123 -----------------------------
124 IS
125
126 v_sequence_number number(15);
127 v_min_delivered number(15);
128 v_max_delivered number(15);
129
130 -- Surrogate id conflicts may arise from two scenario's:
131 -- 1. Where the newly select sequence value conflicts with values
132 -- in the STU tables.
133 -- 2. Where selected surrogate keys, from the installed tables,
134 -- conflict with other rows in the STU tables.
135 --
136 -- Both of the above scenario's are tested for.
137 -- The first is a simple match, where if a value is detected in the
138 -- STU tables and the installed tables then a conflict is detected. In
139 -- This instance all STU surrogate keys, for this table, are updated.
140 -- The second is tested for using the sequences.
141 -- If the next value from the live sequence is within the range of
142 -- delivered surrogate id's then the live sequence must be incremented.
143 -- If no action is taken, then duplicates may be introduced into the
144 -- delivered tables, and child rows may be totally invalidated.
145
146 BEGIN
147
148
149 BEGIN --check that the installed id's will not conflict
150 --with the delivered values
151
152
153 select distinct null
154 into l_null_return
155 from per_cobra_qfying_events_f a
156 where exists
157 (select null
158 from hr_s_cobra_qfying_events_f b
159 where a.qualifying_event_id = b.qualifying_event_id
160 );
161
162 --conflict may exist
163 --update all qualifying_event_id's to remove conflict
164
165 update hr_s_cobra_qfying_events_f
166 set qualifying_event_id = qualifying_event_id - 50000000;
167
168 update hr_s_application_ownerships
169 set key_value = key_value - 50000000
170 where key_name = 'QUALIFYING_EVENT_ID';
171
172 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
173
174 END; --check of qualifying_event_id
175
176
177
178 select min(qualifying_event_id) - (count(*) *3)
179 , max(qualifying_event_id) + (count(*) *3)
180 into v_min_delivered
181 , v_max_delivered
182 from hr_s_cobra_qfying_events_f;
183
184 select per_cobra_qfying_events_s.nextval
185 into v_sequence_number
186 from dual;
187
188 WHILE v_sequence_number BETWEEN v_min_delivered AND v_max_delivered LOOP
189
190
191 select per_cobra_qfying_events_s.nextval
192 into v_sequence_number
193 from dual;
194
195 END LOOP;
196
197 END check_next_sequence;
198
199
200 PROCEDURE crt_exc (exception_type IN varchar2)
201 ----------------------------------------------
202 IS
203 -- Reports any exceptions during the delivery of startup data to
204 -- PER_COBRA_QFYING_EVENTS
205
206 BEGIN
207 -- When the installation procedures encounter an error that cannot
208 -- be handled, an exception is raised and all work is rolled back
209 -- to the last savepoint. The installation process then continues
210 -- with the next primary key to install. The same exception will
211 -- not be raised more than once.
212
213
214 rollback to new_qualifying_event;
215
216 hr_legislation.insert_hr_stu_exceptions('per_cobra_qfying_events_f'
217 , r_distinct.c_surrogate_key
218 , exception_type
219 , r_distinct.c_true_key);
220
221 END crt_exc;
222
223 PROCEDURE remove(v_id IN number)
224 --------------------------------
225 IS
226 -- Subprogram to delete a row from the delivery tables, and all child
227 -- application ownership rows
228
229 BEGIN
230
231
232 delete from hr_s_cobra_qfying_events_f
233 where qualifying_event_id = v_id;
234
235 END remove;
236
237 PROCEDURE update_uid
238 --------------------
239 IS
240 -- Subprogram to update surrogate UID and all occurrences in child rows
241
242 BEGIN
243
244 -- See if this primary key is already installed. If so then the sorrogate
245 -- key of the delivered row must be updated to the value in the installed
246 -- tables. If the row is not already present then select the next value
247 -- from the sequence. In either case all rows for this primary key must
248 -- be updated, as must all child references to the old surrogate uid.
249
250
251 BEGIN
252 select distinct qualifying_event_id
253 into l_new_cqe_id
254 from per_cobra_qfying_events_f
255 where qualifying_event = r_distinct.c_true_key
256 and business_Group_id is null
257 and legislation_code = r_distinct.c_leg_code;
258
259 EXCEPTION WHEN NO_DATA_FOUND THEN
260
261
262 select per_cobra_qfying_events_s.nextval
263 into l_new_cqe_id
264 from dual;
265
266 WHEN TOO_MANY_ROWS THEN
267 hr_legislation.hrrunprc_trace_on;
268 hr_utility.trace('qualifying_event_id TMR: ' || r_distinct.c_true_key);
269 hr_legislation.hrrunprc_trace_off;
270 raise;
271
272
273 END;
274
275 update hr_s_cobra_qfying_events_f
276 set qualifying_event_id = l_new_cqe_id
277 where qualifying_event_id = r_distinct.c_surrogate_key;
278
279 update hr_s_application_ownerships
280 set key_value = to_char(l_new_cqe_id)
281 where key_value = to_char(r_distinct.c_surrogate_key)
282 and key_name = 'QUALIFYING_EVENT_ID';
283
284 END update_uid;
285
286 FUNCTION valid_ownership RETURN BOOLEAN
287 ---------------------------------------
288 IS
289 -- Test ownership of this current row
290
291 -- This function is split into three distinct parts. The first
292 -- checks to see if a row exists with the same primary key, for a
293 -- business group that would have access to the delivered row. The
294 -- second checks details for data created in other legislations,
295 -- in case data is either created with a null legislation or the
296 -- delivered row has a null legislation. The last check examines
297 -- if this data is actually required for a given install by examining
298 -- the product installation table, and the ownership details for
299 -- this row.
300
301 -- A return code of TRUE indicates that the row is required.
302
303 BEGIN
304
305
306 BEGIN
307
308 -- Perform a check to see if the primary key has been created within
309 -- a visible business group. Ie: the business group is for the same
310 -- legislation as the delivered row, or the delivered row has a null
311 -- legislation. If no rows are returned then the primary key has not
312 -- already been created by a user.
313
314
315 select distinct null
316 into l_null_return
317 from per_cobra_qfying_events_f a
318 where a.business_group_id is not null
319 and a.qualifying_event = r_distinct.c_true_key
320 and exists (select null from per_business_groups b
321 where b.business_group_id = a.business_group_id
322 and b.legislation_code = nvl(r_distinct.c_leg_code,b.legislation_code));
323
324
325 crt_exc('Row already created in a business group');
326
327 -- Indicate this row is not to be transferred
328
329 return FALSE;
330
331 EXCEPTION WHEN NO_DATA_FOUND THEN
332
333 null;
334
335 END;
336
337 -- Now perform a check to see if this primary key has been installed
338 -- with a legislation code that would make it visible at the same time
339 -- as this row. Ie: if any legislation code is null within the set of
340 -- returned rows, then the transfer may not go ahead. If no rows are
341 -- returned then the delivered row is fine.
342
343 BEGIN
344
345
346 select distinct null
347 into l_null_return
348 from per_cobra_qfying_events_f
349 where qualifying_event = r_distinct.c_true_key
350 and legislation_code <> r_distinct.c_leg_code
351 and (legislation_code is null
352 or r_distinct.c_leg_code is null );
353
354
355
356 crt_exc('Row already created for a visible legislation');
357
358 -- Indicates this row is not to be transferred
359
360 return FALSE;
361
362 EXCEPTION WHEN NO_DATA_FOUND THEN
363
364 null;
365
366 END;
367
368 -- The last check examines the product installation table, and the
369 -- ownership details for the delivered row. By examining these
370 -- tables the row is either deleted or not. If the delivered row
371 -- is 'stamped' with a legislation subgroup, then a chweck must be
372 -- made to see if that subgroup is active or not. This check only
373 -- needs to be performed in phase 1, since once this decision is
374 -- made, it is pontless to perform this logic again.
375 -- The exception is raised within this procedure if no rows are returned
376 -- in this select statement. If no rows are returned then one of the
377 -- following is true:
378 -- 1. No ownership parameters are defined.
379 -- 2. The products, for which owning parameters are defined, are not
380 -- installed with as status of 'I'.
381 -- 3. The data is defined for a legislation subgroup that is not active.
382
383 IF p_phase <> 1 THEN
384 return TRUE;
385 END IF;
386
387
388 if (r_distinct.c_leg_sgrp is null) then
389 select null --if exception raised then this row is not needed
390 into l_null_return
391 from dual
392 where exists
393 (select null
397 where a.key_name = 'QUALIFYING_EVENT_ID'
394 from hr_s_application_ownerships a
395 , fnd_product_installations b
396 , fnd_application c
398 and a.key_value = r_distinct.c_surrogate_key
399 and a.product_name = c.application_short_name
400 and c.application_id = b.application_id
401 and ((b.status = 'I' and c.application_short_name <> 'PQP')
402 or
403 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
404 else
405 select null --if exception raised then this row is not needed
406 into l_null_return
407 from dual
408 where exists
409 (select null
410 from hr_s_application_ownerships a
411 , fnd_product_installations b
412 , fnd_application c
413 where a.key_name = 'QUALIFYING_EVENT_ID'
414 and a.key_value = r_distinct.c_surrogate_key
415 and a.product_name = c.application_short_name
416 and c.application_id = b.application_id
417 and ((b.status = 'I' and c.application_short_name <> 'PQP')
418 or
419 (b.status in ('I', 'S') and c.application_short_name = 'PQP')))
420 and exists (
421 select null
422 from hr_legislation_subgroups d
423 where d.legislation_code = r_distinct.c_leg_code
424 and d.legislation_subgroup = r_distinct.c_leg_sgrp
425 and d.active_inactive_flag = 'A' );
426 end if;
427
428
429 -- Indicates row is required
430
431 return TRUE;
432
433 EXCEPTION WHEN NO_DATA_FOUND THEN
434
435 -- Row not needed for any installed product
436
437
438 remove(r_distinct.c_surrogate_key);
439
440 -- Indicates row not needed
441
442 return FALSE;
443
444 END valid_ownership;
445
446 BEGIN
447
448 -- Two loops are used here. The main loop which select distinct primary
449 -- key rows and an inner loop which selects all date effective rows for the
450 -- primary key. The inner loop is only required in phase two, since only
451 -- in phase 2 are rows actually transferred. The logic reads as follows:
452
453 -- - Only deal with rows which have correct ownership details and will
454 -- not cause integrity problems (valid_ownership).
455
456 -- - In Phase 1:
457 -- - Delete delivery rows where the installed rows are identicle.
458 -- - The UNION satement compares delivery rows to installed rows.
459 -- If the sub query returns any rows, then the delivered
460 -- tables and the installed tables are different.
461
462 -- - In Phase 2:
463 -- - Delete from the installed tables using the surrogate id.
464 -- - If an installed row is to be replaced, the values of
465 -- the surrogate keys will be identicle at this stage.
466 -- - Data will then be deleted from the delivery tables.
467 -- - Call the installation procedure for any child tables, that
468 -- must be installed within the same commit unit. If any
469 -- errors occur then rollback to the last declared savepoint.
470 -- - Check that all integrity rules are still obeyed at the end
471 -- of the installation (validity_checks).
472
473 -- An exception is used with this procedure 'row_in_error' in case an error
474 -- is encountered from calling any function. If this is raised, then an
475 -- exception is entered into the control tables (crt_exc();) and a rollback
476 -- is performed.
477
478 IF p_phase = 1 THEN
479 check_next_sequence;
480 END IF;
481
482 FOR qualifying_events IN c_distinct LOOP
483
484 savepoint new_qualifying_event;
485
486 r_distinct := qualifying_events;
487
488 BEGIN
489
490 IF valid_ownership THEN
491
492 -- This row is wanted
493
494
495 IF p_phase = 1 THEN
496
497
498 -- Get new surrogate id and update child references
499
500 update_uid;
501
502 ELSE
503
504 -- Phase = 2
505
506
507 delete from per_cobra_qfying_events_f
508 where qualifying_event_id = r_distinct.c_surrogate_key;
509
510 FOR each_row IN c_each_row(r_distinct.c_surrogate_key)
511 LOOP
512
513 r_each_row := each_row;
514
515
516 insert into per_cobra_qfying_events_f
517 (qualifying_event_id
518 ,effective_start_date
519 ,effective_end_date
520 ,business_group_id
521 ,legislation_code
522 ,elector
523 ,event_coverage
524 ,qualifying_event
525 ,legislation_subgroup
526 ,last_update_date
527 ,last_updated_by
528 ,last_update_login
529 ,created_by
530 ,creation_date
531 )
532 values
533 (r_each_row.qualifying_event_id
537 ,r_each_row.legislation_code
534 ,r_each_row.effective_start_date
535 ,r_each_row.effective_end_date
536 ,r_each_row.business_group_id
538 ,r_each_row.elector
539 ,r_each_row.event_coverage
540 ,r_each_row.qualifying_event
541 ,r_each_row.legislation_subgroup
542 ,r_each_row.last_update_date
543 ,r_each_row.last_updated_by
544 ,r_each_row.last_update_login
545 ,r_each_row.created_by
546 ,r_each_row.creation_date
547 );
548
549 remove(r_distinct.c_surrogate_key);
550
551 END LOOP each_row;
552
553 END IF;
554
555 END IF;
556
557 EXCEPTION WHEN row_in_error THEN
558
559 rollback to new_formula_name;
560
561 END;
562
563 END LOOP formula_names;
564
565 END install_cobra_qfying_events;
566
567 --****************************************************************************
568 -- INSTALLATION PROCEDURE FOR : BEN_BENEFIT_CLASSIFICATIONS
569 --****************************************************************************
570
571 PROCEDURE install_ben_class(p_phase IN number)
572 ----------------------------------------------
573 IS
574 -- Install procedure to transfer startup benefit classifications into
575 -- a live account.
576
577 l_null_return varchar2(1); -- For 'select null' statements
578 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
579
580 CURSOR stu -- Selects all rows from startup entity
581 IS
582 select benefit_classification_name c_true_key
583 , rowid
584 , benefit_classification_id c_surrogate_key
585 , business_group_id
586 , legislation_code c_leg_code
587 , active_flag
588 , beneficiary_allowed_flag
589 , benefit_classification_type
590 , chargeable_flag
591 , cobra_flag
592 , contributions_used
593 , dependents_allowed_flag
594 , dflt_post_termination_rule
595 , dflt_processing_type
596 , ben_class_processing_rule
597 , comments
598 , last_update_date
599 , last_updated_by
600 , last_update_login
601 , created_by
602 , creation_date
603 from hr_s_benefit_classifications;
604
605 stu_rec stu%ROWTYPE;
606
607
608 PROCEDURE check_next_sequence
609 -----------------------------
610 IS
611
612 v_sequence_number number(15);
613 v_min_delivered number(15);
614 v_max_delivered number(15);
615
616 -- Surrogate id conflicts may arise from two scenario's:
617 -- 1. Where the newly select sequence value conflicts with values
618 -- in the STU tables.
619 -- 2. Where selected surrogate keys, from the installed tables,
620 -- conflict with other rows in the STU tables.
621 --
622 -- Both of the above scenario's are tested for.
623 -- The first is a simple match, where if a value is detected in the
624 -- STU tables and the installed tables then a conflict is detected. In
625 -- This instance all STU surrogate keys, for this table, are updated.
626 -- The second is tested for using the sequences.
627 -- If the next value from the live sequence is within the range of
628 -- delivered surrogate id's then the live sequence must be incremented.
629 -- If no action is taken, then duplicates may be introduced into the
630 -- delivered tables, and child rows may be totally invalidated.
631
632 BEGIN
633
634
635 BEGIN --check that the installed id's will not conflict
636 --with the delivered values
637
638
639 select distinct null
640 into l_null_return
641 from ben_benefit_classifications a
642 where exists
643 (select null
644 from hr_s_benefit_classifications b
645 where a.benefit_classification_id=b.benefit_classification_id
646 );
647
648 --conflict may exist
649 --update all benefit_classification_id's to remove conflict
650
651 update hr_s_benefit_classifications
652 set benefit_classification_id=benefit_classification_id-50000000;
653
654 update hr_s_application_ownerships
655 set key_value = key_value - 50000000
656 where key_name = 'BENEFIT_CLASSIFICATION_ID';
657
658 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
659
660 END; --check of benefit_classification_id
661
662
663
664 select min(benefit_classification_id) - (count(*) *3)
665 , max(benefit_classification_id) + (count(*) *3)
666 into v_min_delivered
667 , v_max_delivered
668 from hr_s_benefit_classifications;
669
670 select ben_benefit_classifications_s.nextval
671 into v_sequence_number
672 from dual;
673
674 WHILE
675 v_sequence_number BETWEEN v_min_delivered AND v_max_delivered
676 LOOP
677
678
679 select ben_benefit_classifications_s.nextval
680 into v_sequence_number
681 from dual;
682
683 END LOOP;
687 PROCEDURE crt_exc (exception_type IN varchar2)
684
685 END check_next_sequence;
686
688 ----------------------------------------------
689 IS
690 -- Reports any exceptions during the delivery of startup data to
691 -- BEN_BENEFIT_CLASSIFICATIONS
692
693 BEGIN
694 -- When the installation procedures encounter an error that cannot
695 -- be handled, an exception is raised and all work is rolled back
696 -- to the last savepoint. The installation process then continues
697 -- with the next primary key to install. The same exception will
698 -- not be raised more than once.
699
700 rollback to new_classification_name;
701
702 hr_legislation.insert_hr_stu_exceptions('ben_benefit_classifications'
703 , stu_rec.c_surrogate_key
704 , exception_type
705 , stu_rec.c_true_key);
706
707
708 END crt_exc;
709
710 PROCEDURE update_uid
711 --------------------
712 IS
713 -- Subprogram to update surrogate UID and all occurrences in child rows
714
715 BEGIN
716
717 BEGIN
718
719
720 select distinct benefit_classification_id
721 into l_new_surrogate_key
722 from ben_benefit_classifications
723 where benefit_classification_name = stu_rec.c_true_key
724 and business_group_id is null
725 and ( (legislation_code is null
726 and stu_rec.c_leg_code is null)
727 or (legislation_code = stu_rec.c_leg_code) );
728
729 EXCEPTION WHEN NO_DATA_FOUND THEN
730
731
732 select ben_benefit_classifications_s.nextval
733 into l_new_surrogate_key
734 from dual;
735
736 END;
737
738 -- Update all child entities
739
740
741 update hr_s_benefit_classifications
742 set benefit_classification_id = l_new_surrogate_key
743 where benefit_classification_id = stu_rec.c_surrogate_key;
744
745
746 update hr_s_application_ownerships
747 set key_value = to_char(l_new_surrogate_key)
748 where key_value = to_char(stu_rec.c_surrogate_key)
749 and key_name = 'BENEFIT_CLASSIFICATION_ID';
750
751
752 update hr_s_element_types_f
753 set benefit_classification_id = l_new_surrogate_key
754 where benefit_classification_id = stu_rec.c_surrogate_key;
755
756 END update_uid;
757
758 PROCEDURE remove
759 ----------------
760 IS
761 -- Remove a row from either the startup tables or the installed tables
762
763 BEGIN
764
765
766 delete from hr_s_benefit_classifications
767 where rowid = stu_rec.rowid;
768
769 END remove;
770
771 FUNCTION valid_ownership RETURN BOOLEAN
772 ---------------------------------------
773 IS
774 -- Test ownership of this current row
775
776 BEGIN
777 -- This routine only operates in phase 1. Rows are present in the
778 -- table hr_application_ownerships in the delivery account, which
779 -- dictate which products a piece of data is used for. If the query
780 -- returns a rowm then this data is required, and the function will
781 -- return true. If no rows are returned and an exception is raised,
782 -- then this row is not required and may be deleted from the delivery
783 -- tables.
784
785 -- If legislation code and subgroup code are included on the delivery
786 -- tables, a check must be made to determine if the data is defined for
787 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
788 -- installation.
789
790 -- A return code of TRUE indicates that thhe row is required.
791
792 -- The exception is raised within this procedure if no rows are returned
793 -- in this select statement. If no rows are returned then one of the
794 -- following is true:
795 -- 1. No ownership parameters are defined.
796 -- 2. The products, for which owning parameters are defined, are not
797 -- installed with as status of 'I'.
798 -- 3. The data is defined for a legislation subgroup that is not active.
799
800
801 IF p_phase <> 1 THEN
802 return TRUE;
803 END IF;
804
805
806 -- If exception raised below then this row is not needed
807
808 select null
809 into l_null_return
810 from dual
811 where exists
812 (select null
813 from hr_s_application_ownerships a
814 , fnd_product_installations b
815 , fnd_application c
816 where a.key_name = 'BENEFIT_CLASSIFICATION_ID'
817 and a.key_value = stu_rec.c_surrogate_key
818 and a.product_name = c.application_short_name
819 and c.application_id = b.application_id
820 and ((b.status = 'I' and c.application_short_name <> 'PQP')
821 or
822 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
823
824 -- Indicates row is required
825
826 return TRUE;
827
828 EXCEPTION WHEN NO_DATA_FOUND THEN
829
830 -- Row not needed for any installed product
831
832
833 remove;
834
835 -- Indicates row not needed
836
840
837 return FALSE;
838
839 END valid_ownership;
841 PROCEDURE transfer_row
842 ----------------------
843 IS
844 -- Check if a delivered row is needed and insert into the
845 -- live tables if it is
846
847 BEGIN
848
849
850 BEGIN
851
852 -- Perform a check to see if the primary key has been creeated within
853 -- a visible business group. Ie: the business group is for the same
854 -- legislation as the delivered row, or the delivered row has a null
855 -- legislation. If no rows are returned then the primary key has not
856 -- already been created by a user.
857
858 select distinct null
859 into l_null_return
860 from ben_benefit_classifications a
861 where a.business_group_id is not null
862 and a.benefit_classification_name = stu_rec.c_true_key
863 and exists (select null from per_business_groups b
864 where b.business_group_id = a.business_group_id
865 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
866
867 crt_exc('Row already created in a business group');
868
869 -- Indicates this row is not to be transferred
870
871 return;
872
873 EXCEPTION WHEN NO_DATA_FOUND THEN
874
875 null;
876
877 END;
878
879
880 -- Now perform a check to see if this primary key has been installed
881 -- with a legislation code that would make it visible at the same time
882 -- as this row. Ie: if any legislation code is null within the set of
883 -- returned rows, then the transfer may not go ahead. If no rows are
884 -- returned then the delivered row is fine.
885
886 BEGIN
887
888 select distinct null
889 into l_null_return
890 from ben_benefit_classifications
891 where benefit_classification_name = stu_rec.c_true_key
892 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
893 and ( legislation_code is null
894 or stu_rec.c_leg_code is null );
895
896 crt_exc('Row already created for a visible legislation');
897
898 -- Indicates this row is not to be transferred
899
900 return;
901
902 EXCEPTION WHEN NO_DATA_FOUND THEN
903
904 null;
905
906 END;
907
908 -- When the procedure is called in phase 1, there is no need to
909 -- actually perform the transfer from the delivery tables into the
910 -- live. Hence if phase = 1 control is returned to the calling
911 -- procedure and the next row is returned.
912
913 -- If the procedure is called in phase 2, then the live row is updated
914 -- with the values on the delivered row.
915
916 -- The routine check_parents validates foreign key references and
917 -- ensures referential integrity. The routine checks to see if the
918 -- parents of a given row have been transfered to the live tables.
919
920 -- This may only be called in phase two since in phase one all
921 -- parent rows will remain in the delivery tables.
922
923 -- After the above checks only data that has been chanegd or is new
924 -- will be left in the delivery tables. At this stage if the row is
925 -- already present then it must be updated to ensure referential
926 -- integrity. Therefore an update will be performed and if SQL%FOUND
927 -- is FALSE an insert will be performed.
928
929 -- The last step of the transfer, in phase 2, is to delete the now
930 -- transfered row from the delivery tables.
931
932 IF p_phase = 1 THEN
933 return;
934 END IF;
935
936
937 update ben_benefit_classifications
938 set benefit_classification_name = stu_rec.c_true_key
939 , business_group_id = stu_rec.business_group_id
940 , legislation_code = stu_rec.c_leg_code
941 , active_flag = stu_rec.active_flag
942 , beneficiary_allowed_flag = stu_rec.beneficiary_allowed_flag
943 , benefit_classification_type = stu_rec.benefit_classification_type
944 , chargeable_flag = stu_rec.chargeable_flag
945 , cobra_flag = stu_rec.cobra_flag
946 , contributions_used = stu_rec.contributions_used
947 , dependents_allowed_flag = stu_rec.dependents_allowed_flag
948 , dflt_post_termination_rule = stu_rec.dflt_post_termination_rule
949 , dflt_processing_type = stu_rec.dflt_processing_type
950 , ben_class_processing_rule = stu_rec.ben_class_processing_rule
951 , comments = stu_rec.comments
952 , last_update_date = stu_rec.last_update_date
953 , last_updated_by = stu_rec.last_updated_by
954 , last_update_login = stu_rec.last_update_login
955 , created_by = stu_rec.created_by
956 , creation_date = stu_rec.creation_date
957 where benefit_classification_id = stu_rec.c_surrogate_key;
958
959 IF SQL%NOTFOUND THEN
960
961
962 insert into ben_benefit_classifications
963 ( benefit_classification_name
964 , benefit_classification_id
965 , business_group_id
969 , benefit_classification_type
966 , legislation_code
967 , active_flag
968 , beneficiary_allowed_flag
970 , chargeable_flag
971 , cobra_flag
972 , contributions_used
973 , dependents_allowed_flag
974 , dflt_post_termination_rule
975 , dflt_processing_type
976 , ben_class_processing_rule
977 , comments
978 , last_update_date
979 , last_updated_by
980 , last_update_login
981 , created_by
982 , creation_date
983 )
984 values
985 ( stu_rec.c_true_key
986 , stu_rec.c_surrogate_key
987 , stu_rec.business_group_id
988 , stu_rec.c_leg_code
989 , stu_rec.active_flag
990 , stu_rec.beneficiary_allowed_flag
991 , stu_rec.benefit_classification_type
992 , stu_rec.chargeable_flag
993 , stu_rec.cobra_flag
994 , stu_rec.contributions_used
995 , stu_rec.dependents_allowed_flag
996 , stu_rec.dflt_post_termination_rule
997 , stu_rec.dflt_processing_type
998 , stu_rec.ben_class_processing_rule
999 , stu_rec.comments
1000 , stu_rec.last_update_date
1001 , stu_rec.last_updated_by
1002 , stu_rec.last_update_login
1003 , stu_rec.created_by
1004 , stu_rec.creation_date
1005 );
1006
1007 END IF;
1008
1009
1010 remove;
1011
1012 END transfer_row;
1013
1014 BEGIN
1015
1016 -- This is the main loop to perform the installation logic. A cursor
1017 -- is opened to control the loop, and each row returned is placed
1018 -- into a record defined within the main procedure so each sub
1019 -- procedure has full access to all returrned columns. For each
1020 -- new row returned, a new savepoint is declared. If at any time
1021 -- the row is in error a rollback is performed to the savepoint
1022 -- and the next row is returned. Ownership details are checked and
1023 -- if the row is required then the surrogate id is updated and the
1024 -- main transfer logic is called.
1025
1026 IF p_phase = 1 THEN
1027 check_next_sequence;
1028 END IF;
1029
1030 FOR delivered IN stu LOOP
1031
1032 -- Uses main cursor stu to impilicity define a record
1033
1034
1035 savepoint new_classification_name;
1036
1037 stu_rec := delivered;
1038
1039 IF p_phase = 2 THEN
1040 l_new_surrogate_key := stu_rec.c_surrogate_key;
1041 END IF;
1042
1043 IF valid_ownership THEN
1044
1045 -- Test the row onerships for the current row
1046
1047
1048 IF p_phase = 1 THEN
1049 update_uid;
1050 END IF;
1051
1052 transfer_row;
1053
1054 END IF;
1055
1056 END LOOP;
1057
1058 END install_ben_class;
1059
1060 --****************************************************************************
1061 -- INSTALLATION PROCEDURE FOR : BEN_VALID_DEPENDANT_TYPES
1062 --****************************************************************************
1063
1064 PROCEDURE install_valid_dep_type(p_phase IN number)
1065 ---------------------------------------------------
1066 IS
1067 -- Install procedure to transfer startup Valid Dependent Types into
1068 -- a live account.
1069
1070 l_null_return varchar2(1); -- For 'select null' statements
1071 l_new_surrogate_key number(15); -- New surrogate key for the delivery row
1072
1073 CURSOR stu -- Selects all rows from startup entity
1074 IS
1075
1076 select contact_type||coverage_type c_true_key
1077 , rowid
1078 , valid_dependent_type_id c_surrogate_key
1079 , business_group_id
1080 , legislation_code c_leg_code
1081 , contact_type
1082 , coverage_type
1083 , maximum_number
1084 , last_update_date
1085 , last_updated_by
1086 , last_update_login
1087 , created_by
1088 , creation_date
1089 from hr_s_valid_dependent_types;
1090
1091 stu_rec stu%ROWTYPE;
1092
1093 PROCEDURE crt_exc (exception_type IN varchar2)
1094 ----------------------------------------------
1095 IS
1096 -- Reports any exceptions during the delivery of startup data to
1097 -- BEN_VALID_DEPENDANT_TYPES
1098
1099 BEGIN
1100 -- When the installation procedures encounter an error that cannot
1101 -- be handled, an exception is raised and all work is rolled back
1102 -- to the last savepoint. The installation process then continues
1103 -- with the next primary key to install. The same exception will
1104 -- not be raised more than once.
1105
1106 rollback to vdt;
1107
1108 hr_legislation.insert_hr_stu_exceptions('ben_valid_dependent_types'
1109 , stu_rec.c_surrogate_key
1110 , exception_type
1111 , stu_rec.c_true_key);
1112
1113 END crt_exc;
1114
1115 PROCEDURE update_uid
1116 IS
1117 -- Subprogram to update surrogate UID and all occurrences in child rows
1118
1122
1119 BEGIN
1120
1121 BEGIN
1123
1124 select distinct valid_dependent_type_id
1125 into l_new_surrogate_key
1126 from ben_valid_dependent_types
1127 where contact_type||coverage_type = stu_rec.c_true_key
1128 and business_group_id is null
1129 and ( (legislation_code is null
1130 and stu_rec.c_leg_code is null)
1131 or (legislation_code = stu_rec.c_leg_code) );
1132
1133 EXCEPTION WHEN NO_DATA_FOUND THEN
1134
1135
1136 select ben_valid_dependent_types_s.nextval
1137 into l_new_surrogate_key
1138 from dual;
1139
1140 END;
1141
1142 --update all child entities
1143
1144 update hr_s_valid_dependent_types
1145 set valid_dependent_type_id = l_new_surrogate_key
1146 where valid_dependent_type_id = stu_rec.c_surrogate_key;
1147
1148 update hr_s_application_ownerships
1149 set key_value = to_char(l_new_surrogate_key)
1150 where key_value = to_char(stu_rec.c_surrogate_key)
1151 and key_name = 'VALID_DEPENDENT_TYPE_ID';
1152
1153 END update_uid;
1154
1155 PROCEDURE remove
1156 IS
1157 -- Remove a row from either the startup tables or the installed tables
1158
1159 BEGIN
1160
1161 delete from hr_s_valid_dependent_types
1162 where rowid = stu_rec.rowid;
1163
1164 END remove;
1165
1166 FUNCTION valid_ownership RETURN BOOLEAN
1167 ---------------------------------------
1168 IS
1169 -- Test ownership of this current row
1170
1171 BEGIN
1172
1173 -- This routine only operates in phase 1. Rows are present in the
1174 -- table hr_application_ownerships in the delivery account, which
1175 -- dictate which products a piece of data is used for. If the query
1176 -- returns a rowm then this data is required, and the function will
1177 -- return true. If no rows are returned and an exception is raised,
1178 -- then this row is not required and may be deleted from the delivery
1179 -- tables.
1180
1181 -- If legislation code and subgroup code are included on the delivery
1182 -- tables, a check must be made to determine if the data is defined for
1183 -- a specific subgroup. If so the subgroup must be 'A'ctive for this
1184 -- installation.
1185
1186 -- A return code of TRUE indicates that thhe row is required.
1187
1188 -- The exception is raised within this procedure if no rows are returned
1189 -- in this select statement. If no rows are returned then one of the
1190 -- following is true:
1191
1192 -- 1. No ownership parameters are defined.
1193 -- 2. The products, for which owning parameters are defined, are not
1194 -- installed with as status of 'I'.
1195 -- 3. The data is defined for a legislation subgroup that is not active.
1196
1197
1198 IF p_phase <> 1 THEN
1199 return TRUE;
1200 END IF;
1201
1202
1203 select null --if exception raised then this row is not needed
1204 into l_null_return
1205 from dual
1206 where exists
1207 (select null
1208 from hr_s_application_ownerships a
1209 , fnd_product_installations b
1210 , fnd_application c
1211 where a.key_name = 'VALID_DEPENDENT_TYPE_ID'
1212 and a.key_value = stu_rec.c_surrogate_key
1213 and a.product_name = c.application_short_name
1214 and c.application_id = b.application_id
1215 and ((b.status = 'I' and c.application_short_name <> 'PQP')
1216 or
1217 (b.status in ('I', 'S') and c.application_short_name = 'PQP')));
1218
1219 -- Indicates row is required
1220
1221 return TRUE;
1222
1223 EXCEPTION WHEN NO_DATA_FOUND THEN
1224
1225 -- Row not needed for any installed product
1226
1227
1228 remove;
1229
1230 -- Indicates row not needed
1231
1232 return FALSE;
1233
1234 END valid_ownership;
1235
1236 PROCEDURE transfer_row
1237 ----------------------
1238 IS
1239 -- Check if a delivered row is needed and insert into the
1240 -- live tables if it is
1241
1242 BEGIN
1243
1244
1245 BEGIN
1246
1247 -- Perform a check to see if the primary key has been created within
1248 -- a visible business group. Ie: the business group is for the same
1249 -- legislation as the delivered row, or the delivered row has a null
1250 -- legislation. If no rows are returned then the primary key has not
1251 -- already been created by a user.
1252
1253 select distinct null
1254 into l_null_return
1255 from ben_valid_dependent_types a
1256 where a.business_group_id is not null
1257 and a.contact_type||a.coverage_type = stu_rec.c_true_key
1258 and exists (select null from per_business_groups b
1259 where b.business_group_id = a.business_group_id
1260 and b.legislation_code = nvl(stu_rec.c_leg_code,b.legislation_code));
1261
1262 crt_exc('Row already created in a business group');
1263
1264 -- Indicates this row is not to be transferred
1265
1266 return;
1267
1268 EXCEPTION WHEN NO_DATA_FOUND THEN
1269
1270 null;
1271
1275 -- Now perform a check to see if this primary key has been installed
1272 END;
1273
1274
1276 -- with a legislation code that would make it visible at the same time
1277 -- as this row. Ie: if any legislation code is null within the set of
1278 -- returned rows, then the transfer may not go ahead. If no rows are
1279 -- returned then the delivered row is fine.
1280
1281 BEGIN
1282
1283 select distinct null
1284 into l_null_return
1285 from ben_valid_dependent_types
1286 where contact_type||coverage_type = stu_rec.c_true_key
1287 and nvl(legislation_code,'x') <> nvl(stu_rec.c_leg_code,'x')
1288 and (
1289 legislation_code is null
1290 or stu_rec.c_leg_code is null
1291 );
1292 crt_exc('Row already created for a visible legislation');
1293 return; --indicates this row is not to be transferred
1294
1295 EXCEPTION WHEN NO_DATA_FOUND THEN
1296
1297 null;
1298
1299 END;
1300
1301 -- When the procedure is called in phase 1, there is no need to
1302 -- actually perform the transfer from the delivery tables into the
1303 -- live. Hence if phase = 1 control is returned to the calling
1304 -- procedure and the next row is returned.
1305
1306 -- If the procedure is called in phase 2, then the live row is updated
1307 -- with the values on the delivered row.
1308
1309 -- The routine check_parents validates foreign key references and
1310 -- ensures referential integrity. The routine checks to see if the
1311 -- parents of a given row have been transfered to the live tables.
1312
1313 -- This may only be called in phase two since in phase one all
1314 -- parent rows will remain in the delivery tables.
1315
1316 -- After the above checks only data that has been chanegd or is new
1317 -- will be left in the delivery tables. At this stage if the row is
1318 -- already present then it must be updated to ensure referential
1319 -- integrity. Therefore an update will be performed and if SQL%FOUND
1320 -- is FALSE an insert will be performed.
1321
1322 -- The last step of the transfer, in phase 2, is to delete the now
1323 -- transfered row from the delivery tables.
1324
1325 IF p_phase = 1 THEN
1326 return;
1327 END IF;
1328
1329
1330 update ben_valid_dependent_types
1331 set business_group_id = stu_rec.business_group_id
1332 , legislation_code = stu_rec.c_leg_code
1333 , contact_type = stu_rec.contact_type
1334 , coverage_type = stu_rec.coverage_type
1335 , maximum_number = stu_rec.maximum_number
1336 , last_update_date = stu_rec.last_update_date
1337 , last_updated_by = stu_rec.last_updated_by
1338 , last_update_login = stu_rec.last_update_login
1339 , created_by = stu_rec.created_by
1340 , creation_date = stu_rec.creation_date
1341 where valid_dependent_type_id = stu_rec.c_surrogate_key;
1342
1343 IF SQL%NOTFOUND THEN
1344
1345
1346 insert into ben_valid_dependent_types
1347 ( valid_dependent_type_id
1348 , business_group_id
1349 , legislation_code
1350 , contact_type
1351 , coverage_type
1352 , maximum_number
1353 , last_update_date
1354 , last_updated_by
1355 , last_update_login
1356 , created_by
1357 , creation_date
1358 )
1359 values
1360 ( stu_rec.c_surrogate_key
1361 , stu_rec.business_group_id
1362 , stu_rec.c_leg_code
1363 , stu_rec.contact_type
1364 , stu_rec.coverage_type
1365 , stu_rec.maximum_number
1366 , stu_rec.last_update_date
1367 , stu_rec.last_updated_by
1368 , stu_rec.last_update_login
1369 , stu_rec.created_by
1370 , stu_rec.creation_date
1371 );
1372
1373 END IF;
1374
1375
1376 remove;
1377
1378 END transfer_row;
1379
1380 BEGIN
1381
1382 -- This is the main loop to perform the installation logic. A cursor
1383 -- is opened to control the loop, and each row returned is placed
1384 -- into a record defined within the main procedure so each sub
1385 -- procedure has full access to all returrned columns. For each
1386 -- new row returned, a new savepoint is declared. If at any time
1387 -- the row is in error a rollback iss performed to the savepoint
1388 -- and the next row is returned. Ownership details are checked and
1389 -- if the row is required then the surrogate id is updated and the
1390 -- main transfer logic is called.
1391
1392 FOR delivered IN stu LOOP
1393
1394 -- Uses main cursor stu to impilicity define a record
1395
1396
1397 savepoint vdt;
1398
1399 stu_rec := delivered;
1400
1401 IF p_phase = 2 THEN
1402 l_new_surrogate_key := stu_rec.c_surrogate_key;
1403 END IF;
1404
1405 IF valid_ownership THEN
1406
1407 -- Test the row onerships for the current row
1408
1409
1410 IF p_phase = 1 THEN
1411 update_uid;
1412 END IF;
1413
1414 transfer_row;
1415
1416 END IF;
1417
1418 END LOOP;
1419
1420 END install_valid_dep_type;
1421
1422 --****************************************************************************
1423 -- OVERALL INSTALLATION PROCEDURE
1424 --****************************************************************************
1425
1426 PROCEDURE install(p_phase number)
1427 ---------------------------------
1428 IS
1429 -- Driver procedure to control the execution of all installation
1430 -- procedures in this package.
1431
1432 BEGIN
1433
1434 IF p_phase = 1 OR p_phase =2 THEN
1435
1436 hr_legislation.hrrunprc_trace_on;
1437 hr_utility.trace('start ben_install: ' || to_char(p_phase));
1438
1439 hr_utility.trace('start install_cobra_qfying_events : ' || to_char(p_phase));
1440 hr_legislation.hrrunprc_trace_off;
1441
1442 install_cobra_qfying_events(p_phase); -- Cobra Qualifying Events
1443
1444 hr_legislation.hrrunprc_trace_on;
1445 hr_utility.trace('start install_ben_class : ' || to_char(p_phase));
1446 hr_legislation.hrrunprc_trace_off;
1447
1448 install_ben_class(p_phase); -- Benefit Classifications
1449
1450 hr_legislation.hrrunprc_trace_on;
1451 hr_utility.trace('start install_valid_dep_type : ' || to_char(p_phase));
1452 hr_legislation.hrrunprc_trace_off;
1453
1454 install_valid_dep_type(p_phase); -- Valid Dependent Types
1455
1456 hr_legislation.hrrunprc_trace_on;
1457 hr_utility.trace('exit ben_install: ' || to_char(p_phase));
1458 hr_legislation.hrrunprc_trace_off;
1459
1460 END IF;
1461
1462 END install;
1463
1464 end hr_legislation_benefits;
[Home] [Help]