1 PACKAGE BODY igs_en_spaa_lgcy_pub AS
2 /* $Header: IGSENA5B.pls 115.5 2003/10/10 09:17:35 nalkumar noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='IGS_EN_SPAA_LGCY_PUB';
5
6 FUNCTION validate_parameters ( p_awd_aim_rec IN awd_aim_rec_type )
7 RETURN VARCHAR2 AS
8 /*
9 || Created By : nbehera
10 || Created On : 20NOV2002
11 || Purpose : This procedure validates all the fields in the parameter p_the_dtls_rec
12 || which require one or more validation
13 || Known limitations, enhancements or remarks :
14 || Change History :
15 || Who When What
16 || anilk 07-Oct-2003 changes in awd_aim_rec_type for Program Completion Validation, Bug# 3129913
17 || (reverse chronological order - newest change first)
18 */
19 l_val_param VARCHAR2(10) := 'VALID';
20
21 BEGIN
22 -- Check if person_number value is null then log error message
23 IF p_awd_aim_rec.person_number IS NULL THEN
24 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PER_NUM_NULL');
25 FND_MSG_PUB.ADD;
26 l_val_param := 'INVALID';
27 END IF;
28
29 -- Check if program_cd value is null then log error message
30 IF p_awd_aim_rec.program_cd IS NULL THEN
31 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PRGM_CD_NULL');
32 FND_MSG_PUB.ADD;
33 l_val_param := 'INVALID';
34 -- Check if program_cd is not an upper case value then log error message
35 ELSIF p_awd_aim_rec.program_cd <> UPPER(p_awd_aim_rec.program_cd) THEN
36 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PRGM_CD_UCASE');
37 FND_MSG_PUB.ADD;
38 l_val_param := 'INVALID';
39 END IF;
40
41 -- Check if award_cd value is null then log error message
42 IF p_awd_aim_rec.award_cd IS NULL THEN
43 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_AWD_CD_NULL');
44 FND_MSG_PUB.ADD;
45 l_val_param := 'INVALID';
46 -- Check if award_cd is not an upper case value then log error message
47 ELSIF p_awd_aim_rec.award_cd <> UPPER(p_awd_aim_rec.award_cd) THEN
48 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_AWD_CD_UCASE');
49 FND_MSG_PUB.ADD;
50 l_val_param := 'INVALID';
51 END IF;
52
53 -- Check if start_dt value is null then log error message
54 IF p_awd_aim_rec.start_dt IS NULL THEN
55 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PS_STARDT_NOT_NULL');
56 FND_MSG_PUB.ADD;
57 l_val_param := 'INVALID';
58 END IF;
59
60 -- Check if complete_ind is available and is not 'Y' or 'N' then log error message
61 IF p_awd_aim_rec.complete_ind IS NOT NULL THEN
62 IF p_awd_aim_rec.complete_ind NOT IN('Y','N') THEN
63 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_COMPLT_IND_INVALID');
64 FND_MSG_PUB.ADD;
65 l_val_param := 'INVALID';
66 END IF;
67 END IF;
68
69 -- Check if award_mark is available and is in 0-100 range
70 IF p_awd_aim_rec.award_mark IS NOT NULL THEN
71 IF NOT (p_awd_aim_rec.award_mark >= 0 AND p_awd_aim_rec.award_mark <= 100 ) THEN
72 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_MARK_INV_0_100');
73 FND_MSG_PUB.ADD;
74 l_val_param := 'INVALID';
75 END IF;
76 END IF;
77
78 RETURN l_val_param;
79
80 END validate_parameters;
81
82 FUNCTION validate_db_cons ( p_person_id IN NUMBER,
83 p_awd_aim_rec IN awd_aim_rec_type )
84 RETURN VARCHAR2 AS
85 /*
86 || Created By : nbehera
87 || Created On : 20NOV2002
88 || Purpose : This Procedure Checks for all the database constraints required to
89 || check before insertion of a Award Aims record.
90 || Known limitations, enhancements or remarks :
91 || Change History :
92 || Who When What
93 || (reverse chronological order - newest change first)
94 */
95 CURSOR cur_awd_grd_sch(
96 cp_award_cd igs_ps_awd.award_cd%TYPE,
97 cp_grading_schema_cd igs_ps_awd.grading_schema_cd%TYPE,
98 cp_gs_version_number igs_ps_awd.gs_version_number%TYPE ) IS
99 SELECT 'X'
100 FROM igs_ps_awd
101 WHERE award_cd = cp_award_cd
102 AND grading_schema_cd = cp_grading_schema_cd
103 AND gs_version_number = cp_gs_version_number;
104
105 l_ret_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
106 l_dummy VARCHAR2(1);
107
108 BEGIN
109
110 --Check for duplicate record in the Award Aims Table
111 --If record exists then return the return status as 'W' without checking
112 --for further FK checks
113 IF igs_en_spa_awd_aim_pkg.get_pk_for_validation (
114 x_award_cd => p_awd_aim_rec.award_cd,
115 x_course_cd => p_awd_aim_rec.program_cd,
116 x_person_id => p_person_id ) THEN
117 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_AWD_AIM_EXISTS');
118 FND_MSG_PUB.ADD;
119 l_ret_status := 'W';
120 RETURN l_ret_status;
121 END IF;
122
123 -- FK check in the Student Program Attempt table
124 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
125 x_person_id => p_person_id,
126 x_course_cd => p_awd_aim_rec.program_cd ) THEN
127 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PRGM_ATT_NOT_EXIST');
128 FND_MSG_PUB.ADD;
129 l_ret_status := FND_API.G_RET_STS_ERROR;
130 END IF;
131
132 -- FK check in the Award Code table
133 IF NOT igs_ps_awd_pkg.get_pk_for_validation (
134 x_award_cd => p_awd_aim_rec.award_cd ) THEN
135 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_AWD_NOT_EXIST');
136 FND_MSG_PUB.ADD;
137 l_ret_status := FND_API.G_RET_STS_ERROR;
138 END IF;
139
140 -- Check if grading schema matches one stored at the award
141 IF p_awd_aim_rec.grading_schema_cd IS NOT NULL AND
142 p_awd_aim_rec.gs_version_number IS NOT NULL THEN
143 OPEN cur_awd_grd_sch(
144 p_awd_aim_rec.award_cd,
145 p_awd_aim_rec.grading_schema_cd,
146 p_awd_aim_rec.gs_version_number);
147 FETCH cur_awd_grd_sch INTO l_dummy;
148 IF (cur_awd_grd_sch%NOTFOUND) THEN
149 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GR_AWD_GRD_SCH_NO_MTCH');
150 FND_MSG_PUB.ADD;
151 l_ret_status := FND_API.G_RET_STS_ERROR;
152 END IF;
153 END IF;
154
155 -- Check if award_grade belongs to grading schema
156 IF p_awd_aim_rec.award_grade IS NOT NULL THEN
157 IF p_awd_aim_rec.grading_schema_cd IS NOT NULL AND
158 p_awd_aim_rec.gs_version_number IS NOT NULL THEN
159 IF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
160 x_grading_schema_cd => p_awd_aim_rec.grading_schema_cd,
161 x_version_number => p_awd_aim_rec.gs_version_number,
162 x_grade => p_awd_aim_rec.award_grade )
163 THEN
164 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_GRD_NOT_IN_SCHEMA');
165 FND_MSG_PUB.ADD;
166 l_ret_status := FND_API.G_RET_STS_ERROR;
167 END IF;
168 ELSE
169 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_GRD_NOT_IN_SCHEMA');
170 FND_MSG_PUB.ADD;
171 l_ret_status := FND_API.G_RET_STS_ERROR;
172 END IF;
173 END IF;
174
175 RETURN l_ret_status;
176
177 END validate_db_cons;
178
179 FUNCTION validate_stu_awd_aim ( p_person_id IN NUMBER,
180 p_awd_aim_rec IN awd_aim_rec_type )
181 RETURN VARCHAR2 AS
182 /*
183 || Created By : nbehera
184 || Created On : 20NOV2002
185 || Purpose : This procedure checks all the business validations before
186 || inserting a Award Aims record.
187 || Known limitations, enhancements or remarks :
188 || Change History :
189 || Who When What
190 || (reverse chronological order - newest change first)
191 */
192 l_val_awd_aim VARCHAR2(10) := 'VALID';
193 l_message fnd_new_messages.message_name%TYPE;
194
195 BEGIN
196 --If Start Date and End Date is having value then call the below function to
197 --Check if Start Date is less than or equal to End Date. If not then log error message.
198 IF p_awd_aim_rec.start_dt IS NOT NULL AND p_awd_aim_rec.end_dt IS NOT NULL THEN
199 IF NOT igs_ad_val_edtl.genp_val_strt_end_dt (
200 p_start_dt => p_awd_aim_rec.start_dt,
201 p_end_dt => p_awd_aim_rec.end_dt,
202 p_message_name => l_message) THEN
203 FND_MESSAGE.SET_NAME ('IGS', l_message);
204 FND_MSG_PUB.ADD;
205 l_val_awd_aim := 'INVALID';
206 END IF;
207 END IF;
208
209 --Call the below function to check whether the Award code is offered within the enrolled
210 --program version. If not then log error message.
211 IF NOT igs_en_gen_legacy.validate_awd_offer_pgm (
212 p_person_id => p_person_id,
213 p_program_cd => p_awd_aim_rec.program_cd,
214 p_award_cd => p_awd_aim_rec.award_cd) THEN
215 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_AWD_AVAIL_STU_PGM');
216 FND_MSG_PUB.ADD;
217 l_val_awd_aim := 'INVALID';
218 END IF;
219
220 RETURN l_val_awd_aim;
221
222 END validate_stu_awd_aim;
223
224 PROCEDURE create_student_awd_aim
225 ( p_api_version IN NUMBER,
226 p_init_msg_list IN VARCHAR2,
227 p_commit IN VARCHAR2,
228 p_validation_level IN NUMBER ,
229 p_awd_aim_rec IN awd_aim_rec_type,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2 ) IS
233 /*
234 || Created By : nbehera
235 || Created On : 20NOV2002
236 || Purpose : This procedure inserts records into the Award Aims table after
237 || checking all the validations sequencially as below:
238 || 1. Validation of the parameters.
239 || 2. Deriving values for other parameters
240 || 3. Checking DB Constraints
241 || 4. Checking for other validations
242 || 5. Inserting the record into the table
243 || Known limitations, enhancements or remarks :
244 || Change History :
245 || Who When What
246 || (reverse chronological order - newest change first)
247 */
248
249 l_api_name CONSTANT VARCHAR2(30) := 'create_student_awd_aim';
250 l_api_version CONSTANT NUMBER := 1.0;
251 l_stdnt_awd_aim_status VARCHAR2(10) := 'VALID';
252 l_person_id igs_re_thesis.person_id%TYPE := NULL;
253 l_last_update_date igs_en_spa_awd_aim.creation_date%TYPE;
254 l_last_updated_by igs_en_spa_awd_aim.last_updated_by%TYPE;
255 l_last_update_login igs_en_spa_awd_aim.last_update_login%TYPE;
256
257 BEGIN
258 -- Standard Start of API savepoint
259 SAVEPOINT awd_aim_pub;
260 -- Standard call to check for call compatibility.
261 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version,
262 p_api_version,
263 l_api_name,
264 g_pkg_name ) THEN
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267
268 -- Initialize message list if p_init_msg_list is set to TRUE.
269 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
270 FND_MSG_PUB.INITIALIZE;
271 END IF;
272
273 -- Initialize API return status to success
274 x_return_status := FND_API.G_RET_STS_SUCCESS;
275
276 /****** Validating all the parameters available ******/
277 l_stdnt_awd_aim_status := validate_parameters ( p_awd_aim_rec => p_awd_aim_rec );
278
279 /****** Deriving values for the parameters ******/
280 IF l_stdnt_awd_aim_status <> 'INVALID' THEN
281 -- Calling the function to get the person_id
282 l_person_id := igs_ge_gen_003.get_person_id( p_person_number => p_awd_aim_rec.person_number);
283 -- If person id value returned is NULL then log error message.
284 IF l_person_id IS NULL THEN
285 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
286 FND_MSG_PUB.ADD;
287 l_stdnt_awd_aim_status := 'INVALID';
288 END IF;
289 END IF;
290
291 /****** Check for the Database Constraints for the Award Aims record ******/
292 IF l_stdnt_awd_aim_status <> 'INVALID' THEN
293 x_return_status := validate_db_cons (
294 p_person_id => l_person_id ,
295 p_awd_aim_rec => p_awd_aim_rec );
296 IF x_return_status <> 'S' THEN
297 l_stdnt_awd_aim_status := 'INVALID';
298 END IF;
299 END IF;
300
301 /****** Check the Validation for the Award Aims record ******/
302 IF l_stdnt_awd_aim_status <> 'INVALID' THEN
303 l_stdnt_awd_aim_status := validate_stu_awd_aim (
304 p_person_id => l_person_id,
305 p_awd_aim_rec => p_awd_aim_rec);
306 END IF;
307
308 /****** Insert data into Award Aims table ******/
309 IF l_stdnt_awd_aim_status <> 'INVALID' THEN
310 --Deriving values for the WHO Columns
311 l_last_update_date := SYSDATE;
312
313 l_last_updated_by := FND_GLOBAL.USER_ID;
314 IF l_last_updated_by IS NULL THEN
315 l_last_updated_by := -1;
316 END IF;
317
318 l_last_update_login := FND_GLOBAL.LOGIN_ID;
319 IF l_last_update_login IS NULL THEN
320 l_last_update_login := -1;
321 END IF;
322
323 --insert data into Thesis Details table
324 INSERT INTO igs_en_spa_awd_aim (
325 person_id,
326 course_cd,
327 award_cd,
328 start_dt,
329 end_dt,
330 complete_ind,
331 created_by,
332 creation_date,
333 last_updated_by,
334 last_update_date,
335 last_update_login,
336 conferral_date,
337 award_mark ,
338 award_grade ,
339 grading_schema_cd,
340 gs_version_number )
341 VALUES (
342 l_person_id,
343 p_awd_aim_rec.program_cd,
344 p_awd_aim_rec.award_cd,
345 TRUNC(p_awd_aim_rec.start_dt),
346 p_awd_aim_rec.end_dt,
347 NVL( p_awd_aim_rec.complete_ind, 'N' ),
348 l_last_updated_by,
349 l_last_update_date,
350 l_last_updated_by,
351 l_last_update_date,
352 l_last_update_login,
353 p_awd_aim_rec.conferral_dt,
354 p_awd_aim_rec.award_mark,
355 p_awd_aim_rec.award_grade,
356 p_awd_aim_rec.grading_schema_cd,
357 p_awd_aim_rec.gs_version_number );
358 END IF;
359
360 --If l_stdnt_awd_aim_status is INVALID and x_return_status is not 'W' then
361 --make it to 'E' and rollback if any transaction has happened and not commited.
362 --If l_stdnt_awd_aim_status is VALID then x_return_status is 'S'.
363 IF l_stdnt_awd_aim_status = 'INVALID' THEN
364 IF x_return_status <> 'W' THEN
365 x_return_status := FND_API.G_RET_STS_ERROR ;
366 END IF;
367 ROLLBACK TO awd_aim_pub;
368 ELSIF l_stdnt_awd_aim_status = 'VALID' THEN
369 x_return_status := FND_API.G_RET_STS_SUCCESS ;
370 -- Standard check of p_commit.
371 IF FND_API.TO_BOOLEAN( p_commit ) THEN
372 COMMIT WORK;
373 END IF;
374 END IF;
375
376 -- Standard call to get message count and if count is 1, get message info.
377 FND_MSG_PUB.COUNT_AND_GET ( p_count => x_msg_count,
378 p_data => x_msg_data );
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 ROLLBACK TO awd_aim_pub;
382 x_return_status := FND_API.G_RET_STS_ERROR ;
383 FND_MSG_PUB.COUNT_AND_GET ( p_count => x_msg_count,
384 p_data => x_msg_data );
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 ROLLBACK TO awd_aim_pub;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
388 FND_MSG_PUB.COUNT_AND_GET ( p_count => x_msg_count,
389 p_data => x_msg_data );
390 WHEN OTHERS THEN
391 ROLLBACK TO awd_aim_pub;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
393 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
394 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name,
395 l_api_name );
396 END IF;
397 FND_MSG_PUB.COUNT_AND_GET ( p_count => x_msg_count ,
398 p_data => x_msg_data );
399 END create_student_awd_aim;
400
401 END igs_en_spaa_lgcy_pub;
[Home] [Help]