[Home] [Help]
PACKAGE BODY: APPS.IGW_SUBJECT_INFORMATION_PVT
Source
1 PACKAGE BODY IGW_SUBJECT_INFORMATION_PVT AS
2 --$Header: igwvsuib.pls 115.5 2002/11/15 00:50:28 ashkumar ship $
3
4
5 PROCEDURE CREATE_SUBJECT_INFORMATION
6 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
7 , p_commit IN VARCHAR2 := FND_API.G_FALSE
8 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
9 , p_study_title_id IN NUMBER
10 , p_subject_type_code IN VARCHAR2
11 , p_subject_race_code IN VARCHAR2
12 , p_subject_ethnicity_code IN VARCHAR2
13 , p_no_of_subjects IN NUMBER
14 , x_rowid OUT NOCOPY ROWID
15 , x_return_status OUT NOCOPY VARCHAR2
16 , x_msg_count OUT NOCOPY NUMBER
17 , x_msg_data OUT NOCOPY VARCHAR2) IS
18
19
20 l_study_title_id NUMBER;
21 l_return_status VARCHAR2(1);
22 l_msg_count NUMBER;
23 l_msg_data VARCHAR2(250);
24 l_data VARCHAR2(250);
25 l_msg_index_out NUMBER;
26
27 BEGIN
28 IF p_commit = FND_API.G_TRUE THEN
29 SAVEPOINT create_subject_information;
30 END IF;
31
32 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
33 fnd_msg_pub.initialize;
34 end if;
35
36
37 IGW_SUBJECT_INFORMATION_TBH.INSERT_ROW (
38 X_ROWID => x_rowid,
39 X_STUDY_TITLE_ID => p_study_title_id,
40 X_SUBJECT_TYPE_CODE => p_subject_type_code,
41 X_SUBJECT_RACE_CODE => p_subject_race_code,
42 X_SUBJECT_ETHNICITY_CODE => p_subject_ethnicity_code,
43 X_NO_OF_SUBJECTS => p_no_of_subjects,
44 X_RETURN_STATUS => l_return_status);
45
46 x_return_status := l_return_status;
47
48 l_msg_count := FND_MSG_PUB.count_msg;
49
50 If l_msg_count > 0 THEN
51 x_msg_count := l_msg_count;
52 If l_msg_count = 1 THEN
53 fnd_msg_pub.get
54 (p_encoded => FND_API.G_TRUE ,
55 p_msg_index => 1,
56 p_data => l_data,
57 p_msg_index_out => l_msg_index_out );
58 x_msg_data := l_data;
59 End if;
60 RAISE FND_API.G_EXC_ERROR;
61 End if;
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63 EXCEPTION
64
65 when fnd_api.g_exc_unexpected_error then
66 if p_commit = fnd_api.g_true then
67 rollback to create_subject_information;
68 end if;
69 x_return_status := fnd_api.g_ret_sts_unexp_error;
70
71 Fnd_Msg_Pub.Count_And_Get
72 ( p_count => x_msg_count,
73 p_data => x_msg_data);
74
75
76 when fnd_api.g_exc_error then
77 IF p_commit = fnd_api.g_true then
78 rollback TO create_subject_information;
79 end if;
80 x_return_status := 'E';
81 Fnd_Msg_Pub.Count_And_Get
82 ( p_count => x_msg_count,
83 p_data => x_msg_data);
84
85 when others then
86 if p_commit = fnd_api.g_true then
87 rollback to create_subject_information;
88 end if;
89 x_return_status := fnd_api.g_ret_sts_unexp_error;
90 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SUBJECT_INFORMATION_PVT',
91 p_procedure_name => 'CREATE_SUBJECT_INFORMATION');
92
93 Fnd_Msg_Pub.Count_And_Get
94 ( p_count => x_msg_count,
95 p_data => x_msg_data);
96
97 END CREATE_SUBJECT_INFORMATION;
98
99 ------------------------------------------------------------------------------------------------
100
101 PROCEDURE UPDATE_SUBJECT_INFORMATION
102 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
103 , p_commit IN VARCHAR2 := FND_API.G_FALSE
104 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
105 , p_study_title_id IN NUMBER
106 , p_subject_type_code IN VARCHAR2
107 , p_subject_race_code IN VARCHAR2
108 , p_subject_ethnicity_code IN VARCHAR2
109 , p_no_of_subjects IN NUMBER
110 , p_rowid IN ROWID
111 , p_record_version_number IN NUMBER
112 , x_return_status OUT NOCOPY VARCHAR2
113 , x_msg_count OUT NOCOPY NUMBER
114 , x_msg_data OUT NOCOPY VARCHAR2) IS
115
116 l_msg_data VARCHAR2(250);
117 l_msg_count NUMBER;
118 l_data VARCHAR2(250);
119 l_msg_index_out NUMBER;
120 l_return_status VARCHAR2(1);
121 l_proposal_id NUMBER;
122
123
124 BEGIN
125
126 IF p_commit = FND_API.G_TRUE THEN
127 SAVEPOINT update_study_title;
128 END IF;
129
130 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
131 fnd_msg_pub.initialize;
132 end if;
133
134 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
135
136 CHECK_LOCK(p_rowid
137 ,p_record_version_number
138 ,x_return_status );
139
140 l_msg_count := FND_MSG_PUB.count_msg;
141 If l_msg_count > 0 THEN
142 x_msg_count := l_msg_count;
143 If l_msg_count = 1 THEN
144 fnd_msg_pub.get
145 (p_encoded => FND_API.G_TRUE ,
146 p_msg_index => 1,
147 p_data => l_data,
148 p_msg_index_out => l_msg_index_out );
149
150 x_msg_data := l_data;
151 End if;
152 RAISE FND_API.G_EXC_ERROR;
153 End if;
154
155 IGW_SUBJECT_INFORMATION_TBH.UPDATE_ROW (
156 X_ROWID => p_rowid,
157 X_STUDY_TITLE_ID => p_study_title_id,
158 X_SUBJECT_TYPE_CODE => p_subject_type_code,
159 X_SUBJECT_RACE_CODE => p_subject_race_code,
160 X_SUBJECT_ETHNICITY_CODE => p_subject_ethnicity_code,
161 X_NO_OF_SUBJECTS => p_no_of_subjects,
162 X_RECORD_VERSION_NUMBER => p_record_version_number,
163 X_RETURN_STATUS => l_return_status);
164
165 x_return_status := l_return_status;
166
167 end if;
168
169
170 l_msg_count := FND_MSG_PUB.count_msg;
171 If l_msg_count > 0 THEN
172 x_msg_count := l_msg_count;
173 If l_msg_count = 1 THEN
174 fnd_msg_pub.get
175 (p_encoded => FND_API.G_TRUE ,
176 p_msg_index => 1,
177 p_data => l_data,
178 p_msg_index_out => l_msg_index_out );
179
180 x_msg_data := l_data;
181 End if;
182 RAISE FND_API.G_EXC_ERROR;
183 End if;
184
185 -- standard check of p_commit
186 if fnd_api.to_boolean(p_commit) then
187 commit work;
188 end if;
189
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191 EXCEPTION
192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
193 IF p_commit = FND_API.G_TRUE THEN
194 ROLLBACK TO update_study_title;
195 END IF;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 Fnd_Msg_Pub.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data);
200
201 WHEN FND_API.G_EXC_ERROR THEN
202 IF p_commit = FND_API.G_TRUE THEN
203 ROLLBACK TO update_study_title;
204 END IF;
205 x_return_status := 'E';
206 Fnd_Msg_Pub.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data);
209
210 WHEN OTHERS THEN
211 IF p_commit = FND_API.G_TRUE THEN
212 ROLLBACK TO update_study_title;
213 END IF;
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SUBJECT_INFORMATION_PVT',
216 p_procedure_name => 'UPDATE_SUBJECT_INFORMATION');
217 Fnd_Msg_Pub.Count_And_Get
218 ( p_count => x_msg_count,
219 p_data => x_msg_data);
220
221 END UPDATE_SUBJECT_INFORMATION;
222 -------------------------------------------------------------------------------------------
223 PROCEDURE DELETE_SUBJECT_INFORMATION (
224 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
225 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
226 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
227 ,x_rowid IN VARCHAR2
228 ,p_study_title_id IN NUMBER
229 ,p_record_version_number IN NUMBER
230 ,x_return_status OUT NOCOPY VARCHAR2
231 ,x_msg_count OUT NOCOPY NUMBER
232 ,x_msg_data OUT NOCOPY VARCHAR2) is
233
234 l_msg_count NUMBER;
235 l_msg_data VARCHAR2(250);
236 l_data VARCHAR2(250);
237 l_msg_index_out NUMBER;
238
239
240 BEGIN
241 -- create savepoint
242 IF p_commit = FND_API.G_TRUE THEN
243 SAVEPOINT delete_study_title;
244 END IF;
245
246 -- initialize message list if p_init_msg_list is set to true
247 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
248 fnd_msg_pub.initialize;
249 end if;
250
251 CHECK_LOCK (x_rowid => x_rowid
252 ,p_record_version_number => p_record_version_number
253 ,x_return_status => x_return_status) ;
254
255 l_msg_count := FND_MSG_PUB.count_msg;
256 If l_msg_count > 0 THEN
257 x_msg_count := l_msg_count;
258 If l_msg_count = 1 THEN
259 fnd_msg_pub.get
260 (p_encoded => FND_API.G_TRUE ,
261 p_msg_index => 1,
262 p_data => l_data,
263 p_msg_index_out => l_msg_index_out );
264
265 x_msg_data := l_data;
266 End if;
267 RAISE FND_API.G_EXC_ERROR;
268 End if;
269
270 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
271
272 IGW_SUBJECT_INFORMATION_TBH.DELETE_ROW(
273 x_rowid => x_rowid,
274 x_record_version_number => p_record_version_number,
275 x_return_status => x_return_status);
276
277 end if;
278
279 l_msg_count := FND_MSG_PUB.count_msg;
280 If l_msg_count > 0 THEN
281 x_msg_count := l_msg_count;
282 If l_msg_count = 1 THEN
283 fnd_msg_pub.get
284 (p_encoded => FND_API.G_TRUE ,
285 p_msg_index => 1,
286 p_data => l_data,
287 p_msg_index_out => l_msg_index_out );
288
289 x_msg_data := l_data;
290 End if;
291 RAISE FND_API.G_EXC_ERROR;
292 End if;
293
294
295 -- standard check of p_commit
296 if fnd_api.to_boolean(p_commit) then
297 commit work;
298 end if;
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 EXCEPTION
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304 IF p_commit = FND_API.G_TRUE THEN
305 ROLLBACK TO delete_study_title;
306 END IF;
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 Fnd_Msg_Pub.Count_And_Get
309 ( p_count => x_msg_count,
310 p_data => x_msg_data);
311
312 WHEN FND_API.G_EXC_ERROR THEN
313 IF p_commit = FND_API.G_TRUE THEN
314 ROLLBACK TO update_study_title;
315 END IF;
316 x_return_status := 'E';
317 Fnd_Msg_Pub.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data);
320
321 WHEN OTHERS THEN
322 IF p_commit = FND_API.G_TRUE THEN
323 ROLLBACK TO update_study_title;
324 END IF;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SUBJECT_INFORMATION_PVT',
327 p_procedure_name => 'DELETE_SUBJECT_INFORMATION');
328 Fnd_Msg_Pub.Count_And_Get
329 ( p_count => x_msg_count,
330 p_data => x_msg_data);
331 END;
332
333
334 -------------------------------------------------------------------------------------------
335 PROCEDURE CHECK_LOCK
336 (x_rowid IN VARCHAR2
337 ,p_record_version_number IN NUMBER
338 ,x_return_status OUT NOCOPY VARCHAR2) is
339 l_dummy integer;
340 BEGIN
341 select 1
342 into l_dummy
343 from igw_subject_information
344 where rowid = x_rowid
345 and record_version_number = p_record_version_number;
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
351 FND_MSG_PUB.Add;
352 raise fnd_api.g_exc_error;
353
354 WHEN OTHERS THEN
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_SUBJECT_INFORMATION_PVT',
357 p_procedure_name => 'CHECK_LOCK');
358 raise fnd_api.g_exc_unexpected_error;
359
360
361 END CHECK_LOCK;
362
363 END IGW_SUBJECT_INFORMATION_PVT;