1 package body IGS_FI_FEE_RET_SCHD_PKG AS
2 /* $Header: IGSSI33B.pls 120.1 2006/05/26 10:55:26 sapanigr noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_FI_FEE_RET_SCHD%RowType;
5 new_references IGS_FI_FEE_RET_SCHD%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
10 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
11 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
12 x_sequence_number IN NUMBER DEFAULT NULL,
13 x_fee_cat IN VARCHAR2 DEFAULT NULL,
14 x_fee_type IN VARCHAR2 DEFAULT NULL,
15 x_schedule_number IN NUMBER DEFAULT NULL,
16 x_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_dai_sequence_number IN NUMBER DEFAULT NULL,
18 x_retention_percentage IN NUMBER DEFAULT NULL,
19 x_retention_amount IN NUMBER DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
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 || vvutukur 24-Jul-2002 Bug#2425767.removed parameter x_deduction_amount and its reference in
35 || copying old_references value into new_references.value.
36 ----------------------------------------------------------------------------*/
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_FI_FEE_RET_SCHD
40 WHERE rowid = x_rowid;
41 BEGIN
42 l_rowid := x_rowid;
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 Open cur_old_ref_values;
46 Fetch cur_old_ref_values INTO old_references;
47 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
48 Close cur_old_ref_values;
49 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
50 IGS_GE_MSG_STACK.ADD;
51 App_Exception.Raise_Exception;
52 Return;
53 END IF;
54 Close cur_old_ref_values;
55 -- Populate New Values.
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.s_relation_type := x_s_relation_type;
59 new_references.sequence_number := x_sequence_number;
60 new_references.fee_cat := x_fee_cat;
61 new_references.fee_type := x_fee_type;
62 new_references.schedule_number := x_schedule_number;
63 new_references.dt_alias := x_dt_alias;
64 new_references.dai_sequence_number := x_dai_sequence_number;
65 new_references.retention_percentage := x_retention_percentage;
66 new_references.retention_amount := x_retention_amount;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77 END Set_Column_Values;
78 -- Trigger description :-
79 -- "OSS_TST".trg_frtns_br_iu
80 -- BEFORE INSERT OR UPDATE
81 -- ON IGS_FI_FEE_RET_SCHD
82 -- FOR EACH ROW
83 PROCEDURE BeforeRowInsertUpdate1(
84 p_inserting IN BOOLEAN DEFAULT FALSE,
85 p_updating IN BOOLEAN DEFAULT FALSE,
86 p_deleting IN BOOLEAN DEFAULT FALSE
87 ) AS
88 v_message_name varchar2(30);
89 BEGIN
90 -- Validate the fee relationship
91 IF (p_inserting) THEN
92 IF IGS_FI_VAL_FE.finp_val_sched_mbrs (
93 new_references.s_relation_type,
94 new_references.fee_cat,
95 new_references.fee_type,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS',v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102 -- Validate the Fee Type Fee Trigger Cat
103 IF (p_inserting) THEN
104 IF IGS_FI_VAL_FRTNS.finp_val_frtns_ft (
105 new_references.fee_type,
106 new_references.s_relation_type,
107 v_message_name) = FALSE THEN
108 Fnd_Message.Set_Name('IGS',v_message_name);
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113 -- Validate the retention amount and percentage
114 IF (p_inserting OR
115 nvl(old_references.retention_amount,0) <> nvl(new_references.retention_amount,0) OR
116 nvl(old_references.retention_percentage,0) <> nvl(new_references.retention_percentage,0)) THEN
117 IF IGS_FI_VAL_FRTNS.finp_val_frtns_amt (
118 new_references.retention_amount,
119 new_references.retention_percentage,
120 v_message_name) = FALSE THEN
121 Fnd_Message.Set_Name('IGS',v_message_name);
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125 END IF;
126 -- Check related fee cat fee liabilities do not clash with the local currency
127 IF p_inserting THEN
128 IF IGS_FI_VAL_FRTNS.finp_val_frtns_cur (
129 new_references.fee_cal_type,
130 new_references.fee_ci_sequence_number,
131 new_references.fee_type,
132 new_references.s_relation_type,
133 v_message_name) = FALSE THEN
134 Fnd_Message.Set_Name('IGS',v_message_name);
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139 END BeforeRowInsertUpdate1;
140 -- Trigger description :-
141 -- "OSS_TST".trg_frtns_ar_u_hist
142 -- AFTER UPDATE
143 -- ON IGS_FI_FEE_RET_SCHD
144 -- FOR EACH ROW
145 PROCEDURE AfterRowUpdate3(
146 p_inserting IN BOOLEAN DEFAULT FALSE,
147 p_updating IN BOOLEAN DEFAULT FALSE,
148 p_deleting IN BOOLEAN DEFAULT FALSE
149 ) AS
150 /*----------------------------------------------------------------------------
151 || Created By :
152 || Created On :
153 || Purpose :
154 || Known limitations, enhancements or remarks :
155 || Change History :
156 || Who When What
157 || (reverse chronological order - newest change first)
158 || vvutukur 24-Jul-2002 Bug#2425767.removed references to deduction_amount from call to
159 || IGS_FI_GEN_002.FINP_INS_FRTNS_HIST.
160 ----------------------------------------------------------------------------*/
161 BEGIN
162 -- create a history
163 IGS_FI_GEN_002.FINP_INS_FRTNS_HIST( old_references.fee_cal_type,
164 old_references.fee_ci_sequence_number,
165 old_references.s_relation_type,
166 old_references.sequence_number,
167 new_references.fee_type,
168 old_references.fee_type,
169 new_references.fee_cat,
170 old_references.fee_cat,
171 new_references.schedule_number,
172 old_references.schedule_number,
173 new_references.dt_alias,
174 old_references.dt_alias,
175 new_references.dai_sequence_number,
176 old_references.dai_sequence_number,
177 new_references.retention_percentage,
178 old_references.retention_percentage,
179 new_references.retention_amount,
180 old_references.retention_amount,
181 new_references.last_updated_by,
182 old_references.last_updated_by,
183 new_references.last_update_date,
184 old_references.last_update_date);
185 END AfterRowUpdate3;
186 -- Trigger description :-
187 -- "OSS_TST".trg_frtns_as_iu
188 -- AFTER INSERT OR UPDATE
189 -- ON IGS_FI_FEE_RET_SCHD
190 PROCEDURE AfterStmtInsertUpdate4(
191 p_inserting IN BOOLEAN DEFAULT FALSE,
192 p_updating IN BOOLEAN DEFAULT FALSE,
193 p_deleting IN BOOLEAN DEFAULT FALSE
194 ) AS
195 v_message_name varchar2(30);
196 BEGIN
197 -- Validate the dt alias.
198 IF p_inserting OR p_updating THEN
199 IF IGS_FI_VAL_FRTNS.finp_val_frtns_creat (
200 new_references.fee_type,
201 new_references.fee_cal_type,
202 new_references.fee_ci_sequence_number,
203 new_references.s_relation_type,
204 v_message_name) = FALSE THEN
205 Fnd_Message.Set_Name('IGS',v_message_name);
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 END IF;
209 END IF;
210 END AfterStmtInsertUpdate4;
211 PROCEDURE Check_Constraints (
212 Column_Name IN VARCHAR2 DEFAULT NULL,
213 Column_Value IN VARCHAR2 DEFAULT NULL
214 ) AS
215 /*----------------------------------------------------------------------------
216 || Created By :
217 || Created On :
218 || Purpose :
219 || Known limitations, enhancements or remarks :
220 || Change History :
221 || Who When What
222 || (reverse chronological order - newest change first)
223 || sapanigr 26-May-2006 Enh 5217319. Removed highest value criteria for item 'CHG_RATE'
224 || vvutukur 24-Jul-2002 Bug#2425767.Removed reference to deduction_amount.
225 || agairola 14-Jun-2002 Bug 2403209: Removed the validation for deduction amount
226 || vvutukur 17-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
227 ----------------------------------------------------------------------------*/
228 BEGIN
229 IF Column_Name is NULL THEN
230 NULL;
231 ELSIF upper(Column_Name) = 'S_RELATION_TYPE' then
232 new_references.s_relation_type := Column_Value;
233 ELSIF upper(Column_Name) = 'SCHEDULE_NUMBER' then
234 new_references.schedule_number := igs_ge_number.to_num(Column_Value);
235 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
236 new_references.sequence_number := igs_ge_number.to_num(Column_Value);
237 ELSIF upper(Column_Name) = 'DAI_SEQUENCE_NUMBER' then
238 new_references.dai_sequence_number := igs_ge_number.to_num(Column_Value);
239 ELSIF upper(Column_Name) = 'DT_ALIAS' then
240 new_references.dt_alias:= Column_Value;
241 ELSIF upper(Column_Name) = 'FEE_CAL_TYPE' then
242 new_references.fee_cal_type := Column_Value;
243 ELSIF upper(Column_Name) = 'RETENTION_AMOUNT' then
244 new_references.retention_amount := igs_ge_number.to_num(Column_Value);
245 ELSIF upper(Column_Name) = 'RETENTION_PERCENTAGE' then
246 new_references.retention_percentage := igs_ge_number.to_num(Column_Value);
247 ELSIF upper(Column_Name) = 'FEE_CI_SEQUENCE_NUMBER' then
248 new_references.fee_ci_sequence_number := igs_ge_number.to_num(Column_Value);
249 END IF;
250
251
252 IF upper(Column_Name) = 'FEE_CI_SEQUENCE_NUMBER' OR
253 column_name is NULL THEN
254 IF new_references.fee_ci_sequence_number < 1 OR
255 new_references.fee_ci_sequence_number > 999999 THEN
256 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 END IF;
261
262 -- The following code checks for check constraints on the Columns.
263 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR
264 column_name is NULL THEN
265 IF new_references.sequence_number < 1 OR
266 new_references.sequence_number > 999999 THEN
267 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
268 IGS_GE_MSG_STACK.ADD;
269 App_Exception.Raise_Exception;
270 END IF;
271 END IF;
272
273 -- The following code checks for check constraints on the Columns.
274 IF upper(Column_Name) = 'DAI_SEQUENCE_NUMBER' OR
275 column_name is NULL THEN
276 IF new_references.dai_sequence_number < 1 OR
277 new_references.dai_sequence_number > 999999 THEN
278 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 END IF;
283 -- The following code checks for check constraints on the Columns.
284 IF upper(Column_Name) = 'SCHEDULE_NUMBER' OR
285 column_name is NULL THEN
286 IF new_references.schedule_number < 1 OR
287 new_references.schedule_number > 999999 THEN
288 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
289 IGS_GE_MSG_STACK.ADD;
290 App_Exception.Raise_Exception;
291 END IF;
292 END IF;
293 IF upper(Column_Name) = 'S_RELATION_TYPE' OR column_name is NULL THEN
294 IF new_references.S_RELATION_TYPE <> 'FTCI' AND
295 new_references.S_RELATION_TYPE <> 'FCCI' AND
296 new_references.S_RELATION_TYPE <> 'FCFL'
297 THEN
298 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END IF;
302 END IF;
303 IF upper(Column_Name) = 'DT_ALIAS' OR
304 column_name is NULL THEN
305 IF new_references.dt_alias <> UPPER(new_references.dt_alias) THEN
306 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 END IF;
310 END IF;
311 IF upper(Column_Name) = 'FEE_CAL_TYPE' OR
312 column_name is NULL THEN
313 IF new_references.fee_cal_type <> UPPER(new_references.fee_cal_type) THEN
314 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 END IF;
318 END IF;
319
320 IF upper(Column_Name) = 'RETENTION_AMOUNT' OR
321 column_name is NULL THEN
322 IF new_references.retention_amount < 0 THEN
323 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 END IF;
327 END IF;
328 IF upper(Column_Name) = 'RETENTION_PERCENTAGE' OR
329 column_name is NULL THEN
330 IF new_references.retention_percentage < 0 OR
331 new_references.retention_percentage > 100 THEN
332 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 END IF;
336 END IF;
337 END Check_Constraints;
338 FUNCTION Get_UK1_For_Validation (
339 x_fee_cal_type IN VARCHAR2,
340 x_fee_ci_sequence_number IN NUMBER,
341 x_fee_cat IN VARCHAR2,
342 x_fee_type IN VARCHAR2,
343 x_dt_alias IN VARCHAR2,
344 x_s_relation_type IN VARCHAR2,
345 x_dai_sequence_number IN NUMBER
346 )RETURN BOOLEAN AS
347 CURSOR cur_rowid IS
348 SELECT rowid
349 FROM IGS_FI_FEE_RET_SCHD
350 WHERE fee_cal_type = x_fee_cal_type
351 AND fee_ci_sequence_number = x_fee_ci_sequence_number
352 AND ((fee_cat = x_fee_cat) OR (fee_cat IS NULL AND x_fee_cat IS NULL))
353 AND ((fee_type = x_fee_type) OR (fee_type IS NULL AND x_fee_type IS NULL))
354 AND dt_alias = x_dt_alias
355 AND dai_sequence_number = x_dai_sequence_number
356 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
357 lv_rowid cur_rowid%RowType;
358 BEGIN
359
360 /*x_s_relation_type is not used for UK checks - it is retained as is, in parameter list as of now */
361
362 Open cur_rowid;
363 Fetch cur_rowid INTO lv_rowid;
364 IF (cur_rowid%FOUND) THEN
365 Close cur_rowid;
366 Return (TRUE);
367 ELSE
368 Close cur_rowid;
369 Return (FALSE);
370 END IF;
371 END Get_UK1_For_Validation;
372
373
374 FUNCTION Get_UK2_For_Validation (
375 x_fee_cal_type IN VARCHAR2,
376 x_fee_ci_sequence_number IN NUMBER,
377 x_fee_cat IN VARCHAR2,
378 x_fee_type IN VARCHAR2,
379 x_schedule_number IN NUMBER
380 )RETURN BOOLEAN
381 AS
382 CURSOR cur_rowid IS
383 SELECT rowid
384 FROM IGS_FI_FEE_RET_SCHD
385 WHERE fee_cal_type = x_fee_cal_type
386 AND fee_ci_sequence_number = x_fee_ci_sequence_number
387 AND ((fee_type = x_fee_type) OR (fee_type IS NULL AND x_fee_type IS NULL))
388 AND ((fee_cat = x_fee_cat) OR (fee_cat IS NULL AND x_fee_cat IS NULL))
389 AND schedule_number = x_schedule_number
390 AND ((l_rowid IS NULL) OR (rowid <> l_rowid)) ;
391 lv_rowid cur_rowid%RowType;
392 BEGIN
393 Open cur_rowid;
394 Fetch cur_rowid INTO lv_rowid;
395 IF (cur_rowid%FOUND) THEN
396 Close cur_rowid;
397 Return (TRUE);
398 ELSE
399 Close cur_rowid;
400 Return (FALSE);
401 END IF;
402 END Get_UK2_For_Validation;
403
404 PROCEDURE Check_Uniqueness
405 AS
406 BEGIN
407 IF Get_UK1_For_Validation (
408 new_references.fee_cal_type ,
409 new_references.fee_ci_sequence_number ,
410 new_references.fee_cat,
411 new_references.fee_type,
412 new_references.dt_alias,
413 new_references.s_relation_type,
414 new_references.dai_sequence_number) THEN
415 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 END IF;
419 IF Get_UK2_For_Validation (
420 new_references.fee_cal_type,
421 new_references.fee_ci_sequence_number,
422 new_references.fee_cat,
423 new_references.fee_type,
424 new_references.schedule_number) THEN
425 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429
430 END Check_Uniqueness;
431
432 PROCEDURE Check_Parent_Existance AS
433 BEGIN
434 IF (((old_references.dt_alias = new_references.dt_alias) AND
435 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
436 (old_references.fee_cal_type = new_references.fee_cal_type) AND
437 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
438 ((new_references.dt_alias IS NULL) OR
439 (new_references.dai_sequence_number IS NULL) OR
440 (new_references.fee_cal_type IS NULL) OR
441 (new_references.fee_ci_sequence_number IS NULL))) THEN
442 NULL;
443 ELSIF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
444 new_references.dt_alias,
445 new_references.dai_sequence_number,
446 new_references.fee_cal_type,
447 new_references.fee_ci_sequence_number
448 ) THEN
449 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END IF;
453 IF (((old_references.fee_cat = new_references.fee_cat) AND
454 (old_references.fee_cal_type = new_references.fee_cal_type) AND
455 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
456 ((new_references.fee_cat IS NULL) OR
457 (new_references.fee_cal_type IS NULL) OR
458 (new_references.fee_ci_sequence_number IS NULL))) THEN
459 NULL;
460 ELSIF NOT IGS_FI_F_CAT_CA_INST_PKG.Get_PK_For_Validation (
461 new_references.fee_cat,
462 new_references.fee_cal_type,
463 new_references.fee_ci_sequence_number
464 ) THEN
465 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
466 IGS_GE_MSG_STACK.ADD;
467 App_Exception.Raise_Exception;
468 END IF ;
469 IF (((old_references.fee_cat = new_references.fee_cat) AND
470 (old_references.fee_cal_type = new_references.fee_cal_type) AND
471 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
472 (old_references.fee_type = new_references.fee_type)) OR
473 ((new_references.fee_cat IS NULL) OR
474 (new_references.fee_cal_type IS NULL) OR
475 (new_references.fee_ci_sequence_number IS NULL) OR
476 (new_references.fee_type IS NULL))) THEN
477 NULL;
478 ELSIF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
479 new_references.fee_cat,
480 new_references.fee_cal_type,
481 new_references.fee_ci_sequence_number,
482 new_references.fee_type
483 ) THEN
484 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 END IF;
488 IF (((old_references.fee_type = new_references.fee_type) AND
489 (old_references.fee_cal_type = new_references.fee_cal_type) AND
490 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
491 ((new_references.fee_type IS NULL) OR
492 (new_references.fee_cal_type IS NULL) OR
493 (new_references.fee_ci_sequence_number IS NULL))) THEN
494 NULL;
495 ELSIF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
496 new_references.fee_type,
497 new_references.fee_cal_type,
498 new_references.fee_ci_sequence_number
499 ) THEN
500 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
501 IGS_GE_MSG_STACK.ADD;
502 App_Exception.Raise_Exception;
503 END IF;
504 END Check_Parent_Existance;
505
506 FUNCTION Get_PK_For_Validation (
507 x_fee_cal_type IN VARCHAR2,
508 x_fee_ci_sequence_number IN NUMBER,
509 x_s_relation_type IN VARCHAR2,
510 x_sequence_number IN NUMBER
511 ) RETURN BOOLEAN AS
512 CURSOR cur_rowid IS
513 SELECT rowid
514 FROM IGS_FI_FEE_RET_SCHD
515 WHERE fee_cal_type = x_fee_cal_type
516 AND fee_ci_sequence_number = x_fee_ci_sequence_number
517 AND s_relation_type = x_s_relation_type
518 AND sequence_number = x_sequence_number
519 FOR UPDATE NOWAIT;
520 lv_rowid cur_rowid%RowType;
521 BEGIN
522 Open cur_rowid;
523 Fetch cur_rowid INTO lv_rowid;
524 IF (cur_rowid%FOUND) THEN
525 Close cur_rowid;
526 Return (TRUE);
527 ELSE
528 Close cur_rowid;
529 Return (FALSE);
530 END IF;
531 END Get_PK_For_Validation;
532
533
534 PROCEDURE GET_FK_IGS_CA_DA_INST (
535 x_dt_alias IN VARCHAR2,
536 x_sequence_number IN NUMBER,
537 x_cal_type IN VARCHAR2,
538 x_ci_sequence_number IN NUMBER
539 ) AS
540 CURSOR cur_rowid IS
541 SELECT rowid
542 FROM IGS_FI_FEE_RET_SCHD
543 WHERE dt_alias = x_dt_alias
544 AND dai_sequence_number = x_sequence_number
545 AND fee_cal_type = x_cal_type
546 AND fee_ci_sequence_number = x_ci_sequence_number ;
547 lv_rowid cur_rowid%RowType;
548 BEGIN
549 Open cur_rowid;
550 Fetch cur_rowid INTO lv_rowid;
551 IF (cur_rowid%FOUND) THEN
552 Close cur_rowid;
553 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FRTNS_DAI_FK');
554 IGS_GE_MSG_STACK.ADD;
555 App_Exception.Raise_Exception;
556 Return;
557 END IF;
558 Close cur_rowid;
559 END GET_FK_IGS_CA_DA_INST;
560 PROCEDURE GET_FK_IGS_FI_F_CAT_CA_INST (
561 x_fee_cat IN VARCHAR2,
562 x_fee_cal_type IN VARCHAR2,
563 x_fee_ci_sequence_number IN NUMBER
564 ) AS
565 CURSOR cur_rowid IS
566 SELECT rowid
567 FROM IGS_FI_FEE_RET_SCHD
568 WHERE fee_cat = x_fee_cat
569 AND fee_cal_type = x_fee_cal_type
570 AND fee_ci_sequence_number = x_fee_ci_sequence_number ;
571 lv_rowid cur_rowid%RowType;
572 BEGIN
573 Open cur_rowid;
574 Fetch cur_rowid INTO lv_rowid;
575 IF (cur_rowid%FOUND) THEN
576 Close cur_rowid;
577 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FRTNS_FCCI_FK');
578 IGS_GE_MSG_STACK.ADD;
579 App_Exception.Raise_Exception;
580 Return;
581 END IF;
582 Close cur_rowid;
583 END GET_FK_IGS_FI_F_CAT_CA_INST;
584
585 PROCEDURE Before_DML (
586 p_action IN VARCHAR2,
587 x_rowid IN VARCHAR2 DEFAULT NULL,
588 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
589 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
590 x_s_relation_type IN VARCHAR2 DEFAULT NULL,
591 x_sequence_number IN NUMBER DEFAULT NULL,
592 x_fee_cat IN VARCHAR2 DEFAULT NULL,
593 x_fee_type IN VARCHAR2 DEFAULT NULL,
594 x_schedule_number IN NUMBER DEFAULT NULL,
595 x_dt_alias IN VARCHAR2 DEFAULT NULL,
596 x_dai_sequence_number IN NUMBER DEFAULT NULL,
597 x_retention_percentage IN NUMBER DEFAULT NULL,
598 x_retention_amount IN NUMBER DEFAULT NULL,
599 x_creation_date IN DATE DEFAULT NULL,
600 x_created_by IN NUMBER DEFAULT NULL,
601 x_last_update_date IN DATE DEFAULT NULL,
602 x_last_updated_by IN NUMBER DEFAULT NULL,
603 x_last_update_login IN NUMBER DEFAULT NULL
604 ) AS
605 /*----------------------------------------------------------------------------
606 || Created By :
607 || Created On :
608 || Purpose :
609 || Known limitations, enhancements or remarks :
610 || Change History :
611 || Who When What
612 || (reverse chronological order - newest change first)
613 || shtatiko 13-MAR-2003 Bug# 2473845, Added statement l_rowid := null; because, this
614 || is creating problems when get_uk_for_validation is directly
615 || from pld.
616 || vvutukur 24-Jul-2002 Bug#2425767.Removed references of deduction_amount(from call to
617 || set_column_values procedure).
618 ----------------------------------------------------------------------------*/
619 BEGIN
620 Set_Column_Values (
621 p_action,
622 x_rowid,
623 x_fee_cal_type,
624 x_fee_ci_sequence_number,
625 x_s_relation_type,
626 x_sequence_number,
627 x_fee_cat,
628 x_fee_type,
629 x_schedule_number,
630 x_dt_alias,
631 x_dai_sequence_number,
632 x_retention_percentage,
633 x_retention_amount,
634 x_creation_date,
635 x_created_by,
636 x_last_update_date,
637 x_last_updated_by,
638 x_last_update_login
639 );
640 IF (p_action = 'INSERT') THEN
641 -- Call all the procedures related to Before Insert.
642 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
643 IF Get_PK_For_Validation (
644 new_references.fee_cal_type,
645 new_references.fee_ci_sequence_number,
646 new_references.s_relation_type,
647 new_references.sequence_number ) THEN
648 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
649 IGS_GE_MSG_STACK.ADD;
650 App_Exception.Raise_Exception;
651 END IF;
652 Check_Uniqueness;
653 Check_Constraints;
654 Check_Parent_Existance;
655 ELSIF (p_action = 'UPDATE') THEN
656 -- Call all the procedures related to Before Update.
657 BeforeRowInsertUpdate1 ( p_updating => TRUE );
658 Check_Uniqueness;
659 Check_Constraints;
660 Check_Parent_Existance;
661 ELSIF (p_action = 'VALIDATE_INSERT') THEN
662 -- Call all the procedures related to Before Insert.
663 IF Get_PK_For_Validation (
664 new_references.fee_cal_type,
665 new_references.fee_ci_sequence_number,
666 new_references.s_relation_type,
667 new_references.sequence_number ) THEN
668 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
669 IGS_GE_MSG_STACK.ADD;
670 App_Exception.Raise_Exception;
671 END IF;
672 Check_Uniqueness;
673 Check_Constraints;
674 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
675 Check_Uniqueness;
676 Check_Constraints;
677 END IF;
678 l_rowid := NULL;
679 END Before_DML;
680
681 PROCEDURE after_dml (
682 p_action IN VARCHAR2,
683 x_rowid IN VARCHAR2
684 ) AS
685 /*----------------------------------------------------------------------------
686 || Created By :
687 || Created On :
688 || Purpose :
689 || Known limitations, enhancements or remarks :
690 || Change History :
691 || Who When What
692 || (reverse chronological order - newest change first)
693 || smadathi 18-FEB-2003 Bug 2473845.Added logic to re initialize l_rowid to null.
694 || shtatiko 07-FEB-2003 Removed the statement l_rowid := x_rowid; as
695 || l_rowid is not at all used after assignment.
696 || And this assignment is causing the problems
697 || when get_uk is called directly from the pld.
698 ----------------------------------------------------------------------------*/
699 BEGIN
700 IF (p_action = 'INSERT') THEN
701 -- Call all the procedures related to After Insert.
702 AfterStmtInsertUpdate4 ( p_inserting => TRUE );
703 ELSIF (p_action = 'UPDATE') THEN
704 -- Call all the procedures related to After Update.
705 AfterRowUpdate3 ( p_updating => TRUE );
706 AfterStmtInsertUpdate4 ( p_updating => TRUE );
707 END IF;
708 l_rowid := NULL;
709 END after_dml;
710
711 procedure INSERT_ROW (
712 X_ROWID in out NOCOPY VARCHAR2,
713 X_FEE_CAL_TYPE in VARCHAR2,
714 X_SEQUENCE_NUMBER in NUMBER,
715 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
716 X_S_RELATION_TYPE in VARCHAR2,
717 X_FEE_CAT in VARCHAR2,
718 X_FEE_TYPE in VARCHAR2,
719 X_SCHEDULE_NUMBER in NUMBER,
720 X_DT_ALIAS in VARCHAR2,
721 X_DAI_SEQUENCE_NUMBER in NUMBER,
722 X_RETENTION_PERCENTAGE in NUMBER,
723 X_RETENTION_AMOUNT in NUMBER,
724 X_MODE in VARCHAR2 default 'R'
725 ) AS
726 /*----------------------------------------------------------------------------
727 || Created By :
728 || Created On :
729 || Purpose :
730 || Known limitations, enhancements or remarks :
731 || Change History :
732 || Who When What
733 || (reverse chronological order - newest change first)
734 || vvutukur 24-Jul-2002 Bug#2425767.Removed references to deduction_amount(from call to
735 || before_dml and from insert statement).
736 ----------------------------------------------------------------------------*/
737 cursor C is select ROWID from IGS_FI_FEE_RET_SCHD
738 where FEE_CAL_TYPE = X_FEE_CAL_TYPE
739 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
740 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
741 and S_RELATION_TYPE = X_S_RELATION_TYPE;
742 X_LAST_UPDATE_DATE DATE;
743 X_LAST_UPDATED_BY NUMBER;
744 X_LAST_UPDATE_LOGIN NUMBER;
745 X_REQUEST_ID NUMBER;
746 X_PROGRAM_ID NUMBER;
747 X_PROGRAM_APPLICATION_ID NUMBER;
748 X_PROGRAM_UPDATE_DATE DATE;
749 begin
750 X_LAST_UPDATE_DATE := SYSDATE;
751 if(X_MODE = 'I') then
752 X_LAST_UPDATED_BY := 1;
753 X_LAST_UPDATE_LOGIN := 0;
754 elsif (X_MODE = 'R') then
755 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
756 if X_LAST_UPDATED_BY is NULL then
757 X_LAST_UPDATED_BY := -1;
758 end if;
759 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
760 if X_LAST_UPDATE_LOGIN is NULL then
761 X_LAST_UPDATE_LOGIN := -1;
762 end if;
763 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
764 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
765 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
766 if (X_REQUEST_ID = -1) then
767 X_REQUEST_ID := NULL;
768 X_PROGRAM_ID := NULL;
769 X_PROGRAM_APPLICATION_ID := NULL;
770 X_PROGRAM_UPDATE_DATE := NULL;
771 else
772 X_PROGRAM_UPDATE_DATE := SYSDATE;
773 end if;
774 else
775 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
776 IGS_GE_MSG_STACK.ADD;
777 app_exception.raise_exception;
778 end if;
779 Before_DML(
780 p_action=>'INSERT',
781 x_rowid=>X_ROWID,
782 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
783 x_dt_alias=>X_DT_ALIAS,
784 x_fee_cal_type=>X_FEE_CAL_TYPE,
785 x_fee_cat=>X_FEE_CAT,
786 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
787 x_fee_type=>X_FEE_TYPE,
788 x_retention_amount=>X_RETENTION_AMOUNT,
789 x_retention_percentage=>X_RETENTION_PERCENTAGE,
790 x_s_relation_type=>X_S_RELATION_TYPE,
791 x_schedule_number=>X_SCHEDULE_NUMBER,
792 x_sequence_number=>X_SEQUENCE_NUMBER,
793 x_creation_date=>X_LAST_UPDATE_DATE,
794 x_created_by=>X_LAST_UPDATED_BY,
795 x_last_update_date=>X_LAST_UPDATE_DATE,
796 x_last_updated_by=>X_LAST_UPDATED_BY,
797 x_last_update_login=>X_LAST_UPDATE_LOGIN
798 );
799 insert into IGS_FI_FEE_RET_SCHD (
800 FEE_CAL_TYPE,
801 FEE_CI_SEQUENCE_NUMBER,
802 S_RELATION_TYPE,
803 SEQUENCE_NUMBER,
804 FEE_CAT,
805 FEE_TYPE,
806 SCHEDULE_NUMBER,
807 DT_ALIAS,
808 DAI_SEQUENCE_NUMBER,
809 RETENTION_PERCENTAGE,
810 RETENTION_AMOUNT,
811 CREATION_DATE,
812 CREATED_BY,
813 LAST_UPDATE_DATE,
814 LAST_UPDATED_BY,
815 LAST_UPDATE_LOGIN,
816 REQUEST_ID,
817 PROGRAM_ID,
818 PROGRAM_APPLICATION_ID,
819 PROGRAM_UPDATE_DATE
820 ) values (
821 NEW_REFERENCES.FEE_CAL_TYPE,
822 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
823 NEW_REFERENCES.S_RELATION_TYPE,
824 NEW_REFERENCES.SEQUENCE_NUMBER,
825 NEW_REFERENCES.FEE_CAT,
826 NEW_REFERENCES.FEE_TYPE,
827 NEW_REFERENCES.SCHEDULE_NUMBER,
828 NEW_REFERENCES.DT_ALIAS,
829 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
830 NEW_REFERENCES.RETENTION_PERCENTAGE,
831 NEW_REFERENCES.RETENTION_AMOUNT,
832 X_LAST_UPDATE_DATE,
833 X_LAST_UPDATED_BY,
834 X_LAST_UPDATE_DATE,
835 X_LAST_UPDATED_BY,
836 X_LAST_UPDATE_LOGIN,
837 X_REQUEST_ID,
838 X_PROGRAM_ID,
839 X_PROGRAM_APPLICATION_ID,
840 X_PROGRAM_UPDATE_DATE
841 );
842 open c;
843 fetch c into X_ROWID;
844 if (c%notfound) then
845 close c;
846 raise no_data_found;
847 end if;
848 close c;
849 After_DML (
850 p_action => 'INSERT',
851 x_rowid => X_ROWID
852 );
853 end INSERT_ROW;
854 procedure LOCK_ROW (
855 X_ROWID in VARCHAR2,
856 X_FEE_CAL_TYPE in VARCHAR2,
857 X_SEQUENCE_NUMBER in NUMBER,
858 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
859 X_S_RELATION_TYPE in VARCHAR2,
860 X_FEE_CAT in VARCHAR2,
861 X_FEE_TYPE in VARCHAR2,
862 X_SCHEDULE_NUMBER in NUMBER,
863 X_DT_ALIAS in VARCHAR2,
864 X_DAI_SEQUENCE_NUMBER in NUMBER,
865 X_RETENTION_PERCENTAGE in NUMBER,
866 X_RETENTION_AMOUNT in NUMBER
867 ) AS
868 /*----------------------------------------------------------------------------
869 || Created By :
870 || Created On :
871 || Purpose :
872 || Known limitations, enhancements or remarks :
873 || Change History :
874 || Who When What
875 || (reverse chronological order - newest change first)
876 || vvutukur 24-Jul-2002 Bug#2425767.Removed references to deduction_amount(from cursor c1 and
877 || from if condition).
878 ----------------------------------------------------------------------------*/
879 cursor c1 is select
880 FEE_CAT,
881 FEE_TYPE,
882 SCHEDULE_NUMBER,
883 DT_ALIAS,
884 DAI_SEQUENCE_NUMBER,
885 RETENTION_PERCENTAGE,
886 RETENTION_AMOUNT
887 from IGS_FI_FEE_RET_SCHD
888 where ROWID = X_ROWID
889 for update nowait;
890 tlinfo c1%rowtype;
891 begin
892 open c1;
893 fetch c1 into tlinfo;
894 if (c1%notfound) then
895 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
896 IGS_GE_MSG_STACK.ADD;
897 app_exception.raise_exception;
898 close c1;
899 return;
900 end if;
901 close c1;
902 if ( ((tlinfo.FEE_CAT = X_FEE_CAT)
903 OR ((tlinfo.FEE_CAT is null)
904 AND (X_FEE_CAT is null)))
905 AND ((tlinfo.FEE_TYPE = X_FEE_TYPE)
906 OR ((tlinfo.FEE_TYPE is null)
907 AND (X_FEE_TYPE is null)))
908 AND (tlinfo.SCHEDULE_NUMBER = X_SCHEDULE_NUMBER)
909 AND (tlinfo.DT_ALIAS = X_DT_ALIAS)
910 AND (tlinfo.DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER)
911 AND ((tlinfo.RETENTION_PERCENTAGE = X_RETENTION_PERCENTAGE)
912 OR ((tlinfo.RETENTION_PERCENTAGE is null)
913 AND (X_RETENTION_PERCENTAGE is null)))
914 AND ((tlinfo.RETENTION_AMOUNT = X_RETENTION_AMOUNT)
915 OR ((tlinfo.RETENTION_AMOUNT is null)
916 AND (X_RETENTION_AMOUNT is null)))
917 ) then
918 null;
919 else
920 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
921 IGS_GE_MSG_STACK.ADD;
922 app_exception.raise_exception;
923 end if;
924 return;
925 end LOCK_ROW;
926 procedure UPDATE_ROW (
927 X_ROWID in VARCHAR2,
928 X_FEE_CAL_TYPE in VARCHAR2,
929 X_SEQUENCE_NUMBER in NUMBER,
930 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
931 X_S_RELATION_TYPE in VARCHAR2,
932 X_FEE_CAT in VARCHAR2,
933 X_FEE_TYPE in VARCHAR2,
934 X_SCHEDULE_NUMBER in NUMBER,
935 X_DT_ALIAS in VARCHAR2,
936 X_DAI_SEQUENCE_NUMBER in NUMBER,
937 X_RETENTION_PERCENTAGE in NUMBER,
938 X_RETENTION_AMOUNT in NUMBER,
939 X_MODE in VARCHAR2 default 'R'
940 ) AS
941 /*----------------------------------------------------------------------------
942 || Created By :
943 || Created On :
944 || Purpose :
945 || Known limitations, enhancements or remarks :
946 || Change History :
947 || Who When What
948 || (reverse chronological order - newest change first)
949 || vvutukur 24-Jul-2002 Bug#2425767.Removed references to deduction_amount(from call to
950 || before_dml and from update statement).
951 ----------------------------------------------------------------------------*/
952 X_LAST_UPDATE_DATE DATE;
953 X_LAST_UPDATED_BY NUMBER;
954 X_LAST_UPDATE_LOGIN NUMBER;
955 X_REQUEST_ID NUMBER;
956 X_PROGRAM_ID NUMBER;
957 X_PROGRAM_APPLICATION_ID NUMBER;
958 X_PROGRAM_UPDATE_DATE DATE;
959 begin
960 X_LAST_UPDATE_DATE := SYSDATE;
961 if(X_MODE = 'I') then
962 X_LAST_UPDATED_BY := 1;
963 X_LAST_UPDATE_LOGIN := 0;
964 elsif (X_MODE = 'R') then
965 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
966 if X_LAST_UPDATED_BY is NULL then
967 X_LAST_UPDATED_BY := -1;
968 end if;
969 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
970 if X_LAST_UPDATE_LOGIN is NULL then
971 X_LAST_UPDATE_LOGIN := -1;
972 end if;
973 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
974 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
975 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
976 if (X_REQUEST_ID = -1) then
977 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
978 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
979 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
980 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
981 else
982 X_PROGRAM_UPDATE_DATE := SYSDATE;
983 end if;
984 else
985 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
986 IGS_GE_MSG_STACK.ADD;
987 app_exception.raise_exception;
988 end if;
989 Before_DML(
990 p_action=>'UPDATE',
991 x_rowid=>X_ROWID,
992 x_dai_sequence_number=>X_DAI_SEQUENCE_NUMBER,
993 x_dt_alias=>X_DT_ALIAS,
994 x_fee_cal_type=>X_FEE_CAL_TYPE,
995 x_fee_cat=>X_FEE_CAT,
996 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
997 x_fee_type=>X_FEE_TYPE,
998 x_retention_amount=>X_RETENTION_AMOUNT,
999 x_retention_percentage=>X_RETENTION_PERCENTAGE,
1000 x_s_relation_type=>X_S_RELATION_TYPE,
1001 x_schedule_number=>X_SCHEDULE_NUMBER,
1002 x_sequence_number=>X_SEQUENCE_NUMBER,
1003 x_creation_date=>X_LAST_UPDATE_DATE,
1004 x_created_by=>X_LAST_UPDATED_BY,
1005 x_last_update_date=>X_LAST_UPDATE_DATE,
1006 x_last_updated_by=>X_LAST_UPDATED_BY,
1007 x_last_update_login=>X_LAST_UPDATE_LOGIN
1008 );
1009 update IGS_FI_FEE_RET_SCHD set
1010 FEE_CAT = NEW_REFERENCES.FEE_CAT,
1011 FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
1012 SCHEDULE_NUMBER = NEW_REFERENCES.SCHEDULE_NUMBER,
1013 DT_ALIAS = NEW_REFERENCES.DT_ALIAS,
1014 DAI_SEQUENCE_NUMBER = NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
1015 RETENTION_PERCENTAGE = NEW_REFERENCES.RETENTION_PERCENTAGE,
1016 RETENTION_AMOUNT = NEW_REFERENCES.RETENTION_AMOUNT,
1017 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1018 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1019 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1020 REQUEST_ID = X_REQUEST_ID,
1021 PROGRAM_ID = X_PROGRAM_ID,
1022 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1023 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1024 where ROWID = X_ROWID;
1025 if (sql%notfound) then
1026 raise no_data_found;
1027 end if;
1028 After_DML (
1029 p_action => 'UPDATE',
1030 x_rowid => X_ROWID
1031 );
1032 end UPDATE_ROW;
1033 procedure ADD_ROW (
1034 X_ROWID in out NOCOPY VARCHAR2,
1035 X_FEE_CAL_TYPE in VARCHAR2,
1036 X_SEQUENCE_NUMBER in NUMBER,
1037 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1038 X_S_RELATION_TYPE in VARCHAR2,
1039 X_FEE_CAT in VARCHAR2,
1040 X_FEE_TYPE in VARCHAR2,
1041 X_SCHEDULE_NUMBER in NUMBER,
1042 X_DT_ALIAS in VARCHAR2,
1043 X_DAI_SEQUENCE_NUMBER in NUMBER,
1044 X_RETENTION_PERCENTAGE in NUMBER,
1045 X_RETENTION_AMOUNT in NUMBER,
1046 X_MODE in VARCHAR2 default 'R'
1047 ) AS
1048 /*----------------------------------------------------------------------------
1049 || Created By :
1050 || Created On :
1051 || Purpose :
1052 || Known limitations, enhancements or remarks :
1053 || Change History :
1054 || Who When What
1055 || (reverse chronological order - newest change first)
1056 || vvutukur 24-Jul-2002 Bug#2425767.Removed references to deduction_amount(from calls to
1057 || insert_row and update_row).
1058 ----------------------------------------------------------------------------*/
1059 cursor c1 is select rowid from IGS_FI_FEE_RET_SCHD
1060 where FEE_CAL_TYPE = X_FEE_CAL_TYPE
1061 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1062 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
1063 and S_RELATION_TYPE = X_S_RELATION_TYPE
1064 ;
1065 begin
1066 open c1;
1067 fetch c1 into X_ROWID;
1068 if (c1%notfound) then
1069 close c1;
1070 INSERT_ROW (
1071 X_ROWID,
1072 X_FEE_CAL_TYPE,
1073 X_SEQUENCE_NUMBER,
1074 X_FEE_CI_SEQUENCE_NUMBER,
1075 X_S_RELATION_TYPE,
1076 X_FEE_CAT,
1077 X_FEE_TYPE,
1078 X_SCHEDULE_NUMBER,
1079 X_DT_ALIAS,
1080 X_DAI_SEQUENCE_NUMBER,
1081 X_RETENTION_PERCENTAGE,
1082 X_RETENTION_AMOUNT,
1083 X_MODE);
1084 return;
1085 end if;
1086 close c1;
1087 UPDATE_ROW (
1088 X_ROWID,
1089 X_FEE_CAL_TYPE,
1090 X_SEQUENCE_NUMBER,
1091 X_FEE_CI_SEQUENCE_NUMBER,
1092 X_S_RELATION_TYPE,
1093 X_FEE_CAT,
1094 X_FEE_TYPE,
1095 X_SCHEDULE_NUMBER,
1096 X_DT_ALIAS,
1097 X_DAI_SEQUENCE_NUMBER,
1098 X_RETENTION_PERCENTAGE,
1099 X_RETENTION_AMOUNT,
1100 X_MODE);
1101 end ADD_ROW;
1102 procedure DELETE_ROW (
1103 X_ROWID in VARCHAR2
1104 ) AS
1105 begin
1106 Before_DML (
1107 p_action => 'DELETE',
1108 x_rowid => X_ROWID
1109 );
1110 delete from IGS_FI_FEE_RET_SCHD
1111 where ROWID = X_ROWID;
1112 if (sql%notfound) then
1113 raise no_data_found;
1114 end if;
1115 end DELETE_ROW;
1116 end IGS_FI_FEE_RET_SCHD_PKG;
[Home] [Help]