1 PACKAGE BODY igs_co_s_perlt_rptgp_pkg AS
2 /* $Header: IGSLI27B.pls 115.2 2002/11/29 01:08:45 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_s_perlt_rptgp_all%ROWTYPE;
6 new_references igs_co_s_perlt_rptgp_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_person_id IN NUMBER DEFAULT NULL,
13 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
14 x_letter_reference_number IN NUMBER DEFAULT NULL,
15 x_spl_sequence_number IN NUMBER DEFAULT NULL,
16 x_letter_repeating_group_cd IN VARCHAR2 DEFAULT NULL,
17 x_sequence_number IN NUMBER DEFAULT NULL,
18 x_sup_repeating_group_cd IN VARCHAR2 DEFAULT NULL,
19 x_sup_splrg_sequence_number IN NUMBER DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 /*
27 || Created By : [email protected]
28 || Created On : 14-DEC-2000
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM IGS_CO_S_PERLT_RPTGP_ALL
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.org_id := x_org_id;
60 new_references.person_id := x_person_id;
61 new_references.correspondence_type := x_correspondence_type;
62 new_references.letter_reference_number := x_letter_reference_number;
63 new_references.spl_sequence_number := x_spl_sequence_number;
64 new_references.letter_repeating_group_cd := x_letter_repeating_group_cd;
65 new_references.sequence_number := x_sequence_number;
66 new_references.sup_repeating_group_cd := x_sup_repeating_group_cd;
67 new_references.sup_splrg_sequence_number := x_sup_splrg_sequence_number;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 PROCEDURE check_constraints (
85 column_name IN VARCHAR2 DEFAULT NULL,
86 column_value IN VARCHAR2 DEFAULT NULL
87 ) AS
88 /*
89 || Created By : [email protected]
90 || Created On : 14-DEC-2000
91 || Purpose : Handles the Check Constraint logic for the the columns.
92 || Known limitations, enhancements or remarks :
93 || Change History :
94 || Who When What
95 || (reverse chronological order - newest change first)
96 */
97 BEGIN
98
99 IF (column_name IS NULL) THEN
100 NULL;
101 ELSIF (UPPER(column_name) = 'SPL_SEQUENCE_NUMBER') THEN
102 new_references.spl_sequence_number := igs_ge_number.to_num (column_value);
103 ELSIF (UPPER(column_name) = 'SUP_SPLRG_SEQUENCE_NUMBER') THEN
104 new_references.sup_splrg_sequence_number := igs_ge_number.to_num (column_value);
105 END IF;
106
107 IF (UPPER(column_name) = 'SPL_SEQUENCE_NUMBER' OR column_name IS NULL) THEN
108 IF NOT (new_references.spl_sequence_number BETWEEN 1
109 AND 9999999999) THEN
110 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
111 igs_ge_msg_stack.add;
112 app_exception.raise_exception;
113 END IF;
114 END IF;
115
116 IF (UPPER(column_name) = 'SEQUENCE_NUMBER' OR column_name IS NULL) THEN
117 IF NOT (new_references.sequence_number BETWEEN 1
118 AND 9999999999) THEN
119 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123 END IF;
124
125 IF (UPPER(column_name) = 'SUP_SPLRG_SEQUENCE_NUMBER' OR column_name IS NULL) THEN
126 IF NOT (new_references.sup_splrg_sequence_number BETWEEN 1
127 AND 9999999999) THEN
128 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
129 igs_ge_msg_stack.add;
130 app_exception.raise_exception;
131 END IF;
132 END IF;
133
134 END check_constraints;
135
136
137 PROCEDURE check_parent_existance AS
138 /*
139 || Created By : [email protected]
140 || Created On : 14-DEC-2000
141 || Purpose : Checks for the existance of Parent records.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149 IF (((old_references.person_id = new_references.person_id) AND
150 (old_references.correspondence_type = new_references.correspondence_type) AND
151 (old_references.letter_reference_number = new_references.letter_reference_number) AND
152 (old_references.spl_sequence_number = new_references.spl_sequence_number) AND
153 (old_references.sup_repeating_group_cd = new_references.sup_repeating_group_cd) AND
154 (old_references.sup_splrg_sequence_number = new_references.sup_splrg_sequence_number)) OR
155 ((new_references.person_id IS NULL) OR
156 (new_references.correspondence_type IS NULL) OR
157 (new_references.letter_reference_number IS NULL) OR
158 (new_references.spl_sequence_number IS NULL) OR
159 (new_references.sup_repeating_group_cd IS NULL) OR
160 (new_references.sup_splrg_sequence_number IS NULL))) THEN
161 NULL;
162 ELSIF NOT igs_co_s_perlt_rptgp_pkg.get_pk_for_validation (
163 new_references.person_id,
164 new_references.correspondence_type,
165 new_references.letter_reference_number,
166 new_references.spl_sequence_number,
167 new_references.sup_repeating_group_cd,
168 new_references.sup_splrg_sequence_number
169 ) THEN
170 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
171 igs_ge_msg_stack.add;
172 app_exception.raise_exception;
173 END IF;
174
175 IF (((old_references.person_id = new_references.person_id) AND
176 (old_references.correspondence_type = new_references.correspondence_type) AND
177 (old_references.letter_reference_number = new_references.letter_reference_number) AND
178 (old_references.spl_sequence_number = new_references.spl_sequence_number)) OR
179 ((new_references.person_id IS NULL) OR
180 (new_references.correspondence_type IS NULL) OR
181 (new_references.letter_reference_number IS NULL) OR
182 (new_references.spl_sequence_number IS NULL))) THEN
183 NULL;
184 ELSIF NOT igs_co_s_per_ltr_pkg.get_pk_for_validation (
185 new_references.person_id,
186 new_references.correspondence_type,
187 new_references.letter_reference_number,
188 new_references.spl_sequence_number
189 ) THEN
190 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
191 igs_ge_msg_stack.add;
192 app_exception.raise_exception;
193 END IF;
194
195 IF (((old_references.correspondence_type = new_references.correspondence_type) AND
196 (old_references.letter_reference_number = new_references.letter_reference_number) AND
197 (old_references.letter_repeating_group_cd = new_references.letter_repeating_group_cd)) OR
198 ((new_references.correspondence_type IS NULL) OR
199 (new_references.letter_reference_number IS NULL) OR
200 (new_references.letter_repeating_group_cd IS NULL))) THEN
201 NULL;
202 ELSIF NOT igs_co_ltr_rpt_grp_pkg.get_pk_for_validation (
203 new_references.correspondence_type,
204 new_references.letter_reference_number,
205 new_references.letter_repeating_group_cd
206 ) THEN
207 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
208 igs_ge_msg_stack.add;
209 app_exception.raise_exception;
210 END IF;
211
212 END check_parent_existance;
213
214
215 PROCEDURE check_child_existance IS
216 /*
217 || Created By : [email protected]
218 || Created On : 14-DEC-2000
219 || Purpose : Checks for the existance of Child records.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 BEGIN
226
227 igs_co_s_perlt_rptgp_pkg.get_fk_igs_co_s_perlt_rptgp (
228 old_references.person_id,
229 old_references.correspondence_type,
230 old_references.letter_reference_number,
231 old_references.spl_sequence_number,
232 old_references.letter_repeating_group_cd,
233 old_references.sequence_number
234 );
235
236 igs_co_s_per_lt_parm_pkg.get_fk_igs_co_s_perlt_rptgp (
237 old_references.person_id,
238 old_references.correspondence_type,
239 old_references.letter_reference_number,
240 old_references.spl_sequence_number,
241 old_references.letter_repeating_group_cd,
242 old_references.sequence_number
243 );
244
245 END check_child_existance;
246
247
248 FUNCTION get_pk_for_validation (
249 x_person_id IN NUMBER,
250 x_correspondence_type IN VARCHAR2,
251 x_letter_reference_number IN NUMBER,
252 x_spl_sequence_number IN NUMBER,
253 x_letter_repeating_group_cd IN VARCHAR2,
254 x_sequence_number IN NUMBER
255 ) RETURN BOOLEAN AS
256 /*
257 || Created By : [email protected]
258 || Created On : 14-DEC-2000
259 || Purpose : Validates the Primary Key of the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
268 WHERE person_id = x_person_id
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM igs_co_s_perlt_rptgp_all
269 AND correspondence_type = x_correspondence_type
270 AND letter_reference_number = x_letter_reference_number
271 AND spl_sequence_number = x_spl_sequence_number
272 AND letter_repeating_group_cd = x_letter_repeating_group_cd
273 AND sequence_number = x_sequence_number
274 FOR UPDATE NOWAIT;
275
276 lv_rowid cur_rowid%RowType;
277
278 BEGIN
279
280 OPEN cur_rowid;
281 FETCH cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 CLOSE cur_rowid;
284 RETURN(TRUE);
285 ELSE
286 CLOSE cur_rowid;
287 RETURN(FALSE);
288 END IF;
289
290 END get_pk_for_validation;
291
292
293 PROCEDURE get_fk_igs_co_s_perlt_rptgp (
294 x_person_id IN NUMBER,
295 x_correspondence_type IN VARCHAR2,
296 x_letter_reference_number IN NUMBER,
297 x_spl_sequence_number IN NUMBER,
298 x_letter_repeating_group_cd IN VARCHAR2,
299 x_sequence_number IN NUMBER
300 ) AS
301 /*
302 || Created By : [email protected]
303 || Created On : 14-DEC-2000
304 || Purpose : Validates the Foreign Keys for the table.
305 || Known limitations, enhancements or remarks :
306 || Change History :
307 || Who When What
308 || (reverse chronological order - newest change first)
309 */
310 CURSOR cur_rowid IS
311 SELECT rowid
312 FROM igs_co_s_perlt_rptgp_all
313 WHERE ((person_id = x_person_id) AND
314 (correspondence_type = x_correspondence_type) AND
315 (letter_reference_number = x_letter_reference_number) AND
316 (spl_sequence_number = x_spl_sequence_number) AND
317 (sup_repeating_group_cd = x_letter_repeating_group_cd) AND
318 (sup_splrg_sequence_number = x_sequence_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_perlt_rptgp;
336
337
338 PROCEDURE get_fk_igs_co_s_per_ltr (
339 x_person_id IN NUMBER,
340 x_correspondence_type IN VARCHAR2,
341 x_letter_reference_number IN NUMBER,
342 x_sequence_number IN NUMBER
343 ) AS
344 /*
345 || Created By : [email protected]
346 || Created On : 14-DEC-2000
347 || Purpose : Validates the Foreign Keys for the table.
348 || Known limitations, enhancements or remarks :
349 || Change History :
350 || Who When What
351 || (reverse chronological order - newest change first)
352 */
353 CURSOR cur_rowid IS
354 SELECT rowid
355 FROM igs_co_s_perlt_rptgp_all
356 WHERE ((person_id = x_person_id) AND
357 (correspondence_type = x_correspondence_type) AND
358 (letter_reference_number = x_letter_reference_number) AND
359 (spl_sequence_number = x_sequence_number));
360
361 lv_rowid cur_rowid%RowType;
362
363 BEGIN
364
365 OPEN cur_rowid;
366 FETCH cur_rowid INTO lv_rowid;
367 IF (cur_rowid%FOUND) THEN
368 CLOSE cur_rowid;
369 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 RETURN;
373 END IF;
374 CLOSE cur_rowid;
375
376 END get_fk_igs_co_s_per_ltr;
377
378
382 x_letter_repeating_group_cd IN VARCHAR2
379 PROCEDURE get_fk_igs_co_ltr_rpt_grp (
380 x_correspondence_type IN VARCHAR2,
381 x_letter_reference_number IN NUMBER,
383 ) AS
384 /*
385 || Created By : [email protected]
386 || Created On : 14-DEC-2000
387 || Purpose : Validates the Foreign Keys for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393 CURSOR cur_rowid IS
394 SELECT rowid
395 FROM igs_co_s_perlt_rptgp_all
396 WHERE ((correspondence_type = x_correspondence_type) AND
397 (letter_reference_number = x_letter_reference_number) AND
398 (letter_repeating_group_cd = x_letter_repeating_group_cd));
399
400 lv_rowid cur_rowid%RowType;
401
402 BEGIN
403
404 OPEN cur_rowid;
405 FETCH cur_rowid INTO lv_rowid;
406 IF (cur_rowid%FOUND) THEN
407 CLOSE cur_rowid;
408 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
409 igs_ge_msg_stack.add;
410 app_exception.raise_exception;
411 RETURN;
412 END IF;
413 CLOSE cur_rowid;
414
415 END get_fk_igs_co_ltr_rpt_grp;
416
417
418 PROCEDURE before_dml (
419 p_action IN VARCHAR2,
420 x_rowid IN VARCHAR2 DEFAULT NULL,
421 x_org_id IN NUMBER DEFAULT NULL,
422 x_person_id IN NUMBER DEFAULT NULL,
423 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
424 x_letter_reference_number IN NUMBER DEFAULT NULL,
425 x_spl_sequence_number IN NUMBER DEFAULT NULL,
426 x_letter_repeating_group_cd IN VARCHAR2 DEFAULT NULL,
427 x_sequence_number IN NUMBER DEFAULT NULL,
428 x_sup_repeating_group_cd IN VARCHAR2 DEFAULT NULL,
429 x_sup_splrg_sequence_number IN NUMBER DEFAULT NULL,
430 x_creation_date IN DATE DEFAULT NULL,
431 x_created_by IN NUMBER DEFAULT NULL,
432 x_last_update_date IN DATE DEFAULT NULL,
433 x_last_updated_by IN NUMBER DEFAULT NULL,
434 x_last_update_login IN NUMBER DEFAULT NULL
435 ) AS
436 /*
437 || Created By : [email protected]
438 || Created On : 14-DEC-2000
439 || Purpose : Initialises the columns, Checks Constraints, Calls the
440 || Trigger Handlers for the table, before any DML operation.
441 || Known limitations, enhancements or remarks :
442 || Change History :
443 || Who When What
444 || (reverse chronological order - newest change first)
445 */
446 BEGIN
447
448 set_column_values (
449 p_action,
450 x_rowid,
451 x_org_id,
452 x_person_id,
453 x_correspondence_type,
454 x_letter_reference_number,
455 x_spl_sequence_number,
456 x_letter_repeating_group_cd,
457 x_sequence_number,
458 x_sup_repeating_group_cd,
459 x_sup_splrg_sequence_number,
460 x_creation_date,
461 x_created_by,
462 x_last_update_date,
463 x_last_updated_by,
464 x_last_update_login
465 );
466
467 IF (p_action = 'INSERT') THEN
468 -- Call all the procedures related to Before Insert.
469 IF ( get_pk_for_validation(
470 new_references.person_id,
471 new_references.correspondence_type,
472 new_references.letter_reference_number,
473 new_references.spl_sequence_number,
474 new_references.letter_repeating_group_cd,
475 new_references.sequence_number
476 )
477 ) THEN
478 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
479 igs_ge_msg_stack.add;
480 app_exception.raise_exception;
481 END IF;
482 check_constraints;
483 check_parent_existance;
484 ELSIF (p_action = 'UPDATE') THEN
485 -- Call all the procedures related to Before Update.
486 check_constraints;
487 check_parent_existance;
488 ELSIF (p_action = 'DELETE') THEN
489 -- Call all the procedures related to Before Delete.
490 check_child_existance;
491 ELSIF (p_action = 'VALIDATE_INSERT') THEN
492 -- Call all the procedures related to Before Insert.
493 IF ( get_pk_for_validation (
494 new_references.person_id,
495 new_references.correspondence_type,
496 new_references.letter_reference_number,
497 new_references.spl_sequence_number,
498 new_references.letter_repeating_group_cd,
499 new_references.sequence_number
500 )
501 ) THEN
502 fnd_message.set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
503 igs_ge_msg_stack.add;
504 app_exception.raise_exception;
505 END IF;
506 check_constraints;
507 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
508 check_constraints;
509 ELSIF (p_action = 'VALIDATE_DELETE') THEN
510 check_child_existance;
511 END IF;
512
513 END before_dml;
514
515
516 PROCEDURE insert_row (
517 x_rowid IN OUT NOCOPY VARCHAR2,
518 x_org_id IN NUMBER,
519 x_person_id IN NUMBER,
520 x_correspondence_type IN VARCHAR2,
521 x_letter_reference_number IN NUMBER,
522 x_spl_sequence_number IN NUMBER,
523 x_letter_repeating_group_cd IN VARCHAR2,
524 x_sequence_number IN NUMBER,
525 x_sup_repeating_group_cd IN VARCHAR2,
526 x_sup_splrg_sequence_number IN NUMBER,
527 x_mode IN VARCHAR2 DEFAULT 'R'
528 ) AS
529 /*
530 || Created By : [email protected]
531 || Created On : 14-DEC-2000
535 || Who When What
532 || Purpose : Handles the INSERT DML logic for the table.
533 || Known limitations, enhancements or remarks :
534 || Change History :
536 || (reverse chronological order - newest change first)
537 */
538 CURSOR c IS
539 SELECT rowid
540 FROM igs_co_s_perlt_rptgp_all
541 WHERE person_id = x_person_id
542 AND correspondence_type = x_correspondence_type
543 AND letter_reference_number = x_letter_reference_number
544 AND spl_sequence_number = x_spl_sequence_number
545 AND letter_repeating_group_cd = x_letter_repeating_group_cd
546 AND sequence_number = x_sequence_number;
547
548 x_last_update_date DATE;
549 x_last_updated_by NUMBER;
550 x_last_update_login NUMBER;
551 x_request_id NUMBER;
552 x_program_id NUMBER;
553 x_program_application_id NUMBER;
554 x_program_update_date DATE;
555
556 BEGIN
557
558 x_last_update_date := SYSDATE;
559 IF (x_mode = 'I') THEN
560 x_last_updated_by := 1;
561 x_last_update_login := 0;
562 ELSIF (x_mode = 'R') THEN
563 x_last_updated_by := fnd_global.user_id;
564 IF (x_last_updated_by IS NULL) THEN
565 x_last_updated_by := -1;
566 END IF;
567 x_last_update_login := fnd_global.login_id;
568 IF (x_last_update_login IS NULL) THEN
569 x_last_update_login := -1;
570 END IF;
571 x_request_id := fnd_global.conc_request_id;
572 x_program_id := fnd_global.conc_program_id;
573 x_program_application_id := fnd_global.prog_appl_id;
574
575 IF (x_request_id = -1) THEN
576 x_request_id := NULL;
577 x_program_id := NULL;
578 x_program_application_id := NULL;
579 x_program_update_date := NULL;
580 ELSE
581 x_program_update_date := SYSDATE;
582 END IF;
583 ELSE
584 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
585 igs_ge_msg_stack.add;
586 app_exception.raise_exception;
587 END IF;
588
589 before_dml(
590 p_action => 'INSERT',
591 x_rowid => x_rowid,
592 x_org_id => igs_ge_gen_003.get_org_id,
593 x_person_id => x_person_id,
594 x_correspondence_type => x_correspondence_type,
595 x_letter_reference_number => x_letter_reference_number,
596 x_spl_sequence_number => x_spl_sequence_number,
597 x_letter_repeating_group_cd => x_letter_repeating_group_cd,
598 x_sequence_number => x_sequence_number,
599 x_sup_repeating_group_cd => x_sup_repeating_group_cd,
600 x_sup_splrg_sequence_number => x_sup_splrg_sequence_number,
601 x_creation_date => x_last_update_date,
602 x_created_by => x_last_updated_by,
603 x_last_update_date => x_last_update_date,
604 x_last_updated_by => x_last_updated_by,
605 x_last_update_login => x_last_update_login
606 );
607
608 INSERT INTO igs_co_s_perlt_rptgp_all (
609 org_id,
610 person_id,
611 correspondence_type,
612 letter_reference_number,
613 spl_sequence_number,
614 letter_repeating_group_cd,
615 sequence_number,
616 sup_repeating_group_cd,
617 sup_splrg_sequence_number,
618 creation_date,
619 created_by,
620 last_update_date,
621 last_updated_by,
622 last_update_login,
623 request_id,
624 program_id,
625 program_application_id,
626 program_update_date
627 ) VALUES (
628 new_references.org_id,
629 new_references.person_id,
630 new_references.correspondence_type,
631 new_references.letter_reference_number,
632 new_references.spl_sequence_number,
633 new_references.letter_repeating_group_cd,
634 new_references.sequence_number,
635 new_references.sup_repeating_group_cd,
636 new_references.sup_splrg_sequence_number,
637 x_last_update_date,
638 x_last_updated_by,
639 x_last_update_date,
640 x_last_updated_by,
641 x_last_update_login ,
642 x_request_id,
643 x_program_id,
644 x_program_application_id,
645 x_program_update_date
646 );
647
648 OPEN c;
649 FETCH c INTO x_rowid;
650 IF (c%NOTFOUND) THEN
651 CLOSE c;
652 RAISE NO_DATA_FOUND;
653 END IF;
654 CLOSE c;
655
656 END insert_row;
657
658
659 PROCEDURE lock_row (
660 x_rowid IN VARCHAR2,
661 x_person_id IN NUMBER,
662 x_correspondence_type IN VARCHAR2,
663 x_letter_reference_number IN NUMBER,
664 x_spl_sequence_number IN NUMBER,
665 x_letter_repeating_group_cd IN VARCHAR2,
666 x_sequence_number IN NUMBER,
667 x_sup_repeating_group_cd IN VARCHAR2,
668 x_sup_splrg_sequence_number IN NUMBER
669 ) AS
670 /*
671 || Created By : [email protected]
672 || Created On : 14-DEC-2000
673 || Purpose : Handles the LOCK mechanism for the table.
674 || Known limitations, enhancements or remarks :
678 */
675 || Change History :
676 || Who When What
677 || (reverse chronological order - newest change first)
679 CURSOR c1 IS
680 SELECT
681 sup_repeating_group_cd,
682 sup_splrg_sequence_number
683 FROM igs_co_s_perlt_rptgp_all
684 WHERE rowid = x_rowid
685 FOR UPDATE NOWAIT;
686
687 tlinfo c1%ROWTYPE;
688
689 BEGIN
690
691 OPEN c1;
692 FETCH c1 INTO tlinfo;
693 IF (c1%notfound) THEN
694 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
695 igs_ge_msg_stack.add;
696 CLOSE c1;
697 app_exception.raise_exception;
698 RETURN;
699 END IF;
700 CLOSE c1;
701
702 IF (
703 ((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)))
704 AND ((tlinfo.sup_splrg_sequence_number = x_sup_splrg_sequence_number) OR ((tlinfo.sup_splrg_sequence_number IS NULL) AND (X_sup_splrg_sequence_number IS NULL)))
705 ) THEN
706 NULL;
707 ELSE
708 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
709 igs_ge_msg_stack.add;
710 app_exception.raise_exception;
711 END IF;
712
713 RETURN;
714
715 END lock_row;
716
717
718 PROCEDURE update_row (
719 x_rowid IN VARCHAR2,
720 x_person_id IN NUMBER,
724 x_letter_repeating_group_cd IN VARCHAR2,
721 x_correspondence_type IN VARCHAR2,
722 x_letter_reference_number IN NUMBER,
723 x_spl_sequence_number IN NUMBER,
725 x_sequence_number IN NUMBER,
726 x_sup_repeating_group_cd IN VARCHAR2,
727 x_sup_splrg_sequence_number IN NUMBER,
728 x_mode IN VARCHAR2 DEFAULT 'R'
729 ) AS
730 /*
731 || Created By : [email protected]
732 || Created On : 14-DEC-2000
733 || Purpose : Handles the UPDATE DML logic for the table.
734 || Known limitations, enhancements or remarks :
735 || Change History :
736 || Who When What
737 || (reverse chronological order - newest change first)
738 */
739 x_last_update_date DATE ;
740 x_last_updated_by NUMBER;
741 x_last_update_login NUMBER;
742 x_request_id NUMBER;
743 x_program_id NUMBER;
744 x_program_application_id NUMBER;
745 x_program_update_date DATE;
746
747 BEGIN
748
749 x_last_update_date := SYSDATE;
750 IF (X_MODE = 'I') THEN
751 x_last_updated_by := 1;
752 x_last_update_login := 0;
753 ELSIF (x_mode = 'R') THEN
754 x_last_updated_by := fnd_global.user_id;
755 IF x_last_updated_by IS NULL THEN
756 x_last_updated_by := -1;
757 END IF;
758 x_last_update_login := fnd_global.login_id;
759 IF (x_last_update_login IS NULL) THEN
760 x_last_update_login := -1;
761 END IF;
762 ELSE
763 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
764 igs_ge_msg_stack.add;
765 app_exception.raise_exception;
766 END IF;
767
768 before_dml(
769 p_action => 'UPDATE',
770 x_rowid => x_rowid,
771 x_person_id => x_person_id,
772 x_correspondence_type => x_correspondence_type,
773 x_letter_reference_number => x_letter_reference_number,
774 x_spl_sequence_number => x_spl_sequence_number,
775 x_letter_repeating_group_cd => x_letter_repeating_group_cd,
776 x_sequence_number => x_sequence_number,
777 x_sup_repeating_group_cd => x_sup_repeating_group_cd,
778 x_sup_splrg_sequence_number => x_sup_splrg_sequence_number,
779 x_creation_date => x_last_update_date,
780 x_created_by => x_last_updated_by,
781 x_last_update_date => x_last_update_date,
782 x_last_updated_by => x_last_updated_by,
783 x_last_update_login => x_last_update_login
784 );
785
786 IF (x_mode = 'R') THEN
787 x_request_id := fnd_global.conc_request_id;
788 x_program_id := fnd_global.conc_program_id;
789 x_program_application_id := fnd_global.prog_appl_id;
790 IF (x_request_id = -1) THEN
791 x_request_id := old_references.request_id;
792 x_program_id := old_references.program_id;
793 x_program_application_id := old_references.program_application_id;
794 x_program_update_date := old_references.program_update_date;
795 ELSE
796 x_program_update_date := SYSDATE;
797 END IF;
798 END IF;
799
800 UPDATE igs_co_s_perlt_rptgp_all
801 SET
802 sup_repeating_group_cd = new_references.sup_repeating_group_cd,
803 sup_splrg_sequence_number = new_references.sup_splrg_sequence_number,
804 last_update_date = x_last_update_date,
805 last_updated_by = x_last_updated_by,
806 last_update_login = x_last_update_login ,
807 request_id = x_request_id,
808 program_id = x_program_id,
809 program_application_id = x_program_application_id,
810 program_update_date = x_program_update_date
811 WHERE rowid = x_rowid;
812
813 IF (SQL%NOTFOUND) THEN
814 RAISE NO_DATA_FOUND;
815 END IF;
816
817 END update_row;
818
819
820 PROCEDURE add_row (
821 x_rowid IN OUT NOCOPY VARCHAR2,
822 x_org_id IN NUMBER,
823 x_person_id IN NUMBER,
824 x_correspondence_type IN VARCHAR2,
825 x_letter_reference_number IN NUMBER,
826 x_spl_sequence_number IN NUMBER,
827 x_letter_repeating_group_cd IN VARCHAR2,
828 x_sequence_number IN NUMBER,
829 x_sup_repeating_group_cd IN VARCHAR2,
830 x_sup_splrg_sequence_number IN NUMBER,
831 x_mode IN VARCHAR2 DEFAULT 'R'
832 ) AS
833 /*
834 || Created By : [email protected]
835 || Created On : 14-DEC-2000
836 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
837 || Known limitations, enhancements or remarks :
838 || Change History :
839 || Who When What
840 || (reverse chronological order - newest change first)
841 */
842 CURSOR c1 IS
843 SELECT rowid
844 FROM igs_co_s_perlt_rptgp_all
845 WHERE person_id = x_person_id
846 AND correspondence_type = x_correspondence_type
847 AND letter_reference_number = x_letter_reference_number
848 AND spl_sequence_number = x_spl_sequence_number
849 AND letter_repeating_group_cd = x_letter_repeating_group_cd
850 AND sequence_number = x_sequence_number;
851
852 BEGIN
853
854 OPEN c1;
855 FETCH c1 INTO x_rowid;
856 IF (c1%NOTFOUND) THEN
857 CLOSE c1;
858
859 insert_row (
860 x_rowid,
861 x_org_id,
862 x_person_id,
863 x_correspondence_type,
864 x_letter_reference_number,
865 x_spl_sequence_number,
866 x_letter_repeating_group_cd,
867 x_sequence_number,
868 x_sup_repeating_group_cd,
869 x_sup_splrg_sequence_number,
870 x_mode
871 );
872 RETURN;
873 END IF;
874 CLOSE c1;
875
876 update_row (
877 x_rowid,
878 x_person_id,
879 x_correspondence_type,
880 x_letter_reference_number,
881 x_spl_sequence_number,
882 x_letter_repeating_group_cd,
883 x_sequence_number,
884 x_sup_repeating_group_cd,
885 x_sup_splrg_sequence_number,
886 x_mode
887 );
888
889 END add_row;
890
891
892 PROCEDURE delete_row (
893 x_rowid IN VARCHAR2
894 ) AS
895 /*
896 || Created By : [email protected]
897 || Created On : 14-DEC-2000
898 || Purpose : Handles the DELETE DML logic for the table.
899 || Known limitations, enhancements or remarks :
900 || Change History :
901 || Who When What
902 || (reverse chronological order - newest change first)
903 */
904 BEGIN
905
906 before_dml (
907 p_action => 'DELETE',
908 x_rowid => x_rowid
909 );
910
911 DELETE FROM igs_co_s_perlt_rptgp_all
912 WHERE rowid = x_rowid;
913
914 IF (SQL%NOTFOUND) THEN
915 RAISE NO_DATA_FOUND;
916 END IF;
917
918 END delete_row;
919
920
921 END igs_co_s_perlt_rptgp_pkg;
[Home] [Help]