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_GE_SPL_REQ_PKG

Source


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