[Home] [Help]
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;