1 PACKAGE BODY igs_fi_anc_rt_sgmnts_pkg AS
2 /* $Header: IGSSI83B.pls 115.9 2003/02/12 07:29:07 pathipat ship $ */
3
4 l_rowid VARCHAR2(25);
5 l_record_count NUMBER(3) := 0;
6 old_references igs_fi_anc_rt_sgmnts%ROWTYPE;
7 new_references igs_fi_anc_rt_sgmnts%ROWTYPE;
8
9 PROCEDURE set_column_values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2,
12 x_anc_rate_segment_id IN NUMBER ,
13 x_fee_type IN VARCHAR2,
14 x_fee_cal_type IN VARCHAR2,
15 x_fee_ci_sequence_number IN NUMBER ,
16 x_ancillary_attributes IN VARCHAR2,
17 x_ancillary_segments IN VARCHAR2,
18 x_enabled_flag IN VARCHAR2,
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 ) AS
25 /*
26 || Created By : [email protected]
27 || Created On : 09-APR-2001
28 || Purpose : Initialises the Old and New references for the columns of the table.
29 || Known limitations, enhancements or remarks :
30 || Change History :
31 || Who When What
32 || (reverse chronological order - newest change first)
33 */
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_FI_ANC_RT_SGMNTS
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 OPEN cur_old_ref_values;
47 FETCH cur_old_ref_values INTO old_references;
48 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49 CLOSE cur_old_ref_values;
50 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51 igs_ge_msg_stack.add;
52 app_exception.raise_exception;
53 RETURN;
54 END IF;
55 CLOSE cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.anc_rate_segment_id := x_anc_rate_segment_id;
59 new_references.fee_type := x_fee_type;
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.ancillary_attributes := x_ancillary_attributes;
63 new_references.ancillary_segments := x_ancillary_segments;
64 new_references.enabled_flag := x_enabled_flag;
65
66 IF (p_action = 'UPDATE') THEN
67 new_references.creation_date := old_references.creation_date;
68 new_references.created_by := old_references.created_by;
69 ELSE
70 new_references.creation_date := x_creation_date;
71 new_references.created_by := x_created_by;
72 END IF;
73
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
78 END set_column_values;
79
80 -- This local procedure is implementing the bussiness logic of limiting the number of ancillary segments to 15 and also
81 -- populating the ancillary_attributes column of the table with default values like ANCILLARY_ATTRIBUTE1,ANCILLARY_ATTRIBUTE2 etc
82
83 PROCEDURE BeforeRowInsert(x_fee_type IN VARCHAR2,
84 x_fee_cal_type IN VARCHAR2,
85 x_fee_ci_sequence_number IN NUMBER) AS
86 CURSOR cur_cnt IS
87 SELECT count(*)
88 FROM IGS_FI_ANC_RT_SGMNTS_V
89 WHERE fee_type = x_fee_type
90 AND fee_cal_type = x_fee_cal_type
91 AND fee_ci_sequence_number = x_fee_ci_sequence_number ;
92
93
94 BEGIN
95 Open cur_cnt;
96 Fetch cur_cnt INTO l_record_count ;
97 Close cur_cnt;
98
99 IF (l_record_count = 15) THEN
100 Fnd_Message.Set_Name('IGS','IGS_FI_MAX_15_SEGMENTS');
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 Return;
104 ELSE
105 new_references.ancillary_attributes := 'ANCILLARY_ATTRIBUTE'||To_Char(l_record_count +1);
106 END IF;
107 END BeforeRowInsert;
108
109
110
111 PROCEDURE check_parent_existance AS
112 /*
113 || Created By : [email protected]
114 || Created On : 09-APR-2001
115 || Purpose : Checks for the existance of Parent records.
116 || Known limitations, enhancements or remarks :
117 || Change History :
118 || Who When What
119 || (reverse chronological order - newest change first)
120 */
121 BEGIN
122
123 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
124 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
125 (old_references.fee_type = new_references.fee_type)) OR
126 ((new_references.fee_cal_type IS NULL) OR
127 (new_references.fee_ci_sequence_number IS NULL) OR
128 (new_references.fee_type IS NULL))) THEN
129 NULL;
130 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
131 new_references.fee_type,
132 new_references.fee_cal_type,
133 new_references.fee_ci_sequence_number
134 ) THEN
135 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 END IF;
139
140 END check_parent_existance;
141
142
143 FUNCTION get_pk_for_validation (
144 x_anc_rate_segment_id IN NUMBER
145 ) RETURN BOOLEAN AS
146 /*
147 || Created By : [email protected]
148 || Created On : 09-APR-2001
149 || Purpose : Validates the Primary Key of the table.
150 || Known limitations, enhancements or remarks :
151 || Change History :
152 || Who When What
153 || (reverse chronological order - newest change first)
154 */
155 CURSOR cur_rowid IS
156 SELECT rowid
157 FROM igs_fi_anc_rt_sgmnts
158 WHERE anc_rate_segment_id = x_anc_rate_segment_id
159 FOR UPDATE NOWAIT;
160
161 lv_rowid cur_rowid%RowType;
162
163 BEGIN
164
165 OPEN cur_rowid;
166 FETCH cur_rowid INTO lv_rowid;
167 IF (cur_rowid%FOUND) THEN
168 CLOSE cur_rowid;
169 RETURN(TRUE);
170 ELSE
171 CLOSE cur_rowid;
172 RETURN(FALSE);
173 END IF;
174
175 END get_pk_for_validation;
176
177
178 PROCEDURE before_dml (
179 p_action IN VARCHAR2,
180 x_rowid IN VARCHAR2,
181 x_anc_rate_segment_id IN NUMBER ,
182 x_fee_type IN VARCHAR2,
183 x_fee_cal_type IN VARCHAR2,
184 x_fee_ci_sequence_number IN NUMBER ,
185 x_ancillary_attributes IN VARCHAR2,
186 x_ancillary_segments IN VARCHAR2,
187 x_enabled_flag IN VARCHAR2,
188 x_creation_date IN DATE ,
189 x_created_by IN NUMBER ,
190 x_last_update_date IN DATE ,
191 x_last_updated_by IN NUMBER ,
192 x_last_update_login IN NUMBER
193 ) AS
194 /*
195 || Created By : [email protected]
196 || Created On : 09-APR-2001
197 || Purpose : Initialises the columns, Checks Constraints, Calls the
198 || Trigger Handlers for the table, before any DML operation.
199 || Known limitations, enhancements or remarks :
200 || Change History :
201 || Who When What
202 || (reverse chronological order - newest change first)
203 */
204 BEGIN
205
206 set_column_values (
207 p_action,
208 x_rowid,
209 x_anc_rate_segment_id,
210 x_fee_type,
211 x_fee_cal_type,
212 x_fee_ci_sequence_number,
213 x_ancillary_attributes,
214 x_ancillary_segments,
215 x_enabled_flag,
216 x_creation_date,
217 x_created_by,
218 x_last_update_date,
219 x_last_updated_by,
220 x_last_update_login
221 );
222
223 IF (p_action = 'INSERT') THEN
224 -- Call all the procedures related to Before Insert.
225 BeforeRowInsert(x_fee_type => x_fee_type,
226 x_fee_cal_type => x_fee_cal_type,
227 x_fee_ci_sequence_number => x_fee_ci_sequence_number);
228 IF ( get_pk_for_validation(
229 new_references.anc_rate_segment_id
230 )
231 ) THEN
232 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
233 igs_ge_msg_stack.add;
234 app_exception.raise_exception;
235 END IF;
236 check_parent_existance;
237 ELSIF (p_action = 'UPDATE') THEN
238 -- Call all the procedures related to Before Update.
239 check_parent_existance;
240 ELSIF (p_action = 'VALIDATE_INSERT') THEN
241 -- Call all the procedures related to Before Insert.
242 IF ( get_pk_for_validation (
243 new_references.anc_rate_segment_id
244 )
245 ) THEN
246 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249 END IF;
250 END IF;
251
252 END before_dml;
253
254
255 PROCEDURE insert_row (
256 x_rowid IN OUT NOCOPY VARCHAR2,
257 x_anc_rate_segment_id IN OUT NOCOPY NUMBER,
258 x_fee_type IN VARCHAR2,
259 x_fee_cal_type IN VARCHAR2,
260 x_fee_ci_sequence_number IN NUMBER,
261 x_ancillary_attributes IN OUT NOCOPY VARCHAR2,
262 x_ancillary_segments IN VARCHAR2,
263 x_enabled_flag IN VARCHAR2,
264 x_mode IN VARCHAR2
265 ) AS
266 /*
267 || Created By : [email protected]
268 || Created On : 09-APR-2001
269 || Purpose : Handles the INSERT DML logic for the table.
270 || Known limitations, enhancements or remarks :
271 || Change History :
272 || Who When What
273 || pathipat 24-Dec-2002 Bug: 2526337 - Copied new_references.ancillary_attributes
274 || to the OUT parameter x_ancillary_attributes
275 || (reverse chronological order - newest change first)
276 */
277 CURSOR c IS
278 SELECT rowid
279 FROM igs_fi_anc_rt_sgmnts
280 WHERE anc_rate_segment_id = x_anc_rate_segment_id;
281
282 x_last_update_date DATE;
283 x_last_updated_by NUMBER;
284 x_last_update_login NUMBER;
285
286 BEGIN
287
288 x_last_update_date := SYSDATE;
289 IF (x_mode = 'I') THEN
290 x_last_updated_by := 1;
291 x_last_update_login := 0;
292 ELSIF (x_mode = 'R') THEN
293 x_last_updated_by := fnd_global.user_id;
294 IF (x_last_updated_by IS NULL) THEN
295 x_last_updated_by := -1;
296 END IF;
297 x_last_update_login := fnd_global.login_id;
298 IF (x_last_update_login IS NULL) THEN
299 x_last_update_login := -1;
300 END IF;
301 ELSE
302 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305 END IF;
306
307 SELECT IGS_FI_ANC_RT_SGMNTS_S.NEXTVAL INTO x_anc_rate_segment_id FROM DUAL;
308 before_dml(
309 p_action => 'INSERT',
310 x_rowid => x_rowid,
311 x_anc_rate_segment_id => x_anc_rate_segment_id,
312 x_fee_type => x_fee_type,
313 x_fee_cal_type => x_fee_cal_type,
314 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
315 x_ancillary_attributes => x_ancillary_attributes,
316 x_ancillary_segments => x_ancillary_segments,
317 x_enabled_flag => x_enabled_flag,
318 x_creation_date => x_last_update_date,
319 x_created_by => x_last_updated_by,
320 x_last_update_date => x_last_update_date,
321 x_last_updated_by => x_last_updated_by,
322 x_last_update_login => x_last_update_login
323 );
324
325 INSERT INTO igs_fi_anc_rt_sgmnts (
326 anc_rate_segment_id,
327 fee_type,
328 fee_cal_type,
329 fee_ci_sequence_number,
330 ancillary_attributes,
331 ancillary_segments,
332 enabled_flag,
333 creation_date,
334 created_by,
335 last_update_date,
336 last_updated_by,
337 last_update_login
338 ) VALUES (
339 new_references.anc_rate_segment_id,
340 new_references.fee_type,
341 new_references.fee_cal_type,
342 new_references.fee_ci_sequence_number,
343 new_references.ancillary_attributes,
344 new_references.ancillary_segments,
348 x_last_update_date,
345 new_references.enabled_flag,
346 x_last_update_date,
347 x_last_updated_by,
349 x_last_updated_by,
350 x_last_update_login
351 );
352
353 -- The ancillary_attributes value has to be copied back to the OUT variable x_ancillary_attributes
354 -- Added for bug 2526337
355 x_ancillary_attributes := new_references.ancillary_attributes;
356
357 OPEN c;
358 FETCH c INTO x_rowid;
359 IF (c%NOTFOUND) THEN
360 CLOSE c;
361 RAISE NO_DATA_FOUND;
362 END IF;
363 CLOSE c;
364
365 l_rowid := NULL;
366
367 END insert_row;
368
369
370 PROCEDURE lock_row (
371 x_rowid IN VARCHAR2,
372 x_anc_rate_segment_id IN NUMBER,
373 x_fee_type IN VARCHAR2,
374 x_fee_cal_type IN VARCHAR2,
375 x_fee_ci_sequence_number IN NUMBER,
376 x_ancillary_attributes IN VARCHAR2,
377 x_ancillary_segments IN VARCHAR2,
378 x_enabled_flag IN VARCHAR2
379 ) AS
380 /*
381 || Created By : [email protected]
382 || Created On : 09-APR-2001
383 || Purpose : Handles the LOCK mechanism for the table.
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || (reverse chronological order - newest change first)
388 */
389 CURSOR c1 IS
390 SELECT
391 fee_type,
392 fee_cal_type,
393 fee_ci_sequence_number,
394 ancillary_attributes,
395 ancillary_segments,
396 enabled_flag
397 FROM igs_fi_anc_rt_sgmnts
398 WHERE rowid = x_rowid
399 FOR UPDATE NOWAIT;
400
401 tlinfo c1%ROWTYPE;
402
403 BEGIN
404
405 OPEN c1;
406 FETCH c1 INTO tlinfo;
407 IF (c1%notfound) THEN
408 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
409 igs_ge_msg_stack.add;
410 CLOSE c1;
411 app_exception.raise_exception;
412 RETURN;
413 END IF;
414 CLOSE c1;
415 IF (
416 (tlinfo.fee_type = x_fee_type)
417 AND (tlinfo.fee_cal_type = x_fee_cal_type)
418 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
419 AND ((tlinfo.ancillary_attributes = x_ancillary_attributes) OR ((tlinfo.ancillary_attributes IS NULL) AND (X_ancillary_attributes IS NULL)))
420 AND ((tlinfo.ancillary_segments = x_ancillary_segments) OR ((tlinfo.ancillary_segments IS NULL) AND (X_ancillary_segments IS NULL)))
421 AND ((tlinfo.enabled_flag = x_enabled_flag) OR ((tlinfo.enabled_flag IS NULL) AND (X_enabled_flag IS NULL)))
422 ) THEN
423 NULL;
424 ELSE
425 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
426 igs_ge_msg_stack.add;
427 app_exception.raise_exception;
428 END IF;
429
430 l_rowid := NULL;
431 RETURN;
432
433 END lock_row;
434
435
436 PROCEDURE update_row (
437 x_rowid IN VARCHAR2,
438 x_anc_rate_segment_id IN NUMBER,
439 x_fee_type IN VARCHAR2,
440 x_fee_cal_type IN VARCHAR2,
441 x_fee_ci_sequence_number IN NUMBER,
442 x_ancillary_attributes IN VARCHAR2,
443 x_ancillary_segments IN VARCHAR2,
444 x_enabled_flag IN VARCHAR2,
445 x_mode IN VARCHAR2
446 ) AS
447 /*
448 || Created By : [email protected]
449 || Created On : 09-APR-2001
453 || Who When What
450 || Purpose : Handles the UPDATE DML logic for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
454 || (reverse chronological order - newest change first)
455 */
456 x_last_update_date DATE ;
457 x_last_updated_by NUMBER;
458 x_last_update_login NUMBER;
459
460 BEGIN
461
462 x_last_update_date := SYSDATE;
463 IF (X_MODE = 'I') THEN
464 x_last_updated_by := 1;
465 x_last_update_login := 0;
466 ELSIF (x_mode = 'R') THEN
467 x_last_updated_by := fnd_global.user_id;
468 IF x_last_updated_by IS NULL THEN
469 x_last_updated_by := -1;
470 END IF;
471 x_last_update_login := fnd_global.login_id;
472 IF (x_last_update_login IS NULL) THEN
473 x_last_update_login := -1;
474 END IF;
475 ELSE
476 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
477 igs_ge_msg_stack.add;
478 app_exception.raise_exception;
479 END IF;
480
481 before_dml(
482 p_action => 'UPDATE',
483 x_rowid => x_rowid,
484 x_anc_rate_segment_id => x_anc_rate_segment_id,
485 x_fee_type => x_fee_type,
486 x_fee_cal_type => x_fee_cal_type,
487 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
488 x_ancillary_attributes => x_ancillary_attributes,
489 x_ancillary_segments => x_ancillary_segments,
490 x_enabled_flag => x_enabled_flag,
491 x_creation_date => x_last_update_date,
492 x_created_by => x_last_updated_by,
493 x_last_update_date => x_last_update_date,
494 x_last_updated_by => x_last_updated_by,
495 x_last_update_login => x_last_update_login
496 );
497
498 UPDATE igs_fi_anc_rt_sgmnts
499 SET
500 fee_type = new_references.fee_type,
501 fee_cal_type = new_references.fee_cal_type,
502 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
503 ancillary_attributes = new_references.ancillary_attributes,
504 ancillary_segments = new_references.ancillary_segments,
505 enabled_flag = new_references.enabled_flag,
506 last_update_date = x_last_update_date,
507 last_updated_by = x_last_updated_by,
508 last_update_login = x_last_update_login
509 WHERE rowid = x_rowid;
510
511 IF (SQL%NOTFOUND) THEN
512 RAISE NO_DATA_FOUND;
513 END IF;
514
515 l_rowid := NULL;
516
517 END update_row;
518
519
520 PROCEDURE add_row (
521 x_rowid IN OUT NOCOPY VARCHAR2,
522 x_anc_rate_segment_id IN OUT NOCOPY NUMBER,
523 x_fee_type IN VARCHAR2,
524 x_fee_cal_type IN VARCHAR2,
525 x_fee_ci_sequence_number IN NUMBER,
526 x_ancillary_attributes IN OUT NOCOPY VARCHAR2,
527 x_ancillary_segments IN VARCHAR2,
528 x_enabled_flag IN VARCHAR2,
529 x_mode IN VARCHAR2
530 ) AS
531 /*
532 || Created By : [email protected]
533 || Created On : 09-APR-2001
534 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
535 || Known limitations, enhancements or remarks :
536 || Change History :
537 || Who When What
538 || (reverse chronological order - newest change first)
539 */
540 CURSOR c1 IS
541 SELECT rowid
542 FROM igs_fi_anc_rt_sgmnts
543 WHERE anc_rate_segment_id = x_anc_rate_segment_id;
544
545 BEGIN
546
547 OPEN c1;
548 FETCH c1 INTO x_rowid;
549 IF (c1%NOTFOUND) THEN
550 CLOSE c1;
551
552 insert_row (
553 x_rowid,
554 x_anc_rate_segment_id,
555 x_fee_type,
556 x_fee_cal_type,
557 x_fee_ci_sequence_number,
558 x_ancillary_attributes,
559 x_ancillary_segments,
560 x_enabled_flag,
561 x_mode
562 );
563 RETURN;
564 END IF;
565 CLOSE c1;
566
567 update_row (
568 x_rowid,
569 x_anc_rate_segment_id,
570 x_fee_type,
571 x_fee_cal_type,
572 x_fee_ci_sequence_number,
573 x_ancillary_attributes,
574 x_ancillary_segments,
575 x_enabled_flag,
576 x_mode
577 );
578
579 l_rowid := NULL;
580
581 END add_row;
582
583
584 PROCEDURE delete_row (
585 x_rowid IN VARCHAR2
586 ) AS
587 /*
588 || Created By : [email protected]
589 || Created On : 09-APR-2001
590 || Purpose : Handles the DELETE DML logic for the table.
591 || Known limitations, enhancements or remarks :
592 || Change History :
593 || Who When What
594 || (reverse chronological order - newest change first)
595 */
596 BEGIN
597
598 before_dml (
599 p_action => 'DELETE',
600 x_rowid => x_rowid
601 );
602
603 DELETE FROM igs_fi_anc_rt_sgmnts
604 WHERE rowid = x_rowid;
605
606 IF (SQL%NOTFOUND) THEN
607 RAISE NO_DATA_FOUND;
608 END IF;
609
610 l_rowid := NULL;
611
612 END delete_row;
613
614
615 END igs_fi_anc_rt_sgmnts_pkg;
[Home] [Help]