[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_EXAM_SESSION_PKG
Source
1 package body IGS_AS_EXAM_SESSION_PKG AS
2 /* $Header: IGSDI12B.pls 115.7 2002/11/28 23:13:12 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_EXAM_SESSION_ALL%RowType;
5 new_references IGS_AS_EXAM_SESSION_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_org_id IN NUMBER DEFAULT NULL,
10 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_dt_alias IN VARCHAR2 DEFAULT NULL,
13 x_dai_sequence_number IN NUMBER DEFAULT NULL,
14 x_ci_start_dt IN DATE DEFAULT NULL,
15 x_ci_end_dt IN DATE DEFAULT NULL,
16 x_start_time IN DATE DEFAULT NULL,
17 x_end_time IN DATE DEFAULT NULL,
18 x_ese_id IN NUMBER DEFAULT NULL,
19 x_exam_session_number IN NUMBER DEFAULT NULL,
20 x_comments IN VARCHAR2 DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) AS
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_AS_EXAM_SESSION_ALL
30 WHERE rowid = x_rowid;
31 BEGIN
32 l_rowid := x_rowid;
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 IGS_GE_MSG_STACK.ADD;
41 Close cur_old_ref_values;
42 APP_EXCEPTION.RAISE_EXCEPTION;
43
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47 -- Populate New Values.
48 new_references.org_id := x_org_id;
49 new_references.exam_cal_type := x_exam_cal_type;
50 new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
51 new_references.dt_alias := x_dt_alias;
52 new_references.dai_sequence_number := x_dai_sequence_number;
53 new_references.ci_start_dt := x_ci_start_dt;
54 new_references.ci_end_dt := x_ci_end_dt;
55 new_references.start_time := x_start_time;
56 new_references.end_time := x_end_time;
57 new_references.ese_id := x_ese_id;
58 new_references.exam_session_number := x_exam_session_number;
59 new_references.comments := x_comments;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70 END Set_Column_Values;
71 PROCEDURE BeforeRowInsert1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name VARCHAR2(30);
77 BEGIN
78 -- If not set, call routine to set the ci_start_dt and ci_end_dt values.
79 IF new_references.ci_start_dt IS NULL OR new_references.ci_end_dt IS NULL THEN
80 IGS_CA_GEN_001.CALP_GET_CI_DATES(new_references.exam_cal_type,new_references.exam_ci_sequence_number,
81 new_references.ci_start_dt,new_references.ci_end_dt);
82 END IF;
83 -- Check that both the start/end time have the standard 1/1/1900 date component
84 -- to ensure primary key integrity.
85 IF new_references.start_time <> IGS_GE_GEN_003.GENP_SET_TIME(new_references.start_time) THEN
86 new_references.start_time := IGS_GE_GEN_003.GENP_SET_TIME(new_references.start_time);
87 END IF;
88 IF new_references.end_time <> IGS_GE_GEN_003.GENP_SET_TIME(new_references.end_time) THEN
89 new_references.end_time := IGS_GE_GEN_003.GENP_SET_TIME(new_references.end_time);
90 END IF;
91 -- Validate the calendar instance of the record.
92 IF IGS_AS_VAL_ESE.assp_val_ese_ci( new_references.exam_cal_type,
93 new_references.exam_ci_sequence_number,
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 -- Validate the start/end time.
100 IF IGS_AS_VAL_ESE.genp_val_strt_end_tm( new_references.start_time,
101 new_references.end_time,
102 v_message_name) = FALSE THEN
103 FND_MESSAGE.set_name('IGS',v_message_name);
104 IGS_GE_MSG_STACK.ADD;
105 APP_EXCEPTION.RAISE_EXCEPTION;
106 END IF;
107 END BeforeRowInsert1;
108
109 PROCEDURE Check_Parent_Existance AS
110 BEGIN
111 IF (((old_references.dt_alias = new_references.dt_alias) AND
112 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
113 (old_references.exam_cal_type = new_references.exam_cal_type) AND
114 (old_references.exam_ci_sequence_number = new_references.exam_ci_sequence_number)) OR
115 ((new_references.dt_alias IS NULL) OR
116 (new_references.dai_sequence_number IS NULL) OR
117 (new_references.exam_cal_type IS NULL) OR
118 (new_references.exam_ci_sequence_number IS NULL))) THEN
119 NULL;
120 ELSIF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
121 new_references.dt_alias,
122 new_references.dai_sequence_number,
123 new_references.exam_cal_type,
124 new_references.exam_ci_sequence_number ) THEN
125 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
126 IGS_GE_MSG_STACK.ADD;
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END IF;
129
130
131 IF (((old_references.exam_cal_type = new_references.exam_cal_type) OR
132 (old_references.exam_ci_sequence_number = new_references.exam_ci_sequence_number)) OR
133 ((new_references.exam_cal_type IS NULL) OR
134 (new_references.exam_ci_sequence_number IS NULL))) THEN
135 NULL;
136 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
137 new_references.exam_cal_type,
138 new_references.exam_ci_sequence_number )THEN
139 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
140 IGS_GE_MSG_STACK.ADD;
141 APP_EXCEPTION.RAISE_EXCEPTION;
142
143
144 END IF;
145 END Check_Parent_Existance;
146 PROCEDURE Check_Child_Existance AS
147 BEGIN
148 IGS_AS_EXAM_INSTANCE_PKG.GET_FK_IGS_AS_EXAM_SESSION (
149 old_references.exam_cal_type,
150 old_references.exam_ci_sequence_number,
151 old_references.dt_alias,
152 old_references.dai_sequence_number,
153 old_references.start_time,
154 old_references.end_time
155 );
156 IGS_AS_EXMVNU_SESAVL_PKG.GET_FK_IGS_AS_EXAM_SESSION (
157 old_references.exam_cal_type,
158 old_references.exam_ci_sequence_number,
159 old_references.dt_alias,
160 old_references.dai_sequence_number,
161 old_references.start_time,
162 old_references.end_time
163 );
164 IGS_AS_STD_EXM_INSTN_PKG.GET_UFK_IGS_AS_EXAM_SESSION(
165 old_references.ese_id
166 );
167
168 IGS_AS_EXM_INS_SPVSR_PKG.GET_UFK_IGS_AS_EXAM_SESSION(
169 old_references.ese_id
170 );
171 IGS_AS_EXM_SES_VN_SP_PKG.GET_UFK_IGS_AS_EXAM_SESSION(
172 old_references.ese_id
173 );
174 IGS_AS_EXMVNU_SESAVL_PKG.GET_UFK_IGS_AS_EXAM_SESSION (
175 old_references.ese_id
176 );
177
178 END Check_Child_Existance;
179
180 PROCEDURE Check_UK_Child_Existance AS
181 BEGIN
182
183 IF ((old_references.ese_id = new_references.ese_id)
184 OR (old_references.ese_id IS NULL)) THEN
185 NULL;
186 ELSE
187 IGS_AS_EXAM_INSTANCE_PKG.GET_UFK_IGS_AS_EXAM_SESSION(old_references.ese_id);
188 IGS_AS_EXM_INS_SPVSR_PKG.GET_UFK_IGS_AS_EXAM_SESSION(old_references.ese_id);
189 IGS_AS_EXM_SES_VN_SP_PKG.GET_UFK_IGS_AS_EXAM_SESSION(old_references.ese_id);
190 IGS_AS_EXMVNU_SESAVL_PKG.GET_UFK_IGS_AS_EXAM_SESSION(old_references.ese_id);
191 END IF;
192 END Check_UK_Child_Existance;
193
194
195 function Get_PK_For_Validation (
196 x_exam_cal_type IN VARCHAR2,
197 x_exam_ci_sequence_number IN NUMBER,
198 x_dt_alias IN VARCHAR2,
199 x_dai_sequence_number IN NUMBER,
200 x_start_time IN DATE,
201 x_end_time IN DATE
202 )return boolean AS
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM IGS_AS_EXAM_SESSION_ALL
206 WHERE exam_cal_type = x_exam_cal_type
207 AND exam_ci_sequence_number = x_exam_ci_sequence_number
208 AND dt_alias = x_dt_alias
209 AND dai_sequence_number = x_dai_sequence_number
210 AND to_char(start_time,'HH24:MI') = to_char(x_start_time,'HH24:MI')
211 AND to_char(end_time,'HH24:MI') = to_char(x_end_time,'HH24:MI')
212 FOR UPDATE NOWAIT;
213 lv_rowid cur_rowid%RowType;
214 BEGIN
215 Open cur_rowid;
216 Fetch cur_rowid INTO lv_rowid;
217 IF (cur_rowid%FOUND) THEN
218 Close cur_rowid;
219 Return (TRUE);
220 ELSE
221 Close cur_rowid;
222 Return (FALSE);
223 END IF;
224
225 END Get_PK_For_Validation;
226 FUNCTION Get_UK_For_Validation (
227 x_ese_id IN NUMBER
228 ) RETURN BOOLEAN AS
229 CURSOR cur_rowid IS
230 SELECT rowid
231 FROM IGS_AS_EXAM_SESSION_ALL
232 WHERE ese_id = x_ese_id
233 AND (l_rowid is null or rowid <> l_rowid)
234 FOR UPDATE NOWAIT;
235 lv_rowid cur_rowid%RowType;
236 BEGIN
237 Open cur_rowid;
238 Fetch cur_rowid INTO lv_rowid;
239 IF (cur_rowid%FOUND) THEN
240 Close cur_rowid;
241 Return (TRUE);
242 ELSE
243 Close cur_rowid;
244 Return (FALSE);
245 END IF;
246 END Get_UK_For_Validation;
247 PROCEDURE GET_FK_IGS_CA_DA_INST (
248 x_dt_alias IN VARCHAR2,
249 x_sequence_number IN NUMBER,
250 x_cal_type IN VARCHAR2,
251 x_ci_sequence_number IN NUMBER
252 ) AS
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM IGS_AS_EXAM_SESSION_ALL
256 WHERE dt_alias = x_dt_alias
257 AND dai_sequence_number = x_sequence_number
258 AND exam_cal_type = x_cal_type
259 AND exam_ci_sequence_number = x_ci_sequence_number ;
260 lv_rowid cur_rowid%RowType;
261 BEGIN
262 Open cur_rowid;
263 Fetch cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ESE_CI_FK');
266 IGS_GE_MSG_STACK.ADD;
267 Close cur_rowid;
268 APP_EXCEPTION.RAISE_EXCEPTION;
269
270 Return;
271 END IF;
275 x_cal_type IN VARCHAR2,
272 Close cur_rowid;
273 END GET_FK_IGS_CA_DA_INST;
274 PROCEDURE GET_FK_IGS_CA_INST (
276 x_sequence_number IN NUMBER
277 ) AS
278 CURSOR cur_rowid IS
279 SELECT rowid
280 FROM IGS_AS_EXAM_SESSION_ALL
281 WHERE exam_cal_type = x_cal_type
282 AND exam_ci_sequence_number = x_sequence_number ;
283 lv_rowid cur_rowid%RowType;
284 BEGIN
285 Open cur_rowid;
286 Fetch cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ESE_DAI_FK');
289 IGS_GE_MSG_STACK.ADD;
290 Close cur_rowid;
291 APP_EXCEPTION.RAISE_EXCEPTION;
292
293 Return;
294 END IF;
295 Close cur_rowid;
296 END GET_FK_IGS_CA_INST;
297 PROCEDURE Before_DML (
298 p_action IN VARCHAR2,
299 x_rowid IN VARCHAR2 DEFAULT NULL,
300 x_org_id IN NUMBER DEFAULT NULL,
301 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
302 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
303 x_dt_alias IN VARCHAR2 DEFAULT NULL,
304 x_dai_sequence_number IN NUMBER DEFAULT NULL,
305 x_ci_start_dt IN DATE DEFAULT NULL,
306 x_ci_end_dt IN DATE DEFAULT NULL,
307 x_start_time IN DATE DEFAULT NULL,
308 x_end_time IN DATE DEFAULT NULL,
309 x_ese_id IN NUMBER DEFAULT NULL,
310 x_exam_session_number IN NUMBER DEFAULT NULL,
311 x_comments IN VARCHAR2 DEFAULT NULL,
312 x_creation_date IN DATE DEFAULT NULL,
313 x_created_by IN NUMBER DEFAULT NULL,
314 x_last_update_date IN DATE DEFAULT NULL,
315 x_last_updated_by IN NUMBER DEFAULT NULL,
316 x_last_update_login IN NUMBER DEFAULT NULL
317 ) AS
318 BEGIN
319 Set_Column_Values (
320 p_action,
321 x_rowid,
322 x_org_id,
323 x_exam_cal_type,
324 x_exam_ci_sequence_number,
325 x_dt_alias,
326 x_dai_sequence_number,
327 x_ci_start_dt,
328 x_ci_end_dt,
329 x_start_time,
330 x_end_time,
331 x_ese_id,
332 x_exam_session_number,
333 x_comments,
334 x_creation_date,
335 x_created_by,
336 x_last_update_date,
337 x_last_updated_by,
338 x_last_update_login
339 );
340 IF (p_action = 'INSERT') THEN
341 -- Call all the procedures related to Before Insert.
342 BeforeRowInsert1 ( p_inserting => TRUE );
343 IF Get_PK_For_Validation (NEW_REFERENCES.exam_cal_type ,
344 NEW_REFERENCES.exam_ci_sequence_number ,
345 NEW_REFERENCES.dt_alias ,
346 NEW_REFERENCES.dai_sequence_number,
347 NEW_REFERENCES.start_time ,
348 NEW_REFERENCES.end_time ) THEN
349 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
350 IGS_GE_MSG_STACK.ADD;
351 APP_EXCEPTION.RAISE_EXCEPTION;
352 END IF;
353 Check_Uniqueness;
354 Check_Constraints;
355 Check_Parent_Existance;
356 ELSIF (p_action = 'UPDATE') THEN
357 -- Call all the procedures related to Before Update.
358
359 Check_Uniqueness;
360 Check_Constraints;
361
362 Check_Parent_Existance;
363 Check_UK_Child_Existance;
364
365 ELSIF (p_action = 'VALIDATE_INSERT') THEN
366 IF Get_PK_For_Validation (NEW_REFERENCES.exam_cal_type ,
367 NEW_REFERENCES.exam_ci_sequence_number ,
368 NEW_REFERENCES.dt_alias ,
369 NEW_REFERENCES.dai_sequence_number,
370 NEW_REFERENCES.start_time ,
371 NEW_REFERENCES.end_time) THEN
372 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
373 IGS_GE_MSG_STACK.ADD;
374 APP_EXCEPTION.RAISE_EXCEPTION;
375 END IF;
376 Check_Uniqueness;
377 Check_Constraints;
378 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
379 Check_Uniqueness;
380 Check_Constraints;
381 Check_UK_Child_Existance;
382 ELSIF (p_action = 'VALIDATE_DELETE') THEN
383 Check_Child_Existance;
384 END IF;
385 END Before_DML;
386
387 procedure INSERT_ROW (
388 X_ROWID in out NOCOPY VARCHAR2,
389 X_ORG_ID in NUMBER,
390 X_EXAM_CAL_TYPE in VARCHAR2,
391 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
392 X_DT_ALIAS in VARCHAR2,
393 X_DAI_SEQUENCE_NUMBER in NUMBER,
394 X_START_TIME in DATE,
395 X_END_TIME in DATE,
396 X_CI_START_DT in DATE,
397 X_CI_END_DT in DATE,
398 X_ESE_ID in NUMBER,
399 X_EXAM_SESSION_NUMBER in NUMBER,
400 X_COMMENTS in VARCHAR2,
401 X_MODE in VARCHAR2 default 'R'
402 ) AS
403 cursor C is select ROWID from IGS_AS_EXAM_SESSION_ALL
404 where EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
405 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
406 and DT_ALIAS = X_DT_ALIAS
407 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
408 and to_char(start_time,'HH24:MI') = to_char(x_start_time,'HH24:MI')
409 and to_char(end_time,'HH24:MI') = to_char(x_end_time,'HH24:MI') ;
413 begin
410 X_LAST_UPDATE_DATE DATE;
411 X_LAST_UPDATED_BY NUMBER;
412 X_LAST_UPDATE_LOGIN NUMBER;
414 X_LAST_UPDATE_DATE := SYSDATE;
415 if(X_MODE = 'I') then
416 X_LAST_UPDATED_BY := 1;
417 X_LAST_UPDATE_LOGIN := 0;
418 elsif (X_MODE = 'R') then
419 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
420 if X_LAST_UPDATED_BY is NULL then
421 X_LAST_UPDATED_BY := -1;
422 end if;
423 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
424 if X_LAST_UPDATE_LOGIN is NULL then
425 X_LAST_UPDATE_LOGIN := -1;
426 end if;
427 else
428 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
429 IGS_GE_MSG_STACK.ADD;
430 APP_EXCEPTION.RAISE_EXCEPTION;
431 end if;
432 Before_DML(
433 p_action=>'INSERT',
434 x_rowid=>X_ROWID,
435 x_org_id => igs_ge_gen_003.get_org_id,
436 x_ci_end_dt=>X_CI_END_DT,
437 x_ci_start_dt=>X_CI_START_DT,
438 x_comments=>X_COMMENTS,
439 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
440 x_dt_alias=>X_DT_ALIAS,
441 x_end_time=>X_END_TIME,
442 x_ese_id=>X_ESE_ID,
443 x_exam_cal_type=>X_EXAM_CAL_TYPE,
444 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
445 x_exam_session_number=>X_EXAM_SESSION_NUMBER,
446 x_start_time=>X_START_TIME,
447 x_creation_date=>X_LAST_UPDATE_DATE,
448 x_created_by=>X_LAST_UPDATED_BY,
449 x_last_update_date=>X_LAST_UPDATE_DATE,
450 x_last_updated_by=>X_LAST_UPDATED_BY,
451 x_last_update_login=>X_LAST_UPDATE_LOGIN
452 );
453 insert into IGS_AS_EXAM_SESSION_ALL (
454 ORG_ID,
455 EXAM_CAL_TYPE,
456 EXAM_CI_SEQUENCE_NUMBER,
457 DT_ALIAS,
458 DAI_SEQUENCE_NUMBER,
459 CI_START_DT,
460 CI_END_DT,
461 START_TIME,
462 END_TIME,
463 ESE_ID,
464 EXAM_SESSION_NUMBER,
465 COMMENTS,
466 CREATION_DATE,
467 CREATED_BY,
468 LAST_UPDATE_DATE,
469 LAST_UPDATED_BY,
470 LAST_UPDATE_LOGIN
471 ) values (
472 NEW_REFERENCES.ORG_ID,
473 NEW_REFERENCES.EXAM_CAL_TYPE,
474 NEW_REFERENCES.EXAM_CI_SEQUENCE_NUMBER,
475 NEW_REFERENCES.DT_ALIAS,
476 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
477 NEW_REFERENCES.CI_START_DT,
478 NEW_REFERENCES.CI_END_DT,
479 NEW_REFERENCES.START_TIME,
480 NEW_REFERENCES.END_TIME,
481 NEW_REFERENCES.ESE_ID,
482 NEW_REFERENCES.EXAM_SESSION_NUMBER,
483 NEW_REFERENCES.COMMENTS,
484 X_LAST_UPDATE_DATE,
485 X_LAST_UPDATED_BY,
486 X_LAST_UPDATE_DATE,
487 X_LAST_UPDATED_BY,
488 X_LAST_UPDATE_LOGIN
489 );
490 open c;
491 fetch c into X_ROWID;
492 if (c%notfound) then
493 close c;
494 raise no_data_found;
495 end if;
496 close c;
497 l_rowid:=NULL;
498 end INSERT_ROW;
502 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
499 procedure LOCK_ROW (
500 X_ROWID in VARCHAR2,
501 X_EXAM_CAL_TYPE in VARCHAR2,
503 X_DT_ALIAS in VARCHAR2,
504 X_DAI_SEQUENCE_NUMBER in NUMBER,
505 X_START_TIME in DATE,
506 X_END_TIME in DATE,
507 X_CI_START_DT in DATE,
508 X_CI_END_DT in DATE,
509 X_ESE_ID in NUMBER,
510 X_EXAM_SESSION_NUMBER in NUMBER,
511 X_COMMENTS in VARCHAR2
512 ) AS
513 cursor c1 is select
514 CI_START_DT,
515 CI_END_DT,
516 ESE_ID,
517 EXAM_SESSION_NUMBER,
518 COMMENTS
519 from IGS_AS_EXAM_SESSION_ALL
520 where ROWID = X_ROWID for update nowait;
521 tlinfo c1%rowtype;
522 begin
523 open c1;
524 fetch c1 into tlinfo;
525 if (c1%notfound) then
526 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
527 IGS_GE_MSG_STACK.ADD;
528 APP_EXCEPTION.RAISE_EXCEPTION;
529 close c1;
530 return;
531 end if;
532 close c1;
533 if ( (tlinfo.CI_START_DT = X_CI_START_DT)
534 AND (tlinfo.CI_END_DT = X_CI_END_DT)
535 AND (tlinfo.ESE_ID = X_ESE_ID)
536 AND (tlinfo.EXAM_SESSION_NUMBER = X_EXAM_SESSION_NUMBER)
537 AND ((tlinfo.COMMENTS = X_COMMENTS)
538 OR ((tlinfo.COMMENTS is null)
539 AND (X_COMMENTS is null)))
540 ) then
541 null;
542 else
543 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
544 IGS_GE_MSG_STACK.ADD;
545 APP_EXCEPTION.RAISE_EXCEPTION;
546 end if;
547 return;
548 end LOCK_ROW;
549 procedure UPDATE_ROW (
550 X_ROWID in VARCHAR2,
551 X_EXAM_CAL_TYPE in VARCHAR2,
552 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
553 X_DT_ALIAS in VARCHAR2,
554 X_DAI_SEQUENCE_NUMBER in NUMBER,
555 X_START_TIME in DATE,
556 X_END_TIME in DATE,
557 X_CI_START_DT in DATE,
558 X_CI_END_DT in DATE,
559 X_ESE_ID in NUMBER,
560 X_EXAM_SESSION_NUMBER in NUMBER,
561 X_COMMENTS in VARCHAR2,
562 X_MODE in VARCHAR2 default 'R'
563 ) AS
564 X_LAST_UPDATE_DATE DATE;
565 X_LAST_UPDATED_BY NUMBER;
566 X_LAST_UPDATE_LOGIN NUMBER;
567 begin
568 X_LAST_UPDATE_DATE := SYSDATE;
569 if(X_MODE = 'I') then
570 X_LAST_UPDATED_BY := 1;
571 X_LAST_UPDATE_LOGIN := 0;
572 elsif (X_MODE = 'R') then
573 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
574 if X_LAST_UPDATED_BY is NULL then
575 X_LAST_UPDATED_BY := -1;
576 end if;
577 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
578 if X_LAST_UPDATE_LOGIN is NULL then
579 X_LAST_UPDATE_LOGIN := -1;
580 end if;
581 else
582 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
583 IGS_GE_MSG_STACK.ADD;
587 p_action=>'UPDATE',
584 APP_EXCEPTION.RAISE_EXCEPTION;
585 end if;
586 Before_DML(
588 x_rowid=>X_ROWID,
589 x_ci_end_dt=>X_CI_END_DT,
590 x_ci_start_dt=>X_CI_START_DT,
591 x_comments=>X_COMMENTS,
592 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
593 x_dt_alias=>X_DT_ALIAS,
594 x_end_time=>X_END_TIME,
595 x_ese_id=>X_ESE_ID,
596 x_exam_cal_type=>X_EXAM_CAL_TYPE,
597 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
598 x_exam_session_number=>X_EXAM_SESSION_NUMBER,
599 x_start_time=>X_START_TIME,
600 x_creation_date=>X_LAST_UPDATE_DATE,
601 x_created_by=>X_LAST_UPDATED_BY,
602 x_last_update_date=>X_LAST_UPDATE_DATE,
603 x_last_updated_by=>X_LAST_UPDATED_BY,
604 x_last_update_login=>X_LAST_UPDATE_LOGIN
605 );
606 update IGS_AS_EXAM_SESSION_ALL set
607 CI_START_DT = NEW_REFERENCES.CI_START_DT,
608 CI_END_DT = NEW_REFERENCES.CI_END_DT,
609 ESE_ID = NEW_REFERENCES.ESE_ID,
610 EXAM_SESSION_NUMBER = NEW_REFERENCES.EXAM_SESSION_NUMBER,
611 COMMENTS = NEW_REFERENCES.COMMENTS,
612 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
613 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
614 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
615 where ROWID = X_ROWID;
616 if (sql%notfound) then
617 raise no_data_found;
618 end if;
619 l_rowid:=NULL;
620 end UPDATE_ROW;
621 procedure ADD_ROW (
622 X_ROWID in out NOCOPY VARCHAR2,
623 X_ORG_ID in NUMBER,
624 X_EXAM_CAL_TYPE in VARCHAR2,
625 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
626 X_DT_ALIAS in VARCHAR2,
627 X_DAI_SEQUENCE_NUMBER in NUMBER,
628 X_START_TIME in DATE,
629 X_END_TIME in DATE,
630 X_CI_START_DT in DATE,
631 X_CI_END_DT in DATE,
632 X_ESE_ID in NUMBER,
633 X_EXAM_SESSION_NUMBER in NUMBER,
634 X_COMMENTS in VARCHAR2,
635 X_MODE in VARCHAR2 default 'R'
636 ) AS
637 cursor c1 is select rowid from IGS_AS_EXAM_SESSION_ALL
638 where EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
639 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
640 and DT_ALIAS = X_DT_ALIAS
641 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
642 and to_char(start_time,'HH24:MI') = to_char(x_start_time,'HH24:MI')
643 and to_char(end_time,'HH24:MI') = to_char(x_end_time,'HH24:MI')
644 ;
645 begin
646 open c1;
647 fetch c1 into X_ROWID;
648 if (c1%notfound) then
649 close c1;
650 INSERT_ROW (
651 X_ROWID,
652 X_ORG_ID,
653 X_EXAM_CAL_TYPE,
654 X_EXAM_CI_SEQUENCE_NUMBER,
655 X_DT_ALIAS,
656 X_DAI_SEQUENCE_NUMBER,
657 X_START_TIME,
658 X_END_TIME,
659 X_CI_START_DT,
660 X_CI_END_DT,
661 X_ESE_ID,
665 return;
662 X_EXAM_SESSION_NUMBER,
663 X_COMMENTS,
664 X_MODE);
666 end if;
667 close c1;
668 UPDATE_ROW (
669 X_ROWID,
670 X_EXAM_CAL_TYPE,
671 X_EXAM_CI_SEQUENCE_NUMBER,
672 X_DT_ALIAS,
673 X_DAI_SEQUENCE_NUMBER,
674 X_START_TIME,
675 X_END_TIME,
676 X_CI_START_DT,
677 X_CI_END_DT,
678 X_ESE_ID,
679 X_EXAM_SESSION_NUMBER,
680 X_COMMENTS,
681 X_MODE);
682 end ADD_ROW;
683 procedure DELETE_ROW (
684 X_ROWID in VARCHAR2) AS
685 begin
686 Before_DML(
687 p_action => 'DELETE',
688 x_rowid => X_ROWID
689 );
690 delete from IGS_AS_EXAM_SESSION_ALL
691 where ROWID = X_ROWID;
692 if (sql%notfound) then
693 raise no_data_found;
694 end if;
695 l_rowid:=NULL;
696 end DELETE_ROW;
697
698 PROCEDURE Check_Uniqueness AS
699 Begin
700 IF Get_UK_For_Validation (
701 new_references.ESE_ID) THEN
702 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
703 IGS_GE_MSG_STACK.ADD;
704 APP_EXCEPTION.RAISE_EXCEPTION;
705 END IF;
706 End Check_Uniqueness;
707
708 PROCEDURE Check_Constraints (
709 Column_Name IN VARCHAR2 DEFAULT NULL,
710 Column_Value IN VARCHAR2 DEFAULT NULL
711 )AS
712 BEGIN
713 IF column_name is null then
714 NULL;
715 ELSIF upper(Column_name) = 'DT_ALIAS' then
716 new_references.DT_ALIAS := column_value;
717 ELSIF upper(Column_name) = 'EXAM_CAL_TYPE' then
718 new_references.EXAM_CAL_TYPE := column_value;
719 ELSIF upper(Column_name) = 'EXAM_CI_SEQUENCE_NUMBER' then
720 new_references.EXAM_CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
721 ELSIF upper(Column_name) = 'EXAM_SESSION_NUMBER' then
722 new_references.EXAM_SESSION_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
723 ELSIF upper(Column_name) = 'ESE_ID' then
724 new_references.ESE_ID := IGS_GE_NUMBER.TO_NUM(column_value);
725 end if;
726
727
728 IF upper(column_name) = 'DT_ALIAS' OR column_name is null Then
729 IF new_references.DT_ALIAS <> UPPER(new_references.DT_ALIAS) Then
730 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
731 IGS_GE_MSG_STACK.ADD;
732 APP_EXCEPTION.RAISE_EXCEPTION;
733 END IF;
737 IF new_references.EXAM_CAL_TYPE <> UPPER(new_references.EXAM_CAL_TYPE) Then
734 END IF;
735 IF upper(column_name) = 'EXAM_CAL_TYPE' OR
736 column_name is null Then
738 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
739 IGS_GE_MSG_STACK.ADD;
740 APP_EXCEPTION.RAISE_EXCEPTION;
741 END IF;
742 END IF;
743 IF upper(column_name) = 'EXAM_CI_SEQUENCE_NUMBER' OR
744 column_name is null Then
745 IF new_references.EXAM_CI_SEQUENCE_NUMBER < 1 OR new_references.EXAM_CI_SEQUENCE_NUMBER > 999999 Then
746 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
747 IGS_GE_MSG_STACK.ADD;
748 APP_EXCEPTION.RAISE_EXCEPTION;
749 END IF;
750 END IF;
751 IF upper(column_name) = 'EXAM_SESSION_NUMBER' OR
752 column_name is null Then
753 IF new_references.EXAM_SESSION_NUMBER < 0 OR new_references.EXAM_SESSION_NUMBER > 99 Then
754 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
755 IGS_GE_MSG_STACK.ADD;
756 APP_EXCEPTION.RAISE_EXCEPTION;
757 END IF;
758 END IF;
759 IF upper(column_name) = 'DAI_SEQUENCE_NUMBER' OR
760 column_name is null Then
761 IF new_references.DAI_SEQUENCE_NUMBER < 1 OR NEW_REFERENCES.DAI_SEQUENCE_NUMBER > 999999 Then
762 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
763 IGS_GE_MSG_STACK.ADD;
764 APP_EXCEPTION.RAISE_EXCEPTION;
765 END IF;
766 END IF;
767
768 IF upper(Column_name) = 'ESE_ID' OR column_name is null Then
769 IF new_references.ESE_ID < 0 OR new_references.ESE_ID > 999999 Then
770 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
771 IGS_GE_MSG_STACK.ADD;
772 APP_EXCEPTION.RAISE_EXCEPTION;
773 END IF;
774 END IF;
775
776 END Check_Constraints;
777
778
779 end IGS_AS_EXAM_SESSION_PKG;