1 package body IGS_FI_F_CAT_FEE_LBL_PKG AS
2 /* $Header: IGSSI45B.pls 120.1 2005/07/28 07:04:52 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_F_CAT_FEE_LBL_ALL%RowType;
5 new_references IGS_FI_F_CAT_FEE_LBL_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 ,
9 x_fee_cat IN VARCHAR2 ,
10 x_fee_cal_type IN VARCHAR2 ,
11 x_fee_ci_sequence_number IN NUMBER ,
12 x_fee_type IN VARCHAR2 ,
13 x_fee_liability_status IN VARCHAR2 ,
14 x_start_dt_alias IN VARCHAR2 ,
15 x_start_dai_sequence_number IN NUMBER ,
16 x_s_chg_method_type IN VARCHAR2 ,
17 x_rul_sequence_number IN NUMBER ,
18 x_org_id IN NUMBER ,
19 x_creation_date IN DATE ,
20 x_created_by IN NUMBER ,
21 x_last_update_date IN DATE ,
22 x_last_updated_by IN NUMBER ,
23 x_last_update_login IN NUMBER,
24 x_waiver_calc_flag IN VARCHAR2
25 ) AS
26 /*----------------------------------------------------------------------------
27 || Created By :
28 || Created On :
29 || Purpose :
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 || smvk 02-Sep-2002 Default values are removed from the signature of this procedure to overcome
35 || File.Pkg.22 gscc warning.As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
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_FEE_LBL_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.fee_liability_status := x_fee_liability_status;
64 new_references.start_dt_alias := x_start_dt_alias;
65 new_references.start_dai_sequence_number := x_start_dai_sequence_number;
66 new_references.s_chg_method_type := x_s_chg_method_type;
67 new_references.rul_sequence_number := x_rul_sequence_number;
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.org_id := x_org_id;
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79 new_references.waiver_calc_flag := x_waiver_calc_flag;
80 END Set_Column_Values;
81
82 -- Trigger description :-
83 -- BEFORE INSERT OR DELETE OR UPDATE
84 -- ON IGS_FI_F_CAT_FEE_LBL_ALL
85 -- FOR EACH ROW
86 PROCEDURE BeforeRowInsertUpdateDelete1(
87 p_inserting IN BOOLEAN ,
88 p_updating IN BOOLEAN ,
89 p_deleting IN BOOLEAN
90 ) AS
91 /*************************************************************
92 Created By :
93 Date Created By :
94 Purpose :
95 Know limitations, enhancements or remarks
96 Change History
97 Who When What
98 shtatiko 10-FEB-2003 Enh# 2747325, Removed the code of handling p_deleting = 'TRUE' case as deletion of
99 Fee Category Fee Liability records is not allowed.
100 smvk 02-Sep-2002 Default values are removed from the signature of this procedure to overcome
101 File.Pkg.22 gscc warning.As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
102 smvk 14-Mar-2002 Check for system fee type refund
103 as its invalid for Fee Category Fee
104 Liability w.r.t Bug # 2144600
105 SCHODAVA 13-OCT-2001 Bug # 2021281
106 Added IF condition for
107 p_deleting
108 (reverse chronological order - newest change first)
109 ***************************************************************/
110
111 -- Returns the system fee trigger category of a fee type
112 CURSOR c_ft(cp_fee_type IN igs_fi_fee_type.fee_type%TYPE ) IS
113 SELECT s_fee_trigger_cat
114 FROM igs_fi_fee_type
115 WHERE fee_type = cp_fee_type;
116
117 -- checks if there are records in the IGS_FI_FEE_AS table
118 -- for the input FTCI and a NULL fee Category.
119 CURSOR c_ftci(cp_fee_type IN igs_fi_fee_as.fee_type%TYPE,
120 cp_fee_cal_type IN igs_fi_fee_as.fee_cal_type%TYPE,
121 cp_fee_ci_sequence_number IN igs_fi_fee_as.fee_ci_sequence_number%TYPE
122 ) IS
123 SELECT 'x'
124 FROM igs_fi_fee_as
125 WHERE fee_type = cp_fee_type
126 AND fee_cal_type = cp_fee_cal_type
127 AND fee_ci_sequence_number = cp_fee_ci_sequence_number
128 AND fee_cat IS NULL;
129
130 -- Returns the system fee type of a fee type w.r.t Bug # 2144600
131 CURSOR c_sft(cp_fee_type IN igs_fi_fee_type.fee_type%TYPE) IS
132 SELECT s_fee_type
133 FROM igs_fi_fee_type
134 WHERE fee_type = cp_fee_type;
135
136 v_message_name varchar2(30);
137 l_s_fee_trigger_cat igs_fi_fee_type.s_fee_trigger_cat%TYPE;
138 l_s_fee_type igs_fi_fee_type.s_fee_type%TYPE; -- added for Bug # 2144600
139 l_institution CONSTANT VARCHAR2(30) := 'INSTITUTN';
140 BEGIN
141 -- Validate system fee type associated with this fee type is not refund w.r.t. Bug # 2144600
142 IF( p_inserting OR p_updating) THEN
143 OPEN c_sft(new_references.fee_type);
144 FETCH c_sft INTO l_s_fee_type;
145 CLOSE c_sft;
146 IF( l_s_fee_type = 'REFUND') THEN
147 Fnd_Message.Set_Name('IGS','IGS_FI_INVALID_REFUND_FCFL');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152 -- Validate Fee Liability Status.
153 IF (p_inserting OR (old_references.fee_liability_status) <>
154 (new_references.fee_liability_status)) THEN
155 IF IGS_FI_VAL_FCCI.finp_val_fss_closed (
156 new_references.fee_liability_status,
157 v_message_name) = FALSE THEN
158 Fnd_Message.Set_Name('IGS',v_message_name);
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 -- Validate status can be set to 'ACTIVE'
163 IF IGS_FI_VAL_FCFL.finp_val_fcfl_active (
164 new_references.fee_liability_status,
165 new_references.fee_cal_type,
166 new_references.fee_ci_sequence_number,
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 IF IGS_FI_VAL_FCFL.finp_val_fcfl_status (
173 new_references.fee_cal_type,
174 new_references.fee_ci_sequence_number,
175 new_references.fee_cat,
176 new_references.fee_type,
177 new_references.fee_liability_status,
178 v_message_name) = FALSE THEN
179 Fnd_Message.Set_Name('IGS',v_message_name);
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183 END IF;
184 -- Validate Fee Liability Charge Method Type and Rule Sequence Number.
185 IF p_inserting OR p_updating THEN
186 IF IGS_FI_VAL_FCFL.finp_val_fcfl_rqrd (
187 new_references.fee_cal_type,
188 new_references.fee_ci_sequence_number,
189 new_references.fee_type,
190 new_references.s_chg_method_type,
191 new_references.rul_sequence_number,
192 v_message_name) = FALSE THEN
193 Fnd_Message.Set_Name('IGS',v_message_name);
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198 -- Validate fee category currency with details inherited from FTCI
199 IF p_inserting THEN
200 IF IGS_FI_VAL_FCFL.finp_val_fcfl_cur (
201 new_references.fee_cal_type,
202 new_references.fee_ci_sequence_number,
203 new_references.fee_type,
204 new_references.fee_cat,
205 v_message_name) = FALSE THEN
206 Fnd_Message.Set_Name('IGS',v_message_name);
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210 END IF;
211
212 -- Enh# 2747325, Removed the case for p_deleting as deletion is disabled as part of Locking Issues Build.
213 -- Bug # 2021281
214 -- added by schodava
215 -- If the system Fee trigger category is 'INSTITUTION' then,
216 -- Check whether the fee type calendar instance is present in the Fee Assessment table.
217 -- If it is, then restrict deletion of the Fee type from the IGS_FI_F_CAT_FEE_LBL_ALL table.
218 -- This is introduced to implement the foreign key IGS_FI_FEE_AS_PKG.GET_FK_IGS_FI_F_CAT_FEE_LBL
219 -- for a null fee_cat in the IGS_FI_FEE_AS table.
220
221 END BeforeRowInsertUpdateDelete1;
222
223 -- Trigger description :-
224 -- AFTER UPDATE
225 -- ON IGS_FI_F_CAT_FEE_LBL_ALL
226 -- FOR EACH ROW
227 PROCEDURE AfterRowUpdate3(
228 p_inserting IN BOOLEAN ,
229 p_updating IN BOOLEAN ,
230 p_deleting IN BOOLEAN
231 ) AS
232 /*----------------------------------------------------------------------------
233 || Created By :
234 || Created On :
235 || Purpose :
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 || smvk 02-Sep-2002 Default values are removed from the signature of this procedure to overcome
241 || File.Pkg.22 gscc warning.As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
242 || vvutukur 19-Jul-2002 Bug#2425767.removed parameters new_references.payment_hierarchy_rank,
243 || new_references.payment_hierarchy_rank,from the call to
244 || IGS_FI_GEN_002.FINP_INS_FCFL_HIST.
245 ----------------------------------------------------------------------------*/
246 BEGIN
247 -- create a history
248 IGS_FI_GEN_002.FINP_INS_FCFL_HIST (old_references.fee_cat,
249 old_references.fee_cal_type,
250 old_references.fee_ci_sequence_number,
251 old_references.fee_type,
252 new_references.fee_liability_status,
253 old_references.fee_liability_status,
254 new_references.start_dt_alias,
255 old_references.start_dt_alias,
256 new_references.start_dai_sequence_number,
257 old_references.start_dai_sequence_number,
258 new_references.s_chg_method_type,
259 old_references.s_chg_method_type,
260 new_references.rul_sequence_number,
261 old_references.rul_sequence_number,
262 new_references.last_updated_by,
263 old_references.last_updated_by,
264 new_references.last_update_date,
265 old_references.last_update_date);
266 END AfterRowUpdate3;
267
268 -- Following procedure commented as part of bug#2403209
269
270 -- Trigger description :-
271 -- AFTER INSERT OR UPDATE
272 -- ON IGS_FI_F_CAT_FEE_LBL_ALL
273
274 PROCEDURE Check_Uniqueness AS
275 BEGIN
276 IF Get_UK1_For_Validation (
277 new_references.fee_cat,
278 new_references.fee_cal_type,
279 new_references.fee_ci_sequence_number,
280 new_references.fee_type,
281 new_references.s_chg_method_type
282 ) THEN
283 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END IF;
287 END Check_Uniqueness;
288 PROCEDURE Check_Constraints (
289 column_name IN VARCHAR2,
290 column_value IN VARCHAR2
291 ) AS
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 20-May-2002 removed upper check on fee_type,fee_cat,
301 || fee_liability_status(alias of fee_structure_status) columns.bug#2344826.
302 ----------------------------------------------------------------------------*/
303 BEGIN
304 IF (column_name IS NULL) THEN
305 NULL;
306 ELSIF (UPPER (column_name) = 'START_DAI_SEQUENCE_NUMBER') THEN
307 new_references.start_dai_sequence_number := igs_ge_number.to_num (column_value);
308 ELSIF (UPPER (column_name) = 'RUL_SEQUENCE_NUMBER') THEN
309 new_references.rul_sequence_number := igs_ge_number.to_num (column_value);
310 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
311 new_references.fee_ci_sequence_number := igs_ge_number.to_num (column_value);
312 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
313 new_references.fee_cal_type := column_value;
314 ELSIF (UPPER (column_name) = 'START_DT_ALIAS') THEN
315 new_references.start_dt_alias := column_value;
316 ELSIF (UPPER (column_name) = 'S_CHG_METHOD_TYPE') THEN
317 new_references.s_chg_method_type := column_value;
318 END IF;
319 IF ((UPPER (column_name) = 'START_DAI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
320 IF ((new_references.start_dai_sequence_number < 1) OR (new_references.start_dai_sequence_number > 999999)) THEN
321 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
322 IGS_GE_MSG_STACK.ADD;
323 App_Exception.Raise_Exception;
327 IF ((new_references.rul_sequence_number < 1) OR (new_references.rul_sequence_number > 999999)) THEN
324 END IF;
325 END IF;
326 IF ((UPPER (column_name) = 'RUL_SEQUENCE_NUMBER') OR (column_name IS NULL)) 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) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
334 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) 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) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
341 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) 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) = 'START_DT_ALIAS') OR (column_name IS NULL)) THEN
348 IF (new_references.start_dt_alias <> UPPER (new_references.start_dt_alias)) 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 IF ((UPPER (column_name) = 'S_CHG_METHOD_TYPE') OR (column_name IS NULL)) THEN
355 IF (new_references.s_chg_method_type <> UPPER (new_references.s_chg_method_type)) THEN
356 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
357 IGS_GE_MSG_STACK.ADD;
358 App_Exception.Raise_Exception;
359 END IF;
360 END IF;
361 END Check_Constraints;
362 PROCEDURE Check_Parent_Existance AS
363 BEGIN
364 IF (((old_references.start_dt_alias = new_references.start_dt_alias) AND
365 (old_references.start_dai_sequence_number = new_references.start_dai_sequence_number) AND
366 (old_references.fee_cal_type = new_references.fee_cal_type) AND
367 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
368 ((new_references.start_dt_alias IS NULL) OR
369 (new_references.start_dai_sequence_number IS NULL) OR
370 (new_references.fee_cal_type IS NULL) OR
371 (new_references.fee_ci_sequence_number IS NULL))) THEN
372 NULL;
373 ELSE
374 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
375 new_references.start_dt_alias,
376 new_references.start_dai_sequence_number,
377 new_references.fee_cal_type,
378 new_references.fee_ci_sequence_number
379 ) THEN
380 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
381 IGS_GE_MSG_STACK.ADD;
382 App_Exception.Raise_Exception;
383 END IF;
384 END IF;
385 IF (((old_references.fee_cat = new_references.fee_cat) AND
386 (old_references.fee_cal_type = new_references.fee_cal_type) AND
387 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
388 ((new_references.fee_cat IS NULL) OR
389 (new_references.fee_cal_type IS NULL) OR
390 (new_references.fee_ci_sequence_number IS NULL))) THEN
391 NULL;
392 ELSE
393 IF NOT IGS_FI_F_CAT_CA_INST_PKG.Get_PK_For_Validation (
394 new_references.fee_cat,
395 new_references.fee_cal_type,
396 new_references.fee_ci_sequence_number
397 ) THEN
398 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
399 IGS_GE_MSG_STACK.ADD;
400 App_Exception.Raise_Exception;
401 END IF;
402 END IF;
403 IF (((old_references.fee_liability_status = new_references.fee_liability_status)) OR
404 ((new_references.fee_liability_status IS NULL))) THEN
405 NULL;
406 ELSE
407 IF NOT IGS_FI_FEE_STR_STAT_PKG.Get_PK_For_Validation (
408 new_references.fee_liability_status
409 ) THEN
410 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
411 IGS_GE_MSG_STACK.ADD;
412 App_Exception.Raise_Exception;
413 END IF;
414 END IF;
415 IF (((old_references.fee_type = new_references.fee_type) AND
416 (old_references.fee_cal_type = new_references.fee_cal_type) AND
417 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
418 ((new_references.fee_type IS NULL) OR
419 (new_references.fee_cal_type IS NULL) OR
420 (new_references.fee_ci_sequence_number IS NULL))) THEN
421 NULL;
422 ELSE
423 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
424 new_references.fee_type,
425 new_references.fee_cal_type,
426 new_references.fee_ci_sequence_number
427 ) THEN
428 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
429 IGS_GE_MSG_STACK.ADD;
430 App_Exception.Raise_Exception;
431 END IF;
432 END IF;
433 IF (((old_references.rul_sequence_number = new_references.rul_sequence_number)) OR
434 ((new_references.rul_sequence_number IS NULL))) THEN
435 NULL;
436 ELSE
437 IF NOT IGS_RU_RULE_PKG.Get_PK_For_Validation (
438 new_references.rul_sequence_number
442 App_Exception.Raise_Exception;
439 ) THEN
440 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
441 IGS_GE_MSG_STACK.ADD;
443 END IF;
444 END IF;
445 END Check_Parent_Existance;
446
447 -- Removed FOR UPDATE NOWAIT clause from the cur_rowid to avoid locking problems.
448 -- This has been done as part of Enh# 2747325, Locking Issues.
449 FUNCTION Get_PK_For_Validation (
450 x_fee_cat IN VARCHAR2,
451 x_fee_cal_type IN VARCHAR2,
452 x_fee_ci_sequence_number IN NUMBER,
453 x_fee_type IN VARCHAR2
454 ) RETURN BOOLEAN AS
455 CURSOR cur_rowid IS
456 SELECT rowid
457 FROM IGS_FI_F_CAT_FEE_LBL_ALL
458 WHERE fee_cat = x_fee_cat
459 AND fee_cal_type = x_fee_cal_type
460 AND fee_ci_sequence_number = x_fee_ci_sequence_number
461 AND fee_type = x_fee_type;
462 lv_rowid cur_rowid%RowType;
463 BEGIN
464 Open cur_rowid;
465 Fetch cur_rowid INTO lv_rowid;
466 IF (cur_rowid%FOUND) THEN
467 Close cur_rowid;
468 Return (TRUE);
469 ELSE
470 Close cur_rowid;
471 Return (FALSE);
472 END IF;
473 END Get_PK_For_Validation;
474
475 FUNCTION Get_UK1_For_Validation (
476 x_fee_cat IN VARCHAR2,
477 x_fee_cal_type IN VARCHAR2,
478 x_fee_ci_sequence_number IN NUMBER,
479 x_fee_type IN VARCHAR2,
480 x_s_chg_method_type IN VARCHAR2
481 ) RETURN BOOLEAN AS
482 CURSOR cur_rowid IS
483 SELECT rowid
484 FROM IGS_FI_F_CAT_FEE_LBL_ALL
485 WHERE fee_cat = x_fee_cat
486 AND fee_cal_type = x_fee_cal_type
487 AND fee_ci_sequence_number = x_fee_ci_sequence_number
488 AND fee_type = x_fee_type
489 AND s_chg_method_type = x_s_chg_method_type
490 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
491 FOR UPDATE NOWAIT;
492 lv_rowid cur_rowid%RowType;
493 BEGIN
494 Open cur_rowid;
495 Fetch cur_rowid INTO lv_rowid;
496 IF (cur_rowid%FOUND) THEN
497 Close cur_rowid;
498 Return (TRUE);
499 ELSE
500 Close cur_rowid;
501 Return (FALSE);
502 END IF;
503 END Get_UK1_For_Validation;
504 PROCEDURE GET_FK_IGS_CA_DA_INST (
505 x_dt_alias IN VARCHAR2,
506 x_sequence_number IN NUMBER,
507 x_cal_type IN VARCHAR2,
508 x_ci_sequence_number IN NUMBER
509 ) AS
510 CURSOR cur_rowid IS
511 SELECT rowid
512 FROM IGS_FI_F_CAT_FEE_LBL_ALL
513 WHERE start_dt_alias = x_dt_alias
514 AND start_dai_sequence_number = x_sequence_number
515 AND fee_cal_type = x_cal_type
516 AND fee_ci_sequence_number = x_ci_sequence_number ;
517 lv_rowid cur_rowid%RowType;
518 BEGIN
519 Open cur_rowid;
520 Fetch cur_rowid INTO lv_rowid;
521 IF (cur_rowid%FOUND) THEN
522 Close cur_rowid;
523 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCFL_DAI_FK');
524 IGS_GE_MSG_STACK.ADD;
525 App_Exception.Raise_Exception;
526 Return;
527 END IF;
528 Close cur_rowid;
529 END GET_FK_IGS_CA_DA_INST;
530 PROCEDURE GET_FK_IGS_FI_F_CAT_CA_INST (
531 x_fee_cat IN VARCHAR2,
532 x_fee_cal_type IN VARCHAR2,
533 x_fee_ci_sequence_number IN NUMBER
534 ) AS
535 CURSOR cur_rowid IS
536 SELECT rowid
537 FROM IGS_FI_F_CAT_FEE_LBL_ALL
538 WHERE fee_cat = x_fee_cat
539 AND fee_cal_type = x_fee_cal_type
540 AND fee_ci_sequence_number = x_fee_ci_sequence_number ;
541 lv_rowid cur_rowid%RowType;
542 BEGIN
543 Open cur_rowid;
544 Fetch cur_rowid INTO lv_rowid;
545 IF (cur_rowid%FOUND) THEN
546 Close cur_rowid;
547 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCFL_FCCI_FK');
548 IGS_GE_MSG_STACK.ADD;
549 App_Exception.Raise_Exception;
550 Return;
551 END IF;
552 Close cur_rowid;
553 END GET_FK_IGS_FI_F_CAT_CA_INST;
554 PROCEDURE GET_FK_IGS_FI_FEE_STR_STAT (
555 x_fee_structure_status IN VARCHAR2
556 ) AS
557 CURSOR cur_rowid IS
558 SELECT rowid
559 FROM IGS_FI_F_CAT_FEE_LBL_ALL
560 WHERE fee_liability_status = x_fee_structure_status ;
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 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCFL_FSST_FK');
568 IGS_GE_MSG_STACK.ADD;
569 App_Exception.Raise_Exception;
570 Return;
571 END IF;
572 Close cur_rowid;
573 END GET_FK_IGS_FI_FEE_STR_STAT;
574
575 PROCEDURE GET_FK_IGS_RU_RULE (
576 x_sequence_number IN NUMBER
577 ) AS
578 CURSOR cur_rowid IS
579 SELECT rowid
580 FROM IGS_FI_F_CAT_FEE_LBL_ALL
581 WHERE rul_sequence_number = x_sequence_number ;
582 lv_rowid cur_rowid%RowType;
583 BEGIN
584 Open cur_rowid;
585 Fetch cur_rowid INTO lv_rowid;
586 IF (cur_rowid%FOUND) THEN
587 Close cur_rowid;
591 Return;
588 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCFL_RUL_FK');
589 IGS_GE_MSG_STACK.ADD;
590 App_Exception.Raise_Exception;
592 END IF;
593 Close cur_rowid;
594 END GET_FK_IGS_RU_RULE;
595 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
596 x_s_chg_method_type IN VARCHAR2
597 ) AS
598 CURSOR cur_rowid IS
599 SELECT rowid
600 FROM IGS_FI_F_CAT_FEE_LBL_ALL
601 WHERE s_chg_method_type = x_s_chg_method_type ;
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_FCFL_LKUP_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_LOOKUPS_VIEW;
615
616 PROCEDURE Before_DML (
617 p_action IN VARCHAR2,
618 x_rowid IN VARCHAR2,
619 x_fee_cat IN VARCHAR2,
620 x_fee_cal_type IN VARCHAR2,
621 x_fee_ci_sequence_number IN NUMBER,
622 x_fee_type IN VARCHAR2,
623 x_fee_liability_status IN VARCHAR2,
624 x_start_dt_alias IN VARCHAR2,
625 x_start_dai_sequence_number IN NUMBER,
626 x_s_chg_method_type IN VARCHAR2,
627 x_rul_sequence_number IN NUMBER,
628 x_org_id IN NUMBER,
629 x_creation_date IN DATE,
630 x_created_by IN NUMBER,
631 x_last_update_date IN DATE,
632 x_last_updated_by IN NUMBER,
633 x_last_update_login IN NUMBER,
634 x_waiver_calc_flag IN VARCHAR2
635 ) AS
636 /*************************************************************
637 Created By :
638 Date Created By :
639 Purpose :
640 Know limitations, enhancements or remarks
641 Change History
642 Who When What
643 shtatiko 10-FEB-2003 Enh# 2747325, Removed cases for p_action = 'DELETE' and
644 p_action = 'VALIDATE_DELETE'
645 vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank and its reference
646 from the call to set_column_values.
647 SCHODAVA 13-OCT-2001 Bug # 2021281
648 Added the call to
649 BeforeRowInsertUpdateDelete1
650 for p_action = 'VALIDATE_DELETE'
651 (reverse chronological order - newest change first)
652 ***************************************************************/
653
654 BEGIN
655 Set_Column_Values (
656 p_action,
657 x_rowid,
658 x_fee_cat,
659 x_fee_cal_type,
660 x_fee_ci_sequence_number,
661 x_fee_type,
662 x_fee_liability_status,
663 x_start_dt_alias,
664 x_start_dai_sequence_number,
665 x_s_chg_method_type,
666 x_rul_sequence_number,
667 x_org_id,
668 x_creation_date,
669 x_created_by,
670 x_last_update_date,
671 x_last_updated_by,
672 x_last_update_login,
673 x_waiver_calc_flag
674 );
675 IF (p_action = 'INSERT') THEN
676 -- Call all the procedures related to Before Insert.
677 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating =>FALSE , p_deleting => FALSE );
678 IF (Get_PK_For_Validation (
679 x_fee_cat,
680 x_fee_cal_type,
681 x_fee_ci_sequence_number,
682 x_fee_type
683 )) THEN
684 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
685 IGS_GE_MSG_STACK.ADD;
686 App_Exception.Raise_Exception;
687 END IF;
688 Check_Uniqueness;
689 Check_Constraints;
690 Check_Parent_Existance;
691 ELSIF (p_action = 'UPDATE') THEN
692 -- Call all the procedures related to Before Update.
693 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => TRUE , p_deleting => FALSE );
694 Check_Uniqueness;
695 Check_Constraints;
696 Check_Parent_Existance;
697 ELSIF (p_action = 'VALIDATE_INSERT') THEN
698 IF (Get_PK_For_Validation (
699 x_fee_cat,
700 x_fee_cal_type,
701 x_fee_ci_sequence_number,
702 x_fee_type
703 )) THEN
704 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
705 IGS_GE_MSG_STACK.ADD;
706 App_Exception.Raise_Exception;
707 END IF;
708 Check_Uniqueness;
709 Check_Constraints;
710 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
711 Check_Uniqueness;
712 Check_Constraints;
713 END IF;
714 END Before_DML;
715
716 PROCEDURE After_DML (
717 p_action IN VARCHAR2,
718 x_rowid IN VARCHAR2
719 ) AS
720 /*----------------------------------------------------------------------------
721 || Created By :
722 || Created On :
723 || Purpose :
724 || Known limitations, enhancements or remarks :
725 || Change History :
726 || Who When What
727 || (reverse chronological order - newest change first)
728 || smvk 02-Sep-2002 Call to AfterRowUpdate3 is updated to pass all the parameter as per the Apps
729 || standard. As a part of Build SFCR005_Cleanup_Build (Enhancement Bug # 2531390)
733 || ii)p_action='DELETE' as no code exists in those conditions.
730 || vvutukur 19-Jul-2002 Bug#2425767.removed (earlier commented) calls to AfterStmtInsertUpdate4
731 || procedure if p_action='INSERT' or 'UPDATE' as this procedure itself is
732 || removed from this package body.Removed if conditions i)if p_action='INSERT'
734 ----------------------------------------------------------------------------*/
735 BEGIN
736 l_rowid := x_rowid;
737 IF (p_action = 'UPDATE') THEN
738 -- Call all the procedures related to After Update.
739 AfterRowUpdate3 (p_inserting => FALSE, p_updating => TRUE, p_deleting =>FALSE );
740 END IF;
741 END After_DML;
742
743 procedure INSERT_ROW (
744 X_ROWID in out NOCOPY VARCHAR2,
745 X_FEE_CAT in VARCHAR2,
746 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
747 X_FEE_TYPE in VARCHAR2,
748 X_FEE_CAL_TYPE in VARCHAR2,
749 X_FEE_LIABILITY_STATUS in VARCHAR2,
750 X_START_DT_ALIAS in VARCHAR2,
751 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
752 X_S_CHG_METHOD_TYPE in VARCHAR2,
753 X_RUL_SEQUENCE_NUMBER in NUMBER,
754 X_ORG_ID in NUMBER,
755 X_MODE in VARCHAR2,
756 X_WAIVER_CALC_FLAG IN VARCHAR2
757 ) is
758 /************************************************************************************
759 vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank and from call to
760 before_dml and from insert statement.
761 sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
762 in call to before_dml as part of SWCR006 build.
763 *****************************************************************************/
764 cursor C is select ROWID from IGS_FI_F_CAT_FEE_LBL_ALL
765 where FEE_CAT = X_FEE_CAT
766 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
767 and FEE_TYPE = X_FEE_TYPE
768 and FEE_CAL_TYPE = X_FEE_CAL_TYPE;
769 X_LAST_UPDATE_DATE DATE;
770 X_LAST_UPDATED_BY NUMBER;
771 X_LAST_UPDATE_LOGIN NUMBER;
772 X_REQUEST_ID NUMBER;
773 X_PROGRAM_ID NUMBER;
774 X_PROGRAM_APPLICATION_ID NUMBER;
775 X_PROGRAM_UPDATE_DATE DATE;
776 begin
777 X_LAST_UPDATE_DATE := SYSDATE;
778 if(X_MODE = 'I') then
779 X_LAST_UPDATED_BY := 1;
780 X_LAST_UPDATE_LOGIN := 0;
781 elsif (X_MODE = 'R') then
782 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
783 if X_LAST_UPDATED_BY is NULL then
784 X_LAST_UPDATED_BY := -1;
785 end if;
786 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
787 if X_LAST_UPDATE_LOGIN is NULL then
788 X_LAST_UPDATE_LOGIN := -1;
789 end if;
790 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
791 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
792 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
793 if (X_REQUEST_ID = -1 ) then
794 X_REQUEST_ID:=NULL;
795 X_PROGRAM_ID:=NULL;
796 X_PROGRAM_APPLICATION_ID:=NULL;
797 X_PROGRAM_UPDATE_DATE:=NULL;
798 else
799 X_PROGRAM_UPDATE_DATE:=SYSDATE;
800 end if;
801 else
802 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
803 IGS_GE_MSG_STACK.ADD;
804 app_exception.raise_exception;
805 end if;
806 Before_DML(
807 p_action=>'INSERT',
808 x_rowid=>X_ROWID,
809 x_fee_cal_type=>X_FEE_CAL_TYPE,
810 x_fee_cat=>X_FEE_CAT,
811 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
812 x_fee_liability_status=>X_FEE_LIABILITY_STATUS,
813 x_fee_type=>X_FEE_TYPE,
814 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
815 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
816 x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
817 x_start_dt_alias=>X_START_DT_ALIAS,
818 x_org_id=>igs_ge_gen_003.get_org_id,
819 x_creation_date=>X_LAST_UPDATE_DATE,
820 x_created_by=>X_LAST_UPDATED_BY,
821 x_last_update_date=>X_LAST_UPDATE_DATE,
822 x_last_updated_by=>X_LAST_UPDATED_BY,
823 x_last_update_login=>X_LAST_UPDATE_LOGIN,
824 x_waiver_calc_flag => X_WAIVER_CALC_FLAG
825 );
826 insert into IGS_FI_F_CAT_FEE_LBL_ALL (
827 FEE_CAT,
828 FEE_CAL_TYPE,
829 FEE_CI_SEQUENCE_NUMBER,
830 FEE_TYPE,
831 FEE_LIABILITY_STATUS,
832 START_DT_ALIAS,
833 START_DAI_SEQUENCE_NUMBER,
834 S_CHG_METHOD_TYPE,
835 RUL_SEQUENCE_NUMBER,
836 ORG_ID,
837 CREATION_DATE,
838 CREATED_BY,
839 LAST_UPDATE_DATE,
840 LAST_UPDATED_BY,
841 LAST_UPDATE_LOGIN,
842 REQUEST_ID,
843 PROGRAM_ID,
844 PROGRAM_APPLICATION_ID,
845 PROGRAM_UPDATE_DATE,
846 WAIVER_CALC_FLAG
847 ) values (
848 NEW_REFERENCES.FEE_CAT,
849 NEW_REFERENCES.FEE_CAL_TYPE,
850 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
851 NEW_REFERENCES.FEE_TYPE,
852 NEW_REFERENCES.FEE_LIABILITY_STATUS,
853 NEW_REFERENCES.START_DT_ALIAS,
854 NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
855 NEW_REFERENCES.S_CHG_METHOD_TYPE,
856 NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
857 NEW_REFERENCES.ORG_ID,
858 X_LAST_UPDATE_DATE,
859 X_LAST_UPDATED_BY,
860 X_LAST_UPDATE_DATE,
861 X_LAST_UPDATED_BY,
862 X_LAST_UPDATE_LOGIN,
863 X_REQUEST_ID,
864 X_PROGRAM_ID,
865 X_PROGRAM_APPLICATION_ID,
869 open c;
866 X_PROGRAM_UPDATE_DATE,
867 NEW_REFERENCES.WAIVER_CALC_FLAG
868 );
870 fetch c into X_ROWID;
871 if (c%notfound) then
872 close c;
873 raise no_data_found;
874 end if;
875 close c;
876 After_DML (
877 p_action => 'INSERT',
878 x_rowid => X_ROWID
879 );
880 end INSERT_ROW;
881 procedure LOCK_ROW (
882 X_ROWID in VARCHAR2,
883 X_FEE_CAT in VARCHAR2,
884 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
885 X_FEE_TYPE in VARCHAR2,
886 X_FEE_CAL_TYPE in VARCHAR2,
887 X_FEE_LIABILITY_STATUS in VARCHAR2,
888 X_START_DT_ALIAS in VARCHAR2,
889 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
890 X_S_CHG_METHOD_TYPE in VARCHAR2,
891 X_RUL_SEQUENCE_NUMBER in NUMBER,
892 X_WAIVER_CALC_FLAG IN VARCHAR2
893 ) is
894 /*----------------------------------------------------------------------------
895 || Created By :
896 || Created On :
897 || Purpose :
898 || Known limitations, enhancements or remarks :
899 || Change History :
900 || Who When What
901 || (reverse chronological order - newest change first)
902 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank
903 || and its reference from cursor c1 and from if condition.
904 ----------------------------------------------------------------------------*/
905 cursor c1 is select
906 FEE_LIABILITY_STATUS,
907 START_DT_ALIAS,
908 START_DAI_SEQUENCE_NUMBER,
909 S_CHG_METHOD_TYPE,
910 RUL_SEQUENCE_NUMBER,
911 WAIVER_CALC_FLAG
912 from IGS_FI_F_CAT_FEE_LBL_ALL
913 where ROWID=X_ROWID
914 for update nowait;
915 tlinfo c1%rowtype;
916 begin
917 open c1;
918 fetch c1 into tlinfo;
919 if (c1%notfound) then
920 close c1;
921 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
922 IGS_GE_MSG_STACK.ADD;
923 app_exception.raise_exception;
924 return;
925 end if;
926 close c1;
927 if ( (tlinfo.FEE_LIABILITY_STATUS = X_FEE_LIABILITY_STATUS)
928 AND ((tlinfo.START_DT_ALIAS = X_START_DT_ALIAS)
929 OR ((tlinfo.START_DT_ALIAS is null)
930 AND (X_START_DT_ALIAS is null)))
931 AND ((tlinfo.START_DAI_SEQUENCE_NUMBER = X_START_DAI_SEQUENCE_NUMBER)
932 OR ((tlinfo.START_DAI_SEQUENCE_NUMBER is null)
933 AND (X_START_DAI_SEQUENCE_NUMBER is null)))
934 AND ((tlinfo.S_CHG_METHOD_TYPE = X_S_CHG_METHOD_TYPE)
935 OR ((tlinfo.S_CHG_METHOD_TYPE is null)
936 AND (X_S_CHG_METHOD_TYPE is null)))
937 AND ((tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
938 OR ((tlinfo.RUL_SEQUENCE_NUMBER is null)
939 AND (X_RUL_SEQUENCE_NUMBER is null)))
940 AND ((tlinfo.WAIVER_CALC_FLAG = X_WAIVER_CALC_FLAG)
941 OR ((tlinfo.WAIVER_CALC_FLAG is null)
942 AND (X_WAIVER_CALC_FLAG is null)))
943 ) then
944 null;
945 else
946 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
947 IGS_GE_MSG_STACK.ADD;
948 app_exception.raise_exception;
949 end if;
950 return;
951 end LOCK_ROW;
952 procedure UPDATE_ROW (
953 X_ROWID in VARCHAR2,
954 X_FEE_CAT in VARCHAR2,
955 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
956 X_FEE_TYPE in VARCHAR2,
957 X_FEE_CAL_TYPE in VARCHAR2,
958 X_FEE_LIABILITY_STATUS in VARCHAR2,
959 X_START_DT_ALIAS in VARCHAR2,
960 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
961 X_S_CHG_METHOD_TYPE in VARCHAR2,
962 X_RUL_SEQUENCE_NUMBER in NUMBER,
963 X_MODE in VARCHAR2,
964 X_WAIVER_CALC_FLAG IN VARCHAR2
965 ) is
966 /*----------------------------------------------------------------------------
967 || Created By :
968 || Created On :
969 || Purpose :
970 || Known limitations, enhancements or remarks :
971 || Change History :
972 || Who When What
973 || (reverse chronological order - newest change first)
974 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank
975 || and from call to before_dml and from update statement.
976 ----------------------------------------------------------------------------*/
977 X_LAST_UPDATE_DATE DATE;
978 X_LAST_UPDATED_BY NUMBER;
979 X_LAST_UPDATE_LOGIN NUMBER;
980 X_REQUEST_ID NUMBER;
981 X_PROGRAM_ID NUMBER;
982 X_PROGRAM_APPLICATION_ID NUMBER;
983 X_PROGRAM_UPDATE_DATE DATE;
984 begin
985 X_LAST_UPDATE_DATE := SYSDATE;
986 if(X_MODE = 'I') then
987 X_LAST_UPDATED_BY := 1;
988 X_LAST_UPDATE_LOGIN := 0;
989 elsif (X_MODE = 'R') then
990 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
991 if X_LAST_UPDATED_BY is NULL then
992 X_LAST_UPDATED_BY := -1;
993 end if;
994 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
995 if X_LAST_UPDATE_LOGIN is NULL then
996 X_LAST_UPDATE_LOGIN := -1;
997 end if;
998 X_REQUEST_ID:=FND_GLOBAL.CONC_REQUEST_ID;
999 X_PROGRAM_ID:=FND_GLOBAL.CONC_PROGRAM_ID;
1000 X_PROGRAM_APPLICATION_ID:=FND_GLOBAL.PROG_APPL_ID;
1001 if (X_REQUEST_ID = -1 ) then
1002 X_REQUEST_ID:=OLD_REFERENCES.REQUEST_ID;
1003 X_PROGRAM_ID:=OLD_REFERENCES.PROGRAM_ID;
1004 X_PROGRAM_APPLICATION_ID:=OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1005 X_PROGRAM_UPDATE_DATE:=OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1006 else
1007 X_PROGRAM_UPDATE_DATE:=SYSDATE;
1008 end if;
1009 else
1010 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1011 IGS_GE_MSG_STACK.ADD;
1012 app_exception.raise_exception;
1013 end if;
1014 Before_DML(
1015 p_action=>'UPDATE',
1016 x_rowid=>X_ROWID,
1017 x_fee_cal_type=>X_FEE_CAL_TYPE,
1018 x_fee_cat=>X_FEE_CAT,
1019 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
1020 x_fee_liability_status=>X_FEE_LIABILITY_STATUS,
1021 x_fee_type=>X_FEE_TYPE,
1022 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
1023 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
1024 x_start_dai_sequence_number=>X_START_DAI_SEQUENCE_NUMBER,
1025 x_start_dt_alias=>X_START_DT_ALIAS,
1026 x_creation_date=>X_LAST_UPDATE_DATE,
1027 x_created_by=>X_LAST_UPDATED_BY,
1028 x_last_update_date=>X_LAST_UPDATE_DATE,
1029 x_last_updated_by=>X_LAST_UPDATED_BY,
1030 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1031 x_waiver_calc_flag => X_WAIVER_CALC_FLAG
1032 );
1033 update IGS_FI_F_CAT_FEE_LBL_ALL set
1034 FEE_LIABILITY_STATUS = NEW_REFERENCES.FEE_LIABILITY_STATUS,
1035 START_DT_ALIAS = NEW_REFERENCES.START_DT_ALIAS,
1036 START_DAI_SEQUENCE_NUMBER = NEW_REFERENCES.START_DAI_SEQUENCE_NUMBER,
1037 S_CHG_METHOD_TYPE = NEW_REFERENCES.S_CHG_METHOD_TYPE,
1038 RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
1039 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1040 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1041 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1042 REQUEST_ID=X_REQUEST_ID,
1043 PROGRAM_ID=X_PROGRAM_ID,
1044 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID,
1045 PROGRAM_UPDATE_DATE=X_PROGRAM_UPDATE_DATE,
1046 WAIVER_CALC_FLAG=X_WAIVER_CALC_FLAG
1047 where ROWID=X_ROWID
1048 ;
1049 if (sql%notfound) then
1050 raise no_data_found;
1051 end if;
1052 After_DML (
1053 p_action => 'UPDATE',
1054 x_rowid => X_ROWID
1055 );
1056 end UPDATE_ROW;
1057 procedure ADD_ROW (
1058 X_ROWID in out NOCOPY VARCHAR2,
1059 X_FEE_CAT in VARCHAR2,
1060 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1061 X_FEE_TYPE in VARCHAR2,
1062 X_FEE_CAL_TYPE in VARCHAR2,
1063 X_FEE_LIABILITY_STATUS in VARCHAR2,
1064 X_START_DT_ALIAS in VARCHAR2,
1065 X_START_DAI_SEQUENCE_NUMBER in NUMBER,
1066 X_S_CHG_METHOD_TYPE in VARCHAR2,
1067 X_RUL_SEQUENCE_NUMBER in NUMBER,
1068 X_ORG_ID in NUMBER,
1069 X_MODE in VARCHAR2,
1070 X_WAIVER_CALC_FLAG IN VARCHAR2
1071 ) is
1072 /*----------------------------------------------------------------------------
1073 || Created By :
1074 || Created On :
1075 || Purpose :
1076 || Known limitations, enhancements or remarks :
1077 || Change History :
1078 || Who When What
1079 || (reverse chronological order - newest change first)
1080 || vvutukur 19-Jul-2002 Bug#2425767.removed parameter x_payment_hierarchy_rank
1081 || and from calls to insert_row and update_row.
1082 ----------------------------------------------------------------------------*/
1083 cursor c1 is select rowid from IGS_FI_F_CAT_FEE_LBL_ALL
1084 where FEE_CAT = X_FEE_CAT
1085 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
1086 and FEE_TYPE = X_FEE_TYPE
1087 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
1088 ;
1089 begin
1090 open c1;
1091 fetch c1 into X_ROWID;
1092 if (c1%notfound) then
1093 close c1;
1094 INSERT_ROW (
1095 X_ROWID,
1096 X_FEE_CAT,
1097 X_FEE_CI_SEQUENCE_NUMBER,
1098 X_FEE_TYPE,
1099 X_FEE_CAL_TYPE,
1100 X_FEE_LIABILITY_STATUS,
1101 X_START_DT_ALIAS,
1102 X_START_DAI_SEQUENCE_NUMBER,
1103 X_S_CHG_METHOD_TYPE,
1104 X_RUL_SEQUENCE_NUMBER,
1105 X_ORG_ID,
1106 X_MODE,
1107 X_WAIVER_CALC_FLAG
1108 );
1109 return;
1110 end if;
1111 close c1;
1112 UPDATE_ROW (
1113 X_ROWID,
1114 X_FEE_CAT,
1115 X_FEE_CI_SEQUENCE_NUMBER,
1116 X_FEE_TYPE,
1117 X_FEE_CAL_TYPE,
1118 X_FEE_LIABILITY_STATUS,
1119 X_START_DT_ALIAS,
1120 X_START_DAI_SEQUENCE_NUMBER,
1121 X_S_CHG_METHOD_TYPE,
1122 X_RUL_SEQUENCE_NUMBER,
1123 X_MODE,
1124 X_WAIVER_CALC_FLAG
1125 );
1126 end ADD_ROW;
1127
1128 end IGS_FI_F_CAT_FEE_LBL_PKG;
[Home] [Help]