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