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