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