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