[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_EXM_SUPRVISOR_PKG
Source
1 package body IGS_AS_EXM_SUPRVISOR_PKG as
2 /* $Header: IGSDI42B.pls 115.10 2002/12/23 07:08:50 ddey ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_EXM_SUPRVISOR_ALL%RowType;
6 new_references IGS_AS_EXM_SUPRVISOR_ALL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
13 x_previous_sessions IN NUMBER DEFAULT NULL,
14 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
15 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
16 x_comments IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) as
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_AS_EXM_SUPRVISOR_ALL
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 Igs_Ge_Msg_Stack.Add;
40 Close cur_old_ref_values;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.org_id := x_org_id;
48 new_references.person_id := x_person_id;
49 new_references.exam_supervisor_type := x_exam_supervisor_type;
50 new_references.previous_sessions := x_previous_sessions;
51 new_references.responsible_org_unit_cd := x_responsible_org_unit_cd;
52 new_references.responsible_ou_start_dt := x_responsible_ou_start_dt;
53 new_references.comments := x_comments;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66 PROCEDURE BeforeRowInsertUpdate1(
67 p_inserting IN BOOLEAN DEFAULT FALSE,
68 p_updating IN BOOLEAN DEFAULT FALSE,
69 p_deleting IN BOOLEAN DEFAULT FALSE
70 ) as
71 v_message_name varchar2(30);
72 BEGIN
73 -- Validate that the exam supervisor type is not closed.
74 IF p_inserting OR
75 (p_updating AND
76 (new_references.exam_supervisor_type <> old_references.exam_supervisor_type)) THEN
77 IF IGS_AS_VAL_ESU.assp_val_est_closed(new_references.exam_supervisor_type,
78 v_message_name) = FALSE THEN
79 Fnd_Message.Set_Name('IGS', v_message_name);
80 Igs_Ge_Msg_Stack.Add;
81 App_Exception.Raise_Exception;
82 END IF;
83 END IF;
84 -- Validate that the organisation IGS_PS_UNIT is not INACTIVE.
85 IF p_inserting OR
86 (p_updating AND
87 (NVL(new_references.responsible_org_unit_cd, 'NULL') <>
88 NVL(old_references.responsible_org_unit_cd, 'NULL')) OR
89 (NVL(new_references.responsible_ou_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
90 NVL(old_references.responsible_ou_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01')))) THEN
91 -- As part of the bug# 1956374 changed to the below call from IGS_AS_VAL_ESU.crsp_val_ou_sys_sts
92 IF IGS_PS_VAL_CRV.crsp_val_ou_sys_sts(new_references.responsible_org_unit_cd,
93 new_references.responsible_ou_start_dt,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS', v_message_name);
96 Igs_Ge_Msg_Stack.Add;
97 App_Exception.Raise_Exception;
98 END IF;
99 END IF;
100
101
102 END BeforeRowInsertUpdate1;
103
104 PROCEDURE Check_Parent_Existance as
105 BEGIN
106
107 IF (((old_references.exam_supervisor_type = new_references.exam_supervisor_type)) OR
108 ((new_references.exam_supervisor_type IS NULL))) THEN
109 NULL;
110 ELSE
111 IF NOT(IGS_AS_EXM_SPRVSRTYP_PKG.Get_PK_For_Validation (
112 new_references.exam_supervisor_type
113 ))THEN
114 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
115 Igs_Ge_Msg_Stack.Add;
116 App_Exception.Raise_Exception;
117 END IF;
118
119 END IF;
120
121 IF (((old_references.responsible_org_unit_cd = new_references.responsible_org_unit_cd) AND
122 (old_references.responsible_ou_start_dt = new_references.responsible_ou_start_dt)) OR
123 ((new_references.responsible_org_unit_cd IS NULL) OR
124 (new_references.responsible_ou_start_dt IS NULL))) THEN
125 NULL;
126 ELSE
127 IF NOT(IGS_OR_UNIT_PKG.Get_PK_For_Validation (
128 new_references.responsible_org_unit_cd,
129 new_references.responsible_ou_start_dt
130 ))THEN
131 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
132 Igs_Ge_Msg_Stack.Add;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137 IF (((old_references.person_id = new_references.person_id)) OR
138 ((new_references.person_id IS NULL))) THEN
139 NULL;
140 ELSE
141 IF NOT(IGS_PE_PERSON_PKG.Get_PK_For_Validation (
142 new_references.person_id
143 ))THEN
144 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
145 Igs_Ge_Msg_Stack.Add;
146 App_Exception.Raise_Exception;
147 END IF;
148 END IF;
149
150 END Check_Parent_Existance;
151
152 PROCEDURE Check_Constraints (
153 Column_Name IN VARCHAR2 DEFAULT NULL,
154 Column_Value IN VARCHAR2 DEFAULT NULL
155 ) as
156 BEGIN
157 IF column_name is null then
158 NULL;
159 ELSIF upper(Column_name) = 'EXAM_SUPERVISOR_TYPE' then
160 new_references.exam_supervisor_type:= column_value;
161 ELSIF upper(Column_name) = 'RESPONSIBLE_ORG_UNIT_CD' then
162 new_references.responsible_org_unit_cd:= column_value;
163 ELSIF upper(Column_name) = 'PREVIOUS_SESSIONS' then
164 new_references.previous_sessions:= IGS_GE_NUMBER.TO_NUM(column_value);
165
166 END IF;
167 IF upper(column_name) = 'EXAM_SUPERVISOR_TYPE' OR
168 column_name is null Then
169 IF new_references.exam_supervisor_type <> UPPER(new_references.exam_supervisor_type) Then
170 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
171 Igs_Ge_Msg_Stack.Add;
172 App_Exception.Raise_Exception;
173 END IF;
174 END IF;
175
176 IF upper(column_name) = 'PREVIOUS_SESSIONS' OR
177 column_name is null Then
178 IF new_references.previous_sessions < 0 AND new_references.previous_sessions > 9999 Then
179 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
180 Igs_Ge_Msg_Stack.Add;
181 App_Exception.Raise_Exception;
182 END IF;
183 END IF;
184 END Check_Constraints;
185
186
187 PROCEDURE Check_Child_Existance as
188 BEGIN
189
190 IGS_AS_EXM_INS_SPVSR_PKG.GET_FK_IGS_AS_EXM_SUPRVISOR (
191 old_references.person_id
192 );
193
194 IGS_AS_EXM_LOC_SPVSR_PKG.GET_FK_IGS_AS_EXM_SUPRVISOR (
195 old_references.person_id
196 );
197
198 IGS_AS_EXM_SES_VN_SP_PKG.GET_FK_IGS_AS_EXM_SUPRVISOR (
199 old_references.person_id
200 );
201
202 END Check_Child_Existance;
203
204 FUNCTION Get_PK_For_Validation (
205 x_person_id IN NUMBER
206 ) RETURN BOOLEAN AS
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM IGS_AS_EXM_SUPRVISOR_ALL
210 WHERE person_id = x_person_id
211 FOR UPDATE NOWAIT;
212
213 lv_rowid cur_rowid%RowType;
214
215 BEGIN
216
217 Open cur_rowid;
218 Fetch cur_rowid INTO lv_rowid;
219 IF (cur_rowid%FOUND) THEN
220 Close cur_rowid;
221 Return (TRUE);
222 ELSE
223 Close cur_rowid;
224 Return (FALSE);
225 END IF;
226
227 END Get_PK_For_Validation;
228
229 PROCEDURE GET_FK_IGS_AS_EXM_SPRVSRTYP (
230 x_exam_supervisor_type IN VARCHAR2
231 ) as
232
233 CURSOR cur_rowid IS
234 SELECT rowid
235 FROM IGS_AS_EXM_SUPRVISOR_ALL
236 WHERE exam_supervisor_type = x_exam_supervisor_type ;
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 Open cur_rowid;
243 Fetch cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ESU_EST_FK');
246 Igs_Ge_Msg_Stack.Add;
247 Close cur_rowid;
248 App_Exception.Raise_Exception;
249 Return;
250 END IF;
251 Close cur_rowid;
252
253 END GET_FK_IGS_AS_EXM_SPRVSRTYP;
254
255 PROCEDURE GET_FK_IGS_OR_UNIT (
256 x_org_unit_cd IN VARCHAR2,
257 x_start_dt IN VARCHAR2
258 ) as
259
260 CURSOR cur_rowid IS
261 SELECT rowid
262 FROM IGS_AS_EXM_SUPRVISOR_ALL
263 WHERE responsible_org_unit_cd = x_org_unit_cd
264 AND responsible_ou_start_dt = x_start_dt ;
265
266 lv_rowid cur_rowid%RowType;
267
268 BEGIN
269
270 Open cur_rowid;
271 Fetch cur_rowid INTO lv_rowid;
272 IF (cur_rowid%FOUND) THEN
273 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ESU_OU_FK');
274 Igs_Ge_Msg_Stack.Add;
275 Close cur_rowid;
276 App_Exception.Raise_Exception;
277 Return;
278 END IF;
279 Close cur_rowid;
280
281 END GET_FK_IGS_OR_UNIT;
282
283 PROCEDURE GET_FK_IGS_PE_PERSON (
284 x_person_id IN NUMBER
285 ) as
286
287 CURSOR cur_rowid IS
288 SELECT rowid
289 FROM IGS_AS_EXM_SUPRVISOR_ALL
290 WHERE person_id = x_person_id ;
291
292 lv_rowid cur_rowid%RowType;
293
294 BEGIN
295
296 Open cur_rowid;
297 Fetch cur_rowid INTO lv_rowid;
298 IF (cur_rowid%FOUND) THEN
299 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ESU_PE_FK');
300 Igs_Ge_Msg_Stack.Add;
301 Close cur_rowid;
302 App_Exception.Raise_Exception;
303 Return;
304 END IF;
305 Close cur_rowid;
306
307 END GET_FK_IGS_PE_PERSON;
308
309 PROCEDURE Before_DML (
310 p_action IN VARCHAR2,
311 x_rowid IN VARCHAR2 DEFAULT NULL,
312 x_org_id IN NUMBER DEFAULT NULL,
313 x_person_id IN NUMBER DEFAULT NULL,
314 x_exam_supervisor_type IN VARCHAR2 DEFAULT NULL,
315 x_previous_sessions IN NUMBER DEFAULT NULL,
316 x_responsible_org_unit_cd IN VARCHAR2 DEFAULT NULL,
317 x_responsible_ou_start_dt IN DATE DEFAULT NULL,
318 x_comments IN VARCHAR2 DEFAULT NULL,
319 x_creation_date IN DATE DEFAULT NULL,
320 x_created_by IN NUMBER DEFAULT NULL,
321 x_last_update_date IN DATE DEFAULT NULL,
322 x_last_updated_by IN NUMBER DEFAULT NULL,
323 x_last_update_login IN NUMBER DEFAULT NULL
324 ) as
325 BEGIN
326
327 Set_Column_Values (
328 p_action,
329 x_rowid,
330 x_org_id,
331 x_person_id,
332 x_exam_supervisor_type,
333 x_previous_sessions,
334 x_responsible_org_unit_cd,
335 x_responsible_ou_start_dt,
336 x_comments,
337 x_creation_date,
338 x_created_by,
339 x_last_update_date,
340 x_last_updated_by,
341 x_last_update_login
342 );
343
344 IF (p_action = 'INSERT') THEN
345 -- Call all the procedures related to Before Insert.
346 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
347 IF Get_PK_For_Validation (
348 new_references.person_id
349 ) THEN
350 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
351 Igs_Ge_Msg_Stack.Add;
352 App_Exception.Raise_Exception;
353 END IF;
354
355 Check_Constraints;
356 Check_Parent_Existance;
357 ELSIF (p_action = 'UPDATE') THEN
358 -- Call all the procedures related to Before Update.
359 BeforeRowInsertUpdate1 ( p_updating => TRUE );
360 Check_Constraints;
361 Check_Parent_Existance;
362 ELSIF (p_action = 'DELETE') THEN
363 -- Call all the procedures related to Before Delete.
364 Null;
365 Check_Child_Existance;
366 ELSIF (p_action = 'VALIDATE_INSERT') THEN
367 IF Get_PK_For_Validation (
368 new_references.person_id
369 ) THEN
370 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
371 Igs_Ge_Msg_Stack.Add;
372 App_Exception.Raise_Exception;
373 END IF;
374 Check_Constraints;
375 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
376 Check_Constraints;
377 ELSIF (p_action = 'VALIDATE_DELETE') THEN
378 Check_Child_Existance;
379
380 END IF;
381
382 END Before_DML;
383
384
385
386 procedure INSERT_ROW (
387 X_ROWID in out NOCOPY VARCHAR2,
388 X_ORG_ID in NUMBER,
389 X_PERSON_ID in NUMBER,
390 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
391 X_PREVIOUS_SESSIONS in NUMBER,
392 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
393 X_RESPONSIBLE_OU_START_DT in DATE,
394 X_COMMENTS in VARCHAR2,
395 X_MODE in VARCHAR2 default 'R'
396 ) as
397 cursor C is select ROWID from IGS_AS_EXM_SUPRVISOR_ALL
398 where PERSON_ID = X_PERSON_ID;
399 X_LAST_UPDATE_DATE DATE;
400 X_LAST_UPDATED_BY NUMBER;
401 X_LAST_UPDATE_LOGIN NUMBER;
402 begin
403 X_LAST_UPDATE_DATE := SYSDATE;
404 if(X_MODE = 'I') then
405 X_LAST_UPDATED_BY := 1;
406 X_LAST_UPDATE_LOGIN := 0;
407 elsif (X_MODE = 'R') then
408 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
409 if X_LAST_UPDATED_BY is NULL then
410 X_LAST_UPDATED_BY := -1;
411 end if;
412 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
413 if X_LAST_UPDATE_LOGIN is NULL then
414 X_LAST_UPDATE_LOGIN := -1;
415 end if;
416 else
417 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
418 Igs_Ge_Msg_Stack.Add;
419 app_exception.raise_exception;
420 end if;
421 Before_DML(
422 p_action=>'INSERT',
423 x_rowid=>X_ROWID,
424 x_org_id => igs_ge_gen_003.get_org_id,
425 x_comments=>X_COMMENTS,
426 x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
427 x_person_id=>X_PERSON_ID,
428 x_previous_sessions=>X_PREVIOUS_SESSIONS,
429 x_responsible_org_unit_cd=>X_RESPONSIBLE_ORG_UNIT_CD,
430 x_responsible_ou_start_dt=>X_RESPONSIBLE_OU_START_DT,
431 x_creation_date=>X_LAST_UPDATE_DATE,
432 x_created_by=>X_LAST_UPDATED_BY,
433 x_last_update_date=>X_LAST_UPDATE_DATE,
434 x_last_updated_by=>X_LAST_UPDATED_BY,
435 x_last_update_login=>X_LAST_UPDATE_LOGIN
436 );
437 insert into IGS_AS_EXM_SUPRVISOR_ALL (
438 ORG_ID,
439 PERSON_ID,
440 EXAM_SUPERVISOR_TYPE,
441 PREVIOUS_SESSIONS,
442 RESPONSIBLE_ORG_UNIT_CD,
443 RESPONSIBLE_OU_START_DT,
444 COMMENTS,
445 CREATION_DATE,
446 CREATED_BY,
447 LAST_UPDATE_DATE,
448 LAST_UPDATED_BY,
449 LAST_UPDATE_LOGIN
450 ) values (
451 NEW_REFERENCES.ORG_ID,
452 NEW_REFERENCES.PERSON_ID,
453 NEW_REFERENCES.EXAM_SUPERVISOR_TYPE,
454 NEW_REFERENCES.PREVIOUS_SESSIONS,
455 NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
456 NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
457 NEW_REFERENCES.COMMENTS,
458 X_LAST_UPDATE_DATE,
459 X_LAST_UPDATED_BY,
460 X_LAST_UPDATE_DATE,
461 X_LAST_UPDATED_BY,
462 X_LAST_UPDATE_LOGIN
463 );
464
465 open c;
466 fetch c into X_ROWID;
467 if (c%notfound) then
468 close c;
469 raise no_data_found;
470 end if;
471 close c;
472 end INSERT_ROW;
473
474 procedure LOCK_ROW (
475 X_ROWID in VARCHAR2,
476 X_PERSON_ID in NUMBER,
477 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
478 X_PREVIOUS_SESSIONS in NUMBER,
479 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
480 X_RESPONSIBLE_OU_START_DT in DATE,
481 X_COMMENTS in VARCHAR2
482 ) as
483 cursor c1 is select
484 EXAM_SUPERVISOR_TYPE,
485 PREVIOUS_SESSIONS,
486 RESPONSIBLE_ORG_UNIT_CD,
487 RESPONSIBLE_OU_START_DT,
488 COMMENTS
489 from IGS_AS_EXM_SUPRVISOR_ALL
490 where ROWID = X_ROWID for update nowait;
491 tlinfo c1%rowtype;
492
493 begin
494 open c1;
495 fetch c1 into tlinfo;
496 if (c1%notfound) then
497 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
498 Igs_Ge_Msg_Stack.Add;
499 close c1;
500 app_exception.raise_exception;
501 return;
502 end if;
503 close c1;
504
505 if ( (tlinfo.EXAM_SUPERVISOR_TYPE = X_EXAM_SUPERVISOR_TYPE)
506 AND ((tlinfo.PREVIOUS_SESSIONS = X_PREVIOUS_SESSIONS)
507 OR ((tlinfo.PREVIOUS_SESSIONS is null)
508 AND (X_PREVIOUS_SESSIONS is null)))
509 AND ((tlinfo.RESPONSIBLE_ORG_UNIT_CD = X_RESPONSIBLE_ORG_UNIT_CD)
510 OR ((tlinfo.RESPONSIBLE_ORG_UNIT_CD is null)
511 AND (X_RESPONSIBLE_ORG_UNIT_CD is null)))
512 AND ((tlinfo.RESPONSIBLE_OU_START_DT = X_RESPONSIBLE_OU_START_DT)
513 OR ((tlinfo.RESPONSIBLE_OU_START_DT is null)
514 AND (X_RESPONSIBLE_OU_START_DT is null)))
515 AND ((tlinfo.COMMENTS = X_COMMENTS)
516 OR ((tlinfo.COMMENTS is null)
517 AND (X_COMMENTS is null)))
518 ) then
519 null;
520 else
521 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
522 Igs_Ge_Msg_Stack.Add;
523 app_exception.raise_exception;
524 end if;
525 return;
526 end LOCK_ROW;
527
528 procedure UPDATE_ROW (
529 X_ROWID in VARCHAR2,
530 X_PERSON_ID in NUMBER,
531 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
532 X_PREVIOUS_SESSIONS in NUMBER,
533 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
534 X_RESPONSIBLE_OU_START_DT in DATE,
535 X_COMMENTS in VARCHAR2,
536 X_MODE in VARCHAR2 default 'R'
537 ) as
538 X_LAST_UPDATE_DATE DATE;
539 X_LAST_UPDATED_BY NUMBER;
540 X_LAST_UPDATE_LOGIN NUMBER;
541 begin
542 X_LAST_UPDATE_DATE := SYSDATE;
543 if(X_MODE = 'I') then
544 X_LAST_UPDATED_BY := 1;
545 X_LAST_UPDATE_LOGIN := 0;
546 elsif (X_MODE = 'R') then
547 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
548 if X_LAST_UPDATED_BY is NULL then
549 X_LAST_UPDATED_BY := -1;
550 end if;
551 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
552 if X_LAST_UPDATE_LOGIN is NULL then
553 X_LAST_UPDATE_LOGIN := -1;
554 end if;
555 else
556 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
557 Igs_Ge_Msg_Stack.Add;
558 app_exception.raise_exception;
559 end if;
560 Before_DML(
561 p_action=>'UPDATE',
562 x_rowid=>X_ROWID,
563 x_comments=>X_COMMENTS,
564 x_exam_supervisor_type=>X_EXAM_SUPERVISOR_TYPE,
565 x_person_id=>X_PERSON_ID,
566 x_previous_sessions=>X_PREVIOUS_SESSIONS,
567 x_responsible_org_unit_cd=>X_RESPONSIBLE_ORG_UNIT_CD,
568 x_responsible_ou_start_dt=>X_RESPONSIBLE_OU_START_DT,
569 x_creation_date=>X_LAST_UPDATE_DATE,
570 x_created_by=>X_LAST_UPDATED_BY,
571 x_last_update_date=>X_LAST_UPDATE_DATE,
572 x_last_updated_by=>X_LAST_UPDATED_BY,
573 x_last_update_login=>X_LAST_UPDATE_LOGIN
574 );
575 update IGS_AS_EXM_SUPRVISOR_ALL set
576 EXAM_SUPERVISOR_TYPE = NEW_REFERENCES.EXAM_SUPERVISOR_TYPE,
577 PREVIOUS_SESSIONS = NEW_REFERENCES.PREVIOUS_SESSIONS,
578 RESPONSIBLE_ORG_UNIT_CD = NEW_REFERENCES.RESPONSIBLE_ORG_UNIT_CD,
579 RESPONSIBLE_OU_START_DT = NEW_REFERENCES.RESPONSIBLE_OU_START_DT,
580 COMMENTS = NEW_REFERENCES.COMMENTS,
581 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
582 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
583 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
584 where ROWID = X_ROWID;
585 if (sql%notfound) then
586 raise no_data_found;
587 end if;
588 end UPDATE_ROW;
589
590 procedure ADD_ROW (
591 X_ROWID in out NOCOPY VARCHAR2,
592 X_ORG_ID in NUMBER,
593 X_PERSON_ID in NUMBER,
594 X_EXAM_SUPERVISOR_TYPE in VARCHAR2,
595 X_PREVIOUS_SESSIONS in NUMBER,
596 X_RESPONSIBLE_ORG_UNIT_CD in VARCHAR2,
597 X_RESPONSIBLE_OU_START_DT in DATE,
598 X_COMMENTS in VARCHAR2,
599 X_MODE in VARCHAR2 default 'R'
600 ) as
601 cursor c1 is select rowid from IGS_AS_EXM_SUPRVISOR_ALL
602 where PERSON_ID = X_PERSON_ID
603 ;
604 begin
605 open c1;
606 fetch c1 into X_ROWID;
607 if (c1%notfound) then
608 close c1;
609 INSERT_ROW (
610 X_ROWID,
611 X_ORG_ID,
612 X_PERSON_ID,
613 X_EXAM_SUPERVISOR_TYPE,
614 X_PREVIOUS_SESSIONS,
615 X_RESPONSIBLE_ORG_UNIT_CD,
616 X_RESPONSIBLE_OU_START_DT,
617 X_COMMENTS,
618 X_MODE);
619 return;
620 end if;
621 close c1;
622 UPDATE_ROW (
623 X_ROWID,
624 X_PERSON_ID,
625 X_EXAM_SUPERVISOR_TYPE,
626 X_PREVIOUS_SESSIONS,
627 X_RESPONSIBLE_ORG_UNIT_CD,
628 X_RESPONSIBLE_OU_START_DT,
629 X_COMMENTS,
630 X_MODE);
631 end ADD_ROW;
632
633 procedure DELETE_ROW (
634 X_ROWID in VARCHAR2) as
635 begin
636 Before_DML(
637 p_action => 'DELETE',
638 x_rowid => X_ROWID
639 );
640 delete from IGS_AS_EXM_SUPRVISOR_ALL
641 where ROWID = X_ROWID;
642 if (sql%notfound) then
643 raise no_data_found;
644 end if;
645
646 end DELETE_ROW;
647
648 end IGS_AS_EXM_SUPRVISOR_PKG;