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.IGR_I_E_CHARTYP_PKG

Source


1 PACKAGE BODY igr_i_e_chartyp_pkg AS
2 /* $Header: IGSRH04B.pls 120.0 2005/06/01 15:59:53 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGR_I_E_CHARTYP%RowType;
6   new_references IGR_I_E_CHARTYP%RowType;
7 
8   PROCEDURE set_column_values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_enquiry_characteristic_type IN VARCHAR2 DEFAULT NULL,
12     x_description 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     igr_i_e_chartyp
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       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
36       igs_ge_msg_stack.add;
37       CLOSE cur_old_ref_values;
38       app_exception.raise_exception;
39       RETURN;
40     END IF;
41     Close cur_old_ref_values;
42 
43     -- Populate New Values.
44     new_references.enquiry_characteristic_type := x_enquiry_characteristic_type;
45     new_references.description := x_description;
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   PROCEDURE check_child_existance AS
61   BEGIN
62     igr_i_a_chartyp_pkg.igr_i_e_chartyp (
63       old_references.enquiry_characteristic_type
64       );
65   END check_child_existance;
66 
67   FUNCTION   get_pk_for_validation (
68     x_enquiry_characteristic_type IN VARCHAR2,
69     x_closed_ind IN     VARCHAR2
70     ) RETURN BOOLEAN AS
71 
72     CURSOR cur_rowid IS
73       SELECT   ROWID
74       FROM     igr_i_e_chartyp
75       WHERE    enquiry_characteristic_type = x_enquiry_characteristic_type AND
76                closed_ind = NVL(x_closed_ind,closed_ind);
77 
78     lv_rowid cur_rowid%ROWTYPE;
79 
80   BEGIN
81     Open cur_rowid;
82     FETCH cur_rowid INTO lv_rowid;
83     IF (cur_rowid%FOUND) THEN
84       CLOSE cur_rowid;
85       RETURN (TRUE);
86     ELSE
87       CLOSE cur_rowid;
88       RETURN (FALSE);
89     END IF;
90     CLOSE cur_rowid;
91   END get_pk_for_validation;
92 
93 
94 PROCEDURE Check_Constraints (
95 Column_Name	IN	VARCHAR2	DEFAULT NULL,
96 Column_Value 	IN	VARCHAR2	DEFAULT NULL
97 	) as
98 BEGIN
99 
100       IF  column_name IS NULL THEN
101          NULL;
102       ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
103          new_references.closed_ind:= column_value;
104       ELSIF UPPER(column_name) = 'ENQUIRY_CHARACTERISTIC_TYPE' THEN
105          new_references.enquiry_characteristic_type:= column_value;
106       END IF;
107      IF UPPER(column_name) = 'CLOSED_IND' OR
108         column_name IS NULL THEN
109         IF new_references.closed_ind <> UPPER(new_references.closed_ind) or new_references.closed_ind NOT IN ( 'Y' , 'N' ) THEN
110           fnd_message.set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
111           igs_ge_msg_stack.add;
112           app_exception.raise_exception;
113         END IF;
114      END IF;
115 
116      IF UPPER(column_name) = 'ENQUIRY_CHARACTERISTIC_TYPE' OR
117         column_name IS NULL THEN
118         IF new_references.enquiry_characteristic_type <> UPPER(new_references.enquiry_characteristic_type) THEN
119           fnd_message.set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
120           igs_ge_msg_stack.add;
121           app_exception.raise_exception;
122         END IF;
123      END IF;
124 
125 END check_constraints;
126 
127 
128   PROCEDURE Before_DML (
129     p_action IN VARCHAR2,
130     x_rowid IN VARCHAR2 DEFAULT NULL,
131     x_enquiry_characteristic_type IN VARCHAR2 DEFAULT NULL,
132     x_description IN VARCHAR2 DEFAULT NULL,
133     x_closed_ind IN VARCHAR2 DEFAULT NULL,
134     x_creation_date IN DATE DEFAULT NULL,
135     x_created_by IN NUMBER DEFAULT NULL,
136     x_last_update_date IN DATE DEFAULT NULL,
137     x_last_updated_by IN NUMBER DEFAULT NULL,
138     x_last_update_login IN NUMBER DEFAULT NULL
139   ) as
140   BEGIN
141 
142     Set_Column_Values (
143       p_action,
144       x_rowid,
145       x_enquiry_characteristic_type,
146       x_description,
147       x_closed_ind,
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       -- Call all the procedures related to Before Insert.
157       Null;
158     IF Get_PK_For_Validation (
159           new_references.enquiry_characteristic_type
160 	) THEN
161 	Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
162         igs_ge_msg_stack.add;
163  	App_Exception.Raise_Exception;
164     END IF;
165 
166       Check_Constraints;
167     ELSIF (p_action = 'UPDATE') THEN
168       -- Call all the procedures related to Before Update.
169       Check_Constraints;
170     ELSIF (p_action = 'DELETE') THEN
171       -- Call all the procedures related to Before Delete.
172       Check_Child_Existance;
173     ELSIF (p_action = 'VALIDATE_INSERT') THEN
174       IF  Get_PK_For_Validation (
175          new_references.enquiry_characteristic_type
176 			             ) THEN
177 	Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
178         igs_ge_msg_stack.add;
179 	App_Exception.Raise_Exception;
180     END IF;
181 	        Check_Constraints;
182     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
183 	        Check_Constraints;
184     ELSIF (p_action = 'VALIDATE_DELETE') THEN
185               Check_Child_Existance;
186     END IF;
187 
188   END Before_DML;
189 
190 
191 procedure INSERT_ROW (
192   X_ROWID in out NOCOPY VARCHAR2,
193   X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2,
194   X_DESCRIPTION in VARCHAR2,
195   X_CLOSED_IND in VARCHAR2,
196   X_MODE in VARCHAR2 default 'R'
197   ) as
198     CURSOR c IS SELECT ROWID FROM IGR_I_E_CHARTYP
199       where ENQUIRY_CHARACTERISTIC_TYPE = X_ENQUIRY_CHARACTERISTIC_TYPE;
200     X_LAST_UPDATE_DATE DATE;
201     X_LAST_UPDATED_BY NUMBER;
202     X_LAST_UPDATE_LOGIN NUMBER;
203 BEGIN
204   X_LAST_UPDATE_DATE := SYSDATE;
205   IF(X_MODE = 'I') THEN
206     X_LAST_UPDATED_BY := 1;
207     X_LAST_UPDATE_LOGIN := 0;
208   ELSIF (X_MODE = 'R') THEN
209     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
210     IF X_LAST_UPDATED_BY IS NULL THEN
211       X_LAST_UPDATED_BY := -1;
212     END IF;
213     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
214     IF X_LAST_UPDATE_LOGIN is NULL THEN
215       X_LAST_UPDATE_LOGIN := -1;
216     END IF;
217   ELSE
218     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
219       IGS_GE_MSG_STACK.ADD;
220     app_exception.raise_exception;
221   END IF;
222 
223 Before_DML(
224  p_action=>'INSERT',
225  x_rowid=>X_ROWID,
226  x_closed_ind=>NVL(X_CLOSED_IND,'N'),
227  x_description=>X_DESCRIPTION,
228  x_enquiry_characteristic_type=>X_ENQUIRY_CHARACTERISTIC_TYPE,
229  x_creation_date=>X_LAST_UPDATE_DATE,
230  x_created_by=>X_LAST_UPDATED_BY,
231  x_last_update_date=>X_LAST_UPDATE_DATE,
232  x_last_updated_by=>X_LAST_UPDATED_BY,
233  x_last_update_login=>X_LAST_UPDATE_LOGIN
234  );
235 
236   INSERT INTO IGR_I_E_CHARTYP (
237     ENQUIRY_CHARACTERISTIC_TYPE,
238     DESCRIPTION,
239     CLOSED_IND,
240     CREATION_DATE,
241     CREATED_BY,
242     LAST_UPDATE_DATE,
243     LAST_UPDATED_BY,
244     LAST_UPDATE_LOGIN
245   ) VALUES (
246     NEW_REFERENCES.ENQUIRY_CHARACTERISTIC_TYPE,
247     NEW_REFERENCES.DESCRIPTION,
248     NEW_REFERENCES.CLOSED_IND,
249     X_LAST_UPDATE_DATE,
250     X_LAST_UPDATED_BY,
251     X_LAST_UPDATE_DATE,
252     X_LAST_UPDATED_BY,
253     X_LAST_UPDATE_LOGIN
254   );
255 
256   OPEN c;
257   FETCH c INTO X_ROWID;
258   IF (c%NOTFOUND) THEN
259     CLOSE c;
260     RAISE NO_DATA_FOUND;
261   END IF;
262   CLOSE c;
263 
264 end INSERT_ROW;
265 
266 PROCEDURE lock_row (
267   X_ROWID in VARCHAR2,
268   X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2,
269   X_DESCRIPTION in VARCHAR2,
270   X_CLOSED_IND in VARCHAR2
271 ) as
272   CURSOR c1 IS SELECT
273       DESCRIPTION,
274       CLOSED_IND
275     FROM IGR_I_E_CHARTYP
276     WHERE ROWID = X_ROWID
277     FOR UPDATE NOWAIT;
278   tlinfo c1%rowtype;
279 
280 begin
281   OPEN c1;
282   FETCH c1 INTO tlinfo;
283   IF (c1%NOTFOUND) THEN
284     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
285       IGS_GE_MSG_STACK.ADD;
286     CLOSE c1;
287     app_exception.raise_exception;
288     RETURN;
289   END IF;
290   CLOSE c1;
291 
292   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
293       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
294   ) THEN
295     NULL;
296   else
297     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
298       IGS_GE_MSG_STACK.ADD;
299     app_exception.raise_exception;
300   END IF;
301   RETURN;
302 END LOCK_ROW;
303 
304 procedure UPDATE_ROW (
305   X_ROWID in VARCHAR2,
306   X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2,
307   X_DESCRIPTION in VARCHAR2,
308   X_CLOSED_IND in VARCHAR2,
309   X_MODE in VARCHAR2 default 'R'
310   ) as
311     X_LAST_UPDATE_DATE DATE;
312     X_LAST_UPDATED_BY NUMBER;
313     X_LAST_UPDATE_LOGIN NUMBER;
314 begin
315   X_LAST_UPDATE_DATE := SYSDATE;
316   IF(X_MODE = 'I') THEN
317     X_LAST_UPDATED_BY := 1;
318     X_LAST_UPDATE_LOGIN := 0;
319   ELSIF (X_MODE = 'R') THEN
320     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
321     if X_LAST_UPDATED_BY is NULL then
322       X_LAST_UPDATED_BY := -1;
323     END IF;
324     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
325     if X_LAST_UPDATE_LOGIN is NULL then
326       X_LAST_UPDATE_LOGIN := -1;
327     END IF;
328   ELSE
329     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
330       IGS_GE_MSG_STACK.ADD;
331     app_exception.raise_exception;
332   END IF;
333 
334 Before_DML(
335  p_action=>'UPDATE',
336  x_rowid=>X_ROWID,
337  x_closed_ind=>X_CLOSED_IND,
338  x_description=>X_DESCRIPTION,
339  x_enquiry_characteristic_type=>X_ENQUIRY_CHARACTERISTIC_TYPE,
340  x_creation_date=>X_LAST_UPDATE_DATE,
341  x_created_by=>X_LAST_UPDATED_BY,
342  x_last_update_date=>X_LAST_UPDATE_DATE,
343  x_last_updated_by=>X_LAST_UPDATED_BY,
344  x_last_update_login=>X_LAST_UPDATE_LOGIN
345  );
346 
347   UPDATE IGR_I_E_CHARTYP SET
348     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
349     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
350     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
351     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
352     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
353   WHERE ROWID = X_ROWID
354   ;
355   IF (sql%notfound) THEN
356     RAISE NO_DATA_FOUND;
357   END IF;
358 end UPDATE_ROW;
359 
360 procedure ADD_ROW (
361   X_ROWID in out NOCOPY VARCHAR2,
362   X_ENQUIRY_CHARACTERISTIC_TYPE in VARCHAR2,
363   X_DESCRIPTION in VARCHAR2,
364   X_CLOSED_IND in VARCHAR2,
365   X_MODE in VARCHAR2 default 'R'
366   ) as
367   CURSOR c1 IS SELECT ROWID FROM IGR_I_E_CHARTYP
368      WHERE ENQUIRY_CHARACTERISTIC_TYPE = X_ENQUIRY_CHARACTERISTIC_TYPE
369   ;
370 
371 BEGIN
372   OPEN c1;
373   FETCH c1 INTO X_ROWID;
374   IF (c1%notfound) THEN
375     CLOSE c1;
376     INSERT_ROW (
377      X_ROWID,
378      X_ENQUIRY_CHARACTERISTIC_TYPE,
379      X_DESCRIPTION,
380      X_CLOSED_IND,
381      X_MODE);
382     return;
383   END IF;
384   CLOSE c1;
385   UPDATE_ROW (
386    X_ROWID,
387    X_ENQUIRY_CHARACTERISTIC_TYPE,
388    X_DESCRIPTION,
389    X_CLOSED_IND,
390    X_MODE);
391 END ADD_ROW;
392 
393 END IGR_I_E_CHARTYP_PKG;