[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERS_DISABLTY_PKG
Source
1 PACKAGE BODY igs_pe_pers_disablty_pkg AS
2 /* $Header: IGSNI15B.pls 120.3 2005/10/17 02:18:42 appldev ship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 --
7 -- Bug ID : 2000408
8 -- who when what
9 -- CDCRUZ Sep 24,2002 New Col's added for
10 -- Person DLD / Pk changed to Seq Gen Pk
11 ------------------------------------------------------------------
12
13 l_rowid VARCHAR2(25);
14 old_references igs_pe_pers_disablty%RowType;
15 new_references igs_pe_pers_disablty%RowType;
16
17 PROCEDURE Set_Column_Values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,--DEFAULT NULL,
20 x_IGS_PE_PERS_DISABLTY_ID IN NUMBER ,--DEFAULT NULL,
21 x_person_id IN NUMBER,-- DEFAULT NULL,
22 x_disability_type IN VARCHAR2,-- DEFAULT NULL,
23 x_contact_ind IN VARCHAR2 ,--DEFAULT NULL,
24 x_special_allow_cd IN VARCHAR2,--DEFAULT NULL,
25 x_support_level_cd IN VARCHAR2 ,--DEFAULT NULL,
26 x_documented IN VARCHAR2,-- DEFAULT NULL,
27 x_special_service_id IN NUMBER ,--DEFAULT NULL,
28 x_attribute_category IN VARCHAR2 ,--DEFAULT NULL,
29 x_attribute1 IN VARCHAR2 ,--DEFAULT NULL,
30 x_attribute2 IN VARCHAR2 ,--DEFAULT NULL,
31 x_attribute3 IN VARCHAR2 ,--DEFAULT NULL,
32 x_attribute4 IN VARCHAR2 ,--DEFAULT NULL,
33 x_attribute5 IN VARCHAR2 ,--DEFAULT NULL,
34 x_attribute6 IN VARCHAR2 ,--DEFAULT NULL,
35 x_attribute7 IN VARCHAR2 ,--DEFAULT NULL,
36 x_attribute8 IN VARCHAR2 ,--DEFAULT NULL,
37 x_attribute9 IN VARCHAR2 ,--DEFAULT NULL,
38 x_attribute10 IN VARCHAR2,-- DEFAULT NULL,
39 x_attribute11 IN VARCHAR2,-- DEFAULT NULL,
40 x_attribute12 IN VARCHAR2,-- DEFAULT NULL,
41 x_attribute13 IN VARCHAR2,-- DEFAULT NULL,
42 x_attribute14 IN VARCHAR2,-- DEFAULT NULL,
43 x_attribute15 IN VARCHAR2,-- DEFAULT NULL,
44 x_attribute16 IN VARCHAR2,-- DEFAULT NULL,
45 x_attribute17 IN VARCHAR2,-- DEFAULT NULL,
46 x_attribute18 IN VARCHAR2,-- DEFAULT NULL,
47 x_attribute19 IN VARCHAR2,-- DEFAULT NULL,
48 x_attribute20 IN VARCHAR2,-- DEFAULT NULL,
49 X_ELIG_EARLY_REG_IND IN VARCHAR2 ,--DEFAULT NULL,
50 X_START_DATE IN DATE ,--DEFAULT NULL,
51 X_END_DATE IN DATE ,--DEFAULT NULL,
52 X_INFO_SOURCE IN VARCHAR2,-- DEFAULT NULL,
53 X_INTERVIEWER_ID IN NUMBER ,--DEFAULT NULL,
54 X_INTERVIEWER_DATE IN DATE ,--DEFAULT NULL,
55 x_creation_date IN DATE ,--DEFAULT NULL,
56 x_created_by IN NUMBER ,--DEFAULT NULL,
57 x_last_update_date IN DATE,-- DEFAULT NULL,
58 x_last_updated_by IN NUMBER ,--DEFAULT NULL,
59 x_last_update_login IN NUMBER --DEFAULT NULL
60 ) AS
61
62 /*************************************************************
63 Created By :
64 Date Created By :
65 Purpose :
66 Know limitations, enhancements or remarks
67 Change History
68 Who When What
69 sraj 2000/05/05 the table structure has been changed
70 (reverse chronological order - newest change first)
71 ***************************************************************/
72
73 CURSOR cur_old_ref_values IS
74 SELECT *
75 FROM IGS_PE_PERS_DISABLTY
76 WHERE rowid = x_rowid;
77
78 BEGIN
79
80 l_rowid := x_rowid;
81
82 -- Code for setting the Old and New Reference Values.
83 -- Populate Old Values.
84 Open cur_old_ref_values;
85 Fetch cur_old_ref_values INTO old_references;
86 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
87 Close cur_old_ref_values;
88 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
89 IGS_GE_MSG_STACK.ADD;
90 App_Exception.Raise_Exception;
91 Return;
92 END IF;
93 Close cur_old_ref_values;
94
95 -- Populate New Values.
96 new_references.person_id := x_person_id;
97 NEW_REFERENCES.IGS_PE_PERS_DISABLTY_ID := X_IGS_PE_PERS_DISABLTY_ID ;
98 new_references.disability_type := x_disability_type;
99 new_references.contact_ind := x_contact_ind;
100 new_references.special_allow_cd := x_special_allow_cd;
101 new_references.support_level_cd := x_support_level_cd;
102 new_references.documented := x_documented;
103 new_references.special_service_id := x_special_service_id;
104 new_references.attribute_category := x_attribute_category;
105 new_references.attribute1 := x_attribute1;
106 new_references.attribute2 := x_attribute2;
107 new_references.attribute3 := x_attribute3;
108 new_references.attribute4 := x_attribute4;
109 new_references.attribute5 := x_attribute5;
110 new_references.attribute6 := x_attribute6;
111 new_references.attribute7 := x_attribute7;
112 new_references.attribute8 := x_attribute8;
113 new_references.attribute9 := x_attribute9;
114 new_references.attribute10 := x_attribute10;
115 new_references.attribute11 := x_attribute11;
116 new_references.attribute12 := x_attribute12;
117 new_references.attribute13 := x_attribute13;
118 new_references.attribute14 := x_attribute14;
119 new_references.attribute15 := x_attribute15;
120 new_references.attribute16 := x_attribute16;
121 new_references.attribute17 := x_attribute17;
122 new_references.attribute18 := x_attribute18;
123 new_references.attribute19 := x_attribute19;
124 new_references.attribute20 := x_attribute20;
125 NEW_REFERENCES.ELIG_EARLY_REG_IND := X_ELIG_EARLY_REG_IND ;
126 NEW_REFERENCES.START_DATE := X_START_DATE ;
127 NEW_REFERENCES.END_DATE := X_END_DATE ;
128 NEW_REFERENCES.INFO_SOURCE := X_INFO_SOURCE ;
129 NEW_REFERENCES.INTERVIEWER_ID := X_INTERVIEWER_ID ;
130 NEW_REFERENCES.INTERVIEWER_DATE := X_INTERVIEWER_DATE ;
131
132 IF (p_action = 'UPDATE') THEN
133 new_references.creation_date := old_references.creation_date;
134 new_references.created_by := old_references.created_by;
135 ELSE
136 new_references.creation_date := x_creation_date;
137 new_references.created_by := x_created_by;
138 END IF;
139 new_references.last_update_date := x_last_update_date;
140 new_references.last_updated_by := x_last_updated_by;
141 new_references.last_update_login := x_last_update_login;
142
143 END Set_Column_Values;
144
145 PROCEDURE check_uniqueness AS
146 /*
147 || Created By : cdcruz
148 || Created On : 21-SEP-2001
149 || Purpose : Handles the Unique Constraint logic defined for the columns.
150 || Known limitations, enhancements or remarks :
151 || Change History :
152 || Who When What
153 || (reverse chronological order - newest change first)
154 */
155 BEGIN
156
157 IF ( get_uk_for_validation (
158 new_references.person_id,
159 new_references.disability_type,
160 new_references.start_date
161 )
162 ) THEN
163 fnd_message.set_name ('IGS', 'IGS_PE_SPECIAL_DUP_EXISTS');
164 igs_ge_msg_stack.add;
165 app_exception.raise_exception;
166 END IF;
167
168 END check_uniqueness;
169
170 PROCEDURE BeforeRowInsertUpdate1
171 AS
172 /*************************************************************
173 Created By :
174 Date Created By :
175 Purpose :
176 Know limitations, enhancements or remarks
177 Change History
178 Who When What
179 sraj 2000/05/05 the table structure has been changed
180 kumma 06-JUN-2002 Commented the call to enrp_val_pd_contact,
181 as the validation code is now present in pld, bug 2381245
182 (reverse chronological order - newest change first)
183 ***************************************************************/
184
185
186 CURSOR cur_contact IS
187 SELECT 'Y' contact_ind
188 FROM igs_pe_sn_contact
189 WHERE disability_id = new_references.igs_pe_pers_disablty_id;
190 cur_contact_rec cur_contact%ROWTYPE;
191 l_contact_ind VARCHAR2(1);
192 -- End of fix
193
194 v_message_name varchar2(30);
195
196 BEGIN
197
198 -- Validate DISABILITY TYPE.
199
200 -- Closed indicator.
201
202 IF new_references.disability_type IS NOT NULL AND
203
204 (NVL(old_references.disability_type, 'NULL') <> new_references.disability_type) THEN
205
206 IF IGS_EN_VAL_PDI.enrp_val_dit_closed (
207 new_references.disability_type,
208 v_message_name ) = FALSE THEN
209
210 FND_MESSAGE.SET_NAME('IGS', V_MESSAGE_NAME);
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213
214 END IF;
215
216 END IF;
217
218 END BeforeRowInsertUpdate1;
219
220
221
222 PROCEDURE Check_Constraints (
223 Column_Name IN VARCHAR2 ,
224 Column_Value IN VARCHAR2 ) AS
225 /*************************************************************
226 Created By :
227 Date Created By :
228 Purpose :
229 Know limitations, enhancements or remarks
230 Change History
231 Who When What
232 sraj 2000/05/05 the table structure has been changed
233 (reverse chronological order - newest change first)
234 ***************************************************************/
235
236 BEGIN
237
238 IF column_name IS NULL THEN
239 NULL;
240 ELSIF UPPER(column_name) = 'DOCUMENTED' THEN
241 new_references.documented := column_value;
242 ELSIF UPPER(column_name) = 'DISABILITY_TYPE' THEN
243 new_references.disability_type := column_value;
244 END IF;
245
246 IF UPPER(Column_Name) = 'DISABILITY_TYPE' OR
247 Column_Name IS NULL THEN
248 IF new_references.DISABILITY_TYPE <> UPPER(new_references.disability_type) THEN
249 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254
255
256 END Check_Constraints;
257
258 PROCEDURE Check_Child_Existance AS
259 BEGIN
260
261 igs_pe_sn_contact_pkg.get_fk_igs_pe_pers_disablty (
262 old_references.IGS_PE_PERS_DISABLTY_ID
263 );
264
265 igs_pe_sn_service_pkg.get_fk_igs_pe_pers_disablty (
266 old_references.IGS_PE_PERS_DISABLTY_ID
267 );
268
269 END Check_Child_Existance;
270
271 PROCEDURE Check_Parent_Existance AS
272 /*************************************************************
273 Created By :
274 Date Created By :
275 Purpose :
276 Know limitations, enhancements or remarks
277 Change History
278 Who When What
279 sraj 2000/05/05 the table structure has been changed
280 (reverse chronological order - newest change first)
281 ***************************************************************/
282
283 BEGIN
284
285 IF (((old_references.special_allow_cd = new_references.special_allow_cd)) OR
286 ((new_references.special_allow_cd IS NULL))) THEN
287 NULL;
288 ELSIF NOT Igs_lookups_view_pkg.Get_PK_For_Validation (
289 'PE_SN_ALLOW',new_references.special_allow_cd
290 ) THEN
291 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295
296 IF (((old_references.support_level_cd = new_references.support_level_cd)) OR
297 ((new_references.support_level_cd IS NULL))) THEN
298 NULL;
299 ELSIF NOT Igs_lookups_view_pkg.Get_PK_For_Validation (
300 'PE_SN_ADD_SUP_LVL',new_references.support_level_cd
301 ) THEN
302 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
303 IGS_GE_MSG_STACK.ADD;
304 App_Exception.Raise_Exception;
305 END IF;
306
307
308 IF (((old_references.disability_type = new_references.disability_type)) OR
309 ((new_references.disability_type IS NULL))) THEN
310 NULL;
311 ELSIF NOT Igs_Ad_Disbl_Type_Pkg.Get_PK_For_Validation (
312 new_references.disability_type ,
313 'N'
314 ) THEN
315 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
316 IGS_GE_MSG_STACK.ADD;
317 App_Exception.Raise_Exception;
318 END IF;
319
320 IF (((old_references.person_id = new_references.person_id)) OR
321 ((new_references.person_id IS NULL))) THEN
322 NULL;
323 ELSE
324 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
325 new_references.person_id
326 ) THEN
327 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 END IF;
331 END IF;
332
333
334 IF (((old_references.interviewer_id = new_references.interviewer_id)) OR
335 ((new_references.interviewer_id IS NULL))) THEN
336 NULL;
337 ELSE
338 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
339 new_references.interviewer_id
340 ) THEN
341 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END IF;
345 END IF;
346
347
348 END Check_Parent_Existance;
349
350 FUNCTION Get_PK_For_Validation (
351 X_IGS_PE_PERS_DISABLTY_ID IN NUMBER
352 ) RETURN BOOLEAN AS
353
354 /*************************************************************
355 Created By :
356 Date Created By :
357 Purpose :
358 Know limitations, enhancements or remarks
359 Change History
360 Who When What
361 sraj 2000/05/05 the table structure has been changed
362 (reverse chronological order - newest change first)
363 ***************************************************************/
364
365 CURSOR cur_rowid IS
366 SELECT rowid
367 FROM igs_pe_pers_disablty
368 WHERE IGS_PE_PERS_DISABLTY_ID = X_IGS_PE_PERS_DISABLTY_ID
369 FOR UPDATE NOWAIT;
370
371 lv_rowid cur_rowid%RowType;
372
373 BEGIN
374
375 Open cur_rowid;
376 Fetch cur_rowid INTO lv_rowid;
377 IF (cur_rowid%FOUND) THEN
378 Close cur_rowid;
379 Return(TRUE);
380 ELSE
381 Close cur_rowid;
382 Return(FALSE);
383 END IF;
384 END Get_PK_For_Validation;
385
386 FUNCTION get_uk_for_validation (
387 x_person_id IN NUMBER,
388 x_disability_type IN VARCHAR2,
389 x_start_date IN DATE
390 ) RETURN BOOLEAN AS
391 /*
392 || Created By : cdcruz
393 || Created On : 21-SEP-2001
394 || Purpose : Validates the Unique Keys of the table.
395 || Known limitations, enhancements or remarks :
396 || Change History :
397 || Who When What
398 || (reverse chronological order - newest change first)
399 */
400 CURSOR cur_rowid IS
401 SELECT rowid
402 FROM igs_pe_pers_disablty
403 WHERE person_id = x_person_id
404 AND disability_type = x_disability_type
405 AND ( (start_date = x_start_date) OR (start_date IS NULL and x_start_date IS NULL ))
406 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
407
408 lv_rowid cur_rowid%RowType;
409
410 BEGIN
411
412 OPEN cur_rowid;
413 FETCH cur_rowid INTO lv_rowid;
414 IF (cur_rowid%FOUND) THEN
415 CLOSE cur_rowid;
416 RETURN (true);
417 ELSE
418 CLOSE cur_rowid;
419 RETURN(FALSE);
420 END IF;
421
422 END get_uk_for_validation ;
423
424 PROCEDURE Get_FK_Igs_Pe_Person (
425 x_person_id IN NUMBER
426 ) AS
427
428 /*************************************************************
429 Created By :
430 Date Created By :
431 Purpose :
432 Know limitations, enhancements or remarks
433 Change History
434 Who When What
435 sraj 2000/05/05 the table structure has been changed
436 (reverse chronological order - newest change first)
437 ***************************************************************/
438
439 CURSOR cur_rowid IS
440 SELECT rowid
441 FROM igs_pe_pers_disablty
442 WHERE (person_id = x_person_id or INTERVIEWER_ID = x_person_id );
443
444 lv_rowid cur_rowid%RowType;
445
446 BEGIN
447
448 Open cur_rowid;
449 Fetch cur_rowid INTO lv_rowid;
450 IF (cur_rowid%FOUND) THEN
451 Close cur_rowid;
452 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PD_PE_FK');
453 IGS_GE_MSG_STACK.ADD;
454 App_Exception.Raise_Exception;
455 Return;
456 END IF;
457 Close cur_rowid;
458
459 END Get_FK_Igs_Pe_Person;
460
461 PROCEDURE Get_FK_Igs_Ad_Disbl_Type (
462 x_disability_type IN VARCHAR2
463 ) AS
464
465 /*************************************************************
466 Created By :
467 Date Created By :
468 Purpose :
469 Know limitations, enhancements or remarks
470 Change History
471 Who When What
472 sraj 2000/05/05 the table structure has been changed
473 (reverse chronological order - newest change first)
474 ***************************************************************/
475
476 CURSOR cur_rowid IS
477 SELECT rowid
478 FROM igs_pe_pers_disablty
479 WHERE disability_type = x_disability_type ;
480
481 lv_rowid cur_rowid%RowType;
482
483 BEGIN
484
485 Open cur_rowid;
486 Fetch cur_rowid INTO lv_rowid;
487 IF (cur_rowid%FOUND) THEN
488 Close cur_rowid;
489 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PD_DIT_FK');
490 IGS_GE_MSG_STACK.ADD;
491 App_Exception.Raise_Exception;
492 Return;
493 END IF;
494 Close cur_rowid;
495
496 END Get_FK_Igs_Ad_Disbl_Type;
497
498 PROCEDURE beforerowinsertupdate(p_inserting BOOLEAN,p_updating BOOLEAN) AS
499 /*
500 || Created By : pkpatel
501 || Created On : 30-MAY-2003
502 || Purpose : Special Needs CCR.
503 || Other records and None special need records can coexist together, but should not overlap eachother at any time.
504 || Known limitations, enhancements or remarks :
505 || Change History :
506 || Who When What
507 || pkpatel 16-Jul-2005 Bug 4327807 (Person SS Enhancement)
508 || Added validations related to dates
509 || (reverse chronological order - newest change first)
510 */
511 l_default_end_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
512 l_default_start_date DATE := TO_DATE('1000/01/01','YYYY/MM/DD');
513 l_count NUMBER ;
514 l_govt_disability_type igs_ad_disbl_type.govt_disability_type%TYPE;
515
516 CURSOR govt_disability_type_cur(cp_disability_type igs_ad_disbl_type.disability_type%TYPE) IS
517 SELECT govt_disability_type
518 FROM igs_ad_disbl_type
519 WHERE disability_type = cp_disability_type;
520
521 CURSOR none_cur (cp_person_id igs_pe_pers_disablty.person_id%TYPE,
522 cp_start_date igs_pe_pers_disablty.start_date%TYPE,
523 cp_end_date igs_pe_pers_disablty.end_date%TYPE,
524 cp_govt_disability_type igs_ad_disbl_type.govt_disability_type%TYPE)
525 IS
526 SELECT 1
527 FROM igs_pe_pers_disablty pdi,
528 igs_ad_disbl_type dit
529 WHERE person_id = cp_person_id
530 AND dit.disability_type = pdi.disability_type
531 AND dit.govt_disability_type = cp_govt_disability_type
532 AND (
533 NVL(cp_end_date,l_default_end_date) BETWEEN NVL(start_date+1,l_default_start_date) AND NVL(end_date-1,l_default_end_date)
534 OR NVL(cp_start_date,l_default_start_date) BETWEEN NVL(start_date+1,l_default_start_date) AND NVL(end_date-1,l_default_end_date)
535 OR ( NVL(cp_start_date,l_default_start_date) <= NVL(start_date,l_default_start_date) AND
536 NVL(end_date,l_default_end_date) <= NVL(cp_end_date,l_default_end_date))
537 );
538
539
540 CURSOR not_none_cur (cp_person_id igs_pe_pers_disablty.person_id%TYPE,
541 cp_start_date igs_pe_pers_disablty.start_date%TYPE,
542 cp_end_date igs_pe_pers_disablty.end_date%TYPE,
543 cp_govt_disability_type igs_ad_disbl_type.govt_disability_type%TYPE)
544 IS
545 SELECT 1
546 FROM igs_pe_pers_disablty pdi,
547 igs_ad_disbl_type dit
548 WHERE person_id = cp_person_id
549 AND dit.disability_type = pdi.disability_type
550 AND dit.govt_disability_type <> cp_govt_disability_type
551 AND (
552 NVL(cp_end_date,l_default_end_date) BETWEEN NVL(start_date+1,l_default_start_date) AND NVL(end_date-1,l_default_end_date)
553 OR NVL(cp_start_date,l_default_start_date) BETWEEN NVL(start_date+1,l_default_start_date) AND NVL(end_date-1,l_default_end_date)
554 OR ( NVL(cp_start_date,l_default_start_date) <= NVL(start_date,l_default_start_date) AND
555 NVL(end_date,l_default_end_date) <= NVL(cp_end_date,l_default_end_date))
556 );
557
558 CURSOR get_dob_dt_cur(cp_person_id igs_pe_passport.person_id%TYPE)
559 IS
560 SELECT birth_date
561 FROM igs_pe_person_base_v
562 WHERE person_id = cp_person_id;
563
564 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
565 BEGIN
566
567 IF new_references.start_date > new_references.end_date THEN
568 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_FROM_DT_GRT_TO_DATE');
569 IGS_GE_MSG_STACK.ADD;
570 APP_EXCEPTION.RAISE_EXCEPTION;
571 END IF;
572
573 OPEN get_dob_dt_cur(new_references.person_id);
574 FETCH get_dob_dt_cur INTO l_birth_dt;
575 CLOSE get_dob_dt_cur;
576
577 IF l_birth_dt IS NOT NULL AND new_references.start_date IS NOT NULL THEN
578 IF l_birth_dt > new_references.start_date THEN
579 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
580 IGS_GE_MSG_STACK.ADD;
581 APP_EXCEPTION.RAISE_EXCEPTION;
582 END IF;
583 END IF;
584
585 IF new_references.start_date IS NULL AND new_references.end_date IS NOT NULL THEN
586 FND_MESSAGE.SET_NAME( 'IGS','IGS_PE_CANT_SPECIFY_FROM_DATE');
587 IGS_GE_MSG_STACK.ADD;
588 APP_EXCEPTION.RAISE_EXCEPTION;
589 END IF;
590
591 IF new_references.start_date IS NOT NULL AND new_references.end_date IS NOT NULL THEN
592 IF new_references.end_date < new_references.start_date THEN
593 FND_MESSAGE.SET_NAME( 'IGS','IGS_PE_FROM_DT_GRT_TO_DATE');
594 IGS_GE_MSG_STACK.ADD;
595 APP_EXCEPTION.RAISE_EXCEPTION;
596 END IF;
597 END IF;
598
599 IF p_inserting OR (p_updating AND
600 (NVL(old_references.start_date,l_default_start_date) <> NVL(new_references.start_date,l_default_start_date) OR
601 NVL(old_references.end_date,l_default_end_date) <> NVL(new_references.end_date,l_default_end_date))
602 ) THEN
603
604
605 OPEN govt_disability_type_cur(new_references.disability_type);
606 FETCH govt_disability_type_cur INTO l_govt_disability_type;
607 CLOSE govt_disability_type_cur;
608
609 IF l_govt_disability_type = 'NONE' THEN
610 -- Check whether other special need Records overlapped with the 'None' exists.
611
612 OPEN not_none_cur(new_references.person_id,
613 new_references.start_date,
614 new_references.end_date,
615 'NONE');
616 FETCH not_none_cur INTO l_count;
617
618 IF not_none_cur%FOUND THEN
619 CLOSE not_none_cur;
620 FND_MESSAGE.SET_NAME( 'IGS','IGS_PE_NO_NONE_SN');
621 IGS_GE_MSG_STACK.ADD;
622 APP_EXCEPTION.RAISE_EXCEPTION;
623 END IF;
624 CLOSE not_none_cur;
625
626 ELSE
627 -- Check whether 'None' special need Records overlapped with other record exists.
628
629 OPEN none_cur(new_references.person_id,
630 new_references.start_date,
631 new_references.end_date,
632 'NONE');
633 FETCH none_cur INTO l_count;
634
635 IF none_cur%FOUND THEN
636 CLOSE none_cur;
637 FND_MESSAGE.SET_NAME( 'IGS','IGS_EN_PRSN_NOTHAVE_DIABREC');
638 IGS_GE_MSG_STACK.ADD;
639 APP_EXCEPTION.RAISE_EXCEPTION;
640 END IF;
641 CLOSE none_cur;
642
643 END IF;
644 END IF;
645 END beforerowinsertupdate;
646
647 PROCEDURE Before_DML (
648 p_action IN VARCHAR2,
649 x_rowid IN VARCHAR2 ,--DEFAULT NULL,
650 X_IGS_PE_PERS_DISABLTY_ID IN NUMBER ,--DEFAULT NULL,
651 x_person_id IN NUMBER ,--DEFAULT NULL,
652 x_disability_type IN VARCHAR2,-- DEFAULT NULL,
653 x_contact_ind IN VARCHAR2,-- DEFAULT NULL,
654 x_special_allow_cd IN VARCHAR2 ,--DEFAULT NULL,
655 x_support_level_cd IN VARCHAR2 ,--DEFAULT NULL,
656 x_documented IN VARCHAR2 ,--DEFAULT NULL,
657 x_special_service_id IN NUMBER,-- DEFAULT NULL,
658 x_attribute_category IN VARCHAR2 ,--DEFAULT NULL,
659 x_attribute1 IN VARCHAR2 ,--DEFAULT NULL,
660 x_attribute2 IN VARCHAR2 ,--DEFAULT NULL,
661 x_attribute3 IN VARCHAR2 ,--DEFAULT NULL,
662 x_attribute4 IN VARCHAR2 ,--DEFAULT NULL,
663 x_attribute5 IN VARCHAR2 ,--DEFAULT NULL,
664 x_attribute6 IN VARCHAR2 ,--DEFAULT NULL,
665 x_attribute7 IN VARCHAR2 ,--DEFAULT NULL,
666 x_attribute8 IN VARCHAR2 ,--DEFAULT NULL,
667 x_attribute9 IN VARCHAR2 ,--DEFAULT NULL,
668 x_attribute10 IN VARCHAR2,-- DEFAULT NULL,
669 x_attribute11 IN VARCHAR2,-- DEFAULT NULL,
670 x_attribute12 IN VARCHAR2,-- DEFAULT NULL,
671 x_attribute13 IN VARCHAR2,-- DEFAULT NULL,
672 x_attribute14 IN VARCHAR2,-- DEFAULT NULL,
673 x_attribute15 IN VARCHAR2,-- DEFAULT NULL,
674 x_attribute16 IN VARCHAR2,-- DEFAULT NULL,
675 x_attribute17 IN VARCHAR2,-- DEFAULT NULL,
676 x_attribute18 IN VARCHAR2,-- DEFAULT NULL,
677 x_attribute19 IN VARCHAR2,-- DEFAULT NULL,
678 x_attribute20 IN VARCHAR2,-- DEFAULT NULL,
679 X_ELIG_EARLY_REG_IND IN VARCHAR2,-- DEFAULT NULL,
680 X_START_DATE IN DATE ,--DEFAULT NULL,
681 X_END_DATE IN DATE,-- DEFAULT NULL,
682 X_INFO_SOURCE IN VARCHAR2,-- DEFAULT NULL,
683 X_INTERVIEWER_ID IN NUMBER,-- DEFAULT NULL,
684 X_INTERVIEWER_DATE IN DATE ,--DEFAULT NULL,
685 x_creation_date IN DATE ,--DEFAULT NULL,
686 x_created_by IN NUMBER,-- DEFAULT NULL,
687 x_last_update_date IN DATE ,--DEFAULT NULL,
688 x_last_updated_by IN NUMBER ,--DEFAULT NULL,
689 x_last_update_login IN NUMBER-- DEFAULT NULL
690 ) AS
691 /*************************************************************
692 Created By :
693 Date Created By :
694 Purpose :
695 Know limitations, enhancements or remarks
696 Change History
697 Who When What
698 sraj 2000/05/05 the table structure has been changed
699 (reverse chronological order - newest change first)
700 ***************************************************************/
701
702 BEGIN
703
704 Set_Column_Values (
705 p_action,
706 x_rowid,
707 X_IGS_PE_PERS_DISABLTY_ID,
708 x_person_id,
709 x_disability_type,
710 x_contact_ind,
711 x_special_allow_cd,
712 x_support_level_cd,
713 x_documented,
714 x_special_service_id,
715 x_attribute_category,
716 x_attribute1,
717 x_attribute2,
718 x_attribute3,
719 x_attribute4,
720 x_attribute5,
721 x_attribute6,
722 x_attribute7,
723 x_attribute8,
724 x_attribute9,
725 x_attribute10,
726 x_attribute11,
727 x_attribute12,
728 x_attribute13,
729 x_attribute14,
730 x_attribute15,
731 x_attribute16,
732 x_attribute17,
733 x_attribute18,
734 x_attribute19,
735 x_attribute20,
736 X_ELIG_EARLY_REG_IND ,
737 X_START_DATE ,
738 X_END_DATE ,
739 X_INFO_SOURCE ,
740 X_INTERVIEWER_ID ,
741 X_INTERVIEWER_DATE ,
742 x_creation_date,
743 x_created_by,
744 x_last_update_date,
745 x_last_updated_by,
746 x_last_update_login
747 );
748
749 IF (p_action = 'INSERT') THEN
750 -- Call all the procedures related to Before Insert.
751 BeforeRowInsertUpdate1 ;
752 IF Get_PK_For_Validation (
753 new_references.IGS_PE_PERS_DISABLTY_ID
754 ) THEN
755 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
756 IGS_GE_MSG_STACK.ADD;
757 App_Exception.Raise_Exception;
758 END IF;
759 check_uniqueness ;
760 Check_Constraints; -- if procedure present
761 Check_Parent_Existance; -- if procedure present
762 beforerowinsertupdate(TRUE,FALSE);
763
764 ELSIF (p_action = 'UPDATE') THEN
765 -- Call all the procedures related to Before Update.
766 BeforeRowInsertUpdate1;
767
768 check_uniqueness ;
769 Check_Constraints; -- if procedure present
770 Check_Parent_Existance; -- if procedure present
771 beforerowinsertupdate(FALSE,TRUE);
772
773 ELSIF (p_action = 'DELETE') THEN
774 -- Call all the procedures related to Before Delete.
775 Check_Child_Existance ;
776
777 ELSIF (p_action = 'VALIDATE_INSERT') THEN
778
779 IF Get_PK_For_Validation (
780 new_references.IGS_PE_PERS_DISABLTY_ID
781 ) THEN
782 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
783 IGS_GE_MSG_STACK.ADD;
784 App_Exception.Raise_Exception;
785 END IF;
786
787 check_uniqueness ;
788 Check_Constraints; -- if procedure present
789 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
790
791 check_uniqueness ;
792 Check_Constraints; -- if procedure present
793
794 ELSIF (p_action = 'VALIDATE_DELETE') THEN
795 Check_Child_Existance ;
796 END IF;
797
798 END Before_DML;
799
800 PROCEDURE After_DML (
801 p_action IN VARCHAR2,
802 x_rowid IN VARCHAR2
803 ) IS
804 /*************************************************************
805 Created By :
806 Date Created By :
807 Purpose :
808 Know limitations, enhancements or remarks
809 Change History
810 Who When What
811 sraj 2000/05/05 the table structure has been changed
812 (reverse chronological order - newest change first)
813 ***************************************************************/
814
815 BEGIN
816
817 l_rowid := x_rowid;
818
819 IF (p_action = 'INSERT') THEN
820
821 -- Call all the procedures related to After Insert.
822 NULL;
823 ELSIF (p_action = 'UPDATE') THEN
824
825 -- Call all the procedures related to After Update.
826 NULL;
827
828 ELSIF (p_action = 'DELETE') THEN
829
830 -- Call all the procedures related to After Delete.
831 NULL;
832
833 END IF;
834
835 END After_DML;
836
837 procedure INSERT_ROW (
838 X_ROWID in out NOCOPY VARCHAR2,
839 X_IGS_PE_PERS_DISABLTY_ID IN OUT NOCOPY NUMBER,
840 x_PERSON_ID IN NUMBER,
841 x_DISABILITY_TYPE IN VARCHAR2,
842 x_CONTACT_IND IN VARCHAR2,
843 x_SPECIAL_ALLOW_CD IN VARCHAR2,
844 x_SUPPORT_LEVEL_CD IN VARCHAR2,
845 x_DOCUMENTED IN VARCHAR2,
846 x_SPECIAL_SERVICE_ID IN NUMBER,
847 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
848 x_ATTRIBUTE1 IN VARCHAR2,
849 x_ATTRIBUTE2 IN VARCHAR2,
850 x_ATTRIBUTE3 IN VARCHAR2,
851 x_ATTRIBUTE4 IN VARCHAR2,
852 x_ATTRIBUTE5 IN VARCHAR2,
853 x_ATTRIBUTE6 IN VARCHAR2,
854 x_ATTRIBUTE7 IN VARCHAR2,
855 x_ATTRIBUTE8 IN VARCHAR2,
856 x_ATTRIBUTE9 IN VARCHAR2,
857 x_ATTRIBUTE10 IN VARCHAR2,
858 x_ATTRIBUTE11 IN VARCHAR2,
859 x_ATTRIBUTE12 IN VARCHAR2,
860 x_ATTRIBUTE13 IN VARCHAR2,
861 x_ATTRIBUTE14 IN VARCHAR2,
862 x_ATTRIBUTE15 IN VARCHAR2,
863 x_ATTRIBUTE16 IN VARCHAR2,
864 x_ATTRIBUTE17 IN VARCHAR2,
865 x_ATTRIBUTE18 IN VARCHAR2,
866 x_ATTRIBUTE19 IN VARCHAR2,
867 x_ATTRIBUTE20 IN VARCHAR2,
868 X_ELIG_EARLY_REG_IND IN VARCHAR2 ,--DEFAULT NULL,
869 X_START_DATE IN DATE ,--DEFAULT NULL,
870 X_END_DATE IN DATE ,--DEFAULT NULL,
871 X_INFO_SOURCE IN VARCHAR2 ,--DEFAULT NULL,
872 X_INTERVIEWER_ID IN NUMBER ,--DEFAULT NULL,
873 X_INTERVIEWER_DATE IN DATE, --DEFAULT NULL,
874 X_MODE in VARCHAR2 --default 'R'
875 ) AS
876 /*************************************************************
877 Created By :
878 Date Created By :
879 Purpose :
880 Know limitations, enhancements or remarks
881 Change History
882 Who When What
883 sraj 2000/05/05 the table structure has been changed
884 (reverse chronological order - newest change first)
885 ***************************************************************/
886
887 cursor C is select ROWID from IGS_PE_PERS_DISABLTY
888 where
889 IGS_PE_PERS_DISABLTY_ID = X_IGS_PE_PERS_DISABLTY_ID ;
890
891 X_LAST_UPDATE_DATE DATE ;
892 X_LAST_UPDATED_BY NUMBER ;
893 X_LAST_UPDATE_LOGIN NUMBER ;
894
895 begin
896 X_LAST_UPDATE_DATE := SYSDATE;
897 if(X_MODE = 'I') then
898 X_LAST_UPDATED_BY := 1;
899 X_LAST_UPDATE_LOGIN := 0;
900 elsif (X_MODE IN ('R', 'S')) then
901 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
902 if X_LAST_UPDATED_BY is NULL then
903 X_LAST_UPDATED_BY := -1;
904 end if;
905 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
906 if X_LAST_UPDATE_LOGIN is NULL then
907 X_LAST_UPDATE_LOGIN := -1;
908 end if;
909 else
910 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
911 IGS_GE_MSG_STACK.ADD;
912 app_exception.raise_exception;
913 end if;
914
915 SELECT igs_pe_pers_disablty_s.NEXTVAL
916 INTO x_IGS_PE_PERS_DISABLTY_ID
917 FROM dual;
918
919 Before_DML(
920 p_action=>'INSERT',
921 x_rowid=>X_ROWID,
922 X_IGS_PE_PERS_DISABLTY_ID => X_IGS_PE_PERS_DISABLTY_ID ,
923 x_person_id=>X_PERSON_ID,
924 x_disability_type=>X_DISABILITY_TYPE,
925 x_contact_ind=>NVL(X_CONTACT_IND,'N' ),
926 x_special_allow_cd=>X_SPECIAL_ALLOW_CD,
927 x_support_level_cd=>X_SUPPORT_LEVEL_CD,
928 x_documented=>X_DOCUMENTED,
929 x_special_service_id=>X_SPECIAL_SERVICE_ID,
930 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
931 x_attribute1=>X_ATTRIBUTE1,
932 x_attribute2=>X_ATTRIBUTE2,
933 x_attribute3=>X_ATTRIBUTE3,
934 x_attribute4=>X_ATTRIBUTE4,
935 x_attribute5=>X_ATTRIBUTE5,
936 x_attribute6=>X_ATTRIBUTE6,
937 x_attribute7=>X_ATTRIBUTE7,
938 x_attribute8=>X_ATTRIBUTE8,
939 x_attribute9=>X_ATTRIBUTE9,
940 x_attribute10=>X_ATTRIBUTE10,
941 x_attribute11=>X_ATTRIBUTE11,
942 x_attribute12=>X_ATTRIBUTE12,
943 x_attribute13=>X_ATTRIBUTE13,
944 x_attribute14=>X_ATTRIBUTE14,
945 x_attribute15=>X_ATTRIBUTE15,
946 x_attribute16=>X_ATTRIBUTE16,
947 x_attribute17=>X_ATTRIBUTE17,
948 x_attribute18=>X_ATTRIBUTE18,
949 x_attribute19=>X_ATTRIBUTE19,
950 x_attribute20=>X_ATTRIBUTE20,
951 X_ELIG_EARLY_REG_IND => X_ELIG_EARLY_REG_IND ,
952 X_START_DATE => X_START_DATE ,
953 X_END_DATE => X_END_DATE ,
954 X_INFO_SOURCE => X_INFO_SOURCE ,
955 X_INTERVIEWER_ID => X_INTERVIEWER_ID ,
956 X_INTERVIEWER_DATE => X_INTERVIEWER_DATE ,
957 x_creation_date=>X_LAST_UPDATE_DATE,
958 x_created_by=>X_LAST_UPDATED_BY,
959 x_last_update_date=>X_LAST_UPDATE_DATE,
960 x_last_updated_by=>X_LAST_UPDATED_BY,
961 x_last_update_login=>X_LAST_UPDATE_LOGIN);
962
963 IF (x_mode = 'S') THEN
964 igs_sc_gen_001.set_ctx('R');
965 END IF;
966 insert into IGS_PE_PERS_DISABLTY (
967 IGS_PE_PERS_DISABLTY_ID
968 ,PERSON_ID
969 ,DISABILITY_TYPE
970 ,CONTACT_IND
971 ,SPECIAL_ALLOW_CD
972 ,SUPPORT_LEVEL_CD
973 ,DOCUMENTED
974 ,SPECIAL_SERVICE_ID
975 ,ATTRIBUTE_CATEGORY
976 ,ATTRIBUTE1
977 ,ATTRIBUTE2
978 ,ATTRIBUTE3
979 ,ATTRIBUTE4
980 ,ATTRIBUTE5
981 ,ATTRIBUTE6
982 ,ATTRIBUTE7
983 ,ATTRIBUTE8
984 ,ATTRIBUTE9
985 ,ATTRIBUTE10
986 ,ATTRIBUTE11
987 ,ATTRIBUTE12
988 ,ATTRIBUTE13
989 ,ATTRIBUTE14
990 ,ATTRIBUTE15
991 ,ATTRIBUTE16
992 ,ATTRIBUTE17
993 ,ATTRIBUTE18
994 ,ATTRIBUTE19
995 ,ATTRIBUTE20
996 ,ELIG_EARLY_REG_IND
997 ,START_DATE
998 ,END_DATE
999 ,INFO_SOURCE
1000 ,INTERVIEWER_ID
1001 ,INTERVIEWER_DATE
1002 ,CREATION_DATE
1003 ,CREATED_BY
1004 ,LAST_UPDATE_DATE
1005 ,LAST_UPDATED_BY
1006 ,LAST_UPDATE_LOGIN
1007 ) values (
1008 NEW_REFERENCES.IGS_PE_PERS_DISABLTY_ID
1009 ,NEW_REFERENCES.PERSON_ID
1010 ,NEW_REFERENCES.DISABILITY_TYPE
1011 ,NEW_REFERENCES.CONTACT_IND
1012 ,NEW_REFERENCES.SPECIAL_ALLOW_CD
1013 ,NEW_REFERENCES.SUPPORT_LEVEL_CD
1014 ,NEW_REFERENCES.DOCUMENTED
1015 ,NEW_REFERENCES.SPECIAL_SERVICE_ID
1016 ,NEW_REFERENCES.ATTRIBUTE_CATEGORY
1017 ,NEW_REFERENCES.ATTRIBUTE1
1018 ,NEW_REFERENCES.ATTRIBUTE2
1019 ,NEW_REFERENCES.ATTRIBUTE3
1020 ,NEW_REFERENCES.ATTRIBUTE4
1021 ,NEW_REFERENCES.ATTRIBUTE5
1022 ,NEW_REFERENCES.ATTRIBUTE6
1023 ,NEW_REFERENCES.ATTRIBUTE7
1024 ,NEW_REFERENCES.ATTRIBUTE8
1025 ,NEW_REFERENCES.ATTRIBUTE9
1026 ,NEW_REFERENCES.ATTRIBUTE10
1027 ,NEW_REFERENCES.ATTRIBUTE11
1028 ,NEW_REFERENCES.ATTRIBUTE12
1029 ,NEW_REFERENCES.ATTRIBUTE13
1030 ,NEW_REFERENCES.ATTRIBUTE14
1031 ,NEW_REFERENCES.ATTRIBUTE15
1032 ,NEW_REFERENCES.ATTRIBUTE16
1033 ,NEW_REFERENCES.ATTRIBUTE17
1034 ,NEW_REFERENCES.ATTRIBUTE18
1035 ,NEW_REFERENCES.ATTRIBUTE19
1036 ,NEW_REFERENCES.ATTRIBUTE20
1037 ,NEW_REFERENCES.ELIG_EARLY_REG_IND
1038 ,NEW_REFERENCES.START_DATE
1039 ,NEW_REFERENCES.END_DATE
1040 ,NEW_REFERENCES.INFO_SOURCE
1041 ,NEW_REFERENCES.INTERVIEWER_ID
1042 ,NEW_REFERENCES.INTERVIEWER_DATE
1043 ,X_LAST_UPDATE_DATE
1044 ,X_LAST_UPDATED_BY
1045 ,X_LAST_UPDATE_DATE
1046 ,X_LAST_UPDATED_BY
1047 ,X_LAST_UPDATE_LOGIN
1048 );
1049 IF (x_mode = 'S') THEN
1050 igs_sc_gen_001.unset_ctx('R');
1051 END IF;
1052
1053 open c;
1054 fetch c into X_ROWID;
1055 if (c%notfound) then
1056 close c;
1057 raise no_data_found;
1058 end if;
1059 close c;
1060 After_DML (
1061 p_action => 'INSERT' ,
1062 x_rowid => X_ROWID );
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
1066 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1067 fnd_message.set_token ('ERR_CD', SQLCODE);
1068 igs_ge_msg_stack.add;
1069 igs_sc_gen_001.unset_ctx('R');
1070 app_exception.raise_exception;
1071 ELSE
1072 igs_sc_gen_001.unset_ctx('R');
1073 RAISE;
1074 END IF;
1075
1076 end INSERT_ROW;
1077
1078 procedure LOCK_ROW (
1079 X_ROWID in VARCHAR2,
1080 X_IGS_PE_PERS_DISABLTY_ID IN NUMBER,
1081 x_PERSON_ID IN NUMBER,
1082 x_DISABILITY_TYPE IN VARCHAR2,
1083 x_CONTACT_IND IN VARCHAR2,
1084 x_SPECIAL_ALLOW_CD IN VARCHAR2,
1085 x_SUPPORT_LEVEL_CD IN VARCHAR2,
1086 x_DOCUMENTED IN VARCHAR2,
1087 x_SPECIAL_SERVICE_ID IN NUMBER,
1088 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1089 x_ATTRIBUTE1 IN VARCHAR2,
1090 x_ATTRIBUTE2 IN VARCHAR2,
1091 x_ATTRIBUTE3 IN VARCHAR2,
1092 x_ATTRIBUTE4 IN VARCHAR2,
1093 x_ATTRIBUTE5 IN VARCHAR2,
1094 x_ATTRIBUTE6 IN VARCHAR2,
1095 x_ATTRIBUTE7 IN VARCHAR2,
1096 x_ATTRIBUTE8 IN VARCHAR2,
1097 x_ATTRIBUTE9 IN VARCHAR2,
1098 x_ATTRIBUTE10 IN VARCHAR2,
1099 x_ATTRIBUTE11 IN VARCHAR2,
1100 x_ATTRIBUTE12 IN VARCHAR2,
1101 x_ATTRIBUTE13 IN VARCHAR2,
1102 x_ATTRIBUTE14 IN VARCHAR2,
1103 x_ATTRIBUTE15 IN VARCHAR2,
1104 x_ATTRIBUTE16 IN VARCHAR2,
1105 x_ATTRIBUTE17 IN VARCHAR2,
1106 x_ATTRIBUTE18 IN VARCHAR2,
1107 x_ATTRIBUTE19 IN VARCHAR2,
1108 x_ATTRIBUTE20 IN VARCHAR2,
1109 X_ELIG_EARLY_REG_IND IN VARCHAR2 ,--DEFAULT NULL,
1110 X_START_DATE IN DATE ,--DEFAULT NULL,
1111 X_END_DATE IN DATE ,--DEFAULT NULL,
1112 X_INFO_SOURCE IN VARCHAR2 ,--DEFAULT NULL,
1113 X_INTERVIEWER_ID IN NUMBER ,--DEFAULT NULL,
1114 X_INTERVIEWER_DATE IN DATE --DEFAULT NULL
1115
1116 ) AS
1117 /*************************************************************
1118 Created By :
1119 Date Created By :
1120 Purpose :
1121 Know limitations, enhancements or remarks
1122 Change History
1123 Who When What
1124 sraj 2000/05/05 the table structure has been changed
1125 (reverse chronological order - newest change first)
1126 ***************************************************************/
1127
1128 cursor c1 is select
1129 IGS_PE_PERS_DISABLTY_ID
1130 , CONTACT_IND
1131 , SPECIAL_ALLOW_CD
1132 , SUPPORT_LEVEL_CD
1133 , DOCUMENTED
1134 , SPECIAL_SERVICE_ID
1135 , ATTRIBUTE_CATEGORY
1136 , ATTRIBUTE1
1137 , ATTRIBUTE2
1138 , ATTRIBUTE3
1139 , ATTRIBUTE4
1140 , ATTRIBUTE5
1141 , ATTRIBUTE6
1142 , ATTRIBUTE7
1143 , ATTRIBUTE8
1144 , ATTRIBUTE9
1145 , ATTRIBUTE10
1146 , ATTRIBUTE11
1147 , ATTRIBUTE12
1148 , ATTRIBUTE13
1149 , ATTRIBUTE14
1150 , ATTRIBUTE15
1151 , ATTRIBUTE16
1152 , ATTRIBUTE17
1153 , ATTRIBUTE18
1154 , ATTRIBUTE19
1155 , ATTRIBUTE20
1156 , ELIG_EARLY_REG_IND
1157 , START_DATE
1158 , END_DATE
1159 , INFO_SOURCE
1160 , INTERVIEWER_ID
1161 , INTERVIEWER_DATE
1162 from IGS_PE_PERS_DISABLTY
1163 where ROWID = X_ROWID
1164 for update nowait;
1165 tlinfo c1%rowtype;
1166 begin
1167 open c1;
1168 fetch c1 into tlinfo;
1169 if (c1%notfound) then
1170 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1171 IGS_GE_MSG_STACK.ADD;
1172 close c1;
1173 app_exception.raise_exception;
1174 return;
1175 end if;
1176 close c1;
1177 if (
1178 ((tlinfo.SPECIAL_ALLOW_CD = X_SPECIAL_ALLOW_CD)
1179 OR ((tlinfo.SPECIAL_ALLOW_CD is null)
1180 AND (X_SPECIAL_ALLOW_CD is null)))
1181 AND ((tlinfo.SUPPORT_LEVEL_CD = X_SUPPORT_LEVEL_CD)
1182 OR ((tlinfo.SUPPORT_LEVEL_CD is null)
1183 AND (X_SUPPORT_LEVEL_CD is null)))
1184 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1185 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
1186 AND (X_ATTRIBUTE_CATEGORY is null)))
1187 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1188 OR ((tlinfo.ATTRIBUTE1 is null)
1189 AND (X_ATTRIBUTE1 is null)))
1190 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1191 OR ((tlinfo.ATTRIBUTE2 is null)
1192 AND (X_ATTRIBUTE2 is null)))
1193 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1194 OR ((tlinfo.ATTRIBUTE3 is null)
1195 AND (X_ATTRIBUTE3 is null)))
1196 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1197 OR ((tlinfo.ATTRIBUTE4 is null)
1198 AND (X_ATTRIBUTE4 is null)))
1199 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1200 OR ((tlinfo.ATTRIBUTE5 is null)
1201 AND (X_ATTRIBUTE5 is null)))
1202 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1203 OR ((tlinfo.ATTRIBUTE6 is null)
1204 AND (X_ATTRIBUTE6 is null)))
1205 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1206 OR ((tlinfo.ATTRIBUTE7 is null)
1207 AND (X_ATTRIBUTE7 is null)))
1208 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1209 OR ((tlinfo.ATTRIBUTE8 is null)
1210 AND (X_ATTRIBUTE8 is null)))
1211 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1212 OR ((tlinfo.ATTRIBUTE9 is null)
1213 AND (X_ATTRIBUTE9 is null)))
1214 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1215 OR ((tlinfo.ATTRIBUTE10 is null)
1216 AND (X_ATTRIBUTE10 is null)))
1217 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1218 OR ((tlinfo.ATTRIBUTE11 is null)
1219 AND (X_ATTRIBUTE11 is null)))
1220 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1221 OR ((tlinfo.ATTRIBUTE12 is null)
1222 AND (X_ATTRIBUTE12 is null)))
1223 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1224 OR ((tlinfo.ATTRIBUTE13 is null)
1225 AND (X_ATTRIBUTE13 is null)))
1226 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1227 OR ((tlinfo.ATTRIBUTE14 is null)
1228 AND (X_ATTRIBUTE14 is null)))
1229 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1230 OR ((tlinfo.ATTRIBUTE15 is null)
1231 AND (X_ATTRIBUTE15 is null)))
1232 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1233 OR ((tlinfo.ATTRIBUTE16 is null)
1234 AND (X_ATTRIBUTE16 is null)))
1235 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1236 OR ((tlinfo.ATTRIBUTE17 is null)
1237 AND (X_ATTRIBUTE17 is null)))
1238 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1239 OR ((tlinfo.ATTRIBUTE18 is null)
1240 AND (X_ATTRIBUTE18 is null)))
1241 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1242 OR ((tlinfo.ATTRIBUTE19 is null)
1243 AND (X_ATTRIBUTE19 is null)))
1244 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR ((tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
1245 AND ((tlinfo.ELIG_EARLY_REG_IND = X_ELIG_EARLY_REG_IND) OR ((tlinfo.ELIG_EARLY_REG_IND is null) AND (X_ELIG_EARLY_REG_IND is null)))
1246 AND ((tlinfo.START_DATE = X_START_DATE) OR ((tlinfo.START_DATE is null) AND (X_START_DATE is null)))
1247 AND ((tlinfo.END_DATE = X_END_DATE) OR ((tlinfo.END_DATE is null) AND (X_END_DATE is null)))
1248 AND ((tlinfo.INFO_SOURCE = X_INFO_SOURCE) OR ((tlinfo.INFO_SOURCE is null) AND (X_INFO_SOURCE is null)))
1249 AND ((tlinfo.INTERVIEWER_ID = X_INTERVIEWER_ID) OR ((tlinfo.INTERVIEWER_ID is null) AND (X_INTERVIEWER_ID is null)))
1250 AND ((tlinfo.INTERVIEWER_DATE = X_INTERVIEWER_DATE) OR ((tlinfo.INTERVIEWER_DATE is null) AND (X_INTERVIEWER_DATE is null)))
1251
1252
1253 ) then
1254 null;
1255 else
1256 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1257 IGS_GE_MSG_STACK.ADD;
1258 app_exception.raise_exception;
1259 end if;
1260 return;
1261 end LOCK_ROW;
1262 Procedure UPDATE_ROW (
1263 X_ROWID in VARCHAR2,
1264 X_IGS_PE_PERS_DISABLTY_ID IN NUMBER,
1265 x_PERSON_ID IN NUMBER,
1266 x_DISABILITY_TYPE IN VARCHAR2,
1267 x_CONTACT_IND IN VARCHAR2,
1268 x_SPECIAL_ALLOW_CD IN VARCHAR2,
1269 x_SUPPORT_LEVEL_CD IN VARCHAR2,
1270 x_DOCUMENTED IN VARCHAR2,
1271 x_SPECIAL_SERVICE_ID IN NUMBER,
1272 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1273 x_ATTRIBUTE1 IN VARCHAR2,
1274 x_ATTRIBUTE2 IN VARCHAR2,
1275 x_ATTRIBUTE3 IN VARCHAR2,
1276 x_ATTRIBUTE4 IN VARCHAR2,
1277 x_ATTRIBUTE5 IN VARCHAR2,
1278 x_ATTRIBUTE6 IN VARCHAR2,
1279 x_ATTRIBUTE7 IN VARCHAR2,
1280 x_ATTRIBUTE8 IN VARCHAR2,
1281 x_ATTRIBUTE9 IN VARCHAR2,
1282 x_ATTRIBUTE10 IN VARCHAR2,
1283 x_ATTRIBUTE11 IN VARCHAR2,
1284 x_ATTRIBUTE12 IN VARCHAR2,
1285 x_ATTRIBUTE13 IN VARCHAR2,
1286 x_ATTRIBUTE14 IN VARCHAR2,
1287 x_ATTRIBUTE15 IN VARCHAR2,
1288 x_ATTRIBUTE16 IN VARCHAR2,
1289 x_ATTRIBUTE17 IN VARCHAR2,
1290 x_ATTRIBUTE18 IN VARCHAR2,
1291 x_ATTRIBUTE19 IN VARCHAR2,
1292 x_ATTRIBUTE20 IN VARCHAR2,
1293 X_ELIG_EARLY_REG_IND IN VARCHAR2 ,--DEFAULT NULL,
1294 X_START_DATE IN DATE ,--DEFAULT NULL,
1295 X_END_DATE IN DATE ,--DEFAULT NULL,
1296 X_INFO_SOURCE IN VARCHAR2,-- DEFAULT NULL,
1297 X_INTERVIEWER_ID IN NUMBER,-- DEFAULT NULL,
1298 X_INTERVIEWER_DATE IN DATE ,--DEFAULT NULL,
1299 X_MODE in VARCHAR2 -- default 'R'
1300 ) AS
1301 /*************************************************************
1302 Created By :
1303 Date Created By :
1304 Purpose :
1305 Know limitations, enhancements or remarks
1306 Change History
1307 Who When What
1308 sraj 2000/05/05 the table structure has been changed
1309 (reverse chronological order - newest change first)
1310 ***************************************************************/
1311
1312 X_LAST_UPDATE_DATE DATE ;
1313 X_LAST_UPDATED_BY NUMBER ;
1314 X_LAST_UPDATE_LOGIN NUMBER ;
1315 begin
1316 X_LAST_UPDATE_DATE := SYSDATE;
1317 if(X_MODE = 'I') then
1318 X_LAST_UPDATED_BY := 1;
1319 X_LAST_UPDATE_LOGIN := 0;
1320 elsif (X_MODE IN ('R', 'S')) then
1321 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1322 if X_LAST_UPDATED_BY is NULL then
1323 X_LAST_UPDATED_BY := -1;
1324 end if;
1325 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1326 if X_LAST_UPDATE_LOGIN is NULL then
1327 X_LAST_UPDATE_LOGIN := -1;
1328 end if;
1329 else
1330 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1331 IGS_GE_MSG_STACK.ADD;
1332 app_exception.raise_exception;
1333 end if;
1334 Before_DML(
1335 p_action=>'UPDATE',
1336 x_rowid=>X_ROWID,
1337 X_IGS_PE_PERS_DISABLTY_ID => X_IGS_PE_PERS_DISABLTY_ID ,
1338 x_person_id=>X_PERSON_ID,
1339 x_disability_type=>X_DISABILITY_TYPE,
1340 x_special_allow_cd=>X_SPECIAL_ALLOW_CD,
1341 x_support_level_cd=>X_SUPPORT_LEVEL_CD,
1342 x_documented=>X_DOCUMENTED,
1343 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1344 x_attribute1=>X_ATTRIBUTE1,
1345 x_attribute2=>X_ATTRIBUTE2,
1346 x_attribute3=>X_ATTRIBUTE3,
1347 x_attribute4=>X_ATTRIBUTE4,
1348 x_attribute5=>X_ATTRIBUTE5,
1349 x_attribute6=>X_ATTRIBUTE6,
1350 x_attribute7=>X_ATTRIBUTE7,
1351 x_attribute8=>X_ATTRIBUTE8,
1352 x_attribute9=>X_ATTRIBUTE9,
1353 x_attribute10=>X_ATTRIBUTE10,
1354 x_attribute11=>X_ATTRIBUTE11,
1355 x_attribute12=>X_ATTRIBUTE12,
1356 x_attribute13=>X_ATTRIBUTE13,
1357 x_attribute14=>X_ATTRIBUTE14,
1358 x_attribute15=>X_ATTRIBUTE15,
1359 x_attribute16=>X_ATTRIBUTE16,
1360 x_attribute17=>X_ATTRIBUTE17,
1361 x_attribute18=>X_ATTRIBUTE18,
1362 x_attribute19=>X_ATTRIBUTE19,
1363 x_attribute20=>X_ATTRIBUTE20,
1364 X_ELIG_EARLY_REG_IND => X_ELIG_EARLY_REG_IND ,
1365 X_START_DATE => X_START_DATE ,
1366 X_END_DATE => X_END_DATE ,
1367 X_INFO_SOURCE => X_INFO_SOURCE ,
1368 X_INTERVIEWER_ID => X_INTERVIEWER_ID ,
1369 X_INTERVIEWER_DATE => X_INTERVIEWER_DATE ,
1370 x_creation_date=>X_LAST_UPDATE_DATE,
1371 x_created_by=>X_LAST_UPDATED_BY,
1372 x_last_update_date=>X_LAST_UPDATE_DATE,
1373 x_last_updated_by=>X_LAST_UPDATED_BY,
1374 x_last_update_login=>X_LAST_UPDATE_LOGIN);
1375 IF (x_mode = 'S') THEN
1376 igs_sc_gen_001.set_ctx('R');
1377 END IF;
1378 update IGS_PE_PERS_DISABLTY set
1379 SPECIAL_ALLOW_CD = NEW_REFERENCES.SPECIAL_ALLOW_CD,
1380 SUPPORT_LEVEL_CD = NEW_REFERENCES.SUPPORT_LEVEL_CD,
1381 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1382 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1383 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1384 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1385 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1386 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1387 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1388 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1389 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1390 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1391 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1392 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1393 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1394 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1395 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1396 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1397 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1398 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1399 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1400 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1401 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1402 ELIG_EARLY_REG_IND = NEW_REFERENCES.ELIG_EARLY_REG_IND ,
1403 START_DATE = NEW_REFERENCES.START_DATE ,
1404 END_DATE = NEW_REFERENCES.END_DATE ,
1405 INFO_SOURCE = NEW_REFERENCES.INFO_SOURCE ,
1406 INTERVIEWER_ID = NEW_REFERENCES.INTERVIEWER_ID ,
1407 INTERVIEWER_DATE = NEW_REFERENCES.INTERVIEWER_DATE ,
1408 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1409 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1410 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1411 where ROWID = X_ROWID;
1412 if (sql%notfound) then
1413 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1414 igs_ge_msg_stack.add;
1415 igs_sc_gen_001.unset_ctx('R');
1416 app_exception.raise_exception;
1417 end if;
1418 IF (x_mode = 'S') THEN
1419 igs_sc_gen_001.unset_ctx('R');
1420 END IF;
1421
1422
1423 After_DML (
1424 p_action => 'UPDATE' ,
1425 x_rowid => X_ROWID
1426 );
1427 EXCEPTION
1428 WHEN OTHERS THEN
1429 IF (SQLCODE = (-28115)) THEN
1430 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1431 fnd_message.set_token ('ERR_CD', SQLCODE);
1432 igs_ge_msg_stack.add;
1433 igs_sc_gen_001.unset_ctx('R');
1434 app_exception.raise_exception;
1435 ELSE
1436 igs_sc_gen_001.unset_ctx('R');
1437 RAISE;
1438 END IF;
1439
1440 end UPDATE_ROW;
1441 procedure ADD_ROW (
1442 X_ROWID in out NOCOPY VARCHAR2,
1443 X_IGS_PE_PERS_DISABLTY_ID IN OUT NOCOPY NUMBER,
1444 x_PERSON_ID IN NUMBER,
1445 x_DISABILITY_TYPE IN VARCHAR2,
1446 x_CONTACT_IND IN VARCHAR2,
1447 x_SPECIAL_ALLOW_CD IN VARCHAR2,
1448 x_SUPPORT_LEVEL_CD IN VARCHAR2,
1449 x_DOCUMENTED IN VARCHAR2,
1450 x_SPECIAL_SERVICE_ID IN NUMBER,
1451 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
1452 x_ATTRIBUTE1 IN VARCHAR2,
1453 x_ATTRIBUTE2 IN VARCHAR2,
1454 x_ATTRIBUTE3 IN VARCHAR2,
1455 x_ATTRIBUTE4 IN VARCHAR2,
1456 x_ATTRIBUTE5 IN VARCHAR2,
1457 x_ATTRIBUTE6 IN VARCHAR2,
1458 x_ATTRIBUTE7 IN VARCHAR2,
1459 x_ATTRIBUTE8 IN VARCHAR2,
1460 x_ATTRIBUTE9 IN VARCHAR2,
1461 x_ATTRIBUTE10 IN VARCHAR2,
1462 x_ATTRIBUTE11 IN VARCHAR2,
1463 x_ATTRIBUTE12 IN VARCHAR2,
1464 x_ATTRIBUTE13 IN VARCHAR2,
1465 x_ATTRIBUTE14 IN VARCHAR2,
1466 x_ATTRIBUTE15 IN VARCHAR2,
1467 x_ATTRIBUTE16 IN VARCHAR2,
1468 x_ATTRIBUTE17 IN VARCHAR2,
1469 x_ATTRIBUTE18 IN VARCHAR2,
1470 x_ATTRIBUTE19 IN VARCHAR2,
1471 x_ATTRIBUTE20 IN VARCHAR2,
1472 X_ELIG_EARLY_REG_IND IN VARCHAR2 ,--DEFAULT NULL,
1473 X_START_DATE IN DATE ,--DEFAULT NULL,
1474 X_END_DATE IN DATE ,--DEFAULT NULL,
1475 X_INFO_SOURCE IN VARCHAR2,-- DEFAULT NULL,
1476 X_INTERVIEWER_ID IN NUMBER ,--DEFAULT NULL,
1477 X_INTERVIEWER_DATE IN DATE ,--DEFAULT NULL,
1478 X_MODE in VARCHAR2 -- default 'R'
1479 ) AS
1480 /*************************************************************
1481 Created By :
1482 Date Created By :
1483 Purpose :
1484 Know limitations, enhancements or remarks
1485 Change History
1486 Who When What
1487 sraj 2000/05/05 the table structure has been changed
1488 (reverse chronological order - newest change first)
1489 ***************************************************************/
1490
1491 cursor c1 is select ROWID from IGS_PE_PERS_DISABLTY
1492 where IGS_PE_PERS_DISABLTY_ID = X_IGS_PE_PERS_DISABLTY_ID
1493 ;
1494 begin
1495 open c1;
1496 fetch c1 into X_ROWID;
1497 if (c1%notfound) then
1498 close c1;
1499 INSERT_ROW (
1500 X_ROWID,
1501 X_IGS_PE_PERS_DISABLTY_ID,
1502 X_PERSON_ID,
1503 X_DISABILITY_TYPE,
1504 X_CONTACT_IND,
1505 X_SPECIAL_ALLOW_CD,
1506 X_SUPPORT_LEVEL_CD,
1507 X_DOCUMENTED,
1508 X_SPECIAL_SERVICE_ID,
1509 X_ATTRIBUTE_CATEGORY,
1510 X_ATTRIBUTE1,
1511 X_ATTRIBUTE2,
1512 X_ATTRIBUTE3,
1513 X_ATTRIBUTE4,
1514 X_ATTRIBUTE5,
1515 X_ATTRIBUTE6,
1516 X_ATTRIBUTE7,
1517 X_ATTRIBUTE8,
1518 X_ATTRIBUTE9,
1519 X_ATTRIBUTE10,
1520 X_ATTRIBUTE11,
1521 X_ATTRIBUTE12,
1522 X_ATTRIBUTE13,
1523 X_ATTRIBUTE14,
1524 X_ATTRIBUTE15,
1525 X_ATTRIBUTE16,
1526 X_ATTRIBUTE17,
1527 X_ATTRIBUTE18,
1528 X_ATTRIBUTE19,
1529 X_ATTRIBUTE20,
1530 X_ELIG_EARLY_REG_IND ,
1531 X_START_DATE ,
1532 X_END_DATE ,
1533 X_INFO_SOURCE ,
1534 X_INTERVIEWER_ID ,
1535 X_INTERVIEWER_DATE ,
1536 X_MODE );
1537
1538 return;
1539
1540 end if;
1541 close c1;
1542 UPDATE_ROW (
1543 X_ROWID,
1544 X_IGS_PE_PERS_DISABLTY_ID,
1545 X_PERSON_ID,
1546 X_DISABILITY_TYPE,
1547 X_CONTACT_IND,
1548 X_SPECIAL_ALLOW_CD,
1549 X_SUPPORT_LEVEL_CD,
1550 X_DOCUMENTED,
1551 X_SPECIAL_SERVICE_ID,
1552 X_ATTRIBUTE_CATEGORY,
1553 X_ATTRIBUTE1,
1554 X_ATTRIBUTE2,
1555 X_ATTRIBUTE3,
1556 X_ATTRIBUTE4,
1557 X_ATTRIBUTE5,
1558 X_ATTRIBUTE6,
1559 X_ATTRIBUTE7,
1560 X_ATTRIBUTE8,
1561 X_ATTRIBUTE9,
1562 X_ATTRIBUTE10,
1563 X_ATTRIBUTE11,
1564 X_ATTRIBUTE12,
1565 X_ATTRIBUTE13,
1566 X_ATTRIBUTE14,
1567 X_ATTRIBUTE15,
1568 X_ATTRIBUTE16,
1569 X_ATTRIBUTE17,
1570 X_ATTRIBUTE18,
1571 X_ATTRIBUTE19,
1572 X_ATTRIBUTE20,
1573 X_ELIG_EARLY_REG_IND ,
1574 X_START_DATE ,
1575 X_END_DATE ,
1576 X_INFO_SOURCE ,
1577 X_INTERVIEWER_ID ,
1578 X_INTERVIEWER_DATE ,
1579 X_MODE );
1580 end ADD_ROW;
1581 procedure DELETE_ROW (
1582 X_ROWID in VARCHAR2,
1583 x_mode IN VARCHAR2
1584 ) AS
1585 /*************************************************************
1586 Created By :
1587 Date Created By :
1588 Purpose :
1589 Know limitations, enhancements or remarks
1590 Change History
1591 Who When What
1592 sraj 2000/05/05 the table structure has been changed
1593 (reverse chronological order - newest change first)
1594 ***************************************************************/
1595
1596 begin
1597 Before_DML (
1598 p_action => 'DELETE',
1599 x_rowid => X_ROWID
1600 );
1601 IF (x_mode = 'S') THEN
1602 igs_sc_gen_001.set_ctx('R');
1603 END IF;
1604 delete from IGS_PE_PERS_DISABLTY
1605 where ROWID = X_ROWID;
1606 if (sql%notfound) then
1607 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1608 igs_ge_msg_stack.add;
1609 igs_sc_gen_001.unset_ctx('R');
1610 app_exception.raise_exception;
1611 end if;
1612 IF (x_mode = 'S') THEN
1613 igs_sc_gen_001.unset_ctx('R');
1614 END IF;
1615
1616 After_DML (
1617 p_action => 'DELETE',
1618 x_rowid => X_ROWID
1619 );
1620 end DELETE_ROW;
1621 END igs_pe_pers_disablty_pkg;