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