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