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_OR_INST_STAT_PKG

Source


1 package body IGS_OR_INST_STAT_PKG AS
2 /* $Header: IGSOI05B.pls 120.0 2005/06/01 23:09:01 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_OR_INST_STAT%RowType;
6   new_references IGS_OR_INST_STAT%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_institution_status IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_s_institution_status IN VARCHAR2 DEFAULT NULL,
14     x_closed_ind IN VARCHAR2 DEFAULT NULL,
15     x_creation_date IN DATE DEFAULT NULL,
16     x_created_by IN NUMBER DEFAULT NULL,
17     x_last_update_date IN DATE DEFAULT NULL,
18     x_last_updated_by IN NUMBER DEFAULT NULL,
19     x_last_update_login IN NUMBER DEFAULT NULL
20   ) AS
21     CURSOR cur_old_ref_values IS
22       SELECT   *
23       FROM     IGS_OR_INST_STAT
24       WHERE    rowid = x_rowid;
25   BEGIN
26     l_rowid := x_rowid;
27     -- Code for setting the Old and New Reference Values.
28     -- Populate Old Values.
29     Open cur_old_ref_values;
30     Fetch cur_old_ref_values INTO old_references;
31     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
32       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
33       IGS_GE_MSG_STACK.ADD;
34       App_Exception.Raise_Exception;
35       Close cur_old_ref_values;
36       Return;
37     END IF;
38     Close cur_old_ref_values;
39     -- Populate New Values.
40     new_references.institution_status := x_institution_status;
41     new_references.description := x_description;
42     new_references.s_institution_status := x_s_institution_status;
43     new_references.closed_ind := x_closed_ind;
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 
56   PROCEDURE Check_Child_Existance AS
57   BEGIN
58     IGS_OR_INSTITUTION_PKG.GET_FK_IGS_OR_INST_STAT (
59       old_references.institution_status
60       );
61   END Check_Child_Existance;
62 
63   FUNCTION Get_PK_For_Validation (
64     x_institution_status IN VARCHAR2
65     )RETURN BOOLEAN AS
66     CURSOR cur_rowid IS
67       SELECT   rowid
68       FROM     IGS_OR_INST_STAT
69       WHERE    institution_status = x_institution_status
70       FOR UPDATE NOWAIT;
71     lv_rowid cur_rowid%RowType;
72   BEGIN
73     Open cur_rowid;
74     Fetch cur_rowid INTO lv_rowid;
75     IF (cur_rowid%FOUND) THEN
76         Close cur_rowid;
77  		RETURN(TRUE);
78 	ELSE
79         Close cur_rowid;
80 	    RETURN(FALSE);
81 	END IF;
82   END Get_PK_For_Validation;
83 
84   PROCEDURE Before_DML (
85     p_action IN VARCHAR2,
86     x_rowid IN VARCHAR2 DEFAULT NULL,
87     x_institution_status IN VARCHAR2 DEFAULT NULL,
88     x_description IN VARCHAR2 DEFAULT NULL,
89     x_s_institution_status IN VARCHAR2 DEFAULT NULL,
90     x_closed_ind IN VARCHAR2 DEFAULT NULL,
91     x_creation_date IN DATE DEFAULT NULL,
92     x_created_by IN NUMBER DEFAULT NULL,
93     x_last_update_date IN DATE DEFAULT NULL,
94     x_last_updated_by IN NUMBER DEFAULT NULL,
95     x_last_update_login IN NUMBER DEFAULT NULL
96   ) AS
97   BEGIN
98     Set_Column_Values (
99       p_action,
100       x_rowid,
101       x_institution_status,
102       x_description,
103       x_s_institution_status,
104       x_closed_ind,
105       x_creation_date,
106       x_created_by,
107       x_last_update_date,
108       x_last_updated_by,
109       x_last_update_login
110     );
111     IF (p_action = 'INSERT') THEN
112       -- Call all the procedures related to Before Insert.
113 
114   IF Get_PK_For_Validation (
115      new_references.institution_status
116     ) then
117          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
118          IGS_GE_MSG_STACK.ADD;
119          App_Exception.Raise_Exception;
120   END IF;
121 
122   Check_Constraints ;
123 
124     ELSIF (p_action = 'UPDATE') THEN
125       -- Call all the procedures related to Before Update.
126   Check_Constraints ;
127 
128     ELSIF (p_action = 'DELETE') THEN
129       -- Call all the procedures related to Before Delete.
130       Null;
131       Check_Child_Existance;
132 
133    ELSIF (p_action = 'VALIDATE_INSERT') THEN
134 
135       if Get_PK_For_Validation (
136         new_references.institution_status
137 		) then
138 
139         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
140         IGS_GE_MSG_STACK.ADD;
141         App_Exception.Raise_Exception;
142       end if;
143 
144        Check_Constraints ;
145 
146   	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
147 
148        Check_Constraints ;
149 
150   	ELSIF (p_action = 'VALIDATE_DELETE') THEN
151 
152        Check_Child_Existance ;
153 
154 
155   END IF;
156   END Before_DML;
157 
158   PROCEDURE After_DML (
159     p_action IN VARCHAR2,
160     x_rowid IN VARCHAR2
161   ) AS
162   BEGIN
163     l_rowid := x_rowid;
164   END After_DML;
165 
166 procedure INSERT_ROW (
167   X_ROWID in out NOCOPY VARCHAR2,
168   X_INSTITUTION_STATUS in VARCHAR2,
169   X_DESCRIPTION in VARCHAR2,
170   X_S_INSTITUTION_STATUS in VARCHAR2,
171   X_CLOSED_IND in VARCHAR2,
172   X_MODE in VARCHAR2 default 'R'
173   ) AS
174     cursor C is select ROWID from IGS_OR_INST_STAT
175       where INSTITUTION_STATUS = X_INSTITUTION_STATUS;
176     X_LAST_UPDATE_DATE DATE;
177     X_LAST_UPDATED_BY NUMBER;
178     X_LAST_UPDATE_LOGIN NUMBER;
179 begin
180   X_LAST_UPDATE_DATE := SYSDATE;
181   if(X_MODE = 'I') then
182     X_LAST_UPDATED_BY := 1;
183     X_LAST_UPDATE_LOGIN := 0;
184   elsif (X_MODE = 'R') then
185     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
186     if X_LAST_UPDATED_BY is NULL then
187       X_LAST_UPDATED_BY := -1;
188     end if;
189     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
190     if X_LAST_UPDATE_LOGIN is NULL then
191       X_LAST_UPDATE_LOGIN := -1;
192     end if;
193   else
194     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
195     app_exception.raise_exception;
196   end if;
197  Before_DML(
198   p_action=>'INSERT',
199   x_rowid=>X_ROWID,
200   x_closed_ind=>NVL(X_CLOSED_IND,'N'),
201   x_description=>X_DESCRIPTION,
202   x_institution_status=>X_INSTITUTION_STATUS,
203   x_s_institution_status=>X_S_INSTITUTION_STATUS,
204   x_creation_date=>X_LAST_UPDATE_DATE,
205   x_created_by=>X_LAST_UPDATED_BY,
206   x_last_update_date=>X_LAST_UPDATE_DATE,
207   x_last_updated_by=>X_LAST_UPDATED_BY,
208   x_last_update_login=>X_LAST_UPDATE_LOGIN
209   );
210   insert into IGS_OR_INST_STAT (
211     INSTITUTION_STATUS,
212     DESCRIPTION,
213     S_INSTITUTION_STATUS,
214     CLOSED_IND,
215     CREATION_DATE,
216     CREATED_BY,
217     LAST_UPDATE_DATE,
218     LAST_UPDATED_BY,
219     LAST_UPDATE_LOGIN
220   ) values (
221     NEW_REFERENCES.INSTITUTION_STATUS,
222     NEW_REFERENCES.DESCRIPTION,
223     NEW_REFERENCES.S_INSTITUTION_STATUS,
224     NEW_REFERENCES.CLOSED_IND,
225     X_LAST_UPDATE_DATE,
226     X_LAST_UPDATED_BY,
227     X_LAST_UPDATE_DATE,
228     X_LAST_UPDATED_BY,
229     X_LAST_UPDATE_LOGIN
230   );
231   open c;
232   fetch c into X_ROWID;
233   if (c%notfound) then
234     close c;
235     raise no_data_found;
236   end if;
237   close c;
238   After_DML(
239     p_action=>'INSERT',
240     x_rowid=>X_ROWID
241     );
242 end INSERT_ROW;
243 
244 procedure LOCK_ROW (
245   X_ROWID in VARCHAR2,
246   X_INSTITUTION_STATUS in VARCHAR2,
247   X_DESCRIPTION in VARCHAR2,
248   X_S_INSTITUTION_STATUS in VARCHAR2,
249   X_CLOSED_IND in VARCHAR2
250 ) AS
251   cursor c1 is select
252       DESCRIPTION,
253       S_INSTITUTION_STATUS,
254       CLOSED_IND
255     from IGS_OR_INST_STAT
256     where ROWID = X_ROWID
257     for update nowait ;
258   tlinfo c1%rowtype;
259 begin
260   open c1;
261   fetch c1 into tlinfo;
262   if (c1%notfound) then
263     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
264     app_exception.raise_exception;
265     close c1;
266     return;
267   end if;
268   close c1;
269   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
270       AND (tlinfo.S_INSTITUTION_STATUS = X_S_INSTITUTION_STATUS)
271       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
272   ) then
273     null;
274   else
275     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
276     app_exception.raise_exception;
277   end if;
278   return;
279 end LOCK_ROW;
280 
281 procedure UPDATE_ROW (
282   X_ROWID in VARCHAR2,
283   X_INSTITUTION_STATUS in VARCHAR2,
284   X_DESCRIPTION in VARCHAR2,
285   X_S_INSTITUTION_STATUS in VARCHAR2,
286   X_CLOSED_IND in VARCHAR2,
287   X_MODE in VARCHAR2 default 'R'
288   ) AS
289     X_LAST_UPDATE_DATE DATE;
290     X_LAST_UPDATED_BY NUMBER;
291     X_LAST_UPDATE_LOGIN NUMBER;
292 begin
293   X_LAST_UPDATE_DATE := SYSDATE;
294   if(X_MODE = 'I') then
295     X_LAST_UPDATED_BY := 1;
296     X_LAST_UPDATE_LOGIN := 0;
297   elsif (X_MODE = 'R') then
298     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
299     if X_LAST_UPDATED_BY is NULL then
300       X_LAST_UPDATED_BY := -1;
301     end if;
302     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
303     if X_LAST_UPDATE_LOGIN is NULL then
304       X_LAST_UPDATE_LOGIN := -1;
305     end if;
306   else
307     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
308     app_exception.raise_exception;
309   end if;
310   Before_DML(
311    p_action=>'UPDATE',
312    x_rowid=>X_ROWID,
313    x_closed_ind=>NVL(X_CLOSED_IND,'N'),
314    x_description=>X_DESCRIPTION,
315    x_institution_status=>X_INSTITUTION_STATUS,
316    x_s_institution_status=>X_S_INSTITUTION_STATUS,
317    x_creation_date=>X_LAST_UPDATE_DATE,
318    x_created_by=>X_LAST_UPDATED_BY,
319    x_last_update_date=>X_LAST_UPDATE_DATE,
320    x_last_updated_by=>X_LAST_UPDATED_BY,
321    x_last_update_login=>X_LAST_UPDATE_LOGIN
322    );
323   update IGS_OR_INST_STAT set
324     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
325     S_INSTITUTION_STATUS = NEW_REFERENCES.S_INSTITUTION_STATUS,
326     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
327     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
328     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
329     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
330   where ROWID = X_ROWID ;
331   if (sql%notfound) then
332     raise no_data_found;
333   end if;
334   After_DML(
335     p_action=>'UPDATE',
336     x_rowid=>X_ROWID
337     );
338 end UPDATE_ROW;
339 
340 procedure ADD_ROW (
341   X_ROWID in out NOCOPY VARCHAR2,
342   X_INSTITUTION_STATUS in VARCHAR2,
343   X_DESCRIPTION in VARCHAR2,
344   X_S_INSTITUTION_STATUS in VARCHAR2,
345   X_CLOSED_IND in VARCHAR2,
346   X_MODE in VARCHAR2 default 'R'
347   ) AS
348   cursor c1 is select rowid from IGS_OR_INST_STAT
349      where INSTITUTION_STATUS = X_INSTITUTION_STATUS
350   ;
351 begin
352   open c1;
353   fetch c1 into X_ROWID ;
354   if (c1%notfound) then
355     close c1;
356     INSERT_ROW (
357      X_ROWID,
358      X_INSTITUTION_STATUS,
359      X_DESCRIPTION,
360      X_S_INSTITUTION_STATUS,
361      X_CLOSED_IND,
362      X_MODE);
363     return;
364   end if;
365   close c1;
366   UPDATE_ROW (
367    X_ROWID,
368    X_INSTITUTION_STATUS,
369    X_DESCRIPTION,
370    X_S_INSTITUTION_STATUS,
371    X_CLOSED_IND,
372    X_MODE);
373 end ADD_ROW;
374 
375 procedure DELETE_ROW (
376    X_ROWID in VARCHAR2
377 ) AS
378 begin
379   Before_DML(
380    p_action=>'DELETE',
381    x_rowid=>X_ROWID
382    );
383   delete from IGS_OR_INST_STAT
384   where ROWID = X_ROWID ;
385   if (sql%notfound) then
386     raise no_data_found;
387   end if;
388   After_DML(
389     p_action=>'DELETE',
390     x_rowid=>X_ROWID
391     );
392 end DELETE_ROW;
393 
394 procedure Check_Constraints (
395   Column_Name in VARCHAR2 DEFAULT NULL ,
396   Column_Value in VARCHAR2 DEFAULT NULL
397   ) AS
398  begin
399 IF Column_Name is null THEN
400   NULL;
401 ELSIF upper(Column_name) = 'CLOSED_IND' THEN
402   new_references.CLOSED_IND:= COLUMN_VALUE ;
403 ELSIF upper(Column_name) = 'INSTITUTION_STATUS' THEN
404   new_references.INSTITUTION_STATUS:= COLUMN_VALUE ;
405 ELSIF upper(Column_name) = 'S_INSTITUTION_STATUS' THEN
406   new_references.S_INSTITUTION_STATUS:= COLUMN_VALUE ;
407 END IF ;
408 IF upper(Column_name) = 'CLOSED_IND' OR COLUMN_NAME IS NULL THEN
409   IF new_references.CLOSED_IND<> upper(new_references.CLOSED_IND) then
410     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
411     IGS_GE_MSG_STACK.ADD;
412     App_Exception.Raise_Exception ;
413   END IF;
414   IF new_references.CLOSED_IND not in  ('Y','N') then
415     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
416     IGS_GE_MSG_STACK.ADD;
417     App_Exception.Raise_Exception ;
418   END IF;
419 END IF ;
420 IF upper(Column_name) = 'INSTITUTION_STATUS' OR COLUMN_NAME IS NULL THEN
421   IF new_references.INSTITUTION_STATUS<> upper(new_references.INSTITUTION_STATUS) then
422     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
423     IGS_GE_MSG_STACK.ADD;
424     App_Exception.Raise_Exception ;
425   END IF;
426 END IF ;
427 IF upper(Column_name) = 'S_INSTITUTION_STATUS' OR COLUMN_NAME IS NULL THEN
428   IF new_references.S_INSTITUTION_STATUS<> upper(new_references.S_INSTITUTION_STATUS) then
429     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
430     IGS_GE_MSG_STACK.ADD;
431     App_Exception.Raise_Exception ;
432   END IF;
433   IF new_references.S_INSTITUTION_STATUS NOT IN ( 'INACTIVE' , 'ACTIVE' ) then
434     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
435     IGS_GE_MSG_STACK.ADD;
436     App_Exception.Raise_Exception ;
437   END IF;
438 END IF ;
439 end Check_Constraints ;
440 end IGS_OR_INST_STAT_PKG;