[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_FEE_TYPE_HIST_PKG
Source
1 package body IGS_FI_FEE_TYPE_HIST_PKG AS
2 /* $Header: IGSSI39B.pls 120.2 2006/06/09 06:45:04 sapanigr ship $*/
3 --added columns subaccount_id and fee_class w.r.t Bug 2175865
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_FEE_TYPE_HIST_ALL%RowType;
6 new_references IGS_FI_FEE_TYPE_HIST_ALL%RowType;
7
8 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 ,
12 x_fee_type IN VARCHAR2 ,
13 x_hist_start_dt IN DATE ,
14 x_hist_end_dt IN DATE ,
15 x_hist_who IN VARCHAR2 ,
16 x_s_fee_type IN VARCHAR2 ,
17 x_s_fee_trigger_cat IN VARCHAR2 ,
18 x_description IN VARCHAR2 ,
19 x_optional_payment_ind IN VARCHAR2 ,
20 x_closed_ind IN VARCHAR2 ,
21 x_comments IN VARCHAR2 ,
22 x_org_id in NUMBER ,
23 x_fee_class IN VARCHAR2 , --Bug 2175865
24 x_designated_payment_flag IN VARCHAR2,
25 x_creation_date IN DATE ,
26 x_created_by IN NUMBER ,
27 x_last_update_date IN DATE ,
28 x_last_updated_by IN NUMBER ,
29 x_last_update_login IN NUMBER ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_FI_FEE_TYPE_HIST_ALL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.fee_type := x_fee_type;
56 new_references.hist_start_dt := x_hist_start_dt;
57 new_references.hist_end_dt := x_hist_end_dt;
58 new_references.hist_who := x_hist_who;
59 new_references.s_fee_type := x_s_fee_type;
60 new_references.s_fee_trigger_cat := x_s_fee_trigger_cat;
61 new_references.description := x_description;
62 new_references.optional_payment_ind := x_optional_payment_ind;
63 new_references.closed_ind := x_closed_ind;
64 new_references.comments := x_comments;
65 new_references.org_id := x_org_id;
66 new_references.fee_class := x_fee_class; --Bug 2175865
67 new_references.designated_payment_flag := x_designated_payment_flag;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END Set_Column_Values;
82
83 PROCEDURE Check_Constraints (
84 Column_Name IN VARCHAR2 ,
85 Column_Value IN VARCHAR2
86 )AS
87 /*-----------------------------------------------------------------------------
88 CHANGE HISTORY:
89 WHO WHEN WHAT
90 pmarada 28-jul-2005 Enh 3392095, added waiver_adj to the system fee type validation
91 uudayapr 15-oct-2003 Enh#3117341. Audit and Special Fees Build added AUDIT,SPECIAL also
92 in the list of valid values for system fee types and system fee Trigger
93 category.
94 vvutukur 06-Sep-2003 Enh#3045007.Payment Plans Build. Added PAY_PLAN also in the list
95 of valid system fee types.
96 vvutukur 18-may-2002 removed upper check on fee_type column.bug#2344826.
97 smvk 01-Mar-2002 Added three more System Fee Types w.r.t. Bug # 2144600
98 vvutukur 21-feb-2002 removed check for the column "comments" as it allows
99 both and mixed case.Done for bug:2107967
100 -------------------------------------------------------------------------------*/
101
102 BEGIN
103 IF Column_Name is NULL THEN
104 NULL;
105 ELSIF upper(Column_Name) = 'S_FEE_TYPE' then
106 new_references.s_fee_type := Column_Value;
107 ELSIF upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' then
108 new_references.optional_payment_ind := Column_Value;
109 ELSIF upper(Column_Name) = 'CLOSED_IND' then
110 new_references.closed_ind := Column_Value;
111 ELSIF upper(Column_Name) = 'DESCRIPTION' then
112 new_references.description:= Column_Value;
113 ELSIF upper(Column_Name) = 'S_FEE_TRIGGER_CAT' then
114 new_references.s_fee_trigger_cat := Column_Value;
115 END IF;
116 --Added AUDIT AND SPECAIAL ALSO A VALID LIST of S_FEE_TYPE
117 IF upper(Column_Name) = 'S_FEE_TYPE' OR
118 column_name is NULL THEN
119 IF new_references.s_fee_type <> 'HECS' AND
120 new_references.s_fee_type <> 'TUITION' AND
121 new_references.s_fee_type <> 'OTHER' AND
122 new_references.s_fee_type <> 'LATE' AND
123 new_references.s_fee_type <> 'INTEREST' AND
124 new_references.s_fee_type <> 'TUTNFEE' AND
125 new_references.s_fee_type <> 'SPONSOR' AND
126 new_references.s_fee_type <> 'ANCILLARY' AND -- added w.r.t. Bug # 2144600
127 new_references.s_fee_type <> 'EXTERNAL' AND -- added w.r.t. Bug # 2144600
128 new_references.s_fee_type <> 'REFUND' AND -- added w.r.t. Bug # 2144600
129 new_references.s_fee_type <> 'AID_ADJ' AND
130 new_references.s_fee_type <> 'PAY_PLAN' AND
131 new_references.s_fee_type <> 'AUDIT' AND
132 new_references.s_fee_type <> 'SPECIAL' AND
133 new_references.s_fee_type <> 'WAIVER_ADJ' THEN
134 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139 IF upper(Column_Name) = 'OPTIONAL_PAYMENT_IND' OR column_name is NULL THEN
140 IF new_references.optional_payment_ind <> 'Y' AND
141 new_references.optional_payment_ind <> 'N' THEN
142 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147 IF upper(Column_Name) = 'CLOSED_IND' OR column_name is NULL THEN
148 IF new_references.closed_ind <> 'Y' AND
149 new_references.closed_ind <> 'N' THEN
150 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END IF;
154 END IF;
155 -- Added audit and special
156 IF upper(Column_Name) = 'S_FEE_TRIGGER_CAT' OR
157 column_name is NULL THEN
158 IF new_references.S_FEE_TRIGGER_CAT <> 'INSTITUTN' AND
159 new_references.S_FEE_TRIGGER_CAT <> 'COURSE' AND
160 new_references.S_FEE_TRIGGER_CAT <> 'UNIT' AND
161 new_references.S_FEE_TRIGGER_CAT <> 'COMPOSITE' AND
162 new_references.S_FEE_TRIGGER_CAT <> 'UNITSET' AND
163 new_references.S_FEE_TRIGGER_CAT <> 'AUDIT' AND
164 new_references.S_FEE_TRIGGER_CAT <> 'SPECIAL' THEN
165 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
166 IGS_GE_MSG_STACK.ADD;
167 App_Exception.Raise_Exception;
168 END IF;
169 END IF;
170 END Check_Constraints;
171
172 --created procedure as part of Bug 2175865
173 PROCEDURE check_parent_existance AS
174
175 BEGIN
176 -- Check for parent existance of fee class
177 IF ((old_references.fee_class = new_references.fee_class)
178 OR (new_references.fee_class IS NULL)) THEN
179 NULL;
180 ELSE
181 IF NOT igs_lookups_view_pkg.get_pk_for_validation('FEE_CLASS',
182 new_references.fee_class) THEN
183 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
184 IGS_GE_MSG_STACK.ADD;
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 END IF;
187 END IF;
188 END check_parent_existance;
189
190
191 FUNCTION Get_PK_For_Validation (
192 x_fee_type IN VARCHAR2,
193 x_hist_start_dt IN DATE
194 ) RETURN BOOLEAN AS
195
196 CURSOR cur_rowid IS
197 SELECT rowid
198 FROM IGS_FI_FEE_TYPE_HIST_ALL
199 WHERE fee_type = x_fee_type
200 AND hist_start_dt = x_hist_start_dt
201 FOR UPDATE NOWAIT;
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206 Open cur_rowid;
207 Fetch cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 Close cur_rowid;
210 Return (TRUE);
211 ELSE
212 Close cur_rowid;
213 Return (FALSE);
214 END IF;
215 END Get_PK_For_Validation;
216
217 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
218 --added columns subaccount_id and fee_class w.r.t Bug 2175865
219 PROCEDURE Before_DML (
220 p_action IN VARCHAR2,
221 x_rowid IN VARCHAR2 ,
222 x_fee_type IN VARCHAR2 ,
223 x_hist_start_dt IN DATE ,
224 x_hist_end_dt IN DATE ,
225 x_hist_who IN VARCHAR2 ,
226 x_s_fee_type IN VARCHAR2 ,
227 x_s_fee_trigger_cat IN VARCHAR2 ,
228 x_description IN VARCHAR2 ,
229 x_optional_payment_ind IN VARCHAR2 ,
230 x_closed_ind IN VARCHAR2 ,
231 x_comments IN VARCHAR2 ,
232 x_org_id in NUMBER ,
233 x_fee_class IN VARCHAR2 , --Bug 2175865
234 x_designated_payment_flag IN VARCHAR2,
235 x_creation_date IN DATE ,
236 x_created_by IN NUMBER ,
237 x_last_update_date IN DATE ,
238 x_last_updated_by IN NUMBER ,
239 x_last_update_login IN NUMBER
240 ) AS
241
242 BEGIN
243
244 Set_Column_Values (
245 p_action,
246 x_rowid,
247 x_fee_type,
248 x_hist_start_dt,
249 x_hist_end_dt,
250 x_hist_who,
251 x_s_fee_type,
252 x_s_fee_trigger_cat,
253 x_description,
254 x_optional_payment_ind,
255 x_closed_ind,
256 x_comments,
257 x_org_id,
258 x_fee_class, --for Bug 2175865
259 x_designated_payment_flag,
260 x_creation_date,
261 x_created_by,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_login
265 );
266 IF (p_action = 'INSERT') THEN
267 -- Call all the procedures related to Before Insert.
268
269 IF Get_PK_For_Validation ( new_references.fee_type,
270 new_references.hist_start_dt) THEN
271 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275 Check_Constraints;
276 check_parent_existance; --for Bug 2175865
277 ELSIF (p_action = 'UPDATE') THEN
278 -- Call all the procedures related to Before Update.
279 Check_Constraints;
280 check_parent_existance; --for Bug 2175865
281 ELSIF (p_action = 'VALIDATE_INSERT') THEN
282 -- Call all the procedures related to Before Insert.
283 IF Get_PK_For_Validation ( new_references.fee_type,
284 new_references.hist_start_dt) THEN
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 END IF;
289 Check_Constraints;
290 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
291 Check_Constraints;
292 END IF;
293
294 END Before_DML;
295
296 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
297 procedure INSERT_ROW (
298 X_ROWID in out NOCOPY VARCHAR2,
299 X_FEE_TYPE in VARCHAR2,
300 X_HIST_START_DT in DATE,
301 X_HIST_END_DT in DATE,
302 X_HIST_WHO in NUMBER,
303 X_S_FEE_TYPE in VARCHAR2,
304 X_S_FEE_TRIGGER_CAT in VARCHAR2,
305 X_DESCRIPTION in VARCHAR2,
306 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
307 X_CLOSED_IND in VARCHAR2,
308 X_COMMENTS in VARCHAR2,
309 X_ORG_ID in NUMBER,
310 X_MODE in VARCHAR2 ,
311 X_FEE_CLASS IN VARCHAR2, --Bug 2175865
312 x_designated_payment_flag IN VARCHAR2
313 ) AS
314 /*-----------------------------------------------------------------------------
315 CHANGE HISTORY:
316 WHO WHEN WHAT
317 sapanigr 09-Mar-2006 Bug 3296531. Removed NVL clause in call to Before_DML for
318 columns S_FEE_TYPE, S_FEE_TRIGGER_CAT and OPTIONAL_PAYMENT_IND
319 -------------------------------------------------------------------------------*/
320 cursor C is select ROWID from IGS_FI_FEE_TYPE_HIST_ALL
321 where FEE_TYPE = X_FEE_TYPE
322 and HIST_START_DT = X_HIST_START_DT;
323 X_LAST_UPDATE_DATE DATE;
327 X_LAST_UPDATE_DATE := SYSDATE;
324 X_LAST_UPDATED_BY NUMBER;
325 X_LAST_UPDATE_LOGIN NUMBER;
326 begin
328 if(X_MODE = 'I') then
329 X_LAST_UPDATED_BY := 1;
330 X_LAST_UPDATE_LOGIN := 0;
331 elsif (X_MODE = 'R') then
332 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
333 if X_LAST_UPDATED_BY is NULL then
334 X_LAST_UPDATED_BY := -1;
335 end if;
336 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
337 if X_LAST_UPDATE_LOGIN is NULL then
338 X_LAST_UPDATE_LOGIN := -1;
339 end if;
340 else
341 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
342 IGS_GE_MSG_STACK.ADD;
343 app_exception.raise_exception;
344 end if;
345
346
347 Before_DML(
348 p_action=>'INSERT',
349 x_rowid=>X_ROWID,
350 x_closed_ind=>X_CLOSED_IND,
351 x_comments=>X_COMMENTS,
352 x_description=>X_DESCRIPTION,
353 x_fee_type=>X_FEE_TYPE,
354 x_hist_end_dt=>X_HIST_END_DT,
355 x_hist_start_dt=>X_HIST_START_DT,
356 x_hist_who=>X_HIST_WHO,
357 x_optional_payment_ind=>X_OPTIONAL_PAYMENT_IND,
358 x_s_fee_trigger_cat=>X_S_FEE_TRIGGER_CAT,
359 x_s_fee_type=>X_S_FEE_TYPE,
360 x_org_id => igs_ge_gen_003.get_org_id,
361 x_fee_class =>X_FEE_CLASS, --for bug 2175865
362 x_designated_payment_flag => x_designated_payment_flag,
363 x_creation_date=>X_LAST_UPDATE_DATE,
364 x_created_by=>X_LAST_UPDATED_BY,
365 x_last_update_date=>X_LAST_UPDATE_DATE,
366 x_last_updated_by=>X_LAST_UPDATED_BY,
367 x_last_update_login=>X_LAST_UPDATE_LOGIN
368 );
369
370 insert into IGS_FI_FEE_TYPE_HIST_ALL (
371 FEE_TYPE,
372 HIST_START_DT,
373 HIST_END_DT,
374 HIST_WHO,
375 S_FEE_TYPE,
376 S_FEE_TRIGGER_CAT,
377 DESCRIPTION,
378 OPTIONAL_PAYMENT_IND,
379 CLOSED_IND,
380 COMMENTS,
381 ORG_ID,
382 FEE_CLASS, --for Bug 2175865
383 designated_payment_flag,
384 CREATION_DATE,
385 CREATED_BY,
386 LAST_UPDATE_DATE,
387 LAST_UPDATED_BY,
388 LAST_UPDATE_LOGIN
389 ) values (
390 NEW_REFERENCES.FEE_TYPE,
391 NEW_REFERENCES.HIST_START_DT,
392 NEW_REFERENCES.HIST_END_DT,
393 NEW_REFERENCES.HIST_WHO,
394 NEW_REFERENCES.S_FEE_TYPE,
395 NEW_REFERENCES.S_FEE_TRIGGER_CAT,
396 NEW_REFERENCES.DESCRIPTION,
397 NEW_REFERENCES.OPTIONAL_PAYMENT_IND,
398 NEW_REFERENCES.CLOSED_IND,
399 NEW_REFERENCES.COMMENTS,
400 NEW_REFERENCES.ORG_ID,
401 NEW_REFERENCES.FEE_CLASS, --for Bug 2175865
402 new_references.designated_payment_flag,
403 X_LAST_UPDATE_DATE,
404 X_LAST_UPDATED_BY,
405 X_LAST_UPDATE_DATE,
406 X_LAST_UPDATED_BY,
407 X_LAST_UPDATE_LOGIN
408 );
409
410 open c;
411 fetch c into X_ROWID;
412 if (c%notfound) then
413 close c;
414 raise no_data_found;
415 end if;
416 close c;
417
418 end INSERT_ROW;
419
420 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
421 procedure LOCK_ROW (
422 X_ROWID in VARCHAR2,
423 X_FEE_TYPE in VARCHAR2,
424 X_HIST_START_DT in DATE,
425 X_HIST_END_DT in DATE,
426 X_HIST_WHO in NUMBER,
427 X_S_FEE_TYPE in VARCHAR2,
428 X_S_FEE_TRIGGER_CAT in VARCHAR2,
429 X_DESCRIPTION in VARCHAR2,
430 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
431 X_CLOSED_IND in VARCHAR2,
432 X_COMMENTS in VARCHAR2,
433 X_FEE_CLASS in VARCHAR2, --for Bug 2175865
434 x_designated_payment_flag IN VARCHAR2
435 ) AS
436 cursor c1 is select
437 HIST_END_DT,
438 HIST_WHO,
439 S_FEE_TYPE,
440 S_FEE_TRIGGER_CAT,
441 DESCRIPTION,
442 OPTIONAL_PAYMENT_IND,
443 CLOSED_IND,
444 COMMENTS,
445 FEE_CLASS, --for Bug 2175865
446 designated_payment_flag
447 from IGS_FI_FEE_TYPE_HIST_ALL
448 where ROWID = X_ROWID
449 for update nowait;
450 tlinfo c1%rowtype;
451
452 begin
453 open c1;
454 fetch c1 into tlinfo;
455 if (c1%notfound) then
456 close c1;
457 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
458 IGS_GE_MSG_STACK.ADD;
459 app_exception.raise_exception;
460 return;
461 end if;
462 close c1;
463
464 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
465 AND (tlinfo.HIST_WHO = X_HIST_WHO)
466 AND ((tlinfo.S_FEE_TYPE = X_S_FEE_TYPE)
467 OR ((tlinfo.S_FEE_TYPE is null)
468 AND (X_S_FEE_TYPE is null)))
469 AND ((tlinfo.S_FEE_TRIGGER_CAT = X_S_FEE_TRIGGER_CAT)
470 OR ((tlinfo.S_FEE_TRIGGER_CAT is null)
471 AND (X_S_FEE_TRIGGER_CAT is null)))
472 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
473 OR ((tlinfo.DESCRIPTION is null)
474 AND (X_DESCRIPTION is null)))
475 AND ((tlinfo.OPTIONAL_PAYMENT_IND = X_OPTIONAL_PAYMENT_IND)
476 OR ((tlinfo.OPTIONAL_PAYMENT_IND is null)
477 AND (X_OPTIONAL_PAYMENT_IND is null)))
478 AND ((tlinfo.CLOSED_IND = X_CLOSED_IND)
479 OR ((tlinfo.CLOSED_IND is null)
480 AND (X_CLOSED_IND is null)))
481 AND ((tlinfo.COMMENTS = X_COMMENTS)
485 AND ( (tlinfo.FEE_CLASS = X_FEE_CLASS) OR
482 OR ((tlinfo.COMMENTS is null)
483 AND (X_COMMENTS is null)))
484 --for Bug 2175865
486 ((tlinfo.FEE_CLASS IS NULL) AND (X_FEE_CLASS IS NULL)))
487 AND ( (tlinfo.designated_payment_flag = x_designated_payment_flag) OR
488 ((tlinfo.designated_payment_flag IS NULL) AND (x_designated_payment_flag IS NULL)))
489 ) then
490 null;
491 else
492 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
493 IGS_GE_MSG_STACK.ADD;
494 app_exception.raise_exception;
495 end if;
496 return;
497 end LOCK_ROW;
498
499 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
500 procedure UPDATE_ROW (
501 X_ROWID in VARCHAR2,
502 X_FEE_TYPE in VARCHAR2,
503 X_HIST_START_DT in DATE,
504 X_HIST_END_DT in DATE,
505 X_HIST_WHO in NUMBER,
506 X_S_FEE_TYPE in VARCHAR2,
507 X_S_FEE_TRIGGER_CAT in VARCHAR2,
508 X_DESCRIPTION in VARCHAR2,
509 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
510 X_CLOSED_IND in VARCHAR2,
511 X_COMMENTS in VARCHAR2,
512 X_MODE in VARCHAR2 ,
513 X_FEE_CLASS in VARCHAR2, --FOR BUG 2175865
514 x_designated_payment_flag IN VARCHAR2
515 ) AS
516 X_LAST_UPDATE_DATE DATE;
517 X_LAST_UPDATED_BY NUMBER;
518 X_LAST_UPDATE_LOGIN NUMBER;
519 begin
520 X_LAST_UPDATE_DATE := SYSDATE;
521 if(X_MODE = 'I') then
522 X_LAST_UPDATED_BY := 1;
523 X_LAST_UPDATE_LOGIN := 0;
524 elsif (X_MODE = 'R') then
525 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
526 if X_LAST_UPDATED_BY is NULL then
527 X_LAST_UPDATED_BY := -1;
528 end if;
529 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
530 if X_LAST_UPDATE_LOGIN is NULL then
531 X_LAST_UPDATE_LOGIN := -1;
532 end if;
533 else
534 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
535 IGS_GE_MSG_STACK.ADD;
536 app_exception.raise_exception;
537 end if;
538
539
540
541 Before_DML(
542 p_action=>'UPDATE',
543 x_rowid=>X_ROWID,
544 x_closed_ind=>X_CLOSED_IND,
545 x_comments=>X_COMMENTS,
546 x_description=>X_DESCRIPTION,
547 x_fee_type=>X_FEE_TYPE,
548 x_hist_end_dt=>X_HIST_END_DT,
549 x_hist_start_dt=>X_HIST_START_DT,
550 x_fee_class =>X_FEE_CLASS, --for Bug 2175865
551 x_hist_who=>X_HIST_WHO,
552 x_optional_payment_ind=>X_OPTIONAL_PAYMENT_IND,
553 x_s_fee_trigger_cat=>X_S_FEE_TRIGGER_CAT,
554 x_s_fee_type=>X_S_FEE_TYPE,
555 x_designated_payment_flag => x_designated_payment_flag,
556 x_creation_date=>X_LAST_UPDATE_DATE,
557 x_created_by=>X_LAST_UPDATED_BY,
558 x_last_update_date=>X_LAST_UPDATE_DATE,
559 x_last_updated_by=>X_LAST_UPDATED_BY,
560 x_last_update_login=>X_LAST_UPDATE_LOGIN
561 );
562
563
564 update IGS_FI_FEE_TYPE_HIST_ALL set
565 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
566 HIST_WHO = NEW_REFERENCES.HIST_WHO,
567 S_FEE_TYPE = NEW_REFERENCES.S_FEE_TYPE,
568 S_FEE_TRIGGER_CAT = NEW_REFERENCES.S_FEE_TRIGGER_CAT,
569 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
570 OPTIONAL_PAYMENT_IND = NEW_REFERENCES.OPTIONAL_PAYMENT_IND,
571 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
572 COMMENTS = NEW_REFERENCES.COMMENTS,
573 FEE_CLASS = NEW_REFERENCES.FEE_CLASS, --for Bug 2175865
574 designated_payment_flag = new_references.designated_payment_flag,
575 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
576 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
577 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
578 where ROWID = X_ROWID;
579 if (sql%notfound) then
580 raise no_data_found;
581 end if;
582
583 end UPDATE_ROW;
584
585 -- shtatiko 30-MAY-2003 Enh# 2831582, Added new column designated_payment_flag.
586 procedure ADD_ROW (
587 X_ROWID in out NOCOPY VARCHAR2,
588 X_FEE_TYPE in VARCHAR2,
589 X_HIST_START_DT in DATE,
590 X_HIST_END_DT in DATE,
591 X_HIST_WHO in NUMBER,
592 X_S_FEE_TYPE in VARCHAR2,
593 X_S_FEE_TRIGGER_CAT in VARCHAR2,
594 X_DESCRIPTION in VARCHAR2,
595 X_OPTIONAL_PAYMENT_IND in VARCHAR2,
596 X_CLOSED_IND in VARCHAR2,
597 X_COMMENTS in VARCHAR2,
598 X_ORG_ID in NUMBER,
599 X_MODE in VARCHAR2 ,
600 X_FEE_CLASS in VARCHAR2, --for bug 2175865
601 x_designated_payment_flag IN VARCHAR2
602 ) AS
603 cursor c1 is select rowid from IGS_FI_FEE_TYPE_HIST_ALL
604 where FEE_TYPE = X_FEE_TYPE
605 and HIST_START_DT = X_HIST_START_DT
606 ;
607 begin
608 open c1;
609 fetch c1 into X_ROWID;
610 if (c1%notfound) then
611 close c1;
612 INSERT_ROW (
613 X_ROWID,
614 X_FEE_TYPE,
615 X_HIST_START_DT,
616 X_HIST_END_DT,
617 X_HIST_WHO,
618 X_S_FEE_TYPE,
619 X_S_FEE_TRIGGER_CAT,
620 X_DESCRIPTION,
621 X_OPTIONAL_PAYMENT_IND,
622 X_CLOSED_IND,
623 X_COMMENTS,
624 X_ORG_ID,
625 X_MODE,
626 X_FEE_CLASS, --for Bug 2175865
627 x_designated_payment_flag);
628 return;
629 end if;
630 close c1;
631 UPDATE_ROW (
632 X_ROWID,
633 X_FEE_TYPE,
634 X_HIST_START_DT,
635 X_HIST_END_DT,
636 X_HIST_WHO,
637 X_S_FEE_TYPE,
638 X_S_FEE_TRIGGER_CAT,
639 X_DESCRIPTION,
640 X_OPTIONAL_PAYMENT_IND,
641 X_CLOSED_IND,
642 X_COMMENTS,
643 X_MODE,
644 X_FEE_CLASS, --for Bug 2175865
645 x_designated_payment_flag);
646 end ADD_ROW;
647
648 procedure DELETE_ROW (
649 X_ROWID in VARCHAR2
650 ) AS
651 begin
652 Before_DML (
653 p_action => 'DELETE',
654 x_rowid => X_ROWID
655 );
656 delete from IGS_FI_FEE_TYPE_HIST_ALL
657 where ROWID = X_ROWID;
658 if (sql%notfound) then
659 raise no_data_found;
660 end if;
661
662 end DELETE_ROW;
663
664 end IGS_FI_FEE_TYPE_HIST_PKG;