[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GRP_FEE_TRG_PKG
Source
1 package body IGS_PS_GRP_FEE_TRG_PKG AS
2 /* $Header: IGSPI16B.pls 115.7 2003/02/20 10:17:59 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_GRP_FEE_TRG%RowType;
6 new_references IGS_PS_GRP_FEE_TRG%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fee_cat IN VARCHAR2 DEFAULT NULL,
12 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_fee_type IN VARCHAR2 DEFAULT NULL,
15 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
16 x_create_dt IN DATE DEFAULT NULL,
17 x_logical_delete_dt IN DATE DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) AS
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_PS_GRP_FEE_TRG
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.fee_cat := x_fee_cat;
49 new_references.fee_cal_type := x_fee_cal_type;
50 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
51 new_references.fee_type := x_fee_type;
52 new_references.course_group_cd := x_course_group_cd;
53 new_references.create_dt := x_create_dt;
54 new_references.logical_delete_dt := x_logical_delete_dt;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65
66 END Set_Column_Values;
67
68 -- Trigger description :-
69 -- "OSS_TST".trg_cgft_br_iud
70 -- BEFORE INSERT OR DELETE OR UPDATE
71 -- ON IGS_PS_GRP_FEE_TRG
72 -- FOR EACH ROW
73
74 PROCEDURE BeforeRowInsertUpdateDelete1(
75 p_inserting IN BOOLEAN DEFAULT FALSE,
76 p_updating IN BOOLEAN DEFAULT FALSE,
77 p_deleting IN BOOLEAN DEFAULT FALSE
78 ) AS
79 v_message_name varchar2(30);
80 BEGIN
81 IF p_inserting THEN
82 -- Validate trigger can be defined.
83 IF IGS_FI_VAL_CGFT.finp_val_cgft_ins (
84 new_references.fee_type,
85 v_message_name) = FALSE THEN
86 Fnd_Message.Set_Name('IGS',v_message_name);
87 IGS_GE_MSG_STACK.ADD;
88 App_Exception.Raise_Exception;
89 END IF;
90 END IF;
91 IF p_inserting OR p_updating THEN
92 -- Validate IGS_PS_COURSE Group not closed.
93 IF IGS_EN_VAL_PCGE.enrp_val_crs_gp_clsd (
94 new_references.course_group_cd,
95 v_message_name) = FALSE THEN
96 Fnd_Message.Set_Name('IGS',v_message_name);
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99 END IF;
100 END IF;
101
102
103 END BeforeRowInsertUpdateDelete1;
104
105 -- Trigger description :-
106 -- "OSS_TST".trg_cgft_ar_iu
107 -- AFTER INSERT OR UPDATE
108 -- ON IGS_PS_GRP_FEE_TRG
109 -- FOR EACH ROW
110
111 PROCEDURE AfterRowInsertUpdate2(
112 p_inserting IN BOOLEAN DEFAULT FALSE,
113 p_updating IN BOOLEAN DEFAULT FALSE,
114 p_deleting IN BOOLEAN DEFAULT FALSE
115 ) AS
116 v_message_name varchar2(30);
117 v_rowid_saved BOOLEAN := FALSE;
118 BEGIN
119 -- Validate for open IGS_PS_COURSE Group Fee Trig records.
120 IF p_inserting OR
121 (new_references.logical_delete_dt IS NULL) THEN
122 -- Save the rowid of the current row.
123 -- Validate for open ended IGS_PS_GRP_FEE_TRG records.
124 IF new_references.logical_delete_dt IS NULL THEN
125 IF IGS_FI_VAL_CGFT.finp_val_cgft_open(new_references.fee_cat,
126 new_references.fee_cal_type,
127 new_references.fee_ci_sequence_number,
128 new_references.fee_type,
129 new_references.course_group_cd,
130 new_references.create_dt,
131 v_message_name) = FALSE THEN
132 Fnd_Message.Set_Name('IGS',v_message_name);
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception;
135 END IF;
136 END IF;
137 v_rowid_saved := TRUE;
138 END IF;
139
140
141 END AfterRowInsertUpdate2;
142
143 PROCEDURE Check_Constraints (
144 Column_Name IN VARCHAR2 DEFAULT NULL,
145 Column_Value IN VARCHAR2 DEFAULT NULL
146 ) AS
147 /*----------------------------------------------------------------------------
148 || Created By :
149 || Created On :
150 || Purpose :
151 || Known limitations, enhancements or remarks :
152 || Change History :
153 || Who When What
154 || (reverse chronological order - newest change first)
155 || vvutukur 19-May-2002 removed upper check constraint on fee_cat,fee_type columns.bug#2344826.
156 ----------------------------------------------------------------------------*/
157 BEGIN
158
159 IF column_name is null then
160 NULL;
161 ELSIF upper(Column_name) = 'COURSE_GROUP_CD' then
162 new_references.course_group_cd := column_value;
163 ELSIF upper(Column_name) = 'FEE_CAL_TYPE' then
164 new_references.fee_cal_type := column_value;
165 ELSIF upper(Column_name) = 'FEE_CI_SEQUENCE_NUMBER' then
166 new_references.fee_ci_sequence_number := IGS_GE_NUMBER.TO_NUM(column_value);
167 END IF;
168
169 IF upper(column_name) = 'COURSE_GROUP_CD' OR
170 column_name is null Then
171 IF ( new_references.course_group_cd <> UPPER(new_references.course_group_cd) ) Then
172 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 IF upper(column_name) = 'FEE_CAL_TYPE' OR
179 column_name is null Then
180 IF ( new_references.fee_cal_type <> UPPER(new_references.fee_cal_type) ) Then
181 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185 END IF;
186
187 IF upper(column_name) = 'FEE_CI_SEQUENCE_NUMBER' OR
188 column_name is null Then
189 IF ( new_references.fee_ci_sequence_number < 1 OR new_references.fee_ci_sequence_number > 999999 ) Then
190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END IF;
195
196 END Check_Constraints;
197
198 PROCEDURE Check_Parent_Existance AS
199 BEGIN
200
201 IF (((old_references.course_group_cd = new_references.course_group_cd)) OR
202 ((new_references.course_group_cd IS NULL))) THEN
203 NULL;
204 ELSE
205 IF NOT IGS_PS_GRP_PKG.Get_PK_For_Validation (
206 new_references.course_group_cd
207 ) THEN
208 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214 IF (((old_references.fee_cat = new_references.fee_cat) AND
215 (old_references.fee_cal_type = new_references.fee_cal_type) AND
216 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
217 (old_references.fee_type = new_references.fee_type)) OR
218 ((new_references.fee_cat IS NULL) OR
219 (new_references.fee_cal_type IS NULL) OR
220 (new_references.fee_ci_sequence_number IS NULL) OR
221 (new_references.fee_type IS NULL))) THEN
222 NULL;
223 ELSE
224 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
225 new_references.fee_cat,
226 new_references.fee_cal_type,
227 new_references.fee_ci_sequence_number,
228 new_references.fee_type
229 ) THEN
230 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
231 IGS_GE_MSG_STACK.ADD;
232 App_Exception.Raise_Exception;
233 END IF;
234 END IF;
235
236 END Check_Parent_Existance;
237
238 FUNCTION Get_PK_For_Validation (
239 x_fee_cat IN VARCHAR2,
240 x_fee_cal_type IN VARCHAR2,
241 x_fee_ci_sequence_number IN NUMBER,
242 x_fee_type IN VARCHAR2,
243 x_course_group_cd IN VARCHAR2,
244 x_create_dt IN DATE
245 )
246 RETURN BOOLEAN AS
247
248 CURSOR cur_rowid IS
249 SELECT rowid
250 FROM IGS_PS_GRP_FEE_TRG
251 WHERE fee_cat = x_fee_cat
252 AND fee_cal_type = x_fee_cal_type
253 AND fee_ci_sequence_number = x_fee_ci_sequence_number
254 AND fee_type = x_fee_type
255 AND course_group_cd = x_course_group_cd
256 AND create_dt = x_create_dt
257 FOR UPDATE NOWAIT;
258
259 lv_rowid cur_rowid%RowType;
260
261 BEGIN
262
263 Open cur_rowid;
264 Fetch cur_rowid INTO lv_rowid;
265 IF (cur_rowid%FOUND) THEN
266 Close cur_rowid;
267 Return (TRUE);
268 ELSE
269 Close cur_rowid;
270 Return (FALSE);
271 END IF;
272
273 END Get_PK_For_Validation;
274
275 PROCEDURE Before_DML (
276 p_action IN VARCHAR2,
277 x_rowid IN VARCHAR2 DEFAULT NULL,
278 x_fee_cat IN VARCHAR2 DEFAULT NULL,
279 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
280 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
281 x_fee_type IN VARCHAR2 DEFAULT NULL,
282 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
283 x_create_dt IN DATE DEFAULT NULL,
284 x_logical_delete_dt IN DATE DEFAULT NULL,
285 x_creation_date IN DATE DEFAULT NULL,
286 x_created_by IN NUMBER DEFAULT NULL,
287 x_last_update_date IN DATE DEFAULT NULL,
288 x_last_updated_by IN NUMBER DEFAULT NULL,
289 x_last_update_login IN NUMBER DEFAULT NULL
290 ) AS
291 BEGIN
292
293 Set_Column_Values (
294 p_action,
295 x_rowid,
296 x_fee_cat,
297 x_fee_cal_type,
298 x_fee_ci_sequence_number,
299 x_fee_type,
300 x_course_group_cd,
301 x_create_dt,
302 x_logical_delete_dt,
303 x_creation_date,
304 x_created_by,
305 x_last_update_date,
306 x_last_updated_by,
307 x_last_update_login
308 );
309
310 IF (p_action = 'INSERT') THEN
311 -- Call all the procedures related to Before Insert.
312 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
313 IF Get_PK_For_Validation (
314 new_references.fee_cat,
315 new_references.fee_cal_type,
316 new_references.fee_ci_sequence_number,
317 new_references.fee_type,
318 new_references.course_group_cd,
319 new_references.create_dt) THEN
320 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END IF;
324 Check_Constraints;
325 Check_Parent_Existance;
326 ELSIF (p_action = 'UPDATE') THEN
327 -- Call all the procedures related to Before Update.
328 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
329 Check_Constraints;
330 Check_Parent_Existance;
331 ELSIF (p_action = 'DELETE') THEN
332 -- Call all the procedures related to Before Delete.
333 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
334 ELSIF (p_action = 'VALIDATE_INSERT') THEN
335 IF Get_PK_For_Validation (
336 new_references.fee_cat,
337 new_references.fee_cal_type,
338 new_references.fee_ci_sequence_number,
339 new_references.fee_type,
340 new_references.course_group_cd,
341 new_references.create_dt) THEN
342 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 Check_Constraints;
347 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
348 Check_Constraints;
349 END IF;
350
351 END Before_DML;
352
353 PROCEDURE After_DML (
354 p_action IN VARCHAR2,
355 x_rowid IN VARCHAR2
356 ) AS
357 BEGIN
358
359 l_rowid := x_rowid;
360
361
362 END After_DML;
363
364 procedure INSERT_ROW (
365 X_ROWID in out NOCOPY VARCHAR2,
366 X_FEE_CAT in VARCHAR2,
367 X_CREATE_DT in out NOCOPY DATE,
368 X_COURSE_GROUP_CD in VARCHAR2,
369 X_FEE_CAL_TYPE in VARCHAR2,
370 X_FEE_TYPE in VARCHAR2,
371 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
372 X_LOGICAL_DELETE_DT in DATE,
373 X_MODE in VARCHAR2 default 'R'
374 ) AS
375 cursor C is select ROWID from IGS_PS_GRP_FEE_TRG
376 where FEE_CAT = X_FEE_CAT
377 and CREATE_DT = NEW_REFERENCES.CREATE_DT
378 and COURSE_GROUP_CD = X_COURSE_GROUP_CD
379 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
380 and FEE_TYPE = X_FEE_TYPE
381 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER;
382 X_LAST_UPDATE_DATE DATE;
383 X_LAST_UPDATED_BY NUMBER;
384 X_LAST_UPDATE_LOGIN NUMBER;
385 X_REQUEST_ID NUMBER;
386 X_PROGRAM_ID NUMBER;
387 X_PROGRAM_APPLICATION_ID NUMBER;
388 X_PROGRAM_UPDATE_DATE DATE;
389
390 begin
391 X_LAST_UPDATE_DATE := SYSDATE;
392 if(X_MODE = 'I') then
393 X_LAST_UPDATED_BY := 1;
394 X_LAST_UPDATE_LOGIN := 0;
395 elsif (X_MODE = 'R') then
396 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
397 if X_LAST_UPDATED_BY is NULL then
398 X_LAST_UPDATED_BY := -1;
399 end if;
400 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
401 if X_LAST_UPDATE_LOGIN is NULL then
402 X_LAST_UPDATE_LOGIN := -1;
403 end if;
404
405 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
406 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
407 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
408 IF (X_REQUEST_ID = -1) THEN
409 X_REQUEST_ID := NULL;
410 X_PROGRAM_ID := NULL;
411 X_PROGRAM_APPLICATION_ID := NULL;
412 X_PROGRAM_UPDATE_DATE := NULL;
413 ELSE
414 X_PROGRAM_UPDATE_DATE := SYSDATE;
415 END IF;
416
417 else
418 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
419 IGS_GE_MSG_STACK.ADD;
420 app_exception.raise_exception;
421 end if;
422
423 Before_DML (
424 p_action => 'INSERT',
425 x_rowid => X_ROWID,
426 x_fee_cat => X_FEE_CAT,
427 x_fee_cal_type => X_FEE_CAL_TYPE,
428 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
429 x_fee_type => X_FEE_TYPE,
430 x_course_group_cd => X_COURSE_GROUP_CD,
431 x_create_dt => NVL(X_CREATE_DT,SYSDATE),
432 x_logical_delete_dt => X_LOGICAL_DELETE_DT ,
433 x_creation_date => X_LAST_UPDATE_DATE ,
434 x_created_by => X_LAST_UPDATED_BY ,
435 x_last_update_date => X_LAST_UPDATE_DATE ,
436 x_last_updated_by => X_LAST_UPDATED_BY ,
437 x_last_update_login => X_LAST_UPDATE_LOGIN
438 );
439
440 insert into IGS_PS_GRP_FEE_TRG (
441 FEE_CAT,
442 FEE_CAL_TYPE,
443 FEE_CI_SEQUENCE_NUMBER,
444 FEE_TYPE,
445 COURSE_GROUP_CD,
446 CREATE_DT,
447 LOGICAL_DELETE_DT,
448 CREATION_DATE,
449 CREATED_BY,
450 LAST_UPDATE_DATE,
451 LAST_UPDATED_BY,
452 LAST_UPDATE_LOGIN,
453 REQUEST_ID,
454 PROGRAM_ID,
455 PROGRAM_APPLICATION_ID,
456 PROGRAM_UPDATE_DATE
457 ) values (
458 NEW_REFERENCES.FEE_CAT,
459 NEW_REFERENCES.FEE_CAL_TYPE,
460 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
461 NEW_REFERENCES.FEE_TYPE,
462 NEW_REFERENCES.COURSE_GROUP_CD,
463 NEW_REFERENCES.CREATE_DT,
464 NEW_REFERENCES.LOGICAL_DELETE_DT,
465 X_LAST_UPDATE_DATE,
466 X_LAST_UPDATED_BY,
467 X_LAST_UPDATE_DATE,
468 X_LAST_UPDATED_BY,
469 X_LAST_UPDATE_LOGIN,
470 X_REQUEST_ID,
471 X_PROGRAM_ID,
472 X_PROGRAM_APPLICATION_ID,
473 X_PROGRAM_UPDATE_DATE
474 );
475
476 open c;
477 fetch c into X_ROWID;
478 if (c%notfound) then
479 close c;
480 raise no_data_found;
481 end if;
482 After_DML (
483 p_action => 'INSERT',
484 x_rowid => X_ROWID
485 );
486
487 close c;
488
489 end INSERT_ROW;
490
491 procedure LOCK_ROW (
492 X_ROWID IN VARCHAR2,
493 X_FEE_CAT in VARCHAR2,
494 X_CREATE_DT in DATE,
495 X_COURSE_GROUP_CD in VARCHAR2,
496 X_FEE_CAL_TYPE in VARCHAR2,
497 X_FEE_TYPE in VARCHAR2,
498 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
499 X_LOGICAL_DELETE_DT in DATE
500 ) AS
501 cursor c1 is select
502 LOGICAL_DELETE_DT
503 from IGS_PS_GRP_FEE_TRG
504 where ROWID = X_ROWID
505 for update nowait;
506 tlinfo c1%rowtype;
507
508 begin
509 open c1;
510 fetch c1 into tlinfo;
511 if (c1%notfound) then
512 close c1;
513 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
514 IGS_GE_MSG_STACK.ADD;
515 app_exception.raise_exception;
516 return;
517 end if;
518 close c1;
519
520 if ( ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
521 OR ((tlinfo.LOGICAL_DELETE_DT is null)
522 AND (X_LOGICAL_DELETE_DT is null)))
523 ) then
524 null;
525 else
526 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
527 IGS_GE_MSG_STACK.ADD;
528 app_exception.raise_exception;
529 end if;
530 return;
531 end LOCK_ROW;
532
533 procedure UPDATE_ROW (
534 X_ROWID IN VARCHAR2,
535 X_FEE_CAT in VARCHAR2,
536 X_CREATE_DT in DATE,
537 X_COURSE_GROUP_CD in VARCHAR2,
538 X_FEE_CAL_TYPE in VARCHAR2,
539 X_FEE_TYPE in VARCHAR2,
540 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
541 X_LOGICAL_DELETE_DT in DATE,
542 X_MODE in VARCHAR2 default 'R'
543 ) AS
544 X_LAST_UPDATE_DATE DATE;
545 X_LAST_UPDATED_BY NUMBER;
546 X_LAST_UPDATE_LOGIN NUMBER;
547 X_REQUEST_ID NUMBER;
548 X_PROGRAM_ID NUMBER;
549 X_PROGRAM_APPLICATION_ID NUMBER;
550 X_PROGRAM_UPDATE_DATE DATE;
551
552 begin
553 X_LAST_UPDATE_DATE := SYSDATE;
554 if(X_MODE = 'I') then
555 X_LAST_UPDATED_BY := 1;
556 X_LAST_UPDATE_LOGIN := 0;
557 elsif (X_MODE = 'R') then
558 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
559 if X_LAST_UPDATED_BY is NULL then
560 X_LAST_UPDATED_BY := -1;
561 end if;
562 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
563 if X_LAST_UPDATE_LOGIN is NULL then
564 X_LAST_UPDATE_LOGIN := -1;
565 end if;
566 else
567 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
568 IGS_GE_MSG_STACK.ADD;
569 app_exception.raise_exception;
570 end if;
571
572 Before_DML (
573 p_action => 'UPDATE',
574 x_rowid => X_ROWID,
575 x_fee_cat => X_FEE_CAT,
576 x_fee_cal_type => X_FEE_CAL_TYPE,
577 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
578 x_fee_type => X_FEE_TYPE,
579 x_course_group_cd => X_COURSE_GROUP_CD,
580 x_create_dt => X_CREATE_DT,
581 x_logical_delete_dt => X_LOGICAL_DELETE_DT ,
582 x_creation_date => X_LAST_UPDATE_DATE ,
583 x_created_by => X_LAST_UPDATED_BY ,
584 x_last_update_date => X_LAST_UPDATE_DATE ,
585 x_last_updated_by => X_LAST_UPDATED_BY ,
586 x_last_update_login => X_LAST_UPDATE_LOGIN
587 );
588
589 if (X_MODE = 'R') then
590 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
591 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
592 X_PROGRAM_APPLICATION_ID :=
593 FND_GLOBAL.PROG_APPL_ID;
594 if (X_REQUEST_ID = -1) then
595 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
596 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
597 X_PROGRAM_APPLICATION_ID :=
598 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
599 X_PROGRAM_UPDATE_DATE :=
600 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
601 else
602 X_PROGRAM_UPDATE_DATE := SYSDATE;
603 end if;
604 end if;
605
606 update IGS_PS_GRP_FEE_TRG set
607 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
608 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
609 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
610 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
611 REQUEST_ID = X_REQUEST_ID,
612 PROGRAM_ID = X_PROGRAM_ID,
613 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
614 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
615 where ROWID = X_ROWID
616 ;
617 if (sql%notfound) then
618 raise no_data_found;
619 end if;
620 After_DML (
621 p_action => 'UPDATE',
622 x_rowid => X_ROWID
623 );
624 end UPDATE_ROW;
625
626 procedure ADD_ROW (
627 X_ROWID in out NOCOPY VARCHAR2,
628 X_FEE_CAT in VARCHAR2,
629 X_CREATE_DT in out NOCOPY DATE,
630 X_COURSE_GROUP_CD in VARCHAR2,
631 X_FEE_CAL_TYPE in VARCHAR2,
632 X_FEE_TYPE in VARCHAR2,
633 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
634 X_LOGICAL_DELETE_DT in DATE,
635 X_MODE in VARCHAR2 default 'R'
636 ) AS
637 cursor c1 is select rowid from IGS_PS_GRP_FEE_TRG
638 where FEE_CAT = X_FEE_CAT
639 and CREATE_DT = NVL(X_CREATE_DT, SYSDATE)
640 and COURSE_GROUP_CD = X_COURSE_GROUP_CD
641 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
642 and FEE_TYPE = X_FEE_TYPE
643 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
644 ;
645 begin
646 open c1;
647 fetch c1 into X_ROWID;
648 if (c1%notfound) then
649 close c1;
650 INSERT_ROW (
651 X_ROWID,
652 X_FEE_CAT,
653 X_CREATE_DT,
654 X_COURSE_GROUP_CD,
655 X_FEE_CAL_TYPE,
656 X_FEE_TYPE,
657 X_FEE_CI_SEQUENCE_NUMBER,
658 X_LOGICAL_DELETE_DT,
659 X_MODE);
660 return;
661 end if;
662 close c1;
663 UPDATE_ROW (
664 X_ROWID,
665 X_FEE_CAT,
666 X_CREATE_DT,
667 X_COURSE_GROUP_CD,
668 X_FEE_CAL_TYPE,
669 X_FEE_TYPE,
670 X_FEE_CI_SEQUENCE_NUMBER,
671 X_LOGICAL_DELETE_DT,
672 X_MODE);
673 end ADD_ROW;
674
675 procedure DELETE_ROW (
676 X_ROWID in VARCHAR2
677 ) AS
678 begin
679 Before_DML (
680 p_action => 'DELETE',
681 x_rowid => X_ROWID
682 );
683 delete from IGS_PS_GRP_FEE_TRG
684 where ROWID = X_ROWID;
685 if (sql%notfound) then
686 raise no_data_found;
687 end if;
688 After_DML (
689 p_action => 'DELETE',
690 x_rowid => X_ROWID
691 );
692 end DELETE_ROW;
693
694 end IGS_PS_GRP_FEE_TRG_PKG;