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