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