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