1 Package Body ota_tcc_bus as
2 /* $Header: ottccrhi.pkb 120.1 2005/09/01 07:26:31 ssur noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tcc_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_cross_charge_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cross_charge_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 , ota_cross_charges tcc
30 where tcc.cross_charge_id = p_cross_charge_id
31 and pbg.business_group_id = tcc.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 => 'cross_charge_id'
47 ,p_argument_value => p_cross_charge_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_cross_charge_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 , ota_cross_charges tcc
90 where tcc.cross_charge_id = p_cross_charge_id
91 and pbg.business_group_id = tcc.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 => 'cross_charge_id'
107 ,p_argument_value => p_cross_charge_id
108 );
109 --
110 if ( nvl(ota_tcc_bus.g_cross_charge_id, hr_api.g_number)
111 = p_cross_charge_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 := ota_tcc_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 ota_tcc_bus.g_cross_charge_id := p_cross_charge_id;
142 ota_tcc_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 ota_tcc_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 ota_tcc_shd.api_updating
190 (p_cross_charge_id => p_rec.cross_charge_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 -- EDIT_HERE: Add checks to ensure non-updateable args have
200 -- not been updated.
201 --
202 EXCEPTION
203 WHEN l_error THEN
204 hr_api.argument_changed_error
205 (p_api_name => l_proc
206 ,p_argument => l_argument);
207 WHEN OTHERS THEN
208 RAISE;
209 End chk_non_updateable_args;
210
211 -- ----------------------------------------------------------------------------
212 -- |-----------------------< chk_overlap_cc_def>------------------------------|
213 -- ----------------------------------------------------------------------------
214 Procedure chk_overlap_cc_def
215 (p_cross_charge_id in number
216 ,p_start_date_active in date
217 ,p_end_date_active in date
218 ,p_gl_set_of_books_id in number
219 ,p_from_to in varchar2
220 ,p_type in varchar2
221 ,p_business_group_id in number
222 ) IS
223 l_proc varchar2(72) := g_package||'chk_overlap_cc_def';
224 l_exists varchar2(1);
225
226 CURSOR TCC IS
227 SELECT null
228 FROM OTA_CROSS_CHARGES
229 WHERE cross_charge_id <> nvl(p_cross_charge_id,0) and
230 Business_group_id = p_business_group_id and
231 gl_Set_of_books_id = p_gl_set_of_books_id and
232 From_to = p_from_to and
233 type = p_type and
234 (p_start_date_active between start_date_active and nvl(end_date_active, hr_api.g_eot) or
235 nvl(p_end_date_active,hr_api.g_eot) between start_date_active and nvl(end_date_active, hr_api.g_eot));
236 -- bug no 4587140
237 /* ((start_date_active <= p_start_date_active and
238 start_date_active <= nvl(end_date_active,hr_api.g_date) ) or
239 (start_date_active >= p_start_date_active and
240 nvl(p_end_date_active,hr_api.g_eot) >= start_date_active ));
241
242 */
243 /* ((start_date_active <= p_start_date_active and
244 nvl(end_date_active,hr_api.g_date) >= nvl(p_end_date_active,hr_api.g_date)) or
245 (start_date_active >= p_start_date_active and
246 start_date_active <= nvl(p_end_date_active,hr_api.g_eot)) or
247 (start_date_active <= p_start_date_active and
248 nvl(end_date_active,hr_api.g_date) <= nvl(p_end_date_active,hr_api.g_date) and
249 nvl(end_date_active,hr_api.g_date) >= p_start_date_active )) ; */
250
251 Begin
252 hr_utility.set_location(' entering:'||l_proc, 10);
253
254 if (((p_cross_charge_id is not null) and
255 (nvl(ota_tcc_shd.g_old_rec.start_date_active,hr_api.g_date) <>
256 nvl(p_start_date_active,hr_api.g_date) or
257 nvl(ota_tcc_shd.g_old_rec.end_date_active,hr_api.g_date) <>
258 nvl(p_end_date_active,hr_api.g_date)) )
259 or (p_cross_charge_id is null)) then
260
261 If ota_tcc_shd.g_old_rec.end_date_active is not null and
262 nvl(p_end_date_active,hr_api.g_date) >
263 ota_tcc_shd.g_old_rec.end_date_active then
264
265 fnd_message.set_name('OTA','OTA_13348_TCC_EXT_END_DATE');
266 fnd_message.raise_error;
267
268 end if;
269 OPEN TCC;
270 FETCH TCC INTO l_exists;
271 IF TCC%found then
272 fnd_message.set_name('OTA','OTA_13330_TCC_DATE_OVERLAP');
273 fnd_message.raise_error;
274 END IF;
275 CLOSE TCC;
276
277
278 end if;
279 hr_utility.set_location(' Leaving:'||l_proc, 20);
280
281 --
282 end chk_overlap_cc_def;
283
284
285 -- ----------------------------------------------------------------------------
286 -- |-------------------------< chk_end_date_ext>------------------------------|
287 -- ----------------------------------------------------------------------------
288 Procedure chk_end_date_ext
289 (p_cross_charge_id in number
290 ,p_end_date_active in date
291 ) IS
292 l_proc varchar2(72) := g_package||'chk_end_date_ext';
293 l_exists varchar2(1);
294
295 Begin
296 hr_utility.set_location(' entering:'||l_proc, 10);
297
298 if (((p_cross_charge_id is not null) and
299 (nvl(ota_tcc_shd.g_old_rec.end_date_active,hr_api.g_date) <>
300 nvl(p_end_date_active,hr_api.g_date)))
301 or (p_cross_charge_id is null)) then
302
303 If ota_tcc_shd.g_old_rec.end_date_active is not null and
304 nvl(p_end_date_active,hr_api.g_date) >
305 ota_tcc_shd.g_old_rec.end_date_active then
306
307 fnd_message.set_name('OTA','OTA_13348_TCC_EXT_END_DATE');
308 fnd_message.raise_error;
309
310 end if;
311
312
313 end if;
314 hr_utility.set_location(' Leaving:'||l_proc, 20);
315
316 --
317 end chk_end_date_ext;
318
319
320 -- ----------------------------------------------------------------------------
321 -- |------------------------------< chk_type >----------------------------|
322 -- ----------------------------------------------------------------------------
323 Procedure chk_type
324 (p_cross_charge_id in number
325 ,p_type in varchar2
326 ,p_effective_date in date) is
327
328 --
329 l_proc varchar2(72) := g_package||'chk_type';
330 l_api_updating boolean;
331
332 begin
333 hr_utility.set_location(' Leaving:'||l_proc, 10);
334
335
336 if (((p_cross_charge_id is not null) and
337 nvl(ota_tcc_shd.g_old_rec.type,hr_api.g_varchar2) <>
338 nvl(p_type,hr_api.g_varchar2))
339 or
340 (p_cross_charge_id is null)) then
341
342 hr_utility.set_location(' entering:'||l_proc, 20);
343 --
344 -- if type is not null then
345 -- check if the type value exists in hr_lookups
346 -- where lookup_type is 'OTA_CROSS_CHARGE_TYPE'
347 --
348 if p_type is not null then
349 if hr_api.not_exists_in_hrstanlookups
350 (p_effective_date => p_effective_date
351 ,p_lookup_type => 'OTA_CROSS_CHARGE_TYPE'
352 ,p_lookup_code => p_type) then
353 fnd_message.set_name('OTA','OTA_13334_TCC_TYPE_INVALID');
354 fnd_message.raise_error;
355 end if;
356 hr_utility.set_location(' Leaving:'||l_proc, 30);
357
358 end if;
359
360 end if;
361 hr_utility.set_location(' Leaving:'||l_proc, 40);
362
363 end chk_type;
364
365
366 -- ----------------------------------------------------------------------------
367 -- |------------------------------< chk_from_to >----------------------------|
368 -- ----------------------------------------------------------------------------
369 Procedure chk_from_to
370 (p_cross_charge_id in number
371 ,p_from_to in varchar2
372 ,p_effective_date in date) is
373
374 --
375 l_proc varchar2(72) := g_package||'chk_from_to';
376 l_api_updating boolean;
377
378 begin
379 hr_utility.set_location(' Leaving:'||l_proc, 10);
380
381
382 if (((p_cross_charge_id is not null) and
383 nvl(ota_tcc_shd.g_old_rec.from_to,hr_api.g_varchar2) <>
384 nvl(p_from_to,hr_api.g_varchar2))
385 or
386 (p_cross_charge_id is null)) then
387
388 hr_utility.set_location(' entering:'||l_proc, 20);
389 --
390 -- if From_to is not null then
391 -- check if the from_to value exists in hr_lookups
392 -- where lookup_type is 'OTA_CROSS_CHARGE_FROM_TO'
393 --
394 if p_from_to is not null then
395 if hr_api.not_exists_in_hrstanlookups
396 (p_effective_date => p_effective_date
397 ,p_lookup_type => 'OTA_CROSS_CHARGE_FROM_TO'
398 ,p_lookup_code => p_from_to) then
399 fnd_message.set_name('OTA','OTA_13341_TCC_FROM_TO_INVALID');
400 fnd_message.raise_error;
401 end if;
402 hr_utility.set_location(' Leaving:'||l_proc, 30);
403
404 end if;
405
406 end if;
407 hr_utility.set_location(' Leaving:'||l_proc, 40);
408
409 end chk_from_to;
410
411 --
412 -- ----------------------------------------------------------------------------
413 -- |---------------------------< insert_validate >----------------------------|
414 -- ----------------------------------------------------------------------------
415 Procedure insert_validate
416 (p_effective_date in date
417 ,p_rec in ota_tcc_shd.g_rec_type
418 ) is
419 --
420 l_proc varchar2(72) := g_package||'insert_validate';
421 --
422 Begin
423 hr_utility.set_location('Entering:'||l_proc, 5);
424 --
425 -- Call all supporting business operations
426 --
427 chk_overlap_cc_def
428 (p_cross_charge_id =>p_rec.cross_charge_id
429 ,p_start_date_active =>p_rec.start_date_active
430 ,p_end_date_active =>p_rec.end_date_active
431 ,p_gl_set_of_books_id =>p_rec.gl_set_of_books_id
432 ,p_from_to =>p_rec.from_to
433 ,p_type =>p_rec.type
434 ,p_business_group_id =>p_rec.business_group_id);
435
436
437 chk_from_to
438 (p_cross_charge_id => p_rec.cross_charge_id
439 ,p_from_to => p_rec.from_to
440 ,p_effective_date => p_effective_date);
441
442 chk_type
443 (p_cross_charge_id => p_rec.cross_charge_id
444 ,p_type => p_rec.type
445 ,p_effective_date => p_effective_date);
446
447 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
448 --
449 --
450 hr_utility.set_location(' Leaving:'||l_proc, 10);
451 End insert_validate;
452 --
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------< update_validate >----------------------------|
455 -- ----------------------------------------------------------------------------
456 Procedure update_validate
457 (p_effective_date in date
458 ,p_rec in ota_tcc_shd.g_rec_type
459 ) is
460 --
461 l_proc varchar2(72) := g_package||'update_validate';
462 --
463 Begin
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 -- Call all supporting business operations
467 --
468 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
469 --
470 chk_non_updateable_args
471 (p_effective_date => p_effective_date
472 ,p_rec => p_rec
473 );
474
475 chk_overlap_cc_def
476 (p_cross_charge_id =>p_rec.cross_charge_id
477 ,p_start_date_active =>p_rec.start_date_active
478 ,p_end_date_active =>p_rec.end_date_active
479 ,p_gl_set_of_books_id =>p_rec.gl_set_of_books_id
480 ,p_from_to =>p_rec.from_to
481 ,p_type =>p_rec.type
482 ,p_business_group_id =>p_rec.business_group_id);
483
484 chk_end_date_ext
485 (p_cross_charge_id =>p_rec.cross_charge_id
486 ,p_end_date_active =>p_rec.end_date_active);
487
488 chk_from_to
489 (p_cross_charge_id => p_rec.cross_charge_id
490 ,p_from_to => p_rec.from_to
491 ,p_effective_date => p_effective_date);
492
493 chk_type
494 (p_cross_charge_id => p_rec.cross_charge_id
495 ,p_type => p_rec.type
496 ,p_effective_date => p_effective_date);
497
498 --
499 --
500 hr_utility.set_location(' Leaving:'||l_proc, 10);
501 End update_validate;
502 --
503 -- ----------------------------------------------------------------------------
504 -- |---------------------------< delete_validate >----------------------------|
505 -- ----------------------------------------------------------------------------
506 Procedure delete_validate
507 (p_rec in ota_tcc_shd.g_rec_type
508 ) is
509 --
510 l_proc varchar2(72) := g_package||'delete_validate';
511 --
512 Begin
513 hr_utility.set_location('Entering:'||l_proc, 5);
514 --
515 -- Call all supporting business operations
516 --
517 hr_utility.set_location(' Leaving:'||l_proc, 10);
518 End delete_validate;
519 --
520 end ota_tcc_bus;
[Home] [Help]