[Home] [Help]
PACKAGE BODY: APPS.IGS_RATINGS_PUB
Source
1 PACKAGE BODY igs_ratings_pub AS
2 /* $Header: IGSPRATB.pls 120.0 2005/07/05 12:46:47 appldev noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2 (30):='IGS_RATINGS_PUB';
4
5 PROCEDURE check_length(p_param_name IN VARCHAR2, p_table_name IN VARCHAR2, p_param_length IN NUMBER) AS
6 CURSOR c_col_length IS
7 SELECT WIDTH , precision , column_type
8 FROM FND_COLUMNS
9 WHERE table_id IN
10 (SELECT TABLE_ID
11 FROM FND_TABLES
12 WHERE table_name = p_table_name AND APPLICATION_ID = 8405)
13 AND column_name = p_param_name
14 AND APPLICATION_ID = 8405;
15
16 l_col_length c_col_length%ROWTYPE;
17 BEGIN
18 OPEN c_col_length;
19 FETCH c_col_length INTO l_col_length;
20 CLOSE c_col_length;
21 IF l_col_length.column_type = 'V' AND p_param_length > l_col_length.width THEN
22 --- DBMS_OUTPUT.PUT_LINE('failure ' || l_col_length.width);
23 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
24 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
25 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.width);
26 IGS_GE_MSG_STACK.ADD;
27 RAISE FND_API.G_EXC_ERROR;
28
29
30 ELSIF l_col_length.column_type ='N' AND p_param_length > l_col_length.precision THEN
31 -- DBMS_OUTPUT.PUT_LINE('failure ' || l_col_length.precision);
32 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EXCEED_MAX_LENGTH');
33 FND_MESSAGE.SET_TOKEN('PARAMETER',p_param_name);
34 FND_MESSAGE.SET_TOKEN('LENGTH',l_col_length.precision);
35 IGS_GE_MSG_STACK.ADD;
36 RAISE FND_API.G_EXC_ERROR;
37 END IF;
38
39 END check_length;
40
41 PROCEDURE rec_pgm_approval
42 (
43 --Standard Parameters Start
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
46 p_commit IN VARCHAR2 default FND_API.G_FALSE,
47 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 --Standard parameter ends
52 p_person_id IN NUMBER,
53 p_admission_appl_number IN NUMBER,
54 p_nominated_program_cd IN VARCHAR2,
55 p_sequence_number IN NUMBER,
56 p_pgm_approver_id IN NUMBER,
57 p_program_approval_date IN DATE,
58 p_program_approval_status IN VARCHAR2,
59 p_approval_notes IN VARCHAR2
60
61 )
62 IS
63 CURSOR c_pgm_appr IS
64 SELECT rowid row_id, appl_pgmapprv_id
65 FROM Igs_Ad_Appl_Pgmapprv a
66 WHERE person_id = p_person_id
67 AND admission_appl_number = p_admission_appl_number
68 AND nominated_course_cd = p_nominated_program_cd
69 AND sequence_number = p_sequence_number
70 AND pgm_approver_id = p_pgm_approver_id ;
71
72 l_pgm_appr Igs_Ad_Appl_Pgmapprv%ROWTYPE;
73 l_api_version CONSTANT NUMBER := 1.0;
74 l_api_name CONSTANT VARCHAR2(30) := 'REC_PGM_APPROVAL';
75 l_msg_index NUMBER := 0;
76 l_return_status VARCHAR2(1);
77 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
78 lv_rowid ROWID;
79 l_appl_pgmapprv_id NUMBER;
80
81 BEGIN
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 SAVEPOINT Rec_Pgm_Approval_PUB;
85 -- Standard call to check for call compatibility.
86 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89 -- Initialize message list if p_init_msg_list is set to TRUE.
90 IF FND_API.to_Boolean( p_init_msg_list ) THEN
91 FND_MSG_PUB.initialize;
92 END IF;
93 l_msg_index := igs_ge_msg_stack.count_msg;
94
95 -- P_PERSON_ID
96 check_length('PERSON_ID', 'IGS_AD_APPL_PGMAPPRV', length(p_person_id));
97 -- P_ADMISSION_APPL_NUMBER
98 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_APPL_PGMAPPRV', length(p_admission_appl_number));
99 -- p_nominated_program_cd
100 check_length('NOMINATED_COURSE_CD', 'IGS_AD_APPL_PGMAPPRV', length(p_nominated_program_cd));
101 -- P_SEQUENCE_NUMBER
102 check_length('SEQUENCE_NUMBER', 'IGS_AD_APPL_PGMAPPRV', length(p_sequence_number));
103 -- P_PGM_APPROVER_ID
104 check_length('PGM_APPROVER_ID', 'IGS_AD_APPL_PGMAPPRV', length(p_pgm_approver_id));
105 -- P_PROGRAM_APPROVAL_STATUS
106 check_length('PROGRAM_APPROVAL_STATUS', 'IGS_AD_APPL_PGMAPPRV', length(p_program_approval_status));
107 -- P_APPROVAL_NOTES
108 check_length('APPROVAL_NOTES', 'IGS_AD_APPL_PGMAPPRV', length(p_approval_notes));
109
110 OPEN c_pgm_appr;
111 FETCH c_pgm_appr INTO lv_rowid,l_appl_pgmapprv_id;
112 CLOSE c_pgm_appr;
113 IF l_appl_pgmapprv_id is NULL THEN
114 -- Initialize API return status to success
115 Igs_Ad_Appl_Pgmapprv_Pkg.Insert_Row (
116 X_ROWID => lv_rowid,
117 x_APPL_PGMAPPRV_ID => l_appl_pgmapprv_id ,
118 x_PERSON_ID => p_person_id,
119 x_ADMISSION_APPL_NUMBER => p_admission_appl_number,
120 x_NOMINATED_COURSE_CD => p_nominated_program_cd,
121 x_SEQUENCE_NUMBER => p_sequence_number,
122 x_PGM_APPROVER_ID => p_pgm_approver_id,
123 x_ASSIGN_TYPE => 'M',
124 x_ASSIGN_DATE => SYSDATE,
125 x_PROGRAM_APPROVAL_DATE => p_program_approval_date,
126 x_PROGRAM_APPROVAL_STATUS => p_program_approval_status,
127 x_APPROVAL_NOTES => p_approval_notes,
128 X_Mode => 'R'
129 );
130 ELSE
131 Igs_Ad_Appl_Pgmapprv_Pkg.update_row (
132 X_ROWID => lv_rowid,
133 x_APPL_PGMAPPRV_ID => l_appl_pgmapprv_id,
134 x_PERSON_ID => p_person_id,
135 x_ADMISSION_APPL_NUMBER => p_admission_appl_number,
136 x_NOMINATED_COURSE_CD => p_nominated_program_cd,
137 x_SEQUENCE_NUMBER => p_sequence_number,
138 x_PGM_APPROVER_ID => p_pgm_approver_id,
139 x_ASSIGN_TYPE => 'M',
140 x_ASSIGN_DATE => SYSDATE,
141 x_PROGRAM_APPROVAL_DATE => p_program_approval_date,
142 x_PROGRAM_APPROVAL_STATUS => p_program_approval_status,
143 x_APPROVAL_NOTES => p_approval_notes,
144 X_Mode => 'R');
145 END IF;
146
147 --DBMS_OUTPUT.PUT_LINE('EXCEPTION BLOCK : After extracting XDATA :-' );
148
149 -- Standard check of p_commit.
150 IF FND_API.To_Boolean( p_commit ) THEN
151 COMMIT;
152 END IF;
153 -- End of Procedure
154 EXCEPTION
155 WHEN FND_API.G_EXC_ERROR THEN
156 ROLLBACK TO Rec_Pgm_Approval_PUB;
157 x_return_status := FND_API.G_RET_STS_ERROR ;
158 -- Add message to API message list.
159 igs_ad_gen_016.extract_msg_from_stack (
160 p_msg_at_index => l_msg_index,
161 p_return_status => l_return_status,
162 p_msg_count => x_msg_count,
163 p_msg_data => x_msg_data,
164 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
165 x_msg_count := x_msg_count -1 ;
166 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-1).text;
167
168
169 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
170 x_return_status := FND_API.G_RET_STS_ERROR ;
171 ELSE
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 END IF;
174
175 WHEN OTHERS THEN
176 ROLLBACK TO Rec_Pgm_Approval_PUB;
177 x_return_status := FND_API.G_RET_STS_ERROR ;
178 -- Add message to API message list.
179 igs_ad_gen_016.extract_msg_from_stack (
180 p_msg_at_index => l_msg_index,
181 p_return_status => l_return_status,
182 p_msg_count => x_msg_count,
183 p_msg_data => x_msg_data,
184 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
185 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
186 x_return_status := FND_API.G_RET_STS_ERROR ;
187 ELSE
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 END IF;
190 END rec_pgm_approval;
191
192 PROCEDURE ASSIGN_EVALUATORS_TO_AI (
193 --Standard Parameters Start
194 p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
196 p_commit IN VARCHAR2 default FND_API.G_FALSE,
197 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
198
199 --Standard parameter ends
200
201 p_person_id IN igs_ad_appl_arp_v.PERSON_ID%TYPE ,
202 p_admission_appl_number IN igs_ad_appl_arp_v.ADMISSION_APPL_NUMBER%TYPE ,
203 p_nominated_program_cd IN igs_ad_appl_arp_v.NOMINATED_COURSE_CD%TYPE,
204 p_sequence_number IN igs_ad_appl_arp_v.SEQUENCE_NUMBER%TYPE,
205 p_appl_rev_profile_id IN igs_ad_appl_arp_v.APPL_REV_PROFILE_ID%TYPE,
206 p_appl_revprof_revgr_id IN igs_ad_appl_arp_v.APPL_REVPROF_REVGR_ID%TYPE,
207
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2
211 ) AS
212
213 -- IF API then add API comments here...rsharma and add standard parameters
214 /*************************************************************
215 Created By : Rsharma
216 Date Created By :
217 Purpose :
218 Know limitations, enhancements or remarks
219 Change History
220 Who When What
221
222 (reverse chronological order - newest change first)
223 ***************************************************************/
224 CURSOR dup_eval_cur(
225 l_person_id igs_ad_appl_eval.person_id%TYPE,
226 l_adm_apl_num igs_ad_appl_eval.admission_appl_number%TYPE,
227 l_nom_crs_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
228 l_seq_number igs_ad_appl_eval.sequence_number%TYPE)
229 IS
230 SELECT count(*), rating_type_id, rating_scale_id,evaluator_id
231 FROM igs_ad_appl_eval
232 WHERE person_id = l_person_id
233 AND admission_appl_number = l_adm_apl_num
234 AND nominated_course_cd = l_nom_crs_cd
235 AND sequence_number = l_seq_number
236 GROUP BY rating_type_id, rating_scale_id,evaluator_id
237 HAVING count(*) > 1;
238
239 /* Cursor to get the Evaluator records which are duplicates */
240 CURSOR del_dup_eval_cur(
241 l_rating_type_id igs_ad_appl_eval.rating_type_id%TYPE,
242 l_evaluator_id igs_ad_appl_eval.evaluator_id%TYPE,
243 l_person_id igs_ad_appl_eval.person_id%TYPE,
244 l_adm_apl_num igs_ad_appl_eval.admission_appl_number%TYPE,
245 l_nom_crs_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
246 l_seq_number igs_ad_appl_eval.sequence_number%TYPE)
247 IS
248 SELECT rowid
249 FROM igs_ad_appl_eval
250 WHERE rating_type_id = l_rating_type_id
251 AND evaluator_id = l_evaluator_id
252 AND person_id = l_person_id
253 AND admission_appl_number = l_adm_apl_num
254 AND nominated_course_cd = l_nom_crs_cd
255 AND sequence_number = l_seq_number
256 AND rowid <>
257 (SELECT max(rowid)
258 FROM igs_ad_appl_eval
259 WHERE rating_type_id = l_rating_type_id
260 AND evaluator_id = l_evaluator_id
261 AND person_id = l_person_id
262 AND admission_appl_number = l_adm_apl_num
263 AND nominated_course_cd = l_nom_crs_cd
264 AND sequence_number = l_seq_number
265 );
266
267
268 CURSOR c_get_current_eval(
269 l_person_id igs_ad_appl_eval.person_id%TYPE,
270 l_adm_apl_num igs_ad_appl_eval.admission_appl_number%TYPE,
271 l_nom_crs_cd igs_ad_appl_eval.nominated_course_cd%TYPE,
272 l_seq_num igs_ad_appl_eval.sequence_number%TYPE
273 ) IS
274 SELECT 'X'
275 FROM igs_ad_appl_eval
276 WHERE person_id = l_person_id
277 AND admission_appl_number = l_adm_apl_num
278 AND nominated_course_cd = l_nom_crs_cd
279 AND sequence_number = l_seq_num ;
280
281
282 CURSOR c_aplinst_cur IS
283 SELECT a.ROWID, a.*
284 FROM igs_ad_ps_appl_inst a
285 WHERE person_id = p_person_id
286 AND admission_appl_number = p_admission_appl_number
287 AND nominated_course_cd = p_nominated_program_cd
288 AND sequence_number = p_sequence_number;
289
290 CURSOR eval_type_cur( l_appl_rev_profile_id igs_ad_apl_rev_prf_all.appl_rev_profile_id%TYPE)
291 IS
292 SELECT distinct sequential_concurrent_ind
293 FROM igs_ad_apl_rev_prf_all
294 WHERE appl_rev_profile_id = l_appl_rev_profile_id;
295
296 CURSOR c_get_appl_rev_profile_id (
297 l_person_id igs_ad_appl_arp.person_id%TYPE,
298 l_adm_apl_num igs_ad_appl_arp.admission_appl_number%TYPE,
299 l_nom_crs_cd igs_ad_appl_arp.nominated_course_cd%TYPE,
300 l_seq_num igs_ad_appl_arp.sequence_number%TYPE
301 ) IS
302 SELECT *
303 FROM igs_ad_appl_arp_v
304 WHERE person_id = l_person_id
305 AND admission_appl_number = l_adm_apl_num
306 AND nominated_course_cd = l_nom_crs_cd
307 AND sequence_number = l_seq_num ;
308
309 CURSOR get_rating_cur(
310 l_person_id igs_ad_appl_eval_v.person_id%TYPE,
311 l_adm_apl_num igs_ad_appl_eval_v.admission_appl_number%TYPE,
312 l_nom_crs_cd igs_ad_appl_eval_v.nominated_course_cd%TYPE,
313 l_seq_num igs_ad_appl_eval_v.sequence_number%TYPE
314 ) IS
315 SELECT rating
316 FROM igs_ad_appl_eval_v
317 WHERE person_id = l_person_id
318 AND admission_appl_number = l_adm_apl_num
319 AND nominated_course_cd = l_nom_crs_cd
320 AND sequence_number = l_seq_num
321 AND rating IS NOT NULL;
322
323 CURSOR doc_cur(
324 l_person_id igs_ad_appl_arp.person_id%TYPE,
325 l_adm_apl_num igs_ad_appl_arp.admission_appl_number%TYPE,
326 l_nom_crs_cd igs_ad_appl_arp.nominated_course_cd%TYPE,
327 l_seq_number igs_ad_appl_arp.sequence_number%TYPE)
328 IS
329 SELECT
330 doc.s_adm_doc_status
331 FROM
332 igs_ad_ps_appl_inst apl, /* Replaced igs_ad_ps_appl_inst_aplinst_v with igs_ad_ps_appl_inst Bug 3150054 */
333 igs_ad_doc_stat doc
334 WHERE person_id = l_person_id
335 AND admission_appl_number = l_adm_apl_num
336 AND nominated_course_cd = l_nom_crs_cd
337 AND sequence_number = l_seq_number
338 AND doc.adm_doc_status = apl.adm_doc_status;
339
340 l_adm_doc igs_ad_ps_appl_inst.adm_doc_status%TYPE;
341 l_rating igs_ad_appl_eval_v.rating%TYPE;
342 l_exist_arp igs_ad_appl_arp_v%ROWTYPE;
343 l_modif_eval_type igs_ad_apl_rev_prf_all.sequential_concurrent_ind%TYPE;
344 l_exist_eval_type igs_ad_apl_rev_prf_all.sequential_concurrent_ind%TYPE;
345 l_get_current_eval VARCHAR2(1);
346 l_c_aplinst_cur c_aplinst_cur%ROWTYPE;
347 l_api_version CONSTANT NUMBER := 1.0;
348 l_api_name CONSTANT VARCHAR2(30) := 'ASSIGN_EVALUATORS_TO_AI';
349 l_msg_index NUMBER := 0;
350 l_return_status VARCHAR2(1);
351 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
352
353 l_appl_arp_id igs_ad_appl_arp.appl_arp_id%TYPE ;
354 lv_rowid VARCHAR2(25) ;
355 l_errbuf VARCHAR2(100);
356 l_retcode NUMBER;
357 BEGIN
358
359 SAVEPOINT ASSIGN_EVALUATORS_TO_AI_PUB;
360 l_msg_index := igs_ge_msg_stack.count_msg;
361 -- Standard call to check for call compatibility.
362 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
363 RAISE FND_API.G_EXC_ERROR ;
364 END IF;
365 -- Initialize message list if p_init_msg_list is set to TRUE.
366 IF FND_API.to_Boolean( p_init_msg_list ) THEN
367 FND_MSG_PUB.initialize;
368 END IF;
369
370 -- P_PERSON_ID
371 check_length('PERSON_ID', 'IGS_AD_APPL_ARP', length(p_person_id));
372 -- P_ADMISSION_APPL_NUMBER
373 check_length('ADMISSION_APPL_NUMBER', 'IGS_AD_APPL_ARP', length(p_admission_appl_number));
374 -- p_nominated_program_cd
375 check_length('NOMINATED_COURSE_CD', 'IGS_AD_APPL_ARP', length(p_nominated_program_cd));
376 -- P_SEQUENCE_NUMBER
377 check_length('SEQUENCE_NUMBER', 'IGS_AD_APPL_ARP', length(p_sequence_number));
378 -- P_APPL_REV_PROFILE_ID
379 check_length('APPL_REV_PROFILE_ID', 'IGS_AD_APPL_ARP', length(p_appl_rev_profile_id));
380 -- P_APPL_REVPROF_REVGR_ID
381 check_length('APPL_REVPROF_REVGR_ID', 'IGS_AD_APPL_ARP', length(p_appl_revprof_revgr_id));
382
383 -- Initialize API return status to success
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385 -- Check whether any application is available in OSS to update outcome status
386 -- if the corresponding application is not there , then update the interface record with appropriate error code
387 OPEN c_aplinst_cur;
388 FETCH c_aplinst_cur
389 INTO l_c_aplinst_cur;
390 CLOSE c_aplinst_cur;
391 IF l_c_aplinst_cur.person_id IS NULL THEN
392 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
393 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
394 IGS_GE_MSG_STACK.ADD;
395 RAISE FND_API.G_EXC_ERROR;
396 END IF;
397 --Get the Evaluation Type associated with the entered Application Review Profile Name
398 OPEN eval_type_cur(p_appl_rev_profile_id);
399 FETCH eval_type_cur INTO l_modif_eval_type;
400 CLOSE eval_type_cur;
401
402 --Get the Evaluation Type assocoated with the existing Review Profile Name
403
404 OPEN c_get_appl_rev_profile_id (
405 p_person_id,
406 p_admission_appl_number,
407 p_nominated_program_cd,
408 p_sequence_number);
409 FETCH c_get_appl_rev_profile_id INTO l_exist_arp;
410 CLOSE c_get_appl_rev_profile_id;
411
412 IF l_exist_arp.appl_rev_profile_id IS NOT NULL THEN
413 FND_MESSAGE.SET_NAME('IGS','IGS_AD_REV_PRF_EXISTS');
414 IGS_GE_MSG_STACK.ADD;
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417
418 /* The following code is to handle the update of the recview profile record.
419 OPEN eval_type_cur(l_exist_arp.appl_rev_profile_id);
420 FETCH eval_type_cur INTO l_exist_eval_type;
421 CLOSE eval_type_cur;
422
423
424 -- Review Group and Review Profile is already attached and evaluation type is
425 IF l_exist_eval_type IS NOT NULL AND l_exist_eval_type <> l_modif_eval_type THEN
426 fnd_message.set_name('IGS', 'IGS_AD_DIFF_EVAL_TYPE');
427 IGS_GE_MSG_STACK.ADD;
428 RAISE FND_API.G_EXC_ERROR;
429 ELSIF l_exist_eval_type IS NOT NULL THEN
430 OPEN get_rating_cur(
431 p_person_id,
432 p_admission_appl_number,
433 p_nominated_program_cd,
434 p_sequence_number
435 );
436 FETCH get_rating_cur INTO l_rating;
437 -- If rating is defined for atleast one of the evaluator records then update is not allowed
438 IF (get_rating_cur%FOUND) THEN
439 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CANT_UPD_RAT_PRSNT');
440 IGS_GE_MSG_STACK.ADD;
441 CLOSE get_rating_cur;
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444 CLOSE get_rating_cur;
445 ELSE -- Evaluation Type is null
446 -- This cursor fetches the current evaluators that are assigned
447 -- If evaluators are already assigned but no review profile has been assigned , then the evaluation type
448 -- is set to No Review Group
449
450 OPEN c_get_current_eval(
451 p_person_id,
452 p_admission_appl_number,
453 p_nominated_program_cd,
454 p_sequence_number);
455 FETCH c_get_current_eval INTO l_get_current_eval;
456 CLOSE c_get_current_eval;
457
458 IF l_get_current_eval IS NOT NULL THEN
459 IF (l_modif_eval_type = 'S') THEN
460 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DIFF_EVAL_TYPE');
461 IGS_GE_MSG_STACK.ADD;
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464 END IF;
465 END IF; */
466 /* Get the System Doc Status associated with the Application Instance */
467 OPEN doc_cur(
468 p_person_id,
469 p_admission_appl_number,
470 p_nominated_program_cd,
471 p_sequence_number
472 );
473 FETCH doc_cur INTO l_adm_doc;
474 CLOSE doc_cur;
475 --Validtion to check Outcome Status = PENDING is already present in TBH.
476 -- The following to check the documentation status is satisfied or NOT.
477 IF l_adm_doc <> 'SATISFIED' THEN
478 FND_MESSAGE.SET_NAME('IGS','IGS_AD_OTCM_DOC_STAT');
479 IGS_GE_MSG_STACK.ADD;
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482
483 BEGIN
484 IF l_exist_eval_type IS NULL THEN
485 -- insert row of Review Proifile
486 igs_ad_appl_arp_pkg.insert_row (
487 x_mode => 'I',
488 x_rowid => lv_rowid,
489 x_appl_arp_id => l_appl_arp_id,
490 x_person_id => p_PERSON_ID,
491 x_admission_appl_number => p_ADMISSION_APPL_NUMBER,
492 x_nominated_course_cd => p_nominated_program_cd,
493 x_sequence_number => p_SEQUENCE_NUMBER,
494 x_appl_rev_profile_id => p_APPL_REV_PROFILE_ID,
495 x_appl_revprof_revgr_id => p_APPL_REVPROF_REVGR_ID
496 );
497 ELSE -- this case will never happen as we are not allowing the update of review profile
498 -- insert row of Review Proifile
499 igs_ad_appl_arp_pkg.update_Row (
500 x_mode => 'I',
501 x_rowid => l_exist_arp.row_id,
502 x_appl_arp_id => l_exist_arp.appl_arp_id,
503 x_person_id => p_PERSON_ID,
504 x_admission_appl_number => p_ADMISSION_APPL_NUMBER,
505 x_nominated_course_cd => p_nominated_program_cd,
506 x_sequence_number => p_SEQUENCE_NUMBER,
507 x_appl_rev_profile_id => p_APPL_REV_PROFILE_ID,
508 x_appl_revprof_revgr_id => p_APPL_REVPROF_REVGR_ID
509 );
510 END IF;
511
512
513 -- Call to evaluator assigning job
514 igs_ad_assign_eval_ai_pkg.Assign_Eval_To_Ai(
515 l_Errbuf ,
516 l_Retcode ,
517 p_appl_rev_profile_id ,
518 p_appl_revprof_revgr_id ,
519 p_person_id ,
520 p_admission_appl_number ,
521 p_nominated_program_cd ,
522 p_sequence_number
523 );
524
525 FOR dup_rec IN dup_eval_cur(
526 p_PERSON_ID,
527 p_ADMISSION_APPL_NUMBER,
528 p_nominated_program_cd,
529 p_SEQUENCE_NUMBER)
530 LOOP
531 FOR del_dup_rec IN del_dup_eval_cur(dup_rec.rating_type_id,
532 dup_rec.evaluator_id,
533 p_PERSON_ID,
534 p_ADMISSION_APPL_NUMBER,
535 p_nominated_program_cd,
536 p_SEQUENCE_NUMBER)
537 LOOP
538 igs_ad_appl_eval_pkg.delete_row(del_dup_rec.rowid);
539 END LOOP;
540 END LOOP;
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 ROLLBACK TO ASSIGN_EVALUATORS_TO_AI_PUB;
545 igs_ad_gen_016.extract_msg_from_stack (
546 p_msg_at_index => l_msg_index,
547 p_return_status => l_return_status,
548 p_msg_count => x_msg_count,
549 p_msg_data => x_msg_data,
550 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
551 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
552 x_return_status := FND_API.G_RET_STS_ERROR ;
553 ELSE
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 END IF;
556 END;
557
558
559
560
561
562
563
564
565 EXCEPTION
566 WHEN FND_API.G_EXC_ERROR THEN
567 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in API: FND_API.G_EXC_ERROR : '||SQLERRM);
568 x_return_status := FND_API.G_RET_STS_ERROR ;
569 ROLLBACK TO ASSIGN_EVALUATORS_TO_AI_PUB;
570 igs_ad_gen_016.extract_msg_from_stack (
571 p_msg_at_index => l_msg_index,
572 p_return_status => l_return_status,
573 p_msg_count => x_msg_count,
574 p_msg_data => x_msg_data,
575 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
576 x_msg_data := l_hash_msg_name_text_type_tab(x_msg_count-2).text;
577 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'aFTER STACK Exception in API: FND_API.G_EXC_ERROR : '|| l_hash_msg_name_text_type_tab(x_msg_count-2).text);
578
579 WHEN OTHERS THEN
580 ROLLBACK TO ASSIGN_EVALUATORS_TO_AI_PUB;
581 igs_ad_gen_016.extract_msg_from_stack (
582 p_msg_at_index => l_msg_index,
583 p_return_status => l_return_status,
584 p_msg_count => x_msg_count,
585 p_msg_data => x_msg_data,
586 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
587 IF l_hash_msg_name_text_type_tab(x_msg_count-1).name <> 'ORA' THEN
588 x_return_status := FND_API.G_RET_STS_ERROR ;
589 ELSE
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 END IF;
592
593 END ASSIGN_EVALUATORS_TO_AI;
594
595 END igs_ratings_pub;