[Home] [Help]
PACKAGE BODY: APPS.OKL_PDQ_PVT
Source
1 PACKAGE BODY OKL_PDQ_PVT AS
2 /* $Header: OKLSPDQB.pls 115.9 2002/12/18 13:02:22 kjinger noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKL_PDT_PQYS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_pdq_rec IN pdq_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN pdq_rec_type IS
42 CURSOR okl_pdt_pqys_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 PTL_ID,
46 PQY_ID,
47 OBJECT_VERSION_NUMBER,
48 FROM_DATE,
49 TO_DATE,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN
55 FROM Okl_Pdt_Pqys
56 WHERE okl_pdt_pqys.id = p_id;
57 l_okl_pdt_pqys_pk okl_pdt_pqys_pk_csr%ROWTYPE;
58 l_pdq_rec pdq_rec_type;
59 BEGIN
60 x_no_data_found := TRUE;
61 -- Get current database values
62 OPEN okl_pdt_pqys_pk_csr (p_pdq_rec.id);
63 FETCH okl_pdt_pqys_pk_csr INTO
64 l_pdq_rec.ID,
65 l_pdq_rec.PTL_ID,
66 l_pdq_rec.PQY_ID,
67 l_pdq_rec.OBJECT_VERSION_NUMBER,
68 l_pdq_rec.FROM_DATE,
69 l_pdq_rec.TO_DATE,
70 l_pdq_rec.CREATED_BY,
71 l_pdq_rec.CREATION_DATE,
72 l_pdq_rec.LAST_UPDATED_BY,
73 l_pdq_rec.LAST_UPDATE_DATE,
74 l_pdq_rec.LAST_UPDATE_LOGIN;
75 x_no_data_found := okl_pdt_pqys_pk_csr%NOTFOUND;
76 CLOSE okl_pdt_pqys_pk_csr;
77 RETURN(l_pdq_rec);
78 END get_rec;
79
80 FUNCTION get_rec (
81 p_pdq_rec IN pdq_rec_type
82 ) RETURN pdq_rec_type IS
83 l_row_notfound BOOLEAN := TRUE;
84 BEGIN
85 RETURN(get_rec(p_pdq_rec, l_row_notfound));
86 END get_rec;
87 ---------------------------------------------------------------------------
88 -- FUNCTION get_rec for: OKL_PDT_PQYS_V
89 ---------------------------------------------------------------------------
90 FUNCTION get_rec (
91 p_pdqv_rec IN pdqv_rec_type,
92 x_no_data_found OUT NOCOPY BOOLEAN
93 ) RETURN pdqv_rec_type IS
94 CURSOR okl_pdqv_pk_csr (p_id IN NUMBER) IS
95 SELECT
96 ID,
97 OBJECT_VERSION_NUMBER,
98 PTL_ID,
99 PQY_ID,
100 FROM_DATE,
101 TO_DATE,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN
107 FROM Okl_Pdt_Pqys_V
108 WHERE okl_pdt_pqys_v.id = p_id;
109 l_okl_pdqv_pk okl_pdqv_pk_csr%ROWTYPE;
110 l_pdqv_rec pdqv_rec_type;
111 BEGIN
112 x_no_data_found := TRUE;
113 -- Get current database values
114 OPEN okl_pdqv_pk_csr (p_pdqv_rec.id);
115 FETCH okl_pdqv_pk_csr INTO
116 l_pdqv_rec.ID,
117 l_pdqv_rec.OBJECT_VERSION_NUMBER,
118 l_pdqv_rec.PTL_ID,
119 l_pdqv_rec.PQY_ID,
120 l_pdqv_rec.FROM_DATE,
121 l_pdqv_rec.TO_DATE,
122 l_pdqv_rec.CREATED_BY,
123 l_pdqv_rec.CREATION_DATE,
124 l_pdqv_rec.LAST_UPDATED_BY,
125 l_pdqv_rec.LAST_UPDATE_DATE,
126 l_pdqv_rec.LAST_UPDATE_LOGIN;
127 x_no_data_found := okl_pdqv_pk_csr%NOTFOUND;
128 CLOSE okl_pdqv_pk_csr;
129 RETURN(l_pdqv_rec);
130 END get_rec;
131
132 FUNCTION get_rec (
133 p_pdqv_rec IN pdqv_rec_type
134 ) RETURN pdqv_rec_type IS
135 l_row_notfound BOOLEAN := TRUE;
136 BEGIN
137 RETURN(get_rec(p_pdqv_rec, l_row_notfound));
138 END get_rec;
139
140 ----------------------------------------------------
141 -- FUNCTION null_out_defaults for: OKL_PDT_PQYS_V --
142 ----------------------------------------------------
143 FUNCTION null_out_defaults (
144 p_pdqv_rec IN pdqv_rec_type
145 ) RETURN pdqv_rec_type IS
146 l_pdqv_rec pdqv_rec_type := p_pdqv_rec;
147 BEGIN
148 IF (l_pdqv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
149 l_pdqv_rec.object_version_number := NULL;
150 END IF;
151 IF (l_pdqv_rec.ptl_id = OKC_API.G_MISS_NUM) THEN
152 l_pdqv_rec.ptl_id := NULL;
153 END IF;
154 IF (l_pdqv_rec.pqy_id = OKC_API.G_MISS_NUM) THEN
155 l_pdqv_rec.pqy_id := NULL;
156 END IF;
157 IF (l_pdqv_rec.from_date = OKC_API.G_MISS_DATE) THEN
158 l_pdqv_rec.from_date := NULL;
159 END IF;
160 IF (l_pdqv_rec.to_date = OKC_API.G_MISS_DATE) THEN
161 l_pdqv_rec.to_date := NULL;
162 END IF;
163 IF (l_pdqv_rec.created_by = OKC_API.G_MISS_NUM) THEN
164 l_pdqv_rec.created_by := NULL;
165 END IF;
166 IF (l_pdqv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
167 l_pdqv_rec.creation_date := NULL;
168 END IF;
169 IF (l_pdqv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
170 l_pdqv_rec.last_updated_by := NULL;
171 END IF;
172 IF (l_pdqv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
173 l_pdqv_rec.last_update_date := NULL;
174 END IF;
175 IF (l_pdqv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
176 l_pdqv_rec.last_update_login := NULL;
177 END IF;
178 RETURN(l_pdqv_rec);
179 END null_out_defaults;
180
181 /**********************TCHGS: Commenting Old Code ******************************
182 ---------------------------------------------------------------------------
183 -- PROCEDURE Validate_Attributes
184 ---------------------------------------------------------------------------
185 --------------------------------------------
186 -- Validate_Attributes for:OKL_PDT_PQYS_V --
187 --------------------------------------------
188 FUNCTION Validate_Attributes (
189 p_pdqv_rec IN pdqv_rec_type
190 ) RETURN VARCHAR2 IS
191 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
192 BEGIN
193 IF p_pdqv_rec.id = OKC_API.G_MISS_NUM OR
194 p_pdqv_rec.id IS NULL
195 THEN
196 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
197 l_return_status := OKC_API.G_RET_STS_ERROR;
198 ELSIF p_pdqv_rec.object_version_number = OKC_API.G_MISS_NUM OR
199 p_pdqv_rec.object_version_number IS NULL
200 THEN
201 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
202 l_return_status := OKC_API.G_RET_STS_ERROR;
203 ELSIF p_pdqv_rec.ptl_id = OKC_API.G_MISS_NUM OR
204 p_pdqv_rec.ptl_id IS NULL
205 THEN
206 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ptl_id');
207 l_return_status := OKC_API.G_RET_STS_ERROR;
208 ELSIF p_pdqv_rec.pqy_id = OKC_API.G_MISS_NUM OR
209 p_pdqv_rec.pqy_id IS NULL
210 THEN
211 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'pqy_id');
212 l_return_status := OKC_API.G_RET_STS_ERROR;
213 END IF;
214 RETURN(l_return_status);
215 END Validate_Attributes;
216
217 ---------------------------------------------------------------------------
218 -- PROCEDURE Validate_Record
219 ---------------------------------------------------------------------------
220 ----------------------------------------
221 -- Validate_Record for:OKL_PDT_PQYS_V --
222 ----------------------------------------
223 FUNCTION Validate_Record (
224 p_pdqv_rec IN pdqv_rec_type
225 ) RETURN VARCHAR2 IS
226 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
227 BEGIN
228 RETURN (l_return_status);
229 END Validate_Record;
230
231 **********************TCHGS: Commenting Old Code ******************************/
232
233 /************************** TCHGS: Start New Code *****************************/
234 ---------------------------------------------------------------------------
235 -- PROCEDURE Validate_Id
236 ---------------------------------------------------------------------------
237 -- Start of comments
238 --
239 -- Procedure Name : Validate_Id
240 -- Description :
241 -- Business Rules :
242 -- Parameters :
243 -- Version : 1.0
244 -- End of comments
245 ---------------------------------------------------------------------------
246 PROCEDURE Validate_Id(p_pdqv_rec IN pdqv_rec_type
247 ,x_return_status OUT NOCOPY VARCHAR2)
248 IS
249
250 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
251
252 BEGIN
253 -- initialize return status
254 x_return_status := OKC_API.G_RET_STS_SUCCESS;
255
256 -- check for data before processing
257 IF (p_pdqv_rec.id IS NULL) OR
258 (p_pdqv_rec.id = OKC_API.G_MISS_NUM) THEN
259 OKC_API.SET_MESSAGE(p_app_name => g_app_name
260 ,p_msg_name => g_required_value
261 ,p_token1 => g_col_name_token
262 ,p_token1_value => 'id');
263 x_return_status := OKC_API.G_RET_STS_ERROR;
264 RAISE G_EXCEPTION_HALT_VALIDATION;
265 END IF;
266
267 EXCEPTION
268 WHEN G_EXCEPTION_HALT_VALIDATION THEN
269 -- no processing necessary; validation can continue
270 -- with the next column
271 NULL;
272
273 WHEN OTHERS THEN
274 -- store SQL error message on message stack for caller
275 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
276 p_msg_name => g_unexpected_error,
277 p_token1 => g_sqlcode_token,
278 p_token1_value => sqlcode,
279 p_token2 => g_sqlerrm_token,
280 p_token2_value => sqlerrm);
281
282 -- notify caller of an UNEXPECTED error
283 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
284
285 END Validate_Id;
286
287 ---------------------------------------------------------------------------
288 -- PROCEDURE Validate_Ptl_Id
289 ---------------------------------------------------------------------------
290 -- Start of comments
291 --
292 -- Procedure Name : Validate_Ptl_Id
293 -- Description :
294 -- Business Rules :
295 -- Parameters :
296 -- Version : 1.0
297 -- End of comments
298 ---------------------------------------------------------------------------
299 PROCEDURE Validate_Ptl_Id(p_pdqv_rec IN pdqv_rec_type
300 ,x_return_status OUT NOCOPY VARCHAR2)
301 IS
302
303 CURSOR okl_ptlv_pk_csr (p_id IN NUMBER) IS
304 SELECT '1'
305 FROM okl_pdt_templates_v
306 WHERE okl_pdt_templates_v.id = p_id;
307
308 l_ptl_status VARCHAR2(1);
309 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
310 l_row_notfound BOOLEAN := TRUE;
311
312 BEGIN
313 -- initialize return status
314 x_return_status := OKC_API.G_RET_STS_SUCCESS;
315
316 -- check for data before processing
317 IF (p_pdqv_rec.ptl_id IS NULL) OR
318 (p_pdqv_rec.ptl_id = OKC_API.G_MISS_NUM) THEN
319 OKC_API.SET_MESSAGE(p_app_name => g_app_name
320 ,p_msg_name => g_required_value
321 ,p_token1 => g_col_name_token
322 ,p_token1_value => 'ptl_id');
323 x_return_status := OKC_API.G_RET_STS_ERROR;
324 RAISE G_EXCEPTION_HALT_VALIDATION;
325 END IF;
326
327 IF (p_pdqv_rec.PTL_ID IS NOT NULL)
328 THEN
329 OPEN okl_ptlv_pk_csr(p_pdqv_rec.PTL_ID);
330 FETCH okl_ptlv_pk_csr INTO l_ptl_status;
331 l_row_notfound := okl_ptlv_pk_csr%NOTFOUND;
332 CLOSE okl_ptlv_pk_csr;
333 IF (l_row_notfound) THEN
334 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PTL_ID');
335 RAISE G_ITEM_NOT_FOUND_ERROR;
336 END IF;
337 END IF;
338
339
340 EXCEPTION
341 WHEN G_EXCEPTION_HALT_VALIDATION THEN
342 -- no processing necessary; validation can continue
343 -- with the next column
344 NULL;
345 WHEN G_ITEM_NOT_FOUND_ERROR THEN
346 x_return_status := OKC_API.G_RET_STS_ERROR;
347
348 WHEN OTHERS THEN
349 -- store SQL error message on message stack for caller
350 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
351 p_msg_name => g_unexpected_error,
352 p_token1 => g_sqlcode_token,
353 p_token1_value => sqlcode,
354 p_token2 => g_sqlerrm_token,
355 p_token2_value => sqlerrm);
356
357 -- notify caller of an UNEXPECTED error
358 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
359
360 END Validate_Ptl_Id;
361
362 ---------------------------------------------------------------------------
363 -- PROCEDURE Validate_Pqy_Id
364 ---------------------------------------------------------------------------
365 -- Start of comments
366 --
367 -- Procedure Name : Validate_Pqy_Id
368 -- Description :
369 -- Business Rules :
370 -- Parameters :
371 -- Version : 1.0
372 -- End of comments
373 ---------------------------------------------------------------------------
374 PROCEDURE Validate_Pqy_Id(p_pdqv_rec IN pdqv_rec_type
375 ,x_return_status OUT NOCOPY VARCHAR2)
376 IS
377
378 CURSOR okl_pqyv_pk_csr (p_id IN NUMBER) IS
379 SELECT '1'
380 FROM okl_pdt_qualitys_v
381 WHERE okl_pdt_qualitys_v.id = p_id;
382
383 l_pqy_status VARCHAR2(1);
384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
385 l_row_notfound BOOLEAN := TRUE;
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_pdqv_rec.pqy_id IS NULL) OR
393 (p_pdqv_rec.pqy_id = 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 => 'pqy_id');
398 x_return_status := OKC_API.G_RET_STS_ERROR;
399 RAISE G_EXCEPTION_HALT_VALIDATION;
400 END IF;
401
402 IF (p_pdqv_rec.pqy_ID IS NOT NULL)
403 THEN
404 OPEN okl_pqyv_pk_csr(p_pdqv_rec.PQY_ID);
405 FETCH okl_pqyv_pk_csr INTO l_pqy_status;
406 l_row_notfound := okl_pqyv_pk_csr%NOTFOUND;
407 CLOSE okl_pqyv_pk_csr;
408 IF (l_row_notfound) THEN
409 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PQY_ID');
410 RAISE G_ITEM_NOT_FOUND_ERROR;
411 END IF;
412 END IF;
413
414 EXCEPTION
415 WHEN G_EXCEPTION_HALT_VALIDATION THEN
416 -- no processing necessary; validation can continue
417 -- with the next column
418 NULL;
419 WHEN G_ITEM_NOT_FOUND_ERROR THEN
420 x_return_status := OKC_API.G_RET_STS_ERROR;
421
422 WHEN OTHERS THEN
423 -- store SQL error message on message stack for caller
424 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
425 p_msg_name => g_unexpected_error,
426 p_token1 => g_sqlcode_token,
427 p_token1_value => sqlcode,
428 p_token2 => g_sqlerrm_token,
429 p_token2_value => sqlerrm);
430
431 -- notify caller of an UNEXPECTED error
432 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
433
434 END Validate_Pqy_Id;
435
436 ---------------------------------------------------------------------------
437 -- PROCEDURE Validate_Object_Version_Number
438 ---------------------------------------------------------------------------
439 -- Start of comments
440 --
441 -- Procedure Name : Validate_Object_Version_Number
442 -- Description :
443 -- Business Rules :
444 -- Parameters :
445 -- Version : 1.0
446 -- End of comments
447 ---------------------------------------------------------------------------
448 PROCEDURE Validate_Object_Version_Number(p_pdqv_rec IN pdqv_rec_type
449 ,x_return_status OUT NOCOPY VARCHAR2)
450 IS
451
452 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
453
454 BEGIN
455 -- initialize return status
456 x_return_status := OKC_API.G_RET_STS_SUCCESS;
457
458 -- check for data before processing
459 IF (p_pdqv_rec.object_version_number IS NULL) OR
460 (p_pdqv_rec.object_version_Number = OKC_API.G_MISS_NUM) THEN
461 OKC_API.SET_MESSAGE(p_app_name => g_app_name
462 ,p_msg_name => g_required_value
463 ,p_token1 => g_col_name_token
464 ,p_token1_value => 'object_version_number');
465 x_return_status := OKC_API.G_RET_STS_ERROR;
466 RAISE G_EXCEPTION_HALT_VALIDATION;
467 END IF;
468
469 EXCEPTION
470 WHEN G_EXCEPTION_HALT_VALIDATION THEN
471 -- no processing necessary; validation can continue
472 -- with the next column
473 NULL;
474
475 WHEN OTHERS THEN
476 -- store SQL error message on message stack for caller
477 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
478 p_msg_name => g_unexpected_error,
479 p_token1 => g_sqlcode_token,
480 p_token1_value => sqlcode,
481 p_token2 => g_sqlerrm_token,
482 p_token2_value => sqlerrm);
483
484 -- notify caller of an UNEXPECTED error
485 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
486
487 END Validate_Object_Version_Number;
488
489 ---------------------------------------------------------------------------
490 -- PROCEDURE Validate_From_Date
491 ---------------------------------------------------------------------------
492 -- Start of comments
493 --
494 -- Procedure Name : Validate_From_Date
495 -- Description :
496 -- Business Rules :
497 -- Parameters :
498 -- Version : 1.0
499 -- End of comments
500 ---------------------------------------------------------------------------
501 PROCEDURE Validate_From_Date(p_pdqv_rec IN pdqv_rec_type
502 ,x_return_status OUT NOCOPY VARCHAR2)
503 IS
504
505 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
506
507 BEGIN
508 -- initialize return status
509 x_return_status := OKC_API.G_RET_STS_SUCCESS;
510
511 -- check for data before processing
512 IF (p_pdqv_rec.from_date IS NULL) OR
513 (p_pdqv_rec.from_date = OKC_API.G_MISS_DATE) THEN
514 OKC_API.SET_MESSAGE(p_app_name => g_app_name
515 ,p_msg_name => g_required_value
516 ,p_token1 => g_col_name_token
517 ,p_token1_value => 'from_date');
518 x_return_status := OKC_API.G_RET_STS_ERROR;
519 RAISE G_EXCEPTION_HALT_VALIDATION;
520 END IF;
521
522 EXCEPTION
523 WHEN G_EXCEPTION_HALT_VALIDATION THEN
524 -- no processing necessary; validation can continue
525 -- with the next column
526 NULL;
527
528 WHEN OTHERS THEN
529 -- store SQL error message on message stack for caller
530 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
531 p_msg_name => g_unexpected_error,
532 p_token1 => g_sqlcode_token,
533 p_token1_value => sqlcode,
534 p_token2 => g_sqlerrm_token,
535 p_token2_value => sqlerrm);
536
537 -- notify caller of an UNEXPECTED error
538 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
539
540 END Validate_From_Date;
541
542 ---------------------------------------------------------------------------
543 -- PROCEDURE Validate_To_Date
544 ---------------------------------------------------------------------------
545 -- Start of comments
546 --
547 -- Procedure Name : Validate_To_Date
548 -- Description :
549 -- Business Rules :
550 -- Parameters :
551 -- Version : 1.0
552 -- End of comments
553 ---------------------------------------------------------------------------
554 PROCEDURE Validate_To_Date(p_pdqv_rec IN pdqv_rec_type
555 ,x_return_status OUT NOCOPY VARCHAR2)
556 IS
557
558 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
559
560 BEGIN
561 -- initialize return status
562 x_return_status := OKC_API.G_RET_STS_SUCCESS;
563
564 -- check for data before processing
565 IF (p_pdqv_rec.to_date IS NOT NULL) AND
566 (p_pdqv_rec.to_date < p_pdqv_rec.from_date) THEN
567 OKC_API.SET_MESSAGE(p_app_name => g_app_name
568 ,p_msg_name => g_to_date_error
569 ,p_token1 => g_col_name_token
570 ,p_token1_value => 'to_date');
571 x_return_status := OKC_API.G_RET_STS_ERROR;
572 RAISE G_EXCEPTION_HALT_VALIDATION;
573 END IF;
574
575 EXCEPTION
576 WHEN G_EXCEPTION_HALT_VALIDATION THEN
577 -- no processing necessary; validation can continue
578 -- with the next column
579 NULL;
580
581 WHEN OTHERS THEN
582 -- store SQL error message on message stack for caller
583 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
584 p_msg_name => g_unexpected_error,
585 p_token1 => g_sqlcode_token,
586 p_token1_value => sqlcode,
587 p_token2 => g_sqlerrm_token,
588 p_token2_value => sqlerrm);
589
590 -- notify caller of an UNEXPECTED error
591 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
592
593 END Validate_To_Date;
594
595 ---------------------------------------------------------------------------
596 -- FUNCTION Validate_Attributes
597 ---------------------------------------------------------------------------
598 -- Start of comments
599 --
600 -- Function Name : Validate_Attributes
601 -- Description :
602 -- Business Rules :
603 -- Parameters :
604 -- Version : 1.0
605 -- End of comments
606 ---------------------------------------------------------------------------
607
608 FUNCTION Validate_Attributes (
609 p_pdqv_rec IN pdqv_rec_type
610 ) RETURN VARCHAR2 IS
611 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
612 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
613 BEGIN
614 -- Validate_Id
615 Validate_Id(p_pdqv_rec,x_return_status);
616 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
617 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
618 -- need to leave
619 l_return_status := x_return_status;
620 RAISE G_EXCEPTION_HALT_VALIDATION;
621 ELSE
622 -- record that there was an error
623 l_return_status := x_return_status;
624 END IF;
625 END IF;
626
627 -- Validate_Object_Version_Number
628 Validate_Object_Version_Number(p_pdqv_rec,x_return_status);
629 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
630 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
631 -- need to leave
632 l_return_status := x_return_status;
633 RAISE G_EXCEPTION_HALT_VALIDATION;
634 ELSE
635 -- record that there was an error
636 l_return_status := x_return_status;
637 END IF;
638 END IF;
639
640 -- Validate_Ptl_Id
641 Validate_Ptl_Id(p_pdqv_rec,x_return_status);
642 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
643 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
644 -- need to leave
645 l_return_status := x_return_status;
646 RAISE G_EXCEPTION_HALT_VALIDATION;
647 ELSE
648 -- record that there was an error
649 l_return_status := x_return_status;
650 END IF;
651 END IF;
652
653 -- Validate_Pqy_Id
654 Validate_Pqy_Id(p_pdqv_rec,x_return_status);
655 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
656 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
657 -- need to leave
658 l_return_status := x_return_status;
659 RAISE G_EXCEPTION_HALT_VALIDATION;
660 ELSE
661 -- record that there was an error
662 l_return_status := x_return_status;
663 END IF;
664 END IF;
665
666 -- Validate_From_Date
667 Validate_From_Date(p_pdqv_rec,x_return_status);
668 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
669 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
670 -- need to leave
671 l_return_status := x_return_status;
672 RAISE G_EXCEPTION_HALT_VALIDATION;
673 ELSE
674 -- record that there was an error
675 l_return_status := x_return_status;
676 END IF;
677 END IF;
678
679 RETURN(l_return_status);
680 EXCEPTION
681 WHEN G_EXCEPTION_HALT_VALIDATION THEN
682 -- just come out with return status
683 NULL;
684 RETURN (l_return_status);
685
686 WHEN OTHERS THEN
687 -- store SQL error message on message stack for caller
688 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
689 p_msg_name => g_unexpected_error,
690 p_token1 => g_sqlcode_token,
691 p_token1_value => sqlcode,
692 p_token2 => g_sqlerrm_token,
693 p_token2_value => sqlerrm);
694 -- notify caller of an UNEXPECTED error
695 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
696 RETURN(l_return_status);
697
698 END Validate_Attributes;
699
700 ---------------------------------------------------------------------------
701 -- PROCEDURE Validate_Unique_Pdq_Record
702 ---------------------------------------------------------------------------
703 -- Start of comments
704 --
705 -- Procedure Name : Validate_Unique_Pdq_Record
706 -- Description :
707 -- Business Rules :
708 -- Parameters :
709 -- Version : 1.0
710 -- End of comments
711 ---------------------------------------------------------------------------
712 PROCEDURE Validate_Unique_Pdq_Record(p_pdqv_rec IN pdqv_rec_type
713 ,x_return_status OUT NOCOPY VARCHAR2)
714 IS
715
716 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
717 l_unq_tbl OKC_UTIL.unq_tbl_type;
718 l_pdq_status VARCHAR2(1);
719 l_row_found Boolean := False;
720 CURSOR c1(p_ptl_id okl_pdt_pqys_v.ptl_id%TYPE,
721 p_pqy_id okl_pdt_pqys_v.pqy_id%TYPE) is
722 SELECT '1'
723 FROM okl_pdt_pqys_v
724 WHERE ptl_id = p_ptl_id
725 AND pqy_id = p_pqy_id
726 AND id <> nvl(p_pdqv_rec.id,-9999);
727
728 BEGIN
729
730 -- initialize return status
731 x_return_status := OKC_API.G_RET_STS_SUCCESS;
732 OPEN c1(p_pdqv_rec.ptl_id,
733 p_pdqv_rec.pqy_id);
734 FETCH c1 into l_pdq_status;
735 l_row_found := c1%FOUND;
736 CLOSE c1;
737 IF l_row_found then
738 OKC_API.set_message(G_APP_NAME,G_UNQS,G_TABLE_TOKEN, 'Okl_Pdt_Pqys_V'); ---CHG001
739 x_return_status := OKC_API.G_RET_STS_ERROR;
740 END IF;
741 EXCEPTION
742 WHEN G_EXCEPTION_HALT_VALIDATION THEN
743 -- no processing necessary; validation can continue
744 -- with the next column
745 NULL;
746
747 WHEN OTHERS THEN
748 -- store SQL error message on message stack for caller
749 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
750 p_msg_name => g_unexpected_error,
751 p_token1 => g_sqlcode_token,
752 p_token1_value => sqlcode,
753 p_token2 => g_sqlerrm_token,
754 p_token2_value => sqlerrm);
755
756 -- notify caller of an UNEXPECTED error
757 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
758
759 END Validate_Unique_Pdq_Record;
760
761 ---------------------------------------------------------------------------
762 -- PROCEDURE Validate_Record
763 ---------------------------------------------------------------------------
764 -- Start of comments
765 --
766 -- Procedure Name : Validate_Record
767 -- Description :
768 -- Business Rules :
769 -- Parameters :
770 -- Version : 1.0
771 -- End of comments
772 ---------------------------------------------------------------------------
773
774 FUNCTION Validate_Record (
775 p_pdqv_rec IN pdqv_rec_type
776 ) RETURN VARCHAR2 IS
777 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
778 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
779 BEGIN
780 -- Validate_To_Date
781 Validate_To_Date(p_pdqv_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 -- Validate_Unique_Pdq_Record
794 Validate_Unique_Pdq_Record(p_pdqv_rec,x_return_status);
795 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
796 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
797 -- need to leave
798 l_return_status := x_return_status;
799 RAISE G_EXCEPTION_HALT_VALIDATION;
800 ELSE
801 -- record that there was an error
802 l_return_status := x_return_status;
803 END IF;
804 END IF;
805
806 RETURN(l_return_status);
807 EXCEPTION
808 WHEN G_EXCEPTION_HALT_VALIDATION THEN
809 -- just come out with return status
810 NULL;
811 RETURN (l_return_status);
812
813 WHEN OTHERS THEN
814 -- store SQL error message on message stack for caller
815 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
816 p_msg_name => g_unexpected_error,
817 p_token1 => g_sqlcode_token,
818 p_token1_value => sqlcode,
819 p_token2 => g_sqlerrm_token,
820 p_token2_value => sqlerrm);
821 -- notify caller of an UNEXPECTED error
822 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
823 RETURN(l_return_status);
824 END Validate_Record;
825
826 /************************** TCHGS: End New Code *****************************/
827
828 ---------------------------------------------------------------------------
829 -- PROCEDURE Migrate
830 ---------------------------------------------------------------------------
831 PROCEDURE migrate (
832 p_from IN pdqv_rec_type,
833 p_to IN OUT NOCOPY pdq_rec_type
834 ) IS
835 BEGIN
836 p_to.id := p_from.id;
837 p_to.ptl_id := p_from.ptl_id;
838 p_to.pqy_id := p_from.pqy_id;
839 p_to.object_version_number := p_from.object_version_number;
840 p_to.from_date := p_from.from_date;
841 p_to.to_date := p_from.to_date;
842 p_to.created_by := p_from.created_by;
843 p_to.creation_date := p_from.creation_date;
844 p_to.last_updated_by := p_from.last_updated_by;
845 p_to.last_update_date := p_from.last_update_date;
846 p_to.last_update_login := p_from.last_update_login;
847 END migrate;
848 PROCEDURE migrate (
849 p_from IN pdq_rec_type,
850 p_to IN OUT NOCOPY pdqv_rec_type
851 ) IS
852 BEGIN
853 p_to.id := p_from.id;
854 p_to.ptl_id := p_from.ptl_id;
855 p_to.pqy_id := p_from.pqy_id;
856 p_to.object_version_number := p_from.object_version_number;
857 p_to.from_date := p_from.from_date;
858 p_to.to_date := p_from.to_date;
859 p_to.created_by := p_from.created_by;
860 p_to.creation_date := p_from.creation_date;
861 p_to.last_updated_by := p_from.last_updated_by;
862 p_to.last_update_date := p_from.last_update_date;
863 p_to.last_update_login := p_from.last_update_login;
864 END migrate;
865
866 ---------------------------------------------------------------------------
867 -- PROCEDURE validate_row
868 ---------------------------------------------------------------------------
869 -------------------------------------
870 -- validate_row for:OKL_PDT_PQYS_V --
871 -------------------------------------
872 PROCEDURE validate_row(
873 p_api_version IN NUMBER,
874 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
875 x_return_status OUT NOCOPY VARCHAR2,
876 x_msg_count OUT NOCOPY NUMBER,
877 x_msg_data OUT NOCOPY VARCHAR2,
878 p_pdqv_rec IN pdqv_rec_type) IS
879
880 l_api_version CONSTANT NUMBER := 1;
881 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
882 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
883 l_pdqv_rec pdqv_rec_type := p_pdqv_rec;
884 l_pdq_rec pdq_rec_type;
885 BEGIN
886 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
887 G_PKG_NAME,
888 p_init_msg_list,
889 l_api_version,
890 p_api_version,
891 '_PVT',
892 x_return_status);
893 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
894 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
895 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
896 RAISE OKC_API.G_EXCEPTION_ERROR;
897 END IF;
898 --- Validate all non-missing attributes (Item Level Validation)
899 l_return_status := Validate_Attributes(l_pdqv_rec);
900 --- If any errors happen abort API
901 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
902 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
903 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
904 RAISE OKC_API.G_EXCEPTION_ERROR;
905 END IF;
906 l_return_status := Validate_Record(l_pdqv_rec);
907 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
909 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_ERROR;
911 END IF;
912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
913 EXCEPTION
914 WHEN OKC_API.G_EXCEPTION_ERROR THEN
915 x_return_status := OKC_API.HANDLE_EXCEPTIONS
916 (
917 l_api_name,
918 G_PKG_NAME,
919 'OKC_API.G_RET_STS_ERROR',
920 x_msg_count,
921 x_msg_data,
922 '_PVT'
923 );
924 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
925 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
926 (
927 l_api_name,
928 G_PKG_NAME,
929 'OKC_API.G_RET_STS_UNEXP_ERROR',
930 x_msg_count,
931 x_msg_data,
932 '_PVT'
933 );
934 WHEN OTHERS THEN
935 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
936 (
937 l_api_name,
938 G_PKG_NAME,
939 'OTHERS',
940 x_msg_count,
941 x_msg_data,
942 '_PVT'
943 );
944 END validate_row;
945 ------------------------------------------
946 -- PL/SQL TBL validate_row for:PDQV_TBL --
947 ------------------------------------------
948 PROCEDURE validate_row(
949 p_api_version IN NUMBER,
950 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
951 x_return_status OUT NOCOPY VARCHAR2,
952 x_msg_count OUT NOCOPY NUMBER,
953 x_msg_data OUT NOCOPY VARCHAR2,
954 p_pdqv_tbl IN pdqv_tbl_type) IS
955
956 l_api_version CONSTANT NUMBER := 1;
957 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
958 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
959 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
960 i NUMBER := 0;
961 BEGIN
962 OKC_API.init_msg_list(p_init_msg_list);
963 -- Make sure PL/SQL table has records in it before passing
964 IF (p_pdqv_tbl.COUNT > 0) THEN
965 i := p_pdqv_tbl.FIRST;
966 LOOP
967 validate_row (
968 p_api_version => p_api_version,
969 p_init_msg_list => OKC_API.G_FALSE,
970 x_return_status => x_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data,
973 p_pdqv_rec => p_pdqv_tbl(i));
974 -- TCHGS: Store the highest degree of error
975 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
976 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
977 l_overall_status := x_return_status;
978 END IF;
979 END IF;
980 EXIT WHEN (i = p_pdqv_tbl.LAST);
981 i := p_pdqv_tbl.NEXT(i);
982 END LOOP;
983 --TCHGS: return overall status
984 x_return_status := l_overall_status;
985 END IF;
986 EXCEPTION
987 WHEN OKC_API.G_EXCEPTION_ERROR THEN
988 x_return_status := OKC_API.HANDLE_EXCEPTIONS
989 (
990 l_api_name,
991 G_PKG_NAME,
992 'OKC_API.G_RET_STS_ERROR',
993 x_msg_count,
994 x_msg_data,
995 '_PVT'
996 );
997 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
998 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
999 (
1000 l_api_name,
1001 G_PKG_NAME,
1002 'OKC_API.G_RET_STS_UNEXP_ERROR',
1003 x_msg_count,
1004 x_msg_data,
1005 '_PVT'
1006 );
1007 WHEN OTHERS THEN
1008 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1009 (
1010 l_api_name,
1011 G_PKG_NAME,
1012 'OTHERS',
1013 x_msg_count,
1014 x_msg_data,
1015 '_PVT'
1016 );
1017 END validate_row;
1018
1019 ---------------------------------------------------------------------------
1020 -- PROCEDURE insert_row
1021 ---------------------------------------------------------------------------
1022 ---------------------------------
1023 -- insert_row for:OKL_PDT_PQYS --
1024 ---------------------------------
1025 PROCEDURE insert_row(
1026 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2,
1030 p_pdq_rec IN pdq_rec_type,
1031 x_pdq_rec OUT NOCOPY pdq_rec_type) IS
1032
1033 l_api_version CONSTANT NUMBER := 1;
1034 l_api_name CONSTANT VARCHAR2(30) := 'PQYS_insert_row';
1035 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1036 l_pdq_rec pdq_rec_type := p_pdq_rec;
1037 l_def_pdq_rec pdq_rec_type;
1038 -------------------------------------
1039 -- Set_Attributes for:OKL_PDT_PQYS --
1040 -------------------------------------
1041 FUNCTION Set_Attributes (
1042 p_pdq_rec IN pdq_rec_type,
1043 x_pdq_rec OUT NOCOPY pdq_rec_type
1044 ) RETURN VARCHAR2 IS
1045 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1046 BEGIN
1047 x_pdq_rec := p_pdq_rec;
1048 RETURN(l_return_status);
1049 END Set_Attributes;
1050 BEGIN
1051 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1052 p_init_msg_list,
1053 '_PVT',
1054 x_return_status);
1055 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1056 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1057 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1058 RAISE OKC_API.G_EXCEPTION_ERROR;
1059 END IF;
1060 --- Setting item attributes
1061 l_return_status := Set_Attributes(
1062 p_pdq_rec, -- IN
1063 l_pdq_rec); -- OUT
1064 --- If any errors happen abort API
1065 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1066 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1067 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1068 RAISE OKC_API.G_EXCEPTION_ERROR;
1069 END IF;
1070 INSERT INTO OKL_PDT_PQYS(
1071 id,
1072 ptl_id,
1073 pqy_id,
1074 object_version_number,
1075 from_date,
1076 to_date,
1077 created_by,
1078 creation_date,
1079 last_updated_by,
1080 last_update_date,
1081 last_update_login)
1082 VALUES (
1083 l_pdq_rec.id,
1084 l_pdq_rec.ptl_id,
1085 l_pdq_rec.pqy_id,
1086 l_pdq_rec.object_version_number,
1087 l_pdq_rec.from_date,
1088 l_pdq_rec.to_date,
1089 l_pdq_rec.created_by,
1090 l_pdq_rec.creation_date,
1091 l_pdq_rec.last_updated_by,
1092 l_pdq_rec.last_update_date,
1093 l_pdq_rec.last_update_login);
1094 -- Set OUT values
1095 x_pdq_rec := l_pdq_rec;
1096 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1097 EXCEPTION
1098 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1099 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1100 (
1101 l_api_name,
1102 G_PKG_NAME,
1103 'OKC_API.G_RET_STS_ERROR',
1104 x_msg_count,
1105 x_msg_data,
1106 '_PVT'
1107 );
1108 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1109 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1110 (
1111 l_api_name,
1112 G_PKG_NAME,
1113 'OKC_API.G_RET_STS_UNEXP_ERROR',
1114 x_msg_count,
1115 x_msg_data,
1116 '_PVT'
1117 );
1118 WHEN OTHERS THEN
1119 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1120 (
1121 l_api_name,
1122 G_PKG_NAME,
1123 'OTHERS',
1124 x_msg_count,
1125 x_msg_data,
1126 '_PVT'
1127 );
1128 END insert_row;
1129 -----------------------------------
1130 -- insert_row for:OKL_PDT_PQYS_V --
1131 -----------------------------------
1132 PROCEDURE insert_row(
1133 p_api_version IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_count OUT NOCOPY NUMBER,
1137 x_msg_data OUT NOCOPY VARCHAR2,
1138 p_pdqv_rec IN pdqv_rec_type,
1139 x_pdqv_rec OUT NOCOPY pdqv_rec_type) IS
1140
1141 l_api_version CONSTANT NUMBER := 1;
1142 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1143 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1144 l_pdqv_rec pdqv_rec_type;
1145 l_def_pdqv_rec pdqv_rec_type;
1146 l_pdq_rec pdq_rec_type;
1147 lx_pdq_rec pdq_rec_type;
1148 -------------------------------
1149 -- FUNCTION fill_who_columns --
1150 -------------------------------
1151 FUNCTION fill_who_columns (
1152 p_pdqv_rec IN pdqv_rec_type
1153 ) RETURN pdqv_rec_type IS
1154 l_pdqv_rec pdqv_rec_type := p_pdqv_rec;
1155 BEGIN
1156 l_pdqv_rec.CREATION_DATE := SYSDATE;
1157 l_pdqv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1158 l_pdqv_rec.LAST_UPDATE_DATE := l_pdqv_rec.CREATION_DATE;
1159 l_pdqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1160 l_pdqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1161 RETURN(l_pdqv_rec);
1162 END fill_who_columns;
1163 ---------------------------------------
1164 -- Set_Attributes for:OKL_PDT_PQYS_V --
1165 ---------------------------------------
1166 FUNCTION Set_Attributes (
1167 p_pdqv_rec IN pdqv_rec_type,
1168 x_pdqv_rec OUT NOCOPY pdqv_rec_type
1169 ) RETURN VARCHAR2 IS
1170 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1171 BEGIN
1172 x_pdqv_rec := p_pdqv_rec;
1173 x_pdqv_rec.OBJECT_VERSION_NUMBER := 1;
1174 RETURN(l_return_status);
1175 END Set_Attributes;
1176 BEGIN
1177 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1178 G_PKG_NAME,
1179 p_init_msg_list,
1180 l_api_version,
1181 p_api_version,
1182 '_PVT',
1183 x_return_status);
1184 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1185 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1186 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1187 RAISE OKC_API.G_EXCEPTION_ERROR;
1188 END IF;
1189 l_pdqv_rec := null_out_defaults(p_pdqv_rec);
1190 -- Set primary key value
1191 l_pdqv_rec.ID := get_seq_id;
1192 --- Setting item attributes
1193 l_return_status := Set_Attributes(
1194 l_pdqv_rec, -- IN
1195 l_def_pdqv_rec); -- OUT
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_def_pdqv_rec := fill_who_columns(l_def_pdqv_rec);
1203 --- Validate all non-missing attributes (Item Level Validation)
1204 l_return_status := Validate_Attributes(l_def_pdqv_rec);
1205 --- If any errors happen abort API
1206 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1207 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1208 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1209 RAISE OKC_API.G_EXCEPTION_ERROR;
1210 END IF;
1211 l_return_status := Validate_Record(l_def_pdqv_rec);
1212 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1214 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1215 RAISE OKC_API.G_EXCEPTION_ERROR;
1216 END IF;
1217 --------------------------------------
1218 -- Move VIEW record to "Child" records
1219 --------------------------------------
1220 migrate(l_def_pdqv_rec, l_pdq_rec);
1221 --------------------------------------------
1222 -- Call the INSERT_ROW for each child record
1223 --------------------------------------------
1224 insert_row(
1225 p_init_msg_list,
1226 x_return_status,
1227 x_msg_count,
1228 x_msg_data,
1229 l_pdq_rec,
1230 lx_pdq_rec
1231 );
1232 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_ERROR;
1236 END IF;
1237 migrate(lx_pdq_rec, l_def_pdqv_rec);
1238 -- Set OUT values
1239 x_pdqv_rec := l_def_pdqv_rec;
1240 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1241 EXCEPTION
1242 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1243 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1244 (
1245 l_api_name,
1246 G_PKG_NAME,
1247 'OKC_API.G_RET_STS_ERROR',
1248 x_msg_count,
1249 x_msg_data,
1250 '_PVT'
1251 );
1252 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1253 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1254 (
1255 l_api_name,
1256 G_PKG_NAME,
1257 'OKC_API.G_RET_STS_UNEXP_ERROR',
1258 x_msg_count,
1259 x_msg_data,
1260 '_PVT'
1261 );
1262 WHEN OTHERS THEN
1263 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1264 (
1265 l_api_name,
1266 G_PKG_NAME,
1267 'OTHERS',
1268 x_msg_count,
1269 x_msg_data,
1270 '_PVT'
1271 );
1272 END insert_row;
1273 ----------------------------------------
1274 -- PL/SQL TBL insert_row for:PDQV_TBL --
1275 ----------------------------------------
1276 PROCEDURE insert_row(
1277 p_api_version IN NUMBER,
1278 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1279 x_return_status OUT NOCOPY VARCHAR2,
1280 x_msg_count OUT NOCOPY NUMBER,
1281 x_msg_data OUT NOCOPY VARCHAR2,
1282 p_pdqv_tbl IN pdqv_tbl_type,
1283 x_pdqv_tbl OUT NOCOPY pdqv_tbl_type) IS
1284
1285 l_api_version CONSTANT NUMBER := 1;
1286 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1287 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1288 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1289 i NUMBER := 0;
1290 BEGIN
1291 OKC_API.init_msg_list(p_init_msg_list);
1292 -- Make sure PL/SQL table has records in it before passing
1293 IF (p_pdqv_tbl.COUNT > 0) THEN
1294 i := p_pdqv_tbl.FIRST;
1295 LOOP
1296 insert_row (
1297 p_api_version => p_api_version,
1298 p_init_msg_list => OKC_API.G_FALSE,
1299 x_return_status => x_return_status,
1300 x_msg_count => x_msg_count,
1301 x_msg_data => x_msg_data,
1302 p_pdqv_rec => p_pdqv_tbl(i),
1303 x_pdqv_rec => x_pdqv_tbl(i));
1304 -- TCHGS: Store the highest degree of error
1305 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1306 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1307 l_overall_status := x_return_status;
1308 END IF;
1309 END IF;
1310 EXIT WHEN (i = p_pdqv_tbl.LAST);
1311 i := p_pdqv_tbl.NEXT(i);
1312 END LOOP;
1313 --TCHGS: return overall status
1314 x_return_status := l_overall_status;
1315 END IF;
1316 EXCEPTION
1317 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1319 (
1320 l_api_name,
1321 G_PKG_NAME,
1322 'OKC_API.G_RET_STS_ERROR',
1323 x_msg_count,
1324 x_msg_data,
1325 '_PVT'
1326 );
1327 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1328 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1329 (
1330 l_api_name,
1331 G_PKG_NAME,
1332 'OKC_API.G_RET_STS_UNEXP_ERROR',
1333 x_msg_count,
1334 x_msg_data,
1335 '_PVT'
1336 );
1337 WHEN OTHERS THEN
1338 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1339 (
1340 l_api_name,
1341 G_PKG_NAME,
1342 'OTHERS',
1343 x_msg_count,
1344 x_msg_data,
1345 '_PVT'
1346 );
1347 END insert_row;
1348
1349 ---------------------------------------------------------------------------
1350 -- PROCEDURE lock_row
1351 ---------------------------------------------------------------------------
1352 -------------------------------
1353 -- lock_row for:OKL_PDT_PQYS --
1354 -------------------------------
1355 PROCEDURE lock_row(
1356 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1357 x_return_status OUT NOCOPY VARCHAR2,
1358 x_msg_count OUT NOCOPY NUMBER,
1359 x_msg_data OUT NOCOPY VARCHAR2,
1360 p_pdq_rec IN pdq_rec_type) IS
1361
1362 E_Resource_Busy EXCEPTION;
1363 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1364 CURSOR lock_csr (p_pdq_rec IN pdq_rec_type) IS
1365 SELECT OBJECT_VERSION_NUMBER
1366 FROM OKL_PDT_PQYS
1367 WHERE ID = p_pdq_rec.id
1368 AND OBJECT_VERSION_NUMBER = p_pdq_rec.object_version_number
1369 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1370
1371 CURSOR lchk_csr (p_pdq_rec IN pdq_rec_type) IS
1372 SELECT OBJECT_VERSION_NUMBER
1373 FROM OKL_PDT_PQYS
1374 WHERE ID = p_pdq_rec.id;
1375 l_api_version CONSTANT NUMBER := 1;
1376 l_api_name CONSTANT VARCHAR2(30) := 'PQYS_lock_row';
1377 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1378 l_object_version_number OKL_PDT_PQYS.OBJECT_VERSION_NUMBER%TYPE;
1379 lc_object_version_number OKL_PDT_PQYS.OBJECT_VERSION_NUMBER%TYPE;
1380 l_row_notfound BOOLEAN := FALSE;
1381 lc_row_notfound BOOLEAN := FALSE;
1382 BEGIN
1383 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1384 p_init_msg_list,
1385 '_PVT',
1386 x_return_status);
1387 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1388 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1389 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1390 RAISE OKC_API.G_EXCEPTION_ERROR;
1391 END IF;
1392 BEGIN
1393 OPEN lock_csr(p_pdq_rec);
1394 FETCH lock_csr INTO l_object_version_number;
1395 l_row_notfound := lock_csr%NOTFOUND;
1396 CLOSE lock_csr;
1397 EXCEPTION
1398 WHEN E_Resource_Busy THEN
1399 IF (lock_csr%ISOPEN) THEN
1400 CLOSE lock_csr;
1401 END IF;
1402 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1403 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1404 END;
1405
1406 IF ( l_row_notfound ) THEN
1407 OPEN lchk_csr(p_pdq_rec);
1408 FETCH lchk_csr INTO lc_object_version_number;
1409 lc_row_notfound := lchk_csr%NOTFOUND;
1410 CLOSE lchk_csr;
1411 END IF;
1412 IF (lc_row_notfound) THEN
1413 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1414 RAISE OKC_API.G_EXCEPTION_ERROR;
1415 ELSIF lc_object_version_number > p_pdq_rec.object_version_number THEN
1416 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1417 RAISE OKC_API.G_EXCEPTION_ERROR;
1418 ELSIF lc_object_version_number <> p_pdq_rec.object_version_number THEN
1419 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1420 RAISE OKC_API.G_EXCEPTION_ERROR;
1421 ELSIF lc_object_version_number = -1 THEN
1422 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1423 RAISE OKC_API.G_EXCEPTION_ERROR;
1424 END IF;
1425 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1426 EXCEPTION
1427 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1428 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1429 (
1430 l_api_name,
1431 G_PKG_NAME,
1432 'OKC_API.G_RET_STS_ERROR',
1433 x_msg_count,
1434 x_msg_data,
1435 '_PVT'
1436 );
1437 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1438 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1439 (
1440 l_api_name,
1441 G_PKG_NAME,
1442 'OKC_API.G_RET_STS_UNEXP_ERROR',
1443 x_msg_count,
1444 x_msg_data,
1445 '_PVT'
1446 );
1447 WHEN OTHERS THEN
1448 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'OTHERS',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 END lock_row;
1458 ---------------------------------
1459 -- lock_row for:OKL_PDT_PQYS_V --
1460 ---------------------------------
1461 PROCEDURE lock_row(
1462 p_api_version IN NUMBER,
1463 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1464 x_return_status OUT NOCOPY VARCHAR2,
1465 x_msg_count OUT NOCOPY NUMBER,
1466 x_msg_data OUT NOCOPY VARCHAR2,
1467 p_pdqv_rec IN pdqv_rec_type) IS
1468
1469 l_api_version CONSTANT NUMBER := 1;
1470 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1471 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1472 l_pdq_rec pdq_rec_type;
1473 BEGIN
1474 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1475 G_PKG_NAME,
1476 p_init_msg_list,
1477 l_api_version,
1478 p_api_version,
1479 '_PVT',
1480 x_return_status);
1481 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1483 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_ERROR;
1485 END IF;
1486 --------------------------------------
1487 -- Move VIEW record to "Child" records
1488 --------------------------------------
1489 migrate(p_pdqv_rec, l_pdq_rec);
1490 --------------------------------------------
1491 -- Call the LOCK_ROW for each child record
1492 --------------------------------------------
1493 lock_row(
1494 p_init_msg_list,
1495 x_return_status,
1496 x_msg_count,
1497 x_msg_data,
1498 l_pdq_rec
1499 );
1500 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1501 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1502 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1503 RAISE OKC_API.G_EXCEPTION_ERROR;
1504 END IF;
1505 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1506 EXCEPTION
1507 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1508 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1509 (
1510 l_api_name,
1511 G_PKG_NAME,
1512 'OKC_API.G_RET_STS_ERROR',
1513 x_msg_count,
1514 x_msg_data,
1515 '_PVT'
1516 );
1517 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1519 (
1520 l_api_name,
1521 G_PKG_NAME,
1522 'OKC_API.G_RET_STS_UNEXP_ERROR',
1523 x_msg_count,
1524 x_msg_data,
1525 '_PVT'
1526 );
1527 WHEN OTHERS THEN
1528 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1529 (
1530 l_api_name,
1531 G_PKG_NAME,
1532 'OTHERS',
1533 x_msg_count,
1534 x_msg_data,
1535 '_PVT'
1536 );
1537 END lock_row;
1538 --------------------------------------
1539 -- PL/SQL TBL lock_row for:PDQV_TBL --
1540 --------------------------------------
1541 PROCEDURE lock_row(
1542 p_api_version IN NUMBER,
1543 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1544 x_return_status OUT NOCOPY VARCHAR2,
1545 x_msg_count OUT NOCOPY NUMBER,
1546 x_msg_data OUT NOCOPY VARCHAR2,
1547 p_pdqv_tbl IN pdqv_tbl_type) IS
1548
1549 l_api_version CONSTANT NUMBER := 1;
1550 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1551 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1552 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1553 i NUMBER := 0;
1554 BEGIN
1555 OKC_API.init_msg_list(p_init_msg_list);
1556 -- Make sure PL/SQL table has records in it before passing
1557 IF (p_pdqv_tbl.COUNT > 0) THEN
1558 i := p_pdqv_tbl.FIRST;
1559 LOOP
1560 lock_row (
1561 p_api_version => p_api_version,
1562 p_init_msg_list => OKC_API.G_FALSE,
1563 x_return_status => x_return_status,
1564 x_msg_count => x_msg_count,
1565 x_msg_data => x_msg_data,
1566 p_pdqv_rec => p_pdqv_tbl(i));
1567 -- TCHGS: Store the highest degree of error
1568 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1569 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1570 l_overall_status := x_return_status;
1571 END IF;
1572 END IF;
1573 EXIT WHEN (i = p_pdqv_tbl.LAST);
1574 i := p_pdqv_tbl.NEXT(i);
1575 END LOOP;
1576 --TCHGS: return overall status
1577 x_return_status := l_overall_status;
1578 END IF;
1579 EXCEPTION
1580 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1581 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1582 (
1583 l_api_name,
1584 G_PKG_NAME,
1585 'OKC_API.G_RET_STS_ERROR',
1586 x_msg_count,
1587 x_msg_data,
1588 '_PVT'
1589 );
1590 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1591 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1592 (
1593 l_api_name,
1594 G_PKG_NAME,
1595 'OKC_API.G_RET_STS_UNEXP_ERROR',
1596 x_msg_count,
1597 x_msg_data,
1598 '_PVT'
1599 );
1600 WHEN OTHERS THEN
1601 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1602 (
1603 l_api_name,
1604 G_PKG_NAME,
1605 'OTHERS',
1606 x_msg_count,
1607 x_msg_data,
1608 '_PVT'
1609 );
1610 END lock_row;
1611
1612 ---------------------------------------------------------------------------
1613 -- PROCEDURE update_row
1614 ---------------------------------------------------------------------------
1615 ---------------------------------
1616 -- update_row for:OKL_PDT_PQYS --
1617 ---------------------------------
1618 PROCEDURE update_row(
1619 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1620 x_return_status OUT NOCOPY VARCHAR2,
1621 x_msg_count OUT NOCOPY NUMBER,
1622 x_msg_data OUT NOCOPY VARCHAR2,
1623 p_pdq_rec IN pdq_rec_type,
1624 x_pdq_rec OUT NOCOPY pdq_rec_type) IS
1625
1626 l_api_version CONSTANT NUMBER := 1;
1627 l_api_name CONSTANT VARCHAR2(30) := 'PQYS_update_row';
1628 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1629 l_pdq_rec pdq_rec_type := p_pdq_rec;
1630 l_def_pdq_rec pdq_rec_type;
1631 l_row_notfound BOOLEAN := TRUE;
1632 ----------------------------------
1633 -- FUNCTION populate_new_record --
1634 ----------------------------------
1635 FUNCTION populate_new_record (
1636 p_pdq_rec IN pdq_rec_type,
1637 x_pdq_rec OUT NOCOPY pdq_rec_type
1638 ) RETURN VARCHAR2 IS
1639 l_pdq_rec pdq_rec_type;
1640 l_row_notfound BOOLEAN := TRUE;
1641 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1642 BEGIN
1643 x_pdq_rec := p_pdq_rec;
1644 -- Get current database values
1645 l_pdq_rec := get_rec(p_pdq_rec, l_row_notfound);
1646 IF (l_row_notfound) THEN
1647 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1648 END IF;
1649 IF (x_pdq_rec.id = OKC_API.G_MISS_NUM)
1650 THEN
1651 x_pdq_rec.id := l_pdq_rec.id;
1652 END IF;
1653 IF (x_pdq_rec.ptl_id = OKC_API.G_MISS_NUM)
1654 THEN
1655 x_pdq_rec.ptl_id := l_pdq_rec.ptl_id;
1656 END IF;
1657 IF (x_pdq_rec.pqy_id = OKC_API.G_MISS_NUM)
1658 THEN
1659 x_pdq_rec.pqy_id := l_pdq_rec.pqy_id;
1660 END IF;
1661 IF (x_pdq_rec.object_version_number = OKC_API.G_MISS_NUM)
1662 THEN
1663 x_pdq_rec.object_version_number := l_pdq_rec.object_version_number;
1664 END IF;
1665 IF (x_pdq_rec.from_date = OKC_API.G_MISS_DATE)
1666 THEN
1667 x_pdq_rec.from_date := l_pdq_rec.from_date;
1668 END IF;
1669 IF (x_pdq_rec.to_date = OKC_API.G_MISS_DATE)
1670 THEN
1671 x_pdq_rec.to_date := l_pdq_rec.to_date;
1672 END IF;
1673 IF (x_pdq_rec.created_by = OKC_API.G_MISS_NUM)
1674 THEN
1675 x_pdq_rec.created_by := l_pdq_rec.created_by;
1676 END IF;
1677 IF (x_pdq_rec.creation_date = OKC_API.G_MISS_DATE)
1678 THEN
1679 x_pdq_rec.creation_date := l_pdq_rec.creation_date;
1680 END IF;
1681 IF (x_pdq_rec.last_updated_by = OKC_API.G_MISS_NUM)
1682 THEN
1683 x_pdq_rec.last_updated_by := l_pdq_rec.last_updated_by;
1684 END IF;
1685 IF (x_pdq_rec.last_update_date = OKC_API.G_MISS_DATE)
1686 THEN
1687 x_pdq_rec.last_update_date := l_pdq_rec.last_update_date;
1688 END IF;
1689 IF (x_pdq_rec.last_update_login = OKC_API.G_MISS_NUM)
1690 THEN
1691 x_pdq_rec.last_update_login := l_pdq_rec.last_update_login;
1692 END IF;
1693 RETURN(l_return_status);
1694 END populate_new_record;
1695 -------------------------------------
1696 -- Set_Attributes for:OKL_PDT_PQYS --
1697 -------------------------------------
1698 FUNCTION Set_Attributes (
1699 p_pdq_rec IN pdq_rec_type,
1700 x_pdq_rec OUT NOCOPY pdq_rec_type
1701 ) RETURN VARCHAR2 IS
1702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1703 BEGIN
1704 x_pdq_rec := p_pdq_rec;
1705 RETURN(l_return_status);
1706 END Set_Attributes;
1707 BEGIN
1708 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1709 p_init_msg_list,
1710 '_PVT',
1711 x_return_status);
1712 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1713 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1714 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1715 RAISE OKC_API.G_EXCEPTION_ERROR;
1716 END IF;
1717 --- Setting item attributes
1718 l_return_status := Set_Attributes(
1719 p_pdq_rec, -- IN
1720 l_pdq_rec); -- OUT
1721 --- If any errors happen abort API
1722 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1723 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1724 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1725 RAISE OKC_API.G_EXCEPTION_ERROR;
1726 END IF;
1727 l_return_status := populate_new_record(l_pdq_rec, l_def_pdq_rec);
1728 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1729 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1730 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1731 RAISE OKC_API.G_EXCEPTION_ERROR;
1732 END IF;
1733 UPDATE OKL_PDT_PQYS
1734 SET PTL_ID = l_def_pdq_rec.ptl_id,
1735 PQY_ID = l_def_pdq_rec.pqy_id,
1736 OBJECT_VERSION_NUMBER = l_def_pdq_rec.object_version_number,
1737 FROM_DATE = l_def_pdq_rec.from_date,
1738 TO_DATE = l_def_pdq_rec.to_date,
1739 CREATED_BY = l_def_pdq_rec.created_by,
1740 CREATION_DATE = l_def_pdq_rec.creation_date,
1741 LAST_UPDATED_BY = l_def_pdq_rec.last_updated_by,
1742 LAST_UPDATE_DATE = l_def_pdq_rec.last_update_date,
1743 LAST_UPDATE_LOGIN = l_def_pdq_rec.last_update_login
1744 WHERE ID = l_def_pdq_rec.id;
1745
1746 x_pdq_rec := l_def_pdq_rec;
1747 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1748 EXCEPTION
1749 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1750 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1751 (
1752 l_api_name,
1753 G_PKG_NAME,
1754 'OKC_API.G_RET_STS_ERROR',
1755 x_msg_count,
1756 x_msg_data,
1757 '_PVT'
1758 );
1759 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1760 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1761 (
1762 l_api_name,
1763 G_PKG_NAME,
1764 'OKC_API.G_RET_STS_UNEXP_ERROR',
1765 x_msg_count,
1766 x_msg_data,
1767 '_PVT'
1768 );
1769 WHEN OTHERS THEN
1770 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1771 (
1772 l_api_name,
1773 G_PKG_NAME,
1774 'OTHERS',
1775 x_msg_count,
1776 x_msg_data,
1777 '_PVT'
1778 );
1779 END update_row;
1780 -----------------------------------
1781 -- update_row for:OKL_PDT_PQYS_V --
1782 -----------------------------------
1783 PROCEDURE update_row(
1784 p_api_version IN NUMBER,
1785 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_msg_data OUT NOCOPY VARCHAR2,
1789 p_pdqv_rec IN pdqv_rec_type,
1790 x_pdqv_rec OUT NOCOPY pdqv_rec_type) IS
1791
1792 l_api_version CONSTANT NUMBER := 1;
1793 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1794 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1795 l_pdqv_rec pdqv_rec_type := p_pdqv_rec;
1796 l_def_pdqv_rec pdqv_rec_type;
1797 l_pdq_rec pdq_rec_type;
1798 lx_pdq_rec pdq_rec_type;
1799 -------------------------------
1800 -- FUNCTION fill_who_columns --
1801 -------------------------------
1802 FUNCTION fill_who_columns (
1803 p_pdqv_rec IN pdqv_rec_type
1804 ) RETURN pdqv_rec_type IS
1805 l_pdqv_rec pdqv_rec_type := p_pdqv_rec;
1806 BEGIN
1807 l_pdqv_rec.LAST_UPDATE_DATE := SYSDATE;
1808 l_pdqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1809 l_pdqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1810 RETURN(l_pdqv_rec);
1811 END fill_who_columns;
1812 ----------------------------------
1813 -- FUNCTION populate_new_record --
1814 ----------------------------------
1815 FUNCTION populate_new_record (
1816 p_pdqv_rec IN pdqv_rec_type,
1817 x_pdqv_rec OUT NOCOPY pdqv_rec_type
1818 ) RETURN VARCHAR2 IS
1819 l_pdqv_rec pdqv_rec_type;
1820 l_row_notfound BOOLEAN := TRUE;
1821 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1822 BEGIN
1823 x_pdqv_rec := p_pdqv_rec;
1824 -- Get current database values
1825 l_pdqv_rec := get_rec(p_pdqv_rec, l_row_notfound);
1826 IF (l_row_notfound) THEN
1827 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1828 END IF;
1829 IF (x_pdqv_rec.id = OKC_API.G_MISS_NUM)
1830 THEN
1831 x_pdqv_rec.id := l_pdqv_rec.id;
1832 END IF;
1833 IF (x_pdqv_rec.object_version_number = OKC_API.G_MISS_NUM)
1834 THEN
1835 x_pdqv_rec.object_version_number := l_pdqv_rec.object_version_number;
1836 END IF;
1837 IF (x_pdqv_rec.ptl_id = OKC_API.G_MISS_NUM)
1838 THEN
1839 x_pdqv_rec.ptl_id := l_pdqv_rec.ptl_id;
1840 END IF;
1841 IF (x_pdqv_rec.pqy_id = OKC_API.G_MISS_NUM)
1842 THEN
1843 x_pdqv_rec.pqy_id := l_pdqv_rec.pqy_id;
1844 END IF;
1845 IF (x_pdqv_rec.from_date = OKC_API.G_MISS_DATE)
1846 THEN
1847 x_pdqv_rec.from_date := l_pdqv_rec.from_date;
1848 END IF;
1849 IF (x_pdqv_rec.to_date = OKC_API.G_MISS_DATE)
1850 THEN
1851 x_pdqv_rec.to_date := l_pdqv_rec.to_date;
1852 END IF;
1853 IF (x_pdqv_rec.created_by = OKC_API.G_MISS_NUM)
1854 THEN
1855 x_pdqv_rec.created_by := l_pdqv_rec.created_by;
1856 END IF;
1857 IF (x_pdqv_rec.creation_date = OKC_API.G_MISS_DATE)
1858 THEN
1859 x_pdqv_rec.creation_date := l_pdqv_rec.creation_date;
1860 END IF;
1861 IF (x_pdqv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1862 THEN
1863 x_pdqv_rec.last_updated_by := l_pdqv_rec.last_updated_by;
1864 END IF;
1865 IF (x_pdqv_rec.last_update_date = OKC_API.G_MISS_DATE)
1866 THEN
1867 x_pdqv_rec.last_update_date := l_pdqv_rec.last_update_date;
1868 END IF;
1869 IF (x_pdqv_rec.last_update_login = OKC_API.G_MISS_NUM)
1870 THEN
1871 x_pdqv_rec.last_update_login := l_pdqv_rec.last_update_login;
1872 END IF;
1873 RETURN(l_return_status);
1874 END populate_new_record;
1875 ---------------------------------------
1876 -- Set_Attributes for:OKL_PDT_PQYS_V --
1877 ---------------------------------------
1878 FUNCTION Set_Attributes (
1879 p_pdqv_rec IN pdqv_rec_type,
1880 x_pdqv_rec OUT NOCOPY pdqv_rec_type
1881 ) RETURN VARCHAR2 IS
1882 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1883 BEGIN
1884 x_pdqv_rec := p_pdqv_rec;
1885 x_pdqv_rec.OBJECT_VERSION_NUMBER := NVL(x_pdqv_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_pdqv_rec, -- IN
1904 l_pdqv_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_pdqv_rec, l_def_pdqv_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_pdqv_rec := fill_who_columns(l_def_pdqv_rec);
1918 --- Validate all non-missing attributes (Item Level Validation)
1919 l_return_status := Validate_Attributes(l_def_pdqv_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_pdqv_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_pdqv_rec, l_pdq_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_pdq_rec,
1946 lx_pdq_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_pdq_rec, l_def_pdqv_rec);
1954 x_pdqv_rec := l_def_pdqv_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:PDQV_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_pdqv_tbl IN pdqv_tbl_type,
1998 x_pdqv_tbl OUT NOCOPY pdqv_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_pdqv_tbl.COUNT > 0) THEN
2009 i := p_pdqv_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_pdqv_rec => p_pdqv_tbl(i),
2018 x_pdqv_rec => x_pdqv_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_pdqv_tbl.LAST);
2026 i := p_pdqv_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_PDT_PQYS --
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_pdq_rec IN pdq_rec_type) IS
2076
2077 l_api_version CONSTANT NUMBER := 1;
2078 l_api_name CONSTANT VARCHAR2(30) := 'PQYS_delete_row';
2079 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2080 l_pdq_rec pdq_rec_type:= p_pdq_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_PDT_PQYS
2093 WHERE ID = l_pdq_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_PDT_PQYS_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_pdqv_rec IN pdqv_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_pdqv_rec pdqv_rec_type := p_pdqv_rec;
2143 l_pdq_rec pdq_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_pdqv_rec, l_pdq_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_pdq_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:PDQV_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_pdqv_tbl IN pdqv_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_pdqv_tbl.COUNT > 0) THEN
2229 i := p_pdqv_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_pdqv_rec => p_pdqv_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_pdqv_tbl.LAST);
2245 i := p_pdqv_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_PDQ_PVT;