1 PACKAGE OTA_TRNG_PLAN_UTIL_SS AUTHID CURRENT_USER as
2 /* $Header: ottpswrs.pkh 115.8 2004/04/01 19:18:39 cmora noship $ */
3
4 -- global variable
5 g_is_per_trng_plan boolean := false;
6
7 -- ---------------------------------------------------------------------------
8 -- |----------------------< is_personal_trng_plan >--------------------------|
9 -- ---------------------------------------------------------------------------
10 -- {Start Of Comments}
11 --
12 -- Description:
13 -- Checks whether this is a Personal Training Plan
14 --
15 -- Prerequisites:
16 --
17 --
18 -- In Arguments:
19 -- None
20 --
21 -- Post Success:
22 -- 'TRUE' is returned for Personal Training Plan
23 -- 'FALSE' is returned for Organization Training Plan
24 -- Post Failure:
25 --
26 --
27 -- Access Status:
28 -- Internal Development Use Only.
29 --
30 -- {End Of Comments}
31 -- ---------------------------------------------------------------------------
32
33 FUNCTION is_personal_trng_plan
34 RETURN BOOLEAN;
35
36
37 -- ---------------------------------------------------------------------------
38 -- |----------------------< is_personal_trng_plan >--------------------------|
39 -- ---------------------------------------------------------------------------
40 -- {Start Of Comments}
41 --
42 -- Description:
43 -- Checks whether this is a Personal Training Plan
44 --
45 -- Prerequisites:
46 --
47 --
48 -- In Arguments:
49 -- p_training_plan_id
50 --
51 --
52 -- Post Success:
53 -- 'TRUE' is returned for Personal Training Plan
54 -- 'FALSE' is returned for Organization Training Plan
55 -- Post Failure:
56 --
57 --
58 -- Access Status:
59 -- Internal Development Use Only.
60 --
61 -- {End Of Comments}
62 -- ---------------------------------------------------------------------------
63
64 FUNCTION is_personal_trng_plan ( p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
65 RETURN BOOLEAN;
66
67 -- ---------------------------------------------------------------------------
68 -- |----------------------< chk_cancel_plan_ok >--------------------------|
69 -- ---------------------------------------------------------------------------
70 --
71 Function chk_cancel_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
72 return varchar2;
73 -- ---------------------------------------------------------------------------
74 -- |----------------------< chk_complete_plan_ok >--------------------------|
75 -- ---------------------------------------------------------------------------
76 --
77
78 Function chk_complete_plan_ok(p_training_plan_id in ota_training_plans.training_plan_id%type)
79 return varchar2;
80 -- ---------------------------------------------------------------------------
81 -- |----------------------< get_enroll_status >--------------------------|
82 -- ---------------------------------------------------------------------------
83 -- {Start Of Comments}
84 --
85 -- Description:
86 -- Returns the enrollment status of the event in the order A P W R Z
87 --
88 -- Prerequisites:
89 --
90 --
91 -- In Arguments:
92 -- p_training_plan_member_id
93 -- p_person_id
94 --
95 --
96 -- Post Success:
97 -- Enrollment status is returned to calling unit
98 --
99 -- Post Failure:
100 --
101 --
102 -- Access Status:
103 -- Internal Development Use Only.
104 --
105 -- {End Of Comments}
106 -- ---------------------------------------------------------------------------
107
108
109 FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
110 -- Modified for Bug#3479186
111 p_contact_id IN ota_training_plans.contact_id%TYPE,
112 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
113 RETURN VARCHAR2;
114
115 -- ---------------------------------------------------------------------------
116 -- |----------------------< chk_login_person >--------------------------|
117 -- ---------------------------------------------------------------------------
118 -- {Start Of Comments}
119 --
120 -- Description:
121 -- Checks whether the login person is Manager or employee
122 --
123 -- Prerequisites:
124 --
125 --
126 -- In Arguments:
127 -- p_training_plan_id
128 --
129 --
130 -- Post Success:
131 -- 'M' is returned for Manager
132 -- 'E' is returned for employee
133 -- Post Failure:
134 --
135 --
136 -- Access Status:
137 -- Internal Development Use Only.
138 --
139 -- {End Of Comments}
140 -- ---------------------------------------------------------------------------
141
142 FUNCTION chk_login_person ( p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
143 RETURN VARCHAR2;
144
145 -- ---------------------------------------------------------------------------
146 -- |----------------------< chk_src_func_tlntmgt >--------------------------|
147 -- ---------------------------------------------------------------------------
148 -- {Start Of Comments}
149 --
150 -- Description:
151 -- checks if plan_source of type TALENT MANAGMENT already exists for the
152 -- Training plan member being added (based on it's dates)
153 --
154 -- Prerequisites:
155 --
156 -- In Arguments:
157 -- p_person_id
158 -- p_earliest_start_date
159 -- p_target_completion_date
160 -- p_business_group_id
161 --
162 -- Post Success:
163 -- returns the required training plan id number
164 --
165 -- Post Failure:
166 --
167 --
168 -- Access Status:
169 -- Internal Development Use Only.
170 --
171 -- {End Of Comments}
172 -- ---------------------------------------------------------------------------
173
174 FUNCTION chk_src_func_tlntmgt(p_person_id IN ota_training_plans.person_id%TYPE,
175 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
176 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
177 --Added for Bug#3108246
178 p_business_group_id IN number)
179 RETURN number ;
180
181 -- ---------------------------------------------------------------------------
182 -- |----------------------< chk_valid_act_version_dates >--------------------------|
183 -- ---------------------------------------------------------------------------
184 -- {Start Of Comments}
185 --
186 -- Description:
187 -- checks when the activity dates are changed there are no training plan members getting affected
188 -- called from ota_tav_bus.update_validate
189 -- Prerequisites:
190 --
191 --
192 -- In Arguments:
193 -- p_activity_version_id
194 -- p_start_date
195 -- p_end_date
196 --
197 -- Post Success:
198 -- Activity dates are allowed to be changed
199 --
200 -- Post Failure:
201 -- Error is thrown
202 --
203 -- Access Status:
204 -- Internal Development Use Only.
205 --
206 -- {End Of Comments}
207 -- ---------------------------------------------------------------------------
208
209 PROCEDURE chk_valid_act_version_dates (p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
210 p_start_date IN ota_activity_versions.start_date%TYPE,
211 p_end_date IN ota_activity_versions.end_date%TYPE DEFAULT NULL);
212
213 -- ---------------------------------------------------------------------------
214 -- |----------------------< chk_enrollment_exist >--------------------------|
215 -- ---------------------------------------------------------------------------
216 -- {Start Of Comments}
217 --
218 -- Description:
219 -- Checks if there are any enrollments for the person
220 -- for the training plan member
221 --
222 -- Prerequisites:
223 --
224 --
225 -- In Arguments:
226 -- p_person_id
227 -- p_training_plan_member_id
228 --
229 -- Post Success:
230 -- Enrollment icon is either enabled or disabled
231 --
232 -- Post Failure:
233 --
234 -- Access Status:
235 -- Internal Development Use Only.
236 --
237 -- {End Of Comments}
238 -- ---------------------------------------------------------------------------
239 FUNCTION chk_enrollment_exist ( p_person_id IN ota_training_plans.person_id%TYPE,
240 -- Modified for Bug#3479186
241 p_contact_id IN ota_training_plans.contact_id%TYPE,
242 p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE)
243 RETURN boolean;
244
245 -- ---------------------------------------------------------------------------
246 -- |----------------------< get_enroll_status >--------------------------|
247 -- ---------------------------------------------------------------------------
248 -- {Start Of Comments}
249 --
250 -- Description:
251 -- Returns the enrollment status of the event in the order A P W R Z
252 -- Overloaded function.Called while creating a TPC with member_status_TYPE_ID
253 -- 'OTA_PLANNED' to determine the exact ststus of TPC based on enrollments falling under it
254 --
255 -- Prerequisites:
256 --
257 --
258 -- In Arguments:
259 -- p_earliest_start_date
260 -- p_target_completion_date
261 -- p_person_id
262 -- p_training_plan_id
263 -- p_action
264 --
265 --
266 -- Post Success:
267 -- Enrollment status is returned to calling unit
268 --
269 -- Post Failure:
270 --
271 --
272 -- Access Status:
273 -- Internal Development Use Only.
274 --
275 -- {End Of Comments}
276 -- ---------------------------------------------------------------------------
277
278 FUNCTION get_enroll_status(p_person_id IN ota_training_plans.person_id%TYPE,
279 -- Modified for Bug#3479186
280 p_contact_id IN ota_training_plans.contact_id%TYPE,
281 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
282 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
283 p_activity_version_id IN ota_training_plan_members.activity_version_id%TYPE,
284 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
285 p_action IN VARCHAR2)
286 RETURN varchar2 ;
287
288 -- ---------------------------------------------------------------------------
289 -- |----------------------< modify_tpc_status_on_create >--------------------------|
290 -- ---------------------------------------------------------------------------
291 -- {Start Of Comments}
292 --
293 -- Description:
294 -- Returns the member_status_type_id
295 -- Called while creating a TPC with member_status_TYPE_ID
296 -- 'OTA_PLANNED' to determine the exact ststus of TPC based on enrollments falling under it
297 --
298 -- Prerequisites:
299 --
300 --
301 -- In Arguments:
302 -- p_earliest_start_date
303 -- p_target_completion_date
304 -- p_person_id
305 -- p_activity_version_id
306 -- p_training_plan_id
307 --
308 --
309 -- Post Success:
310 -- Member status is returned to calling unit
311 --
312 -- Post Failure:
313 --
314 --
315 -- Access Status:
316 -- Internal Development Use Only.
317 --
318 -- {End Of Comments}
319 -- ---------------------------------------------------------------------------
320
321 PROCEDURE modify_tpc_status_on_create(--p_person_id IN ota_training_plans.person_id%TYPE,
322 -- Modified for Bug#3479186
323 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
324 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
325 p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
326 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
327 p_member_status_id OUT nocopy VARCHAR2);
328
329 -- ---------------------------------------------------------------------------
330 -- |----------------------< modify_tpc_status_on_update >--------------------------|
331 -- ---------------------------------------------------------------------------
332 -- {Start Of Comments}
333 --
334 -- Description:
335 -- Returns the member_status_type_id
336 -- Called while creating a TPC with member_status_TYPE_ID
337 -- not equal 'OTA_CANCELLED' to determine the exact ststus of TPC based on enrollments falling under it
338 --
339 -- Prerequisites:
340 --
341 --
342 -- In Arguments:
343 -- p_earliest_start_date
344 -- p_target_completion_date
345 -- p_person_id
346 -- p_activity_version_id
347 -- p_training_plan_id
348 --
349 --
350 -- Post Success:
351 -- Member status is returned to calling unit
352 --
353 -- Post Failure:
354 --
355 --
356 -- Access Status:
357 -- Internal Development Use Only.
358 --
359 -- {End Of Comments}
360 -- ---------------------------------------------------------------------------
361
362 PROCEDURE modify_tpc_status_on_update(--p_person_id IN ota_training_plans.person_id%TYPE,
363 -- Modified for Bug#3479186
364 p_earliest_start_date IN ota_training_plan_members.earliest_start_date%TYPE,
365 p_target_completion_date IN ota_training_plan_members.target_completion_date%TYPE,
366 p_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
367 p_training_plan_id IN ota_training_plans.training_plan_id%TYPE,
368 p_member_status_id OUT nocopy VARCHAR2);
369
370 -- ----------------------------------------------------------------------------
371 -- |----------------------<get_person_id>-----------------------------------------|
372 -- ----------------------------------------------------------------------------
373 -- {Start Of Comments}
374 --
375 -- Description:
376 -- Returns the person_id associated with the training plan
377 --
378 -- Prerequisites:
379 --
380 --
381 -- In Parameters:
382 --
383 -- p_training_plan_id
384 --
385 -- Post Success:
386 -- Processing continues if the the dates are legal.
387 --
388 -- Post Failure:
389 -- If an error has occurred, an error message will be raised.
390 --
391 -- Developer Implementation Notes:
392 -- None
393 --
394 -- Access Status:
395 -- Internal Row Handler Use Only.
396 --
397 -- {End Of Comments}
398 -- ----------------------------------------------------------------------------
399
400 FUNCTION get_person_id(p_training_plan_id IN ota_training_plans.training_plan_id%TYPE)
401 RETURN number;
402
403 -- ----------------------------------------------------------------------------
404
405 -- ---------------------------< get_valid_enroll >-----------------------------
406 -- ----------------------------------------------------------------------------
407
408 PROCEDURE get_valid_enroll(p_person_id IN ota_training_plans.person_id%TYPE
409 -- Modified for Bug#3479186
410 ,p_contact_id IN ota_training_plans.contact_id%TYPE
411 ,p_training_plan_member_id IN ota_training_plan_members.training_plan_member_id%TYPE
412 ,p_return_status OUT nocopy varchar2);
413
414
415 -- ---------------------------------------------------------------------------
416 -- |----------------------< complete_plan >--------------------------|
417 -- ---------------------------------------------------------------------------
418 --
419
420 PROCEDURE complete_plan(p_training_plan_id in ota_training_plans.training_plan_id%type);
421 -- ---------------------------------------------------------------------------
422
423
424 END ota_trng_plan_util_ss;
[Home] [Help]