[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_EXAM_INSTANCE_PKG
Source
1 package body IGS_AS_EXAM_INSTANCE_PKG AS
2 /* $Header: IGSDI04B.pls 115.8 2003/04/14 09:16:54 anilk ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_EXAM_INSTANCE_ALL%RowType;
5 new_references IGS_AS_EXAM_INSTANCE_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_ass_id IN NUMBER DEFAULT NULL,
11 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_dai_sequence_number IN NUMBER DEFAULT NULL,
15 x_start_time IN DATE DEFAULT NULL,
16 x_end_time IN DATE DEFAULT NULL,
17 x_ese_id IN NUMBER DEFAULT NULL,
18 x_venue_cd IN VARCHAR2 DEFAULT NULL,
19 x_collect_person_id IN NUMBER DEFAULT NULL,
20 x_special_session_ind IN VARCHAR2 DEFAULT NULL,
21 x_override_start_time IN DATE DEFAULT NULL,
22 x_override_end_time IN DATE DEFAULT NULL,
23 x_special_announcements IN VARCHAR2 DEFAULT NULL,
24 x_special_instructions IN VARCHAR2 DEFAULT NULL,
25 x_worked_script_instructions IN VARCHAR2 DEFAULT NULL,
26 x_comments IN VARCHAR2 DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL
32 ) AS
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AS_EXAM_INSTANCE_ALL
36 WHERE rowid = x_rowid;
37 BEGIN
38 l_rowid := x_rowid;
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 Close cur_old_ref_values;
47 APP_EXCEPTION.RAISE_EXCEPTION;
48
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52 -- Populate New Values.
53 new_references.org_id := x_org_id;
54 new_references.ass_id := x_ass_id;
55 new_references.exam_cal_type := x_exam_cal_type;
56 new_references.exam_ci_sequence_number := x_exam_ci_sequence_number;
57 new_references.dt_alias := x_dt_alias;
58 new_references.dai_sequence_number := x_dai_sequence_number;
59 new_references.start_time := x_start_time;
60 new_references.end_time := x_end_time;
61 new_references.ese_id := x_ese_id;
62 new_references.venue_cd := x_venue_cd;
63 new_references.collect_person_id := x_collect_person_id;
64 new_references.special_session_ind := x_special_session_ind;
65 new_references.override_start_time := x_override_start_time;
66 new_references.override_end_time := x_override_end_time;
67 new_references.special_announcements := x_special_announcements;
68 new_references.special_instructions := x_special_instructions;
69 new_references.worked_script_instructions := x_worked_script_instructions;
70 new_references.comments := x_comments;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81 END Set_Column_Values;
82 PROCEDURE BeforeRowInsert1(
83 p_inserting IN BOOLEAN DEFAULT FALSE,
84 p_updating IN BOOLEAN DEFAULT FALSE,
85 p_deleting IN BOOLEAN DEFAULT FALSE
86 ) AS
87 v_message_name VARCHAR2(30);
88 BEGIN
89 -- Call routine to fill in exam session key.
90 IGS_AS_GEN_006.ASSP_GET_ESE_KEY(
91 new_references.exam_cal_type,
92 new_references.exam_ci_sequence_number,
93 new_references.dt_alias,
94 new_references.dai_sequence_number,
95 new_references.start_time,
96 new_references.end_time,
97 new_references.ese_id);
98 -- Validate the venue closed indicator.
99 IF igs_gr_val_gc.assp_val_ve_closed( new_references.venue_cd,
100 v_message_name) = FALSE THEN
101 FND_MESSAGE.SET_NAME('IGS',V_MESSAGE_NAME);
102 IGS_GE_MSG_STACK.ADD;
103 APP_EXCEPTION.RAISE_EXCEPTION;
104 END IF;
105 -- Validate other elements on insert.
106 IF IGS_AS_VAL_EI.assp_val_ei_ins( new_references.exam_cal_type,
107 new_references.exam_ci_sequence_number,
108 new_references.ass_id,
109 v_message_name) = FALSE THEN
110 FND_MESSAGE.SET_NAME('IGS',V_MESSAGE_NAME);
111 IGS_GE_MSG_STACK.ADD;
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 END IF;
114 END BeforeRowInsert1;
115 PROCEDURE Check_Parent_Existance AS
116 BEGIN
117 IF (((old_references.ass_id = new_references.ass_id)) OR
118 ((new_references.ass_id IS NULL))) THEN
119 NULL;
120 ELSIF NOT IGS_AS_ASSESSMNT_ITM_PKG.Get_PK_For_Validation (
121 new_references.ass_id ) THEN
122 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
123 IGS_GE_MSG_STACK.ADD;
124 APP_EXCEPTION.RAISE_EXCEPTION;
125
126 END IF;
127 IF (((old_references.exam_cal_type = new_references.exam_cal_type) AND
128 (old_references.exam_ci_sequence_number = new_references.exam_ci_sequence_number) AND
129 (old_references.dt_alias = new_references.dt_alias) AND
130 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
131 (old_references.start_time = new_references.start_time) AND
132 (old_references.end_time = new_references.end_time)) OR
133 ((new_references.exam_cal_type IS NULL) OR
134 (new_references.exam_ci_sequence_number IS NULL) OR
135 (new_references.dt_alias IS NULL) OR
136 (new_references.dai_sequence_number IS NULL) OR
137 (new_references.start_time IS NULL) OR
138 (new_references.end_time IS NULL))) THEN
139 NULL;
140 ELSIF NOT IGS_AS_EXAM_SESSION_PKG.Get_PK_For_Validation (
141 new_references.exam_cal_type,
142 new_references.exam_ci_sequence_number,
143 new_references.dt_alias,
144 new_references.dai_sequence_number,
145 new_references.start_time,
146 new_references.end_time ) THEN
147 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
148 IGS_GE_MSG_STACK.ADD;
149 APP_EXCEPTION.RAISE_EXCEPTION;
150
151 END IF;
152 IF (((old_references.ese_id = new_references.ese_id)) OR
153 ((new_references.ese_id IS NULL))) THEN
154 NULL;
155 ELSIF NOT IGS_AS_EXAM_SESSION_PKG.Get_UK_For_Validation (
156 new_references.ese_id ) THEN
157 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
158 IGS_GE_MSG_STACK.ADD;
159 APP_EXCEPTION.RAISE_EXCEPTION;
160
161 END IF;
162 IF (((old_references.collect_person_id = new_references.collect_person_id)) OR
163 ((new_references.collect_person_id IS NULL))) THEN
164 NULL;
165 ELSIF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
166 new_references.collect_person_id ) THEN
167 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
168 IGS_GE_MSG_STACK.ADD;
169 APP_EXCEPTION.RAISE_EXCEPTION;
170
171 END IF;
172 IF (((old_references.venue_cd = new_references.venue_cd)) OR
173 ((new_references.venue_cd IS NULL))) THEN
174 NULL;
175 ELSIF NOT IGS_GR_VENUE_PKG.Get_PK_For_Validation (
176 new_references.venue_cd ) THEN
177 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
178 IGS_GE_MSG_STACK.ADD;
179 APP_EXCEPTION.RAISE_EXCEPTION;
180
181 END IF;
182 END Check_Parent_Existance;
183 PROCEDURE Check_Child_Existance AS
184 BEGIN
185 IGS_AS_EXM_INS_SPVSR_PKG.GET_FK_IGS_AS_EXAM_INSTANCE (
186 old_references.ass_id,
187 old_references.exam_cal_type,
188 old_references.exam_ci_sequence_number,
189 old_references.dt_alias,
190 old_references.dai_sequence_number,
191 old_references.start_time,
192 old_references.end_time,
193 old_references.venue_cd
194 );
195 IGS_AS_STD_EXM_INSTN_PKG.GET_FK_IGS_AS_EXAM_INSTANCE (
196 old_references.ass_id,
197 old_references.exam_cal_type,
198 old_references.exam_ci_sequence_number,
199 old_references.dt_alias,
200 old_references.dai_sequence_number,
201 old_references.start_time,
202 old_references.end_time,
203 old_references.venue_cd
204 );
205 END Check_Child_Existance;
206 FUNCTION Get_PK_For_Validation (
207 x_ass_id IN NUMBER,
208 x_exam_cal_type IN VARCHAR2,
209 x_exam_ci_sequence_number IN NUMBER,
210 x_dt_alias IN VARCHAR2,
211 x_dai_sequence_number IN NUMBER,
212 x_start_time IN DATE,
213 x_end_time IN DATE,
214 x_venue_cd IN VARCHAR2
215 ) RETURN BOOLEAN AS
216 CURSOR cur_rowid IS
217 SELECT rowid
218 FROM IGS_AS_EXAM_INSTANCE_ALL
219 WHERE ass_id = x_ass_id
220 AND exam_cal_type = x_exam_cal_type
221 AND exam_ci_sequence_number = x_exam_ci_sequence_number
222 AND dt_alias = x_dt_alias
223 AND dai_sequence_number = x_dai_sequence_number
224 AND start_time = x_start_time
225 AND end_time = x_end_time
226 AND venue_cd = x_venue_cd
227 FOR UPDATE NOWAIT;
228 lv_rowid cur_rowid%RowType;
229 BEGIN
230 Open cur_rowid;
231 Fetch cur_rowid INTO lv_rowid;
232 IF (cur_rowid%FOUND) THEN
233 Close cur_rowid;
234 Return (TRUE);
235 ELSE
236 Close cur_rowid;
237 Return (FALSE);
238 END IF;
239 END Get_PK_For_Validation;
240 PROCEDURE GET_FK_IGS_AS_ASSESSMNT_ITM (
241 x_ass_id IN NUMBER
242 ) AS
243 CURSOR cur_rowid IS
244 SELECT rowid
245 FROM IGS_AS_EXAM_INSTANCE_ALL
246 WHERE ass_id = x_ass_id ;
247 lv_rowid cur_rowid%RowType;
248 BEGIN
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EI_AI_FK');
253 IGS_GE_MSG_STACK.ADD;
254 Close cur_rowid;
255 APP_EXCEPTION.RAISE_EXCEPTION;
256
257 Return;
258 END IF;
259 Close cur_rowid;
260 END GET_FK_IGS_AS_ASSESSMNT_ITM;
261 PROCEDURE GET_FK_IGS_AS_EXAM_SESSION (
262 x_exam_cal_type IN VARCHAR2,
263 x_exam_ci_sequence_number IN NUMBER,
264 x_dt_alias IN VARCHAR2,
265 x_dai_sequence_number IN NUMBER,
266 x_start_time IN DATE,
267 x_end_time IN DATE
268 ) AS
269 CURSOR cur_rowid IS
270 SELECT rowid
271 FROM IGS_AS_EXAM_INSTANCE_ALL
272 WHERE exam_cal_type = x_exam_cal_type
273 AND exam_ci_sequence_number = x_exam_ci_sequence_number
274 AND dt_alias = x_dt_alias
275 AND dai_sequence_number = x_dai_sequence_number
276 AND start_time = x_start_time
277 AND end_time = x_end_time ;
278 lv_rowid cur_rowid%RowType;
279 BEGIN
280 Open cur_rowid;
281 Fetch cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EI_ESE_UFK');
284 IGS_GE_MSG_STACK.ADD;
285 Close cur_rowid;
286 APP_EXCEPTION.RAISE_EXCEPTION;
287
288 Return;
289 END IF;
290 Close cur_rowid;
291 END GET_FK_IGS_AS_EXAM_SESSION;
292 PROCEDURE GET_UFK_IGS_AS_EXAM_SESSION (
293 x_ese_id IN NUMBER
294 ) AS
295 CURSOR cur_rowid IS
296 SELECT rowid
297 FROM IGS_AS_EXAM_INSTANCE_ALL
298 WHERE ese_id = x_ese_id ;
299 lv_rowid cur_rowid%RowType;
300 BEGIN
301 Open cur_rowid;
302 Fetch cur_rowid INTO lv_rowid;
303 IF (cur_rowid%FOUND) THEN
304 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EI_ESE_UFK');
305 IGS_GE_MSG_STACK.ADD;
306 Close cur_rowid;
307 APP_EXCEPTION.RAISE_EXCEPTION;
308
309 Return;
310 END IF;
311 Close cur_rowid;
312 END GET_UFK_IGS_AS_EXAM_SESSION;
313 PROCEDURE GET_FK_IGS_PE_PERSON (
314 x_person_id IN NUMBER
315 ) AS
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM IGS_AS_EXAM_INSTANCE_ALL
319 WHERE collect_person_id = x_person_id ;
320 lv_rowid cur_rowid%RowType;
321 BEGIN
322 Open cur_rowid;
323 Fetch cur_rowid INTO lv_rowid;
324 IF (cur_rowid%FOUND) THEN
325 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EI_PE_FK');
326 IGS_GE_MSG_STACK.ADD;
327 Close cur_rowid;
328 APP_EXCEPTION.RAISE_EXCEPTION;
329
330 Return;
331 END IF;
332 Close cur_rowid;
333 END GET_FK_IGS_PE_PERSON;
334 PROCEDURE GET_FK_IGS_GR_VENUE (
335 x_venue_cd IN VARCHAR2
336 ) AS
337 CURSOR cur_rowid IS
338 SELECT rowid
339 FROM IGS_AS_EXAM_INSTANCE_ALL
340 WHERE venue_cd = x_venue_cd ;
341 lv_rowid cur_rowid%RowType;
342 BEGIN
343 Open cur_rowid;
344 Fetch cur_rowid INTO lv_rowid;
345 IF (cur_rowid%FOUND) THEN
346 Fnd_Message.Set_Name ('IGS', 'IGS_AS_EI_VE_FK');
347 IGS_GE_MSG_STACK.ADD;
348 Close cur_rowid;
349 APP_EXCEPTION.RAISE_EXCEPTION;
350
351 Return;
352 END IF;
353 Close cur_rowid;
354 END GET_FK_IGS_GR_VENUE;
355 PROCEDURE Before_DML (
356 p_action IN VARCHAR2,
357 x_rowid IN VARCHAR2 DEFAULT NULL,
358 x_org_id IN NUMBER DEFAULT NULL,
359 x_ass_id IN NUMBER DEFAULT NULL,
360 x_exam_cal_type IN VARCHAR2 DEFAULT NULL,
361 x_exam_ci_sequence_number IN NUMBER DEFAULT NULL,
362 x_dt_alias IN VARCHAR2 DEFAULT NULL,
363 x_dai_sequence_number IN NUMBER DEFAULT NULL,
364 x_start_time IN DATE DEFAULT NULL,
365 x_end_time IN DATE DEFAULT NULL,
366 x_ese_id IN NUMBER DEFAULT NULL,
367 x_venue_cd IN VARCHAR2 DEFAULT NULL,
368 x_collect_person_id IN NUMBER DEFAULT NULL,
369 x_special_session_ind IN VARCHAR2 DEFAULT NULL,
370 x_override_start_time IN DATE DEFAULT NULL,
371 x_override_end_time IN DATE DEFAULT NULL,
372 x_special_announcements IN VARCHAR2 DEFAULT NULL,
373 x_special_instructions IN VARCHAR2 DEFAULT NULL,
374 x_worked_script_instructions IN VARCHAR2 DEFAULT NULL,
375 x_comments IN VARCHAR2 DEFAULT NULL,
376 x_creation_date IN DATE DEFAULT NULL,
377 x_created_by IN NUMBER DEFAULT NULL,
378 x_last_update_date IN DATE DEFAULT NULL,
379 x_last_updated_by IN NUMBER DEFAULT NULL,
380 x_last_update_login IN NUMBER DEFAULT NULL
381 ) AS
382 BEGIN
383 Set_Column_Values (
384 p_action,
385 x_rowid,
386 x_org_id,
387 x_ass_id,
388 x_exam_cal_type,
389 x_exam_ci_sequence_number,
390 x_dt_alias,
391 x_dai_sequence_number,
392 x_start_time,
393 x_end_time,
394 x_ese_id,
395 x_venue_cd,
396 x_collect_person_id,
397 x_special_session_ind,
398 x_override_start_time,
399 x_override_end_time,
400 x_special_announcements,
401 x_special_instructions,
402 x_worked_script_instructions,
403 x_comments,
404 x_creation_date,
405 x_created_by,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_login
409 );
413 IF Get_PK_For_Validation ( new_references.ass_id ,
410 IF (p_action = 'INSERT') THEN
411 -- Call all the procedures related to Before Insert.
412 BeforeRowInsert1 ( p_inserting => TRUE );
414 new_references.exam_cal_type ,
415 new_references.exam_ci_sequence_number ,
416 new_references.dt_alias ,
417 new_references.dai_sequence_number,
418 new_references.start_time,
419 new_references.end_time ,
420 new_references.venue_cd ) THEN
421 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
422 IGS_GE_MSG_STACK.ADD;
423 APP_EXCEPTION.RAISE_EXCEPTION;
424 END IF;
425
426 Check_Constraints;
427 Check_Parent_Existance;
428 ELSIF (p_action = 'UPDATE') THEN
429 -- Call all the procedures related to Before Update.
430
431 Check_Constraints;
432 Check_Parent_Existance;
433
434 ELSIF (p_action = 'VALIDATE_INSERT') THEN
435 IF Get_PK_For_Validation (
436 new_references.ass_id ,
437 new_references.exam_cal_type ,
438 new_references.exam_ci_sequence_number ,
439 new_references.dt_alias ,
440 new_references.dai_sequence_number,
441 new_references.start_time,
442 new_references.end_time ,
443 new_references.venue_cd ) THEN
444 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
445 IGS_GE_MSG_STACK.ADD;
446 APP_EXCEPTION.RAISE_EXCEPTION;
447 END IF;
448
449 Check_Constraints;
450 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
451
452 Check_Constraints;
453
454 ELSIF (p_action = 'VALIDATE_DELETE') THEN
455 Check_Child_Existance;
456 END IF;
457 END Before_DML;
458
459 procedure INSERT_ROW (
460 X_ROWID in out NOCOPY VARCHAR2,
461 X_ASS_ID in NUMBER,
462 X_ORG_ID in NUMBER,
463 X_EXAM_CAL_TYPE in VARCHAR2,
464 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
465 X_DT_ALIAS in VARCHAR2,
466 X_DAI_SEQUENCE_NUMBER in NUMBER,
467 X_START_TIME in DATE,
468 X_END_TIME in DATE,
469 X_VENUE_CD in VARCHAR2,
470 X_ESE_ID in NUMBER,
471 X_COLLECT_PERSON_ID in NUMBER,
472 X_SPECIAL_SESSION_IND in VARCHAR2,
473 X_OVERRIDE_START_TIME in DATE,
474 X_OVERRIDE_END_TIME in DATE,
475 X_SPECIAL_ANNOUNCEMENTS in VARCHAR2,
476 X_SPECIAL_INSTRUCTIONS in VARCHAR2,
477 X_WORKED_SCRIPT_INSTRUCTIONS in VARCHAR2,
478 X_COMMENTS in VARCHAR2,
479 X_MODE in VARCHAR2 default 'R'
480 ) As
481 cursor C is select ROWID from IGS_AS_EXAM_INSTANCE_ALL
482 where ASS_ID = X_ASS_ID
483 and EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
484 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
485 and DT_ALIAS = X_DT_ALIAS
486 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
487 and START_TIME = X_START_TIME
488 and END_TIME = X_END_TIME
489 and VENUE_CD = X_VENUE_CD;
490 X_LAST_UPDATE_DATE DATE;
491 X_LAST_UPDATED_BY NUMBER;
492 X_LAST_UPDATE_LOGIN NUMBER;
493 begin
494 X_LAST_UPDATE_DATE := SYSDATE;
495 if(X_MODE = 'I') then
496 X_LAST_UPDATED_BY := 1;
497 X_LAST_UPDATE_LOGIN := 0;
498 elsif (X_MODE = 'R') then
499 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
500 if X_LAST_UPDATED_BY is NULL then
501 X_LAST_UPDATED_BY := -1;
502 end if;
503 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
504 if X_LAST_UPDATE_LOGIN is NULL then
505 X_LAST_UPDATE_LOGIN := -1;
506 end if;
507 else
508 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
509 IGS_GE_MSG_STACK.ADD;
510 APP_EXCEPTION.RAISE_EXCEPTION;
511 end if;
512 Before_DML(
513 p_action=>'INSERT',
514 x_rowid=>X_ROWID,
515 x_org_id => igs_ge_gen_003.get_org_id,
516 x_ass_id=>X_ASS_ID,
517 x_collect_person_id=>X_COLLECT_PERSON_ID,
518 x_comments=>X_COMMENTS,
519 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
520 x_dt_alias=>X_DT_ALIAS,
521 x_end_time=>X_END_TIME,
522 x_ese_id=>X_ESE_ID,
523 x_exam_cal_type=>X_EXAM_CAL_TYPE,
524 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
525 x_override_end_time=>X_OVERRIDE_END_TIME,
526 x_override_start_time=>X_OVERRIDE_START_TIME,
527 x_special_announcements=>X_SPECIAL_ANNOUNCEMENTS,
528 x_special_instructions=>X_SPECIAL_INSTRUCTIONS,
529 x_special_session_ind=>X_SPECIAL_SESSION_IND,
530 x_start_time=>X_START_TIME,
531 x_venue_cd=>X_VENUE_CD,
532 x_worked_script_instructions=>X_WORKED_SCRIPT_INSTRUCTIONS,
533 x_creation_date=>X_LAST_UPDATE_DATE,
534 x_created_by=>X_LAST_UPDATED_BY,
535 x_last_update_date=>X_LAST_UPDATE_DATE,
536 x_last_updated_by=>X_LAST_UPDATED_BY,
537 x_last_update_login=>X_LAST_UPDATE_LOGIN
538 );
539 insert into IGS_AS_EXAM_INSTANCE_ALL (
540 ASS_ID,
541 ORG_ID,
542 EXAM_CAL_TYPE,
543 EXAM_CI_SEQUENCE_NUMBER,
544 DT_ALIAS,
545 DAI_SEQUENCE_NUMBER,
546 START_TIME,
547 END_TIME,
548 ESE_ID,
549 VENUE_CD,
550 COLLECT_PERSON_ID,
551 SPECIAL_SESSION_IND,
552 OVERRIDE_START_TIME,
553 OVERRIDE_END_TIME,
554 SPECIAL_ANNOUNCEMENTS,
555 SPECIAL_INSTRUCTIONS,
559 CREATED_BY,
556 WORKED_SCRIPT_INSTRUCTIONS,
557 COMMENTS,
558 CREATION_DATE,
560 LAST_UPDATE_DATE,
561 LAST_UPDATED_BY,
562 LAST_UPDATE_LOGIN
563 ) values (
564 NEW_REFERENCES.ASS_ID,
565 NEW_REFERENCES.ORG_ID,
566 NEW_REFERENCES.EXAM_CAL_TYPE,
567 NEW_REFERENCES.EXAM_CI_SEQUENCE_NUMBER,
568 NEW_REFERENCES.DT_ALIAS,
569 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
570 NEW_REFERENCES.START_TIME,
571 NEW_REFERENCES.END_TIME,
572 NEW_REFERENCES.ESE_ID,
573 NEW_REFERENCES.VENUE_CD,
574 NEW_REFERENCES.COLLECT_PERSON_ID,
575 NEW_REFERENCES.SPECIAL_SESSION_IND,
576 NEW_REFERENCES.OVERRIDE_START_TIME,
577 NEW_REFERENCES.OVERRIDE_END_TIME,
578 NEW_REFERENCES.SPECIAL_ANNOUNCEMENTS,
579 NEW_REFERENCES.SPECIAL_INSTRUCTIONS,
580 NEW_REFERENCES.WORKED_SCRIPT_INSTRUCTIONS,
581 NEW_REFERENCES.COMMENTS,
582 X_LAST_UPDATE_DATE,
583 X_LAST_UPDATED_BY,
584 X_LAST_UPDATE_DATE,
585 X_LAST_UPDATED_BY,
586 X_LAST_UPDATE_LOGIN
587 );
588 open c;
589 fetch c into X_ROWID;
590 if (c%notfound) then
591 close c;
592 raise no_data_found;
593 end if;
594 close c;
595
596 end INSERT_ROW;
597 procedure LOCK_ROW (
598 X_ROWID in VARCHAR2,
599 X_ASS_ID in NUMBER,
600 X_EXAM_CAL_TYPE in VARCHAR2,
601 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
602 X_DT_ALIAS in VARCHAR2,
603 X_DAI_SEQUENCE_NUMBER in NUMBER,
604 X_START_TIME in DATE,
605 X_END_TIME in DATE,
606 X_VENUE_CD in VARCHAR2,
607 X_ESE_ID in NUMBER,
608 X_COLLECT_PERSON_ID in NUMBER,
609 X_SPECIAL_SESSION_IND in VARCHAR2,
610 X_OVERRIDE_START_TIME in DATE,
611 X_OVERRIDE_END_TIME in DATE,
612 X_SPECIAL_ANNOUNCEMENTS in VARCHAR2,
613 X_SPECIAL_INSTRUCTIONS in VARCHAR2,
614 X_WORKED_SCRIPT_INSTRUCTIONS in VARCHAR2,
615 X_COMMENTS in VARCHAR2
616 ) AS
617 cursor c1 is select
618 ESE_ID,
619 COLLECT_PERSON_ID,
620 SPECIAL_SESSION_IND,
621 OVERRIDE_START_TIME,
622 OVERRIDE_END_TIME,
623 SPECIAL_ANNOUNCEMENTS,
624 SPECIAL_INSTRUCTIONS,
625 WORKED_SCRIPT_INSTRUCTIONS,
626 COMMENTS
627 from IGS_AS_EXAM_INSTANCE_ALL
628 where ROWID = X_ROWID for update nowait;
629 tlinfo c1%rowtype;
630 begin
631 open c1;
632 fetch c1 into tlinfo;
633 if (c1%notfound) then
634 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
635 IGS_GE_MSG_STACK.ADD;
636 APP_EXCEPTION.RAISE_EXCEPTION;
637 close c1;
638 return;
639 end if;
640 close c1;
641 if ( (tlinfo.ESE_ID = X_ESE_ID)
642 AND ((tlinfo.COLLECT_PERSON_ID = X_COLLECT_PERSON_ID)
643 OR ((tlinfo.COLLECT_PERSON_ID is null)
644 AND (X_COLLECT_PERSON_ID is null)))
645 AND (tlinfo.SPECIAL_SESSION_IND = X_SPECIAL_SESSION_IND)
646 AND ((tlinfo.OVERRIDE_START_TIME = X_OVERRIDE_START_TIME)
647 OR ((tlinfo.OVERRIDE_START_TIME is null)
648 AND (X_OVERRIDE_START_TIME is null)))
649 AND ((tlinfo.OVERRIDE_END_TIME = X_OVERRIDE_END_TIME)
650 OR ((tlinfo.OVERRIDE_END_TIME is null)
651 AND (X_OVERRIDE_END_TIME is null)))
652 AND ((tlinfo.SPECIAL_ANNOUNCEMENTS = X_SPECIAL_ANNOUNCEMENTS)
653 OR ((tlinfo.SPECIAL_ANNOUNCEMENTS is null)
654 AND (X_SPECIAL_ANNOUNCEMENTS is null)))
655 AND ((tlinfo.SPECIAL_INSTRUCTIONS = X_SPECIAL_INSTRUCTIONS)
656 OR ((tlinfo.SPECIAL_INSTRUCTIONS is null)
657 AND (X_SPECIAL_INSTRUCTIONS is null)))
658 AND ((tlinfo.WORKED_SCRIPT_INSTRUCTIONS = X_WORKED_SCRIPT_INSTRUCTIONS)
659 OR ((tlinfo.WORKED_SCRIPT_INSTRUCTIONS is null)
660 AND (X_WORKED_SCRIPT_INSTRUCTIONS is null)))
661 AND ((tlinfo.COMMENTS = X_COMMENTS)
662 OR ((tlinfo.COMMENTS is null)
663 AND (X_COMMENTS is null)))
664 ) then
665 null;
666 else
667 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
668 IGS_GE_MSG_STACK.ADD;
669 APP_EXCEPTION.RAISE_EXCEPTION;
670 end if;
671 return;
672 end LOCK_ROW;
673 procedure UPDATE_ROW (
674 X_ROWID in VARCHAR2,
675 X_ASS_ID in NUMBER,
676 X_EXAM_CAL_TYPE in VARCHAR2,
677 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
678 X_DT_ALIAS in VARCHAR2,
679 X_DAI_SEQUENCE_NUMBER in NUMBER,
680 X_START_TIME in DATE,
681 X_END_TIME in DATE,
682 X_VENUE_CD in VARCHAR2,
683 X_ESE_ID in NUMBER,
684 X_COLLECT_PERSON_ID in NUMBER,
685 X_SPECIAL_SESSION_IND in VARCHAR2,
686 X_OVERRIDE_START_TIME in DATE,
687 X_OVERRIDE_END_TIME in DATE,
688 X_SPECIAL_ANNOUNCEMENTS in VARCHAR2,
689 X_SPECIAL_INSTRUCTIONS in VARCHAR2,
690 X_WORKED_SCRIPT_INSTRUCTIONS in VARCHAR2,
691 X_COMMENTS in VARCHAR2,
692 X_MODE in VARCHAR2 default 'R'
693 ) AS
694 X_LAST_UPDATE_DATE DATE;
695 X_LAST_UPDATED_BY NUMBER;
696 X_LAST_UPDATE_LOGIN NUMBER;
697 begin
698 X_LAST_UPDATE_DATE := SYSDATE;
699 if(X_MODE = 'I') then
700 X_LAST_UPDATED_BY := 1;
701 X_LAST_UPDATE_LOGIN := 0;
702 elsif (X_MODE = 'R') then
706 end if;
703 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
704 if X_LAST_UPDATED_BY is NULL then
705 X_LAST_UPDATED_BY := -1;
707 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
708 if X_LAST_UPDATE_LOGIN is NULL then
709 X_LAST_UPDATE_LOGIN := -1;
710 end if;
711 else
712 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
713 IGS_GE_MSG_STACK.ADD;
714 APP_EXCEPTION.RAISE_EXCEPTION;
715 end if;
716 Before_DML(
717 p_action=>'UPDATE',
718 x_rowid=>X_ROWID,
719 x_ass_id=>X_ASS_ID,
720 x_collect_person_id=>X_COLLECT_PERSON_ID,
721 x_comments=>X_COMMENTS,
722 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
723 x_dt_alias=>X_DT_ALIAS,
724 x_end_time=>X_END_TIME,
725 x_ese_id=>X_ESE_ID,
726 x_exam_cal_type=>X_EXAM_CAL_TYPE,
727 x_exam_ci_sequence_number=>X_EXAM_CI_SEQUENCE_NUMBER,
728 x_override_end_time=>X_OVERRIDE_END_TIME,
729 x_override_start_time=>X_OVERRIDE_START_TIME,
730 x_special_announcements=>X_SPECIAL_ANNOUNCEMENTS,
731 x_special_instructions=>X_SPECIAL_INSTRUCTIONS,
732 x_special_session_ind=>X_SPECIAL_SESSION_IND,
733 x_start_time=>X_START_TIME,
734 x_venue_cd=>X_VENUE_CD,
735 x_worked_script_instructions=>X_WORKED_SCRIPT_INSTRUCTIONS,
736 x_creation_date=>X_LAST_UPDATE_DATE,
737 x_created_by=>X_LAST_UPDATED_BY,
738 x_last_update_date=>X_LAST_UPDATE_DATE,
739 x_last_updated_by=>X_LAST_UPDATED_BY,
740 x_last_update_login=>X_LAST_UPDATE_LOGIN
741 );
742 update IGS_AS_EXAM_INSTANCE_ALL set
743 ESE_ID = NEW_REFERENCES.ESE_ID,
744 COLLECT_PERSON_ID = NEW_REFERENCES.COLLECT_PERSON_ID,
745 SPECIAL_SESSION_IND = NEW_REFERENCES.SPECIAL_SESSION_IND,
746 OVERRIDE_START_TIME = NEW_REFERENCES.OVERRIDE_START_TIME,
747 OVERRIDE_END_TIME = NEW_REFERENCES.OVERRIDE_END_TIME,
748 SPECIAL_ANNOUNCEMENTS = NEW_REFERENCES.SPECIAL_ANNOUNCEMENTS,
749 SPECIAL_INSTRUCTIONS = NEW_REFERENCES.SPECIAL_INSTRUCTIONS,
750 WORKED_SCRIPT_INSTRUCTIONS = NEW_REFERENCES.WORKED_SCRIPT_INSTRUCTIONS,
751 COMMENTS = NEW_REFERENCES.COMMENTS,
752 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
753 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
754 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
755 where ROWID = X_ROWID;
756 if (sql%notfound) then
757 raise no_data_found;
758 end if;
759
760 end UPDATE_ROW;
761 procedure ADD_ROW (
762 X_ROWID in out NOCOPY VARCHAR2,
763 X_ASS_ID in NUMBER,
764 X_ORG_ID in NUMBER,
765 X_EXAM_CAL_TYPE in VARCHAR2,
766 X_EXAM_CI_SEQUENCE_NUMBER in NUMBER,
767 X_DT_ALIAS in VARCHAR2,
768 X_DAI_SEQUENCE_NUMBER in NUMBER,
769 X_START_TIME in DATE,
770 X_END_TIME in DATE,
771 X_VENUE_CD in VARCHAR2,
772 X_ESE_ID in NUMBER,
773 X_COLLECT_PERSON_ID in NUMBER,
774 X_SPECIAL_SESSION_IND in VARCHAR2,
775 X_OVERRIDE_START_TIME in DATE,
776 X_OVERRIDE_END_TIME in DATE,
777 X_SPECIAL_ANNOUNCEMENTS in VARCHAR2,
778 X_SPECIAL_INSTRUCTIONS in VARCHAR2,
779 X_WORKED_SCRIPT_INSTRUCTIONS in VARCHAR2,
780 X_COMMENTS in VARCHAR2,
781 X_MODE in VARCHAR2 default 'R'
782 ) AS
783 cursor c1 is select rowid from IGS_AS_EXAM_INSTANCE_ALL
784 where ASS_ID = X_ASS_ID
785 and EXAM_CAL_TYPE = X_EXAM_CAL_TYPE
786 and EXAM_CI_SEQUENCE_NUMBER = X_EXAM_CI_SEQUENCE_NUMBER
787 and DT_ALIAS = X_DT_ALIAS
788 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
789 and START_TIME = X_START_TIME
790 and END_TIME = X_END_TIME
791 and VENUE_CD = X_VENUE_CD
792 ;
793 begin
794 open c1;
795 fetch c1 into X_ROWID;
796 if (c1%notfound) then
797 close c1;
798 INSERT_ROW (
799 X_ROWID,
800 X_ASS_ID,
801 X_ORG_ID,
802 X_EXAM_CAL_TYPE,
803 X_EXAM_CI_SEQUENCE_NUMBER,
804 X_DT_ALIAS,
805 X_DAI_SEQUENCE_NUMBER,
806 X_START_TIME,
807 X_END_TIME,
808 X_VENUE_CD,
809 X_ESE_ID,
810 X_COLLECT_PERSON_ID,
811 X_SPECIAL_SESSION_IND,
812 X_OVERRIDE_START_TIME,
813 X_OVERRIDE_END_TIME,
814 X_SPECIAL_ANNOUNCEMENTS,
815 X_SPECIAL_INSTRUCTIONS,
816 X_WORKED_SCRIPT_INSTRUCTIONS,
817 X_COMMENTS,
818 X_MODE);
819 return;
820 end if;
821 close c1;
822 UPDATE_ROW (
823 X_ROWID,
824 X_ASS_ID,
825 X_EXAM_CAL_TYPE,
826 X_EXAM_CI_SEQUENCE_NUMBER,
827 X_DT_ALIAS,
828 X_DAI_SEQUENCE_NUMBER,
829 X_START_TIME,
830 X_END_TIME,
831 X_VENUE_CD,
832 X_ESE_ID,
833 X_COLLECT_PERSON_ID,
834 X_SPECIAL_SESSION_IND,
835 X_OVERRIDE_START_TIME,
836 X_OVERRIDE_END_TIME,
837 X_SPECIAL_ANNOUNCEMENTS,
838 X_SPECIAL_INSTRUCTIONS,
839 X_WORKED_SCRIPT_INSTRUCTIONS,
840 X_COMMENTS,
841 X_MODE);
842 end ADD_ROW;
843 procedure DELETE_ROW (
844 X_ROWID in VARCHAR2) is
845 begin
846 Before_DML(
847 p_action => 'DELETE',
848 x_rowid => X_ROWID
849 );
850 delete from IGS_AS_EXAM_INSTANCE_ALL
851 where ROWID = X_ROWID;
852 if (sql%notfound) then
853 raise no_data_found;
854 end if;
855
856 end DELETE_ROW;
857 PROCEDURE Check_Constraints (
858 Column_Name IN VARCHAR2 DEFAULT NULL,
859 Column_Value IN VARCHAR2 DEFAULT NULL
860 )
861 AS
862 BEGIN
863 IF column_name is null then
864 NULL;
868 new_references.EXAM_CAL_TYPE := column_value;
865 ELSIF upper(Column_name) = 'DT_ALIAS' then
866 new_references.DT_ALIAS := column_value;
867 ELSIF upper(Column_name) = 'EXAM_CAL_TYPE' then
869 ELSIF upper(Column_name) = 'SPECIAL_SESSION_IND' then
870 new_references.SPECIAL_SESSION_IND := column_value;
871 ELSIF upper(Column_name) = 'VENUE_CD' then
872 new_references.VENUE_CD := column_value;
873 ELSIF upper(Column_name) = 'DAI_SEQUENCE_NUMBER' then
874 new_references.DAI_SEQUENCE_NUMBER := igs_ge_number.to_num(column_value);
875 ELSIF upper(Column_name) = 'EXAM_CI_SEQUENCE_NUMBER' then
876 new_references.EXAM_CI_SEQUENCE_NUMBER := igs_ge_number.to_num(column_value);
877 ELSIF upper(Column_name) = 'SPECIAL_SESSION_IND' then
878 new_references.SPECIAL_SESSION_IND := column_value;
879 ELSIF upper(Column_name) = 'ESE_ID' then
880 new_references.ESE_ID := igs_ge_number.to_num(column_value);
881 END IF;
882
883 IF upper(column_name) = 'DT_ALIAS' OR
884 column_name is null Then
885 IF new_references.DT_ALIAS <> UPPER(new_references.DT_ALIAS) Then
886 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
887 IGS_GE_MSG_STACK.ADD;
888 APP_EXCEPTION.RAISE_EXCEPTION;
889 END IF;
890 END IF;
891 IF upper(column_name) = 'EXAM_CAL_TYPE' OR
892 column_name is null Then
893 IF new_references.EXAM_CAL_TYPE <> UPPER(new_references.EXAM_CAL_TYPE) Then
894 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
895 IGS_GE_MSG_STACK.ADD;
896 APP_EXCEPTION.RAISE_EXCEPTION;
897 END IF;
898 END IF;
899 IF upper(column_name) = 'SPECIAL_SESSION_IND' OR
900 column_name is null Then
901 IF new_references.SPECIAL_SESSION_IND <> UPPER(new_references.SPECIAL_SESSION_IND) Then
902 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
903 IGS_GE_MSG_STACK.ADD;
904 APP_EXCEPTION.RAISE_EXCEPTION;
905 END IF;
906 END IF;
907 IF upper(column_name) = 'VENUE_CD' OR
908 column_name is null Then
909 IF new_references.VENUE_CD <> UPPER(new_references.VENUE_CD) Then
910 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
911 IGS_GE_MSG_STACK.ADD;
912 APP_EXCEPTION.RAISE_EXCEPTION;
913 END IF;
914 END IF;
915
916 IF upper(column_name) = 'DAI_SEQUENCE_NUMBER' OR
917 column_name is null Then
918 IF new_references.DAI_SEQUENCE_NUMBER < 1 OR new_references.DAI_SEQUENCE_NUMBER > 99999 Then
919 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
920 IGS_GE_MSG_STACK.ADD;
921 APP_EXCEPTION.RAISE_EXCEPTION;
922 END IF;
923 END IF;
924
925 IF upper(column_name) = 'EXAM_CI_SEQUENCE_NUMBER' OR
926 column_name is null Then
927 IF new_references.EXAM_CI_SEQUENCE_NUMBER < 1 OR new_references.EXAM_CI_SEQUENCE_NUMBER> 99999 Then
928 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
929 IGS_GE_MSG_STACK.ADD;
930 APP_EXCEPTION.RAISE_EXCEPTION;
931 END IF;
932 END IF;
933
934 IF upper(column_name) = 'SPECIAL_SESSION_IND' OR
935 column_name is null Then
936 IF new_references.SPECIAL_SESSION_IND NOT IN ('Y','N') Then
937 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
938 IGS_GE_MSG_STACK.ADD;
939 APP_EXCEPTION.RAISE_EXCEPTION;
940 END IF;
941 END IF;
942 IF upper(column_name) = 'ESE_ID' OR
943 column_name is null Then
944 IF new_references.ESE_ID < 1 OR new_references.ESE_ID > 999999 Then
945 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
946 IGS_GE_MSG_STACK.ADD;
947 APP_EXCEPTION.RAISE_EXCEPTION;
948 END IF;
949 END IF;
950
951
952 END Check_Constraints;
953
954 end IGS_AS_EXAM_INSTANCE_PKG;