[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_ELM_RANGE_H_PKG
Source
1 package body IGS_FI_ELM_RANGE_H_PKG AS
2 /* $Header: IGSSI15B.pls 115.9 2003/02/12 10:03:13 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_ELM_RANGE_H_ALL%RowType;
6 new_references IGS_FI_ELM_RANGE_H_ALL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_fee_type IN VARCHAR2 DEFAULT NULL,
11 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
12 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
13 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
14 x_range_number IN NUMBER DEFAULT NULL,
15 x_hist_start_dt IN DATE DEFAULT NULL,
16 x_hist_end_dt IN DATE DEFAULT NULL,
17 x_hist_who IN VARCHAR2 DEFAULT NULL,
18 x_fee_cat IN VARCHAR2 DEFAULT NULL,
19 x_lower_range IN NUMBER DEFAULT NULL,
20 x_upper_range IN NUMBER DEFAULT NULL,
21 x_s_chg_method_type 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_ELM_RANGE_H_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.fee_type := x_fee_type;
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.s_relation_type := x_s_relation_type;
52 new_references.range_number := x_range_number;
53 new_references.hist_start_dt := x_hist_start_dt;
54 new_references.hist_end_dt := x_hist_end_dt;
55 new_references.hist_who := x_hist_who;
56 new_references.fee_cat := x_fee_cat;
57 new_references.lower_range := x_lower_range;
58 new_references.upper_range := x_upper_range;
59 new_references.s_chg_method_type := x_s_chg_method_type;
60 new_references.org_id := x_org_id;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71 END Set_Column_Values;
72
73 PROCEDURE Check_Uniqueness AS
74 Begin
75 IF Get_UK_For_Validation (
76 new_references.fee_type ,
77 new_references.fee_cal_type ,
78 new_references.fee_ci_sequence_number ,
79 new_references.range_number ,
80 new_references.hist_start_dt ,
81 new_references.fee_cat
82 ) THEN
83 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 END IF;
87 End Check_Uniqueness;
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 17-May-2002 removed upper check constraint on fee_type,fee_cat columns.bug#2344826.
102 ----------------------------------------------------------------------------*/
103 BEGIN
104 IF column_name is null then
105 NULL;
106 ELSIF upper(Column_name) = 'FEE_CAL_TYPE' then
107 new_references.fee_cal_type := column_value;
108 ELSIF upper(Column_name) = 'S_CHG_METHOD_TYPE' then
109 new_references.s_chg_method_type := column_value;
110 ELSIF upper(Column_name) = 'S_RELATION_TYPE' then
111 new_references.s_relation_type := column_value;
112 ELSIF upper(Column_name) = 'RANGE_NUMBER' then
113 new_references.range_number := igs_ge_number.to_num(column_value);
114 ELSIF upper(Column_name) = 'UPPER_RANGE' then
115 new_references.upper_range := igs_ge_number.to_num(column_value);
116 ELSIF upper(Column_name) = 'LOWER_RANGE' then
117 new_references.lower_range := igs_ge_number.to_num(column_value);
118 ELSIF upper(Column_name) = 'FEE_CI_SEQUENCE_NUMBER' then
119 new_references.fee_ci_sequence_number := igs_ge_number.to_num(column_value);
120 End if;
121
122 IF upper(column_name) = 'FEE_CAL_TYPE' OR
123 column_name is null Then
124 IF new_references.FEE_CAL_TYPE <>
125 UPPER(new_references.FEE_CAL_TYPE) Then
126 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131 IF upper(column_name) = 'S_CHG_METHOD_TYPE' OR
132 column_name is null Then
133 IF new_references.S_CHG_METHOD_TYPE <>
134 UPPER(new_references.S_CHG_METHOD_TYPE) 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 IF upper(column_name) = 'S_RELATION_TYPE' OR
141 column_name is null Then
142 IF new_references.S_RELATION_TYPE <>
143 UPPER(new_references.S_RELATION_TYPE) 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) = 'RANGE_NUMBER' OR
151 column_name is null Then
152 IF new_references.range_number < 1 OR
153 new_references.range_number > 999999 Then
154 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159
160 IF upper(column_name) = 'UPPER_RANGE' OR
161 column_name is null Then
162 IF new_references.upper_range < 0 OR
163 new_references.upper_range > 9999.999 Then
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF upper(column_name) = 'LOWER_RANGE' OR
171 column_name is null Then
172 IF new_references.lower_range < 0 OR
173 new_references.lower_range > 9999.999 Then
174 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178 END IF;
179
180 IF upper(column_name) = 'FEE_CI_SEQUENCE_NUMBER' OR
181 column_name is null Then
182 IF new_references.fee_ci_sequence_number < 1 OR
183 new_references.fee_ci_sequence_number > 999999 Then
184 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 END IF;
189 END Check_Constraints;
190 PROCEDURE Check_Parent_Existance AS
191 BEGIN
192 IF (((old_references.fee_cat = new_references.fee_cat) AND
193 (old_references.fee_cal_type = new_references.fee_cal_type) AND
194 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
195 (old_references.fee_type = new_references.fee_type)) OR
196 ((new_references.fee_cat IS NULL) OR
197 (new_references.fee_cal_type IS NULL) OR
198 (new_references.fee_ci_sequence_number IS NULL) OR
199 (new_references.fee_type IS NULL))) THEN
200 NULL;
201 ELSE
202 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
203 new_references.fee_cat,
204 new_references.fee_cal_type,
205 new_references.fee_ci_sequence_number,
206 new_references.fee_type
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 IF (((old_references.fee_type = new_references.fee_type) AND
214 (old_references.fee_cal_type = new_references.fee_cal_type) AND
215 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
216 ((new_references.fee_type IS NULL) OR
217 (new_references.fee_cal_type IS NULL) OR
218 (new_references.fee_ci_sequence_number IS NULL))) THEN
219 NULL;
220 ELSE
221 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
222 new_references.fee_type,
223 new_references.fee_cal_type,
224 new_references.fee_ci_sequence_number
225 ) THEN
226 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END IF;
230 END IF;
231 END Check_Parent_Existance;
232 Function Get_PK_For_Validation (
233 x_fee_type IN VARCHAR2,
234 x_fee_cal_type IN VARCHAR2,
235 x_fee_ci_sequence_number IN NUMBER,
236 x_s_relation_type IN VARCHAR2,
237 x_range_number IN NUMBER,
238 x_hist_start_dt IN DATE
239 ) Return Boolean
240 AS
241 CURSOR cur_rowid IS
242 SELECT rowid
243 FROM IGS_FI_ELM_RANGE_H_ALL
244 WHERE fee_type = x_fee_type
245 AND fee_cal_type = x_fee_cal_type
246 AND fee_ci_sequence_number = x_fee_ci_sequence_number
247 AND s_relation_type = x_s_relation_type
248 AND range_number = x_range_number
249 AND hist_start_dt = x_hist_start_dt
250 FOR UPDATE NOWAIT;
251 lv_rowid cur_rowid%RowType;
252 BEGIN
253 Open cur_rowid;
254 Fetch cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 Close cur_rowid;
257 Return (TRUE);
258 ELSE
259 Close cur_rowid;
260 Return (FALSE);
261 END IF;
262 END Get_PK_For_Validation;
263
264 Function Get_UK_For_Validation (
265 x_fee_type IN VARCHAR2 DEFAULT NULL,
266 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
267 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
268 x_range_number IN NUMBER DEFAULT NULL,
269 x_hist_start_dt IN DATE DEFAULT NULL,
270 x_fee_cat IN VARCHAR2 DEFAULT NULL
271 ) Return Boolean
272 AS
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM IGS_FI_ELM_RANGE_H_ALL
276 WHERE fee_type = new_references.fee_type
277 AND fee_cal_type = new_references.fee_cal_type
278 AND fee_ci_sequence_number = new_references.fee_ci_sequence_number
279 AND range_number = new_references.range_number
280 AND fee_cat = new_references.fee_cat
281 AND hist_start_dt = new_references.hist_start_dt
282 AND ((l_rowid IS NULL) OR (rowid <> l_rowid)) ;
283 lv_rowid cur_rowid%RowType;
284 BEGIN
285 Open cur_rowid;
286 Fetch cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 Close cur_rowid;
289 Return (TRUE);
290 ELSE
291 Close cur_rowid;
292 Return (FALSE);
293 END IF;
294 END Get_UK_For_Validation;
295
296 PROCEDURE Before_DML (
297 p_action IN VARCHAR2,
298 x_rowid IN VARCHAR2 DEFAULT NULL,
299 x_fee_type IN VARCHAR2 DEFAULT NULL,
300 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
301 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
302 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
303 x_range_number IN NUMBER DEFAULT NULL,
304 x_hist_start_dt IN DATE DEFAULT NULL,
305 x_hist_end_dt IN DATE DEFAULT NULL,
306 x_hist_who IN VARCHAR2 DEFAULT NULL,
307 x_fee_cat IN VARCHAR2 DEFAULT NULL,
308 x_lower_range IN NUMBER DEFAULT NULL,
309 x_upper_range IN NUMBER DEFAULT NULL,
310 x_s_chg_method_type IN VARCHAR2 DEFAULT NULL,
311 x_org_id IN NUMBER DEFAULT NULL,
312 x_creation_date IN DATE DEFAULT NULL,
313 x_created_by IN NUMBER DEFAULT NULL,
314 x_last_update_date IN DATE DEFAULT NULL,
315 x_last_updated_by IN NUMBER DEFAULT NULL,
316 x_last_update_login IN NUMBER DEFAULT NULL
317 ) AS
318 BEGIN
319 Set_Column_Values (
320 p_action,
321 x_rowid,
322 x_fee_type,
323 x_fee_cal_type,
324 x_fee_ci_sequence_number,
325 x_s_relation_type,
326 x_range_number,
327 x_hist_start_dt,
328 x_hist_end_dt,
329 x_hist_who,
330 x_fee_cat,
331 x_lower_range,
332 x_upper_range,
333 x_s_chg_method_type,
334 x_org_id,
335 x_creation_date,
336 x_created_by,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_login
340 );
341 IF (p_action = 'INSERT') THEN
342 -- Call all the procedures related to Before Insert.
343 Null;
344 IF Get_PK_For_Validation (
345 new_references.fee_type ,
346 new_references.fee_cal_type ,
347 new_references.fee_ci_sequence_number ,
348 new_references.s_relation_type ,
349 new_references.range_number ,
350 new_references.hist_start_dt
351 ) THEN
352 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception;
355 END IF;
356 Check_Constraints;
357 Check_Uniqueness;
358 Check_Parent_Existance;
359 ELSIF (p_action = 'UPDATE') THEN
360 -- Call all the procedures related to Before Update.
361 Null;
362 Check_Constraints;
363 Check_Uniqueness;
364 Check_Parent_Existance;
365 ELSIF (p_action = 'DELETE') THEN
366 -- Call all the procedures related to Before Delete.
367 Null;
368 ELSIF (p_action = 'VALIDATE_INSERT') THEN
369 IF Get_PK_For_Validation (
370 new_references.fee_type ,
371 new_references.fee_cal_type ,
372 new_references.fee_ci_sequence_number ,
373 new_references.s_relation_type ,
374 new_references.range_number ,
375 new_references.hist_start_dt
376 ) THEN
377 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
378 IGS_GE_MSG_STACK.ADD;
379 App_Exception.Raise_Exception;
380 END IF;
381 Check_Constraints;
382 Check_Uniqueness;
383 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
384 Check_Constraints;
385 Check_Uniqueness;
386 ELSIF (p_action = 'VALIDATE_DELETE') THEN
387 Null;
388 END IF;
389 END Before_DML;
390 procedure INSERT_ROW (
391 X_ROWID in out NOCOPY VARCHAR2,
392 X_FEE_TYPE in VARCHAR2,
393 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
394 X_RANGE_NUMBER in NUMBER,
395 X_HIST_START_DT in DATE,
396 X_S_RELATION_TYPE in VARCHAR2,
397 X_FEE_CAL_TYPE in VARCHAR2,
398 X_HIST_END_DT in DATE,
399 X_HIST_WHO in NUMBER,
400 X_FEE_CAT in VARCHAR2,
401 X_LOWER_RANGE in NUMBER,
402 X_UPPER_RANGE in NUMBER,
403 X_S_CHG_METHOD_TYPE in VARCHAR2,
404 X_ORG_ID in NUMBER,
405 X_MODE in VARCHAR2 default 'R'
406 ) AS
407 cursor C is select ROWID from IGS_FI_ELM_RANGE_H_ALL
408 where FEE_TYPE = X_FEE_TYPE
409 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
410 and RANGE_NUMBER = X_RANGE_NUMBER
411 and HIST_START_DT = X_HIST_START_DT
412 and S_RELATION_TYPE = X_S_RELATION_TYPE
413 and FEE_CAL_TYPE = X_FEE_CAL_TYPE;
414 X_LAST_UPDATE_DATE DATE;
415 X_LAST_UPDATED_BY NUMBER;
416 X_LAST_UPDATE_LOGIN NUMBER;
417 begin
418 X_LAST_UPDATE_DATE := SYSDATE;
419 if(X_MODE = 'I') then
420 X_LAST_UPDATED_BY := 1;
421 X_LAST_UPDATE_LOGIN := 0;
422 elsif (X_MODE = 'R') then
423 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
424 if X_LAST_UPDATED_BY is NULL then
425 X_LAST_UPDATED_BY := -1;
426 end if;
427 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
428 if X_LAST_UPDATE_LOGIN is NULL then
429 X_LAST_UPDATE_LOGIN := -1;
430 end if;
431 else
432 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
433 IGS_GE_MSG_STACK.ADD;
434 app_exception.raise_exception;
435 end if;
436 Before_DML (
437 p_action => 'INSERT',
438 x_rowid => x_rowid,
439 x_fee_type => x_fee_type,
440 x_fee_cal_type => x_fee_cal_type,
441 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
442 x_s_relation_type => x_s_relation_type,
443 x_range_number => x_range_number,
444 x_hist_start_dt => x_hist_start_dt,
445 x_hist_end_dt => x_hist_end_dt ,
446 x_hist_who => x_hist_who,
447 x_fee_cat => x_fee_cat,
448 x_lower_range => x_lower_range,
449 x_upper_range => x_upper_range,
450 x_s_chg_method_type => x_s_chg_method_type,
451 x_org_id => igs_ge_gen_003.get_org_id,
452 x_creation_date => X_LAST_UPDATE_DATE,
453 x_created_by => X_LAST_UPDATED_BY,
454 x_last_update_date => X_LAST_UPDATE_DATE,
455 x_last_updated_by => X_LAST_UPDATED_BY,
456 x_last_update_login => X_LAST_UPDATE_LOGIN
457 );
458 insert into IGS_FI_ELM_RANGE_H_ALL (
459 FEE_TYPE,
460 FEE_CAL_TYPE,
461 FEE_CI_SEQUENCE_NUMBER,
462 S_RELATION_TYPE,
463 RANGE_NUMBER,
464 HIST_START_DT,
465 HIST_END_DT,
466 HIST_WHO,
467 FEE_CAT,
468 LOWER_RANGE,
469 UPPER_RANGE,
470 S_CHG_METHOD_TYPE,
471 ORG_ID,
472 CREATION_DATE,
473 CREATED_BY,
474 LAST_UPDATE_DATE,
475 LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN
477 ) values (
478 NEW_REFERENCES.FEE_TYPE,
479 NEW_REFERENCES.FEE_CAL_TYPE,
480 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
481 NEW_REFERENCES.S_RELATION_TYPE,
482 NEW_REFERENCES.RANGE_NUMBER,
483 NEW_REFERENCES.HIST_START_DT,
484 NEW_REFERENCES.HIST_END_DT,
485 NEW_REFERENCES.HIST_WHO,
486 NEW_REFERENCES.FEE_CAT,
487 NEW_REFERENCES.LOWER_RANGE,
488 NEW_REFERENCES.UPPER_RANGE,
489 NEW_REFERENCES.S_CHG_METHOD_TYPE,
490 NEW_REFERENCES.ORG_ID,
491 X_LAST_UPDATE_DATE,
492 X_LAST_UPDATED_BY,
493 X_LAST_UPDATE_DATE,
494 X_LAST_UPDATED_BY,
495 X_LAST_UPDATE_LOGIN
496 );
497 open c;
498 fetch c into X_ROWID;
499 if (c%notfound) then
500 close c;
501 raise no_data_found;
502 end if;
503 close c;
504 end INSERT_ROW;
505 procedure LOCK_ROW (
506 X_ROWID in VARCHAR2,
507 X_FEE_TYPE in VARCHAR2,
508 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
509 X_RANGE_NUMBER in NUMBER,
510 X_HIST_START_DT in DATE,
511 X_S_RELATION_TYPE in VARCHAR2,
512 X_FEE_CAL_TYPE in VARCHAR2,
513 X_HIST_END_DT in DATE,
514 X_HIST_WHO in NUMBER,
515 X_FEE_CAT in VARCHAR2,
516 X_LOWER_RANGE in NUMBER,
517 X_UPPER_RANGE in NUMBER,
518 X_S_CHG_METHOD_TYPE in VARCHAR2
519 ) AS
520 cursor c1 is select
521 HIST_END_DT,
522 HIST_WHO,
523 FEE_CAT,
524 LOWER_RANGE,
525 UPPER_RANGE,
526 S_CHG_METHOD_TYPE
527 from IGS_FI_ELM_RANGE_H_ALL
528 where ROWID = X_ROWID
529 for update nowait;
530 tlinfo c1%rowtype;
531 begin
532 open c1;
533 fetch c1 into tlinfo;
534 if (c1%notfound) then
535 close c1;
536 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
537 IGS_GE_MSG_STACK.ADD;
538 app_exception.raise_exception;
539 return;
540 end if;
541 close c1;
542 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
543 AND (tlinfo.HIST_WHO = X_HIST_WHO)
544 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
545 OR ((tlinfo.FEE_CAT is null)
546 AND (X_FEE_CAT is null)))
547 AND ((tlinfo.LOWER_RANGE = X_LOWER_RANGE)
548 OR ((tlinfo.LOWER_RANGE is null)
549 AND (X_LOWER_RANGE is null)))
550 AND ((tlinfo.UPPER_RANGE = X_UPPER_RANGE)
551 OR ((tlinfo.UPPER_RANGE is null)
552 AND (X_UPPER_RANGE is null)))
553 AND ((tlinfo.S_CHG_METHOD_TYPE = X_S_CHG_METHOD_TYPE)
554 OR ((tlinfo.S_CHG_METHOD_TYPE is null)
555 AND (X_S_CHG_METHOD_TYPE is null)))
556 ) then
557 null;
558 else
559 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
560 IGS_GE_MSG_STACK.ADD;
561 app_exception.raise_exception;
562 end if;
563 return;
564 end LOCK_ROW;
565 procedure UPDATE_ROW (
566 X_ROWID in VARCHAR2,
567 X_FEE_TYPE in VARCHAR2,
568 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
569 X_RANGE_NUMBER in NUMBER,
570 X_HIST_START_DT in DATE,
571 X_S_RELATION_TYPE in VARCHAR2,
572 X_FEE_CAL_TYPE in VARCHAR2,
573 X_HIST_END_DT in DATE,
574 X_HIST_WHO in NUMBER,
575 X_FEE_CAT in VARCHAR2,
576 X_LOWER_RANGE in NUMBER,
577 X_UPPER_RANGE in NUMBER,
578 X_S_CHG_METHOD_TYPE in VARCHAR2,
579 X_MODE in VARCHAR2 default 'R'
580 ) AS
581 X_LAST_UPDATE_DATE DATE;
582 X_LAST_UPDATED_BY NUMBER;
583 X_LAST_UPDATE_LOGIN NUMBER;
584 begin
585 X_LAST_UPDATE_DATE := SYSDATE;
586 if(X_MODE = 'I') then
587 X_LAST_UPDATED_BY := 1;
588 X_LAST_UPDATE_LOGIN := 0;
589 elsif (X_MODE = 'R') then
590 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
591 if X_LAST_UPDATED_BY is NULL then
592 X_LAST_UPDATED_BY := -1;
593 end if;
594 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
595 if X_LAST_UPDATE_LOGIN is NULL then
596 X_LAST_UPDATE_LOGIN := -1;
597 end if;
598 else
599 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
600 IGS_GE_MSG_STACK.ADD;
601 app_exception.raise_exception;
602 end if;
603 Before_DML (
604 p_action => 'UPDATE',
605 x_rowid => x_rowid,
606 x_fee_type => x_fee_type,
607 x_fee_cal_type => x_fee_cal_type,
608 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
609 x_s_relation_type => x_s_relation_type,
610 x_range_number => x_range_number,
611 x_hist_start_dt => x_hist_start_dt,
612 x_hist_end_dt => x_hist_end_dt ,
613 x_hist_who => x_hist_who,
614 x_fee_cat => x_fee_cat,
615 x_lower_range => x_lower_range,
616 x_upper_range => x_upper_range,
617 x_s_chg_method_type => x_s_chg_method_type,
618 x_creation_date => X_LAST_UPDATE_DATE,
619 x_created_by => X_LAST_UPDATED_BY,
620 x_last_update_date => X_LAST_UPDATE_DATE,
621 x_last_updated_by => X_LAST_UPDATED_BY,
622 x_last_update_login => X_LAST_UPDATE_LOGIN
623 );
624 update IGS_FI_ELM_RANGE_H_ALL set
625 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
626 HIST_WHO = NEW_REFERENCES.HIST_WHO,
627 FEE_CAT = NEW_REFERENCES.FEE_CAT,
628 LOWER_RANGE = NEW_REFERENCES.LOWER_RANGE,
629 UPPER_RANGE = NEW_REFERENCES.UPPER_RANGE,
630 S_CHG_METHOD_TYPE = NEW_REFERENCES.S_CHG_METHOD_TYPE,
631 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
632 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
633 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
634 where ROWID = X_ROWID;
635 if (sql%notfound) then
636 raise no_data_found;
637 end if;
638 end UPDATE_ROW;
639 procedure ADD_ROW (
640 X_ROWID in out NOCOPY VARCHAR2,
641 X_FEE_TYPE in VARCHAR2,
642 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
643 X_RANGE_NUMBER in NUMBER,
644 X_HIST_START_DT in DATE,
645 X_S_RELATION_TYPE in VARCHAR2,
646 X_FEE_CAL_TYPE in VARCHAR2,
647 X_HIST_END_DT in DATE,
648 X_HIST_WHO in NUMBER,
649 X_FEE_CAT in VARCHAR2,
650 X_LOWER_RANGE in NUMBER,
651 X_UPPER_RANGE in NUMBER,
652 X_S_CHG_METHOD_TYPE in VARCHAR2,
653 X_ORG_ID in NUMBER,
654 X_MODE in VARCHAR2 default 'R'
655 ) AS
656 cursor c1 is select rowid from IGS_FI_ELM_RANGE_H_ALL
657 where FEE_TYPE = X_FEE_TYPE
658 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
659 and RANGE_NUMBER = X_RANGE_NUMBER
660 and HIST_START_DT = X_HIST_START_DT
661 and S_RELATION_TYPE = X_S_RELATION_TYPE
662 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
663 ;
664 begin
665 open c1;
666 fetch c1 into X_ROWID;
667 if (c1%notfound) then
668 close c1;
669 INSERT_ROW (
670 X_ROWID,
671 X_FEE_TYPE,
672 X_FEE_CI_SEQUENCE_NUMBER,
673 X_RANGE_NUMBER,
674 X_HIST_START_DT,
675 X_S_RELATION_TYPE,
676 X_FEE_CAL_TYPE,
677 X_HIST_END_DT,
678 X_HIST_WHO,
679 X_FEE_CAT,
680 X_LOWER_RANGE,
681 X_UPPER_RANGE,
682 X_S_CHG_METHOD_TYPE,
683 X_ORG_ID,
684 X_MODE);
685 return;
686 end if;
687 close c1;
688 UPDATE_ROW (
689 X_ROWID,
690 X_FEE_TYPE,
691 X_FEE_CI_SEQUENCE_NUMBER,
692 X_RANGE_NUMBER,
693 X_HIST_START_DT,
694 X_S_RELATION_TYPE,
695 X_FEE_CAL_TYPE,
696 X_HIST_END_DT,
697 X_HIST_WHO,
698 X_FEE_CAT,
699 X_LOWER_RANGE,
700 X_UPPER_RANGE,
701 X_S_CHG_METHOD_TYPE,
702 X_MODE);
703 end ADD_ROW;
704 procedure DELETE_ROW (
705 X_ROWID in VARCHAR2
706 ) AS
707 begin
708 Before_DML(
709 p_action => 'DELETE',
710 x_rowid => X_ROWID
711 );
712 delete from IGS_FI_ELM_RANGE_H_ALL
713 where ROWID = X_ROWID;
714 if (sql%notfound) then
715 raise no_data_found;
716 end if;
717 end DELETE_ROW;
718 end IGS_FI_ELM_RANGE_H_PKG;