[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_NOTE_TYPE_PKG
Source
1 package body IGS_GR_NOTE_TYPE_PKG as
2 /* $Header: IGSGI15B.pls 115.5 2003/05/19 04:45:18 ijeddy ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_GR_NOTE_TYPE%RowType;
5 new_references IGS_GR_NOTE_TYPE%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_grd_note_type IN VARCHAR2 DEFAULT NULL,
11 x_description IN VARCHAR2 DEFAULT NULL,
12 x_creation_date IN DATE DEFAULT NULL,
13 x_created_by IN NUMBER DEFAULT NULL,
14 x_last_update_date IN DATE DEFAULT NULL,
15 x_last_updated_by IN NUMBER DEFAULT NULL,
16 x_last_update_login IN NUMBER DEFAULT NULL
17 ) AS
18
19 CURSOR cur_old_ref_values IS
20 SELECT *
21 FROM IGS_GR_NOTE_TYPE
22 WHERE rowid = x_rowid;
23
24 BEGIN
25
26 l_rowid := x_rowid;
27
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
33 Close cur_old_ref_values;
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 App_Exception.Raise_Exception;
36 Return;
37 END IF;
38 Close cur_old_ref_values;
39
40 -- Populate New Values.
41 new_references.grd_note_type := x_grd_note_type;
42 new_references.description := x_description;
43 IF (p_action = 'UPDATE') THEN
44 new_references.creation_date := old_references.creation_date;
45 new_references.created_by := old_references.created_by;
46 ELSE
47 new_references.creation_date := x_creation_date;
48 new_references.created_by := x_created_by;
49 END IF;
50 new_references.last_update_date := x_last_update_date;
51 new_references.last_updated_by := x_last_updated_by;
52 new_references.last_update_login := x_last_update_login;
53
54 END Set_Column_Values;
55
56 FUNCTION Get_PK_For_Validation (
57 x_grd_note_type IN VARCHAR2
58 ) RETURN BOOLEAN AS
59
60 CURSOR cur_rowid IS
61 SELECT rowid
62 FROM IGS_GR_NOTE_TYPE
63 WHERE grd_note_type = x_grd_note_type
64 FOR UPDATE NOWAIT;
65
66 lv_rowid cur_rowid%RowType;
67
68 BEGIN
69
70 Open cur_rowid;
71 Fetch cur_rowid INTO lv_rowid;
72 IF (cur_rowid%FOUND) THEN
73 Close cur_rowid;
74 Return (TRUE);
75 ELSE
76 Close cur_rowid;
77 Return (FALSE);
78 END IF;
79
80 END Get_PK_For_Validation;
81
82 PROCEDURE CHECK_CONSTRAINTS(
83 Column_Name IN VARCHAR2 DEFAULT NULL,
84 Column_Value IN VARCHAR2 DEFAULT NULL
85 ) AS
86 BEGIN
87 IF Column_Name is null THEN
88 NULL;
89 ELSIF upper(Column_name) = 'GRD_NOTE_TYPE' THEN
90 new_references.GRD_NOTE_TYPE:= COLUMN_VALUE ;
91
92 END IF ;
93
94 IF upper(Column_name) = 'GRD_NOTE_TYPE' OR COLUMN_NAME IS NULL THEN
95 IF new_references.GRD_NOTE_TYPE<> upper(new_references.GRD_NOTE_TYPE) then
96 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
97 App_Exception.Raise_Exception ;
98 END IF;
99
100 END IF ;
101
102 END CHECK_CONSTRAINTS;
103
104 PROCEDURE Check_Child_Existance AS
105 BEGIN
106
107 IGS_GR_CRMN_NOTE_PKG.GET_FK_IGS_GR_NOTE_TYPE (
108 old_references.grd_note_type
109 );
110
111 END Check_Child_Existance;
112
113 PROCEDURE Before_DML (
114 p_action IN VARCHAR2,
115 x_rowid IN VARCHAR2 DEFAULT NULL,
116 x_grd_note_type IN VARCHAR2 DEFAULT NULL,
117 x_description IN VARCHAR2 DEFAULT NULL,
118 x_creation_date IN DATE DEFAULT NULL,
119 x_created_by IN NUMBER DEFAULT NULL,
120 x_last_update_date IN DATE DEFAULT NULL,
121 x_last_updated_by IN NUMBER DEFAULT NULL,
122 x_last_update_login IN NUMBER DEFAULT NULL
123 ) AS
124 BEGIN
125
126 Set_Column_Values (
127 p_action,
128 x_rowid,
129 x_grd_note_type,
130 x_description,
131 x_creation_date,
132 x_created_by,
133 x_last_update_date,
134 x_last_updated_by,
135 x_last_update_login
136 );
137
138 IF (p_action = 'INSERT') THEN
139 -- Call all the procedures related to Before Insert.
140 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.grd_note_type) THEN
141 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
142 App_Exception.Raise_Exception;
143 END IF;
144
145 check_constraints;
146 ELSIF (p_action = 'UPDATE') THEN
147 -- Call all the procedures related to Before Update.
148
149 check_constraints;
150 ELSIF (p_action = 'DELETE') THEN
151 -- Call all the procedures related to Before Delete.
152 Check_Child_Existance;
153 ELSIF (p_action = 'VALIDATE_INSERT') THEN
154 IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.grd_note_type) THEN
155 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
156 App_Exception.Raise_Exception;
157 END IF;
158
159 check_constraints;
160 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
161
162 check_constraints;
163 ELSIF (p_action = 'VALIDATE_DELETE') THEN
164 check_child_existance;
165 END IF;
166
167 /*
168 The (L_ROWID := null) was added by ijeddy on the 12-apr-2003 as
169 part of the bug fix for bug no 2868726, (Uniqueness Check at Item Level)
170 */
171 L_ROWID := null;
172 END Before_DML;
173
174 procedure INSERT_ROW (
175 X_ROWID in out NOCOPY VARCHAR2,
176 X_GRD_NOTE_TYPE in VARCHAR2,
177 X_DESCRIPTION in VARCHAR2,
178 X_MODE in VARCHAR2 default 'R'
179 ) AS
180 cursor C is select ROWID from IGS_GR_NOTE_TYPE
181 where GRD_NOTE_TYPE = X_GRD_NOTE_TYPE;
182 X_LAST_UPDATE_DATE DATE;
183 X_LAST_UPDATED_BY NUMBER;
184 X_LAST_UPDATE_LOGIN NUMBER;
185 begin
186 X_LAST_UPDATE_DATE := SYSDATE;
187 if(X_MODE = 'I') then
188 X_LAST_UPDATED_BY := 1;
189 X_LAST_UPDATE_LOGIN := 0;
190 elsif (X_MODE = 'R') then
191 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
192 if X_LAST_UPDATED_BY is NULL then
193 X_LAST_UPDATED_BY := -1;
194 end if;
195 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
196 if X_LAST_UPDATE_LOGIN is NULL then
197 X_LAST_UPDATE_LOGIN := -1;
198 end if;
199 else
200 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
201 app_exception.raise_exception;
202 end if;
203
204 Before_DML (
205 p_action => 'INSERT',
206 x_rowid => X_ROWID,
207 x_grd_note_type => X_GRD_NOTE_TYPE,
208 x_description => X_DESCRIPTION,
209 x_creation_date => X_LAST_UPDATE_DATE,
210 x_created_by => X_LAST_UPDATED_BY,
211 x_last_update_date => X_LAST_UPDATE_DATE,
212 x_last_updated_by => X_LAST_UPDATED_BY,
213 x_last_update_login => X_LAST_UPDATE_LOGIN
214 );
215
216 insert into IGS_GR_NOTE_TYPE (
217 GRD_NOTE_TYPE,
218 DESCRIPTION,
219 CREATION_DATE,
220 CREATED_BY,
221 LAST_UPDATE_DATE,
222 LAST_UPDATED_BY,
223 LAST_UPDATE_LOGIN
224 ) values (
225 NEW_REFERENCES.GRD_NOTE_TYPE,
226 NEW_REFERENCES.DESCRIPTION,
227 X_LAST_UPDATE_DATE,
228 X_LAST_UPDATED_BY,
229 X_LAST_UPDATE_DATE,
230 X_LAST_UPDATED_BY,
231 X_LAST_UPDATE_LOGIN
232 );
233
234 open c;
235 fetch c into X_ROWID;
236 if (c%notfound) then
237 close c;
238 raise no_data_found;
239 end if;
240 close c;
241
242 end INSERT_ROW;
243
244 procedure LOCK_ROW (
245 X_ROWID in VARCHAR2,
246 X_GRD_NOTE_TYPE in VARCHAR2,
247 X_DESCRIPTION in VARCHAR2
248 ) AS
249 cursor c1 is select
250 DESCRIPTION
251 from IGS_GR_NOTE_TYPE
252 where ROWID = X_ROWID for update nowait;
253 tlinfo c1%rowtype;
254
255 begin
256 open c1;
257 fetch c1 into tlinfo;
258 if (c1%notfound) then
259 close c1;
260 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
261 app_exception.raise_exception;
262 return;
263 end if;
264 close c1;
265
266 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
267 ) then
268 null;
269 else
270 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
271 app_exception.raise_exception;
272 end if;
273 return;
274 end LOCK_ROW;
275
276 procedure UPDATE_ROW (
277 X_ROWID in VARCHAR2,
278 X_GRD_NOTE_TYPE in VARCHAR2,
279 X_DESCRIPTION in VARCHAR2,
280 X_MODE in VARCHAR2 default 'R'
281 ) AS
282 X_LAST_UPDATE_DATE DATE;
283 X_LAST_UPDATED_BY NUMBER;
284 X_LAST_UPDATE_LOGIN NUMBER;
285 begin
286 X_LAST_UPDATE_DATE := SYSDATE;
287 if(X_MODE = 'I') then
288 X_LAST_UPDATED_BY := 1;
289 X_LAST_UPDATE_LOGIN := 0;
290 elsif (X_MODE = 'R') then
291 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
292 if X_LAST_UPDATED_BY is NULL then
293 X_LAST_UPDATED_BY := -1;
294 end if;
295 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
296 if X_LAST_UPDATE_LOGIN is NULL then
297 X_LAST_UPDATE_LOGIN := -1;
298 end if;
299 else
300 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
301 app_exception.raise_exception;
302 end if;
303
304 Before_DML (
305 p_action => 'UPDATE',
306 x_rowid => X_ROWID,
307 x_grd_note_type => X_GRD_NOTE_TYPE,
308 x_description => X_DESCRIPTION,
309 x_creation_date => X_LAST_UPDATE_DATE,
310 x_created_by => X_LAST_UPDATED_BY,
311 x_last_update_date => X_LAST_UPDATE_DATE,
312 x_last_updated_by => X_LAST_UPDATED_BY,
313 x_last_update_login => X_LAST_UPDATE_LOGIN
314 );
315
316 update IGS_GR_NOTE_TYPE set
317 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
318 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
319 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
320 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
321 where ROWID = X_ROWID
322 ;
323 if (sql%notfound) then
324 raise no_data_found;
325 end if;
326
327 end UPDATE_ROW;
328
329 procedure ADD_ROW (
330 X_ROWID in out NOCOPY VARCHAR2,
331 X_GRD_NOTE_TYPE in VARCHAR2,
332 X_DESCRIPTION in VARCHAR2,
333 X_MODE in VARCHAR2 default 'R'
334 ) AS
335 cursor c1 is select rowid from IGS_GR_NOTE_TYPE
336 where GRD_NOTE_TYPE = X_GRD_NOTE_TYPE
337 ;
338
339 begin
340 open c1;
341 fetch c1 into X_ROWID;
342 if (c1%notfound) then
343 close c1;
344 INSERT_ROW (
345 X_ROWID,
346 X_GRD_NOTE_TYPE,
347 X_DESCRIPTION,
348 X_MODE);
349 return;
350 end if;
351 close c1;
352 UPDATE_ROW (
353 X_ROWID,
354 X_GRD_NOTE_TYPE,
355 X_DESCRIPTION,
356 X_MODE);
357 end ADD_ROW;
358
359 procedure DELETE_ROW (
360 X_ROWID in VARCHAR2
361 ) AS
362 begin
363
364 Before_DML (
365 p_action => 'DELETE',
366 x_rowid => X_ROWID
367 );
368
369 delete from IGS_GR_NOTE_TYPE
370 where ROWID = X_ROWID;
371 if (sql%notfound) then
372 raise no_data_found;
373 end if;
374
375 end DELETE_ROW;
376
377 end IGS_GR_NOTE_TYPE_PKG;