1 PACKAGE OKC_UTIL AUTHID CURRENT_USER AS
2 /* $Header: OKCUTILS.pls 120.0 2005/05/25 19:19:39 appldev noship $ */
3
4 -------------------------
5 -- trace global variables
6 -------------------------
7 l_trace_path VARCHAR2(255);
8 l_trace_file UTL_FILE.FILE_TYPE;
9 l_trace_file_name VARCHAR2(255);
10 l_output_file UTL_FILE.FILE_TYPE;
11 l_output_file_name VARCHAR2(255);
12 l_trace_flag BOOLEAN :=FALSE;
13 l_log_flag BOOLEAN :=FALSE;
14 l_output_flag BOOLEAN :=FALSE;
15 l_before_trace_flag BOOLEAN :=FALSE;
16 l_request_id NUMBER;
17 l_program VARCHAR2(80);
18 l_module VARCHAR2(80);
19 l_complete_trace_file_name VARCHAR2(255);
20 l_complete_trace_file_name2 VARCHAR2(255) DEFAULT ' ';
21
22 -------------------------
23 -- standard trace constants
24 -------------------------
25 g_trc_trace_file_prefix CONSTANT VARCHAR2(30) := 'okc_';
26 g_trc_trace_file_suffix CONSTANT VARCHAR2(30) := '.trc';
27
28 ---------------------------------------------------------------------------
29 -- GLOBAL DATASTRUCTURES
30 ---------------------------------------------------------------------------
31 TYPE unq_rec_type IS RECORD (
32 p_col_name varchar2(40),
33 p_col_val varchar2(2000));
34
35 TYPE unq_tbl_type IS TABLE OF unq_rec_type
36 INDEX BY BINARY_INTEGER;
37
38 TYPE okc_control_rec_type IS RECORD (
39 source varchar2(15),
40 id number,
41 flag varchar2(1),
42 code varchar2(30),
43 name varchar2(240),
44 comments varchar2(4000));
45
46 TYPE okc_control_tbl_type IS TABLE OF okc_control_rec_type
47 INDEX BY BINARY_INTEGER;
48
49 -- Stores the languages that are currently defined in FND_LANGUAGES.
50 -- This table is populated by the anonymous block in the package body.
51
52 g_language_code OKC_DATATYPES.Var12TabTyp;
53
54 ----------------------------------------------------------------------------
55 -- Procedure to add a view for checking length into global table
56 ----------------------------------------------------------------------------
57 Procedure add_view(
58 p_view_name IN VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2);
60
61 ----------------------------------------------------------------------------
62 -- checks length of a varchar2 column
63 ----------------------------------------------------------------------------
64 Procedure check_length(
65 p_view_name IN VARCHAR2,
66 p_col_name IN VARCHAR2,
67 p_col_value IN VARCHAR2,
68 x_return_status OUT NOCOPY VARCHAR2);
69
70 ----------------------------------------------------------------------------
71 -- checks length of a number column
72 ----------------------------------------------------------------------------
73 Procedure check_length(
74 p_view_name IN VARCHAR2,
75 p_col_name IN VARCHAR2,
76 p_col_value IN NUMBER,
77 x_return_status OUT NOCOPY VARCHAR2);
78
79 ----------------------------------------------------------------------------
80 --checks uniqnuess of varchar2 when primary key is ID
81 ----------------------------------------------------------------------------
82 Procedure Check_Unique(
83 p_view_name IN VARCHAR2,
84 p_col_name IN VARCHAR2,
85 p_col_value IN VARCHAR2,
86 p_id IN NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2);
88
89 ----------------------------------------------------------------------------
90 --checks uniquness of DATE when primary key is ID
91 ----------------------------------------------------------------------------
92 Procedure Check_Unique(
93 p_view_name IN VARCHAR2,
94 p_col_name IN VARCHAR2,
95 p_col_value IN DATE,
96 p_id IN NUMBER,
97 x_return_status OUT NOCOPY VARCHAR2);
98
99 ----------------------------------------------------------------------------
100 --checks uniquness of NUMBER when primary key is ID
101 ----------------------------------------------------------------------------
102 Procedure Check_Unique(
103 p_view_name IN VARCHAR2,
104 p_col_name IN VARCHAR2,
105 p_col_value IN NUMBER,
106 p_id IN NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2);
108
109 ----------------------------------------------------------------------------
110 --checks uniqueness of composite value made up of multiple columns when primary key is ID
111 ----------------------------------------------------------------------------
112 Procedure Check_Comp_Unique(
113 p_view_name IN VARCHAR2,
114 p_col_tbl IN unq_tbl_type,
115 p_id IN NUMBER,
116 x_return_status OUT NOCOPY VARCHAR2);
117
118 ----------------------------------------------------------------------------
119 --checks uniqueness of varchar2 when primary key is other than ID
120 ----------------------------------------------------------------------------
121 Procedure Check_Unique(
122 p_table_name IN VARCHAR2,
123 p_col_name IN VARCHAR2,
124 p_col_value IN VARCHAR2,
125 p_primary IN unq_tbl_type,
126 x_return_status OUT NOCOPY VARCHAR2);
127
128 ----------------------------------------------------------------------------
129 --checks uniqueness of NUMBER when primary key is other than ID
130 ----------------------------------------------------------------------------
131 Procedure Check_Unique(
132 p_table_name IN VARCHAR2,
133 p_col_name IN VARCHAR2,
134 p_col_value IN NUMBER,
135 p_primary IN unq_tbl_type,
136 x_return_status OUT NOCOPY VARCHAR2);
137
138 ----------------------------------------------------------------------------
139 --checks uniqueness of DATE when primary key is other than ID
140 ----------------------------------------------------------------------------
141 Procedure Check_Unique(
142 p_table_name IN VARCHAR2,
143 p_col_name IN VARCHAR2,
144 p_col_value IN DATE,
145 p_primary IN unq_tbl_type,
146 x_return_status OUT NOCOPY VARCHAR2);
147
148 ----------------------------------------------------------------------------
149 --checks uniqueness of composite value made up of multiple columns when primary key is other than ID
150 ----------------------------------------------------------------------------
151 Procedure Check_Comp_Unique(
152 p_table_name IN VARCHAR2,
153 p_col_tbl IN unq_tbl_type,
154 p_primary IN unq_tbl_type,
155 x_return_status OUT NOCOPY VARCHAR2);
156
157 ----------------------------------------------------------------------------
158 --Check uniquness for COMPOSITE/Primary key Columns in a table
159 ----------------------------------------------------------------------------
160 Procedure Check_Comp_Unique(
161 p_view_name IN VARCHAR2,
162 p_col_tbl IN unq_tbl_type,
163 x_return_status OUT NOCOPY VARCHAR2);
164
165 ---------------------------------------------------------------------------
166 --GLOBAL CONSTANT
167 ---------------------------------------------------------------------------
168 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
169 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
170 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
171 G_LEN_CHK CONSTANT VARCHAR2(200) := 'OKC_LENGTH_EXCEEDS';
172 G_UNQ CONSTANT VARCHAR2(200) := 'OKC_VALUE_NOT_UNIQUE';
173 G_UNQS CONSTANT VARCHAR2(200) := 'OKC_VALUES_NOT_UNIQUE';
174 G_NVL CONSTANT VARCHAR2(200) := 'OKC_NULL_VALUE_PASSED';
175 G_NVL_CODE CONSTANT VARCHAR2(200) := 'OKC_NULL_CODE_PASSED';
176 G_ALL_NVL CONSTANT VARCHAR2(200) := 'OKC_ALL_NULLS_PASSED';
177 G_NOTFOUND CONSTANT VARCHAR2(200) := 'OKC_VIEW_NOT_FOUND';
178 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
179 G_EXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_VALUE_ERROR';
180 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
181 G_VIEW_TOKEN CONSTANT VARCHAR2(200) := 'G_VIEW_TOKEN';
182 ---------------------------------------------------------------------------
183 --GLOBAL CONSTANT
184 ---------------------------------------------------------------------------
185
186 procedure call_user_hook(
187 x_return_status OUT NOCOPY VARCHAR2,
188 p_package_name IN VARCHAR2,
189 p_procedure_name IN VARCHAR2,
190 p_before_after IN VARCHAR2);
191
192 ----------------------------------------------------------------------------
193 -- Count number of business days between two dates
194 ----------------------------------------------------------------------------
195 FUNCTION count_business_days(
196 start_date IN DATE,
197 end_date IN DATE)
198 return NUMBER;
199
200 ----------------------------------------------------------------------------
201 --Check if valid code for a type in fnd lookup
202 ----------------------------------------------------------------------------
203 FUNCTION check_lookup_code(
204 p_type in VARCHAR2,
205 p_code IN VARCHAR2)
206 return VARCHAR2;
207
208
209 ----------------------------------------------------------------------------
210 --Functions from John to get data from JTF objects
211 ----------------------------------------------------------------------------
212 FUNCTION GET_NAME_FROM_JTFV(
213 p_object_code IN VARCHAR2,
214 p_id1 IN VARCHAR2,
215 p_id2 IN VARCHAR2)
216 RETURN VARCHAR2;
217
218 FUNCTION GET_DESC_FROM_JTFV(
219 p_object_code IN VARCHAR2,
220 p_id1 IN VARCHAR2,
221 p_id2 IN VARCHAR2)
222 RETURN VARCHAR2;
223
224 PROCEDURE GET_NAME_DESC_FROM_JTFV(
225 p_object_code IN VARCHAR2,
226 p_id1 IN VARCHAR2,
227 p_id2 IN VARCHAR2,
228 x_name OUT NOCOPY VARCHAR2,
229 x_description OUT NOCOPY VARCHAR2);
230
231 FUNCTION GET_SQL_FROM_JTFV(p_object_code IN VARCHAR2) RETURN VARCHAR2;
232
233 FUNCTION GET_SELECTNAME_FROM_JTFV(p_object_code IN VARCHAR2,p_id IN NUMBER) RETURN VARCHAR2;
234
235 Function Get_All_K_Access_Level(p_chr_id IN NUMBER,
236 p_application_id IN NUMBER Default Null,
237 p_scs_code IN VARCHAR2 Default Null) Return Varchar2;
238
239
240 ----------------------------------------------------------------------
241 --- get_k_access_level
242 ----------------------------------------------------------------------
243 -- Function Get_K_Access_Level
244 -- This function checks whether the current user has access to a given
245 -- contract. The contract id and the subclass (optionally) are passed
246 -- in. The called from parameter denotes whether the function was called
247 -- from forms or the Java(security) code in contracts online. An orig
248 -- source code of KSSA_HDR means that the contract was created in contracts
249 -- online. Currently to isolate the contracts from contracts online and
250 -- the contracts created in forms, a contracts created in forms will have
251 -- only a read access in online, except attachments. Any
252 -- attachment created in forms, can be updated in contracts online subject
253 -- to the modify access being available to the user. Any contract created
254 -- in contracts online can be modified in forms as per the rules pertaining
255 -- to forms contracts.
256 -- It returns the highest type of access that the user has based on the
257 -- setup and the source. The types are:
258 -- U - Update
259 -- R - Read only
260 -- N - No access
261 ----------------------------------------------------------------------
262
263 Function Get_K_Access_Level(p_chr_id IN NUMBER,
264 p_scs_code IN VARCHAR2 Default Null,
265 p_called_from IN VARCHAR2 Default 'F',
266 p_update_attachment IN VARCHAR2 Default 'false',
267 p_orig_source_code IN VARCHAR2 DEFAULT Null ) Return Varchar2;
268
269 Function Create_K_Access(p_scs_code IN VARCHAR2 ) Return Boolean;
270
271 -----------------------------------------------------------------------------
272 --copies clob text to other recs with same source_lang as lang
273 ------------------------------------------------------------------------------
274 FUNCTION Copy_CLOB(id number,release varchar2,lang varchar2)
275 RETURN VARCHAR2;
276
277 -----------------------------------------------------------------------------
278 -- copies clob text to other recs with same source_lang as lang
279 -- in OKC_K_ARTICLES_TL table
280 ------------------------------------------------------------------------------
281 FUNCTION Copy_Articles_Text(p_id NUMBER,lang varchar2,p_text VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
282
283 -----------------------------------------------------------------------------
284 -- copies standard article text to non-standard article's varied text
285 ------------------------------------------------------------------------------
286 FUNCTION Copy_Articles_Varied_Text(
287 p_article_id NUMBER,
288 p_sae_id NUMBER,
289 lang varchar2)
290 RETURN VARCHAR2;
291
292 -----------------------------------------------------------------------------
293 --Function to retrieve the Organization Title for forms; called from OKCSTAND.pll
294 ------------------------------------------------------------------------------
295 FUNCTION Get_Org_Window_Title
296 RETURN VARCHAR2;
297
298
299 PROCEDURE forms_savepoint(p_savepoint IN VARCHAR2);
300
301 PROCEDURE forms_rollback(p_savepoint IN VARCHAR2);
302
303 PROCEDURE init_msg_list(
304 p_init_msg_list IN VARCHAR2);
305
306 PROCEDURE set_message (
307 p_app_name IN VARCHAR2 DEFAULT OKC_API.G_APP_NAME,
308 p_msg_name IN VARCHAR2,
309 p_token1 IN VARCHAR2 DEFAULT NULL,
310 p_token1_value IN VARCHAR2 DEFAULT NULL,
311 p_token2 IN VARCHAR2 DEFAULT NULL,
312 p_token2_value IN VARCHAR2 DEFAULT NULL,
313 p_token3 IN VARCHAR2 DEFAULT NULL,
314 p_token3_value IN VARCHAR2 DEFAULT NULL,
315 p_token4 IN VARCHAR2 DEFAULT NULL,
316 p_token4_value IN VARCHAR2 DEFAULT NULL,
317 p_token5 IN VARCHAR2 DEFAULT NULL,
318 p_token5_value IN VARCHAR2 DEFAULT NULL,
319 p_token6 IN VARCHAR2 DEFAULT NULL,
320 p_token6_value IN VARCHAR2 DEFAULT NULL,
321 p_token7 IN VARCHAR2 DEFAULT NULL,
322 p_token7_value IN VARCHAR2 DEFAULT NULL,
323 p_token8 IN VARCHAR2 DEFAULT NULL,
324 p_token8_value IN VARCHAR2 DEFAULT NULL,
325 p_token9 IN VARCHAR2 DEFAULT NULL,
326 p_token9_value IN VARCHAR2 DEFAULT NULL,
327 p_token10 IN VARCHAR2 DEFAULT NULL,
328 p_token10_value IN VARCHAR2 DEFAULT NULL
329 );
330
331 /*
332 -------------------------------------------------------------------------------------------
333 -- Procedure: get_trace_path
334 -- Version: 1.0
335 -- Purpose: define the root directory for trace files
336 --
337 -- In Parameters:
338 -- Out Parameters:
339 --
340 FUNCTION get_trace_path (p_path IN VARCHAR2)
341 RETURN VARCHAR2;
342
343 -------------------------------------------------------------------------------------------
344
345 -------------------------------------------------------------------------------------------
346 -- Procedure: close_trace_file
347 -- Version: 1.0
348 -- Purpose: close the trace file for the current session
349 --
350 -- In Parameters:
351 -- Out Parameters:
352 --
353 PROCEDURE close_trace_file;
354
355 -------------------------------------------------------------------------------------------
356 -------------------------------------------------------------------------------------------
357 -- Procedure: reset_trace_context
358 -- Version: 1.0
359 -- Purpose: Resets the trace context or closes a log file for a conc.program
360 --
361 -- In Parameters:
362 -- Out Parameters:
363 --
364 PROCEDURE reset_trace_context;
365
366 -------------------------------------------------------------------------------------------
367 -------------------------------------------------------------------------------------------
368 -- Procedure: open_trace_file
369 -- Version: 1.0
370 -- Purpose: open a trace file for the current session
371 --
372 -- In Parameters:
373 -- Out Parameters:
374 --
375 PROCEDURE open_trace_file(g_request_id NUMBER);
376
377 */
378
379 -------------------------------------------------------------------------------------------
380 -- Procedure: set_trace_context
381 -- Version: 1.0
382 -- Purpose: sets the trace context or opens a log file for a conc.program
383 --
384 -- In Parameters:
385 -- Out Parameters:
386 --
387 PROCEDURE set_trace_context(g_request_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2);
388 --
389 -------------------------------------------------------------------------------------------
390 -- Procedure: stop_trace
391 -- Version: 1.0
392 -- Purpose: Turn off the trace mode
393 --
394 -- In Parameters:
395 -- Out Parameters:
396 --
397 PROCEDURE stop_trace;
398
399 -------------------------------------------------------------------------------------------
400 -- Procedure: print_output
401 -- Version: 1.0
402 -- Purpose: write a output line in the output file
403 --
404 -- In Parameters:
405 -- Out Parameters:
406 --
407 PROCEDURE print_output (p_indent IN NUMBER,
408 p_trace_line IN VARCHAR2);
409
410 -------------------------------------------------------------------------------------------
411 -- Procedure: print_trace
412 -- Version: 1.0
413 -- Purpose: write a trace line in the trace file
414 --
415 -- In Parameters:
416 -- Out Parameters:
417 --
418 PROCEDURE print_trace (p_indent IN NUMBER,
419 p_trace_line IN VARCHAR2,
420 p_level IN NUMBER DEFAULT 1,
421 p_module IN VARCHAR2 DEFAULT 'OKC');
422
423 -------------------------------------------------------------------------------------------
424 -- Procedure: print_trace_header
425 -- Purpose: print the standard header for trace files
426 --
427 -- In Parameters:
428 -- Out Parameters:
429 --
430 PROCEDURE print_trace_header;
431
432 -------------------------------------------------------------------------------------------
433 -- Procedure: print_trace_footer
434 -- Purpose: print the standard footer for trace files
435 --
436 -- In Parameters:
437 -- Out Parameters:
438 --
439 PROCEDURE print_trace_footer;
440
441 -------------------------------------------------------------------------------------------
442 -- Procedure: init_trace
443 -- Version: 1.0
444 -- Purpose: setup the trace mode
445 --
446 -- In Parameters:
447 -- Out Parameters:
448 --
449 PROCEDURE init_trace;
450
451 -------------------------------------------------------------------------------------------
452 -- Function get_userenv_lang
453 -- Purpose: This function returns the value of USERENV('LANG').
454 -- Once it has retrieved the value, it is cached and subsequent calls
455 -- to this function from the same session, do not result in a database
456 -- hit. This is because a := USERENV('LANG') results in a
457 -- SELECT USERENV('LANG') FROM SYS.DUAL; and can be an overhead
458 -- for mass INSERTs/UPDATEs.
459 --
460 -- Caching is done in the global variable g_userenv_lang
461 -- declared in the package BODY
462 --
463 -- This is a partial fix for Bug 1365356.
464 --
465 -- In Parameters : None
466 -- Out Parameters: None
467 -- Return value : VARCHAR2
468 --
469
470 FUNCTION get_userenv_lang RETURN VARCHAR2;
471
472 Function get_prcnt(
473 p_owner varchar2,
474 p_table varchar2,
475 p_column varchar2,
476 p_value varchar2) return number;
477
478 -- returns 0 if small group
479 -- returns 1 if not small group
480 Function grp_dense(p_grp_like varchar2) return number;
481
482 -------------------------------------------------------------------------
483 function DECODE_LOOKUP ( p_lookup_type varchar2,
484 p_lookup_code varchar2) return varchar2;
485
486 PROCEDURE Set_Search_String(
487 p_srch_str IN VARCHAR2,
488 x_return_status OUT NOCOPY VARCHAR2);
489
490 -------------------------------------------------------------------
491 ---- Variable used in Set_Search_String
492 -------------------------------------------------------------------
493 g_qry_clause Varchar2(2000);
494
495 PROCEDURE Get_Search_String(
496 x_srch_str OUT NOCOPY VARCHAR2);
497
498 ----------------------------------------------------------------------------
499 ---Procedure to generate contract number
500 ----------------------------------------------------------------------------
501
502 PROCEDURE generate_contract_number(
503 x_contract_number OUT NOCOPY VARCHAR2,
504 x_return_status OUT NOCOPY VARCHAR2);
505
506 -------------------------------------------------------------------------
507 function get_application_name ( p_application_id number) return varchar2;
508
509 -------------------------------------------------------------------------
510 function DECODE_LOOKUP_DESC ( p_lookup_type varchar2,
511 p_lookup_code varchar2) return varchar2;
512 ----------------------------------------------------------------------------
513 ---Procedure to Prepare Contract Terms (dummy in 11.5.9, real for 11.5.10)
514 ----------------------------------------------------------------------------
515 PROCEDURE Prepare_Contract_Terms(
516 p_chr_id IN NUMBER,
517 x_doc_id OUT NOCOPY NUMBER,
518 x_doc_type OUT NOCOPY VARCHAR2,
519 x_return_status OUT NOCOPY VARCHAR2
520 );
521 ------------------------------------------------------------------------
522
523 ----------------------------------------------------------------------------
524 --Function to decide to a descriptive flexfield should be displayed
525 --It will return 'Y' if at least one of the DFF segment is both enabled and displayed
526 --It will return 'N' otherwise
527 --p_api_version: standard input parameter for the API version
528 --p_init_msg_list: standard input parameter for initialize message or not, defaulted to False
529 --p_application_short_name: the three letter application short name, e.g. 'OKC'
530 --p_dff_name: the name of the descriptive flexfield, e.g., 'DELIVERABLES_FLEX'
531 ----------------------------------------------------------------------------
532 FUNCTION Dff_Displayed ( p_api_version IN NUMBER,
533 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
534 p_application_short_name VARCHAR2,
535 p_dff_name VARCHAR2,
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_msg_data OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER)
539 return VARCHAR2;
540 END OKC_UTIL;
[Home] [Help]