[Home] [Help]
PACKAGE BODY: APPS.OKL_SIP_PVT
Source
1 PACKAGE BODY OKL_SIP_PVT AS
2 /* $Header: OKLSSIPB.pls 120.1 2005/07/01 20:30:32 cklee noship $ */
3 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
4 ---------------------------------------------------------------------------
5 -- PROCEDURE load_error_tbl
6 ---------------------------------------------------------------------------
7 PROCEDURE load_error_tbl (
8 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
9 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
10
11 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
12 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
13 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
14 BEGIN
15 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
16 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
17 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
18 -- automatically increments the index by 1, (making it 2), however, when the GET function
19 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
20 -- message 2. To circumvent this problem, check the amount of messages and compensate.
21 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
22 -- will only update the index variable when 1 and only 1 message is on the stack.
23 IF (last_msg_idx = 1) THEN
24 l_msg_idx := FND_MSG_PUB.G_FIRST;
25 END IF;
26 LOOP
27 fnd_msg_pub.get(
28 p_msg_index => l_msg_idx,
29 p_encoded => fnd_api.g_false,
30 p_data => px_error_rec.msg_data,
31 p_msg_index_out => px_error_rec.msg_count);
32 px_error_tbl(j) := px_error_rec;
33 j := j + 1;
34 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
35 END LOOP;
36 END load_error_tbl;
37 ---------------------------------------------------------------------------
38 -- FUNCTION find_highest_exception
39 ---------------------------------------------------------------------------
40 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
41 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
42 FUNCTION find_highest_exception(
43 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
44 ) RETURN VARCHAR2 IS
45 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
46 i INTEGER := 1;
47 BEGIN
48 IF (p_error_tbl.COUNT > 0) THEN
49 i := p_error_tbl.FIRST;
50 LOOP
51 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
52 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
53 l_return_status := p_error_tbl(i).error_type;
54 END IF;
55 END IF;
56 EXIT WHEN (i = p_error_tbl.LAST);
57 i := p_error_tbl.NEXT(i);
58 END LOOP;
59 END IF;
60 RETURN(l_return_status);
61 END find_highest_exception;
62 ---------------------------------------------------------------------------
63 -- FUNCTION get_seq_id
64 ---------------------------------------------------------------------------
65 FUNCTION get_seq_id RETURN NUMBER IS
66 BEGIN
67 RETURN(okc_p_util.raw_to_number(sys_guid()));
68 END get_seq_id;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE qc
72 ---------------------------------------------------------------------------
73 PROCEDURE qc IS
74 BEGIN
75 null;
76 END qc;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE change_version
80 ---------------------------------------------------------------------------
81 PROCEDURE change_version IS
82 BEGIN
83 null;
84 END change_version;
85
86 ---------------------------------------------------------------------------
87 -- PROCEDURE api_copy
88 ---------------------------------------------------------------------------
89 PROCEDURE api_copy IS
90 BEGIN
91 null;
92 END api_copy;
93
94 ---------------------------------------------------------------------------
95 -- PROCEDURE add_language
96 ---------------------------------------------------------------------------
97 PROCEDURE add_language IS
98 BEGIN
99 DELETE FROM OKL_SUBSIDY_POOLS_TL T
100 WHERE NOT EXISTS (
101 SELECT NULL
102 FROM OKL_SUBSIDY_POOLS_B B
103 WHERE B.ID =T.ID
104 );
105
106 UPDATE OKL_SUBSIDY_POOLS_TL T SET(
107 SHORT_DESCRIPTION,
108 DESCRIPTION) = (SELECT
109 B.SHORT_DESCRIPTION,
110 B.DESCRIPTION
111 FROM OKL_SUBSIDY_POOLS_TL B
112 WHERE B.ID = T.ID
113 AND B.LANGUAGE = T.SOURCE_LANG)
114 WHERE ( T.ID,
115 T.LANGUAGE)
116 IN (SELECT
117 SUBT.ID,
118 SUBT.LANGUAGE
119 FROM OKL_SUBSIDY_POOLS_TL SUBB, OKL_SUBSIDY_POOLS_TL SUBT
120 WHERE SUBB.ID = SUBT.ID
121 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
122 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
123 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
124 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
125 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
126 ));
127
128 INSERT INTO OKL_SUBSIDY_POOLS_TL (
129 ID,
130 SHORT_DESCRIPTION,
131 DESCRIPTION,
132 LANGUAGE,
133 SOURCE_LANG,
134 SFWT_FLAG,
135 CREATED_BY,
136 CREATION_DATE,
137 LAST_UPDATED_BY,
138 LAST_UPDATE_DATE,
139 LAST_UPDATE_LOGIN)
140 SELECT
141 B.ID,
142 B.SHORT_DESCRIPTION,
143 B.DESCRIPTION,
144 L.LANGUAGE_CODE,
145 B.SOURCE_LANG,
146 B.SFWT_FLAG,
147 B.CREATED_BY,
148 B.CREATION_DATE,
149 B.LAST_UPDATED_BY,
150 B.LAST_UPDATE_DATE,
151 B.LAST_UPDATE_LOGIN
152 FROM OKL_SUBSIDY_POOLS_TL B, FND_LANGUAGES L
153 WHERE L.INSTALLED_FLAG IN ('I', 'B')
154 AND B.LANGUAGE = USERENV('LANG')
155 AND NOT EXISTS (
156 SELECT NULL
157 FROM OKL_SUBSIDY_POOLS_TL T
158 WHERE T.ID = B.ID
159 AND T.LANGUAGE = L.LANGUAGE_CODE
160 );
161 END add_language;
162
163 ---------------------------------------------------------------------------
164 -- FUNCTION get_rec for: OKL_SUBSIDY_POOLS_V
165 ---------------------------------------------------------------------------
166 FUNCTION get_rec (
167 p_sipv_rec IN sipv_rec_type,
168 x_no_data_found OUT NOCOPY BOOLEAN
169 ) RETURN sipv_rec_type IS
170 CURSOR okl_subsidy_pools_v_pk_csr (p_id IN NUMBER) IS
171 SELECT
172 ID,
173 OBJECT_VERSION_NUMBER,
174 SFWT_FLAG,
175 POOL_TYPE_CODE,
176 SUBSIDY_POOL_NAME,
177 SHORT_DESCRIPTION,
178 DESCRIPTION,
179 EFFECTIVE_FROM_DATE,
180 EFFECTIVE_TO_DATE,
181 CURRENCY_CODE,
182 CURRENCY_CONVERSION_TYPE,
183 DECISION_STATUS_CODE,
184 SUBSIDY_POOL_ID,
185 REPORTING_POOL_LIMIT,
186 TOTAL_BUDGETS,
187 TOTAL_SUBSIDY_AMOUNT,
188 DECISION_DATE,
189 ATTRIBUTE_CATEGORY,
190 ATTRIBUTE1,
191 ATTRIBUTE2,
192 ATTRIBUTE3,
193 ATTRIBUTE4,
194 ATTRIBUTE5,
195 ATTRIBUTE6,
196 ATTRIBUTE7,
197 ATTRIBUTE8,
198 ATTRIBUTE9,
199 ATTRIBUTE10,
200 ATTRIBUTE11,
201 ATTRIBUTE12,
202 ATTRIBUTE13,
203 ATTRIBUTE14,
204 ATTRIBUTE15,
205 CREATED_BY,
206 CREATION_DATE,
207 LAST_UPDATED_BY,
208 LAST_UPDATE_DATE,
209 LAST_UPDATE_LOGIN
210 FROM Okl_Subsidy_Pools_V
211 WHERE okl_subsidy_pools_v.id = p_id;
212 l_okl_subsidy_pools_v_pk okl_subsidy_pools_v_pk_csr%ROWTYPE;
213 l_sipv_rec sipv_rec_type;
214 BEGIN
215 x_no_data_found := TRUE;
216 -- Get current database values
217 OPEN okl_subsidy_pools_v_pk_csr (p_sipv_rec.id);
218 FETCH okl_subsidy_pools_v_pk_csr INTO
219 l_sipv_rec.id,
220 l_sipv_rec.object_version_number,
221 l_sipv_rec.sfwt_flag,
222 l_sipv_rec.pool_type_code,
223 l_sipv_rec.subsidy_pool_name,
224 l_sipv_rec.short_description,
225 l_sipv_rec.description,
226 l_sipv_rec.effective_from_date,
227 l_sipv_rec.effective_to_date,
228 l_sipv_rec.currency_code,
229 l_sipv_rec.currency_conversion_type,
230 l_sipv_rec.decision_status_code,
231 l_sipv_rec.subsidy_pool_id,
232 l_sipv_rec.reporting_pool_limit,
233 l_sipv_rec.total_budgets,
234 l_sipv_rec.total_subsidy_amount,
235 l_sipv_rec.decision_date,
236 l_sipv_rec.attribute_category,
237 l_sipv_rec.attribute1,
238 l_sipv_rec.attribute2,
239 l_sipv_rec.attribute3,
240 l_sipv_rec.attribute4,
241 l_sipv_rec.attribute5,
242 l_sipv_rec.attribute6,
243 l_sipv_rec.attribute7,
244 l_sipv_rec.attribute8,
245 l_sipv_rec.attribute9,
246 l_sipv_rec.attribute10,
247 l_sipv_rec.attribute11,
248 l_sipv_rec.attribute12,
249 l_sipv_rec.attribute13,
250 l_sipv_rec.attribute14,
251 l_sipv_rec.attribute15,
252 l_sipv_rec.created_by,
253 l_sipv_rec.creation_date,
254 l_sipv_rec.last_updated_by,
255 l_sipv_rec.last_update_date,
256 l_sipv_rec.last_update_login;
257 x_no_data_found := okl_subsidy_pools_v_pk_csr%NOTFOUND;
258 CLOSE okl_subsidy_pools_v_pk_csr;
259 RETURN(l_sipv_rec);
260 END get_rec;
261
262 ------------------------------------------------------------------
263 -- This version of get_rec sets error messages if no data found --
264 ------------------------------------------------------------------
265 FUNCTION get_rec (
266 p_sipv_rec IN sipv_rec_type,
267 x_return_status OUT NOCOPY VARCHAR2
268 ) RETURN sipv_rec_type IS
269 l_sipv_rec sipv_rec_type;
270 l_row_notfound BOOLEAN := TRUE;
271 BEGIN
272 x_return_status := OKL_API.G_RET_STS_SUCCESS;
273 l_sipv_rec := get_rec(p_sipv_rec, l_row_notfound);
274 IF (l_row_notfound) THEN
275 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
276 x_return_status := OKL_API.G_RET_STS_ERROR;
277 END IF;
278 RETURN(l_sipv_rec);
279 END get_rec;
280 -----------------------------------------------------------
281 -- So we don't have to pass an "l_row_notfound" variable --
282 -----------------------------------------------------------
283 FUNCTION get_rec (
284 p_sipv_rec IN sipv_rec_type
285 ) RETURN sipv_rec_type IS
286 l_row_not_found BOOLEAN := TRUE;
287 BEGIN
288 RETURN(get_rec(p_sipv_rec, l_row_not_found));
289 END get_rec;
290 ---------------------------------------------------------------------------
291 -- FUNCTION get_rec for: OKL_SUBSIDY_POOLS_TL
292 ---------------------------------------------------------------------------
293 FUNCTION get_rec (
294 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
295 x_no_data_found OUT NOCOPY BOOLEAN
296 ) RETURN okl_subsidy_pools_tl_rec_type IS
297 CURSOR okl_subsidy_pools_tl_pk_csr (p_id IN NUMBER,
298 p_language IN VARCHAR2) IS
299 SELECT
300 ID,
301 SHORT_DESCRIPTION,
302 DESCRIPTION,
303 LANGUAGE,
304 SOURCE_LANG,
305 SFWT_FLAG,
306 CREATED_BY,
307 CREATION_DATE,
308 LAST_UPDATED_BY,
309 LAST_UPDATE_DATE,
310 LAST_UPDATE_LOGIN
311 FROM Okl_Subsidy_Pools_Tl
312 WHERE okl_subsidy_pools_tl.id = p_id
313 AND okl_subsidy_pools_tl.language = p_language;
314 l_okl_subsidy_pools_tl_pk okl_subsidy_pools_tl_pk_csr%ROWTYPE;
315 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
316 BEGIN
317 x_no_data_found := TRUE;
318 -- Get current database values
319 OPEN okl_subsidy_pools_tl_pk_csr (p_okl_subsidy_pools_tl_rec.id,
320 p_okl_subsidy_pools_tl_rec.language);
321 FETCH okl_subsidy_pools_tl_pk_csr INTO
322 l_okl_subsidy_pools_tl_rec.id,
323 l_okl_subsidy_pools_tl_rec.short_description,
324 l_okl_subsidy_pools_tl_rec.description,
325 l_okl_subsidy_pools_tl_rec.language,
326 l_okl_subsidy_pools_tl_rec.source_lang,
327 l_okl_subsidy_pools_tl_rec.sfwt_flag,
328 l_okl_subsidy_pools_tl_rec.created_by,
329 l_okl_subsidy_pools_tl_rec.creation_date,
330 l_okl_subsidy_pools_tl_rec.last_updated_by,
331 l_okl_subsidy_pools_tl_rec.last_update_date,
332 l_okl_subsidy_pools_tl_rec.last_update_login;
333 x_no_data_found := okl_subsidy_pools_tl_pk_csr%NOTFOUND;
334 CLOSE okl_subsidy_pools_tl_pk_csr;
335 RETURN(l_okl_subsidy_pools_tl_rec);
336 END get_rec;
337
338 ------------------------------------------------------------------
339 -- This version of get_rec sets error messages if no data found --
340 ------------------------------------------------------------------
341 FUNCTION get_rec (
342 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
343 x_return_status OUT NOCOPY VARCHAR2
344 ) RETURN okl_subsidy_pools_tl_rec_type IS
345 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
346 l_row_notfound BOOLEAN := TRUE;
347 BEGIN
348 x_return_status := OKL_API.G_RET_STS_SUCCESS;
349 l_okl_subsidy_pools_tl_rec := get_rec(p_okl_subsidy_pools_tl_rec, l_row_notfound);
350 IF (l_row_notfound) THEN
351 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
352 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
353 x_return_status := OKL_API.G_RET_STS_ERROR;
354 END IF;
355 RETURN(l_okl_subsidy_pools_tl_rec);
356 END get_rec;
357 -----------------------------------------------------------
358 -- So we don't have to pass an "l_row_notfound" variable --
359 -----------------------------------------------------------
360 FUNCTION get_rec (
361 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type
362 ) RETURN okl_subsidy_pools_tl_rec_type IS
363 l_row_not_found BOOLEAN := TRUE;
364 BEGIN
365 RETURN(get_rec(p_okl_subsidy_pools_tl_rec, l_row_not_found));
366 END get_rec;
367 ---------------------------------------------------------------------------
368 -- FUNCTION get_rec for: OKL_SUBSIDY_POOLS_B
369 ---------------------------------------------------------------------------
370 FUNCTION get_rec (
371 p_sip_rec IN sip_rec_type,
372 x_no_data_found OUT NOCOPY BOOLEAN
373 ) RETURN sip_rec_type IS
374 CURSOR okl_subsidy_pools_b_pk_csr (p_id IN NUMBER) IS
375 SELECT
376 ID,
377 OBJECT_VERSION_NUMBER,
378 POOL_TYPE_CODE,
379 SUBSIDY_POOL_NAME,
380 EFFECTIVE_FROM_DATE,
381 EFFECTIVE_TO_DATE,
382 CURRENCY_CODE,
383 CURRENCY_CONVERSION_TYPE,
384 DECISION_STATUS_CODE,
385 SUBSIDY_POOL_ID,
386 REPORTING_POOL_LIMIT,
387 TOTAL_BUDGETS,
388 TOTAL_SUBSIDY_AMOUNT,
389 DECISION_DATE,
390 ATTRIBUTE_CATEGORY,
391 ATTRIBUTE1,
392 ATTRIBUTE2,
393 ATTRIBUTE3,
394 ATTRIBUTE4,
395 ATTRIBUTE5,
396 ATTRIBUTE6,
397 ATTRIBUTE7,
398 ATTRIBUTE8,
399 ATTRIBUTE9,
400 ATTRIBUTE10,
401 ATTRIBUTE11,
402 ATTRIBUTE12,
403 ATTRIBUTE13,
404 ATTRIBUTE14,
405 ATTRIBUTE15,
406 CREATED_BY,
407 CREATION_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_DATE,
410 LAST_UPDATE_LOGIN
411 FROM Okl_Subsidy_Pools_B
412 WHERE okl_subsidy_pools_b.id = p_id;
413 l_okl_subsidy_pools_b_pk okl_subsidy_pools_b_pk_csr%ROWTYPE;
414 l_sip_rec sip_rec_type;
415 BEGIN
416 x_no_data_found := TRUE;
417 -- Get current database values
418 OPEN okl_subsidy_pools_b_pk_csr (p_sip_rec.id);
419 FETCH okl_subsidy_pools_b_pk_csr INTO
420 l_sip_rec.id,
421 l_sip_rec.object_version_number,
422 l_sip_rec.pool_type_code,
423 l_sip_rec.subsidy_pool_name,
424 l_sip_rec.effective_from_date,
425 l_sip_rec.effective_to_date,
426 l_sip_rec.currency_code,
427 l_sip_rec.currency_conversion_type,
428 l_sip_rec.decision_status_code,
429 l_sip_rec.subsidy_pool_id,
430 l_sip_rec.reporting_pool_limit,
431 l_sip_rec.total_budgets,
432 l_sip_rec.total_subsidy_amount,
433 l_sip_rec.decision_date,
434 l_sip_rec.attribute_category,
435 l_sip_rec.attribute1,
436 l_sip_rec.attribute2,
437 l_sip_rec.attribute3,
438 l_sip_rec.attribute4,
439 l_sip_rec.attribute5,
440 l_sip_rec.attribute6,
441 l_sip_rec.attribute7,
442 l_sip_rec.attribute8,
443 l_sip_rec.attribute9,
444 l_sip_rec.attribute10,
445 l_sip_rec.attribute11,
446 l_sip_rec.attribute12,
447 l_sip_rec.attribute13,
448 l_sip_rec.attribute14,
449 l_sip_rec.attribute15,
450 l_sip_rec.created_by,
451 l_sip_rec.creation_date,
452 l_sip_rec.last_updated_by,
453 l_sip_rec.last_update_date,
454 l_sip_rec.last_update_login;
455 x_no_data_found := okl_subsidy_pools_b_pk_csr%NOTFOUND;
456 CLOSE okl_subsidy_pools_b_pk_csr;
457 RETURN(l_sip_rec);
458 END get_rec;
459
460 ------------------------------------------------------------------
461 -- This version of get_rec sets error messages if no data found --
462 ------------------------------------------------------------------
463 FUNCTION get_rec (
464 p_sip_rec IN sip_rec_type,
465 x_return_status OUT NOCOPY VARCHAR2
466 ) RETURN sip_rec_type IS
467 l_sip_rec sip_rec_type;
468 l_row_notfound BOOLEAN := TRUE;
469 BEGIN
470 x_return_status := OKL_API.G_RET_STS_SUCCESS;
471 l_sip_rec := get_rec(p_sip_rec, l_row_notfound);
472 IF (l_row_notfound) THEN
473 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
474 x_return_status := OKL_API.G_RET_STS_ERROR;
475 END IF;
476 RETURN(l_sip_rec);
477 END get_rec;
478 -----------------------------------------------------------
479 -- So we don't have to pass an "l_row_notfound" variable --
480 -----------------------------------------------------------
481 FUNCTION get_rec (
482 p_sip_rec IN sip_rec_type
483 ) RETURN sip_rec_type IS
484 l_row_not_found BOOLEAN := TRUE;
485 BEGIN
486 RETURN(get_rec(p_sip_rec, l_row_not_found));
487 END get_rec;
488 ---------------------------------------------------------------------------
489 -- FUNCTION null_out_defaults for: OKL_SUBSIDY_POOLS_V
490 ---------------------------------------------------------------------------
491 FUNCTION null_out_defaults (
492 p_sipv_rec IN sipv_rec_type
493 ) RETURN sipv_rec_type IS
494 l_sipv_rec sipv_rec_type := p_sipv_rec;
495 BEGIN
496 IF (l_sipv_rec.id = OKL_API.G_MISS_NUM ) THEN
497 l_sipv_rec.id := NULL;
498 END IF;
499 IF (l_sipv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
500 l_sipv_rec.object_version_number := NULL;
501 END IF;
502 IF (l_sipv_rec.sfwt_flag = OKL_API.G_MISS_CHAR ) THEN
503 l_sipv_rec.sfwt_flag := NULL;
504 END IF;
505 IF (l_sipv_rec.pool_type_code = OKL_API.G_MISS_CHAR ) THEN
506 l_sipv_rec.pool_type_code := NULL;
507 END IF;
508 IF (l_sipv_rec.subsidy_pool_name = OKL_API.G_MISS_CHAR ) THEN
509 l_sipv_rec.subsidy_pool_name := NULL;
510 END IF;
511 IF (l_sipv_rec.short_description = OKL_API.G_MISS_CHAR ) THEN
512 l_sipv_rec.short_description := NULL;
513 END IF;
514 IF (l_sipv_rec.description = OKL_API.G_MISS_CHAR ) THEN
515 l_sipv_rec.description := NULL;
516 END IF;
517 IF (l_sipv_rec.effective_from_date = OKL_API.G_MISS_DATE ) THEN
518 l_sipv_rec.effective_from_date := NULL;
519 END IF;
520 IF (l_sipv_rec.effective_to_date = OKL_API.G_MISS_DATE ) THEN
521 l_sipv_rec.effective_to_date := NULL;
522 END IF;
523 IF (l_sipv_rec.currency_code = OKL_API.G_MISS_CHAR ) THEN
524 l_sipv_rec.currency_code := NULL;
525 END IF;
526 IF (l_sipv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR ) THEN
527 l_sipv_rec.currency_conversion_type := NULL;
528 END IF;
529 IF (l_sipv_rec.decision_status_code = OKL_API.G_MISS_CHAR ) THEN
530 l_sipv_rec.decision_status_code := NULL;
531 END IF;
532 IF (l_sipv_rec.subsidy_pool_id = OKL_API.G_MISS_NUM ) THEN
533 l_sipv_rec.subsidy_pool_id := NULL;
534 END IF;
535 IF (l_sipv_rec.reporting_pool_limit = OKL_API.G_MISS_NUM ) THEN
536 l_sipv_rec.reporting_pool_limit := NULL;
537 END IF;
538 IF (l_sipv_rec.total_budgets = OKL_API.G_MISS_NUM ) THEN
539 l_sipv_rec.total_budgets := NULL;
540 END IF;
541 IF (l_sipv_rec.total_subsidy_amount = OKL_API.G_MISS_NUM ) THEN
542 l_sipv_rec.total_subsidy_amount := NULL;
543 END IF;
544 IF (l_sipv_rec.decision_date = OKL_API.G_MISS_DATE ) THEN
545 l_sipv_rec.decision_date := NULL;
546 END IF;
547 IF (l_sipv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
548 l_sipv_rec.attribute_category := NULL;
549 END IF;
550 IF (l_sipv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
551 l_sipv_rec.attribute1 := NULL;
552 END IF;
553 IF (l_sipv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
554 l_sipv_rec.attribute2 := NULL;
555 END IF;
556 IF (l_sipv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
557 l_sipv_rec.attribute3 := NULL;
558 END IF;
559 IF (l_sipv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
560 l_sipv_rec.attribute4 := NULL;
561 END IF;
562 IF (l_sipv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
563 l_sipv_rec.attribute5 := NULL;
564 END IF;
565 IF (l_sipv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
566 l_sipv_rec.attribute6 := NULL;
567 END IF;
568 IF (l_sipv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
569 l_sipv_rec.attribute7 := NULL;
570 END IF;
571 IF (l_sipv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
572 l_sipv_rec.attribute8 := NULL;
573 END IF;
574 IF (l_sipv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
575 l_sipv_rec.attribute9 := NULL;
576 END IF;
577 IF (l_sipv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
578 l_sipv_rec.attribute10 := NULL;
579 END IF;
580 IF (l_sipv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
581 l_sipv_rec.attribute11 := NULL;
582 END IF;
583 IF (l_sipv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
584 l_sipv_rec.attribute12 := NULL;
585 END IF;
586 IF (l_sipv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
587 l_sipv_rec.attribute13 := NULL;
588 END IF;
589 IF (l_sipv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
590 l_sipv_rec.attribute14 := NULL;
591 END IF;
592 IF (l_sipv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
593 l_sipv_rec.attribute15 := NULL;
594 END IF;
595 IF (l_sipv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
596 l_sipv_rec.created_by := NULL;
597 END IF;
598 IF (l_sipv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
599 l_sipv_rec.creation_date := NULL;
600 END IF;
601 IF (l_sipv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
602 l_sipv_rec.last_updated_by := NULL;
603 END IF;
604 IF (l_sipv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
605 l_sipv_rec.last_update_date := NULL;
606 END IF;
607 IF (l_sipv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
608 l_sipv_rec.last_update_login := NULL;
609 END IF;
610 RETURN(l_sipv_rec);
611 END null_out_defaults;
612 ---------------------------------
613 -- Validate_Attributes for: ID --
614 ---------------------------------
615 PROCEDURE validate_id(
616 x_return_status OUT NOCOPY VARCHAR2,
617 p_id IN NUMBER) IS
618 BEGIN
619 x_return_status := OKL_API.G_RET_STS_SUCCESS;
620 IF (p_id = OKL_API.G_MISS_NUM OR
621 p_id IS NULL)
622 THEN
623 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
624 x_return_status := OKL_API.G_RET_STS_ERROR;
625 RAISE G_EXCEPTION_HALT_VALIDATION;
626 END IF;
627 EXCEPTION
628 WHEN G_EXCEPTION_HALT_VALIDATION THEN
629 null;
630 WHEN OTHERS THEN
631 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
632 ,p_msg_name => G_UNEXPECTED_ERROR
633 ,p_token1 => G_SQLCODE_TOKEN
634 ,p_token1_value => SQLCODE
635 ,p_token2 => G_SQLERRM_TOKEN
636 ,p_token2_value => SQLERRM);
637 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
638 END validate_id;
639 ----------------------------------------------------
640 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
641 ----------------------------------------------------
642 PROCEDURE validate_object_version_number(
643 x_return_status OUT NOCOPY VARCHAR2,
644 p_object_version_number IN NUMBER) IS
645 BEGIN
646 x_return_status := OKL_API.G_RET_STS_SUCCESS;
647 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
648 p_object_version_number IS NULL)
649 THEN
650 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
651 x_return_status := OKL_API.G_RET_STS_ERROR;
652 RAISE G_EXCEPTION_HALT_VALIDATION;
653 END IF;
654 EXCEPTION
655 WHEN G_EXCEPTION_HALT_VALIDATION THEN
656 null;
657 WHEN OTHERS THEN
658 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
659 ,p_msg_name => G_UNEXPECTED_ERROR
660 ,p_token1 => G_SQLCODE_TOKEN
661 ,p_token1_value => SQLCODE
662 ,p_token2 => G_SQLERRM_TOKEN
663 ,p_token2_value => SQLERRM);
664 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
665 END validate_object_version_number;
666 ----------------------------------------
667 -- Validate_Attributes for: SFWT_FLAG --
668 ----------------------------------------
669 PROCEDURE validate_sfwt_flag(
670 x_return_status OUT NOCOPY VARCHAR2,
671 p_sfwt_flag IN VARCHAR2) IS
672 BEGIN
673 x_return_status := OKL_API.G_RET_STS_SUCCESS;
674 IF (p_sfwt_flag = OKL_API.G_MISS_CHAR OR
675 p_sfwt_flag IS NULL)
676 THEN
677 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sfwt_flag');
678 x_return_status := OKL_API.G_RET_STS_ERROR;
679 RAISE G_EXCEPTION_HALT_VALIDATION;
680 END IF;
681 EXCEPTION
682 WHEN G_EXCEPTION_HALT_VALIDATION THEN
683 null;
684 WHEN OTHERS THEN
685 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
686 ,p_msg_name => G_UNEXPECTED_ERROR
687 ,p_token1 => G_SQLCODE_TOKEN
688 ,p_token1_value => SQLCODE
689 ,p_token2 => G_SQLERRM_TOKEN
690 ,p_token2_value => SQLERRM);
691 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
692 END validate_sfwt_flag;
693 ---------------------------------------------
694 -- Validate_Attributes for: POOL_TYPE_CODE --
695 ---------------------------------------------
696 PROCEDURE validate_pool_type_code(
697 x_return_status OUT NOCOPY VARCHAR2,
698 p_pool_type_code IN VARCHAR2) IS
699 BEGIN
700 x_return_status := OKL_API.G_RET_STS_SUCCESS;
701 IF (p_pool_type_code = OKL_API.G_MISS_CHAR OR
702 p_pool_type_code IS NULL)
703 THEN
704 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'pool_type_code');
705 x_return_status := OKL_API.G_RET_STS_ERROR;
706 RAISE G_EXCEPTION_HALT_VALIDATION;
707 END IF;
708 EXCEPTION
709 WHEN G_EXCEPTION_HALT_VALIDATION THEN
710 null;
711 WHEN OTHERS THEN
712 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
713 ,p_msg_name => G_UNEXPECTED_ERROR
714 ,p_token1 => G_SQLCODE_TOKEN
715 ,p_token1_value => SQLCODE
716 ,p_token2 => G_SQLERRM_TOKEN
717 ,p_token2_value => SQLERRM);
718 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
719 END validate_pool_type_code;
720 ------------------------------------------------
721 -- Validate_Attributes for: SUBSIDY_POOL_NAME --
722 ------------------------------------------------
723 PROCEDURE validate_subsidy_pool_name(
724 x_return_status OUT NOCOPY VARCHAR2,
725 p_subsidy_pool_name IN VARCHAR2) IS
726
727 BEGIN
728 x_return_status := OKL_API.G_RET_STS_SUCCESS;
729 IF (p_subsidy_pool_name = OKL_API.G_MISS_CHAR OR
730 p_subsidy_pool_name IS NULL)
731 THEN
732 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'subsidy_pool_name');
733 x_return_status := OKL_API.G_RET_STS_ERROR;
734 RAISE G_EXCEPTION_HALT_VALIDATION;
735 END IF;
736
737 EXCEPTION
738 WHEN G_EXCEPTION_HALT_VALIDATION THEN
739 null;
740 WHEN OTHERS THEN
741 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
742 ,p_msg_name => G_UNEXPECTED_ERROR
743 ,p_token1 => G_SQLCODE_TOKEN
744 ,p_token1_value => SQLCODE
745 ,p_token2 => G_SQLERRM_TOKEN
746 ,p_token2_value => SQLERRM);
747 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
748 END validate_subsidy_pool_name;
749 ----------------------------------------------
750 -- Validate_Attributes for: NAME UNIQUENESS--
751 ----------------------------------------------
752 -- procedure for checking that subsidy pool name entered should be unique.
753 PROCEDURE validate_name_uniqueness(
754 x_return_status OUT NOCOPY VARCHAR2,
755 p_name IN VARCHAR2,
756 p_id IN NUMBER) IS
757
758 cursor c_record_exists is
759 select 1
760 from okl_subsidy_pools_b sub
761 where sub.id = p_id
762 ;
763
764 cursor c_unique_insert is
765 select 1
766 from okl_subsidy_pools_b sub
767 where sub.subsidy_pool_name = p_name
768 ;
769
770 cursor c_unique_update is
771 select 1
772 from okl_subsidy_pools_b sub
773 where sub.id <> p_id
774 and sub.subsidy_pool_name = p_name
775 ;
776
777 l_dup_row_found boolean;
778 l_row_found boolean;
779 l_dummy number;
780
781 BEGIN
782 x_return_status := OKL_API.G_RET_STS_SUCCESS;
783
784 -- 1. If it's a new ID but has not insert into DB yet
785 -- 2. If it's a g_miss_num (update mode must have ID)
786 open c_record_exists;
787 fetch c_record_exists into l_dummy;
788 l_row_found := c_record_exists%found;
789 close c_record_exists;
790
791 -- update mode
792 IF l_row_found THEN
793 open c_unique_update;
794 fetch c_unique_update into l_dummy;
795 l_dup_row_found := c_unique_update%found;
796 close c_unique_update;
797
798 ELSE -- insert mode
799 open c_unique_insert;
800 fetch c_unique_insert into l_dummy;
801 l_dup_row_found := c_unique_insert%found;
802 close c_unique_insert;
803 END IF;
804
805 IF l_dup_row_found THEN
806 -- start: July 7, 2005 cklee - Fixed error message for subisdy pool name
807 OKL_API.set_message(G_APP_NAME, G_NOT_UNIQUE, G_COL_NAME_TOKEN, 'Subsidy Pool Name');
808 -- end: July 7, 2005 cklee - Fixed error message for subisdy pool name
809 x_return_status := OKL_API.G_RET_STS_ERROR;
810 RAISE G_EXCEPTION_HALT_VALIDATION;
811 END IF;
812
813 EXCEPTION
814 WHEN G_EXCEPTION_HALT_VALIDATION THEN
815 null;
816 WHEN OTHERS THEN
817 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
818 ,p_msg_name => G_UNEXPECTED_ERROR
819 ,p_token1 => G_SQLCODE_TOKEN
820 ,p_token1_value => SQLCODE
821 ,p_token2 => G_SQLERRM_TOKEN
822 ,p_token2_value => SQLERRM);
823 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
824 END validate_name_uniqueness;
825
826 --------------------------------------------------
827 -- Validate_Attributes for: EFFECTIVE_FROM_DATE --
828 --------------------------------------------------
829 PROCEDURE validate_effective_from_date(
830 x_return_status OUT NOCOPY VARCHAR2,
831 p_sipv_rec IN sipv_rec_type) IS
832 BEGIN
833 x_return_status := OKL_API.G_RET_STS_SUCCESS;
834 IF ((p_sipv_rec.effective_from_date <> OKL_API.G_MISS_DATE) OR
835 (p_sipv_rec.effective_from_date IS NOT NULL))
836 THEN
837 -- effective to date cannot be greater than effective from date.
838 IF ((p_sipv_rec.effective_to_date is not null) AND (p_sipv_rec.effective_from_date > p_sipv_rec.effective_to_date)) THEN
839 -- Message Text: Effective to date must be greater than or equal to effective from date.
840 x_return_status := OKL_API.G_RET_STS_ERROR;
841 OKL_API.set_message( p_app_name => G_APP_NAME,
842 p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
843 RAISE G_EXCEPTION_HALT_VALIDATION;
844 END IF;
845 END IF;
846 EXCEPTION
847 WHEN G_EXCEPTION_HALT_VALIDATION THEN
848 null;
849 WHEN OTHERS THEN
850 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
851 ,p_msg_name => G_UNEXPECTED_ERROR
852 ,p_token1 => G_SQLCODE_TOKEN
853 ,p_token1_value => SQLCODE
854 ,p_token2 => G_SQLERRM_TOKEN
855 ,p_token2_value => SQLERRM);
856 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
857 END validate_effective_from_date;
858 --------------------------------------------------
859 -- Validate_Attributes for: DECISION_STATUS_CODE --
860 --------------------------------------------------
861 PROCEDURE validate_decision_status_code(
862 x_return_status OUT NOCOPY VARCHAR2,
863 p_decision_status_code IN VARCHAR2) IS
864
865 -- cursor to fetch the pool status code from lookups.
866 CURSOR c_get_pool_stat_csr IS
867 SELECT '1'
868 FROM fnd_lookups
869 WHERE lookup_type = 'OKL_SUBSIDY_POOL_STATUS'
870 AND lookup_code = p_decision_status_code ;
871 lv_pool_status okl_subsidy_pools_b.decision_status_code%TYPE;
872 BEGIN
873 x_return_status := OKL_API.G_RET_STS_SUCCESS;
874 IF(p_decision_status_code IS NOT NULL)THEN
875 OPEN c_get_pool_stat_csr; FETCH c_get_pool_stat_csr INTO lv_pool_status;
876 -- if status code passed is not found in lookups throw an error, invalid status code.
877 IF(c_get_pool_stat_csr%NOTFOUND)THEN
878 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'decision_status_code');
879 x_return_status := OKL_API.G_RET_STS_ERROR;
880 RAISE G_EXCEPTION_HALT_VALIDATION;
881 END IF;
882 CLOSE c_get_pool_stat_csr;
883 END IF;
884 EXCEPTION
885 WHEN G_EXCEPTION_HALT_VALIDATION THEN
886 null;
887 WHEN OTHERS THEN
888 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
889 ,p_msg_name => G_UNEXPECTED_ERROR
890 ,p_token1 => G_SQLCODE_TOKEN
891 ,p_token1_value => SQLCODE
892 ,p_token2 => G_SQLERRM_TOKEN
893 ,p_token2_value => SQLERRM);
894 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
895 END validate_decision_status_code;
896 --------------------------------------------------
897 -- Validate_Attributes for: REPORTING_POOL_LIMIT --
898 --------------------------------------------------
899 PROCEDURE validate_reporting_pool_limit(
900 x_return_status OUT NOCOPY VARCHAR2,
901 p_reporting_pool_limit IN NUMBER) IS
902 BEGIN
903 x_return_status := OKL_API.G_RET_STS_SUCCESS;
904 -- reporting pool limit cannot be less than zero, throw an error if it is less than zero or negative.
905 IF (p_reporting_pool_limit IS NOT NULL AND p_reporting_pool_limit <> OKL_API.G_MISS_NUM AND p_reporting_pool_limit <= 0 ) THEN
906 OKL_API.set_message( p_app_name => G_APP_NAME,
907 p_msg_name => 'OKL_AMOUNT_GREATER_THAN_ZERO');
908
909 x_return_status := OKL_API.G_RET_STS_ERROR;
910
911 RAISE G_EXCEPTION_HALT_VALIDATION;
912 END IF;
913 EXCEPTION
914 WHEN G_EXCEPTION_HALT_VALIDATION THEN
915 null;
916 WHEN OTHERS THEN
917 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
918 ,p_msg_name => G_UNEXPECTED_ERROR
919 ,p_token1 => G_SQLCODE_TOKEN
920 ,p_token1_value => SQLCODE
921 ,p_token2 => G_SQLERRM_TOKEN
922 ,p_token2_value => SQLERRM);
923 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
924 END validate_reporting_pool_limit;
925 --------------------------------------------------
926 -- Validate_Attributes for: REPORTING_POOL_ID --
927 --------------------------------------------------
928 PROCEDURE validate_reporting_pool_id(
929 x_return_status OUT NOCOPY VARCHAR2,
930 p_sipv_rec IN sipv_rec_type) IS
931
932 CURSOR c_record_exists (cp_pool_id IN okl_subsidy_pools_b.subsidy_pool_id%type) IS
933 SELECT subsidy_pool_id
934 FROM okl_subsidy_pools_v
935 WHERE id = cp_pool_id ;
936
937 CURSOR c_pool_name(cp_pool_id IN okl_subsidy_pools_b.subsidy_pool_id%type) IS
938 SELECT subsidy_pool_name
939 FROM okl_subsidy_pools_v
940 WHERE id = cp_pool_id;
941
942 l_pool_name okl_subsidy_pools_v.subsidy_pool_name%TYPE;
943 l_subsidy_pool_id okl_subsidy_pools_v.subsidy_pool_id%TYPE DEFAULT p_sipv_rec.subsidy_pool_id;
944
945 BEGIN
946
947 IF(l_subsidy_pool_id is not null) THEN
948 OPEN c_pool_name (l_subsidy_pool_id);
949 FETCH c_pool_name INTO l_pool_name;
950 CLOSE c_pool_name;
951 END IF;
952
953 x_return_status := OKL_API.G_RET_STS_SUCCESS;
954 LOOP
955 EXIT WHEN l_subsidy_pool_id is null;
956 OPEN c_record_exists (l_subsidy_pool_id);
957 FETCH c_record_exists INTO l_subsidy_pool_id;
958 IF c_record_exists%NOTFOUND THEN
959 CLOSE c_record_exists;
960 EXIT;
961 END IF;
962 CLOSE c_record_exists;
963 IF(l_subsidy_pool_id is not null AND l_subsidy_pool_id = p_sipv_rec.id ) THEN
964 OKL_API.set_message( p_app_name => G_APP_NAME,
965 p_msg_name => 'OKL_SUB_POOL_CYCLIC',
966 p_token1 => 'REP_POOL',
967 p_token1_value => l_pool_name);
968
969 x_return_status := OKL_API.G_RET_STS_ERROR;
970 RAISE G_EXCEPTION_HALT_VALIDATION;
971 END IF;
972 END LOOP;
973 EXCEPTION
974 WHEN G_EXCEPTION_HALT_VALIDATION THEN
975 null;
976 WHEN OTHERS THEN
977 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
978 ,p_msg_name => G_UNEXPECTED_ERROR
979 ,p_token1 => G_SQLCODE_TOKEN
980 ,p_token1_value => SQLCODE
981 ,p_token2 => G_SQLERRM_TOKEN
982 ,p_token2_value => SQLERRM);
983 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
984 END validate_reporting_pool_id;
985 --------------------------------------------
986 -- Validate_Attributes for: CURRENCY_CODE --
987 --------------------------------------------
988 PROCEDURE validate_currency_code(
989 x_return_status OUT NOCOPY VARCHAR2,
990 p_currency_code IN VARCHAR2) IS
991
992 BEGIN
993 x_return_status := OKL_API.G_RET_STS_SUCCESS;
994 IF (p_currency_code = OKL_API.G_MISS_CHAR OR
995 p_currency_code IS NULL)
996 THEN
997 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'currency_code');
998 x_return_status := OKL_API.G_RET_STS_ERROR;
999 RAISE G_EXCEPTION_HALT_VALIDATION;
1000 ELSE
1001 -- validate whether the currency code entered is correct or not.
1002 IF(OKL_ACCOUNTING_UTIL.validate_currency_code(p_currency_code) = OKL_API.G_FALSE) THEN
1003 --Corresponding Column value not found
1004 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'currency_code');
1005 x_return_status:= OKL_API.G_RET_STS_ERROR;
1006 RAISE G_EXCEPTION_HALT_VALIDATION;
1007 END IF;
1008 END IF;
1009
1010 EXCEPTION
1011 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1012 null;
1013 WHEN OTHERS THEN
1014 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1015 ,p_msg_name => G_UNEXPECTED_ERROR
1016 ,p_token1 => G_SQLCODE_TOKEN
1017 ,p_token1_value => SQLCODE
1018 ,p_token2 => G_SQLERRM_TOKEN
1019 ,p_token2_value => SQLERRM);
1020 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1021 END validate_currency_code;
1022 -------------------------------------------------------
1023 -- Validate_Attributes for: CURRENCY_CONVERSION_TYPE --
1024 -------------------------------------------------------
1025 PROCEDURE validate_currency_c1(
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 p_currency_conversion_type IN VARCHAR2) IS
1028 BEGIN
1029 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1030 IF (p_currency_conversion_type = OKL_API.G_MISS_CHAR OR
1031 p_currency_conversion_type IS NULL)
1032 THEN
1033 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'currency_conversion_type');
1034 x_return_status := OKL_API.G_RET_STS_ERROR;
1035 RAISE G_EXCEPTION_HALT_VALIDATION;
1036 END IF;
1037 EXCEPTION
1038 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1039 null;
1040 WHEN OTHERS THEN
1041 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1042 ,p_msg_name => G_UNEXPECTED_ERROR
1043 ,p_token1 => G_SQLCODE_TOKEN
1044 ,p_token1_value => SQLCODE
1045 ,p_token2 => G_SQLERRM_TOKEN
1046 ,p_token2_value => SQLERRM);
1047 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1048 END validate_currency_c1;
1049 ----------------------------------------------
1050 -- Validate_Attributes for: SUBSIDY_POOL_ID --
1051 ----------------------------------------------
1052 PROCEDURE validate_subsidy_pool_id(
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 p_subsidy_pool_id IN NUMBER) IS
1055
1056 -- cursor to fetch the pool type code of the pool which is
1057 -- defined as a parent pool of the current pool.
1058 CURSOR c_get_pool_type_csr IS
1059 SELECT pool_type_code
1060 FROM okl_subsidy_pools_b
1061 WHERE id = p_subsidy_pool_id;
1062 lv_pool_type okl_subsidy_pools_b.pool_type_code%TYPE;
1063 BEGIN
1064 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1065 IF(p_subsidy_pool_id IS NOT NULL)THEN
1066 OPEN c_get_pool_type_csr; FETCH c_get_pool_type_csr INTO lv_pool_type;
1067 CLOSE c_get_pool_type_csr;
1068 -- if the parent pool type is not of type "reporting" throw error.
1069 -- only reporting pools can be defined as a parent of any other pool type.
1070 IF(lv_pool_type <> 'REPORTING')THEN
1071 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'subsidy_pool_id');
1072 x_return_status := OKL_API.G_RET_STS_ERROR;
1073 RAISE G_EXCEPTION_HALT_VALIDATION;
1074 END IF;
1075 END IF;
1076 EXCEPTION
1077 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1078 null;
1079 WHEN OTHERS THEN
1080 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1081 ,p_msg_name => G_UNEXPECTED_ERROR
1082 ,p_token1 => G_SQLCODE_TOKEN
1083 ,p_token1_value => SQLCODE
1084 ,p_token2 => G_SQLERRM_TOKEN
1085 ,p_token2_value => SQLERRM);
1086 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1087 END validate_subsidy_pool_id;
1088 ---------------------------------------------------------------------------
1089 -- FUNCTION Validate_Attributes
1090 ---------------------------------------------------------------------------
1091 -------------------------------------------------
1092 -- Validate_Attributes for:OKL_SUBSIDY_POOLS_V --
1093 -------------------------------------------------
1094 FUNCTION Validate_Attributes (
1095 p_sipv_rec IN sipv_rec_type
1096 ) RETURN VARCHAR2 IS
1097 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1098 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1099 l_sipv_rec sipv_rec_type := p_sipv_rec;
1100 BEGIN
1101 -----------------------------
1102 -- Column Level Validation --
1103 -----------------------------
1104 -- ***
1105 -- id
1106 -- ***
1107 validate_id(x_return_status, p_sipv_rec.id);
1108 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1109 l_return_status := x_return_status;
1110 RAISE G_EXCEPTION_HALT_VALIDATION;
1111 END IF;
1112
1113 -- ***
1114 -- object_version_number
1115 -- ***
1116 validate_object_version_number(x_return_status, p_sipv_rec.object_version_number);
1117 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1118 l_return_status := x_return_status;
1119 RAISE G_EXCEPTION_HALT_VALIDATION;
1120 END IF;
1121
1122 -- ***
1123 -- sfwt_flag
1124 -- ***
1125 validate_sfwt_flag(x_return_status, p_sipv_rec.sfwt_flag);
1126 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1127 l_return_status := x_return_status;
1128 RAISE G_EXCEPTION_HALT_VALIDATION;
1129 END IF;
1130
1131 -- ***
1132 -- pool_type_code
1133 -- ***
1134 validate_pool_type_code(x_return_status, p_sipv_rec.pool_type_code);
1135 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1136 l_return_status := x_return_status;
1137 RAISE G_EXCEPTION_HALT_VALIDATION;
1138 END IF;
1139
1140 -- ***
1141 -- subsidy_pool_name
1142 -- ***
1143 validate_subsidy_pool_name(x_return_status, p_sipv_rec.subsidy_pool_name);
1144 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1145 l_return_status := x_return_status;
1146 RAISE G_EXCEPTION_HALT_VALIDATION;
1147 END IF;
1148
1149 -- ************
1150 -- unique name
1151 -- ************
1152 validate_name_uniqueness(x_return_status, p_sipv_rec.subsidy_pool_name, p_sipv_rec.id);
1153 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1154 l_return_status := x_return_status;
1155 RAISE G_EXCEPTION_HALT_VALIDATION;
1156 END IF;
1157
1158 -- ***
1159 -- effective_from_date
1160 -- ***
1161 validate_effective_from_date(x_return_status, l_sipv_rec);
1162 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1163 l_return_status := x_return_status;
1164 RAISE G_EXCEPTION_HALT_VALIDATION;
1165 END IF;
1166
1167 -- ***
1168 -- reporting_pool_limit
1169 -- ***
1170 validate_reporting_pool_limit(x_return_status, p_sipv_rec.reporting_pool_limit);
1171 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1172 l_return_status := x_return_status;
1173 RAISE G_EXCEPTION_HALT_VALIDATION;
1174 END IF;
1175
1176 -- ***
1177 -- reporting_pool_id
1178 -- ***
1179 validate_reporting_pool_id(x_return_status, l_sipv_rec);
1180 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1181 l_return_status := x_return_status;
1182 RAISE G_EXCEPTION_HALT_VALIDATION;
1183 END IF;
1184
1185 -- ************
1186 -- decision status code
1187 -- ************
1188 validate_decision_status_code(x_return_status, p_sipv_rec.decision_status_code);
1189 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1190 l_return_status := x_return_status;
1191 RAISE G_EXCEPTION_HALT_VALIDATION;
1192 END IF;
1193
1194 -- ***
1195 -- currency_code
1196 -- ***
1197 validate_currency_code(x_return_status, p_sipv_rec.currency_code);
1198 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1199 l_return_status := x_return_status;
1200 RAISE G_EXCEPTION_HALT_VALIDATION;
1201 END IF;
1202
1203 -- ***
1204 -- currency_conversion_type
1205 -- ***
1206 validate_currency_c1(x_return_status, p_sipv_rec.currency_conversion_type);
1207 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1208 l_return_status := x_return_status;
1209 RAISE G_EXCEPTION_HALT_VALIDATION;
1210 END IF;
1211
1212 -- ***
1213 -- subsidy_pool_id
1214 -- ***
1215 validate_subsidy_pool_id(x_return_status, p_sipv_rec.subsidy_pool_id);
1216 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1217 l_return_status := x_return_status;
1218 RAISE G_EXCEPTION_HALT_VALIDATION;
1219 END IF;
1220
1221 RETURN(l_return_status);
1222 EXCEPTION
1223 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1224 RETURN(l_return_status);
1225 WHEN OTHERS THEN
1226 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1227 ,p_msg_name => G_UNEXPECTED_ERROR
1228 ,p_token1 => G_SQLCODE_TOKEN
1229 ,p_token1_value => SQLCODE
1230 ,p_token2 => G_SQLERRM_TOKEN
1231 ,p_token2_value => SQLERRM);
1232 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1233 RETURN(l_return_status);
1234 END Validate_Attributes;
1235 ---------------------------------------------------------------------------
1236 -- PROCEDURE Validate_Record
1237 ---------------------------------------------------------------------------
1238 ---------------------------------------------
1239 -- Validate Record for:OKL_SUBSIDY_POOLS_V --
1240 ---------------------------------------------
1241 FUNCTION Validate_Record (
1242 p_sipv_rec IN sipv_rec_type,
1243 p_db_sipv_rec IN sipv_rec_type
1244 ) RETURN VARCHAR2 IS
1245 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1246 ------------------------------------
1247 -- FUNCTION validate_foreign_keys --
1248 ------------------------------------
1249 FUNCTION validate_foreign_keys (
1250 p_sipv_rec IN sipv_rec_type,
1251 p_db_sipv_rec IN sipv_rec_type
1252 ) RETURN VARCHAR2 IS
1253 item_not_found_error EXCEPTION;
1254 BEGIN
1255 l_return_status := validate_foreign_keys(p_sipv_rec, p_db_sipv_rec);
1256 RETURN (l_return_status);
1257 --avsingh
1258 --had to correct generated code manually
1259 END validate_foreign_keys;
1260 BEGIN
1261 RETURN (l_return_status);
1262 EXCEPTION
1263 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1264 l_return_status := OKL_API.G_RET_STS_ERROR;
1265 RETURN (l_return_status);
1266
1267 End validate_record;
1268
1269 FUNCTION Validate_Record (
1270 p_sipv_rec IN sipv_rec_type
1271 ) RETURN VARCHAR2 IS
1272 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1273 l_db_sipv_rec sipv_rec_type := get_rec(p_sipv_rec);
1274 BEGIN
1275 l_return_status := Validate_Record(p_sipv_rec => p_sipv_rec,
1276 p_db_sipv_rec => l_db_sipv_rec);
1277 RETURN (l_return_status);
1278 END Validate_Record;
1279
1280 ---------------------------------------------------------------------------
1281 -- PROCEDURE Migrate
1282 ---------------------------------------------------------------------------
1283 PROCEDURE migrate (
1284 p_from IN sipv_rec_type,
1285 p_to IN OUT NOCOPY okl_subsidy_pools_tl_rec_type
1286 ) IS
1287 BEGIN
1288 p_to.id := p_from.id;
1289 p_to.short_description := p_from.short_description;
1290 p_to.description := p_from.description;
1291 p_to.sfwt_flag := p_from.sfwt_flag;
1292 p_to.created_by := p_from.created_by;
1293 p_to.creation_date := p_from.creation_date;
1294 p_to.last_updated_by := p_from.last_updated_by;
1295 p_to.last_update_date := p_from.last_update_date;
1296 p_to.last_update_login := p_from.last_update_login;
1297 END migrate;
1298 PROCEDURE migrate (
1299 p_from IN okl_subsidy_pools_tl_rec_type,
1300 p_to IN OUT NOCOPY sipv_rec_type
1301 ) IS
1302 BEGIN
1303 p_to.id := p_from.id;
1304 p_to.sfwt_flag := p_from.sfwt_flag;
1305 p_to.short_description := p_from.short_description;
1306 p_to.description := p_from.description;
1307 p_to.created_by := p_from.created_by;
1308 p_to.creation_date := p_from.creation_date;
1309 p_to.last_updated_by := p_from.last_updated_by;
1310 p_to.last_update_date := p_from.last_update_date;
1311 p_to.last_update_login := p_from.last_update_login;
1312 END migrate;
1313 PROCEDURE migrate (
1314 p_from IN sipv_rec_type,
1315 p_to IN OUT NOCOPY sip_rec_type
1316 ) IS
1317 BEGIN
1318 p_to.id := p_from.id;
1319 p_to.object_version_number := p_from.object_version_number;
1320 p_to.pool_type_code := p_from.pool_type_code;
1321 p_to.subsidy_pool_name := p_from.subsidy_pool_name;
1322 p_to.effective_from_date := p_from.effective_from_date;
1323 p_to.effective_to_date := p_from.effective_to_date;
1324 p_to.currency_code := p_from.currency_code;
1325 p_to.currency_conversion_type := p_from.currency_conversion_type;
1326 p_to.decision_status_code := p_from.decision_status_code;
1327 p_to.subsidy_pool_id := p_from.subsidy_pool_id;
1328 p_to.reporting_pool_limit := p_from.reporting_pool_limit;
1329 p_to.total_budgets := p_from.total_budgets;
1330 p_to.total_subsidy_amount := p_from.total_subsidy_amount;
1331 p_to.decision_date := p_from.decision_date;
1332 p_to.attribute_category := p_from.attribute_category;
1333 p_to.attribute1 := p_from.attribute1;
1334 p_to.attribute2 := p_from.attribute2;
1335 p_to.attribute3 := p_from.attribute3;
1336 p_to.attribute4 := p_from.attribute4;
1337 p_to.attribute5 := p_from.attribute5;
1338 p_to.attribute6 := p_from.attribute6;
1339 p_to.attribute7 := p_from.attribute7;
1340 p_to.attribute8 := p_from.attribute8;
1341 p_to.attribute9 := p_from.attribute9;
1342 p_to.attribute10 := p_from.attribute10;
1343 p_to.attribute11 := p_from.attribute11;
1344 p_to.attribute12 := p_from.attribute12;
1345 p_to.attribute13 := p_from.attribute13;
1346 p_to.attribute14 := p_from.attribute14;
1347 p_to.attribute15 := p_from.attribute15;
1348 p_to.created_by := p_from.created_by;
1349 p_to.creation_date := p_from.creation_date;
1350 p_to.last_updated_by := p_from.last_updated_by;
1351 p_to.last_update_date := p_from.last_update_date;
1352 p_to.last_update_login := p_from.last_update_login;
1353 END migrate;
1354 PROCEDURE migrate (
1355 p_from IN sip_rec_type,
1356 p_to IN OUT NOCOPY sipv_rec_type
1357 ) IS
1358 BEGIN
1359 p_to.id := p_from.id;
1360 p_to.object_version_number := p_from.object_version_number;
1361 p_to.pool_type_code := p_from.pool_type_code;
1362 p_to.subsidy_pool_name := p_from.subsidy_pool_name;
1363 p_to.effective_from_date := p_from.effective_from_date;
1364 p_to.effective_to_date := p_from.effective_to_date;
1365 p_to.currency_code := p_from.currency_code;
1366 p_to.currency_conversion_type := p_from.currency_conversion_type;
1367 p_to.decision_status_code := p_from.decision_status_code;
1368 p_to.subsidy_pool_id := p_from.subsidy_pool_id;
1369 p_to.reporting_pool_limit := p_from.reporting_pool_limit;
1370 p_to.total_budgets := p_from.total_budgets;
1371 p_to.total_subsidy_amount := p_from.total_subsidy_amount;
1372 p_to.decision_date := p_from.decision_date;
1373 p_to.attribute_category := p_from.attribute_category;
1374 p_to.attribute1 := p_from.attribute1;
1375 p_to.attribute2 := p_from.attribute2;
1376 p_to.attribute3 := p_from.attribute3;
1377 p_to.attribute4 := p_from.attribute4;
1378 p_to.attribute5 := p_from.attribute5;
1379 p_to.attribute6 := p_from.attribute6;
1380 p_to.attribute7 := p_from.attribute7;
1381 p_to.attribute8 := p_from.attribute8;
1382 p_to.attribute9 := p_from.attribute9;
1383 p_to.attribute10 := p_from.attribute10;
1384 p_to.attribute11 := p_from.attribute11;
1385 p_to.attribute12 := p_from.attribute12;
1386 p_to.attribute13 := p_from.attribute13;
1387 p_to.attribute14 := p_from.attribute14;
1388 p_to.attribute15 := p_from.attribute15;
1389 p_to.created_by := p_from.created_by;
1390 p_to.creation_date := p_from.creation_date;
1391 p_to.last_updated_by := p_from.last_updated_by;
1392 p_to.last_update_date := p_from.last_update_date;
1393 p_to.last_update_login := p_from.last_update_login;
1394 END migrate;
1395 ---------------------------------------------------------------------------
1396 -- PROCEDURE validate_row
1397 ---------------------------------------------------------------------------
1398 ------------------------------------------
1399 -- validate_row for:OKL_SUBSIDY_POOLS_V --
1400 ------------------------------------------
1401 PROCEDURE validate_row(
1402 p_api_version IN NUMBER,
1403 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1404 x_return_status OUT NOCOPY VARCHAR2,
1405 x_msg_count OUT NOCOPY NUMBER,
1406 x_msg_data OUT NOCOPY VARCHAR2,
1407 p_sipv_rec IN sipv_rec_type) IS
1408
1409 l_api_version CONSTANT NUMBER := 1;
1410 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1411 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1412 l_sipv_rec sipv_rec_type := p_sipv_rec;
1413 l_sip_rec sip_rec_type;
1414 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
1415 BEGIN
1416 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1417 G_PKG_NAME,
1418 p_init_msg_list,
1419 l_api_version,
1420 p_api_version,
1421 '_PVT',
1422 x_return_status);
1423 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1424 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1425 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1426 RAISE OKL_API.G_EXCEPTION_ERROR;
1427 END IF;
1428 --- Validate all non-missing attributes (Item Level Validation)
1429 l_return_status := Validate_Attributes(l_sipv_rec);
1430 --- If any errors happen abort API
1431 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1432 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1433 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1434 RAISE OKL_API.G_EXCEPTION_ERROR;
1435 END IF;
1436 l_return_status := Validate_Record(l_sipv_rec);
1437 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1438 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1439 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1440 RAISE OKL_API.G_EXCEPTION_ERROR;
1441 END IF;
1442 x_return_status := l_return_status;
1443 EXCEPTION
1444 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1445 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1446 (
1447 l_api_name,
1448 G_PKG_NAME,
1449 'OKL_API.G_RET_STS_ERROR',
1450 x_msg_count,
1451 x_msg_data,
1452 '_PVT'
1453 );
1454 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1455 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1456 (
1457 l_api_name,
1458 G_PKG_NAME,
1459 'OKL_API.G_RET_STS_UNEXP_ERROR',
1460 x_msg_count,
1461 x_msg_data,
1462 '_PVT'
1463 );
1464 WHEN OTHERS THEN
1465 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1466 (
1467 l_api_name,
1468 G_PKG_NAME,
1469 'OTHERS',
1470 x_msg_count,
1471 x_msg_data,
1472 '_PVT'
1473 );
1474 END validate_row;
1475 -----------------------------------------------------
1476 -- PL/SQL TBL validate_row for:OKL_SUBSIDY_POOLS_V --
1477 -----------------------------------------------------
1478 PROCEDURE validate_row(
1479 p_api_version IN NUMBER,
1480 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_count OUT NOCOPY NUMBER,
1483 x_msg_data OUT NOCOPY VARCHAR2,
1484 p_sipv_tbl IN sipv_tbl_type,
1485 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1486
1487 l_api_version CONSTANT NUMBER := 1;
1488 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1489 i NUMBER := 0;
1490 BEGIN
1491 OKL_API.init_msg_list(p_init_msg_list);
1492 -- Make sure PL/SQL table has records in it before passing
1493 IF (p_sipv_tbl.COUNT > 0) THEN
1494 i := p_sipv_tbl.FIRST;
1495 LOOP
1496 DECLARE
1497 l_error_rec OKL_API.ERROR_REC_TYPE;
1498 BEGIN
1499 l_error_rec.api_name := l_api_name;
1500 l_error_rec.api_package := G_PKG_NAME;
1501 l_error_rec.idx := i;
1502 validate_row (
1503 p_api_version => p_api_version,
1504 p_init_msg_list => OKL_API.G_FALSE,
1505 x_return_status => l_error_rec.error_type,
1506 x_msg_count => l_error_rec.msg_count,
1507 x_msg_data => l_error_rec.msg_data,
1508 p_sipv_rec => p_sipv_tbl(i));
1509 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1510 l_error_rec.sqlcode := SQLCODE;
1511 load_error_tbl(l_error_rec, px_error_tbl);
1512 ELSE
1513 x_msg_count := l_error_rec.msg_count;
1514 x_msg_data := l_error_rec.msg_data;
1515 END IF;
1516 EXCEPTION
1517 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1518 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1519 l_error_rec.sqlcode := SQLCODE;
1520 load_error_tbl(l_error_rec, px_error_tbl);
1521 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1522 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1523 l_error_rec.sqlcode := SQLCODE;
1524 load_error_tbl(l_error_rec, px_error_tbl);
1525 WHEN OTHERS THEN
1526 l_error_rec.error_type := 'OTHERS';
1527 l_error_rec.sqlcode := SQLCODE;
1528 load_error_tbl(l_error_rec, px_error_tbl);
1529 END;
1530 EXIT WHEN (i = p_sipv_tbl.LAST);
1531 i := p_sipv_tbl.NEXT(i);
1532 END LOOP;
1533 END IF;
1534 -- Loop through the error_tbl to find the error with the highest severity
1535 -- and return it.
1536 x_return_status := find_highest_exception(px_error_tbl);
1537 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1538 EXCEPTION
1539 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1540 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1541 (
1542 l_api_name,
1543 G_PKG_NAME,
1544 'OKL_API.G_RET_STS_ERROR',
1545 x_msg_count,
1546 x_msg_data,
1547 '_PVT'
1548 );
1549 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1550 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1551 (
1552 l_api_name,
1553 G_PKG_NAME,
1554 'OKL_API.G_RET_STS_UNEXP_ERROR',
1555 x_msg_count,
1556 x_msg_data,
1557 '_PVT'
1558 );
1559 WHEN OTHERS THEN
1560 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1561 (
1562 l_api_name,
1563 G_PKG_NAME,
1564 'OTHERS',
1565 x_msg_count,
1566 x_msg_data,
1567 '_PVT'
1568 );
1569 END validate_row;
1570
1571 -----------------------------------------------------
1572 -- PL/SQL TBL validate_row for:OKL_SUBSIDY_POOLS_V --
1573 -----------------------------------------------------
1574 PROCEDURE validate_row(
1575 p_api_version IN NUMBER,
1576 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1577 x_return_status OUT NOCOPY VARCHAR2,
1578 x_msg_count OUT NOCOPY NUMBER,
1579 x_msg_data OUT NOCOPY VARCHAR2,
1580 p_sipv_tbl IN sipv_tbl_type) IS
1581
1582 l_api_version CONSTANT NUMBER := 1;
1583 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1584 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1585 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1586 BEGIN
1587 OKL_API.init_msg_list(p_init_msg_list);
1588 -- Make sure PL/SQL table has records in it before passing
1589 IF (p_sipv_tbl.COUNT > 0) THEN
1590 validate_row (
1591 p_api_version => p_api_version,
1592 p_init_msg_list => OKL_API.G_FALSE,
1593 x_return_status => x_return_status,
1594 x_msg_count => x_msg_count,
1595 x_msg_data => x_msg_data,
1596 p_sipv_tbl => p_sipv_tbl,
1597 px_error_tbl => l_error_tbl);
1598 END IF;
1599 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1600 EXCEPTION
1601 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1602 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OKL_API.G_RET_STS_ERROR',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT'
1610 );
1611 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1612 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1613 (
1614 l_api_name,
1615 G_PKG_NAME,
1616 'OKL_API.G_RET_STS_UNEXP_ERROR',
1617 x_msg_count,
1618 x_msg_data,
1619 '_PVT'
1620 );
1621 WHEN OTHERS THEN
1622 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1623 (
1624 l_api_name,
1625 G_PKG_NAME,
1626 'OTHERS',
1627 x_msg_count,
1628 x_msg_data,
1629 '_PVT'
1630 );
1631 END validate_row;
1632
1633 ---------------------------------------------------------------------------
1634 -- PROCEDURE insert_row
1635 ---------------------------------------------------------------------------
1636 ----------------------------------------
1637 -- insert_row for:OKL_SUBSIDY_POOLS_B --
1638 ----------------------------------------
1639 PROCEDURE insert_row(
1640 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1641 x_return_status OUT NOCOPY VARCHAR2,
1642 x_msg_count OUT NOCOPY NUMBER,
1643 x_msg_data OUT NOCOPY VARCHAR2,
1644 p_sip_rec IN sip_rec_type,
1645 x_sip_rec OUT NOCOPY sip_rec_type) IS
1646
1647 l_api_version CONSTANT NUMBER := 1;
1648 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1649 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1650 l_sip_rec sip_rec_type := p_sip_rec;
1651 l_def_sip_rec sip_rec_type;
1652 --------------------------------------------
1653 -- Set_Attributes for:OKL_SUBSIDY_POOLS_B --
1654 --------------------------------------------
1655 FUNCTION Set_Attributes (
1656 p_sip_rec IN sip_rec_type,
1657 x_sip_rec OUT NOCOPY sip_rec_type
1658 ) RETURN VARCHAR2 IS
1659 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1660 BEGIN
1661 x_sip_rec := p_sip_rec;
1662 RETURN(l_return_status);
1663 END Set_Attributes;
1664 BEGIN
1665 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1666 p_init_msg_list,
1667 '_PVT',
1668 x_return_status);
1669 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1670 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1671 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1672 RAISE OKL_API.G_EXCEPTION_ERROR;
1673 END IF;
1674 --- Setting item atributes
1675 l_return_status := Set_Attributes(
1676 p_sip_rec, -- IN
1677 l_sip_rec); -- OUT
1678 --- If any errors happen abort API
1679 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1680 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1681 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1682 RAISE OKL_API.G_EXCEPTION_ERROR;
1683 END IF;
1684 INSERT INTO OKL_SUBSIDY_POOLS_B(
1685 id,
1686 object_version_number,
1687 pool_type_code,
1688 subsidy_pool_name,
1689 effective_from_date,
1690 effective_to_date,
1691 currency_code,
1692 currency_conversion_type,
1693 decision_status_code,
1694 subsidy_pool_id,
1695 reporting_pool_limit,
1696 total_budgets,
1697 total_subsidy_amount,
1698 decision_date,
1699 attribute_category,
1700 attribute1,
1701 attribute2,
1702 attribute3,
1703 attribute4,
1704 attribute5,
1705 attribute6,
1706 attribute7,
1707 attribute8,
1708 attribute9,
1709 attribute10,
1710 attribute11,
1711 attribute12,
1712 attribute13,
1713 attribute14,
1714 attribute15,
1715 created_by,
1716 creation_date,
1717 last_updated_by,
1718 last_update_date,
1719 last_update_login)
1720 VALUES (
1721 l_sip_rec.id,
1722 l_sip_rec.object_version_number,
1723 l_sip_rec.pool_type_code,
1724 l_sip_rec.subsidy_pool_name,
1725 l_sip_rec.effective_from_date,
1726 l_sip_rec.effective_to_date,
1727 l_sip_rec.currency_code,
1728 l_sip_rec.currency_conversion_type,
1729 l_sip_rec.decision_status_code,
1730 l_sip_rec.subsidy_pool_id,
1731 l_sip_rec.reporting_pool_limit,
1732 l_sip_rec.total_budgets,
1733 l_sip_rec.total_subsidy_amount,
1734 l_sip_rec.decision_date,
1735 l_sip_rec.attribute_category,
1736 l_sip_rec.attribute1,
1737 l_sip_rec.attribute2,
1738 l_sip_rec.attribute3,
1739 l_sip_rec.attribute4,
1740 l_sip_rec.attribute5,
1741 l_sip_rec.attribute6,
1742 l_sip_rec.attribute7,
1743 l_sip_rec.attribute8,
1744 l_sip_rec.attribute9,
1745 l_sip_rec.attribute10,
1746 l_sip_rec.attribute11,
1747 l_sip_rec.attribute12,
1748 l_sip_rec.attribute13,
1749 l_sip_rec.attribute14,
1750 l_sip_rec.attribute15,
1751 l_sip_rec.created_by,
1752 l_sip_rec.creation_date,
1753 l_sip_rec.last_updated_by,
1754 l_sip_rec.last_update_date,
1755 l_sip_rec.last_update_login);
1756 -- Set OUT values
1757 x_sip_rec := l_sip_rec;
1758 x_return_status := l_return_status;
1759 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1760 EXCEPTION
1761 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1762 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1763 (
1764 l_api_name,
1765 G_PKG_NAME,
1766 'OKL_API.G_RET_STS_ERROR',
1767 x_msg_count,
1768 x_msg_data,
1769 '_PVT'
1770 );
1771 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1772 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1773 (
1774 l_api_name,
1775 G_PKG_NAME,
1776 'OKL_API.G_RET_STS_UNEXP_ERROR',
1777 x_msg_count,
1778 x_msg_data,
1779 '_PVT'
1780 );
1781 WHEN OTHERS THEN
1782 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1783 (
1784 l_api_name,
1785 G_PKG_NAME,
1786 'OTHERS',
1787 x_msg_count,
1788 x_msg_data,
1789 '_PVT'
1790 );
1791 END insert_row;
1792 -----------------------------------------
1793 -- insert_row for:OKL_SUBSIDY_POOLS_TL --
1794 -----------------------------------------
1795 PROCEDURE insert_row(
1796 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1797 x_return_status OUT NOCOPY VARCHAR2,
1798 x_msg_count OUT NOCOPY NUMBER,
1799 x_msg_data OUT NOCOPY VARCHAR2,
1800 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
1801 x_okl_subsidy_pools_tl_rec OUT NOCOPY okl_subsidy_pools_tl_rec_type) IS
1802
1803 l_api_version CONSTANT NUMBER := 1;
1804 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1805 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1806 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type := p_okl_subsidy_pools_tl_rec;
1807 l_def_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
1808 CURSOR get_languages IS
1809 SELECT *
1810 FROM FND_LANGUAGES
1811 WHERE INSTALLED_FLAG IN ('I', 'B');
1812 ---------------------------------------------
1813 -- Set_Attributes for:OKL_SUBSIDY_POOLS_TL --
1814 ---------------------------------------------
1815 FUNCTION Set_Attributes (
1816 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
1817 x_okl_subsidy_pools_tl_rec OUT NOCOPY okl_subsidy_pools_tl_rec_type
1818 ) RETURN VARCHAR2 IS
1819 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1820 BEGIN
1821 x_okl_subsidy_pools_tl_rec := p_okl_subsidy_pools_tl_rec;
1822 x_okl_subsidy_pools_tl_rec.LANGUAGE := USERENV('LANG');
1823 x_okl_subsidy_pools_tl_rec.SOURCE_LANG := USERENV('LANG');
1824 RETURN(l_return_status);
1825 END Set_Attributes;
1826 BEGIN
1827 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1828 p_init_msg_list,
1829 '_PVT',
1830 x_return_status);
1831 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1832 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1833 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1834 RAISE OKL_API.G_EXCEPTION_ERROR;
1835 END IF;
1836 --- Setting item attributes
1837 l_return_status := Set_Attributes(
1838 p_okl_subsidy_pools_tl_rec, -- IN
1839 l_okl_subsidy_pools_tl_rec); -- OUT
1840 --- If any errors happen abort API
1841 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1843 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1844 RAISE OKL_API.G_EXCEPTION_ERROR;
1845 END IF;
1846 FOR l_lang_rec IN get_languages LOOP
1847 l_okl_subsidy_pools_tl_rec.language := l_lang_rec.language_code;
1848 INSERT INTO OKL_SUBSIDY_POOLS_TL(
1849 id,
1850 short_description,
1851 description,
1852 language,
1853 source_lang,
1854 sfwt_flag,
1855 created_by,
1856 creation_date,
1857 last_updated_by,
1858 last_update_date,
1859 last_update_login)
1860 VALUES (
1861 l_okl_subsidy_pools_tl_rec.id,
1862 l_okl_subsidy_pools_tl_rec.short_description,
1863 l_okl_subsidy_pools_tl_rec.description,
1864 l_okl_subsidy_pools_tl_rec.language,
1865 l_okl_subsidy_pools_tl_rec.source_lang,
1866 l_okl_subsidy_pools_tl_rec.sfwt_flag,
1867 l_okl_subsidy_pools_tl_rec.created_by,
1868 l_okl_subsidy_pools_tl_rec.creation_date,
1869 l_okl_subsidy_pools_tl_rec.last_updated_by,
1870 l_okl_subsidy_pools_tl_rec.last_update_date,
1871 l_okl_subsidy_pools_tl_rec.last_update_login);
1872 END LOOP;
1873 -- Set OUT values
1874 x_okl_subsidy_pools_tl_rec := l_okl_subsidy_pools_tl_rec;
1875 x_return_status := l_return_status;
1876 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1877 EXCEPTION
1878 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1879 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1880 (
1881 l_api_name,
1882 G_PKG_NAME,
1883 'OKL_API.G_RET_STS_ERROR',
1884 x_msg_count,
1885 x_msg_data,
1886 '_PVT'
1887 );
1888 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1889 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1890 (
1891 l_api_name,
1892 G_PKG_NAME,
1893 'OKL_API.G_RET_STS_UNEXP_ERROR',
1894 x_msg_count,
1895 x_msg_data,
1896 '_PVT'
1897 );
1898 WHEN OTHERS THEN
1899 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1900 (
1901 l_api_name,
1902 G_PKG_NAME,
1903 'OTHERS',
1904 x_msg_count,
1905 x_msg_data,
1906 '_PVT'
1907 );
1908 END insert_row;
1909 -----------------------------------------
1910 -- insert_row for :OKL_SUBSIDY_POOLS_V --
1911 -----------------------------------------
1912 PROCEDURE insert_row(
1913 p_api_version IN NUMBER,
1914 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1915 x_return_status OUT NOCOPY VARCHAR2,
1916 x_msg_count OUT NOCOPY NUMBER,
1917 x_msg_data OUT NOCOPY VARCHAR2,
1918 p_sipv_rec IN sipv_rec_type,
1919 x_sipv_rec OUT NOCOPY sipv_rec_type) IS
1920
1921 l_api_version CONSTANT NUMBER := 1;
1922 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1923 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1924 l_sipv_rec sipv_rec_type := p_sipv_rec;
1925 l_def_sipv_rec sipv_rec_type;
1926 l_sip_rec sip_rec_type;
1927 lx_sip_rec sip_rec_type;
1928 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
1929 lx_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
1930 -------------------------------
1931 -- FUNCTION fill_who_columns --
1932 -------------------------------
1933 FUNCTION fill_who_columns (
1934 p_sipv_rec IN sipv_rec_type
1935 ) RETURN sipv_rec_type IS
1936 l_sipv_rec sipv_rec_type := p_sipv_rec;
1937 BEGIN
1938 l_sipv_rec.CREATION_DATE := SYSDATE;
1939 l_sipv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1940 l_sipv_rec.LAST_UPDATE_DATE := l_sipv_rec.CREATION_DATE;
1941 l_sipv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1942 l_sipv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1943 RETURN(l_sipv_rec);
1944 END fill_who_columns;
1945 --------------------------------------------
1946 -- Set_Attributes for:OKL_SUBSIDY_POOLS_V --
1947 --------------------------------------------
1948 FUNCTION Set_Attributes (
1949 p_sipv_rec IN sipv_rec_type,
1950 x_sipv_rec OUT NOCOPY sipv_rec_type
1951 ) RETURN VARCHAR2 IS
1952 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1953 BEGIN
1954 x_sipv_rec := p_sipv_rec;
1955 x_sipv_rec.OBJECT_VERSION_NUMBER := 1;
1956 x_sipv_rec.SFWT_FLAG := 'N';
1957 RETURN(l_return_status);
1958 END Set_Attributes;
1959 BEGIN
1960 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1961 G_PKG_NAME,
1962 p_init_msg_list,
1963 l_api_version,
1964 p_api_version,
1965 '_PVT',
1966 x_return_status);
1967 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1968 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1969 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1970 RAISE OKL_API.G_EXCEPTION_ERROR;
1971 END IF;
1972 l_sipv_rec := null_out_defaults(p_sipv_rec);
1973 -- Set primary key value
1974 l_sipv_rec.ID := get_seq_id;
1975 -- Setting item attributes
1976 l_return_Status := Set_Attributes(
1977 l_sipv_rec, -- IN
1978 l_def_sipv_rec); -- OUT
1979 --- If any errors happen abort API
1980 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1981 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1982 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1983 RAISE OKL_API.G_EXCEPTION_ERROR;
1984 END IF;
1985 l_def_sipv_rec := fill_who_columns(l_def_sipv_rec);
1986 --- Validate all non-missing attributes (Item Level Validation)
1987 l_return_status := Validate_Attributes(l_def_sipv_rec);
1988 --- If any errors happen abort API
1989 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1990 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1991 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1992 RAISE OKL_API.G_EXCEPTION_ERROR;
1993 END IF;
1994 l_return_status := Validate_Record(l_def_sipv_rec);
1995 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1997 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1998 RAISE OKL_API.G_EXCEPTION_ERROR;
1999 END IF;
2000 -----------------------------------------
2001 -- Move VIEW record to "Child" records --
2002 -----------------------------------------
2003 migrate(l_def_sipv_rec, l_sip_rec);
2004 migrate(l_def_sipv_rec, l_okl_subsidy_pools_tl_rec);
2005 -----------------------------------------------
2006 -- Call the INSERT_ROW for each child record --
2007 -----------------------------------------------
2008 insert_row(
2009 p_init_msg_list,
2010 l_return_status,
2011 x_msg_count,
2012 x_msg_data,
2013 l_sip_rec,
2014 lx_sip_rec
2015 );
2016 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2017 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2018 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2019 RAISE OKL_API.G_EXCEPTION_ERROR;
2020 END IF;
2021 migrate(lx_sip_rec, l_def_sipv_rec);
2022 insert_row(
2023 p_init_msg_list,
2024 l_return_status,
2025 x_msg_count,
2026 x_msg_data,
2027 l_okl_subsidy_pools_tl_rec,
2028 lx_okl_subsidy_pools_tl_rec
2029 );
2030 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2031 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2032 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2033 RAISE OKL_API.G_EXCEPTION_ERROR;
2034 END IF;
2035 migrate(lx_okl_subsidy_pools_tl_rec, l_def_sipv_rec);
2036 -- Set OUT values
2037 x_sipv_rec := l_def_sipv_rec;
2038 x_return_status := l_return_status;
2039 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2040 EXCEPTION
2041 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2042 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2043 (
2044 l_api_name,
2045 G_PKG_NAME,
2046 'OKL_API.G_RET_STS_ERROR',
2047 x_msg_count,
2048 x_msg_data,
2049 '_PVT'
2050 );
2051 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2052 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2053 (
2054 l_api_name,
2055 G_PKG_NAME,
2056 'OKL_API.G_RET_STS_UNEXP_ERROR',
2057 x_msg_count,
2058 x_msg_data,
2059 '_PVT'
2060 );
2061 WHEN OTHERS THEN
2062 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2063 (
2064 l_api_name,
2065 G_PKG_NAME,
2066 'OTHERS',
2067 x_msg_count,
2068 x_msg_data,
2069 '_PVT'
2070 );
2071 END insert_row;
2072 ----------------------------------------
2073 -- PL/SQL TBL insert_row for:SIPV_TBL --
2074 ----------------------------------------
2075 PROCEDURE insert_row(
2076 p_api_version IN NUMBER,
2077 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2078 x_return_status OUT NOCOPY VARCHAR2,
2079 x_msg_count OUT NOCOPY NUMBER,
2080 x_msg_data OUT NOCOPY VARCHAR2,
2081 p_sipv_tbl IN sipv_tbl_type,
2082 x_sipv_tbl OUT NOCOPY sipv_tbl_type,
2083 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2084
2085 l_api_version CONSTANT NUMBER := 1;
2086 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
2087 i NUMBER := 0;
2088 BEGIN
2089 OKL_API.init_msg_list(p_init_msg_list);
2090 -- Make sure PL/SQL table has records in it before passing
2091 IF (p_sipv_tbl.COUNT > 0) THEN
2092 i := p_sipv_tbl.FIRST;
2093 LOOP
2094 DECLARE
2095 l_error_rec OKL_API.ERROR_REC_TYPE;
2096 BEGIN
2097 l_error_rec.api_name := l_api_name;
2098 l_error_rec.api_package := G_PKG_NAME;
2099 l_error_rec.idx := i;
2100 insert_row (
2101 p_api_version => p_api_version,
2102 p_init_msg_list => OKL_API.G_FALSE,
2103 x_return_status => l_error_rec.error_type,
2104 x_msg_count => l_error_rec.msg_count,
2105 x_msg_data => l_error_rec.msg_data,
2106 p_sipv_rec => p_sipv_tbl(i),
2107 x_sipv_rec => x_sipv_tbl(i));
2108 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2109 l_error_rec.sqlcode := SQLCODE;
2110 load_error_tbl(l_error_rec, px_error_tbl);
2111 ELSE
2112 x_msg_count := l_error_rec.msg_count;
2113 x_msg_data := l_error_rec.msg_data;
2114 END IF;
2115 EXCEPTION
2116 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2117 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2118 l_error_rec.sqlcode := SQLCODE;
2119 load_error_tbl(l_error_rec, px_error_tbl);
2120 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2121 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2122 l_error_rec.sqlcode := SQLCODE;
2123 load_error_tbl(l_error_rec, px_error_tbl);
2124 WHEN OTHERS THEN
2125 l_error_rec.error_type := 'OTHERS';
2126 l_error_rec.sqlcode := SQLCODE;
2127 load_error_tbl(l_error_rec, px_error_tbl);
2128 END;
2129 EXIT WHEN (i = p_sipv_tbl.LAST);
2130 i := p_sipv_tbl.NEXT(i);
2131 END LOOP;
2132 END IF;
2133 -- Loop through the error_tbl to find the error with the highest severity
2134 -- and return it.
2135 x_return_status := find_highest_exception(px_error_tbl);
2136 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2137 EXCEPTION
2138 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2139 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2140 (
2141 l_api_name,
2142 G_PKG_NAME,
2143 'OKL_API.G_RET_STS_ERROR',
2144 x_msg_count,
2145 x_msg_data,
2146 '_PVT'
2147 );
2148 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2149 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2150 (
2151 l_api_name,
2152 G_PKG_NAME,
2153 'OKL_API.G_RET_STS_UNEXP_ERROR',
2154 x_msg_count,
2155 x_msg_data,
2156 '_PVT'
2157 );
2158 WHEN OTHERS THEN
2159 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2160 (
2161 l_api_name,
2162 G_PKG_NAME,
2163 'OTHERS',
2164 x_msg_count,
2165 x_msg_data,
2166 '_PVT'
2167 );
2168 END insert_row;
2169
2170 ----------------------------------------
2171 -- PL/SQL TBL insert_row for:SIPV_TBL --
2172 ----------------------------------------
2173 PROCEDURE insert_row(
2174 p_api_version IN NUMBER,
2175 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2176 x_return_status OUT NOCOPY VARCHAR2,
2177 x_msg_count OUT NOCOPY NUMBER,
2178 x_msg_data OUT NOCOPY VARCHAR2,
2179 p_sipv_tbl IN sipv_tbl_type,
2180 x_sipv_tbl OUT NOCOPY sipv_tbl_type) IS
2181
2182 l_api_version CONSTANT NUMBER := 1;
2183 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2184 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2185 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2186 BEGIN
2187 OKL_API.init_msg_list(p_init_msg_list);
2188 -- Make sure PL/SQL table has records in it before passing
2189 IF (p_sipv_tbl.COUNT > 0) THEN
2190 insert_row (
2191 p_api_version => p_api_version,
2192 p_init_msg_list => OKL_API.G_FALSE,
2193 x_return_status => x_return_status,
2194 x_msg_count => x_msg_count,
2195 x_msg_data => x_msg_data,
2196 p_sipv_tbl => p_sipv_tbl,
2197 x_sipv_tbl => x_sipv_tbl,
2198 px_error_tbl => l_error_tbl);
2199 END IF;
2200 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2201 EXCEPTION
2202 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2203 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2204 (
2205 l_api_name,
2206 G_PKG_NAME,
2207 'OKL_API.G_RET_STS_ERROR',
2208 x_msg_count,
2209 x_msg_data,
2210 '_PVT'
2211 );
2212 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2213 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2214 (
2215 l_api_name,
2216 G_PKG_NAME,
2217 'OKL_API.G_RET_STS_UNEXP_ERROR',
2218 x_msg_count,
2219 x_msg_data,
2220 '_PVT'
2221 );
2222 WHEN OTHERS THEN
2223 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2224 (
2225 l_api_name,
2226 G_PKG_NAME,
2227 'OTHERS',
2228 x_msg_count,
2229 x_msg_data,
2230 '_PVT'
2231 );
2232 END insert_row;
2233
2234 ---------------------------------------------------------------------------
2235 -- PROCEDURE lock_row
2236 ---------------------------------------------------------------------------
2237 --------------------------------------
2238 -- lock_row for:OKL_SUBSIDY_POOLS_B --
2239 --------------------------------------
2240 PROCEDURE lock_row(
2241 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2242 x_return_status OUT NOCOPY VARCHAR2,
2243 x_msg_count OUT NOCOPY NUMBER,
2244 x_msg_data OUT NOCOPY VARCHAR2,
2245 p_sip_rec IN sip_rec_type) IS
2246
2247 E_Resource_Busy EXCEPTION;
2248 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2249 CURSOR lock_csr (p_sip_rec IN sip_rec_type) IS
2250 SELECT OBJECT_VERSION_NUMBER
2251 FROM OKL_SUBSIDY_POOLS_B
2252 WHERE ID = p_sip_rec.id
2253 AND OBJECT_VERSION_NUMBER = p_sip_rec.object_version_number
2254 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2255
2256 CURSOR lchk_csr (p_sip_rec IN sip_rec_type) IS
2257 SELECT OBJECT_VERSION_NUMBER
2258 FROM OKL_SUBSIDY_POOLS_B
2259 WHERE ID = p_sip_rec.id;
2260 l_api_version CONSTANT NUMBER := 1;
2261 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2262 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2263 l_object_version_number OKL_SUBSIDY_POOLS_B.OBJECT_VERSION_NUMBER%TYPE;
2264 lc_object_version_number OKL_SUBSIDY_POOLS_B.OBJECT_VERSION_NUMBER%TYPE;
2265 l_row_notfound BOOLEAN := FALSE;
2266 lc_row_notfound BOOLEAN := FALSE;
2267 BEGIN
2268 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2269 p_init_msg_list,
2270 '_PVT',
2271 x_return_status);
2272 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2273 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2274 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2275 RAISE OKL_API.G_EXCEPTION_ERROR;
2276 END IF;
2277 BEGIN
2278 OPEN lock_csr(p_sip_rec);
2279 FETCH lock_csr INTO l_object_version_number;
2280 l_row_notfound := lock_csr%NOTFOUND;
2281 CLOSE lock_csr;
2282 EXCEPTION
2283 WHEN E_Resource_Busy THEN
2284 IF (lock_csr%ISOPEN) THEN
2285 CLOSE lock_csr;
2286 END IF;
2287 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2288 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2289 END;
2290
2291 IF ( l_row_notfound ) THEN
2292 OPEN lchk_csr(p_sip_rec);
2293 FETCH lchk_csr INTO lc_object_version_number;
2294 lc_row_notfound := lchk_csr%NOTFOUND;
2295 CLOSE lchk_csr;
2296 END IF;
2297 IF (lc_row_notfound) THEN
2298 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2299 RAISE OKL_API.G_EXCEPTION_ERROR;
2300 ELSIF lc_object_version_number > p_sip_rec.object_version_number THEN
2301 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2302 RAISE OKL_API.G_EXCEPTION_ERROR;
2303 ELSIF lc_object_version_number <> p_sip_rec.object_version_number THEN
2304 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2305 RAISE OKL_API.G_EXCEPTION_ERROR;
2306 ELSIF lc_object_version_number = -1 THEN
2307 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2308 RAISE OKL_API.G_EXCEPTION_ERROR;
2309 END IF;
2310 x_return_status := l_return_status;
2311 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2312 EXCEPTION
2313 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2314 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2315 (
2316 l_api_name,
2317 G_PKG_NAME,
2318 'OKL_API.G_RET_STS_ERROR',
2319 x_msg_count,
2320 x_msg_data,
2321 '_PVT'
2322 );
2323 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2324 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2325 (
2326 l_api_name,
2327 G_PKG_NAME,
2328 'OKL_API.G_RET_STS_UNEXP_ERROR',
2329 x_msg_count,
2330 x_msg_data,
2331 '_PVT'
2332 );
2333 WHEN OTHERS THEN
2334 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2335 (
2336 l_api_name,
2337 G_PKG_NAME,
2338 'OTHERS',
2339 x_msg_count,
2340 x_msg_data,
2341 '_PVT'
2342 );
2343 END lock_row;
2344 ---------------------------------------
2345 -- lock_row for:OKL_SUBSIDY_POOLS_TL --
2346 ---------------------------------------
2347 PROCEDURE lock_row(
2348 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2349 x_return_status OUT NOCOPY VARCHAR2,
2350 x_msg_count OUT NOCOPY NUMBER,
2351 x_msg_data OUT NOCOPY VARCHAR2,
2352 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type) IS
2353
2354 E_Resource_Busy EXCEPTION;
2355 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2356 CURSOR lock_csr (p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type) IS
2357 SELECT *
2358 FROM OKL_SUBSIDY_POOLS_TL
2359 WHERE ID = p_okl_subsidy_pools_tl_rec.id
2360 FOR UPDATE NOWAIT;
2361
2362 l_api_version CONSTANT NUMBER := 1;
2363 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2364 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2365 l_lock_var lock_csr%ROWTYPE;
2366 l_row_notfound BOOLEAN := FALSE;
2367 lc_row_notfound BOOLEAN := FALSE;
2368 BEGIN
2369 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2370 p_init_msg_list,
2371 '_PVT',
2372 x_return_status);
2373 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2374 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2375 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2376 RAISE OKL_API.G_EXCEPTION_ERROR;
2377 END IF;
2378 BEGIN
2379 OPEN lock_csr(p_okl_subsidy_pools_tl_rec);
2380 FETCH lock_csr INTO l_lock_var;
2381 l_row_notfound := lock_csr%NOTFOUND;
2382 CLOSE lock_csr;
2383 EXCEPTION
2384 WHEN E_Resource_Busy THEN
2385 IF (lock_csr%ISOPEN) THEN
2386 CLOSE lock_csr;
2387 END IF;
2388 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2389 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2390 END;
2391
2392 IF ( l_row_notfound ) THEN
2393 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2394 RAISE OKL_API.G_EXCEPTION_ERROR;
2395 END IF;
2396 x_return_status := l_return_status;
2397 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2398 EXCEPTION
2399 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2400 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2401 (
2402 l_api_name,
2403 G_PKG_NAME,
2404 'OKL_API.G_RET_STS_ERROR',
2405 x_msg_count,
2406 x_msg_data,
2407 '_PVT'
2408 );
2409 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2410 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2411 (
2412 l_api_name,
2413 G_PKG_NAME,
2414 'OKL_API.G_RET_STS_UNEXP_ERROR',
2415 x_msg_count,
2416 x_msg_data,
2417 '_PVT'
2418 );
2419 WHEN OTHERS THEN
2420 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2421 (
2422 l_api_name,
2423 G_PKG_NAME,
2424 'OTHERS',
2425 x_msg_count,
2426 x_msg_data,
2427 '_PVT'
2428 );
2429 END lock_row;
2430 ---------------------------------------
2431 -- lock_row for: OKL_SUBSIDY_POOLS_V --
2432 ---------------------------------------
2433 PROCEDURE lock_row(
2434 p_api_version IN NUMBER,
2435 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2436 x_return_status OUT NOCOPY VARCHAR2,
2437 x_msg_count OUT NOCOPY NUMBER,
2438 x_msg_data OUT NOCOPY VARCHAR2,
2439 p_sipv_rec IN sipv_rec_type) IS
2440
2441 l_api_version CONSTANT NUMBER := 1;
2442 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2443 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2444 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
2445 l_sip_rec sip_rec_type;
2446 BEGIN
2447 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2448 G_PKG_NAME,
2449 p_init_msg_list,
2450 l_api_version,
2451 p_api_version,
2452 '_PVT',
2453 x_return_status);
2454 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2455 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2456 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2457 RAISE OKL_API.G_EXCEPTION_ERROR;
2458 END IF;
2459 -----------------------------------------
2460 -- Move VIEW record to "Child" records --
2461 -----------------------------------------
2462 migrate(p_sipv_rec, l_okl_subsidy_pools_tl_rec);
2463 migrate(p_sipv_rec, l_sip_rec);
2464 ---------------------------------------------
2465 -- Call the LOCK_ROW for each child record --
2466 ---------------------------------------------
2467 lock_row(
2468 p_init_msg_list,
2469 l_return_status,
2470 x_msg_count,
2471 x_msg_data,
2472 l_okl_subsidy_pools_tl_rec
2473 );
2474 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2476 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2477 RAISE OKL_API.G_EXCEPTION_ERROR;
2478 END IF;
2479 lock_row(
2480 p_init_msg_list,
2481 l_return_status,
2482 x_msg_count,
2483 x_msg_data,
2484 l_sip_rec
2485 );
2486 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2487 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2488 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2489 RAISE OKL_API.G_EXCEPTION_ERROR;
2490 END IF;
2491 x_return_status := l_return_status;
2492 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2493 EXCEPTION
2494 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2495 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2496 (
2497 l_api_name,
2498 G_PKG_NAME,
2499 'OKL_API.G_RET_STS_ERROR',
2500 x_msg_count,
2501 x_msg_data,
2502 '_PVT'
2503 );
2504 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2505 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2506 (
2507 l_api_name,
2508 G_PKG_NAME,
2509 'OKL_API.G_RET_STS_UNEXP_ERROR',
2510 x_msg_count,
2511 x_msg_data,
2512 '_PVT'
2513 );
2514 WHEN OTHERS THEN
2515 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2516 (
2517 l_api_name,
2518 G_PKG_NAME,
2519 'OTHERS',
2520 x_msg_count,
2521 x_msg_data,
2522 '_PVT'
2523 );
2524 END lock_row;
2525 --------------------------------------
2526 -- PL/SQL TBL lock_row for:SIPV_TBL --
2527 --------------------------------------
2528 PROCEDURE lock_row(
2529 p_api_version IN NUMBER,
2530 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2531 x_return_status OUT NOCOPY VARCHAR2,
2532 x_msg_count OUT NOCOPY NUMBER,
2533 x_msg_data OUT NOCOPY VARCHAR2,
2534 p_sipv_tbl IN sipv_tbl_type,
2535 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2536
2537 l_api_version CONSTANT NUMBER := 1;
2538 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2539 i NUMBER := 0;
2540 BEGIN
2541 OKL_API.init_msg_list(p_init_msg_list);
2542 -- Make sure PL/SQL table has recrods in it before passing
2543 IF (p_sipv_tbl.COUNT > 0) THEN
2544 i := p_sipv_tbl.FIRST;
2545 LOOP
2546 DECLARE
2547 l_error_rec OKL_API.ERROR_REC_TYPE;
2548 BEGIN
2549 l_error_rec.api_name := l_api_name;
2550 l_error_rec.api_package := G_PKG_NAME;
2551 l_error_rec.idx := i;
2552 lock_row(
2553 p_api_version => p_api_version,
2554 p_init_msg_list => OKL_API.G_FALSE,
2555 x_return_status => l_error_rec.error_type,
2556 x_msg_count => l_error_rec.msg_count,
2557 x_msg_data => l_error_rec.msg_data,
2558 p_sipv_rec => p_sipv_tbl(i));
2559 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2560 l_error_rec.sqlcode := SQLCODE;
2561 load_error_tbl(l_error_rec, px_error_tbl);
2562 ELSE
2563 x_msg_count := l_error_rec.msg_count;
2564 x_msg_data := l_error_rec.msg_data;
2565 END IF;
2566 EXCEPTION
2567 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2568 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2569 l_error_rec.sqlcode := SQLCODE;
2570 load_error_tbl(l_error_rec, px_error_tbl);
2571 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2572 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2573 l_error_rec.sqlcode := SQLCODE;
2574 load_error_tbl(l_error_rec, px_error_tbl);
2575 WHEN OTHERS THEN
2576 l_error_rec.error_type := 'OTHERS';
2577 l_error_rec.sqlcode := SQLCODE;
2578 load_error_tbl(l_error_rec, px_error_tbl);
2579 END;
2580 EXIT WHEN (i = p_sipv_tbl.LAST);
2581 i := p_sipv_tbl.NEXT(i);
2582 END LOOP;
2583 END IF;
2584 -- Loop through the error_tbl to find the error with the highest severity
2585 -- and return it.
2586 x_return_status := find_highest_exception(px_error_tbl);
2587 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2588 EXCEPTION
2589 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2590 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2591 (
2592 l_api_name,
2593 G_PKG_NAME,
2594 'OKL_API.G_RET_STS_ERROR',
2595 x_msg_count,
2596 x_msg_data,
2597 '_PVT'
2598 );
2599 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2600 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2601 (
2602 l_api_name,
2603 G_PKG_NAME,
2604 'OKL_API.G_RET_STS_UNEXP_ERROR',
2605 x_msg_count,
2606 x_msg_data,
2607 '_PVT'
2608 );
2609 WHEN OTHERS THEN
2610 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2611 (
2612 l_api_name,
2613 G_PKG_NAME,
2614 'OTHERS',
2615 x_msg_count,
2616 x_msg_data,
2617 '_PVT'
2618 );
2619 END lock_row;
2620 --------------------------------------
2621 -- PL/SQL TBL lock_row for:SIPV_TBL --
2622 --------------------------------------
2623 PROCEDURE lock_row(
2624 p_api_version IN NUMBER,
2625 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2626 x_return_status OUT NOCOPY VARCHAR2,
2627 x_msg_count OUT NOCOPY NUMBER,
2628 x_msg_data OUT NOCOPY VARCHAR2,
2629 p_sipv_tbl IN sipv_tbl_type) IS
2630
2631 l_api_version CONSTANT NUMBER := 1;
2632 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2633 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2634 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2635 BEGIN
2636 OKL_API.init_msg_list(p_init_msg_list);
2637 -- Make sure PL/SQL table has recrods in it before passing
2638 IF (p_sipv_tbl.COUNT > 0) THEN
2639 lock_row(
2640 p_api_version => p_api_version,
2641 p_init_msg_list => OKL_API.G_FALSE,
2642 x_return_status => x_return_status,
2643 x_msg_count => x_msg_count,
2644 x_msg_data => x_msg_data,
2645 p_sipv_tbl => p_sipv_tbl,
2646 px_error_tbl => l_error_tbl);
2647 END IF;
2648 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2649 EXCEPTION
2650 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2651 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2652 (
2653 l_api_name,
2654 G_PKG_NAME,
2655 'OKL_API.G_RET_STS_ERROR',
2656 x_msg_count,
2657 x_msg_data,
2658 '_PVT'
2659 );
2660 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2661 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2662 (
2663 l_api_name,
2664 G_PKG_NAME,
2665 'OKL_API.G_RET_STS_UNEXP_ERROR',
2666 x_msg_count,
2667 x_msg_data,
2668 '_PVT'
2669 );
2670 WHEN OTHERS THEN
2671 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2672 (
2673 l_api_name,
2674 G_PKG_NAME,
2675 'OTHERS',
2676 x_msg_count,
2677 x_msg_data,
2678 '_PVT'
2679 );
2680 END lock_row;
2681 ---------------------------------------------------------------------------
2682 -- PROCEDURE update_row
2683 ---------------------------------------------------------------------------
2684 ----------------------------------------
2685 -- update_row for:OKL_SUBSIDY_POOLS_B --
2686 ----------------------------------------
2687 PROCEDURE update_row(
2688 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2689 x_return_status OUT NOCOPY VARCHAR2,
2690 x_msg_count OUT NOCOPY NUMBER,
2691 x_msg_data OUT NOCOPY VARCHAR2,
2692 p_sip_rec IN sip_rec_type,
2693 x_sip_rec OUT NOCOPY sip_rec_type) IS
2694
2695 l_api_version CONSTANT NUMBER := 1;
2696 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2697 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2698 l_sip_rec sip_rec_type := p_sip_rec;
2699 l_def_sip_rec sip_rec_type;
2700 l_row_notfound BOOLEAN := TRUE;
2701 ----------------------------------
2702 -- FUNCTION populate_new_record --
2703 ----------------------------------
2704 FUNCTION populate_new_record (
2705 p_sip_rec IN sip_rec_type,
2706 x_sip_rec OUT NOCOPY sip_rec_type
2707 ) RETURN VARCHAR2 IS
2708 l_sip_rec sip_rec_type;
2709 l_row_notfound BOOLEAN := TRUE;
2710 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2711 BEGIN
2712 x_sip_rec := p_sip_rec;
2713 -- Get current database values
2714 l_sip_rec := get_rec(p_sip_rec, l_return_status);
2715 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2716 IF (x_sip_rec.id = OKL_API.G_MISS_NUM)
2717 THEN
2718 x_sip_rec.id := l_sip_rec.id;
2719 END IF;
2720 IF (x_sip_rec.object_version_number = OKL_API.G_MISS_NUM)
2721 THEN
2722 x_sip_rec.object_version_number := l_sip_rec.object_version_number;
2723 END IF;
2724 IF (x_sip_rec.pool_type_code = OKL_API.G_MISS_CHAR)
2725 THEN
2726 x_sip_rec.pool_type_code := l_sip_rec.pool_type_code;
2727 END IF;
2728 IF (x_sip_rec.subsidy_pool_name = OKL_API.G_MISS_CHAR)
2729 THEN
2730 x_sip_rec.subsidy_pool_name := l_sip_rec.subsidy_pool_name;
2731 END IF;
2732 IF (x_sip_rec.effective_from_date = OKL_API.G_MISS_DATE)
2733 THEN
2734 x_sip_rec.effective_from_date := l_sip_rec.effective_from_date;
2735 END IF;
2736 IF (x_sip_rec.effective_to_date = OKL_API.G_MISS_DATE)
2737 THEN
2738 x_sip_rec.effective_to_date := l_sip_rec.effective_to_date;
2739 END IF;
2740 IF (x_sip_rec.currency_code = OKL_API.G_MISS_CHAR)
2741 THEN
2742 x_sip_rec.currency_code := l_sip_rec.currency_code;
2743 END IF;
2744 IF (x_sip_rec.currency_conversion_type = OKL_API.G_MISS_CHAR)
2745 THEN
2746 x_sip_rec.currency_conversion_type := l_sip_rec.currency_conversion_type;
2747 END IF;
2748 IF (x_sip_rec.decision_status_code = OKL_API.G_MISS_CHAR)
2749 THEN
2750 x_sip_rec.decision_status_code := l_sip_rec.decision_status_code;
2751 END IF;
2752 IF (x_sip_rec.subsidy_pool_id = OKL_API.G_MISS_NUM)
2753 THEN
2754 x_sip_rec.subsidy_pool_id := l_sip_rec.subsidy_pool_id;
2755 END IF;
2756 IF (x_sip_rec.reporting_pool_limit = OKL_API.G_MISS_NUM)
2757 THEN
2758 x_sip_rec.reporting_pool_limit := l_sip_rec.reporting_pool_limit;
2759 END IF;
2760 IF (x_sip_rec.total_budgets = OKL_API.G_MISS_NUM)
2761 THEN
2762 x_sip_rec.total_budgets := l_sip_rec.total_budgets;
2763 END IF;
2764 IF (x_sip_rec.total_subsidy_amount = OKL_API.G_MISS_NUM)
2765 THEN
2766 x_sip_rec.total_subsidy_amount := l_sip_rec.total_subsidy_amount;
2767 END IF;
2768 IF (x_sip_rec.decision_date = OKL_API.G_MISS_DATE)
2769 THEN
2770 x_sip_rec.decision_date := l_sip_rec.decision_date;
2771 END IF;
2772 IF (x_sip_rec.attribute_category = OKL_API.G_MISS_CHAR)
2773 THEN
2774 x_sip_rec.attribute_category := l_sip_rec.attribute_category;
2775 END IF;
2776 IF (x_sip_rec.attribute1 = OKL_API.G_MISS_CHAR)
2777 THEN
2778 x_sip_rec.attribute1 := l_sip_rec.attribute1;
2779 END IF;
2780 IF (x_sip_rec.attribute2 = OKL_API.G_MISS_CHAR)
2781 THEN
2782 x_sip_rec.attribute2 := l_sip_rec.attribute2;
2783 END IF;
2784 IF (x_sip_rec.attribute3 = OKL_API.G_MISS_CHAR)
2785 THEN
2786 x_sip_rec.attribute3 := l_sip_rec.attribute3;
2787 END IF;
2788 IF (x_sip_rec.attribute4 = OKL_API.G_MISS_CHAR)
2789 THEN
2790 x_sip_rec.attribute4 := l_sip_rec.attribute4;
2791 END IF;
2792 IF (x_sip_rec.attribute5 = OKL_API.G_MISS_CHAR)
2793 THEN
2794 x_sip_rec.attribute5 := l_sip_rec.attribute5;
2795 END IF;
2796 IF (x_sip_rec.attribute6 = OKL_API.G_MISS_CHAR)
2797 THEN
2798 x_sip_rec.attribute6 := l_sip_rec.attribute6;
2799 END IF;
2800 IF (x_sip_rec.attribute7 = OKL_API.G_MISS_CHAR)
2801 THEN
2802 x_sip_rec.attribute7 := l_sip_rec.attribute7;
2803 END IF;
2804 IF (x_sip_rec.attribute8 = OKL_API.G_MISS_CHAR)
2805 THEN
2806 x_sip_rec.attribute8 := l_sip_rec.attribute8;
2807 END IF;
2808 IF (x_sip_rec.attribute9 = OKL_API.G_MISS_CHAR)
2809 THEN
2810 x_sip_rec.attribute9 := l_sip_rec.attribute9;
2811 END IF;
2812 IF (x_sip_rec.attribute10 = OKL_API.G_MISS_CHAR)
2813 THEN
2814 x_sip_rec.attribute10 := l_sip_rec.attribute10;
2815 END IF;
2816 IF (x_sip_rec.attribute11 = OKL_API.G_MISS_CHAR)
2817 THEN
2818 x_sip_rec.attribute11 := l_sip_rec.attribute11;
2819 END IF;
2820 IF (x_sip_rec.attribute12 = OKL_API.G_MISS_CHAR)
2821 THEN
2822 x_sip_rec.attribute12 := l_sip_rec.attribute12;
2823 END IF;
2824 IF (x_sip_rec.attribute13 = OKL_API.G_MISS_CHAR)
2825 THEN
2826 x_sip_rec.attribute13 := l_sip_rec.attribute13;
2827 END IF;
2828 IF (x_sip_rec.attribute14 = OKL_API.G_MISS_CHAR)
2829 THEN
2830 x_sip_rec.attribute14 := l_sip_rec.attribute14;
2831 END IF;
2832 IF (x_sip_rec.attribute15 = OKL_API.G_MISS_CHAR)
2833 THEN
2834 x_sip_rec.attribute15 := l_sip_rec.attribute15;
2835 END IF;
2836 IF (x_sip_rec.created_by = OKL_API.G_MISS_NUM)
2837 THEN
2838 x_sip_rec.created_by := l_sip_rec.created_by;
2839 END IF;
2840 IF (x_sip_rec.creation_date = OKL_API.G_MISS_DATE)
2841 THEN
2842 x_sip_rec.creation_date := l_sip_rec.creation_date;
2843 END IF;
2844 IF (x_sip_rec.last_updated_by = OKL_API.G_MISS_NUM)
2845 THEN
2846 x_sip_rec.last_updated_by := l_sip_rec.last_updated_by;
2847 END IF;
2848 IF (x_sip_rec.last_update_date = OKL_API.G_MISS_DATE)
2849 THEN
2850 x_sip_rec.last_update_date := l_sip_rec.last_update_date;
2851 END IF;
2852 IF (x_sip_rec.last_update_login = OKL_API.G_MISS_NUM)
2853 THEN
2854 x_sip_rec.last_update_login := l_sip_rec.last_update_login;
2855 END IF;
2856 END IF;
2857 RETURN(l_return_status);
2858 END populate_new_record;
2859 --------------------------------------------
2860 -- Set_Attributes for:OKL_SUBSIDY_POOLS_B --
2861 --------------------------------------------
2862 FUNCTION Set_Attributes (
2863 p_sip_rec IN sip_rec_type,
2864 x_sip_rec OUT NOCOPY sip_rec_type
2865 ) RETURN VARCHAR2 IS
2866 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2867 BEGIN
2868 x_sip_rec := p_sip_rec;
2869 x_sip_rec.OBJECT_VERSION_NUMBER := p_sip_rec.OBJECT_VERSION_NUMBER + 1;
2870 RETURN(l_return_status);
2871 END Set_Attributes;
2872 BEGIN
2873 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2874 p_init_msg_list,
2875 '_PVT',
2876 x_return_status);
2877 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2878 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2879 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2880 RAISE OKL_API.G_EXCEPTION_ERROR;
2881 END IF;
2882 --- Setting item attributes
2883 l_return_status := Set_Attributes(
2884 p_sip_rec, -- IN
2885 l_sip_rec); -- OUT
2886 --- If any errors happen abort API
2887 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2888 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2889 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2890 RAISE OKL_API.G_EXCEPTION_ERROR;
2891 END IF;
2892 l_return_status := populate_new_record(l_sip_rec, l_def_sip_rec);
2893 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2894 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2895 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2896 RAISE OKL_API.G_EXCEPTION_ERROR;
2897 END IF;
2898 UPDATE OKL_SUBSIDY_POOLS_B
2899 SET OBJECT_VERSION_NUMBER = l_def_sip_rec.object_version_number,
2900 POOL_TYPE_CODE = l_def_sip_rec.pool_type_code,
2901 SUBSIDY_POOL_NAME = l_def_sip_rec.subsidy_pool_name,
2902 EFFECTIVE_FROM_DATE = l_def_sip_rec.effective_from_date,
2903 EFFECTIVE_TO_DATE = l_def_sip_rec.effective_to_date,
2904 CURRENCY_CODE = l_def_sip_rec.currency_code,
2905 CURRENCY_CONVERSION_TYPE = l_def_sip_rec.currency_conversion_type,
2906 DECISION_STATUS_CODE = l_def_sip_rec.decision_status_code,
2907 SUBSIDY_POOL_ID = l_def_sip_rec.subsidy_pool_id,
2908 REPORTING_POOL_LIMIT = l_def_sip_rec.reporting_pool_limit,
2909 TOTAL_BUDGETS = l_def_sip_rec.total_budgets,
2910 TOTAL_SUBSIDY_AMOUNT = l_def_sip_rec.total_subsidy_amount,
2911 DECISION_DATE = l_def_sip_rec.decision_date,
2912 ATTRIBUTE_CATEGORY = l_def_sip_rec.attribute_category,
2913 ATTRIBUTE1 = l_def_sip_rec.attribute1,
2914 ATTRIBUTE2 = l_def_sip_rec.attribute2,
2915 ATTRIBUTE3 = l_def_sip_rec.attribute3,
2916 ATTRIBUTE4 = l_def_sip_rec.attribute4,
2917 ATTRIBUTE5 = l_def_sip_rec.attribute5,
2918 ATTRIBUTE6 = l_def_sip_rec.attribute6,
2919 ATTRIBUTE7 = l_def_sip_rec.attribute7,
2920 ATTRIBUTE8 = l_def_sip_rec.attribute8,
2921 ATTRIBUTE9 = l_def_sip_rec.attribute9,
2922 ATTRIBUTE10 = l_def_sip_rec.attribute10,
2923 ATTRIBUTE11 = l_def_sip_rec.attribute11,
2924 ATTRIBUTE12 = l_def_sip_rec.attribute12,
2925 ATTRIBUTE13 = l_def_sip_rec.attribute13,
2926 ATTRIBUTE14 = l_def_sip_rec.attribute14,
2927 ATTRIBUTE15 = l_def_sip_rec.attribute15,
2928 CREATED_BY = l_def_sip_rec.created_by,
2929 CREATION_DATE = l_def_sip_rec.creation_date,
2930 LAST_UPDATED_BY = l_def_sip_rec.last_updated_by,
2931 LAST_UPDATE_DATE = l_def_sip_rec.last_update_date,
2932 LAST_UPDATE_LOGIN = l_def_sip_rec.last_update_login
2933 WHERE ID = l_def_sip_rec.id;
2934
2935 x_sip_rec := l_sip_rec;
2936 x_return_status := l_return_status;
2937 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2938 EXCEPTION
2939 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2940 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2941 (
2942 l_api_name,
2943 G_PKG_NAME,
2944 'OKL_API.G_RET_STS_ERROR',
2945 x_msg_count,
2946 x_msg_data,
2947 '_PVT'
2948 );
2949 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2950 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2951 (
2952 l_api_name,
2953 G_PKG_NAME,
2954 'OKL_API.G_RET_STS_UNEXP_ERROR',
2955 x_msg_count,
2956 x_msg_data,
2957 '_PVT'
2958 );
2959 WHEN OTHERS THEN
2960 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2961 (
2962 l_api_name,
2963 G_PKG_NAME,
2964 'OTHERS',
2965 x_msg_count,
2966 x_msg_data,
2967 '_PVT'
2968 );
2969 END update_row;
2970 -----------------------------------------
2971 -- update_row for:OKL_SUBSIDY_POOLS_TL --
2972 -----------------------------------------
2973 PROCEDURE update_row(
2974 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2975 x_return_status OUT NOCOPY VARCHAR2,
2976 x_msg_count OUT NOCOPY NUMBER,
2977 x_msg_data OUT NOCOPY VARCHAR2,
2978 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
2979 x_okl_subsidy_pools_tl_rec OUT NOCOPY okl_subsidy_pools_tl_rec_type) IS
2980
2981 l_api_version CONSTANT NUMBER := 1;
2982 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2983 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2984 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type := p_okl_subsidy_pools_tl_rec;
2985 l_def_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
2986 l_row_notfound BOOLEAN := TRUE;
2987 ----------------------------------
2988 -- FUNCTION populate_new_record --
2989 ----------------------------------
2990 FUNCTION populate_new_record (
2991 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
2992 x_okl_subsidy_pools_tl_rec OUT NOCOPY okl_subsidy_pools_tl_rec_type
2993 ) RETURN VARCHAR2 IS
2994 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
2995 l_row_notfound BOOLEAN := TRUE;
2996 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2997 BEGIN
2998 x_okl_subsidy_pools_tl_rec := p_okl_subsidy_pools_tl_rec;
2999 -- Get current database values
3000 l_okl_subsidy_pools_tl_rec := get_rec(p_okl_subsidy_pools_tl_rec, l_return_status);
3001 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
3002 IF (x_okl_subsidy_pools_tl_rec.id = OKL_API.G_MISS_NUM)
3003 THEN
3004 x_okl_subsidy_pools_tl_rec.id := l_okl_subsidy_pools_tl_rec.id;
3005 END IF;
3006 IF (x_okl_subsidy_pools_tl_rec.short_description = OKL_API.G_MISS_CHAR)
3007 THEN
3008 x_okl_subsidy_pools_tl_rec.short_description := l_okl_subsidy_pools_tl_rec.short_description;
3009 END IF;
3010 IF (x_okl_subsidy_pools_tl_rec.description = OKL_API.G_MISS_CHAR)
3011 THEN
3012 x_okl_subsidy_pools_tl_rec.description := l_okl_subsidy_pools_tl_rec.description;
3013 END IF;
3014 IF (x_okl_subsidy_pools_tl_rec.language = OKL_API.G_MISS_CHAR)
3015 THEN
3016 x_okl_subsidy_pools_tl_rec.language := l_okl_subsidy_pools_tl_rec.language;
3017 END IF;
3018 IF (x_okl_subsidy_pools_tl_rec.source_lang = OKL_API.G_MISS_CHAR)
3019 THEN
3020 x_okl_subsidy_pools_tl_rec.source_lang := l_okl_subsidy_pools_tl_rec.source_lang;
3021 END IF;
3022 IF (x_okl_subsidy_pools_tl_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
3023 THEN
3024 x_okl_subsidy_pools_tl_rec.sfwt_flag := l_okl_subsidy_pools_tl_rec.sfwt_flag;
3025 END IF;
3026 IF (x_okl_subsidy_pools_tl_rec.created_by = OKL_API.G_MISS_NUM)
3027 THEN
3028 x_okl_subsidy_pools_tl_rec.created_by := l_okl_subsidy_pools_tl_rec.created_by;
3029 END IF;
3030 IF (x_okl_subsidy_pools_tl_rec.creation_date = OKL_API.G_MISS_DATE)
3031 THEN
3032 x_okl_subsidy_pools_tl_rec.creation_date := l_okl_subsidy_pools_tl_rec.creation_date;
3033 END IF;
3034 IF (x_okl_subsidy_pools_tl_rec.last_updated_by = OKL_API.G_MISS_NUM)
3035 THEN
3036 x_okl_subsidy_pools_tl_rec.last_updated_by := l_okl_subsidy_pools_tl_rec.last_updated_by;
3037 END IF;
3038 IF (x_okl_subsidy_pools_tl_rec.last_update_date = OKL_API.G_MISS_DATE)
3039 THEN
3040 x_okl_subsidy_pools_tl_rec.last_update_date := l_okl_subsidy_pools_tl_rec.last_update_date;
3041 END IF;
3042 IF (x_okl_subsidy_pools_tl_rec.last_update_login = OKL_API.G_MISS_NUM)
3043 THEN
3044 x_okl_subsidy_pools_tl_rec.last_update_login := l_okl_subsidy_pools_tl_rec.last_update_login;
3045 END IF;
3046 END IF;
3047 RETURN(l_return_status);
3048 END populate_new_record;
3049 ---------------------------------------------
3050 -- Set_Attributes for:OKL_SUBSIDY_POOLS_TL --
3051 ---------------------------------------------
3052 FUNCTION Set_Attributes (
3053 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type,
3054 x_okl_subsidy_pools_tl_rec OUT NOCOPY okl_subsidy_pools_tl_rec_type
3055 ) RETURN VARCHAR2 IS
3056 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3057 BEGIN
3058 x_okl_subsidy_pools_tl_rec := p_okl_subsidy_pools_tl_rec;
3059 x_okl_subsidy_pools_tl_rec.LANGUAGE := USERENV('LANG');
3060 x_okl_subsidy_pools_tl_rec.LANGUAGE := USERENV('LANG');
3061 RETURN(l_return_status);
3062 END Set_Attributes;
3063 BEGIN
3064 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3065 p_init_msg_list,
3066 '_PVT',
3067 x_return_status);
3068 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3069 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3070 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3071 RAISE OKL_API.G_EXCEPTION_ERROR;
3072 END IF;
3073 --- Setting item attributes
3074 l_return_status := Set_Attributes(
3075 p_okl_subsidy_pools_tl_rec, -- IN
3076 l_okl_subsidy_pools_tl_rec); -- OUT
3077 --- If any errors happen abort API
3078 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3079 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3080 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3081 RAISE OKL_API.G_EXCEPTION_ERROR;
3082 END IF;
3083 l_return_status := populate_new_record(l_okl_subsidy_pools_tl_rec, l_def_okl_subsidy_pools_tl_rec);
3084 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3085 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3086 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3087 RAISE OKL_API.G_EXCEPTION_ERROR;
3088 END IF;
3089 UPDATE OKL_SUBSIDY_POOLS_TL
3090 SET SHORT_DESCRIPTION = l_def_okl_subsidy_pools_tl_rec.short_description,
3091 DESCRIPTION = l_def_okl_subsidy_pools_tl_rec.description,
3092 CREATED_BY = l_def_okl_subsidy_pools_tl_rec.created_by,
3093 CREATION_DATE = l_def_okl_subsidy_pools_tl_rec.creation_date,
3094 LAST_UPDATED_BY = l_def_okl_subsidy_pools_tl_rec.last_updated_by,
3095 LAST_UPDATE_DATE = l_def_okl_subsidy_pools_tl_rec.last_update_date,
3096 LAST_UPDATE_LOGIN = l_def_okl_subsidy_pools_tl_rec.last_update_login
3097 WHERE ID = l_def_okl_subsidy_pools_tl_rec.id
3098 AND SOURCE_LANG = USERENV('LANG');
3099
3100 UPDATE OKL_SUBSIDY_POOLS_TL
3101 SET SFWT_FLAG = 'Y'
3102 WHERE ID = l_def_okl_subsidy_pools_tl_rec.id
3103 AND SOURCE_LANG <> USERENV('LANG');
3104
3105 x_okl_subsidy_pools_tl_rec := l_okl_subsidy_pools_tl_rec;
3106 x_return_status := l_return_status;
3107 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3108 EXCEPTION
3109 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3110 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3111 (
3112 l_api_name,
3113 G_PKG_NAME,
3114 'OKL_API.G_RET_STS_ERROR',
3115 x_msg_count,
3116 x_msg_data,
3117 '_PVT'
3118 );
3119 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3120 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3121 (
3122 l_api_name,
3123 G_PKG_NAME,
3124 'OKL_API.G_RET_STS_UNEXP_ERROR',
3125 x_msg_count,
3126 x_msg_data,
3127 '_PVT'
3128 );
3129 WHEN OTHERS THEN
3130 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3131 (
3132 l_api_name,
3133 G_PKG_NAME,
3134 'OTHERS',
3135 x_msg_count,
3136 x_msg_data,
3137 '_PVT'
3138 );
3139 END update_row;
3140 ----------------------------------------
3141 -- update_row for:OKL_SUBSIDY_POOLS_V --
3142 ----------------------------------------
3143 PROCEDURE update_row(
3144 p_api_version IN NUMBER,
3145 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3146 x_return_status OUT NOCOPY VARCHAR2,
3147 x_msg_count OUT NOCOPY NUMBER,
3148 x_msg_data OUT NOCOPY VARCHAR2,
3149 p_sipv_rec IN sipv_rec_type,
3150 x_sipv_rec OUT NOCOPY sipv_rec_type) IS
3151
3152 l_api_version CONSTANT NUMBER := 1;
3153 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3154 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3155 l_sipv_rec sipv_rec_type := p_sipv_rec;
3156 l_def_sipv_rec sipv_rec_type;
3157 l_db_sipv_rec sipv_rec_type;
3158 l_sip_rec sip_rec_type;
3159 lx_sip_rec sip_rec_type;
3160 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
3161 lx_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
3162 -------------------------------
3163 -- FUNCTION fill_who_columns --
3164 -------------------------------
3165 FUNCTION fill_who_columns (
3166 p_sipv_rec IN sipv_rec_type
3167 ) RETURN sipv_rec_type IS
3168 l_sipv_rec sipv_rec_type := p_sipv_rec;
3169 BEGIN
3170 l_sipv_rec.LAST_UPDATE_DATE := SYSDATE;
3171 l_sipv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3172 l_sipv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3173 RETURN(l_sipv_rec);
3174 END fill_who_columns;
3175 ----------------------------------
3176 -- FUNCTION populate_new_record --
3177 ----------------------------------
3178 FUNCTION populate_new_record (
3179 p_sipv_rec IN sipv_rec_type,
3180 x_sipv_rec OUT NOCOPY sipv_rec_type
3181 ) RETURN VARCHAR2 IS
3182 l_row_notfound BOOLEAN := TRUE;
3183 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3184 BEGIN
3185 x_sipv_rec := p_sipv_rec;
3186 -- Get current database values
3187 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
3188 -- so it may be verified through LOCK_ROW.
3189 l_db_sipv_rec := get_rec(p_sipv_rec, l_return_status);
3190 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
3191 IF (x_sipv_rec.id = OKL_API.G_MISS_NUM)
3192 THEN
3193 x_sipv_rec.id := l_db_sipv_rec.id;
3194 END IF;
3195 IF (x_sipv_rec.object_version_number = OKL_API.G_MISS_NUM)
3196 THEN
3197 x_sipv_rec.object_version_number := l_db_sipv_rec.object_version_number;
3198 END IF;
3199 IF (x_sipv_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
3200 THEN
3201 x_sipv_rec.sfwt_flag := l_db_sipv_rec.sfwt_flag;
3202 END IF;
3203 IF (x_sipv_rec.pool_type_code = OKL_API.G_MISS_CHAR)
3204 THEN
3205 x_sipv_rec.pool_type_code := l_db_sipv_rec.pool_type_code;
3206 END IF;
3207 IF (x_sipv_rec.subsidy_pool_name = OKL_API.G_MISS_CHAR)
3208 THEN
3209 x_sipv_rec.subsidy_pool_name := l_db_sipv_rec.subsidy_pool_name;
3210 END IF;
3211 IF (x_sipv_rec.short_description = OKL_API.G_MISS_CHAR)
3212 THEN
3213 x_sipv_rec.short_description := l_db_sipv_rec.short_description;
3214 END IF;
3215 IF (x_sipv_rec.description = OKL_API.G_MISS_CHAR)
3216 THEN
3217 x_sipv_rec.description := l_db_sipv_rec.description;
3218 END IF;
3219 IF (x_sipv_rec.effective_from_date = OKL_API.G_MISS_DATE)
3220 THEN
3221 x_sipv_rec.effective_from_date := l_db_sipv_rec.effective_from_date;
3222 END IF;
3223 IF (x_sipv_rec.effective_to_date = OKL_API.G_MISS_DATE)
3224 THEN
3225 x_sipv_rec.effective_to_date := l_db_sipv_rec.effective_to_date;
3226 END IF;
3227 IF (x_sipv_rec.currency_code = OKL_API.G_MISS_CHAR)
3228 THEN
3229 x_sipv_rec.currency_code := l_db_sipv_rec.currency_code;
3230 END IF;
3231 IF (x_sipv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR)
3232 THEN
3233 x_sipv_rec.currency_conversion_type := l_db_sipv_rec.currency_conversion_type;
3234 END IF;
3235 IF (x_sipv_rec.decision_status_code = OKL_API.G_MISS_CHAR)
3236 THEN
3237 x_sipv_rec.decision_status_code := l_db_sipv_rec.decision_status_code;
3238 END IF;
3239 IF (x_sipv_rec.subsidy_pool_id = OKL_API.G_MISS_NUM)
3240 THEN
3241 x_sipv_rec.subsidy_pool_id := l_db_sipv_rec.subsidy_pool_id;
3242 END IF;
3243 IF (x_sipv_rec.reporting_pool_limit = OKL_API.G_MISS_NUM)
3244 THEN
3245 x_sipv_rec.reporting_pool_limit := l_db_sipv_rec.reporting_pool_limit;
3246 END IF;
3247 IF (x_sipv_rec.total_budgets = OKL_API.G_MISS_NUM)
3248 THEN
3249 x_sipv_rec.total_budgets := l_db_sipv_rec.total_budgets;
3250 END IF;
3251 IF (x_sipv_rec.total_subsidy_amount = OKL_API.G_MISS_NUM)
3252 THEN
3253 x_sipv_rec.total_subsidy_amount := l_db_sipv_rec.total_subsidy_amount;
3254 END IF;
3255 IF (x_sipv_rec.decision_date = OKL_API.G_MISS_DATE)
3256 THEN
3257 x_sipv_rec.decision_date := l_db_sipv_rec.decision_date;
3258 END IF;
3259 IF (x_sipv_rec.attribute_category = OKL_API.G_MISS_CHAR)
3260 THEN
3261 x_sipv_rec.attribute_category := l_db_sipv_rec.attribute_category;
3262 END IF;
3263 IF (x_sipv_rec.attribute1 = OKL_API.G_MISS_CHAR)
3264 THEN
3265 x_sipv_rec.attribute1 := l_db_sipv_rec.attribute1;
3266 END IF;
3267 IF (x_sipv_rec.attribute2 = OKL_API.G_MISS_CHAR)
3268 THEN
3269 x_sipv_rec.attribute2 := l_db_sipv_rec.attribute2;
3270 END IF;
3271 IF (x_sipv_rec.attribute3 = OKL_API.G_MISS_CHAR)
3272 THEN
3273 x_sipv_rec.attribute3 := l_db_sipv_rec.attribute3;
3274 END IF;
3275 IF (x_sipv_rec.attribute4 = OKL_API.G_MISS_CHAR)
3276 THEN
3277 x_sipv_rec.attribute4 := l_db_sipv_rec.attribute4;
3278 END IF;
3279 IF (x_sipv_rec.attribute5 = OKL_API.G_MISS_CHAR)
3280 THEN
3281 x_sipv_rec.attribute5 := l_db_sipv_rec.attribute5;
3282 END IF;
3283 IF (x_sipv_rec.attribute6 = OKL_API.G_MISS_CHAR)
3284 THEN
3285 x_sipv_rec.attribute6 := l_db_sipv_rec.attribute6;
3286 END IF;
3287 IF (x_sipv_rec.attribute7 = OKL_API.G_MISS_CHAR)
3288 THEN
3289 x_sipv_rec.attribute7 := l_db_sipv_rec.attribute7;
3290 END IF;
3291 IF (x_sipv_rec.attribute8 = OKL_API.G_MISS_CHAR)
3292 THEN
3293 x_sipv_rec.attribute8 := l_db_sipv_rec.attribute8;
3294 END IF;
3295 IF (x_sipv_rec.attribute9 = OKL_API.G_MISS_CHAR)
3296 THEN
3297 x_sipv_rec.attribute9 := l_db_sipv_rec.attribute9;
3298 END IF;
3299 IF (x_sipv_rec.attribute10 = OKL_API.G_MISS_CHAR)
3300 THEN
3301 x_sipv_rec.attribute10 := l_db_sipv_rec.attribute10;
3302 END IF;
3303 IF (x_sipv_rec.attribute11 = OKL_API.G_MISS_CHAR)
3304 THEN
3305 x_sipv_rec.attribute11 := l_db_sipv_rec.attribute11;
3306 END IF;
3307 IF (x_sipv_rec.attribute12 = OKL_API.G_MISS_CHAR)
3308 THEN
3309 x_sipv_rec.attribute12 := l_db_sipv_rec.attribute12;
3310 END IF;
3311 IF (x_sipv_rec.attribute13 = OKL_API.G_MISS_CHAR)
3312 THEN
3313 x_sipv_rec.attribute13 := l_db_sipv_rec.attribute13;
3314 END IF;
3315 IF (x_sipv_rec.attribute14 = OKL_API.G_MISS_CHAR)
3316 THEN
3317 x_sipv_rec.attribute14 := l_db_sipv_rec.attribute14;
3318 END IF;
3319 IF (x_sipv_rec.attribute15 = OKL_API.G_MISS_CHAR)
3320 THEN
3321 x_sipv_rec.attribute15 := l_db_sipv_rec.attribute15;
3322 END IF;
3323 IF (x_sipv_rec.created_by = OKL_API.G_MISS_NUM)
3324 THEN
3325 x_sipv_rec.created_by := l_db_sipv_rec.created_by;
3326 END IF;
3327 IF (x_sipv_rec.creation_date = OKL_API.G_MISS_DATE)
3328 THEN
3329 x_sipv_rec.creation_date := l_db_sipv_rec.creation_date;
3330 END IF;
3331 IF (x_sipv_rec.last_updated_by = OKL_API.G_MISS_NUM)
3332 THEN
3333 x_sipv_rec.last_updated_by := l_db_sipv_rec.last_updated_by;
3334 END IF;
3335 IF (x_sipv_rec.last_update_date = OKL_API.G_MISS_DATE)
3336 THEN
3337 x_sipv_rec.last_update_date := l_db_sipv_rec.last_update_date;
3338 END IF;
3339 IF (x_sipv_rec.last_update_login = OKL_API.G_MISS_NUM)
3340 THEN
3341 x_sipv_rec.last_update_login := l_db_sipv_rec.last_update_login;
3342 END IF;
3343 END IF;
3344 RETURN(l_return_status);
3345 END populate_new_record;
3346 --------------------------------------------
3347 -- Set_Attributes for:OKL_SUBSIDY_POOLS_V --
3348 --------------------------------------------
3349 FUNCTION Set_Attributes (
3350 p_sipv_rec IN sipv_rec_type,
3351 x_sipv_rec OUT NOCOPY sipv_rec_type
3352 ) RETURN VARCHAR2 IS
3353 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3354 BEGIN
3355 x_sipv_rec := p_sipv_rec;
3356 RETURN(l_return_status);
3357 END Set_Attributes;
3358 BEGIN
3359 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3360 G_PKG_NAME,
3361 p_init_msg_list,
3362 l_api_version,
3363 p_api_version,
3364 '_PVT',
3365 x_return_status);
3366 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3367 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3368 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3369 RAISE OKL_API.G_EXCEPTION_ERROR;
3370 END IF;
3371 --- Setting item attributes
3372 l_return_status := Set_Attributes(
3373 p_sipv_rec, -- IN
3374 l_sipv_rec); -- OUT
3375 --- If any errors happen abort API
3376 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3378 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3379 RAISE OKL_API.G_EXCEPTION_ERROR;
3380 END IF;
3381 l_return_status := populate_new_record(l_sipv_rec, l_def_sipv_rec);
3382 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3383 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3384 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3385 RAISE OKL_API.G_EXCEPTION_ERROR;
3386 END IF;
3387 l_def_sipv_rec := fill_who_columns(l_def_sipv_rec);
3388 --- Validate all non-missing attributes (Item Level Validation)
3389 l_return_status := Validate_Attributes(l_def_sipv_rec);
3390 --- If any errors happen abort API
3391 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3392 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3393 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3394 RAISE OKL_API.G_EXCEPTION_ERROR;
3395 END IF;
3396 l_return_status := Validate_Record(l_def_sipv_rec, l_db_sipv_rec);
3397 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3398 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3399 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3400 RAISE OKL_API.G_EXCEPTION_ERROR;
3401 END IF;
3402 /***********************commented********
3403 --avsingh
3404 -- Lock the Record
3405 lock_row(
3406 p_api_version => p_api_version,
3407 p_init_msg_list => p_init_msg_list,
3408 x_return_status => l_return_status,
3409 x_msg_count => x_msg_count,
3410 x_msg_data => x_msg_data,
3411 p_sipv_rec => p_sipv_rec);
3412 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3413 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3414 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3415 RAISE OKL_API.G_EXCEPTION_ERROR;
3416 END IF;
3417 ************************************************/
3418 -----------------------------------------
3419 -- Move VIEW record to "Child" records --
3420 -----------------------------------------
3421 migrate(l_def_sipv_rec, l_sip_rec);
3422 migrate(l_def_sipv_rec, l_okl_subsidy_pools_tl_rec);
3423 -----------------------------------------------
3424 -- Call the UPDATE_ROW for each child record --
3425 -----------------------------------------------
3426 update_row(
3427 p_init_msg_list,
3428 l_return_status,
3429 x_msg_count,
3430 x_msg_data,
3431 l_sip_rec,
3432 lx_sip_rec
3433 );
3434 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3435 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3436 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3437 RAISE OKL_API.G_EXCEPTION_ERROR;
3438 END IF;
3439 migrate(lx_sip_rec, l_def_sipv_rec);
3440 update_row(
3441 p_init_msg_list,
3442 l_return_status,
3443 x_msg_count,
3444 x_msg_data,
3445 l_okl_subsidy_pools_tl_rec,
3446 lx_okl_subsidy_pools_tl_rec
3447 );
3448 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3449 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3450 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3451 RAISE OKL_API.G_EXCEPTION_ERROR;
3452 END IF;
3453 migrate(lx_okl_subsidy_pools_tl_rec, l_def_sipv_rec);
3454 x_sipv_rec := l_def_sipv_rec;
3455 x_return_status := l_return_status;
3456 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3457 EXCEPTION
3458 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3459 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3460 (
3461 l_api_name,
3462 G_PKG_NAME,
3463 'OKL_API.G_RET_STS_ERROR',
3464 x_msg_count,
3465 x_msg_data,
3466 '_PVT'
3467 );
3468 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3469 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3470 (
3471 l_api_name,
3472 G_PKG_NAME,
3473 'OKL_API.G_RET_STS_UNEXP_ERROR',
3474 x_msg_count,
3475 x_msg_data,
3476 '_PVT'
3477 );
3478 WHEN OTHERS THEN
3479 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3480 (
3481 l_api_name,
3482 G_PKG_NAME,
3483 'OTHERS',
3484 x_msg_count,
3485 x_msg_data,
3486 '_PVT'
3487 );
3488 END update_row;
3489 ----------------------------------------
3490 -- PL/SQL TBL update_row for:sipv_tbl --
3491 ----------------------------------------
3492 PROCEDURE update_row(
3493 p_api_version IN NUMBER,
3494 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3495 x_return_status OUT NOCOPY VARCHAR2,
3496 x_msg_count OUT NOCOPY NUMBER,
3497 x_msg_data OUT NOCOPY VARCHAR2,
3498 p_sipv_tbl IN sipv_tbl_type,
3499 x_sipv_tbl OUT NOCOPY sipv_tbl_type,
3500 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3501
3502 l_api_version CONSTANT NUMBER := 1;
3503 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
3504 i NUMBER := 0;
3505 BEGIN
3506 OKL_API.init_msg_list(p_init_msg_list);
3507 -- Make sure PL/SQL table has records in it before passing
3508 IF (p_sipv_tbl.COUNT > 0) THEN
3509 i := p_sipv_tbl.FIRST;
3510 LOOP
3511 DECLARE
3512 l_error_rec OKL_API.ERROR_REC_TYPE;
3513 BEGIN
3514 l_error_rec.api_name := l_api_name;
3515 l_error_rec.api_package := G_PKG_NAME;
3516 l_error_rec.idx := i;
3517 update_row (
3518 p_api_version => p_api_version,
3519 p_init_msg_list => OKL_API.G_FALSE,
3520 x_return_status => l_error_rec.error_type,
3521 x_msg_count => l_error_rec.msg_count,
3522 x_msg_data => l_error_rec.msg_data,
3523 p_sipv_rec => p_sipv_tbl(i),
3524 x_sipv_rec => x_sipv_tbl(i));
3525 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3526 l_error_rec.sqlcode := SQLCODE;
3527 load_error_tbl(l_error_rec, px_error_tbl);
3528 ELSE
3529 x_msg_count := l_error_rec.msg_count;
3530 x_msg_data := l_error_rec.msg_data;
3531 END IF;
3532 EXCEPTION
3533 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3534 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3535 l_error_rec.sqlcode := SQLCODE;
3536 load_error_tbl(l_error_rec, px_error_tbl);
3537 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3538 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3539 l_error_rec.sqlcode := SQLCODE;
3540 load_error_tbl(l_error_rec, px_error_tbl);
3541 WHEN OTHERS THEN
3542 l_error_rec.error_type := 'OTHERS';
3543 l_error_rec.sqlcode := SQLCODE;
3544 load_error_tbl(l_error_rec, px_error_tbl);
3545 END;
3546 EXIT WHEN (i = p_sipv_tbl.LAST);
3547 i := p_sipv_tbl.NEXT(i);
3548 END LOOP;
3549 END IF;
3550 -- Loop through the error_tbl to find the error with the highest severity
3551 -- and return it.
3552 x_return_status := find_highest_exception(px_error_tbl);
3553 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3554 EXCEPTION
3555 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3556 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3557 (
3558 l_api_name,
3559 G_PKG_NAME,
3560 'OKL_API.G_RET_STS_ERROR',
3561 x_msg_count,
3562 x_msg_data,
3563 '_PVT'
3564 );
3565 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3566 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3567 (
3568 l_api_name,
3569 G_PKG_NAME,
3570 'OKL_API.G_RET_STS_UNEXP_ERROR',
3571 x_msg_count,
3572 x_msg_data,
3573 '_PVT'
3574 );
3575 WHEN OTHERS THEN
3576 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3577 (
3578 l_api_name,
3579 G_PKG_NAME,
3580 'OTHERS',
3581 x_msg_count,
3582 x_msg_data,
3583 '_PVT'
3584 );
3585 END update_row;
3586
3587 ----------------------------------------
3588 -- PL/SQL TBL update_row for:SIPV_TBL --
3589 ----------------------------------------
3590 PROCEDURE update_row(
3591 p_api_version IN NUMBER,
3592 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3593 x_return_status OUT NOCOPY VARCHAR2,
3594 x_msg_count OUT NOCOPY NUMBER,
3595 x_msg_data OUT NOCOPY VARCHAR2,
3596 p_sipv_tbl IN sipv_tbl_type,
3597 x_sipv_tbl OUT NOCOPY sipv_tbl_type) IS
3598
3599 l_api_version CONSTANT NUMBER := 1;
3600 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3601 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3602 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3603 BEGIN
3604 OKL_API.init_msg_list(p_init_msg_list);
3605 -- Make sure PL/SQL table has records in it before passing
3606 IF (p_sipv_tbl.COUNT > 0) THEN
3607 update_row (
3608 p_api_version => p_api_version,
3609 p_init_msg_list => OKL_API.G_FALSE,
3610 x_return_status => x_return_status,
3611 x_msg_count => x_msg_count,
3612 x_msg_data => x_msg_data,
3613 p_sipv_tbl => p_sipv_tbl,
3614 x_sipv_tbl => x_sipv_tbl,
3615 px_error_tbl => l_error_tbl);
3616 END IF;
3617 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3618 EXCEPTION
3619 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3620 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3621 (
3622 l_api_name,
3623 G_PKG_NAME,
3624 'OKL_API.G_RET_STS_ERROR',
3625 x_msg_count,
3626 x_msg_data,
3627 '_PVT'
3628 );
3629 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3630 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OKL_API.G_RET_STS_UNEXP_ERROR',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 WHEN OTHERS THEN
3640 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3641 (
3642 l_api_name,
3643 G_PKG_NAME,
3644 'OTHERS',
3645 x_msg_count,
3646 x_msg_data,
3647 '_PVT'
3648 );
3649 END update_row;
3650
3651 ---------------------------------------------------------------------------
3652 -- PROCEDURE delete_row
3653 ---------------------------------------------------------------------------
3654 ----------------------------------------
3655 -- delete_row for:OKL_SUBSIDY_POOLS_B --
3656 ----------------------------------------
3657 PROCEDURE delete_row(
3658 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3659 x_return_status OUT NOCOPY VARCHAR2,
3660 x_msg_count OUT NOCOPY NUMBER,
3661 x_msg_data OUT NOCOPY VARCHAR2,
3662 p_sip_rec IN sip_rec_type) IS
3663
3664 l_api_version CONSTANT NUMBER := 1;
3665 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3666 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3667 l_sip_rec sip_rec_type := p_sip_rec;
3668 l_row_notfound BOOLEAN := TRUE;
3669 BEGIN
3670 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3671 p_init_msg_list,
3672 '_PVT',
3673 x_return_status);
3674 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3676 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3677 RAISE OKL_API.G_EXCEPTION_ERROR;
3678 END IF;
3679
3680 DELETE FROM OKL_SUBSIDY_POOLS_B
3681 WHERE ID = p_sip_rec.id;
3682
3683 x_return_status := l_return_status;
3684 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3685 EXCEPTION
3686 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3687 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3688 (
3689 l_api_name,
3690 G_PKG_NAME,
3691 'OKL_API.G_RET_STS_ERROR',
3692 x_msg_count,
3693 x_msg_data,
3694 '_PVT'
3695 );
3696 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3697 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3698 (
3699 l_api_name,
3700 G_PKG_NAME,
3701 'OKL_API.G_RET_STS_UNEXP_ERROR',
3702 x_msg_count,
3703 x_msg_data,
3704 '_PVT'
3705 );
3706 WHEN OTHERS THEN
3707 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3708 (
3709 l_api_name,
3710 G_PKG_NAME,
3711 'OTHERS',
3712 x_msg_count,
3713 x_msg_data,
3714 '_PVT'
3715 );
3716 END delete_row;
3717 -----------------------------------------
3718 -- delete_row for:OKL_SUBSIDY_POOLS_TL --
3719 -----------------------------------------
3720 PROCEDURE delete_row(
3721 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3722 x_return_status OUT NOCOPY VARCHAR2,
3723 x_msg_count OUT NOCOPY NUMBER,
3724 x_msg_data OUT NOCOPY VARCHAR2,
3725 p_okl_subsidy_pools_tl_rec IN okl_subsidy_pools_tl_rec_type) IS
3726
3727 l_api_version CONSTANT NUMBER := 1;
3728 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3729 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3730 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type := p_okl_subsidy_pools_tl_rec;
3731 l_row_notfound BOOLEAN := TRUE;
3732 BEGIN
3733 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3734 p_init_msg_list,
3735 '_PVT',
3736 x_return_status);
3737 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3738 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3739 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3740 RAISE OKL_API.G_EXCEPTION_ERROR;
3741 END IF;
3742
3743 DELETE FROM OKL_SUBSIDY_POOLS_TL
3744 WHERE ID = p_okl_subsidy_pools_tl_rec.id;
3745
3746 x_return_status := l_return_status;
3747 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3748 EXCEPTION
3749 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3750 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3751 (
3752 l_api_name,
3753 G_PKG_NAME,
3754 'OKL_API.G_RET_STS_ERROR',
3755 x_msg_count,
3756 x_msg_data,
3757 '_PVT'
3758 );
3759 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3760 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3761 (
3762 l_api_name,
3763 G_PKG_NAME,
3764 'OKL_API.G_RET_STS_UNEXP_ERROR',
3765 x_msg_count,
3766 x_msg_data,
3767 '_PVT'
3768 );
3769 WHEN OTHERS THEN
3770 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3771 (
3772 l_api_name,
3773 G_PKG_NAME,
3774 'OTHERS',
3775 x_msg_count,
3776 x_msg_data,
3777 '_PVT'
3778 );
3779 END delete_row;
3780 ----------------------------------------
3781 -- delete_row for:OKL_SUBSIDY_POOLS_V --
3782 ----------------------------------------
3783 PROCEDURE delete_row(
3784 p_api_version IN NUMBER,
3785 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3786 x_return_status OUT NOCOPY VARCHAR2,
3787 x_msg_count OUT NOCOPY NUMBER,
3788 x_msg_data OUT NOCOPY VARCHAR2,
3789 p_sipv_rec IN sipv_rec_type) IS
3790
3791 l_api_version CONSTANT NUMBER := 1;
3792 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3793 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3794 l_sipv_rec sipv_rec_type := p_sipv_rec;
3795 l_okl_subsidy_pools_tl_rec okl_subsidy_pools_tl_rec_type;
3796 l_sip_rec sip_rec_type;
3797 BEGIN
3798 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3799 G_PKG_NAME,
3800 p_init_msg_list,
3801 l_api_version,
3802 p_api_version,
3803 '_PVT',
3804 x_return_status);
3805 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3806 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3807 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3808 RAISE OKL_API.G_EXCEPTION_ERROR;
3809 END IF;
3810 -----------------------------------------
3811 -- Move VIEW record to "Child" records --
3812 -----------------------------------------
3813 migrate(l_sipv_rec, l_okl_subsidy_pools_tl_rec);
3814 migrate(l_sipv_rec, l_sip_rec);
3815 -----------------------------------------------
3816 -- Call the DELETE_ROW for each child record --
3817 -----------------------------------------------
3818 delete_row(
3819 p_init_msg_list,
3820 l_return_status,
3821 x_msg_count,
3822 x_msg_data,
3823 l_okl_subsidy_pools_tl_rec
3824 );
3825 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3826 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3827 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3828 RAISE OKL_API.G_EXCEPTION_ERROR;
3829 END IF;
3830 delete_row(
3831 p_init_msg_list,
3832 l_return_status,
3833 x_msg_count,
3834 x_msg_data,
3835 l_sip_rec
3836 );
3837 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3838 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3839 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3840 RAISE OKL_API.G_EXCEPTION_ERROR;
3841 END IF;
3842 x_return_status := l_return_status;
3843 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3844 EXCEPTION
3845 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3846 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3847 (
3848 l_api_name,
3849 G_PKG_NAME,
3850 'OKL_API.G_RET_STS_ERROR',
3851 x_msg_count,
3852 x_msg_data,
3853 '_PVT'
3854 );
3855 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3856 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3857 (
3858 l_api_name,
3859 G_PKG_NAME,
3860 'OKL_API.G_RET_STS_UNEXP_ERROR',
3861 x_msg_count,
3862 x_msg_data,
3863 '_PVT'
3864 );
3865 WHEN OTHERS THEN
3866 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3867 (
3868 l_api_name,
3869 G_PKG_NAME,
3870 'OTHERS',
3871 x_msg_count,
3872 x_msg_data,
3873 '_PVT'
3874 );
3875 END delete_row;
3876 ---------------------------------------------------
3877 -- PL/SQL TBL delete_row for:OKL_SUBSIDY_POOLS_V --
3878 ---------------------------------------------------
3879 PROCEDURE delete_row(
3880 p_api_version IN NUMBER,
3881 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3882 x_return_status OUT NOCOPY VARCHAR2,
3883 x_msg_count OUT NOCOPY NUMBER,
3884 x_msg_data OUT NOCOPY VARCHAR2,
3885 p_sipv_tbl IN sipv_tbl_type,
3886 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3887
3888 l_api_version CONSTANT NUMBER := 1;
3889 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3890 i NUMBER := 0;
3891 BEGIN
3892 OKL_API.init_msg_list(p_init_msg_list);
3893 -- Make sure PL/SQL table has records in it before passing
3894 IF (p_sipv_tbl.COUNT > 0) THEN
3895 i := p_sipv_tbl.FIRST;
3896 LOOP
3897 DECLARE
3898 l_error_rec OKL_API.ERROR_REC_TYPE;
3899 BEGIN
3900 l_error_rec.api_name := l_api_name;
3901 l_error_rec.api_package := G_PKG_NAME;
3902 l_error_rec.idx := i;
3903 delete_row (
3904 p_api_version => p_api_version,
3905 p_init_msg_list => OKL_API.G_FALSE,
3906 x_return_status => l_error_rec.error_type,
3907 x_msg_count => l_error_rec.msg_count,
3908 x_msg_data => l_error_rec.msg_data,
3909 p_sipv_rec => p_sipv_tbl(i));
3910 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3911 l_error_rec.sqlcode := SQLCODE;
3912 load_error_tbl(l_error_rec, px_error_tbl);
3913 ELSE
3914 x_msg_count := l_error_rec.msg_count;
3915 x_msg_data := l_error_rec.msg_data;
3916 END IF;
3917 EXCEPTION
3918 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3919 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3920 l_error_rec.sqlcode := SQLCODE;
3921 load_error_tbl(l_error_rec, px_error_tbl);
3922 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3923 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3924 l_error_rec.sqlcode := SQLCODE;
3925 load_error_tbl(l_error_rec, px_error_tbl);
3926 WHEN OTHERS THEN
3927 l_error_rec.error_type := 'OTHERS';
3928 l_error_rec.sqlcode := SQLCODE;
3929 load_error_tbl(l_error_rec, px_error_tbl);
3930 END;
3931 EXIT WHEN (i = p_sipv_tbl.LAST);
3932 i := p_sipv_tbl.NEXT(i);
3933 END LOOP;
3934 END IF;
3935 -- Loop through the error_tbl to find the error with the highest severity
3936 -- and return it.
3937 x_return_status := find_highest_exception(px_error_tbl);
3938 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3939 EXCEPTION
3940 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3941 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3942 (
3943 l_api_name,
3944 G_PKG_NAME,
3945 'OKL_API.G_RET_STS_ERROR',
3946 x_msg_count,
3947 x_msg_data,
3948 '_PVT'
3949 );
3950 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3951 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3952 (
3953 l_api_name,
3954 G_PKG_NAME,
3955 'OKL_API.G_RET_STS_UNEXP_ERROR',
3956 x_msg_count,
3957 x_msg_data,
3958 '_PVT'
3959 );
3960 WHEN OTHERS THEN
3961 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3962 (
3963 l_api_name,
3964 G_PKG_NAME,
3965 'OTHERS',
3966 x_msg_count,
3967 x_msg_data,
3968 '_PVT'
3969 );
3970 END delete_row;
3971
3972 ---------------------------------------------------
3973 -- PL/SQL TBL delete_row for:OKL_SUBSIDY_POOLS_V --
3974 ---------------------------------------------------
3975 PROCEDURE delete_row(
3976 p_api_version IN NUMBER,
3977 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3978 x_return_status OUT NOCOPY VARCHAR2,
3979 x_msg_count OUT NOCOPY NUMBER,
3980 x_msg_data OUT NOCOPY VARCHAR2,
3981 p_sipv_tbl IN sipv_tbl_type) IS
3982
3983 l_api_version CONSTANT NUMBER := 1;
3984 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3985 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3986 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3987 BEGIN
3988 OKL_API.init_msg_list(p_init_msg_list);
3989 -- Make sure PL/SQL table has records in it before passing
3990 IF (p_sipv_tbl.COUNT > 0) THEN
3991 delete_row (
3992 p_api_version => p_api_version,
3993 p_init_msg_list => OKL_API.G_FALSE,
3994 x_return_status => x_return_status,
3995 x_msg_count => x_msg_count,
3996 x_msg_data => x_msg_data,
3997 p_sipv_tbl => p_sipv_tbl,
3998 px_error_tbl => l_error_tbl);
3999 END IF;
4000 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
4001 EXCEPTION
4002 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4003 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4004 (
4005 l_api_name,
4006 G_PKG_NAME,
4007 'OKL_API.G_RET_STS_ERROR',
4008 x_msg_count,
4009 x_msg_data,
4010 '_PVT'
4011 );
4012 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4013 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4014 (
4015 l_api_name,
4016 G_PKG_NAME,
4017 'OKL_API.G_RET_STS_UNEXP_ERROR',
4018 x_msg_count,
4019 x_msg_data,
4020 '_PVT'
4021 );
4022 WHEN OTHERS THEN
4023 x_return_status := OKL_API.HANDLE_EXCEPTIONS
4024 (
4025 l_api_name,
4026 G_PKG_NAME,
4027 'OTHERS',
4028 x_msg_count,
4029 x_msg_data,
4030 '_PVT'
4031 );
4032 END delete_row;
4033
4034 END OKL_SIP_PVT;