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