1 PACKAGE OTA_TPM_BUS1 AUTHID CURRENT_USER as
2 /* $Header: ottpmrhi.pkh 120.0 2005/05/29 07:48:29 appldev noship $ */
3 -- ----------------------------------------------------------------------------
4 -- |----------------------<chk_training_plan_id>------------------------------|
5 -- ----------------------------------------------------------------------------
6 -- {Start Of Comments}
7 --
8 -- Description:
9 -- Validates that the training_plan_id exists in ota_training_plans
10 -- and with the business_group_id.
11 --
12 -- Prerequisites:
13 -- Both this parameter and the business_group_id are present.
14 --
15 -- In Parameters:
16 -- p_training_plan_id
17 -- p_business_group_id
18 --
19 -- Post Success:
20 -- Processing continues if the values are legal.
21 --
22 -- Post Failure:
23 -- If an error has occurred, an error message will be raised.
24 --
25 -- Developer Implementation Notes:
26 -- None
27 --
28 -- Access Status:
29 -- Internal Row Handler Use Only.
30 --
31 -- {End Of Comments}
32 -- ----------------------------------------------------------------------------
33 Procedure chk_training_plan_id
34 (p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
35 ,p_business_group_id in ota_training_plan_members.business_group_id%TYPE
36 );
37 -- ----------------------------------------------------------------------------
38 -- |----------------------<chk_activity_definition_id>-------------------------|
39 -- ----------------------------------------------------------------------------
40 -- {Start Of Comments}
41 --
42 -- Description:
43 -- Validates that the activity definition exists in the database
44 -- and with the business_group_id.
45 --
46 -- Prerequisites:
47 -- call chk_version_definition first
48 --
49 -- In Parameters:
50 -- p_training_plan_member_id
51 -- p_object_version_number
52 -- p_activity_definition_id
53 -- p_business_group_id
54 --
55 -- Post Success:
56 -- Processing continues if the values are legal.
57 --
58 -- Post Failure:
59 -- If an error has occurred, an error message will be raised.
60 --
61 -- Developer Implementation Notes:
62 -- None
63 --
64 -- Access Status:
65 -- Internal Row Handler Use Only.
66 --
67 -- {End Of Comments}
68 -- ----------------------------------------------------------------------------
69 Procedure chk_activity_definition_id (
70 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
71 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
72 ,p_activity_definition_id in ota_training_plan_members.activity_definition_id%TYPE
73 ,p_business_group_id in ota_training_plan_members.business_group_id%TYPE
74 ) ;
75 -- ----------------------------------------------------------------------------
76 -- |----------------------<chk_version_definition>----------------------------|
77 -- ----------------------------------------------------------------------------
78 -- {Start Of Comments}
79 --
80 -- Description:
81 -- A shared function to perform the common functions of
82 -- chk_activity_version_id and
83 -- chk_activity_definition_id
84 -- This must be called before the two chk_ procedures mentioned above.
85 --
86 -- This validates that only one of activity_version_id and activity_definition_id
87 -- is set, and that if an update to one of theses columns is happending,
88 -- there are no per_budget_elements records.
89 --
90 -- Prerequisites:
91 --
92 -- In Parameters:
93 -- p_training_plan_member_id
94 -- p_object_version_number
95 -- p_activity_version_id
96 -- p_activity_definition_id
97 -- p_business_group_id
98 -- p_training_plan_id
99 --
100 -- Post Success:
101 -- Processing continues if the values are legal.
102 --
103 -- Post Failure:
104 -- If an error has occurred, an error message will be raised.
105 --
106 -- Developer Implementation Notes:
107 -- None
108 --
109 -- Access Status:
110 -- Internal Row Handler Use Only.
111 --
112 -- {End Of Comments}
113 -- ----------------------------------------------------------------------------
114 Procedure chk_version_definition (
115 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
116 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
117 ,p_activity_version_id in ota_training_plan_members.activity_version_id%TYPE
118 ,p_activity_definition_id in ota_training_plan_members.activity_definition_id%TYPE
119 ,p_business_group_id in ota_training_plan_members.business_group_id%TYPE
120 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
121 );
122 -- ----------------------------------------------------------------------------
123 -- |----------------------<chk_activity_version_id>---------------------------|
124 -- ----------------------------------------------------------------------------
125 -- {Start Of Comments}
126 --
127 -- Description:
128 -- Validates that the activity_version_id :
129 -- Exists and is in the same business group as the member record
130 -- Is within the date range of the plan
131 -- Has no non-cancelled members in the plan that are superceeding this member.
132 --
133 -- Prerequisites:
134 -- Call chk_version_definition first.
135 --
136 -- In Parameters:
137 -- p_training_plan_member_id
138 -- p_object_version_number
139 -- p_activity_version_id
140 -- p_business_group_id
141 -- p_training_plan_id
142 --
143 -- Post Success:
144 -- Processing continues if the values are legal.
145 --
146 -- Post Failure:
147 -- If an error has occurred, an error message will be raised.
148 --
149 -- Developer Implementation Notes:
150 -- None
151 --
152 -- Access Status:
153 -- Internal Row Handler Use Only.
154 --
155 -- {End Of Comments}
156 -- ----------------------------------------------------------------------------
157 Procedure chk_activity_version_id (
158 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
159 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
160 ,p_activity_version_id in ota_training_plan_members.activity_version_id%TYPE
161 ,p_business_group_id in ota_training_plan_members.business_group_id%TYPE
162 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
163 );
164 -- ----------------------------------------------------------------------------
165 -- |----------------------<chk_member_status_type_id>-------------------------|
166 -- ----------------------------------------------------------------------------
167 -- {Start Of Comments}
168 --
169 -- Description:
170 -- Validates that the member_status exists in hr_lookups.
171 -- An Activity Version whose status is not cancelled cannot have a
172 -- parent activity definition whose status is not cancelled, in the same plan.
173 -- An Activity Definition whose status is not cancelled cannot have a
174 -- child activity version whose status is not cancelled, in the same plan.
175 --
176 --
177 -- Prerequisites:
178 --
179 --
180 -- In Parameters:
181 -- p_effective_date
182 -- p_member_status_type_id
183 -- p_business_group_id
184 -- p_training_plan_member_id
185 -- p_object_version_number
186 -- p_activity_version_id
187 -- p_activity_definition_id
188 -- p_training_plan_id
189 --
190 -- Post Success:
191 -- Processing continues if the values are legal.
192 --
193 -- Post Failure:
194 -- If an error has occurred, an error message will be raised.
195 --
196 -- Developer Implementation Notes:
197 -- None
198 --
199 -- Access Status:
200 -- Internal Row Handler Use Only.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure chk_member_status_type_id (
205 p_effective_date in date
206 ,p_member_status_type_id in ota_training_plan_members.member_status_type_id%TYPE
207 ,p_business_group_id in ota_training_plan_members.business_group_id%TYPE
208 ,p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
209 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
210 ,p_activity_version_id in ota_training_plan_members.activity_version_id%TYPE
211 ,p_activity_definition_id in ota_training_plan_members.activity_definition_id%TYPE
212 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
213 ,p_target_completion_date in ota_training_plan_members.target_completion_date%type
214 );
215
216 -- ----------------------------------------------------------------------------
217 -- |----------------------<chk_unique1>----------------------------------------|
218 -- ----------------------------------------------------------------------------
219 -- {Start Of Comments}
220 --
221 -- Description:
222 -- A procedure to perform the common functions of
223 -- chk_activity_version_id
224 -- chk_activity_definition_id
225 -- chk_training_plan_id
226 -- The values will first be checked by their respective chk_procedures.
227 -- Validates that the column combination is unique
228 --
229 -- Prerequisites:
230 -- chk_ procedures listed above have already been called
231 --
232 -- In Parameters:
233 -- p_training_plan_member_id
234 -- p_object_version_number
235 -- p_activity_version_id
236 -- p_activity_definition_id
237 -- p_training_plan_id
238 --
239 -- Post Success:
240 -- Processing continues if the values are legal.
241 --
242 -- Post Failure:
243 -- If an error has occurred, an error message will be raised.
244 --
245 -- Developer Implementation Notes:
246 -- This may be called from a form to validate that the combination is unique.
247 -- Call only on insert, as all columns are non-updateable.
248 --
249 -- Access Status:
250 -- Internal Development Use Only.
251 --
252 -- {End Of Comments}
253 -- ----------------------------------------------------------------------------
254 Procedure chk_unique1 (
255 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
256 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
257 ,p_activity_definition_id in ota_training_plan_members.activity_definition_id%TYPE
258 ,p_activity_version_id in ota_training_plan_members.activity_version_id%TYPE
259 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
260 ,p_target_completion_date in ota_training_plan_members.target_completion_date%TYPE
261 );
262 -- ----------------------------------------------------------------------------
263 -- |----------------------<chk_unique>----------------------------------------|
264 -- ----------------------------------------------------------------------------
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- A procedure to perform the common functions of
269 -- chk_activity_version_id
270 -- chk_activity_definition_id
271 -- chk_training_plan_id
272 -- The values will first be checked by their respective chk_procedures.
273 -- Validates that the column combination is unique
274 --
275 -- Prerequisites:
276 -- chk_ procedures listed above have already been called
277 --
278 -- In Parameters:
279 -- p_training_plan_member_id
280 -- p_object_version_number
281 -- p_activity_version_id
282 -- p_activity_definition_id
283 -- p_training_plan_id
284 --
285 -- Post Success:
286 -- Processing continues if the values are legal.
287 --
288 -- Post Failure:
289 -- If an error has occurred, an error message will be raised.
290 --
291 -- Developer Implementation Notes:
292 -- This may be called from a form to validate that the combination is unique.
293 -- Call only on insert, as all columns are non-updateable.
294 --
295 -- Access Status:
296 -- Internal Development Use Only.
297 --
298 -- {End Of Comments}
299 -- ----------------------------------------------------------------------------
300 Procedure chk_unique (
301 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
302 ,p_object_version_number in ota_training_plan_members.object_version_number%TYPE
303 ,p_activity_definition_id in ota_training_plan_members.activity_definition_id%TYPE
304 ,p_activity_version_id in ota_training_plan_members.activity_version_id%TYPE
305 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
306 );
307 -- ----------------------------------------------------------------------------
311 --
308 -- |----------------------<chk_delete>-----------------------------------------|
309 -- ----------------------------------------------------------------------------
310 -- {Start Of Comments}
312 -- Description:
313 -- Checks that no records exist in per_budget_elements for the training_plan_member
314 -- that is about to be deleted
315 --
316 -- Prerequisites:
317 --
318 --
319 -- In Parameters:
320 -- p_training_plan_member_id
321 -- p_training_plan_id
322 --
323 -- Post Success:
324 -- Processing continues if the member can be deleted
325 --
326 -- Post Failure:
327 -- If an error has occurred, an error message will be raised.
328 --
329 -- Developer Implementation Notes:
330 -- None
331 --
332 -- Access Status:
333 -- Internal Row Handler Use Only.
334 --
335 -- {End Of Comments}
336 -- ----------------------------------------------------------------------------
337 Procedure chk_delete (
338 p_training_plan_member_id in ota_training_plan_members.training_plan_member_id%TYPE
339 ,p_training_plan_id in ota_training_plan_members.training_plan_id%TYPE
340 );
341
342 -- ----------------------------------------------------------------------------
343 -- |----------------------<chk_source_function>-----------------------------------------|
344 -- ----------------------------------------------------------------------------
345 -- {Start Of Comments}
346 --
347 -- Description:
348 -- Checks that source_function exists in hr_lookups
349 --
350 -- Prerequisites:
351 --
352 --
353 -- In Parameters:
354 -- p_effective_date
355 -- p_training_plan_member_id
356 --
357 -- Post Success:
358 -- Processing continues if the value is legal.
359 --
360 -- Post Failure:
361 -- If an error has occurred, an error message will be raised.
362 --
363 -- Developer Implementation Notes:
364 -- None
365 --
366 -- Access Status:
367 -- Internal Row Handler Use Only.
368 --
369 -- {End Of Comments}
370 -- ----------------------------------------------------------------------------
371
372
373 Procedure chk_source_function
374 (p_training_plan_member_id in number
375 ,p_source_function in varchar2
376 ,p_effective_date in date);
377
378
379
380 -- ----------------------------------------------------------------------------
381 -- |----------------------<chk_cancellation_reason>-----------------------------------------|
382 -- ----------------------------------------------------------------------------
383 -- {Start Of Comments}
384 --
385 -- Description:
386 -- Checks that cancellation_reason exists in hr_lookups
387 --
388 -- Prerequisites:
389 --
390 --
391 -- In Parameters:
392 -- p_effective_date
393 -- p_training_plan_member_id
394 --
395 -- Post Success:
396 -- Processing continues if the value is legal.
397 --
398 -- Post Failure:
399 -- If an error has occurred, an error message will be raised.
400 --
401 -- Developer Implementation Notes:
402 -- None
403 --
404 -- Access Status:
405 -- Internal Row Handler Use Only.
406 --
407 -- {End Of Comments}
408 -- ----------------------------------------------------------------------------
409
410
411 Procedure chk_cancellation_reason
412 (p_training_plan_member_id in number
413 ,p_cancellation_reason in varchar2
414 ,p_effective_date in date);
415
416 -- ----------------------------------------------------------------------------
417 -- |----------------------<chk_tpc_tp_actver_dates>-----------------------------------------|
418 -- ----------------------------------------------------------------------------
419 -- {Start Of Comments}
420 --
421 -- Description:
422 -- Checks that Training Plan Component dates lie well within the Training plan dates and also
423 -- within the Activity Version Id dates
424 --
425 -- Prerequisites:
426 --
427 --
428 -- In Parameters:
429 -- p_earliest_start_date
430 -- p_target_completion_date
431 -- p_object_version_number
432 -- p_training_plan_member_id
433 --
434 -- Post Success:
435 -- Processing continues if the the dates are legal.
436 --
437 -- Post Failure:
438 -- If an error has occurred, an error message will be raised.
439 --
440 -- Developer Implementation Notes:
441 -- None
442 --
443 -- Access Status:
444 -- Internal Row Handler Use Only.
445 --
446 -- {End Of Comments}
447 -- ----------------------------------------------------------------------------
448
449
450 Procedure chk_tpc_tp_actver_dates(p_training_plan_id in ota_training_plans.training_plan_id%type
451 ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
452 ,p_activity_version_id in ota_training_plan_members.activity_version_id%type
453 ,p_earliest_start_date in ota_training_plan_members.earliest_start_date%TYPE
454 ,p_target_completion_date in ota_training_plan_members.target_completion_date%type
455 ,p_object_version_number in ota_training_plan_members.object_version_number%type);
456
457
458
459
460
461 end ota_tpm_bus1;
[Home] [Help]