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