1 PACKAGE fnd_flex_val_util AUTHID CURRENT_USER AS
2 /* $Header: AFFFUTVS.pls 120.2.12010000.2 2008/11/10 18:50:54 hgeorgi ship $ */
3
4
5 bad_parameter EXCEPTION;
6 PRAGMA EXCEPTION_INIT(bad_parameter, -06501);
7
8 -- ==================================================
9 -- Success Codes.
10 -- ==================================================
11 g_ret_no_error NUMBER := 0;
12 g_ret_bad_parameter NUMBER := 1;
13 g_ret_exception_others NUMBER := 2;
14 g_ret_vs_bad_date NUMBER := 3;
15 g_ret_value_too_long NUMBER := 5;
16 g_ret_invalid_number NUMBER := 6;
17 g_ret_invalid_date NUMBER := 7;
18 g_ret_vs_bad_precision NUMBER := 8;
19 g_ret_vs_bad_format NUMBER := 9;
20 g_ret_vs_bad_numrange NUMBER := 10;
21 g_ret_vs_bad_daterange NUMBER := 11;
22 g_ret_val_out_of_range NUMBER := 12;
23
24 -- ==============================
25 -- FUNCTION : is_success
26 -- ==============================
27 -- Returns TRUE if p_success is g_ret_no_error,
28 -- Returns FALSE otherwise.
29 --
30 FUNCTION is_success(p_success IN NUMBER) RETURN BOOLEAN;
31 PRAGMA restrict_references (is_success, wnds, wnps);
32
33 -- ==================================================
34 -- Debugging
35 -- ==================================================
36 -- Errors and some internal steps are reported in debug.
37 --
38 -- ==============================
39 -- FUNCTION : get_debug
40 -- ==============================
41 -- Returns internal debug string.
42 --
43 FUNCTION get_debug RETURN VARCHAR2;
44
45 -- ==============================
46 -- PROCEDURE : set_debuging
47 -- ==============================
48 -- Turn ON/OFF debugging mechanism. By default it is turned ON.
49 --
50 PROCEDURE set_debugging(p_flag IN BOOLEAN DEFAULT TRUE);
51
52 -- ==================================================
53 -- Date, DateTime, Time and Numeric Format Masks
54 -- ==================================================
55 -- ------------------------------
56 -- Canonical Format Masks.
57 -- ------------------------------
58 -- These masks will be used while saving data into database or reading
59 -- data from database. They are hard coded and not alterable.
60 --
61 -- Mask Name: Value:
62 -- -----------------------------------------------
63 -- CANONICAL_DATE | 'YYYY/MM/DD HH24:MI:SS'
64 -- CANONICAL_DATETIME | 'YYYY/MM/DD HH24:MI:SS'
65 -- CANONICAL_TIME | 'HH24:MI:SS'
66 -- CANONICAL_NUMERIC_CHARS | '.,'
67 -- DB_NUMERIC_CHARS | substr(to_char(1234.5,'FM9G999D9'), 6, 1) ||
68 -- | substr(to_char(1234.5,'FM9G999D9'), 2, 1)
69 --
70 -- ------------------------------
71 -- NLS Masks.
72 -- ------------------------------
73 -- These masks will be used while interacting with user. IN masks will
74 -- be used while reading data from user, and OUT masks will be used while
75 -- presenting data to user.
76 -- They can be updated using set_mask function.
77 --
78 -- Out masks can only have one of it's kind.
79 -- In masks can be multiple, if multiple, must be seperated by '|'.
80 --
81 -- Mask Name : Default Value:
82 -- ------------------------------------------------
83 -- NLS_DATE_IN | fnd_date.user_mask
84 -- NLS_DATE_OUT | fnd_date.output_mask
85 -- NLS_DATETIME_IN | fnd_date.userdt_mask
86 -- NLS_DATETIME_OUT | fnd_date.outputdt_mask
87 -- NLS_TIME_IN | 'HH24:MI:SS'
88 -- NLS_TIME_OUT | 'HH24:MI:SS'
89 -- NLS_NUMERIC_CHARS_IN | substr(to_char(1234.5,'FM9G999D9'), 6, 1) ||
90 -- | substr(to_char(1234.5,'FM9G999D9'), 2, 1)
91 -- NLS_NUMERIC_CHARS_OUT | substr(to_char(1234.5,'FM9G999D9'), 6, 1) ||
92 -- | substr(to_char(1234.5,'FM9G999D9'), 2, 1)
93 --
94 -- ==============================
95 -- FUNCTION : get_mask
96 -- ==============================
97 -- Returns TRUE and the value of mask. Valid masks are given above.
98 -- Returns FALSE if the mask is not valid.
99 --
100 FUNCTION get_mask(p_mask_name IN VARCHAR2,
101 x_mask_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
102
103 -- ==============================
104 -- FUNCTION : set_mask
105 -- ==============================
106 -- Sets the value of mask and returns TRUE. Valid masks are given above.
107 -- Returns FALSE if the mask is not valid.
108 --
109 FUNCTION set_mask(p_mask_name IN VARCHAR2,
110 p_mask_value IN VARCHAR2) RETURN BOOLEAN;
111
112 -- ==============================
113 -- PROCEDURE : get_storage_format
114 -- ==============================
115 -- Returns the storage format of a value set.
116 -- Value set format type should be in {Number (N), Date (D), DateTime (T)
117 -- Time (I, t), Standard Date (X), Standard DateTime (Y), Standard Time (Z)}
118 -- In case of failure return FALSE.
119 --
120 FUNCTION get_storage_format(p_vset_format IN VARCHAR2,
121 p_max_length IN NUMBER,
122 p_precision IN NUMBER DEFAULT NULL,
123 x_format OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
124
125 -- ==============================
126 -- PROCEDURE : get_display_format
127 -- ==============================
128 -- Returns the display format of a value set.
129 -- Value set format type should be in {Number (N), Date (D), DateTime (T)
130 -- Time (I, t), Standard Date (X), Standard DateTime (Y), Standard Time (Z)}
131 -- x_format_in is the FROM USER TO VALIDATION ENGINE format.
132 -- x_format_out is the FROM VALIDATION ENGINE TO USER format.
133 -- In case of failure return FALSE.
134 --
135 FUNCTION get_display_format(p_vset_format IN VARCHAR2,
136 p_max_length IN NUMBER,
137 p_precision IN NUMBER DEFAULT NULL,
138 x_format_in OUT NOCOPY VARCHAR2,
139 x_format_out OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
140 -- ==============================
141 -- FUNCTION : is_date
142 -- ==============================
143 -- Checks whether a string is in correct date format, if so returns its date
144 -- value, otherwise returns FALSE.
145 -- p_nls_date_format can have multiple date masks separated by '|'.
146 --
147 FUNCTION is_date(p_value IN VARCHAR2,
148 p_nls_date_format IN VARCHAR2 DEFAULT NULL,
149 x_date OUT NOCOPY DATE) RETURN BOOLEAN;
150 PRAGMA restrict_references (is_date, wnds);
151
152 -- ==============================
153 -- FUNCTION : flex_to_date
154 -- ==============================
155 -- Cover routine for is_date, and this one returns DATE.
156 -- In case of error it returns NULL.
157 --
158 FUNCTION flex_to_date(p_value IN VARCHAR2,
159 p_nls_date_format IN VARCHAR2) RETURN DATE;
160 PRAGMA restrict_references(flex_to_date, wnds);
161
162 -- ==============================
163 -- FUNCTION : is_number
164 -- ==============================
165 -- Checks whether a string is in correct number format, if so returns its
166 -- number value, and its db equivalent, otherwise returns FALSE.
167 -- p_nls_numeric_chars can have multiple NLS_NUMERIC_CHARS separated by '|'.
168 --
169 FUNCTION is_number(p_value IN VARCHAR2,
170 p_nls_numeric_chars IN VARCHAR2,
171 x_value OUT NOCOPY VARCHAR2,
172 x_number OUT NOCOPY NUMBER) RETURN BOOLEAN;
173 PRAGMA restrict_references (is_number, wnds);
174
175 -- ==============================
176 -- FUNCTION : flex_to_number
177 -- ==============================
178 -- Cover routine for is_number, and this one returns NUMBER.
179 -- In case of error it returns NULL.
180 --
181 FUNCTION flex_to_number(p_value IN VARCHAR2,
182 p_nls_numeric_chars IN VARCHAR2) RETURN NUMBER;
183 PRAGMA restrict_references(flex_to_number, wnds);
184
185 -- ==============================
186 -- PROCEDURE : validate_value
187 -- ==============================
188 -- Validates the given value.
189 --
190 PROCEDURE validate_value(p_value IN VARCHAR2,
191 p_is_displayed IN BOOLEAN DEFAULT TRUE,
192 p_vset_name IN VARCHAR2 DEFAULT NULL,
193 p_vset_format IN VARCHAR2 DEFAULT 'C',
194 p_max_length IN NUMBER DEFAULT 0,
195 p_precision IN NUMBER DEFAULT NULL,
196 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
197 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
198 p_zero_fill IN VARCHAR2 DEFAULT 'N',
199 p_min_value IN VARCHAR2 DEFAULT NULL,
200 p_max_value IN VARCHAR2 DEFAULT NULL,
201 x_storage_value OUT NOCOPY VARCHAR2,
202 x_display_value OUT NOCOPY VARCHAR2,
203 x_success OUT NOCOPY BOOLEAN);
204
205 -- ==============================
206 -- FUNCTION : is_value_valid
207 -- ==============================
208 -- Checks if a value is valid or not.
209 --
210 FUNCTION is_value_valid(p_value IN VARCHAR2,
211 p_is_displayed IN BOOLEAN DEFAULT TRUE,
212 p_vset_name IN VARCHAR2 DEFAULT NULL,
213 p_vset_format IN VARCHAR2 DEFAULT 'C',
214 p_max_length IN NUMBER DEFAULT 0,
215 p_precision IN NUMBER DEFAULT NULL,
216 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
217 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
218 p_zero_fill IN VARCHAR2 DEFAULT 'N',
219 p_min_value IN VARCHAR2 DEFAULT NULL,
220 p_max_value IN VARCHAR2 DEFAULT NULL,
221 x_storage_value OUT NOCOPY VARCHAR2,
222 x_display_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
223 PRAGMA restrict_references (is_value_valid, wnds);
224
225 -- ==============================
226 -- FUNCTION : to_display_value
227 -- ==============================
228 -- Returns display equivalent of a storage value.
229 -- Returns NULL in case of an error.
230 --
231 FUNCTION to_display_value(p_value IN VARCHAR2,
232 p_vset_format IN VARCHAR2 DEFAULT 'C',
233 p_vset_name IN VARCHAR2 DEFAULT NULL,
234 p_max_length IN NUMBER DEFAULT 0,
235 p_precision IN NUMBER DEFAULT NULL,
236 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
237 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
238 p_zero_fill IN VARCHAR2 DEFAULT 'N',
239 p_min_value IN VARCHAR2 DEFAULT NULL,
240 p_max_value IN VARCHAR2 DEFAULT NULL)
241 RETURN VARCHAR2;
242 PRAGMA restrict_references (to_display_value, wnds);
243
244 -- ==============================
245 -- FUNCTION : to_storage_value
246 -- ==============================
247 -- Returns storage equivalent of a display value.
248 -- Returns NULL in case of an error.
249 --
250 FUNCTION to_storage_value(p_value IN VARCHAR2,
251 p_vset_format IN VARCHAR2 DEFAULT 'C',
252 p_vset_name IN VARCHAR2 DEFAULT NULL,
253 p_max_length IN NUMBER DEFAULT 0,
254 p_precision IN NUMBER DEFAULT NULL,
255 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
256 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
257 p_zero_fill IN VARCHAR2 DEFAULT 'N',
258 p_min_value IN VARCHAR2 DEFAULT NULL,
259 p_max_value IN VARCHAR2 DEFAULT NULL)
260 RETURN VARCHAR2;
261 PRAGMA restrict_references (to_storage_value, wnds);
262
263 -- ==============================
264 -- PROCEDURE : validate_value_private
265 -- ==============================
266 -- This procedure can only be used by Flexfields Server Side
267 -- Validation Engine. This is exactly same as validate_value, but
268 -- this one returns success code.
269 --
270 PROCEDURE validate_value_ssv(p_value IN VARCHAR2,
271 p_is_displayed IN BOOLEAN DEFAULT TRUE,
272 p_vset_name IN VARCHAR2 DEFAULT NULL,
273 p_vset_format IN VARCHAR2 DEFAULT 'C',
274 p_max_length IN NUMBER DEFAULT 0,
275 p_precision IN NUMBER DEFAULT NULL,
276 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
277 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
278 p_zero_fill IN VARCHAR2 DEFAULT 'N',
279 p_min_value IN VARCHAR2 DEFAULT NULL,
280 p_max_value IN VARCHAR2 DEFAULT NULL,
281 x_storage_value OUT NOCOPY VARCHAR2,
282 x_display_value OUT NOCOPY VARCHAR2,
283 x_success OUT NOCOPY NUMBER);
284 PRAGMA restrict_references (validate_value_ssv, wnds);
285
286 -- ==============================
287 -- PROCEDURE : get_server_global
288 -- ==============================
289 -- Used to get server side globals that can be retrieved as (in PL/SQL);
290 -- BEGIN
291 -- x_char_out := p_char_in;
292 -- END;
293 --
294 -- x_error : NUMBER : 0 : success
295 -- else : failure (SQLCODE)
296 -- x_message : VARCHAR2(1998): encoded error message, in case of failure.
297 --
298 -- This procedure can also be used to call server side functions which return
299 -- VARCHAR2.
300 --
301 -- p_char_in examples :
302 -- --------------------
303 -- 'fnd_date.user_mask'
304 -- 'fnd_number.decimal_character || fnd_number.group_separator'
305 -- 'fnd_number.number_to_canonical(1234.567)'
306 --
307 --
308 PROCEDURE get_server_global(p_char_in IN VARCHAR2,
309 x_char_out OUT NOCOPY VARCHAR2,
310 x_error OUT NOCOPY NUMBER,
311 x_message OUT NOCOPY VARCHAR2);
312
313 -- ==============================
314 -- PROCEDURE : vtv_to_display_value
315 -- ==============================
316 -- Converts the internal VTV (Compiled Value Attribute Values)
317 -- (For historical reasons it is called VTV) to displayed values.
318 -- i.e. Y\nN\nA will become : Yes.No.Asset in English.
319 -- This function is used by FNDFFMSV (Flex Values form.)
320 --
321 -- This function will do it's best, if any kind of failure happens
322 -- it will return NULL. No exception is raised.
323 --
324 -- If p_use_default is TRUE and the storage value is not complete,
325 -- this procedure will use the default values for segment qualifiers.
326 --
327 PROCEDURE vtv_to_display_value(p_flex_value_set_id IN NUMBER,
328 p_use_default IN BOOLEAN,
329 p_storage_value IN VARCHAR2,
330 x_display_value OUT NOCOPY VARCHAR2);
331
332 -- ===============================
333 -- PROCEDURE : flex_date_converter
334 -- ===============================
335 -- Flexfields date converter, (supports timezones)
336 --
337 -- p_vs_format_type : Value set format type.
338 -- p_tz_direction : Time zone conversion direction.
339 -- '1' : Server to local, '2' : Local to server.
340 -- p_input_mask : Format mask for input value.
341 -- p_input : Input datetime value as vc2.
342 -- p_output_mask : Format mask for output value.
343 -- x_output : Output datetime value as vc2.
344 -- x_error : Error code, 0 if there is no error.
345 -- x_message : Error message in case of error.
346 --
347 PROCEDURE flex_date_converter(p_vs_format_type IN VARCHAR2,
348 p_tz_direction IN VARCHAR2,
349 p_input_mask IN VARCHAR2,
350 p_input IN VARCHAR2,
351 p_output_mask IN VARCHAR2,
352 x_output OUT NOCOPY VARCHAR2,
353 x_error OUT NOCOPY NUMBER,
354 x_message OUT NOCOPY VARCHAR2);
355
356 -- =======================================================================
357 -- Added by NGOUGLER START
358 -- =======================================================================
359 -- ===============================
360 -- PROCEDURE : flex_date_converter_cal
361 -- ===============================
362 -- Flexfields calendar date converter, (supports timezones)
363 --
364 -- p_vs_format_type : Value set format type.
365 -- p_tz_direction : Time zone conversion direction.
366 -- '1' : Server to local, '2' : Local to server.
367 -- p_cal_direction : Calendar conversion direction.
368 -- '1' : Gregorain to User calendar, '2' : User to Gregorian calendar
369 -- p_mask : Format mask for date value
370 -- p_calendar : Calendar information for date value
371 -- p_input : Input datetime value as vc2.
372 -- x_output : Output datetime value as vc2.
373 -- x_error : Error code, 0 if there is no error.
374 -- x_message : Error message in case of error.
375 --
376
377 PROCEDURE flex_date_converter_cal(p_vs_format_type IN VARCHAR2,
378 p_tz_direction IN VARCHAR2,
379 p_cal_direction IN VARCHAR2,
380 p_mask IN VARCHAR2,
381 p_calendar IN VARCHAR2,
382 p_input IN VARCHAR2,
383 x_output OUT NOCOPY VARCHAR2,
384 x_error OUT NOCOPY NUMBER,
385 x_message OUT NOCOPY VARCHAR2);
386
387
388 -- =======================================================================
389 -- Added by NGOUGLER END
390 -- =======================================================================
391
392
393 END fnd_flex_val_util;
[Home] [Help]