1 Package ota_tav_api_business_rules AUTHID CURRENT_USER as
2 /* $Header: ottav02t.pkh 120.2 2005/08/11 13:56:56 dhmulia noship $ */
3 --
4 --
5 --
6 -- Global variables
7 --
8 g_version_start_date date :=null;
9 g_version_end_date date :=null;
10 --
11 --
12 --
13 ------------------------------------------------------------------------------
14 -- |--------------------< set_globals >--------------------------------------|
15 ------------------------------------------------------------------------------
16 --
17 procedure set_globals(start_date in date
18 , end_date in date);
19 --
20 -- ----------------------------------------------------------------------------
21 -- |---------------------< check_currency >----------------------------------|
22 -- ----------------------------------------------------------------------------
23 --
24 procedure check_currency(p_currency_code in varchar2);
25 --
26 -- ----------------------------------------------------------------------------
27 -- |---------------------< check_vendor >----------------------------------|
28 -- ----------------------------------------------------------------------------
29 --
30 procedure check_vendor (p_vendor_id in number);
31 --
32 -- ----------------------------------------------------------------------------
33 -- |---------------------< check_cost_vals >----------------------------------|
34 -- ----------------------------------------------------------------------------
35 --
36 procedure check_cost_vals
37 (p_budget_currency_code in varchar2
38 ,p_budget_cost in number
39 ,p_actual_cost in number);
40 --
41 -- ----------------------------------------------------------------------------
42 -- |---------------------< check_professional_credit_vals >-------------------|
43 -- ----------------------------------------------------------------------------
44 --
45 procedure check_professional_credit_vals
46 (p_professional_credit_type in varchar2
47 ,p_professional_credits in number);
48 --
49 -- ----------------------------------------------------------------------------
50 -- |---------------------< check_professional_credit_type >-------------------|
51 -- ----------------------------------------------------------------------------
52 --
53 procedure check_professional_credit_type
54 (p_professional_credit_type in varchar2);
55 --
56 -- ----------------------------------------------------------------------------
57 -- |-------------------------< check_min_max_values >-------------------------|
58 -- ----------------------------------------------------------------------------
59 --
60 -- PUBLIC
61 -- Description:
62 -- The minimum attendees must be less then or equal to the maximum attendees.
63 --
64 Procedure check_min_max_values
65 (
66 p_min in number
67 ,p_max in number
68 );
69 --
70 -- ----------------------------------------------------------------------------
71 -- |--------------------------< check_unique_name >---------------------------|
72 -- ----------------------------------------------------------------------------
73 --
74 -- PUBLIC
75 -- Description:
76 -- Validates the unique key.
77 --
78 Procedure check_unique_name
79 (
80 p_business_group_id in number
81 ,p_activity_id in number
82 ,p_version_name in varchar2
83 ,p_activity_version_id in number
84 );
85 --
86 -- ----------------------------------------------------------------------------
87 -- |---------------------< check_superseding_version >------------------------|
88 -- ----------------------------------------------------------------------------
89 --
90 -- PUBLIC
91 -- Description:
92 -- A activity version may not be superseded ba a activity whose end_date
93 -- is greater than it's own. The supersedinthg activity version must have
94 -- an end date greater than the end date of the activity it supersedes.
95 --
96 Procedure check_superseding_version
97 (
98 p_sup_act_vers_id in number
99 ,p_end_date in date
100 );
101 --
102 -- ---------------------------------------------------------------------------
103 -- |-----------------------< find_overlapping_versions >---------------------|
104 -- ---------------------------------------------------------------------------
105 --
106 -- PUBLIC
107 -- Description:
108 -- Checks to see if an Activity has overlapping versions. If a version has a
109 -- start date between another version's start date and end date then
110 -- overlapping versions exist.
111 --
112 Procedure find_overlapping_versions
113 (
114 p_activity_id in number
115 );
116 --
117 -- ----------------------------------------------------------------------------
118 -- |--------------------------< check_user_status >---------------------------|
119 -- ----------------------------------------------------------------------------
120 --
121 -- PUBLIC
122 -- Description:
123 -- The user status must be in the domain 'Activity User Status'.
124 --
125 Procedure check_user_status
126 (
127 p_user_status in varchar2
128 );
129 --
130 -- ----------------------------------------------------------------------------
131 -- |-------------------------< check_success_criteria >-----------------------|
132 -- ----------------------------------------------------------------------------
133 --
134 -- PUBLIC
135 -- Description:
136 -- The success criteria must be in the domain 'Activity Success Criteria'.
137 --
138 Procedure check_success_criteria
139 (
140 p_succ_criteria in varchar2
141 );
142 --
143 -- ----------------------------------------------------------------------------
144 -- |----------------------< get_activity_version_id >-------------------------|
145 -- ----------------------------------------------------------------------------
146 --
147 -- PUBLIC
148 -- Description:
149 -- Return the surrogate key from a passed parameter
150 --
151 Function get_activity_version_id
152 (
153 p_activity_id in number
154 ,p_version_name in varchar2
155 )
156 Return number;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |----------------------< get_activity_version_name >-----------------------|
160 -- ----------------------------------------------------------------------------
161 --
162 -- PUBLIC
163 -- Description:
164 -- Ruturn the activity version name.
165 --
166 Function get_activity_version_name
167 (
168 p_activity_version_id in number
169 )
170 Return varchar2;
171 --
172 pragma restrict_references ( get_activity_version_name, WNDS, WNPS);
173 --
174 -- ----------------------------------------------------------------------------
175 -- |--------------------------< check_start_end_dates >-----------------------|
176 -- ----------------------------------------------------------------------------
177 --
178 -- PUBLIC
179 -- Description:
180 -- Validates the startdate and enddate.
181 -- Startdate must be less than, or equal to, enddate.
182 --
183 Procedure check_start_end_dates
184 (
185 p_start_date in date
186 ,p_end_date in date
187 );
188 --
189 -- ----------------------------------------------------------------------------
190 -- |-------------------------< check_dates_update_ple >-----------------------|
191 -- ----------------------------------------------------------------------------
192 --
193 -- PUBLIC
194 -- Description:
195 -- Validates the startdate and enddate.
196 -- Update of start and end dates must not invalidate valid pricing details
197 -- for this activity version.
198 --
199 Procedure check_dates_update_ple
200 (
201 p_activity_version_id in number
202 ,p_start_date in date
203 ,p_end_date in date
204 );
205 --
206 -- ----------------------------------------------------------------------------
207 -- |-------------------------< check_dates_update_rud >-----------------------|
208 -- ----------------------------------------------------------------------------
209 --
210 -- PUBLIC
211 -- Description:
212 -- Validates the startdate and enddate.
213 -- Update of start and end dates must not invalidate valid resoruce usages
214 -- for this activity version.
215 --
216 Procedure check_dates_update_rud
217 (
218 p_activity_version_id in number
219 ,p_start_date in date
220 ,p_end_date in date
221 ,p_old_start_date in date
222 ,p_old_end_date in date
223 );
224 --
225 -- ----------------------------------------------------------------------------
226 -- |-------------------------< check_dates_update_tbd >-----------------------|
227 -- ----------------------------------------------------------------------------
228 --
229 -- PUBLIC
230 -- Description:
231 -- Validates the startdate and enddate.
232 -- Update of start and end dates must not invalidate booking deals
233 -- questions for this activity version.
234 --
235 Procedure check_dates_update_tbd
236 (
237 p_activity_version_id in number
238 ,p_start_date in date
239 ,p_end_date in date
240 );
241 --
242 -- ----------------------------------------------------------------------------
243 -- |-------------------------< check_dates_update_evt >-----------------------|
244 -- ----------------------------------------------------------------------------
245 --
246 -- PUBLIC
247 -- Description:
248 -- Validates the startdate and enddate.
249 -- Update of start and end dates must not invalidate events
250 -- for this activity version.
251 -- This requires a check to ensure that the activity version dates do not
252 -- invalidate the Event Booking DAtes or the Event Course Dates if either
253 -- have been entered.
254 --
255 Procedure check_dates_update_evt
256 (
257 p_activity_version_id in number
258 ,p_start_date in date
259 ,p_end_date in date
260 );
261 --
262 -- ----------------------------------------------------------------------------
263 -- |--------------------------< Check_category_dates >------------------------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- PUBLIC
267 -- Description:
268 -- Validates the startdate and enddate with respect to category dates.
269 --
270 Procedure Check_category_dates
271 (p_activity_version_id in number
272 ,p_start_date in date
273 ,p_end_date in date
274 );
275 --
276 -- ----------------------------------------------------------------------------
277 -- |-------------------------< check_if_evt_exists >--------------------------|
278 -- ----------------------------------------------------------------------------
279 --
280 -- PUBLIC
281 -- Description:
282 -- Delete Validation.
283 -- This activity version may not be deleted if child rows in
284 -- ota_events exists.
285 --
286 Procedure check_if_evt_exists
287 (
288 p_activity_version_id in number
289 );
290 --
291 -- ----------------------------------------------------------------------------
292 -- |-------------------------< check_if_tpm_exists >--------------------------|
293 -- ----------------------------------------------------------------------------
294 --
295 -- PUBLIC
296 -- Description:
297 -- Delete Validation.
298 -- This activity version may not be deleted if child rows in
299 -- ota_training_plan_members exists.
300 --
301 Procedure check_if_tpm_exists
302 (
303 p_activity_version_id in number
304 );
305 -- ----------------------------------------------------------------------------
306 -- |-------------------------< check_if_tbd_exists >--------------------------|
307 -- ----------------------------------------------------------------------------
308 --
309 -- PUBLIC
310 -- Description:
311 -- Delete Validation.
312 -- This activity version may not be deleted if child rows in
313 -- ota_booking_deals exists.
314 --
315 Procedure check_if_tbd_exists
316 (
317 p_activity_version_id in number
318 );
319 --
320 -- ----------------------------------------------------------------------------
321 -- |-------------------------< check_if_ple_exists >--------------------------|
322 -- ----------------------------------------------------------------------------
323 --
324 -- PUBLIC
325 -- Description:
326 -- Delete Validation.
327 -- This activity version may not be deleted if child rows in
328 -- ota_price_lists_entries exists.
329 --
330 Procedure check_if_ple_exists
331 (
332 p_activity_version_id in number
333 );
334 --
335 -- ----------------------------------------------------------------------------
336 -- |-------------------------< check_if_tav_exists >--------------------------|
337 -- ----------------------------------------------------------------------------
338 --
339 -- PUBLIC
340 -- Description:
341 -- Delete Validation.
342 -- This activity version may not be deleted if child rows in
343 -- ota_activity_versions exists where this activity version has superseded
344 -- another earlier activity version.
345 --
346 Procedure check_if_tav_exists
347 (
348 p_activity_version_id in number
349 );
350 --
351 -- ----------------------------------------------------------------------------
352 -- |------------------------< check_duration_units >--------------------------|
353 -- ----------------------------------------------------------------------------
354 --
355 -- PUBLIC
356 -- Description:
360 (
357 -- The duration units must be in the domain 'Units'.
358 --
359 Procedure check_duration_units
361 p_duration_units in varchar2
362 );
363 --
364 -- ----------------------------------------------------------------------------
365 -- |--------------------------< check_duration >------------------------------|
366 -- ----------------------------------------------------------------------------
367 --
368 -- PUBLIC
369 -- Description:
370 -- The duration must be a positive integer greater than zero.
371 --
372 Procedure check_duration
373 (
374 p_duration in number
375 );
376 --
377 -- ----------------------------------------------------------------------------
378 -- |---------------------------< check_language >-----------------------------|
379 -- ----------------------------------------------------------------------------
380 --
381 -- PUBLIC
382 -- Description:
383 -- The language must be in the domain 'Languages'.
384 --
385 Procedure check_language
386 (
387 p_language_id in number
388 );
389 --
390 -- ----------------------------------------------------------------------------
391 -- |-------------------< check_controlling_person >---------------------------|
392 -- ----------------------------------------------------------------------------
393 --
394 -- PUBLIC
395 -- Description:
396 -- The controlling person should exist as a valid person on the Validity
397 -- Start Date of the Activity Version.
398 --
399 Procedure check_controlling_person
400 (
401 p_person_id in number
402 ,p_date in date
403 );
404 --
405 -- ----------------------------------------------------------------------------
406 -- |--------------------< set_superseding_start_date >-----------------------|
407 -- ----------------------------------------------------------------------------
408 --
409 -- PUBLIC
410 -- Description:
411 -- If the previous version has an end date, the start date defaults to the end
412 -- date of the previous version plus one
413 --
414 Function set_superseding_start_date
415 (
416 p_activity_id in number
417 ) Return date;
418 --
419 --
420 -- ----------------------------------------------------------------------------
421 -- |--------------------< check_multiple_con_version >-----------------------|
422 -- ----------------------------------------------------------------------------
423 --
424 -- PUBLIC
425 -- Description:
426 -- If the Activity Definitions is specified with the
427 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' then Versions of the Activity may not
428 -- have overlapping validity dates.
429 --
430 Procedure check_multiple_con_version
431 (
432 p_activity_id in number,
433 p_activity_version_id in number,
434 p_start_date in date,
435 p_end_date in date
436 );
437 --
438 --
439 -- ----------------------------------------------------------------------------
440 -- |--------------------< set_superseding_version >-----------------------|
441 -- ----------------------------------------------------------------------------
442 --
443 -- PUBLIC
444 -- Description:
445 -- If the Activity Definitions is specified with the
446 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and a new version is created for
447 -- that activity, the superseded by field on the previous version must be
448 -- populated with the name of the new version
449 --
450 Procedure set_superseding_version
451 (
452 p_activity_id in number
453 ,p_activity_version_id in number
454 ,p_start_date in date
455 );
456 --
457 -- ----------------------------------------------------------------------------
458 -- |------------------< check_version_after_supersede >-----------------------|
459 -- ----------------------------------------------------------------------------
460 --
461 -- PUBLIC
462 -- Description:
463 -- If the Activity Definitions is specified with the
464 -- MULTIPLE_CON_VERSIONS_FLAG set to 'N' and the latest Activity Version has
465 -- been superseded by a Version of a different Activity, then new Version of
466 -- the Activity are not allowed (because there would be confusion over which
467 -- is the valid version of the activity, the new one or the superseding one).
468 --
469 Procedure check_version_after_supersede
470 (
471 p_activity_id in number
472 );
473 --
474
475 -- ----------------------------------------------------------------------------
476 -- |-----------------------------< check_OE_Lines_exist>----------------------|
477 -- ----------------------------------------------------------------------------
478 --
479 -- PUBLIC
480 -- Description:
481 -- If The inventory id that link to this Activity has been ordered through
482 -- Order Line than user cannot change the inventory id.
483 --
484 --
485 Procedure check_OE_lines_exist
486 (
487 p_activity_version_id in number,
488 p_inventory_item_id in number,
489 p_organization_id in number
490
491 );
492
493 --
494 -- ----------------------------------------------------------------------------
495 -- |-----------------------------< check_Inventory_item_id>-------------------|
499 -- Description:
496 -- ----------------------------------------------------------------------------
497 --
498 -- PUBLIC
500 -- If The inventory id is not a valid inventory id in MTL_SYSTEM_ITEMS_B table
501 -- then user has to provide the correct one.
502 --
503 --
504 --
505 Procedure check_Inventory_item_id
506 (
507 p_activity_version_id in number,
508 p_inventory_item_id in number,
509 p_organization_id in number
510 );
511
512 --
513 -- ----------------------------------------------------------------------------
514 -- |-----------------------------< check_unique_rco_id>------------------------|
515 -- ----------------------------------------------------------------------------
516 --
517 -- PUBLIC
518 -- Description:
519 -- Check uniqueness of rco_id
520 --
521 --
522 --
523 --
524 Procedure check_unique_rco_id
525 (
526 p_activity_version_id in number,
527 p_rco_id in number
528 );
529
530 Procedure check_if_tsp_exists
531 (
532 p_activity_version_id in number
533 );
534
535 -----------------------------------------------------------------------------
536 -- |-----------------------------< check_if_lpm_exists>-----------------------|
537 -- ----------------------------------------------------------------------------
538 --
539 -- PUBLIC
540 -- Delete Validation.
541 -- This activity version may not be deleted if child rows in
542 -- ota_learning_path_members exist.
543 --
544 Procedure check_if_lpm_exists
545 (
546 p_activity_version_id in number
547 );
548 Procedure check_if_comp_exists
549 (
550 p_activity_version_id in number
551 );
552 Procedure check_if_off_exists
553 (
554 p_activity_version_id in number
555 );
556 --
557 -- ----------------------------------------------------------------------------
558 -- |-----------------------------< check_course_lp_dates>------------------------|
559 -- ----------------------------------------------------------------------------
560 --
561 -- PUBLIC
562 -- Description:
563 -- Check dates of Course and Learning Path
564 --
565 --
566 --
567 --
568 Procedure check_course_lp_dates
569 (
570 p_activity_version_id IN NUMBER,
571 p_start_date IN DATE,
572 p_end_date IN DATE);
573
574
575 -- ----------------------------------------------------------------------------
576 -- |-------------------------< check_if_noth_exists >--------------------------|
577 -- ----------------------------------------------------------------------------
578 --
579 -- PUBLIC
580 -- Description:
581 -- Delete Validation.
582 -- This activity version may not be deleted if child rows in
583 -- ota_notrng_histories exists where this activity version.
584 --
585 Procedure check_if_noth_exists
586 (
587 p_activity_version_id in number
588 );
589
590 -- ----------------------------------------------------------------------------
591 -- |-------------------------< check_if_crt_exists >--------------------------|
592 -- ----------------------------------------------------------------------------
593 --
594 -- PUBLIC
595 -- Description:
596 -- Delete Validation.
597 -- This activity version may not be deleted if child rows in
598 -- ota_certification_members exists where this activity version.
599 --
600 Procedure check_if_crt_exists
601 (
602 p_activity_version_id in number
603 );
604
605 -- ----------------------------------------------------------------------------
606 -- |-----------------------------< check_course_crt_dates>---------------------|
607 -- ----------------------------------------------------------------------------
608 --
609 -- PUBLIC
610 -- Description:
611 -- Check dates of Course and certification
612 --
613 --
614 --
615 --
616 Procedure check_course_crt_dates
617 (
618 p_activity_version_id IN NUMBER,
619 p_start_date IN DATE,
620 p_end_date IN DATE);
621
622 end ota_tav_api_business_rules;
623
[Home] [Help]