[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_S_LTR_PKG
Source
1 PACKAGE BODY igs_co_s_ltr_pkg AS
2 /* $Header: IGSLI16B.pls 115.8 2002/11/29 01:06:22 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_s_ltr%ROWTYPE;
6 new_references igs_co_s_ltr%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
12 x_letter_reference_number IN NUMBER DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_s_letter_reference_type IN VARCHAR2 DEFAULT NULL,
15 x_s_letter_object IN VARCHAR2 DEFAULT NULL,
16 x_template_filename IN VARCHAR2 DEFAULT NULL,
17 x_letter_title IN VARCHAR2 DEFAULT NULL,
18 x_closed_ind IN VARCHAR2 DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL
24 ) AS
25 /*
26 || Created By : [email protected]
27 || Created On : 14-DEC-2000
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_CO_S_LTR
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.correspondence_type := x_correspondence_type;
59 new_references.letter_reference_number := x_letter_reference_number;
60 new_references.description := x_description;
61 new_references.s_letter_reference_type := x_s_letter_reference_type;
62 new_references.s_letter_object := x_s_letter_object;
63 new_references.template_filename := x_template_filename;
64 new_references.letter_title := x_letter_title;
65 new_references.closed_ind := x_closed_ind;
66
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END set_column_values;
80
81
82 PROCEDURE check_constraints (
83 column_name IN VARCHAR2 DEFAULT NULL,
84 column_value IN VARCHAR2 DEFAULT NULL
85 ) AS
86 /*
87 || Created By : [email protected]
88 || Created On : 14-DEC-2000
89 || Purpose : Handles the Check Constraint logic for the the columns.
90 || Known limitations, enhancements or remarks :
91 || Change History :
92 || Who When What
93 || (reverse chronological order - newest change first)
94 */
95 BEGIN
96
97 IF (column_name IS NULL) THEN
98 NULL;
99 ELSIF (UPPER(column_name) = 'CLOSED_IND') THEN
100 new_references.closed_ind := column_value;
101 END IF;
102
103 IF (UPPER(column_name) = 'CLOSED_IND' OR column_name IS NULL) THEN
104 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
105 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
106 igs_ge_msg_stack.add;
107 app_exception.raise_exception;
108 END IF;
109 END IF;
110
111 END check_constraints;
112
113 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW_LETOBJ(
114 x_s_letter_object IN VARCHAR2
115 ) AS
116
117 CURSOR cur_rowid IS
118 SELECT rowid
119 FROM IGS_CO_S_LTR
120 WHERE s_letter_object = x_s_letter_object ;
121
122 lv_rowid cur_rowid%RowType;
123
124 BEGIN
125
126 Open cur_rowid;
127 Fetch cur_rowid INTO lv_rowid;
128 IF (cur_rowid%FOUND) THEN
129 Close cur_rowid;
130 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DA_LKUP_FK');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 Return;
134 END IF;
135 Close cur_rowid;
136
137 END GET_FK_IGS_LOOKUPS_VIEW_LETOBJ;
138
139 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW_LETREF (
140 x_s_letter_reference_type IN VARCHAR2
141 ) AS
142
143 CURSOR cur_rowid IS
144 SELECT rowid
145 FROM IGS_CO_S_LTR
146 WHERE s_letter_reference_type = x_s_letter_reference_type ;
147
148 lv_rowid cur_rowid%RowType;
149
150 BEGIN
151
152 Open cur_rowid;
153 Fetch cur_rowid INTO lv_rowid;
154 IF (cur_rowid%FOUND) THEN
155 Close cur_rowid;
156 Fnd_Message.Set_Name ('IGS', 'IGS_CA_DA_LKUP_FK');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 Return;
160 END IF;
161 Close cur_rowid;
162
163 END GET_FK_IGS_LOOKUPS_VIEW_LETREF;
164
165 -- Trigger description :-
166 -- "OSS_TST".trg_slet_br_iu
167 -- BEFORE INSERT OR UPDATE
168 -- ON IGS_CO_S_LTR
169 -- FOR EACH ROW
170
171 PROCEDURE BeforeRowInsertUpdate1(
172 p_inserting IN BOOLEAN DEFAULT FALSE,
173 p_updating IN BOOLEAN DEFAULT FALSE,
174 p_deleting IN BOOLEAN DEFAULT FALSE
175 ) AS
176 v_message_name varchar2(30);
177 BEGIN
178 IF p_inserting THEN
179 -- Validate Correspondence Type closed.
180 IF igs_ad_val_aal.corp_val_cort_closed(
181 new_references.correspondence_type,
182 v_message_name) = FALSE THEN
183 Fnd_Message.Set_Name('IGS',v_message_name);
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END IF;
187 -- Validate Correspondence Type is system generated.
188 IF IGS_CO_VAL_SLET.corp_val_cort_sysgen(
189 new_references.correspondence_type,
190 v_message_name) = FALSE THEN
191 Fnd_Message.Set_Name('IGS',v_message_name);
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 END IF;
196 -- Validate System Letter Object closed.
197 IF p_inserting OR
198 (p_updating AND
199 new_references.s_letter_object <> old_references.s_letter_object) THEN
200 IF IGS_CO_VAL_SLET.corp_val_slo_closed(
201 new_references.s_letter_object,
202 v_message_name) = FALSE THEN
203 Fnd_Message.Set_Name('IGS',v_message_name);
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END IF;
207 END IF;
208
209
210 END BeforeRowInsertUpdate1;
211
212
213
214 PROCEDURE check_parent_existance AS
215 /*
216 || Created By : [email protected]
217 || Created On : 14-DEC-2000
218 || Purpose : Checks for the existance of Parent records.
219 || Known limitations, enhancements or remarks :
220 || Change History :
221 || Who When What
222 || (reverse chronological order - newest change first)
223 */
224 BEGIN
225
226 IF (((old_references.correspondence_type = new_references.correspondence_type)) OR
227 ((new_references.correspondence_type IS NULL))) THEN
228 NULL;
229 ELSIF NOT igs_co_type_pkg.get_pk_for_validation (
230 new_references.correspondence_type
231 ) THEN
232 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
233 igs_ge_msg_stack.add;
234 app_exception.raise_exception;
235 END IF;
236
237 END check_parent_existance;
238
239
240 PROCEDURE check_child_existance IS
241 /*
242 || Created By : [email protected]
243 || Created On : 14-DEC-2000
244 || Purpose : Checks for the existance of Child records.
245 || Known limitations, enhancements or remarks :
246 || Change History :
247 || Who When What
248 || (reverse chronological order - newest change first)
249 */
250 BEGIN
251
252 igs_co_ltr_param_pkg.get_fk_igs_co_s_ltr (
253 old_references.correspondence_type,
254 old_references.letter_reference_number
255 );
256
257 igs_co_ltr_rpt_grp_pkg.get_fk_igs_co_s_ltr (
258 old_references.correspondence_type,
259 old_references.letter_reference_number
260 );
261
262 END check_child_existance;
263
264
265 FUNCTION get_pk_for_validation (
266 x_correspondence_type IN VARCHAR2,
267 x_letter_reference_number IN NUMBER
268 ) RETURN BOOLEAN AS
269 /*
270 || Created By : [email protected]
271 || Created On : 14-DEC-2000
272 || Purpose : Validates the Primary Key of the table.
273 || Known limitations, enhancements or remarks :
274 || Change History :
275 || Who When What
276 || (reverse chronological order - newest change first)
277 */
278 CURSOR cur_rowid IS
279 SELECT rowid
280 FROM igs_co_s_ltr
281 WHERE correspondence_type = x_correspondence_type
282 AND letter_reference_number = x_letter_reference_number
283 FOR UPDATE NOWAIT;
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 OPEN cur_rowid;
290 FETCH cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 CLOSE cur_rowid;
293 RETURN(TRUE);
294 ELSE
295 CLOSE cur_rowid;
296 RETURN(FALSE);
297 END IF;
298
299 END get_pk_for_validation;
300
301
302 PROCEDURE get_fk_igs_co_type (
303 x_correspondence_type IN VARCHAR2
304 ) AS
305 /*
306 || Created By : [email protected]
307 || Created On : 14-DEC-2000
308 || Purpose : Validates the Foreign Keys for the table.
309 || Known limitations, enhancements or remarks :
310 || Change History :
311 || Who When What
312 || (reverse chronological order - newest change first)
313 */
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM igs_co_s_ltr
317 WHERE ((correspondence_type = x_correspondence_type));
318
319 lv_rowid cur_rowid%RowType;
320
321 BEGIN
322
323 OPEN cur_rowid;
324 FETCH cur_rowid INTO lv_rowid;
325 IF (cur_rowid%FOUND) THEN
326 CLOSE cur_rowid;
327 fnd_message.set_name ('IGS', 'IGS_CO_CORT_SLET_FK');
328 igs_ge_msg_stack.add;
329 app_exception.raise_exception;
330 RETURN;
331 END IF;
332 CLOSE cur_rowid;
333
334 END get_fk_igs_co_type;
335
336
337 PROCEDURE before_dml (
338 p_action IN VARCHAR2,
339 x_rowid IN VARCHAR2 DEFAULT NULL,
340 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
341 x_letter_reference_number IN NUMBER DEFAULT NULL,
342 x_description IN VARCHAR2 DEFAULT NULL,
343 x_s_letter_reference_type IN VARCHAR2 DEFAULT NULL,
344 x_s_letter_object IN VARCHAR2 DEFAULT NULL,
345 x_template_filename IN VARCHAR2 DEFAULT NULL,
346 x_letter_title IN VARCHAR2 DEFAULT NULL,
347 x_closed_ind IN VARCHAR2 DEFAULT NULL,
348 x_creation_date IN DATE DEFAULT NULL,
349 x_created_by IN NUMBER DEFAULT NULL,
350 x_last_update_date IN DATE DEFAULT NULL,
351 x_last_updated_by IN NUMBER DEFAULT NULL,
352 x_last_update_login IN NUMBER DEFAULT NULL
353 ) AS
354 /*
355 || Created By : [email protected]
356 || Created On : 14-DEC-2000
357 || Purpose : Initialises the columns, Checks Constraints, Calls the
358 || Trigger Handlers for the table, before any DML operation.
359 || Known limitations, enhancements or remarks :
360 || Change History :
361 || Who When What
362 || (reverse chronological order - newest change first)
363 */
364 BEGIN
365
366 set_column_values (
367 p_action,
368 x_rowid,
369 x_correspondence_type,
370 x_letter_reference_number,
371 x_description,
372 x_s_letter_reference_type,
373 x_s_letter_object,
374 x_template_filename,
375 x_letter_title,
376 x_closed_ind,
377 x_creation_date,
378 x_created_by,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_login
382 );
383
384 IF (p_action = 'INSERT') THEN
385 -- Call all the procedures related to Before Insert.
386 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
387
388 IF ( get_pk_for_validation(
389 new_references.correspondence_type,
390 new_references.letter_reference_number
391 )
392 ) THEN
393 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
394 igs_ge_msg_stack.add;
395 app_exception.raise_exception;
396 END IF;
397 check_constraints;
398 check_parent_existance;
399 ELSIF (p_action = 'UPDATE') THEN
400 -- Call all the procedures related to Before Update.
401 BeforeRowInsertUpdate1 ( p_updating => TRUE );
402 check_constraints;
403 check_parent_existance;
404 ELSIF (p_action = 'DELETE') THEN
405 -- Call all the procedures related to Before Delete.
406 check_child_existance;
407 ELSIF (p_action = 'VALIDATE_INSERT') THEN
408 -- Call all the procedures related to Before Insert.
409 IF ( get_pk_for_validation (
410 new_references.correspondence_type,
411 new_references.letter_reference_number
412 )
413 ) THEN
414 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
415 igs_ge_msg_stack.add;
416 app_exception.raise_exception;
417 END IF;
418 check_constraints;
419 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
420 check_constraints;
421 ELSIF (p_action = 'VALIDATE_DELETE') THEN
422 check_child_existance;
423 END IF;
424
425 END before_dml;
426
427
428 PROCEDURE insert_row (
429 x_rowid IN OUT NOCOPY VARCHAR2,
430 x_correspondence_type IN VARCHAR2,
431 x_letter_reference_number IN NUMBER,
432 x_description IN VARCHAR2,
433 x_s_letter_reference_type IN VARCHAR2,
434 x_s_letter_object IN VARCHAR2,
435 x_template_filename IN VARCHAR2,
436 x_letter_title IN VARCHAR2,
437 x_closed_ind IN VARCHAR2,
438 x_mode IN VARCHAR2 DEFAULT 'R'
439 ) AS
440 /*
441 || Created By : [email protected]
442 || Created On : 14-DEC-2000
443 || Purpose : Handles the INSERT DML logic for the table.
444 || Known limitations, enhancements or remarks :
445 || Change History :
446 || Who When What
447 || (reverse chronological order - newest change first)
448 */
449 CURSOR c IS
450 SELECT rowid
451 FROM igs_co_s_ltr
452 WHERE correspondence_type = x_correspondence_type
453 AND letter_reference_number = x_letter_reference_number;
454
455 x_last_update_date DATE;
456 x_last_updated_by NUMBER;
457 x_last_update_login NUMBER;
458
459 BEGIN
460
461 x_last_update_date := SYSDATE;
462 IF (x_mode = 'I') THEN
463 x_last_updated_by := 1;
464 x_last_update_login := 0;
465 ELSIF (x_mode = 'R') THEN
466 x_last_updated_by := fnd_global.user_id;
467 IF (x_last_updated_by IS NULL) THEN
468 x_last_updated_by := -1;
469 END IF;
470 x_last_update_login := fnd_global.login_id;
471 IF (x_last_update_login IS NULL) THEN
472 x_last_update_login := -1;
473 END IF;
474 ELSE
475 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
476 igs_ge_msg_stack.add;
477 app_exception.raise_exception;
478 END IF;
479
480 before_dml(
481 p_action => 'INSERT',
482 x_rowid => x_rowid,
483 x_correspondence_type => x_correspondence_type,
484 x_letter_reference_number => x_letter_reference_number,
485 x_description => x_description,
486 x_s_letter_reference_type => x_s_letter_reference_type,
487 x_s_letter_object => x_s_letter_object,
488 x_template_filename => x_template_filename,
489 x_letter_title => x_letter_title,
490 x_closed_ind => NVL (x_closed_ind,'N' ),
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 INSERT INTO igs_co_s_ltr (
499 correspondence_type,
500 letter_reference_number,
501 description,
502 s_letter_reference_type,
503 s_letter_object,
504 template_filename,
505 letter_title,
506 closed_ind,
507 creation_date,
508 created_by,
509 last_update_date,
510 last_updated_by,
511 last_update_login
512 ) VALUES (
513 new_references.correspondence_type,
514 new_references.letter_reference_number,
515 new_references.description,
516 new_references.s_letter_reference_type,
517 new_references.s_letter_object,
518 new_references.template_filename,
519 new_references.letter_title,
520 new_references.closed_ind,
521 x_last_update_date,
522 x_last_updated_by,
523 x_last_update_date,
524 x_last_updated_by,
525 x_last_update_login
526 );
527
528 OPEN c;
529 FETCH c INTO x_rowid;
530 IF (c%NOTFOUND) THEN
531 CLOSE c;
532 RAISE NO_DATA_FOUND;
533 END IF;
534 CLOSE c;
535
536 END insert_row;
537
538
539 PROCEDURE lock_row (
540 x_rowid IN VARCHAR2,
541 x_correspondence_type IN VARCHAR2,
542 x_letter_reference_number IN NUMBER,
543 x_description IN VARCHAR2,
544 x_s_letter_reference_type IN VARCHAR2,
545 x_s_letter_object IN VARCHAR2,
546 x_template_filename IN VARCHAR2,
547 x_letter_title IN VARCHAR2,
548 x_closed_ind IN VARCHAR2
549 ) AS
550 /*
551 || Created By : [email protected]
552 || Created On : 14-DEC-2000
553 || Purpose : Handles the LOCK mechanism for 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
561 description,
562 s_letter_reference_type,
563 s_letter_object,
564 template_filename,
565 letter_title,
566 closed_ind
567 FROM igs_co_s_ltr
568 WHERE rowid = x_rowid
569 FOR UPDATE NOWAIT;
570
571 tlinfo c1%ROWTYPE;
572
573 BEGIN
574
575 OPEN c1;
576 FETCH c1 INTO tlinfo;
577 IF (c1%notfound) THEN
578 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
579 igs_ge_msg_stack.add;
580 CLOSE c1;
581 app_exception.raise_exception;
582 RETURN;
583 END IF;
584 CLOSE c1;
585
586 IF (
587 (tlinfo.description = x_description)
588 AND (tlinfo.s_letter_reference_type = x_s_letter_reference_type)
589 AND (tlinfo.s_letter_object = x_s_letter_object)
590 AND ((tlinfo.template_filename = x_template_filename) OR ((tlinfo.template_filename IS NULL) AND (X_template_filename IS NULL)))
591 AND (tlinfo.letter_title = x_letter_title)
592 AND (tlinfo.closed_ind = x_closed_ind)
593 ) THEN
594 NULL;
595 ELSE
596 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
597 igs_ge_msg_stack.add;
598 app_exception.raise_exception;
599 END IF;
600
601 RETURN;
602
603 END lock_row;
604
605
606 PROCEDURE update_row (
607 x_rowid IN VARCHAR2,
608 x_correspondence_type IN VARCHAR2,
609 x_letter_reference_number IN NUMBER,
610 x_description IN VARCHAR2,
611 x_s_letter_reference_type IN VARCHAR2,
612 x_s_letter_object IN VARCHAR2,
613 x_template_filename IN VARCHAR2,
614 x_letter_title IN VARCHAR2,
615 x_closed_ind IN VARCHAR2,
616 x_mode IN VARCHAR2 DEFAULT 'R'
617 ) AS
618 /*
619 || Created By : [email protected]
620 || Created On : 14-DEC-2000
621 || Purpose : Handles the UPDATE DML logic for the table.
622 || Known limitations, enhancements or remarks :
623 || Change History :
624 || Who When What
625 || (reverse chronological order - newest change first)
626 */
627 x_last_update_date DATE ;
628 x_last_updated_by NUMBER;
629 x_last_update_login NUMBER;
630
631 BEGIN
632
633 x_last_update_date := SYSDATE;
634 IF (X_MODE = 'I') THEN
635 x_last_updated_by := 1;
636 x_last_update_login := 0;
637 ELSIF (x_mode = 'R') THEN
638 x_last_updated_by := fnd_global.user_id;
639 IF x_last_updated_by IS NULL THEN
640 x_last_updated_by := -1;
641 END IF;
642 x_last_update_login := fnd_global.login_id;
643 IF (x_last_update_login IS NULL) THEN
644 x_last_update_login := -1;
645 END IF;
646 ELSE
647 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
648 igs_ge_msg_stack.add;
649 app_exception.raise_exception;
650 END IF;
651
652 before_dml(
653 p_action => 'UPDATE',
654 x_rowid => x_rowid,
655 x_correspondence_type => x_correspondence_type,
656 x_letter_reference_number => x_letter_reference_number,
657 x_description => x_description,
658 x_s_letter_reference_type => x_s_letter_reference_type,
659 x_s_letter_object => x_s_letter_object,
660 x_template_filename => x_template_filename,
661 x_letter_title => x_letter_title,
662 x_closed_ind => NVL (x_closed_ind,'N' ),
663 x_creation_date => x_last_update_date,
664 x_created_by => x_last_updated_by,
665 x_last_update_date => x_last_update_date,
666 x_last_updated_by => x_last_updated_by,
667 x_last_update_login => x_last_update_login
668 );
669
670 UPDATE igs_co_s_ltr
671 SET
672 description = new_references.description,
673 s_letter_reference_type = new_references.s_letter_reference_type,
674 s_letter_object = new_references.s_letter_object,
675 template_filename = new_references.template_filename,
676 letter_title = new_references.letter_title,
677 closed_ind = new_references.closed_ind,
678 last_update_date = x_last_update_date,
679 last_updated_by = x_last_updated_by,
680 last_update_login = x_last_update_login
681 WHERE rowid = x_rowid;
682
683 IF (SQL%NOTFOUND) THEN
684 RAISE NO_DATA_FOUND;
685 END IF;
686
687 END update_row;
688
689
690 PROCEDURE add_row (
691 x_rowid IN OUT NOCOPY VARCHAR2,
692 x_correspondence_type IN VARCHAR2,
693 x_letter_reference_number IN NUMBER,
694 x_description IN VARCHAR2,
695 x_s_letter_reference_type IN VARCHAR2,
696 x_s_letter_object IN VARCHAR2,
697 x_template_filename IN VARCHAR2,
698 x_letter_title IN VARCHAR2,
699 x_closed_ind IN VARCHAR2,
700 x_mode IN VARCHAR2 DEFAULT 'R'
701 ) AS
702 /*
703 || Created By : [email protected]
704 || Created On : 14-DEC-2000
705 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
706 || Known limitations, enhancements or remarks :
707 || Change History :
708 || Who When What
709 || (reverse chronological order - newest change first)
710 */
711 CURSOR c1 IS
712 SELECT rowid
713 FROM igs_co_s_ltr
714 WHERE correspondence_type = x_correspondence_type
715 AND letter_reference_number = x_letter_reference_number;
716
717 BEGIN
718
719 OPEN c1;
720 FETCH c1 INTO x_rowid;
721 IF (c1%NOTFOUND) THEN
722 CLOSE c1;
723
724 insert_row (
725 x_rowid,
726 x_correspondence_type,
727 x_letter_reference_number,
728 x_description,
729 x_s_letter_reference_type,
730 x_s_letter_object,
731 x_template_filename,
732 x_letter_title,
733 x_closed_ind,
734 x_mode
735 );
736 RETURN;
737 END IF;
738 CLOSE c1;
739
740 update_row (
741 x_rowid,
742 x_correspondence_type,
743 x_letter_reference_number,
744 x_description,
745 x_s_letter_reference_type,
746 x_s_letter_object,
747 x_template_filename,
748 x_letter_title,
749 x_closed_ind,
750 x_mode
751 );
752
753 END add_row;
754
755
756 PROCEDURE delete_row (
757 x_rowid IN VARCHAR2
758 ) AS
759 /*
760 || Created By : [email protected]
761 || Created On : 14-DEC-2000
762 || Purpose : Handles the DELETE DML logic for the table.
763 || Known limitations, enhancements or remarks :
764 || Change History :
765 || Who When What
766 || (reverse chronological order - newest change first)
767 */
768 BEGIN
769
770 before_dml (
771 p_action => 'DELETE',
772 x_rowid => x_rowid
773 );
774
775 DELETE FROM igs_co_s_ltr
776 WHERE rowid = x_rowid;
777
778 IF (SQL%NOTFOUND) THEN
779 RAISE NO_DATA_FOUND;
780 END IF;
781
782 END delete_row;
783
784
785 END igs_co_s_ltr_pkg;