eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_AS_ITM_EXAM_MTRL_PKG

Source


1 package body IGS_AS_ITM_EXAM_MTRL_PKG AS
2  /* $Header: IGSDI03B.pls 115.5 2003/05/19 09:56:07 ijeddy ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_AS_ITM_EXAM_MTRL%RowType;
5   new_references IGS_AS_ITM_EXAM_MTRL%RowType;
6   PROCEDURE Set_Column_Values (
7     p_action IN VARCHAR2,
8     x_rowid IN VARCHAR2 DEFAULT NULL,
9     x_ass_id IN NUMBER DEFAULT NULL,
10     x_exam_material_type IN VARCHAR2 DEFAULT NULL,
11     x_s_material_cat IN VARCHAR2 DEFAULT NULL,
12     x_quantity_per_student IN NUMBER DEFAULT NULL,
13     x_comments IN VARCHAR2 DEFAULT NULL,
14     x_creation_date IN DATE DEFAULT NULL,
15     x_created_by IN NUMBER DEFAULT NULL,
16     x_last_update_date IN DATE DEFAULT NULL,
17     x_last_updated_by IN NUMBER DEFAULT NULL,
18     x_last_update_login IN NUMBER DEFAULT NULL
19   ) AS
20     CURSOR cur_old_ref_values IS
21       SELECT   *
22       FROM     IGS_AS_ITM_EXAM_MTRL
23       WHERE    rowid = x_rowid;
24   BEGIN
25     l_rowid := x_rowid;
26     -- Code for setting the Old and New Reference Values.
27     -- Populate Old Values.
28     Open cur_old_ref_values;
29     Fetch cur_old_ref_values INTO old_references;
30     IF (cur_old_ref_values%NOTFOUND) AND (p_action  NOT IN ('INSERT','VALIDATE_INSERT')) THEN
31        Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
32        IGS_GE_MSG_STACK.ADD;
33        Close cur_old_ref_values;
34        APP_EXCEPTION.RAISE_EXCEPTION;
35        Return;
36     END IF;
37     Close cur_old_ref_values;
38     -- Populate New Values.
39     new_references.ass_id := x_ass_id;
40     new_references.exam_material_type := x_exam_material_type;
41     new_references.s_material_cat := x_s_material_cat;
42     new_references.quantity_per_student := x_quantity_per_student;
43     new_references.comments := x_comments;
44     IF (p_action = 'UPDATE') THEN
45       new_references.creation_date := old_references.creation_date;
46       new_references.created_by := old_references.created_by;
47     ELSE
48       new_references.creation_date := x_creation_date;
49       new_references.created_by := x_created_by;
50     END IF;
51     new_references.last_update_date := x_last_update_date;
52     new_references.last_updated_by := x_last_updated_by;
53     new_references.last_update_login := x_last_update_login;
54   END Set_Column_Values;
55   -- Trigger description :-
56   -- "OSS_TST".trg_aiem_br_iu
57   -- BEFORE INSERT OR UPDATE
58   -- ON IGS_AS_ITM_EXAM_MTRL
59   -- FOR EACH ROW
60   PROCEDURE BeforeRowInsertUpdate1(
61     p_inserting IN BOOLEAN DEFAULT FALSE,
62     p_updating IN BOOLEAN DEFAULT FALSE,
63     p_deleting IN BOOLEAN DEFAULT FALSE
64     ) AS
65 	v_message_name		VARCHAR2(30);
66   BEGIN
67 	-- Validate that inserts are allowed
68 	IF  p_inserting THEN
69 	    -- <aiem1>
70 	    -- Cannot create against closed examination_material_type
71 	    IF	IGS_AS_VAL_AIEM.assp_val_exmt_closed(
72 						new_references.exam_material_type,
73 						v_message_name) = FALSE THEN
74 		FND_MESSAGE.SET_NAME('IGS',V_MESSAGE_NAME);
75 		IGS_GE_MSG_STACK.ADD;
76 		APP_EXCEPTION.RAISE_EXCEPTION;
77 	    END IF;
78 	    -- <aiem2>
79 	    -- Can only create against IGS_AS_ASSESSMNT_ITM records which are
80 	    -- examinations
81 	    IF	IGS_AS_VAL_AIEM.assp_val_ai_exmnbl(
82 						new_references.ass_id,
83 						v_message_name) = FALSE THEN
84 		FND_MESSAGE.SET_NAME('IGS',V_MESSAGE_NAME);
85 		IGS_GE_MSG_STACK.ADD;
86 		APP_EXCEPTION.RAISE_EXCEPTION;
87 	    END IF;
88 	END IF;
89 	IF  p_inserting OR p_updating THEN
90 	    -- <aiem3>
91 	    -- Can only set quantity_per_student when s_material_type = 'SUPPLIED'
92 	    IF	IGS_AS_VAL_AIEM.assp_val_aiem_catqty(
93 						new_references.s_material_cat,
94 						new_references.quantity_per_student,
95 						v_message_name) = FALSE THEN
96 		FND_MESSAGE.SET_NAME('IGS',V_MESSAGE_NAME);
97 		IGS_GE_MSG_STACK.ADD;
98 		APP_EXCEPTION.RAISE_EXCEPTION;
99 	    END IF;
100 	END IF;
101   END BeforeRowInsertUpdate1;
102 
103   PROCEDURE Check_Parent_Existance AS
104   BEGIN
105     IF (((old_references.ass_id = new_references.ass_id)) OR
106         ((new_references.ass_id IS NULL))) THEN
107       NULL;
108     ELSIF NOT IGS_AS_ASSESSMNT_ITM_PKG.Get_PK_For_Validation (
109         new_references.ass_id
110         )THEN
111 	Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
112 	IGS_GE_MSG_STACK.ADD;
113 	APP_EXCEPTION.RAISE_EXCEPTION;
114 
115     END IF;
116     IF (((old_references.exam_material_type = new_references.exam_material_type)) OR
117         ((new_references.exam_material_type IS NULL))) THEN
118       NULL;
119     ELSIF NOT IGS_AS_EXM_MTRL_TYPE_PKG.Get_PK_For_Validation (
120         new_references.exam_material_type
121         )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   END Check_Parent_Existance;
128   FUNCTION Get_PK_For_Validation ( x_ass_id IN NUMBER,
129     x_exam_material_type IN VARCHAR2)RETURN BOOLEAN AS
130     CURSOR cur_rowid IS
131       SELECT   rowid
132       FROM     IGS_AS_ITM_EXAM_MTRL
133       WHERE    ass_id = x_ass_id
134       AND      exam_material_type = x_exam_material_type
135       FOR UPDATE NOWAIT;
136     lv_rowid cur_rowid%RowType;
137   BEGIN
138     Open cur_rowid;
139     Fetch cur_rowid INTO lv_rowid;
140     	IF (cur_rowid%FOUND) THEN
141 	      Close cur_rowid;
142 	      Return (TRUE);
143 	ELSE
144 	      Close cur_rowid;
145 	      Return (FALSE);
146 	END IF;
147   END Get_PK_For_Validation;
148   PROCEDURE GET_FK_IGS_AS_ASSESSMNT_ITM (
149     x_ass_id IN NUMBER
150     ) AS
151     CURSOR cur_rowid IS
152       SELECT   rowid
153       FROM     IGS_AS_ITM_EXAM_MTRL
154       WHERE    ass_id = x_ass_id ;
155     lv_rowid cur_rowid%RowType;
156   BEGIN
157     Open cur_rowid;
158     Fetch cur_rowid INTO lv_rowid;
159     IF (cur_rowid%FOUND) THEN
160        Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIEM_AI_FK');
161        IGS_GE_MSG_STACK.ADD;
162        Close cur_rowid;
163        APP_EXCEPTION.RAISE_EXCEPTION;
164       Return;
165     END IF;
166     Close cur_rowid;
167 
168   END GET_FK_IGS_AS_ASSESSMNT_ITM;
169   PROCEDURE GET_FK_IGS_AS_EXM_MTRL_TYPE (
170     x_exam_material_type IN VARCHAR2
171     ) AS
172     CURSOR cur_rowid IS
173       SELECT   rowid
174       FROM     IGS_AS_ITM_EXAM_MTRL
175       WHERE    exam_material_type = x_exam_material_type ;
176     lv_rowid cur_rowid%RowType;
177   BEGIN
178     Open cur_rowid;
179     Fetch cur_rowid INTO lv_rowid;
180     IF (cur_rowid%FOUND) THEN
181       Fnd_Message.Set_Name ('IGS', 'IGS_AS_AIEM_EXMT_FK');
182       IGS_GE_MSG_STACK.ADD;
183       Close cur_rowid;
184       APP_EXCEPTION.RAISE_EXCEPTION;
185       Return;
186     END IF;
187     Close cur_rowid;
188   END GET_FK_IGS_AS_EXM_MTRL_TYPE;
189 
190   PROCEDURE Before_DML (
191     p_action IN VARCHAR2,
192     x_rowid IN VARCHAR2 DEFAULT NULL,
193     x_ass_id IN NUMBER DEFAULT NULL,
194     x_exam_material_type IN VARCHAR2 DEFAULT NULL,
195     x_s_material_cat IN VARCHAR2 DEFAULT NULL,
196     x_quantity_per_student IN NUMBER DEFAULT NULL,
197     x_comments IN VARCHAR2 DEFAULT NULL,
198     x_creation_date IN DATE DEFAULT NULL,
199     x_created_by IN NUMBER DEFAULT NULL,
200     x_last_update_date IN DATE DEFAULT NULL,
201     x_last_updated_by IN NUMBER DEFAULT NULL,
202     x_last_update_login IN NUMBER DEFAULT NULL
203   ) AS
204   BEGIN
205     Set_Column_Values (
206       p_action,
207       x_rowid,
208       x_ass_id,
209       x_exam_material_type,
210       x_s_material_cat,
211       x_quantity_per_student,
212       x_comments,
213       x_creation_date,
214       x_created_by,
215       x_last_update_date,
216       x_last_updated_by,
217       x_last_update_login
218     );
219     IF (p_action = 'INSERT') THEN
220       -- Call all the procedures related to Before Insert.
221       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
222 	IF  Get_PK_For_Validation (
223 	             new_references.ass_id ,
224    		    new_references.exam_material_type ) THEN
225          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
226 	 IGS_GE_MSG_STACK.ADD;
227 	 APP_EXCEPTION.RAISE_EXCEPTION;
228        END IF;
229        Check_Constraints;
230        Check_Parent_Existance;
231     ELSIF (p_action = 'UPDATE') THEN
232       -- Call all the procedures related to Before Update.
233       BeforeRowInsertUpdate1 ( p_updating => TRUE );
234       Check_Parent_Existance;
235       Check_Constraints;
236     ELSIF (p_action = 'VALIDATE_INSERT') THEN
237 	     IF  Get_PK_For_Validation (
238 	         new_references.ass_id ,
239                  new_references.exam_material_type  ) THEN
240          Fnd_Message.Set_Name ('IGS', 'IGS_AS_MATERIAL_ALREADY_EXISTS');
241 	IGS_GE_MSG_STACK.ADD;
242 	APP_EXCEPTION.RAISE_EXCEPTION;
243      END IF;
244         Check_Constraints;
245      ELSIF (p_action = 'VALIDATE_UPDATE') THEN
246         Check_Constraints;
247     END IF;
248 
249 /*
250 The (L_ROWID := null) was added by ijeddy on 19-May-2003 as
251 part of the bug fix for bug no 2868726, (Uniqueness Check at Item Level)
252 */
253 L_ROWID := null;
254 
255  END Before_DML;
256 
257 procedure INSERT_ROW (
258   X_ROWID in out NOCOPY VARCHAR2,
259   X_ASS_ID in NUMBER,
260   X_EXAM_MATERIAL_TYPE in VARCHAR2,
261   X_S_MATERIAL_CAT in VARCHAR2,
262   X_QUANTITY_PER_STUDENT in NUMBER,
263   X_COMMENTS in VARCHAR2,
264   X_MODE in VARCHAR2 default 'R'
265   ) AS
266     cursor C is select ROWID from IGS_AS_ITM_EXAM_MTRL
267       where ASS_ID = X_ASS_ID
268       and EXAM_MATERIAL_TYPE = X_EXAM_MATERIAL_TYPE;
269     X_LAST_UPDATE_DATE DATE;
270     X_LAST_UPDATED_BY NUMBER;
271     X_LAST_UPDATE_LOGIN NUMBER;
272 begin
273   X_LAST_UPDATE_DATE := SYSDATE;
274   if(X_MODE = 'I') then
275     X_LAST_UPDATED_BY := 1;
276     X_LAST_UPDATE_LOGIN := 0;
277   elsif (X_MODE = 'R') then
278     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
279     if X_LAST_UPDATED_BY is NULL then
280       X_LAST_UPDATED_BY := -1;
281     end if;
282     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
283     if X_LAST_UPDATE_LOGIN is NULL then
284       X_LAST_UPDATE_LOGIN := -1;
285     end if;
286   else
287     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
288 IGS_GE_MSG_STACK.ADD;
289     APP_EXCEPTION.RAISE_EXCEPTION;
290   end if;
291 Before_DML(
292  p_action=>'INSERT',
293  x_rowid=>X_ROWID,
294  x_ass_id=>X_ASS_ID,
295  x_comments=>X_COMMENTS,
296  x_exam_material_type=>X_EXAM_MATERIAL_TYPE,
297  x_quantity_per_student=>X_QUANTITY_PER_STUDENT,
298  x_s_material_cat=>NVL(X_S_MATERIAL_CAT,'ALLOWABLE'),
299  x_creation_date=>X_LAST_UPDATE_DATE,
300  x_created_by=>X_LAST_UPDATED_BY,
301  x_last_update_date=>X_LAST_UPDATE_DATE,
302  x_last_updated_by=>X_LAST_UPDATED_BY,
303  x_last_update_login=>X_LAST_UPDATE_LOGIN
304  );
305   insert into IGS_AS_ITM_EXAM_MTRL (
306     ASS_ID,
307     EXAM_MATERIAL_TYPE,
308     S_MATERIAL_CAT,
309     QUANTITY_PER_STUDENT,
310     COMMENTS,
311     CREATION_DATE,
312     CREATED_BY,
313     LAST_UPDATE_DATE,
314     LAST_UPDATED_BY,
315     LAST_UPDATE_LOGIN
316   ) values (
317     NEW_REFERENCES.ASS_ID,
318     NEW_REFERENCES.EXAM_MATERIAL_TYPE,
319     NEW_REFERENCES.S_MATERIAL_CAT,
320     NEW_REFERENCES.QUANTITY_PER_STUDENT,
321     NEW_REFERENCES.COMMENTS,
322     X_LAST_UPDATE_DATE,
323     X_LAST_UPDATED_BY,
324     X_LAST_UPDATE_DATE,
325     X_LAST_UPDATED_BY,
326     X_LAST_UPDATE_LOGIN
327   );
328   open c;
329   fetch c into X_ROWID;
330   if (c%notfound) then
331     close c;
332     raise no_data_found;
333   end if;
334   close c;
335 
336 end INSERT_ROW;
337 procedure LOCK_ROW (
338   X_ROWID in  VARCHAR2,
339   X_ASS_ID in NUMBER,
340   X_EXAM_MATERIAL_TYPE in VARCHAR2,
341   X_S_MATERIAL_CAT in VARCHAR2,
342   X_QUANTITY_PER_STUDENT in NUMBER,
343   X_COMMENTS in VARCHAR2
344 ) AS
345   cursor c1 is select
346       S_MATERIAL_CAT,
347       QUANTITY_PER_STUDENT,
348       COMMENTS
349     from IGS_AS_ITM_EXAM_MTRL
350     where ROWID = X_ROWID  for update  nowait;
351   tlinfo c1%rowtype;
352 begin
353   open c1;
354   fetch c1 into tlinfo;
355   if (c1%notfound) then
356     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
357 IGS_GE_MSG_STACK.ADD;
358     APP_EXCEPTION.RAISE_EXCEPTION;
359     close c1;
360     return;
361   end if;
362   close c1;
363   if ( (tlinfo.S_MATERIAL_CAT = X_S_MATERIAL_CAT)
364       AND ((tlinfo.QUANTITY_PER_STUDENT = X_QUANTITY_PER_STUDENT)
365            OR ((tlinfo.QUANTITY_PER_STUDENT is null)
366                AND (X_QUANTITY_PER_STUDENT is null)))
367       AND ((tlinfo.COMMENTS = X_COMMENTS)
368            OR ((tlinfo.COMMENTS is null)
369                AND (X_COMMENTS is null)))
370   ) then
371     null;
372   else
373     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
374 IGS_GE_MSG_STACK.ADD;
375     APP_EXCEPTION.RAISE_EXCEPTION;
376   end if;
377   return;
378 end LOCK_ROW;
379 procedure UPDATE_ROW (
380   X_ROWID in  VARCHAR2,
381   X_ASS_ID in NUMBER,
382   X_EXAM_MATERIAL_TYPE in VARCHAR2,
383   X_S_MATERIAL_CAT in VARCHAR2,
384   X_QUANTITY_PER_STUDENT in NUMBER,
385   X_COMMENTS in VARCHAR2,
386   X_MODE in VARCHAR2 default 'R'
387   ) AS
388     X_LAST_UPDATE_DATE DATE;
389     X_LAST_UPDATED_BY NUMBER;
390     X_LAST_UPDATE_LOGIN NUMBER;
391 begin
392   X_LAST_UPDATE_DATE := SYSDATE;
393   if(X_MODE = 'I') then
394     X_LAST_UPDATED_BY := 1;
395     X_LAST_UPDATE_LOGIN := 0;
396   elsif (X_MODE = 'R') then
397     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
398     if X_LAST_UPDATED_BY is NULL then
399       X_LAST_UPDATED_BY := -1;
400     end if;
401     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
402     if X_LAST_UPDATE_LOGIN is NULL then
403       X_LAST_UPDATE_LOGIN := -1;
404     end if;
405   else
406     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
407 IGS_GE_MSG_STACK.ADD;
408     APP_EXCEPTION.RAISE_EXCEPTION;
409   end if;
410 Before_DML(
411  p_action=>'UPDATE',
412  x_rowid=>X_ROWID,
413  x_ass_id=>X_ASS_ID,
414  x_comments=>X_COMMENTS,
415  x_exam_material_type=>X_EXAM_MATERIAL_TYPE,
416  x_quantity_per_student=>X_QUANTITY_PER_STUDENT,
417  x_s_material_cat=>X_S_MATERIAL_CAT,
418  x_creation_date=>X_LAST_UPDATE_DATE,
419  x_created_by=>X_LAST_UPDATED_BY,
420  x_last_update_date=>X_LAST_UPDATE_DATE,
421  x_last_updated_by=>X_LAST_UPDATED_BY,
422  x_last_update_login=>X_LAST_UPDATE_LOGIN
423  );
424   update IGS_AS_ITM_EXAM_MTRL set
425     S_MATERIAL_CAT = NEW_REFERENCES.S_MATERIAL_CAT,
426     QUANTITY_PER_STUDENT = NEW_REFERENCES.QUANTITY_PER_STUDENT,
427     COMMENTS = NEW_REFERENCES.COMMENTS,
428     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
429     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
430     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
431   where ROWID = X_ROWID;
432   if (sql%notfound) then
433     raise no_data_found;
434   end if;
435 
436 end UPDATE_ROW;
437 procedure ADD_ROW (
438   X_ROWID in out NOCOPY VARCHAR2,
439   X_ASS_ID in NUMBER,
440   X_EXAM_MATERIAL_TYPE in VARCHAR2,
441   X_S_MATERIAL_CAT in VARCHAR2,
442   X_QUANTITY_PER_STUDENT in NUMBER,
443   X_COMMENTS in VARCHAR2,
444   X_MODE in VARCHAR2 default 'R'
445   ) AS
446   cursor c1 is select rowid from IGS_AS_ITM_EXAM_MTRL
447      where ASS_ID = X_ASS_ID
448      and EXAM_MATERIAL_TYPE = X_EXAM_MATERIAL_TYPE
449   ;
450 begin
451   open c1;
452   fetch c1 into X_ROWID;
453   if (c1%notfound) then
454     close c1;
455     INSERT_ROW (
456      X_ROWID,
457      X_ASS_ID,
458      X_EXAM_MATERIAL_TYPE,
459      X_S_MATERIAL_CAT,
460      X_QUANTITY_PER_STUDENT,
461      X_COMMENTS,
462      X_MODE);
463     return;
464   end if;
465   close c1;
466   UPDATE_ROW (
467    X_ROWID,
468    X_ASS_ID,
469    X_EXAM_MATERIAL_TYPE,
470    X_S_MATERIAL_CAT,
471    X_QUANTITY_PER_STUDENT,
472    X_COMMENTS,
473    X_MODE);
474 end ADD_ROW;
475 procedure DELETE_ROW (
476   X_ROWID in VARCHAR2) is
477 begin
478  Before_DML(
479   p_action => 'DELETE',
480   x_rowid => X_ROWID
481   );
482   delete from IGS_AS_ITM_EXAM_MTRL
483  where ROWID = X_ROWID;
484 
485   if (sql%notfound) then
486     raise no_data_found;
487   end if;
488 end DELETE_ROW;
489 	PROCEDURE Check_Constraints (
490 	Column_Name	IN	VARCHAR2	DEFAULT NULL,
491 	Column_Value 	IN	VARCHAR2	DEFAULT NULL
492 	)
493 	AS
494 	BEGIN
495 		IF  column_name is null then
496 	    NULL;
497 	ELSIF upper(Column_name) = 'EXAM_MATERIAL_TYPE' then
498 	    new_references.EXAM_MATERIAL_TYPE := column_value;
499       ELSIF upper(Column_name) = 'S_MATERIAL_CAT' then
500 	    new_references.S_MATERIAL_CAT := column_value;
501       END IF;
502 
503 IF upper(column_name) = 'EXAM_MATERIAL_TYPE'  OR
504      column_name is null Then
505      IF new_references.EXAM_MATERIAL_TYPE <> UPPER(new_references.EXAM_MATERIAL_TYPE) Then
506        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
507 IGS_GE_MSG_STACK.ADD;
508        APP_EXCEPTION.RAISE_EXCEPTION;
509                    END IF;
510               END IF;
511 
512 IF upper(column_name) = 'S_MATERIAL_CAT' OR
513      column_name is null Then
514      IF new_references.S_MATERIAL_CAT NOT IN ( 'ALLOWABLE' , 'NON-ALLOW' , 'SUPPLIED' ) then
515        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
516 IGS_GE_MSG_STACK.ADD;
517        APP_EXCEPTION.RAISE_EXCEPTION;
518            END IF;
519       END IF;
520 
521 
522 
523 END Check_Constraints;
524 
525  end IGS_AS_ITM_EXAM_MTRL_PKG;