[Home] [Help]
PACKAGE BODY: APPS.BEN_SEED_LIFE_EVENTS
Source
1 package body ben_seed_life_events as
2 /* $Header: benlerse.pkb 120.4 2006/11/07 09:22:19 nhunur ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Seed Life Events
13 Purpose
14 This package is used to seed life events on a business group basis.
15 History
16 Date Who Version What?
17 ---- --- ------- -----
18 24 Jan 98 G Perry 110.0 Created.
19 08 Apr 98 G Perry 110.1 Accounted for schema change
20 to ben_ler_f.
21 16 Jun 98 G Perry 110.2 Rewrote insert going straight
22 to the table to avoid mutating
23 table issues.
24 31-dec-98 jlamoure 110.3 Added new life events QMSCO and
25 QDRO.
26 15-Jan-99 maagrawa 115.4 Added life event for override
27 enrollment.
28 04-Mar-99 lmcdonal 115.5 remove QMSCO and QDRO.
29 04-Mar-99 stee 115.6 Add new life events, Reduction
30 of hours, Loss of Eligibility
31 and Satisfied Waiting Period
32 events.
33 01-Jul-99 tmathers 115.7 Use startup table.
34 24-Sep-99 stee 115.8 Add cobra qualifying event
35 flag.
36 12-May-00 stee 115.9 Insert into MLS table.
37 30-Jun-06 rbingi 115.11 5367645: Inserting defaults to
38 TimelinesEvalCode and Timneliness Days
39 18-Sep-06 rgajula 115.12 Bug 5521080 : Removed the condition s_ler.name = ler.name
40 2-nov-06 nhunur 115.13 set codes for temporals only
41 */
42 --------------------------------------------------------------------------------
43 --
44 g_package varchar2(80) := 'ben_seed_life_events';
45 --
46 --
47 procedure seed_life_events(p_business_group_id in number) is
48 --
49 l_package varchar2(80) := g_package||'.seed_life_events';
50 l_object_version_number number(38);
51 --
52 begin
53 --
54 hr_utility.set_location ('Entering '||l_package,10);
55 --
56 insert into ben_ler_f
57 (ler_id,
58 effective_start_date,
59 effective_end_date,
60 name,
61 business_group_id,
62 typ_cd,
63 ck_rltd_per_elig_flag,
64 cm_aply_flag,
65 ovridg_le_flag,
66 qualg_evt_flag,
67 tmlns_eval_cd, -- 5367645
68 tmlns_dys_num, -- 5367645
69 object_version_number)
70 select
71 ben_ler_f_s.nextval,
72 to_date('01-01-1950','DD-MM-YYYY'),
73 to_date('31-12-4712','DD-MM-YYYY'),
74 name,
75 p_business_group_id,
76 typ_cd,
77 'N',
78 'N',
79 'N',
80 'N',
81 decode(TYP_CD,'DRVDAGE','PRCM', 'DRVDCAL','PRCM','DRVDCMP','PRCM','DRVDHRW','PRCM','DRVDLOS','PRCM',NULL) ,
82 decode(TYP_CD,'DRVDAGE',90, 'DRVDCAL',90,'DRVDCMP',90,'DRVDHRW',90,'DRVDLOS',90,NULL) ,
83 1
84 from ben_startup_lers s_ler
85 where not exists (select 1
86 from ben_ler_f ler
87 where s_ler.typ_cd = ler.typ_cd
88 and ler.business_group_id = p_business_group_id
89 );
90
91 -- Bug 5521080 : Removed the condition s_ler.name = ler.name
92 -- from the above select statement to make it lesser restrictive.
93 --
94 -- Insert into MLS table.
95 --
96 insert into ben_ler_f_tl (
97 ler_id,
98 effective_start_date,
99 effective_end_date,
100 typ_cd,
101 name,
102 language,
103 source_lang,
104 last_update_date,
105 last_updated_by,
106 last_update_login,
107 created_by,
108 creation_date
109 ) select
110 b.ler_id,
111 b.effective_start_date,
112 b.effective_end_date,
113 tl.typ_cd,
114 tl.name,
115 tl.language,
116 tl.source_lang,
117 b.last_update_date,
118 b.last_updated_by,
119 b.last_update_login,
120 b.created_by,
121 b.creation_date
122 from ben_ler_f b
123 ,ben_startup_lers_tl tl
124 where b.business_group_id = p_business_group_id
125 and b.typ_cd = tl.typ_cd
126 and not exists
127 (select 'Y'
128 from ben_ler_f_tl t
129 where t.ler_id = b.ler_id
130 and t.effective_start_date = b.effective_start_date
131 and t.source_lang = userenv('LANG'));
132
133 hr_utility.set_location ('Leaving '||l_package,10);
134 --
135 end seed_life_events;
136 --
137 end ben_seed_life_events;