[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_INST_APP
Source
1 PACKAGE BODY igf_ap_inst_app AS
2 /* $Header: IGFAP50B.pls 120.0 2005/09/09 17:14:44 appldev noship $ */
3
4
5 PROCEDURE update_ToDo_status(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
6 p_application_code igf_ap_appl_status_all.application_code%TYPE,
7 x_return_status OUT NOCOPY VARCHAR2)
8 IS
9 ------------------------------------------------------------------
10 --Created by : upinjark, Oracle India
11 --Date created: 04-Jul-2004
12 --
13 --Purpose:
14 --
15 --
16 --Known limitations/enhancements and/or remarks:
17 --
18 --Change History:
19 --Who When What
20 -------------------------------------------------------------------
21
22 --Cursor to fetch to do item
23 CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
24 cp_application_code igf_ap_appl_status_all.application_code%TYPE)
25 IS
26 SELECT tdmst.todo_number
27 FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
28 WHERE tdmst.system_todo_type_code = 'INSTAPP'
29 AND baseRec.ci_cal_type = tdmst.ci_cal_type
30 AND baseRec.ci_sequence_number = tdmst.ci_sequence_number
31 AND baseRec.base_id = tdinst.base_id
32 AND tdmst.application_code = cp_application_code
33 AND tdinst.item_sequence_number = tdmst.todo_number
34 AND tdinst.base_id = cp_base_id;
35
36 l_td_item c_td_item%ROWTYPE;
37
38 l_todo_status VARCHAR(10) ;
39
40 BEGIN
41 x_return_status := 'F';
42 l_todo_status := NULL ;
43 --get the to do item sequence number
44 OPEN c_td_item(p_base_id,p_application_code);
45 FETCH c_td_item INTO l_td_item;
46 CLOSE c_td_item;
47
48 IF (l_td_item.todo_number IS NULL) THEN
49 x_return_status := 'N' ; -- for no To-Do item is available
50 ELSE
51 igf_ap_gen.update_td_status(
52 p_base_id => p_base_id ,
53 p_item_sequence_number => l_td_item.todo_number ,
54 p_status => 'COM',
55 p_return_status => l_todo_status
56 );
57
58 IF(l_todo_status <> 'F') THEN
59 x_return_status := 'T'; --- for successful updation of To-DO
60 ELSE
61 x_return_status := 'F'; --- for failure in updation of To-do
62 END IF;
63 END IF;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 ROLLBACK;
68 x_return_status := 'F';
69 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
70 igs_ge_msg_stack.add;
71
72 END update_ToDo_status;
73
74 PROCEDURE update_ToDo (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
75 p_application_code igf_ap_appl_status_all.application_code%TYPE,
76 p_status IN igf_ap_td_item_inst_all.status%TYPE,
77 x_return_status OUT NOCOPY VARCHAR2)
78 IS
79 ------------------------------------------------------------------
80 --Created by : upinjark, Oracle India
81 --Date created: 08-Aug-2004
82 --
83 --Purpose:
84 --
85 --
86 --Known limitations/enhancements and/or remarks:
87 --
88 --Change History:
89 --Who When What
90 -------------------------------------------------------------------
91
92 --Cursor to fetch to do item
93 CURSOR c_td_item (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
94 cp_application_code igf_ap_appl_status_all.application_code%TYPE)
95 IS
96 SELECT tdmst.todo_number
97 FROM igf_ap_td_item_inst_all tdinst, igf_ap_td_item_mst_all tdmst, Igf_ap_fa_base_rec_all baseRec
98 WHERE tdmst.system_todo_type_code = 'INSTAPP'
99 AND baseRec.ci_cal_type = tdmst.ci_cal_type
100 AND baseRec.ci_sequence_number = tdmst.ci_sequence_number
101 AND baseRec.base_id = tdinst.base_id
102 AND tdmst.application_code = cp_application_code
103 AND tdinst.item_sequence_number = tdmst.todo_number
104 AND tdinst.base_id = cp_base_id;
105
106 l_td_item c_td_item%ROWTYPE;
107
108 l_todo_status VARCHAR(10) ;
109
110 BEGIN
111 x_return_status := 'F';
112 l_todo_status := NULL ;
113 --get the to do item sequence number
114 OPEN c_td_item(p_base_id,p_application_code);
115 FETCH c_td_item INTO l_td_item;
116 CLOSE c_td_item;
117
118 IF (l_td_item.todo_number IS NULL) THEN
119 x_return_status := 'N' ; -- for no To-Do item is available
120 ELSE
121 igf_ap_gen.update_td_status(
122 p_base_id => p_base_id ,
123 p_item_sequence_number => l_td_item.todo_number ,
124 p_status => p_status,
125 p_return_status => l_todo_status
126 );
127
128 IF(l_todo_status <> 'F') THEN
129 x_return_status := 'T'; --- for successful updation of To-DO
130 ELSE
131 x_return_status := 'F'; --- for failure in updation of To-do
132 END IF;
133 END IF;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 ROLLBACK;
138 x_return_status := 'F';
139 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
140 igs_ge_msg_stack.add;
141
142 END update_ToDo;
143
144
145
146 PROCEDURE update_app_status(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
147 p_application_code igf_ap_appl_status_all.application_code%TYPE,
148 p_application_status_code igf_ap_appl_status_all.application_status_code%TYPE,
149 x_return_status OUT NOCOPY VARCHAR2)
150 IS
151 ------------------------------------------------------------------
152 --Created by : upinjark, Oracle India
153 --Date created: 04-Jul-2004
154 --
155 --Purpose:
156 --
157 --
158 --Known limitations/enhancements and/or remarks:
159 --
160 --Change History:
161 --Who When What
162 -------------------------------------------------------------------
163
164 -- cursor to get the row from application status table
165 CURSOR c_appl_status (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
166 cp_application_code igf_ap_appl_status_all.application_code%TYPE
167 ) IS
168 SELECT rowid
169 FROM IGF_AP_APPL_STATUS_ALL aps
170 WHERE BASE_ID = cp_base_id
171 AND APPLICATION_CODE = cp_application_code;
172
173 l_appl_status c_appl_status%ROWTYPE;
174
175 BEGIN
176 x_return_status := 'F';
177
178 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
179 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_inst_app.update_app_status.debug','calling igf_ap_appl_status_all_pkg.update_row');
180 END IF;
181
182
183 OPEN c_appl_status(p_base_id, p_application_code);
184 FETCH c_appl_status INTO l_appl_status;
185 CLOSE c_appl_status;
186
187 igf_ap_appl_status_pkg.update_row (
188 x_rowid => l_appl_status.rowid,
189 x_base_id => p_base_id,
190 x_application_code => p_application_code,
191 x_application_status_code => p_application_status_code
192 );
193
194 x_return_status := 'T';
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 ROLLBACK;
199 x_return_status := 'F';
200 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
201 igs_ge_msg_stack.add;
202
203 END update_app_status;
204
205
206 PROCEDURE update_Ant_Data_For_All_Terms (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
207 p_cal_type igs_ca_inst_all.cal_type%TYPE,
208 p_seq_number igs_ca_inst_all.sequence_number%TYPE,
209 p_ant_data_column VARCHAR2,
210 p_ant_data_value VARCHAR2,
211 x_return_status OUT NOCOPY VARCHAR2,
212 p_override_flag VARCHAR2
213 )
214 IS
215 ------------------------------------------------------------------
216 --Created by : upinjark, Oracle India
217 --Date created: 04-Jul-2004
218 --
219 --Purpose:
220 --
221 --
222 --Known limitations/enhancements and/or remarks:
223 --
224 --Change History:
225 --Who When What
226 -------------------------------------------------------------------
227
228 -- cursor to fetch the terms withinn an award year
229 CURSOR c_all_terms(cp_sup_cal_type igs_ca_inst_all.cal_type%TYPE,
230 cp_sup_seq_number igs_ca_inst_all.sequence_number%TYPE)
231 IS
232 SELECT SUB_CAL_TYPE, SUB_CI_SEQUENCE_NUMBER
233 FROM IGS_CA_INST_REL rel, IGS_CA_TYPE typ
234 WHERE rel.SUP_CAL_TYPE = cp_sup_cal_type
235 AND rel.SUP_CI_SEQUENCE_NUMBER = cp_sup_seq_number
236 AND rel.sub_cal_type=typ.cal_type
237 AND typ.s_cal_cat='LOAD';
238
239 l_get_cal_typ_seq_num c_all_terms%ROWTYPE;
240
241 -- to fetch all the anticipated value for the given base_id and award year
242 CURSOR c_get_ant_data(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
243 cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
244 cp_ld_seq_number igs_ca_inst_all.sequence_number%TYPE
245 )
246 IS
247 SELECT *
248 FROM IGF_AP_FA_ANT_DATA
249 WHERE BASE_ID = CP_BASE_ID
250 AND LD_CAL_TYPE = cp_ld_cal_type
251 AND LD_SEQUENCE_NUMBER = cp_ld_seq_number;
252
253 l_ant_data_row c_get_ant_data%ROWTYPE;
254
255 l_row_id VARCHAR2(25);
256
257 BEGIN
258 l_get_cal_typ_seq_num := NULL;
259 l_row_id := NULL ;
260 x_return_status := 'F';
261
262 /*OPEN c_all_terms(p_cal_type,
263 p_seq_number
264 );
265
266 FETCH c_all_terms INTO l_get_cal_typ_seq_num;
267 CLOSE c_all_terms;*/
268
269 FOR l_get_cal_typ_seq_num IN c_all_terms(p_cal_type, p_seq_number) LOOP
270 OPEN c_get_ant_data(p_base_id, l_get_cal_typ_seq_num.sub_cal_type,l_get_cal_typ_seq_num.sub_ci_sequence_number);
271 FETCH c_get_ant_data INTO l_ant_data_row;
272 CLOSE c_get_ant_data;
273
274 IF p_override_flag = 'Y' THEN
275 IF p_ant_data_column = 'ORG_UNIT_CD' THEN
276 l_ant_data_row.ORG_UNIT_CD := p_ant_data_value;
277 ELSIF p_ant_data_column = 'PROGRAM_TYPE' THEN
278 l_ant_data_row.PROGRAM_TYPE := p_ant_data_value;
279 ELSIF p_ant_data_column = 'PROGRAM_LOCATION_CD' THEN
280 l_ant_data_row.PROGRAM_LOCATION_CD := p_ant_data_value;
281 ELSIF p_ant_data_column = 'PROGRAM_CD' THEN
282 l_ant_data_row.PROGRAM_CD := p_ant_data_value;
283 ELSIF p_ant_data_column = 'CLASS_STANDING' THEN
284 l_ant_data_row.CLASS_STANDING := p_ant_data_value;
285 ELSIF p_ant_data_column = 'RESIDENCY_STATUS_CODE' THEN
286 l_ant_data_row.RESIDENCY_STATUS_CODE := p_ant_data_value;
287 ELSIF p_ant_data_column = 'HOUSING_STATUS_CODE' THEN
288 l_ant_data_row.HOUSING_STATUS_CODE := p_ant_data_value;
289 ELSIF p_ant_data_column = 'ATTENDANCE_TYPE' THEN
290 l_ant_data_row.ATTENDANCE_TYPE := p_ant_data_value;
291 ELSIF p_ant_data_column = 'ATTENDANCE_MODE' THEN
292 l_ant_data_row.ATTENDANCE_MODE := p_ant_data_value;
293 ELSIF p_ant_data_column = 'MONTHS_ENROLLED_NUM' THEN
294 l_ant_data_row.MONTHS_ENROLLED_NUM := p_ant_data_value;
295 ELSIF p_ant_data_column = 'CREDIT_POINTS_NUM' THEN
296 l_ant_data_row.CREDIT_POINTS_NUM := p_ant_data_value;
297 END IF;
298
299 ELSE
300 IF p_ant_data_column = 'ORG_UNIT_CD' THEN
301 IF l_ant_data_row.ORG_UNIT_CD IS NULL THEN
302 l_ant_data_row.ORG_UNIT_CD := p_ant_data_value;
303 END IF;
304
305 ELSIF p_ant_data_column = 'PROGRAM_TYPE' THEN
306 IF l_ant_data_row.PROGRAM_TYPE IS NULL THEN
307 l_ant_data_row.PROGRAM_TYPE := p_ant_data_value;
308 END IF;
309
310 ELSIF p_ant_data_column = 'PROGRAM_LOCATION_CD' THEN
311 IF l_ant_data_row.PROGRAM_LOCATION_CD IS NULL THEN
312 l_ant_data_row.PROGRAM_LOCATION_CD := p_ant_data_value;
313 END IF;
314
315 ELSIF p_ant_data_column = 'PROGRAM_CD' THEN
316 IF l_ant_data_row.PROGRAM_CD IS NULL THEN
317 l_ant_data_row.PROGRAM_CD := p_ant_data_value;
318 END IF;
319
320 ELSIF p_ant_data_column = 'CLASS_STANDING' THEN
321 IF l_ant_data_row.CLASS_STANDING IS NULL THEN
322 l_ant_data_row.CLASS_STANDING := p_ant_data_value;
323 END IF;
324
325 ELSIF p_ant_data_column = 'RESIDENCY_STATUS_CODE' THEN
326 IF l_ant_data_row.RESIDENCY_STATUS_CODE IS NULL THEN
327 l_ant_data_row.RESIDENCY_STATUS_CODE := p_ant_data_value;
328 END IF;
329
330 ELSIF p_ant_data_column = 'HOUSING_STATUS_CODE' THEN
331 IF l_ant_data_row.HOUSING_STATUS_CODE IS NULL THEN
332 l_ant_data_row.HOUSING_STATUS_CODE := p_ant_data_value;
333 END IF;
334
335 ELSIF p_ant_data_column = 'ATTENDANCE_TYPE' THEN
336 IF l_ant_data_row.ATTENDANCE_TYPE IS NULL THEN
337 l_ant_data_row.ATTENDANCE_TYPE := p_ant_data_value;
338 END IF;
339
340 ELSIF p_ant_data_column = 'ATTENDANCE_MODE' THEN
341 IF l_ant_data_row.ATTENDANCE_MODE IS NULL THEN
342 l_ant_data_row.ATTENDANCE_MODE := p_ant_data_value;
343 END IF;
344
345 ELSIF p_ant_data_column = 'MONTHS_ENROLLED_NUM' THEN
346 IF l_ant_data_row.MONTHS_ENROLLED_NUM IS NULL THEN
347 l_ant_data_row.MONTHS_ENROLLED_NUM := p_ant_data_value;
348 END IF;
349
350 ELSIF p_ant_data_column = 'CREDIT_POINTS_NUM' THEN
351 IF l_ant_data_row.CREDIT_POINTS_NUM IS NULL THEN
352 l_ant_data_row.CREDIT_POINTS_NUM := p_ant_data_value;
353 END IF;
354 END IF;
355
356 END IF;
357
358 igf_ap_fa_ant_data_pkg.add_row
359 (
360 x_mode => 'R',
361 x_rowid => l_row_id,
362 x_base_id => p_base_id,
363 x_ld_cal_type => l_get_cal_typ_seq_num.sub_cal_type,
364 x_ld_sequence_number => l_get_cal_typ_seq_num.sub_ci_sequence_number,
365 x_org_unit_cd => l_ant_data_row.org_unit_cd ,
366 x_program_type => l_ant_data_row.program_type,
367 x_program_location_cd => l_ant_data_row.program_location_cd,
368 x_program_cd => l_ant_data_row.program_cd,
369 x_class_standing => l_ant_data_row.class_standing,
370 x_residency_status_code => l_ant_data_row.residency_status_code,
371 x_housing_status_code => l_ant_data_row.housing_status_code,
372 x_attendance_type => l_ant_data_row.attendance_type,
373 x_attendance_mode => l_ant_data_row.attendance_mode,
374 x_months_enrolled_num => l_ant_data_row.months_enrolled_num,
375 x_credit_points_num => l_ant_data_row.credit_points_num
376 );
377
378 END LOOP;
379
380 x_return_status := 'T';
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 ROLLBACK;
385 x_return_status := 'F';
386 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
387 igs_ge_msg_stack.add;
388
389 END update_Ant_Data_For_All_Terms;
390
391
392
393 PROCEDURE update_Ant_Data_a_Term(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
394 p_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
395 p_ld_seq_number igs_ca_inst_all.sequence_number%TYPE,
396 p_ant_data_column VARCHAR2,
397 p_ant_data_value VARCHAR2,
398 x_return_status OUT NOCOPY VARCHAR2,
399 p_override_flag VARCHAR2
400 )
401 IS
402 ------------------------------------------------------------------
403 --Created by : upinjark, Oracle India
404 --Date created: 04-Jul-2004
405 --
406 --Purpose:
407 --
408 --
409 --Known limitations/enhancements and/or remarks:
410 --
411 --Change History:
412 --Who When What
413 -------------------------------------------------------------------
414
415
416 -- to fetch all the anticipated value for the given base_id and award year
417 CURSOR c_get_ant_data(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
418 cp_ld_cal_type igs_ca_inst_all.cal_type%TYPE,
419 cp_ld_seq_number igs_ca_inst_all.sequence_number%TYPE
420 )
421 IS
422 SELECT *
423 FROM IGF_AP_FA_ANT_DATA
424 WHERE BASE_ID = CP_BASE_ID
425 AND LD_CAL_TYPE = cp_ld_cal_type
426 AND LD_SEQUENCE_NUMBER = cp_ld_seq_number;
427
428 l_ant_data_row c_get_ant_data%ROWTYPE;
429
430 l_row_id VARCHAR2(25);
431
432 BEGIN
433 l_row_id := NULL ;
434 x_return_status := 'F';
435
436 OPEN c_get_ant_data(p_base_id, p_ld_cal_type, p_ld_seq_number);
437 FETCH c_get_ant_data INTO l_ant_data_row;
438 CLOSE c_get_ant_data;
439
440 IF p_override_flag = 'Y' THEN
441 IF p_ant_data_column = 'ORG_UNIT_CD' THEN
442 l_ant_data_row.ORG_UNIT_CD := p_ant_data_value;
443 ELSIF p_ant_data_column = 'PROGRAM_TYPE' THEN
444 l_ant_data_row.PROGRAM_TYPE := p_ant_data_value;
445 ELSIF p_ant_data_column = 'PROGRAM_LOCATION_CD' THEN
446 l_ant_data_row.PROGRAM_LOCATION_CD := p_ant_data_value;
447 ELSIF p_ant_data_column = 'PROGRAM_CD' THEN
448 l_ant_data_row.PROGRAM_CD := p_ant_data_value;
449 ELSIF p_ant_data_column = 'CLASS_STANDING' THEN
450 l_ant_data_row.CLASS_STANDING := p_ant_data_value;
451 ELSIF p_ant_data_column = 'RESIDENCY_STATUS_CODE' THEN
452 l_ant_data_row.RESIDENCY_STATUS_CODE := p_ant_data_value;
453 ELSIF p_ant_data_column = 'HOUSING_STATUS_CODE' THEN
454 l_ant_data_row.HOUSING_STATUS_CODE := p_ant_data_value;
455 ELSIF p_ant_data_column = 'ATTENDANCE_TYPE' THEN
456 l_ant_data_row.ATTENDANCE_TYPE := p_ant_data_value;
457 ELSIF p_ant_data_column = 'ATTENDANCE_MODE' THEN
458 l_ant_data_row.ATTENDANCE_MODE := p_ant_data_value;
459 ELSIF p_ant_data_column = 'MONTHS_ENROLLED_NUM' THEN
460 l_ant_data_row.MONTHS_ENROLLED_NUM := p_ant_data_value;
461 ELSIF p_ant_data_column = 'CREDIT_POINTS_NUM' THEN
462 l_ant_data_row.CREDIT_POINTS_NUM := p_ant_data_value;
463 END IF;
464
465 ELSE
466 IF p_ant_data_column = 'ORG_UNIT_CD' THEN
467 IF l_ant_data_row.ORG_UNIT_CD IS NULL THEN
468 l_ant_data_row.ORG_UNIT_CD := p_ant_data_value;
469 END IF;
470
471 ELSIF p_ant_data_column = 'PROGRAM_TYPE' THEN
472 IF l_ant_data_row.PROGRAM_TYPE IS NULL THEN
473 l_ant_data_row.PROGRAM_TYPE := p_ant_data_value;
474 END IF;
475
476 ELSIF p_ant_data_column = 'PROGRAM_LOCATION_CD' THEN
477 IF l_ant_data_row.PROGRAM_LOCATION_CD IS NULL THEN
478 l_ant_data_row.PROGRAM_LOCATION_CD := p_ant_data_value;
479 END IF;
480
481 ELSIF p_ant_data_column = 'PROGRAM_CD' THEN
482 IF l_ant_data_row.PROGRAM_CD IS NULL THEN
483 l_ant_data_row.PROGRAM_CD := p_ant_data_value;
484 END IF;
485
486 ELSIF p_ant_data_column = 'CLASS_STANDING' THEN
487 IF l_ant_data_row.CLASS_STANDING IS NULL THEN
488 l_ant_data_row.CLASS_STANDING := p_ant_data_value;
489 END IF;
490
491 ELSIF p_ant_data_column = 'RESIDENCY_STATUS_CODE' THEN
492 IF l_ant_data_row.RESIDENCY_STATUS_CODE IS NULL THEN
493 l_ant_data_row.RESIDENCY_STATUS_CODE := p_ant_data_value;
494 END IF;
495
496 ELSIF p_ant_data_column = 'HOUSING_STATUS_CODE' THEN
497 IF l_ant_data_row.HOUSING_STATUS_CODE IS NULL THEN
498 l_ant_data_row.HOUSING_STATUS_CODE := p_ant_data_value;
499 END IF;
500
501 ELSIF p_ant_data_column = 'ATTENDANCE_TYPE' THEN
502 IF l_ant_data_row.ATTENDANCE_TYPE IS NULL THEN
503 l_ant_data_row.ATTENDANCE_TYPE := p_ant_data_value;
504 END IF;
505
506 ELSIF p_ant_data_column = 'ATTENDANCE_MODE' THEN
507 IF l_ant_data_row.ATTENDANCE_MODE IS NULL THEN
508 l_ant_data_row.ATTENDANCE_MODE := p_ant_data_value;
509 END IF;
510
511 ELSIF p_ant_data_column = 'MONTHS_ENROLLED_NUM' THEN
512 IF l_ant_data_row.MONTHS_ENROLLED_NUM IS NULL THEN
513 l_ant_data_row.MONTHS_ENROLLED_NUM := p_ant_data_value;
514 END IF;
515
516 ELSIF p_ant_data_column = 'CREDIT_POINTS_NUM' THEN
517 IF l_ant_data_row.CREDIT_POINTS_NUM IS NULL THEN
518 l_ant_data_row.CREDIT_POINTS_NUM := p_ant_data_value;
519 END IF;
520 END IF;
521
522 END IF;
523
524
525
526 igf_ap_fa_ant_data_pkg.add_row
527 (
528 x_mode => 'R',
529 x_rowid => l_row_id,
530 x_base_id => p_base_id,
531 x_ld_cal_type => p_ld_cal_type,
532 x_ld_sequence_number => p_ld_seq_number,
533 x_org_unit_cd => l_ant_data_row.org_unit_cd ,
534 x_program_type => l_ant_data_row.program_type,
535 x_program_location_cd => l_ant_data_row.program_location_cd,
536 x_program_cd => l_ant_data_row.program_cd,
537 x_class_standing => l_ant_data_row.class_standing,
538 x_residency_status_code => l_ant_data_row.residency_status_code,
539 x_housing_status_code => l_ant_data_row.housing_status_code,
540 x_attendance_type => l_ant_data_row.attendance_type,
541 x_attendance_mode => l_ant_data_row.attendance_mode,
542 x_months_enrolled_num => l_ant_data_row.months_enrolled_num,
543 x_credit_points_num => l_ant_data_row.credit_points_num
544 );
545
546 x_return_status := 'T';
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 ROLLBACK;
551 x_return_status := 'F';
552 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
553 igs_ge_msg_stack.add;
554
555 END update_Ant_Data_a_Term;
556
557
558 PROCEDURE raise_event_on_IA_submit (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
559 p_application_code igf_ap_appl_status_all.application_code%TYPE,
560 x_return_status OUT NOCOPY VARCHAR2)
561 IS
562 ------------------------------------------------------------------
563 --Created by : upinjark, Oracle India
564 --Date created: 04-Jul-2004
565 --
566 --Purpose:
567 --
568 --
569 --Known limitations/enhancements and/or remarks:
570 --
571 --Change History:
572 --Who When What
573 -------------------------------------------------------------------
574
575 -- Get person number
576 CURSOR c_person_number(
577 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
578 ) IS
579 SELECT hz.party_number
580 FROM hz_parties hz,
581 igf_ap_fa_base_rec_all fa
582 WHERE fa.person_id = hz.party_id
583 AND fa.base_id = cp_base_id;
584 l_person_number hz_parties.party_number%TYPE;
585
586
587 -- Get award year alternate code
588 CURSOR c_award_year(
589 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
590 ) IS
591 SELECT ca.alternate_code
592 FROM igs_ca_inst_all ca,
593 igf_ap_fa_base_rec_all fa
594 WHERE fa.base_id = cp_base_id
595 AND fa.ci_cal_type = ca.cal_type
596 AND fa.ci_sequence_number = ca.sequence_number;
597 l_alternate_code igs_ca_inst_all.alternate_code%TYPE;
598
599
600 -- Get Institutional application name
601 CURSOR c_application_name(
602 cp_application_code igf_ap_appl_setup_all.application_name%TYPE
603 ) IS
604 SELECT APPLICATION_NAME
605 FROM IGF_AP_APPL_SETUP_ALL
606 WHERE APPLICATION_CODE = cp_application_code
607 AND ROWNUM = 1;
608 l_application_name igf_ap_appl_setup_all.application_name%TYPE;
609
610 l_seq_val NUMBER;
611
612 l_wf_event_t WF_EVENT_T;
613 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
614 lv_event_name VARCHAR2(4000);
615
616 BEGIN
617 x_return_status := 'F';
618
619 OPEN c_person_number(p_base_id);
620 FETCH c_person_number INTO l_person_number;
621 CLOSE c_person_number;
622
623 OPEN c_award_year(p_base_id);
624 FETCH c_award_year INTO l_alternate_code;
625 CLOSE c_award_year;
626
627 OPEN c_application_name(p_application_code);
628 FETCH c_application_name INTO l_application_name;
629 CLOSE c_application_name;
630
631 SELECT igs_pe_res_chg_s.nextval INTO l_seq_val FROM DUAL;
632
633 -- Initialize the wf_event_t object
634 WF_EVENT_T.Initialize(l_wf_event_t);
635 -- Set the event name
636 lv_event_name := 'oracle.apps.igf.ap.InstAppSubmitted';
637
638 l_wf_event_t.setEventName(pEventName => lv_event_name);
639
640 -- Set the event key
641 l_wf_event_t.setEventKey(
642 pEventKey => lv_event_name || l_seq_val
643 );
644
645 -- Set the parameter list
646 l_wf_event_t.setParameterList(
647 pParameterList => l_wf_parameter_list_t
648 );
649
650 -- Set the message's subject
651 fnd_message.set_name('IGF','IGF_AP_INSTAPPSUBMITTED_SUBJ');
652
653 wf_event.addparametertolist(
654 p_name => 'SUBJECT',
655 p_value => 'IGF_AP_INSTAPPSUBMITTED_SUBJ', --fnd_message.get,
656 p_parameterlist => l_wf_parameter_list_t
657 );
658
659 -- Set the person number
660 wf_event.addparametertolist(
661 p_name => 'STUDENT_NUMBER',
662 p_value => l_person_number,
663 p_parameterlist => l_wf_parameter_list_t
664 );
665
666 -- Set the to do item description
667 wf_event.addparametertolist(
668 p_name => 'APPLICATION_NAME',
669 p_value => l_application_name,
670 p_parameterlist => l_wf_parameter_list_t
671 );
672
673 -- Set the award year alternate code
674 wf_event.addparametertolist(
675 p_name => 'AWARD_YEAR',
676 p_value => l_alternate_code,
677 p_parameterlist => l_wf_parameter_list_t
678 );
679
680 wf_Event.raise(
681 p_event_name => lv_event_name,
682 p_event_key => lv_event_name || l_seq_val,
683 p_parameters => l_wf_parameter_list_t
684 );
685 x_return_status := 'T';
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 ROLLBACK;
690 x_return_status := 'F';
691 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
692 igs_ge_msg_stack.add;
693
694 END raise_event_on_IA_submit;
695
696
697 END igf_ap_inst_app;