DBA Data[Home] [Help]

PACKAGE BODY: APPS.BEN_CBP_BUS

Source


1 Package Body ben_cbp_bus as
2 /* $Header: becbprhi.pkb 120.0 2005/05/28 00:55:24 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  ben_cbp_bus.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_cmbn_ptip_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 --   cmbn_ptip_id PK of record being inserted or updated.
24 --   effective_date Effective Date of session
25 --   object_version_number Object version number of record being
26 --                         inserted or updated.
27 --
28 -- Post Success
29 --   Processing continues
30 --
31 -- Post Failure
32 --   Errors handled by the procedure
33 --
34 -- Access Status
35 --   Internal table handler use only.
36 --
37 Procedure chk_cmbn_ptip_id(p_cmbn_ptip_id                in number,
38                            p_effective_date              in date,
39                            p_object_version_number       in number) is
40   --
41   l_proc         varchar2(72) := g_package||'chk_cmbn_ptip_id';
42   l_api_updating boolean;
43   --
44 Begin
45   --
46   hr_utility.set_location('Entering:'||l_proc, 5);
47   --
48   l_api_updating := ben_cbp_shd.api_updating
49     (p_effective_date              => p_effective_date,
50      p_cmbn_ptip_id                => p_cmbn_ptip_id,
51      p_object_version_number       => p_object_version_number);
52   --
53   if (l_api_updating
54      and nvl(p_cmbn_ptip_id,hr_api.g_number)
55      <>  ben_cbp_shd.g_old_rec.cmbn_ptip_id) then
56     --
57     -- raise error as PK has changed
58     --
59     ben_cbp_shd.constraint_error('BEN_CMBN_PTIP_PK');
60     --
61   elsif not l_api_updating then
62     --
63     -- check if PK is null
64     --
65     if p_cmbn_ptip_id is not null then
66       --
67       -- raise error as PK is not null
68       --
69       ben_cbp_shd.constraint_error('BEN_CMBN_PTIP_PK');
70       --
71     end if;
72     --
73   end if;
74   --
75   hr_utility.set_location('Leaving:'||l_proc, 10);
76   --
77 End chk_cmbn_ptip_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |--------------------------< dt_update_validate >--------------------------|
81 -- ----------------------------------------------------------------------------
82 -- {Start Of Comments}
83 --
84 -- Description:
85 --   This procedure is used for referential integrity of datetracked
86 --   parent entities when a datetrack update operation is taking place
87 --   and where there is no cascading of update defined for this entity.
88 --
89 -- Prerequisites:
90 --   This procedure is called from the update_validate.
91 --
92 -- In Parameters:
93 --
94 -- Post Success:
95 --   Processing continues.
96 --
97 -- Post Failure:
98 --
99 -- Developer Implementation Notes:
100 --   This procedure should not need maintenance unless the HR Schema model
101 --   changes.
102 --
103 -- Access Status:
104 --   Internal Row Handler Use Only.
105 --
106 -- {End Of Comments}
107 -- ----------------------------------------------------------------------------
108 Procedure dt_update_validate
109             (p_pgm_id                        in number default hr_api.g_number,
110 	     p_datetrack_mode		     in varchar2,
111              p_validation_start_date	     in date,
112 	     p_validation_end_date	     in date) Is
113 --
114   l_proc	    varchar2(72) := g_package||'dt_update_validate';
115   l_integrity_error Exception;
116   l_table_name	    all_tables.table_name%TYPE;
117 --
118 Begin
119   hr_utility.set_location('Entering:'||l_proc, 5);
120   --
121   -- Ensure that the p_datetrack_mode argument is not null
122   --
123   hr_api.mandatory_arg_error
124     (p_api_name       => l_proc,
125      p_argument       => 'datetrack_mode',
126      p_argument_value => p_datetrack_mode);
127   --
128   -- Only perform the validation if the datetrack update mode is valid
129   --
130   If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
131     --
132     --
133     -- Ensure the arguments are not null
134     --
135     hr_api.mandatory_arg_error
136       (p_api_name       => l_proc,
137        p_argument       => 'validation_start_date',
138        p_argument_value => p_validation_start_date);
139     --
140     hr_api.mandatory_arg_error
141       (p_api_name       => l_proc,
142        p_argument       => 'validation_end_date',
143        p_argument_value => p_validation_end_date);
144     --
145     If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
146       NOT (dt_api.check_min_max_dates
147             (p_base_table_name => 'ben_pgm_f',
148              p_base_key_column => 'pgm_id',
149              p_base_key_value  => p_pgm_id,
150              p_from_date       => p_validation_start_date,
151              p_to_date         => p_validation_end_date)))  Then
152       l_table_name := 'ben_pgm_f';
153       Raise l_integrity_error;
154     End If;
155     --
156   End If;
157   --
158   hr_utility.set_location(' Leaving:'||l_proc, 10);
159 Exception
160   When l_integrity_error Then
161     --
162     -- A referential integrity check was violated therefore
163     -- we must error
164     --
165     hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
166     hr_utility.set_message_token('TABLE_NAME', l_table_name);
167     hr_utility.raise_error;
168   When Others Then
169     --
170     -- An unhandled or unexpected error has occurred which
171     -- we must report
172     --
173     hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
174     hr_utility.set_message_token('PROCEDURE', l_proc);
175     hr_utility.set_message_token('STEP','15');
176     hr_utility.raise_error;
177 End dt_update_validate;
178 --
179 -- ----------------------------------------------------------------------------
180 -- |--------------------------< dt_delete_validate >--------------------------|
181 -- ----------------------------------------------------------------------------
182 -- {Start Of Comments}
183 --
184 -- Description:
185 --   This procedure is used for referential integrity of datetracked
186 --   child entities when either a datetrack DELETE or ZAP is in operation
187 --   and where there is no cascading of delete defined for this entity.
188 --   For the datetrack mode of DELETE or ZAP we must ensure that no
189 --   datetracked child rows exist between the validation start and end
190 --   dates.
191 --
192 -- Prerequisites:
193 --   This procedure is called from the delete_validate.
194 --
195 -- In Parameters:
196 --
197 -- Post Success:
198 --   Processing continues.
199 --
200 -- Post Failure:
201 --   If a row exists by determining the returning Boolean value from the
202 --   generic dt_api.rows_exist function then we must supply an error via
203 --   the use of the local exception handler l_rows_exist.
204 --
205 -- Developer Implementation Notes:
206 --   This procedure should not need maintenance unless the HR Schema model
207 --   changes.
208 --
209 -- Access Status:
210 --   Internal Row Handler Use Only.
211 --
212 -- {End Of Comments}
213 -- ----------------------------------------------------------------------------
214 Procedure dt_delete_validate
215             (p_cmbn_ptip_id		in number,
216              p_datetrack_mode		in varchar2,
217 	     p_validation_start_date	in date,
218 	     p_validation_end_date	in date) Is
219 --
220   l_proc	varchar2(72) 	:= g_package||'dt_delete_validate';
221   l_rows_exist	Exception;
222   l_table_name	all_tables.table_name%TYPE;
223 --
224 Begin
225   hr_utility.set_location('Entering:'||l_proc, 5);
226   --
227   -- Ensure that the p_datetrack_mode argument is not null
228   --
229   hr_api.mandatory_arg_error
230     (p_api_name       => l_proc,
231      p_argument       => 'datetrack_mode',
232      p_argument_value => p_datetrack_mode);
233   --
234   -- Only perform the validation if the datetrack mode is either
235   -- DELETE or ZAP
236   --
237   If (p_datetrack_mode = 'DELETE' or
238       p_datetrack_mode = 'ZAP') then
239     --
240     --
241     -- Ensure the arguments are not null
242     --
243     hr_api.mandatory_arg_error
244       (p_api_name       => l_proc,
245        p_argument       => 'validation_start_date',
246        p_argument_value => p_validation_start_date);
247     --
248     hr_api.mandatory_arg_error
249       (p_api_name       => l_proc,
250        p_argument       => 'validation_end_date',
251        p_argument_value => p_validation_end_date);
252     --
253     hr_api.mandatory_arg_error
254       (p_api_name       => l_proc,
255        p_argument       => 'cmbn_ptip_id',
256        p_argument_value => p_cmbn_ptip_id);
257     --
258     If (dt_api.rows_exist
259           (p_base_table_name => 'ben_ptip_f',
260            p_base_key_column => 'cmbn_ptip_id',
261            p_base_key_value  => p_cmbn_ptip_id,
262            p_from_date       => p_validation_start_date,
263            p_to_date         => p_validation_end_date)) Then
264       l_table_name := 'ben_ptip_f';
265       Raise l_rows_exist;
266     End If;
267     If (dt_api.rows_exist
268           (p_base_table_name => 'ben_bnft_prvdr_pool_f',
269            p_base_key_column => 'cmbn_ptip_id',
270            p_base_key_value  => p_cmbn_ptip_id,
271            p_from_date       => p_validation_start_date,
272            p_to_date         => p_validation_end_date)) Then
273       l_table_name := 'ben_bnft_prvdr_pool_f';
274       Raise l_rows_exist;
275     End If;
276     If (dt_api.rows_exist
277           (p_base_table_name => 'ben_acty_base_rt_f',
278            p_base_key_column => 'cmbn_ptip_id',
279            p_base_key_value  => p_cmbn_ptip_id,
280            p_from_date       => p_validation_start_date,
281            p_to_date         => p_validation_end_date)) Then
282       l_table_name := 'ben_acty_base_rt_f';
283       Raise l_rows_exist;
284     End If;
285     --
286   End If;
287   --
288   hr_utility.set_location(' Leaving:'||l_proc, 10);
289 Exception
290   When l_rows_exist Then
291     --
292     -- A referential integrity check was violated therefore
293     -- we must error
294     --
295     hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
296     hr_utility.set_message_token('TABLE_NAME', l_table_name);
297     hr_utility.raise_error;
298   When Others Then
299     --
300     -- An unhandled or unexpected error has occurred which
301     -- we must report
302     --
303     hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
304     hr_utility.set_message_token('PROCEDURE', l_proc);
305     hr_utility.set_message_token('STEP','15');
306     hr_utility.raise_error;
307 End dt_delete_validate;
308 --
309 -- ----------------------------------------------------------------------------
310 -- |---------------------------< insert_validate >----------------------------|
311 -- ----------------------------------------------------------------------------
312 Procedure insert_validate
313 	(p_rec 			 in ben_cbp_shd.g_rec_type,
314 	 p_effective_date	 in date,
315 	 p_datetrack_mode	 in varchar2,
316 	 p_validation_start_date in date,
317 	 p_validation_end_date	 in date) is
318 --
319   l_proc	varchar2(72) := g_package||'insert_validate';
320 --
321 Begin
322   hr_utility.set_location('Entering:'||l_proc, 5);
323   --
324   -- Call all supporting business operations
325   --
326   --
327   hr_api.validate_bus_grp_id(p_rec.business_group_id);  -- Validate Bus Grp
328   --
329   chk_cmbn_ptip_id
330   (p_cmbn_ptip_id          => p_rec.cmbn_ptip_id,
331    p_effective_date        => p_effective_date,
332    p_object_version_number => p_rec.object_version_number);
333   --
334   hr_utility.set_location(' Leaving:'||l_proc, 10);
335 End insert_validate;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |---------------------------< update_validate >----------------------------|
339 -- ----------------------------------------------------------------------------
340 Procedure update_validate
341 	(p_rec 			 in ben_cbp_shd.g_rec_type,
342 	 p_effective_date	 in date,
343 	 p_datetrack_mode	 in varchar2,
344 	 p_validation_start_date in date,
345 	 p_validation_end_date	 in date) is
346 --
347   l_proc	varchar2(72) := g_package||'update_validate';
348 --
349 Begin
350   hr_utility.set_location('Entering:'||l_proc, 5);
351   --
352   -- Call all supporting business operations
353   --
354   --
355   hr_api.validate_bus_grp_id(p_rec.business_group_id);  -- Validate Bus Grp
356   --
357   chk_cmbn_ptip_id
358   (p_cmbn_ptip_id          => p_rec.cmbn_ptip_id,
359    p_effective_date        => p_effective_date,
360    p_object_version_number => p_rec.object_version_number);
361   --
362   -- Call the datetrack update integrity operation
363   --
364   dt_update_validate
365     (p_pgm_id                        => p_rec.pgm_id,
366      p_datetrack_mode                => p_datetrack_mode,
367      p_validation_start_date	     => p_validation_start_date,
368      p_validation_end_date	     => p_validation_end_date);
369   --
370   hr_utility.set_location(' Leaving:'||l_proc, 10);
371 End update_validate;
372 --
373 -- ----------------------------------------------------------------------------
374 -- |---------------------------< delete_validate >----------------------------|
375 -- ----------------------------------------------------------------------------
376 Procedure delete_validate
377 	(p_rec 			 in ben_cbp_shd.g_rec_type,
378 	 p_effective_date	 in date,
379 	 p_datetrack_mode	 in varchar2,
380 	 p_validation_start_date in date,
381 	 p_validation_end_date	 in date) is
382 --
383   l_proc	varchar2(72) := g_package||'delete_validate';
384 --
385 Begin
386   hr_utility.set_location('Entering:'||l_proc, 5);
387   --
388   -- Call all supporting business operations
389   --
390   dt_delete_validate
391     (p_datetrack_mode		=> p_datetrack_mode,
392      p_validation_start_date	=> p_validation_start_date,
393      p_validation_end_date	=> p_validation_end_date,
394      p_cmbn_ptip_id		=> p_rec.cmbn_ptip_id);
395   --
396   hr_utility.set_location(' Leaving:'||l_proc, 10);
397 End delete_validate;
398 --
399 --
400 --  ---------------------------------------------------------------------------
401 --  |---------------------< return_legislation_code >-------------------------|
402 --  ---------------------------------------------------------------------------
403 --
404 function return_legislation_code
405   (p_cmbn_ptip_id in number) return varchar2 is
406   --
407   -- Declare cursor
408   --
409   cursor csr_leg_code is
410     select a.legislation_code
411     from   per_business_groups a,
412            ben_cmbn_ptip_f b
413     where b.cmbn_ptip_id      = p_cmbn_ptip_id
414     and   a.business_group_id = b.business_group_id;
415   --
416   -- Declare local variables
417   --
418   l_legislation_code  varchar2(150);
419   l_proc              varchar2(72)  :=  g_package||'return_legislation_code';
420   --
421 begin
422   --
423   hr_utility.set_location('Entering:'|| l_proc, 10);
424   --
428                              p_argument       => 'cmbn_ptip_id',
425   -- Ensure that all the mandatory parameter are not null
426   --
427   hr_api.mandatory_arg_error(p_api_name       => l_proc,
429                              p_argument_value => p_cmbn_ptip_id);
430   --
431   open csr_leg_code;
432     --
433     fetch csr_leg_code into l_legislation_code;
434     --
435     if csr_leg_code%notfound then
436       --
437       close csr_leg_code;
438       --
439       -- The primary key is invalid therefore we must error
440       --
441       hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
442       hr_utility.raise_error;
443       --
444     end if;
445     --
446   close csr_leg_code;
447   --
448   hr_utility.set_location(' Leaving:'|| l_proc, 20);
449   --
450   return l_legislation_code;
451   --
452 end return_legislation_code;
453 --
454 end ben_cbp_bus;