[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_UNIT_FEE_TR_H_PKG
Source
1 package body IGS_FI_UNIT_FEE_TR_H_PKG AS
2 /* $Header: IGSSI65B.pls 115.8 2003/02/12 10:19:15 shtatiko ship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_UNIT_FEE_TR_H_ALL%RowType;
5 new_references IGS_FI_UNIT_FEE_TR_H_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_location_cd IN VARCHAR2 DEFAULT NULL,
10 x_unit_class IN VARCHAR2 DEFAULT NULL,
11 x_create_dt IN DATE DEFAULT NULL,
12 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
13 x_fee_cat IN VARCHAR2 DEFAULT NULL,
14 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_fee_type IN VARCHAR2 DEFAULT NULL,
17 x_unit_cd IN VARCHAR2 DEFAULT NULL,
18 x_sequence_number IN NUMBER DEFAULT NULL,
19 x_hist_start_dt IN DATE DEFAULT NULL,
20 x_hist_end_dt IN DATE DEFAULT NULL,
21 x_hist_who IN VARCHAR2 DEFAULT NULL,
22 x_version_number IN NUMBER DEFAULT NULL,
23 x_cal_type IN VARCHAR2 DEFAULT NULL,
24 x_ci_sequence_number IN NUMBER DEFAULT NULL,
25 x_org_id IN NUMBER DEFAULT NULL,
26 x_creation_date IN DATE DEFAULT NULL,
27 x_created_by IN NUMBER DEFAULT NULL,
28 x_last_update_date IN DATE DEFAULT NULL,
29 x_last_updated_by IN NUMBER DEFAULT NULL,
30 x_last_update_login IN NUMBER DEFAULT NULL
31 ) AS
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_FI_UNIT_FEE_TR_H_ALL
35 WHERE rowid = x_rowid;
36 BEGIN
37 l_rowid := x_rowid;
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50 -- Populate New Values.
51 new_references.location_cd := x_location_cd;
52 new_references.unit_class := x_unit_class;
53 new_references.create_dt := x_create_dt;
54 new_references.fee_trigger_group_number := x_fee_trigger_group_number;
55 new_references.fee_cat := x_fee_cat;
56 new_references.fee_cal_type := x_fee_cal_type;
57 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
58 new_references.fee_type := x_fee_type;
59 new_references.unit_cd := x_unit_cd;
60 new_references.sequence_number := x_sequence_number;
61 new_references.hist_start_dt := x_hist_start_dt;
62 new_references.hist_end_dt := x_hist_end_dt;
63 new_references.hist_who := x_hist_who;
64 new_references.version_number := x_version_number;
65 new_references.cal_type := x_cal_type;
66 new_references.ci_sequence_number := x_ci_sequence_number;
67 new_references.org_id := x_org_id ;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78 END Set_Column_Values;
79 PROCEDURE Check_Constraints (
80 column_name IN VARCHAR2 DEFAULT NULL,
81 column_value IN VARCHAR2 DEFAULT NULL
82 ) AS
83 /*----------------------------------------------------------------------------
84 || Created By :
85 || Created On :
86 || Purpose :
87 || Known limitations, enhancements or remarks :
88 || Change History :
89 || Who When What
90 || (reverse chronological order - newest change first)
91 || vvutukur 18-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
92 ----------------------------------------------------------------------------*/
93 BEGIN
94 IF (column_name IS NULL) THEN
95 NULL;
96 ELSIF (UPPER (column_name) = 'SEQUENCE_NUMBER') THEN
97 new_references.sequence_number := igs_ge_number.To_Num (column_value);
98 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
99 new_references.fee_ci_sequence_number := igs_ge_number.To_Num (column_value);
100 ELSIF (UPPER (column_name) = 'FEE_TRIGGER_GROUP_NUMBER') THEN
101 new_references.fee_trigger_group_number := igs_ge_number.To_Num (column_value);
102 ELSIF (UPPER (column_name) = 'CI_SEQUENCE_NUMBER') THEN
103 new_references.ci_sequence_number := igs_ge_number.To_Num (column_value);
104 ELSIF (UPPER (column_name) = 'VERSION_NUMBER') THEN
105 new_references.version_number := igs_ge_number.To_Num (column_value);
106 ELSIF (UPPER (column_name) = 'CAL_TYPE') THEN
107 new_references.cal_type := column_value;
108 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
109 new_references.fee_cal_type := column_value;
110 ELSIF (UPPER (column_name) = 'LOCATION_CD') THEN
111 new_references.location_cd := column_value;
112 ELSIF (UPPER (column_name) = 'UNIT_CD') THEN
113 new_references.unit_cd := column_value;
114 ELSIF (UPPER (column_name) = 'UNIT_CLASS') THEN
115 new_references.unit_class := column_value;
116 END IF;
117 IF ((UPPER (column_name) = 'SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
118 IF ((new_references.sequence_number < 1) OR (new_references.sequence_number > 999999)) THEN
119 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124 IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
125 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
126 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131 IF ((UPPER (column_name) = 'FEE_TRIGGER_GROUP_NUMBER') OR (column_name IS NULL)) THEN
132 IF ((new_references.fee_trigger_group_number < 1) OR (new_references.fee_trigger_group_number > 999999)) THEN
133 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138 IF ((UPPER (column_name) = 'CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
139 IF ((new_references.ci_sequence_number < 1) OR (new_references.ci_sequence_number > 999999)) THEN
140 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144 END IF;
145 IF ((UPPER (column_name) = 'VERSION_NUMBER') OR (column_name IS NULL)) THEN
146 IF ((new_references.version_number < 1) OR (new_references.version_number > 999)) THEN
147 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 IF ((UPPER (column_name) = 'CAL_TYPE') OR (column_name IS NULL)) THEN
153 IF (new_references.cal_type <> UPPER (new_references.cal_type)) THEN
154 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159 IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
160 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
161 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
162 IGS_GE_MSG_STACK.ADD;
163 App_Exception.Raise_Exception;
164 END IF;
165 END IF;
166 IF ((UPPER (column_name) = 'LOCATION_CD') OR (column_name IS NULL)) THEN
167 IF (new_references.location_cd <> UPPER (new_references.location_cd)) THEN
168 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173 IF ((UPPER (column_name) = 'UNIT_CD') OR (column_name IS NULL)) THEN
174 IF (new_references.unit_cd <> UPPER (new_references.unit_cd)) THEN
175 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180 IF ((UPPER (column_name) = 'UNIT_CLASS') OR (column_name IS NULL)) THEN
181 IF (new_references.unit_class <> UPPER (new_references.unit_class)) THEN
182 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 END IF;
187 END Check_Constraints;
188 PROCEDURE Check_Uniqueness AS
189 BEGIN
190 IF (Get_UK1_For_Validation (
191 new_references.fee_cat,
192 new_references.fee_cal_type,
193 new_references.fee_ci_sequence_number,
194 new_references.fee_type,
195 new_references.unit_cd,
196 new_references.version_number,
197 new_references.cal_type,
198 new_references.ci_sequence_number,
199 new_references.location_cd,
200 new_references.unit_class,
201 new_references.create_dt,
202 new_references.hist_start_dt
203 )) THEN
204 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208 END Check_Uniqueness;
209 PROCEDURE Check_Parent_Existance AS
210 BEGIN
211 IF (((old_references.fee_cat = new_references.fee_cat) AND
212 (old_references.fee_cal_type = new_references.fee_cal_type) AND
213 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
214 (old_references.fee_type = new_references.fee_type)) OR
215 ((new_references.fee_cat IS NULL) OR
216 (new_references.fee_cal_type IS NULL) OR
217 (new_references.fee_ci_sequence_number IS NULL) OR
218 (new_references.fee_type IS NULL))) THEN
219 NULL;
220 ELSE
221 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
222 new_references.fee_cat,
223 new_references.fee_cal_type,
224 new_references.fee_ci_sequence_number,
225 new_references.fee_type
226 ) THEN
227 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
228 IGS_GE_MSG_STACK.ADD;
229 App_Exception.Raise_Exception;
230 END IF;
231 END IF;
232 END Check_Parent_Existance;
233 FUNCTION Get_PK_For_Validation (
234 x_fee_cat IN VARCHAR2,
235 x_fee_cal_type IN VARCHAR2,
236 x_fee_ci_sequence_number IN NUMBER,
237 x_fee_type IN VARCHAR2,
238 x_unit_cd IN VARCHAR2,
239 x_sequence_number IN NUMBER,
240 x_hist_start_dt IN DATE
241 ) RETURN BOOLEAN AS
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM IGS_FI_UNIT_FEE_TR_H_ALL
245 WHERE fee_cat = x_fee_cat
246 AND fee_cal_type = x_fee_cal_type
247 AND fee_ci_sequence_number = x_fee_ci_sequence_number
248 AND fee_type = x_fee_type
249 AND unit_cd = x_unit_cd
250 AND sequence_number = x_sequence_number
251 AND hist_start_dt = x_hist_start_dt
252 FOR UPDATE NOWAIT;
253 lv_rowid cur_rowid%RowType;
254 BEGIN
255 Open cur_rowid;
256 Fetch cur_rowid INTO lv_rowid;
257 IF (cur_rowid%FOUND) THEN
258 Close cur_rowid;
259 Return (TRUE);
260 ELSE
261 Close cur_rowid;
262 Return (FALSE);
263 END IF;
264 END Get_PK_For_Validation;
265 FUNCTION Get_UK1_For_Validation (
266 x_fee_cat IN VARCHAR2,
267 x_fee_cal_type IN VARCHAR2,
268 x_fee_ci_sequence_number IN NUMBER,
269 x_fee_type IN VARCHAR2,
270 x_unit_cd IN VARCHAR2,
271 x_version_number IN NUMBER,
272 x_cal_type IN VARCHAR2,
273 x_ci_sequence_number IN NUMBER,
274 x_location_cd IN VARCHAR2,
275 x_unit_class IN VARCHAR2,
276 x_create_dt IN DATE,
277 x_hist_start_dt IN DATE
278 ) RETURN BOOLEAN AS
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM IGS_FI_UNIT_FEE_TR_H_ALL
282 WHERE fee_cat = x_fee_cat
283 AND fee_cal_type = x_fee_cal_type
284 AND fee_ci_sequence_number = x_fee_ci_sequence_number
285 AND fee_type = x_fee_type
286 AND unit_cd = x_unit_cd
287 AND version_number = x_version_number
288 AND cal_type = x_cal_type
289 AND ci_sequence_number = x_ci_sequence_number
290 AND location_cd = x_location_cd
291 AND unit_class = x_unit_class
292 AND create_dt = x_create_dt
293 AND hist_start_dt = x_hist_start_dt
294 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
295 FOR UPDATE NOWAIT;
296 lv_rowid cur_rowid%RowType;
297 BEGIN
298 Open cur_rowid;
299 Fetch cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 Close cur_rowid;
302 Return (TRUE);
303 ELSE
304 Close cur_rowid;
305 Return (FALSE);
306 END IF;
307 END Get_UK1_For_Validation;
308
309 PROCEDURE Before_DML (
310 p_action IN VARCHAR2,
311 x_rowid IN VARCHAR2 DEFAULT NULL,
312 x_location_cd IN VARCHAR2 DEFAULT NULL,
313 x_unit_class IN VARCHAR2 DEFAULT NULL,
314 x_create_dt IN DATE DEFAULT NULL,
315 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
316 x_fee_cat IN VARCHAR2 DEFAULT NULL,
317 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
318 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
319 x_fee_type IN VARCHAR2 DEFAULT NULL,
320 x_unit_cd IN VARCHAR2 DEFAULT NULL,
321 x_sequence_number IN NUMBER DEFAULT NULL,
322 x_hist_start_dt IN DATE DEFAULT NULL,
323 x_hist_end_dt IN DATE DEFAULT NULL,
324 x_hist_who IN VARCHAR2 DEFAULT NULL,
325 x_version_number IN NUMBER DEFAULT NULL,
326 x_cal_type IN VARCHAR2 DEFAULT NULL,
327 x_ci_sequence_number IN NUMBER DEFAULT NULL,
328 x_org_id IN NUMBER DEFAULT NULL,
329 x_creation_date IN DATE DEFAULT NULL,
330 x_created_by IN NUMBER DEFAULT NULL,
331 x_last_update_date IN DATE DEFAULT NULL,
332 x_last_updated_by IN NUMBER DEFAULT NULL,
333 x_last_update_login IN NUMBER DEFAULT NULL
334 ) AS
335 BEGIN
336 Set_Column_Values (
337 p_action,
338 x_rowid,
339 x_location_cd,
340 x_unit_class,
341 x_create_dt,
342 x_fee_trigger_group_number,
343 x_fee_cat,
344 x_fee_cal_type,
345 x_fee_ci_sequence_number,
346 x_fee_type,
347 x_unit_cd,
348 x_sequence_number,
349 x_hist_start_dt,
350 x_hist_end_dt,
351 x_hist_who,
352 x_version_number,
353 x_cal_type,
354 x_ci_sequence_number,
355 x_org_id,
356 x_creation_date,
357 x_created_by,
358 x_last_update_date,
359 x_last_updated_by,
360 x_last_update_login
361 );
362 IF (p_action = 'INSERT') THEN
363 -- Call all the procedures related to Before Insert.
364 IF (Get_PK_For_Validation (
365 new_references.fee_cat,
366 new_references.fee_cal_type,
367 new_references.fee_ci_sequence_number,
368 new_references.fee_type,
369 new_references.unit_cd,
370 new_references.sequence_number,
371 new_references.hist_start_dt
372 )) THEN
373 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 Check_Uniqueness;
378 Check_Constraints;
379 Check_Parent_Existance;
380 ELSIF (p_action = 'UPDATE') THEN
381 -- Call all the procedures related to Before Update.
382 Check_Uniqueness;
383 Check_Constraints;
384 Check_Parent_Existance;
385 ELSIF (p_action = 'VALIDATE_INSERT') THEN
386 IF (Get_PK_For_Validation (
387 new_references.fee_cat,
388 new_references.fee_cal_type,
389 new_references.fee_ci_sequence_number,
390 new_references.fee_type,
391 new_references.unit_cd,
392 new_references.sequence_number,
393 new_references.hist_start_dt
394 )) THEN
395 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
396 IGS_GE_MSG_STACK.ADD;
397 App_Exception.Raise_Exception;
398 END IF;
399 Check_Uniqueness;
400 Check_Constraints;
401 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
402 Check_Uniqueness;
403 Check_Constraints;
404 END IF;
405 END Before_DML;
406 procedure INSERT_ROW (
407 X_ROWID in out NOCOPY VARCHAR2,
408 X_FEE_CAT in VARCHAR2,
409 X_FEE_CAL_TYPE in VARCHAR2,
410 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
411 X_FEE_TYPE in VARCHAR2,
412 X_UNIT_CD in VARCHAR2,
413 X_SEQUENCE_NUMBER in NUMBER,
414 X_HIST_START_DT in DATE,
415 X_HIST_END_DT in DATE,
416 X_HIST_WHO in NUMBER,
417 X_VERSION_NUMBER in NUMBER,
418 X_CAL_TYPE in VARCHAR2,
419 X_CI_SEQUENCE_NUMBER in NUMBER,
420 X_LOCATION_CD in VARCHAR2,
421 X_UNIT_CLASS in VARCHAR2,
422 X_CREATE_DT in DATE,
423 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
424 X_ORG_ID in NUMBER,
425 X_MODE in VARCHAR2 default 'R'
426 ) AS
427 cursor C is select ROWID from IGS_FI_UNIT_FEE_TR_H_ALL
428 where FEE_CAT = X_FEE_CAT
429 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
430 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
431 and FEE_TYPE = X_FEE_TYPE
432 and UNIT_CD = X_UNIT_CD
433 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
434 and HIST_START_DT = X_HIST_START_DT;
435 X_LAST_UPDATE_DATE DATE;
436 X_LAST_UPDATED_BY NUMBER;
437 X_LAST_UPDATE_LOGIN NUMBER;
438 begin
439 X_LAST_UPDATE_DATE := SYSDATE;
440 if(X_MODE = 'I') then
441 X_LAST_UPDATED_BY := 1;
442 X_LAST_UPDATE_LOGIN := 0;
443 elsif (X_MODE = 'R') then
444 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
445 if X_LAST_UPDATED_BY is NULL then
446 X_LAST_UPDATED_BY := -1;
447 end if;
448 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
449 if X_LAST_UPDATE_LOGIN is NULL then
450 X_LAST_UPDATE_LOGIN := -1;
451 end if;
452 else
453 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
454 IGS_GE_MSG_STACK.ADD;
455 app_exception.raise_exception;
456 end if;
457 Before_DML(
458 p_action=>'INSERT',
459 x_rowid=>X_ROWID,
460 x_cal_type=>X_CAL_TYPE,
461 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
462 x_create_dt=>X_CREATE_DT,
463 x_fee_cal_type=>X_FEE_CAL_TYPE,
464 x_fee_cat=>X_FEE_CAT,
465 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
466 x_fee_trigger_group_number=>X_FEE_TRIGGER_GROUP_NUMBER,
467 x_fee_type=>X_FEE_TYPE,
468 x_hist_end_dt=>X_HIST_END_DT,
469 x_hist_start_dt=>X_HIST_START_DT,
470 x_hist_who=>X_HIST_WHO,
471 x_location_cd=>X_LOCATION_CD,
472 x_sequence_number=>X_SEQUENCE_NUMBER,
473 x_unit_cd=>X_UNIT_CD,
474 x_unit_class=>X_UNIT_CLASS,
475 x_version_number=>X_VERSION_NUMBER,
476 x_org_id => igs_ge_gen_003.get_org_id ,
477 x_creation_date=>X_LAST_UPDATE_DATE,
478 x_created_by=>X_LAST_UPDATED_BY,
479 x_last_update_date=>X_LAST_UPDATE_DATE,
480 x_last_updated_by=>X_LAST_UPDATED_BY,
481 x_last_update_login=>X_LAST_UPDATE_LOGIN
482 );
483 insert into IGS_FI_UNIT_FEE_TR_H_ALL (
484 FEE_CAT,
485 FEE_CAL_TYPE,
486 FEE_CI_SEQUENCE_NUMBER,
487 FEE_TYPE,
488 UNIT_CD,
489 SEQUENCE_NUMBER,
490 HIST_START_DT,
491 HIST_END_DT,
492 HIST_WHO,
493 VERSION_NUMBER,
494 CAL_TYPE,
495 CI_SEQUENCE_NUMBER,
496 LOCATION_CD,
497 UNIT_CLASS,
498 CREATE_DT,
499 FEE_TRIGGER_GROUP_NUMBER,
500 ORG_ID,
501 CREATION_DATE,
502 CREATED_BY,
503 LAST_UPDATE_DATE,
504 LAST_UPDATED_BY,
505 LAST_UPDATE_LOGIN
506 ) values (
507 NEW_REFERENCES.FEE_CAT,
508 NEW_REFERENCES.FEE_CAL_TYPE,
509 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
510 NEW_REFERENCES.FEE_TYPE,
511 NEW_REFERENCES.UNIT_CD,
512 NEW_REFERENCES.SEQUENCE_NUMBER,
513 NEW_REFERENCES.HIST_START_DT,
514 NEW_REFERENCES.HIST_END_DT,
515 NEW_REFERENCES.HIST_WHO,
516 NEW_REFERENCES.VERSION_NUMBER,
517 NEW_REFERENCES.CAL_TYPE,
518 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
519 NEW_REFERENCES.LOCATION_CD,
520 NEW_REFERENCES.UNIT_CLASS,
521 NEW_REFERENCES.CREATE_DT,
522 NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
523 NEW_REFERENCES.ORG_ID,
524 X_LAST_UPDATE_DATE,
525 X_LAST_UPDATED_BY,
526 X_LAST_UPDATE_DATE,
527 X_LAST_UPDATED_BY,
528 X_LAST_UPDATE_LOGIN
529 );
530 open c;
531 fetch c into X_ROWID;
532 if (c%notfound) then
533 close c;
534 raise no_data_found;
535 end if;
536 close c;
537 end INSERT_ROW;
538 procedure LOCK_ROW (
539 X_ROWID in VARCHAR2,
540 X_FEE_CAT in VARCHAR2,
541 X_FEE_CAL_TYPE in VARCHAR2,
542 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
543 X_FEE_TYPE in VARCHAR2,
544 X_UNIT_CD in VARCHAR2,
545 X_SEQUENCE_NUMBER in NUMBER,
546 X_HIST_START_DT in DATE,
547 X_HIST_END_DT in DATE,
548 X_HIST_WHO in NUMBER,
549 X_VERSION_NUMBER in NUMBER,
550 X_CAL_TYPE in VARCHAR2,
551 X_CI_SEQUENCE_NUMBER in NUMBER,
552 X_LOCATION_CD in VARCHAR2,
553 X_UNIT_CLASS in VARCHAR2,
554 X_CREATE_DT in DATE,
555 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER
556 ) AS
557 cursor c1 is select
558 HIST_END_DT,
559 HIST_WHO,
560 VERSION_NUMBER,
561 CAL_TYPE,
562 CI_SEQUENCE_NUMBER,
563 LOCATION_CD,
564 UNIT_CLASS,
565 CREATE_DT,
566 FEE_TRIGGER_GROUP_NUMBER
567 from IGS_FI_UNIT_FEE_TR_H_ALL
568 where ROWID=X_ROWID
569 for update nowait;
570 tlinfo c1%rowtype;
571 begin
572 open c1;
573 fetch c1 into tlinfo;
574 if (c1%notfound) then
575 close c1;
576 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
577 IGS_GE_MSG_STACK.ADD;
578 app_exception.raise_exception;
579 return;
580 end if;
581 close c1;
582 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
583 AND (tlinfo.HIST_WHO = X_HIST_WHO)
584 AND ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
585 OR ((tlinfo.VERSION_NUMBER is null)
586 AND (X_VERSION_NUMBER is null)))
587 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
588 OR ((tlinfo.CAL_TYPE is null)
589 AND (X_CAL_TYPE is null)))
590 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
591 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
592 AND (X_CI_SEQUENCE_NUMBER is null)))
593 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
594 OR ((tlinfo.LOCATION_CD is null)
595 AND (X_LOCATION_CD is null)))
596 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
597 OR ((tlinfo.UNIT_CLASS is null)
598 AND (X_UNIT_CLASS is null)))
599 AND ((tlinfo.CREATE_DT = X_CREATE_DT)
600 OR ((tlinfo.CREATE_DT is null)
601 AND (X_CREATE_DT is null)))
602 AND ((tlinfo.FEE_TRIGGER_GROUP_NUMBER = X_FEE_TRIGGER_GROUP_NUMBER)
603 OR ((tlinfo.FEE_TRIGGER_GROUP_NUMBER is null)
604 AND (X_FEE_TRIGGER_GROUP_NUMBER is null)))
605 ) then
606 null;
607 else
608 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
609 IGS_GE_MSG_STACK.ADD;
610 app_exception.raise_exception;
611 end if;
612 return;
613 end LOCK_ROW;
614 procedure UPDATE_ROW (
615 X_ROWID in VARCHAR2,
616 X_FEE_CAT in VARCHAR2,
617 X_FEE_CAL_TYPE in VARCHAR2,
618 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
619 X_FEE_TYPE in VARCHAR2,
620 X_UNIT_CD in VARCHAR2,
621 X_SEQUENCE_NUMBER in NUMBER,
622 X_HIST_START_DT in DATE,
623 X_HIST_END_DT in DATE,
624 X_HIST_WHO in NUMBER,
625 X_VERSION_NUMBER in NUMBER,
626 X_CAL_TYPE in VARCHAR2,
627 X_CI_SEQUENCE_NUMBER in NUMBER,
628 X_LOCATION_CD in VARCHAR2,
629 X_UNIT_CLASS in VARCHAR2,
630 X_CREATE_DT in DATE,
631 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
632 X_MODE in VARCHAR2 default 'R'
633 ) AS
634 X_LAST_UPDATE_DATE DATE;
635 X_LAST_UPDATED_BY NUMBER;
636 X_LAST_UPDATE_LOGIN NUMBER;
637 begin
638 X_LAST_UPDATE_DATE := SYSDATE;
639 if(X_MODE = 'I') then
640 X_LAST_UPDATED_BY := 1;
641 X_LAST_UPDATE_LOGIN := 0;
642 elsif (X_MODE = 'R') then
643 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
644 if X_LAST_UPDATED_BY is NULL then
645 X_LAST_UPDATED_BY := -1;
646 end if;
647 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
648 if X_LAST_UPDATE_LOGIN is NULL then
649 X_LAST_UPDATE_LOGIN := -1;
650 end if;
651 else
652 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
653 IGS_GE_MSG_STACK.ADD;
654 app_exception.raise_exception;
655 end if;
656 Before_DML(
657 p_action=>'UPDATE',
658 x_rowid=>X_ROWID,
659 x_cal_type=>X_CAL_TYPE,
660 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
661 x_create_dt=>X_CREATE_DT,
662 x_fee_cal_type=>X_FEE_CAL_TYPE,
663 x_fee_cat=>X_FEE_CAT,
664 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
665 x_fee_trigger_group_number=>X_FEE_TRIGGER_GROUP_NUMBER,
666 x_fee_type=>X_FEE_TYPE,
667 x_hist_end_dt=>X_HIST_END_DT,
668 x_hist_start_dt=>X_HIST_START_DT,
669 x_hist_who=>X_HIST_WHO,
670 x_location_cd=>X_LOCATION_CD,
671 x_sequence_number=>X_SEQUENCE_NUMBER,
672 x_unit_cd=>X_UNIT_CD,
673 x_unit_class=>X_UNIT_CLASS,
674 x_version_number=>X_VERSION_NUMBER,
675 x_creation_date=>X_LAST_UPDATE_DATE,
676 x_created_by=>X_LAST_UPDATED_BY,
677 x_last_update_date=>X_LAST_UPDATE_DATE,
678 x_last_updated_by=>X_LAST_UPDATED_BY,
679 x_last_update_login=>X_LAST_UPDATE_LOGIN
680 );
681 update IGS_FI_UNIT_FEE_TR_H_ALL set
682 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
683 HIST_WHO = NEW_REFERENCES.HIST_WHO,
684 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
685 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
686 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
687 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
688 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
689 CREATE_DT = NEW_REFERENCES.CREATE_DT,
690 FEE_TRIGGER_GROUP_NUMBER = NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
691 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
692 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
693 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
694 where ROWID=X_ROWID
695 ;
696 if (sql%notfound) then
697 raise no_data_found;
698 end if;
699 end UPDATE_ROW;
700 procedure ADD_ROW (
701 X_ROWID in out NOCOPY VARCHAR2,
702 X_FEE_CAT in VARCHAR2,
703 X_FEE_CAL_TYPE in VARCHAR2,
704 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
705 X_FEE_TYPE in VARCHAR2,
706 X_UNIT_CD in VARCHAR2,
707 X_SEQUENCE_NUMBER in NUMBER,
708 X_HIST_START_DT in DATE,
709 X_HIST_END_DT in DATE,
710 X_HIST_WHO in NUMBER,
711 X_VERSION_NUMBER in NUMBER,
712 X_CAL_TYPE in VARCHAR2,
713 X_CI_SEQUENCE_NUMBER in NUMBER,
714 X_LOCATION_CD in VARCHAR2,
715 X_UNIT_CLASS in VARCHAR2,
716 X_CREATE_DT in DATE,
717 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
718 X_ORG_ID in NUMBER,
719 X_MODE in VARCHAR2 default 'R'
720 ) AS
721 cursor c1 is select rowid from IGS_FI_UNIT_FEE_TR_H_ALL
722 where FEE_CAT = X_FEE_CAT
723 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
724 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
725 and FEE_TYPE = X_FEE_TYPE
726 and UNIT_CD = X_UNIT_CD
727 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
728 and HIST_START_DT = X_HIST_START_DT
729 ;
730 begin
731 open c1;
732 fetch c1 into X_ROWID;
733 if (c1%notfound) then
734 close c1;
735 INSERT_ROW (
736 X_ROWID,
737 X_FEE_CAT,
738 X_FEE_CAL_TYPE,
739 X_FEE_CI_SEQUENCE_NUMBER,
740 X_FEE_TYPE,
741 X_UNIT_CD,
742 X_SEQUENCE_NUMBER,
743 X_HIST_START_DT,
744 X_HIST_END_DT,
745 X_HIST_WHO,
746 X_VERSION_NUMBER,
747 X_CAL_TYPE,
748 X_CI_SEQUENCE_NUMBER,
749 X_LOCATION_CD,
750 X_UNIT_CLASS,
751 X_CREATE_DT,
752 X_FEE_TRIGGER_GROUP_NUMBER,
753 X_ORG_ID,
754 X_MODE);
755 return;
756 end if;
757 close c1;
758 UPDATE_ROW (
759 X_ROWID,
760 X_FEE_CAT,
761 X_FEE_CAL_TYPE,
762 X_FEE_CI_SEQUENCE_NUMBER,
763 X_FEE_TYPE,
764 X_UNIT_CD,
765 X_SEQUENCE_NUMBER,
766 X_HIST_START_DT,
767 X_HIST_END_DT,
768 X_HIST_WHO,
769 X_VERSION_NUMBER,
770 X_CAL_TYPE,
771 X_CI_SEQUENCE_NUMBER,
772 X_LOCATION_CD,
773 X_UNIT_CLASS,
774 X_CREATE_DT,
775 X_FEE_TRIGGER_GROUP_NUMBER,
776 X_MODE);
777 end ADD_ROW;
778 procedure DELETE_ROW (
779 X_ROWID in VARCHAR2
780 ) AS
781 begin
782 delete from IGS_FI_UNIT_FEE_TR_H_ALL
783 where ROWID=X_ROWID;
784 if (sql%notfound) then
785 raise no_data_found;
786 end if;
787 end DELETE_ROW;
788 end IGS_FI_UNIT_FEE_TR_H_PKG;