[Home] [Help]
PACKAGE BODY: APPS.BEN_BNG_BUS
Source
1 Package Body ben_bng_bus as
2 /* $Header: bebngrhi.pkb 120.0.12010000.2 2008/08/05 14:08:45 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bng_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_benfts_grp_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- benfts_grp_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_benfts_grp_id(p_benfts_grp_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_benfts_grp_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_bng_shd.api_updating
47 (p_benfts_grp_id => p_benfts_grp_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_benfts_grp_id,hr_api.g_number)
52 <> ben_bng_shd.g_old_rec.benfts_grp_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_bng_shd.constraint_error('BEN_BENFTS_GRP_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_benfts_grp_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_bng_shd.constraint_error('BEN_BENFTS_GRP_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_benfts_grp_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------------------------< chk_name_unique >-------------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- ensure that the Benefits Group Name is unique
82 -- within business_group
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_name is Benefits Group name
89 -- p_benfts_grp_id is benfts_grp_id
90 -- p_business_group_id
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Errors handled by the procedure
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 ---- ----------------------------------------------------------------------------
101 Procedure chk_name_unique
102 ( p_benfts_grp_id in number
103 ,p_name in varchar2
104 ,p_business_group_id in number)
105 is
106 l_proc varchar2(72) := g_package||'chk_name_unique';
107 l_dummy char(1);
108 cursor c1 is select null
109 from ben_benfts_grp
110 Where benfts_grp_id <> nvl(p_benfts_grp_id,-1)
111 and name = p_name
112 and business_group_id = p_business_group_id;
113 --
114 Begin
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 open c1;
118 fetch c1 into l_dummy;
119 if c1%found then
120 close c1;
121 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
122 fnd_message.raise_error;
123 end if;
124 --
125 hr_utility.set_location('Leaving:'||l_proc, 15);
126 End chk_name_unique;
127 --
128 -- ----------------------------------------------------------------------- --
129 -- -----------------------< chk_child_records >-----------------------------|
130 -- -------------------------------------------------------------------------
131 --
132 -- Description
133 -- This procedure is used to check that benefit groups do not exist in the
134 -- per_all_people_f table when the user deletes the record in the ben_
135 -- bnfts_grp table.
136 --
137 -- Pre Conditions
138 -- None.
139 --
140 -- In Parameters
141 -- benfts_grp_id PK of record being inserted or updated.
142 --
143 -- Post Success
144 -- Processing continues
145 --
146 -- Post Failure
147 -- Error handled by procedure
148 --
149 -- Access Status
150 -- Internal table handler use only.
151 --
152 procedure chk_child_records(p_benfts_grp_id in number,
153 p_business_Group_id in number) is
154 --
155 l_proc varchar2(72):= g_package||'chk_child_records';
156 v_dummy varchar2(1);
157 --
158 cursor chk_benefits_group is select null
159 from per_all_people_f per
160 where per.benefit_group_id = p_benfts_grp_id
161 and per.business_Group_id= p_business_group_id; /* Perf Bug 4882374 */
162 begin
163 --
164 hr_utility.set_location('Entering:'||l_proc, 5);
165 --
166 -- check if benefit groups exists in the per_all_people_f table
167 --
168 open chk_benefits_group;
169 --
170 -- fetch value from cursor if it returns a record then the
171 -- the user cannot delete the benefits group
172 --
173 fetch chk_benefits_group into v_dummy;
174 if chk_benefits_group%found then
175 close chk_benefits_group;
176 --
177 -- raise error
178 --
179 fnd_message.set_name('BEN','BEN_91740_BG_CHLD_RCD_EXISTS');
180 fnd_message.raise_error;
181 --
182 end if;
183 --
184 close chk_benefits_group;
185 --
186 hr_utility.set_location('Leaving:'||l_proc,10);
187 --
188 end chk_child_records;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< insert_validate >----------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure insert_validate(p_rec in ben_bng_shd.g_rec_type) is
194 --
195 l_proc varchar2(72) := g_package||'insert_validate';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 --
200 -- Call all supporting business operations
201 --
202 --
203 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
204 --
205 chk_benfts_grp_id
206 (p_benfts_grp_id => p_rec.benfts_grp_id,
207 p_object_version_number => p_rec.object_version_number);
208 --
209 chk_name_unique
210 ( p_benfts_grp_id => p_rec.benfts_grp_id
211 ,p_name => p_rec.name
212 ,p_business_group_id => p_rec.business_group_id);
213 --
214 hr_utility.set_location(' Leaving:'||l_proc, 10);
215 End insert_validate;
216 --
217 -- ----------------------------------------------------------------------------
218 -- |---------------------------< update_validate >----------------------------|
219 -- ----------------------------------------------------------------------------
220 Procedure update_validate(p_rec in ben_bng_shd.g_rec_type) is
221 --
222 l_proc varchar2(72) := g_package||'update_validate';
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 --
227 -- Call all supporting business operations
228 --
229 --
230 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
231 --
232 chk_benfts_grp_id
233 (p_benfts_grp_id => p_rec.benfts_grp_id,
234 p_object_version_number => p_rec.object_version_number);
235 --
236 chk_name_unique
237 ( p_benfts_grp_id => p_rec.benfts_grp_id
238 ,p_name => p_rec.name
239 ,p_business_group_id => p_rec.business_group_id);
240 --
241 hr_utility.set_location(' Leaving:'||l_proc, 10);
242 End update_validate;
243 --
244 -- ----------------------------------------------------------------------------
245 -- |---------------------------< delete_validate >----------------------------|
246 -- ----------------------------------------------------------------------------
247 Procedure delete_validate(p_rec in ben_bng_shd.g_rec_type) is
248 --
249 l_proc varchar2(72) := g_package||'delete_validate';
250 --
251 Begin
252 hr_utility.set_location('Entering:'||l_proc, 5);
253 --
254 -- Call all supporting business operations
255 --
256 chk_child_records
257 (p_benfts_grp_id => p_rec.benfts_grp_id,
258 p_business_Group_id => p_rec.business_Group_id);
259 --
260 hr_utility.set_location(' Leaving:'||l_proc, 10);
261 End delete_validate;
262 --
263 --
264 -- ---------------------------------------------------------------------------
265 -- |---------------------< return_legislation_code >-------------------------|
266 -- ---------------------------------------------------------------------------
267 --
268 function return_legislation_code
269 (p_benfts_grp_id in number) return varchar2 is
270 --
271 -- Declare cursor
272 --
273 cursor csr_leg_code is
274 select a.legislation_code
275 from per_business_groups a,
276 ben_benfts_grp b
277 where b.benfts_grp_id = p_benfts_grp_id
278 and a.business_group_id = b.business_group_id;
279 --
280 -- Declare local variables
281 --
282 l_legislation_code varchar2(150);
283 l_proc varchar2(72) := g_package||'return_legislation_code';
284 --
285 begin
286 --
287 hr_utility.set_location('Entering:'|| l_proc, 10);
288 --
289 -- Ensure that all the mandatory parameter are not null
290 --
291 hr_api.mandatory_arg_error(p_api_name => l_proc,
292 p_argument => 'benfts_grp_id',
293 p_argument_value => p_benfts_grp_id);
294 --
295 open csr_leg_code;
296 --
297 fetch csr_leg_code into l_legislation_code;
298 --
299 if csr_leg_code%notfound then
300 --
301 close csr_leg_code;
302 --
303 -- The primary key is invalid therefore we must error
304 --
305 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
306 hr_utility.raise_error;
307 --
308 end if;
309 --
310 close csr_leg_code;
311 --
312 hr_utility.set_location(' Leaving:'|| l_proc, 20);
313 --
314 return l_legislation_code;
315 --
316 end return_legislation_code;
317 --
318 end ben_bng_bus;