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