1 Package Body pqh_vlp_bus as
2 /* $Header: pqvlprhi.pkb 115.6 2004/03/31 00:31:40 kgowripe noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_vlp_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_validation_period_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_validation_period_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pqh_fr_validation_periods vlp
31 , pqh_fr_validations vld
32 where vld.validation_id = vlp.validation_id
33 and vlp.validation_period_id = p_validation_period_id
34 and pbg.business_group_id = vld.business_group_id;
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'validation_period_id'
50 ,p_argument_value => p_validation_period_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 hr_multi_message.add
64 (p_associated_column1
65 => nvl(p_associated_column1,'VALIDATION_PERIOD_ID')
66 );
67 --
68 else
69 close csr_sec_grp;
70 --
71 -- Set the security_group_id in CLIENT_INFO
72 --
73 hr_api.set_security_group_id
74 (p_security_group_id => l_security_group_id
75 );
76 end if;
77 --
78 hr_utility.set_location(' Leaving:'|| l_proc, 20);
79 --
80 end set_security_group_id;
81 --
82 -- ---------------------------------------------------------------------------
83 -- |---------------------< return_legislation_code >-------------------------|
84 -- ---------------------------------------------------------------------------
85 --
86 Function return_legislation_code
87 (p_validation_period_id in number
88 )
89 Return Varchar2 Is
90 --
91 -- Declare cursor
92 --
93 cursor csr_leg_code is
94 select pbg.legislation_code
95 from per_business_groups pbg
96 , pqh_fr_validation_periods vlp
97 , pqh_Fr_validations vld
98 where vld.validation_id = vlp.validation_id
99 and vlp.validation_period_id = p_validation_period_id
100 and pbg.business_group_id = vld.business_group_id;
101 --
102 -- Declare local variables
103 --
104 l_legislation_code varchar2(150);
105 l_proc varchar2(72) := g_package||'return_legislation_code';
106 --
107 Begin
108 --
109 hr_utility.set_location('Entering:'|| l_proc, 10);
110 --
111 -- Ensure that all the mandatory parameter are not null
112 --
113 hr_api.mandatory_arg_error
114 (p_api_name => l_proc
115 ,p_argument => 'validation_period_id'
116 ,p_argument_value => p_validation_period_id
117 );
118 --
119 if ( nvl(pqh_vlp_bus.g_validation_period_id, hr_api.g_number)
120 = p_validation_period_id) then
121 --
122 -- The legislation code has already been found with a previous
123 -- call to this function. Just return the value in the global
124 -- variable.
125 --
126 l_legislation_code := pqh_vlp_bus.g_legislation_code;
127 hr_utility.set_location(l_proc, 20);
128 else
129 --
130 -- The ID is different to the last call to this function
131 -- or this is the first call to this function.
132 --
133 open csr_leg_code;
134 fetch csr_leg_code into l_legislation_code;
135 --
136 if csr_leg_code%notfound then
137 --
138 -- The primary key is invalid therefore we must error
139 --
140 close csr_leg_code;
141 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
142 fnd_message.raise_error;
143 end if;
144 hr_utility.set_location(l_proc,30);
145 --
146 -- Set the global variables so the values are
147 -- available for the next call to this function.
148 --
149 close csr_leg_code;
150 pqh_vlp_bus.g_validation_period_id := p_validation_period_id;
151 pqh_vlp_bus.g_legislation_code := l_legislation_code;
152 end if;
153 hr_utility.set_location(' Leaving:'|| l_proc, 40);
154 return l_legislation_code;
155 end return_legislation_code;
156 --
157 -- ----------------------------------------------------------------------------
158 -- |-----------------------< chk_non_updateable_args >------------------------|
159 -- ----------------------------------------------------------------------------
160 -- {Start Of Comments}
161 --
162 -- Description:
163 -- This procedure is used to ensure that non updateable attributes have
164 -- not been updated. If an attribute has been updated an error is generated.
165 --
166 -- Pre Conditions:
167 -- g_old_rec has been populated with details of the values currently in
168 -- the database.
169 --
170 -- In Arguments:
171 -- p_rec has been populated with the updated values the user would like the
172 -- record set to.
173 --
174 -- Post Success:
175 -- Processing continues if all the non updateable attributes have not
176 -- changed.
177 --
178 -- Post Failure:
179 -- An application error is raised if any of the non updatable attributes
180 -- have been altered.
181 --
182 -- {End Of Comments}
183 -- ----------------------------------------------------------------------------
184 Procedure chk_non_updateable_args
185 (p_effective_date in date
186 ,p_rec in pqh_vlp_shd.g_rec_type
187 ) IS
188 --
189 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
190 --
191 Begin
192 --
193 -- Only proceed with the validation if a row exists for the current
194 -- record in the HR Schema.
195 --
196 IF NOT pqh_vlp_shd.api_updating
197 (p_validation_period_id => p_rec.validation_period_id
198 ,p_object_version_number => p_rec.object_version_number
199 ) THEN
200 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
201 fnd_message.set_token('PROCEDURE ', l_proc);
202 fnd_message.set_token('STEP ', '5');
203 fnd_message.raise_error;
204 END IF;
205 --
206 End chk_non_updateable_args;
207 --
208 -- ----------------------------------------------------------------------------
209 -- |---------------------------< insert_validate >----------------------------|
210 -- ----------------------------------------------------------------------------
211 Procedure insert_validate
212 (p_effective_date in date
213 ,p_rec in pqh_vlp_shd.g_rec_type
214 ) is
215 --
216 l_proc varchar2(72) := g_package||'insert_validate';
217 --
218 cnt number;
219 Begin
220 hr_utility.set_location('Entering:'||l_proc, 5);
221 --
222 -- Call all supporting business operations
223 --
224 --
225 pqh_vld_bus.set_security_group_id(p_rec.validation_id, null);
226 --
227 -- Validate Dependent Attributes
228 --
229 --
230 select count(*) into cnt from pqh_fr_validation_periods
231 where previous_employer_id = p_rec.previous_employer_id;
232 if cnt > 0 then
233 -- p_rec.start_date
234 select count(*) into cnt from pqh_fr_validation_periods
235 where previous_employer_id = p_rec.previous_employer_id
236 and
237 nvl(p_rec.start_date, hr_general.start_of_time)
238 between
239 nvl(start_date, hr_general.start_of_time)
240 and
241 nvl(end_date, hr_general.end_of_time);
242
243 if cnt > 0 then
244 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
245 fnd_message.raise_error;
246 end if;
247 -- for p_rec.end_date
248 select count(*) into cnt from pqh_fr_validation_periods
249 where previous_employer_id = p_rec.previous_employer_id
250 and
251 nvl(p_rec.end_date, hr_general.end_of_time)
252 between
253 nvl(start_date, hr_general.start_of_time)
254 and
255 nvl(end_date, hr_general.end_of_time);
256
257 if cnt > 0 then
258 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
259 fnd_message.raise_error;
260 end if;
261 --for start_date
262 select count(*) into cnt from pqh_fr_validation_periods
263 where previous_employer_id = p_rec.previous_employer_id
264 and
265 nvl(start_date, hr_general.start_of_time)
266 between
267 nvl(p_rec.start_date, hr_general.start_of_time)
268 and
269 nvl(p_rec.end_date, hr_general.end_of_time);
270
271 if cnt > 0 then
272 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
273 fnd_message.raise_error;
274 end if;
275 -- for end_date
276 select count(*) into cnt from pqh_fr_validation_periods
277 where previous_employer_id = p_rec.previous_employer_id
278 and
279 nvl(end_date, hr_general.end_of_time)
280 between
281 nvl(p_rec.start_date, hr_general.start_of_time)
282 and
283 nvl(p_rec.end_date, hr_general.end_of_time);
284
285 if cnt > 0 then
286 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
287 fnd_message.raise_error;
288 end if;
289 end if;
290 hr_utility.set_location(' Leaving:'||l_proc, 10);
291 End insert_validate;
292 --
293 -- ----------------------------------------------------------------------------
294 -- |---------------------------< update_validate >----------------------------|
295 -- ----------------------------------------------------------------------------
296 Procedure update_validate
297 (p_effective_date in date
298 ,p_rec in pqh_vlp_shd.g_rec_type
299 ) is
300 --
301 l_proc varchar2(72) := g_package||'update_validate';
302 --
303 cnt number;
304 Begin
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 --
307 -- Call all supporting business operations
308 --
309 --
310 pqh_vld_bus.set_security_group_id(p_rec.validation_id, null);
311 --
312 -- Validate Dependent Attributes
313 --
314 chk_non_updateable_args
315 (p_effective_date => p_effective_date
316 ,p_rec => p_rec
317 );
318 --
319 select count(*) into cnt from pqh_fr_validation_periods
320 where previous_employer_id = p_rec.previous_employer_id
321 and validation_period_id <> p_rec.validation_period_id;
322 if cnt > 0 then
323 -- p_rec.start_date
324 select count(*) into cnt from pqh_fr_validation_periods
325 where previous_employer_id = p_rec.previous_employer_id
326 and validation_period_id <> p_rec.validation_period_id
327 and
328 nvl(p_rec.start_date, hr_general.start_of_time)
329 between
330 nvl(start_date, hr_general.start_of_time)
331 and
332 nvl(end_date, hr_general.end_of_time);
333
334 if cnt > 0 then
335 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
336 fnd_message.raise_error;
337 end if;
338 -- for p_rec.end_date
339 select count(*) into cnt from pqh_fr_validation_periods
340 where previous_employer_id = p_rec.previous_employer_id
341 and validation_period_id <> p_rec.validation_period_id
342 and
343 nvl(p_rec.end_date, hr_general.end_of_time)
344 between
345 nvl(start_date, hr_general.start_of_time)
346 and
347 nvl(end_date, hr_general.end_of_time);
348
349 if cnt > 0 then
350 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
351 fnd_message.raise_error;
352 end if;
353 --for start_date
354 select count(*) into cnt from pqh_fr_validation_periods
355 where previous_employer_id = p_rec.previous_employer_id
356 and validation_period_id <> p_rec.validation_period_id
357 and
358 nvl(start_date, hr_general.start_of_time)
359 between
360 nvl(p_rec.start_date, hr_general.start_of_time)
361 and
362 nvl(p_rec.end_date, hr_general.end_of_time);
363
364 if cnt > 0 then
365 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
366 fnd_message.raise_error;
367 end if;
368 -- for end_date
369 select count(*) into cnt from pqh_fr_validation_periods
370 where previous_employer_id = p_rec.previous_employer_id
371 and validation_period_id <> p_rec.validation_period_id
372 and
373 nvl(end_date, hr_general.end_of_time)
374 between
375 nvl(p_rec.start_date, hr_general.start_of_time)
376 and
377 nvl(p_rec.end_date, hr_general.end_of_time);
378
379 if cnt > 0 then
380 fnd_message.set_name('PQH', 'FR_PQH_VALIDATION_OVERLAP');
381 fnd_message.raise_error;
382 end if;
383 end if;
384 --
385 hr_utility.set_location(' Leaving:'||l_proc, 10);
386 End update_validate;
387 --
388 -- ----------------------------------------------------------------------------
389 -- |---------------------------< delete_validate >----------------------------|
390 -- ----------------------------------------------------------------------------
391 Procedure delete_validate
392 (p_rec in pqh_vlp_shd.g_rec_type
393 ) is
394 --
395 l_proc varchar2(72) := g_package||'delete_validate';
396 --
397 Begin
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 -- Call all supporting business operations
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End delete_validate;
404 --
405 end pqh_vlp_bus;
[Home] [Help]