1 PACKAGE BODY IGS_AS_VAL_SUAAP AS
2 /* $Header: IGSAS31B.pls 120.0 2005/07/05 11:21:07 appldev noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .Modified function GENP_VAL_SDTT_SESS
7 -------------------------------------------------------------------------------------------
8 -- Val IGS_PS_UNIT assess pattern applies to stud IGS_PS_UNIT IGS_AD_LOCATION, class and mode.
9 FUNCTION ASSP_VAL_UAP_LOC_UC(
10 p_location_cd IN VARCHAR2 ,
11 p_unit_class IN VARCHAR2 ,
12 p_unit_mode IN VARCHAR2 ,
13 p_uap_location_cd IN VARCHAR2 ,
14 p_uap_unit_class IN VARCHAR2 ,
15 p_uap_unit_mode IN VARCHAR2 ,
16 p_message_name OUT NOCOPY VARCHAR2 )
17 RETURN BOOLEAN IS
18 gv_other_detail VARCHAR2(255);
19 BEGIN -- assp_val_uap_loc_uc
20 -- This routine will validate that the IGS_AS_UNTAS_PATTERN's
21 -- IGS_AD_LOCATION, mode and class are applicable for the parameters passed in
22 DECLARE
23 v_ret_val BOOLEAN DEFAULT TRUE;
24 BEGIN
25 p_message_name := NULL;
26 IF p_location_cd <> NVL(p_uap_location_cd, p_location_cd) THEN
27 p_message_name := 'IGS_AS_UAP_LOC_NA_LOCATION';
28 RETURN FALSE;
29 ELSIF p_unit_class <> NVL(p_uap_unit_class, p_unit_class) THEN
30 p_message_name := 'IGS_AS_UAP_UNITCLASS_NA';
31 RETURN FALSE;
32 ELSIF p_unit_mode <> NVL(p_uap_unit_mode, p_unit_mode) THEN
33 p_message_name := 'IGS_AS_UAP_UNITMODE_NA';
34 RETURN FALSE;
35 ELSE
36 p_message_name := NULL;
37 RETURN TRUE;
38 END IF;
39 RETURN v_ret_val;
40 END;
41
42 END assp_val_uap_loc_uc;
43 --
44 -- Validate able to create stdnt_unit_atmp_ass_pattern.
45 FUNCTION ASSP_VAL_SUAAP_INS(
46 p_person_id IN NUMBER ,
47 p_course_cd IN VARCHAR2 ,
48 p_unit_cd IN VARCHAR2 ,
49 p_cal_type IN VARCHAR2 ,
50 p_ci_sequence_number IN NUMBER ,
51 p_ass_pattern_id IN NUMBER ,
52 p_message_name OUT NOCOPY VARCHAR2 ,
53 -- anilk, 22-Apr-2003, Bug# 2829262
54 p_uoo_id IN NUMBER )
55 RETURN BOOLEAN IS
56 gv_other_detail VARCHAR2(255);
57 BEGIN -- assp_val_suaap_ins
58 -- Validate that the IGS_AS_SU_ATMPT_PAT can be created.
59 -- It must be valid for the students IGS_AD_LOCATION/class/mode and the status of
60 -- the student IGS_PS_UNIT attempt must be either UNCONFIRM/ENROLLED
61 DECLARE
62 cst_enrolled VARCHAR(8) := 'ENROLLED';
63 cst_unconfirmed VARCHAR(9) := 'UNCONFIRM';
64 cst_completed VARCHAR(9) := 'COMPLETED';
65 v_uooap_v_dummy VARCHAR2(1) := NULL;
66 v_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
67 v_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE;
68 CURSOR c_sua IS
69 SELECT sua.unit_attempt_status,
70 sua.uoo_id
71 FROM IGS_EN_SU_ATTEMPT sua
72 WHERE sua.person_id = p_person_id AND
73 sua.course_cd = p_course_cd AND
74 -- anilk, 22-Apr-2003, Bug# 2829262
75 sua.uoo_id = p_uoo_id;
76 CURSOR c_uooap_v(
77 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE)IS
78 SELECT 'x'
79 FROM IGS_PS_UNIT_OFR_OPT_ASS_PAT_V uooap_v
80 WHERE uooap_v.unit_cd = p_unit_cd AND
81 uooap_v.cal_type = p_cal_type AND
82 uooap_v.ci_sequence_number = p_ci_sequence_number AND
83 uooap_v.ass_pattern_id = p_ass_pattern_id AND
84 uooap_v.uoo_id = cp_uoo_id AND
85 uooap_v.uap_logical_delete_dt IS NULL;
86 BEGIN
87 -- Set the default message number
88 p_message_name := NULL;
89 -- Validate IGS_PS_UNIT version
90 OPEN c_sua;
91 FETCH c_sua INTO v_unit_attempt_status,
92 v_uoo_id;
93 IF (c_sua%NOTFOUND) THEN
94 CLOSE c_sua;
95 RAISE NO_DATA_FOUND;
96 ELSE
97 CLOSE c_sua;
98 IF v_unit_attempt_status NOT IN (cst_unconfirmed, cst_enrolled) THEN
99 IF v_unit_attempt_status <> cst_completed THEN
100 -- Set message number to indicate incorrect student IGS_PS_UNIT status.
101 p_message_name := 'IGS_AS_SUA_STATUS_INVALID';
102 RETURN FALSE;
103 ELSE
104 -- Set message number to indicate incorrect student IGS_PS_UNIT status of
105 -- completed.
106 p_message_name := 'IGS_AS_CANNOT_ADD_ASSPATTERN';
107 RETURN FALSE;
108 END IF;
109 END IF;
110 END IF;
111 OPEN c_uooap_v (
112 v_uoo_id);
113 FETCH c_uooap_v INTO v_uooap_v_dummy;
114 IF (c_uooap_v%NOTFOUND) THEN
115 CLOSE c_uooap_v;
116 p_message_name := 'IGS_AS_SUA_ASSPAT_INVALID';
117 RETURN FALSE;
118 END IF;
119 CLOSE c_uooap_v;
120 -- If processing reaches this point then return successfully.
121 RETURN TRUE;
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF (c_uooap_v%ISOPEN) THEN
125 CLOSE c_uooap_v;
126 END IF;
127 IF (c_sua%ISOPEN) THEN
128 CLOSE c_sua;
129 END IF;
130 END;
131 EXCEPTION
132 WHEN OTHERS THEN
133 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
134 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAAP.ASSP_VAL_SUAAP_INS');
135 Igs_Ge_Msg_Stack.Add;
136 App_Exception.Raise_Exception;
137 END assp_val_suaap_ins;
138 --
139 -- Validate only one active instance of the assessment pattern for suaap.
140 FUNCTION ASSP_VAL_SUAAP_ACTV(
141 p_person_id IN NUMBER ,
142 p_course_cd IN VARCHAR2 ,
143 p_unit_cd IN VARCHAR2 ,
144 p_cal_type IN VARCHAR2 ,
145 p_ci_sequence_number IN NUMBER ,
146 p_ass_pattern_id IN NUMBER ,
147 p_creation_dt IN DATE ,
148 p_message_name OUT NOCOPY VARCHAR2 ,
149 -- anilk, 22-Apr-2003, Bug# 2829262
150 p_uoo_id IN NUMBER )
151 RETURN BOOLEAN IS
152 gv_other_detail VARCHAR2(255);
153 BEGIN --assp_val_suaap_actv
154 DECLARE
155 v_suaap_count VARCHAR2(1);
156 CURSOR c_suaap IS
157 SELECT 'x'
158 FROM IGS_AS_SU_ATMPT_PAT suaap
159 WHERE suaap.person_id = p_person_id AND
160 suaap.course_cd = p_course_cd AND
161 -- anilk, 22-Apr-2003, Bug# 2829262
162 suaap.uoo_id = p_uoo_id AND
163 suaap.ass_pattern_id = p_ass_pattern_id AND
164 suaap.creation_dt <> p_creation_dt AND
165 suaap.logical_delete_dt IS NULL;
166 BEGIN
167 -- Set the default message number
168 p_message_name := NULL;
169 OPEN c_suaap ;
170 FETCH c_suaap INTO v_suaap_count;
171 IF c_suaap %FOUND THEN
172 CLOSE c_suaap;
173 p_message_name := 'IGS_AS_ASSPATTERN_EXISTS_STUD';
174 RETURN FALSE;
175 END IF;
176 CLOSE c_suaap ;
177 -- Return the default value
178 RETURN TRUE;
179 EXCEPTION
180 WHEN OTHERS THEN
181 IF c_suaap %ISOPEN THEN
182 CLOSE c_suaap;
183 END IF;
184 RAISE;
185 END;
186 EXCEPTION
187 WHEN OTHERS THEN
188 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
189 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_SUAAP.ASSP_VAL_SUAAP_ACTV');
190 Igs_Ge_Msg_Stack.Add;
191 App_Exception.Raise_Exception;
192 END assp_val_suaap_actv;
193 --
194 -- Validate that an entry exist in s_disable_table_trigger.
195 FUNCTION GENP_VAL_SDTT_SESS(
196 p_table_name IN VARCHAR2 )
197 RETURN BOOLEAN IS
198 -------------------------------------------------------------------------------------------
199 --Change History:
200 --Who When What
201 --smadathi 24-AUG-2001 Bug No. 1956374 .Removed the exception Handler part
202 -------------------------------------------------------------------------------------------
203 gv_other_detail VARCHAR2(255);
204 BEGIN
205 DECLARE
206 CURSOR c_chk_for_tbl IS
207 SELECT 'x'
208 FROM IGS_GE_S_DSB_TAB_TRG
209 WHERE table_name = p_table_name AND
210 session_id = (
211 SELECT userenv('SESSIONID')
212 FROM dual );
213 v_return_result CHAR;
214 BEGIN
215 -- Validates that if a record exists in the s_disable_table_trigger
216 -- database table matching the table name and session id, then return
217 -- false indicating not to execute the table?s database triggers.
218 OPEN c_chk_for_tbl;
219 FETCH c_chk_for_tbl INTO v_return_result;
220 IF c_chk_for_tbl%FOUND THEN
221 CLOSE c_chk_for_tbl;
222 RETURN FALSE;
223 ELSE
224 CLOSE c_chk_for_tbl;
225 RETURN TRUE;
226 END IF;
227 END;
228
229 END GENP_VAL_SDTT_SESS;
230 --
231 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
232
233 END IGS_AS_VAL_SUAAP;
[Home] [Help]