1 PACKAGE BODY igs_en_spi_rcond_lgcy_pub AS
2 /* $Header: IGSENB5B.pls 120.0 2006/04/10 04:23:36 bdeviset noship $ */
3 /*-------------------------------------------------------------------------------------------
4 Created by : Basanth Devisetty, Oracle Student Systems Oracle IDC
5 Purpose : This package is used to validate the passed parameters of student
6 intermission return condition and insert the records.
7
8 --Change History:
9 --Who When What
10
11 ---------------------------------------------------------------------------------------------*/
12
13 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_SPI_RCOND_LGCY_PUB';
14
15 FUNCTION validate_parameters(p_person_id IN hz_parties.party_id%TYPE ,
16 p_intm_rcond_rec IN en_spi_rcond_rec_type)
17 RETURN VARCHAR2 AS
18
19 /**********************************************************************************************
20 Created By : bdeviset
21 Date Created By : 14-Mar-2005
22 Purpose : This function is used to validate the passed parameters
23 Known limitations,enhancements,remarks:
24 Change History
25 Who When What
26 ***********************************************************************************************/
27
28 l_ret_val VARCHAR2(1);
29
30 BEGIN
31 l_ret_val := 'S';
32
33 -- Make sure the program code is in uppercase. If not throw Error
34 IF p_intm_rcond_rec.program_cd <> UPPER(p_intm_rcond_rec.program_cd) THEN
35 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRGM_CD_UCASE');
36 FND_MSG_PUB.ADD;
37 l_ret_val := 'E';
38 END IF;
39
40 -- Ensure return condition is in uppercase. Otherwise throw Error
41 IF p_intm_rcond_rec.return_condition <> UPPER(p_intm_rcond_rec.return_condition) THEN
42 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_INTM_RCOND_UCASE');
43 FND_MSG_PUB.ADD;
44 l_ret_val := 'E';
45 END IF;
46
47
48 RETURN l_ret_val;
49
50 END validate_parameters;
51
52 FUNCTION validate_db_cons ( p_person_id IN hz_parties.party_id%TYPE ,
53 p_intm_rcond_rec IN en_spi_rcond_rec_type)
54 RETURN VARCHAR2 AS
55 /**********************************************************************************************
56 Created By : bdeviset
57 Date Created By : 14-Mar-2005
58 Purpose : This function is used to validate database constraints
59 Known limitations,enhancements,remarks:
60 Change History
61 Who When What
62 ***********************************************************************************************/
63
64 CURSOR chk_rcond_status IS
65 SELECT 'x'
66 FROM igs_lookup_values
67 WHERE lookup_type = 'IGS_EN_INTM_RCOND_STS'
68 AND lookup_Code = p_intm_rcond_rec.status_code;
69
70 CURSOR chk_intermission (cp_person_id hz_parties.party_id%TYPE) IS
71 SELECT cond_return_flag
72 FROM igs_en_stdnt_ps_intm
73 WHERE person_id = cp_person_id
74 AND course_cd = p_intm_rcond_rec.program_cd
75 AND start_dt = p_intm_rcond_rec.start_dt
76 AND logical_delete_date = to_date('31-12-4712','DD-MM-YYYY');
77
78 l_dummy VARCHAR2(1);
79 l_ret_val VARCHAR2(1);
80
81
82 BEGIN
83
84 -- Ensure the return condition is valid by checking the intermission
85 -- conditions. If not throw Error
86 IF NOT igs_en_intm_rconds_pkg.get_pk_for_validation(x_return_condition => p_intm_rcond_rec.return_condition) THEN
87 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_SPI_INV_RCOND');
88 FND_MSG_PUB.ADD;
89 l_ret_val := 'E';
90 END IF;
91
92 -- Check whether the student intermission return condition already exists
93 IF igs_en_spi_rconds_pkg.get_pk_for_validation (
94 x_person_id => p_person_id,
95 x_course_cd => p_intm_rcond_rec.program_cd,
96 x_start_dt => p_intm_rcond_rec.start_dt,
97 x_logical_delete_date => to_date('31-12-4712','DD-MM-YYYY'),
98 x_return_condition => p_intm_rcond_rec.return_condition) THEN
99 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_STU_INTM_RCOND_EXISTS');
100 FND_MSG_PUB.ADD;
101 l_ret_val := 'W';
102 END IF;
103
104 -- Ensure the return condition status is valid.
105 -- Use lookup Type IGS_EN_INTM_RCOND_STS to determine the valid statuses
106 OPEN chk_rcond_status;
107 FETCH chk_rcond_status INTO l_dummy;
108 IF chk_rcond_status%NOTFOUND THEN
109 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_RCOND_INV_STS');
110 FND_MSG_PUB.ADD;
111 l_ret_val := 'E';
112 END IF;
113 CLOSE chk_rcond_status;
114
115 ------ Validate parent record existence and return cond ind-------
116 l_dummy := NULL;
117 OPEN chk_intermission(p_person_id);
118 FETCH chk_intermission INTO l_dummy;
119 CLOSE chk_intermission;
120
121 IF l_dummy IS NULL THEN
122 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_STU_INTM_NOT_EXISTS');
123 FND_MSG_PUB.ADD;
124 l_ret_val := 'E';
125 ELSIF l_dummy = 'N' THEN
126 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NOT_VLD_RCOND');
127 FND_MSG_PUB.ADD;
128 l_ret_val := 'E';
129 END IF;
130
131 RETURN l_ret_val;
132
133
134 END validate_db_cons;
135
136
137 PROCEDURE create_student_intm_rcond
138 ( p_api_version IN NUMBER,
139 p_init_msg_list IN VARCHAR2,
140 p_commit IN VARCHAR2,
141 p_validation_level IN NUMBER,
142 p_intm_rcond_rec IN en_spi_rcond_rec_type,
143 x_return_status OUT NOCOPY VARCHAR2,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2) AS
146
147 /**********************************************************************************************
148 Created By : bdeviset
149 Date Created By : 14-Mar-2005
150 Purpose : Main API for importing student intermission Return Condition.
151 Created for Intermission Authorization to Return Build Bug# 5083465
152 Known limitations,enhancements,remarks:
153 Change History
154 Who When What
155 ***********************************************************************************************/
156
157 l_rowid ROWID;
158 l_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
159 l_person_id hz_parties.party_id%TYPE;
160 l_sca_rec igs_en_stdnt_ps_att%ROWTYPE;
161 l_insert_flag BOOLEAN;
162 l_creation_date igs_en_stdnt_ps_intm.creation_date%TYPE;
163 l_last_update_date igs_en_stdnt_ps_intm.last_update_date%TYPE;
164 l_created_by igs_en_stdnt_ps_intm.created_by%TYPE;
165 l_last_updated_by igs_en_stdnt_ps_intm.last_updated_by%TYPE;
166 l_last_update_login igs_en_stdnt_ps_intm.last_update_login%TYPE;
167 l_api_name CONSTANT VARCHAR2(30) := 'create_student_intm_rcond';
168 l_api_version CONSTANT NUMBER := 1.0;
169 l_approver_id hz_parties.party_id%TYPE;
170 l_ret_val VARCHAR2(1);
171
172 CURSOR c_crs_status (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE) IS
173 SELECT course_attempt_status
174 FROM igs_en_stdnt_ps_att
175 WHERE person_id = cp_person_id
176 AND course_cd = p_intm_rcond_rec.program_cd;
177
178 BEGIN
179
180 SAVEPOINT create_en_spi_rcond_pub;
181 -- initialze the insert flag for inserting student intermission
182 -- return condition to true
183 l_insert_flag := TRUE;
184
185 -- Check if the api version and the parameter p_api_version are compatible. If not raise and FND unexpected error.
186 IF NOT FND_API.COMPATIBLE_API_CALL( p_current_version_number => l_api_version,
187 p_caller_version_number => p_api_version,
188 p_api_name => l_api_name,
189 p_pkg_name => 'IGS_EN_SPI_RCOND_LGCY_PUB')
190 THEN
191
192 l_insert_flag := FALSE;
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194
195 END IF;
196
197 -- initialize the return status to success
198 x_return_status := FND_API.G_RET_STS_SUCCESS;
199
200 -- If parameter p_init_msg_list is passed as TRUE, then initialize the message list
201 IF FND_API.TO_BOOLEAN (p_init_msg_list) THEN
202 FND_MSG_PUB.INITIALIZE;
203 END IF;
204
205
206 -- Ensure Person Number is valid. Otherwise throw Error
207 l_person_id := NULL;
208 l_person_id := Igs_Ge_Gen_003.get_person_id(p_intm_rcond_rec.person_number);
209 IF l_person_id IS NULL THEN
210 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
211 FND_MSG_PUB.ADD;
212 l_insert_flag := FALSE;
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 END IF;
215
216 -- If Approver Number is provided then ensure that it is valid.
217 -- Otherwise throw Error
218 IF p_intm_rcond_rec.approver_number IS NOT NULL THEN
219 l_approver_id := NULL;
220 l_approver_id := Igs_Ge_Gen_003.get_person_id(p_intm_rcond_rec.approver_number);
221 IF l_approver_id IS NULL THEN
222 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NO_APPROV_PERSON');
223 FND_MSG_PUB.ADD;
224 l_insert_flag := FALSE;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 END IF;
227
228 END IF;
229
230
231
232 IF l_insert_flag THEN
233 -- validate the parameters passed
234 IF validate_parameters(l_person_id,p_intm_rcond_rec) = 'E' THEN
235 l_insert_flag := FALSE;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 END IF;
238 END IF;
239
240 OPEN c_crs_status ( l_person_id);
241 FETCH c_crs_status INTO l_course_attempt_status;
242 CLOSE c_crs_status;
243
244 IF l_insert_flag THEN
245 l_ret_val := validate_db_cons( l_person_id, p_intm_rcond_rec);
246 IF l_ret_val = 'E' THEN
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 l_insert_flag := FALSE;
249 ELSIF l_ret_val = 'W' THEN
250 x_return_status := 'W';
251 l_insert_flag := FALSE;
252 END IF;
253 END IF;
254
255 IF l_insert_flag THEN
256
257 l_creation_date := SYSDATE;
258 l_created_by := FND_GLOBAL.USER_ID;
259 l_last_update_date := SYSDATE;
260 l_last_updated_by := FND_GLOBAL.USER_ID;
261 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
262
263 IF l_created_by IS NULL THEN
264 l_created_by := -1;
265 END IF;
266
267 IF l_last_updated_by IS NULL THEN
268 l_last_updated_by := -1;
269 END IF;
270
271 IF l_last_update_login IS NULL THEN
272 l_last_update_login := -1;
273 END IF;
274
275 -- insert the student intermission return cond record
276 INSERT INTO igs_en_spi_rconds (
277 person_id,
278 course_cd,
279 start_dt,
280 logical_delete_date,
281 return_condition,
282 status_code,
283 approved_dt,
284 approved_by,
285 created_by,
286 creation_date,
287 last_updated_by,
288 last_update_date,
289 last_update_login
290 )
291 VALUES (
292 l_person_id,
293 p_intm_rcond_rec.program_cd,
294 p_intm_rcond_rec.start_dt,
295 to_date('31-12-4712','DD-MM-YYYY'),
296 p_intm_rcond_rec.return_condition,
297 p_intm_rcond_rec.status_code,
298 p_intm_rcond_rec.approved_dt,
299 l_approver_id,
300 l_created_by,
301 l_creation_date,
302 l_last_updated_by,
303 l_last_update_date,
304 l_last_update_login
305 );
306
307 IF l_course_attempt_status <> 'INTERMIT' AND p_intm_rcond_rec.status_code IN ('PENDING','FAILED') THEN
308
309 -- call the api to know whether program attempt status has changed
310 -- if so update the spa table with new status
311 IF igs_en_gen_legacy.check_sca_status_upd (
312 p_person_id => l_person_id,
313 p_program_cd => p_intm_rcond_rec.program_cd,
314 p_called_from => 'SPI',
315 p_course_attempt_status => l_course_attempt_status
316 ) THEN
317 UPDATE igs_en_stdnt_ps_att_all SET course_attempt_status = 'INTERMIT'
318 WHERE person_id = l_person_id
319 AND course_cd = p_intm_rcond_rec.program_cd;
320
321 END IF;-- IF igs_en_gen_legacy.check_sca_status_upd
322
323 END IF; -- IF l_course_attempt_status <> 'INTERMIT' AND p_intm_rcond_rec.status_code IN ('PENDING','FAILED')
324
325 ELSE
326
327 ROLLBACK TO create_en_spi_rcond_pub;
328
329 END IF; -- IF l_insert_flag
330
331 -- Commit the record which is inserted in the table.
332 IF (FND_API.TO_BOOLEAN(p_commit) AND l_insert_flag ) THEN
333 COMMIT WORK;
334 END IF;
335
336 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
337 p_data => x_msg_data);
338
339 EXCEPTION
340 WHEN FND_API.G_EXC_ERROR THEN
341 ROLLBACK TO create_en_spi_rcond_pub;
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
344 p_data => x_msg_data);
345
346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
347 ROLLBACK TO create_en_spi_rcond_pub;
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
350 p_data => x_msg_data);
351
352 WHEN OTHERS THEN
353 ROLLBACK TO create_en_spi_rcond_pub;
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
355 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
356 FND_MSG_PUB.ADD_EXC_MSG(g_pkg_name,
357 l_api_name);
358 END IF;
359 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
360 p_data => x_msg_data);
361
362 END create_student_intm_rcond;
363
364
365 END igs_en_spi_rcond_lgcy_pub;
[Home] [Help]