1 PACKAGE BODY igf_aw_fund_unit_pkg AS
2 /* $Header: IGFWI48B.pls 115.1 2002/11/28 12:17:40 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_fund_unit_all%ROWTYPE;
6 new_references igf_aw_fund_unit_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_funit_id IN NUMBER DEFAULT NULL,
12 x_fund_id IN NUMBER DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER 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 : rasingh
23 || Created On : 24-DEC-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_fund_unit_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.funit_id := x_funit_id;
55 new_references.fund_id := x_fund_id;
56 new_references.unit_cd := x_unit_cd;
57 new_references.version_number := x_version_number;
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 : rasingh
77 || Created On : 24-DEC-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.fund_id,
88 new_references.unit_cd,
89 new_references.version_number
90 )
91 ) THEN
92 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 END IF;
96
97 END check_uniqueness;
98
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : rasingh
103 || Created On : 24-DEC-2001
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 || (reverse chronological order - newest change first)
109 */
110 BEGIN
111
112 IF (((old_references.fund_id = new_references.fund_id)) OR
113 ((new_references.fund_id IS NULL))) THEN
114 NULL;
115 ELSIF NOT igf_aw_fund_mast_pkg.get_pk_for_validation (
116 new_references.fund_id
117 ) THEN
118 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
119 igs_ge_msg_stack.add;
120 app_exception.raise_exception;
121 END IF;
122
123 END check_parent_existance;
124
125
126 FUNCTION get_pk_for_validation (
127 x_funit_id IN NUMBER
128 ) RETURN BOOLEAN AS
129 /*
130 || Created By : rasingh
131 || Created On : 24-DEC-2001
132 || Purpose : Validates the Primary Key of the table.
133 || Known limitations, enhancements or remarks :
134 || Change History :
135 || Who When What
136 || (reverse chronological order - newest change first)
137 */
138 CURSOR cur_rowid IS
139 SELECT rowid
140 FROM igf_aw_fund_unit_all
141 WHERE funit_id = x_funit_id
142 FOR UPDATE NOWAIT;
143
144 lv_rowid cur_rowid%RowType;
145
146 BEGIN
147
148 OPEN cur_rowid;
149 FETCH cur_rowid INTO lv_rowid;
150 IF (cur_rowid%FOUND) THEN
151 CLOSE cur_rowid;
152 RETURN(TRUE);
153 ELSE
154 CLOSE cur_rowid;
155 RETURN(FALSE);
156 END IF;
157
158 END get_pk_for_validation;
159
160
161 FUNCTION get_uk_for_validation (
162 x_fund_id IN NUMBER,
163 x_unit_cd IN VARCHAR2,
164 x_version_number IN NUMBER
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By : rasingh
168 || Created On : 24-DEC-2001
169 || Purpose : Validates the Unique Keys of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM igf_aw_fund_unit
178 WHERE fund_id = x_fund_id
179 AND unit_cd = x_unit_cd
180 AND version_number = x_version_number
181 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
182
183 lv_rowid cur_rowid%RowType;
184
185 BEGIN
186
187 OPEN cur_rowid;
188 FETCH cur_rowid INTO lv_rowid;
189 IF (cur_rowid%FOUND) THEN
190 CLOSE cur_rowid;
191 RETURN (true);
192 ELSE
193 CLOSE cur_rowid;
194 RETURN(FALSE);
195 END IF;
196
197 END get_uk_for_validation ;
198
199
200 PROCEDURE get_fk_igf_aw_fund_mast (
201 x_fund_id IN NUMBER
202 ) AS
203 /*
204 || Created By : rasingh
205 || Created On : 24-DEC-2001
206 || Purpose : Validates the Foreign Keys for the table.
207 || Known limitations, enhancements or remarks :
208 || Change History :
209 || Who When What
210 || (reverse chronological order - newest change first)
211 */
212 CURSOR cur_rowid IS
213 SELECT rowid
214 FROM igf_aw_fund_unit_all
215 WHERE ((fund_id = x_fund_id));
216
217 lv_rowid cur_rowid%RowType;
218
219 BEGIN
220
221 OPEN cur_rowid;
222 FETCH cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 CLOSE cur_rowid;
225 fnd_message.set_name ('IGF', 'IGF_AP_FUNIT_FMAST_FK');
226 igs_ge_msg_stack.add;
227 app_exception.raise_exception;
228 RETURN;
229 END IF;
230 CLOSE cur_rowid;
231
232 END get_fk_igf_aw_fund_mast;
233
234
235 PROCEDURE before_dml (
236 p_action IN VARCHAR2,
237 x_rowid IN VARCHAR2 DEFAULT NULL,
238 x_funit_id IN NUMBER DEFAULT NULL,
239 x_fund_id IN NUMBER DEFAULT NULL,
240 x_unit_cd IN VARCHAR2 DEFAULT NULL,
241 x_version_number IN NUMBER DEFAULT NULL,
242 x_creation_date IN DATE DEFAULT NULL,
243 x_created_by IN NUMBER DEFAULT NULL,
244 x_last_update_date IN DATE DEFAULT NULL,
245 x_last_updated_by IN NUMBER DEFAULT NULL,
246 x_last_update_login IN NUMBER DEFAULT NULL
247 ) AS
248 /*
249 || Created By : rasingh
250 || Created On : 24-DEC-2001
251 || Purpose : Initialises the columns, Checks Constraints, Calls the
252 || Trigger Handlers for the table, before any DML operation.
253 || Known limitations, enhancements or remarks :
254 || Change History :
255 || Who When What
256 || (reverse chronological order - newest change first)
257 */
258 BEGIN
259
260 set_column_values (
261 p_action,
262 x_rowid,
263 x_funit_id,
264 x_fund_id,
265 x_unit_cd,
266 x_version_number,
267 x_creation_date,
268 x_created_by,
269 x_last_update_date,
270 x_last_updated_by,
271 x_last_update_login
272 );
273
274 IF (p_action = 'INSERT') THEN
275 -- Call all the procedures related to Before Insert.
276 IF ( get_pk_for_validation(
277 new_references.funit_id
278 )
279 ) THEN
280 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
281 igs_ge_msg_stack.add;
282 app_exception.raise_exception;
283 END IF;
284 check_uniqueness;
285 check_parent_existance;
286 ELSIF (p_action = 'UPDATE') THEN
287 -- Call all the procedures related to Before Update.
288 check_uniqueness;
289 check_parent_existance;
290 ELSIF (p_action = 'VALIDATE_INSERT') THEN
291 -- Call all the procedures related to Before Insert.
292 IF ( get_pk_for_validation (
293 new_references.funit_id
294 )
295 ) THEN
296 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
297 igs_ge_msg_stack.add;
298 app_exception.raise_exception;
299 END IF;
300 check_uniqueness;
301 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
302 check_uniqueness;
303 END IF;
304
305 END before_dml;
306
307
308 PROCEDURE insert_row (
309 x_rowid IN OUT NOCOPY VARCHAR2,
310 x_funit_id IN OUT NOCOPY NUMBER,
311 x_fund_id IN NUMBER,
312 x_unit_cd IN VARCHAR2,
313 x_version_number IN NUMBER,
314 x_mode IN VARCHAR2 DEFAULT 'R'
315 ) AS
316 /*
317 || Created By : rasingh
318 || Created On : 24-DEC-2001
319 || Purpose : Handles the INSERT DML logic for the table.
320 || Known limitations, enhancements or remarks :
321 || Change History :
322 || Who When What
323 || (reverse chronological order - newest change first)
324 */
325 CURSOR c IS
326 SELECT rowid
327 FROM igf_aw_fund_unit_all
328 WHERE funit_id = x_funit_id;
329
330 x_last_update_date DATE;
331 x_last_updated_by NUMBER;
332 x_last_update_login NUMBER;
333
334 BEGIN
335
336 x_last_update_date := SYSDATE;
337 IF (x_mode = 'I') THEN
338 x_last_updated_by := 1;
339 x_last_update_login := 0;
340 ELSIF (x_mode = 'R') THEN
341 x_last_updated_by := fnd_global.user_id;
342 IF (x_last_updated_by IS NULL) THEN
343 x_last_updated_by := -1;
344 END IF;
345 x_last_update_login := fnd_global.login_id;
346 IF (x_last_update_login IS NULL) THEN
347 x_last_update_login := -1;
348 END IF;
349 ELSE
350 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
351 igs_ge_msg_stack.add;
352 app_exception.raise_exception;
353 END IF;
354
355 SELECT igf_aw_fund_unit_s.NEXTVAL
356 INTO x_funit_id
357 FROM dual;
358
359 new_references.org_id := igs_ge_gen_003.get_org_id;
360
361 before_dml(
362 p_action => 'INSERT',
363 x_rowid => x_rowid,
364 x_funit_id => x_funit_id,
365 x_fund_id => x_fund_id,
366 x_unit_cd => x_unit_cd,
367 x_version_number => x_version_number,
368 x_creation_date => x_last_update_date,
369 x_created_by => x_last_updated_by,
370 x_last_update_date => x_last_update_date,
371 x_last_updated_by => x_last_updated_by,
372 x_last_update_login => x_last_update_login
373 );
374
375 INSERT INTO igf_aw_fund_unit_all (
376 funit_id,
377 fund_id,
378 unit_cd,
379 version_number,
380 org_id,
381 creation_date,
382 created_by,
383 last_update_date,
384 last_updated_by,
385 last_update_login
386 ) VALUES (
387 new_references.funit_id,
388 new_references.fund_id,
389 new_references.unit_cd,
390 new_references.version_number,
391 new_references.org_id,
392 x_last_update_date,
393 x_last_updated_by,
394 x_last_update_date,
395 x_last_updated_by,
396 x_last_update_login
397 );
398
399 OPEN c;
400 FETCH c INTO x_rowid;
401 IF (c%NOTFOUND) THEN
402 CLOSE c;
403 RAISE NO_DATA_FOUND;
404 END IF;
405 CLOSE c;
406
407 END insert_row;
408
409
410 PROCEDURE lock_row (
411 x_rowid IN VARCHAR2,
412 x_funit_id IN NUMBER,
413 x_fund_id IN NUMBER,
414 x_unit_cd IN VARCHAR2,
415 x_version_number IN NUMBER
416 ) AS
417 /*
418 || Created By : rasingh
419 || Created On : 24-DEC-2001
420 || Purpose : Handles the LOCK mechanism for the table.
421 || Known limitations, enhancements or remarks :
422 || Change History :
423 || Who When What
424 || (reverse chronological order - newest change first)
425 */
426 CURSOR c1 IS
427 SELECT
428 fund_id,
429 unit_cd,
430 version_number
431 FROM igf_aw_fund_unit_all
432 WHERE rowid = x_rowid
433 FOR UPDATE NOWAIT;
434
435 tlinfo c1%ROWTYPE;
436
437 BEGIN
438
439 OPEN c1;
440 FETCH c1 INTO tlinfo;
441 IF (c1%notfound) THEN
442 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
443 igs_ge_msg_stack.add;
444 CLOSE c1;
445 app_exception.raise_exception;
446 RETURN;
447 END IF;
448 CLOSE c1;
449
450 IF (
451 (tlinfo.fund_id = x_fund_id)
452 AND (tlinfo.unit_cd = x_unit_cd)
453 AND (tlinfo.version_number = x_version_number)
454 ) THEN
455 NULL;
456 ELSE
457 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
458 igs_ge_msg_stack.add;
459 app_exception.raise_exception;
460 END IF;
461
462 RETURN;
463
464 END lock_row;
465
466
467 PROCEDURE update_row (
468 x_rowid IN VARCHAR2,
469 x_funit_id IN NUMBER,
470 x_fund_id IN NUMBER,
471 x_unit_cd IN VARCHAR2,
472 x_version_number IN NUMBER,
473 x_mode IN VARCHAR2 DEFAULT 'R'
474 ) AS
475 /*
476 || Created By : rasingh
477 || Created On : 24-DEC-2001
478 || Purpose : Handles the UPDATE DML logic for the table.
479 || Known limitations, enhancements or remarks :
480 || Change History :
481 || Who When What
482 || (reverse chronological order - newest change first)
483 */
484 x_last_update_date DATE ;
485 x_last_updated_by NUMBER;
486 x_last_update_login NUMBER;
487
488 BEGIN
489
490 x_last_update_date := SYSDATE;
491 IF (X_MODE = 'I') THEN
492 x_last_updated_by := 1;
493 x_last_update_login := 0;
494 ELSIF (x_mode = 'R') THEN
495 x_last_updated_by := fnd_global.user_id;
496 IF x_last_updated_by IS NULL THEN
497 x_last_updated_by := -1;
498 END IF;
499 x_last_update_login := fnd_global.login_id;
500 IF (x_last_update_login IS NULL) THEN
501 x_last_update_login := -1;
502 END IF;
503 ELSE
504 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
505 igs_ge_msg_stack.add;
506 app_exception.raise_exception;
507 END IF;
508
509 before_dml(
510 p_action => 'UPDATE',
511 x_rowid => x_rowid,
512 x_funit_id => x_funit_id,
513 x_fund_id => x_fund_id,
514 x_unit_cd => x_unit_cd,
515 x_version_number => x_version_number,
516 x_creation_date => x_last_update_date,
517 x_created_by => x_last_updated_by,
518 x_last_update_date => x_last_update_date,
519 x_last_updated_by => x_last_updated_by,
520 x_last_update_login => x_last_update_login
521 );
522
523 UPDATE igf_aw_fund_unit_all
524 SET
525 fund_id = new_references.fund_id,
526 unit_cd = new_references.unit_cd,
527 version_number = new_references.version_number,
528 last_update_date = x_last_update_date,
529 last_updated_by = x_last_updated_by,
530 last_update_login = x_last_update_login
531 WHERE rowid = x_rowid;
532
533 IF (SQL%NOTFOUND) THEN
534 RAISE NO_DATA_FOUND;
535 END IF;
536
537 END update_row;
538
539
540 PROCEDURE add_row (
541 x_rowid IN OUT NOCOPY VARCHAR2,
542 x_funit_id IN OUT NOCOPY NUMBER,
543 x_fund_id IN NUMBER,
544 x_unit_cd IN VARCHAR2,
545 x_version_number IN NUMBER,
546 x_mode IN VARCHAR2 DEFAULT 'R'
547 ) AS
548 /*
549 || Created By : rasingh
550 || Created On : 24-DEC-2001
551 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
552 || Known limitations, enhancements or remarks :
553 || Change History :
554 || Who When What
555 || (reverse chronological order - newest change first)
556 */
557 CURSOR c1 IS
558 SELECT rowid
559 FROM igf_aw_fund_unit_all
560 WHERE funit_id = x_funit_id;
561
562 BEGIN
563
564 OPEN c1;
565 FETCH c1 INTO x_rowid;
566 IF (c1%NOTFOUND) THEN
567 CLOSE c1;
568
569 insert_row (
570 x_rowid,
571 x_funit_id,
572 x_fund_id,
573 x_unit_cd,
574 x_version_number,
575 x_mode
576 );
577 RETURN;
578 END IF;
579 CLOSE c1;
580
581 update_row (
582 x_rowid,
583 x_funit_id,
584 x_fund_id,
585 x_unit_cd,
586 x_version_number,
587 x_mode
588 );
589
590 END add_row;
591
592
593 PROCEDURE delete_row (
594 x_rowid IN VARCHAR2
595 ) AS
596 /*
597 || Created By : rasingh
598 || Created On : 24-DEC-2001
599 || Purpose : Handles the DELETE DML logic for the table.
600 || Known limitations, enhancements or remarks :
601 || Change History :
602 || Who When What
603 || (reverse chronological order - newest change first)
604 */
605 BEGIN
606
607 before_dml (
608 p_action => 'DELETE',
609 x_rowid => x_rowid
610 );
611
612 DELETE FROM igf_aw_fund_unit_all
613 WHERE rowid = x_rowid;
614
615 IF (SQL%NOTFOUND) THEN
616 RAISE NO_DATA_FOUND;
617 END IF;
618
619 END delete_row;
620
621
622 END igf_aw_fund_unit_pkg;
[Home] [Help]