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