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