[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_INV_INT_PKG
Source
1 PACKAGE BODY igs_fi_inv_int_pkg AS
2 /* $Header: IGSSI73B.pls 120.3 2006/06/27 14:08:37 skharida ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_fi_inv_int_all%RowType;
5 new_references igs_fi_inv_int_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_invoice_id IN NUMBER,
11 x_person_id IN NUMBER,
12 x_fee_type IN VARCHAR2,
13 x_fee_cat IN VARCHAR2,
14 x_fee_cal_type IN VARCHAR2,
15 x_fee_ci_sequence_number IN NUMBER,
16 x_course_cd IN VARCHAR2,
17 x_attendance_mode IN VARCHAR2,
18 x_attendance_type IN VARCHAR2,
19 x_invoice_amount_due IN NUMBER,
20 x_invoice_creation_date IN DATE,
21 x_invoice_desc IN VARCHAR2,
22 x_transaction_type IN VARCHAR2,
23 x_currency_cd IN VARCHAR2,
24 x_status IN VARCHAR2,
25 x_attribute_category IN VARCHAR2,
26 x_attribute1 IN VARCHAR2,
27 x_attribute2 IN VARCHAR2,
28 x_attribute3 IN VARCHAR2,
29 x_attribute4 IN VARCHAR2,
30 x_attribute5 IN VARCHAR2,
31 x_attribute6 IN VARCHAR2,
32 x_attribute7 IN VARCHAR2,
33 x_attribute8 IN VARCHAR2,
34 x_attribute9 IN VARCHAR2,
35 x_attribute10 IN VARCHAR2,
36 x_ORG_ID IN NUMBER,
37 x_invoice_amount IN NUMBER,
38 x_bill_id IN NUMBER,
39 x_bill_number IN VARCHAR2,
40 x_bill_date IN DATE,
41 x_waiver_flag IN VARCHAR2,
42 x_waiver_reason IN VARCHAR2,
43 x_effective_date IN DATE,
44 x_invoice_number IN VARCHAR2,
45 x_exchange_rate IN NUMBER,
46 x_bill_payment_due_date IN DATE,
47 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
48 x_creation_date IN DATE,
49 x_created_by IN NUMBER,
50 x_last_update_date IN DATE,
51 x_last_updated_by IN NUMBER,
52 x_last_update_login IN NUMBER,
53 x_reversal_gl_date IN DATE,
54 x_tax_year_code IN VARCHAR2,
55 x_waiver_name IN VARCHAR2
56 ) AS
57
58 /*************************************************************
59 Created By :
60 Date Created By :
61 Purpose :
62 Know limitations, enhancements or remarks
63 Change History
64 Who When What
65 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
66 pathipat 30-Jun-2003 Bug: 3026125 - Waiver flag inserted as NULL for Ancillary Charges
67 Added NVL clause for waiver_flag value
68 shtatiko 11-MAR2003 Bug# 2734441, Added TRUNC call before assigning the value of
69 x_invoice_creation_date.
70 smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column reversal_gl_date
71 vvutukur 17-Sep-2002 Enh#2564643.Removed references to column subaccount_id.Also removed
72 DEFAULT clause from procedure parameters to avoid gscc warnings.
73 masehgal 10-JAN-2002 Enh # 2170429
74 Obsoletion of SPONSOR_CD
75 smadathi 05-oct-2001 Balance Flag reference removed .
76 Enhancement Bug No. 2030448
77 (reverse chronological order - newest change first)
78 ***************************************************************/
79
80 CURSOR cur_old_ref_values IS
81 SELECT *
82 FROM IGS_FI_INV_INT_ALL
83 WHERE rowid = x_rowid;
84
85 BEGIN
86
87 l_rowid := x_rowid;
88
89 -- Code for setting the Old and New Reference Values.
90 -- Populate Old Values.
91 Open cur_old_ref_values;
92 Fetch cur_old_ref_values INTO old_references;
93 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
94 Close cur_old_ref_values;
95 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 Return;
99 END IF;
100 Close cur_old_ref_values;
101
102 -- Populate New Values.
103 new_references.invoice_id := x_invoice_id;
104 new_references.person_id := x_person_id;
105 new_references.fee_type := x_fee_type;
106 new_references.fee_cat := x_fee_cat;
107 new_references.fee_cal_type := x_fee_cal_type;
108 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
109 new_references.course_cd := x_course_cd;
110 new_references.attendance_mode := x_attendance_mode;
111 new_references.attendance_type := x_attendance_type;
112 new_references.invoice_amount_due := x_invoice_amount_due;
113 -- Added TRUNC as part of Bug# 2734441 by shtatiko on 11-MAR-2003
114 new_references.invoice_creation_date := TRUNC( x_invoice_creation_date );
115 new_references.invoice_desc := x_invoice_desc;
116 new_references.transaction_type := x_transaction_type;
117 new_references.currency_cd := x_currency_cd;
118 new_references.status := x_status;
119 new_references.attribute_category := x_attribute_category;
120 new_references.attribute1 := x_attribute1;
121 new_references.attribute2 := x_attribute2;
122 new_references.attribute3 := x_attribute3;
123 new_references.attribute4 := x_attribute4;
124 new_references.attribute5 := x_attribute5;
125 new_references.attribute6 := x_attribute6;
126 new_references.attribute7 := x_attribute7;
127 new_references.attribute8 := x_attribute8;
128 new_references.attribute9 := x_attribute9;
129 new_references.attribute10 := x_attribute10;
130 new_references.org_id := x_org_id;
131 new_references.invoice_amount := x_invoice_amount ;
132 new_references.bill_id := x_bill_id ;
133 new_references.bill_number := x_bill_number ;
134 new_references.bill_date := x_bill_date ;
135
136 -- Waiver flag is set to 'N' if passed as NULL (pathipat)
137 new_references.waiver_flag := NVL(x_waiver_flag,'N') ;
138
139 new_references.waiver_reason := x_waiver_reason ;
140 -- Added TRUNC as part of Bug# 2734441 by shtatiko on 24-MAR-2003
141 new_references.effective_date := TRUNC(x_effective_date) ;
142 new_references.invoice_number := x_invoice_number ;
143 new_references.exchange_rate := x_exchange_rate ;
144 new_references.bill_payment_due_date := x_bill_payment_due_date ;
145 new_references.optional_fee_flag := x_optional_fee_flag ;
146 new_references.reversal_gl_date := TRUNC(x_reversal_gl_date);
147 IF (p_action = 'UPDATE') THEN
148 new_references.creation_date := old_references.creation_date;
149 new_references.created_by := old_references.created_by;
150 ELSE
151 new_references.creation_date := x_creation_date;
152 new_references.created_by := x_created_by;
153 END IF;
154 new_references.last_update_date := x_last_update_date;
155 new_references.last_updated_by := x_last_updated_by;
156 new_references.last_update_login := x_last_update_login;
157 new_references.tax_year_code := x_tax_year_code;
158 new_references.waiver_name := x_waiver_name;
159
160 END Set_Column_Values;
161
162 PROCEDURE Check_Constraints (
163 Column_Name IN VARCHAR2,
164 Column_Value IN VARCHAR2) AS
165 /*************************************************************
166 Created By :
167 Date Created By :
168 Purpose :
169 Know limitations, enhancements or remarks
170 Change History
171 Who When What
172 (reverse chronological order - newest change first)
173 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
174 vvutukur 17-sep-2002 Enh#2564643.Removed DEFAULT clause from parameters list to avoid gscc
175 warnings in order to comply with 9i standards.
176 ***************************************************************/
177
178 BEGIN
179
180 IF column_name IS NULL THEN
181 NULL;
182 END IF;
183
184 END Check_Constraints;
185
186 FUNCTION Get_PK_For_Validation (
187 x_invoice_id IN NUMBER
188 ) RETURN BOOLEAN AS
189
190 /*************************************************************
191 Created By :
192 Date Created By :
193 Purpose :
194 Know limitations, enhancements or remarks
195 Change History
196 Who When What
197 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
198 (reverse chronological order - newest change first)
199 ***************************************************************/
200
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM igs_fi_inv_int_all
204 WHERE invoice_id = x_invoice_id
205 FOR UPDATE NOWAIT;
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 Open cur_rowid;
212 Fetch cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 Close cur_rowid;
215 Return(TRUE);
216 ELSE
217 Close cur_rowid;
218 Return(FALSE);
219 END IF;
220 END Get_PK_For_Validation;
221
222 PROCEDURE Check_Parent_Existance AS
223 /*************************************************************
224 Created By :schodava
225 Date Created By :2000/05/11
226 Purpose :
227 Know limitations, enhancements or remarks
228 Change History
229 Who When What
230 vvutukur 23-Sep-2002 Enh#2564643.Removed references to subaccount_id.
231 (reverse chronological order - newest change first)
232 ***************************************************************/
233
234 BEGIN
235
236 IF (((old_references.waiver_reason = new_references.waiver_reason)) OR
237 ((new_references.waiver_reason IS NULL))) THEN
238 NULL;
239 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
240 'IGS_FI_WAIVER_REASON',
241 new_references.waiver_reason
242 )THEN
243 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247
248 IF (((old_references.status = new_references.status) OR
249 (new_references.status IS NULL))) THEN
250 NULL;
251 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
252 'STATUS',
253 new_references.status
254 ) THEN
255 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 END IF;
259
260 IF (((old_references.transaction_type = new_references.transaction_type) OR
261 (new_references.transaction_type IS NULL))) THEN
262 NULL;
263 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
264 'TRANSACTION_TYPE',
265 new_references.transaction_type
266 ) THEN
267 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
268 IGS_GE_MSG_STACK.ADD;
269 App_Exception.Raise_Exception;
270 END IF;
271
272 IF (((old_references.bill_id = new_references.bill_id)) OR
273 ((new_references.bill_id IS NULL))) THEN
274 NULL;
275 ELSIF NOT igs_fi_bill_pkg.get_pk_for_validation (
276 new_references.bill_id
277 ) THEN
278 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 END IF;
282
283 -- Following code added as part of the Enhancement Bug#1754956
284 --removed reference to subaccount_id,ie., code which calls IGS_FI_SUBACCTS_PKG.Get_PK_For_Validation.
285
286 --Bug# 3392095, PK validation from IGS_FI_WAIVER_PGMS table.
287 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
288 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
289 (old_references.waiver_name = new_references.waiver_name)) OR
290 ((new_references.fee_cal_type IS NULL) OR
291 (new_references.fee_ci_sequence_number IS NULL) OR
292 (new_references.waiver_name IS NULL))) THEN
293 NULL;
294 ELSIF NOT Igs_fi_waiver_pgms_Pkg.Get_PK_For_Validation (
295 new_references.fee_cal_type,
296 new_references.fee_ci_sequence_number,
297 new_references.waiver_name
298 ) THEN
299 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303
304 END Check_Parent_Existance;
305
306 PROCEDURE Check_Child_Existance AS
307
308 BEGIN
309 --modified by sarakshi, all new_references are made old_references
310 IGS_FI_APPLICATIONS_PKG.GET_FK_IGS_FI_INV_INT_ALL (
311 old_references.invoice_id
312 );
313
314 IGS_FI_OTC_CHARGES_PKG.GET_FK_IGS_FI_INV_INT_ALL (
315 old_references.invoice_id
316 );
317
318 IGS_FI_BILL_TRNSCTNS_PKG.GET_FK_IGS_FI_INV_INT (
319 old_references.invoice_id
320 );
321
322 --added by sarakshi, bug:2124001
323 IGS_FI_INV_WAV_DET_PKG.GET_FK_IGS_FI_INV_INT_ALL(
324 old_references.invoice_id
325 );
326
327 -- Added Enh#2144600
328 igs_fi_refunds_pkg.get_fk_igs_fi_inv_int( old_references.invoice_id);
329
330 -- Added Enh#2144600
331 igs_fi_refund_int_pkg.get_fk_igs_fi_inv_int( old_references.invoice_id);
332
333 END Check_Child_Existance;
334
335 PROCEDURE beforeRowInsertUpdateDelete( p_inserting IN BOOLEAN DEFAULT FALSE,
336 p_updating IN BOOLEAN DEFAULT FALSE,
337 p_deleting IN BOOLEAN DEFAULT FALSE
338 ) AS
339 /*
340 || Created By : vvutukur
341 || Created On : 19-NOV-2003
342 || Purpose : To carryout the actions before insert/update/delete a record in igs_fi_inv_int_all table.
343 || Created as part of bugfix#3249288.
344 || Known limitations, enhancements or remarks :
345 || Change History :
346 || Who When What
347 || (reverse chronological order - newest change first)
348 */
349
350
351 --Cursor to fetch the value of Optional Payment Indicator value from Fee Type Setup.
352 CURSOR c_igs_fi_fee_type(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE) IS
353 SELECT ft.optional_payment_ind
354 FROM igs_fi_fee_type ft
355 WHERE ft.fee_type = cp_v_fee_type;
356
357 l_v_optional_payment_ind igs_fi_fee_type.optional_payment_ind%TYPE;
358
359 BEGIN
360
361 IF (p_inserting) THEN
362 -- Based on the optional payment indicator value set in the fee type
363 -- set up form, the value of the OPTIONAL_FEE_FLAG column in the charges table
364 -- will be set as either 'O' or 'N'. For all charges created with a Non-optional
365 -- fee type, the value of OPTIONAL_FEE_FLAG column in the charges table will be
366 -- assigned as 'N'.For all charges created with an optional
367 -- fee type, the value of OPTIONAL_FEE_FLAG column in the charges table will be
368 -- assigned as 'O'.
369
370 OPEN c_igs_fi_fee_type(new_references.fee_type);
371 FETCH c_igs_fi_fee_type INTO l_v_optional_payment_ind;
372 CLOSE c_igs_fi_fee_type;
373
374 IF l_v_optional_payment_ind = 'N' THEN
375 new_references.optional_fee_flag := 'N';
376 ELSIF l_v_optional_payment_ind = 'Y' THEN
377 new_references.optional_fee_flag := 'O';
378 END IF;
379 END IF;
380 END beforeRowInsertUpdateDelete;
381
382 PROCEDURE get_fk_igs_fi_bill (
383 x_bill_id IN NUMBER
384 ) AS
385 /*
386 || Created By : [email protected]
387 || Created On : 23-JUL-2001
388 || Purpose : Validates the Foreign Keys for the table.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || (reverse chronological order - newest change first)
393 */
394 CURSOR cur_rowid IS
395 SELECT rowid
396 FROM igs_fi_bill_trnsctns
397 WHERE ((bill_id = x_bill_id));
398
399 lv_rowid cur_rowid%RowType;
400
401 BEGIN
402
403 OPEN cur_rowid;
404 FETCH cur_rowid INTO lv_rowid;
405 IF (cur_rowid%FOUND) THEN
406 CLOSE cur_rowid;
407 fnd_message.set_name ('IGS', 'IGS_FI_INVI_FBLLA_FK');
408 igs_ge_msg_stack.add;
409 app_exception.raise_exception;
410 RETURN;
411 END IF;
412 CLOSE cur_rowid;
413
414 END get_fk_igs_fi_bill;
415
416 --removed procedure get_fk_igs_fi_subaccts_all as part of subaccount removal build. enh#2564643.
417
418 PROCEDURE Before_DML (
419 p_action IN VARCHAR2,
420 x_rowid IN VARCHAR2,
421 x_invoice_id IN NUMBER,
422 x_person_id IN NUMBER,
423 x_fee_type IN VARCHAR2,
424 x_fee_cat IN VARCHAR2,
425 x_fee_cal_type IN VARCHAR2,
426 x_fee_ci_sequence_number IN NUMBER,
427 x_course_cd IN VARCHAR2,
428 x_attendance_mode IN VARCHAR2,
429 x_attendance_type IN VARCHAR2,
430 x_invoice_amount_due IN NUMBER,
431 x_invoice_creation_date IN DATE,
432 x_invoice_desc IN VARCHAR2,
433 x_transaction_type IN VARCHAR2,
434 x_currency_cd IN VARCHAR2,
435 x_status IN VARCHAR2,
436 x_attribute_category IN VARCHAR2,
437 x_attribute1 IN VARCHAR2,
438 x_attribute2 IN VARCHAR2,
439 x_attribute3 IN VARCHAR2,
440 x_attribute4 IN VARCHAR2,
441 x_attribute5 IN VARCHAR2,
442 x_attribute6 IN VARCHAR2,
443 x_attribute7 IN VARCHAR2,
444 x_attribute8 IN VARCHAR2,
445 x_attribute9 IN VARCHAR2,
446 x_attribute10 IN VARCHAR2,
447 x_ORG_ID IN NUMBER,
448 x_invoice_amount IN NUMBER,
449 x_bill_id IN NUMBER,
450 x_bill_number IN VARCHAR2,
451 x_bill_date IN DATE,
452 x_waiver_flag IN VARCHAR2,
453 x_waiver_reason IN VARCHAR2,
454 x_effective_date IN DATE,
455 x_invoice_number IN VARCHAR2,
456 x_exchange_rate IN NUMBER,
457 x_bill_payment_due_date IN DATE,
458 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
459 x_creation_date IN DATE,
460 x_created_by IN NUMBER,
461 x_last_update_date IN DATE,
462 x_last_updated_by IN NUMBER,
463 x_last_update_login IN NUMBER,
464 x_reversal_gl_date IN DATE,
465 x_tax_year_code IN VARCHAR2,
466 x_waiver_name IN VARCHAR2
467 ) AS
468 /*************************************************************
469 Created By :
470 Date Created By :
471 Purpose :
472 Know limitations, enhancements or remarks
473 Change History
474 Who When What
475 vvutukur 19-Nov-2003 Bug#3249288.Added call to newly created procedure beforeRowInsertUpdateDelete.
476 smadathi 05-oct-2002 Enh. Bug 2584986. Added new column reversal_gl_date
477 vvutukur 17-Sep-2002 Enh#2564643.Removed references to column subaccount_id.Also removed
478 DEFAULT clause from parameter list to avoid gscc warnings.
479 maseghal 10-JAN-2002 Enh # 2170429
480 Obsoletion of SPONSOR_CD
481 smadathi 05-oct-2001 Balance Flag reference removed .
482 Enhancement Bug No. 2030448
483 (reverse chronological order - newest change first)
484 ***************************************************************/
485
486 BEGIN
487
488 Set_Column_Values (
489 p_action,
490 x_rowid,
491 x_invoice_id,
492 x_person_id,
493 x_fee_type,
494 x_fee_cat,
495 x_fee_cal_type,
496 x_fee_ci_sequence_number,
497 x_course_cd,
498 x_attendance_mode,
499 x_attendance_type,
500 x_invoice_amount_due,
501 x_invoice_creation_date,
502 x_invoice_desc,
503 x_transaction_type,
504 x_currency_cd,
505 x_status,
506 x_attribute_category,
507 x_attribute1,
508 x_attribute2,
509 x_attribute3,
510 x_attribute4,
511 x_attribute5,
512 x_attribute6,
513 x_attribute7,
514 x_attribute8,
515 x_attribute9,
516 x_attribute10,
517 x_org_id,
518 x_invoice_amount,
519 x_bill_id,
520 x_bill_number,
521 x_bill_date,
522 x_waiver_flag,
523 x_waiver_reason,
524 x_effective_date,
525 x_invoice_number,
526 x_exchange_rate,
527 x_bill_payment_due_date,
528 x_optional_fee_flag,
529 x_creation_date,
530 x_created_by,
531 x_last_update_date,
532 x_last_updated_by,
533 x_last_update_login,
534 x_reversal_gl_date,
535 x_tax_year_code,
536 x_waiver_name
537 );
538
539 IF (p_action = 'INSERT') THEN
540 -- Call all the procedures related to Before Insert.
541 Null;
542 IF Get_Pk_For_Validation(
543 new_references.invoice_id) THEN
544 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
545 IGS_GE_MSG_STACK.ADD;
546 App_Exception.Raise_Exception;
547 END IF;
548 Check_Constraints;
549 Check_Parent_Existance;
550 beforeRowInsertUpdateDelete(p_inserting => TRUE);
551 ELSIF (p_action = 'UPDATE') THEN
552 -- Call all the procedures related to Before Update.
553 Check_Constraints;
554 ELSIF (p_action = 'DELETE') THEN
555 -- Call all the procedures related to Before Delete.
556 Null;
557 ELSIF (p_action = 'VALIDATE_INSERT') THEN
558 -- Call all the procedures related to Before Insert.
559 IF Get_PK_For_Validation (
560 new_references.invoice_id) THEN
561 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
562 IGS_GE_MSG_STACK.ADD;
563 App_Exception.Raise_Exception;
564 END IF;
565 Check_Constraints;
566 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
567 Check_Constraints;
568 ELSIF (p_action = 'VALIDATE_DELETE') THEN
569 Null;
570 END IF;
571
572 END Before_DML;
573
574 PROCEDURE After_DML (
575 p_action IN VARCHAR2,
576 x_rowid IN VARCHAR2
577 ) IS
578 /*************************************************************
579 Created By :
580 Date Created By :
581 Purpose :
582 Know limitations, enhancements or remarks
583 Change History
584 Who When What
585 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
586 masehgal 10-JAN-2002 Enh # 2170429
587 Obsoletion of SPONSOR_CD
588 (reverse chronological order - newest change first)
589 ***************************************************************/
590
591 BEGIN
592
593 l_rowid := x_rowid;
594
595 IF (p_action = 'INSERT') THEN
596 -- Call all the procedures related to After Insert.
597 Null;
598 ELSIF (p_action = 'UPDATE') THEN
599 -- Call all the procedures related to After Update.
600 Null;
601 ELSIF (p_action = 'DELETE') THEN
602 -- Call all the procedures related to After Delete.
603 Null;
604 END IF;
605
606 END After_DML;
607
608 procedure INSERT_ROW (
609 X_ROWID in out NOCOPY VARCHAR2,
610 x_INVOICE_ID IN OUT NOCOPY NUMBER,
611 x_PERSON_ID IN NUMBER,
612 x_FEE_TYPE IN VARCHAR2,
613 x_FEE_CAT IN VARCHAR2,
614 x_FEE_CAL_TYPE IN VARCHAR2,
615 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
616 x_COURSE_CD IN VARCHAR2,
617 x_ATTENDANCE_MODE IN VARCHAR2,
618 x_ATTENDANCE_TYPE IN VARCHAR2,
619 x_INVOICE_AMOUNT_DUE IN NUMBER,
620 x_INVOICE_CREATION_DATE IN DATE,
621 x_INVOICE_DESC IN VARCHAR2,
622 x_TRANSACTION_TYPE IN VARCHAR2,
623 x_CURRENCY_CD IN VARCHAR2,
624 x_STATUS IN VARCHAR2,
625 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
626 x_ATTRIBUTE1 IN VARCHAR2,
627 x_ATTRIBUTE2 IN VARCHAR2,
628 x_ATTRIBUTE3 IN VARCHAR2,
629 x_ATTRIBUTE4 IN VARCHAR2,
630 x_ATTRIBUTE5 IN VARCHAR2,
631 x_ATTRIBUTE6 IN VARCHAR2,
632 x_ATTRIBUTE7 IN VARCHAR2,
633 x_ATTRIBUTE8 IN VARCHAR2,
634 x_ATTRIBUTE9 IN VARCHAR2,
635 x_ATTRIBUTE10 IN VARCHAR2,
636 x_INVOICE_AMOUNT IN NUMBER,
637 x_BILL_ID IN NUMBER,
638 x_BILL_NUMBER IN VARCHAR2,
639 x_BILL_DATE IN DATE,
640 x_WAIVER_FLAG IN VARCHAR2,
641 x_WAIVER_REASON IN VARCHAR2,
642 x_EFFECTIVE_DATE IN DATE,
643 x_INVOICE_NUMBER IN VARCHAR2,
644 x_EXCHANGE_RATE IN NUMBER,
645 x_BILL_PAYMENT_DUE_DATE IN DATE,
646 x_ORG_ID IN NUMBER,
647 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
648 X_MODE IN VARCHAR2,
649 X_REVERSAL_GL_DATE IN DATE,
650 x_tax_year_code IN VARCHAR2,
651 x_waiver_name IN VARCHAR2
652 ) AS
653 /*************************************************************
654 Created By :
655 Date Created By :
656 Purpose :
657 Know limitations, enhancements or remarks
658 Change History
659 Who When What
660 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
661 smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column
662 REVERSAL_GL_DATE
663 vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id.Also removed DEFAULT
664 clause from package body to avoid gscc warnings.
665 masehgal 10-JAN-2002 Enh # 2170429
666 Obsoletion of SPONSOR_CD
667 smadathi 05-oct-2001 Balance Flag reference removed .
668 Enhancement Bug No. 2030448
669 (reverse chronological order - newest change first)
670 ***************************************************************/
671
672 cursor C is select ROWID from IGS_FI_INV_INT_ALL
673 where INVOICE_ID= X_INVOICE_ID
674 ;
675 X_LAST_UPDATE_DATE DATE ;
676 X_LAST_UPDATED_BY NUMBER ;
677 X_LAST_UPDATE_LOGIN NUMBER ;
678 X_REQUEST_ID NUMBER;
679 X_PROGRAM_ID NUMBER;
680 X_PROGRAM_APPLICATION_ID NUMBER;
681 X_PROGRAM_UPDATE_DATE DATE;
682 begin
683 X_LAST_UPDATE_DATE := SYSDATE;
684 if(X_MODE = 'I') then
685 X_LAST_UPDATED_BY := 1;
686 X_LAST_UPDATE_LOGIN := 0;
687 elsif (X_MODE = 'R') then
688 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
689 if X_LAST_UPDATED_BY is NULL then
690 X_LAST_UPDATED_BY := -1;
691 end if;
692 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
693 if X_LAST_UPDATE_LOGIN is NULL then
694 X_LAST_UPDATE_LOGIN := -1;
695 end if;
696 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
697 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
698 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
699 if (X_REQUEST_ID = -1 ) then
700 X_REQUEST_ID:=NULL;
701 X_PROGRAM_ID:=NULL;
702 X_PROGRAM_APPLICATION_ID:=NULL;
703 X_PROGRAM_UPDATE_DATE:=NULL;
704 else
705 X_PROGRAM_UPDATE_DATE:=SYSDATE;
706 end if;
707 else
708 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
709 IGS_GE_MSG_STACK.ADD;
710 app_exception.raise_exception;
711 end if;
712
713 SELECT
714 IGS_FI_INV_INT_S.nextval
715 INTO
716 x_invoice_id
717 FROM
718 dual;
719
720 Before_DML(
721 p_action=>'INSERT',
722 x_rowid=>X_ROWID,
723 x_invoice_id=>X_INVOICE_ID,
724 x_person_id=>X_PERSON_ID,
725 x_fee_type=>X_FEE_TYPE,
726 x_fee_cat=>X_FEE_CAT,
727 x_fee_cal_type=>X_FEE_CAL_TYPE,
728 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
729 x_course_cd=>X_COURSE_CD,
730 x_attendance_mode=>X_ATTENDANCE_MODE,
731 x_attendance_type=>X_ATTENDANCE_TYPE,
732 x_invoice_amount_due=>X_INVOICE_AMOUNT_DUE,
733 x_invoice_creation_date=>X_INVOICE_CREATION_DATE,
734 x_invoice_desc=>X_INVOICE_DESC,
735 x_transaction_type=>X_TRANSACTION_TYPE,
736 x_currency_cd=>X_CURRENCY_CD,
737 x_status=>X_STATUS,
738 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
739 x_attribute1=>X_ATTRIBUTE1,
740 x_attribute2=>X_ATTRIBUTE2,
741 x_attribute3=>X_ATTRIBUTE3,
742 x_attribute4=>X_ATTRIBUTE4,
743 x_attribute5=>X_ATTRIBUTE5,
744 x_attribute6=>X_ATTRIBUTE6,
745 x_attribute7=>X_ATTRIBUTE7,
746 x_attribute8=>X_ATTRIBUTE8,
747 x_attribute9=>X_ATTRIBUTE9,
748 x_attribute10=>X_ATTRIBUTE10,
749 x_org_id=>igs_ge_gen_003.get_org_id,
750 x_invoice_amount => X_INVOICE_AMOUNT,
751 x_bill_id => X_BILL_ID,
752 x_bill_number => X_BILL_NUMBER ,
753 x_bill_date => X_BILL_DATE,
754 x_waiver_flag => X_WAIVER_FLAG,
755 x_waiver_reason => X_WAIVER_REASON,
756 x_effective_date => X_EFFECTIVE_DATE,
757 x_invoice_number => X_INVOICE_NUMBER ,
758 x_exchange_rate => X_EXCHANGE_RATE,
759 x_bill_payment_due_date => X_BILL_PAYMENT_DUE_DATE,
760 x_optional_fee_flag => X_OPTIONAL_FEE_FLAG,
761 x_creation_date => X_LAST_UPDATE_DATE,
762 x_created_by => X_LAST_UPDATED_BY,
763 x_last_update_date => X_LAST_UPDATE_DATE,
764 x_last_updated_by => X_LAST_UPDATED_BY,
765 x_last_update_login => X_LAST_UPDATE_LOGIN,
766 x_reversal_gl_date => x_reversal_gl_date,
767 x_tax_year_code => x_tax_year_code,
768 x_waiver_name => x_waiver_name
769 );
770
771 insert into IGS_FI_INV_INT_ALL (
772 INVOICE_ID
773 ,PERSON_ID
774 ,FEE_TYPE
775 ,FEE_CAT
776 ,FEE_CAL_TYPE
777 ,FEE_CI_SEQUENCE_NUMBER
778 ,COURSE_CD
779 ,ATTENDANCE_MODE
780 ,ATTENDANCE_TYPE
781 ,INVOICE_AMOUNT_DUE
782 ,INVOICE_CREATION_DATE
783 ,INVOICE_DESC
784 ,TRANSACTION_TYPE
785 ,CURRENCY_CD
786 ,STATUS
787 ,ATTRIBUTE_CATEGORY
788 ,ATTRIBUTE1
789 ,ATTRIBUTE2
790 ,ATTRIBUTE3
791 ,ATTRIBUTE4
792 ,ATTRIBUTE5
793 ,ATTRIBUTE6
794 ,ATTRIBUTE7
795 ,ATTRIBUTE8
796 ,ATTRIBUTE9
797 ,ATTRIBUTE10
798 ,ORG_ID
799 ,INVOICE_AMOUNT
800 ,BILL_ID
801 ,BILL_NUMBER
802 ,BILL_DATE
803 ,WAIVER_FLAG
804 ,WAIVER_REASON
805 ,EFFECTIVE_DATE
806 ,INVOICE_NUMBER
807 ,EXCHANGE_RATE
808 ,BILL_PAYMENT_DUE_DATE
809 ,OPTIONAL_FEE_FLAG
810 ,REQUEST_ID
811 ,PROGRAM_APPLICATION_ID
812 ,PROGRAM_ID
813 ,PROGRAM_UPDATE_DATE
814 ,CREATION_DATE
815 ,CREATED_BY
816 ,LAST_UPDATE_DATE
817 ,LAST_UPDATED_BY
818 ,LAST_UPDATE_LOGIN
819 ,REVERSAL_GL_DATE
820 ,TAX_YEAR_CODE
821 ,WAIVER_NAME
822 ) values (
823 NEW_REFERENCES.INVOICE_ID
824 ,NEW_REFERENCES.PERSON_ID
825 ,NEW_REFERENCES.FEE_TYPE
826 ,NEW_REFERENCES.FEE_CAT
827 ,NEW_REFERENCES.FEE_CAL_TYPE
828 ,NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER
829 ,NEW_REFERENCES.COURSE_CD
830 ,NEW_REFERENCES.ATTENDANCE_MODE
831 ,NEW_REFERENCES.ATTENDANCE_TYPE
832 ,NEW_REFERENCES.INVOICE_AMOUNT_DUE
833 ,NEW_REFERENCES.INVOICE_CREATION_DATE
834 ,NEW_REFERENCES.INVOICE_DESC
835 ,NEW_REFERENCES.TRANSACTION_TYPE
836 ,NEW_REFERENCES.CURRENCY_CD
837 ,NEW_REFERENCES.STATUS
838 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
839 ,NEW_REFERENCES.ATTRIBUTE1
840 ,NEW_REFERENCES.ATTRIBUTE2
841 ,NEW_REFERENCES.ATTRIBUTE3
842 ,NEW_REFERENCES.ATTRIBUTE4
843 ,NEW_REFERENCES.ATTRIBUTE5
844 ,NEW_REFERENCES.ATTRIBUTE6
845 ,NEW_REFERENCES.ATTRIBUTE7
846 ,NEW_REFERENCES.ATTRIBUTE8
847 ,NEW_REFERENCES.ATTRIBUTE9
848 ,NEW_REFERENCES.ATTRIBUTE10
849 ,NEW_REFERENCES.ORG_ID
850 ,NEW_REFERENCES.INVOICE_AMOUNT
851 ,NEW_REFERENCES.BILL_ID
852 ,NEW_REFERENCES.BILL_NUMBER
853 ,NEW_REFERENCES.BILL_DATE
854 ,NEW_REFERENCES.WAIVER_FLAG
855 ,NEW_REFERENCES.WAIVER_REASON
856 ,NEW_REFERENCES.EFFECTIVE_DATE
857 ,NEW_REFERENCES.INVOICE_NUMBER
858 ,NEW_REFERENCES.EXCHANGE_RATE
859 ,NEW_REFERENCES.BILL_PAYMENT_DUE_DATE
860 ,NEW_REFERENCES.OPTIONAL_FEE_FLAG
861 ,X_REQUEST_ID
862 ,X_PROGRAM_APPLICATION_ID
863 ,X_PROGRAM_ID
864 ,X_PROGRAM_UPDATE_DATE
865 ,X_LAST_UPDATE_DATE
866 ,X_LAST_UPDATED_BY
867 ,X_LAST_UPDATE_DATE
868 ,X_LAST_UPDATED_BY
869 ,X_LAST_UPDATE_LOGIN
870 ,new_references.reversal_gl_date
871 ,new_references.tax_year_code
872 ,new_references.waiver_name
873 );
874 open c;
875 fetch c into X_ROWID;
876 if (c%notfound) then
877 close c;
878 raise no_data_found;
879 end if;
880 close c;
881 After_DML (
882 p_action => 'INSERT' ,
883 x_rowid => X_ROWID );
884 end INSERT_ROW;
885
886 PROCEDURE lock_row (
887 x_rowid IN VARCHAR2,
888 x_invoice_id IN NUMBER,
889 x_person_id IN NUMBER,
890 x_fee_type IN VARCHAR2,
891 x_fee_cat IN VARCHAR2,
892 x_fee_cal_type IN VARCHAR2,
893 x_fee_ci_sequence_number IN NUMBER,
894 x_course_cd IN VARCHAR2,
895 x_attendance_mode IN VARCHAR2,
896 x_attendance_type IN VARCHAR2,
897 x_invoice_amount_due IN NUMBER,
898 x_invoice_creation_date IN DATE,
899 x_invoice_desc IN VARCHAR2,
900 x_transaction_type IN VARCHAR2,
901 x_currency_cd IN VARCHAR2,
902 x_status IN VARCHAR2,
903 x_attribute_category IN VARCHAR2,
904 x_attribute1 IN VARCHAR2,
905 x_attribute2 IN VARCHAR2,
906 x_attribute3 IN VARCHAR2,
907 x_attribute4 IN VARCHAR2,
908 x_attribute5 IN VARCHAR2,
909 x_attribute6 IN VARCHAR2,
910 x_attribute7 IN VARCHAR2,
911 x_attribute8 IN VARCHAR2,
912 x_attribute9 IN VARCHAR2,
913 x_attribute10 IN VARCHAR2,
914 x_invoice_amount IN NUMBER,
915 x_bill_id IN NUMBER,
916 x_bill_number IN VARCHAR2,
917 x_bill_date IN DATE,
918 x_waiver_flag IN VARCHAR2,
919 x_waiver_reason IN VARCHAR2,
920 x_effective_date IN DATE,
921 x_invoice_number IN VARCHAR2,
922 x_exchange_rate IN NUMBER,
923 x_bill_payment_due_date IN DATE,
924 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
925 x_reversal_gl_date IN DATE,
926 x_tax_year_code IN VARCHAR2,
927 x_waiver_name IN VARCHAR2
928 ) AS
929 /*
930 || Created By : [email protected]
931 || Created On : 03-MAY-2001
932 || Purpose : Handles the LOCK mechanism for the table.
933 || Known limitations, enhancements or remarks :
934 || Change History :
935 || Who When What
936 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
937 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column REVERSAL_GL_DATE
938 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to column subaccount_id.Also
939 || removed DEFAULT clause for parametr x_optional_fee_flag to
940 || avoid gscc warning.
941 || masehgal 10-JAN-2002 Enh # 2170429
942 || Obsoletion of SPONSOR_CD
943 || smadathi 05-oct-2001 Balance Flag reference removed .
944 || Enhancement Bug No. 2030448
945 || (reverse chronological order - newest change first)
946 */
947 CURSOR c1 IS
948 SELECT
949 person_id,
950 fee_type,
951 fee_cat,
952 fee_cal_type,
953 fee_ci_sequence_number,
954 course_cd,
955 attendance_mode,
956 attendance_type,
957 invoice_amount_due,
958 invoice_creation_date,
959 invoice_desc,
960 transaction_type,
961 currency_cd,
962 status,
963 attribute_category,
964 attribute1,
965 attribute2,
966 attribute3,
967 attribute4,
968 attribute5,
969 attribute6,
970 attribute7,
971 attribute8,
972 attribute9,
973 attribute10,
974 invoice_amount,
975 bill_id,
976 bill_number,
977 bill_date,
978 waiver_flag,
979 waiver_reason,
980 effective_date,
981 invoice_number,
982 exchange_rate,
983 bill_payment_due_date,
984 optional_fee_flag,
985 reversal_gl_date,
986 tax_year_code,
987 waiver_name
988 FROM igs_fi_inv_int_all
989 WHERE rowid = x_rowid
990 FOR UPDATE NOWAIT;
991
992 tlinfo c1%ROWTYPE;
993
994 BEGIN
995
996 OPEN c1;
997 FETCH c1 INTO tlinfo;
998 IF (c1%notfound) THEN
999 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1000 igs_ge_msg_stack.add;
1001 CLOSE c1;
1002 app_exception.raise_exception;
1003 RETURN;
1004 END IF;
1005 CLOSE c1;
1006
1007 IF (
1008 (tlinfo.person_id = x_person_id)
1009 AND (tlinfo.fee_type = x_fee_type)
1010 AND ((tlinfo.fee_cat = x_fee_cat) OR ((tlinfo.fee_cat IS NULL) AND (X_fee_cat IS NULL)))
1011 AND (tlinfo.fee_cal_type = x_fee_cal_type)
1012 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
1013 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (X_course_cd IS NULL)))
1014 AND ((tlinfo.attendance_mode = x_attendance_mode) OR ((tlinfo.attendance_mode IS NULL) AND (X_attendance_mode IS NULL)))
1015 AND ((tlinfo.attendance_type = x_attendance_type) OR ((tlinfo.attendance_type IS NULL) AND (X_attendance_type IS NULL)))
1016 AND (tlinfo.invoice_amount_due = x_invoice_amount_due)
1017 AND (trunc(tlinfo.invoice_creation_date) = trunc(x_invoice_creation_date))
1018 AND ((tlinfo.invoice_desc = x_invoice_desc) OR ((tlinfo.invoice_desc IS NULL) AND (X_invoice_desc IS NULL)))
1019 AND (tlinfo.transaction_type = x_transaction_type)
1020 AND (tlinfo.currency_cd = x_currency_cd)
1021 AND (tlinfo.status = x_status)
1022 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1023 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1024 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1025 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1026 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1027 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1028 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1029 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1030 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1031 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1032 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1033 AND ((tlinfo.invoice_amount = x_invoice_amount) OR ((tlinfo.invoice_amount IS NULL) AND (X_invoice_amount IS NULL)))
1034 AND ((tlinfo.bill_id = x_bill_id) OR ((tlinfo.bill_id IS NULL) AND (X_bill_id IS NULL)))
1035 AND ((tlinfo.bill_number = x_bill_number) OR ((tlinfo.bill_number IS NULL) AND (X_bill_number IS NULL)))
1036 AND ((trunc(tlinfo.bill_date) = trunc(x_bill_date)) OR ((tlinfo.bill_date IS NULL) AND (X_bill_date IS NULL)))
1037 AND ((tlinfo.waiver_flag = x_waiver_flag) OR ((tlinfo.waiver_flag IS NULL) AND (X_waiver_flag IS NULL)))
1038 AND ((tlinfo.waiver_reason = x_waiver_reason) OR ((tlinfo.waiver_reason IS NULL) AND (X_waiver_reason IS NULL)))
1039 AND ((trunc(tlinfo.effective_date) = trunc(x_effective_date)) OR ((tlinfo.effective_date IS NULL) AND (X_effective_date IS NULL)))
1040 AND ((tlinfo.invoice_number = x_invoice_number) OR ((tlinfo.invoice_number IS NULL) AND (X_invoice_number IS NULL)))
1041 AND ((tlinfo.exchange_rate = x_exchange_rate) OR ((tlinfo.exchange_rate IS NULL) AND (X_exchange_rate IS NULL)))
1042 AND ((trunc(tlinfo.bill_payment_due_date) = trunc(x_bill_payment_due_date)) OR ((tlinfo.bill_payment_due_date IS NULL) AND (X_bill_payment_due_date IS NULL)))
1043 AND ((tlinfo.optional_fee_flag = x_optional_fee_flag) OR ((tlinfo.optional_fee_flag IS NULL) AND (X_optional_fee_flag IS NULL)))
1044 AND ((TRUNC(tlinfo.reversal_gl_date) = TRUNC(x_reversal_gl_date)) OR ((tlinfo.reversal_gl_date IS NULL) AND (X_reversal_gl_date IS NULL)))
1045 AND ((tlinfo.tax_year_code = x_tax_year_code) OR ((tlinfo.tax_year_code IS NULL) AND (x_tax_year_code IS NULL)))
1046 AND ((tlinfo.waiver_name = x_waiver_name) OR ((tlinfo.waiver_name IS NULL) AND (X_waiver_name IS NULL)))
1047 ) THEN
1048 NULL;
1049 ELSE
1050 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1051 igs_ge_msg_stack.add;
1052 app_exception.raise_exception;
1053 END IF;
1054
1055 RETURN;
1056
1057 END lock_row;
1058
1059 Procedure UPDATE_ROW (
1060 X_ROWID in VARCHAR2,
1061 x_INVOICE_ID IN NUMBER,
1062 x_PERSON_ID IN NUMBER,
1063 x_FEE_TYPE IN VARCHAR2,
1064 x_FEE_CAT IN VARCHAR2,
1065 x_FEE_CAL_TYPE IN VARCHAR2,
1066 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
1067 x_COURSE_CD IN VARCHAR2,
1068 x_ATTENDANCE_MODE IN VARCHAR2,
1069 x_ATTENDANCE_TYPE IN VARCHAR2,
1070 x_INVOICE_AMOUNT_DUE IN NUMBER,
1071 x_INVOICE_CREATION_DATE IN DATE,
1072 x_INVOICE_DESC IN VARCHAR2,
1073 x_TRANSACTION_TYPE IN VARCHAR2,
1074 x_CURRENCY_CD IN VARCHAR2,
1075 x_STATUS IN VARCHAR2,
1076 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1077 x_ATTRIBUTE1 IN VARCHAR2,
1078 x_ATTRIBUTE2 IN VARCHAR2,
1079 x_ATTRIBUTE3 IN VARCHAR2,
1080 x_ATTRIBUTE4 IN VARCHAR2,
1081 x_ATTRIBUTE5 IN VARCHAR2,
1082 x_ATTRIBUTE6 IN VARCHAR2,
1083 x_ATTRIBUTE7 IN VARCHAR2,
1084 x_ATTRIBUTE8 IN VARCHAR2,
1085 x_ATTRIBUTE9 IN VARCHAR2,
1086 x_ATTRIBUTE10 IN VARCHAR2,
1087 x_INVOICE_AMOUNT IN NUMBER,
1088 x_BILL_ID IN NUMBER,
1089 x_BILL_NUMBER IN VARCHAR2,
1090 x_BILL_DATE IN DATE,
1091 x_WAIVER_FLAG IN VARCHAR2,
1092 x_WAIVER_REASON IN VARCHAR2,
1093 x_EFFECTIVE_DATE IN DATE,
1094 x_INVOICE_NUMBER IN VARCHAR2,
1095 x_EXCHANGE_RATE IN NUMBER,
1096 x_BILL_PAYMENT_DUE_DATE IN DATE,
1097 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
1098 X_MODE IN VARCHAR2,
1099 x_reversal_gl_date IN DATE,
1100 x_tax_year_code IN VARCHAR2,
1101 x_waiver_name IN VARCHAR2
1102 ) AS
1103 /*************************************************************
1104 Created By :
1105 Date Created By :
1106 Purpose :
1107 Know limitations, enhancements or remarks
1108 Change History
1109 Who When What
1110 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
1111 smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column REVERSAL_GL_DATE
1112 vvutukur 17-Sep-2002 Enh#2564643.Removed references to column subaccount_id.Also removed
1113 DEFAULT clause from procedure parameters to avoid gscc warnings.
1114 masehgal 10-JAN-2002 Enh # 2170429
1115 Obsoletion of SPONSOR_CD
1116 smadathi 05-oct-2001 Balance Flag reference removed .
1117 Enhancement Bug No. 2030448
1118 (reverse chronological order - newest change first)
1119 ***************************************************************/
1120
1121 X_LAST_UPDATE_DATE DATE ;
1122 X_LAST_UPDATED_BY NUMBER ;
1123 X_LAST_UPDATE_LOGIN NUMBER ;
1124 X_REQUEST_ID NUMBER;
1125 X_PROGRAM_ID NUMBER;
1126 X_PROGRAM_APPLICATION_ID NUMBER;
1127 X_PROGRAM_UPDATE_DATE DATE;
1128 begin
1129 X_LAST_UPDATE_DATE := SYSDATE;
1130 if(X_MODE = 'I') then
1131 X_LAST_UPDATED_BY := 1;
1132 X_LAST_UPDATE_LOGIN := 0;
1133 elsif (X_MODE = 'R') then
1134 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1135 if X_LAST_UPDATED_BY is NULL then
1136 X_LAST_UPDATED_BY := -1;
1137 end if;
1138 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1139 if X_LAST_UPDATE_LOGIN is NULL then
1140 X_LAST_UPDATE_LOGIN := -1;
1141 end if;
1142 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
1143 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
1144 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
1145 if (X_REQUEST_ID = -1 ) then
1146 X_REQUEST_ID:=OLD_REFERENCES.REQUEST_ID;
1147 X_PROGRAM_ID:=OLD_REFERENCES.PROGRAM_ID;
1148 X_PROGRAM_APPLICATION_ID:=OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1149 X_PROGRAM_UPDATE_DATE:=OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1150 else
1151 X_PROGRAM_UPDATE_DATE:=SYSDATE;
1152 end if;
1153 else
1154 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1155 IGS_GE_MSG_STACK.ADD;
1156 app_exception.raise_exception;
1157 end if;
1158 Before_DML(
1159 p_action =>'UPDATE',
1160 x_rowid =>X_ROWID,
1161 x_invoice_id =>X_INVOICE_ID,
1162 x_person_id =>X_PERSON_ID,
1163 x_fee_type =>X_FEE_TYPE,
1164 x_fee_cat =>X_FEE_CAT,
1165 x_fee_cal_type=>X_FEE_CAL_TYPE,
1166 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
1167 x_course_cd =>X_COURSE_CD,
1168 x_attendance_mode=>X_ATTENDANCE_MODE,
1169 x_attendance_type=>X_ATTENDANCE_TYPE,
1170 x_invoice_amount_due=>X_INVOICE_AMOUNT_DUE,
1171 x_invoice_creation_date=>X_INVOICE_CREATION_DATE,
1172 x_invoice_desc=>X_INVOICE_DESC,
1173 x_transaction_type=>X_TRANSACTION_TYPE,
1174 x_currency_cd=>X_CURRENCY_CD,
1175 x_status=>X_STATUS,
1176 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1177 x_attribute1=>X_ATTRIBUTE1,
1178 x_attribute2=>X_ATTRIBUTE2,
1179 x_attribute3=>X_ATTRIBUTE3,
1180 x_attribute4=>X_ATTRIBUTE4,
1181 x_attribute5=>X_ATTRIBUTE5,
1182 x_attribute6=>X_ATTRIBUTE6,
1183 x_attribute7=>X_ATTRIBUTE7,
1184 x_attribute8=>X_ATTRIBUTE8,
1185 x_attribute9=>X_ATTRIBUTE9,
1186 x_attribute10=>X_ATTRIBUTE10,
1187 x_invoice_amount => X_INVOICE_AMOUNT,
1188 x_bill_id => X_BILL_ID,
1189 x_bill_number => X_BILL_NUMBER ,
1190 x_bill_date => X_BILL_DATE,
1191 x_waiver_flag => X_WAIVER_FLAG,
1192 x_waiver_reason => X_WAIVER_REASON,
1193 x_effective_date => X_EFFECTIVE_DATE,
1194 x_invoice_number => X_INVOICE_NUMBER ,
1195 x_exchange_rate => X_EXCHANGE_RATE,
1196 x_bill_payment_due_date => X_BILL_PAYMENT_DUE_DATE,
1197 x_optional_fee_flag => X_OPTIONAL_FEE_FLAG,
1198 x_creation_date => X_LAST_UPDATE_DATE,
1199 x_created_by => X_LAST_UPDATED_BY,
1200 x_last_update_date => X_LAST_UPDATE_DATE,
1201 x_last_updated_by => X_LAST_UPDATED_BY,
1202 x_last_update_login => X_LAST_UPDATE_LOGIN,
1203 x_reversal_gl_date => x_reversal_gl_date,
1204 x_tax_year_code => x_tax_year_code,
1205 x_waiver_name => x_waiver_name
1206 );
1207
1208 update IGS_FI_INV_INT_ALL set
1209 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1210 FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
1211 FEE_CAT = NEW_REFERENCES.FEE_CAT,
1212 FEE_CAL_TYPE = NEW_REFERENCES.FEE_CAL_TYPE,
1213 FEE_CI_SEQUENCE_NUMBER = NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
1214 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1215 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1216 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1217 INVOICE_AMOUNT_DUE = NEW_REFERENCES.INVOICE_AMOUNT_DUE,
1218 INVOICE_CREATION_DATE = NEW_REFERENCES.INVOICE_CREATION_DATE,
1219 INVOICE_DESC = NEW_REFERENCES.INVOICE_DESC,
1220 TRANSACTION_TYPE = NEW_REFERENCES.TRANSACTION_TYPE,
1221 CURRENCY_CD = NEW_REFERENCES.CURRENCY_CD,
1222 STATUS = NEW_REFERENCES.STATUS,
1223 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1224 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1225 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1226 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1227 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1228 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1229 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1230 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1231 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1232 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1233 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1234 INVOICE_AMOUNT = NEW_REFERENCES.INVOICE_AMOUNT,
1235 BILL_ID = NEW_REFERENCES.BILL_ID ,
1236 BILL_NUMBER = NEW_REFERENCES.BILL_NUMBER ,
1237 BILL_DATE = NEW_REFERENCES.BILL_DATE ,
1238 WAIVER_FLAG = NEW_REFERENCES.WAIVER_FLAG ,
1239 WAIVER_REASON = NEW_REFERENCES.WAIVER_REASON ,
1240 EFFECTIVE_DATE = NEW_REFERENCES.EFFECTIVE_DATE ,
1241 INVOICE_NUMBER = NEW_REFERENCES.INVOICE_NUMBER ,
1242 EXCHANGE_RATE = NEW_REFERENCES.EXCHANGE_RATE ,
1243 BILL_PAYMENT_DUE_DATE = NEW_REFERENCES.BILL_PAYMENT_DUE_DATE ,
1244 OPTIONAL_FEE_FLAG = NEW_REFERENCES.OPTIONAL_FEE_FLAG,
1245 REQUEST_ID = NEW_REFERENCES.REQUEST_ID ,
1246 PROGRAM_APPLICATION_ID = NEW_REFERENCES.PROGRAM_APPLICATION_ID ,
1247 PROGRAM_ID = NEW_REFERENCES.PROGRAM_ID ,
1248 PROGRAM_UPDATE_DATE = NEW_REFERENCES.PROGRAM_UPDATE_DATE ,
1249 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1250 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1251 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1252 reversal_gl_date = new_references.reversal_gl_date,
1253 tax_year_code = new_references.tax_year_code,
1254 waiver_name = new_references.waiver_name
1255 where ROWID = X_ROWID;
1256 if (sql%notfound) then
1257 raise no_data_found;
1258 end if;
1259
1260 After_DML (
1261 p_action => 'UPDATE' ,
1262 x_rowid => X_ROWID
1263 );
1264 end UPDATE_ROW;
1265
1266 procedure ADD_ROW (
1267 X_ROWID in out NOCOPY VARCHAR2,
1268 x_INVOICE_ID IN OUT NOCOPY NUMBER,
1269 x_PERSON_ID IN NUMBER,
1270 x_FEE_TYPE IN VARCHAR2,
1271 x_FEE_CAT IN VARCHAR2,
1272 x_FEE_CAL_TYPE IN VARCHAR2,
1273 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
1274 x_COURSE_CD IN VARCHAR2,
1275 x_ATTENDANCE_MODE IN VARCHAR2,
1276 x_ATTENDANCE_TYPE IN VARCHAR2,
1277 x_INVOICE_AMOUNT_DUE IN NUMBER,
1278 x_INVOICE_CREATION_DATE IN DATE,
1279 x_INVOICE_DESC IN VARCHAR2,
1280 x_TRANSACTION_TYPE IN VARCHAR2,
1281 x_CURRENCY_CD IN VARCHAR2,
1282 x_STATUS IN VARCHAR2,
1283 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1284 x_ATTRIBUTE1 IN VARCHAR2,
1285 x_ATTRIBUTE2 IN VARCHAR2,
1286 x_ATTRIBUTE3 IN VARCHAR2,
1287 x_ATTRIBUTE4 IN VARCHAR2,
1288 x_ATTRIBUTE5 IN VARCHAR2,
1289 x_ATTRIBUTE6 IN VARCHAR2,
1290 x_ATTRIBUTE7 IN VARCHAR2,
1291 x_ATTRIBUTE8 IN VARCHAR2,
1292 x_ATTRIBUTE9 IN VARCHAR2,
1293 x_ATTRIBUTE10 IN VARCHAR2,
1294 x_ORG_ID IN NUMBER,
1295 x_INVOICE_AMOUNT IN NUMBER,
1296 x_BILL_ID IN NUMBER,
1297 x_BILL_NUMBER IN VARCHAR2,
1298 x_BILL_DATE IN DATE,
1299 x_WAIVER_FLAG IN VARCHAR2,
1300 x_WAIVER_REASON IN VARCHAR2,
1301 x_EFFECTIVE_DATE IN DATE,
1302 x_INVOICE_NUMBER IN VARCHAR2,
1303 x_EXCHANGE_RATE IN NUMBER,
1304 x_BILL_PAYMENT_DUE_DATE IN DATE,
1305 x_OPTIONAL_FEE_FLAG IN VARCHAR2,
1306 X_MODE IN VARCHAR2,
1307 x_reversal_gl_date IN DATE,
1308 x_tax_year_code IN VARCHAR2,
1309 x_waiver_name IN VARCHAR2
1310 ) AS
1311 /*************************************************************
1312 Created By :
1313 Date Created By :
1314 Purpose :
1315 Know limitations, enhancements or remarks
1316 Change History
1317 Who When What
1318 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
1319 smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column REVERSAL_GL_DATE
1320 vvutukur 17-Sep-2002 Enh#2564643.Removed references to column subaccount_id.Also removed
1321 DEFAULT clause from procedure parameters to avoid gscc warnings.
1322 masehgal 10-JAN-2002 Enh # 2170429
1323 Obsoletion of SPONSOR_CD
1324 smadathi 05-oct-2001 Balance Flag reference removed .
1325 Enhancement Bug No. 2030448
1326 (reverse chronological order - newest change first)
1327 ***************************************************************/
1328
1329 cursor c1 is select ROWID from IGS_FI_INV_INT_ALL
1330 where INVOICE_ID= X_INVOICE_ID
1331 ;
1332 begin
1333 open c1;
1334 fetch c1 into X_ROWID;
1335 if (c1%notfound) then
1336 close c1;
1337 INSERT_ROW (
1338 X_ROWID,
1339 X_INVOICE_ID,
1340 X_PERSON_ID,
1341 X_FEE_TYPE,
1342 X_FEE_CAT,
1343 X_FEE_CAL_TYPE,
1344 X_FEE_CI_SEQUENCE_NUMBER,
1345 X_COURSE_CD,
1346 X_ATTENDANCE_MODE,
1347 X_ATTENDANCE_TYPE,
1348 X_INVOICE_AMOUNT_DUE,
1349 X_INVOICE_CREATION_DATE,
1350 X_INVOICE_DESC,
1351 X_TRANSACTION_TYPE,
1352 X_CURRENCY_CD,
1353 X_STATUS,
1354 X_ATTRIBUTE_CATEGORY,
1355 X_ATTRIBUTE1,
1356 X_ATTRIBUTE2,
1357 X_ATTRIBUTE3,
1358 X_ATTRIBUTE4,
1359 X_ATTRIBUTE5,
1360 X_ATTRIBUTE6,
1361 X_ATTRIBUTE7,
1362 X_ATTRIBUTE8,
1363 X_ATTRIBUTE9,
1364 X_ATTRIBUTE10,
1365 X_INVOICE_AMOUNT ,
1366 X_BILL_ID ,
1367 X_BILL_NUMBER ,
1368 X_BILL_DATE ,
1369 X_WAIVER_FLAG ,
1370 X_WAIVER_REASON ,
1371 X_EFFECTIVE_DATE ,
1372 X_INVOICE_NUMBER ,
1373 X_EXCHANGE_RATE ,
1374 X_BILL_PAYMENT_DUE_DATE,
1375 X_ORG_ID,
1376 x_OPTIONAL_FEE_FLAG,
1377 X_MODE ,
1378 X_REVERSAL_GL_DATE,
1379 x_tax_year_code,
1380 x_waiver_name
1381 );
1382 return;
1383 end if;
1384 close c1;
1385 UPDATE_ROW (
1386 X_ROWID,
1387 X_INVOICE_ID,
1388 X_PERSON_ID,
1389 X_FEE_TYPE,
1390 X_FEE_CAT,
1391 X_FEE_CAL_TYPE,
1392 X_FEE_CI_SEQUENCE_NUMBER,
1393 X_COURSE_CD,
1394 X_ATTENDANCE_MODE,
1395 X_ATTENDANCE_TYPE,
1396 X_INVOICE_AMOUNT_DUE,
1397 X_INVOICE_CREATION_DATE,
1398 X_INVOICE_DESC,
1399 X_TRANSACTION_TYPE,
1400 X_CURRENCY_CD,
1401 X_STATUS,
1402 X_ATTRIBUTE_CATEGORY,
1403 X_ATTRIBUTE1,
1404 X_ATTRIBUTE2,
1405 X_ATTRIBUTE3,
1406 X_ATTRIBUTE4,
1407 X_ATTRIBUTE5,
1408 X_ATTRIBUTE6,
1409 X_ATTRIBUTE7,
1410 X_ATTRIBUTE8,
1411 X_ATTRIBUTE9,
1412 X_ATTRIBUTE10,
1413 X_INVOICE_AMOUNT ,
1414 X_BILL_ID ,
1415 X_BILL_NUMBER ,
1416 X_BILL_DATE ,
1417 X_WAIVER_FLAG ,
1418 X_WAIVER_REASON ,
1419 X_EFFECTIVE_DATE ,
1420 X_INVOICE_NUMBER ,
1421 X_EXCHANGE_RATE ,
1422 X_BILL_PAYMENT_DUE_DATE,
1423 x_OPTIONAL_FEE_FLAG,
1424 X_MODE,
1425 X_REVERSAL_GL_DATE,
1426 x_tax_year_code,
1427 x_waiver_name
1428 );
1429 end ADD_ROW;
1430 procedure DELETE_ROW (
1431 X_ROWID in VARCHAR2
1432 ) AS
1433 /*************************************************************
1434 Created By :
1435 Date Created By :
1436 Purpose :
1437 Know limitations, enhancements or remarks
1438 Change History
1439 Who When What
1440
1441 (reverse chronological order - newest change first)
1442 ***************************************************************/
1443
1444 begin
1445 Before_DML (
1446 p_action => 'DELETE',
1447 x_rowid => X_ROWID
1448 );
1449 delete from IGS_FI_INV_INT_ALL
1450 where ROWID = X_ROWID;
1451 if (sql%notfound) then
1452 raise no_data_found;
1453 end if;
1454 After_DML (
1455 p_action => 'DELETE',
1456 x_rowid => X_ROWID
1457 );
1458 end DELETE_ROW;
1459 END igs_fi_inv_int_pkg;