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