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