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