eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_FI_F_CAT_F_LBL_H_PKG

Source


1 package body IGS_FI_F_CAT_F_LBL_H_PKG AS
2 /* $Header: IGSSI46B.pls 115.9 2002/11/29 03:48:46 nsidana ship $*/
3   l_rowid VARCHAR2(25);
4   old_references IGS_FI_F_CAT_F_LBL_H_ALL%RowType;
5   new_references IGS_FI_F_CAT_F_LBL_H_ALL%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_hist_start_dt IN DATE DEFAULT NULL,
14     x_hist_end_dt IN DATE DEFAULT NULL,
15     x_hist_who IN VARCHAR2 DEFAULT NULL,
16     x_fee_liability_status IN VARCHAR2 DEFAULT NULL,
17     x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
18     x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
19     x_s_chg_method_type IN VARCHAR2 DEFAULT NULL,
20     x_rul_sequence_number IN NUMBER DEFAULT NULL,
21     x_org_id IN NUMBER DEFAULT NULL,
22     x_creation_date IN DATE DEFAULT NULL,
23     x_created_by IN NUMBER DEFAULT NULL,
24     x_last_update_date IN DATE DEFAULT NULL,
25     x_last_updated_by IN NUMBER DEFAULT NULL,
26     x_last_update_login IN NUMBER DEFAULT NULL
27   ) AS
28   /*----------------------------------------------------------------------------
29   ||  Created By :
30   ||  Created On :
31   ||  Purpose :
32   ||  Known limitations, enhancements or remarks :
33   ||  Change History :
34   ||  Who             When            What
35   ||  (reverse chronological order - newest change first)
36   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
37   ||                               and its reference in copying old_references value
38   ||                               into new_references.value.
39   ----------------------------------------------------------------------------*/
40     CURSOR cur_old_ref_values IS
41       SELECT   *
42       FROM     IGS_FI_F_CAT_F_LBL_H_ALL
43       WHERE    rowid = x_rowid;
44   BEGIN
45     l_rowid := x_rowid;
46     -- Code for setting the Old and New Reference Values.
47     -- Populate Old Values.
48     Open cur_old_ref_values;
49     Fetch cur_old_ref_values INTO old_references;
50     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
51       Close cur_old_ref_values;
52       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
53       IGS_GE_MSG_STACK.ADD;
54       App_Exception.Raise_Exception;
55       Return;
56     END IF;
57     Close cur_old_ref_values;
58     -- Populate New Values.
59     new_references.fee_cat := x_fee_cat;
60     new_references.fee_cal_type := x_fee_cal_type;
61     new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
62     new_references.fee_type := x_fee_type;
63     new_references.hist_start_dt := x_hist_start_dt;
64     new_references.hist_end_dt := x_hist_end_dt;
65     new_references.hist_who := x_hist_who;
66     new_references.fee_liability_status := x_fee_liability_status;
67     new_references.start_dt_alias := x_start_dt_alias;
68     new_references.start_dai_sequence_number := x_start_dai_sequence_number;
69     new_references.s_chg_method_type := x_s_chg_method_type;
70     new_references.rul_sequence_number := x_rul_sequence_number;
71     new_references.org_id := x_org_id;
72     IF (p_action = 'UPDATE') THEN
73       new_references.creation_date := old_references.creation_date;
74       new_references.created_by := old_references.created_by;
75     ELSE
76       new_references.creation_date := x_creation_date;
77       new_references.created_by := x_created_by;
78     END IF;
79     new_references.last_update_date := x_last_update_date;
80     new_references.last_updated_by := x_last_updated_by;
81     new_references.last_update_login := x_last_update_login;
82   END Set_Column_Values;
83   PROCEDURE Check_Constraints (
84     column_name  IN  VARCHAR2 DEFAULT NULL,
85     column_value IN  VARCHAR2 DEFAULT NULL
86   ) AS
87    /*----------------------------------------------------------------------------
88   ||  Created By :
89   ||  Created On :
90   ||  Purpose :
91   ||  Known limitations, enhancements or remarks :
92   ||  Change History :
93   ||  Who             When            What
94   ||  (reverse chronological order - newest change first)
95   ||  vvutukur        20-May-2002  removed upper check on fee_type,fee_cat,
96   ||                               fee_liability_status(alias of fee_structure_status) columns.bug#2344826.
97   ----------------------------------------------------------------------------*/
98   BEGIN
99     IF (column_name IS NULL) THEN
100       NULL;
101     ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
102       new_references.fee_ci_sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
103     ELSIF (UPPER (column_name) = 'RUL_SEQUENCE_NUMBER') THEN
104       new_references.rul_sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
105     ELSIF (UPPER (column_name) = 'START_DAI_SEQUENCE_NUMBER') THEN
106       new_references.start_dai_sequence_number := IGS_GE_NUMBER.TO_NUM (column_value);
107     ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
108       new_references.fee_cal_type := column_value;
109     ELSIF (UPPER (column_name) = 'START_DT_ALIAS') THEN
110       new_references.start_dt_alias := column_value;
111     ELSIF (UPPER (column_name) = 'S_CHG_METHOD_TYPE') THEN
112       new_references.s_chg_method_type := column_value;
113     END IF;
114     IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
115       IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
116         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
117         IGS_GE_MSG_STACK.ADD;
118         App_Exception.Raise_Exception;
119       END IF;
120     END IF;
121     IF ((UPPER (column_name) = 'RUL_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
122       IF ((new_references.rul_sequence_number < 1) OR (new_references.rul_sequence_number > 999999)) THEN
123         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
124         IGS_GE_MSG_STACK.ADD;
125         App_Exception.Raise_Exception;
126       END IF;
127     END IF;
128     IF ((UPPER (column_name) = 'START_DAI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
129       IF ((new_references.start_dai_sequence_number < 1) OR (new_references.start_dai_sequence_number > 999999)) THEN
130         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
131         IGS_GE_MSG_STACK.ADD;
132         App_Exception.Raise_Exception;
133       END IF;
134     END IF;
135     IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
136       IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
137         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
138         IGS_GE_MSG_STACK.ADD;
139         App_Exception.Raise_Exception;
140       END IF;
141     END IF;
142     IF ((UPPER (column_name) = 'START_DT_ALIAS') OR (column_name IS NULL)) THEN
143       IF (new_references.start_dt_alias <> UPPER (new_references.start_dt_alias)) THEN
144         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
145         IGS_GE_MSG_STACK.ADD;
146         App_Exception.Raise_Exception;
147       END IF;
148     END IF;
149     IF ((UPPER (column_name) = 'S_CHG_METHOD_TYPE') OR (column_name IS NULL)) THEN
150       IF (new_references.s_chg_method_type <> UPPER (new_references.s_chg_method_type)) THEN
151         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
152         IGS_GE_MSG_STACK.ADD;
153         App_Exception.Raise_Exception;
154       END IF;
155     END IF;
156   END Check_Constraints;
157   FUNCTION Get_PK_For_Validation (
158     x_fee_cat IN VARCHAR2,
159     x_fee_cal_type IN VARCHAR2,
160     x_fee_ci_sequence_number IN NUMBER,
161     x_fee_type IN VARCHAR2,
162     x_hist_start_dt IN DATE
163     ) RETURN BOOLEAN AS
164     CURSOR cur_rowid IS
165       SELECT   rowid
166       FROM     IGS_FI_F_CAT_F_LBL_H_ALL
167       WHERE    fee_cat = x_fee_cat
168       AND      fee_cal_type = x_fee_cal_type
169       AND      fee_ci_sequence_number = x_fee_ci_sequence_number
170       AND      fee_type = x_fee_type
171       AND      hist_start_dt = x_hist_start_dt
172       FOR UPDATE NOWAIT;
173     lv_rowid cur_rowid%RowType;
174   BEGIN
175     Open cur_rowid;
176     Fetch cur_rowid INTO lv_rowid;
177     IF (cur_rowid%FOUND) THEN
178       Close cur_rowid;
179       Return (TRUE);
180     ELSE
181       Close cur_rowid;
182       Return (FALSE);
183     END IF;
184   END Get_PK_For_Validation;
185   PROCEDURE Before_DML (
186     p_action IN VARCHAR2,
187     x_rowid IN VARCHAR2 DEFAULT NULL,
188     x_fee_cat IN VARCHAR2 DEFAULT NULL,
189     x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
190     x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
191     x_fee_type IN VARCHAR2 DEFAULT NULL,
192     x_hist_start_dt IN DATE DEFAULT NULL,
193     x_hist_end_dt IN DATE DEFAULT NULL,
194     x_hist_who IN VARCHAR2 DEFAULT NULL,
195     x_fee_liability_status IN VARCHAR2 DEFAULT NULL,
196     x_start_dt_alias IN VARCHAR2 DEFAULT NULL,
197     x_start_dai_sequence_number IN NUMBER DEFAULT NULL,
198     x_s_chg_method_type IN VARCHAR2 DEFAULT NULL,
199     x_rul_sequence_number IN NUMBER DEFAULT NULL,
200     x_org_id IN NUMBER DEFAULT NULL,
201     x_creation_date IN DATE DEFAULT NULL,
202     x_created_by IN NUMBER DEFAULT NULL,
203     x_last_update_date IN DATE DEFAULT NULL,
204     x_last_updated_by IN NUMBER DEFAULT NULL,
205     x_last_update_login IN NUMBER DEFAULT NULL
206   ) AS
207   /*----------------------------------------------------------------------------
208   ||  Created By :
209   ||  Created On :
210   ||  Purpose :
211   ||  Known limitations, enhancements or remarks :
212   ||  Change History :
213   ||  Who             When            What
214   ||  (reverse chronological order - newest change first)
215   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
216   ||                               and from call to set_column_values procedure.
217   ----------------------------------------------------------------------------*/
218   BEGIN
219     Set_Column_Values (
220       p_action,
221       x_rowid,
222       x_fee_cat,
223       x_fee_cal_type,
224       x_fee_ci_sequence_number,
225       x_fee_type,
226       x_hist_start_dt,
227       x_hist_end_dt,
228       x_hist_who,
229       x_fee_liability_status,
230       x_start_dt_alias,
231       x_start_dai_sequence_number,
232       x_s_chg_method_type,
233       x_rul_sequence_number,
234       x_org_id,
235       x_creation_date,
236       x_created_by,
237       x_last_update_date,
238       x_last_updated_by,
239       x_last_update_login
240     );
241     IF (p_action = 'INSERT') THEN
242       -- Call all the procedures related to Before Insert.
243       IF (Get_PK_For_Validation (
244             new_references.fee_cat,
245             new_references.fee_cal_type,
246             new_references.fee_ci_sequence_number,
247 			new_references.fee_type,
248 			new_references.hist_start_dt
249             )) THEN
250         Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
251         IGS_GE_MSG_STACK.ADD;
252         App_Exception.Raise_Exception;
253       END IF;
254       Check_Constraints;
255     ELSIF (p_action = 'UPDATE') THEN
256       -- Call all the procedures related to Before Update.
257       Check_Constraints;
258     ELSIF (p_action = 'VALIDATE_INSERT') THEN
259       IF (Get_PK_For_Validation (
260             new_references.fee_cat,
261             new_references.fee_cal_type,
262             new_references.fee_ci_sequence_number,
263             new_references.fee_type,
264             new_references.hist_start_dt
265           )) THEN
266         Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
267         IGS_GE_MSG_STACK.ADD;
268         App_Exception.Raise_Exception;
269       END IF;
270       Check_Constraints;
271     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
272       Check_Constraints;
273     END IF;
274   END Before_DML;
275   procedure INSERT_ROW (
276   X_ROWID in out NOCOPY VARCHAR2,
277   X_FEE_CAT in VARCHAR2,
278   X_FEE_CAL_TYPE in VARCHAR2,
279   X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
280   X_FEE_TYPE in VARCHAR2,
281   X_HIST_START_DT in DATE,
282   X_HIST_END_DT in DATE,
283   X_HIST_WHO in NUMBER,
284   X_FEE_LIABILITY_STATUS in VARCHAR2,
285   X_START_DT_ALIAS in VARCHAR2,
286   X_START_DAI_SEQUENCE_NUMBER in NUMBER,
287   X_S_CHG_METHOD_TYPE in VARCHAR2,
288   X_RUL_SEQUENCE_NUMBER in NUMBER,
289   X_ORG_ID in NUMBER,
290   X_MODE in VARCHAR2 default 'R'
291   ) is
292     /*----------------------------------------------------------------------------
293   ||  Created By :
294   ||  Created On :
295   ||  Purpose :
296   ||  Known limitations, enhancements or remarks :
297   ||  Change History :
298   ||  Who             When            What
299   ||  (reverse chronological order - newest change first)
300   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
301   ||                               and from call to before_dml and insert statement.
302   ----------------------------------------------------------------------------*/
303     cursor C is select ROWID from IGS_FI_F_CAT_F_LBL_H_ALL
304       where FEE_CAT = X_FEE_CAT
305       and FEE_CAL_TYPE = X_FEE_CAL_TYPE
306       and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
307       and FEE_TYPE = X_FEE_TYPE
308       and HIST_START_DT = X_HIST_START_DT;
309     X_LAST_UPDATE_DATE DATE;
310     X_LAST_UPDATED_BY NUMBER;
311     X_LAST_UPDATE_LOGIN NUMBER;
312 begin
313   X_LAST_UPDATE_DATE := SYSDATE;
314   if(X_MODE = 'I') then
315     X_LAST_UPDATED_BY := 1;
316     X_LAST_UPDATE_LOGIN := 0;
317   elsif (X_MODE = 'R') then
318     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
319     if X_LAST_UPDATED_BY is NULL then
320       X_LAST_UPDATED_BY := -1;
321     end if;
322     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
323     if X_LAST_UPDATE_LOGIN is NULL then
324       X_LAST_UPDATE_LOGIN := -1;
325     end if;
326   else
327     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
328     IGS_GE_MSG_STACK.ADD;
329     app_exception.raise_exception;
330   end if;
331 Before_DML(
332  p_action=>'INSERT',
333  x_rowid=>X_ROWID,
334  x_fee_cal_type=>X_FEE_CAL_TYPE,
335  x_fee_cat=>X_FEE_CAT,
336  x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
337  x_fee_liability_status=>X_FEE_LIABILITY_STATUS,
338  x_fee_type=>X_FEE_TYPE,
342  x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
339  x_hist_end_dt=>X_HIST_END_DT,
340  x_hist_start_dt=>X_HIST_START_DT,
341  x_hist_who=>X_HIST_WHO,
343  x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
344  x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
345  x_start_dt_alias=>X_START_DT_ALIAS,
346  x_org_id => igs_ge_gen_003.get_org_id,
347  x_creation_date=>X_LAST_UPDATE_DATE,
348  x_created_by=>X_LAST_UPDATED_BY,
349  x_last_update_date=>X_LAST_UPDATE_DATE,
350  x_last_updated_by=>X_LAST_UPDATED_BY,
351  x_last_update_login=>X_LAST_UPDATE_LOGIN
352 );
353   insert into IGS_FI_F_CAT_F_LBL_H_ALL (
354     FEE_CAT,
355     FEE_CAL_TYPE,
356     FEE_CI_SEQUENCE_NUMBER,
357     FEE_TYPE,
358     HIST_START_DT,
359     HIST_END_DT,
360     HIST_WHO,
361     FEE_LIABILITY_STATUS,
362     START_DT_ALIAS,
363     START_DAI_SEQUENCE_NUMBER,
364     S_CHG_METHOD_TYPE,
365     RUL_SEQUENCE_NUMBER,
366     ORG_ID,
367     CREATION_DATE,
368     CREATED_BY,
369     LAST_UPDATE_DATE,
370     LAST_UPDATED_BY,
371     LAST_UPDATE_LOGIN
372   ) values (
373     NEW_REFERENCES.FEE_CAT,
374     NEW_REFERENCES.FEE_CAL_TYPE,
375     NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
376     NEW_REFERENCES.FEE_TYPE,
377     NEW_REFERENCES.HIST_START_DT,
378     NEW_REFERENCES.HIST_END_DT,
379     NEW_REFERENCES.HIST_WHO,
380     NEW_REFERENCES.FEE_LIABILITY_STATUS,
381     NEW_REFERENCES.START_DT_ALIAS,
382     NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
383     NEW_REFERENCES.S_CHG_METHOD_TYPE,
384     NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
385     NEW_REFERENCES.ORG_ID,
386     X_LAST_UPDATE_DATE,
387     X_LAST_UPDATED_BY,
388     X_LAST_UPDATE_DATE,
389     X_LAST_UPDATED_BY,
390     X_LAST_UPDATE_LOGIN
391   );
392   open c;
393   fetch c into X_ROWID;
394   if (c%notfound) then
395     close c;
396     raise no_data_found;
397   end if;
398   close c;
399 end INSERT_ROW;
400 procedure LOCK_ROW (
401   X_ROWID in VARCHAR2,
402   X_FEE_CAT in VARCHAR2,
403   X_FEE_CAL_TYPE in VARCHAR2,
404   X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
405   X_FEE_TYPE in VARCHAR2,
406   X_HIST_START_DT in DATE,
407   X_HIST_END_DT in DATE,
408   X_HIST_WHO in NUMBER,
409   X_FEE_LIABILITY_STATUS in VARCHAR2,
410   X_START_DT_ALIAS in VARCHAR2,
411   X_START_DAI_SEQUENCE_NUMBER in NUMBER,
412   X_S_CHG_METHOD_TYPE in VARCHAR2,
413   X_RUL_SEQUENCE_NUMBER in NUMBER
414 ) is
415   /*----------------------------------------------------------------------------
416   ||  Created By :
417   ||  Created On :
418   ||  Purpose :
419   ||  Known limitations, enhancements or remarks :
420   ||  Change History :
421   ||  Who             When            What
422   ||  (reverse chronological order - newest change first)
423   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
424   ||                               and from cursor c1 and from if condition.
425   ----------------------------------------------------------------------------*/
426   cursor c1 is select
427       HIST_END_DT,
428       HIST_WHO,
429       FEE_LIABILITY_STATUS,
430       START_DT_ALIAS,
431       START_DAI_SEQUENCE_NUMBER,
432       S_CHG_METHOD_TYPE,
433       RUL_SEQUENCE_NUMBER
434     from IGS_FI_F_CAT_F_LBL_H_ALL
435     where ROWID=X_ROWID
436     for update nowait;
437   tlinfo c1%rowtype;
438 begin
439   open c1;
440   fetch c1 into tlinfo;
441   if (c1%notfound) then
442     close c1;
443     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
444     IGS_GE_MSG_STACK.ADD;
445     app_exception.raise_exception;
446     return;
447   end if;
448   close c1;
449   if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
450       AND (tlinfo.HIST_WHO = X_HIST_WHO)
451       AND ((tlinfo.FEE_LIABILITY_STATUS = X_FEE_LIABILITY_STATUS)
452            OR ((tlinfo.FEE_LIABILITY_STATUS is null)
453                AND (X_FEE_LIABILITY_STATUS is null)))
454       AND ((tlinfo.START_DT_ALIAS = X_START_DT_ALIAS)
455            OR ((tlinfo.START_DT_ALIAS is null)
456                AND (X_START_DT_ALIAS is null)))
457       AND ((tlinfo.START_DAI_SEQUENCE_NUMBER = X_START_DAI_SEQUENCE_NUMBER)
458            OR ((tlinfo.START_DAI_SEQUENCE_NUMBER is null)
459                AND (X_START_DAI_SEQUENCE_NUMBER is null)))
460       AND ((tlinfo.S_CHG_METHOD_TYPE = X_S_CHG_METHOD_TYPE)
461            OR ((tlinfo.S_CHG_METHOD_TYPE is null)
462                AND (X_S_CHG_METHOD_TYPE is null)))
463       AND ((tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
464            OR ((tlinfo.RUL_SEQUENCE_NUMBER is null)
465                AND (X_RUL_SEQUENCE_NUMBER is null)))
466   ) then
467     null;
468   else
469     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
470     IGS_GE_MSG_STACK.ADD;
471     app_exception.raise_exception;
472   end if;
473   return;
474 end LOCK_ROW;
475 procedure UPDATE_ROW (
476   X_ROWID in VARCHAR2,
477   X_FEE_CAT in VARCHAR2,
478   X_FEE_CAL_TYPE in VARCHAR2,
479   X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
480   X_FEE_TYPE in VARCHAR2,
481   X_HIST_START_DT in DATE,
482   X_HIST_END_DT in DATE,
483   X_HIST_WHO in NUMBER,
484   X_FEE_LIABILITY_STATUS in VARCHAR2,
485   X_START_DT_ALIAS in VARCHAR2,
486   X_START_DAI_SEQUENCE_NUMBER in NUMBER,
487   X_S_CHG_METHOD_TYPE in VARCHAR2,
488   X_RUL_SEQUENCE_NUMBER in NUMBER,
489   X_MODE in VARCHAR2 default 'R'
490   ) is
491   /*----------------------------------------------------------------------------
492   ||  Created By :
493   ||  Created On :
494   ||  Purpose :
495   ||  Known limitations, enhancements or remarks :
496   ||  Change History :
497   ||  Who             When            What
498   ||  (reverse chronological order - newest change first)
499   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
500   ||                               and from call to before_dml and from update statement.
501   ----------------------------------------------------------------------------*/
502     X_LAST_UPDATE_DATE DATE;
503     X_LAST_UPDATED_BY NUMBER;
504     X_LAST_UPDATE_LOGIN NUMBER;
505 begin
506   X_LAST_UPDATE_DATE := SYSDATE;
507   if(X_MODE = 'I') then
508     X_LAST_UPDATED_BY := 1;
509     X_LAST_UPDATE_LOGIN := 0;
510   elsif (X_MODE = 'R') then
511     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
512     if X_LAST_UPDATED_BY is NULL then
513       X_LAST_UPDATED_BY := -1;
514     end if;
515     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
516     if X_LAST_UPDATE_LOGIN is NULL then
517       X_LAST_UPDATE_LOGIN := -1;
518     end if;
519   else
520     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
521     IGS_GE_MSG_STACK.ADD;
522     app_exception.raise_exception;
523   end if;
524 Before_DML(
525  p_action=>'UPDATE',
526  x_rowid=>X_ROWID,
527  x_fee_cal_type=>X_FEE_CAL_TYPE,
528  x_fee_cat=>X_FEE_CAT,
529  x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
530  x_fee_liability_status=>X_FEE_LIABILITY_STATUS,
531  x_fee_type=>X_FEE_TYPE,
532  x_hist_end_dt=>X_HIST_END_DT,
533  x_hist_start_dt=>X_HIST_START_DT,
534  x_hist_who=>X_HIST_WHO,
535  x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
536  x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
537  x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
538  x_start_dt_alias=>X_START_DT_ALIAS,
539  x_creation_date=>X_LAST_UPDATE_DATE,
540  x_created_by=>X_LAST_UPDATED_BY,
541  x_last_update_date=>X_LAST_UPDATE_DATE,
542  x_last_updated_by=>X_LAST_UPDATED_BY,
543  x_last_update_login=>X_LAST_UPDATE_LOGIN
544 );
545   update IGS_FI_F_CAT_F_LBL_H_ALL set
546     HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
547     HIST_WHO = NEW_REFERENCES.HIST_WHO,
548     FEE_LIABILITY_STATUS = NEW_REFERENCES.FEE_LIABILITY_STATUS,
549     START_DT_ALIAS = NEW_REFERENCES.START_DT_ALIAS,
550     START_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
551     S_CHG_METHOD_TYPE = NEW_REFERENCES.S_CHG_METHOD_TYPE,
552     RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
553     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
554     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
555     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
556   where ROWID=X_ROWID
557   ;
558   if (sql%notfound) then
559     raise no_data_found;
560   end if;
561 end UPDATE_ROW;
562 procedure ADD_ROW (
563   X_ROWID in out NOCOPY VARCHAR2,
564   X_FEE_CAT in VARCHAR2,
565   X_FEE_CAL_TYPE in VARCHAR2,
566   X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
567   X_FEE_TYPE in VARCHAR2,
568   X_HIST_START_DT in DATE,
569   X_HIST_END_DT in DATE,
570   X_HIST_WHO in NUMBER,
571   X_FEE_LIABILITY_STATUS in VARCHAR2,
572   X_START_DT_ALIAS in VARCHAR2,
573   X_START_DAI_SEQUENCE_NUMBER in NUMBER,
574   X_S_CHG_METHOD_TYPE in VARCHAR2,
575   X_RUL_SEQUENCE_NUMBER in NUMBER,
576   X_ORG_ID in NUMBER,
577   X_MODE in VARCHAR2 default 'R'
578   ) is
579   /*----------------------------------------------------------------------------
580   ||  Created By :
581   ||  Created On :
582   ||  Purpose :
583   ||  Known limitations, enhancements or remarks :
584   ||  Change History :
585   ||  Who             When            What
586   ||  (reverse chronological order - newest change first)
587   ||  vvutukur        19-Jul-2002  Bug#2425767.removed parameter x_payment_hierarchy_rank,
588   ||                               and from calls to insert_row and update_row.
589   ----------------------------------------------------------------------------*/
590   cursor c1 is select rowid from IGS_FI_F_CAT_F_LBL_H_ALL
591      where FEE_CAT = X_FEE_CAT
592      and FEE_CAL_TYPE = X_FEE_CAL_TYPE
593      and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
594      and FEE_TYPE = X_FEE_TYPE
595      and HIST_START_DT = X_HIST_START_DT
596   ;
597 begin
598   open c1;
599   fetch c1 into X_ROWID;
600   if (c1%notfound) then
601     close c1;
602     INSERT_ROW (
603      X_ROWID,
604      X_FEE_CAT,
605      X_FEE_CAL_TYPE,
606      X_FEE_CI_SEQUENCE_NUMBER,
607      X_FEE_TYPE,
608      X_HIST_START_DT,
609      X_HIST_END_DT,
610      X_HIST_WHO,
611      X_FEE_LIABILITY_STATUS,
612      X_START_DT_ALIAS,
613      X_START_DAI_SEQUENCE_NUMBER,
614      X_S_CHG_METHOD_TYPE,
615      X_RUL_SEQUENCE_NUMBER,
616      X_ORG_ID,
617      X_MODE);
618     return;
619   end if;
620   close c1;
621   UPDATE_ROW (
622    X_ROWID,
623    X_FEE_CAT,
624    X_FEE_CAL_TYPE,
625    X_FEE_CI_SEQUENCE_NUMBER,
626    X_FEE_TYPE,
627    X_HIST_START_DT,
628    X_HIST_END_DT,
629    X_HIST_WHO,
630    X_FEE_LIABILITY_STATUS,
631    X_START_DT_ALIAS,
632    X_START_DAI_SEQUENCE_NUMBER,
633    X_S_CHG_METHOD_TYPE,
634    X_RUL_SEQUENCE_NUMBER,
635    X_MODE);
636 end ADD_ROW;
637 procedure DELETE_ROW (
638   X_ROWID in VARCHAR2
639 ) is
640 begin
641 BEFORE_DML (
642  p_action=>'DELETE',
643  x_rowid=>X_ROWID
644 );
645  delete from IGS_FI_F_CAT_F_LBL_H_ALL
646   where ROWID=X_ROWID;
647   if (sql%notfound) then
648     raise no_data_found;
649   end if;
650 end DELETE_ROW;
651 end IGS_FI_F_CAT_F_LBL_H_PKG;