1 PACKAGE BODY igf_aw_awd_rvsn_rsn_pkg AS
2 /* $Header: IGFWI32B.pls 115.7 2002/11/28 14:41:05 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_awd_rvsn_rsn_all%ROWTYPE;
6 new_references igf_aw_awd_rvsn_rsn_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_rvsn_id IN NUMBER DEFAULT NULL,
12 x_rvsn_code IN VARCHAR2 DEFAULT NULL,
13 x_descp IN VARCHAR2 DEFAULT NULL,
14 x_active 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 ) AS
21 /*
22 || Created By : prchandr
23 || Created On : 01-JUN-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGF_AW_AWD_RVSN_RSN_ALL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.rvsn_id := x_rvsn_id;
55 new_references.rvsn_code := x_rvsn_code;
56 new_references.descp := x_descp;
57 new_references.active := x_active;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 PROCEDURE check_uniqueness AS
75 /*
76 || Created By : prchandr
77 || Created On : 01-JUN-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF ( get_uk_for_validation (
87 new_references.rvsn_code,
88 new_references.org_id
89 )
90 ) THEN
91 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
92 igs_ge_msg_stack.add;
93 app_exception.raise_exception;
94 END IF;
95
96 END check_uniqueness;
97
98
99 PROCEDURE check_child_existance IS
100 /*
101 || Created By : prchandr
102 || Created On : 01-JUN-2001
103 || Purpose : Checks for the existance of Child records.
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 */
109 BEGIN
110
111 igf_aw_award_pkg.get_fk_igf_aw_awd_rvsn_rsn (
112 old_references.rvsn_id
113 );
114
115 igf_aw_awd_disb_pkg.get_fk_igf_aw_awd_rvsn_rsn (
116 old_references.rvsn_id
117 );
118
119 END check_child_existance;
120
121
122 FUNCTION get_pk_for_validation (
123 x_rvsn_id IN NUMBER
124 ) RETURN BOOLEAN AS
125 /*
126 || Created By : prchandr
127 || Created On : 01-JUN-2001
128 || Purpose : Validates the Primary Key of the table.
129 || Known limitations, enhancements or remarks :
130 || Change History :
131 || Who When What
132 || (reverse chronological order - newest change first)
133 */
134 CURSOR cur_rowid IS
135 SELECT rowid
136 FROM igf_aw_awd_rvsn_rsn_all
137 WHERE rvsn_id = x_rvsn_id
138 FOR UPDATE NOWAIT;
139
140 lv_rowid cur_rowid%RowType;
141
142 BEGIN
143
144 OPEN cur_rowid;
145 FETCH cur_rowid INTO lv_rowid;
146 IF (cur_rowid%FOUND) THEN
147 CLOSE cur_rowid;
148 RETURN(TRUE);
149 ELSE
150 CLOSE cur_rowid;
151 RETURN(FALSE);
152
153 END IF;
154
155 END get_pk_for_validation;
156
157
158 FUNCTION get_uk_for_validation (
159 x_rvsn_code IN VARCHAR2,
160 x_org_id IN NUMBER
161 ) RETURN BOOLEAN AS
162 /*
163 || Created By : prchandr
164 || Created On : 01-JUN-2001
165 || Purpose : Validates the Unique Keys of the table.
166 || Known limitations, enhancements or remarks :
167 || Change History :
168 || Who When What
169 || vvutukur 18-feb-20022 modified org_id check in cur_rowid cursor and selected from igf_aw_awd_rvsn_rsn instead
170 || of igf_aw_aed_rvsn_rsn_all for bug : 2222272 as part of SWSCR006 Build.
171 || adhawan 23-feb-02' made the primary key incasesensitive by adding the UPPER clause
172 || 2168913
173 || (reverse chronological order - newest change first)
174 */
175
176 l_org_id igf_aw_awd_rvsn_rsn_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
177
178 CURSOR cur_rowid IS
179 SELECT rowid
180 FROM igf_aw_awd_rvsn_rsn
181 WHERE UPPER(rvsn_code) = UPPER(x_rvsn_code) -- BUG 2168913
182 AND NVL(org_id,NVL(l_org_id,-99)) = NVL(l_org_id,-99) --bug 2222272
183 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
184
185 lv_rowid cur_rowid%RowType;
186
187 BEGIN
188
189 OPEN cur_rowid;
190 FETCH cur_rowid INTO lv_rowid;
191 IF (cur_rowid%FOUND) THEN
192 CLOSE cur_rowid;
193 RETURN (true);
194 ELSE
195 CLOSE cur_rowid;
196 RETURN(FALSE);
197 END IF;
198
199 END get_uk_for_validation ;
200
201
202 PROCEDURE before_dml (
203 p_action IN VARCHAR2,
204 x_rowid IN VARCHAR2 DEFAULT NULL,
205 x_rvsn_id IN NUMBER DEFAULT NULL,
206 x_rvsn_code IN VARCHAR2 DEFAULT NULL,
207 x_descp IN VARCHAR2 DEFAULT NULL,
208 x_active IN VARCHAR2 DEFAULT NULL,
209 x_creation_date IN DATE DEFAULT NULL,
210 x_created_by IN NUMBER DEFAULT NULL,
211 x_last_update_date IN DATE DEFAULT NULL,
212 x_last_updated_by IN NUMBER DEFAULT NULL,
213 x_last_update_login IN NUMBER DEFAULT NULL
214 ) AS
215 /*
216 || Created By : prchandr
217 || Created On : 01-JUN-2001
218 || Purpose : Initialises the columns, Checks Constraints, Calls the
219 || Trigger Handlers for the table, before any DML operation.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 BEGIN
226
227 set_column_values (
228 p_action,
229 x_rowid,
230 x_rvsn_id,
231 x_rvsn_code,
232 x_descp,
233 x_active,
234 x_creation_date,
235 x_created_by,
236 x_last_update_date,
237 x_last_updated_by,
238 x_last_update_login
239 );
240
241 IF (p_action = 'INSERT') THEN
242 -- Call all the procedures related to Before Insert.
243 IF ( get_pk_for_validation(
244 new_references.rvsn_id
245 )
246 ) THEN
247 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 END IF;
251 check_uniqueness;
252 ELSIF (p_action = 'UPDATE') THEN
253 -- Call all the procedures related to Before Update.
254 check_uniqueness;
255 ELSIF (p_action = 'DELETE') THEN
256 -- Call all the procedures related to Before Delete.
257 check_child_existance;
258 ELSIF (p_action = 'VALIDATE_INSERT') THEN
259 -- Call all the procedures related to Before Insert.
260 IF ( get_pk_for_validation (
261 new_references.rvsn_id
262 )
263 ) THEN
264 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268 check_uniqueness;
269 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
270 check_uniqueness;
271 ELSIF (p_action = 'VALIDATE_DELETE') THEN
272 check_child_existance;
273 END IF;
274
275 END before_dml;
276
277
278 PROCEDURE insert_row (
279 x_rowid IN OUT NOCOPY VARCHAR2,
280 x_rvsn_id IN OUT NOCOPY NUMBER,
281 x_rvsn_code IN VARCHAR2,
282 x_descp IN VARCHAR2,
283 x_active IN VARCHAR2,
284 x_mode IN VARCHAR2 DEFAULT 'R'
285 ) AS
286 /*
287 || Created By : prchandr
288 || Created On : 01-JUN-2001
289 || Purpose : Handles the INSERT DML logic for the table.
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 || (reverse chronological order - newest change first)
294 */
295 CURSOR c IS
296 SELECT rowid
297 FROM igf_aw_awd_rvsn_rsn_all
298 WHERE rvsn_id = x_rvsn_id;
299
300 x_last_update_date DATE;
301 x_last_updated_by NUMBER;
302 x_last_update_login NUMBER;
303
304 BEGIN
305
306 x_last_update_date := SYSDATE;
307 IF (x_mode = 'I') THEN
308 x_last_updated_by := 1;
309 x_last_update_login := 0;
310 ELSIF (x_mode = 'R') THEN
311 x_last_updated_by := fnd_global.user_id;
312 IF (x_last_updated_by IS NULL) THEN
313 x_last_updated_by := -1;
314 END IF;
315 x_last_update_login := fnd_global.login_id;
316 IF (x_last_update_login IS NULL) THEN
317 x_last_update_login := -1;
318 END IF;
319 ELSE
320 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
321 igs_ge_msg_stack.add;
322 app_exception.raise_exception;
323 END IF;
324
325 SELECT igf_aw_awd_rvsn_rsn_s.NEXTVAL
326 INTO x_rvsn_id
327 FROM dual;
328
329 new_references.org_id := igs_ge_gen_003.get_org_id;
330
331 before_dml(
332 p_action => 'INSERT',
333 x_rowid => x_rowid,
334 x_rvsn_id => x_rvsn_id,
335 x_rvsn_code => x_rvsn_code,
336 x_descp => x_descp,
337 x_active => x_active,
338 x_creation_date => x_last_update_date,
339 x_created_by => x_last_updated_by,
340 x_last_update_date => x_last_update_date,
341 x_last_updated_by => x_last_updated_by,
342 x_last_update_login => x_last_update_login
343 );
344
345 INSERT INTO igf_aw_awd_rvsn_rsn_all (
346 rvsn_id,
347 rvsn_code,
348 descp,
349 active,
350 org_id,
351 creation_date,
352 created_by,
353 last_update_date,
354 last_updated_by,
355 last_update_login
356 ) VALUES (
357 new_references.rvsn_id,
358 new_references.rvsn_code,
359 new_references.descp,
360 new_references.active,
361 new_references.org_id,
362 x_last_update_date,
363 x_last_updated_by,
364 x_last_update_date,
365 x_last_updated_by,
366 x_last_update_login
367 );
368
369 OPEN c;
370 FETCH c INTO x_rowid;
371 IF (c%NOTFOUND) THEN
372 CLOSE c;
373 RAISE NO_DATA_FOUND;
374 END IF;
375 CLOSE c;
376
377 END insert_row;
378
379
380 PROCEDURE lock_row (
381 x_rowid IN VARCHAR2,
382 x_rvsn_id IN NUMBER,
383 x_rvsn_code IN VARCHAR2,
384 x_descp IN VARCHAR2,
385 x_active IN VARCHAR2
386 ) AS
387 /*
388 || Created By : prchandr
389 || Created On : 01-JUN-2001
390 || Purpose : Handles the LOCK mechanism for the table.
391 || Known limitations, enhancements or remarks :
392 || Change History :
393 || Who When What
394 || (reverse chronological order - newest change first)
395 */
396 CURSOR c1 IS
397 SELECT
398 rvsn_code,
399 descp,
400 active
401 FROM igf_aw_awd_rvsn_rsn_all
402 WHERE rowid = x_rowid
403 FOR UPDATE NOWAIT;
404
405 tlinfo c1%ROWTYPE;
406
407 BEGIN
408
409 OPEN c1;
410 FETCH c1 INTO tlinfo;
411 IF (c1%notfound) THEN
412 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
413 igs_ge_msg_stack.add;
414 CLOSE c1;
415 app_exception.raise_exception;
416 RETURN;
417 END IF;
418 CLOSE c1;
419
420 IF (
421 (tlinfo.rvsn_code = x_rvsn_code)
422 AND (tlinfo.descp = x_descp)
423 AND (tlinfo.active = x_active)
424 ) THEN
425 NULL;
426 ELSE
427 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
428 igs_ge_msg_stack.add;
429 app_exception.raise_exception;
430 END IF;
431
432 RETURN;
433
434 END lock_row;
435
436
437 PROCEDURE update_row (
438 x_rowid IN VARCHAR2,
439 x_rvsn_id IN NUMBER,
440 x_rvsn_code IN VARCHAR2,
441 x_descp IN VARCHAR2,
442 x_active IN VARCHAR2,
443 x_mode IN VARCHAR2 DEFAULT 'R'
444 ) AS
445 /*
446 || Created By : prchandr
447 || Created On : 01-JUN-2001
448 || Purpose : Handles the UPDATE DML logic for the table.
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
452 || (reverse chronological order - newest change first)
453 */
454 x_last_update_date DATE ;
455 x_last_updated_by NUMBER;
456 x_last_update_login NUMBER;
457
458 BEGIN
459
460 x_last_update_date := SYSDATE;
461 IF (X_MODE = 'I') THEN
462 x_last_updated_by := 1;
463 x_last_update_login := 0;
464 ELSIF (x_mode = 'R') THEN
465 x_last_updated_by := fnd_global.user_id;
466 IF x_last_updated_by IS NULL THEN
467 x_last_updated_by := -1;
468 END IF;
469 x_last_update_login := fnd_global.login_id;
470 IF (x_last_update_login IS NULL) THEN
471 x_last_update_login := -1;
472 END IF;
473 ELSE
474 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
475 igs_ge_msg_stack.add;
476 app_exception.raise_exception;
477 END IF;
478
479 before_dml(
480 p_action => 'UPDATE',
481 x_rowid => x_rowid,
482 x_rvsn_id => x_rvsn_id,
483 x_rvsn_code => x_rvsn_code,
484 x_descp => x_descp,
485 x_active => x_active,
486 x_creation_date => x_last_update_date,
487 x_created_by => x_last_updated_by,
488 x_last_update_date => x_last_update_date,
489 x_last_updated_by => x_last_updated_by,
490 x_last_update_login => x_last_update_login
491 );
492
493 UPDATE igf_aw_awd_rvsn_rsn_all
494 SET
495 rvsn_code = new_references.rvsn_code,
496 descp = new_references.descp,
497 active = new_references.active,
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 WHERE rowid = x_rowid;
502
503 IF (SQL%NOTFOUND) THEN
504 RAISE NO_DATA_FOUND;
505 END IF;
506
507 END update_row;
508
509
510 PROCEDURE add_row (
511 x_rowid IN OUT NOCOPY VARCHAR2,
512 x_rvsn_id IN OUT NOCOPY NUMBER,
513 x_rvsn_code IN VARCHAR2,
514 x_descp IN VARCHAR2,
515 x_active IN VARCHAR2,
516 x_mode IN VARCHAR2 DEFAULT 'R'
517 ) AS
518 /*
519 || Created By : prchandr
520 || Created On : 01-JUN-2001
521 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
522 || Known limitations, enhancements or remarks :
523 || Change History :
524 || Who When What
525 || (reverse chronological order - newest change first)
526 */
527 CURSOR c1 IS
528 SELECT rowid
529 FROM igf_aw_awd_rvsn_rsn_all
530 WHERE rvsn_id = x_rvsn_id;
531
532 BEGIN
533
534 OPEN c1;
535 FETCH c1 INTO x_rowid;
536 IF (c1%NOTFOUND) THEN
537 CLOSE c1;
538
539 insert_row (
540 x_rowid,
541 x_rvsn_id,
542 x_rvsn_code,
543 x_descp,
544 x_active,
545 x_mode
546 );
547 RETURN;
548 END IF;
549 CLOSE c1;
550
551 update_row (
552 x_rowid,
553 x_rvsn_id,
554 x_rvsn_code,
555 x_descp,
556 x_active,
557 x_mode
558 );
559
560 END add_row;
561
562
563 PROCEDURE delete_row (
564 x_rowid IN VARCHAR2
565 ) AS
566 /*
567 || Created By : prchandr
568 || Created On : 01-JUN-2001
569 || Purpose : Handles the DELETE DML logic for the table.
570 || Known limitations, enhancements or remarks :
571 || Change History :
572 || Who When What
573 || (reverse chronological order - newest change first)
574 */
575 BEGIN
576
577 before_dml (
578 p_action => 'DELETE',
579 x_rowid => x_rowid
580 );
581
582 DELETE FROM igf_aw_awd_rvsn_rsn_all
583 WHERE rowid = x_rowid;
584
585 IF (SQL%NOTFOUND) THEN
586 RAISE NO_DATA_FOUND;
587 END IF;
588
589 END delete_row;
590
591
592 END igf_aw_awd_rvsn_rsn_pkg;
[Home] [Help]