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