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