1 PACKAGE BODY igs_re_the_lgcy_pub AS
2 /* $Header: IGSRE20B.pls 115.9 2002/12/30 09:19:42 nbehera noship $ */
3
4 -- Who When What
5 -- (reverse chronological order - newest change first)
6 -- Nishikant 27DEC2002 Bug#2722106. If all the fields submission_dt,thesis_result_cd,thesis_exam_type
7 -- and thesis_panel_type are having NULL value then dont create the thesis exam record.
8 -- In this case only Thesis Details record will be created of 'PENDING' status.
9 -- If any of these field value is available then proceed to insert the Thesis Exam Record,
10 -- after the bussiness validation and db_cons validations for the the Exam record will be
11 -- done successfully.
12
13
14 -- Global variable which contains the package name.
15 g_pkg_name CONSTANT VARCHAR2(30):='IGS_RE_THE_LGCY_PUB';
16
17 PROCEDURE validate_parameters(
18 p_the_dtls_rec IN the_dtls_rec_type ,
19 p_the_status OUT NOCOPY VARCHAR2) IS
20 /*
21 || Created By : nbehera
22 || Created On : 14-NOV-2002
23 || Purpose : This procedure validates all the fields in the parameter p_the_dtls_rec
24 || which require some validation
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30 l_msg_count NUMBER := 0;
31 BEGIN
32 p_the_status := 'VALID';
33 -- Check for value exists for person number
34 IF p_the_dtls_rec.person_number IS NULL THEN
35 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
36 FND_MSG_PUB.ADD;
37 p_the_status := 'INVALID';
38 END IF;
39
40 -- Check for value exists for Program Code
41 IF p_the_dtls_rec.program_cd IS NULL THEN
42 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
43 FND_MSG_PUB.ADD;
44 p_the_status := 'INVALID';
45 END IF;
46
47 -- Check for value exists for title
48 IF p_the_dtls_rec.title IS NULL THEN
49 FND_MESSAGE.SET_NAME('IGS','IGS_RE_THE_TITLE_NUL');
50 FND_MSG_PUB.ADD;
51 p_the_status := 'INVALID';
52 END IF;
53
54 --check constraint for final_title_ind, it should be 'Y' or 'N'
55 IF p_the_dtls_rec.final_title_ind IS NOT NULL THEN
56 BEGIN
57 igs_re_thesis_pkg.check_constraints('final_title_ind',p_the_dtls_rec.final_title_ind);
58 EXCEPTION
59 WHEN OTHERS THEN
60 l_msg_count := FND_MSG_PUB.COUNT_MSG;
61 FND_MSG_PUB.DELETE_MSG (p_msg_index => l_msg_count);
62 FND_MESSAGE.SET_NAME('IGS','IGS_RE_FNL_TITLE_INV');
63 FND_MSG_PUB.ADD;
64 p_the_status := 'INVALID';
65 END;
66 END IF;
67
68 --Check constraint for the final thesis result code
69 IF p_the_dtls_rec.final_thesis_result_cd IS NOT NULL THEN
70 BEGIN
71 igs_re_thesis_pkg.check_constraints('thesis_result_cd',p_the_dtls_rec.final_thesis_result_cd);
72 EXCEPTION
73 WHEN OTHERS THEN
74 l_msg_count := FND_MSG_PUB.COUNT_MSG;
75 FND_MSG_PUB.DELETE_MSG (p_msg_index => l_msg_count);
76 FND_MESSAGE.SET_NAME('IGS','IGS_RE_RESULT_CD_INVALID_VAL');
77 FND_MSG_PUB.ADD;
78 p_the_status := 'INVALID';
79 END;
80 END IF;
81
82 --Check constraint for thesis exam type
83 IF p_the_dtls_rec.thesis_exam_type IS NOT NULL THEN
84 BEGIN
85 igs_re_thesis_exam_pkg.check_constraints('thesis_exam_type',p_the_dtls_rec.thesis_exam_type);
86 EXCEPTION
87 WHEN OTHERS THEN
88 l_msg_count := FND_MSG_PUB.COUNT_MSG;
89 FND_MSG_PUB.DELETE_MSG (p_msg_index => l_msg_count);
90 FND_MESSAGE.SET_NAME('IGS','IGS_RE_EXAM_TYP_INVALID_VAL');
91 FND_MSG_PUB.ADD;
92 p_the_status := 'INVALID';
93 END;
94 END IF;
95
96 --Check constraint for the Thesis Panel Type parameter
97 IF p_the_dtls_rec.thesis_panel_type IS NOT NULL THEN
98 BEGIN
99 igs_re_thesis_exam_pkg.check_constraints('thesis_panel_type',p_the_dtls_rec.thesis_panel_type);
100 EXCEPTION
101 WHEN OTHERS THEN
102 l_msg_count := FND_MSG_PUB.COUNT_MSG;
103 FND_MSG_PUB.DELETE_MSG (p_msg_index => l_msg_count);
104 FND_MESSAGE.SET_NAME('IGS','IGS_RE_PNL_TYP_INVALID_VAL');
105 FND_MSG_PUB.ADD;
106 p_the_status := 'INVALID';
107 END;
108 END IF;
109
110 --Check Constraint for the thesis result code
111 IF p_the_dtls_rec.thesis_result_cd IS NOT NULL THEN
112 BEGIN
113 igs_re_thesis_exam_pkg.check_constraints('thesis_result_cd',p_the_dtls_rec.thesis_result_cd);
114 EXCEPTION
115 WHEN OTHERS THEN
116 l_msg_count := FND_MSG_PUB.COUNT_MSG;
117 FND_MSG_PUB.DELETE_MSG (p_msg_index => l_msg_count);
118 FND_MESSAGE.SET_NAME('IGS','IGS_RE_RESULT_CD_INVALID_VAL');
119 FND_MSG_PUB.ADD;
120 p_the_status := 'INVALID';
121 END;
122 END IF;
123
124 END validate_parameters;
125
126 FUNCTION validate_the_db_cons(
127 p_person_id IN NUMBER ,
128 p_ca_sequence_number IN NUMBER,
129 p_the_dtls_rec IN the_dtls_rec_type)
130 RETURN VARCHAR2 IS
131 /*
132 || Created By : nbehera
133 || Created On : 14-NOV-2002
134 || Purpose : This Procedure Checks for all the database constraints required to
135 || check before insertion of a Thesis Details record.
136 || Known limitations, enhancements or remarks :
137 || Change History :
138 || Who When What
139 || (reverse chronological order - newest change first)
140 */
141 l_ret_status VARCHAR2(1) := 'S';
142 BEGIN
143
144 --check for duplicate record in the Thesis table
145 IF NOT igs_re_val_the.check_dup_thesis (
146 p_person_id ,
147 p_the_dtls_rec.title,
148 p_ca_sequence_number) THEN
149 FND_MESSAGE.SET_NAME('IGS','IGS_RE_THE_DUPLIDATE');
150 FND_MSG_PUB.ADD;
151 RETURN 'W';
152 END IF;
153
154 -- FK check in the Thesis Result table
155 IF p_the_dtls_rec.final_thesis_result_cd IS NOT NULL THEN
156 IF NOT IGS_RE_THESIS_RESULT_PKG.Get_PK_For_Validation (
157 p_the_dtls_rec.final_thesis_result_cd ) THEN
158 FND_MESSAGE.SET_NAME ('IGS', 'IGS_RE_RESULT_CD_INVALID_VAL');
159 FND_MSG_PUB.ADD;
160 l_ret_status := 'E';
161 END IF;
162 END IF;
163 RETURN l_ret_status;
164
165 END validate_the_db_cons;
166
167 PROCEDURE validate_the (
168 p_person_id IN NUMBER ,
169 p_ca_sequence_number IN NUMBER,
170 p_the_dtls_rec IN the_dtls_rec_type ,
171 p_the_status OUT NOCOPY VARCHAR2 ) IS
172 /*
173 || Created By : nbehera
174 || Created On : 14-NOV-2002
175 || Purpose : This procedure checks all the business validations before
176 || inserting a Thesis Details Record
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || (reverse chronological order - newest change first)
181 */
182 l_thesis_status VARCHAR2(15) := NULL;
183 l_message fnd_new_messages.message_name%TYPE;
184 l_dummy_bool BOOLEAN;
185 BEGIN
186 p_the_status := 'VALID';
187
188 --IF Expected Submission Date < Minimum Submission Date OR Expected Submission Date > Maximum Submission Date
189 --then error will be logged.
190 l_message := NULL;
191 l_dummy_bool := igs_re_val_the.resp_val_the_expct (
192 p_person_id ,
193 p_ca_sequence_number,
194 p_the_dtls_rec.expected_submission_dt,
195 'Y',
196 l_message);
197 IF l_message IS NOT NULL THEN
198 p_the_status := 'INVALID';
199 END IF;
200
201 -- If submission date available and final thesis result code is not available then thesis_status should be 'SUBMITTED'
202 -- If submission date available and final thesis result code is available then thesis_status should be 'EXAMINED'
203 -- If submission date is not available then thesis_status should be 'PENDING'
204 IF p_the_dtls_rec.submission_dt IS NOT NULL THEN
205 IF p_the_dtls_rec.final_thesis_result_cd IS NULL THEN
206 l_thesis_status := 'SUBMITTED';
207 ELSE
208 l_thesis_status := 'EXAMINED';
209 END IF;
210 ELSE
211 l_thesis_status := 'PENDING';
212 END IF ;
213
214 l_message := NULL;
215 -- Embargo details or Embargo Expiry date cannot have value unless the thesis has been SUBMITTED or EXAMINED
216 -- If Embargo Expiration Date is available without Embargo Details then log error message.
217 l_dummy_bool := igs_re_val_the.resp_val_the_embrg(
218 p_the_dtls_rec.embargo_details,
219 NULL,
220 p_the_dtls_rec.embargo_expiry_dt,
221 l_thesis_status,
222 'Y',
223 l_message);
224 IF l_message IS NOT NULL THEN
225 p_the_status := 'INVALID';
226 END IF;
227
228 --If thesis status is either SUBMITTED or EXAMINED.
229 IF l_thesis_status = 'SUBMITTED' OR l_thesis_status = 'EXAMINED' THEN
230
231 l_message := NULL;
232 --If final title indicator has not been set and thesis status is either SUBMITTED or EXAMINED
233 --then log error message
234 l_dummy_bool := igs_re_val_the.resp_val_the_fnl(
235 p_person_id,
236 p_ca_sequence_number,
237 NULL,
238 p_the_dtls_rec.final_title_ind,
239 l_thesis_status,
240 l_message);
241 IF l_message IS NOT NULL THEN
242 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CANT_SUBMIT_THESIS');
243 FND_MSG_PUB.ADD;
244 p_the_status := 'INVALID';
245 END IF;
246
247 l_message := NULL;
248 -- If final result indicator is not set then log error message
249 l_dummy_bool := igs_re_val_the.resp_val_the_thr(
250 p_person_id,
251 p_ca_sequence_number ,
252 NULL,
253 p_the_dtls_rec.final_thesis_result_cd,
254 l_thesis_status,
255 'Y',
256 l_message);
257 IF l_message IS NOT NULL THEN
258 p_the_status := 'INVALID';
259 END IF;
260
261 -- If Thesis result code is not available and the thesis status is EXAMINED then log error message.
262 IF p_the_dtls_rec.thesis_result_cd IS NULL AND
263 l_thesis_status = 'EXAMINED' THEN
264 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CHK_RES_OUTSTAND_EXAM');
265 FND_MSG_PUB.ADD;
266 p_the_status := 'INVALID';
267 END IF;
268
269 -- If thesis status is PENDING.
270 ELSE
271
272 l_message := NULL;
273 -- If citation Details are available then log error message, since thesis status is not SUBMITTED or EXAMINED
274 l_dummy_bool := igs_re_val_the.resp_val_the_ctn(
275 l_thesis_status,
276 p_the_dtls_rec.citation,
277 l_message);
278 IF l_message IS NOT NULL THEN
279 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CANT_ENTER_GRAD_CITAT');
280 FND_MSG_PUB.ADD;
281 p_the_status := 'INVALID';
282 END IF;
283
284
285 l_message := NULL;
286 -- If library catalog number or library lodgement date is available then log error message, since thesis status
287 -- is not SUBMITTED or EXAMINED
288 l_dummy_bool := igs_re_val_the.resp_val_the_lbry(
289 p_person_id,
290 p_ca_sequence_number,
291 NULL,
292 p_the_dtls_rec.library_catalogue_number,
293 p_the_dtls_rec.library_lodgement_dt,
294 l_thesis_status,
295 l_message);
296 IF l_message IS NOT NULL THEN
297 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CANT_ENTER_LIBR_DETAIL');
298 FND_MSG_PUB.ADD;
299 p_the_status := 'INVALID';
300 END IF;
301
302
303 l_message := NULL;
304 --If thesis result code is available and thesis status is PENDING then log error message
305 --If final result indicator is not set then log error message
306 l_dummy_bool := igs_re_val_the.resp_val_the_thr(
307 p_person_id,
308 p_ca_sequence_number ,
309 NULL,
310 p_the_dtls_rec.final_thesis_result_cd,
311 l_thesis_status,
312 'Y',
313 l_message);
314 IF l_message IS NOT NULL THEN
315 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CHK_RES_NOT_FINAL_RES');
316 FND_MSG_PUB.ADD;
317 p_the_status := 'INVALID';
318 END IF;
319 END IF;
320
321 END validate_the;
322
323 FUNCTION validate_the_exam_db_cons(
324 p_person_id IN NUMBER ,
325 p_ca_sequence_number IN NUMBER,
326 p_the_dtls_rec IN the_dtls_rec_type )
327 RETURN VARCHAR2 IS
328 /*
329 || Created By : nbehera
330 || Created On : 14-NOV-2002
331 || Purpose : This Procedure Checks for all the database constraints required to
332 || check before insertion of a Thesis exam record.
333 || Known limitations, enhancements or remarks :
334 || Change History :
335 || Who When What
336 || (reverse chronological order - newest change first)
337 */
338 l_ret_status VARCHAR2(1) := 'S';
339 BEGIN
340 --Primary key check in the Thesis Exam table is not required.
341 --This check is not required because the sequence number of the thesis record ,
342 --which is freshly being picked up from a sequence will be unique.
343 --And the sequence number is part of the primary key of the thesis exam table,
344 --Then definitely the pk check will not be failed for any thesis exam record.
345
346 --FK check to the table igs_re_ths_exam_type for the field thesis_exam_type
347 IF p_the_dtls_rec.thesis_exam_type IS NOT NULL THEN
348 IF NOT igs_re_ths_exam_type_pkg.get_pk_for_validation (
349 p_the_dtls_rec.thesis_exam_type) THEN
350 FND_MESSAGE.SET_NAME ('IGS', 'IGS_RE_THE_EXM_TYP_INV');
351 FND_MSG_PUB.ADD;
352 l_ret_status := 'E';
353 END IF;
354 END IF;
355
356 --FK check to the table igs_re_thesis_result for the field thesis_result_cd
357 IF p_the_dtls_rec.thesis_result_cd IS NOT NULL THEN
358 IF NOT igs_re_thesis_result_pkg.get_pk_for_validation (
359 p_the_dtls_rec.thesis_result_cd ) THEN
360 FND_MESSAGE.SET_NAME ('IGS', 'IGS_RE_THE_RES_CD_INV');
361 FND_MSG_PUB.ADD;
362 l_ret_status := 'E';
363 END IF;
364 END IF;
365
366 --FK check to the table igs_re_ths_pnl_type for the field thesis_panel_type
367 IF p_the_dtls_rec.thesis_panel_type IS NOT NULL THEN
368 IF NOT igs_re_ths_pnl_type_pkg.get_pk_for_validation (
369 p_the_dtls_rec.thesis_panel_type) THEN
370 FND_MESSAGE.SET_NAME ('IGS', 'IGS_RE_THE_PNL_TYP_INV');
371 FND_MSG_PUB.ADD;
372 l_ret_status := 'E';
373 END IF;
374 END IF;
375 RETURN l_ret_status;
376
377 END validate_the_exam_db_cons;
378
379 PROCEDURE validate_the_exam(
380 p_person_id IN NUMBER,
381 p_ca_sequence_number IN NUMBER,
382 p_the_dtls_rec IN the_dtls_rec_type,
383 p_exam_status OUT NOCOPY VARCHAR2 ) IS
384 /*
385 || Created By : nbehera
386 || Created On : 14-NOV-2002
387 || Purpose : This procedure checks all the business validations before
388 || inserting a Thesis Exam Record
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 || Nishikant 30DEC2002 Bug#2722106. A new validation has been put to check for the fields
394 || thesis_exam_type, thesis_panel_type, submission_dt.
395 */
396 l_thesis_status VARCHAR2(15) := NULL;
397 l_message fnd_new_messages.message_name%TYPE;
398 l_dummy_bool BOOLEAN;
399 BEGIN
400 p_exam_status := 'VALID';
401
402 --The following check added added as part of the Bug#2722106
403 --If THESIS_EXAM_TYPE is available , THESIS_PANEL_TYPE should also be available for the Thesis Exam Details record.
404 --Simm. if THESIS_PANEL_TYPE is available, THESIS_PANEL_TYPE should also be available.
405 --If SUBMISSION_DATE is available then both of THESIS_EXAM_TYPE and THESIS_PANEL_TYPE should also be available.
406 IF ( p_the_dtls_rec.thesis_exam_type IS NULL AND
407 p_the_dtls_rec.thesis_panel_type IS NOT NULL ) OR
408 ( p_the_dtls_rec.thesis_panel_type IS NULL AND
409 p_the_dtls_rec.thesis_exam_type IS NOT NULL ) OR
410 ( p_the_dtls_rec.submission_dt IS NOT NULL AND
411 p_the_dtls_rec.thesis_exam_type IS NULL ) THEN
412 FND_MESSAGE.SET_NAME('IGS','IGS_EN_INCOMP_THE_EXAM_DTLS');
413 FND_MSG_PUB.ADD;
414 p_exam_status := 'INVALID';
415 END IF;
416
417 l_message := NULL;
418 -- The below validations are being taken care in the following function call
419 -- If final title indicator is 'N' and submission date is available then log error message.
420 -- If Principal supervisor is not available and submission date is available then log error message.
421 -- If Govt type of activity code has not been set and submission date is available then log error message.
422 -- If submission date is prior to course attempt commencement date then log error message.
423 l_dummy_bool := igs_re_val_tex.resp_val_tex_sbmsn(
424 p_person_id,
425 p_ca_sequence_number,
426 NULL,
427 NULL,-- pass NULL for creation_dt
428 p_the_dtls_rec.thesis_result_cd,
429 p_the_dtls_rec.submission_dt,
430 'Y',
431 p_the_dtls_rec.final_title_ind,-- new parameter added
432 l_message);
433 IF l_message IS NOT NULL THEN
434 p_exam_status := 'INVALID';
435 END IF;
436
437 END validate_the_exam;
438
439 PROCEDURE create_the
440 ( p_api_version IN NUMBER,
441 p_init_msg_list IN VARCHAR2,
442 p_commit IN VARCHAR2,
443 p_validation_level IN NUMBER,
444 p_the_dtls_rec IN the_dtls_rec_type ,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2 ) IS
448 /*
449 || Created By : nbehera
450 || Created On : 14-NOV-2002
451 || Purpose : This procedure inserts records into the Thesis Details table and
452 || Thesis Exam table after checking all the validations.
453 || Known limitations, enhancements or remarks :
454 || Change History :
455 || Who When What
456 || (reverse chronological order - newest change first)
457 || Nishikant 27DEC2002 Bug#2722106. If any of the fields thesis_exam_type, thesis_panel_type
458 || submission_dt, thesis_result_cd having NOT NULL value then proceed to insert the
459 || EXAM Record after all the bussiness validations are done successfully.
460 || Otherwise Insert only the Thesis Details Record, skip the Thesis Exam record.
461 */
462
463 CURSOR c_the_seq_num IS
464 SELECT IGS_RE_THESIS_SEQ_NUM_S.NEXTVAL
465 FROM DUAL;
466 l_the_seq_num igs_re_thesis_all.sequence_number%TYPE;
467
468 l_api_name CONSTANT VARCHAR2(30) := 'Thesis Details';
469 l_api_version CONSTANT NUMBER := 1.0;
470 l_the_exam_status VARCHAR2(15) := 'VALID';
471 l_person_id igs_re_thesis.person_id%TYPE := NULL;
472 l_ca_sequence_number igs_re_candidature_all.sequence_number%TYPE;
473 l_creation_dt igs_re_thesis_exam.creation_dt%TYPE := SYSDATE;
474 l_last_update_date igs_re_thesis_all.creation_date%TYPE;
475 l_last_updated_by igs_re_thesis_all.last_updated_by%TYPE;
476 l_last_update_login igs_re_thesis_all.last_update_login%TYPE;
477
478 BEGIN
479 -- Standard Start of API savepoint
480 SAVEPOINT THEDET_PUB;
481
482 -- Standard call to check for call compatibility.
483 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version,
484 p_api_version,
485 l_api_name,
486 g_pkg_name ) THEN
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 END IF;
489
490 -- Initialize message list if p_init_msg_list is set to TRUE.
491 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
492 FND_MSG_PUB.INITIALIZE;
493 END IF;
494
495 -- Initialize API return status to success
496 x_return_status := FND_API.G_RET_STS_SUCCESS;
497
498 /****** Validating all the parameters available ******/
499 validate_parameters( p_the_dtls_rec,
500 l_the_exam_status );
501
502 /****** Deriving values for the parameters ******/
503 IF l_the_exam_status <> 'INVALID' THEN
504 -- Calling the function to get the person_id
505 l_person_id := igs_ge_gen_003.get_person_id( p_the_dtls_rec.person_number);
506 IF l_person_id IS NULL THEN
507 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
508 FND_MSG_PUB.ADD;
509 l_the_exam_status := 'INVALID';
510 END IF;
511
512 -- Calling the function to get the value for ca_sequence_number
513 IF NOT igs_re_val_the.get_candidacy_dtls(
514 l_person_id,
515 p_the_dtls_rec.program_cd,
516 l_ca_sequence_number) THEN
517 FND_MESSAGE.SET_NAME ('IGS', 'IGS_RE_CANT_LOCATE_CAND_DET');
518 FND_MSG_PUB.ADD;
519 l_the_exam_status := 'INVALID';
520 END IF;
521 END IF;
522
523 /****** Check for the Database constraints for the thesis details record ******/
524 IF l_the_exam_status <> 'INVALID' THEN
525 x_return_status:= validate_the_db_cons(
526 l_person_id,
527 l_ca_sequence_number,
528 p_the_dtls_rec);
529 IF x_return_status <> 'S' THEN
530 l_the_exam_status := 'INVALID';
531 END IF;
532 END IF;
533
534 /****** Check the bussiness validation for the thesis details record ******/
535 IF l_the_exam_status <> 'INVALID' THEN
536 validate_the (
537 l_person_id,
538 l_ca_sequence_number ,
539 p_the_dtls_rec,
540 l_the_exam_status);
541 END IF;
542
543 /****** Insert data into Thesis Details and Thesis Exam table ******/
544 IF l_the_exam_status <> 'INVALID' THEN --1st level IF clause starts here
545
546 --Deriving values for the WHO Columns
547 l_last_update_date := SYSDATE;
548 l_last_updated_by := FND_GLOBAL.USER_ID;
549 IF l_last_updated_by IS NULL THEN
550 l_last_updated_by := -1;
551 END IF;
552 l_last_update_login := FND_GLOBAL.LOGIN_ID;
553 IF l_last_update_login IS NULL THEN
554 l_last_update_login := -1;
555 END IF;
556
557 -- Getting the sequence number from the sequence for the field sequence_number
558 OPEN c_the_seq_num;
559 FETCH c_the_seq_num INTO l_the_seq_num;
560 CLOSE c_the_seq_num;
561
562 BEGIN
563 --insert data into Thesis Details table
564 INSERT INTO igs_re_thesis_all (
565 PERSON_ID,
566 CA_SEQUENCE_NUMBER,
567 SEQUENCE_NUMBER,
568 TITLE,
569 FINAL_TITLE_IND,
570 SHORT_TITLE,
571 ABBREVIATED_TITLE,
572 THESIS_RESULT_CD,
573 EXPECTED_SUBMISSION_DT,
574 LIBRARY_LODGEMENT_DT,
575 LIBRARY_CATALOGUE_NUMBER,
576 EMBARGO_EXPIRY_DT,
577 THESIS_FORMAT,
578 LOGICAL_DELETE_DT,
579 EMBARGO_DETAILS,
580 THESIS_TOPIC,
581 CITATION,
582 COMMENTS,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_DATE,
586 LAST_UPDATED_BY,
587 LAST_UPDATE_LOGIN,
588 ORG_ID )
589 VALUES (
590 l_person_id,
591 l_ca_sequence_number,
592 l_the_seq_num,
593 p_the_dtls_rec.title,
594 NVL(p_the_dtls_rec.final_title_ind,'N'),
595 p_the_dtls_rec.short_title,
596 p_the_dtls_rec.abbreviated_title,
597 p_the_dtls_rec.final_thesis_result_cd,
598 p_the_dtls_rec.expected_submission_dt,
599 p_the_dtls_rec.library_lodgement_dt,
600 p_the_dtls_rec.library_catalogue_number,
601 p_the_dtls_rec.embargo_expiry_dt,
602 p_the_dtls_rec.thesis_format,
603 NULL,
604 p_the_dtls_rec.embargo_details,
605 p_the_dtls_rec.thesis_topic,
606 p_the_dtls_rec.citation,
607 p_the_dtls_rec.comments,
608 l_last_update_date,
609 l_last_updated_by,
610 l_last_update_date,
611 l_last_updated_by,
612 l_last_update_login,
613 igs_ge_gen_003.get_org_id);
614 END;
615
616 --Condition added as part of the Bug#2722106.
617 --If all of the fields submission_dt,thesis_result_cd, thesis_exam_type and thesis_panel_type are having NULL value
618 --then dont create the thesis exam record. In this case only Thesis Details record will be created of 'PENDING' status.
619 --If any of these field value is available then proceed to insert the Thesis Exam Record, after the
620 --bussiness validation and db_cons validations for the the Exam record will be over successfully.
621 IF p_the_dtls_rec.submission_dt IS NOT NULL OR
622 p_the_dtls_rec.thesis_exam_type IS NOT NULL OR
623 p_the_dtls_rec.thesis_panel_type IS NOT NULL OR
624 p_the_dtls_rec.thesis_result_cd IS NOT NULL THEN --2nd level IF clause starts here
625
626 /****** Check for the Database constraints for the Thesis Exam record ******/
627 x_return_status:= validate_the_exam_db_cons(
628 l_person_id,
629 l_ca_sequence_number,
630 p_the_dtls_rec);
631 IF x_return_status <> 'S' THEN
632 l_the_exam_status := 'INVALID';
633 END IF;
634
635 /****** Check the bussiness validation for the Thesis Exam record ******/
636 IF l_the_exam_status <> 'INVALID' THEN
637 validate_the_exam(
638 l_person_id,
639 l_ca_sequence_number,
640 p_the_dtls_rec,
641 l_the_exam_status);
642 END IF;
643
644 IF l_the_exam_status <> 'INVALID' THEN --3rd level IF clause starts here
645 BEGIN
646 --insert data into thesis exam table
647 INSERT INTO igs_re_thesis_exam (
648 PERSON_ID,
649 CA_SEQUENCE_NUMBER,
650 THE_SEQUENCE_NUMBER,
651 CREATION_DT,
652 SUBMISSION_DT,
653 THESIS_EXAM_TYPE,
654 THESIS_PANEL_TYPE,
655 TRACKING_ID,
656 THESIS_RESULT_CD,
657 CREATION_DATE,
658 CREATED_BY,
659 LAST_UPDATE_DATE,
660 LAST_UPDATED_BY,
661 LAST_UPDATE_LOGIN )
662 VALUES (
663 l_person_id,
664 l_ca_sequence_number,
665 l_the_seq_num,
666 l_creation_dt,
667 p_the_dtls_rec.submission_dt,
668 p_the_dtls_rec.thesis_exam_type,
669 p_the_dtls_rec.thesis_panel_type,
670 NULL, --tracking_id
671 p_the_dtls_rec.thesis_result_cd,
672 l_last_update_date,
673 l_last_updated_by,
674 l_last_update_date,
675 l_last_updated_by,
676 l_last_update_login);
677 END;
678 END IF; --3rd level IF clause ends here
679 END IF; --2nd level IF clause ends here
680 END IF; --1st level IF clause ends here
681
682 IF l_the_exam_status = 'INVALID' THEN
683 IF x_return_status <> 'W' THEN
684 x_return_status := FND_API.G_RET_STS_ERROR ;
685 END IF;
686 ROLLBACK TO THEDET_PUB;
687 ELSIF l_the_exam_status = 'VALID' THEN
688 x_return_status := FND_API.G_RET_STS_SUCCESS ;
689 -- Standard check of p_commit.
690 IF FND_API.To_Boolean( p_commit ) THEN
691 COMMIT WORK;
692 END IF;
693 END IF;
694
695 -- Standard call to get message count and if count is 1, get message info.
696 FND_MSG_PUB.Count_And_Get (
697 p_count => x_msg_count,
698 p_data => x_msg_data );
699
700 EXCEPTION
701 WHEN FND_API.G_EXC_ERROR THEN
702 ROLLBACK TO THEDET_PUB;
703 x_return_status := FND_API.G_RET_STS_ERROR ;
704 FND_MSG_PUB.Count_And_Get (
705 p_count => x_msg_count,
706 p_data => x_msg_data );
707
708 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
709 ROLLBACK TO THEDET_PUB;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
711 FND_MSG_PUB.Count_And_Get (
712 p_count => x_msg_count,
713 p_data => x_msg_data );
714
715 WHEN OTHERS THEN
716 ROLLBACK TO THEDET_PUB;
717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
718 IF FND_MSG_PUB.Check_Msg_Level
719 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
720 THEN
721 FND_MSG_PUB.Add_Exc_Msg
722 (g_pkg_name,
723 l_api_name );
724 END IF;
725 FND_MSG_PUB.Count_And_Get (
726 p_count => x_msg_count,
727 p_data => x_msg_data );
728 END create_the;
729
730 END igs_re_the_lgcy_pub;
[Home] [Help]