[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_HLTH_INS_PKG
Source
1 PACKAGE BODY igs_pe_hlth_ins_pkg AS
2 /* $Header: IGSNI57B.pls 120.3 2005/10/17 02:21:48 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PE_HLTH_INS_ALL%RowType;
6 new_references IGS_PE_HLTH_INS_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_health_ins_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_insurance_provider IN VARCHAR2,
14 x_policy_number IN VARCHAR2 ,
15 x_start_date IN DATE ,
16 x_end_date IN DATE ,
17 x_attribute_category IN VARCHAR2 ,
18 x_attribute1 IN VARCHAR2 ,
19 x_attribute2 IN VARCHAR2 ,
20 x_attribute3 IN VARCHAR2 ,
21 x_attribute4 IN VARCHAR2 ,
22 x_attribute5 IN VARCHAR2 ,
23 x_attribute6 IN VARCHAR2 ,
24 x_attribute7 IN VARCHAR2 ,
25 x_attribute8 IN VARCHAR2 ,
26 x_attribute9 IN VARCHAR2 ,
27 x_attribute10 IN VARCHAR2,
28 x_attribute11 IN VARCHAR2,
29 x_attribute12 IN VARCHAR2,
30 x_attribute13 IN VARCHAR2,
31 x_attribute14 IN VARCHAR2,
32 x_attribute15 IN VARCHAR2,
33 x_attribute16 IN VARCHAR2,
34 x_attribute17 IN VARCHAR2,
35 x_attribute18 IN VARCHAR2,
36 x_attribute19 IN VARCHAR2,
37 x_attribute20 IN VARCHAR2,
38 X_ORG_ID in NUMBER ,
39 x_creation_date IN DATE ,
40 x_created_by IN NUMBER ,
41 x_last_update_date IN DATE ,
42 x_last_updated_by IN NUMBER ,
43 x_last_update_login IN NUMBER ,
44 x_insurance_cd IN VARCHAR2
45 ) AS
46 /***********************************************************
47
48 Created By : vvaitla
49
50 Date Created By : 2000/05/10
51
52 Purpose : To update,insert, add rows
53
54 Know limitations, enhancements or remarks
55
56 Change History
57
58 Who When What
59
60 ****************************************************************/
61
62 CURSOR cur_old_ref_values IS
63 SELECT *
64 FROM IGS_PE_HLTH_INS_ALL
65 WHERE rowid = x_rowid;
66
67 BEGIN
68
69 l_rowid := x_rowid;
70
71 -- Code for setting the Old and New Reference Values.
72 -- Populate Old Values.
73 Open cur_old_ref_values;
74 Fetch cur_old_ref_values INTO old_references;
75 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
76 Close cur_old_ref_values;
77 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
78 IGS_GE_MSG_STACK.ADD;
79 App_Exception.Raise_Exception;
80 Return;
81 END IF;
82 Close cur_old_ref_values;
83
84 -- Populate New Values.
85 new_references.health_ins_id := x_health_ins_id;
86 new_references.person_id := x_person_id;
87 new_references.insurance_provider := x_insurance_provider;
88 new_references.policy_number := x_policy_number;
89 new_references.start_date := x_start_date;
90 new_references.end_date := x_end_date;
91 new_references.attribute_category := x_attribute_category;
92 new_references.attribute1 := x_attribute1;
93 new_references.attribute2 := x_attribute2;
94 new_references.attribute3 := x_attribute3;
95 new_references.attribute4 := x_attribute4;
96 new_references.attribute5 := x_attribute5;
97 new_references.attribute6 := x_attribute6;
98 new_references.attribute7 := x_attribute7;
99 new_references.attribute8 := x_attribute8;
100 new_references.attribute9 := x_attribute9;
101 new_references.attribute10 := x_attribute10;
102 new_references.attribute11 := x_attribute11;
103 new_references.attribute12 := x_attribute12;
104 new_references.attribute13 := x_attribute13;
105 new_references.attribute14 := x_attribute14;
106 new_references.attribute15 := x_attribute15;
107 new_references.attribute16 := x_attribute16;
108 new_references.attribute17 := x_attribute17;
109 new_references.attribute18 := x_attribute18;
110 new_references.attribute19 := x_attribute19;
111 new_references.attribute20 := x_attribute20;
112 new_references.org_id := x_org_id;
113 new_references.insurance_cd := x_insurance_cd;
114 IF (p_action = 'UPDATE') THEN
115 new_references.creation_date := old_references.creation_date;
116 new_references.created_by := old_references.created_by;
117 ELSE
118 new_references.creation_date := x_creation_date;
119 new_references.created_by := x_created_by;
120 END IF;
121 new_references.last_update_date := x_last_update_date;
122 new_references.last_updated_by := x_last_updated_by;
123 new_references.last_update_login := x_last_update_login;
124
125 END Set_Column_Values;
126
127
128 PROCEDURE BeforeRowInsertUpdate(
129 p_inserting IN BOOLEAN,
130 p_updating IN BOOLEAN,
131 p_deleting IN BOOLEAN
132 ) as
133 ------------------------------------------------------------------------------------------
134 --Created by : vredkar
135 --Date created: 19-JUL-2005
136 --
137 --Purpose:
138 --Known limitations/enhancements and/or remarks:
139 --
140 --Change History:
141 --Who When What
142 ----------------------------------------------------------------------------------------------
143 CURSOR validate_brth_dt(cp_person_id NUMBER) IS
144 SELECT birth_date FROM
145 IGS_PE_PERSON_BASE_V
146 WHERE person_id = cp_person_id ;
147
148 l_bth_dt IGS_PE_PERSON_BASE_V.birth_date%TYPE;
149
150 BEGIN
151 IF p_inserting OR p_updating THEN
152 OPEN validate_brth_dt(new_references.person_id);
153 FETCH validate_brth_dt INTO l_bth_dt;
154 CLOSE validate_brth_dt;
155
156 IF new_references.END_DATE IS NOT NULL AND new_references.START_DATE > new_references.END_DATE THEN
157 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_DT_LE_END_DT');
158 IGS_GE_MSG_STACK.ADD;
159 APP_EXCEPTION.RAISE_EXCEPTION;
160
161 ELSIF l_bth_dt IS NOT NULL AND l_bth_dt > new_references.START_DATE THEN
162 FND_MESSAGE.SET_NAME('IGS','IGS_AD_STRT_DT_LESS_BIRTH_DT');
163 IGS_GE_MSG_STACK.ADD;
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 END IF;
166
167 END IF;
168
169
170 END BeforeRowInsertUpdate;
171
172 PROCEDURE Check_Constraints (
173 Column_Name IN VARCHAR2 ,
174 Column_Value IN VARCHAR2 ) AS
175 /***********************************************************
176
177 Created By : vvaitla
178
179 Date Created By : 2000/05/10
180
181 Purpose : To update,insert, add rows
182
183 Know limitations, enhancements or remarks
184
185 Change History
186
187 Who When What
188
189 ****************************************************************/
190 BEGIN
191
192 IF column_name IS NULL THEN
193 NULL;
194 NULL;
195 END IF;
196
197 END Check_Constraints;
198
199 PROCEDURE Check_Uniqueness AS
200 /***********************************************************
201
202 Created By : svisweas
203
204 Date Created By : 2000/05/17
205
206 Purpose : Check for uniqueness
207
208 Know limitations, enhancements or remarks
209
210 Change History
211
212 Who When What
213
214 ****************************************************************/
215 begin
216 IF Get_Uk_For_Validation (
217 new_references.insurance_cd --making id cd
218 ,new_references.start_date
219 ,new_references.person_id
220 ) THEN
221 Fnd_Message.Set_Name ('IGS', 'IGS_PE_HLTH_INS_DUP_EXISTS');
222 IGS_GE_MSG_STACK.ADD;
223 app_exception.raise_exception;
224 END IF;
225 END Check_Uniqueness ;
226
227
228
229 PROCEDURE Check_Parent_Existance AS
230 /***********************************************************
231
232 Created By : vvaitla
233
234 Date Created By : 2000/05/10
235
236 Purpose : To update,insert, add rows
237
238 Know limitations, enhancements or remarks
239
240 Change History
241
242 Who When What
243
244 ****************************************************************/
245 BEGIN
246
247 IF (((old_references.person_id = new_references.person_id)) OR
248 ((new_references.person_id IS NULL))) THEN
249 NULL;
250 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
251 new_references.person_id
252 ) THEN
253 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257
258 IF (((old_references.insurance_cd = new_references.insurance_cd)) OR -- making id cd
259 ((new_references.insurance_cd IS NULL))) THEN
260 NULL;
261 ELSIF NOT igs_lookups_view_pkg.Get_PK_For_Validation (
262 'PE_INS_TYPE',
263 new_references.insurance_cd
264 ) THEN
265 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
266 IGS_GE_MSG_STACK.ADD;
267 App_Exception.Raise_Exception;
268 END IF;
269
270 END Check_Parent_Existance;
271
272 FUNCTION Get_PK_For_Validation (
273 x_health_ins_id IN NUMBER
274 ) RETURN BOOLEAN AS
275 /***********************************************************
276
277 Created By : vvaitla
278
279 Date Created By : 2000/05/10
280
281 Purpose : To update,insert, add rows
282
283 Know limitations, enhancements or remarks
284
285 Change History
286
287 Who When What
288
289 ****************************************************************/
290
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM IGS_PE_HLTH_INS_ALL
294 WHERE health_ins_id = x_health_ins_id
295 FOR UPDATE NOWAIT;
296
297 lv_rowid cur_rowid%RowType;
298
299 BEGIN
300
301 Open cur_rowid;
302 Fetch cur_rowid INTO lv_rowid;
303 IF (cur_rowid%FOUND) THEN
304 Close cur_rowid;
305 Return(TRUE);
306 ELSE
307 Close cur_rowid;
308 Return(FALSE);
309 END IF;
310 END Get_PK_For_Validation;
311
312 FUNCTION Get_UK_For_Validation (
313 x_insurance_cd IN VARCHAR2, -- change id to cd
314 x_start_date IN DATE,
315 x_person_id IN NUMBER
316 ) RETURN BOOLEAN AS
317 /***********************************************************
318
319 Created By : svisweas
320
321 Date Created By : 2000/05/17
322
323 Purpose : Check for uniqueness
324
325 Know limitations, enhancements or remarks
326
327 Change History
328
329 Who When What
330
331 ****************************************************************/
332 CURSOR cur_rowid IS
333 SELECT rowid
334 FROM IGS_PE_HLTH_INS_ALL
335 WHERE insurance_cd = x_insurance_cd
336 AND start_date = x_start_date and person_id = x_person_id and ((l_rowid is null) or (rowid <> l_rowid))
337
338 ;
339 lv_rowid cur_rowid%RowType;
340
341 BEGIN
342
343 Open cur_rowid;
344 Fetch cur_rowid INTO lv_rowid;
345 IF (cur_rowid%FOUND) THEN
346 Close cur_rowid;
347 return (true);
348 ELSE
349 close cur_rowid;
350 return(false);
351 END IF;
352 END Get_UK_For_Validation ;
353
354
355
356 PROCEDURE Get_FK_Igs_Pe_Person (
357 x_person_id IN NUMBER
358 ) AS
359 /***********************************************************
360
361 Created By : vvaitla
362
363 Date Created By : 2000/05/10
364
365 Purpose : To update,insert, add rows
366
367 Know limitations, enhancements or remarks
368
369 Change History
370
371 Who When What
372
373 ****************************************************************/
374
375 CURSOR cur_rowid IS
376 SELECT rowid
377 FROM IGS_PE_HLTH_INS_ALL
378 WHERE person_id = x_person_id ;
379
380 lv_rowid cur_rowid%RowType;
381
382 BEGIN
383
384 Open cur_rowid;
385 Fetch cur_rowid INTO lv_rowid;
386 IF (cur_rowid%FOUND) THEN
387 Close cur_rowid;
388 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PHI_PP_FK');
389 IGS_GE_MSG_STACK.ADD;
390 App_Exception.Raise_Exception;
391 Return;
392 END IF;
393 Close cur_rowid;
394
395 END Get_FK_Igs_Pe_Person;
396
397 /* PROCEDURE Get_FK_Igs_Pe_Code_Classes (
398 x_code_classes_id IN NUMBER
399 ) AS
400
401
402 Created By : vvaitla
403
404 Date Created By : 2000/05/10
405
406 Purpose : To update,insert, add rows
407
408 Know limitations, enhancements or remarks
409
410 Change History
411
412 Who When What
413
414
415
416 CURSOR cur_rowid IS
417 SELECT rowid
418 FROM IGS_PE_HLTH_INS_ALL
419 WHERE insurance_id = x_code_classes_id ;
420
421 lv_rowid cur_rowid%RowType;
422
423 BEGIN
424
425 Open cur_rowid;
426 Fetch cur_rowid INTO lv_rowid;
427 IF (cur_rowid%FOUND) THEN
428 Close cur_rowid;
429 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PHI_PCC_FK');
430 IGS_GE_MSG_STACK.ADD;
431 App_Exception.Raise_Exception;
432 Return;
433 END IF;
434 Close cur_rowid;
435
436 END Get_FK_Igs_Pe_Code_Classes; */
437
438 PROCEDURE Before_DML (
439 p_action IN VARCHAR2,
440 x_rowid IN VARCHAR2 ,
441 x_health_ins_id IN NUMBER ,
442 x_person_id IN NUMBER ,
443 x_insurance_provider IN VARCHAR2 ,
444 x_policy_number IN VARCHAR2 ,
445 x_start_date IN DATE ,
446 x_end_date IN DATE ,
447 x_attribute_category IN VARCHAR2 ,
448 x_attribute1 IN VARCHAR2 ,
449 x_attribute2 IN VARCHAR2 ,
450 x_attribute3 IN VARCHAR2 ,
451 x_attribute4 IN VARCHAR2 ,
452 x_attribute5 IN VARCHAR2 ,
453 x_attribute6 IN VARCHAR2 ,
454 x_attribute7 IN VARCHAR2 ,
455 x_attribute8 IN VARCHAR2 ,
456 x_attribute9 IN VARCHAR2 ,
457 x_attribute10 IN VARCHAR2,
458 x_attribute11 IN VARCHAR2,
459 x_attribute12 IN VARCHAR2,
460 x_attribute13 IN VARCHAR2,
461 x_attribute14 IN VARCHAR2,
462 x_attribute15 IN VARCHAR2,
463 x_attribute16 IN VARCHAR2,
464 x_attribute17 IN VARCHAR2,
465 x_attribute18 IN VARCHAR2,
466 x_attribute19 IN VARCHAR2,
467 x_attribute20 IN VARCHAR2,
468 X_ORG_ID in NUMBER ,
469 x_creation_date IN DATE ,
470 x_created_by IN NUMBER ,
471 x_last_update_date IN DATE ,
472 x_last_updated_by IN NUMBER ,
473 x_last_update_login IN NUMBER,
474 x_insurance_cd IN VARCHAR2
475 ) AS
476 /***********************************************************
477
478 Created By : vvaitla
479
480 Date Created By : 2000/05/10
481
482 Purpose : To update,insert, add rows
483
484 Know limitations, enhancements or remarks
485
486 Change History
487
488 Who When What
489
490 ****************************************************************/
491 BEGIN
492
493 Set_Column_Values (
494 p_action,
495 x_rowid,
496 x_health_ins_id,
497 x_person_id,
498 x_insurance_provider,
499 x_policy_number,
500 x_start_date,
501 x_end_date,
502 x_attribute_category,
503 x_attribute1,
504 x_attribute2,
505 x_attribute3,
506 x_attribute4,
507 x_attribute5,
508 x_attribute6,
509 x_attribute7,
510 x_attribute8,
511 x_attribute9,
512 x_attribute10,
513 x_attribute11,
514 x_attribute12,
515 x_attribute13,
516 x_attribute14,
517 x_attribute15,
518 x_attribute16,
519 x_attribute17,
520 x_attribute18,
521 x_attribute19,
522 x_attribute20,
523 x_org_id,
524 x_creation_date,
525 x_created_by,
526 x_last_update_date,
527 x_last_updated_by,
528 x_last_update_login,
529 x_insurance_cd
530 );
531
532 IF (p_action = 'INSERT') THEN
533 -- Call all the procedures related to Before Insert.
534 BeforeRowInsertUpdate( TRUE, FALSE,FALSE );
535 Null;
536 IF Get_Pk_For_Validation(
537 new_references.health_ins_id) THEN
538 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
539 IGS_GE_MSG_STACK.ADD;
540 App_Exception.Raise_Exception;
541 END IF;
542 Check_Constraints;
543 Check_Uniqueness;
544 Check_Parent_Existance;
545 ELSIF (p_action = 'UPDATE') THEN
546 -- Call all the procedures related to Before Update.
547 BeforeRowInsertUpdate( FALSE,TRUE,FALSE );
548 Null;
549 Check_Constraints;
550 Check_Uniqueness;
551 Check_Parent_Existance;
552 ELSIF (p_action = 'DELETE') THEN
553 -- Call all the procedures related to Before Delete.
554 Null;
555 ELSIF (p_action = 'VALIDATE_INSERT') THEN
556 -- Call all the procedures related to Before Insert.
557 IF Get_PK_For_Validation (
558 new_references.health_ins_id) THEN
559 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception;
562 END IF;
563 Check_Constraints;
564 Check_Uniqueness;
565 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
566 Check_Constraints;
567 Check_Uniqueness;
568 ELSIF (p_action = 'VALIDATE_DELETE') THEN
569 Null;
570 END IF;
571
572 END Before_DML;
573
574 PROCEDURE After_DML (
575 p_action IN VARCHAR2,
576 x_rowid IN VARCHAR2
577 ) IS
578 /***********************************************************
579
580 Created By : vvaitla
581
582 Date Created By : 2000/05/10
583
584 Purpose : To update,insert, add rows
585
586 Know limitations, enhancements or remarks
587
588 Change History
589
590 Who When What
591
592 ****************************************************************/
593 BEGIN
594
595 l_rowid := x_rowid;
596
597 IF (p_action = 'INSERT') THEN
598 -- Call all the procedures related to After Insert.
599 Null;
600 ELSIF (p_action = 'UPDATE') THEN
601 -- Call all the procedures related to After Update.
602 Null;
603 ELSIF (p_action = 'DELETE') THEN
604 -- Call all the procedures related to After Delete.
605 Null;
606 END IF;
607
608 END After_DML;
609
610 procedure INSERT_ROW (
611 X_ROWID in out NOCOPY VARCHAR2,
612 x_HEALTH_INS_ID IN OUT NOCOPY NUMBER,
613 x_PERSON_ID IN NUMBER,
614 x_INSURANCE_PROVIDER IN VARCHAR2,
615 x_POLICY_NUMBER IN VARCHAR2,
616 x_START_DATE IN DATE,
617 x_END_DATE IN DATE,
618 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
619 x_ATTRIBUTE1 IN VARCHAR2,
620 x_ATTRIBUTE2 IN VARCHAR2,
621 x_ATTRIBUTE3 IN VARCHAR2,
622 x_ATTRIBUTE4 IN VARCHAR2,
623 x_ATTRIBUTE5 IN VARCHAR2,
624 x_ATTRIBUTE6 IN VARCHAR2,
625 x_ATTRIBUTE7 IN VARCHAR2,
626 x_ATTRIBUTE8 IN VARCHAR2,
627 x_ATTRIBUTE9 IN VARCHAR2,
628 x_ATTRIBUTE10 IN VARCHAR2,
629 x_ATTRIBUTE11 IN VARCHAR2,
630 x_ATTRIBUTE12 IN VARCHAR2,
631 x_ATTRIBUTE13 IN VARCHAR2,
632 x_ATTRIBUTE14 IN VARCHAR2,
633 x_ATTRIBUTE15 IN VARCHAR2,
634 x_ATTRIBUTE16 IN VARCHAR2,
635 x_ATTRIBUTE17 IN VARCHAR2,
636 x_ATTRIBUTE18 IN VARCHAR2,
637 x_ATTRIBUTE19 IN VARCHAR2,
638 x_ATTRIBUTE20 IN VARCHAR2,
639 X_ORG_ID in NUMBER,
640 X_MODE in VARCHAR2 ,
641 x_INSURANCE_CD IN VARCHAR2
642 ) AS
643 /***********************************************************
644
645 Created By : vvaitla
646
647 Date Created By : 2000/05/10
648
649 Purpose : To update,insert, add rows
650
651 Know limitations, enhancements or remarks
652
653 Change History
654
655 Who When What
656
657 ****************************************************************/
658 cursor C is select ROWID from IGS_PE_HLTH_INS_ALL
659 where HEALTH_INS_ID= X_HEALTH_INS_ID
660 ;
661 X_LAST_UPDATE_DATE DATE ;
662 X_LAST_UPDATED_BY NUMBER ;
663 X_LAST_UPDATE_LOGIN NUMBER ;
664 begin
665 X_LAST_UPDATE_DATE := SYSDATE;
666 if(X_MODE = 'I') then
667 X_LAST_UPDATED_BY := 1;
668 X_LAST_UPDATE_LOGIN := 0;
669 elsif (X_MODE IN ('R', 'S')) then
670 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
671 if X_LAST_UPDATED_BY is NULL then
672 X_LAST_UPDATED_BY := -1;
673 end if;
674 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
675 if X_LAST_UPDATE_LOGIN is NULL then
676 X_LAST_UPDATE_LOGIN := -1;
677 end if;
678 else
679 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
680 IGS_GE_MSG_STACK.ADD;
681 app_exception.raise_exception;
682 end if;
683 SELECT IGS_PE_HLTH_INS_S.NEXTVAL INTO X_HEALTH_INS_ID
684 FROM DUAL;
685
686 Before_DML(
687 p_action =>'INSERT',
688 x_rowid =>X_ROWID,
689 x_health_ins_id =>X_HEALTH_INS_ID,
690 x_person_id =>X_PERSON_ID,
691 x_insurance_provider =>X_INSURANCE_PROVIDER,
692 x_policy_number =>X_POLICY_NUMBER,
693 x_start_date =>X_START_DATE,
694 x_end_date =>X_END_DATE,
695 x_attribute_category =>X_ATTRIBUTE_CATEGORY,
696 x_attribute1 =>X_ATTRIBUTE1,
697 x_attribute2 =>X_ATTRIBUTE2,
698 x_attribute3 =>X_ATTRIBUTE3,
699 x_attribute4 =>X_ATTRIBUTE4,
700 x_attribute5 =>X_ATTRIBUTE5,
701 x_attribute6 =>X_ATTRIBUTE6,
702 x_attribute7 =>X_ATTRIBUTE7,
703 x_attribute8 =>X_ATTRIBUTE8,
704 x_attribute9 =>X_ATTRIBUTE9,
705 x_attribute10 =>X_ATTRIBUTE10,
706 x_attribute11 =>X_ATTRIBUTE11,
707 x_attribute12 =>X_ATTRIBUTE12,
708 x_attribute13 =>X_ATTRIBUTE13,
709 x_attribute14 =>X_ATTRIBUTE14,
710 x_attribute15 =>X_ATTRIBUTE15,
711 x_attribute16 =>X_ATTRIBUTE16,
712 x_attribute17 =>X_ATTRIBUTE17,
713 x_attribute18 =>X_ATTRIBUTE18,
714 x_attribute19 =>X_ATTRIBUTE19,
715 x_attribute20 =>X_ATTRIBUTE20,
716 x_org_id => igs_ge_gen_003.get_org_id,
717 x_creation_date =>X_LAST_UPDATE_DATE,
718 x_created_by =>X_LAST_UPDATED_BY,
719 x_last_update_date =>X_LAST_UPDATE_DATE,
720 x_last_updated_by =>X_LAST_UPDATED_BY,
721 x_last_update_login =>X_LAST_UPDATE_LOGIN,
722 x_insurance_cd => X_INSURANCE_CD);
723 IF (x_mode = 'S') THEN
724 igs_sc_gen_001.set_ctx('R');
725 END IF;
726 insert into IGS_PE_HLTH_INS_ALL (
727 HEALTH_INS_ID
728 ,PERSON_ID
729 ,INSURANCE_PROVIDER
730 ,POLICY_NUMBER
731 ,START_DATE
732 ,END_DATE
733 ,ATTRIBUTE_CATEGORY
734 ,ATTRIBUTE1
735 ,ATTRIBUTE2
736 ,ATTRIBUTE3
737 ,ATTRIBUTE4
738 ,ATTRIBUTE5
739 ,ATTRIBUTE6
740 ,ATTRIBUTE7
741 ,ATTRIBUTE8
742 ,ATTRIBUTE9
743 ,ATTRIBUTE10
744 ,ATTRIBUTE11
745 ,ATTRIBUTE12
746 ,ATTRIBUTE13
747 ,ATTRIBUTE14
748 ,ATTRIBUTE15
749 ,ATTRIBUTE16
750 ,ATTRIBUTE17
751 ,ATTRIBUTE18
752 ,ATTRIBUTE19
753 ,ATTRIBUTE20
754 ,ORG_ID
755 ,CREATION_DATE
756 ,CREATED_BY
757 ,LAST_UPDATE_DATE
758 ,LAST_UPDATED_BY
759 ,LAST_UPDATE_LOGIN
760 ,INSURANCE_CD
761 ) values (
762 NEW_REFERENCES.HEALTH_INS_ID
763 ,NEW_REFERENCES.PERSON_ID
764 ,NEW_REFERENCES.INSURANCE_PROVIDER
765 ,NEW_REFERENCES.POLICY_NUMBER
766 ,NEW_REFERENCES.START_DATE
767 ,NEW_REFERENCES.END_DATE
768 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
769 ,NEW_REFERENCES.ATTRIBUTE1
770 ,NEW_REFERENCES.ATTRIBUTE2
771 ,NEW_REFERENCES.ATTRIBUTE3
772 ,NEW_REFERENCES.ATTRIBUTE4
773 ,NEW_REFERENCES.ATTRIBUTE5
774 ,NEW_REFERENCES.ATTRIBUTE6
775 ,NEW_REFERENCES.ATTRIBUTE7
776 ,NEW_REFERENCES.ATTRIBUTE8
777 ,NEW_REFERENCES.ATTRIBUTE9
778 ,NEW_REFERENCES.ATTRIBUTE10
779 ,NEW_REFERENCES.ATTRIBUTE11
780 ,NEW_REFERENCES.ATTRIBUTE12
781 ,NEW_REFERENCES.ATTRIBUTE13
782 ,NEW_REFERENCES.ATTRIBUTE14
783 ,NEW_REFERENCES.ATTRIBUTE15
784 ,NEW_REFERENCES.ATTRIBUTE16
785 ,NEW_REFERENCES.ATTRIBUTE17
786 ,NEW_REFERENCES.ATTRIBUTE18
787 ,NEW_REFERENCES.ATTRIBUTE19
788 ,NEW_REFERENCES.ATTRIBUTE20
789 ,NEW_REFERENCES.ORG_ID
790 ,X_LAST_UPDATE_DATE
791 ,X_LAST_UPDATED_BY
792 ,X_LAST_UPDATE_DATE
793 ,X_LAST_UPDATED_BY
794 ,X_LAST_UPDATE_LOGIN
795 ,NEW_REFERENCES.INSURANCE_CD
796 );
797 IF (x_mode = 'S') THEN
798 igs_sc_gen_001.unset_ctx('R');
799 END IF;
800
801 open c;
802 fetch c into X_ROWID;
803 if (c%notfound) then
804 close c;
805 raise no_data_found;
806 end if;
807 close c;
808 After_DML (
809 p_action => 'INSERT' ,
810 x_rowid => X_ROWID );
811 EXCEPTION
812 WHEN OTHERS THEN
813 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
814 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
815 fnd_message.set_token ('ERR_CD', SQLCODE);
816 igs_ge_msg_stack.add;
817 igs_sc_gen_001.unset_ctx('R');
818 app_exception.raise_exception;
819 ELSE
820 igs_sc_gen_001.unset_ctx('R');
821 RAISE;
822 END IF;
823
824 end INSERT_ROW;
825
826 procedure LOCK_ROW (
827 X_ROWID in VARCHAR2,
828 x_HEALTH_INS_ID IN NUMBER,
829 x_PERSON_ID IN NUMBER,
830 x_INSURANCE_PROVIDER IN VARCHAR2,
831 x_POLICY_NUMBER IN VARCHAR2,
832 x_START_DATE IN DATE,
833 x_END_DATE IN DATE,
834 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
835 x_ATTRIBUTE1 IN VARCHAR2,
836 x_ATTRIBUTE2 IN VARCHAR2,
837 x_ATTRIBUTE3 IN VARCHAR2,
838 x_ATTRIBUTE4 IN VARCHAR2,
839 x_ATTRIBUTE5 IN VARCHAR2,
840 x_ATTRIBUTE6 IN VARCHAR2,
841 x_ATTRIBUTE7 IN VARCHAR2,
842 x_ATTRIBUTE8 IN VARCHAR2,
843 x_ATTRIBUTE9 IN VARCHAR2,
844 x_ATTRIBUTE10 IN VARCHAR2,
845 x_ATTRIBUTE11 IN VARCHAR2,
846 x_ATTRIBUTE12 IN VARCHAR2,
847 x_ATTRIBUTE13 IN VARCHAR2,
848 x_ATTRIBUTE14 IN VARCHAR2,
849 x_ATTRIBUTE15 IN VARCHAR2,
850 x_ATTRIBUTE16 IN VARCHAR2,
851 x_ATTRIBUTE17 IN VARCHAR2,
852 x_ATTRIBUTE18 IN VARCHAR2,
853 x_ATTRIBUTE19 IN VARCHAR2,
854 x_ATTRIBUTE20 IN VARCHAR2,
855 x_INSURANCE_CD IN VARCHAR2
856 ) AS
857 /***********************************************************
858
859 Created By : vvaitla
860
861 Date Created By : 2000/05/10
862
863 Purpose : To update,insert, add rows
864
865 Know limitations, enhancements or remarks
866
867 Change History
868
869 Who When What
870
871 ****************************************************************/
872 cursor c1 is select
873 PERSON_ID
874 , INSURANCE_PROVIDER
875 , POLICY_NUMBER
876 , START_DATE
877 , END_DATE
878 , ATTRIBUTE_CATEGORY
879 , ATTRIBUTE1
880 , ATTRIBUTE2
881 , ATTRIBUTE3
882 , ATTRIBUTE4
883 , ATTRIBUTE5
884 , ATTRIBUTE6
885 , ATTRIBUTE7
886 , ATTRIBUTE8
887 , ATTRIBUTE9
888 , ATTRIBUTE10
889 , ATTRIBUTE11
890 , ATTRIBUTE12
891 , ATTRIBUTE13
892 , ATTRIBUTE14
893 , ATTRIBUTE15
894 , ATTRIBUTE16
895 , ATTRIBUTE17
896 , ATTRIBUTE18
897 , ATTRIBUTE19
898 , ATTRIBUTE20
899 , INSURANCE_CD
900 from IGS_PE_HLTH_INS_ALL
901 where ROWID = X_ROWID
902 for update nowait;
903 tlinfo c1%rowtype;
904 begin
905 open c1;
906 fetch c1 into tlinfo;
907 if (c1%notfound) then
908 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
909 IGS_GE_MSG_STACK.ADD;
910 close c1;
911 app_exception.raise_exception;
912 return;
913 end if;
914 close c1;
915 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
916 AND (tlinfo.INSURANCE_PROVIDER = X_INSURANCE_PROVIDER)
917 AND (tlinfo.POLICY_NUMBER = X_POLICY_NUMBER)
918 AND (tlinfo.START_DATE = X_START_DATE)
919 AND ((tlinfo.END_DATE = X_END_DATE)
920 OR ((tlinfo.END_DATE is null)
921 AND (X_END_DATE is null)))
922
923
924 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
925 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
926 AND (X_ATTRIBUTE_CATEGORY is null)))
927 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
928 OR ((tlinfo.ATTRIBUTE1 is null)
929 AND (X_ATTRIBUTE1 is null)))
930 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
931 OR ((tlinfo.ATTRIBUTE2 is null)
932 AND (X_ATTRIBUTE2 is null)))
933 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
934 OR ((tlinfo.ATTRIBUTE3 is null)
935 AND (X_ATTRIBUTE3 is null)))
936 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
937 OR ((tlinfo.ATTRIBUTE4 is null)
938 AND (X_ATTRIBUTE4 is null)))
939 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
940 OR ((tlinfo.ATTRIBUTE5 is null)
941 AND (X_ATTRIBUTE5 is null)))
942 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
943 OR ((tlinfo.ATTRIBUTE6 is null)
944 AND (X_ATTRIBUTE6 is null)))
945 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
946 OR ((tlinfo.ATTRIBUTE7 is null)
947 AND (X_ATTRIBUTE7 is null)))
948 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
949 OR ((tlinfo.ATTRIBUTE8 is null)
950 AND (X_ATTRIBUTE8 is null)))
951 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
952 OR ((tlinfo.ATTRIBUTE9 is null)
953 AND (X_ATTRIBUTE9 is null)))
954 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
955 OR ((tlinfo.ATTRIBUTE10 is null)
956 AND (X_ATTRIBUTE10 is null)))
957 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
958 OR ((tlinfo.ATTRIBUTE11 is null)
959 AND (X_ATTRIBUTE11 is null)))
960 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
961 OR ((tlinfo.ATTRIBUTE12 is null)
962 AND (X_ATTRIBUTE12 is null)))
963 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
964 OR ((tlinfo.ATTRIBUTE13 is null)
965 AND (X_ATTRIBUTE13 is null)))
966 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
967 OR ((tlinfo.ATTRIBUTE14 is null)
968 AND (X_ATTRIBUTE14 is null)))
969 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
970 OR ((tlinfo.ATTRIBUTE15 is null)
971 AND (X_ATTRIBUTE15 is null)))
972 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
973 OR ((tlinfo.ATTRIBUTE16 is null)
974 AND (X_ATTRIBUTE16 is null)))
975 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
976 OR ((tlinfo.ATTRIBUTE17 is null)
977 AND (X_ATTRIBUTE17 is null)))
978 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
979 OR ((tlinfo.ATTRIBUTE18 is null)
980 AND (X_ATTRIBUTE18 is null)))
981 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
982 OR ((tlinfo.ATTRIBUTE19 is null)
983 AND (X_ATTRIBUTE19 is null)))
984 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR ((tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
985
986
987 AND (tlinfo.INSURANCE_CD = X_INSURANCE_CD)
988 ) then
989 null;
990 else
991 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
992 IGS_GE_MSG_STACK.ADD;
993 app_exception.raise_exception;
994 end if;
995 return;
996 end LOCK_ROW;
997
998 Procedure UPDATE_ROW (
999 X_ROWID in VARCHAR2,
1000 x_HEALTH_INS_ID IN NUMBER,
1001 x_PERSON_ID IN NUMBER,
1002 x_INSURANCE_PROVIDER IN VARCHAR2,
1003 x_POLICY_NUMBER IN VARCHAR2,
1004 x_START_DATE IN DATE,
1005 x_END_DATE IN DATE,
1006 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1007 x_ATTRIBUTE1 IN VARCHAR2,
1008 x_ATTRIBUTE2 IN VARCHAR2,
1009 x_ATTRIBUTE3 IN VARCHAR2,
1010 x_ATTRIBUTE4 IN VARCHAR2,
1011 x_ATTRIBUTE5 IN VARCHAR2,
1012 x_ATTRIBUTE6 IN VARCHAR2,
1013 x_ATTRIBUTE7 IN VARCHAR2,
1014 x_ATTRIBUTE8 IN VARCHAR2,
1015 x_ATTRIBUTE9 IN VARCHAR2,
1016 x_ATTRIBUTE10 IN VARCHAR2,
1017 x_ATTRIBUTE11 IN VARCHAR2,
1018 x_ATTRIBUTE12 IN VARCHAR2,
1019 x_ATTRIBUTE13 IN VARCHAR2,
1020 x_ATTRIBUTE14 IN VARCHAR2,
1021 x_ATTRIBUTE15 IN VARCHAR2,
1022 x_ATTRIBUTE16 IN VARCHAR2,
1023 x_ATTRIBUTE17 IN VARCHAR2,
1024 x_ATTRIBUTE18 IN VARCHAR2,
1025 x_ATTRIBUTE19 IN VARCHAR2,
1026 x_ATTRIBUTE20 IN VARCHAR2,
1027 X_MODE in VARCHAR2 ,
1028 x_INSURANCE_CD IN VARCHAR2
1029 ) AS
1030 /***********************************************************
1031
1032 Created By : vvaitla
1033
1034 Date Created By : 2000/05/10
1035
1036 Purpose : To update,insert, add rows
1037
1038 Know limitations, enhancements or remarks
1039
1040 Change History
1041
1042 Who When What
1043
1044 ****************************************************************/
1045 X_LAST_UPDATE_DATE DATE ;
1046 X_LAST_UPDATED_BY NUMBER ;
1047 X_LAST_UPDATE_LOGIN NUMBER ;
1048 begin
1049 X_LAST_UPDATE_DATE := SYSDATE;
1050 if(X_MODE = 'I') then
1051 X_LAST_UPDATED_BY := 1;
1052 X_LAST_UPDATE_LOGIN := 0;
1053 elsif (X_MODE IN ('R', 'S')) then
1054 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1055 if X_LAST_UPDATED_BY is NULL then
1056 X_LAST_UPDATED_BY := -1;
1057 end if;
1058 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1059 if X_LAST_UPDATE_LOGIN is NULL then
1060 X_LAST_UPDATE_LOGIN := -1;
1061 end if;
1062 else
1063 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1064 IGS_GE_MSG_STACK.ADD;
1065 app_exception.raise_exception;
1066 end if;
1067 Before_DML(
1068 p_action=>'UPDATE',
1069 x_rowid=>X_ROWID,
1070 x_health_ins_id=>X_HEALTH_INS_ID,
1071 x_person_id=>X_PERSON_ID,
1072 x_insurance_provider=>X_INSURANCE_PROVIDER,
1073 x_policy_number=>X_POLICY_NUMBER,
1074 x_start_date=>X_START_DATE,
1075 x_end_date=>X_END_DATE,
1076 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1077 x_attribute1=>X_ATTRIBUTE1,
1078 x_attribute2=>X_ATTRIBUTE2,
1079 x_attribute3=>X_ATTRIBUTE3,
1080 x_attribute4=>X_ATTRIBUTE4,
1081 x_attribute5=>X_ATTRIBUTE5,
1082 x_attribute6=>X_ATTRIBUTE6,
1083 x_attribute7=>X_ATTRIBUTE7,
1084 x_attribute8=>X_ATTRIBUTE8,
1085 x_attribute9=>X_ATTRIBUTE9,
1086 x_attribute10=>X_ATTRIBUTE10,
1087 x_attribute11=>X_ATTRIBUTE11,
1088 x_attribute12=>X_ATTRIBUTE12,
1089 x_attribute13=>X_ATTRIBUTE13,
1090 x_attribute14=>X_ATTRIBUTE14,
1091 x_attribute15=>X_ATTRIBUTE15,
1092 x_attribute16=>X_ATTRIBUTE16,
1093 x_attribute17=>X_ATTRIBUTE17,
1094 x_attribute18=>X_ATTRIBUTE18,
1095 x_attribute19=>X_ATTRIBUTE19,
1096 x_attribute20=>X_ATTRIBUTE20,
1097 x_creation_date=>X_LAST_UPDATE_DATE,
1098 x_created_by=>X_LAST_UPDATED_BY,
1099 x_last_update_date=>X_LAST_UPDATE_DATE,
1100 x_last_updated_by=>X_LAST_UPDATED_BY,
1101 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1102 x_insurance_cd=>X_INSURANCE_CD );
1103 IF (x_mode = 'S') THEN
1104 igs_sc_gen_001.set_ctx('R');
1105 END IF;
1106 update IGS_PE_HLTH_INS_ALL set
1107 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1108 INSURANCE_PROVIDER = NEW_REFERENCES.INSURANCE_PROVIDER,
1109 POLICY_NUMBER = NEW_REFERENCES.POLICY_NUMBER,
1110 START_DATE = NEW_REFERENCES.START_DATE,
1111 END_DATE = NEW_REFERENCES.END_DATE,
1112 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1113 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1114 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1115 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1116 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1117 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1118 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1119 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1120 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1121 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1122 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1123 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1124 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1125 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1126 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1127 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1128 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1129 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1130 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1131 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1132 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1133 INSURANCE_CD =NEW_REFERENCES.INSURANCE_CD,
1134 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1135 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1136 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1137 where ROWID = X_ROWID;
1138 if (sql%notfound) then
1139 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1140 igs_ge_msg_stack.add;
1141 igs_sc_gen_001.unset_ctx('R');
1142 app_exception.raise_exception;
1143 end if;
1144 IF (x_mode = 'S') THEN
1145 igs_sc_gen_001.unset_ctx('R');
1146 END IF;
1147
1148
1149 After_DML (
1150 p_action => 'UPDATE' ,
1151 x_rowid => X_ROWID
1152 );
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 IF (SQLCODE = (-28115)) THEN
1156 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1157 fnd_message.set_token ('ERR_CD', SQLCODE);
1158 igs_ge_msg_stack.add;
1159 igs_sc_gen_001.unset_ctx('R');
1160 app_exception.raise_exception;
1161 ELSE
1162 igs_sc_gen_001.unset_ctx('R');
1163 RAISE;
1164 END IF;
1165
1166 end UPDATE_ROW;
1167
1168 procedure ADD_ROW (
1169 X_ROWID in out NOCOPY VARCHAR2,
1170 x_HEALTH_INS_ID IN OUT NOCOPY NUMBER,
1171 x_PERSON_ID IN NUMBER,
1172 x_INSURANCE_PROVIDER IN VARCHAR2,
1173 x_POLICY_NUMBER IN VARCHAR2,
1174 x_START_DATE IN DATE,
1175 x_END_DATE IN DATE,
1176 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1177 x_ATTRIBUTE1 IN VARCHAR2,
1178 x_ATTRIBUTE2 IN VARCHAR2,
1179 x_ATTRIBUTE3 IN VARCHAR2,
1180 x_ATTRIBUTE4 IN VARCHAR2,
1181 x_ATTRIBUTE5 IN VARCHAR2,
1182 x_ATTRIBUTE6 IN VARCHAR2,
1183 x_ATTRIBUTE7 IN VARCHAR2,
1184 x_ATTRIBUTE8 IN VARCHAR2,
1185 x_ATTRIBUTE9 IN VARCHAR2,
1186 x_ATTRIBUTE10 IN VARCHAR2,
1187 x_ATTRIBUTE11 IN VARCHAR2,
1188 x_ATTRIBUTE12 IN VARCHAR2,
1189 x_ATTRIBUTE13 IN VARCHAR2,
1190 x_ATTRIBUTE14 IN VARCHAR2,
1191 x_ATTRIBUTE15 IN VARCHAR2,
1192 x_ATTRIBUTE16 IN VARCHAR2,
1193 x_ATTRIBUTE17 IN VARCHAR2,
1194 x_ATTRIBUTE18 IN VARCHAR2,
1195 x_ATTRIBUTE19 IN VARCHAR2,
1196 x_ATTRIBUTE20 IN VARCHAR2,
1197 X_ORG_ID in NUMBER,
1198 X_MODE in VARCHAR2,
1199 x_INSURANCE_CD IN VARCHAR2
1200 ) AS
1201 /***********************************************************
1202
1203 Created By : vvaitla
1204
1205 Date Created By : 2000/05/10
1206
1207 Purpose : To update,insert, add rows
1208
1209 Know limitations, enhancements or remarks
1210
1211 Change History
1212
1213 Who When What
1214
1215 ****************************************************************/
1216 cursor c1 is select ROWID from IGS_PE_HLTH_INS_ALL
1217 where HEALTH_INS_ID= X_HEALTH_INS_ID
1218 ;
1219 begin
1220 open c1;
1221 fetch c1 into X_ROWID;
1222 if (c1%notfound) then
1223 close c1;
1224 INSERT_ROW (
1225 X_ROWID,
1226 X_HEALTH_INS_ID,
1227 X_PERSON_ID,
1228 X_INSURANCE_PROVIDER,
1229 X_POLICY_NUMBER,
1230 X_START_DATE,
1231 X_END_DATE,
1232 X_ATTRIBUTE_CATEGORY,
1233 X_ATTRIBUTE1,
1234 X_ATTRIBUTE2,
1235 X_ATTRIBUTE3,
1236 X_ATTRIBUTE4,
1237 X_ATTRIBUTE5,
1238 X_ATTRIBUTE6,
1239 X_ATTRIBUTE7,
1240 X_ATTRIBUTE8,
1241 X_ATTRIBUTE9,
1242 X_ATTRIBUTE10,
1243 X_ATTRIBUTE11,
1244 X_ATTRIBUTE12,
1245 X_ATTRIBUTE13,
1246 X_ATTRIBUTE14,
1247 X_ATTRIBUTE15,
1248 X_ATTRIBUTE16,
1249 X_ATTRIBUTE17,
1250 X_ATTRIBUTE18,
1251 X_ATTRIBUTE19,
1252 X_ATTRIBUTE20,
1253 X_ORG_ID,
1254 X_MODE,
1255 X_INSURANCE_CD);
1256 return;
1257 end if;
1258 close c1;
1259 UPDATE_ROW (
1260 X_ROWID,
1261 X_HEALTH_INS_ID,
1262 X_PERSON_ID,
1263 X_INSURANCE_PROVIDER,
1264 X_POLICY_NUMBER,
1265 X_START_DATE,
1266 X_END_DATE,
1267 X_ATTRIBUTE_CATEGORY,
1268 X_ATTRIBUTE1,
1269 X_ATTRIBUTE2,
1270 X_ATTRIBUTE3,
1271 X_ATTRIBUTE4,
1272 X_ATTRIBUTE5,
1273 X_ATTRIBUTE6,
1274 X_ATTRIBUTE7,
1275 X_ATTRIBUTE8,
1276 X_ATTRIBUTE9,
1277 X_ATTRIBUTE10,
1278 X_ATTRIBUTE11,
1279 X_ATTRIBUTE12,
1280 X_ATTRIBUTE13,
1281 X_ATTRIBUTE14,
1282 X_ATTRIBUTE15,
1283 X_ATTRIBUTE16,
1284 X_ATTRIBUTE17,
1285 X_ATTRIBUTE18,
1286 X_ATTRIBUTE19,
1287 X_ATTRIBUTE20,
1288 X_MODE,
1289 X_INSURANCE_CD);
1290 end ADD_ROW;
1291
1292 procedure DELETE_ROW (
1293 X_ROWID in VARCHAR2,
1294 x_mode IN VARCHAR2
1295 ) AS
1296 /***********************************************************
1297
1298 Created By : vvaitla
1299
1300 Date Created By : 2000/05/10
1301
1302 Purpose : To update,insert, add rows
1303
1304 Know limitations, enhancements or remarks
1305
1306 Change History
1307
1308 Who When What
1309
1310 ****************************************************************/
1311 begin
1312 Before_DML (
1313 p_action => 'DELETE',
1314 x_rowid => X_ROWID
1315 );
1316 IF (x_mode = 'S') THEN
1317 igs_sc_gen_001.set_ctx('R');
1318 END IF;
1319 delete from IGS_PE_HLTH_INS_ALL
1320 where ROWID = X_ROWID;
1321 if (sql%notfound) then
1322 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1323 igs_ge_msg_stack.add;
1324 igs_sc_gen_001.unset_ctx('R');
1325 app_exception.raise_exception;
1326 end if;
1327 IF (x_mode = 'S') THEN
1328 igs_sc_gen_001.unset_ctx('R');
1329 END IF;
1330
1331 After_DML (
1332 p_action => 'DELETE',
1333 x_rowid => X_ROWID
1334 );
1335 end DELETE_ROW;
1336 END igs_pe_hlth_ins_pkg;