[Home] [Help]
PACKAGE BODY: APPS.OKC_TAG_PVT
Source
1 PACKAGE BODY OKC_TAG_PVT AS
2 /* $Header: OKCSTAGB.pls 120.0 2005/05/25 19:22:13 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /*+++++++++++++Start of hand code +++++++++++++++++*/
7 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
8 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
9 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
10 g_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
11 /*+++++++++++++End of hand code +++++++++++++++++++*/
12
13 ---------------------------------------------------------------------------
14 -- FUNCTION get_seq_id
15 ---------------------------------------------------------------------------
16 FUNCTION get_seq_id RETURN NUMBER IS
17 BEGIN
18 RETURN(okc_p_util.raw_to_number(sys_guid()));
19 END get_seq_id;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE add_language
23 ---------------------------------------------------------------------------
24 PROCEDURE add_language IS
25 BEGIN
26 INSERT INTO OKC_REPORT_TAG_TL (
27 ID,
28 LANGUAGE,
29 SOURCE_LANG,
30 SFWT_FLAG,
31 MEANING,
32 CREATED_BY,
33 CREATION_DATE,
34 LAST_UPDATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATE_LOGIN)
37 SELECT
38 B.ID,
39 L.LANGUAGE_CODE,
40 B.SOURCE_LANG,
41 B.SFWT_FLAG,
42 B.MEANING,
43 B.CREATED_BY,
44 B.CREATION_DATE,
45 B.LAST_UPDATED_BY,
46 B.LAST_UPDATE_DATE,
47 B.LAST_UPDATE_LOGIN
48 FROM OKC_REPORT_TAG_TL B, FND_LANGUAGES L
49 WHERE L.INSTALLED_FLAG IN ('I', 'B')
50 AND B.LANGUAGE = USERENV('LANG')
51 AND NOT EXISTS(
52 SELECT NULL
53 FROM OKC_REPORT_TAG_TL T
54 WHERE T.ID = B.ID
55 AND T.LANGUAGE = L.LANGUAGE_CODE
56 );
57
58 END add_language;
59
60 -- Start of comments
61 --
62 -- Procedure Name : validate_enabled_flag
63 -- Description :
64 -- Business Rules :
65 -- Parameters :
66 -- Version : 1.0
67 -- End of comments
68 procedure validate_enabled_flag(p_oper IN VARCHAR2,
69 x_return_status OUT NOCOPY VARCHAR2,
70 p_tagv_rec IN tagv_rec_TYPE) is
71 begin
72 x_return_status := OKC_API.G_RET_STS_SUCCESS;
73 if (p_tagv_rec.enabled_flag in ('Y','N')) then
74 x_return_status := OKC_API.G_RET_STS_SUCCESS;
75 return;
76 end if;
77 if (p_tagv_rec.enabled_flag is NULL or
78 (p_tagv_rec.enabled_flag = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
79 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ENABLED_FLAG');
80 x_return_status := OKC_API.G_RET_STS_ERROR;
81 return;
82 end if;
83 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ENABLED_FLAG');
84 x_return_status := OKC_API.G_RET_STS_ERROR;
85 return;
86 exception
87 when OTHERS then
88 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
89 p_msg_name => g_unexpected_error,
90 p_token1 => g_sqlcode_token,
91 p_token1_value => sqlcode,
92 p_token2 => g_sqlerrm_token,
93 p_token2_value => sqlerrm);
94 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
95 end validate_enabled_flag;
96
97 -- Start of comments
98 --
99 -- Procedure Name : validate_code
100 -- Description :
101 -- Business Rules :
102 -- Parameters :
103 -- Version : 1.0
104 -- End of comments
105 procedure validate_code(p_oper IN VARCHAR2,
106 x_return_status OUT NOCOPY VARCHAR2,
107 p_tagv_rec IN tagv_rec_TYPE) is
108 l_dummy varchar2(1) := '?';
109 cursor c1(n1 number, c2 varchar2, n3 number) is
110 select '!'
111 from OKC_REPORT_TAG_V
112 where xsl_id = n1 and code = c2
113 and id <> n3;
114 begin
115 x_return_status := OKC_API.G_RET_STS_SUCCESS;
116 if (p_tagv_rec.XSL_ID is NULL or
117 (p_tagv_rec.XSL_ID = OKC_API.G_MISS_NUM and p_oper = 'I')) then
118 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'XSL_ID');
119 x_return_status := OKC_API.G_RET_STS_ERROR;
120 return;
121 end if;
122 if (p_tagv_rec.code is NULL or
123 (p_tagv_rec.code = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
124 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'CODE');
125 x_return_status := OKC_API.G_RET_STS_ERROR;
126 return;
127 end if;
128 open c1(p_tagv_rec.XSL_ID,p_tagv_rec.CODE,p_tagv_rec.id);
129 fetch c1 into l_dummy;
130 close c1;
131 if (l_dummy = '!') then
132 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
133 p_msg_name => OKC_UTIL.G_UNQ,
134 p_token1 => OKC_API.G_COL_NAME_TOKEN,
135 p_token1_value => 'CODE');
136 x_return_status := OKC_API.G_RET_STS_ERROR;
137 end if;
138 exception
139 when OTHERS then
140 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
141 p_msg_name => g_unexpected_error,
142 p_token1 => g_sqlcode_token,
143 p_token1_value => sqlcode,
144 p_token2 => g_sqlerrm_token,
145 p_token2_value => sqlerrm);
146 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
147 end validate_code;
148
149 -- Start of comments
150 --
151 -- Procedure Name : validate_meaning
152 -- Description :
153 -- Business Rules :
154 -- Parameters :
155 -- Version : 1.0
156 -- End of comments
157 procedure validate_meaning(p_oper IN VARCHAR2,
158 x_return_status OUT NOCOPY VARCHAR2,
159 p_tagv_rec IN tagv_rec_TYPE) is
160 l_dummy varchar2(1) := '?';
161 cursor c1(n1 number, c2 varchar2, n3 number) is
162 select '!'
163 from OKC_REPORT_TAG_V
164 where xsl_id = n1 and meaning = c2
165 and id <> n3;
166 begin
167 x_return_status := OKC_API.G_RET_STS_SUCCESS;
168
169 if (p_tagv_rec.XSL_ID is NULL or
170 (p_tagv_rec.XSL_ID = OKC_API.G_MISS_NUM and p_oper = 'I')) then
171 x_return_status := OKC_API.G_RET_STS_ERROR;
172 return;
173 end if;
174 if (p_tagv_rec.meaning is NULL or
175 (p_tagv_rec.meaning = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
176 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'MEANING');
177 x_return_status := OKC_API.G_RET_STS_ERROR;
178 return;
179 end if;
180 open c1(p_tagv_rec.XSL_ID,p_tagv_rec.MEANING,p_tagv_rec.id);
181 fetch c1 into l_dummy;
182 close c1;
183 if (l_dummy = '!') then
184 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
185 p_msg_name => OKC_UTIL.G_UNQ,
186 p_token1 => OKC_API.G_COL_NAME_TOKEN,
187 p_token1_value => 'MEANING');
188 x_return_status := OKC_API.G_RET_STS_ERROR;
189 end if;
190 exception
191 when OTHERS then
192 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
193 p_msg_name => g_unexpected_error,
194 p_token1 => g_sqlcode_token,
195 p_token1_value => sqlcode,
196 p_token2 => g_sqlerrm_token,
197 p_token2_value => sqlerrm);
198 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
199 end validate_meaning;
200
201 FUNCTION Validate_Attributes (p_oper IN varchar2,
202 p_tagv_rec IN tagv_rec_type
203 ) RETURN VARCHAR2 IS
204 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
205 x_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
206 BEGIN
207
208 validate_enabled_flag(p_oper => p_oper, x_return_status => l_return_status, p_tagv_rec => p_tagv_rec);
209 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
210 return OKC_API.G_RET_STS_UNEXP_ERROR;
211 end if;
212 if (l_return_status = OKC_API.G_RET_STS_ERROR
213 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
214 x_return_status := OKC_API.G_RET_STS_ERROR;
215 end if;
216
217 validate_code(p_oper => p_oper, x_return_status => l_return_status, p_tagv_rec => p_tagv_rec);
218 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
219 return OKC_API.G_RET_STS_UNEXP_ERROR;
220 end if;
221 if (l_return_status = OKC_API.G_RET_STS_ERROR
222 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
223 x_return_status := OKC_API.G_RET_STS_ERROR;
224 end if;
225
226 validate_meaning(p_oper => p_oper, x_return_status => l_return_status, p_tagv_rec => p_tagv_rec);
227 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
228 return OKC_API.G_RET_STS_UNEXP_ERROR;
229 end if;
230 if (l_return_status = OKC_API.G_RET_STS_ERROR
231 and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
232 x_return_status := OKC_API.G_RET_STS_ERROR;
233 end if;
234
235 return x_return_status;
236 exception
237 when OTHERS then
238 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
239 p_msg_name => g_unexpected_error,
240 p_token1 => g_sqlcode_token,
241 p_token1_value => sqlcode,
242 p_token2 => g_sqlerrm_token,
243 p_token2_value => sqlerrm);
244 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
245 return x_return_status;
246 END Validate_Attributes;
247
248 ---------------------------------------------------------------------------
249 -- PROCEDURE insert_row
250 ---------------------------------------------------------------------------
251
252 PROCEDURE date_xsl(p_xsl_id IN NUMBER) IS
253
254 E_Resource_Busy EXCEPTION;
255 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
256
257 CURSOR xsl_csr (p IN NUMBER) IS
258 SELECT '!' FROM OKC_REPORT_XSL_B
259 WHERE ID = p FOR UPDATE OF LAST_UPDATE_DATE;
260 l_dummy varchar2(1) := '?';
261
262 begin
263 open xsl_csr(p_xsl_id);
264 fetch xsl_csr into l_dummy;
265 close xsl_csr;
266 update okc_report_xsl_b set LAST_UPDATE_DATE = sysdate
267 where id = p_xsl_id;
268 EXCEPTION
269 WHEN E_Resource_Busy THEN
270 IF (xsl_csr%ISOPEN) THEN
271 CLOSE xsl_csr;
272 END IF;
273 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
274 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
275 end;
276
277 PROCEDURE insert_row(
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2,
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2,
283 p_tagv_rec IN tagv_rec_type,
284 x_tagv_rec OUT NOCOPY tagv_rec_type) IS
285
286 l_api_version CONSTANT NUMBER := 1;
287 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
288 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
289 l_tagv_rec tagv_rec_type;
290
291 begin
292
293 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
294 G_PKG_NAME,
295 p_init_msg_list,
296 l_api_version,
297 p_api_version,
298 '_PVT',
299 x_return_status);
300 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
301 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
302 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
303 RAISE OKC_API.G_EXCEPTION_ERROR;
304 END IF;
305
306 l_tagv_rec.ID := get_seq_id;
307 l_tagv_rec.XSL_ID := p_tagv_rec.XSL_ID;
308 l_tagv_rec.CODE := p_tagv_rec.CODE;
309 l_tagv_rec.MEANING := p_tagv_rec.MEANING;
310 l_tagv_rec.ENABLED_FLAG := p_tagv_rec.ENABLED_FLAG;
311 l_tagv_rec.OBJECT_VERSION_NUMBER := 1;
312 l_tagv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
313 l_tagv_rec.CREATION_DATE := SYSDATE;
314 l_tagv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
315 l_tagv_rec.LAST_UPDATE_DATE := SYSDATE;
316 l_tagv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
317
318 l_return_status := Validate_Attributes('I',l_tagv_rec);
319 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
320 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
321 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
322 RAISE OKC_API.G_EXCEPTION_ERROR;
323 END IF;
324
325 insert into OKC_REPORT_TAG_B
326 (
327 ID
328 ,XSL_ID
329 ,CODE
330 ,ENABLED_FLAG
331 ,OBJECT_VERSION_NUMBER
332 ,CREATED_BY
333 ,CREATION_DATE
334 ,LAST_UPDATED_BY
335 ,LAST_UPDATE_DATE
336 ,LAST_UPDATE_LOGIN
337 ) values
338 (
339 l_tagv_rec.ID
340 ,l_tagv_rec.XSL_ID
341 ,l_tagv_rec.CODE
342 ,l_tagv_rec.ENABLED_FLAG
343 ,l_tagv_rec.OBJECT_VERSION_NUMBER
344 ,l_tagv_rec.CREATED_BY
345 ,l_tagv_rec.CREATION_DATE
346 ,l_tagv_rec.LAST_UPDATED_BY
347 ,l_tagv_rec.LAST_UPDATE_DATE
348 ,l_tagv_rec.LAST_UPDATE_LOGIN
349 );
350
351 INSERT INTO OKC_REPORT_TAG_TL (
352 ID,
353 LANGUAGE,
354 SOURCE_LANG,
355 SFWT_FLAG,
356 MEANING,
357 CREATED_BY,
358 CREATION_DATE,
359 LAST_UPDATED_BY,
360 LAST_UPDATE_DATE,
361 LAST_UPDATE_LOGIN)
362 SELECT
363 l_tagv_rec.ID,
364 L.LANGUAGE_CODE,
365 USERENV('LANG'),
366 decode(L.LANGUAGE_CODE,USERENV('LANG'),'N','Y'),
367 l_tagv_rec.MEANING,
368 l_tagv_rec.CREATED_BY,
369 l_tagv_rec.CREATION_DATE,
370 l_tagv_rec.LAST_UPDATED_BY,
371 l_tagv_rec.LAST_UPDATE_DATE,
372 l_tagv_rec.LAST_UPDATE_LOGIN
373 FROM FND_LANGUAGES L
374 WHERE L.INSTALLED_FLAG IN ('I', 'B')
375 ;
376 date_xsl(l_tagv_rec.XSL_ID);
377 x_tagv_rec := l_tagv_rec;
378 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
379 EXCEPTION
380 WHEN OKC_API.G_EXCEPTION_ERROR THEN
381 x_return_status := OKC_API.HANDLE_EXCEPTIONS
382 (
383 l_api_name,
384 G_PKG_NAME,
385 'OKC_API.G_RET_STS_ERROR',
386 x_msg_count,
387 x_msg_data,
388 '_PVT'
389 );
390 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
391 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
392 (
393 l_api_name,
394 G_PKG_NAME,
395 'OKC_API.G_RET_STS_UNEXP_ERROR',
396 x_msg_count,
397 x_msg_data,
398 '_PVT'
399 );
400 WHEN OTHERS THEN
401 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
402 (
403 l_api_name,
404 G_PKG_NAME,
405 'OTHERS',
406 x_msg_count,
407 x_msg_data,
408 '_PVT'
409 );
410 END insert_row;
411
412 ---------------------------------------------------------------------------
413 -- PROCEDURE lock_row
414 ---------------------------------------------------------------------------
415
416 PROCEDURE lock_row(
417 p_init_msg_list IN VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2,
421 p_tagv_rec IN tagv_rec_type) IS
422
423 E_Resource_Busy EXCEPTION;
424 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
425
426 CURSOR lock_csr (p_tagv_rec IN tagv_rec_type) IS
427 SELECT OBJECT_VERSION_NUMBER
428 FROM OKC_REPORT_TAG_V
429 WHERE ID = p_tagv_rec.id
430 AND OBJECT_VERSION_NUMBER =
431 decode(p_tagv_rec.object_version_number,NULL,OBJECT_VERSION_NUMBER,
432 OKC_API.G_MISS_NUM,OBJECT_VERSION_NUMBER,p_tagv_rec.object_version_number)
433 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
434
435 CURSOR lchk_csr (p_tagv_rec IN tagv_rec_type) IS
436 SELECT OBJECT_VERSION_NUMBER
437 FROM OKC_REPORT_TAG_B
438 WHERE ID = p_tagv_rec.id;
439
440 l_api_version CONSTANT NUMBER := 1;
441 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
442 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
443
444 l_object_version_number NUMBER;
445 lc_object_version_number NUMBER;
446 l_row_notfound BOOLEAN := FALSE;
447 lc_row_notfound BOOLEAN := FALSE;
448
449 BEGIN
450 x_return_status := 'S';
451 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
452 p_init_msg_list,
453 '_PVT',
454 x_return_status);
455 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
456 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
457 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
458 RAISE OKC_API.G_EXCEPTION_ERROR;
459 END IF;
460 BEGIN
461 OPEN lock_csr(p_tagv_rec);
462 FETCH lock_csr INTO l_object_version_number;
463 l_row_notfound := lock_csr%NOTFOUND;
464 CLOSE lock_csr;
465 EXCEPTION
466 WHEN E_Resource_Busy THEN
467 IF (lock_csr%ISOPEN) THEN
468 CLOSE lock_csr;
469 END IF;
470 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
471 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
472 END;
473
474 IF ( l_row_notfound ) THEN
475 OPEN lchk_csr(p_tagv_rec);
476 FETCH lchk_csr INTO lc_object_version_number;
477 lc_row_notfound := lchk_csr%NOTFOUND;
478 CLOSE lchk_csr;
479 IF (lc_row_notfound) THEN
480 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
481 RAISE OKC_API.G_EXCEPTION_ERROR;
482 ELSE
483 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
484 RAISE OKC_API.G_EXCEPTION_ERROR;
485 END IF;
486 END IF;
487
488 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
489 EXCEPTION
490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
492 (
493 l_api_name,
494 G_PKG_NAME,
495 'OKC_API.G_RET_STS_ERROR',
496 x_msg_count,
497 x_msg_data,
498 '_PVT'
499 );
500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
502 (
503 l_api_name,
504 G_PKG_NAME,
505 'OKC_API.G_RET_STS_UNEXP_ERROR',
506 x_msg_count,
507 x_msg_data,
508 '_PVT'
509 );
510 WHEN OTHERS THEN
511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
512 (
513 l_api_name,
514 G_PKG_NAME,
515 'OTHERS',
516 x_msg_count,
517 x_msg_data,
518 '_PVT'
519 );
520 END lock_row;
521
522 ---------------------------------------------------------------------------
523 -- PROCEDURE update_row
524 ---------------------------------------------------------------------------
525
526 PROCEDURE update_row(
527 p_api_version IN NUMBER,
528 p_init_msg_list IN VARCHAR2,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2,
532 p_tagv_rec IN tagv_rec_type,
533 x_tagv_rec OUT NOCOPY tagv_rec_type) IS
534
535 l_api_version CONSTANT NUMBER := 1;
536 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
537 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
538 l_tagv_rec tagv_rec_type := p_tagv_rec;
539
540 begin
541
542 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
543 G_PKG_NAME,
544 p_init_msg_list,
545 l_api_version,
546 p_api_version,
547 '_PVT',
548 x_return_status);
549 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
550 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
551 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
552 RAISE OKC_API.G_EXCEPTION_ERROR;
553 END IF;
554
555 lock_row(p_init_msg_list => 'F',
556 x_return_status => l_return_status,
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data,
559 p_tagv_rec => p_tagv_rec);
560 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
561 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
562 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
563 RAISE OKC_API.G_EXCEPTION_ERROR;
564 END IF;
565
566 l_tagv_rec.ID := p_tagv_rec.ID;
567 select
568 XSL_ID
569 ,CODE
570 ,MEANING
571 ,ENABLED_FLAG
572 ,OBJECT_VERSION_NUMBER+1
573 ,created_by
574 ,creation_date
575 into
576 l_tagv_rec.XSL_ID
577 ,l_tagv_rec.CODE
578 ,l_tagv_rec.MEANING
579 ,l_tagv_rec.ENABLED_FLAG
580 ,l_tagv_rec.OBJECT_VERSION_NUMBER
581 ,l_tagv_rec.created_by
582 ,l_tagv_rec.creation_date
583 from OKC_REPORT_TAG_V
584 where ID = l_tagv_rec.ID;
585
586 l_tagv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
587 l_tagv_rec.LAST_UPDATE_DATE := SYSDATE;
588 l_tagv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
589
590
591 if (p_tagv_rec.XSL_ID is NULL or p_tagv_rec.XSL_ID <> OKC_API.G_MISS_NUM) then
592 l_tagv_rec.XSL_ID := p_tagv_rec.XSL_ID;
593 end if;
594 if (p_tagv_rec.CODE is NULL or p_tagv_rec.CODE <> OKC_API.G_MISS_CHAR) then
595 l_tagv_rec.CODE := p_tagv_rec.CODE;
596 end if;
597 if (p_tagv_rec.MEANING is NULL or p_tagv_rec.MEANING <> OKC_API.G_MISS_CHAR) then
598 l_tagv_rec.MEANING := p_tagv_rec.MEANING;
599 end if;
600 if (p_tagv_rec.ENABLED_FLAG is NULL or p_tagv_rec.ENABLED_FLAG <> OKC_API.G_MISS_CHAR) then
601 l_tagv_rec.ENABLED_FLAG := p_tagv_rec.ENABLED_FLAG;
602 end if;
603
604 l_return_status := Validate_Attributes('U',l_tagv_rec);
605 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
606 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
607 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
608 RAISE OKC_API.G_EXCEPTION_ERROR;
609 END IF;
610
611 UPDATE OKC_REPORT_TAG_B set
612 XSL_ID = l_tagv_rec.XSL_ID
613 ,CODE = l_tagv_rec.CODE
614 ,ENABLED_FLAG = l_tagv_rec.ENABLED_FLAG
615 ,OBJECT_VERSION_NUMBER = l_tagv_rec.OBJECT_VERSION_NUMBER
616 ,LAST_UPDATED_BY = l_tagv_rec.LAST_UPDATED_BY
617 ,LAST_UPDATE_DATE = l_tagv_rec.LAST_UPDATE_DATE
618 ,LAST_UPDATE_LOGIN = l_tagv_rec.LAST_UPDATE_LOGIN
619 where ID = l_tagv_rec.ID;
620
621 UPDATE OKC_REPORT_TAG_TL set
622 SOURCE_LANG = USERENV('LANG')
623 ,SFWT_FLAG = decode(LANGUAGE,USERENV('LANG'),'N','Y')
624 ,MEANING = l_tagv_rec.MEANING
625 ,LAST_UPDATED_BY = l_tagv_rec.LAST_UPDATED_BY
626 ,LAST_UPDATE_DATE = l_tagv_rec.LAST_UPDATE_DATE
627 ,LAST_UPDATE_LOGIN = l_tagv_rec.LAST_UPDATE_LOGIN
628 where ID = l_tagv_rec.ID
629 and USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
630
631 date_xsl(l_tagv_rec.XSL_ID);
632 x_tagv_rec := l_tagv_rec;
633 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
634 EXCEPTION
635 WHEN OKC_API.G_EXCEPTION_ERROR THEN
636 x_return_status := OKC_API.HANDLE_EXCEPTIONS
637 (
638 l_api_name,
639 G_PKG_NAME,
640 'OKC_API.G_RET_STS_ERROR',
641 x_msg_count,
642 x_msg_data,
643 '_PVT'
644 );
645 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
646 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
647 (
648 l_api_name,
649 G_PKG_NAME,
650 'OKC_API.G_RET_STS_UNEXP_ERROR',
651 x_msg_count,
652 x_msg_data,
653 '_PVT'
654 );
655 WHEN OTHERS THEN
656 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
657 (
658 l_api_name,
659 G_PKG_NAME,
660 'OTHERS',
661 x_msg_count,
662 x_msg_data,
663 '_PVT'
664 );
665 END update_row;
666
667
668 ---------------------------------------------------------------------------
669 -- PROCEDURE delete_row
670 ---------------------------------------------------------------------------
671 PROCEDURE delete_row(
672 p_api_version IN NUMBER,
673 p_init_msg_list IN VARCHAR2,
674 x_return_status OUT NOCOPY VARCHAR2,
675 x_msg_count OUT NOCOPY NUMBER,
676 x_msg_data OUT NOCOPY VARCHAR2,
677 p_tagv_rec IN tagv_rec_type) IS
678
679 l_api_version CONSTANT NUMBER := 1;
680 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
681 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
682
683 cursor xsl_csr(p NUMBER) is
684 select S.id
685 from okc_report_tag_v TAG, okc_report_xsl_v S
686 where TAG.id = p and S.id = TAG.xsl_id;
687
688 l_xsl_id NUMBER;
689
690 begin
691
692 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
693 G_PKG_NAME,
694 p_init_msg_list,
695 l_api_version,
696 p_api_version,
697 '_PVT',
698 x_return_status);
699 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
700 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
701 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
702 RAISE OKC_API.G_EXCEPTION_ERROR;
703 END IF;
704
705 lock_row(p_init_msg_list => 'F',
706 x_return_status => l_return_status,
707 x_msg_count => x_msg_count,
708 x_msg_data => x_msg_data,
709 p_tagv_rec => p_tagv_rec);
710 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
711 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
712 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
713 RAISE OKC_API.G_EXCEPTION_ERROR;
714 END IF;
715
716 delete
717 from OKC_REPORT_TAG_B
718 where ID = p_tagv_rec.ID;
719
720 delete
721 from OKC_REPORT_TAG_TL
722 where ID = p_tagv_rec.ID;
723
724 open xsl_csr(p_tagv_rec.ID);
725 fetch xsl_csr into l_xsl_id;
726 close xsl_csr;
727 if (l_xsl_id is not null) then
728 date_xsl(l_xsl_id);
729 end if;
730
731 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
732 EXCEPTION
733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
734 x_return_status := OKC_API.HANDLE_EXCEPTIONS
735 (
736 l_api_name,
737 G_PKG_NAME,
738 'OKC_API.G_RET_STS_ERROR',
739 x_msg_count,
740 x_msg_data,
741 '_PVT'
742 );
743 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
744 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
745 (
746 l_api_name,
747 G_PKG_NAME,
748 'OKC_API.G_RET_STS_UNEXP_ERROR',
749 x_msg_count,
750 x_msg_data,
751 '_PVT'
752 );
753 WHEN OTHERS THEN
754 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
755 (
756 l_api_name,
757 G_PKG_NAME,
758 'OTHERS',
759 x_msg_count,
760 x_msg_data,
761 '_PVT'
762 );
763 END delete_row;
764
765 END OKC_TAG_PVT;