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