[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_012
Source
1 PACKAGE BODY igs_ad_gen_012 AS
2 /* $Header: IGSAD12B.pls 120.3 2005/10/18 02:48:39 appldev ship $ */
3
4 /******************************************************************
5 Change History
6 Who When What
7 sjlaport 18-FEB-2005 Removed function get_inq_stat_id for IGR Migration. Bug 4114493
8 sarakshi 23-Nov-2004 Bug#4027591, removed outermost exception such that masking of the message does not happen in admp_upd_acai_comm.
9 cdcruz feb18 bug 2217104 Admit to future term Enhancement,updated tbh call for
10 new columns being added to IGS_AD_PS_APPL_INST
11 nshee 29-Aug-2002 Bug 2395510 DEferments build Added 6 columns in update row call
12 rboddu 13-FEB-2003 removed PROCEDURE Admp_Upd_Eap_Avail. Moved this to
13 igs_rc_gen_001 package. Bug:2664699
14 ********************************************************************/
15
16 FUNCTION Admp_Upd_Acai_Comm(
17 p_person_id IN NUMBER ,
18 p_admission_appl_number IN NUMBER ,
19 p_nominated_course_cd IN VARCHAR2 ,
20 p_acai_sequence_number IN NUMBER ,
21 p_prpsd_commencement_dt IN DATE ,
22 p_message_name OUT NOCOPY VARCHAR2 )
23 RETURN BOOLEAN IS
24
25 -- This module updates the IGS_AD_PS_APPL_INST.prpsd_commencement_dt.
26
27 v_adm_appl_status IGS_AD_APPL.adm_appl_status%TYPE;
28 v_dummy VARCHAR2(1);
29 v_message_name VARCHAR2(30);
30 v_update_non_enrol_detail_ind VARCHAR2(1);
31 CURSOR c_acai IS
32 SELECT ROWID, acai.*
33 FROM IGS_AD_PS_APPL_INST acai
34 WHERE acai.person_id = p_person_id AND
35 acai.admission_appl_number = p_admission_appl_number AND
36 acai.nominated_course_cd = p_nominated_course_cd AND
37 acai.sequence_number = p_acai_sequence_number
38 FOR UPDATE OF acai.prpsd_commencement_dt NOWAIT;
39 CURSOR c_aa IS
40 SELECT aa.adm_appl_status
41 FROM IGS_AD_APPL aa
42 WHERE aa.person_id = p_person_id AND
43 aa.admission_appl_number = p_admission_appl_number;
44 e_resource_busy_exception EXCEPTION;
45 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
46 Rec_IGS_AD_PS_APPL_Inst c_acai%RowType;
47 BEGIN
48 /*
49 || Change History :
50 || Who When What
51 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
52 || rrengara 26-JUL-2001 Bug Enh No: 1891835 : For the DLD Process Student Response to Offer. Added two columns in TBH
53 || (reverse chronological order - newest change first)
54 */
55
56 OPEN c_acai;
57 -- FETCH c_acai INTO v_dummy;
58 FETCH c_acai INTO Rec_IGS_AD_PS_APPL_Inst;
59 IF c_acai%NOTFOUND THEN
60 --Invalid parameters
61 CLOSE c_acai;
62 p_message_name := null;
63 RETURN TRUE;
64 END IF;
65 OPEN c_aa;
66 FETCH c_aa INTO v_adm_appl_status;
67 CLOSE c_aa;
68 -- Validate if the commencement date can be updated.
69 IF IGS_AD_VAL_ACAI.admp_val_acai_update (
70 v_adm_appl_status,
71 p_person_id,
72 p_admission_appl_number,
73 p_nominated_course_cd,
74 p_acai_sequence_number,
75 v_message_name,
76 v_update_non_enrol_detail_ind) = FALSE THEN
77
78 -- begin apadegal adtd001 igs.m
79
80 -- PRPSD_COMMENCEMENT_DT can be udpated in proceed phase
81 IF ( v_message_name = 'IGS_AD_APPL_INST_COMPL')
82 THEN
83
84
85 IF igs_ad_gen_002.check_adm_appl_inst_stat( p_person_id => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
86 p_admission_appl_number => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
87 p_nominated_course_cd => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
88 p_sequence_number => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
89 p_updateable => 'Y'
90 )='N' --- not updateable.. so throw error
91 THEN
92 p_message_name := 'IGS_AD_APPL_INST_COMPL';
93 RETURN FALSE;
94 END IF;
95 ELSE
96 --end apadegal adtd001 igs.m
97 p_message_name := v_message_name;
98 RETURN FALSE;
99 END IF;
100 END IF;
101 IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
102 X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
103 X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
104 X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
105 X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
106 X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
107 X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
108 X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
109 X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
110 X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
111 X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
112 X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
113 X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
114 X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
115 X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
116 X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
117 X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
118 X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
119 X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
120 X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
121 X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
122 X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
123 X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
124 X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
125 X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
126 X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
127 X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
128 X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
129 X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
130 X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
131 X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
132 X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
133 X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
134 X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
135 X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
136 X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
137 X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
138 X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
139 X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
140 X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
141 X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
142 X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
143 X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
144 X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
145 X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
146 X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
147 X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
148 X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
149 X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
150 X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
151 X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
152 X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
153 X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
154 X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
155 X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
156 X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
157 X_ADM_OUTCOME_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS ,
158 X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
159 X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
160 X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
161 X_OFFER_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_DT ,
162 X_OFFER_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_RESPONSE_DT ,
163 X_PRPSD_COMMENCEMENT_DT => p_prpsd_commencement_dt ,
164 X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
165 X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
166 X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
167 X_ADM_OFFER_RESP_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_RESP_STATUS ,
168 X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
169 X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
170 X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
171 X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
172 X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
173 X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
174 X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
175 X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
176 X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
177 X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
178 X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
179 X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
180 X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
181 X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
182 X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
183 X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
184 X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
185 X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
186 X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
187 X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
188 X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
189 X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
190 X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
191 X_WAITLIST_STATUS => Rec_IGS_AD_PS_APPL_Inst.WAITLIST_STATUS, -- BUG # 2097333
192 X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
193 X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
194 X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
195 X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
196 X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
197 X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
198 X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
199 X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
200 X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
201 X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
202 X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
203 X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
204 X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
205 X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
206 X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
207 X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
208 X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
209 X_MODE => 'R',
210 X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
211 X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
212 X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
213 X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
214 X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
215 X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
216 X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
217 X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
218 X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
219 X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
220 X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
221 X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
222 X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
223 X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
224 X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
225 X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
226 X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
227 X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
228 X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
229 X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
230 X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
231 x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
232 x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
233 );
234
235 CLOSE c_acai;
236 p_message_name := null;
237 RETURN TRUE;
238 EXCEPTION
239 WHEN e_resource_busy_exception THEN
240 IF (c_acai%ISOPEN) THEN
241 CLOSE c_acai;
242 END IF;
243 IF (c_aa%ISOPEN) THEN
244 CLOSE c_aa;
245 END IF;
246 p_message_name := 'IGS_AD_NOTUPD_LOCKED_ANOTHUSR';
247 RETURN FALSE;
248 WHEN OTHERS THEN
249 IF (c_acai%ISOPEN) THEN
250 CLOSE c_acai;
251 END IF;
252 IF (c_aa%ISOPEN) THEN
253 CLOSE c_aa;
254 END IF;
255 APP_EXCEPTION.RAISE_EXCEPTION;
256 END admp_upd_acai_comm;
257
258 PROCEDURE Admp_Upd_Acai_Defer(
259 p_log_creation_dt OUT NOCOPY DATE )
260 IS
261 BEGIN
262 /******************************************************************
263 The code has been commented out NOCOPY completely becuase of the obsoletion of the Job IGSADS06 report as a part of DLD_DEFERMETNT_CHAGES_2395510
264 According the obsoletion process we need to remove all the code and put only NULL.
265 Done By rrengara on 20-SEP-2002 for Bug 2563941 (D) 2395510 (P)
266 ***************************/
267 NULL;
268 END admp_upd_acai_defer;
269
270 PROCEDURE Admp_Upd_Acai_Lapsed(
271 errbuf OUT NOCOPY VARCHAR2,
272 retcode OUT NOCOPY NUMBER,
273 p_org_id IN NUMBER)
274 IS
275 gv_other_detail VARCHAR2(255);
276 -- admp_upd_acai_lapsed
277 -- This module updates all IGS_AD_PS_APPL_INST.adm_offer_resp_status to
278 -- lapsed if the applicant has not responded to an offer in the appropriate
279 -- time. This will be run nightly by the Job Scheduler.
280
281 e_resource_busy EXCEPTION;
282 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
283 cst_offer CONSTANT VARCHAR2(5) := 'OFFER';
284 cst_cond_offer CONSTANT VARCHAR2(10) := 'COND-OFFER';
285 cst_pending CONSTANT VARCHAR2(7) := 'PENDING';
286 cst_lapsed CONSTANT VARCHAR2(6) := 'LAPSED';
287 v_adm_offer_resp_status IGS_AD_PS_APPL_INST.adm_offer_resp_status%TYPE;
288
289 CURSOR c_update_acai IS
290 SELECT acai.ROWID, acai.*
291 FROM IGS_AD_PS_APPL_INST acai
292 WHERE EXISTS (SELECT 'x' from igs_ad_ou_stat aos
293 WHERE aos.adm_outcome_status = acai.adm_outcome_status
294 AND aos.s_adm_outcome_status IN ( cst_offer, cst_cond_offer))
295 AND EXISTS (SELECT 'x' FROM igs_ad_ofr_resp_stat aors
296 WHERE aors.adm_offer_resp_status = acai.adm_offer_resp_status
297 AND aors.s_adm_offer_resp_status = cst_pending
298 AND acai.offer_response_dt < TRUNC(SYSDATE))
299 FOR UPDATE OF acai.adm_offer_resp_status NOWAIT;
300
301 l_msg_data VARCHAR2(2000);
302
303 BEGIN
304
305 /*
306 || Change History :
307 || Who When What
308 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
309 || rrengara 26-JUL-2001 Bug Enh No: 1891835 : For the DLD Process Student Response to Offer. Added two columns in TBH
310 || (reverse chronological order - newest change first)
311 */
312
313 retcode := 0;
314 igs_ge_gen_003.set_org_id(p_org_id);
315
316 -- Get default value for system offer response status LAPSED.
317 v_adm_offer_resp_status := IGS_AD_GEN_009.admp_get_sys_aors(cst_lapsed);
318 -- All admission course applications that are passed their offer response date
319 -- should be lapsed.
320
321 FOR Rec_IGS_AD_PS_APPL_Inst IN c_update_acai LOOP
322 BEGIN
323 IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
324 X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
325 X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
326 X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
327 X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
328 X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
329 X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
330 X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
331 X_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.ADM_CAL_TYPE ,
332 X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
333 X_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADM_CI_SEQUENCE_NUMBER ,
334 X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
335 X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
336 X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
337 X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
338 X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
339 X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
340 X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
341 X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
342 X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
343 X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
344 X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
345 X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
346 X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
347 X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
348 X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
349 X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
350 X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
351 X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
352 X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
353 X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
354 X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
355 X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
356 X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
357 X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
358 X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
359 X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
360 X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
361 X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
362 X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
363 X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
364 X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
365 X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
366 X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
367 X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
368 X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
369 X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
370 X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
371 X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
372 X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
373 X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
374 X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
375 X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
376 X_LATE_ADM_FEE_STATUS => Rec_IGS_AD_PS_APPL_Inst.LATE_ADM_FEE_STATUS ,
377 X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
378 X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
379 X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
380 X_ADM_OUTCOME_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS ,
381 X_ADM_OTCM_STAT_AUTH_PER_ID => Rec_IGS_AD_PS_APPL_Inst.ADM_OTCM_STATUS_AUTH_PERSON_ID ,
382 X_ADM_OUTCOME_STATUS_AUTH_DT => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_AUTH_DT ,
383 X_ADM_OUTCOME_STATUS_REASON => Rec_IGS_AD_PS_APPL_Inst.ADM_OUTCOME_STATUS_REASON ,
384 X_OFFER_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_DT ,
385 X_OFFER_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.OFFER_RESPONSE_DT ,
386 X_PRPSD_COMMENCEMENT_DT => Rec_IGS_AD_PS_APPL_Inst.Prpsd_Commencement_Dt,
387 X_ADM_CNDTNL_OFFER_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_CNDTNL_OFFER_STATUS ,
388 X_CNDTNL_OFFER_SATISFIED_DT => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_SATISFIED_DT ,
389 X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
390 X_ADM_OFFER_RESP_STATUS => v_adm_offer_resp_status,
391 X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
392 X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
393 X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
394 X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
395 X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
396 X_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.ASS_RANK ,
397 X_SECONDARY_ASS_RANK => Rec_IGS_AD_PS_APPL_Inst.SECONDARY_ASS_RANK ,
398 X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
399 X_ASS_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.ASS_TRACKING_ID ,
400 X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
401 X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
402 X_EXPECTED_COMPLETION_YR => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_YR ,
403 X_EXPECTED_COMPLETION_PERD => Rec_IGS_AD_PS_APPL_Inst.EXPECTED_COMPLETION_PERD ,
404 X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
405 X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
406 X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
407 X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
408 X_CNDTNL_OFFER_CNDTN => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_CNDTN ,
409 X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
410 X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
411 X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
412 X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
413 X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
414 X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
415 X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
416 X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
417 X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
418 X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
419 X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
420 X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
421 X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
422 X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
423 X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
424 X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
425 X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
426 X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
427 X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
428 X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
429 X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
430 X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
431 X_MODE => 'R',
432 X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
433 X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
434 X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
435 X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
436 X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
437 X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
438 X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
439 X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
440 X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
441 X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
442 X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
443 X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
444 X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
445 X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
446 X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
447 X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
448 X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
449 X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
450 X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
451 X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
452 X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
453 x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
454 x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
455
456 );
457 EXCEPTION
458 WHEN e_resource_busy THEN
459 RAISE;
460 WHEN OTHERS THEN
461 fnd_file.put_line(fnd_file.log, 'Failed to update Offer Response Status to LAPSED for Person ID: '|| Rec_IGS_AD_PS_APPL_Inst.PERSON_ID || '; Admission Application Number: ' ||
462 Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER || '; Course Code: ' || Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD || '; Sequence Number: '||
463 Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER );
464 l_msg_data := FND_MESSAGE.GET;
465 IF l_msg_data is not null THEN
466 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Reason: ' || l_msg_data);
467 END IF;
468 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
469 l_msg_data := null;
470 END;
471
472 END LOOP;
473
474 IF c_update_acai%ISOPEN THEN
475 CLOSE c_update_acai;
476 END IF;
477
478 COMMIT;
479
480 EXCEPTION
481 WHEN e_resource_busy THEN
482 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PRGAPPL_NOT_LAPSED');
483 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
484 WHEN OTHERS THEN
485 IF c_update_acai%ISOPEN THEN
486 CLOSE c_update_acai;
487 END IF;
488
489 retcode := 2;
490 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
491 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
492 END admp_upd_acai_lapsed;
493
494 PROCEDURE Admp_Upd_Acai_Recon(
495 p_log_creation_dt OUT NOCOPY DATE )
496 IS
497 gv_other_detail VARCHAR2(255);
498 BEGIN -- admp_upd_acai_recon
499 -- Routine to update a IGS_AD_PS_APPL_INST when the admission
500 -- course application made a request for reconsideration, and the
501 -- outcome of the course application was REJECTED, NO-QUOTA.
502 -- This procedure will be triggered nightly for relevant date ranges
503 -- in the year, by the Job Scheduler
504 DECLARE
505 e_resource_busy_exception EXCEPTION;
506 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
507 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
508 cst_not_applic CONSTANT VARCHAR2(10) := 'NOT-APPLIC';
509 cst_rejected CONSTANT VARCHAR2(10) := 'REJECTED';
510 cst_no_quota CONSTANT VARCHAR2(10) := 'NO-QUOTA';
511 cst_adm_recon CONSTANT VARCHAR2(10) := 'ADM-RECON';
512 CURSOR c_daiv IS
513 SELECT daiv.cal_type,
514 daiv.ci_sequence_number,
515 ci.start_dt
516 FROM IGS_CA_DA_INST_V daiv,
517 IGS_CA_INST ci,
518 IGS_AD_CAL_CONF sacco
519 WHERE sacco.s_control_num = 1 AND
520 daiv.dt_alias = sacco.initialise_adm_perd_dt_alias AND
521 daiv.alias_val = TRUNC(SYSDATE) AND
522 daiv.cal_type = ci.cal_type AND
523 daiv.ci_sequence_number = ci.sequence_number
524 ORDER BY
525 ci.start_dt;
526 CURSOR c_apapc (
527 cp_adm_cal_type IGS_AD_PRD_AD_PRC_CA.adm_cal_type%TYPE,
528 cp_adm_ci_sequence_number IGS_AD_PRD_AD_PRC_CA.adm_ci_sequence_number%TYPE) IS
529
530 SELECT aa.admission_cat,
531 aa.s_admission_process_type,
532 aa.person_id,
533 aa.admission_appl_number,
534 aa.appl_dt,
535 aca.nominated_course_cd
536 FROM IGS_AD_PS_APPL aca,
537 IGS_AD_APPL aa
538 WHERE aa.person_id = aca.person_id
539 AND aa.admission_appl_number = aca.admission_appl_number
540 AND aca.req_for_reconsideration_ind = 'Y'
541 AND EXISTS ( SELECT 'x' FROM igs_ad_prd_ad_prc_ca apapc
542 WHERE apapc.adm_cal_type = cp_adm_cal_type
543 AND apapc.adm_ci_sequence_number = cp_adm_ci_sequence_number
544 AND apapc.admission_cat = aa.admission_cat
545 AND apapc.s_admission_process_type = aa.s_admission_process_type
546 AND apapc.closed_ind = 'N');
547
548 CURSOR c_aca (
549 cp_person_id IGS_AD_PS_APPL.person_id%TYPE,
550 cp_admission_appl_number IGS_AD_PS_APPL.admission_appl_number%TYPE,
551 cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE ) IS
552
553 SELECT ROWID, aca.*
554 FROM IGS_AD_PS_APPL aca
555 WHERE aca.person_id = cp_person_id AND
556 aca.admission_appl_number = cp_admission_appl_number AND
557 aca.nominated_course_cd = cp_nominated_course_cd
558 FOR UPDATE OF aca.req_for_reconsideration_ind NOWAIT;
559
560 Rec_IGS_AD_PS_APPL c_aca%ROWTYPE;
561
562 CURSOR c_acaiv (
563 cp_start_dt IGS_CA_INST.start_dt%TYPE,
564 cp_person_id IGS_AD_APPL.person_id%TYPE,
565 cp_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
566 cp_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE) IS
567 SELECT acaiv.person_id,
568 acaiv.admission_appl_number,
569 acaiv.nominated_course_cd,
570 acaiv.sequence_number,
571 acaiv.course_cd,
572 acaiv.crv_version_number,
573 aa.acad_cal_type,
574 aa.acad_ci_sequence_number,
575 acaiv.attendance_type,
576 acaiv.attendance_mode,
577 acaiv.location_cd
578 FROM igs_ad_ps_appl_inst acaiv,
579 igs_ad_appl aa
580 WHERE aa.person_id = acaiv.person_id
581 AND aa.admission_appl_number = acaiv.admission_appl_number
582 AND acaiv.person_id = cp_person_id
583 AND acaiv.admission_appl_number = cp_admission_appl_number
584 AND acaiv.nominated_course_cd = cp_nominated_course_cd
585 AND EXISTS (SELECT 'x' FROM igs_ad_ou_stat aos
586 WHERE aos.adm_outcome_status = acaiv.adm_outcome_status
587 AND aos.s_adm_outcome_status IN (cst_rejected, cst_no_quota))
588 AND EXISTS (SELECT 'x' FROM igs_ca_inst ci
589 WHERE ci.cal_type = NVL (acaiv.adm_cal_type,aa.adm_cal_type)
590 AND ci.sequence_number = NVL (acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number)
591 AND ci.start_dt < cp_start_dt);
592
593
594 CURSOR c_acai (
595 cp_person_id IGS_AD_PS_APPL_INST.person_id%TYPE,
596 cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
597 cp_nominated_course_cd IGS_AD_PS_APPL_INST.nominated_course_cd%TYPE,
598 cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE) IS
599 SELECT acai.ROWID, acai.*
600 FROM IGS_AD_PS_APPL_INST acai
601 WHERE acai.person_id = cp_person_id AND
602 acai.admission_appl_number = cp_admission_appl_number AND
603 acai.nominated_course_cd = cp_nominated_course_cd AND
604 acai.sequence_number = cp_sequence_number
605 FOR UPDATE OF
606 acai.adm_cal_type,
607 acai.adm_ci_sequence_number,
608 acai.late_adm_fee_status,
609 acai.adm_outcome_status,
610 acai.offer_dt,
611 acai.offer_response_dt,
612 acai.adm_cndtnl_offer_status,
613 acai.cndtnl_offer_satisfied_dt,
614 acai.cndtnl_offer_cndtn,
615 acai.ass_tracking_id,
616 acai.ass_rank,
617 acai.secondary_ass_rank,
618 acai.adm_otcm_status_auth_person_id,
619 acai.adm_outcome_status_auth_dt,
620 acai.adm_outcome_status_reason,
621 acai.expected_completion_perd,
622 acai.expected_completion_yr NOWAIT;
623
624 Rec_IGS_AD_PS_APPL_Inst c_acai%ROWTYPE;
625 v_creation_dt DATE;
626 v_message_name varchar2(30);
627 v_log_message_name varchar2(30);
628 v_return_type VARCHAR2(1);
629 v_late_ind VARCHAR2(1);
630 v_aca_exists VARCHAR2(1);
631 v_acai_exists VARCHAR2(1);
632 v_acai_updated VARCHAR2(1) DEFAULT 'N';
633 v_adm_outcome_status IGS_AD_OU_STAT.adm_outcome_status%TYPE;
634 v_adm_cndtnl_offer_status IGS_AD_CNDNL_OFRSTAT.adm_cndtnl_offer_status%TYPE;
635 v_late_adm_fee_status IGS_AD_FEE_STAT.adm_fee_status%TYPE;
636 v_crv_version_number NUMBER;
637 v_course_start_dt IGS_CA_DA_INST_V.alias_val%TYPE;
638 v_expected_completion_yr IGS_AD_PS_APPL_INST.expected_completion_yr%TYPE;
639 v_expected_completion_perd
640 IGS_AD_PS_APPL_INST.expected_completion_perd%TYPE;
641 v_completion_dt DATE;
642 BEGIN
643 /*
644 || Change History :
645 || Who When What
646 || knag 29-OCT-2002 Bug 2647482 - added parameters attendance_mode, location_cd for proposed completion date calculation
647 || samaresh 02-DEC-2001 Bug # 2097333 : Impact of addition of the waitlist_status field to igs_ad_ps_appl_inst_all
648 || rrengara 26-JUL-2001 Bug Enh No: 1891835 : For the DLD Process Student Response to Offer. Added two columns in TBH
649 || (reverse chronological order - newest change first)
650 */
651
652 -- Determine if it is time to process reconsiderations.
653 FOR v_daiv_rec IN c_daiv LOOP
654 IF c_daiv%ROWCOUNT = 1 THEN
655 -- Insert log for exception reporting
656 IGS_GE_GEN_003.genp_ins_log(
657 cst_adm_recon,
658 NULL,
659 v_creation_dt);
660 p_log_creation_dt := v_creation_dt;
661 -- Get user default for pending outcome status and not
662 -- applicable conditional Offer status, late fee status
663 v_adm_outcome_status := IGS_AD_GEN_009.admp_get_sys_aos(
664 cst_pending);
665 v_adm_cndtnl_offer_status := IGS_AD_GEN_009.admp_get_sys_acos(
666 cst_not_applic);
667 v_late_adm_fee_status := IGS_AD_GEN_009.admp_get_sys_afs(
668 cst_not_applic);
669 END IF;
670 -- Find each admission process category for this admission period
671 -- Find previous admission application with "Reconsiderations" not yet
672 -- processed
673 FOR v_apapc_rec IN c_apapc (
674 v_daiv_rec.cal_type,
675 v_daiv_rec.ci_sequence_number) LOOP
676 BEGIN -- lock_block A
677 -- Check that record (IGS_AD_PS_APPL) can be locked prior to update
678 SAVEPOINT sp_aca;
679 OPEN c_aca(
680 v_apapc_rec.person_id,
681 v_apapc_rec.admission_appl_number,
682 v_apapc_rec.nominated_course_cd);
683 -- FETCH c_aca INTO v_aca_exists;
684 FETCH c_aca INTO Rec_IGS_AD_PS_APPL;
685 IF c_aca%FOUND THEN
686 -- Reset the ACAI updated indicator.
687 v_acai_updated := 'N';
688 FOR v_acaiv_rec IN c_acaiv(
689 v_daiv_rec.start_dt,
690 v_apapc_rec.person_id,
691 v_apapc_rec.admission_appl_number,
692 v_apapc_rec.nominated_course_cd) LOOP
693
694 -- Validate the admission calendar.
695 IF IGS_AD_VAL_AA.admp_val_aa_adm_cal(
696 v_daiv_rec.cal_type,
697 v_daiv_rec.ci_sequence_number, v_acaiv_rec.acad_cal_type,
698 v_acaiv_rec.acad_ci_sequence_number,
699 v_apapc_rec.admission_cat,
700 v_apapc_rec.s_admission_process_type,
701 v_message_name) THEN
702 -- the following section/block was not indented for
703 -- readability.
704 -- Validate the course application in the new admission period
705 IF NOT IGS_AD_VAL_ACAI.admp_val_acai_course(
706 v_acaiv_rec.course_cd,
707 v_acaiv_rec.crv_version_number,
708 v_apapc_rec.admission_cat,
709 v_apapc_rec.s_admission_process_type,
710 v_acaiv_rec.acad_cal_type,
711 v_acaiv_rec.acad_ci_sequence_number,
712 v_daiv_rec.cal_type,
713 v_daiv_rec.ci_sequence_number,
714 v_apapc_rec.appl_dt,
715 'Y',
716 'N',
717 v_crv_version_number,
718 v_message_name,
719 v_return_type) THEN
720 v_log_message_name := v_message_name;
721 ELSE
722 v_log_message_name := NULL;
723 BEGIN -- lock_block B
724 OPEN c_acai(
725 v_acaiv_rec.person_id,
726 v_acaiv_rec.admission_appl_number,
727 v_acaiv_rec.nominated_course_cd,
728 v_acaiv_rec.sequence_number);
729 -- FETCH c_acai INTO v_acai_exists;
730 FETCH c_acai INTO Rec_IGS_AD_PS_APPL_Inst;
731 IF c_acai%FOUND THEN
732 -- Derive the expected completion details
733 v_course_start_dt := IGS_AD_GEN_005.admp_get_crv_strt_dt(
734 v_daiv_rec.cal_type,
735 v_daiv_rec.ci_sequence_number);
736 IGS_AD_GEN_004.admp_get_crv_comp_dt (
737 v_acaiv_rec.course_cd,
738 v_acaiv_rec.crv_version_number,
739 v_acaiv_rec.acad_cal_type,
740 v_acaiv_rec.attendance_type,
741 v_course_start_dt,
742 v_expected_completion_yr,
743 v_expected_completion_perd,
744 v_completion_dt,
745 v_acaiv_rec.attendance_mode,
746 v_acaiv_rec.location_cd);
747 -- Update this record (IGS_AD_PS_APPL_INST)
748
749 IGS_AD_PS_APPL_Inst_Pkg.UPDATE_ROW (
750 X_ROWID => Rec_IGS_AD_PS_APPL_Inst.ROWID ,
751 X_PERSON_ID => Rec_IGS_AD_PS_APPL_Inst.PERSON_ID ,
752 X_ADMISSION_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.ADMISSION_APPL_NUMBER ,
753 X_NOMINATED_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.NOMINATED_COURSE_CD ,
754 X_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.SEQUENCE_NUMBER ,
755 X_PREDICTED_GPA => Rec_IGS_AD_PS_APPL_Inst.PREDICTED_GPA ,
756 X_ACADEMIC_INDEX => Rec_IGS_AD_PS_APPL_Inst.ACADEMIC_INDEX,
757 X_Adm_Cal_Type => v_daiv_rec.cal_type,
758 X_APP_FILE_LOCATION => Rec_IGS_AD_PS_APPL_Inst.APP_FILE_LOCATION ,
759 X_Adm_Ci_Sequence_Number => v_daiv_rec.ci_sequence_number,
760 X_COURSE_CD => Rec_IGS_AD_PS_APPL_Inst.COURSE_CD ,
761 X_APP_SOURCE_ID => Rec_IGS_AD_PS_APPL_Inst.APP_SOURCE_ID ,
762 X_CRV_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.CRV_VERSION_NUMBER ,
763 X_Waitlist_Rank => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Rank,
764 X_Waitlist_Status => Rec_IGS_AD_PS_APPL_Inst.Waitlist_Status,
765 X_LOCATION_CD => Rec_IGS_AD_PS_APPL_Inst.LOCATION_CD ,
766 X_Attent_Other_Inst_Cd => Rec_IGS_AD_PS_APPL_Inst.Attent_Other_Inst_Cd,
767 X_ATTENDANCE_MODE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_MODE ,
768 X_Edu_Goal_Prior_Enroll_Id => Rec_IGS_AD_PS_APPL_Inst.Edu_Goal_Prior_Enroll_Id,
769 X_ATTENDANCE_TYPE => Rec_IGS_AD_PS_APPL_Inst.ATTENDANCE_TYPE ,
770 X_Decision_Make_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Make_Id,
771 X_UNIT_SET_CD => Rec_IGS_AD_PS_APPL_Inst.UNIT_SET_CD ,
772 X_Decision_Date => Rec_IGS_AD_PS_APPL_Inst.Decision_Date,
773 X_Attribute_Category => Rec_IGS_AD_PS_APPL_Inst.Attribute_Category,
774 X_Attribute1 => Rec_IGS_AD_PS_APPL_Inst.Attribute1,
775 X_Attribute2 => Rec_IGS_AD_PS_APPL_Inst.Attribute2,
776 X_Attribute3 => Rec_IGS_AD_PS_APPL_Inst.Attribute3,
777 X_Attribute4 => Rec_IGS_AD_PS_APPL_Inst.Attribute4,
778 X_Attribute5 => Rec_IGS_AD_PS_APPL_Inst.Attribute5,
779 X_Attribute6 => Rec_IGS_AD_PS_APPL_Inst.Attribute6,
780 X_Attribute7 => Rec_IGS_AD_PS_APPL_Inst.Attribute7,
781 X_Attribute8 => Rec_IGS_AD_PS_APPL_Inst.Attribute8,
782 X_Attribute9 => Rec_IGS_AD_PS_APPL_Inst.Attribute9,
783 X_Attribute10 => Rec_IGS_AD_PS_APPL_Inst.Attribute10,
784 X_Attribute11 => Rec_IGS_AD_PS_APPL_Inst.Attribute11,
785 X_Attribute12 => Rec_IGS_AD_PS_APPL_Inst.Attribute12,
786 X_Attribute13 => Rec_IGS_AD_PS_APPL_Inst.Attribute13,
787 X_Attribute14 => Rec_IGS_AD_PS_APPL_Inst.Attribute14,
788 X_Attribute15 => Rec_IGS_AD_PS_APPL_Inst.Attribute15,
789 X_Attribute16 => Rec_IGS_AD_PS_APPL_Inst.Attribute16,
790 X_Attribute17 => Rec_IGS_AD_PS_APPL_Inst.Attribute17,
791 X_Attribute18 => Rec_IGS_AD_PS_APPL_Inst.Attribute18,
792 X_Attribute19 => Rec_IGS_AD_PS_APPL_Inst.Attribute19,
793 X_Attribute20 => Rec_IGS_AD_PS_APPL_Inst.Attribute20,
794 X_Decision_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Decision_Reason_Id,
795 X_US_VERSION_NUMBER => Rec_IGS_AD_PS_APPL_Inst.US_VERSION_NUMBER ,
796 X_Decision_Notes => Rec_IGS_AD_PS_APPL_Inst.Decision_Notes,
797 X_Pending_Reason_Id => Rec_IGS_AD_PS_APPL_Inst.Pending_Reason_Id,
798 X_PREFERENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREFERENCE_NUMBER ,
799 X_ADM_DOC_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_DOC_STATUS ,
800 X_ADM_ENTRY_QUAL_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_ENTRY_QUAL_STATUS ,
801 X_DEFICIENCY_IN_PREP => Rec_IGS_AD_PS_APPL_Inst.DEFICIENCY_IN_PREP ,
802 X_Late_Adm_Fee_Status => v_late_adm_fee_status,
803 X_Spl_Consider_Comments => Rec_IGS_AD_PS_APPL_Inst.Spl_Consider_Comments,
804 X_Apply_For_Finaid => Rec_IGS_AD_PS_APPL_Inst.Apply_For_Finaid,
805 X_Finaid_Apply_Date => Rec_IGS_AD_PS_APPL_Inst.Finaid_Apply_Date,
806 X_Adm_Outcome_Status => v_adm_outcome_status,
807 X_ADM_OTCM_STAT_AUTH_PER_ID => NULL,
808 X_Adm_Outcome_Status_Auth_Dt => NULL,
809 X_Adm_Outcome_Status_Reason => NULL,
810 X_Offer_Dt => NULL,
811 X_Offer_Response_Dt => NULL,
812 X_PRPSD_COMMENCEMENT_DT => Rec_IGS_AD_PS_APPL_Inst.prpsd_commencement_dt ,
813 X_Adm_Cndtnl_Offer_Status => v_adm_cndtnl_offer_status,
814 X_Cndtnl_Offer_Satisfied_Dt => NULL,
815 X_CNDNL_OFR_MUST_BE_STSFD_IND => Rec_IGS_AD_PS_APPL_Inst.CNDTNL_OFFER_MUST_BE_STSFD_IND ,
816 X_ADM_OFFER_RESP_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_RESP_STATUS ,
817 X_ACTUAL_RESPONSE_DT => Rec_IGS_AD_PS_APPL_Inst.ACTUAL_RESPONSE_DT ,
818 X_ADM_OFFER_DFRMNT_STATUS => Rec_IGS_AD_PS_APPL_Inst.ADM_OFFER_DFRMNT_STATUS ,
819 X_DEFERRED_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CAL_TYPE ,
820 X_DEFERRED_ADM_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_ADM_CI_SEQUENCE_NUM ,
821 X_DEFERRED_TRACKING_ID => Rec_IGS_AD_PS_APPL_Inst.DEFERRED_TRACKING_ID ,
822 X_ASS_RANK => NULL,
823 X_SECONDARY_ASS_RANK => NULL,
824 X_INTR_ACCEPT_ADVICE_NUM => Rec_IGS_AD_PS_APPL_Inst.INTRNTNL_ACCEPTANCE_ADVICE_NUM ,
825 X_ASS_TRACKING_ID => NULL,
826 X_FEE_CAT => Rec_IGS_AD_PS_APPL_Inst.FEE_CAT ,
827 X_HECS_PAYMENT_OPTION => Rec_IGS_AD_PS_APPL_Inst.HECS_PAYMENT_OPTION ,
828 X_EXPECTED_COMPLETION_YR => V_EXPECTED_COMPLETION_YR ,
829 X_EXPECTED_COMPLETION_PERD => V_EXPECTED_COMPLETION_PERD ,
830 X_CORRESPONDENCE_CAT => Rec_IGS_AD_PS_APPL_Inst.CORRESPONDENCE_CAT ,
831 X_ENROLMENT_CAT => Rec_IGS_AD_PS_APPL_Inst.ENROLMENT_CAT ,
832 X_FUNDING_SOURCE => Rec_IGS_AD_PS_APPL_Inst.FUNDING_SOURCE ,
833 X_APPLICANT_ACPTNCE_CNDTN => Rec_IGS_AD_PS_APPL_Inst.APPLICANT_ACPTNCE_CNDTN ,
834 X_CNDTNL_OFFER_CNDTN => NULL ,
835 X_SS_APPLICATION_ID => Rec_IGS_AD_PS_APPL_Inst.SS_APPLICATION_ID ,
836 X_SS_PWD => Rec_IGS_AD_PS_APPL_Inst.SS_PWD,
837 X_AUTHORIZED_DT => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZED_DT, -- BUG ENH NO : 1891835 Added this column in table
838 X_AUTHORIZING_PERS_ID => Rec_IGS_AD_PS_APPL_Inst.AUTHORIZING_PERS_ID, -- BUG ENH NO : 1891835 Added this column in table
839 X_IDX_CALC_DATE => Rec_IGS_AD_PS_APPL_Inst.IDX_CALC_DATE,
840 X_ENTRY_STATUS => Rec_IGS_AD_PS_APPL_Inst.ENTRY_STATUS, -- Bug # 1905651
841 X_ENTRY_LEVEL => Rec_IGS_AD_PS_APPL_Inst.ENTRY_LEVEL, -- Bug # 1905651
842 X_SCH_APL_TO_ID => Rec_IGS_AD_PS_APPL_Inst.SCH_APL_TO_ID, -- Bug # 1905651
843 X_FUT_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CAL_TYPE, -- Bug # 2217104
844 X_FUT_ACAD_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ACAD_CI_SEQUENCE_NUMBER,-- Bug # 2217104
845 X_FUT_ADM_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CAL_TYPE, -- Bug # 2217104
846 X_FUT_ADM_CI_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_ADM_CI_SEQUENCE_NUMBER, -- Bug # 2217104
847 X_PREV_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
848 X_PREV_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.PREVIOUS_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
849 X_FUT_TERM_ADM_APPL_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_ADM_APPL_NUMBER, -- Bug # 2217104
850 X_FUT_TERM_SEQUENCE_NUMBER => Rec_IGS_AD_PS_APPL_Inst.FUTURE_TERM_SEQUENCE_NUMBER, -- Bug # 2217104
851 X_DEF_ACAD_CAL_TYPE => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CAL_TYPE, --Bug 2395510
852 X_DEF_ACAD_CI_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_ACAD_CI_SEQUENCE_NUM, --Bug 2395510
853 X_DEF_PREV_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_TERM_ADM_APPL_NUM,--Bug 2395510
854 X_DEF_PREV_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_PREV_APPL_SEQUENCE_NUM,--Bug 2395510
855 X_DEF_TERM_ADM_APPL_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_TERM_ADM_APPL_NUM,--Bug 2395510
856 X_DEF_APPL_SEQUENCE_NUM => Rec_IGS_AD_PS_APPL_Inst.DEF_APPL_SEQUENCE_NUM,--Bug 2395510
857 X_MODE => 'R',
858 X_Attribute21 => Rec_IGS_AD_PS_APPL_Inst.Attribute21,
859 X_Attribute22 => Rec_IGS_AD_PS_APPL_Inst.Attribute22,
860 X_Attribute23 => Rec_IGS_AD_PS_APPL_Inst.Attribute23,
861 X_Attribute24 => Rec_IGS_AD_PS_APPL_Inst.Attribute24,
862 X_Attribute25 => Rec_IGS_AD_PS_APPL_Inst.Attribute25,
863 X_Attribute26 => Rec_IGS_AD_PS_APPL_Inst.Attribute26,
864 X_Attribute27 => Rec_IGS_AD_PS_APPL_Inst.Attribute27,
865 X_Attribute28 => Rec_IGS_AD_PS_APPL_Inst.Attribute28,
866 X_Attribute29 => Rec_IGS_AD_PS_APPL_Inst.Attribute29,
867 X_Attribute30 => Rec_IGS_AD_PS_APPL_Inst.Attribute30,
868 X_Attribute31 => Rec_IGS_AD_PS_APPL_Inst.Attribute31,
869 X_Attribute32 => Rec_IGS_AD_PS_APPL_Inst.Attribute32,
870 X_Attribute33 => Rec_IGS_AD_PS_APPL_Inst.Attribute33,
871 X_Attribute34 => Rec_IGS_AD_PS_APPL_Inst.Attribute34,
872 X_Attribute35 => Rec_IGS_AD_PS_APPL_Inst.Attribute35,
873 X_Attribute36 => Rec_IGS_AD_PS_APPL_Inst.Attribute36,
874 X_Attribute37 => Rec_IGS_AD_PS_APPL_Inst.Attribute37,
875 X_Attribute38 => Rec_IGS_AD_PS_APPL_Inst.Attribute38,
876 X_Attribute39 => Rec_IGS_AD_PS_APPL_Inst.Attribute39,
877 X_Attribute40 => Rec_IGS_AD_PS_APPL_Inst.Attribute40,
878 X_APPL_INST_STATUS => Rec_IGS_AD_PS_APPL_Inst.appl_inst_status,
879 x_ais_reason => Rec_IGS_AD_PS_APPL_Inst.ais_reason,
880 x_decline_ofr_reason => Rec_IGS_AD_PS_APPL_Inst.decline_ofr_reason
881 );
882 v_acai_updated := 'Y';
883 CLOSE c_acai;
884 -- Update admission course application units
885 IGS_AD_UPD_INITIALISE.admp_upd_acaiu_init(
886 v_acaiv_rec.person_id,
887 v_acaiv_rec.admission_appl_number,
888 v_acaiv_rec.nominated_course_cd,
889 v_acaiv_rec.sequence_number,
890 v_acaiv_rec.acad_cal_type,
891 v_acaiv_rec.acad_ci_sequence_number,
892 v_daiv_rec.cal_type,
893 v_daiv_rec.ci_sequence_number,
894 v_apapc_rec.s_admission_process_type,
895 'N',
896 cst_adm_recon,
897 v_creation_dt);
898 ELSE
899 CLOSE c_acai;
900 END IF;
901 EXCEPTION
902 WHEN e_resource_busy_exception THEN
903 v_log_message_name := 'IGS_AD_APPL_NOT_PROCESSED';
904 WHEN OTHERS THEN
905 IF c_acai%ISOPEN THEN
906 CLOSE c_acai;
907 END IF;
908 ROLLBACK TO sp_aca;
909 APP_EXCEPTION.RAISE_EXCEPTION;
910 END; -- lock_block B
911 END IF;
912 -- continues from unindented block.
913 ELSE
914 v_log_message_name := v_message_name;
915 END IF;
916 -- Insert into log for reporting
917 IGS_GE_GEN_003.genp_ins_log_entry(
918 cst_adm_recon,
919 v_creation_dt,
920 (FND_NUMBER.NUMBER_TO_CANONICAL(v_acaiv_rec.person_id) || ',' ||
921 FND_NUMBER.NUMBER_TO_CANONICAL(v_acaiv_rec.admission_appl_number) || ',' ||
922 v_acaiv_rec.nominated_course_cd || ',' ||
923 FND_NUMBER.NUMBER_TO_CANONICAL(v_acaiv_rec.sequence_number)),
924 v_log_message_name,
925 ' ');
926 -- Reset the log message number
927 v_log_message_name := NULL;
928 END LOOP; -- v_acaiv_rec
929 IF v_acai_updated = 'Y' THEN
930 IGS_AD_PS_APPL_Pkg.Update_Row (
931 X_Mode => 'R',
932 X_RowId => Rec_IGS_AD_PS_APPL.RowId,
933 X_Person_Id => Rec_IGS_AD_PS_APPL.Person_Id,
934 X_Admission_Appl_Number => Rec_IGS_AD_PS_APPL.Admission_Appl_Number,
935 X_Nominated_Course_Cd => Rec_IGS_AD_PS_APPL.Nominated_Course_Cd,
936 X_Transfer_Course_Cd => Rec_IGS_AD_PS_APPL.Transfer_Course_Cd,
937 X_Basis_For_Admission_Type => Rec_IGS_AD_PS_APPL.Basis_For_Admission_Type,
938 X_Admission_Cd => Rec_IGS_AD_PS_APPL.Admission_Cd,
939 X_Course_Rank_Set => Rec_IGS_AD_PS_APPL.Course_Rank_Set,
940 X_Course_Rank_Schedule => Rec_IGS_AD_PS_APPL.Course_Rank_Schedule,
941 X_Req_For_Reconsideration_Ind => 'N',
942 X_Req_For_Adv_Standing_Ind => Rec_IGS_AD_PS_APPL.Req_For_Adv_Standing_Ind
943 );
944
945 END IF;
946 CLOSE c_aca;
947 ELSE
948 CLOSE c_aca;
949 END IF;
950 EXCEPTION
951 WHEN e_resource_busy_exception THEN
952 v_log_message_name := 'IGS_AD_APPL_CURR_BEING_UPD';
953 -- Insert into log for reporting
954 IGS_GE_GEN_003.genp_ins_log_entry(
955 cst_adm_recon,
956 v_creation_dt,
957 (FND_NUMBER.NUMBER_TO_CANONICAL(v_apapc_rec.person_id) || ',' ||
958 FND_NUMBER.NUMBER_TO_CANONICAL(v_apapc_rec.admission_appl_number) || ',' ||
959 v_apapc_rec.nominated_course_cd || ',' ||
960 NULL),
961 v_log_message_name,
962 ' ');
963 -- Reset the log message number
964 v_log_message_name := NULL;
965 WHEN OTHERS THEN
966 IF c_aca%ISOPEN THEN
967 CLOSE c_aca;
968 END IF;
969 APP_EXCEPTION.RAISE_EXCEPTION;
970 END; -- lock_block A
971 COMMIT;
972 END LOOP; -- v_apapc_rec
973 END LOOP; -- v_daiv_rec
974 RETURN;
975 EXCEPTION
976 WHEN OTHERS THEN
977 IF c_daiv%ISOPEN THEN
978 CLOSE c_daiv;
979 END IF;
980 IF c_apapc%ISOPEN THEN
981 CLOSE c_apapc;
982 END IF;
983 IF c_acaiv%ISOPEN THEN
984 CLOSE c_acaiv;
985 END IF;
986 IF c_aca%ISOPEN THEN
987 CLOSE c_aca;
988 END IF;
989 IF c_acai%ISOPEN THEN
990 CLOSE c_acai;
991 END IF;
992 APP_EXCEPTION.RAISE_EXCEPTION;
993 END;
994 EXCEPTION
995 WHEN OTHERS THEN
996 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
997 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.admp_upd_acai_recon');
998 IGS_GE_MSG_STACK.ADD;
999 App_Exception.Raise_Exception;
1000 END admp_upd_acai_recon;
1001
1002 PROCEDURE Admp_Upd_Adm_Pp(
1003 p_oracle_username IN VARCHAR2 ,
1004 p_adm_acad_cal_type IN VARCHAR2 ,
1005 p_adm_acad_ci_sequence_number IN NUMBER ,
1006 p_adm_adm_cal_type IN VARCHAR2 ,
1007 p_adm_adm_ci_sequence_number IN NUMBER ,
1008 p_adm_admission_cat IN VARCHAR2 ,
1009 p_adm_s_admission_process_type IN VARCHAR2 )
1010 IS
1011 gv_other_detail VARCHAR2(255);
1012 BEGIN -- admp_upd_adm_pp
1013 -- routine to update the admission values on the IGS_PE_PERS_PREFS table
1014 DECLARE
1015 e_resource_busy EXCEPTION;
1016 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
1017 CURSOR c_pp (
1018 cp_pp_per_id IN IGS_PE_PERSON.person_id%TYPE)IS
1019 -- SELECT 'x'
1020 SELECT ROWID, pp.*
1021 FROM IGS_PE_PERS_PREFS_ALL pp
1022 WHERE pp.person_id = cp_pp_per_id
1023 FOR UPDATE OF enr_acad_cal_type NOWAIT;
1024
1025 Rec_IGS_PE_PERS_PREFS C_PP%ROWTYPE;
1026 v_person_id IGS_PE_PERSON.person_id%TYPE;
1027 v_check CHAR;
1028 lv_rowid VARCHAR2(25);
1029 l_org_id NUMBER(15);
1030 BEGIN
1031 -- Select the person and then update or insert the Admission Person Preferences
1032 -- table
1033 -- OPEN c_pe(
1034 -- p_oracle_username);
1035 -- FETCH c_pe INTO v_person_id;
1036
1037 v_person_id := FND_GLOBAL.USER_ID;
1038
1039 BEGIN -- sub-block
1040 -- IF (c_pe%FOUND) THEN
1041 OPEN c_pp (v_person_id);
1042 -- FETCH c_pp INTO v_check;
1043
1044 FETCH c_pp INTO Rec_IGS_PE_PERS_PREFS;
1045
1046 IF (c_pp%FOUND) THEN
1047
1048 IGS_PE_PERS_PREFS_Pkg.Update_Row (
1049 X_Mode => 'R',
1050 X_RowId => Rec_IGS_PE_PERS_PREFS.RowId,
1051 X_Person_Id => Rec_IGS_PE_PERS_PREFS.Person_Id,
1052 X_Enr_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Cal_Type,
1053 X_Enr_Acad_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Sequence_Number,
1054 X_Enr_Enrolment_Cat => Rec_IGS_PE_PERS_PREFS.Enr_Enrolment_Cat,
1055 X_Enr_Enr_Method_Type => Rec_IGS_PE_PERS_PREFS.Enr_Enr_Method_Type,
1056 X_Adm_Acad_Cal_Type => p_adm_acad_cal_type,
1057 X_Adm_Acad_Ci_Sequence_Number => p_adm_acad_ci_sequence_number,
1058 X_Adm_Adm_Cal_Type => p_adm_adm_cal_type,
1059 X_Adm_Adm_Ci_Sequence_Number => p_adm_adm_ci_sequence_number,
1060 X_Adm_Admission_Cat => p_adm_admission_cat,
1061 X_Adm_S_Admission_Process_Type => p_adm_s_admission_process_type,
1062 X_Enq_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enq_Acad_Cal_Type,
1063 X_Enq_Acad_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enq_Acad_Ci_Sequence_Number,
1064 X_Enq_Adm_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enq_Adm_Cal_Type,
1065 X_Enq_Adm_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enq_Adm_Ci_Sequence_Number,
1066 X_Server_Printer_Dflt => Rec_IGS_PE_PERS_PREFS.Server_Printer_Dflt,
1067 X_Allow_Stnd_Req_Ind => Rec_IGS_PE_PERS_PREFS.Allow_Stnd_Req_Ind
1068 );
1069
1070 ELSE
1071 l_org_id := igs_ge_gen_003.get_org_id;
1072 IGS_PE_PERS_PREFS_Pkg.Insert_Row (
1073 X_Mode => 'R',
1074 X_Org_Id => l_org_id,
1075 X_RowId => lv_rowid,
1076 X_Person_Id => v_person_id,
1077 X_Enr_Acad_Cal_Type => Null,
1078 X_Enr_Acad_Sequence_Number => Null,
1079 X_Enr_Enrolment_Cat => Null,
1080 X_Enr_Enr_Method_Type => Null,
1081 X_Adm_Acad_Cal_Type => p_adm_acad_cal_type,
1082 X_Adm_Acad_Ci_Sequence_Number => p_adm_acad_ci_sequence_number,
1083 X_Adm_Adm_Cal_Type => p_adm_adm_cal_type,
1084 X_Adm_Adm_Ci_Sequence_Number => p_adm_adm_ci_sequence_number,
1085 X_Adm_Admission_Cat => p_adm_admission_cat,
1086 X_Adm_S_Admission_Process_Type => p_adm_s_admission_process_type,
1087 X_Enq_Acad_Cal_Type => Null,
1088 X_Enq_Acad_Ci_Sequence_Number => Null,
1089 X_Enq_Adm_Cal_Type => Null,
1090 X_Enq_Adm_Ci_Sequence_Number => Null,
1091 X_Server_Printer_Dflt => Null,
1092 X_Allow_Stnd_Req_Ind => 'N'
1093 );
1094
1095
1096 END IF;
1097 COMMIT;
1098 CLOSE c_pp;
1099 -- END IF;
1100 EXCEPTION
1101 WHEN e_resource_busy THEN
1102 -- CLOSE c_pe;
1103 APP_EXCEPTION.RAISE_EXCEPTION;
1104 END; -- sub-block
1105
1106 --CLOSE c_pe;
1107 END;
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1111 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.admp_upd_adm_pp');
1112 IGS_GE_MSG_STACK.ADD;
1113 App_Exception.Raise_Exception;
1114 END admp_upd_adm_pp;
1115
1116 PROCEDURE Admp_Upd_Enq_Pp(
1117 p_oracle_username IN VARCHAR2 ,
1118 p_enq_acad_cal_type IN VARCHAR2 ,
1119 p_enq_acad_ci_sequence_number IN NUMBER ,
1120 p_enq_adm_cal_type IN VARCHAR2 ,
1121 p_enq_adm_ci_sequence_number IN NUMBER )
1122 IS
1123 gv_other_detail VARCHAR2(255);
1124 BEGIN -- admp_upd_enq_pp
1125 -- routine to update the enquiry values on the IGS_PE_PERS_PREFS table
1126 DECLARE
1127 e_resource_busy EXCEPTION;
1128 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
1129 CURSOR c_pp (
1130 cp_pp_per_id IN IGS_PE_PERSON.person_id%TYPE)IS
1131 -- SELECT 'x'
1132 SELECT ROWID, pp.*
1133 FROM IGS_PE_PERS_PREFS_ALL pp
1134 WHERE pp.person_id = cp_pp_per_id
1135 FOR UPDATE OF enr_acad_cal_type NOWAIT;
1136
1137 Rec_IGS_PE_PERS_PREFS c_pp%ROWTYPE;
1138 v_person_id IGS_PE_PERSON.person_id%TYPE;
1139 v_check CHAR;
1140 lv_rowid VARCHAR2(25);
1141 l_org_id NUMBER(15);
1142 BEGIN
1143 -- Select the person and then update or insert the Admission Person Preferences
1144 -- table
1145 --OPEN c_pe(
1146 -- p_oracle_username);
1147 --FETCH c_pe INTO v_person_id;
1148 v_person_id := FND_GLOBAL.USER_ID;
1149 BEGIN -- sub-block
1150 -- IF (c_pe%FOUND) THEN
1151 OPEN c_pp (v_person_id);
1152 -- FETCH c_pp INTO v_check;
1153 FETCH c_pp INTO Rec_IGS_PE_PERS_PREFS;
1154 IF (c_pp%FOUND) THEN
1155
1156 IGS_PE_PERS_PREFS_Pkg.Update_Row (
1157 X_Mode => 'R',
1158 X_RowId => Rec_IGS_PE_PERS_PREFS.RowId,
1159 X_Person_Id => Rec_IGS_PE_PERS_PREFS.Person_Id,
1160 X_Enr_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Cal_Type,
1161 X_Enr_Acad_Sequence_Number => Rec_IGS_PE_PERS_PREFS.Enr_Acad_Sequence_Number,
1162 X_Enr_Enrolment_Cat => Rec_IGS_PE_PERS_PREFS.Enr_Enrolment_Cat,
1163 X_Enr_Enr_Method_Type => Rec_IGS_PE_PERS_PREFS.Enr_Enr_Method_Type,
1164 X_Adm_Acad_Cal_Type => Rec_IGS_PE_PERS_PREFS.adm_acad_cal_type,
1165 X_Adm_Acad_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.adm_acad_ci_sequence_number,
1166 X_Adm_Adm_Cal_Type => Rec_IGS_PE_PERS_PREFS.adm_adm_cal_type,
1167 X_Adm_Adm_Ci_Sequence_Number => Rec_IGS_PE_PERS_PREFS.adm_adm_ci_sequence_number,
1168 X_Adm_Admission_Cat => Rec_IGS_PE_PERS_PREFS.adm_admission_cat,
1169 X_Adm_S_Admission_Process_Type => Rec_IGS_PE_PERS_PREFS.adm_s_admission_process_type,
1170 X_Enq_Acad_Cal_Type => p_enq_acad_cal_type,
1171 X_Enq_Acad_Ci_Sequence_Number => p_enq_acad_ci_sequence_number,
1172 X_Enq_Adm_Cal_Type => p_enq_adm_cal_type,
1173 X_Enq_Adm_Ci_Sequence_Number => p_enq_adm_ci_sequence_number,
1174 X_Server_Printer_Dflt => Rec_IGS_PE_PERS_PREFS.Server_Printer_Dflt,
1175 X_Allow_Stnd_Req_Ind => Rec_IGS_PE_PERS_PREFS.Allow_Stnd_Req_Ind
1176 );
1177
1178 ELSE
1179 l_org_id := igs_ge_gen_003.get_org_id;
1180 IGS_PE_PERS_PREFS_Pkg.Insert_Row (
1181 X_Mode => 'R',
1182 X_org_id => l_org_id,
1183 X_RowId => lv_rowid,
1184 X_Person_Id => v_person_id,
1185 X_Enr_Acad_Cal_Type => Null,
1186 X_Enr_Acad_Sequence_Number => Null,
1187 X_Enr_Enrolment_Cat => Null,
1188 X_Enr_Enr_Method_Type => Null,
1189 X_Adm_Acad_Cal_Type => Null,
1190 X_Adm_Acad_Ci_Sequence_Number => Null,
1191 X_Adm_Adm_Cal_Type => Null,
1192 X_Adm_Adm_Ci_Sequence_Number => Null,
1193 X_Adm_Admission_Cat => Null,
1194 X_Adm_S_Admission_Process_Type => Null,
1195 X_Enq_Acad_Cal_Type => p_enq_acad_cal_type,
1196 X_Enq_Acad_Ci_Sequence_Number => p_enq_acad_ci_sequence_number,
1197 X_Enq_Adm_Cal_Type => p_enq_adm_cal_type,
1198 X_Enq_Adm_Ci_Sequence_Number => p_enq_adm_ci_sequence_number,
1199 X_Server_Printer_Dflt => Null,
1200 X_Allow_Stnd_Req_Ind => 'N'
1201 );
1202
1203
1204 END IF;
1205 COMMIT;
1206 CLOSE c_pp;
1207 -- END IF;
1208 EXCEPTION
1209 WHEN e_resource_busy THEN
1210 --CLOSE c_pe;
1211 APP_EXCEPTION.RAISE_EXCEPTION;
1212 END; -- sub-block
1213 --CLOSE c_pe;
1214 END;
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1218 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.admp_upd_enq_pp');
1219 IGS_GE_MSG_STACK.ADD;
1220 App_Exception.Raise_Exception;
1221 END admp_upd_enq_pp;
1222
1223 FUNCTION Adms_Get_Acaiu_Uv(
1224 p_unit_cd IN VARCHAR2 ,
1225 p_version_number IN NUMBER ,
1226 p_s_admission_process_type IN VARCHAR2 ,
1227 p_offer_ind IN VARCHAR2 )
1228 RETURN VARCHAR2 IS
1229 v_message_name varchar2(30);
1230 BEGIN
1231 IF IGS_AD_VAL_ACAIU.admp_val_acaiu_uv (
1232 p_unit_cd,
1233 p_version_number,
1234 p_s_admission_process_type,
1235 p_offer_ind,
1236 v_message_name) = TRUE THEN
1237 --Unit Offering Option Unit Version is valid
1238 Return 'Y';
1239 ELSE
1240 -- Unit Offering Unit Version is not valid
1241 Return 'N';
1242 END IF;
1243 END adms_get_acaiu_uv;
1244
1245 FUNCTION ret_group_cd RETURN VARCHAR2 IS
1246
1247 l_group_cd igs_pe_persid_group.group_cd%TYPE;
1248
1249 FUNCTION ret_random RETURN VARCHAR2 IS
1250
1251 --/* Linear congruential random number generator */
1252 --
1253 m constant number:=100000000;
1254 m1 constant number:=10000;
1255 b constant number:=31415821;
1256 --
1257 a number;
1258 --
1259 the_date date;
1260 days number;
1261 secs number;
1262
1263 l_ret_char VARCHAR2(7);
1264
1265 --/*-------------------------- mult ---------------------------*/
1266 --/* Private utility function */
1267 --
1268 FUNCTION mult(p in number, q in number) return number is
1269 p1 number;
1270 p0 number;
1271 q1 number;
1272 q0 number;
1273 BEGIN
1274 p1:=trunc(p/m1);
1275 p0:=mod(p,m1);
1276 q1:=trunc(q/m1);
1277 q0:=mod(q,m1);
1278 return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m));
1279 END mult; /* mult */
1280
1281 BEGIN
1282 --/* package body random */
1283 -- /* Generate an initial seed "a" based on system date */
1284 -- /* (Must be connected to database.) */
1285 the_date:=sysdate;
1286 days:=IGS_GE_NUMBER.TO_NUM(to_char(the_date, 'J'));
1287 secs:=IGS_GE_NUMBER.TO_NUM(to_char(the_date, 'SSSSS'));
1288 a:=days*24*3600+secs;
1289
1290
1291 -- /* generate a random number and set it to be the new seed */
1292 a:=mod(mult(a,b)+1,m);
1293
1294 a := a*10000000;
1295 a := floor (a);
1296
1297 l_ret_char := LPAD ( FND_NUMBER.NUMBER_TO_CANONICAL ( a), 7, '*');
1298
1299 RETURN l_ret_char;
1300
1301 END ret_random;
1302
1303 FUNCTION is_dup_grpcd_exists ( p_group_cd VARCHAR2) RETURN BOOLEAN
1304 IS
1305 CURSOR dup_group_cd_cur IS
1306 SELECT
1307 group_cd
1308 FROM
1309 igs_pe_persid_group
1310 WHERE
1311 group_cd = p_group_cd;
1312
1313 l_dup_group_cd igs_pe_persid_group.group_cd%TYPE DEFAULT NULL;
1314
1315 BEGIN
1316
1317 OPEN dup_group_cd_cur;
1318 FETCH dup_group_cd_cur INTO l_dup_group_cd;
1319 CLOSE dup_group_cd_cur;
1320
1321 IF l_dup_group_cd IS NOT NULL THEN
1322 RETURN TRUE;
1323 ELSE
1324 RETURN FALSE;
1325 END IF;
1326 END is_dup_grpcd_exists;
1327
1328 BEGIN
1329
1330 LOOP
1331
1332 l_group_cd := ret_random();
1333 l_group_cd := 'ADM' || l_group_cd;
1334
1335 IF NOT is_dup_grpcd_exists ( l_group_cd ) THEN
1336 RETURN l_group_cd;
1337 END IF;
1338 END LOOP;
1339 END ret_group_cd;
1340
1341 --removed the function get_inq_stat_id for IGR migration (bug 2664699) sjlaport
1342
1343 END igs_ad_gen_012;