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