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