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_PS_GOVT_DSCP_PKG

Source


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