[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FEE_TRG_HIST_PKG
Source
1 package body IGS_PS_FEE_TRG_HIST_PKG AS
2 /* $Header: IGSPI12B.pls 115.9 2003/02/12 09:49:00 shtatiko ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PS_FEE_TRG_HIST_ALL%RowType;
7 new_references IGS_PS_FEE_TRG_HIST_ALL%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_cd IN VARCHAR2 DEFAULT NULL,
17 x_sequence_number IN NUMBER DEFAULT NULL,
18 x_hist_start_dt IN DATE DEFAULT NULL,
19 x_hist_end_dt IN DATE DEFAULT NULL,
20 x_hist_who IN NUMBER DEFAULT NULL,
21 x_version_number IN NUMBER DEFAULT NULL,
22 x_cal_type IN VARCHAR2 DEFAULT NULL,
23 x_location_cd IN VARCHAR2 DEFAULT NULL,
24 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
25 x_attendance_type IN VARCHAR2 DEFAULT NULL,
26 x_create_dt IN DATE DEFAULT NULL,
27 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL,
33 x_org_id IN NUMBER DEFAULT NULL
34 ) AS
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_PS_FEE_TRG_HIST_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 Open cur_old_ref_values;
48 Fetch cur_old_ref_values INTO old_references;
49 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
50 Close cur_old_ref_values;
51 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
52 IGS_GE_MSG_STACK.ADD;
53 App_Exception.Raise_Exception;
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.fee_cat := x_fee_cat;
60 new_references.fee_cal_type := x_fee_cal_type;
61 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
62 new_references.fee_type := x_fee_type;
63 new_references.course_cd := x_course_cd;
64 new_references.sequence_number := x_sequence_number;
65 new_references.hist_start_dt := x_hist_start_dt;
66 new_references.hist_end_dt := x_hist_end_dt;
67 new_references.hist_who := x_hist_who;
68 new_references.version_number := x_version_number;
69 new_references.cal_type := x_cal_type;
70 new_references.location_cd := x_location_cd;
71 new_references.attendance_mode := x_attendance_mode;
72 new_references.attendance_type := x_attendance_type;
73 new_references.create_dt := x_create_dt;
74 new_references.fee_trigger_group_number := x_fee_trigger_group_number;
75 new_references.org_id := x_org_id;
76 IF (p_action = 'UPDATE') THEN
77 new_references.creation_date := old_references.creation_date;
78 new_references.created_by := old_references.created_by;
79 ELSE
80 new_references.creation_date := x_creation_date;
81 new_references.created_by := x_created_by;
82 END IF;
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END Set_Column_Values;
88
89 PROCEDURE Check_Constraints (
90 Column_Name IN VARCHAR2 DEFAULT NULL,
91 Column_Value IN VARCHAR2 DEFAULT NULL
92 ) AS
93 /*----------------------------------------------------------------------------
94 || Created By :
95 || Created On :
96 || Purpose :
97 || Known limitations, enhancements or remarks :
98 || Change History :
99 || Who When What
100 || (reverse chronological order - newest change first)
101 || vvutukur 19-May-2002 removed upper check constraint on fee_cat,fee_type columns.bug#2344826.
102 ----------------------------------------------------------------------------*/
103
104 BEGIN
105
106 IF column_name is null then
107 NULL;
108 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
109 new_references.attendance_mode := column_value;
110 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
111 new_references.attendance_type := column_value;
112 ELSIF upper(Column_name) = 'CAL_TYPE' then
113 new_references.cal_type := column_value;
114 ELSIF upper(Column_name) = 'COURSE_CD' then
115 new_references.course_cd := column_value;
116 ELSIF upper(Column_name) = 'FEE_CAL_TYPE' then
117 new_references.fee_cal_type := column_value;
118 ELSIF upper(Column_name) = 'LOCATION_CD' then
119 new_references.location_cd := column_value;
120 ELSIF upper(Column_name) = 'FEE_TRIGGER_GROUP_NUMBER' then
121 new_references.fee_trigger_group_number := igs_ge_number.to_num(column_value);
122 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
123 new_references.sequence_number := igs_ge_number.to_num(column_value);
124 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
125 new_references.version_number := igs_ge_number.to_num(column_value);
126 ELSIF upper(Column_name) = 'FEE_CI_SEQUENCE_NUMBER' then
127 new_references.fee_ci_sequence_number := igs_ge_number.to_num(column_value);
128 END IF;
129
130 IF upper(column_name) = 'ATTENDANCE_MODE' OR
131 column_name is null Then
132 IF ( new_references.attendance_mode <> UPPER(new_references.attendance_mode) ) Then
133 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138
139 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
140 column_name is null Then
141 IF ( new_references.attendance_type <> UPPER(new_references.attendance_type) ) 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
148 IF upper(column_name) = 'CAL_TYPE' OR
149 column_name is null Then
150 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
151 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152 IGS_GE_MSG_STACK.ADD;
153 App_Exception.Raise_Exception;
154 END IF;
155 END IF;
156
157 IF upper(column_name) = 'COURSE_CD' OR
158 column_name is null Then
159 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
160 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END IF;
164 END IF;
165
166 IF upper(column_name) = 'FEE_CAL_TYPE' OR
167 column_name is null Then
168 IF ( new_references.fee_cal_type <> UPPER(new_references.fee_cal_type) ) 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
175 IF upper(column_name) = 'LOCATION_CD' OR
176 column_name is null Then
177 IF ( new_references.location_cd <> UPPER(new_references.location_cd) ) Then
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183
184 IF upper(column_name) = 'FEE_CI_SEQUENCE_NUMBER' OR
185 column_name is null Then
186 IF ( new_references.fee_ci_sequence_number < 1 OR new_references.fee_ci_sequence_number > 999999 ) Then
187 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
194 column_name is null Then
195 IF ( new_references.sequence_number < 1 OR new_references.sequence_number > 999999 ) Then
196 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
197 IGS_GE_MSG_STACK.ADD;
198 App_Exception.Raise_Exception;
199 END IF;
200 END IF;
201
202 IF upper(column_name) = 'VERSION_NUMBER' OR
203 column_name is null Then
204 IF ( new_references.version_number < 1 OR new_references.version_number > 999 ) Then
205 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 END IF;
209 END IF;
210
211 IF upper(column_name) = 'FEE_TRIGGER_GROUP_NUMBER' OR
212 column_name is null Then
213 IF ( new_references.fee_trigger_group_number < 1 OR new_references.fee_trigger_group_number > 999999 ) Then
214 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 END IF;
219
220 END Check_Constraints;
221
222 PROCEDURE Check_Uniqueness AS
223 BEGIN
224
225 IF Get_UK_For_Validation (
226 new_references.fee_cat ,
227 new_references.fee_cal_type,
228 new_references.fee_ci_sequence_number,
229 new_references.fee_type,
230 new_references.course_cd,
231 new_references.VERSION_NUMBER,
232 new_references.CAL_TYPE,
233 new_references.LOCATION_CD,
234 new_references.ATTENDANCE_MODE,
235 new_references.ATTENDANCE_TYPE,
236 new_references.CREATE_DT,
237 new_references.hist_start_dt) THEN
238 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
239 IGS_GE_MSG_STACK.ADD;
240 App_Exception.Raise_Exception;
241 END IF;
242 END Check_Uniqueness ;
243
244 PROCEDURE Check_Parent_Existance AS
245 BEGIN
246
247 IF (((old_references.fee_cat = new_references.fee_cat) AND
248 (old_references.fee_cal_type = new_references.fee_cal_type) AND
249 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
250 (old_references.fee_type = new_references.fee_type)) OR
251 ((new_references.fee_cat IS NULL) OR
252 (new_references.fee_cal_type IS NULL) OR
253 (new_references.fee_ci_sequence_number IS NULL) OR
254 (new_references.fee_type IS NULL))) THEN
255 NULL;
256 ELSE
257 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
258 new_references.fee_cat,
259 new_references.fee_cal_type,
260 new_references.fee_ci_sequence_number,
261 new_references.fee_type ) THEN
262 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IF;
266 END IF;
267
268 END Check_Parent_Existance;
269
270 FUNCTION Get_PK_For_Validation (
271 x_fee_cat IN VARCHAR2,
272 x_fee_cal_type IN VARCHAR2,
273 x_fee_ci_sequence_number IN NUMBER,
274 x_fee_type IN VARCHAR2,
275 x_course_cd IN VARCHAR2,
276 x_sequence_number IN NUMBER,
277 x_hist_start_dt IN DATE
278 )
279 RETURN BOOLEAN AS
280
281 CURSOR cur_rowid IS
282 SELECT rowid
283 FROM IGS_PS_FEE_TRG_HIST_ALL
284 WHERE fee_cat = x_fee_cat
285 AND fee_cal_type = x_fee_cal_type
286 AND fee_ci_sequence_number = x_fee_ci_sequence_number
287 AND fee_type = x_fee_type
288 AND course_cd = x_course_cd
289 AND sequence_number = x_sequence_number
290 AND hist_start_dt = x_hist_start_dt
291 FOR UPDATE NOWAIT;
292
293 lv_rowid cur_rowid%RowType;
294
295 BEGIN
296
297 Open cur_rowid;
298 Fetch cur_rowid INTO lv_rowid;
299 IF (cur_rowid%FOUND) THEN
300 Close cur_rowid;
301 Return (TRUE);
302 ELSE
303 Close cur_rowid;
304 Return (FALSE);
305 END IF;
306
307 END Get_PK_For_Validation;
308
309 FUNCTION Get_UK_For_Validation (
310 x_fee_cat IN VARCHAR2,
311 x_fee_cal_type IN VARCHAR2,
312 x_fee_ci_sequence_number IN NUMBER,
313 x_fee_type IN VARCHAR2,
314 x_course_cd IN VARCHAR2,
315 X_VERSION_NUMBER in NUMBER,
316 X_CAL_TYPE in VARCHAR2,
317 X_LOCATION_CD in VARCHAR2,
318 X_ATTENDANCE_MODE in VARCHAR2,
319 X_ATTENDANCE_TYPE in VARCHAR2,
320 X_CREATE_DT in DATE,
321 x_hist_start_dt IN DATE )
322 RETURN BOOLEAN AS
323
324 CURSOR cur_rowid IS
325 SELECT rowid
326 FROM IGS_PS_FEE_TRG_HIST_ALL
327 WHERE fee_cat = new_references.fee_cat
328 AND fee_cal_type = new_references.fee_cal_type
329 AND fee_ci_sequence_number = new_references.fee_ci_sequence_number
330 AND fee_type = new_references.fee_type
331 AND course_cd = new_references.course_cd
332 AND version_number = new_references.version_number
333 AND cal_type = new_references.cal_type
334 AND location_cd = new_references.location_cd
335 AND attendance_mode = new_references.attendance_mode
336 AND attendance_type = new_references.attendance_type
337 AND create_dt = new_references.create_dt
338 AND hist_start_dt = new_references.hist_start_dt
339 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
340 FOR UPDATE NOWAIT;
341
342 lv_rowid cur_rowid%RowType;
343
344 BEGIN
345 Open cur_rowid;
346 Fetch cur_rowid INTO lv_rowid;
347 IF (cur_rowid%FOUND) THEN
348 Close cur_rowid;
349 Return (TRUE);
350 ELSE
351 Close cur_rowid;
352 Return (FALSE);
353 END IF;
354
355 END Get_UK_For_Validation;
356
357 PROCEDURE Before_DML (
358 p_action IN VARCHAR2,
359 x_rowid IN VARCHAR2 DEFAULT NULL,
360 x_fee_cat IN VARCHAR2 DEFAULT NULL,
361 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
362 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
363 x_fee_type IN VARCHAR2 DEFAULT NULL,
364 x_course_cd IN VARCHAR2 DEFAULT NULL,
365 x_sequence_number IN NUMBER DEFAULT NULL,
366 x_hist_start_dt IN DATE DEFAULT NULL,
367 x_hist_end_dt IN DATE DEFAULT NULL,
368 x_hist_who IN NUMBER DEFAULT NULL,
369 x_version_number IN NUMBER DEFAULT NULL,
370 x_cal_type IN VARCHAR2 DEFAULT NULL,
371 x_location_cd IN VARCHAR2 DEFAULT NULL,
372 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
373 x_attendance_type IN VARCHAR2 DEFAULT NULL,
374 x_create_dt IN DATE DEFAULT NULL,
375 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
376 x_creation_date IN DATE DEFAULT NULL,
377 x_created_by IN NUMBER DEFAULT NULL,
378 x_last_update_date IN DATE DEFAULT NULL,
379 x_last_updated_by IN NUMBER DEFAULT NULL,
380 x_last_update_login IN NUMBER DEFAULT NULL,
381 x_org_id IN NUMBER DEFAULT NULL
382 ) AS
383 BEGIN
384
385 Set_Column_Values (
386 p_action,
387 x_rowid,
388 x_fee_cat,
389 x_fee_cal_type,
390 x_fee_ci_sequence_number,
391 x_fee_type,
392 x_course_cd,
393 x_sequence_number,
394 x_hist_start_dt,
395 x_hist_end_dt,
396 x_hist_who,
397 x_version_number,
398 x_cal_type,
399 x_location_cd,
400 x_attendance_mode,
401 x_attendance_type,
402 x_create_dt,
403 x_fee_trigger_group_number,
404 x_creation_date,
405 x_created_by,
406 x_last_update_date,
407 x_last_updated_by,
408 x_last_update_login,
409 x_org_id
410 );
411
412 IF (p_action = 'INSERT') THEN
413 -- Call all the procedures related to Before Insert.
414 IF Get_PK_For_Validation (
415 new_references.fee_cat,
416 new_references.fee_cal_type ,
417 new_references.fee_ci_sequence_number,
418 new_references.fee_type,
419 new_references.course_cd,
420 new_references.sequence_number,
421 new_references.hist_start_dt) THEN
422 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
423 IGS_GE_MSG_STACK.ADD;
424 App_Exception.Raise_Exception;
425 END IF;
426 Check_Constraints;
427 Check_Parent_Existance;
428 Check_Uniqueness;
429 ELSIF (p_action = 'UPDATE') THEN
430 -- Call all the procedures related to Before Update.
431 Check_Constraints;
432 Check_Parent_Existance;
433 Check_Uniqueness;
434 ELSIF (p_action = 'VALIDATE_INSERT') THEN
435 IF Get_PK_For_Validation (
436 new_references.fee_cat,
437 new_references.fee_cal_type ,
438 new_references.fee_ci_sequence_number,
439 new_references.fee_type,
440 new_references.course_cd,
441 new_references.sequence_number,
442 new_references.hist_start_dt) THEN
443 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
444 IGS_GE_MSG_STACK.ADD;
445 App_Exception.Raise_Exception;
446 END IF;
447 Check_Constraints;
448 Check_Uniqueness;
449 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
450 Check_Constraints;
451 Check_Uniqueness;
452 END IF;
453
454 END Before_DML;
455
456 PROCEDURE After_DML (
457 p_action IN VARCHAR2,
458 x_rowid IN VARCHAR2
459 ) AS
460 BEGIN
461
462 l_rowid := null;
463
464
465 END After_DML;
466
467 procedure INSERT_ROW (
468 X_ROWID in out NOCOPY VARCHAR2,
469 X_FEE_CAT in VARCHAR2,
470 X_FEE_TYPE in VARCHAR2,
471 X_COURSE_CD in VARCHAR2,
472 X_SEQUENCE_NUMBER in NUMBER,
473 X_HIST_START_DT in DATE,
474 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
475 X_FEE_CAL_TYPE in VARCHAR2,
476 X_HIST_END_DT in DATE,
477 X_HIST_WHO in NUMBER,
478 X_VERSION_NUMBER in NUMBER,
479 X_CAL_TYPE in VARCHAR2,
480 X_LOCATION_CD in VARCHAR2,
481 X_ATTENDANCE_MODE in VARCHAR2,
482 X_ATTENDANCE_TYPE in VARCHAR2,
483 X_CREATE_DT in DATE,
484 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
485 X_MODE in VARCHAR2 default 'R',
486 X_ORG_ID in number
487 ) AS
488 cursor C is select ROWID from IGS_PS_FEE_TRG_HIST_ALL
489 where FEE_CAT = X_FEE_CAT
490 and FEE_TYPE = X_FEE_TYPE
491 and COURSE_CD = X_COURSE_CD
492 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
493 and HIST_START_DT = X_HIST_START_DT
494 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
495 and FEE_CAL_TYPE = X_FEE_CAL_TYPE;
496 X_LAST_UPDATE_DATE DATE;
497 X_LAST_UPDATED_BY NUMBER;
498 X_LAST_UPDATE_LOGIN NUMBER;
499 begin
500 X_LAST_UPDATE_DATE := SYSDATE;
501 if(X_MODE = 'I') then
502 X_LAST_UPDATED_BY := 1;
503 X_LAST_UPDATE_LOGIN := 0;
504 elsif (X_MODE = 'R') then
505 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
506 if X_LAST_UPDATED_BY is NULL then
507 X_LAST_UPDATED_BY := -1;
508 end if;
509 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
510 if X_LAST_UPDATE_LOGIN is NULL then
511 X_LAST_UPDATE_LOGIN := -1;
512 end if;
513 else
514 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
515 IGS_GE_MSG_STACK.ADD;
516 app_exception.raise_exception;
517 end if;
518
519 Before_DML (
520 p_action => 'INSERT',
521 x_rowid => X_ROWID,
522 x_fee_cat => X_FEE_CAT,
523 x_fee_cal_type => X_FEE_CAL_TYPE,
524 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
525 x_fee_type => X_FEE_TYPE,
526 x_course_cd => X_COURSE_CD,
527 x_sequence_number => X_SEQUENCE_NUMBER,
528 x_hist_start_dt => X_HIST_START_DT,
529 x_hist_end_dt => X_HIST_END_DT,
530 x_hist_who => X_HIST_WHO,
531 x_version_number => X_VERSION_NUMBER,
532 x_cal_type => X_CAL_TYPE,
533 x_location_cd => X_LOCATION_CD,
534 x_attendance_mode => X_ATTENDANCE_MODE,
535 x_attendance_type => X_ATTENDANCE_TYPE,
536 x_create_dt => NVL(X_CREATE_DT,SYSDATE),
537 x_fee_trigger_group_number => X_FEE_TRIGGER_GROUP_NUMBER ,
538 x_creation_date => X_LAST_UPDATE_DATE ,
539 x_created_by => X_LAST_UPDATED_BY ,
540 x_last_update_date => X_LAST_UPDATE_DATE ,
541 x_last_updated_by => X_LAST_UPDATED_BY ,
542 x_last_update_login => X_LAST_UPDATE_LOGIN,
543 x_org_id => igs_ge_gen_003.get_org_id
544 );
545
546 insert into IGS_PS_FEE_TRG_HIST_ALL (
547 FEE_CAT,
548 FEE_CAL_TYPE,
549 FEE_CI_SEQUENCE_NUMBER,
550 FEE_TYPE,
551 COURSE_CD,
552 SEQUENCE_NUMBER,
553 HIST_START_DT,
554 HIST_END_DT,
555 HIST_WHO,
556 VERSION_NUMBER,
557 CAL_TYPE,
558 LOCATION_CD,
559 ATTENDANCE_MODE,
560 ATTENDANCE_TYPE,
561 CREATE_DT,
562 FEE_TRIGGER_GROUP_NUMBER,
563 CREATION_DATE,
564 CREATED_BY,
565 LAST_UPDATE_DATE,
566 LAST_UPDATED_BY,
567 LAST_UPDATE_LOGIN,
568 ORG_ID
569 ) values (
570 NEW_REFERENCES.FEE_CAT,
571 NEW_REFERENCES.FEE_CAL_TYPE,
572 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
573 NEW_REFERENCES.FEE_TYPE,
574 NEW_REFERENCES.COURSE_CD,
575 NEW_REFERENCES.SEQUENCE_NUMBER,
576 NEW_REFERENCES.HIST_START_DT,
577 NEW_REFERENCES.HIST_END_DT,
578 NEW_REFERENCES.HIST_WHO,
579 NEW_REFERENCES.VERSION_NUMBER,
580 NEW_REFERENCES.CAL_TYPE,
581 NEW_REFERENCES.LOCATION_CD,
582 NEW_REFERENCES.ATTENDANCE_MODE,
583 NEW_REFERENCES.ATTENDANCE_TYPE,
584 NEW_REFERENCES.CREATE_DT,
585 NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
586 X_LAST_UPDATE_DATE,
587 X_LAST_UPDATED_BY,
588 X_LAST_UPDATE_DATE,
589 X_LAST_UPDATED_BY,
590 X_LAST_UPDATE_LOGIN,
591 NEW_REFERENCES.ORG_ID
592 );
593
594 open c;
595 fetch c into X_ROWID;
596 if (c%notfound) then
597 close c;
598 raise no_data_found;
599 end if;
600
601 close c;
602 After_DML (
603 p_action => 'INSERT',
604 x_rowid => X_ROWID
605 );
606 end INSERT_ROW;
607
608 procedure LOCK_ROW (
609 X_ROWID IN VARCHAR2,
610 X_FEE_CAT in VARCHAR2,
611 X_FEE_TYPE in VARCHAR2,
612 X_COURSE_CD in VARCHAR2,
613 X_SEQUENCE_NUMBER in NUMBER,
614 X_HIST_START_DT in DATE,
615 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
616 X_FEE_CAL_TYPE in VARCHAR2,
617 X_HIST_END_DT in DATE,
618 X_HIST_WHO in NUMBER,
619 X_VERSION_NUMBER in NUMBER,
620 X_CAL_TYPE in VARCHAR2,
621 X_LOCATION_CD in VARCHAR2,
622 X_ATTENDANCE_MODE in VARCHAR2,
623 X_ATTENDANCE_TYPE in VARCHAR2,
624 X_CREATE_DT in DATE,
625 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER
626
627 ) AS
628 cursor c1 is select
629 HIST_END_DT,
630 HIST_WHO,
631 VERSION_NUMBER,
632 CAL_TYPE,
633 LOCATION_CD,
634 ATTENDANCE_MODE,
635 ATTENDANCE_TYPE,
636 CREATE_DT,
637 FEE_TRIGGER_GROUP_NUMBER
638
639 from IGS_PS_FEE_TRG_HIST_ALL
640 where ROWID = X_ROWID
641 for update nowait;
642 tlinfo c1%rowtype;
643
644 begin
645 open c1;
646 fetch c1 into tlinfo;
647 if (c1%notfound) then
648 close c1;
649 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
650 IGS_GE_MSG_STACK.ADD;
651 app_exception.raise_exception;
652 return;
653 end if;
654 close c1;
655
656 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
657 AND (tlinfo.HIST_WHO = X_HIST_WHO)
658 AND ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
659 OR ((tlinfo.VERSION_NUMBER is null)
660 AND (X_VERSION_NUMBER is null)))
661 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
662 OR ((tlinfo.CAL_TYPE is null)
663 AND (X_CAL_TYPE is null)))
664 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
665 OR ((tlinfo.LOCATION_CD is null)
666 AND (X_LOCATION_CD is null)))
667 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
668 OR ((tlinfo.ATTENDANCE_MODE is null)
669 AND (X_ATTENDANCE_MODE is null)))
670 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
671 OR ((tlinfo.ATTENDANCE_TYPE is null)
672 AND (X_ATTENDANCE_TYPE is null)))
673 AND ((tlinfo.CREATE_DT = X_CREATE_DT)
674 OR ((tlinfo.CREATE_DT is null)
675 AND (X_CREATE_DT is null)))
676 AND ((tlinfo.FEE_TRIGGER_GROUP_NUMBER = X_FEE_TRIGGER_GROUP_NUMBER)
677 OR ((tlinfo.FEE_TRIGGER_GROUP_NUMBER is null)
678 AND (X_FEE_TRIGGER_GROUP_NUMBER is null)))
679
680 ) then
681 null;
682 else
683 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
684 IGS_GE_MSG_STACK.ADD;
685 app_exception.raise_exception;
686 end if;
687 return;
688 end LOCK_ROW;
689
690 procedure UPDATE_ROW (
691 X_ROWID IN VARCHAR2,
692 X_FEE_CAT in VARCHAR2,
693 X_FEE_TYPE in VARCHAR2,
694 X_COURSE_CD in VARCHAR2,
695 X_SEQUENCE_NUMBER in NUMBER,
696 X_HIST_START_DT in DATE,
697 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
698 X_FEE_CAL_TYPE in VARCHAR2,
699 X_HIST_END_DT in DATE,
700 X_HIST_WHO in NUMBER,
701 X_VERSION_NUMBER in NUMBER,
702 X_CAL_TYPE in VARCHAR2,
703 X_LOCATION_CD in VARCHAR2,
704 X_ATTENDANCE_MODE in VARCHAR2,
705 X_ATTENDANCE_TYPE in VARCHAR2,
706 X_CREATE_DT in DATE,
707 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
708 X_MODE in VARCHAR2 default 'R'
709
710 ) AS
711 X_LAST_UPDATE_DATE DATE;
712 X_LAST_UPDATED_BY NUMBER;
713 X_LAST_UPDATE_LOGIN NUMBER;
714 begin
715 X_LAST_UPDATE_DATE := SYSDATE;
716 if(X_MODE = 'I') then
717 X_LAST_UPDATED_BY := 1;
718 X_LAST_UPDATE_LOGIN := 0;
719 elsif (X_MODE = 'R') then
720 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
721 if X_LAST_UPDATED_BY is NULL then
722 X_LAST_UPDATED_BY := -1;
723 end if;
724 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
725 if X_LAST_UPDATE_LOGIN is NULL then
726 X_LAST_UPDATE_LOGIN := -1;
727 end if;
728 else
729 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
730 IGS_GE_MSG_STACK.ADD;
731 app_exception.raise_exception;
732 end if;
733
734 Before_DML (
735 p_action => 'UPDATE',
736 x_rowid => X_ROWID,
737 x_fee_cat => X_FEE_CAT,
738 x_fee_cal_type => X_FEE_CAL_TYPE,
739 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
740 x_fee_type => X_FEE_TYPE,
741 x_course_cd => X_COURSE_CD,
742 x_sequence_number => X_SEQUENCE_NUMBER,
743 x_hist_start_dt => X_HIST_START_DT,
744 x_hist_end_dt => X_HIST_END_DT,
745 x_hist_who => X_HIST_WHO,
746 x_version_number => X_VERSION_NUMBER,
747 x_cal_type => X_CAL_TYPE,
748 x_location_cd => X_LOCATION_CD,
749 x_attendance_mode => X_ATTENDANCE_MODE,
750 x_attendance_type => X_ATTENDANCE_TYPE,
751 x_create_dt => X_CREATE_DT,
752 x_fee_trigger_group_number => X_FEE_TRIGGER_GROUP_NUMBER ,
753 x_creation_date => X_LAST_UPDATE_DATE ,
754 x_created_by => X_LAST_UPDATED_BY ,
755 x_last_update_date => X_LAST_UPDATE_DATE ,
756 x_last_updated_by => X_LAST_UPDATED_BY ,
757 x_last_update_login => X_LAST_UPDATE_LOGIN
758
759 );
760
761 update IGS_PS_FEE_TRG_HIST_ALL set
762 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
763 HIST_WHO = NEW_REFERENCES.HIST_WHO,
764 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
765 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
766 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
767 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
768 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
769 CREATE_DT = NEW_REFERENCES.CREATE_DT,
770 FEE_TRIGGER_GROUP_NUMBER = NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
771 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
772 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
773 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
774
775 where ROWID = X_ROWID
776 ;
777 if (sql%notfound) then
778 raise no_data_found;
779 end if;
780 After_DML (
781 p_action => 'UPDATE',
782 x_rowid => X_ROWID
783 );
784 end UPDATE_ROW;
785
786 procedure ADD_ROW (
787 X_ROWID in out NOCOPY VARCHAR2,
788 X_FEE_CAT in VARCHAR2,
789 X_FEE_TYPE in VARCHAR2,
790 X_COURSE_CD in VARCHAR2,
791 X_SEQUENCE_NUMBER in NUMBER,
792 X_HIST_START_DT in DATE,
793 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
794 X_FEE_CAL_TYPE in VARCHAR2,
795 X_HIST_END_DT in DATE,
796 X_HIST_WHO in NUMBER,
797 X_VERSION_NUMBER in NUMBER,
798 X_CAL_TYPE in VARCHAR2,
799 X_LOCATION_CD in VARCHAR2,
800 X_ATTENDANCE_MODE in VARCHAR2,
801 X_ATTENDANCE_TYPE in VARCHAR2,
802 X_CREATE_DT in DATE,
803 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
804 X_MODE in VARCHAR2 default 'R',
805 X_ORG_ID in NUMBER
806 ) AS
807 cursor c1 is select rowid from IGS_PS_FEE_TRG_HIST_ALL
808 where FEE_CAT = X_FEE_CAT
809 and FEE_TYPE = X_FEE_TYPE
810 and COURSE_CD = X_COURSE_CD
811 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
812 and HIST_START_DT = X_HIST_START_DT
813 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
814 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
815 ;
816 begin
817 open c1;
818 fetch c1 into X_ROWID;
819 if (c1%notfound) then
820 close c1;
821 INSERT_ROW (
822 X_ROWID,
823 X_FEE_CAT,
824 X_FEE_TYPE,
825 X_COURSE_CD,
826 X_SEQUENCE_NUMBER,
827 X_HIST_START_DT,
828 X_FEE_CI_SEQUENCE_NUMBER,
829 X_FEE_CAL_TYPE,
830 X_HIST_END_DT,
831 X_HIST_WHO,
832 X_VERSION_NUMBER,
833 X_CAL_TYPE,
834 X_LOCATION_CD,
835 X_ATTENDANCE_MODE,
836 X_ATTENDANCE_TYPE,
837 X_CREATE_DT,
838 X_FEE_TRIGGER_GROUP_NUMBER,
839 X_MODE,
840 X_ORG_ID);
841 return;
842 end if;
843 close c1;
844 UPDATE_ROW (
845 X_ROWID,
846 X_FEE_CAT,
847 X_FEE_TYPE,
848 X_COURSE_CD,
849 X_SEQUENCE_NUMBER,
850 X_HIST_START_DT,
851 X_FEE_CI_SEQUENCE_NUMBER,
852 X_FEE_CAL_TYPE,
853 X_HIST_END_DT,
854 X_HIST_WHO,
855 X_VERSION_NUMBER,
856 X_CAL_TYPE,
857 X_LOCATION_CD,
858 X_ATTENDANCE_MODE,
859 X_ATTENDANCE_TYPE,
860 X_CREATE_DT,
861 X_FEE_TRIGGER_GROUP_NUMBER,
862 X_MODE
863 );
864 end ADD_ROW;
865
866 procedure DELETE_ROW (
867 X_ROWID in VARCHAR2
868 ) AS
869 begin
870 Before_DML (
871 p_action => 'DELETE',
872 x_rowid => X_ROWID
873 );
874 delete from IGS_PS_FEE_TRG_HIST_ALL
875 where ROWID = X_ROWID;
876 if (sql%notfound) then
877 raise no_data_found;
878 end if;
879 After_DML (
880 p_action => 'DELETE',
881 x_rowid => X_ROWID
882 );
883 end DELETE_ROW;
884
885 end IGS_PS_FEE_TRG_HIST_PKG;