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