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