DBA Data[Home] [Help]

PACKAGE BODY: APPS.OKC_PRM_PVT

Source


1 PACKAGE BODY OKC_PRM_PVT AS
2 /* $Header: OKCSPRMB.pls 120.2 2006/03/01 13:38:34 smallya 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_PRM_TL (
27         ID,
28         LANGUAGE,
29         SOURCE_LANG,
30         SFWT_FLAG,
31         PROMPT,
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.PROMPT,
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_PRM_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_PRM_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_type
63 -- Description     :
64 -- Business Rules  :
65 -- Parameters      :
66 -- Version         : 1.0
67 -- End of comments
68 procedure validate_type(p_oper IN VARCHAR2,
69 			x_return_status OUT NOCOPY VARCHAR2,
70                         p_prmv_rec	  IN	prmv_rec_TYPE) is
71 begin
72   x_return_status := OKC_API.G_RET_STS_SUCCESS;
73   if (p_prmv_rec.type in ('DATE','NUMBER','VARCHAR2','LOV')) then
74     x_return_status := OKC_API.G_RET_STS_SUCCESS;
75     return;
76   end if;
77   if (p_prmv_rec.type is NULL or
78 	(p_prmv_rec.type = 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,'TYPE');
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,'TYPE');
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_type;
96 
97 -- Start of comments
98 --
99 -- Procedure Name  : validate_prompt
100 -- Description     :
101 -- Business Rules  :
102 -- Parameters      :
103 -- Version         : 1.0
104 -- End of comments
105 procedure validate_prompt(p_oper IN VARCHAR2,
106 			x_return_status OUT NOCOPY VARCHAR2,
107                         p_prmv_rec	  IN	prmv_rec_TYPE) is
108 begin
109   x_return_status := OKC_API.G_RET_STS_SUCCESS;
110   if (p_prmv_rec.prompt is NULL or
111 	(p_prmv_rec.prompt = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
112     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'PROMPT');
113     x_return_status := OKC_API.G_RET_STS_ERROR;
114     return;
115   end if;
116   return;
117 exception
118   when OTHERS then
119     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
120                         p_msg_name     => g_unexpected_error,
121                         p_token1       => g_sqlcode_token,
122                         p_token1_value => sqlcode,
123                         p_token2       => g_sqlerrm_token,
124                         p_token2_value => sqlerrm);
125     x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
126 end validate_prompt;
127 
128 -- Start of comments
129 --
130 -- Procedure Name  : validate_lov_query
131 -- Description     :
132 -- Business Rules  :
133 -- Parameters      :
134 -- Version         : 1.0
135 -- End of comments
136 procedure validate_lov_query(p_oper IN VARCHAR2,
137 			x_return_status OUT NOCOPY VARCHAR2,
138                         p_prmv_rec	  IN	prmv_rec_TYPE) is
139 begin
140   x_return_status := OKC_API.G_RET_STS_SUCCESS;
141   if ((p_prmv_rec.type = 'LOV') and
142       (p_prmv_rec.lov_query is NULL or
143 	(p_prmv_rec.lov_query = OKC_API.G_MISS_CHAR and p_oper = 'I')
144       )
145      ) then
146     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'LOV_QUERY');
147     x_return_status := OKC_API.G_RET_STS_ERROR;
148     return;
149   end if;
150   return;
151 exception
152   when OTHERS then
153     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
154                         p_msg_name     => g_unexpected_error,
155                         p_token1       => g_sqlcode_token,
156                         p_token1_value => sqlcode,
157                         p_token2       => g_sqlerrm_token,
158                         p_token2_value => sqlerrm);
159     x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
160 end validate_lov_query;
161 
162 -- Start of comments
163 --
164 -- Procedure Name  : validate_required_yn
165 -- Description     :
166 -- Business Rules  :
167 -- Parameters      :
168 -- Version         : 1.0
169 -- End of comments
170 procedure validate_required_yn(p_oper IN VARCHAR2,
171 			x_return_status OUT NOCOPY VARCHAR2,
172                         p_prmv_rec	  IN	prmv_rec_TYPE) is
173 begin
174   x_return_status := OKC_API.G_RET_STS_SUCCESS;
175   if (p_prmv_rec.required_yn in ('Y','N')) then
176     x_return_status := OKC_API.G_RET_STS_SUCCESS;
177     return;
178   end if;
179   if (p_prmv_rec.required_yn is NULL or
180 	(p_prmv_rec.required_yn = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
181     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'REQUIRED_YN');
182     x_return_status := OKC_API.G_RET_STS_ERROR;
183     return;
184   end if;
185   OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REQUIRED_YN');
186   x_return_status := OKC_API.G_RET_STS_ERROR;
187   return;
188 exception
189   when OTHERS then
190     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
191                         p_msg_name     => g_unexpected_error,
192                         p_token1       => g_sqlcode_token,
193                         p_token1_value => sqlcode,
194                         p_token2       => g_sqlerrm_token,
195                         p_token2_value => sqlerrm);
196     x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
197 end validate_required_yn;
198 
199 -- Start of comments
200 --
201 -- Procedure Name  : validate_sequence_num
202 -- Description     :
203 -- Business Rules  :
204 -- Parameters      :
205 -- Version         : 1.0
206 -- End of comments
207 procedure validate_sequence_num(p_oper IN VARCHAR2,
208 			x_return_status OUT NOCOPY VARCHAR2,
209                         p_prmv_rec	  IN	prmv_rec_TYPE) is
210 l_dummy varchar2(1) := '?';
211 cursor c1(n1 number, n2 number, n3 number) is
212     select '!'
213     from OKC_REPORT_PRM_V
214     where sql_id = n1 and sequence_num = n2
215     and id <> n3;
216 begin
217   x_return_status := OKC_API.G_RET_STS_SUCCESS;
218   if (p_prmv_rec.SQL_ID is NULL or
219 	(p_prmv_rec.SQL_ID = OKC_API.G_MISS_NUM and p_oper = 'I')) then
220     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'SQL_ID');
221     x_return_status := OKC_API.G_RET_STS_ERROR;
222     return;
223   end if;
224   if (p_prmv_rec.sequence_num is NULL or
225 	(p_prmv_rec.sequence_num = OKC_API.G_MISS_NUM and p_oper = 'I')) then
226     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'SEQUENCE_NUM');
227     x_return_status := OKC_API.G_RET_STS_ERROR;
228     return;
229   end if;
230     open c1(p_prmv_rec.SQL_ID,p_prmv_rec.SEQUENCE_NUM, p_prmv_rec.id);
231     fetch c1 into l_dummy;
232     close c1;
233     if (l_dummy = '!') then
234       OKC_API.SET_MESSAGE(p_app_name      =>  G_APP_NAME,
235 			  p_msg_name      =>  OKC_UTIL.G_UNQ,
236                           p_token1        =>  OKC_API.G_COL_NAME_TOKEN,
237 			  p_token1_value  =>  'SEQUENCE_NUM');
238       x_return_status := OKC_API.G_RET_STS_ERROR;
239     end if;
240 exception
241   when OTHERS then
242     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
243                         p_msg_name     => g_unexpected_error,
244                         p_token1       => g_sqlcode_token,
245                         p_token1_value => sqlcode,
246                         p_token2       => g_sqlerrm_token,
247                         p_token2_value => sqlerrm);
248     x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
249 end validate_sequence_num;
250 
251 -- Start of comments
252 --
253 -- Procedure Name  : validate_code
254 -- Description     :
255 -- Business Rules  :
256 -- Parameters      :
257 -- Version         : 1.0
258 -- End of comments
259 procedure validate_code(p_oper IN VARCHAR2,
260 			x_return_status OUT NOCOPY VARCHAR2,
261                         p_prmv_rec	  IN	prmv_rec_TYPE) is
262 l_dummy varchar2(1) := '?';
263 cursor c1(n1 number, c2 varchar2, n3 number) is
264     select '!'
265     from OKC_REPORT_PRM_V
266     where sql_id = n1 and code = c2
267     and id <> n3;
268 begin
269   x_return_status := OKC_API.G_RET_STS_SUCCESS;
270   if (p_prmv_rec.CODE = 'xid') then
271     OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE: xid');
272     x_return_status := OKC_API.G_RET_STS_ERROR;
273     return;
274   end if;
275   if (substr(p_prmv_rec.CODE,1,1) = '_') then
276     OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE: _...');
277     x_return_status := OKC_API.G_RET_STS_ERROR;
278     return;
279   end if;
280   if (instr(p_prmv_rec.CODE,'"') > 0) then
281     OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE: ..."...');
282     x_return_status := OKC_API.G_RET_STS_ERROR;
283     return;
284   end if;
285   if (p_prmv_rec.SQL_ID is NULL or
286 	(p_prmv_rec.SQL_ID = OKC_API.G_MISS_NUM and p_oper = 'I')) then
287     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'SQL_ID');
288     x_return_status := OKC_API.G_RET_STS_ERROR;
289     return;
290   end if;
291   if (p_prmv_rec.code is NULL or
292 	(p_prmv_rec.code = OKC_API.G_MISS_CHAR and p_oper = 'I')) then
293     OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'CODE');
294     x_return_status := OKC_API.G_RET_STS_ERROR;
295     return;
296   end if;
297     open c1(p_prmv_rec.SQL_ID,p_prmv_rec.CODE,p_prmv_rec.id);
298     fetch c1 into l_dummy;
299     close c1;
300     if (l_dummy = '!') then
301       OKC_API.SET_MESSAGE(p_app_name      =>  G_APP_NAME,
302 			  p_msg_name      =>  OKC_UTIL.G_UNQ,
303                           p_token1        =>  OKC_API.G_COL_NAME_TOKEN,
304 			  p_token1_value  =>  'CODE');
305       x_return_status := OKC_API.G_RET_STS_ERROR;
306     end if;
307 exception
308   when OTHERS then
309     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
310                         p_msg_name     => g_unexpected_error,
311                         p_token1       => g_sqlcode_token,
312                         p_token1_value => sqlcode,
313                         p_token2       => g_sqlerrm_token,
314                         p_token2_value => sqlerrm);
315     x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
316 end validate_code;
317 
318 FUNCTION Validate_Attributes (p_oper IN varchar2,
319     p_prmv_rec IN  prmv_rec_type
320   ) RETURN VARCHAR2 IS
321     l_return_status	VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
322     x_return_status  varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
323 BEGIN
324 
325     validate_type(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
326     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
327       return OKC_API.G_RET_STS_UNEXP_ERROR;
328     end if;
329     if (l_return_status = OKC_API.G_RET_STS_ERROR
330         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
331         x_return_status := OKC_API.G_RET_STS_ERROR;
332     end if;
333 
334     validate_lov_query(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
335     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
336       return OKC_API.G_RET_STS_UNEXP_ERROR;
337     end if;
338     if (l_return_status = OKC_API.G_RET_STS_ERROR
339         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
340         x_return_status := OKC_API.G_RET_STS_ERROR;
341     end if;
342 
343     validate_prompt(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
344     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
345       return OKC_API.G_RET_STS_UNEXP_ERROR;
346     end if;
347     if (l_return_status = OKC_API.G_RET_STS_ERROR
348         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
349         x_return_status := OKC_API.G_RET_STS_ERROR;
350     end if;
351 
352     validate_required_yn(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
353     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
354       return OKC_API.G_RET_STS_UNEXP_ERROR;
355     end if;
356     if (l_return_status = OKC_API.G_RET_STS_ERROR
357         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
358         x_return_status := OKC_API.G_RET_STS_ERROR;
359     end if;
360 
361     validate_sequence_num(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
362     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
363       return OKC_API.G_RET_STS_UNEXP_ERROR;
364     end if;
365     if (l_return_status = OKC_API.G_RET_STS_ERROR
366         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
367         x_return_status := OKC_API.G_RET_STS_ERROR;
368     end if;
369 
370     validate_code(p_oper => p_oper, x_return_status => l_return_status, p_prmv_rec => p_prmv_rec);
371     if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
372       return OKC_API.G_RET_STS_UNEXP_ERROR;
373     end if;
374     if (l_return_status = OKC_API.G_RET_STS_ERROR
375         and x_return_status = OKC_API.G_RET_STS_SUCCESS) then
376         x_return_status := OKC_API.G_RET_STS_ERROR;
377     end if;
378 
379     return x_return_status;
380   exception
381     when OTHERS then
382     OKC_API.SET_MESSAGE(p_app_name     => g_app_name,
383                         p_msg_name     => g_unexpected_error,
384                         p_token1       => g_sqlcode_token,
385                         p_token1_value => sqlcode,
386                         p_token2       => g_sqlerrm_token,
387                         p_token2_value => sqlerrm);
388       x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
389       return x_return_status;
390   END Validate_Attributes;
391 
392   ---------------------------------------------------------------------------
393   -- PROCEDURE insert_row
394   ---------------------------------------------------------------------------
395 
396   PROCEDURE date_sql(p_sql_id IN NUMBER) IS
397 
398     E_Resource_Busy               EXCEPTION;
399     PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
400 
401     CURSOR  sql_csr (p IN NUMBER) IS
402     SELECT '!' FROM OKC_REPORT_SQL_B
403     WHERE ID = p FOR UPDATE OF LAST_UPDATE_DATE;
404     l_dummy varchar2(1) := '?';
405 
406   begin
407     open sql_csr(p_sql_id);
408     fetch sql_csr into l_dummy;
409     close sql_csr;
410     update okc_report_sql_b set LAST_UPDATE_DATE = sysdate
411     where id = p_sql_id;
412   EXCEPTION
413     WHEN E_Resource_Busy THEN
414       IF (sql_csr%ISOPEN) THEN
415          CLOSE sql_csr;
416       END IF;
417       OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
418       RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
419   end;
420 
421   PROCEDURE insert_row(
422     p_api_version                  IN NUMBER,
423     p_init_msg_list                IN VARCHAR2 ,
424     x_return_status                OUT NOCOPY VARCHAR2,
425     x_msg_count                    OUT NOCOPY NUMBER,
426     x_msg_data                     OUT NOCOPY VARCHAR2,
427     p_prmv_rec                     IN prmv_rec_type,
428     x_prmv_rec                     OUT NOCOPY prmv_rec_type) IS
429 
430     l_api_version                  CONSTANT NUMBER := 1;
431     l_api_name                     CONSTANT VARCHAR2(30) := 'insert_row';
432     l_return_status                VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
433     l_prmv_rec                     prmv_rec_type;
434 
435   begin
436 
437     l_return_status := OKC_API.START_ACTIVITY(l_api_name,
438                                               G_PKG_NAME,
439                                               p_init_msg_list,
440                                               l_api_version,
441                                               p_api_version,
442                                               '_PVT',
443                                               x_return_status);
444     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
445       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
446     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
447       RAISE OKC_API.G_EXCEPTION_ERROR;
448     END IF;
449 
450     l_prmv_rec.ID 			:= get_seq_id;
451     l_prmv_rec.SQL_ID 			:= p_prmv_rec.SQL_ID;
452     l_prmv_rec.SEQUENCE_NUM 		:= p_prmv_rec.SEQUENCE_NUM;
453     l_prmv_rec.CODE 			:= p_prmv_rec.CODE;
454     l_prmv_rec.PROMPT 			:= p_prmv_rec.PROMPT;
455     l_prmv_rec.TYPE 			:= p_prmv_rec.TYPE;
456     l_prmv_rec.REQUIRED_YN 		:= p_prmv_rec.REQUIRED_YN;
457     l_prmv_rec.LOV_QUERY 		:= p_prmv_rec.LOV_QUERY;
458     l_prmv_rec.OBJECT_VERSION_NUMBER 	:= 1;
459     l_prmv_rec.CREATED_BY 		:= FND_GLOBAL.USER_ID;
460     l_prmv_rec.CREATION_DATE 		:= SYSDATE;
461     l_prmv_rec.LAST_UPDATED_BY 		:= FND_GLOBAL.USER_ID;
462     l_prmv_rec.LAST_UPDATE_DATE 	:= SYSDATE;
463     l_prmv_rec.LAST_UPDATE_LOGIN 	:= FND_GLOBAL.LOGIN_ID;
464 
465     l_return_status := Validate_Attributes('I',l_prmv_rec);
466     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
467       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
468     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
469       RAISE OKC_API.G_EXCEPTION_ERROR;
470     END IF;
471 
472     insert into OKC_REPORT_PRM_B
473     (
474 	 ID
475 	 ,SQL_ID
476 	 ,SEQUENCE_NUM
477 	 ,CODE
478 	 ,TYPE
479 	 ,REQUIRED_YN
480 	 ,LOV_QUERY
481 	 ,OBJECT_VERSION_NUMBER
482 	 ,CREATED_BY
483 	 ,CREATION_DATE
484 	 ,LAST_UPDATED_BY
485 	 ,LAST_UPDATE_DATE
486 	 ,LAST_UPDATE_LOGIN
487     ) values
488     (
489 	 l_prmv_rec.ID
490 	 ,l_prmv_rec.SQL_ID
491 	 ,l_prmv_rec.SEQUENCE_NUM
492 	 ,l_prmv_rec.CODE
493 	 ,l_prmv_rec.TYPE
494 	 ,l_prmv_rec.REQUIRED_YN
495 	 ,l_prmv_rec.LOV_QUERY
496 	 ,l_prmv_rec.OBJECT_VERSION_NUMBER
497 	 ,l_prmv_rec.CREATED_BY
498 	 ,l_prmv_rec.CREATION_DATE
499 	 ,l_prmv_rec.LAST_UPDATED_BY
500 	 ,l_prmv_rec.LAST_UPDATE_DATE
501 	 ,l_prmv_rec.LAST_UPDATE_LOGIN
502     );
503 
504     INSERT INTO OKC_REPORT_PRM_TL (
505         ID,
506         LANGUAGE,
507         SOURCE_LANG,
508         SFWT_FLAG,
509         PROMPT,
510         CREATED_BY,
511         CREATION_DATE,
512         LAST_UPDATED_BY,
513         LAST_UPDATE_DATE,
514         LAST_UPDATE_LOGIN)
515       SELECT
516   	    l_prmv_rec.ID,
517             L.LANGUAGE_CODE,
518             USERENV('LANG'),
519             decode(L.LANGUAGE_CODE,USERENV('LANG'),'N','Y'),
520   	    l_prmv_rec.PROMPT,
521  	    l_prmv_rec.CREATED_BY,
522 	    l_prmv_rec.CREATION_DATE,
523 	    l_prmv_rec.LAST_UPDATED_BY,
524 	    l_prmv_rec.LAST_UPDATE_DATE,
525 	    l_prmv_rec.LAST_UPDATE_LOGIN
526         FROM FND_LANGUAGES L
527        WHERE L.INSTALLED_FLAG IN ('I', 'B')
528      ;
529     date_sql(l_prmv_rec.SQL_ID);
530     x_prmv_rec := l_prmv_rec;
531     OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
532   EXCEPTION
533     WHEN OKC_API.G_EXCEPTION_ERROR THEN
534       x_return_status := OKC_API.HANDLE_EXCEPTIONS
535       (
536         l_api_name,
537         G_PKG_NAME,
538         'OKC_API.G_RET_STS_ERROR',
539         x_msg_count,
540         x_msg_data,
541         '_PVT'
542       );
543     WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
544       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
545       (
546         l_api_name,
547         G_PKG_NAME,
548         'OKC_API.G_RET_STS_UNEXP_ERROR',
549         x_msg_count,
550         x_msg_data,
551         '_PVT'
552       );
553     WHEN OTHERS THEN
554       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
555       (
556         l_api_name,
557         G_PKG_NAME,
558         'OTHERS',
559         x_msg_count,
560         x_msg_data,
561         '_PVT'
562       );
563   END insert_row;
564 
565   ---------------------------------------------------------------------------
566   -- PROCEDURE lock_row
567   ---------------------------------------------------------------------------
568 
569   PROCEDURE lock_row(
570     p_init_msg_list                IN VARCHAR2 ,
571     x_return_status                OUT NOCOPY VARCHAR2,
572     x_msg_count                    OUT NOCOPY NUMBER,
573     x_msg_data                     OUT NOCOPY VARCHAR2,
574     p_prmv_rec                     IN prmv_rec_type) IS
575 
576     E_Resource_Busy               EXCEPTION;
577     PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
578 
579     CURSOR lock_csr (p_prmv_rec IN prmv_rec_type) IS
580     SELECT OBJECT_VERSION_NUMBER
581       FROM OKC_REPORT_PRM_V
582      WHERE ID = p_prmv_rec.id
583        AND OBJECT_VERSION_NUMBER =
584          decode(p_prmv_rec.object_version_number,NULL,OBJECT_VERSION_NUMBER,
585 			OKC_API.G_MISS_NUM,OBJECT_VERSION_NUMBER,p_prmv_rec.object_version_number)
586     FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
587 
588     CURSOR  lchk_csr (p_prmv_rec IN prmv_rec_type) IS
589     SELECT OBJECT_VERSION_NUMBER
590       FROM OKC_REPORT_PRM_B
591     WHERE ID = p_prmv_rec.id;
592 
593     l_api_version                 CONSTANT NUMBER := 1;
594     l_api_name                     CONSTANT VARCHAR2(30) := 'lock_row';
595     l_return_status                VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
596 
597     l_object_version_number       NUMBER;
598     lc_object_version_number      NUMBER;
599     l_row_notfound                BOOLEAN := FALSE;
600     lc_row_notfound               BOOLEAN := FALSE;
601 
602   BEGIN
603     x_return_status := 'S';
604     l_return_status := OKC_API.START_ACTIVITY(l_api_name,
605                                               p_init_msg_list,
606                                               '_PVT',
607                                               x_return_status);
608     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
609       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
610     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
611       RAISE OKC_API.G_EXCEPTION_ERROR;
612     END IF;
613     BEGIN
614       OPEN lock_csr(p_prmv_rec);
615       FETCH lock_csr INTO l_object_version_number;
616       l_row_notfound := lock_csr%NOTFOUND;
617       CLOSE lock_csr;
618     EXCEPTION
619       WHEN E_Resource_Busy THEN
620         IF (lock_csr%ISOPEN) THEN
621           CLOSE lock_csr;
622         END IF;
623         OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
624         RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
625     END;
626 
627     IF ( l_row_notfound ) THEN
628       OPEN lchk_csr(p_prmv_rec);
629       FETCH lchk_csr INTO lc_object_version_number;
630       lc_row_notfound := lchk_csr%NOTFOUND;
631       CLOSE lchk_csr;
632       IF (lc_row_notfound) THEN
633         OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
634         RAISE OKC_API.G_EXCEPTION_ERROR;
635       ELSE
636         OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
637         RAISE OKC_API.G_EXCEPTION_ERROR;
638       END IF;
639     END IF;
640 
641     OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
642   EXCEPTION
643     WHEN OKC_API.G_EXCEPTION_ERROR THEN
644       x_return_status := OKC_API.HANDLE_EXCEPTIONS
645       (
646         l_api_name,
647         G_PKG_NAME,
648         'OKC_API.G_RET_STS_ERROR',
649         x_msg_count,
650         x_msg_data,
651         '_PVT'
652       );
653     WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
654       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
655       (
656         l_api_name,
657         G_PKG_NAME,
658         'OKC_API.G_RET_STS_UNEXP_ERROR',
659         x_msg_count,
660         x_msg_data,
661         '_PVT'
662       );
663     WHEN OTHERS THEN
664       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
665       (
666         l_api_name,
667         G_PKG_NAME,
668         'OTHERS',
669         x_msg_count,
670         x_msg_data,
671         '_PVT'
672       );
673   END lock_row;
674 
675   ---------------------------------------------------------------------------
676   -- PROCEDURE update_row
677   ---------------------------------------------------------------------------
678 
679   PROCEDURE update_row(
680     p_api_version                  IN NUMBER,
681     p_init_msg_list                IN VARCHAR2 ,
682     x_return_status                OUT NOCOPY VARCHAR2,
683     x_msg_count                    OUT NOCOPY NUMBER,
684     x_msg_data                     OUT NOCOPY VARCHAR2,
685     p_prmv_rec                     IN prmv_rec_type,
686     x_prmv_rec                     OUT NOCOPY prmv_rec_type) IS
687 
688     l_api_version                 CONSTANT NUMBER := 1;
689     l_api_name                     CONSTANT VARCHAR2(30) := 'update_row';
690     l_return_status                VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
691     l_prmv_rec                     prmv_rec_type := p_prmv_rec;
692 
693   begin
694 
695     l_return_status := OKC_API.START_ACTIVITY(l_api_name,
696                                               G_PKG_NAME,
697                                               p_init_msg_list,
698                                               l_api_version,
699                                               p_api_version,
700                                               '_PVT',
701                                               x_return_status);
702     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
703       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
704     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
705       RAISE OKC_API.G_EXCEPTION_ERROR;
706     END IF;
707 
708     lock_row(p_init_msg_list => 'F',
709 	    x_return_status => l_return_status,
710 	    x_msg_count     => x_msg_count,
711 	    x_msg_data      => x_msg_data,
712 	    p_prmv_rec      => p_prmv_rec);
713     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
714       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
715     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
716       RAISE OKC_API.G_EXCEPTION_ERROR;
717     END IF;
718 
719     l_prmv_rec.ID 			:= p_prmv_rec.ID;
720     select
721 	 SQL_ID
722 	,SEQUENCE_NUM
723 	,CODE
724 	,PROMPT
725 	,TYPE
726 	,REQUIRED_YN
727 	,LOV_QUERY
728 	,OBJECT_VERSION_NUMBER+1
729         ,created_by
730         ,creation_date
731     into
732 	 l_prmv_rec.SQL_ID
733 	,l_prmv_rec.SEQUENCE_NUM
734 	,l_prmv_rec.CODE
735 	,l_prmv_rec.PROMPT
736 	,l_prmv_rec.TYPE
737 	,l_prmv_rec.REQUIRED_YN
738 	,l_prmv_rec.LOV_QUERY
739 	,l_prmv_rec.OBJECT_VERSION_NUMBER
740         ,l_prmv_rec.created_by
741         ,l_prmv_rec.creation_date
742     from OKC_REPORT_PRM_V
743     where ID = l_prmv_rec.ID;
744 
745     l_prmv_rec.LAST_UPDATED_BY 		:= FND_GLOBAL.USER_ID;
746     l_prmv_rec.LAST_UPDATE_DATE 	:= SYSDATE;
747     l_prmv_rec.LAST_UPDATE_LOGIN 	:= FND_GLOBAL.LOGIN_ID;
748 
749 
750     if (p_prmv_rec.SQL_ID is NULL or p_prmv_rec.SQL_ID <> OKC_API.G_MISS_NUM) then
751 	l_prmv_rec.SQL_ID 			:= p_prmv_rec.SQL_ID;
752     end if;
753     if (p_prmv_rec.SEQUENCE_NUM is NULL or p_prmv_rec.SEQUENCE_NUM <> OKC_API.G_MISS_NUM) then
754 	l_prmv_rec.SEQUENCE_NUM 			:= p_prmv_rec.SEQUENCE_NUM;
755     end if;
756     if (p_prmv_rec.CODE is NULL or p_prmv_rec.CODE <> OKC_API.G_MISS_CHAR) then
757 	l_prmv_rec.CODE 			:= p_prmv_rec.CODE;
758     end if;
759     if (p_prmv_rec.PROMPT is NULL or p_prmv_rec.PROMPT <> OKC_API.G_MISS_CHAR) then
760 	l_prmv_rec.PROMPT 			:= p_prmv_rec.PROMPT;
761     end if;
762     if (p_prmv_rec.TYPE is NULL or p_prmv_rec.TYPE <> OKC_API.G_MISS_CHAR) then
763 	l_prmv_rec.TYPE 			:= p_prmv_rec.TYPE;
764     end if;
765     if (p_prmv_rec.REQUIRED_YN is NULL or p_prmv_rec.REQUIRED_YN <> OKC_API.G_MISS_CHAR) then
766 	l_prmv_rec.REQUIRED_YN 			:= p_prmv_rec.REQUIRED_YN;
767     end if;
768     if (p_prmv_rec.LOV_QUERY is NULL or p_prmv_rec.LOV_QUERY <> OKC_API.G_MISS_CHAR) then
769 	l_prmv_rec.LOV_QUERY 			:= p_prmv_rec.LOV_QUERY;
770     end if;
771 
772     l_return_status := Validate_Attributes('U',l_prmv_rec);
773     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
774       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
775     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
776       RAISE OKC_API.G_EXCEPTION_ERROR;
777     END IF;
778 
779     UPDATE OKC_REPORT_PRM_B set
780 	 SQL_ID = l_prmv_rec.SQL_ID
781 	 ,SEQUENCE_NUM = l_prmv_rec.SEQUENCE_NUM
782 	 ,CODE = l_prmv_rec.CODE
783 	 ,TYPE = l_prmv_rec.TYPE
784 	 ,REQUIRED_YN = l_prmv_rec.REQUIRED_YN
785 	 ,LOV_QUERY = l_prmv_rec.LOV_QUERY
786 	 ,OBJECT_VERSION_NUMBER = l_prmv_rec.OBJECT_VERSION_NUMBER
787 	 ,LAST_UPDATED_BY = l_prmv_rec.LAST_UPDATED_BY
788 	 ,LAST_UPDATE_DATE = l_prmv_rec.LAST_UPDATE_DATE
789 	 ,LAST_UPDATE_LOGIN = l_prmv_rec.LAST_UPDATE_LOGIN
790     where ID = l_prmv_rec.ID;
791 
792     UPDATE OKC_REPORT_PRM_TL set
793 	 SOURCE_LANG = USERENV('LANG')
794          ,SFWT_FLAG = decode(LANGUAGE,USERENV('LANG'),'N','Y')
795          ,PROMPT = l_prmv_rec.PROMPT
796 	 ,LAST_UPDATED_BY = l_prmv_rec.LAST_UPDATED_BY
797 	 ,LAST_UPDATE_DATE = l_prmv_rec.LAST_UPDATE_DATE
798 	 ,LAST_UPDATE_LOGIN = l_prmv_rec.LAST_UPDATE_LOGIN
799     where ID = l_prmv_rec.ID
800       and USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
801 
802     date_sql(l_prmv_rec.SQL_ID);
803     x_prmv_rec := l_prmv_rec;
804     OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
805   EXCEPTION
806     WHEN OKC_API.G_EXCEPTION_ERROR THEN
807       x_return_status := OKC_API.HANDLE_EXCEPTIONS
808       (
809         l_api_name,
810         G_PKG_NAME,
811         'OKC_API.G_RET_STS_ERROR',
812         x_msg_count,
813         x_msg_data,
814         '_PVT'
815       );
816     WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
817       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
818       (
819         l_api_name,
820         G_PKG_NAME,
821         'OKC_API.G_RET_STS_UNEXP_ERROR',
822         x_msg_count,
823         x_msg_data,
824         '_PVT'
825       );
826     WHEN OTHERS THEN
827       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
828       (
829         l_api_name,
830         G_PKG_NAME,
831         'OTHERS',
832         x_msg_count,
833         x_msg_data,
834         '_PVT'
835       );
836   END update_row;
837 
838 
839   ---------------------------------------------------------------------------
840   -- PROCEDURE delete_row
841   ---------------------------------------------------------------------------
842   PROCEDURE delete_row(
843     p_api_version                  IN NUMBER,
844     p_init_msg_list                IN VARCHAR2 ,
845     x_return_status                OUT NOCOPY VARCHAR2,
846     x_msg_count                    OUT NOCOPY NUMBER,
847     x_msg_data                     OUT NOCOPY VARCHAR2,
848     p_prmv_rec                     IN prmv_rec_type) IS
849 
850     l_api_version                 CONSTANT NUMBER := 1;
851     l_api_name                     CONSTANT VARCHAR2(30) := 'delete_row';
852     l_return_status                VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
853 
854     cursor sql_csr(p NUMBER) is
855     select S.id
856     from okc_report_prm_v PRM, okc_report_sql_v S
857     where PRM.id = p and S.id = PRM.sql_id;
858 
859     l_sql_id NUMBER;
860 
861   begin
862 
863     l_return_status := OKC_API.START_ACTIVITY(l_api_name,
864                                               G_PKG_NAME,
865                                               p_init_msg_list,
866                                               l_api_version,
867                                               p_api_version,
868                                               '_PVT',
869                                               x_return_status);
870     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
871       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
872     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
873       RAISE OKC_API.G_EXCEPTION_ERROR;
874     END IF;
875 
876     lock_row(p_init_msg_list => 'F',
877 	    x_return_status => l_return_status,
878 	    x_msg_count     => x_msg_count,
879 	    x_msg_data      => x_msg_data,
880 	    p_prmv_rec      => p_prmv_rec);
881     IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
882       RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
883     ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
884       RAISE OKC_API.G_EXCEPTION_ERROR;
885     END IF;
886 
887     delete
888     from OKC_REPORT_PRM_B
889     where ID = p_prmv_rec.ID;
890 
891     delete
892     from OKC_REPORT_PRM_TL
893     where ID = p_prmv_rec.ID;
894 
895     open sql_csr(p_prmv_rec.ID);
896     fetch sql_csr into l_sql_id;
897     close sql_csr;
898     if (l_sql_id is not null) then
899       date_sql(l_sql_id);
900     end if;
901 
902     OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
903   EXCEPTION
904     WHEN OKC_API.G_EXCEPTION_ERROR THEN
905       x_return_status := OKC_API.HANDLE_EXCEPTIONS
906       (
907         l_api_name,
908         G_PKG_NAME,
909         'OKC_API.G_RET_STS_ERROR',
910         x_msg_count,
911         x_msg_data,
912         '_PVT'
913       );
914     WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
915       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
916       (
917         l_api_name,
918         G_PKG_NAME,
919         'OKC_API.G_RET_STS_UNEXP_ERROR',
920         x_msg_count,
921         x_msg_data,
922         '_PVT'
923       );
924     WHEN OTHERS THEN
925       x_return_status :=OKC_API.HANDLE_EXCEPTIONS
926       (
927         l_api_name,
928         G_PKG_NAME,
929         'OTHERS',
930         x_msg_count,
931         x_msg_data,
932         '_PVT'
933       );
934   END delete_row;
935 
936 END OKC_PRM_PVT;