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