[Home] [Help]
PACKAGE BODY: APPS.OKC_SCN_PVT
Source
1 PACKAGE BODY OKC_SCN_PVT AS
2 /* $Header: OKCSSCNB.pls 120.0 2005/05/25 22:50:29 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /************************ HAND-CODED *********************************/
7 FUNCTION Validate_Attributes (p_scnv_rec in scnv_rec_type) RETURN VARCHAR2;
8
9 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
10
11 -- Start of comments
12 --
13 -- Procedure Name : validate_scn_type
14 -- Description :
15 -- Business Rules :
16 -- Parameters :
17 -- Version : 1.0
18 -- End of comments
19 PROCEDURE validate_scn_type(x_return_status OUT NOCOPY VARCHAR2,
20 p_scnv_rec IN scnv_rec_type) is
21
22 Begin
23
24 IF (l_debug = 'Y') THEN
25 okc_debug.Set_Indentation('OKC_SCN_PVT');
26 okc_debug.log('100: Entered validate_scn_type', 2);
27 END IF;
28
29 -- initialize return status
30 x_return_status := OKC_API.G_RET_STS_SUCCESS;
31
32 -- check that data exists
33 If (p_scnv_rec.scn_type = OKC_API.G_MISS_CHAR or
34 p_scnv_rec.scn_type IS NULL)
35 Then
36 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
37 p_msg_name => g_required_value,
38 p_token1 => g_col_name_token,
39 p_token1_value => 'Section Type');
40 -- notify caller of an error
41 x_return_status := OKC_API.G_RET_STS_ERROR;
42
43 -- halt validation
44 raise G_EXCEPTION_HALT_VALIDATION;
45 End If;
46
47 -- check allowed values
48 If (upper(p_scnv_rec.scn_type) NOT IN ('CHR','SAT')) Then
49 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
50 p_msg_name => g_invalid_value,
51 p_token1 => g_col_name_token,
52 p_token1_value => 'Section Type');
53 -- notify caller of an error
54 x_return_status := OKC_API.G_RET_STS_ERROR;
55 End If;
56
57
58 IF (l_debug = 'Y') THEN
59 okc_debug.log('200: Leaving validate_scn_type', 2);
60 okc_debug.Reset_Indentation;
61 END IF;
62
63 exception
64 when G_EXCEPTION_HALT_VALIDATION then
65
66 IF (l_debug = 'Y') THEN
67 okc_debug.log('300: Exiting validate_scn_type:G_EXCEPTION_HALT_VALIDATION Exception', 2);
68 okc_debug.Reset_Indentation;
69 END IF;
70
71 -- no processing necessary; validation can continue with next column
72 null;
73
74 when OTHERS then
75
76 IF (l_debug = 'Y') THEN
77 okc_debug.log('400: Exiting validate_scn_type:OTHERS Exception', 2);
78 okc_debug.Reset_Indentation;
79 END IF;
80
81 -- store SQL error message on message stack
82 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
83 p_msg_name => g_unexpected_error,
84 p_token1 => g_sqlcode_token,
85 p_token1_value => sqlcode,
86 p_token2 => g_sqlerrm_token,
87 p_token2_value => sqlerrm);
88 -- notify caller of an error as UNEXPETED error
89 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
90
91 end validate_scn_type;
92
93 -- Start of comments
94 --
95 -- Procedure Name : validate_chr_id
96 -- Description :
97 -- Business Rules :
98 -- Parameters :
99 -- Version : 1.0
100 -- End of comments
101 PROCEDURE validate_chr_id(x_return_status OUT NOCOPY VARCHAR2,
102 p_scnv_rec IN scnv_rec_type) is
103
104 l_dummy_var VARCHAR2(1) := '?';
105 Cursor l_chr_csr Is
106 select 'x'
107 from OKC_K_HEADERS_B
108 where id = p_scnv_rec.chr_id;
109
110 Begin
111
112 IF (l_debug = 'Y') THEN
113 okc_debug.Set_Indentation('OKC_SCN_PVT');
114 okc_debug.log('500: Entered validate_chr_id', 2);
115 END IF;
116
117 -- initialize return status
118 x_return_status := OKC_API.G_RET_STS_SUCCESS;
119
120 --
121 -- enforce foreign key (chr_id is logically optional
122 -- mutually exclusive with SAT_CODE)
123 --
124 If (p_scnv_rec.scn_type = 'CHR') Then
125 Open l_chr_csr;
126 Fetch l_chr_csr Into l_dummy_var;
127 Close l_chr_csr;
128 -- if l_dummy_var still set to default, data was not found
129 If (l_dummy_var = '?') Then
130 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
131 p_msg_name => g_no_parent_record,
132 p_token1 => g_col_name_token,
133 p_token1_value => 'Header ID',
134 p_token2 => g_child_table_token,
135 p_token2_value => 'OKC_K_HEADERS_B',
136 p_token3 => g_parent_table_token,
137 p_token3_value => G_VIEW);
138 -- notify caller of an error
139 x_return_status := OKC_API.G_RET_STS_ERROR;
140 End If;
141 End If;
142
143 IF (l_debug = 'Y') THEN
144 okc_debug.log('600: Leaving validate_chr_id', 2);
145 okc_debug.Reset_Indentation;
146 END IF;
147 exception
148 when OTHERS then
149
150 IF (l_debug = 'Y') THEN
151 okc_debug.log('700: Exiting validate_chr_id:OTHERS Exception', 2);
152 okc_debug.Reset_Indentation;
153 END IF;
154
155 -- store SQL error message on message stack
156 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
157 p_msg_name => g_unexpected_error,
158 p_token1 => g_sqlcode_token,
159 p_token1_value => sqlcode,
160 p_token2 => g_sqlerrm_token,
161 p_token2_value => sqlerrm);
162 -- notify caller of an error as UNEXPETED error
163 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
164
165 -- verify that cursor was closed
166 if l_chr_csr%ISOPEN then
167 close l_chr_csr;
168 end if;
169
170 End validate_chr_id;
171
172 -- Start of comments
173 --
174 -- Procedure Name : validate_sat_code
175 -- Description :
176 -- Business Rules :
177 -- Parameters :
178 -- Version : 1.0
179 -- End of comments
180
181 PROCEDURE validate_sat_code(
182 x_return_status OUT NOCOPY VARCHAR2,
183 p_scnv_rec IN scnv_rec_type
184 ) IS
185 l_dummy_var VARCHAR2(1) := '?';
186 CURSOR l_satv_csr IS
187 SELECT 'x'
188 FROM FND_LOOKUP_VALUES satv
189 WHERE satv.LOOKUP_CODE = p_scnv_rec.sat_code
190 AND satv.lookup_type = 'OKC_ARTICLE_SET';
191 BEGIN
192
193 IF (l_debug = 'Y') THEN
194 okc_debug.Set_Indentation('OKC_SCN_PVT');
195 okc_debug.log('800: Entered validate_sat_code', 2);
196 END IF;
197
198 -- initialize return status
199 x_return_status := OKC_API.G_RET_STS_SUCCESS;
200
201 -- data is required if scn_type = 'SAT'
202 If (p_scnv_rec.scn_type = 'SAT') Then
203 IF (p_scnv_rec.sat_code = OKC_API.G_MISS_CHAR OR
204 p_scnv_rec.sat_code IS NULL) THEN
205 OKC_API.set_message(
206 p_app_name => G_APP_NAME,
207 p_msg_name => G_REQUIRED_VALUE,
208 p_token1 => G_COL_NAME_TOKEN,
209 p_token1_value => 'rgd_code');
210
211 -- notify caller of an error
212 x_return_status := OKC_API.G_RET_STS_ERROR;
213
214 -- halt validation
215 RAISE G_EXCEPTION_HALT_VALIDATION;
216 END IF;
217
218 -- enforce foreign key
219 OPEN l_satv_csr;
220 FETCH l_satv_csr INTO l_dummy_var;
221 CLOSE l_satv_csr;
222
223 -- if l_dummy_var still set to default, data was not found
224 IF (l_dummy_var = '?') THEN
225 --set error message in message stack
226 OKC_API.set_message(
227 p_app_name => G_APP_NAME,
228 p_msg_name => G_INVALID_VALUE,
229 p_token1 => G_COL_NAME_TOKEN,
230 p_token1_value => 'rgd_code');
231
232 -- notify caller of an error
233 x_return_status := OKC_API.G_RET_STS_ERROR;
234 END IF;
235 END IF;
236
237 IF (l_debug = 'Y') THEN
238 okc_debug.log('900: Leaving validate_sat_code', 2);
239 okc_debug.Reset_Indentation;
240 END IF;
241
242 EXCEPTION
243 WHEN G_EXCEPTION_HALT_VALIDATION THEN
244
245 IF (l_debug = 'Y') THEN
246 okc_debug.log('1000: Exiting validate_sat_code:G_EXCEPTION_HALT_VALIDATION Exception', 2);
247 okc_debug.Reset_Indentation;
248 END IF;
249
250 -- no processing necessary; validation can continue with next column
251 NULL;
252 WHEN OTHERS THEN
253
254 IF (l_debug = 'Y') THEN
255 okc_debug.log('1100: Exiting validate_sat_code:OTHERS Exception', 2);
256 okc_debug.Reset_Indentation;
257 END IF;
258
259 -- store SQL error message on message stack
260 OKC_API.SET_MESSAGE(
261 p_app_name => g_app_name,
262 p_msg_name => g_unexpected_error,
263 p_token1 => g_sqlcode_token,
264 p_token1_value => sqlcode,
265 p_token2 => g_sqlerrm_token,
266 p_token2_value => sqlerrm);
267 -- notify caller of an error as UNEXPETED error
268 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
269 -- verify that cursor was closed
270 IF l_satv_csr%ISOPEN THEN
271 CLOSE l_satv_csr;
272 END IF;
273
274 END validate_sat_code;
275
276 -- Start of comments
277 --
278 -- Procedure Name : validate_section_sequence
279 -- Description :
280 -- Business Rules :
281 -- Parameters :
282 -- Version : 1.0
283 -- End of comments
284 PROCEDURE validate_section_sequence(x_return_status OUT NOCOPY VARCHAR2,
285 p_scnv_rec IN scnv_rec_type) is
286
287 Begin
288
289 IF (l_debug = 'Y') THEN
290 okc_debug.Set_Indentation('OKC_SCN_PVT');
291 okc_debug.log('1200: Entered validate_section_sequence', 2);
292 END IF;
293
294 -- initialize return status
295 x_return_status := OKC_API.G_RET_STS_SUCCESS;
296
297 If (p_scnv_rec.section_sequence = OKC_API.G_MISS_NUM or
298 p_scnv_rec.section_sequence IS NULL)
299 Then
300 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
301 p_msg_name => g_required_value,
302 p_token1 => g_col_name_token,
303 p_token1_value => 'section_sequence');
304 -- set error flag
305 x_return_status := OKC_API.G_RET_STS_ERROR;
306
307 -- halt validation
308 raise G_EXCEPTION_HALT_VALIDATION;
309 End If;
310
311 IF (l_debug = 'Y') THEN
312 okc_debug.log('1300: Leaving validate_section_sequence', 2);
313 okc_debug.Reset_Indentation;
314 END IF;
315 exception
316 when OTHERS then
317
318 IF (l_debug = 'Y') THEN
319 okc_debug.log('1400: Exiting validate_section_sequence:OTHERS Exception', 2);
320 okc_debug.Reset_Indentation;
321 END IF;
322
323 -- store SQL error message on message stack
324 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
325 p_msg_name => g_unexpected_error,
326 p_token1 => g_sqlcode_token,
327 p_token1_value => sqlcode,
328 p_token2 => g_sqlerrm_token,
329 p_token2_value => sqlerrm);
330 -- notify caller of an error as UNEXPETED error
331 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
332
333 End validate_section_sequence;
334
335 -- Start of comments
336 --
337 -- Procedure Name : validate_sfwt_flag
338 -- Description :
339 -- Business Rules :
340 -- Parameters :
341 -- Version : 1.0
342 -- End of comments
343 PROCEDURE validate_sfwt_flag(x_return_status OUT NOCOPY VARCHAR2,
344 p_scnv_rec IN scnv_rec_type) is
345
346 Begin
347
348 IF (l_debug = 'Y') THEN
349 okc_debug.Set_Indentation('OKC_SCN_PVT');
350 okc_debug.log('1500: Entered validate_sfwt_flag', 2);
351 END IF;
352
353 -- initialize return status
354 x_return_status := OKC_API.G_RET_STS_SUCCESS;
355
356 -- check that data exists
357 If (p_scnv_rec.sfwt_flag = OKC_API.G_MISS_CHAR or
358 p_scnv_rec.sfwt_flag IS NULL)
359 Then
360 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
361 p_msg_name => g_required_value,
362 p_token1 => g_col_name_token,
363 p_token1_value => 'sfwt_flag');
364 -- notify caller of an error
365 x_return_status := OKC_API.G_RET_STS_ERROR;
366
367 -- halt validation
368 raise G_EXCEPTION_HALT_VALIDATION;
369 End If;
370
371 -- check allowed values
372 If (upper(p_scnv_rec.sfwt_flag) NOT IN ('Y','N')) Then
373 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
374 p_msg_name => g_invalid_value,
375 p_token1 => g_col_name_token,
376 p_token1_value => 'sfwt_flag');
377 -- notify caller of an error
378 x_return_status := OKC_API.G_RET_STS_ERROR;
379 End If;
380
381 IF (l_debug = 'Y') THEN
382 okc_debug.log('1600: Leaving validate_sfwt_flag', 2);
383 okc_debug.Reset_Indentation;
384 END IF;
385
386 exception
387 when G_EXCEPTION_HALT_VALIDATION then
388
389 IF (l_debug = 'Y') THEN
390 okc_debug.log('1700: Exiting validate_sfwt_flag:G_EXCEPTION_HALT_VALIDATION Exception', 2);
391 okc_debug.Reset_Indentation;
392 END IF;
393
394 -- no processing necessary; validation can continue with next column
395 null;
396
397 when OTHERS then
398
399 IF (l_debug = 'Y') THEN
400 okc_debug.log('1800: Exiting validate_sfwt_flag:OTHERS Exception', 2);
401 okc_debug.Reset_Indentation;
402 END IF;
403
404 -- store SQL error message on message stack
405 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
406 p_msg_name => g_unexpected_error,
407 p_token1 => g_sqlcode_token,
408 p_token1_value => sqlcode,
409 p_token2 => g_sqlerrm_token,
410 p_token2_value => sqlerrm);
411 -- notify caller of an error as UNEXPETED error
412 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
413
414 end validate_sfwt_flag;
415
416 -- Start of comments
417 --
418 -- Procedure Name : validate_scn_id
419 -- Description :
420 -- Business Rules :
421 -- Parameters :
422 -- Version : 1.0
423 -- End of comments
424 PROCEDURE validate_scn_id(x_return_status OUT NOCOPY VARCHAR2,
425 p_scnv_rec IN scnv_rec_type) is
426
427 l_dummy_var VARCHAR2(1) := '?';
428 Cursor l_scnv_csr Is
429 select 'x'
430 from OKC_SECTIONS_B
431 where id = p_scnv_rec.scn_id;
432
433 Begin
434
435 IF (l_debug = 'Y') THEN
436 okc_debug.Set_Indentation('OKC_SCN_PVT');
437 okc_debug.log('1900: Entered validate_scn_id', 2);
438 END IF;
439
440 -- initialize return status
441 x_return_status := OKC_API.G_RET_STS_SUCCESS;
442
443 -- enforce foreign key (scn_id is optional)
444 If (p_scnv_rec.scn_id <> OKC_API.G_MISS_NUM and
445 p_scnv_rec.scn_id IS NOT NULL)
446 Then
447 Open l_scnv_csr;
448 Fetch l_scnv_csr Into l_dummy_var;
449 Close l_scnv_csr;
450 -- if l_dummy_var still set to default, data was not found
451 If (l_dummy_var = '?') Then
452 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
453 p_msg_name => g_no_parent_record,
454 p_token1 => g_col_name_token,
455 p_token1_value => 'scn_id',
456 p_token2 => g_child_table_token,
457 p_token2_value => G_VIEW,
458 p_token3 => g_parent_table_token,
459 p_token3_value => G_VIEW);
460 -- notify caller of an error
461 x_return_status := OKC_API.G_RET_STS_ERROR;
462 End If;
463 End If;
464
465 IF (l_debug = 'Y') THEN
466 okc_debug.log('2000: Leaving validate_scn_id', 2);
467 okc_debug.Reset_Indentation;
468 END IF;
469
470 exception
471 when OTHERS then
472
473 IF (l_debug = 'Y') THEN
474 okc_debug.log('2100: Exiting validate_scn_id:OTHERS Exception', 2);
475 okc_debug.Reset_Indentation;
476 END IF;
477
478 -- store SQL error message on message stack
479 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
480 p_msg_name => g_unexpected_error,
481 p_token1 => g_sqlcode_token,
482 p_token1_value => sqlcode,
483 p_token2 => g_sqlerrm_token,
484 p_token2_value => sqlerrm);
485 -- notify caller of an error as UNEXPETED error
486 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
487
488 -- verify that cursor was closed
489 if l_scnv_csr%ISOPEN then
490 close l_scnv_csr;
491 end if;
492
493 End validate_scn_id;
494
495 /*********************** END HAND-CODED ******************************/
496 ---------------------------------------------------------------------------
497 -- FUNCTION get_seq_id
498 ---------------------------------------------------------------------------
499 FUNCTION get_seq_id RETURN NUMBER IS
500 BEGIN
501
502 RETURN(okc_p_util.raw_to_number(sys_guid()));
503
504 END get_seq_id;
505
506 ---------------------------------------------------------------------------
507 -- PROCEDURE add_language
508 ---------------------------------------------------------------------------
509 PROCEDURE add_language IS
510 BEGIN
511
512 IF (l_debug = 'Y') THEN
513 okc_debug.Set_Indentation('OKC_SCN_PVT');
514 okc_debug.log('2300: Entered add_language', 2);
515 END IF;
516
517 DELETE FROM OKC_SECTIONS_TL T
518 WHERE NOT EXISTS (
519 SELECT NULL
520 FROM OKC_SECTIONS_B B
521 WHERE B.ID = T.ID
522 );
523
524 UPDATE OKC_SECTIONS_TL T SET (
525 HEADING) = (SELECT
526 B.HEADING
527 FROM OKC_SECTIONS_TL B
528 WHERE B.ID = T.ID
529 AND B.LANGUAGE = T.SOURCE_LANG)
530 WHERE (
531 T.ID,
532 T.LANGUAGE)
533 IN (SELECT
534 SUBT.ID,
535 SUBT.LANGUAGE
536 FROM OKC_SECTIONS_TL SUBB, OKC_SECTIONS_TL SUBT
537 WHERE SUBB.ID = SUBT.ID
538 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
539 AND (SUBB.HEADING <> SUBT.HEADING
540 OR (SUBB.HEADING IS NULL AND SUBT.HEADING IS NOT NULL)
541 OR (SUBB.HEADING IS NOT NULL AND SUBT.HEADING IS NULL)
542 ));
543
544 INSERT INTO OKC_SECTIONS_TL (
545 ID,
546 LANGUAGE,
547 SOURCE_LANG,
548 SFWT_FLAG,
549 HEADING,
550 CREATED_BY,
551 CREATION_DATE,
552 LAST_UPDATED_BY,
553 LAST_UPDATE_DATE,
554 LAST_UPDATE_LOGIN)
555 SELECT
556 B.ID,
557 L.LANGUAGE_CODE,
558 B.SOURCE_LANG,
559 B.SFWT_FLAG,
560 B.HEADING,
561 B.CREATED_BY,
562 B.CREATION_DATE,
563 B.LAST_UPDATED_BY,
564 B.LAST_UPDATE_DATE,
565 B.LAST_UPDATE_LOGIN
566 FROM OKC_SECTIONS_TL B, FND_LANGUAGES L
567 WHERE L.INSTALLED_FLAG IN ('I', 'B')
568 AND B.LANGUAGE = USERENV('LANG')
569 AND NOT EXISTS(
570 SELECT NULL
571 FROM OKC_SECTIONS_TL T
572 WHERE T.ID = B.ID
573 AND T.LANGUAGE = L.LANGUAGE_CODE
574 );
575 DELETE FROM OKC_SECTIONS_TLH T
576 WHERE NOT EXISTS (
577 SELECT NULL
578 FROM OKC_SECTIONS_BH B
579 WHERE B.ID = T.ID
580 AND B.MAJOR_VERSION = T.MAJOR_VERSION
581 );
582
583 UPDATE OKC_SECTIONS_TLH T SET (
584 HEADING) = (SELECT
585 B.HEADING
586 FROM OKC_SECTIONS_TLH B
587 WHERE B.ID = T.ID
588 AND B.MAJOR_VERSION = T.MAJOR_VERSION
589 AND B.LANGUAGE = T.SOURCE_LANG)
590 WHERE (
591 T.ID,
592 T.MAJOR_VERSION,
593 T.LANGUAGE)
594 IN (SELECT
595 SUBT.ID,
596 SUBT.MAJOR_VERSION,
597 SUBT.LANGUAGE
598 FROM OKC_SECTIONS_TLH SUBB, OKC_SECTIONS_TLH SUBT
599 WHERE SUBB.ID = SUBT.ID
600 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
601 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
602 AND (SUBB.HEADING <> SUBT.HEADING
603 OR (SUBB.HEADING IS NULL AND SUBT.HEADING IS NOT NULL)
604 OR (SUBB.HEADING IS NOT NULL AND SUBT.HEADING IS NULL)
605 ));
606
607 INSERT INTO OKC_SECTIONS_TLH (
608 ID,
609 LANGUAGE,
610 MAJOR_VERSION,
611 SOURCE_LANG,
612 SFWT_FLAG,
613 HEADING,
614 CREATED_BY,
615 CREATION_DATE,
616 LAST_UPDATED_BY,
617 LAST_UPDATE_DATE,
618 LAST_UPDATE_LOGIN)
619 SELECT
620 B.ID,
621 L.LANGUAGE_CODE,
622 B.MAJOR_VERSION,
623 B.SOURCE_LANG,
624 B.SFWT_FLAG,
625 B.HEADING,
626 B.CREATED_BY,
627 B.CREATION_DATE,
628 B.LAST_UPDATED_BY,
629 B.LAST_UPDATE_DATE,
630 B.LAST_UPDATE_LOGIN
631 FROM OKC_SECTIONS_TLH B, FND_LANGUAGES L
632 WHERE L.INSTALLED_FLAG IN ('I', 'B')
633 AND B.LANGUAGE = USERENV('LANG')
634 AND NOT EXISTS(
635 SELECT NULL
636 FROM OKC_SECTIONS_TLH T
637 WHERE T.ID = B.ID
638 AND T.MAJOR_VERSION = B.MAJOR_VERSION
639 AND T.LANGUAGE = L.LANGUAGE_CODE
640 );
641
642
643
644 IF (l_debug = 'Y') THEN
645 okc_debug.log('11950: Leaving add_language ', 2);
646 okc_debug.Reset_Indentation;
647 END IF;
648 END add_language;
649
650 ---------------------------------------------------------------------------
651 -- FUNCTION get_rec for: OKC_SECTIONS_B
652 ---------------------------------------------------------------------------
653 FUNCTION get_rec (
654 p_scn_rec IN scn_rec_type,
655 x_no_data_found OUT NOCOPY BOOLEAN
656 ) RETURN scn_rec_type IS
657 CURSOR okc_sections_b_pk_csr (p_id IN NUMBER) IS
658 SELECT
659 ID,
660 SCN_TYPE,
661 CHR_ID,
662 SAT_CODE,
663 SECTION_SEQUENCE,
664 OBJECT_VERSION_NUMBER,
665 CREATED_BY,
666 CREATION_DATE,
667 LAST_UPDATED_BY,
668 LAST_UPDATE_DATE,
669 LAST_UPDATE_LOGIN,
670 LABEL,
671 SCN_ID,
672 ATTRIBUTE_CATEGORY,
673 ATTRIBUTE1,
674 ATTRIBUTE2,
675 ATTRIBUTE3,
676 ATTRIBUTE4,
677 ATTRIBUTE5,
678 ATTRIBUTE6,
679 ATTRIBUTE7,
680 ATTRIBUTE8,
681 ATTRIBUTE9,
682 ATTRIBUTE10,
683 ATTRIBUTE11,
684 ATTRIBUTE12,
685 ATTRIBUTE13,
686 ATTRIBUTE14,
687 ATTRIBUTE15
688 FROM Okc_Sections_B
689 WHERE okc_sections_b.id = p_id;
690 l_okc_sections_b_pk okc_sections_b_pk_csr%ROWTYPE;
691 l_scn_rec scn_rec_type;
692 BEGIN
693
694 IF (l_debug = 'Y') THEN
695 okc_debug.Set_Indentation('OKC_SCN_PVT');
696 okc_debug.log('2400: Entered get_rec', 2);
697 END IF;
698
699 x_no_data_found := TRUE;
700 -- Get current database values
701 OPEN okc_sections_b_pk_csr (p_scn_rec.id);
702 FETCH okc_sections_b_pk_csr INTO
703 l_scn_rec.ID,
704 l_scn_rec.SCN_TYPE,
705 l_scn_rec.CHR_ID,
706 l_scn_rec.SAT_CODE,
707 l_scn_rec.SECTION_SEQUENCE,
708 l_scn_rec.OBJECT_VERSION_NUMBER,
709 l_scn_rec.CREATED_BY,
710 l_scn_rec.CREATION_DATE,
711 l_scn_rec.LAST_UPDATED_BY,
712 l_scn_rec.LAST_UPDATE_DATE,
713 l_scn_rec.LAST_UPDATE_LOGIN,
714 l_scn_rec.LABEL,
715 l_scn_rec.SCN_ID,
716 l_scn_rec.ATTRIBUTE_CATEGORY,
717 l_scn_rec.ATTRIBUTE1,
718 l_scn_rec.ATTRIBUTE2,
719 l_scn_rec.ATTRIBUTE3,
720 l_scn_rec.ATTRIBUTE4,
721 l_scn_rec.ATTRIBUTE5,
722 l_scn_rec.ATTRIBUTE6,
723 l_scn_rec.ATTRIBUTE7,
724 l_scn_rec.ATTRIBUTE8,
725 l_scn_rec.ATTRIBUTE9,
726 l_scn_rec.ATTRIBUTE10,
727 l_scn_rec.ATTRIBUTE11,
728 l_scn_rec.ATTRIBUTE12,
729 l_scn_rec.ATTRIBUTE13,
730 l_scn_rec.ATTRIBUTE14,
731 l_scn_rec.ATTRIBUTE15;
732 x_no_data_found := okc_sections_b_pk_csr%NOTFOUND;
733 CLOSE okc_sections_b_pk_csr;
734
735 IF (l_debug = 'Y') THEN
736 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
737 okc_debug.Reset_Indentation;
738 END IF;
739
740 RETURN(l_scn_rec);
741
742 END get_rec;
743
744 FUNCTION get_rec (
745 p_scn_rec IN scn_rec_type
746 ) RETURN scn_rec_type IS
747 l_row_notfound BOOLEAN := TRUE;
748 BEGIN
749
750 RETURN(get_rec(p_scn_rec, l_row_notfound));
751
752 END get_rec;
753 ---------------------------------------------------------------------------
754 -- FUNCTION get_rec for: OKC_SECTIONS_TL
755 ---------------------------------------------------------------------------
756 FUNCTION get_rec (
757 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
758 x_no_data_found OUT NOCOPY BOOLEAN
759 ) RETURN okc_sections_tl_rec_type IS
760 CURSOR okc_sections_tl_pk_csr (p_id IN NUMBER,
761 p_language IN VARCHAR2) IS
762 SELECT
763 ID,
764 LANGUAGE,
765 SOURCE_LANG,
766 SFWT_FLAG,
767 HEADING,
768 CREATED_BY,
769 CREATION_DATE,
770 LAST_UPDATED_BY,
771 LAST_UPDATE_DATE,
772 LAST_UPDATE_LOGIN
773 FROM Okc_Sections_Tl
774 WHERE okc_sections_tl.id = p_id
775 AND okc_sections_tl.language = p_language;
776 l_okc_sections_tl_pk okc_sections_tl_pk_csr%ROWTYPE;
777 l_okc_sections_tl_rec okc_sections_tl_rec_type;
778 BEGIN
779
780 IF (l_debug = 'Y') THEN
781 okc_debug.Set_Indentation('OKC_SCN_PVT');
782 okc_debug.log('2600: Entered get_rec', 2);
783 END IF;
784
785 x_no_data_found := TRUE;
786 -- Get current database values
787 OPEN okc_sections_tl_pk_csr (p_okc_sections_tl_rec.id,
788 p_okc_sections_tl_rec.language);
789 FETCH okc_sections_tl_pk_csr INTO
790 l_okc_sections_tl_rec.ID,
791 l_okc_sections_tl_rec.LANGUAGE,
792 l_okc_sections_tl_rec.SOURCE_LANG,
793 l_okc_sections_tl_rec.SFWT_FLAG,
794 l_okc_sections_tl_rec.HEADING,
795 l_okc_sections_tl_rec.CREATED_BY,
796 l_okc_sections_tl_rec.CREATION_DATE,
797 l_okc_sections_tl_rec.LAST_UPDATED_BY,
798 l_okc_sections_tl_rec.LAST_UPDATE_DATE,
799 l_okc_sections_tl_rec.LAST_UPDATE_LOGIN;
800 x_no_data_found := okc_sections_tl_pk_csr%NOTFOUND;
801 CLOSE okc_sections_tl_pk_csr;
802 IF (l_debug = 'Y') THEN
803 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
804 okc_debug.Reset_Indentation;
805 END IF;
806
807 RETURN(l_okc_sections_tl_rec);
808
809 END get_rec;
810
811 FUNCTION get_rec (
812 p_okc_sections_tl_rec IN okc_sections_tl_rec_type
813 ) RETURN okc_sections_tl_rec_type IS
814 l_row_notfound BOOLEAN := TRUE;
815 BEGIN
816
817 RETURN(get_rec(p_okc_sections_tl_rec, l_row_notfound));
818
819 END get_rec;
820 ---------------------------------------------------------------------------
821 -- FUNCTION get_rec for: OKC_SECTIONS_V
822 ---------------------------------------------------------------------------
823 FUNCTION get_rec (
824 p_scnv_rec IN scnv_rec_type,
825 x_no_data_found OUT NOCOPY BOOLEAN
826 ) RETURN scnv_rec_type IS
827
828 -- TAPI generator code MISSING FROM HERE - JOHN (added)
829 CURSOR okc_scnv_pk_csr (p_id IN NUMBER) IS
830 SELECT
831 ID,
832 SCN_TYPE,
833 CHR_ID,
834 SAT_CODE,
835 SECTION_SEQUENCE,
836 LABEL,
837 HEADING,
838 SCN_ID,
839 OBJECT_VERSION_NUMBER,
840 SFWT_FLAG,
841 CREATED_BY,
842 CREATION_DATE,
843 LAST_UPDATED_BY,
844 LAST_UPDATE_DATE,
845 LAST_UPDATE_LOGIN,
846 ATTRIBUTE_CATEGORY,
847 ATTRIBUTE1,
848 ATTRIBUTE2,
849 ATTRIBUTE3,
850 ATTRIBUTE4,
851 ATTRIBUTE5,
852 ATTRIBUTE6,
853 ATTRIBUTE7,
854 ATTRIBUTE8,
855 ATTRIBUTE9,
856 ATTRIBUTE10,
857 ATTRIBUTE11,
858 ATTRIBUTE12,
859 ATTRIBUTE13,
860 ATTRIBUTE14,
861 ATTRIBUTE15
862 FROM okc_sections_v
863 WHERE okc_sections_v.id = p_id;
864
865 l_okc_scnv_pk okc_scnv_pk_csr%ROWTYPE;
866 -- UPTO THIS (JOHN)
867
868 l_scnv_rec scnv_rec_type;
869 BEGIN
870
871 IF (l_debug = 'Y') THEN
872 okc_debug.Set_Indentation('OKC_SCN_PVT');
873 okc_debug.log('2800: Entered get_rec', 2);
874 END IF;
875
876 x_no_data_found := TRUE;
877
878 -- TAPI generator code missing from here - added JOHN
879
880 -- Get current database values
881 OPEN okc_scnv_pk_csr (p_scnv_rec.id);
882 FETCH okc_scnv_pk_csr INTO
883 l_scnv_rec.ID,
884 l_scnv_rec.SCN_TYPE,
885 l_scnv_rec.CHR_ID,
886 l_scnv_rec.SAT_CODE,
887 l_scnv_rec.SECTION_SEQUENCE,
888 l_scnv_rec.LABEL,
889 l_scnv_rec.HEADING,
890 l_scnv_rec.SCN_ID,
891 l_scnv_rec.OBJECT_VERSION_NUMBER,
892 l_scnv_rec.SFWT_FLAG,
893 l_scnv_rec.CREATED_BY,
894 l_scnv_rec.CREATION_DATE,
895 l_scnv_rec.LAST_UPDATED_BY,
896 l_scnv_rec.LAST_UPDATE_DATE,
897 l_scnv_rec.LAST_UPDATE_LOGIN,
898 l_scnv_rec.ATTRIBUTE_CATEGORY,
899 l_scnv_rec.ATTRIBUTE1,
900 l_scnv_rec.ATTRIBUTE2,
901 l_scnv_rec.ATTRIBUTE3,
902 l_scnv_rec.ATTRIBUTE4,
903 l_scnv_rec.ATTRIBUTE5,
904 l_scnv_rec.ATTRIBUTE6,
905 l_scnv_rec.ATTRIBUTE7,
906 l_scnv_rec.ATTRIBUTE8,
907 l_scnv_rec.ATTRIBUTE9,
908 l_scnv_rec.ATTRIBUTE10,
909 l_scnv_rec.ATTRIBUTE11,
910 l_scnv_rec.ATTRIBUTE12,
911 l_scnv_rec.ATTRIBUTE13,
912 l_scnv_rec.ATTRIBUTE14,
913 l_scnv_rec.ATTRIBUTE15;
914
915 x_no_data_found := okc_scnv_pk_csr%NOTFOUND;
916 CLOSE okc_scnv_pk_csr;
917
918 -- UPTO THIS - JOHN
919 IF (l_debug = 'Y') THEN
920 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
921 okc_debug.Reset_Indentation;
922 END IF;
923
924 RETURN(l_scnv_rec);
925
926 END get_rec;
927
928 FUNCTION get_rec (
929 p_scnv_rec IN scnv_rec_type
930 ) RETURN scnv_rec_type IS
931 l_row_notfound BOOLEAN := TRUE;
932 BEGIN
933
934 RETURN(get_rec(p_scnv_rec, l_row_notfound));
935
936 END get_rec;
937
938 ----------------------------------------------------
939 -- FUNCTION null_out_defaults for: OKC_SECTIONS_V --
940 ----------------------------------------------------
941 FUNCTION null_out_defaults (
942 p_scnv_rec IN scnv_rec_type
943 ) RETURN scnv_rec_type IS
944 l_scnv_rec scnv_rec_type := p_scnv_rec;
945 BEGIN
946
947 IF (l_debug = 'Y') THEN
948 okc_debug.Set_Indentation('OKC_SCN_PVT');
949 okc_debug.log('3000: Entered null_out_defaults', 2);
950 END IF;
951
952 IF (l_scnv_rec.id = OKC_API.G_MISS_NUM) THEN
953 l_scnv_rec.id := NULL;
954 END IF;
955 IF (l_scnv_rec.scn_type = OKC_API.G_MISS_CHAR) THEN
956 l_scnv_rec.scn_type := NULL;
957 END IF;
958 IF (l_scnv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
959 l_scnv_rec.chr_id := NULL;
960 END IF;
961 IF (l_scnv_rec.sat_code = OKC_API.G_MISS_CHAR) THEN
962 l_scnv_rec.sat_code := NULL;
963 END IF;
964 IF (l_scnv_rec.section_sequence = OKC_API.G_MISS_NUM) THEN
965 l_scnv_rec.section_sequence := NULL;
966 END IF;
967 IF (l_scnv_rec.label = OKC_API.G_MISS_CHAR) THEN
968 l_scnv_rec.label := NULL;
969 END IF;
970 IF (l_scnv_rec.heading = OKC_API.G_MISS_CHAR) THEN
971 l_scnv_rec.heading := NULL;
972 END IF;
973 IF (l_scnv_rec.scn_id = OKC_API.G_MISS_NUM) THEN
974 l_scnv_rec.scn_id := NULL;
975 END IF;
976 IF (l_scnv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
977 l_scnv_rec.object_version_number := NULL;
978 END IF;
979 IF (l_scnv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
980 l_scnv_rec.sfwt_flag := NULL;
981 END IF;
982 IF (l_scnv_rec.created_by = OKC_API.G_MISS_NUM) THEN
983 l_scnv_rec.created_by := NULL;
984 END IF;
985 IF (l_scnv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
986 l_scnv_rec.creation_date := NULL;
987 END IF;
988 IF (l_scnv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
989 l_scnv_rec.last_updated_by := NULL;
990 END IF;
991 IF (l_scnv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
992 l_scnv_rec.last_update_date := NULL;
993 END IF;
994 IF (l_scnv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
995 l_scnv_rec.last_update_login := NULL;
996 END IF;
997 IF (l_scnv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
998 l_scnv_rec.attribute_category := NULL;
999 END IF;
1000 IF (l_scnv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1001 l_scnv_rec.attribute1 := NULL;
1002 END IF;
1003 IF (l_scnv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1004 l_scnv_rec.attribute2 := NULL;
1005 END IF;
1006 IF (l_scnv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1007 l_scnv_rec.attribute3 := NULL;
1008 END IF;
1009 IF (l_scnv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1010 l_scnv_rec.attribute4 := NULL;
1011 END IF;
1012 IF (l_scnv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1013 l_scnv_rec.attribute5 := NULL;
1014 END IF;
1015 IF (l_scnv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1016 l_scnv_rec.attribute6 := NULL;
1017 END IF;
1018 IF (l_scnv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1019 l_scnv_rec.attribute7 := NULL;
1020 END IF;
1021 IF (l_scnv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1022 l_scnv_rec.attribute8 := NULL;
1023 END IF;
1024 IF (l_scnv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1025 l_scnv_rec.attribute9 := NULL;
1026 END IF;
1027 IF (l_scnv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1028 l_scnv_rec.attribute10 := NULL;
1029 END IF;
1030 IF (l_scnv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1031 l_scnv_rec.attribute11 := NULL;
1032 END IF;
1033 IF (l_scnv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1034 l_scnv_rec.attribute12 := NULL;
1035 END IF;
1036 IF (l_scnv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1037 l_scnv_rec.attribute13 := NULL;
1038 END IF;
1039 IF (l_scnv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1040 l_scnv_rec.attribute14 := NULL;
1041 END IF;
1042 IF (l_scnv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1043 l_scnv_rec.attribute15 := NULL;
1044 END IF;
1045
1046 IF (l_debug = 'Y') THEN
1047 okc_debug.log('500: Leaving null_out_defaults ', 2);
1048 okc_debug.Reset_Indentation;
1049 END IF;
1050
1051 RETURN(l_scnv_rec);
1052
1053 END null_out_defaults;
1054 ---------------------------------------------------------------------------
1055 -- PROCEDURE Validate_Attributes
1056 ---------------------------------------------------------------------------
1057 --------------------------------------------
1058 -- Validate_Attributes for:OKC_SECTIONS_V --
1059 --------------------------------------------
1060 FUNCTION Validate_Attributes (
1061 p_scnv_rec IN scnv_rec_type
1062 ) RETURN VARCHAR2 IS
1063 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1064 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1065 BEGIN
1066
1067 IF (l_debug = 'Y') THEN
1068 okc_debug.Set_Indentation('OKC_SCN_PVT');
1069 okc_debug.log('3100: Entered Validate_Attributes', 2);
1070 END IF;
1071
1072 /************************ HAND-CODED *********************************/
1073 validate_scn_type
1074 (x_return_status => l_return_status,
1075 p_scnv_rec => p_scnv_rec);
1076
1077 -- store the highest degree of error
1078 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1079 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1080 x_return_status := l_return_status;
1081 End If;
1082 End If;
1083
1084 validate_chr_id
1085 (x_return_status => l_return_status,
1086 p_scnv_rec => p_scnv_rec);
1087
1088 -- store the highest degree of error
1089 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1090 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1091 x_return_status := l_return_status;
1092 End If;
1093 End If;
1094
1095 validate_sat_code
1096 (x_return_status => l_return_status,
1097 p_scnv_rec => p_scnv_rec);
1098
1099 -- store the highest degree of error
1100 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1101 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1102 x_return_status := l_return_status;
1103 End If;
1104 End If;
1105
1106 validate_section_sequence
1107 (x_return_status => l_return_status,
1108 p_scnv_rec => p_scnv_rec);
1109
1110 -- store the highest degree of error
1111 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1112 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1113 x_return_status := l_return_status;
1114 End If;
1115 End If;
1116
1117 validate_sfwt_flag
1118 (x_return_status => l_return_status,
1119 p_scnv_rec => p_scnv_rec);
1120
1121 -- store the highest degree of error
1122 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1123 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1124 x_return_status := l_return_status;
1125 End If;
1126 End If;
1127
1128 validate_scn_id
1129 (x_return_status => l_return_status,
1130 p_scnv_rec => p_scnv_rec);
1131
1132 -- store the highest degree of error
1133 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1134 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1135 x_return_status := l_return_status;
1136 End If;
1137 End If;
1138
1139 IF (l_debug = 'Y') THEN
1140 okc_debug.log('3200: Leaving Validate_Attributes', 2);
1141 okc_debug.Reset_Indentation;
1142 END IF;
1143
1144 RETURN(x_return_status);
1145
1146 exception
1147 when OTHERS then
1148
1149 IF (l_debug = 'Y') THEN
1150 okc_debug.log('3300: Exiting Validate_Attributes:OTHERS Exception', 2);
1151 okc_debug.Reset_Indentation;
1152 END IF;
1153
1154 -- store SQL error message on message stack
1155 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1156 p_msg_name => g_unexpected_error,
1157 p_token1 => g_sqlcode_token,
1158 p_token1_value => sqlcode,
1159 p_token2 => g_sqlerrm_token,
1160 p_token2_value => sqlerrm);
1161
1162 -- notify caller of an UNEXPETED error
1163 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1164
1165 -- return status to caller
1166 RETURN(x_return_status);
1167
1168 END Validate_Attributes;
1169
1170 ---------------------------------------------------------------------------
1171 -- PROCEDURE Validate_Record
1172 ---------------------------------------------------------------------------
1173 ----------------------------------------
1174 -- Validate_Record for:OKC_SECTIONS_V --
1175 ----------------------------------------
1176 FUNCTION Validate_Record (
1177 p_scnv_rec IN scnv_rec_type
1178 ) RETURN VARCHAR2 IS
1179 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1180 -- l_unq_tbl OKC_UTIL.unq_tbl_type;
1181
1182 -- ------------------------------------------------------
1183 -- To check for any matching row, for unique check
1184 -- The cursor includes id check filter to handle updates
1185 -- for case K2 should not overwrite already existing K1
1186 -- Two cursors with and without SCN_ID null condition
1187 -- ------------------------------------------------------
1188 CURSOR cur_scn_1 IS
1189 SELECT 'x'
1190 FROM okc_sections_v
1191 WHERE chr_id = p_scnv_rec.CHR_ID
1192 AND sat_code = p_scnv_rec.SAT_CODE
1193 AND section_sequence = p_scnv_rec.SECTION_SEQUENCE
1194 AND scn_id = p_scnv_rec.SCN_ID
1195 AND id <> NVL(p_scnv_rec.ID,-9999);
1196
1197 CURSOR cur_scn_2 IS
1198 SELECT 'x'
1199 FROM okc_sections_b
1200 WHERE chr_id = p_scnv_rec.CHR_ID
1201 AND sat_code = p_scnv_rec.SAT_CODE
1202 AND section_sequence = p_scnv_rec.SECTION_SEQUENCE
1203 AND scn_id IS NULL
1204 AND id <> NVL(p_scnv_rec.ID,-9999);
1205
1206 l_row_found BOOLEAN := False;
1207 l_dummy VARCHAR2(1);
1208
1209 BEGIN
1210
1211 IF (l_debug = 'Y') THEN
1212 okc_debug.Set_Indentation('OKC_SCN_PVT');
1213 okc_debug.log('3400: Entered Validate_Record', 2);
1214 END IF;
1215
1216
1217 -- ---------------------------------------------------------------------
1218 -- Bug 1636056 related changes - Shyam
1219 -- OKC_UTIL.check_comp_unique call earlier was not using
1220 -- the bind variables and parses everytime, replaced with
1221 -- the explicit cursors above, for identical function to
1222 -- check uniqueness for CHR_ID + SAT_CODE + SECTION_SEQUENCE
1223 -- chr_id and sat_code are mutually exclusive (in actual values)
1224 -- if chr_id >0, sat_code is -99, else sat code should be valid(<> -99)
1225 -- ---------------------------------------------------------------------
1226 IF ( p_scnv_rec.SCN_ID IS NOT NULL
1227 AND p_scnv_rec.SCN_ID <> OKC_API.G_MISS_NUM )
1228 THEN
1229 OPEN cur_scn_1;
1230 FETCH cur_scn_1 INTO l_dummy;
1231 l_row_found := cur_scn_1%FOUND;
1232 CLOSE cur_scn_1;
1233 ELSE
1234 -- check any matched row with scn_id as null
1235 OPEN cur_scn_2;
1236 FETCH cur_scn_2 INTO l_dummy;
1237 l_row_found := cur_scn_2%FOUND;
1238 CLOSE cur_scn_2;
1239 END IF;
1240
1241 IF (l_row_found)
1242 THEN
1243 -- Display the newly defined error message
1244 OKC_API.set_message(G_APP_NAME,
1245 'OKC_DUP_SEQUENCE_NUMBER');
1246 l_return_status := OKC_API.G_RET_STS_ERROR;
1247 END IF;
1248
1249 -- if contract number not unique, raise exception
1250 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1251 RAISE G_EXCEPTION_HALT_VALIDATION;
1252 END IF;
1253
1254 IF (l_debug = 'Y') THEN
1255 okc_debug.log('3500: Leaving Validate_Record', 2);
1256 okc_debug.Reset_Indentation;
1257 END IF;
1258
1259 RETURN (l_return_status);
1260
1261 EXCEPTION
1262 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1263
1264 IF (l_debug = 'Y') THEN
1265 okc_debug.log('3600: Exiting Validate_Record:G_EXCEPTION_HALT_VALIDATION Exception', 2);
1266 okc_debug.Reset_Indentation;
1267 END IF;
1268
1269 -- no processing necessary; validation can continue with next column
1270 RETURN (l_return_status);
1271
1272 END Validate_Record;
1273
1274 /*********************** END HAND-CODED ********************************/
1275
1276 ---------------------------------------------------------------------------
1277 -- PROCEDURE Migrate
1278 ---------------------------------------------------------------------------
1279 PROCEDURE migrate (
1280 p_from IN scnv_rec_type,
1281 p_to IN OUT NOCOPY scn_rec_type
1282 ) IS
1283 BEGIN
1284
1285 p_to.id := p_from.id;
1286 p_to.scn_type := p_from.scn_type;
1287 p_to.chr_id := p_from.chr_id;
1288 p_to.sat_code := p_from.sat_code;
1289 p_to.section_sequence := p_from.section_sequence;
1290 p_to.object_version_number := p_from.object_version_number;
1291 p_to.created_by := p_from.created_by;
1292 p_to.creation_date := p_from.creation_date;
1293 p_to.last_updated_by := p_from.last_updated_by;
1294 p_to.last_update_date := p_from.last_update_date;
1295 p_to.last_update_login := p_from.last_update_login;
1296 p_to.label := p_from.label;
1297 p_to.scn_id := p_from.scn_id;
1298 p_to.attribute_category := p_from.attribute_category;
1299 p_to.attribute1 := p_from.attribute1;
1300 p_to.attribute2 := p_from.attribute2;
1301 p_to.attribute3 := p_from.attribute3;
1302 p_to.attribute4 := p_from.attribute4;
1303 p_to.attribute5 := p_from.attribute5;
1304 p_to.attribute6 := p_from.attribute6;
1305 p_to.attribute7 := p_from.attribute7;
1306 p_to.attribute8 := p_from.attribute8;
1307 p_to.attribute9 := p_from.attribute9;
1308 p_to.attribute10 := p_from.attribute10;
1309 p_to.attribute11 := p_from.attribute11;
1310 p_to.attribute12 := p_from.attribute12;
1311 p_to.attribute13 := p_from.attribute13;
1312 p_to.attribute14 := p_from.attribute14;
1313 p_to.attribute15 := p_from.attribute15;
1314
1315 END migrate;
1316
1317
1318 PROCEDURE migrate (
1319 p_from IN scn_rec_type,
1320 p_to IN OUT NOCOPY scnv_rec_type
1321 ) IS
1322 BEGIN
1323
1324 p_to.id := p_from.id;
1325 p_to.scn_type := p_from.scn_type;
1326 p_to.chr_id := p_from.chr_id;
1327 p_to.sat_code := p_from.sat_code;
1328 p_to.section_sequence := p_from.section_sequence;
1329 p_to.object_version_number := p_from.object_version_number;
1330 p_to.created_by := p_from.created_by;
1331 p_to.creation_date := p_from.creation_date;
1332 p_to.last_updated_by := p_from.last_updated_by;
1333 p_to.last_update_date := p_from.last_update_date;
1334 p_to.last_update_login := p_from.last_update_login;
1335 p_to.label := p_from.label;
1336 p_to.scn_id := p_from.scn_id;
1337 p_to.attribute_category := p_from.attribute_category;
1338 p_to.attribute1 := p_from.attribute1;
1339 p_to.attribute2 := p_from.attribute2;
1340 p_to.attribute3 := p_from.attribute3;
1341 p_to.attribute4 := p_from.attribute4;
1342 p_to.attribute5 := p_from.attribute5;
1343 p_to.attribute6 := p_from.attribute6;
1344 p_to.attribute7 := p_from.attribute7;
1345 p_to.attribute8 := p_from.attribute8;
1346 p_to.attribute9 := p_from.attribute9;
1347 p_to.attribute10 := p_from.attribute10;
1348 p_to.attribute11 := p_from.attribute11;
1349 p_to.attribute12 := p_from.attribute12;
1350 p_to.attribute13 := p_from.attribute13;
1351 p_to.attribute14 := p_from.attribute14;
1352 p_to.attribute15 := p_from.attribute15;
1353
1354 END migrate;
1355
1356
1357 PROCEDURE migrate (
1358 p_from IN scnv_rec_type,
1359 p_to IN OUT NOCOPY okc_sections_tl_rec_type
1360 ) IS
1361 BEGIN
1362
1363 p_to.id := p_from.id;
1364 p_to.sfwt_flag := p_from.sfwt_flag;
1365 p_to.heading := p_from.heading;
1366 p_to.created_by := p_from.created_by;
1367 p_to.creation_date := p_from.creation_date;
1368 p_to.last_updated_by := p_from.last_updated_by;
1369 p_to.last_update_date := p_from.last_update_date;
1370 p_to.last_update_login := p_from.last_update_login;
1371
1372 END migrate;
1373
1374
1375 PROCEDURE migrate (
1376 p_from IN okc_sections_tl_rec_type,
1377 p_to IN OUT NOCOPY scnv_rec_type
1378 ) IS
1379 BEGIN
1380
1381 p_to.id := p_from.id;
1382 p_to.sfwt_flag := p_from.sfwt_flag;
1383 p_to.heading := p_from.heading;
1384 p_to.created_by := p_from.created_by;
1385 p_to.creation_date := p_from.creation_date;
1386 p_to.last_updated_by := p_from.last_updated_by;
1387 p_to.last_update_date := p_from.last_update_date;
1388 p_to.last_update_login := p_from.last_update_login;
1389
1390 END migrate;
1391
1392 ---------------------------------------------------------------------------
1393 -- PROCEDURE validate_row
1394 ---------------------------------------------------------------------------
1395 -------------------------------------
1396 -- validate_row for:OKC_SECTIONS_V --
1397 -------------------------------------
1398 PROCEDURE validate_row(
1399 p_api_version IN NUMBER,
1400 p_init_msg_list IN VARCHAR2,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_scnv_rec IN scnv_rec_type) IS
1405
1406 l_api_version CONSTANT NUMBER := 1;
1407 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1408 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1409 l_scnv_rec scnv_rec_type := p_scnv_rec;
1410 l_scn_rec scn_rec_type;
1411 l_okc_sections_tl_rec okc_sections_tl_rec_type;
1412 BEGIN
1413
1414 IF (l_debug = 'Y') THEN
1415 okc_debug.Set_Indentation('OKC_SCN_PVT');
1416 okc_debug.log('4100: Entered validate_row', 2);
1417 END IF;
1418
1419 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1420 G_PKG_NAME,
1421 p_init_msg_list,
1422 l_api_version,
1423 p_api_version,
1424 '_PVT',
1425 x_return_status);
1426 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1428 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1429 RAISE OKC_API.G_EXCEPTION_ERROR;
1430 END IF;
1431 --- Validate all non-missing attributes (Item Level Validation)
1432 l_return_status := Validate_Attributes(l_scnv_rec);
1433 --- If any errors happen abort API
1434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1435 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1436 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1437 RAISE OKC_API.G_EXCEPTION_ERROR;
1438 END IF;
1439 l_return_status := Validate_Record(l_scnv_rec);
1440 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1441 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1442 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1443 RAISE OKC_API.G_EXCEPTION_ERROR;
1444 END IF;
1445 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1446
1447 IF (l_debug = 'Y') THEN
1448 okc_debug.log('4200: Leaving validate_row', 2);
1449 okc_debug.Reset_Indentation;
1450 END IF;
1451
1452 EXCEPTION
1453 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1454
1455 IF (l_debug = 'Y') THEN
1456 okc_debug.log('4300: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1457 okc_debug.Reset_Indentation;
1458 END IF;
1459
1460 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1461 (
1462 l_api_name,
1463 G_PKG_NAME,
1464 'OKC_API.G_RET_STS_ERROR',
1465 x_msg_count,
1466 x_msg_data,
1467 '_PVT'
1468 );
1469 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1470
1471 IF (l_debug = 'Y') THEN
1472 okc_debug.log('4400: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1473 okc_debug.Reset_Indentation;
1474 END IF;
1475
1476 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1477 (
1478 l_api_name,
1479 G_PKG_NAME,
1480 'OKC_API.G_RET_STS_UNEXP_ERROR',
1481 x_msg_count,
1482 x_msg_data,
1483 '_PVT'
1484 );
1485 WHEN OTHERS THEN
1486
1487 IF (l_debug = 'Y') THEN
1488 okc_debug.log('4500: Exiting validate_row:OTHERS Exception', 2);
1489 okc_debug.Reset_Indentation;
1490 END IF;
1491
1492 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1493 (
1494 l_api_name,
1495 G_PKG_NAME,
1496 'OTHERS',
1497 x_msg_count,
1498 x_msg_data,
1499 '_PVT'
1500 );
1501
1502 END validate_row;
1503 ------------------------------------------
1504 -- PL/SQL TBL validate_row for:SCNV_TBL --
1505 ------------------------------------------
1506 PROCEDURE validate_row(
1507 p_api_version IN NUMBER,
1508 p_init_msg_list IN VARCHAR2,
1509 x_return_status OUT NOCOPY VARCHAR2,
1510 x_msg_count OUT NOCOPY NUMBER,
1511 x_msg_data OUT NOCOPY VARCHAR2,
1512 p_scnv_tbl IN scnv_tbl_type) IS
1513
1514 l_api_version CONSTANT NUMBER := 1;
1515 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1516 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1517 i NUMBER := 0;
1518 BEGIN
1519
1520 IF (l_debug = 'Y') THEN
1521 okc_debug.Set_Indentation('OKC_SCN_PVT');
1522 okc_debug.log('4600: Entered validate_row', 2);
1523 END IF;
1524
1525 OKC_API.init_msg_list(p_init_msg_list);
1526 -- Make sure PL/SQL table has records in it before passing
1527 IF (p_scnv_tbl.COUNT > 0) THEN
1528 i := p_scnv_tbl.FIRST;
1529 LOOP
1530 validate_row (
1531 p_api_version => p_api_version,
1532 p_init_msg_list => OKC_API.G_FALSE,
1533 x_return_status => x_return_status,
1534 x_msg_count => x_msg_count,
1535 x_msg_data => x_msg_data,
1536 p_scnv_rec => p_scnv_tbl(i));
1537 EXIT WHEN (i = p_scnv_tbl.LAST);
1538 i := p_scnv_tbl.NEXT(i);
1539 END LOOP;
1540 END IF;
1541
1542 IF (l_debug = 'Y') THEN
1543 okc_debug.log('4700: Leaving validate_row', 2);
1544 okc_debug.Reset_Indentation;
1545 END IF;
1546 EXCEPTION
1547 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1548
1549 IF (l_debug = 'Y') THEN
1550 okc_debug.log('4800: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1551 okc_debug.Reset_Indentation;
1552 END IF;
1553
1554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1555 (
1556 l_api_name,
1557 G_PKG_NAME,
1558 'OKC_API.G_RET_STS_ERROR',
1559 x_msg_count,
1560 x_msg_data,
1561 '_PVT'
1562 );
1563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1564
1565 IF (l_debug = 'Y') THEN
1566 okc_debug.log('4900: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1567 okc_debug.Reset_Indentation;
1568 END IF;
1569
1570 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1571 (
1572 l_api_name,
1573 G_PKG_NAME,
1574 'OKC_API.G_RET_STS_UNEXP_ERROR',
1575 x_msg_count,
1576 x_msg_data,
1577 '_PVT'
1578 );
1579 WHEN OTHERS THEN
1580
1581 IF (l_debug = 'Y') THEN
1582 okc_debug.log('5000: Exiting validate_row:OTHERS Exception', 2);
1583 okc_debug.Reset_Indentation;
1584 END IF;
1585
1586 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1587 (
1588 l_api_name,
1589 G_PKG_NAME,
1590 'OTHERS',
1591 x_msg_count,
1592 x_msg_data,
1593 '_PVT'
1594 );
1595
1596 END validate_row;
1597
1598 -----------------------------------
1599 -- insert_row for:OKC_SECTIONS_B --
1600 -----------------------------------
1601 PROCEDURE insert_row(
1602 p_init_msg_list IN VARCHAR2,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2,
1606 p_scn_rec IN scn_rec_type,
1607 x_scn_rec OUT NOCOPY scn_rec_type) IS
1608
1609 l_api_version CONSTANT NUMBER := 1;
1610 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1611 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1612 l_scn_rec scn_rec_type := p_scn_rec;
1613 l_def_scn_rec scn_rec_type;
1614 ---------------------------------------
1615 -- Set_Attributes for:OKC_SECTIONS_B --
1616 ---------------------------------------
1617 FUNCTION Set_Attributes (
1618 p_scn_rec IN scn_rec_type,
1619 x_scn_rec OUT NOCOPY scn_rec_type
1620 ) RETURN VARCHAR2 IS
1621 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1622 BEGIN
1623
1624 x_scn_rec := p_scn_rec;
1625 RETURN(l_return_status);
1626
1627 END Set_Attributes;
1628 BEGIN
1629
1630 IF (l_debug = 'Y') THEN
1631 okc_debug.Set_Indentation('OKC_SCN_PVT');
1632 okc_debug.log('5200: Entered insert_row', 2);
1633 END IF;
1634
1635 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1636 p_init_msg_list,
1637 '_PVT',
1638 x_return_status);
1639 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1640 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1641 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1642 RAISE OKC_API.G_EXCEPTION_ERROR;
1643 END IF;
1644 --- Setting item attributes
1645 l_return_status := Set_Attributes(
1646 p_scn_rec, -- IN
1647 l_scn_rec); -- OUT
1648 --- If any errors happen abort API
1649 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1650 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1651 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1652 RAISE OKC_API.G_EXCEPTION_ERROR;
1653 END IF;
1654 INSERT INTO OKC_SECTIONS_B(
1655 id,
1656 scn_type,
1657 chr_id,
1658 sat_code,
1659 section_sequence,
1660 object_version_number,
1661 created_by,
1662 creation_date,
1663 last_updated_by,
1664 last_update_date,
1665 last_update_login,
1666 label,
1667 scn_id,
1668 attribute_category,
1669 attribute1,
1670 attribute2,
1671 attribute3,
1672 attribute4,
1673 attribute5,
1674 attribute6,
1675 attribute7,
1676 attribute8,
1677 attribute9,
1678 attribute10,
1679 attribute11,
1680 attribute12,
1681 attribute13,
1682 attribute14,
1683 attribute15)
1684 VALUES (
1685 l_scn_rec.id,
1686 l_scn_rec.scn_type,
1687 l_scn_rec.chr_id,
1688 l_scn_rec.sat_code,
1689 l_scn_rec.section_sequence,
1690 l_scn_rec.object_version_number,
1691 l_scn_rec.created_by,
1692 l_scn_rec.creation_date,
1693 l_scn_rec.last_updated_by,
1694 l_scn_rec.last_update_date,
1695 l_scn_rec.last_update_login,
1696 l_scn_rec.label,
1697 l_scn_rec.scn_id,
1698 l_scn_rec.attribute_category,
1699 l_scn_rec.attribute1,
1700 l_scn_rec.attribute2,
1701 l_scn_rec.attribute3,
1702 l_scn_rec.attribute4,
1703 l_scn_rec.attribute5,
1704 l_scn_rec.attribute6,
1705 l_scn_rec.attribute7,
1706 l_scn_rec.attribute8,
1707 l_scn_rec.attribute9,
1708 l_scn_rec.attribute10,
1709 l_scn_rec.attribute11,
1710 l_scn_rec.attribute12,
1711 l_scn_rec.attribute13,
1712 l_scn_rec.attribute14,
1713 l_scn_rec.attribute15);
1714 -- Set OUT values
1715 x_scn_rec := l_scn_rec;
1716 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1717
1718
1719 IF (l_debug = 'Y') THEN
1720 okc_debug.log('5300: Leaving insert_row', 2);
1721 okc_debug.Reset_Indentation;
1722 END IF;
1723
1724 EXCEPTION
1725 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1726
1727 IF (l_debug = 'Y') THEN
1728 okc_debug.log('5400: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1729 okc_debug.Reset_Indentation;
1730 END IF;
1731
1732 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1733 (
1734 l_api_name,
1735 G_PKG_NAME,
1736 'OKC_API.G_RET_STS_ERROR',
1737 x_msg_count,
1738 x_msg_data,
1739 '_PVT'
1740 );
1741 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1742
1743 IF (l_debug = 'Y') THEN
1744 okc_debug.log('5500: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1745 okc_debug.Reset_Indentation;
1746 END IF;
1747
1748 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1749 (
1750 l_api_name,
1751 G_PKG_NAME,
1752 'OKC_API.G_RET_STS_UNEXP_ERROR',
1753 x_msg_count,
1754 x_msg_data,
1755 '_PVT'
1756 );
1757 WHEN OTHERS THEN
1758
1759 IF (l_debug = 'Y') THEN
1760 okc_debug.log('5600: Exiting insert_row:OTHERS Exception', 2);
1761 okc_debug.Reset_Indentation;
1762 END IF;
1763
1764 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1765 (
1766 l_api_name,
1767 G_PKG_NAME,
1768 'OTHERS',
1769 x_msg_count,
1770 x_msg_data,
1771 '_PVT'
1772 );
1773
1774 END insert_row;
1775 ------------------------------------
1776 -- insert_row for:OKC_SECTIONS_TL --
1777 ------------------------------------
1778 PROCEDURE insert_row(
1779 p_init_msg_list IN VARCHAR2,
1780 x_return_status OUT NOCOPY VARCHAR2,
1781 x_msg_count OUT NOCOPY NUMBER,
1782 x_msg_data OUT NOCOPY VARCHAR2,
1783 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
1784 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type) IS
1785
1786 l_api_version CONSTANT NUMBER := 1;
1787 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1788 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1789 l_okc_sections_tl_rec okc_sections_tl_rec_type := p_okc_sections_tl_rec;
1790 l_def_okc_sections_tl_rec okc_sections_tl_rec_type;
1791 CURSOR get_languages IS
1792 SELECT *
1793 FROM FND_LANGUAGES
1794 WHERE INSTALLED_FLAG IN ('I', 'B');
1795 ----------------------------------------
1796 -- Set_Attributes for:OKC_SECTIONS_TL --
1797 ----------------------------------------
1798 FUNCTION Set_Attributes (
1799 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
1800 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type
1801 ) RETURN VARCHAR2 IS
1802 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1803 BEGIN
1804
1805 x_okc_sections_tl_rec := p_okc_sections_tl_rec;
1806 x_okc_sections_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1807 x_okc_sections_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1808 RETURN(l_return_status);
1809
1810 END Set_Attributes;
1811 BEGIN
1812
1813 IF (l_debug = 'Y') THEN
1814 okc_debug.Set_Indentation('OKC_SCN_PVT');
1815 okc_debug.log('5800: Entered insert_row', 2);
1816 END IF;
1817
1818 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1819 p_init_msg_list,
1820 '_PVT',
1821 x_return_status);
1822 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1823 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1824 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1825 RAISE OKC_API.G_EXCEPTION_ERROR;
1826 END IF;
1827 --- Setting item attributes
1828 l_return_status := Set_Attributes(
1829 p_okc_sections_tl_rec, -- IN
1830 l_okc_sections_tl_rec); -- OUT
1831 --- If any errors happen abort API
1832 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1833 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1834 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1835 RAISE OKC_API.G_EXCEPTION_ERROR;
1836 END IF;
1837 FOR l_lang_rec IN get_languages LOOP
1838 l_okc_sections_tl_rec.language := l_lang_rec.language_code;
1839 INSERT INTO OKC_SECTIONS_TL(
1840 id,
1841 language,
1842 source_lang,
1843 sfwt_flag,
1844 heading,
1845 created_by,
1846 creation_date,
1847 last_updated_by,
1848 last_update_date,
1849 last_update_login)
1850 VALUES (
1851 l_okc_sections_tl_rec.id,
1852 l_okc_sections_tl_rec.language,
1853 l_okc_sections_tl_rec.source_lang,
1854 l_okc_sections_tl_rec.sfwt_flag,
1855 l_okc_sections_tl_rec.heading,
1856 l_okc_sections_tl_rec.created_by,
1857 l_okc_sections_tl_rec.creation_date,
1858 l_okc_sections_tl_rec.last_updated_by,
1859 l_okc_sections_tl_rec.last_update_date,
1860 l_okc_sections_tl_rec.last_update_login);
1861 END LOOP;
1862 -- Set OUT values
1863 x_okc_sections_tl_rec := l_okc_sections_tl_rec;
1864 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1865
1866 IF (l_debug = 'Y') THEN
1867 okc_debug.log('5900: Leaving insert_row', 2);
1868 okc_debug.Reset_Indentation;
1869 END IF;
1870
1871 EXCEPTION
1872 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1873
1874 IF (l_debug = 'Y') THEN
1875 okc_debug.log('6000: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1876 okc_debug.Reset_Indentation;
1877 END IF;
1878
1879 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1880 (
1881 l_api_name,
1882 G_PKG_NAME,
1883 'OKC_API.G_RET_STS_ERROR',
1884 x_msg_count,
1885 x_msg_data,
1886 '_PVT'
1887 );
1888 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1889
1890 IF (l_debug = 'Y') THEN
1891 okc_debug.log('6100: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1892 okc_debug.Reset_Indentation;
1893 END IF;
1894
1895 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1896 (
1897 l_api_name,
1898 G_PKG_NAME,
1899 'OKC_API.G_RET_STS_UNEXP_ERROR',
1900 x_msg_count,
1901 x_msg_data,
1902 '_PVT'
1903 );
1904 WHEN OTHERS THEN
1905
1906 IF (l_debug = 'Y') THEN
1907 okc_debug.log('6200: Exiting insert_row:OTHERS Exception', 2);
1908 okc_debug.Reset_Indentation;
1909 END IF;
1910
1911 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1912 (
1913 l_api_name,
1914 G_PKG_NAME,
1915 'OTHERS',
1916 x_msg_count,
1917 x_msg_data,
1918 '_PVT'
1919 );
1920
1921 END insert_row;
1922 -----------------------------------
1923 -- insert_row for:OKC_SECTIONS_V --
1924 -----------------------------------
1925 PROCEDURE insert_row(
1926 p_api_version IN NUMBER,
1927 p_init_msg_list IN VARCHAR2,
1928 x_return_status OUT NOCOPY VARCHAR2,
1929 x_msg_count OUT NOCOPY NUMBER,
1930 x_msg_data OUT NOCOPY VARCHAR2,
1931 p_scnv_rec IN scnv_rec_type,
1932 x_scnv_rec OUT NOCOPY scnv_rec_type) IS
1933
1934 l_api_version CONSTANT NUMBER := 1;
1935 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1936 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1937 l_scnv_rec scnv_rec_type;
1938 l_def_scnv_rec scnv_rec_type;
1939 l_scn_rec scn_rec_type;
1940 lx_scn_rec scn_rec_type;
1941 l_okc_sections_tl_rec okc_sections_tl_rec_type;
1942 lx_okc_sections_tl_rec okc_sections_tl_rec_type;
1943 -------------------------------
1944 -- FUNCTION fill_who_columns --
1945 -------------------------------
1946 FUNCTION fill_who_columns (
1947 p_scnv_rec IN scnv_rec_type
1948 ) RETURN scnv_rec_type IS
1949 l_scnv_rec scnv_rec_type := p_scnv_rec;
1950 BEGIN
1951
1952 l_scnv_rec.CREATION_DATE := SYSDATE;
1953 l_scnv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1954 l_scnv_rec.LAST_UPDATE_DATE := l_scnv_rec.CREATION_DATE;
1955 l_scnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1956 l_scnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1957 RETURN(l_scnv_rec);
1958
1959 END fill_who_columns;
1960 ---------------------------------------
1961 -- Set_Attributes for:OKC_SECTIONS_V --
1962 ---------------------------------------
1963 FUNCTION Set_Attributes (
1964 p_scnv_rec IN scnv_rec_type,
1965 x_scnv_rec OUT NOCOPY scnv_rec_type
1966 ) RETURN VARCHAR2 IS
1967 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1968 BEGIN
1969
1970 x_scnv_rec := p_scnv_rec;
1971 x_scnv_rec.OBJECT_VERSION_NUMBER := 1;
1972 /************************ HAND-CODED *********************************/
1973 -- set scn_type to upper
1974 x_scnv_rec.SCN_TYPE := UPPER(x_scnv_rec.SCN_TYPE);
1975
1976 -- Default CHR_ID or SAT_CODE
1977 If (x_scnv_rec.SCN_TYPE = 'CHR') Then
1978 x_scnv_rec.SAT_CODE := '-99';
1979 Elsif (x_scnv_rec.SCN_TYPE = 'SAT') Then
1980 x_scnv_rec.CHR_ID := -99;
1981 End If;
1982 /*********************** END HAND-CODED ******************************/
1983 RETURN(l_return_status);
1984
1985 END Set_Attributes;
1986 BEGIN
1987
1988 IF (l_debug = 'Y') THEN
1989 okc_debug.Set_Indentation('OKC_SCN_PVT');
1990 okc_debug.log('6500: Entered insert_row', 2);
1991 END IF;
1992
1993 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1994 G_PKG_NAME,
1995 p_init_msg_list,
1996 l_api_version,
1997 p_api_version,
1998 '_PVT',
1999 x_return_status);
2000 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2001 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2002 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2003 RAISE OKC_API.G_EXCEPTION_ERROR;
2004 END IF;
2005 l_scnv_rec := null_out_defaults(p_scnv_rec);
2006 -- Set primary key value
2007 l_scnv_rec.ID := get_seq_id;
2008 --- Setting item attributes
2009 l_return_status := Set_Attributes(
2010 l_scnv_rec, -- IN
2011 l_def_scnv_rec); -- OUT
2012 --- If any errors happen abort API
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 l_def_scnv_rec := fill_who_columns(l_def_scnv_rec);
2019 --- Validate all non-missing attributes (Item Level Validation)
2020 l_return_status := Validate_Attributes(l_def_scnv_rec);
2021 --- If any errors happen abort API
2022 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2023 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2024 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2025 RAISE OKC_API.G_EXCEPTION_ERROR;
2026 END IF;
2027 l_return_status := Validate_Record(l_def_scnv_rec);
2028 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2029 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2030 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2031 RAISE OKC_API.G_EXCEPTION_ERROR;
2032 END IF;
2033 --------------------------------------
2034 -- Move VIEW record to "Child" records
2035 --------------------------------------
2036 migrate(l_def_scnv_rec, l_scn_rec);
2037 migrate(l_def_scnv_rec, l_okc_sections_tl_rec);
2038 --------------------------------------------
2039 -- Call the INSERT_ROW for each child record
2040 --------------------------------------------
2041 insert_row(
2042 p_init_msg_list,
2043 x_return_status,
2044 x_msg_count,
2045 x_msg_data,
2046 l_scn_rec,
2047 lx_scn_rec
2048 );
2049 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2050 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2051 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2052 RAISE OKC_API.G_EXCEPTION_ERROR;
2053 END IF;
2054 migrate(lx_scn_rec, l_def_scnv_rec);
2055 insert_row(
2056 p_init_msg_list,
2057 x_return_status,
2058 x_msg_count,
2059 x_msg_data,
2060 l_okc_sections_tl_rec,
2061 lx_okc_sections_tl_rec
2062 );
2063 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2064 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2065 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2066 RAISE OKC_API.G_EXCEPTION_ERROR;
2067 END IF;
2068 migrate(lx_okc_sections_tl_rec, l_def_scnv_rec);
2069 -- Set OUT values
2070 x_scnv_rec := l_def_scnv_rec;
2071 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2072
2073 IF (l_debug = 'Y') THEN
2074 okc_debug.log('6600: Leaving insert_row', 2);
2075 okc_debug.Reset_Indentation;
2076 END IF;
2077
2078 EXCEPTION
2079 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2080
2081 IF (l_debug = 'Y') THEN
2082 okc_debug.log('6700: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2083 okc_debug.Reset_Indentation;
2084 END IF;
2085
2086 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2087 (
2088 l_api_name,
2089 G_PKG_NAME,
2090 'OKC_API.G_RET_STS_ERROR',
2091 x_msg_count,
2092 x_msg_data,
2093 '_PVT'
2094 );
2095 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2096
2097 IF (l_debug = 'Y') THEN
2098 okc_debug.log('6800: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2099 okc_debug.Reset_Indentation;
2100 END IF;
2101
2102 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2103 (
2104 l_api_name,
2105 G_PKG_NAME,
2106 'OKC_API.G_RET_STS_UNEXP_ERROR',
2107 x_msg_count,
2108 x_msg_data,
2109 '_PVT'
2110 );
2111 WHEN OTHERS THEN
2112
2113 IF (l_debug = 'Y') THEN
2114 okc_debug.log('6900: Exiting insert_row:OTHERS Exception', 2);
2115 okc_debug.Reset_Indentation;
2116 END IF;
2117
2118 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2119 (
2120 l_api_name,
2121 G_PKG_NAME,
2122 'OTHERS',
2123 x_msg_count,
2124 x_msg_data,
2125 '_PVT'
2126 );
2127
2128 END insert_row;
2129 ----------------------------------------
2130 -- PL/SQL TBL insert_row for:SCNV_TBL --
2131 ----------------------------------------
2132 PROCEDURE insert_row(
2133 p_api_version IN NUMBER,
2134 p_init_msg_list IN VARCHAR2,
2135 x_return_status OUT NOCOPY VARCHAR2,
2136 x_msg_count OUT NOCOPY NUMBER,
2137 x_msg_data OUT NOCOPY VARCHAR2,
2138 p_scnv_tbl IN scnv_tbl_type,
2139 x_scnv_tbl OUT NOCOPY scnv_tbl_type) IS
2140
2141 l_api_version CONSTANT NUMBER := 1;
2142 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2143 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2144 i NUMBER := 0;
2145 BEGIN
2146
2147 IF (l_debug = 'Y') THEN
2148 okc_debug.Set_Indentation('OKC_SCN_PVT');
2149 okc_debug.log('7000: Entered insert_row', 2);
2150 END IF;
2151
2152 OKC_API.init_msg_list(p_init_msg_list);
2153 -- Make sure PL/SQL table has records in it before passing
2154 IF (p_scnv_tbl.COUNT > 0) THEN
2155 i := p_scnv_tbl.FIRST;
2156 LOOP
2157 insert_row (
2158 p_api_version => p_api_version,
2159 p_init_msg_list => OKC_API.G_FALSE,
2160 x_return_status => x_return_status,
2161 x_msg_count => x_msg_count,
2162 x_msg_data => x_msg_data,
2163 p_scnv_rec => p_scnv_tbl(i),
2164 x_scnv_rec => x_scnv_tbl(i));
2165 EXIT WHEN (i = p_scnv_tbl.LAST);
2166 i := p_scnv_tbl.NEXT(i);
2167 END LOOP;
2168 END IF;
2169
2170 IF (l_debug = 'Y') THEN
2171 okc_debug.log('7100: Leaving insert_row', 2);
2172 okc_debug.Reset_Indentation;
2173 END IF;
2174
2175 EXCEPTION
2176 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2177
2178 IF (l_debug = 'Y') THEN
2179 okc_debug.log('7200: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2180 okc_debug.Reset_Indentation;
2181 END IF;
2182
2183 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2184 (
2185 l_api_name,
2186 G_PKG_NAME,
2187 'OKC_API.G_RET_STS_ERROR',
2188 x_msg_count,
2189 x_msg_data,
2190 '_PVT'
2191 );
2192 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2193
2194 IF (l_debug = 'Y') THEN
2195 okc_debug.log('7300: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2196 okc_debug.Reset_Indentation;
2197 END IF;
2198
2199 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2200 (
2201 l_api_name,
2202 G_PKG_NAME,
2203 'OKC_API.G_RET_STS_UNEXP_ERROR',
2204 x_msg_count,
2205 x_msg_data,
2206 '_PVT'
2207 );
2208 WHEN OTHERS THEN
2209
2210 IF (l_debug = 'Y') THEN
2211 okc_debug.log('7400: Exiting insert_row:OTHERS Exception', 2);
2212 okc_debug.Reset_Indentation;
2213 END IF;
2214
2215 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2216 (
2217 l_api_name,
2218 G_PKG_NAME,
2219 'OTHERS',
2220 x_msg_count,
2221 x_msg_data,
2222 '_PVT'
2223 );
2224
2225 END insert_row;
2226
2227 ---------------------------------------------------------------------------
2228 -- PROCEDURE lock_row
2229 ---------------------------------------------------------------------------
2230 ---------------------------------
2231 -- lock_row for:OKC_SECTIONS_B --
2232 ---------------------------------
2233 PROCEDURE lock_row(
2234 p_init_msg_list IN VARCHAR2,
2235 x_return_status OUT NOCOPY VARCHAR2,
2236 x_msg_count OUT NOCOPY NUMBER,
2237 x_msg_data OUT NOCOPY VARCHAR2,
2238 p_scn_rec IN scn_rec_type) IS
2239
2240 E_Resource_Busy EXCEPTION;
2241 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2242 CURSOR lock_csr (p_scn_rec IN scn_rec_type) IS
2243 SELECT OBJECT_VERSION_NUMBER
2244 FROM OKC_SECTIONS_B
2245 WHERE ID = p_scn_rec.id
2246 AND OBJECT_VERSION_NUMBER = p_scn_rec.object_version_number
2247 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2248
2249 CURSOR lchk_csr (p_scn_rec IN scn_rec_type) IS
2250 SELECT OBJECT_VERSION_NUMBER
2251 FROM OKC_SECTIONS_B
2252 WHERE ID = p_scn_rec.id;
2253 l_api_version CONSTANT NUMBER := 1;
2254 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2255 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2256 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
2257 lc_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
2258 l_row_notfound BOOLEAN := FALSE;
2259 lc_row_notfound BOOLEAN := FALSE;
2260 BEGIN
2261
2262 IF (l_debug = 'Y') THEN
2263 okc_debug.Set_Indentation('OKC_SCN_PVT');
2264 okc_debug.log('7500: Entered lock_row', 2);
2265 END IF;
2266
2267 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2268 p_init_msg_list,
2269 '_PVT',
2270 x_return_status);
2271 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2272 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2273 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2274 RAISE OKC_API.G_EXCEPTION_ERROR;
2275 END IF;
2276 BEGIN
2277
2278 -- okc_debug.Set_Indentation('OKC_SCN_PVT');
2279 -- okc_debug.log('7600: Entered lock_row', 2);
2280
2281 OPEN lock_csr(p_scn_rec);
2282 FETCH lock_csr INTO l_object_version_number;
2283 l_row_notfound := lock_csr%NOTFOUND;
2284 CLOSE lock_csr;
2285
2286 EXCEPTION
2287 WHEN E_Resource_Busy THEN
2288
2289 IF (l_debug = 'Y') THEN
2290 okc_debug.log('7800: Exiting lock_row:E_Resource_Busy Exception', 2);
2291 okc_debug.Reset_Indentation;
2292 END IF;
2293
2294 IF (lock_csr%ISOPEN) THEN
2295 CLOSE lock_csr;
2296 END IF;
2297 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2298 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2299 END;
2300
2301 IF ( l_row_notfound ) THEN
2302 OPEN lchk_csr(p_scn_rec);
2303 FETCH lchk_csr INTO lc_object_version_number;
2304 lc_row_notfound := lchk_csr%NOTFOUND;
2305 CLOSE lchk_csr;
2306 END IF;
2307 IF (lc_row_notfound) THEN
2308 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2309 RAISE OKC_API.G_EXCEPTION_ERROR;
2310 ELSIF lc_object_version_number > p_scn_rec.object_version_number THEN
2311 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2312 RAISE OKC_API.G_EXCEPTION_ERROR;
2313 ELSIF lc_object_version_number <> p_scn_rec.object_version_number THEN
2314 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2315 RAISE OKC_API.G_EXCEPTION_ERROR;
2316 ELSIF lc_object_version_number = -1 THEN
2317 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2318 RAISE OKC_API.G_EXCEPTION_ERROR;
2319 END IF;
2320 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2321
2322 IF (l_debug = 'Y') THEN
2323 okc_debug.log('7900: Leaving lock_row', 2);
2324 okc_debug.Reset_Indentation;
2325 END IF;
2326
2327 EXCEPTION
2328 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2329
2330 IF (l_debug = 'Y') THEN
2331 okc_debug.log('8000: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2332 okc_debug.Reset_Indentation;
2333 END IF;
2334
2335 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2336 (
2337 l_api_name,
2338 G_PKG_NAME,
2339 'OKC_API.G_RET_STS_ERROR',
2340 x_msg_count,
2341 x_msg_data,
2342 '_PVT'
2343 );
2344 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2345
2346 IF (l_debug = 'Y') THEN
2347 okc_debug.log('8100: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2348 okc_debug.Reset_Indentation;
2349 END IF;
2350
2351 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2352 (
2353 l_api_name,
2354 G_PKG_NAME,
2355 'OKC_API.G_RET_STS_UNEXP_ERROR',
2356 x_msg_count,
2357 x_msg_data,
2358 '_PVT'
2359 );
2360 WHEN OTHERS THEN
2361
2362 IF (l_debug = 'Y') THEN
2363 okc_debug.log('8200: Exiting lock_row:OTHERS Exception', 2);
2364 okc_debug.Reset_Indentation;
2365 END IF;
2366
2367 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2368 (
2369 l_api_name,
2370 G_PKG_NAME,
2371 'OTHERS',
2372 x_msg_count,
2373 x_msg_data,
2374 '_PVT'
2375 );
2376
2377 END lock_row;
2378 ----------------------------------
2379 -- lock_row for:OKC_SECTIONS_TL --
2380 ----------------------------------
2381 PROCEDURE lock_row(
2382 p_init_msg_list IN VARCHAR2,
2383 x_return_status OUT NOCOPY VARCHAR2,
2384 x_msg_count OUT NOCOPY NUMBER,
2385 x_msg_data OUT NOCOPY VARCHAR2,
2386 p_okc_sections_tl_rec IN okc_sections_tl_rec_type) IS
2387
2388 E_Resource_Busy EXCEPTION;
2389 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2390 CURSOR lock_csr (p_okc_sections_tl_rec IN okc_sections_tl_rec_type) IS
2391 SELECT *
2392 FROM OKC_SECTIONS_TL
2393 WHERE ID = p_okc_sections_tl_rec.id
2394 FOR UPDATE NOWAIT;
2395
2396 l_api_version CONSTANT NUMBER := 1;
2397 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2398 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2399 l_lock_var lock_csr%ROWTYPE;
2400 l_row_notfound BOOLEAN := FALSE;
2401 lc_row_notfound BOOLEAN := FALSE;
2402 BEGIN
2403
2404 IF (l_debug = 'Y') THEN
2405 okc_debug.Set_Indentation('OKC_SCN_PVT');
2406 okc_debug.log('8300: Entered lock_row', 2);
2407 END IF;
2408
2409 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2410 p_init_msg_list,
2411 '_PVT',
2412 x_return_status);
2413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2416 RAISE OKC_API.G_EXCEPTION_ERROR;
2417 END IF;
2418 BEGIN
2419
2420 -- okc_debug.Set_Indentation('OKC_SCN_PVT');
2421 -- okc_debug.log('8400: Entered lock_row', 2);
2422
2423 OPEN lock_csr(p_okc_sections_tl_rec);
2424 FETCH lock_csr INTO l_lock_var;
2425 l_row_notfound := lock_csr%NOTFOUND;
2426 CLOSE lock_csr;
2427
2428 EXCEPTION
2429 WHEN E_Resource_Busy THEN
2430
2431 IF (l_debug = 'Y') THEN
2432 okc_debug.log('8600: Exiting lock_row:E_Resource_Busy Exception', 2);
2433 okc_debug.Reset_Indentation;
2434 END IF;
2435
2436 IF (lock_csr%ISOPEN) THEN
2437 CLOSE lock_csr;
2438 END IF;
2439 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2440 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2441 END;
2442
2443 IF ( l_row_notfound ) THEN
2444 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2445 RAISE OKC_API.G_EXCEPTION_ERROR;
2446 END IF;
2447 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2448
2449 IF (l_debug = 'Y') THEN
2450 okc_debug.log('8700: Exiting lock_row', 2);
2451 okc_debug.Reset_Indentation;
2452 END IF;
2453
2454 EXCEPTION
2455 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2456
2457 IF (l_debug = 'Y') THEN
2458 okc_debug.log('8800: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2459 okc_debug.Reset_Indentation;
2460 END IF;
2461
2462 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2463 (
2464 l_api_name,
2465 G_PKG_NAME,
2466 'OKC_API.G_RET_STS_ERROR',
2467 x_msg_count,
2468 x_msg_data,
2469 '_PVT'
2470 );
2471 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2472
2473 IF (l_debug = 'Y') THEN
2474 okc_debug.log('8900: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2475 okc_debug.Reset_Indentation;
2476 END IF;
2477
2478 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2479 (
2480 l_api_name,
2481 G_PKG_NAME,
2482 'OKC_API.G_RET_STS_UNEXP_ERROR',
2483 x_msg_count,
2484 x_msg_data,
2485 '_PVT'
2486 );
2487 WHEN OTHERS THEN
2488
2489 IF (l_debug = 'Y') THEN
2490 okc_debug.log('9000: Exiting lock_row:OTHERS Exception', 2);
2491 okc_debug.Reset_Indentation;
2492 END IF;
2493
2494 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2495 (
2496 l_api_name,
2497 G_PKG_NAME,
2498 'OTHERS',
2499 x_msg_count,
2500 x_msg_data,
2501 '_PVT'
2502 );
2503
2504 END lock_row;
2505 ---------------------------------
2506 -- lock_row for:OKC_SECTIONS_V --
2507 ---------------------------------
2508 PROCEDURE lock_row(
2509 p_api_version IN NUMBER,
2510 p_init_msg_list IN VARCHAR2,
2511 x_return_status OUT NOCOPY VARCHAR2,
2512 x_msg_count OUT NOCOPY NUMBER,
2513 x_msg_data OUT NOCOPY VARCHAR2,
2514 p_scnv_rec IN scnv_rec_type) IS
2515
2516 l_api_version CONSTANT NUMBER := 1;
2517 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2518 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2519 l_scn_rec scn_rec_type;
2520 l_okc_sections_tl_rec okc_sections_tl_rec_type;
2521 BEGIN
2522
2523 IF (l_debug = 'Y') THEN
2524 okc_debug.Set_Indentation('OKC_SCN_PVT');
2525 okc_debug.log('9100: Entered lock_row', 2);
2526 END IF;
2527
2528 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2529 G_PKG_NAME,
2530 p_init_msg_list,
2531 l_api_version,
2532 p_api_version,
2533 '_PVT',
2534 x_return_status);
2535 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2536 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2537 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2538 RAISE OKC_API.G_EXCEPTION_ERROR;
2539 END IF;
2540 --------------------------------------
2541 -- Move VIEW record to "Child" records
2542 --------------------------------------
2543 migrate(p_scnv_rec, l_scn_rec);
2544 migrate(p_scnv_rec, l_okc_sections_tl_rec);
2545 --------------------------------------------
2546 -- Call the LOCK_ROW for each child record
2547 --------------------------------------------
2548 lock_row(
2549 p_init_msg_list,
2550 x_return_status,
2551 x_msg_count,
2552 x_msg_data,
2553 l_scn_rec
2554 );
2555 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2556 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2557 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2558 RAISE OKC_API.G_EXCEPTION_ERROR;
2559 END IF;
2560 lock_row(
2561 p_init_msg_list,
2562 x_return_status,
2563 x_msg_count,
2564 x_msg_data,
2565 l_okc_sections_tl_rec
2566 );
2567 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2568 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2569 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2570 RAISE OKC_API.G_EXCEPTION_ERROR;
2571 END IF;
2572 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2573
2574 IF (l_debug = 'Y') THEN
2575 okc_debug.log('9200: Leaving lock_row', 2);
2576 okc_debug.Reset_Indentation;
2577 END IF;
2578 EXCEPTION
2579 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2580
2581 IF (l_debug = 'Y') THEN
2582 okc_debug.log('9300: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2583 okc_debug.Reset_Indentation;
2584 END IF;
2585
2586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2587 (
2588 l_api_name,
2589 G_PKG_NAME,
2590 'OKC_API.G_RET_STS_ERROR',
2591 x_msg_count,
2592 x_msg_data,
2593 '_PVT'
2594 );
2595 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2596
2597 IF (l_debug = 'Y') THEN
2598 okc_debug.log('9400: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2599 okc_debug.Reset_Indentation;
2600 END IF;
2601
2602 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2603 (
2604 l_api_name,
2605 G_PKG_NAME,
2606 'OKC_API.G_RET_STS_UNEXP_ERROR',
2607 x_msg_count,
2608 x_msg_data,
2609 '_PVT'
2610 );
2611 WHEN OTHERS THEN
2612
2613 IF (l_debug = 'Y') THEN
2614 okc_debug.log('9500: Exiting lock_row:OTHERS Exception', 2);
2615 okc_debug.Reset_Indentation;
2616 END IF;
2617
2618 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2619 (
2620 l_api_name,
2621 G_PKG_NAME,
2622 'OTHERS',
2623 x_msg_count,
2624 x_msg_data,
2625 '_PVT'
2626 );
2627
2628 END lock_row;
2629 --------------------------------------
2630 -- PL/SQL TBL lock_row for:SCNV_TBL --
2631 --------------------------------------
2632 PROCEDURE lock_row(
2633 p_api_version IN NUMBER,
2634 p_init_msg_list IN VARCHAR2,
2635 x_return_status OUT NOCOPY VARCHAR2,
2636 x_msg_count OUT NOCOPY NUMBER,
2637 x_msg_data OUT NOCOPY VARCHAR2,
2638 p_scnv_tbl IN scnv_tbl_type) IS
2639
2640 l_api_version CONSTANT NUMBER := 1;
2641 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2642 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2643 i NUMBER := 0;
2644 BEGIN
2645
2646 IF (l_debug = 'Y') THEN
2647 okc_debug.Set_Indentation('OKC_SCN_PVT');
2648 okc_debug.log('9600: Entered lock_row', 2);
2649 END IF;
2650
2651 OKC_API.init_msg_list(p_init_msg_list);
2652 -- Make sure PL/SQL table has records in it before passing
2653 IF (p_scnv_tbl.COUNT > 0) THEN
2654 i := p_scnv_tbl.FIRST;
2655 LOOP
2656 lock_row (
2657 p_api_version => p_api_version,
2658 p_init_msg_list => OKC_API.G_FALSE,
2659 x_return_status => x_return_status,
2660 x_msg_count => x_msg_count,
2661 x_msg_data => x_msg_data,
2662 p_scnv_rec => p_scnv_tbl(i));
2663 EXIT WHEN (i = p_scnv_tbl.LAST);
2664 i := p_scnv_tbl.NEXT(i);
2665 END LOOP;
2666 END IF;
2667
2668 IF (l_debug = 'Y') THEN
2669 okc_debug.log('9700: Leaving lock_row', 2);
2670 okc_debug.Reset_Indentation;
2671 END IF;
2672
2673 EXCEPTION
2674 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2675
2676 IF (l_debug = 'Y') THEN
2677 okc_debug.log('9800: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2678 okc_debug.Reset_Indentation;
2679 END IF;
2680
2681 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2682 (
2683 l_api_name,
2684 G_PKG_NAME,
2685 'OKC_API.G_RET_STS_ERROR',
2686 x_msg_count,
2687 x_msg_data,
2688 '_PVT'
2689 );
2690 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2691
2692 IF (l_debug = 'Y') THEN
2693 okc_debug.log('9900: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2694 okc_debug.Reset_Indentation;
2695 END IF;
2696
2697 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2698 (
2699 l_api_name,
2700 G_PKG_NAME,
2701 'OKC_API.G_RET_STS_UNEXP_ERROR',
2702 x_msg_count,
2703 x_msg_data,
2704 '_PVT'
2705 );
2706 WHEN OTHERS THEN
2707
2708 IF (l_debug = 'Y') THEN
2709 okc_debug.log('10000: Exiting lock_row:OTHERS Exception', 2);
2710 okc_debug.Reset_Indentation;
2711 END IF;
2712
2713 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2714 (
2715 l_api_name,
2716 G_PKG_NAME,
2717 'OTHERS',
2718 x_msg_count,
2719 x_msg_data,
2720 '_PVT'
2721 );
2722
2723 END lock_row;
2724
2725 ---------------------------------------------------------------------------
2726 -- PROCEDURE update_row
2727 ---------------------------------------------------------------------------
2728 -----------------------------------
2729 -- update_row for:OKC_SECTIONS_B --
2730 -----------------------------------
2731 PROCEDURE update_row(
2732 p_init_msg_list IN VARCHAR2,
2733 x_return_status OUT NOCOPY VARCHAR2,
2734 x_msg_count OUT NOCOPY NUMBER,
2735 x_msg_data OUT NOCOPY VARCHAR2,
2736 p_scn_rec IN scn_rec_type,
2737 x_scn_rec OUT NOCOPY scn_rec_type) IS
2738
2739 l_api_version CONSTANT NUMBER := 1;
2740 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2741 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2742 l_scn_rec scn_rec_type := p_scn_rec;
2743 l_def_scn_rec scn_rec_type;
2744 l_row_notfound BOOLEAN := TRUE;
2745 ----------------------------------
2746 -- FUNCTION populate_new_record --
2747 ----------------------------------
2748 FUNCTION populate_new_record (
2749 p_scn_rec IN scn_rec_type,
2750 x_scn_rec OUT NOCOPY scn_rec_type
2751 ) RETURN VARCHAR2 IS
2752 l_scn_rec scn_rec_type;
2753 l_row_notfound BOOLEAN := TRUE;
2754 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2755 BEGIN
2756
2757 IF (l_debug = 'Y') THEN
2758 okc_debug.Set_Indentation('OKC_SCN_PVT');
2759 okc_debug.log('10100: Entered populate_new_record', 2);
2760 END IF;
2761
2762 x_scn_rec := p_scn_rec;
2763 -- Get current database values
2764 l_scn_rec := get_rec(p_scn_rec, l_row_notfound);
2765 IF (l_row_notfound) THEN
2766 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2767 END IF;
2768 IF (x_scn_rec.id = OKC_API.G_MISS_NUM)
2769 THEN
2770 x_scn_rec.id := l_scn_rec.id;
2771 END IF;
2772 IF (x_scn_rec.scn_type = OKC_API.G_MISS_CHAR)
2773 THEN
2774 x_scn_rec.scn_type := l_scn_rec.scn_type;
2775 END IF;
2776 IF (x_scn_rec.chr_id = OKC_API.G_MISS_NUM)
2777 THEN
2778 x_scn_rec.chr_id := l_scn_rec.chr_id;
2779 END IF;
2780 IF (x_scn_rec.sat_code = OKC_API.G_MISS_CHAR)
2781 THEN
2782 x_scn_rec.sat_code := l_scn_rec.sat_code;
2783 END IF;
2784 IF (x_scn_rec.section_sequence = OKC_API.G_MISS_NUM)
2785 THEN
2786 x_scn_rec.section_sequence := l_scn_rec.section_sequence;
2787 END IF;
2788 IF (x_scn_rec.object_version_number = OKC_API.G_MISS_NUM)
2789 THEN
2790 x_scn_rec.object_version_number := l_scn_rec.object_version_number;
2791 END IF;
2792 IF (x_scn_rec.created_by = OKC_API.G_MISS_NUM)
2793 THEN
2794 x_scn_rec.created_by := l_scn_rec.created_by;
2795 END IF;
2796 IF (x_scn_rec.creation_date = OKC_API.G_MISS_DATE)
2797 THEN
2798 x_scn_rec.creation_date := l_scn_rec.creation_date;
2799 END IF;
2800 IF (x_scn_rec.last_updated_by = OKC_API.G_MISS_NUM)
2801 THEN
2802 x_scn_rec.last_updated_by := l_scn_rec.last_updated_by;
2803 END IF;
2804 IF (x_scn_rec.last_update_date = OKC_API.G_MISS_DATE)
2805 THEN
2806 x_scn_rec.last_update_date := l_scn_rec.last_update_date;
2807 END IF;
2808 IF (x_scn_rec.last_update_login = OKC_API.G_MISS_NUM)
2809 THEN
2810 x_scn_rec.last_update_login := l_scn_rec.last_update_login;
2811 END IF;
2812 IF (x_scn_rec.label = OKC_API.G_MISS_CHAR)
2813 THEN
2814 x_scn_rec.label := l_scn_rec.label;
2815 END IF;
2816 IF (x_scn_rec.scn_id = OKC_API.G_MISS_NUM)
2817 THEN
2818 x_scn_rec.scn_id := l_scn_rec.scn_id;
2819 END IF;
2820 IF (x_scn_rec.attribute_category = OKC_API.G_MISS_CHAR)
2821 THEN
2822 x_scn_rec.attribute_category := l_scn_rec.attribute_category;
2823 END IF;
2824 IF (x_scn_rec.attribute1 = OKC_API.G_MISS_CHAR)
2825 THEN
2826 x_scn_rec.attribute1 := l_scn_rec.attribute1;
2827 END IF;
2828 IF (x_scn_rec.attribute2 = OKC_API.G_MISS_CHAR)
2829 THEN
2830 x_scn_rec.attribute2 := l_scn_rec.attribute2;
2831 END IF;
2832 IF (x_scn_rec.attribute3 = OKC_API.G_MISS_CHAR)
2833 THEN
2834 x_scn_rec.attribute3 := l_scn_rec.attribute3;
2835 END IF;
2836 IF (x_scn_rec.attribute4 = OKC_API.G_MISS_CHAR)
2837 THEN
2838 x_scn_rec.attribute4 := l_scn_rec.attribute4;
2839 END IF;
2840 IF (x_scn_rec.attribute5 = OKC_API.G_MISS_CHAR)
2841 THEN
2842 x_scn_rec.attribute5 := l_scn_rec.attribute5;
2843 END IF;
2844 IF (x_scn_rec.attribute6 = OKC_API.G_MISS_CHAR)
2845 THEN
2846 x_scn_rec.attribute6 := l_scn_rec.attribute6;
2847 END IF;
2848 IF (x_scn_rec.attribute7 = OKC_API.G_MISS_CHAR)
2849 THEN
2850 x_scn_rec.attribute7 := l_scn_rec.attribute7;
2851 END IF;
2852 IF (x_scn_rec.attribute8 = OKC_API.G_MISS_CHAR)
2853 THEN
2854 x_scn_rec.attribute8 := l_scn_rec.attribute8;
2855 END IF;
2856 IF (x_scn_rec.attribute9 = OKC_API.G_MISS_CHAR)
2857 THEN
2858 x_scn_rec.attribute9 := l_scn_rec.attribute9;
2859 END IF;
2860 IF (x_scn_rec.attribute10 = OKC_API.G_MISS_CHAR)
2861 THEN
2862 x_scn_rec.attribute10 := l_scn_rec.attribute10;
2863 END IF;
2864 IF (x_scn_rec.attribute11 = OKC_API.G_MISS_CHAR)
2865 THEN
2866 x_scn_rec.attribute11 := l_scn_rec.attribute11;
2867 END IF;
2868 IF (x_scn_rec.attribute12 = OKC_API.G_MISS_CHAR)
2869 THEN
2870 x_scn_rec.attribute12 := l_scn_rec.attribute12;
2871 END IF;
2872 IF (x_scn_rec.attribute13 = OKC_API.G_MISS_CHAR)
2873 THEN
2874 x_scn_rec.attribute13 := l_scn_rec.attribute13;
2875 END IF;
2876 IF (x_scn_rec.attribute14 = OKC_API.G_MISS_CHAR)
2877 THEN
2878 x_scn_rec.attribute14 := l_scn_rec.attribute14;
2879 END IF;
2880 IF (x_scn_rec.attribute15 = OKC_API.G_MISS_CHAR)
2881 THEN
2882 x_scn_rec.attribute15 := l_scn_rec.attribute15;
2883 END IF;
2884
2885 IF (l_debug = 'Y') THEN
2886 okc_debug.log('11950: Leaving populate_new_record ', 2);
2887 okc_debug.Reset_Indentation;
2888 END IF;
2889
2890 RETURN(l_return_status);
2891 END populate_new_record;
2892 ---------------------------------------
2893 -- Set_Attributes for:OKC_SECTIONS_B --
2894 ---------------------------------------
2895 FUNCTION Set_Attributes (
2896 p_scn_rec IN scn_rec_type,
2897 x_scn_rec OUT NOCOPY scn_rec_type
2898 ) RETURN VARCHAR2 IS
2899 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2900 BEGIN
2901
2902 x_scn_rec := p_scn_rec;
2903 RETURN(l_return_status);
2904
2905 END Set_Attributes;
2906 BEGIN
2907
2908 IF (l_debug = 'Y') THEN
2909 okc_debug.Set_Indentation('OKC_SCN_PVT');
2910 okc_debug.log('10300: Entered update_row', 2);
2911 END IF;
2912
2913 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2914 p_init_msg_list,
2915 '_PVT',
2916 x_return_status);
2917 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2918 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2919 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2920 RAISE OKC_API.G_EXCEPTION_ERROR;
2921 END IF;
2922 --- Setting item attributes
2923 l_return_status := Set_Attributes(
2924 p_scn_rec, -- IN
2925 l_scn_rec); -- OUT
2926 --- If any errors happen abort API
2927 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2928 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2929 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2930 RAISE OKC_API.G_EXCEPTION_ERROR;
2931 END IF;
2932 l_return_status := populate_new_record(l_scn_rec, l_def_scn_rec);
2933 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2934 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2935 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2936 RAISE OKC_API.G_EXCEPTION_ERROR;
2937 END IF;
2938 UPDATE OKC_SECTIONS_B
2939 SET SCN_TYPE = l_def_scn_rec.scn_type,
2940 CHR_ID = l_def_scn_rec.chr_id,
2941 SAT_CODE = l_def_scn_rec.sat_code,
2942 SECTION_SEQUENCE = l_def_scn_rec.section_sequence,
2943 OBJECT_VERSION_NUMBER = l_def_scn_rec.object_version_number,
2944 CREATED_BY = l_def_scn_rec.created_by,
2945 CREATION_DATE = l_def_scn_rec.creation_date,
2946 LAST_UPDATED_BY = l_def_scn_rec.last_updated_by,
2947 LAST_UPDATE_DATE = l_def_scn_rec.last_update_date,
2948 LAST_UPDATE_LOGIN = l_def_scn_rec.last_update_login,
2949 LABEL = l_def_scn_rec.label,
2950 SCN_ID = l_def_scn_rec.scn_id,
2951 ATTRIBUTE_CATEGORY = l_def_scn_rec.attribute_category,
2952 ATTRIBUTE1 = l_def_scn_rec.attribute1,
2953 ATTRIBUTE2 = l_def_scn_rec.attribute2,
2954 ATTRIBUTE3 = l_def_scn_rec.attribute3,
2955 ATTRIBUTE4 = l_def_scn_rec.attribute4,
2956 ATTRIBUTE5 = l_def_scn_rec.attribute5,
2957 ATTRIBUTE6 = l_def_scn_rec.attribute6,
2958 ATTRIBUTE7 = l_def_scn_rec.attribute7,
2959 ATTRIBUTE8 = l_def_scn_rec.attribute8,
2960 ATTRIBUTE9 = l_def_scn_rec.attribute9,
2961 ATTRIBUTE10 = l_def_scn_rec.attribute10,
2962 ATTRIBUTE11 = l_def_scn_rec.attribute11,
2963 ATTRIBUTE12 = l_def_scn_rec.attribute12,
2964 ATTRIBUTE13 = l_def_scn_rec.attribute13,
2965 ATTRIBUTE14 = l_def_scn_rec.attribute14,
2966 ATTRIBUTE15 = l_def_scn_rec.attribute15
2967 WHERE ID = l_def_scn_rec.id;
2968
2969 x_scn_rec := l_def_scn_rec;
2970 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2971
2972 IF (l_debug = 'Y') THEN
2973 okc_debug.log('10400: Leaving update_row', 2);
2974 okc_debug.Reset_Indentation;
2975 END IF;
2976
2977 EXCEPTION
2978 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2979
2980 IF (l_debug = 'Y') THEN
2981 okc_debug.log('10500: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2982 okc_debug.Reset_Indentation;
2983 END IF;
2984
2985 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2986 (
2987 l_api_name,
2988 G_PKG_NAME,
2989 'OKC_API.G_RET_STS_ERROR',
2990 x_msg_count,
2991 x_msg_data,
2992 '_PVT'
2993 );
2994 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2995
2996 IF (l_debug = 'Y') THEN
2997 okc_debug.log('10600: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2998 okc_debug.Reset_Indentation;
2999 END IF;
3000
3001 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3002 (
3003 l_api_name,
3004 G_PKG_NAME,
3005 'OKC_API.G_RET_STS_UNEXP_ERROR',
3006 x_msg_count,
3007 x_msg_data,
3008 '_PVT'
3009 );
3010 WHEN OTHERS THEN
3011
3012 IF (l_debug = 'Y') THEN
3013 okc_debug.log('10700: Exiting update_row:OTHERS Exception', 2);
3014 okc_debug.Reset_Indentation;
3015 END IF;
3016
3017 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3018 (
3019 l_api_name,
3020 G_PKG_NAME,
3021 'OTHERS',
3022 x_msg_count,
3023 x_msg_data,
3024 '_PVT'
3025 );
3026
3027 END update_row;
3028 ------------------------------------
3029 -- update_row for:OKC_SECTIONS_TL --
3030 ------------------------------------
3031 PROCEDURE update_row(
3032 p_init_msg_list IN VARCHAR2,
3033 x_return_status OUT NOCOPY VARCHAR2,
3034 x_msg_count OUT NOCOPY NUMBER,
3035 x_msg_data OUT NOCOPY VARCHAR2,
3036 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
3037 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type) IS
3038
3039 l_api_version CONSTANT NUMBER := 1;
3040 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3041 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3042 l_okc_sections_tl_rec okc_sections_tl_rec_type := p_okc_sections_tl_rec;
3043 l_def_okc_sections_tl_rec okc_sections_tl_rec_type;
3044 l_row_notfound BOOLEAN := TRUE;
3045 ----------------------------------
3046 -- FUNCTION populate_new_record --
3047 ----------------------------------
3048 FUNCTION populate_new_record (
3049 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
3050 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type
3051 ) RETURN VARCHAR2 IS
3052 l_okc_sections_tl_rec okc_sections_tl_rec_type;
3053 l_row_notfound BOOLEAN := TRUE;
3054 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3055 BEGIN
3056
3057 IF (l_debug = 'Y') THEN
3058 okc_debug.Set_Indentation('OKC_SCN_PVT');
3059 okc_debug.log('10800: Entered populate_new_record', 2);
3060 END IF;
3061
3062 x_okc_sections_tl_rec := p_okc_sections_tl_rec;
3063 -- Get current database values
3064 l_okc_sections_tl_rec := get_rec(p_okc_sections_tl_rec, l_row_notfound);
3065 IF (l_row_notfound) THEN
3066 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3067 END IF;
3068 IF (x_okc_sections_tl_rec.id = OKC_API.G_MISS_NUM)
3069 THEN
3070 x_okc_sections_tl_rec.id := l_okc_sections_tl_rec.id;
3071 END IF;
3072 IF (x_okc_sections_tl_rec.language = OKC_API.G_MISS_CHAR)
3073 THEN
3074 x_okc_sections_tl_rec.language := l_okc_sections_tl_rec.language;
3075 END IF;
3076 IF (x_okc_sections_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
3077 THEN
3078 x_okc_sections_tl_rec.source_lang := l_okc_sections_tl_rec.source_lang;
3079 END IF;
3080 IF (x_okc_sections_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3081 THEN
3082 x_okc_sections_tl_rec.sfwt_flag := l_okc_sections_tl_rec.sfwt_flag;
3083 END IF;
3084 IF (x_okc_sections_tl_rec.heading = OKC_API.G_MISS_CHAR)
3085 THEN
3086 x_okc_sections_tl_rec.heading := l_okc_sections_tl_rec.heading;
3087 END IF;
3088 IF (x_okc_sections_tl_rec.created_by = OKC_API.G_MISS_NUM)
3089 THEN
3090 x_okc_sections_tl_rec.created_by := l_okc_sections_tl_rec.created_by;
3091 END IF;
3092 IF (x_okc_sections_tl_rec.creation_date = OKC_API.G_MISS_DATE)
3093 THEN
3094 x_okc_sections_tl_rec.creation_date := l_okc_sections_tl_rec.creation_date;
3095 END IF;
3096 IF (x_okc_sections_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3097 THEN
3098 x_okc_sections_tl_rec.last_updated_by := l_okc_sections_tl_rec.last_updated_by;
3099 END IF;
3100 IF (x_okc_sections_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
3101 THEN
3102 x_okc_sections_tl_rec.last_update_date := l_okc_sections_tl_rec.last_update_date;
3103 END IF;
3104 IF (x_okc_sections_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
3105 THEN
3106 x_okc_sections_tl_rec.last_update_login := l_okc_sections_tl_rec.last_update_login;
3107 END IF;
3108
3109 IF (l_debug = 'Y') THEN
3110 okc_debug.log('11950: Leaving populate_new_record ', 2);
3111 okc_debug.Reset_Indentation;
3112 END IF;
3113
3114 RETURN(l_return_status);
3115 END populate_new_record;
3116 ----------------------------------------
3117 -- Set_Attributes for:OKC_SECTIONS_TL --
3118 ----------------------------------------
3119 FUNCTION Set_Attributes (
3120 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
3121 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type
3122 ) RETURN VARCHAR2 IS
3123 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3124 BEGIN
3125
3126 x_okc_sections_tl_rec := p_okc_sections_tl_rec;
3127 x_okc_sections_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3128 x_okc_sections_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
3129 RETURN(l_return_status);
3130
3131 END Set_Attributes;
3132 BEGIN
3133
3134 IF (l_debug = 'Y') THEN
3135 okc_debug.Set_Indentation('OKC_SCN_PVT');
3136 okc_debug.log('11000: Entered update_row', 2);
3137 END IF;
3138
3139 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3140 p_init_msg_list,
3141 '_PVT',
3142 x_return_status);
3143 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3144 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3145 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3146 RAISE OKC_API.G_EXCEPTION_ERROR;
3147 END IF;
3148 --- Setting item attributes
3149 l_return_status := Set_Attributes(
3150 p_okc_sections_tl_rec, -- IN
3151 l_okc_sections_tl_rec); -- OUT
3152 --- If any errors happen abort API
3153 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3154 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3155 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3156 RAISE OKC_API.G_EXCEPTION_ERROR;
3157 END IF;
3158 l_return_status := populate_new_record(l_okc_sections_tl_rec, l_def_okc_sections_tl_rec);
3159 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3160 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3161 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3162 RAISE OKC_API.G_EXCEPTION_ERROR;
3163 END IF;
3164 UPDATE OKC_SECTIONS_TL
3165 SET HEADING = l_def_okc_sections_tl_rec.heading,
3166 CREATED_BY = l_def_okc_sections_tl_rec.created_by,
3167 CREATION_DATE = l_def_okc_sections_tl_rec.creation_date,
3168 LAST_UPDATED_BY = l_def_okc_sections_tl_rec.last_updated_by,
3169 LAST_UPDATE_DATE = l_def_okc_sections_tl_rec.last_update_date,
3170 LAST_UPDATE_LOGIN = l_def_okc_sections_tl_rec.last_update_login
3171 WHERE ID = l_def_okc_sections_tl_rec.id
3172 AND SOURCE_LANG = USERENV('LANG');
3173
3174 UPDATE OKC_SECTIONS_TL
3175 SET SFWT_FLAG = 'Y'
3176 WHERE ID = l_def_okc_sections_tl_rec.id
3177 AND SOURCE_LANG <> USERENV('LANG');
3178
3179 x_okc_sections_tl_rec := l_def_okc_sections_tl_rec;
3180 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3181
3182
3183 IF (l_debug = 'Y') THEN
3184 okc_debug.log('11100: Leaving update_row', 2);
3185 okc_debug.Reset_Indentation;
3186 END IF;
3187
3188 EXCEPTION
3189 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3190
3191 IF (l_debug = 'Y') THEN
3192 okc_debug.log('11200: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3193 okc_debug.Reset_Indentation;
3194 END IF;
3195
3196 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3197 (
3198 l_api_name,
3199 G_PKG_NAME,
3200 'OKC_API.G_RET_STS_ERROR',
3201 x_msg_count,
3202 x_msg_data,
3203 '_PVT'
3204 );
3205 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3206
3207 IF (l_debug = 'Y') THEN
3208 okc_debug.log('11300: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3209 okc_debug.Reset_Indentation;
3210 END IF;
3211
3212 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3213 (
3214 l_api_name,
3215 G_PKG_NAME,
3216 'OKC_API.G_RET_STS_UNEXP_ERROR',
3217 x_msg_count,
3218 x_msg_data,
3219 '_PVT'
3220 );
3221 WHEN OTHERS THEN
3222
3223 IF (l_debug = 'Y') THEN
3224 okc_debug.log('11400: Exiting update_row:OTHERS Exception', 2);
3225 okc_debug.Reset_Indentation;
3226 END IF;
3227
3228 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3229 (
3230 l_api_name,
3231 G_PKG_NAME,
3232 'OTHERS',
3233 x_msg_count,
3234 x_msg_data,
3235 '_PVT'
3236 );
3237
3238 END update_row;
3239 -----------------------------------
3240 -- update_row for:OKC_SECTIONS_V --
3241 -----------------------------------
3242 PROCEDURE update_row(
3243 p_api_version IN NUMBER,
3244 p_init_msg_list IN VARCHAR2,
3245 x_return_status OUT NOCOPY VARCHAR2,
3246 x_msg_count OUT NOCOPY NUMBER,
3247 x_msg_data OUT NOCOPY VARCHAR2,
3248 p_scnv_rec IN scnv_rec_type,
3249 x_scnv_rec OUT NOCOPY scnv_rec_type) IS
3250
3251 l_api_version CONSTANT NUMBER := 1;
3252 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3253 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3254 l_scnv_rec scnv_rec_type := p_scnv_rec;
3255 l_def_scnv_rec scnv_rec_type;
3256 l_okc_sections_tl_rec okc_sections_tl_rec_type;
3257 lx_okc_sections_tl_rec okc_sections_tl_rec_type;
3258 l_scn_rec scn_rec_type;
3259 lx_scn_rec scn_rec_type;
3260 -------------------------------
3261 -- FUNCTION fill_who_columns --
3262 -------------------------------
3263 FUNCTION fill_who_columns (
3264 p_scnv_rec IN scnv_rec_type
3265 ) RETURN scnv_rec_type IS
3266 l_scnv_rec scnv_rec_type := p_scnv_rec;
3267 BEGIN
3268
3269 l_scnv_rec.LAST_UPDATE_DATE := SYSDATE;
3270 l_scnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3271 l_scnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3272 RETURN(l_scnv_rec);
3273
3274 END fill_who_columns;
3275 ----------------------------------
3276 -- FUNCTION populate_new_record --
3277 ----------------------------------
3278 FUNCTION populate_new_record (
3279 p_scnv_rec IN scnv_rec_type,
3280 x_scnv_rec OUT NOCOPY scnv_rec_type
3281 ) RETURN VARCHAR2 IS
3282 l_scnv_rec scnv_rec_type;
3283 l_row_notfound BOOLEAN := TRUE;
3284 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3285 BEGIN
3286
3287 IF (l_debug = 'Y') THEN
3288 okc_debug.Set_Indentation('OKC_SCN_PVT');
3289 okc_debug.log('11600: Entered populate_new_record', 2);
3290 END IF;
3291
3292 x_scnv_rec := p_scnv_rec;
3293 -- Get current database values
3294 l_scnv_rec := get_rec(p_scnv_rec, l_row_notfound);
3295 IF (l_row_notfound) THEN
3296 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3297 END IF;
3298 IF (x_scnv_rec.id = OKC_API.G_MISS_NUM)
3299 THEN
3300 x_scnv_rec.id := l_scnv_rec.id;
3301 END IF;
3302 IF (x_scnv_rec.scn_type = OKC_API.G_MISS_CHAR)
3303 THEN
3304 x_scnv_rec.scn_type := l_scnv_rec.scn_type;
3305 END IF;
3306 IF (x_scnv_rec.chr_id = OKC_API.G_MISS_NUM)
3307 THEN
3308 x_scnv_rec.chr_id := l_scnv_rec.chr_id;
3309 END IF;
3310 IF (x_scnv_rec.sat_code = OKC_API.G_MISS_CHAR)
3311 THEN
3312 x_scnv_rec.sat_code := l_scnv_rec.sat_code;
3313 END IF;
3314 IF (x_scnv_rec.section_sequence = OKC_API.G_MISS_NUM)
3315 THEN
3316 x_scnv_rec.section_sequence := l_scnv_rec.section_sequence;
3317 END IF;
3318 IF (x_scnv_rec.label = OKC_API.G_MISS_CHAR)
3319 THEN
3320 x_scnv_rec.label := l_scnv_rec.label;
3321 END IF;
3322 IF (x_scnv_rec.heading = OKC_API.G_MISS_CHAR)
3323 THEN
3324 x_scnv_rec.heading := l_scnv_rec.heading;
3325 END IF;
3326 IF (x_scnv_rec.scn_id = OKC_API.G_MISS_NUM)
3327 THEN
3328 x_scnv_rec.scn_id := l_scnv_rec.scn_id;
3329 END IF;
3330 IF (x_scnv_rec.object_version_number = OKC_API.G_MISS_NUM)
3331 THEN
3332 x_scnv_rec.object_version_number := l_scnv_rec.object_version_number;
3333 END IF;
3334 IF (x_scnv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3335 THEN
3336 x_scnv_rec.sfwt_flag := l_scnv_rec.sfwt_flag;
3337 END IF;
3338 IF (x_scnv_rec.created_by = OKC_API.G_MISS_NUM)
3339 THEN
3340 x_scnv_rec.created_by := l_scnv_rec.created_by;
3341 END IF;
3342 IF (x_scnv_rec.creation_date = OKC_API.G_MISS_DATE)
3343 THEN
3344 x_scnv_rec.creation_date := l_scnv_rec.creation_date;
3345 END IF;
3346 IF (x_scnv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3347 THEN
3348 x_scnv_rec.last_updated_by := l_scnv_rec.last_updated_by;
3349 END IF;
3350 IF (x_scnv_rec.last_update_date = OKC_API.G_MISS_DATE)
3351 THEN
3352 x_scnv_rec.last_update_date := l_scnv_rec.last_update_date;
3353 END IF;
3354 IF (x_scnv_rec.last_update_login = OKC_API.G_MISS_NUM)
3355 THEN
3356 x_scnv_rec.last_update_login := l_scnv_rec.last_update_login;
3357 END IF;
3358 IF (x_scnv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3359 THEN
3360 x_scnv_rec.attribute_category := l_scnv_rec.attribute_category;
3361 END IF;
3362 IF (x_scnv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3363 THEN
3364 x_scnv_rec.attribute1 := l_scnv_rec.attribute1;
3365 END IF;
3366 IF (x_scnv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3367 THEN
3368 x_scnv_rec.attribute2 := l_scnv_rec.attribute2;
3369 END IF;
3370 IF (x_scnv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3371 THEN
3372 x_scnv_rec.attribute3 := l_scnv_rec.attribute3;
3373 END IF;
3374 IF (x_scnv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3375 THEN
3376 x_scnv_rec.attribute4 := l_scnv_rec.attribute4;
3377 END IF;
3378 IF (x_scnv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3379 THEN
3380 x_scnv_rec.attribute5 := l_scnv_rec.attribute5;
3381 END IF;
3382 IF (x_scnv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3383 THEN
3384 x_scnv_rec.attribute6 := l_scnv_rec.attribute6;
3385 END IF;
3386 IF (x_scnv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3387 THEN
3388 x_scnv_rec.attribute7 := l_scnv_rec.attribute7;
3389 END IF;
3390 IF (x_scnv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3391 THEN
3392 x_scnv_rec.attribute8 := l_scnv_rec.attribute8;
3393 END IF;
3394 IF (x_scnv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3395 THEN
3396 x_scnv_rec.attribute9 := l_scnv_rec.attribute9;
3397 END IF;
3398 IF (x_scnv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3399 THEN
3400 x_scnv_rec.attribute10 := l_scnv_rec.attribute10;
3401 END IF;
3402 IF (x_scnv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3403 THEN
3404 x_scnv_rec.attribute11 := l_scnv_rec.attribute11;
3405 END IF;
3406 IF (x_scnv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3407 THEN
3408 x_scnv_rec.attribute12 := l_scnv_rec.attribute12;
3409 END IF;
3410 IF (x_scnv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3411 THEN
3412 x_scnv_rec.attribute13 := l_scnv_rec.attribute13;
3413 END IF;
3414 IF (x_scnv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3415 THEN
3416 x_scnv_rec.attribute14 := l_scnv_rec.attribute14;
3417 END IF;
3418 IF (x_scnv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3419 THEN
3420 x_scnv_rec.attribute15 := l_scnv_rec.attribute15;
3421 END IF;
3422
3423 IF (l_debug = 'Y') THEN
3424 okc_debug.log('11100: Leaving update_row', 2);
3425 okc_debug.Reset_Indentation;
3426 END IF;
3427
3428 RETURN(l_return_status);
3429
3430 END populate_new_record;
3431 ---------------------------------------
3432 -- Set_Attributes for:OKC_SECTIONS_V --
3433 ---------------------------------------
3434 FUNCTION Set_Attributes (
3435 p_scnv_rec IN scnv_rec_type,
3436 x_scnv_rec OUT NOCOPY scnv_rec_type
3437 ) RETURN VARCHAR2 IS
3438 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3439 BEGIN
3440
3441 x_scnv_rec := p_scnv_rec;
3442 x_scnv_rec.OBJECT_VERSION_NUMBER := NVL(x_scnv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3443 RETURN(l_return_status);
3444
3445 END Set_Attributes;
3446 BEGIN
3447
3448 IF (l_debug = 'Y') THEN
3449 okc_debug.Set_Indentation('OKC_SCN_PVT');
3450 okc_debug.log('11800: Entered update_row', 2);
3451 END IF;
3452
3453 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3454 G_PKG_NAME,
3455 p_init_msg_list,
3456 l_api_version,
3457 p_api_version,
3458 '_PVT',
3459 x_return_status);
3460 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3461 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3462 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3463 RAISE OKC_API.G_EXCEPTION_ERROR;
3464 END IF;
3465 --- Setting item attributes
3466 l_return_status := Set_Attributes(
3467 p_scnv_rec, -- IN
3468 l_scnv_rec); -- OUT
3469 --- If any errors happen abort API
3470 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3471 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3472 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3473 RAISE OKC_API.G_EXCEPTION_ERROR;
3474 END IF;
3475 l_return_status := populate_new_record(l_scnv_rec, l_def_scnv_rec);
3476 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3477 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3478 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3479 RAISE OKC_API.G_EXCEPTION_ERROR;
3480 END IF;
3481 l_def_scnv_rec := fill_who_columns(l_def_scnv_rec);
3482 --- Validate all non-missing attributes (Item Level Validation)
3483 l_return_status := Validate_Attributes(l_def_scnv_rec);
3484 --- If any errors happen abort API
3485 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3486 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3487 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3488 RAISE OKC_API.G_EXCEPTION_ERROR;
3489 END IF;
3490 l_return_status := Validate_Record(l_def_scnv_rec);
3491 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3492 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3493 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3494 RAISE OKC_API.G_EXCEPTION_ERROR;
3495 END IF;
3496
3497 --------------------------------------
3498 -- Move VIEW record to "Child" records
3499 --------------------------------------
3500 migrate(l_def_scnv_rec, l_okc_sections_tl_rec);
3501 migrate(l_def_scnv_rec, l_scn_rec);
3502 --------------------------------------------
3503 -- Call the UPDATE_ROW for each child record
3504 --------------------------------------------
3505 update_row(
3506 p_init_msg_list,
3507 x_return_status,
3508 x_msg_count,
3509 x_msg_data,
3510 l_okc_sections_tl_rec,
3511 lx_okc_sections_tl_rec
3512 );
3513 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3514 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3515 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3516 RAISE OKC_API.G_EXCEPTION_ERROR;
3517 END IF;
3518 migrate(lx_okc_sections_tl_rec, l_def_scnv_rec);
3519 update_row(
3520 p_init_msg_list,
3521 x_return_status,
3522 x_msg_count,
3523 x_msg_data,
3524 l_scn_rec,
3525 lx_scn_rec
3526 );
3527
3528 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3529 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3530 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3531 RAISE OKC_API.G_EXCEPTION_ERROR;
3532 END IF;
3533 migrate(lx_scn_rec, l_def_scnv_rec);
3534 x_scnv_rec := l_def_scnv_rec;
3535 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3536
3537 IF (l_debug = 'Y') THEN
3538 okc_debug.log('11900: Leaving update_row', 2);
3539 okc_debug.Reset_Indentation;
3540 END IF;
3541
3542 EXCEPTION
3543 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3544
3545 IF (l_debug = 'Y') THEN
3546 okc_debug.log('12000: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3547 okc_debug.Reset_Indentation;
3548 END IF;
3549
3550 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3551 (
3552 l_api_name,
3553 G_PKG_NAME,
3554 'OKC_API.G_RET_STS_ERROR',
3555 x_msg_count,
3556 x_msg_data,
3557 '_PVT'
3558 );
3559 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3560
3561 IF (l_debug = 'Y') THEN
3562 okc_debug.log('12100: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3563 okc_debug.Reset_Indentation;
3564 END IF;
3565
3566 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3567 (
3568 l_api_name,
3569 G_PKG_NAME,
3570 'OKC_API.G_RET_STS_UNEXP_ERROR',
3571 x_msg_count,
3572 x_msg_data,
3573 '_PVT'
3574 );
3575 WHEN OTHERS THEN
3576
3577 IF (l_debug = 'Y') THEN
3578 okc_debug.log('12200: Exiting update_row:OTHERS Exception', 2);
3579 okc_debug.Reset_Indentation;
3580 END IF;
3581
3582 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3583 (
3584 l_api_name,
3585 G_PKG_NAME,
3586 'OTHERS',
3587 x_msg_count,
3588 x_msg_data,
3589 '_PVT'
3590 );
3591
3592 END update_row;
3593 ----------------------------------------
3594 -- PL/SQL TBL update_row for:SCNV_TBL --
3595 ----------------------------------------
3596 PROCEDURE update_row(
3597 p_api_version IN NUMBER,
3598 p_init_msg_list IN VARCHAR2,
3599 x_return_status OUT NOCOPY VARCHAR2,
3600 x_msg_count OUT NOCOPY NUMBER,
3601 x_msg_data OUT NOCOPY VARCHAR2,
3602 p_scnv_tbl IN scnv_tbl_type,
3603 x_scnv_tbl OUT NOCOPY scnv_tbl_type) IS
3604
3605 l_api_version CONSTANT NUMBER := 1;
3606 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3607 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3608 i NUMBER := 0;
3609 BEGIN
3610
3611 IF (l_debug = 'Y') THEN
3612 okc_debug.Set_Indentation('OKC_SCN_PVT');
3613 okc_debug.log('12300: Entered update_row', 2);
3614 END IF;
3615
3616 OKC_API.init_msg_list(p_init_msg_list);
3617 -- Make sure PL/SQL table has records in it before passing
3618 IF (p_scnv_tbl.COUNT > 0) THEN
3619 i := p_scnv_tbl.FIRST;
3620 LOOP
3621 update_row (
3622 p_api_version => p_api_version,
3623 p_init_msg_list => OKC_API.G_FALSE,
3624 x_return_status => x_return_status,
3625 x_msg_count => x_msg_count,
3626 x_msg_data => x_msg_data,
3627 p_scnv_rec => p_scnv_tbl(i),
3628 x_scnv_rec => x_scnv_tbl(i));
3629 EXIT WHEN (i = p_scnv_tbl.LAST);
3630 i := p_scnv_tbl.NEXT(i);
3631 END LOOP;
3632 END IF;
3633
3634 IF (l_debug = 'Y') THEN
3635 okc_debug.log('12400: Leaving update_row', 2);
3636 okc_debug.Reset_Indentation;
3637 END IF;
3638
3639 EXCEPTION
3640 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3641
3642 IF (l_debug = 'Y') THEN
3643 okc_debug.log('12500: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3644 okc_debug.Reset_Indentation;
3645 END IF;
3646
3647 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3648 (
3649 l_api_name,
3650 G_PKG_NAME,
3651 'OKC_API.G_RET_STS_ERROR',
3652 x_msg_count,
3653 x_msg_data,
3654 '_PVT'
3655 );
3656 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3657
3658 IF (l_debug = 'Y') THEN
3659 okc_debug.log('12600: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3660 okc_debug.Reset_Indentation;
3661 END IF;
3662
3663 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3664 (
3665 l_api_name,
3666 G_PKG_NAME,
3667 'OKC_API.G_RET_STS_UNEXP_ERROR',
3668 x_msg_count,
3669 x_msg_data,
3670 '_PVT'
3671 );
3672 WHEN OTHERS THEN
3673
3674 IF (l_debug = 'Y') THEN
3675 okc_debug.log('12700: Exiting update_row:OTHERS Exception', 2);
3676 okc_debug.Reset_Indentation;
3677 END IF;
3678
3679 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3680 (
3681 l_api_name,
3682 G_PKG_NAME,
3683 'OTHERS',
3684 x_msg_count,
3685 x_msg_data,
3686 '_PVT'
3687 );
3688
3689 END update_row;
3690
3691 ---------------------------------------------------------------------------
3692 -- PROCEDURE delete_row
3693 ---------------------------------------------------------------------------
3694 -----------------------------------
3695 -- delete_row for:OKC_SECTIONS_B --
3696 -----------------------------------
3697 PROCEDURE delete_row(
3698 p_init_msg_list IN VARCHAR2,
3699 x_return_status OUT NOCOPY VARCHAR2,
3700 x_msg_count OUT NOCOPY NUMBER,
3701 x_msg_data OUT NOCOPY VARCHAR2,
3702 p_scn_rec IN scn_rec_type) IS
3703
3704 l_api_version CONSTANT NUMBER := 1;
3705 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3706 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3707 l_scn_rec scn_rec_type:= p_scn_rec;
3708 l_row_notfound BOOLEAN := TRUE;
3709 BEGIN
3710
3711 IF (l_debug = 'Y') THEN
3712 okc_debug.Set_Indentation('OKC_SCN_PVT');
3713 okc_debug.log('12800: Entered delete_row', 2);
3714 END IF;
3715
3716 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3717 p_init_msg_list,
3718 '_PVT',
3719 x_return_status);
3720 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3721 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3722 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3723 RAISE OKC_API.G_EXCEPTION_ERROR;
3724 END IF;
3725 DELETE FROM OKC_SECTIONS_B
3726 WHERE ID = l_scn_rec.id;
3727
3728 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3729
3730 IF (l_debug = 'Y') THEN
3731 okc_debug.log('12900: Leaving delete_row', 2);
3732 okc_debug.Reset_Indentation;
3733 END IF;
3734
3735 EXCEPTION
3736 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3737
3738 IF (l_debug = 'Y') THEN
3739 okc_debug.log('13000: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3740 okc_debug.Reset_Indentation;
3741 END IF;
3742
3743 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3744 (
3745 l_api_name,
3746 G_PKG_NAME,
3747 'OKC_API.G_RET_STS_ERROR',
3748 x_msg_count,
3749 x_msg_data,
3750 '_PVT'
3751 );
3752 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3753
3754 IF (l_debug = 'Y') THEN
3755 okc_debug.log('13100: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3756 okc_debug.Reset_Indentation;
3757 END IF;
3758
3759 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3760 (
3761 l_api_name,
3762 G_PKG_NAME,
3763 'OKC_API.G_RET_STS_UNEXP_ERROR',
3764 x_msg_count,
3765 x_msg_data,
3766 '_PVT'
3767 );
3768 WHEN OTHERS THEN
3769
3770 IF (l_debug = 'Y') THEN
3771 okc_debug.log('13200: Exiting delete_row:OTHERS Exception', 2);
3772 okc_debug.Reset_Indentation;
3773 END IF;
3774
3775 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3776 (
3777 l_api_name,
3778 G_PKG_NAME,
3779 'OTHERS',
3780 x_msg_count,
3781 x_msg_data,
3782 '_PVT'
3783 );
3784
3785 END delete_row;
3786 ------------------------------------
3787 -- delete_row for:OKC_SECTIONS_TL --
3788 ------------------------------------
3789 PROCEDURE delete_row(
3790 p_init_msg_list IN VARCHAR2,
3791 x_return_status OUT NOCOPY VARCHAR2,
3792 x_msg_count OUT NOCOPY NUMBER,
3793 x_msg_data OUT NOCOPY VARCHAR2,
3794 p_okc_sections_tl_rec IN okc_sections_tl_rec_type) IS
3795
3796 l_api_version CONSTANT NUMBER := 1;
3797 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3798 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3799 l_okc_sections_tl_rec okc_sections_tl_rec_type:= p_okc_sections_tl_rec;
3800 l_row_notfound BOOLEAN := TRUE;
3801 ----------------------------------------
3802 -- Set_Attributes for:OKC_SECTIONS_TL --
3803 ----------------------------------------
3804 FUNCTION Set_Attributes (
3805 p_okc_sections_tl_rec IN okc_sections_tl_rec_type,
3806 x_okc_sections_tl_rec OUT NOCOPY okc_sections_tl_rec_type
3807 ) RETURN VARCHAR2 IS
3808 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3809 BEGIN
3810
3811 x_okc_sections_tl_rec := p_okc_sections_tl_rec;
3812 x_okc_sections_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3813 RETURN(l_return_status);
3814
3815 END Set_Attributes;
3816 BEGIN
3817
3818 IF (l_debug = 'Y') THEN
3819 okc_debug.Set_Indentation('OKC_SCN_PVT');
3820 okc_debug.log('13400: Entered delete_row', 2);
3821 END IF;
3822
3823 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3824 p_init_msg_list,
3825 '_PVT',
3826 x_return_status);
3827 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3828 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3829 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3830 RAISE OKC_API.G_EXCEPTION_ERROR;
3831 END IF;
3832 --- Setting item attributes
3833 l_return_status := Set_Attributes(
3834 p_okc_sections_tl_rec, -- IN
3835 l_okc_sections_tl_rec); -- OUT
3836 --- If any errors happen abort API
3837 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3838 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3839 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3840 RAISE OKC_API.G_EXCEPTION_ERROR;
3841 END IF;
3842 DELETE FROM OKC_SECTIONS_TL
3843 WHERE ID = l_okc_sections_tl_rec.id;
3844
3845 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3846
3847 IF (l_debug = 'Y') THEN
3848 okc_debug.log('13500: Leaving delete_row', 2);
3849 okc_debug.Reset_Indentation;
3850 END IF;
3851
3852 EXCEPTION
3853 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3854
3855 IF (l_debug = 'Y') THEN
3856 okc_debug.log('13600: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3857 okc_debug.Reset_Indentation;
3858 END IF;
3859
3860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3861 (
3862 l_api_name,
3863 G_PKG_NAME,
3864 'OKC_API.G_RET_STS_ERROR',
3865 x_msg_count,
3866 x_msg_data,
3867 '_PVT'
3868 );
3869 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3870
3871 IF (l_debug = 'Y') THEN
3872 okc_debug.log('13700: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3873 okc_debug.Reset_Indentation;
3874 END IF;
3875
3876 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3877 (
3878 l_api_name,
3879 G_PKG_NAME,
3880 'OKC_API.G_RET_STS_UNEXP_ERROR',
3881 x_msg_count,
3882 x_msg_data,
3883 '_PVT'
3884 );
3885 WHEN OTHERS THEN
3886
3887 IF (l_debug = 'Y') THEN
3888 okc_debug.log('13800: Exiting delete_row:OTHERS Exception', 2);
3889 okc_debug.Reset_Indentation;
3890 END IF;
3891
3892 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3893 (
3894 l_api_name,
3895 G_PKG_NAME,
3896 'OTHERS',
3897 x_msg_count,
3898 x_msg_data,
3899 '_PVT'
3900 );
3901
3902 END delete_row;
3903 -----------------------------------
3904 -- delete_row for:OKC_SECTIONS_V --
3905 -----------------------------------
3906 PROCEDURE delete_row(
3907 p_api_version IN NUMBER,
3908 p_init_msg_list IN VARCHAR2,
3909 x_return_status OUT NOCOPY VARCHAR2,
3910 x_msg_count OUT NOCOPY NUMBER,
3911 x_msg_data OUT NOCOPY VARCHAR2,
3912 p_scnv_rec IN scnv_rec_type) IS
3913
3914 l_api_version CONSTANT NUMBER := 1;
3915 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3916 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3917 l_scnv_rec scnv_rec_type := p_scnv_rec;
3918 l_okc_sections_tl_rec okc_sections_tl_rec_type;
3919 l_scn_rec scn_rec_type;
3920 BEGIN
3921
3922 IF (l_debug = 'Y') THEN
3923 okc_debug.Set_Indentation('OKC_SCN_PVT');
3924 okc_debug.log('13900: Entered delete_row', 2);
3925 END IF;
3926
3927 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3928 G_PKG_NAME,
3929 p_init_msg_list,
3930 l_api_version,
3931 p_api_version,
3932 '_PVT',
3933 x_return_status);
3934 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3935 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3936 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3937 RAISE OKC_API.G_EXCEPTION_ERROR;
3938 END IF;
3939 --------------------------------------
3940 -- Move VIEW record to "Child" records
3941 --------------------------------------
3942 migrate(l_scnv_rec, l_okc_sections_tl_rec);
3943 migrate(l_scnv_rec, l_scn_rec);
3944 --------------------------------------------
3945 -- Call the DELETE_ROW for each child record
3946 --------------------------------------------
3947 delete_row(
3948 p_init_msg_list,
3949 x_return_status,
3950 x_msg_count,
3951 x_msg_data,
3952 l_okc_sections_tl_rec
3953 );
3954 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3955 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3956 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3957 RAISE OKC_API.G_EXCEPTION_ERROR;
3958 END IF;
3959 delete_row(
3960 p_init_msg_list,
3961 x_return_status,
3962 x_msg_count,
3963 x_msg_data,
3964 l_scn_rec
3965 );
3966 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3967 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3968 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3969 RAISE OKC_API.G_EXCEPTION_ERROR;
3970 END IF;
3971 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3972
3973 IF (l_debug = 'Y') THEN
3974 okc_debug.log('14000: Leaving delete_row', 2);
3975 okc_debug.Reset_Indentation;
3976 END IF;
3977
3978 EXCEPTION
3979 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3980
3981 IF (l_debug = 'Y') THEN
3982 okc_debug.log('14100: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3983 okc_debug.Reset_Indentation;
3984 END IF;
3985
3986 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3987 (
3988 l_api_name,
3989 G_PKG_NAME,
3990 'OKC_API.G_RET_STS_ERROR',
3991 x_msg_count,
3992 x_msg_data,
3993 '_PVT'
3994 );
3995 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3996
3997 IF (l_debug = 'Y') THEN
3998 okc_debug.log('14200: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3999 okc_debug.Reset_Indentation;
4000 END IF;
4001
4002 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4003 (
4004 l_api_name,
4005 G_PKG_NAME,
4006 'OKC_API.G_RET_STS_UNEXP_ERROR',
4007 x_msg_count,
4008 x_msg_data,
4009 '_PVT'
4010 );
4011 WHEN OTHERS THEN
4012
4013 IF (l_debug = 'Y') THEN
4014 okc_debug.log('14300: Exiting delete_row:OTHERS Exception', 2);
4015 okc_debug.Reset_Indentation;
4016 END IF;
4017
4018 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4019 (
4020 l_api_name,
4021 G_PKG_NAME,
4022 'OTHERS',
4023 x_msg_count,
4024 x_msg_data,
4025 '_PVT'
4026 );
4027
4028 END delete_row;
4029 ----------------------------------------
4030 -- PL/SQL TBL delete_row for:SCNV_TBL --
4031 ----------------------------------------
4032 PROCEDURE delete_row(
4033 p_api_version IN NUMBER,
4034 p_init_msg_list IN VARCHAR2,
4035 x_return_status OUT NOCOPY VARCHAR2,
4036 x_msg_count OUT NOCOPY NUMBER,
4037 x_msg_data OUT NOCOPY VARCHAR2,
4038 p_scnv_tbl IN scnv_tbl_type) IS
4039
4040 l_api_version CONSTANT NUMBER := 1;
4041 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4042 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4043 i NUMBER := 0;
4044 BEGIN
4045
4046 IF (l_debug = 'Y') THEN
4047 okc_debug.Set_Indentation('OKC_SCN_PVT');
4048 okc_debug.log('14400: Entered delete_row', 2);
4049 END IF;
4050
4051 OKC_API.init_msg_list(p_init_msg_list);
4052 -- Make sure PL/SQL table has records in it before passing
4053 IF (p_scnv_tbl.COUNT > 0) THEN
4054 i := p_scnv_tbl.FIRST;
4055 LOOP
4056 delete_row (
4057 p_api_version => p_api_version,
4058 p_init_msg_list => OKC_API.G_FALSE,
4059 x_return_status => x_return_status,
4060 x_msg_count => x_msg_count,
4061 x_msg_data => x_msg_data,
4062 p_scnv_rec => p_scnv_tbl(i));
4063 EXIT WHEN (i = p_scnv_tbl.LAST);
4064 i := p_scnv_tbl.NEXT(i);
4065 END LOOP;
4066 END IF;
4067
4068 IF (l_debug = 'Y') THEN
4069 okc_debug.log('14500: Leaving delete_row', 2);
4070 okc_debug.Reset_Indentation;
4071 END IF;
4072
4073 EXCEPTION
4074 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4075
4076 IF (l_debug = 'Y') THEN
4077 okc_debug.log('14600: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
4078 okc_debug.Reset_Indentation;
4079 END IF;
4080
4081 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4082 (
4083 l_api_name,
4084 G_PKG_NAME,
4085 'OKC_API.G_RET_STS_ERROR',
4086 x_msg_count,
4087 x_msg_data,
4088 '_PVT'
4089 );
4090 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4091
4092 IF (l_debug = 'Y') THEN
4093 okc_debug.log('14700: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
4094 okc_debug.Reset_Indentation;
4095 END IF;
4096
4097 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4098 (
4099 l_api_name,
4100 G_PKG_NAME,
4101 'OKC_API.G_RET_STS_UNEXP_ERROR',
4102 x_msg_count,
4103 x_msg_data,
4104 '_PVT'
4105 );
4106 WHEN OTHERS THEN
4107
4108 IF (l_debug = 'Y') THEN
4109 okc_debug.log('14800: Exiting delete_row:OTHERS Exception', 2);
4110 okc_debug.Reset_Indentation;
4111 END IF;
4112
4113 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4114 (
4115 l_api_name,
4116 G_PKG_NAME,
4117 'OTHERS',
4118 x_msg_count,
4119 x_msg_data,
4120 '_PVT'
4121 );
4122
4123 END delete_row;
4124
4125 --This function is called from versioning API OKC_VERSION_PVT
4126 --Old Location: OKCRVERB.pls
4127 --New Location: Base Table API
4128
4129 FUNCTION create_version(
4130 p_chr_id IN NUMBER,
4131 p_major_version IN NUMBER
4132 ) RETURN VARCHAR2 IS
4133
4134
4135 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4136
4137 BEGIN
4138
4139 IF (l_debug = 'Y') THEN
4140 okc_debug.Set_Indentation('OKC_SCN_PVT');
4141 okc_debug.log('14900: Entered create_version', 2);
4142 END IF;
4143
4144 INSERT INTO okc_sections_bh
4145 (
4146 major_version,
4147 id,
4148 scn_type,
4149 chr_id,
4150 sat_code,
4151 section_sequence,
4152 object_version_number,
4153 created_by,
4154 creation_date,
4155 last_updated_by,
4156 last_update_date,
4157 last_update_login,
4158 label,
4159 scn_id,
4160 attribute_category,
4161 attribute1,
4162 attribute2,
4163 attribute3,
4164 attribute4,
4165 attribute5,
4166 attribute6,
4167 attribute7,
4168 attribute8,
4169 attribute9,
4170 attribute10,
4171 attribute11,
4172 attribute12,
4173 attribute13,
4174 attribute14,
4175 attribute15
4176 )
4177 SELECT
4178 p_major_version,
4179 id,
4180 scn_type,
4181 chr_id,
4182 sat_code,
4183 section_sequence,
4184 object_version_number,
4185 created_by,
4186 creation_date,
4187 last_updated_by,
4188 last_update_date,
4189 last_update_login,
4190 label,
4191 scn_id,
4192 attribute_category,
4193 attribute1,
4194 attribute2,
4195 attribute3,
4196 attribute4,
4197 attribute5,
4198 attribute6,
4199 attribute7,
4200 attribute8,
4201 attribute9,
4202 attribute10,
4203 attribute11,
4204 attribute12,
4205 attribute13,
4206 attribute14,
4207 attribute15
4208 FROM okc_sections_b
4209 WHERE chr_id = p_chr_id;
4210
4211 --------------------------
4212 -- Versioning TL Table
4213 --------------------------
4214
4215 INSERT INTO okc_sections_tlh
4216 (
4217 major_version,
4218 id,
4219 language,
4220 source_lang,
4221 sfwt_flag,
4222 heading,
4223 created_by,
4224 creation_date,
4225 last_updated_by,
4226 last_update_date,
4227 last_update_login
4228 )
4229 SELECT
4230 p_major_version,
4231 id,
4232 language,
4233 source_lang,
4234 sfwt_flag,
4235 heading,
4236 created_by,
4237 creation_date,
4238 last_updated_by,
4239 last_update_date,
4240 last_update_login
4241 FROM okc_sections_tl
4242 WHERE id in (SELECT id
4243 FROM okc_sections_b
4244 WHERE chr_id= p_chr_id);
4245
4246 IF (l_debug = 'Y') THEN
4247 okc_debug.log('15000: Leaving create_version', 2);
4248 okc_debug.Reset_Indentation;
4249 END IF;
4250
4251 RETURN l_return_status;
4252
4253
4254 EXCEPTION
4255 -- other appropriate handlers
4256 WHEN OTHERS THEN
4257
4258 IF (l_debug = 'Y') THEN
4259 okc_debug.log('15100: Exiting create_version:OTHERS Exception', 2);
4260 okc_debug.Reset_Indentation;
4261 END IF;
4262
4263 -- store SQL error message on message stack
4264 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4265 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4266 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4267 p_token1_value => sqlcode,
4268 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4269 p_token2_value => sqlerrm);
4270
4271 -- notify UNEXPECTED error
4272 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4273 return l_return_status;
4274
4275 END create_version;
4276
4277 --This Function is called from Versioning API OKC_VERSION_PVT
4278 --Old Location:OKCRVERB.pls
4279 --New Location:Base Table API
4280
4281 FUNCTION restore_version(
4282 p_chr_id IN NUMBER,
4283 p_major_version IN NUMBER
4284 ) RETURN VARCHAR2 IS
4285
4286 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
4287
4288 BEGIN
4289
4290 IF (l_debug = 'Y') THEN
4291 okc_debug.Set_Indentation('OKC_SCN_PVT');
4292 okc_debug.log('15200: Entered restore_version', 2);
4293 END IF;
4294
4295 INSERT INTO okc_sections_tl
4296 (
4297 id,
4298 language,
4299 source_lang,
4300 sfwt_flag,
4301 heading,
4302 created_by,
4303 creation_date,
4304 last_updated_by,
4305 last_update_date,
4306 last_update_login
4307 )
4308 SELECT
4309 id,
4310 language,
4311 source_lang,
4312 sfwt_flag,
4313 heading,
4314 created_by,
4315 creation_date,
4316 last_updated_by,
4317 last_update_date,
4318 last_update_login
4319 FROM okc_sections_tlh
4320 WHERE id in (SELECT id
4321 FROM okc_sections_bh
4322 WHERE chr_id = p_chr_id)
4323 AND major_version = p_major_version;
4324
4325 -----------------------------
4326 -- Restoring for Base Table
4327 -----------------------------
4328
4329 INSERT INTO okc_sections_b
4330 (
4331 id,
4332 scn_type,
4333 chr_id,
4334 sat_code,
4335 section_sequence,
4336 object_version_number,
4337 created_by,
4338 creation_date,
4339 last_updated_by,
4340 last_update_date,
4341 last_update_login,
4342 label,
4343 scn_id,
4344 attribute_category,
4345 attribute1,
4346 attribute2,
4347 attribute3,
4348 attribute4,
4349 attribute5,
4350 attribute6,
4351 attribute7,
4352 attribute8,
4353 attribute9,
4354 attribute10,
4355 attribute11,
4356 attribute12,
4357 attribute13,
4358 attribute14,
4359 attribute15
4360 )
4361 SELECT
4362 id,
4363 scn_type,
4364 chr_id,
4365 sat_code,
4366 section_sequence,
4367 object_version_number,
4368 created_by,
4369 creation_date,
4370 last_updated_by,
4371 last_update_date,
4372 last_update_login,
4373 label,
4374 scn_id,
4375 attribute_category,
4376 attribute1,
4377 attribute2,
4378 attribute3,
4379 attribute4,
4380 attribute5,
4381 attribute6,
4382 attribute7,
4383 attribute8,
4384 attribute9,
4385 attribute10,
4386 attribute11,
4387 attribute12,
4388 attribute13,
4389 attribute14,
4390 attribute15
4391 FROM okc_sections_bh
4392 WHERE chr_id = p_chr_id
4393 AND major_version = p_major_version;
4394
4395 IF (l_debug = 'Y') THEN
4396 okc_debug.log('15300: Leaving restore_version', 2);
4397 okc_debug.Reset_Indentation;
4398 END IF;
4399
4400 RETURN l_return_status;
4401
4402 EXCEPTION
4403 -- other appropriate handlers
4404 WHEN OTHERS THEN
4405
4406 IF (l_debug = 'Y') THEN
4407 okc_debug.log('15400: Exiting restore_version:OTHERS Exception', 2);
4408 okc_debug.Reset_Indentation;
4409 END IF;
4410
4411 -- store SQL error message on message stack
4412 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4413 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4414 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4415 p_token1_value => sqlcode,
4416 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4417 p_token2_value => sqlerrm);
4418
4419 -- notify UNEXPECTED error
4420 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4421 return l_return_status;
4422
4423 END restore_version;
4424 --
4425
4426 END OKC_SCN_PVT;