[Home] [Help]
PACKAGE BODY: APPS.OKE_POOL_PVT
Source
1 PACKAGE BODY OKE_POOL_PVT AS
2 /* $Header: OKEVFPLB.pls 120.0 2005/05/25 17:46:47 appldev noship $ */
3
4 -- validate record
5
6 FUNCTION validate_record (
7 p_pool_rec IN pool_rec_type
8 ) RETURN VARCHAR2 IS
9 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
10 BEGIN
11
12 RETURN(l_return_status);
13
14 END validate_record;
15
16 -- validate individual attributes
17
18 FUNCTION validate_attributes(
19 p_pool_rec IN pool_rec_type
20 ) RETURN VARCHAR2 IS
21
22 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
23 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
24
25
26 PROCEDURE validate_name (x_return_status OUT NOCOPY VARCHAR2,
27 p_pool_rec IN pool_rec_type)IS
28
29 BEGIN
30
31 x_return_status := OKE_API.G_RET_STS_SUCCESS;
32
33
34 -- check required value - not null
35
36 IF ( p_pool_rec.name = OKE_API.G_MISS_CHAR
37 OR p_pool_rec.name IS NULL) THEN
38 OKE_API.SET_MESSAGE(
39 p_app_name =>g_app_name,
40 p_msg_name =>g_required_value,
41 p_token1 =>g_col_name_token,
42 p_token1_value =>'NAME');
43
44 x_return_status := OKE_API.G_RET_STS_ERROR;
45 END IF;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 -- store SQL error message on message stack
50 OKE_API.SET_MESSAGE(
51 p_app_name =>g_app_name,
52 p_msg_name =>G_UNEXPECTED_ERROR,
53 p_token1 =>G_SQLCODE_TOKEN,
54 p_token1_value =>SQLCODE,
55 p_token2 =>G_SQLERRM_TOKEN,
56 p_token2_value =>SQLERRM);
57 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
58
59 END validate_name;
60
61
62 PROCEDURE validate_currency_code(x_return_status OUT NOCOPY VARCHAR2,
63 p_pool_rec IN pool_rec_type)IS
64
65 l_dummy_val VARCHAR2(1):='?';
66 CURSOR l_csr IS
67 SELECT 'x'
68 FROM FND_CURRENCIES
69 WHERE CURRENCY_CODE = p_pool_rec.CURRENCY_CODE;
70
71 BEGIN
72
73 x_return_status := OKE_API.G_RET_STS_SUCCESS;
74
75
76 -- check required value - not null
77
78 IF ( p_pool_rec.currency_code = OKE_API.G_MISS_CHAR
79 OR p_pool_rec.currency_code IS NULL) THEN
80 OKE_API.SET_MESSAGE(
81 p_app_name =>g_app_name,
82 p_msg_name =>g_required_value,
83 p_token1 =>g_col_name_token,
84 p_token1_value =>'CURRENCY_CODE');
85
86 x_return_status := OKE_API.G_RET_STS_ERROR;
87 raise G_EXCEPTION_HALT_VALIDATION;
88 END IF;
89
90
91 OPEN l_csr;
92 FETCH l_csr INTO l_dummy_val;
93 CLOSE l_csr;
94
95 IF (l_dummy_val = '?') THEN
96 OKE_API.SET_MESSAGE(
97 p_app_name =>g_app_name,
98 p_msg_name =>g_no_parent_record,
99 p_token1 =>g_col_name_token,
100 p_token1_value =>'CURRENCY_CODE',
101 p_token2 =>g_child_table_token,
102 p_token2_value =>G_VIEW,
103 p_token3 =>g_parent_table_token,
104 p_token3_value =>'FND_CURRENCIES');
105
106 x_return_status := OKE_API.G_RET_STS_ERROR;
107 END IF;
108
109
110 EXCEPTION
111 WHEN G_EXCEPTION_HALT_VALIDATION THEN
112 NULL;
113 WHEN OTHERS THEN
114 -- store SQL error message on message stack
115 OKE_API.SET_MESSAGE(
116 p_app_name =>g_app_name,
117 p_msg_name =>G_UNEXPECTED_ERROR,
118 p_token1 =>G_SQLCODE_TOKEN,
119 p_token1_value =>SQLCODE,
120 p_token2 =>G_SQLERRM_TOKEN,
121 p_token2_value =>SQLERRM);
122 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
123
124 IF l_csr%ISOPEN THEN
125 CLOSE l_csr;
126 END IF;
127
128 END validate_currency_code;
129
130
131 PROCEDURE validate_contact_person_id(x_return_status OUT NOCOPY VARCHAR2,
132 p_pool_rec IN pool_rec_type)IS
133
134 l_dummy_val VARCHAR2(1):='?';
135 CURSOR l_csr IS
136 SELECT 'x'
137 FROM PER_EMPLOYEES_CURRENT_X
138 WHERE EMPLOYEE_ID= p_pool_rec.CONTACT_PERSON_ID;
139
140 BEGIN
141
142 x_return_status := OKE_API.G_RET_STS_SUCCESS;
143
144 IF(p_pool_rec.contact_person_id <> OKE_API.G_MISS_NUM
145 AND p_pool_rec.contact_person_id IS NOT NULL) THEN
146
147 OPEN l_csr;
148 FETCH l_csr INTO l_dummy_val;
149 CLOSE l_csr;
150
151 IF (l_dummy_val = '?') THEN
152 OKE_API.SET_MESSAGE(
153 p_app_name =>g_app_name,
154 p_msg_name =>g_no_parent_record,
155 p_token1 =>g_col_name_token,
156 p_token1_value =>'CONTACT_PERSON_ID',
157 p_token2 =>g_child_table_token,
158 p_token2_value =>G_VIEW,
159 p_token3 =>g_parent_table_token,
160 p_token3_value =>'PER_EMPLOYEES_CURRENT_X');
161
162 x_return_status := OKE_API.G_RET_STS_ERROR;
163 END IF;
164 END IF;
165 EXCEPTION
166 WHEN OTHERS THEN
167 -- store SQL error message on message stack
168 OKE_API.SET_MESSAGE(
169 p_app_name =>g_app_name,
170 p_msg_name =>G_UNEXPECTED_ERROR,
171 p_token1 =>G_SQLCODE_TOKEN,
172 p_token1_value =>SQLCODE,
173 p_token2 =>G_SQLERRM_TOKEN,
174 p_token2_value =>SQLERRM);
175 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
176
177 IF l_csr%ISOPEN THEN
178 CLOSE l_csr;
179 END IF;
180
181 END validate_contact_person_id;
182
183
184
185 PROCEDURE validate_program_id(x_return_status OUT NOCOPY VARCHAR2,
186 p_pool_rec IN pool_rec_type)IS
187
188 l_dummy_val VARCHAR2(1):='?';
189 CURSOR l_csr IS
190 SELECT 'x'
191 FROM OKE_PROGRAMS
192 WHERE PROGRAM_ID= p_pool_rec.PROGRAM_ID
193 AND SYSDATE BETWEEN START_DATE
194 AND NVL(END_DATE+1 , SYSDATE )
195 ;
196
197 BEGIN
198
199 x_return_status := OKE_API.G_RET_STS_SUCCESS;
200
201 IF(p_pool_rec.program_id <> OKE_API.G_MISS_NUM
202 AND p_pool_rec.program_id IS NOT NULL) THEN
203
204 OPEN l_csr;
205 FETCH l_csr INTO l_dummy_val;
206 CLOSE l_csr;
207
208 IF (l_dummy_val = '?') THEN
209 OKE_API.SET_MESSAGE(
210 p_app_name =>g_app_name,
211 p_msg_name =>g_no_parent_record,
212 p_token1 =>g_col_name_token,
213 p_token1_value =>'PROGRAM_ID',
214 p_token2 =>g_child_table_token,
215 p_token2_value =>G_VIEW,
216 p_token3 =>g_parent_table_token,
217 p_token3_value =>'OKE_PROGRAMS');
218
219 x_return_status := OKE_API.G_RET_STS_ERROR;
220 END IF;
221 END IF;
222 EXCEPTION
223 WHEN OTHERS THEN
224 -- store SQL error message on message stack
225 OKE_API.SET_MESSAGE(
226 p_app_name =>g_app_name,
227 p_msg_name =>G_UNEXPECTED_ERROR,
228 p_token1 =>G_SQLCODE_TOKEN,
229 p_token1_value =>SQLCODE,
230 p_token2 =>G_SQLERRM_TOKEN,
231 p_token2_value =>SQLERRM);
232 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
233
234 IF l_csr%ISOPEN THEN
235 CLOSE l_csr;
236 END IF;
237
238 END validate_program_id;
239
240
241
242 BEGIN
243
244 validate_name (x_return_status => l_return_status,
245 p_pool_rec => p_pool_rec);
246 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
247 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
248 x_return_status := l_return_status;
249 END IF;
250 END IF;
251
252 validate_currency_code (x_return_status => l_return_status,
253 p_pool_rec => p_pool_rec);
254 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
255 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
256 x_return_status := l_return_status;
257 END IF;
258 END IF;
259
260 validate_contact_person_id (x_return_status => l_return_status,
261 p_pool_rec => p_pool_rec);
262 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
263 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
264 x_return_status := l_return_status;
265 END IF;
266 END IF;
267
268 validate_program_id (x_return_status => l_return_status,
269 p_pool_rec => p_pool_rec);
270 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
271 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
272 x_return_status := l_return_status;
273 END IF;
274 END IF;
275 /* call individual validation procedure */
276 -- return status to caller
277 RETURN(x_return_status);
278
279 END Validate_Attributes;
280
281
282 -- called by insert_row to make unfilled attributes NULL
283
284 FUNCTION null_out_defaults(
285 p_pool_rec IN pool_rec_type ) RETURN pool_rec_type IS
286
287 l_pool_rec pool_rec_type := p_pool_rec;
288
289 BEGIN
290
291 IF l_pool_rec.FUNDING_POOL_ID = OKE_API.G_MISS_NUM THEN
292 l_pool_rec.FUNDING_POOL_ID := NULL;
293 END IF;
294
295 IF l_pool_rec.NAME = OKE_API.G_MISS_CHAR THEN
296 l_pool_rec.NAME := NULL;
297 END IF;
298
299 IF l_pool_rec.DESCRIPTION = OKE_API.G_MISS_CHAR THEN
300 l_pool_rec.DESCRIPTION := NULL;
301 END IF;
302
303 IF l_pool_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
304 l_pool_rec.CURRENCY_CODE := NULL;
305 END IF;
306
307 IF l_pool_rec.CONTACT_PERSON_ID = OKE_API.G_MISS_NUM THEN
308 l_pool_rec.CONTACT_PERSON_ID := NULL;
309 END IF;
310
311 IF l_pool_rec.PROGRAM_ID = OKE_API.G_MISS_NUM THEN
312 l_pool_rec.PROGRAM_ID := NULL;
313 END IF;
314
315
316 IF l_pool_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
317 l_pool_rec.ATTRIBUTE_CATEGORY := NULL;
318 END IF;
319
320 IF l_pool_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
321 l_pool_rec.ATTRIBUTE1 := NULL;
322 END IF;
323
324 IF l_pool_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
325 l_pool_rec.ATTRIBUTE2 := NULL;
326 END IF;
327
328 IF l_pool_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
329 l_pool_rec.ATTRIBUTE3 := NULL;
330 END IF;
331
332 IF l_pool_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
333 l_pool_rec.ATTRIBUTE4 := NULL;
334 END IF;
335
336 IF l_pool_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
337 l_pool_rec.ATTRIBUTE5 := NULL;
338 END IF;
339
340 IF l_pool_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
341 l_pool_rec.ATTRIBUTE6 := NULL;
342 END IF;
343
344 IF l_pool_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
345 l_pool_rec.ATTRIBUTE7 := NULL;
346 END IF;
347
348 IF l_pool_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
349 l_pool_rec.ATTRIBUTE8 := NULL;
350 END IF;
351
352 IF l_pool_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
353 l_pool_rec.ATTRIBUTE9 := NULL;
354 END IF;
355
356 IF l_pool_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
357 l_pool_rec.ATTRIBUTE10 := NULL;
358 END IF;
359
360 IF l_pool_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
361 l_pool_rec.ATTRIBUTE11 := NULL;
362 END IF;
363
364 IF l_pool_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
365 l_pool_rec.ATTRIBUTE12 := NULL;
366 END IF;
367
368 IF l_pool_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
369 l_pool_rec.ATTRIBUTE13 := NULL;
370 END IF;
371
372 IF l_pool_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
373 l_pool_rec.ATTRIBUTE14 := NULL;
374 END IF;
375
376 IF l_pool_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
377 l_pool_rec.ATTRIBUTE15 := NULL;
378 END IF;
379
380 IF l_pool_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
381 l_pool_rec.CREATED_BY := NULL;
382 END IF;
383
384 IF l_pool_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
385 l_pool_rec.CREATION_DATE := NULL;
386 END IF;
387
388 IF l_pool_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
389 l_pool_rec.LAST_UPDATED_BY := NULL;
390 END IF;
391
392 IF l_pool_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
393 l_pool_rec.LAST_UPDATE_LOGIN := NULL;
394 END IF;
395
396 IF l_pool_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
397 l_pool_rec.LAST_UPDATE_DATE := NULL;
398 END IF;
399
400 RETURN(l_pool_rec);
401
402 END null_out_defaults;
403
404
405 -- gets the record based on a key attribute
406
407 FUNCTION get_rec (
408 p_pool_rec IN pool_rec_type,
409 x_no_data_found OUT NOCOPY BOOLEAN
410 ) RETURN pool_rec_type IS
411
412 CURSOR pool_pk_csr ( p_funding_pool_id NUMBER) IS
413 SELECT
414
415 FUNDING_POOL_ID,
416 NAME,
417 DESCRIPTION,
418 CURRENCY_CODE,
419 CONTACT_PERSON_ID,
420 PROGRAM_ID,
421
422 CREATION_DATE,
423 CREATED_BY,
424 LAST_UPDATE_DATE,
425 LAST_UPDATED_BY,
426 LAST_UPDATE_LOGIN,
427 ATTRIBUTE_CATEGORY,
428 ATTRIBUTE1 ,
429 ATTRIBUTE2 ,
430 ATTRIBUTE3 ,
431 ATTRIBUTE4 ,
432 ATTRIBUTE5 ,
433 ATTRIBUTE6 ,
434 ATTRIBUTE7 ,
435 ATTRIBUTE8 ,
436 ATTRIBUTE9 ,
437 ATTRIBUTE10 ,
438 ATTRIBUTE11 ,
439 ATTRIBUTE12 ,
440 ATTRIBUTE13 ,
441 ATTRIBUTE14 ,
442 ATTRIBUTE15
443
444
445 FROM OKE_FUNDING_POOLS a
446 WHERE (a.funding_pool_id = p_funding_pool_id);
447
448 l_pool_pk pool_pk_csr%ROWTYPE;
449 l_pool_rec pool_rec_type;
450
451 BEGIN
452 x_no_data_found := TRUE;
453
454 -- get current database value
455
456 OPEN pool_pk_csr(p_pool_rec.FUNDING_POOL_ID);
457 FETCH pool_pk_csr INTO
458 l_pool_rec.FUNDING_POOL_ID ,
459 l_pool_rec.NAME ,
460 l_pool_rec.DESCRIPTION ,
461 l_pool_rec.CURRENCY_CODE ,
462 l_pool_rec.CONTACT_PERSON_ID ,
463 l_pool_rec.PROGRAM_ID ,
464 l_pool_rec.CREATION_DATE ,
465 l_pool_rec.CREATED_BY ,
466 l_pool_rec.LAST_UPDATE_DATE ,
467 l_pool_rec.LAST_UPDATED_BY ,
468 l_pool_rec.LAST_UPDATE_LOGIN ,
469 l_pool_rec.ATTRIBUTE_CATEGORY ,
470 l_pool_rec.ATTRIBUTE1 ,
471 l_pool_rec.ATTRIBUTE2 ,
472 l_pool_rec.ATTRIBUTE3 ,
473 l_pool_rec.ATTRIBUTE4 ,
474 l_pool_rec.ATTRIBUTE5 ,
475 l_pool_rec.ATTRIBUTE6 ,
476 l_pool_rec.ATTRIBUTE7 ,
477 l_pool_rec.ATTRIBUTE8 ,
478 l_pool_rec.ATTRIBUTE9 ,
479 l_pool_rec.ATTRIBUTE10 ,
480 l_pool_rec.ATTRIBUTE11 ,
481 l_pool_rec.ATTRIBUTE12 ,
482 l_pool_rec.ATTRIBUTE13 ,
483 l_pool_rec.ATTRIBUTE14 ,
484 l_pool_rec.ATTRIBUTE15 ;
485
486 x_no_data_found := pool_pk_csr%NOTFOUND;
487 CLOSE pool_pk_csr;
488 IF(x_no_data_found) THEN
489 OKE_API.set_message(G_APP_NAME,G_FORM_RECORD_DELETED);
490 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
491 END IF;
492 RETURN(l_pool_rec);
493
494 END get_rec;
495
496
497
498 -- row level insert
499 -- will create using nextVal from sequence oke_funding_pools_s
500
501 PROCEDURE insert_row(
502 p_api_version IN NUMBER,
503 p_init_msg_list IN VARCHAR2 ,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_pool_rec IN pool_rec_type,
508 x_pool_rec OUT NOCOPY pool_rec_type) IS
509
510 l_api_version CONSTANT NUMBER := 1;
511 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
512 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
513 l_pool_rec pool_rec_type;
514 l_def_pool_rec pool_rec_type;
515 lx_pool_rec pool_rec_type;
516 l_seq NUMBER;
517 l_row_id RowID;
518
519 -- FUNCTION fill_who_columns --
520 -------------------------------
521 FUNCTION fill_who_columns (
522 p_pool_rec IN pool_rec_type
523 ) RETURN pool_rec_type IS
524
525 l_pool_rec pool_rec_type := p_pool_rec;
526
527 BEGIN
528
529 l_pool_rec.CREATION_DATE := SYSDATE;
530 l_pool_rec.CREATED_BY := FND_GLOBAL.USER_ID;
531 l_pool_rec.LAST_UPDATE_DATE := SYSDATE;
532 l_pool_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
533 l_pool_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
534 RETURN(l_pool_rec);
535
536 END fill_who_columns;
537
538
539 -- nothing much here. flags to UPPERCASE
540
541 FUNCTION Set_Attributes (
542 p_pool_rec IN pool_rec_type,
543 x_pool_rec OUT NOCOPY pool_rec_type
544 ) RETURN VARCHAR2 IS
545 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
546 BEGIN
547 x_pool_rec := p_pool_rec;
548 RETURN(l_return_status);
549
550 END Set_Attributes;
551
552
553 BEGIN -- insert
554 --oke_debug.debug('start call oke_pool_pvt.insert_row');
555
556 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
557 G_PKG_NAME,
558 p_init_msg_list,
559 l_api_version,
560 p_api_version,
561 '_PVT',
562 x_return_status);
563
564
565 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
566 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
567 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
568 RAISE OKE_API.G_EXCEPTION_ERROR;
569 END IF;
570
571
572 IF p_pool_rec.funding_pool_id <> OKE_API.G_MISS_NUM THEN
573 OKE_API.SET_MESSAGE(
574 p_app_name =>g_app_name,
575 p_msg_name =>g_invalid_value,
576 p_token1 =>g_col_name_token,
577 p_token1_value =>'funding_pool_id');
578 --dbms_output.put_line('must not provide funding_pool_id');
579 RAISE OKE_API.G_EXCEPTION_ERROR;
580 END IF;
581
582 l_pool_rec := null_out_defaults(p_pool_rec);
583
584 --oke_debug.debug(' called null out defaults');
585
586 --- Setting item attributes
587 l_return_status := Set_Attributes(
588 l_pool_rec, -- IN
589 l_def_pool_rec); -- OUT
590
591 --- If any errors happen abort API
592 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
593 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
594 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
595 RAISE OKE_API.G_EXCEPTION_ERROR;
596 END IF;
597
598 --oke_debug.debug('attributes set for insert');
599
600 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
601 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
602 END IF;
603
604 l_def_pool_rec := fill_who_columns(l_def_pool_rec);
605
606 --oke_debug.debug('who column filled for insert');
607
608 --- Validate all non-missing attributes (Item Level Validation)
609 l_return_status := Validate_Attributes(l_def_pool_rec);
610
611 --- If any errors happen abort API
612 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
613 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
614
615 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
616 RAISE OKE_API.G_EXCEPTION_ERROR;
617 END IF;
618
619 --oke_debug.debug('attributes validated for insert');
620
621 l_return_status := Validate_Record(l_def_pool_rec);
622
623 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
624 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
625 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
626 RAISE OKE_API.G_EXCEPTION_ERROR;
627 END IF;
628
629 --oke_debug.debug('record validated for insert');
630
631 SELECT OKE_FUNDING_POOLS_S.nextval INTO l_seq FROM dual;
632
633 OKE_FUNDING_POOLS_PKG.Insert_Row
634 (l_row_id,
635 l_seq,
636 l_def_pool_rec.NAME,
637 l_def_pool_rec.DESCRIPTION,
638 l_def_pool_rec.CURRENCY_CODE,
639 l_def_pool_rec.CONTACT_PERSON_ID,
640 l_def_pool_rec.PROGRAM_ID,
641 l_def_pool_rec.LAST_UPDATE_DATE ,
642 l_def_pool_rec.LAST_UPDATED_BY ,
643 l_def_pool_rec.CREATION_DATE ,
644 l_def_pool_rec.CREATED_BY ,
645 l_def_pool_rec.LAST_UPDATE_LOGIN ,
646 l_def_pool_rec.ATTRIBUTE_CATEGORY ,
647 l_def_pool_rec.ATTRIBUTE1 ,
648 l_def_pool_rec.ATTRIBUTE2 ,
649 l_def_pool_rec.ATTRIBUTE3 ,
650 l_def_pool_rec.ATTRIBUTE4 ,
651 l_def_pool_rec.ATTRIBUTE5 ,
652 l_def_pool_rec.ATTRIBUTE6 ,
653 l_def_pool_rec.ATTRIBUTE7 ,
654 l_def_pool_rec.ATTRIBUTE8 ,
655 l_def_pool_rec.ATTRIBUTE9 ,
656 l_def_pool_rec.ATTRIBUTE10 ,
657 l_def_pool_rec.ATTRIBUTE11 ,
658 l_def_pool_rec.ATTRIBUTE12 ,
659 l_def_pool_rec.ATTRIBUTE13 ,
660 l_def_pool_rec.ATTRIBUTE14 ,
661 l_def_pool_rec.ATTRIBUTE15
662 );
663
664 --oke_debug.debug('record inserted');
665 -- Set OUT values
666 x_pool_rec := l_def_pool_rec;
667 x_pool_rec.FUNDING_POOL_ID:=l_seq;
668
669 --oke_debug.debug('end call oke_pool_pvt.insert_row');
670 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
671
672 EXCEPTION
673 WHEN OKE_API.G_EXCEPTION_ERROR THEN
674 x_return_status := OKE_API.HANDLE_EXCEPTIONS
675 (
676 l_api_name,
677 G_PKG_NAME,
678 'OKE_API.G_RET_STS_ERROR',
679 x_msg_count,
680 x_msg_data,
681 '_PVT'
682 );
683 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
684 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
685 (
686 l_api_name,
687 G_PKG_NAME,
688 'OKE_API.G_RET_STS_UNEXP_ERROR',
689 x_msg_count,
690 x_msg_data,
691 '_PVT'
692 );
693 WHEN OTHERS THEN
694 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
695 (
696 l_api_name,
697 G_PKG_NAME,
698 'OTHERS',
699 x_msg_count,
700 x_msg_data,
701 '_PVT'
702 );
703 END insert_row; -- row level
704
705
706
707
708 -- table level insert
709
710 PROCEDURE insert_row(
711 p_api_version IN NUMBER,
712 p_init_msg_list IN VARCHAR2 ,
713 x_return_status OUT NOCOPY VARCHAR2,
714 x_msg_count OUT NOCOPY NUMBER,
715 x_msg_data OUT NOCOPY VARCHAR2,
716 p_pool_tbl IN pool_tbl_type,
717 x_pool_tbl OUT NOCOPY pool_tbl_type) IS
718
719 l_api_version CONSTANT NUMBER := 1;
720 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
721 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
722 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
723 i NUMBER := 0;
724 BEGIN
725
726 --oke_debug.debug('start call oke_pool_pvt.insert_row');
727
728 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
729 G_PKG_NAME,
730 p_init_msg_list,
731 l_api_version,
732 p_api_version,
733 '_PVT',
734 x_return_status);
735 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
736 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
737 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
738 RAISE OKE_API.G_EXCEPTION_ERROR;
739 END IF;
740
741
742 -- Make sure PL/SQL table has records in it before passing
743 IF (p_pool_tbl.COUNT > 0) THEN
744 i := p_pool_tbl.FIRST;
745 LOOP
746 insert_row (
747 p_api_version => p_api_version,
748 p_init_msg_list => OKE_API.G_FALSE,
749 x_return_status => x_return_status,
750 x_msg_count => x_msg_count,
751 x_msg_data => x_msg_data,
752
753 p_pool_rec => p_pool_tbl(i),
754 x_pool_rec => x_pool_tbl(i));
755
756 -- store the highest degree of error
757 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
758 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
759 l_overall_status := x_return_status;
760 End If;
761 End If;
762
763 EXIT WHEN (i = p_pool_tbl.LAST);
764
765 i := p_pool_tbl.NEXT(i);
766 END LOOP;
767 -- return overall status
768 x_return_status := l_overall_status;
769 END IF;
770 --oke_debug.debug('end call oke_pool_pvt.insert_row');
771 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
772
773 EXCEPTION
774 WHEN OKE_API.G_EXCEPTION_ERROR THEN
775 x_return_status := OKE_API.HANDLE_EXCEPTIONS
776 (
777 l_api_name,
778 G_PKG_NAME,
779 'OKE_API.G_RET_STS_ERROR',
780 x_msg_count,
781 x_msg_data,
782 '_PVT'
783 );
784 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
785 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
786 (
787 l_api_name,
788 G_PKG_NAME,
789 'OKE_API.G_RET_STS_UNEXP_ERROR',
790 x_msg_count,
791 x_msg_data,
792 '_PVT'
793 );
794 WHEN OTHERS THEN
795 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
796 (
797 l_api_name,
798 G_PKG_NAME,
799 'OTHERS',
800 x_msg_count,
801 x_msg_data,
802 '_PVT'
803 );
804 END insert_row; -- table level
805
806
807
808
809
810
811
812
813 PROCEDURE update_row(
814 p_api_version IN NUMBER,
815 p_init_msg_list IN VARCHAR2 ,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_pool_rec IN pool_rec_type,
820 x_pool_rec OUT NOCOPY pool_rec_type) IS
821
822 l_api_version CONSTANT NUMBER := 1.0;
823 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
824 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
825 l_pool_rec pool_rec_type := p_pool_rec;
826 l_def_pool_rec pool_rec_type;
827 lx_pool_rec pool_rec_type;
828 l_temp NUMBER;
829
830 Cursor l_csr_id IS
831 select funding_pool_id
832 from oke_funding_pools
833 where funding_pool_id=p_pool_rec.funding_pool_id;
834
835 -------------------------------
836 -- FUNCTION fill_who_columns --
837 -------------------------------
838 FUNCTION fill_who_columns (
839 p_pool_rec IN pool_rec_type
840 ) RETURN pool_rec_type IS
841
842 l_pool_rec pool_rec_type := p_pool_rec;
843
844 BEGIN
845 l_pool_rec.LAST_UPDATE_DATE := SYSDATE;
846 l_pool_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
847 l_pool_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
848 RETURN(l_pool_rec);
849 END fill_who_columns;
850
851 ----------------------------------
852 -- FUNCTION populate_new_record --
853 ----------------------------------
854 FUNCTION populate_new_record (
855 p_pool_rec IN pool_rec_type,
856 x_pool_rec OUT NOCOPY pool_rec_type
857 ) RETURN VARCHAR2 IS
858
859 l_pool_rec pool_rec_type;
860 l_row_notfound BOOLEAN := TRUE;
861 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
862
863 BEGIN
864
865 x_pool_rec := p_pool_rec;
866
867 l_pool_rec := get_rec(p_pool_rec, l_row_notfound);
868
869 IF (l_row_notfound) THEN
870 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
871 END IF;
872
873 IF x_pool_rec.NAME = OKE_API.G_MISS_CHAR THEN
874 x_pool_rec.NAME := l_pool_rec.NAME;
875 END IF;
876
877 IF x_pool_rec.DESCRIPTION = OKE_API.G_MISS_CHAR THEN
878 x_pool_rec.DESCRIPTION := l_pool_rec.DESCRIPTION;
879 END IF;
880
881 IF x_pool_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
882 x_pool_rec.CURRENCY_CODE := l_pool_rec.CURRENCY_CODE;
883 END IF;
884
885 IF x_pool_rec.CONTACT_PERSON_ID = OKE_API.G_MISS_NUM THEN
886 x_pool_rec.CONTACT_PERSON_ID := l_pool_rec.CONTACT_PERSON_ID;
887 END IF;
888
889 IF x_pool_rec.PROGRAM_ID = OKE_API.G_MISS_NUM THEN
890 x_pool_rec.PROGRAM_ID := l_pool_rec.PROGRAM_ID;
891 END IF;
892
893
894 IF x_pool_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
895 x_pool_rec.CREATION_DATE := l_pool_rec.CREATION_DATE;
896 END IF;
897
898 IF x_pool_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
899 x_pool_rec.CREATED_BY := l_pool_rec.CREATED_BY;
900 END IF;
901
902 IF x_pool_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
903 x_pool_rec.LAST_UPDATE_DATE := l_pool_rec.LAST_UPDATE_DATE;
904 END IF;
905
906 IF x_pool_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
907 x_pool_rec.LAST_UPDATED_BY := l_pool_rec.LAST_UPDATED_BY ;
908 END IF;
909
910 IF x_pool_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
911 x_pool_rec.LAST_UPDATE_LOGIN := l_pool_rec.LAST_UPDATE_LOGIN;
912 END IF;
913
914 IF x_pool_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
915 x_pool_rec.ATTRIBUTE_CATEGORY := l_pool_rec.ATTRIBUTE_CATEGORY;
916 END IF;
917
918 IF x_pool_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
919 x_pool_rec.ATTRIBUTE1 := l_pool_rec.ATTRIBUTE1;
920 END IF;
921
922 IF x_pool_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
923 x_pool_rec.ATTRIBUTE2 := l_pool_rec.ATTRIBUTE2;
924 END IF;
925
926 IF x_pool_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
927 x_pool_rec.ATTRIBUTE3 := l_pool_rec.ATTRIBUTE3;
928 END IF;
929
930 IF x_pool_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
931 x_pool_rec.ATTRIBUTE4 := l_pool_rec.ATTRIBUTE4;
932 END IF;
933
934 IF x_pool_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
935 x_pool_rec.ATTRIBUTE5 := l_pool_rec.ATTRIBUTE5;
936 END IF;
937
938 IF x_pool_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
939 x_pool_rec.ATTRIBUTE6 := l_pool_rec.ATTRIBUTE6;
940 END IF;
941
942 IF x_pool_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
943 x_pool_rec.ATTRIBUTE7 := l_pool_rec.ATTRIBUTE7;
944 END IF;
945
946 IF x_pool_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
947 x_pool_rec.ATTRIBUTE8 := l_pool_rec.ATTRIBUTE8;
948 END IF;
949
950 IF x_pool_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
951 x_pool_rec.ATTRIBUTE9 := l_pool_rec.ATTRIBUTE9;
952 END IF;
953
954 IF x_pool_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
955 x_pool_rec.ATTRIBUTE10 := l_pool_rec.ATTRIBUTE10;
956 END IF;
957
958 IF x_pool_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
959 x_pool_rec.ATTRIBUTE11 := l_pool_rec.ATTRIBUTE11;
960 END IF;
961
962 IF x_pool_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
963 x_pool_rec.ATTRIBUTE12 := l_pool_rec.ATTRIBUTE12;
964 END IF;
965
966 IF x_pool_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
967 x_pool_rec.ATTRIBUTE13 := l_pool_rec.ATTRIBUTE13;
968 END IF;
969
970 IF x_pool_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
971 x_pool_rec.ATTRIBUTE14 := l_pool_rec.ATTRIBUTE14;
972 END IF;
973
974 IF x_pool_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
975 x_pool_rec.ATTRIBUTE15 := l_pool_rec.ATTRIBUTE15;
976 END IF;
977
978 RETURN(l_return_status);
979
980 END populate_new_record;
981
982
983
984 FUNCTION set_attributes(
985 p_pool_rec IN pool_rec_type,
986 x_pool_rec OUT NOCOPY pool_rec_type
987 ) RETURN VARCHAR2 IS
988 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
989 BEGIN
990 x_pool_rec := p_pool_rec;
991 RETURN(l_return_status);
992
993 END Set_Attributes;
994
995
996 BEGIN -- update row
997
998 --oke_debug.debug('start call oke_pool_pvt.update_row');
999
1000 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1001 G_PKG_NAME,
1002 p_init_msg_list,
1003 l_api_version,
1004 p_api_version,
1005 '_PVT',
1006 x_return_status);
1007
1008
1009 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1010 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1012 RAISE OKE_API.G_EXCEPTION_ERROR;
1013 END IF;
1014
1015
1016 IF p_pool_rec.funding_pool_id = OKE_API.G_MISS_NUM THEN
1017 OKE_API.SET_MESSAGE(
1018 p_app_name =>g_app_name,
1019 p_msg_name =>g_invalid_value,
1020 p_token1 =>g_col_name_token,
1021 p_token1_value =>'funding_pool_id');
1022 --dbms_output.put_line('must provide funding_pool_id');
1023 RAISE OKE_API.G_EXCEPTION_ERROR;
1024 END IF;
1025
1026 OPEN l_csr_id;
1027 FETCH l_csr_id INTO l_temp;
1028 IF l_csr_id%NOTFOUND THEN
1029 OKE_API.SET_MESSAGE(
1030 p_app_name =>g_app_name,
1031 p_msg_name =>g_invalid_value,
1032 p_token1 =>g_col_name_token,
1033 p_token1_value =>'funding_pool_id');
1034 --dbms_output.put_line('must provide valid funding_pool_id');
1035 RAISE OKE_API.G_EXCEPTION_ERROR;
1036 END IF;
1037 CLOSE l_csr_id;
1038
1039 l_return_status := Set_Attributes(
1040 p_pool_rec, -- IN
1041 l_pool_rec); -- OUT
1042
1043 --- If any errors happen abort API
1044 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1045 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1046 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1047 RAISE OKE_API.G_EXCEPTION_ERROR;
1048 END IF;
1049
1050 --oke_debug.debug('attributes set');
1051
1052
1053 -- check if user is trying to update attributes
1054 -- that should not be updated
1055
1056 If( l_pool_rec.CURRENCY_CODE <> OKE_API.G_MISS_CHAR) Then
1057 OKE_API.SET_MESSAGE(
1058 p_app_name =>g_app_name,
1059 p_msg_name =>G_INVALID_VALUE,
1060 p_token1 => g_col_name_token,
1061 p_token1_value=>'CURRENCY_CODE');
1062 raise OKE_API.G_EXCEPTION_ERROR;
1063 End If;
1064
1065
1066
1067 l_return_status := populate_new_record(l_pool_rec, l_def_pool_rec);
1068
1069 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1070 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1071 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1072 RAISE OKE_API.G_EXCEPTION_ERROR;
1073 END IF;
1074
1075 --oke_debug.debug('record populated');
1076
1077 l_def_pool_rec := fill_who_columns(l_def_pool_rec);
1078
1079 --oke_debug.debug('who column filled');
1080
1081 --- Validate all non-missing attributes (Item Level Validation)
1082 l_return_status := Validate_Attributes(l_def_pool_rec);
1083
1084 --- If any errors happen abort API
1085 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1086 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1087 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1088 RAISE OKE_API.G_EXCEPTION_ERROR;
1089 END IF;
1090
1091 --oke_debug.debug('attributes validated');
1092
1093 l_return_status := Validate_Record(l_def_pool_rec);
1094 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1095 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1096 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1097 RAISE OKE_API.G_EXCEPTION_ERROR;
1098 END IF;
1099
1100 --oke_debug.debug('update base table');
1101
1102
1103 OKE_FUNDING_POOLS_PKG.update_row(
1104 l_def_pool_rec.FUNDING_POOL_ID,
1105 l_def_pool_rec.NAME,
1106 l_def_pool_rec.DESCRIPTION,
1107 l_def_pool_rec.CONTACT_PERSON_ID,
1108 l_def_pool_rec.PROGRAM_ID,
1109
1110 l_def_pool_rec.LAST_UPDATE_DATE,
1111 l_def_pool_rec.LAST_UPDATED_BY,
1112 l_def_pool_rec.LAST_UPDATE_LOGIN,
1113
1114 l_def_pool_rec.ATTRIBUTE_CATEGORY,
1115 l_def_pool_rec.ATTRIBUTE1,
1116 l_def_pool_rec.ATTRIBUTE2,
1117 l_def_pool_rec.ATTRIBUTE3,
1118 l_def_pool_rec.ATTRIBUTE4,
1119 l_def_pool_rec.ATTRIBUTE5,
1120 l_def_pool_rec.ATTRIBUTE6,
1121 l_def_pool_rec.ATTRIBUTE7,
1122 l_def_pool_rec.ATTRIBUTE8,
1123 l_def_pool_rec.ATTRIBUTE9,
1124 l_def_pool_rec.ATTRIBUTE10,
1125 l_def_pool_rec.ATTRIBUTE11,
1126 l_def_pool_rec.ATTRIBUTE12,
1127 l_def_pool_rec.ATTRIBUTE13,
1128 l_def_pool_rec.ATTRIBUTE14,
1129 l_def_pool_rec.ATTRIBUTE15 );
1130
1131 x_pool_rec := l_def_pool_rec;
1132 --oke_debug.debug('end call oke_pool_pvt.update_row');
1133 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1134
1135 EXCEPTION
1136 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1137 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'OKE_API.G_RET_STS_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_PVT'
1145 );
1146 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1147 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OKE_API.G_RET_STS_UNEXP_ERROR',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 WHEN OTHERS THEN
1157 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1158 (
1159 l_api_name,
1160 G_PKG_NAME,
1161 'OTHERS',
1162 x_msg_count,
1163 x_msg_data,
1164 '_PVT'
1165 );
1166 END update_row; -- row level update
1167
1168
1169
1170 PROCEDURE update_row(
1171 p_api_version IN NUMBER,
1172 p_init_msg_list IN VARCHAR2 ,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2,
1176 p_pool_tbl IN pool_tbl_type,
1177 x_pool_tbl OUT NOCOPY pool_tbl_type) IS
1178
1179 l_api_version CONSTANT NUMBER := 1.0;
1180 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
1181
1182
1183 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1184 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1185 i NUMBER := 0;
1186 BEGIN
1187
1188
1189 --oke_debug.debug('start call oke_pool_pvt.update_row');
1190
1191 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1192 G_PKG_NAME,
1193 p_init_msg_list,
1194 l_api_version,
1195 p_api_version,
1196 '_PVT',
1197 x_return_status);
1198
1199 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1200 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1201 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1202 RAISE OKE_API.G_EXCEPTION_ERROR;
1203 END IF;
1204
1205 -- Make sure PL/SQL table has records in it before passing
1206 IF (p_pool_tbl.COUNT > 0) THEN
1207 i := p_pool_tbl.FIRST;
1208 LOOP
1209
1210 update_row (
1211 p_api_version => p_api_version,
1212 p_init_msg_list => OKE_API.G_FALSE,
1213 x_return_status => x_return_status,
1214 x_msg_count => x_msg_count,
1215 x_msg_data => x_msg_data,
1216 p_pool_rec => p_pool_tbl(i),
1217 x_pool_rec => x_pool_tbl(i));
1218
1219 -- store the highest degree of error
1220 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1221 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1222 l_overall_status := x_return_status;
1223 End If;
1224 End If;
1225
1226 EXIT WHEN (i = p_pool_tbl.LAST);
1227 i := p_pool_tbl.NEXT(i);
1228 END LOOP;
1229 -- return overall status
1230 x_return_status := l_overall_status;
1231 END IF;
1232 --oke_debug.debug('end call oke_pool_pvt.update_row');
1233 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1234
1235 EXCEPTION
1236 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1237 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1238 (
1239 l_api_name,
1240 G_PKG_NAME,
1241 'OKE_API.G_RET_STS_ERROR',
1242 x_msg_count,
1243 x_msg_data,
1244 '_PVT'
1245 );
1246
1247 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1248 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1249 (
1250 l_api_name,
1251 G_PKG_NAME,
1252 'OKE_API.G_RET_STS_UNEXP_ERROR',
1253 x_msg_count,
1254 x_msg_data,
1255 '_PVT'
1256 );
1257 WHEN OTHERS THEN
1258 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1259 (
1260 l_api_name,
1261 G_PKG_NAME,
1262 'OTHERS',
1263 x_msg_count,
1264 x_msg_data,
1265 '_PVT'
1266 );
1267 END update_row; -- table level update
1268
1269
1270 -- deletes by the funding_pool_id
1271
1272 PROCEDURE delete_row(
1273 p_api_version IN NUMBER,
1274 p_init_msg_list IN VARCHAR2 ,
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2,
1278 p_pool_rec IN pool_rec_type) IS
1279
1280 l_api_version CONSTANT NUMBER := 1;
1281 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1282 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1283 l_pool_rec pool_rec_type := p_pool_rec;
1284 l_temp NUMBER;
1285
1286 Cursor l_csr_id IS
1287 select funding_pool_id
1288 from oke_funding_pools
1289 where funding_pool_id=p_pool_rec.funding_pool_id;
1290
1291 BEGIN
1292 --oke_debug.debug('start call oke_pool_pvt.delete_row');
1293
1294 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1295 G_PKG_NAME,
1296 p_init_msg_list,
1297 l_api_version,
1298 p_api_version,
1299 '_PVT',
1300 x_return_status);
1301
1302
1303 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1304 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1305 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1306 RAISE OKE_API.G_EXCEPTION_ERROR;
1307 END IF;
1308
1309 IF p_pool_rec.funding_pool_id = OKE_API.G_MISS_NUM THEN
1310 OKE_API.SET_MESSAGE(
1311 p_app_name =>g_app_name,
1312 p_msg_name =>g_invalid_value,
1313 p_token1 =>g_col_name_token,
1314 p_token1_value =>'funding_pool_id');
1315 --dbms_output.put_line('must provide funding_pool_id');
1316 RAISE OKE_API.G_EXCEPTION_ERROR;
1317 END IF;
1318
1319 OPEN l_csr_id;
1320 FETCH l_csr_id INTO l_temp;
1321 IF l_csr_id%NOTFOUND THEN
1322 OKE_API.SET_MESSAGE(
1323 p_app_name =>g_app_name,
1324 p_msg_name =>g_invalid_value,
1325 p_token1 =>g_col_name_token,
1326 p_token1_value =>'funding_pool_id');
1327 --dbms_output.put_line('must provide valid funding_pool_id');
1328 RAISE OKE_API.G_EXCEPTION_ERROR;
1329 END IF;
1330 CLOSE l_csr_id;
1331
1332
1333 DELETE FROM OKE_FUNDING_POOLS
1334 WHERE FUNDING_POOL_ID = p_pool_rec.FUNDING_POOL_ID;
1335
1336 --oke_debug.debug('end call oke_pool_pvt.delete_row');
1337 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1338
1339 EXCEPTION
1340 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1341 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1342 (
1343 l_api_name,
1344 G_PKG_NAME,
1345 'OKE_API.G_RET_STS_ERROR',
1346 x_msg_count,
1347 x_msg_data,
1348 '_PVT'
1349 );
1350 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1351 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1352 (
1353 l_api_name,
1354 G_PKG_NAME,
1355 'OKE_API.G_RET_STS_UNEXP_ERROR',
1356 x_msg_count,
1357 x_msg_data,
1358 '_PVT'
1359 );
1360 WHEN OTHERS THEN
1361 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1362 (
1363 l_api_name,
1364 G_PKG_NAME,
1365 'OTHERS',
1366 x_msg_count,
1367 x_msg_data,
1368 '_PVT'
1369 );
1370 END delete_row;
1371
1372
1373 -- table level delete
1374
1375 PROCEDURE delete_row(
1376 p_api_version IN NUMBER,
1377 p_init_msg_list IN VARCHAR2 ,
1378 x_return_status OUT NOCOPY VARCHAR2,
1379 x_msg_count OUT NOCOPY NUMBER,
1380 x_msg_data OUT NOCOPY VARCHAR2,
1381 p_pool_tbl IN pool_tbl_type) IS
1382
1383 l_api_version CONSTANT NUMBER := 1;
1384 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
1385 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1386 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1387 i NUMBER := 0;
1388 BEGIN
1389 --oke_debug.debug('start call oke_pool_pvt.delete_row');
1390
1391 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1392 G_PKG_NAME,
1393 p_init_msg_list,
1394 l_api_version,
1395 p_api_version,
1396 '_PVT',
1397 x_return_status);
1398
1399
1400 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1403 RAISE OKE_API.G_EXCEPTION_ERROR;
1404 END IF;
1405
1406 -- Make sure PL/SQL table has records in it before passing
1407 IF (p_pool_tbl.COUNT > 0) THEN
1408 i := p_pool_tbl.FIRST;
1409 LOOP
1410 delete_row (
1411 p_api_version => p_api_version,
1412 p_init_msg_list => OKE_API.G_FALSE,
1413 x_return_status => x_return_status,
1414 x_msg_count => x_msg_count,
1415 x_msg_data => x_msg_data,
1416 p_pool_rec => p_pool_tbl(i));
1417
1418
1419
1420 -- store the highest degree of error
1421 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1422 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1423 l_overall_status := x_return_status;
1424 End If;
1425 End If;
1426
1427 EXIT WHEN (i = p_pool_tbl.LAST);
1428 i := p_pool_tbl.NEXT(i);
1429 END LOOP;
1430
1431 -- return overall status
1432 x_return_status := l_overall_status;
1433 END IF;
1434 --oke_debug.debug('end call oke_pool_pvt.delete_row');
1435 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1436
1437 EXCEPTION
1438 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1439 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1440 (
1441 l_api_name,
1442 G_PKG_NAME,
1443 'OKE_API.G_RET_STS_ERROR',
1444 x_msg_count,
1445 x_msg_data,
1446 '_PVT'
1447 );
1448 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1449 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1450 (
1451 l_api_name,
1452 G_PKG_NAME,
1453 'OKE_API.G_RET_STS_UNEXP_ERROR',
1454 x_msg_count,
1455 x_msg_data,
1456 '_PVT'
1457 );
1458 WHEN OTHERS THEN
1459 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1460 (
1461 l_api_name,
1462 G_PKG_NAME,
1463 'OTHERS',
1464 x_msg_count,
1465 x_msg_data,
1466 '_PVT'
1467 );
1468 END delete_row; -- table level delete
1469
1470
1471 PROCEDURE lock_row(
1472 p_api_version IN NUMBER,
1473 p_init_msg_list IN VARCHAR2 ,
1474 x_return_status OUT NOCOPY VARCHAR2,
1475 x_msg_count OUT NOCOPY NUMBER,
1476 x_msg_data OUT NOCOPY VARCHAR2,
1477 p_pool_rec IN pool_rec_type) IS
1478
1479
1480 l_api_version CONSTANT NUMBER := 1;
1481 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1482 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1483 l_row_notfound BOOLEAN := FALSE;
1484
1485 l_funding_pool_id NUMBER;
1486
1487 E_Resource_Busy EXCEPTION;
1488 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1489
1490
1491 CURSOR lock_csr (p IN pool_rec_type) IS
1492 SELECT funding_pool_id FROM oke_funding_pools a
1493 WHERE
1494 a.funding_pool_id = p.funding_pool_id
1495 FOR UPDATE NOWAIT;
1496
1497
1498 BEGIN
1499 --oke_debug.debug('start call oke_pool_pvt.lock_row');
1500
1501 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1502 G_PKG_NAME,
1503 p_init_msg_list,
1504 l_api_version,
1505 p_api_version,
1506 '_PVT',
1507 x_return_status);
1508
1509
1510 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1511 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1512 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1513 RAISE OKE_API.G_EXCEPTION_ERROR;
1514 END IF;
1515
1516
1517 BEGIN
1518 OPEN lock_csr(p_pool_rec);
1519 FETCH lock_csr INTO l_funding_pool_id;
1520 l_row_notfound := lock_csr%NOTFOUND;
1521 CLOSE lock_csr;
1522
1523
1524 EXCEPTION
1525 WHEN E_Resource_Busy THEN
1526 IF (lock_csr%ISOPEN) THEN
1527 CLOSE lock_csr;
1528 END IF;
1529 OKE_API.set_message(G_APP_NAME,G_FORM_UNABLE_TO_RESERVE_REC);
1530 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1531 END;
1532
1533
1534 IF (l_row_notfound) THEN
1535 OKE_API.set_message(G_APP_NAME,G_FORM_RECORD_DELETED);
1536 RAISE OKE_API.G_EXCEPTION_ERROR;
1537 END IF;
1538
1539 --oke_debug.debug('end call oke_pool_pvt.lock_row');
1540 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1541
1542 EXCEPTION
1543 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1544 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1545 (
1546 l_api_name,
1547 G_PKG_NAME,
1548 'OKE_API.G_RET_STS_ERROR',
1549 x_msg_count,
1550 x_msg_data,
1551 '_PVT'
1552 );
1553 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1554 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1555 (
1556 l_api_name,
1557 G_PKG_NAME,
1558 'OKE_API.G_RET_STS_UNEXP_ERROR',
1559 x_msg_count,
1560 x_msg_data,
1561 '_PVT'
1562 );
1563 WHEN OTHERS THEN
1564 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1565 (
1566 l_api_name,
1567 G_PKG_NAME,
1568 'OTHERS',
1569 x_msg_count,
1570 x_msg_data,
1571 '_PVT'
1572 );
1573 END lock_row;
1574
1575
1576 END OKE_POOL_PVT;
1577