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