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