[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_CLSRNK_LGCY_PUB
Source
1 PACKAGE BODY igs_pr_clsrnk_lgcy_pub AS
2 /* $Header: IGSPPR2B.pls 115.2 2002/11/29 07:51:54 smanglm noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_PR_CLSRNK_LGCY_PUB';
6
7 PROCEDURE initialise ( p_lgcy_clsrnk_rec IN OUT NOCOPY lgcy_clsrnk_rec_type )
8 IS
9 BEGIN
10 p_lgcy_clsrnk_rec.person_number := NULL;
11 p_lgcy_clsrnk_rec.program_cd := NULL;
12 p_lgcy_clsrnk_rec.cohort_name := NULL;
13 p_lgcy_clsrnk_rec.calendar_alternate_code := NULL;
14 p_lgcy_clsrnk_rec.cohort_rank := NULL;
15 p_lgcy_clsrnk_rec.cohort_override_rank := NULL;
16 p_lgcy_clsrnk_rec.comments := NULL;
17 p_lgcy_clsrnk_rec.as_of_rank_gpa := NULL;
18
19 END initialise;
20
21 -- forward declaration of procedure/function used in this package
22
23 /*
24 validate_parameters function checks all the mandatory parameters
25 for the passed record type are not null
26 */
27 FUNCTION validate_parameters
28 (
29 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type
30 )
31 RETURN BOOLEAN;
32
33 /*
34 derive_level_data procedure derives advanced standing unit level data like: -
35 1. Derive Person_id from person_number .
36 2. Derive cal_type and sequence_number from cal_alt_code
37 */
38
39 PROCEDURE derive_clsrnk_data
40 (
41 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type,
42 p_person_id OUT NOCOPY igs_pe_person.person_id%type,
43 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%type,
44 p_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%type
45 );
46
47 /*
48 validate_db_cons function performs all the data integrity validation
49 */
50 FUNCTION validate_db_cons
51 (
52 p_person_id IN igs_pe_person.person_id%type,
53 p_cal_type IN igs_ca_inst.cal_type%type,
54 p_sequence_number IN igs_ca_inst.sequence_number%type,
55 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type
56 )
57 RETURN VARCHAR2;
58
59
60
61
62 /*===================================================================+
63 | PROCEDURE |
64 | create_class_rank |
65 | |
66 | DESCRIPTION |
67 | Creates Class Rank |
68 | |
69 | SCOPE - PUBLIC |
70 | |
71 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
72 | |
73 | ARGUMENTS : IN: |
74 | p_api_version |
75 | p_init_msg_list |
76 | p_commit |
77 | p_lgcy_clsrnk_rec |
78 | OUT: |
79 | x_return_status |
80 | x_msg_count |
81 | x_msg_data |
82 | IN/ OUT: |
83 | |
84 | RETURNS : NONE |
85 | |
86 | NOTES |
87 | |
88 | MODIFICATION HISTORY |
89 | smanglm 11-11-2002 Created |
90 +===================================================================*/
91
92
93 PROCEDURE create_class_rank
94 (p_api_version IN NUMBER,
95 p_init_msg_list IN VARCHAR2 ,
96 p_commit IN VARCHAR2 ,
97 p_validation_level IN VARCHAR2 ,
98 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2
102 )
103 IS
104 l_api_name CONSTANT VARCHAR2(30) := 'create_class_rank';
105 l_api_version CONSTANT NUMBER := 1.0;
106
107 -- variables declared to fetch data from derive_clsrnk_data
108 l_person_id igs_pe_person.person_id%type;
109 l_cal_type igs_ca_inst.cal_type%type;
110 l_sequence_number igs_ca_inst.sequence_number%type;
111
112 l_return VARCHAR2(1) ;
113
114 BEGIN -- main begin
115 --Standard start of API savepoint
116 SAVEPOINT create_class_rank;
117
118 --Standard call to check for call compatibility.
119 IF NOT FND_API.Compatible_API_Call(
120 l_api_version,
121 p_api_version,
122 l_api_name,
123 G_PKG_NAME)
124 THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127
128 --Initialize message list if p_init_msg_list is set to TRUE.
129 IF FND_API.to_Boolean(p_init_msg_list) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 --Initialize API return status to success.
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136
137 -- main code logic begins
138 /*
139 validate the parameters
140 */
141 IF NOT validate_parameters
142 (
143 p_lgcy_clsrnk_rec => p_lgcy_clsrnk_rec
144 ) THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 /*
148 derive the necessary data,
149 if this proc raised any error, exception
150 will be handled in the exception block directly
151 i.e. it will not proceed beyond derive_clsrnk_data
152 */
153 derive_clsrnk_data
154 (
155 p_lgcy_clsrnk_rec => p_lgcy_clsrnk_rec,
156 p_person_id => l_person_id,
157 p_cal_type => l_cal_type,
158 p_sequence_number => l_sequence_number
159 );
160 /*
161 validate db constraints
162 */
163 l_return := validate_db_cons
164 (
165 p_person_id => l_person_id,
166 p_cal_type => l_cal_type,
167 p_sequence_number => l_sequence_number,
168 p_lgcy_clsrnk_rec => p_lgcy_clsrnk_rec
169 );
170 IF l_return = 'S' THEN
171 /*
172 insert into igs_pr_cohinst_rank
173 */
174 INSERT INTO IGS_PR_COHINST_RANK (
175 PERSON_ID ,
176 COURSE_CD ,
177 COHORT_NAME ,
178 LOAD_CAL_TYPE ,
179 LOAD_CI_SEQUENCE_NUMBER,
180 AS_OF_RANK_GPA ,
181 COHORT_RANK ,
182 COHORT_OVERRIDE_RANK ,
183 COMMENTS ,
184 CREATED_BY ,
185 CREATION_DATE ,
186 LAST_UPDATED_BY ,
187 LAST_UPDATE_DATE ,
188 LAST_UPDATE_LOGIN ,
189 REQUEST_ID ,
190 PROGRAM_APPLICATION_ID ,
191 PROGRAM_ID ,
192 PROGRAM_UPDATE_DATE )
193 VALUES (
194 l_person_id,
195 p_lgcy_clsrnk_rec.program_cd,
196 p_lgcy_clsrnk_rec.cohort_name,
197 l_cal_type,
198 l_sequence_number,
199 p_lgcy_clsrnk_rec.as_of_rank_gpa,
200 p_lgcy_clsrnk_rec.cohort_rank,
201 p_lgcy_clsrnk_rec.cohort_override_rank,
202 p_lgcy_clsrnk_rec.comments,
203 NVL(FND_GLOBAL.USER_ID,-1),
204 SYSDATE,
205 NVL(FND_GLOBAL.USER_ID,-1),
206 SYSDATE,
207 NVL(FND_GLOBAL.LOGIN_ID,-1),
208 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
209 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
210 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
211 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE)
212 );
213 ELSE
214 x_return_status := l_return;
215 END IF;
216 --Standard check of p_commit.
217 IF FND_API.to_Boolean(p_commit) AND l_return = 'S' THEN
218 commit;
219 END IF;
220
221 --Standard call to get message count and if count is 1, get message info.
222 FND_MSG_PUB.Count_And_Get(
223 p_count => x_msg_count,
224 p_data => x_msg_data);
225
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228 ROLLBACK TO create_class_rank;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MSG_PUB.Count_And_Get(
231 p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO create_class_rank;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_MSG_PUB.Count_And_Get(
238 p_count => x_msg_count,
239 p_data => x_msg_data);
240 WHEN OTHERS THEN
241 ROLLBACK TO create_class_rank;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
244 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
245 FND_MSG_PUB.ADD;
246 FND_MSG_PUB.Count_And_Get(
247 p_count => x_msg_count,
248 p_data => x_msg_data);
249
250 END create_class_rank;
251
252
253 FUNCTION validate_parameters
254 (
255 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type
256 )
257 RETURN BOOLEAN
258 /*************************************************************
259 Created By : smanglm
260 Date Created on : 2002/11/13
261 Purpose : validate_parameters function checks all the mandatory
262 parameters for the passed record type are not null
263 Know limitations, enhancements or remarks
264 Change History
265 Who When What
266 (reverse chronological order - newest change first)
267 ***************************************************************/
268 IS
269 x_return_status BOOLEAN;
270 BEGIN
271 x_return_status := TRUE;
272
273 IF p_lgcy_clsrnk_rec.person_number IS NULL THEN
274 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PER_NUM_NULL');
275 FND_MSG_PUB.ADD;
276 x_return_status := FALSE;
277 END IF;
278 IF p_lgcy_clsrnk_rec.program_cd IS NULL THEN
279 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRGM_CD_NULL');
280 FND_MSG_PUB.ADD;
281 x_return_status := FALSE;
282 END IF;
283 IF p_lgcy_clsrnk_rec.calendar_alternate_code IS NULL THEN
284 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_CAL_ALT_CD_NULL');
285 FND_MSG_PUB.ADD;
286 x_return_status := FALSE;
287 END IF;
288 IF p_lgcy_clsrnk_rec.cohort_rank IS NULL OR p_lgcy_clsrnk_rec.cohort_rank <=0 THEN
289 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_RANK_NULL');
290 FND_MSG_PUB.ADD;
291 x_return_status := FALSE;
292 END IF;
293 IF p_lgcy_clsrnk_rec.cohort_name IS NULL THEN
294 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_CHRT_NAME_NULL');
295 FND_MSG_PUB.ADD;
296 x_return_status := FALSE;
297 END IF;
298 IF p_lgcy_clsrnk_rec.as_of_rank_gpa IS NULL OR p_lgcy_clsrnk_rec.as_of_rank_gpa < 0 THEN
299 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_GPA_NULL');
300 FND_MSG_PUB.ADD;
301 x_return_status := FALSE;
302 END IF;
303 IF p_lgcy_clsrnk_rec.cohort_override_rank IS NOT NULL AND
304 p_lgcy_clsrnk_rec.cohort_override_rank <= 0 THEN
305 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_RANK_NULL');
306 FND_MSG_PUB.ADD;
307 x_return_status := FALSE;
308 END IF;
309
310 /*
311 return the value of x_return_status
312 */
313 return x_return_status;
314 END validate_parameters;
315
316
317 PROCEDURE derive_clsrnk_data
318 (
319 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type,
320 p_person_id OUT NOCOPY igs_pe_person.person_id%type,
321 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%type,
322 p_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%type
323 )
324 IS
325 /*************************************************************
326 Created By : smanglm
327 Date Created on : 2002/11/13
328 Purpose :
329 derive_clsrnk_data procedure derives data like: -
330 1. Derive Person_id from person_number .
331 2. Derive cal_type and sequence_number from cal_alt_code
332 Know limitations, enhancements or remarks
333 Change History
334 Who When What
335 (reverse chronological order - newest change first)
336 ***************************************************************/
337 x_return_status BOOLEAN;
338 BEGIN
339 x_return_status := TRUE;
340 /*
341 get person_id
342 */
343 p_person_id := IGS_GE_GEN_003.GET_PERSON_ID(p_lgcy_clsrnk_rec.person_number);
344 IF p_person_id IS NULL THEN
345 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
346 FND_MSG_PUB.ADD;
347 x_return_status := FALSE;
348 END IF;
349 /*
350 get cal_type and sequence_number
351 */
352 DECLARE
353 l_start_dt igs_ca_inst.start_dt%TYPE;
354 l_end_dt igs_ca_inst.end_dt%TYPE;
355 l_return_status VARCHAR2(2000);
356 BEGIN
357 IGS_GE_GEN_003.GET_CALENDAR_INSTANCE
358 (
359 P_ALTERNATE_CD => p_lgcy_clsrnk_rec.calendar_alternate_code,
360 P_S_CAL_CATEGORY => NULL,
361 P_CAL_TYPE => p_cal_type,
362 P_CI_SEQUENCE_NUMBER => p_sequence_number,
363 P_START_DT => l_start_dt,
364 P_END_DT => l_end_dt,
365 P_RETURN_STATUS => l_return_status
366 );
367 IF l_return_status = 'INVALID' THEN
368 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_NO_ALT_CODE');
369 FND_MSG_PUB.ADD;
370 x_return_status := FALSE;
371 ELSIF l_return_status = 'MULTIPLE' THEN
372 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_MULTI_ALT_CODE');
373 FND_MSG_PUB.ADD;
374 x_return_status := FALSE;
375 END IF;
376 END;
377
378 IF NOT x_return_status THEN
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381
382 END derive_clsrnk_data;
383
384 FUNCTION validate_db_cons
385 (
386 p_person_id IN igs_pe_person.person_id%type,
387 p_cal_type IN igs_ca_inst.cal_type%type,
388 p_sequence_number IN igs_ca_inst.sequence_number%type,
389 p_lgcy_clsrnk_rec IN lgcy_clsrnk_rec_type
390 )
391 RETURN VARCHAR2
392 /*************************************************************
393 Created By : smanglm
394 Date Created on : 2002/11/13
395 Purpose : validate_db_cons function performs
396 all the data integrity validation
397 Know limitations, enhancements or remarks
398 Change History
399 Who When What
400 (reverse chronological order - newest change first)
401 ***************************************************************/
402 IS
403 x_return_status VARCHAR2(1);
404 BEGIN
405 x_return_status := 'S';
406 /*
407 check whether cohort inst exists or not
408 */
409 IF NOT IGS_PR_COHORT_INST_PKG.GET_PK_FOR_VALIDATION
410 (
411 x_cohort_name => p_lgcy_clsrnk_rec.cohort_name,
412 x_load_cal_type => p_cal_type,
413 x_load_ci_sequence_number => p_sequence_number
414 ) THEN
415 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_COHINST_NOT_EXIST');
416 FND_MSG_PUB.ADD;
417 x_return_status := 'E';
418 END IF;
419 /*
420 check whether program attempt exists or not
421 */
422 IF NOT IGS_EN_STDNT_PS_ATT_PKG.GET_PK_FOR_VALIDATION
423 (
424 x_person_id => p_person_id,
425 x_course_cd => p_lgcy_clsrnk_rec.program_cd
426 ) THEN
427 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRGM_ATT_NOT_EXIST');
428 FND_MSG_PUB.ADD;
429 x_return_status := 'E';
430 END IF;
431 /*
432 Check if the student already has already been ranked for the cohort instance.
433 If yes, then set the message IGS_PR_RANK_EXIST into message stack and return
434 from the function with return value 'W', as the record already exists in
435 the system, so no need to do other validations
436 */
437 IF IGS_PR_COHINST_RANK_PKG.GET_PK_FOR_VALIDATION
438 (
439 x_person_id => p_person_id,
440 x_course_cd => p_lgcy_clsrnk_rec.program_cd,
441 x_cohort_name => p_lgcy_clsrnk_rec.cohort_name,
442 x_load_cal_type => p_cal_type,
443 x_load_ci_sequence_number => p_sequence_number
444 ) THEN
445 FND_MESSAGE.SET_NAME('IGS', 'IGS_PR_RANK_EXIST');
446 FND_MSG_PUB.ADD;
447 x_return_status := 'W';
448 END IF;
449
450 return x_return_status;
451 END validate_db_cons;
452
453 END igs_pr_clsrnk_lgcy_pub;