1 Package Body pay_pga_bus as
2 /* $Header: pypgarhi.pkb 120.0 2005/09/29 10:53 tvankayl noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pga_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_pay_gl_account_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 -- {Start Of Comments}
20 --
21 -- Description:
22 -- This procedure is used to ensure that non updateable attributes have
23 -- not been updated. If an attribute has been updated an error is generated.
24 --
25 -- Pre Conditions:
26 -- g_old_rec has been populated with details of the values currently in
27 -- the database.
28 --
29 -- In Arguments:
30 -- p_rec has been populated with the updated values the user would like the
31 -- record set to.
32 --
33 -- Post Success:
34 -- Processing continues if all the non updateable attributes have not
35 -- changed.
36 --
37 -- Post Failure:
38 -- An application error is raised if any of the non updatable attributes
39 -- have been altered.
40 --
41 -- {End Of Comments}
42 -- ----------------------------------------------------------------------------
43 Procedure chk_non_updateable_args
44 (p_effective_date in date
45 ,p_rec in pay_pga_shd.g_rec_type
46 ) IS
47 --
48 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
49 --
50 Begin
51 --
52 -- Only proceed with the validation if a row exists for the current
53 -- record in the HR Schema.
54 --
55 IF NOT pay_pga_shd.api_updating
56 (p_pay_gl_account_id => p_rec.pay_gl_account_id
57 ,p_effective_date => p_effective_date
58 ,p_object_version_number => p_rec.object_version_number
59 ) THEN
60 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
61 fnd_message.set_token('PROCEDURE ', l_proc);
62 fnd_message.set_token('STEP ', '5');
63 fnd_message.raise_error;
64 END IF;
65 --
66 if nvl(p_rec.external_account_id, hr_api.g_number) <>
67 pay_pga_shd.g_old_rec.external_account_id then
68 hr_api.argument_changed_error
69 (p_api_name => l_proc
70 ,p_argument => 'EXTERNAL_ACCOUNT_ID'
71 ,p_base_table => pay_pga_shd.g_tab_nam
72 );
73 end if;
74 --
75 if nvl(p_rec.org_payment_method_id, hr_api.g_number) <>
76 pay_pga_shd.g_old_rec.org_payment_method_id then
77 hr_api.argument_changed_error
78 (p_api_name => l_proc
79 ,p_argument => 'ORG_PAYMENT_METHOD_ID'
80 ,p_base_table => pay_pga_shd.g_tab_nam
81 );
82 end if;
83 --
84 End chk_non_updateable_args;
85 --
86 -- ----------------------------------------------------------------------------
87 -- |--------------------------< dt_update_validate >--------------------------|
88 -- ----------------------------------------------------------------------------
89 -- {Start Of Comments}
90 --
91 -- Description:
92 -- This procedure is used for referential integrity of datetracked
93 -- parent entities when a datetrack update operation is taking place
94 -- and where there is no cascading of update defined for this entity.
95 --
96 -- Prerequisites:
97 -- This procedure is called from the update_validate.
98 --
99 -- In Parameters:
100 --
101 -- Post Success:
102 -- Processing continues.
103 --
104 -- Post Failure:
105 --
106 -- Developer Implementation Notes:
107 -- This procedure should not need maintenance unless the HR Schema model
108 -- changes.
109 --
110 -- Access Status:
111 -- Internal Row Handler Use Only.
112 --
113 -- {End Of Comments}
114 -- ----------------------------------------------------------------------------
115 Procedure dt_update_validate
116 (p_datetrack_mode in varchar2
117 ,p_validation_start_date in date
118 ,p_validation_end_date in date
119 ) Is
120 --
121 l_proc varchar2(72) := g_package||'dt_update_validate';
122 --
123 Begin
124 --
125 -- Ensure that the p_datetrack_mode argument is not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'datetrack_mode'
130 ,p_argument_value => p_datetrack_mode
131 );
132 --
133 -- Mode will be valid, as this is checked at the start of the upd.
134 --
135 -- Ensure the arguments are not null
136 --
137 hr_api.mandatory_arg_error
138 (p_api_name => l_proc
139 ,p_argument => 'validation_start_date'
140 ,p_argument_value => p_validation_start_date
141 );
142 --
143 hr_api.mandatory_arg_error
144 (p_api_name => l_proc
145 ,p_argument => 'validation_end_date'
146 ,p_argument_value => p_validation_end_date
147 );
148 --
149 --
150 --
151 Exception
152 When Others Then
153 --
154 -- An unhandled or unexpected error has occurred which
155 -- we must report
156 --
157 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
158 fnd_message.set_token('PROCEDURE', l_proc);
159 fnd_message.set_token('STEP','15');
160 fnd_message.raise_error;
161 End dt_update_validate;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |--------------------------< dt_delete_validate >--------------------------|
165 -- ----------------------------------------------------------------------------
166 -- {Start Of Comments}
167 --
168 -- Description:
169 -- This procedure is used for referential integrity of datetracked
170 -- child entities when either a datetrack DELETE or ZAP is in operation
171 -- and where there is no cascading of delete defined for this entity.
172 -- For the datetrack mode of DELETE or ZAP we must ensure that no
173 -- datetracked child rows exist between the validation start and end
174 -- dates.
175 --
176 -- Prerequisites:
177 -- This procedure is called from the delete_validate.
178 --
179 -- In Parameters:
180 --
181 -- Post Success:
182 -- Processing continues.
183 --
184 -- Post Failure:
185 -- If a row exists by determining the returning Boolean value from the
186 -- generic dt_api.rows_exist function then we must supply an error via
187 -- the use of the local exception handler l_rows_exist.
188 --
189 -- Developer Implementation Notes:
190 -- This procedure should not need maintenance unless the HR Schema model
191 -- changes.
192 --
193 -- Access Status:
194 -- Internal Row Handler Use Only.
195 --
196 -- {End Of Comments}
197 -- ----------------------------------------------------------------------------
198 Procedure dt_delete_validate
199 (p_pay_gl_account_id in number
200 ,p_datetrack_mode in varchar2
201 ,p_validation_start_date in date
202 ,p_validation_end_date in date
203 ) Is
204 --
205 l_proc varchar2(72) := g_package||'dt_delete_validate';
206 --
207 Begin
208 --
209 -- Ensure that the p_datetrack_mode argument is not null
210 --
211 hr_api.mandatory_arg_error
212 (p_api_name => l_proc
213 ,p_argument => 'datetrack_mode'
214 ,p_argument_value => p_datetrack_mode
215 );
216 --
217 -- Only perform the validation if the datetrack mode is either
218 -- DELETE or ZAP
219 --
220 If (p_datetrack_mode = hr_api.g_delete or
221 p_datetrack_mode = hr_api.g_zap) then
222 --
223 --
224 -- Ensure the arguments are not null
225 --
226 hr_api.mandatory_arg_error
227 (p_api_name => l_proc
228 ,p_argument => 'validation_start_date'
229 ,p_argument_value => p_validation_start_date
230 );
231 --
232 hr_api.mandatory_arg_error
233 (p_api_name => l_proc
234 ,p_argument => 'validation_end_date'
235 ,p_argument_value => p_validation_end_date
236 );
237 --
238 hr_api.mandatory_arg_error
239 (p_api_name => l_proc
240 ,p_argument => 'pay_gl_account_id'
241 ,p_argument_value => p_pay_gl_account_id
242 );
243 --
244 --
245 --
246 End If;
247 --
248 Exception
249 When Others Then
250 --
251 -- An unhandled or unexpected error has occurred which
252 -- we must report
253 --
254 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
255 fnd_message.set_token('PROCEDURE', l_proc);
256 fnd_message.set_token('STEP','15');
257 fnd_message.raise_error;
258 --
259 End dt_delete_validate;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |------------------------< chk_set_of_books_id >---------------------------|
263 -- ----------------------------------------------------------------------------
264 procedure chk_set_of_books_id
265 (p_set_of_books_id in number
266 ) is
267 --
268 cursor csr_set_of_books_id is
269 select null
270 from gl_sets_of_books
271 where set_of_books_id = p_set_of_books_id;
272 --
273 l_exists varchar2(1);
274 --
275 l_proc varchar2(100) := g_package || 'chk_set_of_books_id';
276 begin
277
278 hr_utility.set_location('Entering:'|| l_proc, 10);
279 --
280 if p_set_of_books_id is not null and p_set_of_books_id <> 0 then
281
282 hr_utility.set_location('Entering:'|| l_proc, 20);
283
284 open csr_set_of_books_id;
285 fetch csr_set_of_books_id into l_exists;
286
287 if csr_set_of_books_id%notfound then
288 close csr_set_of_books_id;
289 fnd_message.set_name('PAY', 'PAY_33456_SET_OF_BOOKS_INV');
290 fnd_message.raise_error;
291 end if;
292 close csr_set_of_books_id;
293
294 end if;
295 --
296 hr_utility.set_location(' Leaving:'|| l_proc, 30);
297 --
298 exception
299 when others then
300 if csr_set_of_books_id%isopen then
301 close csr_set_of_books_id;
302 end if;
303 raise;
304
305 end chk_set_of_books_id;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |------------------------< chk_external_account_id >-----------------------|
309 -- ----------------------------------------------------------------------------
310 procedure chk_external_account_id
311 (p_external_account_id in number
312 ) is
313 --
314 cursor csr_external_account_id is
315 select null
316 from pay_external_accounts
317 where external_account_id = p_external_account_id;
318 --
319 l_exists varchar2(1);
320 --
321 l_proc varchar2(100) := g_package || 'chk_external_account_id';
322 begin
323 --
324 hr_utility.set_location('Entering:'|| l_proc, 10);
325
326 if p_external_account_id is not null then
327
328 open csr_external_account_id;
329 fetch csr_external_account_id into l_exists;
330
331 if csr_external_account_id%notfound then
332 close csr_external_account_id;
333 fnd_message.set_name('PAY', 'PAY_33457_BANK_DETAILS_INV');
334 fnd_message.raise_error;
335 end if;
336
337 close csr_external_account_id;
338
339 end if;
340
341 hr_utility.set_location(' Leaving:'|| l_proc, 20);
342 --
343 exception
344 when others then
345 if csr_external_account_id%isopen then
346 close csr_external_account_id;
347 end if;
348 raise;
349
350 end chk_external_account_id;
351 --
352 -- ----------------------------------------------------------------------------
353 -- |----------------------------< chk_gl_account_id >-------------------------|
354 -- ----------------------------------------------------------------------------
355 procedure chk_gl_account_id
356 (p_gl_account_id in number
357 ) is
358 --
359 cursor csr_gl_account_id is
360 select null
361 from gl_code_combinations
362 where code_combination_id = p_gl_account_id;
363 --
364 l_exists varchar2(1);
365 --
366 l_proc varchar2(100) := g_package || 'chk_gl_account_id';
367 begin
368 --
369 hr_utility.set_location('Entering:'|| l_proc, 10);
370
371 if p_gl_account_id is not null then
372
376 if csr_gl_account_id%notfound then
373 open csr_gl_account_id;
374 fetch csr_gl_account_id into l_exists;
375
377 close csr_gl_account_id;
378 fnd_message.set_name('PAY', 'PAY_33458_GL_ACT_DETAILS_INV');
379 fnd_message.raise_error;
380 end if;
381
382 close csr_gl_account_id;
383
384 end if;
385
386 hr_utility.set_location(' Leaving:'|| l_proc, 20);
387 --
388 exception
389 when others then
390 if csr_gl_account_id%isopen then
391 close csr_gl_account_id;
392 end if;
393 raise;
394
395 end chk_gl_account_id;
396 --
397 -- ----------------------------------------------------------------------------
398 -- |---------------------------< insert_validate >----------------------------|
399 -- ----------------------------------------------------------------------------
400 Procedure insert_validate
401 (p_rec in pay_pga_shd.g_rec_type
402 ,p_effective_date in date
403 ,p_datetrack_mode in varchar2
404 ,p_validation_start_date in date
405 ,p_validation_end_date in date
406 ) is
407 --
408 l_proc varchar2(72) := g_package||'insert_validate';
409 --
410 Begin
411 hr_utility.set_location('Entering:'||l_proc, 5);
412 --
413 --
414 -- Validate Dependent Attributes
415 --
416 --
417 chk_set_of_books_id
418 (p_set_of_books_id => p_rec.set_of_books_id
419 );
420 --
421 chk_external_account_id
422 (p_external_account_id => p_rec.external_account_id
423 );
424 --
425 chk_gl_account_id
426 (p_gl_account_id => p_rec.gl_cash_ac_id
427 );
428 --
429 chk_gl_account_id
430 (p_gl_account_id => p_rec.gl_cash_clearing_ac_id
431 );
432 --
433 chk_gl_account_id
434 (p_gl_account_id => p_rec.gl_control_ac_id
435 );
436 --
437 chk_gl_account_id
438 (p_gl_account_id => p_rec.gl_error_ac_id
439 );
440 --
441 hr_utility.set_location(' Leaving:'||l_proc, 10);
442 End insert_validate;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |---------------------------< update_validate >----------------------------|
446 -- ----------------------------------------------------------------------------
447 Procedure update_validate
448 (p_rec in pay_pga_shd.g_rec_type
449 ,p_effective_date in date
450 ,p_datetrack_mode in varchar2
451 ,p_validation_start_date in date
452 ,p_validation_end_date in date
453 ) is
454 --
455 l_proc varchar2(72) := g_package||'update_validate';
456 --
457 Begin
458 hr_utility.set_location('Entering:'||l_proc, 5);
459 --
460 -- Call all supporting business operations
461 --
462 -- Validate Dependent Attributes
463 --
464 -- Call the datetrack update integrity operation
465 --
466 dt_update_validate
467 (p_datetrack_mode => p_datetrack_mode
468 ,p_validation_start_date => p_validation_start_date
469 ,p_validation_end_date => p_validation_end_date
470 );
471 --
472 chk_non_updateable_args
473 (p_effective_date => p_effective_date
474 ,p_rec => p_rec
475 );
476 --
477 chk_set_of_books_id
478 (p_set_of_books_id => p_rec.set_of_books_id
479 );
480 --
481 chk_gl_account_id
482 (p_gl_account_id => p_rec.gl_cash_ac_id
483 );
484 --
485 chk_gl_account_id
486 (p_gl_account_id => p_rec.gl_cash_clearing_ac_id
487 );
488 --
489 chk_gl_account_id
490 (p_gl_account_id => p_rec.gl_control_ac_id
491 );
492 --
493 chk_gl_account_id
494 (p_gl_account_id => p_rec.gl_error_ac_id
495 );
496 --
497 hr_utility.set_location(' Leaving:'||l_proc, 10);
498 End update_validate;
499 --
500 -- ----------------------------------------------------------------------------
501 -- |---------------------------< delete_validate >----------------------------|
502 -- ----------------------------------------------------------------------------
503 Procedure delete_validate
504 (p_rec in pay_pga_shd.g_rec_type
505 ,p_effective_date in date
506 ,p_datetrack_mode in varchar2
507 ,p_validation_start_date in date
511 l_proc varchar2(72) := g_package||'delete_validate';
508 ,p_validation_end_date in date
509 ) is
510 --
512 --
513 Begin
514 hr_utility.set_location('Entering:'||l_proc, 5);
515 --
516 -- Call all supporting business operations
517 --
518 dt_delete_validate
519 (p_datetrack_mode => p_datetrack_mode
520 ,p_validation_start_date => p_validation_start_date
521 ,p_validation_end_date => p_validation_end_date
522 ,p_pay_gl_account_id => p_rec.pay_gl_account_id
523 );
524 --
525 hr_utility.set_location(' Leaving:'||l_proc, 10);
526 End delete_validate;
527 --
528 end pay_pga_bus;