[Home] [Help]
PACKAGE BODY: APPS.OTA_TPC_BUS1
Source
1 Package body ota_tpc_bus1 as
2 /* $Header: ottpcrhi.pkb 115.5 2003/06/17 14:27:43 sfmorris noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tpc_bus1.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------<chk_tp_measurement_type_id>-------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 Procedure chk_tp_measurement_type_id
16 (p_tp_measurement_type_id in ota_training_plan_costs.tp_measurement_type_id%TYPE
17 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
18 ) is
19 --
20 l_cost_level varchar2(30);
21 l_proc varchar2(72) := g_package|| 'chk_tp_measurement_type_id';
22 --
23 cursor csr_tp_measurement_type is
24 select cost_level
25 from OTA_TP_MEASUREMENT_TYPES
26 where tp_measurement_type_id = p_tp_measurement_type_id
27 and business_group_id = p_business_group_id;
28 Begin
29 --
30 -- check mandatory parameters have been set
31 --
32 hr_utility.set_location(' Step:'|| l_proc, 20);
33 hr_api.mandatory_arg_error
34 (p_api_name => l_proc
35 ,p_argument => 'p_tp_measurement_type_id'
36 ,p_argument_value => p_tp_measurement_type_id
37 );
38 --
39 hr_utility.set_location(' Step:'|| l_proc, 30);
40 hr_api.mandatory_arg_error
41 (p_api_name => l_proc
42 ,p_argument => 'p_business_group_id'
43 ,p_argument_value => p_business_group_id
44 );
45 --
46 --
47 hr_utility.set_location(' Step:'|| l_proc, 50);
48 open csr_tp_measurement_type;
49 fetch csr_tp_measurement_type into l_cost_level;
50 if csr_tp_measurement_type%NOTFOUND then
51 close csr_tp_measurement_type;
52 hr_utility.set_location(' Step:'|| l_proc, 60);
53 fnd_message.set_name('OTA', 'OTA_13826_TPC_NO_MEASURE_DEF');
54 fnd_message.raise_error;
55 elsif l_cost_level = 'NONE' then
56 close csr_tp_measurement_type;
57 hr_utility.set_location(' Step:'|| l_proc, 70);
58 fnd_message.set_name('OTA', 'OTA_13827_TPC_BAD_COST_LEVEL');
59 fnd_message.raise_error;
60 else
61 hr_utility.set_location(' Step:'|| l_proc, 80);
62 close csr_tp_measurement_type;
63 end if;
64 --
65 hr_utility.set_location(' Leaving:'||l_proc, 90);
66 end chk_tp_measurement_type_id;
67 -- ----------------------------------------------------------------------------
68 -- |----------------------<chk_training_plan_id>-------------------------------|
69 -- ----------------------------------------------------------------------------
70 --
71 Procedure chk_training_plan_id
72 (p_training_plan_id in ota_training_plan_costs.training_plan_id%TYPE
73 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
74 ) is
75 --
76 l_exists varchar2(1);
77 l_proc varchar2(72) := g_package|| 'chk_training_plan_id';
78 --
79 cursor csr_training_plan_id is
80 select null
81 from OTA_TRAINING_PLANS
82 where training_plan_id = p_training_plan_id
83 and business_group_id = p_business_group_id;
84 Begin
85 --
86 -- check mandatory parameters have been set
87 --
88 hr_utility.set_location(' Step:'|| l_proc, 20);
89 hr_api.mandatory_arg_error
90 (p_api_name => l_proc
91 ,p_argument => 'p_training_plan_id'
92 ,p_argument_value => p_training_plan_id
93 );
94 --
95 hr_utility.set_location(' Step:'|| l_proc, 30);
96 hr_api.mandatory_arg_error
97 (p_api_name => l_proc
98 ,p_argument => 'p_business_group_id'
99 ,p_argument_value => p_business_group_id
100 );
101 --
102 --
103 hr_utility.set_location(' Step:'|| l_proc, 50);
104 open csr_training_plan_id;
105 fetch csr_training_plan_id into l_exists;
106 if csr_training_plan_id%NOTFOUND then
107 close csr_training_plan_id;
108 hr_utility.set_location(' Step:'|| l_proc, 60);
109 fnd_message.set_name('OTA', 'OTA_13828_TPC_NO_TRAINING_PLAN');
110 fnd_message.raise_error;
111 else
112 hr_utility.set_location(' Step:'|| l_proc, 80);
113 close csr_training_plan_id;
114 end if;
115 --
116 hr_utility.set_location(' Leaving:'||l_proc, 90);
117 end chk_training_plan_id;
118 -- ----------------------------------------------------------------------------
119 -- |----------------------<chk_booking_id>------------------------------------|
120 -- ----------------------------------------------------------------------------
121 --
122 Procedure chk_booking_id
123 (p_booking_id in ota_training_plan_costs.booking_id%TYPE
124 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
125 ) is
126 --
127 l_business_group_id ota_training_plan_costs.business_group_id%TYPE;
128 l_proc varchar2(72) := g_package|| 'chk_booking_id';
129 --
130 cursor csr_booking_id is
131 select business_group_id
132 from OTA_DELEGATE_BOOKINGS
133 where booking_id = p_booking_id;
134 Begin
135 --
136 -- check mandatory parameters have been set
137 --
138 --
139 hr_utility.set_location(' Step:'|| l_proc, 30);
140 hr_api.mandatory_arg_error
141 (p_api_name => l_proc
142 ,p_argument => 'p_business_group_id'
143 ,p_argument_value => p_business_group_id
144 );
145 --
146 --
147 hr_utility.set_location(' Step:'|| l_proc, 50);
148 if p_booking_id is not null then
149 open csr_booking_id;
150 fetch csr_booking_id into l_business_group_id;
151 if csr_booking_id%NOTFOUND then
152 close csr_booking_id;
153 hr_utility.set_location(' Step:'|| l_proc, 60);
154 fnd_message.set_name('OTA', 'OTA_13829_TPC_NO_BOOKING');
155 fnd_message.raise_error;
156 else
157 close csr_booking_id;
158 hr_utility.set_location(' Step:'|| l_proc, 70);
159 if l_business_group_id <> p_business_group_id then
160 fnd_message.set_name('OTA', 'OTA_13830_TPC_BOOKING_BAD_BG');
161 fnd_message.raise_error;
162 end if;
163 end if;
164 end if;
165 --
166 hr_utility.set_location(' Leaving:'||l_proc, 90);
167 end chk_booking_id;
168 -- ----------------------------------------------------------------------------
169 -- |----------------------<chk_booking_event>----------------------------------|
170 -- ----------------------------------------------------------------------------
171 --
172 Procedure chk_booking_event
173 (p_booking_id in ota_training_plan_costs.booking_id%TYPE
174 ,p_event_id in ota_training_plan_costs.event_id%TYPE
175 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
176 ,p_tp_measurement_type_id in ota_training_plan_costs.tp_measurement_type_id%TYPE
177 ) is
178 --
179 l_cost_level ota_tp_measurement_types.cost_level%TYPE;
180 l_proc varchar2(72) := g_package|| 'chk_booking_event';
181 --
182 cursor csr_booking_event is
183 select cost_level
184 from OTA_TP_MEASUREMENT_TYPES
185 where tp_measurement_type_id = p_tp_measurement_type_id
186 and business_group_id = p_business_group_id;
187 Begin
188 --
189 -- check mandatory parameters have been set
190 --
191 --
192 hr_utility.set_location(' Step:'|| l_proc, 30);
193 hr_api.mandatory_arg_error
194 (p_api_name => l_proc
195 ,p_argument => 'p_business_group_id'
196 ,p_argument_value => p_business_group_id
197 );
198 --
199 --
200 hr_utility.set_location(' Step:'|| l_proc, 40);
201 hr_api.mandatory_arg_error
202 (p_api_name => l_proc
203 ,p_argument => 'p_tp_measurement_type_id'
204 ,p_argument_value => p_tp_measurement_type_id
205 );
206 --
207 -- One and only one of event_id, booking_id must be null;
208 --
209 If (p_booking_id is not null and p_event_id is not null) then
210 fnd_message.set_name('OTA', 'OTA_13831_TPC_EVENT_OR_BOOKING');
211 fnd_message.raise_error;
212 end if;
213
214 hr_utility.set_location(' Step:'|| l_proc, 50);
215 open csr_booking_event;
216 fetch csr_booking_event into l_cost_level;
217 if csr_booking_event%NOTFOUND then
218 close csr_booking_event;
219 hr_utility.set_location(' Step:'|| l_proc, 60);
220 fnd_message.set_name('OTA', 'OTA_13826_TPC_NO_MEASURE_DEF');
221 fnd_message.raise_error;
222 else
223 close csr_booking_event;
224 hr_utility.set_location(' Step:'|| l_proc, 70);
225 if (p_event_id is not null and l_cost_level <> 'EVENT') then
226 fnd_message.set_name('OTA', 'OTA_13842_TPC_NOT_EVENT_COSTS');
227 fnd_message.raise_error;
228 elsif
229 (p_event_id is null and l_cost_level = 'EVENT') then
230 fnd_message.set_name('OTA', 'OTA_13832_TPC_EVENT_COST_LEVEL');
231 fnd_message.raise_error;
232 elsif (p_booking_id is not null) and
233 (l_cost_level = 'PLAN' OR l_cost_level = 'EVENT') then
234 hr_utility.set_location(' Step:'|| l_proc, 80);
235 fnd_message.set_name('OTA', 'OTA_13833_TPC_DELEGATE_COST');
236 fnd_message.raise_error;
237 elsif (p_booking_id is null) and
238 (l_cost_level = 'DELEGATE') then
239 hr_utility.set_location(' Step:'|| l_proc, 90);
240 fnd_message.set_name('OTA', 'OTA_13841_TPC_SUPPLY_DELEGATE');
241 fnd_message.raise_error;
242 end if;
243 end if;
244 --
245 hr_utility.set_location(' Leaving:'||l_proc, 100);
246 end chk_booking_event;
247 -- ----------------------------------------------------------------------------
248 -- |----------------------<chk_event_id>---------------------------------------|
249 -- ----------------------------------------------------------------------------
250 --
251 Procedure chk_event_id
252 (p_event_id in ota_training_plan_costs.event_id%TYPE
253 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
254 ,p_training_plan_id in ota_training_plans.training_plan_id%TYPE
255 ) is
256 --
257 l_business_group_id ota_training_plan_costs.business_group_id%TYPE;
258 l_plan_start_date per_time_periods.start_date%TYPE;
259 l_event_start_date ota_events.course_start_date%TYPE;
260 l_proc varchar2(72) := g_package|| 'chk_event_id';
261 --
262 cursor csr_event_id is
263 select business_group_id, course_start_date
264 from OTA_EVENTS
265 where event_id = p_event_id;
266
267 cursor csr_event_id_dates is
268 select ptp.start_date
269 from PER_TIME_PERIODS ptp
270 ,OTA_TRAINING_PLANS tps
271 where ptp.time_period_id = tps.time_period_id
272 and tps.training_plan_id = p_training_plan_id;
273
274 Begin
275 --
276 -- check mandatory parameters have been set
277 --
278 --
279 hr_utility.set_location(' Step:'|| l_proc, 30);
280 hr_api.mandatory_arg_error
281 (p_api_name => l_proc
282 ,p_argument => 'p_training_plan_id'
283 ,p_argument_value => p_training_plan_id
284 );
285 --
286 hr_utility.set_location(' Step:'|| l_proc, 40);
287 hr_api.mandatory_arg_error
288 (p_api_name => l_proc
289 ,p_argument => 'p_business_group_id'
290 ,p_argument_value => p_business_group_id
291 );
292 --
293 --
294 hr_utility.set_location(' Step:'|| l_proc, 50);
295 if p_event_id is not null then
296 open csr_event_id;
297 fetch csr_event_id into l_business_group_id, l_event_start_date;
298 if csr_event_id%NOTFOUND then
299 close csr_event_id;
300 hr_utility.set_location(' Step:'|| l_proc, 70);
301 fnd_message.set_name('OTA', 'OTA_13834_TPC_NO_EVENT');
302 fnd_message.raise_error;
303 else
304 close csr_event_id;
305 hr_utility.set_location(' Step:'|| l_proc, 80);
306 if l_business_group_id <> p_business_group_id then
307 fnd_message.set_name('OTA', 'OTA_13835_TPC_EVENT_BAD_BG');
308 fnd_message.raise_error;
309 end if;
310 end if;
311 --
312 -- Check that the dates correspond, if there is a course start date
313 --
314 if l_event_start_date is not null then
315 hr_utility.set_location(' Step:'|| l_proc, 90);
316 open csr_event_id_dates;
317 fetch csr_event_id_dates into l_plan_start_date;
318 close csr_event_id_dates;
319 if l_plan_start_date > l_event_start_date then
320 hr_utility.set_location(' Step:'|| l_proc, 100);
321 fnd_message.set_name('OTA', 'OTA_13836_TPC_EVENT_DATE');
322 fnd_message.raise_error;
323 end if;
324 end if;
325 end if;
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 110);
328 end chk_event_id;
329 -- ----------------------------------------------------------------------------
330 -- |----------------------<chk_currency_value>---------------------------------|
331 -- ----------------------------------------------------------------------------
332 --
333 Procedure chk_currency_value
334 (p_currency_code in ota_training_plan_costs.currency_code%TYPE
335 ,p_training_plan_cost_id in ota_training_plan_costs.training_plan_cost_id%TYPE
336 ,p_object_version_number in ota_training_plan_costs.object_version_number%TYPE
337 ,p_business_group_id in ota_training_plan_costs.business_group_id%TYPE
338 ,p_amount in ota_training_plan_costs.amount%TYPE
339 ,p_tp_measurement_type_id in ota_training_plan_costs.tp_measurement_type_id%TYPE
340 )is
341 --
342 l_exists varchar2(1);
343 l_proc varchar2(72) := g_package|| 'chk_currency_value';
344 l_api_updating boolean;
345 l_unit ota_tp_measurement_types.unit%TYPE;
346 --
347 cursor csr_currency_code is
348 select null
349 from FND_CURRENCIES
350 where currency_code = p_currency_code;
351 cursor csr_unit is
352 select unit
353 from OTA_TP_MEASUREMENT_TYPES
354 where tp_measurement_type_id = p_tp_measurement_type_id
355 and business_group_id = p_business_group_id;
356 --
357 Begin
358 --
359 -- check mandatory parameters have been set. Currency code can
360 -- be null, so it is not mandatory.
361 --
362 hr_utility.set_location(' Step:'|| l_proc, 30);
363 hr_api.mandatory_arg_error
364 (p_api_name => l_proc
365 ,p_argument => 'p_business_group_id'
366 ,p_argument_value => p_business_group_id
367 );
368 --
369 hr_utility.set_location(' Step:'|| l_proc, 40);
370 hr_api.mandatory_arg_error
371 (p_api_name => l_proc
372 ,p_argument => 'p_amount'
373 ,p_argument_value => p_amount
374 );
375 --
376 hr_utility.set_location(' Step:'|| l_proc, 40);
377 hr_api.mandatory_arg_error
378 (p_api_name => l_proc
379 ,p_argument => 'p_tp_measurement_type_id'
380 ,p_argument_value => p_tp_measurement_type_id
381 );
382 --
383 l_api_updating := ota_tpc_shd.api_updating
384 (p_training_plan_cost_id => p_training_plan_cost_id
385 ,p_object_version_number => p_object_version_number
386 );
387 --
388 -- If this is a changing update, or a new insert, test
389 --
390 if ((l_api_updating and
391 nvl(ota_tpc_shd.g_old_rec.currency_code, hr_api.g_varchar2) <>
392 nvl(p_currency_code, hr_api.g_varchar2)
393 or
394 nvl(ota_tpc_shd.g_old_rec.amount, hr_api.g_number) <>
395 nvl(p_amount, hr_api.g_number))
396 or (NOT l_api_updating))
397 then
398 hr_utility.set_location(' Step:'|| l_proc, 50);
399 if p_currency_code is not null then
400 open csr_currency_code;
401 fetch csr_currency_code into l_exists;
402 if csr_currency_code%NOTFOUND then
403 close csr_currency_code;
404 fnd_message.set_name('AOL', 'MC_INVALID_CURRENCY');
405 fnd_message.set_token('CODE', p_currency_code);
406 fnd_message.raise_error;
407 else
408 close csr_currency_code;
409 end if;
410 end if;
411 --
412 -- Get the measurement type UNIT, and perform validation
413 --
414 hr_utility.set_location(' Step:'|| l_proc, 60);
415 open csr_unit;
416 fetch csr_unit into l_unit;
417 if csr_unit%NOTFOUND then
418 close csr_unit;
419 fnd_message.set_name('OTA', 'OTA_13826_TPC_NO_MEASURE_DEF');
420 fnd_message.raise_error;
421 else
422 close csr_unit;
423 if (l_unit = 'M' and p_currency_code is null)
424 or (l_unit <> 'M' and p_currency_code is not null) then
425 fnd_message.set_name('OTA', 'OTA_13838_TPC_BAD_CURR_VALUE');
426 fnd_message.raise_error;
427 end if;
428 end if;
429 --
430 -- validate the format of the value field
431 --
432 hr_utility.set_location(' Step:'|| l_proc, 70);
433 if l_unit = 'M' then
434 hr_dbchkfmt.is_db_format
435 (p_value => p_amount
436 ,p_arg_name => 'VALUE'
437 ,p_format => 'MONEY'
438 ,p_curcode => p_currency_code);
439 else
440 hr_dbchkfmt.is_db_format
441 (p_value => p_amount
442 ,p_arg_name => 'VALUE'
443 ,p_format => l_unit);
444 end if;
445 end if;
446 --
447 hr_utility.set_location(' Leaving:'||l_proc, 90);
448 end chk_currency_value;
449 -- ----------------------------------------------------------------------------
450 -- |----------------------<chk_unique>-----------------------------------------|
451 -- ----------------------------------------------------------------------------
452 --
453 Procedure chk_unique
454 (p_tp_measurement_type_id in ota_training_plan_costs.tp_measurement_type_id%TYPE
455 ,p_event_id in ota_training_plan_costs.event_id%TYPE
456 ,p_booking_id in ota_training_plan_costs.booking_id%TYPE
457 ,p_training_plan_id in ota_training_plan_costs.training_plan_id%TYPE
458 ) is
459 --
460 l_proc varchar2(72) := g_package|| 'chk_unique';
461 l_exists varchar2(1);
462 --
463 cursor csr_unique is
464 select null
465 from OTA_TRAINING_PLAN_COSTS
466 where tp_measurement_type_id = p_tp_measurement_type_id
467 and training_plan_id = p_training_plan_id
468 and((p_event_id is not null and event_id = p_event_id)
469 or p_event_id is null)
470 and((p_booking_id is not null and booking_id = p_booking_id)
471 or p_booking_id is null);
472 Begin
473 --
474 -- check mandatory parameters have been set
475 --
476 --
477 hr_utility.set_location(' Step:'|| l_proc, 30);
478 hr_api.mandatory_arg_error
479 (p_api_name => l_proc
480 ,p_argument => 'p_tp_measurement_type_id'
481 ,p_argument_value => p_tp_measurement_type_id
482 );
483 --
484 --
485 hr_utility.set_location(' Step:'|| l_proc, 40);
486 hr_api.mandatory_arg_error
487 (p_api_name => l_proc
488 ,p_argument => 'p_training_plan_id'
489 ,p_argument_value => p_training_plan_id
490 );
491 --
492 -- check the combination is unique
493 --
494 hr_utility.set_location(' Step:'|| l_proc, 50);
495 open csr_unique;
496 fetch csr_unique into l_exists;
497 if csr_unique%FOUND then
498 close csr_unique;
499 hr_utility.set_location(' Step:'|| l_proc, 60);
500 fnd_message.set_name('OTA', 'OTA_13837_TPC_BAD_UNIQUE_COST');
501 fnd_message.raise_error;
502 else
503 close csr_unique;
504 hr_utility.set_location(' Step:'|| l_proc, 70);
505 end if;
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 90);
508 end chk_unique;
509 end ota_tpc_bus1;