1 package body IGS_FI_FEE_TYPE_PKG AS
2 /* $Header: IGSSI37B.pls 120.3 2005/09/22 05:43:39 appldev ship $*/
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_FI_FEE_TYPE_ALL%RowType;
7 new_references IGS_FI_FEE_TYPE_ALL%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 ,
12 x_fee_type IN VARCHAR2 ,
13 x_s_fee_type IN VARCHAR2 ,
14 x_s_fee_trigger_cat IN VARCHAR2 ,
15 x_description IN VARCHAR2 ,
16 x_optional_payment_ind IN VARCHAR2 ,
17 x_closed_ind IN VARCHAR2 ,
18 x_comments IN VARCHAR2 ,
19 x_org_id IN NUMBER ,
20 x_fee_class IN VARCHAR2 , --Bug 2175865
21 x_designated_payment_flag IN VARCHAR2,
22 x_creation_date IN DATE ,
23 x_created_by IN NUMBER ,
24 x_last_update_date IN DATE ,
25 x_last_updated_by IN NUMBER ,
26 x_last_update_login IN NUMBER
27 ) AS
28 /*----------------------------------------------------------------------------
29 || Created By :
30 || Created On :
31 || Purpose :
32 || Known limitations, enhancements or remarks :
33 || Change History :
34 || Who When What
35 || (reverse chronological order - newest change first)
36 || shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
37 || smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
38 || As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
39 ----------------------------------------------------------------------------*/
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_FI_FEE_TYPE_ALL
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52
53 Open cur_old_ref_values;
54 Fetch cur_old_ref_values INTO old_references;
55 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
56 Close cur_old_ref_values;
57 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
58 IGS_GE_MSG_STACK.ADD;
59 App_Exception.Raise_Exception;
60 Return;
61 END IF;
62 Close cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.fee_type := x_fee_type;
66 new_references.s_fee_type := x_s_fee_type;
67 new_references.s_fee_trigger_cat := x_s_fee_trigger_cat;
68 new_references.description := x_description;
69 new_references.optional_payment_ind := x_optional_payment_ind;
70 new_references.closed_ind := x_closed_ind;
71 new_references.comments := x_comments;
72 new_references.org_id := x_org_id;
73 new_references.fee_class := x_fee_class; --Bug 2175865
74 new_references.designated_payment_flag := x_designated_payment_flag;
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login := x_last_update_login;
85
86 END Set_Column_Values;
87
88 -- Trigger description :-
89
90 -- "OSS_TST".trg_ft_br_iud
91
92 -- BEFORE INSERT OR DELETE OR UPDATE
93
94 -- ON IGS_FI_FEE_TYPE_ALL
95
96 -- FOR EACH ROW
97
98 PROCEDURE BeforeRowInsertUpdateDelete1(
99 p_inserting IN BOOLEAN ,
100 p_updating IN BOOLEAN ,
101 p_deleting IN BOOLEAN
102
103 ) AS
104 /*---------------------------------------------------------------------------
105 CHANGE HISTORY:
106 WHO WHEN WHAT
107 svuppala 09-SEP-2005 Bug#3286824 Modify cursors cur_docactiveft_count and c_ft to have one cursor c_act_ft
108 Made check for DOCUMENT and REFUND once.
109 vvutukur 03-Dec-2003 Bug#3249288.Modified cursor cur_optfeeflag_set to remove additional condition
110 on optional_fee_flag.
111 uudayapr 15-oct-2003 Enh #3117341 Modified by adding the token to IGS_FI_ANC_TRG_CAT message
112 smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
113 As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
114 vvutukur 24-Jun-2002 Added cursor cur_docactiveft_count and related validation
115 to throw error message if user tries to save two active fee type
116 records with system fee type 'DOCUMENT'.
117 smvk 13-Mar-2002 checking System Fee Trigger Category as INSTITUTN and
118 and only one active fee type having system fee type as
119 Refunds for a subaccount w.r.t Bug # 2144600
120 ----------------------------------------------------------------------------*/
121
122 CURSOR cur_optfeeflag_set(p_fee_type varchar2) is
123 SELECT 'x'
124 FROM igs_fi_inv_int
125 WHERE fee_type = p_fee_type;
126
127 ---cursor to get the number of active fee types of system fee type 'DOCUMENT' and 'REFUND' system fee type
128 CURSOR cur_act_ft(cp_s_fee_type IN igs_fi_fee_type.s_fee_type%TYPE,
129 cp_fee_type IN igs_fi_fee_type.fee_type%TYPE) IS
130 SELECT count('x')
131 FROM igs_fi_fee_type
132 WHERE s_fee_type = cp_s_fee_type
133 AND fee_type <> cp_fee_type
134 AND closed_ind='N';
135
136
137 -- Added for Refunds Build Enh BugNo:2144600
138 l_count NUMBER;
139 l_optfeeflag cur_optfeeflag_set%rowtype;
140 v_message_name varchar2(30);
141
142 l_desc igs_lookup_values.meaning%TYPE;
143
144 BEGIN
145 -- Validate Fee Type system trigger category
146
147 IF (p_updating AND (old_references.s_fee_trigger_cat) <>
148 (new_references.s_fee_trigger_cat) ) THEN
149 IF IGS_FI_VAL_FT.finp_val_ft_trig (
150 new_references.fee_type,
151 new_references.s_fee_trigger_cat,
152 old_references.s_fee_trigger_cat,
153 v_message_name) = FALSE THEN
154 Fnd_Message.Set_Name('IGS',v_message_name);
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 IF IGS_FI_VAL_FT.finp_val_ft_sftc (
159 new_references.fee_type,
160 new_references.s_fee_trigger_cat,
161 old_references.s_fee_trigger_cat,
162 v_message_name) = FALSE THEN
163 Fnd_Message.Set_Name('IGS',v_message_name);
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168
169 -- Validate Fee Type system fee type and system trigger category
170 IF (p_inserting OR (p_updating AND
171 (((old_references.s_fee_type) <> (new_references.s_fee_type)) OR
172 ((old_references.s_fee_trigger_cat) <> (new_references.s_fee_trigger_cat))))) THEN
173 IF IGS_FI_VAL_FT.finp_val_ft_sft_trig (
174 new_references.s_fee_type,
175 new_references.s_fee_trigger_cat,
176 v_message_name) = FALSE THEN
177 Fnd_Message.Set_Name('IGS',v_message_name);
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181 END IF;
182
183 -- Validate Fee Type optional payment indicator
184
185 IF (p_updating AND (old_references.optional_payment_ind) <>
186 (new_references.optional_payment_ind) ) THEN
187 IF IGS_FI_VAL_FT.finp_val_ft_opt_pymt (
188 new_references.fee_type,
189 new_references.optional_payment_ind,
190 v_message_name) = FALSE THEN
191 Fnd_Message.Set_Name('IGS',v_message_name);
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 /* Bug- 1989694, SF012_DLD-Account History and Payment
196 When Optional_Fee_Flag column in IGS_INV_INT_ALL Table is set then error out NOCOPY
197 that Optional_Payment_ind cannot be changed */
198
199 Open cur_optfeeflag_set(new_references.fee_type);
200 fetch cur_optfeeflag_set into l_optfeeflag;
201 IF cur_optfeeflag_set%FOUND THEN
202 CLOSE cur_optfeeflag_set;
203 Fnd_Message.Set_Name('IGS','IGS_FI_CANT_MODIFY_OPT_IND');
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END IF;
207 CLOSE cur_optfeeflag_set;
208 END IF;
209
210 --Throw error in case if more than one active fee types can exist with system fee type as 'DOCUMENT' or 'REFUND'.
211 IF (p_inserting OR (p_updating AND old_references.closed_ind <> new_references.closed_ind)) THEN
212
213 IF new_references.s_fee_type IN ('DOCUMENT','REFUND') AND
214 new_references.closed_ind='N' THEN
215
216 OPEN cur_act_ft(cp_s_fee_type => new_references.s_fee_type,
217 cp_fee_type => new_references.fee_type);
218 FETCH cur_act_ft INTO l_count;
219 CLOSE cur_act_ft;
220
221 IF NVL(l_count,0) >= 1 THEN
222 --If system fee type is of Refund
223 IF new_references.s_fee_type = 'REFUND' THEN
224 fnd_message.set_name('IGS','IGS_FI_REFUND_FEE');
225 --If system fee type is of Document
226 ELSIF new_references.s_fee_type = 'DOCUMENT' THEN
227 fnd_message.set_name('IGS','IGS_FI_DOC_TYP_NOT_MORE_ONE');
228 END IF;
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 END IF;
232 END IF;
233 END IF;
234
235 -- Checking for System Fee Type Refund to have System Fee Trigger Category as 'INSTITUTN' only
236 IF p_inserting OR p_updating THEN
237 IF new_references.s_fee_type = 'REFUND' THEN
238 IF new_references.s_fee_trigger_cat <> 'INSTITUTN' THEN
239 --got the lookup meaning and added token to the
240 --message IGS_FI_ANC_TRG_CAT.
241 fnd_Message.Set_Name('IGS','IGS_FI_ANC_TRG_CAT');
242 fnd_message.set_token('S_FEE_TRIG_CAT', igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => 'IGS_FI_LOCKBOX',
243 p_v_lookup_code => 'INSTITUTION'));
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 END IF;
248 END IF;
249
250
251 END BeforeRowInsertUpdateDelete1;
252
253 PROCEDURE BeforeRowInsertUpdate2(
254 p_inserting IN BOOLEAN ,
255 p_updating IN BOOLEAN ,
256 p_deleteing IN BOOLEAN
257 )AS
258
259 --HISTORY
260 --Who When What
261 --smvk 02-Sep-200 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
262 -- As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
263 --vvutukur 15-Jul-2002 Removed cursor cur_ft and related code as multiple fee classes can be attached to the
264 -- single subaccount that exists in the system,for bug#2432134.
265 --vvutukur 15-1-2002 created the procedure for Bug 2175865
266
267
268 --Cursor to check if the fee type has been used for creation of charge.
269 CURSOR cur_ft_chrg(
270 cp_new_fee_type VARCHAR2
271 ) IS
272 SELECT 'x'
273 FROM igs_fi_inv_int
274 WHERE fee_type = cp_new_fee_type;
275
276 l_var VARCHAR2(1);
277 BEGIN
278
279 --Validate if the fee type has been used for creation of a charge
280 IF(p_updating) THEN
281 --Validations if the fee type has been used for creation of a charge if fee class is modified.
282 IF (NVL(new_references.fee_class,'NULL') <> NVL(old_references.fee_class,'NULL')) THEN
283 OPEN cur_ft_chrg(new_references.fee_type);
284 FETCH cur_ft_chrg INTO l_var;
285 IF(cur_ft_chrg%FOUND) THEN
286 CLOSE cur_ft_chrg;
287 FND_MESSAGE.SET_NAME('IGS','IGS_FI_FEE_CLASS_USED');
288 IGS_GE_MSG_STACK.ADD;
289 APP_EXCEPTION.RAISE_EXCEPTION;
290 END IF;
291 CLOSE cur_ft_chrg;
292 END IF;
293 END IF;
294
295 END BeforeRowInsertUpdate2;
296
297 -- Trigger description :-
298 -- "OSS_TST".trg_ft_ar_u_hist
299 -- AFTER UPDATE
300 -- ON IGS_FI_FEE_TYPE_ALL
301 -- FOR EACH ROW
302
303 PROCEDURE AfterRowUpdate2(
304 p_inserting IN BOOLEAN ,
305 p_updating IN BOOLEAN ,
306 p_deleting IN BOOLEAN
307 ) AS
308 /*----------------------------------------------------------------------------
309 || Created By :
310 || Created On :
311 || Purpose :
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 || shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
317 || smvk 02-Sep-2002 Removed the Default value in the parameters to overcome File.Pkg.22 gscc warnings.
318 || As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
319 ----------------------------------------------------------------------------*/
320
321 BEGIN
322 -- create a history
323
324 IGS_FI_GEN_002.FINP_INS_FT_HIST(old_references.fee_type,
325 new_references.s_fee_type,
326 old_references.s_fee_type,
327 new_references.s_fee_trigger_cat,
328 old_references.s_fee_trigger_cat,
329 new_references.description,
330 old_references.description,
331 new_references.optional_payment_ind,
332 old_references.optional_payment_ind,
333 new_references.closed_ind,
334 old_references.closed_ind,
335 new_references.fee_class,
336 old_references.fee_class,
337 new_references.designated_payment_flag,
338 old_references.designated_payment_flag,
339 new_references.last_updated_by,
340 old_references.last_updated_by,
341 new_references.last_update_date,
342 old_references.last_update_date,
343 new_references.comments,
344 old_references.comments);
345
346 END AfterRowUpdate2;
347
348 PROCEDURE Check_Constraints (
349 Column_Name IN VARCHAR2,
350 Column_Value IN VARCHAR2
351 )AS
352 /*---------------------------------------------------------------------------
353 CHANGE HISTORY:
354 WHO WHEN WHAT
355 pmarada 28-jul-2005 Enh 3392095, added waiver_adj to the system fee type validation
359 of valid system fee types.
356 uudayapr 15-oct-2003 Enh# 3117341.Audit and special fees build added AUDIT,SPECIAL in
357 the list of valid values of system fee type and system fee trigger category.
358 vvutukur 06-Sep-2003 Enh#3045007.Payment Plans Build. Added PAY_PLAN also in the list
360 shtatiko 02-JUN-2003 Enh# 2831582, Added check for new column designated_payment_flag.
361 vvutukur 13-may-2002 removed upper case check on fee_type column.bug#2344826.
362 agairola 22-Mar-2002 Added the validation for System Fee Types LATE, FINANCE, REFUND, DOCUMENT,
363 AID_ADJ to have the Optional Payment Indicator as Y
364 smvk 13-Mar-2002 Added REFUND as valid System fee Type, checking SFTC as INSTITUTN and
365 and only one active fee type having system fee type for a subaccount
366 w.r.t Bug # 2144600
367 vvutukur 21-feb-2002 removed comments part for bug:2107967.
368 ----------------------------------------------------------------------------*/
369 BEGIN
370 IF Column_Name is NULL THEN
371 NULL;
372 ELSIF upper(Column_Name) = 'S_FEE_TYPE' then
373 new_references.s_fee_type := Column_Value;
374 ELSIF upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' then
375 new_references.optional_payment_ind := Column_Value;
376 ELSIF upper(Column_Name) = 'CLOSED_IND' then
377 new_references.closed_ind := Column_Value;
378 ELSIF upper(Column_Name) = 'DESCRIPTION' then
379 new_references.description:= Column_Value;
380 ELSIF upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' then
381 new_references.optional_payment_ind := Column_Value;
382 ELSIF upper(Column_Name) = 'S_FEE_TRIGGER_CAT' then
383 new_references.s_fee_trigger_cat := Column_Value;
384 ELSIF UPPER(column_name) = 'DESIGNATED_PAYMENT_FLAG' THEN
385 new_references.designated_payment_flag := column_value;
386 END IF;
387
388 -- As part of the enhancement bug #1715208 new_references.s_fee_type <> 'ANCILLARY' was also added in the
389 -- And condition of the IF statement.Thus making ANCILLARY a valid System fee type.
390 -- 'REFUND' is also added as per the Enhancement Bug no: 2144600
391
392 IF upper(Column_Name) = 'S_FEE_TYPE' OR
393 column_name is NULL THEN
394 IF new_references.s_fee_type <> 'HECS' AND
395 new_references.s_fee_type <> 'TUITION' AND
396 new_references.s_fee_type <> 'OTHER' AND
397 new_references.s_fee_type <> 'TUTNFEE' AND
398 new_references.s_fee_type <> 'EXTERNAL' AND
399 new_references.s_fee_type <> 'LATE' AND
400 new_references.s_fee_type <> 'INTEREST' AND
401 new_references.s_fee_type <> 'ANCILLARY' AND
402 new_references.s_fee_type <> 'DOCUMENT' AND -- add by kkillams w.r.t. bug no:2212964
403 new_references.s_fee_type <> 'REFUND' AND -- added w.r.t. Bug No: 2144600
404 new_references.s_fee_type <> 'SPONSOR' AND
405 new_references.s_fee_type <> 'AID_ADJ' AND
406 new_references.s_fee_type <> 'PAY_PLAN' AND
407 new_references.s_fee_type <> 'AUDIT' AND
408 new_references.s_fee_type <> 'SPECIAL' AND
409 new_references.s_fee_type <> 'WAIVER_ADJ' THEN
410 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
411 IGS_GE_MSG_STACK.ADD;
412 App_Exception.Raise_Exception;
413 END IF;
414 END IF;
415 IF (upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' OR
416 column_name is NULL) THEN
417 IF new_references.optional_payment_ind <> 'Y' AND
418 new_references.optional_payment_ind <> 'N'
419 THEN
420 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
421 IGS_GE_MSG_STACK.ADD;
422 App_Exception.Raise_Exception;
423 END IF;
424 END IF;
425 IF (upper(Column_Name) = 'CLOSED_IND' OR
426 column_name is NULL) THEN
427 IF(new_references.closed_ind <> 'Y' AND
428 new_references.closed_ind <> 'N') THEN
429 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
430 IGS_GE_MSG_STACK.ADD;
431 App_Exception.Raise_Exception;
432 END IF;
433 END IF;
434 IF (upper(Column_Name) = 'S_FEE_TRIGGER_CAT' OR
435 column_name is NULL) THEN
436 IF new_references.s_fee_trigger_cat <> 'INSTITUTN' AND
437 new_references.s_fee_trigger_cat <> 'COURSE' AND
438 new_references.s_fee_trigger_cat <> 'UNIT' AND
439 new_references.s_fee_trigger_cat <> 'COMPOSITE' AND
440 new_references.s_fee_trigger_cat <> 'UNITSET' AND
441 new_references.s_fee_trigger_cat <> 'AUDIT' AND
442 new_references.s_fee_trigger_cat <> 'SPECIAL' THEN
443
444 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
445 IGS_GE_MSG_STACK.ADD;
446 App_Exception.Raise_Exception;
447 END IF;
448 END IF;
449
450 -- If the Optional Payment Indicator is set for Refunds,Finance, Late Charges etc. System Fee Types
454 IF new_references.optional_payment_ind = 'Y' THEN
451 -- then raise error
452 IF (upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' OR
453 column_name IS NULL) THEN
455 IF new_references.s_fee_type IN ('REFUND',
456 'LATE',
457 'INTEREST',
458 'SPONSOR',
459 'AID_ADJ',
460 'DOCUMENT') THEN
461 fnd_message.set_name('IGS',
462 'IGS_FI_CANNOT_SET_OPT');
463 igs_ge_msg_stack.add;
464 app_exception.raise_exception;
465 END IF;
466 END IF;
467 END IF;
468
469 IF ( UPPER(column_name) = 'DESIGNATED_PAYMENT_FLAG'
470 OR column_name is NULL) THEN
471 IF( new_references.designated_payment_flag <> 'Y'
472 AND new_references.designated_payment_flag <> 'N' ) THEN
473 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
474 igs_ge_msg_stack.ADD;
475 app_exception.raise_exception;
476 END IF;
477 END IF;
478
479 END Check_Constraints;
480
481
482 --created procedure as part of Bug 2175865
483 PROCEDURE check_parent_existance AS
484
485 --HISTORY
486 --Created by : vvutukur
487 --Purpose : for Bug 2175865
488 --Who When What
489 --
490
491 BEGIN
492
493 -- Check for parent existance of fee class
494 IF ((old_references.fee_class = new_references.fee_class)
495 OR (new_references.fee_class IS NULL)) THEN
496 NULL;
497 ELSE
498 IF NOT igs_lookups_view_pkg.get_pk_for_validation('FEE_CLASS',
499 new_references.fee_class) THEN
500 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
501 IGS_GE_MSG_STACK.ADD;
502 APP_EXCEPTION.RAISE_EXCEPTION;
503 END IF;
504 END IF;
505 END check_parent_existance;
506
507
508 FUNCTION get_pk_for_validation (
509 x_fee_type IN VARCHAR2
510 ) RETURN BOOLEAN AS
511 ------------------------------------------------------------------
512 --Change History:
513 --Who When What
514 --pathipat 11-Feb-2003 Enh 2747325 - Locking Issues build
515 -- Removed FOR UPDATE NOWAIT clause in cur_rowid
516 -------------------------------------------------------------------
517 CURSOR cur_rowid IS
518 SELECT rowid
519 FROM igs_fi_fee_type_all
520 WHERE fee_type = x_fee_type ;
521
522 lv_rowid cur_rowid%ROWTYPE;
523
524 BEGIN
525 OPEN cur_rowid;
526 FETCH cur_rowid INTO lv_rowid;
527 IF (cur_rowid%FOUND) THEN
528 CLOSE cur_rowid;
529 RETURN(TRUE);
530 ELSE
531 CLOSE cur_rowid;
532 RETURN(FALSE);
533 END IF;
534 END get_pk_for_validation;
535
536
537 PROCEDURE Before_DML (
538 p_action IN VARCHAR2,
539 x_rowid IN VARCHAR2,
540 x_fee_type IN VARCHAR2,
541 x_s_fee_type IN VARCHAR2,
542 x_s_fee_trigger_cat IN VARCHAR2,
543 x_description IN VARCHAR2,
544 x_optional_payment_ind IN VARCHAR2,
545 x_closed_ind IN VARCHAR2,
546 x_comments IN VARCHAR2,
547 x_org_id IN NUMBER,
548 x_fee_class IN VARCHAR2, --Bug 2175865
549 x_designated_payment_flag IN VARCHAR2,
550 x_creation_date IN DATE,
551 x_created_by IN NUMBER,
552 x_last_update_date IN DATE,
553 x_last_updated_by IN NUMBER,
554 x_last_update_login IN NUMBER
555 ) AS
556 ------------------------------------------------------------------
557 --Change History:
558 --Who When What
559 --shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
560 --pathipat 11-Feb-2003 Enh 2747325 - Locking Issues build
561 -- Removed code for p_action = 'DELETE' and
562 -- 'VALIDATE_DELETE'
563 -------------------------------------------------------------------
564
565 BEGIN
566
567 Set_Column_Values (
568 p_action,
569 x_rowid,
570 x_fee_type,
571 x_s_fee_type,
572 x_s_fee_trigger_cat,
573 x_description,
574 x_optional_payment_ind,
575 x_closed_ind,
576 x_comments,
577 x_org_id,
578 x_fee_class, --Bug 2175865
579 x_designated_payment_flag,
580 x_creation_date,
581 x_created_by,
582 x_last_update_date,
583 x_last_updated_by,
584 x_last_update_login
585 );
586
587 IF (p_action = 'INSERT') THEN
588 -- Call all the procedures related to Before Insert.
589 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating =>FALSE , p_deleting =>FALSE);
590 IF Get_PK_For_Validation ( new_references.fee_type )THEN
591 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
592 IGS_GE_MSG_STACK.ADD;
593 App_Exception.Raise_Exception;
594 END IF;
595
596 --by vvutukur for Bug 2175865
597
601 ELSIF (p_action = 'UPDATE') THEN
598 BeforeRowInsertUpdate2 ( p_inserting => TRUE, p_updating => FALSE, p_deleteing =>FALSE);
599 Check_Constraints;
600 check_parent_existance; --Bug 2175865
602 -- Call all the procedures related to Before Update.
603 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => TRUE , p_deleting =>FALSE);
604
605 --by vvutukur for Bug 2175865
606 BeforeRowInsertUpdate2 ( p_inserting => FALSE, p_updating => TRUE, p_deleteing =>FALSE);
607
608 Check_Constraints;
609 check_parent_existance; --Bug 2175865
610 ELSIF (p_action = 'VALIDATE_INSERT') THEN
611 -- Call all the procedures related to Before Insert.
612 IF Get_PK_For_Validation ( new_references.fee_type ) THEN
613 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
614 IGS_GE_MSG_STACK.ADD;
615 App_Exception.Raise_Exception;
616 END IF;
617 Check_Constraints;
618 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
619 Check_Constraints;
620 END IF;
621 END Before_DML;
622
623
624 PROCEDURE After_DML (
625
626 p_action IN VARCHAR2,
627
628 x_rowid IN VARCHAR2
629
630 ) AS
631
632 BEGIN
633
634
635
636 l_rowid := x_rowid;
637
638 IF (p_action = 'UPDATE') THEN
639
640 -- Call all the procedures related to After Update.
641
642 AfterRowUpdate2 (p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
643 END IF;
644 END After_DML;
645
646
647 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
648 procedure INSERT_ROW (
649 X_ROWID in out NOCOPY VARCHAR2,
650 X_FEE_TYPE in VARCHAR2,
651 X_S_FEE_TYPE in VARCHAR2,
652 X_S_FEE_TRIGGER_CAT in VARCHAR2,
653 X_DESCRIPTION in VARCHAR2,
654 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
655 X_CLOSED_IND in VARCHAR2,
656 X_COMMENTS in VARCHAR2,
657 X_MODE in VARCHAR2,
658 X_ORG_ID in NUMBER,
659 X_FEE_CLASS in VARCHAR2, --Bug 2175865
660 X_DESIGNATED_PAYMENT_FLAG IN VARCHAR2
661 ) AS
662 cursor C is select ROWID from IGS_FI_FEE_TYPE_ALL
663 where FEE_TYPE = X_FEE_TYPE;
664 X_LAST_UPDATE_DATE DATE;
665 X_LAST_UPDATED_BY NUMBER;
666 X_LAST_UPDATE_LOGIN NUMBER;
667 begin
668 X_LAST_UPDATE_DATE := SYSDATE;
669 if (X_MODE = 'I') then
670 X_LAST_UPDATED_BY := 1;
671 X_LAST_UPDATE_LOGIN := 0;
672 elsif (X_MODE = 'R') then
673 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
674 if X_LAST_UPDATED_BY is NULL then
675 X_LAST_UPDATED_BY := -1;
676 end if;
677 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
678 if X_LAST_UPDATE_LOGIN is NULL then
679 X_LAST_UPDATE_LOGIN := -1;
680 end if;
681 else
682 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
683 IGS_GE_MSG_STACK.ADD;
684 app_exception.raise_exception;
685 end if;
686
687
688
689 Before_DML(
690 p_action=>'INSERT',
691 x_rowid=>X_ROWID,
692 x_closed_ind=>NVL(X_CLOSED_IND,'N'),
693 x_comments=>X_COMMENTS,
694 x_description=>X_DESCRIPTION,
695 x_fee_type=>X_FEE_TYPE,
696 x_optional_payment_ind=>NVL(X_OPTIONAL_PAYMENT_IND,'N'),
697 x_s_fee_trigger_cat=>NVL(X_S_FEE_TRIGGER_CAT,'INSTITUTN'),
698 x_s_fee_type=>NVL(X_S_FEE_TYPE,'OTHER'),
699 x_org_id => igs_ge_gen_003.get_org_id,
700 x_fee_class => X_FEE_CLASS, --Bug 2175865
701 x_designated_payment_flag => x_designated_payment_flag,
702 x_creation_date=>X_LAST_UPDATE_DATE,
703 x_created_by=>X_LAST_UPDATED_BY,
704 x_last_update_date=>X_LAST_UPDATE_DATE,
705 x_last_updated_by=>X_LAST_UPDATED_BY,
706 x_last_update_login=>X_LAST_UPDATE_LOGIN
707 );
708
709 insert into IGS_FI_FEE_TYPE_ALL (
710 FEE_TYPE,
711 S_FEE_TYPE,
712 S_FEE_TRIGGER_CAT,
713 DESCRIPTION,
714 OPTIONAL_PAYMENT_IND,
715 CLOSED_IND,
716 COMMENTS,
717 ORG_ID,
718 FEE_CLASS, --Bug 2175865
719 DESIGNATED_PAYMENT_FLAG,
720 CREATION_DATE,
721 CREATED_BY,
722 LAST_UPDATE_DATE,
723 LAST_UPDATED_BY,
724 LAST_UPDATE_LOGIN
725 ) values (
726 NEW_REFERENCES.FEE_TYPE,
727 NEW_REFERENCES.S_FEE_TYPE,
728 NEW_REFERENCES.S_FEE_TRIGGER_CAT,
729 NEW_REFERENCES.DESCRIPTION,
730 NEW_REFERENCES.OPTIONAL_PAYMENT_IND,
731 NEW_REFERENCES.CLOSED_IND,
732 NEW_REFERENCES.COMMENTS,
733 NEW_REFERENCES.ORG_ID,
734 NEW_REFERENCES.FEE_CLASS, --Bug 2175865
735 NEW_REFERENCES.DESIGNATED_PAYMENT_FLAG,
736 X_LAST_UPDATE_DATE,
737 X_LAST_UPDATED_BY,
738 X_LAST_UPDATE_DATE,
739 X_LAST_UPDATED_BY,
740 X_LAST_UPDATE_LOGIN
741 );
742
743 open c;
744 fetch c into X_ROWID;
745 if (c%notfound) then
746 close c;
747 raise no_data_found;
748 end if;
749 close c;
750
751 end INSERT_ROW;
752
753 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
754 procedure LOCK_ROW (
755 X_ROWID in VARCHAR2,
756 X_FEE_TYPE in VARCHAR2,
760 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
757 X_S_FEE_TYPE in VARCHAR2,
758 X_S_FEE_TRIGGER_CAT in VARCHAR2,
759 X_DESCRIPTION in VARCHAR2,
761 X_CLOSED_IND in VARCHAR2,
762 X_COMMENTS in VARCHAR2,
763 X_FEE_CLASS IN VARCHAR2, --Bug 2175865
764 X_DESIGNATED_PAYMENT_FLAG IN VARCHAR2
765 ) AS
766 cursor c1 is select
767 S_FEE_TYPE,
768 S_FEE_TRIGGER_CAT,
769 DESCRIPTION,
770 OPTIONAL_PAYMENT_IND,
771 CLOSED_IND,
772 COMMENTS,
773 FEE_CLASS, --Bug 2175865
774 designated_payment_flag
775 from IGS_FI_FEE_TYPE_ALL
776 where ROWID = X_ROWID
777 for update nowait;
778 tlinfo c1%rowtype;
779
780 begin
781 open c1;
782 fetch c1 into tlinfo;
783 if (c1%notfound) then
784 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
785 IGS_GE_MSG_STACK.ADD;
786 app_exception.raise_exception;
787 close c1;
788 return;
789 end if;
790 close c1;
791
792 if ( (tlinfo.S_FEE_TYPE = X_S_FEE_TYPE)
793 AND (tlinfo.S_FEE_TRIGGER_CAT = X_S_FEE_TRIGGER_CAT)
794 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
795 AND (tlinfo.OPTIONAL_PAYMENT_IND = X_OPTIONAL_PAYMENT_IND)
796 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
797 AND ((tlinfo.COMMENTS = X_COMMENTS)
798 OR ((tlinfo.COMMENTS is null)
799 AND (X_COMMENTS is null)))
800 -- BUG 2175865 by vvutukur
801 AND ((tlinfo.FEE_CLASS = X_FEE_CLASS)
802 OR ((tlinfo.FEE_CLASS IS NULL) AND (X_FEE_CLASS IS NULL)))
803 AND ((tlinfo.DESIGNATED_PAYMENT_FLAG = X_DESIGNATED_PAYMENT_FLAG)
804 OR ((tlinfo.DESIGNATED_PAYMENT_FLAG IS NULL) AND (X_DESIGNATED_PAYMENT_FLAG IS NULL)))
805 ) then
806 null;
807 else
808 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
809 IGS_GE_MSG_STACK.ADD;
810 app_exception.raise_exception;
811 end if;
812 return;
813 end LOCK_ROW;
814
815 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
816 procedure UPDATE_ROW (
817 X_ROWID in VARCHAR2,
818 X_FEE_TYPE in VARCHAR2,
819 X_S_FEE_TYPE in VARCHAR2,
820 X_S_FEE_TRIGGER_CAT in VARCHAR2,
821 X_DESCRIPTION in VARCHAR2,
822 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
823 X_CLOSED_IND in VARCHAR2,
824 X_COMMENTS in VARCHAR2,
825 X_MODE in VARCHAR2,
826 X_FEE_CLASS in VARCHAR2, --Bug 2175865
827 X_DESIGNATED_PAYMENT_FLAG IN VARCHAR2
828 ) is
829 X_LAST_UPDATE_DATE DATE;
830 X_LAST_UPDATED_BY NUMBER;
831 X_LAST_UPDATE_LOGIN NUMBER;
832 begin
833 X_LAST_UPDATE_DATE := SYSDATE;
834 if(X_MODE = 'I') then
835 X_LAST_UPDATED_BY := 1;
836 X_LAST_UPDATE_LOGIN := 0;
837 elsif (X_MODE = 'R') then
838 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
839 if X_LAST_UPDATED_BY is NULL then
840 X_LAST_UPDATED_BY := -1;
841 end if;
842 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
843 if X_LAST_UPDATE_LOGIN is NULL then
844 X_LAST_UPDATE_LOGIN := -1;
845 end if;
846 else
847 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
848 IGS_GE_MSG_STACK.ADD;
849 app_exception.raise_exception;
850 end if;
851
852
853
854 Before_DML(
855 p_action=>'UPDATE',
856 x_rowid=>X_ROWID,
857 x_closed_ind=>X_CLOSED_IND,
858 x_comments=>X_COMMENTS,
859 x_description=>X_DESCRIPTION,
860 x_fee_type=>X_FEE_TYPE,
861 x_optional_payment_ind=>X_OPTIONAL_PAYMENT_IND,
862 x_s_fee_trigger_cat=>X_S_FEE_TRIGGER_CAT,
863 x_s_fee_type=>X_S_FEE_TYPE,
864 x_fee_class => X_FEE_CLASS, --Bug 2175865
865 x_designated_payment_flag => x_designated_payment_flag,
866 x_creation_date=>X_LAST_UPDATE_DATE,
867 x_created_by=>X_LAST_UPDATED_BY,
868 x_last_update_date=>X_LAST_UPDATE_DATE,
869 x_last_updated_by=>X_LAST_UPDATED_BY,
870 x_last_update_login=>X_LAST_UPDATE_LOGIN
871 );
872
873
874 update IGS_FI_FEE_TYPE_ALL set
875 S_FEE_TYPE = NEW_REFERENCES.S_FEE_TYPE,
876 S_FEE_TRIGGER_CAT = NEW_REFERENCES.S_FEE_TRIGGER_CAT,
877 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
878 OPTIONAL_PAYMENT_IND = NEW_REFERENCES.OPTIONAL_PAYMENT_IND,
879 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
880 COMMENTS = NEW_REFERENCES.COMMENTS,
881 FEE_CLASS = NEW_REFERENCES.FEE_CLASS, --Bug 2175865
882 designated_payment_flag = new_references.designated_payment_flag,
883 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
884 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
885 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
886 where ROWID = X_ROWID;
887 if (sql%notfound) then
888 raise no_data_found;
889 end if;
890
891
892
893 After_DML (
894 p_action => 'UPDATE',
895 x_rowid => X_ROWID
896 );
897 END update_row;
898
899 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag
900 procedure ADD_ROW (
901 X_ROWID in out NOCOPY VARCHAR2,
902 X_FEE_TYPE in VARCHAR2,
903 X_S_FEE_TYPE in VARCHAR2,
904 X_S_FEE_TRIGGER_CAT in VARCHAR2,
905 X_DESCRIPTION in VARCHAR2,
906 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
907 X_CLOSED_IND in VARCHAR2,
908 X_COMMENTS in VARCHAR2,
909 X_MODE in VARCHAR2,
910 X_ORG_ID in NUMBER,
911 X_FEE_CLASS in VARCHAR2, --Bug 2175865
912 X_DESIGNATED_PAYMENT_FLAG IN VARCHAR2
913 ) AS
914 CURSOR c1 is SELECT rowid FROM igs_fi_fee_type_all
915 WHERE FEE_TYPE = X_FEE_TYPE
916 ;
917 begin
918 OPEN c1;
919 FETCH c1 INTO X_ROWID;
920 IF (c1%NOTFOUND) THEN
921 close c1;
922 INSERT_ROW (
923 X_ROWID,
924 X_FEE_TYPE,
925 X_S_FEE_TYPE,
926 X_S_FEE_TRIGGER_CAT,
927 X_DESCRIPTION,
928 X_OPTIONAL_PAYMENT_IND,
929 X_CLOSED_IND,
930 X_COMMENTS,
931 X_MODE,
932 X_ORG_ID,
933 X_FEE_CLASS, --Bug 2175865
934 X_DESIGNATED_PAYMENT_FLAG );
935 RETURN;
936 END IF;
937 CLOSE c1;
938 UPDATE_ROW (
939 X_ROWID,
940 X_FEE_TYPE,
941 X_S_FEE_TYPE,
942 X_S_FEE_TRIGGER_CAT,
943 X_DESCRIPTION,
944 X_OPTIONAL_PAYMENT_IND,
945 X_CLOSED_IND,
946 X_COMMENTS,
947 X_MODE,
948 X_FEE_CLASS, --Bug 2175865
949 X_DESIGNATED_PAYMENT_FLAG );
950 END add_row;
951
952
953 END igs_fi_fee_type_pkg;
[Home] [Help]