1 PACKAGE BODY IGS_EN_GEN_001 AS
2 /* $Header: IGSEN01B.pls 120.1 2005/08/17 22:56:05 appldev ship $ */
3 /*
4 who when what
5 rvivekan 9-sep-2003 PSS integration build#3052433. modified behavior of
6 repeatable indicator in the igs_ps_unit_ver table
7 jbegum 25-Jun-2003 BUG#2930935
8 Modified local functions ENRP_CLC_SCA_PASS_CP
9 npalanis 8-may-2002 Bug - 2362467
10 The application id passed to function Check_HRMS_Installed
11 is changed to 800 because the application id for HRMS is 800
12 */
13
14 l_rowid VARCHAR2(25);
15 Function Check_HRMS_Installed
16 RETURN VARCHAR2 IS
17 L_VAR BOOLEAN;
18 L_INDUSTRY VARCHAR2(10);
19 L_STATUS VARCHAR2(10);
20 BEGIN
21 L_VAR := FND_INSTALLATION.GET(800,800,L_STATUS,L_INDUSTRY);
22 IF L_STATUS IS NOT NULL THEN
23 RETURN 'Y';
24 ELSE
25 RETURN 'N';
26 END IF;
27
28 END;
29
30
31 Procedure Enrp_Clc_Crrnt_Acad(
32 p_cal_type IN VARCHAR2 ,
33 p_effective_dt IN DATE ,
34 p_sequence_number OUT NOCOPY NUMBER )
35 AS
36 BEGIN
37 DECLARE
38 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
39 v_effect_enr_strt_dt_alias IGS_EN_CAL_CONF.effect_enr_strt_dt_alias%TYPE;
40 v_record_found BOOLEAN;
41 CURSOR c_s_enr_cal_conf IS
42 SELECT effect_enr_strt_dt_alias
43 FROM IGS_EN_CAL_CONF
44 WHERE s_control_num = 1;
45 CURSOR c_dai_v(cp_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
46 cp_dt_alias IGS_CA_DA_INST_V.dt_alias%TYPE,
47 cp_effective_dt DATE) IS
48 SELECT IGS_CA_DA_INST_V.alias_val,
49 IGS_CA_DA_INST_V.ci_sequence_number
50 FROM IGS_CA_DA_INST_V,
51 IGS_CA_INST,
52 IGS_CA_STAT
53 WHERE IGS_CA_DA_INST_V.cal_type = cp_cal_type AND
54 IGS_CA_DA_INST_V.dt_alias = cp_dt_alias AND
55 IGS_CA_DA_INST_V.alias_val <= cp_effective_dt AND
56 IGS_CA_DA_INST_V.cal_type = IGS_CA_INST.cal_type AND
57 IGS_CA_DA_INST_V.ci_sequence_number = IGS_CA_INST.sequence_number AND
58 IGS_CA_STAT.cal_status = IGS_CA_INST.cal_status AND
59 IGS_CA_STAT.s_cal_status <> cst_planned
60 ORDER BY alias_val DESC;
61 BEGIN
62 -- this module alculates the current instance of academic period calendar
63 -- for the nominated academic calendar type. This is determined by searching
64 -- for the ?effective enrolment start date alias? within the academic
65 -- calendar instance. The dt_alias to search for is located in the
66 -- IGS_EN_CAL_CONF.effect_enr_strt_dt_alias column. If no match is determinable
67 -- then the returned sequence number will be set to 0.
68 v_record_found := FALSE;
69 p_sequence_number := 0;
70 OPEN c_s_enr_cal_conf;
71 FETCH c_s_enr_cal_conf INTO v_effect_enr_strt_dt_alias;
72 IF (c_s_enr_cal_conf%NOTFOUND) THEN
73 CLOSE c_s_enr_cal_conf;
74 RETURN;
75 END IF;
76 CLOSE c_s_enr_cal_conf;
77 FOR c_dai_v_rec IN c_dai_v(
78 p_cal_type,
79 v_effect_enr_strt_dt_alias,
80 p_effective_dt)
81 LOOP
82 v_record_found := TRUE;
83 p_sequence_number := c_dai_v_rec.ci_sequence_number;
84 EXIT;
85 END LOOP;
86 IF(v_record_found = FALSE) THEN
87 p_sequence_number := 0;
88 END IF;
89 RETURN;
90 EXCEPTION
91 WHEN OTHERS THEN
92 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
93 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_clc_crrnt_acad');
94 IGS_GE_MSG_STACK.ADD;
95 App_Exception.Raise_Exception;
96 END;
97 END enrp_clc_crrnt_acad;
98
99
100 Procedure Enrp_Clc_Sca_Acad(
101 p_person_id IN NUMBER ,
102 p_course_cd IN VARCHAR2 ,
103 p_effective_dt IN DATE ,
104 p_cal_type OUT NOCOPY VARCHAR2 ,
105 p_sequence_number OUT NOCOPY NUMBER )
106 AS
107 BEGIN
108 DECLARE
109 v_cal_type IGS_CA_INST.cal_type%TYPE;
110 v_sequence_number NUMBER(6);
111 CURSOR c_sca(
112 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
113 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
114 SELECT IGS_EN_STDNT_PS_ATT.cal_type
115 FROM IGS_EN_STDNT_PS_ATT
116 WHERE person_id = cp_person_id AND
117 course_cd = cp_course_cd;
118 BEGIN
119 -- calculates the current academic period in which the nominated student IGS_PS_COURSE
120 -- attempt is enrolling as at the nominated date. The cal_type is selected
121 -- from the IGS_EN_STDNT_PS_ATT and passed to the ENRP_CLC_CRRNT_ACAD
122 -- routine to determine the appropriate instance of that cal_type.
123 OPEN c_sca(
124 p_person_id,
125 p_course_cd);
126 FETCH c_sca INTO v_cal_type;
127 IF(c_sca%NOTFOUND) THEN
128 p_cal_type := NULL;
129 p_sequence_number := 0;
130 RETURN;
131 END IF;
132 enrp_clc_crrnt_acad(v_cal_type,
133 p_effective_dt,
134 v_sequence_number);
135 IF(v_sequence_number = 0) THEN
136 p_cal_type := NULL;
137 p_sequence_number := 0;
138 ELSE
139 p_cal_type := v_cal_type;
140 p_sequence_number := v_sequence_number;
141 END IF;
142 RETURN;
143 EXCEPTION
144 WHEN OTHERS THEN
145 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
146 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_clc_sca_acad');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END;
150 END enrp_clc_sca_acad;
151
152
153 FUNCTION Enrp_Clc_Sca_Pass_Cp(
154 p_person_id IN NUMBER ,
155 p_course_cd IN VARCHAR2 ,
156 p_effective_dt IN DATE )
157 RETURN NUMBER AS
158 BEGIN
159 -------------------------------------------------------------------------------------------
160 -- enrp_clc_sca_pass_cp
161 -- Calculate the CP passed by a student in a nominated IGS_PS_COURSE attempt,
162 -- counting advanced stANDing which has been granted.
163 -- The p_effective_dt parameter will cause only outcomes / advanced stANDing
164 -- received prior to the date to be considered. This is required by
165 -- retrospective EFTSU calculations which use annual load structures, as they
166 -- need to estimate the load as at the time of the original EFTSU calculation.
167 -- It should be noted, that this routine IS IN termediary until the
168 -- Rules Sub-system is capable of calculating 'achievable' credit points
169 -- on the same basis, which is envisaged for 1.4.2 delivery.
170 --Change History:
171 --Who When What
172 --jbegum 25-jun-2003 Bug#2930935.Modified the cursor c_sua_uv
173 --kkillams 24-04-2003 Modified the c_sua_uv cursor and passing uoo_id to the
174 -- IGS_AS_GEN_003.assp_get_sua_outcome function w.r.t. bug number 2829262
175 --rvivekan 9-sep-2003 PSP integration build#3052433. modified behavior of
176 -- repeatable_ind in the igs_ps_unit_ver table
177
178 -------------------------------------------------------------------------------------------
179 DECLARE
180 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
181 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
182 cst_pass CONSTANT VARCHAR2(10) := 'PASS';
183 v_credit_point_total NUMBER;
184 v_advanced_standing NUMBER;
185 v_last_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE;
186 v_result_type IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE;
187 v_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE;
188 v_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
189 v_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
190 v_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
191 v_mark IGS_AS_SU_STMPTOUT.mark%TYPE;
192 v_origin_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
193
194 CURSOR c_sua_uv IS
195 SELECT sua.person_id,
196 sua.course_cd,
197 sua.unit_cd,
198 sua.cal_type,
199 sua.ci_sequence_number,
200 sua.unit_attempt_status,
201 sua.override_achievable_cp,
202 uv.repeatable_ind,
203 NVL(cps.achievable_credit_points,uv.achievable_credit_points) achievable_credit_points,
204 NVL(cps.enrolled_credit_points,uv.enrolled_credit_points) enrolled_credit_points,
205 sua.uoo_id
206 FROM IGS_EN_SU_ATTEMPT sua,
207 IGS_PS_UNIT_VER uv,
208 IGS_PS_USEC_CPS cps,
209 IGS_CA_INST ci
210 WHERE sua.person_id = p_person_id AND
211 sua.course_cd = p_course_cd AND
212 sua.unit_attempt_status IN(cst_completed,
213 cst_duplicate) AND
214 uv.unit_cd = sua.unit_cd AND
215 uv.version_number = sua.version_number AND
216 ci.cal_type = sua.cal_type AND
217 ci.sequence_number = sua.ci_sequence_number AND
218 sua.uoo_id = cps.uoo_id (+)
219 ORDER BY sua.unit_cd asc,
220 sua.ci_end_dt asc;
221 BEGIN
222 -- Set the initial values
223 v_credit_point_total := 0;
224 v_last_unit_cd := NULL;
225 FOR v_sua_uv_rec IN c_sua_uv LOOP
226 --If same as last IGS_PS_UNIT code AND not repeatable,then skip IGS_PS_UNIT;doesn't count.
227 IF v_last_unit_cd IS NOT NULL AND
228 v_sua_uv_rec.repeatable_ind = 'X' AND
229 v_last_unit_cd = v_sua_uv_rec.unit_cd THEN
230 -- The IGS_PS_UNIT has been attempted earlier AND was passed but it was not
231 -- a repeatable IGS_PS_UNIT,hence further attempts attain NO credit points.
232 NULL;
233 ELSE -- repeatable IGS_PS_UNIT
234 --Retrieve the outcome FROM the assessments tables
235 v_result_type := IGS_AS_GEN_003.assp_get_sua_outcome(
236 v_sua_uv_rec.person_id,
237 v_sua_uv_rec.course_cd,
238 v_sua_uv_rec.unit_cd,
239 v_sua_uv_rec.cal_type,
240 v_sua_uv_rec.ci_sequence_number,
241 v_sua_uv_rec.unit_attempt_status,
242 'Y',
243 v_outcome_dt,
244 v_grading_schema_cd,
245 v_gs_version_number,
246 v_grade,
247 v_mark,
248 v_origin_course_cd,
249 v_sua_uv_rec.uoo_id,
250 --added by LKAKI----
251 'N');
252 --Only consider outcomes before or on the effective date
253 IF v_result_type = cst_pass AND
254 (p_effective_dt IS NULL OR
255 TRUNC(v_outcome_dt) <= p_effective_dt) THEN
256 --Add passed grades to total
257 v_credit_point_total := v_credit_point_total +
258 NVL(v_sua_uv_rec.override_achievable_cp,
259 NVL(v_sua_uv_rec.achievable_credit_points,
260 v_sua_uv_rec.enrolled_credit_points));
261 --Set the last IGS_PS_UNIT processed
262 v_last_unit_cd := v_sua_uv_rec.unit_cd;
263 END IF;
264 END IF; -- repeateable IGS_PS_UNIT
265 END LOOP;
266 --Add advanced standing before or on the effective date.
267 v_advanced_standing := IGS_AV_GEN_001.advp_get_as_total(p_person_id,
268 p_course_cd,
269 p_effective_dt );
270 --add v_advanced_standing to v_credit_point_total
271 v_credit_point_total := v_credit_point_total + v_advanced_standing;
272 RETURN v_credit_point_total;
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF c_sua_uv%ISOPEN THEN
276 CLOSE c_sua_uv;
277 END IF;
278 RAISE;
279 END;
280 /*
281 EXCEPTION
282 WHEN OTHERS THEN
283 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
284 App_Exception.Raise_Exception;
285 */
286 END enrp_clc_sca_pass_cp;
287
288
289 PROCEDURE Enrp_Del_Suao_Discon(
290 p_person_id IN NUMBER ,
291 p_course_cd IN VARCHAR2 ,
292 p_unit_cd IN VARCHAR2 ,
293 p_cal_type IN VARCHAR2 ,
294 p_ci_sequence_number IN NUMBER ,
295 p_discontinued_dt IN DATE,
296 p_uoo_id IN NUMBER ) AS
297 -------------------------------------------------------------------------------------------
298 --Change History:
299 --Who When What
300 --kkillams 24-04-2003 New parameter p_uoo_id is added to the procedure and cursor
301 -- c_suao_find_details modified w.r.t. bug number 2829262
302 -------------------------------------------------------------------------------------------
303 BEGIN
304 DECLARE
305 --v_suao_rec IGS_AS_SU_STMPTOUT%ROWTYPE;
306 CURSOR c_suao_find_details
307 (cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
308 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
309 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE,
310 cp_discontinued_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE) IS
311 SELECT rowid
312 FROM IGS_AS_SU_STMPTOUT
313 WHERE person_id = cp_person_id AND
314 course_cd = cp_course_cd AND
315 uoo_id = cp_uoo_id AND
316 TRUNC(outcome_dt) = TRUNC(cp_discontinued_dt) AND
317 s_grade_creation_method_type = 'DISCONTIN';
318 BEGIN
319 -- This module deletes a student IGS_PS_UNIT attempt
320 -- outcome record when student IGS_PS_UNIT attempt has
321 -- removed their discontinued date.
322 OPEN c_suao_find_details(p_person_id,
323 p_course_cd,
324 p_uoo_id,
325 p_discontinued_dt);
326 FETCH c_suao_find_details INTO l_rowid;
327 -- check if a record has been found
328 -- if so, the record can be deleted.
329 IF (c_suao_find_details%FOUND) THEN
330 IGS_AS_SU_STMPTOUT_PKG.DELETE_ROW(l_rowid);
331 CLOSE c_suao_find_details;
332 ELSE
333 CLOSE c_suao_find_details;
334 END IF;
335 EXCEPTION
336 WHEN OTHERS THEN
337 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
338 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_del_suao_discon');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END;
342 END enrp_del_suao_discon;
343
344
345 FUNCTION Enrp_Del_Sua_Sut(
346 p_person_id IN NUMBER ,
347 p_course_cd IN VARCHAR2 ,
348 p_unit_cd IN VARCHAR2 ,
349 p_cal_type IN VARCHAR2 ,
350 p_ci_sequence_number IN NUMBER ,
351 p_unit_attempt_status IN VARCHAR2 ,
352 p_message_name OUT NOCOPY VARCHAR2,
353 p_uoo_id IN NUMBER)
354 RETURN BOOLEAN AS
355 -------------------------------------------------------------------------------------------
356 --Change History:
357 --Who When What
358 --kkillams 24-04-2003 New parameter p_uoo_id is added to the function.
359 -- Cursor c_sua_delete is modified w.r.t. bug number 2829262
360 -------------------------------------------------------------------------------------------
361 e_resource_busy_exception EXCEPTION;
362 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
363 BEGIN -- enrp_del_sua_sut
364 -- This module validates if a duplicate IGS_EN_SU_ATTEMPT can be deleted,
365 -- and deletes associated IGS_PS_STDNT_UNT_TRN detail.
366 DECLARE
367 cst_duplicate VARCHAR2(10) := 'DUPLICATE';
368 v_person_id IGS_PS_STDNT_UNT_TRN.person_id%TYPE;
369 -- cursor to locate duplicate in another IGS_PS_COURSE.
370 CURSOR c_sut_duplicate IS
371 SELECT person_id
372 FROM IGS_PS_STDNT_UNT_TRN sut
373 WHERE sut.person_id = p_person_id AND
374 sut.transfer_course_cd = p_course_cd AND
375 sut.uoo_id = p_uoo_id;
376 --cursor to acquire lock for delete
377 CURSOR c_sut_delete IS
378 SELECT rowid,person_id
379 FROM IGS_PS_STDNT_UNT_TRN sut
380 WHERE sut.person_id = p_person_id AND
381 sut.course_cd = p_course_cd AND
382 sut.uoo_id = p_uoo_id
383 FOR UPDATE OF sut.person_id NOWAIT;
384 BEGIN
385 IF p_unit_attempt_status = cst_duplicate THEN
386 -- Validate the duplicate student attempt is not a duplicate in another IGS_PS_COURSE
387 OPEN c_sut_duplicate;
388 FETCH c_sut_duplicate INTO v_person_id;
389 IF (c_sut_duplicate%FOUND) THEN
390 CLOSE c_sut_duplicate;
391 p_message_name := 'IGS_EN_DUPL_STUD_UNIT_ATTEMPT';
392 RETURN FALSE;
393 END IF;
394 CLOSE c_sut_duplicate;
395 FOR v_sut_delete_rec IN c_sut_delete LOOP
396 IGS_PS_STDNT_UNT_TRN_PKG.DELETE_ROW(v_sut_delete_rec.rowid);
397 END LOOP;
398 END IF;
399 p_message_name := Null;
400 RETURN TRUE;
401 EXCEPTION
402 -- If record cannot be locked for deletion
403 -- this exception was unable to be tested because only insertions and deletions
404 -- are applicable to the Student IGS_PS_UNIT Transfer table.
405 WHEN e_resource_busy_exception THEN
406 IF (c_sut_delete%ISOPEN) THEN
407 CLOSE c_sut_delete;
408 END IF;
409 p_message_name := 'IGS_EN_SU_TRANSFER_NOTDEL';
410 RETURN FALSE;
411 -- handling any other exception
412 WHEN OTHERS THEN
413 IF (c_sut_duplicate%ISOPEN) THEN
414 CLOSE c_sut_duplicate;
415 END IF;
416 IF (c_sut_delete%ISOPEN) THEN
417 CLOSE c_sut_delete;
418 END IF;
419 RAISE;
420 END;
421 EXCEPTION
422 WHEN OTHERS THEN
423 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
424 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_del_sua_sut');
425 IGS_GE_MSG_STACK.ADD;
426 App_Exception.Raise_Exception;
427 END enrp_del_sua_sut;
428
429
430 FUNCTION Enrp_Del_Sua_Trnsfr(
431 p_person_id IN NUMBER ,
432 p_course_cd IN VARCHAR2 ,
433 p_unit_cd IN VARCHAR2 ,
434 p_cal_type IN VARCHAR2 ,
435 p_ci_sequence_number IN NUMBER ,
436 p_message_name OUT NOCOPY VARCHAR2,
437 p_uoo_id IN NUMBER )
438 RETURN BOOLEAN AS
439 -------------------------------------------------------------------------------------------
440 --Change History:
441 --Who When What
442 --kkillams 24-04-2003 New parameter p_uoo_id is added to the function.
443 -- Cursor c_sua_delete is modified.
444 -- w.r.t. bug number 2829262
445 -------------------------------------------------------------------------------------------
446 BEGIN -- enrp_del_sua_trnsfr
447 -- This module deletes a transferred IGS_EN_SU_ATTEMPT record.
448 DECLARE
449 e_resource_busy_exception EXCEPTION;
450 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
451 -- cursor to lock records for delete
452 CURSOR c_sua_delete IS
453 SELECT rowid rowid1,person_id
454 FROM IGS_EN_SU_ATTEMPT sua
455 WHERE sua.person_id = p_person_id AND
456 sua.course_cd = p_course_cd AND
457 sua.uoo_id = p_uoo_id
458 FOR UPDATE OF sua.person_id NOWAIT;
459 BEGIN
460 p_message_name := null;
461 -- Check parameters
462 IF p_person_id IS NULL OR
463 p_course_cd IS NULL OR
464 p_unit_cd IS NULL OR
465 p_cal_type IS NULL OR
466 p_ci_sequence_number IS NULL OR
467 p_uoo_id IS NULL THEN
468 RETURN TRUE;
469 END IF;
470
471 FOR v_sua_delete_rec IN c_sua_delete
472 LOOP
473 -- Delete current record
474 IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW(v_sua_delete_rec.rowid1 );
475 END LOOP;
476 -- Record successfuly deleted
477 RETURN TRUE;
478 EXCEPTION
479 -- Record cannot be locked for deletion
480 WHEN e_resource_busy_exception THEN
481 IF (c_sua_delete%ISOPEN) THEN
482 CLOSE c_sua_delete;
483 END IF;
484 p_message_name := 'IGS_EN_TRNS_SUA_NOTDEL';
485 RETURN FALSE;
486 -- Any other exception.
487 WHEN OTHERS THEN
488 IF (c_sua_delete%ISOPEN) THEN
489 CLOSE c_sua_delete;
490 END IF;
491 RAISE;
492 END;
493 EXCEPTION
494 WHEN OTHERS THEN
495 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
496 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_del_sua_trnsfr');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 END enrp_del_sua_trnsfr;
500
501
502 Function Enrp_Del_Susa_Hist(
503 p_person_id IN NUMBER ,
504 p_course_cd IN VARCHAR2 ,
505 p_unit_set_cd IN VARCHAR2 ,
506 p_sequence_number IN NUMBER ,
507 p_message_name out NOCOPY Varchar2 )
508 RETURN BOOLEAN AS
509
510 resource_busy_exception EXCEPTION;
511 PRAGMA EXCEPTION_INIT(resource_busy_exception, -54);
512 BEGIN -- enrp_del_susa_hist
513 -- This module will delete the history records associated with a
514 -- IGS_AS_SU_SETATMPT record.
515 DECLARE
516 v_person_id IGS_AS_SU_SETATMPT_H.person_id%TYPE;
517 -- Delete student_unit_set_attempt_hist record, returning false if
518 -- a lock exists. Select FOR UPDATE NOWAIT to acquire locks.
519 CURSOR c_susah IS
520 SELECT susah.rowid,
521 susah.person_id person_id
522 FROM IGS_AS_SU_SETATMPT_H susah
523 WHERE susah.person_id = p_person_id AND
524 susah.course_cd = p_course_cd AND
525 susah.unit_set_cd = p_unit_set_cd AND
526 susah.sequence_number = p_sequence_number
527 FOR UPDATE OF susah.person_id NOWAIT;
528 BEGIN
529 -- Set the default message number
530 p_message_name := null;
531 FOR v_susah_rec IN c_susah LOOP
532 -- Delete the current record.
533
534 IGS_AS_SU_SETATMPT_H_PKG.DELETE_ROW(
535 v_susah_rec.rowid
536 );
537
538 END LOOP;
539 -- If processing successful then
540 RETURN TRUE;
541 EXCEPTION
542 -- If an exception raised indicating a lock on any of the records in the
543 -- select set, then want to handle the exception by returning false and
544 -- an error message from this routine.
545 WHEN resource_busy_exception THEN
546 IF (c_susah%ISOPEN) THEN
547 CLOSE c_susah;
548 END IF;
549
550 p_message_name := 'IGS_EN_UNABLE_NOTDEL_SUA_LOCK';
551 RETURN FALSE;
552 WHEN OTHERS THEN
553 IF (c_susah%NOTFOUND) THEN
554 CLOSE c_susah;
555 END IF;
556 RAISE;
557 END;
558 EXCEPTION
559 WHEN OTHERS THEN
560 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
561 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_del_susa_hist');
562 IGS_GE_MSG_STACK.ADD;
563 App_Exception.Raise_Exception;
564 END enrp_del_susa_hist ;
565
566
567 Function Enrp_Del_Susa_Trnsfr(
568 p_person_id IN NUMBER ,
569 p_course_cd IN VARCHAR2 ,
570 p_unit_set_cd IN VARCHAR2 ,
571 p_us_version_number IN NUMBER ,
572 p_message_name out NOCOPY Varchar2 )
573 RETURN BOOLEAN AS
574
575 BEGIN -- enrp_del_susa_trnsfr
576 -- This module deletes a transferred IGS_AS_SU_SETATMPT
577 -- record.
578 DECLARE
579 e_resource_busy EXCEPTION;
580 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
581 CURSOR c_susa_delete IS
582 SELECT rowid,person_id
583 FROM IGS_AS_SU_SETATMPT susa
584 WHERE susa.person_id = p_person_id AND
585 susa.course_cd = p_course_cd AND
586 susa.unit_set_cd = p_unit_set_cd AND
587 susa.us_version_number = p_us_version_number
588 FOR UPDATE OF susa.person_id NOWAIT;
589 BEGIN
590 p_message_name := null;
591 -- Check parameters
592 IF p_person_id IS NULL OR
593 p_course_cd IS NULL OR
594 p_unit_set_cd IS NULL OR
595 p_us_version_number IS NULL THEN
596 RETURN TRUE;
597 END IF;
598 FOR v_susa_delete_rec IN c_susa_delete LOOP
599 -- Delete current record
600
601 IGS_AS_SU_SETATMPT_PKG.DELETE_ROW(
602 v_susa_delete_rec.rowid
603 );
604
605 END LOOP;
606 -- Record successfuly deleted
607 RETURN TRUE;
608 EXCEPTION
609 -- Record cannot be locked for deletion
610 WHEN e_resource_busy THEN
611 IF (c_susa_delete%ISOPEN) THEN
612 CLOSE c_susa_delete;
613 END IF;
614 p_message_name := 'IGS_EN_TRNS_SUA_NOTDEL_UPD';
615 RETURN FALSE;
616 -- Any other exception.
617 WHEN OTHERS THEN
618 IF (c_susa_delete%ISOPEN) THEN
619 CLOSE c_susa_delete;
620 END IF;
621 RAISE;
622 END;
623 EXCEPTION
624 WHEN OTHERS THEN
625 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
626 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_001.enrp_del_susa_trnsfr');
627 IGS_GE_MSG_STACK.ADD;
628 App_Exception.Raise_Exception;
629 END enrp_del_susa_trnsfr;
630
631 END IGS_EN_GEN_001;
[Home] [Help]