[Home] [Help]
PACKAGE BODY: APPS.BEN_DSQ_BUS
Source
1 Package Body ben_dsq_bus as
2 /* $Header: bedsqrhi.pkb 115.7 2002/12/09 12:49:41 lakrish ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_dsq_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------< chk_ded_sched_py_freq_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 -- ded_sched_py_freq_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_ded_sched_py_freq_id
37 (p_ded_sched_py_freq_id in number,
38 p_object_version_number in number) is
39 --
40 l_proc varchar2(72) := g_package||'chk_ded_sched_py_freq_id';
41 l_api_updating boolean;
42 --
43 Begin
44 --
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 l_api_updating := ben_dsq_shd.api_updating
48 (p_ded_sched_py_freq_id => p_ded_sched_py_freq_id,
49 p_object_version_number => p_object_version_number);
50 --
51 if (l_api_updating
52 and nvl(p_ded_sched_py_freq_id,hr_api.g_number)
53 <> ben_dsq_shd.g_old_rec.ded_sched_py_freq_id) then
54 --
55 -- raise error as PK has changed
56 --
57 ben_dsq_shd.constraint_error('BEN_DED_SCHED_PY_FREQ_PK');
58 --
59 elsif not l_api_updating then
60 --
61 -- check if PK is null
62 --
63 if p_ded_sched_py_freq_id is not null then
64 --
65 -- raise error as PK is not null
66 --
67 ben_dsq_shd.constraint_error('BEN_DED_SCHED_PY_FREQ_PK');
68 --
69 end if;
70 --
71 end if;
72 --
73 hr_utility.set_location('Leaving:'||l_proc, 10);
74 --
75 End chk_ded_sched_py_freq_id;
76 --
77 -- ----------------------------------------------------------------------------
78 -- |--------------------------< chk_dflt_flag >-------------------------------|
79 -- ----------------------------------------------------------------------------
80 --
81 -- Description
82 -- This procedure is used to check that the lookup value is valid.
83 --
84 -- Pre Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- ded_sched_py_freq_id PK of record being inserted or updated.
89 -- dflt_flag Value of lookup code.
90 -- effective_date effective date
91 -- object_version_number Object version number of record being
92 -- inserted or updated.
93 --
94 -- Post Success
95 -- Processing continues
96 --
97 -- Post Failure
98 -- Error handled by procedure
99 --
100 -- Access Status
101 -- Internal table handler use only.
102 --
103 Procedure chk_dflt_flag(p_ded_sched_py_freq_id in number,
104 p_dflt_flag in varchar2,
105 p_effective_date in date,
106 p_object_version_number in number) is
107 --
108 l_proc varchar2(72) := g_package||'chk_dflt_flag';
109 l_api_updating boolean;
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 l_api_updating := ben_dsq_shd.api_updating
116 (p_ded_sched_py_freq_id => p_ded_sched_py_freq_id,
117 p_object_version_number => p_object_version_number);
118 --
119 if (l_api_updating
120 and p_dflt_flag
121 <> nvl(ben_dsq_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
122 or not l_api_updating)
123 and p_dflt_flag is not null then
124 --
125 -- check if value of lookup falls within lookup type.
126 --
127 if hr_api.not_exists_in_hr_lookups
128 (p_lookup_type => 'YES_NO',
129 p_lookup_code => p_dflt_flag,
130 p_effective_date => p_effective_date) then
131 --
132 -- raise error as does not exist as lookup
133 --
134 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
135 fnd_message.raise_error;
136 --
137 end if;
138 --
139 end if;
140 --
141 hr_utility.set_location('Leaving:'||l_proc,10);
142 --
143 end chk_dflt_flag;
144 --
145 /*
146 -- ----------------------------------------------------------------------------
147 -- |--------------------------< chk_pymt_sched_cd >---------------------------|
148 -- ----------------------------------------------------------------------------
149 --
150 -- Description
151 -- This procedure is used to check that the lookup value is valid.
152 --
153 -- Pre Conditions
154 -- None.
155 --
156 -- In Parameters
157 -- ded_sched_py_freq_id PK of record being inserted or updated.
158 -- pymt_sched_cd Value of lookup code.
159 -- effective_date effective date
160 -- object_version_number Object version number of record being
161 -- inserted or updated.
162 --
163 -- Post Success
164 -- Processing continues
165 --
166 -- Post Failure
167 -- Error handled by procedure
168 --
169 -- Access Status
170 -- Internal table handler use only.
171 --
172 Procedure chk_pymt_sched_cd(p_ded_sched_py_freq_id in number,
173 p_pymt_sched_cd in varchar2,
174 p_effective_date in date,
175 p_object_version_number in number) is
176 --
177 l_proc varchar2(72) := g_package||'chk_pymt_sched_cd';
178 l_api_updating boolean;
179 --
180 Begin
181 --
182 hr_utility.set_location('Entering:'||l_proc, 5);
183 --
184 l_api_updating := ben_dsq_shd.api_updating
185 (p_ded_sched_py_freq_id => p_ded_sched_py_freq_id,
186 p_object_version_number => p_object_version_number);
187 --
188 if (l_api_updating
189 and p_pymt_sched_cd
190 <> nvl(ben_dsq_shd.g_old_rec.pymt_sched_cd,hr_api.g_varchar2)
191 or not l_api_updating) then
192 --
193 -- check if value of lookup falls within lookup type.
194 --
195 --
196 if p_pymt_sched_cd is not null then
197 if hr_api.not_exists_in_hr_lookups
198 (p_lookup_type => 'BEN_PYMT_SCHED',
199 p_lookup_code => p_pymt_sched_cd,
200 p_effective_date => p_effective_date) then
201 --
202 -- raise error as does not exist as lookup
203 --
204 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
205 fnd_message.raise_error;
206 --
207 end if;
208 end if;
209 --
210 end if;
211 --
212 hr_utility.set_location('Leaving:'||l_proc,10);
213 --
214 end chk_pymt_sched_cd;
215 --
216 */
217 -- ----------------------------------------------------------------------------
218 -- |--------------------------< chk_py_freq_cd >------------------------------|
219 -- ----------------------------------------------------------------------------
220 --
221 -- Description
222 -- This procedure is used to check that the lookup value is valid.
223 --
224 -- Pre Conditions
225 -- None.
226 --
227 -- In Parameters
228 -- ded_sched_py_freq_id PK of record being inserted or updated.
229 -- py_freq_cd Value of lookup code.
230 -- effective_date effective date
231 -- object_version_number Object version number of record being
232 -- inserted or updated.
233 --
234 -- Post Success
235 -- Processing continues
236 --
237 -- Post Failure
238 -- Error handled by procedure
239 --
240 -- Access Status
241 -- Internal table handler use only.
242 --
243 Procedure chk_py_freq_cd(p_ded_sched_py_freq_id in number,
244 p_py_freq_cd in varchar2,
245 p_effective_date in date,
246 p_object_version_number in number) is
247 --
248 l_proc varchar2(72) := g_package||'chk_py_freq_cd';
249 l_api_updating boolean;
250 --
251 Begin
252 --
253 hr_utility.set_location('Entering:'||l_proc, 5);
254 --
255 l_api_updating := ben_dsq_shd.api_updating
256 (p_ded_sched_py_freq_id => p_ded_sched_py_freq_id,
257 p_object_version_number => p_object_version_number);
258 --
259 if (l_api_updating
260 and p_py_freq_cd
261 <> nvl(ben_dsq_shd.g_old_rec.py_freq_cd,hr_api.g_varchar2)
262 or not l_api_updating) then
263 --
264 -- check if value of lookup falls within lookup type.
265 --
266 --
267 if p_py_freq_cd is not null then
268 if hr_api.not_exists_in_hr_lookups
269 (p_lookup_type => 'BEN_FREQ',
270 p_lookup_code => p_py_freq_cd,
271 p_effective_date => p_effective_date) then
272 --
273 -- raise error as does not exist as lookup
274 --
275 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
276 fnd_message.raise_error;
277 --
278 end if;
279 end if;
280 --
281 end if;
282 --
283 hr_utility.set_location('Leaving:'||l_proc,10);
284 --
285 end chk_py_freq_cd;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |---------------------------< insert_validate >----------------------------|
289 -- ----------------------------------------------------------------------------
290 Procedure insert_validate(p_rec in ben_dsq_shd.g_rec_type
291 ,p_effective_date in date) is
292 --
293 l_proc varchar2(72) := g_package||'insert_validate';
294 --
295 Begin
296 hr_utility.set_location('Entering:'||l_proc, 5);
297 --
298 -- Call all supporting business operations
299 --
300 --
301 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
302 --
303 chk_ded_sched_py_freq_id
304 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
305 p_object_version_number => p_rec.object_version_number);
306 --
307 chk_dflt_flag
308 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
309 p_dflt_flag => p_rec.dflt_flag,
310 p_effective_date => p_effective_date,
311 p_object_version_number => p_rec.object_version_number);
312 --
313 /*
314 chk_pymt_sched_cd
315 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
316 p_pymt_sched_cd => p_rec.pymt_sched_cd,
317 p_effective_date => p_effective_date,
318 p_object_version_number => p_rec.object_version_number);
319 */
320 --
321 chk_py_freq_cd
322 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
323 p_py_freq_cd => p_rec.py_freq_cd,
324 p_effective_date => p_effective_date,
325 p_object_version_number => p_rec.object_version_number);
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 10);
328 End insert_validate;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |---------------------------< update_validate >----------------------------|
332 -- ----------------------------------------------------------------------------
333 Procedure update_validate(p_rec in ben_dsq_shd.g_rec_type
334 ,p_effective_date in date) is
335 --
336 l_proc varchar2(72) := g_package||'update_validate';
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Call all supporting business operations
342 --
343 --
344 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
345 --
346 chk_ded_sched_py_freq_id
347 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
348 p_object_version_number => p_rec.object_version_number);
349 --
350 chk_dflt_flag
351 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
352 p_dflt_flag => p_rec.dflt_flag,
353 p_effective_date => p_effective_date,
354 p_object_version_number => p_rec.object_version_number);
355 --
356 /*
357 chk_pymt_sched_cd
358 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
359 p_pymt_sched_cd => p_rec.pymt_sched_cd,
360 p_effective_date => p_effective_date,
361 p_object_version_number => p_rec.object_version_number);
362 */
363 --
364 chk_py_freq_cd
365 (p_ded_sched_py_freq_id => p_rec.ded_sched_py_freq_id,
366 p_py_freq_cd => p_rec.py_freq_cd,
367 p_effective_date => p_effective_date,
368 p_object_version_number => p_rec.object_version_number);
369 --
370 hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< delete_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 Procedure delete_validate(p_rec in ben_dsq_shd.g_rec_type
377 ,p_effective_date in date) is
378 --
379 l_proc varchar2(72) := g_package||'delete_validate';
380 --
381 Begin
382 hr_utility.set_location('Entering:'||l_proc, 5);
383 --
384 -- Call all supporting business operations
385 --
386 hr_utility.set_location(' Leaving:'||l_proc, 10);
387 End delete_validate;
388 --
389 --
390 -- ---------------------------------------------------------------------------
391 -- |---------------------< return_legislation_code >-------------------------|
392 -- ---------------------------------------------------------------------------
393 --
394 function return_legislation_code
395 (p_ded_sched_py_freq_id in number) return varchar2 is
396 --
397 -- Declare cursor
398 --
399 cursor csr_leg_code is
400 select a.legislation_code
401 from per_business_groups a,
402 ben_ded_sched_py_freq b
403 where b.ded_sched_py_freq_id = p_ded_sched_py_freq_id
404 and a.business_group_id = b.business_group_id;
405 --
406 -- Declare local variables
407 --
408 l_legislation_code varchar2(150);
409 l_proc varchar2(72) := g_package||'return_legislation_code';
410 --
411 begin
412 --
413 hr_utility.set_location('Entering:'|| l_proc, 10);
414 --
415 -- Ensure that all the mandatory parameter are not null
416 --
417 hr_api.mandatory_arg_error(p_api_name => l_proc,
418 p_argument => 'ded_sched_py_freq_id',
419 p_argument_value => p_ded_sched_py_freq_id);
420 --
421 open csr_leg_code;
422 --
423 fetch csr_leg_code into l_legislation_code;
424 --
425 if csr_leg_code%notfound then
426 --
427 close csr_leg_code;
428 --
429 -- The primary key is invalid therefore we must error
430 --
431 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
432 fnd_message.raise_error;
433 --
434 end if;
435 --
436 close csr_leg_code;
437 --
438 hr_utility.set_location(' Leaving:'|| l_proc, 20);
439 --
440 return l_legislation_code;
441 --
442 end return_legislation_code;
443 --
444 end ben_dsq_bus;