[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ASSESSMNT_ITM_PKG
Source
1 package body IGS_AS_ASSESSMNT_ITM_PKG as
2 /* $Header: IGSDI38B.pls 115.10 2003/02/24 11:58:23 anilk ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_ASSESSMNT_ITM_ALL%RowType;
6 new_references IGS_AS_ASSESSMNT_ITM_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_org_id IN NUMBER DEFAULT NULL,
12 x_ass_id IN NUMBER DEFAULT NULL,
13 x_assessment_type IN VARCHAR2 DEFAULT NULL,
14 x_description IN VARCHAR2 DEFAULT NULL,
15 x_exam_scheduled_ind IN VARCHAR2 DEFAULT NULL,
16 x_exam_working_time IN DATE DEFAULT NULL,
17 x_exam_announcements IN VARCHAR2 DEFAULT NULL,
18 x_exam_short_paper_name IN VARCHAR2 DEFAULT NULL,
19 x_exam_paper_name IN VARCHAR2 DEFAULT NULL,
20 x_exam_perusal_time IN DATE DEFAULT NULL,
21 x_exam_supervisor_instrctn IN VARCHAR2 DEFAULT NULL,
22 x_exam_allowable_instrctn IN VARCHAR2 DEFAULT NULL,
23 x_exam_non_allowed_instrctn IN VARCHAR2 DEFAULT NULL,
24 x_exam_supplied_instrctn IN VARCHAR2 DEFAULT NULL,
25 x_question_or_title IN VARCHAR2 DEFAULT NULL,
26 x_ass_length_or_duration IN VARCHAR2 DEFAULT NULL,
27 x_comments IN VARCHAR2 DEFAULT NULL,
28 x_exam_constraints IN VARCHAR2 DEFAULT NULL,
29 x_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
30 x_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
31 x_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
32 x_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
33 x_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
34 x_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
35 x_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
36 x_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
37 x_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
38 x_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
39 x_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
40 x_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
41 x_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
42 x_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
43 x_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
44 x_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
45 x_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
46 x_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
47 x_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
48 x_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
49 x_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL ,
50 x_creation_date IN DATE DEFAULT NULL,
51 x_created_by IN NUMBER DEFAULT NULL,
52 x_last_update_date IN DATE DEFAULT NULL,
53 x_last_updated_by IN NUMBER DEFAULT NULL,
54 x_last_update_login IN NUMBER DEFAULT NULL,
55 -- anilk, bug#2784198
56 x_closed_ind IN VARCHAR2 DEFAULT NULL
57 ) as
58
59 CURSOR cur_old_ref_values IS
60 SELECT *
61 FROM IGS_AS_ASSESSMNT_ITM_ALL
62 WHERE rowid = x_rowid;
63
64 BEGIN
65
66 l_rowid := x_rowid;
67
68 -- Code for setting the Old and New Reference Values.
69 -- Populate Old Values.
70 Open cur_old_ref_values;
71 Fetch cur_old_ref_values INTO old_references;
72 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
73 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
74 IGS_GE_MSG_STACK.ADD;
75 Close cur_old_ref_values;
76 App_Exception.Raise_Exception;
77 Return;
78 END IF;
79 Close cur_old_ref_values;
80
81 -- Populate New Values.
82 new_references.org_id := x_org_id;
83 new_references.ass_id := x_ass_id;
84 new_references.assessment_type := x_assessment_type;
85 new_references.description := x_description;
86 new_references.exam_scheduled_ind := x_exam_scheduled_ind;
87 new_references.exam_working_time := x_exam_working_time;
88 new_references.exam_announcements := x_exam_announcements;
89 new_references.exam_short_paper_name := x_exam_short_paper_name;
90 new_references.exam_paper_name := x_exam_paper_name;
91 new_references.exam_perusal_time := x_exam_perusal_time;
92 new_references.exam_supervisor_instrctn := x_exam_supervisor_instrctn;
93 new_references.exam_allowable_instrctn := x_exam_allowable_instrctn;
94 new_references.exam_non_allowed_instrctn := x_exam_non_allowed_instrctn;
95 new_references.exam_supplied_instrctn := x_exam_supplied_instrctn;
96 new_references.question_or_title := x_question_or_title;
97 new_references.ass_length_or_duration := x_ass_length_or_duration;
98 new_references.comments := x_comments;
99 new_references.exam_constraints := x_exam_constraints;
100 new_references.attribute_category := x_attribute_category;
101 new_references.attribute1 := x_attribute1;
102 new_references.attribute2 := x_attribute2;
103 new_references.attribute3 := x_attribute3;
104 new_references.attribute4 := x_attribute4;
105 new_references.attribute5 := x_attribute5;
106 new_references.attribute6 := x_attribute6;
107 new_references.attribute7 := x_attribute7;
108 new_references.attribute8 := x_attribute8;
109 new_references.attribute9 := x_attribute9;
110 new_references.attribute10 := x_attribute10;
111 new_references.attribute11 := x_attribute11;
112 new_references.attribute12 := x_attribute12;
113 new_references.attribute13 := x_attribute13;
114 new_references.attribute14 := x_attribute14;
115 new_references.attribute15 := x_attribute15;
116 new_references.attribute16 := x_attribute16;
117 new_references.attribute17 := x_attribute17;
118 new_references.attribute18 := x_attribute18;
119 new_references.attribute19 := x_attribute19;
120 new_references.attribute20 := x_attribute20;
121 IF (p_action = 'UPDATE') THEN
122 new_references.creation_date := old_references.creation_date;
123 new_references.created_by := old_references.created_by;
124 ELSE
125 new_references.creation_date := x_creation_date;
126 new_references.created_by := x_created_by;
127 END IF;
128 new_references.last_update_date := x_last_update_date;
129 new_references.last_updated_by := x_last_updated_by;
130 new_references.last_update_login := x_last_update_login;
131 -- anilk, bug#2784198
132 new_references.closed_ind := x_closed_ind;
133
134
135 END Set_Column_Values;
136
137 -- Trigger description :-
138 -- "OSS_TST".trg_ai_br_iu
139 -- BEFORE INSERT OR UPDATE
140 -- ON IGS_AS_ASSESSMNT_ITM
141 -- FOR EACH ROW
142
143 PROCEDURE BeforeRowInsertUpdate1(
144 p_inserting IN BOOLEAN DEFAULT FALSE,
145 p_updating IN BOOLEAN DEFAULT FALSE,
146 p_deleting IN BOOLEAN DEFAULT FALSE
147 ) as
148 v_message_name varchar2(30);
149 BEGIN
150 -- Validate that inserts are allowed
151 IF p_inserting THEN
152 -- <ai1>
153 -- Validate assessment type closed indicator
154 IF IGS_AS_VAL_AI.assp_val_atyp_closed(new_references.assessment_type,
155 v_message_name) = FALSE THEN
156 Fnd_Message.Set_Name('IGS', v_message_name);
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161 -- Validate that inserts/updates are allowed
162 IF p_inserting OR p_updating THEN
163 -- <ai2>
164 -- Validate the approp. ass item details set and are not set for the
165 -- respective assessment type examinable indicator setting
166 IF IGS_AS_VAL_AI.assp_val_ai_details(new_references.assessment_type,
167 new_references.exam_scheduled_ind,
168 new_references.exam_working_time,
169 new_references.exam_announcements,
170 new_references.exam_short_paper_name,
171 new_references.exam_paper_name,
172 new_references.exam_perusal_time,
173 new_references.exam_supervisor_instrctn,
174 new_references.exam_allowable_instrctn,
175 new_references.exam_non_allowed_instrctn,
176 new_references.exam_supplied_instrctn,
177 new_references.question_or_title,
178 new_references.ass_length_or_duration,
179 v_message_name) = FALSE THEN
180 Fnd_Message.Set_Name('IGS', v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 -- <ai3>
185 -- Validate exam times
186 IF IGS_AS_VAL_AI.assp_val_ai_ex_times(new_references.assessment_type,
187 new_references.exam_working_time,
188 new_references.exam_perusal_time,
189 v_message_name) = FALSE THEN
190 Fnd_Message.Set_Name('IGS', v_message_name);
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END IF;
195
196
197 END BeforeRowInsertUpdate1;
198
199 -- Trigger description :-
200 -- "OSS_TST".trg_ai_ar_u
201 -- AFTER UPDATE
202 -- ON IGS_AS_ASSESSMNT_ITM
203 -- FOR EACH ROW
204
205 PROCEDURE AfterRowUpdate2(
206 p_inserting IN BOOLEAN DEFAULT FALSE,
207 p_updating IN BOOLEAN DEFAULT FALSE,
208 p_deleting IN BOOLEAN DEFAULT FALSE
209 ) as
210
211 v_message_name varchar2(30);
212 BEGIN
213 IF p_updating AND
214 (new_references.ASSESSMENT_TYPE <> old_references.ASSESSMENT_TYPE) THEN
215 -- Validate that p_updating the assessment type will not cause non-unique
216 -- IGS_AS_UNITASS_ITEM.reference within a IGS_PS_UNIT offering pattern.
217 -- Save row id as validation causes a mutating trigger.
218 IF IGS_AS_VAL_AI.assp_val_ai_type(
219 new_references.ass_id,
220 new_references.assessment_type,
221 new_references.assessment_type,
222 v_message_name) = FALSE THEN
223 -- Error.
224 Fnd_Message.Set_Name('IGS', v_message_name);
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228
229
230 END IF;
231
232
233 END AfterRowUpdate2;
234
235 -- Trigger description :-
236 -- "OSS_TST".trg_ai_as_u
237 -- AFTER UPDATE
238 -- ON IGS_AS_ASSESSMNT_ITM
239
240
241
242 PROCEDURE Check_Parent_Existance as
243 BEGIN
244
245 IF (((old_references.assessment_type = new_references.assessment_type)) OR
246 ((new_references.assessment_type IS NULL))) THEN
247 NULL;
248 ELSE
249 IF NOT(IGS_AS_ASSESSMNT_TYP_PKG.Get_PK_For_Validation (
250 new_references.assessment_type
251 ))THEN
252 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256
257 END IF;
258
259 END Check_Parent_Existance;
260
261 PROCEDURE Check_Constraints (
262 Column_Name IN VARCHAR2 DEFAULT NULL,
263 Column_Value IN VARCHAR2 DEFAULT NULL
264 ) as
265 BEGIN
266 IF column_name is null then
267 NULL;
268 ELSIF upper(Column_name) = 'ASSESSMENT_TYPE' then
269 new_references.assessment_type:= column_value;
270 ELSIF upper(Column_name) = 'ASS_LENGTH_OR_DURATION' then
271 new_references.ass_length_or_duration:= column_value;
272 ELSIF upper(Column_name) = 'EXAM_SCHEDULED_IND' then
273 new_references.exam_scheduled_ind:= column_value;
274 END IF;
275 IF upper(column_name) = 'ASSESSMENT_TYPE' OR
276 column_name is null Then
277 IF new_references.assessment_type <> UPPER(new_references.assessment_type) Then
278 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 END IF;
283
284 IF upper(column_name) = 'ASS_LENGTH_OR_DURATION' OR
285 column_name is null Then
286 IF new_references.ass_length_or_duration <> UPPER(new_references.ass_length_or_duration) Then
287 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 END IF;
291 END IF;
292
293 IF upper(column_name) = 'EXAM_SCHEDULED_IND' OR
294 column_name is null Then
295 IF new_references.exam_scheduled_ind <> UPPER(new_references.exam_scheduled_ind) OR new_references.exam_scheduled_ind NOT IN ( 'Y' , 'N' ) Then
296 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF;
300 END IF;
301
302 END Check_Constraints;
303
304
305
306 PROCEDURE Check_Child_Existance as
307 BEGIN
308
309 IGS_AS_COURSE_TYPE_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
310 old_references.ass_id
311 );
312
313 IGS_AS_ITEM_ASSESSOR_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
314 old_references.ass_id
315 );
316
317 IGS_AS_ITM_EXAM_MTRL_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
318 old_references.ass_id
319 );
320
321 IGS_AS_EXAM_INSTANCE_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
322 old_references.ass_id
323 );
324
325 IGS_AS_SU_ATMPT_ITM_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
326 old_references.ass_id
327 );
328
329 IGS_AS_UNITASS_ITEM_PKG.GET_FK_IGS_AS_ASSESSMNT_ITM (
330 old_references.ass_id
331 );
332
333 END Check_Child_Existance;
334
335 FUNCTION Get_PK_For_Validation (
336 x_ass_id IN NUMBER
337 ) RETURN BOOLEAN AS
338 CURSOR cur_rowid IS
339 SELECT rowid
340 FROM IGS_AS_ASSESSMNT_ITM_ALL
341 WHERE ass_id = x_ass_id;
342
343 lv_rowid cur_rowid%RowType;
344
345 BEGIN
346
347 Open cur_rowid;
348 Fetch cur_rowid INTO lv_rowid;
349 IF (cur_rowid%FOUND) THEN
350 Close cur_rowid;
351 Return (TRUE);
352 ELSE
353 Close cur_rowid;
354 Return (FALSE);
355 END IF;
356
357 END Get_PK_For_Validation;
358
359 PROCEDURE GET_FK_IGS_AS_ASSESSMNT_TYP (
360 x_assessment_type IN VARCHAR2
361 ) as
362
363 CURSOR cur_rowid IS
364 SELECT rowid
365 FROM IGS_AS_ASSESSMNT_ITM_ALL
366 WHERE assessment_type = x_assessment_type ;
367
368 lv_rowid cur_rowid%RowType;
369
370 BEGIN
371
372 Open cur_rowid;
373 Fetch cur_rowid INTO lv_rowid;
374 IF (cur_rowid%FOUND) THEN
375 Fnd_Message.Set_Name ('IGS', 'IGS_AS_AI_ATYP_FK');
376 IGS_GE_MSG_STACK.ADD;
377 Close cur_rowid;
378 App_Exception.Raise_Exception;
379 Return;
380 END IF;
381 Close cur_rowid;
382
383 END GET_FK_IGS_AS_ASSESSMNT_TYP;
384
385 PROCEDURE Before_DML (
386 p_action IN VARCHAR2,
387 x_rowid IN VARCHAR2 DEFAULT NULL,
388 x_org_id IN NUMBER DEFAULT NULL,
389 x_ass_id IN NUMBER DEFAULT NULL,
390 x_assessment_type IN VARCHAR2 DEFAULT NULL,
391 x_description IN VARCHAR2 DEFAULT NULL,
392 x_exam_scheduled_ind IN VARCHAR2 DEFAULT NULL,
393 x_exam_working_time IN DATE DEFAULT NULL,
394 x_exam_announcements IN VARCHAR2 DEFAULT NULL,
395 x_exam_short_paper_name IN VARCHAR2 DEFAULT NULL,
396 x_exam_paper_name IN VARCHAR2 DEFAULT NULL,
397 x_exam_perusal_time IN DATE DEFAULT NULL,
398 x_exam_supervisor_instrctn IN VARCHAR2 DEFAULT NULL,
399 x_exam_allowable_instrctn IN VARCHAR2 DEFAULT NULL,
400 x_exam_non_allowed_instrctn IN VARCHAR2 DEFAULT NULL,
401 x_exam_supplied_instrctn IN VARCHAR2 DEFAULT NULL,
402 x_question_or_title IN VARCHAR2 DEFAULT NULL,
403 x_ass_length_or_duration IN VARCHAR2 DEFAULT NULL,
404 x_comments IN VARCHAR2 DEFAULT NULL,
405 x_exam_constraints IN VARCHAR2 DEFAULT NULL,
406 x_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
410 x_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
407 x_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
408 x_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
409 x_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
411 x_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
412 x_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
413 x_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
414 x_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
415 x_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
416 x_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
417 x_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
418 x_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
419 x_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
420 x_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
421 x_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
422 x_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
423 x_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
424 x_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
425 x_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
426 x_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL ,
427 x_creation_date IN DATE DEFAULT NULL,
428 x_created_by IN NUMBER DEFAULT NULL,
429 x_last_update_date IN DATE DEFAULT NULL,
430 x_last_updated_by IN NUMBER DEFAULT NULL,
431 x_last_update_login IN NUMBER DEFAULT NULL,
432 -- anilk, bug#2784198
433 x_closed_ind IN VARCHAR2 DEFAULT NULL
434
435 ) as
436 BEGIN
437
438 Set_Column_Values (
439 p_action,
440 x_rowid,
441 x_org_id,
442 x_ass_id,
443 x_assessment_type,
444 x_description,
445 x_exam_scheduled_ind,
446 x_exam_working_time,
447 x_exam_announcements,
448 x_exam_short_paper_name,
449 x_exam_paper_name,
450 x_exam_perusal_time,
451 x_exam_supervisor_instrctn,
452 x_exam_allowable_instrctn,
453 x_exam_non_allowed_instrctn,
454 x_exam_supplied_instrctn,
455 x_question_or_title,
456 x_ass_length_or_duration,
457 x_comments,
458 x_exam_constraints,
459 x_attribute_category,
460 x_attribute1,
461 x_attribute2,
462 x_attribute3,
463 x_attribute4,
464 x_attribute5,
465 x_attribute6,
466 x_attribute7,
467 x_attribute8,
468 x_attribute9,
469 x_attribute10,
470 x_attribute11,
471 x_attribute12,
472 x_attribute13,
473 x_attribute14,
474 x_attribute15,
475 x_attribute16,
476 x_attribute17,
477 x_attribute18,
478 x_attribute19,
479 x_attribute20,
480 x_creation_date,
481 x_created_by,
482 x_last_update_date,
483 x_last_updated_by,
484 x_last_update_login,
485 -- anilk, bug#2784198
486 x_closed_ind
487
488 );
489
490 IF (p_action = 'INSERT') THEN
491 -- Call all the procedures related to Before Insert.
492 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
493 IF Get_PK_For_Validation (
494 new_references.ass_id
495 ) THEN
496 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 END IF;
500
501 Check_Constraints;
502 Check_Parent_Existance;
503 ELSIF (p_action = 'UPDATE') THEN
504 -- Call all the procedures related to Before Update.
505 BeforeRowInsertUpdate1 ( p_updating => TRUE );
506 Check_Constraints;
507 Check_Parent_Existance;
508 ELSIF (p_action = 'DELETE') THEN
509 -- Call all the procedures related to Before Delete.
510 Null;
511 Check_Child_Existance;
512 ELSIF (p_action = 'VALIDATE_INSERT') THEN
513 IF Get_PK_For_Validation (
514 new_references.ass_id
515 ) THEN
516 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
517 IGS_GE_MSG_STACK.ADD;
518 App_Exception.Raise_Exception;
519 END IF;
520 Check_Constraints;
521 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
522 Check_Constraints;
523 ELSIF (p_action = 'VALIDATE_DELETE') THEN
524 Check_Child_Existance;
525
526 END IF;
527
528 END Before_DML;
529
530
531 procedure INSERT_ROW (
532 X_ROWID in out NOCOPY VARCHAR2,
533 X_ORG_ID in NUMBER,
534 X_ASS_ID in NUMBER,
535 X_ASSESSMENT_TYPE in VARCHAR2,
536 X_DESCRIPTION in VARCHAR2,
537 X_EXAM_SHORT_PAPER_NAME in VARCHAR2,
538 X_EXAM_PAPER_NAME in VARCHAR2,
539 X_EXAM_WORKING_TIME in DATE,
540 X_EXAM_PERUSAL_TIME in DATE,
541 X_EXAM_SCHEDULED_IND in VARCHAR2,
542 X_EXAM_SUPERVISOR_INSTRCTN in VARCHAR2,
543 X_EXAM_ANNOUNCEMENTS in VARCHAR2,
544 X_EXAM_ALLOWABLE_INSTRCTN in VARCHAR2,
545 X_EXAM_NON_ALLOWED_INSTRCTN in VARCHAR2,
546 X_EXAM_SUPPLIED_INSTRCTN in VARCHAR2,
547 X_EXAM_CONSTRAINTS in VARCHAR2,
548 X_QUESTION_OR_TITLE in VARCHAR2,
549 X_ASS_LENGTH_OR_DURATION in VARCHAR2,
550 X_COMMENTS in VARCHAR2,
554 x_attribute3 IN VARCHAR2,
551 x_attribute_category IN VARCHAR2,
552 x_attribute1 IN VARCHAR2,
553 x_attribute2 IN VARCHAR2,
555 x_attribute4 IN VARCHAR2,
556 x_attribute5 IN VARCHAR2,
557 x_attribute6 IN VARCHAR2,
558 x_attribute7 IN VARCHAR2,
559 x_attribute8 IN VARCHAR2,
560 x_attribute9 IN VARCHAR2,
561 x_attribute10 IN VARCHAR2,
562 x_attribute11 IN VARCHAR2,
563 x_attribute12 IN VARCHAR2,
564 x_attribute13 IN VARCHAR2,
565 x_attribute14 IN VARCHAR2,
566 x_attribute15 IN VARCHAR2,
567 x_attribute16 IN VARCHAR2,
568 x_attribute17 IN VARCHAR2,
569 x_attribute18 IN VARCHAR2,
570 x_attribute19 IN VARCHAR2,
571 x_attribute20 IN VARCHAR2,
572 X_MODE in VARCHAR2 default 'R',
573 -- anilk, bug#2784198
574 x_closed_ind IN VARCHAR2
575 ) as
576 cursor C is select ROWID from IGS_AS_ASSESSMNT_ITM_all
577 where ASS_ID = X_ASS_ID;
578 X_LAST_UPDATE_DATE DATE;
579 X_LAST_UPDATED_BY NUMBER;
580 X_LAST_UPDATE_LOGIN NUMBER;
581 begin
582 X_LAST_UPDATE_DATE := SYSDATE;
583 if(X_MODE = 'I') then
584 X_LAST_UPDATED_BY := 1;
585 X_LAST_UPDATE_LOGIN := 0;
586 elsif (X_MODE = 'R') then
587 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
588 if X_LAST_UPDATED_BY is NULL then
589 X_LAST_UPDATED_BY := -1;
590 end if;
591 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
592 if X_LAST_UPDATE_LOGIN is NULL then
593 X_LAST_UPDATE_LOGIN := -1;
594 end if;
595 else
596 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 end if;
600 Before_DML(
601 p_action=>'INSERT',
602 x_rowid=>X_ROWID,
603 x_org_id => igs_ge_gen_003.get_org_id,
604 x_ass_id=>X_ASS_ID,
605 x_ass_length_or_duration=>X_ASS_LENGTH_OR_DURATION,
606 x_assessment_type=>X_ASSESSMENT_TYPE,
607 x_comments=>X_COMMENTS,
608 x_description=>X_DESCRIPTION,
609 x_exam_allowable_instrctn=>X_EXAM_ALLOWABLE_INSTRCTN,
610 x_exam_announcements=>X_EXAM_ANNOUNCEMENTS,
611 x_exam_constraints=>X_EXAM_CONSTRAINTS,
612 x_exam_non_allowed_instrctn=>X_EXAM_NON_ALLOWED_INSTRCTN,
613 x_exam_paper_name=>X_EXAM_PAPER_NAME,
614 x_exam_perusal_time=>X_EXAM_PERUSAL_TIME,
615 x_exam_scheduled_ind=>X_EXAM_SCHEDULED_IND,
616 x_exam_short_paper_name=>X_EXAM_SHORT_PAPER_NAME,
617 x_exam_supervisor_instrctn=>X_EXAM_SUPERVISOR_INSTRCTN,
618 x_exam_supplied_instrctn=>X_EXAM_SUPPLIED_INSTRCTN,
619 x_exam_working_time=>X_EXAM_WORKING_TIME,
620 x_question_or_title=>X_QUESTION_OR_TITLE,
621 x_attribute_category=>x_attribute_category,
622 x_attribute1=>x_attribute1,
623 x_attribute2=>x_attribute2,
624 x_attribute3=>x_attribute3,
625 x_attribute4=>x_attribute4,
626 x_attribute5=>x_attribute5,
627 x_attribute6=>x_attribute6,
628 x_attribute7=>x_attribute7,
629 x_attribute8=>x_attribute8,
630 x_attribute9=>x_attribute9,
631 x_attribute10=>x_attribute10,
632 x_attribute11=>x_attribute11,
633 x_attribute12=>x_attribute12,
634 x_attribute13=>x_attribute13,
635 x_attribute14=>x_attribute14,
636 x_attribute15=>x_attribute15,
637 x_attribute16=>x_attribute16,
638 x_attribute17=>x_attribute17,
639 x_attribute18=>x_attribute18,
640 x_attribute19=>x_attribute19,
641 x_attribute20=>x_attribute20,
642 x_creation_date=>X_LAST_UPDATE_DATE,
643 x_created_by=>X_LAST_UPDATED_BY,
644 x_last_update_date=>X_LAST_UPDATE_DATE,
645 x_last_updated_by=>X_LAST_UPDATED_BY,
646 x_last_update_login=>X_LAST_UPDATE_LOGIN,
647 -- anilk, bug#2784198
648 x_closed_ind => x_closed_ind
649
650 );
651 insert into IGS_AS_ASSESSMNT_ITM_ALL (
652 ORG_ID,
653 ASS_ID,
654 ASSESSMENT_TYPE,
655 DESCRIPTION,
656 EXAM_SHORT_PAPER_NAME,
657 EXAM_PAPER_NAME,
658 EXAM_WORKING_TIME,
659 EXAM_PERUSAL_TIME,
660 EXAM_SCHEDULED_IND,
661 EXAM_SUPERVISOR_INSTRCTN,
662 EXAM_ANNOUNCEMENTS,
663 EXAM_ALLOWABLE_INSTRCTN,
664 EXAM_NON_ALLOWED_INSTRCTN,
665 EXAM_SUPPLIED_INSTRCTN,
666 EXAM_CONSTRAINTS,
667 QUESTION_OR_TITLE,
668 ASS_LENGTH_OR_DURATION,
669 COMMENTS,
670 attribute_category,
671 attribute1,
672 attribute2,
673 attribute3,
674 attribute4,
675 attribute5,
676 attribute6,
677 attribute7,
678 attribute8,
679 attribute9,
680 attribute10,
681 attribute11,
682 attribute12,
683 attribute13,
684 attribute14,
685 attribute15,
686 attribute16,
687 attribute17,
688 attribute18,
689 attribute19,
690 attribute20,
691 CREATION_DATE,
692 CREATED_BY,
693 LAST_UPDATE_DATE,
694 LAST_UPDATED_BY,
695 LAST_UPDATE_LOGIN,
696 -- anilk, bug#2784198
697 closed_ind
698 ) values (
699 NEW_REFERENCES.ORG_ID,
700 NEW_REFERENCES.ASS_ID,
701 NEW_REFERENCES.ASSESSMENT_TYPE,
702 NEW_REFERENCES.DESCRIPTION,
703 NEW_REFERENCES.EXAM_SHORT_PAPER_NAME,
704 NEW_REFERENCES.EXAM_PAPER_NAME,
708 NEW_REFERENCES.EXAM_SUPERVISOR_INSTRCTN,
705 NEW_REFERENCES.EXAM_WORKING_TIME,
706 NEW_REFERENCES.EXAM_PERUSAL_TIME,
707 NEW_REFERENCES.EXAM_SCHEDULED_IND,
709 NEW_REFERENCES.EXAM_ANNOUNCEMENTS,
710 NEW_REFERENCES.EXAM_ALLOWABLE_INSTRCTN,
711 NEW_REFERENCES.EXAM_NON_ALLOWED_INSTRCTN,
712 NEW_REFERENCES.EXAM_SUPPLIED_INSTRCTN,
713 NEW_REFERENCES.EXAM_CONSTRAINTS,
714 NEW_REFERENCES.QUESTION_OR_TITLE,
715 NEW_REFERENCES.ASS_LENGTH_OR_DURATION,
716 NEW_REFERENCES.COMMENTS,
717 new_references.attribute_category,
718 new_references.attribute1,
719 new_references.attribute2,
720 new_references.attribute3,
721 new_references.attribute4,
722 new_references.attribute5,
723 new_references.attribute6,
724 new_references.attribute7,
725 new_references.attribute8,
726 new_references.attribute9,
727 new_references.attribute10,
728 new_references.attribute11,
729 new_references.attribute12,
730 new_references.attribute13,
731 new_references.attribute14,
732 new_references.attribute15,
733 new_references.attribute16,
734 new_references.attribute17,
735 new_references.attribute18,
736 new_references.attribute19,
737 new_references.attribute20,
738 X_LAST_UPDATE_DATE,
739 X_LAST_UPDATED_BY,
740 X_LAST_UPDATE_DATE,
741 X_LAST_UPDATED_BY,
742 X_LAST_UPDATE_LOGIN,
743 -- anilk, bug#2784198
744 new_references.closed_ind
745 );
746
747 open c;
748 fetch c into X_ROWID;
749 if (c%notfound) then
750 close c;
751 raise no_data_found;
752 end if;
753 close c;
754
755
756 end INSERT_ROW;
757
758 procedure LOCK_ROW (
759 X_ROWID in VARCHAR2,
760 X_ASS_ID in NUMBER,
761 X_ASSESSMENT_TYPE in VARCHAR2,
762 X_DESCRIPTION in VARCHAR2,
763 X_EXAM_SHORT_PAPER_NAME in VARCHAR2,
764 X_EXAM_PAPER_NAME in VARCHAR2,
765 X_EXAM_WORKING_TIME in DATE,
766 X_EXAM_PERUSAL_TIME in DATE,
767 X_EXAM_SCHEDULED_IND in VARCHAR2,
768 X_EXAM_SUPERVISOR_INSTRCTN in VARCHAR2,
769 X_EXAM_ANNOUNCEMENTS in VARCHAR2,
770 X_EXAM_ALLOWABLE_INSTRCTN in VARCHAR2,
771 X_EXAM_NON_ALLOWED_INSTRCTN in VARCHAR2,
772 X_EXAM_SUPPLIED_INSTRCTN in VARCHAR2,
773 X_EXAM_CONSTRAINTS in VARCHAR2,
774 X_QUESTION_OR_TITLE in VARCHAR2,
775 X_ASS_LENGTH_OR_DURATION in VARCHAR2,
776 X_COMMENTS in VARCHAR2,
777 x_attribute_category IN VARCHAR2,
778 x_attribute1 IN VARCHAR2,
779 x_attribute2 IN VARCHAR2,
780 x_attribute3 IN VARCHAR2,
781 x_attribute4 IN VARCHAR2,
782 x_attribute5 IN VARCHAR2,
783 x_attribute6 IN VARCHAR2,
784 x_attribute7 IN VARCHAR2,
785 x_attribute8 IN VARCHAR2,
786 x_attribute9 IN VARCHAR2,
787 x_attribute10 IN VARCHAR2,
788 x_attribute11 IN VARCHAR2,
789 x_attribute12 IN VARCHAR2,
790 x_attribute13 IN VARCHAR2,
791 x_attribute14 IN VARCHAR2,
792 x_attribute15 IN VARCHAR2,
793 x_attribute16 IN VARCHAR2,
794 x_attribute17 IN VARCHAR2,
795 x_attribute18 IN VARCHAR2,
796 x_attribute19 IN VARCHAR2,
797 x_attribute20 IN VARCHAR2,
798 -- anilk, bug#2784198
799 x_closed_ind IN VARCHAR2
800 ) as
801 cursor c1 is select
802 ASSESSMENT_TYPE,
803 DESCRIPTION,
804 EXAM_SHORT_PAPER_NAME,
805 EXAM_PAPER_NAME,
806 EXAM_WORKING_TIME,
807 EXAM_PERUSAL_TIME,
808 EXAM_SCHEDULED_IND,
809 EXAM_SUPERVISOR_INSTRCTN,
810 EXAM_ANNOUNCEMENTS,
811 EXAM_ALLOWABLE_INSTRCTN,
812 EXAM_NON_ALLOWED_INSTRCTN,
813 EXAM_SUPPLIED_INSTRCTN,
814 EXAM_CONSTRAINTS,
815 QUESTION_OR_TITLE,
816 ASS_LENGTH_OR_DURATION,
817 COMMENTS,
818 attribute_category,
819 attribute1,
820 attribute2,
821 attribute3,
822 attribute4,
823 attribute5,
824 attribute6,
825 attribute7,
826 attribute8,
827 attribute9,
828 attribute10,
829 attribute11,
830 attribute12,
831 attribute13,
832 attribute14,
833 attribute15,
834 attribute16,
835 attribute17,
836 attribute18,
837 attribute19,
838 attribute20,
839 -- anilk, bug#2784198
840 closed_ind
841 from IGS_AS_ASSESSMNT_ITM_ALL
842
843 where ROWID = X_ROWID for update nowait;
844 tlinfo c1%rowtype;
845
846 begin
847 open c1;
848 fetch c1 into tlinfo;
849 if (c1%notfound) then
850 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
851 IGS_GE_MSG_STACK.ADD;
852 close c1;
853 app_exception.raise_exception;
854 return;
855 end if;
856 close c1;
857
858 if ( (tlinfo.ASSESSMENT_TYPE = X_ASSESSMENT_TYPE)
859 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
860 AND ((tlinfo.EXAM_SHORT_PAPER_NAME = X_EXAM_SHORT_PAPER_NAME)
861 OR ((tlinfo.EXAM_SHORT_PAPER_NAME is null)
862 AND (X_EXAM_SHORT_PAPER_NAME is null)))
866 AND ((tlinfo.EXAM_WORKING_TIME = X_EXAM_WORKING_TIME)
863 AND ((tlinfo.EXAM_PAPER_NAME = X_EXAM_PAPER_NAME)
864 OR ((tlinfo.EXAM_PAPER_NAME is null)
865 AND (X_EXAM_PAPER_NAME is null)))
867 OR ((tlinfo.EXAM_WORKING_TIME is null)
868 AND (X_EXAM_WORKING_TIME is null)))
869 AND ((tlinfo.EXAM_PERUSAL_TIME = X_EXAM_PERUSAL_TIME)
870 OR ((tlinfo.EXAM_PERUSAL_TIME is null)
871 AND (X_EXAM_PERUSAL_TIME is null)))
872 AND (tlinfo.EXAM_SCHEDULED_IND = X_EXAM_SCHEDULED_IND)
873 AND ((tlinfo.EXAM_SUPERVISOR_INSTRCTN = X_EXAM_SUPERVISOR_INSTRCTN)
874 OR ((tlinfo.EXAM_SUPERVISOR_INSTRCTN is null)
875 AND (X_EXAM_SUPERVISOR_INSTRCTN is null)))
876 AND ((tlinfo.EXAM_ANNOUNCEMENTS = X_EXAM_ANNOUNCEMENTS)
877 OR ((tlinfo.EXAM_ANNOUNCEMENTS is null)
878 AND (X_EXAM_ANNOUNCEMENTS is null)))
879 AND ((tlinfo.EXAM_ALLOWABLE_INSTRCTN = X_EXAM_ALLOWABLE_INSTRCTN)
880 OR ((tlinfo.EXAM_ALLOWABLE_INSTRCTN is null)
881 AND (X_EXAM_ALLOWABLE_INSTRCTN is null)))
882 AND ((tlinfo.EXAM_NON_ALLOWED_INSTRCTN = X_EXAM_NON_ALLOWED_INSTRCTN)
883 OR ((tlinfo.EXAM_NON_ALLOWED_INSTRCTN is null)
884 AND (X_EXAM_NON_ALLOWED_INSTRCTN is null)))
885 AND ((tlinfo.EXAM_SUPPLIED_INSTRCTN = X_EXAM_SUPPLIED_INSTRCTN)
886 OR ((tlinfo.EXAM_SUPPLIED_INSTRCTN is null)
887 AND (X_EXAM_SUPPLIED_INSTRCTN is null)))
888 AND ((tlinfo.EXAM_CONSTRAINTS = X_EXAM_CONSTRAINTS)
889 OR ((tlinfo.EXAM_CONSTRAINTS is null)
890 AND (X_EXAM_CONSTRAINTS is null)))
891 AND ((tlinfo.QUESTION_OR_TITLE = X_QUESTION_OR_TITLE)
892 OR ((tlinfo.QUESTION_OR_TITLE is null)
893 AND (X_QUESTION_OR_TITLE is null)))
894 AND ((tlinfo.ASS_LENGTH_OR_DURATION = X_ASS_LENGTH_OR_DURATION)
895 OR ((tlinfo.ASS_LENGTH_OR_DURATION is null)
896 AND (X_ASS_LENGTH_OR_DURATION is null)))
897 AND ((tlinfo.COMMENTS = X_COMMENTS)
898 OR ((tlinfo.COMMENTS is null)
899 AND (X_COMMENTS is null)))
900 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL)))
901 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL)))
902 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL)))
903 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (x_attribute3 IS NULL)))
904 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (x_attribute4 IS NULL)))
905 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (x_attribute5 IS NULL)))
906 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (x_attribute6 IS NULL)))
907 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (x_attribute7 IS NULL)))
908 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (x_attribute8 IS NULL)))
909 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (x_attribute9 IS NULL)))
910 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (x_attribute10 IS NULL)))
911 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (x_attribute11 IS NULL)))
912 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (x_attribute12 IS NULL)))
913 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (x_attribute13 IS NULL)))
914 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (x_attribute14 IS NULL)))
915 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (x_attribute15 IS NULL)))
916 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (x_attribute16 IS NULL)))
917 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (x_attribute17 IS NULL)))
918 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (x_attribute18 IS NULL)))
919 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (x_attribute19 IS NULL)))
920 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (x_attribute20 IS NULL)))
921
922 -- anilk, bug#2784198
923 AND ((tlinfo.closed_ind = x_closed_ind)
924 OR ((tlinfo.closed_ind IS NULL)
925 AND (x_closed_ind IS NULL)))
926
927 ) then
928 null;
929 else
930 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
931 IGS_GE_MSG_STACK.ADD;
932 app_exception.raise_exception;
933 end if;
934 return;
935 end LOCK_ROW;
936
937 procedure UPDATE_ROW (
938 X_ROWID in VARCHAR2,
939 X_ASS_ID in NUMBER,
940 X_ASSESSMENT_TYPE in VARCHAR2,
941 X_DESCRIPTION in VARCHAR2,
942 X_EXAM_SHORT_PAPER_NAME in VARCHAR2,
943 X_EXAM_PAPER_NAME in VARCHAR2,
944 X_EXAM_WORKING_TIME in DATE,
945 X_EXAM_PERUSAL_TIME in DATE,
946 X_EXAM_SCHEDULED_IND in VARCHAR2,
947 X_EXAM_SUPERVISOR_INSTRCTN in VARCHAR2,
948 X_EXAM_ANNOUNCEMENTS in VARCHAR2,
949 X_EXAM_ALLOWABLE_INSTRCTN in VARCHAR2,
950 X_EXAM_NON_ALLOWED_INSTRCTN in VARCHAR2,
951 X_EXAM_SUPPLIED_INSTRCTN in VARCHAR2,
955 X_COMMENTS in VARCHAR2,
952 X_EXAM_CONSTRAINTS in VARCHAR2,
953 X_QUESTION_OR_TITLE in VARCHAR2,
954 X_ASS_LENGTH_OR_DURATION in VARCHAR2,
956 x_attribute_category IN VARCHAR2,
957 x_attribute1 IN VARCHAR2,
958 x_attribute2 IN VARCHAR2,
959 x_attribute3 IN VARCHAR2,
960 x_attribute4 IN VARCHAR2,
961 x_attribute5 IN VARCHAR2,
962 x_attribute6 IN VARCHAR2,
963 x_attribute7 IN VARCHAR2,
964 x_attribute8 IN VARCHAR2,
965 x_attribute9 IN VARCHAR2,
966 x_attribute10 IN VARCHAR2,
967 x_attribute11 IN VARCHAR2,
968 x_attribute12 IN VARCHAR2,
969 x_attribute13 IN VARCHAR2,
970 x_attribute14 IN VARCHAR2,
971 x_attribute15 IN VARCHAR2,
972 x_attribute16 IN VARCHAR2,
973 x_attribute17 IN VARCHAR2,
974 x_attribute18 IN VARCHAR2,
975 x_attribute19 IN VARCHAR2,
976 x_attribute20 IN VARCHAR2,
977 X_MODE in VARCHAR2 default 'R',
978 -- anilk, bug#2784198
979 x_closed_ind IN VARCHAR2
980 ) as
981 X_LAST_UPDATE_DATE DATE;
982 X_LAST_UPDATED_BY NUMBER;
983 X_LAST_UPDATE_LOGIN NUMBER;
984 begin
985 X_LAST_UPDATE_DATE := SYSDATE;
986 if(X_MODE = 'I') then
987 X_LAST_UPDATED_BY := 1;
988 X_LAST_UPDATE_LOGIN := 0;
989 elsif (X_MODE = 'R') then
990 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
991 if X_LAST_UPDATED_BY is NULL then
992 X_LAST_UPDATED_BY := -1;
993 end if;
994 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
995 if X_LAST_UPDATE_LOGIN is NULL then
996 X_LAST_UPDATE_LOGIN := -1;
997 end if;
998 else
999 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1000 IGS_GE_MSG_STACK.ADD;
1001 app_exception.raise_exception;
1002 end if;
1003 Before_DML(
1004 p_action=>'UPDATE',
1005 x_rowid=>X_ROWID,
1006 x_ass_id=>X_ASS_ID,
1007 x_ass_length_or_duration=>X_ASS_LENGTH_OR_DURATION,
1008 x_assessment_type=>X_ASSESSMENT_TYPE,
1009 x_comments=>X_COMMENTS,
1010 x_description=>X_DESCRIPTION,
1011 x_exam_allowable_instrctn=>X_EXAM_ALLOWABLE_INSTRCTN,
1012 x_exam_announcements=>X_EXAM_ANNOUNCEMENTS,
1013 x_exam_constraints=>X_EXAM_CONSTRAINTS,
1014 x_exam_non_allowed_instrctn=>X_EXAM_NON_ALLOWED_INSTRCTN,
1015 x_exam_paper_name=>X_EXAM_PAPER_NAME,
1016 x_exam_perusal_time=>X_EXAM_PERUSAL_TIME,
1017 x_exam_scheduled_ind=>X_EXAM_SCHEDULED_IND,
1018 x_exam_short_paper_name=>X_EXAM_SHORT_PAPER_NAME,
1019 x_exam_supervisor_instrctn=>X_EXAM_SUPERVISOR_INSTRCTN,
1020 x_exam_supplied_instrctn=>X_EXAM_SUPPLIED_INSTRCTN,
1021 x_exam_working_time=>X_EXAM_WORKING_TIME,
1022 x_attribute_category=>x_attribute_category,
1023 x_attribute1=>x_attribute1,
1024 x_attribute2=>x_attribute2,
1025 x_attribute3=>x_attribute3,
1026 x_attribute4=>x_attribute4,
1027 x_attribute5=>x_attribute5,
1028 x_attribute6=>x_attribute6,
1029 x_attribute7=>x_attribute7,
1030 x_attribute8=>x_attribute8,
1031 x_attribute9=>x_attribute9,
1032 x_attribute10=>x_attribute10,
1033 x_attribute11=>x_attribute11,
1034 x_attribute12=>x_attribute12,
1035 x_attribute13=>x_attribute13,
1036 x_attribute14=>x_attribute14,
1037 x_attribute15=>x_attribute15,
1038 x_attribute16=>x_attribute16,
1039 x_attribute17=>x_attribute17,
1040 x_attribute18=>x_attribute18,
1041 x_attribute19=>x_attribute19,
1042 x_attribute20=>x_attribute20,
1043 x_question_or_title=>X_QUESTION_OR_TITLE,
1044 x_creation_date=>X_LAST_UPDATE_DATE,
1045 x_created_by=>X_LAST_UPDATED_BY,
1046 x_last_update_date=>X_LAST_UPDATE_DATE,
1047 x_last_updated_by=>X_LAST_UPDATED_BY,
1048 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1049 -- anilk, bug#2784198
1050 x_closed_ind => x_closed_ind
1051 );
1052
1053 update IGS_AS_ASSESSMNT_ITM_ALL set
1054 ASSESSMENT_TYPE = NEW_REFERENCES.ASSESSMENT_TYPE,
1055 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1056 EXAM_SHORT_PAPER_NAME = NEW_REFERENCES.EXAM_SHORT_PAPER_NAME,
1057 EXAM_PAPER_NAME = NEW_REFERENCES.EXAM_PAPER_NAME,
1058 EXAM_WORKING_TIME = NEW_REFERENCES.EXAM_WORKING_TIME,
1059 EXAM_PERUSAL_TIME = NEW_REFERENCES.EXAM_PERUSAL_TIME,
1060 EXAM_SCHEDULED_IND = NEW_REFERENCES.EXAM_SCHEDULED_IND,
1061 EXAM_SUPERVISOR_INSTRCTN = NEW_REFERENCES.EXAM_SUPERVISOR_INSTRCTN,
1062 EXAM_ANNOUNCEMENTS = NEW_REFERENCES.EXAM_ANNOUNCEMENTS,
1063 EXAM_ALLOWABLE_INSTRCTN = NEW_REFERENCES.EXAM_ALLOWABLE_INSTRCTN,
1064 EXAM_NON_ALLOWED_INSTRCTN = NEW_REFERENCES.EXAM_NON_ALLOWED_INSTRCTN,
1065 EXAM_SUPPLIED_INSTRCTN = NEW_REFERENCES.EXAM_SUPPLIED_INSTRCTN,
1066 EXAM_CONSTRAINTS = NEW_REFERENCES.EXAM_CONSTRAINTS,
1067 QUESTION_OR_TITLE = NEW_REFERENCES.QUESTION_OR_TITLE,
1068 ASS_LENGTH_OR_DURATION = NEW_REFERENCES.ASS_LENGTH_OR_DURATION,
1069 COMMENTS = NEW_REFERENCES.COMMENTS,
1070 attribute_category = new_references.attribute_category,
1071 attribute1 = new_references.attribute1,
1072 attribute2 = new_references.attribute2,
1073 attribute3 = new_references.attribute3,
1074 attribute4 = new_references.attribute4,
1075 attribute5 = new_references.attribute5,
1076 attribute6 = new_references.attribute6,
1077 attribute7 = new_references.attribute7,
1078 attribute8 = new_references.attribute8,
1079 attribute9 = new_references.attribute9,
1080 attribute10 = new_references.attribute10,
1081 attribute11 = new_references.attribute11,
1082 attribute12 = new_references.attribute12,
1086 attribute16 = new_references.attribute16,
1083 attribute13 = new_references.attribute13,
1084 attribute14 = new_references.attribute14,
1085 attribute15 = new_references.attribute15,
1087 attribute17 = new_references.attribute17,
1088 attribute18 = new_references.attribute18,
1089 attribute19 = new_references.attribute19,
1090 attribute20 = new_references.attribute20,
1091 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1092 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1093 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1094 -- anilk, bug#2784198
1095 closed_ind = new_references.closed_ind
1096 where ROWID = X_ROWID;
1097 if (sql%notfound) then
1098 raise no_data_found;
1099 end if;
1100
1101 end UPDATE_ROW;
1102
1103 procedure ADD_ROW (
1104 X_ROWID in out NOCOPY VARCHAR2,
1105 X_ORG_ID in NUMBER,
1106 X_ASS_ID in NUMBER,
1107 X_ASSESSMENT_TYPE in VARCHAR2,
1108 X_DESCRIPTION in VARCHAR2,
1109 X_EXAM_SHORT_PAPER_NAME in VARCHAR2,
1110 X_EXAM_PAPER_NAME in VARCHAR2,
1111 X_EXAM_WORKING_TIME in DATE,
1112 X_EXAM_PERUSAL_TIME in DATE,
1113 X_EXAM_SCHEDULED_IND in VARCHAR2,
1114 X_EXAM_SUPERVISOR_INSTRCTN in VARCHAR2,
1115 X_EXAM_ANNOUNCEMENTS in VARCHAR2,
1116 X_EXAM_ALLOWABLE_INSTRCTN in VARCHAR2,
1117 X_EXAM_NON_ALLOWED_INSTRCTN in VARCHAR2,
1118 X_EXAM_SUPPLIED_INSTRCTN in VARCHAR2,
1119 X_EXAM_CONSTRAINTS in VARCHAR2,
1120 X_QUESTION_OR_TITLE in VARCHAR2,
1121 X_ASS_LENGTH_OR_DURATION in VARCHAR2,
1122 X_COMMENTS in VARCHAR2,
1123 x_attribute_category IN VARCHAR2,
1124 x_attribute1 IN VARCHAR2,
1125 x_attribute2 IN VARCHAR2,
1126 x_attribute3 IN VARCHAR2,
1127 x_attribute4 IN VARCHAR2,
1128 x_attribute5 IN VARCHAR2,
1129 x_attribute6 IN VARCHAR2,
1130 x_attribute7 IN VARCHAR2,
1131 x_attribute8 IN VARCHAR2,
1132 x_attribute9 IN VARCHAR2,
1133 x_attribute10 IN VARCHAR2,
1134 x_attribute11 IN VARCHAR2,
1135 x_attribute12 IN VARCHAR2,
1136 x_attribute13 IN VARCHAR2,
1137 x_attribute14 IN VARCHAR2,
1138 x_attribute15 IN VARCHAR2,
1139 x_attribute16 IN VARCHAR2,
1140 x_attribute17 IN VARCHAR2,
1141 x_attribute18 IN VARCHAR2,
1142 x_attribute19 IN VARCHAR2,
1143 x_attribute20 IN VARCHAR2,
1144 X_MODE in VARCHAR2 default 'R',
1145 -- anilk, bug#2784198
1146 x_closed_ind IN VARCHAR2
1147 ) as
1148 cursor c1 is select rowid from IGS_AS_ASSESSMNT_ITM_ALL
1149 where ASS_ID = X_ASS_ID
1150 ;
1151 begin
1152 open c1;
1153 fetch c1 into X_ROWID;
1154 if (c1%notfound) then
1155 close c1;
1156 INSERT_ROW (
1157 X_ROWID,
1158 X_ORG_ID,
1159 X_ASS_ID,
1160 X_ASSESSMENT_TYPE,
1161 X_DESCRIPTION,
1162 X_EXAM_SHORT_PAPER_NAME,
1163 X_EXAM_PAPER_NAME,
1164 X_EXAM_WORKING_TIME,
1165 X_EXAM_PERUSAL_TIME,
1166 X_EXAM_SCHEDULED_IND,
1167 X_EXAM_SUPERVISOR_INSTRCTN,
1168 X_EXAM_ANNOUNCEMENTS,
1169 X_EXAM_ALLOWABLE_INSTRCTN,
1170 X_EXAM_NON_ALLOWED_INSTRCTN,
1171 X_EXAM_SUPPLIED_INSTRCTN,
1172 X_EXAM_CONSTRAINTS,
1173 X_QUESTION_OR_TITLE,
1174 X_ASS_LENGTH_OR_DURATION,
1175 X_COMMENTS,
1176 x_attribute_category,
1177 x_attribute1,
1178 x_attribute2,
1179 x_attribute3,
1180 x_attribute4,
1181 x_attribute5,
1182 x_attribute6,
1183 x_attribute7,
1184 x_attribute8,
1185 x_attribute9,
1186 x_attribute10,
1187 x_attribute11,
1188 x_attribute12,
1189 x_attribute13,
1190 x_attribute14,
1191 x_attribute15,
1192 x_attribute16,
1193 x_attribute17,
1194 x_attribute18,
1195 x_attribute19,
1196 x_attribute20,
1197 X_MODE,
1198 -- anilk, bug#2784198
1199 x_closed_ind
1200 );
1201 return;
1202 end if;
1203 close c1;
1204 UPDATE_ROW (
1205 X_ROWID,
1206 X_ASS_ID,
1207 X_ASSESSMENT_TYPE,
1208 X_DESCRIPTION,
1209 X_EXAM_SHORT_PAPER_NAME,
1210 X_EXAM_PAPER_NAME,
1211 X_EXAM_WORKING_TIME,
1212 X_EXAM_PERUSAL_TIME,
1213 X_EXAM_SCHEDULED_IND,
1214 X_EXAM_SUPERVISOR_INSTRCTN,
1215 X_EXAM_ANNOUNCEMENTS,
1216 X_EXAM_ALLOWABLE_INSTRCTN,
1217 X_EXAM_NON_ALLOWED_INSTRCTN,
1218 X_EXAM_SUPPLIED_INSTRCTN,
1219 X_EXAM_CONSTRAINTS,
1220 X_QUESTION_OR_TITLE,
1221 X_ASS_LENGTH_OR_DURATION,
1222 X_COMMENTS,
1223 x_attribute_category,
1224 x_attribute1,
1225 x_attribute2,
1226 x_attribute3,
1227 x_attribute4,
1228 x_attribute5,
1229 x_attribute6,
1230 x_attribute7,
1231 x_attribute8,
1232 x_attribute9,
1233 x_attribute10,
1234 x_attribute11,
1235 x_attribute12,
1236 x_attribute13,
1237 x_attribute14,
1238 x_attribute15,
1239 x_attribute16,
1240 x_attribute17,
1241 x_attribute18,
1242 x_attribute19,
1243 x_attribute20,
1244 X_MODE,
1245 -- anilk, bug#2784198
1246 x_closed_ind
1247 );
1248 end ADD_ROW;
1249
1250 end IGS_AS_ASSESSMNT_ITM_PKG;