[Home] [Help]
PACKAGE BODY: APPS.OKL_CLL_PVT
Source
1 PACKAGE BODY OKL_CLL_PVT AS
2 /* $Header: OKLSCLLB.pls 115.9 2002/12/18 12:56:38 kjinger 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 OKC_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 -- FUNCTION get_rec for: OKL_CNTR_LVLNG_LNS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_cllv_rec IN cllv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN cllv_rec_type IS
100 CURSOR okl_cllv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 KLE_ID,
104 CLG_ID,
105 OBJECT_VERSION_NUMBER,
106 ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1,
108 ATTRIBUTE2,
109 ATTRIBUTE3,
110 ATTRIBUTE4,
111 ATTRIBUTE5,
112 ATTRIBUTE6,
113 ATTRIBUTE7,
114 ATTRIBUTE8,
115 ATTRIBUTE9,
116 ATTRIBUTE10,
117 ATTRIBUTE11,
118 ATTRIBUTE12,
119 ATTRIBUTE13,
120 ATTRIBUTE14,
121 ATTRIBUTE15,
122 CREATED_BY,
123 CREATION_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_DATE,
126 LAST_UPDATE_LOGIN
127 FROM Okl_Cntr_Lvlng_Lns_V
128 WHERE okl_cntr_lvlng_lns_v.id = p_id;
129 l_okl_cllv_pk okl_cllv_pk_csr%ROWTYPE;
130 l_cllv_rec cllv_rec_type;
131 BEGIN
132 x_no_data_found := TRUE;
133 -- Get current database values
134 OPEN okl_cllv_pk_csr (p_cllv_rec.id);
135 FETCH okl_cllv_pk_csr INTO
136 l_cllv_rec.id,
137 l_cllv_rec.kle_id,
138 l_cllv_rec.clg_id,
139 l_cllv_rec.object_version_number,
140 l_cllv_rec.attribute_category,
141 l_cllv_rec.attribute1,
142 l_cllv_rec.attribute2,
143 l_cllv_rec.attribute3,
144 l_cllv_rec.attribute4,
145 l_cllv_rec.attribute5,
146 l_cllv_rec.attribute6,
147 l_cllv_rec.attribute7,
148 l_cllv_rec.attribute8,
149 l_cllv_rec.attribute9,
150 l_cllv_rec.attribute10,
151 l_cllv_rec.attribute11,
152 l_cllv_rec.attribute12,
153 l_cllv_rec.attribute13,
154 l_cllv_rec.attribute14,
155 l_cllv_rec.attribute15,
156 l_cllv_rec.created_by,
157 l_cllv_rec.creation_date,
158 l_cllv_rec.last_updated_by,
159 l_cllv_rec.last_update_date,
160 l_cllv_rec.last_update_login;
161 x_no_data_found := okl_cllv_pk_csr%NOTFOUND;
162 CLOSE okl_cllv_pk_csr;
163 RETURN(l_cllv_rec);
164 END get_rec;
165
166 ------------------------------------------------------------------
167 -- This version of get_rec sets error messages if no data found --
168 ------------------------------------------------------------------
169 FUNCTION get_rec (
170 p_cllv_rec IN cllv_rec_type,
171 x_return_status OUT NOCOPY VARCHAR2
172 ) RETURN cllv_rec_type IS
173 l_cllv_rec cllv_rec_type;
174 l_row_notfound BOOLEAN := TRUE;
175 BEGIN
176 x_return_status := OKL_API.G_RET_STS_SUCCESS;
177 l_cllv_rec := get_rec(p_cllv_rec, l_row_notfound);
178 IF (l_row_notfound) THEN
179 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
180 x_return_status := OKL_API.G_RET_STS_ERROR;
181 END IF;
182 RETURN(l_cllv_rec);
183 END get_rec;
184 -----------------------------------------------------------
185 -- So we don't have to pass an "l_row_notfound" variable --
186 -----------------------------------------------------------
187 FUNCTION get_rec (
188 p_cllv_rec IN cllv_rec_type
189 ) RETURN cllv_rec_type IS
190 l_row_not_found BOOLEAN := TRUE;
191 BEGIN
192 RETURN(get_rec(p_cllv_rec, l_row_not_found));
193 END get_rec;
194 ---------------------------------------------------------------------------
195 -- FUNCTION get_rec for: OKL_CNTR_LVLNG_LNS_B
196 ---------------------------------------------------------------------------
197 FUNCTION get_rec (
198 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
199 x_no_data_found OUT NOCOPY BOOLEAN
200 ) RETURN okl_cntr_lvlng_lns_b_rec_type IS
201 CURSOR okl_cntr_lvlng_lns_b_pk_csr (p_id IN NUMBER) IS
202 SELECT
203 ID,
204 KLE_ID,
205 CLG_ID,
206 OBJECT_VERSION_NUMBER,
207 ATTRIBUTE_CATEGORY,
208 ATTRIBUTE1,
209 ATTRIBUTE2,
210 ATTRIBUTE3,
211 ATTRIBUTE4,
212 ATTRIBUTE5,
213 ATTRIBUTE6,
214 ATTRIBUTE7,
215 ATTRIBUTE8,
216 ATTRIBUTE9,
217 ATTRIBUTE10,
218 ATTRIBUTE11,
219 ATTRIBUTE12,
220 ATTRIBUTE13,
221 ATTRIBUTE14,
222 ATTRIBUTE15,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATE_LOGIN
228 FROM Okl_Cntr_Lvlng_Lns_B
229 WHERE okl_cntr_lvlng_lns_b.id = p_id;
230 l_okl_cntr_lvlng_lns_b_pk okl_cntr_lvlng_lns_b_pk_csr%ROWTYPE;
231 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
232 BEGIN
233 x_no_data_found := TRUE;
234 -- Get current database values
235 OPEN okl_cntr_lvlng_lns_b_pk_csr (p_okl_cntr_lvlng_lns_b_rec.id);
236 FETCH okl_cntr_lvlng_lns_b_pk_csr INTO
237 l_okl_cntr_lvlng_lns_b_rec.id,
238 l_okl_cntr_lvlng_lns_b_rec.kle_id,
239 l_okl_cntr_lvlng_lns_b_rec.clg_id,
240 l_okl_cntr_lvlng_lns_b_rec.object_version_number,
241 l_okl_cntr_lvlng_lns_b_rec.attribute_category,
242 l_okl_cntr_lvlng_lns_b_rec.attribute1,
243 l_okl_cntr_lvlng_lns_b_rec.attribute2,
244 l_okl_cntr_lvlng_lns_b_rec.attribute3,
245 l_okl_cntr_lvlng_lns_b_rec.attribute4,
246 l_okl_cntr_lvlng_lns_b_rec.attribute5,
247 l_okl_cntr_lvlng_lns_b_rec.attribute6,
248 l_okl_cntr_lvlng_lns_b_rec.attribute7,
249 l_okl_cntr_lvlng_lns_b_rec.attribute8,
250 l_okl_cntr_lvlng_lns_b_rec.attribute9,
251 l_okl_cntr_lvlng_lns_b_rec.attribute10,
252 l_okl_cntr_lvlng_lns_b_rec.attribute11,
253 l_okl_cntr_lvlng_lns_b_rec.attribute12,
254 l_okl_cntr_lvlng_lns_b_rec.attribute13,
255 l_okl_cntr_lvlng_lns_b_rec.attribute14,
256 l_okl_cntr_lvlng_lns_b_rec.attribute15,
257 l_okl_cntr_lvlng_lns_b_rec.created_by,
258 l_okl_cntr_lvlng_lns_b_rec.creation_date,
259 l_okl_cntr_lvlng_lns_b_rec.last_updated_by,
260 l_okl_cntr_lvlng_lns_b_rec.last_update_date,
261 l_okl_cntr_lvlng_lns_b_rec.last_update_login;
262 x_no_data_found := okl_cntr_lvlng_lns_b_pk_csr%NOTFOUND;
263 CLOSE okl_cntr_lvlng_lns_b_pk_csr;
264 RETURN(l_okl_cntr_lvlng_lns_b_rec);
265 END get_rec;
266
267 ------------------------------------------------------------------
268 -- This version of get_rec sets error messages if no data found --
269 ------------------------------------------------------------------
270 FUNCTION get_rec (
271 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
272 x_return_status OUT NOCOPY VARCHAR2
273 ) RETURN okl_cntr_lvlng_lns_b_rec_type IS
274 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
275 l_row_notfound BOOLEAN := TRUE;
276 BEGIN
277 x_return_status := OKL_API.G_RET_STS_SUCCESS;
278 l_okl_cntr_lvlng_lns_b_rec := get_rec(p_okl_cntr_lvlng_lns_b_rec, l_row_notfound);
279 IF (l_row_notfound) THEN
280 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
281 x_return_status := OKL_API.G_RET_STS_ERROR;
282 END IF;
283 RETURN(l_okl_cntr_lvlng_lns_b_rec);
284 END get_rec;
285 -----------------------------------------------------------
286 -- So we don't have to pass an "l_row_notfound" variable --
287 -----------------------------------------------------------
288 FUNCTION get_rec (
289 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type
290 ) RETURN okl_cntr_lvlng_lns_b_rec_type IS
291 l_row_not_found BOOLEAN := TRUE;
292 BEGIN
293 RETURN(get_rec(p_okl_cntr_lvlng_lns_b_rec, l_row_not_found));
294 END get_rec;
295 ---------------------------------------------------------------------------
296 -- FUNCTION null_out_defaults for: OKL_CNTR_LVLNG_LNS_V
297 ---------------------------------------------------------------------------
298 FUNCTION null_out_defaults (
299 p_cllv_rec IN cllv_rec_type
300 ) RETURN cllv_rec_type IS
301 l_cllv_rec cllv_rec_type := p_cllv_rec;
302 BEGIN
303 IF (l_cllv_rec.id = OKL_API.G_MISS_NUM ) THEN
304 l_cllv_rec.id := NULL;
305 END IF;
306 IF (l_cllv_rec.kle_id = OKL_API.G_MISS_NUM ) THEN
307 l_cllv_rec.kle_id := NULL;
308 END IF;
309 IF (l_cllv_rec.clg_id = OKL_API.G_MISS_NUM ) THEN
310 l_cllv_rec.clg_id := NULL;
311 END IF;
312 IF (l_cllv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
313 l_cllv_rec.object_version_number := NULL;
314 END IF;
315 IF (l_cllv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
316 l_cllv_rec.attribute_category := NULL;
317 END IF;
318 IF (l_cllv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
319 l_cllv_rec.attribute1 := NULL;
320 END IF;
321 IF (l_cllv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
322 l_cllv_rec.attribute2 := NULL;
323 END IF;
324 IF (l_cllv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
325 l_cllv_rec.attribute3 := NULL;
326 END IF;
327 IF (l_cllv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
328 l_cllv_rec.attribute4 := NULL;
329 END IF;
330 IF (l_cllv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
331 l_cllv_rec.attribute5 := NULL;
332 END IF;
333 IF (l_cllv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
334 l_cllv_rec.attribute6 := NULL;
335 END IF;
336 IF (l_cllv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
337 l_cllv_rec.attribute7 := NULL;
338 END IF;
339 IF (l_cllv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
340 l_cllv_rec.attribute8 := NULL;
341 END IF;
342 IF (l_cllv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
343 l_cllv_rec.attribute9 := NULL;
344 END IF;
345 IF (l_cllv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
346 l_cllv_rec.attribute10 := NULL;
347 END IF;
348 IF (l_cllv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
349 l_cllv_rec.attribute11 := NULL;
350 END IF;
351 IF (l_cllv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
352 l_cllv_rec.attribute12 := NULL;
353 END IF;
354 IF (l_cllv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
355 l_cllv_rec.attribute13 := NULL;
356 END IF;
357 IF (l_cllv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
358 l_cllv_rec.attribute14 := NULL;
359 END IF;
360 IF (l_cllv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
361 l_cllv_rec.attribute15 := NULL;
362 END IF;
363 IF (l_cllv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
364 l_cllv_rec.created_by := NULL;
365 END IF;
366 IF (l_cllv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
367 l_cllv_rec.creation_date := NULL;
368 END IF;
369 IF (l_cllv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
370 l_cllv_rec.last_updated_by := NULL;
371 END IF;
372 IF (l_cllv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
373 l_cllv_rec.last_update_date := NULL;
374 END IF;
375 IF (l_cllv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
376 l_cllv_rec.last_update_login := NULL;
377 END IF;
378 RETURN(l_cllv_rec);
379 END null_out_defaults;
380
381 ---------------------------------
382 -- Validate_Attributes for: ID --
383 ---------------------------------
384 PROCEDURE validate_id(
385 x_return_status OUT NOCOPY VARCHAR2,
386 p_id IN NUMBER) IS
387 BEGIN
388 x_return_status := OKL_API.G_RET_STS_SUCCESS;
389 IF (p_id = OKL_API.G_MISS_NUM OR
390 p_id IS NULL)
391 THEN
392 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Counter');
393 x_return_status := OKL_API.G_RET_STS_ERROR;
394 RAISE G_EXCEPTION_HALT_VALIDATION;
395 END IF;
396 EXCEPTION
397 WHEN G_EXCEPTION_HALT_VALIDATION THEN
398 null;
399 WHEN OTHERS THEN
400 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
401 ,p_msg_name => G_UNEXPECTED_ERROR
402 ,p_token1 => G_SQLCODE_TOKEN
403 ,p_token1_value => SQLCODE
404 ,p_token2 => G_SQLERRM_TOKEN
405 ,p_token2_value => SQLERRM);
406 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
407 END validate_id;
408 -------------------------------------
409 -- Validate_Attributes for: KLE_ID --
410 -------------------------------------
411 PROCEDURE validate_kle_id(
412 x_return_status OUT NOCOPY VARCHAR2,
413 p_kle_id IN NUMBER) IS
414 BEGIN
415 x_return_status := OKL_API.G_RET_STS_SUCCESS;
416 IF (p_kle_id = OKL_API.G_MISS_NUM OR
417 p_kle_id IS NULL)
418 THEN
419 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'Counter');
420 x_return_status := OKL_API.G_RET_STS_ERROR;
421 RAISE G_EXCEPTION_HALT_VALIDATION;
422 END IF;
423 EXCEPTION
424 WHEN G_EXCEPTION_HALT_VALIDATION THEN
425 null;
426 WHEN OTHERS THEN
427 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
428 ,p_msg_name => G_UNEXPECTED_ERROR
429 ,p_token1 => G_SQLCODE_TOKEN
430 ,p_token1_value => SQLCODE
431 ,p_token2 => G_SQLERRM_TOKEN
432 ,p_token2_value => SQLERRM);
433 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
434 END validate_kle_id;
435 -------------------------------------
436 -- Validate_Attributes for: CLG_ID --
437 -------------------------------------
438 PROCEDURE validate_clg_id(
439 x_return_status OUT NOCOPY VARCHAR2,
440 p_clg_id IN NUMBER) IS
441 BEGIN
442 x_return_status := OKL_API.G_RET_STS_SUCCESS;
443 IF (p_clg_id = OKL_API.G_MISS_NUM OR
444 p_clg_id IS NULL)
445 THEN
446 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'clg_id');
447 x_return_status := OKL_API.G_RET_STS_ERROR;
448 RAISE G_EXCEPTION_HALT_VALIDATION;
449 END IF;
450 EXCEPTION
451 WHEN G_EXCEPTION_HALT_VALIDATION THEN
452 null;
453 WHEN OTHERS THEN
454 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
455 ,p_msg_name => G_UNEXPECTED_ERROR
456 ,p_token1 => G_SQLCODE_TOKEN
457 ,p_token1_value => SQLCODE
458 ,p_token2 => G_SQLERRM_TOKEN
459 ,p_token2_value => SQLERRM);
460 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
461 END validate_clg_id;
462 ----------------------------------------------------
463 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
464 ----------------------------------------------------
465 PROCEDURE validate_object_version_number(
466 x_return_status OUT NOCOPY VARCHAR2,
467 p_object_version_number IN NUMBER) IS
468 BEGIN
469 x_return_status := OKL_API.G_RET_STS_SUCCESS;
470 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
471 p_object_version_number IS NULL)
472 THEN
473 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
474 x_return_status := OKL_API.G_RET_STS_ERROR;
475 RAISE G_EXCEPTION_HALT_VALIDATION;
476 END IF;
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_VALIDATION THEN
479 null;
480 WHEN OTHERS THEN
481 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
482 ,p_msg_name => G_UNEXPECTED_ERROR
483 ,p_token1 => G_SQLCODE_TOKEN
484 ,p_token1_value => SQLCODE
485 ,p_token2 => G_SQLERRM_TOKEN
486 ,p_token2_value => SQLERRM);
487 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
488 END validate_object_version_number;
489 ---------------------------------------------------------------------------
490 -- FUNCTION Validate_Attributes
491 ---------------------------------------------------------------------------
492 --------------------------------------------------
493 -- Validate_Attributes for:OKL_CNTR_LVLNG_LNS_V --
494 --------------------------------------------------
495 FUNCTION Validate_Attributes (
496 p_cllv_rec IN cllv_rec_type
497 ) RETURN VARCHAR2 IS
498 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
499 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
500 BEGIN
501 -----------------------------
502 -- Column Level Validation --
503 -----------------------------
504 -- ***
505 -- id
506 -- ***
507 validate_id(x_return_status, p_cllv_rec.id);
508 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
509 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
510 l_return_status := x_return_status;
511 END IF;
512 END IF;
513
514 -- ***
515 -- kle_id
516 -- ***
517 validate_kle_id(x_return_status, p_cllv_rec.kle_id);
518 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
519 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
520 l_return_status := x_return_status;
521 END IF;
522 END IF;
523
524 -- ***
525 -- clg_id
526 -- ***
527 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
528 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
529 l_return_status := x_return_status;
530 END IF;
531 END IF;
532
533 -- ***
534 -- object_version_number
535 -- ***
536 validate_object_version_number(x_return_status, p_cllv_rec.object_version_number);
537 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
538 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
539 l_return_status := x_return_status;
540 END IF;
541 END IF;
542
543 RETURN(l_return_status);
544 EXCEPTION
545 WHEN G_EXCEPTION_HALT_VALIDATION THEN
546 RETURN(l_return_status);
547 WHEN OTHERS THEN
548 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
549 ,p_msg_name => G_UNEXPECTED_ERROR
550 ,p_token1 => G_SQLCODE_TOKEN
551 ,p_token1_value => SQLCODE
552 ,p_token2 => G_SQLERRM_TOKEN
553 ,p_token2_value => SQLERRM);
554 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
555 RETURN(l_return_status);
556 END Validate_Attributes;
557 ---------------------------------------------------------------------------
558 -- PROCEDURE Validate_Record
559 ---------------------------------------------------------------------------
560 ----------------------------------------------
561 -- Validate Record for:OKL_CNTR_LVLNG_LNS_V --
562 ----------------------------------------------
563 FUNCTION Validate_Record (
564 p_cllv_rec IN cllv_rec_type,
565 p_db_cllv_rec IN cllv_rec_type
566 ) RETURN VARCHAR2 IS
567 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
568 ------------------------------------
569 -- FUNCTION validate_foreign_keys --
570 ------------------------------------
571 FUNCTION validate_foreign_keys (
572 p_cllv_rec IN cllv_rec_type,
573 p_db_cllv_rec IN cllv_rec_type
574 ) RETURN VARCHAR2 IS
575 item_not_found_error EXCEPTION;
576 CURSOR okl_cllv_clg_fk_csr (p_id IN NUMBER) IS
577 SELECT 'x'
578 FROM Okl_Cntr_Lvlng_Grps_B
579 WHERE okl_cntr_lvlng_grps_b.id = p_id;
580 l_okl_cllv_clg_fk okl_cllv_clg_fk_csr%ROWTYPE;
581
582 CURSOR okl_cllv_kle_fk_csr (p_id IN NUMBER) IS
583 SELECT 'x'
584 FROM Okl_K_Lines
585 WHERE okl_k_lines.id = p_id;
586 l_okl_cllv_kle_fk okl_cllv_kle_fk_csr%ROWTYPE;
587
588 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
589 l_row_notfound BOOLEAN := TRUE;
590 BEGIN
591 /* IF ((p_cllv_rec.KLE_ID IS NOT NULL)
592 AND
593 (p_cllv_rec.KLE_ID <> p_db_cllv_rec.KLE_ID))
594 THEN
595 OPEN okl_cllv_kle_fk_csr (p_cllv_rec.KLE_ID);
596 FETCH okl_cllv_kle_fk_csr INTO l_okl_cllv_kle_fk;
597 l_row_notfound := okl_cllv_kle_fk_csr%NOTFOUND;
598 CLOSE okl_cllv_kle_fk_csr;
599 IF (l_row_notfound) THEN
600 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'KLE_ID');
601 RAISE item_not_found_error;
602 END IF;
603 END IF;
604 */ IF ((p_cllv_rec.CLG_ID IS NOT NULL)
605 AND
606 (p_cllv_rec.CLG_ID <> p_db_cllv_rec.CLG_ID))
607 THEN
608 OPEN okl_cllv_clg_fk_csr (p_cllv_rec.CLG_ID);
609 FETCH okl_cllv_clg_fk_csr INTO l_okl_cllv_clg_fk;
610 l_row_notfound := okl_cllv_clg_fk_csr%NOTFOUND;
611 CLOSE okl_cllv_clg_fk_csr;
612 IF (l_row_notfound) THEN
613 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLG_ID');
614 RAISE item_not_found_error;
615 END IF;
616 END IF;
617 RETURN (l_return_status);
618 EXCEPTION
619 WHEN item_not_found_error THEN
620 l_return_status := OKL_API.G_RET_STS_ERROR;
621 RETURN (l_return_status);
622 END validate_foreign_keys;
623 BEGIN
624 l_return_status := validate_foreign_keys(p_cllv_rec, p_db_cllv_rec);
625 RETURN (l_return_status);
626 END Validate_Record;
627 FUNCTION Validate_Record (
628 p_cllv_rec IN cllv_rec_type
629 ) RETURN VARCHAR2 IS
630 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
631 l_db_cllv_rec cllv_rec_type := get_rec(p_cllv_rec);
632 BEGIN
633 l_return_status := Validate_Record(p_cllv_rec => p_cllv_rec,
634 p_db_cllv_rec => l_db_cllv_rec);
635 RETURN (l_return_status);
636 END Validate_Record;
637
638 ---------------------------------------------------------------------------
639 -- PROCEDURE Migrate
640 ---------------------------------------------------------------------------
641 PROCEDURE migrate (
642 p_from IN cllv_rec_type,
643 p_to IN OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type
644 ) IS
645 BEGIN
646 p_to.id := p_from.id;
647 p_to.kle_id := p_from.kle_id;
648 p_to.clg_id := p_from.clg_id;
649 p_to.object_version_number := p_from.object_version_number;
650 p_to.attribute_category := p_from.attribute_category;
651 p_to.attribute1 := p_from.attribute1;
652 p_to.attribute2 := p_from.attribute2;
653 p_to.attribute3 := p_from.attribute3;
654 p_to.attribute4 := p_from.attribute4;
655 p_to.attribute5 := p_from.attribute5;
656 p_to.attribute6 := p_from.attribute6;
657 p_to.attribute7 := p_from.attribute7;
658 p_to.attribute8 := p_from.attribute8;
659 p_to.attribute9 := p_from.attribute9;
660 p_to.attribute10 := p_from.attribute10;
661 p_to.attribute11 := p_from.attribute11;
662 p_to.attribute12 := p_from.attribute12;
663 p_to.attribute13 := p_from.attribute13;
664 p_to.attribute14 := p_from.attribute14;
665 p_to.attribute15 := p_from.attribute15;
666 p_to.created_by := p_from.created_by;
667 p_to.creation_date := p_from.creation_date;
668 p_to.last_updated_by := p_from.last_updated_by;
669 p_to.last_update_date := p_from.last_update_date;
670 p_to.last_update_login := p_from.last_update_login;
671 END migrate;
672 PROCEDURE migrate (
673 p_from IN okl_cntr_lvlng_lns_b_rec_type,
674 p_to IN OUT NOCOPY cllv_rec_type
675 ) IS
676 BEGIN
677 p_to.id := p_from.id;
678 p_to.kle_id := p_from.kle_id;
679 p_to.clg_id := p_from.clg_id;
680 p_to.object_version_number := p_from.object_version_number;
681 p_to.attribute_category := p_from.attribute_category;
682 p_to.attribute1 := p_from.attribute1;
683 p_to.attribute2 := p_from.attribute2;
684 p_to.attribute3 := p_from.attribute3;
685 p_to.attribute4 := p_from.attribute4;
686 p_to.attribute5 := p_from.attribute5;
687 p_to.attribute6 := p_from.attribute6;
688 p_to.attribute7 := p_from.attribute7;
689 p_to.attribute8 := p_from.attribute8;
690 p_to.attribute9 := p_from.attribute9;
691 p_to.attribute10 := p_from.attribute10;
692 p_to.attribute11 := p_from.attribute11;
693 p_to.attribute12 := p_from.attribute12;
694 p_to.attribute13 := p_from.attribute13;
695 p_to.attribute14 := p_from.attribute14;
696 p_to.attribute15 := p_from.attribute15;
697 p_to.created_by := p_from.created_by;
698 p_to.creation_date := p_from.creation_date;
699 p_to.last_updated_by := p_from.last_updated_by;
700 p_to.last_update_date := p_from.last_update_date;
701 p_to.last_update_login := p_from.last_update_login;
702 END migrate;
703 ---------------------------------------------------------------------------
704 -- PROCEDURE validate_row
705 ---------------------------------------------------------------------------
706 -------------------------------------------
707 -- validate_row for:OKL_CNTR_LVLNG_LNS_V --
708 -------------------------------------------
709 PROCEDURE validate_row(
710 p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_count OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2,
715 p_cllv_rec IN cllv_rec_type) IS
716
717 l_api_version CONSTANT NUMBER := 1;
718 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
719 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
720 l_cllv_rec cllv_rec_type := p_cllv_rec;
721 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
722 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
723 BEGIN
724 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
725 G_PKG_NAME,
726 p_init_msg_list,
727 l_api_version,
728 p_api_version,
729 '_PVT',
730 x_return_status);
731 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
732 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
733 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
734 RAISE OKL_API.G_EXCEPTION_ERROR;
735 END IF;
736 --- Validate all non-missing attributes (Item Level Validation)
737 l_return_status := Validate_Attributes(l_cllv_rec);
738 --- If any errors happen abort API
739 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
740 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
741 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743 END IF;
744 l_return_status := Validate_Record(l_cllv_rec);
745 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
746 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
747 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
748 RAISE OKL_API.G_EXCEPTION_ERROR;
749 END IF;
750 x_return_status := l_return_status;
751 EXCEPTION
752 WHEN OKL_API.G_EXCEPTION_ERROR THEN
753 x_return_status := OKL_API.HANDLE_EXCEPTIONS
754 (
755 l_api_name,
756 G_PKG_NAME,
757 'OKL_API.G_RET_STS_ERROR',
758 x_msg_count,
759 x_msg_data,
760 '_PVT'
761 );
762 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
763 x_return_status := OKL_API.HANDLE_EXCEPTIONS
764 (
765 l_api_name,
766 G_PKG_NAME,
767 'OKL_API.G_RET_STS_UNEXP_ERROR',
768 x_msg_count,
769 x_msg_data,
770 '_PVT'
771 );
772 WHEN OTHERS THEN
773 x_return_status := OKL_API.HANDLE_EXCEPTIONS
774 (
775 l_api_name,
776 G_PKG_NAME,
777 'OTHERS',
778 x_msg_count,
779 x_msg_data,
780 '_PVT'
781 );
782 END validate_row;
783 ------------------------------------------------------
784 -- PL/SQL TBL validate_row for:OKL_CNTR_LVLNG_LNS_V --
785 ------------------------------------------------------
786 PROCEDURE validate_row(
787 p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_msg_count OUT NOCOPY NUMBER,
791 x_msg_data OUT NOCOPY VARCHAR2,
792 p_cllv_tbl IN cllv_tbl_type,
793 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
794
795 l_api_version CONSTANT NUMBER := 1;
796 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
797 i NUMBER := 0;
798 BEGIN
799 OKL_API.init_msg_list(p_init_msg_list);
800 -- Make sure PL/SQL table has records in it before passing
801 IF (p_cllv_tbl.COUNT > 0) THEN
802 i := p_cllv_tbl.FIRST;
803 LOOP
804 DECLARE
805 l_error_rec OKL_API.ERROR_REC_TYPE;
806 BEGIN
807 l_error_rec.api_name := l_api_name;
808 l_error_rec.api_package := G_PKG_NAME;
809 l_error_rec.idx := i;
810 validate_row (
811 p_api_version => p_api_version,
812 p_init_msg_list => OKL_API.G_FALSE,
813 x_return_status => l_error_rec.error_type,
814 x_msg_count => l_error_rec.msg_count,
815 x_msg_data => l_error_rec.msg_data,
816 p_cllv_rec => p_cllv_tbl(i));
817 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
818 l_error_rec.sqlcode := SQLCODE;
819 load_error_tbl(l_error_rec, px_error_tbl);
820 ELSE
821 x_msg_count := l_error_rec.msg_count;
822 x_msg_data := l_error_rec.msg_data;
823 END IF;
824 EXCEPTION
825 WHEN OKL_API.G_EXCEPTION_ERROR THEN
826 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
827 l_error_rec.sqlcode := SQLCODE;
828 load_error_tbl(l_error_rec, px_error_tbl);
829 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
830 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
831 l_error_rec.sqlcode := SQLCODE;
832 load_error_tbl(l_error_rec, px_error_tbl);
833 WHEN OTHERS THEN
834 l_error_rec.error_type := 'OTHERS';
835 l_error_rec.sqlcode := SQLCODE;
836 load_error_tbl(l_error_rec, px_error_tbl);
837 END;
838 EXIT WHEN (i = p_cllv_tbl.LAST);
839 i := p_cllv_tbl.NEXT(i);
840 END LOOP;
841 END IF;
842 -- Loop through the error_tbl to find the error with the highest severity
843 -- and return it.
844 x_return_status := find_highest_exception(px_error_tbl);
845 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
846 EXCEPTION
847 WHEN OKL_API.G_EXCEPTION_ERROR THEN
848 x_return_status := OKL_API.HANDLE_EXCEPTIONS
849 (
850 l_api_name,
851 G_PKG_NAME,
852 'OKL_API.G_RET_STS_ERROR',
853 x_msg_count,
854 x_msg_data,
855 '_PVT'
856 );
857 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
858 x_return_status := OKL_API.HANDLE_EXCEPTIONS
859 (
860 l_api_name,
861 G_PKG_NAME,
862 'OKL_API.G_RET_STS_UNEXP_ERROR',
863 x_msg_count,
864 x_msg_data,
865 '_PVT'
866 );
867 WHEN OTHERS THEN
868 x_return_status := OKL_API.HANDLE_EXCEPTIONS
869 (
870 l_api_name,
871 G_PKG_NAME,
872 'OTHERS',
873 x_msg_count,
874 x_msg_data,
875 '_PVT'
876 );
877 END validate_row;
878
879 ------------------------------------------------------
880 -- PL/SQL TBL validate_row for:OKL_CNTR_LVLNG_LNS_V --
881 ------------------------------------------------------
882 PROCEDURE validate_row(
883 p_api_version IN NUMBER,
884 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
888 p_cllv_tbl IN cllv_tbl_type) IS
889
890 l_api_version CONSTANT NUMBER := 1;
891 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
892 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
893 l_error_tbl OKL_API.ERROR_TBL_TYPE;
894 BEGIN
895 OKL_API.init_msg_list(p_init_msg_list);
896 -- Make sure PL/SQL table has records in it before passing
897 IF (p_cllv_tbl.COUNT > 0) THEN
898 validate_row (
899 p_api_version => p_api_version,
900 p_init_msg_list => OKL_API.G_FALSE,
901 x_return_status => x_return_status,
902 x_msg_count => x_msg_count,
903 x_msg_data => x_msg_data,
904 p_cllv_tbl => p_cllv_tbl,
905 px_error_tbl => l_error_tbl);
906 END IF;
907 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
908 EXCEPTION
909 WHEN OKL_API.G_EXCEPTION_ERROR THEN
910 x_return_status := OKL_API.HANDLE_EXCEPTIONS
911 (
912 l_api_name,
913 G_PKG_NAME,
914 'OKL_API.G_RET_STS_ERROR',
915 x_msg_count,
916 x_msg_data,
917 '_PVT'
918 );
919 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
920 x_return_status := OKL_API.HANDLE_EXCEPTIONS
921 (
922 l_api_name,
923 G_PKG_NAME,
924 'OKL_API.G_RET_STS_UNEXP_ERROR',
925 x_msg_count,
926 x_msg_data,
927 '_PVT'
928 );
929 WHEN OTHERS THEN
930 x_return_status := OKL_API.HANDLE_EXCEPTIONS
931 (
932 l_api_name,
933 G_PKG_NAME,
934 'OTHERS',
935 x_msg_count,
936 x_msg_data,
937 '_PVT'
938 );
939 END validate_row;
940
941 ---------------------------------------------------------------------------
942 -- PROCEDURE insert_row
943 ---------------------------------------------------------------------------
944 -----------------------------------------
945 -- insert_row for:OKL_CNTR_LVLNG_LNS_B --
946 -----------------------------------------
947 PROCEDURE insert_row(
948 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
949 x_return_status OUT NOCOPY VARCHAR2,
950 x_msg_count OUT NOCOPY NUMBER,
951 x_msg_data OUT NOCOPY VARCHAR2,
952 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
953 x_okl_cntr_lvlng_lns_b_rec OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type) IS
954
955 l_api_version CONSTANT NUMBER := 1;
956 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
957 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
958 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type := p_okl_cntr_lvlng_lns_b_rec;
959 l_def_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
960 ---------------------------------------------
961 -- Set_Attributes for:OKL_CNTR_LVLNG_LNS_B --
962 ---------------------------------------------
963 FUNCTION Set_Attributes (
964 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
965 x_okl_cntr_lvlng_lns_b_rec OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type
966 ) RETURN VARCHAR2 IS
967 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
968 BEGIN
969 x_okl_cntr_lvlng_lns_b_rec := p_okl_cntr_lvlng_lns_b_rec;
970 RETURN(l_return_status);
971 END Set_Attributes;
972 BEGIN
973 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
974 p_init_msg_list,
975 '_PVT',
976 x_return_status);
977 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
978 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
980 RAISE OKL_API.G_EXCEPTION_ERROR;
981 END IF;
982 --- Setting item atributes
983 l_return_status := Set_Attributes(
984 p_okl_cntr_lvlng_lns_b_rec, -- IN
985 l_okl_cntr_lvlng_lns_b_rec); -- OUT
986 --- If any errors happen abort API
987 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
988 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
989 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
990 RAISE OKL_API.G_EXCEPTION_ERROR;
991 END IF;
992 INSERT INTO OKL_CNTR_LVLNG_LNS_B(
993 id,
994 kle_id,
995 clg_id,
996 object_version_number,
997 attribute_category,
998 attribute1,
999 attribute2,
1000 attribute3,
1001 attribute4,
1002 attribute5,
1003 attribute6,
1004 attribute7,
1005 attribute8,
1006 attribute9,
1007 attribute10,
1008 attribute11,
1009 attribute12,
1010 attribute13,
1011 attribute14,
1012 attribute15,
1013 created_by,
1014 creation_date,
1015 last_updated_by,
1016 last_update_date,
1017 last_update_login)
1018 VALUES (
1019 l_okl_cntr_lvlng_lns_b_rec.id,
1020 l_okl_cntr_lvlng_lns_b_rec.kle_id,
1021 l_okl_cntr_lvlng_lns_b_rec.clg_id,
1022 l_okl_cntr_lvlng_lns_b_rec.object_version_number,
1023 l_okl_cntr_lvlng_lns_b_rec.attribute_category,
1024 l_okl_cntr_lvlng_lns_b_rec.attribute1,
1025 l_okl_cntr_lvlng_lns_b_rec.attribute2,
1026 l_okl_cntr_lvlng_lns_b_rec.attribute3,
1027 l_okl_cntr_lvlng_lns_b_rec.attribute4,
1028 l_okl_cntr_lvlng_lns_b_rec.attribute5,
1029 l_okl_cntr_lvlng_lns_b_rec.attribute6,
1030 l_okl_cntr_lvlng_lns_b_rec.attribute7,
1031 l_okl_cntr_lvlng_lns_b_rec.attribute8,
1032 l_okl_cntr_lvlng_lns_b_rec.attribute9,
1033 l_okl_cntr_lvlng_lns_b_rec.attribute10,
1034 l_okl_cntr_lvlng_lns_b_rec.attribute11,
1035 l_okl_cntr_lvlng_lns_b_rec.attribute12,
1036 l_okl_cntr_lvlng_lns_b_rec.attribute13,
1037 l_okl_cntr_lvlng_lns_b_rec.attribute14,
1038 l_okl_cntr_lvlng_lns_b_rec.attribute15,
1039 l_okl_cntr_lvlng_lns_b_rec.created_by,
1040 l_okl_cntr_lvlng_lns_b_rec.creation_date,
1041 l_okl_cntr_lvlng_lns_b_rec.last_updated_by,
1042 l_okl_cntr_lvlng_lns_b_rec.last_update_date,
1043 l_okl_cntr_lvlng_lns_b_rec.last_update_login);
1044 -- Set OUT values
1045 x_okl_cntr_lvlng_lns_b_rec := l_okl_cntr_lvlng_lns_b_rec;
1046 x_return_status := l_return_status;
1047 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1048 EXCEPTION
1049 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1050 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1051 (
1052 l_api_name,
1053 G_PKG_NAME,
1054 'OKL_API.G_RET_STS_ERROR',
1055 x_msg_count,
1056 x_msg_data,
1057 '_PVT'
1058 );
1059 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1060 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1061 (
1062 l_api_name,
1063 G_PKG_NAME,
1064 'OKL_API.G_RET_STS_UNEXP_ERROR',
1065 x_msg_count,
1066 x_msg_data,
1067 '_PVT'
1068 );
1069 WHEN OTHERS THEN
1070 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1071 (
1072 l_api_name,
1073 G_PKG_NAME,
1074 'OTHERS',
1075 x_msg_count,
1076 x_msg_data,
1077 '_PVT'
1078 );
1079 END insert_row;
1080 ------------------------------------------
1081 -- insert_row for :OKL_CNTR_LVLNG_LNS_V --
1082 ------------------------------------------
1083 PROCEDURE insert_row(
1084 p_api_version IN NUMBER,
1085 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1086 x_return_status OUT NOCOPY VARCHAR2,
1087 x_msg_count OUT NOCOPY NUMBER,
1088 x_msg_data OUT NOCOPY VARCHAR2,
1089 p_cllv_rec IN cllv_rec_type,
1090 x_cllv_rec OUT NOCOPY cllv_rec_type) IS
1091
1092 l_api_version CONSTANT NUMBER := 1;
1093 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1094 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1095 l_cllv_rec cllv_rec_type := p_cllv_rec;
1096 l_def_cllv_rec cllv_rec_type;
1097 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1098 lx_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1099 -------------------------------
1100 -- FUNCTION fill_who_columns --
1101 -------------------------------
1102 FUNCTION fill_who_columns (
1103 p_cllv_rec IN cllv_rec_type
1104 ) RETURN cllv_rec_type IS
1105 l_cllv_rec cllv_rec_type := p_cllv_rec;
1106 BEGIN
1107 l_cllv_rec.CREATION_DATE := SYSDATE;
1108 l_cllv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1109 l_cllv_rec.LAST_UPDATE_DATE := l_cllv_rec.CREATION_DATE;
1110 l_cllv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1111 l_cllv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1112 RETURN(l_cllv_rec);
1113 END fill_who_columns;
1114 ---------------------------------------------
1115 -- Set_Attributes for:OKL_CNTR_LVLNG_LNS_V --
1116 ---------------------------------------------
1117 FUNCTION Set_Attributes (
1118 p_cllv_rec IN cllv_rec_type,
1119 x_cllv_rec OUT NOCOPY cllv_rec_type
1120 ) RETURN VARCHAR2 IS
1121 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1122 BEGIN
1123 x_cllv_rec := p_cllv_rec;
1124 x_cllv_rec.OBJECT_VERSION_NUMBER := 1;
1125 RETURN(l_return_status);
1126 END Set_Attributes;
1127 BEGIN
1128 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1129 G_PKG_NAME,
1130 p_init_msg_list,
1131 l_api_version,
1132 p_api_version,
1133 '_PVT',
1134 x_return_status);
1135 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1136 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1137 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1138 RAISE OKL_API.G_EXCEPTION_ERROR;
1139 END IF;
1140 l_cllv_rec := null_out_defaults(p_cllv_rec);
1141 -- Set primary key value
1142 l_cllv_rec.ID := get_seq_id;
1143 -- Setting item attributes
1144 l_return_Status := Set_Attributes(
1145 l_cllv_rec, -- IN
1146 l_def_cllv_rec); -- OUT
1147 --- If any errors happen abort API
1148 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1149 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1150 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1151 RAISE OKL_API.G_EXCEPTION_ERROR;
1152 END IF;
1153 l_def_cllv_rec := fill_who_columns(l_def_cllv_rec);
1154 --- Validate all non-missing attributes (Item Level Validation)
1155 l_return_status := Validate_Attributes(l_def_cllv_rec);
1156 --- If any errors happen abort API
1157 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1158 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1159 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1160 RAISE OKL_API.G_EXCEPTION_ERROR;
1161 END IF;
1162 l_return_status := Validate_Record(l_def_cllv_rec);
1163 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1165 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1166 RAISE OKL_API.G_EXCEPTION_ERROR;
1167 END IF;
1168 -----------------------------------------
1169 -- Move VIEW record to "Child" records --
1170 -----------------------------------------
1171 migrate(l_def_cllv_rec, l_okl_cntr_lvlng_lns_b_rec);
1172 -----------------------------------------------
1173 -- Call the INSERT_ROW for each child record --
1174 -----------------------------------------------
1175 insert_row(
1176 p_init_msg_list,
1177 l_return_status,
1178 x_msg_count,
1179 x_msg_data,
1180 l_okl_cntr_lvlng_lns_b_rec,
1181 lx_okl_cntr_lvlng_lns_b_rec
1182 );
1183 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1184 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1185 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_ERROR;
1187 END IF;
1188 migrate(lx_okl_cntr_lvlng_lns_b_rec, l_def_cllv_rec);
1189 -- Set OUT values
1190 x_cllv_rec := l_def_cllv_rec;
1191 x_return_status := l_return_status;
1192 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1193 EXCEPTION
1194 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1195 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1196 (
1197 l_api_name,
1198 G_PKG_NAME,
1199 'OKL_API.G_RET_STS_ERROR',
1200 x_msg_count,
1201 x_msg_data,
1202 '_PVT'
1203 );
1204 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1205 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1206 (
1207 l_api_name,
1208 G_PKG_NAME,
1209 'OKL_API.G_RET_STS_UNEXP_ERROR',
1210 x_msg_count,
1211 x_msg_data,
1212 '_PVT'
1213 );
1214 WHEN OTHERS THEN
1215 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1216 (
1217 l_api_name,
1218 G_PKG_NAME,
1219 'OTHERS',
1220 x_msg_count,
1221 x_msg_data,
1222 '_PVT'
1223 );
1224 END insert_row;
1225 ----------------------------------------
1226 -- PL/SQL TBL insert_row for:CLLV_TBL --
1227 ----------------------------------------
1228 PROCEDURE insert_row(
1229 p_api_version IN NUMBER,
1230 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1231 x_return_status OUT NOCOPY VARCHAR2,
1232 x_msg_count OUT NOCOPY NUMBER,
1233 x_msg_data OUT NOCOPY VARCHAR2,
1234 p_cllv_tbl IN cllv_tbl_type,
1235 x_cllv_tbl OUT NOCOPY cllv_tbl_type,
1236 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1237
1238 l_api_version CONSTANT NUMBER := 1;
1239 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1240 i NUMBER := 0;
1241 BEGIN
1242 OKL_API.init_msg_list(p_init_msg_list);
1243 -- Make sure PL/SQL table has records in it before passing
1244 IF (p_cllv_tbl.COUNT > 0) THEN
1245 i := p_cllv_tbl.FIRST;
1246 LOOP
1247 DECLARE
1248 l_error_rec OKL_API.ERROR_REC_TYPE;
1249 BEGIN
1250 l_error_rec.api_name := l_api_name;
1251 l_error_rec.api_package := G_PKG_NAME;
1252 l_error_rec.idx := i;
1253 insert_row (
1254 p_api_version => p_api_version,
1255 p_init_msg_list => OKL_API.G_FALSE,
1256 x_return_status => l_error_rec.error_type,
1257 x_msg_count => l_error_rec.msg_count,
1258 x_msg_data => l_error_rec.msg_data,
1259 p_cllv_rec => p_cllv_tbl(i),
1260 x_cllv_rec => x_cllv_tbl(i));
1261 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1262 l_error_rec.sqlcode := SQLCODE;
1263 load_error_tbl(l_error_rec, px_error_tbl);
1264 ELSE
1265 x_msg_count := l_error_rec.msg_count;
1266 x_msg_data := l_error_rec.msg_data;
1267 END IF;
1268 EXCEPTION
1269 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1270 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1271 l_error_rec.sqlcode := SQLCODE;
1272 load_error_tbl(l_error_rec, px_error_tbl);
1273 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1274 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1275 l_error_rec.sqlcode := SQLCODE;
1276 load_error_tbl(l_error_rec, px_error_tbl);
1277 WHEN OTHERS THEN
1278 l_error_rec.error_type := 'OTHERS';
1279 l_error_rec.sqlcode := SQLCODE;
1280 load_error_tbl(l_error_rec, px_error_tbl);
1281 END;
1282 EXIT WHEN (i = p_cllv_tbl.LAST);
1283 i := p_cllv_tbl.NEXT(i);
1284 END LOOP;
1285 END IF;
1286 -- Loop through the error_tbl to find the error with the highest severity
1287 -- and return it.
1288 x_return_status := find_highest_exception(px_error_tbl);
1289 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1290 EXCEPTION
1291 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1292 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1293 (
1294 l_api_name,
1295 G_PKG_NAME,
1296 'OKL_API.G_RET_STS_ERROR',
1297 x_msg_count,
1298 x_msg_data,
1299 '_PVT'
1300 );
1301 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1302 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1303 (
1304 l_api_name,
1305 G_PKG_NAME,
1306 'OKL_API.G_RET_STS_UNEXP_ERROR',
1307 x_msg_count,
1308 x_msg_data,
1309 '_PVT'
1310 );
1311 WHEN OTHERS THEN
1312 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1313 (
1314 l_api_name,
1315 G_PKG_NAME,
1316 'OTHERS',
1317 x_msg_count,
1318 x_msg_data,
1319 '_PVT'
1320 );
1321 END insert_row;
1322
1323 ----------------------------------------
1324 -- PL/SQL TBL insert_row for:CLLV_TBL --
1325 ----------------------------------------
1326 PROCEDURE insert_row(
1327 p_api_version IN NUMBER,
1328 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 x_msg_count OUT NOCOPY NUMBER,
1331 x_msg_data OUT NOCOPY VARCHAR2,
1332 p_cllv_tbl IN cllv_tbl_type,
1333 x_cllv_tbl OUT NOCOPY cllv_tbl_type) IS
1334
1335 l_api_version CONSTANT NUMBER := 1;
1336 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1337 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1338 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1339 BEGIN
1340 OKL_API.init_msg_list(p_init_msg_list);
1341 -- Make sure PL/SQL table has records in it before passing
1342 IF (p_cllv_tbl.COUNT > 0) THEN
1343 insert_row (
1344 p_api_version => p_api_version,
1345 p_init_msg_list => OKL_API.G_FALSE,
1346 x_return_status => x_return_status,
1347 x_msg_count => x_msg_count,
1348 x_msg_data => x_msg_data,
1349 p_cllv_tbl => p_cllv_tbl,
1350 x_cllv_tbl => x_cllv_tbl,
1351 px_error_tbl => l_error_tbl);
1352 END IF;
1353 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1354 EXCEPTION
1355 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1356 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1357 (
1358 l_api_name,
1359 G_PKG_NAME,
1360 'OKL_API.G_RET_STS_ERROR',
1361 x_msg_count,
1362 x_msg_data,
1363 '_PVT'
1364 );
1365 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1366 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1367 (
1368 l_api_name,
1369 G_PKG_NAME,
1370 'OKL_API.G_RET_STS_UNEXP_ERROR',
1371 x_msg_count,
1372 x_msg_data,
1373 '_PVT'
1374 );
1375 WHEN OTHERS THEN
1376 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1377 (
1378 l_api_name,
1379 G_PKG_NAME,
1380 'OTHERS',
1381 x_msg_count,
1382 x_msg_data,
1383 '_PVT'
1384 );
1385 END insert_row;
1386
1387 ---------------------------------------------------------------------------
1388 -- PROCEDURE lock_row
1389 ---------------------------------------------------------------------------
1390 ---------------------------------------
1391 -- lock_row for:OKL_CNTR_LVLNG_LNS_B --
1392 ---------------------------------------
1393 PROCEDURE lock_row(
1394 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1395 x_return_status OUT NOCOPY VARCHAR2,
1396 x_msg_count OUT NOCOPY NUMBER,
1397 x_msg_data OUT NOCOPY VARCHAR2,
1398 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type) IS
1399
1400 E_Resource_Busy EXCEPTION;
1401 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1402 CURSOR lock_csr (p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type) IS
1403 SELECT OBJECT_VERSION_NUMBER
1404 FROM OKL_CNTR_LVLNG_LNS_B
1405 WHERE ID = p_okl_cntr_lvlng_lns_b_rec.id
1406 AND OBJECT_VERSION_NUMBER = p_okl_cntr_lvlng_lns_b_rec.object_version_number
1407 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1408
1409 CURSOR lchk_csr (p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type) IS
1410 SELECT OBJECT_VERSION_NUMBER
1411 FROM OKL_CNTR_LVLNG_LNS_B
1412 WHERE ID = p_okl_cntr_lvlng_lns_b_rec.id;
1413 l_api_version CONSTANT NUMBER := 1;
1414 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1415 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1416 l_object_version_number OKL_CNTR_LVLNG_LNS_B.OBJECT_VERSION_NUMBER%TYPE;
1417 lc_object_version_number OKL_CNTR_LVLNG_LNS_B.OBJECT_VERSION_NUMBER%TYPE;
1418 l_row_notfound BOOLEAN := FALSE;
1419 lc_row_notfound BOOLEAN := FALSE;
1420 BEGIN
1421 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1422 p_init_msg_list,
1423 '_PVT',
1424 x_return_status);
1425 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1427 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1428 RAISE OKL_API.G_EXCEPTION_ERROR;
1429 END IF;
1430 BEGIN
1431 OPEN lock_csr(p_okl_cntr_lvlng_lns_b_rec);
1432 FETCH lock_csr INTO l_object_version_number;
1433 l_row_notfound := lock_csr%NOTFOUND;
1434 CLOSE lock_csr;
1435 EXCEPTION
1436 WHEN E_Resource_Busy THEN
1437 IF (lock_csr%ISOPEN) THEN
1438 CLOSE lock_csr;
1439 END IF;
1440 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1441 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1442 END;
1443
1444 IF ( l_row_notfound ) THEN
1445 OPEN lchk_csr(p_okl_cntr_lvlng_lns_b_rec);
1446 FETCH lchk_csr INTO lc_object_version_number;
1447 lc_row_notfound := lchk_csr%NOTFOUND;
1448 CLOSE lchk_csr;
1449 END IF;
1450 IF (lc_row_notfound) THEN
1451 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1452 RAISE OKL_API.G_EXCEPTION_ERROR;
1453 ELSIF lc_object_version_number > p_okl_cntr_lvlng_lns_b_rec.object_version_number THEN
1454 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1455 RAISE OKL_API.G_EXCEPTION_ERROR;
1456 ELSIF lc_object_version_number <> p_okl_cntr_lvlng_lns_b_rec.object_version_number THEN
1457 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1458 RAISE OKL_API.G_EXCEPTION_ERROR;
1459 ELSIF lc_object_version_number = -1 THEN
1460 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1461 RAISE OKL_API.G_EXCEPTION_ERROR;
1462 END IF;
1463 x_return_status := l_return_status;
1464 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1465 EXCEPTION
1466 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1467 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1468 (
1469 l_api_name,
1470 G_PKG_NAME,
1471 'OKL_API.G_RET_STS_ERROR',
1472 x_msg_count,
1473 x_msg_data,
1474 '_PVT'
1475 );
1476 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1477 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1478 (
1479 l_api_name,
1480 G_PKG_NAME,
1481 'OKL_API.G_RET_STS_UNEXP_ERROR',
1482 x_msg_count,
1483 x_msg_data,
1484 '_PVT'
1485 );
1486 WHEN OTHERS THEN
1487 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1488 (
1489 l_api_name,
1490 G_PKG_NAME,
1491 'OTHERS',
1492 x_msg_count,
1493 x_msg_data,
1494 '_PVT'
1495 );
1496 END lock_row;
1497 ----------------------------------------
1498 -- lock_row for: OKL_CNTR_LVLNG_LNS_V --
1499 ----------------------------------------
1500 PROCEDURE lock_row(
1501 p_api_version IN NUMBER,
1502 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1503 x_return_status OUT NOCOPY VARCHAR2,
1504 x_msg_count OUT NOCOPY NUMBER,
1505 x_msg_data OUT NOCOPY VARCHAR2,
1506 p_cllv_rec IN cllv_rec_type) IS
1507
1508 l_api_version CONSTANT NUMBER := 1;
1509 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1510 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1511 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1512 BEGIN
1513 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1514 G_PKG_NAME,
1515 p_init_msg_list,
1516 l_api_version,
1517 p_api_version,
1518 '_PVT',
1519 x_return_status);
1520 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1521 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1522 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1523 RAISE OKL_API.G_EXCEPTION_ERROR;
1524 END IF;
1525 -----------------------------------------
1526 -- Move VIEW record to "Child" records --
1527 -----------------------------------------
1528 migrate(p_cllv_rec, l_okl_cntr_lvlng_lns_b_rec);
1529 ---------------------------------------------
1530 -- Call the LOCK_ROW for each child record --
1531 ---------------------------------------------
1532 lock_row(
1533 p_init_msg_list,
1534 l_return_status,
1535 x_msg_count,
1536 x_msg_data,
1537 l_okl_cntr_lvlng_lns_b_rec
1538 );
1539 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1540 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1541 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1542 RAISE OKL_API.G_EXCEPTION_ERROR;
1543 END IF;
1544 x_return_status := l_return_status;
1545 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1546 EXCEPTION
1547 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1548 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1549 (
1550 l_api_name,
1551 G_PKG_NAME,
1552 'OKL_API.G_RET_STS_ERROR',
1553 x_msg_count,
1554 x_msg_data,
1555 '_PVT'
1556 );
1557 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1558 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1559 (
1560 l_api_name,
1561 G_PKG_NAME,
1562 'OKL_API.G_RET_STS_UNEXP_ERROR',
1563 x_msg_count,
1564 x_msg_data,
1565 '_PVT'
1566 );
1567 WHEN OTHERS THEN
1568 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1569 (
1570 l_api_name,
1571 G_PKG_NAME,
1572 'OTHERS',
1573 x_msg_count,
1574 x_msg_data,
1575 '_PVT'
1576 );
1577 END lock_row;
1578 --------------------------------------
1579 -- PL/SQL TBL lock_row for:CLLV_TBL --
1580 --------------------------------------
1581 PROCEDURE lock_row(
1582 p_api_version IN NUMBER,
1583 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1584 x_return_status OUT NOCOPY VARCHAR2,
1585 x_msg_count OUT NOCOPY NUMBER,
1586 x_msg_data OUT NOCOPY VARCHAR2,
1587 p_cllv_tbl IN cllv_tbl_type,
1588 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1589
1590 l_api_version CONSTANT NUMBER := 1;
1591 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1592 i NUMBER := 0;
1593 BEGIN
1594 OKL_API.init_msg_list(p_init_msg_list);
1595 -- Make sure PL/SQL table has recrods in it before passing
1596 IF (p_cllv_tbl.COUNT > 0) THEN
1597 i := p_cllv_tbl.FIRST;
1598 LOOP
1599 DECLARE
1600 l_error_rec OKL_API.ERROR_REC_TYPE;
1601 BEGIN
1602 l_error_rec.api_name := l_api_name;
1603 l_error_rec.api_package := G_PKG_NAME;
1604 l_error_rec.idx := i;
1605 lock_row(
1606 p_api_version => p_api_version,
1607 p_init_msg_list => OKL_API.G_FALSE,
1608 x_return_status => l_error_rec.error_type,
1609 x_msg_count => l_error_rec.msg_count,
1610 x_msg_data => l_error_rec.msg_data,
1611 p_cllv_rec => p_cllv_tbl(i));
1612 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1613 l_error_rec.sqlcode := SQLCODE;
1614 load_error_tbl(l_error_rec, px_error_tbl);
1615 ELSE
1616 x_msg_count := l_error_rec.msg_count;
1617 x_msg_data := l_error_rec.msg_data;
1618 END IF;
1619 EXCEPTION
1620 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1621 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1622 l_error_rec.sqlcode := SQLCODE;
1623 load_error_tbl(l_error_rec, px_error_tbl);
1624 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1625 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1626 l_error_rec.sqlcode := SQLCODE;
1627 load_error_tbl(l_error_rec, px_error_tbl);
1628 WHEN OTHERS THEN
1629 l_error_rec.error_type := 'OTHERS';
1630 l_error_rec.sqlcode := SQLCODE;
1631 load_error_tbl(l_error_rec, px_error_tbl);
1632 END;
1633 EXIT WHEN (i = p_cllv_tbl.LAST);
1634 i := p_cllv_tbl.NEXT(i);
1635 END LOOP;
1636 END IF;
1637 -- Loop through the error_tbl to find the error with the highest severity
1638 -- and return it.
1639 x_return_status := find_highest_exception(px_error_tbl);
1640 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1641 EXCEPTION
1642 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1643 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1644 (
1645 l_api_name,
1646 G_PKG_NAME,
1647 'OKL_API.G_RET_STS_ERROR',
1648 x_msg_count,
1649 x_msg_data,
1650 '_PVT'
1651 );
1652 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1653 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1654 (
1655 l_api_name,
1656 G_PKG_NAME,
1657 'OKL_API.G_RET_STS_UNEXP_ERROR',
1658 x_msg_count,
1659 x_msg_data,
1660 '_PVT'
1661 );
1662 WHEN OTHERS THEN
1663 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1664 (
1665 l_api_name,
1666 G_PKG_NAME,
1667 'OTHERS',
1668 x_msg_count,
1669 x_msg_data,
1670 '_PVT'
1671 );
1672 END lock_row;
1673 --------------------------------------
1674 -- PL/SQL TBL lock_row for:CLLV_TBL --
1675 --------------------------------------
1676 PROCEDURE lock_row(
1677 p_api_version IN NUMBER,
1678 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1679 x_return_status OUT NOCOPY VARCHAR2,
1680 x_msg_count OUT NOCOPY NUMBER,
1681 x_msg_data OUT NOCOPY VARCHAR2,
1682 p_cllv_tbl IN cllv_tbl_type) IS
1683
1684 l_api_version CONSTANT NUMBER := 1;
1685 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1686 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1687 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1688 BEGIN
1689 OKL_API.init_msg_list(p_init_msg_list);
1690 -- Make sure PL/SQL table has recrods in it before passing
1691 IF (p_cllv_tbl.COUNT > 0) THEN
1692 lock_row(
1693 p_api_version => p_api_version,
1694 p_init_msg_list => OKL_API.G_FALSE,
1695 x_return_status => x_return_status,
1696 x_msg_count => x_msg_count,
1697 x_msg_data => x_msg_data,
1698 p_cllv_tbl => p_cllv_tbl,
1699 px_error_tbl => l_error_tbl);
1700 END IF;
1701 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1702 EXCEPTION
1703 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1704 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1705 (
1706 l_api_name,
1707 G_PKG_NAME,
1708 'OKL_API.G_RET_STS_ERROR',
1709 x_msg_count,
1710 x_msg_data,
1711 '_PVT'
1712 );
1713 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1714 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1715 (
1716 l_api_name,
1717 G_PKG_NAME,
1718 'OKL_API.G_RET_STS_UNEXP_ERROR',
1719 x_msg_count,
1720 x_msg_data,
1721 '_PVT'
1722 );
1723 WHEN OTHERS THEN
1724 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1725 (
1726 l_api_name,
1727 G_PKG_NAME,
1728 'OTHERS',
1729 x_msg_count,
1730 x_msg_data,
1731 '_PVT'
1732 );
1733 END lock_row;
1734 ---------------------------------------------------------------------------
1735 -- PROCEDURE update_row
1736 ---------------------------------------------------------------------------
1737 -----------------------------------------
1738 -- update_row for:OKL_CNTR_LVLNG_LNS_B --
1739 -----------------------------------------
1740 PROCEDURE update_row(
1741 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1742 x_return_status OUT NOCOPY VARCHAR2,
1743 x_msg_count OUT NOCOPY NUMBER,
1744 x_msg_data OUT NOCOPY VARCHAR2,
1745 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
1746 x_okl_cntr_lvlng_lns_b_rec OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type) IS
1747
1748 l_api_version CONSTANT NUMBER := 1;
1749 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1750 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1751 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type := p_okl_cntr_lvlng_lns_b_rec;
1752 l_def_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1753 l_row_notfound BOOLEAN := TRUE;
1754 ----------------------------------
1755 -- FUNCTION populate_new_record --
1756 ----------------------------------
1757 FUNCTION populate_new_record (
1758 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
1759 x_okl_cntr_lvlng_lns_b_rec OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type
1760 ) RETURN VARCHAR2 IS
1761 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1762 l_row_notfound BOOLEAN := TRUE;
1763 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1764 BEGIN
1765 x_okl_cntr_lvlng_lns_b_rec := p_okl_cntr_lvlng_lns_b_rec;
1766 -- Get current database values
1767 l_okl_cntr_lvlng_lns_b_rec := get_rec(p_okl_cntr_lvlng_lns_b_rec, l_return_status);
1768 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1769 IF (x_okl_cntr_lvlng_lns_b_rec.id = OKL_API.G_MISS_NUM)
1770 THEN
1771 x_okl_cntr_lvlng_lns_b_rec.id := l_okl_cntr_lvlng_lns_b_rec.id;
1772 END IF;
1773 IF (x_okl_cntr_lvlng_lns_b_rec.kle_id = OKL_API.G_MISS_NUM)
1774 THEN
1775 x_okl_cntr_lvlng_lns_b_rec.kle_id := l_okl_cntr_lvlng_lns_b_rec.kle_id;
1776 END IF;
1777 IF (x_okl_cntr_lvlng_lns_b_rec.clg_id = OKL_API.G_MISS_NUM)
1778 THEN
1779 x_okl_cntr_lvlng_lns_b_rec.clg_id := l_okl_cntr_lvlng_lns_b_rec.clg_id;
1780 END IF;
1781 IF (x_okl_cntr_lvlng_lns_b_rec.object_version_number = OKL_API.G_MISS_NUM)
1782 THEN
1783 x_okl_cntr_lvlng_lns_b_rec.object_version_number := l_okl_cntr_lvlng_lns_b_rec.object_version_number;
1784 END IF;
1785 IF (x_okl_cntr_lvlng_lns_b_rec.attribute_category = OKL_API.G_MISS_CHAR)
1786 THEN
1787 x_okl_cntr_lvlng_lns_b_rec.attribute_category := l_okl_cntr_lvlng_lns_b_rec.attribute_category;
1788 END IF;
1789 IF (x_okl_cntr_lvlng_lns_b_rec.attribute1 = OKL_API.G_MISS_CHAR)
1790 THEN
1791 x_okl_cntr_lvlng_lns_b_rec.attribute1 := l_okl_cntr_lvlng_lns_b_rec.attribute1;
1792 END IF;
1793 IF (x_okl_cntr_lvlng_lns_b_rec.attribute2 = OKL_API.G_MISS_CHAR)
1794 THEN
1795 x_okl_cntr_lvlng_lns_b_rec.attribute2 := l_okl_cntr_lvlng_lns_b_rec.attribute2;
1796 END IF;
1797 IF (x_okl_cntr_lvlng_lns_b_rec.attribute3 = OKL_API.G_MISS_CHAR)
1798 THEN
1799 x_okl_cntr_lvlng_lns_b_rec.attribute3 := l_okl_cntr_lvlng_lns_b_rec.attribute3;
1800 END IF;
1801 IF (x_okl_cntr_lvlng_lns_b_rec.attribute4 = OKL_API.G_MISS_CHAR)
1802 THEN
1803 x_okl_cntr_lvlng_lns_b_rec.attribute4 := l_okl_cntr_lvlng_lns_b_rec.attribute4;
1804 END IF;
1805 IF (x_okl_cntr_lvlng_lns_b_rec.attribute5 = OKL_API.G_MISS_CHAR)
1806 THEN
1807 x_okl_cntr_lvlng_lns_b_rec.attribute5 := l_okl_cntr_lvlng_lns_b_rec.attribute5;
1808 END IF;
1809 IF (x_okl_cntr_lvlng_lns_b_rec.attribute6 = OKL_API.G_MISS_CHAR)
1810 THEN
1811 x_okl_cntr_lvlng_lns_b_rec.attribute6 := l_okl_cntr_lvlng_lns_b_rec.attribute6;
1812 END IF;
1813 IF (x_okl_cntr_lvlng_lns_b_rec.attribute7 = OKL_API.G_MISS_CHAR)
1814 THEN
1815 x_okl_cntr_lvlng_lns_b_rec.attribute7 := l_okl_cntr_lvlng_lns_b_rec.attribute7;
1816 END IF;
1817 IF (x_okl_cntr_lvlng_lns_b_rec.attribute8 = OKL_API.G_MISS_CHAR)
1818 THEN
1819 x_okl_cntr_lvlng_lns_b_rec.attribute8 := l_okl_cntr_lvlng_lns_b_rec.attribute8;
1820 END IF;
1821 IF (x_okl_cntr_lvlng_lns_b_rec.attribute9 = OKL_API.G_MISS_CHAR)
1822 THEN
1823 x_okl_cntr_lvlng_lns_b_rec.attribute9 := l_okl_cntr_lvlng_lns_b_rec.attribute9;
1824 END IF;
1825 IF (x_okl_cntr_lvlng_lns_b_rec.attribute10 = OKL_API.G_MISS_CHAR)
1826 THEN
1827 x_okl_cntr_lvlng_lns_b_rec.attribute10 := l_okl_cntr_lvlng_lns_b_rec.attribute10;
1828 END IF;
1829 IF (x_okl_cntr_lvlng_lns_b_rec.attribute11 = OKL_API.G_MISS_CHAR)
1830 THEN
1831 x_okl_cntr_lvlng_lns_b_rec.attribute11 := l_okl_cntr_lvlng_lns_b_rec.attribute11;
1832 END IF;
1833 IF (x_okl_cntr_lvlng_lns_b_rec.attribute12 = OKL_API.G_MISS_CHAR)
1834 THEN
1835 x_okl_cntr_lvlng_lns_b_rec.attribute12 := l_okl_cntr_lvlng_lns_b_rec.attribute12;
1836 END IF;
1837 IF (x_okl_cntr_lvlng_lns_b_rec.attribute13 = OKL_API.G_MISS_CHAR)
1838 THEN
1839 x_okl_cntr_lvlng_lns_b_rec.attribute13 := l_okl_cntr_lvlng_lns_b_rec.attribute13;
1840 END IF;
1841 IF (x_okl_cntr_lvlng_lns_b_rec.attribute14 = OKL_API.G_MISS_CHAR)
1842 THEN
1843 x_okl_cntr_lvlng_lns_b_rec.attribute14 := l_okl_cntr_lvlng_lns_b_rec.attribute14;
1844 END IF;
1845 IF (x_okl_cntr_lvlng_lns_b_rec.attribute15 = OKL_API.G_MISS_CHAR)
1846 THEN
1847 x_okl_cntr_lvlng_lns_b_rec.attribute15 := l_okl_cntr_lvlng_lns_b_rec.attribute15;
1848 END IF;
1849 IF (x_okl_cntr_lvlng_lns_b_rec.created_by = OKL_API.G_MISS_NUM)
1850 THEN
1851 x_okl_cntr_lvlng_lns_b_rec.created_by := l_okl_cntr_lvlng_lns_b_rec.created_by;
1852 END IF;
1853 IF (x_okl_cntr_lvlng_lns_b_rec.creation_date = OKL_API.G_MISS_DATE)
1854 THEN
1855 x_okl_cntr_lvlng_lns_b_rec.creation_date := l_okl_cntr_lvlng_lns_b_rec.creation_date;
1856 END IF;
1857 IF (x_okl_cntr_lvlng_lns_b_rec.last_updated_by = OKL_API.G_MISS_NUM)
1858 THEN
1859 x_okl_cntr_lvlng_lns_b_rec.last_updated_by := l_okl_cntr_lvlng_lns_b_rec.last_updated_by;
1860 END IF;
1861 IF (x_okl_cntr_lvlng_lns_b_rec.last_update_date = OKL_API.G_MISS_DATE)
1862 THEN
1863 x_okl_cntr_lvlng_lns_b_rec.last_update_date := l_okl_cntr_lvlng_lns_b_rec.last_update_date;
1864 END IF;
1865 IF (x_okl_cntr_lvlng_lns_b_rec.last_update_login = OKL_API.G_MISS_NUM)
1866 THEN
1867 x_okl_cntr_lvlng_lns_b_rec.last_update_login := l_okl_cntr_lvlng_lns_b_rec.last_update_login;
1868 END IF;
1869 END IF;
1870 RETURN(l_return_status);
1871 END populate_new_record;
1872 ---------------------------------------------
1873 -- Set_Attributes for:OKL_CNTR_LVLNG_LNS_B --
1874 ---------------------------------------------
1875 FUNCTION Set_Attributes (
1876 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type,
1877 x_okl_cntr_lvlng_lns_b_rec OUT NOCOPY okl_cntr_lvlng_lns_b_rec_type
1878 ) RETURN VARCHAR2 IS
1879 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1880 BEGIN
1881 x_okl_cntr_lvlng_lns_b_rec := p_okl_cntr_lvlng_lns_b_rec;
1882 x_okl_cntr_lvlng_lns_b_rec.OBJECT_VERSION_NUMBER := p_okl_cntr_lvlng_lns_b_rec.OBJECT_VERSION_NUMBER + 1;
1883 RETURN(l_return_status);
1884 END Set_Attributes;
1885 BEGIN
1886 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1887 p_init_msg_list,
1888 '_PVT',
1889 x_return_status);
1890 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1891 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1892 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1893 RAISE OKL_API.G_EXCEPTION_ERROR;
1894 END IF;
1895 --- Setting item attributes
1896 l_return_status := Set_Attributes(
1897 p_okl_cntr_lvlng_lns_b_rec, -- IN
1898 l_okl_cntr_lvlng_lns_b_rec); -- OUT
1899 --- If any errors happen abort API
1900 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1901 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1902 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1903 RAISE OKL_API.G_EXCEPTION_ERROR;
1904 END IF;
1905 l_return_status := populate_new_record(l_okl_cntr_lvlng_lns_b_rec, l_def_okl_cntr_lvlng_lns_b_rec);
1906 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1907 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1908 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1909 RAISE OKL_API.G_EXCEPTION_ERROR;
1910 END IF;
1911 UPDATE OKL_CNTR_LVLNG_LNS_B
1912 SET KLE_ID = l_def_okl_cntr_lvlng_lns_b_rec.kle_id,
1913 CLG_ID = l_def_okl_cntr_lvlng_lns_b_rec.clg_id,
1914 OBJECT_VERSION_NUMBER = l_def_okl_cntr_lvlng_lns_b_rec.object_version_number,
1915 ATTRIBUTE_CATEGORY = l_def_okl_cntr_lvlng_lns_b_rec.attribute_category,
1916 ATTRIBUTE1 = l_def_okl_cntr_lvlng_lns_b_rec.attribute1,
1917 ATTRIBUTE2 = l_def_okl_cntr_lvlng_lns_b_rec.attribute2,
1918 ATTRIBUTE3 = l_def_okl_cntr_lvlng_lns_b_rec.attribute3,
1919 ATTRIBUTE4 = l_def_okl_cntr_lvlng_lns_b_rec.attribute4,
1920 ATTRIBUTE5 = l_def_okl_cntr_lvlng_lns_b_rec.attribute5,
1921 ATTRIBUTE6 = l_def_okl_cntr_lvlng_lns_b_rec.attribute6,
1922 ATTRIBUTE7 = l_def_okl_cntr_lvlng_lns_b_rec.attribute7,
1923 ATTRIBUTE8 = l_def_okl_cntr_lvlng_lns_b_rec.attribute8,
1924 ATTRIBUTE9 = l_def_okl_cntr_lvlng_lns_b_rec.attribute9,
1925 ATTRIBUTE10 = l_def_okl_cntr_lvlng_lns_b_rec.attribute10,
1926 ATTRIBUTE11 = l_def_okl_cntr_lvlng_lns_b_rec.attribute11,
1927 ATTRIBUTE12 = l_def_okl_cntr_lvlng_lns_b_rec.attribute12,
1928 ATTRIBUTE13 = l_def_okl_cntr_lvlng_lns_b_rec.attribute13,
1929 ATTRIBUTE14 = l_def_okl_cntr_lvlng_lns_b_rec.attribute14,
1930 ATTRIBUTE15 = l_def_okl_cntr_lvlng_lns_b_rec.attribute15,
1931 CREATED_BY = l_def_okl_cntr_lvlng_lns_b_rec.created_by,
1932 CREATION_DATE = l_def_okl_cntr_lvlng_lns_b_rec.creation_date,
1933 LAST_UPDATED_BY = l_def_okl_cntr_lvlng_lns_b_rec.last_updated_by,
1934 LAST_UPDATE_DATE = l_def_okl_cntr_lvlng_lns_b_rec.last_update_date,
1935 LAST_UPDATE_LOGIN = l_def_okl_cntr_lvlng_lns_b_rec.last_update_login
1936 WHERE ID = l_def_okl_cntr_lvlng_lns_b_rec.id;
1937
1938 x_okl_cntr_lvlng_lns_b_rec := l_okl_cntr_lvlng_lns_b_rec;
1939 x_return_status := l_return_status;
1940 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1941 EXCEPTION
1942 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1943 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1944 (
1945 l_api_name,
1946 G_PKG_NAME,
1947 'OKL_API.G_RET_STS_ERROR',
1948 x_msg_count,
1949 x_msg_data,
1950 '_PVT'
1951 );
1952 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1953 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1954 (
1955 l_api_name,
1956 G_PKG_NAME,
1957 'OKL_API.G_RET_STS_UNEXP_ERROR',
1958 x_msg_count,
1959 x_msg_data,
1960 '_PVT'
1961 );
1962 WHEN OTHERS THEN
1963 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1964 (
1965 l_api_name,
1966 G_PKG_NAME,
1967 'OTHERS',
1968 x_msg_count,
1969 x_msg_data,
1970 '_PVT'
1971 );
1972 END update_row;
1973 -----------------------------------------
1974 -- update_row for:OKL_CNTR_LVLNG_LNS_V --
1975 -----------------------------------------
1976 PROCEDURE update_row(
1977 p_api_version IN NUMBER,
1978 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1979 x_return_status OUT NOCOPY VARCHAR2,
1980 x_msg_count OUT NOCOPY NUMBER,
1981 x_msg_data OUT NOCOPY VARCHAR2,
1982 p_cllv_rec IN cllv_rec_type,
1983 x_cllv_rec OUT NOCOPY cllv_rec_type) IS
1984
1985 l_api_version CONSTANT NUMBER := 1;
1986 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1987 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1988 l_cllv_rec cllv_rec_type := p_cllv_rec;
1989 l_def_cllv_rec cllv_rec_type;
1990 l_db_cllv_rec cllv_rec_type;
1991 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1992 lx_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
1993 -------------------------------
1994 -- FUNCTION fill_who_columns --
1995 -------------------------------
1996 FUNCTION fill_who_columns (
1997 p_cllv_rec IN cllv_rec_type
1998 ) RETURN cllv_rec_type IS
1999 l_cllv_rec cllv_rec_type := p_cllv_rec;
2000 BEGIN
2001 l_cllv_rec.LAST_UPDATE_DATE := SYSDATE;
2002 l_cllv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2003 l_cllv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2004 RETURN(l_cllv_rec);
2005 END fill_who_columns;
2006 ----------------------------------
2007 -- FUNCTION populate_new_record --
2008 ----------------------------------
2009 FUNCTION populate_new_record (
2010 p_cllv_rec IN cllv_rec_type,
2011 x_cllv_rec OUT NOCOPY cllv_rec_type
2012 ) RETURN VARCHAR2 IS
2013 l_row_notfound BOOLEAN := TRUE;
2014 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2015 BEGIN
2016 x_cllv_rec := p_cllv_rec;
2017 -- Get current database values
2018 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2019 -- so it may be verified through LOCK_ROW.
2020 l_db_cllv_rec := get_rec(p_cllv_rec, l_return_status);
2021 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2022 IF (x_cllv_rec.id = OKL_API.G_MISS_NUM)
2023 THEN
2024 x_cllv_rec.id := l_db_cllv_rec.id;
2025 END IF;
2026 -- Hand Coded
2027 IF (x_cllv_rec.object_version_number = OKL_API.G_MISS_NUM)
2028 THEN
2029 x_cllv_rec.object_version_number := l_db_cllv_rec.object_version_number;
2030 END IF;
2031 -- End hand Coded
2032 IF (x_cllv_rec.kle_id = OKL_API.G_MISS_NUM)
2033 THEN
2034 x_cllv_rec.kle_id := l_db_cllv_rec.kle_id;
2035 END IF;
2036 IF (x_cllv_rec.clg_id = OKL_API.G_MISS_NUM)
2037 THEN
2038 x_cllv_rec.clg_id := l_db_cllv_rec.clg_id;
2039 END IF;
2040 IF (x_cllv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2041 THEN
2042 x_cllv_rec.attribute_category := l_db_cllv_rec.attribute_category;
2043 END IF;
2044 IF (x_cllv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2045 THEN
2046 x_cllv_rec.attribute1 := l_db_cllv_rec.attribute1;
2047 END IF;
2048 IF (x_cllv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2049 THEN
2050 x_cllv_rec.attribute2 := l_db_cllv_rec.attribute2;
2051 END IF;
2052 IF (x_cllv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2053 THEN
2054 x_cllv_rec.attribute3 := l_db_cllv_rec.attribute3;
2055 END IF;
2056 IF (x_cllv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2057 THEN
2058 x_cllv_rec.attribute4 := l_db_cllv_rec.attribute4;
2059 END IF;
2060 IF (x_cllv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2061 THEN
2062 x_cllv_rec.attribute5 := l_db_cllv_rec.attribute5;
2063 END IF;
2064 IF (x_cllv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2065 THEN
2066 x_cllv_rec.attribute6 := l_db_cllv_rec.attribute6;
2067 END IF;
2068 IF (x_cllv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2069 THEN
2070 x_cllv_rec.attribute7 := l_db_cllv_rec.attribute7;
2071 END IF;
2072 IF (x_cllv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2073 THEN
2074 x_cllv_rec.attribute8 := l_db_cllv_rec.attribute8;
2075 END IF;
2076 IF (x_cllv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2077 THEN
2078 x_cllv_rec.attribute9 := l_db_cllv_rec.attribute9;
2079 END IF;
2080 IF (x_cllv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2081 THEN
2082 x_cllv_rec.attribute10 := l_db_cllv_rec.attribute10;
2083 END IF;
2084 IF (x_cllv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2085 THEN
2086 x_cllv_rec.attribute11 := l_db_cllv_rec.attribute11;
2087 END IF;
2088 IF (x_cllv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2089 THEN
2090 x_cllv_rec.attribute12 := l_db_cllv_rec.attribute12;
2091 END IF;
2092 IF (x_cllv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2093 THEN
2094 x_cllv_rec.attribute13 := l_db_cllv_rec.attribute13;
2095 END IF;
2096 IF (x_cllv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2097 THEN
2098 x_cllv_rec.attribute14 := l_db_cllv_rec.attribute14;
2099 END IF;
2100 IF (x_cllv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2101 THEN
2102 x_cllv_rec.attribute15 := l_db_cllv_rec.attribute15;
2103 END IF;
2104 IF (x_cllv_rec.created_by = OKL_API.G_MISS_NUM)
2105 THEN
2106 x_cllv_rec.created_by := l_db_cllv_rec.created_by;
2107 END IF;
2108 IF (x_cllv_rec.creation_date = OKL_API.G_MISS_DATE)
2109 THEN
2110 x_cllv_rec.creation_date := l_db_cllv_rec.creation_date;
2111 END IF;
2112 IF (x_cllv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2113 THEN
2114 x_cllv_rec.last_updated_by := l_db_cllv_rec.last_updated_by;
2115 END IF;
2116 IF (x_cllv_rec.last_update_date = OKL_API.G_MISS_DATE)
2117 THEN
2118 x_cllv_rec.last_update_date := l_db_cllv_rec.last_update_date;
2119 END IF;
2120 IF (x_cllv_rec.last_update_login = OKL_API.G_MISS_NUM)
2121 THEN
2122 x_cllv_rec.last_update_login := l_db_cllv_rec.last_update_login;
2123 END IF;
2124 END IF;
2125 RETURN(l_return_status);
2126 END populate_new_record;
2127 ---------------------------------------------
2128 -- Set_Attributes for:OKL_CNTR_LVLNG_LNS_V --
2129 ---------------------------------------------
2130 FUNCTION Set_Attributes (
2131 p_cllv_rec IN cllv_rec_type,
2132 x_cllv_rec OUT NOCOPY cllv_rec_type
2133 ) RETURN VARCHAR2 IS
2134 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2135 BEGIN
2136 x_cllv_rec := p_cllv_rec;
2137 RETURN(l_return_status);
2138 END Set_Attributes;
2139 BEGIN
2140 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2141 G_PKG_NAME,
2142 p_init_msg_list,
2143 l_api_version,
2144 p_api_version,
2145 '_PVT',
2146 x_return_status);
2147 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2148 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2149 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2150 RAISE OKL_API.G_EXCEPTION_ERROR;
2151 END IF;
2152 --- Setting item attributes
2153 l_return_status := Set_Attributes(
2154 p_cllv_rec, -- IN
2155 x_cllv_rec); -- OUT
2156 --- If any errors happen abort API
2157 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2158 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2159 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2160 RAISE OKL_API.G_EXCEPTION_ERROR;
2161 END IF;
2162 l_return_status := populate_new_record(l_cllv_rec, l_def_cllv_rec);
2163 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2165 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2166 RAISE OKL_API.G_EXCEPTION_ERROR;
2167 END IF;
2168 l_def_cllv_rec := fill_who_columns(l_def_cllv_rec);
2169 --- Validate all non-missing attributes (Item Level Validation)
2170 l_return_status := Validate_Attributes(l_def_cllv_rec);
2171 --- If any errors happen abort API
2172 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2173 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2174 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2175 RAISE OKL_API.G_EXCEPTION_ERROR;
2176 END IF;
2177 l_return_status := Validate_Record(l_def_cllv_rec, l_db_cllv_rec);
2178 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2179 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2180 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2181 RAISE OKL_API.G_EXCEPTION_ERROR;
2182 END IF;
2183
2184 -- Lock the Record
2185 /* lock_row(
2186 p_api_version => p_api_version,
2187 p_init_msg_list => p_init_msg_list,
2188 x_return_status => l_return_status,
2189 x_msg_count => x_msg_count,
2190 x_msg_data => x_msg_data,
2191 p_cllv_rec => p_cllv_rec);
2192 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2193 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2194 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2195 RAISE OKL_API.G_EXCEPTION_ERROR;
2196 END IF;
2197 */
2198 -----------------------------------------
2199 -- Move VIEW record to "Child" records --
2200 -----------------------------------------
2201 migrate(l_def_cllv_rec, l_okl_cntr_lvlng_lns_b_rec);
2202 -----------------------------------------------
2203 -- Call the UPDATE_ROW for each child record --
2204 -----------------------------------------------
2205 update_row(
2206 p_init_msg_list,
2207 l_return_status,
2208 x_msg_count,
2209 x_msg_data,
2210 l_okl_cntr_lvlng_lns_b_rec,
2211 lx_okl_cntr_lvlng_lns_b_rec
2212 );
2213 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2214 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2215 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2216 RAISE OKL_API.G_EXCEPTION_ERROR;
2217 END IF;
2218 migrate(lx_okl_cntr_lvlng_lns_b_rec, l_def_cllv_rec);
2219 x_cllv_rec := l_def_cllv_rec;
2220 x_return_status := l_return_status;
2221 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2222 EXCEPTION
2223 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2224 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2225 (
2226 l_api_name,
2227 G_PKG_NAME,
2228 'OKL_API.G_RET_STS_ERROR',
2229 x_msg_count,
2230 x_msg_data,
2231 '_PVT'
2232 );
2233 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2234 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2235 (
2236 l_api_name,
2237 G_PKG_NAME,
2238 'OKL_API.G_RET_STS_UNEXP_ERROR',
2239 x_msg_count,
2240 x_msg_data,
2241 '_PVT'
2242 );
2243 WHEN OTHERS THEN
2244 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2245 (
2246 l_api_name,
2247 G_PKG_NAME,
2248 'OTHERS',
2249 x_msg_count,
2250 x_msg_data,
2251 '_PVT'
2252 );
2253 END update_row;
2254 ----------------------------------------
2255 -- PL/SQL TBL update_row for:cllv_tbl --
2256 ----------------------------------------
2257 PROCEDURE update_row(
2258 p_api_version IN NUMBER,
2259 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2260 x_return_status OUT NOCOPY VARCHAR2,
2261 x_msg_count OUT NOCOPY NUMBER,
2262 x_msg_data OUT NOCOPY VARCHAR2,
2263 p_cllv_tbl IN cllv_tbl_type,
2264 x_cllv_tbl OUT NOCOPY cllv_tbl_type,
2265 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2266
2267 l_api_version CONSTANT NUMBER := 1;
2268 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2269 i NUMBER := 0;
2270 BEGIN
2271 OKL_API.init_msg_list(p_init_msg_list);
2272 -- Make sure PL/SQL table has records in it before passing
2273 IF (p_cllv_tbl.COUNT > 0) THEN
2274 i := p_cllv_tbl.FIRST;
2275 LOOP
2276 DECLARE
2277 l_error_rec OKL_API.ERROR_REC_TYPE;
2278 BEGIN
2279 l_error_rec.api_name := l_api_name;
2280 l_error_rec.api_package := G_PKG_NAME;
2281 l_error_rec.idx := i;
2282 update_row (
2283 p_api_version => p_api_version,
2284 p_init_msg_list => OKL_API.G_FALSE,
2285 x_return_status => l_error_rec.error_type,
2286 x_msg_count => l_error_rec.msg_count,
2287 x_msg_data => l_error_rec.msg_data,
2288 p_cllv_rec => p_cllv_tbl(i),
2289 x_cllv_rec => x_cllv_tbl(i));
2290 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2291 l_error_rec.sqlcode := SQLCODE;
2292 load_error_tbl(l_error_rec, px_error_tbl);
2293 ELSE
2294 x_msg_count := l_error_rec.msg_count;
2295 x_msg_data := l_error_rec.msg_data;
2296 END IF;
2297 EXCEPTION
2298 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2299 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2300 l_error_rec.sqlcode := SQLCODE;
2301 load_error_tbl(l_error_rec, px_error_tbl);
2302 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2303 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2304 l_error_rec.sqlcode := SQLCODE;
2305 load_error_tbl(l_error_rec, px_error_tbl);
2306 WHEN OTHERS THEN
2307 l_error_rec.error_type := 'OTHERS';
2308 l_error_rec.sqlcode := SQLCODE;
2309 load_error_tbl(l_error_rec, px_error_tbl);
2310 END;
2311 EXIT WHEN (i = p_cllv_tbl.LAST);
2312 i := p_cllv_tbl.NEXT(i);
2313 END LOOP;
2314 END IF;
2315 -- Loop through the error_tbl to find the error with the highest severity
2316 -- and return it.
2317 x_return_status := find_highest_exception(px_error_tbl);
2318 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2319 EXCEPTION
2320 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2321 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2322 (
2323 l_api_name,
2324 G_PKG_NAME,
2325 'OKL_API.G_RET_STS_ERROR',
2326 x_msg_count,
2327 x_msg_data,
2328 '_PVT'
2329 );
2330 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2331 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2332 (
2333 l_api_name,
2334 G_PKG_NAME,
2335 'OKL_API.G_RET_STS_UNEXP_ERROR',
2336 x_msg_count,
2337 x_msg_data,
2338 '_PVT'
2339 );
2340 WHEN OTHERS THEN
2341 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2342 (
2343 l_api_name,
2344 G_PKG_NAME,
2345 'OTHERS',
2346 x_msg_count,
2347 x_msg_data,
2348 '_PVT'
2349 );
2350 END update_row;
2351
2352 ----------------------------------------
2353 -- PL/SQL TBL update_row for:CLLV_TBL --
2354 ----------------------------------------
2355 PROCEDURE update_row(
2356 p_api_version IN NUMBER,
2357 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2358 x_return_status OUT NOCOPY VARCHAR2,
2359 x_msg_count OUT NOCOPY NUMBER,
2360 x_msg_data OUT NOCOPY VARCHAR2,
2361 p_cllv_tbl IN cllv_tbl_type,
2362 x_cllv_tbl OUT NOCOPY cllv_tbl_type) IS
2363
2364 l_api_version CONSTANT NUMBER := 1;
2365 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2366 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2367 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2368 BEGIN
2369 OKL_API.init_msg_list(p_init_msg_list);
2370 -- Make sure PL/SQL table has records in it before passing
2371 IF (p_cllv_tbl.COUNT > 0) THEN
2372 update_row (
2373 p_api_version => p_api_version,
2374 p_init_msg_list => OKL_API.G_FALSE,
2375 x_return_status => x_return_status,
2376 x_msg_count => x_msg_count,
2377 x_msg_data => x_msg_data,
2378 p_cllv_tbl => p_cllv_tbl,
2379 x_cllv_tbl => x_cllv_tbl,
2380 px_error_tbl => l_error_tbl);
2381 END IF;
2382 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2383 EXCEPTION
2384 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2385 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2386 (
2387 l_api_name,
2388 G_PKG_NAME,
2389 'OKL_API.G_RET_STS_ERROR',
2390 x_msg_count,
2391 x_msg_data,
2392 '_PVT'
2393 );
2394 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2395 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2396 (
2397 l_api_name,
2398 G_PKG_NAME,
2399 'OKL_API.G_RET_STS_UNEXP_ERROR',
2400 x_msg_count,
2401 x_msg_data,
2402 '_PVT'
2403 );
2404 WHEN OTHERS THEN
2405 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2406 (
2407 l_api_name,
2408 G_PKG_NAME,
2409 'OTHERS',
2410 x_msg_count,
2411 x_msg_data,
2412 '_PVT'
2413 );
2414 END update_row;
2415
2416 ---------------------------------------------------------------------------
2417 -- PROCEDURE delete_row
2418 ---------------------------------------------------------------------------
2419 -----------------------------------------
2420 -- delete_row for:OKL_CNTR_LVLNG_LNS_B --
2421 -----------------------------------------
2422 PROCEDURE delete_row(
2423 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2424 x_return_status OUT NOCOPY VARCHAR2,
2425 x_msg_count OUT NOCOPY NUMBER,
2426 x_msg_data OUT NOCOPY VARCHAR2,
2427 p_okl_cntr_lvlng_lns_b_rec IN okl_cntr_lvlng_lns_b_rec_type) IS
2428
2429 l_api_version CONSTANT NUMBER := 1;
2430 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2431 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2432 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type := p_okl_cntr_lvlng_lns_b_rec;
2433 l_row_notfound BOOLEAN := TRUE;
2434 BEGIN
2435 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2436 p_init_msg_list,
2437 '_PVT',
2438 x_return_status);
2439 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2440 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2441 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2442 RAISE OKL_API.G_EXCEPTION_ERROR;
2443 END IF;
2444
2445 DELETE FROM OKL_CNTR_LVLNG_LNS_B
2446 WHERE ID = p_okl_cntr_lvlng_lns_b_rec.id;
2447
2448 x_return_status := l_return_status;
2449 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2450 EXCEPTION
2451 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2452 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2453 (
2454 l_api_name,
2455 G_PKG_NAME,
2456 'OKL_API.G_RET_STS_ERROR',
2457 x_msg_count,
2458 x_msg_data,
2459 '_PVT'
2460 );
2461 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2462 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2463 (
2464 l_api_name,
2465 G_PKG_NAME,
2466 'OKL_API.G_RET_STS_UNEXP_ERROR',
2467 x_msg_count,
2468 x_msg_data,
2469 '_PVT'
2470 );
2471 WHEN OTHERS THEN
2472 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2473 (
2474 l_api_name,
2475 G_PKG_NAME,
2476 'OTHERS',
2477 x_msg_count,
2478 x_msg_data,
2479 '_PVT'
2480 );
2481 END delete_row;
2482 -----------------------------------------
2483 -- delete_row for:OKL_CNTR_LVLNG_LNS_V --
2484 -----------------------------------------
2485 PROCEDURE delete_row(
2486 p_api_version IN NUMBER,
2487 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2488 x_return_status OUT NOCOPY VARCHAR2,
2489 x_msg_count OUT NOCOPY NUMBER,
2490 x_msg_data OUT NOCOPY VARCHAR2,
2491 p_cllv_rec IN cllv_rec_type) IS
2492
2493 l_api_version CONSTANT NUMBER := 1;
2494 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2495 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2496 l_cllv_rec cllv_rec_type := p_cllv_rec;
2497 l_okl_cntr_lvlng_lns_b_rec okl_cntr_lvlng_lns_b_rec_type;
2498 BEGIN
2499 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2500 G_PKG_NAME,
2501 p_init_msg_list,
2502 l_api_version,
2503 p_api_version,
2504 '_PVT',
2505 x_return_status);
2506 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2507 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2508 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2509 RAISE OKL_API.G_EXCEPTION_ERROR;
2510 END IF;
2511 -----------------------------------------
2512 -- Move VIEW record to "Child" records --
2513 -----------------------------------------
2514 migrate(l_cllv_rec, l_okl_cntr_lvlng_lns_b_rec);
2515 -----------------------------------------------
2516 -- Call the DELETE_ROW for each child record --
2517 -----------------------------------------------
2518 delete_row(
2519 p_init_msg_list,
2520 l_return_status,
2521 x_msg_count,
2522 x_msg_data,
2523 l_okl_cntr_lvlng_lns_b_rec
2524 );
2525 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2527 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2528 RAISE OKL_API.G_EXCEPTION_ERROR;
2529 END IF;
2530 x_return_status := l_return_status;
2531 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2532 EXCEPTION
2533 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2534 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2535 (
2536 l_api_name,
2537 G_PKG_NAME,
2538 'OKL_API.G_RET_STS_ERROR',
2539 x_msg_count,
2540 x_msg_data,
2541 '_PVT'
2542 );
2543 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2544 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2545 (
2546 l_api_name,
2547 G_PKG_NAME,
2548 'OKL_API.G_RET_STS_UNEXP_ERROR',
2549 x_msg_count,
2550 x_msg_data,
2551 '_PVT'
2552 );
2553 WHEN OTHERS THEN
2554 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2555 (
2556 l_api_name,
2557 G_PKG_NAME,
2558 'OTHERS',
2559 x_msg_count,
2560 x_msg_data,
2561 '_PVT'
2562 );
2563 END delete_row;
2564 ----------------------------------------------------
2565 -- PL/SQL TBL delete_row for:OKL_CNTR_LVLNG_LNS_V --
2566 ----------------------------------------------------
2567 PROCEDURE delete_row(
2568 p_api_version IN NUMBER,
2569 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2570 x_return_status OUT NOCOPY VARCHAR2,
2571 x_msg_count OUT NOCOPY NUMBER,
2572 x_msg_data OUT NOCOPY VARCHAR2,
2573 p_cllv_tbl IN cllv_tbl_type,
2574 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2575
2576 l_api_version CONSTANT NUMBER := 1;
2577 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2578 i NUMBER := 0;
2579 BEGIN
2580 OKL_API.init_msg_list(p_init_msg_list);
2581 -- Make sure PL/SQL table has records in it before passing
2582 IF (p_cllv_tbl.COUNT > 0) THEN
2583 i := p_cllv_tbl.FIRST;
2584 LOOP
2585 DECLARE
2586 l_error_rec OKL_API.ERROR_REC_TYPE;
2587 BEGIN
2588 l_error_rec.api_name := l_api_name;
2589 l_error_rec.api_package := G_PKG_NAME;
2590 l_error_rec.idx := i;
2591 delete_row (
2592 p_api_version => p_api_version,
2593 p_init_msg_list => OKL_API.G_FALSE,
2594 x_return_status => l_error_rec.error_type,
2595 x_msg_count => l_error_rec.msg_count,
2596 x_msg_data => l_error_rec.msg_data,
2597 p_cllv_rec => p_cllv_tbl(i));
2598 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2599 l_error_rec.sqlcode := SQLCODE;
2600 load_error_tbl(l_error_rec, px_error_tbl);
2601 ELSE
2602 x_msg_count := l_error_rec.msg_count;
2603 x_msg_data := l_error_rec.msg_data;
2604 END IF;
2605 EXCEPTION
2606 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2607 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2608 l_error_rec.sqlcode := SQLCODE;
2609 load_error_tbl(l_error_rec, px_error_tbl);
2610 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2611 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2612 l_error_rec.sqlcode := SQLCODE;
2613 load_error_tbl(l_error_rec, px_error_tbl);
2614 WHEN OTHERS THEN
2615 l_error_rec.error_type := 'OTHERS';
2616 l_error_rec.sqlcode := SQLCODE;
2617 load_error_tbl(l_error_rec, px_error_tbl);
2618 END;
2619 EXIT WHEN (i = p_cllv_tbl.LAST);
2620 i := p_cllv_tbl.NEXT(i);
2621 END LOOP;
2622 END IF;
2623 -- Loop through the error_tbl to find the error with the highest severity
2624 -- and return it.
2625 x_return_status := find_highest_exception(px_error_tbl);
2626 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2627 EXCEPTION
2628 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2629 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2630 (
2631 l_api_name,
2632 G_PKG_NAME,
2633 'OKL_API.G_RET_STS_ERROR',
2634 x_msg_count,
2635 x_msg_data,
2636 '_PVT'
2637 );
2638 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2639 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2640 (
2641 l_api_name,
2642 G_PKG_NAME,
2643 'OKL_API.G_RET_STS_UNEXP_ERROR',
2644 x_msg_count,
2645 x_msg_data,
2646 '_PVT'
2647 );
2648 WHEN OTHERS THEN
2649 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2650 (
2651 l_api_name,
2652 G_PKG_NAME,
2653 'OTHERS',
2654 x_msg_count,
2655 x_msg_data,
2656 '_PVT'
2657 );
2658 END delete_row;
2659
2660 ----------------------------------------------------
2661 -- PL/SQL TBL delete_row for:OKL_CNTR_LVLNG_LNS_V --
2662 ----------------------------------------------------
2663 PROCEDURE delete_row(
2664 p_api_version IN NUMBER,
2665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2666 x_return_status OUT NOCOPY VARCHAR2,
2667 x_msg_count OUT NOCOPY NUMBER,
2668 x_msg_data OUT NOCOPY VARCHAR2,
2669 p_cllv_tbl IN cllv_tbl_type) IS
2670
2671 l_api_version CONSTANT NUMBER := 1;
2672 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2673 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2674 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2675 BEGIN
2676 OKL_API.init_msg_list(p_init_msg_list);
2677 -- Make sure PL/SQL table has records in it before passing
2678 IF (p_cllv_tbl.COUNT > 0) THEN
2679 delete_row (
2680 p_api_version => p_api_version,
2681 p_init_msg_list => OKL_API.G_FALSE,
2682 x_return_status => x_return_status,
2683 x_msg_count => x_msg_count,
2684 x_msg_data => x_msg_data,
2685 p_cllv_tbl => p_cllv_tbl,
2686 px_error_tbl => l_error_tbl);
2687 END IF;
2688 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2689 EXCEPTION
2690 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2691 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2692 (
2693 l_api_name,
2694 G_PKG_NAME,
2695 'OKL_API.G_RET_STS_ERROR',
2696 x_msg_count,
2697 x_msg_data,
2698 '_PVT'
2699 );
2700 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_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_UNEXP_ERROR',
2706 x_msg_count,
2707 x_msg_data,
2708 '_PVT'
2709 );
2710 WHEN OTHERS THEN
2711 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2712 (
2713 l_api_name,
2714 G_PKG_NAME,
2715 'OTHERS',
2716 x_msg_count,
2717 x_msg_data,
2718 '_PVT'
2719 );
2720 END delete_row;
2721
2722 END OKL_CLL_PVT;