[Home] [Help]
PACKAGE BODY: APPS.OTA_TMT_BUS1
Source
1 Package body ota_tmt_bus1 as
2 /* $Header: ottmtrhi.pkb 115.6 2002/11/26 17:09:48 hwinsor noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tmt_bus1.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------<chk_tp_measurement_code>---------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 Procedure chk_tp_measurement_code
16 (p_effective_date in date
17 ,p_tp_measurement_code in ota_tp_measurement_types.tp_measurement_code%TYPE
18 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
19 ) is
20 --
21 l_exists varchar2(1);
22 l_proc varchar2(72) := g_package||'chk_tp_measurement_code';
23 --
24 cursor csr_tp_measurement_code is
25 select null
26 from OTA_TP_MEASUREMENT_TYPES
27 where tp_measurement_code = p_tp_measurement_code
28 and business_group_id = p_business_group_id;
29 Begin
30 --
31 -- check mandatory parameters have been set
32 --
33 hr_utility.set_location('Entering:'||l_proc, 5);
34 hr_api.mandatory_arg_error
35 (p_api_name => l_proc
36 ,p_argument => 'p_effective_date'
37 ,p_argument_value => p_effective_date
38 );
39 --
40 hr_utility.set_location(' Step:'|| l_proc, 20);
41 hr_api.mandatory_arg_error
42 (p_api_name => l_proc
43 ,p_argument => 'p_tp_measurement_code'
44 ,p_argument_value => p_tp_measurement_code
45 );
46 --
47 hr_utility.set_location(' Step:'|| l_proc, 30);
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument => 'p_business_group_id'
51 ,p_argument_value => p_business_group_id
52 );
53 --
54 -- Check that the lookup code is valid
55 --
56 hr_utility.set_location(' Step:'|| l_proc, 40);
57 if hr_api.not_exists_in_hr_lookups
58 (p_effective_date => p_effective_date
59 ,p_lookup_type => 'OTA_PLAN_MEASUREMENT_TYPE'
60 ,p_lookup_code => p_tp_measurement_code
61 ) then
62 -- Error, lookup not available
63 fnd_message.set_name('OTA', 'OTA_13800_TMT_INV_MEAS_TYPE');
64 fnd_message.raise_error;
65 end if;
66 --
67 -- Check that the combination is unique
68 --
69 hr_utility.set_location(' Step:'|| l_proc, 50);
70 open csr_tp_measurement_code;
71 fetch csr_tp_measurement_code into l_exists;
72 if csr_tp_measurement_code%FOUND then
73 close csr_tp_measurement_code;
74 hr_utility.set_location(' Step:'|| l_proc, 60);
75 fnd_message.set_name('OTA', 'OTA_13801_TMT_DUP_MEAS_TYPE');
76 fnd_message.raise_error;
77 end if;
78 close csr_tp_measurement_code;
79 --
80 hr_utility.set_location(' Leaving:'||l_proc, 70);
81 end chk_tp_measurement_code;
82 -- ----------------------------------------------------------------------------
83 -- |----------------------<chk_del_tp_measurement_code>-----------------------|
84 -- ----------------------------------------------------------------------------
85 Procedure chk_del_tp_measurement_code
86 (p_tp_measurement_code in ota_tp_measurement_types.tp_measurement_code%TYPE
87 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
88 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
89 ) is
90 --
91 l_exists varchar2(1);
92 l_proc varchar2(72) := g_package||'chk_del_tp_measurement_code';
93 --
94 cursor csr_del_tp_measurement_code is
95 select null
96 from PER_BUDGETS pb
97 ,PER_BUDGET_VERSIONS pbv
98 ,PER_BUDGET_ELEMENTS pbe
99 ,PER_BUDGET_VALUES pbva
100 where pb.unit = p_tp_measurement_code
101 and pb.business_group_id = p_business_group_id
102 and pb.budget_type_code = 'OTA_BUDGET'
103 and pb.budget_id = pbv.budget_id
104 and pbv.budget_version_id = pbe.budget_version_id
105 and pbe.budget_element_id = pbva.budget_element_id;
106 --
107 Begin
108 --
109 -- check mandatory parameters have been set
110 --
111 hr_utility.set_location('Entering:'||l_proc, 5);
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc
114 ,p_argument => 'p_tp_measurement_code'
115 ,p_argument_value => p_tp_measurement_code
116 );
117 --
118 hr_utility.set_location('Entering:'||l_proc, 10);
119 hr_api.mandatory_arg_error
120 (p_api_name => l_proc
121 ,p_argument => 'p_tp_measurement_type_id'
122 ,p_argument_value => p_tp_measurement_type_id
123 );
124 --
125 hr_utility.set_location(' Step:'|| l_proc, 30);
126 hr_api.mandatory_arg_error
127 (p_api_name => l_proc
128 ,p_argument => 'p_business_group_id'
129 ,p_argument_value => p_business_group_id
130 );
131 --
132 -- Check that the code can be deleted
133 --
134 hr_utility.set_location(' Step:'|| l_proc, 50);
135 open csr_del_tp_measurement_code;
136 fetch csr_del_tp_measurement_code into l_exists;
137 if csr_del_tp_measurement_code%FOUND then
138 close csr_del_tp_measurement_code;
139 hr_utility.set_location(' Step:'|| l_proc, 60);
140 fnd_message.set_name('OTA', 'OTA_13813_TMT_NO_DEL_BUDGET');
141 fnd_message.raise_error;
142 end if;
143 close csr_del_tp_measurement_code;
144 --
145 hr_utility.set_location(' Leaving:'||l_proc, 70);
146 end chk_del_tp_measurement_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------------------<chk_unit>-----------------------------|
150 -- ----------------------------------------------------------------------------
151 Procedure chk_unit
152 (p_effective_date in date
153 ,p_unit in ota_tp_measurement_types.unit%TYPE
154 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
155 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
156 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
157 ) is
158 --
159 l_exists varchar2(1);
160 l_proc varchar2(72) := g_package||'chk_unit';
161 l_api_updating boolean;
162
163 cursor csr_chk_no_cost_recs is
164 select null
165 from OTA_TRAINING_PLAN_COSTS
166 where business_group_id = p_business_group_id
167 and tp_measurement_type_id = p_tp_measurement_type_id;
168 Begin
169 --
170 -- check mandatory parameters have been set
171 --
172 hr_utility.set_location('Entering:'||l_proc, 5);
173 hr_api.mandatory_arg_error
174 (p_api_name => l_proc
175 ,p_argument => 'p_unit'
176 ,p_argument_value => p_unit
177 );
178 --
179 hr_utility.set_location(' Step:'|| l_proc, 30);
180 hr_api.mandatory_arg_error
181 (p_api_name => l_proc
182 ,p_argument => 'p_business_group_id'
183 ,p_argument_value => p_business_group_id
184 );
185 --
186 hr_utility.set_location(' Step:'|| l_proc, 40);
187 hr_api.mandatory_arg_error
188 (p_api_name => l_proc
189 ,p_argument => 'p_effective_date'
190 ,p_argument_value => p_effective_date
191 );
192 --
193 l_api_updating := ota_tmt_shd.api_updating
194 (p_tp_measurement_type_id => p_tp_measurement_type_id
195 ,p_object_version_number => p_object_version_number
196 );
197 --
198 -- If this is a changing update, or a new insert, test
199 --
200 if ((l_api_updating and
201 nvl(ota_tmt_shd.g_old_rec.unit, hr_api.g_varchar2) <>
202 nvl(p_unit, hr_api.g_varchar2))
203 or (NOT l_api_updating))
204 then
205 hr_utility.set_location(l_proc, 50);
206 --
207 -- Validate that the code exists in the lookups view
208 --
209 if hr_api.not_exists_in_hr_lookups
210 (p_effective_date => p_effective_date
211 ,p_lookup_type => 'UNITS'
212 ,p_lookup_code => p_unit
213 ) then
214 -- Error, lookup not available
215 hr_utility.set_location(l_proc, 60);
216 fnd_message.set_name('OTA', 'OTA_13804_INV_UNIT_TYPE');
217 fnd_message.raise_error;
218 elsif (p_unit <> 'I')
219 and (p_unit <> 'M')
220 and (p_unit <> 'N') then
221 -- Error, lookup not in sub list of allowed values
222 hr_utility.set_location(l_proc, 70);
223 fnd_message.set_name('OTA', 'OTA_13804_INV_UNIT_TYPE');
224 fnd_message.raise_error;
225 End if;
226 End if;
227 --
228 -- but changes are only allowed if there are no current recs in costs
229 --
230 If l_api_updating
231 and nvl(ota_tmt_shd.g_old_rec.unit, hr_api.g_varchar2) <>
232 nvl(p_unit, hr_api.g_varchar2) then
233 hr_utility.set_location(' Step:'|| l_proc, 80);
234 open csr_chk_no_cost_recs;
235 fetch csr_chk_no_cost_recs into l_exists;
236 If csr_chk_no_cost_recs%FOUND then
237 close csr_chk_no_cost_recs;
238 hr_utility.set_location(' Step:'|| l_proc, 90);
239 fnd_message.set_name('OTA', 'OTA_13803_TMT_UNIT_UPD_COST');
240 fnd_message.raise_error;
241 End if;
242 close csr_chk_no_cost_recs;
243 End if;
244 hr_utility.set_location(' Leaving:'||l_proc, 100);
245 --
246 end chk_unit;
247 -- ----------------------------------------------------------------------------
248 -- |-----------------------------------<chk_budget_level>----------------------|
249 -- ----------------------------------------------------------------------------
250 Procedure chk_budget_level
251 (p_effective_date in date
252 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
253 ,p_budget_level in ota_tp_measurement_types.budget_level%TYPE
254 ,p_tp_measurement_code in ota_tp_measurement_types.tp_measurement_code%TYPE
255 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
256 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
257 ) is
258 --
259 l_exists varchar2(1);
260 l_proc varchar2(72) := g_package||'chk_budget_level';
261 l_api_updating boolean;
262
263 cursor csr_upd_tp_budget_level is
264 select null
265 from PER_BUDGETS pb
266 ,PER_BUDGET_VERSIONS pbs
267 ,PER_BUDGET_ELEMENTS pbe
268 where pb.unit = p_tp_measurement_code
269 and pb.business_group_id = p_business_group_id
270 and pb.budget_type_code = 'OTA_BUDGET'
271 and pb.budget_id = pbs.budget_id
272 and pbs.budget_version_id = pbe.budget_version_id;
273 Begin
274 --
275 -- check mandatory parameters have been set
276 --
277 hr_utility.set_location('Entering:'||l_proc, 5);
278 hr_api.mandatory_arg_error
279 (p_api_name => l_proc
280 ,p_argument => 'p_tp_measurement_code'
281 ,p_argument_value => p_tp_measurement_code
282 );
283 --
284 hr_utility.set_location(' Step:'|| l_proc, 30);
285 hr_api.mandatory_arg_error
286 (p_api_name => l_proc
287 ,p_argument => 'p_business_group_id'
288 ,p_argument_value => p_business_group_id
289 );
290 --
291 hr_utility.set_location(' Step:'|| l_proc, 40);
292 hr_api.mandatory_arg_error
293 (p_api_name => l_proc
294 ,p_argument => 'p_effective_date'
295 ,p_argument_value => p_effective_date
296 );
297 --
298 hr_utility.set_location(' Step:'|| l_proc, 50);
299 hr_api.mandatory_arg_error
300 (p_api_name => l_proc
301 ,p_argument => 'p_budget_level'
302 ,p_argument_value => p_budget_level
303 );
304 --
305 l_api_updating := ota_tmt_shd.api_updating
306 (p_tp_measurement_type_id => p_tp_measurement_type_id
307 ,p_object_version_number => p_object_version_number
308 );
309 --
310 -- If this is a changing update, or a new insert, test
311 --
312 if (l_api_updating and
313 nvl(ota_tmt_shd.g_old_rec.budget_level, hr_api.g_varchar2) <>
314 nvl(p_budget_level, hr_api.g_varchar2))
315 or (NOT l_api_updating)
316 then
317 hr_utility.set_location(l_proc, 60);
318 --
319 -- Validate that the code exists in the lookups view
320 --
321 if hr_api.not_exists_in_hr_lookups
322 (p_effective_date => p_effective_date
323 ,p_lookup_type => 'OTA_TRAINING_PLAN_BUDGET_LEVEL'
324 ,p_lookup_code => p_budget_level
325 ) then
326 -- Error, lookup not available
327 hr_utility.set_location(l_proc, 70);
328 fnd_message.set_name('OTA', 'OTA_13805_TMT_INV_BUDGET');
329 fnd_message.raise_error;
330 End if;
331 --
332 End if;
333 --
334 -- but changes are only allowed if there are no current recs in budget elements
335 --
336 If l_api_updating
337 and nvl(ota_tmt_shd.g_old_rec.budget_level, hr_api.g_varchar2) <>
338 nvl(p_budget_level, hr_api.g_varchar2) then
339 hr_utility.set_location(' Step:'|| l_proc, 80);
340 open csr_upd_tp_budget_level;
341 fetch csr_upd_tp_budget_level into l_exists;
342 If csr_upd_tp_budget_level%FOUND then
343 close csr_upd_tp_budget_level;
344 hr_utility.set_location(' Step:'|| l_proc, 90);
345 fnd_message.set_name('OTA', 'OTA_13806_TMT_UPD_BUDGET');
346 fnd_message.raise_error;
347 End if;
348 close csr_upd_tp_budget_level;
349 --
350 End if;
351 --
352 --
353 hr_utility.set_location(' Leaving:'||l_proc, 100);
354 end chk_budget_level;
355 -- ----------------------------------------------------------------------------
356 -- |-------------------<chk_budget_cost_combination>---------------------------|
357 -- ----------------------------------------------------------------------------
358 Procedure chk_budget_cost_combination
359 (p_budget_level in ota_tp_measurement_types.budget_level%TYPE
360 ,p_cost_level in ota_tp_measurement_types.cost_level%TYPE
361 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
362 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
363 ) is
364 --
365 l_exists varchar2(1);
366 l_proc varchar2(72) := g_package||'chk_budget_cost_combination';
367 l_api_updating boolean;
368
369 Begin
370 --
371 -- check mandatory parameters have been set
372 --
373 hr_utility.set_location('Entering:'||l_proc, 5);
374 hr_api.mandatory_arg_error
375 (p_api_name => l_proc
376 ,p_argument => 'p_budget_level'
377 ,p_argument_value => p_budget_level
378 );
379 --
380 hr_utility.set_location(' Step:'|| l_proc, 20);
381 hr_api.mandatory_arg_error
382 (p_api_name => l_proc
383 ,p_argument => 'p_cost_level'
384 ,p_argument_value => p_cost_level
385 );
386 hr_utility.set_location(' Step:'|| l_proc, 30);
387
388 l_api_updating := ota_tmt_shd.api_updating
389 (p_tp_measurement_type_id => p_tp_measurement_type_id
390 ,p_object_version_number => p_object_version_number
391 );
392 --
393 -- If either budget level or cost level is changing, or this is an
394 -- insert, check the combinations
398 nvl(p_budget_level, hr_api.g_varchar2)
395 --
396 if ((l_api_updating and
397 nvl(ota_tmt_shd.g_old_rec.budget_level, hr_api.g_varchar2) <>
399 or
400 nvl(ota_tmt_shd.g_old_rec.cost_level, hr_api.g_varchar2) <>
401 nvl(p_cost_level, hr_api.g_varchar2))
402 or (NOT l_api_updating))
403 then
404 hr_utility.set_location(l_proc, 50);
405 --
406 -- Validate that the combinations exist
407 --
408 If (p_cost_level = 'PLAN' and ( p_budget_level = 'EVENT'
409 or p_budget_level = 'ACTIVITY' ))
410 then
411 -- Error, combination invalid
412 hr_utility.set_location(l_proc, 60);
413 fnd_message.set_name('OTA', 'OTA_13807_TMT_BUDGET_COST_COMB');
414 fnd_message.raise_error;
415 End if;
416 --
417 End if;
418 hr_utility.set_location(' Leaving:'||l_proc, 70);
419 end chk_budget_cost_combination;
420 -- ----------------------------------------------------------------------------
421 -- |-----------------------------------<chk_cost_level>------------------------|
422 -- ----------------------------------------------------------------------------
423 Procedure chk_cost_level
424 (p_effective_date in date
425 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
426 ,p_cost_level in ota_tp_measurement_types.cost_level%TYPE
427 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
428 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
429 ) is
430 --
431 l_exists varchar2(1);
432 l_proc varchar2(72) := g_package||'chk_cost_level';
433 l_api_updating boolean;
434
435 cursor csr_upd_cost_level is
436 select null
437 from OTA_TRAINING_PLAN_COSTS
438 where tp_measurement_type_id = p_tp_measurement_type_id
439 and business_group_id = p_business_group_id;
440 Begin
441 --
442 -- check mandatory parameters have been set
443 --
444 hr_utility.set_location('Entering:'||l_proc, 5);
445 hr_api.mandatory_arg_error
446 (p_api_name => l_proc
447 ,p_argument => 'p_business_group_id'
448 ,p_argument_value => p_business_group_id
449 );
450 --
451 hr_utility.set_location(' Step:'|| l_proc, 40);
452 hr_api.mandatory_arg_error
453 (p_api_name => l_proc
454 ,p_argument => 'p_effective_date'
455 ,p_argument_value => p_effective_date
456 );
457 --
458 hr_utility.set_location(' Step:'|| l_proc, 50);
459 hr_api.mandatory_arg_error
460 (p_api_name => l_proc
461 ,p_argument => 'p_cost_level'
462 ,p_argument_value => p_cost_level
463 );
464 --
465 l_api_updating := ota_tmt_shd.api_updating
466 (p_tp_measurement_type_id => p_tp_measurement_type_id
467 ,p_object_version_number => p_object_version_number
468 );
469 --
470 -- If this is a changing update, or a new insert, test
471 --
472 if (l_api_updating and
473 nvl(ota_tmt_shd.g_old_rec.cost_level, hr_api.g_varchar2) <>
474 nvl(p_cost_level, hr_api.g_varchar2))
475 or (NOT l_api_updating)
476 then
477 hr_utility.set_location(l_proc, 60);
478 --
479 -- Validate that the code exists in the lookups view
480 --
481 if hr_api.not_exists_in_hr_lookups
482 (p_effective_date => p_effective_date
483 ,p_lookup_type => 'OTA_TRAINING_PLAN_COST_LEVEL'
484 ,p_lookup_code => p_cost_level
485 ) then
486 -- Error, lookup not available
487 hr_utility.set_location(l_proc, 70);
488 fnd_message.set_name('OTA', 'OTA_13808_TMT_INV_COST');
489 fnd_message.raise_error;
490 End if;
491 --
492 End if;
493 --
494 -- but changes are only allowed if there are no current recs in cost table
495 --
496 If l_api_updating
497 and nvl(ota_tmt_shd.g_old_rec.cost_level, hr_api.g_varchar2) <>
498 nvl(p_cost_level, hr_api.g_varchar2) then
499 hr_utility.set_location(' Step:'|| l_proc, 80);
500 open csr_upd_cost_level;
501 fetch csr_upd_cost_level into l_exists;
502 If csr_upd_cost_level%FOUND then
503 close csr_upd_cost_level;
504 hr_utility.set_location(' Step:'|| l_proc, 90);
505 fnd_message.set_name('OTA', 'OTA_13809_TMT_UPD_COSTS');
506 fnd_message.raise_error;
507 End if;
508 close csr_upd_cost_level;
509 --
510 End if;
511 --
512 --
513 hr_utility.set_location(' Leaving:'||l_proc, 100);
514 end chk_cost_level;
515 -- ----------------------------------------------------------------------------
516 -- |----------------------<chk_many_budget_values_flag>------------------------|
517 -- ----------------------------------------------------------------------------
518 Procedure chk_many_budget_values_flag
519 (p_effective_date in date
520 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
521 ,p_many_budget_values_flag in ota_tp_measurement_types.many_budget_values_flag%TYPE
522 ,p_tp_measurement_code in ota_tp_measurement_types.tp_measurement_code%TYPE
526 --
523 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
524 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
525 ) is
527 l_exists varchar2(1);
528 l_proc varchar2(72) := g_package||'chk_many_budget_values_flag';
529 l_api_updating boolean;
530
531 cursor csr_upd_tp_budget_flag is
532 select count(pba.budget_value_id)
533 from PER_BUDGETS pb
534 ,PER_BUDGET_VERSIONS pbv
535 ,PER_BUDGET_ELEMENTS pbe
536 ,PER_BUDGET_VALUES pba
537 where pb.unit = p_tp_measurement_code
538 and pb.business_group_id = p_business_group_id
539 and pb.budget_id = pbv.budget_id
540 and pbv.budget_version_id = pbe.budget_version_id
541 and pbe.budget_element_id = pba.budget_element_id
542 and pb.budget_type_code = 'OTA_BUDGET'
543 group by pb.budget_id
544 having count(pba.budget_value_id) >= 2;
545 Begin
546 --
547 -- check mandatory parameters have been set
548 --
549 hr_utility.set_location('Entering:'||l_proc, 5);
550 hr_api.mandatory_arg_error
551 (p_api_name => l_proc
552 ,p_argument => 'p_tp_measurement_code'
553 ,p_argument_value => p_tp_measurement_code
554 );
555 --
556 hr_utility.set_location(' Step:'|| l_proc, 30);
557 hr_api.mandatory_arg_error
558 (p_api_name => l_proc
559 ,p_argument => 'p_business_group_id'
560 ,p_argument_value => p_business_group_id
561 );
562 --
563 hr_utility.set_location(' Step:'|| l_proc, 40);
564 hr_api.mandatory_arg_error
565 (p_api_name => l_proc
566 ,p_argument => 'p_effective_date'
567 ,p_argument_value => p_effective_date
568 );
569 --
570 hr_utility.set_location(' Step:'|| l_proc, 50);
571 hr_api.mandatory_arg_error
572 (p_api_name => l_proc
573 ,p_argument => 'p_many_budget_values_flag'
574 ,p_argument_value => p_many_budget_values_flag
575 );
576 --
577 l_api_updating := ota_tmt_shd.api_updating
578 (p_tp_measurement_type_id => p_tp_measurement_type_id
579 ,p_object_version_number => p_object_version_number
580 );
581 --
582 -- If this is a changing update, or a new insert, test
583 --
584 if ((l_api_updating and
585 nvl(ota_tmt_shd.g_old_rec.many_budget_values_flag, hr_api.g_varchar2) <>
586 nvl(p_many_budget_values_flag, hr_api.g_varchar2))
587 or (NOT l_api_updating))
588 then
589 hr_utility.set_location(l_proc, 60);
590 --
591 -- Validate that the code exists in the lookups view
592 --
593 if hr_api.not_exists_in_hr_lookups
594 (p_effective_date => p_effective_date
595 ,p_lookup_type => 'YES_NO'
596 ,p_lookup_code => p_many_budget_values_flag
597 ) then
598 -- Error, lookup not available
599 hr_utility.set_location(l_proc, 70);
600 fnd_message.set_name('OTA', 'OTA_13810_TMT_INV_MANY_BUDGETS');
601 fnd_message.raise_error;
602 End if;
603 --
604 End if;
605 --
606 -- but changes to 'N' are only allowed if there zero or 1 recs in budget elements
607 --
608 If l_api_updating
609 and p_many_budget_values_flag = 'N'
610 and (nvl(ota_tmt_shd.g_old_rec.many_budget_values_flag, hr_api.g_varchar2) <>
611 nvl(p_many_budget_values_flag, hr_api.g_varchar2)) then
612 hr_utility.set_location(' Step:'|| l_proc, 80);
613 open csr_upd_tp_budget_flag;
614 fetch csr_upd_tp_budget_flag into l_exists;
615 if csr_upd_tp_budget_flag%FOUND then
616 close csr_upd_tp_budget_flag;
617 fnd_message.set_name('OTA', 'OTA_13811_TMT_INV_UPD_FLAG');
618 fnd_message.raise_error;
619 else
620 close csr_upd_tp_budget_flag;
621 End if;
622 --
623 End if;
624 --
625 hr_utility.set_location(' Leaving:'||l_proc, 100);
626 end chk_many_budget_values_flag;
627 -- ----------------------------------------------------------------------------
628 -- |----------------------<chk_item_type_usage_id>----------------------------|
629 -- ----------------------------------------------------------------------------
630 Procedure chk_item_type_usage_id
631 (p_item_type_usage_id in ota_tp_measurement_types.item_type_usage_id%TYPE
632 ,p_business_group_id in ota_tp_measurement_types.business_group_id%TYPE
633 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
634 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
635 ) is
636 --
637 l_exists varchar2(1);
638 l_proc varchar2(72) := g_package||'chk_item_type_usage_id';
639 l_api_updating boolean;
640
641 cursor csr_chk_item_type is
642 select null
643 from HR_SUMMARY_ITEM_TYPE_USAGE
644 where item_type_usage_id = p_item_type_usage_id
645 and business_group_id = p_business_group_id;
646 Begin
647 --
648 -- check mandatory parameters have been set
649 --
650 --
651 hr_utility.set_location('Entering:'||l_proc, 5);
652 hr_api.mandatory_arg_error
656 );
653 (p_api_name => l_proc
654 ,p_argument => 'p_business_group_id'
655 ,p_argument_value => p_business_group_id
657 l_api_updating := ota_tmt_shd.api_updating
658 (p_tp_measurement_type_id => p_tp_measurement_type_id
659 ,p_object_version_number => p_object_version_number
660 );
661 --
662 -- It can always change to null
663 -- If this is a changing update, or a new insert, test
664 --
665 If p_item_type_usage_id is not null then
666 If ((l_api_updating and
667 nvl(ota_tmt_shd.g_old_rec.item_type_usage_id, hr_api.g_number) <>
668 nvl(p_item_type_usage_id, hr_api.g_number))
669 or (NOT l_api_updating))
670 Then
671 -- Test that it exists in hr_summary
672 hr_utility.set_location(l_proc, 10);
673 --
674 open csr_chk_item_type;
675 fetch csr_chk_item_type into l_exists;
676 If csr_chk_item_type%NOTFOUND then
677 -- Error, item type does not exist.
678 close csr_chk_item_type;
679 hr_utility.set_location(' Step:'|| l_proc, 20);
680 fnd_message.set_name('OTA', 'OTA_13812_TMT_INV_CALC');
681 fnd_message.raise_error;
682 End if;
683 close csr_chk_item_type;
684 --
685 End if;
686 End if;
687 --
688 hr_utility.set_location(' Leaving:'||l_proc, 30);
689 end chk_item_type_usage_id;
690 --
691 -- ----------------------------------------------------------------------------
692 -- |----------------------<chk_del_tp_measurement_type_id>--------------------|
693 -- ----------------------------------------------------------------------------
694 Procedure chk_del_tp_measurement_type_id
695 (p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
696 ) is
697 --
698 l_exists varchar2(1);
699 l_proc varchar2(72) := g_package||'chk_del_tp_measurement_type_id';
700
701 cursor csr_del_tp_measurement_type_id is
702 select null
703 from OTA_TRAINING_PLAN_COSTS
704 where tp_measurement_type_id = p_tp_measurement_type_id;
705 Begin
706 --
707 -- check mandatory parameters have been set
708 --
709 hr_utility.set_location('Entering:'||l_proc, 5);
710 hr_api.mandatory_arg_error
711 (p_api_name => l_proc
712 ,p_argument => 'p_tp_measurement_type_id'
713 ,p_argument_value => p_tp_measurement_type_id
714 );
715 --
716 -- Check that the code can be deleted
717 --
718 open csr_del_tp_measurement_type_id;
719 fetch csr_del_tp_measurement_type_id into l_exists;
720 if csr_del_tp_measurement_type_id%FOUND then
721 close csr_del_tp_measurement_type_id;
722 hr_utility.set_location(' Step:'|| l_proc, 10);
723 fnd_message.set_name('OTA', 'OTA_13802_TMT_DEL_COST');
724 fnd_message.raise_error;
725 end if;
726 close csr_del_tp_measurement_type_id;
727 hr_utility.set_location(' Leaving:'||l_proc, 20);
728 --
729 end chk_del_tp_measurement_type_id;
730 --
731 -- ----------------------------------------------------------------------------
732 -- |-----------------------------------<chk_legislative_setup>----------------|
733 -- ----------------------------------------------------------------------------
734 Procedure chk_legislative_setup(
735 p_legislation_code in per_business_groups.legislation_code%TYPE
736 ,p_tp_measurement_code in ota_tp_measurement_types.tp_measurement_code%TYPE
737 ,p_unit in ota_tp_measurement_types.unit%TYPE
738 ,p_budget_level in ota_tp_measurement_types.budget_level%TYPE
739 ,p_cost_level in ota_tp_measurement_types.cost_level%TYPE
740 ,p_many_budget_values_flag in ota_tp_measurement_types.many_budget_values_flag%TYPE
741 ,p_object_version_number in ota_tp_measurement_types.object_version_number%TYPE
742 ,p_tp_measurement_type_id in ota_tp_measurement_types.tp_measurement_type_id%TYPE
743 ) is
744 --
745 l_exists varchar2(1);
746 l_proc varchar2(72) := g_package||'chk_legislative_setup';
747 l_api_updating boolean;
748 --
749 Begin
750 --
751 -- check mandatory parameters have been set
752 --
753 hr_utility.set_location('Entering:'||l_proc, 5);
754 hr_api.mandatory_arg_error
755 (p_api_name => l_proc
756 ,p_argument => 'tp_measurement_code'
760 hr_utility.set_location(' Step:'|| l_proc, 10);
757 ,p_argument_value => p_tp_measurement_code
758 );
759 --
761 hr_api.mandatory_arg_error
762 (p_api_name => l_proc
763 ,p_argument => 'p_unit'
764 ,p_argument_value => p_unit
765 );
766 --
767 hr_utility.set_location(' Step:'|| l_proc, 20);
768 hr_api.mandatory_arg_error
769 (p_api_name => l_proc
770 ,p_argument => 'p_cost_level'
771 ,p_argument_value => p_cost_level
772 );
773 --
774 hr_utility.set_location(' Step:'|| l_proc, 30);
775 hr_api.mandatory_arg_error
776 (p_api_name => l_proc
777 ,p_argument => 'p_budget_level'
778 ,p_argument_value => p_budget_level
779 );
780 --
781 hr_utility.set_location(' Step:'|| l_proc, 40);
782 hr_api.mandatory_arg_error
783 (p_api_name => l_proc
784 ,p_argument => 'p_many_budget_values_flag'
785 ,p_argument_value => p_many_budget_values_flag
786 );
787 --
788 hr_utility.set_location(' Step:'|| l_proc, 50);
789 hr_api.mandatory_arg_error
790 (p_api_name => l_proc
791 ,p_argument => 'p_legislation_code'
792 ,p_argument_value => p_legislation_code
793 );
794 --
795 -- French Specific setup
796 --
797 if p_legislation_code = 'FR' then
798 if p_tp_measurement_code = 'FR_SALARY_PER_CATEGORY'
799 or p_tp_measurement_code = 'FR_DELEGATES_PER_CATEGORY'
800 or p_tp_measurement_code = 'FR_NUMBER_EVENTS'
801 or p_tp_measurement_code = 'FR_DURATION_HOURS'
802 or p_tp_measurement_code = 'FR_ACTUAL_HOURS' then
803 --
804 -- Test each measurement type individually for setup
805 --
806 if ( p_tp_measurement_code = 'FR_SALARY_PER_CATEGORY'
807 and (p_unit <> 'M' or p_budget_level <> 'PLAN'
808 or p_cost_level <> 'NONE' ) )
809 or
810 ( p_tp_measurement_code = 'FR_DELEGATES_PER_CATEGORY'
811 and (p_unit <> 'I' or p_budget_level = 'PLAN'
812 or p_cost_level <> 'NONE' ) )
813 or
814 ( p_tp_measurement_code = 'FR_NUMBER_EVENTS'
815 and (p_unit <> 'I' or p_budget_level <> 'ACTIVITY'
816 or p_cost_level <> 'NONE' or p_many_budget_values_flag = 'Y' ) )
817 or
818 ( p_tp_measurement_code = 'FR_DURATION_HOURS'
819 and (p_unit <> 'N' or p_budget_level = 'PLAN'
820 or p_cost_level <> 'NONE' or p_many_budget_values_flag = 'Y' ) )
821 or
822 ( p_tp_measurement_code = 'FR_ACTUAL_HOURS'
823 and (p_unit <> 'N' or p_cost_level <> 'DELEGATE' ) ) then
824 --
825 -- Error, legislative setup not correct
826 --
827 hr_utility.set_location(l_proc, 70);
828 fnd_message.set_name('OTA', 'OTA_13876_TMT_INV_SETUP');
829 fnd_message.raise_error;
830 end if;
831 end if;
832 end if;
833 --
834 hr_utility.set_location(' Leaving:'||l_proc, 100);
835 end chk_legislative_setup;
836 --
837 end ota_tmt_bus1;