[Home] [Help]
PACKAGE BODY: APPS.OKE_POOLS_PARTIES_PUB
Source
1 PACKAGE BODY OKE_POOLS_PARTIES_PUB AS
2 /* $Header: OKEPPPPB.pls 120.2 2010/04/09 05:30:55 aveeraba ship $ */
3 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
4 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_pools_parties_pub.';
5
6
7
8 PROCEDURE create_pool(
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 ,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_pool_rec IN oke_pool_pvt.pool_rec_type,
15 x_pool_rec OUT NOCOPY oke_pool_pvt.pool_rec_type) IS
16
17
18 l_pool_rec oke_pool_pvt.pool_rec_type;
19
20 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_POOL';
21 l_api_version CONSTANT NUMBER := 1.0;
22 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
23 --bug 9354391 modified line_number length to 150 as per table
24 l_line_number VARCHAR2(150);
25
26 BEGIN
27 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call oke_pools_parties_pub.create_pool');
28 -- call START_ACTIVITY to create savepoint, check compatibility
29 -- and initialize message list
30 l_return_status := OKE_API.START_ACTIVITY(
31 p_api_name => l_api_name,
32 p_pkg_name => g_pkg_name,
33 p_init_msg_list => p_init_msg_list,
34 l_api_version => l_api_version,
35 p_api_version => p_api_version,
36 p_api_type => g_api_type,
37 x_return_status => x_return_status);
38
39 -- check if activity started successfully
40 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
41 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
42 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
43 raise OKE_API.G_EXCEPTION_ERROR;
44 End If;
45
46 l_pool_rec := p_pool_rec;
47
48 -- call procedure in complex API
49
50 OKE_POOL_PVT.Insert_Row(
51 p_api_version => p_api_version,
52 p_init_msg_list => p_init_msg_list,
53 x_return_status => x_return_status,
54 x_msg_count => x_msg_count,
55 x_msg_data => x_msg_data,
56 p_pool_rec => l_pool_rec,
57 x_pool_rec => x_pool_rec);
58
59
60 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
61 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
62 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
63 raise OKE_API.G_EXCEPTION_ERROR;
64 End If;
65
66 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'end call oke_pools_parties_pub.create_pool');
67
68 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
69 x_msg_data => x_msg_data);
70 EXCEPTION
71 when OKE_API.G_EXCEPTION_ERROR then
72 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
73 p_api_name => l_api_name,
74 p_pkg_name => g_pkg_name,
75 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
76 x_msg_count => x_msg_count,
77 x_msg_data => x_msg_data,
78 p_api_type => g_api_type);
79
80 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
81 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
82 p_api_name => l_api_name,
83 p_pkg_name => g_pkg_name,
84 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
85 x_msg_count => x_msg_count,
86 x_msg_data => x_msg_data,
87 p_api_type => g_api_type);
88
89 when OTHERS then
90 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
91 p_api_name => l_api_name,
92 p_pkg_name => g_pkg_name,
93 p_exc_name => 'OTHERS',
94 x_msg_count => x_msg_count,
95 x_msg_data => x_msg_data,
96 p_api_type => g_api_type);
97
98 END create_pool;
99
100
101
102 PROCEDURE create_pool(
103 p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2 ,
105 x_return_status OUT NOCOPY VARCHAR2,
106 x_msg_count OUT NOCOPY NUMBER,
107 x_msg_data OUT NOCOPY VARCHAR2,
108 p_pool_tbl IN oke_pool_pvt.pool_tbl_type,
109 x_pool_tbl OUT NOCOPY oke_pool_pvt.pool_tbl_type) IS
110
111 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_POOL';
112 l_api_version CONSTANT NUMBER := 1.0;
113 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
114 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
115 i NUMBER;
116 l_pool_tbl oke_pool_pvt.pool_tbl_type;
117 BEGIN
118 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call oke_pools_parties_pub.create_pool');
119 -- call START_ACTIVITY to create savepoint, check compatibility
120 -- and initialize message list
121 l_return_status := OKE_API.START_ACTIVITY(
122 p_api_name => l_api_name,
123 p_pkg_name => g_pkg_name,
124 p_init_msg_list => p_init_msg_list,
125 l_api_version => l_api_version,
126 p_api_version => p_api_version,
127 p_api_type => g_api_type,
128 x_return_status => x_return_status);
129
130 -- check if activity started successfully
131 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
132 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
133 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
134 raise OKE_API.G_EXCEPTION_ERROR;
135 End If;
136
137 OKE_POOL_PVT.Insert_Row(
138 p_api_version => p_api_version,
139 p_init_msg_list => p_init_msg_list,
140 x_return_status => x_return_status,
141 x_msg_count => x_msg_count,
142 x_msg_data => x_msg_data,
143 p_pool_tbl => p_pool_tbl,
144 x_pool_tbl => x_pool_tbl);
145
146
147 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
148 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
150 raise OKE_API.G_EXCEPTION_ERROR;
151 End If;
152
153 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'end call oke_pools_parties_pub.create_pool');
154 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data);
156 EXCEPTION
157 when OKE_API.G_EXCEPTION_ERROR then
158 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
159 p_api_name => l_api_name,
160 p_pkg_name => g_pkg_name,
161 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_api_type => g_api_type);
165
166 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
167 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
168 p_api_name => l_api_name,
169 p_pkg_name => g_pkg_name,
170 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
171 x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data,
173 p_api_type => g_api_type);
174
175 when OTHERS then
176 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
177 p_api_name => l_api_name,
178 p_pkg_name => g_pkg_name,
179 p_exc_name => 'OTHERS',
180 x_msg_count => x_msg_count,
181 x_msg_data => x_msg_data,
182 p_api_type => g_api_type);
183
184 END create_pool;
185
186 PROCEDURE update_pool(
187 p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2 ,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 p_pool_rec IN oke_pool_pvt.pool_rec_type,
193 x_pool_rec OUT NOCOPY oke_pool_pvt.pool_rec_type) IS
194
195 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_POOL';
196 l_api_version CONSTANT NUMBER := 1.0;
197 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
198 BEGIN
199 -- call START_ACTIVITY to create savepoint, check compatibility
200 -- and initialize message list
201 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call oke_pools_parties_pub.update_pool');
202
203 l_return_status := OKE_API.START_ACTIVITY(
204 p_api_name => l_api_name,
205 p_pkg_name => g_pkg_name,
206 p_init_msg_list => p_init_msg_list,
207 l_api_version => l_api_version,
208 p_api_version => p_api_version,
209 p_api_type => g_api_type,
210 x_return_status => x_return_status);
211
212 -- check if activity started successfully
213 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
214 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
215 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
216 raise OKE_API.G_EXCEPTION_ERROR;
217 End If;
218
219
220 -- call complex api
221 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.going to call pvt_update_row');
222 OKE_POOL_PVT.Update_Row(
223 p_api_version => p_api_version,
224 p_init_msg_list => p_init_msg_list,
225 x_return_status => x_return_status,
226 x_msg_count => x_msg_count,
227 x_msg_data => x_msg_data,
228 p_pool_rec => p_pool_rec,
229 x_pool_rec => x_pool_rec);
230
231
232 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
233 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
234 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
235 raise OKE_API.G_EXCEPTION_ERROR;
236 End If;
237
238 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data);
240 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'end call oke_pools_parties_pub.update_pool');
241
242 EXCEPTION
243 when OKE_API.G_EXCEPTION_ERROR then
244 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
245 p_api_name => l_api_name,
246 p_pkg_name => g_pkg_name,
247 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data,
250 p_api_type => g_api_type);
251
252 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
253 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
254 p_api_name => l_api_name,
255 p_pkg_name => g_pkg_name,
256 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data,
259 p_api_type => g_api_type);
260
261 when OTHERS then
262 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
263 p_api_name => l_api_name,
264 p_pkg_name => g_pkg_name,
265 p_exc_name => 'OTHERS',
266 x_msg_count => x_msg_count,
267 x_msg_data => x_msg_data,
268 p_api_type => g_api_type);
269
270 END update_pool;
271
272
273 PROCEDURE update_pool(
274 p_api_version IN NUMBER,
275 p_init_msg_list IN VARCHAR2 ,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 p_pool_tbl IN oke_pool_pvt.pool_tbl_type,
280 x_pool_tbl OUT NOCOPY oke_pool_pvt.pool_tbl_type) IS
281
282 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_POOL';
283 l_api_version CONSTANT NUMBER := 1.0;
284 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
285 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
286 i NUMBER;
287 BEGIN
288 -- call START_ACTIVITY to create savepoint, check compatibility
289 -- and initialize message list
290
291 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.start call pub update table');
292
293 l_return_status := OKE_API.START_ACTIVITY(
294 p_api_name => l_api_name,
295 p_pkg_name => g_pkg_name,
296 p_init_msg_list => p_init_msg_list,
297 l_api_version => l_api_version,
298 p_api_version => p_api_version,
299 p_api_type => g_api_type,
300 x_return_status => x_return_status);
301
302 -- check if activity started successfully
303 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
304 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
305 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
306 raise OKE_API.G_EXCEPTION_ERROR;
307
308 End If;
309
310 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.going to call PVT.update_row table version');
311
312 OKE_POOL_PVT.Update_Row(
313 p_api_version => p_api_version,
314 p_init_msg_list => p_init_msg_list,
315 x_return_status => x_return_status,
316 x_msg_count => x_msg_count,
317 x_msg_data => x_msg_data,
318 p_pool_tbl => p_pool_tbl,
319 x_pool_tbl => x_pool_tbl);
320
321
322 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
323 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
324 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
325 raise OKE_API.G_EXCEPTION_ERROR;
326 End If;
327
328 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data);
330 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.ended');
331 EXCEPTION
332 when OKE_API.G_EXCEPTION_ERROR then
333 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
334 p_api_name => l_api_name,
335 p_pkg_name => g_pkg_name,
336 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
337 x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data,
339 p_api_type => g_api_type);
340
341 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
342 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
343 p_api_name => l_api_name,
344 p_pkg_name => g_pkg_name,
345 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data,
348 p_api_type => g_api_type);
349
350 when OTHERS then
351 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
352 p_api_name => l_api_name,
353 p_pkg_name => g_pkg_name,
354 p_exc_name => 'OTHERS',
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_api_type => g_api_type);
358
359 END update_pool;
360
361
362 PROCEDURE delete_pool(
363
364 p_api_version IN NUMBER,
365 p_init_msg_list IN VARCHAR2 ,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 p_funding_pool_id IN NUMBER) IS
370
371 l_pool_rec oke_pool_pvt.pool_rec_type;
372 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_POOL';
373 l_api_version CONSTANT NUMBER := 1.0;
374 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
375
376 l_current_id NUMBER;
377 l_temp NUMBER;
378
379 CURSOR l_csr IS
380 SELECT POOL_PARTY_ID
381 FROM OKE_POOL_PARTIES
382 WHERE FUNDING_POOL_ID = p_funding_pool_id;
383
384 Cursor l_csr_id IS
385 select funding_pool_id
386 from oke_funding_pools
387 where funding_pool_id=p_funding_pool_id;
388
389
390 BEGIN
391 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call oke_pools_parties_pub.delete_pool');
392 -- call START_ACTIVITY to create savepoint, check compatibility
393 -- and initialize message list
394 l_return_status := OKE_API.START_ACTIVITY(
395 p_api_name => l_api_name,
396 p_pkg_name => g_pkg_name,
397 p_init_msg_list => p_init_msg_list,
398 l_api_version => l_api_version,
399 p_api_version => p_api_version,
400 p_api_type => g_api_type,
401 x_return_status => x_return_status);
402
403 -- check if activity started successfully
404 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
405 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
407 raise OKE_API.G_EXCEPTION_ERROR;
408 End If;
409
410
411 OPEN l_csr_id;
412 FETCH l_csr_id INTO l_temp;
413 IF l_csr_id%NOTFOUND THEN
414 OKE_API.SET_MESSAGE(
415 p_app_name =>g_app_name,
416 p_msg_name =>oke_api.g_invalid_value,
417 p_token1 =>oke_api.g_col_name_token,
418 p_token1_value =>'funding_pool_id');
419 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'must provide valid funding_pool_id');
420 RAISE OKE_API.G_EXCEPTION_ERROR;
421 END IF;
422 CLOSE l_csr_id;
423
424
425 l_pool_rec.funding_pool_id := p_funding_pool_id;
426
427 OPEN l_csr;
428 LOOP
429 FETCH l_csr INTO l_current_id;
430 EXIT WHEN l_csr%NOTFOUND;
431 delete_party(p_api_version => p_api_version,
432 p_init_msg_list => p_init_msg_list,
433 x_return_status => x_return_status,
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_pool_party_id => l_current_id);
437
438 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
439 close l_csr;
440 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
441 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
442 close l_csr;
443 raise OKE_API.G_EXCEPTION_ERROR;
444 End If;
445
446 END LOOP;
447 CLOSE l_csr;
448
449 -- call complex api
450
451 OKE_POOL_PVT.delete_row(
452 p_api_version => p_api_version,
453 p_init_msg_list => p_init_msg_list,
454 x_return_status => x_return_status,
455 x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data,
457 p_pool_rec => l_pool_rec);
458
459
460 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
461 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
462 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
463 raise OKE_API.G_EXCEPTION_ERROR;
464 End If;
465
466 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'end call oke_pools_parties_pub.delete_pool');
467 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data);
469 EXCEPTION
470 when OKE_API.G_EXCEPTION_ERROR then
471 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
472 p_api_name => l_api_name,
473 p_pkg_name => g_pkg_name,
474 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data,
477 p_api_type => g_api_type);
478
479 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
480 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
481 p_api_name => l_api_name,
482 p_pkg_name => g_pkg_name,
483 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data,
486 p_api_type => g_api_type);
487
488 when OTHERS then
489 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
490 p_api_name => l_api_name,
491 p_pkg_name => g_pkg_name,
492 p_exc_name => 'OTHERS',
493 x_msg_count => x_msg_count,
494 x_msg_data => x_msg_data,
495 p_api_type => g_api_type);
496
497 END delete_pool;
498
499
500 PROCEDURE delete_pool(
501
502 p_api_version IN NUMBER,
503 p_init_msg_list IN VARCHAR2 ,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_pool_rec IN oke_pool_pvt.pool_rec_type) IS
508
509 l_pool_rec oke_pool_pvt.pool_rec_type;
510 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_POOL';
511 l_api_version CONSTANT NUMBER := 1.0;
512 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
513 BEGIN
514 -- call START_ACTIVITY to create savepoint, check compatibility
515 -- and initialize message list
516 l_return_status := OKE_API.START_ACTIVITY(
517 p_api_name => l_api_name,
518 p_pkg_name => g_pkg_name,
519 p_init_msg_list => p_init_msg_list,
520 l_api_version => l_api_version,
521 p_api_version => p_api_version,
522 p_api_type => g_api_type,
523 x_return_status => x_return_status);
524
525 -- check if activity started successfully
526 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
527 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
528 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
529 raise OKE_API.G_EXCEPTION_ERROR;
530 End If;
531
532 l_pool_rec := p_pool_rec;
533
534 -- call complex api
535
536 delete_pool(
537 p_api_version => p_api_version,
538 p_init_msg_list => p_init_msg_list,
539 x_return_status => x_return_status,
540 x_msg_count => x_msg_count,
541 x_msg_data => x_msg_data,
542 p_funding_pool_id => p_pool_rec.funding_pool_id);
543
544
545 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
546 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
547 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
548 raise OKE_API.G_EXCEPTION_ERROR;
549 End If;
550
551
552 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data);
554 EXCEPTION
555 when OKE_API.G_EXCEPTION_ERROR then
556 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
557 p_api_name => l_api_name,
558 p_pkg_name => g_pkg_name,
559 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_api_type => g_api_type);
563
564 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
565 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
566 p_api_name => l_api_name,
567 p_pkg_name => g_pkg_name,
568 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data,
571 p_api_type => g_api_type);
572
573 when OTHERS then
574 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
575 p_api_name => l_api_name,
576 p_pkg_name => g_pkg_name,
577 p_exc_name => 'OTHERS',
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 p_api_type => g_api_type);
581
582 END delete_pool;
583
584 PROCEDURE delete_pool(
585 p_api_version IN NUMBER,
586 p_init_msg_list IN VARCHAR2 ,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590 p_pool_tbl IN oke_pool_pvt.pool_tbl_type) IS
591
592 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_POOL';
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
595 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
596 i NUMBER;
597 BEGIN
598 -- call START_ACTIVITY to create savepoint, check compatibility
599 -- and initialize message list
600 l_return_status := OKE_API.START_ACTIVITY(
601 p_api_name => l_api_name,
602 p_pkg_name => g_pkg_name,
603 p_init_msg_list => p_init_msg_list,
604 l_api_version => l_api_version,
605 p_api_version => p_api_version,
606 p_api_type => g_api_type,
607 x_return_status => x_return_status);
608
609 -- check if activity started successfully
610 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
611 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
612 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
613 raise OKE_API.G_EXCEPTION_ERROR;
614 End If;
615
616
617
618 If (p_pool_tbl.COUNT>0) Then
619 i:=p_pool_tbl.FIRST;
620 LOOP
621 Delete_Pool(
622 p_api_version => p_api_version,
623 p_init_msg_list => p_init_msg_list,
624 x_return_status => x_return_status,
625 x_msg_count => x_msg_count,
626 x_msg_data => x_msg_data,
627 p_funding_pool_id => p_pool_tbl(i).funding_pool_id);
628
629 -- store the highest degree of error
630 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
631 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
632 l_overall_status := x_return_status;
633 End If;
634 End If;
635
636
637 EXIT WHEN (i = p_pool_tbl.LAST);
638 i := p_pool_tbl.NEXT(i);
639 END LOOP;
640 x_return_status := l_overall_status;
641 End If;
642
643
644 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
645 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
646 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
647 raise OKE_API.G_EXCEPTION_ERROR;
648 End If;
649
650 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data);
652 EXCEPTION
653 when OKE_API.G_EXCEPTION_ERROR then
654 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
655 p_api_name => l_api_name,
656 p_pkg_name => g_pkg_name,
657 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
658 x_msg_count => x_msg_count,
659 x_msg_data => x_msg_data,
660 p_api_type => g_api_type);
661
662 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
663 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
664 p_api_name => l_api_name,
665 p_pkg_name => g_pkg_name,
666 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
667 x_msg_count => x_msg_count,
668 x_msg_data => x_msg_data,
669 p_api_type => g_api_type);
670
671 when OTHERS then
672 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
673 p_api_name => l_api_name,
674 p_pkg_name => g_pkg_name,
675 p_exc_name => 'OTHERS',
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 p_api_type => g_api_type);
679
680 END delete_pool;
681
682
683 PROCEDURE lock_pool(
684 p_api_version IN NUMBER,
685 p_init_msg_list IN VARCHAR2 ,
686 x_return_status OUT NOCOPY VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER,
688 x_msg_data OUT NOCOPY VARCHAR2,
689 p_pool_rec IN OKE_POOL_PVT.pool_rec_type) IS
690
691
692 l_del_rec oke_deliverable_pvt.del_rec_type;
693 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_POOL';
694 l_api_version CONSTANT NUMBER := 1.0;
695 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
696
697 BEGIN
698 l_return_status := OKE_API.START_ACTIVITY(
699 p_api_name => l_api_name,
700 p_pkg_name => g_pkg_name,
701 p_init_msg_list => p_init_msg_list,
702 l_api_version => l_api_version,
703 p_api_version => p_api_version,
704 p_api_type => g_api_type,
705 x_return_status => x_return_status);
706
707 -- check if activity started successfully
708 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
709 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
710 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
711 raise OKE_API.G_EXCEPTION_ERROR;
712 End If;
713
714 OKE_POOL_PVT.Lock_Row(
715 p_api_version => p_api_version,
716 p_init_msg_list => p_init_msg_list,
717 x_return_status => x_return_status,
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data,
720 p_pool_rec => p_pool_rec);
721
722 -- check return status
723 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
724 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
725 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
726 raise OKE_API.G_EXCEPTION_ERROR;
727 End If;
728
729 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
730 x_msg_data => x_msg_data);
731 EXCEPTION
732 when OKE_API.G_EXCEPTION_ERROR then
733 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
734 p_api_name => l_api_name,
735 p_pkg_name => g_pkg_name,
736 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_api_type => g_api_type);
740
741 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
742 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
743 p_api_name => l_api_name,
744 p_pkg_name => g_pkg_name,
745 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
746 x_msg_count => x_msg_count,
747 x_msg_data => x_msg_data,
748 p_api_type => g_api_type);
749
750 when OTHERS then
751 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
752 p_api_name => l_api_name,
753 p_pkg_name => g_pkg_name,
754 p_exc_name => 'OTHERS',
755 x_msg_count => x_msg_count,
756 x_msg_data => x_msg_data,
757 p_api_type => g_api_type);
758
759 END lock_pool;
760
761 PROCEDURE lock_pool(
762 p_api_version IN NUMBER,
763 p_init_msg_list IN VARCHAR2 ,
764 x_return_status OUT NOCOPY VARCHAR2,
765 x_msg_count OUT NOCOPY NUMBER,
766 x_msg_data OUT NOCOPY VARCHAR2,
767 p_pool_tbl IN oke_pool_pvt.pool_tbl_type) IS
768
769 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_POOL';
770 l_api_version CONSTANT NUMBER := 1.0;
771 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
772 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
773 i NUMBER;
774 BEGIN
775 -- call START_ACTIVITY to create savepoint, check compatibility
776 -- and initialize message list
777 l_return_status := OKE_API.START_ACTIVITY(
778 p_api_name => l_api_name,
779 p_pkg_name => g_pkg_name,
780 p_init_msg_list => p_init_msg_list,
781 l_api_version => l_api_version,
782 p_api_version => p_api_version,
783 p_api_type => g_api_type,
784 x_return_status => x_return_status);
785
786 -- check if activity started successfully
787 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
788 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
789 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
790 raise OKE_API.G_EXCEPTION_ERROR;
791 End If;
792
793 If (p_pool_tbl.COUNT > 0) Then
794 i := p_pool_tbl.FIRST;
795 LOOP
796 -- call procedure in complex API
797 OKE_POOL_PVT.lock_row(
798 p_api_version => p_api_version,
799 p_init_msg_list => p_init_msg_list,
800 x_return_status => x_return_status,
801 x_msg_count => x_msg_count,
802 x_msg_data => x_msg_data,
803 p_pool_rec => p_pool_tbl(i));
804
805 -- store the highest degree of error
806 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
807 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
808 l_overall_status := x_return_status;
809 End If;
810 End If;
811 EXIT WHEN (i = p_pool_tbl.LAST);
812 i := p_pool_tbl.NEXT(i);
813 END LOOP;
814 -- return overall status
815 x_return_status := l_overall_status;
816 End If;
817
818 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
819 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
821 raise OKE_API.G_EXCEPTION_ERROR;
822 End If;
823
824 -- end activity
825 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
826 x_msg_data => x_msg_data);
827 EXCEPTION
828 when OKE_API.G_EXCEPTION_ERROR then
829 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
830 p_api_name => l_api_name,
831 p_pkg_name => g_pkg_name,
832 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
833 x_msg_count => x_msg_count,
834 x_msg_data => x_msg_data,
835 p_api_type => g_api_type);
836
837 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
838 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
839 p_api_name => l_api_name,
840 p_pkg_name => g_pkg_name,
841 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data,
844 p_api_type => g_api_type);
845
846 when OTHERS then
847 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
848 p_api_name => l_api_name,
849 p_pkg_name => g_pkg_name,
850 p_exc_name => 'OTHERS',
851 x_msg_count => x_msg_count,
852 x_msg_data => x_msg_data,
853 p_api_type => g_api_type);
854
855 END lock_pool;
856
857
858 --- start of party section ------------------
859
860
861
862 PROCEDURE create_party(
863 p_api_version IN NUMBER,
864 p_init_msg_list IN VARCHAR2 ,
865 x_return_status OUT NOCOPY VARCHAR2,
866 x_msg_count OUT NOCOPY NUMBER,
867 x_msg_data OUT NOCOPY VARCHAR2,
868 p_party_rec IN oke_party_pvt.party_rec_type,
869 x_party_rec OUT NOCOPY oke_party_pvt.party_rec_type) IS
870
871
872 l_party_rec oke_party_pvt.party_rec_type;
873
874 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY';
875 l_api_version CONSTANT NUMBER := 1.0;
876 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
877 --bug 9354391 modified line_number length to 150 as per table
878 l_line_number VARCHAR2(150);
879
880 BEGIN
881 -- call START_ACTIVITY to create savepoint, check compatibility
882 -- and initialize message list
883 l_return_status := OKE_API.START_ACTIVITY(
884 p_api_name => l_api_name,
885 p_pkg_name => g_pkg_name,
886 p_init_msg_list => p_init_msg_list,
887 l_api_version => l_api_version,
888 p_api_version => p_api_version,
889 p_api_type => g_api_type,
890 x_return_status => x_return_status);
891
892 -- check if activity started successfully
893 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
894 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
895 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
896 raise OKE_API.G_EXCEPTION_ERROR;
897 End If;
898
899 l_party_rec := p_party_rec;
900
901 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call partys pvt');
902
903 -- call procedure in complex API
904
905 OKE_PARTY_PVT.Insert_Row(
906 p_api_version => p_api_version,
907 p_init_msg_list => p_init_msg_list,
908 x_return_status => x_return_status,
909 x_msg_count => x_msg_count,
910 x_msg_data => x_msg_data,
911 p_party_rec => l_party_rec,
912 x_party_rec => x_party_rec);
913
914
915 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
916 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
917 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
918 raise OKE_API.G_EXCEPTION_ERROR;
919 End If;
920
921 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'oke call passed');
922
923 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
924 x_msg_data => x_msg_data);
925 EXCEPTION
926 when OKE_API.G_EXCEPTION_ERROR then
927 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
928 p_api_name => l_api_name,
929 p_pkg_name => g_pkg_name,
930 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 p_api_type => g_api_type);
934
935 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
936 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
937 p_api_name => l_api_name,
938 p_pkg_name => g_pkg_name,
939 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data,
942 p_api_type => g_api_type);
943
944 when OTHERS then
945 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
946 p_api_name => l_api_name,
947 p_pkg_name => g_pkg_name,
948 p_exc_name => 'OTHERS',
949 x_msg_count => x_msg_count,
950 x_msg_data => x_msg_data,
951 p_api_type => g_api_type);
952
953 END create_party;
954
955
956
957 PROCEDURE create_party(
958 p_api_version IN NUMBER,
959 p_init_msg_list IN VARCHAR2 ,
960 x_return_status OUT NOCOPY VARCHAR2,
961 x_msg_count OUT NOCOPY NUMBER,
962 x_msg_data OUT NOCOPY VARCHAR2,
963 p_party_tbl IN oke_party_pvt.party_tbl_type,
964 x_party_tbl OUT NOCOPY oke_party_pvt.party_tbl_type) IS
965
966 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PARTY';
967 l_api_version CONSTANT NUMBER := 1.0;
968 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
969 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
970 i NUMBER;
971 l_party_tbl oke_party_pvt.party_tbl_type;
972 BEGIN
973 -- call START_ACTIVITY to create savepoint, check compatibility
974 -- and initialize message list
975 l_return_status := OKE_API.START_ACTIVITY(
976 p_api_name => l_api_name,
977 p_pkg_name => g_pkg_name,
978 p_init_msg_list => p_init_msg_list,
979 l_api_version => l_api_version,
980 p_api_version => p_api_version,
981 p_api_type => g_api_type,
982 x_return_status => x_return_status);
983
984 -- check if activity started successfully
985 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
986 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
987 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
988 raise OKE_API.G_EXCEPTION_ERROR;
989 End If;
990
991 OKE_PARTY_PVT.Insert_Row(
992 p_api_version => p_api_version,
993 p_init_msg_list => p_init_msg_list,
994 x_return_status => x_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_party_tbl => p_party_tbl,
998 x_party_tbl => x_party_tbl);
999
1000
1001 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1002 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1003 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1004 raise OKE_API.G_EXCEPTION_ERROR;
1005 End If;
1006
1007 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data);
1009 EXCEPTION
1010 when OKE_API.G_EXCEPTION_ERROR then
1011 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1012 p_api_name => l_api_name,
1013 p_pkg_name => g_pkg_name,
1014 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data,
1017 p_api_type => g_api_type);
1018
1019 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1020 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1021 p_api_name => l_api_name,
1022 p_pkg_name => g_pkg_name,
1023 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 p_api_type => g_api_type);
1027
1028 when OTHERS then
1029 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1030 p_api_name => l_api_name,
1031 p_pkg_name => g_pkg_name,
1032 p_exc_name => 'OTHERS',
1033 x_msg_count => x_msg_count,
1034 x_msg_data => x_msg_data,
1035 p_api_type => g_api_type);
1036
1037 END create_party;
1038
1039
1040 PROCEDURE update_party(
1041 p_api_version IN NUMBER,
1042 p_init_msg_list IN VARCHAR2 ,
1043 x_return_status OUT NOCOPY VARCHAR2,
1044 x_msg_count OUT NOCOPY NUMBER,
1045 x_msg_data OUT NOCOPY VARCHAR2,
1046 p_party_rec IN oke_party_pvt.party_rec_type,
1047 x_party_rec OUT NOCOPY oke_party_pvt.party_rec_type) IS
1048
1049 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY';
1050 l_api_version CONSTANT NUMBER := 1.0;
1051 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1052
1053
1054 l_dummy_val VARCHAR2(1):='?';
1055 CURSOR l_csr IS
1056 SELECT 'x'
1057 FROM OKE_K_FUNDING_SOURCES
1058 WHERE POOL_PARTY_ID = p_party_rec.POOL_PARTY_ID;
1059
1060
1061 BEGIN
1062 -- call START_ACTIVITY to create savepoint, check compatibility
1063 -- and initialize message list
1064
1065 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.START TO CALL PUB LINE REC UPDATE');
1066 l_return_status := OKE_API.START_ACTIVITY(
1067 p_api_name => l_api_name,
1068 p_pkg_name => g_pkg_name,
1069 p_init_msg_list => p_init_msg_list,
1070 l_api_version => l_api_version,
1071 p_api_version => p_api_version,
1072 p_api_type => g_api_type,
1073 x_return_status => x_return_status);
1074
1075 -- check if activity started successfully
1076 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1077 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1078 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1079 raise OKE_API.G_EXCEPTION_ERROR;
1080 End If;
1081
1082
1083 -- call complex api
1084 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.going to call pvt_update_row');
1085 OKE_PARTY_PVT.Update_Row(
1086 p_api_version => p_api_version,
1087 p_init_msg_list => p_init_msg_list,
1088 x_return_status => x_return_status,
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data,
1091 p_party_rec => p_party_rec,
1092 x_party_rec => x_party_rec);
1093
1094
1095 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1096 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1098 raise OKE_API.G_EXCEPTION_ERROR;
1099 End If;
1100
1101 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data);
1103 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.ended');
1104 EXCEPTION
1105 when OKE_API.G_EXCEPTION_ERROR then
1106 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1107 p_api_name => l_api_name,
1108 p_pkg_name => g_pkg_name,
1109 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data,
1112 p_api_type => g_api_type);
1113
1114 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1115 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1116 p_api_name => l_api_name,
1117 p_pkg_name => g_pkg_name,
1118 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1119 x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data,
1121 p_api_type => g_api_type);
1122
1123 when OTHERS then
1124 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1125 p_api_name => l_api_name,
1126 p_pkg_name => g_pkg_name,
1127 p_exc_name => 'OTHERS',
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data,
1130 p_api_type => g_api_type);
1131
1132 END update_party;
1133
1134
1135 PROCEDURE update_party(
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2 ,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_party_tbl IN oke_party_pvt.party_tbl_type,
1142 x_party_tbl OUT NOCOPY oke_party_pvt.party_tbl_type) IS
1143
1144 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY';
1145 l_api_version CONSTANT NUMBER := 1.0;
1146 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1147 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1148 i NUMBER;
1149
1150
1151 l_dummy_val VARCHAR2(1):='?';
1152 CURSOR l_csr (p_row NUMBER) IS
1153 SELECT 'x'
1154 FROM OKE_K_FUNDING_SOURCES
1155 WHERE POOL_PARTY_ID = p_party_tbl(p_row).POOL_PARTY_ID;
1156
1157 BEGIN
1158 -- call START_ACTIVITY to create savepoint, check compatibility
1159 -- and initialize message list
1160
1161 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.start call pub update table');
1162
1163 l_return_status := OKE_API.START_ACTIVITY(
1164 p_api_name => l_api_name,
1165 p_pkg_name => g_pkg_name,
1166 p_init_msg_list => p_init_msg_list,
1167 l_api_version => l_api_version,
1168 p_api_version => p_api_version,
1169 p_api_type => g_api_type,
1170 x_return_status => x_return_status);
1171
1172 -- check if activity started successfully
1173 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1174 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1176 raise OKE_API.G_EXCEPTION_ERROR;
1177
1178 End If;
1179
1180 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.going to call PVT.update_row table version');
1181
1182 OKE_PARTY_PVT.Update_Row(
1183 p_api_version => p_api_version,
1184 p_init_msg_list => p_init_msg_list,
1185 x_return_status => x_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_party_tbl => p_party_tbl,
1189 x_party_tbl => x_party_tbl);
1190
1191
1192
1193 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1194 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1195 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1196 raise OKE_API.G_EXCEPTION_ERROR;
1197 End If;
1198
1199 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1200 x_msg_data => x_msg_data);
1201 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'PUB.ended');
1202 EXCEPTION
1203 when OKE_API.G_EXCEPTION_ERROR then
1204 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1205 p_api_name => l_api_name,
1206 p_pkg_name => g_pkg_name,
1207 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_api_type => g_api_type);
1211
1212 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1213 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1214 p_api_name => l_api_name,
1215 p_pkg_name => g_pkg_name,
1216 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 p_api_type => g_api_type);
1220
1221 when OTHERS then
1222 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1223 p_api_name => l_api_name,
1224 p_pkg_name => g_pkg_name,
1225 p_exc_name => 'OTHERS',
1226 x_msg_count => x_msg_count,
1227 x_msg_data => x_msg_data,
1228 p_api_type => g_api_type);
1229
1230 END update_party;
1231
1232
1233 PROCEDURE delete_party(
1234
1235 p_api_version IN NUMBER,
1236 p_init_msg_list IN VARCHAR2 ,
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2,
1240 p_party_rec IN oke_party_pvt.party_rec_type) IS
1241
1242 l_party_rec oke_party_pvt.party_rec_type;
1243 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY';
1244 l_api_version CONSTANT NUMBER := 1.0;
1245 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1246 BEGIN
1247 -- call START_ACTIVITY to create savepoint, check compatibility
1248 -- and initialize message list
1249 l_return_status := OKE_API.START_ACTIVITY(
1250 p_api_name => l_api_name,
1251 p_pkg_name => g_pkg_name,
1252 p_init_msg_list => p_init_msg_list,
1253 l_api_version => l_api_version,
1254 p_api_version => p_api_version,
1255 p_api_type => g_api_type,
1256 x_return_status => x_return_status);
1257
1258 -- check if activity started successfully
1259 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1260 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1262 raise OKE_API.G_EXCEPTION_ERROR;
1263 End If;
1264
1265 l_party_rec := p_party_rec;
1266
1267 -- call complex api
1268
1269 delete_party(
1270 p_api_version => p_api_version,
1271 p_init_msg_list => p_init_msg_list,
1272 x_return_status => x_return_status,
1273 x_msg_count => x_msg_count,
1274 x_msg_data => x_msg_data,
1275 p_pool_party_id => p_party_rec.pool_party_id);
1276
1277
1278 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1279 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1280 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1281 raise OKE_API.G_EXCEPTION_ERROR;
1282 End If;
1283
1284
1285 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data);
1287 EXCEPTION
1288 when OKE_API.G_EXCEPTION_ERROR then
1289 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1290 p_api_name => l_api_name,
1291 p_pkg_name => g_pkg_name,
1292 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1293 x_msg_count => x_msg_count,
1294 x_msg_data => x_msg_data,
1295 p_api_type => g_api_type);
1296
1297 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1298 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1299 p_api_name => l_api_name,
1300 p_pkg_name => g_pkg_name,
1301 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 p_api_type => g_api_type);
1305
1306 when OTHERS then
1307 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1308 p_api_name => l_api_name,
1309 p_pkg_name => g_pkg_name,
1310 p_exc_name => 'OTHERS',
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 p_api_type => g_api_type);
1314
1315 END delete_party;
1316
1317
1318
1319 PROCEDURE delete_party(
1320
1321 p_api_version IN NUMBER,
1322 p_init_msg_list IN VARCHAR2 ,
1323 x_return_status OUT NOCOPY VARCHAR2,
1324 x_msg_count OUT NOCOPY NUMBER,
1325 x_msg_data OUT NOCOPY VARCHAR2,
1326 p_pool_party_id IN NUMBER) IS
1327
1328 l_party_rec oke_party_pvt.party_rec_type;
1329 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY';
1330 l_api_version CONSTANT NUMBER := 1.0;
1331 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1332 l_current_id NUMBER;
1333 l_temp NUMBER;
1334
1335 CURSOR l_csr IS
1336 SELECT FUNDING_SOURCE_ID
1337 FROM OKE_K_FUNDING_SOURCES
1338 WHERE POOL_PARTY_ID = p_pool_party_id;
1339
1340 Cursor l_csr_id IS
1341 select pool_party_id
1342 from oke_pool_parties
1343 where pool_party_id=p_pool_party_id;
1344
1345 BEGIN
1346 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start call oke_pools_parties_pub.delete_party');
1347 -- call START_ACTIVITY to create savepoint, check compatibility
1348 -- and initialize message list
1349 l_return_status := OKE_API.START_ACTIVITY(
1350 p_api_name => l_api_name,
1351 p_pkg_name => g_pkg_name,
1352 p_init_msg_list => p_init_msg_list,
1353 l_api_version => l_api_version,
1354 p_api_version => p_api_version,
1355 p_api_type => g_api_type,
1356 x_return_status => x_return_status);
1357
1358 -- check if activity started successfully
1359 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1360 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1361 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1362 raise OKE_API.G_EXCEPTION_ERROR;
1363 End If;
1364
1365 OPEN l_csr_id;
1366 FETCH l_csr_id INTO l_temp;
1367 IF l_csr_id%NOTFOUND THEN
1368 OKE_API.SET_MESSAGE(
1369 p_app_name =>g_app_name,
1370 p_msg_name =>oke_api.g_invalid_value,
1371 p_token1 =>oke_api.g_col_name_token,
1372 p_token1_value =>'pool_party_id');
1373 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'must provide valid pool_party_id');
1374 RAISE OKE_API.G_EXCEPTION_ERROR;
1375 END IF;
1376 CLOSE l_csr_id;
1377
1378
1379 l_party_rec.pool_party_id := p_pool_party_id;
1380
1381
1382 OPEN l_csr;
1383 LOOP
1384 FETCH l_csr INTO l_current_id;
1385 EXIT WHEN l_csr%NOTFOUND;
1386 oke_funding_pub.delete_funding(
1387 p_api_version => p_api_version,
1388 p_init_msg_list => p_init_msg_list,
1389 p_commit => OKE_API.G_FALSE,
1390 x_return_status => x_return_status,
1391 x_msg_count => x_msg_count,
1392 x_msg_data => x_msg_data,
1393 p_funding_source_id => l_current_id);
1394
1395 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1396 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1397 close l_csr;
1398 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1399 raise OKE_API.G_EXCEPTION_ERROR;
1400 close l_csr;
1401 End If;
1402
1403 END LOOP;
1404 CLOSE l_csr;
1405
1406
1407 -- call complex api
1408
1409 OKE_PARTY_PVT.delete_row(
1410 p_api_version => p_api_version,
1411 p_init_msg_list => p_init_msg_list,
1412 x_return_status => x_return_status,
1413 x_msg_count => x_msg_count,
1414 x_msg_data => x_msg_data,
1415 p_party_rec => l_party_rec);
1416
1417
1418 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1419 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1420 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1421 raise OKE_API.G_EXCEPTION_ERROR;
1422 End If;
1423
1424 --FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'end call oke_pools_parties_pub.delete_party');
1425 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1426 x_msg_data => x_msg_data);
1427 EXCEPTION
1428 when OKE_API.G_EXCEPTION_ERROR then
1429 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1430 p_api_name => l_api_name,
1431 p_pkg_name => g_pkg_name,
1432 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1433 x_msg_count => x_msg_count,
1434 x_msg_data => x_msg_data,
1435 p_api_type => g_api_type);
1436
1437 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1438 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1439 p_api_name => l_api_name,
1440 p_pkg_name => g_pkg_name,
1441 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1442 x_msg_count => x_msg_count,
1443 x_msg_data => x_msg_data,
1444 p_api_type => g_api_type);
1445
1446 when OTHERS then
1447 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1448 p_api_name => l_api_name,
1449 p_pkg_name => g_pkg_name,
1450 p_exc_name => 'OTHERS',
1451 x_msg_count => x_msg_count,
1452 x_msg_data => x_msg_data,
1453 p_api_type => g_api_type);
1454
1455 END delete_party;
1456
1457
1458
1459 PROCEDURE delete_party(
1460 p_api_version IN NUMBER,
1461 p_init_msg_list IN VARCHAR2 ,
1462 x_return_status OUT NOCOPY VARCHAR2,
1463 x_msg_count OUT NOCOPY NUMBER,
1464 x_msg_data OUT NOCOPY VARCHAR2,
1465 p_party_tbl IN oke_party_pvt.party_tbl_type) IS
1466
1467 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY';
1468 l_api_version CONSTANT NUMBER := 1.0;
1469 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1470 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1471 i NUMBER;
1472 BEGIN
1473 -- call START_ACTIVITY to create savepoint, check compatibility
1474 -- and initialize message list
1475 l_return_status := OKE_API.START_ACTIVITY(
1476 p_api_name => l_api_name,
1477 p_pkg_name => g_pkg_name,
1478 p_init_msg_list => p_init_msg_list,
1479 l_api_version => l_api_version,
1480 p_api_version => p_api_version,
1481 p_api_type => g_api_type,
1482 x_return_status => x_return_status);
1483
1484 -- check if activity started successfully
1485 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1486 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1487 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1488 raise OKE_API.G_EXCEPTION_ERROR;
1489 End If;
1490
1491
1492 If (p_party_tbl.COUNT>0) Then
1493 i:=p_party_tbl.FIRST;
1494 LOOP
1495 Delete_Party(
1496 p_api_version => p_api_version,
1497 p_init_msg_list => p_init_msg_list,
1498 x_return_status => x_return_status,
1499 x_msg_count => x_msg_count,
1500 x_msg_data => x_msg_data,
1501 p_pool_party_id => p_party_tbl(i).pool_party_id);
1502
1503 -- store the highest degree of error
1504 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1505 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1506 l_overall_status := x_return_status;
1507 End If;
1508 End If;
1509
1510
1511 EXIT WHEN (i = p_party_tbl.LAST);
1512 i := p_party_tbl.NEXT(i);
1513 END LOOP;
1514 x_return_status := l_overall_status;
1515 End If;
1516
1517
1518 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1519 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1520 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1521 raise OKE_API.G_EXCEPTION_ERROR;
1522 End If;
1523
1524 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1525 x_msg_data => x_msg_data);
1526 EXCEPTION
1527 when OKE_API.G_EXCEPTION_ERROR then
1528 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1529 p_api_name => l_api_name,
1530 p_pkg_name => g_pkg_name,
1531 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data,
1534 p_api_type => g_api_type);
1535
1536 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1537 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1538 p_api_name => l_api_name,
1539 p_pkg_name => g_pkg_name,
1540 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543 p_api_type => g_api_type);
1544
1545 when OTHERS then
1546 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1547 p_api_name => l_api_name,
1548 p_pkg_name => g_pkg_name,
1549 p_exc_name => 'OTHERS',
1550 x_msg_count => x_msg_count,
1551 x_msg_data => x_msg_data,
1552 p_api_type => g_api_type);
1553
1554 END delete_party;
1555
1556
1557 PROCEDURE lock_party(
1558 p_api_version IN NUMBER,
1559 p_init_msg_list IN VARCHAR2 ,
1560 x_return_status OUT NOCOPY VARCHAR2,
1561 x_msg_count OUT NOCOPY NUMBER,
1562 x_msg_data OUT NOCOPY VARCHAR2,
1563 p_party_rec IN OKE_PARTY_PVT.party_rec_type) IS
1564
1565
1566 l_del_rec oke_deliverable_pvt.del_rec_type;
1567 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY';
1568 l_api_version CONSTANT NUMBER := 1.0;
1569 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1570
1571 BEGIN
1572 l_return_status := OKE_API.START_ACTIVITY(
1573 p_api_name => l_api_name,
1574 p_pkg_name => g_pkg_name,
1575 p_init_msg_list => p_init_msg_list,
1576 l_api_version => l_api_version,
1577 p_api_version => p_api_version,
1578 p_api_type => g_api_type,
1579 x_return_status => x_return_status);
1580
1581 -- check if activity started successfully
1582 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1583 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1584 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1585 raise OKE_API.G_EXCEPTION_ERROR;
1586 End If;
1587
1588 OKE_PARTY_PVT.Lock_Row(
1589 p_api_version => p_api_version,
1590 p_init_msg_list => p_init_msg_list,
1591 x_return_status => x_return_status,
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 p_party_rec => p_party_rec);
1595
1596 -- check return status
1597 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1598 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1599 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1600 raise OKE_API.G_EXCEPTION_ERROR;
1601 End If;
1602
1603 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1604 x_msg_data => x_msg_data);
1605 EXCEPTION
1606 when OKE_API.G_EXCEPTION_ERROR then
1607 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1608 p_api_name => l_api_name,
1609 p_pkg_name => g_pkg_name,
1610 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1611 x_msg_count => x_msg_count,
1612 x_msg_data => x_msg_data,
1613 p_api_type => g_api_type);
1614
1615 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1616 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1617 p_api_name => l_api_name,
1618 p_pkg_name => g_pkg_name,
1619 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1620 x_msg_count => x_msg_count,
1621 x_msg_data => x_msg_data,
1622 p_api_type => g_api_type);
1623
1624 when OTHERS then
1625 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1626 p_api_name => l_api_name,
1627 p_pkg_name => g_pkg_name,
1628 p_exc_name => 'OTHERS',
1629 x_msg_count => x_msg_count,
1630 x_msg_data => x_msg_data,
1631 p_api_type => g_api_type);
1632
1633 END lock_party;
1634
1635 PROCEDURE lock_party(
1636 p_api_version IN NUMBER,
1637 p_init_msg_list IN VARCHAR2 ,
1638 x_return_status OUT NOCOPY VARCHAR2,
1639 x_msg_count OUT NOCOPY NUMBER,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 p_party_tbl IN oke_party_pvt.party_tbl_type) IS
1642
1643 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_PARTY';
1644 l_api_version CONSTANT NUMBER := 1.0;
1645 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1646 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1647 i NUMBER;
1648 BEGIN
1649 -- call START_ACTIVITY to create savepoint, check compatibility
1650 -- and initialize message list
1651 l_return_status := OKE_API.START_ACTIVITY(
1652 p_api_name => l_api_name,
1653 p_pkg_name => g_pkg_name,
1654 p_init_msg_list => p_init_msg_list,
1655 l_api_version => l_api_version,
1656 p_api_version => p_api_version,
1657 p_api_type => g_api_type,
1658 x_return_status => x_return_status);
1659
1660 -- check if activity started successfully
1661 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1662 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1663 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1664 raise OKE_API.G_EXCEPTION_ERROR;
1665 End If;
1666
1667 If (p_party_tbl.COUNT > 0) Then
1668 i := p_party_tbl.FIRST;
1669 LOOP
1670 -- call procedure in complex API
1671 OKE_PARTY_PVT.lock_row(
1672 p_api_version => p_api_version,
1673 p_init_msg_list => p_init_msg_list,
1674 x_return_status => x_return_status,
1675 x_msg_count => x_msg_count,
1676 x_msg_data => x_msg_data,
1677 p_party_rec => p_party_tbl(i));
1678
1679 -- store the highest degree of error
1680 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1681 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1682 l_overall_status := x_return_status;
1683 End If;
1684 End If;
1685 EXIT WHEN (i = p_party_tbl.LAST);
1686 i := p_party_tbl.NEXT(i);
1687 END LOOP;
1688 -- return overall status
1689 x_return_status := l_overall_status;
1690 End If;
1691
1692 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1693 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1694 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1695 raise OKE_API.G_EXCEPTION_ERROR;
1696 End If;
1697
1698 -- end activity
1699 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data);
1701 EXCEPTION
1702 when OKE_API.G_EXCEPTION_ERROR then
1703 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1704 p_api_name => l_api_name,
1705 p_pkg_name => g_pkg_name,
1706 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1707 x_msg_count => x_msg_count,
1708 x_msg_data => x_msg_data,
1709 p_api_type => g_api_type);
1710
1711 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1712 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1713 p_api_name => l_api_name,
1714 p_pkg_name => g_pkg_name,
1715 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data,
1718 p_api_type => g_api_type);
1719
1720 when OTHERS then
1721 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1722 p_api_name => l_api_name,
1723 p_pkg_name => g_pkg_name,
1724 p_exc_name => 'OTHERS',
1725 x_msg_count => x_msg_count,
1726 x_msg_data => x_msg_data,
1727 p_api_type => g_api_type);
1728
1729 END lock_party;
1730
1731
1732
1733 END OKE_POOLS_PARTIES_PUB;
1734