1 Package OTA_TBD_API AUTHID CURRENT_USER as
2 /* $Header: ottbd01t.pkh 115.3 2003/06/27 06:06:16 dbatra ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 booking_deal_id number(9),
11 customer_id number(15),
12 approved_by_person_id ota_booking_deals.approved_by_person_id%TYPE,
13 business_group_id number(9),
14 name varchar2(80),
15 object_version_number number(9), -- Increased length
16 start_date date,
17 category varchar2(30),
21 end_date date,
18 comments varchar2(2000),
19 description varchar2(240),
20 discount_percentage number(17,2), -- Increased length
22 number_of_places number(9),
23 LIMIT_EACH_EVENT_FLAG varchar2 (1),
24 overdraft_limit number(17,2), -- Increased length
25 type varchar2(30),
26 price_list_id number(9),
27 activity_version_id number(9),
28 event_id number(9),
29 tbd_information_category varchar2(30),
30 tbd_information1 varchar2(150),
31 tbd_information2 varchar2(150),
32 tbd_information3 varchar2(150),
33 tbd_information4 varchar2(150),
34 tbd_information5 varchar2(150),
35 tbd_information6 varchar2(150),
36 tbd_information7 varchar2(150),
37 tbd_information8 varchar2(150),
38 tbd_information9 varchar2(150),
39 tbd_information10 varchar2(150),
40 tbd_information11 varchar2(150),
41 tbd_information12 varchar2(150),
42 tbd_information13 varchar2(150),
43 tbd_information14 varchar2(150),
44 tbd_information15 varchar2(150),
45 tbd_information16 varchar2(150),
46 tbd_information17 varchar2(150),
47 tbd_information18 varchar2(150),
48 tbd_information19 varchar2(150),
49 tbd_information20 varchar2(150)
50 );
51 --
52 -- ----------------------------------------------------------------------------
53 -- |------------------------< return_api_dml_status >-------------------------|
54 -- ----------------------------------------------------------------------------
55 -- {Start Of Comments}
56 --
57 -- Description:
58 -- This function will return the current g_api_dml private global
59 -- boolean status.
60 -- The g_api_dml status determines if at the time of the function
61 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
62 -- is being issued from within an api.
63 -- If the status is TRUE then a dml statement is being issued from
64 -- within this entity api.
65 -- This function is primarily to support database triggers which
66 -- need to maintain the object_version_number for non-supported
67 -- dml statements (i.e. dml statement issued outside of the api layer).
68 --
69 -- Pre Conditions:
70 -- None.
71 --
72 -- In Arguments:
73 -- None.
74 --
75 -- Post Success:
76 -- Processing continues.
77 -- If the function returns a TRUE value then, dml is being executed from
78 -- within this api.
79 --
80 -- Post Failure:
81 -- None.
82 --
83 -- {End Of Comments}
84 -- ----------------------------------------------------------------------------
85 Function return_api_dml_status Return Boolean;
86 --
87 -- ----------------------------------------------------------------------------
88 -- -----------------------------< TFL_PURCHASED >------------------------------
89 -- ----------------------------------------------------------------------------
90 --
91 -- Function returns the purchased amount from OTA_FINANCE_LINES for the
92 -- BOOKING_DEAL_ID supplied in uniTs or Money as requested.
93 --
94 function TFL_PURCHASED (
95 P_BOOKING_DEAL_ID in number,
96 P_TYPE in varchar2
97 ) return number;
98 --
99 pragma restrict_references (TFL_PURCHASED, WNDS, WNPS);
100 --
101 -- ----------------------------------------------------------------------------
102 -- ------------------------------< TFL_BALANCE >-------------------------------
103 -- ----------------------------------------------------------------------------
104 --
105 -- Function returns the outstanding balance from OTA_FINANCE_LINES for the
106 -- BOOKING_DEAL_ID supplied in uniTs or Money as requested.
107 --
108 function TFL_BALANCE (
109 P_BOOKING_DEAL_ID in number,
110 P_TYPE in varchar2
111 ) return number;
112 --
113 pragma restrict_references (TFL_BALANCE, WNDS, WNPS);
114 --
115 -- ----------------------------------------------------------------------------
116 -- --------------------------------< TFL_FLAG >--------------------------------
117 -- ----------------------------------------------------------------------------
118 --
119 -- Returns 'TRUE' if OTA_FINANCE_LINES exist for the BOOKING_DEAL_ID,
120 -- or null if not present.
121 --
122 function TFL_FLAG (
123 P_BOOKING_DEAL_ID in number
124 ) return varchar2;
125 --
126 pragma restrict_references (TFL_FLAG, WNDS, WNPS);
127 --
128 -- ----------------------------------------------------------------------------
129 -- --------------------------------< CHECK_DATES_CONFLICT >--------------------
130 -- ----------------------------------------------------------------------------
131 --
132 -- Checks id booking deal dates have been changed that they do not
133 -- validate any student enrollments.
134 --
135 procedure check_dates_conflict(p_booking_deal_id in number,
136 p_start_date in date,
137 p_end_date in date);
138 --
139 --
140 -- ----------------------------------------------------------------------------
141 -- --------------------------------< CHECK_APPROVER >--------------------------
142 -- ----------------------------------------------------------------------------
143 --
144 -- The person approving the booking deal must be an employee when the
148 P_APPROVED_BY_PERSON_ID in number);
145 -- deal first becomes valid.
146 --
147 procedure CHECK_APPROVER (
149 --
150 -- ----------------------------------------------------------------------------
151 -- ---------------------------------< TFL_LINES >------------------------------
152 -- ----------------------------------------------------------------------------
153 --
154 function TFL_LINES (
155 P_BOOKING_DEAL_ID in number
156 ) return boolean;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |---------------------------------< lck >----------------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- The Lck process has two main functions to perform. Firstly, the row to be
165 -- updated or deleted must be locked. The locking of the row will only be
166 -- successful if the row is not currently locked by another user.
167 -- Secondly, during the locking of the row, the row is selected into
168 -- the g_old_rec data structure which enables the current row values from the
169 -- server to be available to the api.
170 --
171 -- Pre Conditions:
172 -- When attempting to call the lock the object version number (if defined)
173 -- is mandatory.
174 --
175 -- In Arguments:
176 -- The arguments to the Lck process are the primary key(s) which uniquely
177 -- identify the row and the object version number of row.
178 --
179 -- Post Success:
180 -- On successful completion of the Lck process the row to be updated or
181 -- deleted will be locked and selected into the global data structure
182 -- g_old_rec.
183 --
184 -- Post Failure:
185 -- The Lck process can fail for three reasons:
186 -- 1) When attempting to lock the row the row could already be locked by
187 -- another user. This will raise the HR_Api.Object_Locked exception.
188 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
189 -- This error is trapped and reported using the message name
190 -- 'HR_7220_INVALID_PRIMARY_KEY'.
191 -- 3) The row although existing in the HR Schema has a different object
192 -- version number than the object version number specified.
193 -- This error is trapped and reported using the message name
194 -- 'HR_7155_OBJECT_INVALID'.
195 --
196 -- Developer Implementation Notes:
197 -- For each primary key and the object version number arguments add a
198 -- call to hr_api.mandatory_arg_error procedure to ensure that these
199 -- argument values are not null.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 Procedure lck
204 (
205 p_booking_deal_id in number,
206 p_object_version_number in number
207 );
208 --
209 -- ----------------------------------------------------------------------------
210 -- |---------------------------------< ins >----------------------------------|
211 -- ----------------------------------------------------------------------------
212 -- {Start Of Comments}
213 --
214 -- Description:
215 -- This procedure is the record interface for the insert business process
216 -- for the specified entity. The role of this process is to insert a fully
217 -- validated row, into the HR schema passing back to the calling process,
221 -- 1) If the p_validate argument has been set to true then a savepoint is
218 -- any system generated values (e.g. primary and object version number
219 -- attributes). This process is the main backbone of the ins business
220 -- process. The processing of this procedure is as follows:
222 -- issued.
223 -- 2) The controlling validation process insert_validate is then executed
224 -- which will execute all private and public validation business rule
225 -- processes.
226 -- 3) The pre_insert business process is then executed which enables any
227 -- logic to be processed before the insert dml process is executed.
228 -- 4) The insert_dml process will physical perform the insert dml into the
229 -- specified entity.
230 -- 5) The post_insert business process is then executed which enables any
231 -- logic to be processed after the insert dml process.
232 -- 6) If the p_validate argument has been set to true an exception is raised
233 -- which is handled and processed by performing a rollback to the
234 -- savepoint which was issued at the beginning of the Ins process.
235 --
236 -- Pre Conditions:
237 -- The main arguments to the business process have to be in the record
238 -- format.
239 --
240 -- In Arguments:
241 -- p_validate
242 -- Determines if the business process is to be validated. Setting this
243 -- boolean value to true will invoke the process to be validated. The
244 -- default is false. The validation is controlled by a savepoint and
245 -- rollback mechanism. The savepoint is issued at the beginning of the
246 -- business process and is rollbacked at the end of the business process
247 -- when all the processing has been completed. The rollback is controlled
248 -- by raising and handling the exception hr_api.validate_enabled. We use
249 -- the exception because, by raising the exception with the business
250 -- process, we can exit successfully without having any of the 'OUT'
251 -- arguments being set.
252 --
253 -- Post Success:
254 -- A fully validated row will be inserted into the specified entity
255 -- without being committed. If the p_validate argument has been set to true
256 -- then all the work will be rolled back.
257 --
258 -- Post Failure:
259 -- If an error has occurred, an error message will be supplied with the work
260 -- rolled back.
261 --
262 -- Developer Implementation Notes:
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 Procedure ins
267 (
268 p_rec in out nocopy g_rec_type,
269 p_validate in boolean default false
270 );
271 --
272 -- ----------------------------------------------------------------------------
273 -- |---------------------------------< ins >----------------------------------|
274 -- ----------------------------------------------------------------------------
275 -- {Start Of Comments}
276 --
277 -- Description:
278 -- This procedure is the attribute interface for the insert business
279 -- process for the specified entity and is the outermost layer. The role
280 -- of this process is to insert a fully validated row into the HR schema
281 -- passing back to the calling process, any system generated values
282 -- (e.g. object version number attributes).The processing of this
283 -- procedure is as follows:
284 -- 1) The attributes are converted into a local record structure by
285 -- calling the convert_defs function.
286 -- 2) After the conversion has taken place, the corresponding record ins
287 -- interface business process is executed.
288 -- 3) OUT arguments are then set to their corresponding record arguments.
289 --
290 -- Pre Conditions:
291 --
292 -- In Arguments:
293 -- p_validate
294 -- Determines if the business process is to be validated. Setting this
295 -- Boolean value to true will invoke the process to be validated.
296 -- The default is false.
297 --
298 -- Post Success:
299 -- A fully validated row will be inserted for the specified entity
300 -- without being committed (or rollbacked depending on the p_validate
301 -- status).
302 --
303 -- Post Failure:
304 -- If an error has occurred, an error message will be supplied with the work
305 -- rolled back.
306 --
307 -- Developer Implementation Notes:
308 --
309 -- {End Of Comments}
310 -- ----------------------------------------------------------------------------
311 Procedure ins
312 (
313 p_booking_deal_id out nocopy number,
314 p_customer_id in number default null,
315 p_approved_by_person_id in number default null,
316 p_business_group_id in number,
317 p_name in varchar2,
318 p_object_version_number out nocopy number,
319 p_start_date in date,
320 p_category in varchar2 default null,
321 p_comments in varchar2 default null,
322 p_description in varchar2 default null,
323 p_discount_percentage in number default null,
324 p_end_date in date default null,
325 p_number_of_places in number default null,
326 P_LIMIT_EACH_EVENT_FLAG in varchar2 default null,
327 p_overdraft_limit in number default null,
328 p_type in varchar2 default null,
329 p_price_list_id in number default null,
330 p_activity_version_id in number default null,
331 p_event_id in number default null,
332 p_tbd_information_category in varchar2 default null,
336 p_tbd_information4 in varchar2 default null,
333 p_tbd_information1 in varchar2 default null,
334 p_tbd_information2 in varchar2 default null,
335 p_tbd_information3 in varchar2 default null,
337 p_tbd_information5 in varchar2 default null,
338 p_tbd_information6 in varchar2 default null,
339 p_tbd_information7 in varchar2 default null,
340 p_tbd_information8 in varchar2 default null,
341 p_tbd_information9 in varchar2 default null,
342 p_tbd_information10 in varchar2 default null,
343 p_tbd_information11 in varchar2 default null,
344 p_tbd_information12 in varchar2 default null,
345 p_tbd_information13 in varchar2 default null,
346 p_tbd_information14 in varchar2 default null,
347 p_tbd_information15 in varchar2 default null,
348 p_tbd_information16 in varchar2 default null,
349 p_tbd_information17 in varchar2 default null,
350 p_tbd_information18 in varchar2 default null,
351 p_tbd_information19 in varchar2 default null,
352 p_tbd_information20 in varchar2 default null,
353 p_validate in boolean default false
354 );
355 --
356 -- ----------------------------------------------------------------------------
357 -- |---------------------------------< upd >----------------------------------|
358 -- ----------------------------------------------------------------------------
359 -- {Start Of Comments}
360 --
361 -- Description:
362 -- This procedure is the record interface for the update business
363 -- process for the specified entity. The role of this process is
364 -- to update a fully validated row for the HR schema passing back
365 -- to the calling process, any system generated values (e.g.
366 -- object version number attribute). This process is the main
367 -- backbone of the upd business process. The processing of this
368 -- procedure is as follows:
369 -- 1) If the p_validate argument has been set to true then a savepoint
370 -- is issued.
371 -- 2) The row to be updated is then locked and selected into the record
372 -- structure g_old_rec.
373 -- 3) Because on update arguments which are not part of the update do not
374 -- have to be defaulted, we need to build up the updated row by
375 -- converting any system defaulted arguments to their corresponding
376 -- value.
377 -- 4) The controlling validation process update_validate is then executed
378 -- which will execute all private and public validation business rule
379 -- processes.
380 -- 5) The pre_update business process is then executed which enables any
381 -- logic to be processed before the update dml process is executed.
382 -- 6) The update_dml process will physical perform the update dml into the
383 -- specified entity.
384 -- 7) The post_update business process is then executed which enables any
385 -- logic to be processed after the update dml process.
386 -- 8) If the p_validate argument has been set to true an exception is
387 -- raised which is handled and processed by performing a rollback to
388 -- the savepoint which was issued at the beginning of the upd process.
389 --
390 -- Pre Conditions:
391 -- The main arguments to the business process have to be in the record
392 -- format.
393 --
394 -- In Arguments:
395 -- p_validate
396 -- Determines if the business process is to be validated. Setting this
397 -- boolean value to true will invoke the process to be validated. The
398 -- default is false. The validation is controlled by a savepoint and
399 -- rollback mechanism. The savepoint is issued at the beginning of the
400 -- business process and is rollbacked at the end of the business process
401 -- when all the processing has been completed. The rollback is controlled
402 -- by raising and handling the exception hr_api.validate_enabled. We use
403 -- the exception because, by raising the exception with the business
404 -- process, we can exit successfully without having any of the 'OUT'
405 -- arguments being set.
406 --
407 -- Post Success:
408 -- The specified row will be fully validated and updated for the specified
409 -- entity without being committed. If the p_validate argument has been set
410 -- to true then all the work will be rolled back.
411 --
412 -- Post Failure:
413 -- If an error has occurred, an error message will be supplied with the work
414 -- rolled back.
415 --
416 -- Developer Implementation Notes:
417 --
418 -- {End Of Comments}
419 -- ----------------------------------------------------------------------------
420 Procedure upd
421 (
422 p_rec in out nocopy g_rec_type,
423 p_validate in boolean default false
424 );
425 --
426 -- ----------------------------------------------------------------------------
427 -- |---------------------------------< upd >----------------------------------|
428 -- ----------------------------------------------------------------------------
429 -- {Start Of Comments}
430 --
431 -- Description:
432 -- This procedure is the attribute interface for the update business
433 -- process for the specified entity and is the outermost layer. The role
434 -- of this process is to update a fully validated row into the HR schema
435 -- passing back to the calling process, any system generated values
436 -- (e.g. object version number attributes).The processing of this
437 -- procedure is as follows:
438 -- 1) The attributes are converted into a local record structure by
442 -- 3) OUT arguments are then set to their corresponding record arguments.
439 -- calling the convert_defs function.
440 -- 2) After the conversion has taken place, the corresponding record upd
441 -- interface business process is executed.
443 --
444 -- Pre Conditions:
445 --
446 -- In Arguments:
447 -- p_validate
448 -- Determines if the business process is to be validated. Setting this
449 -- Boolean value to true will invoke the process to be validated.
450 -- The default is false.
451 --
452 -- Post Success:
453 -- A fully validated row will be updated for the specified entity
454 -- without being committed (or rollbacked depending on the p_validate
455 -- status).
456 --
457 -- Post Failure:
458 -- If an error has occurred, an error message will be supplied with the work
459 -- rolled back.
460 --
461 -- Developer Implementation Notes:
462 --
463 -- {End Of Comments}
464 -- ----------------------------------------------------------------------------
465 Procedure upd
466 (
467 p_booking_deal_id in number,
468 p_customer_id in number default hr_api.g_number,
469 p_approved_by_person_id in number default hr_api.g_number,
470 p_business_group_id in number default hr_api.g_number,
471 p_name in varchar2 default hr_api.g_varchar2,
472 p_object_version_number in out nocopy number,
473 p_start_date in date default hr_api.g_date,
474 p_category in varchar2 default hr_api.g_varchar2,
475 p_comments in varchar2 default hr_api.g_varchar2,
476 p_description in varchar2 default hr_api.g_varchar2,
477 p_discount_percentage in number default hr_api.g_number,
478 p_end_date in date default hr_api.g_date,
479 p_number_of_places in number default hr_api.g_number,
480 P_LIMIT_EACH_EVENT_FLAG in varchar2 default hr_api.g_varchar2,
481 p_overdraft_limit in number default hr_api.g_number,
482 p_type in varchar2 default hr_api.g_varchar2,
483 p_price_list_id in number default hr_api.g_number,
484 p_activity_version_id in number default hr_api.g_number,
485 p_event_id in number default hr_api.g_number,
486 p_tbd_information_category in varchar2 default hr_api.g_varchar2,
487 p_tbd_information1 in varchar2 default hr_api.g_varchar2,
488 p_tbd_information2 in varchar2 default hr_api.g_varchar2,
489 p_tbd_information3 in varchar2 default hr_api.g_varchar2,
490 p_tbd_information4 in varchar2 default hr_api.g_varchar2,
491 p_tbd_information5 in varchar2 default hr_api.g_varchar2,
492 p_tbd_information6 in varchar2 default hr_api.g_varchar2,
493 p_tbd_information7 in varchar2 default hr_api.g_varchar2,
494 p_tbd_information8 in varchar2 default hr_api.g_varchar2,
495 p_tbd_information9 in varchar2 default hr_api.g_varchar2,
496 p_tbd_information10 in varchar2 default hr_api.g_varchar2,
497 p_tbd_information11 in varchar2 default hr_api.g_varchar2,
498 p_tbd_information12 in varchar2 default hr_api.g_varchar2,
499 p_tbd_information13 in varchar2 default hr_api.g_varchar2,
500 p_tbd_information14 in varchar2 default hr_api.g_varchar2,
501 p_tbd_information15 in varchar2 default hr_api.g_varchar2,
502 p_tbd_information16 in varchar2 default hr_api.g_varchar2,
503 p_tbd_information17 in varchar2 default hr_api.g_varchar2,
504 p_tbd_information18 in varchar2 default hr_api.g_varchar2,
505 p_tbd_information19 in varchar2 default hr_api.g_varchar2,
506 p_tbd_information20 in varchar2 default hr_api.g_varchar2,
507 p_validate in boolean default false
508 );
509 --
510 -- ----------------------------------------------------------------------------
511 -- |---------------------------------< del >----------------------------------|
512 -- ----------------------------------------------------------------------------
513 -- {Start Of Comments}
514 --
515 -- Description:
516 -- This procedure is the record interface for the delete business process
517 -- for the specified entity. The role of this process is to delete the
518 -- row from the HR schema. This process is the main backbone of the del
519 -- business process. The processing of this procedure is as follows:
520 -- 1) If the p_validate argument has been set to true then a savepoint is
521 -- issued.
522 -- 2) The controlling validation process delete_validate is then executed
523 -- which will execute all private and public validation business rule
524 -- processes.
525 -- 3) The pre_delete business process is then executed which enables any
526 -- logic to be processed before the delete dml process is executed.
527 -- 4) The delete_dml process will physical perform the delete dml for the
528 -- specified row.
529 -- 5) The post_delete business process is then executed which enables any
530 -- logic to be processed after the delete dml process.
531 -- 6) If the p_validate argument has been set to true an exception is raised
532 -- which is handled and processed by performing a rollback to the
533 -- savepoint which was issued at the beginning of the del process.
534 --
535 -- Pre Conditions:
536 -- The main arguments to the business process have to be in the record
537 -- format.
538 --
539 -- In Arguments:
540 -- p_validate
541 -- Determines if the business process is to be validated. Setting this
542 -- boolean value to true will invoke the process to be validated. The
543 -- default is false. The validation is controlled by a savepoint and
544 -- rollback mechanism. The savepoint is issued at the beginning of the
545 -- business process and is rollbacked at the end of the business process
546 -- when all the processing has been completed. The rollback is controlled
547 -- by raising and handling the exception hr_api.validate_enabled. We use
548 -- the exception because, by raising the exception with the business
549 -- process, we can exit successfully without having any of the 'OUT'
550 -- arguments being set.
551 --
552 -- Post Success:
553 -- The specified row will be fully validated and deleted for the specified
554 -- entity without being committed. If the p_validate argument has been set
555 -- to true then all the work will be rolled back.
556 --
557 -- Post Failure:
558 -- If an error has occurred, an error message will be supplied with the work
559 -- rolled back.
560 --
561 -- Developer Implementation Notes:
562 --
563 -- {End Of Comments}
564 -- ----------------------------------------------------------------------------
565 Procedure del
566 (
567 p_rec in g_rec_type,
568 p_validate in boolean default false
569 );
570 --
571 -- ----------------------------------------------------------------------------
572 -- |---------------------------------< del >----------------------------------|
573 -- ----------------------------------------------------------------------------
574 -- {Start Of Comments}
575 --
576 -- Description:
577 -- This procedure is the attribute interface for the delete business
578 -- process for the specified entity and is the outermost layer. The role
579 -- of this process is to validate and delete the specified row from the
580 -- HR schema. The processing of this procedure is as follows:
581 -- 1) The attributes are converted into a local record structure by
582 -- explicitly coding the attribute arguments into the g_rec_type
583 -- datatype.
584 -- 2) After the conversion has taken place, the corresponding record del
585 -- interface business process is executed.
586 --
587 -- Pre Conditions:
588 --
589 -- In Arguments:
590 -- p_validate
591 -- Determines if the business process is to be validated. Setting this
592 -- Boolean value to true will invoke the process to be validated.
593 -- The default is false.
594 --
595 -- Post Success:
596 -- The specified row will be fully validated and deleted for the specified
597 -- entity without being committed (or rollbacked depending on the
598 -- p_validate status).
599 --
600 -- Post Failure:
601 -- If an error has occurred, an error message will be supplied with the work
602 -- rolled back.
603 --
604 -- Developer Implementation Notes:
605 -- The attrbute in arguments should be modified as to the business process
606 -- requirements.
607 --
608 -- {End Of Comments}
609 -- ----------------------------------------------------------------------------
610 Procedure del
611 (
612 p_booking_deal_id in number,
613 p_object_version_number in number,
614 p_validate in boolean default false
615 );
616 --
617 end OTA_TBD_API;
[Home] [Help]