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