[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_EL_RNG_PKG
Source
1 package body IGS_FI_EL_RNG_PKG AS
2 /* $Header: IGSSI66B.pls 120.1 2005/07/05 21:52:05 appldev ship $*/
3 l_rowid VARCHAR2(25) ;
4 old_references IGS_FI_ELM_RANGE%RowType;
5 new_references IGS_FI_ELM_RANGE%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_ER_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_fee_cat IN VARCHAR2 DEFAULT NULL,
16 x_lower_range IN NUMBER DEFAULT NULL,
17 x_upper_range IN NUMBER DEFAULT NULL,
18 x_s_chg_method_type IN VARCHAR2 DEFAULT NULL,
19 x_logical_delete_dt IN DATE DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_FI_ELM_RANGE
29 WHERE rowid = x_rowid;
30 BEGIN
31 l_rowid := x_rowid;
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44 -- Populate New Values.
45 new_references.ER_ID := x_ER_ID;
46 new_references.fee_type := x_fee_type;
47 new_references.fee_cal_type := x_fee_cal_type;
48 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
49 new_references.s_relation_type := x_s_relation_type;
50 new_references.range_number := x_range_number;
51 new_references.fee_cat := x_fee_cat;
52 new_references.lower_range := x_lower_range;
53 new_references.upper_range := x_upper_range;
54 new_references.s_chg_method_type := x_s_chg_method_type;
55 new_references.logical_delete_dt := x_logical_delete_dt;
56 IF (p_action = 'UPDATE') THEN
57 new_references.creation_date := old_references.creation_date;
58 new_references.created_by := old_references.created_by;
59 ELSE
60 new_references.creation_date := x_creation_date;
61 new_references.created_by := x_created_by;
62 END IF;
63 new_references.last_update_date := x_last_update_date;
64 new_references.last_updated_by := x_last_updated_by;
65 new_references.last_update_login := x_last_update_login;
66 END Set_Column_Values;
67 -- Trigger description :-
68 -- BEFORE INSERT OR DELETE OR UPDATE
69 -- ON IGS_FI_ELM_RANGE
70 -- FOR EACH ROW
71 PROCEDURE BeforeRowInsertUpdateDelete1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name varchar2(30);
77 BEGIN
78 -- Validate Elements Range can be created.
79 IF p_inserting THEN
80 -- If IGS_FI_FEE_TYPE.s_fee_trigger_cat = 'INSTITUTN' or
81 -- IGS_FI_FEE_TYPE.s_fee_type = 'HECS', then element ranges
82 -- can only be defined against FTCI's.
83 IF new_references.s_relation_type <> 'FTCI' THEN
84 IF IGS_FI_VAL_ER.finp_val_er_ins (
85 new_references.fee_type,
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 END IF;
92 -- If charge method type of parent record is 'FLATRATE' or fee_type.s_fee_type
93 -- is 'HECS' then elements ranges cannot be defined.
94 IF IGS_FI_VAL_ER.finp_val_er_create (
95 new_references.fee_cal_type,
96 new_references.fee_ci_sequence_number,
97 new_references.fee_type,
98 new_references.fee_cat,
99 v_message_name) = FALSE THEN
100 Fnd_Message.Set_Name('IGS',v_message_name);
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END IF;
104 END IF;
105 -- Validate fee category is only set when the relation type = 'FCFL'.
106 IF p_inserting OR p_updating THEN
107 IF IGS_FI_VAL_ER.finp_val_er_rltn (
108 new_references.s_relation_type,
109 new_references.fee_cat,
110 v_message_name) = FALSE THEN
111 Fnd_Message.Set_Name('IGS',v_message_name);
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END IF;
115 IF IGS_FI_VAL_ER.finp_val_er_ranges (
116 new_references.lower_range,
117 new_references.upper_range,
118 v_message_name) = FALSE THEN
119 Fnd_Message.Set_Name('IGS',v_message_name);
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124 END BeforeRowInsertUpdateDelete1;
125 -- Trigger description :-
126 -- AFTER UPDATE
127 -- ON IGS_FI_ELM_RANGE
128 -- FOR EACH ROW
129 PROCEDURE AfterRowUpdate3(
130 p_inserting IN BOOLEAN DEFAULT FALSE,
131 p_updating IN BOOLEAN DEFAULT FALSE,
132 p_deleting IN BOOLEAN DEFAULT FALSE
133 ) AS
134 BEGIN
135 -- create a history
136 IGS_FI_GEN_002.FINP_INS_ER_HIST(old_references.fee_type,
137 old_references.fee_cal_type,
138 old_references.fee_ci_sequence_number,
139 old_references.s_relation_type,
140 old_references.range_number,
141 new_references.fee_cat,
142 old_references.fee_cat,
143 new_references.lower_range,
144 old_references.lower_range,
145 new_references.upper_range,
146 old_references.upper_range,
147 new_references.s_chg_method_type,
148 old_references.s_chg_method_type,
149 new_references.last_updated_by,
150 old_references.last_updated_by,
151 new_references.last_update_date,
152 old_references.last_update_date);
153 END AfterRowUpdate3;
154 -- Trigger description :-
155 -- AFTER INSERT OR UPDATE
156 -- ON IGS_FI_ELM_RANGE
157 PROCEDURE AfterStmtInsertUpdate4(
158 p_inserting IN BOOLEAN DEFAULT FALSE,
159 p_updating IN BOOLEAN DEFAULT FALSE,
160 p_deleting IN BOOLEAN DEFAULT FALSE
161 ) AS
162 v_message_name varchar2(30);
163 v_message_string VARCHAR2(512);
164 BEGIN
165 -- Validate if elements_range can be created and if so, then
166 -- validate the range value for overlaps.
167 IF p_inserting OR p_updating THEN
168 IF IGS_FI_VAL_ER.finp_val_er_defn(new_references.fee_type,
169 new_references.fee_cal_type,
170 new_references.fee_ci_sequence_number,
171 new_references.s_relation_type,
172 v_message_name) = FALSE THEN
173 Fnd_Message.Set_Name('IGS',v_message_name);
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 IF IGS_FI_VAL_ER.finp_val_er_ovrlp(new_references.fee_type,
178 new_references.fee_cal_type,
179 new_references.fee_ci_sequence_number,
180 new_references.s_relation_type,
181 new_references.fee_cat,
182 new_references.range_number,
183 new_references.lower_range,
184 new_references.upper_range,
185 v_message_name) = FALSE THEN
186 Fnd_Message.Set_Name('IGS',v_message_name);
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191 END AfterStmtInsertUpdate4;
192 PROCEDURE Check_Constraints (
193 column_name IN VARCHAR2 DEFAULT NULL,
194 column_value IN VARCHAR2 DEFAULT NULL
195 ) AS
196 /*----------------------------------------------------------------------------
197 || Created By :
198 || Created On :
199 || Purpose :
200 || Known limitations, enhancements or remarks :
201 || Change History :
202 || Who When What
203 || (reverse chronological order - newest change first)
204 || svuppala 24-JUN-2005 Bug 3392088 Modifications as part of CPF build
205 || Added Incremental check also for override Charge Method
206 || vvutukur 18-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
207 ----------------------------------------------------------------------------*/
208 BEGIN
209 IF (column_name IS NULL) THEN
210 NULL;
211 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
212 new_references.fee_cal_type := column_value;
213 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
214 new_references.fee_ci_sequence_number := igs_ge_number.To_Num (column_value);
215 ELSIF (UPPER (column_name) = 'S_RELATION_TYPE') THEN
216 new_references.s_relation_type := column_value;
217 ELSIF (UPPER (column_name) = 'RANGE_NUMBER') THEN
218 new_references.range_number := igs_ge_number.To_Num (column_value);
219 ELSIF (UPPER (column_name) = 'UPPER_RANGE') THEN
220 new_references.upper_range := igs_ge_number.To_Num (column_value);
221 ELSIF (UPPER (column_name) = 'S_CHG_METHOD_TYPE') THEN
222 new_references.s_chg_method_type := column_value;
223 ELSIF (UPPER (column_name) = 'LOWER_RANGE') THEN
224 new_references.lower_range := igs_ge_number.To_Num (column_value);
225 END IF;
226 IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
227 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
228 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 END IF;
233 IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
234 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
235 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240 IF ((UPPER (column_name) = 'S_RELATION_TYPE') OR (column_name IS NULL)) THEN
241 IF (new_references.s_relation_type NOT IN ('FTCI', 'FCFL')) THEN
242 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247 IF ((UPPER (column_name) = 'RANGE_NUMBER') OR (column_name IS NULL)) THEN
248 IF ((new_references.range_number < 1) OR (new_references.range_number > 999999)) THEN
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254 IF ((UPPER (column_name) = 'UPPER_RANGE') OR (column_name IS NULL)) THEN
255 IF ((new_references.upper_range < 0) OR (new_references.upper_range > 9999.999)) THEN
256 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 END IF;
261 IF ((UPPER (column_name) = 'S_CHG_METHOD_TYPE') OR (column_name IS NULL)) THEN
262 IF (new_references.s_chg_method_type NOT IN ('FLATRATE','INCREMENTAL')) THEN
263 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268 IF ((UPPER (column_name) = 'LOWER_RANGE') OR (column_name IS NULL)) THEN
269 IF ((new_references.lower_range < 0) OR (new_references.lower_range > 9999.999)) THEN
270 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274 END IF;
275 END Check_Constraints;
276 PROCEDURE Check_Uniqueness AS
277 BEGIN
278 IF (Get_UK1_For_Validation (
279 new_references.fee_type,
280 new_references.fee_cal_type,
281 new_references.fee_ci_sequence_number,
282 new_references.range_number,
283 new_references.fee_cat
284 )) THEN
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 END IF;
289 IF (Get_UK2_For_Validation (
290 new_references.fee_type,
291 new_references.fee_cal_type,
292 new_references.fee_ci_sequence_number,
293 new_references.s_relation_type,
294 new_references.range_number,
295 new_references.fee_cat
296 )) THEN
297 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 END IF;
301 IF (Get_UK3_For_Validation (
302 new_references.er_id
303 )) THEN
304 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 END IF;
308 END Check_Uniqueness;
309 PROCEDURE Check_Parent_Existance AS
310 BEGIN
311 IF (((old_references.fee_cat = new_references.fee_cat) AND
312 (old_references.fee_cal_type = new_references.fee_cal_type) AND
313 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
314 (old_references.fee_type = new_references.fee_type)) OR
315 ((new_references.fee_cat IS NULL) OR
316 (new_references.fee_cal_type IS NULL) OR
317 (new_references.fee_ci_sequence_number IS NULL) OR
318 (new_references.fee_type IS NULL))) THEN
319 NULL;
320 ELSE
321 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
322 new_references.fee_cat,
323 new_references.fee_cal_type,
324 new_references.fee_ci_sequence_number,
325 new_references.fee_type
326 ) THEN
327 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 END IF;
331 END IF;
332 IF (((old_references.fee_type = new_references.fee_type) AND
333 (old_references.fee_cal_type = new_references.fee_cal_type) AND
334 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
335 ((new_references.fee_type IS NULL) OR
336 (new_references.fee_cal_type IS NULL) OR
337 (new_references.fee_ci_sequence_number IS NULL))) THEN
338 NULL;
339 ELSE
340 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
341 new_references.fee_type,
342 new_references.fee_cal_type,
343 new_references.fee_ci_sequence_number
344 ) THEN
345 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
346 IGS_GE_MSG_STACK.ADD;
347 App_Exception.Raise_Exception;
348 END IF;
349 END IF;
350 IF (((old_references.s_chg_method_type = new_references.s_chg_method_type)) OR
351 ((new_references.s_chg_method_type IS NULL))) THEN
352 NULL;
353 ELSE
354 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
355 'CHG_METHOD',
356 new_references.s_chg_method_type
357 ) THEN
358 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
359 IGS_GE_MSG_STACK.ADD;
360 App_Exception.Raise_Exception;
361 END IF;
362 END IF;
363 END Check_Parent_Existance;
364 PROCEDURE Check_Child_Existance AS
365 BEGIN
366 IGS_FI_ELM_RANGE_RT_PKG.GET_UFK_IGS_FI_ELM_RANGE (
367 new_references.fee_type,
368 new_references.fee_cal_type,
369 new_references.fee_ci_sequence_number,
370 new_references.s_relation_type,
371 new_references.range_number,
372 new_references.fee_cat
373 );
374 END Check_Child_Existance;
375 PROCEDURE Check_UK_Child_Existance AS
376 BEGIN
377 IF (((old_references.fee_type = new_references.fee_type) AND
378 (old_references.fee_cal_type = new_references.fee_cal_type) AND
379 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
380 (old_references.s_relation_type = new_references.s_relation_type) AND
381 (old_references.range_number = new_references.range_number) AND
382 (old_references.fee_cat = new_references.fee_cat)) OR
383 ((old_references.fee_type = Null) AND
384 (old_references.fee_cal_type = Null) AND
385 (old_references.fee_ci_sequence_number = Null) AND
386 (old_references.s_relation_type = Null) AND
387 (old_references.range_number = Null) AND
388 (old_references.fee_cat = Null))) THEN
389 Null;
390 ELSE
391 IGS_FI_ELM_RANGE_RT_PKG.GET_UFK_IGS_FI_ELM_RANGE (
392 old_references.fee_type,
393 old_references.fee_cal_type,
394 old_references.fee_ci_sequence_number,
395 old_references.s_relation_type,
396 old_references.range_number,
397 old_references.fee_cat
398 );
399 END IF;
400 END Check_UK_Child_Existance;
401 FUNCTION Get_PK_For_Validation (
402 x_ER_ID NUMBER
403 ) RETURN BOOLEAN AS
404 CURSOR cur_rowid IS
405 SELECT rowid
406 FROM IGS_FI_ELM_RANGE
407 WHERE ER_ID = x_ER_ID
408 FOR UPDATE NOWAIT;
409 lv_rowid cur_rowid%RowType;
410 BEGIN
411 Open cur_rowid;
412 Fetch cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 Close cur_rowid;
415 Return (TRUE);
416 ELSE
417 Close cur_rowid;
418 Return (FALSE);
419 END IF;
420 END Get_PK_For_Validation;
421 FUNCTION Get_UK1_For_Validation (
422 x_fee_type IN VARCHAR2,
423 x_fee_cal_type IN VARCHAR2,
424 x_fee_ci_sequence_number IN NUMBER,
425 x_range_number IN NUMBER,
426 x_fee_cat IN VARCHAR2
427 ) RETURN BOOLEAN AS
428 CURSOR cur_rowid IS
429 SELECT rowid
430 FROM IGS_FI_ELM_RANGE
431 WHERE fee_type = x_fee_type
432 AND fee_cal_type = x_fee_cal_type
433 AND fee_ci_sequence_number = x_fee_ci_sequence_number
434 AND range_number = x_range_number
435 AND fee_cat = x_fee_cat
436 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
437 FOR UPDATE NOWAIT;
438 lv_rowid cur_rowid%RowType;
439 BEGIN
440 Open cur_rowid;
441 IF (cur_rowid%FOUND) THEN
442 Close cur_rowid;
443 Return (TRUE);
444 ELSE
445 Close cur_rowid;
446 Return (FALSE);
447 END IF;
448 END Get_UK1_For_Validation;
449 FUNCTION Get_UK2_For_Validation (
450 x_fee_type IN VARCHAR2,
451 x_fee_cal_type IN VARCHAR2,
452 x_fee_ci_sequence_number IN NUMBER,
453 x_s_relation_type IN VARCHAR2,
454 x_range_number IN NUMBER,
455 x_fee_cat IN VARCHAR2
456 ) RETURN BOOLEAN AS
457 CURSOR cur_rowid IS
458 SELECT rowid
459 FROM IGS_FI_ELM_RANGE
460 WHERE fee_type = x_fee_type
461 AND fee_cal_type = x_fee_cal_type
462 AND fee_ci_sequence_number = x_fee_ci_sequence_number
463 AND s_relation_type = x_s_relation_type
464 AND range_number = x_range_number
465 AND ( fee_cat = x_fee_cat or fee_cat is null)
466 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
467 FOR UPDATE NOWAIT;
468 lv_rowid cur_rowid%RowType;
469 BEGIN
470 Open cur_rowid;
471 Fetch cur_rowid INTO lv_rowid;
472 IF (cur_rowid%FOUND) THEN
473 Close cur_rowid;
474 Return (TRUE);
475 ELSE
476 Close cur_rowid;
477 Return (FALSE);
478 END IF;
479 END Get_UK2_For_Validation;
480 FUNCTION Get_UK3_For_Validation (
481 x_er_id IN NUMBER
482 ) RETURN BOOLEAN AS
483 CURSOR cur_rowid IS
484 SELECT rowid
485 FROM IGS_FI_ELM_RANGE
486 WHERE er_id = x_er_id
487 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
488 FOR UPDATE NOWAIT;
489 lv_rowid cur_rowid%RowType;
490 BEGIN
491 Open cur_rowid;
492 Fetch cur_rowid INTO lv_rowid;
493 IF (cur_rowid%FOUND) THEN
494 Close cur_rowid;
495 Return (TRUE);
496 ELSE
497 Close cur_rowid;
498 Return (FALSE);
499 END IF;
500 END Get_UK3_For_Validation;
501
502 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
503 x_s_chg_method_type IN VARCHAR2
504 ) AS
505 CURSOR cur_rowid IS
506 SELECT rowid
507 FROM IGS_FI_ELM_RANGE
508 WHERE s_chg_method_type = x_s_chg_method_type ;
509 lv_rowid cur_rowid%RowType;
510 BEGIN
511 Open cur_rowid;
512 Fetch cur_rowid INTO lv_rowid;
513 IF (cur_rowid%FOUND) THEN
514 Close cur_rowid;
515 Fnd_Message.Set_Name ('IGS', 'IGS_FI_ER_SLV_FK');
516 IGS_GE_MSG_STACK.ADD;
517 App_Exception.Raise_Exception;
518 Return;
519 END IF;
520 Close cur_rowid;
521 END GET_FK_IGS_LOOKUPS_VIEW;
522 PROCEDURE Before_DML (
523 p_action IN VARCHAR2,
524 x_rowid IN VARCHAR2 DEFAULT NULL,
525 x_ER_ID IN NUMBER DEFAULT NULL,
526 x_fee_type IN VARCHAR2 DEFAULT NULL,
527 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
528 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
529 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
530 x_range_number IN NUMBER DEFAULT NULL,
531 x_fee_cat IN VARCHAR2 DEFAULT NULL,
532 x_lower_range IN NUMBER DEFAULT NULL,
533 x_upper_range IN NUMBER DEFAULT NULL,
534 x_s_chg_method_type IN VARCHAR2 DEFAULT NULL,
535 x_logical_delete_dt IN DATE DEFAULT NULL,
536 x_creation_date IN DATE DEFAULT NULL,
537 x_created_by IN NUMBER DEFAULT NULL,
538 x_last_update_date IN DATE DEFAULT NULL,
539 x_last_updated_by IN NUMBER DEFAULT NULL,
540 x_last_update_login IN NUMBER DEFAULT NULL
541 ) AS
542 BEGIN
543 Set_Column_Values (
544 p_action,
545 x_rowid,
546 x_ER_ID,
547 x_fee_type,
548 x_fee_cal_type,
549 x_fee_ci_sequence_number,
550 x_s_relation_type,
551 x_range_number,
552 x_fee_cat,
553 x_lower_range,
554 x_upper_range,
555 x_s_chg_method_type,
556 x_logical_delete_dt,
557 x_creation_date,
558 x_created_by,
559 x_last_update_date,
560 x_last_updated_by,
561 x_last_update_login
562 );
563 IF (p_action = 'INSERT') THEN
564 -- Call all the procedures related to Before Insert.
565 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
566 IF (Get_PK_For_Validation (
567 new_references.er_id
568 )) THEN
569 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
570 IGS_GE_MSG_STACK.ADD;
571 App_Exception.Raise_Exception;
572 END IF;
573 Check_Uniqueness;
574 Check_Constraints;
575 Check_Parent_Existance;
576 ELSIF (p_action = 'UPDATE') THEN
577 -- Call all the procedures related to Before Update.
578 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
579 Check_Uniqueness;
580 Check_Parent_Existance;
581 ELSIF (p_action = 'DELETE') THEN
582 -- Call all the procedures related to Before Delete.
583 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
584 Check_Child_Existance;
585 ELSIF (p_action = 'VALIDATE_INSERT') THEN
586 IF (Get_PK_For_Validation (
587 new_references.er_id
588 )) THEN
589 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
590 IGS_GE_MSG_STACK.ADD;
591 App_Exception.Raise_Exception;
592 END IF;
593 Check_Uniqueness;
594 Check_Constraints;
595 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
596 Check_Uniqueness;
597 Check_Constraints;
598 Check_UK_Child_Existance;
599 ELSIF (p_action = 'VALIDATE_DELETE') THEN
600 Check_Child_Existance;
601 END IF;
602 END Before_DML;
603 PROCEDURE After_DML (
604 p_action IN VARCHAR2,
605 x_rowid IN VARCHAR2
606 ) AS
607 BEGIN
608 l_rowid := x_rowid;
609 IF (p_action = 'INSERT') THEN
610 -- Call all the procedures related to After Insert.
611 AfterStmtInsertUpdate4 ( p_inserting => TRUE );
612 ELSIF (p_action = 'UPDATE') THEN
613 -- Call all the procedures related to After Update.
614 AfterRowUpdate3 ( p_updating => TRUE );
615 AfterStmtInsertUpdate4 ( p_updating => TRUE );
616 END IF;
617 l_rowid := NULL;
618 END After_DML;
619 procedure INSERT_ROW (
620 X_ROWID in out NOCOPY VARCHAR2,
621 X_ER_ID IN OUT NOCOPY NUMBER,
622 X_FEE_TYPE in VARCHAR2,
623 X_FEE_CAL_TYPE in VARCHAR2,
624 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
625 X_S_RELATION_TYPE in VARCHAR2,
626 X_RANGE_NUMBER in NUMBER,
627 X_FEE_CAT in VARCHAR2,
628 X_LOWER_RANGE in NUMBER,
629 X_UPPER_RANGE in NUMBER,
630 X_S_CHG_METHOD_TYPE in VARCHAR2,
631 X_LOGICAL_DELETE_DT in DATE,
632 X_MODE in VARCHAR2 default 'R'
633 ) AS
634 cursor C (cp_range_id IN NUMBER) is select ROWID from IGS_FI_ELM_RANGE
635 where ER_ID = cp_range_id;
636 X_LAST_UPDATE_DATE DATE;
637 X_LAST_UPDATED_BY NUMBER;
638 X_LAST_UPDATE_LOGIN NUMBER;
639 X_REQUEST_ID NUMBER;
640 X_PROGRAM_ID NUMBER;
641 X_PROGRAM_APPLICATION_ID NUMBER;
642 X_PROGRAM_UPDATE_DATE DATE;
643 begin
644 X_LAST_UPDATE_DATE := SYSDATE;
645 if(X_MODE = 'I') then
646 X_LAST_UPDATED_BY := 1;
647 X_LAST_UPDATE_LOGIN := 0;
648 elsif (X_MODE = 'R') then
649 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
650 if X_LAST_UPDATED_BY is NULL then
651 X_LAST_UPDATED_BY := -1;
652 end if;
653 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
654 if X_LAST_UPDATE_LOGIN is NULL then
655 X_LAST_UPDATE_LOGIN := -1;
656 end if;
657 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
658 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
659 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
660 if (X_REQUEST_ID = -1 ) then
661 X_REQUEST_ID:=NULL;
662 X_PROGRAM_ID:=NULL;
663 X_PROGRAM_APPLICATION_ID:=NULL;
664 X_PROGRAM_UPDATE_DATE:=NULL;
665 else
666 X_PROGRAM_UPDATE_DATE:=SYSDATE;
667 end if;
668 else
669 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
670 IGS_GE_MSG_STACK.ADD;
671 app_exception.raise_exception;
672 end if;
673 SELECT IGS_FI_ELM_RANGE_ER_ID_S.NextVal
674 INTO X_ER_ID
675 FROM dual;
676 Before_DML(
677 p_action=>'INSERT',
678 x_rowid=>X_ROWID,
679 x_ER_ID => X_ER_ID,
680 x_fee_cal_type=>X_FEE_CAL_TYPE,
681 x_fee_cat=>X_FEE_CAT,
682 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
683 x_fee_type=>X_FEE_TYPE,
684 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
685 x_lower_range=>X_LOWER_RANGE,
686 x_range_number=>X_RANGE_NUMBER,
687 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
688 x_s_relation_type=>X_S_RELATION_TYPE,
689 x_upper_range=>X_UPPER_RANGE,
690 x_creation_date=>X_LAST_UPDATE_DATE,
691 x_created_by=>X_LAST_UPDATED_BY,
692 x_last_update_date=>X_LAST_UPDATE_DATE,
693 x_last_updated_by=>X_LAST_UPDATED_BY,
694 x_last_update_login=>X_LAST_UPDATE_LOGIN
695 );
696 insert into IGS_FI_ELM_RANGE (
697 ER_ID,
698 FEE_TYPE,
699 FEE_CAL_TYPE,
700 FEE_CI_SEQUENCE_NUMBER,
701 S_RELATION_TYPE,
702 RANGE_NUMBER,
703 FEE_CAT,
704 LOWER_RANGE,
705 UPPER_RANGE,
706 S_CHG_METHOD_TYPE,
707 LOGICAL_DELETE_DT,
708 CREATION_DATE,
709 CREATED_BY,
710 LAST_UPDATE_DATE,
711 LAST_UPDATED_BY,
712 LAST_UPDATE_LOGIN,
713 REQUEST_ID,
714 PROGRAM_ID,
715 PROGRAM_APPLICATION_ID,
716 PROGRAM_UPDATE_DATE
717 ) values (
718 NEW_REFERENCES.ER_ID,
719 NEW_REFERENCES.FEE_TYPE,
720 NEW_REFERENCES.FEE_CAL_TYPE,
721 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
722 NEW_REFERENCES.S_RELATION_TYPE,
723 NEW_REFERENCES.RANGE_NUMBER,
724 NEW_REFERENCES.FEE_CAT,
725 NEW_REFERENCES.LOWER_RANGE,
726 NEW_REFERENCES.UPPER_RANGE,
727 NEW_REFERENCES.S_CHG_METHOD_TYPE,
728 NEW_REFERENCES.LOGICAL_DELETE_DT,
729 X_LAST_UPDATE_DATE,
730 X_LAST_UPDATED_BY,
731 X_LAST_UPDATE_DATE,
732 X_LAST_UPDATED_BY,
733 X_LAST_UPDATE_LOGIN,
734 X_REQUEST_ID,
735 X_PROGRAM_ID,
736 X_PROGRAM_APPLICATION_ID,
737 X_PROGRAM_UPDATE_DATE
738 );
739 open c (X_ER_ID);
740 fetch c into X_ROWID;
741 if (c%notfound) then
742 close c;
743 raise no_data_found;
744 end if;
745 close c;
746 After_DML(
747 p_action =>'INSERT',
748 x_rowid => X_ROWID
749 );
750 end INSERT_ROW;
751 procedure LOCK_ROW (
752 X_ROWID in VARCHAR2,
753 X_ER_ID IN NUMBER,
754 X_FEE_TYPE in VARCHAR2,
755 X_FEE_CAL_TYPE in VARCHAR2,
756 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
757 X_S_RELATION_TYPE in VARCHAR2,
758 X_RANGE_NUMBER in NUMBER,
759 X_FEE_CAT in VARCHAR2,
760 X_LOWER_RANGE in NUMBER,
761 X_UPPER_RANGE in NUMBER,
762 X_S_CHG_METHOD_TYPE in VARCHAR2,
763 X_LOGICAL_DELETE_DT in DATE
764 ) AS
765 cursor c1 is select
766 FEE_TYPE,
767 FEE_CAL_TYPE,
768 FEE_CI_SEQUENCE_NUMBER,
769 S_RELATION_TYPE,
770 RANGE_NUMBER,
771 FEE_CAT,
772 LOWER_RANGE,
773 UPPER_RANGE,
774 S_CHG_METHOD_TYPE,
775 LOGICAL_DELETE_DT
776 from IGS_FI_ELM_RANGE
777 where ROWID=X_ROWID
778 for update nowait;
779 tlinfo c1%rowtype;
780 begin
781 open c1;
782 fetch c1 into tlinfo;
783 if (c1%notfound) then
784 close c1;
785 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
786 IGS_GE_MSG_STACK.ADD;
787 app_exception.raise_exception;
788 return;
789 end if;
790 close c1;
791 if ( (tlinfo.FEE_TYPE = X_FEE_TYPE)
792 AND (tlinfo.FEE_CAL_TYPE = X_FEE_CAL_TYPE)
793 AND (tlinfo.FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER)
794 AND (tlinfo.S_RELATION_TYPE = X_S_RELATION_TYPE)
795 AND (tlinfo.RANGE_NUMBER = X_RANGE_NUMBER)
796 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
797 OR ((tlinfo.FEE_CAT is null)
798 AND (X_FEE_CAT is null)))
799 AND ((tlinfo.LOWER_RANGE = X_LOWER_RANGE)
800 OR ((tlinfo.LOWER_RANGE is null)
801 AND (X_LOWER_RANGE is null)))
802 AND ((tlinfo.UPPER_RANGE = X_UPPER_RANGE)
803 OR ((tlinfo.UPPER_RANGE is null)
804 AND (X_UPPER_RANGE is null)))
805 AND ((tlinfo.S_CHG_METHOD_TYPE = X_S_CHG_METHOD_TYPE)
806 OR ((tlinfo.S_CHG_METHOD_TYPE is null)
807 AND (X_S_CHG_METHOD_TYPE is null)))
808 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
809 OR ((tlinfo.LOGICAL_DELETE_DT is null)
810 AND (X_LOGICAL_DELETE_DT is null)))
811 ) then
812 null;
813 else
814 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
815 IGS_GE_MSG_STACK.ADD;
816 app_exception.raise_exception;
817 end if;
818 return;
819 end LOCK_ROW;
820 procedure UPDATE_ROW (
821 X_ROWID in VARCHAR2,
822 X_ER_ID IN NUMBER,
823 X_FEE_TYPE in VARCHAR2,
824 X_FEE_CAL_TYPE in VARCHAR2,
825 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
826 X_S_RELATION_TYPE in VARCHAR2,
827 X_RANGE_NUMBER in NUMBER,
828 X_FEE_CAT in VARCHAR2,
829 X_LOWER_RANGE in NUMBER,
830 X_UPPER_RANGE in NUMBER,
831 X_S_CHG_METHOD_TYPE in VARCHAR2,
832 X_LOGICAL_DELETE_DT in DATE,
833 X_MODE in VARCHAR2 default 'R'
834 ) AS
835 X_LAST_UPDATE_DATE DATE;
836 X_LAST_UPDATED_BY NUMBER;
837 X_LAST_UPDATE_LOGIN NUMBER;
838 X_REQUEST_ID NUMBER;
839 X_PROGRAM_ID NUMBER;
840 X_PROGRAM_APPLICATION_ID NUMBER;
841 X_PROGRAM_UPDATE_DATE DATE;
842 begin
843 X_LAST_UPDATE_DATE := SYSDATE;
844 if(X_MODE = 'I') then
845 X_LAST_UPDATED_BY := 1;
846 X_LAST_UPDATE_LOGIN := 0;
847 elsif (X_MODE = 'R') then
848 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
849 if X_LAST_UPDATED_BY is NULL then
850 X_LAST_UPDATED_BY := -1;
851 end if;
852 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
853 if X_LAST_UPDATE_LOGIN is NULL then
854 X_LAST_UPDATE_LOGIN := -1;
855 end if;
856 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
857 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
858 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
859 if (X_REQUEST_ID = -1 ) then
860 X_REQUEST_ID:=OLD_REFERENCES.REQUEST_ID;
861 X_PROGRAM_ID:=OLD_REFERENCES.PROGRAM_ID;
862 X_PROGRAM_APPLICATION_ID:=OLD_REFERENCES.PROGRAM_APPLICATION_ID;
863 X_PROGRAM_UPDATE_DATE:=OLD_REFERENCES.PROGRAM_UPDATE_DATE;
864 else
865 X_PROGRAM_UPDATE_DATE:=SYSDATE;
866 end if;
867 else
868 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
869 IGS_GE_MSG_STACK.ADD;
870 app_exception.raise_exception;
871 end if;
872 Before_DML(
873 p_action=>'UPDATE',
874 x_rowid=>X_ROWID,
875 x_ER_ID => X_ER_ID,
876 x_fee_cal_type=>X_FEE_CAL_TYPE,
877 x_fee_cat=>X_FEE_CAT,
878 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
879 x_fee_type=>X_FEE_TYPE,
880 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
881 x_lower_range=>X_LOWER_RANGE,
882 x_range_number=>X_RANGE_NUMBER,
883 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
884 x_s_relation_type=>X_S_RELATION_TYPE,
885 x_upper_range=>X_UPPER_RANGE,
886 x_creation_date=>X_LAST_UPDATE_DATE,
887 x_created_by=>X_LAST_UPDATED_BY,
888 x_last_update_date=>X_LAST_UPDATE_DATE,
889 x_last_updated_by=>X_LAST_UPDATED_BY,
890 x_last_update_login=>X_LAST_UPDATE_LOGIN
891 );
892 update IGS_FI_ELM_RANGE set
893 FEE_CAL_TYPE = NEW_REFERENCES.FEE_CAL_TYPE,
894 FEE_CI_SEQUENCE_NUMBER = NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
895 FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
896 S_RELATION_TYPE = NEW_REFERENCES.S_RELATION_TYPE,
897 RANGE_NUMBER = NEW_REFERENCES.RANGE_NUMBER,
898 FEE_CAT = NEW_REFERENCES.FEE_CAT,
899 LOWER_RANGE = NEW_REFERENCES.LOWER_RANGE,
900 UPPER_RANGE = NEW_REFERENCES.UPPER_RANGE,
901 S_CHG_METHOD_TYPE = NEW_REFERENCES.S_CHG_METHOD_TYPE,
902 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
903 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
904 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
905 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
906 REQUEST_ID=X_REQUEST_ID,
907 PROGRAM_ID=X_PROGRAM_ID,
908 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID,
909 PROGRAM_UPDATE_DATE=X_PROGRAM_UPDATE_DATE
910 where ROWID=X_ROWID
911 ;
912 if (sql%notfound) then
913 raise no_data_found;
914 end if;
915 After_DML(
916 p_action =>'UPDATE',
917 x_rowid => X_ROWID
918 );
919 end UPDATE_ROW;
920 procedure ADD_ROW (
921 X_ROWID in out NOCOPY VARCHAR2,
922 X_ER_ID IN OUT NOCOPY NUMBER,
923 X_FEE_TYPE in VARCHAR2,
924 X_FEE_CAL_TYPE in VARCHAR2,
925 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
926 X_S_RELATION_TYPE in VARCHAR2,
927 X_RANGE_NUMBER in NUMBER,
928 X_FEE_CAT in VARCHAR2,
929 X_LOWER_RANGE in NUMBER,
930 X_UPPER_RANGE in NUMBER,
931 X_S_CHG_METHOD_TYPE in VARCHAR2,
932 X_LOGICAL_DELETE_DT in DATE,
933 X_MODE in VARCHAR2 default 'R'
934 ) AS
935 cursor c1 is select rowid from IGS_FI_ELM_RANGE
936 where ER_ID = X_ER_ID;
937 begin
938 open c1;
939 fetch c1 into X_ROWID;
940 if (c1%notfound) then
941 close c1;
942 INSERT_ROW (
943 X_ROWID,
944 X_ER_ID,
945 X_FEE_TYPE,
946 X_FEE_CAL_TYPE,
947 X_FEE_CI_SEQUENCE_NUMBER,
948 X_S_RELATION_TYPE,
949 X_RANGE_NUMBER,
950 X_FEE_CAT,
951 X_LOWER_RANGE,
952 X_UPPER_RANGE,
953 X_S_CHG_METHOD_TYPE,
954 X_LOGICAL_DELETE_DT,
955 X_MODE);
956 return;
957 end if;
958 close c1;
959 UPDATE_ROW (
960 X_ROWID,
961 X_ER_ID,
962 X_FEE_TYPE,
963 X_FEE_CAL_TYPE,
964 X_FEE_CI_SEQUENCE_NUMBER,
965 X_S_RELATION_TYPE,
966 X_RANGE_NUMBER,
967 X_FEE_CAT,
968 X_LOWER_RANGE,
969 X_UPPER_RANGE,
970 X_S_CHG_METHOD_TYPE,
971 X_LOGICAL_DELETE_DT,
972 X_MODE);
973 end ADD_ROW;
974 procedure DELETE_ROW (
975 X_ROWID in VARCHAR2
976 ) AS
977 begin
978 Before_DML(
979 p_action =>'DELETE',
980 x_rowid => X_ROWID
981 );
982 delete from IGS_FI_ELM_RANGE
983 where ROWID=X_ROWID;
984 if (sql%notfound) then
985 raise no_data_found;
986 end if;
987 end DELETE_ROW;
988 end IGS_FI_EL_RNG_PKG;