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_GR_TESTAMUR_TYPE_PKG

Source


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