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