1 PACKAGE BODY IGS_RE_VAL_CAFOS AS
2 /* $Header: IGSRE05B.pls 120.1 2006/07/25 15:04:15 sommukhe noship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 25-AUG-2001 Bug No. 1956374 .The function GENP_VAL_SDTT_SESS removed
7 --skpandey 10-JUL-2006 Bug#5343912, changed cursor c_cafos definition to include 'per fos_type_code' percentage check for all non CIP type
8 -------------------------------------------------------------------------------------------
9 /*
10 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
11 || Removed program unit (RESP_VAL_CA_CHILDUPD) - from the spec and body. -- kdande
12 */
13 -- Validate IGS_RE_CANDIDATURE field of study percentage.
14 FUNCTION resp_val_cafos_perc(
15 p_person_id IN NUMBER ,
16 p_ca_sequence_number IN NUMBER ,
17 p_message_name OUT NOCOPY VARCHAR2 )
18 RETURN BOOLEAN AS
19 BEGIN -- resp_val_cafos_perc
20 -- Description: This module validates IGS_RE_CDT_FLD_OF_SY.percentage.
21 -- Validations are:
22 -- Total percentage for research IGS_RE_CANDIDATURE must be 100.
23 DECLARE
24
25 CURSOR c_cafos IS
26 SELECT NVL(Sum(cafos.percentage), 0) total, cafos.fos_type_code
27 FROM igs_re_cdt_fld_of_sy_v cafos
28 WHERE cafos.fos_type_code <> 'CIP'
29 AND cafos.person_id = p_person_id
30 AND cafos.ca_sequence_number = p_ca_sequence_number
31 GROUP BY cafos.fos_type_code
32 HAVING Sum(cafos.percentage)<>100;
33
34 c_cafos_rec c_cafos%rowtype;
35
36 BEGIN
37 p_message_name := null;
38 OPEN c_cafos;
39 FETCH c_cafos INTO c_cafos_rec;
40 CLOSE c_cafos;
41 IF (c_cafos_rec.total = 0) THEN
42 --'No values for research IGS_RE_CANDIDATURE field of study entered yet');
43 p_message_name := null;
44 RETURN TRUE;
45 ELSIF (c_cafos_rec.total <> 100) THEN
46 p_message_name := 'IGS_RE_CAND_FIELD_OF_STUDY';
47 RETURN FALSE;
48 END IF;
49 RETURN TRUE;
50 END;
51 EXCEPTION
52 WHEN OTHERS THEN
53 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 END resp_val_cafos_perc;
57 --
58 -- Validate IGS_RE_CANDIDATURE field of study.
59 FUNCTION resp_val_cafos_fos(
60 p_person_id IN NUMBER ,
61 p_ca_sequence_number IN NUMBER ,
62 p_field_of_study IN VARCHAR2 ,
63 p_message_name OUT NOCOPY VARCHAR2 )
64 RETURN BOOLEAN AS
65 BEGIN -- resp_val_cafos_fos
66 -- This module validate IGS_RE_CDT_FLD_OF_SY.IGS_PS_FLD_OF_STUDY. Validations are:
67 -- . IGS_PS_FLD_OF_STUDY is not closed.
68 -- . IGS_RE_GV_FLD_OF_SDY.res_fcd_class_ind is the same for all
69 -- IGS_RE_CDT_FLD_OF_SY for a research IGS_RE_CANDIDATURE.
70 DECLARE
71 v_dummy VARCHAR2(1);
72 v_message_name VARCHAR2(30);
73 v_res_fcd_class_ind IGS_RE_GV_FLD_OF_SDY.res_fcd_class_ind%TYPE;
74 CURSOR c_get_rfci IS
75 SELECT gfos.res_fcd_class_ind
76 FROM IGS_PS_FLD_OF_STUDY fos,
77 IGS_RE_GV_FLD_OF_SDY gfos
78 WHERE fos.field_of_study = p_field_of_study AND
79 Fos.govt_field_of_study = gfos.govt_field_of_study;
80 CURSOR c_not_same(
81 cp_res_fcd_class_ind IGS_RE_GV_FLD_OF_SDY.res_fcd_class_ind%TYPE) IS
82 SELECT 'x'
83 FROM IGS_RE_CDT_FLD_OF_SY cafos,
84 IGS_PS_FLD_OF_STUDY fos,
85 IGS_RE_GV_FLD_OF_SDY gfos
86 WHERE cafos.person_id = p_person_id AND
87 cafos.ca_sequence_number = p_ca_sequence_number AND
88 cafos.field_of_study <> p_field_of_study AND
89 cafos.field_of_study = fos.field_of_study AND
90 fos.govt_field_of_study = gfos.govt_field_of_study AND
91 gfos.res_fcd_class_ind <> cp_res_fcd_class_ind;
92 BEGIN
93 -- Set initial value
94 p_message_name := NULL;
95 -- Validate that field of study is not closed
96 IF IGS_RE_VAL_CAFOS.crsp_val_fos_closed(
97 p_field_of_study,
98 v_message_name) = FALSE THEN
99 p_message_name := v_message_name;
100 RETURN FALSE;
101 END IF;
102 -- Validate field of study is of the same coding scheme as others
103 -- specified (if any) for the research IGS_RE_CANDIDATURE.
104 OPEN c_get_rfci;
105 FETCH c_get_rfci INTO v_res_fcd_class_ind;
106 IF c_get_rfci%NOTFOUND THEN
107 -- If no record found, invalid parameter, this will be handled elsewhere
108 p_message_name := NULL;
109 CLOSE c_get_rfci;
110 RETURN TRUE;
111 END IF;
112 CLOSE c_get_rfci;
113 OPEN c_not_same(
114 v_res_fcd_class_ind);
115 FETCH c_not_same INTO v_dummy;
116 IF c_not_same%FOUND THEN
117 p_message_name := 'IGS_RE_FLD_STDY_BE_SAME_CODE';
118 CLOSE c_not_same;
119 RETURN FALSE;
120 END IF;
121 CLOSE c_not_same;
122 RETURN TRUE;
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF c_get_rfci%ISOPEN THEN
126 CLOSE c_get_rfci;
127 END IF;
128 IF c_not_same%ISOPEN THEN
129 CLOSE c_not_same;
130 END IF;
131 RAISE;
132 END;
133 EXCEPTION
134 WHEN OTHERS THEN
135 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END; -- resp_val_cafos_fos
139 --
140 -- Validate if IGS_PS_FLD_OF_STUDY.field_of_study is closed.
141 FUNCTION crsp_val_fos_closed(
142 p_field_of_study IN VARCHAR2 ,
143 p_message_name OUT NOCOPY VARCHAR2 )
144 RETURN BOOLEAN AS
145 BEGIN --crsp_val_fos_closed
146 --validate if IGS_PS_FLD_OF_STUDY.field_of_study is closed
147 DECLARE
148 v_fos_exists VARCHAR2(1);
149 CURSOR c_fos IS
150 SELECT 'X'
151 FROM IGS_PS_FLD_OF_STUDY fos
152 WHERE fos.field_of_study = p_field_of_study AND
153 fos.closed_ind = 'Y';
154 BEGIN
155 --Set the default message number
156 p_message_name := NULL;
157 --If the closed indicator is 'Y' then set p_message_name
158 OPEN c_fos;
159 FETCH c_fos INTO v_fos_exists;
160 IF (c_fos%FOUND) THEN
161 p_message_name := 'IGS_PS_FIELD_OF_STUDY_CLOSED';
162 CLOSE c_fos;
163 RETURN FALSE;
164 END IF;
165 CLOSE c_fos;
166 RETURN TRUE;
167 END;
168 EXCEPTION
169 WHEN OTHERS THEN
170 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
171 IGS_GE_MSG_STACK.ADD;
172 App_Exception.Raise_Exception;
173 END crsp_val_fos_closed;
174 END IGS_RE_VAL_CAFOS;
[Home] [Help]