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