1 PACKAGE BODY igs_fi_anc_rates_pkg AS
2 /* $Header: IGSSI82B.pls 115.6 2003/02/12 09:35:55 pathipat ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_anc_rates%ROWTYPE;
6 new_references igs_fi_anc_rates%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_ancillary_rate_id IN NUMBER DEFAULT NULL,
12 x_fee_type IN VARCHAR2 DEFAULT NULL,
13 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_ancillary_attribute1 IN VARCHAR2 DEFAULT NULL,
16 x_ancillary_attribute2 IN VARCHAR2 DEFAULT NULL,
17 x_ancillary_attribute3 IN VARCHAR2 DEFAULT NULL,
18 x_ancillary_attribute4 IN VARCHAR2 DEFAULT NULL,
19 x_ancillary_attribute5 IN VARCHAR2 DEFAULT NULL,
20 x_ancillary_attribute6 IN VARCHAR2 DEFAULT NULL,
21 x_ancillary_attribute7 IN VARCHAR2 DEFAULT NULL,
22 x_ancillary_attribute8 IN VARCHAR2 DEFAULT NULL,
23 x_ancillary_attribute9 IN VARCHAR2 DEFAULT NULL,
24 x_ancillary_attribute10 IN VARCHAR2 DEFAULT NULL,
25 x_ancillary_attribute11 IN VARCHAR2 DEFAULT NULL,
26 x_ancillary_attribute12 IN VARCHAR2 DEFAULT NULL,
27 x_ancillary_attribute13 IN VARCHAR2 DEFAULT NULL,
28 x_ancillary_attribute14 IN VARCHAR2 DEFAULT NULL,
29 x_ancillary_attribute15 IN VARCHAR2 DEFAULT NULL,
30 x_ancillary_chg_rate IN NUMBER DEFAULT NULL,
31 x_enabled_flag IN VARCHAR2 DEFAULT NULL,
32 x_creation_date IN DATE DEFAULT NULL,
33 x_created_by IN NUMBER DEFAULT NULL,
34 x_last_update_date IN DATE DEFAULT NULL,
35 x_last_updated_by IN NUMBER DEFAULT NULL,
36 x_last_update_login IN NUMBER DEFAULT NULL
37 ) AS
38 /*
39 || Created By : [email protected]
40 || Created On : 09-APR-2001
41 || Purpose : Initialises the Old and New references for the columns of the table.
42 || Known limitations, enhancements or remarks :
43 || Change History :
44 || Who When What
45 || (reverse chronological order - newest change first)
46 */
47
48 CURSOR cur_old_ref_values IS
49 SELECT *
50 FROM IGS_FI_ANC_RATES
51 WHERE rowid = x_rowid;
52
53 BEGIN
54
55 l_rowid := x_rowid;
56
57 -- Code for setting the Old and New Reference Values.
58 -- Populate Old Values.
59 OPEN cur_old_ref_values;
60 FETCH cur_old_ref_values INTO old_references;
61 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
62 CLOSE cur_old_ref_values;
63 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
64 igs_ge_msg_stack.add;
65 app_exception.raise_exception;
66 RETURN;
67 END IF;
68 CLOSE cur_old_ref_values;
69
70 -- Populate New Values.
71 new_references.ancillary_rate_id := x_ancillary_rate_id;
72 new_references.fee_type := x_fee_type;
73 new_references.fee_cal_type := x_fee_cal_type;
74 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
75 new_references.ancillary_attribute1 := x_ancillary_attribute1;
76 new_references.ancillary_attribute2 := x_ancillary_attribute2;
77 new_references.ancillary_attribute3 := x_ancillary_attribute3;
78 new_references.ancillary_attribute4 := x_ancillary_attribute4;
79 new_references.ancillary_attribute5 := x_ancillary_attribute5;
80 new_references.ancillary_attribute6 := x_ancillary_attribute6;
81 new_references.ancillary_attribute7 := x_ancillary_attribute7;
82 new_references.ancillary_attribute8 := x_ancillary_attribute8;
83 new_references.ancillary_attribute9 := x_ancillary_attribute9;
84 new_references.ancillary_attribute10 := x_ancillary_attribute10;
85 new_references.ancillary_attribute11 := x_ancillary_attribute11;
86 new_references.ancillary_attribute12 := x_ancillary_attribute12;
87 new_references.ancillary_attribute13 := x_ancillary_attribute13;
88 new_references.ancillary_attribute14 := x_ancillary_attribute14;
89 new_references.ancillary_attribute15 := x_ancillary_attribute15;
90 new_references.ancillary_chg_rate := x_ancillary_chg_rate;
91 new_references.enabled_flag := x_enabled_flag;
92
93 IF (p_action = 'UPDATE') THEN
94 new_references.creation_date := old_references.creation_date;
95 new_references.created_by := old_references.created_by;
96 ELSE
97 new_references.creation_date := x_creation_date;
98 new_references.created_by := x_created_by;
99 END IF;
100
101 new_references.last_update_date := x_last_update_date;
102 new_references.last_updated_by := x_last_updated_by;
103 new_references.last_update_login := x_last_update_login;
104
105 END set_column_values;
106
107 PROCEDURE BeforeRowUpdateDelete(p_inserting BOOLEAN DEFAULT FALSE,
108 p_updating BOOLEAN DEFAULT FALSE,
109 p_deleting BOOLEAN DEFAULT FALSE) AS
110
111 /*
112 || Created By : SYKRISHN
113 || Created On : 20-MAY-2002
114 || Purpose : Checks if The rate record is used - Prevents deletion and updation
115 || | Added BeforeRowUpdateDelete as part of bug 2378893
116 || Known limitations, enhancements or remarks :
117 || Change History :
118 || Who When What
119 || (reverse chronological order - newest change first)
120 */
121
122 CURSOR cur_impchgs_lines ( cp_anc_att1 IGS_FI_ANC_RATES.ancillary_attribute1%TYPE, cp_anc_att2 IGS_FI_ANC_RATES.ancillary_attribute2%TYPE,
123 cp_anc_att3 IGS_FI_ANC_RATES.ancillary_attribute3%TYPE, cp_anc_att4 IGS_FI_ANC_RATES.ancillary_attribute4%TYPE,
124 cp_anc_att5 IGS_FI_ANC_RATES.ancillary_attribute5%TYPE, cp_anc_att6 IGS_FI_ANC_RATES.ancillary_attribute6%TYPE,
125 cp_anc_att7 IGS_FI_ANC_RATES.ancillary_attribute7%TYPE, cp_anc_att8 IGS_FI_ANC_RATES.ancillary_attribute8%TYPE,
126 cp_anc_att9 IGS_FI_ANC_RATES.ancillary_attribute9%TYPE, cp_anc_att10 IGS_FI_ANC_RATES.ancillary_attribute10%TYPE,
127 cp_anc_att11 IGS_FI_ANC_RATES.ancillary_attribute11%TYPE,cp_anc_att12 IGS_FI_ANC_RATES.ancillary_attribute12%TYPE,
128 cp_anc_att13 IGS_FI_ANC_RATES.ancillary_attribute13%TYPE,cp_anc_att14 IGS_FI_ANC_RATES.ancillary_attribute14%TYPE,
129 cp_anc_att15 IGS_FI_ANC_RATES.ancillary_attribute15%TYPE,cp_fee_type IGS_FI_F_TYP_CA_INST_LKP_V.fee_type%TYPE,
130 cp_sequence_number IGS_FI_F_TYP_CA_INST_LKP_V.fee_ci_sequence_number%TYPE,
131 cp_fee_cal_type IGS_FI_F_TYP_CA_INST_LKP_V.fee_cal_type%TYPE,
132 cp_anc_chg_rate IGS_FI_ANC_RATES.ancillary_chg_rate%TYPE) IS
133
134 SELECT 'x' FROM igs_fi_impchgs_lines i
135 WHERE (ancillary_attribute1 = cp_anc_att1 OR ( ancillary_attribute1 IS NULL AND cp_anc_att1 IS NULL)) AND
136 (ancillary_attribute2 = cp_anc_att2 OR ( ancillary_attribute2 IS NULL AND cp_anc_att2 IS NULL)) AND
137 (ancillary_attribute3 = cp_anc_att3 OR ( ancillary_attribute3 IS NULL AND cp_anc_att3 IS NULL)) AND
138 (ancillary_attribute4 = cp_anc_att4 OR ( ancillary_attribute4 IS NULL AND cp_anc_att4 IS NULL)) AND
139 (ancillary_attribute5 = cp_anc_att5 OR ( ancillary_attribute5 IS NULL AND cp_anc_att5 IS NULL)) AND
140 (ancillary_attribute6 = cp_anc_att6 OR ( ancillary_attribute6 IS NULL AND cp_anc_att6 IS NULL)) AND
141 (ancillary_attribute7 = cp_anc_att7 OR ( ancillary_attribute7 IS NULL AND cp_anc_att7 IS NULL)) AND
142 (ancillary_attribute8 = cp_anc_att8 OR ( ancillary_attribute8 IS NULL AND cp_anc_att8 IS NULL)) AND
143 (ancillary_attribute9 = cp_anc_att9 OR ( ancillary_attribute9 IS NULL AND cp_anc_att9 IS NULL)) AND
144 (ancillary_attribute10 = cp_anc_att10 OR ( ancillary_attribute10 IS NULL AND cp_anc_att10 IS NULL)) AND
145 (ancillary_attribute11 = cp_anc_att11 OR ( ancillary_attribute11 IS NULL AND cp_anc_att11 IS NULL)) AND
146 (ancillary_attribute12 = cp_anc_att12 OR ( ancillary_attribute12 IS NULL AND cp_anc_att12 IS NULL)) AND
147 (ancillary_attribute13 = cp_anc_att13 OR ( ancillary_attribute13 IS NULL AND cp_anc_att13 IS NULL)) AND
148 (ancillary_attribute14 = cp_anc_att14 OR ( ancillary_attribute14 IS NULL AND cp_anc_att14 IS NULL)) AND
149 (ancillary_attribute15 = cp_anc_att15 OR ( ancillary_attribute15 IS NULL AND cp_anc_att15 IS NULL)) AND
150 (transaction_amount = cp_anc_chg_rate)
151 AND
152 EXISTS
153 ( SELECT 'x' from igs_fi_imp_chgs c
154 WHERE fee_type = cp_fee_type AND
155 fee_ci_sequence_number = cp_sequence_number AND
156 fee_cal_type = cp_fee_cal_type AND
157 transaction_type = 'ANCILLARY' AND
158 c.import_charges_id = i.import_charges_id
159 );
160
161 l_bool BOOLEAN DEFAULT FALSE;
162 l_var VARCHAR2(1);
163 BEGIN
164 l_bool := FALSE;
165 IF p_updating THEN
166 IF ((new_references.ancillary_attribute1 = old_references.ancillary_attribute1) OR
167 (new_references.ancillary_attribute1 IS NULL AND old_references.ancillary_attribute1 IS NULL)) AND
168 ((new_references.ancillary_attribute2 = old_references.ancillary_attribute2) OR
169 (new_references.ancillary_attribute2 IS NULL AND old_references.ancillary_attribute2 IS NULL)) AND
170 ((new_references.ancillary_attribute3 = old_references.ancillary_attribute3) OR
171 (new_references.ancillary_attribute3 IS NULL AND old_references.ancillary_attribute3 IS NULL)) AND
172 ((new_references.ancillary_attribute4 = old_references.ancillary_attribute4) OR
173 (new_references.ancillary_attribute4 IS NULL AND old_references.ancillary_attribute4 IS NULL)) AND
174 ((new_references.ancillary_attribute5 = old_references.ancillary_attribute5) OR
178 ((new_references.ancillary_attribute7 = old_references.ancillary_attribute7) OR
175 (new_references.ancillary_attribute5 IS NULL AND old_references.ancillary_attribute5 IS NULL)) AND
176 ((new_references.ancillary_attribute6 = old_references.ancillary_attribute6) OR
177 (new_references.ancillary_attribute6 IS NULL AND old_references.ancillary_attribute6 IS NULL)) AND
179 (new_references.ancillary_attribute7 IS NULL AND old_references.ancillary_attribute7 IS NULL)) AND
180 ((new_references.ancillary_attribute8 = old_references.ancillary_attribute8) OR
181 (new_references.ancillary_attribute8 IS NULL AND old_references.ancillary_attribute8 IS NULL)) AND
182 ((new_references.ancillary_attribute9 = old_references.ancillary_attribute9) OR
183 (new_references.ancillary_attribute9 IS NULL AND old_references.ancillary_attribute9 IS NULL)) AND
184 ((new_references.ancillary_attribute10 = old_references.ancillary_attribute10) OR
185 (new_references.ancillary_attribute10 IS NULL AND old_references.ancillary_attribute10 IS NULL)) AND
186 ((new_references.ancillary_attribute11 = old_references.ancillary_attribute11) OR
187 (new_references.ancillary_attribute11 IS NULL AND old_references.ancillary_attribute11 IS NULL)) AND
188 ((new_references.ancillary_attribute12 = old_references.ancillary_attribute12) OR
189 (new_references.ancillary_attribute12 IS NULL AND old_references.ancillary_attribute12 IS NULL)) AND
190 ((new_references.ancillary_attribute13 = old_references.ancillary_attribute13) OR
191 (new_references.ancillary_attribute13 IS NULL AND old_references.ancillary_attribute13 IS NULL)) AND
192 ((new_references.ancillary_attribute14 = old_references.ancillary_attribute14) OR
193 (new_references.ancillary_attribute14 IS NULL AND old_references.ancillary_attribute14 IS NULL)) AND
194 ((new_references.ancillary_attribute15 = old_references.ancillary_attribute15) OR
195 (new_references.ancillary_attribute15 IS NULL AND old_references.ancillary_attribute15 IS NULL)) AND
196 (new_references.ancillary_chg_rate = old_references.ancillary_chg_rate) THEN
197 l_bool := TRUE;
198 END IF;
199
200 OPEN cur_impchgs_lines(old_references.ancillary_attribute1,
201 old_references.ancillary_attribute2,
202 old_references.ancillary_attribute3,
203 old_references.ancillary_attribute4,
204 old_references.ancillary_attribute5,
205 old_references.ancillary_attribute6,
206 old_references.ancillary_attribute7,
207 old_references.ancillary_attribute8,
211 old_references.ancillary_attribute12,
208 old_references.ancillary_attribute9,
209 old_references.ancillary_attribute10,
210 old_references.ancillary_attribute11,
212 old_references.ancillary_attribute13,
213 old_references.ancillary_attribute14,
214 old_references.ancillary_attribute15,
215 old_references.fee_type,
216 old_references.fee_ci_sequence_number,
217 old_references.fee_cal_type,
218 old_references.ancillary_chg_rate
219 );
220 FETCH cur_impchgs_lines INTO l_var;
221 IF cur_impchgs_lines%FOUND THEN
222 IF NOT l_bool THEN
223 CLOSE cur_impchgs_lines;
224 FND_MESSAGE.SET_NAME('IGS','IGS_FI_IMPCHGS_LINES_EXISTS');
225 --'Update of this record is not allowed since Ancillary Charges exist for these attributes and rate'
226 IGS_GE_MSG_STACK.ADD;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228 END IF;
229 END IF;
230 CLOSE cur_impchgs_lines;
231
232 ELSIF p_deleting THEN
233
234 OPEN cur_impchgs_lines(old_references.ancillary_attribute1,
235 old_references.ancillary_attribute2,
236 old_references.ancillary_attribute3,
237 old_references.ancillary_attribute4,
238 old_references.ancillary_attribute5,
239 old_references.ancillary_attribute6,
240 old_references.ancillary_attribute7,
241 old_references.ancillary_attribute8,
242 old_references.ancillary_attribute9,
243 old_references.ancillary_attribute10,
244 old_references.ancillary_attribute11,
245 old_references.ancillary_attribute12,
246 old_references.ancillary_attribute13,
247 old_references.ancillary_attribute14,
248 old_references.ancillary_attribute15,
249 old_references.fee_type,
250 old_references.fee_ci_sequence_number,
251 old_references.fee_cal_type ,
252 old_references.ancillary_chg_rate
253 );
254 FETCH cur_impchgs_lines INTO l_var;
255 IF cur_impchgs_lines%FOUND THEN
256 CLOSE cur_impchgs_lines;
257 FND_MESSAGE.SET_NAME('IGS','IGS_FI_IMP_CHGS_EXISTS');
258 --'Deletion of this record is not allowed since Ancillary Charges exist for these attributes'
259 IGS_GE_MSG_STACK.ADD;
260 APP_EXCEPTION.RAISE_EXCEPTION;
261 END IF;
262 CLOSE cur_impchgs_lines;
263 END IF;
264
265 END BeforeRowUpdateDelete;
266
267 PROCEDURE check_uniqueness AS
268 /*
269 || Created By : [email protected]
270 || Created On : 09-APR-2001
271 || Purpose : Handles the Unique Constraint logic defined for the columns.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 BEGIN
278
279 IF ( get_uk_for_validation (
280 new_references.fee_cal_type,
281 new_references.fee_ci_sequence_number,
282 new_references.fee_type,
283 new_references.ancillary_attribute1,
284 new_references.ancillary_attribute2,
285 new_references.ancillary_attribute3,
286 new_references.ancillary_attribute4,
287 new_references.ancillary_attribute5,
288 new_references.ancillary_attribute6,
289 new_references.ancillary_attribute7,
290 new_references.ancillary_attribute8,
291 new_references.ancillary_attribute9,
292 new_references.ancillary_attribute10,
293 new_references.ancillary_attribute11,
294 new_references.ancillary_attribute12,
295 new_references.ancillary_attribute13,
296 new_references.ancillary_attribute14,
297 new_references.ancillary_attribute15,
298 new_references.enabled_flag
299 )
300 ) THEN
301 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
302 igs_ge_msg_stack.add;
303 app_exception.raise_exception;
304 END IF;
305
306 END check_uniqueness;
307
308
309 PROCEDURE check_parent_existance AS
310 /*
311 || Created By : [email protected]
312 || Created On : 09-APR-2001
313 || Purpose : Checks for the existance of Parent records.
314 || Known limitations, enhancements or remarks :
315 || Change History :
316 || Who When What
317 || (reverse chronological order - newest change first)
318 */
319 BEGIN
320
321 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
325 (new_references.fee_ci_sequence_number IS NULL) OR
322 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
323 (old_references.fee_type = new_references.fee_type)) OR
324 ((new_references.fee_cal_type IS NULL) OR
326 (new_references.fee_type IS NULL))) THEN
327 NULL;
328 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
329 new_references.fee_type,
330 new_references.fee_cal_type,
331 new_references.fee_ci_sequence_number
332 ) THEN
333 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 END IF;
337
338 END check_parent_existance;
339
340
341 FUNCTION get_pk_for_validation (
342 x_ancillary_rate_id IN NUMBER
343 ) RETURN BOOLEAN AS
344 /*
345 || Created By : [email protected]
346 || Created On : 09-APR-2001
347 || Purpose : Validates the Primary Key of the table.
348 || Known limitations, enhancements or remarks :
349 || Change History :
350 || Who When What
351 || (reverse chronological order - newest change first)
352 */
353 CURSOR cur_rowid IS
354 SELECT rowid
355 FROM igs_fi_anc_rates
356 WHERE ancillary_rate_id = x_ancillary_rate_id
357 FOR UPDATE NOWAIT;
358
359 lv_rowid cur_rowid%RowType;
360
361 BEGIN
362
363 OPEN cur_rowid;
364 FETCH cur_rowid INTO lv_rowid;
365 IF (cur_rowid%FOUND) THEN
366 CLOSE cur_rowid;
367 RETURN(TRUE);
368 ELSE
369 CLOSE cur_rowid;
370 RETURN(FALSE);
371 END IF;
372
373 END get_pk_for_validation;
374
375
376 FUNCTION get_uk_for_validation (
377 x_fee_cal_type IN VARCHAR2,
378 x_fee_ci_sequence_number IN NUMBER,
379 x_fee_type IN VARCHAR2,
380 x_ancillary_attribute1 IN VARCHAR2,
381 x_ancillary_attribute2 IN VARCHAR2,
382 x_ancillary_attribute3 IN VARCHAR2,
383 x_ancillary_attribute4 IN VARCHAR2,
384 x_ancillary_attribute5 IN VARCHAR2,
385 x_ancillary_attribute6 IN VARCHAR2,
386 x_ancillary_attribute7 IN VARCHAR2,
387 x_ancillary_attribute8 IN VARCHAR2,
388 x_ancillary_attribute9 IN VARCHAR2,
389 x_ancillary_attribute10 IN VARCHAR2,
390 x_ancillary_attribute11 IN VARCHAR2,
391 x_ancillary_attribute12 IN VARCHAR2,
392 x_ancillary_attribute13 IN VARCHAR2,
393 x_ancillary_attribute14 IN VARCHAR2,
394 x_ancillary_attribute15 IN VARCHAR2,
395 x_enabled_flag IN VARCHAR2
396 ) RETURN BOOLEAN AS
397 /*
398 || Created By : [email protected]
399 || Created On : 09-APR-2001
400 || Purpose : Validates the Unique Keys of the table.
401 || Known limitations, enhancements or remarks :
405 */
402 || Change History :
403 || Who When What
404 || (reverse chronological order - newest change first)
406 CURSOR cur_rowid IS
407 SELECT rowid
408 FROM igs_fi_anc_rates
409 WHERE fee_cal_type = x_fee_cal_type
410 AND fee_ci_sequence_number = x_fee_ci_sequence_number
411 AND fee_type = x_fee_type
412 AND ((ancillary_attribute1 = x_ancillary_attribute1) OR (ancillary_attribute1 IS NULL AND x_ancillary_attribute1 IS NULL))
413 AND ((ancillary_attribute2 = x_ancillary_attribute2) OR (ancillary_attribute2 IS NULL AND x_ancillary_attribute2 IS NULL))
414 AND ((ancillary_attribute3 = x_ancillary_attribute3) OR (ancillary_attribute3 IS NULL AND x_ancillary_attribute3 IS NULL))
415 AND ((ancillary_attribute4 = x_ancillary_attribute4) OR (ancillary_attribute4 IS NULL AND x_ancillary_attribute4 IS NULL))
416 AND ((ancillary_attribute5 = x_ancillary_attribute5) OR (ancillary_attribute5 IS NULL AND x_ancillary_attribute5 IS NULL))
417 AND ((ancillary_attribute6 = x_ancillary_attribute6) OR (ancillary_attribute6 IS NULL AND x_ancillary_attribute6 IS NULL))
418 AND ((ancillary_attribute7 = x_ancillary_attribute7) OR (ancillary_attribute7 IS NULL AND x_ancillary_attribute7 IS NULL))
419 AND ((ancillary_attribute8 = x_ancillary_attribute8) OR (ancillary_attribute8 IS NULL AND x_ancillary_attribute8 IS NULL))
420 AND ((ancillary_attribute9 = x_ancillary_attribute9) OR (ancillary_attribute9 IS NULL AND x_ancillary_attribute9 IS NULL))
421 AND ((ancillary_attribute10 = x_ancillary_attribute10) OR (ancillary_attribute10 IS NULL AND x_ancillary_attribute10 IS NULL))
422 AND ((ancillary_attribute11 = x_ancillary_attribute11) OR (ancillary_attribute11 IS NULL AND x_ancillary_attribute11 IS NULL))
423 AND ((ancillary_attribute12 = x_ancillary_attribute12) OR (ancillary_attribute12 IS NULL AND x_ancillary_attribute12 IS NULL))
424 AND ((ancillary_attribute13 = x_ancillary_attribute13) OR (ancillary_attribute13 IS NULL AND x_ancillary_attribute13 IS NULL))
425 AND ((ancillary_attribute14 = x_ancillary_attribute14) OR (ancillary_attribute14 IS NULL AND x_ancillary_attribute14 IS NULL))
426 AND ((ancillary_attribute15 = x_ancillary_attribute15) OR (ancillary_attribute15 IS NULL AND x_ancillary_attribute15 IS NULL))
427 AND ((enabled_flag = x_enabled_flag) OR (enabled_flag IS NULL AND x_enabled_flag IS NULL))
428 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
429
430 lv_rowid cur_rowid%RowType;
431
432 BEGIN
433
434 OPEN cur_rowid;
435 FETCH cur_rowid INTO lv_rowid;
436 IF (cur_rowid%FOUND) THEN
437 CLOSE cur_rowid;
438 RETURN (true);
439 ELSE
440 CLOSE cur_rowid;
441 RETURN(FALSE);
442 END IF;
443
444 END get_uk_for_validation ;
445
449 x_ancillary_rate_id IN NUMBER DEFAULT NULL,
446 PROCEDURE before_dml (
447 p_action IN VARCHAR2,
448 x_rowid IN VARCHAR2 DEFAULT NULL,
450 x_fee_type IN VARCHAR2 DEFAULT NULL,
451 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
452 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
453 x_ancillary_attribute1 IN VARCHAR2 DEFAULT NULL,
454 x_ancillary_attribute2 IN VARCHAR2 DEFAULT NULL,
455 x_ancillary_attribute3 IN VARCHAR2 DEFAULT NULL,
456 x_ancillary_attribute4 IN VARCHAR2 DEFAULT NULL,
457 x_ancillary_attribute5 IN VARCHAR2 DEFAULT NULL,
458 x_ancillary_attribute6 IN VARCHAR2 DEFAULT NULL,
459 x_ancillary_attribute7 IN VARCHAR2 DEFAULT NULL,
460 x_ancillary_attribute8 IN VARCHAR2 DEFAULT NULL,
461 x_ancillary_attribute9 IN VARCHAR2 DEFAULT NULL,
462 x_ancillary_attribute10 IN VARCHAR2 DEFAULT NULL,
463 x_ancillary_attribute11 IN VARCHAR2 DEFAULT NULL,
464 x_ancillary_attribute12 IN VARCHAR2 DEFAULT NULL,
465 x_ancillary_attribute13 IN VARCHAR2 DEFAULT NULL,
466 x_ancillary_attribute14 IN VARCHAR2 DEFAULT NULL,
467 x_ancillary_attribute15 IN VARCHAR2 DEFAULT NULL,
468 x_ancillary_chg_rate IN NUMBER DEFAULT NULL,
469 x_enabled_flag IN VARCHAR2 DEFAULT NULL,
470 x_creation_date IN DATE DEFAULT NULL,
471 x_created_by IN NUMBER DEFAULT NULL,
472 x_last_update_date IN DATE DEFAULT NULL,
473 x_last_updated_by IN NUMBER DEFAULT NULL,
474 x_last_update_login IN NUMBER DEFAULT NULL
475 ) AS
476 /*
477 || Created By : [email protected]
478 || Created On : 09-APR-2001
479 || Purpose : Initialises the columns, Checks Constraints, Calls the
480 || Trigger Handlers for the table, before any DML operation.
481 || Known limitations, enhancements or remarks :
482 || Change History :
483 || Who When What
484 || (reverse chronological order - newest change first)
485 */
486 BEGIN
487
488 set_column_values (
489 p_action,
490 x_rowid,
491 x_ancillary_rate_id,
492 x_fee_type,
493 x_fee_cal_type,
494 x_fee_ci_sequence_number,
495 x_ancillary_attribute1,
496 x_ancillary_attribute2,
497 x_ancillary_attribute3,
498 x_ancillary_attribute4,
499 x_ancillary_attribute5,
500 x_ancillary_attribute6,
501 x_ancillary_attribute7,
502 x_ancillary_attribute8,
503 x_ancillary_attribute9,
504 x_ancillary_attribute10,
505 x_ancillary_attribute11,
506 x_ancillary_attribute12,
507 x_ancillary_attribute13,
508 x_ancillary_attribute14,
509 x_ancillary_attribute15,
510 x_ancillary_chg_rate,
511 x_enabled_flag,
512 x_creation_date,
513 x_created_by,
514 x_last_update_date,
515 x_last_updated_by,
516 x_last_update_login
517 );
518
519 IF (p_action = 'INSERT') THEN
520 -- Call all the procedures related to Before Insert.
521 IF ( get_pk_for_validation(
522 new_references.ancillary_rate_id
523 )
524 ) THEN
525 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
528 END IF;
529 check_uniqueness;
530 check_parent_existance;
531 ELSIF (p_action = 'UPDATE') THEN
532 -- Call all the procedures related to Before Update.
533 check_uniqueness;
534 check_parent_existance;
535 BeforeRowUpdateDelete(p_updating => TRUE);
536 ELSIF (p_action = 'VALIDATE_INSERT') THEN
537 -- Call all the procedures related to Before Insert.
538 IF ( get_pk_for_validation (
539 new_references.ancillary_rate_id
540 )
541 ) THEN
542 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
543 igs_ge_msg_stack.add;
544 app_exception.raise_exception;
545 END IF;
546 check_uniqueness;
547 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
548 check_uniqueness;
549 BeforeRowUpdateDelete(p_updating => TRUE);
550 ELSIF (p_action = 'VALIDATE_DELETE') THEN
551 BeforeRowUpdateDelete(p_deleting => TRUE);
552 ELSIF (p_action = 'DELETE') THEN
556 END before_dml;
553 BeforeRowUpdateDelete(p_deleting => TRUE);
554 END IF;
555
557
558
559 PROCEDURE insert_row (
560 x_rowid IN OUT NOCOPY VARCHAR2,
561 x_ancillary_rate_id IN OUT NOCOPY NUMBER,
562 x_fee_type IN VARCHAR2,
563 x_fee_cal_type IN VARCHAR2,
564 x_fee_ci_sequence_number IN NUMBER,
565 x_ancillary_attribute1 IN VARCHAR2,
566 x_ancillary_attribute2 IN VARCHAR2,
567 x_ancillary_attribute3 IN VARCHAR2,
568 x_ancillary_attribute4 IN VARCHAR2,
569 x_ancillary_attribute5 IN VARCHAR2,
570 x_ancillary_attribute6 IN VARCHAR2,
571 x_ancillary_attribute7 IN VARCHAR2,
572 x_ancillary_attribute8 IN VARCHAR2,
573 x_ancillary_attribute9 IN VARCHAR2,
574 x_ancillary_attribute10 IN VARCHAR2,
575 x_ancillary_attribute11 IN VARCHAR2,
576 x_ancillary_attribute12 IN VARCHAR2,
577 x_ancillary_attribute13 IN VARCHAR2,
578 x_ancillary_attribute14 IN VARCHAR2,
579 x_ancillary_attribute15 IN VARCHAR2,
580 x_ancillary_chg_rate IN NUMBER,
581 x_enabled_flag IN VARCHAR2,
582 x_mode IN VARCHAR2 DEFAULT 'R'
583 ) AS
584 /*
585 || Created By : [email protected]
586 || Created On : 09-APR-2001
587 || Purpose : Handles the INSERT DML logic for the table.
588 || Known limitations, enhancements or remarks :
589 || Change History :
590 || Who When What
591 || (reverse chronological order - newest change first)
592 */
593 CURSOR c IS
594 SELECT rowid
595 FROM igs_fi_anc_rates
596 WHERE ancillary_rate_id = x_ancillary_rate_id;
597
598 x_last_update_date DATE;
599 x_last_updated_by NUMBER;
600 x_last_update_login NUMBER;
601
602 BEGIN
603
604 x_last_update_date := SYSDATE;
605 IF (x_mode = 'I') THEN
606 x_last_updated_by := 1;
607 x_last_update_login := 0;
608 ELSIF (x_mode = 'R') THEN
609 x_last_updated_by := fnd_global.user_id;
610 IF (x_last_updated_by IS NULL) THEN
611 x_last_updated_by := -1;
612 END IF;
613 x_last_update_login := fnd_global.login_id;
614 IF (x_last_update_login IS NULL) THEN
615 x_last_update_login := -1;
616 END IF;
617 ELSE
618 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
619 igs_ge_msg_stack.add;
620 app_exception.raise_exception;
621 END IF;
622 SELECT IGS_FI_ANC_RATES_S.NEXTVAL INTO x_ancillary_rate_id FROM DUAL;
623
624 before_dml(
625 p_action => 'INSERT',
626 x_rowid => x_rowid,
627 x_ancillary_rate_id => x_ancillary_rate_id,
628 x_fee_type => x_fee_type,
629 x_fee_cal_type => x_fee_cal_type,
630 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
631 x_ancillary_attribute1 => x_ancillary_attribute1,
632 x_ancillary_attribute2 => x_ancillary_attribute2,
633 x_ancillary_attribute3 => x_ancillary_attribute3,
634 x_ancillary_attribute4 => x_ancillary_attribute4,
635 x_ancillary_attribute5 => x_ancillary_attribute5,
636 x_ancillary_attribute6 => x_ancillary_attribute6,
637 x_ancillary_attribute7 => x_ancillary_attribute7,
638 x_ancillary_attribute8 => x_ancillary_attribute8,
639 x_ancillary_attribute9 => x_ancillary_attribute9,
640 x_ancillary_attribute10 => x_ancillary_attribute10,
641 x_ancillary_attribute11 => x_ancillary_attribute11,
642 x_ancillary_attribute12 => x_ancillary_attribute12,
643 x_ancillary_attribute13 => x_ancillary_attribute13,
644 x_ancillary_attribute14 => x_ancillary_attribute14,
645 x_ancillary_attribute15 => x_ancillary_attribute15,
646 x_ancillary_chg_rate => x_ancillary_chg_rate,
650 x_last_update_date => x_last_update_date,
647 x_enabled_flag => x_enabled_flag,
648 x_creation_date => x_last_update_date,
649 x_created_by => x_last_updated_by,
651 x_last_updated_by => x_last_updated_by,
652 x_last_update_login => x_last_update_login
653 );
654
655 INSERT INTO igs_fi_anc_rates (
656 ancillary_rate_id,
657 fee_type,
658 fee_cal_type,
659 fee_ci_sequence_number,
660 ancillary_attribute1,
661 ancillary_attribute2,
662 ancillary_attribute3,
663 ancillary_attribute4,
664 ancillary_attribute5,
665 ancillary_attribute6,
666 ancillary_attribute7,
667 ancillary_attribute8,
668 ancillary_attribute9,
669 ancillary_attribute10,
670 ancillary_attribute11,
671 ancillary_attribute12,
672 ancillary_attribute13,
673 ancillary_attribute14,
674 ancillary_attribute15,
675 ancillary_chg_rate,
676 enabled_flag,
677 creation_date,
678 created_by,
679 last_update_date,
680 last_updated_by,
681 last_update_login
682 ) VALUES (
683 new_references.ancillary_rate_id,
684 new_references.fee_type,
685 new_references.fee_cal_type,
686 new_references.fee_ci_sequence_number,
687 new_references.ancillary_attribute1,
688 new_references.ancillary_attribute2,
689 new_references.ancillary_attribute3,
690 new_references.ancillary_attribute4,
691 new_references.ancillary_attribute5,
692 new_references.ancillary_attribute6,
693 new_references.ancillary_attribute7,
694 new_references.ancillary_attribute8,
695 new_references.ancillary_attribute9,
696 new_references.ancillary_attribute10,
697 new_references.ancillary_attribute11,
698 new_references.ancillary_attribute12,
699 new_references.ancillary_attribute13,
700 new_references.ancillary_attribute14,
701 new_references.ancillary_attribute15,
702 new_references.ancillary_chg_rate,
703 new_references.enabled_flag,
704 x_last_update_date,
705 x_last_updated_by,
706 x_last_update_date,
707 x_last_updated_by,
708 x_last_update_login
709 );
710
711 OPEN c;
712 FETCH c INTO x_rowid;
713 IF (c%NOTFOUND) THEN
714 CLOSE c;
715 RAISE NO_DATA_FOUND;
716 END IF;
717 CLOSE c;
718
719 l_rowid := NULL;
720
721 END insert_row;
722
723
724 PROCEDURE lock_row (
725 x_rowid IN VARCHAR2,
726 x_ancillary_rate_id IN NUMBER,
727 x_fee_type IN VARCHAR2,
728 x_fee_cal_type IN VARCHAR2,
729 x_fee_ci_sequence_number IN NUMBER,
730 x_ancillary_attribute1 IN VARCHAR2,
731 x_ancillary_attribute2 IN VARCHAR2,
732 x_ancillary_attribute3 IN VARCHAR2,
733 x_ancillary_attribute4 IN VARCHAR2,
734 x_ancillary_attribute5 IN VARCHAR2,
735 x_ancillary_attribute6 IN VARCHAR2,
736 x_ancillary_attribute7 IN VARCHAR2,
737 x_ancillary_attribute8 IN VARCHAR2,
738 x_ancillary_attribute9 IN VARCHAR2,
739 x_ancillary_attribute10 IN VARCHAR2,
740 x_ancillary_attribute11 IN VARCHAR2,
741 x_ancillary_attribute12 IN VARCHAR2,
742 x_ancillary_attribute13 IN VARCHAR2,
743 x_ancillary_attribute14 IN VARCHAR2,
744 x_ancillary_attribute15 IN VARCHAR2,
745 x_ancillary_chg_rate IN NUMBER,
746 x_enabled_flag IN VARCHAR2
747 ) AS
748 /*
749 || Created By : [email protected]
750 || Created On : 09-APR-2001
751 || Purpose : Handles the LOCK mechanism for the table.
752 || Known limitations, enhancements or remarks :
753 || Change History :
754 || Who When What
755 || (reverse chronological order - newest change first)
756 */
757 CURSOR c1 IS
758 SELECT
759 fee_type,
760 fee_cal_type,
761 fee_ci_sequence_number,
762 ancillary_attribute1,
763 ancillary_attribute2,
764 ancillary_attribute3,
765 ancillary_attribute4,
766 ancillary_attribute5,
767 ancillary_attribute6,
768 ancillary_attribute7,
769 ancillary_attribute8,
770 ancillary_attribute9,
771 ancillary_attribute10,
772 ancillary_attribute11,
773 ancillary_attribute12,
774 ancillary_attribute13,
775 ancillary_attribute14,
776 ancillary_attribute15,
777 ancillary_chg_rate,
778 enabled_flag
779 FROM igs_fi_anc_rates
780 WHERE rowid = x_rowid
781 FOR UPDATE NOWAIT;
782
783 tlinfo c1%ROWTYPE;
784
785 BEGIN
786
787 OPEN c1;
788 FETCH c1 INTO tlinfo;
789 IF (c1%notfound) THEN
793 app_exception.raise_exception;
790 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
791 igs_ge_msg_stack.add;
792 CLOSE c1;
797
794 RETURN;
795 END IF;
796 CLOSE c1;
798 IF (
799 (tlinfo.fee_type = x_fee_type)
800 AND (tlinfo.fee_cal_type = x_fee_cal_type)
801 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
802 AND ((tlinfo.ancillary_attribute1 = x_ancillary_attribute1) OR ((tlinfo.ancillary_attribute1 IS NULL) AND (X_ancillary_attribute1 IS NULL)))
803 AND ((tlinfo.ancillary_attribute2 = x_ancillary_attribute2) OR ((tlinfo.ancillary_attribute2 IS NULL) AND (X_ancillary_attribute2 IS NULL)))
804 AND ((tlinfo.ancillary_attribute3 = x_ancillary_attribute3) OR ((tlinfo.ancillary_attribute3 IS NULL) AND (X_ancillary_attribute3 IS NULL)))
805 AND ((tlinfo.ancillary_attribute4 = x_ancillary_attribute4) OR ((tlinfo.ancillary_attribute4 IS NULL) AND (X_ancillary_attribute4 IS NULL)))
806 AND ((tlinfo.ancillary_attribute5 = x_ancillary_attribute5) OR ((tlinfo.ancillary_attribute5 IS NULL) AND (X_ancillary_attribute5 IS NULL)))
807 AND ((tlinfo.ancillary_attribute6 = x_ancillary_attribute6) OR ((tlinfo.ancillary_attribute6 IS NULL) AND (X_ancillary_attribute6 IS NULL)))
808 AND ((tlinfo.ancillary_attribute7 = x_ancillary_attribute7) OR ((tlinfo.ancillary_attribute7 IS NULL) AND (X_ancillary_attribute7 IS NULL)))
809 AND ((tlinfo.ancillary_attribute8 = x_ancillary_attribute8) OR ((tlinfo.ancillary_attribute8 IS NULL) AND (X_ancillary_attribute8 IS NULL)))
810 AND ((tlinfo.ancillary_attribute9 = x_ancillary_attribute9) OR ((tlinfo.ancillary_attribute9 IS NULL) AND (X_ancillary_attribute9 IS NULL)))
811 AND ((tlinfo.ancillary_attribute10 = x_ancillary_attribute10) OR ((tlinfo.ancillary_attribute10 IS NULL) AND (X_ancillary_attribute10 IS NULL)))
812 AND ((tlinfo.ancillary_attribute11 = x_ancillary_attribute11) OR ((tlinfo.ancillary_attribute11 IS NULL) AND (X_ancillary_attribute11 IS NULL)))
813 AND ((tlinfo.ancillary_attribute12 = x_ancillary_attribute12) OR ((tlinfo.ancillary_attribute12 IS NULL) AND (X_ancillary_attribute12 IS NULL)))
814 AND ((tlinfo.ancillary_attribute13 = x_ancillary_attribute13) OR ((tlinfo.ancillary_attribute13 IS NULL) AND (X_ancillary_attribute13 IS NULL)))
815 AND ((tlinfo.ancillary_attribute14 = x_ancillary_attribute14) OR ((tlinfo.ancillary_attribute14 IS NULL) AND (X_ancillary_attribute14 IS NULL)))
816 AND ((tlinfo.ancillary_attribute15 = x_ancillary_attribute15) OR ((tlinfo.ancillary_attribute15 IS NULL) AND (X_ancillary_attribute15 IS NULL)))
817 AND (tlinfo.ancillary_chg_rate = x_ancillary_chg_rate)
818 AND ((tlinfo.enabled_flag = x_enabled_flag) OR ((tlinfo.enabled_flag IS NULL) AND (X_enabled_flag IS NULL)))
819 ) THEN
820 NULL;
821 ELSE
822 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
823 igs_ge_msg_stack.add;
824 app_exception.raise_exception;
825 END IF;
826
827 l_rowid := NULL;
828 RETURN;
829
830 END lock_row;
831
832
833 PROCEDURE update_row (
834 x_rowid IN VARCHAR2,
835 x_ancillary_rate_id IN NUMBER,
836 x_fee_type IN VARCHAR2,
837 x_fee_cal_type IN VARCHAR2,
838 x_fee_ci_sequence_number IN NUMBER,
839 x_ancillary_attribute1 IN VARCHAR2,
840 x_ancillary_attribute2 IN VARCHAR2,
841 x_ancillary_attribute3 IN VARCHAR2,
842 x_ancillary_attribute4 IN VARCHAR2,
843 x_ancillary_attribute5 IN VARCHAR2,
844 x_ancillary_attribute6 IN VARCHAR2,
845 x_ancillary_attribute7 IN VARCHAR2,
846 x_ancillary_attribute8 IN VARCHAR2,
847 x_ancillary_attribute9 IN VARCHAR2,
848 x_ancillary_attribute10 IN VARCHAR2,
849 x_ancillary_attribute11 IN VARCHAR2,
850 x_ancillary_attribute12 IN VARCHAR2,
851 x_ancillary_attribute13 IN VARCHAR2,
852 x_ancillary_attribute14 IN VARCHAR2,
853 x_ancillary_attribute15 IN VARCHAR2,
854 x_ancillary_chg_rate IN NUMBER,
855 x_enabled_flag IN VARCHAR2,
856 x_mode IN VARCHAR2 DEFAULT 'R'
857 ) AS
858 /*
859 || Created By : [email protected]
860 || Created On : 09-APR-2001
861 || Purpose : Handles the UPDATE DML logic for the table.
862 || Known limitations, enhancements or remarks :
863 || Change History :
864 || Who When What
868 x_last_updated_by NUMBER;
865 || (reverse chronological order - newest change first)
866 */
867 x_last_update_date DATE ;
869 x_last_update_login NUMBER;
870
871 BEGIN
872
873 x_last_update_date := SYSDATE;
874 IF (X_MODE = 'I') THEN
875 x_last_updated_by := 1;
876 x_last_update_login := 0;
877 ELSIF (x_mode = 'R') THEN
878 x_last_updated_by := fnd_global.user_id;
879 IF x_last_updated_by IS NULL THEN
880 x_last_updated_by := -1;
881 END IF;
882 x_last_update_login := fnd_global.login_id;
883 IF (x_last_update_login IS NULL) THEN
884 x_last_update_login := -1;
885 END IF;
886 ELSE
887 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
888 igs_ge_msg_stack.add;
889 app_exception.raise_exception;
890 END IF;
891
892 before_dml(
893 p_action => 'UPDATE',
894 x_rowid => x_rowid,
895 x_ancillary_rate_id => x_ancillary_rate_id,
896 x_fee_type => x_fee_type,
897 x_fee_cal_type => x_fee_cal_type,
898 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
899 x_ancillary_attribute1 => x_ancillary_attribute1,
900 x_ancillary_attribute2 => x_ancillary_attribute2,
901 x_ancillary_attribute3 => x_ancillary_attribute3,
902 x_ancillary_attribute4 => x_ancillary_attribute4,
903 x_ancillary_attribute5 => x_ancillary_attribute5,
904 x_ancillary_attribute6 => x_ancillary_attribute6,
905 x_ancillary_attribute7 => x_ancillary_attribute7,
906 x_ancillary_attribute8 => x_ancillary_attribute8,
907 x_ancillary_attribute9 => x_ancillary_attribute9,
908 x_ancillary_attribute10 => x_ancillary_attribute10,
909 x_ancillary_attribute11 => x_ancillary_attribute11,
910 x_ancillary_attribute12 => x_ancillary_attribute12,
911 x_ancillary_attribute13 => x_ancillary_attribute13,
912 x_ancillary_attribute14 => x_ancillary_attribute14,
913 x_ancillary_attribute15 => x_ancillary_attribute15,
914 x_ancillary_chg_rate => x_ancillary_chg_rate,
915 x_enabled_flag => x_enabled_flag,
916 x_creation_date => x_last_update_date,
917 x_created_by => x_last_updated_by,
918 x_last_update_date => x_last_update_date,
919 x_last_updated_by => x_last_updated_by,
920 x_last_update_login => x_last_update_login
921 );
922
923 UPDATE igs_fi_anc_rates
924 SET
925 fee_type = new_references.fee_type,
926 fee_cal_type = new_references.fee_cal_type,
927 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
928 ancillary_attribute1 = new_references.ancillary_attribute1,
929 ancillary_attribute2 = new_references.ancillary_attribute2,
930 ancillary_attribute3 = new_references.ancillary_attribute3,
931 ancillary_attribute4 = new_references.ancillary_attribute4,
932 ancillary_attribute5 = new_references.ancillary_attribute5,
933 ancillary_attribute6 = new_references.ancillary_attribute6,
934 ancillary_attribute7 = new_references.ancillary_attribute7,
935 ancillary_attribute8 = new_references.ancillary_attribute8,
936 ancillary_attribute9 = new_references.ancillary_attribute9,
937 ancillary_attribute10 = new_references.ancillary_attribute10,
938 ancillary_attribute11 = new_references.ancillary_attribute11,
939 ancillary_attribute12 = new_references.ancillary_attribute12,
940 ancillary_attribute13 = new_references.ancillary_attribute13,
941 ancillary_attribute14 = new_references.ancillary_attribute14,
942 ancillary_attribute15 = new_references.ancillary_attribute15,
943 ancillary_chg_rate = new_references.ancillary_chg_rate,
944 enabled_flag = new_references.enabled_flag,
945 last_update_date = x_last_update_date,
946 last_updated_by = x_last_updated_by,
947 last_update_login = x_last_update_login
948 WHERE rowid = x_rowid;
949
950 IF (SQL%NOTFOUND) THEN
951 RAISE NO_DATA_FOUND;
952 END IF;
953
954 l_rowid := NULL;
955
956 END update_row;
957
958
959 PROCEDURE add_row (
960 x_rowid IN OUT NOCOPY VARCHAR2,
961 x_ancillary_rate_id IN OUT NOCOPY NUMBER,
962 x_fee_type IN VARCHAR2,
963 x_fee_cal_type IN VARCHAR2,
964 x_fee_ci_sequence_number IN NUMBER,
965 x_ancillary_attribute1 IN VARCHAR2,
966 x_ancillary_attribute2 IN VARCHAR2,
967 x_ancillary_attribute3 IN VARCHAR2,
968 x_ancillary_attribute4 IN VARCHAR2,
969 x_ancillary_attribute5 IN VARCHAR2,
970 x_ancillary_attribute6 IN VARCHAR2,
971 x_ancillary_attribute7 IN VARCHAR2,
972 x_ancillary_attribute8 IN VARCHAR2,
973 x_ancillary_attribute9 IN VARCHAR2,
974 x_ancillary_attribute10 IN VARCHAR2,
975 x_ancillary_attribute11 IN VARCHAR2,
976 x_ancillary_attribute12 IN VARCHAR2,
977 x_ancillary_attribute13 IN VARCHAR2,
978 x_ancillary_attribute14 IN VARCHAR2,
979 x_ancillary_attribute15 IN VARCHAR2,
980 x_ancillary_chg_rate IN NUMBER,
981 x_enabled_flag IN VARCHAR2,
982 x_mode IN VARCHAR2 DEFAULT 'R'
983 ) AS
984 /*
985 || Created By : [email protected]
986 || Created On : 09-APR-2001
987 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
988 || Known limitations, enhancements or remarks :
989 || Change History :
990 || Who When What
991 || (reverse chronological order - newest change first)
992 */
993 CURSOR c1 IS
994 SELECT rowid
995 FROM igs_fi_anc_rates
996 WHERE ancillary_rate_id = x_ancillary_rate_id;
997
998 BEGIN
999
1000 OPEN c1;
1001 FETCH c1 INTO x_rowid;
1002 IF (c1%NOTFOUND) THEN
1003 CLOSE c1;
1004
1005 insert_row (
1006 x_rowid,
1007 x_ancillary_rate_id,
1008 x_fee_type,
1009 x_fee_cal_type,
1010 x_fee_ci_sequence_number,
1011 x_ancillary_attribute1,
1012 x_ancillary_attribute2,
1013 x_ancillary_attribute3,
1014 x_ancillary_attribute4,
1015 x_ancillary_attribute5,
1016 x_ancillary_attribute6,
1017 x_ancillary_attribute7,
1018 x_ancillary_attribute8,
1019 x_ancillary_attribute9,
1020 x_ancillary_attribute10,
1021 x_ancillary_attribute11,
1022 x_ancillary_attribute12,
1023 x_ancillary_attribute13,
1024 x_ancillary_attribute14,
1025 x_ancillary_attribute15,
1026 x_ancillary_chg_rate,
1027 x_enabled_flag,
1028 x_mode
1029 );
1030 RETURN;
1031 END IF;
1032 CLOSE c1;
1033
1034 update_row (
1035 x_rowid,
1036 x_ancillary_rate_id,
1037 x_fee_type,
1038 x_fee_cal_type,
1039 x_fee_ci_sequence_number,
1040 x_ancillary_attribute1,
1041 x_ancillary_attribute2,
1042 x_ancillary_attribute3,
1043 x_ancillary_attribute4,
1044 x_ancillary_attribute5,
1045 x_ancillary_attribute6,
1046 x_ancillary_attribute7,
1047 x_ancillary_attribute8,
1048 x_ancillary_attribute9,
1049 x_ancillary_attribute10,
1050 x_ancillary_attribute11,
1051 x_ancillary_attribute12,
1052 x_ancillary_attribute13,
1053 x_ancillary_attribute14,
1054 x_ancillary_attribute15,
1055 x_ancillary_chg_rate,
1056 x_enabled_flag,
1057 x_mode
1058 );
1059
1060 l_rowid := NULL;
1061
1062 END add_row;
1063
1064
1065 PROCEDURE delete_row (
1066 x_rowid IN VARCHAR2
1067 ) AS
1068 /*
1069 || Created By : [email protected]
1070 || Created On : 09-APR-2001
1071 || Purpose : Handles the DELETE DML logic for the table.
1072 || Known limitations, enhancements or remarks :
1073 || Change History :
1074 || Who When What
1075 || (reverse chronological order - newest change first)
1076 */
1077 BEGIN
1078
1079 before_dml (
1080 p_action => 'DELETE',
1081 x_rowid => x_rowid
1082 );
1083
1084 DELETE FROM igs_fi_anc_rates
1085 WHERE rowid = x_rowid;
1086
1087 IF (SQL%NOTFOUND) THEN
1088 RAISE NO_DATA_FOUND;
1089 END IF;
1090
1091 l_rowid := NULL;
1092
1093 END delete_row;
1094
1095
1096 END igs_fi_anc_rates_pkg;
[Home] [Help]