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