PACKAGE BODY: APPS.BEN_SEED_REGULATIONS

Source

1 package body ben_seed_regulations as
2 /* $Header: benregse.pkb 120.0 2005/05/28 09:25:43 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 |			Copyright (c) 1997 Oracle Corporation		       |
7 |			   Redwood Shores, California, USA		       |
8 |			        All rights reserved.			       |
9 +==============================================================================+
10 --
11 Name
12 	Seed Regulations
13 Purpose
14         This package is used to seed regulations on a business group basis.
15 History
16         Date             Who        Version    What?
17         ----             ---        -------    -----
18         08 Oct 99        S Tee      115.0      Created.
19         11 May 00        S Tee      115.1      Insert into MLS table.
20 */
21 --------------------------------------------------------------------------------
22 --
23 g_package varchar2(80) := 'ben_seed_regulations';
24 --
25 --
26 procedure seed_regulations(p_business_group_id in number) is
27   --
28   l_package               varchar2(80) := g_package||'.seed_regulations';
29   l_object_version_number number(38);
30   --
31 begin
32   --
33   hr_utility.set_location ('Entering '||l_package,10);
34     --
35     insert into ben_regn_f
36     (regn_id,
37      effective_start_date,
38      effective_end_date,
39      name,
40      sttry_citn_name,
41      business_group_id,
42      object_version_number)
43     select
44      ben_regn_f_s.nextval,
45      to_date('01-01-1950','DD-MM-YYYY'),
46      to_date('31-12-4712','DD-MM-YYYY'),
47      name,
48      sttry_citn_name,
49      p_business_group_id,
50      1
51    from ben_startup_regn s_reg
52    where not exists (select 1
53                      from ben_regn_f reg
54                      where s_reg.sttry_citn_name = reg.sttry_citn_name
55                      and   reg.business_group_id = p_business_group_id
56                     );
57   --
58   -- Insert into MLS table.
59   --
60   insert into ben_regn_f_tl (
61     regn_id,
62     effective_start_date,
63     effective_end_date,
64     name,
65     sttry_citn_name,
66     language,
67     source_lang,
68     last_update_date,
69     last_updated_by,
70     last_update_login,
71     created_by,
72     creation_date
73   ) select
74     b.regn_id,
75     b.effective_start_date,
76     b.effective_end_date,
77     tl.name,
78     tl.sttry_citn_name,
79     tl.language,
80     tl.source_lang,
81     b.last_update_date,
82     b.last_updated_by,
83     b.last_update_login,
84     b.created_by,
85     b.creation_date
86   from ben_regn_f b
87       ,ben_startup_regn_tl tl
88   where b.business_group_id = p_business_group_id
89   and b.sttry_citn_name = tl.sttry_citn_name
90   and not exists
91           (select 'Y'
92            from ben_regn_f_tl t
93            where t.regn_id = b.regn_id
94            and t.effective_start_date = b.effective_start_date
95            and   t.source_lang = userenv('LANG'));
96  --
97  hr_utility.set_location ('Leaving '||l_package,10);
98   --
99 end seed_regulations;
100 --
101 end ben_seed_regulations;