[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_WF_001
Source
1 PACKAGE BODY igs_ad_wf_001 AS
2 /* $Header: IGSADC0B.pls 120.6 2006/05/24 09:38:30 arvsrini ship $ */
3
4
5 PROCEDURE wf_adm_academic_index (
6 p_person_id IN NUMBER,
7 p_admission_appl_number IN NUMBER DEFAULT NULL,
8 p_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
9 p_sequence_number IN NUMBER DEFAULT NULL,
10 p_old_academic_index IN VARCHAR2 DEFAULT NULL,
11 p_new_academic_index IN VARCHAR2 DEFAULT NULL
12 ) IS
13 /*
14 || Created By : rboddu
15 || Created On : 10-OCT-2003
16 || Purpose : To raise the Academic Index business event. Financial Aid Integration build. Bug:3202866
17 || Known limitations, enhancements or remarks :
18 || Change History :
19 || Who When What
20 || || (reverse chronological order - newest change first)
21 */
22
23 CURSOR cur_prof_value IS
24 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
25 FROM dual;
26
27 -- Get a unique sequence number
28 CURSOR c_seq_num IS
29 SELECT igs_ad_acadindex_wf_s.NEXTVAL
30 FROM sys.dual;
31 ln_seq_val NUMBER;
32
33 -- Get Person number and full name.
34 CURSOR get_person_attr IS
35 SELECT person_number, full_name
36 FROM igs_pe_person_base_v
37 WHERE person_id = p_person_id;
38
39 CURSOR get_application_id IS
40 SELECT application_id
41 FROM igs_ad_appl
42 WHERE person_id = p_person_id
43 AND admission_appl_number = p_admission_appl_number;
44
45 CURSOR cur_user_name IS
46 SELECT user_name
47 FROM fnd_user
48 WHERE user_id = fnd_global.user_id;
49
50
51 l_cur_prof_value cur_prof_value%ROWTYPE;
52 lv_raise_event VARCHAR2(100);
53 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
54 l_person_number igs_pe_person_base_v.person_number%TYPE;
55 l_person_name igs_pe_person_base_v.full_name%TYPE;
56 l_application_id igs_ad_appl_all.application_id%TYPE;
57 l_wf_role fnd_user.user_name%TYPE;
58
59
60 BEGIN
61 -- Checking if the Workflow is installed at the environment or not.
62 OPEN cur_prof_value;
63 FETCH cur_prof_value INTO l_cur_prof_value;
64 CLOSE cur_prof_value;
65
66 IF (l_cur_prof_value.value = 'Y') THEN
67
68 -- Get the sequence value
69 OPEN c_seq_num;
70 FETCH c_seq_num INTO ln_seq_val ;
71 CLOSE c_seq_num ;
72
73 OPEN get_person_attr;
74 FETCH get_person_attr INTO l_person_number, l_person_name;
75 CLOSE get_person_attr;
76
77 OPEN get_application_id;
78 FETCH get_application_id INTO l_application_id;
79 CLOSE get_application_id;
80
81 OPEN cur_user_name;
82 FETCH cur_user_name INTO l_wf_role;
83 CLOSE cur_user_name;
84
85 lv_raise_event := 'oracle.apps.igs.ad.acadindex.changed';
86
87 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => p_person_id , p_parameterlist=> l_parameter_list);
88 wf_event.AddParameterToList(p_name => 'P_PERSON_NUMBER' , p_value => l_person_number , p_parameterlist=> l_parameter_list);
89 wf_event.AddParameterToList(p_name => 'P_NEW_ACADEMIC_INDEX' , p_value => p_new_academic_index , p_parameterlist => l_parameter_list);
90 wf_event.AddParameterToList(p_name => 'P_OLD_ACADEMIC_INDEX' , p_value => p_old_academic_index , p_parameterlist => l_parameter_list);
91 wf_event.AddParameterToList(p_name => 'P_PERSON_NAME' , p_value => l_person_name , p_parameterlist=> l_parameter_list);
92 wf_event.AddParameterToList(p_name => 'P_APPLICATION_ID' , p_value => l_application_id , p_parameterlist=> l_parameter_list);
93 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => p_nominated_course_cd , p_parameterlist => l_parameter_list);
94 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => p_admission_appl_number , p_parameterlist => l_parameter_list);
95 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => p_sequence_number , p_parameterlist => l_parameter_list);
96 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
97
98 -- Raise the event
99 wf_event.raise( p_event_name => lv_raise_event, p_event_key => ln_seq_val, p_parameters => l_parameter_list);
100 l_parameter_list.DELETE;
101 END IF;
102 EXCEPTION
103 WHEN OTHERS THEN
104 WF_CORE.CONTEXT('IGS_AD_WF_001', 'wf_adm_academic_index',
105 'IGSADIDX'||ln_seq_val,lv_raise_event);
106 raise;
107
108 END wf_adm_academic_index;
109
110
111
112 PROCEDURE wf_raise_event(
113 p_person_id IN NUMBER,
114 p_raised_for IN VARCHAR2,
115 p_admission_appl_number IN NUMBER DEFAULT NULL,
116 p_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
117 p_sequence_number IN NUMBER DEFAULT NULL,
118 p_old_outcome_status IN VARCHAR2 DEFAULT NULL,
119 p_new_outcome_status IN VARCHAR2 DEFAULT NULL
120 ) AS
121 /*
122 || Created By : vdixit
123 || Created On : 16-Feb-2002
124 || Purpose : To raise the business events in work flow
125 || Known limitations, enhancements or remarks :
126 || Change History :
127 || Who When What
128 || rboddu 15-OCT-2003 Modified signature of procedure for build:
129 || (reverse chronological order - newest change first)
130 */
131
132 ln_seq_val NUMBER;
133 lv_raise_event VARCHAR2(30);
134 lv_item_type VARCHAR2(100) ;
135 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
136 l_person_number igs_pe_person_base_v.person_number%TYPE;
137 l_person_name igs_pe_person_base_v.full_name%TYPE;
138 l_application_id igs_ad_appl_all.application_id%TYPE;
139 l_wf_role fnd_user.user_name%TYPE;
140
141
142 -- Get a unique sequence number
143 CURSOR c_seq_num IS
144 SELECT igs_ad_wf_req_submit_s.NEXTVAL
145 FROM sys.dual;
146
147 -- Get Person number and full name.
148 CURSOR get_person_attr IS
149 SELECT person_number, full_name
150 FROM igs_pe_person_base_v
151 WHERE person_id = p_person_id;
152
153 CURSOR get_application_id IS
154 SELECT application_id
155 FROM igs_ad_appl
156 WHERE person_id = p_person_id
157 AND admission_appl_number = p_admission_appl_number;
158
159 CURSOR cur_user_name IS
160 SELECT user_name
161 FROM fnd_user
162 WHERE user_id = fnd_global.user_id;
163
164 BEGIN
165
166 IF p_raised_for ='MAC' THEN --Manual application creation in IGSAD046
167 lv_raise_event := 'oracle.apps.igs.pe.rescal.ac';
168 lv_item_type := 'IGSAD001' ;
169 ELSIF p_raised_for = 'SAC' THEN --Self Service Application creation
170 lv_raise_event := 'oracle.apps.igs.pe.rescal.ss';
171 lv_item_type := 'IGSAD002';
172 ELSIF p_raised_for = 'IAC' THEN --Import Application creation
173 lv_raise_event := 'oracle.apps.igs.pe.rescal.ai';
174 lv_item_type := 'IGSAD003';
175 ELSIF p_raised_for = 'AOD' THEN --Admission outcome decision in IGSAD046
176 lv_raise_event := 'oracle.apps.igs.pe.rescal.os';
177 lv_item_type := 'IGSADOUD';
178 ELSIF p_raised_for = 'IOD' THEN --Import outcome decision in IGSAD081
179 lv_raise_event := 'oracle.apps.igs.pe.rescal.io';
180 lv_item_type := 'IGSADOUD';
181 END IF;
182
183 -- Get the sequence value
184 OPEN c_seq_num;
185 FETCH c_seq_num INTO ln_seq_val ;
186 CLOSE c_seq_num ;
187
188 --If the Outcome Status is getting updated then pass Additional parameters to the Business event, to identify the Application Instance
189 --Old and New Outcome statuses. Financial Aid Integration build -
190 IF p_raised_for = 'AOD' OR p_raised_for = 'IOD' THEN
191
192 OPEN get_person_attr;
193 FETCH get_person_attr INTO l_person_number, l_person_name;
194 CLOSE get_person_attr;
195
196 OPEN get_application_id;
197 FETCH get_application_id INTO l_application_id;
198 CLOSE get_application_id;
199
200 OPEN cur_user_name;
201 FETCH cur_user_name INTO l_wf_role;
202 CLOSE cur_user_name;
203
204 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => p_person_id , p_parameterlist=> l_parameter_list);
205 wf_event.AddParameterToList(p_name => 'P_PERSON_NUMBER' , p_value => l_person_number , p_parameterlist=> l_parameter_list);
206 wf_event.AddParameterToList(p_name => 'P_NEW_OUTCOME_STATUS' , p_value => p_new_outcome_status , p_parameterlist => l_parameter_list);
207 wf_event.AddParameterToList(p_name => 'P_OLD_OUTCOME_STATUS' , p_value => p_old_outcome_status , p_parameterlist => l_parameter_list);
208 wf_event.AddParameterToList(p_name => 'P_PERSON_NAME' , p_value => l_person_name , p_parameterlist=> l_parameter_list);
209 wf_event.AddParameterToList(p_name => 'P_APPLICATION_ID' , p_value => l_application_id , p_parameterlist=> l_parameter_list);
210 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => p_nominated_course_cd , p_parameterlist => l_parameter_list);
211 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => p_admission_appl_number , p_parameterlist => l_parameter_list);
212 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => p_sequence_number , p_parameterlist => l_parameter_list);
213 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
214 ELSIF p_raised_for = 'SAC' THEN
215 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => p_person_id , p_parameterlist=> l_parameter_list);
216 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => p_admission_appl_number , p_parameterlist => l_parameter_list);
217 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => p_nominated_course_cd , p_parameterlist => l_parameter_list);
218 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => p_sequence_number , p_parameterlist => l_parameter_list);
219 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
220 ELSE
221 wf_event.AddParameterToList(p_name => 'IA_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist => l_parameter_list);
222 END IF;
223
224 -- Raise the event
225 wf_event.raise( p_event_name => lv_raise_event,
226 p_event_key => ln_seq_val,
227 p_parameters => l_parameter_list);
228 l_parameter_list.DELETE;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 WF_CORE.CONTEXT('IGS_AD_WF_001', 'WF_RAISE_EVENT',
233 lv_item_type||ln_seq_val,lv_raise_event);
234 raise;
235
236 END wf_raise_event;
237
238
239 PROCEDURE transcript_entrd_event(
240 p_person_id IN NUMBER,
241 p_education_id IN NUMBER,
242 p_transcript_id IN NUMBER
243 ) AS
244 /*
245 || Created By : pbondugu
246 || Created On : 29-Sep-2003
247 || Purpose : To raise the business events in work flow
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253
254 ln_seq_val NUMBER;
255 lv_raise_event VARCHAR2(50);
256 lv_item_type VARCHAR2(100) ;
257 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
258
259
260 -- Get a unique sequence number
261 CURSOR
262 c_seq_num
263 IS
264 SELECT
265 IGS_AD_WF_TXCPT_S.NEXTVAL
266 FROM SYS.DUAL;
267
268
269 BEGIN
270
271 lv_raise_event := 'oracle.apps.igs.ad.appl.mult_trns';
272 -- Get the sequence value
273 OPEN c_seq_num;
274 FETCH c_seq_num INTO ln_seq_val ;
275 CLOSE c_seq_num ;
276
277 wf_event.AddParameterToList(p_name => 'P_PERSON_ID',
278 p_value => P_PERSON_ID,
279 p_parameterlist=> l_parameter_list);
280
281 wf_event.AddParameterToList(p_name => 'P_EDUCATION_ID',
282 p_value => P_EDUCATION_ID,
283 p_parameterlist=> l_parameter_list);
284
285 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_ID',
286 p_value => P_TRANSCRIPT_ID,
287 p_parameterlist=> l_parameter_list);
288
289 -- Raise the event
290 WF_EVENT.RAISE( p_event_name => lv_raise_event,
291 p_event_key => ln_seq_val,
292 p_parameters => l_parameter_list);
293 l_parameter_list.DELETE;
294
295 -- Call the AV procedure to validate the Advanced Standing records when a new Transcript is submitted.
296 -- Added as part of the RECR050; Nalin Kumar; 14-Nov-2003;
297 igs_av_val_asuleb.validate_transcript(p_person_id, p_education_id, p_transcript_id);
298
299 EXCEPTION
300
301 WHEN OTHERS THEN
302 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TRANSCRIPT_ENTRD_EVENT',
303 ln_seq_val,lv_raise_event);
304 RAISE;
305
306 END transcript_entrd_event;
307
308 PROCEDURE TESTSCORE_CRT_EVENT
309 (
310 P_TEST_RESULTS_ID IN NUMBER,
311 P_PERSON_ID IN NUMBER,
312 P_ACTIVE_IND IN VARCHAR2
313 ) AS
314 -- Get a unique sequence number
315 CURSOR c_seq_num IS
316 SELECT igs_ad_wf_tstscr_crt_s.NEXTVAL
317 FROM sys.dual;
318
319 CURSOR cur_user_name IS
320 SELECT user_name
321 FROM fnd_user
322 WHERE user_id = fnd_global.user_id;
323
324 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
325 l_wf_role fnd_user.user_name%TYPE;
326 l_seq_val NUMBER;
327 lv_raise_event VARCHAR2(100);
328
329
330
331 BEGIN
332
333 OPEN cur_user_name;
334 FETCH cur_user_name INTO l_wf_role;
335 CLOSE cur_user_name;
336
337 OPEN c_seq_num;
338 FETCH c_seq_num INTO l_seq_val ;
339 CLOSE c_seq_num ;
340
341 lv_raise_event := 'oracle.apps.igs.ad.test_score';
342
343 wf_event.AddParameterToList(p_name => 'P_TEST_RESULTS_ID' , p_value => P_TEST_RESULTS_ID , p_parameterlist=> l_parameter_list);
344 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
345 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND' , p_value => P_ACTIVE_IND , p_parameterlist => l_parameter_list);
346 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
347
348 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
349 l_parameter_list.DELETE;
350
351 EXCEPTION
352 WHEN OTHERS THEN
353 IF cur_user_name%ISOPEN THEN
354 CLOSE cur_user_name;
355 END IF;
356 IF c_seq_num%ISOPEN THEN
357 CLOSE c_seq_num;
358 END IF;
359
360
361 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TESTSCORE_CRT_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
362 raise;
363
364
365
366 END TESTSCORE_CRT_EVENT;
367
368
369
370 PROCEDURE TESTSEG_CRT_EVENT
371 (
372 P_TEST_RESULTS_ID IN NUMBER,
373 P_TST_RSLT_DTLS_ID IN NUMBER,
374 P_TEST_SEGMENT_ID IN NUMBER,
375 P_PERSON_ID IN NUMBER
376 ) AS
377 -- Get a unique sequence number
378 CURSOR c_seq_num IS
379 SELECT igs_ad_wf_tstseg_crt_s.NEXTVAL
380 FROM sys.dual;
381
382 CURSOR cur_user_name IS
383 SELECT user_name
384 FROM fnd_user
385 WHERE user_id = fnd_global.user_id;
386
387 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
388 l_wf_role fnd_user.user_name%TYPE;
389 l_seq_val NUMBER;
390 lv_raise_event VARCHAR2(100);
391
392
393
394 BEGIN
395 -- Checking if the Workflow is installed at the environment or not.
396
397 OPEN cur_user_name;
398 FETCH cur_user_name INTO l_wf_role;
399 CLOSE cur_user_name;
400
401 OPEN c_seq_num;
402 FETCH c_seq_num INTO l_seq_val ;
403 CLOSE c_seq_num ;
404
405 lv_raise_event := 'oracle.apps.igs.ad.test_score_dtls';
406
407 wf_event.AddParameterToList(p_name => 'P_TEST_RESULTS_ID' , p_value => P_TEST_RESULTS_ID , p_parameterlist=> l_parameter_list);
408 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
409 wf_event.AddParameterToList(p_name => 'P_TST_RSLT_DTLS_ID' , p_value => P_TST_RSLT_DTLS_ID , p_parameterlist => l_parameter_list);
410 wf_event.AddParameterToList(p_name => 'P_TEST_SEGMENT_ID' , p_value => P_TEST_SEGMENT_ID , p_parameterlist => l_parameter_list);
411 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
412
413 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
414 l_parameter_list.DELETE;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF cur_user_name%ISOPEN THEN
419 CLOSE cur_user_name;
420 END IF;
421 IF c_seq_num%ISOPEN THEN
422 CLOSE c_seq_num;
423 END IF;
424
425 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TESTSEG_CRT_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
426 raise;
427 END TESTSEG_CRT_EVENT;
428
429
430
431 PROCEDURE TESTSCORE_UPD_EVENT
432 (
433 P_TEST_RESULTS_ID IN NUMBER,
434 P_PERSON_ID IN NUMBER,
435 P_ACTIVE_IND_NEW IN VARCHAR2,
436 P_ACTIVE_IND_OLD IN VARCHAR2,
437 P_ADMISSION_TEST_TYPE_NEW IN VARCHAR2,
438 P_ADMISSION_TEST_TYPE_OLD IN VARCHAR2,
439 P_GRADE_ID_NEW IN NUMBER,
440 P_GRADE_ID_OLD IN NUMBER,
441 P_COMP_TEST_SCORE_NEW IN NUMBER,
442 P_COMP_TEST_SCORE_OLD IN NUMBER
443 ) AS
444 -- Get a unique sequence number
445 CURSOR c_seq_num IS
446 SELECT igs_ad_wf_tstscr_upd_s.NEXTVAL
447 FROM sys.dual;
448
449 CURSOR cur_user_name IS
450 SELECT user_name
451 FROM fnd_user
452 WHERE user_id = fnd_global.user_id;
453
454 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
455 l_wf_role fnd_user.user_name%TYPE;
456 l_seq_val NUMBER;
457 lv_raise_event VARCHAR2(100);
458
459
460
461 BEGIN
462 -- Checking if the Workflow is installed at the environment or not.
463
464
465 OPEN cur_user_name;
466 FETCH cur_user_name INTO l_wf_role;
467 CLOSE cur_user_name;
468
469 OPEN c_seq_num;
470 FETCH c_seq_num INTO l_seq_val ;
471 CLOSE c_seq_num ;
472
473 lv_raise_event := 'oracle.apps.igs.ad.test_score.changed';
474
475 wf_event.AddParameterToList(p_name => 'P_TEST_RESULTS_ID' , p_value => P_TEST_RESULTS_ID , p_parameterlist=> l_parameter_list);
476 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
477 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND_NEW' , p_value => P_ACTIVE_IND_NEW , p_parameterlist => l_parameter_list);
478 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND_OLD' , p_value => P_ACTIVE_IND_OLD , p_parameterlist => l_parameter_list);
479 wf_event.AddParameterToList(p_name => 'P_ADMISSION_TEST_TYPE_NEW' , p_value => P_ADMISSION_TEST_TYPE_NEW , p_parameterlist => l_parameter_list);
480 wf_event.AddParameterToList(p_name => 'P_ADMISSION_TEST_TYPE_OLD' , p_value => P_ADMISSION_TEST_TYPE_OLD , p_parameterlist => l_parameter_list);
481 wf_event.AddParameterToList(p_name => 'P_GRADE_ID_NEW' , p_value => P_GRADE_ID_NEW , p_parameterlist => l_parameter_list);
482 wf_event.AddParameterToList(p_name => 'P_GRADE_ID_OLD' , p_value => P_GRADE_ID_OLD , p_parameterlist => l_parameter_list);
483 wf_event.AddParameterToList(p_name => 'P_COMP_TEST_SCORE_NEW' , p_value => P_COMP_TEST_SCORE_NEW , p_parameterlist => l_parameter_list);
484 wf_event.AddParameterToList(p_name => 'P_COMP_TEST_SCORE_OLD' , p_value => P_COMP_TEST_SCORE_OLD , p_parameterlist => l_parameter_list);
485 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
486
487 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
488 l_parameter_list.DELETE;
489
490 EXCEPTION
491 WHEN OTHERS THEN
492 IF cur_user_name%ISOPEN THEN
493 CLOSE cur_user_name;
494 END IF;
495 IF c_seq_num%ISOPEN THEN
496 CLOSE c_seq_num;
497 END IF;
498
499 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TESTSCORE_UPD_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
500 raise;
501 END TESTSCORE_UPD_EVENT;
502
503
504 PROCEDURE TESTSEG_UPD_EVENT
505 (
506 P_TEST_RESULTS_ID IN NUMBER,
507 P_TST_RSLT_DTLS_ID IN NUMBER,
508 P_TEST_SEGMENT_ID IN NUMBER,
509 P_PERSON_ID IN NUMBER,
510 P_TEST_SCORE_NEW IN NUMBER,
511 P_TEST_SCORE_OLD IN NUMBER
512 ) AS
513 -- Get a unique sequence number
514 CURSOR c_seq_num IS
515 SELECT igs_ad_wf_tstseg_upd_s.NEXTVAL
516 FROM sys.dual;
517
518 CURSOR cur_user_name IS
519 SELECT user_name
520 FROM fnd_user
521 WHERE user_id = fnd_global.user_id;
522
523 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
524 l_wf_role fnd_user.user_name%TYPE;
525 l_seq_val NUMBER;
526 lv_raise_event VARCHAR2(100);
527
528
529
530 BEGIN
531
532
533 OPEN cur_user_name;
534 FETCH cur_user_name INTO l_wf_role;
535 CLOSE cur_user_name;
536
537 OPEN c_seq_num;
538 FETCH c_seq_num INTO l_seq_val ;
539 CLOSE c_seq_num ;
540
541 lv_raise_event := 'oracle.apps.igs.ad.test_score_dtls.changed';
542
543 wf_event.AddParameterToList(p_name => 'P_TEST_RESULTS_ID' , p_value => P_TEST_RESULTS_ID , p_parameterlist=> l_parameter_list);
544 wf_event.AddParameterToList(p_name => 'P_TST_RSLT_DTLS_ID' , p_value => P_TST_RSLT_DTLS_ID , p_parameterlist => l_parameter_list);
545 wf_event.AddParameterToList(p_name => 'P_TEST_SEGMENT_ID' , p_value => P_TEST_SEGMENT_ID , p_parameterlist => l_parameter_list);
546 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
547 wf_event.AddParameterToList(p_name => 'P_TEST_SCORE_NEW' , p_value => P_TEST_SCORE_NEW , p_parameterlist => l_parameter_list);
548 wf_event.AddParameterToList(p_name => 'P_TEST_SCORE_OLD' , p_value => P_TEST_SCORE_OLD , p_parameterlist => l_parameter_list);
549 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
550
551 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
552 l_parameter_list.DELETE;
553
554 EXCEPTION
555 WHEN OTHERS THEN
556 IF cur_user_name%ISOPEN THEN
557 CLOSE cur_user_name;
558 END IF;
559 IF c_seq_num%ISOPEN THEN
560 CLOSE c_seq_num;
561 END IF;
562
563 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TESTSEG_UPD_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
564 raise;
565 END TESTSEG_UPD_EVENT;
566
567
568 PROCEDURE ACADHIST_CRT_EVENT
569 (
570 P_HZ_ACAD_HIST_ID IN NUMBER,
571 P_EDUCATION_ID IN NUMBER,
572 P_PERSON_ID IN NUMBER,
573 P_ACTIVE_IND IN VARCHAR2,
574 P_REQUIRED_IND IN VARCHAR2
575 ) AS
576 -- Get a unique sequence number
577 CURSOR c_seq_num IS
578 SELECT igs_ad_wf_acadhist_crt_s.NEXTVAL
579 FROM sys.dual;
580
581 CURSOR cur_user_name IS
582 SELECT user_name
583 FROM fnd_user
584 WHERE user_id = fnd_global.user_id;
585
586 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
587 l_wf_role fnd_user.user_name%TYPE;
588 l_seq_val NUMBER;
589 lv_raise_event VARCHAR2(100);
590
591
592
593 BEGIN
594
595
596 OPEN cur_user_name;
597 FETCH cur_user_name INTO l_wf_role;
598 CLOSE cur_user_name;
599
600 OPEN c_seq_num;
601 FETCH c_seq_num INTO l_seq_val ;
602 CLOSE c_seq_num ;
603
604 lv_raise_event := 'oracle.apps.igs.ad.acad_hist';
605 wf_event.AddParameterToList(p_name => 'P_HZ_ACAD_HIST_ID' , p_value => P_HZ_ACAD_HIST_ID , p_parameterlist=> l_parameter_list);
606 wf_event.AddParameterToList(p_name => 'P_EDUCATION_ID' , p_value => P_EDUCATION_ID , p_parameterlist=> l_parameter_list);
607 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
608 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND' , p_value => P_ACTIVE_IND , p_parameterlist => l_parameter_list);
609 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
610
611 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
612 l_parameter_list.DELETE;
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 IF cur_user_name%ISOPEN THEN
617 CLOSE cur_user_name;
618 END IF;
619 IF c_seq_num%ISOPEN THEN
620 CLOSE c_seq_num;
621 END IF;
622
623 WF_CORE.CONTEXT('IGS_AD_WF_001', 'ACADHIST_CRT_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
624 raise;
625 END ACADHIST_CRT_EVENT;
626
627
628 PROCEDURE ACADHIST_UPD_EVENT
629 (
630 P_HZ_ACAD_HIST_ID IN NUMBER,
631 P_EDUCATION_ID IN NUMBER,
632 P_PERSON_ID IN NUMBER,
633 P_ACTIVE_IND_OLD IN VARCHAR2,
634 P_ACTIVE_IND_NEW IN VARCHAR2,
635 P_REQUIRED_IND_NEW IN VARCHAR2,
636 P_REQUIRED_IND_OLD IN VARCHAR2
637 ) AS
638 -- Get a unique sequence number
639 CURSOR c_seq_num IS
640 SELECT igs_ad_wf_acadhist_upd_s.NEXTVAL
641 FROM sys.dual;
642
643 CURSOR cur_user_name IS
644 SELECT user_name
645 FROM fnd_user
646 WHERE user_id = fnd_global.user_id;
647
648 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
649 l_wf_role fnd_user.user_name%TYPE;
650 l_seq_val NUMBER;
651 lv_raise_event VARCHAR2(100);
652
653
654
655 BEGIN
656
657
658 OPEN cur_user_name;
659 FETCH cur_user_name INTO l_wf_role;
660 CLOSE cur_user_name;
661
662 OPEN c_seq_num;
663 FETCH c_seq_num INTO l_seq_val ;
664 CLOSE c_seq_num ;
665
666 lv_raise_event := 'oracle.apps.igs.ad.acad_hist.changed';
667 wf_event.AddParameterToList(p_name => 'P_HZ_ACAD_HIST_ID' , p_value => P_HZ_ACAD_HIST_ID , p_parameterlist=> l_parameter_list);
668 wf_event.AddParameterToList(p_name => 'P_EDUCATION_ID' , p_value => P_EDUCATION_ID , p_parameterlist=> l_parameter_list);
669 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
670 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND_OLD' , p_value => P_ACTIVE_IND_OLD , p_parameterlist => l_parameter_list);
671 wf_event.AddParameterToList(p_name => 'P_ACTIVE_IND_NEW' , p_value => P_ACTIVE_IND_NEW , p_parameterlist => l_parameter_list);
672 wf_event.AddParameterToList(p_name => 'P_REQUIRED_IND_NEW' , p_value => P_REQUIRED_IND_NEW , p_parameterlist => l_parameter_list);
673 wf_event.AddParameterToList(p_name => 'P_REQUIRED_IND_OLD' , p_value => P_REQUIRED_IND_OLD , p_parameterlist => l_parameter_list);
674 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
675 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
676 l_parameter_list.DELETE;
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF cur_user_name%ISOPEN THEN
681 CLOSE cur_user_name;
682 END IF;
683 IF c_seq_num%ISOPEN THEN
684 CLOSE c_seq_num;
685 END IF;
686
687 WF_CORE.CONTEXT('IGS_AD_WF_001', 'ACADHIST_UPD_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
688 raise;
689 END ACADHIST_UPD_EVENT;
690
691
692 PROCEDURE TRANSCRIPT_UPD_EVENT
693 (
694 P_TRANSCRIPT_ID IN NUMBER,
695 P_EDUCATION_ID IN NUMBER,
696 P_PERSON_ID IN NUMBER,
697 P_TRANSCRIPT_STATUS_OLD IN VARCHAR2,
698 P_TRANSCRIPT_STATUS_NEW IN VARCHAR2,
699 P_TRANSCRIPT_TYPE_OLD IN VARCHAR2,
700 P_TRANSCRIPT_TYPE_NEW IN VARCHAR2
701 ) AS
702 -- Get a unique sequence number
703 CURSOR c_seq_num IS
704 SELECT igs_ad_wf_txcpt_upd_s.NEXTVAL
705 FROM sys.dual;
706
707 CURSOR cur_user_name IS
708 SELECT user_name
709 FROM fnd_user
710 WHERE user_id = fnd_global.user_id;
711
712 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
713 l_wf_role fnd_user.user_name%TYPE;
714 l_seq_val NUMBER;
715 lv_raise_event VARCHAR2(100);
716
717
718
719 BEGIN
720
721
722 OPEN cur_user_name;
723 FETCH cur_user_name INTO l_wf_role;
724 CLOSE cur_user_name;
725
726 OPEN c_seq_num;
727 FETCH c_seq_num INTO l_seq_val ;
728 CLOSE c_seq_num ;
729
730 lv_raise_event := 'oracle.apps.igs.ad.transcript.changed';
731
732 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_ID' , p_value => P_TRANSCRIPT_ID , p_parameterlist=> l_parameter_list);
733 wf_event.AddParameterToList(p_name => 'P_EDUCATION_ID' , p_value => P_EDUCATION_ID , p_parameterlist=> l_parameter_list);
734 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
735 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_STATUS_OLD' , p_value => P_TRANSCRIPT_STATUS_OLD , p_parameterlist => l_parameter_list);
736 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_STATUS_NEW' , p_value => P_TRANSCRIPT_STATUS_NEW , p_parameterlist => l_parameter_list);
737 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_TYPE_OLD' , p_value => P_TRANSCRIPT_TYPE_OLD , p_parameterlist => l_parameter_list);
738 wf_event.AddParameterToList(p_name => 'P_TRANSCRIPT_TYPE_NEW' , p_value => P_TRANSCRIPT_TYPE_NEW , p_parameterlist => l_parameter_list);
739 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
740
741 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
742 l_parameter_list.DELETE;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 IF cur_user_name%ISOPEN THEN
747 CLOSE cur_user_name;
748 END IF;
749 IF c_seq_num%ISOPEN THEN
750 CLOSE c_seq_num;
751 END IF;
752
753 WF_CORE.CONTEXT('IGS_AD_WF_001', 'TRANSCRIPT_UPD_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
754 raise;
755 END TRANSCRIPT_UPD_EVENT;
756
757
758 PROCEDURE APPCOMP_STATUS_UPD_EVENT
759 (
760 P_PERSON_ID IN NUMBER,
761 P_ADMISSION_APPL_NUMBER IN NUMBER,
762 P_NOMINATED_COURSE_CD IN VARCHAR2,
763 P_SEQUENCE_NUMBER IN NUMBER,
764 P_ADM_DOC_STATUS_NEW IN VARCHAR2,
765 P_ADM_DOC_STATUS_OLD IN VARCHAR2
766 ) AS
767 -- Get a unique sequence number
768 CURSOR c_seq_num IS
769 SELECT igs_ad_wf_applcomp_upd_s.NEXTVAL
770 FROM sys.dual;
771
772 CURSOR cur_user_name IS
773 SELECT user_name
774 FROM fnd_user
775 WHERE user_id = fnd_global.user_id;
776
777 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
778 l_wf_role fnd_user.user_name%TYPE;
779 l_seq_val NUMBER;
780 lv_raise_event VARCHAR2(100);
781
782
783
784 BEGIN
785 -- Checking if the Workflow is installed at the environment or not.
786
787 OPEN cur_user_name;
788 FETCH cur_user_name INTO l_wf_role;
789 CLOSE cur_user_name;
790
791 OPEN c_seq_num;
792 FETCH c_seq_num INTO l_seq_val ;
793 CLOSE c_seq_num ;
794
795 lv_raise_event := 'oracle.apps.igs.ad.comp_status.changed';
796
797 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
798 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => P_ADMISSION_APPL_NUMBER , p_parameterlist => l_parameter_list);
799 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
800 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
801 wf_event.AddParameterToList(p_name => 'P_ADM_DOC_STATUS_NEW' , p_value => P_ADM_DOC_STATUS_NEW , p_parameterlist => l_parameter_list);
802 wf_event.AddParameterToList(p_name => 'P_ADM_DOC_STATUS_OLD' , p_value => P_ADM_DOC_STATUS_OLD , p_parameterlist => l_parameter_list);
803 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
804
805
806 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
807 l_parameter_list.DELETE;
808 EXCEPTION
809 WHEN OTHERS THEN
810 IF cur_user_name%ISOPEN THEN
811 CLOSE cur_user_name;
812 END IF;
813 IF c_seq_num%ISOPEN THEN
814 CLOSE c_seq_num;
815 END IF;
816
817 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APPCOMP_STATUS_UPD_EVENT','IGSADIDX'||l_seq_val,lv_raise_event);
818 raise;
819 END APPCOMP_STATUS_UPD_EVENT;
820
821
822 PROCEDURE ENTRY_QUAL_STATUS_UPD_EVENT
823 (
824 P_PERSON_ID IN NUMBER,
825 P_ADMISSION_APPL_NUMBER IN NUMBER,
826 P_NOMINATED_COURSE_CD IN VARCHAR2,
827 P_SEQUENCE_NUMBER IN NUMBER,
828 P_ADM_ENTRY_QUAL_STATUS_NEW IN VARCHAR2,
829 P_ADM_ENTRY_QUAL_STATUS_OLD IN VARCHAR2
830 ) AS
831 -- Get a unique sequence number
832 CURSOR c_seq_num IS
833 SELECT IGS_AD_WF_ENTQUAL_UPD_S.NEXTVAL
834 FROM sys.dual;
835
836 CURSOR cur_user_name IS
837 SELECT user_name
838 FROM fnd_user
839 WHERE user_id = fnd_global.user_id;
840
841 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
842 l_wf_role fnd_user.user_name%TYPE;
843 l_seq_val NUMBER;
844 lv_raise_event VARCHAR2(100);
845
846
847
848 BEGIN
849
850 OPEN cur_user_name;
851 FETCH cur_user_name INTO l_wf_role;
852 CLOSE cur_user_name;
853
854 OPEN c_seq_num;
855 FETCH c_seq_num INTO l_seq_val ;
856 CLOSE c_seq_num ;
857
858 lv_raise_event := 'oracle.apps.igs.ad.ent_qual_status.changed';
859
860 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
861 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => P_ADMISSION_APPL_NUMBER , p_parameterlist => l_parameter_list);
862 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
863 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
864 wf_event.AddParameterToList(p_name => 'P_ADM_ENTRY_QUAL_STATUS_NEW' , p_value => P_ADM_ENTRY_QUAL_STATUS_NEW , p_parameterlist => l_parameter_list);
865 wf_event.AddParameterToList(p_name => 'P_ADM_ENTRY_QUAL_STATUS_OLD' , p_value => P_ADM_ENTRY_QUAL_STATUS_OLD , p_parameterlist => l_parameter_list);
866 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
867
868
869 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
870 l_parameter_list.DELETE;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF cur_user_name%ISOPEN THEN
875 CLOSE cur_user_name;
876 END IF;
877 IF c_seq_num%ISOPEN THEN
878 CLOSE c_seq_num;
879 END IF;
880
881 WF_CORE.CONTEXT('IGS_AD_WF_001', 'ENTRY_QUAL_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
882 raise;
883 END ENTRY_QUAL_STATUS_UPD_EVENT ;
884
885
886
887 PROCEDURE LATE_ADM_FEE_STATUS_UPD_EVENT
888 (
889 P_PERSON_ID IN NUMBER,
890 P_ADMISSION_APPL_NUMBER IN NUMBER,
891 P_NOMINATED_COURSE_CD IN VARCHAR2,
892 P_SEQUENCE_NUMBER IN NUMBER,
893 P_LATE_ADM_FEE_STATUS_NEW IN VARCHAR2,
894 P_LATE_ADM_FEE_STATUS_OLD IN VARCHAR2
895 ) AS
896 -- Get a unique sequence number
897 CURSOR c_seq_num IS
898 SELECT IGS_AD_WF_LATEFEE_UPD_S.NEXTVAL
899 FROM sys.dual;
900
901 CURSOR cur_user_name IS
902 SELECT user_name
903 FROM fnd_user
904 WHERE user_id = fnd_global.user_id;
905
906 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
907
908 l_wf_role fnd_user.user_name%TYPE;
909 l_seq_val NUMBER;
910 lv_raise_event VARCHAR2(100);
911
912
913
914 BEGIN
915
916
917 OPEN cur_user_name;
918 FETCH cur_user_name INTO l_wf_role;
919 CLOSE cur_user_name;
920
921 OPEN c_seq_num;
922 FETCH c_seq_num INTO l_seq_val ;
923 CLOSE c_seq_num ;
924
925 lv_raise_event := 'oracle.apps.igs.ad.late_fee_status.changed';
926
927 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
928 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => P_ADMISSION_APPL_NUMBER , p_parameterlist => l_parameter_list);
929 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
930 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
931 wf_event.AddParameterToList(p_name => 'P_LATE_ADM_FEE_STATUS_NEW' , p_value => P_LATE_ADM_FEE_STATUS_NEW , p_parameterlist => l_parameter_list);
932 wf_event.AddParameterToList(p_name => 'P_LATE_ADM_FEE_STATUS_OLD' , p_value => P_LATE_ADM_FEE_STATUS_OLD , p_parameterlist => l_parameter_list);
933 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
934
935
936 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
937 l_parameter_list.DELETE;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 IF cur_user_name%ISOPEN THEN
942 CLOSE cur_user_name;
943 END IF;
944 IF c_seq_num%ISOPEN THEN
945 CLOSE c_seq_num;
946 END IF;
947
948 WF_CORE.CONTEXT('IGS_AD_WF_001', 'LATE_ADM_FEE_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
949 raise;
950 END LATE_ADM_FEE_STATUS_UPD_EVENT ;
951
952 PROCEDURE COND_OFFER_STATUS_UPD_EVENT
953 (
954 P_PERSON_ID IN NUMBER,
955 P_ADMISSION_APPL_NUMBER IN NUMBER,
956 P_NOMINATED_COURSE_CD IN VARCHAR2,
957 P_SEQUENCE_NUMBER IN NUMBER,
958 P_ADM_CNDTNL_OFFER_STATUS_NEW IN VARCHAR2,
959 P_ADM_CNDTNL_OFFER_STATUS_OLD IN VARCHAR2
960 ) AS
961 -- Get a unique sequence number
962 CURSOR c_seq_num IS
963 SELECT IGS_AD_WF_CNDOFFER_UPD_S.NEXTVAL
964 FROM sys.dual;
965
966 CURSOR cur_user_name IS
967 SELECT user_name
968 FROM fnd_user
969 WHERE user_id = fnd_global.user_id;
970
971 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
972
973 l_wf_role fnd_user.user_name%TYPE;
974 l_seq_val NUMBER;
975 lv_raise_event VARCHAR2(100);
976
977
978
979 BEGIN
980
981 OPEN cur_user_name;
982 FETCH cur_user_name INTO l_wf_role;
983 CLOSE cur_user_name;
984
985 OPEN c_seq_num;
986 FETCH c_seq_num INTO l_seq_val ;
987 CLOSE c_seq_num ;
988
989 lv_raise_event := 'oracle.apps.igs.ad.cond_offer_status.changed';
990
991 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
992 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => P_ADMISSION_APPL_NUMBER , p_parameterlist => l_parameter_list);
993 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
994 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
995 wf_event.AddParameterToList(p_name => 'P_ADM_CNDTNL_OFFER_STATUS_NEW' , p_value => P_ADM_CNDTNL_OFFER_STATUS_NEW , p_parameterlist => l_parameter_list);
996 wf_event.AddParameterToList(p_name => 'P_ADM_CNDTNL_OFFER_STATUS_OLD' , p_value => P_ADM_CNDTNL_OFFER_STATUS_OLD , p_parameterlist => l_parameter_list);
997 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
998
999
1000 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1001 l_parameter_list.DELETE;
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 IF cur_user_name%ISOPEN THEN
1006 CLOSE cur_user_name;
1007 END IF;
1008 IF c_seq_num%ISOPEN THEN
1009 CLOSE c_seq_num;
1010 END IF;
1011
1012 WF_CORE.CONTEXT('IGS_AD_WF_001', 'COND_OFFER_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1013 raise;
1014 END COND_OFFER_STATUS_UPD_EVENT ;
1015
1016 PROCEDURE OFFER_DEFER_STATUS_UPD_EVENT
1017 (
1018 P_PERSON_ID IN NUMBER,
1019 P_ADMISSION_APPL_NUMBER IN NUMBER,
1020 P_NOMINATED_COURSE_CD IN VARCHAR2,
1021 P_SEQUENCE_NUMBER IN NUMBER,
1022 P_ADM_OFFER_DFRMNT_STATUS_NEW IN VARCHAR2,
1023 P_ADM_OFFER_DFRMNT_STATUS_OLD IN VARCHAR2,
1024 P_ADM_CAL_TYPE IN VARCHAR2,
1025 P_ADM_CI_SEQUENCE_NUMBER IN NUMBER
1026 ) AS
1027 -- Get a unique sequence number
1028 CURSOR c_seq_num IS
1029 SELECT IGS_AD_WF_OFER_DEFER_UPD_S.NEXTVAL
1030 FROM sys.dual;
1031
1032 CURSOR cur_user_name IS
1033 SELECT user_name
1034 FROM fnd_user
1035 WHERE user_id = fnd_global.user_id;
1036
1037 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1038
1039 l_wf_role fnd_user.user_name%TYPE;
1040 l_seq_val NUMBER;
1041 lv_raise_event VARCHAR2(100);
1042
1043
1044
1045 BEGIN
1046
1047
1048 OPEN cur_user_name;
1049 FETCH cur_user_name INTO l_wf_role;
1050 CLOSE cur_user_name;
1051
1052 OPEN c_seq_num;
1053 FETCH c_seq_num INTO l_seq_val ;
1054 CLOSE c_seq_num ;
1055
1056 lv_raise_event := 'oracle.apps.igs.ad.offer_defer_status.changed';
1057
1058 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1059 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1060 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD, p_parameterlist => l_parameter_list);
1061 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER, p_parameterlist => l_parameter_list);
1062 wf_event.AddParameterToList(p_name => 'P_ADM_CAL_TYPE', p_value => P_ADM_CAL_TYPE, p_parameterlist => l_parameter_list);
1063 wf_event.AddParameterToList(p_name => 'P_ADM_CI_SEQUENCE_NUMBER', p_value => P_ADM_CI_SEQUENCE_NUMBER, p_parameterlist => l_parameter_list);
1064 wf_event.AddParameterToList(p_name => 'P_ADM_OFFER_DFRMNT_STATUS_NEW', p_value => P_ADM_OFFER_DFRMNT_STATUS_NEW, p_parameterlist => l_parameter_list);
1065 wf_event.AddParameterToList(p_name => 'P_ADM_OFFER_DFRMNT_STATUS_OLD', p_value => P_ADM_OFFER_DFRMNT_STATUS_OLD, p_parameterlist => l_parameter_list);
1066 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1067
1068 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1069 l_parameter_list.DELETE;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 IF cur_user_name%ISOPEN THEN
1074 CLOSE cur_user_name;
1075 END IF;
1076 IF c_seq_num%ISOPEN THEN
1077 CLOSE c_seq_num;
1078 END IF;
1079 WF_CORE.CONTEXT('IGS_AD_WF_001', 'OFFER_DEFER_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1080 raise;
1081 END OFFER_DEFER_STATUS_UPD_EVENT ;
1082
1083 PROCEDURE WAITLIST_STATUS_UPD_EVENT
1084 (
1085 P_PERSON_ID IN NUMBER,
1086 P_ADMISSION_APPL_NUMBER IN NUMBER,
1087 P_NOMINATED_COURSE_CD IN VARCHAR2,
1088 P_SEQUENCE_NUMBER IN NUMBER,
1089 P_WAITLIST_STATUS_NEW IN VARCHAR2,
1090 P_WAITLIST_STATUS_OLD IN VARCHAR2
1091 ) AS
1092 -- Get a unique sequence number
1093 CURSOR c_seq_num IS
1094 SELECT IGS_AD_WF_WAITLIST_UPD_S.NEXTVAL
1095 FROM sys.dual;
1096
1097 CURSOR cur_user_name IS
1098 SELECT user_name
1099 FROM fnd_user
1100 WHERE user_id = fnd_global.user_id;
1101
1102 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1103
1104 l_wf_role fnd_user.user_name%TYPE;
1105 l_seq_val NUMBER;
1106 lv_raise_event VARCHAR2(100);
1107
1108
1109
1110 BEGIN
1111
1112
1113 OPEN cur_user_name;
1114 FETCH cur_user_name INTO l_wf_role;
1115 CLOSE cur_user_name;
1116
1117 OPEN c_seq_num;
1118 FETCH c_seq_num INTO l_seq_val ;
1119 CLOSE c_seq_num ;
1120
1121 lv_raise_event := 'oracle.apps.igs.ad.waitlist_status.changed';
1122
1123 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
1124 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER' , p_value => P_ADMISSION_APPL_NUMBER , p_parameterlist => l_parameter_list);
1125 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD' , p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
1126 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER' , p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1127 wf_event.AddParameterToList(p_name => 'P_WAITLIST_STATUS_NEW' , p_value => P_WAITLIST_STATUS_NEW , p_parameterlist => l_parameter_list);
1128 wf_event.AddParameterToList(p_name => 'P_WAITLIST_STATUS_OLD' , p_value => P_WAITLIST_STATUS_OLD , p_parameterlist => l_parameter_list);
1129 wf_event.AddParameterToList(p_name => 'P_WF_ROLE' , p_value => l_wf_role , p_parameterlist => l_parameter_list);
1130
1131
1132 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1133 l_parameter_list.DELETE;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 IF cur_user_name%ISOPEN THEN
1138 CLOSE cur_user_name;
1139 END IF;
1140 IF c_seq_num%ISOPEN THEN
1141 CLOSE c_seq_num;
1142 END IF;
1143
1144 WF_CORE.CONTEXT('IGS_AD_WF_001', 'WAITLIST_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1145 raise;
1146 END WAITLIST_STATUS_UPD_EVENT ;
1147
1148
1149 PROCEDURE CREATE_APPLICATION_EVENT
1150 (
1151 P_PERSON_ID IN NUMBER,
1152 P_LOGIN_RESP IN VARCHAR2,
1153 P_ACAD_CAL_TYPE IN VARCHAR2,
1154 P_ACAD_CAL_SEQ_NUMBER IN NUMBER,
1155 P_ADM_CAL_TYPE IN VARCHAR2,
1156 P_ADM_CI_SEQUENCE_NUMBER IN NUMBER,
1157 P_APPLICATION_TYPE IN OUT NOCOPY VARCHAR2,
1158 P_LOCATION_CODE IN VARCHAR2,
1159 P_PROGRAM_TYPE IN VARCHAR2,
1160 P_SCH_APL_TO_ID IN NUMBER,
1161 P_ATTENDANCE_TYPE IN VARCHAR2,
1162 P_ATTENDANCE_MODE IN VARCHAR2,
1163 P_OO_ATTRIBUTE_1 IN VARCHAR2,
1164 P_OO_ATTRIBUTE_2 IN VARCHAR2,
1165 P_OO_ATTRIBUTE_3 IN VARCHAR2,
1166 P_OO_ATTRIBUTE_4 IN VARCHAR2,
1167 P_OO_ATTRIBUTE_5 IN VARCHAR2,
1168 P_OO_ATTRIBUTE_6 IN VARCHAR2,
1169 P_OO_ATTRIBUTE_7 IN VARCHAR2,
1170 P_OO_ATTRIBUTE_8 IN VARCHAR2,
1171 P_OO_ATTRIBUTE_9 IN VARCHAR2,
1172 P_OO_ATTRIBUTE_10 IN VARCHAR2,
1173 P_CITIZENSHIP_RESIDENCY_IND IN VARCHAR2,
1174 P_CIT_RES_ATTRIBUTE_1 IN VARCHAR2,
1175 P_CIT_RES_ATTRIBUTE_2 IN VARCHAR2,
1176 P_CIT_RES_ATTRIBUTE_3 IN VARCHAR2,
1177 P_CIT_RES_ATTRIBUTE_4 IN VARCHAR2,
1178 P_CIT_RES_ATTRIBUTE_5 IN VARCHAR2,
1179 P_CIT_RES_ATTRIBUTE_6 IN VARCHAR2,
1180 P_CIT_RES_ATTRIBUTE_7 IN VARCHAR2,
1181 P_CIT_RES_ATTRIBUTE_8 IN VARCHAR2,
1182 P_CIT_RES_ATTRIBUTE_9 IN VARCHAR2,
1183 P_CIT_RES_ATTRIBUTE_10 IN VARCHAR2,
1184 P_STATE_OF_RES_TYPE_CODE IN VARCHAR2,
1185 P_DOM_ATTRIBUTE_1 IN VARCHAR2,
1186 P_DOM_ATTRIBUTE_2 IN VARCHAR2,
1187 P_DOM_ATTRIBUTE_3 IN VARCHAR2,
1188 P_DOM_ATTRIBUTE_4 IN VARCHAR2,
1189 P_DOM_ATTRIBUTE_5 IN VARCHAR2,
1190 P_DOM_ATTRIBUTE_6 IN VARCHAR2,
1191 P_DOM_ATTRIBUTE_7 IN VARCHAR2,
1192 P_DOM_ATTRIBUTE_8 IN VARCHAR2,
1193 P_DOM_ATTRIBUTE_9 IN VARCHAR2,
1194 P_DOM_ATTRIBUTE_10 IN VARCHAR2,
1195 P_GEN_ATTRIBUTE_1 IN VARCHAR2,
1196 P_GEN_ATTRIBUTE_2 IN VARCHAR2,
1197 P_GEN_ATTRIBUTE_3 IN VARCHAR2,
1198 P_GEN_ATTRIBUTE_4 IN VARCHAR2,
1199 P_GEN_ATTRIBUTE_5 IN VARCHAR2,
1200 P_GEN_ATTRIBUTE_6 IN VARCHAR2,
1201 P_GEN_ATTRIBUTE_7 IN VARCHAR2,
1202 P_GEN_ATTRIBUTE_8 IN VARCHAR2,
1203 P_GEN_ATTRIBUTE_9 IN VARCHAR2,
1204 P_GEN_ATTRIBUTE_10 IN VARCHAR2,
1205 P_GEN_ATTRIBUTE_11 IN VARCHAR2,
1206 P_GEN_ATTRIBUTE_12 IN VARCHAR2,
1207 P_GEN_ATTRIBUTE_13 IN VARCHAR2,
1208 P_GEN_ATTRIBUTE_14 IN VARCHAR2,
1209 P_GEN_ATTRIBUTE_15 IN VARCHAR2,
1210 P_GEN_ATTRIBUTE_16 IN VARCHAR2,
1211 P_GEN_ATTRIBUTE_17 IN VARCHAR2,
1212 P_GEN_ATTRIBUTE_18 IN VARCHAR2,
1213 P_GEN_ATTRIBUTE_19 IN VARCHAR2,
1214 P_GEN_ATTRIBUTE_20 IN VARCHAR2,
1215 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1216 X_MSG_COUNT OUT NOCOPY NUMBER,
1217 X_MSG_DATA OUT NOCOPY VARCHAR2,
1218 X_SS_ADM_APPL_ID OUT NOCOPY NUMBER
1219 ) AS
1220 -- Get a unique sequence number
1221 CURSOR c_seq_num IS
1222 SELECT IGS_AD_WF_CRT_APPL_S.NEXTVAL
1223 FROM sys.dual;
1224
1225 CURSOR cur_user_name IS
1226 SELECT user_name
1227 FROM fnd_user
1228 WHERE user_id = fnd_global.user_id;
1229
1230 CURSOR cur_prof_value IS
1231 SELECT FND_PROFILE.VALUE('IGS_WF_ENABLE') value
1232 FROM dual;
1233
1234 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1235
1236 l_wf_role fnd_user.user_name%TYPE;
1237 l_seq_val NUMBER;
1238 lv_raise_event VARCHAR2(100);
1239 l_cur_prof_value cur_prof_value%ROWTYPE;
1240
1241 PROCEDURE set_return_message(
1242 message_name in VARCHAR2,
1243 message_text out nocopy VARCHAR2,
1244 message_token in VARCHAR2 DEFAULT NULL,
1245 message_token_text in VARCHAR2 DEFAULT NULL) IS
1246
1247 l_msg_idx_start NUMBER;
1248 l_msg_index NUMBER;
1249 l_app_nme VARCHAR2(1000);
1250 l_msg_nme VARCHAR2(2000);
1251 l_msg_txt fnd_new_messages.message_text%TYPE;
1252
1253 BEGIN
1254
1255 l_msg_index := igs_ge_msg_stack.count_msg;
1256 FND_MESSAGE.SET_NAME('IGS',message_name);
1257 IF message_token IS NOT NULL THEN
1258 FND_MESSAGE.SET_TOKEN(message_token ,message_token_text);
1259 END IF;
1260 IGS_GE_MSG_STACK.ADD;
1261
1262 igs_ge_msg_stack.get(l_msg_index + 1,'T',l_msg_txt,l_msg_idx_start);
1263 igs_ge_msg_stack.delete_msg(l_msg_idx_start);
1264 fnd_message.parse_encoded (l_msg_txt, l_app_nme, l_msg_nme);
1265 fnd_message.set_encoded (l_msg_txt);
1266 message_text := fnd_message.get;
1267
1268 END set_return_message;
1269
1270 BEGIN
1271
1272 l_wf_role := NULL;
1273 l_seq_val := NULL;
1274 lv_raise_event := NULL;
1275 l_cur_prof_value := NULL;
1276
1277 OPEN cur_prof_value;
1278 FETCH cur_prof_value INTO l_cur_prof_value;
1279 CLOSE cur_prof_value;
1280
1281 IF (l_cur_prof_value.value = 'Y') THEN
1282
1283 OPEN cur_user_name;
1284 FETCH cur_user_name INTO l_wf_role;
1285 CLOSE cur_user_name;
1286
1287 OPEN c_seq_num;
1288 FETCH c_seq_num INTO l_seq_val ;
1289 CLOSE c_seq_num ;
1290
1291 lv_raise_event := 'oracle.apps.igs.ad.create_application';
1292
1293 wf_event.AddParameterToList(p_name => 'P_PERSON_ID' , p_value => P_PERSON_ID , p_parameterlist=> l_parameter_list);
1294 wf_event.AddParameterToList(p_name => 'P_LOGIN_RESP' , p_value => P_LOGIN_RESP , p_parameterlist => l_parameter_list);
1295 wf_event.AddParameterToList(p_name => 'P_ACAD_CAL_TYPE' , p_value => P_ACAD_CAL_TYPE , p_parameterlist=> l_parameter_list);
1296 wf_event.AddParameterToList(p_name => 'P_ACAD_CAL_SEQ_NUMBER' , p_value => P_ACAD_CAL_SEQ_NUMBER , p_parameterlist => l_parameter_list);
1297 wf_event.AddParameterToList(p_name => 'P_ADM_CAL_TYPE' , p_value => P_ADM_CAL_TYPE , p_parameterlist=> l_parameter_list);
1298 wf_event.AddParameterToList(p_name => 'P_ADM_CI_SEQUENCE_NUMBER' , p_value => P_ADM_CI_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1299 wf_event.AddParameterToList(p_name => 'P_APPLICATION_TYPE' , p_value => P_APPLICATION_TYPE , p_parameterlist=> l_parameter_list);
1300 wf_event.AddParameterToList(p_name => 'P_LOCATION_CODE' , p_value => P_LOCATION_CODE , p_parameterlist=> l_parameter_list);
1301 wf_event.AddParameterToList(p_name => 'P_PROGRAM_TYPE' , p_value => P_PROGRAM_TYPE , p_parameterlist => l_parameter_list);
1302 wf_event.AddParameterToList(p_name => 'P_SCH_APL_TO_ID' , p_value => P_SCH_APL_TO_ID , p_parameterlist=> l_parameter_list);
1303 wf_event.AddParameterToList(p_name => 'P_ATTENDANCE_TYPE' , p_value => P_ATTENDANCE_TYPE , p_parameterlist => l_parameter_list);
1304 wf_event.AddParameterToList(p_name => 'P_ATTENDANCE_MODE' , p_value => P_ATTENDANCE_MODE , p_parameterlist=> l_parameter_list);
1305 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_1' , p_value => P_OO_ATTRIBUTE_1 , p_parameterlist => l_parameter_list);
1306 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_2' , p_value => P_OO_ATTRIBUTE_2 , p_parameterlist=> l_parameter_list);
1307 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_3' , p_value => P_OO_ATTRIBUTE_3 , p_parameterlist => l_parameter_list);
1308 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_4' , p_value => P_OO_ATTRIBUTE_4 , p_parameterlist=> l_parameter_list);
1309 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_5' , p_value => P_OO_ATTRIBUTE_5 , p_parameterlist => l_parameter_list);
1310 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_6' , p_value => P_OO_ATTRIBUTE_6 , p_parameterlist=> l_parameter_list);
1311 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_7' , p_value => P_OO_ATTRIBUTE_7 , p_parameterlist => l_parameter_list);
1312 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_8' , p_value => P_OO_ATTRIBUTE_8 , p_parameterlist=> l_parameter_list);
1313 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_9' , p_value => P_OO_ATTRIBUTE_9 , p_parameterlist => l_parameter_list);
1314 wf_event.AddParameterToList(p_name => 'P_OO_ATTRIBUTE_10', p_value => P_OO_ATTRIBUTE_10, p_parameterlist=> l_parameter_list);
1315 wf_event.AddParameterToList(p_name => 'P_CITIZENSHIP_RESIDENCY_IND', p_value => P_CITIZENSHIP_RESIDENCY_IND , p_parameterlist => l_parameter_list);
1316 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_1' , p_value => P_CIT_RES_ATTRIBUTE_1 , p_parameterlist=> l_parameter_list);
1317 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_2' , p_value => P_CIT_RES_ATTRIBUTE_2 , p_parameterlist => l_parameter_list);
1318 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_3' , p_value => P_CIT_RES_ATTRIBUTE_3 , p_parameterlist=> l_parameter_list);
1319 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_4' , p_value => P_CIT_RES_ATTRIBUTE_4 , p_parameterlist => l_parameter_list);
1320 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_5' , p_value => P_CIT_RES_ATTRIBUTE_5 , p_parameterlist=> l_parameter_list);
1321 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_6' , p_value => P_CIT_RES_ATTRIBUTE_6 , p_parameterlist => l_parameter_list);
1322 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_7' , p_value => P_CIT_RES_ATTRIBUTE_7 , p_parameterlist=> l_parameter_list);
1323 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_8' , p_value => P_CIT_RES_ATTRIBUTE_8 , p_parameterlist => l_parameter_list);
1324 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_9' , p_value => P_CIT_RES_ATTRIBUTE_9 , p_parameterlist=> l_parameter_list);
1325 wf_event.AddParameterToList(p_name => 'P_CIT_RES_ATTRIBUTE_10', p_value => P_CIT_RES_ATTRIBUTE_10 , p_parameterlist => l_parameter_list);
1326 wf_event.AddParameterToList(p_name => 'P_STATE_OF_RES_TYPE_CODE' , p_value => P_STATE_OF_RES_TYPE_CODE , p_parameterlist=> l_parameter_list);
1327 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_1' , p_value => P_DOM_ATTRIBUTE_1 , p_parameterlist => l_parameter_list);
1328 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_2' , p_value => P_DOM_ATTRIBUTE_2 , p_parameterlist=> l_parameter_list);
1329 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_3' , p_value => P_DOM_ATTRIBUTE_3 , p_parameterlist => l_parameter_list);
1330 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_4' , p_value => P_DOM_ATTRIBUTE_4 , p_parameterlist=> l_parameter_list);
1331 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_5' , p_value => P_DOM_ATTRIBUTE_5 , p_parameterlist => l_parameter_list);
1332 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_6' , p_value => P_DOM_ATTRIBUTE_6 , p_parameterlist=> l_parameter_list);
1333 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_7' , p_value => P_DOM_ATTRIBUTE_7 , p_parameterlist => l_parameter_list);
1334 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_8' , p_value => P_DOM_ATTRIBUTE_8 , p_parameterlist=> l_parameter_list);
1335 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_9' , p_value => P_DOM_ATTRIBUTE_9 , p_parameterlist => l_parameter_list);
1336 wf_event.AddParameterToList(p_name => 'P_DOM_ATTRIBUTE_10' , p_value => P_DOM_ATTRIBUTE_10 , p_parameterlist=> l_parameter_list);
1337 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_1' , p_value => P_GEN_ATTRIBUTE_1 , p_parameterlist => l_parameter_list);
1338 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_2' , p_value => P_GEN_ATTRIBUTE_2 , p_parameterlist=> l_parameter_list);
1339 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_3' , p_value => P_GEN_ATTRIBUTE_3 , p_parameterlist => l_parameter_list);
1340 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_4' , p_value => P_GEN_ATTRIBUTE_4 , p_parameterlist=> l_parameter_list);
1341 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_5' , p_value => P_GEN_ATTRIBUTE_5 , p_parameterlist => l_parameter_list);
1342 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_6' , p_value => P_GEN_ATTRIBUTE_6 , p_parameterlist=> l_parameter_list);
1343 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_7' , p_value => P_GEN_ATTRIBUTE_7 , p_parameterlist => l_parameter_list);
1344 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_8' , p_value => P_GEN_ATTRIBUTE_8 , p_parameterlist=> l_parameter_list);
1345 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_9' , p_value => P_GEN_ATTRIBUTE_9 , p_parameterlist => l_parameter_list);
1346 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_10' , p_value => P_GEN_ATTRIBUTE_10 , p_parameterlist=> l_parameter_list);
1347 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_11' , p_value => P_GEN_ATTRIBUTE_11 , p_parameterlist => l_parameter_list);
1348 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_12' , p_value => P_GEN_ATTRIBUTE_12 , p_parameterlist=> l_parameter_list);
1349 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_13' , p_value => P_GEN_ATTRIBUTE_13 , p_parameterlist => l_parameter_list);
1350 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_14' , p_value => P_GEN_ATTRIBUTE_14 , p_parameterlist=> l_parameter_list);
1351 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_15' , p_value => P_GEN_ATTRIBUTE_15 , p_parameterlist => l_parameter_list);
1352 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_16' , p_value => P_GEN_ATTRIBUTE_16 , p_parameterlist => l_parameter_list);
1353 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_17' , p_value => P_GEN_ATTRIBUTE_17 , p_parameterlist=> l_parameter_list);
1354 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_18' , p_value => P_GEN_ATTRIBUTE_18 , p_parameterlist => l_parameter_list);
1355 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_19' , p_value => P_GEN_ATTRIBUTE_19 , p_parameterlist=> l_parameter_list);
1356 wf_event.AddParameterToList(p_name => 'P_GEN_ATTRIBUTE_20' , p_value => P_GEN_ATTRIBUTE_20 , p_parameterlist => l_parameter_list);
1357
1358 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1359 l_parameter_list.DELETE;
1360
1361 x_return_status := wf_engine.getitemattrtext('IGSADP2A',l_seq_val,'P_RETURN_STATUS');
1362 x_msg_count := wf_engine.getitemattrnumber('IGSADP2A',l_seq_val,'P_MSG_COUNT');
1363 x_msg_data := wf_engine.getitemattrtext('IGSADP2A',l_seq_val,'P_MSG_DATA');
1364 x_ss_adm_appl_id := wf_engine.getitemattrtext('IGSADP2A',l_seq_val,'P_SS_ADM_APPL_ID');
1365 p_application_type := wf_engine.getitemattrtext('IGSADP2A',l_seq_val,'P_APPLICATION_TYPE');
1366
1367 IF x_return_status IS NULL THEN
1368 x_return_status := 'U';
1369 x_msg_count := 1;
1370 set_return_message('IGS_AV_UNHANDLED_ERROR',x_msg_data,'ERROR',sqlerrm);
1371 END IF;
1372
1373 ELSE
1374 x_return_status := 'E';
1375 x_msg_count := 1;
1376 set_return_message('IGS_EN_WF_NOT_ENABLE',x_msg_data);
1377
1378 END IF;
1379
1380
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 IF cur_user_name%ISOPEN THEN
1384 CLOSE cur_user_name;
1385 END IF;
1386 IF c_seq_num%ISOPEN THEN
1387 CLOSE c_seq_num;
1388 END IF;
1389
1390 IF cur_prof_value%ISOPEN THEN
1391 CLOSE cur_prof_value;
1392 END IF;
1393
1394 WF_CORE.CONTEXT('IGS_AD_WF_001', 'CREATE_APPLICATION_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1395 raise;
1396 END CREATE_APPLICATION_EVENT ;
1397
1398
1399 PROCEDURE ASSIGN_REQUIRMENT_DONE_EVENT
1400 (
1401 P_PERSON_ID IN NUMBER,
1402 P_ADMISSION_APPL_NUMBER IN NUMBER,
1403 P_NOMINATED_COURSE_CD IN VARCHAR2,
1404 P_SEQUENCE_NUMBER IN NUMBER
1405 ) AS
1406 -- Get a unique sequence number
1407 CURSOR c_seq_num IS
1408 SELECT IGS_AD_WF_ASSREQ_S.NEXTVAL
1409 FROM sys.dual;
1410
1411 CURSOR cur_user_name IS
1412 SELECT user_name
1413 FROM fnd_user
1414 WHERE user_id = fnd_global.user_id;
1415
1416 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1417
1418 l_wf_role fnd_user.user_name%TYPE;
1419 l_seq_val NUMBER;
1420 lv_raise_event VARCHAR2(100);
1421
1422
1423
1424 BEGIN
1425
1426
1427 OPEN cur_user_name;
1428 FETCH cur_user_name INTO l_wf_role;
1429 CLOSE cur_user_name;
1430
1431 OPEN c_seq_num;
1432 FETCH c_seq_num INTO l_seq_val ;
1433 CLOSE c_seq_num ;
1434
1435 lv_raise_event := 'oracle.apps.igs.ad.app_requirement';
1436
1437 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1438 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1439 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD, p_parameterlist => l_parameter_list);
1440 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER,p_parameterlist => l_parameter_list);
1441 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1442
1443 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1444 l_parameter_list.DELETE;
1445
1446 EXCEPTION
1447 WHEN OTHERS THEN
1448 IF cur_user_name%ISOPEN THEN
1449 CLOSE cur_user_name;
1450 END IF;
1451 IF c_seq_num%ISOPEN THEN
1452 CLOSE c_seq_num;
1453 END IF;
1454 WF_CORE.CONTEXT('IGS_AD_WF_001', 'ASSIGN_REQUIRMENT_DONE_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1455 raise;
1456 END ASSIGN_REQUIRMENT_DONE_EVENT;
1457
1458 PROCEDURE FEE_PAYMENT_CRT_EVENT
1459 (
1460 P_PERSON_ID IN NUMBER,
1461 P_ADMISSION_APPL_NUMBER IN NUMBER,
1462 P_APP_REQ_ID IN NUMBER,
1463 P_APPLICANT_FEE_TYPE IN NUMBER,
1464 P_APPLICANT_FEE_STATUS IN NUMBER
1465 ) AS
1466 -- Get a unique sequence number
1467 CURSOR c_seq_num IS
1468 SELECT IGS_AD_WF_FEE_PAY_CRT_S.NEXTVAL
1469 FROM sys.dual;
1470
1471 CURSOR cur_user_name IS
1472 SELECT user_name
1473 FROM fnd_user
1474 WHERE user_id = fnd_global.user_id;
1475
1476 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1477
1478 l_wf_role fnd_user.user_name%TYPE;
1479 l_seq_val NUMBER;
1480 lv_raise_event VARCHAR2(100);
1481
1482
1483
1484 BEGIN
1485
1486
1487 OPEN cur_user_name;
1488 FETCH cur_user_name INTO l_wf_role;
1489 CLOSE cur_user_name;
1490
1491 OPEN c_seq_num;
1492 FETCH c_seq_num INTO l_seq_val ;
1493 CLOSE c_seq_num ;
1494
1495 lv_raise_event := 'oracle.apps.igs.ad.app_fee_status';
1496
1497 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1498 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1499 wf_event.AddParameterToList(p_name => 'P_APP_REQ_ID', p_value => P_APP_REQ_ID, p_parameterlist => l_parameter_list);
1500 wf_event.AddParameterToList(p_name => 'P_APPLICANT_FEE_TYPE', p_value => P_APPLICANT_FEE_TYPE, p_parameterlist => l_parameter_list);
1501 wf_event.AddParameterToList(p_name => 'P_APPLICANT_FEE_STATUS', p_value => P_APPLICANT_FEE_STATUS, p_parameterlist => l_parameter_list);
1502 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1503
1504 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1505 l_parameter_list.DELETE;
1506
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 IF cur_user_name%ISOPEN THEN
1510 CLOSE cur_user_name;
1511 END IF;
1512 IF c_seq_num%ISOPEN THEN
1513 CLOSE c_seq_num;
1514 END IF;
1515 WF_CORE.CONTEXT('IGS_AD_WF_001', 'FEE_PAYMENT_CRT_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1516 raise;
1517 END FEE_PAYMENT_CRT_EVENT;
1518
1519 PROCEDURE FEE_PAYMENT_UPD_EVENT
1520 (
1521 P_PERSON_ID IN NUMBER,
1522 P_ADMISSION_APPL_NUMBER IN NUMBER,
1523 P_APP_REQ_ID IN NUMBER,
1524 P_APPLICANT_FEE_TYPE IN NUMBER,
1525 P_APPLICANT_FEE_STATUS_NEW IN NUMBER,
1526 P_APPLICANT_FEE_STATUS_OLD IN NUMBER
1527 ) AS
1528 -- Get a unique sequence number
1529 CURSOR c_seq_num IS
1530 SELECT IGS_AD_WF_FEE_PAY_UPD_S.NEXTVAL
1531 FROM sys.dual;
1532
1533 CURSOR cur_user_name IS
1534 SELECT user_name
1535 FROM fnd_user
1536 WHERE user_id = fnd_global.user_id;
1537
1538 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1539
1540 l_wf_role fnd_user.user_name%TYPE;
1541 l_seq_val NUMBER;
1542 lv_raise_event VARCHAR2(100);
1543
1544
1545
1546 BEGIN
1547
1548
1549 OPEN cur_user_name;
1550 FETCH cur_user_name INTO l_wf_role;
1551 CLOSE cur_user_name;
1552
1553 OPEN c_seq_num;
1554 FETCH c_seq_num INTO l_seq_val ;
1555 CLOSE c_seq_num ;
1556
1557 lv_raise_event := 'oracle.apps.igs.ad.app_fee_status.changed';
1558
1559 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1560 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1561 wf_event.AddParameterToList(p_name => 'P_APP_REQ_ID', p_value => P_APP_REQ_ID, p_parameterlist => l_parameter_list);
1562 wf_event.AddParameterToList(p_name => 'P_APPLICANT_FEE_TYPE', p_value => P_APPLICANT_FEE_TYPE, p_parameterlist => l_parameter_list);
1563 wf_event.AddParameterToList(p_name => 'P_APPLICANT_FEE_STATUS_NEW', p_value => P_APPLICANT_FEE_STATUS_NEW, p_parameterlist => l_parameter_list);
1564 wf_event.AddParameterToList(p_name => 'P_APPLICANT_FEE_STATUS_OLD', p_value => P_APPLICANT_FEE_STATUS_OLD, p_parameterlist => l_parameter_list);
1565 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1566
1567 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1568 l_parameter_list.DELETE;
1569
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 IF cur_user_name%ISOPEN THEN
1573 CLOSE cur_user_name;
1574 END IF;
1575 IF c_seq_num%ISOPEN THEN
1576 CLOSE c_seq_num;
1577 END IF;
1578 WF_CORE.CONTEXT('IGS_AD_WF_001', 'FEE_PAYMENT_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1579 raise;
1580 END FEE_PAYMENT_UPD_EVENT;
1581
1582 PROCEDURE APP_PRCOC_STATUS_UPD_EVENT
1583 (
1584 P_PERSON_ID IN NUMBER,
1585 P_ADMISSION_APPL_NUMBER IN NUMBER,
1586 P_ADM_APPL_STATUS_NEW IN VARCHAR2,
1587 P_ADM_APPL_STATUS_OLD IN VARCHAR2
1588 ) AS
1589 -- Get a unique sequence number
1590 CURSOR c_seq_num IS
1591 SELECT IGS_AD_WF_APP_PROC_STAT_UPD_S.NEXTVAL
1592 FROM sys.dual;
1593
1594 CURSOR cur_user_name IS
1595 SELECT user_name
1596 FROM fnd_user
1597 WHERE user_id = fnd_global.user_id;
1598
1599 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1600
1601 l_wf_role fnd_user.user_name%TYPE;
1602 l_seq_val NUMBER;
1603 lv_raise_event VARCHAR2(100);
1604 BEGIN
1605 OPEN cur_user_name;
1606 FETCH cur_user_name INTO l_wf_role;
1607 CLOSE cur_user_name;
1608
1609 OPEN c_seq_num;
1610 FETCH c_seq_num INTO l_seq_val ;
1611 CLOSE c_seq_num ;
1612
1613 lv_raise_event := 'oracle.apps.igs.ad.app_process_status.changed';
1614
1615 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1616 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1617 wf_event.AddParameterToList(p_name => 'P_ADM_APPL_STATUS_NEW', p_value => P_ADM_APPL_STATUS_NEW, p_parameterlist => l_parameter_list);
1618 wf_event.AddParameterToList(p_name => 'P_ADM_APPL_STATUS_OLD', p_value => P_ADM_APPL_STATUS_OLD, p_parameterlist => l_parameter_list);
1619 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1620
1621 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1622 l_parameter_list.DELETE;
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 IF cur_user_name%ISOPEN THEN
1627 CLOSE cur_user_name;
1628 END IF;
1629 IF c_seq_num%ISOPEN THEN
1630 CLOSE c_seq_num;
1631 END IF;
1632 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APP_PRCOC_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1633 raise;
1634 END APP_PRCOC_STATUS_UPD_EVENT;
1635
1636 PROCEDURE APP_INSTANCE_STATUS_UPD_EVENT
1637 (
1638 P_PERSON_ID IN NUMBER,
1639 P_ADMISSION_APPL_NUMBER IN NUMBER,
1640 P_NOMINATED_COURSE_CD IN VARCHAR2,
1641 P_SEQUENCE_NUMBER IN NUMBER,
1642 P_APPL_INST_STATUS_NEW IN VARCHAR2,
1643 P_APPL_INST_STATUS_OLD IN VARCHAR2
1644 ) AS
1645 -- Get a unique sequence number
1646 CURSOR c_seq_num IS
1647 SELECT IGS_AD_WF_APP_INST_STAT_UPD_S.NEXTVAL
1648 FROM sys.dual;
1649
1650 CURSOR cur_user_name IS
1651 SELECT user_name
1652 FROM fnd_user
1653 WHERE user_id = fnd_global.user_id;
1654
1655 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1656
1657 l_wf_role fnd_user.user_name%TYPE;
1658 l_seq_val NUMBER;
1659 lv_raise_event VARCHAR2(100);
1660 BEGIN
1661 OPEN cur_user_name;
1662 FETCH cur_user_name INTO l_wf_role;
1663 CLOSE cur_user_name;
1664
1665 OPEN c_seq_num;
1666 FETCH c_seq_num INTO l_seq_val ;
1667 CLOSE c_seq_num ;
1668
1669 lv_raise_event := 'oracle.apps.igs.ad.app_inst_process_status.changed';
1670
1671 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1672 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1673 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
1674 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1675 wf_event.AddParameterToList(p_name => 'P_APPL_INST_STATUS_NEW', p_value => P_APPL_INST_STATUS_NEW, p_parameterlist => l_parameter_list);
1676 wf_event.AddParameterToList(p_name => 'P_APPL_INST_STATUS_OLD', p_value => P_APPL_INST_STATUS_OLD, p_parameterlist => l_parameter_list);
1677 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1678
1679 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1680 l_parameter_list.DELETE;
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 IF cur_user_name%ISOPEN THEN
1685 CLOSE cur_user_name;
1686 END IF;
1687 IF c_seq_num%ISOPEN THEN
1688 CLOSE c_seq_num;
1689 END IF;
1690 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APP_INSTANCE_STATUS_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1691 raise;
1692 END APP_INSTANCE_STATUS_UPD_EVENT;
1693
1694 PROCEDURE APP_RECONSIDER_REQUEST_EVENT
1695 (
1696 P_PERSON_ID IN NUMBER,
1697 P_ADMISSION_APPL_NUMBER IN NUMBER,
1698 P_NOMINATED_COURSE_CD IN VARCHAR2,
1699 P_SEQUENCE_NUMBER IN NUMBER,
1700 P_ADM_OUTCOME_STATUS IN VARCHAR2,
1701 P_ADM_OFFER_RESP_STATUS IN VARCHAR2
1702 ) AS
1703 -- Get a unique sequence number
1704 CURSOR c_seq_num IS
1705 SELECT IGS_AD_WF_APP_RECONS_REQ_S.NEXTVAL
1706 FROM sys.dual;
1707
1708 CURSOR cur_user_name IS
1709 SELECT user_name
1710 FROM fnd_user
1711 WHERE user_id = fnd_global.user_id;
1712
1713 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1714
1715 l_wf_role fnd_user.user_name%TYPE;
1716 l_seq_val NUMBER;
1717 lv_raise_event VARCHAR2(100);
1718 BEGIN
1719 OPEN cur_user_name;
1720 FETCH cur_user_name INTO l_wf_role;
1721 CLOSE cur_user_name;
1722
1723 OPEN c_seq_num;
1724 FETCH c_seq_num INTO l_seq_val ;
1725 CLOSE c_seq_num ;
1726
1727 lv_raise_event := 'oracle.apps.igs.ad.app_reconsider.changed';
1728
1729 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1730 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1731 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
1732 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1733 wf_event.AddParameterToList(p_name => 'P_ADM_OUTCOME_STATUS', p_value => P_ADM_OUTCOME_STATUS, p_parameterlist => l_parameter_list);
1734 wf_event.AddParameterToList(p_name => 'P_ADM_OFFER_RESP_STATUS', p_value => P_ADM_OFFER_RESP_STATUS, p_parameterlist => l_parameter_list);
1735 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1736
1737 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1738 l_parameter_list.DELETE;
1739
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 IF cur_user_name%ISOPEN THEN
1743 CLOSE cur_user_name;
1744 END IF;
1745 IF c_seq_num%ISOPEN THEN
1746 CLOSE c_seq_num;
1747 END IF;
1748 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APP_RECONSIDER_REQUEST_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1749 raise;
1750 END APP_RECONSIDER_REQUEST_EVENT;
1751
1752 PROCEDURE APP_RECON_FUT_REQ_UPD_EVENT
1753 (
1754 P_PERSON_ID IN NUMBER,
1755 P_ADMISSION_APPL_NUMBER IN NUMBER,
1756 P_NOMINATED_COURSE_CD IN VARCHAR2,
1757 P_SEQUENCE_NUMBER IN NUMBER,
1758 P_ADM_OUTCOME_STATUS IN VARCHAR2,
1759 P_ADM_OFFER_RESP_STATUS IN VARCHAR2,
1760 P_FUTURE_ADM_CAL_TYPE_NEW IN VARCHAR2,
1761 P_FUTURE_ADM_CAL_TYPE_OLD IN VARCHAR2,
1762 P_FUTURE_ADM_CI_SEQU_NUM_NEW IN NUMBER,
1763 P_FUTURE_ADM_CI_SEQU_NUM_OLD IN NUMBER,
1764 P_FUTURE_ACAD_CAL_TYPE_NEW IN VARCHAR2,
1765 P_FUTURE_ACAD_CAL_TYPE_OLD IN VARCHAR2,
1766 P_FUTURE_ACAD_CI_SEQU_NUM_NEW IN NUMBER,
1767 P_FUTURE_ACAD_CI_SEQ_NUM_OLD IN NUMBER,
1768 P_REQ_FOR_RECONSIDERATION_IND IN VARCHAR2
1769 ) AS
1770 -- Get a unique sequence number
1771 CURSOR c_seq_num IS
1772 SELECT IGS_AD_WF_APP_RECONS_REQ_S.NEXTVAL
1773 FROM sys.dual;
1774
1775 CURSOR cur_user_name IS
1776 SELECT user_name
1777 FROM fnd_user
1778 WHERE user_id = fnd_global.user_id;
1779
1780 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1781
1782 l_wf_role fnd_user.user_name%TYPE;
1783 l_seq_val NUMBER;
1784 lv_raise_event VARCHAR2(100);
1785 BEGIN
1786 OPEN cur_user_name;
1787 FETCH cur_user_name INTO l_wf_role;
1788 CLOSE cur_user_name;
1789
1790 OPEN c_seq_num;
1791 FETCH c_seq_num INTO l_seq_val ;
1792 CLOSE c_seq_num ;
1793
1794 lv_raise_event := 'oracle.apps.igs.ad.app_reconsider_future.changed';
1795
1796 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1797 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1798 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
1799 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1800 wf_event.AddParameterToList(p_name => 'P_ADM_OUTCOME_STATUS', p_value => P_ADM_OUTCOME_STATUS, p_parameterlist => l_parameter_list);
1801 wf_event.AddParameterToList(p_name => 'P_ADM_OFFER_RESP_STATUS', p_value => P_ADM_OFFER_RESP_STATUS, p_parameterlist => l_parameter_list);
1802 wf_event.AddParameterToList(p_name => 'P_FUTURE_ADM_CAL_TYPE_NEW', p_value => P_FUTURE_ADM_CAL_TYPE_NEW, p_parameterlist=> l_parameter_list);
1803 wf_event.AddParameterToList(p_name => 'P_FUTURE_ADM_CAL_TYPE_OLD', p_value => P_FUTURE_ADM_CAL_TYPE_OLD, p_parameterlist => l_parameter_list);
1804 wf_event.AddParameterToList(p_name => 'P_FUTURE_ADM_CI_SEQU_NUM_NEW', p_value => P_FUTURE_ADM_CI_SEQU_NUM_NEW, p_parameterlist => l_parameter_list);
1805 wf_event.AddParameterToList(p_name => 'P_FUTURE_ADM_CI_SEQU_NUM_OLD', p_value => P_FUTURE_ADM_CI_SEQU_NUM_OLD, p_parameterlist => l_parameter_list);
1806 wf_event.AddParameterToList(p_name => 'P_FUTURE_ACAD_CAL_TYPE_NEW', p_value => P_FUTURE_ACAD_CAL_TYPE_NEW, p_parameterlist => l_parameter_list);
1807 wf_event.AddParameterToList(p_name => 'P_FUTURE_ACAD_CAL_TYPE_OLD', p_value => P_FUTURE_ACAD_CAL_TYPE_OLD, p_parameterlist => l_parameter_list);
1808 wf_event.AddParameterToList(p_name => 'P_FUTURE_ACAD_CI_SEQU_NUM_NEW', p_value => P_FUTURE_ACAD_CI_SEQU_NUM_NEW, p_parameterlist => l_parameter_list);
1809 wf_event.AddParameterToList(p_name => 'P_FUTURE_ACAD_CI_SEQ_NUM_OLD', p_value => P_FUTURE_ACAD_CI_SEQ_NUM_OLD, p_parameterlist => l_parameter_list);
1810 wf_event.AddParameterToList(p_name => 'P_REQ_FOR_RECONSIDERATION_IND', p_value => P_REQ_FOR_RECONSIDERATION_IND, p_parameterlist => l_parameter_list);
1811 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1812
1813 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1814 l_parameter_list.DELETE;
1815
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818 IF cur_user_name%ISOPEN THEN
1819 CLOSE cur_user_name;
1820 END IF;
1821 IF c_seq_num%ISOPEN THEN
1822 CLOSE c_seq_num;
1823 END IF;
1824 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APP_RECON_FUT_REQ_UPD_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1825 raise;
1826 END APP_RECON_FUT_REQ_UPD_EVENT;
1827
1828 PROCEDURE APP_INST_QUALIFYING_CODE_EVENT
1829 (
1830 P_PERSON_ID IN NUMBER,
1831 P_ADMISSION_APPL_NUMBER IN NUMBER,
1832 P_NOMINATED_COURSE_CD IN VARCHAR2,
1833 P_SEQUENCE_NUMBER IN NUMBER,
1834 P_QUALIFYING_TYPE_CODE IN VARCHAR2,
1835 P_QUALIFYING_CODE_ID_NEW IN NUMBER,
1836 P_QUALIFYING_CODE_ID_OLD IN NUMBER,
1837 P_QUALIFYING_VALUE_NEW IN VARCHAR2,
1838 P_QUALIFYING_VALUE_OLD IN VARCHAR2
1839 ) AS
1840 -- Get a unique sequence number
1841 CURSOR c_seq_num IS
1842 SELECT IGS_AD_WF_APP_QUAL_CODE_S.NEXTVAL
1843 FROM sys.dual;
1844
1845 CURSOR cur_user_name IS
1846 SELECT user_name
1847 FROM fnd_user
1848 WHERE user_id = fnd_global.user_id;
1849
1850 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1851
1852 l_wf_role fnd_user.user_name%TYPE;
1853 l_seq_val NUMBER;
1854 lv_raise_event VARCHAR2(100);
1855 BEGIN
1856 OPEN cur_user_name;
1857 FETCH cur_user_name INTO l_wf_role;
1858 CLOSE cur_user_name;
1859
1860 OPEN c_seq_num;
1861 FETCH c_seq_num INTO l_seq_val ;
1862 CLOSE c_seq_num ;
1863
1864 lv_raise_event := 'oracle.apps.igs.ad.qual_code';
1865
1866 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1867 wf_event.AddParameterToList(p_name => 'P_ADMISSION_APPL_NUMBER', p_value => P_ADMISSION_APPL_NUMBER, p_parameterlist => l_parameter_list);
1868 wf_event.AddParameterToList(p_name => 'P_NOMINATED_COURSE_CD', p_value => P_NOMINATED_COURSE_CD , p_parameterlist => l_parameter_list);
1869 wf_event.AddParameterToList(p_name => 'P_SEQUENCE_NUMBER', p_value => P_SEQUENCE_NUMBER , p_parameterlist => l_parameter_list);
1870 wf_event.AddParameterToList(p_name => 'P_QUALIFYING_TYPE_CODE', p_value => P_QUALIFYING_TYPE_CODE, p_parameterlist => l_parameter_list);
1871 wf_event.AddParameterToList(p_name => 'P_QUALIFYING_CODE_ID_NEW', p_value => P_QUALIFYING_CODE_ID_NEW, p_parameterlist => l_parameter_list);
1872 wf_event.AddParameterToList(p_name => 'P_QUALIFYING_CODE_ID_OLD', p_value => P_QUALIFYING_CODE_ID_OLD, p_parameterlist => l_parameter_list);
1873 wf_event.AddParameterToList(p_name => 'P_QUALIFYING_VALUE_NEW', p_value => P_QUALIFYING_VALUE_NEW, p_parameterlist => l_parameter_list);
1874 wf_event.AddParameterToList(p_name => 'P_QUALIFYING_VALUE_OLD', p_value => P_QUALIFYING_VALUE_OLD, p_parameterlist => l_parameter_list);
1875 wf_event.AddParameterToList(p_name => 'P_WF_ROLE', p_value => l_wf_role, p_parameterlist => l_parameter_list);
1876
1877 wf_event.raise( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameters => l_parameter_list);
1878 l_parameter_list.DELETE;
1879
1880 EXCEPTION
1881 WHEN OTHERS THEN
1882 IF cur_user_name%ISOPEN THEN
1883 CLOSE cur_user_name;
1884 END IF;
1885 IF c_seq_num%ISOPEN THEN
1886 CLOSE c_seq_num;
1887 END IF;
1888 WF_CORE.CONTEXT('IGS_AD_WF_001', 'APP_INST_QUALIFYING_CODE_EVENT ','IGSADIDX'||l_seq_val,lv_raise_event);
1889 raise;
1890 END APP_INST_QUALIFYING_CODE_EVENT;
1891
1892 PROCEDURE PRE_SUBMIT_EVENT
1893 (
1894 P_PERSON_ID IN NUMBER,
1895 P_SS_ADM_APPL_ID IN NUMBER,
1896 P_RETURN_STATUS IN OUT NOCOPY VARCHAR2,
1897 P_MSG_DATA OUT NOCOPY VARCHAR2
1898 ) AS
1899 -- Get a unique sequence number
1900 CURSOR c_seq_num IS
1901 SELECT IGS_AD_BE_PRESUB_APPL_S.NEXTVAL
1902 FROM sys.dual;
1903
1904
1905 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1906
1907 l_seq_val NUMBER;
1908 lv_raise_event VARCHAR2(100);
1909 BEGIN
1910
1911 OPEN c_seq_num;
1912 FETCH c_seq_num INTO l_seq_val ;
1913 CLOSE c_seq_num ;
1914
1915 lv_raise_event := 'oracle.apps.igs.ad.onsubmit_application';
1916
1917 wf_event.AddParameterToList(p_name => 'P_PERSON_ID', p_value => P_PERSON_ID, p_parameterlist=> l_parameter_list);
1918 wf_event.AddParameterToList(p_name => 'P_SS_ADM_APPL_ID', p_value => P_SS_ADM_APPL_ID, p_parameterlist => l_parameter_list);
1919 wf_event.AddParameterToList(p_name => 'P_RETURN_STATUS', p_value => P_RETURN_STATUS , p_parameterlist => l_parameter_list);
1920 wf_event.AddParameterToList(p_name => 'P_MSG_DATA', p_value => P_MSG_DATA, p_parameterlist => l_parameter_list);
1921
1922 wf_event.raise3( p_event_name => lv_raise_event, p_event_key => l_seq_val, p_parameter_list => l_parameter_list);
1923 p_return_status := wf_event.GetValueForParameter(p_name => 'P_RETURN_STATUS',p_parameterlist =>l_parameter_list);
1924 p_msg_data := wf_event.GetValueForParameter(p_name => 'P_MSG_DATA',p_parameterlist =>l_parameter_list);
1925 l_parameter_list.DELETE;
1926
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 IF c_seq_num%ISOPEN THEN
1930 CLOSE c_seq_num;
1931 END IF;
1932 WF_CORE.CONTEXT('IGS_AD_WF_001', 'PRE_SUBMIT_EVENT ','IGSADIDX '||l_seq_val,lv_raise_event);
1933 raise;
1934 END PRE_SUBMIT_EVENT;
1935
1936 END igs_ad_wf_001;