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