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