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