[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_GEN_012
Source
1 PACKAGE BODY Igs_En_Gen_012 AS
2 /* $Header: IGSEN12B.pls 120.9 2006/04/13 01:52:36 smaddali ship $ */
3 /*-------------------------------------------------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
7 call as a part of bug 3544927.
8 --npalanis 10-JUN-2003 Bug:2923413 igs_pe_typ_instances_pkgs call
9 -- modified for the new employment category column added in the table
10 --pradhakr 16-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
11 -- table to igs_en_sua_api.update_unit_attempt.
12 -- Changes wrt ENCR031 build. Bug#2643207
13 --nalkumar 05-OCT-2001 Modified the IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW call.
14 -- Added four new parameters to call it as per the Bug# 2027984.
15 --Aiyer 10-Oct-2001 Added the column grading schema in all Tbh calls of IGS_EN_SU_ATTEMPT_PKG as a part of the bug 2037897.
16 --pradhakr 07-Dec-2001 Added a column deg_aud_detail_id in the TBH calls
17 -- of IGS_EN_SU_ATTEMPT_PKG as part of
18 -- Degree Audit Interface build.(Bug# 2033208)
19 --svenkata 20-Dec-2001 Added columns student_career_transcript and Student_career_statistics as part of build Career
20 -- Impact Part2 . Bug #2158626
21 --svenkata 07-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
22 -- to table handler procedure calls as part of CCR - ENCR022.
23 --Nishikant 29-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
24 -- as a part of the bug 2172380.
25 --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in TBH call of IGS_EN_SU_ATTEMPT
26 -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
27 -- Added refernces to column ORG_UNIT_CD incall to IGS_EN_SU_ATTEMPT TBH call as a part of bug 1964697
28 --Bayadav 05-May-2002 Included code in ENRP_UPD_SCA_STATUS proc to pass message back to IGSEN036 as a part of
29 -- bug 2335633 to indicate the program attempt status has changed.
30 --Nishikant 15-may-2002 Condition in an IF clause in the function Enrp_Upd_Sca_Discont modified as part of the bug#2364216.
31 --PKPATEL/SSAWHNEY 04-OCT-2002 Bug No: 2600842
32 -- Added the logic for synchronization of expiry date for IGS_PE_FUND_EXCL in the procedure Enrp_Upd_Expiry_Dts
33 --amuthu 08-Oct-2002 Added call to drop_all_workflow as part of Drop Transfer Build.
34 -- the calls to the drop_all workflow is done after either dropping or
35 -- discontinuing a unit attempt. created new local procedure invoke_drop_workflow. Bug 2599925.
36 --kkillams 08-11-2002 As part of Legacy Build bug no:2661533,
37 -- Impacted object, due to addition of new paramter to the enrp_val_sca_discont fuctions
38 --svenkata 20-NOV-2002 Modified the call to the function igs_en_val_sua.enrp_val_sua_discont to add value 'N' for the parameter
39 -- p_legacy. Bug#2661533.
40 --ptandon 21-MAY-2003 Replaced usage of Message IGS_GE_OK with message IGS_AD_OK. Bug#2755657
41 --svanukur 26-jun-2003 Passing discontinued date with a nvl substitution of sysdate in the call to the update_row api of
42 -- ig_en_su_attmept in case of a "dropped" unit attempt status as part of bug 2898213.
43 --smaddali 04-jul-03 modified procedure enrp_upd_sca_coo for bug 3035523 , to update igs_he_st_spa_all.version_number
44 --rvivekan 3-SEP-2003 Waitlist Enhacements build # 3052426. 2 new columns added to
45 -- IGS_EN_SU_ATTEMPT_PKG procedures and consequently to IGS_EN_SUA_API procedures
46 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
47 -- and IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW, added as part of Prevent Dropping Core Units. Enh Bug# 3052432
48 --ptandon 05-DEC-2003 Modified cursor c_sua in Function Enrp_Upd_Sca_Discont as per Placements build. Bug# 3052438.
49 --vkarthik 19-Apr-2004 Modified exception handling section to handle no_ausl_record_found
50 for bug 3526251 in Enrp_Upd_Sca_Discont
51 --amuthu 21-NOV-2004 Modifed as part of program transfer build. When the program transfer is done the enrolled units are
52 -- no longer discontinued they are always dropped. Modified the logic for the same in enrp_upd_sca_discont
53 -- sgurusam 17-Jun-05 Modified function Enrp_Upd_Sca_Discont to add parameters for upd_audit_flag and ss_source_ind
54 --amuthu 11-Oct-05 Modified the exception section of the local procedure enrpl_upd_get_status such that it
55 -- does not throw an exception and stop the student program attempt update job from erroring out
56 -- completely. It will log a message in the log file and continue processing the next student.
57 --bdeviset 17-Jan-05 Modified Enrp_Upd_Sca_Discont. Modifided the logic of making the program as seconadry
58 for Bug# 5020357
59 -- smaddali 10-apr-06 Modified procedure Enrp_Upd_Sca_Statusb for EN324 build - bug#5091858
60 -----------------------------------------------------------------------------------------------------------------------------------------*/
61
62 PROCEDURE Enrp_Upd_Expiry_Dts(
63 p_person_id IN NUMBER ,
64 p_encumbrance_type IN VARCHAR2 ,
65 p_start_dt IN DATE ,
66 p_expiry_dt IN DATE ,
67 p_message_name OUT NOCOPY VARCHAR2)
68 AS
69 /*
70 || Created By : pkpatel
71 || Created On : 27-SEP-2002
72 || Purpose :
73 || Known limitations, enhancements or remarks :
74 || Change History :
75 || Who When What
76 || pkpatel 04-OCT-2002 Bug No: 2600842
77 || Added the logic for synchronization of expiry date for IGS_PE_FUND_EXCL
78 || (reverse chronological order - newest change first)
79 */
80 BEGIN
81 DECLARE
82
83 CURSOR c_psn_encmb_eff (
84 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
85 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
86 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
87 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
88 SELECT ROWID, IGS_PE_PERSENC_EFFCT.*
89 FROM IGS_PE_PERSENC_EFFCT
90 WHERE person_id = cp_person_id AND
91 encumbrance_type = cp_encumbrance_type AND
92 pen_start_dt = cp_start_dt AND
93 (expiry_dt IS NULL OR
94 expiry_dt > cp_expiry_dt)
95 FOR UPDATE OF IGS_PE_PERSENC_EFFCT.person_id NOWAIT;
96
97
98 CURSOR c_psn_crs_grp_excl (
99 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
100 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
101 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
102 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
103 SELECT PCGE.ROWID, PCGE.*
104 FROM IGS_PE_CRS_GRP_EXCL PCGE,
105 IGS_PE_PERSENC_EFFCT PEE
106 WHERE PCGE.person_id = cp_person_id AND
107 PCGE.encumbrance_type = cp_encumbrance_type AND
108 PCGE.pen_start_dt = cp_start_dt AND
109 PCGE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
110 PCGE.pee_start_dt = PEE.pee_start_dt AND
111 (PCGE.expiry_dt IS NULL OR
112 PCGE.expiry_dt > cp_expiry_dt)
113 FOR UPDATE OF PCGE.person_id NOWAIT;
114
115
116 CURSOR c_psn_crs_excl (
117 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
118 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
119 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
120 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
121 SELECT PCE.ROWID, PCE.*
122 FROM IGS_PE_COURSE_EXCL PCE,
123 IGS_PE_PERSENC_EFFCT PEE
124 WHERE PCE.person_id = cp_person_id AND
125 PCE.encumbrance_type = cp_encumbrance_type AND
126 PCE.pen_start_dt = cp_start_dt AND
127 PCE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
128 PCE.pee_start_dt = PEE.pee_start_dt AND
129 (PCE.expiry_dt IS NULL OR
130 PCE.expiry_dt > cp_expiry_dt)
131 FOR UPDATE OF PCE.person_id NOWAIT;
132
133
134 CURSOR c_psn_unit_excl (
135 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
136 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
137 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
138 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
139 SELECT PUE.ROWID,
140 PUE.*
141 FROM IGS_PE_PERS_UNT_EXCL PUE,
142 IGS_PE_PERSENC_EFFCT PEE
143 WHERE PUE.person_id = cp_person_id AND
144 PUE.encumbrance_type = cp_encumbrance_type AND
145 PUE.pen_start_dt = cp_start_dt AND
146 PUE.s_encmb_effect_type = PEE.s_encmb_effect_type AND
147 PUE.pee_start_dt = PEE.pee_start_dt AND
148 (PUE.expiry_dt IS NULL OR
149 PUE.expiry_dt > cp_expiry_dt)
150 FOR UPDATE OF PUE.person_id NOWAIT;
151
152
153 CURSOR c_psn_unit_rqmnt (
154 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
155 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
156 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
157 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
158 SELECT PUR.ROWID,
159 PUR.*
160 FROM IGS_PE_UNT_REQUIRMNT PUR,
161 IGS_PE_PERSENC_EFFCT PEE
162 WHERE PUR.person_id = cp_person_id AND
163 PUR.encumbrance_type = cp_encumbrance_type AND
164 PUR.pen_start_dt = cp_start_dt AND
165 PUR.s_encmb_effect_type = PEE.s_encmb_effect_type AND
166 PUR.pee_start_dt = PEE.pee_start_dt AND
167 (PUR.expiry_dt IS NULL OR
168 PUR.expiry_dt > cp_expiry_dt)
169 FOR UPDATE OF PUR.person_id NOWAIT;
170
171 CURSOR fund_cur(
172 cp_person_id IGS_PE_PERS_ENCUMB.person_id%TYPE,
173 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
174 cp_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
175 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
176 SELECT fun.ROWID,
177 fun.*
178 FROM IGS_PE_FUND_EXCL fun,
179 IGS_PE_PERSENC_EFFCT PEE
180 WHERE fun.person_id = cp_person_id AND
181 fun.encumbrance_type = cp_encumbrance_type AND
182 fun.pen_start_dt = cp_start_dt AND
183 fun.s_encmb_effect_type = PEE.s_encmb_effect_type AND
184 fun.pee_start_dt = PEE.pee_start_dt AND
185 fun.person_id = pee.person_id AND
186 fun.encumbrance_type = pee.encumbrance_type AND
187 fun.pen_start_dt = pee.pee_start_dt AND
188 fun.pee_sequence_number = pee.sequence_number AND
189 (fun.expiry_dt IS NULL OR
190 fun.expiry_dt > cp_expiry_dt)
191 FOR UPDATE OF fun.person_id NOWAIT;
192
193 BEGIN
194 -- This procedure sets the expiry date for all
195 -- child records of the nominated IGS_PE_PERS_ENCUMB
196 -- when the expiry_dt is set.
197 -- SELECTING ALL IGS_PE_PERS_ENCUMB RECORDS
198 FOR v_psn_encmb_eff_rec IN c_psn_encmb_eff(p_person_id,
199 p_encumbrance_type,
200 p_start_dt,
201 p_expiry_dt) LOOP
202 -- for each IGS_PE_PERSENC_EFFCT record returned,
203 -- update the expiry_dt
204
205
206 IF (v_psn_encmb_eff_rec.pee_start_dt > p_expiry_dt) THEN
207
208 Igs_Pe_Persenc_Effct_Pkg.UPDATE_ROW(
209 X_ROWID => v_psn_encmb_eff_rec.ROWID ,
210 X_PERSON_ID => v_psn_encmb_eff_rec.PERSON_ID ,
211 X_ENCUMBRANCE_TYPE => v_psn_encmb_eff_rec.ENCUMBRANCE_TYPE ,
212 X_PEN_START_DT => v_psn_encmb_eff_rec.PEN_START_DT ,
213 X_S_ENCMB_EFFECT_TYPE => v_psn_encmb_eff_rec.S_ENCMB_EFFECT_TYPE ,
214 X_PEE_START_DT => v_psn_encmb_eff_rec.PEE_START_DT ,
215 X_SEQUENCE_NUMBER => v_psn_encmb_eff_rec.SEQUENCE_NUMBER ,
216 X_EXPIRY_DT => v_psn_encmb_eff_rec.pee_start_dt ,
217 X_COURSE_CD => v_psn_encmb_eff_rec.COURSE_CD ,
218 X_RESTRICTED_ENROLMENT_CP => v_psn_encmb_eff_rec.RESTRICTED_ENROLMENT_CP ,
219 X_RESTRICTED_ATTENDANCE_TYPE => v_psn_encmb_eff_rec.RESTRICTED_ATTENDANCE_TYPE ,
220 X_MODE => 'R'
221 );
222
223
224 ELSE
225
226 Igs_Pe_Persenc_Effct_Pkg.UPDATE_ROW(
227 X_ROWID => v_psn_encmb_eff_rec.ROWID ,
228 X_PERSON_ID => v_psn_encmb_eff_rec.PERSON_ID ,
229 X_ENCUMBRANCE_TYPE => v_psn_encmb_eff_rec.ENCUMBRANCE_TYPE ,
230 X_PEN_START_DT => v_psn_encmb_eff_rec.PEN_START_DT ,
231 X_S_ENCMB_EFFECT_TYPE => v_psn_encmb_eff_rec.S_ENCMB_EFFECT_TYPE ,
232 X_PEE_START_DT => v_psn_encmb_eff_rec.PEE_START_DT ,
233 X_SEQUENCE_NUMBER => v_psn_encmb_eff_rec.SEQUENCE_NUMBER ,
234 X_EXPIRY_DT => p_expiry_dt ,
235 X_COURSE_CD => v_psn_encmb_eff_rec.COURSE_CD ,
236 X_RESTRICTED_ENROLMENT_CP => v_psn_encmb_eff_rec.RESTRICTED_ENROLMENT_CP ,
237 X_RESTRICTED_ATTENDANCE_TYPE => v_psn_encmb_eff_rec.RESTRICTED_ATTENDANCE_TYPE ,
238 X_MODE => 'R'
239 );
240
241
242
243
244
245 END IF;
246 -- SELECTING ALL IGS_PE_CRS_GRP_EXCL RECORDS
247 FOR v_psn_crs_grp_excl_rec IN c_psn_crs_grp_excl(p_person_id,
248 p_encumbrance_type,
249 p_start_dt,
250 p_expiry_dt) LOOP
251 -- for each IGS_PE_CRS_GRP_EXCL record returned,
252 -- update the expiry_dt
253 IF (v_psn_crs_grp_excl_rec.pcge_start_dt > p_expiry_dt) THEN
254
255 Igs_Pe_Crs_Grp_Excl_Pkg.UPDATE_ROW(
256 X_ROWID => v_psn_crs_grp_excl_rec.ROWID ,
257 X_PERSON_ID => v_psn_crs_grp_excl_rec.PERSON_ID ,
258 X_ENCUMBRANCE_TYPE => v_psn_crs_grp_excl_rec.ENCUMBRANCE_TYPE ,
259 X_PEN_START_DT => v_psn_crs_grp_excl_rec.PEN_START_DT ,
260 X_S_ENCMB_EFFECT_TYPE => v_psn_crs_grp_excl_rec.S_ENCMB_EFFECT_TYPE ,
261 X_PEE_START_DT => v_psn_crs_grp_excl_rec.PEE_START_DT ,
262 X_PEE_SEQUENCE_NUMBER => v_psn_crs_grp_excl_rec.PEE_SEQUENCE_NUMBER ,
263 X_COURSE_GROUP_CD => v_psn_crs_grp_excl_rec.COURSE_GROUP_CD ,
264 X_PCGE_START_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
265 X_EXPIRY_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
266 X_MODE => 'R'
267 );
268
269
270 ELSE
271 Igs_Pe_Crs_Grp_Excl_Pkg.UPDATE_ROW(
272 X_ROWID => v_psn_crs_grp_excl_rec.ROWID ,
273 X_PERSON_ID => v_psn_crs_grp_excl_rec.PERSON_ID ,
274 X_ENCUMBRANCE_TYPE => v_psn_crs_grp_excl_rec.ENCUMBRANCE_TYPE ,
275 X_PEN_START_DT => v_psn_crs_grp_excl_rec.PEN_START_DT ,
276 X_S_ENCMB_EFFECT_TYPE => v_psn_crs_grp_excl_rec.S_ENCMB_EFFECT_TYPE ,
277 X_PEE_START_DT => v_psn_crs_grp_excl_rec.PEE_START_DT ,
278 X_PEE_SEQUENCE_NUMBER => v_psn_crs_grp_excl_rec.PEE_SEQUENCE_NUMBER ,
279 X_COURSE_GROUP_CD => v_psn_crs_grp_excl_rec.COURSE_GROUP_CD ,
280 X_PCGE_START_DT => v_psn_crs_grp_excl_rec.PCGE_START_DT ,
281 X_EXPIRY_DT => p_expiry_dt ,
282 X_MODE => 'R'
283 );
284
285
286 END IF;
287 END LOOP;
288 -- SELECTING ALL IGS_PE_COURSE_EXCL RECORDS
289
290
291 FOR v_psn_crs_excl_rec IN c_psn_crs_excl(p_person_id,
292 p_encumbrance_type,
293 p_start_dt,
294 p_expiry_dt) LOOP
295 -- for each IGS_PE_COURSE_EXCL record returned,
296 -- update the expiry_dt
297 IF (v_psn_crs_excl_rec.pce_start_dt > p_expiry_dt) THEN
298 Igs_Pe_Course_Excl_Pkg.UPDATE_ROW(
299 X_ROWID => v_psn_crs_excl_rec.ROWID ,
300 X_PERSON_ID => v_psn_crs_excl_rec.PERSON_ID ,
301 X_ENCUMBRANCE_TYPE => v_psn_crs_excl_rec.ENCUMBRANCE_TYPE ,
302 X_PEN_START_DT => v_psn_crs_excl_rec.PEN_START_DT ,
303 X_S_ENCMB_EFFECT_TYPE => v_psn_crs_excl_rec.S_ENCMB_EFFECT_TYPE ,
304 X_PEE_START_DT => v_psn_crs_excl_rec.PEE_START_DT ,
305 X_PEE_SEQUENCE_NUMBER => v_psn_crs_excl_rec.PEE_SEQUENCE_NUMBER ,
306 X_COURSE_CD => v_psn_crs_excl_rec.COURSE_CD ,
307 X_PCE_START_DT => v_psn_crs_excl_rec.PCE_START_DT ,
308 X_EXPIRY_DT => v_psn_crs_excl_rec.pce_start_dt ,
309 X_MODE => 'R'
310 );
311
312
313
314 ELSE
315
316 Igs_Pe_Course_Excl_Pkg.UPDATE_ROW(
317 X_ROWID => v_psn_crs_excl_rec.ROWID ,
318 X_PERSON_ID => v_psn_crs_excl_rec.PERSON_ID ,
319 X_ENCUMBRANCE_TYPE => v_psn_crs_excl_rec.ENCUMBRANCE_TYPE ,
320 X_PEN_START_DT => v_psn_crs_excl_rec.PEN_START_DT ,
321 X_S_ENCMB_EFFECT_TYPE => v_psn_crs_excl_rec.S_ENCMB_EFFECT_TYPE ,
322 X_PEE_START_DT => v_psn_crs_excl_rec.PEE_START_DT ,
323 X_PEE_SEQUENCE_NUMBER => v_psn_crs_excl_rec.PEE_SEQUENCE_NUMBER ,
324 X_COURSE_CD => v_psn_crs_excl_rec.COURSE_CD ,
325 X_PCE_START_DT => v_psn_crs_excl_rec.PCE_START_DT ,
326 X_EXPIRY_DT => p_expiry_dt,
327 X_MODE => 'R'
328 );
329
330
331
332 END IF;
333 END LOOP;
334 -- SELECTING ALL IGS_PE_PERS_UNT_EXCL RECORDS
335 FOR v_psn_unit_excl_rec IN c_psn_unit_excl(p_person_id,
336 p_encumbrance_type,
337 p_start_dt,
338 p_expiry_dt) LOOP
339 -- for each IGS_PE_PERS_UNT_EXCL record returned,
340 -- update the expiry_dt
341 IF (v_psn_unit_excl_rec.pue_start_dt > p_expiry_dt) THEN
342
343 Igs_Pe_Pers_Unt_Excl_Pkg.UPDATE_ROW(
344 X_ROWID => v_psn_unit_excl_rec.ROWID ,
345 X_PERSON_ID => v_psn_unit_excl_rec.PERSON_ID ,
346 X_ENCUMBRANCE_TYPE => v_psn_unit_excl_rec.ENCUMBRANCE_TYPE ,
347 X_PEN_START_DT => v_psn_unit_excl_rec.PEN_START_DT ,
348 X_S_ENCMB_EFFECT_TYPE => v_psn_unit_excl_rec.S_ENCMB_EFFECT_TYPE ,
349 X_PEE_START_DT => v_psn_unit_excl_rec.PEE_START_DT ,
350 X_PEE_SEQUENCE_NUMBER => v_psn_unit_excl_rec.PEE_SEQUENCE_NUMBER ,
351 X_UNIT_CD => v_psn_unit_excl_rec.UNIT_CD ,
352 X_PUE_START_DT => v_psn_unit_excl_rec.PUE_START_DT ,
353 X_EXPIRY_DT => v_psn_unit_excl_rec.pue_start_dt,
354 X_MODE => 'R'
355 );
356
357
358 ELSE
359 Igs_Pe_Pers_Unt_Excl_Pkg.UPDATE_ROW(
360 X_ROWID => v_psn_unit_excl_rec.ROWID ,
361 X_PERSON_ID => v_psn_unit_excl_rec.PERSON_ID ,
362 X_ENCUMBRANCE_TYPE => v_psn_unit_excl_rec.ENCUMBRANCE_TYPE ,
363 X_PEN_START_DT => v_psn_unit_excl_rec.PEN_START_DT ,
364 X_S_ENCMB_EFFECT_TYPE => v_psn_unit_excl_rec.S_ENCMB_EFFECT_TYPE ,
365 X_PEE_START_DT => v_psn_unit_excl_rec.PEE_START_DT ,
366 X_PEE_SEQUENCE_NUMBER => v_psn_unit_excl_rec.PEE_SEQUENCE_NUMBER ,
367 X_UNIT_CD => v_psn_unit_excl_rec.UNIT_CD ,
368 X_PUE_START_DT => v_psn_unit_excl_rec.PUE_START_DT ,
369 X_EXPIRY_DT => p_expiry_dt,
370 X_MODE => 'R'
371 );
372
373 END IF;
374 END LOOP;
375 -- SELECTING ALL IGS_PE_UNT_REQUIRMNT RECORDS
376 FOR v_psn_unit_rqmnt_rec IN c_psn_unit_rqmnt(p_person_id,
377 p_encumbrance_type,
378 p_start_dt,
379 p_expiry_dt) LOOP
380 -- for each IGS_PE_UNT_REQUIRMNT record returned,
381 -- update the expiry_dt
382 IF (v_psn_unit_rqmnt_rec.pur_start_dt > p_expiry_dt) THEN
383
384
385 Igs_Pe_Unt_Requirmnt_Pkg.UPDATE_ROW(
386 X_ROWID =>v_psn_unit_rqmnt_rec.ROWID ,
387 X_PERSON_ID =>v_psn_unit_rqmnt_rec.PERSON_ID ,
388 X_ENCUMBRANCE_TYPE =>v_psn_unit_rqmnt_rec.ENCUMBRANCE_TYPE ,
389 X_PEN_START_DT =>v_psn_unit_rqmnt_rec.PEN_START_DT ,
390 X_S_ENCMB_EFFECT_TYPE =>v_psn_unit_rqmnt_rec.S_ENCMB_EFFECT_TYPE ,
391 X_PEE_START_DT =>v_psn_unit_rqmnt_rec.PEE_START_DT ,
392 X_PEE_SEQUENCE_NUMBER =>v_psn_unit_rqmnt_rec.PEE_SEQUENCE_NUMBER ,
393 X_UNIT_CD =>v_psn_unit_rqmnt_rec.UNIT_CD ,
394 X_PUR_START_DT =>v_psn_unit_rqmnt_rec.PUR_START_DT ,
395 X_EXPIRY_DT => v_psn_unit_rqmnt_rec.pur_start_dt,
396 X_MODE => 'R');
397
398
399
400 ELSE
401
402 Igs_Pe_Unt_Requirmnt_Pkg.UPDATE_ROW(
403 X_ROWID =>v_psn_unit_rqmnt_rec.ROWID ,
404 X_PERSON_ID =>v_psn_unit_rqmnt_rec.PERSON_ID ,
405 X_ENCUMBRANCE_TYPE =>v_psn_unit_rqmnt_rec.ENCUMBRANCE_TYPE ,
406 X_PEN_START_DT =>v_psn_unit_rqmnt_rec.PEN_START_DT ,
407 X_S_ENCMB_EFFECT_TYPE =>v_psn_unit_rqmnt_rec.S_ENCMB_EFFECT_TYPE ,
408 X_PEE_START_DT =>v_psn_unit_rqmnt_rec.PEE_START_DT ,
409 X_PEE_SEQUENCE_NUMBER =>v_psn_unit_rqmnt_rec.PEE_SEQUENCE_NUMBER ,
410 X_UNIT_CD =>v_psn_unit_rqmnt_rec.UNIT_CD ,
411 X_PUR_START_DT =>v_psn_unit_rqmnt_rec.PUR_START_DT ,
412 X_EXPIRY_DT => p_expiry_dt,
413 X_MODE => 'R');
414
415
416
417 END IF;
418
419 END LOOP;
420
421 -- SELECTING ALL IGS_PE_FUND_EXCL RECORDS
422 FOR fund_rec IN fund_cur(p_person_id,
423 p_encumbrance_type,
424 p_start_dt,
425 p_expiry_dt) LOOP
426 -- for each IGS_PE_FUND_EXCL record returned,
427 -- update the expiry_dt
428 IF (fund_rec.pfe_start_dt > p_expiry_dt) THEN
429
430 igs_pe_fund_excl_pkg.update_row(
431 X_ROWID =>fund_rec.ROWID ,
432 X_FUND_EXCL_ID =>fund_rec.FUND_EXCL_ID,
433 X_PERSON_ID =>fund_rec.PERSON_ID ,
434 X_ENCUMBRANCE_TYPE =>fund_rec.ENCUMBRANCE_TYPE ,
435 X_PEN_START_DT =>fund_rec.PEN_START_DT ,
436 X_S_ENCMB_EFFECT_TYPE =>fund_rec.S_ENCMB_EFFECT_TYPE ,
437 X_PEE_START_DT =>fund_rec.PEE_START_DT ,
438 X_PEE_SEQUENCE_NUMBER =>fund_rec.PEE_SEQUENCE_NUMBER ,
439 X_FUND_CODE =>fund_rec.FUND_CODE ,
440 X_PFE_START_DT =>fund_rec.PFE_START_DT ,
441 X_EXPIRY_DT =>fund_rec.pfe_start_dt,
442 X_MODE => 'R');
443 ELSE
444 igs_pe_fund_excl_pkg.update_row(
445 X_ROWID =>fund_rec.ROWID ,
446 X_FUND_EXCL_ID =>fund_rec.FUND_EXCL_ID,
447 X_PERSON_ID =>fund_rec.PERSON_ID ,
448 X_ENCUMBRANCE_TYPE =>fund_rec.ENCUMBRANCE_TYPE ,
449 X_PEN_START_DT =>fund_rec.PEN_START_DT ,
450 X_S_ENCMB_EFFECT_TYPE =>fund_rec.S_ENCMB_EFFECT_TYPE ,
451 X_PEE_START_DT =>fund_rec.PEE_START_DT ,
452 X_PEE_SEQUENCE_NUMBER =>fund_rec.PEE_SEQUENCE_NUMBER ,
453 X_FUND_CODE =>fund_rec.FUND_CODE ,
454 X_PFE_START_DT =>fund_rec.PFE_START_DT ,
455 X_EXPIRY_DT =>p_expiry_dt,
456 X_MODE => 'R');
457
458 END IF;
459
460 END LOOP;
461
462 END LOOP;
463 EXCEPTION
464 WHEN OTHERS THEN
465 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXP');
466 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrp_upd_expiry_dts');
467 IGS_GE_MSG_STACK.ADD;
468 APP_EXCEPTION.RAISE_EXCEPTION;
469 END;
470 END enrp_upd_expiry_dts;
471
472 FUNCTION Enrp_Upd_Sca_Coo(
473 p_person_id IN NUMBER ,
474 p_course_cd IN VARCHAR2 ,
475 p_version_number IN NUMBER ,
476 p_cal_type IN VARCHAR2 ,
477 p_location_cd IN VARCHAR2 ,
478 p_attendance_mode IN VARCHAR2 ,
479 p_attendance_type IN VARCHAR2 ,
480 p_message_name OUT NOCOPY VARCHAR2)
481 RETURN BOOLEAN AS
482 /*
483 || Created By :
484 || Created On :
485 || Purpose :
486 || Known limitations, enhancements or remarks :
487 || Change History :
488 || Who When What
489 || sarakshi 19-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
490 || ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
491 || call as a part of bug 3544927.
492 || smaddali 04-jul-03 Bug No: 3035523
493 || Added the logic for updating the version number in hesa program attempt details belonging to this program attempt
494 || (reverse chronological order - newest change first)
495 || svanukur 17-feb-2004 Added logic to trap the exception IGS_RE_ATND_HIST_STRT_CRS_ATM since this should not be
496 || displayed as an error as per bug 3297473
497 */
498
499 v_other_detail VARCHAR2(255);
500 v_person_id IGS_PE_PERSON.person_id%TYPE;
501 v_course_cd IGS_PS_OFR_OPT.course_cd%TYPE;
502 v_version_number IGS_PS_OFR_OPT.version_number%TYPE;
503 v_cal_type IGS_PS_OFR_OPT.cal_type%TYPE;
504 v_location_cd IGS_PS_OFR_OPT.location_cd%TYPE;
505 v_attendance_mode IGS_PS_OFR_OPT.attendance_mode%TYPE;
506 v_attendance_type IGS_PS_OFR_OPT.attendance_type%TYPE;
507 v_coo_id IGS_PS_OFR_OPT.coo_id%TYPE;
508
509 CURSOR c_IGS_EN_STDNT_PS_ATT IS
510 SELECT ROWID,
511 IGS_EN_STDNT_PS_ATT.*
512 FROM IGS_EN_STDNT_PS_ATT
513 WHERE person_id = p_person_id AND
514 course_cd = p_course_cd
515 FOR UPDATE NOWAIT;
516
517
518 -- cursor added as per the HESA DLD Bug# 3035523.
519 -- get the hesa program attempt details for update
520 CURSOR c_upd_spa IS
521 SELECT spa.rowid , spa.*
522 FROM igs_he_st_spa_all spa
523 WHERE spa.person_id = p_person_id
524 AND spa.course_cd = p_course_cd
525 FOR UPDATE NOWAIT;
526
527 l_enc_message_name VARCHAR2(2000) := NULL;
528 l_app_short_name VARCHAR2(10) := NULL;
529 l_message_name VARCHAR2(100) := NULL;
530 l_mesg_txt VARCHAR2(4000) := NULL;
531 l_msg_index NUMBER;
532
533 BEGIN
534 p_message_name := NULL;
535
536 -- Attempt to lock the record - failure will fall through to
537 -- the exception handler.
538
539 v_course_cd := p_course_cd;
540 v_version_number := p_version_number;
541 v_cal_type := p_cal_type;
542 v_location_cd := p_location_cd;
543 v_attendance_mode := p_attendance_mode;
544 v_attendance_type := p_attendance_type;
545
546 Igs_Ps_Gen_003.crsp_get_coo_key (
547 v_coo_id,
548 v_course_cd,
549 v_version_number,
550 v_cal_type,
551 v_location_cd,
552 v_attendance_mode,
553 v_attendance_type);
554
555 -- Having gotten the lock, update the record.
556
557 FOR c_IGS_EN_STDNT_PS_ATT_rec IN c_IGS_EN_STDNT_PS_ATT LOOP
558
559 Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
560 X_ROWID => c_IGS_EN_STDNT_PS_ATT_rec.ROWID,
561 X_PERSON_ID => c_IGS_EN_STDNT_PS_ATT_rec.PERSON_ID,
562 X_COURSE_CD => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_CD,
563 X_ADVANCED_STANDING_IND => c_IGS_EN_STDNT_PS_ATT_rec.ADVANCED_STANDING_IND,
564 X_FEE_CAT => c_IGS_EN_STDNT_PS_ATT_rec.FEE_CAT,
565 X_CORRESPONDENCE_CAT => c_IGS_EN_STDNT_PS_ATT_rec.CORRESPONDENCE_CAT,
566 X_SELF_HELP_GROUP_IND => c_IGS_EN_STDNT_PS_ATT_rec.SELF_HELP_GROUP_IND,
567 X_LOGICAL_DELETE_DT => c_IGS_EN_STDNT_PS_ATT_rec.LOGICAL_DELETE_DT,
568 X_ADM_ADMISSION_APPL_NUMBER => c_IGS_EN_STDNT_PS_ATT_rec.ADM_ADMISSION_APPL_NUMBER,
569 X_ADM_NOMINATED_COURSE_CD => c_IGS_EN_STDNT_PS_ATT_rec.ADM_NOMINATED_COURSE_CD,
570 X_ADM_SEQUENCE_NUMBER => c_IGS_EN_STDNT_PS_ATT_rec.ADM_SEQUENCE_NUMBER,
571 X_VERSION_NUMBER => p_version_number,
572 X_CAL_TYPE => p_cal_type,
573 X_LOCATION_CD => p_location_cd,
574 X_ATTENDANCE_MODE => p_attendance_mode,
575 X_ATTENDANCE_TYPE => p_attendance_type,
576 X_COO_ID => v_coo_id,
577 X_STUDENT_CONFIRMED_IND => c_IGS_EN_STDNT_PS_ATT_rec.STUDENT_CONFIRMED_IND,
578 X_COMMENCEMENT_DT => c_IGS_EN_STDNT_PS_ATT_rec.COMMENCEMENT_DT,
579 X_COURSE_ATTEMPT_STATUS => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_ATTEMPT_STATUS,
580 X_PROGRESSION_STATUS => c_IGS_EN_STDNT_PS_ATT_rec.PROGRESSION_STATUS,
581 X_DERIVED_ATT_TYPE => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_ATT_TYPE,
582 X_DERIVED_ATT_MODE => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_ATT_MODE,
583 X_PROVISIONAL_IND => c_IGS_EN_STDNT_PS_ATT_rec.PROVISIONAL_IND,
584 X_DISCONTINUED_DT => c_IGS_EN_STDNT_PS_ATT_rec.DISCONTINUED_DT,
585 X_DISCONTINUATION_REASON_CD => c_IGS_EN_STDNT_PS_ATT_rec.DISCONTINUATION_REASON_CD,
586 X_LAPSED_DT => c_IGS_EN_STDNT_PS_ATT_rec.LAPSED_DT,
587 X_FUNDING_SOURCE => c_IGS_EN_STDNT_PS_ATT_rec.FUNDING_SOURCE,
588 X_EXAM_LOCATION_CD => c_IGS_EN_STDNT_PS_ATT_rec.EXAM_LOCATION_CD,
589 X_DERIVED_COMPLETION_YR => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_COMPLETION_YR,
590 X_DERIVED_COMPLETION_PERD => c_IGS_EN_STDNT_PS_ATT_rec.DERIVED_COMPLETION_PERD,
591 X_NOMINATED_COMPLETION_YR => c_IGS_EN_STDNT_PS_ATT_rec.NOMINATED_COMPLETION_YR,
592 X_NOMINATED_COMPLETION_PERD => c_IGS_EN_STDNT_PS_ATT_rec.NOMINATED_COMPLETION_PERD,
593 X_RULE_CHECK_IND => c_IGS_EN_STDNT_PS_ATT_rec.RULE_CHECK_IND,
594 X_WAIVE_OPTION_CHECK_IND => c_IGS_EN_STDNT_PS_ATT_rec.WAIVE_OPTION_CHECK_IND,
595 X_LAST_RULE_CHECK_DT => c_IGS_EN_STDNT_PS_ATT_rec.LAST_RULE_CHECK_DT,
596 X_PUBLISH_OUTCOMES_IND => c_IGS_EN_STDNT_PS_ATT_rec.PUBLISH_OUTCOMES_IND,
597 X_COURSE_RQRMNT_COMPLETE_IND => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_RQRMNT_COMPLETE_IND,
598 X_COURSE_RQRMNTS_COMPLETE_DT => c_IGS_EN_STDNT_PS_ATT_rec.COURSE_RQRMNTS_COMPLETE_DT,
599 X_S_COMPLETED_SOURCE_TYPE => c_IGS_EN_STDNT_PS_ATT_rec.S_COMPLETED_SOURCE_TYPE,
600 X_OVERRIDE_TIME_LIMITATION => c_IGS_EN_STDNT_PS_ATT_rec.OVERRIDE_TIME_LIMITATION,
601 X_MODE => 'R',
602 X_LAST_DATE_OF_ATTENDANCE => c_IGS_EN_STDNT_PS_ATT_rec.LAST_DATE_OF_ATTENDANCE,
603 X_DROPPED_BY => c_IGS_EN_STDNT_PS_ATT_rec.DROPPED_BY,
604 X_IGS_PR_CLASS_STD_ID => c_IGS_EN_STDNT_PS_ATT_rec.IGS_PR_CLASS_STD_ID,
605 -- Added next four parameters as per the Career Impact Build Bug# 2027984
606 x_primary_program_type => c_IGS_EN_STDNT_PS_ATT_rec.primary_program_type,
607 x_primary_prog_type_source => c_IGS_EN_STDNT_PS_ATT_rec.primary_prog_type_source,
608 x_catalog_cal_type => c_IGS_EN_STDNT_PS_ATT_rec.catalog_cal_type,
609 x_catalog_seq_num => c_IGS_EN_STDNT_PS_ATT_rec.catalog_seq_num,
610 x_key_program => c_IGS_EN_STDNT_PS_ATT_rec.key_program,
611 -- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
612 x_override_cmpl_dt => c_IGS_EN_STDNT_PS_ATT_rec.override_cmpl_dt,
613 x_manual_ovr_cmpl_dt_ind => c_IGS_EN_STDNT_PS_ATT_rec.manual_ovr_cmpl_dt_ind,
614 -- added by ckasu as part of bug # 3544927
615 X_ATTRIBUTE_CATEGORY => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE_CATEGORY,
616 X_ATTRIBUTE1 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE1,
617 X_ATTRIBUTE2 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE2,
618 X_ATTRIBUTE3 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE3,
619 X_ATTRIBUTE4 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE4,
620 X_ATTRIBUTE5 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE5,
621 X_ATTRIBUTE6 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE6,
622 X_ATTRIBUTE7 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE7,
623 X_ATTRIBUTE8 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE8,
624 X_ATTRIBUTE9 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE9,
625 X_ATTRIBUTE10 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE10,
626 X_ATTRIBUTE11 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE11,
627 X_ATTRIBUTE12 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE12,
628 X_ATTRIBUTE13 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE13,
629 X_ATTRIBUTE14 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE14,
630 X_ATTRIBUTE15 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE15,
631 X_ATTRIBUTE16 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE16,
632 X_ATTRIBUTE17 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE17,
633 X_ATTRIBUTE18 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE18,
634 X_ATTRIBUTE19 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE19,
635 X_ATTRIBUTE20 => c_IGS_EN_STDNT_PS_ATT_rec.ATTRIBUTE20,
636 X_FUTURE_DATED_TRANS_FLAG => c_IGS_EN_STDNT_PS_ATT_rec.FUTURE_DATED_TRANS_FLAG);
637
638
639
640 -- Start of the New code added as per the HESA bug#3035523
641 IF ( c_igs_en_stdnt_ps_att_rec.version_number <> p_version_number AND
642 fnd_profile.value('OSS_COUNTRY_CODE') = 'GB' ) THEN
643
644 BEGIN
645 FOR c_upd_spa_rec IN c_upd_spa LOOP
646 -- update the version_number of the hesa program attempt record
647 IGS_HE_ST_SPA_ALL_PKG.update_row (
648 x_rowid => c_upd_spa_rec.rowid ,
649 x_hesa_st_spa_id => c_upd_spa_rec.hesa_st_spa_id ,
650 x_org_id => c_upd_spa_rec.org_id ,
651 x_person_id => c_upd_spa_rec.person_id ,
652 x_course_cd => c_upd_spa_rec.course_cd ,
653 x_version_number => p_version_number , -- updated
654 x_fe_student_marker => c_upd_spa_rec.fe_student_marker ,
655 x_domicile_cd => c_upd_spa_rec.domicile_cd ,
656 x_inst_last_attended => c_upd_spa_rec.inst_last_attended ,
657 x_year_left_last_inst => c_upd_spa_rec.year_left_last_inst ,
658 x_highest_qual_on_entry => c_upd_spa_rec.highest_qual_on_entry ,
659 x_date_qual_on_entry_calc => c_upd_spa_rec.date_qual_on_entry_calc ,
660 x_a_level_point_score => c_upd_spa_rec.a_level_point_score ,
661 x_highers_points_scores => c_upd_spa_rec.highers_points_scores ,
662 x_occupation_code => c_upd_spa_rec.occupation_code ,
663 x_commencement_dt => c_upd_spa_rec.commencement_dt ,
664 x_special_student => c_upd_spa_rec.special_student ,
665 x_student_qual_aim => c_upd_spa_rec.student_qual_aim ,
666 x_student_fe_qual_aim => c_upd_spa_rec.student_fe_qual_aim ,
667 x_teacher_train_prog_id => c_upd_spa_rec.teacher_train_prog_id ,
668 x_itt_phase => c_upd_spa_rec.itt_phase ,
669 x_bilingual_itt_marker => c_upd_spa_rec.bilingual_itt_marker ,
670 x_teaching_qual_gain_sector => c_upd_spa_rec.teaching_qual_gain_sector ,
671 x_teaching_qual_gain_subj1 => c_upd_spa_rec.teaching_qual_gain_subj1 ,
672 x_teaching_qual_gain_subj2 => c_upd_spa_rec.teaching_qual_gain_subj2 ,
673 x_teaching_qual_gain_subj3 => c_upd_spa_rec.teaching_qual_gain_subj3 ,
674 x_student_inst_number => c_upd_spa_rec.student_inst_number ,
675 x_destination => c_upd_spa_rec.destination ,
676 x_itt_prog_outcome => c_upd_spa_rec.itt_prog_outcome ,
677 x_hesa_return_name => c_upd_spa_rec.hesa_return_name ,
678 x_hesa_return_id => c_upd_spa_rec.hesa_return_id ,
679 x_hesa_submission_name => c_upd_spa_rec.hesa_submission_name ,
680 x_associate_ucas_number => c_upd_spa_rec.associate_ucas_number ,
681 x_associate_scott_cand => c_upd_spa_rec.associate_scott_cand ,
682 x_associate_teach_ref_num => c_upd_spa_rec.associate_teach_ref_num ,
683 x_associate_nhs_reg_num => c_upd_spa_rec.associate_nhs_reg_num ,
684 x_nhs_funding_source => c_upd_spa_rec.nhs_funding_source ,
685 x_ufi_place => c_upd_spa_rec.ufi_place ,
686 x_postcode => c_upd_spa_rec.postcode ,
687 x_social_class_ind => c_upd_spa_rec.social_class_ind ,
688 x_occcode => c_upd_spa_rec.occcode ,
689 x_total_ucas_tariff => c_upd_spa_rec.total_ucas_tariff ,
690 x_nhs_employer => c_upd_spa_rec.nhs_employer ,
691 x_return_type => c_upd_spa_rec.return_type ,
692 x_qual_aim_subj1 => c_upd_spa_rec.qual_aim_subj1 ,
693 x_qual_aim_subj2 => c_upd_spa_rec.qual_aim_subj2 ,
694 x_qual_aim_subj3 => c_upd_spa_rec.qual_aim_subj3 ,
695 x_qual_aim_proportion => c_upd_spa_rec.qual_aim_proportion ,
696 x_mode => 'R' ,
697 x_dependants_cd => c_upd_spa_rec.dependants_cd ,
698 x_implied_fund_rate => c_upd_spa_rec.implied_fund_rate ,
699 x_gov_initiatives_cd => c_upd_spa_rec.gov_initiatives_cd ,
700 x_units_for_qual => c_upd_spa_rec.units_for_qual ,
701 x_disadv_uplift_elig_cd => c_upd_spa_rec.disadv_uplift_elig_cd ,
702 x_franch_partner_cd => c_upd_spa_rec.franch_partner_cd ,
703 x_units_completed => c_upd_spa_rec.units_completed ,
704 x_franch_out_arr_cd => c_upd_spa_rec.franch_out_arr_cd ,
705 x_employer_role_cd => c_upd_spa_rec.employer_role_cd ,
706 x_disadv_uplift_factor => c_upd_spa_rec.disadv_uplift_factor ,
707 x_enh_fund_elig_cd => c_upd_spa_rec.enh_fund_elig_cd
708 ) ;
709 END LOOP ;
710 EXCEPTION
711 WHEN OTHERS THEN
712 p_message_name := 'IGS_HE_UPD_SPA_FAIL' ;
713 RETURN FALSE ;
714 END ;
715
716 END IF ; -- end of change in version and country code check
717 -- End of the New code added as per the HESA bug#3035523
718
719 -- trap the exception to return true to the calling form along with the message name
720 -- so that the message can be shown as a warnign to the user, bug 3297473.
721
722 IGS_GE_MSG_STACK.GET(-1, 'T', l_enc_message_name, l_msg_index);
723 FND_MESSAGE.PARSE_ENCODED(l_enc_message_name,l_app_short_name,l_message_name);
724 IF l_message_name = 'IGS_RE_ATND_HIST_STRT_CRS_ATM' THEN
725 p_message_name := l_message_name;
726 END IF;
727 END LOOP;
728
729
730 RETURN TRUE;
731
732
733 EXCEPTION
734 --raise any functional messaged raised as errors to teh calling form
735 --to avoid unhandled exceptions when there are valid error messages
736 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
737 RAISE;
738 WHEN OTHERS THEN
739 IF SQLCODE = -54 THEN
740 p_message_name := 'IGS_EN_STUD_PRG_REC_LOCKED';
741 RETURN FALSE;
742 ELSE
743 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
744 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.Enrp_Upd_Sca_Coo');
745 Igs_Ge_Msg_Stack.ADD;
746 App_Exception.Raise_Exception;
747 END IF;
748 END enrp_upd_sca_coo;
749
750
751
752 PROCEDURE invoke_drop_workflow(p_uoo_ids IN VARCHAR2,
753 p_unit_cds IN VARCHAR2,
754 p_teach_cal_type IN VARCHAR2,
755 p_teach_ci_sequence_number IN NUMBER,
756 p_person_id IN NUMBER,
757 p_course_cd IN VARCHAR2,
758 p_source_of_drop IN VARCHAR2,
759 p_message_name IN OUT NOCOPY VARCHAR2)
760 AS
761 /*
762 || Created By :
763 || Created On :
764 || Purpose :
765 || Known limitations, enhancements or remarks :
766 || Change History :
767 || Who When What
768 || kkillams 08-10-2003 Remove the call to drop_all_workflow procedure and setting the
769 || student unit attempt package variables as part of bug#3160856
770 */
771 CURSOR c_tl IS
772 SELECT load_cal_type, load_ci_sequence_number
773 FROM IGS_CA_TEACH_TO_LOAD_V
774 WHERE teach_cal_type = p_teach_cal_type
775 AND teach_ci_sequence_number = p_teach_ci_sequence_number
776 ORDER BY LOAD_START_DT ASC;
777
778 CURSOR c_reason IS
779 SELECT meaning
780 FROM IGS_LOOKUPS_VIEW
781 WHERE lookup_type = 'CRS_ATTEMPT_STATUS'
782 AND lookup_CODE = 'DISCONTIN';
783
784 l_load_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
785 l_load_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
786 l_return_status VARCHAR2(10);
787 l_meaning IGS_LOOKUPS_VIEW.MEANING%TYPE;
788
789 BEGIN
790
791 OPEN c_tl;
792 FETCH c_tl INTO l_load_cal_type, l_load_ci_sequence_number;
793 CLOSE c_tl;
794
795 OPEN c_reason;
796 FETCH c_reason INTO l_meaning;
797 CLOSE c_reason;
798
799 FND_MESSAGE.SET_NAME('IGS','IGS_EN_REASON_DRP_UNIT');
800 FND_MESSAGE.SET_TOKEN('UNIT',p_unit_cds);
801 FND_MESSAGE.SET_TOKEN('REASON',l_meaning);
802 igs_ss_en_wrappers.drop_notif_variable(FND_MESSAGE.GET(),p_source_of_drop );
803
804 END invoke_drop_workflow;
805
806
807
808 FUNCTION Enrp_Upd_Sca_Discont(
809 p_person_id IN NUMBER ,
810 p_course_cd IN VARCHAR2 ,
811 p_version_number IN NUMBER ,
812 p_course_attempt_status IN VARCHAR2 ,
813 p_commencement_dt IN DATE ,
814 p_discontinued_dt IN DATE ,
815 p_discontinuation_reason_cd IN VARCHAR2 ,
816 p_message_name OUT NOCOPY VARCHAR2,
817 p_source IN VARCHAR2,
818 p_transf_course_cd IN VARCHAR2
819 )
820 /*
821 || Created By : pkpatel
822 || Created On : 27-SEP-2002
823 || Purpose :
824 || Known limitations, enhancements or remarks :
825 || Change History :
826 || Who When What
827 || sarakshi 19-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
828 || ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
829 || call as a part of bug 3544927.
830 || kkillams 21-03-2003 Added new parameter p_transf_course_cd to the function.
831 || Which will distigush the from where this function was called.
832 || Value will be passed if function is invoked from program transfer
833 || else null value wil come w.r.t bug 2863707
834 || kkillams 28-04-2003 Modified c_suao_check,c_sua_drop and c_igs_en_su_attempt cursors in this function
835 || due to change in pk of student unit attempt w.r.t. bug number 2829262
836 || rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
837 || and IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW, added as part of Prevent Dropping Core Units. Enh Bug# 3052432
838 || ptandon 05-DEC-2003 Modified the cursor c_sua to include the order by clause to select the subordinate units first
839 || for discontinuation. Placements build. Bug# 3052438.
840 || vkarthik 19-Apr-2004 Modified exception handling section to handle no_ausl_record_found
841 || for bug 3526251
842 || amuthu 23-Dec-2004 Corrected the logic for setting the program as non-key based on the parameter rather than the sct record
843 */
844 RETURN BOOLEAN AS
845 BEGIN
846 DECLARE
847 v_discontinuation_reason_cd IGS_EN_STDNT_PS_ATT.discontinuation_reason_cd%TYPE;
848 v_description IGS_EN_DCNT_REASONCD.description%TYPE;
849 v_administrative_unit_status IGS_EN_SU_ATTEMPT.ADMINISTRATIVE_UNIT_STATUS%TYPE;
850 v_suao_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
851 v_sca_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
852 v_alias_val DATE;
853 v_message_name VARCHAR2(30);
854 v_admin_unit_status_str VARCHAR2(2000);
855 v_other_detail VARCHAR2(255);
856 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
857 cst_lapsed CONSTANT VARCHAR2(6) := 'LAPSED';
858 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
859 cst_intermit CONSTANT VARCHAR2(8) := 'INTERMIT';
860 cst_discontinue CONSTANT VARCHAR2(10) := 'DISCONTIN';
861 cst_dropped CONSTANT VARCHAR2(10) := 'DROPPED';
862 cst_waitlisted CONSTANT VARCHAR2(10) := 'WAITLISTED';
863 l_primary_prg_type igs_en_stdnt_ps_att_all.primary_program_type%TYPE;
864 v_dummy VARCHAR2(1);
865 -- added for bug 3526251
866 NO_AUSL_RECORD_FOUND EXCEPTION;
867 PRAGMA EXCEPTION_INIT(NO_AUSL_RECORD_FOUND , -20010);
868
869 CURSOR c_sua
870 (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
871 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
872 SELECT sua.person_id,
873 sua.course_cd,
874 sua.unit_cd,
875 sua.version_number,
876 sua.cal_type,
877 sua.ci_sequence_number,
878 sua.ci_start_dt,
879 sua.enrolled_dt,
880 sua.unit_attempt_status,
881 sua.uoo_id
882 FROM IGS_EN_SU_ATTEMPT sua
883 WHERE sua.person_id = cp_person_id AND
884 sua.course_cd = cp_course_cd AND
885 sua.unit_attempt_status IN (cst_enrolled,cst_waitlisted)
886 ORDER BY sup_unit_cd ASC
887 FOR UPDATE NOWAIT;
888
889 CURSOR c_suao_check
890 (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
891 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
892 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
893 SELECT suao.person_id
894 FROM IGS_AS_SU_STMPTOUT suao
895 WHERE suao.person_id = cp_person_id AND
896 suao.course_cd = cp_course_cd AND
897 suao.uoo_id = cp_uoo_id;
898
899 CURSOR c_sca_check
900 (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
901 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
902 SELECT sca.course_attempt_status, primary_program_type
903 FROM IGS_EN_STDNT_PS_ATT sca
904 WHERE sca.person_id = cp_person_id AND
905 sca.course_cd = cp_course_cd;
906
907 CURSOR c_prgs_in_career IS
908 SELECT 'X'
909 FROM igs_en_stdnt_ps_att spa, igs_ps_ver pv1, igs_ps_ver pv2
910 WHERE spa.person_id = p_person_id
911 AND spa.course_cd = p_transf_course_cd -- dest course cd
912 AND pv1.course_cd = spa.course_cd
913 AND pv1.version_number = spa.version_number
914 AND pv2.course_cd = p_course_cd
915 AND pv2.version_number = p_version_number
916 AND pv1.course_type = pv2.course_type;
917
918 CURSOR c_unit_dcnt is
919 SELECT DISCONTINUATION_REASON_CD
920 FROM IGS_EN_DCNT_REASONCD
921 WHERE S_DISCONTINUATION_REASON_TYPE = 'UNIT_TRANS'
922 AND DCNT_UNIT_IND = 'Y'
923 AND SYS_DFLT_IND = 'Y'
924 AND CLOSED_IND = 'N';
925
926 l_unt_disc_code IGS_EN_DCNT_REASONCD.DISCONTINUATION_REASON_CD%TYPE := null;
927 l_dflt_disc_code IGS_EN_DCNT_REASONCD.DISCONTINUATION_REASON_CD%TYPE := null;
928
929 CURSOR c_dcnt_rsn IS
930 SELECT discontinuation_reason_cd
931 FROM igs_en_dcnt_reasoncd
932 WHERE NVL(closed_ind,'N') ='N'
933 AND dflt_ind ='Y'
934 AND dcnt_unit_ind ='Y'
935 AND s_discontinuation_reason_type IS NULL;
936
937 BEGIN
938 -- This module updates the IGS_EN_STDNT_PS_ATT future dated
939 -- discontinuation. This will need to do the following :
940 -- 1. Discontinue/delete currently enrolled student_unit_attmmpts :
941 -- a) check to see if a IGS_EN_SU_ATTEMPT can be deleted
942 -- as defined by IGS_PS_UNIT Discontinuation date Criteria
943 -- b) if deletion is allowed, delete
944 -- c) if deletion is not allowed, then discontinue
945 -- IGS_EN_SU_ATTEMPT
946 -- i) get administrative IGS_PS_UNIT status
947 -- ii) validate that the IGS_EN_SU_ATTEMPT
948 -- can be discontinued
949 -- iii) set IGS_EN_SU_ATTEMPT discontinuation
950 -- details
951 -- iv) set IGS_EN_SU_ATTEMPT.unit_attempt_status
952 -- (this is done by table database trigger)
953 -- 2. Validate that the IGS_EN_STDNT_PS_ATT can be
954 -- discontinued
955 -- 3. Discontinue IGS_EN_STDNT_PS_ATT, setting default
956 -- IGS_EN_DCNT_REASONCD
957 -- 4. Set IGS_EN_STDNT_PS_ATT.course_attempt_status (this
958 -- is done by table database trigger).
959 --
960 -- IGS_GE_NOTE : This process will be called to the update student_
961 -- course_attempt.course_attempt_status process that
962 -- should be run by the job schedular on a nightly
963 -- basis.
964 -- validate the input parameters
965 IF (p_person_id IS NULL OR
966 p_course_cd IS NULL OR
967 p_discontinued_dt IS NULL) THEN
968 p_message_name := 'IGS_EN_INSUFF_INFO_SPA_DISCON';
969 RETURN FALSE;
970 END IF;
971 -- establish a savepoint
972 SAVEPOINT sp_discontinue_sua;
973 -- setting the message number beforehand
974 -- so if failure of the lock occurs, this
975 -- value can be passed to the exception handler
976 p_message_name := 'IGS_EN_UNABLE_UPD_STUDENR';
977
978 --get the unit discontinuation reason type as part of transfer
979 OPEN c_unit_dcnt;
980 FETCH c_unit_dcnt into l_unt_disc_code;
981 CLOSE c_unit_dcnt;
982
983 OPEN c_dcnt_rsn;
984 FETCH c_dcnt_rsn INTO l_dflt_disc_code;
985 CLOSE c_dcnt_rsn;
986
987 FOR v_sua IN c_sua(
988 p_person_id,
989 p_course_cd) LOOP
990 -- delete the IGS_EN_SU_ATTEMPT if allowed
991 -- Added the OR clause in the below If condtion OR unit_attempt status is WAITLISTED
992 -- Added by Nishikant - bug#2364216. If the status is WAITLISTED then no need to check whether the unit attempt can be deleted
993 IF (Igs_En_Gen_008.enrp_get_ua_del_alwd(
994 v_sua.cal_type,
995 v_sua.ci_sequence_number,
996 p_discontinued_dt, v_sua.uoo_id) = 'Y'
997 OR v_sua.unit_attempt_status = 'WAITLISTED'
998 OR p_transf_course_cd IS NOT NULL) THEN
999 -- check to see whether child
1000 -- IGS_AS_SU_STMPTOUT records
1001 -- exist, otherwise a delete can not
1002 -- be performed on the IGS_EN_SU_ATTEMPT
1003 -- table
1004 OPEN c_suao_check(
1005 p_person_id,
1006 p_course_cd,
1007 v_sua.uoo_id);
1008 FETCH c_suao_check INTO v_suao_person_id;
1009 IF (c_suao_check%FOUND) THEN
1010 CLOSE c_suao_check;
1011 -- rollback to the savepoint
1012 ROLLBACK TO sp_discontinue_sua;
1013 p_message_name := 'IGS_EN_ONE_SUA_NOT_DISCONT';
1014 RETURN FALSE;
1015 ELSE
1016 CLOSE c_suao_check;
1017 -- delete IGS_EN_SU_ATTEMPT
1018 -- The logic of deleting the Unit attempt status
1019 -- when trying to discontinue has been modified
1020 -- to Update the Record with the Unit Attempt status
1021 -- set to DROPPED
1022 -- amuthu
1023
1024 DECLARE
1025 v_unit_cds VARCHAR2(4000);
1026 v_uoo_ids VARCHAR2(4000);
1027 tr_flag NUMBER(1);
1028 CURSOR c_sua_drop IS
1029 SELECT sua.ROWID, sua.*
1030 FROM IGS_EN_SU_ATTEMPT sua
1031 WHERE person_id = p_person_id AND
1032 course_cd = p_course_cd AND
1033 uoo_id = v_sua.uoo_id;
1034 CURSOR c_trans_cd(cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
1035 SELECT 1
1036 FROM IGS_EN_SU_ATTEMPT sua
1037 WHERE person_id = p_person_id
1038 AND course_cd = p_transf_course_cd
1039 AND uoo_id = cp_uoo_id
1040 AND unit_Attempt_status <> 'DROPPED';
1041 BEGIN
1042 v_uoo_ids := null;
1043 v_unit_cds := null;
1044 FOR sua_drop_rec IN c_sua_drop LOOP
1045 --Following validation is added as part of bug fix.2860412
1046 --Cursor will checks the context unit attempt got transferred or not.
1047 --If Unit got transferred then call the direct tbh call else
1048 --call the igs_en_sua_api.update_unit_attempt.
1049 --Reason is,igs_en_sua_api.update_unit_attempt function will
1050 --decrement available seats for that unit section.
1051 tr_flag:=0;
1052 OPEN c_trans_cd(sua_drop_rec.uoo_id);
1053 FETCH c_trans_cd INTO tr_flag;
1054 CLOSE c_trans_cd;
1055 invoke_drop_workflow(
1056 p_uoo_ids => v_uoo_ids,
1057 p_unit_cds => v_unit_cds,
1058 p_teach_cal_type => v_sua.cal_type,
1059 p_teach_ci_sequence_number => v_sua.ci_sequence_number,
1060 p_person_id => p_person_id,
1061 p_course_cd => p_course_cd,
1062 p_source_of_drop => p_source,
1063 p_message_name => v_message_name
1064 );
1065 IF (p_transf_course_cd IS NULL OR tr_flag =0)
1066 THEN
1067 -- Call the API to update the student unit attempt. This API is a
1068 -- wrapper to the update row of the TBH.
1069 igs_en_sua_api.update_unit_attempt(
1070 X_ROWID => sua_drop_rec.ROWID,
1071 X_PERSON_ID => sua_drop_rec.PERSON_ID,
1072 X_COURSE_CD => sua_drop_rec.COURSE_CD ,
1073 X_UNIT_CD => sua_drop_rec.UNIT_CD,
1074 X_CAL_TYPE => sua_drop_rec.CAL_TYPE,
1075 X_CI_SEQUENCE_NUMBER => sua_drop_rec.CI_SEQUENCE_NUMBER ,
1076 X_VERSION_NUMBER => sua_drop_rec.VERSION_NUMBER ,
1077 X_LOCATION_CD => sua_drop_rec.LOCATION_CD,
1078 X_UNIT_CLASS => sua_drop_rec.UNIT_CLASS ,
1079 X_CI_START_DT => sua_drop_rec.CI_START_DT,
1080 X_CI_END_DT => sua_drop_rec.CI_END_DT,
1081 X_UOO_ID => sua_drop_rec.UOO_ID ,
1082 X_ENROLLED_DT => sua_drop_rec.ENROLLED_DT,
1083 X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
1084 X_ADMINISTRATIVE_UNIT_STATUS => sua_drop_rec.administrative_unit_status,
1085 X_ADMINISTRATIVE_PRIORITY => sua_drop_rec.administrative_PRIORITY,
1086 X_DISCONTINUED_DT => nvl(sua_drop_rec.discontinued_dt,trunc(SYSDATE)),
1087 X_DCNT_REASON_CD => l_dflt_disc_code,
1088 X_RULE_WAIVED_DT => sua_drop_rec.RULE_WAIVED_DT ,
1089 X_RULE_WAIVED_PERSON_ID => sua_drop_rec.RULE_WAIVED_PERSON_ID ,
1090 X_NO_ASSESSMENT_IND => sua_drop_rec.NO_ASSESSMENT_IND,
1091 X_SUP_UNIT_CD => sua_drop_rec.SUP_UNIT_CD ,
1092 X_SUP_VERSION_NUMBER => sua_drop_rec.SUP_VERSION_NUMBER,
1093 X_EXAM_LOCATION_CD => sua_drop_rec.EXAM_LOCATION_CD,
1094 X_ALTERNATIVE_TITLE => sua_drop_rec.ALTERNATIVE_TITLE,
1095 X_OVERRIDE_ENROLLED_CP => sua_drop_rec.OVERRIDE_ENROLLED_CP,
1096 X_OVERRIDE_EFTSU => sua_drop_rec.OVERRIDE_EFTSU ,
1097 X_OVERRIDE_ACHIEVABLE_CP => sua_drop_rec.OVERRIDE_ACHIEVABLE_CP,
1098 X_OVERRIDE_OUTCOME_DUE_DT => sua_drop_rec.OVERRIDE_OUTCOME_DUE_DT,
1099 X_OVERRIDE_CREDIT_REASON => sua_drop_rec.OVERRIDE_CREDIT_REASON,
1100 X_WAITLIST_DT => sua_drop_rec.waitlist_dt,
1101 X_MODE => 'R',
1102 -- Added 5 columns as part of Enroll Process build - amuthu
1103 X_GS_VERSION_NUMBER => sua_drop_rec.gs_version_number,
1104 X_ENR_METHOD_TYPE => sua_drop_rec.enr_method_type,
1105 X_FAILED_UNIT_RULE => sua_drop_rec.FAILED_UNIT_RULE,
1106 X_CART => sua_drop_rec.CART,
1107 X_RSV_SEAT_EXT_ID => sua_drop_rec.RSV_SEAT_EXT_ID ,
1108 X_ORG_UNIT_CD => sua_drop_rec.org_unit_cd ,
1109 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1110 X_SESSION_ID => sua_drop_rec.session_id,
1111 -- Added the column grading schema as a part of the bug 2037897. - aiyer
1112 X_GRADING_SCHEMA_CODE => sua_drop_rec.grading_schema_code,
1113 X_DEG_AUD_DETAIL_ID => sua_drop_rec.deg_aud_detail_id,
1114 X_SUBTITLE => sua_drop_rec.subtitle,
1115 X_STUDENT_CAREER_TRANSCRIPT => sua_drop_rec.student_career_transcript,
1116 X_STUDENT_CAREER_STATISTICS => sua_drop_rec.student_career_statistics,
1117 X_ATTRIBUTE_CATEGORY => sua_drop_rec.attribute_category,
1118 X_ATTRIBUTE1 => sua_drop_rec.attribute1,
1119 X_ATTRIBUTE2 => sua_drop_rec.attribute2,
1120 X_ATTRIBUTE3 => sua_drop_rec.attribute3,
1121 X_ATTRIBUTE4 => sua_drop_rec.attribute4,
1122 X_ATTRIBUTE5 => sua_drop_rec.attribute5,
1123 X_ATTRIBUTE6 => sua_drop_rec.attribute6,
1124 X_ATTRIBUTE7 => sua_drop_rec.attribute7,
1125 X_ATTRIBUTE8 => sua_drop_rec.attribute8,
1126 X_ATTRIBUTE9 => sua_drop_rec.attribute9,
1127 X_ATTRIBUTE10 => sua_drop_rec.attribute10,
1128 X_ATTRIBUTE11 => sua_drop_rec.attribute11,
1129 X_ATTRIBUTE12 => sua_drop_rec.attribute12,
1130 X_ATTRIBUTE13 => sua_drop_rec.attribute13,
1131 X_ATTRIBUTE14 => sua_drop_rec.attribute14,
1132 X_ATTRIBUTE15 => sua_drop_rec.attribute15,
1133 X_ATTRIBUTE16 => sua_drop_rec.attribute16,
1134 X_ATTRIBUTE17 => sua_drop_rec.attribute17,
1135 X_ATTRIBUTE18 => sua_drop_rec.attribute18,
1136 X_ATTRIBUTE19 => sua_drop_rec.attribute19,
1137 X_ATTRIBUTE20 => sua_drop_rec.attribute20,
1138 X_WAITLIST_MANUAL_IND => sua_drop_rec.waitlist_manual_ind, --Added by mesriniv for Bug 2554109 Mini Waitlist Build.
1139 X_WLST_PRIORITY_WEIGHT_NUM => sua_drop_rec.wlst_priority_weight_num,
1140 X_WLST_PREFERENCE_WEIGHT_NUM => sua_drop_rec.wlst_preference_weight_num,
1141 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
1142 X_CORE_INDICATOR_CODE => sua_drop_rec.core_indicator_code
1143 );
1144 ELSE
1145 IF l_unt_disc_code IS NULL THEN
1146 -- implies no system reason for unit drop due to transfer is setup
1147 --hence throw error and return
1148 ROLLBACK to sp_discontinue_sua;
1149 p_message_name := 'IGS_EN_NO_SYS_DFLT_REASON';
1150 RETURN FALSE;
1151 END IF;
1152
1153 IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW(
1154 X_ROWID => sua_drop_rec.ROWID,
1155 X_PERSON_ID => sua_drop_rec.PERSON_ID,
1156 X_COURSE_CD => sua_drop_rec.COURSE_CD ,
1157 X_UNIT_CD => sua_drop_rec.UNIT_CD,
1158 X_CAL_TYPE => sua_drop_rec.CAL_TYPE,
1159 X_CI_SEQUENCE_NUMBER => sua_drop_rec.CI_SEQUENCE_NUMBER ,
1160 X_VERSION_NUMBER => sua_drop_rec.VERSION_NUMBER ,
1161 X_LOCATION_CD => sua_drop_rec.LOCATION_CD,
1162 X_UNIT_CLASS => sua_drop_rec.UNIT_CLASS ,
1163 X_CI_START_DT => sua_drop_rec.CI_START_DT,
1164 X_CI_END_DT => sua_drop_rec.CI_END_DT,
1165 X_UOO_ID => sua_drop_rec.UOO_ID ,
1166 X_ENROLLED_DT => sua_drop_rec.ENROLLED_DT,
1167 X_UNIT_ATTEMPT_STATUS => cst_dropped, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
1168 X_ADMINISTRATIVE_UNIT_STATUS => sua_drop_rec.administrative_unit_status,
1169 X_ADMINISTRATIVE_PRIORITY => sua_drop_rec.administrative_PRIORITY,
1170 X_DISCONTINUED_DT => nvl(sua_drop_rec.discontinued_dt,SYSDATE),
1171 X_DCNT_REASON_CD => l_unt_disc_code,
1172 X_RULE_WAIVED_DT => sua_drop_rec.RULE_WAIVED_DT ,
1173 X_RULE_WAIVED_PERSON_ID => sua_drop_rec.RULE_WAIVED_PERSON_ID ,
1174 X_NO_ASSESSMENT_IND => sua_drop_rec.NO_ASSESSMENT_IND,
1175 X_SUP_UNIT_CD => sua_drop_rec.SUP_UNIT_CD ,
1176 X_SUP_VERSION_NUMBER => sua_drop_rec.SUP_VERSION_NUMBER,
1177 X_EXAM_LOCATION_CD => sua_drop_rec.EXAM_LOCATION_CD,
1178 X_ALTERNATIVE_TITLE => sua_drop_rec.ALTERNATIVE_TITLE,
1179 X_OVERRIDE_ENROLLED_CP => sua_drop_rec.OVERRIDE_ENROLLED_CP,
1180 X_OVERRIDE_EFTSU => sua_drop_rec.OVERRIDE_EFTSU ,
1181 X_OVERRIDE_ACHIEVABLE_CP => sua_drop_rec.OVERRIDE_ACHIEVABLE_CP,
1182 X_OVERRIDE_OUTCOME_DUE_DT => sua_drop_rec.OVERRIDE_OUTCOME_DUE_DT,
1183 X_OVERRIDE_CREDIT_REASON => sua_drop_rec.OVERRIDE_CREDIT_REASON,
1184 X_WAITLIST_DT => sua_drop_rec.waitlist_dt,
1185 X_MODE => 'R',
1186 -- Added 5 columns as part of Enroll Process build - amuthu
1187 X_GS_VERSION_NUMBER => sua_drop_rec.gs_version_number,
1188 X_ENR_METHOD_TYPE => sua_drop_rec.enr_method_type,
1189 X_FAILED_UNIT_RULE => sua_drop_rec.FAILED_UNIT_RULE,
1190 X_CART => sua_drop_rec.CART,
1191 X_RSV_SEAT_EXT_ID => sua_drop_rec.RSV_SEAT_EXT_ID ,
1192 X_ORG_UNIT_CD => sua_drop_rec.org_unit_cd ,
1193 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1194 X_SESSION_ID => sua_drop_rec.session_id,
1195 -- Added the column grading schema as a part of the bug 2037897. - aiyer
1196 X_GRADING_SCHEMA_CODE => sua_drop_rec.grading_schema_code,
1197 X_DEG_AUD_DETAIL_ID => sua_drop_rec.deg_aud_detail_id,
1198 X_SUBTITLE => sua_drop_rec.subtitle,
1199 X_STUDENT_CAREER_TRANSCRIPT => sua_drop_rec.student_career_transcript,
1200 X_STUDENT_CAREER_STATISTICS => sua_drop_rec.student_career_statistics,
1201 X_ATTRIBUTE_CATEGORY => sua_drop_rec.attribute_category,
1202 X_ATTRIBUTE1 => sua_drop_rec.attribute1,
1203 X_ATTRIBUTE2 => sua_drop_rec.attribute2,
1204 X_ATTRIBUTE3 => sua_drop_rec.attribute3,
1205 X_ATTRIBUTE4 => sua_drop_rec.attribute4,
1206 X_ATTRIBUTE5 => sua_drop_rec.attribute5,
1207 X_ATTRIBUTE6 => sua_drop_rec.attribute6,
1208 X_ATTRIBUTE7 => sua_drop_rec.attribute7,
1209 X_ATTRIBUTE8 => sua_drop_rec.attribute8,
1210 X_ATTRIBUTE9 => sua_drop_rec.attribute9,
1211 X_ATTRIBUTE10 => sua_drop_rec.attribute10,
1212 X_ATTRIBUTE11 => sua_drop_rec.attribute11,
1213 X_ATTRIBUTE12 => sua_drop_rec.attribute12,
1214 X_ATTRIBUTE13 => sua_drop_rec.attribute13,
1215 X_ATTRIBUTE14 => sua_drop_rec.attribute14,
1216 X_ATTRIBUTE15 => sua_drop_rec.attribute15,
1217 X_ATTRIBUTE16 => sua_drop_rec.attribute16,
1218 X_ATTRIBUTE17 => sua_drop_rec.attribute17,
1219 X_ATTRIBUTE18 => sua_drop_rec.attribute18,
1220 X_ATTRIBUTE19 => sua_drop_rec.attribute19,
1221 X_ATTRIBUTE20 => sua_drop_rec.attribute20,
1222 X_WAITLIST_MANUAL_IND => sua_drop_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
1223 X_WLST_PRIORITY_WEIGHT_NUM => sua_drop_rec.wlst_priority_weight_num,
1224 X_WLST_PREFERENCE_WEIGHT_NUM => sua_drop_rec.wlst_preference_weight_num,
1225 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
1226 X_CORE_INDICATOR_CODE => sua_drop_rec.core_indicator_code,
1227 X_UPD_AUDIT_FLAG => sua_drop_rec.upd_audit_flag,
1228 X_SS_SOURCE_IND => sua_drop_rec.ss_source_ind
1229 );
1230 END IF;
1231 IF v_unit_cds IS NULL THEN
1232 v_unit_cds := v_sua.unit_Cd;
1233 ELSE
1234 v_unit_cds := v_unit_Cds || ',' || v_sua.unit_Cd;
1235 END IF;
1236 IF v_uoo_ids IS NULL THEN
1237 v_uoo_ids := to_char(sua_drop_rec.uoo_id);
1238 ELSE
1239 v_uoo_ids := v_uoo_ids || ',' || to_char(sua_drop_rec.uoo_id);
1240 END IF;
1241 END LOOP;
1242 END;
1243
1244 END IF;
1245 ELSE
1246 -- discontinue IGS_EN_SU_ATTEMPT
1247 -- get administrative IGS_PS_UNIT status associated
1248 -- with discontinued
1249 v_administrative_unit_status := Igs_En_Gen_008.enrp_get_uddc_aus(
1250 p_discontinued_dt,
1251 v_sua.cal_type,
1252 v_sua.ci_sequence_number,
1253 v_admin_unit_status_str,
1254 v_alias_val,
1255 v_sua.uoo_id);
1256 IF (v_administrative_unit_status IS NULL) THEN
1257 -- rollback any student_unit_attempts deleted previously
1258 ROLLBACK TO sp_discontinue_sua;
1259 p_message_name := 'IGS_EN_ONE_SUA_NOT_DISCONTINU';
1260 RETURN FALSE;
1261 END IF;
1262 -- validate discontinuation
1263 IF (Igs_En_Val_Sua.enrp_val_sua_discont(
1264 p_person_id,
1265 p_course_cd,
1266 v_sua.unit_cd,
1267 v_sua.version_number,
1268 v_sua.ci_start_dt,
1269 v_sua.enrolled_dt,
1270 v_administrative_unit_status,
1271 v_sua.unit_attempt_status,
1272 p_discontinued_dt,
1273 v_message_name ,
1274 'N' ) = FALSE) THEN
1275 -- rollback any student_unit_attempts deleted previously
1276 ROLLBACK TO sp_discontinue_sua;
1277 p_message_name := 'IGS_EN_ONE_SUA_NOTBE_DISCONT';
1278 RETURN FALSE;
1279 ELSE
1280 -- update IGS_EN_SU_ATTEMPT
1281
1282 DECLARE
1283 v_unit_cds VARCHAR2(4000);
1284 v_uoo_ids VARCHAR2(4000);
1285 CURSOR c_igs_en_su_attempt IS
1286 SELECT ROWID, IGS_EN_SU_ATTEMPT.*
1287 FROM IGS_EN_SU_ATTEMPT
1288 WHERE person_id = p_person_id AND
1289 course_cd = p_course_cd AND
1290 uoo_id = v_sua.uoo_id;
1291
1292 BEGIN
1293 v_uoo_ids := null;
1294 v_unit_cds := null;
1295
1296 FOR c_IGS_EN_SU_ATTEMPT_rec IN c_IGS_EN_SU_ATTEMPT LOOP
1297
1298 --Following validation is added as part of bug fix.2860412
1299 --Cursor will checks the context unit attempt got transferred or not.
1300 --If Unit got transferred then call the direct tbh call else
1301 --call the igs_en_sua_api.update_unit_attempt.
1302 --Reason is,igs_en_sua_api.update_unit_attempt function will
1303 --decrement available seats for the unit section.
1304 invoke_drop_workflow(
1305 p_uoo_ids => v_uoo_ids,
1306 p_unit_cds => v_unit_cds,
1307 p_teach_cal_type => v_sua.cal_type,
1308 p_teach_ci_sequence_number => v_sua.ci_sequence_number,
1309 p_person_id => p_person_id,
1310 p_course_cd => p_course_cd,
1311 p_source_of_drop => p_source,
1312 p_message_name => v_message_name
1313 );
1314 -- Call the API to update the student unit attempt. This API is a
1315 -- wrapper to the update row of the TBH.
1316 igs_en_sua_api.update_unit_attempt(
1317 X_ROWID => c_IGS_EN_SU_ATTEMPT_rec.ROWID,
1318 X_PERSON_ID => c_IGS_EN_SU_ATTEMPT_rec.PERSON_ID,
1319 X_COURSE_CD => c_IGS_EN_SU_ATTEMPT_rec.COURSE_CD ,
1320 X_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.UNIT_CD,
1321 X_CAL_TYPE => c_IGS_EN_SU_ATTEMPT_rec.CAL_TYPE,
1322 X_CI_SEQUENCE_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.CI_SEQUENCE_NUMBER ,
1323 X_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.VERSION_NUMBER ,
1324 X_LOCATION_CD => c_IGS_EN_SU_ATTEMPT_rec.LOCATION_CD,
1325 X_UNIT_CLASS => c_IGS_EN_SU_ATTEMPT_rec.UNIT_CLASS ,
1326 X_CI_START_DT => c_IGS_EN_SU_ATTEMPT_rec.CI_START_DT,
1327 X_CI_END_DT => c_IGS_EN_SU_ATTEMPT_rec.CI_END_DT,
1328 X_UOO_ID => c_IGS_EN_SU_ATTEMPT_rec.UOO_ID ,
1329 X_ENROLLED_DT => c_IGS_EN_SU_ATTEMPT_rec.ENROLLED_DT,
1330 X_UNIT_ATTEMPT_STATUS => cst_discontinue, -- c_IGS_EN_SU_ATTEMPT_rec.UNIT_ATTEMPT_STATUS,
1331 X_ADMINISTRATIVE_UNIT_STATUS => v_administrative_unit_status,
1332 X_ADMINISTRATIVE_PRIORITY => c_IGS_EN_SU_ATTEMPT_rec.administrative_PRIORITY,
1333 X_DISCONTINUED_DT => p_discontinued_dt,
1334 X_DCNT_REASON_CD => NULL, -- unable to insert value in to this field
1335 X_RULE_WAIVED_DT => c_IGS_EN_SU_ATTEMPT_rec.RULE_WAIVED_DT ,
1336 X_RULE_WAIVED_PERSON_ID => c_IGS_EN_SU_ATTEMPT_rec.RULE_WAIVED_PERSON_ID ,
1337 X_NO_ASSESSMENT_IND => c_IGS_EN_SU_ATTEMPT_rec.NO_ASSESSMENT_IND,
1338 X_SUP_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.SUP_UNIT_CD ,
1339 X_SUP_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.SUP_VERSION_NUMBER,
1340 X_EXAM_LOCATION_CD => c_IGS_EN_SU_ATTEMPT_rec.EXAM_LOCATION_CD,
1341 X_ALTERNATIVE_TITLE => c_IGS_EN_SU_ATTEMPT_rec.ALTERNATIVE_TITLE,
1342 X_OVERRIDE_ENROLLED_CP => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_ENROLLED_CP,
1343 X_OVERRIDE_EFTSU => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_EFTSU ,
1344 X_OVERRIDE_ACHIEVABLE_CP => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_ACHIEVABLE_CP,
1345 X_OVERRIDE_OUTCOME_DUE_DT => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_OUTCOME_DUE_DT,
1346 X_OVERRIDE_CREDIT_REASON => c_IGS_EN_SU_ATTEMPT_rec.OVERRIDE_CREDIT_REASON,
1347 X_WAITLIST_DT => c_IGS_EN_SU_ATTEMPT_rec.waitlist_dt,
1348 X_MODE => 'R',
1349 -- Added 5 cols as part of Enrollement Process Build -- amuthu
1350 X_GS_VERSION_NUMBER => c_IGS_EN_SU_ATTEMPT_rec.gs_version_number,
1351 X_ENR_METHOD_TYPE => c_IGS_EN_SU_ATTEMPT_rec.enr_method_type,
1352 X_FAILED_UNIT_RULE => c_IGS_EN_SU_ATTEMPT_rec.FAILED_UNIT_RULE,
1353 X_CART => c_IGS_EN_SU_ATTEMPT_rec.cart,
1354 X_RSV_SEAT_EXT_ID => c_IGS_EN_SU_ATTEMPT_rec.RSV_SEAT_EXT_ID,
1355 X_ORG_UNIT_CD => c_IGS_EN_SU_ATTEMPT_rec.ORG_UNIT_CD,
1356 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
1357 X_SESSION_ID => c_IGS_EN_SU_ATTEMPT_rec.SESSION_ID,
1358 -- Added the column grading schema as a part of the bug 2037897. - aiyer
1359 X_GRADING_SCHEMA_CODE => c_IGS_EN_SU_ATTEMPT_rec.grading_schema_code,
1360 X_DEG_AUD_DETAIL_ID => c_IGS_EN_SU_ATTEMPT_rec.deg_aud_detail_id,
1361 X_SUBTITLE => c_IGS_EN_SU_ATTEMPT_rec.subtitle,
1362 X_STUDENT_CAREER_TRANSCRIPT => c_IGS_EN_SU_ATTEMPT_rec.student_career_transcript,
1363 X_STUDENT_CAREER_STATISTICS => c_IGS_EN_SU_ATTEMPT_rec.student_career_statistics,
1364 X_ATTRIBUTE_CATEGORY => c_IGS_EN_SU_ATTEMPT_rec.attribute_category,
1365 X_ATTRIBUTE1 => c_IGS_EN_SU_ATTEMPT_rec.attribute1,
1366 X_ATTRIBUTE2 => c_IGS_EN_SU_ATTEMPT_rec.attribute2,
1367 X_ATTRIBUTE3 => c_IGS_EN_SU_ATTEMPT_rec.attribute3,
1368 X_ATTRIBUTE4 => c_IGS_EN_SU_ATTEMPT_rec.attribute4,
1369 X_ATTRIBUTE5 => c_IGS_EN_SU_ATTEMPT_rec.attribute5,
1370 X_ATTRIBUTE6 => c_IGS_EN_SU_ATTEMPT_rec.attribute6,
1371 X_ATTRIBUTE7 => c_IGS_EN_SU_ATTEMPT_rec.attribute7,
1372 X_ATTRIBUTE8 => c_IGS_EN_SU_ATTEMPT_rec.attribute8,
1373 X_ATTRIBUTE9 => c_IGS_EN_SU_ATTEMPT_rec.attribute9,
1374 X_ATTRIBUTE10 => c_IGS_EN_SU_ATTEMPT_rec.attribute10,
1375 X_ATTRIBUTE11 => c_IGS_EN_SU_ATTEMPT_rec.attribute11,
1376 X_ATTRIBUTE12 => c_IGS_EN_SU_ATTEMPT_rec.attribute12,
1377 X_ATTRIBUTE13 => c_IGS_EN_SU_ATTEMPT_rec.attribute13,
1378 X_ATTRIBUTE14 => c_IGS_EN_SU_ATTEMPT_rec.attribute14,
1379 X_ATTRIBUTE15 => c_IGS_EN_SU_ATTEMPT_rec.attribute15,
1380 X_ATTRIBUTE16 => c_IGS_EN_SU_ATTEMPT_rec.attribute16,
1381 X_ATTRIBUTE17 => c_IGS_EN_SU_ATTEMPT_rec.attribute17,
1382 X_ATTRIBUTE18 => c_IGS_EN_SU_ATTEMPT_rec.attribute18,
1383 X_ATTRIBUTE19 => c_IGS_EN_SU_ATTEMPT_rec.attribute19,
1384 X_ATTRIBUTE20 => c_IGS_EN_SU_ATTEMPT_rec.attribute20,
1385 X_WAITLIST_MANUAL_IND => c_IGS_EN_SU_ATTEMPT_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
1386 X_WLST_PRIORITY_WEIGHT_NUM => c_IGS_EN_SU_ATTEMPT_rec.wlst_priority_weight_num,
1387 X_WLST_PREFERENCE_WEIGHT_NUM => c_IGS_EN_SU_ATTEMPT_rec.wlst_preference_weight_num,
1388 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
1389 X_CORE_INDICATOR_CODE => c_IGS_EN_SU_ATTEMPT_rec.core_indicator_code
1390 );
1391
1392 IF v_unit_cds IS NULL THEN
1393 v_unit_cds := v_sua.unit_cd;
1394 ELSE
1395 v_unit_cds := v_unit_Cds || ',' || v_sua.unit_Cd;
1396 END IF;
1397 IF v_uoo_ids IS NULL THEN
1398 v_uoo_ids := to_char(c_IGS_EN_SU_ATTEMPT_rec.uoo_id);
1399 ELSE
1400 v_uoo_ids := v_uoo_ids || ',' || to_char(c_IGS_EN_SU_ATTEMPT_rec.uoo_id);
1401 END IF;
1402 END LOOP;
1403 END;
1404 END IF;
1405 END IF;
1406 END LOOP;
1407 -- Suspend the current session to overcome problem of multiple updates
1408 -- affecting the SCA history table with primary key conflicts.
1409 DBMS_LOCK.SLEEP (1);
1410 -- discontinue IGS_EN_STDNT_PS_ATT
1411 -- validate discontinuation
1412 IF (Igs_En_Val_Sca.enrp_val_sca_discont(
1413 p_person_id,
1414 p_course_cd,
1415 p_version_number,
1416 p_course_attempt_status,
1417 p_discontinuation_reason_cd,
1418 p_discontinued_dt,
1419 p_commencement_dt,
1420 v_message_name,
1421 'N') = FALSE) THEN
1422 p_message_name := v_message_name;
1423 RETURN FALSE;
1424 ELSE
1425 -- Check if the IGS_PS_COURSE attempt status has been changed by the IGS_PS_UNIT processing.
1426 -- If so, check that it is still valid to be discontinued.
1427 OPEN c_sca_check (
1428 p_person_id,
1429 p_course_cd);
1430 FETCH c_sca_check INTO v_sca_status,l_primary_prg_type;
1431 IF (c_sca_check%FOUND) THEN
1432 CLOSE c_sca_check;
1433 IF v_sca_status IN
1434 (cst_enrolled, cst_lapsed, cst_inactive, cst_intermit) OR
1435 (v_sca_status= cst_discontinue AND l_primary_prg_type='PRIMARY' AND p_source = 'PROGRAM_TRANSFER' )THEN
1436 -- discontinue IGS_EN_STDNT_PS_ATT
1437
1438 DECLARE
1439 CURSOR c_si_en_stnd_ps_att IS
1440 SELECT ROWID, IGS_EN_STDNT_PS_ATT.*
1441 FROM IGS_EN_STDNT_PS_ATT
1442 WHERE person_id = p_person_id AND
1443 course_cd = p_course_cd;
1444 BEGIN
1445
1446 FOR c_si_en_stnd_ps_att_rec IN c_si_en_stnd_ps_att LOOP
1447 -- If program discontinuing from the Program transfer form then changing the key_program value to 'N', The FROM program
1448 -- discontinueing and key program value changing to N. pmarada bug 2384025
1449 IF p_transf_course_cd IS NOT NULL AND p_source = 'PROGRAM_TRANSFER' THEN
1450
1451 c_si_en_stnd_ps_att_rec.key_program := 'N';
1452 OPEN c_prgs_in_career;
1453 FETCH c_prgs_in_career INTO v_dummy;
1454 IF c_prgs_in_career%FOUND THEN
1455 c_si_en_stnd_ps_att_rec.primary_program_type := 'SECONDARY';
1456 END IF;
1457 CLOSE c_prgs_in_career;
1458
1459 END IF;
1460
1461 Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
1462 X_ROWID => c_si_en_stnd_ps_att_rec.ROWID,
1463 X_PERSON_ID => c_si_en_stnd_ps_att_rec.PERSON_ID,
1464 X_COURSE_CD => c_si_en_stnd_ps_att_rec.COURSE_CD,
1465 X_ADVANCED_STANDING_IND => c_si_en_stnd_ps_att_rec.ADVANCED_STANDING_IND,
1466 X_FEE_CAT => c_si_en_stnd_ps_att_rec.FEE_CAT,
1467 X_CORRESPONDENCE_CAT => c_si_en_stnd_ps_att_rec.CORRESPONDENCE_CAT,
1468 X_SELF_HELP_GROUP_IND => c_si_en_stnd_ps_att_rec.SELF_HELP_GROUP_IND,
1469 X_LOGICAL_DELETE_DT => c_si_en_stnd_ps_att_rec.LOGICAL_DELETE_DT,
1470 X_ADM_ADMISSION_APPL_NUMBER => c_si_en_stnd_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
1471 X_ADM_NOMINATED_COURSE_CD => c_si_en_stnd_ps_att_rec.ADM_NOMINATED_COURSE_CD,
1472 X_ADM_SEQUENCE_NUMBER => c_si_en_stnd_ps_att_rec.ADM_SEQUENCE_NUMBER,
1473 X_VERSION_NUMBER => c_si_en_stnd_ps_att_rec.version_number,
1474 X_CAL_TYPE => c_si_en_stnd_ps_att_rec.cal_type,
1475 X_LOCATION_CD => c_si_en_stnd_ps_att_rec.location_cd,
1476 X_ATTENDANCE_MODE => c_si_en_stnd_ps_att_rec.attendance_mode,
1477 X_ATTENDANCE_TYPE => c_si_en_stnd_ps_att_rec.attendance_type,
1478 X_COO_ID => c_si_en_stnd_ps_att_rec.coo_id,
1479 X_STUDENT_CONFIRMED_IND => c_si_en_stnd_ps_att_rec.STUDENT_CONFIRMED_IND,
1480 X_COMMENCEMENT_DT => c_si_en_stnd_ps_att_rec.COMMENCEMENT_DT,
1481 X_COURSE_ATTEMPT_STATUS => cst_discontinue, --c_si_en_stnd_ps_att_rec.COURSE_ATTEMPT_STATUS,
1482 X_PROGRESSION_STATUS => c_si_en_stnd_ps_att_rec.PROGRESSION_STATUS,
1483 X_DERIVED_ATT_TYPE => c_si_en_stnd_ps_att_rec.DERIVED_ATT_TYPE,
1484 X_DERIVED_ATT_MODE => c_si_en_stnd_ps_att_rec.DERIVED_ATT_MODE,
1485 X_PROVISIONAL_IND => c_si_en_stnd_ps_att_rec.PROVISIONAL_IND,
1486 X_DISCONTINUED_DT => p_discontinued_dt,
1487 X_DISCONTINUATION_REASON_CD => p_discontinuation_reason_cd,
1488 X_LAPSED_DT => c_si_en_stnd_ps_att_rec.LAPSED_DT,
1489 X_FUNDING_SOURCE => c_si_en_stnd_ps_att_rec.FUNDING_SOURCE,
1490 X_EXAM_LOCATION_CD => c_si_en_stnd_ps_att_rec.EXAM_LOCATION_CD,
1491 X_DERIVED_COMPLETION_YR => c_si_en_stnd_ps_att_rec.DERIVED_COMPLETION_YR,
1492 X_DERIVED_COMPLETION_PERD => c_si_en_stnd_ps_att_rec.DERIVED_COMPLETION_PERD,
1493 X_NOMINATED_COMPLETION_YR => c_si_en_stnd_ps_att_rec.NOMINATED_COMPLETION_YR,
1494 X_NOMINATED_COMPLETION_PERD => c_si_en_stnd_ps_att_rec.NOMINATED_COMPLETION_PERD,
1495 X_RULE_CHECK_IND => c_si_en_stnd_ps_att_rec.RULE_CHECK_IND,
1496 X_WAIVE_OPTION_CHECK_IND => c_si_en_stnd_ps_att_rec.WAIVE_OPTION_CHECK_IND,
1497 X_LAST_RULE_CHECK_DT => c_si_en_stnd_ps_att_rec.LAST_RULE_CHECK_DT,
1498 X_PUBLISH_OUTCOMES_IND => c_si_en_stnd_ps_att_rec.PUBLISH_OUTCOMES_IND,
1499 X_COURSE_RQRMNT_COMPLETE_IND => c_si_en_stnd_ps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
1500 X_COURSE_RQRMNTS_COMPLETE_DT => c_si_en_stnd_ps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
1501 X_S_COMPLETED_SOURCE_TYPE => c_si_en_stnd_ps_att_rec.S_COMPLETED_SOURCE_TYPE,
1502 X_OVERRIDE_TIME_LIMITATION => c_si_en_stnd_ps_att_rec.OVERRIDE_TIME_LIMITATION,
1503 X_MODE => 'R',
1504 X_LAST_DATE_OF_ATTENDANCE => c_si_en_stnd_ps_att_rec.LAST_DATE_OF_ATTENDANCE,
1505 X_DROPPED_BY => c_si_en_stnd_ps_att_rec.DROPPED_BY,
1506 X_IGS_PR_CLASS_STD_ID => c_si_en_stnd_ps_att_rec.IGS_PR_CLASS_STD_ID,
1507 -- Added next four parameters as per the Career Impact Build Bug# 2027984
1508 x_primary_program_type => c_si_en_stnd_ps_att_rec.primary_program_type,
1509 x_primary_prog_type_source => c_si_en_stnd_ps_att_rec.primary_prog_type_source,
1510 x_catalog_cal_type => c_si_en_stnd_ps_att_rec.catalog_cal_type,
1511 x_catalog_seq_num => c_si_en_stnd_ps_att_rec.catalog_seq_num,
1512 x_key_program => c_si_en_stnd_ps_att_rec.key_program,
1513 -- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
1514 x_override_cmpl_dt => c_si_en_stnd_ps_att_rec.override_cmpl_dt,
1515 x_manual_ovr_cmpl_dt_ind => c_si_en_stnd_ps_att_rec.manual_ovr_cmpl_dt_ind,
1516 -- added by ckasu as a part of bug # 3544927
1517 X_ATTRIBUTE_CATEGORY => c_si_en_stnd_ps_att_rec.ATTRIBUTE_CATEGORY,
1518 X_ATTRIBUTE1 => c_si_en_stnd_ps_att_rec.ATTRIBUTE1,
1519 X_ATTRIBUTE2 => c_si_en_stnd_ps_att_rec.ATTRIBUTE2,
1520 X_ATTRIBUTE3 => c_si_en_stnd_ps_att_rec.ATTRIBUTE3,
1521 X_ATTRIBUTE4 => c_si_en_stnd_ps_att_rec.ATTRIBUTE4,
1522 X_ATTRIBUTE5 => c_si_en_stnd_ps_att_rec.ATTRIBUTE5,
1523 X_ATTRIBUTE6 => c_si_en_stnd_ps_att_rec.ATTRIBUTE6,
1524 X_ATTRIBUTE7 => c_si_en_stnd_ps_att_rec.ATTRIBUTE7,
1525 X_ATTRIBUTE8 => c_si_en_stnd_ps_att_rec.ATTRIBUTE8,
1526 X_ATTRIBUTE9 => c_si_en_stnd_ps_att_rec.ATTRIBUTE9,
1527 X_ATTRIBUTE10 => c_si_en_stnd_ps_att_rec.ATTRIBUTE10,
1528 X_ATTRIBUTE11 => c_si_en_stnd_ps_att_rec.ATTRIBUTE11,
1529 X_ATTRIBUTE12 => c_si_en_stnd_ps_att_rec.ATTRIBUTE12,
1530 X_ATTRIBUTE13 => c_si_en_stnd_ps_att_rec.ATTRIBUTE13,
1531 X_ATTRIBUTE14 => c_si_en_stnd_ps_att_rec.ATTRIBUTE14,
1532 X_ATTRIBUTE15 => c_si_en_stnd_ps_att_rec.ATTRIBUTE15,
1533 X_ATTRIBUTE16 => c_si_en_stnd_ps_att_rec.ATTRIBUTE16,
1534 X_ATTRIBUTE17 => c_si_en_stnd_ps_att_rec.ATTRIBUTE17,
1535 X_ATTRIBUTE18 => c_si_en_stnd_ps_att_rec.ATTRIBUTE18,
1536 X_ATTRIBUTE19 => c_si_en_stnd_ps_att_rec.ATTRIBUTE19,
1537 X_ATTRIBUTE20 => c_si_en_stnd_ps_att_rec.ATTRIBUTE20,
1538 X_FUTURE_DATED_TRANS_FLAG => c_si_en_stnd_ps_att_rec.FUTURE_DATED_TRANS_FLAG
1539 );
1540 END LOOP;
1541 END;
1542
1543 END IF;
1544 ELSE
1545 CLOSE c_sca_check;
1546 END IF;
1547 END IF;
1548 -- return the default message
1549 p_message_name := NULL;
1550 RETURN TRUE;
1551
1552 EXCEPTION
1553 -- added for bug 3526251
1554 WHEN NO_AUSL_RECORD_FOUND THEN
1555 ROLLBACK TO sp_discontinue_sua;
1556 RAISE;
1557 WHEN OTHERS THEN
1558 IF SQLCODE = -54 THEN
1559 -- rollback any student_unit_attempts updated
1560 ROLLBACK TO sp_discontinue_sua;
1561 RETURN FALSE;
1562 ELSE
1563 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1564 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrp_upd_sca_discont');
1565 Igs_Ge_Msg_Stack.ADD;
1566 App_Exception.Raise_Exception;
1567 END IF;
1568 END;
1569 END enrp_upd_sca_discont;
1570
1571
1572 PROCEDURE Enrp_Upd_Sca_Lapse(
1573 p_acad_cal_type IN VARCHAR2 ,
1574 p_acad_sequence_number IN NUMBER ,
1575 p_org_unit_cd IN VARCHAR2 ,
1576 p_course_cd IN VARCHAR2 ,
1577 p_enrolment_cat IN VARCHAR2 ,
1578 p_grace_days IN NUMBER ,
1579 p_log_creation_dt OUT NOCOPY DATE ,
1580 p_trm_or_tch_cal_type IN VARCHAR2 ,
1581 p_trm_or_tch_seq_number IN NUMBER )
1582 -- add the parameters p_trm_or_tch_cal_type,p_trm_or_tch_seq_number, pmarada
1583 AS
1584 gv_other_detail VARCHAR2(255);
1585 BEGIN
1586 /****************************************************************************
1587 History
1588 Who When Why
1589 sarakshi 19-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
1590 ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
1591 call as a part of bug 3544927.
1592
1593
1594 *****************************************************************************/
1595 -- enrp_upd_sca_lapse
1596 -- Update students who's enrolment has lapsed and set the lapse date in
1597 -- their IGS_PS_COURSE attempt, which in turn will set their IGS_PS_COURSE attempt status
1598 -- to lapsed.
1599 -- IGS_GE_NOTE: This job is run from the report ENRR05E0 which handles the
1600 -- parameter processing and running the report subsequent to this job
1601 -- committing the log entry records.
1602
1603 DECLARE
1604 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
1605 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
1606 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1607 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1608 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1609 cst_sca_lapse CONSTANT VARCHAR2(10) := 'SCA-LAPSE';
1610 cst_future CONSTANT VARCHAR2(10) := 'FUTURE';
1611 cst_no_future CONSTANT VARCHAR2(10) := 'NO-FUTURE';
1612 e_record_locked EXCEPTION;
1613 PRAGMA EXCEPTION_INIT(e_record_locked, -54);
1614 v_lapse_dt_alias IGS_EN_CAL_CONF.lapse_dt_alias%TYPE ;
1615 v_log_creation_dt IGS_GE_S_LOG.creation_dt%TYPE ;
1616 v_next_lapse_dt IGS_CA_DA_INST_V.alias_val%TYPE ;
1617 v_next_lapse_dt_found BOOLEAN := FALSE;
1618 v_enr_form_due_dt DATE ;
1619 v_lapsed BOOLEAN := FALSE;
1620 v_dummy VARCHAR2(1);
1621 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
1622 v_last_teaching VARCHAR2(30);
1623 CURSOR c_secc IS
1624 SELECT secc.lapse_dt_alias
1625 FROM IGS_EN_CAL_CONF secc
1626 WHERE secc.s_control_num = 1;
1627 CURSOR c_ci IS
1628 SELECT ci.start_dt,
1629 ci.end_dt
1630 FROM IGS_CA_INST ci
1631 WHERE ci.cal_type = p_acad_cal_type AND
1632 ci.sequence_number = p_acad_sequence_number;
1633 v_ci_rec c_ci%ROWTYPE;
1634 CURSOR c_daiv (p_cal_type VARCHAR2, p_seq_num NUMBER)IS
1635 SELECT daiv.absolute_val alias_val
1636 FROM IGS_CA_DA_INST_V daiv
1637 WHERE daiv.cal_type = p_cal_type AND
1638 daiv.ci_sequence_number = p_seq_num AND
1639 daiv.dt_alias = v_lapse_dt_alias
1640 ORDER BY daiv.absolute_val; -- first row found is the earliest date
1641 --modified for perf bug 3699543 : sqlid 14792129
1642 CURSOR c_sca_crv IS
1643 SELECT sca.person_id,
1644 sca.course_cd
1645 FROM IGS_EN_STDNT_PS_ATT sca,
1646 IGS_PS_VER crv
1647 WHERE sca.cal_type = p_acad_cal_type AND
1648 sca.course_cd LIKE p_course_cd AND
1649 sca.course_attempt_status = cst_inactive AND
1650 crv.course_cd = sca.course_cd AND
1651 crv.version_number = sca.version_number AND
1652 (crv.responsible_org_unit_cd LIKE p_org_unit_cd OR
1653 EXISTS (
1654 SELECT 'X'
1655 FROM IGS_OR_INST_ORG_BASE_V ou,
1656 IGS_OR_STATUS os
1657 WHERE ou.PARTY_NUMBER LIKE p_org_unit_cd AND
1658 os.org_status = ou.org_status AND
1659 os.s_org_status = cst_active AND
1660 Igs_Or_Gen_001.orgp_get_within_ou(ou.PARTY_NUMBER,
1661 ou.start_dt,
1662 crv.responsible_org_unit_cd,
1663 crv.responsible_ou_start_dt,
1664 'N')
1665 = 'Y')) AND
1666 (p_enrolment_cat = '%' OR
1667 EXISTS (
1668 SELECT 'X'
1669 FROM IGS_AS_SC_ATMPT_ENR scae
1670 WHERE scae.person_id = sca.person_id AND
1671 scae.course_cd = sca.course_cd AND
1672 Igs_En_Gen_014.enrs_get_within_ci(p_acad_cal_type,
1673 p_acad_sequence_number,
1674 scae.cal_type,
1675 scae.ci_sequence_number,
1676 'Y')
1677 = 'Y' AND
1678 scae.enrolment_cat LIKE p_enrolment_cat));
1679 CURSOR c_scae_ci (cp_person_id IGS_AS_SC_ATMPT_ENR.person_id%TYPE,
1680 cp_course_cd IGS_AS_SC_ATMPT_ENR.course_cd%TYPE) IS
1681 SELECT scae.person_id,
1682 scae.course_cd,
1683 scae.cal_type,
1684 scae.ci_sequence_number,
1685 scae.enr_form_due_dt
1686 FROM IGS_AS_SC_ATMPT_ENR scae,
1687 IGS_CA_INST ci
1688 WHERE scae.person_id = cp_person_id AND
1689 scae.course_cd = cp_course_cd AND
1690 ci.cal_type = scae.cal_type AND
1691 ci.sequence_number = scae.ci_sequence_number AND
1692 ci.end_dt > SYSDATE
1693 ORDER BY ci.start_dt DESC; -- for latest date
1694 CURSOR c_sua (
1695 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1696 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
1697 SELECT ci.alternate_code,
1698 sua.cal_type,
1699 sua.ci_sequence_number
1700 FROM IGS_EN_SU_ATTEMPT sua,
1701 IGS_CA_INST ci
1702 WHERE sua.person_id = cp_person_id AND
1703 sua.course_cd = cp_course_cd AND
1704 sua.unit_attempt_status IN (cst_enrolled,cst_discontin,cst_completed) AND
1705 sua.ci_start_dt <= TRUNC(SYSDATE) AND
1706 ci.cal_type = sua.cal_type AND
1707 ci.sequence_number = sua.ci_sequence_number
1708 ORDER BY ci_start_dt DESC;
1709 v_sua_rec c_sua%ROWTYPE;
1710 v_scae_ci_rec c_scae_ci%ROWTYPE;
1711 CURSOR c_sca (
1712 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1713 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE) IS
1714 SELECT ROWID,sca.*
1715 FROM IGS_EN_STDNT_PS_ATT sca
1716 WHERE sca.person_id = cp_person_id AND
1717 sca.course_cd = cp_course_cd
1718 FOR UPDATE NOWAIT;
1719 v_sca_exists c_sca%ROWTYPE;
1720
1721 CURSOR c_teachLoad(cp_cal_type VARCHAR2, cp_ci_sequence_number NUMBER) IS
1722 SELECT start_dt, end_dt FROM IGS_CA_INST_ALL
1723 WHERE cal_type = cp_cal_type AND
1724 sequence_number = cp_ci_sequence_number;
1725 v_teachload_rec c_teachload%ROWTYPE;
1726 v_trm_or_tch_start_date DATE;
1727 v_trm_or_tch_end_date DATE;
1728
1729 BEGIN
1730 -- Load the calendar configuration details which point to lapse date aliases.
1731 -- If no configuration details exist, or the lapse date alias is null then
1732 -- raise an exception. The process cannot continue.
1733 OPEN c_secc;
1734 FETCH c_secc INTO v_lapse_dt_alias;
1735 IF (c_secc%NOTFOUND) THEN
1736 CLOSE c_secc;
1737
1738 Fnd_Message.Set_Name('IGS', 'IGS_EN_NO_CAL_CONFIG_DTL_XS');
1739 Igs_Ge_Msg_Stack.ADD;
1740 App_Exception.Raise_Exception;
1741
1742 END IF;
1743 CLOSE c_secc;
1744 IF v_lapse_dt_alias IS NULL THEN
1745 Fnd_Message.Set_Name('IGS', 'IGS_EN_LAPSE_DT_ALIAS_NOT_SET');
1746 Igs_Ge_Msg_Stack.ADD;
1747 App_Exception.Raise_Exception;
1748 END IF;
1749 -- Create a system log to hold details of students who have lapsed.
1750 OPEN c_ci;
1751 FETCH c_ci INTO v_ci_rec;
1752 CLOSE c_ci;
1753
1754 OPEN c_teachLoad( p_trm_or_tch_cal_type, p_trm_or_tch_seq_number);
1755 FETCH c_teachLoad INTO v_teachload_rec ;
1756 IF c_teachLoad%FOUND THEN
1757 v_trm_or_tch_start_date := v_teachload_rec.start_dt;
1758 v_trm_or_tch_end_date := v_teachload_rec.end_dt;
1759 END IF;
1760 CLOSE c_teachload;
1761
1762 Igs_Ge_Gen_003.genp_ins_log(cst_sca_lapse,
1763 p_acad_cal_type || ',' ||
1764 TO_CHAR(p_acad_sequence_number) || ',' ||
1765 TO_CHAR(v_ci_rec.start_dt,'DD/MM/YYYY') || ',' ||
1766 TO_CHAR(v_ci_rec.end_dt,'DD/MM/YYYY') || ',' ||
1767 p_org_unit_cd || ',' ||
1768 p_course_cd || ',' ||
1769 p_enrolment_cat || ',' ||
1770 TO_CHAR(p_grace_days) || ',' ||
1771 p_trm_or_tch_cal_type || ',' ||
1772 TO_CHAR(p_trm_or_tch_seq_number) || ',' ||
1773 TO_CHAR(v_trm_or_tch_start_date, 'DD/MM/YYYY') || ',' ||
1774 TO_CHAR(v_trm_or_tch_end_date ,'DD/MM/YYYY') ,
1775 v_log_creation_dt);
1776 -- passing p_trm_or_tch_cal_type,p_trm_or_seq_number,P_trm_or_tch_start_date,p_trm_or_tch_end_date parameters.
1777 -- bug no 1830175 pmarada
1778
1779 p_log_creation_dt := v_log_creation_dt;
1780 -- Select the next relevant lapse date in the Term period.
1781 -- bugno 1830175 pmarada
1782 FOR v_alias_val_rec IN c_daiv (p_trm_or_tch_cal_type, p_trm_or_tch_seq_number) LOOP
1783 v_next_lapse_dt_found := TRUE;
1784 IF v_alias_val_rec.alias_val >= SYSDATE THEN
1785 v_next_lapse_dt := v_alias_val_rec.alias_val;
1786 EXIT;
1787 END IF;
1788 END LOOP;
1789 -- Select the next relevant lapse date in the academic period.
1790 IF NOT v_next_lapse_dt_found THEN
1791 FOR v_alias_val_rec IN c_daiv (p_acad_cal_type, p_acad_sequence_number) LOOP
1792 v_next_lapse_dt_found := TRUE;
1793 IF v_alias_val_rec.alias_val >= SYSDATE THEN
1794 v_next_lapse_dt := v_alias_val_rec.alias_val;
1795 EXIT;
1796 END IF;
1797 END LOOP;
1798 END IF;
1799
1800 IF NOT v_next_lapse_dt_found THEN
1801 -- c_daiv record not found
1802 v_next_lapse_dt := SYSDATE;
1803 END IF;
1804 -- If there is no future date (or no date at all - which means always lapse)
1805 -- then exit the routine - nobody has to be lapsed
1806 IF v_next_lapse_dt IS NULL THEN
1807 RETURN;
1808 END IF;
1809
1810 -- Select students who are currently inactive and match the parameters
1811 FOR v_sca_crv_rec IN c_sca_crv LOOP
1812 -- Determine if the student has been pre-enrolled for a future
1813 -- enrollment period and has not yet passed the enrolment form
1814 -- due date
1815 v_lapsed := TRUE;
1816 OPEN c_scae_ci(
1817 v_sca_crv_rec.person_id,
1818 v_sca_crv_rec.course_cd);
1819 FETCH c_scae_ci INTO v_scae_ci_rec;
1820 IF (c_scae_ci%FOUND) THEN
1821 -- Call routine to determine the enrolment form due date for
1822 -- the scae record
1823 v_enr_form_due_dt := Igs_En_Gen_004.enrp_get_scae_due(v_scae_ci_rec.person_id,
1824 v_scae_ci_rec.course_cd,
1825 v_scae_ci_rec.cal_type,
1826 v_scae_ci_rec.ci_sequence_number,
1827 'Y',
1828 v_scae_ci_rec.enr_form_due_dt);
1829 IF v_enr_form_due_dt IS NOT NULL THEN
1830
1831 IF v_enr_form_due_dt + p_grace_days > SYSDATE THEN
1832 v_lapsed := FALSE;
1833 END IF;
1834 END IF;
1835
1836 END IF;
1837 CLOSE c_scae_ci;
1838 IF v_lapsed THEN
1839 BEGIN
1840 -- Anybody who has reached this point in the code is to be lapsed
1841 -- as they are not enrolled for a future period, and there is a
1842 -- lapse date >= the SYSDATE value.
1843 OPEN c_sca(
1844 v_sca_crv_rec.person_id,
1845 v_sca_crv_rec.course_cd);
1846 FETCH c_sca INTO v_sca_exists;
1847 IF (c_sca%FOUND) THEN
1848 Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
1849 X_ROWID => v_sca_exists.ROWID,
1850 X_PERSON_ID => v_sca_exists.PERSON_ID,
1851 X_COURSE_CD => v_sca_exists.COURSE_CD,
1852 X_ADVANCED_STANDING_IND => v_sca_exists.ADVANCED_STANDING_IND,
1853 X_FEE_CAT => v_sca_exists.FEE_CAT,
1854 X_CORRESPONDENCE_CAT => v_sca_exists.CORRESPONDENCE_CAT,
1855 X_SELF_HELP_GROUP_IND => v_sca_exists.SELF_HELP_GROUP_IND,
1856 X_LOGICAL_DELETE_DT => v_sca_exists.LOGICAL_DELETE_DT,
1857 X_ADM_ADMISSION_APPL_NUMBER => v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
1858 X_ADM_NOMINATED_COURSE_CD => v_sca_exists.ADM_NOMINATED_COURSE_CD,
1859 X_ADM_SEQUENCE_NUMBER => v_sca_exists.ADM_SEQUENCE_NUMBER,
1860 X_VERSION_NUMBER => v_sca_exists.version_number,
1861 X_CAL_TYPE => v_sca_exists.cal_type,
1862 X_LOCATION_CD => v_sca_exists.location_cd,
1863 X_ATTENDANCE_MODE => v_sca_exists.attendance_mode,
1864 X_ATTENDANCE_TYPE => v_sca_exists.attendance_type,
1865 X_COO_ID => v_sca_exists.coo_id,
1866 X_STUDENT_CONFIRMED_IND => v_sca_exists.STUDENT_CONFIRMED_IND,
1867 X_COMMENCEMENT_DT => v_sca_exists.COMMENCEMENT_DT,
1868 X_COURSE_ATTEMPT_STATUS =>v_sca_exists.COURSE_ATTEMPT_STATUS,
1869 X_PROGRESSION_STATUS => v_sca_exists.PROGRESSION_STATUS,
1870 X_DERIVED_ATT_TYPE => v_sca_exists.DERIVED_ATT_TYPE,
1871 X_DERIVED_ATT_MODE => v_sca_exists.DERIVED_ATT_MODE,
1872 X_PROVISIONAL_IND => v_sca_exists.PROVISIONAL_IND,
1873 X_DISCONTINUED_DT => v_sca_exists.discontinued_dt,
1874 X_DISCONTINUATION_REASON_CD => v_sca_exists.discontinuation_reason_cd,
1875 X_LAPSED_DT => TRUNC(SYSDATE),
1876 X_FUNDING_SOURCE => v_sca_exists.FUNDING_SOURCE,
1877 X_EXAM_LOCATION_CD => v_sca_exists.EXAM_LOCATION_CD,
1878 X_DERIVED_COMPLETION_YR => v_sca_exists.DERIVED_COMPLETION_YR,
1879 X_DERIVED_COMPLETION_PERD => v_sca_exists.DERIVED_COMPLETION_PERD,
1880 X_NOMINATED_COMPLETION_YR => v_sca_exists.NOMINATED_COMPLETION_YR,
1881 X_NOMINATED_COMPLETION_PERD => v_sca_exists.NOMINATED_COMPLETION_PERD,
1882 X_RULE_CHECK_IND => v_sca_exists.RULE_CHECK_IND,
1883 X_WAIVE_OPTION_CHECK_IND => v_sca_exists.WAIVE_OPTION_CHECK_IND,
1884 X_LAST_RULE_CHECK_DT => v_sca_exists.LAST_RULE_CHECK_DT,
1885 X_PUBLISH_OUTCOMES_IND => v_sca_exists.PUBLISH_OUTCOMES_IND,
1886 X_COURSE_RQRMNT_COMPLETE_IND => v_sca_exists.COURSE_RQRMNT_COMPLETE_IND,
1887 X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_exists.COURSE_RQRMNTS_COMPLETE_DT,
1888 X_S_COMPLETED_SOURCE_TYPE => v_sca_exists.S_COMPLETED_SOURCE_TYPE,
1889 X_OVERRIDE_TIME_LIMITATION => v_sca_exists.OVERRIDE_TIME_LIMITATION,
1890 X_MODE => 'R',
1891 X_LAST_DATE_OF_ATTENDANCE => v_sca_exists.LAST_DATE_OF_ATTENDANCE,
1892 X_DROPPED_BY => v_sca_exists.DROPPED_BY,
1893 X_IGS_PR_CLASS_STD_ID => v_sca_exists.IGS_PR_CLASS_STD_ID,
1894 -- Added next four parameters as per the Career Impact Build Bug# 2027984
1895 x_primary_program_type => v_sca_exists.primary_program_type,
1896 x_primary_prog_type_source => v_sca_exists.primary_prog_type_source,
1897 x_catalog_cal_type => v_sca_exists.catalog_cal_type,
1898 x_catalog_seq_num => v_sca_exists.catalog_seq_num,
1899 x_key_program => v_sca_exists.key_program,
1900 -- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
1901 x_override_cmpl_dt => v_sca_exists.override_cmpl_dt,
1902 x_manual_ovr_cmpl_dt_ind => v_sca_exists.manual_ovr_cmpl_dt_ind,
1903 -- added by ckasu as part of bug # 3544927
1904 X_ATTRIBUTE_CATEGORY => v_sca_exists.ATTRIBUTE_CATEGORY,
1905 X_ATTRIBUTE1 => v_sca_exists.ATTRIBUTE1,
1906 X_ATTRIBUTE2 => v_sca_exists.ATTRIBUTE2,
1907 X_ATTRIBUTE3 => v_sca_exists.ATTRIBUTE3,
1908 X_ATTRIBUTE4 => v_sca_exists.ATTRIBUTE4,
1909 X_ATTRIBUTE5 => v_sca_exists.ATTRIBUTE5,
1910 X_ATTRIBUTE6 => v_sca_exists.ATTRIBUTE6,
1911 X_ATTRIBUTE7 => v_sca_exists.ATTRIBUTE7,
1912 X_ATTRIBUTE8 => v_sca_exists.ATTRIBUTE8,
1913 X_ATTRIBUTE9 => v_sca_exists.ATTRIBUTE9,
1914 X_ATTRIBUTE10 => v_sca_exists.ATTRIBUTE10,
1915 X_ATTRIBUTE11 => v_sca_exists.ATTRIBUTE11,
1916 X_ATTRIBUTE12 => v_sca_exists.ATTRIBUTE12,
1917 X_ATTRIBUTE13 => v_sca_exists.ATTRIBUTE13,
1918 X_ATTRIBUTE14 => v_sca_exists.ATTRIBUTE14,
1919 X_ATTRIBUTE15 => v_sca_exists.ATTRIBUTE15,
1920 X_ATTRIBUTE16 => v_sca_exists.ATTRIBUTE16,
1921 X_ATTRIBUTE17 => v_sca_exists.ATTRIBUTE17,
1922 X_ATTRIBUTE18 => v_sca_exists.ATTRIBUTE18,
1923 X_ATTRIBUTE19 => v_sca_exists.ATTRIBUTE19,
1924 X_ATTRIBUTE20 => v_sca_exists.ATTRIBUTE20,
1925 X_FUTURE_DATED_TRANS_FLAG => v_sca_exists.FUTURE_DATED_TRANS_FLAG
1926 );
1927
1928
1929 DECLARE
1930 CURSOR c1 IS
1931 --modified for performance bug 3699543
1932 SELECT 'X'
1933 FROM igs_pe_typ_instances_all PTI,
1934 IGS_PE_PERSON_TYPES PPT
1935 WHERE PPT.system_type = 'STUDENT' AND
1936 ppt.person_type_code = pti.person_type_code AND
1937 pti.COURSE_CD = v_sca_exists.COURSE_CD AND
1938 pti.PERSON_ID = v_sca_exists.PERSON_ID;
1939
1940 c1_rec c1%ROWTYPE;
1941
1942 CURSOR c_former_stdnt IS
1943 SELECT person_type_code
1944 FROM igs_pe_person_types
1945 WHERE system_type = 'FORMER_STUDENT';
1946
1947 l_person_type_code igs_pe_person_types.person_type_code%TYPE := NULL;
1948
1949 l_rowid VARCHAR2(25);
1950 l_pk NUMBER(15);
1951 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
1952
1953 BEGIN
1954 -- fetching person_type_code for system_type of 'FORMER_STUDENT'
1955 OPEN c_former_stdnt;
1956 FETCH c_former_stdnt INTO l_person_type_code;
1957 CLOSE c_former_stdnt;
1958
1959 OPEN c1;
1960 FETCH c1 INTO c1_rec;
1961 IF c1%FOUND THEN
1962
1963 DECLARE
1964 CURSOR c2 IS
1965 SELECT ROWID, ti.*
1966 FROM igs_pe_typ_instances_all ti
1967 WHERE course_cd = v_sca_exists.COURSE_CD AND
1968 person_id = v_sca_exists.PERSON_ID;
1969
1970 l_rowid VARCHAR2(25);
1971 l_pk NUMBER(15);
1972
1973 BEGIN
1974
1975 FOR c2_rec IN c2 LOOP
1976 Igs_Pe_Typ_Instances_Pkg.update_row(
1977 -- X_ROWID =>l_rowid, -- Old
1978 X_ROWID =>c2_rec.ROWID, -- New due to bug no#1516658
1979 X_PERSON_ID =>c2_rec.PERSON_ID,
1980 X_COURSE_CD =>c2_rec.COURSE_CD,
1981 X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
1982 X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
1983 X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
1984 X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
1985 X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
1986 X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
1987 X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
1988 X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
1989 X_START_DATE =>c2_rec.START_DATE,
1990 X_END_DATE => SYSDATE,
1991 X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
1992 X_ENDED_BY =>c2_rec.ENDED_BY,
1993 X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
1994 X_MODE =>'R',
1995 X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
1996
1997 END LOOP; -- End loop for Ending the Type.
1998
1999 Igs_Pe_Typ_Instances_Pkg.insert_row(
2000 X_ROWID =>l_rowid,
2001 X_PERSON_ID => v_sca_exists.PERSON_ID,
2002 X_COURSE_CD =>v_sca_exists.COURSE_CD,
2003 X_TYPE_INSTANCE_ID =>l_pk,
2004 X_PERSON_TYPE_CODE =>l_person_type_code,
2005 X_CC_VERSION_NUMBER =>NULL,
2006 X_FUNNEL_STATUS => NULL,
2007 X_ADMISSION_APPL_NUMBER=>
2008 v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
2009 X_NOMINATED_COURSE_CD =>
2010 v_sca_exists.ADM_NOMINATED_COURSE_CD,
2011 X_NCC_VERSION_NUMBER =>v_sca_exists.VERSION_NUMBER,
2012 X_SEQUENCE_NUMBER =>NULL,
2013 X_START_DATE => SYSDATE,
2014 X_END_DATE =>NULL,
2015 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2016 X_ENDED_BY =>NULL,
2017 X_END_METHOD =>NULL,
2018 X_MODE =>'R',
2019 x_org_id => l_org_id,
2020 X_EMPLMNT_CATEGORY_CODE => null);
2021 END;-- End for Update Process
2022
2023 ELSE -- If Cursor%NOTFOUND
2024
2025 Igs_Pe_Typ_Instances_Pkg.insert_row(
2026 X_ROWID =>l_rowid,
2027 X_PERSON_ID => v_sca_exists.PERSON_ID,
2028 X_COURSE_CD =>v_sca_exists.COURSE_CD,
2029 X_TYPE_INSTANCE_ID =>l_pk,
2030 X_PERSON_TYPE_CODE =>l_person_type_code,
2031 X_CC_VERSION_NUMBER =>NULL,
2032 X_FUNNEL_STATUS => NULL,
2033 X_ADMISSION_APPL_NUMBER
2034 =>v_sca_exists.ADM_ADMISSION_APPL_NUMBER,
2035 X_NOMINATED_COURSE_CD
2036 =>v_sca_exists.ADM_NOMINATED_COURSE_CD,
2037 X_NCC_VERSION_NUMBER =>v_sca_exists.VERSION_NUMBER,
2038 X_SEQUENCE_NUMBER =>NULL,
2039 X_START_DATE => SYSDATE,
2040 X_END_DATE =>NULL,
2041 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2042 X_ENDED_BY =>NULL,
2043 X_END_METHOD =>NULL,
2044 X_MODE =>'R',
2045 x_org_id => l_org_id,
2046 X_EMPLMNT_CATEGORY_CODE => null);
2047
2048 END IF;-- End IF for Cursor%FOUND
2049 CLOSE C1;
2050 END;
2051
2052 CLOSE c_sca;
2053 ELSE
2054 CLOSE c_sca;
2055 END IF; --End IF for sca%found
2056
2057 OPEN c_sua(
2058 v_sca_crv_rec.person_id,
2059 v_sca_crv_rec.course_cd);
2060 FETCH c_sua INTO v_sua_rec;
2061 IF (c_sua%FOUND) THEN
2062 v_alternate_code := Igs_En_Gen_014.ENRS_GET_ACAD_ALT_CD(v_sua_rec.cal_type,
2063 v_sua_rec.ci_sequence_number);
2064 v_last_teaching := v_alternate_code || '/' || v_sua_rec.alternate_code;
2065 ELSE
2066 v_last_teaching := '-';
2067 END IF;
2068 CLOSE c_sua;
2069
2070 -- Write log entry indicating future enrolled IGS_PS_UNIT attempts
2071 -- were found - typically the student will be intermittent
2072 -- or the like.
2073
2074 Igs_Ge_Gen_003.genp_ins_log_entry(cst_sca_lapse,
2075 v_log_creation_dt,
2076 v_sca_crv_rec.person_id || ',' ||
2077 v_sca_crv_rec.course_cd || ',' ||
2078 v_last_teaching,
2079 NULL,
2080 NULL);
2081 EXCEPTION
2082
2083 WHEN e_record_locked THEN
2084 NULL;
2085 END;
2086 END IF; -- End If for v_lapsed
2087 END LOOP;
2088 -- initially thought of commenting the commit statement but on investigating it found that
2089 -- this procedure is called from IGSEN%01.rdf reports hence the commit is used
2090 -- Hence do not comment this commit statement
2091 -- amuthu 27-jul-2001
2092 COMMIT;
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095 IF (c_secc%ISOPEN) THEN
2096 CLOSE c_secc;
2097 END IF;
2098 IF (c_daiv%ISOPEN) THEN
2099 CLOSE c_daiv;
2100 END IF;
2101 IF (c_sca_crv%ISOPEN) THEN
2102 CLOSE c_sca_crv;
2103 END IF;
2104 IF (c_scae_ci%ISOPEN) THEN
2105 CLOSE c_scae_ci;
2106 END IF;
2107 IF (c_sua%ISOPEN) THEN
2108 CLOSE c_sua;
2109 END IF;
2110 RAISE;
2111 END;
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114
2115 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2116 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrp_upd_sca_lapse');
2117 Igs_Ge_Msg_Stack.ADD;
2118 App_Exception.Raise_Exception;
2119 END enrp_upd_sca_lapse;
2120
2121 FUNCTION Enrp_Upd_Sca_Status(
2122 p_person_id IN NUMBER ,
2123 p_course_cd IN VARCHAR2 ,
2124 p_message_name OUT NOCOPY VARCHAR2)
2125 RETURN BOOLEAN AS
2126 CURSOR c_sca (cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2127 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
2128 IS
2129 SELECT sca.course_attempt_status
2130 FROM IGS_EN_STDNT_PS_ATT sca
2131 WHERE sca.person_id = p_person_id AND
2132 sca.course_cd = p_course_cd;
2133 v_course_attempt_status IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE;
2134 v_new_course_attempt_status
2135 IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE;
2136 v_other_detail VARCHAR2(255);
2137 v_person_id IGS_PE_PERSON.person_id%TYPE;
2138 /****************************************************************************
2139 History
2140 Who When Why
2141 sarakshi 19-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
2142 ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
2143 call as a part of bug 3544927.
2144
2145 *****************************************************************************/
2146 BEGIN
2147 p_message_name := NULL;
2148 OPEN c_sca (p_person_id, p_course_cd);
2149 FETCH c_sca INTO v_course_attempt_status;
2150 IF c_sca%NOTFOUND THEN
2151 CLOSE c_sca;
2152 RETURN TRUE;
2153 END IF;
2154 CLOSE c_sca;
2155 v_new_course_attempt_status := Igs_En_Gen_006.ENRP_GET_SCA_STATUS(p_person_id,
2156 p_course_cd,
2157 NULL,
2158 NULL,
2159 NULL,
2160 NULL,
2161 NULL,
2162 NULL);
2163
2164 IF v_course_attempt_status <> v_new_course_attempt_status THEN
2165 -- Attempt to lock the record - failure will fall through to
2166 -- the exception handler.
2167 DECLARE
2168 CURSOR c_ps_att IS
2169 SELECT ROWID,
2170 IGS_EN_STDNT_PS_ATT.*
2171 FROM IGS_EN_STDNT_PS_ATT
2172 WHERE person_id = p_person_id AND
2173 course_cd = p_course_cd
2174 FOR UPDATE NOWAIT;
2175 BEGIN
2176 -- Having gotten the lock, update the record.
2177
2178 FOR c_ps_att_rec IN c_ps_att LOOP
2179 Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
2180 X_ROWID => c_ps_att_rec.ROWID,
2181 X_PERSON_ID => c_ps_att_rec.PERSON_ID,
2182 X_COURSE_CD => c_ps_att_rec.COURSE_CD,
2183 X_ADVANCED_STANDING_IND => c_ps_att_rec.ADVANCED_STANDING_IND,
2184 X_FEE_CAT => c_ps_att_rec.FEE_CAT,
2185 X_CORRESPONDENCE_CAT => c_ps_att_rec.CORRESPONDENCE_CAT,
2186 X_SELF_HELP_GROUP_IND => c_ps_att_rec.SELF_HELP_GROUP_IND,
2187 X_LOGICAL_DELETE_DT => c_ps_att_rec.LOGICAL_DELETE_DT,
2188 X_ADM_ADMISSION_APPL_NUMBER => c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2189 X_ADM_NOMINATED_COURSE_CD => c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
2190 X_ADM_SEQUENCE_NUMBER => c_ps_att_rec.ADM_SEQUENCE_NUMBER,
2191 X_VERSION_NUMBER => c_ps_att_rec.version_number,
2192 X_CAL_TYPE => c_ps_att_rec.cal_type,
2193 X_LOCATION_CD => c_ps_att_rec.location_cd,
2194 X_ATTENDANCE_MODE => c_ps_att_rec.attendance_mode,
2195 X_ATTENDANCE_TYPE => c_ps_att_rec.attendance_type,
2196 X_COO_ID => c_ps_att_rec.coo_id,
2197 X_STUDENT_CONFIRMED_IND => c_ps_att_rec.STUDENT_CONFIRMED_IND,
2198 X_COMMENCEMENT_DT => c_ps_att_rec.COMMENCEMENT_DT,
2199 X_COURSE_ATTEMPT_STATUS => v_new_course_attempt_status,
2200 X_PROGRESSION_STATUS => c_ps_att_rec.PROGRESSION_STATUS,
2201 X_DERIVED_ATT_TYPE => c_ps_att_rec.DERIVED_ATT_TYPE,
2202 X_DERIVED_ATT_MODE => c_ps_att_rec.DERIVED_ATT_MODE,
2203 X_PROVISIONAL_IND => c_ps_att_rec.PROVISIONAL_IND,
2204 X_DISCONTINUED_DT => c_ps_att_rec.discontinued_dt,
2205 X_DISCONTINUATION_REASON_CD => c_ps_att_rec.discontinuation_reason_cd,
2206 X_LAPSED_DT =>c_ps_att_rec.lapsed_dt,
2207 X_FUNDING_SOURCE => c_ps_att_rec.FUNDING_SOURCE,
2208 X_EXAM_LOCATION_CD => c_ps_att_rec.EXAM_LOCATION_CD,
2209 X_DERIVED_COMPLETION_YR => c_ps_att_rec.DERIVED_COMPLETION_YR,
2210 X_DERIVED_COMPLETION_PERD => c_ps_att_rec.DERIVED_COMPLETION_PERD,
2211 X_NOMINATED_COMPLETION_YR => c_ps_att_rec.NOMINATED_COMPLETION_YR,
2212 X_NOMINATED_COMPLETION_PERD => c_ps_att_rec.NOMINATED_COMPLETION_PERD,
2213 X_RULE_CHECK_IND => c_ps_att_rec.RULE_CHECK_IND,
2214 X_WAIVE_OPTION_CHECK_IND => c_ps_att_rec.WAIVE_OPTION_CHECK_IND,
2215 X_LAST_RULE_CHECK_DT => c_ps_att_rec.LAST_RULE_CHECK_DT,
2216 X_PUBLISH_OUTCOMES_IND => c_ps_att_rec.PUBLISH_OUTCOMES_IND,
2217 X_COURSE_RQRMNT_COMPLETE_IND => c_ps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
2218 X_COURSE_RQRMNTS_COMPLETE_DT => c_ps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
2219 X_S_COMPLETED_SOURCE_TYPE => c_ps_att_rec.S_COMPLETED_SOURCE_TYPE,
2220 X_OVERRIDE_TIME_LIMITATION => c_ps_att_rec.OVERRIDE_TIME_LIMITATION,
2221 X_MODE => 'R',
2222 X_LAST_DATE_OF_ATTENDANCE => c_ps_att_rec.LAST_DATE_OF_ATTENDANCE,
2223 X_DROPPED_BY => c_ps_att_rec.DROPPED_BY,
2224 X_IGS_PR_CLASS_STD_ID => c_ps_att_rec.IGS_PR_CLASS_STD_ID,
2225 -- Added next four parameters as per the Career Impact Build Bug# 2027984
2226 x_primary_program_type => c_ps_att_rec.primary_program_type,
2227 x_primary_prog_type_source => c_ps_att_rec.primary_prog_type_source,
2228 x_catalog_cal_type => c_ps_att_rec.catalog_cal_type,
2229 x_catalog_seq_num => c_ps_att_rec.catalog_seq_num,
2230 x_key_program => c_ps_att_rec.key_program,
2231 -- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
2232 x_override_cmpl_dt => c_ps_att_rec.override_cmpl_dt,
2233 x_manual_ovr_cmpl_dt_ind => c_ps_att_rec.manual_ovr_cmpl_dt_ind,
2234 -- added by ckasu as part of bug # 3544927
2235 X_ATTRIBUTE_CATEGORY => c_ps_att_rec.ATTRIBUTE_CATEGORY,
2236 X_ATTRIBUTE1 => c_ps_att_rec.ATTRIBUTE1,
2237 X_ATTRIBUTE2 => c_ps_att_rec.ATTRIBUTE2,
2238 X_ATTRIBUTE3 => c_ps_att_rec.ATTRIBUTE3,
2239 X_ATTRIBUTE4 => c_ps_att_rec.ATTRIBUTE4,
2240 X_ATTRIBUTE5 => c_ps_att_rec.ATTRIBUTE5,
2241 X_ATTRIBUTE6 => c_ps_att_rec.ATTRIBUTE6,
2242 X_ATTRIBUTE7 => c_ps_att_rec.ATTRIBUTE7,
2243 X_ATTRIBUTE8 => c_ps_att_rec.ATTRIBUTE8,
2244 X_ATTRIBUTE9 => c_ps_att_rec.ATTRIBUTE9,
2245 X_ATTRIBUTE10 => c_ps_att_rec.ATTRIBUTE10,
2246 X_ATTRIBUTE11 => c_ps_att_rec.ATTRIBUTE11,
2247 X_ATTRIBUTE12 => c_ps_att_rec.ATTRIBUTE12,
2248 X_ATTRIBUTE13 => c_ps_att_rec.ATTRIBUTE13,
2249 X_ATTRIBUTE14 => c_ps_att_rec.ATTRIBUTE14,
2250 X_ATTRIBUTE15 => c_ps_att_rec.ATTRIBUTE15,
2251 X_ATTRIBUTE16 => c_ps_att_rec.ATTRIBUTE16,
2252 X_ATTRIBUTE17 => c_ps_att_rec.ATTRIBUTE17,
2253 X_ATTRIBUTE18 => c_ps_att_rec.ATTRIBUTE18,
2254 X_ATTRIBUTE19 => c_ps_att_rec.ATTRIBUTE19,
2255 X_ATTRIBUTE20 => c_ps_att_rec.ATTRIBUTE20,
2256 X_FUTURE_DATED_TRANS_FLAG => c_ps_att_rec.FUTURE_DATED_TRANS_FLAG
2257 );
2258
2259 IF v_new_course_attempt_status= 'LAPSED' THEN
2260
2261 DECLARE
2262 CURSOR c1 IS
2263 -- modified for perf bug 3699560
2264 SELECT 'X'
2265 FROM igs_pe_typ_instances_all PTI,
2266 IGS_PE_PERSON_TYPES PPT
2267 WHERE PPT.system_type = 'STUDENT' AND
2268 ppt.person_type_code = pti.person_type_code
2269 AND pti.COURSE_CD = c_ps_att_rec.COURSE_CD
2270 AND pti.PERSON_ID = c_ps_att_rec.PERSON_ID;
2271
2272 c1_rec c1%ROWTYPE;
2273
2274 CURSOR c_former_stdnt IS
2275 SELECT person_type_code
2276 FROM igs_pe_person_types
2277 WHERE system_type = 'FORMER_STUDENT';
2278
2279 l_person_type_code igs_pe_person_types.person_type_code%TYPE := NULL;
2280
2281
2282 l_rowid VARCHAR2(25);
2283 l_pk NUMBER(15);
2284
2285 BEGIN
2286
2287 -- fetching person_type_code for system_type of 'FORMER_STUDENT'
2288 OPEN c_former_stdnt;
2289 FETCH c_former_stdnt INTO l_person_type_code;
2290 CLOSE c_former_stdnt;
2291 OPEN c1;
2292 FETCH c1 INTO c1_rec;
2293 IF c1%FOUND THEN
2294 DECLARE
2295 CURSOR c2 IS
2296 SELECT ROWID, ti.*
2297 FROM igs_pe_typ_instances_all ti
2298 WHERE course_cd = c_ps_att_rec.COURSE_CD
2299 AND person_id = c_ps_att_rec.PERSON_ID;
2300 l_rowid VARCHAR2(25);
2301 l_pk NUMBER(15);
2302 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
2303 BEGIN
2304
2305
2306
2307 FOR c2_rec IN c2 LOOP
2308
2309 Igs_Pe_Typ_Instances_Pkg.update_row(
2310 -- previously l_rowid was being passed to the update row
2311 -- changed it to c2_rec.rowid
2312 -- amuthu 27-Jul-2001
2313 X_ROWID =>c2_rec.ROWID,
2314 X_PERSON_ID =>c2_rec.PERSON_ID,
2315 X_COURSE_CD =>c2_rec.COURSE_CD,
2316 X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
2317 X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
2318 X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
2319 X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
2320 X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
2321 X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
2322 X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
2323 X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
2324 X_START_DATE =>c2_rec.START_DATE,
2325 X_END_DATE => SYSDATE,
2326 X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
2327 X_ENDED_BY =>c2_rec.ENDED_BY,
2328 X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
2329 X_MODE =>'R',
2330 X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
2331 END LOOP; -- End loop for Ending the Type.
2332
2333
2334 Igs_Pe_Typ_Instances_Pkg.insert_row(
2335 X_ROWID =>l_rowid,
2336 X_PERSON_ID => c_ps_att_rec.PERSON_ID,
2337 X_COURSE_CD =>c_ps_att_rec.COURSE_CD,
2338 X_TYPE_INSTANCE_ID =>l_pk,
2339 X_PERSON_TYPE_CODE =>l_person_type_code,
2340 X_CC_VERSION_NUMBER =>NULL,
2341 X_FUNNEL_STATUS => NULL,
2342 X_ADMISSION_APPL_NUMBER=>
2343 c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2344 X_NOMINATED_COURSE_CD =>
2345 c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
2346 X_NCC_VERSION_NUMBER =>c_ps_att_rec.VERSION_NUMBER,
2347 X_SEQUENCE_NUMBER =>NULL,
2348 X_START_DATE => SYSDATE,
2349 X_END_DATE =>NULL,
2350 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2351 X_ENDED_BY =>NULL,
2352 X_END_METHOD =>NULL,
2353 X_MODE =>'R',
2354 x_org_id => l_org_id,
2355 X_EMPLMNT_CATEGORY_CODE => null);
2356 END;-- End for Update Process
2357 ELSE -- If Cursor%NOTFOUND
2358 DECLARE
2359 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
2360 BEGIN
2361 Igs_Pe_Typ_Instances_Pkg.insert_row(
2362 X_ROWID =>l_rowid,
2363 X_PERSON_ID => c_ps_att_rec.PERSON_ID,
2364 X_COURSE_CD =>c_ps_att_rec.COURSE_CD,
2365 X_TYPE_INSTANCE_ID =>l_pk,
2366 X_PERSON_TYPE_CODE =>l_person_type_code,
2367 X_CC_VERSION_NUMBER =>NULL,
2368 X_FUNNEL_STATUS => NULL,
2369 X_ADMISSION_APPL_NUMBER
2370 =>c_ps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2371 X_NOMINATED_COURSE_CD
2372 =>c_ps_att_rec.ADM_NOMINATED_COURSE_CD,
2373 X_NCC_VERSION_NUMBER =>c_ps_att_rec.VERSION_NUMBER,
2374 X_SEQUENCE_NUMBER =>NULL,
2375 X_START_DATE => SYSDATE,
2376 X_END_DATE =>NULL,
2377 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2378 X_ENDED_BY =>NULL,
2379 X_END_METHOD =>NULL,
2380 X_MODE =>'R',
2381 x_org_id => l_org_id,
2382 X_EMPLMNT_CATEGORY_CODE => null);
2383 END;
2384 END IF;-- End IF for Cursor%FOUND
2385 CLOSE C1;
2386 END;
2387 END IF;
2388 END LOOP;
2389 END;
2390
2391 --Included this code as a part of bug 2335633 to indicate the program attempt status has changed.
2392 p_message_name := 'IGS_AD_OK';
2393 END IF;
2394 RETURN TRUE;
2395 /*commented as part of BUg 1571109
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 IF SQLCODE = -54 THEN
2399 p_message_name := 'IGS_EN_STUD_PRG_REC_LOCKED';
2400 RETURN FALSE;
2401 ELSE
2402 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2403 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.Enrp_Upd_Sca_Status');
2404 Igs_Ge_Msg_Stack.ADD;
2405 App_Exception.Raise_Exception;
2406 END IF;
2407 */
2408 END enrp_upd_sca_status;
2409
2410 PROCEDURE Enrp_Upd_Sca_Statusb(
2411 errbuf OUT NOCOPY VARCHAR2,
2412 retcode OUT NOCOPY NUMBER,
2413 p_org_id IN NUMBER)
2414 AS
2415 ------------------------------------------------------------------------
2416 -- sarakshi 13-sep-2001 modified cursor c_sci_comm and c_sci_end
2417 -- as a part of Acedemic Record Maintenance Build.
2418 -- rnirwani 13-Sep-2004 changed cursor c_sci_comm, c_sci_end to not consider logically deleted records and
2419 -- also to avoid un-approved intermission records. Bug# 3885804
2420 -- smaddali Modified for build EN324 - bug#5091858
2421 -------------------------------------------------------------------------
2422 gv_other_detail VARCHAR2(255);
2423
2424 BEGIN
2425 retcode := 0;
2426 IGS_GE_GEN_003.set_org_id(p_org_id);
2427 DECLARE
2428 v_creation_dt DATE;
2429 v_updates_done NUMBER;
2430 v_message_name VARCHAR2(30);
2431 v_log_text VARCHAR2(2000);
2432
2433 CURSOR c_sci_comm IS
2434 SELECT sca.person_id,
2435 sca.course_cd,
2436 sca.course_attempt_status
2437 FROM IGS_EN_STDNT_PS_INTM sci,
2438 IGS_EN_INTM_TYPES eit,
2439 IGS_EN_STDNT_PS_ATT sca
2440 WHERE sci.start_dt <= trunc(SYSDATE) AND
2441 sci.end_dt >= trunc(SYSDATE) AND
2442 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
2443 sca.person_id = sci.person_id AND
2444 sca.course_cd = sci.course_cd AND
2445 eit.intermission_type=sci.intermission_type AND
2446 ((eit.appr_reqd_ind ='Y' AND sci.approved='Y') OR (eit.appr_reqd_ind='N')) AND
2447 sca.course_attempt_status NOT IN ('INTERMIT',
2448 'DISCONTIN',
2449 'DELETED',
2450 'COMPLETED');
2451
2452 CURSOR c_sci_end IS
2453 SELECT sca.person_id,
2454 sca.course_cd,
2455 sca.course_attempt_status,
2456 sci.start_dt,
2457 sci.logical_delete_date,
2458 cond_return_flag
2459 FROM IGS_EN_STDNT_PS_INTM sci,
2460 IGS_EN_INTM_TYPES eit,
2461 IGS_EN_STDNT_PS_ATT sca
2462 WHERE sci.end_dt < TRUNC(SYSDATE) AND
2463 sca.person_id = sci.person_id AND
2464 sca.course_cd = sci.course_cd AND
2465 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
2466 eit.intermission_type=sci.intermission_type AND
2467 ((eit.appr_reqd_ind ='Y' AND sci.approved='Y') OR (eit.appr_reqd_ind='N')) AND
2468 sca.course_attempt_status = 'INTERMIT';
2469
2470 CURSOR c_scae IS
2471 SELECT sca.person_id, sca.course_cd, sca.course_attempt_status
2472 FROM IGS_EN_STDNT_PS_ATT sca
2473 WHERE sca.course_attempt_status = 'ENROLLED' AND
2474 (
2475 sca.discontinued_dt IS NULL OR
2476 sca.discontinued_dt > SYSDATE
2477 ) AND
2478 NOT EXISTS ( SELECT 'x'
2479 FROM IGS_EN_SU_ATTEMPT sua
2480 WHERE sua.person_id = sca.person_id AND
2481 sua.unit_attempt_status = 'ENROLLED' AND
2482 sua.ci_start_dt <= SYSDATE) AND
2483 NOT EXISTS ( SELECT 'x'
2484 FROM IGS_AS_SC_ATMPT_ENR scae
2485 WHERE scae.person_id = sca.person_id AND
2486 scae.course_cd = sca.course_cd AND
2487 Igs_En_Gen_004.ENRP_GET_SCAE_DUE(
2488 scae.person_id,
2489 scae.course_cd,
2490 scae.cal_type,
2491 scae.ci_sequence_number,
2492 'Y',
2493 scae.enr_form_due_dt) > SYSDATE );
2494
2495 CURSOR c_sca_inactive IS
2496 SELECT sca.person_id,
2497 sca.course_cd,
2498 sca.course_attempt_status
2499 FROM IGS_EN_STDNT_PS_ATT sca
2500 WHERE sca.course_attempt_status IN ('INACTIVE','LAPSED') AND
2501 EXISTS (
2502 SELECT 'x'
2503 FROM IGS_EN_SU_ATTEMPT sua
2504 WHERE person_id = sca.person_id AND
2505 course_cd = sca.course_cd AND
2506 unit_attempt_status = 'ENROLLED' AND
2507 ci_start_dt <= SYSDATE);
2508
2509 CURSOR c_sca_future IS
2510 SELECT sca.person_id,
2511 sca.course_cd,
2512 sca.version_number,
2513 sca.course_attempt_status,
2514 sca.commencement_dt,
2515 sca.discontinued_dt,
2516 sca.discontinuation_reason_cd
2517 FROM IGS_EN_STDNT_PS_ATT sca
2518 WHERE sca.discontinued_dt IS NOT NULL AND
2519 sca.discontinued_dt <= SYSDATE AND
2520 sca.course_attempt_status NOT IN ('DISCONTIN',
2521 'DELETED');
2522 -- smaddali added cursor for build EN324 - bug#5091858
2523 CURSOR c_sci_rcond (cp_person_id hz_parties.party_id%TYPE,
2524 cp_course_cd igs_ps_ver.course_cd%TYPE,
2525 cp_start_dt DATE,
2526 cp_logical_del_dt DATE) IS
2527 SELECT hz.party_number
2528 FROM igs_en_spi_rconds rc, hz_parties hz
2529 WHERE rc.person_id = hz.party_id
2530 AND rc.person_id =cp_person_id
2531 AND rc.course_cd =cp_course_cd
2532 AND rc.start_dt =cp_start_dt
2533 AND rc.logical_delete_date =cp_logical_del_dt
2534 AND rc.status_code IN('FAILED','PENDING');
2535 l_rcond_exists c_sci_rcond%ROWTYPE;
2536
2537
2538 FUNCTION enrpl_upd_get_status(
2539 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
2540 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
2541 p_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE)
2542 RETURN BOOLEAN
2543 AS
2544 e_resource_busy_exception EXCEPTION;
2545 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
2546 l_msg_text VARCHAR2(2000);
2547
2548 CURSOR c_person_number (cp_person_id HZ_PARTIES.PARTY_ID%TYPE) IS
2549 SELECT PARTY_NUMBER
2550 FROM HZ_PARTIES
2551 WHERE party_id = cp_person_id;
2552
2553 l_person_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2554
2555 /****************************************************************************
2556 History
2557 Who When Why
2558 sarakshi 19-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
2559 ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
2560 call as a part of bug 3544927.
2561 *****************************************************************************/
2562 BEGIN -- enrpl_upd_get_status
2563
2564 -- This local function calls the function enrp_get_sca_status.
2565 -- If the return value is different to the current
2566 -- student_course_attempt_status the status is updated and TRUE
2567 -- is returned otherwise FALSE is returned.
2568 -- If a locked record is encountered the offending record is recorded
2569 -- and FALSE is returned allowing processing to continue.
2570
2571 OPEN c_person_number(p_person_id);
2572 FETCH c_person_number INTO l_person_number;
2573 CLOSE c_person_number;
2574
2575 SAVEPOINT enrpl_upd_get_status_a;
2576
2577 DECLARE
2578 v_new_course_attempt_status
2579 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
2580 v_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE;
2581 BEGIN
2582
2583
2584 v_new_course_attempt_status := Igs_En_Gen_006.enrp_get_sca_status(
2585 p_person_id,
2586 p_course_cd,
2587 NULL, -- course_attempt_status
2588 NULL, -- student_cnfirmed_ind
2589 NULL, -- discontinued_dt
2590 NULL, -- lapsed_dt
2591 NULL, -- course_rqrmnt_complete_ind
2592 NULL); -- logical_delete_dt
2593
2594 IF (v_new_course_attempt_status <> p_course_attempt_status) THEN
2595 -- Attempt to lock the record - failure will fall through to
2596 -- the exception handler.
2597
2598 DECLARE
2599 CURSOR c_enps_att IS
2600 SELECT ROWID,
2601 IGS_EN_STDNT_PS_ATT.*
2602 FROM IGS_EN_STDNT_PS_ATT
2603 WHERE person_id = p_person_id AND
2604 course_cd = p_course_cd
2605 FOR UPDATE NOWAIT;
2606 BEGIN
2607
2608 -- Having gotten the lock, update the record.
2609 FOR c_enps_att_rec IN c_enps_att LOOP
2610
2611
2612
2613 Igs_En_Stdnt_Ps_Att_Pkg.UPDATE_ROW(
2614 X_ROWID => c_enps_att_rec.ROWID,
2615 X_PERSON_ID => c_enps_att_rec.PERSON_ID,
2616 X_COURSE_CD => c_enps_att_rec.COURSE_CD,
2617 X_ADVANCED_STANDING_IND => c_enps_att_rec.ADVANCED_STANDING_IND,
2618 X_FEE_CAT => c_enps_att_rec.FEE_CAT,
2619 X_CORRESPONDENCE_CAT => c_enps_att_rec.CORRESPONDENCE_CAT,
2620 X_SELF_HELP_GROUP_IND => c_enps_att_rec.SELF_HELP_GROUP_IND,
2621 X_LOGICAL_DELETE_DT => c_enps_att_rec.LOGICAL_DELETE_DT,
2622 X_ADM_ADMISSION_APPL_NUMBER => c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2623 X_ADM_NOMINATED_COURSE_CD => c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
2624 X_ADM_SEQUENCE_NUMBER => c_enps_att_rec.ADM_SEQUENCE_NUMBER,
2625 X_VERSION_NUMBER => c_enps_att_rec.version_number,
2626 X_CAL_TYPE => c_enps_att_rec.cal_type,
2627 X_LOCATION_CD => c_enps_att_rec.location_cd,
2628 X_ATTENDANCE_MODE => c_enps_att_rec.attendance_mode,
2629 X_ATTENDANCE_TYPE => c_enps_att_rec.attendance_type,
2630 X_COO_ID => c_enps_att_rec.coo_id,
2631 X_STUDENT_CONFIRMED_IND => c_enps_att_rec.STUDENT_CONFIRMED_IND,
2632 X_COMMENCEMENT_DT => c_enps_att_rec.COMMENCEMENT_DT,
2633 X_COURSE_ATTEMPT_STATUS => v_new_course_attempt_status,
2634 X_PROGRESSION_STATUS => c_enps_att_rec.PROGRESSION_STATUS,
2635 X_DERIVED_ATT_TYPE => c_enps_att_rec.DERIVED_ATT_TYPE,
2636 X_DERIVED_ATT_MODE => c_enps_att_rec.DERIVED_ATT_MODE,
2637 X_PROVISIONAL_IND => c_enps_att_rec.PROVISIONAL_IND,
2638 X_DISCONTINUED_DT => c_enps_att_rec.discontinued_dt,
2639 X_DISCONTINUATION_REASON_CD => c_enps_att_rec.discontinuation_reason_cd,
2640 X_LAPSED_DT =>c_enps_att_rec.lapsed_dt,
2641 X_FUNDING_SOURCE => c_enps_att_rec.FUNDING_SOURCE,
2642 X_EXAM_LOCATION_CD => c_enps_att_rec.EXAM_LOCATION_CD,
2643 X_DERIVED_COMPLETION_YR => c_enps_att_rec.DERIVED_COMPLETION_YR,
2644 X_DERIVED_COMPLETION_PERD => c_enps_att_rec.DERIVED_COMPLETION_PERD,
2645 X_NOMINATED_COMPLETION_YR => c_enps_att_rec.NOMINATED_COMPLETION_YR,
2646 X_NOMINATED_COMPLETION_PERD => c_enps_att_rec.NOMINATED_COMPLETION_PERD,
2647 X_RULE_CHECK_IND => c_enps_att_rec.RULE_CHECK_IND,
2648 X_WAIVE_OPTION_CHECK_IND => c_enps_att_rec.WAIVE_OPTION_CHECK_IND,
2649 X_LAST_RULE_CHECK_DT => c_enps_att_rec.LAST_RULE_CHECK_DT,
2650 X_PUBLISH_OUTCOMES_IND => c_enps_att_rec.PUBLISH_OUTCOMES_IND,
2651 X_COURSE_RQRMNT_COMPLETE_IND => c_enps_att_rec.COURSE_RQRMNT_COMPLETE_IND,
2652 X_COURSE_RQRMNTS_COMPLETE_DT => c_enps_att_rec.COURSE_RQRMNTS_COMPLETE_DT,
2653 X_S_COMPLETED_SOURCE_TYPE => c_enps_att_rec.S_COMPLETED_SOURCE_TYPE,
2654 X_OVERRIDE_TIME_LIMITATION => c_enps_att_rec.OVERRIDE_TIME_LIMITATION,
2655 X_MODE => 'R',
2656 X_LAST_DATE_OF_ATTENDANCE => c_enps_att_rec.LAST_DATE_OF_ATTENDANCE,
2657 X_DROPPED_BY => c_enps_att_rec.DROPPED_BY,
2658 X_IGS_PR_CLASS_STD_ID => c_enps_att_rec.IGS_PR_CLASS_STD_ID,
2659 -- Added next four parameters as per the Career Impact Build Bug# 2027984
2660 x_primary_program_type => c_enps_att_rec.primary_program_type,
2661 x_primary_prog_type_source => c_enps_att_rec.primary_prog_type_source,
2662 x_catalog_cal_type => c_enps_att_rec.catalog_cal_type,
2663 x_catalog_seq_num => c_enps_att_rec.catalog_seq_num ,
2664 x_key_program => c_enps_att_rec.key_program,
2665 -- The following two parameters were added as part of EN015 build. Bug# 2158654 - pradhakr
2666 x_override_cmpl_dt => c_enps_att_rec.override_cmpl_dt,
2667 x_manual_ovr_cmpl_dt_ind => c_enps_att_rec.manual_ovr_cmpl_dt_ind,
2668 -- added by ckasu as part of bug # 3544927
2669 X_ATTRIBUTE_CATEGORY => c_enps_att_rec.ATTRIBUTE_CATEGORY,
2670 X_ATTRIBUTE1 => c_enps_att_rec.ATTRIBUTE1,
2671 X_ATTRIBUTE2 => c_enps_att_rec.ATTRIBUTE2,
2672 X_ATTRIBUTE3 => c_enps_att_rec.ATTRIBUTE3,
2673 X_ATTRIBUTE4 => c_enps_att_rec.ATTRIBUTE4,
2674 X_ATTRIBUTE5 => c_enps_att_rec.ATTRIBUTE5,
2675 X_ATTRIBUTE6 => c_enps_att_rec.ATTRIBUTE6,
2676 X_ATTRIBUTE7 => c_enps_att_rec.ATTRIBUTE7,
2677 X_ATTRIBUTE8 => c_enps_att_rec.ATTRIBUTE8,
2678 X_ATTRIBUTE9 => c_enps_att_rec.ATTRIBUTE9,
2679 X_ATTRIBUTE10 => c_enps_att_rec.ATTRIBUTE10,
2680 X_ATTRIBUTE11 => c_enps_att_rec.ATTRIBUTE11,
2681 X_ATTRIBUTE12 => c_enps_att_rec.ATTRIBUTE12,
2682 X_ATTRIBUTE13 => c_enps_att_rec.ATTRIBUTE13,
2683 X_ATTRIBUTE14 => c_enps_att_rec.ATTRIBUTE14,
2684 X_ATTRIBUTE15 => c_enps_att_rec.ATTRIBUTE15,
2685 X_ATTRIBUTE16 => c_enps_att_rec.ATTRIBUTE16,
2686 X_ATTRIBUTE17 => c_enps_att_rec.ATTRIBUTE17,
2687 X_ATTRIBUTE18 => c_enps_att_rec.ATTRIBUTE18,
2688 X_ATTRIBUTE19 => c_enps_att_rec.ATTRIBUTE19,
2689 X_ATTRIBUTE20 => c_enps_att_rec.ATTRIBUTE20,
2690 X_FUTURE_DATED_TRANS_FLAG => c_enps_att_rec.FUTURE_DATED_TRANS_FLAG);
2691
2692
2693 IF v_new_course_attempt_status= 'LAPSED' THEN
2694
2695 DECLARE
2696 CURSOR c1 IS
2697 -- modified for perf bug 3699628
2698 SELECT 'X'
2699 FROM igs_pe_typ_instances_all PTI,
2700 IGS_PE_PERSON_TYPES PPT
2701 WHERE PPT.system_type = 'STUDENT' AND
2702 ppt.person_type_code = pti.person_type_code
2703 AND pti.COURSE_CD = c_enps_att_rec.COURSE_CD
2704 AND pti.PERSON_ID = c_enps_att_rec.PERSON_ID;
2705 c1_rec c1%ROWTYPE;
2706
2707 CURSOR c_former_stdnt IS
2708 SELECT person_type_code
2709 FROM igs_pe_person_types
2710 WHERE system_type = 'FORMER_STUDENT';
2711
2712 l_person_type_code igs_pe_person_types.person_type_code%TYPE := NULL;
2713
2714 l_rowid VARCHAR2(25);
2715 l_pk NUMBER(15);
2716 BEGIN
2717 -- fetching person_type_code for system_type of 'FORMER_STUDENT'
2718 OPEN c_former_stdnt;
2719 FETCH c_former_stdnt INTO l_person_type_code;
2720 CLOSE c_former_stdnt;
2721
2722 OPEN c1;
2723 FETCH c1 INTO c1_rec;
2724 IF c1%FOUND THEN
2725
2726 DECLARE
2727
2728 CURSOR c2 IS
2729 SELECT ROWID, ti.*
2730 FROM igs_pe_typ_instances_all ti
2731 WHERE course_cd = c_enps_att_rec.COURSE_CD
2732 AND person_id = c_enps_att_rec.PERSON_ID;
2733 l_rowid VARCHAR2(25);
2734 l_pk NUMBER(15);
2735 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
2736
2737 BEGIN
2738
2739
2740
2741 FOR c2_rec IN c2 LOOP
2742 Igs_Pe_Typ_Instances_Pkg.update_row(
2743 -- previously l_rowid was being passed to the update row
2744 -- changed it to c2_rec.rowid
2745 -- amuthu 27-Jul-2001
2746 X_ROWID =>c2_rec.ROWID,
2747 X_PERSON_ID =>c2_rec.PERSON_ID,
2748 X_COURSE_CD =>c2_rec.COURSE_CD,
2749 X_TYPE_INSTANCE_ID =>c2_rec.TYPE_INSTANCE_ID,
2750 X_PERSON_TYPE_CODE =>c2_rec.PERSON_TYPE_CODE,
2751 X_CC_VERSION_NUMBER =>c2_rec.CC_VERSION_NUMBER,
2752 X_FUNNEL_STATUS =>c2_rec.FUNNEL_STATUS,
2753 X_ADMISSION_APPL_NUMBER =>c2_rec.ADMISSION_APPL_NUMBER,
2754 X_NOMINATED_COURSE_CD =>c2_rec.NOMINATED_COURSE_CD,
2755 X_NCC_VERSION_NUMBER =>c2_rec.NCC_VERSION_NUMBER,
2756 X_SEQUENCE_NUMBER =>c2_rec.SEQUENCE_NUMBER,
2757 X_START_DATE =>c2_rec.START_DATE,
2758 X_END_DATE => SYSDATE,
2759 X_CREATE_METHOD =>c2_rec.CREATE_METHOD,
2760 X_ENDED_BY =>c2_rec.ENDED_BY,
2761 X_END_METHOD =>'PRG_ATTMPT_ST_INACTIVE',
2762 X_MODE =>'R',
2763 X_EMPLMNT_CATEGORY_CODE => c2_rec.emplmnt_category_code);
2764 END LOOP; -- End loop for Ending the Type.
2765 Igs_Pe_Typ_Instances_Pkg.insert_row(
2766 X_ROWID =>l_rowid,
2767 X_PERSON_ID => c_enps_att_rec.PERSON_ID,
2768 X_COURSE_CD =>c_enps_att_rec.COURSE_CD,
2769 X_TYPE_INSTANCE_ID =>l_pk,
2770 X_PERSON_TYPE_CODE =>l_person_type_code,
2771 X_CC_VERSION_NUMBER =>NULL,
2772 X_FUNNEL_STATUS => NULL,
2773 X_ADMISSION_APPL_NUMBER=>
2774 c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2775 X_NOMINATED_COURSE_CD =>
2776 c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
2777 X_NCC_VERSION_NUMBER =>c_enps_att_rec.VERSION_NUMBER,
2778 X_SEQUENCE_NUMBER =>NULL,
2779 X_START_DATE => SYSDATE,
2780 X_END_DATE =>NULL,
2781 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2782 X_ENDED_BY =>NULL,
2783 X_END_METHOD =>NULL,
2784 X_MODE =>'R',
2785 x_org_id => l_org_id,
2786 X_EMPLMNT_CATEGORY_CODE => null);
2787 END;-- End for Update Process
2788 ELSE -- If Cursor%NOTFOUND
2789
2790 DECLARE
2791
2792 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
2793 BEGIN
2794 Igs_Pe_Typ_Instances_Pkg.insert_row(
2795 X_ROWID =>l_rowid,
2796 X_PERSON_ID => c_enps_att_rec.PERSON_ID,
2797 X_COURSE_CD =>c_enps_att_rec.COURSE_CD,
2798 X_TYPE_INSTANCE_ID =>l_pk,
2799 X_PERSON_TYPE_CODE =>l_person_type_code,
2800 X_CC_VERSION_NUMBER =>NULL,
2801 X_FUNNEL_STATUS => NULL,
2802 X_ADMISSION_APPL_NUMBER
2803 =>c_enps_att_rec.ADM_ADMISSION_APPL_NUMBER,
2804 X_NOMINATED_COURSE_CD
2805 =>c_enps_att_rec.ADM_NOMINATED_COURSE_CD,
2806 X_NCC_VERSION_NUMBER =>c_enps_att_rec.VERSION_NUMBER,
2807 X_SEQUENCE_NUMBER =>NULL,
2808 X_START_DATE => SYSDATE,
2809 X_END_DATE =>NULL,
2810 X_CREATE_METHOD =>'PRG_ATTMPT_ST_LAPSED',
2811 X_ENDED_BY =>NULL,
2812 X_END_METHOD =>NULL,
2813 X_MODE =>'R',
2814 x_org_id => l_org_id,
2815 X_EMPLMNT_CATEGORY_CODE => null);
2816 END;
2817 END IF;-- End IF for Cursor%FOUND
2818 CLOSE C1;
2819 END;
2820 END IF;
2821 END LOOP;
2822 END;
2823
2824
2825 RETURN TRUE;
2826 END IF;
2827 RETURN FALSE;
2828 END;
2829 EXCEPTION
2830 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2831 l_msg_text := NULL;
2832 l_msg_text := FND_MESSAGE.GET;
2833 ROLLBACK TO enrpl_upd_get_status_a;
2834 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_number || ':' || p_course_cd || '- ' || l_msg_text);
2835 RETURN FALSE;
2836 WHEN OTHERS THEN
2837 IF SQLCODE = -54 THEN
2838 FND_MESSAGE.SET_NAME('FND','FND_LOCK_RECORD_ERROR');
2839 l_msg_text := NULL;
2840 l_msg_text := FND_MESSAGE.GET;
2841 ROLLBACK TO enrpl_upd_get_status_a;
2842 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_number || ':' || p_course_cd || '- ' || l_msg_text);
2843 RETURN FALSE;
2844 ELSE
2845 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2846 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrpl_upd_get_status');
2847 l_msg_text := NULL;
2848 l_msg_text := FND_MESSAGE.GET;
2849 ROLLBACK TO enrpl_upd_get_status_a;
2850 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_number || ':' || p_course_cd || '- ' || l_msg_text);
2851 RETURN FALSE;
2852 END IF;
2853 END enrpl_upd_get_status;
2854
2855 BEGIN
2856 -- This updates the IGS_EN_STDNT_PS_ATT.course_attempt_status
2857 -- when an element of the student's enrolment has chaged IN
2858 -- such a way as to require a re-derivation of their status.
2859 -- These things are :
2860 -- 1. If an intermission period has commenced and their
2861 -- status is not INTERMIT
2862 -- 2. If an intermission period has ended and their
2863 -- status is still INTERMIT
2864 -- 3. If an enrolment form due date is reached and their
2865 -- status is ENROLLED (check that at lease one student
2866 -- IGS_PS_UNIT attempt has a status of enrolled)
2867 -- 4. If status is INACTIVE or LAPSED and the teaching calender start date of
2868 -- an ENROLLED IGS_PS_UNIT attempt has been reached
2869 -- 5. IF a future discontinuation date has been reached and
2870 -- the IGS_PS_COURSE attempt is still ENROLLED.
2871 --
2872 -- IGS_GE_NOTE : this process should be run by the job scheduler
2873 -- on a nightly basis to pick up statuses which
2874 -- should be re-calculated.
2875 -- Create a system log on the database
2876
2877 Igs_Ge_Gen_003.genp_ins_log(
2878 'SCA-ST-UPD',
2879 NULL,
2880 v_creation_dt);
2881 -- commit the changes made
2882 COMMIT;
2883
2884 -- 1. CHECK FOR INTERMISSION PERIODS WHICH ARE COMMENCING
2885 -- IGS_GE_NOTE : also ignore IGS_PS_COURSE attempts with a status which
2886 -- would prevent an intermission from being actioned,
2887 -- being discontinuation, deletion or completed
2888 -- setting that no updates have yet been performed
2889 v_updates_done := 0;
2890
2891 FOR v_sci_comm IN c_sci_comm LOOP
2892 -- get the status
2893 IF enrpl_upd_get_status(
2894 v_sci_comm.person_id,
2895 v_sci_comm.course_cd,
2896 v_sci_comm.course_attempt_status) = TRUE THEN
2897
2898 v_updates_done := v_updates_done + 1;
2899 END IF;
2900 END LOOP;
2901
2902 -- the number of updates done to student IGS_PS_COURSE attempt records
2903 -- have been summed and used in the call to genp_ins_log_entry
2904 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STUD_INTER_ACTIVE');
2905 FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
2906 v_log_text := FND_MESSAGE.GET;
2907 FND_FILE.PUT_LINE(FND_FILE.LOG,v_log_text);
2908
2909
2910 Igs_Ge_Gen_003.genp_ins_log_entry(
2911 'SCA-ST-UPD',
2912 v_creation_dt,
2913 'INTERMIT',
2914 NULL,
2915 v_log_text);
2916
2917 -- commit the changes made
2918 COMMIT;
2919
2920
2921 -- 2. CHECK FOR INTERMISSION PERIODS WHICH ARE ENDING
2922 -- setting that no updates have yet been performed
2923 v_updates_done := 0;
2924
2925 FOR v_sci_end IN c_sci_end LOOP
2926
2927 -- get the pending/failed return conditions for the student intermission record
2928 l_rcond_exists := NULL;
2929 OPEN c_sci_rcond(v_sci_end.person_id,
2930 v_sci_end.course_cd,
2931 v_sci_end.start_dt,
2932 v_sci_end.logical_delete_date);
2933 FETCH c_sci_rcond INTO l_rcond_exists;
2934 CLOSE c_sci_rcond;
2935 -- if this student intermission requires authorization to return and
2936 -- this student intermission has got failed/pending return conditions then cannot make program
2937 -- attempt active. Hence log a message
2938 IF (v_sci_end.cond_return_flag = 'Y' AND l_rcond_exists.party_number IS NOT NULL ) THEN
2939 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_UPD_SCA_RCONDS');
2940 FND_MESSAGE.SET_TOKEN('PER_NUM',TO_CHAR(l_rcond_exists.party_number));
2941 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2942 ELSE
2943
2944 -- get the status
2945 IF enrpl_upd_get_status(
2946 v_sci_end.person_id,
2947 v_sci_end.course_cd,
2948 v_sci_end.course_attempt_status) = TRUE THEN
2949
2950 v_updates_done := v_updates_done + 1;
2951 END IF;
2952 END IF;
2953 END LOOP;
2954
2955 -- the number of updates done to student IGS_PS_COURSE attempt records
2956 -- have been summed and used in the call to genp_ins_log_entry
2957 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STUD_INTER_END');
2958 FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
2959 v_log_text := FND_MESSAGE.GET;
2960 FND_FILE.PUT_LINE(FND_FILE.LOG,v_log_text);
2961
2962 Igs_Ge_Gen_003.genp_ins_log_entry(
2963 'SCA-ST-UPD',
2964 v_creation_dt,
2965 'NONINTERMIT',
2966 NULL,
2967 v_log_text);
2968 -- commit the changes made
2969 COMMIT;
2970
2971
2972 -- 3. CHECK FOR ENROLMENT FORM DUE DATE
2973 -- setting that no updates have yet been performed
2974 v_updates_done := 0;
2975
2976 FOR v_scae IN c_scae LOOP
2977 -- get the status
2978 IF enrpl_upd_get_status(
2979 v_scae.person_id,
2980 v_scae.course_cd,
2981 v_scae.course_attempt_status) = TRUE THEN
2982 v_updates_done := v_updates_done + 1;
2983 END IF;
2984 END LOOP;
2985
2986
2987 -- the number of updates done to student IGS_PS_COURSE attempt records
2988 -- have been summed and used in the call to genp_ins_log_entry
2989 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STUD_SCA_INACTIVE');
2990 FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
2991 v_log_text := FND_MESSAGE.GET;
2992 FND_FILE.PUT_LINE(FND_FILE.LOG,v_log_text);
2993
2994 Igs_Ge_Gen_003.genp_ins_log_entry(
2995 'SCA-ST-UPD',
2996 v_creation_dt,
2997 'INACTIVE',
2998 NULL,
2999 v_log_text);
3000 -- commit the changes made
3001 COMMIT;
3002
3003
3004 -- 4. Check for students who are inactive/lapsed but who now have an
3005 -- enrolled IGS_PS_UNIT attempt.
3006 v_updates_done := 0;
3007 FOR v_sca_inactive IN c_sca_inactive LOOP
3008 -- get the status
3009 IF enrpl_upd_get_status(
3010 v_sca_inactive.person_id,
3011 v_sca_inactive.course_cd,
3012 v_sca_inactive.course_attempt_status) = TRUE THEN
3013
3014 v_updates_done := v_updates_done + 1;
3015 END IF;
3016 END LOOP;
3017
3018 -- the number of updates done to student IGS_PS_COURSE attempt records
3019 -- have been summed and used in the call to genp_ins_log_entry
3020 FND_MESSAGE.SET_NAME('IGS','IGS_EN_STUD_SCA_ENROLLED');
3021 FND_MESSAGE.SET_TOKEN('UPDTDONE',TO_CHAR(v_updates_done));
3022 v_log_text := FND_MESSAGE.GET;
3023 FND_FILE.PUT_LINE(FND_FILE.LOG,v_log_text);
3024
3025 Igs_Ge_Gen_003.genp_ins_log_entry(
3026 'SCA-ST-UPD',
3027 v_creation_dt,
3028 'ENROLLED',
3029 NULL,
3030 v_log_text);
3031 -- commit the changes made
3032 COMMIT;
3033
3034
3035 -- 5. CHECK FOR FUTURE-DATES IGS_PS_COURSE DISCONTINUATIONS
3036 -- WHICH HAVE BEEN REACHED
3037 FOR v_sca_future IN c_sca_future LOOP
3038 -- update the student IGS_PS_COURSE attempt future
3039 -- dated discontinuation
3040 IF (enrp_upd_sca_discont(
3041 v_sca_future.person_id,
3042 v_sca_future.course_cd,
3043 v_sca_future.version_number,
3044 v_sca_future.course_attempt_status,
3045 v_sca_future.commencement_dt,
3046 v_sca_future.discontinued_dt,
3047 v_sca_future.discontinuation_reason_cd,
3048 v_message_name) = FALSE) THEN
3049 Igs_Ge_Gen_003.genp_ins_log_entry(
3050 'SCA-ST-UPD',
3051 v_creation_dt,
3052 'DISCONT|' ||
3053 TO_CHAR(v_sca_future.person_id) ||'|'||
3054 v_sca_future.course_cd ||'|'||
3055 TO_CHAR(v_sca_future.discontinued_dt,'DD/MM/YYYY') ||'|'||
3056 v_sca_future.discontinuation_reason_cd,
3057 v_message_name,
3058 NULL);
3059 ELSE
3060 Igs_Ge_Gen_003.genp_ins_log_entry(
3061 'SCA-ST-UPD',
3062 v_creation_dt,
3063 'DISCONT|' ||
3064 TO_CHAR(v_sca_future.person_id) ||'|'||
3065 v_sca_future.course_cd ||'|'||
3066 TO_CHAR(v_sca_future.discontinued_dt,'DD/MM/YYYY') ||'|'||
3067 v_sca_future.discontinuation_reason_cd,
3068 NULL,
3069 NULL);
3070 END IF;
3071 END LOOP;
3072 -- commit the changes made
3073 COMMIT;
3074
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 IF (c_sci_comm%ISOPEN) THEN
3078 CLOSE c_sci_comm;
3079 END IF;
3080 IF (c_sci_end%ISOPEN) THEN
3081 CLOSE c_sci_end;
3082 END IF;
3083 IF (c_scae%ISOPEN) THEN
3084 CLOSE c_scae;
3085 END IF;
3086 IF (c_sca_inactive%ISOPEN) THEN
3087 CLOSE c_sca_inactive;
3088 END IF;
3089 IF (c_sca_future%ISOPEN) THEN
3090 CLOSE c_sca_future;
3091 END IF;
3092 RAISE;
3093 END;
3094 EXCEPTION
3095 WHEN OTHERS THEN
3096 retcode:=2;
3097 ERRBUF := FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
3098 Igs_Ge_Msg_Stack.CONC_EXCEPTION_HNDL;
3099 END enrp_upd_sca_statusb;
3100
3101 FUNCTION Enrp_Upd_Sca_Urule(
3102 p_acad_cal_type IN VARCHAR2 ,
3103 p_acad_sequence_number IN NUMBER ,
3104 p_person_id IN NUMBER ,
3105 p_course_cd IN VARCHAR2 ,
3106 p_s_log_type IN VARCHAR2 ,
3107 p_creation_dt IN DATE )
3108 -------------------------------------------------------------------------------------------
3109 --Change History:
3110 --Who When What
3111 --kkillams 28-04-2003 Modified code in this function due to change in pk of student
3112 -- unit attempt w.r.t. bug number 2829262
3113 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
3114 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
3115 -------------------------------------------------------------------------------------------
3116 RETURN BOOLEAN AS
3117 gv_other_detail VARCHAR2(255);
3118 gv_cntr NUMBER;
3119 BEGIN
3120 DECLARE
3121 -- table to hold sua records which have been checked and cannot be changed
3122 TYPE r_checked_sua_typ IS RECORD(
3123 unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
3124 cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
3125 ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE);
3126 r_checked_sua r_checked_sua_typ;
3127 TYPE t_checked_sua_typ IS TABLE OF r_checked_sua%TYPE
3128 INDEX BY BINARY_INTEGER;
3129 t_checked_sua t_checked_sua_typ;
3130 t_checked_sua_blank t_checked_sua_typ;
3131 e_resource_busy_exception EXCEPTION;
3132 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
3133 cst_invalid VARCHAR2(10) := 'INVALID';
3134 cst_enrolled VARCHAR2(10) := 'ENROLLED';
3135 cst_attdate VARCHAR2(10) := 'ATT-DATE';
3136 cst_unit VARCHAR2(10) := 'UNIT';
3137 cst_changed VARCHAR2(10) := 'CHANGED';
3138 cst_attvalid VARCHAR2(10) := 'ATT-VALID';
3139 v_ret_val BOOLEAN := TRUE;
3140 v_dummy VARCHAR2(1);
3141 v_message_text VARCHAR2(1000);
3142 v_fail_type VARCHAR2(10);
3143 v_rec_found BOOLEAN;
3144 v_validation_error BOOLEAN;
3145 v_message_name VARCHAR2(30);
3146 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
3147 v_change_made BOOLEAN;
3148 PROCEDURE enrpl_upd_invalid_ua(
3149 p_acad_cal_type IGS_CA_INST.cal_type%TYPE,
3150 p_acad_sequence_number IGS_CA_INST.sequence_number%TYPE,
3151 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
3152 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
3153 p_s_log_type IGS_GE_S_LOG.s_log_type%TYPE,
3154 p_creation_dt IGS_GE_S_LOG.creation_dt%TYPE) AS
3155 BEGIN
3156 -- Re-check all INVALID IGS_PS_UNIT attempts and switch to ENROLLED if the rules
3157 -- are now passed. If an invalid IGS_PS_UNIT passes all rules the status is set
3158 -- to ENROLLED and the select is repeated. This repeats until no more invalid
3159 -- IGS_PS_UNIT attempts pass the IGS_PS_UNIT rules.
3160 DECLARE
3161 CURSOR c_sua_sca IS
3162 SELECT
3163 sua.person_id,
3164 sua.course_cd,
3165 -- Add the following one line of code. From Callista 2.0 18-May-2000
3166 sca.version_number sca_version_number,
3167 sua.unit_cd,
3168 sua.version_number,
3169 sua.cal_type,
3170 sua.ci_sequence_number,
3171 sua.ci_end_dt,
3172 sua.location_cd,
3173 sua.unit_class,
3174 sua.enrolled_dt,
3175 sua.uoo_id,
3176 Igs_En_Gen_004.enrp_get_rule_cutoff(
3177 sua.cal_type,
3178 sua.ci_sequence_number,
3179 cst_enrolled) enrolled_cutoff_date
3180 FROM IGS_EN_SU_ATTEMPT sua,
3181 IGS_EN_STDNT_PS_ATT sca
3182 WHERE sua.person_id = p_person_id AND
3183 sua.course_cd = p_course_cd AND
3184 sua.unit_attempt_status = cst_invalid AND
3185 sca.person_id = sua.person_id AND
3186 sca.course_cd = sua.course_cd;
3187
3188 lv_rule_failed IGS_EN_SU_ATTEMPT.FAILED_UNIT_RULE%TYPE;
3189
3190 BEGIN
3191 v_change_made := FALSE;
3192 FOR v_sua_sca_rec IN c_sua_sca LOOP
3193 v_rec_found := FALSE;
3194 -- see if the record has already been checked and failed
3195 IF gv_cntr <> 0 THEN
3196 FOR cntr IN 1..(gv_cntr) LOOP
3197 IF (t_checked_sua(cntr).unit_cd = v_sua_sca_rec.unit_cd AND
3198 t_checked_sua(cntr).cal_type = v_sua_sca_rec.cal_type AND
3199 t_checked_sua(cntr).ci_sequence_number
3200 = v_sua_sca_rec.ci_sequence_number) THEN
3201 v_rec_found := TRUE;
3202 END IF;
3203 END LOOP;
3204 END IF;
3205
3206 IF (v_rec_found = FALSE) THEN
3207 lv_rule_failed := NULL;
3208 IF Igs_Ru_Val_Unit_Rule.rulp_val_enrol_unit(
3209 p_person_id,
3210 p_course_cd,
3211 -- Add the following one line of code. From Callista 2.0 18-May-2000
3212 v_sua_sca_rec.sca_version_number,
3213 v_sua_sca_rec.unit_cd,
3214 v_sua_sca_rec.version_number,
3215 v_sua_sca_rec.cal_type,
3216 v_sua_sca_rec.ci_sequence_number,
3217 v_message_text
3218 ,v_sua_sca_rec.uoo_id,
3219 lv_rule_failed
3220 ) = TRUE THEN
3221 v_validation_error := FALSE;
3222 IF (v_sua_sca_rec.enrolled_cutoff_date IS NOT NULL AND
3223 SYSDATE > v_sua_sca_rec.enrolled_cutoff_date) THEN
3224 Igs_Ge_Gen_003.genp_ins_log_entry(
3225 p_s_log_type,
3226 p_creation_dt,
3227 cst_unit
3228 ||','||cst_attdate
3229 ||','||cst_enrolled
3230 ||','||TO_CHAR(p_person_id)
3231 ||','||p_course_cd
3232 ||','||v_sua_sca_rec.unit_cd
3233 ||','||v_sua_sca_rec.cal_type
3234 ||','||TO_CHAR(v_sua_sca_rec.ci_sequence_number),
3235 'IGS_EN_UA_CHGST_ENROLLED',
3236 NULL);
3237 v_ret_val := FALSE;
3238 v_validation_error := TRUE;
3239 ELSE
3240 IF Igs_En_Val_Sua.enrp_val_sua_cnfrm(
3241 v_sua_sca_rec.person_id,
3242 v_sua_sca_rec.course_cd,
3243 v_sua_sca_rec.unit_cd,
3244 v_sua_sca_rec.version_number,
3245 v_sua_sca_rec.cal_type,
3246 v_sua_sca_rec.ci_sequence_number,
3247 v_sua_sca_rec.ci_end_dt,
3248 v_sua_sca_rec.location_cd,
3249 v_sua_sca_rec.unit_class,
3250 v_sua_sca_rec.enrolled_dt,
3251 v_fail_type,
3252 v_message_name) = FALSE THEN
3253 Igs_Ge_Gen_003.genp_ins_log_entry (
3254 p_s_log_type,
3255 p_creation_dt,
3256 cst_unit
3257 ||','||cst_attvalid
3258 ||','||cst_enrolled
3259 ||','||TO_CHAR(p_person_id)
3260 ||','||p_course_cd
3261 ||','||v_sua_sca_rec.unit_cd
3262 ||','||v_sua_sca_rec.cal_type
3263 ||','||TO_CHAR(v_sua_sca_rec.ci_sequence_number),
3264 v_message_name,
3265 NULL);
3266 v_ret_val := FALSE;
3267 v_validation_error := TRUE;
3268 ELSE
3269 DECLARE
3270 -- update igs_en_su_attempt
3271 CURSOR c_assu IS
3272 SELECT ROWID,
3273 IGS_EN_SU_ATTEMPT.*
3274 FROM IGS_EN_SU_ATTEMPT
3275 WHERE person_id = v_sua_sca_rec.person_id AND
3276 course_cd = v_sua_sca_rec.course_cd AND
3277 uoo_id = v_sua_sca_rec.uoo_id
3278 FOR UPDATE NOWAIT;
3279 -- Having gotten the lock, update the record.
3280 BEGIN
3281 FOR c_assu_rec IN c_assu LOOP
3282 -- Call the API to update the student unit attempt. This API is a
3283 -- wrapper to the update row of the TBH.
3284 igs_en_sua_api.update_unit_attempt(
3285 x_rowid => c_assu_rec.rowid,
3286 x_person_id => c_assu_rec.person_id,
3287 x_course_cd => c_assu_rec.course_cd ,
3288 x_unit_cd => c_assu_rec.unit_cd,
3289 x_cal_type => c_assu_rec.cal_type,
3290 x_ci_sequence_number => c_assu_rec.ci_sequence_number ,
3291 x_version_number => c_assu_rec.version_number ,
3292 x_location_cd => c_assu_rec.location_cd,
3293 x_unit_class => c_assu_rec.unit_class ,
3294 x_ci_start_dt => c_assu_rec.ci_start_dt,
3295 x_ci_end_dt => c_assu_rec.ci_end_dt,
3296 x_uoo_id => c_assu_rec.uoo_id ,
3297 x_enrolled_dt => c_assu_rec.enrolled_dt,
3298 x_unit_attempt_status => cst_enrolled,
3299 x_administrative_unit_status => c_assu_rec.administrative_unit_status,
3300 x_administrative_priority => c_assu_rec.administrative_priority,
3301 x_discontinued_dt => c_assu_rec.discontinued_dt,
3302 x_dcnt_reason_cd => c_assu_rec.dcnt_reason_cd,
3303 x_rule_waived_dt => c_assu_rec.rule_waived_dt ,
3304 x_rule_waived_person_id => c_assu_rec.rule_waived_person_id ,
3305 x_no_assessment_ind => c_assu_rec.no_assessment_ind,
3306 x_sup_unit_cd => c_assu_rec.sup_unit_cd ,
3307 x_sup_version_number => c_assu_rec.sup_version_number,
3308 x_exam_location_cd => c_assu_rec.exam_location_cd,
3309 x_alternative_title => c_assu_rec.alternative_title,
3310 x_override_enrolled_cp => c_assu_rec.override_enrolled_cp,
3311 x_override_eftsu => c_assu_rec.override_eftsu ,
3312 x_override_achievable_cp => c_assu_rec.override_achievable_cp,
3313 x_override_outcome_due_dt => c_assu_rec.override_outcome_due_dt,
3314 x_override_credit_reason => c_assu_rec.override_credit_reason,
3315 x_waitlist_dt => c_assu_rec.waitlist_dt,
3316 x_mode => 'R',
3317 -- Added as part of Enroll Process build - amuthu
3318 x_gs_version_number => c_assu_rec.gs_version_number,
3319 x_enr_method_type => c_assu_rec.enr_method_type,
3320 x_failed_unit_rule => NULL, -- since the rule has succeeded now
3321 x_cart => c_assu_rec.cart,
3322 x_rsv_seat_ext_id => c_assu_rec.RSV_SEAT_EXT_ID,
3323 x_org_unit_cd => c_assu_rec.org_unit_cd,
3324 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3325 x_session_id => c_assu_rec.session_id,
3326 -- Added the column grading schema as a part of the bug 2037897. - aiyer
3327 x_grading_schema_code => c_assu_rec.grading_schema_code,
3328 x_deg_aud_detail_id => c_assu_rec.deg_aud_detail_id,
3329 x_subtitle => c_assu_rec.subtitle,
3330 x_student_career_transcript => c_assu_rec.student_career_transcript,
3331 x_student_career_statistics => c_assu_rec.student_career_statistics,
3332 x_attribute_category => c_assu_rec.attribute_category,
3333 x_attribute1 => c_assu_rec.attribute1,
3334 x_attribute2 => c_assu_rec.attribute2,
3335 x_attribute3 => c_assu_rec.attribute3,
3336 x_attribute4 => c_assu_rec.attribute4,
3337 x_attribute5 => c_assu_rec.attribute5,
3338 x_attribute6 => c_assu_rec.attribute6,
3339 x_attribute7 => c_assu_rec.attribute7,
3340 x_attribute8 => c_assu_rec.attribute8,
3341 x_attribute9 => c_assu_rec.attribute9,
3342 x_attribute10 => c_assu_rec.attribute10,
3343 x_attribute11 => c_assu_rec.attribute11,
3344 x_attribute12 => c_assu_rec.attribute12,
3345 x_attribute13 => c_assu_rec.attribute13,
3346 x_attribute14 => c_assu_rec.attribute14,
3347 x_attribute15 => c_assu_rec.attribute15,
3348 x_attribute16 => c_assu_rec.attribute16,
3349 x_attribute17 => c_assu_rec.attribute17,
3350 x_attribute18 => c_assu_rec.attribute18,
3351 x_attribute19 => c_assu_rec.attribute19,
3352 x_attribute20 => c_assu_rec.attribute20,
3353 x_waitlist_manual_ind => c_assu_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
3354 x_wlst_priority_weight_num => c_assu_rec.wlst_priority_weight_num,
3355 x_wlst_preference_weight_num => c_assu_rec.wlst_preference_weight_num,
3356 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
3357 x_core_indicator_code => c_assu_rec.core_indicator_code
3358 );
3359 END LOOP;
3360 END;
3361 Igs_Ge_Gen_003.genp_ins_log_entry (
3362 p_s_log_type,
3363 p_creation_dt,
3364 cst_unit
3365 ||','||cst_changed
3366 ||','||cst_enrolled
3367 ||','||TO_CHAR(p_person_id)
3368 ||','||p_course_cd
3369 ||','||v_sua_sca_rec.unit_cd
3370 ||','||v_sua_sca_rec.cal_type
3371 ||','||TO_CHAR(v_sua_sca_rec.ci_sequence_number),
3372 'IGS_EN_UA_STALT_INVALID_ENROL',
3373 NULL);
3374 v_ret_val := FALSE;
3375 v_change_made := TRUE;
3376 END IF; -- IGS_EN_VAL_SUA.enrp_val_sua_cnfrm
3377 END IF; --(c_sua_sca_rec.enrolled_cutoff_date IS NOT NULL AND
3378 -- SYSDATE > c_sua_sca_rec.enrolled_cutoff_date)
3379 IF v_validation_error = TRUE THEN
3380 -- no change done
3381 -- add the record to the table
3382 gv_cntr := gv_cntr+1;
3383 t_checked_sua(gv_cntr).unit_cd := v_sua_sca_rec.unit_cd;
3384 t_checked_sua(gv_cntr).cal_type := v_sua_sca_rec.cal_type;
3385 t_checked_sua(gv_cntr).ci_sequence_number
3386 := v_sua_sca_rec.ci_sequence_number;
3387 ELSE
3388 -- change was done so exit and call again
3389 EXIT;
3390 END IF; -- v_validation_error = TRUE
3391 END IF; -- IGS_RU_VAL_UNIT_RULE.rulp_val_enrol_unit
3392 END IF; -- (v_rec_found = FALSE)
3393 END LOOP;
3394 -- if a change was made call the procedure again
3395 IF v_change_made = TRUE THEN
3396 enrpl_upd_invalid_ua(
3397 p_acad_cal_type,
3398 p_acad_sequence_number,
3399 p_person_id,
3400 p_course_cd,
3401 p_s_log_type,
3402 p_creation_dt);
3403 END IF;/*
3404 EXCEPTION
3405 WHEN e_resource_busy_exception THEN
3406 IF (c_sua_sca%ISOPEN) THEN
3407 CLOSE c_sua_sca;
3408 END IF;
3409 RAISE;
3410 WHEN OTHERS THEN
3411 IF (c_sua_sca%ISOPEN) THEN
3412 CLOSE c_sua_sca;
3413 END IF;
3414 RAISE;*/
3415 END;
3416 /*
3417 EXCEPTION
3418 WHEN OTHERS THEN
3419 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
3420 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrpl_upd_invalid_ua');
3421 Igs_Ge_Msg_Stack.ADD;
3422 App_Exception.Raise_Exception;*/
3423 END enrpl_upd_invalid_ua;
3424
3425 PROCEDURE enrpl_upd_enrolled_ua(
3426 p_acad_cal_type IGS_CA_INST.cal_type%TYPE,
3427 p_acad_sequence_number IGS_CA_INST.sequence_number%TYPE,
3428 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
3429 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
3430 p_s_log_type IGS_GE_S_LOG.s_log_type%TYPE,
3431 p_creation_dt IGS_GE_S_LOG.creation_dt%TYPE) AS
3432 -------------------------------------------------------------------------------------------
3433 -- Check all ENROLLED IGS_PS_UNIT attempt IGS_PS_UNIT rules. If a IGS_RU_RULE is failed the
3434 -- status is set to INVALID and the select is repeated. This process
3435 -- repeats until no further units fail rules.
3436 --Change History:
3437 --Who When What
3438 --kkillams 28-04-2003 Modified code in this function due to change in pk of student
3439 -- unit attempt w.r.t. bug number 2829262
3440 -------------------------------------------------------------------------------------------
3441 BEGIN
3442 DECLARE
3443 -- Add the following one line of code. From Callista 2.0 18-May-2000
3444 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
3445 --
3446 CURSOR c_sua IS
3447 SELECT sua.person_id,
3448 sua.course_cd,
3449 sua.unit_cd,
3450 sua.version_number,
3451 sua.cal_type,
3452 sua.ci_sequence_number,
3453 sua.uoo_id,
3454 Igs_En_Gen_004.enrp_get_rule_cutoff(
3455 sua.cal_type,
3456 sua.ci_sequence_number,
3457 cst_invalid) invalid_cutoff_date
3458 FROM IGS_EN_SU_ATTEMPT sua
3459 WHERE sua.person_id = p_person_id AND
3460 sua.course_cd = p_course_cd AND
3461 sua.unit_attempt_status = cst_enrolled AND
3462 sua.rule_waived_dt IS NULL;
3463 -- Add the following five lines of code. From Callista 2.0 18-May-2000
3464 CURSOR c_sca IS
3465 SELECT sca.version_number
3466 FROM IGS_EN_STDNT_PS_ATT sca
3467 WHERE sca.person_id = p_person_id AND
3468 sca.course_cd = p_course_cd;
3469
3470 lv_rule_failed VARCHAR2(30);
3471 --
3472 BEGIN
3473 v_change_made := FALSE;
3474 -- Add the following nine lines of code. From Callista 2.0 18-May-2000
3475 -- Determine the version number of the course to be used in the call to
3476 -- rulp_val_enrol_unit.
3477 OPEN c_sca;
3478 FETCH c_sca INTO v_sca_version_number;
3479 IF c_sca%NOTFOUND THEN
3480 CLOSE c_sca;
3481 RAISE NO_DATA_FOUND;
3482 END IF;
3483 CLOSE c_sca;
3484 --
3485 FOR v_sua_rec IN c_sua LOOP
3486 v_rec_found := FALSE;
3487 IF gv_cntr <> 0 THEN
3488 -- check if the record has already been checked and failed
3489 FOR cntr IN 1..gv_cntr LOOP
3490 IF (t_checked_sua(cntr).unit_cd = v_sua_rec.unit_cd AND
3491 t_checked_sua(cntr).cal_type = v_sua_rec.cal_type AND
3492 t_checked_sua(cntr).ci_sequence_number
3493 = v_sua_rec.ci_sequence_number) THEN
3494 v_rec_found := TRUE;
3495 END IF;
3496 END LOOP;
3497 END IF;
3498
3499 IF (v_rec_found = FALSE) THEN
3500 lv_rule_failed := NULL;
3501 IF Igs_Ru_Val_Unit_Rule.rulp_val_enrol_unit(
3502 p_person_id,
3503 p_course_cd,
3504 -- Add the following one line of code. From Callista 2.0 18-May-2000
3505 v_sca_version_number,
3506 --
3507 v_sua_rec.unit_cd,
3508 v_sua_rec.version_number,
3509 v_sua_rec.cal_type,
3510 v_sua_rec.ci_sequence_number,
3511 v_message_text
3512 ,v_sua_rec.uoo_id,
3513 lv_rule_failed
3514 ) = FALSE THEN
3515 IF (v_sua_rec.invalid_cutoff_date IS NOT NULL AND
3516 SYSDATE > v_sua_rec.invalid_cutoff_date) THEN
3517 Igs_Ge_Gen_003.genp_ins_log_entry(
3518 p_s_log_type,
3519 p_creation_dt,
3520 cst_unit
3521 ||','||cst_attdate
3522 ||','||cst_invalid
3523 ||','||TO_CHAR(p_person_id)
3524 ||','||p_course_cd
3525 ||','||v_sua_rec.unit_cd
3526 ||','||v_sua_rec.cal_type
3527 ||','||TO_CHAR(v_sua_rec.ci_sequence_number),
3528 'IGS_EN_UA_FAILS_ST_INVALID',
3529 v_message_text);
3530 v_ret_val := FALSE;
3531 -- no change done
3532 -- so add the record to the table
3533 gv_cntr := gv_cntr+1;
3534 t_checked_sua(gv_cntr).unit_cd := v_sua_rec.unit_cd;
3535 t_checked_sua(gv_cntr).cal_type := v_sua_rec.cal_type;
3536 t_checked_sua(gv_cntr).ci_sequence_number
3537 := v_sua_rec.ci_sequence_number;
3538 ELSE
3539 -- set the IGS_EN_SU_ATTEMPT to invalid as rules are being breached
3540 -- update IGS_EN_SU_ATTEMPT
3541 DECLARE
3542 CURSOR c_suatt IS
3543 SELECT ROWID,
3544 IGS_EN_SU_ATTEMPT.*
3545 FROM IGS_EN_SU_ATTEMPT
3546 WHERE person_id = v_sua_rec.person_id AND
3547 course_cd = v_sua_rec.course_cd AND
3548 uoo_id = v_sua_rec.uoo_id
3549 FOR UPDATE NOWAIT;
3550 -- Having gotten the lock, update the record.
3551 BEGIN
3552 FOR c_suatt_rec IN c_suatt LOOP
3553 -- Call the API to update the student unit attempt. This API is a
3554 -- wrapper to the update row of the TBH.
3555 igs_en_sua_api.update_unit_attempt(
3556 X_ROWID => c_suatt_rec.ROWID,
3557 X_PERSON_ID => c_suatt_rec.PERSON_ID,
3558 X_COURSE_CD => c_suatt_rec.COURSE_CD ,
3559 X_UNIT_CD => c_suatt_rec.UNIT_CD,
3560 X_CAL_TYPE => c_suatt_rec.CAL_TYPE,
3561 X_CI_SEQUENCE_NUMBER => c_suatt_rec.CI_SEQUENCE_NUMBER ,
3562 X_VERSION_NUMBER => c_suatt_rec.VERSION_NUMBER ,
3563 X_LOCATION_CD => c_suatt_rec.LOCATION_CD,
3564 X_UNIT_CLASS => c_suatt_rec.UNIT_CLASS ,
3565 X_CI_START_DT => c_suatt_rec.CI_START_DT,
3566 X_CI_END_DT => c_suatt_rec.CI_END_DT,
3567 X_UOO_ID => c_suatt_rec.UOO_ID ,
3568 X_ENROLLED_DT => c_suatt_rec.ENROLLED_DT,
3569 X_UNIT_ATTEMPT_STATUS => cst_invalid,
3570 X_ADMINISTRATIVE_UNIT_STATUS => c_suatt_rec.administrative_unit_status,
3571 X_ADMINISTRATIVE_PRIORITY => c_suatt_rec.administrative_PRIORITY,
3572 X_DISCONTINUED_DT => c_suatt_rec.discontinued_dt,
3573 X_DCNT_REASON_CD => c_suatt_rec.DCNT_REASON_CD ,
3574 X_RULE_WAIVED_DT => c_suatt_rec.RULE_WAIVED_DT ,
3575 X_RULE_WAIVED_PERSON_ID => c_suatt_rec.RULE_WAIVED_PERSON_ID ,
3576 X_NO_ASSESSMENT_IND => c_suatt_rec.NO_ASSESSMENT_IND,
3577 X_SUP_UNIT_CD => c_suatt_rec.SUP_UNIT_CD ,
3578 X_SUP_VERSION_NUMBER => c_suatt_rec.SUP_VERSION_NUMBER,
3579 X_EXAM_LOCATION_CD => c_suatt_rec.EXAM_LOCATION_CD,
3580 X_ALTERNATIVE_TITLE => c_suatt_rec.ALTERNATIVE_TITLE,
3581 X_OVERRIDE_ENROLLED_CP => c_suatt_rec.OVERRIDE_ENROLLED_CP,
3582 X_OVERRIDE_EFTSU => c_suatt_rec.OVERRIDE_EFTSU ,
3583 X_OVERRIDE_ACHIEVABLE_CP => c_suatt_rec.OVERRIDE_ACHIEVABLE_CP,
3584 X_OVERRIDE_OUTCOME_DUE_DT => c_suatt_rec.OVERRIDE_OUTCOME_DUE_DT,
3585 X_OVERRIDE_CREDIT_REASON => c_suatt_rec.OVERRIDE_CREDIT_REASON,
3586 X_WAITLIST_DT => c_suatt_rec.waitlist_dt,
3587 X_MODE => 'R',
3588 -- Added as part of Enroll Process build - amuthu
3589 X_GS_VERSION_NUMBER => c_suatt_rec.gs_version_number,
3590 X_ENR_METHOD_TYPE => c_suatt_rec.enr_method_type,
3591 X_FAILED_UNIT_RULE => lv_rule_failed,
3592 X_CART => c_suatt_rec.cart,
3593 X_RSV_SEAT_EXT_ID => c_suatt_rec.RSV_SEAT_EXT_ID,
3594 X_ORG_UNIT_CD => c_suatt_rec.ORG_UNIT_CD,
3595 -- session_id added by Nishikant 28JAN2002 - Enh Bug#2172380.
3596 X_SESSION_ID => c_suatt_rec.session_id,
3597 -- Added the column grading schema as a part of the bug 2037897. - aiyer
3598 X_GRADING_SCHEMA_CODE => c_suatt_rec.grading_schema_code,
3599 X_DEG_AUD_DETAIL_ID => c_suatt_rec.deg_aud_detail_id,
3600 X_SUBTITLE => c_suatt_rec.subtitle,
3601 X_STUDENT_CAREER_TRANSCRIPT => c_suatt_rec.student_career_transcript,
3602 X_STUDENT_CAREER_STATISTICS => c_suatt_rec.student_career_statistics,
3603 X_ATTRIBUTE_CATEGORY => c_suatt_rec.attribute_category,
3604 X_ATTRIBUTE1 => c_suatt_rec.attribute1,
3605 X_ATTRIBUTE2 => c_suatt_rec.attribute2,
3606 X_ATTRIBUTE3 => c_suatt_rec.attribute3,
3607 X_ATTRIBUTE4 => c_suatt_rec.attribute4,
3608 X_ATTRIBUTE5 => c_suatt_rec.attribute5,
3609 X_ATTRIBUTE6 => c_suatt_rec.attribute6,
3610 X_ATTRIBUTE7 => c_suatt_rec.attribute7,
3611 X_ATTRIBUTE8 => c_suatt_rec.attribute8,
3612 X_ATTRIBUTE9 => c_suatt_rec.attribute9,
3613 X_ATTRIBUTE10 => c_suatt_rec.attribute10,
3614 X_ATTRIBUTE11 => c_suatt_rec.attribute11,
3615 X_ATTRIBUTE12 => c_suatt_rec.attribute12,
3616 X_ATTRIBUTE13 => c_suatt_rec.attribute13,
3617 X_ATTRIBUTE14 => c_suatt_rec.attribute14,
3618 X_ATTRIBUTE15 => c_suatt_rec.attribute15,
3619 X_ATTRIBUTE16 => c_suatt_rec.attribute16,
3620 X_ATTRIBUTE17 => c_suatt_rec.attribute17,
3621 X_ATTRIBUTE18 => c_suatt_rec.attribute18,
3622 X_ATTRIBUTE19 => c_suatt_rec.attribute19,
3623 X_ATTRIBUTE20 => c_suatt_rec.attribute20,
3624 X_WAITLIST_MANUAL_IND => c_suatt_rec.waitlist_manual_ind ,--Added by mesriniv for Bug 2554109 Mini Waitlist Build.
3625 X_WLST_PRIORITY_WEIGHT_NUM => c_suatt_rec.wlst_priority_weight_num,
3626 X_WLST_PREFERENCE_WEIGHT_NUM => c_suatt_rec.wlst_preference_weight_num,
3627 -- CORE_INDICATOR_CODE added by rvangala 07-OCT-2003. Enh Bug# 3052432
3628 X_CORE_INDICATOR_CODE => c_suatt_rec.core_indicator_code
3629 );
3630 END LOOP;
3631 END;
3632 Igs_Ge_Gen_003.genp_ins_log_entry (
3633 p_s_log_type,
3634 p_creation_dt,
3635 cst_unit
3636 ||','||cst_changed
3637 ||','||cst_invalid
3638 ||','||TO_CHAR(p_person_id)
3639 ||','||p_course_cd
3640 ||','||v_sua_rec.unit_cd
3641 ||','||v_sua_rec.cal_type
3642 ||','||TO_CHAR(v_sua_rec.ci_sequence_number),
3643 'IGS_EN_UA_FAILES_STATUS_INVAL',
3644 v_message_text);
3645 v_ret_val := FALSE;
3646 -- change has been made so exit and call again
3647 v_change_made := TRUE;
3648 EXIT;
3649 END IF; -- (c_sua_rec.invalid_cutoff_date IS NOT NULL AND
3650 -- SYSDATE > c_sua_sca_rec.invalid_cutoff_date)
3651 END IF; -- IGS_RU_VAL_UNIT_RULE.rulp_val_enrol_unit
3652 END IF; -- (v_rec_found = FALSE)
3653 END LOOP;
3654 -- if a change was made call the procedure again.
3655 IF v_change_made = TRUE THEN
3656 enrpl_upd_enrolled_ua(
3657 p_acad_cal_type,
3658 p_acad_sequence_number,
3659 p_person_id,
3660 p_course_cd,
3661 p_s_log_type,
3662 p_creation_dt);
3663 END IF;
3664 EXCEPTION
3665 WHEN e_resource_busy_exception THEN
3666 IF (c_sua%ISOPEN) THEN
3667 CLOSE c_sua;
3668 END IF;
3669 RAISE;
3670 WHEN OTHERS THEN
3671 IF (c_sua%ISOPEN) THEN
3672 CLOSE c_sua;
3673 END IF;
3674 RAISE;
3675 END;
3676 EXCEPTION
3677 WHEN OTHERS THEN
3678 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
3679 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrpl_upd_enrolled_ua');
3680 Igs_Ge_Msg_Stack.ADD;
3681 App_Exception.Raise_Exception;
3682 END enrpl_upd_enrolled_ua;
3683 BEGIN
3684 -- enrp_upd_sca_urule
3685 -- This process validates the IGS_PS_UNIT rules for all units for a student IGS_PS_COURSE
3686 -- attempt within the nominated academic calendar instance.
3687 -- Rules are checked recursively until no more rules are failed or passed
3688 -- (where invalid). The outcome is ENROLLED or INVALID IGS_PS_UNIT attempts.
3689 -- Switching of the status is subject to the
3690 -- IGS_EN_CAL_CONF.enrolled_rule_cutoff_dt_alias and
3691 -- IGS_EN_CAL_CONF.invalid_rule_cutoff_dt_alias which are represented as
3692 -- IGS_CA_DA_INST records within the relevant teaching periods.
3693 -- All parameters are required, else checking and logging is not possible.
3694 IF( p_acad_cal_type IS NULL OR
3695 p_acad_sequence_number IS NULL OR
3696 p_person_id IS NULL OR
3697 p_course_cd IS NULL OR
3698 p_s_log_type IS NULL OR
3699 p_creation_dt IS NULL) THEN
3700 RETURN v_ret_val;
3701 END IF;
3702 -- Issue savepoint
3703 SAVEPOINT sp_upd_sua_status;
3704 gv_cntr := 0;
3705 t_checked_sua := t_checked_sua_blank;
3706 -- Re-check all INVALID IGS_PS_UNIT attempts and switch to ENROLLED if the rules
3707 -- are now passed. If an invalid IGS_PS_UNIT passes all rules the status is set
3708 -- to ENROLLED and the select is repeated(procedure calls itself). This
3709 -- repeats until no more invalid IGS_PS_UNIT attempts pass the IGS_PS_UNIT rules.
3710 enrpl_upd_invalid_ua(
3711 p_acad_cal_type,
3712 p_acad_sequence_number,
3713 p_person_id,
3714 p_course_cd,
3715 p_s_log_type,
3716 p_creation_dt);
3717 gv_cntr := 0;
3718 t_checked_sua := t_checked_sua_blank;
3719 -- Check all ENROLLED IGS_PS_UNIT attempt IGS_PS_UNIT rules. If a IGS_RU_RULE is failed the
3720 -- status is set to INVALID and the select is repeated (procedure calls
3721 -- itself). This process repeats until no further units fail rules.
3722 enrpl_upd_enrolled_ua(
3723 p_acad_cal_type,
3724 p_acad_sequence_number,
3725 p_person_id,
3726 p_course_cd,
3727 p_s_log_type,
3728 p_creation_dt);
3729 RETURN v_ret_val;
3730 EXCEPTION
3731 WHEN e_resource_busy_exception THEN
3732 -- exception is handled by calling routine
3733 ROLLBACK TO sp_upd_sua_status;
3734 RAISE;
3735 END;
3736 /*
3737 EXCEPTION
3738 WHEN OTHERS THEN
3739 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
3740 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrp_upd_sca_urule');
3741 Igs_Ge_Msg_Stack.ADD;
3742 App_Exception.Raise_Exception;
3743 */
3744 END enrp_upd_sca_urule;
3745
3746 FUNCTION Enrp_Upd_Scho_Tfn(
3747 p_person_id IN NUMBER ,
3748 p_course_cd IN VARCHAR2 ,
3749 p_start_dt IN DATE ,
3750 p_tax_file_number IN NUMBER ,
3751 p_message_name OUT NOCOPY VARCHAR2)
3752 RETURN BOOLEAN AS
3753 BEGIN
3754 DECLARE
3755 CURSOR c_scho(
3756 cp_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE) IS
3757 SELECT scho.tax_file_number
3758 FROM IGS_EN_STDNTPSHECSOP scho
3759 WHERE scho.person_id = cp_person_id AND
3760 scho.tax_file_number IS NOT NULL AND
3761 scho.tax_file_invalid_dt IS NULL
3762 ORDER BY scho.start_dt DESC;
3763 v_tax_file_number IGS_EN_STDNTPSHECSOP.tax_file_number%TYPE;
3764 v_scho_rec_found BOOLEAN;
3765 v_person_id IGS_EN_STDNTPSHECSOP.person_id%TYPE;
3766
3767 BEGIN
3768 -- Update IGS_EN_STDNTPSHECSOP.tax_file_number.
3769 p_message_name := NULL;
3770 v_scho_rec_found := FALSE;
3771 -- If the Tax File Number is set to 999999999 then a default value
3772 -- must be determined
3773 IF(p_tax_file_number = 999999999) THEN
3774 FOR v_scho_rec IN c_scho(
3775 p_person_id) LOOP
3776 v_scho_rec_found := TRUE;
3777 v_tax_file_number := v_scho_rec.tax_file_number;
3778 EXIT;
3779 END LOOP;
3780 IF(v_scho_rec_found = FALSE) THEN
3781 p_message_name := 'IGS_EN_DFLT_TAX_FILE_NUM';
3782 RETURN FALSE;
3783 END IF;
3784 ELSE
3785 v_tax_file_number := p_tax_file_number;
3786 END IF;
3787 -- Attempt to lock the record.
3788 -- Failure will fall through to the exception handler.
3789
3790 DECLARE
3791
3792 CURSOR c_hecs_opt IS
3793 SELECT scho.ROWID,
3794 scho.*
3795 FROM IGS_EN_STDNTPSHECSOP scho
3796 WHERE scho.person_id = p_person_id AND
3797 scho.course_cd = p_course_cd AND
3798 scho.start_dt = p_start_dt
3799 FOR UPDATE NOWAIT;
3800
3801
3802 BEGIN
3803
3804 FOR c_hecs_opt_rec IN c_hecs_opt LOOP
3805
3806 -- update IGS_PE_PERSON's tax file number
3807
3808 Igs_En_Stdntpshecsop_Pkg.UPDATE_ROW(
3809 X_ROWID => c_hecs_opt_rec.ROWID,
3810 X_PERSON_ID => c_hecs_opt_rec.PERSON_ID,
3811 X_COURSE_CD => c_hecs_opt_rec.COURSE_CD,
3812 X_START_DT => c_hecs_opt_rec.START_DT,
3813 X_END_DT => c_hecs_opt_rec.end_dt,
3814 X_HECS_PAYMENT_OPTION => c_hecs_opt_rec.HECS_PAYMENT_OPTION,
3815 X_DIFFERENTIAL_HECS_IND => c_hecs_opt_rec.DIFFERENTIAL_HECS_IND,
3816 X_DIFF_HECS_IND_UPDATE_WHO => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_WHO,
3817 X_DIFF_HECS_IND_UPDATE_ON => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_ON ,
3818 X_OUTSIDE_AUS_RES_IND => c_hecs_opt_rec.OUTSIDE_AUS_RES_IND,
3819 X_NZ_CITIZEN_IND => c_hecs_opt_rec.NZ_CITIZEN_IND,
3820 X_NZ_CITIZEN_LESS2YR_IND => c_hecs_opt_rec.NZ_CITIZEN_LESS2YR_IND,
3821 X_NZ_CITIZEN_NOT_RES_IND => c_hecs_opt_rec.NZ_CITIZEN_NOT_RES_IND,
3822 X_SAFETY_NET_IND => c_hecs_opt_rec.SAFETY_NET_IND,
3823 X_TAX_FILE_NUMBER => v_tax_file_number,
3824 X_TAX_FILE_NUMBER_COLLECTED_DT => c_hecs_opt_rec.TAX_FILE_NUMBER_COLLECTED_DT,
3825 X_TAX_FILE_INVALID_DT => c_hecs_opt_rec.TAX_FILE_INVALID_DT,
3826 X_TAX_FILE_CERTIFICATE_NUMBER => c_hecs_opt_rec.TAX_FILE_CERTIFICATE_NUMBER,
3827 X_DIFF_HECS_IND_UPDATE_COMMENT => c_hecs_opt_rec.DIFF_HECS_IND_UPDATE_COMMENTs,
3828 X_MODE => 'R'
3829 );
3830
3831 END LOOP;
3832
3833 END;
3834
3835 RETURN TRUE;
3836
3837 EXCEPTION
3838 WHEN OTHERS THEN
3839 IF SQLCODE = -54 THEN
3840 -- A record lock.
3841 p_message_name := 'IGS_EN_HECS_PRSN_LOCKED';
3842 RETURN FALSE;
3843 ELSE
3844 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
3845 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_012.enrp_upd_scho_tfn');
3846 Igs_Ge_Msg_Stack.ADD;
3847 App_Exception.Raise_Exception;
3848 END IF;
3849 END;
3850 END enrp_upd_scho_tfn;
3851
3852 END igs_en_gen_012;