[Home] [Help]
PACKAGE BODY: APPS.OKC_CPS_PVT
Source
1 PACKAGE BODY OKC_CPS_PVT AS
2 /* $Header: OKCSCPSB.pls 120.0 2005/05/25 19:42:48 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /************************ HAND-CODED *********************************/
7 FUNCTION Validate_Attributes ( p_cpsv_rec IN cpsv_rec_type)
8 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) := 'ERROR_MESSAGE';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
13 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_K_PROCESSES_V';
14 G_EXCEPTION_HALT_VALIDATION exception;
15 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16
17 -- Start of comments
18 --
19 -- Procedure Name : validate_pdf_id
20 -- Description :
21 -- Business Rules :
22 -- Parameters :
23 -- Version : 1.0
24 -- End of comments
25 PROCEDURE validate_pdf_id(x_return_status OUT NOCOPY VARCHAR2,
26 p_cpsv_rec IN cpsv_rec_type) is
27
28 l_dummy_var VARCHAR2(1) := '?';
29 Cursor l_pdfv_csr Is
30 select 'x'
31 from OKC_PROCESS_DEFS_B
32 where ID = p_cpsv_rec.pdf_id;
33 Begin
34 -- initialize return status
35 x_return_status := OKC_API.G_RET_STS_SUCCESS;
36
37 -- check that data exists
38 If (p_cpsv_rec.pdf_id = OKC_API.G_MISS_NUM or
39 p_cpsv_rec.pdf_id IS NULL)
40 Then
41 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
42 p_msg_name => g_required_value,
43 p_token1 => g_col_name_token,
44 p_token1_value => 'pdf_id/Workflow Name');
45 -- set error flag
46 x_return_status := OKC_API.G_RET_STS_ERROR;
47
48 -- halt validation
49 raise G_EXCEPTION_HALT_VALIDATION;
50 End If;
51
52 -- enforce foreign key
53 Open l_pdfv_csr;
54 Fetch l_pdfv_csr Into l_dummy_var;
55 Close l_pdfv_csr;
56
57 -- if l_dummy_var still set to default, data was not found
58 If (l_dummy_var = '?') Then
59 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
60 p_msg_name => g_no_parent_record,
61 p_token1 => g_col_name_token,
62 p_token1_value => 'pdf_id',
63 p_token2 => g_child_table_token,
64 p_token2_value => G_VIEW,
65 p_token3 => g_parent_table_token,
66 p_token3_value => 'OKC_PROCESS_DEFS_V');
67 -- notify caller of an error
68 x_return_status := OKC_API.G_RET_STS_ERROR;
69 End If;
70 exception
71 when G_EXCEPTION_HALT_VALIDATION then
72 -- no processing necessary; validation can continue with next column
73 null;
74
75 when OTHERS then
76 -- store SQL error message on message stack
77 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
78 p_msg_name => g_unexpected_error,
79 p_token1 => g_sqlcode_token,
80 p_token1_value => sqlcode,
81 p_token2 => g_sqlerrm_token,
82 p_token2_value => sqlerrm);
83 -- set error flag as UNEXPETED error
84 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
85
86 -- verify that cursor was closed
87 if l_pdfv_csr%ISOPEN then
88 close l_pdfv_csr;
89 end if;
90
91 End validate_pdf_id;
92
93 -- Start of comments
94 --
95 -- Procedure Name : validate_chr_id
96 -- Description :
97 -- Business Rules :
98 -- Parameters :
99 -- Version : 1.0
100 -- End of comments
101 PROCEDURE validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
102 p_cpsv_rec IN cpsv_rec_type) is
103
104 l_dummy_var VARCHAR2(1) := '?';
105 Cursor l_chrv_csr Is
106 select 'x'
107 from OKC_K_HEADERS_B
108 where ID = p_cpsv_rec.chr_id;
109 Begin
110 -- initialize return status
111 x_return_status := OKC_API.G_RET_STS_SUCCESS;
112 -- check that data exists
113 If (p_cpsv_rec.chr_id <> OKC_API.G_MISS_NUM and
114 p_cpsv_rec.chr_id IS NOT NULL)
115 Then
116 -- enforce foreign key
117 Open l_chrv_csr;
118 Fetch l_chrv_csr Into l_dummy_var;
119 Close l_chrv_csr;
120
121 -- if l_dummy_var still set to default, data was not found
122 If (l_dummy_var = '?') Then
123 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
124 p_msg_name => g_no_parent_record,
125 p_token1 => g_col_name_token,
126 p_token1_value => 'chr_id',
127 p_token2 => g_child_table_token,
128 p_token2_value => G_VIEW,
129 p_token3 => g_parent_table_token,
130 p_token3_value => 'OKC_K_HEADERS_V');
131 -- notify caller of an error
132 x_return_status := OKC_API.G_RET_STS_ERROR;
133 End If;
134 End If;
135 exception
136 when OTHERS then
137 -- store SQL error message on message stack
138 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
139 p_msg_name => g_unexpected_error,
140 p_token1 => g_sqlcode_token,
141 p_token1_value => sqlcode,
142 p_token2 => g_sqlerrm_token,
143 p_token2_value => sqlerrm);
144 -- set error flag as UNEXPETED error
145 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
146
147 -- verify that cursor was closed
148 if l_chrv_csr%ISOPEN then
149 close l_chrv_csr;
150 end if;
151
152 End validate_chr_id;
153
154 -- Start of comments
155 --
156 -- Procedure Name : validate_user_id
157 -- Description :
158 -- Business Rules :
159 -- Parameters :
160 -- Version : 1.0
161 -- End of comments
162 PROCEDURE validate_user_id(x_return_status OUT NOCOPY VARCHAR2,
163 p_cpsv_rec IN cpsv_rec_type) is
164
165 l_dummy_var VARCHAR2(1) := '?';
166 Cursor l_usrv_csr Is
167 select 'x'
168 from FND_USER_VIEW
169 where USER_ID = p_cpsv_rec.user_id;
170 Begin
171 -- initialize return status
172 x_return_status := OKC_API.G_RET_STS_SUCCESS;
173
174 -- check that data exists
175 If (p_cpsv_rec.user_id <> OKC_API.G_MISS_NUM and
176 p_cpsv_rec.user_id IS NOT NULL)
177 Then
178 -- enforce foreign key
179 Open l_usrv_csr;
180 Fetch l_usrv_csr Into l_dummy_var;
181 Close l_usrv_csr;
182
183 -- if l_dummy_var still set to default, data was not found
184 If (l_dummy_var = '?') Then
185 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
186 p_msg_name => g_no_parent_record,
187 p_token1 => g_col_name_token,
188 p_token1_value => 'user_id',
189 p_token2 => g_child_table_token,
190 p_token2_value => G_VIEW,
191 p_token3 => g_parent_table_token,
192 p_token3_value => 'FND_USER_VIEW');
193 -- notify caller of an error
194 x_return_status := OKC_API.G_RET_STS_ERROR;
195 End If;
196 End If;
197 exception
198 when OTHERS then
199 -- store SQL error message on message stack
200 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
201 p_msg_name => g_unexpected_error,
202 p_token1 => g_sqlcode_token,
203 p_token1_value => sqlcode,
204 p_token2 => g_sqlerrm_token,
205 p_token2_value => sqlerrm);
206 -- set error flag as UNEXPETED error
207 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
208
209 -- verify that cursor was closed
210 if l_usrv_csr%ISOPEN then
211 close l_usrv_csr;
212 end if;
213
214 End validate_user_id;
215
216 -- Start of comments
217 --
218 -- Procedure Name : validate_crt_id
219 -- Description :
220 -- Business Rules :
221 -- Parameters :
222 -- Version : 1.0
223 -- End of comments
224 PROCEDURE validate_crt_id(x_return_status OUT NOCOPY VARCHAR2,
225 p_cpsv_rec IN cpsv_rec_type) is
226
227 l_dummy_var VARCHAR2(1) := '?';
228 Cursor l_cpsv_csr Is
229 select 'x'
230 from OKC_CHANGE_REQUESTS_B
231 where ID = p_cpsv_rec.crt_id;
232 Begin
233 -- initialize return status
234 x_return_status := OKC_API.G_RET_STS_SUCCESS;
235
236 -- check that data exists
237 If (p_cpsv_rec.crt_id <> OKC_API.G_MISS_NUM and
238 p_cpsv_rec.crt_id IS NOT NULL)
239 Then
240 -- enforce foreign key
241 Open l_cpsv_csr;
242 Fetch l_cpsv_csr Into l_dummy_var;
243 Close l_cpsv_csr;
244
245 -- if l_dummy_var still set to default, data was not found
246 If (l_dummy_var = '?') Then
247 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
248 p_msg_name => g_no_parent_record,
249 p_token1 => g_col_name_token,
250 p_token1_value => 'crt_id',
251 p_token2 => g_child_table_token,
252 p_token2_value => G_VIEW,
253 p_token3 => g_parent_table_token,
254 p_token3_value => 'OKC_CHANGE_REQUESTS_V');
255 -- notify caller of an error
256 x_return_status := OKC_API.G_RET_STS_ERROR;
257 End If;
258 End If;
259 exception
260 when OTHERS then
261 -- store SQL error message on message stack
262 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
263 p_msg_name => g_unexpected_error,
264 p_token1 => g_sqlcode_token,
265 p_token1_value => sqlcode,
266 p_token2 => g_sqlerrm_token,
267 p_token2_value => sqlerrm);
268 -- set error flag as UNEXPETED error
269 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
270
271 -- verify that cursor was closed
272 if l_cpsv_csr%ISOPEN then
273 close l_cpsv_csr;
274 end if;
275
276 End validate_crt_id;
277
278 -- Start of comments
279 --
280 -- Procedure Name : validate_process_id
281 -- Description :
282 -- Business Rules :
283 -- Parameters :
284 -- Version : 1.0
285 -- End of comments
286 PROCEDURE validate_process_id(x_return_status OUT NOCOPY VARCHAR2,
287 p_cpsv_rec IN cpsv_rec_type) is
288 Begin
289 -- initialize return status
290 x_return_status := OKC_API.G_RET_STS_SUCCESS;
291
292 -- call column length utility
293 If (p_cpsv_rec.process_id is not null and
294 p_cpsv_rec.process_id <> OKC_API.G_MISS_CHAR)
295 Then
296 If (length(p_cpsv_rec.process_id) > 240) Then
297 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
298 p_msg_name => G_LEN_CHK,
299 p_token1 => G_COL_NAME_TOKEN,
300 p_token1_value => 'Process Id',
301 p_token2 => 'COL_LEN',
302 p_token2_value => '240');
303
304 x_return_status := OKC_API.G_RET_STS_ERROR;
305 End If;
306
307 -- if length is not within allowed limits, set error flag
308 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
309 raise G_EXCEPTION_HALT_VALIDATION;
310 End If;
311 End If;
312
313 exception
314 when G_EXCEPTION_HALT_VALIDATION then
315 -- no processing necessary; validation can continue with next column
316 null;
317
318 when OTHERS then
319 -- store SQL error message on message stack
320 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
321 p_msg_name => g_unexpected_error,
322 p_token1 => g_sqlcode_token,
323 p_token1_value => sqlcode,
324 p_token2 => g_sqlerrm_token,
325 p_token2_value => sqlerrm);
326 -- set error flag as UNEXPETED error
327 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
328 End validate_process_id;
329
330 -- Start of comments
331 --
332 -- Procedure Name : validate_in_process_yn
333 -- Description :
334 -- Business Rules :
335 -- Parameters :
336 -- Version : 1.0
337 -- End of comments
338 PROCEDURE validate_in_process_yn(x_return_status OUT NOCOPY VARCHAR2,
339 p_cpsv_rec IN cpsv_rec_type) is
340 Begin
341 -- initialize return status
342 x_return_status := OKC_API.G_RET_STS_SUCCESS;
343
344 -- check that data exists
345 If (p_cpsv_rec.in_process_yn <> OKC_API.G_MISS_CHAR and
346 p_cpsv_rec.in_process_yn IS NOT NULL)
347 Then
348 If (upper(p_cpsv_rec.in_process_yn) NOT IN ('Y','N','E')) Then
349 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
350 p_msg_name => g_invalid_value,
351 p_token1 => g_col_name_token,
352 p_token1_value => 'in_process_yn');
353 -- notify caller of an error
354 x_return_status := OKC_API.G_RET_STS_ERROR;
355
356 -- halt validation
357 raise G_EXCEPTION_HALT_VALIDATION;
358 end if;
359 End If;
360 exception
361 when G_EXCEPTION_HALT_VALIDATION then
362 -- no processing necessary; validation can continue with next column
363 null;
364
365 when OTHERS then
366 -- store SQL error message on message stack
367 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
368 p_msg_name => g_unexpected_error,
369 p_token1 => g_sqlcode_token,
370 p_token1_value => sqlcode,
371 p_token2 => g_sqlerrm_token,
372 p_token2_value => sqlerrm);
373 -- notify caller of an error as UNEXPETED error
374 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
375 End validate_in_process_yn;
376
377 /*********************** END HAND-CODED ********************************/
378 ---------------------------------------------------------------------------
379 -- FUNCTION get_seq_id
380 ---------------------------------------------------------------------------
381 FUNCTION get_seq_id RETURN NUMBER IS
382 BEGIN
383 RETURN(okc_p_util.raw_to_number(sys_guid()));
384 END get_seq_id;
385
386 ---------------------------------------------------------------------------
387 -- PROCEDURE qc
388 ---------------------------------------------------------------------------
389 PROCEDURE qc IS
390 BEGIN
391 null;
392 END qc;
393
394 ---------------------------------------------------------------------------
395 -- PROCEDURE change_version
396 ---------------------------------------------------------------------------
397 PROCEDURE change_version IS
398 BEGIN
399 null;
400 END change_version;
401
402 ---------------------------------------------------------------------------
403 -- PROCEDURE api_copy
404 ---------------------------------------------------------------------------
405 PROCEDURE api_copy IS
406 BEGIN
407 null;
408 END api_copy;
409
410 ---------------------------------------------------------------------------
411 -- FUNCTION get_rec for: OKC_K_PROCESSES
412 ---------------------------------------------------------------------------
413 FUNCTION get_rec (
414 p_cps_rec IN cps_rec_type,
415 x_no_data_found OUT NOCOPY BOOLEAN
416 ) RETURN cps_rec_type IS
417 CURSOR cps_pk_csr (p_id IN NUMBER) IS
418 SELECT
419 ID,
420 PDF_ID,
421 CHR_ID,
422 USER_ID,
423 CRT_ID,
424 OBJECT_VERSION_NUMBER,
425 CREATED_BY,
426 CREATION_DATE,
427 LAST_UPDATED_BY,
428 LAST_UPDATE_DATE,
429 PROCESS_ID,
430 IN_PROCESS_YN,
431 LAST_UPDATE_LOGIN,
432 ATTRIBUTE_CATEGORY,
433 ATTRIBUTE1,
434 ATTRIBUTE2,
435 ATTRIBUTE3,
436 ATTRIBUTE4,
437 ATTRIBUTE5,
438 ATTRIBUTE6,
439 ATTRIBUTE7,
440 ATTRIBUTE8,
441 ATTRIBUTE9,
442 ATTRIBUTE10,
443 ATTRIBUTE11,
444 ATTRIBUTE12,
445 ATTRIBUTE13,
446 ATTRIBUTE14,
447 ATTRIBUTE15
448 FROM Okc_K_Processes
449 WHERE okc_k_processes.id = p_id;
450 l_cps_pk cps_pk_csr%ROWTYPE;
451 l_cps_rec cps_rec_type;
452 BEGIN
453 x_no_data_found := TRUE;
454 -- Get current database values
455 OPEN cps_pk_csr (p_cps_rec.id);
456 FETCH cps_pk_csr INTO
457 l_cps_rec.ID,
458 l_cps_rec.PDF_ID,
459 l_cps_rec.CHR_ID,
460 l_cps_rec.USER_ID,
461 l_cps_rec.CRT_ID,
462 l_cps_rec.OBJECT_VERSION_NUMBER,
463 l_cps_rec.CREATED_BY,
464 l_cps_rec.CREATION_DATE,
465 l_cps_rec.LAST_UPDATED_BY,
466 l_cps_rec.LAST_UPDATE_DATE,
467 l_cps_rec.PROCESS_ID,
468 l_cps_rec.IN_PROCESS_YN,
469 l_cps_rec.LAST_UPDATE_LOGIN,
470 l_cps_rec.ATTRIBUTE_CATEGORY,
471 l_cps_rec.ATTRIBUTE1,
472 l_cps_rec.ATTRIBUTE2,
473 l_cps_rec.ATTRIBUTE3,
474 l_cps_rec.ATTRIBUTE4,
475 l_cps_rec.ATTRIBUTE5,
476 l_cps_rec.ATTRIBUTE6,
477 l_cps_rec.ATTRIBUTE7,
478 l_cps_rec.ATTRIBUTE8,
479 l_cps_rec.ATTRIBUTE9,
480 l_cps_rec.ATTRIBUTE10,
481 l_cps_rec.ATTRIBUTE11,
482 l_cps_rec.ATTRIBUTE12,
483 l_cps_rec.ATTRIBUTE13,
484 l_cps_rec.ATTRIBUTE14,
485 l_cps_rec.ATTRIBUTE15;
486 x_no_data_found := cps_pk_csr%NOTFOUND;
487 CLOSE cps_pk_csr;
488 RETURN(l_cps_rec);
489 END get_rec;
490
491 FUNCTION get_rec (
492 p_cps_rec IN cps_rec_type
493 ) RETURN cps_rec_type IS
494 l_row_notfound BOOLEAN := TRUE;
495 BEGIN
496 RETURN(get_rec(p_cps_rec, l_row_notfound));
497 END get_rec;
498 ---------------------------------------------------------------------------
499 -- FUNCTION get_rec for: OKC_K_PROCESSES_V
500 ---------------------------------------------------------------------------
501 FUNCTION get_rec (
502 p_cpsv_rec IN cpsv_rec_type,
503 x_no_data_found OUT NOCOPY BOOLEAN
504 ) RETURN cpsv_rec_type IS
505 CURSOR okc_cpsv_pk_csr (p_id IN NUMBER) IS
506 SELECT
507 ID,
508 OBJECT_VERSION_NUMBER,
509 PDF_ID,
510 CHR_ID,
511 USER_ID,
512 CRT_ID,
513 PROCESS_ID,
514 IN_PROCESS_YN,
515 ATTRIBUTE_CATEGORY,
516 ATTRIBUTE1,
517 ATTRIBUTE2,
518 ATTRIBUTE3,
519 ATTRIBUTE4,
520 ATTRIBUTE5,
521 ATTRIBUTE6,
522 ATTRIBUTE7,
523 ATTRIBUTE8,
524 ATTRIBUTE9,
525 ATTRIBUTE10,
526 ATTRIBUTE11,
527 ATTRIBUTE12,
528 ATTRIBUTE13,
529 ATTRIBUTE14,
530 ATTRIBUTE15,
531 CREATED_BY,
532 CREATION_DATE,
533 LAST_UPDATED_BY,
534 LAST_UPDATE_DATE,
535 LAST_UPDATE_LOGIN
536 FROM Okc_K_Processes_V
537 WHERE okc_k_processes_v.id = p_id;
538 l_okc_cpsv_pk okc_cpsv_pk_csr%ROWTYPE;
539 l_cpsv_rec cpsv_rec_type;
540 BEGIN
541 x_no_data_found := TRUE;
542 -- Get current database values
543 OPEN okc_cpsv_pk_csr (p_cpsv_rec.id);
544 FETCH okc_cpsv_pk_csr INTO
545 l_cpsv_rec.ID,
546 l_cpsv_rec.OBJECT_VERSION_NUMBER,
547 l_cpsv_rec.PDF_ID,
548 l_cpsv_rec.CHR_ID,
549 l_cpsv_rec.USER_ID,
550 l_cpsv_rec.CRT_ID,
551 l_cpsv_rec.PROCESS_ID,
552 l_cpsv_rec.IN_PROCESS_YN,
553 l_cpsv_rec.ATTRIBUTE_CATEGORY,
554 l_cpsv_rec.ATTRIBUTE1,
555 l_cpsv_rec.ATTRIBUTE2,
556 l_cpsv_rec.ATTRIBUTE3,
557 l_cpsv_rec.ATTRIBUTE4,
558 l_cpsv_rec.ATTRIBUTE5,
559 l_cpsv_rec.ATTRIBUTE6,
560 l_cpsv_rec.ATTRIBUTE7,
561 l_cpsv_rec.ATTRIBUTE8,
562 l_cpsv_rec.ATTRIBUTE9,
563 l_cpsv_rec.ATTRIBUTE10,
564 l_cpsv_rec.ATTRIBUTE11,
565 l_cpsv_rec.ATTRIBUTE12,
566 l_cpsv_rec.ATTRIBUTE13,
567 l_cpsv_rec.ATTRIBUTE14,
568 l_cpsv_rec.ATTRIBUTE15,
569 l_cpsv_rec.CREATED_BY,
570 l_cpsv_rec.CREATION_DATE,
571 l_cpsv_rec.LAST_UPDATED_BY,
572 l_cpsv_rec.LAST_UPDATE_DATE,
573 l_cpsv_rec.LAST_UPDATE_LOGIN;
574 x_no_data_found := okc_cpsv_pk_csr%NOTFOUND;
575 CLOSE okc_cpsv_pk_csr;
576 RETURN(l_cpsv_rec);
577 END get_rec;
578
579 FUNCTION get_rec (
580 p_cpsv_rec IN cpsv_rec_type
581 ) RETURN cpsv_rec_type IS
582 l_row_notfound BOOLEAN := TRUE;
583 BEGIN
584 RETURN(get_rec(p_cpsv_rec, l_row_notfound));
585 END get_rec;
586
587 -------------------------------------------------------
588 -- FUNCTION null_out_defaults for: OKC_K_PROCESSES_V --
589 -------------------------------------------------------
590 FUNCTION null_out_defaults (
591 p_cpsv_rec IN cpsv_rec_type
592 ) RETURN cpsv_rec_type IS
593 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
594 BEGIN
595 IF (l_cpsv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
596 l_cpsv_rec.object_version_number := NULL;
597 END IF;
598 IF (l_cpsv_rec.pdf_id = OKC_API.G_MISS_NUM) THEN
599 l_cpsv_rec.pdf_id := NULL;
600 END IF;
601 IF (l_cpsv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
602 l_cpsv_rec.chr_id := NULL;
603 END IF;
604 IF (l_cpsv_rec.user_id = OKC_API.G_MISS_NUM) THEN
605 l_cpsv_rec.user_id := NULL;
606 END IF;
607 IF (l_cpsv_rec.crt_id = OKC_API.G_MISS_NUM) THEN
608 l_cpsv_rec.crt_id := NULL;
609 END IF;
610 IF (l_cpsv_rec.process_id = OKC_API.G_MISS_CHAR) THEN
611 l_cpsv_rec.process_id := NULL;
612 END IF;
613 IF (l_cpsv_rec.in_process_yn = OKC_API.G_MISS_CHAR) THEN
614 l_cpsv_rec.in_process_yn := NULL;
615 END IF;
616 IF (l_cpsv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
617 l_cpsv_rec.attribute_category := NULL;
618 END IF;
619 IF (l_cpsv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
620 l_cpsv_rec.attribute1 := NULL;
621 END IF;
622 IF (l_cpsv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
623 l_cpsv_rec.attribute2 := NULL;
624 END IF;
625 IF (l_cpsv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
626 l_cpsv_rec.attribute3 := NULL;
627 END IF;
628 IF (l_cpsv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
629 l_cpsv_rec.attribute4 := NULL;
630 END IF;
631 IF (l_cpsv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
632 l_cpsv_rec.attribute5 := NULL;
633 END IF;
634 IF (l_cpsv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
635 l_cpsv_rec.attribute6 := NULL;
636 END IF;
637 IF (l_cpsv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
638 l_cpsv_rec.attribute7 := NULL;
639 END IF;
640 IF (l_cpsv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
641 l_cpsv_rec.attribute8 := NULL;
642 END IF;
643 IF (l_cpsv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
644 l_cpsv_rec.attribute9 := NULL;
645 END IF;
646 IF (l_cpsv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
647 l_cpsv_rec.attribute10 := NULL;
648 END IF;
649 IF (l_cpsv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
650 l_cpsv_rec.attribute11 := NULL;
651 END IF;
652 IF (l_cpsv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
653 l_cpsv_rec.attribute12 := NULL;
654 END IF;
655 IF (l_cpsv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
656 l_cpsv_rec.attribute13 := NULL;
657 END IF;
658 IF (l_cpsv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
659 l_cpsv_rec.attribute14 := NULL;
660 END IF;
661 IF (l_cpsv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
662 l_cpsv_rec.attribute15 := NULL;
663 END IF;
664 IF (l_cpsv_rec.created_by = OKC_API.G_MISS_NUM) THEN
665 l_cpsv_rec.created_by := NULL;
666 END IF;
667 IF (l_cpsv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
668 l_cpsv_rec.creation_date := NULL;
669 END IF;
670 IF (l_cpsv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
671 l_cpsv_rec.last_updated_by := NULL;
672 END IF;
673 IF (l_cpsv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
674 l_cpsv_rec.last_update_date := NULL;
675 END IF;
676 IF (l_cpsv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
677 l_cpsv_rec.last_update_login := NULL;
678 END IF;
679 RETURN(l_cpsv_rec);
680 END null_out_defaults;
681 ---------------------------------------------------------------------------
682 -- PROCEDURE Validate_Attributes
683 ---------------------------------------------------------------------------
684 -----------------------------------------------
685 -- Validate_Attributes for:OKC_K_PROCESSES_V --
686 -----------------------------------------------
687 FUNCTION Validate_Attributes (
688 p_cpsv_rec IN cpsv_rec_type
689 ) RETURN VARCHAR2 IS
690 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
691 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
692 BEGIN
693 /************************ HAND-CODED *********************************/
694 validate_pdf_id
695 (x_return_status => l_return_status,
696 p_cpsv_rec => p_cpsv_rec);
697
698 -- store the highest degree of error
699 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
700 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
701 x_return_status := l_return_status;
702 End If;
703 End If;
704 --dbms_output.put_line('2 Return Status : ' || l_return_status);
705
706 validate_chr_id
707 (x_return_status => l_return_status,
708 p_cpsv_rec => p_cpsv_rec);
709
710 -- store the highest degree of error
711 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
712 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
713 x_return_status := l_return_status;
714 End If;
715 End If;
716 --dbms_output.put_line('3 Return Status : ' || l_return_status);
717
718 validate_user_id
719 (x_return_status => l_return_status,
720 p_cpsv_rec => p_cpsv_rec);
721
722 -- store the highest degree of error
723 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
724 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
725 x_return_status := l_return_status;
726 End If;
727 End If;
728 --dbms_output.put_line('3.a Return Status : ' || l_return_status);
729
730 validate_crt_id (x_return_status => l_return_status,
731 p_cpsv_rec => p_cpsv_rec);
732
733 -- store the highest degree of error
734 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
735 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
736 x_return_status := l_return_status;
737 End If;
738 End If;
739 --dbms_output.put_line('3.b Return Status : ' || l_return_status);
740
741 validate_process_id
742 (x_return_status => l_return_status,
743 p_cpsv_rec => p_cpsv_rec);
744
745 -- store the highest degree of error
746 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
747 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
748 x_return_status := l_return_status;
749 End If;
750 End If;
751 --dbms_output.put_line('3.d Return Status : ' || l_return_status);
752
753 validate_in_process_yn
754 (x_return_status => l_return_status,
755 p_cpsv_rec => p_cpsv_rec);
756
757 -- store the highest degree of error
758 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
759 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
760 x_return_status := l_return_status;
761 End If;
762 End If;
763 --dbms_output.put_line('3.e Return Status : ' || l_return_status);
764
765 RETURN(x_return_status);
766 exception
767 when OTHERS then
768 -- store SQL error message on message stack
769 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
770 p_msg_name => g_unexpected_error,
771 p_token1 => g_sqlcode_token,
772 p_token1_value => sqlcode,
773 p_token2 => g_sqlerrm_token,
774 p_token2_value => sqlerrm);
775
776 -- notify caller of an UNEXPETED error
777 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
778
779 -- return status to caller
780 RETURN(x_return_status);
781 /*********************** END HAND-CODED ********************************/
782 END Validate_Attributes;
783
784 ---------------------------------------------------------------------------
785 -- PROCEDURE Validate_Record
786 ---------------------------------------------------------------------------
787 -------------------------------------------
788 -- Validate_Record for:OKC_K_PROCESSES_V --
789 -------------------------------------------
790 FUNCTION Validate_Record (
791 p_cpsv_rec IN cpsv_rec_type
792 ) RETURN VARCHAR2 IS
793 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
794 -- l_unq_tbl OKC_UTIL.unq_tbl_type;
795
796 Cursor l_cps_csr Is
797 select count(1)
798 from okc_k_processes cps, okc_process_defs_b pdfb
799 where cps.pdf_id = pdfb.id
800 and cps.chr_id = p_cpsv_rec.chr_id
801 and pdfb.usage='APPROVE' and pdfb.pdf_type='WPS'
802 and cps.id <> p_cpsv_rec.id;
803 l_count NUMBER;
804
805 -- ------------------------------------------------------
806 -- For the combination chr_id not null and crt_id is null
807 -- The cursor includes id check filter to handle updates
808 -- for case K2 should not overwrite already existing K1
809 -- ------------------------------------------------------
810 CURSOR cur_pdf_1 IS
811 SELECT 'x'
812 FROM okc_k_processes cps
813 WHERE chr_id = p_cpsv_rec.CHR_ID
814 AND crt_id IS NULL
815 AND pdf_id = p_cpsv_rec.PDF_ID
816 AND id <> NVL(p_cpsv_rec.ID,-9999);
817
818 -- ------------------------------------------------------
819 -- For the combination chr_id null and crt_id is not null
820 -- The cursor includes id check filter to handle updates
821 -- for case K2 should not overwrite already existing K1
822 -- ------------------------------------------------------
823 CURSOR cur_pdf_2 IS
824 SELECT 'x'
825 FROM okc_k_processes cps
826 WHERE chr_id IS NULL
827 AND crt_id = p_cpsv_rec.CRT_ID
828 AND pdf_id = p_cpsv_rec.PDF_ID
829 AND id <> NVL(p_cpsv_rec.ID,-9999);
830
831 l_row_found BOOLEAN := False;
832 l_dummy VARCHAR2(1);
833 BEGIN
834 -- ------------------------------------------------------
835 -- Bug# 1636056 related changes - Shyam
836 -- OKC_UTIL.check_comp_unique call earlier was not using
837 -- the bind variables and parses everytime, replaced with
838 -- the explicit cursors above, for identical function.
839 -- ------------------------------------------------------
840 IF ( p_cpsv_rec.CHR_ID IS NOT NULL
841 AND p_cpsv_rec.CHR_ID <> OKC_API.G_MISS_NUM )
842 THEN
843 OPEN cur_pdf_1;
844 FETCH cur_pdf_1 INTO l_dummy;
845 l_row_found := cur_pdf_1%FOUND;
846 CLOSE cur_pdf_1;
847 ELSIF ( p_cpsv_rec.CRT_ID IS NOT NULL
848 AND p_cpsv_rec.CRT_ID <> OKC_API.G_MISS_NUM )
849 THEN
850 OPEN cur_pdf_2;
851 FETCH cur_pdf_2 INTO l_dummy;
852 l_row_found := cur_pdf_2%FOUND;
853 CLOSE cur_pdf_2;
854 END IF;
855
856 IF (l_row_found)
857 THEN
858 -- Display the newly defined error message
859 OKC_API.set_message(G_APP_NAME,
860 'OKC_DUPLICATE_PROCESS');
861 l_return_status := OKC_API.G_RET_STS_ERROR;
862 END IF;
863
864 -- if contract number not unique, raise exception
865 If l_return_status <> OKC_API.G_RET_STS_SUCCESS
866 Then
867 raise G_EXCEPTION_HALT_VALIDATION;
868 End If;
869
870
871 -- Do not allow mulitple APPROVAL processes for one contract
872 If (p_cpsv_rec.chr_id is not null) Then
873 open l_cps_csr;
874 fetch l_cps_csr into l_count;
875 close l_cps_csr;
876
877 If (l_count > 0) Then
878 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
879 p_msg_name => 'OKC_MULTIPLE_PROCESSES');
880
881 -- notify caller of an error
882 l_return_status := OKC_API.G_RET_STS_ERROR;
883 RAISE OKC_API.G_EXCEPTION_ERROR;
884 End If;
885 End If;
886
887 RETURN (l_return_status);
888 exception
889 when G_EXCEPTION_HALT_VALIDATION then
890 -- no processing necessary; validation can continue with next column
891 RETURN (l_return_status);
892 END Validate_Record;
893
894 ---------------------------------------------------------------------------
895 -- PROCEDURE Migrate
896 ---------------------------------------------------------------------------
897 PROCEDURE migrate (
898 p_from IN cpsv_rec_type,
899 p_to IN OUT NOCOPY cps_rec_type
900 ) IS
901 BEGIN
902 p_to.id := p_from.id;
903 p_to.pdf_id := p_from.pdf_id;
904 p_to.chr_id := p_from.chr_id;
905 p_to.user_id := p_from.user_id;
906 p_to.crt_id := p_from.crt_id;
907 p_to.object_version_number := p_from.object_version_number;
908 p_to.created_by := p_from.created_by;
909 p_to.creation_date := p_from.creation_date;
910 p_to.last_updated_by := p_from.last_updated_by;
911 p_to.last_update_date := p_from.last_update_date;
912 p_to.process_id := p_from.process_id;
913 p_to.in_process_yn := p_from.in_process_yn;
914 p_to.last_update_login := p_from.last_update_login;
915 p_to.attribute_category := p_from.attribute_category;
916 p_to.attribute1 := p_from.attribute1;
917 p_to.attribute2 := p_from.attribute2;
918 p_to.attribute3 := p_from.attribute3;
919 p_to.attribute4 := p_from.attribute4;
920 p_to.attribute5 := p_from.attribute5;
921 p_to.attribute6 := p_from.attribute6;
922 p_to.attribute7 := p_from.attribute7;
923 p_to.attribute8 := p_from.attribute8;
924 p_to.attribute9 := p_from.attribute9;
925 p_to.attribute10 := p_from.attribute10;
926 p_to.attribute11 := p_from.attribute11;
927 p_to.attribute12 := p_from.attribute12;
928 p_to.attribute13 := p_from.attribute13;
929 p_to.attribute14 := p_from.attribute14;
930 p_to.attribute15 := p_from.attribute15;
931 END migrate;
932 PROCEDURE migrate (
933 p_from IN cps_rec_type,
934 p_to IN OUT NOCOPY cpsv_rec_type
935 ) IS
936 BEGIN
937 p_to.id := p_from.id;
938 p_to.pdf_id := p_from.pdf_id;
939 p_to.chr_id := p_from.chr_id;
940 p_to.user_id := p_from.user_id;
941 p_to.crt_id := p_from.crt_id;
942 p_to.object_version_number := p_from.object_version_number;
943 p_to.created_by := p_from.created_by;
944 p_to.creation_date := p_from.creation_date;
945 p_to.last_updated_by := p_from.last_updated_by;
946 p_to.last_update_date := p_from.last_update_date;
947 p_to.process_id := p_from.process_id;
948 p_to.in_process_yn := p_from.in_process_yn;
949 p_to.last_update_login := p_from.last_update_login;
950 p_to.attribute_category := p_from.attribute_category;
951 p_to.attribute1 := p_from.attribute1;
952 p_to.attribute2 := p_from.attribute2;
953 p_to.attribute3 := p_from.attribute3;
954 p_to.attribute4 := p_from.attribute4;
955 p_to.attribute5 := p_from.attribute5;
956 p_to.attribute6 := p_from.attribute6;
957 p_to.attribute7 := p_from.attribute7;
958 p_to.attribute8 := p_from.attribute8;
959 p_to.attribute9 := p_from.attribute9;
960 p_to.attribute10 := p_from.attribute10;
961 p_to.attribute11 := p_from.attribute11;
962 p_to.attribute12 := p_from.attribute12;
963 p_to.attribute13 := p_from.attribute13;
964 p_to.attribute14 := p_from.attribute14;
965 p_to.attribute15 := p_from.attribute15;
966 END migrate;
967
968 ---------------------------------------------------------------------------
969 -- PROCEDURE validate_row
970 ---------------------------------------------------------------------------
971 ----------------------------------------
972 -- validate_row for:OKC_K_PROCESSES_V --
973 ----------------------------------------
974 PROCEDURE validate_row(
975 p_api_version IN NUMBER,
976 p_init_msg_list IN VARCHAR2 ,
977 x_return_status OUT NOCOPY VARCHAR2,
978 x_msg_count OUT NOCOPY NUMBER,
979 x_msg_data OUT NOCOPY VARCHAR2,
980 p_cpsv_rec IN cpsv_rec_type) IS
981
982 l_api_version CONSTANT NUMBER := 1;
983 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
984 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
985 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
986 l_cps_rec cps_rec_type;
987 BEGIN
988 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
989 G_PKG_NAME,
990 p_init_msg_list,
991 l_api_version,
992 p_api_version,
993 '_PVT',
994 x_return_status);
995 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
996 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
997 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
998 RAISE OKC_API.G_EXCEPTION_ERROR;
999 END IF;
1000 --- Validate all non-missing attributes (Item Level Validation)
1001 l_return_status := Validate_Attributes(l_cpsv_rec);
1002 --- If any errors happen abort API
1003 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1004 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1005 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1006 RAISE OKC_API.G_EXCEPTION_ERROR;
1007 END IF;
1008 l_return_status := Validate_Record(l_cpsv_rec);
1009 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1012 RAISE OKC_API.G_EXCEPTION_ERROR;
1013 END IF;
1014 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1015 EXCEPTION
1016 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1017 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1018 (
1019 l_api_name,
1020 G_PKG_NAME,
1021 'OKC_API.G_RET_STS_ERROR',
1022 x_msg_count,
1023 x_msg_data,
1024 '_PVT'
1025 );
1026 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1027 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1028 (
1029 l_api_name,
1030 G_PKG_NAME,
1031 'OKC_API.G_RET_STS_UNEXP_ERROR',
1032 x_msg_count,
1033 x_msg_data,
1034 '_PVT'
1035 );
1036 WHEN OTHERS THEN
1037 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1038 (
1039 l_api_name,
1040 G_PKG_NAME,
1041 'OTHERS',
1042 x_msg_count,
1043 x_msg_data,
1044 '_PVT'
1045 );
1046 END validate_row;
1047 ------------------------------------------
1048 -- PL/SQL TBL validate_row for:CPSV_TBL --
1049 ------------------------------------------
1050 PROCEDURE validate_row(
1051 p_api_version IN NUMBER,
1052 p_init_msg_list IN VARCHAR2 ,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_count OUT NOCOPY NUMBER,
1055 x_msg_data OUT NOCOPY VARCHAR2,
1056 p_cpsv_tbl IN cpsv_tbl_type) IS
1057
1058 l_api_version CONSTANT NUMBER := 1;
1059 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1060 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1061 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1062 i NUMBER := 0;
1063 BEGIN
1064 OKC_API.init_msg_list(p_init_msg_list);
1065 -- Make sure PL/SQL table has records in it before passing
1066 IF (p_cpsv_tbl.COUNT > 0) THEN
1067 i := p_cpsv_tbl.FIRST;
1068 LOOP
1069 validate_row (
1070 p_api_version => p_api_version,
1071 p_init_msg_list => OKC_API.G_FALSE,
1072 x_return_status => x_return_status,
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data,
1075 p_cpsv_rec => p_cpsv_tbl(i));
1076
1077 -- store the highest degree of error
1078 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1079 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1080 l_overall_status := x_return_status;
1081 End If;
1082 End If;
1083
1084 EXIT WHEN (i = p_cpsv_tbl.LAST);
1085 i := p_cpsv_tbl.NEXT(i);
1086 END LOOP;
1087 -- return overall status
1088 x_return_status := l_overall_status;
1089 END IF;
1090 EXCEPTION
1091 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1092 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1093 (
1094 l_api_name,
1095 G_PKG_NAME,
1096 'OKC_API.G_RET_STS_ERROR',
1097 x_msg_count,
1098 x_msg_data,
1099 '_PVT'
1100 );
1101 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1102 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1103 (
1104 l_api_name,
1105 G_PKG_NAME,
1106 'OKC_API.G_RET_STS_UNEXP_ERROR',
1107 x_msg_count,
1108 x_msg_data,
1109 '_PVT'
1110 );
1111 WHEN OTHERS THEN
1112 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1113 (
1114 l_api_name,
1115 G_PKG_NAME,
1116 'OTHERS',
1117 x_msg_count,
1118 x_msg_data,
1119 '_PVT'
1120 );
1121 END validate_row;
1122
1123 ---------------------------------------------------------------------------
1124 -- PROCEDURE insert_row
1125 ---------------------------------------------------------------------------
1126 ------------------------------------
1127 -- insert_row for:OKC_K_PROCESSES --
1128 ------------------------------------
1129 PROCEDURE insert_row(
1130 p_init_msg_list IN VARCHAR2 ,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 x_msg_count OUT NOCOPY NUMBER,
1133 x_msg_data OUT NOCOPY VARCHAR2,
1134 p_cps_rec IN cps_rec_type,
1135 x_cps_rec OUT NOCOPY cps_rec_type) IS
1136
1137 l_api_version CONSTANT NUMBER := 1;
1138 l_api_name CONSTANT VARCHAR2(30) := 'PROCESSES_insert_row';
1139 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1140 l_cps_rec cps_rec_type := p_cps_rec;
1141 l_def_cps_rec cps_rec_type;
1142 ----------------------------------------
1143 -- Set_Attributes for:OKC_K_PROCESSES --
1144 ----------------------------------------
1145 FUNCTION Set_Attributes (
1146 p_cps_rec IN cps_rec_type,
1147 x_cps_rec OUT NOCOPY cps_rec_type
1148 ) RETURN VARCHAR2 IS
1149 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1150 BEGIN
1151 x_cps_rec := p_cps_rec;
1152 RETURN(l_return_status);
1153 END Set_Attributes;
1154 BEGIN
1155 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1156 p_init_msg_list,
1157 '_PVT',
1158 x_return_status);
1159 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1160 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1161 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1162 RAISE OKC_API.G_EXCEPTION_ERROR;
1163 END IF;
1164 --- Setting item attributes
1165 l_return_status := Set_Attributes(
1166 p_cps_rec, -- IN
1167 l_cps_rec); -- OUT
1168 --- If any errors happen abort API
1169 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1170 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1172 RAISE OKC_API.G_EXCEPTION_ERROR;
1173 END IF;
1174 INSERT INTO OKC_K_PROCESSES(
1175 id,
1176 pdf_id,
1177 chr_id,
1178 user_id,
1179 crt_id,
1180 object_version_number,
1181 created_by,
1182 creation_date,
1183 last_updated_by,
1184 last_update_date,
1185 process_id,
1186 in_process_yn,
1187 last_update_login,
1188 attribute_category,
1189 attribute1,
1190 attribute2,
1191 attribute3,
1192 attribute4,
1193 attribute5,
1194 attribute6,
1195 attribute7,
1196 attribute8,
1197 attribute9,
1198 attribute10,
1199 attribute11,
1200 attribute12,
1201 attribute13,
1202 attribute14,
1203 attribute15)
1204 VALUES (
1205 l_cps_rec.id,
1206 l_cps_rec.pdf_id,
1207 l_cps_rec.chr_id,
1208 l_cps_rec.user_id,
1209 l_cps_rec.crt_id,
1210 l_cps_rec.object_version_number,
1211 l_cps_rec.created_by,
1212 l_cps_rec.creation_date,
1213 l_cps_rec.last_updated_by,
1214 l_cps_rec.last_update_date,
1215 l_cps_rec.process_id,
1216 l_cps_rec.in_process_yn,
1217 l_cps_rec.last_update_login,
1218 l_cps_rec.attribute_category,
1219 l_cps_rec.attribute1,
1220 l_cps_rec.attribute2,
1221 l_cps_rec.attribute3,
1222 l_cps_rec.attribute4,
1223 l_cps_rec.attribute5,
1224 l_cps_rec.attribute6,
1225 l_cps_rec.attribute7,
1226 l_cps_rec.attribute8,
1227 l_cps_rec.attribute9,
1228 l_cps_rec.attribute10,
1229 l_cps_rec.attribute11,
1230 l_cps_rec.attribute12,
1231 l_cps_rec.attribute13,
1232 l_cps_rec.attribute14,
1233 l_cps_rec.attribute15);
1234 -- Set OUT values
1235 x_cps_rec := l_cps_rec;
1236 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1237 EXCEPTION
1238 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1239 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1240 (
1241 l_api_name,
1242 G_PKG_NAME,
1243 'OKC_API.G_RET_STS_ERROR',
1244 x_msg_count,
1245 x_msg_data,
1246 '_PVT'
1247 );
1248 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1249 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1250 (
1251 l_api_name,
1252 G_PKG_NAME,
1253 'OKC_API.G_RET_STS_UNEXP_ERROR',
1254 x_msg_count,
1255 x_msg_data,
1256 '_PVT'
1257 );
1258 WHEN OTHERS THEN
1259 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1260 (
1261 l_api_name,
1262 G_PKG_NAME,
1263 'OTHERS',
1264 x_msg_count,
1265 x_msg_data,
1266 '_PVT'
1267 );
1268 END insert_row;
1269 --------------------------------------
1270 -- insert_row for:OKC_K_PROCESSES_V --
1271 --------------------------------------
1272 PROCEDURE insert_row(
1273 p_api_version IN NUMBER,
1274 p_init_msg_list IN VARCHAR2 ,
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2,
1278 p_cpsv_rec IN cpsv_rec_type,
1279 x_cpsv_rec OUT NOCOPY cpsv_rec_type) IS
1280
1281 l_api_version CONSTANT NUMBER := 1;
1282 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1283 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1284 l_cpsv_rec cpsv_rec_type;
1285 l_def_cpsv_rec cpsv_rec_type;
1286 l_cps_rec cps_rec_type;
1287 lx_cps_rec cps_rec_type;
1288
1289 -------------------------------
1290 -- FUNCTION fill_who_columns --
1291 -------------------------------
1292 FUNCTION fill_who_columns (
1293 p_cpsv_rec IN cpsv_rec_type
1294 ) RETURN cpsv_rec_type IS
1295 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
1296 BEGIN
1297 l_cpsv_rec.CREATION_DATE := SYSDATE;
1298 l_cpsv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1299 l_cpsv_rec.LAST_UPDATE_DATE := l_cpsv_rec.CREATION_DATE;
1300 l_cpsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1301 l_cpsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1302 RETURN(l_cpsv_rec);
1303 END fill_who_columns;
1304 ------------------------------------------
1305 -- Set_Attributes for:OKC_K_PROCESSES_V --
1306 ------------------------------------------
1307 FUNCTION Set_Attributes (
1308 p_cpsv_rec IN cpsv_rec_type,
1309 x_cpsv_rec OUT NOCOPY cpsv_rec_type
1310 ) RETURN VARCHAR2 IS
1311 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1312 BEGIN
1313 x_cpsv_rec := p_cpsv_rec;
1314 x_cpsv_rec.OBJECT_VERSION_NUMBER := 1;
1315 /************************ HAND-CODED *********************************/
1316 x_cpsv_rec.IN_PROCESS_YN := UPPER(x_cpsv_rec.IN_PROCESS_YN);
1317 /*********************** END HAND-CODED ******************************/
1318 RETURN(l_return_status);
1319 END Set_Attributes;
1320 BEGIN
1321 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1322 G_PKG_NAME,
1323 p_init_msg_list,
1324 l_api_version,
1325 p_api_version,
1326 '_PVT',
1327 x_return_status);
1328 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1329 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1330 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1331 RAISE OKC_API.G_EXCEPTION_ERROR;
1332 END IF;
1333
1334 l_cpsv_rec := null_out_defaults(p_cpsv_rec);
1335 -- Set primary key value
1336 l_cpsv_rec.ID := get_seq_id;
1337 --- Setting item attributes
1338 l_return_status := Set_Attributes(
1339 l_cpsv_rec, -- IN
1340 l_def_cpsv_rec); -- OUT
1341 --- If any errors happen abort API
1342 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1343 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1344 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1345 RAISE OKC_API.G_EXCEPTION_ERROR;
1346 END IF;
1347 l_def_cpsv_rec := fill_who_columns(l_def_cpsv_rec);
1348 --- Validate all non-missing attributes (Item Level Validation)
1349 l_return_status := Validate_Attributes(l_def_cpsv_rec);
1350 --- If any errors happen abort API
1351 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1352 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1353 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1354 RAISE OKC_API.G_EXCEPTION_ERROR;
1355 END IF;
1356 l_return_status := Validate_Record(l_def_cpsv_rec);
1357 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1358 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1359 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1360 RAISE OKC_API.G_EXCEPTION_ERROR;
1361 END IF;
1362 --------------------------------------
1363 -- Move VIEW record to "Child" records
1364 --------------------------------------
1365 migrate(l_def_cpsv_rec, l_cps_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_cps_rec,
1375 lx_cps_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_cps_rec, l_def_cpsv_rec);
1383 -- Set OUT values
1384 x_cpsv_rec := l_def_cpsv_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:CPSV_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_cpsv_tbl IN cpsv_tbl_type,
1428 x_cpsv_tbl OUT NOCOPY cpsv_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 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1434 i NUMBER := 0;
1435 BEGIN
1436 OKC_API.init_msg_list(p_init_msg_list);
1437 -- Make sure PL/SQL table has records in it before passing
1438 IF (p_cpsv_tbl.COUNT > 0) THEN
1439 i := p_cpsv_tbl.FIRST;
1440 LOOP
1441 insert_row (
1442 p_api_version => p_api_version,
1443 p_init_msg_list => OKC_API.G_FALSE,
1444 x_return_status => x_return_status,
1445 x_msg_count => x_msg_count,
1446 x_msg_data => x_msg_data,
1447 p_cpsv_rec => p_cpsv_tbl(i),
1448 x_cpsv_rec => x_cpsv_tbl(i));
1449
1450 -- store the highest degree of error
1451 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1452 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1453 l_overall_status := x_return_status;
1454 End If;
1455 End If;
1456
1457 EXIT WHEN (i = p_cpsv_tbl.LAST);
1458 i := p_cpsv_tbl.NEXT(i);
1459 END LOOP;
1460 -- return overall status
1461 x_return_status := l_overall_status;
1462 END IF;
1463 EXCEPTION
1464 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1465 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1466 (
1467 l_api_name,
1468 G_PKG_NAME,
1469 'OKC_API.G_RET_STS_ERROR',
1470 x_msg_count,
1471 x_msg_data,
1472 '_PVT'
1473 );
1474 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1475 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1476 (
1477 l_api_name,
1478 G_PKG_NAME,
1479 'OKC_API.G_RET_STS_UNEXP_ERROR',
1480 x_msg_count,
1481 x_msg_data,
1482 '_PVT'
1483 );
1484 WHEN OTHERS THEN
1485 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'OTHERS',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 END insert_row;
1495
1496 ---------------------------------------------------------------------------
1497 -- PROCEDURE lock_row
1498 ---------------------------------------------------------------------------
1499 ----------------------------------
1500 -- lock_row for:OKC_K_PROCESSES --
1501 ----------------------------------
1502 PROCEDURE lock_row(
1503 p_init_msg_list IN VARCHAR2 ,
1504 x_return_status OUT NOCOPY VARCHAR2,
1505 x_msg_count OUT NOCOPY NUMBER,
1506 x_msg_data OUT NOCOPY VARCHAR2,
1507 p_cps_rec IN cps_rec_type) IS
1508
1509 E_Resource_Busy EXCEPTION;
1510 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1511 CURSOR lock_csr (p_cps_rec IN cps_rec_type) IS
1512 SELECT OBJECT_VERSION_NUMBER
1513 FROM OKC_K_PROCESSES
1514 WHERE ID = p_cps_rec.id
1515 AND OBJECT_VERSION_NUMBER in (p_cps_rec.object_version_number,
1516 OKC_API.G_MISS_NUM)
1517 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1518
1519 CURSOR lchk_csr (p_cps_rec IN cps_rec_type) IS
1520 SELECT OBJECT_VERSION_NUMBER
1521 FROM OKC_K_PROCESSES
1522 WHERE ID = p_cps_rec.id;
1523 l_api_version CONSTANT NUMBER := 1;
1524 l_api_name CONSTANT VARCHAR2(30) := 'PROCESSES_lock_row';
1525 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1526 l_object_version_number OKC_K_PROCESSES.OBJECT_VERSION_NUMBER%TYPE;
1527 lc_object_version_number OKC_K_PROCESSES.OBJECT_VERSION_NUMBER%TYPE;
1528 l_row_notfound BOOLEAN := FALSE;
1529 lc_row_notfound BOOLEAN := FALSE;
1530 BEGIN
1531 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1532 p_init_msg_list,
1533 '_PVT',
1534 x_return_status);
1535 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1536 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1537 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1538 RAISE OKC_API.G_EXCEPTION_ERROR;
1539 END IF;
1540 BEGIN
1541 OPEN lock_csr(p_cps_rec);
1542 FETCH lock_csr INTO l_object_version_number;
1543 l_row_notfound := lock_csr%NOTFOUND;
1544 CLOSE lock_csr;
1545 EXCEPTION
1546 WHEN E_Resource_Busy THEN
1547 IF (lock_csr%ISOPEN) THEN
1548 CLOSE lock_csr;
1549 END IF;
1550 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1551 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1552 END;
1553
1554 IF ( l_row_notfound ) THEN
1555 OPEN lchk_csr(p_cps_rec);
1556 FETCH lchk_csr INTO lc_object_version_number;
1557 lc_row_notfound := lchk_csr%NOTFOUND;
1558 CLOSE lchk_csr;
1559 END IF;
1560 IF (lc_row_notfound) THEN
1561 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1562 RAISE OKC_API.G_EXCEPTION_ERROR;
1563 ELSIF lc_object_version_number > p_cps_rec.object_version_number THEN
1564 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1565 RAISE OKC_API.G_EXCEPTION_ERROR;
1566 ELSIF lc_object_version_number <> p_cps_rec.object_version_number THEN
1567 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1568 RAISE OKC_API.G_EXCEPTION_ERROR;
1569 ELSIF lc_object_version_number = -1 THEN
1570 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1571 RAISE OKC_API.G_EXCEPTION_ERROR;
1572 END IF;
1573 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1574 EXCEPTION
1575 WHEN OKC_API.G_EXCEPTION_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_ERROR',
1581 x_msg_count,
1582 x_msg_data,
1583 '_PVT'
1584 );
1585 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1586 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1587 (
1588 l_api_name,
1589 G_PKG_NAME,
1590 'OKC_API.G_RET_STS_UNEXP_ERROR',
1591 x_msg_count,
1592 x_msg_data,
1593 '_PVT'
1594 );
1595 WHEN OTHERS THEN
1596 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1597 (
1598 l_api_name,
1599 G_PKG_NAME,
1600 'OTHERS',
1601 x_msg_count,
1602 x_msg_data,
1603 '_PVT'
1604 );
1605 END lock_row;
1606 ------------------------------------
1607 -- lock_row for:OKC_K_PROCESSES_V --
1608 ------------------------------------
1609 PROCEDURE lock_row(
1610 p_api_version IN NUMBER,
1611 p_init_msg_list IN VARCHAR2 ,
1612 x_return_status OUT NOCOPY VARCHAR2,
1613 x_msg_count OUT NOCOPY NUMBER,
1614 x_msg_data OUT NOCOPY VARCHAR2,
1615 p_cpsv_rec IN cpsv_rec_type) IS
1616
1617 l_api_version CONSTANT NUMBER := 1;
1618 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1619 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1620 l_cps_rec cps_rec_type;
1621 BEGIN
1622 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1623 G_PKG_NAME,
1624 p_init_msg_list,
1625 l_api_version,
1626 p_api_version,
1627 '_PVT',
1628 x_return_status);
1629 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1630 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1631 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1632 RAISE OKC_API.G_EXCEPTION_ERROR;
1633 END IF;
1634 --------------------------------------
1635 -- Move VIEW record to "Child" records
1636 --------------------------------------
1637 migrate(p_cpsv_rec, l_cps_rec);
1638 --------------------------------------------
1639 -- Call the LOCK_ROW for each child record
1640 --------------------------------------------
1641 lock_row(
1642 p_init_msg_list,
1643 x_return_status,
1644 x_msg_count,
1645 x_msg_data,
1646 l_cps_rec
1647 );
1648 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1649 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1650 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1651 RAISE OKC_API.G_EXCEPTION_ERROR;
1652 END IF;
1653 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1654 EXCEPTION
1655 WHEN OKC_API.G_EXCEPTION_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_ERROR',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1666 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1667 (
1668 l_api_name,
1669 G_PKG_NAME,
1670 'OKC_API.G_RET_STS_UNEXP_ERROR',
1671 x_msg_count,
1672 x_msg_data,
1673 '_PVT'
1674 );
1675 WHEN OTHERS THEN
1676 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1677 (
1678 l_api_name,
1679 G_PKG_NAME,
1680 'OTHERS',
1681 x_msg_count,
1682 x_msg_data,
1683 '_PVT'
1684 );
1685 END lock_row;
1686 --------------------------------------
1687 -- PL/SQL TBL lock_row for:CPSV_TBL --
1688 --------------------------------------
1689 PROCEDURE lock_row(
1690 p_api_version IN NUMBER,
1691 p_init_msg_list IN VARCHAR2 ,
1692 x_return_status OUT NOCOPY VARCHAR2,
1693 x_msg_count OUT NOCOPY NUMBER,
1694 x_msg_data OUT NOCOPY VARCHAR2,
1695 p_cpsv_tbl IN cpsv_tbl_type) IS
1696
1697 l_api_version CONSTANT NUMBER := 1;
1698 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1699 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1700 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1701 i NUMBER := 0;
1702 BEGIN
1703 OKC_API.init_msg_list(p_init_msg_list);
1704 -- Make sure PL/SQL table has records in it before passing
1705 IF (p_cpsv_tbl.COUNT > 0) THEN
1706 i := p_cpsv_tbl.FIRST;
1707 LOOP
1708 lock_row (
1709 p_api_version => p_api_version,
1710 p_init_msg_list => OKC_API.G_FALSE,
1711 x_return_status => x_return_status,
1712 x_msg_count => x_msg_count,
1713 x_msg_data => x_msg_data,
1714 p_cpsv_rec => p_cpsv_tbl(i));
1715
1716 -- store the highest degree of error
1717 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1718 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1719 l_overall_status := x_return_status;
1720 End If;
1721 End If;
1722
1723 EXIT WHEN (i = p_cpsv_tbl.LAST);
1724 i := p_cpsv_tbl.NEXT(i);
1725 END LOOP;
1726 -- return overall status
1727 x_return_status := l_overall_status;
1728 END IF;
1729 EXCEPTION
1730 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1731 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1732 (
1733 l_api_name,
1734 G_PKG_NAME,
1735 'OKC_API.G_RET_STS_ERROR',
1736 x_msg_count,
1737 x_msg_data,
1738 '_PVT'
1739 );
1740 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1741 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1742 (
1743 l_api_name,
1744 G_PKG_NAME,
1745 'OKC_API.G_RET_STS_UNEXP_ERROR',
1746 x_msg_count,
1747 x_msg_data,
1748 '_PVT'
1749 );
1750 WHEN OTHERS THEN
1751 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1752 (
1753 l_api_name,
1754 G_PKG_NAME,
1755 'OTHERS',
1756 x_msg_count,
1757 x_msg_data,
1758 '_PVT'
1759 );
1760 END lock_row;
1761
1762 ---------------------------------------------------------------------------
1763 -- PROCEDURE update_row
1764 ---------------------------------------------------------------------------
1765 ------------------------------------
1766 -- update_row for:OKC_K_PROCESSES --
1767 ------------------------------------
1768 PROCEDURE update_row(
1769 p_init_msg_list IN VARCHAR2 ,
1770 x_return_status OUT NOCOPY VARCHAR2,
1771 x_msg_count OUT NOCOPY NUMBER,
1772 x_msg_data OUT NOCOPY VARCHAR2,
1773 p_cps_rec IN cps_rec_type,
1774 x_cps_rec OUT NOCOPY cps_rec_type) IS
1775
1776 l_api_version CONSTANT NUMBER := 1;
1777 l_api_name CONSTANT VARCHAR2(30) := 'PROCESSES_update_row';
1778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1779 l_cps_rec cps_rec_type := p_cps_rec;
1780 l_def_cps_rec cps_rec_type;
1781 l_row_notfound BOOLEAN := TRUE;
1782 ----------------------------------
1783 -- FUNCTION populate_new_record --
1784 ----------------------------------
1785 FUNCTION populate_new_record (
1786 p_cps_rec IN cps_rec_type,
1787 x_cps_rec OUT NOCOPY cps_rec_type
1788 ) RETURN VARCHAR2 IS
1789 l_cps_rec cps_rec_type;
1790 l_row_notfound BOOLEAN := TRUE;
1791 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1792 BEGIN
1793 x_cps_rec := p_cps_rec;
1794 -- Get current database values
1795 l_cps_rec := get_rec(p_cps_rec, l_row_notfound);
1796 IF (l_row_notfound) THEN
1797 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1798 END IF;
1799 IF (x_cps_rec.id = OKC_API.G_MISS_NUM)
1800 THEN
1801 x_cps_rec.id := l_cps_rec.id;
1802 END IF;
1803 IF (x_cps_rec.pdf_id = OKC_API.G_MISS_NUM)
1804 THEN
1805 x_cps_rec.pdf_id := l_cps_rec.pdf_id;
1806 END IF;
1807 IF (x_cps_rec.chr_id = OKC_API.G_MISS_NUM)
1808 THEN
1809 x_cps_rec.chr_id := l_cps_rec.chr_id;
1810 END IF;
1811 IF (x_cps_rec.user_id = OKC_API.G_MISS_NUM)
1812 THEN
1813 x_cps_rec.user_id := l_cps_rec.user_id;
1814 END IF;
1815 IF (x_cps_rec.crt_id = OKC_API.G_MISS_NUM)
1816 THEN
1817 x_cps_rec.crt_id := l_cps_rec.crt_id;
1818 END IF;
1819 IF (x_cps_rec.object_version_number = OKC_API.G_MISS_NUM)
1820 THEN
1821 x_cps_rec.object_version_number := l_cps_rec.object_version_number;
1822 END IF;
1823 IF (x_cps_rec.created_by = OKC_API.G_MISS_NUM)
1824 THEN
1825 x_cps_rec.created_by := l_cps_rec.created_by;
1826 END IF;
1827 IF (x_cps_rec.creation_date = OKC_API.G_MISS_DATE)
1828 THEN
1829 x_cps_rec.creation_date := l_cps_rec.creation_date;
1830 END IF;
1831 IF (x_cps_rec.last_updated_by = OKC_API.G_MISS_NUM)
1832 THEN
1833 x_cps_rec.last_updated_by := l_cps_rec.last_updated_by;
1834 END IF;
1835 IF (x_cps_rec.last_update_date = OKC_API.G_MISS_DATE)
1836 THEN
1837 x_cps_rec.last_update_date := l_cps_rec.last_update_date;
1838 END IF;
1839 IF (x_cps_rec.process_id = OKC_API.G_MISS_CHAR)
1840 THEN
1841 x_cps_rec.process_id := l_cps_rec.process_id;
1842 END IF;
1843 IF (x_cps_rec.in_process_yn = OKC_API.G_MISS_CHAR)
1844 THEN
1845 x_cps_rec.in_process_yn := l_cps_rec.in_process_yn;
1846 END IF;
1847 IF (x_cps_rec.last_update_login = OKC_API.G_MISS_NUM)
1848 THEN
1849 x_cps_rec.last_update_login := l_cps_rec.last_update_login;
1850 END IF;
1851 IF (x_cps_rec.attribute_category = OKC_API.G_MISS_CHAR)
1852 THEN
1853 x_cps_rec.attribute_category := l_cps_rec.attribute_category;
1854 END IF;
1855 IF (x_cps_rec.attribute1 = OKC_API.G_MISS_CHAR)
1856 THEN
1857 x_cps_rec.attribute1 := l_cps_rec.attribute1;
1858 END IF;
1859 IF (x_cps_rec.attribute2 = OKC_API.G_MISS_CHAR)
1860 THEN
1861 x_cps_rec.attribute2 := l_cps_rec.attribute2;
1862 END IF;
1863 IF (x_cps_rec.attribute3 = OKC_API.G_MISS_CHAR)
1864 THEN
1865 x_cps_rec.attribute3 := l_cps_rec.attribute3;
1866 END IF;
1867 IF (x_cps_rec.attribute4 = OKC_API.G_MISS_CHAR)
1868 THEN
1869 x_cps_rec.attribute4 := l_cps_rec.attribute4;
1870 END IF;
1871 IF (x_cps_rec.attribute5 = OKC_API.G_MISS_CHAR)
1872 THEN
1873 x_cps_rec.attribute5 := l_cps_rec.attribute5;
1874 END IF;
1875 IF (x_cps_rec.attribute6 = OKC_API.G_MISS_CHAR)
1876 THEN
1877 x_cps_rec.attribute6 := l_cps_rec.attribute6;
1878 END IF;
1879 IF (x_cps_rec.attribute7 = OKC_API.G_MISS_CHAR)
1880 THEN
1881 x_cps_rec.attribute7 := l_cps_rec.attribute7;
1882 END IF;
1883 IF (x_cps_rec.attribute8 = OKC_API.G_MISS_CHAR)
1884 THEN
1885 x_cps_rec.attribute8 := l_cps_rec.attribute8;
1886 END IF;
1887 IF (x_cps_rec.attribute9 = OKC_API.G_MISS_CHAR)
1888 THEN
1889 x_cps_rec.attribute9 := l_cps_rec.attribute9;
1890 END IF;
1891 IF (x_cps_rec.attribute10 = OKC_API.G_MISS_CHAR)
1892 THEN
1893 x_cps_rec.attribute10 := l_cps_rec.attribute10;
1894 END IF;
1895 IF (x_cps_rec.attribute11 = OKC_API.G_MISS_CHAR)
1896 THEN
1897 x_cps_rec.attribute11 := l_cps_rec.attribute11;
1898 END IF;
1899 IF (x_cps_rec.attribute12 = OKC_API.G_MISS_CHAR)
1900 THEN
1901 x_cps_rec.attribute12 := l_cps_rec.attribute12;
1902 END IF;
1903 IF (x_cps_rec.attribute13 = OKC_API.G_MISS_CHAR)
1904 THEN
1905 x_cps_rec.attribute13 := l_cps_rec.attribute13;
1906 END IF;
1907 IF (x_cps_rec.attribute14 = OKC_API.G_MISS_CHAR)
1908 THEN
1909 x_cps_rec.attribute14 := l_cps_rec.attribute14;
1910 END IF;
1911 IF (x_cps_rec.attribute15 = OKC_API.G_MISS_CHAR)
1912 THEN
1913 x_cps_rec.attribute15 := l_cps_rec.attribute15;
1914 END IF;
1915 RETURN(l_return_status);
1916 END populate_new_record;
1917 ----------------------------------------
1918 -- Set_Attributes for:OKC_K_PROCESSES --
1919 ----------------------------------------
1920 FUNCTION Set_Attributes (
1921 p_cps_rec IN cps_rec_type,
1922 x_cps_rec OUT NOCOPY cps_rec_type
1923 ) RETURN VARCHAR2 IS
1924 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1925 BEGIN
1926 x_cps_rec := p_cps_rec;
1927 RETURN(l_return_status);
1928 END Set_Attributes;
1929 BEGIN
1930 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1931 p_init_msg_list,
1932 '_PVT',
1933 x_return_status);
1934 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1935 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1936 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1937 RAISE OKC_API.G_EXCEPTION_ERROR;
1938 END IF;
1939 --- Setting item attributes
1940 l_return_status := Set_Attributes(
1941 p_cps_rec, -- IN
1942 l_cps_rec); -- OUT
1943 --- If any errors happen abort API
1944 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1945 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1946 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1947 RAISE OKC_API.G_EXCEPTION_ERROR;
1948 END IF;
1949 l_return_status := populate_new_record(l_cps_rec, l_def_cps_rec);
1950 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1951 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1952 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1953 RAISE OKC_API.G_EXCEPTION_ERROR;
1954 END IF;
1955 UPDATE OKC_K_PROCESSES
1956 SET PDF_ID = l_def_cps_rec.pdf_id,
1957 CHR_ID = l_def_cps_rec.chr_id,
1958 USER_ID = l_def_cps_rec.user_id,
1959 CRT_ID = l_def_cps_rec.crt_id,
1960 OBJECT_VERSION_NUMBER = l_def_cps_rec.object_version_number,
1961 CREATED_BY = l_def_cps_rec.created_by,
1962 CREATION_DATE = l_def_cps_rec.creation_date,
1963 LAST_UPDATED_BY = l_def_cps_rec.last_updated_by,
1964 LAST_UPDATE_DATE = l_def_cps_rec.last_update_date,
1965 PROCESS_ID = l_def_cps_rec.process_id,
1966 IN_PROCESS_YN = l_def_cps_rec.in_process_yn,
1967 LAST_UPDATE_LOGIN = l_def_cps_rec.last_update_login,
1968 ATTRIBUTE_CATEGORY = l_def_cps_rec.attribute_category,
1969 ATTRIBUTE1 = l_def_cps_rec.attribute1,
1970 ATTRIBUTE2 = l_def_cps_rec.attribute2,
1971 ATTRIBUTE3 = l_def_cps_rec.attribute3,
1972 ATTRIBUTE4 = l_def_cps_rec.attribute4,
1973 ATTRIBUTE5 = l_def_cps_rec.attribute5,
1974 ATTRIBUTE6 = l_def_cps_rec.attribute6,
1975 ATTRIBUTE7 = l_def_cps_rec.attribute7,
1976 ATTRIBUTE8 = l_def_cps_rec.attribute8,
1977 ATTRIBUTE9 = l_def_cps_rec.attribute9,
1978 ATTRIBUTE10 = l_def_cps_rec.attribute10,
1979 ATTRIBUTE11 = l_def_cps_rec.attribute11,
1980 ATTRIBUTE12 = l_def_cps_rec.attribute12,
1981 ATTRIBUTE13 = l_def_cps_rec.attribute13,
1982 ATTRIBUTE14 = l_def_cps_rec.attribute14,
1983 ATTRIBUTE15 = l_def_cps_rec.attribute15
1984 WHERE ID = l_def_cps_rec.id;
1985
1986 x_cps_rec := l_def_cps_rec;
1987 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1988 EXCEPTION
1989 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1990 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1991 (
1992 l_api_name,
1993 G_PKG_NAME,
1994 'OKC_API.G_RET_STS_ERROR',
1995 x_msg_count,
1996 x_msg_data,
1997 '_PVT'
1998 );
1999 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2000 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2001 (
2002 l_api_name,
2003 G_PKG_NAME,
2004 'OKC_API.G_RET_STS_UNEXP_ERROR',
2005 x_msg_count,
2006 x_msg_data,
2007 '_PVT'
2008 );
2009 WHEN OTHERS THEN
2010 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2011 (
2012 l_api_name,
2013 G_PKG_NAME,
2014 'OTHERS',
2015 x_msg_count,
2016 x_msg_data,
2017 '_PVT'
2018 );
2019 END update_row;
2020 --------------------------------------
2021 -- update_row for:OKC_K_PROCESSES_V --
2022 --------------------------------------
2023 PROCEDURE update_row(
2024 p_api_version IN NUMBER,
2025 p_init_msg_list IN VARCHAR2 ,
2026 x_return_status OUT NOCOPY VARCHAR2,
2027 x_msg_count OUT NOCOPY NUMBER,
2028 x_msg_data OUT NOCOPY VARCHAR2,
2029 p_cpsv_rec IN cpsv_rec_type,
2030 x_cpsv_rec OUT NOCOPY cpsv_rec_type) IS
2031
2032 l_api_version CONSTANT NUMBER := 1;
2033 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2034 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2035 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
2036 l_def_cpsv_rec cpsv_rec_type;
2037 l_cps_rec cps_rec_type;
2038 lx_cps_rec cps_rec_type;
2039 -------------------------------
2040 -- FUNCTION fill_who_columns --
2041 -------------------------------
2042 FUNCTION fill_who_columns (
2043 p_cpsv_rec IN cpsv_rec_type
2044 ) RETURN cpsv_rec_type IS
2045 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
2046 BEGIN
2047 l_cpsv_rec.LAST_UPDATE_DATE := SYSDATE;
2048 l_cpsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2049 l_cpsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2050 RETURN(l_cpsv_rec);
2051 END fill_who_columns;
2052 ----------------------------------
2053 -- FUNCTION populate_new_record --
2054 ----------------------------------
2055 FUNCTION populate_new_record (
2056 p_cpsv_rec IN cpsv_rec_type,
2057 x_cpsv_rec OUT NOCOPY cpsv_rec_type
2058 ) RETURN VARCHAR2 IS
2059 l_cpsv_rec cpsv_rec_type;
2060 l_row_notfound BOOLEAN := TRUE;
2061 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2062 BEGIN
2063 x_cpsv_rec := p_cpsv_rec;
2064 -- Get current database values
2065 l_cpsv_rec := get_rec(p_cpsv_rec, l_row_notfound);
2066 IF (l_row_notfound) THEN
2067 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2068 END IF;
2069 IF (x_cpsv_rec.id = OKC_API.G_MISS_NUM)
2070 THEN
2071 x_cpsv_rec.id := l_cpsv_rec.id;
2072 END IF;
2073 IF (x_cpsv_rec.object_version_number = OKC_API.G_MISS_NUM)
2074 THEN
2075 x_cpsv_rec.object_version_number := l_cpsv_rec.object_version_number;
2076 END IF;
2077 IF (x_cpsv_rec.pdf_id = OKC_API.G_MISS_NUM)
2078 THEN
2079 x_cpsv_rec.pdf_id := l_cpsv_rec.pdf_id;
2080 END IF;
2081 IF (x_cpsv_rec.chr_id = OKC_API.G_MISS_NUM)
2082 THEN
2083 x_cpsv_rec.chr_id := l_cpsv_rec.chr_id;
2084 END IF;
2085 IF (x_cpsv_rec.user_id = OKC_API.G_MISS_NUM)
2086 THEN
2087 x_cpsv_rec.user_id := l_cpsv_rec.user_id;
2088 END IF;
2089 IF (x_cpsv_rec.crt_id = OKC_API.G_MISS_NUM)
2090 THEN
2091 x_cpsv_rec.crt_id := l_cpsv_rec.crt_id;
2092 END IF;
2093 IF (x_cpsv_rec.process_id = OKC_API.G_MISS_CHAR)
2094 THEN
2095 x_cpsv_rec.process_id := l_cpsv_rec.process_id;
2096 END IF;
2097 IF (x_cpsv_rec.in_process_yn = OKC_API.G_MISS_CHAR)
2098 THEN
2099 x_cpsv_rec.in_process_yn := l_cpsv_rec.in_process_yn;
2100 END IF;
2101 IF (x_cpsv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2102 THEN
2103 x_cpsv_rec.attribute_category := l_cpsv_rec.attribute_category;
2104 END IF;
2105 IF (x_cpsv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2106 THEN
2107 x_cpsv_rec.attribute1 := l_cpsv_rec.attribute1;
2108 END IF;
2109 IF (x_cpsv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2110 THEN
2111 x_cpsv_rec.attribute2 := l_cpsv_rec.attribute2;
2112 END IF;
2113 IF (x_cpsv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2114 THEN
2115 x_cpsv_rec.attribute3 := l_cpsv_rec.attribute3;
2116 END IF;
2117 IF (x_cpsv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2118 THEN
2119 x_cpsv_rec.attribute4 := l_cpsv_rec.attribute4;
2120 END IF;
2121 IF (x_cpsv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2122 THEN
2123 x_cpsv_rec.attribute5 := l_cpsv_rec.attribute5;
2124 END IF;
2125 IF (x_cpsv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2126 THEN
2127 x_cpsv_rec.attribute6 := l_cpsv_rec.attribute6;
2128 END IF;
2129 IF (x_cpsv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2130 THEN
2131 x_cpsv_rec.attribute7 := l_cpsv_rec.attribute7;
2132 END IF;
2133 IF (x_cpsv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2134 THEN
2135 x_cpsv_rec.attribute8 := l_cpsv_rec.attribute8;
2136 END IF;
2137 IF (x_cpsv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2138 THEN
2139 x_cpsv_rec.attribute9 := l_cpsv_rec.attribute9;
2140 END IF;
2141 IF (x_cpsv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2142 THEN
2143 x_cpsv_rec.attribute10 := l_cpsv_rec.attribute10;
2144 END IF;
2145 IF (x_cpsv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2146 THEN
2147 x_cpsv_rec.attribute11 := l_cpsv_rec.attribute11;
2148 END IF;
2149 IF (x_cpsv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2150 THEN
2151 x_cpsv_rec.attribute12 := l_cpsv_rec.attribute12;
2152 END IF;
2153 IF (x_cpsv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2154 THEN
2155 x_cpsv_rec.attribute13 := l_cpsv_rec.attribute13;
2156 END IF;
2157 IF (x_cpsv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2158 THEN
2159 x_cpsv_rec.attribute14 := l_cpsv_rec.attribute14;
2160 END IF;
2161 IF (x_cpsv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2162 THEN
2163 x_cpsv_rec.attribute15 := l_cpsv_rec.attribute15;
2164 END IF;
2165 IF (x_cpsv_rec.created_by = OKC_API.G_MISS_NUM)
2166 THEN
2167 x_cpsv_rec.created_by := l_cpsv_rec.created_by;
2168 END IF;
2169 IF (x_cpsv_rec.creation_date = OKC_API.G_MISS_DATE)
2170 THEN
2171 x_cpsv_rec.creation_date := l_cpsv_rec.creation_date;
2172 END IF;
2173 IF (x_cpsv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2174 THEN
2175 x_cpsv_rec.last_updated_by := l_cpsv_rec.last_updated_by;
2176 END IF;
2177 IF (x_cpsv_rec.last_update_date = OKC_API.G_MISS_DATE)
2178 THEN
2179 x_cpsv_rec.last_update_date := l_cpsv_rec.last_update_date;
2180 END IF;
2181 IF (x_cpsv_rec.last_update_login = OKC_API.G_MISS_NUM)
2182 THEN
2183 x_cpsv_rec.last_update_login := l_cpsv_rec.last_update_login;
2184 END IF;
2185 RETURN(l_return_status);
2186 END populate_new_record;
2187 ------------------------------------------
2188 -- Set_Attributes for:OKC_K_PROCESSES_V --
2189 ------------------------------------------
2190 FUNCTION Set_Attributes (
2191 p_cpsv_rec IN cpsv_rec_type,
2192 x_cpsv_rec OUT NOCOPY cpsv_rec_type
2193 ) RETURN VARCHAR2 IS
2194 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2195 BEGIN
2196 x_cpsv_rec := p_cpsv_rec;
2197 x_cpsv_rec.OBJECT_VERSION_NUMBER := NVL(x_cpsv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2198 /************************ HAND-CODED *********************************/
2199 x_cpsv_rec.IN_PROCESS_YN := UPPER(x_cpsv_rec.IN_PROCESS_YN);
2200 /********************* END HAND-CODED ********************************/
2201 RETURN(l_return_status);
2202 END Set_Attributes;
2203 BEGIN
2204 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2205 G_PKG_NAME,
2206 p_init_msg_list,
2207 l_api_version,
2208 p_api_version,
2209 '_PVT',
2210 x_return_status);
2211 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2212 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2213 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2214 RAISE OKC_API.G_EXCEPTION_ERROR;
2215 END IF;
2216 --- Setting item attributes
2217 l_return_status := Set_Attributes(
2218 p_cpsv_rec, -- IN
2219 l_cpsv_rec); -- OUT
2220 --- If any errors happen abort API
2221 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2222 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2223 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2224 RAISE OKC_API.G_EXCEPTION_ERROR;
2225 END IF;
2226 l_return_status := populate_new_record(l_cpsv_rec, l_def_cpsv_rec);
2227 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2228 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2229 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2230 RAISE OKC_API.G_EXCEPTION_ERROR;
2231 END IF;
2232 l_def_cpsv_rec := fill_who_columns(l_def_cpsv_rec);
2233 --- Validate all non-missing attributes (Item Level Validation)
2234 l_return_status := Validate_Attributes(l_def_cpsv_rec);
2235 --- If any errors happen abort API
2236 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2237 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2238 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2239 RAISE OKC_API.G_EXCEPTION_ERROR;
2240 END IF;
2241 l_return_status := Validate_Record(l_def_cpsv_rec);
2242 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2243 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2244 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2245 RAISE OKC_API.G_EXCEPTION_ERROR;
2246 END IF;
2247
2248 --------------------------------------
2249 -- Move VIEW record to "Child" records
2250 --------------------------------------
2251 migrate(l_def_cpsv_rec, l_cps_rec);
2252 --------------------------------------------
2253 -- Call the UPDATE_ROW for each child record
2254 --------------------------------------------
2255 update_row(
2256 p_init_msg_list,
2257 x_return_status,
2258 x_msg_count,
2259 x_msg_data,
2260 l_cps_rec,
2261 lx_cps_rec
2262 );
2263 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2264 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2265 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2266 RAISE OKC_API.G_EXCEPTION_ERROR;
2267 END IF;
2268 migrate(lx_cps_rec, l_def_cpsv_rec);
2269 x_cpsv_rec := l_def_cpsv_rec;
2270 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2271 EXCEPTION
2272 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2273 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2274 (
2275 l_api_name,
2276 G_PKG_NAME,
2277 'OKC_API.G_RET_STS_ERROR',
2278 x_msg_count,
2279 x_msg_data,
2280 '_PVT'
2281 );
2282 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2283 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2284 (
2285 l_api_name,
2286 G_PKG_NAME,
2287 'OKC_API.G_RET_STS_UNEXP_ERROR',
2288 x_msg_count,
2289 x_msg_data,
2290 '_PVT'
2291 );
2292 WHEN OTHERS THEN
2293 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2294 (
2295 l_api_name,
2296 G_PKG_NAME,
2297 'OTHERS',
2298 x_msg_count,
2299 x_msg_data,
2300 '_PVT'
2301 );
2302 END update_row;
2303 ----------------------------------------
2304 -- PL/SQL TBL update_row for:CPSV_TBL --
2305 ----------------------------------------
2306 PROCEDURE update_row(
2307 p_api_version IN NUMBER,
2308 p_init_msg_list IN VARCHAR2 ,
2309 x_return_status OUT NOCOPY VARCHAR2,
2310 x_msg_count OUT NOCOPY NUMBER,
2311 x_msg_data OUT NOCOPY VARCHAR2,
2312 p_cpsv_tbl IN cpsv_tbl_type,
2313 x_cpsv_tbl OUT NOCOPY cpsv_tbl_type) IS
2314
2315 l_api_version CONSTANT NUMBER := 1;
2316 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2317 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2318 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2319 i NUMBER := 0;
2320 BEGIN
2321 OKC_API.init_msg_list(p_init_msg_list);
2322 -- Make sure PL/SQL table has records in it before passing
2323 IF (p_cpsv_tbl.COUNT > 0) THEN
2324 i := p_cpsv_tbl.FIRST;
2325 LOOP
2326 update_row (
2327 p_api_version => p_api_version,
2328 p_init_msg_list => OKC_API.G_FALSE,
2329 x_return_status => x_return_status,
2330 x_msg_count => x_msg_count,
2331 x_msg_data => x_msg_data,
2332 p_cpsv_rec => p_cpsv_tbl(i),
2333 x_cpsv_rec => x_cpsv_tbl(i));
2334
2335 -- store the highest degree of error
2336 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2337 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2338 l_overall_status := x_return_status;
2339 End If;
2340 End If;
2341
2342 EXIT WHEN (i = p_cpsv_tbl.LAST);
2343 i := p_cpsv_tbl.NEXT(i);
2344 END LOOP;
2345 -- return overall status
2346 x_return_status := l_overall_status;
2347 END IF;
2348 EXCEPTION
2349 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2351 (
2352 l_api_name,
2353 G_PKG_NAME,
2354 'OKC_API.G_RET_STS_ERROR',
2355 x_msg_count,
2356 x_msg_data,
2357 '_PVT'
2358 );
2359 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2360 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2361 (
2362 l_api_name,
2363 G_PKG_NAME,
2364 'OKC_API.G_RET_STS_UNEXP_ERROR',
2365 x_msg_count,
2366 x_msg_data,
2367 '_PVT'
2368 );
2369 WHEN OTHERS THEN
2370 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2371 (
2372 l_api_name,
2373 G_PKG_NAME,
2374 'OTHERS',
2375 x_msg_count,
2376 x_msg_data,
2377 '_PVT'
2378 );
2379 END update_row;
2380
2381 ---------------------------------------------------------------------------
2382 -- PROCEDURE delete_row
2383 ---------------------------------------------------------------------------
2384 ------------------------------------
2385 -- delete_row for:OKC_K_PROCESSES --
2386 ------------------------------------
2387 PROCEDURE delete_row(
2388 p_init_msg_list IN VARCHAR2 ,
2389 x_return_status OUT NOCOPY VARCHAR2,
2390 x_msg_count OUT NOCOPY NUMBER,
2391 x_msg_data OUT NOCOPY VARCHAR2,
2392 p_cps_rec IN cps_rec_type) IS
2393
2394 l_api_version CONSTANT NUMBER := 1;
2395 l_api_name CONSTANT VARCHAR2(30) := 'PROCESSES_delete_row';
2396 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2397 l_cps_rec cps_rec_type:= p_cps_rec;
2398 l_row_notfound BOOLEAN := TRUE;
2399 BEGIN
2400 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2401 p_init_msg_list,
2402 '_PVT',
2403 x_return_status);
2404 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2405 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2406 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2407 RAISE OKC_API.G_EXCEPTION_ERROR;
2408 END IF;
2409 DELETE FROM OKC_K_PROCESSES
2410 WHERE ID = l_cps_rec.id;
2411
2412 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2413 EXCEPTION
2414 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2415 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2416 (
2417 l_api_name,
2418 G_PKG_NAME,
2419 'OKC_API.G_RET_STS_ERROR',
2420 x_msg_count,
2421 x_msg_data,
2422 '_PVT'
2423 );
2424 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2425 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2426 (
2427 l_api_name,
2428 G_PKG_NAME,
2429 'OKC_API.G_RET_STS_UNEXP_ERROR',
2430 x_msg_count,
2431 x_msg_data,
2432 '_PVT'
2433 );
2434 WHEN OTHERS THEN
2435 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2436 (
2437 l_api_name,
2438 G_PKG_NAME,
2439 'OTHERS',
2440 x_msg_count,
2441 x_msg_data,
2442 '_PVT'
2443 );
2444 END delete_row;
2445 --------------------------------------
2446 -- delete_row for:OKC_K_PROCESSES_V --
2447 --------------------------------------
2448 PROCEDURE delete_row(
2449 p_api_version IN NUMBER,
2450 p_init_msg_list IN VARCHAR2 ,
2451 x_return_status OUT NOCOPY VARCHAR2,
2452 x_msg_count OUT NOCOPY NUMBER,
2453 x_msg_data OUT NOCOPY VARCHAR2,
2454 p_cpsv_rec IN cpsv_rec_type) IS
2455
2456 l_api_version CONSTANT NUMBER := 1;
2457 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2458 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2459 l_cpsv_rec cpsv_rec_type := p_cpsv_rec;
2460 l_cps_rec cps_rec_type;
2461 BEGIN
2462 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2463 G_PKG_NAME,
2464 p_init_msg_list,
2465 l_api_version,
2466 p_api_version,
2467 '_PVT',
2468 x_return_status);
2469 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2470 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2471 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2472 RAISE OKC_API.G_EXCEPTION_ERROR;
2473 END IF;
2474 --------------------------------------
2475 -- Move VIEW record to "Child" records
2476 --------------------------------------
2477 migrate(l_cpsv_rec, l_cps_rec);
2478 --------------------------------------------
2479 -- Call the DELETE_ROW for each child record
2480 --------------------------------------------
2481 delete_row(
2482 p_init_msg_list,
2483 x_return_status,
2484 x_msg_count,
2485 x_msg_data,
2486 l_cps_rec
2487 );
2488 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2489 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2490 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2491 RAISE OKC_API.G_EXCEPTION_ERROR;
2492 END IF;
2493 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2494 EXCEPTION
2495 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2496 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2497 (
2498 l_api_name,
2499 G_PKG_NAME,
2500 'OKC_API.G_RET_STS_ERROR',
2501 x_msg_count,
2502 x_msg_data,
2503 '_PVT'
2504 );
2505 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2506 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2507 (
2508 l_api_name,
2509 G_PKG_NAME,
2510 'OKC_API.G_RET_STS_UNEXP_ERROR',
2511 x_msg_count,
2512 x_msg_data,
2513 '_PVT'
2514 );
2515 WHEN OTHERS THEN
2516 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2517 (
2518 l_api_name,
2519 G_PKG_NAME,
2520 'OTHERS',
2521 x_msg_count,
2522 x_msg_data,
2523 '_PVT'
2524 );
2525 END delete_row;
2526 ----------------------------------------
2527 -- PL/SQL TBL delete_row for:CPSV_TBL --
2528 ----------------------------------------
2529 PROCEDURE delete_row(
2530 p_api_version IN NUMBER,
2531 p_init_msg_list IN VARCHAR2 ,
2532 x_return_status OUT NOCOPY VARCHAR2,
2533 x_msg_count OUT NOCOPY NUMBER,
2534 x_msg_data OUT NOCOPY VARCHAR2,
2535 p_cpsv_tbl IN cpsv_tbl_type) IS
2536
2537 l_api_version CONSTANT NUMBER := 1;
2538 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2539 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2540 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2541 i NUMBER := 0;
2542 BEGIN
2543 OKC_API.init_msg_list(p_init_msg_list);
2544 -- Make sure PL/SQL table has records in it before passing
2545 IF (p_cpsv_tbl.COUNT > 0) THEN
2546 i := p_cpsv_tbl.FIRST;
2547 LOOP
2548 delete_row (
2549 p_api_version => p_api_version,
2550 p_init_msg_list => OKC_API.G_FALSE,
2551 x_return_status => x_return_status,
2552 x_msg_count => x_msg_count,
2553 x_msg_data => x_msg_data,
2554 p_cpsv_rec => p_cpsv_tbl(i));
2555
2556 -- store the highest degree of error
2557 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2558 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2559 l_overall_status := x_return_status;
2560 End If;
2561 End If;
2562
2563 EXIT WHEN (i = p_cpsv_tbl.LAST);
2564 i := p_cpsv_tbl.NEXT(i);
2565 END LOOP;
2566 -- return overall status
2567 x_return_status := l_overall_status;
2568 END IF;
2569 EXCEPTION
2570 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2571 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2572 (
2573 l_api_name,
2574 G_PKG_NAME,
2575 'OKC_API.G_RET_STS_ERROR',
2576 x_msg_count,
2577 x_msg_data,
2578 '_PVT'
2579 );
2580 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2581 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2582 (
2583 l_api_name,
2584 G_PKG_NAME,
2585 'OKC_API.G_RET_STS_UNEXP_ERROR',
2586 x_msg_count,
2587 x_msg_data,
2588 '_PVT'
2589 );
2590 WHEN OTHERS THEN
2591 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2592 (
2593 l_api_name,
2594 G_PKG_NAME,
2595 'OTHERS',
2596 x_msg_count,
2597 x_msg_data,
2598 '_PVT'
2599 );
2600 END delete_row;
2601
2602 ---------------------------------------------------------------
2603 -- Procedure for mass insert in OKC_K_PROCESSES table
2604 ---------------------------------------------------------------
2605 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_cpsv_tbl cpsv_tbl_type) IS
2606 l_tabsize NUMBER := p_cpsv_tbl.COUNT;
2607 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
2608
2609 in_id OKC_DATATYPES.NumberTabTyp;
2610 in_object_version_number OKC_DATATYPES.NumberTabTyp;
2611 in_pdf_id OKC_DATATYPES.NumberTabTyp;
2612 in_chr_id OKC_DATATYPES.NumberTabTyp;
2613 in_user_id OKC_DATATYPES.NumberTabTyp;
2614 in_crt_id OKC_DATATYPES.NumberTabTyp;
2615 in_process_id OKC_DATATYPES.Var240TabTyp;
2616 in_in_process_yn OKC_DATATYPES.Var3TabTyp;
2617 in_attribute_category OKC_DATATYPES.Var90TabTyp;
2618 in_attribute1 OKC_DATATYPES.Var450TabTyp;
2619 in_attribute2 OKC_DATATYPES.Var450TabTyp;
2620 in_attribute3 OKC_DATATYPES.Var450TabTyp;
2621 in_attribute4 OKC_DATATYPES.Var450TabTyp;
2622 in_attribute5 OKC_DATATYPES.Var450TabTyp;
2623 in_attribute6 OKC_DATATYPES.Var450TabTyp;
2624 in_attribute7 OKC_DATATYPES.Var450TabTyp;
2625 in_attribute8 OKC_DATATYPES.Var450TabTyp;
2626 in_attribute9 OKC_DATATYPES.Var450TabTyp;
2627 in_attribute10 OKC_DATATYPES.Var450TabTyp;
2628 in_attribute11 OKC_DATATYPES.Var450TabTyp;
2629 in_attribute12 OKC_DATATYPES.Var450TabTyp;
2630 in_attribute13 OKC_DATATYPES.Var450TabTyp;
2631 in_attribute14 OKC_DATATYPES.Var450TabTyp;
2632 in_attribute15 OKC_DATATYPES.Var450TabTyp;
2633 in_created_by OKC_DATATYPES.NumberTabTyp;
2634 in_creation_date OKC_DATATYPES.DateTabTyp;
2635 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
2636 in_last_update_date OKC_DATATYPES.DateTabTyp;
2637 in_last_update_login OKC_DATATYPES.NumberTabTyp;
2638 i NUMBER := p_cpsv_tbl.FIRST;
2639 j NUMBER := 0;
2640 BEGIN
2641 --Initialize return status
2642 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2643
2644 -- pkoganti 08/26/2000
2645 -- replace for loop with while loop to handle
2646 -- gaps in pl/sql table indexes.
2647 -- Example:
2648 -- consider a pl/sql table(A) with the following elements
2649 -- A(1) = 10
2650 -- A(2) = 20
2651 -- A(6) = 30
2652 -- A(7) = 40
2653 --
2654 -- The for loop was erroring for indexes 3,4,5, the while loop
2655 -- along with the NEXT operator would handle the missing indexes
2656 -- with out causing the API to fail.
2657 --
2658
2659 WHILE i IS NOT NULL
2660 LOOP
2661 j := j + 1;
2662
2663 in_id (j) := p_cpsv_tbl(i).id;
2664 in_object_version_number (j) := p_cpsv_tbl(i).object_version_number;
2665 in_pdf_id (j) := p_cpsv_tbl(i).pdf_id;
2666 in_chr_id (j) := p_cpsv_tbl(i).chr_id;
2667 in_user_id (j) := p_cpsv_tbl(i).user_id;
2668 in_crt_id (j) := p_cpsv_tbl(i).crt_id;
2669 in_process_id (j) := p_cpsv_tbl(i).process_id;
2670 in_in_process_yn (j) := p_cpsv_tbl(i).in_process_yn;
2671 in_attribute_category (j) := p_cpsv_tbl(i).attribute_category;
2672 in_attribute1 (j) := p_cpsv_tbl(i).attribute1;
2673 in_attribute2 (j) := p_cpsv_tbl(i).attribute2;
2674 in_attribute3 (j) := p_cpsv_tbl(i).attribute3;
2675 in_attribute4 (j) := p_cpsv_tbl(i).attribute4;
2676 in_attribute5 (j) := p_cpsv_tbl(i).attribute5;
2677 in_attribute6 (j) := p_cpsv_tbl(i).attribute6;
2678 in_attribute7 (j) := p_cpsv_tbl(i).attribute7;
2679 in_attribute8 (j) := p_cpsv_tbl(i).attribute8;
2680 in_attribute9 (j) := p_cpsv_tbl(i).attribute9;
2681 in_attribute10 (j) := p_cpsv_tbl(i).attribute10;
2682 in_attribute11 (j) := p_cpsv_tbl(i).attribute11;
2683 in_attribute12 (j) := p_cpsv_tbl(i).attribute12;
2684 in_attribute13 (j) := p_cpsv_tbl(i).attribute13;
2685 in_attribute14 (j) := p_cpsv_tbl(i).attribute14;
2686 in_attribute15 (j) := p_cpsv_tbl(i).attribute15;
2687 in_created_by (j) := p_cpsv_tbl(i).created_by;
2688 in_creation_date (j) := p_cpsv_tbl(i).creation_date;
2689 in_last_updated_by (j) := p_cpsv_tbl(i).last_updated_by;
2690 in_last_update_date (j) := p_cpsv_tbl(i).last_update_date;
2691 in_last_update_login (j) := p_cpsv_tbl(i).last_update_login;
2692
2693 i := p_cpsv_tbl.NEXT(i);
2694
2695 END LOOP;
2696
2697 FORALL i in 1..l_tabsize
2698 INSERT
2699 INTO OKC_K_PROCESSES
2700 (
2701 id,
2702 pdf_id,
2703 chr_id,
2704 user_id,
2705 crt_id,
2706 object_version_number,
2707 created_by,
2708 creation_date,
2709 last_updated_by,
2710 last_update_date,
2711 process_id,
2712 in_process_yn,
2713 last_update_login,
2714 attribute_category,
2715 attribute1,
2716 attribute2,
2717 attribute3,
2718 attribute4,
2719 attribute5,
2720 attribute6,
2721 attribute7,
2722 attribute8,
2723 attribute9,
2724 attribute10,
2725 attribute11,
2726 attribute12,
2727 attribute13,
2728 attribute14,
2729 attribute15
2730 )
2731 VALUES (
2732 in_id(i),
2733 in_pdf_id(i),
2734 in_chr_id(i),
2735 in_user_id(i),
2736 in_crt_id(i),
2737 in_object_version_number(i),
2738 in_created_by(i),
2739 in_creation_date(i),
2740 in_last_updated_by(i),
2741 in_last_update_date(i),
2742 in_process_id(i),
2743 in_in_process_yn(i),
2744 in_last_update_login(i),
2745 in_attribute_category(i),
2746 in_attribute1(i),
2747 in_attribute2(i),
2748 in_attribute3(i),
2749 in_attribute4(i),
2750 in_attribute5(i),
2751 in_attribute6(i),
2752 in_attribute7(i),
2753 in_attribute8(i),
2754 in_attribute9(i),
2755 in_attribute10(i),
2756 in_attribute11(i),
2757 in_attribute12(i),
2758 in_attribute13(i),
2759 in_attribute14(i),
2760 in_attribute15(i)
2761 );
2762
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 -- store SQL error message on message stack
2766 OKC_API.SET_MESSAGE(
2767 p_app_name => G_APP_NAME,
2768 p_msg_name => G_UNEXPECTED_ERROR,
2769 p_token1 => G_SQLCODE_TOKEN,
2770 p_token1_value => SQLCODE,
2771 p_token2 => G_SQLERRM_TOKEN,
2772 p_token2_value => SQLERRM);
2773 -- notify caller of an error as UNEXPECTED error
2774 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2775
2776 -- RAISE;
2777 END INSERT_ROW_UPG;
2778
2779 --This function is called from versioning API OKC_VERSION_PVT
2780 --Old Location: OKCRVERB.pls
2781 --New Location: Base Table API
2782
2783 FUNCTION create_version(
2784 p_chr_id IN NUMBER,
2785 p_major_version IN NUMBER
2786 ) RETURN VARCHAR2 IS
2787
2788 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2789
2790 BEGIN
2791 INSERT INTO okc_k_processes_h
2792 (
2793 major_version,
2794 id,
2795 pdf_id,
2796 chr_id,
2797 user_id,
2798 crt_id,
2799 object_version_number,
2800 created_by,
2801 creation_date,
2802 last_updated_by,
2803 last_update_date,
2804 process_id,
2805 in_process_yn,
2806 last_update_login,
2807 attribute_category,
2808 attribute1,
2809 attribute2,
2810 attribute3,
2811 attribute4,
2812 attribute5,
2813 attribute6,
2814 attribute7,
2815 attribute8,
2816 attribute9,
2817 attribute10,
2818 attribute11,
2819 attribute12,
2820 attribute13,
2821 attribute14,
2822 attribute15
2823 )
2824 SELECT
2825 p_major_version,
2826 id,
2827 pdf_id,
2828 chr_id,
2829 user_id,
2830 crt_id,
2831 object_version_number,
2832 created_by,
2833 creation_date,
2834 last_updated_by,
2835 last_update_date,
2836 process_id,
2837 in_process_yn,
2838 last_update_login,
2839 attribute_category,
2840 attribute1,
2841 attribute2,
2842 attribute3,
2843 attribute4,
2844 attribute5,
2845 attribute6,
2846 attribute7,
2847 attribute8,
2848 attribute9,
2849 attribute10,
2850 attribute11,
2851 attribute12,
2852 attribute13,
2853 attribute14,
2854 attribute15
2855 FROM okc_k_processes
2856 WHERE chr_id = p_chr_id;
2857
2858 RETURN l_return_status;
2859 EXCEPTION
2860 -- other appropriate handlers
2861 WHEN OTHERS THEN
2862 -- store SQL error message on message stack
2863 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2864 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2865 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2866 p_token1_value => sqlcode,
2867 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2868 p_token2_value => sqlerrm);
2869
2870 -- notify UNEXPECTED error
2871 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2872 return l_return_status;
2873 END create_version;
2874
2875 --This Function is called from Versioning API OKC_VERSION_PVT
2876 --Old Location:OKCRVERB.pls
2877 --New Location:Base Table API
2878
2879 FUNCTION restore_version(
2880 p_chr_id IN NUMBER,
2881 p_major_version IN NUMBER
2882 ) RETURN VARCHAR2 IS
2883
2884 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2885
2886 BEGIN
2887 INSERT INTO okc_k_processes
2888 (
2889 id,
2890 pdf_id,
2891 chr_id,
2892 user_id,
2893 crt_id,
2894 object_version_number,
2895 created_by,
2896 creation_date,
2897 last_updated_by,
2898 last_update_date,
2899 process_id,
2900 in_process_yn,
2901 last_update_login,
2902 attribute_category,
2903 attribute1,
2904 attribute2,
2905 attribute3,
2906 attribute4,
2907 attribute5,
2908 attribute6,
2909 attribute7,
2910 attribute8,
2911 attribute9,
2912 attribute10,
2913 attribute11,
2914 attribute12,
2915 attribute13,
2916 attribute14,
2917 attribute15
2918 )
2919 SELECT
2920 id,
2921 pdf_id,
2922 chr_id,
2923 user_id,
2924 crt_id,
2925 object_version_number,
2926 created_by,
2927 creation_date,
2928 last_updated_by,
2929 last_update_date,
2930 process_id,
2931 in_process_yn,
2932 last_update_login,
2933 attribute_category,
2934 attribute1,
2935 attribute2,
2936 attribute3,
2937 attribute4,
2938 attribute5,
2939 attribute6,
2940 attribute7,
2941 attribute8,
2942 attribute9,
2943 attribute10,
2944 attribute11,
2945 attribute12,
2946 attribute13,
2947 attribute14,
2948 attribute15
2949 FROM okc_k_processes_h
2950 WHERE chr_id = p_chr_id
2951 AND major_version = p_major_version;
2952
2953 RETURN l_return_status;
2954 EXCEPTION
2955 -- other appropriate handlers
2956 WHEN OTHERS THEN
2957 -- store SQL error message on message stack
2958 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2959 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2960 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2961 p_token1_value => sqlcode,
2962 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2963 p_token2_value => sqlerrm);
2964
2965 -- notify UNEXPECTED error
2966 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2967 return l_return_status;
2968 END restore_version;
2969
2970
2971 END OKC_CPS_PVT;