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