APPS.IGS_AD_AC_COMP dependencies on IGS_AD_PS_APPL_INST

Line 20: | new columns being added to IGS_AD_PS_APPL_INST

16: | Who When What |
17: | brajendr 9-Aug-2001 Incorporaetd the review comments |
18: | brajendr 3-Aug-2001 Creation of the Initial Code |
19: | cdcruz 18-feb-2002 bug 2217104 Admit to future term Enhancement,updated tbh call for
20: | new columns being added to IGS_AD_PS_APPL_INST
21: | hreddych 4-apr-2002 bug 2273789 The function get_cmp_apltritm was returning TRUE
22: | if one of the tracking status was complete which was modified to
23: | to return TRUE if all are complete
24: | nshee 29-Aug-2002 Bug 2395510 added 6 columns as part of deferments build

Line 41: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,

37: -- Declare all Global variables and global constants
38:
39:
40: FUNCTION get_cmp_apltritm(
41: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
42: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
43: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
44: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
45: ) RETURN BOOLEAN AS

Line 42: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,

38:
39:
40: FUNCTION get_cmp_apltritm(
41: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
42: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
43: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
44: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
45: ) RETURN BOOLEAN AS
46: /*

Line 43: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,

39:
40: FUNCTION get_cmp_apltritm(
41: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
42: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
43: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
44: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
45: ) RETURN BOOLEAN AS
46: /*
47: || Created By : brajendr

Line 44: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE

40: FUNCTION get_cmp_apltritm(
41: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
42: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
43: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
44: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
45: ) RETURN BOOLEAN AS
46: /*
47: || Created By : brajendr
48: || Created On :

Line 58: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,

54: */
55:
56: -- Get all the tracking step details from for each application of the PERSON.
57: CURSOR cur_tr(
58: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
59: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
60: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
61: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
62: ) IS

Line 59: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,

55:
56: -- Get all the tracking step details from for each application of the PERSON.
57: CURSOR cur_tr(
58: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
59: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
60: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
61: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
62: ) IS
63: SELECT ti.tracking_id

Line 60: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,

56: -- Get all the tracking step details from for each application of the PERSON.
57: CURSOR cur_tr(
58: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
59: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
60: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
61: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
62: ) IS
63: SELECT ti.tracking_id
64: FROM igs_ad_aplins_admreq aa,

Line 61: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE

57: CURSOR cur_tr(
58: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
59: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
60: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
61: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
62: ) IS
63: SELECT ti.tracking_id
64: FROM igs_ad_aplins_admreq aa,
65: igs_tr_item ti,

Line 109: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,

105: END get_cmp_apltritm;
106:
107:
108: PROCEDURE get_cpti_apcmp(
109: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
110: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
111: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
112: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
113: ) AS

Line 110: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,

106:
107:
108: PROCEDURE get_cpti_apcmp(
109: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
110: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
111: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
112: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
113: ) AS
114: /*

Line 111: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,

107:
108: PROCEDURE get_cpti_apcmp(
109: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
110: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
111: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
112: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
113: ) AS
114: /*
115: || Created By : brajendr

Line 112: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE

108: PROCEDURE get_cpti_apcmp(
109: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
110: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
111: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
112: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
113: ) AS
114: /*
115: || Created By : brajendr
116: || Created On :

Line 121: || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all

117: || Purpose : This procedure will update given Application Instance to the COMPLETE all the Tracking Items under this are COMPLTE
118: || Known limitations, enhancements or remarks :
119: || Change History :
120: || Who When What
121: || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
122: || (reverse chronological order - newest change first)
123: */
124:
125: -- Get the details of

Line 135: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,

131: AND system_default_ind= 'Y';
132:
133: -- Get the details of
134: CURSOR cur_apcmp(
135: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
136: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139: ) IS

Line 136: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,

132:
133: -- Get the details of
134: CURSOR cur_apcmp(
135: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
136: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139: ) IS
140: SELECT *

Line 137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,

133: -- Get the details of
134: CURSOR cur_apcmp(
135: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
136: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139: ) IS
140: SELECT *
141: FROM igs_ad_ps_appl_inst ain

Line 138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE

134: CURSOR cur_apcmp(
135: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
136: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139: ) IS
140: SELECT *
141: FROM igs_ad_ps_appl_inst ain
142: WHERE ain.person_id = p_person_id

Line 141: FROM igs_ad_ps_appl_inst ain

137: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
138: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE
139: ) IS
140: SELECT *
141: FROM igs_ad_ps_appl_inst ain
142: WHERE ain.person_id = p_person_id
143: AND ain.admission_appl_number = p_admission_appl_number
144: AND ain.course_cd = p_course_cd
145: AND ain.sequence_number = p_sequence_number;

Line 182: igs_ad_ps_appl_inst_pkg.update_row(

178: FOR cur_adm_doc_status_rec IN cur_adm_doc_status LOOP
179:
180: -- Update the tracking item status.
181: FOR cur_apcmp_rec IN cur_apcmp( p_person_id, p_admission_appl_number, p_course_cd, p_sequence_number) LOOP
182: igs_ad_ps_appl_inst_pkg.update_row(
183: -- cur_adm_doc_status_rec.adm_doc_status
184: x_rowid => cur_apcmp_rec.row_id,
185: x_person_id => cur_apcmp_rec.person_id,
186: x_admission_appl_number => cur_apcmp_rec.admission_appl_number,

Line 352: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,

348:
349: PROCEDURE upd_apl_cmp_st(
350: ERRBUF OUT NOCOPY VARCHAR2,
351: RETCODE OUT NOCOPY NUMBER,
352: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
353: p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
354: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
355: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
356: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,

Line 354: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,

350: ERRBUF OUT NOCOPY VARCHAR2,
351: RETCODE OUT NOCOPY NUMBER,
352: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
353: p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
354: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
355: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
356: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
357: p_calendar_details IN VARCHAR2,
358: p_admission_process_category IN VARCHAR2,

Line 355: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,

351: RETCODE OUT NOCOPY NUMBER,
352: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
353: p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
354: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
355: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
356: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
357: p_calendar_details IN VARCHAR2,
358: p_admission_process_category IN VARCHAR2,
359: p_org_id IN igs_fi_posting_int_all.org_id%TYPE

Line 356: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,

352: p_person_id IN igs_ad_ps_appl_inst.person_id%TYPE,
353: p_person_id_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
354: p_admission_appl_number IN igs_ad_ps_appl_inst.admission_appl_number%TYPE,
355: p_course_cd IN igs_ad_ps_appl_inst.course_cd%TYPE,
356: p_sequence_number IN igs_ad_ps_appl_inst.sequence_number%TYPE,
357: p_calendar_details IN VARCHAR2,
358: p_admission_process_category IN VARCHAR2,
359: p_org_id IN igs_fi_posting_int_all.org_id%TYPE
360: ) AS

Line 378: TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,

374:
375:
376: TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
377: c_dyn_pig_check c_dyn_pig_checkCurTyp;
378: TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
379: admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
380: course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
381: sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
382: c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;

Line 379: admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,

375:
376: TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
377: c_dyn_pig_check c_dyn_pig_checkCurTyp;
378: TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
379: admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
380: course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
381: sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
382: c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;
383:

Line 380: course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,

376: TYPE c_dyn_pig_checkCurTyp IS REF CURSOR;
377: c_dyn_pig_check c_dyn_pig_checkCurTyp;
378: TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
379: admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
380: course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
381: sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
382: c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;
383:
384:

Line 381: sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);

377: c_dyn_pig_check c_dyn_pig_checkCurTyp;
378: TYPE c_dyn_pig_checkrecTyp IS RECORD ( person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
379: admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
380: course_cd igs_ad_ps_appl_inst_all.course_cd%TYPE,
381: sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE);
382: c_dyn_pig_check_rec c_dyn_pig_checkrecTyp ;
383:
384:
385: lv_status VARCHAR2(1);

Line 391: p_person_id igs_ad_ps_appl_inst.person_id%TYPE,

387: lv_group_type IGS_PE_PERSID_GROUP_V.group_type%TYPE;
388:
389: -- Get the details of
390: CURSOR cur_appl_case1(
391: p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
392: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395: ) IS

Line 392: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,

388:
389: -- Get the details of
390: CURSOR cur_appl_case1(
391: p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
392: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395: ) IS
396: SELECT person_id, admission_appl_number, course_cd, sequence_number

Line 393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,

389: -- Get the details of
390: CURSOR cur_appl_case1(
391: p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
392: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395: ) IS
396: SELECT person_id, admission_appl_number, course_cd, sequence_number
397: FROM igs_ad_ps_appl_inst apai,

Line 394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE

390: CURSOR cur_appl_case1(
391: p_person_id igs_ad_ps_appl_inst.person_id%TYPE,
392: p_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE,
393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395: ) IS
396: SELECT person_id, admission_appl_number, course_cd, sequence_number
397: FROM igs_ad_ps_appl_inst apai,
398: igs_ad_ou_stat aos,

Line 397: FROM igs_ad_ps_appl_inst apai,

393: p_course_cd igs_ad_ps_appl_inst.course_cd%TYPE,
394: p_sequence_number igs_ad_ps_appl_inst.sequence_number%TYPE
395: ) IS
396: SELECT person_id, admission_appl_number, course_cd, sequence_number
397: FROM igs_ad_ps_appl_inst apai,
398: igs_ad_ou_stat aos,
399: igs_ad_doc_stat ads
400: WHERE apai.person_id = p_person_id
401: AND apai.admission_appl_number = p_admission_appl_number

Line 414: p_person_id igs_ad_ps_appl_inst.person_id%TYPE

410:
411:
412: -- Get the details of
413: CURSOR cur_appl_case2(
414: p_person_id igs_ad_ps_appl_inst.person_id%TYPE
415: ) IS
416: SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
417: FROM igs_ad_ps_appl_inst apai,
418: igs_ad_ou_stat aos,

Line 417: FROM igs_ad_ps_appl_inst apai,

413: CURSOR cur_appl_case2(
414: p_person_id igs_ad_ps_appl_inst.person_id%TYPE
415: ) IS
416: SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
417: FROM igs_ad_ps_appl_inst apai,
418: igs_ad_ou_stat aos,
419: igs_ad_doc_stat ads
420: WHERE apai.person_id = p_person_id
421: AND aos.s_adm_outcome_status IN ('PENDING','COND-OFFER')

Line 439: FROM igs_ad_ps_appl_inst apai,

435: p_adm_cal_type igs_ad_appl_all.adm_cal_type%TYPE,
436: p_adm_ci_sequence_number igs_ad_appl_all.adm_ci_sequence_number%TYPE
437: ) IS
438: SELECT apai.person_id, apai.admission_appl_number, apai. course_cd, apai.sequence_number
439: FROM igs_ad_ps_appl_inst apai,
440: igs_ad_appl aa,
441: igs_ad_ou_stat aos,
442: igs_ad_doc_stat ads
443: WHERE apai.person_id = aa.person_id

Line 608: FROM igs_ad_ps_appl_inst apai,

604: IF lv_group_type = 'STATIC' THEN
605:
606: OPEN c_dyn_pig_check FOR
607: 'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
608: FROM igs_ad_ps_appl_inst apai,
609: igs_ad_ou_stat aos,
610: igs_ad_doc_stat ads
611: WHERE apai.person_id IN ( '||lv_sql_stmt||')
612: AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')

Line 641: FROM igs_ad_ps_appl_inst apai,

637: ELSIF lv_group_type = 'DYNAMIC' THEN
638:
639: OPEN c_dyn_pig_check FOR
640: 'SELECT apai.person_id, apai.admission_appl_number, apai.course_cd, apai.sequence_number
641: FROM igs_ad_ps_appl_inst apai,
642: igs_ad_ou_stat aos,
643: igs_ad_doc_stat ads
644: WHERE apai.person_id IN ( '||lv_sql_stmt||')
645: AND aos.s_adm_outcome_status IN (''PENDING'',''COND-OFFER'')