1 PACKAGE BODY IGS_PS_VAL_UAI AS
2 /* $Header: IGSPS76B.pls 115.11 2002/12/26 07:34:30 sarakshi ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 -- ddey 09-JAN-2001 The function assp_val_uai_links is removed.
7 -- The function was called from the library IGSPS092.pld and was called from the TBH
8 -- IGSPI0KB.pls of the . This TBH is used for the form IGSPS092. All the calls for
9 -- this functions are removed form the library and the TBH. Apart form this, the function
10 -- assp_val_uai_links is not called from any other place .
11 -- As per the requirement mentioned in the DLD Calcualtion of results Part 1 (Bug # 2162831)
12 -- this function is no more required. Hence it is removed.
13 --smadathi 29-AUG-2001 The function genp_val_sdtt_sess removed .
14 -----------------------------------------------------------------------------------
15
16
17 --
18 --
19 -- Validate Calendar Instance for IGS_PS_COURSE Information.
20 FUNCTION CRSP_VAL_CRS_CI(
21 p_cal_type IN VARCHAR2 ,
22 p_ci_sequence_number IN NUMBER ,
23 p_message_name OUT NOCOPY VARCHAR2 )
24 RETURN BOOLEAN AS
25 /*************************************************************
26 Created By : jdeekoll
27 Date Created By : 11-May-2000
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 sarakshi 23-dec-2002 Bug#2689625,removed the exception part
33 (reverse chronological order - newest change first)
34 ***************************************************************/
35
36 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
37 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
38 CURSOR c_cal_status(
39 cp_cal_type IGS_CA_INST_ALL.cal_type%TYPE,
40 cp_ci_sequence_number IGS_CA_INST_ALL.sequence_number%TYPE) IS
41 SELECT IGS_CA_STAT.s_cal_status
42 FROM IGS_CA_INST, IGS_CA_STAT
43 WHERE IGS_CA_INST.cal_type = cp_cal_type AND
44 IGS_CA_INST.sequence_number = cp_ci_sequence_number AND
45 IGS_CA_INST.cal_status = IGS_CA_STAT.cal_status;
46 v_other_detail VARCHAR2(255);
47 BEGIN
48 P_MESSAGE_NAME := null;
49 OPEN c_cal_status(
50 p_cal_type,
51 p_ci_sequence_number);
52 FETCH c_cal_status INTO v_s_cal_status;
53 CLOSE c_cal_status;
54 IF (v_s_cal_status = cst_active) THEN
55 RETURN TRUE;
56 ELSE
57 P_MESSAGE_NAME := 'IGS_PS_CAL_MUSTBE_ACTIVE';
58 RETURN FALSE;
59 END IF;
60 END crsp_val_crs_ci;
61
62 -- Retrofitted
63 -- Retrofitted
64 FUNCTION assp_val_uai_opt_ref(
65 p_unit_cd IN VARCHAR2 ,
66 p_version_number IN NUMBER ,
67 p_cal_type IN VARCHAR2 ,
68 p_ci_sequence_number IN NUMBER ,
69 p_sequence_number IN NUMBER ,
70 p_reference IN VARCHAR2 ,
71 p_ass_id IN NUMBER ,
72 p_assessment_type IN VARCHAR2 ,
73 p_message_name OUT NOCOPY VARCHAR2 )
74 RETURN BOOLEAN AS
75 /*************************************************************
76 Created By : jdeekoll
77 Date Created By : 11-May-2000
78 Purpose :
79 Know limitations, enhancements or remarks
80 Change History
81 Who When What
82 sarakshi 23-dec-2002 Bug#2689625,removed the outer exception
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86 gv_other_detail VARCHAR2(255);
87 BEGIN -- assp_val_uai_opt_ref
88 -- Validate that the reference number (when it has been set)
89 -- is unique within an assessment type within a IGS_PS_UNIT offering
90 -- pattern for non-examinable items which have not been deleted.
91 -- This is similar to ASSP_VAL_UAI_UNIQREF except that:
92 -- * The routine validates non-examinable items as opposed
93 -- to examinable items
94 -- * Reference is optional
95 -- * Reference when set is unique within an assessment type and
96 -- only for items that have not been deleted
97 DECLARE
98 CURSOR c_uai IS
99 SELECT 'x'
100 FROM igs_ps_unitass_item uai,
101 IGS_AS_ASSESSMNT_ITM ai,
102 IGS_AS_ASSESSMNT_TYP atyp,
103 igs_ps_unit_ofr_opt uoo
104 WHERE atyp.examinable_ind = 'N' AND
105 atyp.ASSESSMENT_TYPE = p_assessment_type AND
106 atyp.ASSESSMENT_TYPE = ai.ASSESSMENT_TYPE AND
107 uai.ass_id = ai.ass_id AND
108 uoo.unit_cd = p_unit_cd AND
109 uoo.version_number = p_version_number AND
110 uoo.cal_type = p_cal_type AND
111 uoo.ci_sequence_number = p_ci_sequence_number AND
112 uai.ass_id <> p_ass_id AND
113 uai.sequence_number <> p_sequence_number AND
114 uai.reference = p_reference AND
115 uai.logical_delete_dt IS NULL and uai.uoo_id=uoo.uoo_id;
116 v_uai_exists VARCHAR2(1);
117 BEGIN
118 -- Set the default message number
119 P_MESSAGE_NAME := null;
120 IF p_reference IS NOT NULL THEN
121 -- Select from the table taking care not to select
122 -- record passed in.
123 OPEN c_uai;
124 FETCH c_uai INTO v_uai_exists;
125 IF c_uai%FOUND THEN
126 CLOSE c_uai;
127 P_MESSAGE_NAME := 'IGS_PS_REF_UAI_UNIQUE';
128 RETURN FALSE;
129 END IF;
130 CLOSE c_uai;
131 END IF;
132 -- Return the default value
133 RETURN TRUE;
134 EXCEPTION
135 WHEN OTHERS THEN
136 IF c_uai%ISOPEN THEN
137 CLOSE c_uai;
138 END IF;
139 RAISE;
140 END;
141 END assp_val_uai_opt_ref;
142 --
143 -- Retrofitted
144 --
145
146
147 -- Retrofitted
148 FUNCTION assp_val_uai_sameref(
149 p_unit_cd IN VARCHAR2 ,
150 p_version_number IN NUMBER ,
151 p_cal_type IN VARCHAR2 ,
152 p_ci_sequence_number IN NUMBER ,
153 p_sequence_number IN NUMBER ,
154 p_reference IN VARCHAR2 ,
155 p_ass_id IN NUMBER ,
156 p_message_name OUT NOCOPY VARCHAR2 )
157 RETURN BOOLEAN AS
158 /*************************************************************
159 Created By : jdeekoll
160 Date Created By : 11-May-2000
161 Purpose :
162 Know limitations, enhancements or remarks
163 Change History
164 Who When What
165 sarakshi 23-dec-2002 Bug#2689625,removed the outer exception
166
167 (reverse chronological order - newest change first)
168 ***************************************************************/
169
170 gv_other_detail VARCHAR2(255);
171 BEGIN -- assp_val_uai_sameref
172 -- Validate reference number is the same for all items,
173 -- with the same assessment id, within a IGS_PS_UNIT offering pattern
174 -- for examinable items.
175 DECLARE
176 CURSOR c_uai IS
177 SELECT 'x'
178 FROM IGS_AS_ASSESSMNT_TYP atyp,
179 IGS_AS_ASSESSMNT_ITM ai,
180 igs_ps_unitass_item uai,
181 igs_ps_unit_ofr_opt uoo
182 WHERE atyp.examinable_ind = 'Y' AND
183 ai.assessment_type = atyp.assessment_type AND
184 uai.ass_id = ai.ass_id AND
185 uoo.unit_cd = p_unit_cd AND
186 uoo.version_number = p_version_number AND
187 uoo.cal_type = p_cal_type AND
188 uoo.ci_sequence_number = p_ci_sequence_number AND
189 uai.ass_id = p_ass_id AND
190 uai.sequence_number <> p_sequence_number AND
191 NVL(uai.reference, 'NULL') <> NVL(p_reference, 'NULL') and uai.uoo_id=uoo.uoo_id;
192 v_uai_exists VARCHAR2(1);
193 BEGIN
194 -- Set the default message number
195 P_MESSAGE_NAME := null;
196 -- Check for the existence of a record
197 OPEN c_uai;
198 FETCH c_uai INTO v_uai_exists;
199 IF c_uai%NOTFOUND THEN
200 CLOSE c_uai;
201 RETURN TRUE;
202 END IF;
203 CLOSE c_uai;
204 -- Records have been found
205 P_MESSAGE_NAME := 'IGS_AS_REF_UAI_SAME';
206 RETURN FALSE;
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF c_uai%ISOPEN THEN
210 CLOSE c_uai;
211 END IF;
212 RAISE;
213 END;
214 END assp_val_uai_sameref;
215 --
216
217 --
218 END IGS_PS_VAL_UAI;
[Home] [Help]