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