[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_IMMU_DTLS_PKG
Source
1 PACKAGE BODY igs_pe_immu_dtls_pkg AS
2 /* $Header: IGSNI90B.pls 120.3 2005/10/17 02:22:16 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_immu_dtls%ROWTYPE;
6 new_references igs_pe_immu_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2, --DEFAULT NULL,
11 x_immu_details_id IN NUMBER , -- DEFAULT NULL,
12 x_person_id IN NUMBER , -- DEFAULT NULL,
13 x_immunization_code IN VARCHAR2, -- DEFAULT NULL,
14 x_status_code IN VARCHAR2, -- DEFAULT NULL,
15 x_start_date IN DATE , -- DEFAULT NULL,
16 x_end_date IN DATE , -- DEFAULT NULL,
17 x_attribute_category IN VARCHAR2 , -- DEFAULT NULL,
18 x_attribute1 IN VARCHAR2 , -- DEFAULT NULL,
19 x_attribute2 IN VARCHAR2 , -- DEFAULT NULL,
20 x_attribute3 IN VARCHAR2 , -- DEFAULT NULL,
21 x_attribute4 IN VARCHAR2 , -- DEFAULT NULL,
22 x_attribute5 IN VARCHAR2 , -- DEFAULT NULL,
23 x_attribute6 IN VARCHAR2 , -- DEFAULT NULL,
24 x_attribute7 IN VARCHAR2 , -- DEFAULT NULL,
25 x_attribute8 IN VARCHAR2 , -- DEFAULT NULL,
26 x_attribute9 IN VARCHAR2 , -- DEFAULT NULL,
27 x_attribute10 IN VARCHAR2 , -- DEFAULT NULL,
28 x_attribute11 IN VARCHAR2 , -- DEFAULT NULL,
29 x_attribute12 IN VARCHAR2 , -- DEFAULT NULL,
30 x_attribute13 IN VARCHAR2 , -- DEFAULT NULL,
31 x_attribute14 IN VARCHAR2 , -- DEFAULT NULL,
32 x_attribute15 IN VARCHAR2 , -- DEFAULT NULL,
33 x_attribute16 IN VARCHAR2 , -- DEFAULT NULL,
34 x_attribute17 IN VARCHAR2 , -- DEFAULT NULL,
35 x_attribute18 IN VARCHAR2 , -- DEFAULT NULL,
36 x_attribute19 IN VARCHAR2 , -- DEFAULT NULL,
37 x_attribute20 IN VARCHAR2 , -- DEFAULT NULL,
38 x_creation_date IN DATE , -- DEFAULT NULL,
39 x_created_by IN NUMBER , -- DEFAULT NULL,
40 x_last_update_date IN DATE , -- DEFAULT NULL,
41 x_last_updated_by IN NUMBER , -- DEFAULT NULL,
42 x_last_update_login IN NUMBER -- DEFAULT NULL
43 ) AS
44 /*
45 || Created By : cdcruz
46 || Created On : 21-SEP-2001
47 || Purpose : Initialises the Old and New references for the columns of the table.
48 || Known limitations, enhancements or remarks :
49 || Change History :
50 || Who When What
51 || (reverse chronological order - newest change first)
52 */
53
54 CURSOR cur_old_ref_values IS
55 SELECT *
56 FROM igs_pe_immu_dtls
57 WHERE rowid = x_rowid;
58
59 BEGIN
60
61 l_rowid := x_rowid;
62
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 OPEN cur_old_ref_values;
66 FETCH cur_old_ref_values INTO old_references;
67 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
68 CLOSE cur_old_ref_values;
69 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
70 igs_ge_msg_stack.add;
71 app_exception.raise_exception;
72 RETURN;
73 END IF;
74 CLOSE cur_old_ref_values;
75
76 -- Populate New Values.
77 new_references.immu_details_id := x_immu_details_id;
78 new_references.person_id := x_person_id;
79 new_references.immunization_code := x_immunization_code;
80 new_references.status_code := x_status_code;
81 new_references.start_date := x_start_date;
82 new_references.end_date := x_end_date;
83 new_references.attribute_category := x_attribute_category;
84 new_references.attribute1 := x_attribute1;
85 new_references.attribute2 := x_attribute2;
86 new_references.attribute3 := x_attribute3;
87 new_references.attribute4 := x_attribute4;
88 new_references.attribute5 := x_attribute5;
89 new_references.attribute6 := x_attribute6;
90 new_references.attribute7 := x_attribute7;
91 new_references.attribute8 := x_attribute8;
92 new_references.attribute9 := x_attribute9;
93 new_references.attribute10 := x_attribute10;
94 new_references.attribute11 := x_attribute11;
95 new_references.attribute12 := x_attribute12;
96 new_references.attribute13 := x_attribute13;
97 new_references.attribute14 := x_attribute14;
98 new_references.attribute15 := x_attribute15;
99 new_references.attribute16 := x_attribute16;
100 new_references.attribute17 := x_attribute17;
101 new_references.attribute18 := x_attribute18;
102 new_references.attribute19 := x_attribute19;
103 new_references.attribute20 := x_attribute20;
104
105 IF (p_action = 'UPDATE') THEN
106 new_references.creation_date := old_references.creation_date;
107 new_references.created_by := old_references.created_by;
108 ELSE
109 new_references.creation_date := x_creation_date;
110 new_references.created_by := x_created_by;
111 END IF;
112
113 new_references.last_update_date := x_last_update_date;
114 new_references.last_updated_by := x_last_updated_by;
115 new_references.last_update_login := x_last_update_login;
116
117 END set_column_values;
118
119
120 PROCEDURE BeforeRowInsertUpdate(
121 p_inserting IN BOOLEAN,
122 p_updating IN BOOLEAN,
123 p_deleting IN BOOLEAN
124 ) as
125 ------------------------------------------------------------------------------------------
126 --Created by : vredkar
127 --Date created: 19-JUL-2005
128 --
129 --Purpose:
130 --Known limitations/enhancements and/or remarks:
131 --
132 --Change History:
133 --Who When What
134 ----------------------------------------------------------------------------------------------
135 CURSOR validate_brth_dt(cp_person_id NUMBER) IS
136 SELECT birth_date FROM
137 IGS_PE_PERSON_BASE_V
138 WHERE person_id = cp_person_id ;
139
140 l_bth_dt IGS_PE_PERSON_BASE_V.birth_date%TYPE;
141
142 BEGIN
143 IF p_inserting OR p_updating THEN
144 OPEN validate_brth_dt(new_references.person_id);
145 FETCH validate_brth_dt INTO l_bth_dt;
146 CLOSE validate_brth_dt;
147
148 IF new_references.END_DATE IS NOT NULL AND new_references.START_DATE > new_references.END_DATE THEN
149 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_DT_LE_END_DT');
150 IGS_GE_MSG_STACK.ADD;
151 APP_EXCEPTION.RAISE_EXCEPTION;
152
153 ELSIF l_bth_dt IS NOT NULL AND l_bth_dt > new_references.START_DATE THEN
154 FND_MESSAGE.SET_NAME('IGS','IGS_AD_STRT_DT_LESS_BIRTH_DT');
155 IGS_GE_MSG_STACK.ADD;
156 APP_EXCEPTION.RAISE_EXCEPTION;
157 END IF;
158
159 END IF;
160
161 END BeforeRowInsertUpdate;
162
163 PROCEDURE check_uniqueness AS
164 /*
165 || Created By : cdcruz
166 || Created On : 21-SEP-2001
167 || Purpose : Handles the Unique Constraint logic defined for the columns.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 BEGIN
174
175 IF ( get_uk_for_validation (
176 new_references.person_id,
177 new_references.immunization_code,
178 new_references.start_date
179 )
180 ) THEN
181 fnd_message.set_name ('IGS', 'IGS_PE_HLTH_IMM_DUP_EXISTS');
182 igs_ge_msg_stack.add;
183 app_exception.raise_exception;
184 END IF;
185
186 END check_uniqueness;
187
188
189 PROCEDURE check_parent_existance AS
190 /*
191 || Created By : cdcruz
192 || Created On : 21-SEP-2001
193 || Purpose : Checks for the existance of Parent records.
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199 BEGIN
200
201 IF (((old_references.person_id = new_references.person_id)) OR
202 ((new_references.person_id IS NULL))) THEN
203 NULL;
204 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
205 new_references.person_id
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 FUNCTION get_pk_for_validation (
216 x_immu_details_id IN NUMBER
217 ) RETURN BOOLEAN AS
218 /*
219 || Created By : cdcruz
220 || Created On : 21-SEP-2001
221 || Purpose : Validates the Primary Key of the table.
222 || Known limitations, enhancements or remarks :
223 || Change History :
224 || Who When What
225 || (reverse chronological order - newest change first)
226 */
227 CURSOR cur_rowid IS
228 SELECT rowid
229 FROM igs_pe_immu_dtls
230 WHERE immu_details_id = x_immu_details_id
231 FOR UPDATE NOWAIT;
232
233 lv_rowid cur_rowid%RowType;
234
235 BEGIN
236
237 OPEN cur_rowid;
238 FETCH cur_rowid INTO lv_rowid;
239 IF (cur_rowid%FOUND) THEN
240 CLOSE cur_rowid;
241 RETURN(TRUE);
242 ELSE
243 CLOSE cur_rowid;
244 RETURN(FALSE);
245 END IF;
246
247 END get_pk_for_validation;
248
249
250 FUNCTION get_uk_for_validation (
251 x_person_id IN NUMBER,
252 x_immunization_code IN VARCHAR2,
253 x_start_date IN DATE
254 ) RETURN BOOLEAN AS
255 /*
256 || Created By : cdcruz
257 || Created On : 21-SEP-2001
258 || Purpose : Validates the Unique Keys of the table.
259 || Known limitations, enhancements or remarks :
260 || Change History :
261 || Who When What
262 || (reverse chronological order - newest change first)
263 */
264 CURSOR cur_rowid IS
265 SELECT rowid
266 FROM igs_pe_immu_dtls
267 WHERE person_id = x_person_id
268 AND immunization_code = x_immunization_code
269 AND start_date = x_start_date
270 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 OPEN cur_rowid;
277 FETCH cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 CLOSE cur_rowid;
280 RETURN (true);
281 ELSE
282 CLOSE cur_rowid;
283 RETURN(FALSE);
284 END IF;
285
286 END get_uk_for_validation ;
287
288
289 PROCEDURE get_fk_hz_parties (
290 x_party_id IN NUMBER
291 ) AS
292 /*
293 || Created By : cdcruz
294 || Created On : 21-SEP-2001
295 || Purpose : Validates the Foreign Keys for the table.
296 || Known limitations, enhancements or remarks :
297 || Change History :
298 || Who When What
299 || (reverse chronological order - newest change first)
300 */
301 CURSOR cur_rowid IS
302 SELECT rowid
303 FROM igs_pe_immu_dtls
304 WHERE ((person_id = x_party_id));
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_PE_PID_HZ_FK');
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_hz_parties;
322
323
324 PROCEDURE before_dml (
325 p_action IN VARCHAR2,
326 x_rowid IN VARCHAR2,
327 x_immu_details_id IN NUMBER ,
328 x_person_id IN NUMBER ,
329 x_immunization_code IN VARCHAR2 ,
330 x_status_code IN VARCHAR2 ,
331 x_start_date IN DATE ,
332 x_end_date IN DATE ,
333 x_attribute_category IN VARCHAR2 ,
334 x_attribute1 IN VARCHAR2 ,
335 x_attribute2 IN VARCHAR2 ,
336 x_attribute3 IN VARCHAR2 ,
337 x_attribute4 IN VARCHAR2 ,
338 x_attribute5 IN VARCHAR2 ,
339 x_attribute6 IN VARCHAR2 ,
340 x_attribute7 IN VARCHAR2 ,
341 x_attribute8 IN VARCHAR2 ,
342 x_attribute9 IN VARCHAR2 ,
343 x_attribute10 IN VARCHAR2,
344 x_attribute11 IN VARCHAR2,
345 x_attribute12 IN VARCHAR2,
346 x_attribute13 IN VARCHAR2,
347 x_attribute14 IN VARCHAR2,
348 x_attribute15 IN VARCHAR2,
349 x_attribute16 IN VARCHAR2,
350 x_attribute17 IN VARCHAR2,
351 x_attribute18 IN VARCHAR2,
352 x_attribute19 IN VARCHAR2,
353 x_attribute20 IN VARCHAR2,
354 x_creation_date IN DATE ,
355 x_created_by IN NUMBER,
356 x_last_update_date IN DATE ,
357 x_last_updated_by IN NUMBER,
358 x_last_update_login IN NUMBER
359 ) AS
360 /*
361 || Created By : cdcruz
362 || Created On : 21-SEP-2001
363 || Purpose : Initialises the columns, Checks Constraints, Calls the
364 || Trigger Handlers for the table, before any DML operation.
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || (reverse chronological order - newest change first)
369 */
370 BEGIN
371
372 set_column_values (
373 p_action,
374 x_rowid,
375 x_immu_details_id,
376 x_person_id,
377 x_immunization_code,
378 x_status_code,
379 x_start_date,
380 x_end_date,
381 x_attribute_category,
382 x_attribute1,
383 x_attribute2,
384 x_attribute3,
385 x_attribute4,
386 x_attribute5,
387 x_attribute6,
388 x_attribute7,
389 x_attribute8,
390 x_attribute9,
391 x_attribute10,
392 x_attribute11,
393 x_attribute12,
394 x_attribute13,
395 x_attribute14,
396 x_attribute15,
397 x_attribute16,
398 x_attribute17,
399 x_attribute18,
400 x_attribute19,
401 x_attribute20,
402 x_creation_date,
403 x_created_by,
404 x_last_update_date,
405 x_last_updated_by,
406 x_last_update_login
407 );
408
409 IF (p_action = 'INSERT') THEN
410 -- Call all the procedures related to Before Insert.
411 BeforeRowInsertUpdate( TRUE, FALSE,FALSE );
412
413 IF ( get_pk_for_validation(
414 new_references.immu_details_id
415 )
416 ) THEN
417 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
418 igs_ge_msg_stack.add;
419 app_exception.raise_exception;
420 END IF;
421 check_uniqueness;
422 check_parent_existance;
423 ELSIF (p_action = 'UPDATE') THEN
424 -- Call all the procedures related to Before Update.
425 BeforeRowInsertUpdate( FALSE,TRUE,FALSE );
426 check_uniqueness;
427 check_parent_existance;
428 ELSIF (p_action = 'VALIDATE_INSERT') THEN
429 -- Call all the procedures related to Before Insert.
430 IF ( get_pk_for_validation (
431 new_references.immu_details_id
432 )
433 ) THEN
434 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
435 igs_ge_msg_stack.add;
436 app_exception.raise_exception;
437 END IF;
438 check_uniqueness;
439 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
440 check_uniqueness;
441 END IF;
442
443 END before_dml;
444
445
446 PROCEDURE insert_row (
447 x_rowid IN OUT NOCOPY VARCHAR2,
448 x_immu_details_id IN OUT NOCOPY NUMBER,
449 x_person_id IN NUMBER,
450 x_immunization_code IN VARCHAR2,
451 x_status_code IN VARCHAR2,
452 x_start_date IN DATE,
453 x_end_date IN DATE,
454 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
455 x_ATTRIBUTE1 IN VARCHAR2,
456 x_ATTRIBUTE2 IN VARCHAR2,
457 x_ATTRIBUTE3 IN VARCHAR2,
458 x_ATTRIBUTE4 IN VARCHAR2,
459 x_ATTRIBUTE5 IN VARCHAR2,
460 x_ATTRIBUTE6 IN VARCHAR2,
461 x_ATTRIBUTE7 IN VARCHAR2,
462 x_ATTRIBUTE8 IN VARCHAR2,
463 x_ATTRIBUTE9 IN VARCHAR2,
464 x_ATTRIBUTE10 IN VARCHAR2,
465 x_ATTRIBUTE11 IN VARCHAR2,
466 x_ATTRIBUTE12 IN VARCHAR2,
467 x_ATTRIBUTE13 IN VARCHAR2,
468 x_ATTRIBUTE14 IN VARCHAR2,
469 x_ATTRIBUTE15 IN VARCHAR2,
470 x_ATTRIBUTE16 IN VARCHAR2,
471 x_ATTRIBUTE17 IN VARCHAR2,
472 x_ATTRIBUTE18 IN VARCHAR2,
473 x_ATTRIBUTE19 IN VARCHAR2,
474 x_ATTRIBUTE20 IN VARCHAR2,
475 x_mode IN VARCHAR2 -- DEFAULT 'R'
476 ) AS
477 /*
478 || Created By : cdcruz
479 || Created On : 21-SEP-2001
480 || Purpose : Handles the INSERT DML logic for the table.
481 || Known limitations, enhancements or remarks :
482 || Change History :
483 || Who When What
484 || (reverse chronological order - newest change first)
485 */
486 CURSOR c IS
487 SELECT rowid
488 FROM igs_pe_immu_dtls
489 WHERE immu_details_id = x_immu_details_id;
490
491 x_last_update_date DATE;
492 x_last_updated_by NUMBER;
493 x_last_update_login NUMBER;
494
495 BEGIN
496
497 x_last_update_date := SYSDATE;
498 IF (x_mode = 'I') THEN
499 x_last_updated_by := 1;
500 x_last_update_login := 0;
501 ELSIF (X_MODE IN ('R', 'S')) THEN
502 x_last_updated_by := fnd_global.user_id;
503 IF (x_last_updated_by IS NULL) THEN
504 x_last_updated_by := -1;
505 END IF;
506 x_last_update_login := fnd_global.login_id;
507 IF (x_last_update_login IS NULL) THEN
508 x_last_update_login := -1;
509 END IF;
510 ELSE
511 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
512 igs_ge_msg_stack.add;
513 app_exception.raise_exception;
514 END IF;
515
516 SELECT igs_pe_immu_dtls_s.NEXTVAL
517 INTO x_immu_details_id
518 FROM dual;
519
520 before_dml(
521 p_action => 'INSERT',
522 x_rowid => x_rowid,
523 x_immu_details_id => x_immu_details_id,
524 x_person_id => x_person_id,
525 x_immunization_code => x_immunization_code,
526 x_status_code => x_status_code,
527 x_start_date => x_start_date,
528 x_end_date => x_end_date,
529 x_attribute_category =>X_ATTRIBUTE_CATEGORY,
530 x_attribute1 =>X_ATTRIBUTE1,
531 x_attribute2 =>X_ATTRIBUTE2,
532 x_attribute3 =>X_ATTRIBUTE3,
533 x_attribute4 =>X_ATTRIBUTE4,
534 x_attribute5 =>X_ATTRIBUTE5,
535 x_attribute6 =>X_ATTRIBUTE6,
536 x_attribute7 =>X_ATTRIBUTE7,
537 x_attribute8 =>X_ATTRIBUTE8,
538 x_attribute9 =>X_ATTRIBUTE9,
539 x_attribute10 =>X_ATTRIBUTE10,
540 x_attribute11 =>X_ATTRIBUTE11,
541 x_attribute12 =>X_ATTRIBUTE12,
542 x_attribute13 =>X_ATTRIBUTE13,
543 x_attribute14 =>X_ATTRIBUTE14,
544 x_attribute15 =>X_ATTRIBUTE15,
545 x_attribute16 =>X_ATTRIBUTE16,
546 x_attribute17 =>X_ATTRIBUTE17,
547 x_attribute18 =>X_ATTRIBUTE18,
548 x_attribute19 =>X_ATTRIBUTE19,
549 x_attribute20 =>X_ATTRIBUTE20,
550 x_creation_date => x_last_update_date,
551 x_created_by => x_last_updated_by,
552 x_last_update_date => x_last_update_date,
553 x_last_updated_by => x_last_updated_by,
554 x_last_update_login => x_last_update_login
555 );
556
557 IF (x_mode = 'S') THEN
558 igs_sc_gen_001.set_ctx('R');
559 END IF;
560 INSERT INTO igs_pe_immu_dtls (
561 immu_details_id,
562 person_id,
563 immunization_code,
564 status_code,
565 start_date,
566 end_date,
567 ATTRIBUTE_CATEGORY,
568 ATTRIBUTE1,
569 ATTRIBUTE2,
570 ATTRIBUTE3,
571 ATTRIBUTE4,
572 ATTRIBUTE5,
573 ATTRIBUTE6,
574 ATTRIBUTE7,
575 ATTRIBUTE8,
576 ATTRIBUTE9,
577 ATTRIBUTE10,
578 ATTRIBUTE11,
579 ATTRIBUTE12,
580 ATTRIBUTE13,
581 ATTRIBUTE14,
582 ATTRIBUTE15,
583 ATTRIBUTE16,
584 ATTRIBUTE17,
585 ATTRIBUTE18,
586 ATTRIBUTE19,
587 ATTRIBUTE20,
588 creation_date,
589 created_by,
590 last_update_date,
591 last_updated_by,
592 last_update_login
593 ) VALUES (
594 new_references.immu_details_id,
595 new_references.person_id,
596 new_references.immunization_code,
597 new_references.status_code,
598 new_references.start_date,
599 new_references.end_date,
600 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
601 NEW_REFERENCES.ATTRIBUTE1,
602 NEW_REFERENCES.ATTRIBUTE2,
603 NEW_REFERENCES.ATTRIBUTE3,
604 NEW_REFERENCES.ATTRIBUTE4,
605 NEW_REFERENCES.ATTRIBUTE5,
606 NEW_REFERENCES.ATTRIBUTE6,
607 NEW_REFERENCES.ATTRIBUTE7,
608 NEW_REFERENCES.ATTRIBUTE8,
609 NEW_REFERENCES.ATTRIBUTE9,
610 NEW_REFERENCES.ATTRIBUTE10,
611 NEW_REFERENCES.ATTRIBUTE11,
612 NEW_REFERENCES.ATTRIBUTE12,
613 NEW_REFERENCES.ATTRIBUTE13,
614 NEW_REFERENCES.ATTRIBUTE14,
615 NEW_REFERENCES.ATTRIBUTE15,
616 NEW_REFERENCES.ATTRIBUTE16,
617 NEW_REFERENCES.ATTRIBUTE17,
618 NEW_REFERENCES.ATTRIBUTE18,
619 NEW_REFERENCES.ATTRIBUTE19,
620 NEW_REFERENCES.ATTRIBUTE20,
621 x_last_update_date,
622 x_last_updated_by,
623 x_last_update_date,
624 x_last_updated_by,
625 x_last_update_login
626 );
627 IF (x_mode = 'S') THEN
628 igs_sc_gen_001.unset_ctx('R');
629 END IF;
630
631
632 OPEN c;
633 FETCH c INTO x_rowid;
634 IF (c%NOTFOUND) THEN
635 CLOSE c;
636 RAISE NO_DATA_FOUND;
637 END IF;
638 CLOSE c;
639
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
644 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
645 fnd_message.set_token ('ERR_CD', SQLCODE);
646 igs_ge_msg_stack.add;
647 igs_sc_gen_001.unset_ctx('R');
648 app_exception.raise_exception;
649 ELSE
650 igs_sc_gen_001.unset_ctx('R');
651 RAISE;
652 END IF;
653 END insert_row;
654
655
656 PROCEDURE lock_row (
657 x_rowid IN VARCHAR2,
658 x_immu_details_id IN NUMBER,
659 x_person_id IN NUMBER,
660 x_immunization_code IN VARCHAR2,
661 x_status_code IN VARCHAR2,
662 x_start_date IN DATE,
663 x_end_date IN DATE,
664 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
665 x_ATTRIBUTE1 IN VARCHAR2,
666 x_ATTRIBUTE2 IN VARCHAR2,
667 x_ATTRIBUTE3 IN VARCHAR2,
668 x_ATTRIBUTE4 IN VARCHAR2,
669 x_ATTRIBUTE5 IN VARCHAR2,
670 x_ATTRIBUTE6 IN VARCHAR2,
671 x_ATTRIBUTE7 IN VARCHAR2,
672 x_ATTRIBUTE8 IN VARCHAR2,
673 x_ATTRIBUTE9 IN VARCHAR2,
674 x_ATTRIBUTE10 IN VARCHAR2,
675 x_ATTRIBUTE11 IN VARCHAR2,
676 x_ATTRIBUTE12 IN VARCHAR2,
677 x_ATTRIBUTE13 IN VARCHAR2,
678 x_ATTRIBUTE14 IN VARCHAR2,
679 x_ATTRIBUTE15 IN VARCHAR2,
680 x_ATTRIBUTE16 IN VARCHAR2,
681 x_ATTRIBUTE17 IN VARCHAR2,
682 x_ATTRIBUTE18 IN VARCHAR2,
683 x_ATTRIBUTE19 IN VARCHAR2,
684 x_ATTRIBUTE20 IN VARCHAR2
685 ) AS
686 /*
687 || Created By : cdcruz
688 || Created On : 21-SEP-2001
689 || Purpose : Handles the LOCK mechanism for the table.
690 || Known limitations, enhancements or remarks :
691 || Change History :
692 || Who When What
693 || (reverse chronological order - newest change first)
694 */
695 CURSOR c1 IS
696 SELECT
697 person_id,
698 immunization_code,
699 status_code,
700 start_date,
701 end_date,
702 ATTRIBUTE_CATEGORY,
703 ATTRIBUTE1,
704 ATTRIBUTE2,
705 ATTRIBUTE3,
706 ATTRIBUTE4,
707 ATTRIBUTE5,
708 ATTRIBUTE6,
709 ATTRIBUTE7,
710 ATTRIBUTE8,
711 ATTRIBUTE9,
712 ATTRIBUTE10,
713 ATTRIBUTE11,
714 ATTRIBUTE12,
715 ATTRIBUTE13,
716 ATTRIBUTE14,
717 ATTRIBUTE15,
718 ATTRIBUTE16,
719 ATTRIBUTE17,
720 ATTRIBUTE18,
721 ATTRIBUTE19,
722 ATTRIBUTE20
723
724 FROM igs_pe_immu_dtls
725 WHERE rowid = x_rowid
726 FOR UPDATE NOWAIT;
727
728 tlinfo c1%ROWTYPE;
729
730 BEGIN
731
732 OPEN c1;
733 FETCH c1 INTO tlinfo;
734 IF (c1%notfound) THEN
735 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
736 igs_ge_msg_stack.add;
737 CLOSE c1;
738 app_exception.raise_exception;
739 RETURN;
740 END IF;
741 CLOSE c1;
742
743 IF (
744 (tlinfo.person_id = x_person_id)
745 AND (tlinfo.immunization_code = x_immunization_code)
746 AND (tlinfo.status_code = x_status_code)
747 AND (tlinfo.start_date = x_start_date)
748 AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
749 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
750 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
751 AND (X_ATTRIBUTE_CATEGORY is null)))
752 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
753 OR ((tlinfo.ATTRIBUTE1 is null)
754 AND (X_ATTRIBUTE1 is null)))
755 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
756 OR ((tlinfo.ATTRIBUTE2 is null)
757 AND (X_ATTRIBUTE2 is null)))
758 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
759 OR ((tlinfo.ATTRIBUTE3 is null)
760 AND (X_ATTRIBUTE3 is null)))
761 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
762 OR ((tlinfo.ATTRIBUTE4 is null)
763 AND (X_ATTRIBUTE4 is null)))
764 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
765 OR ((tlinfo.ATTRIBUTE5 is null)
766 AND (X_ATTRIBUTE5 is null)))
767 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
768 OR ((tlinfo.ATTRIBUTE6 is null)
769 AND (X_ATTRIBUTE6 is null)))
770 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
771 OR ((tlinfo.ATTRIBUTE7 is null)
772 AND (X_ATTRIBUTE7 is null)))
773 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
774 OR ((tlinfo.ATTRIBUTE8 is null)
775 AND (X_ATTRIBUTE8 is null)))
776 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
777 OR ((tlinfo.ATTRIBUTE9 is null)
778 AND (X_ATTRIBUTE9 is null)))
779 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
780 OR ((tlinfo.ATTRIBUTE10 is null)
781 AND (X_ATTRIBUTE10 is null)))
782 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
783 OR ((tlinfo.ATTRIBUTE11 is null)
784 AND (X_ATTRIBUTE11 is null)))
785 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
786 OR ((tlinfo.ATTRIBUTE12 is null)
787 AND (X_ATTRIBUTE12 is null)))
788 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
789 OR ((tlinfo.ATTRIBUTE13 is null)
790 AND (X_ATTRIBUTE13 is null)))
791 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
792 OR ((tlinfo.ATTRIBUTE14 is null)
793 AND (X_ATTRIBUTE14 is null)))
794 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
795 OR ((tlinfo.ATTRIBUTE15 is null)
796 AND (X_ATTRIBUTE15 is null)))
797 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
798 OR ((tlinfo.ATTRIBUTE16 is null)
799 AND (X_ATTRIBUTE16 is null)))
800 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
801 OR ((tlinfo.ATTRIBUTE17 is null)
802 AND (X_ATTRIBUTE17 is null)))
803 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
804 OR ((tlinfo.ATTRIBUTE18 is null)
805 AND (X_ATTRIBUTE18 is null)))
806 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
807 OR ((tlinfo.ATTRIBUTE19 is null)
808 AND (X_ATTRIBUTE19 is null)))
809 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR ((tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
810
811 ) THEN
812 NULL;
813 ELSE
814 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
815 igs_ge_msg_stack.add;
816 app_exception.raise_exception;
817 END IF;
818
819 RETURN;
820
821 END lock_row;
822
823
824 PROCEDURE update_row (
825 x_rowid IN VARCHAR2,
826 x_immu_details_id IN NUMBER,
827 x_person_id IN NUMBER,
828 x_immunization_code IN VARCHAR2,
829 x_status_code IN VARCHAR2,
830 x_start_date IN DATE,
831 x_end_date IN DATE,
832 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
833 x_ATTRIBUTE1 IN VARCHAR2,
834 x_ATTRIBUTE2 IN VARCHAR2,
835 x_ATTRIBUTE3 IN VARCHAR2,
836 x_ATTRIBUTE4 IN VARCHAR2,
837 x_ATTRIBUTE5 IN VARCHAR2,
838 x_ATTRIBUTE6 IN VARCHAR2,
839 x_ATTRIBUTE7 IN VARCHAR2,
840 x_ATTRIBUTE8 IN VARCHAR2,
841 x_ATTRIBUTE9 IN VARCHAR2,
842 x_ATTRIBUTE10 IN VARCHAR2,
843 x_ATTRIBUTE11 IN VARCHAR2,
844 x_ATTRIBUTE12 IN VARCHAR2,
845 x_ATTRIBUTE13 IN VARCHAR2,
846 x_ATTRIBUTE14 IN VARCHAR2,
847 x_ATTRIBUTE15 IN VARCHAR2,
848 x_ATTRIBUTE16 IN VARCHAR2,
849 x_ATTRIBUTE17 IN VARCHAR2,
850 x_ATTRIBUTE18 IN VARCHAR2,
851 x_ATTRIBUTE19 IN VARCHAR2,
852 x_ATTRIBUTE20 IN VARCHAR2,
853 x_mode IN VARCHAR2 -- DEFAULT 'R'
854 ) AS
855 /*
856 || Created By : cdcruz
857 || Created On : 21-SEP-2001
858 || Purpose : Handles the UPDATE DML logic for the table.
859 || Known limitations, enhancements or remarks :
860 || Change History :
861 || Who When What
862 || (reverse chronological order - newest change first)
863 */
864 x_last_update_date DATE ;
865 x_last_updated_by NUMBER;
866 x_last_update_login NUMBER;
867
868 BEGIN
869
870 x_last_update_date := SYSDATE;
871 IF (X_MODE = 'I') THEN
872 x_last_updated_by := 1;
873 x_last_update_login := 0;
874 ELSIF (X_MODE IN ('R', 'S')) THEN
875 x_last_updated_by := fnd_global.user_id;
876 IF x_last_updated_by IS NULL THEN
877 x_last_updated_by := -1;
878 END IF;
879 x_last_update_login := fnd_global.login_id;
880 IF (x_last_update_login IS NULL) THEN
881 x_last_update_login := -1;
882 END IF;
883 ELSE
884 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
885 igs_ge_msg_stack.add;
886 app_exception.raise_exception;
887 END IF;
888
889 before_dml(
890 p_action => 'UPDATE',
891 x_rowid => x_rowid,
892 x_immu_details_id => x_immu_details_id,
893 x_person_id => x_person_id,
894 x_immunization_code => x_immunization_code,
895 x_status_code => x_status_code,
896 x_start_date => x_start_date,
897 x_end_date => x_end_date,
898 x_attribute_category =>X_ATTRIBUTE_CATEGORY,
899 x_attribute1 =>X_ATTRIBUTE1,
900 x_attribute2 =>X_ATTRIBUTE2,
901 x_attribute3 =>X_ATTRIBUTE3,
902 x_attribute4 =>X_ATTRIBUTE4,
903 x_attribute5 =>X_ATTRIBUTE5,
904 x_attribute6 =>X_ATTRIBUTE6,
905 x_attribute7 =>X_ATTRIBUTE7,
906 x_attribute8 =>X_ATTRIBUTE8,
907 x_attribute9 =>X_ATTRIBUTE9,
908 x_attribute10 =>X_ATTRIBUTE10,
909 x_attribute11 =>X_ATTRIBUTE11,
910 x_attribute12 =>X_ATTRIBUTE12,
911 x_attribute13 =>X_ATTRIBUTE13,
912 x_attribute14 =>X_ATTRIBUTE14,
913 x_attribute15 =>X_ATTRIBUTE15,
914 x_attribute16 =>X_ATTRIBUTE16,
915 x_attribute17 =>X_ATTRIBUTE17,
916 x_attribute18 =>X_ATTRIBUTE18,
917 x_attribute19 =>X_ATTRIBUTE19,
918 x_attribute20 =>X_ATTRIBUTE20,
919 x_creation_date => x_last_update_date,
920 x_created_by => x_last_updated_by,
921 x_last_update_date => x_last_update_date,
922 x_last_updated_by => x_last_updated_by,
923 x_last_update_login => x_last_update_login
924 );
925
926 IF (x_mode = 'S') THEN
927 igs_sc_gen_001.set_ctx('R');
928 END IF;
929 UPDATE igs_pe_immu_dtls
930 SET
931 person_id = new_references.person_id,
932 immunization_code = new_references.immunization_code,
933 status_code = new_references.status_code,
934 start_date = new_references.start_date,
935 end_date = new_references.end_date,
936 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
937 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
938 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
939 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
940 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
941 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
942 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
943 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
944 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
945 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
946 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
947 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
948 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
949 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
950 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
951 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
952 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
953 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
954 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
955 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
956 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
957 last_update_date = x_last_update_date,
958 last_updated_by = x_last_updated_by,
959 last_update_login = x_last_update_login
960 WHERE rowid = x_rowid;
961
962 IF (SQL%NOTFOUND) THEN
963 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
964 igs_ge_msg_stack.add;
965 igs_sc_gen_001.unset_ctx('R');
966 app_exception.raise_exception;
967 END IF;
968 IF (x_mode = 'S') THEN
969 igs_sc_gen_001.unset_ctx('R');
970 END IF;
971
972
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 IF (SQLCODE = (-28115)) THEN
977 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
978 fnd_message.set_token ('ERR_CD', SQLCODE);
979 igs_ge_msg_stack.add;
980 igs_sc_gen_001.unset_ctx('R');
981 app_exception.raise_exception;
982 ELSE
983 igs_sc_gen_001.unset_ctx('R');
984 RAISE;
985 END IF;
986 END update_row;
987
988
989 PROCEDURE add_row (
990 x_rowid IN OUT NOCOPY VARCHAR2,
991 x_immu_details_id IN OUT NOCOPY NUMBER,
992 x_person_id IN NUMBER,
993 x_immunization_code IN VARCHAR2,
994 x_status_code IN VARCHAR2,
995 x_start_date IN DATE,
996 x_end_date IN DATE,
997 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
998 x_ATTRIBUTE1 IN VARCHAR2,
999 x_ATTRIBUTE2 IN VARCHAR2,
1000 x_ATTRIBUTE3 IN VARCHAR2,
1001 x_ATTRIBUTE4 IN VARCHAR2,
1002 x_ATTRIBUTE5 IN VARCHAR2,
1003 x_ATTRIBUTE6 IN VARCHAR2,
1004 x_ATTRIBUTE7 IN VARCHAR2,
1005 x_ATTRIBUTE8 IN VARCHAR2,
1006 x_ATTRIBUTE9 IN VARCHAR2,
1007 x_ATTRIBUTE10 IN VARCHAR2,
1008 x_ATTRIBUTE11 IN VARCHAR2,
1009 x_ATTRIBUTE12 IN VARCHAR2,
1010 x_ATTRIBUTE13 IN VARCHAR2,
1011 x_ATTRIBUTE14 IN VARCHAR2,
1012 x_ATTRIBUTE15 IN VARCHAR2,
1013 x_ATTRIBUTE16 IN VARCHAR2,
1014 x_ATTRIBUTE17 IN VARCHAR2,
1015 x_ATTRIBUTE18 IN VARCHAR2,
1016 x_ATTRIBUTE19 IN VARCHAR2,
1017 x_ATTRIBUTE20 IN VARCHAR2,
1018 x_mode IN VARCHAR2 -- DEFAULT 'R'
1019 ) AS
1020 /*
1021 || Created By : cdcruz
1022 || Created On : 21-SEP-2001
1023 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1024 || Known limitations, enhancements or remarks :
1025 || Change History :
1026 || Who When What
1027 || (reverse chronological order - newest change first)
1028 */
1029 CURSOR c1 IS
1030 SELECT rowid
1031 FROM igs_pe_immu_dtls
1032 WHERE immu_details_id = x_immu_details_id;
1033
1034 BEGIN
1035
1036 OPEN c1;
1037 FETCH c1 INTO x_rowid;
1038 IF (c1%NOTFOUND) THEN
1039 CLOSE c1;
1040
1041 insert_row (
1042 x_rowid,
1043 x_immu_details_id,
1044 x_person_id,
1045 x_immunization_code,
1046 x_status_code,
1047 x_start_date,
1048 x_end_date,
1049 X_ATTRIBUTE_CATEGORY,
1050 X_ATTRIBUTE1,
1051 X_ATTRIBUTE2,
1052 X_ATTRIBUTE3,
1053 X_ATTRIBUTE4,
1054 X_ATTRIBUTE5,
1055 X_ATTRIBUTE6,
1056 X_ATTRIBUTE7,
1057 X_ATTRIBUTE8,
1058 X_ATTRIBUTE9,
1059 X_ATTRIBUTE10,
1060 X_ATTRIBUTE11,
1061 X_ATTRIBUTE12,
1062 X_ATTRIBUTE13,
1063 X_ATTRIBUTE14,
1064 X_ATTRIBUTE15,
1065 X_ATTRIBUTE16,
1066 X_ATTRIBUTE17,
1067 X_ATTRIBUTE18,
1068 X_ATTRIBUTE19,
1069 X_ATTRIBUTE20,
1070 x_mode
1071 );
1072 RETURN;
1073 END IF;
1074 CLOSE c1;
1075
1076 update_row (
1077 x_rowid,
1078 x_immu_details_id,
1079 x_person_id,
1080 x_immunization_code,
1081 x_status_code,
1082 x_start_date,
1083 x_end_date,
1084 X_ATTRIBUTE_CATEGORY,
1085 X_ATTRIBUTE1,
1086 X_ATTRIBUTE2,
1087 X_ATTRIBUTE3,
1088 X_ATTRIBUTE4,
1089 X_ATTRIBUTE5,
1090 X_ATTRIBUTE6,
1091 X_ATTRIBUTE7,
1092 X_ATTRIBUTE8,
1093 X_ATTRIBUTE9,
1094 X_ATTRIBUTE10,
1095 X_ATTRIBUTE11,
1096 X_ATTRIBUTE12,
1097 X_ATTRIBUTE13,
1098 X_ATTRIBUTE14,
1099 X_ATTRIBUTE15,
1100 X_ATTRIBUTE16,
1101 X_ATTRIBUTE17,
1102 X_ATTRIBUTE18,
1103 X_ATTRIBUTE19,
1104 X_ATTRIBUTE20,
1105 x_mode
1106 );
1107
1108 END add_row;
1109
1110
1111 PROCEDURE delete_row (
1112 x_rowid IN VARCHAR2,
1113 x_mode IN VARCHAR2
1114 ) AS
1115 /*
1116 || Created By : cdcruz
1117 || Created On : 21-SEP-2001
1118 || Purpose : Handles the DELETE DML logic for the table.
1119 || Known limitations, enhancements or remarks :
1120 || Change History :
1121 || Who When What
1122 || (reverse chronological order - newest change first)
1123 */
1124 BEGIN
1125
1126 before_dml (
1127 p_action => 'DELETE',
1128 x_rowid => x_rowid
1129 );
1130
1131 IF (x_mode = 'S') THEN
1132 igs_sc_gen_001.set_ctx('R');
1133 END IF;
1134 DELETE FROM igs_pe_immu_dtls
1135 WHERE rowid = x_rowid;
1136
1137 IF (SQL%NOTFOUND) THEN
1138 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1139 igs_ge_msg_stack.add;
1140 igs_sc_gen_001.unset_ctx('R');
1141 app_exception.raise_exception;
1142 END IF;
1143 IF (x_mode = 'S') THEN
1144 igs_sc_gen_001.unset_ctx('R');
1145 END IF;
1146
1147
1148 END delete_row;
1149
1150
1151 END igs_pe_immu_dtls_pkg;