1 PACKAGE BODY igs_re_sprvsr_lgcy_pub AS
2 /* $Header: IGSRE19B.pls 120.4 2006/02/15 01:45:32 bdeviset noship $ */
3
4 /*------------------------------------------------------------------------------+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
6 | All rights reserved. |
7 +==============================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: igs_re_sprvsr_lgcy_pub |
11 | |
12 | NOTES : Research Supervisor Legacy API. This API imports supervisor |
13 | information against the specified program attempt / candidature. |
14 | Created as part of Enrollment Legacy build. Bug# 2661533 |
15 | |
16 | HISTORY |
17 | Who When What |
18 | |
19 +==============================================================================+
20 |Nalin Kumar 28-Jan-2003 Modified create_sprvsr.c_repl_person_dtls cursor to fetch sequence_number by comparing person_id; |
21 | Previously it was fetching replaced_sequence_number by comparing replaced_person_id; |
22 | This is to fix bug# 2725852. |
23 *==============================================================================*/
24
25 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_RE_SPRVSR_LGCY_PUB';
26
27 FUNCTION validate_parameters
28 (
29 p_sprvsr_dtls_rec IN sprvsr_dtls_rec_type
30 ) RETURN VARCHAR2 AS
31
32 /**********************************************************************************************
33 Created By : pradhakr
34 Date Created By : 14-Nov-02
35 Purpose : This function is used to validate the input parameters.
36 Known limitations,enhancements,remarks:
37 Change History
38 Who When What
39 ***********************************************************************************************/
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_sprvsr_status VARCHAR2(10) := 'VALID';
43
44 BEGIN
45
46 -- Check whether the passed person number is null or not.
47 IF p_sprvsr_dtls_rec.ca_person_number IS NULL OR p_sprvsr_dtls_rec.person_number IS NULL THEN
48 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
49 FND_MSG_PUB.Add;
50 l_sprvsr_status := 'INVALID';
51 END IF;
52
53 -- Check whether the user has passed program code or not.
54 IF p_sprvsr_dtls_rec.program_cd IS NULL THEN
55 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
56 FND_MSG_PUB.Add;
57 l_sprvsr_status := 'INVALID';
58 END IF;
59
60 -- Check whether the start date is null or not.
61 IF p_sprvsr_dtls_rec.start_dt IS NULL THEN
62 FND_MESSAGE.SET_NAME('IGS','IGS_PS_STARDT_NOT_NULL');
63 FND_MSG_PUB.Add;
64 l_sprvsr_status := 'INVALID';
65 END IF;
66
67 -- Check whether the research supervisor type is null or not
68 IF p_sprvsr_dtls_rec.research_supervisor_type IS NULL THEN
69 FND_MESSAGE.SET_NAME('IGS','IGS_RE_SPRVSR_TYP_NULL');
70 FND_MSG_PUB.Add;
71 l_sprvsr_status := 'INVALID';
72 ELSE
73 BEGIN
74
75 -- If research supervisor is specified then validate it by calling the check constraints. Incase if there is
76 -- any error it will log an error message "IGS_GE_INVALID_VALUE", which doesn't give much info. to the user.
77 -- So we delete that message and add a meaningful message to the stack.
78
79 igs_re_sprvsr_pkg.check_constraints ('RESEARCH_SUPERVISOR_TYPE', p_sprvsr_dtls_rec.research_supervisor_type);
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 FND_MSG_PUB.COUNT_AND_GET( p_count => l_msg_count,
84 p_data => l_msg_data);
85
86 -- Delete the message 'IGS_GE_INVALID_VALUE'
87 FND_MSG_PUB.DELETE_MSG (l_msg_count);
88
89 -- set the customized message
90 FND_MESSAGE.Set_Name('IGS','IGS_RE_SPRVSR_TYPE_VAL');
91 FND_MSG_PUB.Add;
92 l_sprvsr_status := 'INVALID';
93 END;
94 END IF;
95
96
97 IF p_sprvsr_dtls_rec.supervision_percentage IS NOT NULL THEN
98 BEGIN
99 igs_re_sprvsr_pkg.check_constraints ('SUPERVISION_PERCENTAGE', p_sprvsr_dtls_rec.supervision_percentage);
100 EXCEPTION
101 WHEN OTHERS THEN
102 FND_MSG_PUB.COUNT_AND_GET( p_count => l_msg_count,
103 p_data => l_msg_data);
104
105 FND_MSG_PUB.DELETE_MSG (l_msg_count);
106 FND_MESSAGE.Set_Name('IGS','IGS_RE_SPRVSN_PERC_INVALID_VAL');
107 FND_MSG_PUB.Add;
108 l_sprvsr_status := 'INVALID';
109 END;
110 END IF;
111
112
113 IF p_sprvsr_dtls_rec.funding_percentage IS NOT NULL THEN
114 BEGIN
115 igs_re_sprvsr_pkg.check_constraints ('FUNDING_PERCENTAGE', p_sprvsr_dtls_rec.funding_percentage);
116 EXCEPTION
117 WHEN OTHERS THEN
118 FND_MSG_PUB.COUNT_AND_GET( p_count => l_msg_count,
119 p_data => l_msg_data);
120
121 FND_MSG_PUB.DELETE_MSG (l_msg_count);
122 FND_MESSAGE.Set_Name('IGS','IGS_RE_FUND_PERC_INVALID_VAL');
123 FND_MSG_PUB.Add;
124 l_sprvsr_status := 'INVALID';
125 END;
126 END IF;
127
128
129 IF p_sprvsr_dtls_rec.org_unit_cd IS NOT NULL THEN
130 BEGIN
131 igs_re_sprvsr_pkg.check_constraints ('ORG_UNIT_CD', p_sprvsr_dtls_rec.org_unit_cd);
132 EXCEPTION
133 WHEN OTHERS THEN
134 FND_MSG_PUB.COUNT_AND_GET( p_count => l_msg_count,
135 p_data => l_msg_data);
136 FND_MSG_PUB.DELETE_MSG (l_msg_count);
137 FND_MESSAGE.Set_Name('IGS','IGS_FI_INVALID_ORG_UNIT_CD');
138 FND_MESSAGE.SET_TOKEN('ORG_CD',p_sprvsr_dtls_rec.org_unit_cd);
139 FND_MSG_PUB.Add;
140 l_sprvsr_status := 'INVALID';
141 END;
142 END IF;
143
144 RETURN l_sprvsr_status;
145
146 END validate_parameters;
147
148
149 FUNCTION validate_sprvsr
150 (
151 p_person_id IN igs_re_sprvsr.person_id%TYPE,
152 p_sprvsr_dtls_rec IN sprvsr_dtls_rec_type,
153 p_ca_person_id IN igs_re_sprvsr.ca_person_id%TYPE,
154 p_ca_sequence_number IN igs_re_sprvsr.ca_sequence_number%TYPE,
155 p_ou_start_dt IN igs_re_sprvsr.ou_start_dt%TYPE
156 ) RETURN VARCHAR2 AS
157
158 /**********************************************************************************************
159 Created By : pradhakr
160 Date Created By : 14-Nov-02
161 Purpose : This function is used to validate the business logic.
162 Known limitations,enhancements,remarks:
163 Change History
164 Who When What
165 bdeviset 27-Jan-2006 Modified cursor c_date_ovrlp.Used the call igs_en_gen_003.get_staff_ind
166 and removed cursor c_staff_ind for bug# 4995230
167 bdeviset 13-Feb-2006 REplaced cursor c_meaning with fnd_message.set and get and using the cursor c_org_unit_exists
168 instead of literal when no where clause exists.Bug# 5034696
169 ***********************************************************************************************/
170
171
172
173 -- Cursor to get the sequence number of the person
174 CURSOR c_person_dtls ( l_person_id igs_re_sprvsr.person_id%TYPE, l_ca_person_id igs_re_sprvsr.ca_person_id%TYPE,
175 l_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE ) IS
176 SELECT sequence_number
177 FROM igs_re_sprvsr
178 WHERE person_id = l_person_id
179 AND ca_person_id = l_ca_person_id
180 AND ca_sequence_number = l_ca_sequence_number;
181
182 -- Cursor to get the replaced sequence number of the person
183 CURSOR c_repl_person_dtls ( l_repl_person_id igs_re_sprvsr.replaced_person_id%TYPE ) IS
184 SELECT replaced_sequence_number
185 FROM igs_re_sprvsr
186 WHERE replaced_person_id = l_repl_person_id;
187
188 CURSOR c_date_ovrlp (l_sequence_number IN NUMBER ) IS
189 SELECT 'x'
190 FROM igs_re_sprvsr rsup, igs_pe_person_base_v pdv
191 WHERE rsup.ca_person_id = p_ca_person_id
192 AND rsup.ca_sequence_number = p_ca_sequence_number
193 AND rsup.person_id = p_person_id
194 AND rsup.sequence_number = l_sequence_number
195 AND (rsup.end_dt IS NULL OR rsup.end_dt > p_sprvsr_dtls_rec.start_dt)
196 AND rsup.person_id = pdv.person_id;
197
198 CURSOR c_org_unit_exists (cp_org_unit_cd igs_or_unit.org_unit_cd%TYPE) IS
199 SELECT 'X'
200 FROM igs_or_unit
201 WHERE org_unit_cd = cp_org_unit_cd;
202
203 TYPE c_ref_cur IS REF CURSOR;
204
205 l_staff_member_ind igs_pe_person.staff_member_ind%TYPE;
206 l_message_name VARCHAR2(30) DEFAULT NULL;
207 l_result BOOLEAN;
208 l_where_clause VARCHAR2(450);
209 c_org_cur c_ref_cur ;
210 curr_stat VARCHAR2(500);
211 l_rec_found varchar2(1);
212 l_ca_person_id igs_re_sprvsr.ca_person_id%TYPE;
213 l_legacy VARCHAR2(1) DEFAULT 'Y';
214 l_sprvsr_status VARCHAR2(10) DEFAULT 'VALID';
215 l_sequence_number igs_re_sprvsr.sequence_number%TYPE;
216 l_replaced_person_id igs_re_sprvsr.replaced_person_id%TYPE;
217 l_replaced_sequence_number igs_re_sprvsr.replaced_sequence_number%TYPE;
218 l_date_ovrlp c_date_ovrlp%ROWTYPE;
219 l_message_text fnd_new_messages.message_text%TYPE;
220
221 BEGIN
222
223 l_staff_member_ind := igs_en_gen_003.get_staff_ind(p_person_id) ;
224
225 -- Call to check all funding related validations.
226 l_result := igs_re_val_rsup.resp_val_rsup_fund (
227 p_person_id ,
228 p_sprvsr_dtls_rec.org_unit_cd ,
229 p_ou_start_dt ,
230 p_sprvsr_dtls_rec.funding_percentage ,
231 l_staff_member_ind,
232 l_legacy,
233 l_message_name
234 );
235
236 IF l_message_name IS NOT NULL THEN
237 l_sprvsr_status := 'INVALID';
238 l_message_name := NULL;
239 END IF;
240
241 -- Check for Oraganisation Unit Code when the person is a staff member.
242 IF l_staff_member_ind = 'Y' THEN
243 l_result := igs_re_val_rsup.resp_val_rsup_ou (
244 p_person_id ,
245 p_sprvsr_dtls_rec.org_unit_cd ,
246 p_ou_start_dt,
247 l_staff_member_ind ,
248 l_legacy,
249 l_message_name
250 );
251
252 IF l_message_name IS NOT NULL THEN
253 l_sprvsr_status := 'INVALID';
254 l_message_name := NULL;
255 END IF;
256
257 END IF;
258
259 IF p_sprvsr_dtls_rec.end_dt IS NOT NULL THEN
260 OPEN c_person_dtls(p_person_id, p_ca_person_id, p_ca_sequence_number);
261 FETCH c_person_dtls INTO l_sequence_number;
262 CLOSE c_person_dtls;
263
264 -- Check whether Supervision end date must be earlier than the start date of replacement supervisor.
265 l_result := igs_re_val_rsup.resp_val_rsup_end_dt (
266 p_ca_person_id ,
267 p_ca_sequence_number ,
268 p_person_id ,
269 l_sequence_number ,
270 p_sprvsr_dtls_rec.start_dt,
271 p_sprvsr_dtls_rec.end_dt ,
272 l_legacy,
273 l_message_name
274 );
275
276 IF l_message_name IS NOT NULL THEN
277 l_sprvsr_status := 'INVALID';
278 l_message_name := NULL;
279 END IF;
280
281 -- Check whether there is any overlap of date.
282 OPEN c_date_ovrlp(l_sequence_number);
283 FETCH c_date_ovrlp INTO l_date_ovrlp;
284 IF c_date_ovrlp%FOUND THEN
285 FND_MESSAGE.SET_NAME('IGS', 'IGS_RE_END_DT_CANT_GE_ST_DT');
286 FND_MSG_PUB.Add;
287 END IF;
288 CLOSE c_date_ovrlp;
289
290 END IF;
291
292
293 IF p_sprvsr_dtls_rec.replaced_person_number IS NOT NULL THEN
294
295 l_replaced_person_id := Igs_Ge_Gen_003.Get_Person_id (p_sprvsr_dtls_rec.replaced_person_number);
296
297 OPEN c_repl_person_dtls(l_replaced_person_id);
298 FETCH c_repl_person_dtls INTO l_replaced_sequence_number;
299 CLOSE c_repl_person_dtls;
300
301 -- The call to the function resp_val_rsup_repl does the following validations
302 -- A supervisor cannot replace themselves.
303 -- A supervisor can only replace the latest instance of another supervisor
304 -- A replaced supervisor must have been ended.
305 -- A replacement supervisor cannot overlap the replaced supervisor
306
307 l_result := igs_re_val_rsup.resp_val_rsup_repl(
308 p_ca_person_id ,
309 p_ca_sequence_number ,
310 p_person_id ,
311 p_sprvsr_dtls_rec.start_dt,
312 l_replaced_person_id ,
313 l_replaced_sequence_number ,
314 l_legacy,
315 l_message_name
316 );
317
318 IF l_message_name IS NOT NULL THEN
319 l_sprvsr_status := 'INVALID';
320 l_message_name := NULL;
321 END IF;
322
323 END IF;
324
325 -- Organisation unit filter integration validation. Call to find the filter condition
326 -- modified the code for bug 5028599
327 IF p_sprvsr_dtls_rec.org_unit_cd IS NOT NULL THEN
328
329 l_rec_found := NULL;
330 igs_or_gen_012_pkg.get_where_clause_api('RES_SPRVSR_LGCY', l_where_clause);
331 IF l_where_clause IS NOT NULL THEN
332 l_where_clause := CONCAT(' AND ',l_where_clause);
333
334 curr_stat := 'SELECT ''x'' FROM igs_or_unit WHERE org_unit_cd = :1 '||l_where_clause;
335 OPEN c_org_cur FOR curr_stat USING p_sprvsr_dtls_rec.org_unit_cd,'RES_SPRVSR_LGCY';
336 FETCH c_org_cur INTO l_rec_found;
337 CLOSE c_org_cur;
338
339 ELSE
340
341 OPEN c_org_unit_exists (p_sprvsr_dtls_rec.org_unit_cd);
342 FETCH c_org_unit_exists INTO l_rec_found;
343 CLOSE c_org_unit_exists;
344
345 END IF;
346
347
348
349 IF l_rec_found IS NULL THEN
350
351 FND_MESSAGE.SET_NAME('IGS','IGS_RE_ORG_UNIT_CD');
352 l_message_text := FND_MESSAGE.GET();
353
354 FND_MESSAGE.SET_NAME('IGS','IGS_EN_INV');
355 FND_MESSAGE.SET_TOKEN('PARAM',l_message_text);
356 FND_MSG_PUB.Add;
357 l_sprvsr_status := 'INVALID';
358 END IF;
359
360 END IF;
361
362 -- Check whether research student is the same as the supervisor
363 IF NOT igs_re_val_rsup.resp_val_rsup_person( p_ca_person_id, p_person_id, l_legacy , l_message_name ) THEN
364 IF l_message_name IS NOT NULL THEN
365 FND_MESSAGE.SET_NAME('IGS',l_message_name);
366 FND_MSG_PUB.Add;
367 l_sprvsr_status := 'INVALID';
368 l_message_name := NULL;
369 END IF;
370 END IF;
371
372 -- Validate research supervisor overlaps.
373 IF igs_re_val_rsup.resp_val_rsup_ovrlp( p_ca_person_id, p_ca_sequence_number, p_person_id, l_sequence_number,
374 p_sprvsr_dtls_rec.start_dt, p_sprvsr_dtls_rec.end_dt, l_legacy, l_message_name ) THEN
375 IF l_message_name IS NOT NULL THEN
376 l_sprvsr_status := 'INVALID';
377 END IF;
378 END IF;
379
380
381 RETURN l_sprvsr_status;
382
383 END validate_sprvsr;
384
385
386
387 FUNCTION validate_sprvsr_db_cons
388 (
389 p_person_id IN igs_re_sprvsr.person_id%TYPE,
390 p_sprvsr_dtls_rec IN sprvsr_dtls_rec_type,
391 p_ca_person_id IN igs_re_sprvsr.ca_person_id%TYPE,
392 p_ca_sequence_number IN igs_re_sprvsr.ca_sequence_number%TYPE,
393 p_sprvsr_status OUT NOCOPY VARCHAR2
394 ) RETURN VARCHAR2 AS
395
396 /**********************************************************************************************
397 Created By : pradhakr
398 Date Created By : 14-Nov-02
399 Purpose : This function is used to validate the database constraints.
400 Known limitations,enhancements,remarks:
401 Change History
402 Who When What
403 ***********************************************************************************************/
404
405 CURSOR c_person_dtls ( l_person_id igs_re_sprvsr.person_id%TYPE, l_ca_person_id igs_re_sprvsr.ca_person_id%TYPE,
406 l_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE ) IS
407 SELECT sequence_number, start_dt
408 FROM igs_re_sprvsr
409 WHERE person_id = l_person_id
410 AND ca_person_id = l_ca_person_id
411 AND ca_sequence_number = l_ca_sequence_number;
412
413 l_person_dtls c_person_dtls%ROWTYPE;
414 l_ret_value VARCHAR2(1) := 'S';
415 l_ca_person_id igs_re_sprvsr.ca_person_id%TYPE;
416 l_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE;
417 l_result BOOLEAN;
418
419 BEGIN
420
421 p_sprvsr_status := 'VALID';
422
423 OPEN c_person_dtls(p_person_id, p_ca_person_id, p_ca_sequence_number);
424 FETCH c_person_dtls INTO l_person_dtls;
425 CLOSE c_person_dtls;
426
427 -- Check for Unique Key validation. If validation fails, stop the processing and return back
428 -- to the calling procedure.
429
430 IF igs_re_sprvsr_pkg.get_uk1_for_validation ( p_ca_person_id, p_ca_sequence_number,
431 p_person_id, p_sprvsr_dtls_rec.start_dt ) THEN
432 FND_MESSAGE.Set_Name('IGS','IGS_RE_SPRVSR_EXTS');
433 FND_MSG_PUB.Add;
434 p_sprvsr_status := 'INVALID';
435 l_ret_value := 'W';
436 RETURN l_ret_value;
437 END IF;
438
439 IF NOT igs_re_candidature_pkg.get_pk_for_validation ( p_ca_person_id, p_ca_sequence_number ) THEN
440 FND_MESSAGE.Set_Name('IGS','IGS_RE_CAND_NOT_EXTS');
441 FND_MSG_PUB.Add;
442 p_sprvsr_status := 'INVALID';
443 l_ret_value := 'E';
444 END IF;
445
446 IF NOT igs_re_sprvsr_type_pkg.get_pk_for_validation (p_sprvsr_dtls_rec.research_supervisor_type) THEN
447 FND_MESSAGE.Set_Name('IGS','IGS_RE_SPRVSR_TYP_NT_EXTS');
448 fnd_message.set_token('TYPE',p_sprvsr_dtls_rec.research_supervisor_type);
449 FND_MSG_PUB.Add;
450 p_sprvsr_status := 'INVALID';
451 l_ret_value := 'E';
452 END IF;
453
454 RETURN l_ret_value;
455
456 END validate_sprvsr_db_cons;
457
458
459 PROCEDURE create_sprvsr
460 (
461 p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2,
463 p_commit IN VARCHAR2,
464 p_validation_level IN NUMBER,
465 p_sprvsr_dtls_rec IN sprvsr_dtls_rec_type ,
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_msg_count OUT NOCOPY NUMBER,
468 x_msg_data OUT NOCOPY VARCHAR2
469 ) AS
470
471 /**********************************************************************************************
472 Created By : pradhakr
473 Date Created By : 14-Nov-02
474 Purpose : This procedure imports the legacy data inot OSS tables. Before inserting it
475 validates the input parameters, checks for Data Integrity Constraints and
476 business validations.
477 Known limitations,enhancements,remarks:
478 Change History
479 Who When What
480 Nalin Kumar 28-Jan-2003 Modified create_sprvsr.c_repl_person_dtls cursor to fetch sequence_number by comparing person_id;
481 Previously it was fetching replaced_sequence_number by comparing replaced_person_id;
482 This is to fix bug# 2725852.
483 ***********************************************************************************************/
484
485 CURSOR c_next_val IS
486 SELECT igs_re_sprvsr_seq_num_s.nextval
487 FROM dual;
488
489 CURSOR c_repl_person_dtls (l_repl_person_id igs_re_sprvsr.replaced_person_id%TYPE) IS
490 SELECT sequence_number
491 FROM igs_re_sprvsr
492 WHERE person_id = l_repl_person_id;
493
494
495 l_ca_sequence_number igs_re_sprvsr.ca_sequence_number%TYPE;
496 l_ca_person_id igs_re_sprvsr.ca_person_id%TYPE;
497 l_person_id igs_re_sprvsr.person_id%TYPE;
498 l_replaced_person_id igs_re_sprvsr.replaced_person_id%TYPE;
499 l_ou_start_dt DATE;
500 l_result BOOLEAN;
501 l_api_name CONSTANT VARCHAR2(30) := 'Create_Sprvsr';
502 l_api_version CONSTANT NUMBER := 1.0;
503 p_sprvsr_status VARCHAR2(10) DEFAULT 'VALID';
504 l_sequence_number NUMBER;
505 l_replaced_sequence_number igs_re_sprvsr.replaced_sequence_number%TYPE;
506
507 l_creation_date DATE;
508 l_last_update_date DATE;
509 l_created_by NUMBER;
510 l_last_updated_by NUMBER;
511 l_last_update_login NUMBER;
512 l_ret_val VARCHAR2(1) DEFAULT 'S';
513
514 BEGIN
515
516 -- Create a savepoint.
517 SAVEPOINT create_re_sprvsr_pub;
518
519 -- Check for the Compatible API call
520 IF NOT FND_API.Compatible_Api_Call( l_api_version,
521 p_api_version,
522 l_api_name,
523 g_pkg_name) THEN
524
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 -- If the calling program has passed the parameter for initializing the message list
529 IF FND_API.To_Boolean(p_init_msg_list) THEN
530 FND_MSG_PUB.Initialize;
531 END IF;
532
533 -- Set the return status to success
534 x_return_status := FND_API.G_RET_STS_SUCCESS;
535
536 -- Validate input paramaters
537 p_sprvsr_status := validate_parameters(p_sprvsr_dtls_rec);
538
539 IF p_sprvsr_status = 'INVALID' THEN
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 END IF;
542
543 IF p_sprvsr_status = 'VALID' THEN
544 -- Check whether ca_person_number is valid or not.
545 l_ca_person_id := Igs_Ge_Gen_003.Get_Person_id (p_sprvsr_dtls_rec.ca_person_number);
546
547 IF l_ca_person_id IS NULL THEN
548 -- Add exception to stack
549 FND_MESSAGE.Set_Name('IGS','IGS_GE_INVALID_PERSON_NUMBER');
550 FND_MSG_PUB.Add;
551 p_sprvsr_status := 'INVALID';
552 END IF;
553
554 -- Check whether ca_person_number is valid or not.
555 l_person_id := Igs_Ge_Gen_003.Get_Person_id (p_sprvsr_dtls_rec.person_number);
556
557 IF l_person_id IS NULL THEN
558 FND_MESSAGE.Set_Name('IGS','IGS_GE_INVALID_PERSON_NUMBER');
559 FND_MSG_PUB.Add;
560 p_sprvsr_status := 'INVALID';
561 END IF;
562
563 -- Check whether candidacy details exists, if it exists then get the ca_sequence_number
564 l_result := igs_re_val_the.get_candidacy_dtls (
565 l_ca_person_id,
566 p_sprvsr_dtls_rec.program_cd,
567 l_ca_sequence_number
568 );
569
570 IF NOT l_result THEN
571 FND_MESSAGE.Set_Name('IGS','IGS_RE_CAND_NOT_EXTS');
572 FND_MSG_PUB.Add;
573 p_sprvsr_status := 'INVALID';
574 END IF;
575
576 -- Get the start date of the organisation
577 l_result := igs_re_val_rsup.get_org_unit_dtls (
578 p_sprvsr_dtls_rec.org_unit_cd ,
579 l_ou_start_dt
580 );
581
582 IF p_sprvsr_status = 'INVALID' THEN
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 END IF;
585
586 END IF;
587
588
589 IF p_sprvsr_status = 'VALID' THEN
590 -- Validate all db constraints
591 l_ret_val := validate_sprvsr_db_cons ( l_person_id, p_sprvsr_dtls_rec, l_ca_person_id, l_ca_sequence_number, p_sprvsr_status );
592
593 IF l_ret_val = 'E' THEN
594 x_return_status := FND_API.G_RET_STS_ERROR;
595 ELSIF l_ret_val = 'W' THEN
596 x_return_status := 'W';
597 ELSIF l_ret_val = 'S' THEN
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599 END IF;
600 END IF;
601
602
603 IF p_sprvsr_status = 'VALID' THEN
604
605 IF p_sprvsr_dtls_rec.replaced_person_number IS NOT NULL THEN
606 l_replaced_person_id := Igs_Ge_Gen_003.Get_Person_id (p_sprvsr_dtls_rec.replaced_person_number);
607 OPEN c_repl_person_dtls(l_replaced_person_id);
608 FETCH c_repl_person_dtls INTO l_replaced_sequence_number;
609 CLOSE c_repl_person_dtls;
610 END IF;
611
612 -- Validate the business rules
613 p_sprvsr_status := validate_sprvsr ( l_person_id, p_sprvsr_dtls_rec, l_ca_person_id, l_ca_sequence_number , l_ou_start_dt);
614
615 IF p_sprvsr_status = 'INVALID' THEN
616 x_return_status := FND_API.G_RET_STS_ERROR;
617 END IF;
618
619 END IF;
620
621
622
623 IF p_sprvsr_status = 'VALID' THEN
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 l_creation_date := SYSDATE;
627 l_created_by := FND_GLOBAL.USER_ID;
628 l_last_update_date := SYSDATE;
629 l_last_updated_by := FND_GLOBAL.USER_ID;
630 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
631
632 IF l_created_by IS NULL THEN
633 l_created_by := -1;
634 END IF;
635
636 IF l_last_updated_by IS NULL THEN
637 l_last_updated_by := -1;
638 END IF;
639
640 IF l_last_update_login IS NULL THEN
641 l_last_update_login := -1;
642 END IF;
643
644 BEGIN
645
646 OPEN c_next_val;
647 FETCH c_next_val INTO l_sequence_number;
648 CLOSE c_next_val;
649
650 -- Insert the record in IGS_RE_SPRVSR table
651 INSERT INTO igs_re_sprvsr (
652 ca_person_id,
653 ca_sequence_number,
654 person_id,
655 sequence_number,
656 start_dt,
657 end_dt,
658 research_supervisor_type,
659 supervisor_profession,
660 supervision_percentage,
661 funding_percentage,
662 org_unit_cd,
663 ou_start_dt,
664 replaced_person_id,
665 replaced_sequence_number,
666 comments,
667 created_by,
668 creation_date,
669 last_updated_by,
670 last_update_date,
671 last_update_login )
672 VALUES (
673 l_ca_person_id,
674 l_ca_sequence_number,
675 l_person_id,
676 l_sequence_number,
677 p_sprvsr_dtls_rec.start_dt,
678 p_sprvsr_dtls_rec.end_dt,
679 p_sprvsr_dtls_rec.research_supervisor_type,
680 p_sprvsr_dtls_rec.supervisor_profession,
681 p_sprvsr_dtls_rec.supervision_percentage,
682 p_sprvsr_dtls_rec.funding_percentage,
683 p_sprvsr_dtls_rec.org_unit_cd,
684 l_ou_start_dt,
685 l_replaced_person_id,
686 l_replaced_sequence_number,
687 p_sprvsr_dtls_rec.comments,
688 l_created_by,
689 l_creation_date,
690 l_last_updated_by,
691 l_last_update_date,
692 l_last_update_login
693 );
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 p_sprvsr_status := 'INVALID';
699 ROLLBACK TO create_re_sprvsr_pub;
700 END;
701
702 -- Commit the record which is inserted in the table.
703 IF (FND_API.To_Boolean(p_commit) and p_sprvsr_status = 'VALID') THEN
704 COMMIT WORK;
705 END IF;
706
707 END IF;
708
709 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
710 p_data => x_msg_data);
711
712 EXCEPTION
713 WHEN FND_API.G_EXC_ERROR THEN
714 ROLLBACK TO create_re_sprvsr_pub;
715 x_return_status := FND_API.G_RET_STS_ERROR;
716 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
717 p_data => x_msg_data);
718
719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
720 ROLLBACK TO create_re_sprvsr_pub;
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
723 p_data => x_msg_data);
724
725 WHEN OTHERS THEN
726 ROLLBACK TO create_re_sprvsr_pub;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
729 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
730 l_api_name);
731 END IF;
732 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
733 p_data => x_msg_data);
734
735 END create_sprvsr;
736
737 END igs_re_sprvsr_lgcy_pub;
[Home] [Help]