1 package body IGS_RE_SPRVSR_PKG as
2 /* $Header: IGSRI13B.pls 120.1 2005/07/04 00:42:01 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_rsup.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references IGS_RE_SPRVSR%RowType;
11 new_references IGS_RE_SPRVSR%RowType;
12
13 PROCEDURE Set_Column_Values (
14 p_action IN VARCHAR2,
15 x_rowid IN VARCHAR2 ,
16 x_ca_person_id IN NUMBER ,
17 x_ca_sequence_number IN NUMBER ,
18 x_person_id IN NUMBER ,
19 x_sequence_number IN NUMBER ,
20 x_start_dt IN DATE ,
21 x_end_dt IN DATE ,
22 x_research_supervisor_type IN VARCHAR2 ,
23 x_supervisor_profession IN VARCHAR2 ,
24 x_supervision_percentage IN NUMBER ,
25 x_funding_percentage IN NUMBER ,
26 x_org_unit_cd IN VARCHAR2 ,
27 x_ou_start_dt IN DATE ,
28 x_replaced_person_id IN NUMBER ,
29 x_replaced_sequence_number IN NUMBER ,
30 x_comments IN VARCHAR2 ,
31 x_creation_date IN DATE ,
32 x_created_by IN NUMBER ,
33 x_last_update_date IN DATE ,
34 x_last_updated_by IN NUMBER ,
35 x_last_update_login IN NUMBER
36 ) AS
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM IGS_RE_SPRVSR
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 Open cur_old_ref_values;
50 Fetch cur_old_ref_values INTO old_references;
51 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
52 Close cur_old_ref_values;
53 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 Return;
57 END IF;
58 Close cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.ca_person_id := x_ca_person_id;
62 new_references.ca_sequence_number := x_ca_sequence_number;
63 new_references.person_id := x_person_id;
64 new_references.sequence_number := x_sequence_number;
65 new_references.start_dt := x_start_dt;
66 new_references.end_dt := x_end_dt;
67 new_references.research_supervisor_type := x_research_supervisor_type;
68 new_references.supervisor_profession := x_supervisor_profession;
69 new_references.supervision_percentage := x_supervision_percentage;
70 new_references.funding_percentage := x_funding_percentage;
71 new_references.org_unit_cd := x_org_unit_cd;
72 new_references.ou_start_dt := x_ou_start_dt;
73 new_references.replaced_person_id := x_replaced_person_id;
74 new_references.replaced_sequence_number := x_replaced_sequence_number;
75 new_references.comments := x_comments;
76 IF (p_action = 'UPDATE') THEN
77 new_references.creation_date := old_references.creation_date;
78 new_references.created_by := old_references.created_by;
79 ELSE
80 new_references.creation_date := x_creation_date;
81 new_references.created_by := x_created_by;
82 END IF;
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END Set_Column_Values;
88
89 PROCEDURE BeforeRowInsertUpdateDelete1(
90 p_inserting IN BOOLEAN ,
91 p_updating IN BOOLEAN ,
92 p_deleting IN BOOLEAN
93 ) AS
94 v_message_name VARCHAR2(30);
95 p_legacy VARCHAR2(1);
96 BEGIN
97 p_legacy := 'N';
98
99 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
100 -- as a result of IGS_PS_COURSE transfer
101 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
102 IF p_inserting OR
103 (p_updating AND
104 (new_references.start_dt <> old_references.start_dt) OR
105 (NVL(new_references.end_dt,igs_ge_date.igsdate('9999/01/01')) <>
106 NVL(old_references.end_dt,igs_ge_date.igsdate('9999/01/01'))) OR
107 (NVL(new_references.supervision_percentage,-1) <>
108 NVL(old_references.supervision_percentage,-1)) OR
109 (new_references.research_supervisor_type <>
110 old_references.research_supervisor_type) OR
111 (NVL(new_references.funding_percentage,-1) <>
112 NVL(old_references.funding_percentage,-1)) OR
113 (NVL(new_references.org_unit_cd,'NULL') <>
114 NVL(old_references.org_unit_cd,'NULL')) OR
115 (NVL(new_references.replaced_person_id,-1) <>
116 NVL(old_references.replaced_person_id,-1))) THEN
117 -- Validate changes are allowed to be made
118 /*
119 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
120 || Changed the reference of "IGS_RE_VAL_RSUP.RESP_VAL_CA_CHILDUPD" to program unit "IGS_RE_VAL_CAH.RESP_VAL_CA_CHILDUPD". -- kdande
121 */
122 IF IGS_RE_VAL_CAH.resp_val_ca_childupd(
123 new_references.ca_person_id,
124 new_references.ca_sequence_number,
125 v_message_name) = FALSE THEN
126 Fnd_Message.Set_Name ('IGS', v_message_name);
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131 IF p_inserting THEN
132 -- Validate IGS_PE_PERSON
133 IF IGS_RE_VAL_RSUP.resp_val_rsup_person(
134 new_references.ca_person_id,
135 new_references.person_id,
136 p_legacy,
137 v_message_name) = FALSE THEN
138 Fnd_Message.Set_Name ('IGS', v_message_name);
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141 END IF;
142 END IF;
143 IF p_inserting OR
144 p_updating THEN
145 -- Validate that research supervisor type
146 IF p_inserting OR
147 (p_updating AND
148 new_references.research_supervisor_type <> old_references.research_supervisor_type) THEN
149 IF IGS_RE_VAL_RSUP.resp_val_rst_closed (
150 new_references.research_supervisor_type,
151 v_message_name) = FALSE THEN
152 Fnd_Message.Set_Name ('IGS', v_message_name);
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 END IF;
157 -- Validate funding percentage
158 IF p_inserting OR
159 (p_updating AND
160 ((NVL(new_references.funding_percentage,-1) <> NVL(old_references.funding_percentage,-1)) OR
161 new_references.org_unit_cd IS NULL)) THEN
162 /*
163 IF IGS_RE_VAL_RSUP.resp_val_rsup_fund(
164 new_references.person_id,
165 new_references.org_unit_cd,
166 new_references.ou_start_dt,
167 new_references.funding_percentage,
168 NULL, -- staff member indicator
169 v_message_name) = FALSE THEN
170 Fnd_Message.Set_Name ('IGS', v_message_name);
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173
174 END IF;
175 */
176 -- This code has been commented because the validation has
177 -- been taken care at the library at the event ON_COMMIT.
178 NULL;
179 END IF;
180
181 -- Validate Organisational IGS_PS_UNIT
182 IF new_references.org_unit_cd IS NOT NULL THEN
183 IF p_inserting OR
184 (p_updating AND
185 NVL(new_references.funding_percentage,-1) = NVL(old_references.funding_percentage,-1) AND
186 ((NVL(new_references.org_unit_cd,'NULL') <> NVL(old_references.org_unit_cd,'NULL')) OR
187 (NVL(new_references.ou_start_dt,igs_ge_date.igsdate('9999/01/01')) <>
188 NVL(old_references.ou_start_dt,igs_ge_date.igsdate('9999/01/01'))))) THEN
189
190 IF IGS_RE_VAL_RSUP.resp_val_rsup_ou(
191 new_references.person_id,
192 new_references.org_unit_cd,
193 new_references.ou_start_dt,
194 NULL, -- staff member indicator
195 p_legacy,
196 v_message_name) = FALSE THEN
197 Fnd_Message.Set_Name ('IGS', v_message_name);
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202 END IF;
203 END IF;
204 END IF;
205 IF p_deleting THEN
206 -- Validate deletes are allowed
207 /*
208 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
209 || Changed the reference of "IGS_RE_VAL_RSUP.RESP_VAL_CA_CHILDUPD" to program unit "IGS_RE_VAL_CAH.RESP_VAL_CA_CHILDUPD". -- kdande
210 */
211 IF IGS_RE_VAL_CAH.resp_val_ca_childupd(
212 old_references.ca_person_id,
213 old_references.ca_sequence_number,
214 v_message_name) = FALSE THEN
215 Fnd_Message.Set_Name ('IGS', v_message_name);
216 IGS_GE_MSG_STACK.ADD;
217 App_Exception.Raise_Exception;
218 END IF;
219 END IF;
220
221
222 END BeforeRowInsertUpdateDelete1;
223
224 PROCEDURE AfterRowInsertUpdate2(
225 p_inserting IN BOOLEAN ,
226 p_updating IN BOOLEAN ,
227 p_deleting IN BOOLEAN
228 ) AS
229 v_message_name VARCHAR2(30);
230 v_supervision_start_dt DATE;
231 p_legacy VARCHAR2(1);
232 BEGIN
233 p_legacy := 'N';
234
235 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
236 -- as a result of IGS_PS_COURSE transfer
237 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
238 IF p_inserting OR
239 p_updating THEN
240 -- Set rowid
241
242
243 --Validate research supervisor end date
244 IF (NVL( NEW_REFERENCES.end_dt,igs_ge_date.igsdate('9999/01/01')) <>
245 NVL(OLD_REFERENCES.end_dt,igs_ge_date.igsdate('9999/01/01')))
246 THEN
247 IF IGS_RE_VAL_RSUP.resp_val_rsup_end_dt(
248 NEW_REFERENCES.ca_person_id,
249 NEW_REFERENCES.ca_sequence_number,
250 NEW_REFERENCES.person_id,
251 NEW_REFERENCES.sequence_number,
252 NEW_REFERENCES.start_dt,
253 NEW_REFERENCES.end_dt,
254 p_legacy,
255 v_message_name) = FALSE THEN
256 Fnd_Message.Set_Name ('IGS', v_message_name);
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 END IF;
261
262 -- Validate research supervisor overlapping periods
263 IF p_inserting OR
264 (p_updating AND
265 (NEW_REFERENCES.start_dt <> OLD_REFERENCES.start_dt) OR
266 (NVL(NEW_REFERENCES.end_dt,igs_ge_date.igsdate('9999/01/01')) <>
267 NVL(OLD_REFERENCES.end_dt,igs_ge_date.igsdate('9999/01/01'))))
268 THEN
269 IF IGS_RE_VAL_RSUP.resp_val_rsup_ovrlp(
270 NEW_REFERENCES.ca_person_id,
271 NEW_REFERENCES.ca_sequence_number,
272 NEW_REFERENCES.person_id,
273 NEW_REFERENCES.sequence_number,
274 NEW_REFERENCES.start_dt,
275 NEW_REFERENCES.end_dt,
276 p_legacy,
277 v_message_name) = FALSE THEN
278 Fnd_Message.Set_Name ('IGS', v_message_name);
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 END IF;
283
284 -- Validate replaced supervisor
285 IF NEW_REFERENCES.replaced_person_id IS NOT NULL THEN
286 IF p_inserting OR
287 (p_updating AND
288 (NEW_REFERENCES.replaced_person_id <>
289 NVL(OLD_REFERENCES.replaced_person_id,0)) OR
290 (NEW_REFERENCES.replaced_sequence_number <>
291 NVL(OLD_REFERENCES.replaced_sequence_number,0)) OR
292 (NEW_REFERENCES.start_dt <> OLD_REFERENCES.start_dt)) THEN
293 IF IGS_RE_VAL_RSUP.resp_val_rsup_repl(
294 NEW_REFERENCES.ca_person_id,
295 NEW_REFERENCES.ca_sequence_number,
296 NEW_REFERENCES.person_id,
297 NEW_REFERENCES.start_dt,
298 NEW_REFERENCES.replaced_person_id,
299 NEW_REFERENCES.replaced_sequence_number,
300 p_legacy,
301 v_message_name) = FALSE THEN
302 Fnd_Message.Set_Name ('IGS', v_message_name);
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306 END IF;
307 END IF;
308
309 -- Do not validate at database level if being updated via form RESF3250
310 IF igs_as_val_suaap.genp_val_sdtt_sess('RESP_VAL_RSUP_PERC') THEN
311 -- Validate supervision and funding percentage
312 IF p_inserting OR
313 (p_updating AND
314 (NEW_REFERENCES.start_dt <> OLD_REFERENCES.start_dt) OR
315 (NVL(NEW_REFERENCES.end_dt,igs_ge_date.igsdate('9999/01/01')) <>
316 NVL(OLD_REFERENCES.end_dt,
317 igs_ge_date.igsdate('9999/01/01'))) OR
318 (NVL(NEW_REFERENCES.supervision_percentage,-1) <>
319 NVL(OLD_REFERENCES.supervision_percentage,-1)) OR
320 (NVL(NEW_REFERENCES.funding_percentage,-1) <>
321 NVL(OLD_REFERENCES.funding_percentage,-1))) THEN
322 /*
323 IF IGS_RE_VAL_RSUP.resp_val_rsup_perc(
324 NEW_REFERENCES.ca_person_id,
325 NEW_REFERENCES.ca_sequence_number,
326 NULL, -- sca_course_cd
327 NULL, -- acai_admission_appl_number
328 NULL, -- acai_nominated_course_cd
329 NULL, -- acai_sequence_number,
330 'Y', -- Validate supervision percentage
331 'Y', -- Validate funding percentage
332 'RSUP',
333 V_supervision_start_dt,
334 v_message_name) = FALSE THEN
335 Fnd_Message.Set_Name ('IGS', v_message_name);
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339 */
340 NULL;
341 -- This code has been commented out NOCOPY because the validations have
342 -- been taken care at the library.
343 END IF;
344 END IF;
345 END IF;
346 END IF;
347
348
349 IF p_deleting THEN
350 -- Do not validate at database level if being updated via form RESF3250
351 IF igs_as_val_suaap.genp_val_sdtt_sess('RESP_VAL_RSUP_PERC') THEN
352 -- Validate supervision and funding percentage
353 /*
354 IF IGS_RE_VAL_RSUP.resp_val_rsup_perc(
355 OLD_REFERENCES.ca_person_id,
356 OLD_REFERENCES.ca_sequence_number,
357 NULL, -- sca_course_cd
358 NULL, -- acai_admission_appl_number
359 NULL, -- acai_nominated_course_cd
360 NULL, -- acai_sequence_number,
361 'Y', -- Validate supervision percentage
362 'Y', -- Validate funding percentage
363 'RSUP',
364 v_supervision_start_dt,
365 v_message_name) = FALSE THEN
366 Fnd_Message.Set_Name ('IGS', v_message_name);
367 IGS_GE_MSG_STACK.ADD;
368 App_Exception.Raise_Exception;
369 END IF;*/
370 -- This code has been commented out NOCOPY because this
371 -- validation is done at the library .
372 NULL;
373 END IF;
374 END IF;
375
376 -- Bug # 2829275 . UK Correspondence.The TBH needs to be modified to invoke the supervision event is raised when supervisor attributes of a student changes.
377
378
379 IF (p_inserting
380 OR(p_updating AND ((new_references.start_dt <> old_references.start_dt )OR
381 ((new_references.end_dt <> old_references.end_dt ) OR (old_references.end_dt IS NULL AND new_references.end_dt IS NOT NULL) OR (new_references.end_dt IS NULL AND old_references.end_dt IS NOT NULL)) OR
382 (new_references.research_supervisor_type <> old_references.research_supervisor_type) OR
383 ((new_references.supervision_percentage <> old_references.supervision_percentage) OR (old_references.supervision_percentage IS NULL AND new_references.supervision_percentage IS NOT NULL)
384 OR (old_references.supervision_percentage IS NOT NULL AND new_references.supervision_percentage IS NULL)) OR
385 ((new_references.org_unit_cd <> old_references.org_unit_cd) OR (old_references.org_unit_cd IS NULL AND new_references.org_unit_cd IS NOT NULL)
386 OR (old_references.org_unit_cd IS NOT NULL AND new_references.org_unit_cd IS NULL) ) OR
387 ((new_references.replaced_person_id <> old_references.replaced_person_id )OR (old_references.replaced_person_id IS NULL AND new_references.replaced_person_id IS NOT NULL)
388 OR (old_references.replaced_person_id IS NOT NULL AND new_references.replaced_person_id IS NULL))))) THEN
389
390 igs_re_workflow.supervision_event (
391 p_personid => new_references.ca_person_id,
392 p_ca_seq_num => new_references.ca_sequence_number,
393 p_supervisorid => new_references.person_id,
394 p_startdt => new_references.start_dt,
395 p_enddt => new_references.end_dt,
396 p_spr_percent => new_references.supervision_percentage,
397 p_spr_type => new_references.research_supervisor_type,
398 p_fund_percent => new_references.funding_percentage,
399 p_org_unit_cd => new_references.org_unit_cd,
400 p_rep_person_id => new_references.replaced_person_id,
401 p_rep_seq_num => new_references.replaced_sequence_number
402 ) ;
403
404 END IF;
405
406 END AfterRowInsertUpdate2;
407
408 -- Trigger description :-
409 -- "OSS_TST".trg_rsup_as_iud
410 -- AFTER INSERT OR DELETE OR UPDATE
411 -- ON IGS_RE_SPRVSR
412
413 PROCEDURE Check_Constraints (
414 Column_Name in VARCHAR2 ,
415 Column_Value in VARCHAR2
416 ) AS
417 BEGIN
418
419 IF Column_Name is null then
420 NULL;
421 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
422 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
423 ELSIF upper(Column_name) = 'SUPERVISION_PERCENTAGE' THEN
424 new_references.SUPERVISION_PERCENTAGE := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
425 ELSIF upper(Column_name) = 'REPLACED_SEQUENCE_NUMBER' THEN
426 new_references.REPLACED_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
427 ELSIF upper(Column_name) = 'FUNDING_PERCENTAGE' THEN
428 new_references.FUNDING_PERCENTAGE := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
429 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
430 new_references.SEQUENCE_NUMBER := COLUMN_VALUE ;
431 ELSIF upper(Column_name) = 'ORG_UNIT_CD' THEN
432 new_references.ORG_UNIT_CD := COLUMN_VALUE ;
433 ELSIF upper(Column_name) = 'RESEARCH_SUPERVISOR_TYPE' THEN
434 new_references.RESEARCH_SUPERVISOR_TYPE := COLUMN_VALUE ;
435 ELSIF upper(Column_name) = 'SUPERVISOR_PROFESSION' THEN
436 new_references.SUPERVISOR_PROFESSION := COLUMN_VALUE ;
437 END IF;
438
439 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
440 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
441 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
442 IGS_GE_MSG_STACK.ADD;
443 App_Exception.Raise_Exception ;
444 END IF;
445 END IF;
446 IF upper(column_name) = 'SUPERVISION_PERCENTAGE' OR COLUMN_NAME IS NULL THEN
447 IF new_references.SUPERVISION_PERCENTAGE < 0 OR new_references.SUPERVISION_PERCENTAGE > 100 then
448 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
449 IGS_GE_MSG_STACK.ADD;
450 App_Exception.Raise_Exception ;
451 END IF;
452 END IF;
453 IF upper(column_name) = 'REPLACED_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
454 IF new_references.REPLACED_SEQUENCE_NUMBER < 1 OR new_references.REPLACED_SEQUENCE_NUMBER > 999999 then
455 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
456 IGS_GE_MSG_STACK.ADD;
457 App_Exception.Raise_Exception ;
458 END IF;
459 END IF;
460 IF upper(column_name) = 'FUNDING_PERCENTAGE' OR COLUMN_NAME IS NULL THEN
461 IF new_references.FUNDING_PERCENTAGE < 0 OR new_references.FUNDING_PERCENTAGE > 100 then
462 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
463 IGS_GE_MSG_STACK.ADD;
464 App_Exception.Raise_Exception ;
465 END IF;
466 END IF;
467 IF upper(column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
468 IF new_references.SEQUENCE_NUMBER < 1 OR new_references.SEQUENCE_NUMBER > 999999 then
469 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
470 IGS_GE_MSG_STACK.ADD;
471 App_Exception.Raise_Exception ;
472 END IF;
473 END IF;
474
475 IF upper(column_name) = 'RESEARCH_SUPERVISOR_TYPE' OR COLUMN_NAME IS NULL THEN
476 IF new_references.RESEARCH_SUPERVISOR_TYPE <> upper(NEW_REFERENCES.RESEARCH_SUPERVISOR_TYPE) then
477 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
478 IGS_GE_MSG_STACK.ADD;
479 App_Exception.Raise_Exception ;
480 END IF;
481 END IF;
482 IF upper(column_name) = 'SUPERVISOR_PROFESSION' OR COLUMN_NAME IS NULL THEN
483 IF new_references.SUPERVISOR_PROFESSION <> upper(NEW_REFERENCES.SUPERVISOR_PROFESSION) then
484 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception ;
487 END IF;
488 END IF;
489 END Check_Constraints ;
490
491 PROCEDURE Check_Uniqueness AS
492 BEGIN
493 IF Get_UK1_For_Validation (
494 new_references.ca_person_id,
495 new_references.ca_sequence_number,
496 new_references.person_id,
497 new_references.start_dt
498 ) THEN
499 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
500 IGS_GE_MSG_STACK.ADD;
501 App_Exception.Raise_Exception;
502 END IF;
503
504 END Check_Uniqueness;
505
506 PROCEDURE Check_Parent_Existance AS
507 BEGIN
508
509 IF (((old_references.ca_person_id = new_references.ca_person_id) AND
510 (old_references.ca_sequence_number = new_references.ca_sequence_number)) OR
511 ((new_references.ca_person_id IS NULL) OR
512 (new_references.ca_sequence_number IS NULL))) THEN
513 NULL;
514 ELSE
515 IF NOT IGS_RE_CANDIDATURE_PKG.Get_PK_For_Validation (
516 new_references.ca_person_id,
517 new_references.ca_sequence_number
518 ) THEN
519 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
520 IGS_GE_MSG_STACK.ADD;
521 App_Exception.Raise_Exception;
522 END IF;
523 END IF;
524
525 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
526 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
527 ((new_references.org_unit_cd IS NULL) OR
528 (new_references.ou_start_dt IS NULL))) THEN
529 NULL;
530 ELSE
531 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
532 new_references.org_unit_cd,
533 new_references.ou_start_dt
534 ) THEN
535 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
536 IGS_GE_MSG_STACK.ADD;
537 App_Exception.Raise_Exception;
538 END IF;
539 END IF;
540
541 IF (((old_references.person_id = new_references.person_id)) OR
542 ((new_references.person_id IS NULL))) THEN
543 NULL;
544 ELSE
545 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
546 new_references.person_id
547 ) THEN
548 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
549 IGS_GE_MSG_STACK.ADD;
550 App_Exception.Raise_Exception;
551 END IF;
552 END IF;
553
554 IF (((old_references.research_supervisor_type = new_references.research_supervisor_type)) OR
555 ((new_references.research_supervisor_type IS NULL))) THEN
556 NULL;
557 ELSE
558 IF NOT IGS_RE_SPRVSR_TYPE_PKG.Get_PK_For_Validation (
559 new_references.research_supervisor_type
560 ) THEN
561 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
562 IGS_GE_MSG_STACK.ADD;
563 App_Exception.Raise_Exception;
564 END IF;
565 END IF;
566
567 IF (((old_references.ca_person_id = new_references.ca_person_id) AND
568 (old_references.ca_sequence_number = new_references.ca_sequence_number) AND
569 (old_references.replaced_person_id = new_references.replaced_person_id) AND
570 (old_references.replaced_sequence_number = new_references.replaced_sequence_number)) OR
571 ((new_references.ca_person_id IS NULL) OR
572 (new_references.ca_sequence_number IS NULL) OR
573 (new_references.replaced_person_id IS NULL) OR
574 (new_references.replaced_sequence_number IS NULL))) THEN
575 NULL;
576 ELSE
577 IF NOT IGS_RE_SPRVSR_PKG.Get_PK_For_Validation (
578 new_references.ca_person_id,
579 new_references.ca_sequence_number,
580 new_references.replaced_person_id,
581 new_references.replaced_sequence_number
582 ) THEN
583 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
584 IGS_GE_MSG_STACK.ADD;
585 App_Exception.Raise_Exception;
586 END IF;
587 END IF;
588
589 END Check_Parent_Existance;
590
591 PROCEDURE Check_Child_Existance AS
592 BEGIN
593
594 IGS_RE_SPRVSR_PKG.GET_FK_IGS_RE_SPRVSR (
595 old_references.ca_person_id,
596 old_references.ca_sequence_number,
597 old_references.person_id,
598 old_references.sequence_number
599 );
600
601 END Check_Child_Existance;
602
603 FUNCTION Get_PK_For_Validation (
604 x_ca_person_id IN NUMBER,
605 x_ca_sequence_number IN NUMBER,
606 x_person_id IN NUMBER,
607 x_sequence_number IN NUMBER
608 )
609 RETURN BOOLEAN
610 AS
611
612 CURSOR cur_rowid IS
613 SELECT rowid
614 FROM IGS_RE_SPRVSR
615 WHERE ca_person_id = x_ca_person_id
616 AND ca_sequence_number = x_ca_sequence_number
617 AND person_id = x_person_id
618 AND sequence_number = x_sequence_number
619 FOR UPDATE NOWAIT;
620
621 lv_rowid cur_rowid%RowType;
622
623 BEGIN
624
625 Open cur_rowid;
626 Fetch cur_rowid INTO lv_rowid;
627 IF (cur_rowid%FOUND) THEN
628 Close cur_rowid;
629 RETURN(TRUE);
630 ELSE
631 Close cur_rowid;
632 RETURN(FALSE);
633 END IF;
634
635 END Get_PK_For_Validation;
636
637 FUNCTION Get_UK1_For_Validation (
638 x_ca_person_id IN NUMBER,
639 x_ca_sequence_number IN NUMBER,
640 x_person_id IN NUMBER,
641 x_start_dt IN DATE
642 )
643 RETURN BOOLEAN
644 AS
645 CURSOR cur_rowid IS
646 SELECT rowid
647 FROM IGS_RE_SPRVSR
648 WHERE ca_person_id = x_ca_person_id
649 AND ca_sequence_number = x_ca_sequence_number
650 AND person_id = x_person_id
651 AND start_dt = x_start_dt
652 AND ((l_rowid is null) or (rowid <> l_rowid))
653 FOR UPDATE NOWAIT;
654
655 lv_rowid cur_rowid%RowType;
656
657 BEGIN
658 Open cur_rowid;
659 Fetch cur_rowid INTO lv_rowid;
660 IF (cur_rowid%FOUND) THEN
661 Close cur_rowid;
662 RETURN(TRUE);
663 ELSE
664 Close cur_rowid;
665 RETURN(FALSE);
666 END IF;
667
668 END Get_UK1_For_Validation;
669
670 PROCEDURE GET_FK_IGS_RE_CANDIDATURE (
671 x_person_id IN NUMBER,
672 x_sequence_number IN NUMBER
673 ) AS
674
675 CURSOR cur_rowid IS
676 SELECT rowid
677 FROM IGS_RE_SPRVSR
678 WHERE ca_person_id = x_person_id
679 AND ca_sequence_number = x_sequence_number ;
680
681 lv_rowid cur_rowid%RowType;
682
683 BEGIN
684
685 Open cur_rowid;
686 Fetch cur_rowid INTO lv_rowid;
687 IF (cur_rowid%FOUND) THEN
688 Close cur_rowid;
689 Fnd_Message.Set_Name ('IGS', 'IGS_RE_RSUP_CA_FK');
690 IGS_GE_MSG_STACK.ADD;
691 App_Exception.Raise_Exception;
692 Return;
693 END IF;
694 Close cur_rowid;
695
696 END GET_FK_IGS_RE_CANDIDATURE;
697
698 PROCEDURE GET_FK_IGS_OR_UNIT (
699 x_org_unit_cd IN VARCHAR2,
700 x_start_dt IN DATE
701 ) AS
702
703 CURSOR cur_rowid IS
704 SELECT rowid
705 FROM IGS_RE_SPRVSR
706 WHERE org_unit_cd = x_org_unit_cd
707 AND ou_start_dt = x_start_dt ;
708
709 lv_rowid cur_rowid%RowType;
710
711 BEGIN
712
713 Open cur_rowid;
714 Fetch cur_rowid INTO lv_rowid;
715 IF (cur_rowid%FOUND) THEN
716 Close cur_rowid;
717 Fnd_Message.Set_Name ('IGS', 'IGS_RE_RSUP_OU_FK');
718 IGS_GE_MSG_STACK.ADD;
719 App_Exception.Raise_Exception;
720 Return;
721 END IF;
722 Close cur_rowid;
723
724 END GET_FK_IGS_OR_UNIT;
725
726 PROCEDURE GET_FK_IGS_PE_PERSON (
727 x_person_id IN NUMBER
728 ) AS
729
730 CURSOR cur_rowid IS
731 SELECT rowid
732 FROM IGS_RE_SPRVSR
733 WHERE person_id = x_person_id ;
734
735 lv_rowid cur_rowid%RowType;
736
737 BEGIN
738
739 Open cur_rowid;
740 Fetch cur_rowid INTO lv_rowid;
741 IF (cur_rowid%FOUND) THEN
742 Close cur_rowid;
743 Fnd_Message.Set_Name ('IGS', 'IGS_RE_RSUP_PE_FK');
744 IGS_GE_MSG_STACK.ADD;
745 App_Exception.Raise_Exception;
746 Return;
747 END IF;
748 Close cur_rowid;
749
750 END GET_FK_IGS_PE_PERSON;
751
752 PROCEDURE GET_FK_IGS_RE_SPRVSR_TYPE (
753 x_research_supervisor_type IN VARCHAR2
754 ) AS
755
756 CURSOR cur_rowid IS
757 SELECT rowid
758 FROM IGS_RE_SPRVSR
759 WHERE research_supervisor_type = x_research_supervisor_type ;
760
761 lv_rowid cur_rowid%RowType;
762
763 BEGIN
764
765 Open cur_rowid;
766 Fetch cur_rowid INTO lv_rowid;
767 IF (cur_rowid%FOUND) THEN
768 Close cur_rowid;
769 Fnd_Message.Set_Name ('IGS', 'IGS_RE_RSUP_RST_FK');
770 IGS_GE_MSG_STACK.ADD;
771 App_Exception.Raise_Exception;
772 Return;
773 END IF;
774 Close cur_rowid;
775
776 END GET_FK_IGS_RE_SPRVSR_TYPE;
777
778 PROCEDURE GET_FK_IGS_RE_SPRVSR (
779 x_ca_person_id IN NUMBER,
780 x_ca_sequence_number IN NUMBER,
781 x_person_id IN NUMBER,
782 x_sequence_number IN NUMBER
783 ) AS
784
785 CURSOR cur_rowid IS
786 SELECT rowid
787 FROM IGS_RE_SPRVSR
788 WHERE ca_person_id = x_ca_person_id
789 AND ca_sequence_number = x_ca_sequence_number
790 AND replaced_person_id = x_person_id
791 AND replaced_sequence_number = x_sequence_number ;
792
793 lv_rowid cur_rowid%RowType;
794
795 BEGIN
796
797 Open cur_rowid;
798 Fetch cur_rowid INTO lv_rowid;
799 IF (cur_rowid%FOUND) THEN
800 Close cur_rowid;
801 Fnd_Message.Set_Name ('IGS', 'IGS_RE_RSUP_RSUP_FK');
802 IGS_GE_MSG_STACK.ADD;
803 App_Exception.Raise_Exception;
804 Return;
805 END IF;
806 Close cur_rowid;
807
808 END GET_FK_IGS_RE_SPRVSR;
809
810 PROCEDURE Before_DML (
811 p_action IN VARCHAR2,
812 x_rowid IN VARCHAR2 ,
813 x_ca_person_id IN NUMBER ,
814 x_ca_sequence_number IN NUMBER ,
815 x_person_id IN NUMBER ,
816 x_sequence_number IN NUMBER ,
817 x_start_dt IN DATE ,
818 x_end_dt IN DATE ,
819 x_research_supervisor_type IN VARCHAR2 ,
820 x_supervisor_profession IN VARCHAR2 ,
821 x_supervision_percentage IN NUMBER ,
822 x_funding_percentage IN NUMBER ,
823 x_org_unit_cd IN VARCHAR2 ,
824 x_ou_start_dt IN DATE ,
825 x_replaced_person_id IN NUMBER ,
826 x_replaced_sequence_number IN NUMBER ,
827 x_comments IN VARCHAR2 ,
828 x_creation_date IN DATE ,
829 x_created_by IN NUMBER ,
830 x_last_update_date IN DATE ,
831 x_last_updated_by IN NUMBER ,
832 x_last_update_login IN NUMBER
833 ) AS
834 BEGIN
835
836 Set_Column_Values (
837 p_action,
838 x_rowid,
839 x_ca_person_id,
840 x_ca_sequence_number,
841 x_person_id,
842 x_sequence_number,
843 x_start_dt,
844 x_end_dt,
845 x_research_supervisor_type,
846 x_supervisor_profession,
847 x_supervision_percentage,
848 x_funding_percentage,
849 x_org_unit_cd,
850 x_ou_start_dt,
851 x_replaced_person_id,
852 x_replaced_sequence_number,
853 x_comments,
854 x_creation_date,
855 x_created_by,
856 x_last_update_date,
857 x_last_updated_by,
858 x_last_update_login
859 );
860
861 IF (p_action = 'INSERT') THEN
862 -- Call all the procedures related to Before Insert.
863 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
864 p_updating => FALSE,
865 p_deleting => FALSE
866 );
867 IF Get_PK_For_Validation (
868 new_references.ca_person_id,
869 new_references.ca_sequence_number,
870 new_references.person_id,
871 new_references.sequence_number
872 ) THEN
873 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
874 IGS_GE_MSG_STACK.ADD;
875 App_Exception.Raise_Exception;
876 END IF;
877 Check_Uniqueness;
878 Check_Constraints;
879 Check_Parent_Existance;
880 ELSIF (p_action = 'UPDATE') THEN
881 -- Call all the procedures related to Before Update.
882 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
883 p_updating => TRUE,
884 p_deleting => FALSE
885 );
886 Check_Uniqueness;
887 Check_Constraints;
888 Check_Parent_Existance;
889 ELSIF (p_action = 'DELETE') THEN
890 -- Call all the procedures related to Before Delete.
891 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
892 p_updating => FALSE,
893 p_deleting => TRUE
894 );
895 Check_Child_Existance;
896 ELSIF (p_action = 'VALIDATE_INSERT') THEN
897 IF Get_PK_For_Validation (
898 new_references.ca_person_id,
899 new_references.ca_sequence_number,
900 new_references.person_id,
901 new_references.sequence_number
902 ) THEN
903 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
904 IGS_GE_MSG_STACK.ADD;
905 App_Exception.Raise_Exception;
906 END IF;
907 Check_Uniqueness;
908 Check_Constraints;
909 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
910 Check_Uniqueness;
911 Check_Constraints;
912 ELSIF (p_action = 'VALIDATE_DELETE') THEN
913 Check_Child_Existance;
914 END IF;
915 END Before_DML;
916
917 PROCEDURE After_DML (
918 p_action IN VARCHAR2,
919 x_rowid IN VARCHAR2
920 ) AS
921 BEGIN
922
923 l_rowid := x_rowid;
924
925 IF (p_action = 'INSERT') THEN
926 -- Call all the procedures related to After Insert.
927 AfterRowInsertUpdate2 ( p_inserting => TRUE,
928 p_updating => FALSE,
929 p_deleting => FALSE
930 );
931 ELSIF (p_action = 'UPDATE') THEN
932 -- Call all the procedures related to After Update.
933 AfterRowInsertUpdate2 ( p_inserting => FALSE,
934 p_updating => TRUE,
935 p_deleting => FALSE
936 );
937 END IF;
938 END After_DML;
939
940 procedure INSERT_ROW (
941 X_ROWID in out NOCOPY VARCHAR2,
942 X_CA_PERSON_ID in NUMBER,
943 X_CA_SEQUENCE_NUMBER in NUMBER,
944 X_PERSON_ID in NUMBER,
945 X_SEQUENCE_NUMBER in NUMBER,
946 X_START_DT in DATE,
947 X_END_DT in DATE,
948 X_RESEARCH_SUPERVISOR_TYPE in VARCHAR2,
949 X_SUPERVISOR_PROFESSION in VARCHAR2,
950 X_SUPERVISION_PERCENTAGE in NUMBER,
951 X_FUNDING_PERCENTAGE in NUMBER,
952 X_ORG_UNIT_CD in VARCHAR2,
953 X_OU_START_DT in DATE,
954 X_REPLACED_PERSON_ID in NUMBER,
955 X_REPLACED_SEQUENCE_NUMBER in NUMBER,
956 X_COMMENTS in VARCHAR2,
957 X_MODE in VARCHAR2
958 ) as
959 cursor C is select ROWID from IGS_RE_SPRVSR
960 where CA_PERSON_ID = X_CA_PERSON_ID
961 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
962 and PERSON_ID = X_PERSON_ID
963 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
964 X_LAST_UPDATE_DATE DATE;
965 X_LAST_UPDATED_BY NUMBER;
966 X_LAST_UPDATE_LOGIN NUMBER;
967 begin
968 X_LAST_UPDATE_DATE := SYSDATE;
969 if(X_MODE = 'I') then
970 X_LAST_UPDATED_BY := 1;
971 X_LAST_UPDATE_LOGIN := 0;
972 elsif (X_MODE IN ('R', 'S')) then
973 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
974 if X_LAST_UPDATED_BY is NULL then
975 X_LAST_UPDATED_BY := -1;
976 end if;
977 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
978 if X_LAST_UPDATE_LOGIN is NULL then
979 X_LAST_UPDATE_LOGIN := -1;
980 end if;
981 else
982 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
983 IGS_GE_MSG_STACK.ADD;
984 app_exception.raise_exception;
985 end if;
986
987 Before_DML (
988 p_action => 'INSERT',
989 x_rowid => X_ROWID,
990 x_ca_person_id => X_CA_PERSON_ID,
991 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
992 x_person_id => X_PERSON_ID,
993 x_sequence_number => X_SEQUENCE_NUMBER,
994 x_start_dt => X_START_DT,
995 x_end_dt => X_END_DT,
996 x_research_supervisor_type => X_RESEARCH_SUPERVISOR_TYPE,
997 x_supervisor_profession => X_SUPERVISOR_PROFESSION,
998 x_supervision_percentage => X_SUPERVISION_PERCENTAGE,
999 x_funding_percentage => X_FUNDING_PERCENTAGE,
1000 x_org_unit_cd => X_ORG_UNIT_CD,
1001 x_ou_start_dt => X_OU_START_DT,
1002 x_replaced_person_id => X_REPLACED_PERSON_ID,
1003 x_replaced_sequence_number => X_REPLACED_SEQUENCE_NUMBER,
1004 x_comments => X_COMMENTS,
1005 x_created_by => X_LAST_UPDATED_BY ,
1006 x_creation_date => X_LAST_UPDATE_DATE,
1007 x_last_updated_by => X_LAST_UPDATED_BY,
1008 x_last_update_date => X_LAST_UPDATE_DATE,
1009 x_last_update_login => X_LAST_UPDATE_LOGIN
1010 );
1011
1012 IF (x_mode = 'S') THEN
1013 igs_sc_gen_001.set_ctx('R');
1014 END IF;
1015 insert into IGS_RE_SPRVSR (
1016 CA_PERSON_ID,
1017 CA_SEQUENCE_NUMBER,
1018 PERSON_ID,
1019 SEQUENCE_NUMBER,
1020 START_DT,
1021 END_DT,
1022 RESEARCH_SUPERVISOR_TYPE,
1023 SUPERVISOR_PROFESSION,
1024 SUPERVISION_PERCENTAGE,
1025 FUNDING_PERCENTAGE,
1026 ORG_UNIT_CD,
1027 OU_START_DT,
1028 REPLACED_PERSON_ID,
1029 REPLACED_SEQUENCE_NUMBER,
1030 COMMENTS,
1031 CREATION_DATE,
1032 CREATED_BY,
1033 LAST_UPDATE_DATE,
1034 LAST_UPDATED_BY,
1035 LAST_UPDATE_LOGIN
1036 ) values (
1037 NEW_REFERENCES.CA_PERSON_ID,
1038 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
1039 NEW_REFERENCES.PERSON_ID,
1040 NEW_REFERENCES.SEQUENCE_NUMBER,
1041 NEW_REFERENCES.START_DT,
1042 NEW_REFERENCES.END_DT,
1043 NEW_REFERENCES.RESEARCH_SUPERVISOR_TYPE,
1044 NEW_REFERENCES.SUPERVISOR_PROFESSION,
1045 NEW_REFERENCES.SUPERVISION_PERCENTAGE,
1046 NEW_REFERENCES.FUNDING_PERCENTAGE,
1047 NEW_REFERENCES.ORG_UNIT_CD,
1048 NEW_REFERENCES.OU_START_DT,
1049 NEW_REFERENCES.REPLACED_PERSON_ID,
1050 NEW_REFERENCES.REPLACED_SEQUENCE_NUMBER,
1051 NEW_REFERENCES.COMMENTS,
1052 X_LAST_UPDATE_DATE,
1053 X_LAST_UPDATED_BY,
1054 X_LAST_UPDATE_DATE,
1055 X_LAST_UPDATED_BY,
1056 X_LAST_UPDATE_LOGIN
1057 );
1058 IF (x_mode = 'S') THEN
1059 igs_sc_gen_001.unset_ctx('R');
1060 END IF;
1061
1062
1063 open c;
1064 fetch c into X_ROWID;
1065 if (c%notfound) then
1066 close c;
1067 raise no_data_found;
1068 end if;
1069 close c;
1070
1071 After_DML (
1072 p_action => 'INSERT',
1073 x_rowid => X_ROWID
1074 );
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1079 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1080 fnd_message.set_token ('ERR_CD', SQLCODE);
1081 igs_ge_msg_stack.add;
1082 igs_sc_gen_001.unset_ctx('R');
1083 app_exception.raise_exception;
1084 ELSE
1085 igs_sc_gen_001.unset_ctx('R');
1086 RAISE;
1087 END IF;
1088
1089 end INSERT_ROW;
1090
1091 procedure LOCK_ROW (
1092 X_ROWID in VARCHAR2,
1093 X_CA_PERSON_ID in NUMBER,
1094 X_CA_SEQUENCE_NUMBER in NUMBER,
1095 X_PERSON_ID in NUMBER,
1096 X_SEQUENCE_NUMBER in NUMBER,
1097 X_START_DT in DATE,
1098 X_END_DT in DATE,
1099 X_RESEARCH_SUPERVISOR_TYPE in VARCHAR2,
1100 X_SUPERVISOR_PROFESSION in VARCHAR2,
1101 X_SUPERVISION_PERCENTAGE in NUMBER,
1102 X_FUNDING_PERCENTAGE in NUMBER,
1103 X_ORG_UNIT_CD in VARCHAR2,
1104 X_OU_START_DT in DATE,
1105 X_REPLACED_PERSON_ID in NUMBER,
1106 X_REPLACED_SEQUENCE_NUMBER in NUMBER,
1107 X_COMMENTS in VARCHAR2
1108 ) as
1109 cursor c1 is select
1110 START_DT,
1111 END_DT,
1112 RESEARCH_SUPERVISOR_TYPE,
1113 SUPERVISOR_PROFESSION,
1114 SUPERVISION_PERCENTAGE,
1115 FUNDING_PERCENTAGE,
1116 ORG_UNIT_CD,
1117 OU_START_DT,
1118 REPLACED_PERSON_ID,
1119 REPLACED_SEQUENCE_NUMBER,
1120 COMMENTS
1121 from IGS_RE_SPRVSR
1122 where ROWID = X_ROWID
1123 for update nowait;
1124 tlinfo c1%rowtype;
1125
1126 begin
1127 open c1;
1128 fetch c1 into tlinfo;
1129 if (c1%notfound) then
1130 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1131 app_exception.raise_exception;
1132 close c1;
1133 return;
1134 end if;
1135 close c1;
1136
1137 if ( (tlinfo.START_DT = X_START_DT)
1138 AND ((tlinfo.END_DT = X_END_DT)
1139 OR ((tlinfo.END_DT is null)
1140 AND (X_END_DT is null)))
1141 AND (tlinfo.RESEARCH_SUPERVISOR_TYPE = X_RESEARCH_SUPERVISOR_TYPE)
1142 AND ((tlinfo.SUPERVISOR_PROFESSION = X_SUPERVISOR_PROFESSION)
1143 OR ((tlinfo.SUPERVISOR_PROFESSION is null)
1144 AND (X_SUPERVISOR_PROFESSION is null)))
1145 AND ((tlinfo.SUPERVISION_PERCENTAGE = X_SUPERVISION_PERCENTAGE)
1146 OR ((tlinfo.SUPERVISION_PERCENTAGE is null)
1147 AND (X_SUPERVISION_PERCENTAGE is null)))
1148 AND ((tlinfo.FUNDING_PERCENTAGE = X_FUNDING_PERCENTAGE)
1149 OR ((tlinfo.FUNDING_PERCENTAGE is null)
1150 AND (X_FUNDING_PERCENTAGE is null)))
1151 AND ((tlinfo.ORG_UNIT_CD = X_ORG_UNIT_CD)
1152 OR ((tlinfo.ORG_UNIT_CD is null)
1153 AND (X_ORG_UNIT_CD is null)))
1154 AND ((tlinfo.OU_START_DT = X_OU_START_DT)
1155 OR ((tlinfo.OU_START_DT is null)
1156 AND (X_OU_START_DT is null)))
1157 AND ((tlinfo.REPLACED_PERSON_ID = X_REPLACED_PERSON_ID)
1158 OR ((tlinfo.REPLACED_PERSON_ID is null)
1159 AND (X_REPLACED_PERSON_ID is null)))
1160 AND ((tlinfo.REPLACED_SEQUENCE_NUMBER = X_REPLACED_SEQUENCE_NUMBER)
1161 OR ((tlinfo.REPLACED_SEQUENCE_NUMBER is null)
1162 AND (X_REPLACED_SEQUENCE_NUMBER is null)))
1163 AND ((tlinfo.COMMENTS = X_COMMENTS)
1164 OR ((tlinfo.COMMENTS is null)
1165 AND (X_COMMENTS is null)))
1166 ) then
1167 null;
1168 else
1169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1170 app_exception.raise_exception;
1171 end if;
1172 return;
1173 end LOCK_ROW;
1174
1175 procedure UPDATE_ROW (
1176 X_ROWID in VARCHAR2,
1177 X_CA_PERSON_ID in NUMBER,
1178 X_CA_SEQUENCE_NUMBER in NUMBER,
1179 X_PERSON_ID in NUMBER,
1180 X_SEQUENCE_NUMBER in NUMBER,
1181 X_START_DT in DATE,
1182 X_END_DT in DATE,
1183 X_RESEARCH_SUPERVISOR_TYPE in VARCHAR2,
1184 X_SUPERVISOR_PROFESSION in VARCHAR2,
1185 X_SUPERVISION_PERCENTAGE in NUMBER,
1186 X_FUNDING_PERCENTAGE in NUMBER,
1187 X_ORG_UNIT_CD in VARCHAR2,
1188 X_OU_START_DT in DATE,
1189 X_REPLACED_PERSON_ID in NUMBER,
1190 X_REPLACED_SEQUENCE_NUMBER in NUMBER,
1191 X_COMMENTS in VARCHAR2,
1192 X_MODE in VARCHAR2
1193 ) as
1194 X_LAST_UPDATE_DATE DATE;
1195 X_LAST_UPDATED_BY NUMBER;
1196 X_LAST_UPDATE_LOGIN NUMBER;
1197 begin
1198 X_LAST_UPDATE_DATE := SYSDATE;
1199 if(X_MODE = 'I') then
1200 X_LAST_UPDATED_BY := 1;
1201 X_LAST_UPDATE_LOGIN := 0;
1202 elsif (X_MODE IN ('R', 'S')) then
1203 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1204 if X_LAST_UPDATED_BY is NULL then
1205 X_LAST_UPDATED_BY := -1;
1206 end if;
1207 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1208 if X_LAST_UPDATE_LOGIN is NULL then
1209 X_LAST_UPDATE_LOGIN := -1;
1210 end if;
1211 else
1212 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1213 IGS_GE_MSG_STACK.ADD;
1214 app_exception.raise_exception;
1215 end if;
1216
1217 Before_DML (
1218 p_action => 'UPDATE',
1219 x_rowid => X_ROWID,
1220 x_ca_person_id => X_CA_PERSON_ID,
1221 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
1222 x_person_id => X_PERSON_ID,
1223 x_sequence_number => X_SEQUENCE_NUMBER,
1224 x_start_dt => X_START_DT,
1225 x_end_dt => X_END_DT,
1226 x_research_supervisor_type => X_RESEARCH_SUPERVISOR_TYPE,
1227 x_supervisor_profession => X_SUPERVISOR_PROFESSION,
1228 x_supervision_percentage => X_SUPERVISION_PERCENTAGE,
1229 x_funding_percentage => X_FUNDING_PERCENTAGE,
1230 x_org_unit_cd => X_ORG_UNIT_CD,
1231 x_ou_start_dt => X_OU_START_DT,
1232 x_replaced_person_id => X_REPLACED_PERSON_ID,
1233 x_replaced_sequence_number => X_REPLACED_SEQUENCE_NUMBER,
1234 x_comments => X_COMMENTS,
1235 x_created_by => X_LAST_UPDATED_BY ,
1236 x_creation_date => X_LAST_UPDATE_DATE,
1237 x_last_updated_by => X_LAST_UPDATED_BY,
1238 x_last_update_date => X_LAST_UPDATE_DATE,
1239 x_last_update_login => X_LAST_UPDATE_LOGIN
1240 );
1241
1242 IF (x_mode = 'S') THEN
1243 igs_sc_gen_001.set_ctx('R');
1244 END IF;
1245 update IGS_RE_SPRVSR set
1246 START_DT = NEW_REFERENCES.START_DT,
1247 END_DT = NEW_REFERENCES.END_DT,
1248 RESEARCH_SUPERVISOR_TYPE = NEW_REFERENCES.RESEARCH_SUPERVISOR_TYPE,
1249 SUPERVISOR_PROFESSION = NEW_REFERENCES.SUPERVISOR_PROFESSION,
1250 SUPERVISION_PERCENTAGE = NEW_REFERENCES.SUPERVISION_PERCENTAGE,
1251 FUNDING_PERCENTAGE = NEW_REFERENCES.FUNDING_PERCENTAGE,
1252 ORG_UNIT_CD = NEW_REFERENCES.ORG_UNIT_CD,
1253 OU_START_DT = NEW_REFERENCES.OU_START_DT,
1254 REPLACED_PERSON_ID = NEW_REFERENCES.REPLACED_PERSON_ID,
1255 REPLACED_SEQUENCE_NUMBER = NEW_REFERENCES.REPLACED_SEQUENCE_NUMBER,
1256 COMMENTS = NEW_REFERENCES.COMMENTS,
1257 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1258 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1259 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1260 where ROWID = X_ROWID
1261 ;
1262 if (sql%notfound) then
1263 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1264 igs_ge_msg_stack.add;
1265 igs_sc_gen_001.unset_ctx('R');
1266 app_exception.raise_exception;
1267 end if;
1268 IF (x_mode = 'S') THEN
1269 igs_sc_gen_001.unset_ctx('R');
1270 END IF;
1271
1272
1273 After_DML (
1274 p_action => 'UPDATE',
1275 x_rowid => X_ROWID
1276 );
1277
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 IF (SQLCODE = (-28115)) THEN
1281 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1282 fnd_message.set_token ('ERR_CD', SQLCODE);
1283 igs_ge_msg_stack.add;
1284 igs_sc_gen_001.unset_ctx('R');
1285 app_exception.raise_exception;
1286 ELSE
1287 igs_sc_gen_001.unset_ctx('R');
1288 RAISE;
1289 END IF;
1290
1291 end UPDATE_ROW;
1292
1293 procedure ADD_ROW (
1294 X_ROWID in out NOCOPY VARCHAR2,
1295 X_CA_PERSON_ID in NUMBER,
1296 X_CA_SEQUENCE_NUMBER in NUMBER,
1297 X_PERSON_ID in NUMBER,
1298 X_SEQUENCE_NUMBER in NUMBER,
1299 X_START_DT in DATE,
1300 X_END_DT in DATE,
1301 X_RESEARCH_SUPERVISOR_TYPE in VARCHAR2,
1302 X_SUPERVISOR_PROFESSION in VARCHAR2,
1303 X_SUPERVISION_PERCENTAGE in NUMBER,
1304 X_FUNDING_PERCENTAGE in NUMBER,
1305 X_ORG_UNIT_CD in VARCHAR2,
1306 X_OU_START_DT in DATE,
1307 X_REPLACED_PERSON_ID in NUMBER,
1308 X_REPLACED_SEQUENCE_NUMBER in NUMBER,
1309 X_COMMENTS in VARCHAR2,
1310 X_MODE in VARCHAR2
1311 ) as
1312 cursor c1 is
1313 select rowid from IGS_RE_SPRVSR
1314 where CA_PERSON_ID = X_CA_PERSON_ID
1315 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
1316 and PERSON_ID = X_PERSON_ID
1317 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1318 ;
1319 begin
1320 open c1;
1321 fetch c1 into X_ROWID;
1322 if (c1%notfound) then
1323 close c1;
1324 INSERT_ROW (
1325 X_ROWID,
1326 X_CA_PERSON_ID,
1327 X_CA_SEQUENCE_NUMBER,
1328 X_PERSON_ID,
1329 X_SEQUENCE_NUMBER,
1330 X_START_DT,
1331 X_END_DT,
1332 X_RESEARCH_SUPERVISOR_TYPE,
1333 X_SUPERVISOR_PROFESSION,
1334 X_SUPERVISION_PERCENTAGE,
1335 X_FUNDING_PERCENTAGE,
1336 X_ORG_UNIT_CD,
1337 X_OU_START_DT,
1338 X_REPLACED_PERSON_ID,
1339 X_REPLACED_SEQUENCE_NUMBER,
1340 X_COMMENTS,
1341 X_MODE);
1342 return;
1343 end if;
1344 close c1;
1345 UPDATE_ROW (
1346 X_ROWID,
1347 X_CA_PERSON_ID,
1348 X_CA_SEQUENCE_NUMBER,
1349 X_PERSON_ID,
1350 X_SEQUENCE_NUMBER,
1351 X_START_DT,
1352 X_END_DT,
1353 X_RESEARCH_SUPERVISOR_TYPE,
1354 X_SUPERVISOR_PROFESSION,
1355 X_SUPERVISION_PERCENTAGE,
1356 X_FUNDING_PERCENTAGE,
1357 X_ORG_UNIT_CD,
1358 X_OU_START_DT,
1359 X_REPLACED_PERSON_ID,
1360 X_REPLACED_SEQUENCE_NUMBER,
1361 X_COMMENTS,
1362 X_MODE);
1363 end ADD_ROW;
1364
1365 procedure DELETE_ROW (
1366 X_ROWID in VARCHAR2,
1367 x_mode IN VARCHAR2
1368 ) as
1369 begin
1370
1371 Before_DML (
1372 p_action => 'DELETE',
1373 x_rowid => X_ROWID
1374 );
1375
1376 IF (x_mode = 'S') THEN
1377 igs_sc_gen_001.set_ctx('R');
1378 END IF;
1379 delete from IGS_RE_SPRVSR
1380 where ROWID = X_ROWID;
1381 if (sql%notfound) then
1382 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1383 igs_ge_msg_stack.add;
1384 igs_sc_gen_001.unset_ctx('R');
1385 app_exception.raise_exception;
1386 end if;
1387 IF (x_mode = 'S') THEN
1388 igs_sc_gen_001.unset_ctx('R');
1389 END IF;
1390
1391 end DELETE_ROW;
1392
1393 end IGS_RE_SPRVSR_PKG;
[Home] [Help]