1 PACKAGE BODY IGS_AS_VAL_SCAP AS
2 /* $Header: IGSAS28B.pls 115.8 2003/05/27 18:45:13 anilk ship $ */
3
4 --
5 -- Validate special consideration category closed indicator.
6 FUNCTION assp_val_spcc_closed(
7 p_spcl_consideration_cat IGS_AS_SPCL_CONS_CAT.spcl_consideration_cat%TYPE ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN IS
10 gv_other_detail VARCHAR2(255);
11 BEGIN -- assp_val_spcc_closed
12 -- Validate the special consideration category closed indicator.
13 DECLARE
14 v_closed_ind IGS_AS_SPCL_CONS_CAT.closed_ind%TYPE;
15 CURSOR c_spcc IS
16 SELECT closed_ind
17 FROM IGS_AS_SPCL_CONS_CAT
18 WHERE spcl_consideration_cat = p_spcl_consideration_cat;
19 BEGIN
20 -- Set the default message number
21 p_message_name := null;
22 OPEN c_spcc;
23 FETCH c_spcc INTO v_closed_ind;
24 IF (c_spcc%NOTFOUND) THEN
25 CLOSE c_spcc;
26 RETURN TRUE;
27 ELSIF (v_closed_ind = 'Y') THEN
28 CLOSE c_spcc;
29 p_message_name := 'IGS_AS_SPLCONS_CAT_CLOSED';
30 RETURN FALSE;
31 END IF;
32 CLOSE c_spcc;
33 RETURN TRUE;
34 END;
35 EXCEPTION
36 WHEN OTHERS THEN
37 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
38 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SCAP.assp_val_spcc_closed');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 END assp_val_spcc_closed;
42 --
43 -- Validate special consideration outcome closed indicator.
44 FUNCTION assp_val_spco_closed(
45 p_spcl_consideration_outcome IGS_AS_SPCL_CONS_OUT.spcl_consideration_outcome%TYPE ,
46 p_message_name OUT NOCOPY VARCHAR2 )
47 RETURN BOOLEAN IS
48 gv_other_detail VARCHAR2(255);
49 BEGIN -- assp_val_spco_closed
50 -- Validate the special conderation outcome closed indicator
51 -- also caters for the sought outcome closed indicator as sought outcomes
52 -- exist in
53 -- the same table as special consideration outcomes (ie; the actual outcome of
54 -- an application) and are simply a subset of the special consideration
55 -- outcomes.
56 DECLARE
57 v_closed_ind IGS_AS_SPCL_CONS_OUT.closed_ind%TYPE;
58 CURSOR c_spco IS
59 SELECT closed_ind
60 FROM IGS_AS_SPCL_CONS_OUT
61 WHERE spcl_consideration_outcome = p_spcl_consideration_outcome;
62 BEGIN
63 -- Set the default message number
64 p_message_name := null;
65 OPEN c_spco;
66 FETCH c_spco INTO v_closed_ind;
67 IF (c_spco%NOTFOUND) THEN
68 CLOSE c_spco;
69 RETURN TRUE;
70 ELSIF (v_closed_ind = 'Y') THEN
71 CLOSE c_spco;
72 p_message_name := 'IGS_AS_SPLCONS_OUTCOME_CLOSED';
73 RETURN FALSE;
74 END IF;
75 CLOSE c_spco;
76 RETURN TRUE;
77 END;
78 EXCEPTION
79 WHEN OTHERS THEN
80 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
81 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SCAP.assp_val_spco_closed');
82 IGS_GE_MSG_STACK.ADD;
83 App_Exception.Raise_Exception;
84 END assp_val_spco_closed;
85 --
86 -- Validate SUAAI or SCAP can be created
87 FUNCTION assp_val_suaai_ins(
88 p_person_id IGS_AS_SU_ATMPT_ITM.person_id%TYPE ,
89 p_course_cd IGS_AS_SU_ATMPT_ITM.course_cd%TYPE ,
90 p_unit_cd IGS_AS_SU_ATMPT_ITM.unit_cd%TYPE ,
91 p_cal_type IGS_AS_SU_ATMPT_ITM.cal_type%TYPE ,
92 p_ci_sequence_number IGS_AS_SU_ATMPT_ITM.ci_sequence_number%TYPE ,
93 p_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE ,
94 p_message_name OUT NOCOPY VARCHAR2 ,
95 -- anilk, 22-Apr-2003, Bug# 2829262
96 p_uoo_id IN NUMBER )
97 RETURN BOOLEAN IS
98 gv_other_detail VARCHAR2(255);
99 BEGIN
100 DECLARE
101 v_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
102 l_uoo_id igs_en_su_attempt.uoo_id%TYPE;
103 v_person_id IGS_AS_UAI_SUA_V.person_id%TYPE;
104 cst_unconfirm CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
105 'UNCONFIRM';
106 cst_enrolled CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE :=
107 'ENROLLED';
108 l_dummy VARCHAR2(1);
109 CURSOR c_sua IS
110 SELECT sua.unit_attempt_status,
111 sua.uoo_id
112 FROM IGS_EN_SU_ATTEMPT sua
113 WHERE sua.person_id = p_person_id AND
114 sua.course_cd = p_course_cd AND
115 -- anilk, 22-Apr-2003, Bug# 2829262
116 sua.uoo_id = p_uoo_id;
117 CURSOR c_suv IS
118 SELECT suv.person_id
119 FROM IGS_AS_UAI_SUA_V suv
120 WHERE suv.person_id = p_person_id AND
121 suv.course_cd = p_course_cd AND
122 -- anilk, 22-Apr-2003, Bug# 2829262
123 suv.uoo_id = p_uoo_id AND
124 suv.ass_id = p_ass_id AND
125 suv.uai_logical_delete_dt IS NULL;
126 -- The Following cursor is added by Nishikant -15JAN2002- Enh Bug#2162831
127 -- to check whether the assessment item is available at unitsection level or not
128 -- Changed as required by the JOB.Maintain the student attempt Items and the
129 -- form IGSAS016
130 CURSOR c_usv(l_usv_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
131 SELECT 'X'
132 FROM igs_ps_unitass_item_v usv
133 WHERE usv.ass_id = p_ass_id AND
134 usv.uoo_id = l_usv_uoo_id AND
135 usv.logical_delete_dt IS NULL;
136 BEGIN
137 --- Set the default message number
138 p_message_name := null;
139 OPEN c_sua;
140 FETCH c_sua INTO v_attempt_status,l_uoo_id;
141 IF (c_sua%NOTFOUND) THEN
142 CLOSE c_sua;
143 RAISE NO_DATA_FOUND;
144 ELSE
145 IF (v_attempt_status NOT IN(cst_unconfirm, cst_enrolled)) THEN
146 IF (v_attempt_status <> 'COMPLETED') THEN
147 p_message_name := 'IGS_AS_SUA_STATUS_INVALID';
148 CLOSE c_sua;
149 RETURN FALSE;
150 ELSE
151 p_message_name := 'IGS_AS_SUA_STATUS_INVALID_COM';
152 CLOSE c_sua;
153 RETURN FALSE;
154 END IF;
155 END IF;
156 END IF;
157 CLOSE c_sua;
158
159 OPEN c_usv(l_uoo_id);
160 FETCH c_usv INTO l_dummy;
161 IF c_usv%FOUND THEN
162 CLOSE c_usv;
163 RETURN TRUE;
164 ELSE
165 OPEN c_suv;
166 FETCH c_suv INTO v_person_id;
167 IF (c_suv%NOTFOUND) THEN
168 p_message_name := 'IGS_AS_SUA_ASSITEM_INVALID';
169 CLOSE c_suv;
170 RETURN FALSE;
171 END IF;
172 CLOSE c_suv;
173 RETURN TRUE;
174 END IF;
175 END;
176 EXCEPTION
177 WHEN OTHERS THEN
178 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
179 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SCAP.assp_val_suaai_ins');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END assp_val_suaai_ins;
183 --
184 -- Retrofitted
185 FUNCTION assp_val_suaai_delet(
186 p_person_id IN NUMBER ,
187 p_course_cd IN VARCHAR2 ,
188 p_unit_cd IN VARCHAR2 ,
189 p_cal_type IN VARCHAR2 ,
190 p_ci_sequence_number IN NUMBER ,
191 p_ass_id IGS_AS_SU_ATMPT_ITM.ass_id%TYPE ,
192 p_creation_dt IN DATE ,
193 p_message_name OUT NOCOPY VARCHAR2 ,
194 -- anilk, 22-Apr-2003, Bug# 2829262
195 p_uoo_id IN NUMBER )
196 RETURN BOOLEAN IS
197 gv_other_detail VARCHAR2(255);
198 BEGIN -- assp_val_suaai_delet
199 -- This module validates the deletion date a suaai record
200 DECLARE
201 CURSOR c_suaai IS
202 SELECT suaai.logical_delete_dt
203 FROM IGS_AS_SU_ATMPT_ITM suaai
204 WHERE suaai.person_id = p_person_id AND
205 suaai.course_cd = p_course_cd AND
206 -- anilk, 22-Apr-2003, Bug# 2829262
207 suaai.uoo_id = p_uoo_id AND
208 suaai.ass_id = p_ass_id AND
209 suaai.creation_dt = p_creation_dt AND
210 suaai.logical_delete_dt IS NOT NULL;
211 v_suaai_logical_delete_dt DATE;
212 BEGIN
213 -- Set the default message number
214 p_message_name := null;
215 -- Cursor handling
216 OPEN c_suaai;
217 FETCH c_suaai INTO v_suaai_logical_delete_dt;
218 IF c_suaai%FOUND THEN
219 CLOSE c_suaai;
220 p_message_name := 'IGS_FI_ELERNG_RATE_FEEASS_RAT';
221 RETURN FALSE;
222 END IF;
223 -- Return the default value
224 CLOSE c_suaai;
225 RETURN TRUE;
226 END;
227 EXCEPTION
228 WHEN OTHERS THEN
229 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
230 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_VAL_SCAP.assp_val_suaai_delet');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END assp_val_suaai_delet;
234 END IGS_AS_VAL_SCAP;
[Home] [Help]