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