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