1 PACKAGE OTA_EVT_BUS AUTHID CURRENT_USER as
2 /* $Header: otevt01t.pkh 120.0.12010000.2 2009/05/05 12:20:45 pekasi ship $ */
3 --
4 --added for eBS by asud
5 -- ---------------------------------------------------------------------------
6 -- |----------------------< set_security_group_id >--------------------------|
7 -- ---------------------------------------------------------------------------
8 -- {Start Of Comments}
9 --
10 -- Description:
11 -- Sets the security_group_id in CLIENT_INFO for the appropriate business
12 -- group context.
13 --
14 -- Prerequisites:
15 -- The primary key identified by p_event_id
16 -- already exists.
17 --
18 -- In Arguments:
19 -- p_event_id
20 --
21 --
22 -- Post Success:
23 -- The security_group_id will be set in CLIENT_INFO.
24 --
25 -- Post Failure:
26 -- An error is raised if the value does not exist.
27 --
28 -- Access Status:
29 -- Internal Development Use Only.
30 --
31 -- {End Of Comments}
32 -- ---------------------------------------------------------------------------
33 procedure set_security_group_id
34 (p_event_id in number
35 ,p_associated_column1 in varchar2 default null
36 );
37 --added for eBS by asud
38 --
39 -- Added For Bug 4348949
40 -- ---------------------------------------------------------------------------
41 -- |---------------------< return_legislation_code >-------------------------|
42 -- ---------------------------------------------------------------------------
43 -- {Start Of Comments}
44 --
45 -- Description:
46 -- Return the legislation code for a specific primary key value
47 --
48 -- Prerequisites:
49 -- The primary key identified by p_event_id
50 -- already exists.
51 --
52 -- In Arguments:
53 -- p_event_id
54 --
55 --
56 -- Post Success:
57 -- The business group's legislation code will be returned.
58 --
59 -- Post Failure:
60 -- An error is raised if the value does not exist.
61 --
62 -- Access Status:
63 -- Internal Development Use Only.
64 --
65 -- {End Of Comments}
66 -- ---------------------------------------------------------------------------
67 FUNCTION return_legislation_code
68 (p_event_id in number
69 ) RETURN varchar2;
70 --
71 -- Added For Bug 4348949
72
73
74 -- ----------------------------------------------------------------------------
75 -- -------------------------< UNIQUE_EVENT_TITLE >-----------------------------
76 -- ----------------------------------------------------------------------------
77 --
78 -- Returns TRUE if the event has a title which is unique within its
79 -- business group. If the event id is not null, then the check avoids
80 -- comparing the title against itself. Titles are compared regardless
81 -- of case.
82 --
83 function UNIQUE_EVENT_TITLE (
84 P_TITLE in varchar2,
85 P_BUSINESS_GROUP_ID in number,
86 P_PARENT_EVENT_ID in number,
87 P_EVENT_ID in number default null
88 ) return boolean;
89 --
90 -- ----------------------------------------------------------------------------
91 -- -------------------------< RESOURCES_AFFECTED >-----------------------------
92 -- ----------------------------------------------------------------------------
93 --
94 -- Returns TRUE if the event time has changed so that resources are
95 -- outside the times of the event.
96 --
97 function RESOURCES_AFFECTED (
98 P_EVENT_ID in number,
99 P_START_TIME in varchar2,
100 P_END_TIME in varchar2,
101 P_COURSE_START_DATE in date,
102 P_COURSE_END_DATE in date
103 ) return boolean;
104 --
105 -- ----------------------------------------------------------------------------
106 -- ----------------------------< STATUS_CHANGE_NORMAL >------------------------
107 -- ----------------------------------------------------------------------------
108 --
109 -- Returns true if a program member exists that is wait_listed
110 --
111 function status_change_normal (p_event_id in number) return boolean;
112 -- ----------------------------------------------------------------------------
113 -- -------------------------< ENROLLMENT_DATES_EVENT_VALID >-------------------
114 -- ----------------------------------------------------------------------------
115 --
116 -- Checks if enrollment dates are valid when matched against
117 -- the start and end dates of the event.
118 --
119 Procedure enrollment_dates_event_valid (p_enrollment_start_date in out nocopy date,
120 p_enrollment_end_date in out nocopy date,
121 p_course_start_date in out nocopy date,
122 p_course_end_date in out nocopy date);
123 -- ----------------------------------------------------------------------------
124 -- ----------------------------< CHECK_EVENT_STATUS >--------------------------
125 -- ----------------------------------------------------------------------------
126 --
127 -- Checks if event status is planned or event type is SELFPACED, in which
128 -- case you can have an event that has a null end date, otherwise an event
129 -- must have an end date.
130 --
131 Procedure check_event_status (p_event_status in varchar2,
132 p_course_end_date in date,
133 p_event_type in varchar2);
134 --
135 -- ----------------------------------------------------------------------------
136 -- -------------------------< ENROLLMENT_DATES_ARE_VALID >---------------------
137 -- ----------------------------------------------------------------------------
138 --
139 -- Checks if enrollment dates are valid against the activity start
140 -- and end dates.
141 --
142 /*--changes made for eBS by asud
143 Procedure enrollment_dates_are_valid( p_activity_version_id in number,
144 p_enrollment_start_date in date,
145 p_enrollment_end_date in date);
146 */ --changes made for eBS by asud
147 Procedure enrollment_dates_are_valid( p_parent_offering_id in number,
148 p_enrollment_start_date in date,
149 p_enrollment_end_date in date);
150 --
151 -- ----------------------------------------------------------------------------
152 -- --------------------------< GET_PROG_TITLE >--------------------------------
153 -- ----------------------------------------------------------------------------
154 --
155 -- Returns Program Title if an event is part of a program
156 --
157 function GET_PROG_TITLE (
158 P_EVENT_ID in number
159 ) return varchar2;
160 --
161 pragma restrict_references (GET_PROG_TITLE, WNDS, WNPS);
162 -- ----------------------------------------------------------------------------
163 -- -------------------------< CHECK_ENROLLMENT_DATES >-----------------------------
164 -- ----------------------------------------------------------------------------
165 --
166 -- Used to call error messages if an error occurs.
167 --
168 Procedure check_enrollment_dates
169 (
170 p_par_start in date
171 ,p_par_end in date
172 ,p_child_start in date
173 ,p_child_end in date
174 );
175 --
176 -- ----------------------------------------------------------------------------
177 -- -------------------------< ENROLLMENT_AFTER_EVENT_END >---------------------
178 -- ----------------------------------------------------------------------------
179 --
180 -- Checks if enrollment end date is after event end date.
181 --
182 Function enrollment_after_event_end(
183 p_enrollment_end_date in out nocopy date,
184 p_course_end_date in out nocopy date)
185 return boolean;
186 --
187 --
188 -- ----------------------------------------------------------------------------
189 -- -------------------------< COURSE_DATES_ARE_VALID >-------------------------
190 -- ----------------------------------------------------------------------------
191 --
192 -- Check course dates are valid when compared to the activity
193 -- start date.
194 --
195 /*--changes made for eBS by asud
196 procedure COURSE_DATES_ARE_VALID (p_activity_version_id in number,
197 p_course_start_date in date,
198 p_course_end_date in date,
199 p_event_status in varchar2);
200 */--changes made for eBS by asud
201 procedure COURSE_DATES_ARE_VALID (p_parent_offering_id in number,
202 p_course_start_date in date,
203 p_course_end_date in date,
204 p_event_status in varchar2,
205 p_event_type in varchar2);
206 -- ----------------------------------------------------------------------------
207 -- -------------------------< SESSION_VALID >----------------------------------
208 -- ----------------------------------------------------------------------------
209 --
210 -- Check session dates are still valid even if course start or end
211 -- dates have changed.
212 --
213 procedure SESSION_VALID (p_event_id in number,
214 p_course_start_date in date,
215 p_course_end_date in date);
216
217
218 --
219 -- ----------------------------------------------------------------------------
220 -- -------------------------< CHANGE_TO_WAIT_STATUS >--------------------------
221 -- ----------------------------------------------------------------------------
222 --
223 -- Checks if an event can be changed to wait status, this depends if
224 -- any enrollments on the evnt have a status other than wait status.
225 --
226 function CHANGE_TO_WAIT_STATUS (p_business_group_id in number,
227 p_event_id in number) return boolean;
228 --
229 -- ----------------------------------------------------------------------------
230 -- -----------------------< CHECK_TITLE_IS_UNIQUE >----------------------------
231 -- ----------------------------------------------------------------------------
232 --
233 -- Validates the uniqueness of the event title (ignoring case).
234 --
235 procedure CHECK_TITLE_IS_UNIQUE (
236 P_TITLE in varchar2,
237 P_BUSINESS_GROUP_ID in number,
238 P_PARENT_EVENT_ID in number,
239 P_EVENT_ID in number default null,
240 P_OBJECT_VERSION_NUMBER in number default null
241 );
242 --
243 -- ----------------------------------------------------------------------------
244 -- ---------------------< COURSE_DATES_SPAN_SESSIONS >-------------------------
245 -- ----------------------------------------------------------------------------
246 --
247 -- Returns TRUE if the course dates for an event still span the dates of
248 -- its sessions. This function is overloaded so that one can check either
249 -- a new session date is valid or that updates to the course dates will
250 -- not invalidate any sessions.
251 --
252 -- This version of the function checks that a new or updated session date
253 --
254 function COURSE_DATES_SPAN_SESSIONS (
255 P_PARENT_EVENT_ID in number,
256 P_NEW_SESSION_DATE in date
257 ) return boolean;
258 --
259 -- This version of the function checks that updated course dates do not
260 -- invalidate any of the event's sessions.
261 --
262 function COURSE_DATES_SPAN_SESSIONS (
263 P_EVENT_ID in number,
264 P_COURSE_START_DATE in date,
265 P_COURSE_END_DATE in date
266 ) return boolean;
267 --
268 -- ----------------------------------------------------------------------------
269 -- -----------------------< CHECK_UPDATED_COURSE_DATES >-----------------------
270 -- ----------------------------------------------------------------------------
271 --
272 procedure CHECK_UPDATED_COURSE_DATES (
273 P_EVENT_ID in number,
274 P_OBJECT_VERSION_NUMBER in number,
275 P_EVENT_TYPE in varchar2,
276 P_COURSE_START_DATE in date,
277 P_COURSE_END_DATE in date
278 );
279 --
280 -- ----------------------------------------------------------------------------
281 -- ------------------------< CHECK_SESSION_WITHIN_COURSE >---------------------
282 -- ----------------------------------------------------------------------------
283 --
284 -- Checks that a session date lies between the course start and end dates
285 -- of its parent event.
286 --
287 procedure CHECK_SESSION_WITHIN_COURSE (
288 P_EVENT_TYPE in varchar2,
289 P_PARENT_EVENT_ID in number,
290 P_COURSE_START_DATE in date,
291 P_EVENT_ID in number default null,
292 P_OBJECT_VERSION_NUMBER in number default null
293 );
294
295 --
296 -- ----------------------------------------------------------------------------
297 -- -----------------------< VALID_PARENT_EVENT >-------------------------------
298 -- ----------------------------------------------------------------------------
299 --
300 -- Returns TRUE if the parent event ID specified exists in the events
301 -- table, has the same business group as the child row and is a valid
302 -- parent for the event type specified.
303 --
304 function VALID_PARENT_EVENT (
305 P_PARENT_EVENT_ID in number,
306 P_BUSINESS_GROUP_ID in number,
307 P_EVENT_TYPE in varchar2
308 ) return boolean;
309 --
310 -- ----------------------------------------------------------------------------
311 -- -----------------------< CHECK_PARENT_EVENT_IS_VALID >----------------------
312 -- ----------------------------------------------------------------------------
313 --
314 procedure CHECK_PARENT_EVENT_IS_VALID (
315 P_PARENT_EVENT_ID in number,
316 P_BUSINESS_GROUP_ID in number,
317 P_EVENT_TYPE in varchar2,
318 P_EVENT_ID in number default null,
319 P_OBJECT_VERSION_NUMBER in number default null
320 );
321 --
322 -- ----------------------------------------------------------------------------
323 -- -----------------------< CHECK_CHILD_ENTITIES >-----------------------------
324 -- ----------------------------------------------------------------------------
325 --
326 procedure CHECK_CHILD_ENTITIES (
327 P_EVENT_ID in number
328 );
329 --
330 -- ----------------------------------------------------------------------------
331 -- -----------------------< CHECK_FOR_ST_FINANCE_LINES >-------------------------------
332 -- ----------------------------------------------------------------------------
333 --
337 --
334 -- This function checks to see if any 'ST' succesful Transferred finance Lines
335 -- which have not been cancelled exists for any booking within the Event.
336 -- Returns TRUE if FL exists.
338 function CHECK_FOR_ST_FINANCE_LINES (
339 P_EVENT_ID in number
340 ) return boolean;
341
342 --
343 -- ----------------------------------------------------------------------------
344 -- |---------------------------< check_owner_id >-----------------------------|
345 -- ----------------------------------------------------------------------------
346 --
347 -- This function checks to see if any the owner_id exists in
348 -- per_people_f table
349 --
350 --
351 Procedure check_owner_id (p_event_id in number,
352 p_owner_id in number,
353 p_business_group_id in number,
354 p_course_start_date in date);
355
356 -- ----------------------------------------------------------------------------
357 -- |---------------------------< check_line_id >---------------------------|
358 -- ----------------------------------------------------------------------------
359 --
360 -- This function checks to see if any the Line_id exists in
361 -- oe_order_lines table
362 --
363 --
364
365 Procedure check_line_id
366 (p_event_id in number
367 ,p_line_id in number
368 ,p_org_id in number) ;
369
370
371 -- ----------------------------------------------------------------------------
372 -- |---------------------------< chk_status_changed >----------------------|
373 -- ----------------------------------------------------------------------------
374 -- This procedure will check whether the status is changed. this procedure is
375 -- called by post_update procedure and will be only used by OM integration.
376 -- The purpose of this procedure is to cancel an order line, Create RMA and
377 -- To notify the Workflow to continue.
378
379 Procedure chk_status_changed
380 (p_line_id in number
381 ,p_event_status in varchar2
382 ,p_event_id in number
383 ,p_org_id in number
384 ,p_owner_id in number
385 ,p_event_title in varchar2
386 );
387 --
388 -- ----------------------------------------------------------------------------
389 -- |---------------------------< chk_order_line_exist >---------------------|
390 -- ----------------------------------------------------------------------------
391 -- Description : This procedure will be called by Delete_validate procedure. This
392 -- procedure will check whether order line exist or not.
393 Procedure chk_Order_line_exist
394 (p_line_id in number
395 ,p_org_id in number);
396 --
397 -- ----------------------------------------------------------------------------
398 -- |-----------------------< chk_Training_center >---------------------------|
399 -- ----------------------------------------------------------------------------
400 -- Description : This procedure will be called by Insert_validate procedure and
401 -- Update_validaate procedure. This
402 -- procedure will check whether Training center exist or not.
403 --
404 --
405 Procedure chk_Training_center
406 (p_event_id in number,
407 p_training_center_id in number);
408 --
409 -- ----------------------------------------------------------------------------
410 -- |-----------------------------< chk_location >---------------------------|
411 -- ----------------------------------------------------------------------------
412 -- Description : This procedure will be called by Insert_validate procedure and
413 -- Update_validaate procedure. This
414 -- procedure will check whether Location exist or not.
415 --
416 Procedure Chk_location
417 (p_event_id in number,
418 p_location_id in number,
419 p_training_center_id in number,
420 p_course_end_date in date);
421
422 --
423 -- ----------------------------------------------------------------------------
424 -- |------------------------< check_unique_offering_id>------------------------|
425 -- ----------------------------------------------------------------------------
426 --
427 -- PUBLIC
428 -- Description:
429 -- Check uniqueness of offering_id
430 --
431 --
432 --
433 --
434 Procedure check_unique_offering_id
435 (
436 p_event_id in number,
437 p_offering_id in number);
438 --
439 --
440 -- ----------------------------------------------------------------------------
441 -- |-----------------< check_valid_activity_version_id>------------------------|
442 -- ----------------------------------------------------------------------------
443 --
444 -- PUBLIC
445 -- Description:
446 --Checks if the parent_offering_id belongs to the activity_version_id
447 --
448 --
449 Procedure chk_activity_version_id
450 (p_activity_version_id in number,
451 p_parent_offering_id in number);
452
453 -- ----------------------------------------------------------------------------
454 -- |---------------------------< insert_validate >----------------------------|
455 -- ----------------------------------------------------------------------------
456 -- {Start Of Comments}
457 --
458 -- Description:
459 -- This procedure controls the execution of all insert business rules
460 -- validation.
461 --
462 -- Pre Conditions:
466 -- A Pl/Sql record structre.
463 -- This private procedure is called from ins procedure.
464 --
465 -- In Arguments:
467 --
468 -- Post Success:
469 -- Processing continues.
470 --
471 -- Post Failure:
472 -- If a business rules fails the error will not be handled by this procedure
473 -- unless explicity coded.
474 --
475 -- Developer Implementation Notes:
476 -- For insert, your business rules should be coded within this procedure and
477 -- should ideally (unless really necessary) just be straight procedure or
478 -- function calls. Try and avoid using conditional branching logic.
479 --
480 -- Access Status:
481 -- Internal Development Use Only.
482 --
483 -- {End Of Comments}
484 -- ----------------------------------------------------------------------------
485 -- VT 05/06/97 #488173
486 Procedure insert_validate(p_rec in out nocopy ota_evt_shd.g_rec_type);
487 --
488 -- ----------------------------------------------------------------------------
489 -- |---------------------------< update_validate >----------------------------|
490 -- ----------------------------------------------------------------------------
491 -- {Start Of Comments}
492 --
493 -- Description:
494 -- This procedure controls the execution of all update business rules
495 -- validation.
496 --
497 -- Pre Conditions:
498 -- This private procedure is called from upd procedure.
499 --
500 -- In Arguments:
501 -- A Pl/Sql record structre.
502 --
503 -- Post Success:
504 -- Processing continues.
505 --
506 -- Post Failure:
507 -- If a business rules fails the error will not be handled by this procedure
508 -- unless explicity coded.
509 --
510 -- Developer Implementation Notes:
511 -- For update, your business rules should be coded within this procedure and
512 -- should ideally (unless really necessary) just be straight procedure or
513 -- function calls. Try and avoid using conditional branching logic.
514 --
515 -- Access Status:
516 -- Internal Development Use Only.
517 --
518 -- {End Of Comments}
519 -- ----------------------------------------------------------------------------
520 -- VT 05/06/97 #488173
521 Procedure update_validate(p_rec in out nocopy ota_evt_shd.g_rec_type);
522 --
523 -- ----------------------------------------------------------------------------
524 -- |---------------------------< delete_validate >----------------------------|
525 -- ----------------------------------------------------------------------------
526 -- {Start Of Comments}
527 --
528 -- Description:
529 -- This procedure controls the execution of all delete business rules
530 -- validation.
531 --
532 -- Pre Conditions:
533 -- This private procedure is called from del procedure.
534 --
535 -- In Arguments:
536 -- A Pl/Sql record structre.
537 --
538 -- Post Success:
539 -- Processing continues.
540 --
541 -- Post Failure:
542 -- If a business rules fails the error will not be handled by this procedure
543 -- unless explicity coded.
544 --
545 -- Developer Implementation Notes:
546 -- For delete, your business rules should be coded within this procedure and
547 -- should ideally (unless really necessary) just be straight procedure or
548 -- function calls. Try and avoid using conditional branching logic.
549 --
550 -- Access Status:
551 -- Internal Development Use Only.
552 --
553 -- {End Of Comments}
554 -- ----------------------------------------------------------------------------
555 Procedure delete_validate(p_rec in ota_evt_shd.g_rec_type);
556
557 end ota_evt_bus;
[Home] [Help]