1 PACKAGE BODY OKC_UTIL AS
2 /* $Header: OKCUTILB.pls 120.2 2005/07/21 00:10:03 mmadhavi noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 l_Null_Val_Exception EXCEPTION;
7
8 -- This is a body global variable storing the value of
9 -- USERENV('LANG'). The value is cached into this variable so that
10 -- calling functions do not have to hit the database to determine this
11 -- value.
12
13 g_user_id NUMBER := OKC_API.G_MISS_NUM;
14 g_resp_id NUMBER := OKC_API.G_MISS_NUM;
15 g_reset_access_flag BOOLEAN := FALSE;
16 g_reset_lang_flag BOOLEAN := FALSE;
17 g_reset_resp_flag BOOLEAN := FALSE;
18 g_userenv_lang fnd_languages.language_code%TYPE;
19 g_resp_access okc_k_accesses.access_level%TYPE := OKC_API.G_MISS_CHAR;
20 g_user_resource_id okc_k_accesses.resource_id%TYPE := OKC_API.G_MISS_NUM;
21 g_scs_code okc_k_headers_b.scs_code%TYPE := OKC_API.G_MISS_CHAR;
22 g_groups_processed Boolean := False;
23
24 TYPE sec_group_tbl IS TABLE OF okc_k_accesses.group_id%TYPE;
25 g_sec_groups sec_group_tbl;
26
27 TYPE lenchk_rec_type IS RECORD (
28 VName VARCHAR2(30),
29 CName VARCHAR2(30),
30 CDType VARCHAR2(20),
31 CLength number,
32 CScale number);
33
34 TYPE lenchk_tbl_type IS TABLE OF lenchk_rec_type
35 INDEX by BINARY_INTEGER;
36
37 G_lenchk_tbl lenchk_tbl_type;
38 G_SPECIAL_STR constant VARCHAR2(3):='~*|';
39 G_COL_NAME_TOKEN1 CONSTANT VARCHAR2(30):='COL_NAME1';
40 G_COL_NAME_TOKEN2 CONSTANT VARCHAR2(30):='COL_NAME2';
41 G_COL_NAME_TOKEN3 CONSTANT VARCHAR2(30):='COL_NAME3';
42 G_COL_NAME_TOKEN4 CONSTANT VARCHAR2(30):='COL_NAME4';
43 G_COL_NAME_TOKEN5 CONSTANT VARCHAR2(30):='COL_NAME5';
44 G_COL_NAME_TOKEN6 CONSTANT VARCHAR2(30):='COL_NAME6';
45 G_COL_NAME_TOKEN7 CONSTANT VARCHAR2(30):='COL_NAME7';
46 G_COL_NAME_TOKEN8 CONSTANT VARCHAR2(30):='COL_NAME8';
47 G_COL_NAME_TOKEN9 CONSTANT VARCHAR2(30):='COL_NAME9';
48 G_COL_NAME_TOKEN0 CONSTANT VARCHAR2(30):='COL_NAME0';
49
50 ----------------------------------------------------------------------------
51 --Function to decide to a descriptive flexfield should be displayed
52 --It will return 'Y' if at least one of the DFF segment is both enabled and displayed
53 --It will return 'N' otherwise
54 --p_api_version: standard input parameter for the API version
55 --p_init_msg_list: standard input parameter for initialize message or not, defaulted to False
56 --p_application_short_name: the three letter application short name, e.g. 'OKC'
57 --p_dff_name: the name of the descriptive flexfield, e.g., 'DELIVERABLES_FLEX'
58 ----------------------------------------------------------------------------
59
60 FUNCTION Dff_Displayed ( p_api_version IN NUMBER,
61 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
62 p_application_short_name VARCHAR2,
63 p_dff_name VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER)
67 return VARCHAR2
68
69 IS
70
71 flexfield FND_DFLEX.dflex_r;
72 flexinfo FND_DFLEX.dflex_dr;
73 contexts FND_DFLEX.contexts_dr;
74 i BINARY_INTEGER;
75 segments FND_DFLEX.segments_dr;
76
77 l_module CONSTANT VARCHAR2(3) := 'OKC';
78 l_api_name CONSTANT VARCHAR2(30) := 'DFF_DISPLAYED';
79
80 l_displayed_yes CONSTANT VARCHAR2(1) := 'Y';
81 l_displayed_no CONSTANT VARCHAR2(1) := 'N';
82 l_return_success CONSTANT VARCHAR2(1) := 'S';
83 l_return_error CONSTANT VARCHAR2(1) := 'E';
84
85 BEGIN
86
87 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
88 okc_debug.Set_Indentation('OKC_UTIL');
89 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_module||l_api_name,'Entered Dff_Displayed');
90 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_module||l_api_name,'p_application_short_name' ||p_application_short_name);
91 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_module||l_api_name,'p_dff_name' ||p_dff_name);
92 END IF;
93
94
95 FND_DFLEX.get_flexfield(p_application_short_name, p_dff_name, flexfield, flexinfo);
96
97 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
98 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'=== FLEXFIELD INFO ===');
99 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'title=' || flexinfo.title);
100 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'table=' || flexinfo.table_name);
101 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'descr=' || flexinfo.description);
102 END IF;
103
104 FND_DFLEX.get_contexts(flexfield, contexts);
105 FOR i IN 1 .. contexts.ncontexts LOOP
106 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
107 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'Processing context: ' || contexts.context_code(i) || ' - ' ||
108 contexts.context_description(i));
109 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'=== SEGMENT INFO (for one context) ===');
110 END IF;
111
112 FND_DFLEX.get_segments(FND_DFLEX.make_context(flexfield, contexts.context_code(i)),
113 segments,
114 TRUE);
115 FOR i IN 1 .. segments.nsegments LOOP
116
117 IF(segments.is_displayed(i)) THEN
118 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'This segments is displayed: ' || segments.segment_name(i));
120 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'Returning Y');
121 END IF;
122
123 x_return_status := l_return_success;
124 RETURN l_displayed_yes;
125 END IF;
126
127 END LOOP; -- FOR i IN 1 .. segments.nsegments
128
129 END LOOP; -- FOR i IN 1 .. contexts.ncontexts
130
131 --After looping through all the segments, none are displayed
132 --Returning false
133 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,l_module||l_api_name,'None of the segments are displayed. Returning N');
135 END IF;
136
137
138 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,l_module||l_api_name,'Leaving Dff_Displayed');
140 END IF;
141
142 x_return_status := l_return_success;
143 RETURN l_displayed_no;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
148 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_module||l_api_name,'leaving OKC_UTIL.Dff_Displayed with error');
149 END IF;
150
151 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
152 FND_MSG_PUB.Add_Exc_Msg(l_module,l_api_name);
153 END IF;
154
155 x_return_status := l_return_error;
156
157 FND_MSG_PUB.Count_And_Get(
158 p_count => x_msg_count,
159 p_data => x_msg_data
160 );
161
162 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_module||l_api_name,'x_msg_count: '||x_msg_count);
164 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_module||l_api_name,'x_msg_data: '||x_msg_data);
165 END IF;
166
167 END Dff_Displayed;
168
169
170 Procedure Set_Connection_Context Is
171 Begin
172 If (g_user_id = OKC_API.G_MISS_NUM) Or
173 (g_user_id <> fnd_global.user_id) Then
174 g_user_id := fnd_global.user_id;
175 g_reset_access_flag := True;
176 g_reset_lang_flag := True;
177 End If;
178
179 If (g_resp_id = OKC_API.G_MISS_NUM) Or
180 (g_resp_id <> fnd_global.resp_id) Then
181 g_resp_id := fnd_global.resp_id;
182 g_reset_resp_flag := True;
183 End If;
184 End;
185
186 Procedure checknumlen(
187 p_view_name IN VARCHAR2,
188 p_col_name IN VARCHAR2,
189 p_col_value IN Number,
190 x_return_status OUT NOCOPY VARCHAR2,
191 ind IN Number);
192
193 ----------------------------------------------------------------------------
194 -- Function to add column token if column exists (private function)
195 ----------------------------------------------------------------------------
196 Function Column_Exists(e boolean,val varchar2) return varchar2 IS
197 Begin
198 If e then
199 return val;
200 else
201 return null;
202 end if;
203 end;
204
205 ----------------------------------------------------------------------------
206 -- Function to add column name if column exists (private function)
207 ----------------------------------------------------------------------------
208 Function Value_Exists(e unq_tbl_type,ind number) return varchar2 IS
209 Begin
210 If e.count>=ind then
211 return e(ind).p_col_name;
212 else
213 -- return null;
214 return ' ';
215 end if;
216 end;
217
218 /* Procedure add_view populates the global table for checking lengths.
219 x_return_status has 'S' if successful else 'E'
220 */
221 ----------------------------------------------------------------------------
222 -- Procedure to add a view for checking length into global table
223 ----------------------------------------------------------------------------
224 Procedure add_view(
225 p_view_name IN VARCHAR2,
226 x_return_status OUT NOCOPY VARCHAR2) IS
227 cursor av_csr is select table_name,Column_Name ,data_type,data_length,NVL(data_precision,OKC_API.G_MISS_NUM)
228 data_precision,NVL(data_scale,0) data_scale
229 FROM user_tab_columns
230 WHERE table_name = UPPER( p_view_name) and (data_type='VARCHAR2' OR data_type='NUMBER');
231 var1 av_csr%rowtype;
232 i number:=1;
233 found Boolean:=FALSE;
234 Begin
235 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
236 i:=G_lenchk_tbl.First;
237 If G_lenchk_tbl.Count>0 Then
238 Loop
239 if (UPPER(p_view_name)=UPPER(G_lenchk_tbl(i).vname)) Then
240 found:=TRUE;
241 Exit;
242 End if;
243 Exit when i=G_lenchk_tbl.Last;
244 i:=G_lenchk_tbl.Next(i);
245 End Loop;
246 End if;
247 If NOT found Then
248 OPEN av_csr;
249 i:=G_lenchk_tbl.count;
250 LOOP
251 FETCH av_csr into var1;
252 EXIT WHEN av_csr%NOTFOUND;
253 i:=i+1;
254 G_lenchk_tbl(i).vname:=var1.table_name;
255 G_lenchk_tbl(i).cname:=var1.column_name;
256 G_lenchk_tbl(i).cdtype:=var1.data_type;
257 if var1.data_type='NUMBER' Then
258 G_lenchk_tbl(i).clength:=var1.data_precision;
259 G_lenchk_tbl(i).cscale:=var1.data_scale;
260 else
261 G_lenchk_tbl(i).clength:=var1.data_length;
262 end if;
263 END LOOP;
264 If av_csr%ROWCOUNT<1 Then
265 x_return_status:=OKC_API.G_RET_STS_ERROR;
266 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
267 p_msg_name => G_NOTFOUND,
268 p_token1 => G_VIEW_TOKEN,
269 p_token1_value => UPPER(p_view_name));
270
271 End If;
272
273 CLOSE av_csr;
274 End If;
275
276 Exception
277 when others then
278 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
279 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
280 p_msg_name => G_UNEXPECTED_ERROR,
281 p_token1 => G_SQLCODE_TOKEN,
282 p_token1_value => sqlcode,
283 p_token2 => G_SQLERRM_TOKEN,
284 p_token2_value => sqlerrm);
285
286 End add_view;
287 /* Procedure check_length checks the length of the passed in value of column
288 x_return_status has 'S' if length is less than or equal to maximum length for that column
289 x_return_status has 'E' if length is more than maximum length for that column
290 x_return_status has 'U' if it cannot find the column in the global table populated trough add_view
291 */
292 ----------------------------------------------------------------------------
293 -- checks length of a varchar2 column
294 ----------------------------------------------------------------------------
295 Procedure check_length(
296 p_view_name IN VARCHAR2,
297 p_col_name IN VARCHAR2,
298 p_col_value IN VARCHAR2,
299 x_return_status OUT NOCOPY VARCHAR2) IS
300 i number:=0;
301 col_len number:=0;
302 Begin
303 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
304 i:=G_lenchk_tbl.First;
305 Loop
306 if ((UPPER(p_view_name)=UPPER(G_lenchk_tbl(i).vname)) and
307 (UPPER(p_col_name)=UPPER(G_lenchk_tbl(i).cname)) ) Then
308 If (UPPER(G_lenchk_tbl(i).cdtype)='VARCHAR2') Then
309 col_len:=nvl(length(p_col_value),0);
310 if col_len<=TRUNC((G_lenchk_tbl(i).CLength)/3) then
311 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
312 else
313 x_return_status:= OKC_API.G_RET_STS_ERROR;
314 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
315 p_msg_name => G_LEN_CHK,
316 p_token1 => G_COL_NAME_TOKEN,
317 p_token1_value => p_col_name,
318 p_token2 => 'COL_LEN',
319 p_token2_value => '('||trunc((G_lenchk_tbl(i).clength)/3)||')');
320 end if;
321 ElsIf (UPPER(G_lenchk_tbl(i).cdtype)='NUMBER') Then
322 checknumlen(p_view_name,p_col_name,to_number(p_col_value),x_return_status,i);
323
324 End If;
325 Exit;
326 End if;
327 Exit when i=G_lenchk_tbl.Last;
328 i:=G_lenchk_tbl.Next(i);
329 End Loop;
330
331 EXCEPTION
332 when others then
333 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
334
335 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
336 p_msg_name => G_UNEXPECTED_ERROR,
337 p_token1 => G_SQLCODE_TOKEN,
338 p_token1_value => sqlcode,
339 p_token2 => G_SQLERRM_TOKEN,
340 p_token2_value => sqlerrm);
341 End check_length;
342
343 ----------------------------------------------------------------------------
344 -- checks length of a number column (private procedure)
345 ----------------------------------------------------------------------------
346 Procedure checknumlen(
347 p_view_name IN VARCHAR2,
348 p_col_name IN VARCHAR2,
349 p_col_value IN Number,
350 x_return_status OUT NOCOPY VARCHAR2,
351 ind IN Number) IS
352 i Number:=ind;
353 l_pre Number :=0;
354 l_scale Number :=0;
355 l_str_pos Varchar2(40):='';
356 l_pos Number :=0;
357 l_neg Number :=0;
358 l_value Number :=0;
359 l_val varchar2(64):='.';
360 cursor c1 is select value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';
361 Begin
362 -- get the character specified for decimal right now in the database
363 open c1;
364 fetch c1 into l_val;
365 close c1;
366 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
367 l_value:=NVL(p_col_value,0);
368 IF (G_lenchk_tbl(i).clength=OKC_API.G_MISS_NUM) Then
369 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
370 ELSE
371 l_pre:=G_lenchk_tbl(i).clength-ABS(G_lenchk_tbl(i).cscale);
372 for j in 1..l_pre loop
373 l_str_pos:=l_str_pos||'9';
374 end loop;
375 l_scale:=G_lenchk_tbl(i).cscale;
376 If (l_scale>0) Then
377 --l_str_pos:=l_str_pos||'.';
378 l_str_pos:=l_str_pos||substr(l_val,1,1);
379 for j in 1..l_scale loop
380 l_str_pos:=l_str_pos||'9';
381 end loop;
382 ElsIf (l_scale<0) Then
383 for j in 1..ABS(l_scale) loop
384 l_str_pos:=l_str_pos||'0';
385 end loop;
386 end if;
387 l_pos:=to_number(l_str_pos);
388 l_neg:=(-1)*l_pos;
389 if l_value<=l_pos and l_value>=l_neg then
390 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
391 else
392 x_return_status:=OKC_API.G_RET_STS_ERROR;
393 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
394 p_msg_name => G_LEN_CHK,
395 p_token1 => G_COL_NAME_TOKEN,
396 p_token1_value => p_col_name);
397 end if;
398 End If;
399 EXCEPTION
400 when others then
401 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
402 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
403 p_msg_name => G_UNEXPECTED_ERROR,
404 p_token1 => G_SQLCODE_TOKEN,
405 p_token1_value => sqlcode,
406 p_token2 => G_SQLERRM_TOKEN,
407 p_token2_value => sqlerrm);
408 End checknumlen;
409
410 ----------------------------------------------------------------------------
411 -- checks length of a number column
412 ----------------------------------------------------------------------------
413 Procedure check_length(
414 p_view_name IN VARCHAR2,
415 p_col_name IN VARCHAR2,
416 p_col_value IN NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2) IS
418 Begin
419 check_length(p_view_name,p_col_name, to_char(p_col_value) , x_return_status);
420 End check_length;
421
422
423 /*---------------------------------------------------------------------------+
424 The procedure check_unique checks for uniqueness of the p_col_value passed.
425 It returns
426 OKC_API.G_RET_STS_SUCCESS if the p_col_value being passed is
427 unique that is not already in view p_view_name
428 OKC_API.G_RET_STS_ERROR If the p_col_value being passed is
429 not unique that is already in view p_view_name
430 OKC_API.G_RET_STS_UNEXP_ERROR If there is some unexpected error in processing
431 *-------------------------------------------------------------------------- */
432
433 ----------------------------------------------------------------------------
434 --checks uniqnuess of varchar2 when primary key is ID
435 ----------------------------------------------------------------------------
436 Procedure Check_Unique(
437 p_view_name IN VARCHAR2,
438 p_col_name IN VARCHAR2,
439 p_col_value IN VARCHAR2,
440 p_id IN NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2) IS
442 TYPE UnqTyp IS REF CURSOR;
443 unq_csr UnqTyp;
444 l_query VARCHAR2(1000);
445 l_id Number:=OKC_API.G_MISS_NUM;
446
447 Begin
448 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
449 IF (p_col_value is NULL) Then
450 RAISE l_Null_Val_Exception;
451 End If;
452
453 --form query
454 --l_query:='select ''1'' from ' || p_view_name || ' where ' || p_col_name || ' =:l_value and id <>l_id' ;
455 l_query:='select id from ' || p_view_name || ' where ' || p_col_name || ' =:l_value' ;
456 --Execute query
457 /*EXECUTE IMMEDIATE l_query INTO l_COUNT
458 USING p_col_value,p_id;*/
459
460 OPEN unq_csr FOR l_query
461 USING p_col_value;
462 FETCH unq_csr into l_id;
463 Close unq_csr;
464
465 IF (l_id<>OKC_API.G_MISS_NUM AND l_id<>nvl(p_id,0)) THEN
466 x_return_status:=OKC_API.G_RET_STS_ERROR;
467 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
468 p_msg_name => G_UNQ,
469 p_token1 => G_COL_NAME_TOKEN ,
470 p_token1_value => p_col_name);
471 END IF;
472
473 EXCEPTION
474 WHEN l_Null_Val_Exception then
475 x_return_status:=OKC_API.G_RET_STS_ERROR;
476 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
477 p_msg_name => G_NVL,
478 p_token1 => G_COL_NAME_TOKEN,
479 p_token1_value => p_col_name);
480
481
482 WHEN others then
483 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
484 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
485 p_msg_name => G_UNEXPECTED_ERROR,
486 p_token1 => G_SQLCODE_TOKEN,
487 p_token1_value => sqlcode,
488 p_token2 => G_SQLERRM_TOKEN,
489 p_token2_value => sqlerrm);
490 End Check_Unique;
491
492 ----------------------------------------------------------------------------
493 --checks uniquness of NUMBER when primary key is ID
494 ----------------------------------------------------------------------------
495 Procedure Check_Unique(
496 p_view_name IN VARCHAR2,
497 p_col_name IN VARCHAR2,
498 p_col_value IN NUMBER,
499 p_id IN NUMBER,
500 x_return_status OUT NOCOPY VARCHAR2) IS
501 TYPE UnqTyp IS REF CURSOR;
502 unq_csr UnqTyp;
503 l_query VARCHAR2(1000);
504 l_id Number:=OKC_API.G_MISS_NUM;
505
506 Begin
507 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
508 IF (p_col_value is NULL) Then
509 RAISE l_Null_Val_Exception;
510 End If;
511
512 --form query
513 --l_query:='select ''1'' from ' || p_view_name || ' where ' || p_col_name || ' =:l_value and id <>l_id' ;
514
515 l_query:='select id from ' || p_view_name || ' where ' || p_col_name || ' =:l_value' ;
516
517 --Execute query
518 /*EXECUTE IMMEDIATE l_query INTO l_COUNT
519 USING p_col_value,p_id;*/
520
521 OPEN unq_csr FOR l_query
522 USING p_col_value;
523 FETCH unq_csr into l_id;
524 Close unq_csr;
525
526 IF (l_id<>OKC_API.G_MISS_NUM AND l_id<>nvl(p_id,0)) THEN
527 x_return_status:=OKC_API.G_RET_STS_ERROR;
528 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
529 p_msg_name => G_UNQ,
530 p_token1 => G_COL_NAME_TOKEN ,
531 p_token1_value => p_col_name);
532 END IF;
533
534 EXCEPTION
535 WHEN l_Null_Val_Exception then
536 x_return_status:=OKC_API.G_RET_STS_ERROR;
537 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
538 p_msg_name => G_NVL,
539 p_token1 => G_COL_NAME_TOKEN,
540 p_token1_value => p_col_name);
541
542
543 WHEN others then
544 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
545 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
546 p_msg_name => G_UNEXPECTED_ERROR,
547 p_token1 => G_SQLCODE_TOKEN,
548 p_token1_value => sqlcode,
549 p_token2 => G_SQLERRM_TOKEN,
550 p_token2_value => sqlerrm);
551
552 End Check_Unique;
553
554 ---------------------------------------------------------------------------
555 --checks uniquness of DATE when primary key is ID
556 ----------------------------------------------------------------------------
557 Procedure Check_Unique(
558 p_view_name IN VARCHAR2,
559 p_col_name IN VARCHAR2,
560 p_col_value IN DATE,
561 p_id IN NUMBER,
562 x_return_status OUT NOCOPY VARCHAR2) IS
563
564 l_query VARCHAR2(1000);
565 l_id Number:=OKC_API.G_MISS_NUM;
566 TYPE UnqTyp IS REF CURSOR;
567 unq_csr UnqTyp;
568
569 Begin
570 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
571 IF (p_col_value is NULL) Then
572 RAISE l_Null_Val_Exception;
573 End If;
574
575 --Form query
576 l_query:='select id from ' || p_view_name || ' where trunc(' || p_col_name || ' ) =trunc(:l_value)';
577
578 --Execute query
579 OPEN unq_csr FOR l_query
580 USING p_col_value;
581 FETCH unq_csr into l_id;
582 Close unq_csr;
583
584 IF (l_id<>OKC_API.G_MISS_NUM AND l_id<>nvl(p_id,0)) THEN
585 x_return_status:=OKC_API.G_RET_STS_ERROR;
586 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
587 p_msg_name => G_UNQ,
588 p_token1 => G_COL_NAME_TOKEN ,
589 p_token1_value => p_col_name);
590
591 END IF;
592
593 EXCEPTION
594 WHEN l_Null_Val_Exception then
595 x_return_status:=OKC_API.G_RET_STS_ERROR;
596 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
597 p_msg_name => G_NVL,
598 p_token1 => G_COL_NAME_TOKEN,
599 p_token1_value => p_col_name);
600
601
602 WHEN others then
603 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
604 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
605 p_msg_name => G_UNEXPECTED_ERROR,
606 p_token1 => G_SQLCODE_TOKEN,
607 p_token1_value => sqlcode,
608 p_token2 => G_SQLERRM_TOKEN,
609 p_token2_value => sqlerrm);
610 End Check_Unique;
611
612 ----------------------------------------------------------------------------
613 --checks uniqueness of composite value made up of multiple columns when primary key is ID
614 ----------------------------------------------------------------------------
615 Procedure Check_Comp_Unique(
616 p_view_name IN VARCHAR2,
617 p_col_tbl IN unq_tbl_type,
618 p_id IN NUMBER,
619 x_return_status OUT NOCOPY VARCHAR2) IS
620
621 l_all_null Boolean:= TRUE;
622 l_query VARCHAR2(3000);
623 l_id Number:=OKC_API.G_MISS_NUM;
624 l_ind Number:=0;
625 l_index Number:=1;
626 l_cols unq_tbl_type;
627 TYPE UnqTyp IS REF CURSOR;
628 unq_csr UnqTyp;
629
630 Begin
631 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
632
633 l_query:='select id from ' || p_view_name || ' where ';
634
635 If p_col_tbl.Count>0 Then
636 l_ind:=p_col_tbl.FIRST;
637 Loop
638 IF (p_col_tbl(l_ind).p_col_val is NULL) Then
639 l_query:=l_query||p_col_tbl(l_ind).p_col_name||' is null';
640 else
641 l_query:=l_query||p_col_tbl(l_ind).p_col_name||'='''||replace(p_col_tbl(l_ind).p_col_val,'''','''''')||'''';
642 l_all_null := FALSE;
643 End If;
644 l_cols(l_index).p_col_name:=p_col_tbl(l_ind).p_col_name;
645 Exit when l_ind=p_col_tbl.Last;
646 l_query:=l_query||' and ' ;
647 l_ind:=p_col_tbl.Next(l_ind);
648 l_index:=l_index+1;
649 End Loop;
650
651 If l_all_null Then
652 RAISE l_Null_Val_Exception;
653 end if;
654
655 --Execute query
656 OPEN unq_csr FOR l_query;
657 FETCH unq_csr into l_id;
658 Close unq_csr;
659
660 IF (l_id<>OKC_API.G_MISS_NUM AND l_id<>nvl(p_id,0)) THEN
661 x_return_status:=OKC_API.G_RET_STS_ERROR;
662 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
663 p_msg_name => G_UNQS,
664 p_token1 => G_COL_NAME_TOKEN1,
665 p_token1_value => l_cols(1).p_col_name,
666 p_token2 => G_COL_NAME_TOKEN2,
667 p_token2_value => Value_Exists(l_cols,2),
668 p_token3 => G_COL_NAME_TOKEN3,
669 p_token3_value => Value_Exists(l_cols,3),
670 p_token4 => G_COL_NAME_TOKEN4,
671 p_token4_value => Value_Exists(l_cols,4),
672 p_token5 => G_COL_NAME_TOKEN5,
673 p_token5_value => Value_Exists(l_cols,5),
674 p_token6 => G_COL_NAME_TOKEN6,
675 p_token6_value => Value_Exists(l_cols,6),
676 p_token7 => G_COL_NAME_TOKEN7,
677 p_token7_value => Value_Exists(l_cols,7),
678 p_token8 => G_COL_NAME_TOKEN8,
679 p_token8_value => Value_Exists(l_cols,8),
680 p_token9 => G_COL_NAME_TOKEN9,
681 p_token9_value => Value_Exists(l_cols,9),
682 p_token10 => G_COL_NAME_TOKEN0,
683 p_token10_value => Value_Exists(l_cols,10));
684 /*
685 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
686 p_msg_name => G_UNQS,
687 p_token1 => G_COL_NAME_TOKEN,
688 p_token1_value => l_cols(1).p_col_name,
689 p_token2 => Column_Exists(l_cols.count>=2,G_COL_NAME_TOKEN),
690 p_token2_value => Value_Exists(l_cols,2),
691 p_token3 => Column_Exists(l_cols.count>=3,G_COL_NAME_TOKEN),
692 p_token3_value => Value_Exists(l_cols,3),
693 p_token4 => Column_Exists(l_cols.count>=4,G_COL_NAME_TOKEN),
694 p_token4_value => Value_Exists(l_cols,4),
695 p_token5 => Column_Exists(l_cols.count>=5,G_COL_NAME_TOKEN),
696 p_token5_value => Value_Exists(l_cols,5),
697 p_token6 => Column_Exists(l_cols.count>=6,G_COL_NAME_TOKEN),
698 p_token6_value => Value_Exists(l_cols,6),
699 p_token7 => Column_Exists(l_cols.count>=7,G_COL_NAME_TOKEN),
700 p_token7_value => Value_Exists(l_cols,7),
701 p_token8 => Column_Exists(l_cols.count>=8,G_COL_NAME_TOKEN),
702 p_token8_value => Value_Exists(l_cols,8),
703 p_token9 => Column_Exists(l_cols.count>=9,G_COL_NAME_TOKEN),
704 p_token9_value => Value_Exists(l_cols,9),
705 p_token10 => Column_Exists(l_cols.count>=10,G_COL_NAME_TOKEN),
706 p_token10_value => Value_Exists(l_cols,10));
707
708 */
709
710 END IF;
711
712 End If;
713
714 EXCEPTION
715 WHEN l_Null_Val_Exception then
716 x_return_status:=OKC_API.G_RET_STS_ERROR;
717 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
718 p_msg_name => G_ALL_NVL);
719
720 WHEN OTHERS then
721 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
722 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
723 p_msg_name => G_UNEXPECTED_ERROR,
724 p_token1 => G_SQLCODE_TOKEN,
725 p_token1_value => sqlcode,
726 p_token2 => G_SQLERRM_TOKEN,
727 p_token2_value => sqlerrm);
728 End Check_Comp_Unique;
729
730
731
732 ----------------------------------------------------------------------------
733 --checks uniqueness of varchar2 when primary key is other than ID
734 ----------------------------------------------------------------------------
735 Procedure Check_Unique(
736 p_table_name IN VARCHAR2,
737 p_col_name IN VARCHAR2,
738 p_col_value IN VARCHAR2,
739 p_primary IN unq_tbl_type,
740 x_return_status OUT NOCOPY VARCHAR2) IS
741
742 TYPE UnqTyp IS REF CURSOR;
743 unq_csr UnqTyp;
744 l_query VARCHAR2(1000);
745
746 l_pk_in_str VARCHAR2(600);
747 l_pk_cols VARCHAR2(600);
748 l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
749 l_column VARCHAR2(50);
750 l_ind number:=0;
751
752 Begin
753 IF (p_col_value is NULL) Then
754 RAISE l_Null_Val_Exception;
755 End If;
756 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
757
758 If p_primary.Count>0 Then
759 l_ind:=p_primary.FIRST;
760 --make a string out of primary key values (l_pk_in_str)
761 --make a string to make part of the queries which will fetch the
762 --string of primary key values for which p_col_value is found(l_pk_cols)
763 Loop
764 l_pk_in_str:=l_pk_in_str||nvl(p_primary(l_ind).p_col_val,' ')||G_SPECIAL_STR;
765 l_pk_cols :=l_pk_cols||p_primary(l_ind).p_col_name||'||'''||G_SPECIAL_STR||'''';
766 Exit when l_ind=p_primary.Last;
767 l_pk_cols :=l_pk_cols||'||';
768 l_ind:=p_primary.Next(l_ind);
769
770 End Loop;
771
772 --form query
773 l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where ' || p_col_name || ' =:l_value';
774
775 -- Here assumption is that since the coulmn always has unique value
776 --hence only one record with p_col_value can be there in the database maximum.
777 --Execute query
778 OPEN unq_csr FOR l_query
779 USING p_col_value;
780 FETCH unq_csr into l_pk_selected_str;
781 Close unq_csr;
782
783 -- The value returned in l_pk_selected_str is either its original value
784 --since no record was found or the found value is same as the string of
785 --primary key values passed thru the record. If its neither case
786 --then the p_col_value exists for some other primary key value.
787 --hence error
788 IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
789 x_return_status:=OKC_API.G_RET_STS_ERROR;
790 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
791 p_msg_name => G_UNQ,
792 p_token1 => G_COL_NAME_TOKEN ,
793 p_token1_value => p_col_name);
794
795 END IF;
796 END IF;
797
798 EXCEPTION
799 WHEN l_Null_Val_Exception then
800 x_return_status:=OKC_API.G_RET_STS_ERROR;
801 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
802 p_msg_name => G_NVL,
803 p_token1 => G_COL_NAME_TOKEN,
804 p_token1_value => p_col_name);
805
806
807 WHEN OTHERS then
808 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
809 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
810 p_msg_name => G_UNEXPECTED_ERROR,
811 p_token1 => G_SQLCODE_TOKEN,
812 p_token1_value => sqlcode,
813 p_token2 => G_SQLERRM_TOKEN,
814 p_token2_value => sqlerrm);
815 End Check_Unique;
816
817
818 ----------------------------------------------------------------------------
819 --checks uniqueness of NUMBER when primary key is other than ID
820 ----------------------------------------------------------------------------
821 Procedure Check_Unique(
822 p_table_name IN VARCHAR2,
823 p_col_name IN VARCHAR2,
824 p_col_value IN NUMBER,
825 p_primary IN unq_tbl_type,
826 x_return_status OUT NOCOPY VARCHAR2) IS
827 Begin
828 check_unique(p_table_name ,p_col_name,to_char(p_col_value),p_primary,x_return_status);
829 End Check_Unique;
830
831
832
833 ----------------------------------------------------------------------------
834 --checks uniqueness of varchar2 when primary key is other than ID
835 ----------------------------------------------------------------------------
836 Procedure Check_Unique(
837 p_table_name IN VARCHAR2,
838 p_col_name IN VARCHAR2,
839 p_col_value IN DATE,
840 p_primary IN unq_tbl_type,
841 x_return_status OUT NOCOPY VARCHAR2) IS
842
843 TYPE UnqTyp IS REF CURSOR;
844 unq_csr UnqTyp;
845 l_query VARCHAR2(1000);
846
847 l_pk_in_str VARCHAR2(600);
848 l_pk_cols VARCHAR2(600);
849 l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
850 l_column VARCHAR2(50);
851 l_ind number:=0;
852
853 Begin
854 IF (p_col_value is NULL) Then
855 RAISE l_Null_Val_Exception;
856 End If;
857 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
858
859 If p_primary.Count>0 Then
860 l_ind:=p_primary.FIRST;
861 --make a string out of primary key values (l_pk_in_str)
862 --make a string to make part of the query which will fetch the
863 --string of primary key values if p_col_value is found(l_pk_cols)
864 Loop
865 l_pk_in_str:=l_pk_in_str||nvl(p_primary(l_ind).p_col_val,' ')||G_SPECIAL_STR;
866 l_pk_cols :=l_pk_cols||p_primary(l_ind).p_col_name||'||'''||G_SPECIAL_STR||'''';
867 Exit when l_ind=p_primary.Last;
868 l_pk_cols :=l_pk_cols||'||';
869 l_ind:=p_primary.Next(l_ind);
870
871 End Loop;
872
873 --form query
874 l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where trunc(' || p_col_name || ') =trunc(:l_value)';
875
876 -- Here assumption is that since the coulmn always has unique value
877 --hence only one record with p_col_value can be there in the database maximum.
878 --Execute query
879 OPEN unq_csr FOR l_query
880 USING p_col_value;
881 FETCH unq_csr into l_pk_selected_str;
882 Close unq_csr;
883
884 -- The value returned in l_pk_selected_str is either its original value
885 --since no record was found or the found value is same as the string of
886 --primary key values passed thru the record. If its neither case
887 --then the p_col_value exists for some other primary key value.
888 --hence error
889 IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
890 x_return_status:=OKC_API.G_RET_STS_ERROR;
891 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
892 p_msg_name => G_UNQ,
893 p_token1 => G_COL_NAME_TOKEN ,
894 p_token1_value => p_col_name);
895
896 END IF;
897 END IF;
898
899 EXCEPTION
900 WHEN l_Null_Val_Exception then
901 x_return_status:=OKC_API.G_RET_STS_ERROR;
902 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
903 p_msg_name => G_NVL,
904 p_token1 => G_COL_NAME_TOKEN,
905 p_token1_value => p_col_name);
906
907
908 WHEN OTHERS then
909 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
910 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
911 p_msg_name => G_UNEXPECTED_ERROR,
912 p_token1 => G_SQLCODE_TOKEN,
913 p_token1_value => sqlcode,
914 p_token2 => G_SQLERRM_TOKEN,
915 p_token2_value => sqlerrm);
916 End Check_Unique;
917
918
919 ----------------------------------------------------------------------------
920 --checks uniqueness of composite value made up of multiple columns when primary key is other than ID
921 ----------------------------------------------------------------------------
922 Procedure Check_Comp_Unique(
923 p_table_name IN VARCHAR2,
924 p_col_tbl IN unq_tbl_type,
925 p_primary IN unq_tbl_type,
926 x_return_status OUT NOCOPY VARCHAR2) IS
927
928
929
930 l_pk_in_str VARCHAR2(600);
931 l_pk_cols VARCHAR2(600);
932 l_pk_selected_str VARCHAR2(600):=G_SPECIAL_STR;
933
934
935 l_query VARCHAR2(3000);
936 l_ind Number:=0;
937 l_index Number:=1;
938 l_cols unq_tbl_type;
939
940 TYPE UnqTyp IS REF CURSOR;
941 unq_csr UnqTyp;
942 l_all_null Boolean :=TRUE;
943
944 Begin
945 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
946
947 If p_primary.Count>0 Then
948 l_ind:=p_primary.FIRST;
949 --make a string out of primary key values (l_pk_in_str)
950 --make a string to make part of the query which will fetch the
951 --string of primary key values if set of values is found(l_pk_cols)
952 Loop
953 l_pk_in_str:=l_pk_in_str||nvl(p_primary(l_ind).p_col_val,' ')||G_SPECIAL_STR;
954 l_pk_cols :=l_pk_cols||p_primary(l_ind).p_col_name||'||'''||G_SPECIAL_STR||'''';
955 Exit when l_ind=p_primary.Last;
956 l_pk_cols :=l_pk_cols||'||';
957 l_ind:=p_primary.Next(l_ind);
958
959 End Loop;
960
961 -- form next query
962 l_query:='select ' || l_pk_cols||' from ' || p_table_name || ' where ';
963
964 If p_col_tbl.Count>0 Then
965 l_ind:=p_col_tbl.FIRST;
966 Loop
967 IF (p_col_tbl(l_ind).p_col_val is NULL) Then
968 l_query:=l_query||p_col_tbl(l_ind).p_col_name||' is null';
969 else
970 l_query:=l_query||p_col_tbl(l_ind).p_col_name||'='''||replace(p_col_tbl(l_ind).p_col_val,'''','''''')||'''';
971 l_all_null := FALSE;
972 End If;
973 l_cols(l_index).p_col_name:=p_col_tbl(l_ind).p_col_name;
974 Exit when l_ind=p_col_tbl.Last;
975 l_query:=l_query||' and ' ;
976 l_ind:=p_col_tbl.Next(l_ind);
977 l_index:=l_index+1;
978 End Loop;
979
980 If l_all_null Then
981 RAISE l_Null_Val_Exception;
982 End if;
983 -- Here assumption is that since the set of coulmns always has unique value
984 --hence only one record with these values can be there in the database maximum.
985 --Execute query
986 OPEN unq_csr FOR l_query;
987 FETCH unq_csr into l_pk_selected_str;
988 Close unq_csr;
989
990 -- The value returned in l_pk_selected_str is either its original value
991 --since no record was found or the found value is same as the string of
992 --primary key values passed thru the record. If its neither case
993 --then the p_col_value exists for some other primary key value.
994 --hence error
995 IF (l_pk_selected_str<>G_SPECIAL_STR AND l_pk_selected_str<>l_pk_in_str) THEN
996 x_return_status:=OKC_API.G_RET_STS_ERROR;
997 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
998 p_msg_name => G_UNQS,
999 p_token1 => G_COL_NAME_TOKEN1,
1000 p_token1_value => l_cols(1).p_col_name,
1001 p_token2 => G_COL_NAME_TOKEN2,
1002 p_token2_value => Value_Exists(l_cols,2),
1003 p_token3 => G_COL_NAME_TOKEN3,
1004 p_token3_value => Value_Exists(l_cols,3),
1005 p_token4 => G_COL_NAME_TOKEN4,
1006 p_token4_value => Value_Exists(l_cols,4),
1007 p_token5 => G_COL_NAME_TOKEN5,
1008 p_token5_value => Value_Exists(l_cols,5),
1009 p_token6 => G_COL_NAME_TOKEN6,
1010 p_token6_value => Value_Exists(l_cols,6),
1011 p_token7 => G_COL_NAME_TOKEN7,
1012 p_token7_value => Value_Exists(l_cols,7),
1013 p_token8 => G_COL_NAME_TOKEN8,
1014 p_token8_value => Value_Exists(l_cols,8),
1015 p_token9 => G_COL_NAME_TOKEN9,
1016 p_token9_value => Value_Exists(l_cols,9),
1017 p_token10 => G_COL_NAME_TOKEN0,
1018 p_token10_value => Value_Exists(l_cols,10));
1019 /*
1020 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1021 p_msg_name => G_UNQS,
1022 p_token1 => G_COL_NAME_TOKEN,
1023 p_token1_value => l_cols(1).p_col_name,
1024 p_token2 => Column_Exists(l_cols.count>=2,G_COL_NAME_TOKEN),
1025 p_token2_value => Value_Exists(l_cols,2),
1026 p_token3 => Column_Exists(l_cols.count>=3,G_COL_NAME_TOKEN),
1027 p_token3_value => Value_Exists(l_cols,3),
1028 p_token4 => Column_Exists(l_cols.count>=4,G_COL_NAME_TOKEN),
1029 p_token4_value => Value_Exists(l_cols,4),
1030 p_token5 => Column_Exists(l_cols.count>=5,G_COL_NAME_TOKEN),
1031 p_token5_value => Value_Exists(l_cols,5),
1032 p_token6 => Column_Exists(l_cols.count>=6,G_COL_NAME_TOKEN),
1033 p_token6_value => Value_Exists(l_cols,6),
1034 p_token7 => Column_Exists(l_cols.count>=7,G_COL_NAME_TOKEN),
1035 p_token7_value => Value_Exists(l_cols,7),
1036 p_token8 => Column_Exists(l_cols.count>=8,G_COL_NAME_TOKEN),
1037 p_token8_value => Value_Exists(l_cols,8),
1038 p_token9 => Column_Exists(l_cols.count>=9,G_COL_NAME_TOKEN),
1039 p_token9_value => Value_Exists(l_cols,9),
1040 p_token10 => Column_Exists(l_cols.count>=10,G_COL_NAME_TOKEN),
1041 p_token10_value => Value_Exists(l_cols,10));
1042 */
1043 END IF;
1044 End If;
1045 End If;
1046
1047 EXCEPTION
1048 WHEN l_Null_Val_Exception then
1049 x_return_status:=OKC_API.G_RET_STS_ERROR;
1050 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1051 p_msg_name => G_ALL_NVL);
1052
1053
1054 WHEN OTHERS then
1055 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
1056 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1057 p_msg_name => G_UNEXPECTED_ERROR,
1058 p_token1 => G_SQLCODE_TOKEN,
1059 p_token1_value => sqlcode,
1060 p_token2 => G_SQLERRM_TOKEN,
1061 p_token2_value => sqlerrm);
1062 End Check_Comp_Unique;
1063
1064 ----------------------------------------------------------------------------
1065 --Check uniquness for COMPOSITE/Primary key Columns in a table
1066 ----------------------------------------------------------------------------
1067 Procedure Check_Comp_Unique(
1068 p_view_name IN VARCHAR2,
1069 p_col_tbl IN unq_tbl_type,
1070 x_return_status OUT NOCOPY VARCHAR2) IS
1071
1072 l_all_null Boolean:=TRUE;
1073 l_query VARCHAR2(3000);
1074 l_count Varchar2(1):='0';
1075 l_ind Number:=0;
1076 l_index Number:=1;
1077 l_cols unq_tbl_type;
1078 TYPE UnqTyp IS REF CURSOR;
1079 unq_csr UnqTyp;
1080 --l_format varchar2(20):='YYYY';
1081 Begin
1082 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
1083 l_query:='select ''1'' from ' || p_view_name || ' where ';
1084 If p_col_tbl.Count>0 Then
1085 l_ind:=p_col_tbl.FIRST;
1086
1087 Loop
1088 IF (p_col_tbl(l_ind).p_col_val is NULL) Then
1089 l_query:=l_query||p_col_tbl(l_ind).p_col_name||' is null';
1090 else
1091 l_query:=l_query||p_col_tbl(l_ind).p_col_name||'='''||replace(p_col_tbl(l_ind).p_col_val,'''','''''')||'''';
1092 l_all_null := FALSE;
1093 End If;
1094 l_cols(l_index).p_col_name:=p_col_tbl(l_ind).p_col_name;
1095 Exit when l_ind=p_col_tbl.Last;
1096 l_query:=l_query||' and ' ;
1097 l_ind:=p_col_tbl.Next(l_ind);
1098 l_index:=l_index+1;
1099 End Loop;
1100
1101 /*
1102 For i in l_index+1 .. 10
1103 LOOP
1104 l_cols(i).p_col_name:='';
1105 end loop;
1106 */
1107 If l_all_null Then
1108 RAISE l_Null_Val_Exception;
1109 End if;
1110
1111
1112
1113 --Execute query
1114 OPEN unq_csr FOR l_query;
1115
1116 FETCH unq_csr into l_count;
1117 Close unq_csr;
1118 IF (l_COUNT='1') THEN
1119 x_return_status:=OKC_API.G_RET_STS_ERROR;
1120 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1121 p_msg_name => G_UNQS,
1122 p_token1 => G_COL_NAME_TOKEN1,
1123 p_token1_value => l_cols(1).p_col_name,
1124 p_token2 => G_COL_NAME_TOKEN2,
1125 p_token2_value => Value_Exists(l_cols,2),
1126 p_token3 => G_COL_NAME_TOKEN3,
1127 p_token3_value => Value_Exists(l_cols,3),
1128 p_token4 => G_COL_NAME_TOKEN4,
1129 p_token4_value => Value_Exists(l_cols,4),
1130 p_token5 => G_COL_NAME_TOKEN5,
1131 p_token5_value => Value_Exists(l_cols,5),
1132 p_token6 => G_COL_NAME_TOKEN6,
1133 p_token6_value => Value_Exists(l_cols,6),
1134 p_token7 => G_COL_NAME_TOKEN7,
1135 p_token7_value => Value_Exists(l_cols,7),
1136 p_token8 => G_COL_NAME_TOKEN8,
1137 p_token8_value => Value_Exists(l_cols,8),
1138 p_token9 => G_COL_NAME_TOKEN9,
1139 p_token9_value => Value_Exists(l_cols,9),
1140 p_token10 => G_COL_NAME_TOKEN0,
1141 p_token10_value => Value_Exists(l_cols,10));
1142 /*
1143 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1144 p_msg_name => G_UNQS,
1145 p_token1 => G_COL_NAME_TOKEN,
1146 p_token1_value => l_cols(1).p_col_name,
1147 p_token2 => Column_Exists(l_cols.count>=2,G_COL_NAME_TOKEN),
1148 p_token2_value => Value_Exists(l_cols,2),
1149 p_token3 => Column_Exists(l_cols.count>=3,G_COL_NAME_TOKEN),
1150 p_token3_value => Value_Exists(l_cols,3),
1151 p_token4 => Column_Exists(l_cols.count>=4,G_COL_NAME_TOKEN),
1152 p_token4_value => Value_Exists(l_cols,4),
1153 p_token5 => Column_Exists(l_cols.count>=5,G_COL_NAME_TOKEN),
1154 p_token5_value => Value_Exists(l_cols,5),
1155 p_token6 => Column_Exists(l_cols.count>=6,G_COL_NAME_TOKEN),
1156 p_token6_value => Value_Exists(l_cols,6),
1157 p_token7 => Column_Exists(l_cols.count>=7,G_COL_NAME_TOKEN),
1158 p_token7_value => Value_Exists(l_cols,7),
1159 p_token8 => Column_Exists(l_cols.count>=8,G_COL_NAME_TOKEN),
1160 p_token8_value => Value_Exists(l_cols,8),
1161 p_token9 => Column_Exists(l_cols.count>=9,G_COL_NAME_TOKEN),
1162 p_token9_value => Value_Exists(l_cols,9),
1163 p_token10 => Column_Exists(l_cols.count>=10,G_COL_NAME_TOKEN),
1164 p_token10_value => Value_Exists(l_cols,10));
1165 */
1166 END IF;
1167 End If;
1168 exception
1169 WHEN l_Null_Val_Exception then
1170 x_return_status:=OKC_API.G_RET_STS_ERROR;
1171 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1172 p_msg_name => G_ALL_NVL);
1173
1174 WHEN others then
1175 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
1176 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1177 p_msg_name => G_UNEXPECTED_ERROR,
1178 p_token1 => G_SQLCODE_TOKEN,
1179 p_token1_value => sqlcode,
1180 p_token2 => G_SQLERRM_TOKEN,
1181 p_token2_value => sqlerrm);
1182 End Check_Comp_Unique;
1183
1184
1185 procedure call_user_hook(x_return_status OUT NOCOPY VARCHAR2,
1186 p_package_name IN VARCHAR2,
1187 p_procedure_name IN VARCHAR2,
1188 p_before_after IN VARCHAR2) IS
1189 begin
1190 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1191 end;
1192
1193 /*procedure call_user_hook(x_return_status OUT NOCOPY VARCHAR2,
1194 p_package_name IN VARCHAR2,
1195 p_procedure_name IN VARCHAR2,
1196 p_before_after IN VARCHAR2) IS
1197
1198 l_statement VARCHAR2(200);
1199
1200 cursor c_pdf_using is
1201 SELECT uhcv.pdf_using_id
1202 FROM okc_process_defs_b pdfv,
1203 okc_user_hook_calls_b uhcv
1204 WHERE pdfv.id = uhcv.pdf_id
1205 AND pdfv.package_name = upper(p_package_name)
1206 AND pdfv.procedure_name = upper(p_procedure_name)
1207 ORDER BY uhcv.run_sequence;
1208
1209 cursor c_user_hook(p_pdf_using IN NUMBER) is
1210 SELECT decode(pdf.package_name, null, rtrim(pdf.procedure_name),
1211 rtrim(pdf.package_name) || '.' || rtrim(pdf.procedure_name)) proc_name
1212 FROM okc_process_defs_b pdf;
1213 WHERE pdf.before_after = p_before_after
1214 AND pdf.user_defined_yn = 'Y'
1215 AND pdf.id = p_pdf_using;
1216
1217 BEGIN
1218
1219 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1220 FOR l_c_pdf_using IN c_pdf_using LOOP
1221 FOR l_c_user_hook IN c_user_hook(l_c_pdf_using.pdf_using_id) LOOP
1222 l_statement := 'BEGIN ' || l_c_user_hook.proc_name ||
1223 '(:return_status); END;';
1224 EXECUTE IMMEDIATE l_statement USING OUT x_return_status;
1225 IF ((x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
1226 OR (x_return_status = OKC_API.G_RET_STS_ERROR)) THEN
1227 return;
1228 END IF;
1229 END LOOP;
1230 END LOOP;
1231 END call_user_hook;
1232 */
1233
1234 ----------------------------------------------------------------------------
1235 -- Count number of business days between two dates
1236 ----------------------------------------------------------------------------
1237 FUNCTION count_business_days(start_date IN DATE, end_date IN DATE)
1238 return NUMBER is
1239 v_current_date Date := start_date;
1240 v_day_of_week Varchar2(10); /* day of the week for v_current_date */
1241 v_counter Number := 0; /* Counter for business days */
1242 begin
1243 if end_date - start_date <= 0 then
1244 return(0);
1245 end if;
1246 loop
1247 v_current_date := v_current_date + 1;
1248 exit when v_current_date > end_date;
1249 v_day_of_week := to_char(v_current_date, 'fmDay');
1250 if v_day_of_week <> 'Saturday' and v_day_of_week <> 'Sunday' then
1251 v_counter := v_counter + 1;
1252 end if;
1253 end loop;
1254 return(v_counter);
1255 end count_business_days;
1256
1257 ----------------------------------------------------------------------------
1258 --Check if valid code for a type in fnd lookup
1259 ----------------------------------------------------------------------------
1260 FUNCTION Check_Lookup_Code (p_type in VARCHAR2,
1261 p_code IN VARCHAR2) return VARCHAR2 is
1262
1263 result Varchar2(1):= OKC_API.G_RET_STS_ERROR;
1264
1265 -- Bug 3674499 Need to truncate for dates
1266 cursor C1 is -- /striping/ only for p_type <> 'OKC_RULE_DEF'
1267 SELECT 'S'
1268 FROM fnd_lookups fndlup
1269 where fndlup.lookup_type = p_type
1270 and fndlup.lookup_code = p_code
1271 and trunc(sysdate) between
1272 trunc(nvl(fndlup.start_date_active,sysdate))
1273 and
1274 nvl(fndlup.end_date_active,sysdate);
1275
1276 -- /striping/ only for p_type = 'OKC_RULE_DEF'
1277 cursor C2 is
1278 SELECT 'S'
1279 FROM okc_rule_defs_v fndlup
1280 where fndlup.rule_code = p_code;
1281
1282 Begin
1283 IF (p_type is NULL) OR (p_code is NULL) Then
1284 RAISE l_Null_Val_Exception;
1285 End If;
1286 -- /striping/
1287 if p_type = 'OKC_RULE_DEF' then
1288 open C2;
1289 fetch C2 into result;
1290 if C2%NOTFOUND then result := OKC_API.G_RET_STS_ERROR; end if;
1291 close C2;
1292 else
1293 open C1;
1294 fetch C1 into result;
1295 if C1%NOTFOUND then result := OKC_API.G_RET_STS_ERROR; end if;
1296 close C1;
1297 end if;
1298
1299 If result='S' then
1300 result:=OKC_API.G_RET_STS_SUCCESS;
1301 end if;
1302 return result;
1303 EXCEPTION
1304 WHEN l_Null_Val_Exception then
1305 result := OKC_API.G_RET_STS_UNEXP_ERROR;
1306 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1307 p_msg_name => G_NVL_CODE);
1308 return result;
1309
1310 WHEN OTHERS THEN
1311 result:=OKC_API.G_RET_STS_UNEXP_ERROR;
1312 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1313 p_msg_name => G_UNEXPECTED_ERROR,
1314 p_token1 => G_SQLCODE_TOKEN,
1315 p_token1_value => sqlcode,
1316 p_token2 => G_SQLERRM_TOKEN,
1317 p_token2_value => sqlerrm);
1318 If C1%ISOPEN Then close C1; End If;
1319 If C2%ISOPEN Then close C2; End If;
1320 return result;
1321 END check_lookup_code;
1322
1323 -- functions from JOHN for JTF objects
1324
1325 FUNCTION GET_NAME_FROM_JTFV(
1326 p_object_code IN VARCHAR2,
1327 p_id1 IN VARCHAR2,
1328 p_id2 IN VARCHAR2)
1329 RETURN VARCHAR2 IS
1330 l_name VARCHAR2(255);
1331 l_from_table VARCHAR2(200);
1332 l_where_clause VARCHAR2(2000);
1333 l_sql_stmt VARCHAR2(500);
1334 l_not_found BOOLEAN;
1335
1336 Cursor jtfv_csr IS
1337 SELECT FROM_TABLE, WHERE_CLAUSE
1338 FROM JTF_OBJECTS_B
1339 WHERE OBJECT_CODE = p_object_code;
1340 Type SOURCE_CSR IS REF CURSOR;
1341 c SOURCE_CSR;
1342
1343 BEGIN
1344 open jtfv_csr;
1345 fetch jtfv_csr into l_from_table, l_where_clause;
1346 l_not_found := jtfv_csr%NOTFOUND;
1347 close jtfv_csr;
1348
1349 If (l_not_found) Then
1350 --my_message('Cannot find OBJECT in JTF_OBJECTS_B table');
1351 return NULL;
1352 End if;
1353
1354 l_sql_stmt := 'SELECT name FROM ' || l_from_table ||
1355 ' WHERE ID1 = :id_1 AND ID2 = :id2';
1356 If (l_where_clause is not null) Then
1357 l_sql_stmt := l_sql_stmt || ' AND ' || l_where_clause;
1358 End If;
1359 open c for l_sql_stmt using p_id1, p_id2;
1360 fetch c into l_name;
1361 l_not_found := c%NOTFOUND;
1362 close c;
1363
1364
1365
1366 If (l_not_found) Then
1367 --my_message('Cannot find NAME in ' || l_from_table || ' table');
1368 return NULL;
1369 End if;
1370 return l_name;
1371 EXCEPTION
1372 when NO_DATA_FOUND then
1373 If (jtfv_csr%ISOPEN) Then
1374 Close jtfv_csr;
1375 End If;
1376 If (c%ISOPEN) Then
1377 Close c;
1378 End If;
1379 return NULL;
1380 END;
1381
1382 FUNCTION GET_DESC_FROM_JTFV(
1383 p_object_code IN VARCHAR2,
1384 p_id1 IN VARCHAR2,
1385 p_id2 IN VARCHAR2)
1386 RETURN VARCHAR2 IS
1387 l_description VARCHAR2(255);
1388 l_from_table VARCHAR2(200);
1389 l_where_clause VARCHAR2(2000);
1390 l_sql_stmt VARCHAR2(500);
1391 l_not_found BOOLEAN;
1392
1393 Cursor jtfv_csr IS
1394 SELECT FROM_TABLE, WHERE_CLAUSE
1395 FROM JTF_OBJECTS_B
1396 WHERE OBJECT_CODE = p_object_code;
1397 Type SOURCE_CSR IS REF CURSOR;
1398 c SOURCE_CSR;
1399
1400 BEGIN
1401 open jtfv_csr;
1402 fetch jtfv_csr into l_from_table, l_where_clause;
1403 l_not_found := jtfv_csr%NOTFOUND;
1404 close jtfv_csr;
1405
1406 If (l_not_found) Then
1407 --my_message('Cannot find OBJECT in JTF_OBJECTS_B table');
1408 return NULL;
1409 End if;
1410
1411
1412 l_sql_stmt := 'SELECT description FROM ' || l_from_table ||
1413 ' WHERE ID1 = :id_1 AND ID2 = :id2';
1414 If (l_where_clause is not null) Then
1415 l_sql_stmt := l_sql_stmt || ' AND ' || l_where_clause;
1416 End If;
1417 open c for l_sql_stmt using p_id1, p_id2;
1418 fetch c into l_description;
1419 l_not_found := c%NOTFOUND;
1420 close c;
1421 If (l_not_found) Then
1422 --my_message('Cannot find NAME in ' || l_from_table || ' table');
1423 return NULL;
1424 End if;
1425 return l_description;
1426 EXCEPTION
1427 when NO_DATA_FOUND then
1428 If (jtfv_csr%ISOPEN) Then
1429 Close jtfv_csr;
1430 End If;
1431 If (c%ISOPEN) Then
1432 Close c;
1433 End If;
1434 return NULL;
1435 END;
1436
1437 PROCEDURE GET_NAME_DESC_FROM_JTFV(
1438 p_object_code IN VARCHAR2,
1439 p_id1 IN VARCHAR2,
1440 p_id2 IN VARCHAR2,
1441 x_name OUT NOCOPY VARCHAR2,
1442 x_description OUT NOCOPY VARCHAR2)
1443 IS
1444 l_name VARCHAR2(255);
1445 l_description VARCHAR2(255);
1446 l_from_table VARCHAR2(200);
1447 l_where_clause VARCHAR2(2000);
1448 l_sql_stmt VARCHAR2(500);
1449 l_not_found BOOLEAN;
1450
1451 Cursor jtfv_csr IS
1452 SELECT FROM_TABLE, WHERE_CLAUSE
1453 FROM JTF_OBJECTS_B
1454 WHERE OBJECT_CODE = p_object_code;
1455 Type SOURCE_CSR IS REF CURSOR;
1456 c SOURCE_CSR;
1457
1458 BEGIN
1459 open jtfv_csr;
1460 fetch jtfv_csr into l_from_table, l_where_clause;
1461 l_not_found := jtfv_csr%NOTFOUND;
1462 close jtfv_csr;
1463
1464 If (l_not_found) Then
1465 --my_message('Cannot find OBJECT in JTF_OBJECTS_B table');
1466 return;
1467 End if;
1468 l_sql_stmt := 'SELECT name,description FROM ' || l_from_table ||
1469 ' WHERE ID1 = :id_1 AND ID2 = :id2';
1470 If (l_where_clause is not null) Then
1471 l_sql_stmt := l_sql_stmt || ' AND ' || l_where_clause;
1472 End If;
1473 open c for l_sql_stmt using p_id1, p_id2;
1474 fetch c into l_name,l_description;
1475 l_not_found := c%NOTFOUND;
1476 close c;
1477 If (l_not_found) Then
1478 x_name := '';
1479 x_description := '';
1480 --my_message('Cannot find NAME and DESCRIPTION in ' || l_from_table || ' table');
1481 Else
1482 x_name := l_name;
1483 x_description := l_description;
1484 End if;
1485 EXCEPTION
1486 when NO_DATA_FOUND then
1487 If (jtfv_csr%ISOPEN) Then
1488 Close jtfv_csr;
1489 End If;
1490 If (c%ISOPEN) Then
1491 Close c;
1492 End If;
1493 x_name := NULL;
1494 x_description := NULL;
1495 END;
1496
1497 FUNCTION GET_SQL_FROM_JTFV(p_object_code IN VARCHAR2)
1498 RETURN VARCHAR2 IS
1499 l_from_table VARCHAR2(200);
1500 l_where_clause VARCHAR2(2000);
1501 l_order_by_clause VARCHAR2(200);
1502 l_return_str VARCHAR(2500);
1503 l_not_found BOOLEAN;
1504 Cursor jtfv_csr(p_object_code IN VARCHAR2) Is
1505 SELECT from_table, where_clause, order_by_clause
1506 FROM jtf_objects_b
1507 WHERE object_code = p_object_code;
1508 BEGIN
1509 open jtfv_csr(p_object_code);
1510 fetch jtfv_csr into l_from_table, l_where_clause, l_order_by_clause;
1511 l_not_found := jtfv_csr%NOTFOUND;
1512 close jtfv_csr;
1513 If (l_not_found) Then
1514 return NULL;
1515 Else
1516 l_return_str := l_from_table;
1517 If (l_where_clause is not null) Then
1518 l_return_str := l_return_str || ' WHERE ' || l_where_clause;
1519 End If;
1520 If (l_order_by_clause is not null) Then
1521 If (upper(substr(l_order_by_clause,1,8)) = 'ORDER BY') Then
1522 l_return_str := l_return_str || ' ' || l_order_by_clause;
1523 Else
1524 l_return_str := l_return_str || ' ORDER BY ' || l_order_by_clause;
1525 End If;
1526 End If;
1527 return l_return_str;
1528 End If;
1529 EXCEPTION
1530 when NO_DATA_FOUND then
1531 If (jtfv_csr%ISOPEN) Then
1532 Close jtfv_csr;
1533 End If;
1534 return NULL;
1535 END;
1536
1537 FUNCTION GET_SELECTNAME_FROM_JTFV(
1538 p_object_code IN VARCHAR2,
1539 p_id IN NUMBER)
1540 RETURN VARCHAR2 IS
1541 l_selname VARCHAR2(2000);
1542 l_select_id VARCHAR2(2000);
1543 l_select_name VARCHAR2(2500);
1544 l_from_table VARCHAR2(200);
1545 l_where_clause VARCHAR2(2000);
1546 l_sql_stmt VARCHAR2(2000);
1547 l_not_found BOOLEAN;
1548
1549 CURSOR jtfv_csr IS
1550 SELECT SELECT_ID,SELECT_NAME,FROM_TABLE,WHERE_CLAUSE
1551 FROM JTF_OBJECTS_B
1552 WHERE OBJECT_CODE = p_object_code;
1553 Type SOURCE_CSR IS REF CURSOR ;
1554 c SOURCE_CSR;
1555 BEGIN
1556 OPEN jtfv_csr;
1557 FETCH jtfv_csr INTO l_select_id,l_select_name,l_from_table,l_where_clause;
1558 l_not_found := jtfv_csr%NOTFOUND;
1559 CLOSE jtfv_csr;
1560 IF l_not_found THEN
1561 RETURN NULL;
1562 END IF;
1563
1564 IF p_object_code = 'OKC_K_LINE' THEN
1565 l_sql_stmt := 'SELECT contract_number||'||''''||' '||''''||'||contract_number_modifier||'||''''||' '||''''||'||line_number'||' '||
1566 'FROM okc_k_headers_b khr,okc_k_lines_b khl,okc_condition_headers_b cnh WHERE khr.id = khl.dnz_chr_id and cnh.object_id = khl.id and khl.Id = :id';
1567 ELSE
1568 l_sql_stmt := 'SELECT ' || l_select_name || ' FROM ' || l_from_table || ' WHERE ' || l_select_id || ' = :id';
1569 /*l_sql_stmt := 'SELECT ' || l_select_name || ' FROM ' || l_from_table;
1570
1571 IF l_where_clause IS NOT NULL THEN
1572 l_sql_stmt := l_sql_stmt || ' WHERE ' || l_where_clause;
1573 END IF;*/
1574 END IF;
1575 OPEN c FOR l_sql_stmt USING p_id;
1576
1577 FETCH c INTO l_selname;
1578 l_not_found := c%NOTFOUND;
1579 CLOSE c;
1580 IF l_not_found THEN
1581 RETURN NULL;
1582 END IF;
1583
1584 RETURN l_selname;
1585
1586 EXCEPTION
1587 WHEN NO_DATA_FOUND THEN
1588 IF (jtfv_csr%ISOPEN) THEN
1589 CLOSE jtfv_csr;
1590 END IF;
1591 IF (c%ISOPEN) THEN
1592 CLOSE c;
1593 END IF;
1594 RETURN NULL;
1595 END;
1596
1597 ----------------------------------------------------------------------
1598 --- get_k_access_level
1599 ----------------------------------------------------------------------
1600 -- Function Get_K_Access_Level
1601 -- This function checks whether the current user has access to a given
1602 -- contract. The contract id and the subclass (optionally) are passed
1603 -- in. The called from parameter denotes whether the function was called
1604 -- from forms or the Java(security) code in contracts online. An orig
1605 -- source code of KSSA_HDR means that the contract was created in contracts
1606 -- online. Currently to isolate the contracts from contracts online and
1607 -- the contracts created in forms, a contracts created in forms will have
1608 -- only a read access in online, except attachments. Any
1609 -- attachment created in forms, can be updated in contracts online subject
1610 -- to the modify access being available to the user. Any contract created
1611 -- in contracts online can be modified in forms as per the rules pertaining
1612 -- to forms contracts.
1613 -- It returns the highest type of access that the user has based on the
1614 -- setup and the source. The types are:
1615 -- U - Update
1616 -- R - Read only
1617 -- N - No access
1618 -- For contracts online, a null will be returned, if the contract was not found
1619 ----------------------------------------------------------------------
1620
1621 Function Get_K_Access_Level(p_chr_id IN NUMBER,
1622 p_scs_code IN VARCHAR2 ,
1623 p_called_from IN VARCHAR2 , -- F for forms, W for contracts online
1624 p_update_attachment IN VARCHAR2 ,
1625 p_orig_source_code IN VARCHAR2)
1626 RETURN Varchar2 IS
1627
1628 l_scs_code okc_k_headers_b.scs_code%TYPE;
1629
1630 l_partner_cat CONSTANT Varchar2(7) := 'PARTNER';
1631
1632 l_modify_access CONSTANT Varchar2(1) := 'U';
1633 l_read_access CONSTANT Varchar2(1) := 'R';
1634 l_no_access CONSTANT Varchar2(1) := 'N';
1635
1636 l_resp_access okc_subclass_resps.access_level%TYPE;
1637 l_resource_access okc_subclass_resps.access_level%TYPE;
1638 l_group_access okc_k_accesses.access_level%TYPE;
1639 l_group_id okc_k_accesses.group_id%TYPE;
1640
1641 l_row_notfound Boolean;
1642 l_group_has_read_access Boolean;
1643
1644 l_date Date := Sysdate;
1645 l_orig_sys_source_code okc_k_headers_v.orig_system_source_code%TYPE;
1646 l_ret_status Varchar2(1) ;
1647 l_k_check Varchar2(1);
1648
1649 l_multiorg VARCHAR2(1) := NVL(FND_PROFILE.VALUE('OKC_VIEW_K_BY_ORG'),'N');
1650 l_authoring_org_id number;
1651 l_org_id number := NVL(FND_PROFILE.VALUE('ORG_ID'), -99);
1652
1653 exception_modify_access Exception;
1654 exception_read_access Exception;
1655 exception_no_access Exception;
1656
1657 -- This cursor retrieves the sub class code for the contract. This is
1658 -- executed only if the subclass is not passed in
1659
1660 CURSOR chr_csr IS
1661 SELECT scs_code,nvl(orig_system_source_code,'NOSOURCECODE')
1662 FROM okc_k_headers_b
1663 WHERE id = p_chr_id;
1664
1665 -- This cursor checks to see the type of access granted to the current
1666 -- user's responsibility to the sub class
1667
1668 CURSOR resp_csr IS
1669 SELECT ras.access_level
1670 FROM okc_subclass_resps ras
1671 WHERE ras.scs_code = l_scs_code
1672 AND ras.resp_id = fnd_global.resp_id
1673 AND l_date BETWEEN ras.start_date AND nvl(ras.end_date, l_date);
1674
1675 -- This cursor retrieves the resource id corresponding to the logged
1676 -- in user. The resource has to have a role of CONTRACT for this to be
1677 -- considered
1678
1679 CURSOR res_csr IS
1680 SELECT res.resource_id
1681 FROM jtf_rs_resource_extns res,
1682 jtf_rs_role_relations rrr,
1683 jtf_rs_roles_b rr
1684 WHERE res.user_id = fnd_global.user_id
1685 AND l_date between res.start_date_active
1686 and nvl(res.end_date_active, l_date)
1687 AND res.resource_id = rrr.role_resource_id
1688 AND rrr.role_resource_type = 'RS_INDIVIDUAL'
1689 AND nvl(rrr.delete_flag,'N') = 'N'
1690 AND l_date between rrr.start_date_active
1691 and nvl(rrr.end_date_active, l_date)
1692 AND rrr.role_id = rr.role_id
1693 AND rr.role_type_code = 'CONTRACTS';
1694
1695 -- This checks the access level for the resource and the contract
1696
1697 CURSOR res_acc_csr is
1698 SELECT cas.access_level
1699 FROM okc_k_accesses cas
1700 WHERE cas.chr_id = p_chr_id
1701 AND cas.resource_id = g_user_resource_id;
1702
1703 -- This cursor selects all the resource groups and the access level
1704 -- for the contract.
1705
1706 CURSOR grp_acc_csr is
1707 SELECT cas.group_id,
1708 cas.access_level
1709 FROM okc_k_accesses cas
1710 WHERE cas.chr_id = p_chr_id
1711 AND cas.group_id is not null
1712 ORDER BY 2 DESC;
1713
1714 -- This cursor selects all the resource groups that the resource
1715 -- belongs to. Fetched only once per session. The retrieved rows are
1716 -- stored in pl/sql global table and this table is used for
1717 -- subsequent contracts in the same session.
1718
1719 CURSOR res_grp_csr is
1720 SELECT rgm.group_id
1721 FROM jtf_rs_group_members rgm,
1722 jtf_rs_role_relations rrr,
1723 jtf_rs_roles_b rr,
1724 jtf_rs_groups_b rgb
1725 WHERE rgm.resource_id = g_user_resource_id
1726 AND rgm.group_id = rgb.group_id
1727 AND l_date between nvl(rgb.start_date_active, l_date)
1728 and nvl(rgb.end_date_active, l_date)
1729 AND rgm.group_id = rrr.role_resource_id
1730 AND nvl(rgm.delete_flag,'N') = 'N'
1731 AND rrr.role_resource_type = 'RS_GROUP'
1732 AND nvl(rrr.delete_flag,'N') = 'N'
1733 AND l_date between rrr.start_date_active
1734 and nvl(rrr.end_date_active, l_date)
1735 AND rrr.role_id = rr.role_id
1736 AND rr.role_type_code = 'CONTRACTS'
1737 UNION
1738 SELECT rgd.parent_group_id
1739 FROM jtf_rs_group_members rgm,
1740 jtf_rs_groups_denorm rgd,
1741 jtf_rs_role_relations rrr,
1742 jtf_rs_roles_b rr,
1743 jtf_rs_groups_b rgb
1744 WHERE rgm.resource_id = g_user_resource_id
1745 AND nvl(rgm.delete_flag,'N') = 'N'
1746 AND rgd.group_id = rgm.group_id
1747 AND rgd.parent_group_id = rgb.group_id
1748 AND l_date between nvl(rgb.start_date_active, l_date)
1749 and nvl(rgb.end_date_active, l_date)
1750 AND rgd.parent_group_id = rrr.role_resource_id
1751 AND rrr.role_resource_type = 'RS_GROUP'
1752 AND nvl(rrr.delete_flag,'N') = 'N'
1753 AND l_date between rrr.start_date_active
1754 and nvl(rrr.end_date_active, l_date)
1755 AND rrr.role_id = rr.role_id
1756 AND rr.role_type_code = 'CONTRACTS';
1757
1758 -- Cursor to check for the existance of a contract (it could heve been deleted)
1759 -- used only for contracts online.
1760
1761 CURSOR c_check_contract(p_contract_id IN NUMBER) IS
1762 SELECT 'X'
1763 FROM okc_k_headers_b
1764 WHERE ID = p_contract_id;
1765
1766 --MMadhavi commenting for MOAC
1767 /*
1768 -- This cursor retrieves the authoring org id for that particular contract.
1769 CURSOR auth_org_csr IS
1770 SELECT authoring_org_id
1771 FROM okc_k_headers_b
1772 WHERE id = p_chr_id;
1773 */
1774
1775 BEGIN
1776
1777 -- Global variable g_user_id introduced to resolve the problem of connection pooling.
1778 -- This variable is not guaranteed to be same for the same user across multiple
1779 -- web requests. So everytime a global needs to be checked, make sure it was built
1780 -- by the same user.
1781
1782 Set_Connection_Context;
1783
1784 -- If no contract identifier is passed, then do not allow access
1785
1786 If p_chr_id Is Null Then
1787 Raise Exception_No_Access;
1788 End If;
1789
1790
1791 --MMadhavi commenting for MOAC
1792 /*
1793 If l_multiorg = 'Y' Then
1794 Open auth_org_csr;
1795 Fetch auth_org_csr Into l_authoring_org_id;
1796 Close auth_org_csr;
1797
1798 If l_org_id <> l_authoring_org_id Then
1799 Raise Exception_No_Access;
1800 End If;
1801 End If;
1802 */
1803 -- If the sub class is not passed in, then derive it using the
1804 -- contract identifier
1805
1806 l_scs_code := p_scs_code;
1807 l_orig_sys_source_code := p_orig_source_code;
1808
1809 If l_scs_code Is Null Then
1810
1811 -- Get the subclass/category from the contracts table
1812 Open chr_csr;
1813
1814 Fetch chr_csr Into l_scs_code,l_orig_sys_source_code;
1815
1816 l_row_notfound := chr_csr%NotFound;
1817
1818 Close chr_csr;
1819
1820 If l_row_notfound Then
1821 Raise Exception_No_Access;
1822 End If;
1823 End If;
1824 -- fnd_log.string(1, 'okc', 'l_scs_code : ' || l_scs_code);
1825
1826 -- Determine if the access for the category and responsibility has
1827 -- been determined earlier and cached in the global variables. If not,
1828 -- then determine it using the resp_csr g_resp_access is initialized
1829 -- to g_miss_char. If this could not be determined the first time
1830 -- around, the variables are set to null and not examined during the
1831 -- next round
1832
1833 /* If (l_scs_code <> g_scs_code) OR
1834 (g_resp_access = OKC_API.G_MISS_CHAR) Then */
1835
1836 If (l_scs_code <> g_scs_code) OR (g_reset_resp_flag) Then
1837
1838 Open resp_csr;
1839
1840 Fetch resp_csr Into l_resp_access;
1841
1842 l_row_notfound := resp_csr%NotFound;
1843
1844 Close resp_csr;
1845
1846 If l_row_notfound Then
1847 l_resp_access := Null;
1848 End If;
1849
1850 -- fnd_log.string(1, 'okc', 'l_resp_access : ' || l_resp_access);
1851 -- Save the current access level into global variables. If no access
1852 -- was determined, the local variables hold null and so do the global
1853 -- variables
1854
1855 g_scs_code := l_scs_code;
1856 g_resp_access := l_resp_access;
1857
1858 If g_reset_resp_flag Then
1859 g_reset_resp_flag := False;
1860 End If;
1861
1862 End If;
1863
1864 -- Check the access level at the category and responsibility level first
1865
1866 If g_resp_access = l_modify_access Then
1867 Raise Exception_Modify_Access;
1868 End If;
1869
1870 -- If could not find 'Update' access from the user's responsibility,
1871 -- continue to check if granted any access at the user resource level.
1872 -- If the user resource id is not determined earlier, then retrieve it
1873 -- and cache it as it will not change during the current session
1874
1875 If (g_user_resource_id = OKC_API.G_MISS_NUM) Or
1876 (g_reset_access_flag) Then
1877
1878 Open res_csr;
1879
1880 Fetch res_csr Into g_user_resource_id;
1881
1882 l_row_notfound := res_csr%NotFound;
1883
1884 Close res_csr;
1885
1886 g_groups_processed := False;
1887
1888 If l_row_notfound Then
1889 g_user_resource_id := Null;
1890 End If;
1891 -- fnd_log.string(1, 'okc', 'g_user_resource_id : ' || g_user_resource_id);
1892 End If;
1893
1894 -- Determine the access level for the resource id on the contract
1895
1896 If g_user_resource_id Is Not Null Then
1897
1898 Open res_acc_csr;
1899
1900 Fetch res_acc_csr Into l_resource_access;
1901
1902 Close res_acc_csr;
1903
1904 If l_resource_access = l_modify_access Then
1905 Raise Exception_Modify_Access;
1906 End If;
1907
1908 -- fnd_log.string(1, 'okc', 'l_resource_access : ' || l_resource_access);
1909 -- Since the resource does not have Update access, we need to get its
1910 -- parent group and its grand parent groups (recursively). Cache it in
1911 -- the global pl/sql table since this hierarchy is not going to change
1912 -- for a resource. So do it only for the first time. Do this by
1913 -- examining the global variable g_groups_processed. This indicates
1914 -- that the array of groups has been retrieved for the session
1915
1916 If g_groups_processed Then
1917 null;
1918 Else
1919 Open res_grp_csr;
1920 Fetch res_grp_csr BULK COLLECT INTO g_sec_groups;
1921 Close res_grp_csr;
1922 g_groups_processed := True;
1923 End If;
1924
1925 -- Finally check for any access granted at the group level.
1926 -- Do it only if the resource belongs to at least one group
1927 -- fnd_log.string(1, 'okc', 'g_sec_groups.count : ' || to_char(g_sec_groups.count));
1928
1929 l_group_has_read_access := False;
1930 If g_sec_groups.COUNT > 0 Then
1931 Open grp_acc_csr;
1932 Loop
1933 -- Get all the groups assigned to the contract
1934 Fetch grp_acc_csr Into l_group_id, l_group_access;
1935 Exit When grp_acc_csr%NOTFOUND;
1936 For i in 1 .. g_sec_groups.LAST
1937 Loop
1938 If g_sec_groups(i) = l_group_id Then
1939 -- If the groups match and access level is 'U', exit immediately
1940 If l_group_access = l_modify_access Then
1941 Raise Exception_Modify_Access;
1942 End If;
1943 If l_group_access = l_read_access Then
1944 l_group_has_read_access := True;
1945 End If;
1946 End If;
1947 End Loop;
1948 End Loop;
1949 Close grp_acc_csr;
1950 End If;
1951
1952 End If;
1953
1954 -- fnd_log.string(1, 'okc', 'l_resource_access : ' || l_resource_access);
1955 -- fnd_log.string(1, 'okc', 'l_group_access : ' || l_group_Access);
1956 -- fnd_log.string(1, 'okc', 'g_resp_access : ' || g_resp_access);
1957 If (l_read_access in (g_resp_access, l_resource_access)) Or
1958 l_group_has_read_access Then
1959 Raise Exception_Read_Access;
1960 End If;
1961
1962 Raise Exception_No_Access;
1963
1964 EXCEPTION
1965
1966 When Exception_Modify_Access Then
1967 -- fnd_log.string(1, 'okc', 'Modify Access Allowed');
1968 If grp_acc_csr%ISOPEN Then
1969 Close grp_acc_csr;
1970 End If;
1971 If g_reset_access_flag Then
1972 g_reset_access_flag := False;
1973 End If;
1974
1975 l_ret_status := l_modify_access;
1976
1977 -- IF p_called_from = 'F' and l_orig_sys_source_code = 'KSSA_HDR' THEN -- KOL contract called in Forms
1978 -- l_ret_status := l_read_access;
1979 -- ELSE
1980 /* Check for the contract created from forms */
1981 IF p_called_from = 'W' and l_orig_sys_source_code <> 'KSSA_HDR' THEN -- Forms contract called in KOL
1982 l_ret_status := l_read_access;
1983 IF p_update_attachment = 'true' THEN
1984 /* The contract created from forms,but can the attachment be updated in contracts online */
1985 -- IF OKC_ASSENT_PUB.header_operation_allowed(p_chr_id,'UPDATE') = OKC_API.G_TRUE THEN
1986 l_ret_status := l_modify_access;
1987 -- END IF; -- if update allowed in okc_assent_pub
1988 END IF; -- if update attachment = true
1989
1990 ELSIF p_called_from = 'W' and l_orig_sys_source_code = 'KSSA_HDR' THEN -- Created in KOL and accessed in KOL
1991 -- IF OKC_ASSENT_PUB.header_operation_allowed(p_chr_id,p_operation_allowed) <> OKC_API.G_TRUE THEN
1992 -- l_ret_status := l_read_access;
1993 -- END IF; -- if update/delete allowed in okc_assent_pub
1994 NULL;
1995
1996 END IF; -- if the p_mode = 'W' and the source code <> 'KSSA_HDR'
1997
1998 --If called from KOL and the contract category is Partner
1999 --Give readonly access
2000 IF p_called_from = 'W' AND l_scs_code = l_partner_cat THEN
2001 l_ret_status := l_read_access;
2002 END IF;
2003
2004 -- END IF;
2005
2006 Return(l_ret_status);
2007
2008 When Exception_Read_Access Then
2009 -- fnd_log.string(1, 'okc', 'Read Access Allowed');
2010 If g_reset_access_flag Then
2011 g_reset_access_flag := False;
2012 End If;
2013 Return(l_read_access);
2014
2015 When Exception_No_Access Then
2016 -- fnd_log.string(1, 'okc', 'No Access Allowed');
2017 l_ret_status := l_no_access;
2018
2019 If g_reset_access_flag Then
2020 g_reset_access_flag := False;
2021 End If;
2022
2023 -- The No Access could be because of the actual access itself
2024 -- or in the case where a contract could not be found. If the access
2025 -- level for a contract is checked from the web, then the return result
2026 -- should be more accurate. Hence 'N' will be returned for a no access
2027 -- and a NULL will be returned, if the contract itself was not found.
2028 -- This is applicable only for contracts online.
2029
2030 IF p_called_from = 'W' THEN
2031 OPEN c_check_contract(p_chr_id);
2032 FETCH c_check_contract INTO l_k_check;
2033 IF c_check_contract%NOTFOUND THEN
2034 l_ret_status := NULL;
2035 END IF;
2036 CLOSE c_check_contract;
2037 END IF;
2038
2039 Return(l_ret_status);
2040 End get_k_access_level;
2041
2042 -----------------------------------------------------------------------------
2043 -- Get_All_K_Access_Level - Function to call different security
2044 -- function depending on the application ID
2045 -----------------------------------------------------------------------------
2046 Function Get_All_K_Access_Level(p_chr_id IN NUMBER,
2047 p_application_id IN NUMBER ,
2048 p_scs_code IN VARCHAR2 )
2049 Return Varchar2 IS
2050 l_access_level Varchar2(30);
2051 l_application_id okc_k_headers_b.application_id%TYPE := p_application_id;
2052 cursor c1 is
2053 select application_id
2054 from okc_k_headers_b
2055 where id = p_chr_id;
2056 BEGIN
2057 -- For OKE Contracts (Application ID = 777), use OKE's security function
2058 If p_application_id Is Null Then
2059 Open c1;
2060 Fetch c1 Into l_application_id;
2061 Close c1;
2062 -- If c1%NotFound we can assume, it is non-OKE Contract
2063 End If;
2064 If l_application_id = 777 Then
2065 l_access_level := Oke_K_Security_Pkg.Get_K_Access(p_chr_id);
2066 -- Transform OKE's return value to OKC's return value
2067 If l_access_level = 'EDIT' Then
2068 l_access_level := 'U';
2069 Elsif l_access_level = 'VIEW' Then
2070 l_access_level := 'R';
2071 Else
2072 l_access_level := 'N';
2073 End If;
2074 Else
2075 l_access_level := Get_K_Access_Level(p_chr_id, p_scs_code);
2076 End If;
2077 Return(l_access_level);
2078 END Get_All_K_Access_Level;
2079
2080 --------------------------------------------------------------------------
2081 -- Function returns TRUE if user has modify access on a contract category
2082 --------------------------------------------------------------------------
2083 FUNCTION Create_K_Access(p_scs_code varchar2) RETURN BOOLEAN IS
2084
2085 Cursor access_level_csr is
2086 Select access_level
2087 from okc_subclass_resps_v
2088 where scs_code=p_scs_code
2089 and resp_id=fnd_global.resp_id
2090 and sysdate between start_date and nvl(end_date,sysdate);
2091
2092 Cursor subclass_csr is
2093 Select meaning
2094 from okc_subclasses_v
2095 where code=p_scs_code;
2096
2097 l_scs_meaning VARCHAR2(30);
2098 l_create_access_level VARCHAR2(1);
2099
2100 BEGIN
2101 Open access_level_csr;
2102 Fetch access_level_csr into l_create_access_level;
2103 Close access_level_csr;
2104
2105 If l_create_access_level = 'U' then
2106 Return(TRUE);
2107 Else
2108 Open subclass_csr;
2109 Fetch subclass_csr into l_scs_meaning;
2110 Close subclass_csr;
2111 OKC_API.SET_MESSAGE(p_app_name =>'OKC',
2112 p_msg_name =>'OKC_CREATE_NA',
2113 p_token1 =>'CATEGORY',
2114 p_token1_value =>l_scs_meaning);
2115 Return(FALSE);
2116 End If;
2117 END Create_K_Access;
2118
2119 -----------------------------------------------------------------------------
2120 --copies clob text to other recs with same source_lang as lang
2121 ------------------------------------------------------------------------------
2122 FUNCTION Copy_CLOB(id number,release varchar2,lang varchar2)
2123 RETURN VARCHAR2 IS
2124 l_stat VARCHAR2(1):='S';
2125 begin
2126 update okc_std_art_versions_tl set text=(select text from okc_std_art_versions_tl
2127 where sae_id=id and sav_release=release and language=lang)
2128 where sae_id=id and sav_release=release and source_lang=lang and language<>lang;
2129 return l_stat;
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 l_stat:='E';
2133 return l_stat;
2134 END Copy_CLOB;
2135
2136
2137 -----------------------------------------------------------------------------
2138 -- If p_text passed, updates the text with p_text.
2139 -- Otherwise copies clob text to other recs with same source_lang as lang
2140 -- in OKC_K_ARTICLES_TL table
2141 ------------------------------------------------------------------------------
2142 -- new 11510 version of FUNCTION Copy_Articles_Text
2143 FUNCTION Copy_Articles_Text(p_id NUMBER,lang VARCHAR2,p_text VARCHAR2 ) RETURN VARCHAR2
2144 IS
2145 l_return_status VARCHAR2(1) := 'S';
2146 length NUMBER;
2147 BEGIN
2148 IF (p_text IS NOT NULL) THEN
2149 UPDATE okc_article_versions
2150 SET article_text = p_text,
2151 object_version_number = object_version_number+1,
2152 last_updated_by = FND_GLOBAL.USER_ID,
2153 last_update_login = FND_GLOBAL.LOGIN_ID,
2154 last_update_date = Sysdate
2155 WHERE article_version_id=(SELECT article_version_id
2156 FROM okc_k_articles_b WHERE id = p_id);
2157 END IF;
2158 RETURN l_return_status;
2159 EXCEPTION
2160 WHEN OTHERS THEN
2161 l_return_status := 'E';
2162 RETURN l_return_status;
2163 END Copy_Articles_Text;
2164 /* 11510
2165 FUNCTION Copy_Articles_Text(p_id NUMBER,lang varchar2,p_text VARCHAR2 ) RETURN VARCHAR2 Is
2166 l_return_status VARCHAR2(1) := 'S';
2167 length NUMBER;
2168 Begin
2169 If (p_text is null) Then
2170 select dbms_lob.getlength(text) into length from okc_k_articles_tl
2171 where id = p_id and language = lang;
2172 If length > 0 Then
2173 update okc_k_articles_tl
2174 set text=(select text from okc_k_articles_tl
2175 where id=p_id and language=lang)
2176 where id = p_id and source_lang = lang and language <> lang;
2177 Else
2178 update okc_k_articles_tl
2179 set text=NULL
2180 where id = p_id and language = lang;
2181 End If;
2182 Else
2183 update okc_k_articles_tl
2184 set text= p_text
2185 where id = p_id and language = lang;
2186 End If;
2187 return l_return_status;
2188 EXCEPTION
2189 WHEN OTHERS THEN
2190 l_return_status := 'E';
2191 return l_return_status;
2192 End Copy_Articles_Text;
2193 */
2194
2195 -- new 11510 version of FUNCTION Copy_Articles_Varied_Text
2196 FUNCTION Copy_Articles_Varied_Text(
2197 p_article_id NUMBER,
2198 p_sae_id NUMBER,
2199 lang VARCHAR2
2200 ) RETURN VARCHAR2 Is
2201 l_return_status VARCHAR2(1) := 'S';
2202 BEGIN
2203 IF (l_debug = 'Y') THEN
2204 okc_debug.Set_Indentation('OKC_UTIL');
2205 okc_debug.log('1000: Entered Copy_Articles_Varied_Text', 2);
2206 okc_debug.log('1001: p_article_id: '||p_article_id, 2);
2207 okc_debug.log('1002: p_sae_id: '||p_sae_id, 2);
2208 END IF;
2209 UPDATE okc_article_versions
2210 SET article_text
2211 = (SELECT article_text FROM okc_article_versions
2212 WHERE article_id=p_sae_id
2213 AND sysdate BETWEEN start_date AND Nvl(end_date,Sysdate+1) ),
2214 object_version_number = object_version_number+1,
2215 last_updated_by = FND_GLOBAL.USER_ID,
2216 last_update_login = FND_GLOBAL.LOGIN_ID,
2217 last_update_date = Sysdate
2218 WHERE article_version_id
2219 =(SELECT article_version_id
2220 FROM okc_k_articles_b WHERE id = p_article_id);
2221 IF (l_debug = 'Y') THEN
2222 okc_debug.log('1010: Leaving Copy_Articles_Varied_Text', 2);
2223 okc_debug.Reset_Indentation;
2224 END IF;
2225 return l_return_status;
2226 EXCEPTION
2227 When OTHERS Then
2228 IF (l_debug = 'Y') THEN
2229 okc_debug.log('1020: Leaving Copy_Articles_Varied_Text because of an exception: '||sqlerrm, 2);
2230 okc_debug.Reset_Indentation;
2231 END IF;
2232 OKC_API.SET_MESSAGE(
2233 p_app_name => G_APP_NAME,
2234 p_msg_name => G_UNEXPECTED_ERROR,
2235 p_token1 => G_SQLCODE_TOKEN,
2236 p_token1_value => sqlcode,
2237 p_token2 => G_SQLERRM_TOKEN,
2238 p_token2_value => sqlerrm);
2239 return 'E';
2240 END;
2241
2242 /* 11510
2243 FUNCTION Copy_Articles_Varied_Text(p_article_id NUMBER,
2244 p_sae_id NUMBER,
2245 lang VARCHAR2)
2246 RETURN VARCHAR2 Is
2247
2248 l_release VARCHAR2(50);
2249 l_return_status VARCHAR2(1) := 'S';
2250
2251 Cursor l_savv_scr Is
2252 SELECT sav_release
2253 FROM okc_std_art_versions_b
2254 WHERE sae_id = p_sae_id
2255 AND date_active = (SELECT max(date_active)
2256 FROM okc_std_art_versions_b
2257 WHERE sae_id = p_sae_id
2258 AND date_active <= sysdate);
2259 BEGIN
2260
2261 Open l_savv_scr;
2262 Fetch l_savv_scr Into l_release;
2263 Close l_savv_scr;
2264
2265 update okc_k_articles_tl
2266 set text = (select text
2267 from okc_std_art_versions_tl
2268 where sae_id = p_sae_id
2269 and sav_release = l_release
2270 and language = lang)
2271 where id = p_article_id;
2272
2273 return l_return_status;
2274 EXCEPTION
2275 When NO_DATA_FOUND then
2276 If (l_savv_scr%ISOPEN) Then
2277 close l_savv_scr;
2278 End If;
2279 OKC_API.SET_MESSAGE(
2280 p_app_name => G_APP_NAME,
2281 p_msg_name => G_UNEXPECTED_ERROR,
2282 p_token1 => G_SQLCODE_TOKEN,
2283 p_token1_value => sqlcode,
2284 p_token2 => G_SQLERRM_TOKEN,
2285 p_token2_value => sqlerrm);
2286 return 'E';
2287 When OTHERS Then
2288 If (l_savv_scr%ISOPEN) Then
2289 close l_savv_scr;
2290 End If;
2291 OKC_API.SET_MESSAGE(
2292 p_app_name => G_APP_NAME,
2293 p_msg_name => G_UNEXPECTED_ERROR,
2294 p_token1 => G_SQLCODE_TOKEN,
2295 p_token1_value => sqlcode,
2296 p_token2 => G_SQLERRM_TOKEN,
2297 p_token2_value => sqlerrm);
2298 return 'E';
2299 END;
2300 */
2301 -----------------------------------------------------------------------------
2302 --Function to retrieve the Organization Title for forms; called from OKCSTAND.pll
2303 ------------------------------------------------------------------------------
2304 FUNCTION Get_Org_Window_Title RETURN VARCHAR2
2305 IS
2306 l_multi_org VARCHAR2(1);
2307 l_multi_cur VARCHAR2(1);
2308 l_wnd_context VARCHAR2(80);
2309 l_id VARCHAR2(15);
2310
2311 Cursor C1 IS
2312 select g.short_name||decode(g.mrc_sob_type_code, 'N', NULL,
2313 decode(l_multi_cur, 'N', NULL,
2314 ': ' || g.currency_code))
2315 from okx_set_of_books_v g
2316 where g.set_of_books_id=NVL(fnd_profile.value('GL_SET_OF_BKS_ID'),0);
2317 Cursor C2 IS
2318 select g.short_name||decode(g.mrc_sob_type_code, 'N', NULL,
2319 decode(l_multi_cur, 'N', NULL,
2320 ': ' || g.currency_code))
2321 from okx_set_of_books_v g, okx_organization_defs_v o
2322 where o.id1=NVL(fnd_profile.value('ORG_ID'),0)
2323 and g.set_of_books_id = to_number(o.set_of_books_id)
2324 and o.organization_type= 'OPERATING_UNIT'
2325 and o.information_type= 'Operating Unit Information' ;
2326 BEGIN
2327 /*
2328 ***
2329 *** Get multi-org and MRC information on the current
2330 *** product installation.
2331 ***
2332 */
2333 SELECT nvl(multi_org_flag, 'N')
2334 , nvl(multi_currency_flag, 'N')
2335 INTO l_multi_org
2336 , l_multi_cur
2337 FROM fnd_product_groups;
2338 /*
2339 ***
2340 *** Case #1 : Non-Multi-Org or Multi-SOB
2341 ***
2342 *** A. MRC not installed, OR
2343 *** MRC installed, Non-Primary/Reporting Books
2344 *** Form Name (SOB Short Name) - Context Info
2345 *** e.g. Maintain Forecast(US OPS) - Forecast Context Info
2346 ***
2347 *** B. MRC installed, Primary Books
2348 *** Form Name (SOB Short Name: Primary Currency) - Context Info
2349 *** e.g. Maintain Forecast(US OPS: USD) - Forecast Context Info
2350 ***
2351 *** C. MRC installed, Reporting Books
2352 *** Form Name (SOB Short Name: Reporting Currency) - Context Info
2353 *** e.g. Maintain Forecast(US OPS: EUR) - Forecast Context Info
2354 ***
2355 */
2356 IF (l_multi_org = 'N') THEN
2357 Open C1;
2358 Fetch C1 into l_wnd_context;
2359 Close C1;
2360 /*
2361 ***
2362 *** Case #2 : Multi-Org
2363 ***
2364 *** A. MRC not installed, OR
2365 *** MRC installed, Non-Primary/Reporting Books
2366 *** Form Name (OU Name) - Context Info
2367 *** e.g. Maintain Forecast(US West) - Forecast Context Info
2368 ***
2369 *** B. MRC installed, Primary Books
2370 *** Form Name (OU Name: Primary Currency) - Context Info
2371 *** e.g. Maintain Forecast(US West: USD) - Forecast Context Info
2372 ***
2373 *** C. MRC installed, Reporting Books
2374 *** Form Name (OU Name: Reporting Currency) - Context Info
2375 *** e.g. Maintain Forecast(US West: EUR) - Forecast Context Info
2376 ***
2377 */
2378 ELSE
2379 Open C2;
2380 Fetch C2 into l_wnd_context;
2381 Close C2;
2382 END IF;
2383 return l_wnd_context;
2384 exception
2385 when others then
2386 return l_wnd_context;
2387 END Get_Org_Window_Title;
2388
2389
2390 PROCEDURE forms_savepoint(p_savepoint IN VARCHAR2) IS
2391 BEGIN
2392 dbms_transaction.savepoint(p_savepoint);
2393 END;
2394
2395 PROCEDURE forms_rollback(p_savepoint IN VARCHAR2) IS
2396 BEGIN
2397 dbms_transaction.rollback_savepoint(p_savepoint);
2398 END;
2399
2400 --------------------------------------------------------------------------------
2401 -- PROCEDURE init_msg_list
2402 --------------------------------------------------------------------------------
2403 PROCEDURE init_msg_list (
2404 p_init_msg_list IN VARCHAR2
2405 ) IS
2406 BEGIN
2407 OKC_API.init_msg_list(p_init_msg_list);
2408 END init_msg_list;
2409
2410 --------------------------------------------------------------------------------
2411 -- PROCEDURE set_message
2412 --------------------------------------------------------------------------------
2413 PROCEDURE set_message (
2414 p_app_name IN VARCHAR2 ,
2415 p_msg_name IN VARCHAR2,
2416 p_token1 IN VARCHAR2 ,
2417 p_token1_value IN VARCHAR2 ,
2418 p_token2 IN VARCHAR2 ,
2419 p_token2_value IN VARCHAR2 ,
2420 p_token3 IN VARCHAR2 ,
2421 p_token3_value IN VARCHAR2 ,
2422 p_token4 IN VARCHAR2 ,
2423 p_token4_value IN VARCHAR2 ,
2424 p_token5 IN VARCHAR2 ,
2425 p_token5_value IN VARCHAR2 ,
2426 p_token6 IN VARCHAR2 ,
2427 p_token6_value IN VARCHAR2 ,
2428 p_token7 IN VARCHAR2 ,
2429 p_token7_value IN VARCHAR2 ,
2430 p_token8 IN VARCHAR2 ,
2431 p_token8_value IN VARCHAR2 ,
2432 p_token9 IN VARCHAR2 ,
2433 p_token9_value IN VARCHAR2 ,
2434 p_token10 IN VARCHAR2 ,
2435 p_token10_value IN VARCHAR2
2436 ) IS
2437 BEGIN
2438 FND_MESSAGE.SET_NAME( P_APP_NAME, P_MSG_NAME);
2439 IF (p_token1 IS NOT NULL) AND (p_token1_value IS NOT NULL) THEN
2440 FND_MESSAGE.SET_TOKEN( TOKEN => p_token1,
2441 VALUE => p_token1_value);
2442 END IF;
2443 IF (p_token2 IS NOT NULL) AND (p_token2_value IS NOT NULL) THEN
2444 FND_MESSAGE.SET_TOKEN( TOKEN => p_token2,
2445 VALUE => p_token2_value);
2446 END IF;
2447 IF (p_token3 IS NOT NULL) AND (p_token3_value IS NOT NULL) THEN
2448 FND_MESSAGE.SET_TOKEN( TOKEN => p_token3,
2449 VALUE => p_token3_value);
2450 END IF;
2451 IF (p_token4 IS NOT NULL) AND (p_token4_value IS NOT NULL) THEN
2452 FND_MESSAGE.SET_TOKEN( TOKEN => p_token4,
2453 VALUE => p_token4_value);
2454 END IF;
2455 IF (p_token5 IS NOT NULL) AND (p_token5_value IS NOT NULL) THEN
2456 FND_MESSAGE.SET_TOKEN( TOKEN => p_token5,
2457 VALUE => p_token5_value);
2458 END IF;
2459 IF (p_token6 IS NOT NULL) AND (p_token6_value IS NOT NULL) THEN
2460 FND_MESSAGE.SET_TOKEN( TOKEN => p_token6,
2461 VALUE => p_token6_value);
2462 END IF;
2463 IF (p_token7 IS NOT NULL) AND (p_token7_value IS NOT NULL) THEN
2464 FND_MESSAGE.SET_TOKEN( TOKEN => p_token7,
2465 VALUE => p_token7_value);
2466 END IF;
2467 IF (p_token8 IS NOT NULL) AND (p_token8_value IS NOT NULL) THEN
2468 FND_MESSAGE.SET_TOKEN( TOKEN => p_token8,
2469 VALUE => p_token8_value);
2470 END IF;
2471 IF (p_token9 IS NOT NULL) AND (p_token9_value IS NOT NULL) THEN
2472 FND_MESSAGE.SET_TOKEN( TOKEN => p_token9,
2473 VALUE => p_token9_value);
2474 END IF;
2475 IF (p_token10 IS NOT NULL) AND (p_token10_value IS NOT NULL) THEN
2476 FND_MESSAGE.SET_TOKEN( TOKEN => p_token10,
2477 VALUE => p_token10_value);
2478 END IF;
2479 FND_MSG_PUB.add;
2480 END set_message;
2481
2482 /*
2483 -------------------------------------------------------------------------------
2484 -- Procedure: get_trace_path
2485 -- Purpose: define the root directory for trace files
2486 --
2487 -- In Parameters:
2488 -- Out Parameters:
2489 --
2490 FUNCTION get_trace_path (p_path IN VARCHAR2)
2491 RETURN VARCHAR2
2492 IS
2493 BEGIN
2494 IF INSTR(p_path,',',1) = 0 THEN
2495 IF INSTR(p_path, ';', 1) = 0 THEN
2496 RETURN SUBSTR(p_path, 1, LENGTH(p_path));
2497 ELSE
2498 RETURN SUBSTR(p_path, 1, INSTR(p_path, ';', 1)-1);
2499 END IF;
2500 ELSE
2501 RETURN SUBSTR(p_path, 1 , INSTR(p_path, ',', 1)-1);
2502 END IF;
2503
2504 EXCEPTION
2505 WHEN OTHERS THEN
2506 OKC_API.set_message(p_app_name => g_app_name,
2507 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2508 p_token1 => 'ACTION',
2509 p_token1_value => 'GET_TRACE_PATH');
2510 RAISE;
2511 END get_trace_path;
2512
2513 -------------------------------------------------------------------------------
2514 -- Procedure: close_trace_file
2515 -- Purpose: close the trace file for the current session
2516 --
2517 -- In Parameters:
2518 -- Out Parameters:
2519 --
2520 PROCEDURE close_trace_file
2521 IS
2522 BEGIN
2523 UTL_FILE.FCLOSE(l_trace_file);
2524
2525 EXCEPTION
2526 WHEN OTHERS THEN
2527 OKC_API.set_message(p_app_name => g_app_name,
2528 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2529 p_token1 => 'ACTION',
2530 p_token1_value => 'CLOSE_TRACE_FILE');
2531 RAISE;
2532 END close_trace_file;
2533
2534 ------------------------------------------------------------------------------
2535 -- Procedure: Reset_trace_context
2536 -- Purpose: close the trace file for the current session
2537 --
2538 -- In Parameters:
2539 -- Out Parameters:
2540 --
2541 PROCEDURE Reset_trace_context
2542 IS
2543 BEGIN
2544 IF SUBSTR(LTRIM(RTRIM(FND_PROFILE.VALUE('AFLOG_ENABLED'))),1,1) = 'Y'
2545 THEN -- Disable the log_enabled mode i.e. stop debugging mode
2546 FND_PROFILE.PUT('AFLOG_ENABLED','N');
2547 END IF;
2548
2549 EXCEPTION
2550 WHEN OTHERS THEN
2551 OKC_API.set_message(p_app_name => g_app_name,
2552 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2553 p_token1 => 'ACTION',
2554 p_token1_value => 'RESET_TRACE_CONTEXT');
2555 RAISE;
2556 END reset_trace_context;
2557
2558 -------------------------------------------------------------------------------
2559 -- Procedure: open_trace_file
2560 -- Purpose: open a trace file for the current session
2561 --
2562 -- In Parameters:
2563 -- Out Parameters:
2564 --
2565 PROCEDURE open_trace_file(g_request_id IN NUMBER,
2566 x_return_status OUT NOCOPY VARCHAR2)
2567 IS
2568 l_parameter_value VARCHAR2(255);
2569 l_process_id NUMBER;
2570 l_session_id NUMBER;
2571 BEGIN
2572 IF g_request_id IN (0, -1) THEN
2573 --NO conc. prog. is running
2574 SELECT pr.spid
2575 ,se.sid
2576 ,se.program
2577 ,se.module
2578 INTO l_process_id
2579 ,l_session_id
2580 ,l_program
2581 ,l_module
2582 FROM v$session se
2583 ,v$process pr
2584 WHERE se.audsid = USERENV('SESSIONID')
2585 AND se.paddr = pr.addr;
2586
2587 SELECT pa.value
2588 INTO l_parameter_value
2589 FROM v$parameter pa
2590 WHERE pa.name = 'utl_file_dir';
2591
2592 l_trace_path := get_trace_path (l_parameter_value);
2593
2594 ---
2595 --- The file mode must be opened in w mode in 7.3, otherwise in a mode
2596 ---
2597 BEGIN
2598 l_trace_file := utl_file.fopen(l_trace_path, g_trc_trace_file_prefix
2599 || TO_CHAR(l_process_id)
2600 || '_'
2601 || TO_CHAR(l_session_id)
2602 || g_trc_trace_file_suffix, 'a');
2603 EXCEPTION
2604 WHEN utl_file.invalid_mode THEN
2605 l_trace_file := utl_file.fopen(l_trace_path, g_trc_trace_file_prefix
2606 || TO_CHAR(l_process_id)
2607 || '_'
2608 || TO_CHAR(l_session_id)
2609 || g_trc_trace_file_suffix, 'w');
2610 END;
2611 l_trace_file_name := g_trc_trace_file_prefix
2612 || TO_CHAR(l_process_id)
2613 || '_'
2614 || TO_CHAR(l_session_id)
2615 || g_trc_trace_file_suffix;
2616 l_complete_trace_file_name := l_trace_path||'/'||l_trace_file_name;
2617 ELSE
2618 --Select and Open the log file
2619 l_trace_file.id:=FND_FILE.log;
2620 FND_FILE.put_line(l_trace_file.id, ' ');
2621 --Select and Open the output file
2622 l_output_file.id:=FND_FILE.output;
2623 FND_FILE.put_line(l_output_file.id, ' ');
2624 --Get log and output file names
2625 FND_FILE.get_names(l_trace_file_name, l_output_file_name);
2626 END IF;
2627 x_return_status:=OKC_API.g_true;
2628 EXCEPTION
2629 WHEN OTHERS THEN
2630 OKC_API.set_message(p_app_name => g_app_name,
2631 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2632 p_token1 => 'ACTION',
2633 p_token1_value => 'OPEN_TRACE_FILE');
2634 -- RAISE; --BUG# 1850274
2635 x_return_status:=OKC_API.g_false;
2636 END open_trace_file;
2637 */
2638
2639 ------------------------------------------------------------------------------
2640 -- Procedure: set_trace_context
2641 -- Purpose: Open the log and output files for the
2642 -- concurrent program else set up the context
2643 --
2644 -- In Parameters:
2645 -- Out Parameters:
2646 --
2647 PROCEDURE set_trace_context(g_request_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
2648 IS
2649
2650 BEGIN
2651
2652 IF g_request_id NOT IN (0, -1) THEN -- Conc. prog. is running
2653 --Select and Open the log file
2654 l_trace_file.id:=FND_FILE.log;
2655 FND_FILE.put_line(l_trace_file.id, ' ');
2656 --Select and Open the output file
2657 l_output_file.id:=FND_FILE.output;
2658 FND_FILE.put_line(l_output_file.id, ' ');
2659 --Get log and output file names
2660 FND_FILE.get_names(l_trace_file_name, l_output_file_name);
2661 END IF;
2662 x_return_status:=OKC_API.g_true;
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 OKC_API.set_message(p_app_name => g_app_name,
2666 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2667 p_token1 => 'ACTION',
2668 p_token1_value => 'SET_TRACE_CONTEXT');
2669 --RAISE;
2670 x_return_status:=OKC_API.g_false; --Bug # 1850274
2671 END set_trace_context;
2672
2673 -------------------------------------------------------------------------------
2674 -- Procedure: print_trace
2675 -- Purpose: write a trace line in the trace file
2676 --
2677 -- In Parameters:
2678 -- Out Parameters:
2679 --
2680 PROCEDURE print_trace (p_indent IN NUMBER,
2681 p_trace_line IN VARCHAR2,
2682 p_level IN NUMBER ,
2683 p_module IN VARCHAR2 )
2684 IS
2685 l_indent NUMBER;
2686 l_mesg VARCHAR2(1900);
2687 l_level NUMBER;
2688 l_db_module VARCHAR2(200);
2689
2690 -- The print trace procedure serves as a wrapper to the OKC_DEBUG API.
2691
2692 BEGIN
2693
2694 l_indent :=p_indent;
2695 l_level := p_level;
2696 l_db_module := p_module;
2697
2698 l_mesg:=LPAD(p_trace_line, LENGTH(p_trace_line)+(4*p_indent), ' ');
2699
2700 IF l_trace_flag THEN -- If true, write into fnd_log_messages
2701 IF (l_debug = 'Y') THEN
2702 OKC_DEBUG.log(l_mesg,l_level,l_db_module);
2703 END IF;
2704 ELSE -- write into log file for the conc.program
2705
2706 IF l_log_flag AND l_trace_file.id IS NOT NULL THEN
2707 -- FND_FILE.PUT_LINE(l_trace_file.id, l_mesg);
2708 -- Bug 1993476
2709 FND_FILE.PUT_LINE(l_trace_file.id, replace(l_mesg,OKC_API.G_MISS_CHAR));
2710 END IF;
2711
2712 END IF;
2713 EXCEPTION
2714 WHEN OTHERS THEN
2715 OKC_API.set_message(p_app_name => g_app_name,
2716 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2717 p_token1 => 'ACTION',
2718 p_token1_value => 'PRINT_TRACE');
2719 RAISE;
2720 END print_trace;
2721
2722 -------------------------------------------------------------------------------
2723 -- Procedure: init_trace
2724 -- Purpose: setup the trace mode
2725 --
2726 -- In Parameters:
2727 -- Out Parameters:
2728 --
2729 PROCEDURE init_trace
2730 IS
2731 g_request_id NUMBER;
2732 l_user_id NUMBER;
2733 l_log_enable VARCHAR2(20);
2734 l_session_id NUMBER;
2735 l_init_profile_log_enabled varchar2(20);
2736 l_init_profile_module_level varchar2(20);
2737 l_init_profile_module_name varchar2(20);
2738 lx_return_status varchar2(50);
2739 l_file varchar2(100);
2740 --
2741 l_sql_string varchar2(100) := 'ALTER SESSION SET SQL_TRACE TRUE';
2742 p_id NUMBER :=0;
2743 osp_id NUMBER :=0;
2744 s_id NUMBER :=0;
2745
2746 BEGIN
2747
2748 IF NOT l_trace_flag AND NOT l_log_flag THEN
2749 --Bug : 1993476 Moved the following block up here so that it can print process ids etc when called from concurrent programs.
2750 BEGIN
2751 EXECUTE IMMEDIATE l_sql_string;
2752 -- Bug 1996039
2753 l_sql_string := 'alter session set events '''||' 10046 trace name context forever, level 4 ''' ;
2754 EXECUTE IMMEDIATE l_sql_string;
2755
2756 SELECT
2757 SPID, S.AUDSID
2758 INTO
2759 osp_id, s_id
2760 FROM
2761 V$PROCESS P,
2762 V$SESSION S
2763 WHERE
2764 S.AUDSID = USERENV('SESSIONID')
2765 AND P.Addr = S.Paddr
2766 AND rownum <= 1;
2767
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 NULL;
2771 END;
2772
2773 g_request_id:=FND_GLOBAL.conc_request_id;
2774 IF g_request_id NOT IN (0, -1) -- The conc. prog. is running
2775 THEN
2776 -- Sets up the log file for the conc.req
2777 set_trace_context(g_request_id, lx_return_status);
2778 IF lx_return_status = OKC_API.g_true THEN
2779 l_log_flag :=TRUE;
2780 l_output_flag :=TRUE;
2781 ELSE
2782 --We disregard the error and consider the trace mode is not
2783 --activated, but cannot stop the user even if he requires a
2784 --trace file
2785 NULL;
2786 END IF;
2787 ELSE -- Not a concurrent program
2788 -- Obtain the info to fill in the trace file
2789 SELECT se.program
2790 ,se.module -- header and footer
2791 INTO l_program
2792 ,l_module
2793 FROM v$session se
2794 ,v$process pr
2795 WHERE se.audsid = USERENV('SESSIONID')
2796 AND se.paddr = pr.addr;
2797
2798 l_trace_flag := TRUE;
2799
2800 -- Obtain the initial profile settings so that it can be reset later.
2801
2802 l_init_profile_log_enabled := FND_PROFILE.VALUE('AFLOG_ENABLED');
2803 l_init_profile_module_level:= FND_PROFILE.VALUE('AFLOG_LEVEL');
2804 l_init_profile_module_name := FND_PROFILE.VALUE('AFLOG_MODULE');
2805
2806 l_user_id := fnd_global.user_id; -- obtain the user ID
2807
2808 IF l_user_id = -1 -- Non-Apps Mode.
2809 THEN
2810 IF (l_debug = 'Y') THEN
2811 OKC_DEBUG.g_session_id := sys_context('USERENV','SESSIONID');
2812 END IF;
2813
2814 -- Set the profile values
2815
2816 FND_PROFILE.PUT('AFLOG_ENABLED','Y'); -- Enable the log
2817 FND_PROFILE.PUT('AFLOG_LEVEL',1); -- Set the debug level
2818 FND_PROFILE.PUT('AFLOG_MODULE','OKC'); -- Set the module name
2819 --
2820 --In the Non-apps mode the value of g_profile_log_level in the okc_debug API
2821 --is set to 0. However in the subsequent call to the Fnd_Log.test procedure
2822 --the value of G_CURRENT_RUNTIME_LEVEL is used.Hence we need to set its value
2823 --to 1 by executing the fnd_log_repository.init procedure call,as done below.
2824 --Also, when the okc_util.print_trace is called it DOESNOT go through the
2825 --looping in the set connection context procedure in the OKC_DEBUG package, so
2826 --g_profile_log_level is never set and it is defaulted to 0
2827 --
2828
2829 -- Initialize the current runtime level
2830
2831 FND_LOG_REPOSITORY.INIT(OKC_DEBUG.g_session_id,l_user_id);
2832 -- Bug 1996039 Initializing aso debug
2833 aso_debug_pub.debug_on;
2834 aso_debug_pub.initialize;
2835 l_file :=aso_DEBUG_PUB.Set_Debug_Mode('FILE');
2836 aso_Debug_pub.setdebuglevel(10);
2837
2838 ELSE -- Apps mode
2839
2840 --
2841 --In the apps mode,the profile values are explicitly set and the
2842 --fnd_log_repository.init procedure is invoked ,which sets the
2843 --G_CURRENT_RUNTIME_LEVEL (based on profile option values)
2844 --but since the set connection context is already
2845 --executed (to run apps_initialize) and has set up g_profile_log_level,
2846 --we need now to explicitly call fnd_log_repository.init to assign the
2847 --new value of G_CURRENT_RUNTIME_LEVEL, which is then used to initialize
2848 --g_profile_log_level value (p_level of okc_debug.log will be then greater
2849 --or equal to g_profile_log_level)
2850 --
2851 IF (l_debug = 'Y') THEN
2852 OKC_DEBUG.set_connection_context;
2853 END IF;
2854
2855 FND_PROFILE.PUT('AFLOG_ENABLED','Y');
2856 FND_PROFILE.PUT('AFLOG_LEVEL',1);
2857 FND_PROFILE.PUT('AFLOG_MODULE','OKC');
2858
2859 FND_LOG_REPOSITORY.INIT(OKC_DEBUG.g_session_id,l_user_id);
2860
2861 IF (l_debug = 'Y') THEN
2862 OKC_DEBUG.g_profile_log_level := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
2863 END IF;
2864 -- Bug 1996039 Initializing aso debug
2865 aso_debug_pub.debug_on;
2866 aso_debug_pub.initialize;
2867 l_file :=aso_DEBUG_PUB.Set_Debug_Mode('FILE');
2868 aso_Debug_pub.setdebuglevel(10);
2869
2870
2871 END IF;
2872
2873 -- Display the latest profile settings and other parameters
2874
2875
2876 -- Reset the profile values since the Initialization is already
2877 -- done (mandatory step in case our API is called by an external
2878 -- API which may have different settings from ours)
2879
2880 FND_PROFILE.PUT('AFLOG_ENABLED',l_init_profile_log_enabled);
2881 FND_PROFILE.PUT('AFLOG_LEVEL',l_init_profile_module_level);
2882 FND_PROFILE.PUT('AFLOG_MODULE',l_init_profile_module_name);
2883
2884 -- Set up the oracle trace enable to true and print out the DB trace file info
2885
2886 END IF;
2887 --IF (l_trace_flag OR l_log_flag) AND NOT l_before_trace_flag THEN
2888
2889 print_trace_header;
2890
2891 print_trace(0,'');
2892 print_trace(0,'');
2893 print_trace(0,'********** DATABASE TRACE INFORMATION***************');
2894 print_trace(0,'***** SESSION ID: '||s_id);
2895 print_trace(0,'***** ORACLE SERVER PROCESS ID '||osp_id);
2896 print_trace(0,'****************************************************');
2897 print_trace(0,'');
2898 print_trace(0,'');
2899
2900 --END IF;
2901 END IF;
2902
2903 EXCEPTION
2904 WHEN OTHERS THEN
2905 OKC_API.set_message(p_app_name => g_app_name,
2906 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2907 p_token1 => 'ACTION',
2908 p_token1_value => 'INIT_TRACE');
2909 OKC_API.set_message(G_APP_NAME
2910 ,G_UNEXPECTED_ERROR
2911 ,G_SQLCODE_TOKEN
2912 ,SQLCODE
2913 ,G_SQLERRM_TOKEN
2914 ,SQLERRM);
2915 --RAISE; --Bug # 1850274
2916 l_log_flag :=FALSE;
2917 l_output_flag :=FALSE;
2918 l_trace_flag :=FALSE;
2919 END init_trace;
2920
2921 ------------------------------------------------------------------------------
2922 --
2923 -- Procedure: stop_trace
2924 -- Purpose: Turn off the trace mode
2925 --
2926 -- In Parameters:
2927 -- Out Parameters:
2928 --
2929 PROCEDURE stop_trace
2930 IS
2931
2932 l_sql_string varchar2(100) := 'ALTER SESSION SET SQL_TRACE FALSE';
2933
2934 BEGIN
2935 IF l_trace_flag AND NOT l_before_trace_flag THEN
2936 -- reset_trace_context;
2937 -- IF NOT l_before_trace_flag THEN
2938 print_trace_footer;
2939 -- END IF;
2940 l_trace_flag := FALSE;
2941 ELSIF l_log_flag AND NOT l_before_trace_flag THEN
2942 print_trace_footer;
2943 l_log_flag := FALSE;
2944 l_output_flag := FALSE;
2945 l_trace_file.id := NULL;
2946 l_output_file.id := NULL;
2947 END IF;
2948 -- Bug 1996039 Stopping ASO debugg
2949 aso_debug_pub.debug_off;
2950
2951
2952
2953 BEGIN
2954 EXECUTE IMMEDIATE l_sql_string;
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 NULL;
2958 END;
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 OKC_API.set_message(p_app_name => g_app_name,
2962 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2963 p_token1 => 'ACTION',
2964 p_token1_value => 'STOP_TRACE');
2965 OKC_API.set_message(G_APP_NAME
2966 ,G_UNEXPECTED_ERROR
2967 ,G_SQLCODE_TOKEN
2968 ,SQLCODE
2969 ,G_SQLERRM_TOKEN
2970 ,SQLERRM);
2971 RAISE;
2972 END stop_trace;
2973
2974 ------------------------------------------------------------------------------
2975 -- Procedure: print_output
2976 -- Purpose: write a output line in the output file
2977 --
2978 -- In Parameters:
2979 -- Out Parameters:
2980 --
2981 PROCEDURE print_output(p_indent IN NUMBER,
2982 p_trace_line IN VARCHAR2)
2983 IS
2984 l_mesg VARCHAR2(1900);
2985 BEGIN
2986 IF l_output_flag AND l_output_file.id IS NOT NULL THEN
2987 l_mesg:=LPAD(p_trace_line, LENGTH(p_trace_line)+(4*p_indent), ' ');
2988 FND_FILE.PUT_LINE(l_output_file.id, l_mesg);
2989 END IF;
2990
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 OKC_API.set_message(p_app_name => g_app_name,
2994 p_msg_name => 'OKC_K2Q_TRACE_MODE',
2995 p_token1 => 'ACTION',
2996 p_token1_value => 'PRINT_TRACE');
2997 RAISE;
2998 END print_output;
2999
3000 -------------------------------------------------------------------------------
3001 -- Procedure: print_trace_header
3002 -- Purpose: print the standard header for trace files
3003 --
3004 -- In Parameters:
3005 -- Out Parameters:
3006 --
3007 PROCEDURE print_trace_header
3008 IS
3009 BEGIN
3010 print_trace(0, '----------------------------------------------------------');
3011 print_trace(0, 'Trace activated');
3012 print_trace(0, 'Datetime = ' || TO_CHAR(sysdate,'DD-MM HH24:MI:SS'));
3013 -- print_trace(0, 'Trace location = ' || l_trace_path);
3014 -- print_trace(0, 'Trace file name = ' || l_trace_file_name);
3015 print_trace(0, 'Program = ' || l_program);
3016 print_trace(0, 'MODULE = ' || l_module);
3017 print_trace(0, '----------------------------------------------------------');
3018
3019 EXCEPTION
3020 WHEN OTHERS THEN
3021 OKC_API.set_message(p_app_name => g_app_name,
3022 p_msg_name => 'OKC_K2Q_TRACE_MODE',
3023 p_token1 => 'ACTION',
3024 p_token1_value => 'PRINT_TRACE_HEADER');
3025 RAISE;
3026 END print_trace_header;
3027
3028 -------------------------------------------------------------------------------
3029 -- Procedure: print_trace_footer
3030 -- Purpose: print the standard footer for trace files
3031 --
3032 -- In Parameters:
3033 -- Out Parameters:
3034 --
3035 PROCEDURE print_trace_footer
3036 IS
3037 BEGIN
3038 print_trace(0, '----------------------------------------------------------');
3039 print_trace(0, 'Trace deactivated');
3040 print_trace(0, 'Datetime = ' || TO_CHAR(sysdate,'DD-MM HH24:MI:SS'));
3041 -- print_trace(0, 'Trace location = ' || l_trace_path);
3042 -- print_trace(0, 'Trace file name = ' || l_trace_file_name);
3043 print_trace(0, 'Program = ' || l_program);
3044 print_trace(0, 'MODULE = ' || l_module);
3045 print_trace(0, '----------------------------------------------------------');
3046
3047 EXCEPTION
3048 WHEN OTHERS THEN
3049 OKC_API.set_message(p_app_name => g_app_name,
3050 p_msg_name => 'OKC_K2Q_TRACE_MODE',
3051 p_token1 => 'ACTION',
3052 p_token1_value => 'PRINT_TRACE_FOOTER');
3053 RAISE;
3054 END print_trace_footer;
3055
3056 -------------------------------------------------------------------------------------------
3057 -- Function get_userenv_lang
3058 -- Purpose: See specs in OKCUTILS.pls
3059 -- Briefly: This caches the value of userenv('lang') so
3060 -- that subsequent calls do not result in a database hit
3061 -- Partial Fix for Bug 1365356.
3062 --
3063 --
3064 FUNCTION get_userenv_lang RETURN VARCHAR2 IS
3065
3066 BEGIN
3067
3068 -- Determine if this was determined before by examining the global
3069 -- variable g_userenv_lang. If this is NOT null, return the value,
3070 -- otherwise, determine the value, populate the global variable and
3071 -- return the value.
3072
3073 Set_Connection_Context;
3074
3075 IF (g_userenv_lang IS NULL) Or (g_reset_lang_flag) Then
3076 g_userenv_lang := USERENV('LANG');
3077 g_reset_lang_flag := False;
3078 END IF;
3079
3080 RETURN g_userenv_lang;
3081
3082 END get_userenv_lang;
3083
3084 -------------------------------------------------------------------------------------------
3085 -- Function get_prcnt
3086 -- Purpose: gets data from histogram
3087
3088 Function get_prcnt(
3089 p_owner varchar2,
3090 p_table varchar2,
3091 p_column varchar2,
3092 p_value varchar2) return number as
3093 l_startpoint number;
3094 l_endpoint number;
3095 l_percent number;
3096 begin
3097 begin
3098 select max(ENDPOINT_NUMBER) into l_percent
3099 from all_histograms
3100 where owner=p_owner
3101 and TABLE_NAME=p_table
3102 and COLUMN_NAME=p_column;
3103 exception
3104 when others then l_percent := 0;
3105 end;
3106 if (l_percent=0 or l_percent is NULL) then return 0;
3107 end if;
3108 begin
3109 select ENDPOINT_NUMBER into l_endpoint
3110 from all_histograms
3111 where owner=p_owner
3112 and TABLE_NAME=p_table
3113 and COLUMN_NAME=p_column
3114 and ENDPOINT_VALUE=p_value;
3115 exception
3116 when others then return 0;
3117 end;
3118 if (l_endpoint=0 or l_endpoint is NULL) then return 0;
3119 end if;
3120 begin
3121 select max(ENDPOINT_NUMBER) into l_startpoint
3122 from all_histograms
3123 where owner=p_owner
3124 and TABLE_NAME=p_table
3125 and COLUMN_NAME=p_column
3126 and ENDPOINT_NUMBER<l_endpoint;
3127 exception
3128 when others then NULL;
3129 end;
3130 if (l_startpoint is NULL) then l_startpoint := 0;
3131 end if;
3132 return 100*(l_endpoint-l_startpoint)/l_percent;
3133 end get_prcnt;
3134
3135 -------------------------------------------------------------------------------------------
3136 -- Function grp_dense
3137 -- Purpose: gets density from histogram for k groups
3138 -- returns 0 if small group, and 1 if not
3139
3140 Function grp_dense(p_grp_like varchar2) return number is
3141 d number;
3142 v number := fnd_profile.value('OKC_PERCENT_FOR_IDX');
3143 begin
3144 select sum(OKC_UTIL.get_prcnt('OKC','OKC_K_GRPINGS','CGP_PARENT_ID',g.id)) into d
3145 from okc_k_groups_tl g
3146 where g.name like p_grp_like and g.language=userenv('LANG');
3147 if (d is NULL or d<=v) then return 0;
3148 else return 1;
3149 end if;
3150 end;
3151
3152 ---------------------------------------------------------------------------
3153 function DECODE_LOOKUP (
3154 --******************************************************************************
3155 --* Returns the meaning for a lookup code of a specified type.
3156 --******************************************************************************
3157 --
3158 p_lookup_type varchar2,
3159 p_lookup_code varchar2) return varchar2 is
3160 --
3161 cursor csr_lookup is -- /striping/ only for p_lookup_type = 'OKC_RULE_DEF'
3162 select meaning
3163 from FND_LOOKUP_VALUES
3164 where language = userenv('LANG')
3165 and NVL(enabled_flag,'N') = 'Y'
3166 and lookup_type = p_lookup_type
3167 and lookup_code = p_lookup_code;
3168
3169 -- /striping/ -- only for p_lookup_type = 'OKC_RULE_DEF'
3170 cursor csr_lookup1 is
3171 select meaning
3172 from okc_rule_defs_v
3173 where rule_code = p_lookup_code;
3174
3175 --
3176 v_meaning varchar2(80) := null;
3177 --
3178 begin
3179 --
3180 -- Only open the cursor if the parameters are going to retrieve anything
3181 --
3182 if p_lookup_type is not null and p_lookup_code is not null then
3183 --
3184 -- /striping/
3185 if p_lookup_type = 'OKC_RULE_DEF' then
3186 open csr_lookup1;
3187 fetch csr_lookup1 into v_meaning;
3188 close csr_lookup1;
3189 else
3190 open csr_lookup;
3191 fetch csr_lookup into v_meaning;
3192 close csr_lookup;
3193 end if;
3194 --
3195 end if;
3196 --
3197 return v_meaning;
3198 --
3199 end decode_lookup;
3200
3201 ---------------------------------------------------------------------------
3202 PROCEDURE Set_Search_String(p_srch_str IN VARCHAR2,
3203 x_return_status OUT NOCOPY VARCHAR2) IS
3204 BEGIN
3205 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3206 g_qry_clause := p_srch_str;
3207 EXCEPTION
3208 WHEN OTHERS THEN
3209 x_return_status := OKC_API.G_RET_STS_ERROR;
3210 END;
3211
3212 PROCEDURE Get_Search_String(
3213 x_srch_str OUT NOCOPY VARCHAR2) IS
3214 BEGIN
3215 x_srch_str := g_qry_clause;
3216 END;
3217
3218 -------------------------------------------------------------------------------
3219 --Procedure is just a sample API that allows users to create their own
3220 -- contract number. This procedure should be registered in the process
3221 -- definition form and associated in the Auto Numbering setup screen before
3222 -- it can be used to generate the contract number. This procedure can be
3223 -- defined in any package and all the user has to do is to register it in
3224 --the Process Definition Form.
3225 ------------------------------------------------------------------------------
3226 PROCEDURE generate_contract_number(
3227 x_contract_number OUT NOCOPY VARCHAR2,
3228 x_return_status OUT NOCOPY VARCHAR2) IS
3229 BEGIN
3230 x_contract_number := to_char(SYSDATE,'DDMMYYYYHHMISS');
3231 x_return_status := 'S';
3232 EXCEPTION
3233 WHEN OTHERS THEN
3234 x_return_status := 'U';
3235 END;
3236
3237 -------------------------------------------------------------------------
3238 FUNCTION get_application_name ( p_application_id number) return varchar2
3239 IS
3240 CURSOR csr_appl_name IS
3241 SELECT application_name
3242 FROM fnd_application_vl
3243 WHERE application_id = p_application_id;
3244
3245 l_application_name VARCHAR2(240):= NULL;
3246
3247 BEGIN
3248
3249 OPEN csr_appl_name;
3250 FETCH csr_appl_name INTO l_application_name;
3251 CLOSE csr_appl_name;
3252
3253 RETURN l_application_name;
3254
3255 END get_application_name;
3256
3257 -------------------------------------------------------------------------
3258 function DECODE_LOOKUP_DESC (p_lookup_type varchar2, p_lookup_code varchar2) return varchar2
3259 is
3260 --******************************************************************************
3261 --* Returns the description for a lookup code of a specified type.
3262 --******************************************************************************
3263 --
3264 cursor csr_lookup is
3265 select description
3266 from FND_LOOKUP_VALUES
3267 where language = userenv('LANG')
3268 and lookup_type = p_lookup_type
3269 and lookup_code = p_lookup_code;
3270
3271 v_description varchar2(240) := null;
3272
3273 begin
3274
3275 if p_lookup_type is not null and p_lookup_code is not null then
3276
3277 OPEN csr_lookup;
3278 FETCH csr_lookup INTO v_description;
3279 CLOSE csr_lookup;
3280
3281 end if;
3282
3283 return v_description;
3284
3285 end DECODE_LOOKUP_DESC;
3286 -------------------------------------------------------------------------
3287
3288 ----------------------------------------------------------------------------
3289 ---Procedure to Prepare Contract Terms (dummy in 11.5.9, real for 11.5.10)
3290 ----------------------------------------------------------------------------
3291 PROCEDURE Prepare_Contract_Terms(
3292 p_chr_id IN NUMBER,
3293 x_doc_id OUT NOCOPY NUMBER,
3294 x_doc_type OUT NOCOPY VARCHAR2,
3295 x_return_status OUT NOCOPY VARCHAR2
3296 ) IS
3297 l_msg_count NUMBER;
3298 l_msg_data VARCHAR2(2000);
3299 l_tmpl_id NUMBER;
3300 l_tmpl_name VARCHAR2(100);
3301 l_start_date DATE;
3302 l_org_id NUMBER;
3303 CURSOR start_date_crs IS
3304 SELECT start_date, authoring_org_id
3305 FROM OKC_K_HEADERS_B WHERE id=p_chr_id;
3306 CURSOR get_doc_usage_crs IS
3307 SELECT TEMPLATE_ID FROM okc_template_usages_v
3308 WHERE document_type = x_doc_type AND document_id = x_doc_id ;
3309 CURSOR get_apps_upg_tmpl_id_crs IS
3310 SELECT TEMPLATE_ID FROM okc_terms_templates_all
3311 WHERE template_name = l_tmpl_name and org_id=l_org_id;
3312 BEGIN
3313 IF (l_debug = 'Y') THEN
3314 okc_debug.log('11400: Entering Prepare_Contract_Terms ', 2);
3315 END IF;
3316 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
3317 OKC_TERMS_UTIL_GRP.Get_Contract_Document_Type_id(
3318 p_api_version => 1,
3319 x_return_status => x_return_status,
3320 x_msg_data => l_msg_data,
3321 x_msg_count => l_msg_count,
3322 p_chr_id => p_chr_id,
3323 x_doc_id => x_doc_id,
3324 x_doc_type => x_doc_type
3325 );
3326 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3328 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3329 RAISE FND_API.G_EXC_ERROR ;
3330 END IF;
3331
3332 OPEN get_doc_usage_crs;
3333 FETCH get_doc_usage_crs INTO l_tmpl_id;
3334 CLOSE get_doc_usage_crs;
3335
3336 IF l_tmpl_id IS NULL THEN
3337 OPEN start_date_crs;
3338 FETCH start_date_crs INTO l_start_date, l_org_id;
3339 CLOSE start_date_crs;
3340
3341 l_tmpl_name := x_doc_type || '_11510_UPG_TEMPLATE';
3342
3343 OPEN get_apps_upg_tmpl_id_crs;
3344 FETCH get_apps_upg_tmpl_id_crs INTO l_tmpl_id;
3345 CLOSE get_apps_upg_tmpl_id_crs;
3346
3347 IF l_tmpl_id IS NULL THEN
3348 SELECT OKC_TERMS_TEMPLATES_ALL_S.NEXTVAL
3349 INTO l_tmpl_id FROM DUAL;
3350 INSERT INTO OKC_TERMS_TEMPLATES_ALL(
3351 TEMPLATE_NAME,
3352 TEMPLATE_ID,
3353 WORKING_COPY_FLAG,
3354 INTENT,
3355 STATUS_CODE,
3356 START_DATE,
3357 GLOBAL_FLAG,
3358 CONTRACT_EXPERT_ENABLED,
3359 DESCRIPTION,
3360 ORG_ID,
3361 ORIG_SYSTEM_REFERENCE_CODE,
3362 HIDE_YN,
3363 OBJECT_VERSION_NUMBER,
3364 CREATED_BY,
3365 CREATION_DATE,
3366 LAST_UPDATED_BY,
3367 LAST_UPDATE_LOGIN,
3368 LAST_UPDATE_DATE)
3369 VALUES (
3370 l_tmpl_name,
3371 l_tmpl_id,
3372 'N',
3373 Decode( x_doc_type,'OKC_BUY','B','OKE_BUY','B', 'S'),
3374 'APPROVED',
3375 to_date('01-01-1951','DD-MM-YYYY'),
3376 'N',
3377 'N',
3378 'Dummy Template for 11.5.10 Upgrade',
3379 l_org_id,
3380 decode (x_doc_type,'OKE_SELL', 'OKC11510UPG:OKE', 'OKE_BUY', 'OKC11510UPG:OKE', 'OKC11510UPG'),
3381 decode(x_doc_type,'OKS','N','Y'),
3382 1,
3383 Fnd_Global.User_Id,
3384 trunc(sysdate),
3385 Fnd_Global.User_Id,
3386 Fnd_Global.Login_Id,
3387 trunc(sysdate)
3388 );
3389 INSERT INTO OKC_ALLOWED_TMPL_USAGES(
3390 ALLOWED_TMPL_USAGES_ID,
3391 TEMPLATE_ID,
3392 DOCUMENT_TYPE,
3393 DEFAULT_YN,
3394 OBJECT_VERSION_NUMBER,
3395 CREATED_BY,
3396 CREATION_DATE,
3397 LAST_UPDATED_BY,
3398 LAST_UPDATE_LOGIN,
3399 LAST_UPDATE_DATE)
3400 VALUES (
3401 OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL,
3402 l_tmpl_id,
3403 x_doc_type,
3404 'N',
3405 1,
3406 Fnd_Global.User_Id,
3407 trunc(sysdate),
3408 Fnd_Global.User_Id,
3409 Fnd_Global.Login_Id,
3410 trunc(sysdate)
3411 );
3412 END IF;
3413 IF l_tmpl_id IS NOT NULL THEN
3414 OKC_TERMS_COPY_GRP.copy_terms(
3415 p_api_version => 1,
3416 x_return_status => x_return_status,
3417 x_msg_data => l_msg_data,
3418 x_msg_count => l_msg_count,
3419 p_commit => FND_API.G_TRUE,
3420
3421 p_template_id => l_tmpl_id,
3422 p_target_doc_type => x_doc_type,
3423 p_target_doc_id => x_doc_id,
3424 p_article_effective_date => l_start_date,
3425 p_validation_string => NULL
3426 );
3427 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3429 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3430 RAISE FND_API.G_EXC_ERROR ;
3431 END IF;
3432 END IF;
3433 END IF;
3434
3435 IF (l_debug = 'Y') THEN
3436 okc_debug.log('900: Leaving Prepare_Contract_Terms.', 2);
3437 END IF;
3438
3439 EXCEPTION
3440
3441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3442 IF (l_debug = 'Y') THEN
3443 okc_debug.log('500: Leaving Prepare_Contract_Terms because of FND_API.G_EXC_UNEXPECTED_ERROR ', 2);
3444 END IF;
3445
3446 IF get_doc_usage_crs%ISOPEN THEN
3447 CLOSE get_doc_usage_crs;
3448 END IF;
3449
3450 IF get_apps_upg_tmpl_id_crs%ISOPEN THEN
3451 CLOSE get_apps_upg_tmpl_id_crs;
3452 END IF;
3453
3454 IF start_date_crs%ISOPEN THEN
3455 CLOSE start_date_crs;
3456 END IF;
3457
3458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3459
3460 WHEN FND_API.G_EXC_ERROR THEN
3461 IF (l_debug = 'Y') THEN
3462 okc_debug.log('500: Leaving Prepare_Contract_Terms because of FND_API.G_EXC_ERROR. ', 2);
3463 END IF;
3464
3465 IF get_doc_usage_crs%ISOPEN THEN
3466 CLOSE get_doc_usage_crs;
3467 END IF;
3468
3469 IF get_apps_upg_tmpl_id_crs%ISOPEN THEN
3470 CLOSE get_apps_upg_tmpl_id_crs;
3471 END IF;
3472
3473 IF start_date_crs%ISOPEN THEN
3474 CLOSE start_date_crs;
3475 END IF;
3476
3477 x_return_status := FND_API.G_RET_STS_ERROR ;
3478
3479 WHEN OTHERS THEN
3480 IF (l_debug = 'Y') THEN
3481 okc_debug.log('500: Leaving Prepare_Contract_Terms because of EXCEPTION: '||sqlerrm, 2);
3482 END IF;
3483
3484 IF get_doc_usage_crs%ISOPEN THEN
3485 CLOSE get_doc_usage_crs;
3486 END IF;
3487
3488 IF get_apps_upg_tmpl_id_crs%ISOPEN THEN
3489 CLOSE get_apps_upg_tmpl_id_crs;
3490 END IF;
3491
3492 IF start_date_crs%ISOPEN THEN
3493 CLOSE start_date_crs;
3494 END IF;
3495
3496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3497 END Prepare_Contract_Terms;
3498
3499
3500
3501 ------------------------------------------------------------
3502 -- Anonymous block for the package
3503 --
3504 -- This anonymous block is used to populate the global variable
3505 -- g_language_code which contains the list of languages from
3506 -- FND_LANGUAGES
3507 ------------------------------------------------------------
3508
3509 BEGIN
3510
3511 SELECT language_code
3512 BULK COLLECT INTO g_language_code
3513 FROM fnd_languages
3514 WHERE installed_flag IN ( 'I', 'B' );
3515
3516 END OKC_UTIL;
[Home] [Help]