1 Package ota_tdb_bus as
2 /* $Header: ottdb01t.pkh 120.5 2007/11/21 13:43:01 shwnayak noship $ */
3 --
4 --
5 --
6 --
7 -- ----------------------------------------------------------------------------
8 -- |-----------------------< chk_non_updateable_args >------------------------|
9 -- ----------------------------------------------------------------------------
10 -- {Start Of Comments}
11 --
12 -- Description:
13 -- This procedure is used to ensure that non updateable attributes have
14 -- not been updated. If an attribute has been updated an error is generated.
15 --
16 -- Pre Conditions:
17 -- g_old_rec has been populated with details of the values currently in
18 -- the database.
19 --
20 -- In Arguments:
21 -- p_rec has been populated with the updated values the user would like the
22 -- record set to.
23 --
24 -- Post Success:
25 -- Processing continues if all the non updateable attributes have not
26 -- changed.
27 --
28 -- Post Failure:
29 -- An application error is raised if any of the non updatable attributes
30 -- have been altered.
31 --
32 -- {End Of Comments}
33 -- ----------------------------------------------------------------------------
34 Procedure chk_non_updateable_args
35 (
36 p_rec in ota_tdb_shd.g_rec_type);
37
38
39 --
40 --added for eBS by dhmulia
41 -- ---------------------------------------------------------------------------
42 -- |----------------------< set_security_group_id >--------------------------|
43 -- ---------------------------------------------------------------------------
44 -- {Start Of Comments}
45 --
46 -- Description:
47 -- Sets the security_group_id in CLIENT_INFO for the appropriate business
48 -- group context.
49 --
50 -- Prerequisites:
51 -- The primary key identified by p_booking_id
52 -- already exists.
53 --
54 -- In Arguments:
55 -- p_booking_id
56 --
57 --
58 -- Post Success:
59 -- The security_group_id will be set in CLIENT_INFO.
60 --
61 -- Post Failure:
62 -- An error is raised if the value does not exist.
63 --
64 -- Access Status:
65 -- Internal Development Use Only.
66 --
67 -- {End Of Comments}
68 -- ---------------------------------------------------------------------------
69 procedure set_security_group_id
70 (p_booking_id in number
71 ,p_associated_column1 in varchar2 default null
72 );
73 --added for eBS by dhmulia
74 --
75 -- Added For Bug 4649610
76 -- ---------------------------------------------------------------------------
77 -- |---------------------< return_legislation_code >-------------------------|
78 -- ---------------------------------------------------------------------------
79 -- {Start Of Comments}
80 --
81 -- Description:
82 -- Return the legislation code for a specific primary key value
83 --
84 -- Prerequisites:
85 -- The primary key identified by p_booking_id
86 -- already exists.
87 --
88 -- In Arguments:
89 -- p_booking_id
90 --
91 --
92 -- Post Success:
93 -- The business group's legislation code will be returned.
94 --
95 -- Post Failure:
96 -- An error is raised if the value does not exist.
97 --
98 -- Access Status:
99 -- Internal Development Use Only.
100 --
101 -- {End Of Comments}
102 -- ---------------------------------------------------------------------------
103 FUNCTION return_legislation_code
104 (p_booking_id in number
105 ) RETURN varchar2;
106 --
107 -- Added For Bug 4649610
108
109 -- ---------------------------------------------------------------------------
110 -- ---------------------------------------------------------------------------
111 -- ---------------------------------------------------------------------------
112 -- PUBLIC
113 -- Function to return concatenated full_name
114 --
115 function get_full_name (p_last_name in varchar2
116 ,p_title in varchar2
117 ,p_first_name in varchar2) return varchar2;
118 pragma restrict_references (get_full_name, WNPS,WNDS);
119 --
120 -- ----------------------------------------------------------------------------
121 -- |-----------------------------< get_full_name >----------------------------|
122 -- ----------------------------------------------------------------------------
123 -- PUBLIC
124 -- Function to return legislative concatenated full_name
125 --
126 function get_full_name
127 (p_last_name in varchar2
128 ,p_title in varchar2
129 ,p_first_name in varchar2
130 ,p_legislation_code in varchar2
131 ,p_last_name_alt in varchar2 DEFAULT null
132 ,p_first_name_alt in varchar2 DEFAULT null
133 ) return varchar2;
134
135 pragma restrict_references (get_full_name, WNDS, WNPS);
136 --
137 -- ----------------------------------------------------------------------------
138 -- |-----------------------------< get_full_name >----------------------------|
139 -- |----------------- version with legislative check ------------------------|
140 -- ----------------------------------------------------------------------------
141 -- PUBLIC
142 -- Function to return legislative concatenated full_name
143 --
144 function get_full_name
145 (p_last_name in varchar2
146 ,p_title in varchar2
147 ,p_first_name in varchar2
148 ,p_business_group_id in number
149 ,p_last_name_alt in varchar2 DEFAULT null
150 ,p_first_name_alt in varchar2 DEFAULT null
151 ) return varchar2;
152
153 pragma restrict_references (get_full_name, WNDS, WNPS);
154 --
155 -- ----------------------------------------------------------------------------
156 -- ----------------------------------------------------------------------------
157 -- ----------------------------------------------------------------------------
158 -- PUBLIC
159 -- Function to check if an assignment is ok
160 --
161 function assignment_ok (p_person_type in varchar2,
162 p_assignment_id in number,
163 p_event_id in number,
164 p_date_booking_placed in date) return rowid;
165 pragma restrict_references (assignment_ok,WNPS,WNDS);
166 -- ----------------------------------------------------------------------------
167 -- |-----------------------------< check_places >-----------------------------|
168 -- ----------------------------------------------------------------------------
169 --
170 -- PUBLIC
171 -- Description: Check Places
172 --
173 -- Checks that if a delegate is specified then the number of
174 -- places should be one
175 --
176 Function booking_status_type (p_booking_status_type_id in number)
177 return varchar2;
178 --
179 --
180 -- ----------------------------------------------------------------------------
181 -- |--------------------------< booking_status_type >-------------------------|
182 -- ----------------------------------------------------------------------------
183 --
184 -- PUBLIC
185 -- Description: Booking Status Type
186 --
187 -- Returns the type of a booking status id
188 --
189 Procedure check_places (p_delegate_person_id in number,
190 p_number_of_places in number);
191 --
192 --
193 -- --------------------------------------------------------------------
194 -- |------------------------< get_event_type>-------------------------|
195 -- --------------------------------------------------------------------
196 --
197 -- PUBLIC
198 -- Description: get_event_type
199 --
200 -- Returns the event_type for a given event
201 --
202 Function get_event_type (p_event_id in number) return varchar2;
203 pragma restrict_references (get_event_type,WNPS,WNDS);
204 -- --------------------------------------------------------------------
205 -- |------------------------< check_person>---------------------------|
206 -- --------------------------------------------------------------------
207 --
208 -- PUBLIC
209 -- Description: check_person
210 --
211 -- Checks that a given person is active on a given date
212 --
213 function check_person(p_person_id in number,
214 p_date in date,
215 p_person_type in varchar2,
216 p_person_address_type in varchar2) return boolean;
217 --
218 --
219 -- ----------------------------------------------------------------------------
220 -- |-------------------------< check_unique_booking >-------------------------|
221 -- ----------------------------------------------------------------------------
222 --
223 -- PUBLIC
224 -- Description: Check Unique Booking
225 --
226 -- Checks that the booking being made has not already been made
227 --
228 Procedure check_unique_booking (p_customer_id in number,
229 p_organization_id in number,
230 p_event_id in number,
231 p_delegate_person_id in number,
232 p_delegate_contact_id in number,
233 p_booking_id in number);
234 --
235 -- ----------------------------------------------------------------------------
236 -- |----------------------------< check_failure >-----------------------------|
237 -- ----------------------------------------------------------------------------
238 --
239 -- Description: Check Failure
240 --
241 -- Checks that the reason for failure is not specified for a
242 -- successful delegate
243 --
244 Procedure check_failure (p_failure_reason in varchar2,
245 p_successful_attendance_flag in varchar2);
246 --
247 --
248 -- ----------------------------------------------------------------------------
249 -- |---------------------------< check_attendance >---------------------------|
250 -- ----------------------------------------------------------------------------
251 --
252 -- Description: Check Attendance
253 --
254 -- Checks that successful attendance is only valid for confirmed
255 -- bookings
256 --
257 Procedure check_attendance (p_successful_attendance_flag in varchar2,
258 p_booking_status_type_id in number);
259 --
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------< check_internal_booking >---------------------|
263 -- ----------------------------------------------------------------------------
264 --
265 -- Description: Check Internal Booking
266 --
267 -- Checks that when the internal booking flag is checked that it
268 -- doesn't exceed the event max internal limit.
269 --
270 Procedure check_internal_booking (p_event_id in number,
271 p_number_of_places in number,
272 p_booking_id in number);
273 --
274 --
275 --
276 -- ----------------------------------------------------------------------------
277 -- |----------------------< check_type_business_group >-----------------------|
278 -- ----------------------------------------------------------------------------
279 --
280 -- Description: Check Type Business Group
281 --
282 -- Checks that the business group of the booking is the same as
283 -- that of the booking status type being used
284 --
285 Procedure check_type_business_group (p_business_group_id in number,
286 p_booking_status_type_id in number);
287 --
288 -- ----------------------------------------------------------------------------
289 -- |--------------------------< event_place_needed >--------------------------|
290 -- ----------------------------------------------------------------------------
291 --
292 -- PUBLIC
293 -- Description: Event Place Needed
294 --
295 -- Checks whether a place on an event is needed, in other words
296 -- is it a placed or attended enrollment status
297 --
298 Function event_place_needed(p_booking_status_type_id in number) return number;
299 pragma restrict_references (event_place_needed,WNPS,WNDS);
300 --
301 -- ----------------------------------------------------------------------------
302 -- |----------------------< check_event_business_group >----------------------|
303 -- ----------------------------------------------------------------------------
304 --
305 -- PUBLIC
306 -- Description: Check Event Business Group
307 --
308 -- Checks that the business group of the booking is the same as
309 -- that of the event being booked
310 --
311 Procedure check_event_business_group
312 (p_business_group_id in number,
313 p_event_id in number,
314 p_event_record_use in varchar2 default 'NEW EVENT');
315 --
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------< check_resources >----------------------------|
319 -- ----------------------------------------------------------------------------
320 --
321 -- Description: Check resources
322 --
323 -- Checks if any resources exists for the booking
324 --
325 Procedure check_resources (p_booking_id in number);
326 --
327 -- ---------------------------------------------------------------------------
328 -- |-----------------------< check_training_plan_costs >----------------------|
329 -- ---------------------------------------------------------------------------
330 --
331 -- Description: Check Training Plan Cost records
332 --
333 -- Checks if any training plan cost records exist for the booking
334 --
335 Procedure check_training_plan_costs(p_booking_id in number);
336 --
337 -- ----------------------------------------------------------------------------
338 -- |---------------------------< check_finance_lines >----------------------------|
339 -- ----------------------------------------------------------------------------
340 --
341 -- Description: Check finance lines
342 --
343 -- Checks if any finance lines exists for the booking
344 --
345 Procedure check_finance_lines (p_booking_id in number);
346 --
347 -- ----------------------------------------------------------------------------
348 -- |----------------------------< booking_id_for >----------------------------|
349 -- ----------------------------------------------------------------------------
350 --
351 -- Description: Booking ID For
352 --
353 -- Returns the Booking Id for a given Organization-Event-Delegate
354 -- combination
355 --
356 Function booking_id_for (p_customer_id in number,
357 p_organization_id in number,
358 p_event_id in number,
359 p_person_id in number) Return number;
360 --
361 -- ----------------------------------------------------------------------------
362 -- |--------------------------< Finance Line Exists >-------------------------|
363 -- ----------------------------------------------------------------------------
364 --
365 -- Description: Checks whether a finance line exists for a particular booking_Id.
366 --
367 --
368 Function Finance_Line_Exists (p_booking_id in number
369 ,p_cancelled_flag in varchar2)
370 Return boolean;
371 --
372 --
373 -- ----------------------------------------------------------------------------
374 -- |--------------------------< internal_booking >----------------------------|
375 -- ----------------------------------------------------------------------------
376 --
377 -- Description: Internal Booking
378 --
379 -- Checks if the booking is internal then the person (Contact or
380 -- Delegate) is also internal
381 --
382 Function internal_booking (p_internal_booking_flag in varchar2,
383 p_person_id in number,
387 -- ----------------------------------------------------------------------------
384 p_date_booking_placed in date)
385 Return boolean;
386 --
388 -- |------------------------< check_delegate_eligible >-----------------------|
389 -- ----------------------------------------------------------------------------
390 --
391 -- Description: Check Delegate Eligible
392 --
393 -- If the event is not public, only delegates from organizations
394 -- which have an association with the event are eligible
395 --
396 Procedure check_delegate_eligible (p_event_id in number,
397 p_customer_id in number,
398 p_delegate_contact_id in number,
399 p_organization_id in number,
400 p_delegate_person_id in number,
401 p_delegate_assignment_id in number);
402 --
403 --
404 --
405 --
406 --
407 --
408 -- ----------------------------------------------------------------------------
409 -- |--------------------------< places_for_status >---------------------------|
410 -- ----------------------------------------------------------------------------
411 --
412 -- PUBLIC
413 -- Description: Places for status
414 --
415 -- Returns the number of places on an event at either a given
416 -- status type or a given status type ID
417 -- for either ALL delegates or only INTERNAL delegates not
418 -- counting the given booking
419 --
420 Function places_for_status (p_event_id in number,
421 p_all_or_internal in varchar2,
422 p_booking_status_type_id in number default null,
423 p_status_type in varchar2 default null,
424 p_usage_type in varchar2 default null,
425 p_booking_id in number default null)
426 Return number;
427 --
428 --
429 -- ----------------------------------------------------------------------------
430 -- |---------------------------< places_allowed >-----------------------------|
431 -- ----------------------------------------------------------------------------
432 --
433 -- PUBLIC
434 -- Description: Places allowed
435 --
436 -- Returns the number of places allowed on an event for either
437 -- ALL delegates or only INTERNAL delegates
438 --
439 Function places_allowed (p_event_id in number,
440 p_all_or_internal in varchar2) Return number;
441 --
442 --
443 -- ----------------------------------------------------------------------------
444 -- |--------------------------< check_max_allowance >-------------------------|
445 -- ----------------------------------------------------------------------------
446 --
447 -- PUBLIC
448 -- Description: Check Maximum Allowance
449 --
450 -- Checks if after the booking, the number for the event exceeds
451 -- or has reached the maximum allowed for the event
452 --
453 Procedure check_max_allowance
454 (p_event_id in number,
455 p_booking_status_type_id in number,
456 p_number_of_places in number,
457 p_internal_booking_flag in varchar2,
458 p_max_reached out nocopy boolean,
459 p_max_exceeded out nocopy boolean,
460 p_all_or_internal in varchar2 default 'ALL',
461 p_booking_id in number default NULL);
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------------< ota_letter_lines >---------------------------|
465 -- ----------------------------------------------------------------------------
466 --
467 procedure ota_letter_lines (p_booking_id in number,
468 p_booking_status_type_id in number,
469 p_event_id in number,
470 p_delegate_person_id in number default null);
471 --
472 -- ----------------------------------------------------------------------------
473 -- |-----------------------< Check_programme_member >-------------------------|
474 -- ----------------------------------------------------------------------------
475 --
476 -- Description: Check Programme Member
477 --
478 -- Checks that a booking made for a programme member has another
479 -- existing booking for the programme
480 --
481 Procedure check_programme_member
482 (p_event_id in number,
483 p_customer_id in number,
484 p_organization_id in number,
485 p_delegate_person_id in number,
486 p_delegate_contact_id in number,
487 p_event_record_use in varchar2 default 'NEW EVENT',
488 p_booking_id in number default null);
489 --
490 -- ----------------------------------------------------------------------------
491 -- |------------------------------< enrolling >-------------------------------|
492 -- ----------------------------------------------------------------------------
493 --
494 -- Description: Enrolling
495 --
496 -- Checks if the given event is enrolling
497 --
498 Function enrolling (p_event_id in number,
502 -- ----------------------------------------------------------------------------
499 p_event_record_use in varchar2 default 'NEW EVENT')
500 return BOOLEAN;
501 --
503 -- |--------------------------< enrolling_on_date >---------------------------|
504 -- ----------------------------------------------------------------------------
505 --
506 -- Description: Enrolling On Date
507 --
508 -- Checks if the given event is enrolling on the given date
509 --
510 Function enrolling_on_date
511 (p_event_id in number,
512 p_date in date,
513 p_event_record_use in varchar2 default 'NEW EVENT')
514 return BOOLEAN;
515 --
516 -- ----------------------------------------------------------------------------
517 -- |-----------------------------< closed_event >-----------------------------|
518 -- ----------------------------------------------------------------------------
519 --
520 -- Description: Closed Event
521 --
522 -- Checks if the given event is closed
523 --
524 Function closed_event (p_event_id in number,
525 p_event_record_use in varchar2 default 'NEW EVENT')
526 return BOOLEAN;
527 --
528 -- ----------------------------------------------------------------------------
529 -- |--------------------------< check_closed_event >--------------------------|
530 -- ----------------------------------------------------------------------------
531 --
532 -- Description: Check Closed Event
533 --
534 -- Checks that the event to which the booking is being made is
535 -- not closed
536 --
537 Procedure check_closed_event
538 (p_event_id in number,
539 p_date_booking_placed in date,
540 p_event_record_use in varchar2 default 'NEW EVENT');
541 --
542 -- ----------------------------------------------------------------------------
543 -- |---------------------< maintain_status_history >--------------------------|
544 -- ----------------------------------------------------------------------------
545 --
546 -- Description: Maintain Status History
547 --
548 -- Maintains a history of status changes for the booking when the
549 -- booking status type is updated
550 --
551 Procedure maintain_status_history (p_booking_status_type_id in number,
552 p_date_status_changed in date,
553 p_administrator in number,
554 p_status_change_comments in varchar2,
555 p_booking_id in number,
556 p_previous_status_change in date,
557 p_previous_status_type_id in number,
558 p_created_by in number,
559 p_date_booking_placed in date);
560 --
561 --
562 -- ----------------------------------------------------------------------------
563 -- |-------------------------------< get_event>-------------------------------|
564 -- ----------------------------------------------------------------------------
565 --
566 -- Description: Get Event
567 --
568 -- Retrieves the details associated with the event required for
569 -- subsequent checks in the package and stores the values in
570 -- the global record g_event_rec
571 --
572 Procedure get_event (p_event_id in number,
573 p_record_use in varchar2 default 'NEW EVENT');
574 --
575 --
576 -- ----------------------------------------------------------------------------
577 -- |--------------------------< check_constraints >---------------------------|
578 -- ----------------------------------------------------------------------------
579 Procedure check_constraints
580 (
581 p_internal_booking_flag in varchar2,
582 p_successful_attendance_flag in varchar2
583 );
584 --
585 --
586 -- ----------------------------------------------------------------------------
587 -- |----------------< check_program_member_enrollments >----------------------|
588 -- ----------------------------------------------------------------------------
589 --
590 -- Description: Determines whether a person is enrolled onto program
591 -- member events before their program enrollment can be
592 -- deleted.
593 --
594 Procedure check_pmm_enrollments;
595 --
596 --
597 -- ----------------------------------------------------------------------------
598 -- |---------------------------< chk_line_id >------------------------------|
599 -- ----------------------------------------------------------------------------
600 Procedure chk_line_id
601 (
602 p_booking_id in number
603 ,p_line_id in number
604 ,p_org_id in number
605 );
606 --
607 -- ----------------------------------------------------------------------------
608 -- |---------------------------< chk_order_line_exist >---------------------|
609 -- ----------------------------------------------------------------------------
610 Procedure chk_Order_line_exist
611 (p_line_id in number
612 ,p_org_id in number);
613 --
614 --
615 -- ----------------------------------------------------------------------------
616 -- |---------------------------< chk_status_changed >----------------------|
617 -- ----------------------------------------------------------------------------
618 Procedure chk_status_changed
619 (p_line_id in number
620 ,p_status_type_id in number
621 ,p_daemon_type in varchar2
622 ,p_event_id in number
623 ,p_booking_id in number
624 ,p_org_id in number
625 );
626
627 --
628 --
629 -- ----------------------------------------------------------------------------
630 -- |---------------------------< check_enrollment_dates >----------------------|
631 -- ----------------------------------------------------------------------------
632 Function check_enrollment_dates
633 (p_event_id in number,
634 p_date in date,
635 p_throw_error IN VARCHAR2 DEFAULT 'Y')
636 return VARCHAR2;
637 -- ----------------------------------------------------------------------------
638 -- |---------------------------< insert_validate >----------------------------|
639 -- ----------------------------------------------------------------------------
640 -- {Start Of Comments}
641 --
642 -- Description:
643 -- This procedure controls the execution of all insert business rules
644 -- validation.
645 --
646 -- Pre Conditions:
647 -- This private procedure is called from ins procedure.
648 --
649 -- In Arguments:
650 -- A Pl/Sql record structre.
651 --
652 -- Post Success:
653 -- Processing continues.
654 --
655 -- Post Failure:
656 -- If a business rules fails the error will not be handled by this procedure
657 -- unless explicity coded.
658 --
659 -- Developer Implementation Notes:
660 -- For insert, your business rules should be coded within this procedure and
661 -- should ideally (unless really necessary) just be straight procedure or
662 -- function calls. Try and avoid using conditional branching logic.
663 --
664 -- Access Status:
665 -- Internal Development Use Only.
666 --
667 -- {End Of Comments}
668 -- ----------------------------------------------------------------------------
669 Procedure insert_validate(
670 p_rec in ota_tdb_shd.g_rec_type,
671 p_enrollment_type in varchar2
672 );
673 --
674 -- ----------------------------------------------------------------------------
675 -- |---------------------------< update_validate >----------------------------|
676 -- ----------------------------------------------------------------------------
677 -- {Start Of Comments}
678 --
679 -- Description:
680 -- This procedure controls the execution of all update business rules
681 -- validation.
682 --
683 -- Pre Conditions:
684 -- This private procedure is called from upd procedure.
685 --
686 -- In Arguments:
687 -- A Pl/Sql record structre.
688 --
689 -- Post Success:
690 -- Processing continues.
691 --
692 -- Post Failure:
693 -- If a business rules fails the error will not be handled by this procedure
694 -- unless explicity coded.
695 --
696 -- Developer Implementation Notes:
697 -- For update, your business rules should be coded within this procedure and
698 -- should ideally (unless really necessary) just be straight procedure or
699 -- function calls. Try and avoid using conditional branching logic.
700 --
701 -- Access Status:
702 -- Internal Development Use Only.
703 --
704 -- {End Of Comments}
705 -- ----------------------------------------------------------------------------
706 Procedure update_validate(
707 p_rec in ota_tdb_shd.g_rec_type,
708 p_enrollment_type in varchar2
709 );
710 --
711 -- ----------------------------------------------------------------------------
712 -- |---------------------------< delete_validate >----------------------------|
713 -- ----------------------------------------------------------------------------
714 -- {Start Of Comments}
715 --
716 -- Description:
717 -- This procedure controls the execution of all delete business rules
718 -- validation.
719 --
720 -- Pre Conditions:
721 -- This private procedure is called from del procedure.
722 --
723 -- In Arguments:
724 -- A Pl/Sql record structre.
725 --
726 -- Post Success:
727 -- Processing continues.
728 --
729 -- Post Failure:
730 -- If a business rules fails the error will not be handled by this procedure
731 -- unless explicity coded.
732 --
733 -- Developer Implementation Notes:
734 -- For delete, your business rules should be coded within this procedure and
735 -- should ideally (unless really necessary) just be straight procedure or
736 -- function calls. Try and avoid using conditional branching logic.
737 --
738 -- Access Status:
739 -- Internal Development Use Only.
740 --
741 -- {End Of Comments}
742 -- ----------------------------------------------------------------------------
743 Procedure delete_validate(p_rec in ota_tdb_shd.g_rec_type);
744 --
745 -- Added for bug#4606760
746 PROCEDURE check_secure_event(p_event_id IN NUMBER
747 ,p_delegate_person_id IN NUMBER);
748 end ota_tdb_bus;
[Home] [Help]