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_SPCL_CONS_OUT_PKG

Source


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