[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_CREDITS_PKG
Source
1 PACKAGE BODY igs_fi_credits_pkg AS
2 /* $Header: IGSSI86B.pls 120.2 2005/08/08 01:20:45 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_credits_all%ROWTYPE;
6 new_references igs_fi_credits_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_credit_id IN NUMBER ,
12 x_credit_number IN VARCHAR2,
13 x_status IN VARCHAR2,
14 x_credit_source IN VARCHAR2,
15 x_party_id IN NUMBER ,
16 x_credit_type_id IN NUMBER ,
17 x_credit_instrument IN VARCHAR2,
18 x_description IN VARCHAR2,
19 x_amount IN NUMBER ,
20 x_currency_cd IN VARCHAR2,
21 x_exchange_rate IN NUMBER ,
22 x_transaction_date IN DATE ,
23 x_effective_date IN DATE ,
24 x_reversal_date IN DATE ,
25 x_reversal_reason_code IN VARCHAR2,
26 x_reversal_comments IN VARCHAR2,
27 x_unapplied_amount IN NUMBER ,
28 x_source_transaction_id IN NUMBER ,
29 x_receipt_lockbox_number IN VARCHAR2,
30 x_merchant_id IN VARCHAR2,
31 x_credit_card_code IN VARCHAR2,
32 x_credit_card_holder_name IN VARCHAR2,
33 x_credit_card_number IN VARCHAR2,
34 x_credit_card_expiration_date IN DATE ,
35 x_credit_card_approval_code IN VARCHAR2,
36 x_awd_yr_cal_type IN VARCHAR2,
37 x_awd_yr_ci_sequence_number IN NUMBER ,
38 x_fee_cal_type IN VARCHAR2,
39 x_fee_ci_sequence_number IN NUMBER ,
40 x_attribute_category IN VARCHAR2,
41 x_attribute1 IN VARCHAR2,
42 x_attribute2 IN VARCHAR2,
43 x_attribute3 IN VARCHAR2,
44 x_attribute4 IN VARCHAR2,
45 x_attribute5 IN VARCHAR2,
46 x_attribute6 IN VARCHAR2,
47 x_attribute7 IN VARCHAR2,
48 x_attribute8 IN VARCHAR2,
49 x_attribute9 IN VARCHAR2,
50 x_attribute10 IN VARCHAR2,
51 x_attribute11 IN VARCHAR2,
52 x_attribute12 IN VARCHAR2,
53 x_attribute13 IN VARCHAR2,
54 x_attribute14 IN VARCHAR2,
55 x_attribute15 IN VARCHAR2,
56 x_attribute16 IN VARCHAR2,
57 x_attribute17 IN VARCHAR2,
58 x_attribute18 IN VARCHAR2,
59 x_attribute19 IN VARCHAR2,
60 x_attribute20 IN VARCHAR2,
61 x_creation_date IN DATE ,
62 x_created_by IN NUMBER ,
63 x_last_update_date IN DATE ,
64 x_last_updated_by IN NUMBER ,
65 x_last_update_login IN NUMBER ,
66 x_gl_date IN DATE ,
67 x_check_number IN VARCHAR2,
68 x_source_transaction_type IN VARCHAR2,
69 x_source_transaction_ref IN VARCHAR2,
70 x_credit_card_status_code IN VARCHAR2,
71 x_credit_card_payee_cd IN VARCHAR2,
72 x_credit_card_tangible_cd IN VARCHAR2,
73 x_lockbox_interface_id IN NUMBER ,
74 x_batch_name IN VARCHAR2,
75 x_deposit_date IN DATE,
76 x_source_invoice_id IN NUMBER,
77 x_tax_year_code IN VARCHAR2,
78 x_waiver_name IN VARCHAR2
79 ) AS
80 /*
81 || Created By : BDEVARAK
82 || Created On : 26-APR-2001
83 || Purpose : Initialises the Old and New references for the columns of the table.
84 || Known limitations, enhancements or remarks :
85 || Change History :
86 || Who When What
87 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
88 || Added the Waiver name column
89 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
90 || Added new column source_invoice_id
91 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
92 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
93 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
94 || source_transaction_type and source_transaction_ref
95 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE
96 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id.Also removed
97 || DEFAULT clause from procedure parameter list.
98 || SMVK 04-Feb-2002 Updated existing procedure for
99 || Four parameters awd_yr_cal_type
100 || awd_yr_ci_sequence_number
101 || fee_cal_type, fee_ci_sequence_number
102 || Enhancement Bug No.2191470
103 || smadathi 05-oct-2001 Balance Flag reference removed .
104 || Enhancement Bug No. 2030448
105 || (reverse chronological order - newest change first)
106 */
107
108 CURSOR cur_old_ref_values IS
109 SELECT *
110 FROM IGS_FI_CREDITS_ALL
111 WHERE rowid = x_rowid;
112
113 BEGIN
114
115 l_rowid := x_rowid;
116
117 -- Code for setting the Old and New Reference Values.
118 -- Populate Old Values.
119 OPEN cur_old_ref_values;
120 FETCH cur_old_ref_values INTO old_references;
121 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
122 CLOSE cur_old_ref_values;
123 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
124 igs_ge_msg_stack.add;
125 app_exception.raise_exception;
126 RETURN;
127 END IF;
128 CLOSE cur_old_ref_values;
129
130 -- Populate New Values.
131 new_references.credit_id := x_credit_id;
132 new_references.credit_number := x_credit_number;
133 new_references.status := x_status;
134 new_references.credit_source := x_credit_source;
135 new_references.party_id := x_party_id;
136 new_references.credit_type_id := x_credit_type_id;
137 new_references.credit_instrument := x_credit_instrument;
138 new_references.description := x_description;
139 new_references.amount := x_amount;
140 new_references.currency_cd := x_currency_cd;
141 new_references.exchange_rate := x_exchange_rate;
142 new_references.transaction_date := x_transaction_date;
143 new_references.effective_date := x_effective_date;
144 new_references.reversal_date := x_reversal_date;
145 new_references.reversal_reason_code := x_reversal_reason_code;
146 new_references.reversal_comments := x_reversal_comments;
147 new_references.unapplied_amount := x_unapplied_amount;
148 new_references.source_transaction_id := x_source_transaction_id;
149 new_references.receipt_lockbox_number := x_receipt_lockbox_number;
150 new_references.merchant_id := x_merchant_id;
151 new_references.credit_card_code := x_credit_card_code;
152 new_references.credit_card_holder_name := x_credit_card_holder_name;
153 new_references.credit_card_number := x_credit_card_number;
154 new_references.credit_card_expiration_date := x_credit_card_expiration_date;
155 new_references.credit_card_approval_code := x_credit_card_approval_code;
156 new_references.awd_yr_cal_type := x_awd_yr_cal_type;
157 new_references.awd_yr_ci_sequence_number := x_awd_yr_ci_sequence_number;
158 new_references.fee_cal_type := x_fee_cal_type;
159 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
160 new_references.attribute_category := x_attribute_category;
161 new_references.attribute1 := x_attribute1;
162 new_references.attribute2 := x_attribute2;
163 new_references.attribute3 := x_attribute3;
164 new_references.attribute4 := x_attribute4;
165 new_references.attribute5 := x_attribute5;
166 new_references.attribute6 := x_attribute6;
167 new_references.attribute7 := x_attribute7;
168 new_references.attribute8 := x_attribute8;
169 new_references.attribute9 := x_attribute9;
170 new_references.attribute10 := x_attribute10;
171 new_references.attribute11 := x_attribute11;
172 new_references.attribute12 := x_attribute12;
173 new_references.attribute13 := x_attribute13;
174 new_references.attribute14 := x_attribute14;
175 new_references.attribute15 := x_attribute15;
176 new_references.attribute16 := x_attribute16;
177 new_references.attribute17 := x_attribute17;
178 new_references.attribute18 := x_attribute18;
179 new_references.attribute19 := x_attribute19;
180 new_references.attribute20 := x_attribute20;
181 new_references.gl_date := TRUNC(x_gl_date);
182 new_references.check_number := x_check_number;
183 new_references.source_transaction_type := x_source_transaction_type;
184 new_references.source_transaction_ref := x_source_transaction_ref;
185 new_references.credit_card_status_code := x_credit_card_status_code;
186 new_references.credit_card_payee_cd := x_credit_card_payee_cd;
187 new_references.credit_card_tangible_cd := x_credit_card_tangible_cd;
188 new_references.lockbox_interface_id := x_lockbox_interface_id;
189 new_references.batch_name := x_batch_name;
190 new_references.deposit_date := TRUNC(x_deposit_date);
191 new_references.source_invoice_id := x_source_invoice_id;
192 new_references.waiver_name := x_waiver_name;
193
194 IF (p_action = 'UPDATE') THEN
195 new_references.creation_date := old_references.creation_date;
196 new_references.created_by := old_references.created_by;
197 ELSE
198 new_references.creation_date := x_creation_date;
199 new_references.created_by := x_created_by;
200 END IF;
201
202 new_references.last_update_date := x_last_update_date;
203 new_references.last_updated_by := x_last_updated_by;
204 new_references.last_update_login := x_last_update_login;
205 new_references.tax_year_code := x_tax_year_code;
206
207 END set_column_values;
208
209 PROCEDURE BeforeRowInsertUpdate AS
210 /*
211 || Created By : VVUTUKUR
212 || Created On : 14-MAY-2002
213 || Purpose : For validating reversal date with payment date.
214 || Known limitations, enhancements or remarks :
215 || Change History :
216 || Who When What
217 || (reverse chronological order - newest change first)
218 */
219 BEGIN
220
221 --If reversal date specified in reversal form is less than payment date of a credit,
222 IF TRUNC(new_references.reversal_date) IS NOT NULL THEN
223 IF (TRUNC(new_references.reversal_date) < TRUNC(new_references.transaction_date)) THEN
224 --Throw error message
225 fnd_message.set_name('IGS','IGS_FI_REV_DT_LESS_RECT_DT');
226 fnd_message.set_token('REVDATE',TRUNC(new_references.reversal_date));
227 fnd_message.set_token('PMTDATE',TRUNC(new_references.transaction_date));
228 fnd_message.set_token('CREDIT',new_references.credit_number);
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 END IF;
232 END IF;
233 END BeforeRowInsertUpdate;
234
235 PROCEDURE check_uniqueness AS
236 /*
237 || Created By : BDEVARAK
238 || Created On : 26-APR-2001
239 || Purpose : Handles the Unique Constraint logic defined for the columns.
240 || Known limitations, enhancements or remarks :
241 || Change History :
242 || Who When What
243 || shtatiko 09-APR-2003 Enh# 2831554, Changed the message to IGS_FI_CREDIT_DUPLICATE
244 || from IGS_GE_RECORD_ALREADY_EXISTS
245 || (reverse chronological order - newest change first)
246 */
247 BEGIN
248
249 IF ( get_uk_for_validation (
250 new_references.credit_number,
251 new_references.party_id
252 )
253 ) THEN
254 fnd_message.set_name ('IGS', 'IGS_FI_CREDIT_DUPLICATE');
255 igs_ge_msg_stack.add;
256 app_exception.raise_exception;
257 END IF;
258
259 END check_uniqueness;
260
261 PROCEDURE check_parent_existance AS
262 /*
263 || Created By : BDEVARAK
264 || Created On : 26-APR-2001
265 || Purpose : Checks for the existance of Parent records.
266 || Known limitations, enhancements or remarks :
267 || Change History :
268 || Who When What
269 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build Added the IGS_FI_WAV_REVERSAL_REASON
270 || lookup code validation
271 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
272 || Added new column source_invoice_id - FK with igs_fi_inv_int_all
273 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added check for the column receipt_lockbox_number in parent table.
274 || shtatiko 09-DEC-2002 Added condition for source_transaction_type.
275 || vvutukur 17-Aug-2002 Removed call to igs_fi_subaccts_pkg.get_pk_for_validation and
276 || related code as part of subaccount.
277 || smadathi 10-Jun-2002 Bug 2404523. The row share table lock on the table hz_parties
278 || and igf_lookups_view removed.
279 || sykrishn 8-FEB-2002 Removed get pk for validation
280 || with igs_lookups_view for credit source
281 || and introdiced linek with IGF_LOOKUPS_VIEW - 2191470
282 SFCR020
283 || SMVK 04-Feb-2002 Checking included for
284 || Four parameters awd_yr_cal_type
285 || awd_yr_ci_sequence_number
286 || fee_cal_type, fee_ci_sequence_number
287 || Enhancement Bug No.2191470
288 || (reverse chronological order - newest change first)
289 */
290
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM hz_parties
294 WHERE party_id = new_references.party_id;
295
296
297 lv_rowid cur_rowid%RowType;
298
299
300 CURSOR cur_igf_lookup IS
301 SELECT rowid
302 FROM igf_lookups_view
303 WHERE lookup_type = 'IGF_AW_FED_FUND'
304 AND lookup_code = new_references.credit_source;
305
306
307 lv_igf_rowid cur_igf_lookup%RowType;
308
309 BEGIN
310
311 IF (((old_references.party_id = new_references.party_id)) OR
312 ((new_references.party_id IS NULL))) THEN
313 NULL;
314 ELSE
315 OPEN cur_rowid;
316 FETCH cur_rowid INTO lv_rowid;
317 IF (cur_rowid%FOUND) THEN
318 CLOSE cur_rowid;
319 ELSE
320 CLOSE cur_rowid;
321 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 END IF;
325 END IF;
326
327 IF (((old_references.status = new_references.status)) OR
328 ((new_references.status IS NULL))) THEN
329 NULL;
330 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
331 'IGS_FI_CREDIT_STATUS',
332 new_references.status
333 )THEN
334 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338
339 IF (((old_references.credit_source = new_references.credit_source)) OR
340 ((new_references.credit_source IS NULL))) THEN
341 NULL;
342 ELSE
343 OPEN cur_igf_lookup;
344 FETCH cur_igf_lookup INTO lv_igf_rowid;
345 IF (cur_igf_lookup%FOUND) THEN
346 CLOSE cur_igf_lookup;
347 ELSE
348 CLOSE cur_igf_lookup;
349 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
350 igs_ge_msg_stack.add;
351 app_exception.raise_exception;
352 END IF;
353 END IF;
354
355 IF (((old_references.credit_instrument = new_references.credit_instrument)) OR
356 ((new_references.credit_instrument IS NULL))) THEN
357 NULL;
358 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
359 'IGS_FI_CREDIT_INSTRUMENT',
360 new_references.credit_instrument
361 )THEN
362 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END IF;
366 --Added the IGS_FI_WAV_REVERSAL_REASON lookup code validation as a part of Tution Waiver Build.
367 IF (((old_references.reversal_reason_code = new_references.reversal_reason_code)) OR
368 ((new_references.reversal_reason_code IS NULL))) THEN
369 NULL;
370 ELSIF NOT ( IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation('IGS_FI_REVERSAL_REASON',new_references.reversal_reason_code)
371 OR IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation('IGS_FI_WAV_REVERSAL_REASON',new_references.reversal_reason_code)
372 )THEN
373 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377
378 IF (((old_references.credit_type_id = new_references.credit_type_id)) OR
379 ((new_references.credit_type_id IS NULL))) THEN
380 NULL;
381 ELSIF NOT igs_fi_cr_types_pkg.get_pk_for_validation (
382 new_references.credit_type_id
383 ) THEN
384 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
385 igs_ge_msg_stack.add;
386 app_exception.raise_exception;
387 END IF;
388
389 --removed call to igs_fi_subaccts_pkg.get_pk_for_validation and related code as part of subaccount
390 --removal build.Enh#2564643.
391
392 IF (
393 ((old_references.awd_yr_cal_type = new_references.awd_yr_cal_type) AND
394 (old_references.awd_yr_ci_sequence_number = new_references.awd_yr_ci_sequence_number)) OR
395
396 ((new_references.awd_yr_cal_type IS NULL) OR
397 (new_references.awd_yr_ci_sequence_number IS NULL))
398 ) THEN
399 NULL;
400 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
401 new_references.awd_yr_cal_type , new_references.awd_yr_ci_sequence_number
402 ) THEN
403 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
404 igs_ge_msg_stack.add;
405 app_exception.raise_exception;
406 END IF;
407
408 IF (
409 ((old_references.fee_cal_type = new_references.fee_cal_type) AND
410 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
411
412 ((new_references.fee_cal_type IS NULL) OR
413 (new_references.fee_ci_sequence_number IS NULL))
414 ) THEN
415 NULL;
416 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
417 new_references.fee_cal_type , new_references.fee_ci_sequence_number
418 ) THEN
419 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
420 igs_ge_msg_stack.add;
421 app_exception.raise_exception;
422 END IF;
423
424 -- Following check of foreign key has been added as part of Deposits Build, Bug# 2584741
425 IF (((old_references.source_transaction_type = new_references.source_transaction_type)) OR
426 ((new_references.source_transaction_type IS NULL))) THEN
427 NULL;
428 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
429 'IGS_FI_SOURCE_TRANSACTION_REF',
430 new_references.source_transaction_type
431 )THEN
432 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
433 IGS_GE_MSG_STACK.ADD;
434 App_Exception.Raise_Exception;
435 END IF;
436
437 -- Following check of foreign key has been added as part of Lockbox Build, Enh#2831582.
438 IF ((old_references.receipt_lockbox_number = new_references.receipt_lockbox_number) OR
439 (new_references.receipt_lockbox_number IS NULL)
440 ) THEN
441 NULL;
442 ELSIF NOT igs_fi_lockboxes_pkg.get_pk_for_validation(new_references.receipt_lockbox_number)THEN
443 fnd_message.set_name('FND','FORM_RECORD_DELETED');
444 igs_ge_msg_stack.add;
445 app_exception.raise_exception;
446 END IF;
447
448 -- Source Invoice Id as a FK with igs_fi_inv_int_all
449 IF ((old_references.source_invoice_id = new_references.source_invoice_id) OR
450 (new_references.source_invoice_id IS NULL)
451 ) THEN
452 NULL;
453 ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation(new_references.source_invoice_id) THEN
454 fnd_message.set_name('FND','FORM_RECORD_DELETED');
455 igs_ge_msg_stack.add;
456 app_exception.raise_exception;
457 END IF;
458
459 END check_parent_existance;
460
461 PROCEDURE check_child_existance IS
462 /*
463 || Created By : BDEVARAK
464 || Created On : 26-APR-2001
465 || Purpose : Checks for the existance of Child records.
466 || Known limitations, enhancements or remarks :
467 || Change History :
468 || Who When What
469 || (reverse chronological order - newest change first)
470 || pathipat 11-Aug-2003 Enh 3076768 - Auto Release of Holds
471 || Added call to igs_fi_person_holds_pkg.get_fk_igs_fi_credits_all
472 */
473 BEGIN
474
475 igs_fi_applications_pkg.get_fk_igs_fi_credits_all (
476 old_references.credit_id
477 );
478
479 igs_fi_cr_activities_pkg.get_fk_igs_fi_credits_all (
480 old_references.credit_id
481 );
482
483 igs_fi_otc_charges_pkg.get_fk_igs_fi_credits_all (
484 old_references.credit_id
485 );
486
487 igs_fi_person_holds_pkg.get_fk_igs_fi_credits_all (
488 old_references.credit_id
489 );
490
491 END check_child_existance;
492
493
494 FUNCTION get_pk_for_validation (
495 x_credit_id IN NUMBER
496 ) RETURN BOOLEAN AS
497 /*
498 || Created By : BDEVARAK
499 || Created On : 26-APR-2001
500 || Purpose : Validates the Primary Key of the table.
501 || Known limitations, enhancements or remarks :
502 || Change History :
503 || Who When What
504 || pathipat 13-Aug-2003 Enh 3067678 - Auto Release of holds
505 || Removed FOR UPDATE NOWAIT clause in cur_rowid
506 || (reverse chronological order - newest change first)
507 */
508 CURSOR cur_rowid IS
509 SELECT rowid
510 FROM igs_fi_credits_all
511 WHERE credit_id = x_credit_id;
512
513 lv_rowid cur_rowid%RowType;
514
515 BEGIN
516
517 OPEN cur_rowid;
518 FETCH cur_rowid INTO lv_rowid;
519 IF (cur_rowid%FOUND) THEN
520 CLOSE cur_rowid;
521 RETURN(TRUE);
522 ELSE
523 CLOSE cur_rowid;
524 RETURN(FALSE);
525 END IF;
526
527 END get_pk_for_validation;
528
529
530 FUNCTION get_uk_for_validation (
531 x_credit_number IN VARCHAR2,
532 x_party_id IN NUMBER
533 ) RETURN BOOLEAN AS
534 /*
535 || Created By : BDEVARAK
536 || Created On : 26-APR-2001
537 || Purpose : Validates the Unique Keys of the table.
538 || Known limitations, enhancements or remarks :
539 || Change History :
540 || Who When What
541 || (reverse chronological order - newest change first)
542 */
543 CURSOR cur_rowid IS
544 SELECT rowid
545 FROM igs_fi_credits_all
546 WHERE credit_number = x_credit_number
547 AND party_id = x_party_id
548 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
549
550 lv_rowid cur_rowid%RowType;
551
552 BEGIN
553
554 OPEN cur_rowid;
555 FETCH cur_rowid INTO lv_rowid;
556 IF (cur_rowid%FOUND) THEN
557 CLOSE cur_rowid;
558 RETURN (true);
559 ELSE
560 CLOSE cur_rowid;
561 RETURN(FALSE);
562 END IF;
563
564 END get_uk_for_validation ;
565
566
567 PROCEDURE get_fk_igs_fi_cr_types_all (
568 x_credit_type_id IN NUMBER
569 ) AS
570 /*
571 || Created By : BDEVARAK
572 || Created On : 26-APR-2001
573 || Purpose : Validates the Foreign Keys for the table.
574 || Known limitations, enhancements or remarks :
575 || Change History :
576 || Who When What
577 || (reverse chronological order - newest change first)
578 */
579 CURSOR cur_rowid IS
580 SELECT rowid
581 FROM igs_fi_credits_all
582 WHERE ((credit_type_id = x_credit_type_id));
583
584 lv_rowid cur_rowid%RowType;
585
586 BEGIN
587
588 OPEN cur_rowid;
589 FETCH cur_rowid INTO lv_rowid;
590 IF (cur_rowid%FOUND) THEN
591 CLOSE cur_rowid;
592 fnd_message.set_name ('IGS', 'IGS_FI_CRDT_CRTY_FK');
593 igs_ge_msg_stack.add;
594 app_exception.raise_exception;
595 RETURN;
596 END IF;
597 CLOSE cur_rowid;
598
599 END get_fk_igs_fi_cr_types_all;
600
601 --removed procedure get_fk_igs_fi_subaccts_all as part of subaccount removal build.Enh#2564643.
602
603 PROCEDURE get_fk_igs_ca_inst_1 (
604 x_awd_yr_cal_type IN VARCHAR2,
605 x_awd_yr_ci_sequence_number IN NUMBER
606 ) AS
607 /*
608 || Created By : SMVK
609 || Created On : 04-FEB-2002
610 || Purpose : Validates the Foreign Keys for the table.
611 || Known limitations, enhancements or remarks :
612 || Change History :
613 || Who When What
614 || (reverse chronological order - newest change first)
615 */
616 CURSOR cur_rowid IS
617 SELECT rowid
618 FROM igs_fi_credits_all
619 WHERE (( (awd_yr_cal_type = x_awd_yr_cal_type ) AND ( awd_yr_ci_sequence_number = x_awd_yr_ci_sequence_number) ));
620
621 lv_rowid cur_rowid%RowType;
622
623 BEGIN
624
625 OPEN cur_rowid;
626 FETCH cur_rowid INTO lv_rowid;
627 IF (cur_rowid%FOUND) THEN
628 CLOSE cur_rowid;
629 fnd_message.set_name ('IGS', 'IGS_FI_CRD_CI_FK');
630 igs_ge_msg_stack.add;
631 app_exception.raise_exception;
632 RETURN;
633 END IF;
634 CLOSE cur_rowid;
635
636 END get_fk_igs_ca_inst_1;
637
638
639 PROCEDURE get_fk_igs_ca_inst_2 (
640 x_fee_cal_type IN VARCHAR2,
641 x_fee_ci_sequence_number IN NUMBER
642 ) AS
643 /*
644 || Created By : SMVK
645 || Created On : 04-FEB-2002
646 || Purpose : Validates the Foreign Keys for the table.
647 || Known limitations, enhancements or remarks :
648 || Change History :
649 || Who When What
650 || (reverse chronological order - newest change first)
651 */
652 CURSOR cur_rowid IS
653 SELECT rowid
654 FROM igs_fi_credits_all
655 WHERE (( (fee_cal_type = x_fee_cal_type ) AND ( fee_ci_sequence_number = x_fee_ci_sequence_number) ));
656
657 lv_rowid cur_rowid%RowType;
658
659 BEGIN
660
661 OPEN cur_rowid;
662 FETCH cur_rowid INTO lv_rowid;
663 IF (cur_rowid%FOUND) THEN
664 CLOSE cur_rowid;
665 fnd_message.set_name ('IGS', 'IGS_FI_FTCI_CI_FK');
666 igs_ge_msg_stack.add;
667 app_exception.raise_exception;
668 RETURN;
669 END IF;
670 CLOSE cur_rowid;
671
672 END get_fk_igs_ca_inst_2;
673
674 PROCEDURE before_dml (
675 p_action IN VARCHAR2,
676 x_rowid IN VARCHAR2,
677 x_credit_id IN NUMBER ,
678 x_credit_number IN VARCHAR2,
679 x_status IN VARCHAR2,
680 x_credit_source IN VARCHAR2,
681 x_party_id IN NUMBER ,
682 x_credit_type_id IN NUMBER ,
683 x_credit_instrument IN VARCHAR2,
684 x_description IN VARCHAR2,
685 x_amount IN NUMBER ,
686 x_currency_cd IN VARCHAR2,
687 x_exchange_rate IN NUMBER ,
688 x_transaction_date IN DATE ,
689 x_effective_date IN DATE ,
690 x_reversal_date IN DATE ,
691 x_reversal_reason_code IN VARCHAR2,
692 x_reversal_comments IN VARCHAR2,
693 x_unapplied_amount IN NUMBER ,
694 x_source_transaction_id IN NUMBER ,
695 x_receipt_lockbox_number IN VARCHAR2,
696 x_merchant_id IN VARCHAR2,
697 x_credit_card_code IN VARCHAR2,
698 x_credit_card_holder_name IN VARCHAR2,
699 x_credit_card_number IN VARCHAR2,
700 x_credit_card_expiration_date IN DATE ,
701 x_credit_card_approval_code IN VARCHAR2,
702 x_awd_yr_cal_type IN VARCHAR2,
703 x_awd_yr_ci_sequence_number IN NUMBER ,
704 x_fee_cal_type IN VARCHAR2,
705 x_fee_ci_sequence_number IN NUMBER ,
706 x_attribute_category IN VARCHAR2,
707 x_attribute1 IN VARCHAR2,
708 x_attribute2 IN VARCHAR2,
709 x_attribute3 IN VARCHAR2,
710 x_attribute4 IN VARCHAR2,
711 x_attribute5 IN VARCHAR2,
712 x_attribute6 IN VARCHAR2,
713 x_attribute7 IN VARCHAR2,
714 x_attribute8 IN VARCHAR2,
715 x_attribute9 IN VARCHAR2,
716 x_attribute10 IN VARCHAR2,
717 x_attribute11 IN VARCHAR2,
718 x_attribute12 IN VARCHAR2,
719 x_attribute13 IN VARCHAR2,
720 x_attribute14 IN VARCHAR2,
721 x_attribute15 IN VARCHAR2,
722 x_attribute16 IN VARCHAR2,
723 x_attribute17 IN VARCHAR2,
724 x_attribute18 IN VARCHAR2,
725 x_attribute19 IN VARCHAR2,
726 x_attribute20 IN VARCHAR2,
727 x_creation_date IN DATE ,
728 x_created_by IN NUMBER ,
729 x_last_update_date IN DATE ,
730 x_last_updated_by IN NUMBER ,
731 x_last_update_login IN NUMBER ,
732 x_gl_date IN DATE ,
733 x_check_number IN VARCHAR2,
734 x_source_transaction_type IN VARCHAR2,
735 x_source_transaction_ref IN VARCHAR2,
736 x_credit_card_status_code IN VARCHAR2,
737 x_credit_card_payee_cd IN VARCHAR2,
738 x_credit_card_tangible_cd IN VARCHAR2,
739 x_lockbox_interface_id IN NUMBER ,
740 x_batch_name IN VARCHAR2,
741 x_deposit_date IN DATE,
742 x_source_invoice_id IN NUMBER,
743 x_tax_year_code IN VARCHAR2,
744 x_waiver_name IN VARCHAR2
745 ) AS
746 /*
747 || Created By : BDEVARAK
748 || Created On : 26-APR-2001
749 || Purpose : Initialises the columns, Checks Constraints, Calls the
750 || Trigger Handlers for the table, before any DML operation.
751 || Known limitations, enhancements or remarks :
752 || Change History :
753 || Who When What
754 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
755 || Added the Waiver name column
756 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
757 || Added new column source_invoice_id
758 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
759 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
760 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
761 || source_transaction_type and source_transaction_ref. Removed calls to
762 || check_constraints.
763 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE. Also added call to
764 || check constraints procedure
765 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id. Also removed DEFAULT
766 || clause from procedure parameter list to avoid gscc warnings.
767 || vvutukur 14-May-2002 Called newly created private procedure BeforeRowInsertUpdate
768 || which validates reversal date with transaction date.
769 || SMVK 04-Feb-2002 Updated existing procedure for
770 || Four parameters awd_yr_cal_type
771 || awd_yr_ci_sequence_number
772 || fee_cal_type, fee_ci_sequence_number
773 || Enhancement Bug No.2191470
774 || smadathi 05-oct-2001 Balance Flag reference removed .
775 || Enhancement Bug No. 2030448
776 || (reverse chronological order - newest change first)
777 */
778 BEGIN
779
780 set_column_values (
781 p_action,
782 x_rowid,
783 x_credit_id,
784 x_credit_number,
785 x_status,
786 x_credit_source,
787 x_party_id,
788 x_credit_type_id,
789 x_credit_instrument,
790 x_description,
791 x_amount,
792 x_currency_cd,
793 x_exchange_rate,
794 x_transaction_date,
795 x_effective_date,
796 x_reversal_date,
797 x_reversal_reason_code,
798 x_reversal_comments,
799 x_unapplied_amount,
800 x_source_transaction_id,
801 x_receipt_lockbox_number,
802 x_merchant_id,
803 x_credit_card_code,
804 x_credit_card_holder_name,
805 x_credit_card_number,
806 x_credit_card_expiration_date,
807 x_credit_card_approval_code,
808 x_awd_yr_cal_type,
809 x_awd_yr_ci_sequence_number,
810 x_fee_cal_type,
811 x_fee_ci_sequence_number,
812 x_attribute_category,
813 x_attribute1,
814 x_attribute2,
815 x_attribute3,
816 x_attribute4,
817 x_attribute5,
818 x_attribute6,
819 x_attribute7,
820 x_attribute8,
821 x_attribute9,
822 x_attribute10,
823 x_attribute11,
824 x_attribute12,
825 x_attribute13,
826 x_attribute14,
827 x_attribute15,
828 x_attribute16,
829 x_attribute17,
830 x_attribute18,
831 x_attribute19,
832 x_attribute20,
833 x_creation_date,
834 x_created_by,
835 x_last_update_date,
836 x_last_updated_by,
837 x_last_update_login ,
838 x_gl_date,
839 x_check_number,
840 x_source_transaction_type,
841 x_source_transaction_ref,
842 x_credit_card_status_code,
843 x_credit_card_payee_cd,
844 x_credit_card_tangible_cd,
845 x_lockbox_interface_id,
846 x_batch_name,
847 x_deposit_date,
848 x_source_invoice_id,
849 x_tax_year_code,
850 x_waiver_name
851 );
852
853 IF (p_action = 'INSERT') THEN
854 -- Call all the procedures related to Before Insert.
855 IF ( get_pk_for_validation(
856 new_references.credit_id
857 )
858 ) THEN
859 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
860 igs_ge_msg_stack.add;
861 app_exception.raise_exception;
862 END IF;
863 BeforeRowInsertUpdate;
864 check_uniqueness;
865 check_parent_existance;
866 ELSIF (p_action = 'UPDATE') THEN
867 -- Call all the procedures related to Before Update.
868 BeforeRowInsertUpdate;
869 check_uniqueness;
870 check_parent_existance;
871 ELSIF (p_action = 'DELETE') THEN
872 -- Call all the procedures related to Before Delete.
873 check_child_existance;
874 ELSIF (p_action = 'VALIDATE_INSERT') THEN
875 -- Call all the procedures related to Before Insert.
876 IF ( get_pk_for_validation (
877 new_references.credit_id
878 )
879 ) THEN
880 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
881 igs_ge_msg_stack.add;
882 app_exception.raise_exception;
883 END IF;
884 BeforeRowInsertUpdate;
885 check_uniqueness;
886 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
887 BeforeRowInsertUpdate;
888 check_uniqueness;
889 ELSIF (p_action = 'VALIDATE_DELETE') THEN
890 check_child_existance;
891 END IF;
892
893 END before_dml;
894
895 PROCEDURE insert_row (
896 x_rowid IN OUT NOCOPY VARCHAR2,
897 x_credit_id IN OUT NOCOPY NUMBER,
898 x_credit_number IN OUT NOCOPY VARCHAR2,
899 x_status IN VARCHAR2,
900 x_credit_source IN VARCHAR2,
901 x_party_id IN NUMBER,
902 x_credit_type_id IN NUMBER,
903 x_credit_instrument IN VARCHAR2,
904 x_description IN VARCHAR2,
905 x_amount IN NUMBER,
906 x_currency_cd IN VARCHAR2,
907 x_exchange_rate IN NUMBER,
908 x_transaction_date IN DATE,
909 x_effective_date IN DATE,
910 x_reversal_date IN DATE,
911 x_reversal_reason_code IN VARCHAR2,
912 x_reversal_comments IN VARCHAR2,
913 x_unapplied_amount IN NUMBER,
914 x_source_transaction_id IN NUMBER,
915 x_receipt_lockbox_number IN VARCHAR2,
916 x_merchant_id IN VARCHAR2,
917 x_credit_card_code IN VARCHAR2,
918 x_credit_card_holder_name IN VARCHAR2,
919 x_credit_card_number IN VARCHAR2,
920 x_credit_card_expiration_date IN DATE,
921 x_credit_card_approval_code IN VARCHAR2,
922 x_awd_yr_cal_type IN VARCHAR2,
923 x_awd_yr_ci_sequence_number IN NUMBER,
924 x_fee_cal_type IN VARCHAR2,
925 x_fee_ci_sequence_number IN NUMBER,
926 x_attribute_category IN VARCHAR2,
927 x_attribute1 IN VARCHAR2,
928 x_attribute2 IN VARCHAR2,
929 x_attribute3 IN VARCHAR2,
930 x_attribute4 IN VARCHAR2,
931 x_attribute5 IN VARCHAR2,
932 x_attribute6 IN VARCHAR2,
933 x_attribute7 IN VARCHAR2,
934 x_attribute8 IN VARCHAR2,
935 x_attribute9 IN VARCHAR2,
936 x_attribute10 IN VARCHAR2,
937 x_attribute11 IN VARCHAR2,
938 x_attribute12 IN VARCHAR2,
939 x_attribute13 IN VARCHAR2,
940 x_attribute14 IN VARCHAR2,
941 x_attribute15 IN VARCHAR2,
942 x_attribute16 IN VARCHAR2,
943 x_attribute17 IN VARCHAR2,
944 x_attribute18 IN VARCHAR2,
945 x_attribute19 IN VARCHAR2,
946 x_attribute20 IN VARCHAR2,
947 x_mode IN VARCHAR2,
948 x_gl_date IN DATE ,
949 x_check_number IN VARCHAR2,
950 x_source_transaction_type IN VARCHAR2,
951 x_source_transaction_ref IN VARCHAR2,
952 x_credit_card_status_code IN VARCHAR2,
953 x_credit_card_payee_cd IN VARCHAR2,
954 x_credit_card_tangible_cd IN VARCHAR2,
955 x_lockbox_interface_id IN NUMBER ,
956 x_batch_name IN VARCHAR2,
957 x_deposit_date IN DATE,
958 x_source_invoice_id IN NUMBER,
959 x_tax_year_code IN VARCHAR2,
960 x_waiver_name IN VARCHAR2
961 ) AS
962 /*
963 || Created By : BDEVARAK
964 || Created On : 26-APR-2001
965 || Purpose : Handles the INSERT DML logic for the table.
966 || Known limitations, enhancements or remarks :
967 || Change History :
968 || Who When What
969 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
970 || Added the Waiver name column
971 || svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
972 || Changed x_credit_number parameter as IN OUT in Insert row
973 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
974 || Added new column source_invoice_id
975 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
976 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
977 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
978 || source_transaction_type and source_transaction_ref
979 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE
980 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id column as this has been
981 || obsoleted.Also removed DEFAULT clause from procedure parameter list
982 || to avoid gscc warnings.
983 || SMVK 04-Feb-2002 Updated existing procedure for
984 || Four parameters awd_yr_cal_type
985 || awd_yr_ci_sequence_number
986 || fee_cal_type, fee_ci_sequence_number
987 || Enhancement Bug No.2191470
988 || smadathi 05-oct-2001 Balance Flag reference removed .
989 || Enhancement Bug No. 2030448
990 || (reverse chronological order - newest change first)
991 */
992 CURSOR c IS
993 SELECT rowid
994 FROM igs_fi_credits_all
995 WHERE credit_id = x_credit_id;
996
997 x_last_update_date DATE;
998 x_last_updated_by NUMBER;
999 x_last_update_login NUMBER;
1000 x_request_id NUMBER;
1001 x_program_id NUMBER;
1002 x_program_application_id NUMBER;
1003 x_program_update_date DATE;
1004
1005 BEGIN
1006
1007 x_last_update_date := SYSDATE;
1008 IF (x_mode = 'I') THEN
1009 x_last_updated_by := 1;
1010 x_last_update_login := 0;
1011 ELSIF (x_mode = 'R') THEN
1012 x_last_updated_by := fnd_global.user_id;
1013 IF (x_last_updated_by IS NULL) THEN
1014 x_last_updated_by := -1;
1015 END IF;
1016 x_last_update_login := fnd_global.login_id;
1017 IF (x_last_update_login IS NULL) THEN
1018 x_last_update_login := -1;
1019 END IF;
1020 x_request_id := fnd_global.conc_request_id;
1021 x_program_id := fnd_global.conc_program_id;
1022 x_program_application_id := fnd_global.prog_appl_id;
1023
1024 IF (x_request_id = -1) THEN
1025 x_request_id := NULL;
1026 x_program_id := NULL;
1027 x_program_application_id := NULL;
1028 x_program_update_date := NULL;
1029 ELSE
1030 x_program_update_date := SYSDATE;
1031 END IF;
1032 ELSE
1033 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1034 igs_ge_msg_stack.add;
1035 app_exception.raise_exception;
1036 END IF;
1037
1038 SELECT igs_fi_credits_s.NEXTVAL
1039 INTO x_credit_id
1040 FROM dual;
1041
1042 SELECT igs_fi_credit_num_s.NEXTVAL
1043 INTO x_credit_number
1044 FROM dual;
1045
1046 new_references.org_id := igs_ge_gen_003.get_org_id;
1047
1048 before_dml(
1049 p_action => 'INSERT',
1050 x_rowid => x_rowid,
1051 x_credit_id => x_credit_id,
1052 x_credit_number => x_credit_number,
1053 x_status => x_status,
1054 x_credit_source => x_credit_source,
1055 x_party_id => x_party_id,
1056 x_credit_type_id => x_credit_type_id,
1057 x_credit_instrument => x_credit_instrument,
1058 x_description => x_description,
1059 x_amount => x_amount,
1060 x_currency_cd => x_currency_cd,
1061 x_exchange_rate => x_exchange_rate,
1062 x_transaction_date => x_transaction_date,
1063 x_effective_date => x_effective_date,
1064 x_reversal_date => x_reversal_date,
1065 x_reversal_reason_code => x_reversal_reason_code,
1066 x_reversal_comments => x_reversal_comments,
1067 x_unapplied_amount => x_unapplied_amount,
1068 x_source_transaction_id => x_source_transaction_id,
1069 x_receipt_lockbox_number => x_receipt_lockbox_number,
1070 x_merchant_id => x_merchant_id,
1071 x_credit_card_code => x_credit_card_code,
1072 x_credit_card_holder_name => x_credit_card_holder_name,
1073 x_credit_card_number => x_credit_card_number,
1074 x_credit_card_expiration_date => x_credit_card_expiration_date,
1075 x_credit_card_approval_code => x_credit_card_approval_code,
1076 x_awd_yr_cal_type => x_awd_yr_cal_type,
1077 x_awd_yr_ci_sequence_number => x_awd_yr_ci_sequence_number,
1078 x_fee_cal_type => x_fee_cal_type,
1079 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1080 x_attribute_category => x_attribute_category,
1081 x_attribute1 => x_attribute1,
1082 x_attribute2 => x_attribute2,
1083 x_attribute3 => x_attribute3,
1084 x_attribute4 => x_attribute4,
1085 x_attribute5 => x_attribute5,
1086 x_attribute6 => x_attribute6,
1087 x_attribute7 => x_attribute7,
1088 x_attribute8 => x_attribute8,
1089 x_attribute9 => x_attribute9,
1090 x_attribute10 => x_attribute10,
1091 x_attribute11 => x_attribute11,
1092 x_attribute12 => x_attribute12,
1093 x_attribute13 => x_attribute13,
1094 x_attribute14 => x_attribute14,
1095 x_attribute15 => x_attribute15,
1096 x_attribute16 => x_attribute16,
1097 x_attribute17 => x_attribute17,
1098 x_attribute18 => x_attribute18,
1099 x_attribute19 => x_attribute19,
1100 x_attribute20 => x_attribute20,
1101 x_creation_date => x_last_update_date,
1102 x_created_by => x_last_updated_by,
1103 x_last_update_date => x_last_update_date,
1104 x_last_updated_by => x_last_updated_by,
1105 x_last_update_login => x_last_update_login,
1106 x_gl_date => x_gl_date,
1107 x_check_number => x_check_number,
1108 x_source_transaction_type => x_source_transaction_type,
1109 x_source_transaction_ref => x_source_transaction_ref,
1110 x_credit_card_status_code => x_credit_card_status_code,
1111 x_credit_card_payee_cd => x_credit_card_payee_cd,
1112 x_credit_card_tangible_cd => x_credit_card_tangible_cd,
1113 x_lockbox_interface_id => x_lockbox_interface_id,
1114 x_batch_name => x_batch_name,
1115 x_deposit_date => x_deposit_date,
1116 x_source_invoice_id => x_source_invoice_id,
1117 x_tax_year_code => x_tax_year_code,
1118 x_waiver_name => x_waiver_name
1119 );
1120
1121 INSERT INTO igs_fi_credits_all (
1122 credit_id,
1123 credit_number,
1124 status,
1125 credit_source,
1126 party_id,
1127 credit_type_id,
1128 credit_instrument,
1129 description,
1130 amount,
1131 currency_cd,
1132 exchange_rate,
1133 transaction_date,
1134 effective_date,
1135 reversal_date,
1136 reversal_reason_code,
1137 reversal_comments,
1138 org_id,
1139 unapplied_amount,
1140 source_transaction_id,
1141 receipt_lockbox_number,
1142 merchant_id,
1143 credit_card_code,
1144 credit_card_holder_name,
1145 credit_card_number,
1146 credit_card_expiration_date,
1147 credit_card_approval_code,
1148 awd_yr_cal_type,
1149 awd_yr_ci_sequence_number,
1150 fee_cal_type,
1151 fee_ci_sequence_number,
1152 attribute_category,
1153 attribute1,
1154 attribute2,
1155 attribute3,
1156 attribute4,
1157 attribute5,
1158 attribute6,
1159 attribute7,
1160 attribute8,
1161 attribute9,
1162 attribute10,
1163 attribute11,
1164 attribute12,
1165 attribute13,
1166 attribute14,
1167 attribute15,
1168 attribute16,
1169 attribute17,
1170 attribute18,
1171 attribute19,
1172 attribute20,
1173 creation_date,
1174 created_by,
1175 last_update_date,
1176 last_updated_by,
1177 last_update_login,
1178 request_id,
1179 program_id,
1180 program_application_id,
1181 program_update_date ,
1182 gl_date,
1183 check_number,
1184 source_transaction_type,
1185 source_transaction_ref,
1186 credit_card_status_code,
1187 credit_card_payee_cd,
1188 credit_card_tangible_cd,
1189 lockbox_interface_id,
1190 batch_name,
1191 deposit_date,
1192 source_invoice_id,
1193 tax_year_code,
1194 waiver_name
1195 ) VALUES (
1196 new_references.credit_id,
1197 new_references.credit_number,
1198 new_references.status,
1199 new_references.credit_source,
1200 new_references.party_id,
1201 new_references.credit_type_id,
1202 new_references.credit_instrument,
1203 new_references.description,
1204 new_references.amount,
1205 new_references.currency_cd,
1206 new_references.exchange_rate,
1207 new_references.transaction_date,
1208 new_references.effective_date,
1209 new_references.reversal_date,
1210 new_references.reversal_reason_code,
1211 new_references.reversal_comments,
1212 new_references.org_id,
1213 new_references.unapplied_amount,
1214 new_references.source_transaction_id,
1215 new_references.receipt_lockbox_number,
1216 new_references.merchant_id,
1217 new_references.credit_card_code,
1218 new_references.credit_card_holder_name,
1219 new_references.credit_card_number,
1220 new_references.credit_card_expiration_date,
1221 new_references.credit_card_approval_code,
1222 new_references.awd_yr_cal_type,
1223 new_references.awd_yr_ci_sequence_number,
1224 new_references.fee_cal_type,
1225 new_references.fee_ci_sequence_number,
1226 new_references.attribute_category,
1227 new_references.attribute1,
1228 new_references.attribute2,
1229 new_references.attribute3,
1230 new_references.attribute4,
1231 new_references.attribute5,
1232 new_references.attribute6,
1233 new_references.attribute7,
1234 new_references.attribute8,
1235 new_references.attribute9,
1236 new_references.attribute10,
1237 new_references.attribute11,
1238 new_references.attribute12,
1239 new_references.attribute13,
1240 new_references.attribute14,
1241 new_references.attribute15,
1242 new_references.attribute16,
1243 new_references.attribute17,
1244 new_references.attribute18,
1245 new_references.attribute19,
1246 new_references.attribute20,
1247 x_last_update_date,
1248 x_last_updated_by,
1249 x_last_update_date,
1250 x_last_updated_by,
1251 x_last_update_login ,
1252 x_request_id,
1253 x_program_id,
1254 x_program_application_id,
1255 x_program_update_date ,
1256 new_references.gl_date,
1257 new_references.check_number,
1258 new_references.source_transaction_type,
1259 new_references.source_transaction_ref,
1260 new_references.credit_card_status_code,
1261 new_references.credit_card_payee_cd,
1262 new_references.credit_card_tangible_cd,
1263 new_references.lockbox_interface_id,
1264 new_references.batch_name,
1265 new_references.deposit_date,
1266 new_references.source_invoice_id,
1267 new_references.tax_year_code,
1268 new_references.waiver_name
1269 );
1270 OPEN c;
1271 FETCH c INTO x_rowid;
1272 IF (c%NOTFOUND) THEN
1273 CLOSE c;
1274 RAISE NO_DATA_FOUND;
1275 END IF;
1276 CLOSE c;
1277
1278 END insert_row;
1279
1280
1281 PROCEDURE lock_row (
1282 x_rowid IN VARCHAR2,
1283 x_credit_id IN NUMBER,
1284 x_credit_number IN VARCHAR2,
1285 x_status IN VARCHAR2,
1286 x_credit_source IN VARCHAR2,
1287 x_party_id IN NUMBER,
1288 x_credit_type_id IN NUMBER,
1289 x_credit_instrument IN VARCHAR2,
1290 x_description IN VARCHAR2,
1291 x_amount IN NUMBER,
1292 x_currency_cd IN VARCHAR2,
1293 x_exchange_rate IN NUMBER,
1294 x_transaction_date IN DATE,
1295 x_effective_date IN DATE,
1296 x_reversal_date IN DATE,
1297 x_reversal_reason_code IN VARCHAR2,
1298 x_reversal_comments IN VARCHAR2,
1299 x_unapplied_amount IN NUMBER,
1300 x_source_transaction_id IN NUMBER,
1301 x_receipt_lockbox_number IN VARCHAR2,
1302 x_merchant_id IN VARCHAR2,
1303 x_credit_card_code IN VARCHAR2,
1304 x_credit_card_holder_name IN VARCHAR2,
1305 x_credit_card_number IN VARCHAR2,
1306 x_credit_card_expiration_date IN DATE,
1307 x_credit_card_approval_code IN VARCHAR2,
1308 x_awd_yr_cal_type IN VARCHAR2,
1309 x_awd_yr_ci_sequence_number IN NUMBER,
1310 x_fee_cal_type IN VARCHAR2,
1311 x_fee_ci_sequence_number IN NUMBER,
1312 x_attribute_category IN VARCHAR2,
1313 x_attribute1 IN VARCHAR2,
1314 x_attribute2 IN VARCHAR2,
1315 x_attribute3 IN VARCHAR2,
1316 x_attribute4 IN VARCHAR2,
1317 x_attribute5 IN VARCHAR2,
1318 x_attribute6 IN VARCHAR2,
1319 x_attribute7 IN VARCHAR2,
1320 x_attribute8 IN VARCHAR2,
1321 x_attribute9 IN VARCHAR2,
1322 x_attribute10 IN VARCHAR2,
1323 x_attribute11 IN VARCHAR2,
1324 x_attribute12 IN VARCHAR2,
1325 x_attribute13 IN VARCHAR2,
1326 x_attribute14 IN VARCHAR2,
1327 x_attribute15 IN VARCHAR2,
1328 x_attribute16 IN VARCHAR2,
1329 x_attribute17 IN VARCHAR2,
1330 x_attribute18 IN VARCHAR2,
1331 x_attribute19 IN VARCHAR2,
1332 x_attribute20 IN VARCHAR2,
1333 x_gl_date IN DATE ,
1334 x_check_number IN VARCHAR2,
1335 x_source_transaction_type IN VARCHAR2,
1336 x_source_transaction_ref IN VARCHAR2,
1337 x_credit_card_status_code IN VARCHAR2,
1338 x_credit_card_payee_cd IN VARCHAR2,
1339 x_credit_card_tangible_cd IN VARCHAR2,
1340 x_lockbox_interface_id IN NUMBER,
1341 x_batch_name IN VARCHAR2,
1342 x_deposit_date IN DATE,
1343 x_source_invoice_id IN NUMBER,
1344 x_tax_year_code IN VARCHAR2,
1345 x_waiver_name IN VARCHAR2
1346 ) AS
1347 /*
1348 || Created By : BDEVARAK
1349 || Created On : 26-APR-2001
1350 || Purpose : Handles the LOCK mechanism for the table.
1351 || Known limitations, enhancements or remarks :
1352 || Change History :
1353 || Who When What
1354 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
1355 || Added the Waiver name column and added the column related changes
1356 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
1357 || Added new column source_invoice_id
1358 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
1359 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
1360 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
1361 || source_transaction_type and source_transaction_ref
1362 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE
1363 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id column as this has been
1364 || obsoleted.Also removed DEFAULT clause from procedure parameter list
1365 || to avoid gscc warnings.
1366 || SMVK 04-Feb-2002 Updated existing procedure for
1367 || Four parameters awd_yr_cal_type
1368 || awd_yr_ci_sequence_number
1369 || fee_cal_type, fee_ci_sequence_number
1370 || Enhancement Bug No.2191470
1371 || smadathi 05-oct-2001 Balance Flag reference removed .
1372 || Enhancement Bug No. 2030448
1373 || (reverse chronological order - newest change first)
1374 */
1375 CURSOR c1 IS
1376 SELECT
1377 credit_number,
1378 status,
1379 credit_source,
1380 party_id,
1381 credit_type_id,
1382 credit_instrument,
1383 description,
1384 amount,
1385 currency_cd,
1386 exchange_rate,
1387 transaction_date,
1388 effective_date,
1389 reversal_date,
1390 reversal_reason_code,
1391 reversal_comments,
1392 unapplied_amount,
1393 source_transaction_id,
1394 receipt_lockbox_number,
1395 merchant_id,
1396 credit_card_code,
1397 credit_card_holder_name,
1398 credit_card_number,
1399 credit_card_expiration_date,
1400 credit_card_approval_code,
1401 awd_yr_cal_type,
1402 awd_yr_ci_sequence_number,
1403 fee_cal_type,
1404 fee_ci_sequence_number,
1405 attribute_category,
1406 attribute1,
1407 attribute2,
1408 attribute3,
1409 attribute4,
1410 attribute5,
1411 attribute6,
1412 attribute7,
1413 attribute8,
1414 attribute9,
1415 attribute10,
1416 attribute11,
1417 attribute12,
1418 attribute13,
1419 attribute14,
1420 attribute15,
1421 attribute16,
1422 attribute17,
1423 attribute18,
1424 attribute19,
1425 attribute20,
1426 gl_date,
1427 check_number,
1428 source_transaction_type,
1429 source_transaction_ref,
1430 credit_card_status_code,
1431 credit_card_payee_cd,
1432 credit_card_tangible_cd,
1433 lockbox_interface_id,
1434 batch_name,
1435 deposit_date,
1436 source_invoice_id,
1437 tax_year_code,
1438 waiver_name
1439 FROM igs_fi_credits_all
1440 WHERE rowid = x_rowid
1441 FOR UPDATE NOWAIT;
1442
1443 tlinfo c1%ROWTYPE;
1444
1445 BEGIN
1446
1447 OPEN c1;
1448 FETCH c1 INTO tlinfo;
1449 IF (c1%notfound) THEN
1450 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1451 igs_ge_msg_stack.add;
1452 CLOSE c1;
1453 app_exception.raise_exception;
1454 RETURN;
1455 END IF;
1456 CLOSE c1;
1457
1458 IF (
1459 (tlinfo.credit_number = x_credit_number)
1460 AND (tlinfo.status = x_status)
1461 AND ((tlinfo.credit_source = x_credit_source) OR ((tlinfo.credit_source IS NULL) AND (X_credit_source IS NULL)))
1462 AND (tlinfo.party_id = x_party_id)
1463 AND (tlinfo.credit_type_id = x_credit_type_id)
1464 AND (tlinfo.credit_instrument = x_credit_instrument)
1465 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
1466 AND (tlinfo.amount = x_amount)
1467 AND (tlinfo.currency_cd = x_currency_cd)
1468 AND ((tlinfo.exchange_rate = x_exchange_rate) OR ((tlinfo.exchange_rate IS NULL) AND (X_exchange_rate IS NULL)))
1469 AND (tlinfo.transaction_date = x_transaction_date)
1470 AND (tlinfo.effective_date = x_effective_date)
1471 AND ((tlinfo.reversal_date = x_reversal_date) OR ((tlinfo.reversal_date IS NULL) AND (X_reversal_date IS NULL)))
1472 AND ((tlinfo.reversal_reason_code = x_reversal_reason_code) OR ((tlinfo.reversal_reason_code IS NULL) AND (X_reversal_reason_code IS NULL)))
1473 AND ((tlinfo.reversal_comments = x_reversal_comments) OR ((tlinfo.reversal_comments IS NULL) AND (X_reversal_comments IS NULL)))
1474 AND ((tlinfo.unapplied_amount = x_unapplied_amount) OR ((tlinfo.unapplied_amount IS NULL) AND (X_unapplied_amount IS NULL)))
1475 AND ((tlinfo.source_transaction_id = x_source_transaction_id) OR ((tlinfo.source_transaction_id IS NULL) AND (X_source_transaction_id IS NULL)))
1476 AND ((tlinfo.receipt_lockbox_number = x_receipt_lockbox_number) OR ((tlinfo.receipt_lockbox_number IS NULL) AND (X_receipt_lockbox_number IS NULL)))
1477 AND ((tlinfo.merchant_id = x_merchant_id) OR ((tlinfo.merchant_id IS NULL) AND (X_merchant_id IS NULL)))
1478 AND ((tlinfo.credit_card_code = x_credit_card_code) OR ((tlinfo.credit_card_code IS NULL) AND (X_credit_card_code IS NULL)))
1479 AND ((tlinfo.credit_card_holder_name = x_credit_card_holder_name) OR ((tlinfo.credit_card_holder_name IS NULL) AND (X_credit_card_holder_name IS NULL)))
1480 AND ((tlinfo.credit_card_number = x_credit_card_number) OR ((tlinfo.credit_card_number IS NULL) AND (X_credit_card_number IS NULL)))
1481 AND ((tlinfo.credit_card_expiration_date = x_credit_card_expiration_date) OR ((tlinfo.credit_card_expiration_date IS NULL) AND (X_credit_card_expiration_date IS NULL)))
1482 AND ((tlinfo.credit_card_approval_code = x_credit_card_approval_code) OR ((tlinfo.credit_card_approval_code IS NULL) AND (X_credit_card_approval_code IS NULL)))
1483 AND ((tlinfo.awd_yr_cal_type = x_awd_yr_cal_type) OR ((tlinfo.awd_yr_cal_type IS NULL) AND (X_awd_yr_cal_type IS NULL)))
1484 AND ((tlinfo.awd_yr_ci_sequence_number = x_awd_yr_ci_sequence_number) OR ((tlinfo.awd_yr_ci_sequence_number IS NULL) AND (X_awd_yr_ci_sequence_number IS NULL)))
1485 AND ((tlinfo.fee_cal_type = x_fee_cal_type) OR ((tlinfo.fee_cal_type IS NULL) AND (X_fee_cal_type IS NULL)))
1486 AND ((tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number) OR ((tlinfo.fee_ci_sequence_number IS NULL) AND (X_fee_ci_sequence_number IS NULL)))
1487 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1488 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1489 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1490 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1491 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1492 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1493 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1494 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1495 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1496 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1497 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1498 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1499 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1500 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1501 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1502 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1503 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1504 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1505 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1506 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1507 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1508 AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (X_gl_date IS NULL)))
1509 AND ((tlinfo.check_number = x_check_number) OR ((tlinfo.check_number IS NULL) AND (x_check_number IS NULL)))
1510 AND ((tlinfo.source_transaction_type = x_source_transaction_type) OR ((tlinfo.source_transaction_type IS NULL) AND (x_source_transaction_type IS NULL)))
1511 AND ((tlinfo.source_transaction_ref = x_source_transaction_ref) OR ((tlinfo.source_transaction_ref IS NULL) AND (x_source_transaction_ref IS NULL)))
1512 AND ((tlinfo.credit_card_status_code = x_credit_card_status_code) OR ((tlinfo.credit_card_status_code IS NULL) AND (x_credit_card_status_code IS NULL)))
1513 AND ((tlinfo.credit_card_payee_cd = x_credit_card_payee_cd) OR ((tlinfo.credit_card_payee_cd IS NULL) AND (x_credit_card_payee_cd IS NULL)))
1514 AND ((tlinfo.credit_card_tangible_cd = x_credit_card_tangible_cd) OR ((tlinfo.credit_card_tangible_cd IS NULL) AND (x_credit_card_tangible_cd IS NULL)))
1515 AND ((tlinfo.lockbox_interface_id = x_lockbox_interface_id) OR ((tlinfo.lockbox_interface_id IS NULL) AND (x_lockbox_interface_id IS NULL)))
1516 AND ((tlinfo.batch_name = x_batch_name) OR ((tlinfo.batch_name IS NULL) AND (x_batch_name IS NULL)))
1517 AND ((TRUNC(tlinfo.deposit_date) = TRUNC(x_deposit_date)) OR ((tlinfo.deposit_date IS NULL) AND (x_deposit_date IS NULL)))
1518 AND ((tlinfo.source_invoice_id = x_source_invoice_id) OR ((tlinfo.source_invoice_id IS NULL) AND (x_source_invoice_id IS NULL)))
1519 AND ((TRUNC(tlinfo.tax_year_code) = TRUNC(x_tax_year_code)) OR ((tlinfo.tax_year_code IS NULL) AND (x_tax_year_code IS NULL)))
1520 AND ((tlinfo.waiver_name = x_waiver_name) OR ((tlinfo.waiver_name IS NULL) AND (x_waiver_name IS NULL)))
1521 ) THEN
1522 NULL;
1523 ELSE
1524 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1525 igs_ge_msg_stack.add;
1526 app_exception.raise_exception;
1527 END IF;
1528
1529 RETURN;
1530
1531 END lock_row;
1532
1533
1534 PROCEDURE update_row (
1535 x_rowid IN VARCHAR2,
1536 x_credit_id IN NUMBER,
1537 x_credit_number IN VARCHAR2,
1538 x_status IN VARCHAR2,
1539 x_credit_source IN VARCHAR2,
1540 x_party_id IN NUMBER,
1541 x_credit_type_id IN NUMBER,
1542 x_credit_instrument IN VARCHAR2,
1543 x_description IN VARCHAR2,
1544 x_amount IN NUMBER,
1545 x_currency_cd IN VARCHAR2,
1546 x_exchange_rate IN NUMBER,
1547 x_transaction_date IN DATE,
1548 x_effective_date IN DATE,
1549 x_reversal_date IN DATE,
1550 x_reversal_reason_code IN VARCHAR2,
1551 x_reversal_comments IN VARCHAR2,
1552 x_unapplied_amount IN NUMBER,
1553 x_source_transaction_id IN NUMBER,
1554 x_receipt_lockbox_number IN VARCHAR2,
1555 x_merchant_id IN VARCHAR2,
1556 x_credit_card_code IN VARCHAR2,
1557 x_credit_card_holder_name IN VARCHAR2,
1558 x_credit_card_number IN VARCHAR2,
1559 x_credit_card_expiration_date IN DATE,
1560 x_credit_card_approval_code IN VARCHAR2,
1561 x_awd_yr_cal_type IN VARCHAR2,
1562 x_awd_yr_ci_sequence_number IN NUMBER,
1563 x_fee_cal_type IN VARCHAR2,
1564 x_fee_ci_sequence_number IN NUMBER,
1565 x_attribute_category IN VARCHAR2,
1566 x_attribute1 IN VARCHAR2,
1567 x_attribute2 IN VARCHAR2,
1568 x_attribute3 IN VARCHAR2,
1569 x_attribute4 IN VARCHAR2,
1570 x_attribute5 IN VARCHAR2,
1571 x_attribute6 IN VARCHAR2,
1572 x_attribute7 IN VARCHAR2,
1573 x_attribute8 IN VARCHAR2,
1574 x_attribute9 IN VARCHAR2,
1575 x_attribute10 IN VARCHAR2,
1576 x_attribute11 IN VARCHAR2,
1577 x_attribute12 IN VARCHAR2,
1578 x_attribute13 IN VARCHAR2,
1579 x_attribute14 IN VARCHAR2,
1580 x_attribute15 IN VARCHAR2,
1581 x_attribute16 IN VARCHAR2,
1582 x_attribute17 IN VARCHAR2,
1583 x_attribute18 IN VARCHAR2,
1584 x_attribute19 IN VARCHAR2,
1585 x_attribute20 IN VARCHAR2,
1586 x_mode IN VARCHAR2,
1587 x_gl_date IN DATE ,
1588 x_check_number IN VARCHAR2,
1589 x_source_transaction_type IN VARCHAR2,
1590 x_source_transaction_ref IN VARCHAR2,
1591 x_credit_card_status_code IN VARCHAR2,
1592 x_credit_card_payee_cd IN VARCHAR2,
1593 x_credit_card_tangible_cd IN VARCHAR2,
1594 x_lockbox_interface_id IN NUMBER ,
1595 x_batch_name IN VARCHAR2,
1596 x_deposit_date IN DATE,
1597 x_source_invoice_id IN NUMBER,
1598 x_tax_year_code IN VARCHAR2,
1599 x_waiver_name IN VARCHAR2
1600 ) AS
1601 /*
1602 || Created By : BDEVARAK
1603 || Created On : 26-APR-2001
1604 || Purpose : Handles the UPDATE DML logic for the table.
1605 || Known limitations, enhancements or remarks :
1606 || Change History :
1607 || Who When What
1608 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
1609 || Added the Waiver name column
1610 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
1611 || Added new column source_invoice_id
1612 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
1613 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
1614 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
1615 || source_transaction_type and source_transaction_ref
1616 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE
1617 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id column as this has been
1618 || obsoleted.Also removed DEFAULT clause from procedure parameter list
1619 || to avoid gscc warnings.
1620 || SMVK 04-Feb-2002 Updated existing procedure for
1621 || Four parameters awd_yr_cal_type
1622 || awd_yr_ci_sequence_number
1623 || fee_cal_type, fee_ci_sequence_number
1624 || Enhancement Bug No.2191470
1625 || smadathi 05-oct-2001 Balance Flag reference removed .
1626 || Enhancement Bug No. 2030448
1627 || (reverse chronological order - newest change first)
1628 */
1629 x_last_update_date DATE ;
1630 x_last_updated_by NUMBER;
1631 x_last_update_login NUMBER;
1632 x_request_id NUMBER;
1633 x_program_id NUMBER;
1634 x_program_application_id NUMBER;
1635 x_program_update_date DATE;
1636
1637 BEGIN
1638
1639 x_last_update_date := SYSDATE;
1640 IF (X_MODE = 'I') THEN
1641 x_last_updated_by := 1;
1642 x_last_update_login := 0;
1643 ELSIF (x_mode = 'R') THEN
1644 x_last_updated_by := fnd_global.user_id;
1645 IF x_last_updated_by IS NULL THEN
1646 x_last_updated_by := -1;
1647 END IF;
1648 x_last_update_login := fnd_global.login_id;
1649 IF (x_last_update_login IS NULL) THEN
1650 x_last_update_login := -1;
1651 END IF;
1652 ELSE
1653 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1654 igs_ge_msg_stack.add;
1655 app_exception.raise_exception;
1656 END IF;
1657
1658 before_dml(
1659 p_action => 'UPDATE',
1660 x_rowid => x_rowid,
1661 x_credit_id => x_credit_id,
1662 x_credit_number => x_credit_number,
1663 x_status => x_status,
1664 x_credit_source => x_credit_source,
1665 x_party_id => x_party_id,
1666 x_credit_type_id => x_credit_type_id,
1667 x_credit_instrument => x_credit_instrument,
1668 x_description => x_description,
1669 x_amount => x_amount,
1670 x_currency_cd => x_currency_cd,
1671 x_exchange_rate => x_exchange_rate,
1672 x_transaction_date => x_transaction_date,
1673 x_effective_date => x_effective_date,
1674 x_reversal_date => x_reversal_date,
1675 x_reversal_reason_code => x_reversal_reason_code,
1676 x_reversal_comments => x_reversal_comments,
1677 x_unapplied_amount => x_unapplied_amount,
1678 x_source_transaction_id => x_source_transaction_id,
1679 x_receipt_lockbox_number => x_receipt_lockbox_number,
1680 x_merchant_id => x_merchant_id,
1681 x_credit_card_code => x_credit_card_code,
1682 x_credit_card_holder_name => x_credit_card_holder_name,
1683 x_credit_card_number => x_credit_card_number,
1684 x_credit_card_expiration_date => x_credit_card_expiration_date,
1685 x_credit_card_approval_code => x_credit_card_approval_code,
1686 x_awd_yr_cal_type => x_awd_yr_cal_type,
1687 x_awd_yr_ci_sequence_number => x_awd_yr_ci_sequence_number,
1688 x_fee_cal_type => x_fee_cal_type,
1689 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1690 x_attribute_category => x_attribute_category,
1691 x_attribute1 => x_attribute1,
1692 x_attribute2 => x_attribute2,
1693 x_attribute3 => x_attribute3,
1694 x_attribute4 => x_attribute4,
1695 x_attribute5 => x_attribute5,
1696 x_attribute6 => x_attribute6,
1697 x_attribute7 => x_attribute7,
1698 x_attribute8 => x_attribute8,
1699 x_attribute9 => x_attribute9,
1700 x_attribute10 => x_attribute10,
1701 x_attribute11 => x_attribute11,
1702 x_attribute12 => x_attribute12,
1703 x_attribute13 => x_attribute13,
1704 x_attribute14 => x_attribute14,
1705 x_attribute15 => x_attribute15,
1706 x_attribute16 => x_attribute16,
1707 x_attribute17 => x_attribute17,
1708 x_attribute18 => x_attribute18,
1709 x_attribute19 => x_attribute19,
1710 x_attribute20 => x_attribute20,
1711 x_creation_date => x_last_update_date,
1712 x_created_by => x_last_updated_by,
1713 x_last_update_date => x_last_update_date,
1714 x_last_updated_by => x_last_updated_by,
1715 x_last_update_login => x_last_update_login,
1716 x_gl_date => x_gl_date,
1717 x_check_number => x_check_number,
1718 x_source_transaction_type => x_source_transaction_type,
1719 x_source_transaction_ref => x_source_transaction_ref,
1720 x_credit_card_status_code => x_credit_card_status_code,
1721 x_credit_card_payee_cd => x_credit_card_payee_cd,
1722 x_credit_card_tangible_cd => x_credit_card_tangible_cd,
1723 x_lockbox_interface_id => x_lockbox_interface_id,
1724 x_batch_name => x_batch_name,
1725 x_deposit_date => x_deposit_date,
1726 x_source_invoice_id => x_source_invoice_id,
1727 x_tax_year_code => x_tax_year_code,
1728 x_waiver_name => x_waiver_name
1729 );
1730
1731 IF (x_mode = 'R') THEN
1732 x_request_id := fnd_global.conc_request_id;
1733 x_program_id := fnd_global.conc_program_id;
1734 x_program_application_id := fnd_global.prog_appl_id;
1735 IF (x_request_id = -1) THEN
1736 x_request_id := old_references.request_id;
1737 x_program_id := old_references.program_id;
1738 x_program_application_id := old_references.program_application_id;
1739 x_program_update_date := old_references.program_update_date;
1740 ELSE
1741 x_program_update_date := SYSDATE;
1742 END IF;
1743 END IF;
1744
1745 UPDATE igs_fi_credits_all
1746 SET
1747 credit_number = new_references.credit_number,
1748 status = new_references.status,
1749 credit_source = new_references.credit_source,
1750 party_id = new_references.party_id,
1751 credit_type_id = new_references.credit_type_id,
1752 credit_instrument = new_references.credit_instrument,
1753 description = new_references.description,
1754 amount = new_references.amount,
1755 currency_cd = new_references.currency_cd,
1756 exchange_rate = new_references.exchange_rate,
1757 transaction_date = new_references.transaction_date,
1758 effective_date = new_references.effective_date,
1759 reversal_date = new_references.reversal_date,
1760 reversal_reason_code = new_references.reversal_reason_code,
1761 reversal_comments = new_references.reversal_comments,
1762 unapplied_amount = new_references.unapplied_amount,
1763 source_transaction_id = new_references.source_transaction_id,
1764 receipt_lockbox_number = new_references.receipt_lockbox_number,
1765 merchant_id = new_references.merchant_id,
1766 credit_card_code = new_references.credit_card_code,
1767 credit_card_holder_name = new_references.credit_card_holder_name,
1768 credit_card_number = new_references.credit_card_number,
1769 credit_card_expiration_date = new_references.credit_card_expiration_date,
1770 credit_card_approval_code = new_references.credit_card_approval_code,
1771 awd_yr_cal_type = new_references.awd_yr_cal_type,
1772 awd_yr_ci_sequence_number = new_references.awd_yr_ci_sequence_number,
1773 fee_cal_type = new_references.fee_cal_type,
1774 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1775 attribute_category = new_references.attribute_category,
1776 attribute1 = new_references.attribute1,
1777 attribute2 = new_references.attribute2,
1778 attribute3 = new_references.attribute3,
1779 attribute4 = new_references.attribute4,
1780 attribute5 = new_references.attribute5,
1781 attribute6 = new_references.attribute6,
1782 attribute7 = new_references.attribute7,
1783 attribute8 = new_references.attribute8,
1784 attribute9 = new_references.attribute9,
1785 attribute10 = new_references.attribute10,
1786 attribute11 = new_references.attribute11,
1787 attribute12 = new_references.attribute12,
1788 attribute13 = new_references.attribute13,
1789 attribute14 = new_references.attribute14,
1790 attribute15 = new_references.attribute15,
1791 attribute16 = new_references.attribute16,
1792 attribute17 = new_references.attribute17,
1793 attribute18 = new_references.attribute18,
1794 attribute19 = new_references.attribute19,
1795 attribute20 = new_references.attribute20,
1796 last_update_date = x_last_update_date,
1797 last_updated_by = x_last_updated_by,
1798 last_update_login = x_last_update_login ,
1799 request_id = x_request_id,
1800 program_id = x_program_id,
1801 program_application_id = x_program_application_id,
1802 program_update_date = x_program_update_date,
1803 gl_date = x_gl_date,
1804 check_number = x_check_number,
1805 source_transaction_type = x_source_transaction_type,
1806 source_transaction_ref = x_source_transaction_ref,
1807 credit_card_status_code = x_credit_card_status_code,
1808 credit_card_payee_cd = x_credit_card_payee_cd,
1809 credit_card_tangible_cd = x_credit_card_tangible_cd,
1810 lockbox_interface_id = x_lockbox_interface_id,
1811 batch_name = x_batch_name,
1812 deposit_date = x_deposit_date,
1813 source_invoice_id = x_source_invoice_id,
1814 tax_year_code = x_tax_year_code,
1815 waiver_name = x_waiver_name
1816 WHERE rowid = x_rowid;
1817
1818 IF (SQL%NOTFOUND) THEN
1819 RAISE NO_DATA_FOUND;
1820 END IF;
1821
1822 END update_row;
1823
1824
1825 PROCEDURE add_row (
1826 x_rowid IN OUT NOCOPY VARCHAR2,
1827 x_credit_id IN OUT NOCOPY NUMBER,
1828 x_credit_number IN OUT NOCOPY VARCHAR2,
1829 x_status IN VARCHAR2,
1830 x_credit_source IN VARCHAR2,
1831 x_party_id IN NUMBER,
1832 x_credit_type_id IN NUMBER,
1833 x_credit_instrument IN VARCHAR2,
1834 x_description IN VARCHAR2,
1835 x_amount IN NUMBER,
1836 x_currency_cd IN VARCHAR2,
1837 x_exchange_rate IN NUMBER,
1838 x_transaction_date IN DATE,
1839 x_effective_date IN DATE,
1840 x_reversal_date IN DATE,
1841 x_reversal_reason_code IN VARCHAR2,
1842 x_reversal_comments IN VARCHAR2,
1843 x_unapplied_amount IN NUMBER,
1844 x_source_transaction_id IN NUMBER,
1845 x_receipt_lockbox_number IN VARCHAR2,
1846 x_merchant_id IN VARCHAR2,
1847 x_credit_card_code IN VARCHAR2,
1848 x_credit_card_holder_name IN VARCHAR2,
1849 x_credit_card_number IN VARCHAR2,
1850 x_credit_card_expiration_date IN DATE,
1851 x_credit_card_approval_code IN VARCHAR2,
1852 x_awd_yr_cal_type IN VARCHAR2,
1853 x_awd_yr_ci_sequence_number IN NUMBER,
1854 x_fee_cal_type IN VARCHAR2,
1855 x_fee_ci_sequence_number IN NUMBER,
1856 x_attribute_category IN VARCHAR2,
1857 x_attribute1 IN VARCHAR2,
1858 x_attribute2 IN VARCHAR2,
1859 x_attribute3 IN VARCHAR2,
1860 x_attribute4 IN VARCHAR2,
1861 x_attribute5 IN VARCHAR2,
1862 x_attribute6 IN VARCHAR2,
1863 x_attribute7 IN VARCHAR2,
1864 x_attribute8 IN VARCHAR2,
1865 x_attribute9 IN VARCHAR2,
1866 x_attribute10 IN VARCHAR2,
1867 x_attribute11 IN VARCHAR2,
1868 x_attribute12 IN VARCHAR2,
1869 x_attribute13 IN VARCHAR2,
1870 x_attribute14 IN VARCHAR2,
1871 x_attribute15 IN VARCHAR2,
1872 x_attribute16 IN VARCHAR2,
1873 x_attribute17 IN VARCHAR2,
1874 x_attribute18 IN VARCHAR2,
1875 x_attribute19 IN VARCHAR2,
1876 x_attribute20 IN VARCHAR2,
1877 x_mode IN VARCHAR2,
1878 x_gl_date IN DATE ,
1879 x_check_number IN VARCHAR2,
1880 x_source_transaction_type IN VARCHAR2,
1881 x_source_transaction_ref IN VARCHAR2,
1882 x_credit_card_status_code IN VARCHAR2,
1883 x_credit_card_payee_cd IN VARCHAR2,
1884 x_credit_card_tangible_cd IN VARCHAR2,
1885 x_lockbox_interface_id IN NUMBER ,
1886 x_batch_name IN VARCHAR2,
1887 x_deposit_date IN DATE,
1888 x_source_invoice_id IN NUMBER,
1889 x_tax_year_code IN VARCHAR2,
1890 x_waiver_name IN VARCHAR2
1891 ) AS
1892 /*
1893 || Created By : BDEVARAK
1894 || Created On : 26-APR-2001
1895 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1896 || Known limitations, enhancements or remarks :
1897 || Change History :
1898 || Who When What
1899 || uudayapr 8-Aug2005 Enh 3392095 Tution waiver Build
1900 || Added the Waiver name column
1901 || svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
1902 || Changed x_credit_number parameter as IN OUT in Add row
1903 || pathipat 20-Apr-2004 Enh 3558549 - Comm Rec Enhancements
1904 || Added new column source_invoice_id
1905 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new columns lockbox_interface_id,batch_name,deposit_date.
1906 || schodava 11-Jun-2003 Enh# 2831587, Added three new columns
1907 || shtatiko 03-DEC-2002 Enh Bug 2584741, Added three new columns, check_number,
1908 || source_transaction_type and source_transaction_ref
1909 || smadathi 01-Nov-2002 Enh Bug 2584986. Added new column GL_DATE
1910 || vvutukur 17-Sep-2002 Enh#2564643.Removed references to subaccount_id column as this has been
1911 || obsoleted.Also removed DEFAULT clause from procedure parameter list
1912 || to avoid gscc warnings.
1913 || SMVK 04-Feb-2002 Updated existing procedure for
1914 || Four parameters awd_yr_cal_type
1915 || awd_yr_ci_sequence_number
1916 || fee_cal_type, fee_ci_sequence_number
1917 || Enhancement Bug No.2191470
1918 || smadathi 05-oct-2001 Balance Flag reference removed .
1919 || Enhancement Bug No. 2030448
1920 || (reverse chronological order - newest change first)
1921 */
1922 CURSOR c1 IS
1923 SELECT rowid
1924 FROM igs_fi_credits_all
1925 WHERE credit_id = x_credit_id;
1926
1927 BEGIN
1928
1929 OPEN c1;
1930 FETCH c1 INTO x_rowid;
1931 IF (c1%NOTFOUND) THEN
1932 CLOSE c1;
1933
1934 insert_row (
1935 x_rowid,
1936 x_credit_id,
1937 x_credit_number,
1938 x_status,
1939 x_credit_source,
1940 x_party_id,
1941 x_credit_type_id,
1942 x_credit_instrument,
1943 x_description,
1944 x_amount,
1945 x_currency_cd,
1946 x_exchange_rate,
1947 x_transaction_date,
1948 x_effective_date,
1949 x_reversal_date,
1950 x_reversal_reason_code,
1951 x_reversal_comments,
1952 x_unapplied_amount,
1953 x_source_transaction_id,
1954 x_receipt_lockbox_number,
1955 x_merchant_id,
1956 x_credit_card_code,
1957 x_credit_card_holder_name,
1958 x_credit_card_number,
1959 x_credit_card_expiration_date,
1960 x_credit_card_approval_code,
1961 x_awd_yr_cal_type,
1962 x_awd_yr_ci_sequence_number,
1963 x_fee_cal_type,
1964 x_fee_ci_sequence_number,
1965 x_attribute_category,
1966 x_attribute1,
1967 x_attribute2,
1968 x_attribute3,
1969 x_attribute4,
1970 x_attribute5,
1971 x_attribute6,
1972 x_attribute7,
1973 x_attribute8,
1974 x_attribute9,
1975 x_attribute10,
1976 x_attribute11,
1977 x_attribute12,
1978 x_attribute13,
1979 x_attribute14,
1980 x_attribute15,
1981 x_attribute16,
1982 x_attribute17,
1983 x_attribute18,
1984 x_attribute19,
1985 x_attribute20,
1986 x_mode ,
1987 x_gl_date,
1988 x_check_number,
1989 x_source_transaction_type,
1990 x_source_transaction_ref,
1991 x_credit_card_status_code,
1992 x_credit_card_payee_cd,
1993 x_credit_card_tangible_cd,
1994 x_lockbox_interface_id,
1995 x_batch_name,
1996 x_deposit_date,
1997 x_source_invoice_id,
1998 x_tax_year_code,
1999 x_waiver_name
2000 );
2001 RETURN;
2002 END IF;
2003 CLOSE c1;
2004
2005 update_row (
2006 x_rowid,
2007 x_credit_id,
2008 x_credit_number,
2009 x_status,
2010 x_credit_source,
2011 x_party_id,
2012 x_credit_type_id,
2013 x_credit_instrument,
2014 x_description,
2015 x_amount,
2016 x_currency_cd,
2017 x_exchange_rate,
2018 x_transaction_date,
2019 x_effective_date,
2020 x_reversal_date,
2021 x_reversal_reason_code,
2022 x_reversal_comments,
2023 x_unapplied_amount,
2024 x_source_transaction_id,
2025 x_receipt_lockbox_number,
2026 x_merchant_id,
2027 x_credit_card_code,
2028 x_credit_card_holder_name,
2029 x_credit_card_number,
2030 x_credit_card_expiration_date,
2031 x_credit_card_approval_code,
2032 x_awd_yr_cal_type,
2033 x_awd_yr_ci_sequence_number,
2034 x_fee_cal_type,
2035 x_fee_ci_sequence_number,
2036 x_attribute_category,
2037 x_attribute1,
2038 x_attribute2,
2039 x_attribute3,
2040 x_attribute4,
2041 x_attribute5,
2042 x_attribute6,
2043 x_attribute7,
2044 x_attribute8,
2045 x_attribute9,
2046 x_attribute10,
2047 x_attribute11,
2048 x_attribute12,
2049 x_attribute13,
2050 x_attribute14,
2051 x_attribute15,
2052 x_attribute16,
2053 x_attribute17,
2054 x_attribute18,
2055 x_attribute19,
2056 x_attribute20,
2057 x_mode ,
2058 x_gl_date,
2059 x_check_number,
2060 x_source_transaction_type,
2061 x_source_transaction_ref,
2062 x_credit_card_status_code,
2063 x_credit_card_payee_cd,
2064 x_credit_card_tangible_cd,
2065 x_lockbox_interface_id,
2066 x_batch_name,
2067 x_deposit_date,
2068 x_source_invoice_id,
2069 x_tax_year_code,
2070 x_waiver_name
2071 );
2072
2073 END add_row;
2074
2075 END igs_fi_credits_pkg;