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