[Home] [Help]
PACKAGE BODY: APPS.OKC_ATN_PVT
Source
1 PACKAGE BODY OKC_ATN_PVT AS
2 /* $Header: OKCSATNB.pls 120.0 2005/05/25 22:59:09 appldev 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_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
8 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
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 -- FUNCTION get_rec for: OKC_ARTICLE_TRANS
46 ---------------------------------------------------------------------------
47 FUNCTION get_rec (
48 p_atn_rec IN atn_rec_type,
49 x_no_data_found OUT NOCOPY BOOLEAN
50 ) RETURN atn_rec_type IS
51 CURSOR atn_pk_csr (p_id IN NUMBER) IS
52 SELECT
53 ID,
54 CAT_ID,
55 RUL_ID,
56 CLE_ID,
57 DNZ_CHR_ID,
58 OBJECT_VERSION_NUMBER,
59 CREATED_BY,
60 CREATION_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATE_LOGIN
64 FROM Okc_Article_Trans
65 WHERE okc_article_trans.id = p_id;
66 l_atn_pk atn_pk_csr%ROWTYPE;
67 l_atn_rec atn_rec_type;
68 BEGIN
69 x_no_data_found := TRUE;
70 -- Get current database values
71 OPEN atn_pk_csr (p_atn_rec.id);
72 FETCH atn_pk_csr INTO
73 l_atn_rec.ID,
74 l_atn_rec.CAT_ID,
75 l_atn_rec.RUL_ID,
76 l_atn_rec.CLE_ID,
77 l_atn_rec.DNZ_CHR_ID,
78 l_atn_rec.OBJECT_VERSION_NUMBER,
79 l_atn_rec.CREATED_BY,
80 l_atn_rec.CREATION_DATE,
81 l_atn_rec.LAST_UPDATED_BY,
82 l_atn_rec.LAST_UPDATE_DATE,
83 l_atn_rec.LAST_UPDATE_LOGIN;
84 x_no_data_found := atn_pk_csr%NOTFOUND;
85 CLOSE atn_pk_csr;
86 RETURN(l_atn_rec);
87 END get_rec;
88
89 FUNCTION get_rec (
90 p_atn_rec IN atn_rec_type
91 ) RETURN atn_rec_type IS
92 l_row_notfound BOOLEAN := TRUE;
93 BEGIN
94 RETURN(get_rec(p_atn_rec, l_row_notfound));
95 END get_rec;
96 ---------------------------------------------------------------------------
97 -- FUNCTION get_rec for: OKC_ARTICLE_TRANS_V
98 ---------------------------------------------------------------------------
99 FUNCTION get_rec (
100 p_atnv_rec IN atnv_rec_type,
101 x_no_data_found OUT NOCOPY BOOLEAN
102 ) RETURN atnv_rec_type IS
103 CURSOR okc_atnv_pk_csr (p_id IN NUMBER) IS
104 SELECT
105 ID,
106 OBJECT_VERSION_NUMBER,
107 CAT_ID,
108 CLE_ID,
109 RUL_ID,
110 DNZ_CHR_ID,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATE_LOGIN
116 FROM Okc_Article_Trans_V
117 WHERE okc_article_trans_v.id = p_id;
118 l_okc_atnv_pk okc_atnv_pk_csr%ROWTYPE;
119 l_atnv_rec atnv_rec_type;
120 BEGIN
121 x_no_data_found := TRUE;
122 -- Get current database values
123 OPEN okc_atnv_pk_csr (p_atnv_rec.id);
124 FETCH okc_atnv_pk_csr INTO
125 l_atnv_rec.ID,
126 l_atnv_rec.OBJECT_VERSION_NUMBER,
127 l_atnv_rec.CAT_ID,
128 l_atnv_rec.CLE_ID,
129 l_atnv_rec.RUL_ID,
130 l_atnv_rec.DNZ_CHR_ID,
131 l_atnv_rec.CREATED_BY,
132 l_atnv_rec.CREATION_DATE,
133 l_atnv_rec.LAST_UPDATED_BY,
134 l_atnv_rec.LAST_UPDATE_DATE,
135 l_atnv_rec.LAST_UPDATE_LOGIN;
136 x_no_data_found := okc_atnv_pk_csr%NOTFOUND;
137 CLOSE okc_atnv_pk_csr;
138 RETURN(l_atnv_rec);
139 END get_rec;
140
141 FUNCTION get_rec (
142 p_atnv_rec IN atnv_rec_type
143 ) RETURN atnv_rec_type IS
144 l_row_notfound BOOLEAN := TRUE;
145 BEGIN
146 RETURN(get_rec(p_atnv_rec, l_row_notfound));
147 END get_rec;
148
149 ---------------------------------------------------------
150 -- FUNCTION null_out_defaults for: OKC_ARTICLE_TRANS_V --
151 ---------------------------------------------------------
152 FUNCTION null_out_defaults (
153 p_atnv_rec IN atnv_rec_type
154 ) RETURN atnv_rec_type IS
155 l_atnv_rec atnv_rec_type := p_atnv_rec;
156 BEGIN
157 IF (l_atnv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
158 l_atnv_rec.object_version_number := NULL;
159 END IF;
160 IF (l_atnv_rec.cat_id = OKC_API.G_MISS_NUM) THEN
161 l_atnv_rec.cat_id := NULL;
162 END IF;
163 IF (l_atnv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
164 l_atnv_rec.cle_id := NULL;
165 END IF;
166 IF (l_atnv_rec.rul_id = OKC_API.G_MISS_NUM) THEN
167 l_atnv_rec.rul_id := NULL;
168 END IF;
169 IF (l_atnv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
170 l_atnv_rec.dnz_chr_id := NULL;
171 END IF;
172 IF (l_atnv_rec.created_by = OKC_API.G_MISS_NUM) THEN
173 l_atnv_rec.created_by := NULL;
174 END IF;
175 IF (l_atnv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
176 l_atnv_rec.creation_date := NULL;
177 END IF;
178 IF (l_atnv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
179 l_atnv_rec.last_updated_by := NULL;
180 END IF;
181 IF (l_atnv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
182 l_atnv_rec.last_update_date := NULL;
183 END IF;
184 IF (l_atnv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
185 l_atnv_rec.last_update_login := NULL;
186 END IF;
187 RETURN(l_atnv_rec);
188 END null_out_defaults;
189 ---------------------------------------------------------------------------
190 -- PROCEDURE Validate_Attributes
191 ---------------------------------------------------------------------------
192 /*+++++++++++++Start of hand code +++++++++++++++++*/
193 -- Start of comments
194 --
195 -- Procedure Name : validate_cat_id
196 -- Description :
197 -- Business Rules :
198 -- Parameters :
199 -- Version : 1.0
200 -- End of comments
201 procedure validate_cat_id(x_return_status OUT NOCOPY VARCHAR2,
202 p_atnv_rec IN ATNV_REC_TYPE) is
203 l_dummy_var varchar2(1) := '?';
204 cursor l_cat_csr is
205 select '!'
206 from OKC_K_ARTICLES_B
207 where id = p_atnv_rec.cat_id;
208 begin
209 x_return_status := OKC_API.G_RET_STS_SUCCESS;
210 if (p_atnv_rec.cat_id = OKC_API.G_MISS_NUM) then
211 return;
212 end if;
213 if (p_atnv_rec.cat_id is NULL) then
214 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
215 p_msg_name => G_REQUIRED_VALUE,
216 p_token1 => G_COL_NAME_TOKEN,
217 p_token1_value => 'CAT_ID');
218 raise G_EXCEPTION_HALT_VALIDATION;
219 end if;
220 open l_cat_csr;
221 fetch l_cat_csr into l_dummy_var;
222 close l_cat_csr;
223 if (l_dummy_var = '?') then
224 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID');
225 raise G_EXCEPTION_HALT_VALIDATION;
226 end if;
227 exception
228 when G_EXCEPTION_HALT_VALIDATION then
229 x_return_status := OKC_API.G_RET_STS_ERROR;
230 when OTHERS then
231 if l_cat_csr%ISOPEN then
232 close l_cat_csr;
233 end if;
234 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
235 p_msg_name => g_unexpected_error,
236 p_token1 => g_sqlcode_token,
237 p_token1_value => sqlcode,
238 p_token2 => g_sqlerrm_token,
239 p_token2_value => sqlerrm);
240 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
241 end validate_cat_id;
242
243 -- Start of comments
244 --
245 -- Procedure Name : validate_cle_id
246 -- Description :
247 -- Business Rules :
248 -- Parameters :
249 -- Version : 1.0
250 -- End of comments
251 procedure validate_cle_id(x_return_status OUT NOCOPY VARCHAR2,
252 p_atnv_rec IN ATNV_REC_TYPE) is
253 l_dummy_var varchar2(1) := '?';
254 cursor l_cle_csr is
255 select '!'
256 from OKC_K_LINES_B
257 where id = p_atnv_rec.cle_id;
258 begin
259 x_return_status := OKC_API.G_RET_STS_SUCCESS;
260 if (p_atnv_rec.cle_id = OKC_API.G_MISS_NUM or p_atnv_rec.cle_id is NULL) then
261 return;
262 end if;
263 open l_cle_csr;
264 fetch l_cle_csr into l_dummy_var;
265 close l_cle_csr;
266 if (l_dummy_var = '?') then
267 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
268 raise G_EXCEPTION_HALT_VALIDATION;
269 end if;
270 exception
271 when G_EXCEPTION_HALT_VALIDATION then
272 x_return_status := OKC_API.G_RET_STS_ERROR;
273 when OTHERS then
274 if l_cle_csr%ISOPEN then
275 close l_cle_csr;
276 end if;
277 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
278 p_msg_name => g_unexpected_error,
279 p_token1 => g_sqlcode_token,
280 p_token1_value => sqlcode,
281 p_token2 => g_sqlerrm_token,
282 p_token2_value => sqlerrm);
283 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
284 end validate_cle_id;
285
286 -- Start of comments
287 --
288 -- Procedure Name : validate_rul_id
289 -- Description :
290 -- Business Rules :
291 -- Parameters :
292 -- Version : 1.0
293 -- End of comments
294 procedure validate_rul_id(x_return_status OUT NOCOPY VARCHAR2,
295 p_atnv_rec IN ATNV_REC_TYPE) is
296 l_dummy_var varchar2(1) := '?';
297 cursor l_rul_csr is
298 select '!'
299 from OKC_RULES_B
300 where id = p_atnv_rec.rul_id;
301 begin
302 x_return_status := OKC_API.G_RET_STS_SUCCESS;
303 if (p_atnv_rec.rul_id = OKC_API.G_MISS_NUM or p_atnv_rec.rul_id is NULL) then
304 return;
305 end if;
306 open l_rul_csr;
307 fetch l_rul_csr into l_dummy_var;
308 close l_rul_csr;
309 if (l_dummy_var = '?') then
310 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RUL_ID');
311 raise G_EXCEPTION_HALT_VALIDATION;
312 end if;
313 exception
314 when G_EXCEPTION_HALT_VALIDATION then
315 x_return_status := OKC_API.G_RET_STS_ERROR;
316 when OTHERS then
317 if l_rul_csr%ISOPEN then
318 close l_rul_csr;
319 end if;
320 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
321 p_msg_name => g_unexpected_error,
322 p_token1 => g_sqlcode_token,
323 p_token1_value => sqlcode,
324 p_token2 => g_sqlerrm_token,
325 p_token2_value => sqlerrm);
326 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
327 end validate_rul_id;
328
329 -- Start of comments
330 --
331 -- Procedure Name : validate_dnz_chr_id
332 -- Description :
333 -- Business Rules :
334 -- Parameters :
335 -- Version : 1.0
336 -- End of comments
337
338 procedure validate_dnz_chr_id(x_return_status OUT NOCOPY VARCHAR2,
339 p_atnv_rec IN atnv_rec_TYPE) is
340 l_dummy varchar2(1) := '?';
341 cursor Kt_Hr_Mj_Vr is
342 select '!'
343 from okc_k_headers_b
344 where id = p_atnv_rec.dnz_chr_id;
345 begin
346 x_return_status := OKC_API.G_RET_STS_SUCCESS;
347 if (p_atnv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) then
348 return;
349 end if;
350 if (p_atnv_rec.dnz_chr_id is NULL) then
351 OKC_API.set_message(p_app_name => g_app_name,
352 p_msg_name => G_REQUIRED_VALUE,
353 p_token1 => G_COL_NAME_TOKEN,
354 p_token1_value => 'DNZ_CHR_ID');
355 x_return_status := OKC_API.G_RET_STS_ERROR;
356 return;
357 end if;
358 open Kt_Hr_Mj_Vr;
359 fetch Kt_Hr_Mj_Vr into l_dummy;
360 close Kt_Hr_Mj_Vr;
361 if (l_dummy='?') then
362 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DNZ_CHR_ID');
363 x_return_status := OKC_API.G_RET_STS_ERROR;
364 return;
365 end if;
366 exception
367 when OTHERS then
368 OKC_API.set_message(p_app_name => g_app_name,
369 p_msg_name => g_unexpected_error,
370 p_token1 => g_sqlcode_token,
371 p_token1_value => sqlcode,
372 p_token2 => g_sqlerrm_token,
373 p_token2_value => sqlerrm);
374 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
375 end validate_dnz_chr_id;
376
377 /*+++++++++++++End of hand code +++++++++++++++++*/
378 -------------------------------------------------
379 -- Validate_Attributes for:OKC_ARTICLE_TRANS_V --
380 -------------------------------------------------
381 FUNCTION Validate_Attributes (
382 p_atnv_rec IN atnv_rec_type
383 ) RETURN VARCHAR2 IS
384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
385 /*-------------Commented in favor of hand code------
386 BEGIN
387 IF p_atnv_rec.id = OKC_API.G_MISS_NUM OR
388 p_atnv_rec.id IS NULL
389 THEN
390 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
391 l_return_status := OKC_API.G_RET_STS_ERROR;
392 ELSIF p_atnv_rec.object_version_number = OKC_API.G_MISS_NUM OR
393 p_atnv_rec.object_version_number IS NULL
394 THEN
395 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
396 l_return_status := OKC_API.G_RET_STS_ERROR;
397 ELSIF p_atnv_rec.cat_id = OKC_API.G_MISS_NUM OR
398 p_atnv_rec.cat_id IS NULL
399 THEN
400 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cat_id');
401 l_return_status := OKC_API.G_RET_STS_ERROR;
402 ELSIF p_atnv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
403 p_atnv_rec.dnz_chr_id IS NULL
404 THEN
405 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'dnz_chr_id');
406 l_return_status := OKC_API.G_RET_STS_ERROR;
407 END IF;
408 RETURN(l_return_status);
409 END Validate_Attributes;
410 ---------------End of the commented code-----------*/
411 /*+++++++++++++Start of hand code +++++++++++++++++*/
412 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
413 BEGIN
414 -- call each column-level validation
415 validate_cat_id(x_return_status => l_return_status,
416 p_atnv_rec => p_atnv_rec);
417 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
418 return OKC_API.G_RET_STS_UNEXP_ERROR;
419 end if;
420 if (l_return_status = OKC_API.G_RET_STS_ERROR
421 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
422 x_return_status := OKC_API.G_RET_STS_ERROR;
423 end if;
424 validate_cle_id(x_return_status => l_return_status,
425 p_atnv_rec => p_atnv_rec);
426 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
427 return OKC_API.G_RET_STS_UNEXP_ERROR;
428 end if;
429 if (l_return_status = OKC_API.G_RET_STS_ERROR
430 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
431 x_return_status := OKC_API.G_RET_STS_ERROR;
432 end if;
433 validate_rul_id(x_return_status => l_return_status,
434 p_atnv_rec => p_atnv_rec);
435 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
436 return OKC_API.G_RET_STS_UNEXP_ERROR;
437 end if;
438 if (l_return_status = OKC_API.G_RET_STS_ERROR
439 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
440 x_return_status := OKC_API.G_RET_STS_ERROR;
441 end if;
442 validate_dnz_chr_id(x_return_status => l_return_status,
443 p_atnv_rec => p_atnv_rec);
444 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
445 return OKC_API.G_RET_STS_UNEXP_ERROR;
446 end if;
447 if (l_return_status = OKC_API.G_RET_STS_ERROR
448 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
449 x_return_status := OKC_API.G_RET_STS_ERROR;
450 end if;
451 return x_return_status;
452 exception
453 when OTHERS then
454 -- store SQL error message on message stack for caller
455 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
456 p_msg_name => g_unexpected_error,
457 p_token1 => g_sqlcode_token,
458 p_token1_value => sqlcode,
459 p_token2 => g_sqlerrm_token,
460 p_token2_value => sqlerrm);
461 -- notify caller of an UNEXPECTED error
462 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
463 return x_return_status;
464 END Validate_Attributes;
465 /*+++++++++++++End of hand code +++++++++++++++++++*/
466
467 ---------------------------------------------------------------------------
468 -- PROCEDURE Validate_Record
469 ---------------------------------------------------------------------------
470 ---------------------------------------------
471 -- Validate_Record for:OKC_ARTICLE_TRANS_V --
472 ---------------------------------------------
473 FUNCTION Validate_Record (
474 p_atnv_rec IN atnv_rec_type
475 --+++++++++++++++Start handcode +++++++++++++++++++++++++++++++++++
476 ,p_mode IN varchar2 DEFAULT 'UPDATE' -- or 'INSERT'
477 --+++++++++++++++End handcode +++++++++++++++++++++++++++++++++++
478 ) RETURN VARCHAR2 IS
479 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
480 /*-------------Commented in favor of hand code------
481 ------------------------------------
482 -- FUNCTION validate_foreign_keys --
483 ------------------------------------
484 FUNCTION validate_foreign_keys (
485 p_atnv_rec IN atnv_rec_type
486 ) RETURN VARCHAR2 IS
487 item_not_found_error EXCEPTION;
488 CURSOR okc_catv_pk_csr (p_id IN NUMBER) IS
489 SELECT
490 ID,
491 CHR_ID,
492 CLE_ID,
493 CAT_ID,
494 OBJECT_VERSION_NUMBER,
495 SFWT_FLAG,
496 SAV_SAE_ID,
497 SAV_SAV_RELEASE,
498 SBT_CODE,
499 DNZ_CHR_ID,
500 COMMENTS,
501 FULLTEXT_YN,
502 VARIATION_DESCRIPTION,
503 NAME,
504 TEXT,
505 ATTRIBUTE_CATEGORY,
506 ATTRIBUTE1,
507 ATTRIBUTE2,
508 ATTRIBUTE3,
509 ATTRIBUTE4,
510 ATTRIBUTE5,
511 ATTRIBUTE6,
512 ATTRIBUTE7,
513 ATTRIBUTE8,
514 ATTRIBUTE9,
515 ATTRIBUTE10,
516 ATTRIBUTE11,
517 ATTRIBUTE12,
518 ATTRIBUTE13,
519 ATTRIBUTE14,
520 ATTRIBUTE15,
521 CAT_TYPE,
522 CREATED_BY,
523 CREATION_DATE,
524 LAST_UPDATED_BY,
525 LAST_UPDATE_DATE,
526 LAST_UPDATE_LOGIN
527 FROM Okc_K_Articles_V
528 WHERE okc_k_articles_v.id = p_id;
529 l_okc_catv_pk okc_catv_pk_csr%ROWTYPE;
530 CURSOR okc_rulv_pk_csr (p_id IN NUMBER) IS
531 SELECT
532 ID,
533 OBJECT_VERSION_NUMBER,
534 SFWT_FLAG,
535 OBJECT1_ID1,
536 OBJECT2_ID1,
537 OBJECT3_ID1,
538 OBJECT1_ID2,
539 OBJECT2_ID2,
540 OBJECT3_ID2,
541 JTOT_OBJECT1_CODE,
542 JTOT_OBJECT2_CODE,
543 JTOT_OBJECT3_CODE,
544 DNZ_CHR_ID,
545 RGP_ID,
546 SAV_SAE_ID,
547 SAV_SAV_RELEASE,
548 PRIORITY,
549 STD_TEMPLATE_YN,
550 COMMENTS,
551 WARN_YN,
552 ATTRIBUTE_CATEGORY,
553 ATTRIBUTE1,
554 ATTRIBUTE2,
555 ATTRIBUTE3,
556 ATTRIBUTE4,
557 ATTRIBUTE5,
558 ATTRIBUTE6,
559 ATTRIBUTE7,
560 ATTRIBUTE8,
561 ATTRIBUTE9,
562 ATTRIBUTE10,
563 ATTRIBUTE11,
564 ATTRIBUTE12,
565 ATTRIBUTE13,
566 ATTRIBUTE14,
567 ATTRIBUTE15,
568 CREATED_BY,
569 CREATION_DATE,
570 LAST_UPDATED_BY,
571 LAST_UPDATE_DATE,
572 LAST_UPDATE_LOGIN,
573 TEXT,
574 RULE_INFORMATION_CATEGORY,
575 RULE_INFORMATION1,
576 RULE_INFORMATION2,
577 RULE_INFORMATION3,
578 RULE_INFORMATION4,
579 RULE_INFORMATION5,
580 RULE_INFORMATION6,
581 RULE_INFORMATION7,
582 RULE_INFORMATION8,
583 RULE_INFORMATION9,
584 RULE_INFORMATION10,
585 RULE_INFORMATION11,
586 RULE_INFORMATION12,
587 RULE_INFORMATION13,
588 RULE_INFORMATION14,
589 RULE_INFORMATION15
590 FROM Okc_Rules_V
591 WHERE okc_rules_v.id = p_id;
592 l_okc_rulv_pk okc_rulv_pk_csr%ROWTYPE;
593 CURSOR okc_clev_pk_csr (p_id IN NUMBER) IS
594 SELECT
595 ID,
596 OBJECT_VERSION_NUMBER,
597 SFWT_FLAG,
598 CHR_ID,
599 CLE_ID,
600 LSE_ID,
601 LINE_NUMBER,
602 STS_CODE,
603 DISPLAY_SEQUENCE,
604 TRN_CODE,
605 DNZ_CHR_ID,
606 COMMENTS,
607 ITEM_DESCRIPTION,
608 HIDDEN_IND,
609 PRICE_NEGOTIATED,
610 PRICE_LEVEL_IND,
611 INVOICE_LINE_LEVEL_IND,
612 DPAS_RATING,
613 BLOCK23TEXT,
614 EXCEPTION_YN,
615 TEMPLATE_USED,
616 DATE_TERMINATED,
617 NAME,
618 START_DATE,
619 END_DATE,
620 ATTRIBUTE_CATEGORY,
621 ATTRIBUTE1,
622 ATTRIBUTE2,
623 ATTRIBUTE3,
624 ATTRIBUTE4,
625 ATTRIBUTE5,
626 ATTRIBUTE6,
627 ATTRIBUTE7,
628 ATTRIBUTE8,
629 ATTRIBUTE9,
630 ATTRIBUTE10,
631 ATTRIBUTE11,
632 ATTRIBUTE12,
633 ATTRIBUTE13,
634 ATTRIBUTE14,
635 ATTRIBUTE15,
636 CREATED_BY,
637 CREATION_DATE,
638 LAST_UPDATED_BY,
639 LAST_UPDATE_DATE,
640 PRICE_TYPE,
641 UNIT_OF_MEASURE,
642 CURRENCY_CODE,
643 LAST_UPDATE_LOGIN
644 FROM Okc_K_Lines_V
645 WHERE okc_k_lines_v.id = p_id;
646 l_okc_clev_pk okc_clev_pk_csr%ROWTYPE;
647 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
648 l_row_notfound BOOLEAN := TRUE;
649 BEGIN
650 IF (p_atnv_rec.CAT_ID IS NOT NULL)
651 THEN
652 OPEN okc_catv_pk_csr(p_atnv_rec.CAT_ID);
653 FETCH okc_catv_pk_csr INTO l_okc_catv_pk;
654 l_row_notfound := okc_catv_pk_csr%NOTFOUND;
655 CLOSE okc_catv_pk_csr;
656 IF (l_row_notfound) THEN
657 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID');
658 RAISE item_not_found_error;
659 END IF;
660 END IF;
661 IF (p_atnv_rec.RUL_ID IS NOT NULL)
662 THEN
663 OPEN okc_rulv_pk_csr(p_atnv_rec.RUL_ID);
664 FETCH okc_rulv_pk_csr INTO l_okc_rulv_pk;
665 l_row_notfound := okc_rulv_pk_csr%NOTFOUND;
666 CLOSE okc_rulv_pk_csr;
667 IF (l_row_notfound) THEN
668 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RUL_ID');
669 RAISE item_not_found_error;
670 END IF;
671 END IF;
672 IF (p_atnv_rec.CLE_ID IS NOT NULL)
673 THEN
674 OPEN okc_clev_pk_csr(p_atnv_rec.CLE_ID);
675 FETCH okc_clev_pk_csr INTO l_okc_clev_pk;
676 l_row_notfound := okc_clev_pk_csr%NOTFOUND;
677 CLOSE okc_clev_pk_csr;
678 IF (l_row_notfound) THEN
679 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
680 RAISE item_not_found_error;
681 END IF;
682 END IF;
683 RETURN (l_return_status);
684 EXCEPTION
685 WHEN item_not_found_error THEN
686 l_return_status := OKC_API.G_RET_STS_ERROR;
687 RETURN (l_return_status);
688 END validate_foreign_keys;
689 BEGIN
690 l_return_status := validate_foreign_keys (p_atnv_rec);
691 RETURN (l_return_status);
692 END Validate_Record;
693 ---------------End of the commented code-----------*/
694 /*+++++++++++++Start of hand code +++++++++++++++++*/
695
696 cursor pk1_csr is
697 select '!' from okc_article_trans_v
698 where cat_id = p_atnv_rec.cat_id
699 and cle_id = p_atnv_rec.cle_id;
700 cursor pk2_csr is
701 select '!' from okc_article_trans_v
702 where cat_id = p_atnv_rec.cat_id
703 and rul_id = p_atnv_rec.rul_id;
704 l_dummy varchar2(1) := '?';
705 BEGIN
706 if (p_atnv_rec.cle_id IS NULL and p_atnv_rec.rul_id IS NULL)
707 then
708 OKC_API.SET_MESSAGE(g_app_name,g_required_value,g_col_name_token,'CLE_ID V RUL_ID');
709 l_return_status := OKC_API.G_RET_STS_ERROR;
710 end if;
711 if ((p_atnv_rec.cle_id IS NOT NULL and p_atnv_rec.cle_id <> OKC_API.G_MISS_NUM) and
712 (p_atnv_rec.rul_id IS NOT NULL and p_atnv_rec.rul_id <> OKC_API.G_MISS_NUM))
713 then
714 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID V RUL_ID');
715 l_return_status := OKC_API.G_RET_STS_ERROR;
716 end if;
717 --
718 /*
719 if (p_mode = 'INSERT') then
720 if not(p_atnv_rec.cle_id is NULL or p_atnv_rec.cle_id = OKC_API.G_MISS_NUM)
721 then
722 open pk1_csr;
723 fetch pk1_csr into l_dummy;
724 close pk1_csr;
725 if (l_dummy = '?') then
726 return OKC_API.G_RET_STS_SUCCESS;
727 end if;
728 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID, CLE_ID');
729 return OKC_API.G_RET_STS_ERROR;
730 end if;
731 if not(p_atnv_rec.rul_id is NULL or p_atnv_rec.rul_id = OKC_API.G_MISS_NUM)
732 then
733 open pk2_csr;
734 fetch pk2_csr into l_dummy;
735 close pk2_csr;
736 if (l_dummy = '?') then
737 return OKC_API.G_RET_STS_SUCCESS;
738 end if;
739 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CAT_ID, RUL_ID');
740 return OKC_API.G_RET_STS_ERROR;
741 end if;
742 end if;
743 */
744 --
745 RETURN (l_return_status);
746 exception
747 when G_EXCEPTION_HALT_VALIDATION then RETURN(OKC_API.G_RET_STS_ERROR);
748 when OTHERS then
749 -- store SQL error message on message stack for caller
750 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
751 p_msg_name => g_unexpected_error,
752 p_token1 => g_sqlcode_token,
753 p_token1_value => sqlcode,
754 p_token2 => g_sqlerrm_token,
755 p_token2_value => sqlerrm);
756 RETURN(OKC_API.G_RET_STS_UNEXP_ERROR);
757 END Validate_Record;
758 /*+++++++++++++End of hand code +++++++++++++++++++*/
759
760 ---------------------------------------------------------------------------
761 -- PROCEDURE Migrate
762 ---------------------------------------------------------------------------
763 PROCEDURE migrate (
764 p_from IN atnv_rec_type,
765 p_to IN OUT NOCOPY atn_rec_type
766 ) IS
767 BEGIN
768 p_to.id := p_from.id;
769 p_to.cat_id := p_from.cat_id;
770 p_to.rul_id := p_from.rul_id;
771 p_to.cle_id := p_from.cle_id;
772 p_to.dnz_chr_id := p_from.dnz_chr_id;
773 p_to.object_version_number := p_from.object_version_number;
774 p_to.created_by := p_from.created_by;
775 p_to.creation_date := p_from.creation_date;
776 p_to.last_updated_by := p_from.last_updated_by;
777 p_to.last_update_date := p_from.last_update_date;
778 p_to.last_update_login := p_from.last_update_login;
779 END migrate;
780 PROCEDURE migrate (
781 p_from IN atn_rec_type,
782 p_to IN OUT NOCOPY atnv_rec_type
783 ) IS
784 BEGIN
785 p_to.id := p_from.id;
786 p_to.cat_id := p_from.cat_id;
787 p_to.rul_id := p_from.rul_id;
788 p_to.cle_id := p_from.cle_id;
789 p_to.dnz_chr_id := p_from.dnz_chr_id;
790 p_to.object_version_number := p_from.object_version_number;
791 p_to.created_by := p_from.created_by;
792 p_to.creation_date := p_from.creation_date;
793 p_to.last_updated_by := p_from.last_updated_by;
794 p_to.last_update_date := p_from.last_update_date;
795 p_to.last_update_login := p_from.last_update_login;
796 END migrate;
797
798 ---------------------------------------------------------------------------
799 -- PROCEDURE validate_row
800 ---------------------------------------------------------------------------
801 ------------------------------------------
802 -- validate_row for:OKC_ARTICLE_TRANS_V --
803 ------------------------------------------
804 PROCEDURE validate_row(
805 p_api_version IN NUMBER,
806 p_init_msg_list IN VARCHAR2 ,
807 x_return_status OUT NOCOPY VARCHAR2,
808 x_msg_count OUT NOCOPY NUMBER,
809 x_msg_data OUT NOCOPY VARCHAR2,
810 p_atnv_rec IN atnv_rec_type) IS
811
812 l_api_version CONSTANT NUMBER := 1;
813 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
814 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
815 l_atnv_rec atnv_rec_type := p_atnv_rec;
816 l_atn_rec atn_rec_type;
817 BEGIN
818 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
819 G_PKG_NAME,
820 p_init_msg_list,
821 l_api_version,
822 p_api_version,
823 '_PVT',
824 x_return_status);
825 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
828 RAISE OKC_API.G_EXCEPTION_ERROR;
829 END IF;
830 --- Validate all non-missing attributes (Item Level Validation)
831 l_return_status := Validate_Attributes(l_atnv_rec);
832 --- If any errors happen abort API
833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
836 RAISE OKC_API.G_EXCEPTION_ERROR;
837 END IF;
838 l_return_status := Validate_Record(l_atnv_rec);
839 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
840 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
841 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
842 RAISE OKC_API.G_EXCEPTION_ERROR;
843 END IF;
844 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
845 EXCEPTION
846 WHEN OKC_API.G_EXCEPTION_ERROR THEN
847 x_return_status := OKC_API.HANDLE_EXCEPTIONS
848 (
849 l_api_name,
850 G_PKG_NAME,
851 'OKC_API.G_RET_STS_ERROR',
852 x_msg_count,
853 x_msg_data,
854 '_PVT'
855 );
856 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
857 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
858 (
859 l_api_name,
860 G_PKG_NAME,
861 'OKC_API.G_RET_STS_UNEXP_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PVT'
865 );
866 WHEN OTHERS THEN
867 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
868 (
869 l_api_name,
870 G_PKG_NAME,
871 'OTHERS',
872 x_msg_count,
873 x_msg_data,
874 '_PVT'
875 );
876 END validate_row;
877 ------------------------------------------
878 -- PL/SQL TBL validate_row for:ATNV_TBL --
879 ------------------------------------------
880 PROCEDURE validate_row(
881 p_api_version IN NUMBER,
882 p_init_msg_list IN VARCHAR2 ,
883 x_return_status OUT NOCOPY VARCHAR2,
884 x_msg_count OUT NOCOPY NUMBER,
885 x_msg_data OUT NOCOPY VARCHAR2,
886 p_atnv_tbl IN atnv_tbl_type) IS
887
888 l_api_version CONSTANT NUMBER := 1;
889 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
890 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
891 i NUMBER := 0;
892 BEGIN
893 OKC_API.init_msg_list(p_init_msg_list);
894 -- Make sure PL/SQL table has records in it before passing
895 IF (p_atnv_tbl.COUNT > 0) THEN
896 i := p_atnv_tbl.FIRST;
897 LOOP
898 validate_row (
899 p_api_version => p_api_version,
900 p_init_msg_list => OKC_API.G_FALSE,
901 x_return_status => x_return_status,
902 x_msg_count => x_msg_count,
903 x_msg_data => x_msg_data,
904 p_atnv_rec => p_atnv_tbl(i));
905 EXIT WHEN (i = p_atnv_tbl.LAST);
906 i := p_atnv_tbl.NEXT(i);
907 END LOOP;
908 END IF;
909 EXCEPTION
910 WHEN OKC_API.G_EXCEPTION_ERROR THEN
911 x_return_status := OKC_API.HANDLE_EXCEPTIONS
912 (
913 l_api_name,
914 G_PKG_NAME,
915 'OKC_API.G_RET_STS_ERROR',
916 x_msg_count,
917 x_msg_data,
918 '_PVT'
919 );
920 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
921 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
922 (
923 l_api_name,
924 G_PKG_NAME,
925 'OKC_API.G_RET_STS_UNEXP_ERROR',
926 x_msg_count,
927 x_msg_data,
928 '_PVT'
929 );
930 WHEN OTHERS THEN
931 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OTHERS',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 END validate_row;
941
942 ---------------------------------------------------------------------------
943 -- PROCEDURE insert_row
944 ---------------------------------------------------------------------------
945 --------------------------------------
946 -- insert_row for:OKC_ARTICLE_TRANS --
947 --------------------------------------
948 PROCEDURE insert_row(
949 p_init_msg_list IN VARCHAR2 ,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2,
953 p_atn_rec IN atn_rec_type,
954 x_atn_rec OUT NOCOPY atn_rec_type) IS
955
956 l_api_version CONSTANT NUMBER := 1;
957 l_api_name CONSTANT VARCHAR2(30) := 'TRANS_insert_row';
958 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
959 l_atn_rec atn_rec_type := p_atn_rec;
960 l_def_atn_rec atn_rec_type;
961 ------------------------------------------
962 -- Set_Attributes for:OKC_ARTICLE_TRANS --
963 ------------------------------------------
964 FUNCTION Set_Attributes (
965 p_atn_rec IN atn_rec_type,
966 x_atn_rec OUT NOCOPY atn_rec_type
967 ) RETURN VARCHAR2 IS
968 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
969 BEGIN
970 x_atn_rec := p_atn_rec;
971 RETURN(l_return_status);
972 END Set_Attributes;
973 BEGIN
974 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
975 p_init_msg_list,
976 '_PVT',
977 x_return_status);
978 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
979 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
980 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
981 RAISE OKC_API.G_EXCEPTION_ERROR;
982 END IF;
983 --- Setting item attributes
984 l_return_status := Set_Attributes(
985 p_atn_rec, -- IN
986 l_atn_rec); -- OUT
987 --- If any errors happen abort API
988 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
989 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
991 RAISE OKC_API.G_EXCEPTION_ERROR;
992 END IF;
993 INSERT INTO OKC_ARTICLE_TRANS(
994 id,
995 cat_id,
996 rul_id,
997 cle_id,
998 dnz_chr_id,
999 object_version_number,
1000 created_by,
1001 creation_date,
1002 last_updated_by,
1003 last_update_date,
1004 last_update_login)
1005 VALUES (
1006 l_atn_rec.id,
1007 l_atn_rec.cat_id,
1008 l_atn_rec.rul_id,
1009 l_atn_rec.cle_id,
1010 l_atn_rec.dnz_chr_id,
1011 l_atn_rec.object_version_number,
1012 l_atn_rec.created_by,
1013 l_atn_rec.creation_date,
1014 l_atn_rec.last_updated_by,
1015 l_atn_rec.last_update_date,
1016 l_atn_rec.last_update_login);
1017 -- Set OUT values
1018 x_atn_rec := l_atn_rec;
1019 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1020 EXCEPTION
1021 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1022 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1023 (
1024 l_api_name,
1025 G_PKG_NAME,
1026 'OKC_API.G_RET_STS_ERROR',
1027 x_msg_count,
1028 x_msg_data,
1029 '_PVT'
1030 );
1031 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1032 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1033 (
1034 l_api_name,
1035 G_PKG_NAME,
1036 'OKC_API.G_RET_STS_UNEXP_ERROR',
1037 x_msg_count,
1038 x_msg_data,
1039 '_PVT'
1040 );
1041 WHEN OTHERS THEN
1042 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1043 (
1044 l_api_name,
1045 G_PKG_NAME,
1046 'OTHERS',
1047 x_msg_count,
1048 x_msg_data,
1049 '_PVT'
1050 );
1051 END insert_row;
1052 ----------------------------------------
1053 -- insert_row for:OKC_ARTICLE_TRANS_V --
1054 ----------------------------------------
1055 PROCEDURE insert_row(
1056 p_api_version IN NUMBER,
1057 p_init_msg_list IN VARCHAR2 ,
1058 x_return_status OUT NOCOPY VARCHAR2,
1059 x_msg_count OUT NOCOPY NUMBER,
1060 x_msg_data OUT NOCOPY VARCHAR2,
1061 p_atnv_rec IN atnv_rec_type,
1062 x_atnv_rec OUT NOCOPY atnv_rec_type) IS
1063
1064 l_api_version CONSTANT NUMBER := 1;
1065 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1066 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1067 l_atnv_rec atnv_rec_type;
1068 l_def_atnv_rec atnv_rec_type;
1069 l_atn_rec atn_rec_type;
1070 lx_atn_rec atn_rec_type;
1071 -------------------------------
1072 -- FUNCTION fill_who_columns --
1073 -------------------------------
1074 FUNCTION fill_who_columns (
1075 p_atnv_rec IN atnv_rec_type
1076 ) RETURN atnv_rec_type IS
1077 l_atnv_rec atnv_rec_type := p_atnv_rec;
1078 BEGIN
1079 l_atnv_rec.CREATION_DATE := SYSDATE;
1080 l_atnv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1081 l_atnv_rec.LAST_UPDATE_DATE := l_atnv_rec.CREATION_DATE;
1082 l_atnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1083 l_atnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1084 RETURN(l_atnv_rec);
1085 END fill_who_columns;
1086 --------------------------------------------
1087 -- Set_Attributes for:OKC_ARTICLE_TRANS_V --
1088 --------------------------------------------
1089 FUNCTION Set_Attributes (
1090 p_atnv_rec IN atnv_rec_type,
1091 x_atnv_rec OUT NOCOPY atnv_rec_type
1092 ) RETURN VARCHAR2 IS
1093 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1094 BEGIN
1095 x_atnv_rec := p_atnv_rec;
1096 x_atnv_rec.OBJECT_VERSION_NUMBER := 1;
1097 RETURN(l_return_status);
1098 END Set_Attributes;
1099 BEGIN
1100 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1101 G_PKG_NAME,
1102 p_init_msg_list,
1103 l_api_version,
1104 p_api_version,
1105 '_PVT',
1106 x_return_status);
1107 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1108 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1109 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1110 RAISE OKC_API.G_EXCEPTION_ERROR;
1111 END IF;
1112 l_atnv_rec := null_out_defaults(p_atnv_rec);
1113 -- Set primary key value
1114 l_atnv_rec.ID := get_seq_id;
1115 --- Setting item attributes
1116 l_return_status := Set_Attributes(
1117 l_atnv_rec, -- IN
1118 l_def_atnv_rec); -- OUT
1119 --- If any errors happen abort API
1120 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1121 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1122 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1123 RAISE OKC_API.G_EXCEPTION_ERROR;
1124 END IF;
1125 l_def_atnv_rec := fill_who_columns(l_def_atnv_rec);
1126 --- Validate all non-missing attributes (Item Level Validation)
1127 l_return_status := Validate_Attributes(l_def_atnv_rec);
1128 --- If any errors happen abort API
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 /*------------------------commented in favor of hand code-----------
1135 l_return_status := Validate_Record(l_def_atnv_rec);
1136 ------------------------commented in favor of hand code-----------*/
1137 --++++++++++++++++++++++Hand code start+++++++++++++++++++++++++++++
1138 l_return_status := Validate_Record(l_def_atnv_rec,'INSERT');
1139 --++++++++++++++++++++++Hand code end+++++++++++++++++++++++++++++
1140 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1142 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1143 RAISE OKC_API.G_EXCEPTION_ERROR;
1144 END IF;
1145 --------------------------------------
1146 -- Move VIEW record to "Child" records
1147 --------------------------------------
1148 migrate(l_def_atnv_rec, l_atn_rec);
1149 --------------------------------------------
1150 -- Call the INSERT_ROW for each child record
1151 --------------------------------------------
1152 insert_row(
1153 p_init_msg_list,
1154 x_return_status,
1155 x_msg_count,
1156 x_msg_data,
1157 l_atn_rec,
1158 lx_atn_rec
1159 );
1160 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1162 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1163 RAISE OKC_API.G_EXCEPTION_ERROR;
1164 END IF;
1165 migrate(lx_atn_rec, l_def_atnv_rec);
1166 -- Set OUT values
1167 x_atnv_rec := l_def_atnv_rec;
1168 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1169 EXCEPTION
1170 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1171 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1172 (
1173 l_api_name,
1174 G_PKG_NAME,
1175 'OKC_API.G_RET_STS_ERROR',
1176 x_msg_count,
1177 x_msg_data,
1178 '_PVT'
1179 );
1180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1181 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1182 (
1183 l_api_name,
1184 G_PKG_NAME,
1185 'OKC_API.G_RET_STS_UNEXP_ERROR',
1186 x_msg_count,
1187 x_msg_data,
1188 '_PVT'
1189 );
1190 WHEN OTHERS THEN
1191 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1192 (
1193 l_api_name,
1194 G_PKG_NAME,
1195 'OTHERS',
1196 x_msg_count,
1197 x_msg_data,
1198 '_PVT'
1199 );
1200 END insert_row;
1201 ----------------------------------------
1202 -- PL/SQL TBL insert_row for:ATNV_TBL --
1203 ----------------------------------------
1204 PROCEDURE insert_row(
1205 p_api_version IN NUMBER,
1206 p_init_msg_list IN VARCHAR2 ,
1207 x_return_status OUT NOCOPY VARCHAR2,
1208 x_msg_count OUT NOCOPY NUMBER,
1209 x_msg_data OUT NOCOPY VARCHAR2,
1210 p_atnv_tbl IN atnv_tbl_type,
1211 x_atnv_tbl OUT NOCOPY atnv_tbl_type) IS
1212
1213 l_api_version CONSTANT NUMBER := 1;
1214 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1215 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1216 i NUMBER := 0;
1217 BEGIN
1218 OKC_API.init_msg_list(p_init_msg_list);
1219 -- Make sure PL/SQL table has records in it before passing
1220 IF (p_atnv_tbl.COUNT > 0) THEN
1221 i := p_atnv_tbl.FIRST;
1222 LOOP
1223 insert_row (
1224 p_api_version => p_api_version,
1225 p_init_msg_list => OKC_API.G_FALSE,
1226 x_return_status => x_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 p_atnv_rec => p_atnv_tbl(i),
1230 x_atnv_rec => x_atnv_tbl(i));
1231 EXIT WHEN (i = p_atnv_tbl.LAST);
1232 i := p_atnv_tbl.NEXT(i);
1233 END LOOP;
1234 END IF;
1235 EXCEPTION
1236 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1237 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1238 (
1239 l_api_name,
1240 G_PKG_NAME,
1241 'OKC_API.G_RET_STS_ERROR',
1242 x_msg_count,
1243 x_msg_data,
1244 '_PVT'
1245 );
1246 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1247 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1248 (
1249 l_api_name,
1250 G_PKG_NAME,
1251 'OKC_API.G_RET_STS_UNEXP_ERROR',
1252 x_msg_count,
1253 x_msg_data,
1254 '_PVT'
1255 );
1256 WHEN OTHERS THEN
1257 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1258 (
1259 l_api_name,
1260 G_PKG_NAME,
1261 'OTHERS',
1262 x_msg_count,
1263 x_msg_data,
1264 '_PVT'
1265 );
1266 END insert_row;
1267
1268 ---------------------------------------------------------------------------
1269 -- PROCEDURE lock_row
1270 ---------------------------------------------------------------------------
1271 ------------------------------------
1272 -- lock_row for:OKC_ARTICLE_TRANS --
1273 ------------------------------------
1274 PROCEDURE lock_row(
1275 p_init_msg_list IN VARCHAR2 ,
1276 x_return_status OUT NOCOPY VARCHAR2,
1277 x_msg_count OUT NOCOPY NUMBER,
1278 x_msg_data OUT NOCOPY VARCHAR2,
1279 p_atn_rec IN atn_rec_type) IS
1280
1281 E_Resource_Busy EXCEPTION;
1282 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1283 CURSOR lock_csr (p_atn_rec IN atn_rec_type) IS
1284 SELECT OBJECT_VERSION_NUMBER
1285 FROM OKC_ARTICLE_TRANS
1286 WHERE ID = p_atn_rec.id
1287 AND OBJECT_VERSION_NUMBER = p_atn_rec.object_version_number
1288 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1289
1290 CURSOR lchk_csr (p_atn_rec IN atn_rec_type) IS
1291 SELECT OBJECT_VERSION_NUMBER
1292 FROM OKC_ARTICLE_TRANS
1293 WHERE ID = p_atn_rec.id;
1294 l_api_version CONSTANT NUMBER := 1;
1295 l_api_name CONSTANT VARCHAR2(30) := 'TRANS_lock_row';
1296 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1297 l_object_version_number OKC_ARTICLE_TRANS.OBJECT_VERSION_NUMBER%TYPE;
1298 lc_object_version_number OKC_ARTICLE_TRANS.OBJECT_VERSION_NUMBER%TYPE;
1299 l_row_notfound BOOLEAN := FALSE;
1300 lc_row_notfound BOOLEAN := FALSE;
1301 BEGIN
1302 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1303 p_init_msg_list,
1304 '_PVT',
1305 x_return_status);
1306 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1307 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1308 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1309 RAISE OKC_API.G_EXCEPTION_ERROR;
1310 END IF;
1311 BEGIN
1312 OPEN lock_csr(p_atn_rec);
1313 FETCH lock_csr INTO l_object_version_number;
1314 l_row_notfound := lock_csr%NOTFOUND;
1315 CLOSE lock_csr;
1316 EXCEPTION
1317 WHEN E_Resource_Busy THEN
1318 IF (lock_csr%ISOPEN) THEN
1319 CLOSE lock_csr;
1320 END IF;
1321 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1322 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1323 END;
1324
1325 IF ( l_row_notfound ) THEN
1326 OPEN lchk_csr(p_atn_rec);
1327 FETCH lchk_csr INTO lc_object_version_number;
1328 lc_row_notfound := lchk_csr%NOTFOUND;
1329 CLOSE lchk_csr;
1330 END IF;
1331 IF (lc_row_notfound) THEN
1332 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1333 RAISE OKC_API.G_EXCEPTION_ERROR;
1334 ELSIF lc_object_version_number > p_atn_rec.object_version_number THEN
1335 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1336 RAISE OKC_API.G_EXCEPTION_ERROR;
1337 ELSIF lc_object_version_number <> p_atn_rec.object_version_number THEN
1338 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1339 RAISE OKC_API.G_EXCEPTION_ERROR;
1340 ELSIF lc_object_version_number = -1 THEN
1341 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1342 RAISE OKC_API.G_EXCEPTION_ERROR;
1343 END IF;
1344 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1345 EXCEPTION
1346 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1347 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1348 (
1349 l_api_name,
1350 G_PKG_NAME,
1351 'OKC_API.G_RET_STS_ERROR',
1352 x_msg_count,
1353 x_msg_data,
1354 '_PVT'
1355 );
1356 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1357 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1358 (
1359 l_api_name,
1360 G_PKG_NAME,
1361 'OKC_API.G_RET_STS_UNEXP_ERROR',
1362 x_msg_count,
1363 x_msg_data,
1364 '_PVT'
1365 );
1366 WHEN OTHERS THEN
1367 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1368 (
1369 l_api_name,
1370 G_PKG_NAME,
1371 'OTHERS',
1372 x_msg_count,
1373 x_msg_data,
1374 '_PVT'
1375 );
1376 END lock_row;
1377 --------------------------------------
1378 -- lock_row for:OKC_ARTICLE_TRANS_V --
1379 --------------------------------------
1380 PROCEDURE lock_row(
1381 p_api_version IN NUMBER,
1382 p_init_msg_list IN VARCHAR2 ,
1383 x_return_status OUT NOCOPY VARCHAR2,
1384 x_msg_count OUT NOCOPY NUMBER,
1385 x_msg_data OUT NOCOPY VARCHAR2,
1386 p_atnv_rec IN atnv_rec_type) IS
1387
1388 l_api_version CONSTANT NUMBER := 1;
1389 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1390 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1391 l_atn_rec atn_rec_type;
1392 BEGIN
1393 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1394 G_PKG_NAME,
1395 p_init_msg_list,
1396 l_api_version,
1397 p_api_version,
1398 '_PVT',
1399 x_return_status);
1400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 END IF;
1405 --------------------------------------
1406 -- Move VIEW record to "Child" records
1407 --------------------------------------
1408 migrate(p_atnv_rec, l_atn_rec);
1409 --------------------------------------------
1410 -- Call the LOCK_ROW for each child record
1411 --------------------------------------------
1412 lock_row(
1413 p_init_msg_list,
1414 x_return_status,
1415 x_msg_count,
1416 x_msg_data,
1417 l_atn_rec
1418 );
1419 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1421 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1422 RAISE OKC_API.G_EXCEPTION_ERROR;
1423 END IF;
1424 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1425 EXCEPTION
1426 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1427 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1428 (
1429 l_api_name,
1430 G_PKG_NAME,
1431 'OKC_API.G_RET_STS_ERROR',
1432 x_msg_count,
1433 x_msg_data,
1434 '_PVT'
1435 );
1436 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1437 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1438 (
1439 l_api_name,
1440 G_PKG_NAME,
1441 'OKC_API.G_RET_STS_UNEXP_ERROR',
1442 x_msg_count,
1443 x_msg_data,
1444 '_PVT'
1445 );
1446 WHEN OTHERS THEN
1447 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1448 (
1449 l_api_name,
1450 G_PKG_NAME,
1451 'OTHERS',
1452 x_msg_count,
1453 x_msg_data,
1454 '_PVT'
1455 );
1456 END lock_row;
1457 --------------------------------------
1458 -- PL/SQL TBL lock_row for:ATNV_TBL --
1459 --------------------------------------
1460 PROCEDURE lock_row(
1461 p_api_version IN NUMBER,
1462 p_init_msg_list IN VARCHAR2 ,
1463 x_return_status OUT NOCOPY VARCHAR2,
1464 x_msg_count OUT NOCOPY NUMBER,
1465 x_msg_data OUT NOCOPY VARCHAR2,
1466 p_atnv_tbl IN atnv_tbl_type) IS
1467
1468 l_api_version CONSTANT NUMBER := 1;
1469 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1470 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1471 i NUMBER := 0;
1472 BEGIN
1473 OKC_API.init_msg_list(p_init_msg_list);
1474 -- Make sure PL/SQL table has records in it before passing
1475 IF (p_atnv_tbl.COUNT > 0) THEN
1476 i := p_atnv_tbl.FIRST;
1477 LOOP
1478 lock_row (
1479 p_api_version => p_api_version,
1480 p_init_msg_list => OKC_API.G_FALSE,
1481 x_return_status => x_return_status,
1482 x_msg_count => x_msg_count,
1483 x_msg_data => x_msg_data,
1484 p_atnv_rec => p_atnv_tbl(i));
1485 EXIT WHEN (i = p_atnv_tbl.LAST);
1486 i := p_atnv_tbl.NEXT(i);
1487 END LOOP;
1488 END IF;
1489 EXCEPTION
1490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1492 (
1493 l_api_name,
1494 G_PKG_NAME,
1495 'OKC_API.G_RET_STS_ERROR',
1496 x_msg_count,
1497 x_msg_data,
1498 '_PVT'
1499 );
1500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1502 (
1503 l_api_name,
1504 G_PKG_NAME,
1505 'OKC_API.G_RET_STS_UNEXP_ERROR',
1506 x_msg_count,
1507 x_msg_data,
1508 '_PVT'
1509 );
1510 WHEN OTHERS THEN
1511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OTHERS',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 END lock_row;
1521
1522 ---------------------------------------------------------------------------
1523 -- PROCEDURE update_row
1524 ---------------------------------------------------------------------------
1525 --------------------------------------
1526 -- update_row for:OKC_ARTICLE_TRANS --
1527 --------------------------------------
1528 PROCEDURE update_row(
1529 p_init_msg_list IN VARCHAR2 ,
1530 x_return_status OUT NOCOPY VARCHAR2,
1531 x_msg_count OUT NOCOPY NUMBER,
1532 x_msg_data OUT NOCOPY VARCHAR2,
1533 p_atn_rec IN atn_rec_type,
1534 x_atn_rec OUT NOCOPY atn_rec_type) IS
1535
1536 l_api_version CONSTANT NUMBER := 1;
1537 l_api_name CONSTANT VARCHAR2(30) := 'TRANS_update_row';
1538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1539 l_atn_rec atn_rec_type := p_atn_rec;
1540 l_def_atn_rec atn_rec_type;
1541 l_row_notfound BOOLEAN := TRUE;
1542 ----------------------------------
1543 -- FUNCTION populate_new_record --
1544 ----------------------------------
1545 FUNCTION populate_new_record (
1546 p_atn_rec IN atn_rec_type,
1547 x_atn_rec OUT NOCOPY atn_rec_type
1548 ) RETURN VARCHAR2 IS
1549 l_atn_rec atn_rec_type;
1550 l_row_notfound BOOLEAN := TRUE;
1551 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1552 BEGIN
1553 x_atn_rec := p_atn_rec;
1554 -- Get current database values
1555 l_atn_rec := get_rec(p_atn_rec, l_row_notfound);
1556 IF (l_row_notfound) THEN
1557 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1558 END IF;
1559 IF (x_atn_rec.id = OKC_API.G_MISS_NUM)
1560 THEN
1561 x_atn_rec.id := l_atn_rec.id;
1562 END IF;
1563 IF (x_atn_rec.cat_id = OKC_API.G_MISS_NUM)
1564 THEN
1565 x_atn_rec.cat_id := l_atn_rec.cat_id;
1566 END IF;
1567 IF (x_atn_rec.rul_id = OKC_API.G_MISS_NUM)
1568 THEN
1569 x_atn_rec.rul_id := l_atn_rec.rul_id;
1570 END IF;
1571 IF (x_atn_rec.cle_id = OKC_API.G_MISS_NUM)
1572 THEN
1573 x_atn_rec.cle_id := l_atn_rec.cle_id;
1574 END IF;
1575 IF (x_atn_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1576 THEN
1577 x_atn_rec.dnz_chr_id := l_atn_rec.dnz_chr_id;
1578 END IF;
1579 IF (x_atn_rec.object_version_number = OKC_API.G_MISS_NUM)
1580 THEN
1581 x_atn_rec.object_version_number := l_atn_rec.object_version_number;
1582 END IF;
1583 IF (x_atn_rec.created_by = OKC_API.G_MISS_NUM)
1584 THEN
1585 x_atn_rec.created_by := l_atn_rec.created_by;
1586 END IF;
1587 IF (x_atn_rec.creation_date = OKC_API.G_MISS_DATE)
1588 THEN
1589 x_atn_rec.creation_date := l_atn_rec.creation_date;
1590 END IF;
1591 IF (x_atn_rec.last_updated_by = OKC_API.G_MISS_NUM)
1592 THEN
1593 x_atn_rec.last_updated_by := l_atn_rec.last_updated_by;
1594 END IF;
1595 IF (x_atn_rec.last_update_date = OKC_API.G_MISS_DATE)
1596 THEN
1597 x_atn_rec.last_update_date := l_atn_rec.last_update_date;
1598 END IF;
1599 IF (x_atn_rec.last_update_login = OKC_API.G_MISS_NUM)
1600 THEN
1601 x_atn_rec.last_update_login := l_atn_rec.last_update_login;
1602 END IF;
1603 RETURN(l_return_status);
1604 END populate_new_record;
1605 ------------------------------------------
1606 -- Set_Attributes for:OKC_ARTICLE_TRANS --
1607 ------------------------------------------
1608 FUNCTION Set_Attributes (
1609 p_atn_rec IN atn_rec_type,
1610 x_atn_rec OUT NOCOPY atn_rec_type
1611 ) RETURN VARCHAR2 IS
1612 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1613 BEGIN
1614 x_atn_rec := p_atn_rec;
1615 RETURN(l_return_status);
1616 END Set_Attributes;
1617 BEGIN
1618 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1619 p_init_msg_list,
1620 '_PVT',
1621 x_return_status);
1622 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1623 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1624 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1625 RAISE OKC_API.G_EXCEPTION_ERROR;
1626 END IF;
1627 --- Setting item attributes
1628 l_return_status := Set_Attributes(
1629 p_atn_rec, -- IN
1630 l_atn_rec); -- OUT
1631 --- If any errors happen abort API
1632 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1633 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1634 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1635 RAISE OKC_API.G_EXCEPTION_ERROR;
1636 END IF;
1637 l_return_status := populate_new_record(l_atn_rec, l_def_atn_rec);
1638 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1639 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1640 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1641 RAISE OKC_API.G_EXCEPTION_ERROR;
1642 END IF;
1643 UPDATE OKC_ARTICLE_TRANS
1644 SET CAT_ID = l_def_atn_rec.cat_id,
1645 RUL_ID = l_def_atn_rec.rul_id,
1646 CLE_ID = l_def_atn_rec.cle_id,
1647 DNZ_CHR_ID = l_def_atn_rec.dnz_chr_id,
1648 OBJECT_VERSION_NUMBER = l_def_atn_rec.object_version_number,
1649 CREATED_BY = l_def_atn_rec.created_by,
1650 CREATION_DATE = l_def_atn_rec.creation_date,
1651 LAST_UPDATED_BY = l_def_atn_rec.last_updated_by,
1652 LAST_UPDATE_DATE = l_def_atn_rec.last_update_date,
1653 LAST_UPDATE_LOGIN = l_def_atn_rec.last_update_login
1654 WHERE ID = l_def_atn_rec.id;
1655
1656 x_atn_rec := l_def_atn_rec;
1657 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1658 EXCEPTION
1659 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1660 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1661 (
1662 l_api_name,
1663 G_PKG_NAME,
1664 'OKC_API.G_RET_STS_ERROR',
1665 x_msg_count,
1666 x_msg_data,
1667 '_PVT'
1668 );
1669 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1670 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1671 (
1672 l_api_name,
1673 G_PKG_NAME,
1674 'OKC_API.G_RET_STS_UNEXP_ERROR',
1675 x_msg_count,
1676 x_msg_data,
1677 '_PVT'
1678 );
1679 WHEN OTHERS THEN
1680 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1681 (
1682 l_api_name,
1683 G_PKG_NAME,
1684 'OTHERS',
1685 x_msg_count,
1686 x_msg_data,
1687 '_PVT'
1688 );
1689 END update_row;
1690 ----------------------------------------
1691 -- update_row for:OKC_ARTICLE_TRANS_V --
1692 ----------------------------------------
1693 PROCEDURE update_row(
1694 p_api_version IN NUMBER,
1695 p_init_msg_list IN VARCHAR2 ,
1696 x_return_status OUT NOCOPY VARCHAR2,
1697 x_msg_count OUT NOCOPY NUMBER,
1698 x_msg_data OUT NOCOPY VARCHAR2,
1699 p_atnv_rec IN atnv_rec_type,
1700 x_atnv_rec OUT NOCOPY atnv_rec_type) IS
1701
1702 l_api_version CONSTANT NUMBER := 1;
1703 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1704 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1705 l_atnv_rec atnv_rec_type := p_atnv_rec;
1706 l_def_atnv_rec atnv_rec_type;
1707 l_atn_rec atn_rec_type;
1708 lx_atn_rec atn_rec_type;
1709 -------------------------------
1710 -- FUNCTION fill_who_columns --
1711 -------------------------------
1712 FUNCTION fill_who_columns (
1713 p_atnv_rec IN atnv_rec_type
1714 ) RETURN atnv_rec_type IS
1715 l_atnv_rec atnv_rec_type := p_atnv_rec;
1716 BEGIN
1717 l_atnv_rec.LAST_UPDATE_DATE := SYSDATE;
1718 l_atnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1719 l_atnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1720 RETURN(l_atnv_rec);
1721 END fill_who_columns;
1722 ----------------------------------
1723 -- FUNCTION populate_new_record --
1724 ----------------------------------
1725 FUNCTION populate_new_record (
1726 p_atnv_rec IN atnv_rec_type,
1727 x_atnv_rec OUT NOCOPY atnv_rec_type
1728 ) RETURN VARCHAR2 IS
1729 l_atnv_rec atnv_rec_type;
1730 l_row_notfound BOOLEAN := TRUE;
1731 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1732 BEGIN
1733 x_atnv_rec := p_atnv_rec;
1734 -- Get current database values
1735 l_atnv_rec := get_rec(p_atnv_rec, l_row_notfound);
1736 IF (l_row_notfound) THEN
1737 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1738 END IF;
1739 IF (x_atnv_rec.id = OKC_API.G_MISS_NUM)
1740 THEN
1741 x_atnv_rec.id := l_atnv_rec.id;
1742 END IF;
1743 IF (x_atnv_rec.object_version_number = OKC_API.G_MISS_NUM)
1744 THEN
1745 x_atnv_rec.object_version_number := l_atnv_rec.object_version_number;
1746 END IF;
1747 IF (x_atnv_rec.cat_id = OKC_API.G_MISS_NUM)
1748 THEN
1749 x_atnv_rec.cat_id := l_atnv_rec.cat_id;
1750 END IF;
1751 IF (x_atnv_rec.cle_id = OKC_API.G_MISS_NUM)
1752 THEN
1753 x_atnv_rec.cle_id := l_atnv_rec.cle_id;
1754 END IF;
1755 IF (x_atnv_rec.rul_id = OKC_API.G_MISS_NUM)
1756 THEN
1757 x_atnv_rec.rul_id := l_atnv_rec.rul_id;
1758 END IF;
1759 IF (x_atnv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
1760 THEN
1761 x_atnv_rec.dnz_chr_id := l_atnv_rec.dnz_chr_id;
1762 END IF;
1763 IF (x_atnv_rec.created_by = OKC_API.G_MISS_NUM)
1764 THEN
1765 x_atnv_rec.created_by := l_atnv_rec.created_by;
1766 END IF;
1767 IF (x_atnv_rec.creation_date = OKC_API.G_MISS_DATE)
1768 THEN
1769 x_atnv_rec.creation_date := l_atnv_rec.creation_date;
1770 END IF;
1771 IF (x_atnv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1772 THEN
1773 x_atnv_rec.last_updated_by := l_atnv_rec.last_updated_by;
1774 END IF;
1775 IF (x_atnv_rec.last_update_date = OKC_API.G_MISS_DATE)
1776 THEN
1777 x_atnv_rec.last_update_date := l_atnv_rec.last_update_date;
1778 END IF;
1779 IF (x_atnv_rec.last_update_login = OKC_API.G_MISS_NUM)
1780 THEN
1781 x_atnv_rec.last_update_login := l_atnv_rec.last_update_login;
1782 END IF;
1783 RETURN(l_return_status);
1784 END populate_new_record;
1785 --------------------------------------------
1786 -- Set_Attributes for:OKC_ARTICLE_TRANS_V --
1787 --------------------------------------------
1788 FUNCTION Set_Attributes (
1789 p_atnv_rec IN atnv_rec_type,
1790 x_atnv_rec OUT NOCOPY atnv_rec_type
1791 ) RETURN VARCHAR2 IS
1792 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1793 BEGIN
1794 x_atnv_rec := p_atnv_rec;
1795 x_atnv_rec.OBJECT_VERSION_NUMBER := NVL(x_atnv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1796 RETURN(l_return_status);
1797 END Set_Attributes;
1798 BEGIN
1799 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1800 G_PKG_NAME,
1801 p_init_msg_list,
1802 l_api_version,
1803 p_api_version,
1804 '_PVT',
1805 x_return_status);
1806 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1807 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1808 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1809 RAISE OKC_API.G_EXCEPTION_ERROR;
1810 END IF;
1811 --- Setting item attributes
1812 l_return_status := Set_Attributes(
1813 p_atnv_rec, -- IN
1814 l_atnv_rec); -- OUT
1815 --- If any errors happen abort API
1816 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1817 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1818 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1819 RAISE OKC_API.G_EXCEPTION_ERROR;
1820 END IF;
1821 l_return_status := populate_new_record(l_atnv_rec, l_def_atnv_rec);
1822 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1823 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1824 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1825 RAISE OKC_API.G_EXCEPTION_ERROR;
1826 END IF;
1827 l_def_atnv_rec := fill_who_columns(l_def_atnv_rec);
1828 --- Validate all non-missing attributes (Item Level Validation)
1829 l_return_status := Validate_Attributes(l_def_atnv_rec);
1830 --- If any errors happen abort API
1831 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1832 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1833 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1834 RAISE OKC_API.G_EXCEPTION_ERROR;
1835 END IF;
1836 l_return_status := Validate_Record(l_def_atnv_rec);
1837 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1838 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1839 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1840 RAISE OKC_API.G_EXCEPTION_ERROR;
1841 END IF;
1842
1843 --------------------------------------
1844 -- Move VIEW record to "Child" records
1845 --------------------------------------
1846 migrate(l_def_atnv_rec, l_atn_rec);
1847 --------------------------------------------
1848 -- Call the UPDATE_ROW for each child record
1849 --------------------------------------------
1850 update_row(
1851 p_init_msg_list,
1852 x_return_status,
1853 x_msg_count,
1854 x_msg_data,
1855 l_atn_rec,
1856 lx_atn_rec
1857 );
1858 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1859 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1860 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1861 RAISE OKC_API.G_EXCEPTION_ERROR;
1862 END IF;
1863 migrate(lx_atn_rec, l_def_atnv_rec);
1864 x_atnv_rec := l_def_atnv_rec;
1865 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1866 EXCEPTION
1867 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1868 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1869 (
1870 l_api_name,
1871 G_PKG_NAME,
1872 'OKC_API.G_RET_STS_ERROR',
1873 x_msg_count,
1874 x_msg_data,
1875 '_PVT'
1876 );
1877 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1878 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1879 (
1880 l_api_name,
1881 G_PKG_NAME,
1882 'OKC_API.G_RET_STS_UNEXP_ERROR',
1883 x_msg_count,
1884 x_msg_data,
1885 '_PVT'
1886 );
1887 WHEN OTHERS THEN
1888 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1889 (
1890 l_api_name,
1891 G_PKG_NAME,
1892 'OTHERS',
1893 x_msg_count,
1894 x_msg_data,
1895 '_PVT'
1896 );
1897 END update_row;
1898 ----------------------------------------
1899 -- PL/SQL TBL update_row for:ATNV_TBL --
1900 ----------------------------------------
1901 PROCEDURE update_row(
1902 p_api_version IN NUMBER,
1903 p_init_msg_list IN VARCHAR2 ,
1904 x_return_status OUT NOCOPY VARCHAR2,
1905 x_msg_count OUT NOCOPY NUMBER,
1906 x_msg_data OUT NOCOPY VARCHAR2,
1907 p_atnv_tbl IN atnv_tbl_type,
1908 x_atnv_tbl OUT NOCOPY atnv_tbl_type) IS
1909
1910 l_api_version CONSTANT NUMBER := 1;
1911 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1912 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1913 i NUMBER := 0;
1914 BEGIN
1915 OKC_API.init_msg_list(p_init_msg_list);
1916 -- Make sure PL/SQL table has records in it before passing
1917 IF (p_atnv_tbl.COUNT > 0) THEN
1918 i := p_atnv_tbl.FIRST;
1919 LOOP
1920 update_row (
1921 p_api_version => p_api_version,
1922 p_init_msg_list => OKC_API.G_FALSE,
1923 x_return_status => x_return_status,
1924 x_msg_count => x_msg_count,
1925 x_msg_data => x_msg_data,
1926 p_atnv_rec => p_atnv_tbl(i),
1927 x_atnv_rec => x_atnv_tbl(i));
1928 EXIT WHEN (i = p_atnv_tbl.LAST);
1929 i := p_atnv_tbl.NEXT(i);
1930 END LOOP;
1931 END IF;
1932 EXCEPTION
1933 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1934 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1935 (
1936 l_api_name,
1937 G_PKG_NAME,
1938 'OKC_API.G_RET_STS_ERROR',
1939 x_msg_count,
1940 x_msg_data,
1941 '_PVT'
1942 );
1943 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1944 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1945 (
1946 l_api_name,
1947 G_PKG_NAME,
1948 'OKC_API.G_RET_STS_UNEXP_ERROR',
1949 x_msg_count,
1950 x_msg_data,
1951 '_PVT'
1952 );
1953 WHEN OTHERS THEN
1954 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1955 (
1956 l_api_name,
1957 G_PKG_NAME,
1958 'OTHERS',
1959 x_msg_count,
1960 x_msg_data,
1961 '_PVT'
1962 );
1963 END update_row;
1964
1965 ---------------------------------------------------------------------------
1966 -- PROCEDURE delete_row
1967 ---------------------------------------------------------------------------
1968 --------------------------------------
1969 -- delete_row for:OKC_ARTICLE_TRANS --
1970 --------------------------------------
1971 PROCEDURE delete_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_atn_rec IN atn_rec_type) IS
1977
1978 l_api_version CONSTANT NUMBER := 1;
1979 l_api_name CONSTANT VARCHAR2(30) := 'TRANS_delete_row';
1980 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1981 l_atn_rec atn_rec_type:= p_atn_rec;
1982 l_row_notfound BOOLEAN := TRUE;
1983 BEGIN
1984 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1985 p_init_msg_list,
1986 '_PVT',
1987 x_return_status);
1988 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1989 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1990 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1991 RAISE OKC_API.G_EXCEPTION_ERROR;
1992 END IF;
1993 DELETE FROM OKC_ARTICLE_TRANS
1994 WHERE ID = l_atn_rec.id;
1995
1996 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1997 EXCEPTION
1998 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1999 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2000 (
2001 l_api_name,
2002 G_PKG_NAME,
2003 'OKC_API.G_RET_STS_ERROR',
2004 x_msg_count,
2005 x_msg_data,
2006 '_PVT'
2007 );
2008 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2009 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2010 (
2011 l_api_name,
2012 G_PKG_NAME,
2013 'OKC_API.G_RET_STS_UNEXP_ERROR',
2014 x_msg_count,
2015 x_msg_data,
2016 '_PVT'
2017 );
2018 WHEN OTHERS THEN
2019 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2020 (
2021 l_api_name,
2022 G_PKG_NAME,
2023 'OTHERS',
2024 x_msg_count,
2025 x_msg_data,
2026 '_PVT'
2027 );
2028 END delete_row;
2029 ----------------------------------------
2030 -- delete_row for:OKC_ARTICLE_TRANS_V --
2031 ----------------------------------------
2032 PROCEDURE delete_row(
2033 p_api_version IN NUMBER,
2034 p_init_msg_list IN VARCHAR2 ,
2035 x_return_status OUT NOCOPY VARCHAR2,
2036 x_msg_count OUT NOCOPY NUMBER,
2037 x_msg_data OUT NOCOPY VARCHAR2,
2038 p_atnv_rec IN atnv_rec_type) IS
2039
2040 l_api_version CONSTANT NUMBER := 1;
2041 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2042 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2043 l_atnv_rec atnv_rec_type := p_atnv_rec;
2044 l_atn_rec atn_rec_type;
2045 BEGIN
2046 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2047 G_PKG_NAME,
2048 p_init_msg_list,
2049 l_api_version,
2050 p_api_version,
2051 '_PVT',
2052 x_return_status);
2053 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2054 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2055 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2056 RAISE OKC_API.G_EXCEPTION_ERROR;
2057 END IF;
2058 --------------------------------------
2059 -- Move VIEW record to "Child" records
2060 --------------------------------------
2061 migrate(l_atnv_rec, l_atn_rec);
2062 --------------------------------------------
2063 -- Call the DELETE_ROW for each child record
2064 --------------------------------------------
2065 delete_row(
2066 p_init_msg_list,
2067 x_return_status,
2068 x_msg_count,
2069 x_msg_data,
2070 l_atn_rec
2071 );
2072 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2073 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2074 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2075 RAISE OKC_API.G_EXCEPTION_ERROR;
2076 END IF;
2077 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2078 EXCEPTION
2079 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2080 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2081 (
2082 l_api_name,
2083 G_PKG_NAME,
2084 'OKC_API.G_RET_STS_ERROR',
2085 x_msg_count,
2086 x_msg_data,
2087 '_PVT'
2088 );
2089 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2090 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2091 (
2092 l_api_name,
2093 G_PKG_NAME,
2094 'OKC_API.G_RET_STS_UNEXP_ERROR',
2095 x_msg_count,
2096 x_msg_data,
2097 '_PVT'
2098 );
2099 WHEN OTHERS THEN
2100 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2101 (
2102 l_api_name,
2103 G_PKG_NAME,
2104 'OTHERS',
2105 x_msg_count,
2106 x_msg_data,
2107 '_PVT'
2108 );
2109 END delete_row;
2110 ----------------------------------------
2111 -- PL/SQL TBL delete_row for:ATNV_TBL --
2112 ----------------------------------------
2113 PROCEDURE delete_row(
2114 p_api_version IN NUMBER,
2115 p_init_msg_list IN VARCHAR2 ,
2116 x_return_status OUT NOCOPY VARCHAR2,
2117 x_msg_count OUT NOCOPY NUMBER,
2118 x_msg_data OUT NOCOPY VARCHAR2,
2119 p_atnv_tbl IN atnv_tbl_type) IS
2120
2121 l_api_version CONSTANT NUMBER := 1;
2122 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2123 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2124 i NUMBER := 0;
2125 BEGIN
2126 OKC_API.init_msg_list(p_init_msg_list);
2127 -- Make sure PL/SQL table has records in it before passing
2128 IF (p_atnv_tbl.COUNT > 0) THEN
2129 i := p_atnv_tbl.FIRST;
2130 LOOP
2131 delete_row (
2132 p_api_version => p_api_version,
2133 p_init_msg_list => OKC_API.G_FALSE,
2134 x_return_status => x_return_status,
2135 x_msg_count => x_msg_count,
2136 x_msg_data => x_msg_data,
2137 p_atnv_rec => p_atnv_tbl(i));
2138 EXIT WHEN (i = p_atnv_tbl.LAST);
2139 i := p_atnv_tbl.NEXT(i);
2140 END LOOP;
2141 END IF;
2142 EXCEPTION
2143 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2144 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2145 (
2146 l_api_name,
2147 G_PKG_NAME,
2148 'OKC_API.G_RET_STS_ERROR',
2149 x_msg_count,
2150 x_msg_data,
2151 '_PVT'
2152 );
2153 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2154 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2155 (
2156 l_api_name,
2157 G_PKG_NAME,
2158 'OKC_API.G_RET_STS_UNEXP_ERROR',
2159 x_msg_count,
2160 x_msg_data,
2161 '_PVT'
2162 );
2163 WHEN OTHERS THEN
2164 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2165 (
2166 l_api_name,
2167 G_PKG_NAME,
2168 'OTHERS',
2169 x_msg_count,
2170 x_msg_data,
2171 '_PVT'
2172 );
2173 END delete_row;
2174
2175
2176 --This function is called from versioning API OKC_VERSION_PVT
2177 --Old Location: OKCRVERB.pls
2178 --New Location: Base Table API
2179
2180 FUNCTION create_version(
2181 p_chr_id IN NUMBER,
2182 p_major_version IN NUMBER
2183 ) RETURN VARCHAR2 IS
2184
2185
2186 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2187
2188 BEGIN
2189 INSERT INTO okc_article_trans_h
2190 (
2191 major_version,
2192 id,
2193 cat_id,
2194 rul_id,
2195 cle_id,
2196 dnz_chr_id,
2197 object_version_number,
2198 created_by,
2199 creation_date,
2200 last_updated_by,
2201 last_update_date,
2202 last_update_login
2203 )
2204 SELECT
2205 p_major_version,
2206 id,
2207 cat_id,
2208 rul_id,
2209 cle_id,
2210 dnz_chr_id,
2211 object_version_number,
2212 created_by,
2213 creation_date,
2214 last_updated_by,
2215 last_update_date,
2216 last_update_login
2217 FROM okc_article_trans
2218 WHERE dnz_chr_id = p_chr_id;
2219
2220 RETURN l_return_status;
2221 EXCEPTION
2222 -- other appropriate handlers
2223 WHEN OTHERS THEN
2224 -- store SQL error message on message stack
2225 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2226 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2227 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2228 p_token1_value => sqlcode,
2229 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2230 p_token2_value => sqlerrm);
2231
2232 -- notify UNEXPECTED error
2233 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2234 return l_return_status;
2235 END create_version;
2236
2237 --This Function is called from Versioning API OKC_VERSION_PVT
2238 --Old Location:OKCRVERB.pls
2239 --New Location:Base Table API
2240
2241 FUNCTION restore_version(
2242 p_chr_id IN NUMBER,
2243 p_major_version IN NUMBER
2244 ) RETURN VARCHAR2 IS
2245
2246 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
2247
2248 BEGIN
2249 INSERT INTO okc_article_trans
2250 (
2251 id,
2252 cat_id,
2253 rul_id,
2254 cle_id,
2255 dnz_chr_id,
2256 object_version_number,
2257 created_by,
2258 creation_date,
2259 last_updated_by,
2260 last_update_date,
2261 last_update_login
2262 )
2263 SELECT
2264 id,
2265 cat_id,
2266 rul_id,
2267 cle_id,
2268 dnz_chr_id,
2269 object_version_number,
2270 created_by,
2271 creation_date,
2272 last_updated_by,
2273 last_update_date,
2274 last_update_login
2275 FROM okc_article_trans_h
2276 WHERE dnz_chr_id = p_chr_id
2277 AND major_version = p_major_version;
2278
2279 RETURN l_return_status;
2280 EXCEPTION
2281 -- other appropriate handlers
2282 WHEN OTHERS THEN
2283 -- store SQL error message on message stack
2284 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
2285 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
2286 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
2287 p_token1_value => sqlcode,
2288 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
2289 p_token2_value => sqlerrm);
2290
2291 -- notify UNEXPECTED error
2292 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2293 return l_return_status;
2294 END restore_version;
2295
2296 END OKC_ATN_PVT;