1 Package ota_trb_api_procedures as
2 /* $Header: ottrb02t.pkh 120.5.12000000.3 2007/07/05 09:09:07 aabalakr noship $ */
3 --
4 -- The business rules......
5 --
6 --
7 --
8 -- takes into account even PLANNED bookings while checking conflicts
9 function check_booking_conflict(p_supplied_resource_id in number
10 ,p_required_date_from in Date
11 ,p_required_start_time in varchar2
12 ,p_required_date_to in Date
13 ,p_required_end_time in varchar2
14 ,p_timezone in varchar2
15 ,p_resource_booking_id in number default null
16 ,p_book_entire_period_flag in varchar2 default 'Y'
17 )return varchar2 ;
18
19 function is_booking_conflict(p_supplied_resource_id in number
20 ,p_required_date_from in Date
21 ,p_required_start_time in varchar2
22 ,p_required_date_to in Date
23 ,p_required_end_time in varchar2
24 ,p_timezone in varchar2
25 ,p_target_resource_booking_id in number
26 ,p_book_entire_period_flag in varchar2 default 'Y'
27 )return varchar2 ;
28
29 function check_ss_double_booking(p_supplied_resource_id in number
30 ,p_required_date_from in date
31 ,p_required_start_time in varchar2
32 ,p_required_date_to in date
33 ,p_required_end_time in varchar2
34 ,p_resource_booking_id in number
35 ,p_book_entire_period_flag in varchar2
36 ,p_timezone in varchar2) return varchar2;
37
38 -- ---------------------------------------------------------------------
39 -- |----------------------------< check_obj_booking_dates >---------------------
40 -- ---------------------------------------------------------------------
41 -- PUBLIC
42 -- Description: Required dates must be within boudaries of suppliable
43 -- resource validity dates.
44 --
45 procedure check_obj_booking_dates(p_supplied_resource_id in number,
46 p_req_from in date,
47 p_req_to in date,
48 p_event_id in number,
49 p_chat_id in number,
50 p_forum_id in number,
51 p_timezone_code in varchar2,
52 p_req_time_from in varchar2,
53 p_req_time_to in varchar2,
54 p_warning out nocopy varchar2);
55 -- --------------------------------------------------------------------
56 -- |------------------< check_resource_type >--------------------------
57 -- ----------------------------------------------------------------aru_LM Class1----
58 -- PRIVATE
59 -- Description: This function returns a TRUE if the resource type is a
60 -- venue based upon the given supplied_resource_id. This is
61 -- only for use by procedures in this package.
62 --
63 function check_resource_type(p_supplied_resource_id in number,
64 p_type in varchar2)
65 return boolean;
66 --
67 -- ---------------------------------------------------------------------
68 -- |-------------------< check_role_to_play >---------------------------
69 -- ---------------------------------------------------------------------
70 -- PUBLIC
71 -- Description: The role_to_play field must be in the domain
72 -- Trainer Participation.
73 --
74 procedure check_role_to_play(p_role_to_play in varchar2);
75 --
76 -- ---------------------------------------------------------------------
77 -- |------------------< check_role_res_type_excl >----------------------
78 -- ---------------------------------------------------------------------
79 -- PUBLIC
80 -- Description:
81 --
82 procedure check_role_res_type_excl(p_supplied_resource_id in number,
83 p_role_to_play in varchar2);
84 --
85 -- ---------------------------------------------------------------------
86 -- |------------------------< get_total_cost >--------------------------
87 -- ---------------------------------------------------------------------
88 -- PUBLIC
89 -- Description: get the total cost of all resources required for the
90 -- event.
91 --
92 procedure get_total_cost(p_event_id in number,
93 p_total_cost in out nocopy number);
94 --
95 -- ---------------------------------------------------------------------
96 -- |--------------------< check_quantity_entered >----------------------
97 -- ---------------------------------------------------------------------
98 -- PUBLIC
99 -- Description: The quantity field can only be entered if the resource
100 -- type is not VENUE.
101 --
102 procedure check_quantity_entered(p_supplied_resource_id in number,
103 p_quantity in number);
104 --
105 -- ---------------------------------------------------------------------
106 -- |-------------------< check_delivery_address >-----------------------
107 -- ---------------------------------------------------------------------
108 -- PUBLIC
109 -- Description: Delivery address cannot be entered if resource type is
110 -- VENUE or TRAINER.
111 --
112 procedure check_delivery_address(p_supplied_resource_id in number,
113 p_del_add in varchar2);
114 --
115 -- ---------------------------------------------------------------------
116 -- |------------------------< get_resource_booking_id >-----------------
117 -- ---------------------------------------------------------------------
118 -- PUBLIC
119 -- Description: Return the RESOURCE BOOKING ID when supplied with only
120 -- the SUPPLIED RESOURCE ID and EVENT ID.
121 --
122 function get_resource_booking_id(p_supplied_resource_id in number,
123 p_event_id in number)
124 return number;
125 --
126 -- ---------------------------------------------------------------------
127 -- |-----------------------< resource_booked_for_event >----------------
128 -- ---------------------------------------------------------------------
129 -- PUBLIC
130 -- Description: Return a TRUE if any resource bookings have been made
131 -- for the specified event. Otherwise return a FALSE.
132 --
133 function resource_booked_for_event(p_event_id in number)
134 return boolean;
135 --
136 -- ---------------------------------------------------------------------
137 -- |----------------------------< check_dates_tsr >---------------------
138 -- ---------------------------------------------------------------------
139 -- PUBLIC
140 -- Description: Required dates must be within boudaries of suppliable
141 -- resource validity dates.
142 --
143 procedure check_dates_tsr(p_supplied_resource_id in number,
144 p_req_from in date,
145 p_req_to in date,
146 p_req_start_time in varchar2,
147 p_req_end_time in varchar2,
148 p_timezone_code in varchar2);
149 --
150 -- ---------------------------------------------------------------------
151 -- |-------------------------< check_evt_tsr_bus_grp >------------------
152 -- ---------------------------------------------------------------------
153 -- PUBLIC
154 -- Description: The events business group id must be the same as that of
155 -- the suppliable resource.
156 --
157 -- NB: This business rule may well disappear in the future as the
158 -- structure of organizations for OTA may change with the
159 -- addition of a VENDORS table. Suppliable Resources table may
160 -- well then contain a business_group_id column anyway.
161 -- KLS 24/11/94.
162 --
163 procedure check_evt_tsr_bus_grp(p_event_id in number,
164 p_supplied_resource_id in number);
165 --
166 -- ---------------------------------------------------------------------
167 -- |---------------------------< check_from_to_dates >------------------
168 -- ---------------------------------------------------------------------
169 -- PUBLIC
170 -- Description: required date from must be less than or equal to the
171 -- required date to.
172 --
173 procedure check_from_to_dates(p_req_from in date,
174 p_req_to in date);
175 --
176 -- ---------------------------------------------------------------------
177 -- |----------------------------< check_update_tra >--------------------
178 -- ---------------------------------------------------------------------
179 -- PUBLIC
180 -- Description: Update of required dates must not invalidate any
181 -- resource allocations.
182 --
183 procedure check_update_tra(p_resource_booking_id in number,
184 p_req_date_from in date,
185 p_req_date_to in date);
186 --
187 -- ---------------------------------------------------------------------
188 -- |------------------------< check_tra_trainer_exists >----------------
189 -- ---------------------------------------------------------------------
190 -- PUBLIC
191 -- Description: A resource booking may not be deleted if a row exists
192 -- in OTA_RESOURCE_ALLOCATIONS with this resource booking
193 -- id.
194 --
195 procedure check_tra_trainer_exists(p_resource_booking_id in number);
196 --
197 -- ---------------------------------------------------------------------
198 -- |--------------------< check_tra_resource_exists >-------------------
199 -- ---------------------------------------------------------------------
200 -- PUBLIC
201 -- Description: A resource booking may not be deleted if in use as a
202 -- EQUIPMENT_RESOURCE_BOOKING_ID in
203 -- OTA_RESOURCE_ALLOCATIONS.
204 --
205 procedure check_tra_resource_exists(p_resource_booking_id in number);
206 --
207 -- ---------------------------------------------------------------------
208 -- |------------------------< check_status >----------------------------
209 -- ---------------------------------------------------------------------
210 -- PUBLIC
211 -- Description: The user status must be in the domain RESOURCE BOOKING
212 -- STATUS.
213 --
214 procedure check_status(p_status in varchar2);
215 --
216 -- ---------------------------------------------------------------------
217 -- |-----------------------< check_status_value >-----------------------
218 -- ---------------------------------------------------------------------
219 -- PUBLIC
220 -- Description: If status is confirmed then check that its valid to have
221 -- confirmed resource bookings against this event.
222 --
223 procedure check_status_value;
224 --
225 -- ---------------------------------------------------------------------
226 -- |---------------------< check_primary_venue >------------------------
227 -- ---------------------------------------------------------------------
228 -- PUBLIC
229 -- Description: Only one venue resource booking may be a primary venue.
230 --
231 procedure check_primary_venue(p_event_id in number,
232 p_resource_booking_id in number,
233 p_prim_ven in varchar2,
234 p_req_from in date,
235 p_req_to in date);
236 --
237 -- ---------------------------------------------------------------------
238 -- |--------------------< check_double_booking >------------------------
239 -- ---------------------------------------------------------------------
240 -- PUBLIC
241 -- Description: Function returning TRUE is another CONFIRMED booking for the
242 -- resource is found
243 --
244 function check_double_booking(p_supplied_resource_id in number
245 ,p_required_date_from in date
246 ,p_required_start_time in varchar2
247 ,p_required_date_to in date
248 ,p_required_end_time in varchar2
249 ,p_resource_booking_id in number
250 ,p_book_entire_period_flag in varchar2
251 ,p_timezone in varchar2
252 ,p_last_res_bkng_id in number default null) return boolean;
253 --
254 -- ---------------------------------------------------------------------
255 -- |---------------------< check_if_tfl_exists >------------------------
256 -- ---------------------------------------------------------------------
257 -- PUBLIC
258 -- Description: If finance lines exist for the booking it may not be
259 -- deleted.
260 --
261 procedure check_if_tfl_exists(p_resource_booking_id in number);
262 --
263 -- -------------------------------------------------------------------
264 -- |-------------------< check_event_type >---------------------------
265 -- -------------------------------------------------------------------
266 -- PUBLIC
267 -- Description: Resource bookings may be made for the following event
268 -- types: SCEDULED, SESSION, PROGRAMME MEMBER, DEVELOPMENT
269 --
270 procedure check_event_type(p_event_id in number);
271 --
272 -- -------------------------------------------------------------------
273 -- |-------------------< check_update_quant_del >---------------------
274 -- -------------------------------------------------------------------
275 -- PUBLIC
276 -- Description: If the Quantity or Delegates_per_unit fields are upated
277 -- then a check needs to made to ensure that their sum is
278 -- not exceeded by the number of resource allocations made
279 -- to the booking.
280 --
281 -- returns TRUE if no. of allocations is ok or the
282 -- calculation cannot be performed due to one of the
283 -- variables being null. Returns FALSE if no. of
284 -- allocations not ok.
285 --
286 function check_update_quant_del(p_resource_booking_id in number,
287 p_quantity in number,
288 p_del_per_unit in number)
289 return boolean;
290 --
291 -- ------------------------------------------------------------------
292 -- |---------------------< get_required_resources>------------------
293 ---------------------------------------------------------------------
294 -- Description: Get the mandatory resources defined for an activity
295
296 Procedure get_required_resources(p_activity_version_id in number,
297 p_event_id in number,
298 p_date_booking_placed in date,
299 p_event_start_date in date,
300 p_event_end_date in date );
301 --
302 -- -------------------------------------------------------------------
303 -- |----------------------< get_evt_defaults >------------------------
304 -- -------------------------------------------------------------------
305 -- PUBLIC
306 procedure get_evt_defaults(p_event_id in number,
307 p_event_title in out nocopy varchar2,
308 p_event_start_date in out nocopy date,
309 p_event_end_date in out nocopy date,
310 p_event_start_time in out nocopy varchar2,
311 p_event_end_time in out nocopy varchar2,
312 p_curr_code in out nocopy varchar2,
313 p_curr_meaning in out nocopy varchar2);
314 --
315 -- ---------------------------------------------------------------------
316 -- -------------------< check_trainer_venue_book >----------------------
317 -- ---------------------------------------------------------------------
318 --
319 -- PUBLIC
320 -- Description: Check that Trainers and Venues cannot be double booked
321 -- if Confirmed
322 --
323 --
324 procedure check_trainer_venue_book
325 (p_supplied_resource_id in number
326 ,p_required_date_from in date
327 ,p_required_start_time in varchar2
328 ,p_required_date_to in date
329 ,p_required_end_time in varchar2
330 ,p_resource_booking_id in number
331 ,p_book_entire_period_flag in varchar2
332 ,p_timezone in varchar2
333 );
334 --
335 -- ---------------------------------------------------------------------
336 -- |---------------------< check_start_end_times >----------------------
337 -- ---------------------------------------------------------------------
338 -- PUBLIC
339 -- Description: Start time must be before end time.
340 --
341 procedure check_start_end_times(p_start_time in varchar2,
342 p_end_time in varchar2);
343 --
344 -- ---------------------------------------------------------------------
345
346 -- ---------------------------------------------------------------------
347 -- |--------------------< check_trainer_competence >--------------------
348 -- ---------------------------------------------------------------------
349 -- PUBLIC
350 -- Description: Check trainer competence match the activity.
351 --
352 procedure check_trainer_competence (p_event_id in number,
353 p_supplied_resource_id in number,
354 p_required_date_from IN DATE,
355 p_required_date_to IN DATE,
356 p_end_of_time IN DATE,
357 p_warn out nocopy boolean) ;
358
359 end ota_trb_api_procedures;
[Home] [Help]