1 PACKAGE BODY IGS_PR_VAL_SPU AS
2 /* $Header: IGSPR22B.pls 115.7 2002/11/29 02:49:37 nsidana ship $ */
3
4 /* Bug 1956374
5 Who msrinivi
6 What duplicate removal Rremoved genp_prc_clear_rowid
7 */
8 /*
9 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
10 || Removed program unit (PRGP_VAL_UV_ACTIVE) - from the spec and body. -- kdande
11 */
12 --
13
14 -- Validate student progression unit / outcome relationship
15
16 FUNCTION prgp_val_spu_spo(
17
18 p_person_id IN NUMBER ,
19
20 p_course_cd IN VARCHAR2 ,
21
22 p_sequence_number IN NUMBER ,
23
24 p_s_unit_type IN VARCHAR2 ,
25
26 p_message_name OUT NOCOPY VARCHAR2 )
27
28 RETURN BOOLEAN IS
29
30 gv_other_detail VARCHAR2(255);
31
32 BEGIN -- PRGP_VAL_SPU_SPO
33
34
35 -- Purpose: When creating a Student Progression Unit record validate that the
36
37 -- record is related to a IGS_PR_STDNT_PR_OU with a
38
39 -- progression_outcome_type that relates to:
40
41 -- a s_encmb_effect_type of EXC_CRS_U when s_unit_type = EXCLUDED or
42
43 -- a s_encmb_effect_type of RQRD_CRS_U when s_unit_type = REQUIRED.
44
45 DECLARE
46
47 v_exists VARCHAR2(1);
48
49 CURSOR c_spo(
50
51 cp_see_type IGS_FI_ENC_DFLT_EFT.s_encmb_effect_type%TYPE) IS
52
53 SELECT 'x'
54
55 FROM IGS_PR_STDNT_PR_OU spo,
56
57 IGS_PR_OU_TYPE pot,
58
59
60 IGS_FI_ENC_DFLT_EFT etde
61
62 WHERE spo.person_id = p_person_id AND
63
64 spo.course_cd = p_course_cd AND
65
66 spo.sequence_number = p_sequence_number AND
67
68 spo.progression_outcome_type = pot.progression_outcome_type AND
69
70 pot.encumbrance_type = etde.encumbrance_type AND
71
72 etde.s_encmb_effect_type = cp_see_type;
73
74 BEGIN
75
76 -- Set the default message number
77
78 p_message_name := null;
79
80 IF p_person_id IS NULL OR
81
82
83 p_course_cd IS NULL OR
84
85 p_sequence_number IS NULL OR
86
87 p_s_unit_type IS NULL THEN
88
89 RETURN TRUE;
90
91 END IF;
92
93 IF p_s_unit_type = 'EXCLUDED' THEN
94
95 OPEN c_spo('EXC_CRS_U');
96
97 FETCH c_spo INTO v_exists;
98
99 IF c_spo%NOTFOUND THEN
100
101 CLOSE c_spo;
102
103 p_message_name := 'IGS_PR_STPR_OUT_EXC_CRS_U';
104
105 RETURN FALSE;
106
107
108 END IF;
109
110 CLOSE c_spo;
111
112 END IF;
113
114 IF p_s_unit_type = 'REQUIRED' THEN
115
116 OPEN c_spo('RQRD_CRS_U');
117
118 FETCH c_spo INTO v_exists;
119
120 IF c_spo%NOTFOUND THEN
121
122 CLOSE c_spo;
123
124 p_message_name := 'IGS_PR_STPR_OUT_RQRD_CRS_U';
125
126 RETURN FALSE;
127
128 END IF;
129
130
131 CLOSE c_spo;
132
133 END IF;
134
135 -- Return the default value
136
137 RETURN TRUE;
138
139 EXCEPTION
140
141 WHEN OTHERS THEN
142
143 IF c_spo%ISOPEN THEN
144
145 CLOSE c_spo;
146
147 END IF;
148
149 RAISE;
150
151 END;
152
153 EXCEPTION
154
155
156 WHEN OTHERS THEN
157
158
159 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END; -- Function PRGP_VAL_SPU_SPO
163
164 --
165
166
167 -- Routine to process rowids in a PL/SQL TABLE for the current commit.
168
169 FUNCTION prgp_prc_spu_rowids(
170
171 p_inserting IN BOOLEAN ,
172
173 p_updating IN BOOLEAN ,
174
175
176 p_deleting IN BOOLEAN ,
177
178 p_message_name OUT NOCOPY VARCHAR2 )
179
180 RETURN BOOLEAN IS
181
182 v_index BINARY_INTEGER;
183
184 BEGIN
185
186 -- Process saved rows.
187
188 FOR v_index IN 1..gv_table_index - 1 LOOP
189
190 IF p_inserting OR p_updating OR p_deleting THEN
191
192 -- Update student progression outcome applied date
193
194 IGS_PR_GEN_004.IGS_PR_UPD_SPO_APLY_DT (
195
196 gt_rowid_table(v_index).person_id,
197
198 gt_rowid_table(v_index).course_cd,
199
200
201 gt_rowid_table(v_index).sequence_number);
202
203 END IF;
204
205 END LOOP;
206
207 RETURN TRUE;
208
209 END prgp_prc_spu_rowids;
210
211 --
212
213 -- Routine to save key in a PL/SQL TABLE for the current commit.
214
215 PROCEDURE prgp_set_spu_rowid(
216
217 p_person_id IN NUMBER ,
218
219 p_course_cd IN VARCHAR2 ,
220
221 p_sequence_number IN NUMBER )
222
223
224 IS
225
226 v_index BINARY_INTEGER;
227
228 v_spo_found BOOLEAN DEFAULT FALSE;
229
230 BEGIN
231
232 -- Check if record already exists in gt_rowid_table
233
234 FOR v_index IN 1..gv_table_index - 1 LOOP
235
236 IF gt_rowid_table(v_index).person_id = p_person_id AND
237
238 gt_rowid_table(v_index).course_cd = p_course_cd AND
239
240 gt_rowid_table(v_index).sequence_number = p_sequence_number THEN
241
242 v_spo_found := TRUE;
243
244 EXIT;
245
246 END IF;
247
248
249 END LOOP;
250
251 -- Save student progression outcome key details
252
253 IF NOT v_spo_found THEN
254
255 gt_rowid_table(gv_table_index).person_id := p_person_id;
256
257 gt_rowid_table(gv_table_index).course_cd := p_course_cd;
258
259 gt_rowid_table(gv_table_index).sequence_number := p_sequence_number;
260
261 gv_table_index := gv_table_index +1;
262
263 END IF;
264
265 END prgp_set_spu_rowid;
266
267 END IGS_PR_VAL_SPU;
[Home] [Help]