[Home] [Help]
PACKAGE BODY: APPS.IGW_STUDY_TITLES_PVT
Source
1 PACKAGE BODY IGW_STUDY_TITLES_PVT AS
2 --$Header: igwvsttb.pls 115.8 2002/11/18 19:20:11 ashkumar ship $
3
4
5 PROCEDURE CREATE_STUDY_TITLE
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_proposal_id IN NUMBER
10 , p_proposal_number IN VARCHAR2
11 , x_study_title_id OUT NOCOPY NUMBER
12 , p_study_title IN VARCHAR2
13 , p_enrollment_status IN VARCHAR2
14 , p_protocol_number IN VARCHAR2
15 , x_rowid OUT NOCOPY ROWID
16 , x_return_status OUT NOCOPY VARCHAR2
17 , x_msg_count OUT NOCOPY NUMBER
18 , x_msg_data OUT NOCOPY VARCHAR2) IS
19
20
21 l_study_title_id NUMBER;
22 l_return_status VARCHAR2(1);
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(250);
25 l_data VARCHAR2(250);
26 l_msg_index_out NUMBER;
27 l_proposal_id NUMBER;
28
29 BEGIN
30 if p_commit = fnd_api.g_true then
31 savepoint create_study_title;
32 end if;
33
34 if fnd_api.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
35 fnd_msg_pub.initialize;
36 end if;
37
38 l_proposal_id := p_proposal_id;
39
40 /* No need to validate proposal number */
41 /*
42 --PROPOSAL NUMBER
43 --IF (p_proposal_id is null OR p_proposal_id = FND_API.G_MISS_NUM ) THEN
44 IGW_UTILS.GET_PROPOSAL_ID(
45 p_context_field => 'PROPOSAL_ID'
46 ,p_proposal_number => p_proposal_number
47 ,x_proposal_id => l_proposal_id
48 ,x_return_status => l_return_status);
49
50 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
51 x_return_status := 'E';
52 END IF;
53 END IF;
54 */
55
56 l_msg_count := FND_MSG_PUB.count_msg;
57 If l_msg_count > 0 THEN
58 x_msg_count := l_msg_count;
59 If l_msg_count = 1 THEN
60 fnd_msg_pub.get
61 (p_encoded => FND_API.G_TRUE ,
62 p_msg_index => 1,
63 p_data => l_data,
64 p_msg_index_out => l_msg_index_out );
65
66 x_msg_data := l_data;
67 End if;
68 RAISE FND_API.G_EXC_ERROR;
69 End if;
70
71
72 begin
73 select IGW_STUDY_TITLES_S.NEXTVAL
74 into l_study_title_id
75 from dual;
76 exception
77 when others then
78 x_return_status:= fnd_api.g_ret_sts_unexp_error;
79 raise;
80 end;
81
82 IGW_STUDY_TITLES_TBH.INSERT_ROW (
83 X_ROWID => x_rowid,
84 X_STUDY_TITLE_ID => l_study_title_id,
85 X_STUDY_TITLE => p_study_title,
86 X_ENROLLMENT_STATUS => p_enrollment_status,
87 X_PROTOCOL_NUMBER => p_protocol_number,
88 X_PROPOSAL_ID => p_proposal_id,
89 X_RETURN_STATUS => l_return_status);
90
91 x_return_status := l_return_status;
92
93 l_msg_count := FND_MSG_PUB.count_msg;
94
95 If l_msg_count > 0 THEN
96 x_msg_count := l_msg_count;
97 If l_msg_count = 1 THEN
98 fnd_msg_pub.get
99 (p_encoded => FND_API.G_TRUE ,
100 p_msg_index => 1,
101 p_data => l_data,
102 p_msg_index_out => l_msg_index_out );
103 x_msg_data := l_data;
104 End if;
105 RAISE FND_API.G_EXC_ERROR;
106 End if;
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108 EXCEPTION
109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
110 IF p_commit = FND_API.G_TRUE THEN
111 ROLLBACK TO create_study_title;
112 END IF;
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
115 p_data => x_msg_data);
116
117 WHEN FND_API.G_EXC_ERROR THEN
118 IF p_commit = FND_API.G_TRUE THEN
119 ROLLBACK TO create_study_title;
120 END IF;
121 x_return_status := 'E';
122 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
123 p_data => x_msg_data);
124 WHEN OTHERS THEN
125 IF p_commit = FND_API.G_TRUE THEN
126 ROLLBACK TO create_study_title;
127 END IF;
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_STUDY_TITLES_PVT',
130 p_procedure_name => 'CREATE_STUDY_TITLE');
131 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
132 p_data => x_msg_data);
133 END CREATE_STUDY_TITLE;
134
135 ------------------------------------------------------------------------------------------------
136
137 PROCEDURE UPDATE_STUDY_TITLE
138 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
139 , p_commit IN VARCHAR2 := FND_API.G_FALSE
140 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
141 , p_proposal_id IN NUMBER
142 , p_proposal_number IN VARCHAR2
143 , p_study_title_id IN NUMBER
144 , p_study_title IN VARCHAR2
145 , p_enrollment_status IN VARCHAR2
146 , p_protocol_number IN VARCHAR2
147 , p_rowid IN ROWID
148 , p_record_version_number IN NUMBER
149 , x_return_status OUT NOCOPY VARCHAR2
150 , x_msg_count OUT NOCOPY NUMBER
151 , x_msg_data OUT NOCOPY VARCHAR2) IS
152
153 l_msg_data VARCHAR2(250);
154 l_msg_count NUMBER;
155 l_data VARCHAR2(250);
156 l_msg_index_out NUMBER;
157 l_return_status VARCHAR2(1);
158 l_proposal_id NUMBER;
159
160
161 BEGIN
162
163 IF p_commit = FND_API.G_TRUE THEN
164 SAVEPOINT update_study_title;
165 END IF;
166
167 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
168 fnd_msg_pub.initialize;
169 end if;
170
171 l_proposal_id := p_proposal_id;
172
173 /* No need to validate proposal number */
174 /*
175 --PROPOSAL NUMBER
176 --IF (p_proposal_id is null OR p_proposal_id = FND_API.G_MISS_NUM ) THEN
177 IGW_UTILS.GET_PROPOSAL_ID(
178 p_context_field => 'PROPOSAL_ID'
179 ,p_proposal_number => p_proposal_number
180 ,x_proposal_id => l_proposal_id
181 ,x_return_status => l_return_status);
182
183 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
184 x_return_status := 'E';
185 END IF;
186 END IF;
187 */
188
189 l_msg_count := FND_MSG_PUB.count_msg;
190 If l_msg_count > 0 THEN
191 x_msg_count := l_msg_count;
192 If l_msg_count = 1 THEN
193 fnd_msg_pub.get
194 (p_encoded => FND_API.G_TRUE ,
195 p_msg_index => 1,
196 p_data => l_data,
197 p_msg_index_out => l_msg_index_out );
198
199 x_msg_data := l_data;
200 End if;
201 RAISE FND_API.G_EXC_ERROR;
202 End if;
203
204 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
205
206 CHECK_LOCK(p_rowid
207 ,p_record_version_number
208 ,x_return_status );
209
210 l_msg_count := FND_MSG_PUB.count_msg;
211 If l_msg_count > 0 THEN
212 x_msg_count := l_msg_count;
213 If l_msg_count = 1 THEN
214 fnd_msg_pub.get
215 (p_encoded => FND_API.G_TRUE ,
216 p_msg_index => 1,
217 p_data => l_data,
218 p_msg_index_out => l_msg_index_out );
219
220 x_msg_data := l_data;
221 End if;
222 RAISE FND_API.G_EXC_ERROR;
223 End if;
224
225
226 IGW_STUDY_TITLES_TBH.UPDATE_ROW (
227 X_ROWID => p_rowid,
228 X_STUDY_TITLE_ID => p_study_title_id,
229 X_STUDY_TITLE => p_study_title,
230 X_ENROLLMENT_STATUS => p_enrollment_status,
231 X_PROTOCOL_NUMBER => p_protocol_number,
232 X_PROPOSAL_ID => l_proposal_id,
233 X_RECORD_VERSION_NUMBER => p_record_version_number,
234 X_RETURN_STATUS => l_return_status);
235
236 x_return_status := l_return_status;
237
238 end if;
239
240
241 l_msg_count := FND_MSG_PUB.count_msg;
242 If l_msg_count > 0 THEN
243 x_msg_count := l_msg_count;
244 If l_msg_count = 1 THEN
245 fnd_msg_pub.get
246 (p_encoded => FND_API.G_TRUE ,
247 p_msg_index => 1,
248 p_data => l_data,
249 p_msg_index_out => l_msg_index_out );
250
251 x_msg_data := l_data;
252 End if;
253 RAISE FND_API.G_EXC_ERROR;
254 End if;
255
256 -- standard check of p_commit
257 if fnd_api.to_boolean(p_commit) then
258 commit work;
259 end if;
260
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262 EXCEPTION
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264 IF p_commit = FND_API.G_TRUE THEN
265 ROLLBACK TO update_study_title;
266 END IF;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
269 p_data => x_msg_data);
270
271 WHEN FND_API.G_EXC_ERROR THEN
272 IF p_commit = FND_API.G_TRUE THEN
273 ROLLBACK TO update_study_title;
274 END IF;
275 x_return_status := 'E';
276 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
277 p_data => x_msg_data);
278
279 WHEN OTHERS THEN
280 IF p_commit = FND_API.G_TRUE THEN
281 ROLLBACK TO update_study_title;
282 END IF;
283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
284 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_STUDY_TITLES_PVT',
285 p_procedure_name => 'UPDATE_STUDY_TITLE');
286 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
287 p_data => x_msg_data);
288
289 END UPDATE_STUDY_TITLE;
290 -------------------------------------------------------------------------------------------
291 PROCEDURE DELETE_STUDY_TITLE (
292 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
293 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
294 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
295 ,p_study_title_id IN NUMBER
296 ,p_record_version_number IN NUMBER
297 ,x_rowid IN VARCHAR2
298 ,x_return_status OUT NOCOPY VARCHAR2
299 ,x_msg_count OUT NOCOPY NUMBER
300 ,x_msg_data OUT NOCOPY VARCHAR2) is
301
302 l_msg_count NUMBER;
303 l_msg_data VARCHAR2(250);
304 l_data VARCHAR2(250);
305 l_msg_index_out NUMBER;
306
307
308 BEGIN
309 -- create savepoint
310 IF p_commit = FND_API.G_TRUE THEN
311 SAVEPOINT delete_study_title;
312 END IF;
313
314 -- initialize message list if p_init_msg_list is set to true
315 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
316 fnd_msg_pub.initialize;
317 end if;
318
319 CHECK_LOCK (x_rowid => x_rowid
320 ,p_record_version_number => p_record_version_number
321 ,x_return_status => x_return_status) ;
322
323 l_msg_count := FND_MSG_PUB.count_msg;
324 If l_msg_count > 0 THEN
325 x_msg_count := l_msg_count;
326 If l_msg_count = 1 THEN
327 fnd_msg_pub.get
328 (p_encoded => FND_API.G_TRUE ,
329 p_msg_index => 1,
330 p_data => l_data,
331 p_msg_index_out => l_msg_index_out );
332
333 x_msg_data := l_data;
334 End if;
335 RAISE FND_API.G_EXC_ERROR;
336 End if;
337
338 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
339
340 IGW_STUDY_TITLES_TBH.DELETE_ROW(
341 x_rowid => x_rowid,
342 x_study_title_id => p_study_title_id,
343 x_record_version_number => p_record_version_number,
344 x_return_status => x_return_status);
345
346 end if;
347
348 l_msg_count := FND_MSG_PUB.count_msg;
349 If l_msg_count > 0 THEN
350 x_msg_count := l_msg_count;
351 If l_msg_count = 1 THEN
352 fnd_msg_pub.get
353 (p_encoded => FND_API.G_TRUE ,
354 p_msg_index => 1,
355 p_data => l_data,
356 p_msg_index_out => l_msg_index_out );
357
358 x_msg_data := l_data;
359 End if;
360 RAISE FND_API.G_EXC_ERROR;
361 End if;
362
363
364 -- standard check of p_commit
365 if fnd_api.to_boolean(p_commit) then
366 commit work;
367 end if;
368
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370
371 EXCEPTION
372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
373 IF p_commit = FND_API.G_TRUE THEN
374 ROLLBACK TO delete_study_title;
375 END IF;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
378 p_data => x_msg_data);
379
380 WHEN FND_API.G_EXC_ERROR THEN
381 IF p_commit = FND_API.G_TRUE THEN
382 ROLLBACK TO update_study_title;
383 END IF;
384 x_return_status := 'E';
385 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
386 p_data => x_msg_data);
387
388 WHEN OTHERS THEN
389 IF p_commit = FND_API.G_TRUE THEN
390 ROLLBACK TO update_study_title;
391 END IF;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_STUDY_TITLES_PVT',
394 p_procedure_name => 'DELETE_STUDY_TITLE');
395 Fnd_Msg_Pub.Count_And_Get( p_count => x_msg_count,
396 p_data => x_msg_data);
397 END;
398
399
400 -------------------------------------------------------------------------------------------
401 PROCEDURE CHECK_LOCK
402 (x_rowid IN VARCHAR2
403 ,p_record_version_number IN NUMBER
404 ,x_return_status OUT NOCOPY VARCHAR2) is
405 l_dummy integer;
406 BEGIN
407 select 1
408 into l_dummy
409 from igw_study_titles
410 where rowid = x_rowid
411 and record_version_number = p_record_version_number;
412
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
417 FND_MSG_PUB.Add;
418 raise fnd_api.g_exc_error;
419
420 WHEN OTHERS THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_STUDY_TITLES_PVT',
423 p_procedure_name => 'CHECK_LOCK');
424 raise fnd_api.g_exc_unexpected_error;
425
426
427 END CHECK_LOCK;
428
429 END IGW_STUDY_TITLES_PVT;