1 PACKAGE BODY igf_db_pays_prg_t_pkg AS
2 /* $Header: IGFDI08B.pls 115.2 2002/11/28 14:14:50 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_db_pays_prg_t%ROWTYPE;
6 new_references igf_db_pays_prg_t%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_dbpays_id IN NUMBER ,
12 x_base_id IN NUMBER ,
13 x_program_cd IN VARCHAR2,
14 x_prg_ver_num IN NUMBER ,
15 x_unit_cd IN VARCHAR2,
16 x_unit_ver_num IN NUMBER ,
17 x_creation_date IN DATE ,
18 x_created_by IN NUMBER ,
19 x_last_update_date IN DATE ,
20 x_last_updated_by IN NUMBER ,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : rasingh
25 || Created On : 10-JAN-2002
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igf_db_pays_prg_t
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.dbpays_id := x_dbpays_id;
57 new_references.base_id := x_base_id;
58 new_references.program_cd := x_program_cd;
59 new_references.prg_ver_num := x_prg_ver_num;
60 new_references.unit_cd := x_unit_cd;
61 new_references.unit_ver_num := x_unit_ver_num;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
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
78 FUNCTION get_pk_for_validation (
79 x_dbpays_id IN NUMBER
80 ) RETURN BOOLEAN AS
81 /*
82 || Created By : rasingh
83 || Created On : 10-JAN-2002
84 || Purpose : Validates the Primary Key of the table.
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90 CURSOR cur_rowid IS
91 SELECT rowid
92 FROM igf_db_pays_prg_t
93 WHERE dbpays_id = x_dbpays_id
94 FOR UPDATE NOWAIT;
95
96 lv_rowid cur_rowid%RowType;
97
98 BEGIN
99
100 OPEN cur_rowid;
101 FETCH cur_rowid INTO lv_rowid;
102 IF (cur_rowid%FOUND) THEN
103 CLOSE cur_rowid;
104 RETURN(TRUE);
105 ELSE
106 CLOSE cur_rowid;
107 RETURN(FALSE);
108 END IF;
109
110 END get_pk_for_validation;
111
112
113 PROCEDURE before_dml (
114 p_action IN VARCHAR2,
115 x_rowid IN VARCHAR2,
116 x_dbpays_id IN NUMBER ,
117 x_base_id IN NUMBER ,
118 x_program_cd IN VARCHAR2,
119 x_prg_ver_num IN NUMBER ,
120 x_unit_cd IN VARCHAR2,
121 x_unit_ver_num IN NUMBER ,
122 x_creation_date IN DATE ,
123 x_created_by IN NUMBER ,
124 x_last_update_date IN DATE ,
125 x_last_updated_by IN NUMBER ,
126 x_last_update_login IN NUMBER
127 ) AS
128 /*
129 || Created By : rasingh
130 || Created On : 10-JAN-2002
131 || Purpose : Initialises the columns, Checks Constraints, Calls the
132 || Trigger Handlers for the table, before any DML operation.
133 || Known limitations, enhancements or remarks :
134 || Change History :
135 || Who When What
136 || (reverse chronological order - newest change first)
137 */
138 BEGIN
139
140 set_column_values (
141 p_action,
142 x_rowid,
143 x_dbpays_id,
144 x_base_id,
145 x_program_cd,
146 x_prg_ver_num,
147 x_unit_cd,
148 x_unit_ver_num,
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 ( get_pk_for_validation(
159 new_references.dbpays_id
160 )
161 ) THEN
162 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
163 igs_ge_msg_stack.add;
164 app_exception.raise_exception;
165 END IF;
166 ELSIF (p_action = 'VALIDATE_INSERT') THEN
167 -- Call all the procedures related to Before Insert.
168 IF ( get_pk_for_validation (
169 new_references.dbpays_id
170 )
171 ) THEN
172 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
173 igs_ge_msg_stack.add;
174 app_exception.raise_exception;
175 END IF;
176 END IF;
177
178 END before_dml;
179
180
181 PROCEDURE insert_row (
182 x_rowid IN OUT NOCOPY VARCHAR2,
183 x_dbpays_id IN OUT NOCOPY NUMBER,
184 x_base_id IN NUMBER,
185 x_program_cd IN VARCHAR2,
186 x_prg_ver_num IN NUMBER,
187 x_unit_cd IN VARCHAR2,
188 x_unit_ver_num IN NUMBER,
189 x_mode IN VARCHAR2
190 ) AS
191 /*
192 || Created By : rasingh
193 || Created On : 10-JAN-2002
194 || Purpose : Handles the INSERT DML logic for the table.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 CURSOR c IS
201 SELECT rowid
202 FROM igf_db_pays_prg_t
203 WHERE dbpays_id = x_dbpays_id;
204
205 x_last_update_date DATE;
206 x_last_updated_by NUMBER;
207 x_last_update_login NUMBER;
208
209 BEGIN
210
211 x_last_update_date := SYSDATE;
212 IF (x_mode = 'I') THEN
213 x_last_updated_by := 1;
214 x_last_update_login := 0;
215 ELSIF (x_mode = 'R') THEN
216 x_last_updated_by := fnd_global.user_id;
217 IF (x_last_updated_by IS NULL) THEN
218 x_last_updated_by := -1;
219 END IF;
220 x_last_update_login := fnd_global.login_id;
221 IF (x_last_update_login IS NULL) THEN
222 x_last_update_login := -1;
223 END IF;
224 ELSE
225 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
226 igs_ge_msg_stack.add;
227 app_exception.raise_exception;
228 END IF;
229
230 SELECT igf_db_pays_prg_t_s.NEXTVAL
231 INTO x_dbpays_id
232 FROM dual;
233
234 before_dml(
235 p_action => 'INSERT',
236 x_rowid => x_rowid,
237 x_dbpays_id => x_dbpays_id,
238 x_base_id => x_base_id,
239 x_program_cd => x_program_cd,
240 x_prg_ver_num => x_prg_ver_num,
241 x_unit_cd => x_unit_cd,
242 x_unit_ver_num => x_unit_ver_num,
243 x_creation_date => x_last_update_date,
244 x_created_by => x_last_updated_by,
245 x_last_update_date => x_last_update_date,
246 x_last_updated_by => x_last_updated_by,
247 x_last_update_login => x_last_update_login
248 );
249
250 INSERT INTO igf_db_pays_prg_t (
251 dbpays_id,
252 base_id,
253 program_cd,
254 prg_ver_num,
255 unit_cd,
256 unit_ver_num,
257 creation_date,
258 created_by,
259 last_update_date,
260 last_updated_by,
261 last_update_login
262 ) VALUES (
263 new_references.dbpays_id,
264 new_references.base_id,
265 new_references.program_cd,
266 new_references.prg_ver_num,
267 new_references.unit_cd,
268 new_references.unit_ver_num,
269 x_last_update_date,
270 x_last_updated_by,
271 x_last_update_date,
272 x_last_updated_by,
273 x_last_update_login
274 );
275
276 OPEN c;
277 FETCH c INTO x_rowid;
278 IF (c%NOTFOUND) THEN
279 CLOSE c;
280 RAISE NO_DATA_FOUND;
281 END IF;
282 CLOSE c;
283
284 END insert_row;
285
286
287 PROCEDURE lock_row (
288 x_rowid IN VARCHAR2,
289 x_dbpays_id IN NUMBER,
290 x_base_id IN NUMBER,
291 x_program_cd IN VARCHAR2,
292 x_prg_ver_num IN NUMBER,
293 x_unit_cd IN VARCHAR2,
294 x_unit_ver_num IN NUMBER
295 ) AS
296 /*
297 || Created By : rasingh
298 || Created On : 10-JAN-2002
299 || Purpose : Handles the LOCK mechanism for the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 CURSOR c1 IS
306 SELECT
307 base_id,
308 program_cd,
309 prg_ver_num,
310 unit_cd,
311 unit_ver_num
312 FROM igf_db_pays_prg_t
313 WHERE rowid = x_rowid
314 FOR UPDATE NOWAIT;
315
316 tlinfo c1%ROWTYPE;
317
318 BEGIN
319
320 OPEN c1;
321 FETCH c1 INTO tlinfo;
322 IF (c1%notfound) THEN
323 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
324 igs_ge_msg_stack.add;
325 CLOSE c1;
326 app_exception.raise_exception;
327 RETURN;
328 END IF;
329 CLOSE c1;
330
331 IF (
332 (tlinfo.base_id = x_base_id)
333 AND ((tlinfo.program_cd = x_program_cd) OR ((tlinfo.program_cd IS NULL) AND (X_program_cd IS NULL)))
334 AND ((tlinfo.prg_ver_num = x_prg_ver_num) OR ((tlinfo.prg_ver_num IS NULL) AND (X_prg_ver_num IS NULL)))
335 AND ((tlinfo.unit_cd = x_unit_cd) OR ((tlinfo.unit_cd IS NULL) AND (X_unit_cd IS NULL)))
336 AND ((tlinfo.unit_ver_num = x_unit_ver_num) OR ((tlinfo.unit_ver_num IS NULL) AND (X_unit_ver_num IS NULL)))
337 ) THEN
338 NULL;
339 ELSE
340 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 END IF;
344
345 RETURN;
346
347 END lock_row;
348
349
350 PROCEDURE update_row (
351 x_rowid IN VARCHAR2,
352 x_dbpays_id IN NUMBER,
353 x_base_id IN NUMBER,
354 x_program_cd IN VARCHAR2,
355 x_prg_ver_num IN NUMBER,
356 x_unit_cd IN VARCHAR2,
357 x_unit_ver_num IN NUMBER,
358 x_mode IN VARCHAR2
359 ) AS
360 /*
361 || Created By : rasingh
365 || Change History :
362 || Created On : 10-JAN-2002
363 || Purpose : Handles the UPDATE DML logic for the table.
364 || Known limitations, enhancements or remarks :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 */
369 x_last_update_date DATE ;
370 x_last_updated_by NUMBER;
371 x_last_update_login NUMBER;
372
373 BEGIN
374
375 x_last_update_date := SYSDATE;
376 IF (X_MODE = 'I') THEN
377 x_last_updated_by := 1;
378 x_last_update_login := 0;
379 ELSIF (x_mode = 'R') THEN
380 x_last_updated_by := fnd_global.user_id;
381 IF x_last_updated_by IS NULL THEN
382 x_last_updated_by := -1;
383 END IF;
384 x_last_update_login := fnd_global.login_id;
385 IF (x_last_update_login IS NULL) THEN
386 x_last_update_login := -1;
387 END IF;
388 ELSE
389 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
390 igs_ge_msg_stack.add;
391 app_exception.raise_exception;
392 END IF;
393
394 before_dml(
395 p_action => 'UPDATE',
396 x_rowid => x_rowid,
397 x_dbpays_id => x_dbpays_id,
398 x_base_id => x_base_id,
399 x_program_cd => x_program_cd,
400 x_prg_ver_num => x_prg_ver_num,
401 x_unit_cd => x_unit_cd,
402 x_unit_ver_num => x_unit_ver_num,
403 x_creation_date => x_last_update_date,
404 x_created_by => x_last_updated_by,
405 x_last_update_date => x_last_update_date,
406 x_last_updated_by => x_last_updated_by,
407 x_last_update_login => x_last_update_login
408 );
409
410 UPDATE igf_db_pays_prg_t
411 SET
412 base_id = new_references.base_id,
413 program_cd = new_references.program_cd,
414 prg_ver_num = new_references.prg_ver_num,
415 unit_cd = new_references.unit_cd,
416 unit_ver_num = new_references.unit_ver_num,
417 last_update_date = x_last_update_date,
418 last_updated_by = x_last_updated_by,
419 last_update_login = x_last_update_login
420 WHERE rowid = x_rowid;
421
422 IF (SQL%NOTFOUND) THEN
423 RAISE NO_DATA_FOUND;
424 END IF;
425
426 END update_row;
427
428
429 PROCEDURE add_row (
430 x_rowid IN OUT NOCOPY VARCHAR2,
431 x_dbpays_id IN OUT NOCOPY NUMBER,
432 x_base_id IN NUMBER,
433 x_program_cd IN VARCHAR2,
434 x_prg_ver_num IN NUMBER,
435 x_unit_cd IN VARCHAR2,
436 x_unit_ver_num IN NUMBER,
437 x_mode IN VARCHAR2
438 ) AS
439 /*
440 || Created By : rasingh
441 || Created On : 10-JAN-2002
442 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
443 || Known limitations, enhancements or remarks :
444 || Change History :
445 || Who When What
446 || (reverse chronological order - newest change first)
447 */
448 CURSOR c1 IS
449 SELECT rowid
450 FROM igf_db_pays_prg_t
451 WHERE dbpays_id = x_dbpays_id;
452
453 BEGIN
454
455 OPEN c1;
456 FETCH c1 INTO x_rowid;
457 IF (c1%NOTFOUND) THEN
458 CLOSE c1;
459
460 insert_row (
461 x_rowid,
462 x_dbpays_id,
463 x_base_id,
464 x_program_cd,
465 x_prg_ver_num,
466 x_unit_cd,
467 x_unit_ver_num,
468 x_mode
469 );
470 RETURN;
471 END IF;
472 CLOSE c1;
473
474 update_row (
475 x_rowid,
476 x_dbpays_id,
477 x_base_id,
478 x_program_cd,
479 x_prg_ver_num,
480 x_unit_cd,
481 x_unit_ver_num,
482 x_mode
483 );
484
485 END add_row;
486
487
488 PROCEDURE delete_row (
489 x_rowid IN VARCHAR2
490 ) AS
491 /*
492 || Created By : rasingh
493 || Created On : 10-JAN-2002
494 || Purpose : Handles the DELETE DML logic for the table.
495 || Known limitations, enhancements or remarks :
496 || Change History :
497 || Who When What
498 || (reverse chronological order - newest change first)
499 */
500 BEGIN
501
502 before_dml (
503 p_action => 'DELETE',
504 x_rowid => x_rowid
505 );
506
507 DELETE FROM igf_db_pays_prg_t
508 WHERE rowid = x_rowid;
509
510 IF (SQL%NOTFOUND) THEN
511 RAISE NO_DATA_FOUND;
512 END IF;
513
514 END delete_row;
515
516
517 END igf_db_pays_prg_t_pkg;
[Home] [Help]