[Home] [Help]
PACKAGE BODY: APPS.OKC_SCC_PVT
Source
1 PACKAGE BODY OKC_SCC_PVT AS
2 /* $Header: OKCSSCCB.pls 120.0 2005/05/25 19:27:42 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /************************ HAND-CODED *********************************/
7 FUNCTION Validate_Attributes (p_sccv_rec in sccv_rec_type) RETURN VARCHAR2;
8
9 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
10 -- Start of comments
11 --
12 -- Procedure Name : validate_scn_id
13 -- Description :
14 -- Business Rules :
15 -- Parameters :
16 -- Version : 1.0
17 -- End of comments
18 PROCEDURE validate_scn_id(x_return_status OUT NOCOPY VARCHAR2,
19 p_sccv_rec IN sccv_rec_type) is
20
21 l_dummy_var VARCHAR2(1) := '?';
22 Cursor l_scnv_csr Is
23 select 'x'
24 from OKC_SECTIONS_B
25 where id = p_sccv_rec.scn_id;
26
27 Begin
28
29 IF (l_debug = 'Y') THEN
30 okc_debug.Set_Indentation('OKC_SCC_PVT');
31 okc_debug.log('100: Entered validate_scn_id', 2);
32 END IF;
33
34 -- initialize return status
35 x_return_status := OKC_API.G_RET_STS_SUCCESS;
36
37 -- check that data exists
38 If (p_sccv_rec.scn_id = OKC_API.G_MISS_NUM or
39 p_sccv_rec.scn_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 => 'scn_id');
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_scnv_csr;
54 Fetch l_scnv_csr Into l_dummy_var;
55 Close l_scnv_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 => 'scn_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_SECTIONS_V');
67
68 -- notify caller of an error
69 x_return_status := OKC_API.G_RET_STS_ERROR;
70 End If;
71
72 IF (l_debug = 'Y') THEN
73 okc_debug.log('200: Leaving validate_scn_id', 2);
74 okc_debug.Reset_Indentation;
75 END IF;
76
77 exception
78 when OTHERS then
79
80 IF (l_debug = 'Y') THEN
81 okc_debug.log('300: Exiting validate_scn_id:OTHERS Exception', 2);
82 okc_debug.Reset_Indentation;
83 END IF;
84
85 -- store SQL error message on message stack
86 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
87 p_msg_name => g_unexpected_error,
88 p_token1 => g_sqlcode_token,
89 p_token1_value => sqlcode,
90 p_token2 => g_sqlerrm_token,
91 p_token2_value => sqlerrm);
92 -- notify caller of an error as UNEXPETED error
93 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
94
95 -- verify that cursor was closed
96 if l_scnv_csr%ISOPEN then
97 close l_scnv_csr;
98 end if;
99
100 End validate_scn_id;
101
102 -- Start of comments
103 --
104 -- Procedure Name : validate_cat_id
105 -- Description :
106 -- Business Rules :
107 -- Parameters :
108 -- Version : 1.0
109 -- End of comments
110 PROCEDURE validate_cat_id(x_return_status OUT NOCOPY VARCHAR2,
111 p_sccv_rec IN sccv_rec_type) is
112
113 l_dummy_var VARCHAR2(1) := '?';
114 Cursor l_catv_csr Is
115 select 'x'
116 from OKC_K_ARTICLES_B
117 where id = p_sccv_rec.cat_id;
118
119 Begin
120
121 IF (l_debug = 'Y') THEN
122 okc_debug.Set_Indentation('OKC_SCC_PVT');
123 okc_debug.log('400: Entered validate_cat_id', 2);
124 END IF;
125
126 -- initialize return status
127 x_return_status := OKC_API.G_RET_STS_SUCCESS;
128
129 -- enforce foreign key (cat_id is optional)
130 If (p_sccv_rec.cat_id <> OKC_API.G_MISS_NUM and
131 p_sccv_rec.cat_id IS NOT NULL)
132 Then
133 Open l_catv_csr;
134 Fetch l_catv_csr Into l_dummy_var;
135 Close l_catv_csr;
136 -- if l_dummy_var still set to default, data was not found
137 If (l_dummy_var = '?') Then
138 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
139 p_msg_name => g_no_parent_record,
140 p_token1 => g_col_name_token,
141 p_token1_value => 'cat_id',
142 p_token2 => g_child_table_token,
143 p_token2_value => G_VIEW,
144 p_token3 => g_parent_table_token,
145 p_token3_value => 'OKC_K_ARTICLES_V');
146 -- notify caller of an error
147 x_return_status := OKC_API.G_RET_STS_ERROR;
148 End If;
149 End If;
150
151 IF (l_debug = 'Y') THEN
152 okc_debug.log('500: Leaving validate_cat_id', 2);
153 okc_debug.Reset_Indentation;
154 END IF;
155
156 exception
157 when OTHERS then
158
159 IF (l_debug = 'Y') THEN
160 okc_debug.log('600: Exiting validate_cat_id:OTHERS Exception', 2);
161 okc_debug.Reset_Indentation;
162 END IF;
163
164 -- store SQL error message on message stack
165 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
166 p_msg_name => g_unexpected_error,
167 p_token1 => g_sqlcode_token,
168 p_token1_value => sqlcode,
169 p_token2 => g_sqlerrm_token,
170 p_token2_value => sqlerrm);
171 -- notify caller of an error as UNEXPETED error
172 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
173
174 -- verify that cursor was closed
175 if l_catv_csr%ISOPEN then
176 close l_catv_csr;
177 end if;
178
179 End validate_cat_id;
180
181 -- Start of comments
182 --
183 -- Procedure Name : validate_cle_id
184 -- Description :
185 -- Business Rules :
186 -- Parameters :
187 -- Version : 1.0
188 -- End of comments
189 PROCEDURE validate_cle_id(x_return_status OUT NOCOPY VARCHAR2,
190 p_sccv_rec IN sccv_rec_type) is
191
192 l_dummy_var VARCHAR2(1) := '?';
193 Cursor l_clev_csr Is
194 select 'x'
195 from OKC_K_LINES_B
196 where id = p_sccv_rec.cle_id;
197
198 Begin
199
200 IF (l_debug = 'Y') THEN
201 okc_debug.Set_Indentation('OKC_SCC_PVT');
202 okc_debug.log('700: Entered validate_cle_id', 2);
203 END IF;
204
205 -- initialize return status
206 x_return_status := OKC_API.G_RET_STS_SUCCESS;
207
208 -- enforce foreign key (cle_id is optional)
209 If (p_sccv_rec.cle_id <> OKC_API.G_MISS_NUM and
210 p_sccv_rec.cle_id IS NOT NULL)
211 Then
212 Open l_clev_csr;
213 Fetch l_clev_csr Into l_dummy_var;
214 Close l_clev_csr;
215 -- if l_dummy_var still set to default, data was not found
216 If (l_dummy_var = '?') Then
217 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
218 p_msg_name => g_no_parent_record,
219 p_token1 => g_col_name_token,
220 p_token1_value => 'cle_id',
221 p_token2 => g_child_table_token,
222 p_token2_value => G_VIEW,
223 p_token3 => g_parent_table_token,
224 p_token3_value => 'OKC_K_LINES_V');
225 -- notify caller of an error
226 x_return_status := OKC_API.G_RET_STS_ERROR;
227 End If;
228 End If;
229
230 IF (l_debug = 'Y') THEN
231 okc_debug.log('800: Leaving validate_cle_id', 2);
232 okc_debug.Reset_Indentation;
233 END IF;
234
235 exception
236 when OTHERS then
237
238 IF (l_debug = 'Y') THEN
239 okc_debug.log('900: Exiting validate_cle_id:OTHERS Exception', 2);
240 okc_debug.Reset_Indentation;
241 END IF;
242
243 -- store SQL error message on message stack
244 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
245 p_msg_name => g_unexpected_error,
246 p_token1 => g_sqlcode_token,
247 p_token1_value => sqlcode,
248 p_token2 => g_sqlerrm_token,
249 p_token2_value => sqlerrm);
250 -- notify caller of an error as UNEXPETED error
251 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
252
253 -- verify that cursor was closed
254 if l_clev_csr%ISOPEN then
255 close l_clev_csr;
256 end if;
257
258 End validate_cle_id;
259
260 -- Start of comments
261 --
262 -- Procedure Name : validate_sae_id
263 -- Description :
264 -- Business Rules :
265 -- Parameters :
266 -- Version : 1.0
267 -- End of comments
268 PROCEDURE validate_sae_id(x_return_status OUT NOCOPY VARCHAR2,
269 p_sccv_rec IN sccv_rec_type) is
270
271 l_dummy_var VARCHAR2(1) := '?';
272 Cursor l_saev_csr Is
273 select 'x'
274 from OKC_STD_ARTICLES_B
275 where id = p_sccv_rec.sae_id;
276
277 Begin
278
279 IF (l_debug = 'Y') THEN
280 okc_debug.Set_Indentation('OKC_SCC_PVT');
281 okc_debug.log('1000: Entered validate_sae_id', 2);
282 END IF;
283
284 -- initialize return status
285 x_return_status := OKC_API.G_RET_STS_SUCCESS;
286
287 -- enforce foreign key (sae_id is optional)
288 If (p_sccv_rec.sae_id <> OKC_API.G_MISS_NUM and
289 p_sccv_rec.sae_id IS NOT NULL)
290 Then
291 Open l_saev_csr;
292 Fetch l_saev_csr Into l_dummy_var;
293 Close l_saev_csr;
294 -- if l_dummy_var still set to default, data was not found
295 If (l_dummy_var = '?') Then
296 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
297 p_msg_name => g_no_parent_record,
298 p_token1 => g_col_name_token,
299 p_token1_value => 'sae_id',
300 p_token2 => g_child_table_token,
301 p_token2_value => G_VIEW,
302 p_token3 => g_parent_table_token,
303 p_token3_value => 'OKC_STD_ARTICLES_V');
304 -- notify caller of an error
305 x_return_status := OKC_API.G_RET_STS_ERROR;
306 End If;
307 End If;
308
309 IF (l_debug = 'Y') THEN
310 okc_debug.log('1100: Leaving validate_sae_id', 2);
311 okc_debug.Reset_Indentation;
312 END IF;
313
314 exception
315 when OTHERS then
316
317 IF (l_debug = 'Y') THEN
318 okc_debug.log('1200: Exiting validate_sae_id:OTHERS Exception', 2);
319 okc_debug.Reset_Indentation;
320 END IF;
321
322 -- store SQL error message on message stack
323 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
324 p_msg_name => g_unexpected_error,
325 p_token1 => g_sqlcode_token,
326 p_token1_value => sqlcode,
327 p_token2 => g_sqlerrm_token,
328 p_token2_value => sqlerrm);
329 -- notify caller of an error as UNEXPETED error
330 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
331
332 -- verify that cursor was closed
333 if l_saev_csr%ISOPEN then
334 close l_saev_csr;
335 end if;
336
337 End validate_sae_id;
338
339 -- Start of comments
340 --
341 -- Procedure Name : validate_content_sequence
342 -- Description :
343 -- Business Rules :
344 -- Parameters :
345 -- Version : 1.0
346 -- End of comments
347 PROCEDURE validate_content_sequence(x_return_status OUT NOCOPY VARCHAR2,
348 p_sccv_rec IN sccv_rec_type) is
349
350 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
351 -- l_unq_tbl OKC_UTIL.unq_tbl_type;
352
353 -- ------------------------------------------------------
354 -- To check for any matching row, for unique check
355 -- The cursor includes id check filter to handle updates
356 -- for case K2 should not overwrite already existing K1
357 -- ------------------------------------------------------
358 CURSOR cur_vcs IS
359 SELECT 'x'
360 FROM okc_section_contents
361 WHERE scn_id = p_sccv_rec.SCN_ID
362 AND content_sequence = p_sccv_rec.CONTENT_SEQUENCE
363 AND id <> NVL(p_sccv_rec.ID,-9999);
364
365 l_row_found BOOLEAN := False;
366 l_dummy VARCHAR2(1);
367
368 BEGIN
369
370 IF (l_debug = 'Y') THEN
371 okc_debug.Set_Indentation('OKC_SCC_PVT');
372 okc_debug.log('1300: Entered validate_content_sequence', 2);
373 END IF;
374
375 -- initialize return status
376 x_return_status := OKC_API.G_RET_STS_SUCCESS;
377
378 -- -------------------------------------------------------------------
379 -- Bug 1636056 related changes - Shyam
380 -- OKC_UTIL.check_comp_unique call earlier was not using
381 -- the bind variables and parses everytime, replaced with
382 -- the explicit cursors above, for identical function
383 -- to check uniqueness for SCN_ID + CONTENT_SEQUENCE
384 -- chr_id and sat_code are mutually exclusive (in actual values)
385 -- if chr_id >0, sat_code is -99, else sat code should be valid(<> -99)
386 -- -------------------------------------------------------------------
387
388 IF ( p_sccv_rec.content_sequence IS NOT NULL
389 AND p_sccv_rec.content_sequence <> OKC_API.G_MISS_NUM )
390 THEN
391 -- check for any matching value in the database
392 OPEN cur_vcs;
393 FETCH cur_vcs INTO l_dummy;
394 l_row_found := cur_vcs%FOUND;
395 CLOSE cur_vcs;
396 ELSE
397 -- display the alert for missing required-value
398 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
399 p_msg_name => g_required_value,
400 p_token1 => g_col_name_token,
401 p_token1_value => 'content_sequence');
402
403 -- set error flag and halt validation
404 x_return_status := OKC_API.G_RET_STS_ERROR;
405 RAISE G_EXCEPTION_HALT_VALIDATION;
406 END IF;
407
408 IF (l_row_found)
409 THEN
410 -- Display the newly defined error message
411 OKC_API.set_message(G_APP_NAME,
412 'OKC_DUP_SEQUENCE_NUMBER');
413
414 -- set error flag and halt validation
415 x_return_status := OKC_API.G_RET_STS_ERROR;
416 END IF;
417
418 IF (l_debug = 'Y') THEN
419 okc_debug.log('1400: Leaving validate_content_sequence', 2);
420 okc_debug.Reset_Indentation;
421 END IF;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425
426 IF (l_debug = 'Y') THEN
427 okc_debug.log('1500: Exiting validate_content_sequence:OTHERS Exception', 2);
428 okc_debug.Reset_Indentation;
429 END IF;
430
431 -- store SQL error message on message stack
432 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
433 p_msg_name => g_unexpected_error,
434 p_token1 => g_sqlcode_token,
435 p_token1_value => sqlcode,
436 p_token2 => g_sqlerrm_token,
437 p_token2_value => sqlerrm);
438 -- notify caller of an error as UNEXPETED error
439 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
440
441
442 END validate_content_sequence;
443
444 /*********************** END HAND-CODED ******************************/
445
446 ---------------------------------------------------------------------------
447 -- FUNCTION get_seq_id
448 ---------------------------------------------------------------------------
449 FUNCTION get_seq_id RETURN NUMBER IS
450 BEGIN
451
452 RETURN(okc_p_util.raw_to_number(sys_guid()));
453
454 END get_seq_id;
455
456 ---------------------------------------------------------------------------
457 -- FUNCTION get_rec for: OKC_SECTION_CONTENTS
458 ---------------------------------------------------------------------------
459 FUNCTION get_rec (
460 p_scc_rec IN scc_rec_type,
461 x_no_data_found OUT NOCOPY BOOLEAN
462 ) RETURN scc_rec_type IS
463 CURSOR okc_section_contents_pk_csr (p_id IN NUMBER) IS
464 SELECT
465 ID,
466 SCN_ID,
467 CONTENT_SEQUENCE,
468 OBJECT_VERSION_NUMBER,
469 CREATED_BY,
470 CREATION_DATE,
471 LAST_UPDATED_BY,
472 LAST_UPDATE_DATE,
473 LAST_UPDATE_LOGIN,
474 LABEL,
475 CAT_ID,
476 CLE_ID,
477 SAE_ID,
478 ATTRIBUTE_CATEGORY,
479 ATTRIBUTE1,
480 ATTRIBUTE2,
481 ATTRIBUTE3,
482 ATTRIBUTE4,
483 ATTRIBUTE5,
484 ATTRIBUTE6,
485 ATTRIBUTE7,
486 ATTRIBUTE8,
487 ATTRIBUTE9,
488 ATTRIBUTE10,
489 ATTRIBUTE11,
490 ATTRIBUTE12,
491 ATTRIBUTE13,
492 ATTRIBUTE14,
493 ATTRIBUTE15
494 FROM Okc_Section_Contents
495 WHERE okc_section_contents.id = p_id;
496 l_okc_section_contents_pk okc_section_contents_pk_csr%ROWTYPE;
497 l_scc_rec scc_rec_type;
498 BEGIN
499
500 IF (l_debug = 'Y') THEN
501 okc_debug.Set_Indentation('OKC_SCC_PVT');
502 okc_debug.log('1700: Entered get_rec', 2);
503 END IF;
504
505 x_no_data_found := TRUE;
506 -- Get current database values
507 OPEN okc_section_contents_pk_csr (p_scc_rec.id);
508 FETCH okc_section_contents_pk_csr INTO
509 l_scc_rec.ID,
510 l_scc_rec.SCN_ID,
511 l_scc_rec.CONTENT_SEQUENCE,
512 l_scc_rec.OBJECT_VERSION_NUMBER,
513 l_scc_rec.CREATED_BY,
514 l_scc_rec.CREATION_DATE,
515 l_scc_rec.LAST_UPDATED_BY,
516 l_scc_rec.LAST_UPDATE_DATE,
517 l_scc_rec.LAST_UPDATE_LOGIN,
518 l_scc_rec.LABEL,
519 l_scc_rec.CAT_ID,
520 l_scc_rec.CLE_ID,
521 l_scc_rec.SAE_ID,
522 l_scc_rec.ATTRIBUTE_CATEGORY,
523 l_scc_rec.ATTRIBUTE1,
524 l_scc_rec.ATTRIBUTE2,
525 l_scc_rec.ATTRIBUTE3,
526 l_scc_rec.ATTRIBUTE4,
527 l_scc_rec.ATTRIBUTE5,
528 l_scc_rec.ATTRIBUTE6,
529 l_scc_rec.ATTRIBUTE7,
530 l_scc_rec.ATTRIBUTE8,
531 l_scc_rec.ATTRIBUTE9,
532 l_scc_rec.ATTRIBUTE10,
533 l_scc_rec.ATTRIBUTE11,
534 l_scc_rec.ATTRIBUTE12,
535 l_scc_rec.ATTRIBUTE13,
536 l_scc_rec.ATTRIBUTE14,
537 l_scc_rec.ATTRIBUTE15;
538 x_no_data_found := okc_section_contents_pk_csr%NOTFOUND;
539 CLOSE okc_section_contents_pk_csr;
540
541 IF (l_debug = 'Y') THEN
542 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
543 okc_debug.Reset_Indentation;
544 END IF;
545
546 RETURN(l_scc_rec);
547
548 END get_rec;
549
550 FUNCTION get_rec (
551 p_scc_rec IN scc_rec_type
552 ) RETURN scc_rec_type IS
553 l_row_notfound BOOLEAN := TRUE;
554 BEGIN
555
556 RETURN(get_rec(p_scc_rec, l_row_notfound));
557
558 END get_rec;
559 ---------------------------------------------------------------------------
560 -- FUNCTION get_rec for: OKC_SECTION_CONTENTS_V
561 ---------------------------------------------------------------------------
562 FUNCTION get_rec (
563 p_sccv_rec IN sccv_rec_type,
564 x_no_data_found OUT NOCOPY BOOLEAN
565 ) RETURN sccv_rec_type IS
566
567 -- TAPI generator code MISSING FROM HERE - JOHN (added)
568 CURSOR okc_sccv_pk_csr (p_id IN NUMBER) IS
569 SELECT
570 ID,
571 SCN_ID,
572 LABEL,
573 CAT_ID,
574 CLE_ID,
575 SAE_ID,
576 CONTENT_SEQUENCE,
577 OBJECT_VERSION_NUMBER,
578 CREATED_BY,
579 CREATION_DATE,
580 LAST_UPDATED_BY,
581 LAST_UPDATE_DATE,
582 LAST_UPDATE_LOGIN,
583 ATTRIBUTE_CATEGORY,
584 ATTRIBUTE1,
585 ATTRIBUTE2,
586 ATTRIBUTE3,
587 ATTRIBUTE4,
588 ATTRIBUTE5,
589 ATTRIBUTE6,
590 ATTRIBUTE7,
591 ATTRIBUTE8,
592 ATTRIBUTE9,
593 ATTRIBUTE10,
594 ATTRIBUTE11,
595 ATTRIBUTE12,
596 ATTRIBUTE13,
597 ATTRIBUTE14,
598 ATTRIBUTE15
599 FROM okc_section_contents_v
600 WHERE okc_section_contents_v.id = p_id;
601
602 l_okc_sccv_pk okc_sccv_pk_csr%ROWTYPE;
603
604 -- UPTO THIS (JOHN)
605
606 l_sccv_rec sccv_rec_type;
607 BEGIN
608
609 IF (l_debug = 'Y') THEN
610 okc_debug.Set_Indentation('OKC_SCC_PVT');
611 okc_debug.log('1900: Entered get_rec', 2);
612 END IF;
613
614 x_no_data_found := TRUE;
615
616 -- TAPI generator code missing from here - added JOHN
617
618 -- Get current database values
619 OPEN okc_sccv_pk_csr (p_sccv_rec.id);
620 FETCH okc_sccv_pk_csr INTO
621 l_sccv_rec.ID,
622 l_sccv_rec.SCN_ID,
623 l_sccv_rec.LABEL,
624 l_sccv_rec.CAT_ID,
625 l_sccv_rec.CLE_ID,
626 l_sccv_rec.SAE_ID,
627 l_sccv_rec.CONTENT_SEQUENCE,
628 l_sccv_rec.OBJECT_VERSION_NUMBER,
629 l_sccv_rec.CREATED_BY,
630 l_sccv_rec.CREATION_DATE,
631 l_sccv_rec.LAST_UPDATED_BY,
632 l_sccv_rec.LAST_UPDATE_DATE,
633 l_sccv_rec.LAST_UPDATE_LOGIN,
634 l_sccv_rec.ATTRIBUTE_CATEGORY,
635 l_sccv_rec.ATTRIBUTE1,
636 l_sccv_rec.ATTRIBUTE2,
637 l_sccv_rec.ATTRIBUTE3,
638 l_sccv_rec.ATTRIBUTE4,
639 l_sccv_rec.ATTRIBUTE5,
640 l_sccv_rec.ATTRIBUTE6,
641 l_sccv_rec.ATTRIBUTE7,
642 l_sccv_rec.ATTRIBUTE8,
643 l_sccv_rec.ATTRIBUTE9,
644 l_sccv_rec.ATTRIBUTE10,
645 l_sccv_rec.ATTRIBUTE11,
646 l_sccv_rec.ATTRIBUTE12,
647 l_sccv_rec.ATTRIBUTE13,
648 l_sccv_rec.ATTRIBUTE14,
649 l_sccv_rec.ATTRIBUTE15;
650
651 x_no_data_found := okc_sccv_pk_csr%NOTFOUND;
652 CLOSE okc_sccv_pk_csr;
653
654 -- UPTO THIS - JOHN
655
656 IF (l_debug = 'Y') THEN
657 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
658 okc_debug.Reset_Indentation;
659 END IF;
660
661 RETURN(l_sccv_rec);
662
663 END get_rec;
664
665 FUNCTION get_rec (
666 p_sccv_rec IN sccv_rec_type
667 ) RETURN sccv_rec_type IS
668 l_row_notfound BOOLEAN := TRUE;
669 BEGIN
670
671 RETURN(get_rec(p_sccv_rec, l_row_notfound));
672
673 END get_rec;
674
675 ------------------------------------------------------------
676 -- FUNCTION null_out_defaults for: OKC_SECTION_CONTENTS_V --
677 ------------------------------------------------------------
678 FUNCTION null_out_defaults (
679 p_sccv_rec IN sccv_rec_type
680 ) RETURN sccv_rec_type IS
681 l_sccv_rec sccv_rec_type := p_sccv_rec;
682 BEGIN
683
684 IF (l_debug = 'Y') THEN
685 okc_debug.Set_Indentation('OKC_SCC_PVT');
686 okc_debug.log('2100: Entered null_out_defaults', 2);
687 END IF;
688
689 IF (l_sccv_rec.id = OKC_API.G_MISS_NUM) THEN
690 l_sccv_rec.id := NULL;
691 END IF;
692 IF (l_sccv_rec.scn_id = OKC_API.G_MISS_NUM) THEN
693 l_sccv_rec.scn_id := NULL;
694 END IF;
695 IF (l_sccv_rec.label = OKC_API.G_MISS_CHAR) THEN
696 l_sccv_rec.label := NULL;
697 END IF;
698 IF (l_sccv_rec.cat_id = OKC_API.G_MISS_NUM) THEN
699 l_sccv_rec.cat_id := NULL;
700 END IF;
701 IF (l_sccv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
702 l_sccv_rec.cle_id := NULL;
703 END IF;
704 IF (l_sccv_rec.sae_id = OKC_API.G_MISS_NUM) THEN
705 l_sccv_rec.sae_id := NULL;
706 END IF;
707 IF (l_sccv_rec.content_sequence = OKC_API.G_MISS_NUM) THEN
708 l_sccv_rec.content_sequence := NULL;
709 END IF;
710 IF (l_sccv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
711 l_sccv_rec.object_version_number := NULL;
712 END IF;
713 IF (l_sccv_rec.created_by = OKC_API.G_MISS_NUM) THEN
714 l_sccv_rec.created_by := NULL;
715 END IF;
716 IF (l_sccv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
717 l_sccv_rec.creation_date := NULL;
718 END IF;
719 IF (l_sccv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
720 l_sccv_rec.last_updated_by := NULL;
721 END IF;
722 IF (l_sccv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
723 l_sccv_rec.last_update_date := NULL;
724 END IF;
725 IF (l_sccv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
726 l_sccv_rec.last_update_login := NULL;
727 END IF;
728 IF (l_sccv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
729 l_sccv_rec.attribute_category := NULL;
730 END IF;
731 IF (l_sccv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
732 l_sccv_rec.attribute1 := NULL;
733 END IF;
734 IF (l_sccv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
735 l_sccv_rec.attribute2 := NULL;
736 END IF;
737 IF (l_sccv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
738 l_sccv_rec.attribute3 := NULL;
739 END IF;
740 IF (l_sccv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
741 l_sccv_rec.attribute4 := NULL;
742 END IF;
743 IF (l_sccv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
744 l_sccv_rec.attribute5 := NULL;
745 END IF;
746 IF (l_sccv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
747 l_sccv_rec.attribute6 := NULL;
748 END IF;
749 IF (l_sccv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
750 l_sccv_rec.attribute7 := NULL;
751 END IF;
752 IF (l_sccv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
753 l_sccv_rec.attribute8 := NULL;
754 END IF;
755 IF (l_sccv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
756 l_sccv_rec.attribute9 := NULL;
757 END IF;
758 IF (l_sccv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
759 l_sccv_rec.attribute10 := NULL;
760 END IF;
761 IF (l_sccv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
762 l_sccv_rec.attribute11 := NULL;
763 END IF;
764 IF (l_sccv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
765 l_sccv_rec.attribute12 := NULL;
766 END IF;
767 IF (l_sccv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
768 l_sccv_rec.attribute13 := NULL;
769 END IF;
770 IF (l_sccv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
771 l_sccv_rec.attribute14 := NULL;
772 END IF;
773 IF (l_sccv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
774 l_sccv_rec.attribute15 := NULL;
775 END IF;
776
777 IF (l_debug = 'Y') THEN
778 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
779 okc_debug.Reset_Indentation;
780 END IF;
781
782 RETURN(l_sccv_rec);
783
784 END null_out_defaults;
785 ---------------------------------------------------------------------------
786 -- PROCEDURE Validate_Attributes
787 ---------------------------------------------------------------------------
788 ----------------------------------------------------
789 -- Validate_Attributes for:OKC_SECTION_CONTENTS_V --
790 ----------------------------------------------------
791 FUNCTION Validate_Attributes (
792 p_sccv_rec IN sccv_rec_type
793 ) RETURN VARCHAR2 IS
794 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
795 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
796 BEGIN
797
798 IF (l_debug = 'Y') THEN
799 okc_debug.Set_Indentation('OKC_SCC_PVT');
800 okc_debug.log('2200: Entered Validate_Attributes', 2);
801 END IF;
802
803 /************************ HAND-CODED *********************************/
804 validate_scn_id
805 (x_return_status => l_return_status,
806 p_sccv_rec => p_sccv_rec);
807
808 -- store the highest degree of error
809 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
810 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
811 x_return_status := l_return_status;
812 End If;
813 End If;
814
815 validate_cat_id
816 (x_return_status => l_return_status,
817 p_sccv_rec => p_sccv_rec);
818
819 -- store the highest degree of error
820 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
821 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
822 x_return_status := l_return_status;
823 End If;
824 End If;
825
826 validate_cle_id
827 (x_return_status => l_return_status,
828 p_sccv_rec => p_sccv_rec);
829
830 -- store the highest degree of error
831 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
832 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
833 x_return_status := l_return_status;
834 End If;
835 End If;
836
837 validate_sae_id
838 (x_return_status => l_return_status,
839 p_sccv_rec => p_sccv_rec);
840
841 -- store the highest degree of error
842 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
843 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
844 x_return_status := l_return_status;
845 End If;
846 End If;
847
848 validate_content_sequence
849 (x_return_status => l_return_status,
850 p_sccv_rec => p_sccv_rec);
851
852 -- store the highest degree of error
853 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
854 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
855 x_return_status := l_return_status;
856 End If;
857 End If;
858
859 IF (l_debug = 'Y') THEN
860 okc_debug.log('2300: Leaving Validate_Attributes', 2);
861 okc_debug.Reset_Indentation;
862 END IF;
863
864 RETURN(x_return_status);
865
866 exception
867 when OTHERS then
868
869 IF (l_debug = 'Y') THEN
870 okc_debug.log('2400: Exiting Validate_Attributes:OTHERS Exception', 2);
871 okc_debug.Reset_Indentation;
872 END IF;
873
874 -- store SQL error message on message stack
875 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
876 p_msg_name => g_unexpected_error,
877 p_token1 => g_sqlcode_token,
878 p_token1_value => sqlcode,
879 p_token2 => g_sqlerrm_token,
880 p_token2_value => sqlerrm);
881
882 -- notify caller of an UNEXPETED error
883 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
884
885 -- return status to caller
886 RETURN(x_return_status);
887 /*********************** END HAND-CODED ********************************/
888
889 END Validate_Attributes;
890
891 ---------------------------------------------------------------------------
892 -- PROCEDURE Validate_Record
893 ---------------------------------------------------------------------------
894 ------------------------------------------------
895 -- Validate_Record for:OKC_SECTION_CONTENTS_V --
896 ------------------------------------------------
897 FUNCTION Validate_Record (
898 p_sccv_rec IN sccv_rec_type
899 ) RETURN VARCHAR2 IS
900 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
901
902 l_counter NUMBER := 0;
903
904 Cursor l_sccv_csr Is
905 SELECT count(*)
906 FROM okc_section_contents_v
907 WHERE cat_id = p_sccv_rec.CAT_ID
908 AND scn_id IN (SELECT id
909 FROM okc_sections_b
910 WHERE chr_id = (SELECT chr_id
911 FROM okc_sections_b
912 WHERE id = p_sccv_rec.SCN_ID))
913 AND id <> p_sccv_rec.id;
914 BEGIN
915
916 IF (l_debug = 'Y') THEN
917 okc_debug.Set_Indentation('OKC_SCC_PVT');
918 okc_debug.log('2500: Entered Validate_Record', 2);
919 END IF;
920
921 Open l_sccv_csr;
922 Fetch l_sccv_csr Into l_counter;
923 Close l_sccv_csr;
924
925 If (l_counter > 0 ) Then
926 l_return_status := OKC_API.G_RET_STS_ERROR;
927 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
928 p_msg_name => 'OKC_VALUE_NOT_UNIQUE',
929 p_token1 => G_COL_NAME_TOKEN,
930 p_token1_value => 'Article');
931 End If;
932
933 IF (l_debug = 'Y') THEN
934 okc_debug.log('500: Leaving Validate_Record ', 2);
935 okc_debug.Reset_Indentation;
936 END IF;
937
938 RETURN (l_return_status);
939
940 END Validate_Record;
941
942 ---------------------------------------------------------------------------
943 -- PROCEDURE Migrate
944 ---------------------------------------------------------------------------
945 PROCEDURE migrate (
946 p_from IN sccv_rec_type,
947 p_to OUT NOCOPY scc_rec_type
948 ) IS
949 BEGIN
950
951 p_to.id := p_from.id;
952 p_to.scn_id := p_from.scn_id;
953 p_to.content_sequence := p_from.content_sequence;
954 p_to.object_version_number := p_from.object_version_number;
955 p_to.created_by := p_from.created_by;
956 p_to.creation_date := p_from.creation_date;
957 p_to.last_updated_by := p_from.last_updated_by;
958 p_to.last_update_date := p_from.last_update_date;
959 p_to.last_update_login := p_from.last_update_login;
960 p_to.label := p_from.label;
961 p_to.cat_id := p_from.cat_id;
962 p_to.cle_id := p_from.cle_id;
963 p_to.sae_id := p_from.sae_id;
964 p_to.attribute_category := p_from.attribute_category;
965 p_to.attribute1 := p_from.attribute1;
966 p_to.attribute2 := p_from.attribute2;
967 p_to.attribute3 := p_from.attribute3;
968 p_to.attribute4 := p_from.attribute4;
969 p_to.attribute5 := p_from.attribute5;
970 p_to.attribute6 := p_from.attribute6;
971 p_to.attribute7 := p_from.attribute7;
972 p_to.attribute8 := p_from.attribute8;
973 p_to.attribute9 := p_from.attribute9;
974 p_to.attribute10 := p_from.attribute10;
975 p_to.attribute11 := p_from.attribute11;
976 p_to.attribute12 := p_from.attribute12;
977 p_to.attribute13 := p_from.attribute13;
978 p_to.attribute14 := p_from.attribute14;
979 p_to.attribute15 := p_from.attribute15;
980
981 END migrate;
982
983
984 PROCEDURE migrate (
985 p_from IN scc_rec_type,
986 p_to OUT NOCOPY sccv_rec_type
987 ) IS
988 BEGIN
989
990 p_to.id := p_from.id;
991 p_to.scn_id := p_from.scn_id;
992 p_to.content_sequence := p_from.content_sequence;
993 p_to.object_version_number := p_from.object_version_number;
994 p_to.created_by := p_from.created_by;
995 p_to.creation_date := p_from.creation_date;
996 p_to.last_updated_by := p_from.last_updated_by;
997 p_to.last_update_date := p_from.last_update_date;
998 p_to.last_update_login := p_from.last_update_login;
999 p_to.label := p_from.label;
1000 p_to.cat_id := p_from.cat_id;
1001 p_to.cle_id := p_from.cle_id;
1002 p_to.sae_id := p_from.sae_id;
1003 p_to.attribute_category := p_from.attribute_category;
1004 p_to.attribute1 := p_from.attribute1;
1005 p_to.attribute2 := p_from.attribute2;
1006 p_to.attribute3 := p_from.attribute3;
1007 p_to.attribute4 := p_from.attribute4;
1008 p_to.attribute5 := p_from.attribute5;
1009 p_to.attribute6 := p_from.attribute6;
1010 p_to.attribute7 := p_from.attribute7;
1011 p_to.attribute8 := p_from.attribute8;
1012 p_to.attribute9 := p_from.attribute9;
1013 p_to.attribute10 := p_from.attribute10;
1014 p_to.attribute11 := p_from.attribute11;
1015 p_to.attribute12 := p_from.attribute12;
1016 p_to.attribute13 := p_from.attribute13;
1017 p_to.attribute14 := p_from.attribute14;
1018 p_to.attribute15 := p_from.attribute15;
1019
1020 END migrate;
1021
1022 ---------------------------------------------------------------------------
1023 -- PROCEDURE validate_row
1024 ---------------------------------------------------------------------------
1025 ---------------------------------------------
1026 -- validate_row for:OKC_SECTION_CONTENTS_V --
1027 ---------------------------------------------
1028 PROCEDURE validate_row(
1029 p_api_version IN NUMBER,
1030 p_init_msg_list IN VARCHAR2,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2,
1034 p_sccv_rec IN sccv_rec_type) IS
1035
1036 l_api_version CONSTANT NUMBER := 1;
1037 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1038 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1039 l_sccv_rec sccv_rec_type := p_sccv_rec;
1040 l_scc_rec scc_rec_type;
1041 BEGIN
1042
1043 IF (l_debug = 'Y') THEN
1044 okc_debug.Set_Indentation('OKC_SCC_PVT');
1045 okc_debug.log('2800: Entered validate_row', 2);
1046 END IF;
1047
1048 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1049 G_PKG_NAME,
1050 p_init_msg_list,
1051 l_api_version,
1052 p_api_version,
1053 '_PVT',
1054 x_return_status);
1055 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1056 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1057 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1058 RAISE OKC_API.G_EXCEPTION_ERROR;
1059 END IF;
1060 --- Validate all non-missing attributes (Item Level Validation)
1061 l_return_status := Validate_Attributes(l_sccv_rec);
1062 --- If any errors happen abort API
1063 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1064 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1065 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1066 RAISE OKC_API.G_EXCEPTION_ERROR;
1067 END IF;
1068 l_return_status := Validate_Record(l_sccv_rec);
1069 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1070 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1071 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1072 RAISE OKC_API.G_EXCEPTION_ERROR;
1073 END IF;
1074 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1075
1076 IF (l_debug = 'Y') THEN
1077 okc_debug.log('2900: Leaving validate_row', 2);
1078 okc_debug.Reset_Indentation;
1079 END IF;
1080
1081 EXCEPTION
1082 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1083
1084 IF (l_debug = 'Y') THEN
1085 okc_debug.log('3000: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1086 okc_debug.Reset_Indentation;
1087 END IF;
1088
1089 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1090 (
1091 l_api_name,
1092 G_PKG_NAME,
1093 'OKC_API.G_RET_STS_ERROR',
1094 x_msg_count,
1095 x_msg_data,
1096 '_PVT'
1097 );
1098 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1099
1100 IF (l_debug = 'Y') THEN
1101 okc_debug.log('3100: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1102 okc_debug.Reset_Indentation;
1103 END IF;
1104
1105 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1106 (
1107 l_api_name,
1108 G_PKG_NAME,
1109 'OKC_API.G_RET_STS_UNEXP_ERROR',
1110 x_msg_count,
1111 x_msg_data,
1112 '_PVT'
1113 );
1114 WHEN OTHERS THEN
1115
1116 IF (l_debug = 'Y') THEN
1117 okc_debug.log('3200: Exiting validate_row:OTHERS Exception', 2);
1118 okc_debug.Reset_Indentation;
1119 END IF;
1120
1121 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OTHERS',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PVT'
1129 );
1130
1131 END validate_row;
1132 ------------------------------------------
1133 -- PL/SQL TBL validate_row for:SCCV_TBL --
1134 ------------------------------------------
1135 PROCEDURE validate_row(
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_sccv_tbl IN sccv_tbl_type) IS
1142
1143 l_api_version CONSTANT NUMBER := 1;
1144 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1145 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1146 i NUMBER := 0;
1147 BEGIN
1148
1149 IF (l_debug = 'Y') THEN
1150 okc_debug.Set_Indentation('OKC_SCC_PVT');
1151 okc_debug.log('3300: Entered validate_row', 2);
1152 END IF;
1153
1154 OKC_API.init_msg_list(p_init_msg_list);
1155 -- Make sure PL/SQL table has records in it before passing
1156 IF (p_sccv_tbl.COUNT > 0) THEN
1157 i := p_sccv_tbl.FIRST;
1158 LOOP
1159 validate_row (
1160 p_api_version => p_api_version,
1161 p_init_msg_list => OKC_API.G_FALSE,
1162 x_return_status => x_return_status,
1163 x_msg_count => x_msg_count,
1164 x_msg_data => x_msg_data,
1165 p_sccv_rec => p_sccv_tbl(i));
1166 EXIT WHEN (i = p_sccv_tbl.LAST);
1167 i := p_sccv_tbl.NEXT(i);
1168 END LOOP;
1169 END IF;
1170
1171 IF (l_debug = 'Y') THEN
1172 okc_debug.log('3400: Leaving validate_row', 2);
1173 okc_debug.Reset_Indentation;
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1178
1179 IF (l_debug = 'Y') THEN
1180 okc_debug.log('3500: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1181 okc_debug.Reset_Indentation;
1182 END IF;
1183
1184 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1185 (
1186 l_api_name,
1187 G_PKG_NAME,
1188 'OKC_API.G_RET_STS_ERROR',
1189 x_msg_count,
1190 x_msg_data,
1191 '_PVT'
1192 );
1193 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1194
1195 IF (l_debug = 'Y') THEN
1196 okc_debug.log('3600: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1197 okc_debug.Reset_Indentation;
1198 END IF;
1199
1200 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1201 (
1202 l_api_name,
1203 G_PKG_NAME,
1204 'OKC_API.G_RET_STS_UNEXP_ERROR',
1205 x_msg_count,
1206 x_msg_data,
1207 '_PVT'
1208 );
1209 WHEN OTHERS THEN
1210
1211 IF (l_debug = 'Y') THEN
1212 okc_debug.log('3700: Exiting validate_row:OTHERS Exception', 2);
1213 okc_debug.Reset_Indentation;
1214 END IF;
1215
1216 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1217 (
1218 l_api_name,
1219 G_PKG_NAME,
1220 'OTHERS',
1221 x_msg_count,
1222 x_msg_data,
1223 '_PVT'
1224 );
1225
1226 END validate_row;
1227
1228 ---------------------------------------------------------------------------
1229 -- PROCEDURE insert_row
1230 ---------------------------------------------------------------------------
1231 -----------------------------------------
1232 -- insert_row for:OKC_SECTION_CONTENTS --
1233 -----------------------------------------
1234 PROCEDURE insert_row(
1235 p_init_msg_list IN VARCHAR2,
1236 x_return_status OUT NOCOPY VARCHAR2,
1237 x_msg_count OUT NOCOPY NUMBER,
1238 x_msg_data OUT NOCOPY VARCHAR2,
1239 p_scc_rec IN scc_rec_type,
1240 x_scc_rec OUT NOCOPY scc_rec_type) IS
1241
1242 l_api_version CONSTANT NUMBER := 1;
1243 l_api_name CONSTANT VARCHAR2(30) := 'CONTENTS_insert_row';
1244 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1245 l_scc_rec scc_rec_type := p_scc_rec;
1246 l_def_scc_rec scc_rec_type;
1247 ---------------------------------------------
1248 -- Set_Attributes for:OKC_SECTION_CONTENTS --
1249 ---------------------------------------------
1250 FUNCTION Set_Attributes (
1251 p_scc_rec IN scc_rec_type,
1252 x_scc_rec OUT NOCOPY scc_rec_type
1253 ) RETURN VARCHAR2 IS
1254 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1255 BEGIN
1256
1257 x_scc_rec := p_scc_rec;
1258 RETURN(l_return_status);
1259
1260 END Set_Attributes;
1261 BEGIN
1262
1263 IF (l_debug = 'Y') THEN
1264 okc_debug.Set_Indentation('OKC_SCC_PVT');
1265 okc_debug.log('3900: Entered insert_row', 2);
1266 END IF;
1267
1268 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1269 p_init_msg_list,
1270 '_PVT',
1271 x_return_status);
1272 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1274 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1275 RAISE OKC_API.G_EXCEPTION_ERROR;
1276 END IF;
1277 --- Setting item attributes
1278 l_return_status := Set_Attributes(
1279 p_scc_rec, -- IN
1280 l_scc_rec); -- OUT
1281 --- If any errors happen abort API
1282 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1283 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1284 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1285 RAISE OKC_API.G_EXCEPTION_ERROR;
1286 END IF;
1287 INSERT INTO OKC_SECTION_CONTENTS(
1288 id,
1289 scn_id,
1290 content_sequence,
1291 object_version_number,
1292 created_by,
1293 creation_date,
1294 last_updated_by,
1295 last_update_date,
1296 last_update_login,
1297 label,
1298 cat_id,
1299 cle_id,
1300 sae_id,
1301 attribute_category,
1302 attribute1,
1303 attribute2,
1304 attribute3,
1305 attribute4,
1306 attribute5,
1307 attribute6,
1308 attribute7,
1309 attribute8,
1310 attribute9,
1311 attribute10,
1312 attribute11,
1313 attribute12,
1314 attribute13,
1315 attribute14,
1316 attribute15)
1317 VALUES (
1318 l_scc_rec.id,
1319 l_scc_rec.scn_id,
1320 l_scc_rec.content_sequence,
1321 l_scc_rec.object_version_number,
1322 l_scc_rec.created_by,
1323 l_scc_rec.creation_date,
1324 l_scc_rec.last_updated_by,
1325 l_scc_rec.last_update_date,
1326 l_scc_rec.last_update_login,
1327 l_scc_rec.label,
1328 l_scc_rec.cat_id,
1329 l_scc_rec.cle_id,
1330 l_scc_rec.sae_id,
1331 l_scc_rec.attribute_category,
1332 l_scc_rec.attribute1,
1333 l_scc_rec.attribute2,
1334 l_scc_rec.attribute3,
1335 l_scc_rec.attribute4,
1336 l_scc_rec.attribute5,
1337 l_scc_rec.attribute6,
1338 l_scc_rec.attribute7,
1339 l_scc_rec.attribute8,
1340 l_scc_rec.attribute9,
1341 l_scc_rec.attribute10,
1342 l_scc_rec.attribute11,
1343 l_scc_rec.attribute12,
1344 l_scc_rec.attribute13,
1345 l_scc_rec.attribute14,
1346 l_scc_rec.attribute15);
1347 -- Set OUT values
1348 x_scc_rec := l_scc_rec;
1349 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1350
1351 IF (l_debug = 'Y') THEN
1352 okc_debug.log('4000: Leaving insert_row', 2);
1353 okc_debug.Reset_Indentation;
1354 END IF;
1355
1356 EXCEPTION
1357 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1358
1359 IF (l_debug = 'Y') THEN
1360 okc_debug.log('4100: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1361 okc_debug.Reset_Indentation;
1362 END IF;
1363
1364 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1365 (
1366 l_api_name,
1367 G_PKG_NAME,
1368 'OKC_API.G_RET_STS_ERROR',
1369 x_msg_count,
1370 x_msg_data,
1371 '_PVT'
1372 );
1373 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1374
1375 IF (l_debug = 'Y') THEN
1376 okc_debug.log('4200: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1377 okc_debug.Reset_Indentation;
1378 END IF;
1379
1380 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1381 (
1382 l_api_name,
1383 G_PKG_NAME,
1384 'OKC_API.G_RET_STS_UNEXP_ERROR',
1385 x_msg_count,
1386 x_msg_data,
1387 '_PVT'
1388 );
1389 WHEN OTHERS THEN
1390
1391 IF (l_debug = 'Y') THEN
1392 okc_debug.log('4300: Exiting insert_row:OTHERS Exception', 2);
1393 okc_debug.Reset_Indentation;
1394 END IF;
1395
1396 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1397 (
1398 l_api_name,
1399 G_PKG_NAME,
1400 'OTHERS',
1401 x_msg_count,
1402 x_msg_data,
1403 '_PVT'
1404 );
1405
1406 END insert_row;
1407 -------------------------------------------
1408 -- insert_row for:OKC_SECTION_CONTENTS_V --
1409 -------------------------------------------
1410 PROCEDURE insert_row(
1411 p_api_version IN NUMBER,
1412 p_init_msg_list IN VARCHAR2,
1413 x_return_status OUT NOCOPY VARCHAR2,
1414 x_msg_count OUT NOCOPY NUMBER,
1415 x_msg_data OUT NOCOPY VARCHAR2,
1416 p_sccv_rec IN sccv_rec_type,
1417 x_sccv_rec OUT NOCOPY sccv_rec_type) IS
1418
1419 l_api_version CONSTANT NUMBER := 1;
1420 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1421 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1422 l_sccv_rec sccv_rec_type;
1423 l_def_sccv_rec sccv_rec_type;
1424 l_scc_rec scc_rec_type;
1425 lx_scc_rec scc_rec_type;
1426 -------------------------------
1427 -- FUNCTION fill_who_columns --
1428 -------------------------------
1429 FUNCTION fill_who_columns (
1430 p_sccv_rec IN sccv_rec_type
1431 ) RETURN sccv_rec_type IS
1432 l_sccv_rec sccv_rec_type := p_sccv_rec;
1433 BEGIN
1434
1435 l_sccv_rec.CREATION_DATE := SYSDATE;
1436 l_sccv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1437 l_sccv_rec.LAST_UPDATE_DATE := l_sccv_rec.CREATION_DATE;
1438 l_sccv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1439 l_sccv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1440 RETURN(l_sccv_rec);
1441
1442 END fill_who_columns;
1443 -----------------------------------------------
1444 -- Set_Attributes for:OKC_SECTION_CONTENTS_V --
1445 -----------------------------------------------
1446 FUNCTION Set_Attributes (
1447 p_sccv_rec IN sccv_rec_type,
1448 x_sccv_rec OUT NOCOPY sccv_rec_type
1449 ) RETURN VARCHAR2 IS
1450 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1451 BEGIN
1452
1453 x_sccv_rec := p_sccv_rec;
1454 x_sccv_rec.OBJECT_VERSION_NUMBER := 1;
1455 RETURN(l_return_status);
1456
1457 END Set_Attributes;
1458 BEGIN
1459
1460 IF (l_debug = 'Y') THEN
1461 okc_debug.Set_Indentation('OKC_SCC_PVT');
1462 okc_debug.log('4600: Entered insert_row', 2);
1463 END IF;
1464
1465 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1466 G_PKG_NAME,
1467 p_init_msg_list,
1468 l_api_version,
1469 p_api_version,
1470 '_PVT',
1471 x_return_status);
1472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1475 RAISE OKC_API.G_EXCEPTION_ERROR;
1476 END IF;
1477 l_sccv_rec := null_out_defaults(p_sccv_rec);
1478 -- Set primary key value
1479 l_sccv_rec.ID := get_seq_id;
1480 --- Setting item attributes
1481 l_return_status := Set_Attributes(
1482 l_sccv_rec, -- IN
1483 l_def_sccv_rec); -- OUT
1484 --- If any errors happen abort API
1485 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1486 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1487 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1488 RAISE OKC_API.G_EXCEPTION_ERROR;
1489 END IF;
1490 l_def_sccv_rec := fill_who_columns(l_def_sccv_rec);
1491 --- Validate all non-missing attributes (Item Level Validation)
1492 l_return_status := Validate_Attributes(l_def_sccv_rec);
1493 --- If any errors happen abort API
1494 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1495 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1496 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1497 RAISE OKC_API.G_EXCEPTION_ERROR;
1498 END IF;
1499 l_return_status := Validate_Record(l_def_sccv_rec);
1500 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1501 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1502 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1503 RAISE OKC_API.G_EXCEPTION_ERROR;
1504 END IF;
1505 --------------------------------------
1506 -- Move VIEW record to "Child" records
1507 --------------------------------------
1508 migrate(l_def_sccv_rec, l_scc_rec);
1509 --------------------------------------------
1510 -- Call the INSERT_ROW for each child record
1511 --------------------------------------------
1512 insert_row(
1513 p_init_msg_list,
1514 x_return_status,
1515 x_msg_count,
1516 x_msg_data,
1517 l_scc_rec,
1518 lx_scc_rec
1519 );
1520 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1521 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1522 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1523 RAISE OKC_API.G_EXCEPTION_ERROR;
1524 END IF;
1525 migrate(lx_scc_rec, l_def_sccv_rec);
1526 -- Set OUT values
1527 x_sccv_rec := l_def_sccv_rec;
1528 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1529
1530 IF (l_debug = 'Y') THEN
1531 okc_debug.log('4700: Leaving insert_row', 2);
1532 okc_debug.Reset_Indentation;
1533 END IF;
1534
1535 EXCEPTION
1536 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1537
1538 IF (l_debug = 'Y') THEN
1539 okc_debug.log('4800: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1540 okc_debug.Reset_Indentation;
1541 END IF;
1542
1543 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1544 (
1545 l_api_name,
1546 G_PKG_NAME,
1547 'OKC_API.G_RET_STS_ERROR',
1548 x_msg_count,
1549 x_msg_data,
1550 '_PVT'
1551 );
1552 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1553
1554 IF (l_debug = 'Y') THEN
1555 okc_debug.log('4900: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1556 okc_debug.Reset_Indentation;
1557 END IF;
1558
1559 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKC_API.G_RET_STS_UNEXP_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT'
1567 );
1568 WHEN OTHERS THEN
1569
1570 IF (l_debug = 'Y') THEN
1571 okc_debug.log('5000: Exiting insert_row:OTHERS Exception', 2);
1572 okc_debug.Reset_Indentation;
1573 END IF;
1574
1575 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1576 (
1577 l_api_name,
1578 G_PKG_NAME,
1579 'OTHERS',
1580 x_msg_count,
1581 x_msg_data,
1582 '_PVT'
1583 );
1584
1585 END insert_row;
1586 ----------------------------------------
1587 -- PL/SQL TBL insert_row for:SCCV_TBL --
1588 ----------------------------------------
1589 PROCEDURE insert_row(
1590 p_api_version IN NUMBER,
1591 p_init_msg_list IN VARCHAR2,
1592 x_return_status OUT NOCOPY VARCHAR2,
1593 x_msg_count OUT NOCOPY NUMBER,
1594 x_msg_data OUT NOCOPY VARCHAR2,
1595 p_sccv_tbl IN sccv_tbl_type,
1596 x_sccv_tbl OUT NOCOPY sccv_tbl_type) IS
1597
1598 l_api_version CONSTANT NUMBER := 1;
1599 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1600 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1601 i NUMBER := 0;
1602 BEGIN
1603
1604 IF (l_debug = 'Y') THEN
1605 okc_debug.Set_Indentation('OKC_SCC_PVT');
1606 okc_debug.log('5100: Entered insert_row', 2);
1607 END IF;
1608
1609 OKC_API.init_msg_list(p_init_msg_list);
1610 -- Make sure PL/SQL table has records in it before passing
1611 IF (p_sccv_tbl.COUNT > 0) THEN
1612 i := p_sccv_tbl.FIRST;
1613 LOOP
1614 insert_row (
1615 p_api_version => p_api_version,
1616 p_init_msg_list => OKC_API.G_FALSE,
1617 x_return_status => x_return_status,
1618 x_msg_count => x_msg_count,
1619 x_msg_data => x_msg_data,
1620 p_sccv_rec => p_sccv_tbl(i),
1621 x_sccv_rec => x_sccv_tbl(i));
1622 EXIT WHEN (i = p_sccv_tbl.LAST);
1623 i := p_sccv_tbl.NEXT(i);
1624 END LOOP;
1625 END IF;
1626
1627 IF (l_debug = 'Y') THEN
1628 okc_debug.log('5200: Leaving insert_row', 2);
1629 okc_debug.Reset_Indentation;
1630 END IF;
1631
1632 EXCEPTION
1633 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1634
1635 IF (l_debug = 'Y') THEN
1636 okc_debug.log('5300: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1637 okc_debug.Reset_Indentation;
1638 END IF;
1639
1640 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OKC_API.G_RET_STS_ERROR',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1650
1651 IF (l_debug = 'Y') THEN
1652 okc_debug.log('5400: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1653 okc_debug.Reset_Indentation;
1654 END IF;
1655
1656 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1657 (
1658 l_api_name,
1659 G_PKG_NAME,
1660 'OKC_API.G_RET_STS_UNEXP_ERROR',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 WHEN OTHERS THEN
1666
1667 IF (l_debug = 'Y') THEN
1668 okc_debug.log('5500: Exiting insert_row:OTHERS Exception', 2);
1669 okc_debug.Reset_Indentation;
1670 END IF;
1671
1672 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1673 (
1674 l_api_name,
1675 G_PKG_NAME,
1676 'OTHERS',
1677 x_msg_count,
1678 x_msg_data,
1679 '_PVT'
1680 );
1681
1682 END insert_row;
1683
1684 ---------------------------------------------------------------------------
1685 -- PROCEDURE lock_row
1686 ---------------------------------------------------------------------------
1687 ---------------------------------------
1688 -- lock_row for:OKC_SECTION_CONTENTS --
1689 ---------------------------------------
1690 PROCEDURE lock_row(
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_scc_rec IN scc_rec_type) IS
1696
1697 E_Resource_Busy EXCEPTION;
1698 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1699 CURSOR lock_csr (p_scc_rec IN scc_rec_type) IS
1700 SELECT OBJECT_VERSION_NUMBER
1701 FROM OKC_SECTION_CONTENTS
1702 WHERE ID = p_scc_rec.id
1703 AND OBJECT_VERSION_NUMBER = p_scc_rec.object_version_number
1704 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1705
1706 CURSOR lchk_csr (p_scc_rec IN scc_rec_type) IS
1707 SELECT OBJECT_VERSION_NUMBER
1708 FROM OKC_SECTION_CONTENTS
1709 WHERE ID = p_scc_rec.id;
1710 l_api_version CONSTANT NUMBER := 1;
1711 l_api_name CONSTANT VARCHAR2(30) := 'CONTENTS_lock_row';
1712 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1713 l_object_version_number OKC_SECTION_CONTENTS.OBJECT_VERSION_NUMBER%TYPE;
1714 lc_object_version_number OKC_SECTION_CONTENTS.OBJECT_VERSION_NUMBER%TYPE;
1715 l_row_notfound BOOLEAN := FALSE;
1716 lc_row_notfound BOOLEAN := FALSE;
1717 BEGIN
1718
1719 IF (l_debug = 'Y') THEN
1720 okc_debug.Set_Indentation('OKC_SCC_PVT');
1721 okc_debug.log('5600: Entered lock_row', 2);
1722 END IF;
1723
1724 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1725 p_init_msg_list,
1726 '_PVT',
1727 x_return_status);
1728 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1729 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1730 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1731 RAISE OKC_API.G_EXCEPTION_ERROR;
1732 END IF;
1733
1734 -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1735 BEGIN
1736
1737 IF (l_debug = 'Y') THEN
1738 okc_debug.Set_Indentation('OKC_SCC_PVT');
1739 okc_debug.log('5700: Entered lock_row', 2);
1740 END IF;
1741
1742 OPEN lock_csr(p_scc_rec);
1743 FETCH lock_csr INTO l_object_version_number;
1744 l_row_notfound := lock_csr%NOTFOUND;
1745 CLOSE lock_csr;
1746
1747 IF (l_debug = 'Y') THEN
1748 okc_debug.log('5800: Leaving lock_row', 2);
1749 okc_debug.Reset_Indentation;
1750 END IF;
1751
1752 EXCEPTION
1753 WHEN E_Resource_Busy THEN
1754
1755 IF (l_debug = 'Y') THEN
1756 okc_debug.log('5900: Exiting lock_row:E_Resource_Busy Exception', 2);
1757 okc_debug.Reset_Indentation;
1758 END IF;
1759
1760 IF (lock_csr%ISOPEN) THEN
1761 CLOSE lock_csr;
1762 END IF;
1763 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1764 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1765 END;
1766
1767 -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1768
1769 IF ( l_row_notfound ) THEN
1770 OPEN lchk_csr(p_scc_rec);
1771 FETCH lchk_csr INTO lc_object_version_number;
1772 lc_row_notfound := lchk_csr%NOTFOUND;
1773 CLOSE lchk_csr;
1774 END IF;
1775 IF (lc_row_notfound) THEN
1776 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1777 RAISE OKC_API.G_EXCEPTION_ERROR;
1778 ELSIF lc_object_version_number > p_scc_rec.object_version_number THEN
1779 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1780 RAISE OKC_API.G_EXCEPTION_ERROR;
1781 ELSIF lc_object_version_number <> p_scc_rec.object_version_number THEN
1782 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1783 RAISE OKC_API.G_EXCEPTION_ERROR;
1784 ELSIF lc_object_version_number = -1 THEN
1785 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1786 RAISE OKC_API.G_EXCEPTION_ERROR;
1787 END IF;
1788 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1789
1790 IF (l_debug = 'Y') THEN
1791 okc_debug.log('6000: Leaving lock_row', 2);
1792 okc_debug.Reset_Indentation;
1793 END IF;
1794
1795 EXCEPTION
1796 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1797
1798 IF (l_debug = 'Y') THEN
1799 okc_debug.log('6100: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1800 okc_debug.Reset_Indentation;
1801 END IF;
1802
1803 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1804 (
1805 l_api_name,
1806 G_PKG_NAME,
1807 'OKC_API.G_RET_STS_ERROR',
1808 x_msg_count,
1809 x_msg_data,
1810 '_PVT'
1811 );
1812 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1813
1814 IF (l_debug = 'Y') THEN
1815 okc_debug.log('6200: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1816 okc_debug.Reset_Indentation;
1817 END IF;
1818
1819 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OKC_API.G_RET_STS_UNEXP_ERROR',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828 WHEN OTHERS THEN
1829
1830 IF (l_debug = 'Y') THEN
1831 okc_debug.log('6300: Exiting lock_row:OTHERS Exception', 2);
1832 okc_debug.Reset_Indentation;
1833 END IF;
1834
1835 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1836 (
1837 l_api_name,
1838 G_PKG_NAME,
1839 'OTHERS',
1840 x_msg_count,
1841 x_msg_data,
1842 '_PVT'
1843 );
1844
1845 END lock_row;
1846 -----------------------------------------
1847 -- lock_row for:OKC_SECTION_CONTENTS_V --
1848 -----------------------------------------
1849 PROCEDURE lock_row(
1850 p_api_version IN NUMBER,
1851 p_init_msg_list IN VARCHAR2,
1852 x_return_status OUT NOCOPY VARCHAR2,
1853 x_msg_count OUT NOCOPY NUMBER,
1854 x_msg_data OUT NOCOPY VARCHAR2,
1855 p_sccv_rec IN sccv_rec_type) IS
1856
1857 l_api_version CONSTANT NUMBER := 1;
1858 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1859 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1860 l_scc_rec scc_rec_type;
1861 BEGIN
1862
1863 IF (l_debug = 'Y') THEN
1864 okc_debug.Set_Indentation('OKC_SCC_PVT');
1865 okc_debug.log('6400: Entered lock_row', 2);
1866 END IF;
1867
1868 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1869 G_PKG_NAME,
1870 p_init_msg_list,
1871 l_api_version,
1872 p_api_version,
1873 '_PVT',
1874 x_return_status);
1875 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1876 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1877 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1878 RAISE OKC_API.G_EXCEPTION_ERROR;
1879 END IF;
1880 --------------------------------------
1881 -- Move VIEW record to "Child" records
1882 --------------------------------------
1883 migrate(p_sccv_rec, l_scc_rec);
1884 --------------------------------------------
1885 -- Call the LOCK_ROW for each child record
1886 --------------------------------------------
1887 lock_row(
1888 p_init_msg_list,
1889 x_return_status,
1890 x_msg_count,
1891 x_msg_data,
1892 l_scc_rec
1893 );
1894 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1895 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1896 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1897 RAISE OKC_API.G_EXCEPTION_ERROR;
1898 END IF;
1899 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1900
1901 IF (l_debug = 'Y') THEN
1902 okc_debug.log('6500: Leaving lock_row', 2);
1903 okc_debug.Reset_Indentation;
1904 END IF;
1905
1906 EXCEPTION
1907 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1908
1909 IF (l_debug = 'Y') THEN
1910 okc_debug.log('6600: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1911 okc_debug.Reset_Indentation;
1912 END IF;
1913
1914 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1915 (
1916 l_api_name,
1917 G_PKG_NAME,
1918 'OKC_API.G_RET_STS_ERROR',
1919 x_msg_count,
1920 x_msg_data,
1921 '_PVT'
1922 );
1923 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1924
1925 IF (l_debug = 'Y') THEN
1926 okc_debug.log('6700: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1927 okc_debug.Reset_Indentation;
1928 END IF;
1929
1930 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1931 (
1932 l_api_name,
1933 G_PKG_NAME,
1934 'OKC_API.G_RET_STS_UNEXP_ERROR',
1935 x_msg_count,
1936 x_msg_data,
1937 '_PVT'
1938 );
1939 WHEN OTHERS THEN
1940
1941 IF (l_debug = 'Y') THEN
1942 okc_debug.log('6800: Exiting lock_row:OTHERS Exception', 2);
1943 okc_debug.Reset_Indentation;
1944 END IF;
1945
1946 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1947 (
1948 l_api_name,
1949 G_PKG_NAME,
1950 'OTHERS',
1951 x_msg_count,
1952 x_msg_data,
1953 '_PVT'
1954 );
1955
1956 END lock_row;
1957 --------------------------------------
1958 -- PL/SQL TBL lock_row for:SCCV_TBL --
1959 --------------------------------------
1960 PROCEDURE lock_row(
1961 p_api_version IN NUMBER,
1962 p_init_msg_list IN VARCHAR2,
1963 x_return_status OUT NOCOPY VARCHAR2,
1964 x_msg_count OUT NOCOPY NUMBER,
1965 x_msg_data OUT NOCOPY VARCHAR2,
1966 p_sccv_tbl IN sccv_tbl_type) IS
1967
1968 l_api_version CONSTANT NUMBER := 1;
1969 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1970 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1971 i NUMBER := 0;
1972 BEGIN
1973
1974 IF (l_debug = 'Y') THEN
1975 okc_debug.Set_Indentation('OKC_SCC_PVT');
1976 okc_debug.log('6900: Entered lock_row', 2);
1977 END IF;
1978
1979 OKC_API.init_msg_list(p_init_msg_list);
1980 -- Make sure PL/SQL table has records in it before passing
1981 IF (p_sccv_tbl.COUNT > 0) THEN
1982 i := p_sccv_tbl.FIRST;
1983 LOOP
1984 lock_row (
1985 p_api_version => p_api_version,
1986 p_init_msg_list => OKC_API.G_FALSE,
1987 x_return_status => x_return_status,
1988 x_msg_count => x_msg_count,
1989 x_msg_data => x_msg_data,
1990 p_sccv_rec => p_sccv_tbl(i));
1991 EXIT WHEN (i = p_sccv_tbl.LAST);
1992 i := p_sccv_tbl.NEXT(i);
1993 END LOOP;
1994 END IF;
1995
1996 IF (l_debug = 'Y') THEN
1997 okc_debug.log('7000: Leaving lock_row', 2);
1998 okc_debug.Reset_Indentation;
1999 END IF;
2000
2001 EXCEPTION
2002 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2003
2004 IF (l_debug = 'Y') THEN
2005 okc_debug.log('7100: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2006 okc_debug.Reset_Indentation;
2007 END IF;
2008
2009 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2010 (
2011 l_api_name,
2012 G_PKG_NAME,
2013 'OKC_API.G_RET_STS_ERROR',
2014 x_msg_count,
2015 x_msg_data,
2016 '_PVT'
2017 );
2018 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2019
2020 IF (l_debug = 'Y') THEN
2021 okc_debug.log('7200: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2022 okc_debug.Reset_Indentation;
2023 END IF;
2024
2025 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2026 (
2027 l_api_name,
2028 G_PKG_NAME,
2029 'OKC_API.G_RET_STS_UNEXP_ERROR',
2030 x_msg_count,
2031 x_msg_data,
2032 '_PVT'
2033 );
2034 WHEN OTHERS THEN
2035
2036 IF (l_debug = 'Y') THEN
2037 okc_debug.log('7300: Exiting lock_row:OTHERS Exception', 2);
2038 okc_debug.Reset_Indentation;
2039 END IF;
2040
2041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2042 (
2043 l_api_name,
2044 G_PKG_NAME,
2045 'OTHERS',
2046 x_msg_count,
2047 x_msg_data,
2048 '_PVT'
2049 );
2050
2051 END lock_row;
2052
2053 ---------------------------------------------------------------------------
2054 -- PROCEDURE update_row
2055 ---------------------------------------------------------------------------
2056 -----------------------------------------
2057 -- update_row for:OKC_SECTION_CONTENTS --
2058 -----------------------------------------
2059 PROCEDURE update_row(
2060 p_init_msg_list IN VARCHAR2,
2061 x_return_status OUT NOCOPY VARCHAR2,
2062 x_msg_count OUT NOCOPY NUMBER,
2063 x_msg_data OUT NOCOPY VARCHAR2,
2064 p_scc_rec IN scc_rec_type,
2065 x_scc_rec OUT NOCOPY scc_rec_type) IS
2066
2067 l_api_version CONSTANT NUMBER := 1;
2068 l_api_name CONSTANT VARCHAR2(30) := 'CONTENTS_update_row';
2069 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2070 l_scc_rec scc_rec_type := p_scc_rec;
2071 l_def_scc_rec scc_rec_type;
2072 l_row_notfound BOOLEAN := TRUE;
2073 ----------------------------------
2074 -- FUNCTION populate_new_record --
2075 ----------------------------------
2076 FUNCTION populate_new_record (
2077 p_scc_rec IN scc_rec_type,
2078 x_scc_rec OUT NOCOPY scc_rec_type
2079 ) RETURN VARCHAR2 IS
2080 l_scc_rec scc_rec_type;
2081 l_row_notfound BOOLEAN := TRUE;
2082 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2083 BEGIN
2084
2085 IF (l_debug = 'Y') THEN
2086 okc_debug.Set_Indentation('OKC_SCC_PVT');
2087 okc_debug.log('7400: Entered populate_new_record', 2);
2088 END IF;
2089
2090 x_scc_rec := p_scc_rec;
2091 -- Get current database values
2092 l_scc_rec := get_rec(p_scc_rec, l_row_notfound);
2093 IF (l_row_notfound) THEN
2094 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2095 END IF;
2096 IF (x_scc_rec.id = OKC_API.G_MISS_NUM)
2097 THEN
2098 x_scc_rec.id := l_scc_rec.id;
2099 END IF;
2100 IF (x_scc_rec.scn_id = OKC_API.G_MISS_NUM)
2101 THEN
2102 x_scc_rec.scn_id := l_scc_rec.scn_id;
2103 END IF;
2104 IF (x_scc_rec.content_sequence = OKC_API.G_MISS_NUM)
2105 THEN
2106 x_scc_rec.content_sequence := l_scc_rec.content_sequence;
2107 END IF;
2108 IF (x_scc_rec.object_version_number = OKC_API.G_MISS_NUM)
2109 THEN
2110 x_scc_rec.object_version_number := l_scc_rec.object_version_number;
2111 END IF;
2112 IF (x_scc_rec.created_by = OKC_API.G_MISS_NUM)
2113 THEN
2114 x_scc_rec.created_by := l_scc_rec.created_by;
2115 END IF;
2116 IF (x_scc_rec.creation_date = OKC_API.G_MISS_DATE)
2117 THEN
2118 x_scc_rec.creation_date := l_scc_rec.creation_date;
2119 END IF;
2120 IF (x_scc_rec.last_updated_by = OKC_API.G_MISS_NUM)
2121 THEN
2122 x_scc_rec.last_updated_by := l_scc_rec.last_updated_by;
2123 END IF;
2124 IF (x_scc_rec.last_update_date = OKC_API.G_MISS_DATE)
2125 THEN
2126 x_scc_rec.last_update_date := l_scc_rec.last_update_date;
2127 END IF;
2128 IF (x_scc_rec.last_update_login = OKC_API.G_MISS_NUM)
2129 THEN
2130 x_scc_rec.last_update_login := l_scc_rec.last_update_login;
2131 END IF;
2132 IF (x_scc_rec.label = OKC_API.G_MISS_CHAR)
2133 THEN
2134 x_scc_rec.label := l_scc_rec.label;
2135 END IF;
2136 IF (x_scc_rec.cat_id = OKC_API.G_MISS_NUM)
2137 THEN
2138 x_scc_rec.cat_id := l_scc_rec.cat_id;
2139 END IF;
2140 IF (x_scc_rec.cle_id = OKC_API.G_MISS_NUM)
2141 THEN
2142 x_scc_rec.cle_id := l_scc_rec.cle_id;
2143 END IF;
2144 IF (x_scc_rec.sae_id = OKC_API.G_MISS_NUM)
2145 THEN
2146 x_scc_rec.sae_id := l_scc_rec.sae_id;
2147 END IF;
2148 IF (x_scc_rec.attribute_category = OKC_API.G_MISS_CHAR)
2149 THEN
2150 x_scc_rec.attribute_category := l_scc_rec.attribute_category;
2151 END IF;
2152 IF (x_scc_rec.attribute1 = OKC_API.G_MISS_CHAR)
2153 THEN
2154 x_scc_rec.attribute1 := l_scc_rec.attribute1;
2155 END IF;
2156 IF (x_scc_rec.attribute2 = OKC_API.G_MISS_CHAR)
2157 THEN
2158 x_scc_rec.attribute2 := l_scc_rec.attribute2;
2159 END IF;
2160 IF (x_scc_rec.attribute3 = OKC_API.G_MISS_CHAR)
2161 THEN
2162 x_scc_rec.attribute3 := l_scc_rec.attribute3;
2163 END IF;
2164 IF (x_scc_rec.attribute4 = OKC_API.G_MISS_CHAR)
2165 THEN
2166 x_scc_rec.attribute4 := l_scc_rec.attribute4;
2167 END IF;
2168 IF (x_scc_rec.attribute5 = OKC_API.G_MISS_CHAR)
2169 THEN
2170 x_scc_rec.attribute5 := l_scc_rec.attribute5;
2171 END IF;
2172 IF (x_scc_rec.attribute6 = OKC_API.G_MISS_CHAR)
2173 THEN
2174 x_scc_rec.attribute6 := l_scc_rec.attribute6;
2175 END IF;
2176 IF (x_scc_rec.attribute7 = OKC_API.G_MISS_CHAR)
2177 THEN
2178 x_scc_rec.attribute7 := l_scc_rec.attribute7;
2179 END IF;
2180 IF (x_scc_rec.attribute8 = OKC_API.G_MISS_CHAR)
2181 THEN
2182 x_scc_rec.attribute8 := l_scc_rec.attribute8;
2183 END IF;
2184 IF (x_scc_rec.attribute9 = OKC_API.G_MISS_CHAR)
2185 THEN
2186 x_scc_rec.attribute9 := l_scc_rec.attribute9;
2187 END IF;
2188 IF (x_scc_rec.attribute10 = OKC_API.G_MISS_CHAR)
2189 THEN
2190 x_scc_rec.attribute10 := l_scc_rec.attribute10;
2191 END IF;
2192 IF (x_scc_rec.attribute11 = OKC_API.G_MISS_CHAR)
2193 THEN
2194 x_scc_rec.attribute11 := l_scc_rec.attribute11;
2195 END IF;
2196 IF (x_scc_rec.attribute12 = OKC_API.G_MISS_CHAR)
2197 THEN
2198 x_scc_rec.attribute12 := l_scc_rec.attribute12;
2199 END IF;
2200 IF (x_scc_rec.attribute13 = OKC_API.G_MISS_CHAR)
2201 THEN
2202 x_scc_rec.attribute13 := l_scc_rec.attribute13;
2203 END IF;
2204 IF (x_scc_rec.attribute14 = OKC_API.G_MISS_CHAR)
2205 THEN
2206 x_scc_rec.attribute14 := l_scc_rec.attribute14;
2207 END IF;
2208 IF (x_scc_rec.attribute15 = OKC_API.G_MISS_CHAR)
2209 THEN
2210 x_scc_rec.attribute15 := l_scc_rec.attribute15;
2211 END IF;
2212
2213 IF (l_debug = 'Y') THEN
2214 okc_debug.log('11950: Leaving populate_new_record ', 2);
2215 okc_debug.Reset_Indentation;
2216 END IF;
2217
2218 RETURN(l_return_status);
2219
2220 END populate_new_record;
2221 ---------------------------------------------
2222 -- Set_Attributes for:OKC_SECTION_CONTENTS --
2223 ---------------------------------------------
2224 FUNCTION Set_Attributes (
2225 p_scc_rec IN scc_rec_type,
2226 x_scc_rec OUT NOCOPY scc_rec_type
2227 ) RETURN VARCHAR2 IS
2228 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2229 BEGIN
2230
2231 x_scc_rec := p_scc_rec;
2232 RETURN(l_return_status);
2233
2234 END Set_Attributes;
2235 BEGIN
2236
2237 IF (l_debug = 'Y') THEN
2238 okc_debug.Set_Indentation('OKC_SCC_PVT');
2239 okc_debug.log('7600: Entered update_row', 2);
2240 END IF;
2241
2242 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2243 p_init_msg_list,
2244 '_PVT',
2245 x_return_status);
2246 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2247 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2248 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2249 RAISE OKC_API.G_EXCEPTION_ERROR;
2250 END IF;
2251 --- Setting item attributes
2252 l_return_status := Set_Attributes(
2253 p_scc_rec, -- IN
2254 l_scc_rec); -- OUT
2255 --- If any errors happen abort API
2256 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2257 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2258 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2259 RAISE OKC_API.G_EXCEPTION_ERROR;
2260 END IF;
2261 l_return_status := populate_new_record(l_scc_rec, l_def_scc_rec);
2262 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2264 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2265 RAISE OKC_API.G_EXCEPTION_ERROR;
2266 END IF;
2267 UPDATE OKC_SECTION_CONTENTS
2268 SET SCN_ID = l_def_scc_rec.scn_id,
2269 CONTENT_SEQUENCE = l_def_scc_rec.content_sequence,
2270 OBJECT_VERSION_NUMBER = l_def_scc_rec.object_version_number,
2271 CREATED_BY = l_def_scc_rec.created_by,
2272 CREATION_DATE = l_def_scc_rec.creation_date,
2273 LAST_UPDATED_BY = l_def_scc_rec.last_updated_by,
2274 LAST_UPDATE_DATE = l_def_scc_rec.last_update_date,
2275 LAST_UPDATE_LOGIN = l_def_scc_rec.last_update_login,
2276 LABEL = l_def_scc_rec.label,
2277 CAT_ID = l_def_scc_rec.cat_id,
2278 CLE_ID = l_def_scc_rec.cle_id,
2279 SAE_ID = l_def_scc_rec.sae_id,
2280 ATTRIBUTE_CATEGORY = l_def_scc_rec.attribute_category,
2281 ATTRIBUTE1 = l_def_scc_rec.attribute1,
2282 ATTRIBUTE2 = l_def_scc_rec.attribute2,
2283 ATTRIBUTE3 = l_def_scc_rec.attribute3,
2284 ATTRIBUTE4 = l_def_scc_rec.attribute4,
2285 ATTRIBUTE5 = l_def_scc_rec.attribute5,
2286 ATTRIBUTE6 = l_def_scc_rec.attribute6,
2287 ATTRIBUTE7 = l_def_scc_rec.attribute7,
2288 ATTRIBUTE8 = l_def_scc_rec.attribute8,
2289 ATTRIBUTE9 = l_def_scc_rec.attribute9,
2290 ATTRIBUTE10 = l_def_scc_rec.attribute10,
2291 ATTRIBUTE11 = l_def_scc_rec.attribute11,
2292 ATTRIBUTE12 = l_def_scc_rec.attribute12,
2293 ATTRIBUTE13 = l_def_scc_rec.attribute13,
2294 ATTRIBUTE14 = l_def_scc_rec.attribute14,
2295 ATTRIBUTE15 = l_def_scc_rec.attribute15
2296 WHERE ID = l_def_scc_rec.id;
2297
2298 x_scc_rec := l_def_scc_rec;
2299 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2300
2301 IF (l_debug = 'Y') THEN
2302 okc_debug.log('7700: Leaving update_row', 2);
2303 okc_debug.Reset_Indentation;
2304 END IF;
2305
2306 EXCEPTION
2307 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2308
2309 IF (l_debug = 'Y') THEN
2310 okc_debug.log('7800: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2311 okc_debug.Reset_Indentation;
2312 END IF;
2313
2314 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2315 (
2316 l_api_name,
2317 G_PKG_NAME,
2318 'OKC_API.G_RET_STS_ERROR',
2319 x_msg_count,
2320 x_msg_data,
2321 '_PVT'
2322 );
2323 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2324
2325 IF (l_debug = 'Y') THEN
2326 okc_debug.log('7900: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2327 okc_debug.Reset_Indentation;
2328 END IF;
2329
2330 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2331 (
2332 l_api_name,
2333 G_PKG_NAME,
2334 'OKC_API.G_RET_STS_UNEXP_ERROR',
2335 x_msg_count,
2336 x_msg_data,
2337 '_PVT'
2338 );
2339 WHEN OTHERS THEN
2340
2341 IF (l_debug = 'Y') THEN
2342 okc_debug.log('8000: Exiting update_row:OTHERS Exception', 2);
2343 okc_debug.Reset_Indentation;
2344 END IF;
2345
2346 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2347 (
2348 l_api_name,
2349 G_PKG_NAME,
2350 'OTHERS',
2351 x_msg_count,
2352 x_msg_data,
2353 '_PVT'
2354 );
2355
2356 END update_row;
2357 -------------------------------------------
2358 -- update_row for:OKC_SECTION_CONTENTS_V --
2359 -------------------------------------------
2360 PROCEDURE update_row(
2361 p_api_version IN NUMBER,
2362 p_init_msg_list IN VARCHAR2,
2363 x_return_status OUT NOCOPY VARCHAR2,
2364 x_msg_count OUT NOCOPY NUMBER,
2365 x_msg_data OUT NOCOPY VARCHAR2,
2366 p_sccv_rec IN sccv_rec_type,
2367 x_sccv_rec OUT NOCOPY sccv_rec_type) IS
2368
2369 l_api_version CONSTANT NUMBER := 1;
2370 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2371 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2372 l_sccv_rec sccv_rec_type := p_sccv_rec;
2373 l_def_sccv_rec sccv_rec_type;
2374 l_scc_rec scc_rec_type;
2375 lx_scc_rec scc_rec_type;
2376 -------------------------------
2377 -- FUNCTION fill_who_columns --
2378 -------------------------------
2379 FUNCTION fill_who_columns (
2380 p_sccv_rec IN sccv_rec_type
2381 ) RETURN sccv_rec_type IS
2382 l_sccv_rec sccv_rec_type := p_sccv_rec;
2383 BEGIN
2384
2385 l_sccv_rec.LAST_UPDATE_DATE := SYSDATE;
2386 l_sccv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2387 l_sccv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2388 RETURN(l_sccv_rec);
2389
2390 END fill_who_columns;
2391 ----------------------------------
2392 -- FUNCTION populate_new_record --
2393 ----------------------------------
2394 FUNCTION populate_new_record (
2395 p_sccv_rec IN sccv_rec_type,
2396 x_sccv_rec OUT NOCOPY sccv_rec_type
2397 ) RETURN VARCHAR2 IS
2398 l_sccv_rec sccv_rec_type;
2399 l_row_notfound BOOLEAN := TRUE;
2400 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2401 BEGIN
2402
2403 IF (l_debug = 'Y') THEN
2404 okc_debug.Set_Indentation('OKC_SCC_PVT');
2405 okc_debug.log('8200: Entered populate_new_record', 2);
2406 END IF;
2407
2408 x_sccv_rec := p_sccv_rec;
2409 -- Get current database values
2410 l_sccv_rec := get_rec(p_sccv_rec, l_row_notfound);
2411 IF (l_row_notfound) THEN
2412 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2413 END IF;
2414 IF (x_sccv_rec.id = OKC_API.G_MISS_NUM)
2415 THEN
2416 x_sccv_rec.id := l_sccv_rec.id;
2417 END IF;
2418 IF (x_sccv_rec.scn_id = OKC_API.G_MISS_NUM)
2419 THEN
2420 x_sccv_rec.scn_id := l_sccv_rec.scn_id;
2421 END IF;
2422 IF (x_sccv_rec.label = OKC_API.G_MISS_CHAR)
2423 THEN
2424 x_sccv_rec.label := l_sccv_rec.label;
2425 END IF;
2426 IF (x_sccv_rec.cat_id = OKC_API.G_MISS_NUM)
2427 THEN
2428 x_sccv_rec.cat_id := l_sccv_rec.cat_id;
2429 END IF;
2430 IF (x_sccv_rec.cle_id = OKC_API.G_MISS_NUM)
2431 THEN
2432 x_sccv_rec.cle_id := l_sccv_rec.cle_id;
2433 END IF;
2434 IF (x_sccv_rec.sae_id = OKC_API.G_MISS_NUM)
2435 THEN
2436 x_sccv_rec.sae_id := l_sccv_rec.sae_id;
2437 END IF;
2438 IF (x_sccv_rec.content_sequence = OKC_API.G_MISS_NUM)
2439 THEN
2440 x_sccv_rec.content_sequence := l_sccv_rec.content_sequence;
2441 END IF;
2442 IF (x_sccv_rec.object_version_number = OKC_API.G_MISS_NUM)
2443 THEN
2444 x_sccv_rec.object_version_number := l_sccv_rec.object_version_number;
2445 END IF;
2446 IF (x_sccv_rec.created_by = OKC_API.G_MISS_NUM)
2447 THEN
2448 x_sccv_rec.created_by := l_sccv_rec.created_by;
2449 END IF;
2450 IF (x_sccv_rec.creation_date = OKC_API.G_MISS_DATE)
2451 THEN
2452 x_sccv_rec.creation_date := l_sccv_rec.creation_date;
2453 END IF;
2454 IF (x_sccv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2455 THEN
2456 x_sccv_rec.last_updated_by := l_sccv_rec.last_updated_by;
2457 END IF;
2458 IF (x_sccv_rec.last_update_date = OKC_API.G_MISS_DATE)
2459 THEN
2460 x_sccv_rec.last_update_date := l_sccv_rec.last_update_date;
2461 END IF;
2462 IF (x_sccv_rec.last_update_login = OKC_API.G_MISS_NUM)
2463 THEN
2464 x_sccv_rec.last_update_login := l_sccv_rec.last_update_login;
2465 END IF;
2466 IF (x_sccv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2467 THEN
2468 x_sccv_rec.attribute_category := l_sccv_rec.attribute_category;
2469 END IF;
2470 IF (x_sccv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2471 THEN
2472 x_sccv_rec.attribute1 := l_sccv_rec.attribute1;
2473 END IF;
2474 IF (x_sccv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2475 THEN
2476 x_sccv_rec.attribute2 := l_sccv_rec.attribute2;
2477 END IF;
2478 IF (x_sccv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2479 THEN
2480 x_sccv_rec.attribute3 := l_sccv_rec.attribute3;
2481 END IF;
2482 IF (x_sccv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2483 THEN
2484 x_sccv_rec.attribute4 := l_sccv_rec.attribute4;
2485 END IF;
2486 IF (x_sccv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2487 THEN
2488 x_sccv_rec.attribute5 := l_sccv_rec.attribute5;
2489 END IF;
2490 IF (x_sccv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2491 THEN
2492 x_sccv_rec.attribute6 := l_sccv_rec.attribute6;
2493 END IF;
2494 IF (x_sccv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2495 THEN
2496 x_sccv_rec.attribute7 := l_sccv_rec.attribute7;
2497 END IF;
2498 IF (x_sccv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2499 THEN
2500 x_sccv_rec.attribute8 := l_sccv_rec.attribute8;
2501 END IF;
2502 IF (x_sccv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2503 THEN
2504 x_sccv_rec.attribute9 := l_sccv_rec.attribute9;
2505 END IF;
2506 IF (x_sccv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2507 THEN
2508 x_sccv_rec.attribute10 := l_sccv_rec.attribute10;
2509 END IF;
2510 IF (x_sccv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2511 THEN
2512 x_sccv_rec.attribute11 := l_sccv_rec.attribute11;
2513 END IF;
2514 IF (x_sccv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2515 THEN
2516 x_sccv_rec.attribute12 := l_sccv_rec.attribute12;
2517 END IF;
2518 IF (x_sccv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2519 THEN
2520 x_sccv_rec.attribute13 := l_sccv_rec.attribute13;
2521 END IF;
2522 IF (x_sccv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2523 THEN
2524 x_sccv_rec.attribute14 := l_sccv_rec.attribute14;
2525 END IF;
2526 IF (x_sccv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2527 THEN
2528 x_sccv_rec.attribute15 := l_sccv_rec.attribute15;
2529 END IF;
2530
2531 IF (l_debug = 'Y') THEN
2532 okc_debug.log('11950: Leaving populate_new_record ', 2);
2533 okc_debug.Reset_Indentation;
2534 END IF;
2535
2536 RETURN(l_return_status);
2537
2538 END populate_new_record;
2539 -----------------------------------------------
2540 -- Set_Attributes for:OKC_SECTION_CONTENTS_V --
2541 -----------------------------------------------
2542 FUNCTION Set_Attributes (
2543 p_sccv_rec IN sccv_rec_type,
2544 x_sccv_rec OUT NOCOPY sccv_rec_type
2545 ) RETURN VARCHAR2 IS
2546 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2547 BEGIN
2548
2549 x_sccv_rec := p_sccv_rec;
2550 x_sccv_rec.OBJECT_VERSION_NUMBER := NVL(x_sccv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2551 RETURN(l_return_status);
2552
2553 END Set_Attributes;
2554 BEGIN
2555
2556 IF (l_debug = 'Y') THEN
2557 okc_debug.Set_Indentation('OKC_SCC_PVT');
2558 okc_debug.log('8400: Entered update_row', 2);
2559 END IF;
2560
2561 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2562 G_PKG_NAME,
2563 p_init_msg_list,
2564 l_api_version,
2565 p_api_version,
2566 '_PVT',
2567 x_return_status);
2568 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2569 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2570 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2571 RAISE OKC_API.G_EXCEPTION_ERROR;
2572 END IF;
2573 --- Setting item attributes
2574 l_return_status := Set_Attributes(
2575 p_sccv_rec, -- IN
2576 l_sccv_rec); -- OUT
2577 --- If any errors happen abort API
2578 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2580 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2581 RAISE OKC_API.G_EXCEPTION_ERROR;
2582 END IF;
2583 l_return_status := populate_new_record(l_sccv_rec, l_def_sccv_rec);
2584 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2585 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2586 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2587 RAISE OKC_API.G_EXCEPTION_ERROR;
2588 END IF;
2589 l_def_sccv_rec := fill_who_columns(l_def_sccv_rec);
2590 --- Validate all non-missing attributes (Item Level Validation)
2591 l_return_status := Validate_Attributes(l_def_sccv_rec);
2592 --- If any errors happen abort API
2593 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2594 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2595 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2596 RAISE OKC_API.G_EXCEPTION_ERROR;
2597 END IF;
2598 l_return_status := Validate_Record(l_def_sccv_rec);
2599 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2600 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2601 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2602 RAISE OKC_API.G_EXCEPTION_ERROR;
2603 END IF;
2604
2605 --------------------------------------
2606 -- Move VIEW record to "Child" records
2607 --------------------------------------
2608 migrate(l_def_sccv_rec, l_scc_rec);
2609 --------------------------------------------
2610 -- Call the UPDATE_ROW for each child record
2611 --------------------------------------------
2612 update_row(
2613 p_init_msg_list,
2614 x_return_status,
2615 x_msg_count,
2616 x_msg_data,
2617 l_scc_rec,
2618 lx_scc_rec
2619 );
2620 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2621 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2622 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2623 RAISE OKC_API.G_EXCEPTION_ERROR;
2624 END IF;
2625 migrate(lx_scc_rec, l_def_sccv_rec);
2626 x_sccv_rec := l_def_sccv_rec;
2627 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2628
2629 IF (l_debug = 'Y') THEN
2630 okc_debug.log('8500: Leaving update_row', 2);
2631 okc_debug.Reset_Indentation;
2632 END IF;
2633
2634 EXCEPTION
2635 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2636
2637 IF (l_debug = 'Y') THEN
2638 okc_debug.log('8600: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2639 okc_debug.Reset_Indentation;
2640 END IF;
2641
2642 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2643 (
2644 l_api_name,
2645 G_PKG_NAME,
2646 'OKC_API.G_RET_STS_ERROR',
2647 x_msg_count,
2648 x_msg_data,
2649 '_PVT'
2650 );
2651 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2652
2653 IF (l_debug = 'Y') THEN
2654 okc_debug.log('8700: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2655 okc_debug.Reset_Indentation;
2656 END IF;
2657
2658 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2659 (
2660 l_api_name,
2661 G_PKG_NAME,
2662 'OKC_API.G_RET_STS_UNEXP_ERROR',
2663 x_msg_count,
2664 x_msg_data,
2665 '_PVT'
2666 );
2667 WHEN OTHERS THEN
2668
2669 IF (l_debug = 'Y') THEN
2670 okc_debug.log('8800: Exiting update_row:OTHERS Exception', 2);
2671 okc_debug.Reset_Indentation;
2672 END IF;
2673
2674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2675 (
2676 l_api_name,
2677 G_PKG_NAME,
2678 'OTHERS',
2679 x_msg_count,
2680 x_msg_data,
2681 '_PVT'
2682 );
2683
2684
2685 END update_row;
2686 ----------------------------------------
2687 -- PL/SQL TBL update_row for:SCCV_TBL --
2688 ----------------------------------------
2689 PROCEDURE update_row(
2690 p_api_version IN NUMBER,
2691 p_init_msg_list IN VARCHAR2,
2692 x_return_status OUT NOCOPY VARCHAR2,
2693 x_msg_count OUT NOCOPY NUMBER,
2694 x_msg_data OUT NOCOPY VARCHAR2,
2695 p_sccv_tbl IN sccv_tbl_type,
2696 x_sccv_tbl OUT NOCOPY sccv_tbl_type) IS
2697
2698 l_api_version CONSTANT NUMBER := 1;
2699 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2700 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2701 i NUMBER := 0;
2702 BEGIN
2703
2704 IF (l_debug = 'Y') THEN
2705 okc_debug.Set_Indentation('OKC_SCC_PVT');
2706 okc_debug.log('8900: Entered update_row', 2);
2707 END IF;
2708
2709 OKC_API.init_msg_list(p_init_msg_list);
2710 -- Make sure PL/SQL table has records in it before passing
2711 IF (p_sccv_tbl.COUNT > 0) THEN
2712 i := p_sccv_tbl.FIRST;
2713 LOOP
2714 update_row (
2715 p_api_version => p_api_version,
2716 p_init_msg_list => OKC_API.G_FALSE,
2717 x_return_status => x_return_status,
2718 x_msg_count => x_msg_count,
2719 x_msg_data => x_msg_data,
2720 p_sccv_rec => p_sccv_tbl(i),
2721 x_sccv_rec => x_sccv_tbl(i));
2722 EXIT WHEN (i = p_sccv_tbl.LAST);
2723 i := p_sccv_tbl.NEXT(i);
2724 END LOOP;
2725 END IF;
2726
2727 IF (l_debug = 'Y') THEN
2728 okc_debug.log('9000: Leaving update_row', 2);
2729 okc_debug.Reset_Indentation;
2730 END IF;
2731
2732 EXCEPTION
2733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2734
2735 IF (l_debug = 'Y') THEN
2736 okc_debug.log('9100: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2737 okc_debug.Reset_Indentation;
2738 END IF;
2739
2740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2741 (
2742 l_api_name,
2743 G_PKG_NAME,
2744 'OKC_API.G_RET_STS_ERROR',
2745 x_msg_count,
2746 x_msg_data,
2747 '_PVT'
2748 );
2749 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2750
2751 IF (l_debug = 'Y') THEN
2752 okc_debug.log('9200: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2753 okc_debug.Reset_Indentation;
2754 END IF;
2755
2756 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2757 (
2758 l_api_name,
2759 G_PKG_NAME,
2760 'OKC_API.G_RET_STS_UNEXP_ERROR',
2761 x_msg_count,
2762 x_msg_data,
2763 '_PVT'
2764 );
2765 WHEN OTHERS THEN
2766
2767 IF (l_debug = 'Y') THEN
2768 okc_debug.log('9300: Exiting update_row:OTHERS Exception', 2);
2769 okc_debug.Reset_Indentation;
2770 END IF;
2771
2772 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2773 (
2774 l_api_name,
2775 G_PKG_NAME,
2776 'OTHERS',
2777 x_msg_count,
2778 x_msg_data,
2779 '_PVT'
2780 );
2781
2782 END update_row;
2783
2784 ---------------------------------------------------------------------------
2785 -- PROCEDURE delete_row
2786 ---------------------------------------------------------------------------
2787 -----------------------------------------
2788 -- delete_row for:OKC_SECTION_CONTENTS --
2789 -----------------------------------------
2790 PROCEDURE delete_row(
2791 p_init_msg_list IN VARCHAR2,
2792 x_return_status OUT NOCOPY VARCHAR2,
2793 x_msg_count OUT NOCOPY NUMBER,
2794 x_msg_data OUT NOCOPY VARCHAR2,
2795 p_scc_rec IN scc_rec_type) IS
2796
2797 l_api_version CONSTANT NUMBER := 1;
2798 l_api_name CONSTANT VARCHAR2(30) := 'CONTENTS_delete_row';
2799 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2800 l_scc_rec scc_rec_type:= p_scc_rec;
2801 l_row_notfound BOOLEAN := TRUE;
2802 BEGIN
2803
2804 IF (l_debug = 'Y') THEN
2805 okc_debug.Set_Indentation('OKC_SCC_PVT');
2806 okc_debug.log('9400: Entered delete_row', 2);
2807 END IF;
2808
2809 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2810 p_init_msg_list,
2811 '_PVT',
2812 x_return_status);
2813 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2814 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2815 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2816 RAISE OKC_API.G_EXCEPTION_ERROR;
2817 END IF;
2818 DELETE FROM OKC_SECTION_CONTENTS
2819 WHERE ID = l_scc_rec.id;
2820
2821 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2822
2823 IF (l_debug = 'Y') THEN
2824 okc_debug.log('9500: Leaving delete_row', 2);
2825 okc_debug.Reset_Indentation;
2826 END IF;
2827
2828 EXCEPTION
2829 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2830
2831 IF (l_debug = 'Y') THEN
2832 okc_debug.log('9600: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2833 okc_debug.Reset_Indentation;
2834 END IF;
2835
2836 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2837 (
2838 l_api_name,
2839 G_PKG_NAME,
2840 'OKC_API.G_RET_STS_ERROR',
2841 x_msg_count,
2842 x_msg_data,
2843 '_PVT'
2844 );
2845 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2846
2847 IF (l_debug = 'Y') THEN
2848 okc_debug.log('9700: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2849 okc_debug.Reset_Indentation;
2850 END IF;
2851
2852 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2853 (
2854 l_api_name,
2855 G_PKG_NAME,
2856 'OKC_API.G_RET_STS_UNEXP_ERROR',
2857 x_msg_count,
2858 x_msg_data,
2859 '_PVT'
2860 );
2861 WHEN OTHERS THEN
2862
2863 IF (l_debug = 'Y') THEN
2864 okc_debug.log('9800: Exiting delete_row:OTHERS Exception', 2);
2865 okc_debug.Reset_Indentation;
2866 END IF;
2867
2868 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2869 (
2870 l_api_name,
2871 G_PKG_NAME,
2872 'OTHERS',
2873 x_msg_count,
2874 x_msg_data,
2875 '_PVT'
2876 );
2877
2878 END delete_row;
2879 -------------------------------------------
2880 -- delete_row for:OKC_SECTION_CONTENTS_V --
2881 -------------------------------------------
2882 PROCEDURE delete_row(
2883 p_api_version IN NUMBER,
2884 p_init_msg_list IN VARCHAR2,
2885 x_return_status OUT NOCOPY VARCHAR2,
2886 x_msg_count OUT NOCOPY NUMBER,
2887 x_msg_data OUT NOCOPY VARCHAR2,
2888 p_sccv_rec IN sccv_rec_type) IS
2889
2890 l_api_version CONSTANT NUMBER := 1;
2891 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2892 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2893 l_sccv_rec sccv_rec_type := p_sccv_rec;
2894 l_scc_rec scc_rec_type;
2895 BEGIN
2896
2897 IF (l_debug = 'Y') THEN
2898 okc_debug.Set_Indentation('OKC_SCC_PVT');
2899 okc_debug.log('9900: Entered delete_row', 2);
2900 END IF;
2901
2902 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2903 G_PKG_NAME,
2904 p_init_msg_list,
2905 l_api_version,
2906 p_api_version,
2907 '_PVT',
2908 x_return_status);
2909 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2911 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2912 RAISE OKC_API.G_EXCEPTION_ERROR;
2913 END IF;
2914 --------------------------------------
2915 -- Move VIEW record to "Child" records
2916 --------------------------------------
2917 migrate(l_sccv_rec, l_scc_rec);
2918 --------------------------------------------
2919 -- Call the DELETE_ROW for each child record
2920 --------------------------------------------
2921 delete_row(
2922 p_init_msg_list,
2923 x_return_status,
2924 x_msg_count,
2925 x_msg_data,
2926 l_scc_rec
2927 );
2928 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2929 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2930 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2931 RAISE OKC_API.G_EXCEPTION_ERROR;
2932 END IF;
2933 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2934
2935 IF (l_debug = 'Y') THEN
2936 okc_debug.log('10000: Leaving delete_row', 2);
2937 okc_debug.Reset_Indentation;
2938 END IF;
2939
2940 EXCEPTION
2941 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2942
2943 IF (l_debug = 'Y') THEN
2944 okc_debug.log('10100: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2945 okc_debug.Reset_Indentation;
2946 END IF;
2947
2948 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2949 (
2950 l_api_name,
2951 G_PKG_NAME,
2952 'OKC_API.G_RET_STS_ERROR',
2953 x_msg_count,
2954 x_msg_data,
2955 '_PVT'
2956 );
2957 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2958
2959 IF (l_debug = 'Y') THEN
2960 okc_debug.log('10200: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2961 okc_debug.Reset_Indentation;
2962 END IF;
2963
2964 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2965 (
2966 l_api_name,
2967 G_PKG_NAME,
2968 'OKC_API.G_RET_STS_UNEXP_ERROR',
2969 x_msg_count,
2970 x_msg_data,
2971 '_PVT'
2972 );
2973 WHEN OTHERS THEN
2974
2975 IF (l_debug = 'Y') THEN
2976 okc_debug.log('10300: Exiting delete_row:OTHERS Exception', 2);
2977 okc_debug.Reset_Indentation;
2978 END IF;
2979
2980 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2981 (
2982 l_api_name,
2983 G_PKG_NAME,
2984 'OTHERS',
2985 x_msg_count,
2986 x_msg_data,
2987 '_PVT'
2988 );
2989
2990 END delete_row;
2991 ----------------------------------------
2992 -- PL/SQL TBL delete_row for:SCCV_TBL --
2993 ----------------------------------------
2994 PROCEDURE delete_row(
2995 p_api_version IN NUMBER,
2996 p_init_msg_list IN VARCHAR2,
2997 x_return_status OUT NOCOPY VARCHAR2,
2998 x_msg_count OUT NOCOPY NUMBER,
2999 x_msg_data OUT NOCOPY VARCHAR2,
3000 p_sccv_tbl IN sccv_tbl_type) IS
3001
3002 l_api_version CONSTANT NUMBER := 1;
3003 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3004 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3005 i NUMBER := 0;
3006 BEGIN
3007
3008 IF (l_debug = 'Y') THEN
3009 okc_debug.Set_Indentation('OKC_SCC_PVT');
3010 okc_debug.log('10400: Entered delete_row', 2);
3011 END IF;
3012
3013 OKC_API.init_msg_list(p_init_msg_list);
3014 -- Make sure PL/SQL table has records in it before passing
3015 IF (p_sccv_tbl.COUNT > 0) THEN
3016 i := p_sccv_tbl.FIRST;
3017 LOOP
3018 delete_row (
3019 p_api_version => p_api_version,
3020 p_init_msg_list => OKC_API.G_FALSE,
3021 x_return_status => x_return_status,
3022 x_msg_count => x_msg_count,
3023 x_msg_data => x_msg_data,
3024 p_sccv_rec => p_sccv_tbl(i));
3025 EXIT WHEN (i = p_sccv_tbl.LAST);
3026 i := p_sccv_tbl.NEXT(i);
3027 END LOOP;
3028 END IF;
3029
3030 IF (l_debug = 'Y') THEN
3031 okc_debug.log('10500: Leaving delete_row', 2);
3032 okc_debug.Reset_Indentation;
3033 END IF;
3034
3035 EXCEPTION
3036 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3037
3038 IF (l_debug = 'Y') THEN
3039 okc_debug.log('10600: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3040 okc_debug.Reset_Indentation;
3041 END IF;
3042
3043 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3044 (
3045 l_api_name,
3046 G_PKG_NAME,
3047 'OKC_API.G_RET_STS_ERROR',
3048 x_msg_count,
3049 x_msg_data,
3050 '_PVT'
3051 );
3052 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3053
3054 IF (l_debug = 'Y') THEN
3055 okc_debug.log('10700: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3056 okc_debug.Reset_Indentation;
3057 END IF;
3058
3059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3060 (
3061 l_api_name,
3062 G_PKG_NAME,
3063 'OKC_API.G_RET_STS_UNEXP_ERROR',
3064 x_msg_count,
3065 x_msg_data,
3066 '_PVT'
3067 );
3068 WHEN OTHERS THEN
3069
3070 IF (l_debug = 'Y') THEN
3071 okc_debug.log('10800: Exiting delete_row:OTHERS Exception', 2);
3072 okc_debug.Reset_Indentation;
3073 END IF;
3074
3075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3076 (
3077 l_api_name,
3078 G_PKG_NAME,
3079 'OTHERS',
3080 x_msg_count,
3081 x_msg_data,
3082 '_PVT'
3083 );
3084
3085 END delete_row;
3086
3087 --This function is called from versioning API OKC_VERSION_PVT
3088 --Old Location: OKCRVERB.pls
3089 --New Location: Base Table API
3090
3091 FUNCTION create_version(
3092 p_chr_id IN NUMBER,
3093 p_major_version IN NUMBER
3094 ) RETURN VARCHAR2 IS
3095
3096 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3097
3098 BEGIN
3099
3100 IF (l_debug = 'Y') THEN
3101 okc_debug.Set_Indentation('OKC_SCC_PVT');
3102 okc_debug.log('10900: Entered create_version', 2);
3103 END IF;
3104
3105 INSERT INTO okc_section_contents_h
3106 (
3107 major_version,
3108 id,
3109 scn_id,
3110 content_sequence,
3111 object_version_number,
3112 created_by,
3113 creation_date,
3114 last_updated_by,
3115 last_update_date,
3116 last_update_login,
3117 label,
3118 cat_id,
3119 cle_id,
3120 sae_id,
3121 attribute_category,
3122 attribute1,
3123 attribute2,
3124 attribute3,
3125 attribute4,
3126 attribute5,
3127 attribute6,
3128 attribute7,
3129 attribute8,
3130 attribute9,
3131 attribute10,
3132 attribute11,
3133 attribute12,
3134 attribute13,
3135 attribute14,
3136 attribute15
3137 )
3138 SELECT
3139 p_major_version,
3140 id,
3141 scn_id,
3142 content_sequence,
3143 object_version_number,
3144 created_by,
3145 creation_date,
3146 last_updated_by,
3147 last_update_date,
3148 last_update_login,
3149 label,
3150 cat_id,
3151 cle_id,
3152 sae_id,
3153 attribute_category,
3154 attribute1,
3155 attribute2,
3156 attribute3,
3157 attribute4,
3158 attribute5,
3159 attribute6,
3160 attribute7,
3161 attribute8,
3162 attribute9,
3163 attribute10,
3164 attribute11,
3165 attribute12,
3166 attribute13,
3167 attribute14,
3168 attribute15
3169 FROM okc_section_contents
3170 WHERE scn_id in (SELECT id
3171 FROM okc_sections_b
3172 WHERE CHR_ID=p_chr_id);
3173
3174 IF (l_debug = 'Y') THEN
3175 okc_debug.log('11000: Leaving create_version', 2);
3176 okc_debug.Reset_Indentation;
3177 END IF;
3178 RETURN l_return_status;
3179
3180 EXCEPTION
3181 -- other appropriate handlers
3182 WHEN OTHERS THEN
3183
3184 IF (l_debug = 'Y') THEN
3185 okc_debug.log('11100: Exiting create_version:OTHERS Exception', 2);
3186 okc_debug.Reset_Indentation;
3187 END IF;
3188
3189 -- store SQL error message on message stack
3190 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3191 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3192 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3193 p_token1_value => sqlcode,
3194 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3195 p_token2_value => sqlerrm);
3196
3197 -- notify UNEXPECTED error
3198 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3199 return l_return_status;
3200
3201 IF (l_debug = 'Y') THEN
3202 okc_debug.log('11000: Leaving create_version', 2);
3203 okc_debug.Reset_Indentation;
3204 END IF;
3205 END create_version;
3206
3207 -----
3208 --
3209 ----
3210
3211 --This Function is called from Versioning API OKC_VERSION_PVT
3212 --Old Location:OKCRVERB.pls
3213 --New Location:Base Table API
3214
3215
3216 FUNCTION restore_version(
3217 p_chr_id IN NUMBER,
3218 p_major_version IN NUMBER
3219 ) RETURN VARCHAR2 IS
3220
3221
3222 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3223
3224 BEGIN
3225
3226 IF (l_debug = 'Y') THEN
3227 okc_debug.Set_Indentation('OKC_SCC_PVT');
3228 okc_debug.log('11200: Entered restore_version', 2);
3229 END IF;
3230
3231 INSERT INTO okc_section_contents
3232 (
3233 id,
3234 scn_id,
3235 content_sequence,
3236 object_version_number,
3237 created_by,
3238 creation_date,
3239 last_updated_by,
3240 last_update_date,
3241 last_update_login,
3242 label,
3243 cat_id,
3244 cle_id,
3245 sae_id,
3246 attribute_category,
3247 attribute1,
3248 attribute2,
3249 attribute3,
3250 attribute4,
3251 attribute5,
3252 attribute6,
3253 attribute7,
3254 attribute8,
3255 attribute9,
3256 attribute10,
3257 attribute11,
3258 attribute12,
3259 attribute13,
3260 attribute14,
3261 attribute15
3262 )
3263 SELECT
3264 id,
3265 scn_id,
3266 content_sequence,
3267 object_version_number,
3268 created_by,
3269 creation_date,
3270 last_updated_by,
3271 last_update_date,
3272 last_update_login,
3273 label,
3274 cat_id,
3275 cle_id,
3276 sae_id,
3277 attribute_category,
3278 attribute1,
3279 attribute2,
3280 attribute3,
3281 attribute4,
3282 attribute5,
3283 attribute6,
3284 attribute7,
3285 attribute8,
3286 attribute9,
3287 attribute10,
3288 attribute11,
3289 attribute12,
3290 attribute13,
3291 attribute14,
3292 attribute15
3293 FROM okc_section_contents_h
3294 WHERE scn_id in (SELECT id
3295 FROM okc_sections_bh
3296 WHERE chr_id = p_chr_id)
3297 AND major_version = p_major_version;
3298
3299 IF (l_debug = 'Y') THEN
3300 okc_debug.log('11300: Leaving restore_version', 2);
3301 okc_debug.Reset_Indentation;
3302 END IF;
3303 RETURN l_return_status;
3304
3305 EXCEPTION
3306 -- other appropriate handlers
3307 WHEN OTHERS THEN
3308
3309 IF (l_debug = 'Y') THEN
3310 okc_debug.log('11400: Exiting restore_version:OTHERS Exception', 2);
3311 okc_debug.Reset_Indentation;
3312 END IF;
3313
3314 -- store SQL error message on message stack
3315 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3316 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3317 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3318 p_token1_value => sqlcode,
3319 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3320 p_token2_value => sqlerrm);
3321
3322 -- notify UNEXPECTED error
3323 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3324 return l_return_status;
3325
3326 IF (l_debug = 'Y') THEN
3327 okc_debug.log('11300: Leaving restore_version', 2);
3328 okc_debug.Reset_Indentation;
3329 END IF;
3330 END restore_version;
3331
3332 END OKC_SCC_PVT;