1 Package Body pay_dte_bus as
2 /* $Header: pydterhi.pkb 115.6 2002/12/06 16:15:56 jford noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_dte_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_datetracked_event_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_datetracked_event_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_datetracked_events dte
30 where dte.datetracked_event_id = p_datetracked_event_id
31 and pbg.business_group_id = dte.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'datetracked_event_id'
47 ,p_argument_value => p_datetracked_event_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_datetracked_event_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pay_datetracked_events dte
90 where dte.datetracked_event_id = p_datetracked_event_id
91 and pbg.business_group_id (+) = dte.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'datetracked_event_id'
107 ,p_argument_value => p_datetracked_event_id
108 );
109 --
110 if ( nvl(pay_dte_bus.g_datetracked_event_id, hr_api.g_number)
111 = p_datetracked_event_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pay_dte_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pay_dte_bus.g_datetracked_event_id:= p_datetracked_event_id;
142 pay_dte_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in pay_dte_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 --
184 Begin
185 --
186 -- Only proceed with the validation if a row exists for the current
187 -- record in the HR Schema.
188 --
189 IF NOT pay_dte_shd.api_updating
190 (p_datetracked_event_id => p_rec.datetracked_event_id
191 ,p_object_version_number => p_rec.object_version_number
192 ) THEN
193 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
194 fnd_message.set_token('PROCEDURE ', l_proc);
195 fnd_message.set_token('STEP ', '5');
196 fnd_message.raise_error;
197 END IF;
198 --
199 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
200 nvl(pay_dte_shd.g_old_rec.legislation_code, hr_api.g_varchar2)
201 then
202 l_argument := 'legislation_code';
203 raise l_error;
204 end if;
205 --
206 if nvl(p_rec.business_group_id, hr_api.g_number) <>
207 nvl(pay_dte_shd.g_old_rec.business_group_id, hr_api.g_number)
208 then
209 l_argument := 'business_group_id';
210 raise l_error;
211 end if;
212 --
213 if nvl(p_rec.event_group_id, hr_api.g_number) <>
214 nvl(pay_dte_shd.g_old_rec.event_group_id, hr_api.g_number)
215 then
216 l_argument := 'event_group_id';
217 raise l_error;
218 end if;
219 --
220 EXCEPTION
221 WHEN l_error THEN
222 hr_api.argument_changed_error
223 (p_api_name => l_proc
224 ,p_argument => l_argument);
225 WHEN OTHERS THEN
226 RAISE;
227 End chk_non_updateable_args;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |---------------------------<chk_columns >----------------------------|
231 -- -----------------------------------------------------------------
232 -- {Start of comments}
233 --
234 -- Description:
235 -- This procedure validates the column_name exist on the table referred to
236 --
237 -- Prerequisites:
238 --
239 -- In Parameters:
240 -- A Pl/Sql record structure.
241 --
242 -- Post Success:
243 -- Processing continues.
244 --
245 -- Post Failure:
246 -- Error if column not recognised.
247 --
248 -- Access Status:
249 -- Internal Row Handler Use Only.
250 --
251 -- {End of comments}
252 --
253 Procedure chk_columns
254 ( p_rec in pay_dte_shd.g_rec_type
255 ) is
256 --
257 l_proc varchar2(72) := g_package||'chk_columns';
258 l_error EXCEPTION;
259 l_argument varchar2(30);
260 l_dummy number(1);
261 --
262 cursor csr_chk_column is
263 select 1
264 from dual
265 where exists (
266 select 1
267 from pay_dated_tables dt,
268 fnd_tables tab,
269 fnd_columns col
270 where dt.dated_table_id = p_rec.dated_table_id
271 and dt.table_name = tab.table_name
272 and tab.table_id = col.table_id
273 and col.column_name = p_rec.column_name
274 );
275 --
276 Begin
277 --
278 hr_utility.set_location('Entering:'||l_proc, 5);
279 --
280 If (p_rec.update_type = 'U')
281 Then
282 hr_api.mandatory_arg_error
283 (p_api_name => l_proc
284 ,p_argument => 'COLUMN_NAME'
285 ,p_argument_value => p_rec.column_name
286 );
287 --
288 Open csr_chk_column;
289 Fetch csr_chk_column Into l_dummy;
290 If csr_chk_column%notfound Then
291 Close csr_chk_column;
292 --
293 -- The column does not belong to the table therefore we must error
294 --
295 fnd_message.set_name('PAY', 'HR_xxxx_INVALID_COLUMN_NAME');
296
297 fnd_message.raise_error;
298 End If;
299 Close csr_chk_column;
300 End If;
301 --
302 --
303 hr_utility.set_location(' Leaving:'||l_proc, 10);
304 end chk_columns;
305 --
306 -- ----------------------------------------------------------------------------
307 -- |---------------------------< chk_update_type >----------------------------|
308 -- -----------------------------------------------------------------
309 -- {Start of comments}
310 --
311 -- Description:
312 -- This procedure validates the update type being passed. For the time
313 -- being we can only have a value of DATETRACK_UPDATE.
314 --
315 -- Prerequisites:
316 -- The table identified by p_dated_table_id already exists.
317 --
318 -- In Parameters:
319 -- A Pl/Sql record structure.
320 --
321 -- Post Success:
322 -- Processing continues.
323 --
324 -- Post Failure:
325 -- Error if column not recognised.
326 --
327 -- Access Status:
328 -- Internal Row Handler Use Only.
329 --
330 -- {End of comments}
331 --
332 Procedure chk_update_type
333 (p_effective_date in date
334 ,p_rec in pay_dte_shd.g_rec_type) is
335 --
336 l_proc varchar2(72) := g_package || 'chk_update_type';
337 l_error EXCEPTION;
338 l_argument varchar2(30);
339 --
340 --
341 Begin
342 --
343 --
344 -- Check mandatory parameters have been set
345 --
346 hr_api.mandatory_arg_error
347 (p_api_name => l_proc
348 ,p_argument => 'update_type'
349 ,p_argument_value => p_rec.update_type
350 );
351 --
352 if hr_api.not_exists_in_hrstanlookups(p_effective_date => p_effective_date
353 ,p_lookup_type => 'UPDATE_TYPE'
354 ,p_lookup_code => p_rec.update_type) then
355 --
356 -- The update_type for this record is not recognised
357 --
358 fnd_message.set_name('PAY','HR_xxxx_INVALID_UPDATE_TYPE');
359 fnd_message.raise_error;
360 end if;
361 hr_utility.set_location(l_proc,30);
362 --
363 if p_rec.proration_style is not null then
364 if hr_api.not_exists_in_hrstanlookups(p_effective_date => p_effective_date
365 ,p_lookup_type => 'PAY_PRORATION_STYLE'
366 ,p_lookup_code => p_rec.proration_style) then
367 --
368 -- The proration style is not recognised
369 --
370 fnd_message.set_name('PAY','HR_xxxx_INVALID_PRORATION_STL');
371 fnd_message.raise_error;
372 end if;
373 end if;
374 hr_utility.set_location(l_proc,40);
375 --
376 -- Set the global variables so the values are
377 -- available for the next call to this function.
378 --
379 end chk_update_type;
380 --
381 -- ----------------------------------------------------------------------------
382 -- |---------------------------< insert_validate >----------------------------|
383 -- ----------------------------------------------------------------------------
384 Procedure insert_validate
385 (p_effective_date in date
386 ,p_rec in pay_dte_shd.g_rec_type
387 ) is
388 --
389 l_proc varchar2(72) := g_package||'insert_validate';
390 --
391 Begin
392 hr_utility.set_location('Entering:'||l_proc, 5);
393 --
394 -- commenting this out as bus grp can be null
395 -- hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
396 --
397 chk_update_type(p_effective_date => p_effective_date
398 ,p_rec => p_rec);
399 --
400 chk_columns (p_rec => p_rec);
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End insert_validate;
404 --
405 -- ----------------------------------------------------------------------------
406 -- |---------------------------< update_validate >----------------------------|
407 -- ----------------------------------------------------------------------------
408 Procedure update_validate
409 (p_effective_date in date
410 ,p_rec in pay_dte_shd.g_rec_type
411 ) is
412 --
413 l_proc varchar2(72) := g_package||'update_validate';
414 --
415 Begin
416 hr_utility.set_location('Entering:'||l_proc, 5);
417 --
418 -- commenting this out as bus grp can be null
419 -- hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
420 --
421 chk_non_updateable_args
422 (p_effective_date => p_effective_date
423 ,p_rec => p_rec
424 );
425 --
426 chk_update_type(p_effective_date => p_effective_date
427 ,p_rec => p_rec);
428 --
429 chk_columns (p_rec => p_rec);
430 --
431 hr_utility.set_location(' Leaving:'||l_proc, 10);
432 --
433 End update_validate;
434 --
435 -- ----------------------------------------------------------------------------
436 -- |---------------------------< delete_validate >----------------------------|
437 -- ----------------------------------------------------------------------------
438 Procedure delete_validate
439 (p_rec in pay_dte_shd.g_rec_type
440 ) is
441 --
442 l_proc varchar2(72) := g_package||'delete_validate';
443 --
444 Begin
445 hr_utility.set_location('Entering:'||l_proc, 5);
446 --
447 -- Call all supporting business operations
448 --
449 hr_utility.set_location(' Leaving:'||l_proc, 10);
450 End delete_validate;
451 --
452 end pay_dte_bus;
[Home] [Help]