[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_REFUND_INT_PKG
Source
1 PACKAGE BODY igs_fi_refund_int_pkg AS
2 /* $Header: IGSSIC3B.pls 115.8 2003/02/19 08:05:43 pathipat noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_refund_int_all%ROWTYPE;
6 new_references igs_fi_refund_int_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_refund_id IN NUMBER ,
12 x_voucher_date IN DATE ,
13 x_person_id IN NUMBER ,
14 x_pay_person_id IN NUMBER ,
15 x_dr_gl_ccid IN NUMBER ,
16 x_cr_gl_ccid IN NUMBER ,
17 x_dr_account_cd IN VARCHAR2 ,
18 x_cr_account_cd IN VARCHAR2 ,
19 x_refund_amount IN NUMBER ,
20 x_fee_type IN VARCHAR2 ,
21 x_fee_cal_type IN VARCHAR2 ,
22 x_fee_ci_sequence_number IN NUMBER ,
23 x_source_refund_id IN NUMBER ,
24 x_invoice_id IN NUMBER ,
25 x_reason IN VARCHAR2 ,
26 x_attribute_category IN VARCHAR2 ,
27 x_attribute1 IN VARCHAR2 ,
28 x_attribute2 IN VARCHAR2 ,
29 x_attribute3 IN VARCHAR2 ,
30 x_attribute4 IN VARCHAR2 ,
31 x_attribute5 IN VARCHAR2 ,
32 x_attribute6 IN VARCHAR2 ,
33 x_attribute7 IN VARCHAR2 ,
34 x_attribute8 IN VARCHAR2 ,
35 x_attribute9 IN VARCHAR2 ,
36 x_attribute10 IN VARCHAR2 ,
37 x_attribute11 IN VARCHAR2 ,
38 x_attribute12 IN VARCHAR2 ,
39 x_attribute13 IN VARCHAR2 ,
40 x_attribute14 IN VARCHAR2 ,
41 x_attribute15 IN VARCHAR2 ,
42 x_attribute16 IN VARCHAR2 ,
43 x_attribute17 IN VARCHAR2 ,
44 x_attribute18 IN VARCHAR2 ,
45 x_attribute19 IN VARCHAR2 ,
46 x_attribute20 IN VARCHAR2 ,
47 x_creation_date IN DATE ,
48 x_created_by IN NUMBER ,
49 x_last_update_date IN DATE ,
50 x_last_updated_by IN NUMBER ,
51 x_last_update_login IN NUMBER ,
52 x_gl_date IN DATE
53 ) AS
54 /*
55 || Created By : [email protected]
56 || Created On : 27-FEB-2002
57 || Purpose : Initialises the Old and New references for the columns of the table.
58 || Known limitations, enhancements or remarks :
59 || Change History :
60 || Who When What
61 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
62 || (reverse chronological order - newest change first)
63 */
64
65 CURSOR cur_old_ref_values IS
66 SELECT *
67 FROM igs_fi_refund_int_all
68 WHERE rowid = x_rowid;
69
70 BEGIN
71
72 l_rowid := x_rowid;
73
74 -- Code for setting the Old and New Reference Values.
75 -- Populate Old Values.
76 OPEN cur_old_ref_values;
77 FETCH cur_old_ref_values INTO old_references;
78 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
79 CLOSE cur_old_ref_values;
80 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
81 igs_ge_msg_stack.add;
82 app_exception.raise_exception;
83 RETURN;
84 END IF;
85 CLOSE cur_old_ref_values;
86
87 -- Populate New Values.
88 new_references.refund_id := x_refund_id;
89 new_references.voucher_date := x_voucher_date;
90 new_references.person_id := x_person_id;
91 new_references.pay_person_id := x_pay_person_id;
92 new_references.dr_gl_ccid := x_dr_gl_ccid;
93 new_references.cr_gl_ccid := x_cr_gl_ccid;
94 new_references.dr_account_cd := x_dr_account_cd;
95 new_references.cr_account_cd := x_cr_account_cd;
96 new_references.refund_amount := x_refund_amount;
97 new_references.fee_type := x_fee_type;
98 new_references.fee_cal_type := x_fee_cal_type;
99 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
100 new_references.source_refund_id := x_source_refund_id;
101 new_references.invoice_id := x_invoice_id;
102 new_references.reason := x_reason;
103 new_references.attribute_category := x_attribute_category;
104 new_references.attribute1 := x_attribute1;
105 new_references.attribute2 := x_attribute2;
106 new_references.attribute3 := x_attribute3;
107 new_references.attribute4 := x_attribute4;
108 new_references.attribute5 := x_attribute5;
109 new_references.attribute6 := x_attribute6;
110 new_references.attribute7 := x_attribute7;
111 new_references.attribute8 := x_attribute8;
112 new_references.attribute9 := x_attribute9;
113 new_references.attribute10 := x_attribute10;
114 new_references.attribute11 := x_attribute11;
115 new_references.attribute12 := x_attribute12;
116 new_references.attribute13 := x_attribute13;
117 new_references.attribute14 := x_attribute14;
118 new_references.attribute15 := x_attribute15;
119 new_references.attribute16 := x_attribute16;
120 new_references.attribute17 := x_attribute17;
121 new_references.attribute18 := x_attribute18;
122 new_references.attribute19 := x_attribute19;
123 new_references.attribute20 := x_attribute20;
124 new_references.gl_date := TRUNC(x_gl_date);
125
126 IF (p_action = 'UPDATE') THEN
127 new_references.creation_date := old_references.creation_date;
128 new_references.created_by := old_references.created_by;
129 ELSE
130 new_references.creation_date := x_creation_date;
131 new_references.created_by := x_created_by;
132 END IF;
133
134 new_references.last_update_date := x_last_update_date;
135 new_references.last_updated_by := x_last_updated_by;
136 new_references.last_update_login := x_last_update_login;
137
138 END set_column_values;
139
140
141 PROCEDURE check_parent_existance AS
142 /*
143 || Created By : [email protected]
144 || Created On : 27-FEB-2002
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
152 CURSOR cur_hzp(cp_party_id NUMBER) IS
153 SELECT 'x'
154 FROM hz_parties
155 WHERE party_id = cp_party_id;
156
157 l_var VARCHAR2(1);
158 BEGIN
159
160 IF (((old_references.refund_id = new_references.refund_id)) OR
161 ((new_references.refund_id IS NULL))) THEN
162 NULL;
163 ELSIF NOT igs_fi_refunds_pkg.get_pk_for_validation (
164 new_references.refund_id
165 ) THEN
166 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
167 igs_ge_msg_stack.add;
168 app_exception.raise_exception;
169 END IF;
170
171 IF (((old_references.person_id = new_references.person_id)) OR
172 ((new_references.person_id IS NULL))) THEN
173 NULL;
174 ELSE
175 OPEN cur_hzp(new_references.person_id);
176 FETCH cur_hzp INTO l_var;
177 IF cur_hzp%NOTFOUND THEN
178 CLOSE cur_hzp;
179 fnd_message.set_name('FND','FORM_RECORD_DELETED');
180 igs_ge_msg_stack.add;
181 app_exception.raise_exception;
182 END IF;
183 CLOSE cur_hzp;
184 END IF;
185
186 IF (((old_references.pay_person_id = new_references.pay_person_id)) OR
187 ((new_references.pay_person_id IS NULL))) THEN
188 NULL;
189 ELSE
190 OPEN cur_hzp(new_references.pay_person_id);
191 FETCH cur_hzp INTO l_var;
192 IF cur_hzp%NOTFOUND THEN
193 CLOSE cur_hzp;
194 fnd_message.set_name('FND','FORM_RECORD_DELETED');
195 igs_ge_msg_stack.add;
196 app_exception.raise_exception;
197 END IF;
198 CLOSE cur_hzp;
199 END IF;
200
201 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
202 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
203 (old_references.fee_type = new_references.fee_type)) OR
204 ((new_references.fee_cal_type IS NULL) OR
205 (new_references.fee_ci_sequence_number IS NULL) OR
206 (new_references.fee_type IS NULL))) THEN
207 NULL;
208 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
209 new_references.fee_type,
210 new_references.fee_cal_type,
211 new_references.fee_ci_sequence_number
212 ) THEN
213 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
214 igs_ge_msg_stack.add;
215 app_exception.raise_exception;
216 END IF;
217
218 IF (((old_references.dr_account_cd = new_references.dr_account_cd)) OR
219 ((new_references.dr_account_cd IS NULL))) THEN
220 NULL;
221 ELSIF NOT igs_fi_acc_pkg.get_pk_for_validation (
222 new_references.dr_account_cd
223 ) THEN
224 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
225 igs_ge_msg_stack.add;
226 app_exception.raise_exception;
227 END IF;
228
229 IF (((old_references.cr_account_cd = new_references.cr_account_cd)) OR
230 ((new_references.cr_account_cd IS NULL))) THEN
231 NULL;
232 ELSIF NOT igs_fi_acc_pkg.get_pk_for_validation (
233 new_references.cr_account_cd
234 ) THEN
235 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
236 igs_ge_msg_stack.add;
237 app_exception.raise_exception;
238 END IF;
239
240 IF (((old_references.invoice_id = new_references.invoice_id)) OR
241 ((new_references.invoice_id IS NULL))) THEN
242 NULL;
243 ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation (
244 new_references.invoice_id
245 ) THEN
246 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250
251 IF (((old_references.source_refund_id = new_references.source_refund_id)) OR
252 ((new_references.source_refund_id IS NULL))) THEN
253 NULL;
254 ELSIF NOT igs_fi_refund_int_pkg.get_pk_for_validation (
255 new_references.source_refund_id
256 ) THEN
257 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
258 igs_ge_msg_stack.add;
259 app_exception.raise_exception;
260 END IF;
261
262 END check_parent_existance;
263
264
265 PROCEDURE check_child_existance IS
266 /*
267 || Created By : [email protected]
268 || Created On : 27-FEB-2002
269 || Purpose : Checks for the existance of Child records.
270 || Known limitations, enhancements or remarks :
271 || Change History :
272 || Who When What
273 || (reverse chronological order - newest change first)
274 */
275 BEGIN
276
277 igs_fi_refund_int_pkg.get_fk_igs_fi_refund_int (
278 old_references.refund_id
279 );
280
281 END check_child_existance;
282
283
284 FUNCTION get_pk_for_validation (
285 x_refund_id IN NUMBER
286 ) RETURN BOOLEAN AS
287 /*
288 || Created By : [email protected]
289 || Created On : 27-FEB-2002
290 || Purpose : Validates the Primary Key of the table.
291 || Known limitations, enhancements or remarks :
292 || Change History :
293 || Who When What
294 || (reverse chronological order - newest change first)
295 */
296 CURSOR cur_rowid IS
297 SELECT rowid
298 FROM igs_fi_refund_int_all
299 WHERE refund_id = x_refund_id
300 FOR UPDATE NOWAIT;
301
302 lv_rowid cur_rowid%RowType;
303
304 BEGIN
305
306 OPEN cur_rowid;
307 FETCH cur_rowid INTO lv_rowid;
308 IF (cur_rowid%FOUND) THEN
309 CLOSE cur_rowid;
310 RETURN(TRUE);
311 ELSE
312 CLOSE cur_rowid;
313 RETURN(FALSE);
314 END IF;
315
316 END get_pk_for_validation;
317
318
319 PROCEDURE get_fk_igs_fi_refunds (
320 x_refund_id IN NUMBER
321 ) AS
322 /*
323 || Created By : [email protected]
324 || Created On : 27-FEB-2002
325 || Purpose : Validates the Foreign Keys for the table.
326 || Known limitations, enhancements or remarks :
327 || Change History :
328 || Who When What
329 || (reverse chronological order - newest change first)
330 */
331 CURSOR cur_rowid IS
332 SELECT rowid
333 FROM igs_fi_refund_int_all
334 WHERE ((refund_id = x_refund_id));
335
336 lv_rowid cur_rowid%RowType;
337
338 BEGIN
339
340 OPEN cur_rowid;
341 FETCH cur_rowid INTO lv_rowid;
342 IF (cur_rowid%FOUND) THEN
343 CLOSE cur_rowid;
344 fnd_message.set_name ('IGS', 'IGS_FI_RFND_RINT_FK');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 RETURN;
348 END IF;
349 CLOSE cur_rowid;
350
351 END get_fk_igs_fi_refunds;
352
353
354 PROCEDURE get_fk_igs_fi_inv_int (
355 x_invoice_id IN NUMBER
356 ) AS
357 /*
358 || Created By : [email protected]
359 || Created On : 27-FEB-2002
360 || Purpose : Validates the Foreign Keys for the table.
361 || Known limitations, enhancements or remarks :
362 || Change History :
363 || Who When What
364 || (reverse chronological order - newest change first)
365 */
366 CURSOR cur_rowid IS
367 SELECT rowid
368 FROM igs_fi_refund_int_all
369 WHERE ((invoice_id = x_invoice_id));
370
371 lv_rowid cur_rowid%RowType;
372
373 BEGIN
374
375 OPEN cur_rowid;
376 FETCH cur_rowid INTO lv_rowid;
377 IF (cur_rowid%FOUND) THEN
378 CLOSE cur_rowid;
379 fnd_message.set_name ('IGS', 'IGS_FI_INV_RINT_FK');
380 igs_ge_msg_stack.add;
381 app_exception.raise_exception;
382 RETURN;
383 END IF;
384 CLOSE cur_rowid;
385
386 END get_fk_igs_fi_inv_int;
387
388
389 PROCEDURE get_fk_igs_fi_refund_int (
390 x_refund_id IN NUMBER
391 ) AS
392 /*
393 || Created By : [email protected]
394 || Created On : 27-FEB-2002
395 || Purpose : Validates the Foreign Keys for the table.
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || (reverse chronological order - newest change first)
400 */
401 CURSOR cur_rowid IS
402 SELECT rowid
403 FROM igs_fi_refund_int_all
404 WHERE ((source_refund_id = x_refund_id));
405
406 lv_rowid cur_rowid%RowType;
407
408 BEGIN
409
410 OPEN cur_rowid;
411 FETCH cur_rowid INTO lv_rowid;
412 IF (cur_rowid%FOUND) THEN
413 CLOSE cur_rowid;
414 fnd_message.set_name ('IGS', 'IGS_FI_RINT_RINT_FK');
415 igs_ge_msg_stack.add;
416 app_exception.raise_exception;
417 RETURN;
418 END IF;
419 CLOSE cur_rowid;
420
421 END get_fk_igs_fi_refund_int;
422
423
424 PROCEDURE before_dml (
425 p_action IN VARCHAR2,
426 x_rowid IN VARCHAR2 ,
427 x_refund_id IN NUMBER ,
428 x_voucher_date IN DATE ,
429 x_person_id IN NUMBER ,
430 x_pay_person_id IN NUMBER ,
431 x_dr_gl_ccid IN NUMBER ,
432 x_cr_gl_ccid IN NUMBER ,
433 x_dr_account_cd IN VARCHAR2 ,
434 x_cr_account_cd IN VARCHAR2 ,
435 x_refund_amount IN NUMBER ,
436 x_fee_type IN VARCHAR2 ,
437 x_fee_cal_type IN VARCHAR2 ,
438 x_fee_ci_sequence_number IN NUMBER ,
439 x_source_refund_id IN NUMBER ,
440 x_invoice_id IN NUMBER ,
441 x_reason IN VARCHAR2 ,
442 x_attribute_category IN VARCHAR2 ,
443 x_attribute1 IN VARCHAR2 ,
444 x_attribute2 IN VARCHAR2 ,
445 x_attribute3 IN VARCHAR2 ,
446 x_attribute4 IN VARCHAR2 ,
447 x_attribute5 IN VARCHAR2 ,
448 x_attribute6 IN VARCHAR2 ,
449 x_attribute7 IN VARCHAR2 ,
450 x_attribute8 IN VARCHAR2 ,
451 x_attribute9 IN VARCHAR2 ,
452 x_attribute10 IN VARCHAR2 ,
453 x_attribute11 IN VARCHAR2 ,
454 x_attribute12 IN VARCHAR2 ,
455 x_attribute13 IN VARCHAR2 ,
456 x_attribute14 IN VARCHAR2 ,
457 x_attribute15 IN VARCHAR2 ,
458 x_attribute16 IN VARCHAR2 ,
459 x_attribute17 IN VARCHAR2 ,
460 x_attribute18 IN VARCHAR2 ,
461 x_attribute19 IN VARCHAR2 ,
462 x_attribute20 IN VARCHAR2 ,
463 x_creation_date IN DATE ,
464 x_created_by IN NUMBER ,
465 x_last_update_date IN DATE ,
466 x_last_updated_by IN NUMBER ,
467 x_last_update_login IN NUMBER ,
468 x_gl_date IN DATE
469 ) AS
470 /*
471 || Created By : [email protected]
472 || Created On : 27-FEB-2002
473 || Purpose : Initialises the columns, Checks Constraints, Calls the
474 || Trigger Handlers for the table, before any DML operation.
475 || Known limitations, enhancements or remarks :
476 || Change History :
477 || Who When What
478 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
479 || (reverse chronological order - newest change first)
480 */
481 BEGIN
482
483 set_column_values (
484 p_action,
485 x_rowid,
486 x_refund_id,
487 x_voucher_date,
488 x_person_id,
489 x_pay_person_id,
490 x_dr_gl_ccid,
491 x_cr_gl_ccid,
492 x_dr_account_cd,
493 x_cr_account_cd,
494 x_refund_amount,
495 x_fee_type,
496 x_fee_cal_type,
497 x_fee_ci_sequence_number,
498 x_source_refund_id,
499 x_invoice_id,
500 x_reason,
501 x_attribute_category,
502 x_attribute1,
503 x_attribute2,
504 x_attribute3,
505 x_attribute4,
506 x_attribute5,
507 x_attribute6,
508 x_attribute7,
509 x_attribute8,
510 x_attribute9,
511 x_attribute10,
512 x_attribute11,
513 x_attribute12,
514 x_attribute13,
515 x_attribute14,
516 x_attribute15,
517 x_attribute16,
518 x_attribute17,
519 x_attribute18,
520 x_attribute19,
521 x_attribute20,
522 x_creation_date,
523 x_created_by,
524 x_last_update_date,
525 x_last_updated_by,
526 x_last_update_login ,
527 x_gl_date
528 );
529
530 IF (p_action = 'INSERT') THEN
531 -- Call all the procedures related to Before Insert.
532 IF ( get_pk_for_validation(
533 new_references.refund_id
534 )
535 ) THEN
536 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
537 igs_ge_msg_stack.add;
538 app_exception.raise_exception;
539 END IF;
540 check_parent_existance;
541 ELSIF (p_action = 'UPDATE') THEN
542 -- Call all the procedures related to Before Update.
543 check_parent_existance;
544 ELSIF (p_action = 'DELETE') THEN
545 -- Call all the procedures related to Before Delete.
546 check_child_existance;
547 ELSIF (p_action = 'VALIDATE_INSERT') THEN
548 -- Call all the procedures related to Before Insert.
549 IF ( get_pk_for_validation (
550 new_references.refund_id
551 )
552 ) THEN
553 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
554 igs_ge_msg_stack.add;
555 app_exception.raise_exception;
556 END IF;
557 ELSIF (p_action = 'VALIDATE_DELETE') THEN
558 check_child_existance;
559 END IF;
560
561 END before_dml;
562
563
564 PROCEDURE insert_row (
565 x_rowid IN OUT NOCOPY VARCHAR2,
566 x_refund_id IN NUMBER,
567 x_voucher_date IN DATE,
568 x_person_id IN NUMBER,
569 x_pay_person_id IN NUMBER,
570 x_dr_gl_ccid IN NUMBER,
571 x_cr_gl_ccid IN NUMBER,
572 x_dr_account_cd IN VARCHAR2,
573 x_cr_account_cd IN VARCHAR2,
574 x_refund_amount IN NUMBER,
575 x_fee_type IN VARCHAR2,
576 x_fee_cal_type IN VARCHAR2,
577 x_fee_ci_sequence_number IN NUMBER,
578 x_source_refund_id IN NUMBER,
579 x_invoice_id IN NUMBER,
580 x_reason IN VARCHAR2,
581 x_attribute_category IN VARCHAR2,
582 x_attribute1 IN VARCHAR2,
583 x_attribute2 IN VARCHAR2,
584 x_attribute3 IN VARCHAR2,
585 x_attribute4 IN VARCHAR2,
586 x_attribute5 IN VARCHAR2,
587 x_attribute6 IN VARCHAR2,
588 x_attribute7 IN VARCHAR2,
589 x_attribute8 IN VARCHAR2,
590 x_attribute9 IN VARCHAR2,
591 x_attribute10 IN VARCHAR2,
592 x_attribute11 IN VARCHAR2,
593 x_attribute12 IN VARCHAR2,
594 x_attribute13 IN VARCHAR2,
595 x_attribute14 IN VARCHAR2,
596 x_attribute15 IN VARCHAR2,
597 x_attribute16 IN VARCHAR2,
598 x_attribute17 IN VARCHAR2,
599 x_attribute18 IN VARCHAR2,
600 x_attribute19 IN VARCHAR2,
601 x_attribute20 IN VARCHAR2,
602 x_mode IN VARCHAR2,
603 x_gl_date IN DATE
604 ) AS
605 /*
606 || Created By : [email protected]
607 || Created On : 27-FEB-2002
608 || Purpose : Handles the INSERT DML logic for the table.
609 || Known limitations, enhancements or remarks :
610 || Change History :
611 || Who When What
612 || pathipat 18-Feb-2003 Enh 2747329 - Payables Intg build - Value of org_id set to NULL
613 || instead of selecting ar_int_org_id from igs_Fi_control. (Removed cur_org)
614 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
615 || (reverse chronological order - newest change first)
616 */
617 CURSOR c IS
618 SELECT rowid
619 FROM igs_fi_refund_int_all
620 WHERE refund_id = x_refund_id;
621
622 x_last_update_date DATE;
623 x_last_updated_by NUMBER;
624 x_last_update_login NUMBER;
625 x_request_id NUMBER;
626 x_program_id NUMBER;
627 x_program_application_id NUMBER;
628 x_program_update_date DATE;
629
630 BEGIN
631
632 x_last_update_date := SYSDATE;
633 IF (x_mode = 'I') THEN
634 x_last_updated_by := 1;
635 x_last_update_login := 0;
636 ELSIF (x_mode = 'R') THEN
637 x_last_updated_by := fnd_global.user_id;
638 IF (x_last_updated_by IS NULL) THEN
639 x_last_updated_by := -1;
640 END IF;
641 x_last_update_login := fnd_global.login_id;
642 IF (x_last_update_login IS NULL) THEN
643 x_last_update_login := -1;
644 END IF;
645 x_request_id := fnd_global.conc_request_id;
646 x_program_id := fnd_global.conc_program_id;
647 x_program_application_id := fnd_global.prog_appl_id;
648
649 IF (x_request_id = -1) THEN
650 x_request_id := NULL;
651 x_program_id := NULL;
652 x_program_application_id := NULL;
653 x_program_update_date := NULL;
654 ELSE
655 x_program_update_date := SYSDATE;
656 END IF;
657 ELSE
658 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
659 igs_ge_msg_stack.add;
660 app_exception.raise_exception;
661 END IF;
662
663 -- Org Id is set to NULL, instead of inserting the value of
664 -- ar_int_org_id from igs_fi_control
665 new_references.org_id := NULL;
666
667 before_dml(
668 p_action => 'INSERT',
669 x_rowid => x_rowid,
670 x_refund_id => x_refund_id,
671 x_voucher_date => x_voucher_date,
672 x_person_id => x_person_id,
673 x_pay_person_id => x_pay_person_id,
674 x_dr_gl_ccid => x_dr_gl_ccid,
675 x_cr_gl_ccid => x_cr_gl_ccid,
676 x_dr_account_cd => x_dr_account_cd,
677 x_cr_account_cd => x_cr_account_cd,
678 x_refund_amount => x_refund_amount,
679 x_fee_type => x_fee_type,
680 x_fee_cal_type => x_fee_cal_type,
681 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
682 x_source_refund_id => x_source_refund_id,
683 x_invoice_id => x_invoice_id,
684 x_reason => x_reason,
685 x_attribute_category => x_attribute_category,
686 x_attribute1 => x_attribute1,
687 x_attribute2 => x_attribute2,
688 x_attribute3 => x_attribute3,
689 x_attribute4 => x_attribute4,
690 x_attribute5 => x_attribute5,
691 x_attribute6 => x_attribute6,
692 x_attribute7 => x_attribute7,
693 x_attribute8 => x_attribute8,
694 x_attribute9 => x_attribute9,
695 x_attribute10 => x_attribute10,
696 x_attribute11 => x_attribute11,
697 x_attribute12 => x_attribute12,
698 x_attribute13 => x_attribute13,
699 x_attribute14 => x_attribute14,
700 x_attribute15 => x_attribute15,
701 x_attribute16 => x_attribute16,
702 x_attribute17 => x_attribute17,
703 x_attribute18 => x_attribute18,
704 x_attribute19 => x_attribute19,
705 x_attribute20 => x_attribute20,
706 x_creation_date => x_last_update_date,
707 x_created_by => x_last_updated_by,
708 x_last_update_date => x_last_update_date,
709 x_last_updated_by => x_last_updated_by,
710 x_last_update_login => x_last_update_login,
711 x_gl_date => x_gl_date
712 );
713
714 INSERT INTO igs_fi_refund_int_all (
715 refund_id,
716 voucher_date,
717 person_id,
718 pay_person_id,
719 dr_gl_ccid,
720 cr_gl_ccid,
721 dr_account_cd,
722 cr_account_cd,
723 refund_amount,
724 fee_type,
725 fee_cal_type,
726 fee_ci_sequence_number,
727 source_refund_id,
728 invoice_id,
729 reason,
730 attribute_category,
731 attribute1,
732 attribute2,
733 attribute3,
734 attribute4,
735 attribute5,
736 attribute6,
737 attribute7,
738 attribute8,
739 attribute9,
740 attribute10,
741 attribute11,
742 attribute12,
743 attribute13,
744 attribute14,
745 attribute15,
746 attribute16,
747 attribute17,
748 attribute18,
749 attribute19,
750 attribute20,
751 org_id,
752 creation_date,
753 created_by,
754 last_update_date,
755 last_updated_by,
756 last_update_login,
757 request_id,
758 program_id,
759 program_application_id,
760 program_update_date,
761 gl_date
762 ) VALUES (
763 new_references.refund_id,
764 new_references.voucher_date,
765 new_references.person_id,
766 new_references.pay_person_id,
767 new_references.dr_gl_ccid,
768 new_references.cr_gl_ccid,
769 new_references.dr_account_cd,
770 new_references.cr_account_cd,
771 new_references.refund_amount,
772 new_references.fee_type,
773 new_references.fee_cal_type,
774 new_references.fee_ci_sequence_number,
775 new_references.source_refund_id,
776 new_references.invoice_id,
777 new_references.reason,
778 new_references.attribute_category,
779 new_references.attribute1,
780 new_references.attribute2,
781 new_references.attribute3,
782 new_references.attribute4,
783 new_references.attribute5,
784 new_references.attribute6,
785 new_references.attribute7,
786 new_references.attribute8,
787 new_references.attribute9,
788 new_references.attribute10,
789 new_references.attribute11,
790 new_references.attribute12,
791 new_references.attribute13,
792 new_references.attribute14,
793 new_references.attribute15,
794 new_references.attribute16,
795 new_references.attribute17,
796 new_references.attribute18,
797 new_references.attribute19,
798 new_references.attribute20,
799 new_references.org_id,
800 x_last_update_date,
801 x_last_updated_by,
802 x_last_update_date,
803 x_last_updated_by,
804 x_last_update_login ,
805 x_request_id,
806 x_program_id,
807 x_program_application_id,
808 x_program_update_date ,
809 new_references.gl_date
810 );
811
812 OPEN c;
813 FETCH c INTO x_rowid;
814 IF (c%NOTFOUND) THEN
815 CLOSE c;
816 RAISE NO_DATA_FOUND;
817 END IF;
818 CLOSE c;
819
820 END insert_row;
821
822
823 PROCEDURE lock_row (
824 x_rowid IN VARCHAR2,
825 x_refund_id IN NUMBER,
826 x_voucher_date IN DATE,
827 x_person_id IN NUMBER,
828 x_pay_person_id IN NUMBER,
829 x_dr_gl_ccid IN NUMBER,
830 x_cr_gl_ccid IN NUMBER,
831 x_dr_account_cd IN VARCHAR2,
832 x_cr_account_cd IN VARCHAR2,
833 x_refund_amount IN NUMBER,
834 x_fee_type IN VARCHAR2,
835 x_fee_cal_type IN VARCHAR2,
836 x_fee_ci_sequence_number IN NUMBER,
837 x_source_refund_id IN NUMBER,
838 x_invoice_id IN NUMBER,
839 x_reason IN VARCHAR2,
840 x_attribute_category IN VARCHAR2,
841 x_attribute1 IN VARCHAR2,
842 x_attribute2 IN VARCHAR2,
843 x_attribute3 IN VARCHAR2,
844 x_attribute4 IN VARCHAR2,
845 x_attribute5 IN VARCHAR2,
846 x_attribute6 IN VARCHAR2,
847 x_attribute7 IN VARCHAR2,
848 x_attribute8 IN VARCHAR2,
849 x_attribute9 IN VARCHAR2,
850 x_attribute10 IN VARCHAR2,
851 x_attribute11 IN VARCHAR2,
852 x_attribute12 IN VARCHAR2,
853 x_attribute13 IN VARCHAR2,
854 x_attribute14 IN VARCHAR2,
855 x_attribute15 IN VARCHAR2,
856 x_attribute16 IN VARCHAR2,
857 x_attribute17 IN VARCHAR2,
858 x_attribute18 IN VARCHAR2,
859 x_attribute19 IN VARCHAR2,
860 x_attribute20 IN VARCHAR2,
861 x_gl_date IN DATE
862 ) AS
863 /*
864 || Created By : [email protected]
865 || Created On : 27-FEB-2002
866 || Purpose : Handles the LOCK mechanism for the table.
867 || Known limitations, enhancements or remarks :
868 || Change History :
869 || Who When What
870 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
871 || (reverse chronological order - newest change first)
872 */
873 CURSOR c1 IS
874 SELECT
875 voucher_date,
876 person_id,
877 pay_person_id,
878 dr_gl_ccid,
879 cr_gl_ccid,
880 dr_account_cd,
881 cr_account_cd,
882 refund_amount,
883 fee_type,
884 fee_cal_type,
885 fee_ci_sequence_number,
886 source_refund_id,
887 invoice_id,
888 reason,
889 attribute_category,
890 attribute1,
891 attribute2,
892 attribute3,
893 attribute4,
894 attribute5,
895 attribute6,
896 attribute7,
897 attribute8,
898 attribute9,
899 attribute10,
900 attribute11,
901 attribute12,
902 attribute13,
903 attribute14,
904 attribute15,
905 attribute16,
906 attribute17,
907 attribute18,
908 attribute19,
909 attribute20,
910 gl_date
911 FROM igs_fi_refund_int_all
912 WHERE rowid = x_rowid
913 FOR UPDATE NOWAIT;
914
915 tlinfo c1%ROWTYPE;
916
917 BEGIN
918
919 OPEN c1;
920 FETCH c1 INTO tlinfo;
921 IF (c1%notfound) THEN
922 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
923 igs_ge_msg_stack.add;
924 CLOSE c1;
925 app_exception.raise_exception;
926 RETURN;
927 END IF;
928 CLOSE c1;
929
930 IF (
931 (tlinfo.voucher_date = x_voucher_date)
932 AND (tlinfo.person_id = x_person_id)
933 AND (tlinfo.pay_person_id = x_pay_person_id)
934 AND ((tlinfo.dr_gl_ccid = x_dr_gl_ccid) OR ((tlinfo.dr_gl_ccid IS NULL) AND (X_dr_gl_ccid IS NULL)))
935 AND ((tlinfo.cr_gl_ccid = x_cr_gl_ccid) OR ((tlinfo.cr_gl_ccid IS NULL) AND (X_cr_gl_ccid IS NULL)))
936 AND ((tlinfo.dr_account_cd = x_dr_account_cd) OR ((tlinfo.dr_account_cd IS NULL) AND (X_dr_account_cd IS NULL)))
937 AND ((tlinfo.cr_account_cd = x_cr_account_cd) OR ((tlinfo.cr_account_cd IS NULL) AND (X_cr_account_cd IS NULL)))
938 AND (tlinfo.refund_amount = x_refund_amount)
939 AND (tlinfo.fee_type = x_fee_type)
940 AND (tlinfo.fee_cal_type = x_fee_cal_type)
941 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
942 AND ((tlinfo.source_refund_id = x_source_refund_id) OR ((tlinfo.source_refund_id IS NULL) AND (X_source_refund_id IS NULL)))
943 AND ((tlinfo.invoice_id = x_invoice_id) OR ((tlinfo.invoice_id IS NULL) AND (X_invoice_id IS NULL)))
944 AND ((tlinfo.reason = x_reason) OR ((tlinfo.reason IS NULL) AND (X_reason IS NULL)))
945 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
946 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
947 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
948 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
949 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
950 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
951 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
952 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
953 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
954 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
955 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
956 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
957 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
958 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
959 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
960 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
961 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
962 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
963 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
964 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
965 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
966 AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (X_gl_date IS NULL)))
967 ) THEN
968 NULL;
969 ELSE
970 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
971 igs_ge_msg_stack.add;
972 app_exception.raise_exception;
973 END IF;
974
975 RETURN;
976
977 END lock_row;
978
979
980 PROCEDURE update_row (
981 x_rowid IN VARCHAR2,
982 x_refund_id IN NUMBER,
983 x_voucher_date IN DATE,
984 x_person_id IN NUMBER,
985 x_pay_person_id IN NUMBER,
986 x_dr_gl_ccid IN NUMBER,
987 x_cr_gl_ccid IN NUMBER,
988 x_dr_account_cd IN VARCHAR2,
989 x_cr_account_cd IN VARCHAR2,
990 x_refund_amount IN NUMBER,
991 x_fee_type IN VARCHAR2,
992 x_fee_cal_type IN VARCHAR2,
993 x_fee_ci_sequence_number IN NUMBER,
994 x_source_refund_id IN NUMBER,
995 x_invoice_id IN NUMBER,
996 x_reason IN VARCHAR2,
997 x_attribute_category IN VARCHAR2,
998 x_attribute1 IN VARCHAR2,
999 x_attribute2 IN VARCHAR2,
1000 x_attribute3 IN VARCHAR2,
1001 x_attribute4 IN VARCHAR2,
1002 x_attribute5 IN VARCHAR2,
1003 x_attribute6 IN VARCHAR2,
1004 x_attribute7 IN VARCHAR2,
1005 x_attribute8 IN VARCHAR2,
1006 x_attribute9 IN VARCHAR2,
1007 x_attribute10 IN VARCHAR2,
1008 x_attribute11 IN VARCHAR2,
1009 x_attribute12 IN VARCHAR2,
1010 x_attribute13 IN VARCHAR2,
1011 x_attribute14 IN VARCHAR2,
1012 x_attribute15 IN VARCHAR2,
1013 x_attribute16 IN VARCHAR2,
1014 x_attribute17 IN VARCHAR2,
1015 x_attribute18 IN VARCHAR2,
1016 x_attribute19 IN VARCHAR2,
1017 x_attribute20 IN VARCHAR2,
1018 x_mode IN VARCHAR2,
1019 x_gl_date IN DATE
1020 ) AS
1021 /*
1022 || Created By : [email protected]
1023 || Created On : 27-FEB-2002
1024 || Purpose : Handles the UPDATE DML logic for the table.
1025 || Known limitations, enhancements or remarks :
1026 || Change History :
1027 || Who When What
1028 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
1029 || (reverse chronological order - newest change first)
1030 */
1031 x_last_update_date DATE ;
1032 x_last_updated_by NUMBER;
1033 x_last_update_login NUMBER;
1034 x_request_id NUMBER;
1035 x_program_id NUMBER;
1036 x_program_application_id NUMBER;
1037 x_program_update_date DATE;
1038
1039 BEGIN
1040
1041 x_last_update_date := SYSDATE;
1042 IF (X_MODE = 'I') THEN
1043 x_last_updated_by := 1;
1044 x_last_update_login := 0;
1045 ELSIF (x_mode = 'R') THEN
1046 x_last_updated_by := fnd_global.user_id;
1047 IF x_last_updated_by IS NULL THEN
1048 x_last_updated_by := -1;
1049 END IF;
1050 x_last_update_login := fnd_global.login_id;
1051 IF (x_last_update_login IS NULL) THEN
1052 x_last_update_login := -1;
1053 END IF;
1054 ELSE
1055 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1056 igs_ge_msg_stack.add;
1057 app_exception.raise_exception;
1058 END IF;
1059
1060 before_dml(
1061 p_action => 'UPDATE',
1062 x_rowid => x_rowid,
1063 x_refund_id => x_refund_id,
1064 x_voucher_date => x_voucher_date,
1065 x_person_id => x_person_id,
1066 x_pay_person_id => x_pay_person_id,
1067 x_dr_gl_ccid => x_dr_gl_ccid,
1068 x_cr_gl_ccid => x_cr_gl_ccid,
1069 x_dr_account_cd => x_dr_account_cd,
1070 x_cr_account_cd => x_cr_account_cd,
1071 x_refund_amount => x_refund_amount,
1072 x_fee_type => x_fee_type,
1073 x_fee_cal_type => x_fee_cal_type,
1074 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1075 x_source_refund_id => x_source_refund_id,
1076 x_invoice_id => x_invoice_id,
1077 x_reason => x_reason,
1078 x_attribute_category => x_attribute_category,
1079 x_attribute1 => x_attribute1,
1080 x_attribute2 => x_attribute2,
1081 x_attribute3 => x_attribute3,
1082 x_attribute4 => x_attribute4,
1083 x_attribute5 => x_attribute5,
1084 x_attribute6 => x_attribute6,
1085 x_attribute7 => x_attribute7,
1086 x_attribute8 => x_attribute8,
1087 x_attribute9 => x_attribute9,
1088 x_attribute10 => x_attribute10,
1089 x_attribute11 => x_attribute11,
1090 x_attribute12 => x_attribute12,
1091 x_attribute13 => x_attribute13,
1092 x_attribute14 => x_attribute14,
1093 x_attribute15 => x_attribute15,
1094 x_attribute16 => x_attribute16,
1095 x_attribute17 => x_attribute17,
1096 x_attribute18 => x_attribute18,
1097 x_attribute19 => x_attribute19,
1098 x_attribute20 => x_attribute20,
1099 x_creation_date => x_last_update_date,
1100 x_created_by => x_last_updated_by,
1101 x_last_update_date => x_last_update_date,
1102 x_last_updated_by => x_last_updated_by,
1103 x_last_update_login => x_last_update_login ,
1104 x_gl_date => x_gl_date
1105 );
1106
1107 IF (x_mode = 'R') THEN
1108 x_request_id := fnd_global.conc_request_id;
1109 x_program_id := fnd_global.conc_program_id;
1110 x_program_application_id := fnd_global.prog_appl_id;
1111 IF (x_request_id = -1) THEN
1112 x_request_id := old_references.request_id;
1113 x_program_id := old_references.program_id;
1114 x_program_application_id := old_references.program_application_id;
1115 x_program_update_date := old_references.program_update_date;
1116 ELSE
1117 x_program_update_date := SYSDATE;
1118 END IF;
1119 END IF;
1120
1121 UPDATE igs_fi_refund_int_all
1122 SET
1123 voucher_date = new_references.voucher_date,
1124 person_id = new_references.person_id,
1125 pay_person_id = new_references.pay_person_id,
1126 dr_gl_ccid = new_references.dr_gl_ccid,
1127 cr_gl_ccid = new_references.cr_gl_ccid,
1128 dr_account_cd = new_references.dr_account_cd,
1129 cr_account_cd = new_references.cr_account_cd,
1130 refund_amount = new_references.refund_amount,
1131 fee_type = new_references.fee_type,
1132 fee_cal_type = new_references.fee_cal_type,
1133 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1134 source_refund_id = new_references.source_refund_id,
1135 invoice_id = new_references.invoice_id,
1136 reason = new_references.reason,
1137 attribute_category = new_references.attribute_category,
1138 attribute1 = new_references.attribute1,
1139 attribute2 = new_references.attribute2,
1140 attribute3 = new_references.attribute3,
1141 attribute4 = new_references.attribute4,
1142 attribute5 = new_references.attribute5,
1143 attribute6 = new_references.attribute6,
1144 attribute7 = new_references.attribute7,
1145 attribute8 = new_references.attribute8,
1146 attribute9 = new_references.attribute9,
1147 attribute10 = new_references.attribute10,
1148 attribute11 = new_references.attribute11,
1149 attribute12 = new_references.attribute12,
1150 attribute13 = new_references.attribute13,
1151 attribute14 = new_references.attribute14,
1152 attribute15 = new_references.attribute15,
1153 attribute16 = new_references.attribute16,
1154 attribute17 = new_references.attribute17,
1155 attribute18 = new_references.attribute18,
1156 attribute19 = new_references.attribute19,
1157 attribute20 = new_references.attribute20,
1158 last_update_date = x_last_update_date,
1159 last_updated_by = x_last_updated_by,
1160 last_update_login = x_last_update_login ,
1161 request_id = x_request_id,
1162 program_id = x_program_id,
1163 program_application_id = x_program_application_id,
1164 program_update_date = x_program_update_date ,
1165 gl_date = new_references.gl_date
1166 WHERE rowid = x_rowid;
1167
1168 IF (SQL%NOTFOUND) THEN
1169 RAISE NO_DATA_FOUND;
1170 END IF;
1171
1172 END update_row;
1173
1174
1175 PROCEDURE add_row (
1176 x_rowid IN OUT NOCOPY VARCHAR2,
1177 x_refund_id IN NUMBER,
1178 x_voucher_date IN DATE,
1179 x_person_id IN NUMBER,
1180 x_pay_person_id IN NUMBER,
1181 x_dr_gl_ccid IN NUMBER,
1182 x_cr_gl_ccid IN NUMBER,
1183 x_dr_account_cd IN VARCHAR2,
1184 x_cr_account_cd IN VARCHAR2,
1185 x_refund_amount IN NUMBER,
1186 x_fee_type IN VARCHAR2,
1187 x_fee_cal_type IN VARCHAR2,
1188 x_fee_ci_sequence_number IN NUMBER,
1189 x_source_refund_id IN NUMBER,
1190 x_invoice_id IN NUMBER,
1191 x_reason IN VARCHAR2,
1192 x_attribute_category IN VARCHAR2,
1193 x_attribute1 IN VARCHAR2,
1194 x_attribute2 IN VARCHAR2,
1195 x_attribute3 IN VARCHAR2,
1196 x_attribute4 IN VARCHAR2,
1197 x_attribute5 IN VARCHAR2,
1198 x_attribute6 IN VARCHAR2,
1199 x_attribute7 IN VARCHAR2,
1200 x_attribute8 IN VARCHAR2,
1201 x_attribute9 IN VARCHAR2,
1202 x_attribute10 IN VARCHAR2,
1203 x_attribute11 IN VARCHAR2,
1204 x_attribute12 IN VARCHAR2,
1205 x_attribute13 IN VARCHAR2,
1206 x_attribute14 IN VARCHAR2,
1207 x_attribute15 IN VARCHAR2,
1208 x_attribute16 IN VARCHAR2,
1209 x_attribute17 IN VARCHAR2,
1210 x_attribute18 IN VARCHAR2,
1211 x_attribute19 IN VARCHAR2,
1212 x_attribute20 IN VARCHAR2,
1213 x_mode IN VARCHAR2,
1214 x_gl_date IN DATE
1215 ) AS
1216 /*
1217 || Created By : [email protected]
1218 || Created On : 27-FEB-2002
1219 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1220 || Known limitations, enhancements or remarks :
1221 || Change History :
1222 || Who When What
1223 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE. removed DEFAULT clause
1224 || (reverse chronological order - newest change first)
1225 */
1226 CURSOR c1 IS
1227 SELECT rowid
1228 FROM igs_fi_refund_int_all
1229 WHERE refund_id = x_refund_id;
1230
1231 BEGIN
1232
1233 OPEN c1;
1234 FETCH c1 INTO x_rowid;
1235 IF (c1%NOTFOUND) THEN
1236 CLOSE c1;
1237
1238 insert_row (
1239 x_rowid,
1240 x_refund_id,
1241 x_voucher_date,
1242 x_person_id,
1243 x_pay_person_id,
1244 x_dr_gl_ccid,
1245 x_cr_gl_ccid,
1246 x_dr_account_cd,
1247 x_cr_account_cd,
1248 x_refund_amount,
1249 x_fee_type,
1250 x_fee_cal_type,
1251 x_fee_ci_sequence_number,
1252 x_source_refund_id,
1253 x_invoice_id,
1254 x_reason,
1255 x_attribute_category,
1256 x_attribute1,
1257 x_attribute2,
1258 x_attribute3,
1259 x_attribute4,
1260 x_attribute5,
1261 x_attribute6,
1262 x_attribute7,
1263 x_attribute8,
1264 x_attribute9,
1265 x_attribute10,
1266 x_attribute11,
1267 x_attribute12,
1268 x_attribute13,
1269 x_attribute14,
1270 x_attribute15,
1271 x_attribute16,
1272 x_attribute17,
1273 x_attribute18,
1274 x_attribute19,
1275 x_attribute20,
1276 x_mode,
1277 x_gl_date
1278 );
1279 RETURN;
1280 END IF;
1281 CLOSE c1;
1282
1283 update_row (
1284 x_rowid,
1285 x_refund_id,
1286 x_voucher_date,
1287 x_person_id,
1288 x_pay_person_id,
1289 x_dr_gl_ccid,
1290 x_cr_gl_ccid,
1291 x_dr_account_cd,
1292 x_cr_account_cd,
1293 x_refund_amount,
1294 x_fee_type,
1295 x_fee_cal_type,
1296 x_fee_ci_sequence_number,
1297 x_source_refund_id,
1298 x_invoice_id,
1299 x_reason,
1300 x_attribute_category,
1301 x_attribute1,
1302 x_attribute2,
1303 x_attribute3,
1304 x_attribute4,
1305 x_attribute5,
1306 x_attribute6,
1307 x_attribute7,
1308 x_attribute8,
1309 x_attribute9,
1310 x_attribute10,
1311 x_attribute11,
1312 x_attribute12,
1313 x_attribute13,
1314 x_attribute14,
1315 x_attribute15,
1316 x_attribute16,
1317 x_attribute17,
1318 x_attribute18,
1319 x_attribute19,
1320 x_attribute20,
1321 x_mode ,
1322 x_gl_date
1323 );
1324
1325 END add_row;
1326
1327
1328 PROCEDURE delete_row (
1329 x_rowid IN VARCHAR2
1330 ) AS
1331 /*
1332 || Created By : [email protected]
1333 || Created On : 27-FEB-2002
1334 || Purpose : Handles the DELETE DML logic for the table.
1335 || Known limitations, enhancements or remarks :
1336 || Change History :
1337 || Who When What
1338 || (reverse chronological order - newest change first)
1339 */
1340 BEGIN
1341
1342 before_dml (
1343 p_action => 'DELETE',
1344 x_rowid => x_rowid
1345 );
1346
1347 DELETE FROM igs_fi_refund_int_all
1348 WHERE rowid = x_rowid;
1349
1350 IF (SQL%NOTFOUND) THEN
1351 RAISE NO_DATA_FOUND;
1352 END IF;
1353
1354 END delete_row;
1355
1356
1357 END igs_fi_refund_int_pkg;