DBA Data[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;