1 PACKAGE ota_tav_bus as
2 /* $Header: ottav01t.pkh 120.1.12010000.2 2008/12/19 09:21:42 shwnayak ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------< check_min_max_values >-------------------------|
6 -- ----------------------------------------------------------------------------
7 --
8 -- PUBLIC
9 -- Description:
10 -- The minimum attendees must be less then or equal to the maximum attendees.
11 --
12 Procedure check_min_max_values
13 (
14 p_min in number
15 ,p_max in number
16 );
17 --
18 -- ----------------------------------------------------------------------------
19 -- |--------------------------< check_unique_name >---------------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 -- PUBLIC
23 -- Description:
24 -- Validates the unique key.
25 --
26 Procedure check_unique_name
27 (
28 p_business_group_id in number
29 ,p_activity_id in number
30 ,p_version_name in varchar2
31 ,p_activity_version_id in number
32 );
33 --
34 -- ----------------------------------------------------------------------------
35 -- |---------------------< check_superseding_version >------------------------|
36 -- ----------------------------------------------------------------------------
37 --
38 -- PUBLIC
39 -- Description:
40 -- A activity version may not be superseded ba a activity whose end_date
41 -- is greater than it's own. The supersedinthg activity version must have
42 -- an end date greater than the end date of the activity it supersedes.
43 --
44 Procedure check_superseding_version
45 (
46 p_sup_act_vers_id in number
47 ,p_end_date in date
48 );
49 --
50 -- ----------------------------------------------------------------------------
51 -- |--------------------------< check_user_status >---------------------------|
52 -- ----------------------------------------------------------------------------
53 --
54 -- PUBLIC
55 -- Description:
56 -- The user status must be in the domain 'Activity User Status'.
57 --
58 Procedure check_user_status
59 (
60 p_user_status in varchar2
61 );
62 --
63 -- ----------------------------------------------------------------------------
64 -- |-------------------------< check_success_criteria >-----------------------|
65 -- ----------------------------------------------------------------------------
66 --
67 -- PUBLIC
68 -- Description:
69 -- The success criteria must be in the domain 'Activity Success Criteria'.
70 --
71 Procedure check_success_criteria
72 (
73 p_succ_criteria in varchar2
74 );
75 --
76 -- ----------------------------------------------------------------------------
77 -- |----------------------< get_activity_version_id >-------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- PUBLIC
81 -- Description:
82 -- Return the surrogate key from a passed parameter
83 --
84 Function get_activity_version_id
85 (
86 p_activity_id in number
87 ,p_version_name in varchar2
88 )
89 Return number;
90 --
91 -- ----------------------------------------------------------------------------
92 -- |----------------------< get_activity_version_name >-----------------------|
93 -- ----------------------------------------------------------------------------
94 --
95 -- PUBLIC
96 -- Description:
97 -- Ruturn the activity version name.
98 --
99 Function get_activity_version_name
100 (
101 p_activity_version_id in number
102 )
103 Return VARCHAR2;
104 --
105 pragma restrict_references ( get_activity_version_name, WNDS, WNPS);
106 --
107 -- ----------------------------------------------------------------------------
108 -- |--------------------------< check_start_end_dates >-----------------------|
109 -- ----------------------------------------------------------------------------
110 --
111 -- PUBLIC
112 -- Description:
113 -- Validates the startdate and enddate.
114 -- Startdate must be less than, or equal to, enddate.
115 --
116 Procedure check_start_end_dates
117 (
118 p_start_date in date
119 ,p_end_date in date
120 );
121 --
122 -- ----------------------------------------------------------------------------
123 -- |-------------------------< check_dates_update_ple >-----------------------|
124 -- ----------------------------------------------------------------------------
125 --
126 -- PUBLIC
127 -- Description:
128 -- Validates the startdate and enddate.
129 -- Update of start and end dates must not invalidate valid pricing details
130 -- for this activity version.
131 --
132 Procedure check_dates_update_ple
133 (
134 p_activity_version_id in number
135 ,p_start_date in date
136 ,p_end_date in date
137 );
138 --
139 -- ----------------------------------------------------------------------------
140 -- |-------------------------< check_dates_update_tbd >-----------------------|
141 -- ----------------------------------------------------------------------------
142 --
143 -- PUBLIC
144 -- Description:
145 -- Validates the startdate and enddate.
146 -- Update of start and end dates must not invalidate booking deals
147 -- questions for this activity version.
148 --
149 Procedure check_dates_update_tbd
150 (
151 p_activity_version_id in number
152 ,p_start_date in date
153 ,p_end_date in date
154 );
155 --
156 -- ----------------------------------------------------------------------------
157 -- |-------------------------< check_dates_update_evt >-----------------------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- PUBLIC
161 -- Description:
162 -- Validates the startdate and enddate.
163 -- Update of start and end dates must not invalidate events
164 -- for this activity version.
165 -- This requires a check to ensure that the activity version dates do not
166 -- invalidate the Event Booking DAtes or the Event Course Dates if either
167 -- have been entered.
168 --
169 Procedure check_dates_update_evt
170 (
171 p_activity_version_id in number
172 ,p_start_date in date
173 ,p_end_date in date
174 );
175 --
176 -- ----------------------------------------------------------------------------
177 -- |-------------------------< check_if_evt_exists >--------------------------|
178 -- ----------------------------------------------------------------------------
179 --
180 -- PUBLIC
181 -- Description:
182 -- Delete Validation.
183 -- This activity version may not be deleted if child rows in
184 -- ota_events exists.
185 --
186 Procedure check_if_evt_exists
187 (
188 p_activity_version_id in number
189 );
190 --
191 -- ----------------------------------------------------------------------------
192 -- |-------------------------< check_if_off_exists >--------------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 -- PUBLIC
196 -- Description:
197 -- Delete Validation.
198 -- This activity version may not be deleted if child rows in
199 -- ota_events exists.
200 --
201 Procedure check_if_off_exists
202 (
203 p_activity_version_id in number
204 );
205 --
206
207 -- ----------------------------------------------------------------------------
208 -- |-------------------------< check_if_tbd_exists >--------------------------|
209 -- ----------------------------------------------------------------------------
210 --
211 -- PUBLIC
212 -- Description:
213 -- Delete Validation.
214 -- This activity version may not be deleted if child rows in
215 -- ota_booking_deals exists.
216 --
217 Procedure check_if_tbd_exists
218 (
219 p_activity_version_id in number
220 );
221 --
222 -- ----------------------------------------------------------------------------
223 -- |-------------------------< check_if_ple_exists >--------------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 -- PUBLIC
227 -- Description:
228 -- Delete Validation.
229 -- This activity version may not be deleted if child rows in
230 -- ota_price_lists_entries exists.
231 --
232 Procedure check_if_ple_exists
233 (
234 p_activity_version_id in number
235 );
236 -- ----------------------------------------------------------------------------
237 -- |-------------------------< check_if_tpm_exists >--------------------------|
238 -- ----------------------------------------------------------------------------
239 -- PUBLIC
240 -- Description:
241 -- Delete Validation.
242 -- This activity version may not be deleted if child rows in
243 -- ota_training_plan_members exist.
244 --
245 Procedure check_if_tpm_exists
246 (
247 p_activity_version_id in number
248 );
249 --
250 -- ----------------------------------------------------------------------------
251 -- |-------------------------< check_if_tav_exists >--------------------------|
252 -- ----------------------------------------------------------------------------
253 --
254 -- PUBLIC
255 -- Description:
256 -- Delete Validation.
257 -- This activity version may not be deleted if child rows in
258 -- ota_activity_versions exists where this activity version has superseded
259 -- another earlier activity version.
260 --
261 Procedure check_if_tav_exists
262 (
263 p_activity_version_id in number
264 );
265 --
266 -- ----------------------------------------------------------------------------
267 -- |------------------------< check_duration_units >--------------------------|
268 -- ----------------------------------------------------------------------------
269 --
270 -- PUBLIC
271 -- Description:
272 -- The duration units must be in the domain 'Units'.
273 --
274 Procedure check_duration_units
275 (
276 p_duration_units in varchar2
277 );
278 --
279 -- ----------------------------------------------------------------------------
280 -- |--------------------------< check_duration >------------------------------|
281 -- ----------------------------------------------------------------------------
282 --
283 -- PUBLIC
284 -- Description:
285 -- The duration must be a positive integer greater than zero.
286 --
287 Procedure check_duration
288 (
289 p_duration in number
290 );
291 --
292 -- ----------------------------------------------------------------------------
293 -- |---------------------------< check_language >-----------------------------|
294 -- ----------------------------------------------------------------------------
295 --
296 -- PUBLIC
297 -- Description:
298 -- The language must be in the domain 'Languages'.
299 --
300 Procedure check_language
301 (
302 p_language_id in number
303 );
304 --
305 -- ----------------------------------------------------------------------------
306 -- |-------------------< check_controlling_person >---------------------------|
307 -- ----------------------------------------------------------------------------
308 --
309 -- PUBLIC
310 -- Description:
311 -- The controlling person should exist as a valid person on the Validity
312 -- Start Date of the Activity Version.
313 --
314 Procedure check_controlling_person
315 (
316 p_person_id in number
317 ,p_date in date
318 );
319 --
320 -- ----------------------------------------------------------------------------
321 -- |-------------------< check_multiple_con_version >-----------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- PUBLIC
325 -- Description:
326 -- If the Activity Definitions is specified with the
327 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' then Versions of the Activity may not
328 -- have overlapping validity dates.
329 --
330 Procedure check_multiple_con_version
331 (
332 p_activity_id in number,
333 p_activity_version_id in number,
334 p_start_date in date,
335 p_end_date in date
336 );
337 --
338 -- ----------------------------------------------------------------------------
339 -- |------------------< check_version_after_supersede >-----------------------|
340 -- ----------------------------------------------------------------------------
341 --
342 -- PUBLIC
343 -- Description:
344 -- If the Activity Definitions is specified with the
345 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and the latest Activity Version has
346 -- been superseded by a Version of a different Activity, then new Version of
347 -- the Activity are not allowed (because there would be confusion over which
348 -- is the valid version of the activity, the new one or the superseding one).
349 --
350 Procedure check_version_after_supersede
351 (
352 p_activity_id in number
353 );
354 --
355
356
357 -- ----------------------------------------------------------------------------
358 -- |-----------------------------< chk_category >---------------------|
359 -- ----------------------------------------------------------------------------
360 --
361 -- PUBLIC
362 -- Description:
363 -- Check whether course is getting created under root category
364 --
365 --
366 --
367
368 PROCEDURE chk_category
369 (
370 p_activity_id IN number
371 );
372 --
373
374
375
376 -- ----------------------------------------------------------------------------
377 -- |---------------------------< insert_validate >----------------------------|
378 -- ----------------------------------------------------------------------------
379 -- {Start Of Comments}
380 --
381 -- Description:
382 -- This procedure controls the execution of all insert business rules
383 -- validation.
384 --
385 -- Pre Conditions:
386 -- This private procedure is called from ins procedure.
387 --
388 -- In Arguments:
389 -- A Pl/Sql record structre.
390 --
391 -- Post Success:
392 -- Processing continues.
393 --
394 -- Post Failure:
395 -- If a business rules fails the error will not be handled by this procedure
396 -- unless explicity coded.
397 --
398 -- Developer Implementation Notes:
402 --
399 -- For insert, your business rules should be coded within this procedure and
400 -- should ideally (unless really necessary) just be straight procedure or
401 -- function calls. Try and avoid using conditional branching logic.
403 -- Access Status:
404 -- Internal Development Use Only.
405 --
406 -- {End Of Comments}
407 -- ----------------------------------------------------------------------------
408 Procedure insert_validate(p_rec in ota_tav_shd.g_rec_type);
409 --
410 -- ----------------------------------------------------------------------------
411 -- |---------------------------< update_validate >----------------------------|
412 -- ----------------------------------------------------------------------------
413 -- {Start Of Comments}
414 --
415 -- Description:
416 -- This procedure controls the execution of all update business rules
417 -- validation.
418 --
419 -- Pre Conditions:
420 -- This private procedure is called from upd procedure.
421 --
422 -- In Arguments:
423 -- A Pl/Sql record structre.
424 --
425 -- Post Success:
426 -- Processing continues.
427 --
428 -- Post Failure:
429 -- If a business rules fails the error will not be handled by this procedure
430 -- unless explicity coded.
431 --
432 -- Developer Implementation Notes:
433 -- For update, your business rules should be coded within this procedure and
434 -- should ideally (unless really necessary) just be straight procedure or
435 -- function calls. Try and avoid using conditional branching logic.
436 --
437 -- Access Status:
438 -- Internal Development Use Only.
439 --
440 -- {End Of Comments}
441 -- ----------------------------------------------------------------------------
442 Procedure update_validate(p_rec in ota_tav_shd.g_rec_type);
443 --
444 -- ----------------------------------------------------------------------------
445 -- |---------------------------< delete_validate >----------------------------|
446 -- ----------------------------------------------------------------------------
447 -- {Start Of Comments}
448 --
449 -- Description:
450 -- This procedure controls the execution of all delete business rules
451 -- validation.
452 --
453 -- Pre Conditions:
454 -- This private procedure is called from del procedure.
455 --
456 -- In Arguments:
457 -- A Pl/Sql record structre.
458 --
459 -- Post Success:
460 -- Processing continues.
461 --
462 -- Post Failure:
463 -- If a business rules fails the error will not be handled by this procedure
464 -- unless explicity coded.
465 --
466 -- Developer Implementation Notes:
467 -- For delete, your business rules should be coded within this procedure and
468 -- should ideally (unless really necessary) just be straight procedure or
469 -- function calls. Try and avoid using conditional branching logic.
470 --
471 -- Access Status:
472 -- Internal Development Use Only.
473 --
474 -- {End Of Comments}
475 -- ----------------------------------------------------------------------------
476 Procedure delete_validate(p_rec in ota_tav_shd.g_rec_type);
477 --
478 --
479 -- ----------------------------------------------------------------------------
480 -- |-----------------------< return_legislation_code >-------------------------|
481 -- ----------------------------------------------------------------------------
482 --
483 -- {Start Of Comments}
484 --
485 -- Description:
486 -- This function will be used by the user hooks. Currently this will be used
487 -- hr_competence_element_api business processes and in future will be made use
488 -- of by the user hooks of activity_versions business process.
489 --
490 -- Pre Conditions:
491 -- This function will be called by the user hook packages.
492 --
493 -- In Arguments:
494 -- Activity_version_id
495 --
496 -- Post Success:
497 -- Processing continues.
498 --
499 -- Post Failure:
500 -- Errors out
501 --
502 -- Developer Implementation Notes:
503 --
504 -- Access Status:
505 -- Internal Development Use Only.
506 --
507 -- {End Of Comments}
508 --------------------------------------------------------------------------------
509 --
510 Function return_legislation_code
511 ( p_activity_version_id in number
512 ) return varchar2;
513 --
514 Procedure check_if_tsp_exists
515 (
516 p_activity_version_id in number
517 );
518 --
519 Procedure check_if_lpm_exists
520 (
521 p_activity_version_id in number
522 );
523 --
524 Procedure check_if_comp_exists
525 (
526 p_activity_version_id in number
527 );
528
529 -- ----------------------------------------------------------------------------
530 -- |-------------------------< check_if_noth_exists >--------------------------|
531 -- ----------------------------------------------------------------------------
532 --
533 -- PUBLIC
534 -- Description:
535 -- Delete Validation.
536 -- This activity version may not be deleted if child rows in
537 -- ota_notrng_histories exists where this activity version.
538 --
539 Procedure check_if_noth_exists
540 (
541 p_activity_version_id in number
542 );
543
544 -- ----------------------------------------------------------------------------
548 -- PUBLIC
545 -- |-------------------------< check_if_crt_exists >--------------------------|
546 -- ----------------------------------------------------------------------------
547 --
549 -- Description:
550 -- Delete Validation.
551 -- This activity version may not be deleted if child rows in
552 -- ota_certification_members exists where this activity version.
553 --
554 Procedure check_if_crt_exists
555 (
556 p_activity_version_id in number
557 );
558
559 end ota_tav_bus;