[Home] [Help]
PACKAGE BODY: APPS.IGS_GE_S_LOG_PKG
Source
1 package body IGS_GE_S_LOG_PKG as
2 /* $Header: IGSMI10B.pls 115.4 2002/11/29 01:11:35 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 -- kumma 13-JUN-2002 Removed procedure GET_FK_IGS_OG_TYPE, 2410165
7 -------------------------------------------------------------------------------------------
8 l_rowid VARCHAR2(25);
9 old_references IGS_GE_S_LOG%RowType;
10 new_references IGS_GE_S_LOG%RowType;
11
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 DEFAULT NULL,
15 x_s_log_type IN VARCHAR2 DEFAULT NULL,
16 x_creation_dt IN DATE DEFAULT NULL,
17 x_key IN VARCHAR2 DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) as
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_GE_S_LOG
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.s_log_type := x_s_log_type;
49 new_references.creation_dt := x_creation_dt;
50 new_references.key := x_key;
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_Constraints(
65 Column_Name IN VARCHAR2 DEFAULT NULL,
66 Column_Value IN VARCHAR2 DEFAULT NULL
67 ) as
68 BEGIN
69 IF column_name is null then
70 NULL;
71 ELSIF upper(Column_name) = 'KEY' then
72 new_references.key := column_value;
73 ELSIF upper(Column_name) = 'S_LOG_TYPE' then
74 new_references.s_log_type := column_value;
75 END IF;
76 IF upper(Column_name) = 'KEY' OR column_name is null then
77 IF new_references.key <> UPPER(new_references.key ) then
78 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
79 IGS_GE_MSG_STACK.ADD;
80 App_Exception.Raise_Exception;
81 END IF;
82 END IF;
83 IF upper(Column_name) = 'S_LOG_TYPE' OR column_name is null then
84 IF new_references.s_log_type <> UPPER(new_references.s_log_type) then
85 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 END IF;
90 END Check_Constraints;
91
92
93 PROCEDURE Check_Parent_Existance as
94 BEGIN
95
96 IF (((old_references.s_log_type = new_references.s_log_type)) OR
97 ((new_references.s_log_type IS NULL))) THEN
98 NULL;
99 ELSE
100 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation ( 'LOG_TYPE',new_references.s_log_type ) THEN
101 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107 END Check_Parent_Existance;
108
109 PROCEDURE Check_Child_Existance as
110 BEGIN
111
112 IGS_GE_S_LOG_ENTRY_PKG.GET_FK_IGS_GE_S_LOG (
113 old_references.s_log_type,
114 old_references.creation_dt
115 );
116
117 END Check_Child_Existance;
118
119 FUNCTION GET_PK_FOR_VALIDATION (
120 x_s_log_type IN VARCHAR2,
121 x_creation_dt IN DATE
122 ) RETURN BOOLEAN as
123
124 CURSOR cur_rowid IS
125 SELECT rowid
126 FROM IGS_GE_S_LOG
127 WHERE s_log_type = x_s_log_type
128 AND creation_dt = x_creation_dt
129 FOR UPDATE NOWAIT;
130
131 lv_rowid cur_rowid%RowType;
132
133 BEGIN
134
135 Open cur_rowid;
136 Fetch cur_rowid INTO lv_rowid;
137 IF (cur_rowid%FOUND) THEN
138 Close cur_rowid;
139 Return(TRUE);
140 ELSE
141 Close cur_rowid;
142 Return(FALSE);
143 END IF;
144
145 END Get_PK_For_Validation;
146
147
148 PROCEDURE Before_DML (
149 p_action IN VARCHAR2,
150 x_rowid IN VARCHAR2 DEFAULT NULL,
151 x_s_log_type IN VARCHAR2 DEFAULT NULL,
152 x_creation_dt IN DATE DEFAULT NULL,
153 x_key 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_s_log_type,
166 x_creation_dt,
167 x_key,
168 x_creation_date,
169 x_created_by,
170 x_last_update_date,
171 x_last_updated_by,
172 x_last_update_login
173 );
174
175 IF (p_action = 'INSERT') THEN
176 IF Get_PK_For_Validation(
177 new_references.s_log_type,
178 new_references.creation_dt
179 )THEN
180 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 Check_Constraints;
185 Check_Parent_Existance;
186 ELSIF (p_action = 'UPDATE') THEN
187 -- Call all the procedures related to Before Update.
188 Check_Constraints;
189 Check_Parent_Existance;
190 ELSIF (p_action = 'DELETE') THEN
191 -- Call all the procedures related to Before Delete.
192 Check_Child_Existance;
193 ELSIF (p_action = 'VALIDATE_INSERT') THEN
194 IF Get_PK_For_Validation(
195 new_references.s_log_type,
196 new_references.creation_dt
197 )THEN
198 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END IF;
202 Check_Constraints;
203 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
204 Check_Constraints;
205 ELSIF (p_action = 'VALIDATE_DELETE') THEN
206 Check_Child_Existance;
207 END IF;
208
209 END Before_DML;
210
211 PROCEDURE After_DML (
212 p_action IN VARCHAR2,
213 x_rowid IN VARCHAR2
214 ) as
215 BEGIN
216
217 l_rowid := x_rowid;
218
219 IF (p_action = 'INSERT') THEN
220 -- Call all the procedures related to After Insert.
221 Null;
222 ELSIF (p_action = 'UPDATE') THEN
223 -- Call all the procedures related to After Update.
224 Null;
225 ELSIF (p_action = 'DELETE') THEN
226 -- Call all the procedures related to After Delete.
227 Null;
228 END IF;
229
230 END After_DML;
231
232 procedure INSERT_ROW (
233 X_ROWID in out NOCOPY VARCHAR2,
234 X_S_LOG_TYPE in VARCHAR2,
235 X_CREATION_DT in DATE,
236 X_KEY in VARCHAR2,
237 X_MODE in VARCHAR2 default 'R'
238 ) as
239 cursor C is select ROWID from IGS_GE_S_LOG
240 where S_LOG_TYPE = X_S_LOG_TYPE
241 and CREATION_DT = X_CREATION_DT;
242 X_LAST_UPDATE_DATE DATE;
243 X_LAST_UPDATED_BY NUMBER;
244 X_LAST_UPDATE_LOGIN NUMBER;
245 X_REQUEST_ID NUMBER;
246 X_PROGRAM_ID NUMBER;
247 X_PROGRAM_APPLICATION_ID NUMBER;
248 X_PROGRAM_UPDATE_DATE DATE;
249 begin
250 X_LAST_UPDATE_DATE := SYSDATE;
251 if(X_MODE = 'I') then
252 X_LAST_UPDATED_BY := 1;
253 X_LAST_UPDATE_LOGIN := 0;
254 elsif (X_MODE = 'R') then
255 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
256 if X_LAST_UPDATED_BY is NULL then
257 X_LAST_UPDATED_BY := -1;
258 end if;
259 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
260 if X_LAST_UPDATE_LOGIN is NULL then
261 X_LAST_UPDATE_LOGIN := -1;
262 end if;
263 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
264 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
265 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
266 if (X_REQUEST_ID = -1) then
267 X_REQUEST_ID := NULL;
268 X_PROGRAM_ID := NULL;
269 X_PROGRAM_APPLICATION_ID := NULL;
270 X_PROGRAM_UPDATE_DATE := NULL;
271 else
272 X_PROGRAM_UPDATE_DATE := SYSDATE;
273 end if;
274
275 else
276 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
277 IGS_GE_MSG_STACK.ADD;
278 app_exception.raise_exception;
279 end if;
280
281 Before_DML (
282 p_action => 'INSERT',
283 x_rowid => X_ROWID,
284 x_s_log_type => X_S_LOG_TYPE,
285 x_creation_dt => X_CREATION_DT,
286 x_key => X_KEY,
287 x_created_by => X_LAST_UPDATED_BY,
288 x_creation_date =>X_LAST_UPDATE_DATE,
289 x_last_updated_by => X_LAST_UPDATED_BY,
290 x_last_update_date => X_LAST_UPDATE_DATE,
291 x_last_update_login => X_LAST_UPDATE_LOGIN
292 );
293 insert into IGS_GE_S_LOG (
294 S_LOG_TYPE,
295 CREATION_DT,
296 KEY,
297 CREATION_DATE,
298 CREATED_BY,
299 LAST_UPDATE_DATE,
300 LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN,
302 REQUEST_ID,
303 PROGRAM_ID,
304 PROGRAM_APPLICATION_ID,
305 PROGRAM_UPDATE_DATE
306 ) values (
307 NEW_REFERENCES.S_LOG_TYPE,
308 NEW_REFERENCES.CREATION_DT,
309 NEW_REFERENCES.KEY,
310 X_LAST_UPDATE_DATE,
311 X_LAST_UPDATED_BY,
312 X_LAST_UPDATE_DATE,
313 X_LAST_UPDATED_BY,
314 X_LAST_UPDATE_LOGIN,
315 X_REQUEST_ID,
316 X_PROGRAM_ID,
317 X_PROGRAM_APPLICATION_ID,
318 X_PROGRAM_UPDATE_DATE
319 );
320
321 open c;
322 fetch c into X_ROWID;
323 if (c%notfound) then
324 close c;
325 raise no_data_found;
326 end if;
327 close c;
328 After_DML (
329 p_action => 'INSERT',
330 x_rowid => X_ROWID
331 );
332 end INSERT_ROW;
333
334 procedure LOCK_ROW (
335 X_ROWID in VARCHAR2,
336 X_S_LOG_TYPE in VARCHAR2,
337 X_CREATION_DT in DATE,
338 X_KEY in VARCHAR2
339 ) as
340 cursor c1 is select
341 KEY
342 from IGS_GE_S_LOG
343 where ROWID = X_ROWID
344 for update nowait;
345 tlinfo c1%rowtype;
346 begin
347 open c1;
348 fetch c1 into tlinfo;
349 if (c1%notfound) then
350 close c1;
351 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
352 IGS_GE_MSG_STACK.ADD;
353 app_exception.raise_exception;
354 return;
355 end if;
356 close c1;
357
358 if ( ((tlinfo.KEY = X_KEY)
359 OR ((tlinfo.KEY is null)
360 AND (X_KEY is null)))
361 ) then
362 null;
363 else
364 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
365 IGS_GE_MSG_STACK.ADD;
366 app_exception.raise_exception;
367 end if;
368 return;
369 end LOCK_ROW;
370
371 procedure UPDATE_ROW (
372 X_ROWID in VARCHAR2,
373 X_S_LOG_TYPE in VARCHAR2,
374 X_CREATION_DT in DATE,
375 X_KEY in VARCHAR2,
376 X_MODE in VARCHAR2 default 'R'
377 ) as
378 X_LAST_UPDATE_DATE DATE;
379 X_LAST_UPDATED_BY NUMBER;
380 X_LAST_UPDATE_LOGIN NUMBER;
381 X_REQUEST_ID NUMBER;
382 X_PROGRAM_ID NUMBER;
383 X_PROGRAM_APPLICATION_ID NUMBER;
384 X_PROGRAM_UPDATE_DATE DATE;
385 begin
386 X_LAST_UPDATE_DATE := SYSDATE;
387 if(X_MODE = 'I') then
388 X_LAST_UPDATED_BY := 1;
389 X_LAST_UPDATE_LOGIN := 0;
390 elsif (X_MODE = 'R') then
391 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
392 if X_LAST_UPDATED_BY is NULL then
393 X_LAST_UPDATED_BY := -1;
394 end if;
395 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
396 if X_LAST_UPDATE_LOGIN is NULL then
397 X_LAST_UPDATE_LOGIN := -1;
398 end if;
399 else
400 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
401 IGS_GE_MSG_STACK.ADD;
402 app_exception.raise_exception;
403 end if;
404 Before_DML (
405 p_action => 'UPDATE',
406 x_rowid => X_ROWID,
407 x_s_log_type => X_S_LOG_TYPE,
408 x_creation_dt => X_CREATION_DT,
409 x_key => X_KEY,
410 x_created_by =>X_LAST_UPDATED_BY,
411 x_creation_date => X_LAST_UPDATE_DATE,
412 x_last_updated_by => X_LAST_UPDATED_BY,
413 x_last_update_date => X_LAST_UPDATE_DATE,
414 x_last_update_login => X_LAST_UPDATE_LOGIN
415 );
416
417 if(X_MODE='R') then
418 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
419 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
420 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
421 if (X_REQUEST_ID = -1) then
422 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
423 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
424 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
425 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
426 else
427 X_PROGRAM_UPDATE_DATE := SYSDATE;
428 end if;
429 end if;
430
431 update IGS_GE_S_LOG set
432 KEY = NEW_REFERENCES.KEY,
433 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
434 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
435 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
436 REQUEST_ID = X_REQUEST_ID,
437 PROGRAM_ID = X_PROGRAM_ID,
438 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
439 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
440 where ROWID = X_ROWID;
441
442 if (sql%notfound) then
443 raise no_data_found;
444 end if;
445 After_DML (
446 p_action => 'UPDATE',
447 x_rowid => X_ROWID
448 );
449 end UPDATE_ROW;
450
451 procedure ADD_ROW (
452 X_ROWID in out NOCOPY VARCHAR2,
453 X_S_LOG_TYPE in VARCHAR2,
454 X_CREATION_DT in DATE,
455 X_KEY in VARCHAR2,
456 X_MODE in VARCHAR2 default 'R'
457 ) as
458 cursor c1 is select rowid from IGS_GE_S_LOG
459 where S_LOG_TYPE = X_S_LOG_TYPE
460 and CREATION_DT = X_CREATION_DT
461 ;
462
463 begin
464 open c1;
465 fetch c1 into X_ROWID;
466 if (c1%notfound) then
467 close c1;
468 INSERT_ROW (
469 X_ROWID,
470 X_S_LOG_TYPE,
471 X_CREATION_DT,
472 X_KEY,
473 X_MODE);
474 return;
475 end if;
476 close c1;
477 UPDATE_ROW (
478 X_ROWID,
479 X_S_LOG_TYPE,
480 X_CREATION_DT,
481 X_KEY,
482 X_MODE);
483 end ADD_ROW;
484
485 procedure DELETE_ROW (
486 X_ROWID in VARCHAR2
487 ) as
488 begin
489 Before_DML (
490 p_action => 'DELETE',
491 x_rowid => X_ROWID
492 );
493 delete from IGS_GE_S_LOG
494 where ROWID = X_ROWID;
495 if (sql%notfound) then
496 raise no_data_found;
497 end if;
498 After_DML (
499 p_action => 'DELETE',
500 x_rowid => X_ROWID
501 );
502
503 end DELETE_ROW;
504
505 end IGS_GE_S_LOG_PKG;