1 package body IGS_FI_UNIT_FEE_TRG_PKG AS
2 /* $Header: IGSSI64B.pls 120.6 2006/05/25 08:28:19 abshriva ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_UNIT_FEE_TRG%RowType;
5 new_references IGS_FI_UNIT_FEE_TRG%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_fee_cat IN VARCHAR2 DEFAULT NULL,
10 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
11 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
12 x_fee_type IN VARCHAR2 DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_sequence_number IN NUMBER DEFAULT NULL,
15 x_version_number IN NUMBER DEFAULT NULL,
16 x_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_ci_sequence_number IN NUMBER DEFAULT NULL,
18 x_location_cd IN VARCHAR2 DEFAULT NULL,
19 x_unit_class IN VARCHAR2 DEFAULT NULL,
20 x_create_dt IN DATE DEFAULT NULL,
21 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
22 x_logical_delete_dt IN DATE 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_UNIT_FEE_TRG
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_cat := x_fee_cat;
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.fee_type := x_fee_type;
52 new_references.unit_cd := x_unit_cd;
53 new_references.sequence_number := x_sequence_number;
54 new_references.version_number := x_version_number;
55 new_references.cal_type := x_cal_type;
56 new_references.ci_sequence_number := x_ci_sequence_number;
57 new_references.location_cd := x_location_cd;
58 new_references.unit_class := x_unit_class;
59 new_references.create_dt := x_create_dt;
60 new_references.fee_trigger_group_number := x_fee_trigger_group_number;
61 new_references.logical_delete_dt := x_logical_delete_dt;
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 -- Trigger description :-
76 -- BEFORE INSERT OR UPDATE
77 -- ON IGS_FI_UNIT_FEE_TRG
78 -- FOR EACH ROW
79 PROCEDURE BeforeRowInsertUpdate1(
80 p_inserting IN BOOLEAN DEFAULT FALSE,
81 p_updating IN BOOLEAN DEFAULT FALSE,
82 p_deleting IN BOOLEAN DEFAULT FALSE
83 ) AS
84
85 v_message_name varchar2(30);
86 BEGIN
87 IF p_inserting THEN
88 -- Validate unit fee trigger can be inserted
89 IF IGS_FI_VAL_UFT.finp_val_uft_ins (
90 new_references.fee_type,
91 v_message_name) = FALSE THEN
92 Fnd_Message.Set_Name('IGS',v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 END IF;
97
98 -- Bug# 5070980. Not able to delete Unit Fee Trigger When Unit Version Is Inactive.
99 -- Added a condition to avoid validating unit status while deleting a record.
100 IF NOT (new_references.LOGICAL_DELETE_DT IS NOT NULL AND p_updating ) THEN
101 -- Validate unit version is is active or planned.
102 IF (new_references.unit_cd IS NOT NULL AND
103 new_references.version_number IS NOT NULL) THEN
104 -- As part of the bug# 1956374 changed to the below call from IGS_FI_VAL_UFT.crsp_val_uv_sys_sts
105 IF IGS_PS_VAL_CALUL.crsp_val_uv_sys_sts (
106 new_references.unit_cd,
107 new_references.version_number,
108 v_message_name) = FALSE THEN
109 Fnd_Message.Set_Name('IGS',v_message_name);
110 IGS_GE_MSG_STACK.ADD;
111 App_Exception.Raise_Exception;
112 END IF;
113 END IF;
114 END IF;
115 -- Validate fee trigger group can be defined.
116 IF (new_references.fee_trigger_group_number IS NOT NULL) THEN
117 IF IGS_FI_VAL_UFT.finp_val_uft_ftg (
118 new_references.fee_type,
119 new_references.fee_trigger_group_number,
120 v_message_name) = FALSE THEN
121 Fnd_Message.Set_Name('IGS',v_message_name);
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126 -- Validate calendar type is not closed and is of type teaching.
127 IF (new_references.cal_type IS NOT NULL) THEN
128
129 -- As part of the bug# 1956374 changed to the below call from IGS_FI_VAL_UFT.crsp_val_posp_cat
130 IF IGS_PS_VAL_POSP.crsp_val_posp_cat (
131 new_references.cal_type,
132 v_message_name) = FALSE THEN
133 Fnd_Message.Set_Name('IGS',v_message_name);
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138 -- Validate calendar instance is not inactive.
139 IF (new_references.cal_type IS NOT NULL AND
140 new_references.ci_sequence_number IS NOT NULL) THEN
141 -- BUG #1956374 , Procedure assp_val_ci_status reference is changed
142 IF IGS_AS_VAL_EVSA.assp_val_ci_status (
143 new_references.cal_type,
144 new_references.ci_sequence_number,
145 v_message_name) = FALSE THEN
146 Fnd_Message.Set_Name('IGS',v_message_name);
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151 -- Validate location code is not closed and is of type campus.
152 IF (new_references.location_cd IS NOT NULL) THEN
153 -- As part of the bug# 1956374 changed to the below call from IGS_FI_VAL_UFT.crsp_val_loc_cd
154 IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
155 new_references.location_cd,
156 v_message_name) = FALSE THEN
157 Fnd_Message.Set_Name('IGS',v_message_name);
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END IF;
161 END IF;
162 -- Validate unit class is not closed.
163 IF (new_references.unit_class IS NOT NULL) THEN
164 -- As part of the bug# 1956374 changed to the below call from IGS_FI_VAL_UFT.crsp_val_ucl_closed
165 IF IGS_AS_VAL_UAI.crsp_val_ucl_closed (
166 new_references.unit_class,
167 v_message_name) = FALSE THEN
168 Fnd_Message.Set_Name('IGS',v_message_name);
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173 END BeforeRowInsertUpdate1;
174 -- Trigger description :-
175 -- AFTER UPDATE
176 -- ON IGS_FI_UNIT_FEE_TRG
177 -- FOR EACH ROW
178 PROCEDURE AfterRowUpdate3(
179 p_inserting IN BOOLEAN DEFAULT FALSE,
180 p_updating IN BOOLEAN DEFAULT FALSE,
181 p_deleting IN BOOLEAN DEFAULT FALSE
182 ) AS
183 BEGIN
184 -- create a history
185 IGS_FI_GEN_002.FINP_INS_UFT_HIST( old_references.fee_cat,
186 old_references.fee_cal_type,
187 old_references.fee_ci_sequence_number,
188 old_references.fee_type,
189 old_references.unit_cd,
190 old_references.sequence_number,
191 new_references.version_number,
192 old_references.version_number,
193 new_references.cal_type,
194 old_references.cal_type,
195 new_references.ci_sequence_number,
196 old_references.ci_sequence_number,
197 new_references.location_cd,
198 old_references.location_cd,
199 new_references.unit_class,
200 old_references.unit_class,
201 new_references.create_dt,
202 old_references.create_dt,
203 new_references.fee_trigger_group_number,
204 old_references.fee_trigger_group_number,
205 new_references.last_updated_by,
206 old_references.last_updated_by,
207 new_references.last_update_date,
208 old_references.last_update_date);
209 END AfterRowUpdate3;
210 -- Trigger description :-
211 -- AFTER INSERT OR UPDATE
212 -- ON IGS_FI_UNIT_FEE_TRG
213 PROCEDURE AfterStmtInsertUpdate4(
214 p_inserting IN BOOLEAN DEFAULT FALSE,
215 p_updating IN BOOLEAN DEFAULT FALSE,
216 p_deleting IN BOOLEAN DEFAULT FALSE
217 ) AS
218 v_message_name varchar2(30);
219 v_message_string VARCHAR2(512);
220 BEGIN
221 -- Validate for open ended IGS_FI_UNIT_FEE_TRG records.
222 IF (p_inserting OR p_updating) THEN
223 IF new_references.logical_delete_dt IS NULL THEN
224 IF IGS_FI_VAL_UFT.finp_val_uft_open(
225 new_references.fee_cat,
226 new_references.fee_cal_type,
227 new_references.fee_ci_sequence_number,
228 new_references.fee_type,
229 new_references.unit_cd,
230 new_references.sequence_number,
231 new_references.version_number,
232 new_references.cal_type,
233 new_references.ci_sequence_number,
234 new_references.unit_class,
235 new_references.location_cd,
236 new_references.create_dt,
237 new_references.fee_trigger_group_number,
238 v_message_name) = FALSE THEN
239 Fnd_Message.Set_Name('IGS',v_message_name);
240 IGS_GE_MSG_STACK.ADD;
241 App_Exception.Raise_Exception;
242 END IF;
243 END IF;
244 END IF;
245 END AfterStmtInsertUpdate4;
246 PROCEDURE Check_Constraints (
247 column_name IN VARCHAR2 DEFAULT NULL,
248 column_value IN VARCHAR2 DEFAULT NULL
249 ) AS
250 /*----------------------------------------------------------------------------
251 || Created By :
252 || Created On :
253 || Purpose :
254 || Known limitations, enhancements or remarks :
255 || Change History :
256 || Who When What
257 || (reverse chronological order - newest change first)
258 || vvutukur 18-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
259 ----------------------------------------------------------------------------*/
260 BEGIN
261 IF (column_name IS NULL) THEN
262 NULL;
263 ELSIF (UPPER (column_name) = 'SEQUENCE_NUMBER') THEN
264 new_references.sequence_number := igs_ge_number.To_Num (column_value);
265 ELSIF (UPPER (column_name) = 'CI_SEQUENCE_NUMBER') THEN
266 new_references.ci_sequence_number := igs_ge_number.To_Num (column_value);
267 ELSIF (UPPER (column_name) = 'VERSION_NUMBER') THEN
268 new_references.version_number := igs_ge_number.To_Num (column_value);
269 ELSIF (UPPER (column_name) = 'FEE_TRIGGER_GROUP_NUMBER') THEN
270 new_references.fee_trigger_group_number := igs_ge_number.To_Num (column_value);
271 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
272 new_references.fee_ci_sequence_number := igs_ge_number.To_Num (column_value);
273 ELSIF (UPPER (column_name) = 'CAL_TYPE') THEN
274 new_references.cal_type := column_value;
275 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
276 new_references.fee_cal_type := column_value;
277 ELSIF (UPPER (column_name) = 'LOCATION_CD') THEN
278 new_references.location_cd := column_value;
279 ELSIF (UPPER (column_name) = 'UNIT_CD') THEN
280 new_references.unit_cd := column_value;
281 ELSIF (UPPER (column_name) = 'UNIT_CLASS') THEN
282 new_references.unit_class := column_value;
283 END IF;
284 IF ((UPPER (column_name) = 'SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
285 IF ((new_references.sequence_number < 1) OR (new_references.sequence_number > 999999)) THEN
286 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
287 IGS_GE_MSG_STACK.ADD;
288 App_Exception.Raise_Exception;
289 END IF;
290 END IF;
291 IF ((UPPER (column_name) = 'CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
292 IF ((new_references.ci_sequence_number < 1) OR (new_references.ci_sequence_number > 999999)) THEN
293 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 END IF;
298 IF ((UPPER (column_name) = 'VERSION_NUMBER') OR (column_name IS NULL)) THEN
299 IF ((new_references.version_number < 1) OR (new_references.version_number > 999)) THEN
300 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 END IF;
305 IF ((UPPER (column_name) = 'FEE_TRIGGER_GROUP_NUMBER') OR (column_name IS NULL)) THEN
306 IF ((new_references.fee_trigger_group_number < 1) OR (new_references.fee_trigger_group_number > 999999)) THEN
307 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311 END IF;
312 IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
313 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
314 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 END IF;
318 END IF;
319 IF ((UPPER (column_name) = 'CAL_TYPE') OR (column_name IS NULL)) THEN
320 IF (new_references.cal_type <> UPPER (new_references.cal_type)) THEN
321 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
324 END IF;
325 END IF;
326 IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
327 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
328 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333 IF ((UPPER (column_name) = 'LOCATION_CD') OR (column_name IS NULL)) THEN
334 IF (new_references.location_cd <> UPPER (new_references.location_cd)) THEN
335 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339 END IF;
340 IF ((UPPER (column_name) = 'UNIT_CD') OR (column_name IS NULL)) THEN
341 IF (new_references.unit_cd <> UPPER (new_references.unit_cd)) THEN
342 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
343 IGS_GE_MSG_STACK.ADD;
344 App_Exception.Raise_Exception;
345 END IF;
346 END IF;
347 IF ((UPPER (column_name) = 'UNIT_CLASS') OR (column_name IS NULL)) THEN
348 IF (new_references.unit_class <> UPPER (new_references.unit_class)) THEN
349 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353 END IF;
354 END Check_Constraints;
355 PROCEDURE Check_Uniqueness AS
356 BEGIN
357 IF (Get_UK1_For_Validation (
358 new_references.fee_cat,
359 new_references.fee_cal_type,
360 new_references.fee_ci_sequence_number,
361 new_references.fee_type,
362 new_references.unit_cd,
363 new_references.version_number,
364 new_references.cal_type,
365 new_references.ci_sequence_number,
366 new_references.location_cd,
367 new_references.unit_class,
368 new_references.create_dt
369 )) THEN
370 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 END Check_Uniqueness;
375 PROCEDURE Check_Parent_Existance AS
376 BEGIN
377 IF (((old_references.cal_type = new_references.cal_type)) OR
378 ((new_references.cal_type IS NULL))) THEN
379 NULL;
380 ELSE
381 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
382 new_references.cal_type
383 ) THEN
384 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception;
387 END IF;
388 END IF;
389 IF (((old_references.cal_type = new_references.cal_type) AND
390 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
391 ((new_references.cal_type IS NULL) OR
392 (new_references.ci_sequence_number IS NULL))) THEN
393 NULL;
394 ELSE
395 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
396 new_references.cal_type,
397 new_references.ci_sequence_number
398 ) THEN
399 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
400 IGS_GE_MSG_STACK.ADD;
401 App_Exception.Raise_Exception;
402 END IF;
403 END IF;
404 IF (((old_references.fee_cat = new_references.fee_cat) AND
405 (old_references.fee_cal_type = new_references.fee_cal_type) AND
406 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
407 (old_references.fee_type = new_references.fee_type)) OR
408 ((new_references.fee_cat IS NULL) OR
409 (new_references.fee_cal_type IS NULL) OR
410 (new_references.fee_ci_sequence_number IS NULL) OR
411 (new_references.fee_type IS NULL))) THEN
412 NULL;
413 ELSE
414 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
415 new_references.fee_cat,
416 new_references.fee_cal_type,
417 new_references.fee_ci_sequence_number,
418 new_references.fee_type
419 ) THEN
420 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
421 IGS_GE_MSG_STACK.ADD;
422 App_Exception.Raise_Exception;
423 END IF;
424 END IF;
425 IF (((old_references.fee_cat = new_references.fee_cat) AND
426 (old_references.fee_cal_type = new_references.fee_cal_type) AND
427 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
428 (old_references.fee_type = new_references.fee_type) AND
429 (old_references.fee_trigger_group_number = new_references.fee_trigger_group_number)) OR
430 ((new_references.fee_cat IS NULL) OR
431 (new_references.fee_cal_type IS NULL) OR
432 (new_references.fee_ci_sequence_number IS NULL) OR
433 (new_references.fee_type IS NULL) OR
434 (new_references.fee_trigger_group_number IS NULL))) THEN
435 NULL;
436 ELSE
437 IF NOT IGS_FI_FEE_TRG_GRP_PKG.Get_PK_For_Validation (
438 new_references.fee_cat,
439 new_references.fee_cal_type,
440 new_references.fee_ci_sequence_number,
441 new_references.fee_type,
442 new_references.fee_trigger_group_number
443 ) THEN
444 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
445 IGS_GE_MSG_STACK.ADD;
446 App_Exception.Raise_Exception;
447 END IF;
448 END IF;
449 IF (((old_references.location_cd = new_references.location_cd)) OR
450 ((new_references.location_cd IS NULL))) THEN
451 NULL;
452 ELSE
453 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
454 new_references.location_cd ,
455 'N'
456 ) THEN
457 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
458 IGS_GE_MSG_STACK.ADD;
459 App_Exception.Raise_Exception;
460 END IF;
461 END IF;
462 IF (((old_references.unit_class = new_references.unit_class)) OR
463 ((new_references.unit_class IS NULL))) THEN
464 NULL;
465 ELSE
466 IF NOT IGS_AS_UNIT_CLASS_PKG.Get_PK_For_Validation (
467 new_references.unit_class
468 ) THEN
469 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
470 IGS_GE_MSG_STACK.ADD;
471 App_Exception.Raise_Exception;
472 END IF;
473 END IF;
474 IF (((old_references.unit_cd = new_references.unit_cd)) OR
475 ((new_references.unit_cd IS NULL))) THEN
476 NULL;
477 ELSE
478 IF NOT IGS_PS_UNIT_PKG.Get_PK_For_Validation (
479 new_references.unit_cd
480 ) THEN
481 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
482 IGS_GE_MSG_STACK.ADD;
483 App_Exception.Raise_Exception;
484 END IF;
485 END IF;
486 IF (((old_references.unit_cd = new_references.unit_cd) AND
487 (old_references.version_number = new_references.version_number)) OR
488 ((new_references.unit_cd IS NULL) OR
489 (new_references.version_number IS NULL))) THEN
490 NULL;
491 ELSE
492 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
493 new_references.unit_cd,
494 new_references.version_number
495 ) THEN
496 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
497 IGS_GE_MSG_STACK.ADD;
498 App_Exception.Raise_Exception;
499 END IF;
500 END IF;
501 END Check_Parent_Existance;
502 FUNCTION Get_PK_For_Validation (
503 x_fee_cat IN VARCHAR2,
504 x_fee_cal_type IN VARCHAR2,
505 x_fee_ci_sequence_number IN NUMBER,
506 x_fee_type IN VARCHAR2,
507 x_unit_cd IN VARCHAR2,
508 x_sequence_number IN NUMBER
509 ) RETURN BOOLEAN AS
510 CURSOR cur_rowid IS
511 SELECT rowid
512 FROM IGS_FI_UNIT_FEE_TRG
513 WHERE fee_cat = x_fee_cat
514 AND fee_cal_type = x_fee_cal_type
515 AND fee_ci_sequence_number = x_fee_ci_sequence_number
516 AND fee_type = x_fee_type
517 AND unit_cd = x_unit_cd
518 AND sequence_number = x_sequence_number
519 FOR UPDATE NOWAIT;
520 lv_rowid cur_rowid%RowType;
521 BEGIN
522 Open cur_rowid;
523 Fetch cur_rowid INTO lv_rowid;
524 IF (cur_rowid%FOUND) THEN
525 Close cur_rowid;
526 Return (TRUE);
527 ELSE
528 Close cur_rowid;
529 Return (FALSE);
530 END IF;
531 END Get_PK_For_Validation;
532 FUNCTION Get_UK1_For_Validation (
533 x_fee_cat IN VARCHAR2,
534 x_fee_cal_type IN VARCHAR2,
535 x_fee_ci_sequence_number IN NUMBER,
536 x_fee_type IN VARCHAR2,
537 x_unit_cd IN VARCHAR2,
538 x_version_number IN NUMBER,
539 x_cal_type IN VARCHAR2,
540 x_ci_sequence_number IN NUMBER,
541 x_location_cd IN VARCHAR2,
542 x_unit_class IN VARCHAR2,
543 x_create_dt IN DATE
544 ) RETURN BOOLEAN AS
545 CURSOR cur_rowid IS
546 SELECT rowid
547 FROM IGS_FI_UNIT_FEE_TRG
548 WHERE fee_cat = x_fee_cat
549 AND fee_cal_type = x_fee_cal_type
550 AND fee_ci_sequence_number = x_fee_ci_sequence_number
551 AND fee_type = x_fee_type
552 AND unit_cd = x_unit_cd
553 AND version_number = x_version_number
554 AND cal_type = x_cal_type
555 AND ci_sequence_number = x_ci_sequence_number
556 AND location_cd = x_location_cd
557 AND unit_class = x_unit_class
558 AND create_dt = x_create_dt
559 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
560 FOR UPDATE NOWAIT;
561 lv_rowid cur_rowid%RowType;
562 BEGIN
563 Open cur_rowid;
564 Fetch cur_rowid INTO lv_rowid;
565 IF (cur_rowid%FOUND) THEN
566 Close cur_rowid;
567 Return (TRUE);
568 ELSE
569 Close cur_rowid;
570 Return (FALSE);
571 END IF;
572 END Get_UK1_For_Validation;
573 PROCEDURE GET_FK_IGS_CA_TYPE (
574 x_cal_type IN VARCHAR2
575 ) AS
576 CURSOR cur_rowid IS
577 SELECT rowid
578 FROM IGS_FI_UNIT_FEE_TRG
579 WHERE cal_type = x_cal_type ;
580 lv_rowid cur_rowid%RowType;
581 BEGIN
582 Open cur_rowid;
583 Fetch cur_rowid INTO lv_rowid;
584 IF (cur_rowid%FOUND) THEN
585 Close cur_rowid;
586 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_CAT_FK');
587 IGS_GE_MSG_STACK.ADD;
588 App_Exception.Raise_Exception;
589 Return;
590 END IF;
591 Close cur_rowid;
592 END GET_FK_IGS_CA_TYPE;
593 PROCEDURE GET_FK_IGS_CA_INST (
594 x_cal_type IN VARCHAR2,
595 x_sequence_number IN NUMBER
596 ) AS
597 CURSOR cur_rowid IS
598 SELECT rowid
599 FROM IGS_FI_UNIT_FEE_TRG
600 WHERE cal_type = x_cal_type
601 AND ci_sequence_number = x_sequence_number ;
602 lv_rowid cur_rowid%RowType;
603 BEGIN
604 Open cur_rowid;
605 Fetch cur_rowid INTO lv_rowid;
606 IF (cur_rowid%FOUND) THEN
607 Close cur_rowid;
608 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_CI_FK');
609 IGS_GE_MSG_STACK.ADD;
610 App_Exception.Raise_Exception;
611 Return;
612 END IF;
613 Close cur_rowid;
614 END GET_FK_IGS_CA_INST;
615
616 PROCEDURE GET_FK_IGS_FI_FEE_TRG_GRP (
617 x_fee_cat IN VARCHAR2,
618 x_fee_cal_type IN VARCHAR2,
619 x_fee_ci_sequence_number IN NUMBER,
620 x_fee_type IN VARCHAR2,
621 x_fee_trigger_group_number IN NUMBER
622 ) AS
623 CURSOR cur_rowid IS
624 SELECT rowid
625 FROM IGS_FI_UNIT_FEE_TRG
626 WHERE fee_cat = x_fee_cat
627 AND fee_cal_type = x_fee_cal_type
628 AND fee_ci_sequence_number = x_fee_ci_sequence_number
629 AND fee_type = x_fee_type
630 AND fee_trigger_group_number = x_fee_trigger_group_number ;
631 lv_rowid cur_rowid%RowType;
632 BEGIN
633 Open cur_rowid;
634 Fetch cur_rowid INTO lv_rowid;
635 IF (cur_rowid%FOUND) THEN
636 Close cur_rowid;
637 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_FTG_FK');
638 IGS_GE_MSG_STACK.ADD;
639 App_Exception.Raise_Exception;
640 Return;
641 END IF;
642 Close cur_rowid;
643 END GET_FK_IGS_FI_FEE_TRG_GRP;
644 PROCEDURE GET_FK_IGS_AD_LOCATION (
645 x_location_cd IN VARCHAR2
646 ) AS
647 CURSOR cur_rowid IS
648 SELECT rowid
649 FROM IGS_FI_UNIT_FEE_TRG
650 WHERE location_cd = x_location_cd ;
651 lv_rowid cur_rowid%RowType;
652 BEGIN
653 Open cur_rowid;
654 Fetch cur_rowid INTO lv_rowid;
655 IF (cur_rowid%FOUND) THEN
656 Close cur_rowid;
657 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_LOC_FK');
658 IGS_GE_MSG_STACK.ADD;
659 App_Exception.Raise_Exception;
660 Return;
661 END IF;
662 Close cur_rowid;
663 END GET_FK_IGS_AD_LOCATION;
664 PROCEDURE GET_FK_IGS_PS_UNIT (
665 x_unit_cd IN VARCHAR2
666 ) AS
667 CURSOR cur_rowid IS
668 SELECT rowid
669 FROM IGS_FI_UNIT_FEE_TRG
670 WHERE unit_cd = x_unit_cd ;
671 lv_rowid cur_rowid%RowType;
672 BEGIN
673 Open cur_rowid;
674 Fetch cur_rowid INTO lv_rowid;
675 IF (cur_rowid%FOUND) THEN
676 Close cur_rowid;
677 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_UN_FK');
678 IGS_GE_MSG_STACK.ADD;
679 App_Exception.Raise_Exception;
680 Return;
681 END IF;
682 Close cur_rowid;
683 END GET_FK_IGS_PS_UNIT;
684 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
685 x_unit_cd IN VARCHAR2,
686 x_version_number IN NUMBER
687 ) AS
688 CURSOR cur_rowid IS
689 SELECT rowid
690 FROM IGS_FI_UNIT_FEE_TRG
691 WHERE unit_cd = x_unit_cd
692 AND version_number = x_version_number ;
693 lv_rowid cur_rowid%RowType;
694 BEGIN
695 Open cur_rowid;
696 Fetch cur_rowid INTO lv_rowid;
697 IF (cur_rowid%FOUND) THEN
698 Close cur_rowid;
699 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UFT_UV_FK');
700 IGS_GE_MSG_STACK.ADD;
701 App_Exception.Raise_Exception;
702 Return;
703 END IF;
704 Close cur_rowid;
705 END GET_FK_IGS_PS_UNIT_VER;
706 PROCEDURE Before_DML (
707 p_action IN VARCHAR2,
708 x_rowid IN VARCHAR2 DEFAULT NULL,
709 x_fee_cat IN VARCHAR2 DEFAULT NULL,
710 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
711 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
712 x_fee_type IN VARCHAR2 DEFAULT NULL,
713 x_unit_cd IN VARCHAR2 DEFAULT NULL,
714 x_sequence_number IN NUMBER DEFAULT NULL,
715 x_version_number IN NUMBER DEFAULT NULL,
716 x_cal_type IN VARCHAR2 DEFAULT NULL,
717 x_ci_sequence_number IN NUMBER DEFAULT NULL,
718 x_location_cd IN VARCHAR2 DEFAULT NULL,
719 x_unit_class IN VARCHAR2 DEFAULT NULL,
720 x_create_dt IN DATE DEFAULT NULL,
721 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
722 x_logical_delete_dt IN DATE DEFAULT NULL,
723 x_creation_date IN DATE DEFAULT NULL,
724 x_created_by IN NUMBER DEFAULT NULL,
725 x_last_update_date IN DATE DEFAULT NULL,
726 x_last_updated_by IN NUMBER DEFAULT NULL,
727 x_last_update_login IN NUMBER DEFAULT NULL
728 ) AS
729 BEGIN
730 Set_Column_Values (
731 p_action,
732 x_rowid,
733 x_fee_cat,
734 x_fee_cal_type,
735 x_fee_ci_sequence_number,
736 x_fee_type,
737 x_unit_cd,
738 x_sequence_number,
739 x_version_number,
740 x_cal_type,
741 x_ci_sequence_number,
742 x_location_cd,
743 x_unit_class,
744 x_create_dt,
745 x_fee_trigger_group_number,
746 x_logical_delete_dt,
747 x_creation_date,
748 x_created_by,
749 x_last_update_date,
750 x_last_updated_by,
751 x_last_update_login
752 );
753 IF (p_action = 'INSERT') THEN
754 -- Call all the procedures related to Before Insert.
755 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
756 IF (Get_PK_For_Validation (
757 new_references.fee_cat,
758 new_references.fee_cal_type,
759 new_references.fee_ci_sequence_number,
760 new_references.fee_type,
761 new_references.unit_cd,
762 new_references.sequence_number
763 )) THEN
764 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
765 IGS_GE_MSG_STACK.ADD;
766 App_Exception.Raise_Exception;
767 END IF;
768 Check_Uniqueness;
769 Check_Constraints;
770 Check_Parent_Existance;
771 ELSIF (p_action = 'UPDATE') THEN
772 -- Call all the procedures related to Before Update.
773 BeforeRowInsertUpdate1 ( p_updating => TRUE );
774 Check_Uniqueness;
775 Check_Constraints;
776 Check_Parent_Existance;
777 ELSIF (p_action = 'VALIDATE_INSERT') THEN
778 IF (Get_PK_For_Validation (
779 new_references.fee_cat,
780 new_references.fee_cal_type,
781 new_references.fee_ci_sequence_number,
782 new_references.fee_type,
783 new_references.unit_cd,
784 new_references.sequence_number
785 )) THEN
786 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
787 IGS_GE_MSG_STACK.ADD;
788 App_Exception.Raise_Exception;
789 END IF;
790 Check_Uniqueness;
791 Check_Constraints;
792 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
793 Check_Uniqueness;
794 Check_Constraints;
795 END IF;
796 END Before_DML;
797 PROCEDURE After_DML (
798 p_action IN VARCHAR2,
799 x_rowid IN VARCHAR2
800 ) AS
801 BEGIN
802 l_rowid := x_rowid;
803 IF (p_action = 'INSERT') THEN
804 -- Call all the procedures related to After Insert.
805 AfterStmtInsertUpdate4 ( p_inserting => TRUE );
806 ELSIF (p_action = 'UPDATE') THEN
807 -- Call all the procedures related to After Update.
808 AfterRowUpdate3 ( p_updating => TRUE );
809 AfterStmtInsertUpdate4 ( p_updating => TRUE );
810 END IF;
811 END After_DML;
812 procedure INSERT_ROW (
813 X_ROWID in out NOCOPY VARCHAR2,
814 X_FEE_CAT in VARCHAR2,
815 X_FEE_CAL_TYPE in VARCHAR2,
816 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
817 X_UNIT_CD in VARCHAR2,
818 X_SEQUENCE_NUMBER in NUMBER,
819 X_FEE_TYPE in VARCHAR2,
820 X_VERSION_NUMBER in NUMBER,
821 X_CAL_TYPE in VARCHAR2,
822 X_CI_SEQUENCE_NUMBER in NUMBER,
823 X_LOCATION_CD in VARCHAR2,
824 X_UNIT_CLASS in VARCHAR2,
825 X_CREATE_DT in DATE,
826 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
827 X_LOGICAL_DELETE_DT in DATE,
828 X_MODE in VARCHAR2 default 'R'
829 ) AS
830 cursor C is select ROWID from IGS_FI_UNIT_FEE_TRG
831 where FEE_CAT = X_FEE_CAT
832 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
833 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
834 and UNIT_CD = X_UNIT_CD
835 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
836 and FEE_TYPE = X_FEE_TYPE;
837 X_LAST_UPDATE_DATE DATE;
838 X_LAST_UPDATED_BY NUMBER;
839 X_LAST_UPDATE_LOGIN NUMBER;
840 begin
841 X_LAST_UPDATE_DATE := SYSDATE;
842 if(X_MODE = 'I') then
843 X_LAST_UPDATED_BY := 1;
844 X_LAST_UPDATE_LOGIN := 0;
845 elsif (X_MODE = 'R') then
846 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
847 if X_LAST_UPDATED_BY is NULL then
848 X_LAST_UPDATED_BY := -1;
849 end if;
850 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
851 if X_LAST_UPDATE_LOGIN is NULL then
852 X_LAST_UPDATE_LOGIN := -1;
853 end if;
854 else
855 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
856 IGS_GE_MSG_STACK.ADD;
857 app_exception.raise_exception;
858 end if;
859 Before_DML(
860 p_action=>'INSERT',
861 x_rowid=>X_ROWID,
862 x_cal_type=>X_CAL_TYPE,
863 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
864 x_create_dt=>NVL(X_CREATE_DT,sysdate),
865 x_fee_cal_type=>X_FEE_CAL_TYPE,
866 x_fee_cat=>X_FEE_CAT,
867 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
868 x_fee_trigger_group_number=>X_FEE_TRIGGER_GROUP_NUMBER,
869 x_fee_type=>X_FEE_TYPE,
870 x_location_cd=>X_LOCATION_CD,
871 x_sequence_number=>X_SEQUENCE_NUMBER,
872 x_unit_cd=>X_UNIT_CD,
873 x_unit_class=>X_UNIT_CLASS,
874 x_version_number=>X_VERSION_NUMBER,
875 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
876 x_creation_date=>X_LAST_UPDATE_DATE,
877 x_created_by=>X_LAST_UPDATED_BY,
878 x_last_update_date=>X_LAST_UPDATE_DATE,
879 x_last_updated_by=>X_LAST_UPDATED_BY,
880 x_last_update_login=>X_LAST_UPDATE_LOGIN
881 );
882 insert into IGS_FI_UNIT_FEE_TRG (
883 FEE_CAT,
884 FEE_CAL_TYPE,
885 FEE_CI_SEQUENCE_NUMBER,
886 FEE_TYPE,
887 UNIT_CD,
888 SEQUENCE_NUMBER,
889 VERSION_NUMBER,
890 CAL_TYPE,
891 CI_SEQUENCE_NUMBER,
892 LOCATION_CD,
893 UNIT_CLASS,
894 CREATE_DT,
895 FEE_TRIGGER_GROUP_NUMBER,
896 LOGICAL_DELETE_DT,
897 CREATION_DATE,
898 CREATED_BY,
899 LAST_UPDATE_DATE,
900 LAST_UPDATED_BY,
901 LAST_UPDATE_LOGIN
902 ) values (
903 NEW_REFERENCES.FEE_CAT,
904 NEW_REFERENCES.FEE_CAL_TYPE,
905 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
906 NEW_REFERENCES.FEE_TYPE,
907 NEW_REFERENCES.UNIT_CD,
908 NEW_REFERENCES.SEQUENCE_NUMBER,
909 NEW_REFERENCES.VERSION_NUMBER,
910 NEW_REFERENCES.CAL_TYPE,
911 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
912 NEW_REFERENCES.LOCATION_CD,
913 NEW_REFERENCES.UNIT_CLASS,
914 NEW_REFERENCES.CREATE_DT,
915 NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
916 NEW_REFERENCES.LOGICAL_DELETE_DT,
917 X_LAST_UPDATE_DATE,
918 X_LAST_UPDATED_BY,
919 X_LAST_UPDATE_DATE,
920 X_LAST_UPDATED_BY,
921 X_LAST_UPDATE_LOGIN
922 );
923 open c;
924 fetch c into X_ROWID;
925 if (c%notfound) then
926 close c;
927 raise no_data_found;
928 end if;
929 close c;
930 end INSERT_ROW;
931 procedure LOCK_ROW (
932 X_ROWID in VARCHAR2,
933 X_FEE_CAT in VARCHAR2,
934 X_FEE_CAL_TYPE in VARCHAR2,
935 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
936 X_UNIT_CD in VARCHAR2,
937 X_SEQUENCE_NUMBER in NUMBER,
938 X_FEE_TYPE in VARCHAR2,
939 X_VERSION_NUMBER in NUMBER,
940 X_CAL_TYPE in VARCHAR2,
941 X_CI_SEQUENCE_NUMBER in NUMBER,
942 X_LOCATION_CD in VARCHAR2,
943 X_UNIT_CLASS in VARCHAR2,
944 X_CREATE_DT in DATE,
945 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
946 X_LOGICAL_DELETE_DT in DATE
947 ) AS
948 cursor c1 is select
949 VERSION_NUMBER,
950 CAL_TYPE,
951 CI_SEQUENCE_NUMBER,
952 LOCATION_CD,
953 UNIT_CLASS,
954 CREATE_DT,
955 FEE_TRIGGER_GROUP_NUMBER,
956 LOGICAL_DELETE_DT
957 from IGS_FI_UNIT_FEE_TRG
958 where ROWID=X_ROWID
959 for update nowait;
960 tlinfo c1%rowtype;
961 begin
962 open c1;
963 fetch c1 into tlinfo;
964 if (c1%notfound) then
965 close c1;
966 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
967 IGS_GE_MSG_STACK.ADD;
968 app_exception.raise_exception;
969 return;
970 end if;
971 close c1;
972 if ( ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
973 OR ((tlinfo.VERSION_NUMBER is null)
974 AND (X_VERSION_NUMBER is null)))
975 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
976 OR ((tlinfo.CAL_TYPE is null)
977 AND (X_CAL_TYPE is null)))
978 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
979 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
980 AND (X_CI_SEQUENCE_NUMBER is null)))
981 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
982 OR ((tlinfo.LOCATION_CD is null)
983 AND (X_LOCATION_CD is null)))
984 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
985 OR ((tlinfo.UNIT_CLASS is null)
986 AND (X_UNIT_CLASS is null)))
987 AND (tlinfo.CREATE_DT = X_CREATE_DT)
988 AND ((tlinfo.FEE_TRIGGER_GROUP_NUMBER = X_FEE_TRIGGER_GROUP_NUMBER)
989 OR ((tlinfo.FEE_TRIGGER_GROUP_NUMBER is null)
990 AND (X_FEE_TRIGGER_GROUP_NUMBER is null)))
991 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
992 OR ((tlinfo.LOGICAL_DELETE_DT is null)
993 AND (X_LOGICAL_DELETE_DT is null)))
994 ) then
995 null;
996 else
997 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
998 IGS_GE_MSG_STACK.ADD;
999 app_exception.raise_exception;
1000 end if;
1001 return;
1002 end LOCK_ROW;
1003 procedure UPDATE_ROW (
1004 X_ROWID in VARCHAR2,
1005 X_FEE_CAT in VARCHAR2,
1006 X_FEE_CAL_TYPE in VARCHAR2,
1007 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1008 X_UNIT_CD in VARCHAR2,
1009 X_SEQUENCE_NUMBER in NUMBER,
1010 X_FEE_TYPE in VARCHAR2,
1011 X_VERSION_NUMBER in NUMBER,
1012 X_CAL_TYPE in VARCHAR2,
1013 X_CI_SEQUENCE_NUMBER in NUMBER,
1014 X_LOCATION_CD in VARCHAR2,
1015 X_UNIT_CLASS in VARCHAR2,
1016 X_CREATE_DT in DATE,
1017 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
1018 X_LOGICAL_DELETE_DT in DATE,
1019 X_MODE in VARCHAR2 default 'R'
1020 ) AS
1021 X_LAST_UPDATE_DATE DATE;
1022 X_LAST_UPDATED_BY NUMBER;
1023 X_LAST_UPDATE_LOGIN NUMBER;
1024 begin
1025 X_LAST_UPDATE_DATE := SYSDATE;
1026 if(X_MODE = 'I') then
1027 X_LAST_UPDATED_BY := 1;
1028 X_LAST_UPDATE_LOGIN := 0;
1029 elsif (X_MODE = 'R') then
1030 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1031 if X_LAST_UPDATED_BY is NULL then
1032 X_LAST_UPDATED_BY := -1;
1033 end if;
1034 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1035 if X_LAST_UPDATE_LOGIN is NULL then
1036 X_LAST_UPDATE_LOGIN := -1;
1037 end if;
1038 else
1039 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1040 IGS_GE_MSG_STACK.ADD;
1041 app_exception.raise_exception;
1042 end if;
1043 Before_DML(
1044 p_action=>'UPDATE',
1045 x_rowid=>X_ROWID,
1046 x_cal_type=>X_CAL_TYPE,
1047 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
1048 x_create_dt=>X_CREATE_DT,
1049 x_fee_cal_type=>X_FEE_CAL_TYPE,
1050 x_fee_cat=>X_FEE_CAT,
1051 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
1052 x_fee_trigger_group_number=>X_FEE_TRIGGER_GROUP_NUMBER,
1053 x_fee_type=>X_FEE_TYPE,
1054 x_location_cd=>X_LOCATION_CD,
1055 x_sequence_number=>X_SEQUENCE_NUMBER,
1056 x_unit_cd=>X_UNIT_CD,
1057 x_unit_class=>X_UNIT_CLASS,
1058 x_version_number=>X_VERSION_NUMBER,
1059 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
1060 x_creation_date=>X_LAST_UPDATE_DATE,
1061 x_created_by=>X_LAST_UPDATED_BY,
1062 x_last_update_date=>X_LAST_UPDATE_DATE,
1063 x_last_updated_by=>X_LAST_UPDATED_BY,
1064 x_last_update_login=>X_LAST_UPDATE_LOGIN
1065 );
1066 update IGS_FI_UNIT_FEE_TRG set
1067 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
1068 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
1069 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
1070 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1071 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
1072 CREATE_DT = NEW_REFERENCES.CREATE_DT,
1073 FEE_TRIGGER_GROUP_NUMBER = NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
1074 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
1075 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1076 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1077 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1078 where ROWID=X_ROWID
1079 ;
1080 if (sql%notfound) then
1081 raise no_data_found;
1082 end if;
1083 After_DML(
1084 p_action =>'UPDATE',
1085 x_rowid => X_ROWID
1086 );
1087 end UPDATE_ROW;
1088 procedure ADD_ROW (
1089 X_ROWID in out NOCOPY VARCHAR2,
1090 X_FEE_CAT in VARCHAR2,
1091 X_FEE_CAL_TYPE in VARCHAR2,
1092 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1093 X_UNIT_CD in VARCHAR2,
1094 X_SEQUENCE_NUMBER in NUMBER,
1095 X_FEE_TYPE in VARCHAR2,
1096 X_VERSION_NUMBER in NUMBER,
1097 X_CAL_TYPE in VARCHAR2,
1098 X_CI_SEQUENCE_NUMBER in NUMBER,
1099 X_LOCATION_CD in VARCHAR2,
1100 X_UNIT_CLASS in VARCHAR2,
1101 X_CREATE_DT in DATE,
1102 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
1103 X_LOGICAL_DELETE_DT in DATE,
1104 X_MODE in VARCHAR2 default 'R'
1105 ) AS
1106 cursor c1 is select rowid from IGS_FI_UNIT_FEE_TRG
1107 where FEE_CAT = X_FEE_CAT
1108 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
1109 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
1110 and UNIT_CD = X_UNIT_CD
1111 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1112 and FEE_TYPE = X_FEE_TYPE
1113 ;
1114 begin
1115 open c1;
1116 fetch c1 into X_ROWID;
1117 if (c1%notfound) then
1118 close c1;
1119 INSERT_ROW (
1120 X_ROWID,
1121 X_FEE_CAT,
1122 X_FEE_CAL_TYPE,
1123 X_FEE_CI_SEQUENCE_NUMBER,
1124 X_UNIT_CD,
1125 X_SEQUENCE_NUMBER,
1126 X_FEE_TYPE,
1127 X_VERSION_NUMBER,
1128 X_CAL_TYPE,
1129 X_CI_SEQUENCE_NUMBER,
1130 X_LOCATION_CD,
1131 X_UNIT_CLASS,
1132 X_CREATE_DT,
1133 X_FEE_TRIGGER_GROUP_NUMBER,
1134 X_LOGICAL_DELETE_DT,
1135 X_MODE);
1136 return;
1137 end if;
1138 close c1;
1139 UPDATE_ROW (
1140 X_ROWID,
1141 X_FEE_CAT,
1142 X_FEE_CAL_TYPE,
1143 X_FEE_CI_SEQUENCE_NUMBER,
1144 X_UNIT_CD,
1145 X_SEQUENCE_NUMBER,
1146 X_FEE_TYPE,
1147 X_VERSION_NUMBER,
1148 X_CAL_TYPE,
1149 X_CI_SEQUENCE_NUMBER,
1150 X_LOCATION_CD,
1151 X_UNIT_CLASS,
1152 X_CREATE_DT,
1153 X_FEE_TRIGGER_GROUP_NUMBER,
1154 X_LOGICAL_DELETE_DT,
1155 X_MODE);
1156 end ADD_ROW;
1157 procedure DELETE_ROW (
1158 X_ROWID in VARCHAR2
1159 ) AS
1160 begin
1161 delete from IGS_FI_UNIT_FEE_TRG
1162 where ROWID=X_ROWID;
1163 if (sql%notfound) then
1164 raise no_data_found;
1165 end if;
1166 end DELETE_ROW;
1167 end IGS_FI_UNIT_FEE_TRG_PKG;
[Home] [Help]