[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_INV_WAV_DET_PKG
Source
1 PACKAGE BODY igs_fi_inv_wav_det_pkg AS
2 /* $Header: IGSSIB9B.pls 115.4 2002/11/29 04:05:58 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_inv_wav_det%ROWTYPE;
6 new_references igs_fi_inv_wav_det%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_invoice_id IN NUMBER DEFAULT NULL,
12 x_balance_type IN VARCHAR2 DEFAULT NULL,
13 x_start_dt IN DATE DEFAULT NULL,
14 x_end_dt IN DATE 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 : [email protected]
23 || Created On : 28-NOV-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 IGS_FI_INV_WAV_DET
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.invoice_id := x_invoice_id;
55 new_references.balance_type := x_balance_type;
56 new_references.start_dt := x_start_dt;
57 new_references.end_dt := x_end_dt;
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 FUNCTION validate_overlapping(p_invoice_id IGS_FI_INV_WAV_DET.invoice_id%TYPE ,
75 p_balance_type IGS_FI_INV_WAV_DET.balance_type%TYPE,
76 p_start_dt IGS_FI_INV_WAV_DET.start_dt%TYPE,
77 p_end_dt IGS_FI_INV_WAV_DET.end_dt%TYPE,
78 p_row_id VARCHAR2)
79 RETURN BOOLEAN AS
80 /*
81 || Created By : sarakshi
82 || Created On : 03-DEC-2001
83 || Purpose : Validates the overlapping of the effective dates.
84 || Known limitations, enhancements or remarks :
85 || Change History :
86 || Who When What
87 || (reverse chronological order - newest change first)
88 */
89
90 CURSOR cur_overlapping_u(cp_invoice_id IGS_FI_INV_WAV_DET.invoice_id%TYPE ,
91 cp_balance_type IGS_FI_INV_WAV_DET.balance_type%TYPE,
92 cp_start_dt IGS_FI_INV_WAV_DET.start_dt%TYPE,
93 cp_row_id VARCHAR2 ) IS
94 SELECT 'X'
95 FROM igs_fi_inv_wav_det
96 WHERE invoice_id = cp_invoice_id
97 AND balance_type = cp_balance_type
98 AND cp_start_dt >= start_dt AND cp_start_dt <= NVL(end_dt,cp_start_dt)
99 AND (rowid <> cp_row_id OR (cp_row_id IS NULL));
100
101 CURSOR cur_overlapping_u1(cp_invoice_id IGS_FI_INV_WAV_DET.invoice_id%TYPE ,
102 cp_balance_type IGS_FI_INV_WAV_DET.balance_type%TYPE,
103 cp_start_dt IGS_FI_INV_WAV_DET.start_dt%TYPE,
104 cp_end_dt IGS_FI_INV_WAV_DET.end_dt%TYPE,
105 cp_row_id VARCHAR2 ) IS
106 SELECT 'X'
107 FROM igs_fi_inv_wav_det
108 WHERE invoice_id = cp_invoice_id
109 AND balance_type = cp_balance_type
110 AND (cp_end_dt >= start_dt OR (cp_end_dt IS NULL)) AND cp_start_dt <= start_dt
111 AND (rowid <> cp_row_id OR (cp_row_id IS NULL));
112
113 l_temp VARCHAR2(1);
114
115 BEGIN
116 --Validating if effective dates are not overlapping
117
118 -- start date overlapping
119 OPEN cur_overlapping_u(p_invoice_id,p_balance_type,p_start_dt,p_row_id);
120 FETCH cur_overlapping_u INTO l_temp;
121 IF cur_overlapping_u%FOUND THEN
122 CLOSE cur_overlapping_u;
123 RETURN FALSE;
124 END IF;
125 CLOSE cur_overlapping_u;
126
127 --end date overlapping
128 OPEN cur_overlapping_u1(p_invoice_id,p_balance_type,p_start_dt,p_end_dt,p_row_id);
129 FETCH cur_overlapping_u1 INTO l_temp;
130 IF cur_overlapping_u1%FOUND THEN
131 CLOSE cur_overlapping_u1;
132 RETURN FALSE;
133 END IF;
134 CLOSE cur_overlapping_u1;
135
136 RETURN TRUE;
137
138 END validate_overlapping;
139
140
141 PROCEDURE check_parent_existance AS
142 /*
143 || Created By : [email protected]
144 || Created On : 28-NOV-2001
145 || Purpose : Checks for the existance of Parent records.
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 */
151 BEGIN
152
153 IF (((old_references.invoice_id = new_references.invoice_id)) OR
154 ((new_references.invoice_id IS NULL))) THEN
155 NULL;
156 ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation (
157 new_references.invoice_id
158 ) THEN
159 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
160 igs_ge_msg_stack.add;
161 app_exception.raise_exception;
162 END IF;
163
164 IF (((old_references.balance_type = new_references.balance_type)) OR
165 ((new_references.balance_type IS NULL))) THEN
166 NULL;
167 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
168 'IGS_FI_BALANCE_TYPE',
169 new_references.balance_type
170 ) THEN
171 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 END IF;
175
176 END check_parent_existance;
177
178
179 FUNCTION get_pk_for_validation (
180 x_invoice_id IN NUMBER,
181 x_balance_type IN VARCHAR2,
182 x_start_dt IN DATE
183 ) RETURN BOOLEAN AS
184 /*
185 || Created By : [email protected]
186 || Created On : 28-NOV-2001
187 || Purpose : Validates the Primary Key of the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193 CURSOR cur_rowid IS
194 SELECT rowid
195 FROM igs_fi_inv_wav_det
196 WHERE invoice_id = x_invoice_id
197 AND balance_type = x_balance_type
198 AND start_dt = x_start_dt
199 FOR UPDATE NOWAIT;
200
201 lv_rowid cur_rowid%RowType;
202
203 BEGIN
204
205 OPEN cur_rowid;
206 FETCH cur_rowid INTO lv_rowid;
207 IF (cur_rowid%FOUND) THEN
208 CLOSE cur_rowid;
209 RETURN(TRUE);
210 ELSE
211 CLOSE cur_rowid;
212 RETURN(FALSE);
213 END IF;
214
215 END get_pk_for_validation;
216
217
218 PROCEDURE get_fk_igs_fi_inv_int_all (
219 x_invoice_id IN NUMBER
220 ) AS
221 /*
222 || Created By : [email protected]
223 || Created On : 28-NOV-2001
224 || Purpose : Validates the Foreign Keys for the table.
225 || Known limitations, enhancements or remarks :
226 || Change History :
227 || Who When What
228 || (reverse chronological order - newest change first)
229 */
230 CURSOR cur_rowid IS
231 SELECT rowid
232 FROM igs_fi_inv_wav_det
233 WHERE ((invoice_id = x_invoice_id));
234
235 lv_rowid cur_rowid%RowType;
236
237 BEGIN
238
239 OPEN cur_rowid;
240 FETCH cur_rowid INTO lv_rowid;
241 IF (cur_rowid%FOUND) THEN
242 CLOSE cur_rowid;
243 fnd_message.set_name ('IGS', 'IGS_FI_FIW_INVI_FK');
244 igs_ge_msg_stack.add;
245 app_exception.raise_exception;
246 RETURN;
247 END IF;
248 CLOSE cur_rowid;
249
250 END get_fk_igs_fi_inv_int_all;
251
252
253 PROCEDURE before_dml (
254 p_action IN VARCHAR2,
255 x_rowid IN VARCHAR2 DEFAULT NULL,
256 x_invoice_id IN NUMBER DEFAULT NULL,
257 x_balance_type IN VARCHAR2 DEFAULT NULL,
258 x_start_dt IN DATE DEFAULT NULL,
259 x_end_dt IN DATE DEFAULT NULL,
260 x_creation_date IN DATE DEFAULT NULL,
261 x_created_by IN NUMBER DEFAULT NULL,
262 x_last_update_date IN DATE DEFAULT NULL,
263 x_last_updated_by IN NUMBER DEFAULT NULL,
264 x_last_update_login IN NUMBER DEFAULT NULL
265 ) AS
266 /*
267 || Created By : [email protected]
268 || Created On : 28-NOV-2001
269 || Purpose : Initialises the columns, Checks Constraints, Calls the
270 || Trigger Handlers for the table, before any DML operation.
271 || Known limitations, enhancements or remarks :
272 || Change History :
273 || Who When What
274 || (reverse chronological order - newest change first)
275 */
276 BEGIN
277
278 set_column_values (
279 p_action,
280 x_rowid,
281 x_invoice_id,
282 x_balance_type,
283 x_start_dt,
284 x_end_dt,
285 x_creation_date,
286 x_created_by,
287 x_last_update_date,
288 x_last_updated_by,
289 x_last_update_login
290 );
291
292 --Added by sarakshi, to validate the logic of dates overlap
293 IF p_action IN ( 'INSERT', 'VALIDATE_INSERT','UPDATE','VALIDATE_UPDATE') THEN
294 IF NOT validate_overlapping(x_invoice_id , x_balance_type ,
295 x_start_dt , x_end_dt,x_rowid ) THEN
296 fnd_message.set_name('IGS','IGS_FI_WVR_OVERLAP');
297 igs_ge_msg_stack.add;
298 app_exception.raise_exception;
299 END IF;
300 END IF;
301
302 IF (p_action = 'INSERT') THEN
303 -- Call all the procedures related to Before Insert.
304 IF ( get_pk_for_validation(
305 new_references.invoice_id,
306 new_references.balance_type,
307 new_references.start_dt
308 )
309 ) THEN
310 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313 END IF;
314 check_parent_existance;
315 ELSIF (p_action = 'UPDATE') THEN
316 -- Call all the procedures related to Before Update.
317 check_parent_existance;
318 ELSIF (p_action = 'VALIDATE_INSERT') THEN
319 -- Call all the procedures related to Before Insert.
320 IF ( get_pk_for_validation (
321 new_references.invoice_id,
322 new_references.balance_type,
323 new_references.start_dt
324 )
325 ) THEN
326 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 END IF;
330 END IF;
331
332 END before_dml;
333
334
335 PROCEDURE insert_row (
336 x_rowid IN OUT NOCOPY VARCHAR2,
337 x_invoice_id IN NUMBER,
338 x_balance_type IN VARCHAR2,
339 x_start_dt IN DATE,
340 x_end_dt IN DATE,
341 x_mode IN VARCHAR2 DEFAULT 'R'
342 ) AS
343 /*
344 || Created By : [email protected]
345 || Created On : 28-NOV-2001
346 || Purpose : Handles the INSERT DML logic for the table.
347 || Known limitations, enhancements or remarks :
348 || Change History :
349 || Who When What
350 || (reverse chronological order - newest change first)
351 */
352 CURSOR c IS
353 SELECT rowid
354 FROM igs_fi_inv_wav_det
355 WHERE invoice_id = x_invoice_id
356 AND balance_type = x_balance_type
357 AND start_dt = x_start_dt;
358
359 x_last_update_date DATE;
360 x_last_updated_by NUMBER;
361 x_last_update_login NUMBER;
362 x_request_id NUMBER;
363 x_program_id NUMBER;
364 x_program_application_id NUMBER;
365 x_program_update_date DATE;
366
367 BEGIN
368
369 x_last_update_date := SYSDATE;
370 IF (x_mode = 'I') THEN
371 x_last_updated_by := 1;
372 x_last_update_login := 0;
373 ELSIF (x_mode = 'R') THEN
374 x_last_updated_by := fnd_global.user_id;
375 IF (x_last_updated_by IS NULL) THEN
376 x_last_updated_by := -1;
377 END IF;
378 x_last_update_login := fnd_global.login_id;
379 IF (x_last_update_login IS NULL) THEN
380 x_last_update_login := -1;
381 END IF;
382 x_request_id := fnd_global.conc_request_id;
383 x_program_id := fnd_global.conc_program_id;
384 x_program_application_id := fnd_global.prog_appl_id;
385
386 IF (x_request_id = -1) THEN
387 x_request_id := NULL;
388 x_program_id := NULL;
389 x_program_application_id := NULL;
390 x_program_update_date := NULL;
391 ELSE
392 x_program_update_date := SYSDATE;
393 END IF;
394 ELSE
395 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
396 igs_ge_msg_stack.add;
397 app_exception.raise_exception;
398 END IF;
399
400 before_dml(
401 p_action => 'INSERT',
402 x_rowid => x_rowid,
403 x_invoice_id => x_invoice_id,
404 x_balance_type => x_balance_type,
405 x_start_dt => x_start_dt,
406 x_end_dt => x_end_dt,
407 x_creation_date => x_last_update_date,
408 x_created_by => x_last_updated_by,
409 x_last_update_date => x_last_update_date,
410 x_last_updated_by => x_last_updated_by,
411 x_last_update_login => x_last_update_login
412 );
413
414 INSERT INTO igs_fi_inv_wav_det (
415 invoice_id,
416 balance_type,
417 start_dt,
418 end_dt,
419 creation_date,
420 created_by,
421 last_update_date,
422 last_updated_by,
423 last_update_login,
424 request_id,
425 program_id,
426 program_application_id,
427 program_update_date
428 ) VALUES (
429 new_references.invoice_id,
430 new_references.balance_type,
431 new_references.start_dt,
432 new_references.end_dt,
433 x_last_update_date,
434 x_last_updated_by,
435 x_last_update_date,
436 x_last_updated_by,
437 x_last_update_login ,
438 x_request_id,
439 x_program_id,
440 x_program_application_id,
441 x_program_update_date
442 );
443
444 OPEN c;
445 FETCH c INTO x_rowid;
446 IF (c%NOTFOUND) THEN
447 CLOSE c;
448 RAISE NO_DATA_FOUND;
449 END IF;
450 CLOSE c;
451
452 END insert_row;
453
454
455 PROCEDURE lock_row (
456 x_rowid IN VARCHAR2,
457 x_invoice_id IN NUMBER,
458 x_balance_type IN VARCHAR2,
459 x_start_dt IN DATE,
460 x_end_dt IN DATE
461 ) AS
462 /*
463 || Created By : [email protected]
464 || Created On : 28-NOV-2001
465 || Purpose : Handles the LOCK mechanism for the table.
466 || Known limitations, enhancements or remarks :
467 || Change History :
468 || Who When What
469 || (reverse chronological order - newest change first)
470 */
471 CURSOR c1 IS
472 SELECT
473 end_dt
474 FROM igs_fi_inv_wav_det
475 WHERE rowid = x_rowid
476 FOR UPDATE NOWAIT;
477
478 tlinfo c1%ROWTYPE;
479
480 BEGIN
481
482 OPEN c1;
483 FETCH c1 INTO tlinfo;
484 IF (c1%notfound) THEN
485 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
486 igs_ge_msg_stack.add;
487 CLOSE c1;
488 app_exception.raise_exception;
489 RETURN;
490 END IF;
491 CLOSE c1;
492
493 IF (
494 ((tlinfo.end_dt = x_end_dt) OR ((tlinfo.end_dt IS NULL) AND (X_end_dt IS NULL)))
495 ) THEN
496 NULL;
497 ELSE
498 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
499 igs_ge_msg_stack.add;
500 app_exception.raise_exception;
501 END IF;
502
503 RETURN;
504
505 END lock_row;
506
507
508 PROCEDURE update_row (
509 x_rowid IN VARCHAR2,
510 x_invoice_id IN NUMBER,
511 x_balance_type IN VARCHAR2,
512 x_start_dt IN DATE,
513 x_end_dt IN DATE,
514 x_mode IN VARCHAR2 DEFAULT 'R'
515 ) AS
516 /*
517 || Created By : [email protected]
518 || Created On : 28-NOV-2001
519 || Purpose : Handles the UPDATE DML logic for the table.
520 || Known limitations, enhancements or remarks :
521 || Change History :
522 || Who When What
523 || (reverse chronological order - newest change first)
524 */
525 x_last_update_date DATE ;
526 x_last_updated_by NUMBER;
527 x_last_update_login NUMBER;
528 x_request_id NUMBER;
529 x_program_id NUMBER;
530 x_program_application_id NUMBER;
531 x_program_update_date DATE;
532
533 BEGIN
534
535 x_last_update_date := SYSDATE;
536 IF (X_MODE = 'I') THEN
537 x_last_updated_by := 1;
538 x_last_update_login := 0;
539 ELSIF (x_mode = 'R') THEN
540 x_last_updated_by := fnd_global.user_id;
541 IF x_last_updated_by IS NULL THEN
542 x_last_updated_by := -1;
543 END IF;
544 x_last_update_login := fnd_global.login_id;
545 IF (x_last_update_login IS NULL) THEN
546 x_last_update_login := -1;
547 END IF;
548 ELSE
549 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
550 igs_ge_msg_stack.add;
551 app_exception.raise_exception;
552 END IF;
553
554 before_dml(
555 p_action => 'UPDATE',
556 x_rowid => x_rowid,
557 x_invoice_id => x_invoice_id,
558 x_balance_type => x_balance_type,
559 x_start_dt => x_start_dt,
560 x_end_dt => x_end_dt,
561 x_creation_date => x_last_update_date,
562 x_created_by => x_last_updated_by,
563 x_last_update_date => x_last_update_date,
564 x_last_updated_by => x_last_updated_by,
565 x_last_update_login => x_last_update_login
566 );
567
568 IF (x_mode = 'R') THEN
569 x_request_id := fnd_global.conc_request_id;
570 x_program_id := fnd_global.conc_program_id;
571 x_program_application_id := fnd_global.prog_appl_id;
572 IF (x_request_id = -1) THEN
573 x_request_id := old_references.request_id;
574 x_program_id := old_references.program_id;
575 x_program_application_id := old_references.program_application_id;
576 x_program_update_date := old_references.program_update_date;
577 ELSE
578 x_program_update_date := SYSDATE;
579 END IF;
580 END IF;
581
582 UPDATE igs_fi_inv_wav_det
583 SET
584 end_dt = new_references.end_dt,
585 last_update_date = x_last_update_date,
586 last_updated_by = x_last_updated_by,
587 last_update_login = x_last_update_login ,
588 request_id = x_request_id,
589 program_id = x_program_id,
590 program_application_id = x_program_application_id,
591 program_update_date = x_program_update_date
592 WHERE rowid = x_rowid;
593
594 IF (SQL%NOTFOUND) THEN
595 RAISE NO_DATA_FOUND;
596 END IF;
597
598 END update_row;
599
600
601 PROCEDURE add_row (
602 x_rowid IN OUT NOCOPY VARCHAR2,
603 x_invoice_id IN NUMBER,
604 x_balance_type IN VARCHAR2,
605 x_start_dt IN DATE,
606 x_end_dt IN DATE,
607 x_mode IN VARCHAR2 DEFAULT 'R'
608 ) AS
609 /*
610 || Created By : [email protected]
611 || Created On : 28-NOV-2001
612 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
613 || Known limitations, enhancements or remarks :
614 || Change History :
615 || Who When What
616 || (reverse chronological order - newest change first)
617 */
618 CURSOR c1 IS
619 SELECT rowid
620 FROM igs_fi_inv_wav_det
621 WHERE invoice_id = x_invoice_id
622 AND balance_type = x_balance_type
623 AND start_dt = x_start_dt;
624
625 BEGIN
626
627 OPEN c1;
628 FETCH c1 INTO x_rowid;
629 IF (c1%NOTFOUND) THEN
630 CLOSE c1;
631
632 insert_row (
633 x_rowid,
634 x_invoice_id,
635 x_balance_type,
636 x_start_dt,
637 x_end_dt,
638 x_mode
639 );
640 RETURN;
641 END IF;
642 CLOSE c1;
643
644 update_row (
645 x_rowid,
646 x_invoice_id,
647 x_balance_type,
648 x_start_dt,
649 x_end_dt,
650 x_mode
651 );
652
653 END add_row;
654
655
656 PROCEDURE delete_row (
657 x_rowid IN VARCHAR2
658 ) AS
659 /*
660 || Created By : [email protected]
661 || Created On : 28-NOV-2001
662 || Purpose : Handles the DELETE DML logic for the table.
663 || Known limitations, enhancements or remarks :
664 || Change History :
665 || Who When What
666 || (reverse chronological order - newest change first)
667 */
668 BEGIN
669
670 before_dml (
671 p_action => 'DELETE',
672 x_rowid => x_rowid
673 );
674
675 DELETE FROM igs_fi_inv_wav_det
676 WHERE rowid = x_rowid;
677
678 IF (SQL%NOTFOUND) THEN
679 RAISE NO_DATA_FOUND;
680 END IF;
681
682 END delete_row;
683
684
685 END igs_fi_inv_wav_det_pkg;