[Home] [Help]
PACKAGE BODY: APPS.OKC_COR_PVT
Source
1 PACKAGE BODY OKC_COR_PVT AS
2 /* $Header: OKCSCORB.pls 120.1 2006/06/06 21:41:58 upillai noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 /*+++++++++++++Start of hand code +++++++++++++++++*/
6 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
7 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
8 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
9 g_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
10 G_EXCEPTION_HALT_VALIDATION exception;
11 /*+++++++++++++End of hand code +++++++++++++++++++*/
12 ---------------------------------------------------------------------------
13 -- FUNCTION get_seq_id
14 ---------------------------------------------------------------------------
15 FUNCTION get_seq_id RETURN NUMBER IS
16 BEGIN
17 RETURN(okc_p_util.raw_to_number(sys_guid()));
18 END get_seq_id;
19
20 ---------------------------------------------------------------------------
21 -- PROCEDURE qc
22 ---------------------------------------------------------------------------
23 PROCEDURE qc IS
24 BEGIN
25 null;
26 END qc;
27
28 ---------------------------------------------------------------------------
29 -- PROCEDURE change_version
30 ---------------------------------------------------------------------------
31 PROCEDURE change_version IS
32 BEGIN
33 null;
34 END change_version;
35
36 ---------------------------------------------------------------------------
37 -- PROCEDURE api_copy
38 ---------------------------------------------------------------------------
39 PROCEDURE api_copy IS
40 BEGIN
41 null;
42 END api_copy;
43
44 ---------------------------------------------------------------------------
45 -- PROCEDURE add_language
46 ---------------------------------------------------------------------------
47 PROCEDURE add_language IS
48 BEGIN
49
50 /* The following delete and update statements are commented out */
51 /* as a quick workaround to fix the time-consuming table handler issue */
52 /* Eventually we'll need to turn them into a separate fix_language procedure */
53 /* Refer Bugs 3723612, 4210278 and 5261743
54
55 DELETE FROM OKC_CHANGES_TL T
56 WHERE NOT EXISTS (
57 SELECT NULL
58 FROM OKC_CHANGES_B B
59 WHERE B.ID = T.ID
60 );
61
62 UPDATE OKC_CHANGES_TL T SET (
63 SHORT_DESCRIPTION,
64 CHANGE_TEXT) = (SELECT
65 B.SHORT_DESCRIPTION,
66 B.CHANGE_TEXT
67 FROM OKC_CHANGES_TL B
68 WHERE B.ID = T.ID
69 AND B.LANGUAGE = T.SOURCE_LANG)
70 WHERE (
71 T.ID,
72 T.LANGUAGE)
73 IN (SELECT
74 SUBT.ID,
75 SUBT.LANGUAGE
76 FROM OKC_CHANGES_TL SUBB, OKC_CHANGES_TL SUBT
77 WHERE SUBB.ID = SUBT.ID
78 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
79 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
80 -- Commented in favor of handcode
81 --- OR SUBB.CHANGE_TEXT <> SUBT.CHANGE_TEXT
82 --+Hand code start
83 OR ( (SUBB.CHANGE_TEXT IS NOT NULL AND SUBT.CHANGE_TEXT IS NOT NULL)
84 AND (DBMS_LOB.COMPARE(SUBB.CHANGE_TEXT,SUBT.CHANGE_TEXT) <> 0))
85 --+Hand code end
86 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
87 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
88 OR (SUBB.CHANGE_TEXT IS NULL AND SUBT.CHANGE_TEXT IS NOT NULL)
89 OR (SUBB.CHANGE_TEXT IS NOT NULL AND SUBT.CHANGE_TEXT IS NULL)
90 ));
91
92 */
93
94 /* Modifying Insert as per performance guidelines given in bug 3723874 */
95 INSERT /*+ append parallel(tt) */ INTO OKC_CHANGES_TL tt(
96 ID,
97 LANGUAGE,
98 SOURCE_LANG,
99 SFWT_FLAG,
100 SHORT_DESCRIPTION,
101 CHANGE_TEXT,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN)
107 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.* FROM
108 (SELECT /*+ no_merge ordered parallel(b) */
109 B.ID,
110 L.LANGUAGE_CODE,
111 B.SOURCE_LANG,
112 B.SFWT_FLAG,
113 B.SHORT_DESCRIPTION,
114 B.CHANGE_TEXT,
115 B.CREATED_BY,
116 B.CREATION_DATE,
117 B.LAST_UPDATED_BY,
118 B.LAST_UPDATE_DATE,
119 B.LAST_UPDATE_LOGIN
120 FROM OKC_CHANGES_TL B, FND_LANGUAGES L
121 WHERE L.INSTALLED_FLAG IN ('I', 'B')
122 AND B.LANGUAGE = USERENV('LANG')
123 ) v, OKC_CHANGES_TL T
124 WHERE T.ID(+) = v.ID
125 AND T.LANGUAGE(+) = v.LANGUAGE_CODE
126 AND T.ID IS NULL;
127
128 END add_language;
129
130 ---------------------------------------------------------------------------
131 -- FUNCTION get_rec for: OKC_CHANGES_B
132 ---------------------------------------------------------------------------
133 FUNCTION get_rec (
134 p_cor_rec IN cor_rec_type,
135 x_no_data_found OUT NOCOPY BOOLEAN
136 ) RETURN cor_rec_type IS
137 CURSOR cor_pk_csr (p_id IN NUMBER) IS
138 SELECT
139 ID,
140 CRT_ID,
141 CHANGE_SEQUENCE,
142 OBJECT_VERSION_NUMBER,
143 CREATED_BY,
144 CREATION_DATE,
145 LAST_UPDATED_BY,
146 LAST_UPDATE_DATE,
147 DATETIME_INEFFECTIVE,
148 LAST_UPDATE_LOGIN,
149 ATTRIBUTE_CATEGORY,
150 ATTRIBUTE1,
151 ATTRIBUTE2,
152 ATTRIBUTE3,
153 ATTRIBUTE4,
154 ATTRIBUTE5,
155 ATTRIBUTE6,
156 ATTRIBUTE7,
157 ATTRIBUTE8,
158 ATTRIBUTE9,
159 ATTRIBUTE10,
160 ATTRIBUTE11,
161 ATTRIBUTE12,
162 ATTRIBUTE13,
163 ATTRIBUTE14,
164 ATTRIBUTE15
165 FROM Okc_Changes_B
166 WHERE okc_changes_b.id = p_id;
167 l_cor_pk cor_pk_csr%ROWTYPE;
168 l_cor_rec cor_rec_type;
169 BEGIN
170 x_no_data_found := TRUE;
171 -- Get current database values
172 OPEN cor_pk_csr (p_cor_rec.id);
173 FETCH cor_pk_csr INTO
174 l_cor_rec.ID,
175 l_cor_rec.CRT_ID,
176 l_cor_rec.CHANGE_SEQUENCE,
177 l_cor_rec.OBJECT_VERSION_NUMBER,
178 l_cor_rec.CREATED_BY,
179 l_cor_rec.CREATION_DATE,
180 l_cor_rec.LAST_UPDATED_BY,
181 l_cor_rec.LAST_UPDATE_DATE,
182 l_cor_rec.DATETIME_INEFFECTIVE,
183 l_cor_rec.LAST_UPDATE_LOGIN,
184 l_cor_rec.ATTRIBUTE_CATEGORY,
185 l_cor_rec.ATTRIBUTE1,
186 l_cor_rec.ATTRIBUTE2,
187 l_cor_rec.ATTRIBUTE3,
188 l_cor_rec.ATTRIBUTE4,
189 l_cor_rec.ATTRIBUTE5,
190 l_cor_rec.ATTRIBUTE6,
191 l_cor_rec.ATTRIBUTE7,
192 l_cor_rec.ATTRIBUTE8,
193 l_cor_rec.ATTRIBUTE9,
194 l_cor_rec.ATTRIBUTE10,
195 l_cor_rec.ATTRIBUTE11,
196 l_cor_rec.ATTRIBUTE12,
197 l_cor_rec.ATTRIBUTE13,
198 l_cor_rec.ATTRIBUTE14,
199 l_cor_rec.ATTRIBUTE15;
200 x_no_data_found := cor_pk_csr%NOTFOUND;
201 CLOSE cor_pk_csr;
202 RETURN(l_cor_rec);
203 END get_rec;
204
205 FUNCTION get_rec (
206 p_cor_rec IN cor_rec_type
207 ) RETURN cor_rec_type IS
208 l_row_notfound BOOLEAN := TRUE;
209 BEGIN
210 RETURN(get_rec(p_cor_rec, l_row_notfound));
211 END get_rec;
212 ---------------------------------------------------------------------------
213 -- FUNCTION get_rec for: OKC_CHANGES_TL
214 ---------------------------------------------------------------------------
215 FUNCTION get_rec (
216 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
217 x_no_data_found OUT NOCOPY BOOLEAN
218 ) RETURN okc_changes_tl_rec_type IS
219 CURSOR cor_pktl_csr (p_id IN NUMBER,
220 p_language IN VARCHAR2) IS
221 SELECT
222 ID,
223 LANGUAGE,
224 SOURCE_LANG,
225 SFWT_FLAG,
226 SHORT_DESCRIPTION,
227 CHANGE_TEXT,
228 CREATED_BY,
229 CREATION_DATE,
230 LAST_UPDATED_BY,
231 LAST_UPDATE_DATE,
232 LAST_UPDATE_LOGIN
233 FROM Okc_Changes_Tl
234 WHERE okc_changes_tl.id = p_id
235 AND okc_changes_tl.language = p_language;
236 l_cor_pktl cor_pktl_csr%ROWTYPE;
237 l_okc_changes_tl_rec okc_changes_tl_rec_type;
238 BEGIN
239 x_no_data_found := TRUE;
240 -- Get current database values
241 OPEN cor_pktl_csr (p_okc_changes_tl_rec.id,
242 p_okc_changes_tl_rec.language);
243 FETCH cor_pktl_csr INTO
244 l_okc_changes_tl_rec.ID,
245 l_okc_changes_tl_rec.LANGUAGE,
246 l_okc_changes_tl_rec.SOURCE_LANG,
247 l_okc_changes_tl_rec.SFWT_FLAG,
248 l_okc_changes_tl_rec.SHORT_DESCRIPTION,
249 l_okc_changes_tl_rec.CHANGE_TEXT,
250 l_okc_changes_tl_rec.CREATED_BY,
251 l_okc_changes_tl_rec.CREATION_DATE,
252 l_okc_changes_tl_rec.LAST_UPDATED_BY,
253 l_okc_changes_tl_rec.LAST_UPDATE_DATE,
254 l_okc_changes_tl_rec.LAST_UPDATE_LOGIN;
255 x_no_data_found := cor_pktl_csr%NOTFOUND;
256 CLOSE cor_pktl_csr;
257 RETURN(l_okc_changes_tl_rec);
258 END get_rec;
259
260 FUNCTION get_rec (
261 p_okc_changes_tl_rec IN okc_changes_tl_rec_type
262 ) RETURN okc_changes_tl_rec_type IS
263 l_row_notfound BOOLEAN := TRUE;
264 BEGIN
265 RETURN(get_rec(p_okc_changes_tl_rec, l_row_notfound));
266 END get_rec;
267 ---------------------------------------------------------------------------
268 -- FUNCTION get_rec for: OKC_CHANGES_V
269 ---------------------------------------------------------------------------
270 FUNCTION get_rec (
271 p_corv_rec IN corv_rec_type,
272 x_no_data_found OUT NOCOPY BOOLEAN
273 ) RETURN corv_rec_type IS
274 CURSOR okc_corv_pk_csr (p_id IN NUMBER) IS
275 SELECT
276 ID,
277 OBJECT_VERSION_NUMBER,
278 SFWT_FLAG,
279 CRT_ID,
280 CHANGE_SEQUENCE,
281 CHANGE_TEXT,
282 SHORT_DESCRIPTION,
283 ATTRIBUTE_CATEGORY,
284 ATTRIBUTE1,
285 ATTRIBUTE2,
286 ATTRIBUTE3,
287 ATTRIBUTE4,
288 ATTRIBUTE5,
289 ATTRIBUTE6,
290 ATTRIBUTE7,
291 ATTRIBUTE8,
292 ATTRIBUTE9,
293 ATTRIBUTE10,
294 ATTRIBUTE11,
295 ATTRIBUTE12,
296 ATTRIBUTE13,
297 ATTRIBUTE14,
298 ATTRIBUTE15,
299 CREATED_BY,
300 CREATION_DATE,
301 LAST_UPDATED_BY,
302 LAST_UPDATE_DATE,
303 LAST_UPDATE_LOGIN
304 FROM Okc_Changes_V
305 WHERE okc_changes_v.id = p_id;
306 l_okc_corv_pk okc_corv_pk_csr%ROWTYPE;
307 l_corv_rec corv_rec_type;
308 BEGIN
309 x_no_data_found := TRUE;
310 -- Get current database values
311 OPEN okc_corv_pk_csr (p_corv_rec.id);
312 FETCH okc_corv_pk_csr INTO
313 l_corv_rec.ID,
314 l_corv_rec.OBJECT_VERSION_NUMBER,
315 l_corv_rec.SFWT_FLAG,
316 l_corv_rec.CRT_ID,
317 l_corv_rec.CHANGE_SEQUENCE,
318 l_corv_rec.CHANGE_TEXT,
319 l_corv_rec.SHORT_DESCRIPTION,
320 l_corv_rec.ATTRIBUTE_CATEGORY,
321 l_corv_rec.ATTRIBUTE1,
322 l_corv_rec.ATTRIBUTE2,
323 l_corv_rec.ATTRIBUTE3,
324 l_corv_rec.ATTRIBUTE4,
325 l_corv_rec.ATTRIBUTE5,
326 l_corv_rec.ATTRIBUTE6,
327 l_corv_rec.ATTRIBUTE7,
328 l_corv_rec.ATTRIBUTE8,
329 l_corv_rec.ATTRIBUTE9,
330 l_corv_rec.ATTRIBUTE10,
331 l_corv_rec.ATTRIBUTE11,
332 l_corv_rec.ATTRIBUTE12,
333 l_corv_rec.ATTRIBUTE13,
334 l_corv_rec.ATTRIBUTE14,
335 l_corv_rec.ATTRIBUTE15,
336 l_corv_rec.CREATED_BY,
337 l_corv_rec.CREATION_DATE,
338 l_corv_rec.LAST_UPDATED_BY,
339 l_corv_rec.LAST_UPDATE_DATE,
340 l_corv_rec.LAST_UPDATE_LOGIN;
341 x_no_data_found := okc_corv_pk_csr%NOTFOUND;
342 CLOSE okc_corv_pk_csr;
343 RETURN(l_corv_rec);
344 END get_rec;
345
346 FUNCTION get_rec (
347 p_corv_rec IN corv_rec_type
348 ) RETURN corv_rec_type IS
349 l_row_notfound BOOLEAN := TRUE;
350 BEGIN
351 RETURN(get_rec(p_corv_rec, l_row_notfound));
352 END get_rec;
353
354 ---------------------------------------------------
355 -- FUNCTION null_out_defaults for: OKC_CHANGES_V --
356 ---------------------------------------------------
357 FUNCTION null_out_defaults (
358 p_corv_rec IN corv_rec_type
359 ) RETURN corv_rec_type IS
360 l_corv_rec corv_rec_type := p_corv_rec;
361 BEGIN
362 IF (l_corv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
363 l_corv_rec.object_version_number := NULL;
364 END IF;
365 IF (l_corv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
366 l_corv_rec.sfwt_flag := NULL;
367 END IF;
368 IF (l_corv_rec.crt_id = OKC_API.G_MISS_NUM) THEN
369 l_corv_rec.crt_id := NULL;
370 END IF;
371 IF (l_corv_rec.change_sequence = OKC_API.G_MISS_NUM) THEN
372 l_corv_rec.change_sequence := NULL;
373 END IF;
374 ---change_text field is NULL initially
375 --- IF (l_corv_rec.change_text = OKC_API.G_MISS_NUM) THEN
376 --- l_corv_rec.change_text := NULL;
377 --- END IF;
378 IF (l_corv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
379 l_corv_rec.short_description := NULL;
380 END IF;
381 IF (l_corv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
382 l_corv_rec.attribute_category := NULL;
383 END IF;
384 IF (l_corv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
385 l_corv_rec.attribute1 := NULL;
386 END IF;
387 IF (l_corv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
388 l_corv_rec.attribute2 := NULL;
389 END IF;
390 IF (l_corv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
391 l_corv_rec.attribute3 := NULL;
392 END IF;
393 IF (l_corv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
394 l_corv_rec.attribute4 := NULL;
395 END IF;
396 IF (l_corv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
397 l_corv_rec.attribute5 := NULL;
398 END IF;
399 IF (l_corv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
400 l_corv_rec.attribute6 := NULL;
401 END IF;
402 IF (l_corv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
403 l_corv_rec.attribute7 := NULL;
404 END IF;
405 IF (l_corv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
406 l_corv_rec.attribute8 := NULL;
407 END IF;
408 IF (l_corv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
409 l_corv_rec.attribute9 := NULL;
410 END IF;
411 IF (l_corv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
412 l_corv_rec.attribute10 := NULL;
413 END IF;
414 IF (l_corv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
415 l_corv_rec.attribute11 := NULL;
416 END IF;
417 IF (l_corv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
418 l_corv_rec.attribute12 := NULL;
419 END IF;
420 IF (l_corv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
421 l_corv_rec.attribute13 := NULL;
422 END IF;
423 IF (l_corv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
424 l_corv_rec.attribute14 := NULL;
425 END IF;
426 IF (l_corv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
427 l_corv_rec.attribute15 := NULL;
428 END IF;
429 IF (l_corv_rec.created_by = OKC_API.G_MISS_NUM) THEN
430 l_corv_rec.created_by := NULL;
431 END IF;
432 IF (l_corv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
433 l_corv_rec.creation_date := NULL;
434 END IF;
435 IF (l_corv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
436 l_corv_rec.last_updated_by := NULL;
437 END IF;
438 IF (l_corv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
439 l_corv_rec.last_update_date := NULL;
440 END IF;
441 IF (l_corv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
442 l_corv_rec.last_update_login := NULL;
443 END IF;
444 RETURN(l_corv_rec);
445 END null_out_defaults;
446 ---------------------------------------------------------------------------
447 -- PROCEDURE Validate_Attributes
448 ---------------------------------------------------------------------------
449 /*+++++++++++++Start of hand code +++++++++++++++++*/
450
451 -- Start of comments
452 --
453 -- Procedure Name : validate_crt_id
454 -- Description :
455 -- Business Rules :
456 -- Parameters :
457 -- Version : 1.0
458 -- End of comments
459
460 procedure validate_crt_id(x_return_status OUT NOCOPY VARCHAR2,
461 p_corv_rec IN corv_rec_TYPE) is
462 l_dummy_var varchar2(1) := '?';
463 cursor l_crt_csr is
464 select 'x'
465 from OKC_change_requests_B
466 where id = p_corv_rec.crt_id;
467 begin
468 x_return_status := OKC_API.G_RET_STS_SUCCESS;
469 if (p_corv_rec.crt_id = OKC_API.G_MISS_NUM) then
470 return;
471 end if;
472 if (p_corv_rec.crt_id is NULL) then
473 OKC_API.set_message(p_app_name => g_app_name,
474 p_msg_name => G_REQUIRED_VALUE,
475 p_token1 => G_COL_NAME_TOKEN,
476 p_token1_value => 'CRT_ID');
477 raise G_EXCEPTION_HALT_VALIDATION;
478 end if;
479 open l_crt_csr;
480 fetch l_crt_csr into l_dummy_var;
481 close l_crt_csr;
482 if (l_dummy_var = '?') then
483 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CRT_ID');
484 raise G_EXCEPTION_HALT_VALIDATION;
485 end if;
486 exception
487 when G_EXCEPTION_HALT_VALIDATION then
488 x_return_status := OKC_API.G_RET_STS_ERROR;
489 when OTHERS then
490 if l_crt_csr%ISOPEN then
491 close l_crt_csr;
492 end if;
493 OKC_API.set_message(p_app_name => g_app_name,
494 p_msg_name => g_unexpected_error,
495 p_token1 => g_sqlcode_token,
496 p_token1_value => sqlcode,
497 p_token2 => g_sqlerrm_token,
498 p_token2_value => sqlerrm);
499 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
500 end validate_crt_id;
501
502
503 /*+++++++++++++End of hand code +++++++++++++++++++*/
504 -------------------------------------------
505 -- Validate_Attributes for:OKC_CHANGES_V --
506 -------------------------------------------
507 FUNCTION Validate_Attributes (
508 p_corv_rec IN corv_rec_type
509 ) RETURN VARCHAR2 IS
510 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
511 /*-------------Commented in favor of hand code------
512 BEGIN
513 IF p_corv_rec.id = OKC_API.G_MISS_NUM OR
514 p_corv_rec.id IS NULL
515 THEN
516 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
517 l_return_status := OKC_API.G_RET_STS_ERROR;
518 ELSIF p_corv_rec.object_version_number = OKC_API.G_MISS_NUM OR
519 p_corv_rec.object_version_number IS NULL
520 THEN
521 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
522 l_return_status := OKC_API.G_RET_STS_ERROR;
523 ELSIF p_corv_rec.change_sequence = OKC_API.G_MISS_NUM OR
524 p_corv_rec.change_sequence IS NULL
525 THEN
526 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'change_sequence');
527 l_return_status := OKC_API.G_RET_STS_ERROR;
528 END IF;
529 RETURN(l_return_status);
530 END Validate_Attributes;
531 ---------------End of the commented code-----------*/
532 /*+++++++++++++Start of hand code +++++++++++++++++*/
533 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
534 BEGIN
535 -- call each column-level validation
536 validate_crt_id(x_return_status => l_return_status,
537 p_corv_rec => p_corv_rec);
538 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
539 return OKC_API.G_RET_STS_UNEXP_ERROR;
540 end if;
541 if (l_return_status = OKC_API.G_RET_STS_ERROR
542 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
543 x_return_status := OKC_API.G_RET_STS_ERROR;
544 end if;
545 return x_return_status;
546 exception
547 when OTHERS then
548 -- store SQL error message on message stack for caller
549 OKC_API.set_message(p_app_name => g_app_name,
550 p_msg_name => g_unexpected_error,
551 p_token1 => g_sqlcode_token,
552 p_token1_value => sqlcode,
553 p_token2 => g_sqlerrm_token,
554 p_token2_value => sqlerrm);
555 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
556 return x_return_status;
557 END Validate_Attributes;
558 /*+++++++++++++End of hand code +++++++++++++++++*/
559
560 ---------------------------------------------------------------------------
561 -- PROCEDURE Validate_Record
562 ---------------------------------------------------------------------------
563 ---------------------------------------
564 -- Validate_Record for:OKC_CHANGES_V --
565 ---------------------------------------
566 FUNCTION Validate_Record (
567 p_corv_rec IN corv_rec_type
568 ) RETURN VARCHAR2 IS
569 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
570 BEGIN
571 RETURN (l_return_status);
572 END Validate_Record;
573
574 ---------------------------------------------------------------------------
575 -- PROCEDURE Migrate
576 ---------------------------------------------------------------------------
577 PROCEDURE migrate (
578 p_from IN corv_rec_type,
579 p_to IN OUT NOCOPY cor_rec_type
580 ) IS
581 BEGIN
582 p_to.id := p_from.id;
583 p_to.crt_id := p_from.crt_id;
584 p_to.change_sequence := p_from.change_sequence;
585 p_to.object_version_number := p_from.object_version_number;
586 p_to.created_by := p_from.created_by;
587 p_to.creation_date := p_from.creation_date;
588 p_to.last_updated_by := p_from.last_updated_by;
589 p_to.last_update_date := p_from.last_update_date;
590 p_to.last_update_login := p_from.last_update_login;
591 p_to.attribute_category := p_from.attribute_category;
592 p_to.attribute1 := p_from.attribute1;
593 p_to.attribute2 := p_from.attribute2;
594 p_to.attribute3 := p_from.attribute3;
595 p_to.attribute4 := p_from.attribute4;
596 p_to.attribute5 := p_from.attribute5;
597 p_to.attribute6 := p_from.attribute6;
598 p_to.attribute7 := p_from.attribute7;
599 p_to.attribute8 := p_from.attribute8;
600 p_to.attribute9 := p_from.attribute9;
601 p_to.attribute10 := p_from.attribute10;
602 p_to.attribute11 := p_from.attribute11;
603 p_to.attribute12 := p_from.attribute12;
604 p_to.attribute13 := p_from.attribute13;
605 p_to.attribute14 := p_from.attribute14;
606 p_to.attribute15 := p_from.attribute15;
607 END migrate;
608 PROCEDURE migrate (
609 p_from IN cor_rec_type,
610 p_to IN OUT NOCOPY corv_rec_type
611 ) IS
612 BEGIN
613 p_to.id := p_from.id;
614 p_to.crt_id := p_from.crt_id;
615 p_to.change_sequence := p_from.change_sequence;
616 p_to.object_version_number := p_from.object_version_number;
617 p_to.created_by := p_from.created_by;
618 p_to.creation_date := p_from.creation_date;
619 p_to.last_updated_by := p_from.last_updated_by;
620 p_to.last_update_date := p_from.last_update_date;
621 p_to.last_update_login := p_from.last_update_login;
622 p_to.attribute_category := p_from.attribute_category;
623 p_to.attribute1 := p_from.attribute1;
624 p_to.attribute2 := p_from.attribute2;
625 p_to.attribute3 := p_from.attribute3;
626 p_to.attribute4 := p_from.attribute4;
627 p_to.attribute5 := p_from.attribute5;
628 p_to.attribute6 := p_from.attribute6;
629 p_to.attribute7 := p_from.attribute7;
630 p_to.attribute8 := p_from.attribute8;
631 p_to.attribute9 := p_from.attribute9;
632 p_to.attribute10 := p_from.attribute10;
633 p_to.attribute11 := p_from.attribute11;
634 p_to.attribute12 := p_from.attribute12;
635 p_to.attribute13 := p_from.attribute13;
636 p_to.attribute14 := p_from.attribute14;
637 p_to.attribute15 := p_from.attribute15;
638 END migrate;
639 PROCEDURE migrate (
640 p_from IN corv_rec_type,
641 p_to IN OUT NOCOPY okc_changes_tl_rec_type
642 ) IS
643 BEGIN
644 p_to.id := p_from.id;
645 p_to.sfwt_flag := p_from.sfwt_flag;
646 p_to.short_description := p_from.short_description;
647 p_to.change_text := p_from.change_text;
648 p_to.created_by := p_from.created_by;
649 p_to.creation_date := p_from.creation_date;
650 p_to.last_updated_by := p_from.last_updated_by;
651 p_to.last_update_date := p_from.last_update_date;
652 p_to.last_update_login := p_from.last_update_login;
653 END migrate;
654 PROCEDURE migrate (
655 p_from IN okc_changes_tl_rec_type,
656 p_to IN OUT NOCOPY corv_rec_type
657 ) IS
658 BEGIN
659 p_to.id := p_from.id;
660 p_to.sfwt_flag := p_from.sfwt_flag;
661 p_to.short_description := p_from.short_description;
662 p_to.change_text := p_from.change_text;
663 p_to.created_by := p_from.created_by;
664 p_to.creation_date := p_from.creation_date;
665 p_to.last_updated_by := p_from.last_updated_by;
666 p_to.last_update_date := p_from.last_update_date;
667 p_to.last_update_login := p_from.last_update_login;
668 END migrate;
669
670 ---------------------------------------------------------------------------
671 -- PROCEDURE validate_row
672 ---------------------------------------------------------------------------
673 ------------------------------------
674 -- validate_row for:OKC_CHANGES_V --
675 ------------------------------------
676 PROCEDURE validate_row(
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2 ,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_corv_rec IN corv_rec_type) IS
683
684 l_api_version CONSTANT NUMBER := 1;
685 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
686 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
687 l_corv_rec corv_rec_type := p_corv_rec;
688 l_cor_rec cor_rec_type;
689 l_okc_changes_tl_rec okc_changes_tl_rec_type;
690 BEGIN
691 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
692 G_PKG_NAME,
693 p_init_msg_list,
694 l_api_version,
695 p_api_version,
696 '_PVT',
697 x_return_status);
698 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
699 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
700 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
701 RAISE OKC_API.G_EXCEPTION_ERROR;
702 END IF;
703 --- Validate all non-missing attributes (Item Level Validation)
704 l_return_status := Validate_Attributes(l_corv_rec);
705 --- If any errors happen abort API
706 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
707 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
708 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
709 RAISE OKC_API.G_EXCEPTION_ERROR;
710 END IF;
711 l_return_status := Validate_Record(l_corv_rec);
712 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
713 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
714 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
715 RAISE OKC_API.G_EXCEPTION_ERROR;
716 END IF;
717 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
718 EXCEPTION
719 WHEN OKC_API.G_EXCEPTION_ERROR THEN
720 x_return_status := OKC_API.HANDLE_EXCEPTIONS
721 (
722 l_api_name,
723 G_PKG_NAME,
724 'OKC_API.G_RET_STS_ERROR',
725 x_msg_count,
726 x_msg_data,
727 '_PVT'
728 );
729 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
730 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
731 (
732 l_api_name,
733 G_PKG_NAME,
734 'OKC_API.G_RET_STS_UNEXP_ERROR',
735 x_msg_count,
736 x_msg_data,
737 '_PVT'
738 );
739 WHEN OTHERS THEN
740 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
741 (
742 l_api_name,
743 G_PKG_NAME,
744 'OTHERS',
745 x_msg_count,
746 x_msg_data,
747 '_PVT'
748 );
749 END validate_row;
750 ------------------------------------------
751 -- PL/SQL TBL validate_row for:CORV_TBL --
752 ------------------------------------------
753 PROCEDURE validate_row(
754 p_api_version IN NUMBER,
755 p_init_msg_list IN VARCHAR2 ,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_corv_tbl IN corv_tbl_type) IS
760
761 l_api_version CONSTANT NUMBER := 1;
762 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
763 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
764 i NUMBER := 0;
765 BEGIN
766 OKC_API.init_msg_list(p_init_msg_list);
767 -- Make sure PL/SQL table has records in it before passing
768 IF (p_corv_tbl.COUNT > 0) THEN
769 i := p_corv_tbl.FIRST;
770 LOOP
771 validate_row (
772 p_api_version => p_api_version,
773 p_init_msg_list => OKC_API.G_FALSE,
774 x_return_status => x_return_status,
775 x_msg_count => x_msg_count,
776 x_msg_data => x_msg_data,
777 p_corv_rec => p_corv_tbl(i));
778 EXIT WHEN (i = p_corv_tbl.LAST);
779 i := p_corv_tbl.NEXT(i);
780 END LOOP;
781 END IF;
782 EXCEPTION
783 WHEN OKC_API.G_EXCEPTION_ERROR THEN
784 x_return_status := OKC_API.HANDLE_EXCEPTIONS
785 (
786 l_api_name,
787 G_PKG_NAME,
788 'OKC_API.G_RET_STS_ERROR',
789 x_msg_count,
790 x_msg_data,
791 '_PVT'
792 );
793 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
794 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
795 (
796 l_api_name,
797 G_PKG_NAME,
798 'OKC_API.G_RET_STS_UNEXP_ERROR',
799 x_msg_count,
800 x_msg_data,
801 '_PVT'
802 );
803 WHEN OTHERS THEN
804 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
805 (
806 l_api_name,
807 G_PKG_NAME,
808 'OTHERS',
809 x_msg_count,
810 x_msg_data,
811 '_PVT'
812 );
813 END validate_row;
814
815 ---------------------------------------------------------------------------
816 -- PROCEDURE insert_row
817 ---------------------------------------------------------------------------
818 ----------------------------------
819 -- insert_row for:OKC_CHANGES_B --
820 ----------------------------------
821 PROCEDURE insert_row(
822 p_init_msg_list IN VARCHAR2 ,
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826 p_cor_rec IN cor_rec_type,
827 x_cor_rec OUT NOCOPY cor_rec_type) IS
828
829 l_api_version CONSTANT NUMBER := 1;
830 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
831 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
832 l_cor_rec cor_rec_type := p_cor_rec;
833 l_def_cor_rec cor_rec_type;
834 --------------------------------------
835 -- Set_Attributes for:OKC_CHANGES_B --
836 --------------------------------------
837 FUNCTION Set_Attributes (
838 p_cor_rec IN cor_rec_type,
839 x_cor_rec OUT NOCOPY cor_rec_type
840 ) RETURN VARCHAR2 IS
841 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
842 BEGIN
843 x_cor_rec := p_cor_rec;
844 RETURN(l_return_status);
845 END Set_Attributes;
846 BEGIN
847 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
848 p_init_msg_list,
849 '_PVT',
850 x_return_status);
851 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
852 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
853 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
854 RAISE OKC_API.G_EXCEPTION_ERROR;
855 END IF;
856 --- Setting item attributes
857 l_return_status := Set_Attributes(
858 p_cor_rec, -- IN
859 l_cor_rec); -- OUT
860 --- If any errors happen abort API
861 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
863 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
864 RAISE OKC_API.G_EXCEPTION_ERROR;
865 END IF;
866 INSERT INTO OKC_CHANGES_B(
867 id,
868 crt_id,
869 change_sequence,
870 object_version_number,
871 created_by,
872 creation_date,
873 last_updated_by,
874 last_update_date,
875 datetime_ineffective,
876 last_update_login,
877 attribute_category,
878 attribute1,
879 attribute2,
880 attribute3,
881 attribute4,
882 attribute5,
883 attribute6,
884 attribute7,
885 attribute8,
886 attribute9,
887 attribute10,
888 attribute11,
889 attribute12,
890 attribute13,
891 attribute14,
892 attribute15)
893 VALUES (
894 l_cor_rec.id,
895 l_cor_rec.crt_id,
896 l_cor_rec.change_sequence,
897 l_cor_rec.object_version_number,
898 l_cor_rec.created_by,
899 l_cor_rec.creation_date,
900 l_cor_rec.last_updated_by,
901 l_cor_rec.last_update_date,
902 l_cor_rec.datetime_ineffective,
903 l_cor_rec.last_update_login,
904 l_cor_rec.attribute_category,
905 l_cor_rec.attribute1,
906 l_cor_rec.attribute2,
907 l_cor_rec.attribute3,
908 l_cor_rec.attribute4,
909 l_cor_rec.attribute5,
910 l_cor_rec.attribute6,
911 l_cor_rec.attribute7,
912 l_cor_rec.attribute8,
913 l_cor_rec.attribute9,
914 l_cor_rec.attribute10,
915 l_cor_rec.attribute11,
916 l_cor_rec.attribute12,
917 l_cor_rec.attribute13,
918 l_cor_rec.attribute14,
919 l_cor_rec.attribute15);
920 -- Set OUT values
921 x_cor_rec := l_cor_rec;
922 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
923 EXCEPTION
924 WHEN OKC_API.G_EXCEPTION_ERROR THEN
925 x_return_status := OKC_API.HANDLE_EXCEPTIONS
926 (
927 l_api_name,
928 G_PKG_NAME,
929 'OKC_API.G_RET_STS_ERROR',
930 x_msg_count,
931 x_msg_data,
932 '_PVT'
933 );
934 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
935 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
936 (
937 l_api_name,
938 G_PKG_NAME,
939 'OKC_API.G_RET_STS_UNEXP_ERROR',
940 x_msg_count,
941 x_msg_data,
942 '_PVT'
943 );
944 WHEN OTHERS THEN
945 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
946 (
947 l_api_name,
948 G_PKG_NAME,
949 'OTHERS',
950 x_msg_count,
951 x_msg_data,
952 '_PVT'
953 );
954 END insert_row;
955 -----------------------------------
956 -- insert_row for:OKC_CHANGES_TL --
957 -----------------------------------
958 PROCEDURE insert_row(
959 p_init_msg_list IN VARCHAR2 ,
960 x_return_status OUT NOCOPY VARCHAR2,
961 x_msg_count OUT NOCOPY NUMBER,
962 x_msg_data OUT NOCOPY VARCHAR2,
963 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
964 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type) IS
965
966 l_api_version CONSTANT NUMBER := 1;
967 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
968 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
969 l_okc_changes_tl_rec okc_changes_tl_rec_type := p_okc_changes_tl_rec;
970 l_def_okc_changes_tl_rec okc_changes_tl_rec_type;
971 CURSOR get_languages IS
972 SELECT *
973 FROM FND_LANGUAGES
974 WHERE INSTALLED_FLAG IN ('I', 'B');
975 ---------------------------------------
976 -- Set_Attributes for:OKC_CHANGES_TL --
977 ---------------------------------------
978 FUNCTION Set_Attributes (
979 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
980 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type
981 ) RETURN VARCHAR2 IS
982 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
983 BEGIN
984 x_okc_changes_tl_rec := p_okc_changes_tl_rec;
985 x_okc_changes_tl_rec.LANGUAGE := USERENV('LANG');
986 x_okc_changes_tl_rec.SOURCE_LANG := USERENV('LANG');
987 RETURN(l_return_status);
988 END Set_Attributes;
989 BEGIN
990 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
991 p_init_msg_list,
992 '_PVT',
993 x_return_status);
994 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
995 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
996 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
997 RAISE OKC_API.G_EXCEPTION_ERROR;
998 END IF;
999 --- Setting item attributes
1000 l_return_status := Set_Attributes(
1001 p_okc_changes_tl_rec, -- IN
1002 l_okc_changes_tl_rec); -- OUT
1003 --- If any errors happen abort API
1004 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1005 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1006 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1007 RAISE OKC_API.G_EXCEPTION_ERROR;
1008 END IF;
1009 FOR l_lang_rec IN get_languages LOOP
1010 l_okc_changes_tl_rec.language := l_lang_rec.language_code;
1011 INSERT INTO OKC_CHANGES_TL(
1012 id,
1013 language,
1014 source_lang,
1015 sfwt_flag,
1016 short_description,
1017 change_text,
1018 created_by,
1019 creation_date,
1020 last_updated_by,
1021 last_update_date,
1022 last_update_login)
1023 VALUES (
1024 l_okc_changes_tl_rec.id,
1025 l_okc_changes_tl_rec.language,
1026 l_okc_changes_tl_rec.source_lang,
1027 l_okc_changes_tl_rec.sfwt_flag,
1028 l_okc_changes_tl_rec.short_description,
1029 l_okc_changes_tl_rec.change_text,
1030 l_okc_changes_tl_rec.created_by,
1031 l_okc_changes_tl_rec.creation_date,
1032 l_okc_changes_tl_rec.last_updated_by,
1033 l_okc_changes_tl_rec.last_update_date,
1034 l_okc_changes_tl_rec.last_update_login);
1035 END LOOP;
1036 -- Set OUT values
1037 x_okc_changes_tl_rec := l_okc_changes_tl_rec;
1038 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1039 EXCEPTION
1040 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1041 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1042 (
1043 l_api_name,
1044 G_PKG_NAME,
1045 'OKC_API.G_RET_STS_ERROR',
1046 x_msg_count,
1047 x_msg_data,
1048 '_PVT'
1049 );
1050 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1052 (
1053 l_api_name,
1054 G_PKG_NAME,
1055 'OKC_API.G_RET_STS_UNEXP_ERROR',
1056 x_msg_count,
1057 x_msg_data,
1058 '_PVT'
1059 );
1060 WHEN OTHERS THEN
1061 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1062 (
1063 l_api_name,
1064 G_PKG_NAME,
1065 'OTHERS',
1066 x_msg_count,
1067 x_msg_data,
1068 '_PVT'
1069 );
1070 END insert_row;
1071 ----------------------------------
1072 -- insert_row for:OKC_CHANGES_V --
1073 ----------------------------------
1074 PROCEDURE insert_row(
1075 p_api_version IN NUMBER,
1076 p_init_msg_list IN VARCHAR2 ,
1077 x_return_status OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER,
1079 x_msg_data OUT NOCOPY VARCHAR2,
1080 p_corv_rec IN corv_rec_type,
1081 x_corv_rec OUT NOCOPY corv_rec_type) IS
1082
1083 l_api_version CONSTANT NUMBER := 1;
1084 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1085 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1086 l_corv_rec corv_rec_type;
1087 l_def_corv_rec corv_rec_type;
1088 l_cor_rec cor_rec_type;
1089 lx_cor_rec cor_rec_type;
1090 l_okc_changes_tl_rec okc_changes_tl_rec_type;
1091 lx_okc_changes_tl_rec okc_changes_tl_rec_type;
1092 -------------------------------
1093 -- FUNCTION fill_who_columns --
1094 -------------------------------
1095 FUNCTION fill_who_columns (
1096 p_corv_rec IN corv_rec_type
1097 ) RETURN corv_rec_type IS
1098 l_corv_rec corv_rec_type := p_corv_rec;
1099 BEGIN
1100 l_corv_rec.CREATION_DATE := SYSDATE;
1101 l_corv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1102 l_corv_rec.LAST_UPDATE_DATE := SYSDATE;
1103 l_corv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1104 l_corv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1105 RETURN(l_corv_rec);
1106 END fill_who_columns;
1107 --------------------------------------
1108 -- Set_Attributes for:OKC_CHANGES_V --
1109 --------------------------------------
1110 FUNCTION Set_Attributes (
1111 p_corv_rec IN corv_rec_type,
1112 x_corv_rec OUT NOCOPY corv_rec_type
1113 ) RETURN VARCHAR2 IS
1114 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1115 BEGIN
1116 x_corv_rec := p_corv_rec;
1117 x_corv_rec.OBJECT_VERSION_NUMBER := 1;
1118 x_corv_rec.SFWT_FLAG := 'N';
1119 RETURN(l_return_status);
1120 END Set_Attributes;
1121 BEGIN
1122 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1123 G_PKG_NAME,
1124 p_init_msg_list,
1125 l_api_version,
1126 p_api_version,
1127 '_PVT',
1128 x_return_status);
1129 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1130 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1131 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_ERROR;
1133 END IF;
1134 l_corv_rec := null_out_defaults(p_corv_rec);
1135 -- Set primary key value
1136 l_corv_rec.ID := get_seq_id;
1137 --- Setting item attributes
1138 l_return_status := Set_Attributes(
1139 l_corv_rec, -- IN
1140 l_def_corv_rec); -- OUT
1141 --- If any errors happen abort API
1142 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1143 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1144 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1145 RAISE OKC_API.G_EXCEPTION_ERROR;
1146 END IF;
1147 l_def_corv_rec := fill_who_columns(l_def_corv_rec);
1148 --- Validate all non-missing attributes (Item Level Validation)
1149 l_return_status := Validate_Attributes(l_def_corv_rec);
1150 --- If any errors happen abort API
1151 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1152 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1153 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1154 RAISE OKC_API.G_EXCEPTION_ERROR;
1155 END IF;
1156 l_return_status := Validate_Record(l_def_corv_rec);
1157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1160 RAISE OKC_API.G_EXCEPTION_ERROR;
1161 END IF;
1162 --------------------------------------
1163 -- Move VIEW record to "Child" records
1164 --------------------------------------
1165 migrate(l_def_corv_rec, l_cor_rec);
1166 migrate(l_def_corv_rec, l_okc_changes_tl_rec);
1167 --------------------------------------------
1168 -- Call the INSERT_ROW for each child record
1169 --------------------------------------------
1170 insert_row(
1171 p_init_msg_list,
1172 x_return_status,
1173 x_msg_count,
1174 x_msg_data,
1175 l_cor_rec,
1176 lx_cor_rec
1177 );
1178 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1179 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1180 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1181 RAISE OKC_API.G_EXCEPTION_ERROR;
1182 END IF;
1183 migrate(lx_cor_rec, l_def_corv_rec);
1184 insert_row(
1185 p_init_msg_list,
1186 x_return_status,
1187 x_msg_count,
1188 x_msg_data,
1189 l_okc_changes_tl_rec,
1190 lx_okc_changes_tl_rec
1191 );
1192 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1193 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1194 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1195 RAISE OKC_API.G_EXCEPTION_ERROR;
1196 END IF;
1197 migrate(lx_okc_changes_tl_rec, l_def_corv_rec);
1198 -- Set OUT values
1199 x_corv_rec := l_def_corv_rec;
1200 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1201 EXCEPTION
1202 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1203 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1204 (
1205 l_api_name,
1206 G_PKG_NAME,
1207 'OKC_API.G_RET_STS_ERROR',
1208 x_msg_count,
1209 x_msg_data,
1210 '_PVT'
1211 );
1212 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1213 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1214 (
1215 l_api_name,
1216 G_PKG_NAME,
1217 'OKC_API.G_RET_STS_UNEXP_ERROR',
1218 x_msg_count,
1219 x_msg_data,
1220 '_PVT'
1221 );
1222 WHEN OTHERS THEN
1223 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1224 (
1225 l_api_name,
1226 G_PKG_NAME,
1227 'OTHERS',
1228 x_msg_count,
1229 x_msg_data,
1230 '_PVT'
1231 );
1232 END insert_row;
1233 ----------------------------------------
1234 -- PL/SQL TBL insert_row for:CORV_TBL --
1235 ----------------------------------------
1236 PROCEDURE insert_row(
1237 p_api_version IN NUMBER,
1238 p_init_msg_list IN VARCHAR2 ,
1239 x_return_status OUT NOCOPY VARCHAR2,
1240 x_msg_count OUT NOCOPY NUMBER,
1241 x_msg_data OUT NOCOPY VARCHAR2,
1242 p_corv_tbl IN corv_tbl_type,
1243 x_corv_tbl OUT NOCOPY corv_tbl_type) IS
1244
1245 l_api_version CONSTANT NUMBER := 1;
1246 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1247 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1248 i NUMBER := 0;
1249 BEGIN
1250 OKC_API.init_msg_list(p_init_msg_list);
1251 -- Make sure PL/SQL table has records in it before passing
1252 IF (p_corv_tbl.COUNT > 0) THEN
1253 i := p_corv_tbl.FIRST;
1254 LOOP
1255 insert_row (
1256 p_api_version => p_api_version,
1257 p_init_msg_list => OKC_API.G_FALSE,
1258 x_return_status => x_return_status,
1259 x_msg_count => x_msg_count,
1260 x_msg_data => x_msg_data,
1261 p_corv_rec => p_corv_tbl(i),
1262 x_corv_rec => x_corv_tbl(i));
1263 EXIT WHEN (i = p_corv_tbl.LAST);
1264 i := p_corv_tbl.NEXT(i);
1265 END LOOP;
1266 END IF;
1267 EXCEPTION
1268 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1269 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1270 (
1271 l_api_name,
1272 G_PKG_NAME,
1273 'OKC_API.G_RET_STS_ERROR',
1274 x_msg_count,
1275 x_msg_data,
1276 '_PVT'
1277 );
1278 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1279 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1280 (
1281 l_api_name,
1282 G_PKG_NAME,
1283 'OKC_API.G_RET_STS_UNEXP_ERROR',
1284 x_msg_count,
1285 x_msg_data,
1286 '_PVT'
1287 );
1288 WHEN OTHERS THEN
1289 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1290 (
1291 l_api_name,
1292 G_PKG_NAME,
1293 'OTHERS',
1294 x_msg_count,
1295 x_msg_data,
1296 '_PVT'
1297 );
1298 END insert_row;
1299
1300 ---------------------------------------------------------------------------
1301 -- PROCEDURE lock_row
1302 ---------------------------------------------------------------------------
1303 --------------------------------
1304 -- lock_row for:OKC_CHANGES_B --
1305 --------------------------------
1306 PROCEDURE lock_row(
1307 p_init_msg_list IN VARCHAR2 ,
1308 x_return_status OUT NOCOPY VARCHAR2,
1309 x_msg_count OUT NOCOPY NUMBER,
1310 x_msg_data OUT NOCOPY VARCHAR2,
1311 p_cor_rec IN cor_rec_type) IS
1312
1313 E_Resource_Busy EXCEPTION;
1314 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1315 CURSOR lock_csr (p_cor_rec IN cor_rec_type) IS
1316 SELECT OBJECT_VERSION_NUMBER
1317 FROM OKC_CHANGES_B
1318 WHERE ID = p_cor_rec.id
1319 AND OBJECT_VERSION_NUMBER = p_cor_rec.object_version_number
1320 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1321
1322 CURSOR lchk_csr (p_cor_rec IN cor_rec_type) IS
1323 SELECT OBJECT_VERSION_NUMBER
1324 FROM OKC_CHANGES_B
1325 WHERE ID = p_cor_rec.id;
1326 l_api_version CONSTANT NUMBER := 1;
1327 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1328 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1329 l_object_version_number OKC_CHANGES_B.OBJECT_VERSION_NUMBER%TYPE;
1330 lc_object_version_number OKC_CHANGES_B.OBJECT_VERSION_NUMBER%TYPE;
1331 l_row_notfound BOOLEAN := FALSE;
1332 lc_row_notfound BOOLEAN := FALSE;
1333 BEGIN
1334 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1335 p_init_msg_list,
1336 '_PVT',
1337 x_return_status);
1338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1341 RAISE OKC_API.G_EXCEPTION_ERROR;
1342 END IF;
1343 BEGIN
1344 OPEN lock_csr(p_cor_rec);
1345 FETCH lock_csr INTO l_object_version_number;
1346 l_row_notfound := lock_csr%NOTFOUND;
1347 CLOSE lock_csr;
1348 EXCEPTION
1349 WHEN E_Resource_Busy THEN
1350 IF (lock_csr%ISOPEN) THEN
1351 CLOSE lock_csr;
1352 END IF;
1353 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1354 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1355 END;
1356
1357 IF ( l_row_notfound ) THEN
1358 OPEN lchk_csr(p_cor_rec);
1359 FETCH lchk_csr INTO lc_object_version_number;
1360 lc_row_notfound := lchk_csr%NOTFOUND;
1361 CLOSE lchk_csr;
1362 END IF;
1363 IF (lc_row_notfound) THEN
1364 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1365 RAISE OKC_API.G_EXCEPTION_ERROR;
1366 ELSIF lc_object_version_number > p_cor_rec.object_version_number THEN
1367 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1368 RAISE OKC_API.G_EXCEPTION_ERROR;
1369 ELSIF lc_object_version_number <> p_cor_rec.object_version_number THEN
1370 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1371 RAISE OKC_API.G_EXCEPTION_ERROR;
1372 ELSIF lc_object_version_number = -1 THEN
1373 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1374 RAISE OKC_API.G_EXCEPTION_ERROR;
1375 END IF;
1376 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1377 EXCEPTION
1378 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1379 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1380 (
1381 l_api_name,
1382 G_PKG_NAME,
1383 'OKC_API.G_RET_STS_ERROR',
1384 x_msg_count,
1385 x_msg_data,
1386 '_PVT'
1387 );
1388 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1389 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1390 (
1391 l_api_name,
1392 G_PKG_NAME,
1393 'OKC_API.G_RET_STS_UNEXP_ERROR',
1394 x_msg_count,
1395 x_msg_data,
1396 '_PVT'
1397 );
1398 WHEN OTHERS THEN
1399 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1400 (
1401 l_api_name,
1402 G_PKG_NAME,
1403 'OTHERS',
1404 x_msg_count,
1405 x_msg_data,
1406 '_PVT'
1407 );
1408 END lock_row;
1409 ---------------------------------
1410 -- lock_row for:OKC_CHANGES_TL --
1411 ---------------------------------
1412 PROCEDURE lock_row(
1413 p_init_msg_list IN VARCHAR2 ,
1414 x_return_status OUT NOCOPY VARCHAR2,
1415 x_msg_count OUT NOCOPY NUMBER,
1416 x_msg_data OUT NOCOPY VARCHAR2,
1417 p_okc_changes_tl_rec IN okc_changes_tl_rec_type) IS
1418
1419 E_Resource_Busy EXCEPTION;
1420 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1421 CURSOR lock_csr (p_okc_changes_tl_rec IN okc_changes_tl_rec_type) IS
1422 SELECT *
1423 FROM OKC_CHANGES_TL
1424 WHERE ID = p_okc_changes_tl_rec.id
1425 FOR UPDATE NOWAIT;
1426
1427 l_api_version CONSTANT NUMBER := 1;
1428 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1429 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1430 l_lock_var lock_csr%ROWTYPE;
1431 l_row_notfound BOOLEAN := FALSE;
1432 lc_row_notfound BOOLEAN := FALSE;
1433 BEGIN
1434 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1435 p_init_msg_list,
1436 '_PVT',
1437 x_return_status);
1438 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1439 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1440 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1441 RAISE OKC_API.G_EXCEPTION_ERROR;
1442 END IF;
1443 BEGIN
1444 OPEN lock_csr(p_okc_changes_tl_rec);
1445 FETCH lock_csr INTO l_lock_var;
1446 l_row_notfound := lock_csr%NOTFOUND;
1447 CLOSE lock_csr;
1448 EXCEPTION
1449 WHEN E_Resource_Busy THEN
1450 IF (lock_csr%ISOPEN) THEN
1451 CLOSE lock_csr;
1452 END IF;
1453 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1454 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1455 END;
1456
1457 IF ( l_row_notfound ) THEN
1458 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1459 RAISE OKC_API.G_EXCEPTION_ERROR;
1460 END IF;
1461 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1462 EXCEPTION
1463 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1464 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1465 (
1466 l_api_name,
1467 G_PKG_NAME,
1468 'OKC_API.G_RET_STS_ERROR',
1469 x_msg_count,
1470 x_msg_data,
1471 '_PVT'
1472 );
1473 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1474 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1475 (
1476 l_api_name,
1477 G_PKG_NAME,
1478 'OKC_API.G_RET_STS_UNEXP_ERROR',
1479 x_msg_count,
1480 x_msg_data,
1481 '_PVT'
1482 );
1483 WHEN OTHERS THEN
1484 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1485 (
1486 l_api_name,
1487 G_PKG_NAME,
1488 'OTHERS',
1489 x_msg_count,
1490 x_msg_data,
1491 '_PVT'
1492 );
1493 END lock_row;
1494 --------------------------------
1495 -- lock_row for:OKC_CHANGES_V --
1496 --------------------------------
1497 PROCEDURE lock_row(
1498 p_api_version IN NUMBER,
1499 p_init_msg_list IN VARCHAR2 ,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 x_msg_count OUT NOCOPY NUMBER,
1502 x_msg_data OUT NOCOPY VARCHAR2,
1503 p_corv_rec IN corv_rec_type) IS
1504
1505 l_api_version CONSTANT NUMBER := 1;
1506 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1508 l_cor_rec cor_rec_type;
1509 l_okc_changes_tl_rec okc_changes_tl_rec_type;
1510 BEGIN
1511 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1512 G_PKG_NAME,
1513 p_init_msg_list,
1514 l_api_version,
1515 p_api_version,
1516 '_PVT',
1517 x_return_status);
1518 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1519 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1520 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1521 RAISE OKC_API.G_EXCEPTION_ERROR;
1522 END IF;
1523 --------------------------------------
1524 -- Move VIEW record to "Child" records
1525 --------------------------------------
1526 migrate(p_corv_rec, l_cor_rec);
1527 migrate(p_corv_rec, l_okc_changes_tl_rec);
1528 --------------------------------------------
1529 -- Call the LOCK_ROW for each child record
1530 --------------------------------------------
1531 lock_row(
1532 p_init_msg_list,
1533 x_return_status,
1534 x_msg_count,
1535 x_msg_data,
1536 l_cor_rec
1537 );
1538 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1540 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1541 RAISE OKC_API.G_EXCEPTION_ERROR;
1542 END IF;
1543 lock_row(
1544 p_init_msg_list,
1545 x_return_status,
1546 x_msg_count,
1547 x_msg_data,
1548 l_okc_changes_tl_rec
1549 );
1550 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1551 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1552 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1553 RAISE OKC_API.G_EXCEPTION_ERROR;
1554 END IF;
1555 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1556 EXCEPTION
1557 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1558 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1559 (
1560 l_api_name,
1561 G_PKG_NAME,
1562 'OKC_API.G_RET_STS_ERROR',
1563 x_msg_count,
1564 x_msg_data,
1565 '_PVT'
1566 );
1567 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1568 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1569 (
1570 l_api_name,
1571 G_PKG_NAME,
1572 'OKC_API.G_RET_STS_UNEXP_ERROR',
1573 x_msg_count,
1574 x_msg_data,
1575 '_PVT'
1576 );
1577 WHEN OTHERS THEN
1578 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1579 (
1580 l_api_name,
1581 G_PKG_NAME,
1582 'OTHERS',
1583 x_msg_count,
1584 x_msg_data,
1585 '_PVT'
1586 );
1587 END lock_row;
1588 --------------------------------------
1589 -- PL/SQL TBL lock_row for:CORV_TBL --
1590 --------------------------------------
1591 PROCEDURE lock_row(
1592 p_api_version IN NUMBER,
1593 p_init_msg_list IN VARCHAR2 ,
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 x_msg_count OUT NOCOPY NUMBER,
1596 x_msg_data OUT NOCOPY VARCHAR2,
1597 p_corv_tbl IN corv_tbl_type) IS
1598
1599 l_api_version CONSTANT NUMBER := 1;
1600 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1601 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1602 i NUMBER := 0;
1603 BEGIN
1604 OKC_API.init_msg_list(p_init_msg_list);
1605 -- Make sure PL/SQL table has records in it before passing
1606 IF (p_corv_tbl.COUNT > 0) THEN
1607 i := p_corv_tbl.FIRST;
1608 LOOP
1609 lock_row (
1610 p_api_version => p_api_version,
1611 p_init_msg_list => OKC_API.G_FALSE,
1612 x_return_status => x_return_status,
1613 x_msg_count => x_msg_count,
1614 x_msg_data => x_msg_data,
1615 p_corv_rec => p_corv_tbl(i));
1616 EXIT WHEN (i = p_corv_tbl.LAST);
1617 i := p_corv_tbl.NEXT(i);
1618 END LOOP;
1619 END IF;
1620 EXCEPTION
1621 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1622 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1623 (
1624 l_api_name,
1625 G_PKG_NAME,
1626 'OKC_API.G_RET_STS_ERROR',
1627 x_msg_count,
1628 x_msg_data,
1629 '_PVT'
1630 );
1631 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1632 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1633 (
1634 l_api_name,
1635 G_PKG_NAME,
1636 'OKC_API.G_RET_STS_UNEXP_ERROR',
1637 x_msg_count,
1638 x_msg_data,
1639 '_PVT'
1640 );
1641 WHEN OTHERS THEN
1642 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1643 (
1644 l_api_name,
1645 G_PKG_NAME,
1646 'OTHERS',
1647 x_msg_count,
1648 x_msg_data,
1649 '_PVT'
1650 );
1651 END lock_row;
1652
1653 ---------------------------------------------------------------------------
1654 -- PROCEDURE update_row
1655 ---------------------------------------------------------------------------
1656 ----------------------------------
1657 -- update_row for:OKC_CHANGES_B --
1658 ----------------------------------
1659 PROCEDURE update_row(
1660 p_init_msg_list IN VARCHAR2 ,
1661 x_return_status OUT NOCOPY VARCHAR2,
1662 x_msg_count OUT NOCOPY NUMBER,
1663 x_msg_data OUT NOCOPY VARCHAR2,
1664 p_cor_rec IN cor_rec_type,
1665 x_cor_rec OUT NOCOPY cor_rec_type) IS
1666
1667 l_api_version CONSTANT NUMBER := 1;
1668 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1669 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1670 l_cor_rec cor_rec_type := p_cor_rec;
1671 l_def_cor_rec cor_rec_type;
1672 l_row_notfound BOOLEAN := TRUE;
1673 ----------------------------------
1674 -- FUNCTION populate_new_record --
1675 ----------------------------------
1676 FUNCTION populate_new_record (
1677 p_cor_rec IN cor_rec_type,
1678 x_cor_rec OUT NOCOPY cor_rec_type
1679 ) RETURN VARCHAR2 IS
1680 l_cor_rec cor_rec_type;
1681 l_row_notfound BOOLEAN := TRUE;
1682 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1683 BEGIN
1684 x_cor_rec := p_cor_rec;
1685 -- Get current database values
1686 l_cor_rec := get_rec(p_cor_rec, l_row_notfound);
1687 IF (l_row_notfound) THEN
1688 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1689 END IF;
1690 IF (x_cor_rec.id = OKC_API.G_MISS_NUM)
1691 THEN
1692 x_cor_rec.id := l_cor_rec.id;
1693 END IF;
1694 IF (x_cor_rec.crt_id = OKC_API.G_MISS_NUM)
1695 THEN
1696 x_cor_rec.crt_id := l_cor_rec.crt_id;
1697 END IF;
1698 IF (x_cor_rec.change_sequence = OKC_API.G_MISS_NUM)
1699 THEN
1700 x_cor_rec.change_sequence := l_cor_rec.change_sequence;
1701 END IF;
1702 IF (x_cor_rec.object_version_number = OKC_API.G_MISS_NUM)
1703 THEN
1704 x_cor_rec.object_version_number := l_cor_rec.object_version_number;
1705 END IF;
1706 IF (x_cor_rec.created_by = OKC_API.G_MISS_NUM)
1707 THEN
1708 x_cor_rec.created_by := l_cor_rec.created_by;
1709 END IF;
1710 IF (x_cor_rec.creation_date = OKC_API.G_MISS_DATE)
1711 THEN
1712 x_cor_rec.creation_date := l_cor_rec.creation_date;
1713 END IF;
1714 IF (x_cor_rec.last_updated_by = OKC_API.G_MISS_NUM)
1715 THEN
1716 x_cor_rec.last_updated_by := l_cor_rec.last_updated_by;
1717 END IF;
1718 IF (x_cor_rec.last_update_date = OKC_API.G_MISS_DATE)
1719 THEN
1720 x_cor_rec.last_update_date := l_cor_rec.last_update_date;
1721 END IF;
1722 IF (x_cor_rec.datetime_ineffective = OKC_API.G_MISS_DATE)
1723 THEN
1724 x_cor_rec.datetime_ineffective := l_cor_rec.datetime_ineffective;
1725 END IF;
1726 IF (x_cor_rec.last_update_login = OKC_API.G_MISS_NUM)
1727 THEN
1728 x_cor_rec.last_update_login := l_cor_rec.last_update_login;
1729 END IF;
1730 IF (x_cor_rec.attribute_category = OKC_API.G_MISS_CHAR)
1731 THEN
1732 x_cor_rec.attribute_category := l_cor_rec.attribute_category;
1733 END IF;
1734 IF (x_cor_rec.attribute1 = OKC_API.G_MISS_CHAR)
1735 THEN
1736 x_cor_rec.attribute1 := l_cor_rec.attribute1;
1737 END IF;
1738 IF (x_cor_rec.attribute2 = OKC_API.G_MISS_CHAR)
1739 THEN
1740 x_cor_rec.attribute2 := l_cor_rec.attribute2;
1741 END IF;
1742 IF (x_cor_rec.attribute3 = OKC_API.G_MISS_CHAR)
1743 THEN
1744 x_cor_rec.attribute3 := l_cor_rec.attribute3;
1745 END IF;
1746 IF (x_cor_rec.attribute4 = OKC_API.G_MISS_CHAR)
1747 THEN
1748 x_cor_rec.attribute4 := l_cor_rec.attribute4;
1749 END IF;
1750 IF (x_cor_rec.attribute5 = OKC_API.G_MISS_CHAR)
1751 THEN
1752 x_cor_rec.attribute5 := l_cor_rec.attribute5;
1753 END IF;
1754 IF (x_cor_rec.attribute6 = OKC_API.G_MISS_CHAR)
1755 THEN
1756 x_cor_rec.attribute6 := l_cor_rec.attribute6;
1757 END IF;
1758 IF (x_cor_rec.attribute7 = OKC_API.G_MISS_CHAR)
1759 THEN
1760 x_cor_rec.attribute7 := l_cor_rec.attribute7;
1761 END IF;
1762 IF (x_cor_rec.attribute8 = OKC_API.G_MISS_CHAR)
1763 THEN
1764 x_cor_rec.attribute8 := l_cor_rec.attribute8;
1765 END IF;
1766 IF (x_cor_rec.attribute9 = OKC_API.G_MISS_CHAR)
1767 THEN
1768 x_cor_rec.attribute9 := l_cor_rec.attribute9;
1769 END IF;
1770 IF (x_cor_rec.attribute10 = OKC_API.G_MISS_CHAR)
1771 THEN
1772 x_cor_rec.attribute10 := l_cor_rec.attribute10;
1773 END IF;
1774 IF (x_cor_rec.attribute11 = OKC_API.G_MISS_CHAR)
1775 THEN
1776 x_cor_rec.attribute11 := l_cor_rec.attribute11;
1777 END IF;
1778 IF (x_cor_rec.attribute12 = OKC_API.G_MISS_CHAR)
1779 THEN
1780 x_cor_rec.attribute12 := l_cor_rec.attribute12;
1781 END IF;
1782 IF (x_cor_rec.attribute13 = OKC_API.G_MISS_CHAR)
1783 THEN
1784 x_cor_rec.attribute13 := l_cor_rec.attribute13;
1785 END IF;
1786 IF (x_cor_rec.attribute14 = OKC_API.G_MISS_CHAR)
1787 THEN
1788 x_cor_rec.attribute14 := l_cor_rec.attribute14;
1789 END IF;
1790 IF (x_cor_rec.attribute15 = OKC_API.G_MISS_CHAR)
1791 THEN
1792 x_cor_rec.attribute15 := l_cor_rec.attribute15;
1793 END IF;
1794 RETURN(l_return_status);
1795 END populate_new_record;
1796 --------------------------------------
1797 -- Set_Attributes for:OKC_CHANGES_B --
1798 --------------------------------------
1799 FUNCTION Set_Attributes (
1800 p_cor_rec IN cor_rec_type,
1801 x_cor_rec OUT NOCOPY cor_rec_type
1802 ) RETURN VARCHAR2 IS
1803 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1804 BEGIN
1805 x_cor_rec := p_cor_rec;
1806 RETURN(l_return_status);
1807 END Set_Attributes;
1808 BEGIN
1809 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1810 p_init_msg_list,
1811 '_PVT',
1812 x_return_status);
1813 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1814 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1815 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1816 RAISE OKC_API.G_EXCEPTION_ERROR;
1817 END IF;
1818 --- Setting item attributes
1819 l_return_status := Set_Attributes(
1820 p_cor_rec, -- IN
1821 l_cor_rec); -- OUT
1822 --- If any errors happen abort API
1823 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1824 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1825 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1826 RAISE OKC_API.G_EXCEPTION_ERROR;
1827 END IF;
1828 l_return_status := populate_new_record(l_cor_rec, l_def_cor_rec);
1829 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1830 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1831 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1832 RAISE OKC_API.G_EXCEPTION_ERROR;
1833 END IF;
1834 UPDATE OKC_CHANGES_B
1835 SET CRT_ID = l_def_cor_rec.crt_id,
1836 CHANGE_SEQUENCE = l_def_cor_rec.change_sequence,
1837 OBJECT_VERSION_NUMBER = l_def_cor_rec.object_version_number,
1838 CREATED_BY = l_def_cor_rec.created_by,
1839 CREATION_DATE = l_def_cor_rec.creation_date,
1840 LAST_UPDATED_BY = l_def_cor_rec.last_updated_by,
1841 LAST_UPDATE_DATE = l_def_cor_rec.last_update_date,
1842 DATETIME_INEFFECTIVE = l_def_cor_rec.datetime_ineffective,
1843 LAST_UPDATE_LOGIN = l_def_cor_rec.last_update_login,
1844 ATTRIBUTE_CATEGORY = l_def_cor_rec.attribute_category,
1845 ATTRIBUTE1 = l_def_cor_rec.attribute1,
1846 ATTRIBUTE2 = l_def_cor_rec.attribute2,
1847 ATTRIBUTE3 = l_def_cor_rec.attribute3,
1848 ATTRIBUTE4 = l_def_cor_rec.attribute4,
1849 ATTRIBUTE5 = l_def_cor_rec.attribute5,
1850 ATTRIBUTE6 = l_def_cor_rec.attribute6,
1851 ATTRIBUTE7 = l_def_cor_rec.attribute7,
1852 ATTRIBUTE8 = l_def_cor_rec.attribute8,
1853 ATTRIBUTE9 = l_def_cor_rec.attribute9,
1854 ATTRIBUTE10 = l_def_cor_rec.attribute10,
1855 ATTRIBUTE11 = l_def_cor_rec.attribute11,
1856 ATTRIBUTE12 = l_def_cor_rec.attribute12,
1857 ATTRIBUTE13 = l_def_cor_rec.attribute13,
1858 ATTRIBUTE14 = l_def_cor_rec.attribute14,
1859 ATTRIBUTE15 = l_def_cor_rec.attribute15
1860 WHERE ID = l_def_cor_rec.id;
1861
1862 x_cor_rec := l_def_cor_rec;
1863 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1864 EXCEPTION
1865 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1866 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1867 (
1868 l_api_name,
1869 G_PKG_NAME,
1870 'OKC_API.G_RET_STS_ERROR',
1871 x_msg_count,
1872 x_msg_data,
1873 '_PVT'
1874 );
1875 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1876 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1877 (
1878 l_api_name,
1879 G_PKG_NAME,
1880 'OKC_API.G_RET_STS_UNEXP_ERROR',
1881 x_msg_count,
1882 x_msg_data,
1883 '_PVT'
1884 );
1885 WHEN OTHERS THEN
1886 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1887 (
1888 l_api_name,
1889 G_PKG_NAME,
1890 'OTHERS',
1891 x_msg_count,
1892 x_msg_data,
1893 '_PVT'
1894 );
1895 END update_row;
1896 -----------------------------------
1897 -- update_row for:OKC_CHANGES_TL --
1898 -----------------------------------
1899 PROCEDURE update_row(
1900 p_init_msg_list IN VARCHAR2 ,
1901 x_return_status OUT NOCOPY VARCHAR2,
1902 x_msg_count OUT NOCOPY NUMBER,
1903 x_msg_data OUT NOCOPY VARCHAR2,
1904 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
1905 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type) IS
1906
1907 l_api_version CONSTANT NUMBER := 1;
1908 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
1909 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1910 l_okc_changes_tl_rec okc_changes_tl_rec_type := p_okc_changes_tl_rec;
1911 l_def_okc_changes_tl_rec okc_changes_tl_rec_type;
1912 l_row_notfound BOOLEAN := TRUE;
1913 ----------------------------------
1914 -- FUNCTION populate_new_record --
1915 ----------------------------------
1916 FUNCTION populate_new_record (
1917 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
1918 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type
1919 ) RETURN VARCHAR2 IS
1920 l_okc_changes_tl_rec okc_changes_tl_rec_type;
1921 l_row_notfound BOOLEAN := TRUE;
1922 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1923 BEGIN
1924 x_okc_changes_tl_rec := p_okc_changes_tl_rec;
1925 -- Get current database values
1926 l_okc_changes_tl_rec := get_rec(p_okc_changes_tl_rec, l_row_notfound);
1927 IF (l_row_notfound) THEN
1928 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1929 END IF;
1930 IF (x_okc_changes_tl_rec.id = OKC_API.G_MISS_NUM)
1931 THEN
1932 x_okc_changes_tl_rec.id := l_okc_changes_tl_rec.id;
1933 END IF;
1934 IF (x_okc_changes_tl_rec.language = OKC_API.G_MISS_CHAR)
1935 THEN
1936 x_okc_changes_tl_rec.language := l_okc_changes_tl_rec.language;
1937 END IF;
1938 IF (x_okc_changes_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
1939 THEN
1940 x_okc_changes_tl_rec.source_lang := l_okc_changes_tl_rec.source_lang;
1941 END IF;
1942 IF (x_okc_changes_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
1943 THEN
1944 x_okc_changes_tl_rec.sfwt_flag := l_okc_changes_tl_rec.sfwt_flag;
1945 END IF;
1946 IF (x_okc_changes_tl_rec.short_description = OKC_API.G_MISS_CHAR)
1947 THEN
1948 x_okc_changes_tl_rec.short_description := l_okc_changes_tl_rec.short_description;
1949 END IF;
1950 -- Commented in favor of hand code
1951 --- IF (x_okc_changes_tl_rec.change_text = OKC_API.G_MISS_CHAR)
1952 IF (x_okc_changes_tl_rec.change_text is NULL)
1953 THEN
1954 x_okc_changes_tl_rec.change_text := l_okc_changes_tl_rec.change_text;
1955 END IF;
1956 IF (x_okc_changes_tl_rec.created_by = OKC_API.G_MISS_NUM)
1957 THEN
1958 x_okc_changes_tl_rec.created_by := l_okc_changes_tl_rec.created_by;
1959 END IF;
1960 IF (x_okc_changes_tl_rec.creation_date = OKC_API.G_MISS_DATE)
1961 THEN
1962 x_okc_changes_tl_rec.creation_date := l_okc_changes_tl_rec.creation_date;
1963 END IF;
1964 IF (x_okc_changes_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
1965 THEN
1966 x_okc_changes_tl_rec.last_updated_by := l_okc_changes_tl_rec.last_updated_by;
1967 END IF;
1968 IF (x_okc_changes_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
1969 THEN
1970 x_okc_changes_tl_rec.last_update_date := l_okc_changes_tl_rec.last_update_date;
1971 END IF;
1972 IF (x_okc_changes_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
1973 THEN
1974 x_okc_changes_tl_rec.last_update_login := l_okc_changes_tl_rec.last_update_login;
1975 END IF;
1976 RETURN(l_return_status);
1977 END populate_new_record;
1978 ---------------------------------------
1979 -- Set_Attributes for:OKC_CHANGES_TL --
1980 ---------------------------------------
1981 FUNCTION Set_Attributes (
1982 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
1983 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type
1984 ) RETURN VARCHAR2 IS
1985 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1986 BEGIN
1987 x_okc_changes_tl_rec := p_okc_changes_tl_rec;
1988 x_okc_changes_tl_rec.LANGUAGE := USERENV('LANG');
1989 x_okc_changes_tl_rec.SOURCE_LANG := USERENV('LANG');
1990 RETURN(l_return_status);
1991 END Set_Attributes;
1992 BEGIN
1993 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1994 p_init_msg_list,
1995 '_PVT',
1996 x_return_status);
1997 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1998 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1999 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2000 RAISE OKC_API.G_EXCEPTION_ERROR;
2001 END IF;
2002 --- Setting item attributes
2003 l_return_status := Set_Attributes(
2004 p_okc_changes_tl_rec, -- IN
2005 l_okc_changes_tl_rec); -- OUT
2006 --- If any errors happen abort API
2007 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2008 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2009 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2010 RAISE OKC_API.G_EXCEPTION_ERROR;
2011 END IF;
2012 l_return_status := populate_new_record(l_okc_changes_tl_rec, l_def_okc_changes_tl_rec);
2013 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2016 RAISE OKC_API.G_EXCEPTION_ERROR;
2017 END IF;
2018 UPDATE OKC_CHANGES_TL
2019 SET SHORT_DESCRIPTION = l_def_okc_changes_tl_rec.short_description,
2020 CHANGE_TEXT = l_def_okc_changes_tl_rec.change_text,
2021 CREATED_BY = l_def_okc_changes_tl_rec.created_by,
2022 CREATION_DATE = l_def_okc_changes_tl_rec.creation_date,
2023 LAST_UPDATED_BY = l_def_okc_changes_tl_rec.last_updated_by,
2024 LAST_UPDATE_DATE = l_def_okc_changes_tl_rec.last_update_date,
2025 LAST_UPDATE_LOGIN = l_def_okc_changes_tl_rec.last_update_login,
2026 SOURCE_LANG = l_def_okc_changes_tl_rec.source_lang
2027 WHERE ID = l_def_okc_changes_tl_rec.id
2028 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);
2029
2030 UPDATE OKC_CHANGES_TL
2031 SET SFWT_FLAG = 'Y'
2032 WHERE ID = l_def_okc_changes_tl_rec.id
2033 AND SOURCE_LANG <> USERENV('LANG');
2034
2035 x_okc_changes_tl_rec := l_def_okc_changes_tl_rec;
2036 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2037 EXCEPTION
2038 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2040 (
2041 l_api_name,
2042 G_PKG_NAME,
2043 'OKC_API.G_RET_STS_ERROR',
2044 x_msg_count,
2045 x_msg_data,
2046 '_PVT'
2047 );
2048 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2049 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2050 (
2051 l_api_name,
2052 G_PKG_NAME,
2053 'OKC_API.G_RET_STS_UNEXP_ERROR',
2054 x_msg_count,
2055 x_msg_data,
2056 '_PVT'
2057 );
2058 WHEN OTHERS THEN
2059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2060 (
2061 l_api_name,
2062 G_PKG_NAME,
2063 'OTHERS',
2064 x_msg_count,
2065 x_msg_data,
2066 '_PVT'
2067 );
2068 END update_row;
2069 ----------------------------------
2070 -- update_row for:OKC_CHANGES_V --
2071 ----------------------------------
2072 PROCEDURE update_row(
2073 p_api_version IN NUMBER,
2074 p_init_msg_list IN VARCHAR2 ,
2075 x_return_status OUT NOCOPY VARCHAR2,
2076 x_msg_count OUT NOCOPY NUMBER,
2077 x_msg_data OUT NOCOPY VARCHAR2,
2078 p_corv_rec IN corv_rec_type,
2079 x_corv_rec OUT NOCOPY corv_rec_type) IS
2080
2081 l_api_version CONSTANT NUMBER := 1;
2082 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2083 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2084 l_corv_rec corv_rec_type := p_corv_rec;
2085 l_def_corv_rec corv_rec_type;
2086 l_okc_changes_tl_rec okc_changes_tl_rec_type;
2087 lx_okc_changes_tl_rec okc_changes_tl_rec_type;
2088 l_cor_rec cor_rec_type;
2089 lx_cor_rec cor_rec_type;
2090 -------------------------------
2091 -- FUNCTION fill_who_columns --
2092 -------------------------------
2093 FUNCTION fill_who_columns (
2094 p_corv_rec IN corv_rec_type
2095 ) RETURN corv_rec_type IS
2096 l_corv_rec corv_rec_type := p_corv_rec;
2097 BEGIN
2098 l_corv_rec.LAST_UPDATE_DATE := SYSDATE;
2099 l_corv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2100 l_corv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2101 RETURN(l_corv_rec);
2102 END fill_who_columns;
2103 ----------------------------------
2104 -- FUNCTION populate_new_record --
2105 ----------------------------------
2106 FUNCTION populate_new_record (
2107 p_corv_rec IN corv_rec_type,
2108 x_corv_rec OUT NOCOPY corv_rec_type
2109 ) RETURN VARCHAR2 IS
2110 l_corv_rec corv_rec_type;
2111 l_row_notfound BOOLEAN := TRUE;
2112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2113 BEGIN
2114 x_corv_rec := p_corv_rec;
2115 -- Get current database values
2116 l_corv_rec := get_rec(p_corv_rec, l_row_notfound);
2117 IF (l_row_notfound) THEN
2118 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2119 END IF;
2120 IF (x_corv_rec.id = OKC_API.G_MISS_NUM)
2121 THEN
2122 x_corv_rec.id := l_corv_rec.id;
2123 END IF;
2124 IF (x_corv_rec.object_version_number = OKC_API.G_MISS_NUM)
2125 THEN
2126 x_corv_rec.object_version_number := l_corv_rec.object_version_number;
2127 END IF;
2128 IF (x_corv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2129 THEN
2130 x_corv_rec.sfwt_flag := l_corv_rec.sfwt_flag;
2131 END IF;
2132 IF (x_corv_rec.crt_id = OKC_API.G_MISS_NUM)
2133 THEN
2134 x_corv_rec.crt_id := l_corv_rec.crt_id;
2135 END IF;
2136 IF (x_corv_rec.change_sequence = OKC_API.G_MISS_NUM)
2137 THEN
2138 x_corv_rec.change_sequence := l_corv_rec.change_sequence;
2139 END IF;
2140 IF (x_corv_rec.change_text is NULL)
2141 THEN
2142 x_corv_rec.change_text := l_corv_rec.change_text;
2143 END IF;
2144 IF (x_corv_rec.short_description = OKC_API.G_MISS_CHAR)
2145 THEN
2146 x_corv_rec.short_description := l_corv_rec.short_description;
2147 END IF;
2148 IF (x_corv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2149 THEN
2150 x_corv_rec.attribute_category := l_corv_rec.attribute_category;
2151 END IF;
2152 IF (x_corv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2153 THEN
2154 x_corv_rec.attribute1 := l_corv_rec.attribute1;
2155 END IF;
2156 IF (x_corv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2157 THEN
2158 x_corv_rec.attribute2 := l_corv_rec.attribute2;
2159 END IF;
2160 IF (x_corv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2161 THEN
2162 x_corv_rec.attribute3 := l_corv_rec.attribute3;
2163 END IF;
2164 IF (x_corv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2165 THEN
2166 x_corv_rec.attribute4 := l_corv_rec.attribute4;
2167 END IF;
2168 IF (x_corv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2169 THEN
2170 x_corv_rec.attribute5 := l_corv_rec.attribute5;
2171 END IF;
2172 IF (x_corv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2173 THEN
2174 x_corv_rec.attribute6 := l_corv_rec.attribute6;
2175 END IF;
2176 IF (x_corv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2177 THEN
2178 x_corv_rec.attribute7 := l_corv_rec.attribute7;
2179 END IF;
2180 IF (x_corv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2181 THEN
2182 x_corv_rec.attribute8 := l_corv_rec.attribute8;
2183 END IF;
2184 IF (x_corv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2185 THEN
2186 x_corv_rec.attribute9 := l_corv_rec.attribute9;
2187 END IF;
2188 IF (x_corv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2189 THEN
2190 x_corv_rec.attribute10 := l_corv_rec.attribute10;
2191 END IF;
2192 IF (x_corv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2193 THEN
2194 x_corv_rec.attribute11 := l_corv_rec.attribute11;
2195 END IF;
2196 IF (x_corv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2197 THEN
2198 x_corv_rec.attribute12 := l_corv_rec.attribute12;
2199 END IF;
2200 IF (x_corv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2201 THEN
2202 x_corv_rec.attribute13 := l_corv_rec.attribute13;
2203 END IF;
2204 IF (x_corv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2205 THEN
2206 x_corv_rec.attribute14 := l_corv_rec.attribute14;
2207 END IF;
2208 IF (x_corv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2209 THEN
2210 x_corv_rec.attribute15 := l_corv_rec.attribute15;
2211 END IF;
2212 IF (x_corv_rec.created_by = OKC_API.G_MISS_NUM)
2213 THEN
2214 x_corv_rec.created_by := l_corv_rec.created_by;
2215 END IF;
2216 IF (x_corv_rec.creation_date = OKC_API.G_MISS_DATE)
2217 THEN
2218 x_corv_rec.creation_date := l_corv_rec.creation_date;
2219 END IF;
2220 IF (x_corv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2221 THEN
2222 x_corv_rec.last_updated_by := l_corv_rec.last_updated_by;
2223 END IF;
2224 IF (x_corv_rec.last_update_date = OKC_API.G_MISS_DATE)
2225 THEN
2226 x_corv_rec.last_update_date := l_corv_rec.last_update_date;
2227 END IF;
2228 IF (x_corv_rec.last_update_login = OKC_API.G_MISS_NUM)
2229 THEN
2230 x_corv_rec.last_update_login := l_corv_rec.last_update_login;
2231 END IF;
2232 RETURN(l_return_status);
2233 END populate_new_record;
2234 --------------------------------------
2235 -- Set_Attributes for:OKC_CHANGES_V --
2236 --------------------------------------
2237 FUNCTION Set_Attributes (
2238 p_corv_rec IN corv_rec_type,
2239 x_corv_rec OUT NOCOPY corv_rec_type
2240 ) RETURN VARCHAR2 IS
2241 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2242 BEGIN
2243 x_corv_rec := p_corv_rec;
2244 x_corv_rec.OBJECT_VERSION_NUMBER := NVL(x_corv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2245 RETURN(l_return_status);
2246 END Set_Attributes;
2247 BEGIN
2248 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2249 G_PKG_NAME,
2250 p_init_msg_list,
2251 l_api_version,
2252 p_api_version,
2253 '_PVT',
2254 x_return_status);
2255 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2256 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2257 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2258 RAISE OKC_API.G_EXCEPTION_ERROR;
2259 END IF;
2260 --- Setting item attributes
2261 l_return_status := Set_Attributes(
2262 p_corv_rec, -- IN
2263 l_corv_rec); -- OUT
2264 --- If any errors happen abort API
2265 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2266 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2267 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2268 RAISE OKC_API.G_EXCEPTION_ERROR;
2269 END IF;
2270 l_return_status := populate_new_record(l_corv_rec, l_def_corv_rec);
2271 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2272 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2273 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2274 RAISE OKC_API.G_EXCEPTION_ERROR;
2275 END IF;
2276 l_def_corv_rec := fill_who_columns(l_def_corv_rec);
2277 --- Validate all non-missing attributes (Item Level Validation)
2278 l_return_status := Validate_Attributes(l_def_corv_rec);
2279 --- If any errors happen abort API
2280 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2281 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2282 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2283 RAISE OKC_API.G_EXCEPTION_ERROR;
2284 END IF;
2285 l_return_status := Validate_Record(l_def_corv_rec);
2286 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2287 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2288 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2289 RAISE OKC_API.G_EXCEPTION_ERROR;
2290 END IF;
2291
2292 --------------------------------------
2293 -- Move VIEW record to "Child" records
2294 --------------------------------------
2295 migrate(l_def_corv_rec, l_okc_changes_tl_rec);
2296 migrate(l_def_corv_rec, l_cor_rec);
2297 --------------------------------------------
2298 -- Call the UPDATE_ROW for each child record
2299 --------------------------------------------
2300 update_row(
2301 p_init_msg_list,
2302 x_return_status,
2303 x_msg_count,
2304 x_msg_data,
2305 l_okc_changes_tl_rec,
2306 lx_okc_changes_tl_rec
2307 );
2308 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2309 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2310 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2311 RAISE OKC_API.G_EXCEPTION_ERROR;
2312 END IF;
2313 migrate(lx_okc_changes_tl_rec, l_def_corv_rec);
2314 update_row(
2315 p_init_msg_list,
2316 x_return_status,
2317 x_msg_count,
2318 x_msg_data,
2319 l_cor_rec,
2320 lx_cor_rec
2321 );
2322 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2323 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2324 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2325 RAISE OKC_API.G_EXCEPTION_ERROR;
2326 END IF;
2327 migrate(lx_cor_rec, l_def_corv_rec);
2328 x_corv_rec := l_def_corv_rec;
2329 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2330 EXCEPTION
2331 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2332 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2333 (
2334 l_api_name,
2335 G_PKG_NAME,
2336 'OKC_API.G_RET_STS_ERROR',
2337 x_msg_count,
2338 x_msg_data,
2339 '_PVT'
2340 );
2341 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2342 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2343 (
2344 l_api_name,
2345 G_PKG_NAME,
2346 'OKC_API.G_RET_STS_UNEXP_ERROR',
2347 x_msg_count,
2348 x_msg_data,
2349 '_PVT'
2350 );
2351 WHEN OTHERS THEN
2352 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2353 (
2354 l_api_name,
2355 G_PKG_NAME,
2356 'OTHERS',
2357 x_msg_count,
2358 x_msg_data,
2359 '_PVT'
2360 );
2361 END update_row;
2362 ----------------------------------------
2363 -- PL/SQL TBL update_row for:CORV_TBL --
2364 ----------------------------------------
2365 PROCEDURE update_row(
2366 p_api_version IN NUMBER,
2367 p_init_msg_list IN VARCHAR2 ,
2368 x_return_status OUT NOCOPY VARCHAR2,
2369 x_msg_count OUT NOCOPY NUMBER,
2370 x_msg_data OUT NOCOPY VARCHAR2,
2371 p_corv_tbl IN corv_tbl_type,
2372 x_corv_tbl OUT NOCOPY corv_tbl_type) IS
2373
2374 l_api_version CONSTANT NUMBER := 1;
2375 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2377 i NUMBER := 0;
2378 BEGIN
2379 OKC_API.init_msg_list(p_init_msg_list);
2380 -- Make sure PL/SQL table has records in it before passing
2381 IF (p_corv_tbl.COUNT > 0) THEN
2382 i := p_corv_tbl.FIRST;
2383 LOOP
2384 update_row (
2385 p_api_version => p_api_version,
2386 p_init_msg_list => OKC_API.G_FALSE,
2387 x_return_status => x_return_status,
2388 x_msg_count => x_msg_count,
2389 x_msg_data => x_msg_data,
2390 p_corv_rec => p_corv_tbl(i),
2391 x_corv_rec => x_corv_tbl(i));
2392 EXIT WHEN (i = p_corv_tbl.LAST);
2393 i := p_corv_tbl.NEXT(i);
2394 END LOOP;
2395 END IF;
2396 EXCEPTION
2397 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2398 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2399 (
2400 l_api_name,
2401 G_PKG_NAME,
2402 'OKC_API.G_RET_STS_ERROR',
2403 x_msg_count,
2404 x_msg_data,
2405 '_PVT'
2406 );
2407 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2408 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2409 (
2410 l_api_name,
2411 G_PKG_NAME,
2412 'OKC_API.G_RET_STS_UNEXP_ERROR',
2413 x_msg_count,
2414 x_msg_data,
2415 '_PVT'
2416 );
2417 WHEN OTHERS THEN
2418 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OTHERS',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 END update_row;
2428
2429 ---------------------------------------------------------------------------
2430 -- PROCEDURE delete_row
2431 ---------------------------------------------------------------------------
2432 ----------------------------------
2433 -- delete_row for:OKC_CHANGES_B --
2434 ----------------------------------
2435 PROCEDURE delete_row(
2436 p_init_msg_list IN VARCHAR2 ,
2437 x_return_status OUT NOCOPY VARCHAR2,
2438 x_msg_count OUT NOCOPY NUMBER,
2439 x_msg_data OUT NOCOPY VARCHAR2,
2440 p_cor_rec IN cor_rec_type) IS
2441
2442 l_api_version CONSTANT NUMBER := 1;
2443 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2444 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2445 l_cor_rec cor_rec_type:= p_cor_rec;
2446 l_row_notfound BOOLEAN := TRUE;
2447 BEGIN
2448 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2449 p_init_msg_list,
2450 '_PVT',
2451 x_return_status);
2452 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2453 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2454 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2455 RAISE OKC_API.G_EXCEPTION_ERROR;
2456 END IF;
2457 DELETE FROM OKC_CHANGES_B
2458 WHERE ID = l_cor_rec.id;
2459
2460 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2461 EXCEPTION
2462 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2463 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2464 (
2465 l_api_name,
2466 G_PKG_NAME,
2467 'OKC_API.G_RET_STS_ERROR',
2468 x_msg_count,
2469 x_msg_data,
2470 '_PVT'
2471 );
2472 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2473 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2474 (
2475 l_api_name,
2476 G_PKG_NAME,
2477 'OKC_API.G_RET_STS_UNEXP_ERROR',
2478 x_msg_count,
2479 x_msg_data,
2480 '_PVT'
2481 );
2482 WHEN OTHERS THEN
2483 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2484 (
2485 l_api_name,
2486 G_PKG_NAME,
2487 'OTHERS',
2488 x_msg_count,
2489 x_msg_data,
2490 '_PVT'
2491 );
2492 END delete_row;
2493 -----------------------------------
2494 -- delete_row for:OKC_CHANGES_TL --
2495 -----------------------------------
2496 PROCEDURE delete_row(
2497 p_init_msg_list IN VARCHAR2 ,
2498 x_return_status OUT NOCOPY VARCHAR2,
2499 x_msg_count OUT NOCOPY NUMBER,
2500 x_msg_data OUT NOCOPY VARCHAR2,
2501 p_okc_changes_tl_rec IN okc_changes_tl_rec_type) IS
2502
2503 l_api_version CONSTANT NUMBER := 1;
2504 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2505 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2506 l_okc_changes_tl_rec okc_changes_tl_rec_type:= p_okc_changes_tl_rec;
2507 l_row_notfound BOOLEAN := TRUE;
2508 ---------------------------------------
2509 -- Set_Attributes for:OKC_CHANGES_TL --
2510 ---------------------------------------
2511 FUNCTION Set_Attributes (
2512 p_okc_changes_tl_rec IN okc_changes_tl_rec_type,
2513 x_okc_changes_tl_rec OUT NOCOPY okc_changes_tl_rec_type
2514 ) RETURN VARCHAR2 IS
2515 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2516 BEGIN
2517 x_okc_changes_tl_rec := p_okc_changes_tl_rec;
2518 x_okc_changes_tl_rec.LANGUAGE := USERENV('LANG');
2519 RETURN(l_return_status);
2520 END Set_Attributes;
2521 BEGIN
2522 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2523 p_init_msg_list,
2524 '_PVT',
2525 x_return_status);
2526 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2527 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2529 RAISE OKC_API.G_EXCEPTION_ERROR;
2530 END IF;
2531 --- Setting item attributes
2532 l_return_status := Set_Attributes(
2533 p_okc_changes_tl_rec, -- IN
2534 l_okc_changes_tl_rec); -- OUT
2535 --- If any errors happen abort API
2536 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2537 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2538 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2539 RAISE OKC_API.G_EXCEPTION_ERROR;
2540 END IF;
2541 DELETE FROM OKC_CHANGES_TL
2542 WHERE ID = l_okc_changes_tl_rec.id;
2543
2544 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2545 EXCEPTION
2546 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2547 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2548 (
2549 l_api_name,
2550 G_PKG_NAME,
2551 'OKC_API.G_RET_STS_ERROR',
2552 x_msg_count,
2553 x_msg_data,
2554 '_PVT'
2555 );
2556 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2557 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2558 (
2559 l_api_name,
2560 G_PKG_NAME,
2561 'OKC_API.G_RET_STS_UNEXP_ERROR',
2562 x_msg_count,
2563 x_msg_data,
2564 '_PVT'
2565 );
2566 WHEN OTHERS THEN
2567 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2568 (
2569 l_api_name,
2570 G_PKG_NAME,
2571 'OTHERS',
2572 x_msg_count,
2573 x_msg_data,
2574 '_PVT'
2575 );
2576 END delete_row;
2577 ----------------------------------
2578 -- delete_row for:OKC_CHANGES_V --
2579 ----------------------------------
2580 PROCEDURE delete_row(
2581 p_api_version IN NUMBER,
2582 p_init_msg_list IN VARCHAR2 ,
2583 x_return_status OUT NOCOPY VARCHAR2,
2584 x_msg_count OUT NOCOPY NUMBER,
2585 x_msg_data OUT NOCOPY VARCHAR2,
2586 p_corv_rec IN corv_rec_type) IS
2587
2588 l_api_version CONSTANT NUMBER := 1;
2589 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2590 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2591 l_corv_rec corv_rec_type := p_corv_rec;
2592 l_okc_changes_tl_rec okc_changes_tl_rec_type;
2593 l_cor_rec cor_rec_type;
2594 BEGIN
2595 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2596 G_PKG_NAME,
2597 p_init_msg_list,
2598 l_api_version,
2599 p_api_version,
2600 '_PVT',
2601 x_return_status);
2602 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2603 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2605 RAISE OKC_API.G_EXCEPTION_ERROR;
2606 END IF;
2607 --------------------------------------
2608 -- Move VIEW record to "Child" records
2609 --------------------------------------
2610 migrate(l_corv_rec, l_okc_changes_tl_rec);
2611 migrate(l_corv_rec, l_cor_rec);
2612 --------------------------------------------
2613 -- Call the DELETE_ROW for each child record
2614 --------------------------------------------
2615 delete_row(
2616 p_init_msg_list,
2617 x_return_status,
2618 x_msg_count,
2619 x_msg_data,
2620 l_okc_changes_tl_rec
2621 );
2622 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2623 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2624 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2625 RAISE OKC_API.G_EXCEPTION_ERROR;
2626 END IF;
2627 delete_row(
2628 p_init_msg_list,
2629 x_return_status,
2630 x_msg_count,
2631 x_msg_data,
2632 l_cor_rec
2633 );
2634 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2635 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2637 RAISE OKC_API.G_EXCEPTION_ERROR;
2638 END IF;
2639 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2640 EXCEPTION
2641 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2642 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2643 (
2644 l_api_name,
2645 G_PKG_NAME,
2646 'OKC_API.G_RET_STS_ERROR',
2647 x_msg_count,
2648 x_msg_data,
2649 '_PVT'
2650 );
2651 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2652 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2653 (
2654 l_api_name,
2655 G_PKG_NAME,
2656 'OKC_API.G_RET_STS_UNEXP_ERROR',
2657 x_msg_count,
2658 x_msg_data,
2659 '_PVT'
2660 );
2661 WHEN OTHERS THEN
2662 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2663 (
2664 l_api_name,
2665 G_PKG_NAME,
2666 'OTHERS',
2667 x_msg_count,
2668 x_msg_data,
2669 '_PVT'
2670 );
2671 END delete_row;
2672 ----------------------------------------
2673 -- PL/SQL TBL delete_row for:CORV_TBL --
2674 ----------------------------------------
2675 PROCEDURE delete_row(
2676 p_api_version IN NUMBER,
2677 p_init_msg_list IN VARCHAR2 ,
2678 x_return_status OUT NOCOPY VARCHAR2,
2679 x_msg_count OUT NOCOPY NUMBER,
2680 x_msg_data OUT NOCOPY VARCHAR2,
2681 p_corv_tbl IN corv_tbl_type) IS
2682
2683 l_api_version CONSTANT NUMBER := 1;
2684 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2685 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2686 i NUMBER := 0;
2687 BEGIN
2688 OKC_API.init_msg_list(p_init_msg_list);
2689 -- Make sure PL/SQL table has records in it before passing
2690 IF (p_corv_tbl.COUNT > 0) THEN
2691 i := p_corv_tbl.FIRST;
2692 LOOP
2693 delete_row (
2694 p_api_version => p_api_version,
2695 p_init_msg_list => OKC_API.G_FALSE,
2696 x_return_status => x_return_status,
2697 x_msg_count => x_msg_count,
2698 x_msg_data => x_msg_data,
2699 p_corv_rec => p_corv_tbl(i));
2700 EXIT WHEN (i = p_corv_tbl.LAST);
2701 i := p_corv_tbl.NEXT(i);
2702 END LOOP;
2703 END IF;
2704 EXCEPTION
2705 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2706 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2707 (
2708 l_api_name,
2709 G_PKG_NAME,
2710 'OKC_API.G_RET_STS_ERROR',
2711 x_msg_count,
2712 x_msg_data,
2713 '_PVT'
2714 );
2715 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2716 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2717 (
2718 l_api_name,
2719 G_PKG_NAME,
2720 'OKC_API.G_RET_STS_UNEXP_ERROR',
2721 x_msg_count,
2722 x_msg_data,
2723 '_PVT'
2724 );
2725 WHEN OTHERS THEN
2726 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2727 (
2728 l_api_name,
2729 G_PKG_NAME,
2730 'OTHERS',
2731 x_msg_count,
2732 x_msg_data,
2733 '_PVT'
2734 );
2735 END delete_row;
2736 END OKC_COR_PVT;