[Home] [Help]
PACKAGE BODY: APPS.OKE_PARTY_PVT
Source
1 PACKAGE BODY OKE_PARTY_PVT AS
2 /* $Header: OKEVFPPB.pls 115.12 2003/10/07 00:48:44 alaw ship $ */
3
4
5 PROCEDURE validate_currency_code(x_return_status OUT NOCOPY VARCHAR2,
6 p_party_rec IN party_rec_type)IS
7
8 l_dummy_val VARCHAR2(1):='?';
9 CURSOR l_csr IS
10 SELECT 'x'
11 FROM FND_CURRENCIES
12 WHERE CURRENCY_CODE = p_party_rec.CURRENCY_CODE;
13
14 BEGIN
15
16 x_return_status := OKE_API.G_RET_STS_SUCCESS;
17
18
19 -- check required value - not null
20
21 IF ( p_party_rec.currency_code = OKE_API.G_MISS_CHAR
22 OR p_party_rec.currency_code IS NULL) THEN
23 OKE_API.SET_MESSAGE(
24 p_app_name =>g_app_name,
25 p_msg_name =>g_required_value,
26 p_token1 =>g_col_name_token,
27 p_token1_value =>'CURRENCY_CODE');
28
29 x_return_status := OKE_API.G_RET_STS_ERROR;
30 raise G_EXCEPTION_HALT_VALIDATION;
31 END IF;
32
33
34 OPEN l_csr;
35 FETCH l_csr INTO l_dummy_val;
36 CLOSE l_csr;
37
38 IF (l_dummy_val = '?') THEN
39 OKE_API.SET_MESSAGE(
40 p_app_name =>g_app_name,
41 p_msg_name =>g_no_parent_record,
42 p_token1 =>g_col_name_token,
43 p_token1_value =>'CURRENCY_CODE',
44 p_token2 =>g_child_table_token,
45 p_token2_value =>G_VIEW,
46 p_token3 =>g_parent_table_token,
47 p_token3_value =>'FND_CURRENCIES');
48
49 x_return_status := OKE_API.G_RET_STS_ERROR;
50 END IF;
51
52 EXCEPTION
53 WHEN G_EXCEPTION_HALT_VALIDATION THEN
54 NULL;
55 WHEN OTHERS THEN
56 -- store SQL error message on message stack
57 OKE_API.SET_MESSAGE(
58 p_app_name =>g_app_name,
59 p_msg_name =>G_UNEXPECTED_ERROR,
60 p_token1 =>G_SQLCODE_TOKEN,
61 p_token1_value =>SQLCODE,
62 p_token2 =>G_SQLERRM_TOKEN,
63 p_token2_value =>SQLERRM);
64 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
65
66 IF l_csr%ISOPEN THEN
67 CLOSE l_csr;
68 END IF;
69
70 END validate_currency_code;
71
72
73 PROCEDURE validate_funding_pool_id (x_return_status OUT NOCOPY VARCHAR2,
74 p_party_rec IN party_rec_type)IS
75
76 l_dummy_val VARCHAR2(1):='?';
77 CURSOR l_csr IS
78 SELECT 'x'
79 FROM OKE_FUNDING_POOLS
80 WHERE FUNDING_POOL_ID = p_party_rec.funding_pool_id;
81
82 BEGIN
83
84
85 x_return_status := OKE_API.G_RET_STS_SUCCESS;
86
87
88 -- check required value - not null
89
90 IF ( p_party_rec.funding_pool_id = OKE_API.G_MISS_NUM
91 OR p_party_rec.funding_pool_id IS NULL) THEN
92 OKE_API.SET_MESSAGE(
93 p_app_name =>g_app_name,
94 p_msg_name =>g_required_value,
95 p_token1 =>g_col_name_token,
96 p_token1_value =>'FUNDING_POOL_ID');
97
98 x_return_status := OKE_API.G_RET_STS_ERROR;
99 raise G_EXCEPTION_HALT_VALIDATION;
100 END IF;
101
102
103 OPEN l_csr;
104 FETCH l_csr INTO l_dummy_val;
105 CLOSE l_csr;
106
107 IF (l_dummy_val = '?') THEN
108 OKE_API.SET_MESSAGE(
109 p_app_name =>g_app_name,
110 p_msg_name =>g_no_parent_record,
111 p_token1 =>g_col_name_token,
112 p_token1_value =>'FUNDING_POOL_ID',
113 p_token2 =>g_child_table_token,
114 p_token2_value =>G_VIEW,
115 p_token3 =>g_parent_table_token,
116 p_token3_value =>'OKE_FUNDING_POOLS');
117
118 x_return_status := OKE_API.G_RET_STS_ERROR;
119 END IF;
120
121 EXCEPTION
122 WHEN G_EXCEPTION_HALT_VALIDATION THEN
123 NULL;
124 WHEN OTHERS THEN
125 -- store SQL error message on message stack
126 OKE_API.SET_MESSAGE(
127 p_app_name =>g_app_name,
128 p_msg_name =>G_UNEXPECTED_ERROR,
129 p_token1 =>G_SQLCODE_TOKEN,
130 p_token1_value =>SQLCODE,
131 p_token2 =>G_SQLERRM_TOKEN,
132 p_token2_value =>SQLERRM);
133 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
134
135 IF l_csr%ISOPEN THEN
136 CLOSE l_csr;
137 END IF;
138
139 END validate_funding_pool_id;
140
141
142
143 PROCEDURE validate_party_id(x_return_status OUT NOCOPY VARCHAR2,
144 p_party_rec IN party_rec_type)IS
145
146 l_dummy_val VARCHAR2(1):='?';
147 CURSOR l_csr IS
148 SELECT 'x'
149 FROM HZ_PARTIES
150 WHERE PARTY_ID = p_party_rec.PARTY_ID
151 AND NVL(STATUS, 'A') = 'A';
152
153 BEGIN
154
155 x_return_status := OKE_API.G_RET_STS_SUCCESS;
156
157
158 -- check required value - not null
159
160 IF ( p_party_rec.party_id = OKE_API.G_MISS_NUM
161 OR p_party_rec.party_id IS NULL) THEN
162 OKE_API.SET_MESSAGE(
163 p_app_name =>g_app_name,
164 p_msg_name =>g_required_value,
165 p_token1 =>g_col_name_token,
166 p_token1_value =>'PARTY_ID');
167
168 x_return_status := OKE_API.G_RET_STS_ERROR;
169 raise G_EXCEPTION_HALT_VALIDATION;
170 END IF;
171
172
173 OPEN l_csr;
174 FETCH l_csr INTO l_dummy_val;
175 CLOSE l_csr;
176
177 IF (l_dummy_val = '?') THEN
178 OKE_API.SET_MESSAGE(
179 p_app_name =>g_app_name,
180 p_msg_name =>g_no_parent_record,
181 p_token1 =>g_col_name_token,
182 p_token1_value =>'PARTY_ID',
183 p_token2 =>g_child_table_token,
184 p_token2_value =>G_VIEW,
185 p_token3 =>g_parent_table_token,
186 p_token3_value =>'HZ_PARTIES');
187
188 x_return_status := OKE_API.G_RET_STS_ERROR;
189 END IF;
190
191 EXCEPTION
192 WHEN G_EXCEPTION_HALT_VALIDATION THEN
193 NULL;
194 WHEN OTHERS THEN
195 -- store SQL error message on message stack
196 OKE_API.SET_MESSAGE(
197 p_app_name =>g_app_name,
198 p_msg_name =>G_UNEXPECTED_ERROR,
199 p_token1 =>G_SQLCODE_TOKEN,
200 p_token1_value =>SQLCODE,
201 p_token2 =>G_SQLERRM_TOKEN,
202 p_token2_value =>SQLERRM);
203 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
204
205 IF l_csr%ISOPEN THEN
206 CLOSE l_csr;
207 END IF;
208
209 END validate_party_id;
210
211
212 PROCEDURE validate_initial_amount (x_return_status OUT NOCOPY VARCHAR2,
213 p_party_rec IN party_rec_type)IS
214 BEGIN
215
216 x_return_status := OKE_API.G_RET_STS_SUCCESS;
217
218
219 -- check required value - not null
220
221 IF ( p_party_rec.initial_amount = OKE_API.G_MISS_NUM
222 OR p_party_rec.initial_amount IS NULL) THEN
223 OKE_API.SET_MESSAGE(
224 p_app_name =>g_app_name,
225 p_msg_name =>g_required_value,
226 p_token1 =>g_col_name_token,
227 p_token1_value =>'INITIAL_AMOUNT');
228
229 x_return_status := OKE_API.G_RET_STS_ERROR;
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 -- store SQL error message on message stack
235 OKE_API.SET_MESSAGE(
236 p_app_name =>g_app_name,
237 p_msg_name =>G_UNEXPECTED_ERROR,
238 p_token1 =>G_SQLCODE_TOKEN,
239 p_token1_value =>SQLCODE,
240 p_token2 =>G_SQLERRM_TOKEN,
241 p_token2_value =>SQLERRM);
242 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
243
244 END validate_initial_amount;
245
246 PROCEDURE validate_amount (x_return_status OUT NOCOPY VARCHAR2,
247 p_party_rec IN party_rec_type)IS
248
249 l_available NUMBER;
250 l_result VARCHAR2(1);
251 BEGIN
252
253 x_return_status := OKE_API.G_RET_STS_SUCCESS;
254
255
256 -- check required value - not null
257
258 IF ( p_party_rec.amount = OKE_API.G_MISS_NUM
259 OR p_party_rec.amount IS NULL) THEN
260 OKE_API.SET_MESSAGE(
261 p_app_name =>g_app_name,
262 p_msg_name =>g_required_value,
263 p_token1 =>g_col_name_token,
264 p_token1_value =>'AMOUNT');
265
266 x_return_status := OKE_API.G_RET_STS_ERROR;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 -- store SQL error message on message stack
272 OKE_API.SET_MESSAGE(
273 p_app_name =>g_app_name,
274 p_msg_name =>G_UNEXPECTED_ERROR,
275 p_token1 =>G_SQLCODE_TOKEN,
276 p_token1_value =>SQLCODE,
277 p_token2 =>G_SQLERRM_TOKEN,
278 p_token2_value =>SQLERRM);
279 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
280
281 END validate_amount;
282
283 PROCEDURE validate_available_amount (x_return_status OUT NOCOPY VARCHAR2,
284 p_party_rec IN party_rec_type)IS
285
286 BEGIN
287
288 x_return_status := OKE_API.G_RET_STS_SUCCESS;
289 -- check required value - not null
290
291 IF ( p_party_rec.available_amount = OKE_API.G_MISS_NUM
292 OR p_party_rec.available_amount IS NULL) THEN
293 OKE_API.SET_MESSAGE(
294 p_app_name =>g_app_name,
295 p_msg_name =>g_required_value,
296 p_token1 =>g_col_name_token,
297 p_token1_value =>'AVAILABLE_AMOUNT');
298
299 x_return_status := OKE_API.G_RET_STS_ERROR;
300 END IF;
301
302 EXCEPTION
303
304 WHEN OTHERS THEN
305 -- store SQL error message on message stack
306 OKE_API.SET_MESSAGE(
307 p_app_name =>g_app_name,
308 p_msg_name =>G_UNEXPECTED_ERROR,
309 p_token1 =>G_SQLCODE_TOKEN,
310 p_token1_value =>SQLCODE,
311 p_token2 =>G_SQLERRM_TOKEN,
312 p_token2_value =>SQLERRM);
313 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
314
315 END validate_available_amount;
316
317
318
319
320 PROCEDURE validate_start_date_active (x_return_status OUT NOCOPY VARCHAR2,
321 p_party_rec IN party_rec_type)IS
322
323 l_result VARCHAR2(1);
324
325
326 BEGIN
327
328
329 x_return_status := OKE_API.G_RET_STS_SUCCESS;
330
331 IF p_party_rec.start_date_active <> OKE_API.G_MISS_DATE
332 AND p_party_rec.start_date_active IS NOT NULL THEN
333
334 IF p_party_rec.POOL_PARTY_ID <> OKE_API.G_MISS_NUM
335 AND p_party_rec.POOL_PARTY_ID IS NOT NULL THEN
336 OKE_FUNDING_UTIL_PKG.validate_pool_party_date(
337 x_start_end => 'START',
338 x_pool_party_id => p_party_rec.POOL_PARTY_ID,
339 x_date => p_party_rec.start_date_active,
340 x_return_status => l_result);
341
342 IF(l_result='N') THEN
343 OKE_API.SET_MESSAGE(
344 p_app_name =>g_app_name,
345 p_msg_name =>g_invalid_value,
346 p_token1 =>g_col_name_token,
347 p_token1_value =>'start_date_active');
348
349 x_return_status := OKE_API.G_RET_STS_ERROR;
350 END IF;
351 END IF;
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 -- store SQL error message on message stack
357
358 OKE_API.SET_MESSAGE(
359 p_app_name =>g_app_name,
360 p_msg_name =>G_UNEXPECTED_ERROR,
361 p_token1 =>G_SQLCODE_TOKEN,
362 p_token1_value =>SQLCODE,
363 p_token2 =>G_SQLERRM_TOKEN,
364 p_token2_value =>SQLERRM);
365 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
366
367 END validate_start_date_active;
368
369
370
371 PROCEDURE validate_end_date_active (x_return_status OUT NOCOPY VARCHAR2,
372 p_party_rec IN party_rec_type)IS
373
374 l_result VARCHAR2(1);
375
376 BEGIN
377
378 x_return_status := OKE_API.G_RET_STS_SUCCESS;
379
380
381 IF p_party_rec.end_date_active <> OKE_API.G_MISS_DATE
382 AND p_party_rec.end_date_active IS NOT NULL THEN
383
384 IF p_party_rec.POOL_PARTY_ID <> OKE_API.G_MISS_NUM
385 AND p_party_rec.POOL_PARTY_ID IS NOT NULL THEN
386
387 OKE_FUNDING_UTIL_PKG.validate_pool_party_date(
388 x_start_end => 'END',
389 x_pool_party_id => p_party_rec.POOL_PARTY_ID,
390 x_date => p_party_rec.end_date_active,
391 x_return_status => l_result);
392
393 IF(l_result='N') THEN
394 OKE_API.SET_MESSAGE(
395 p_app_name =>g_app_name,
396 p_msg_name =>g_invalid_value,
397 p_token1 =>g_col_name_token,
398 p_token1_value =>'end_date_active');
399
400 x_return_status := OKE_API.G_RET_STS_ERROR;
401 END IF;
402 END IF;
403 END IF;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 -- store SQL error message on message stack
408 OKE_API.SET_MESSAGE(
409 p_app_name =>g_app_name,
410 p_msg_name =>G_UNEXPECTED_ERROR,
411 p_token1 =>G_SQLCODE_TOKEN,
412 p_token1_value =>SQLCODE,
413 p_token2 =>G_SQLERRM_TOKEN,
414 p_token2_value =>SQLERRM);
415 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
416
417 END validate_end_date_active;
418
419
420 -- validate record
421
422 FUNCTION validate_record (
423 p_party_rec IN party_rec_type
424 ) RETURN VARCHAR2 IS
425
426 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
427 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
428 l_result VARCHAR2(1);
429 l_parent_currency OKE_POOL_PARTIES.CURRENCY_CODE%TYPE;
430
431 CURSOR l_conversion IS
432 SELECT CURRENCY_CODE
433 FROM OKE_FUNDING_POOLS
434 WHERE FUNDING_POOL_ID = p_party_rec.FUNDING_POOL_ID;
435
436 BEGIN
437
438
439
440 -- if start and end dates both exist then validate start before end. otherwise skip validation
441
442 IF ((p_party_rec.START_DATE_ACTIVE IS NOT NULL)AND(p_party_rec.START_DATE_ACTIVE <> OKE_API.G_MISS_DATE))
443 AND
444 ((p_party_rec.END_DATE_ACTIVE IS NOT NULL)AND(p_party_rec.END_DATE_ACTIVE <> OKE_API.G_MISS_DATE)) THEN
445 OKE_FUNDING_UTIL_PKG.validate_start_end_date(p_party_rec.START_DATE_ACTIVE,p_party_rec.END_DATE_ACTIVE,l_result);
446 -- if failure then...
447 IF(l_result = 'N') THEN
448
449 OKE_API.SET_MESSAGE(
450 p_app_name =>g_app_name,
451 p_msg_name =>g_invalid_value,
452 p_token1 =>g_col_name_token,
453 p_token1_value =>'end_date_active');
454 OKE_API.SET_MESSAGE(
455 p_app_name =>g_app_name,
456 p_msg_name =>g_invalid_value,
457 p_token1 =>g_col_name_token,
458 p_token1_value =>'start_date_active');
459
460
461 x_return_status := OKE_API.G_RET_STS_ERROR;
462 RETURN(x_return_status);
463
464
465 END IF;
466 END IF;
467
468
469 -- check if currency the same as in funding pool.
470 -- if same, then must conversions must be null
471 -- otherwise must be filled.
472
473 -- call validate funding_pool_id first to make sure parent record exist.
474
475 validate_funding_pool_id (x_return_status => l_return_status,
476 p_party_rec => p_party_rec);
477 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
478 x_return_status := l_return_status;
479 RETURN(x_return_status);
480 END IF;
481
482 -- parent record exists, carry on to find currency_code from parent.
483 OPEN l_conversion;
484 FETCH l_conversion INTO l_parent_currency;
488 validate_currency_code (x_return_status => l_return_status,
485 CLOSE l_conversion;
486
487 -- call validate_currency_code first to make sure compulsory field is filled.
489 p_party_rec => p_party_rec);
490 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
491 x_return_status := l_return_status;
492 RETURN(x_return_status);
493 END IF;
494
495 -- do the checks now.
496
497 IF p_party_rec.CURRENCY_CODE = l_parent_currency THEN
498 IF((p_party_rec.CONVERSION_TYPE IS NOT NULL)AND(p_party_rec.CONVERSION_TYPE<>OKE_API.G_MISS_CHAR))
499 OR((p_party_rec.CONVERSION_DATE IS NOT NULL)AND(p_party_rec.CONVERSION_DATE<>OKE_API.G_MISS_DATE))
500 THEN
501 OKE_API.SET_MESSAGE(
502 p_app_name =>g_app_name,
503 p_msg_name =>g_invalid_value,
504 p_token1 =>g_col_name_token,
505 p_token1_value =>'conversion_type');
506 OKE_API.SET_MESSAGE(
507 p_app_name =>g_app_name,
508 p_msg_name =>g_invalid_value,
509 p_token1 =>g_col_name_token,
510 p_token1_value =>'conversion_date');
511
512 x_return_status := OKE_API.G_RET_STS_ERROR;
513 --oke_debug.debug('validate record failed on currency compare');
514 RETURN(x_return_status);
515 END IF;
516 ELSE -- check that they are filled.
517
518
519 IF((p_party_rec.CONVERSION_TYPE IS NULL)OR(p_party_rec.CONVERSION_TYPE=OKE_API.G_MISS_CHAR))
520 OR((p_party_rec.CONVERSION_DATE IS NULL)OR(p_party_rec.CONVERSION_DATE=OKE_API.G_MISS_DATE))
521 THEN
522 OKE_API.SET_MESSAGE(
523 p_app_name =>g_app_name,
524 p_msg_name =>g_invalid_value,
525 p_token1 =>g_col_name_token,
526 p_token1_value =>'conversion_type');
527 OKE_API.SET_MESSAGE(
528 p_app_name =>g_app_name,
529 p_msg_name =>g_invalid_value,
530 p_token1 =>g_col_name_token,
531 p_token1_value =>'conversion_date');
532
533 x_return_status := OKE_API.G_RET_STS_ERROR;
534 --oke_debug.debug('validate record fail on currency compare');
535 RETURN(x_return_status);
536 END IF;
537
538 END IF;
539
540 RETURN(x_return_status);
541
542 END validate_record;
543
544 -- validate individual attributes
545
546 FUNCTION validate_attributes(
547 p_party_rec IN party_rec_type
548 ) RETURN VARCHAR2 IS
549
550 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
551 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
552
553 BEGIN
554
555 validate_funding_pool_id (x_return_status => l_return_status,
556 p_party_rec => p_party_rec);
557 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
558 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
559 x_return_status := l_return_status;
560 END IF;
561 END IF;
562 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
563 --oke_debug.debug('validation of funding_pool_id: Success');
564 --ELSE
565 --oke_debug.debug('validation of funding_pool_id: Failure');
566 --END IF;
567
568
569 validate_party_id (x_return_status => l_return_status,
570 p_party_rec => p_party_rec);
571 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
572 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
573 x_return_status := l_return_status;
574 END IF;
575 END IF;
576 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
577 --oke_debug.debug('validation of party_id: Success');
578 --ELSE
579 --oke_debug.debug('validation of party_id: Failure');
580 --END IF;
581
582
583 validate_currency_code (x_return_status => l_return_status,
584 p_party_rec => p_party_rec);
585 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
586 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
587 x_return_status := l_return_status;
588 END IF;
589 END IF;
590 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
591 --oke_debug.debug('validation of currency_code: Success');
592 --ELSE
593 --oke_debug.debug('validation of currency_code: Failure');
594 --END IF;
595
596
597 validate_initial_amount (x_return_status => l_return_status,
598 p_party_rec => p_party_rec);
599 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
600 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
601 x_return_status := l_return_status;
602 END IF;
603 END IF;
604 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
605 --oke_debug.debug('validation of initial_amount: Success');
606 --ELSE
607 --oke_debug.debug('validation of initial_amount: Failure');
608 --END IF;
609
610
611 validate_amount (x_return_status => l_return_status,
612 p_party_rec => p_party_rec);
613 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
614 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
615 x_return_status := l_return_status;
616 END IF;
617 END IF;
618 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
619 --oke_debug.debug('validation of amount: Success');
620 --ELSE
621 --oke_debug.debug('validation of amount: Failure');
622 --END IF;
623
624
625
629 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
626 validate_available_amount (x_return_status => l_return_status,
627 p_party_rec => p_party_rec);
628 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
630 x_return_status := l_return_status;
631 END IF;
632 END IF;
633 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
634 --oke_debug.debug('validation of available_amount: Success');
635 --ELSE
636 --oke_debug.debug('validation of available_amount: Failure');
637 --END IF;
638
639
640 validate_start_date_active (x_return_status => l_return_status,
641 p_party_rec => p_party_rec);
642 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
643 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
644 x_return_status := l_return_status;
645 END IF;
646 END IF;
647 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
648 --oke_debug.debug('validation of start_date_active: Success');
649 --ELSE
650 --oke_debug.debug('validation of start_date_active: Failure');
651 --END IF;
652
653
654 validate_end_date_active (x_return_status => l_return_status,
655 p_party_rec => p_party_rec);
656 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
657 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
658 x_return_status := l_return_status;
659 END IF;
660 END IF;
661 --IF l_return_status=OKE_API.G_RET_STS_SUCCESS THEN
662 --oke_debug.debug('validation of end_date_active: Success');
663 --ELSE
664 --oke_debug.debug('validation of end_date_active: Failure');
665 --END IF;
666
667
668 /* call individual validation procedure */
669 -- return status to caller
670 RETURN(x_return_status);
671
672 END Validate_Attributes;
673
674
675 -- called by insert_row to make unfilled attributes NULL
676
677 FUNCTION null_out_defaults(
678 p_party_rec IN party_rec_type ) RETURN party_rec_type IS
679
680 l_party_rec party_rec_type := p_party_rec;
681
682 BEGIN
683
684 IF l_party_rec.POOL_PARTY_ID = OKE_API.G_MISS_NUM THEN
685 l_party_rec.POOL_PARTY_ID := NULL;
686 END IF;
687
688 IF l_party_rec.FUNDING_POOL_ID = OKE_API.G_MISS_NUM THEN
689 l_party_rec.FUNDING_POOL_ID := NULL;
690 END IF;
691
692 IF l_party_rec.PARTY_ID = OKE_API.G_MISS_NUM THEN
693 l_party_rec.PARTY_ID := NULL;
694 END IF;
695
696 IF l_party_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
697 l_party_rec.CURRENCY_CODE := NULL;
698 END IF;
699
700 IF l_party_rec.CONVERSION_TYPE = OKE_API.G_MISS_CHAR THEN
701 l_party_rec.CONVERSION_TYPE := NULL;
702 END IF;
703
704 IF l_party_rec.CONVERSION_DATE = OKE_API.G_MISS_DATE THEN
705 l_party_rec.CONVERSION_DATE := NULL;
706 END IF;
707
708 IF l_party_rec.CONVERSION_RATE = OKE_API.G_MISS_NUM THEN
709 l_party_rec.CONVERSION_RATE := NULL;
710 END IF;
711
712 IF l_party_rec.INITIAL_AMOUNT = OKE_API.G_MISS_NUM THEN
713 l_party_rec.INITIAL_AMOUNT := NULL;
714 END IF;
715
716 IF l_party_rec.AMOUNT = OKE_API.G_MISS_NUM THEN
717 l_party_rec.AMOUNT := NULL;
718 END IF;
719
720 IF l_party_rec.AVAILABLE_AMOUNT = OKE_API.G_MISS_NUM THEN
721 l_party_rec.AVAILABLE_AMOUNT := NULL;
722 END IF;
723
724 IF l_party_rec.START_DATE_ACTIVE = OKE_API.G_MISS_DATE THEN
725 l_party_rec.START_DATE_ACTIVE := NULL;
726 END IF;
727
728 IF l_party_rec.END_DATE_ACTIVE = OKE_API.G_MISS_DATE THEN
729 l_party_rec.END_DATE_ACTIVE := NULL;
730 END IF;
731
732
733 IF l_party_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
734 l_party_rec.ATTRIBUTE_CATEGORY := NULL;
735 END IF;
736
737 IF l_party_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
738 l_party_rec.ATTRIBUTE1 := NULL;
739 END IF;
740
741 IF l_party_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
742 l_party_rec.ATTRIBUTE2 := NULL;
743 END IF;
744
745 IF l_party_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
746 l_party_rec.ATTRIBUTE3 := NULL;
747 END IF;
748
749 IF l_party_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
750 l_party_rec.ATTRIBUTE4 := NULL;
751 END IF;
752
753 IF l_party_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
754 l_party_rec.ATTRIBUTE5 := NULL;
755 END IF;
756
757 IF l_party_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
758 l_party_rec.ATTRIBUTE6 := NULL;
759 END IF;
760
761 IF l_party_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
762 l_party_rec.ATTRIBUTE7 := NULL;
763 END IF;
764
765 IF l_party_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
766 l_party_rec.ATTRIBUTE8 := NULL;
767 END IF;
768
769 IF l_party_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
770 l_party_rec.ATTRIBUTE9 := NULL;
771 END IF;
772
773 IF l_party_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
774 l_party_rec.ATTRIBUTE10 := NULL;
775 END IF;
776
777 IF l_party_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
781 IF l_party_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
778 l_party_rec.ATTRIBUTE11 := NULL;
779 END IF;
780
782 l_party_rec.ATTRIBUTE12 := NULL;
783 END IF;
784
785 IF l_party_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
786 l_party_rec.ATTRIBUTE13 := NULL;
787 END IF;
788
789 IF l_party_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
790 l_party_rec.ATTRIBUTE14 := NULL;
791 END IF;
792
793 IF l_party_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
794 l_party_rec.ATTRIBUTE15 := NULL;
795 END IF;
796
797 IF l_party_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
798 l_party_rec.CREATED_BY := NULL;
799 END IF;
800
801 IF l_party_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
802 l_party_rec.CREATION_DATE := NULL;
803 END IF;
804
805 IF l_party_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
806 l_party_rec.LAST_UPDATED_BY := NULL;
807 END IF;
808
809 IF l_party_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
810 l_party_rec.LAST_UPDATE_LOGIN := NULL;
811 END IF;
812
813 IF l_party_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
814 l_party_rec.LAST_UPDATE_DATE := NULL;
815 END IF;
816
817 RETURN(l_party_rec);
818
819 END null_out_defaults;
820
821
822 -- gets the record based on a key attribute
823
824 FUNCTION get_rec (
825 p_party_rec IN party_rec_type,
826 x_no_data_found OUT NOCOPY BOOLEAN
827 ) RETURN party_rec_type IS
828
829 CURSOR party_pk_csr ( p_party_id NUMBER) IS
830 SELECT
831
832 POOL_PARTY_ID ,
833 FUNDING_POOL_ID,
834 PARTY_ID ,
835 CURRENCY_CODE ,
836 CONVERSION_TYPE,
837 CONVERSION_DATE,
838 CONVERSION_RATE,
839 INITIAL_AMOUNT,
840 AMOUNT ,
841 AVAILABLE_AMOUNT,
842 START_DATE_ACTIVE,
843 END_DATE_ACTIVE,
844
845 CREATION_DATE,
846 CREATED_BY,
847 LAST_UPDATE_DATE,
848 LAST_UPDATED_BY,
849 LAST_UPDATE_LOGIN,
850 ATTRIBUTE_CATEGORY,
851 ATTRIBUTE1 ,
852 ATTRIBUTE2 ,
853 ATTRIBUTE3 ,
854 ATTRIBUTE4 ,
855 ATTRIBUTE5 ,
856 ATTRIBUTE6 ,
857 ATTRIBUTE7 ,
858 ATTRIBUTE8 ,
859 ATTRIBUTE9 ,
860 ATTRIBUTE10 ,
861 ATTRIBUTE11 ,
862 ATTRIBUTE12 ,
863 ATTRIBUTE13 ,
864 ATTRIBUTE14 ,
865 ATTRIBUTE15
866
867
868 FROM OKE_POOL_PARTIES a
869 WHERE (a.pool_party_id = p_party_id);
870
871 l_party_pk party_pk_csr%ROWTYPE;
872 l_party_rec party_rec_type;
873
874 BEGIN
875 x_no_data_found := TRUE;
876
877 -- get current database value
878
879 OPEN party_pk_csr(p_party_rec.POOL_PARTY_ID);
880 FETCH party_pk_csr INTO
881
882 l_party_rec.POOL_PARTY_ID ,
883 l_party_rec.FUNDING_POOL_ID,
884 l_party_rec.PARTY_ID ,
885 l_party_rec.CURRENCY_CODE ,
886 l_party_rec.CONVERSION_TYPE,
887 l_party_rec.CONVERSION_DATE,
888 l_party_rec.CONVERSION_RATE,
889 l_party_rec.INITIAL_AMOUNT,
890 l_party_rec.AMOUNT ,
891 l_party_rec.AVAILABLE_AMOUNT,
892 l_party_rec.START_DATE_ACTIVE,
893 l_party_rec.END_DATE_ACTIVE,
894
895 l_party_rec.CREATION_DATE ,
896 l_party_rec.CREATED_BY ,
897 l_party_rec.LAST_UPDATE_DATE ,
898 l_party_rec.LAST_UPDATED_BY ,
899 l_party_rec.LAST_UPDATE_LOGIN ,
900 l_party_rec.ATTRIBUTE_CATEGORY ,
901 l_party_rec.ATTRIBUTE1 ,
902 l_party_rec.ATTRIBUTE2 ,
903 l_party_rec.ATTRIBUTE3 ,
904 l_party_rec.ATTRIBUTE4 ,
905 l_party_rec.ATTRIBUTE5 ,
906 l_party_rec.ATTRIBUTE6 ,
907 l_party_rec.ATTRIBUTE7 ,
908 l_party_rec.ATTRIBUTE8 ,
909 l_party_rec.ATTRIBUTE9 ,
910 l_party_rec.ATTRIBUTE10 ,
911 l_party_rec.ATTRIBUTE11 ,
912 l_party_rec.ATTRIBUTE12 ,
913 l_party_rec.ATTRIBUTE13 ,
914 l_party_rec.ATTRIBUTE14 ,
915 l_party_rec.ATTRIBUTE15 ;
916
917 x_no_data_found := party_pk_csr%NOTFOUND;
918 CLOSE party_pk_csr;
919 IF(x_no_data_found) THEN
920 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
921 END IF;
922 RETURN(l_party_rec);
923
924 END get_rec;
925
926
927
928 -- row level insert
929 -- will create using nextVal from sequence OKE_POOL_PARTIES_s
930
931 PROCEDURE insert_row(
932 p_api_version IN NUMBER,
933 p_init_msg_list IN VARCHAR2 ,
934 x_return_status OUT NOCOPY VARCHAR2,
935 x_msg_count OUT NOCOPY NUMBER,
936 x_msg_data OUT NOCOPY VARCHAR2,
937 p_party_rec IN party_rec_type,
938 x_party_rec OUT NOCOPY party_rec_type) IS
939
940 l_api_version CONSTANT NUMBER := 1;
941 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
942 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
946 l_seq NUMBER;
943 l_party_rec party_rec_type;
944 l_def_party_rec party_rec_type;
945 lx_party_rec party_rec_type;
947 l_row_id RowID;
948
949 -- FUNCTION fill_who_columns --
950 -------------------------------
951 FUNCTION fill_who_columns (
952 p_party_rec IN party_rec_type
953 ) RETURN party_rec_type IS
954
955 l_party_rec party_rec_type := p_party_rec;
956
957 BEGIN
958
959 l_party_rec.CREATION_DATE := SYSDATE;
960 l_party_rec.CREATED_BY := FND_GLOBAL.USER_ID;
961 l_party_rec.LAST_UPDATE_DATE := SYSDATE;
962 l_party_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
963 l_party_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
964 RETURN(l_party_rec);
965
966 END fill_who_columns;
967
968
969 -- nothing much here. flags to UPPERCASE
970
971 FUNCTION Set_Attributes (
972 p_party_rec IN party_rec_type,
973 x_party_rec OUT NOCOPY party_rec_type
974 ) RETURN VARCHAR2 IS
975 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
976
977 l_to_currency VARCHAR2(15);
978 l_rate NUMBER;
979 BEGIN
980 x_party_rec := p_party_rec;
981 x_party_rec.available_amount:=x_party_rec.amount;
982 validate_funding_pool_id (x_return_status => l_return_status,
983 p_party_rec => p_party_rec);
984 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
985 RETURN (l_return_status);
986 END IF;
987
988 SELECT CURRENCY_CODE INTO l_to_currency
989 FROM OKE_FUNDING_POOLS
990 WHERE FUNDING_POOL_ID = p_party_rec.FUNDING_POOL_ID;
991
992 IF p_party_rec.CURRENCY_CODE <> l_to_currency THEN
993
994 OKE_FUNDING_UTIL_PKG.GET_CONVERSION_RATE
995 ( p_party_rec.CURRENCY_CODE,
996 l_to_currency,
997 p_party_rec.CONVERSION_TYPE,
998 p_party_rec.CONVERSION_DATE,
999 x_party_rec.CONVERSION_RATE,
1000 l_return_status);
1001
1002 END IF;
1003 RETURN(l_return_status);
1004
1005 END Set_Attributes;
1006
1007
1008 BEGIN -- insert
1009 --oke_debug.debug('start call oke_party_pvt.insert_row');
1010
1011 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1012 G_PKG_NAME,
1013 p_init_msg_list,
1014 l_api_version,
1015 p_api_version,
1016 '_PVT',
1017 x_return_status);
1018
1019
1020 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1021 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1022 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1023 RAISE OKE_API.G_EXCEPTION_ERROR;
1024 END IF;
1025
1026
1027 IF p_party_rec.pool_party_id <> OKE_API.G_MISS_NUM THEN
1028 OKE_API.SET_MESSAGE(
1029 p_app_name =>g_app_name,
1030 p_msg_name =>g_invalid_value,
1031 p_token1 =>g_col_name_token,
1032 p_token1_value =>'pool_party_id');
1033 --oke_debug.debug('must not provide pool_party_id');
1034 RAISE OKE_API.G_EXCEPTION_ERROR;
1035 END IF;
1036
1037
1038 IF p_party_rec.available_amount <> OKE_API.G_MISS_NUM THEN
1039
1040 OKE_API.SET_MESSAGE(
1041 p_app_name =>g_app_name,
1042 p_msg_name =>g_invalid_value,
1043 p_token1 =>g_col_name_token,
1044 p_token1_value =>'available_amount');
1045 --oke_debug.debug('must not provide available_amount');
1046 RAISE OKE_API.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049 IF p_party_rec.conversion_rate <> OKE_API.G_MISS_NUM THEN
1050
1051 OKE_API.SET_MESSAGE(
1052 p_app_name =>g_app_name,
1053 p_msg_name =>g_invalid_value,
1054 p_token1 =>g_col_name_token,
1055 p_token1_value =>'conversion_rate');
1056 --oke_debug.debug('must not provide conversion_rate');
1057 RAISE OKE_API.G_EXCEPTION_ERROR;
1058 END IF;
1059
1060
1061 --oke_debug.debug('start call null out defaults');
1062
1063 l_party_rec := null_out_defaults(p_party_rec);
1064 -- overide, since cannot insert id
1065 l_party_rec.pool_party_id := NULL;
1066
1067 --oke_debug.debug(' called null out defaults');
1068
1069 --- Setting item attributes
1070 l_return_status := Set_Attributes(
1071 l_party_rec, -- IN
1072 l_def_party_rec); -- OUT
1073
1074 --- If any errors happen abort API
1075 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1076 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1077 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1078 RAISE OKE_API.G_EXCEPTION_ERROR;
1079 END IF;
1080
1081 --oke_debug.debug('attributes set for insert');
1082
1083 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1084 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1085 END IF;
1086
1087 l_def_party_rec := fill_who_columns(l_def_party_rec);
1088
1092 l_return_status := Validate_Attributes(l_def_party_rec);
1089 --oke_debug.debug('who column filled for insert');
1090
1091 --- Validate all non-missing attributes (Item Level Validation)
1093
1094 --- If any errors happen abort API
1095 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097
1098 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1099 RAISE OKE_API.G_EXCEPTION_ERROR;
1100 END IF;
1101
1102 --oke_debug.debug('attributes validated for insert');
1103
1104 l_return_status := Validate_Record(l_def_party_rec);
1105
1106 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1107 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1108 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1109 RAISE OKE_API.G_EXCEPTION_ERROR;
1110 END IF;
1111 --oke_debug.debug('record validated');
1112
1113 SELECT OKE_POOL_PARTIES_S.nextval INTO l_seq FROM dual;
1114
1115 OKE_POOL_PARTIES_PKG.Insert_row
1116 ( l_row_id,
1117 l_seq ,
1118 l_def_party_rec.FUNDING_POOL_ID,
1119 l_def_party_rec.PARTY_ID ,
1120 l_def_party_rec.CURRENCY_CODE ,
1121 l_def_party_rec.CONVERSION_TYPE,
1122 l_def_party_rec.CONVERSION_DATE,
1123 l_def_party_rec.CONVERSION_RATE,
1124 l_def_party_rec.INITIAL_AMOUNT,
1125 l_def_party_rec.AMOUNT ,
1126 l_def_party_rec.AVAILABLE_AMOUNT,
1127 l_def_party_rec.START_DATE_ACTIVE,
1128 l_def_party_rec.END_DATE_ACTIVE,
1129 l_def_party_rec.LAST_UPDATE_DATE ,
1130 l_def_party_rec.LAST_UPDATED_BY ,
1131
1132 l_def_party_rec.CREATION_DATE ,
1133 l_def_party_rec.CREATED_BY ,
1134 l_def_party_rec.LAST_UPDATE_LOGIN ,
1135 l_def_party_rec.ATTRIBUTE_CATEGORY ,
1136 l_def_party_rec.ATTRIBUTE1 ,
1137 l_def_party_rec.ATTRIBUTE2 ,
1138 l_def_party_rec.ATTRIBUTE3 ,
1139 l_def_party_rec.ATTRIBUTE4 ,
1140 l_def_party_rec.ATTRIBUTE5 ,
1141 l_def_party_rec.ATTRIBUTE6 ,
1142 l_def_party_rec.ATTRIBUTE7 ,
1143 l_def_party_rec.ATTRIBUTE8 ,
1144 l_def_party_rec.ATTRIBUTE9 ,
1145 l_def_party_rec.ATTRIBUTE10 ,
1146 l_def_party_rec.ATTRIBUTE11 ,
1147 l_def_party_rec.ATTRIBUTE12 ,
1148 l_def_party_rec.ATTRIBUTE13 ,
1149 l_def_party_rec.ATTRIBUTE14 ,
1150 l_def_party_rec.ATTRIBUTE15 );
1151
1152 --oke_debug.debug('record inserted');
1153 -- Set OUT values
1154 x_party_rec := l_def_party_rec;
1155 x_party_rec.POOL_PARTY_ID:=l_seq;
1156 --oke_debug.debug('end call oke_party_pvt.insert_row');
1157 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1158
1159 EXCEPTION
1160 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1161 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1162 (
1163 l_api_name,
1164 G_PKG_NAME,
1165 'OKE_API.G_RET_STS_ERROR',
1166 x_msg_count,
1167 x_msg_data,
1168 '_PVT'
1169 );
1170 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1171 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1172 (
1173 l_api_name,
1174 G_PKG_NAME,
1175 'OKE_API.G_RET_STS_UNEXP_ERROR',
1176 x_msg_count,
1177 x_msg_data,
1178 '_PVT'
1179 );
1180 WHEN OTHERS THEN
1181 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1182 (
1183 l_api_name,
1184 G_PKG_NAME,
1185 'OTHERS',
1186 x_msg_count,
1187 x_msg_data,
1188 '_PVT'
1189 );
1190 END insert_row; -- row level
1191
1192
1193
1194
1195 -- table level insert
1196
1197 PROCEDURE insert_row(
1198 p_api_version IN NUMBER,
1199 p_init_msg_list IN VARCHAR2 ,
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 x_msg_count OUT NOCOPY NUMBER,
1202 x_msg_data OUT NOCOPY VARCHAR2,
1203 p_party_tbl IN party_tbl_type,
1204 x_party_tbl OUT NOCOPY party_tbl_type) IS
1205
1206 l_api_version CONSTANT NUMBER := 1;
1207 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
1208 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1209 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1210 i NUMBER := 0;
1211 BEGIN
1212 --oke_debug.debug('start call oke_party_pvt.insert_row');
1213
1214 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1215 G_PKG_NAME,
1216 p_init_msg_list,
1217 l_api_version,
1218 p_api_version,
1219 '_PVT',
1220 x_return_status);
1221
1222 -- Make sure PL/SQL table has records in it before passing
1223 IF (p_party_tbl.COUNT > 0) THEN
1224 i := p_party_tbl.FIRST;
1225 LOOP
1226 insert_row (
1230 x_msg_count => x_msg_count,
1227 p_api_version => p_api_version,
1228 p_init_msg_list => OKE_API.G_FALSE,
1229 x_return_status => x_return_status,
1231 x_msg_data => x_msg_data,
1232
1233 p_party_rec => p_party_tbl(i),
1234 x_party_rec => x_party_tbl(i));
1235
1236 -- store the highest degree of error
1237 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1238 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1239 l_overall_status := x_return_status;
1240 End If;
1241 End If;
1242
1243 EXIT WHEN (i = p_party_tbl.LAST);
1244
1245 i := p_party_tbl.NEXT(i);
1246 END LOOP;
1247 -- return overall status
1248 x_return_status := l_overall_status;
1249 END IF;
1250 --oke_debug.debug('end call oke_party_pvt.insert_row');
1251 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1252
1253 EXCEPTION
1254 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1255 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1256 (
1257 l_api_name,
1258 G_PKG_NAME,
1259 'OKE_API.G_RET_STS_ERROR',
1260 x_msg_count,
1261 x_msg_data,
1262 '_PVT'
1263 );
1264 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1265 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1266 (
1267 l_api_name,
1268 G_PKG_NAME,
1269 'OKE_API.G_RET_STS_UNEXP_ERROR',
1270 x_msg_count,
1271 x_msg_data,
1272 '_PVT'
1273 );
1274 WHEN OTHERS THEN
1275 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1276 (
1277 l_api_name,
1278 G_PKG_NAME,
1279 'OTHERS',
1280 x_msg_count,
1281 x_msg_data,
1282 '_PVT'
1283 );
1284 END insert_row; -- table level
1285
1286
1287
1288
1289
1290
1291
1292
1293 PROCEDURE update_row(
1294 p_api_version IN NUMBER,
1295 p_init_msg_list IN VARCHAR2 ,
1296 x_return_status OUT NOCOPY VARCHAR2,
1297 x_msg_count OUT NOCOPY NUMBER,
1298 x_msg_data OUT NOCOPY VARCHAR2,
1299 p_party_rec IN party_rec_type,
1300 x_party_rec OUT NOCOPY party_rec_type) IS
1301
1302 l_api_version CONSTANT NUMBER := 1.0;
1303 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1304 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1305 l_party_rec party_rec_type := p_party_rec;
1306 l_def_party_rec party_rec_type;
1307 lx_party_rec party_rec_type;
1308 l_available NUMBER;
1309 l_result VARCHAR2(1);
1310 l_currency VARCHAR2(15);
1311
1312 l_dummy_val VARCHAR2(1):='?';
1313 l_temp NUMBER;
1314
1315 Cursor l_csr_id IS
1316 select pool_party_id
1317 from oke_pool_parties
1318 where pool_party_id=p_party_rec.pool_party_id;
1319
1320 CURSOR l_csr IS
1321 SELECT 'x'
1322 FROM OKE_K_FUNDING_SOURCES
1323 WHERE pool_party_id = p_party_rec.pool_party_id;
1324
1325
1326 -------------------------------
1327 -- FUNCTION fill_who_columns --
1328 -------------------------------
1329 FUNCTION fill_who_columns (
1330 p_party_rec IN party_rec_type
1331 ) RETURN party_rec_type IS
1332
1333 l_party_rec party_rec_type := p_party_rec;
1334
1335 BEGIN
1336 l_party_rec.LAST_UPDATE_DATE := SYSDATE;
1337 l_party_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1338 l_party_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1339 RETURN(l_party_rec);
1340 END fill_who_columns;
1341
1342 ----------------------------------
1343 -- FUNCTION populate_new_record --
1344 ----------------------------------
1345 FUNCTION populate_new_record (
1346 p_party_rec IN party_rec_type,
1347 x_party_rec OUT NOCOPY party_rec_type
1348 ) RETURN VARCHAR2 IS
1349
1350 l_party_rec party_rec_type;
1351 l_row_notfound BOOLEAN := TRUE;
1352 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1353
1354 BEGIN
1355
1356 x_party_rec := p_party_rec;
1357
1358 l_party_rec := get_rec(p_party_rec, l_row_notfound);
1359
1360 IF (l_row_notfound) THEN
1361 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1362 END IF;
1363
1364
1365 IF x_party_rec.FUNDING_POOL_ID = OKE_API.G_MISS_NUM THEN
1366 x_party_rec.FUNDING_POOL_ID := l_party_rec.FUNDING_POOL_ID;
1367 END IF;
1368
1369 IF x_party_rec.PARTY_ID = OKE_API.G_MISS_NUM THEN
1370 x_party_rec.PARTY_ID := l_party_rec.PARTY_ID;
1371 END IF;
1372
1373 IF x_party_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
1374 x_party_rec.CURRENCY_CODE := l_party_rec.CURRENCY_CODE;
1375 END IF;
1376
1377 IF x_party_rec.CONVERSION_TYPE = OKE_API.G_MISS_CHAR THEN
1381 IF x_party_rec.CONVERSION_DATE = OKE_API.G_MISS_DATE THEN
1378 x_party_rec.CONVERSION_TYPE := l_party_rec.CONVERSION_TYPE;
1379 END IF;
1380
1382 x_party_rec.CONVERSION_DATE := l_party_rec.CONVERSION_DATE;
1383 END IF;
1384
1385 IF x_party_rec.CONVERSION_RATE = OKE_API.G_MISS_NUM THEN
1386 x_party_rec.CONVERSION_RATE := l_party_rec.CONVERSION_RATE;
1387 END IF;
1388
1389 IF x_party_rec.INITIAL_AMOUNT = OKE_API.G_MISS_NUM THEN
1390 x_party_rec.INITIAL_AMOUNT := l_party_rec.INITIAL_AMOUNT;
1391 END IF;
1392
1393 IF x_party_rec.AMOUNT = OKE_API.G_MISS_NUM THEN
1394 x_party_rec.AMOUNT := l_party_rec.AMOUNT;
1395 END IF;
1396
1397 IF x_party_rec.AVAILABLE_AMOUNT = OKE_API.G_MISS_NUM THEN
1398 x_party_rec.AVAILABLE_AMOUNT := l_party_rec.AVAILABLE_AMOUNT;
1399 END IF;
1400
1401 IF x_party_rec.START_DATE_ACTIVE = OKE_API.G_MISS_DATE THEN
1402 x_party_rec.START_DATE_ACTIVE := l_party_rec.START_DATE_ACTIVE;
1403 END IF;
1404
1405 IF x_party_rec.END_DATE_ACTIVE = OKE_API.G_MISS_DATE THEN
1406 x_party_rec.END_DATE_ACTIVE := l_party_rec.END_DATE_ACTIVE;
1407 END IF;
1408
1409 IF x_party_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
1410 x_party_rec.CREATION_DATE := l_party_rec.CREATION_DATE;
1411 END IF;
1412
1413 IF x_party_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
1414 x_party_rec.CREATED_BY := l_party_rec.CREATED_BY;
1415 END IF;
1416
1417 IF x_party_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
1418 x_party_rec.LAST_UPDATE_DATE := l_party_rec.LAST_UPDATE_DATE;
1419 END IF;
1420
1421 IF x_party_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
1422 x_party_rec.LAST_UPDATED_BY := l_party_rec.LAST_UPDATED_BY ;
1423 END IF;
1424
1425 IF x_party_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
1426 x_party_rec.LAST_UPDATE_LOGIN := l_party_rec.LAST_UPDATE_LOGIN;
1427 END IF;
1428
1429 IF x_party_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
1430 x_party_rec.ATTRIBUTE_CATEGORY := l_party_rec.ATTRIBUTE_CATEGORY;
1431 END IF;
1432
1433 IF x_party_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
1434 x_party_rec.ATTRIBUTE1 := l_party_rec.ATTRIBUTE1;
1435 END IF;
1436
1437 IF x_party_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
1438 x_party_rec.ATTRIBUTE2 := l_party_rec.ATTRIBUTE2;
1439 END IF;
1440
1441 IF x_party_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
1442 x_party_rec.ATTRIBUTE3 := l_party_rec.ATTRIBUTE3;
1443 END IF;
1444
1445 IF x_party_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
1446 x_party_rec.ATTRIBUTE4 := l_party_rec.ATTRIBUTE4;
1447 END IF;
1448
1449 IF x_party_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
1450 x_party_rec.ATTRIBUTE5 := l_party_rec.ATTRIBUTE5;
1451 END IF;
1452
1453 IF x_party_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
1454 x_party_rec.ATTRIBUTE6 := l_party_rec.ATTRIBUTE6;
1455 END IF;
1456
1457 IF x_party_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
1458 x_party_rec.ATTRIBUTE7 := l_party_rec.ATTRIBUTE7;
1459 END IF;
1460
1461 IF x_party_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
1462 x_party_rec.ATTRIBUTE8 := l_party_rec.ATTRIBUTE8;
1463 END IF;
1464
1465 IF x_party_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
1466 x_party_rec.ATTRIBUTE9 := l_party_rec.ATTRIBUTE9;
1467 END IF;
1468
1469 IF x_party_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
1470 x_party_rec.ATTRIBUTE10 := l_party_rec.ATTRIBUTE10;
1471 END IF;
1472
1473 IF x_party_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
1474 x_party_rec.ATTRIBUTE11 := l_party_rec.ATTRIBUTE11;
1475 END IF;
1476
1477 IF x_party_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
1478 x_party_rec.ATTRIBUTE12 := l_party_rec.ATTRIBUTE12;
1479 END IF;
1480
1481 IF x_party_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
1482 x_party_rec.ATTRIBUTE13 := l_party_rec.ATTRIBUTE13;
1483 END IF;
1484
1485 IF x_party_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
1486 x_party_rec.ATTRIBUTE14 := l_party_rec.ATTRIBUTE14;
1487 END IF;
1488
1489 IF x_party_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
1490 x_party_rec.ATTRIBUTE15 := l_party_rec.ATTRIBUTE15;
1491 END IF;
1492
1493 RETURN(l_return_status);
1494
1495 END populate_new_record;
1496
1497
1498
1499 FUNCTION set_attributes(
1500 p_party_rec IN party_rec_type,
1501 x_party_rec OUT NOCOPY party_rec_type
1502 ) RETURN VARCHAR2 IS
1503 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1504 l_to_currency VARCHAR2(15);
1505 l_rate NUMBER;
1506 l_pool_id NUMBER;
1507
1508 BEGIN
1509 x_party_rec := p_party_rec;
1510
1511 IF (p_party_rec.CONVERSION_TYPE IS NOT NULL) AND (p_party_rec.CONVERSION_TYPE <> OKE_API.G_MISS_CHAR) AND (p_party_rec.CONVERSION_DATE IS NOT NULL) AND (p_party_rec.CONVERSION_DATE <> OKE_API.G_MISS_DATE) THEN
1512
1513 SELECT FUNDING_POOL_ID INTO l_pool_id
1514 FROM OKE_POOL_PARTIES
1515 WHERE pool_party_id = p_party_rec.pool_party_id;
1516
1517 SELECT CURRENCY_CODE INTO l_to_currency
1518 FROM OKE_FUNDING_POOLS
1522
1519 WHERE FUNDING_POOL_ID = l_pool_id;
1520
1521 IF p_party_rec.CURRENCY_CODE <> l_to_currency THEN
1523 OKE_FUNDING_UTIL_PKG.GET_CONVERSION_RATE
1524 ( p_party_rec.CURRENCY_CODE,
1525 l_to_currency,
1526 p_party_rec.CONVERSION_TYPE,
1527 p_party_rec.CONVERSION_DATE,
1528 x_party_rec.CONVERSION_RATE,
1529 l_return_status);
1530 END IF;
1531
1532 ELSE
1533 x_party_rec.CONVERSION_RATE := NULL;
1534
1535 END IF;
1536
1537 RETURN(l_return_status);
1538
1539 END Set_Attributes;
1540
1541
1542 BEGIN -- update row
1543
1544 --oke_debug.debug('start call oke_party_pvt.update_row');
1545
1546 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1547 G_PKG_NAME,
1548 p_init_msg_list,
1549 l_api_version,
1550 p_api_version,
1551 '_PVT',
1552 x_return_status);
1553
1554
1555 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1556 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1557 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1558 RAISE OKE_API.G_EXCEPTION_ERROR;
1559 END IF;
1560
1561
1562 IF p_party_rec.pool_party_id = OKE_API.G_MISS_NUM THEN
1563 OKE_API.SET_MESSAGE(
1564 p_app_name =>g_app_name,
1565 p_msg_name =>g_invalid_value,
1566 p_token1 =>g_col_name_token,
1567 p_token1_value =>'pool_party_id');
1568 --oke_debug.debug('must provide pool_party_id');
1569 RAISE OKE_API.G_EXCEPTION_ERROR;
1570 END IF;
1571
1572
1573 IF p_party_rec.conversion_rate <> OKE_API.G_MISS_NUM THEN
1574 OKE_API.SET_MESSAGE(
1575 p_app_name =>g_app_name,
1576 p_msg_name =>g_invalid_value,
1577 p_token1 =>g_col_name_token,
1578 p_token1_value =>'conversion_rate');
1579 --oke_debug.debug('must not provide conversion_rate');
1580 RAISE OKE_API.G_EXCEPTION_ERROR;
1581 END IF;
1582
1583
1584 OPEN l_csr_id;
1585 FETCH l_csr_id INTO l_temp;
1586 IF l_csr_id%NOTFOUND THEN
1587 OKE_API.SET_MESSAGE(
1588 p_app_name =>g_app_name,
1589 p_msg_name =>g_invalid_value,
1590 p_token1 =>g_col_name_token,
1591 p_token1_value =>'pool_party_id');
1592 --oke_debug.debug('must provide valid pool_party_id');
1593 RAISE OKE_API.G_EXCEPTION_ERROR;
1594 END IF;
1595 CLOSE l_csr_id;
1596
1597 l_return_status := Set_Attributes(
1598 p_party_rec, -- IN
1599 l_party_rec); -- OUT
1600
1601 --- If any errors happen abort API
1602 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1603 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1604 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1605 RAISE OKE_API.G_EXCEPTION_ERROR;
1606 END IF;
1607
1608 --oke_debug.debug('attributes set');
1609
1610
1611
1612
1613 --check not updatable fields
1614
1615 IF p_party_rec.funding_pool_id <> OKE_API.G_MISS_NUM THEN
1616 OKE_API.SET_MESSAGE(
1617 p_app_name =>g_app_name,
1618 p_msg_name =>g_invalid_value,
1619 p_token1 =>g_col_name_token,
1620 p_token1_value =>'funding_pool_id');
1621 RAISE OKE_API.G_EXCEPTION_ERROR;
1622 END IF;
1623
1624 IF p_party_rec.initial_amount <> OKE_API.G_MISS_NUM THEN
1625 OKE_API.SET_MESSAGE(
1626 p_app_name =>g_app_name,
1627 p_msg_name =>g_invalid_value,
1628 p_token1 =>g_col_name_token,
1629 p_token1_value =>'initial_amount');
1630 RAISE OKE_API.G_EXCEPTION_ERROR;
1631 END IF;
1632
1633 IF p_party_rec.available_amount <> OKE_API.G_MISS_NUM THEN
1634 OKE_API.SET_MESSAGE(
1635 p_app_name =>g_app_name,
1636 p_msg_name =>g_invalid_value,
1637 p_token1 =>g_col_name_token,
1638 p_token1_value =>'available_amount');
1639 RAISE OKE_API.G_EXCEPTION_ERROR;
1640 END IF;
1641
1642 IF p_party_rec.currency_code <> OKE_API.G_MISS_CHAR THEN
1643
1644 OPEN l_csr;
1645 FETCH l_csr INTO l_dummy_val;
1646 CLOSE l_csr;
1647
1648 IF l_dummy_val = 'x' THEN --child records exist
1649 --oke_debug.debug('-child records exist');
1650 OKE_API.SET_MESSAGE(
1651 p_app_name =>g_app_name,
1652 p_msg_name =>g_invalid_value,
1653 p_token1 =>g_col_name_token,
1654 p_token1_value =>'currency_code');
1655 RAISE OKE_API.G_EXCEPTION_ERROR;
1656 END IF;
1657
1658
1659 SELECT a.currency_code INTO l_currency
1660 FROM oke_funding_pools a,oke_pool_parties b
1661 WHERE a.funding_pool_id = b.funding_pool_id
1662 AND b.pool_party_id=p_party_rec.pool_party_id;
1663
1664 IF l_currency = p_party_rec.currency_code THEN
1665
1666 IF(p_party_rec.CONVERSION_TYPE IS NOT NULL AND p_party_rec.CONVERSION_TYPE<>OKE_API.G_MISS_CHAR)
1667 OR(p_party_rec.CONVERSION_DATE IS NOT NULL AND p_party_rec.CONVERSION_DATE<>OKE_API.G_MISS_DATE)
1668
1669 THEN --error
1673 p_msg_name =>g_invalid_value,
1670 --oke_debug.debug('must not specify conversion');
1671 OKE_API.SET_MESSAGE(
1672 p_app_name =>g_app_name,
1674 p_token1 =>g_col_name_token,
1675 p_token1_value =>'conversion_date');
1676 OKE_API.SET_MESSAGE(
1677 p_app_name =>g_app_name,
1678 p_msg_name =>g_invalid_value,
1679 p_token1 =>g_col_name_token,
1680 p_token1_value =>'conversion_type');
1681
1682 RAISE OKE_API.G_EXCEPTION_ERROR;
1683 END IF;
1684
1685 ELSE
1686 IF((p_party_rec.CONVERSION_TYPE IS NULL)OR(p_party_rec.CONVERSION_TYPE=OKE_API.G_MISS_CHAR))
1687 OR((p_party_rec.CONVERSION_DATE IS NULL)OR(p_party_rec.CONVERSION_DATE=OKE_API.G_MISS_DATE))
1688
1689 THEN --error
1690 --oke_debug.debug('must specify conversion');
1691 OKE_API.SET_MESSAGE(
1692 p_app_name =>g_app_name,
1693 p_msg_name =>g_required_value,
1694 p_token1 =>g_col_name_token,
1695 p_token1_value =>'conversion_type');
1696 OKE_API.SET_MESSAGE(
1697 p_app_name =>g_app_name,
1698 p_msg_name =>g_required_value,
1699 p_token1 =>g_col_name_token,
1700 p_token1_value =>'conversion_date');
1701
1702
1703 RAISE OKE_API.G_EXCEPTION_ERROR;
1704 END IF;
1705
1706 END IF;
1707 END IF;
1708
1709
1710 ---- set available amount
1711
1712 IF (l_party_rec.amount <> OKE_API.G_MISS_NUM) THEN
1713
1714 OKE_FUNDING_UTIL_PKG.validate_pool_party_amount
1715 (
1716 x_pool_party_id => l_party_rec.pool_party_id,
1717 x_amount => l_party_rec.amount,
1718 x_allocated_amount=> l_available,
1719 x_return_status=> l_result );
1720
1721 IF l_result='Y' THEN
1722 l_party_rec.available_amount := l_party_rec.amount-l_available;
1723 ELSE
1724 OKE_API.SET_MESSAGE(
1725 p_app_name =>g_app_name,
1726 p_msg_name =>g_invalid_value,
1727 p_token1 =>g_col_name_token,
1728 p_token1_value =>'amount');
1729
1730 --oke_debug.debug('invalid amount');
1731 RAISE OKE_API.G_EXCEPTION_ERROR;
1732 END IF;
1733
1734 END IF;
1735
1736
1737 l_return_status := populate_new_record(l_party_rec, l_def_party_rec);
1738
1739 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1740 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1741 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1742 RAISE OKE_API.G_EXCEPTION_ERROR;
1743 END IF;
1744
1745 --oke_debug.debug('record populated');
1746
1747 l_def_party_rec := fill_who_columns(l_def_party_rec);
1748
1749 --oke_debug.debug('who column filled');
1750
1751
1752 --- Validate all non-missing attributes (Item Level Validation)
1753 l_return_status := Validate_Attributes(l_def_party_rec);
1754
1755 --- If any errors happen abort API
1756 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1757 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1758 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1759 RAISE OKE_API.G_EXCEPTION_ERROR;
1760 END IF;
1761
1762 --oke_debug.debug('attributes validated');
1763
1764 l_return_status := Validate_Record(l_def_party_rec);
1765 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1766 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1767 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1768 RAISE OKE_API.G_EXCEPTION_ERROR;
1769 END IF;
1770 --oke_debug.debug('record validated');
1771
1772 OKE_POOL_PARTIES_PKG.Update_Row(
1773 l_def_party_rec.POOL_PARTY_ID,
1774 l_def_party_rec.PARTY_ID,
1775 l_def_party_rec.CURRENCY_CODE,
1776 l_def_party_rec.CONVERSION_TYPE,
1777 l_def_party_rec.CONVERSION_DATE,
1778 l_def_party_rec.CONVERSION_RATE,
1779
1780 l_def_party_rec.AMOUNT,
1781 l_def_party_rec.AVAILABLE_AMOUNT,
1782 l_def_party_rec.START_DATE_ACTIVE,
1783 l_def_party_rec.END_DATE_ACTIVE,
1784
1785 l_def_party_rec.LAST_UPDATE_DATE,
1786 l_def_party_rec.LAST_UPDATED_BY,
1787 l_def_party_rec.LAST_UPDATE_LOGIN,
1788
1789 l_def_party_rec.ATTRIBUTE_CATEGORY,
1790 l_def_party_rec.ATTRIBUTE1,
1791 l_def_party_rec.ATTRIBUTE2,
1792 l_def_party_rec.ATTRIBUTE3,
1793 l_def_party_rec.ATTRIBUTE4,
1794 l_def_party_rec.ATTRIBUTE5,
1795 l_def_party_rec.ATTRIBUTE6,
1796 l_def_party_rec.ATTRIBUTE7,
1797 l_def_party_rec.ATTRIBUTE8,
1798 l_def_party_rec.ATTRIBUTE9,
1799 l_def_party_rec.ATTRIBUTE10,
1800 l_def_party_rec.ATTRIBUTE11,
1801 l_def_party_rec.ATTRIBUTE12,
1802 l_def_party_rec.ATTRIBUTE13,
1803 l_def_party_rec.ATTRIBUTE14,
1804 l_def_party_rec.ATTRIBUTE15);
1805
1806 x_party_rec := l_def_party_rec;
1807 --oke_debug.debug('end call oke_party_pvt.update_row');
1808 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1809
1810 EXCEPTION
1811 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1812 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1813 (
1814 l_api_name,
1815 G_PKG_NAME,
1816 'OKE_API.G_RET_STS_ERROR',
1817 x_msg_count,
1818 x_msg_data,
1819 '_PVT'
1820 );
1821 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1825 G_PKG_NAME,
1822 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1823 (
1824 l_api_name,
1826 'OKE_API.G_RET_STS_UNEXP_ERROR',
1827 x_msg_count,
1828 x_msg_data,
1829 '_PVT'
1830 );
1831 WHEN OTHERS THEN
1832 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1833 (
1834 l_api_name,
1835 G_PKG_NAME,
1836 'OTHERS',
1837 x_msg_count,
1838 x_msg_data,
1839 '_PVT'
1840 );
1841 END update_row; -- row level update
1842
1843
1844
1845 PROCEDURE update_row(
1846 p_api_version IN NUMBER,
1847 p_init_msg_list IN VARCHAR2 ,
1848 x_return_status OUT NOCOPY VARCHAR2,
1849 x_msg_count OUT NOCOPY NUMBER,
1850 x_msg_data OUT NOCOPY VARCHAR2,
1851 p_party_tbl IN party_tbl_type,
1852 x_party_tbl OUT NOCOPY party_tbl_type) IS
1853
1854 l_api_version CONSTANT NUMBER := 1.0;
1855 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
1856
1857
1858 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1859 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1860 i NUMBER := 0;
1861 BEGIN
1862
1863 --oke_debug.debug('start call oke_party_pvt.update_row');
1864
1865 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1866 G_PKG_NAME,
1867 p_init_msg_list,
1868 l_api_version,
1869 p_api_version,
1870 '_PVT',
1871 x_return_status);
1872
1873
1874 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1875 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1876 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1877 RAISE OKE_API.G_EXCEPTION_ERROR;
1878 END IF;
1879
1880 -- Make sure PL/SQL table has records in it before passing
1881 IF (p_party_tbl.COUNT > 0) THEN
1882 i := p_party_tbl.FIRST;
1883 LOOP
1884
1885 update_row (
1886 p_api_version => p_api_version,
1887 p_init_msg_list => OKE_API.G_FALSE,
1888 x_return_status => x_return_status,
1889 x_msg_count => x_msg_count,
1890 x_msg_data => x_msg_data,
1891 p_party_rec => p_party_tbl(i),
1892 x_party_rec => x_party_tbl(i));
1893
1894 -- store the highest degree of error
1895 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1896 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1897 l_overall_status := x_return_status;
1898 End If;
1899 End If;
1900
1901 EXIT WHEN (i = p_party_tbl.LAST);
1902 i := p_party_tbl.NEXT(i);
1903 END LOOP;
1904 -- return overall status
1905 x_return_status := l_overall_status;
1906 END IF;
1907 --oke_debug.debug('end call oke_party_pvt.update_row');
1908 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1909
1910 EXCEPTION
1911 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1912 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1913 (
1914 l_api_name,
1915 G_PKG_NAME,
1916 'OKE_API.G_RET_STS_ERROR',
1917 x_msg_count,
1918 x_msg_data,
1919 '_PVT'
1920 );
1921
1922 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1923 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1924 (
1925 l_api_name,
1926 G_PKG_NAME,
1927 'OKE_API.G_RET_STS_UNEXP_ERROR',
1928 x_msg_count,
1929 x_msg_data,
1930 '_PVT'
1931 );
1932 WHEN OTHERS THEN
1933 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1934 (
1935 l_api_name,
1936 G_PKG_NAME,
1937 'OTHERS',
1938 x_msg_count,
1939 x_msg_data,
1940 '_PVT'
1941 );
1942 END update_row; -- table level update
1943
1944
1945 -- deletes by the funding_party_id
1946
1947 PROCEDURE delete_row(
1948 p_api_version IN NUMBER,
1949 p_init_msg_list IN VARCHAR2 ,
1950 x_return_status OUT NOCOPY VARCHAR2,
1951 x_msg_count OUT NOCOPY NUMBER,
1952 x_msg_data OUT NOCOPY VARCHAR2,
1953 p_party_rec IN party_rec_type) IS
1954
1955 l_api_version CONSTANT NUMBER := 1;
1956 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1957 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1958 l_party_rec party_rec_type := p_party_rec;
1959 l_temp NUMBER;
1960
1961 Cursor l_csr_id IS
1962 select pool_party_id
1966 BEGIN
1963 from oke_pool_parties
1964 where pool_party_id=p_party_rec.pool_party_id;
1965
1967 --oke_debug.debug('start call oke_party_pvt.delete_row');
1968
1969 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1970 G_PKG_NAME,
1971 p_init_msg_list,
1972 l_api_version,
1973 p_api_version,
1974 '_PVT',
1975 x_return_status);
1976
1977
1978 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1979 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1980 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1981 RAISE OKE_API.G_EXCEPTION_ERROR;
1982 END IF;
1983
1984 IF p_party_rec.pool_party_id = OKE_API.G_MISS_NUM THEN
1985 OKE_API.SET_MESSAGE(
1986 p_app_name =>g_app_name,
1987 p_msg_name =>g_invalid_value,
1988 p_token1 =>g_col_name_token,
1989 p_token1_value =>'pool_party_id');
1990 --oke_debug.debug('must provide pool_party_id');
1991 RAISE OKE_API.G_EXCEPTION_ERROR;
1992 END IF;
1993
1994 OPEN l_csr_id;
1995 FETCH l_csr_id INTO l_temp;
1996 IF l_csr_id%NOTFOUND THEN
1997 OKE_API.SET_MESSAGE(
1998 p_app_name =>g_app_name,
1999 p_msg_name =>g_invalid_value,
2000 p_token1 =>g_col_name_token,
2001 p_token1_value =>'pool_party_id');
2002 --oke_debug.debug('must provide valid pool_party_id');
2003 RAISE OKE_API.G_EXCEPTION_ERROR;
2004 END IF;
2005 CLOSE l_csr_id;
2006
2007 DELETE FROM OKE_POOL_PARTIES
2008 WHERE POOL_PARTY_ID = p_party_rec.POOL_PARTY_ID;
2009
2010 --oke_debug.debug('end call oke_party_pvt.delete_row');
2011 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
2012
2013
2014 EXCEPTION
2015 WHEN OKE_API.G_EXCEPTION_ERROR THEN
2016 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2017 (
2018 l_api_name,
2019 G_PKG_NAME,
2020 'OKE_API.G_RET_STS_ERROR',
2021 x_msg_count,
2022 x_msg_data,
2023 '_PVT'
2024 );
2025 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2026 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2027 (
2028 l_api_name,
2029 G_PKG_NAME,
2030 'OKE_API.G_RET_STS_UNEXP_ERROR',
2031 x_msg_count,
2032 x_msg_data,
2033 '_PVT'
2034 );
2035 WHEN OTHERS THEN
2036 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2037 (
2038 l_api_name,
2039 G_PKG_NAME,
2040 'OTHERS',
2041 x_msg_count,
2042 x_msg_data,
2043 '_PVT'
2044 );
2045 END delete_row;
2046
2047
2048 -- table level delete
2049
2050 PROCEDURE delete_row(
2051 p_api_version IN NUMBER,
2052 p_init_msg_list IN VARCHAR2 ,
2053 x_return_status OUT NOCOPY VARCHAR2,
2054 x_msg_count OUT NOCOPY NUMBER,
2055 x_msg_data OUT NOCOPY VARCHAR2,
2056 p_party_tbl IN party_tbl_type) IS
2057
2058 l_api_version CONSTANT NUMBER := 1;
2059 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
2060 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2061 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2062 i NUMBER := 0;
2063 BEGIN
2064 --oke_debug.debug('start call oke_party_pvt.delete_row');
2065
2066 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
2067 G_PKG_NAME,
2068 p_init_msg_list,
2069 l_api_version,
2070 p_api_version,
2071 '_PVT',
2072 x_return_status);
2073
2074
2075 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2076 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2077 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2078 RAISE OKE_API.G_EXCEPTION_ERROR;
2079 END IF;
2080
2081 -- Make sure PL/SQL table has records in it before passing
2082 IF (p_party_tbl.COUNT > 0) THEN
2083 i := p_party_tbl.FIRST;
2084 LOOP
2085 delete_row (
2086 p_api_version => p_api_version,
2087 p_init_msg_list => OKE_API.G_FALSE,
2088 x_return_status => x_return_status,
2089 x_msg_count => x_msg_count,
2090 x_msg_data => x_msg_data,
2091 p_party_rec => p_party_tbl(i));
2092
2093 -- store the highest degree of error
2094 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2095 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2096 l_overall_status := x_return_status;
2100 EXIT WHEN (i = p_party_tbl.LAST);
2097 End If;
2098 End If;
2099
2101 i := p_party_tbl.NEXT(i);
2102 END LOOP;
2103
2104 -- return overall status
2105 x_return_status := l_overall_status;
2106 END IF;
2107 --oke_debug.debug('end call oke_party_pvt.delete_row');
2108 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
2109
2110
2111 EXCEPTION
2112 WHEN OKE_API.G_EXCEPTION_ERROR THEN
2113 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2114 (
2115 l_api_name,
2116 G_PKG_NAME,
2117 'OKE_API.G_RET_STS_ERROR',
2118 x_msg_count,
2119 x_msg_data,
2120 '_PVT'
2121 );
2122 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2123 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2124 (
2125 l_api_name,
2126 G_PKG_NAME,
2127 'OKE_API.G_RET_STS_UNEXP_ERROR',
2128 x_msg_count,
2129 x_msg_data,
2130 '_PVT'
2131 );
2132 WHEN OTHERS THEN
2133 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2134 (
2135 l_api_name,
2136 G_PKG_NAME,
2137 'OTHERS',
2138 x_msg_count,
2139 x_msg_data,
2140 '_PVT'
2141 );
2142 END delete_row; -- table level delete
2143
2144
2145 PROCEDURE lock_row(
2146 p_api_version IN NUMBER,
2147 p_init_msg_list IN VARCHAR2 ,
2148 x_return_status OUT NOCOPY VARCHAR2,
2149 x_msg_count OUT NOCOPY NUMBER,
2150 x_msg_data OUT NOCOPY VARCHAR2,
2151 p_party_rec IN party_rec_type) IS
2152
2153
2154 l_api_version CONSTANT NUMBER := 1;
2155 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
2156 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2157 l_row_notfound BOOLEAN := FALSE;
2158
2159 l_pool_party_id NUMBER;
2160
2161 E_Resource_Busy EXCEPTION;
2162 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2163
2164
2165 CURSOR lock_csr (p IN party_rec_type) IS
2166 SELECT pool_party_id FROM OKE_POOL_PARTIES a
2167 WHERE
2168 a.pool_party_id = p.pool_party_id
2169 FOR UPDATE NOWAIT;
2170
2171
2172 BEGIN
2173
2174 --oke_debug.debug('start call oke_party_pvt.lock_row');
2175
2176 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
2177 G_PKG_NAME,
2178 p_init_msg_list,
2179 l_api_version,
2180 p_api_version,
2181 '_PVT',
2182 x_return_status);
2183
2184
2185 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
2186 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2187 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
2188 RAISE OKE_API.G_EXCEPTION_ERROR;
2189 END IF;
2190
2191
2192 BEGIN
2193 OPEN lock_csr(p_party_rec);
2194 FETCH lock_csr INTO l_pool_party_id;
2195 l_row_notfound := lock_csr%NOTFOUND;
2196 CLOSE lock_csr;
2197
2198
2199 EXCEPTION
2200 WHEN E_Resource_Busy THEN
2201 IF (lock_csr%ISOPEN) THEN
2202 CLOSE lock_csr;
2203 END IF;
2204 OKE_API.set_message(G_APP_NAME,G_FORM_UNABLE_TO_RESERVE_REC);
2205 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2206 END;
2207
2208
2209 IF (l_row_notfound) THEN
2210 OKE_API.set_message(G_APP_NAME,G_FORM_RECORD_DELETED);
2211 RAISE OKE_API.G_EXCEPTION_ERROR;
2212 END IF;
2213
2214 --oke_debug.debug('end call oke_party_pvt.lock_row');
2215 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
2216
2217 EXCEPTION
2218 WHEN OKE_API.G_EXCEPTION_ERROR THEN
2219 x_return_status := OKE_API.HANDLE_EXCEPTIONS
2220 (
2221 l_api_name,
2222 G_PKG_NAME,
2223 'OKE_API.G_RET_STS_ERROR',
2224 x_msg_count,
2225 x_msg_data,
2226 '_PVT'
2227 );
2228 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2229 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2230 (
2231 l_api_name,
2232 G_PKG_NAME,
2233 'OKE_API.G_RET_STS_UNEXP_ERROR',
2234 x_msg_count,
2235 x_msg_data,
2236 '_PVT'
2237 );
2238 WHEN OTHERS THEN
2239 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
2240 (
2241 l_api_name,
2242 G_PKG_NAME,
2243 'OTHERS',
2244 x_msg_count,
2245 x_msg_data,
2246 '_PVT'
2247 );
2248 END lock_row;
2249
2250
2251 END OKE_PARTY_PVT;
2252