[Home] [Help]
PACKAGE BODY: APPS.OTA_LRNR_ENROLL_UNENROLL_WF
Source
1 PACKAGE BODY OTA_LRNR_ENROLL_UNENROLL_WF AS
2 /* $Header: otaenrwf.pkb 120.6 2007/01/04 17:36:15 sschauha noship $ */
3
4 g_package VARCHAR2(33) := 'OTA_LRNR_ENROLL_UNENROLL_WF';
5
6 Procedure Cert_Enrollment(p_process in wf_process_activities.process_name%type,
7 p_itemtype in wf_items.item_type%type,
8 p_person_id in number ,
9 p_certificationid in ota_certifications_b.certification_id%type)
10
11 is
12 l_proc varchar2(72) := g_package||'Cert_Enrollment';
13 l_process wf_activities.name%type := upper(p_process);
14 l_item_type wf_items.item_type%type := upper(p_itemtype);
15 l_item_key wf_items.item_key%type;
16
17
18 l_cert_name ota_certifications_tl.name%type;
19 l_cert_comp_date ota_certifications_b.initial_completion_date%type;
20 l_cert_dur ota_certifications_b.initial_completion_duration%type;
21
22
23
24 l_process_display_name varchar2(240);
25
26 Cursor get_display_name is
27 SELECT wrpv.display_name displayName
28 FROM wf_runnable_processes_v wrpv
29 WHERE wrpv.item_type = p_itemtype
30 AND wrpv.process_name = p_process;
31
32
33 Cursor get_cert_details is
34 select ctl.name, ctb.initial_completion_date,ctb.initial_completion_duration
35 from ota_Certifications_tl ctl , ota_Certifications_b ctb
36 where ctl.certification_id = ctb.certification_id
37 and ctl.Language= USERENV('LANG')
38 and ctl.certification_id= p_certificationid;
39
40 begin
41
42 hr_utility.set_location('Entering:'||l_proc, 10);
43
44 -- Get the next item key from the sequence
45 select hr_workflow_item_key_s.nextval
46 into l_item_key
47 from sys.dual;
48
49 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
50
51 OPEN get_display_name;
52 FETCH get_display_name INTO l_process_display_name;
53 CLOSE get_display_name;
54
55 OPEN get_cert_details;
56 FETCH get_cert_details INTO
57 l_cert_name,
58 l_cert_comp_date,l_cert_dur;
59
60 CLOSE get_cert_details;
61
62 --set wf attributes
63 OTA_INITIALIZATION_WF.set_wf_item_attr(p_person_id => p_person_id,
64 p_item_type => l_item_type,
65 p_item_key => l_item_key);
66
67
68 -- Set wf attributes
69
70
71
72 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_DISPLAY_NAME', l_process_display_name);
73 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_NAME',l_process );
74 --hard coded date format required by pqh
75 WF_ENGINE.setitemattrtext(l_item_type, l_item_key,'P_EFFECTIVE_DATE',to_char(trunc(sysdate),'RRRR-MM-DD'));
76
77 WF_ENGINE.setitemattrDate(l_item_type, l_item_key,'CURRENT_EFFECTIVE_DATE',trunc(sysdate));
78
79
80
81 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','OTA');
82 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'HR_AME_APP_ID_ATTR', 810);
83
84 -- always set to NO for wf launched from pl/sql api
85 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_RUNTIME_APPROVAL_REQ_FLAG', 'NO');
86
87
88 WF_ENGINE.setitemattrnumber(l_item_type,
89 l_item_key,
90 'BOOKING_ID',
91 p_certificationid);
92
93
94
95 WF_ENGINE.setitemattrtext(l_item_type,
96 l_item_key,
97 'OTA_ACTIVITY_VERSION_NAME',
98 l_cert_name);
99
100
101 if l_cert_comp_date is null then
102 l_cert_comp_date := trunc(sysdate)+l_cert_dur;
103 end if;
104
105 WF_ENGINE.setitemattrtext(l_item_type,
106 l_item_key,
107 'OTA_COURSE_START_DATE',
108 l_cert_comp_date);
109
110 WF_ENGINE.setitemattrtext(l_item_type,
111 l_item_key,
112 'OTA_DELIVERY_MODE_NAME',
113 '');
114
115
116
117
118
119
120
121 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
122
123 hr_utility.set_location('leaving:'||l_proc, 20);
124 EXCEPTION
125 WHEN OTHERS THEN
126
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 -- Raise;
129
130
131
132 end Cert_Enrollment;
133
134
135 Procedure Learner_Enrollment(p_process in wf_process_activities.process_name%type,
136 p_itemtype in wf_items.item_type%type,
137 p_person_id in number ,
138 p_eventid in ota_Events.event_id%type,
139 p_booking_id in number)
140 is
141 l_proc varchar2(72) := g_package||'Learner_Enrollment';
142 l_process wf_activities.name%type := upper(p_process);
143 l_item_type wf_items.item_type%type := upper(p_itemtype);
144 l_item_key wf_items.item_key%type;
145
146 /*l_supervisor_id per_all_people_f.person_id%Type;
147 l_supervisor_username fnd_user.user_name%TYPE;
148 l_supervisor_full_name per_all_people_f.full_name%TYPE;
149
150 l_current_username fnd_user.user_name%TYPE;
151 l_creator_username fnd_user.user_name%TYPE;
152 l_current_displayname per_all_people_f.full_name%TYPE;
153 l_creator_displayname per_all_people_f.full_name%TYPE;
154 */
155 l_event_title ota_events.title%type;
156 l_course_start_date ota_events.course_start_date%type;
157 l_course_start_time ota_events.course_start_time%type;
158 l_course_end_date ota_events.course_end_date%type;
159 l_delivery_mode ota_category_usages_tl.category%type;
160 l_event_location hr_locations_all.location_code%TYPE;
161 l_version_name ota_activity_versions.version_name%type;
162
163 l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
164
165 l_return_status varchar2(1000);
166
167
168 l_process_display_name varchar2(240);
169 l_timezone fnd_timezones_tl.name%TYPE;
170 l_course_end_time ota_events.course_start_time%type;
171
172 Cursor get_display_name is
173 SELECT wrpv.display_name displayName
174 FROM wf_runnable_processes_v wrpv
175 WHERE wrpv.item_type = p_itemtype
176 AND wrpv.process_name = p_process;
177
178
179 Cursor csr_name is
180 select evt.title,oev.course_start_date,
181 oev.course_end_date, oev.course_start_time,
182 ctl.category,
183 ota_general.get_location_code(oev.location_id) Location_Name,
184 oav.version_name,ota_timezone_util.get_timezone_name(oev.timezone),oev.course_end_time
185 from ota_Events oev , ota_events_tl evt,ota_activity_versions_tl oav,
186 OTA_CATEGORY_USAGES_TL ctl,ota_offerings off
187 where oev.event_id = evt.event_id
188 and oev.parent_offering_id=off.offering_id
189 and off.activity_version_id= oav.activity_version_id
190 and ctl.Category_usage_id = off.delivery_mode_id
191 AND ctl.language = userenv('LANG')
192 and oev.event_id= p_eventid;
193
194 begin
195
196 hr_utility.set_location('Entering:'||l_proc, 10);
197
198 -- Get the next item key from the sequence
199 select hr_workflow_item_key_s.nextval
200 into l_item_key
201 from sys.dual;
202
203 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
204
205 OPEN get_display_name;
206 FETCH get_display_name INTO l_process_display_name;
207 CLOSE get_display_name;
208
209 OPEN csr_name;
210 FETCH csr_name INTO
211 l_event_title,
212 l_course_start_date,
213 l_course_end_date,
214 l_course_start_time,
215 l_delivery_mode,
216 l_event_location,l_version_name,l_timezone,l_course_end_time;
217
218 CLOSE csr_name;
219
220 --set wf attributes
221 OTA_INITIALIZATION_WF.set_wf_item_attr(p_person_id => p_person_id,
222 p_item_type => l_item_type,
223 p_item_key => l_item_key);
224 /*ota_learner_enroll_ss.get_wf_attr_for_cancel_ntf
225 (p_event_id => p_eventid,
226 p_person_id => p_delegate_person_id,
227 p_supervisor_username => l_supervisor_username,
228 p_supervisor_full_name => l_supervisor_full_name,
229 p_supervisor_id => l_supervisor_id,
230 p_current_person_name => l_current_username, --delegate person username
231 p_current_username => l_creator_username,-- creator username
232 p_person_displayname => l_current_displayname,
233 p_creator_displayname => l_creator_displayname,
234 x_return_status => l_return_status);
235 */
236
237 -- Set wf attributes
238
239
240
241 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_DISPLAY_NAME', l_process_display_name);
242 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'PROCESS_NAME',l_process );
243 --hard coded date format required by pqh
244 WF_ENGINE.setitemattrtext(l_item_type, l_item_key,'P_EFFECTIVE_DATE',to_char(trunc(sysdate),'RRRR-MM-DD'));
245
246 WF_ENGINE.setitemattrDate(l_item_type, l_item_key,'CURRENT_EFFECTIVE_DATE',trunc(sysdate));
247
248
249
250 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_AME_TRAN_TYPE_ATTR','OTA');
251 WF_ENGINE.setitemattrNumber(l_item_type, l_item_key, 'HR_AME_APP_ID_ATTR', 810);
252
253 -- always set to NO for wf launched from pl/sql api
254 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'HR_RUNTIME_APPROVAL_REQ_FLAG', 'NO');
255
256 hr_approval_wf.create_item_attrib_if_notexist
257 (p_item_type => l_item_type
258 ,p_item_key => l_item_key
259 ,p_name => 'OTA_EVENT_ID');
260
261 WF_ENGINE.setitemattrnumber(l_item_type,
262 l_item_key,
263 'OTA_EVENT_ID',
264 p_eventid);
265
266
267
268 WF_ENGINE.setitemattrtext(l_item_type,
269 l_item_key,
270 'OTA_ACTIVITY_VERSION_NAME',
271 l_version_name);
272
273
274
275 WF_ENGINE.setitemattrtext(l_item_type,
276 l_item_key,
277 'OTA_EVENT_TITLE',
278 p_eventid); --Enh 5606090: Language support for Event Details.
279
280 WF_ENGINE.setitemattrtext(l_item_type,
281 l_item_key,
282 'OTA_COURSE_START_DATE',
283 l_course_start_date);
284
285
286 WF_ENGINE.setitemattrtext(l_item_type,
287 l_item_key,
288 'OTA_COURSE_END_DATE',
289 l_course_end_date);
290
291 WF_ENGINE.setitemattrtext(l_item_type,
292 l_item_key,
293 'OTA_CLASS_START_TIME',
294 nvl(l_course_start_time,'00:00'));
295
296 WF_ENGINE.setitemattrtext(l_item_type,
297 l_item_key,
298 'OTA_DELIVERY_MODE_NAME',
299 l_delivery_mode);
300
301 WF_ENGINE.setitemattrtext(l_item_type,
302 l_item_key,
303 'OTA_LOCATION_ADDRESS',
304 l_event_location);
305
306 -- get enrollment status
307
308 l_enrollment_status_name := ota_utility.get_enrollment_status(p_delegate_person_id => p_person_id,
309 p_delegate_contact_id => null,
310 p_event_id => p_eventid,
311 p_code =>0);
312
313 WF_ENGINE.setitemattrText(l_item_type, l_item_key, 'ENROLL_IN_A_CLASS_STATUS', l_enrollment_status_name);
314
315 wf_engine.setItemAttrNumber(itemtype => l_item_type
316 ,itemkey => l_item_key
317 ,aname => 'BOOKING_ID'
318 ,avalue => p_booking_id);
319 wf_engine.setItemAttrText(l_item_type,l_item_key,'STATE_LIST',l_timezone );
320 /* hr_approval_wf.create_item_attrib_if_notexist
321 (p_item_type => l_item_type
322 ,p_item_key => l_item_key
323 ,p_name => 'OTA_CLASS_END_TIME');*/
324 wf_engine.setItemAttrText(l_item_type,l_item_key,'PQH_EVENT_NAME',nvl(l_course_end_time,'23:59'));
325
326 --p_itemkey := l_item_key;
327
328 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
329
330 hr_utility.set_location('leaving:'||l_proc, 20);
331 EXCEPTION
332 WHEN OTHERS THEN
333
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335 -- Raise;
336
337
338
339 end Learner_Enrollment;
340
341
342
343 Procedure Learner_UnEnrollment(p_process in wf_process_activities.process_name%type,
344 p_itemtype in wf_items.item_type%type,
345 p_person_id in number ,
346 p_eventid in ota_Events.event_id%type)
347
348 is
349 l_proc varchar2(72) := g_package||'Learner_UnEnrollment';
350 l_process wf_activities.name%type := upper(p_process);
351 l_item_type wf_items.item_type%type := upper(p_itemtype);
352 l_item_key wf_items.item_key%type;
353
354
355 l_event_title ota_events.title%type;
356 l_course_start_date ota_events.course_start_date%type;
357
358 l_version_name ota_activity_versions.version_name%type;
359
360 --l_enrollment_status_name ota_booking_status_types_tl.name%TYPE;
361
362
363
364
365 Cursor csr_name is
366 select evt.title,oev.course_start_date,
367 oav.version_name
368 from ota_Events oev , ota_events_tl evt,ota_activity_versions_tl oav
369 where oev.event_id = evt.event_id
370 and oev.activity_version_id= oav.activity_version_id
371 AND evt.language = userenv('LANG')
372 and oev.event_id= p_eventid;
373
374 begin
375
376 hr_utility.set_location('Entering:'||l_proc, 10);
377
378 -- Get the next item key from the sequence
379 select hr_workflow_item_key_s.nextval
380 into l_item_key
381 from sys.dual;
382
383 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
384
385 /*OPEN get_display_name;
386 FETCH get_display_name INTO l_process_display_name;
387 CLOSE get_display_name;
388 */
389 OPEN csr_name;
390 FETCH csr_name INTO
391 l_event_title,
392 l_course_start_date,
393 l_version_name;
394
395 CLOSE csr_name;
396
397 --set wf attributes
398 OTA_INITIALIZATION_WF.set_wf_item_attr(p_person_id => p_person_id,
399 p_item_type => l_item_type,
400 p_item_key => l_item_key);
401
402
403 -- Set wf attributes
404
405
406
407 WF_ENGINE.setitemattrtext(l_item_type,
408 l_item_key,
409 'OTA_ACTIVITY_VERSION_NAME',
410 l_version_name);
411
412
413
414 WF_ENGINE.setitemattrtext(l_item_type,
415 l_item_key,
416 'OTA_EVENT_TITLE',
417 p_eventid); --Enh 5606090: Language support for Event Details.
418
419 WF_ENGINE.setitemattrtext(l_item_type,
420 l_item_key,
421 'OTA_COURSE_START_DATE',
422 l_course_start_date);
423
424 WF_ENGINE.setitemattrtext(l_item_type,
425 l_item_key,
426 'HR_OAF_NAVIGATION_ATTR',
427 'N');
428
429
430 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
431
432 hr_utility.set_location('leaving:'||l_proc, 20);
433 EXCEPTION
434 WHEN OTHERS THEN
435
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 -- Raise;
438
439
440
441 end Learner_UnEnrollment;
442
443
444
445
446
447 end OTA_LRNR_ENROLL_UNENROLL_WF;
448
449