1 PACKAGE BODY igs_ps_exp_wl_pkg AS
2 /* $Header: IGSPI3DB.pls 115.4 2003/07/24 13:33:40 shtatiko noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_exp_wl%ROWTYPE;
6 new_references igs_ps_exp_wl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_calendar_cat 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 || Created By : smaddali
20 || Created On : 17-JAN-2002
21 || Purpose : Initialises the Old and New references for the columns of the table.
22 || Known limitations, enhancements or remarks :
23 || Change History :
24 || Who When What
25 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
26 || (reverse chronological order - newest change first)
27 */
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM igs_ps_exp_wl
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.calendar_cat := x_calendar_cat;
53
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END set_column_values;
67
68 FUNCTION get_pk_for_validation (
69 x_calendar_cat IN VARCHAR2
70 ) RETURN BOOLEAN AS
71 /*
72 || Created By : smaddali
73 || Created On : 17-JAN-2002
74 || Purpose : Validates the Primary Key of the table.
75 || Known limitations, enhancements or remarks :
76 || Change History :
77 || Who When What
78 || (reverse chronological order - newest change first)
79 */
80 CURSOR cur_rowid IS
81 SELECT rowid
82 FROM igs_ps_exp_wl
83 WHERE calendar_cat = x_calendar_cat
84 FOR UPDATE NOWAIT;
85
86 lv_rowid cur_rowid%RowType;
87
88 BEGIN
89
90 OPEN cur_rowid;
91 FETCH cur_rowid INTO lv_rowid;
92 IF (cur_rowid%FOUND) THEN
93 CLOSE cur_rowid;
94 RETURN(TRUE);
95 ELSE
96 CLOSE cur_rowid;
97 RETURN(FALSE);
98 END IF;
99
100 END get_pk_for_validation;
101
102 -- check if there is already aone record in this table .
103 --If so , don't let insertion of the second record
104 PROCEDURE BeforeRowInsert AS
105 l_count_recs NUMBER(2);
106 CURSOR c_num_of_recs IS
107 SELECT count(*)
108 FROM igs_ps_exp_wl ;
109
110 BEGIN
111 OPEN c_num_of_recs ;
112 FETCH c_num_of_recs INTO l_count_recs ;
113 CLOSE c_num_of_recs ;
114 IF l_count_recs > 0 THEN
115 Fnd_Message.Set_Name('IGS', 'IGS_PS_ONLY_ONE_REC');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 END IF;
119 RETURN ;
120 END BeforeRowInsert ;
121
122 PROCEDURE before_dml (
123 p_action IN VARCHAR2,
124 x_rowid IN VARCHAR2 DEFAULT NULL,
125 x_calendar_cat IN VARCHAR2 DEFAULT NULL,
126 x_creation_date IN DATE DEFAULT NULL,
127 x_created_by IN NUMBER DEFAULT NULL,
128 x_last_update_date IN DATE DEFAULT NULL,
129 x_last_updated_by IN NUMBER DEFAULT NULL,
130 x_last_update_login IN NUMBER DEFAULT NULL
131 ) AS
132 /*
133 || Created By : smaddali
134 || Created On : 17-JAN-2002
135 || Purpose : Initialises the columns, Checks Constraints, Calls the
136 || Trigger Handlers for the table, before any DML operation.
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || (reverse chronological order - newest change first)
141 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
142 */
143 BEGIN
144
145 set_column_values (
146 p_action,
147 x_rowid,
148 x_calendar_cat,
149 x_creation_date,
150 x_created_by,
151 x_last_update_date,
152 x_last_updated_by,
153 x_last_update_login
154 );
155
156 IF (p_action = 'INSERT') THEN
157 -- Call all the procedures related to Before Insert.
158 -- If there is already one record in this table then don't let the new record to be inserted
159 BeforeRowInsert ;
160 IF ( get_pk_for_validation(
161 new_references.calendar_cat
162 )
163 ) THEN
164 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
165 igs_ge_msg_stack.add;
166 app_exception.raise_exception;
167 END IF;
168 ELSIF (p_action = 'VALIDATE_INSERT') THEN
169 -- Call all the procedures related to Before Insert.
170 IF ( get_pk_for_validation (
171 new_references.calendar_cat
172 )
173 ) THEN
174 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
175 igs_ge_msg_stack.add;
176 app_exception.raise_exception;
177 END IF;
178 END IF;
179
180 END before_dml;
181
182
183 PROCEDURE insert_row (
184 x_rowid IN OUT NOCOPY VARCHAR2,
185 x_calendar_cat IN VARCHAR2,
186 x_mode IN VARCHAR2 DEFAULT 'R'
187 ) AS
188 /*
189 || Created By : smaddali
190 || Created On : 17-JAN-2002
191 || Purpose : Handles the INSERT DML logic for the table.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
197 */
198 CURSOR c IS
199 SELECT rowid
200 FROM igs_ps_exp_wl
201 WHERE calendar_cat = x_calendar_cat;
202
203 x_last_update_date DATE;
204 x_last_updated_by NUMBER;
205 x_last_update_login NUMBER;
206
207 BEGIN
208
209 x_last_update_date := SYSDATE;
210 IF (x_mode = 'I') THEN
211 x_last_updated_by := 1;
212 x_last_update_login := 0;
213 ELSIF (x_mode = 'R') THEN
214 x_last_updated_by := fnd_global.user_id;
215 IF (x_last_updated_by IS NULL) THEN
216 x_last_updated_by := -1;
217 END IF;
218 x_last_update_login := fnd_global.login_id;
219 IF (x_last_update_login IS NULL) THEN
220 x_last_update_login := -1;
221 END IF;
222 ELSE
223 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END IF;
227
228 before_dml(
229 p_action => 'INSERT',
230 x_rowid => x_rowid,
231 x_calendar_cat => x_calendar_cat,
232 x_creation_date => x_last_update_date,
233 x_created_by => x_last_updated_by,
234 x_last_update_date => x_last_update_date,
235 x_last_updated_by => x_last_updated_by,
236 x_last_update_login => x_last_update_login
237 );
238
239 INSERT INTO igs_ps_exp_wl (
240 calendar_cat,
241 creation_date,
242 created_by,
243 last_update_date,
244 last_updated_by,
245 last_update_login
246 ) VALUES (
247 new_references.calendar_cat,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_date,
251 x_last_updated_by,
252 x_last_update_login
253 );
254
255 OPEN c;
256 FETCH c INTO x_rowid;
257 IF (c%NOTFOUND) THEN
258 CLOSE c;
259 RAISE NO_DATA_FOUND;
260 END IF;
261 CLOSE c;
262
263 END insert_row;
264
265
266 PROCEDURE lock_row (
267 x_rowid IN VARCHAR2,
268 x_calendar_cat IN VARCHAR2
269 ) AS
270 /*
271 || Created By : smaddali
272 || Created On : 17-JAN-2002
273 || Purpose : Handles the LOCK mechanism for the table.
274 || Known limitations, enhancements or remarks :
275 || Change History :
276 || Who When What
277 || (reverse chronological order - newest change first)
278 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
279 */
280 CURSOR c1 IS
281 SELECT
282 calendar_cat
283 FROM igs_ps_exp_wl
284 WHERE rowid = x_rowid
285 FOR UPDATE NOWAIT;
286
287 tlinfo c1%ROWTYPE;
288
289 BEGIN
290
291 OPEN c1;
292 FETCH c1 INTO tlinfo;
293 IF (c1%notfound) THEN
294 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
295 igs_ge_msg_stack.add;
296 CLOSE c1;
297 app_exception.raise_exception;
298 RETURN;
299 END IF;
300 CLOSE c1;
301
302 IF (
303 (tlinfo.calendar_cat = x_calendar_cat)
304 ) THEN
305 NULL;
306 ELSE
307 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
308 igs_ge_msg_stack.add;
309 app_exception.raise_exception;
310 END IF;
311
312 RETURN;
313
314 END lock_row;
315
316
317 PROCEDURE update_row (
318 x_rowid IN VARCHAR2,
319 x_calendar_cat IN VARCHAR2,
320 x_mode IN VARCHAR2 DEFAULT 'R'
321 ) AS
322 /*
323 || Created By : smaddali
324 || Created On : 17-JAN-2002
325 || Purpose : Handles the UPDATE DML logic for the table.
326 || Known limitations, enhancements or remarks :
327 || Change History :
328 || Who When What
329 || (reverse chronological order - newest change first)
330 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
331 */
332 x_last_update_date DATE ;
333 x_last_updated_by NUMBER;
334 x_last_update_login NUMBER;
335
336 BEGIN
337
338 x_last_update_date := SYSDATE;
339 IF (X_MODE = 'I') THEN
340 x_last_updated_by := 1;
341 x_last_update_login := 0;
342 ELSIF (x_mode = 'R') THEN
343 x_last_updated_by := fnd_global.user_id;
344 IF x_last_updated_by IS NULL THEN
345 x_last_updated_by := -1;
346 END IF;
347 x_last_update_login := fnd_global.login_id;
348 IF (x_last_update_login IS NULL) THEN
349 x_last_update_login := -1;
350 END IF;
351 ELSE
352 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
353 igs_ge_msg_stack.add;
354 app_exception.raise_exception;
355 END IF;
356
357 before_dml(
358 p_action => 'UPDATE',
359 x_rowid => x_rowid,
360 x_calendar_cat => x_calendar_cat,
361 x_creation_date => x_last_update_date,
362 x_created_by => x_last_updated_by,
363 x_last_update_date => x_last_update_date,
364 x_last_updated_by => x_last_updated_by,
365 x_last_update_login => x_last_update_login
366 );
367
368 UPDATE igs_ps_exp_wl
369 SET
370 calendar_cat = new_references.calendar_cat,
371 last_update_date = x_last_update_date,
372 last_updated_by = x_last_updated_by,
373 last_update_login = x_last_update_login
374 WHERE rowid = x_rowid;
375
376 IF (SQL%NOTFOUND) THEN
377 RAISE NO_DATA_FOUND;
378 END IF;
379
380 END update_row;
381
382
383 PROCEDURE add_row (
384 x_rowid IN OUT NOCOPY VARCHAR2,
385 x_calendar_cat IN VARCHAR2,
386 x_mode IN VARCHAR2 DEFAULT 'R'
387 ) AS
388 /*
389 || Created By : smaddali
390 || Created On : 17-JAN-2002
391 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
392 || Known limitations, enhancements or remarks :
393 || Change History :
394 || Who When What
395 || (reverse chronological order - newest change first)
396 || shtatiko 24-JUL-2003 Bug# 2986863, Removed references to obsoleted column, control_num
397 */
398 CURSOR c1 IS
399 SELECT rowid
400 FROM igs_ps_exp_wl
401 WHERE calendar_cat = x_calendar_cat;
402
403 BEGIN
404
405 OPEN c1;
406 FETCH c1 INTO x_rowid;
407 IF (c1%NOTFOUND) THEN
408 CLOSE c1;
409
410 insert_row (
411 x_rowid,
412 x_calendar_cat,
413 x_mode
414 );
415 RETURN;
416 END IF;
417 CLOSE c1;
418
419 update_row (
420 x_rowid,
421 x_calendar_cat,
422 x_mode
423 );
424
425 END add_row;
426
427
428 PROCEDURE delete_row (
429 x_rowid IN VARCHAR2
430 ) AS
431 /*
432 || Created By : smaddali
433 || Created On : 17-JAN-2002
434 || Purpose : Handles the DELETE DML logic for the table.
435 || Known limitations, enhancements or remarks :
436 || Change History :
437 || Who When What
438 || (reverse chronological order - newest change first)
439 */
440 BEGIN
441
442 before_dml (
443 p_action => 'DELETE',
444 x_rowid => x_rowid
445 );
446
447 DELETE FROM igs_ps_exp_wl
448 WHERE rowid = x_rowid;
449
450 IF (SQL%NOTFOUND) THEN
451 RAISE NO_DATA_FOUND;
452 END IF;
453
454 END delete_row;
455
456
457 END igs_ps_exp_wl_pkg;
[Home] [Help]