1 PACKAGE fnd_flex_server1 AUTHID CURRENT_USER AS
2 /* $Header: AFFFSV1S.pls 120.1.12010000.1 2008/07/25 14:14:25 appldev ship $ */
3
4 --------
5 -- PRIVATE INTER-PACKAGE TYPES
6 --
7 -- Segment array is 1-based containing entries for i <= i <= nsegs
8 --
9
10 TYPE ValueArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
11 TYPE ValueIdArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
12 TYPE ValueDescArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
13
14 TYPE StringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
15 TYPE CharArray IS TABLE OF CHAR INDEX BY BINARY_INTEGER;
16 TYPE NumberArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
17 TYPE BooleanArray IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
18
19 TYPE ValAttribArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
20 TYPE QualNameArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
21 TYPE TabColArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
22 TYPE ErrMsgArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
23 TYPE AppNameArray IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
24
25 TYPE FlexStructId IS RECORD
26 (isa_key_flexfield BOOLEAN,
27 application_id FND_ID_FLEX_SEGMENTS.APPLICATION_ID%TYPE,
28 id_flex_code FND_ID_FLEX_SEGMENTS.ID_FLEX_CODE%TYPE,
29 id_flex_num FND_ID_FLEX_SEGMENTS.ID_FLEX_NUM%TYPE,
30 desc_flex_name FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
31 desc_flex_context FND_DESCR_FLEX_CONTEXTS.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE
32 );
33
34 TYPE DescFlexInfo IS RECORD
35 (application_id FND_DESCRIPTIVE_FLEXS.APPLICATION_ID%TYPE,
36 name FND_DESCRIPTIVE_FLEXS.DESCRIPTIVE_FLEXFIELD_NAME%TYPE,
37 description FND_DESCRIPTIVE_FLEXS_TL.DESCRIPTION%TYPE,
38 table_appl_id FND_DESCRIPTIVE_FLEXS.TABLE_APPLICATION_ID%TYPE,
39 table_name FND_DESCRIPTIVE_FLEXS.APPLICATION_TABLE_NAME%TYPE,
40 table_id FND_TABLES.TABLE_ID%TYPE,
41 context_required FND_DESCRIPTIVE_FLEXS.CONTEXT_REQUIRED_FLAG%TYPE,
42 context_column FND_DESCRIPTIVE_FLEXS.CONTEXT_COLUMN_NAME%TYPE,
43 context_override FND_DESCRIPTIVE_FLEXS.CONTEXT_USER_OVERRIDE_FLAG%TYPE,
44 segment_delimiter FND_DESCRIPTIVE_FLEXS.CONCATENATED_SEGMENT_DELIMITER%TYPE,
45 protected_flag FND_DESCRIPTIVE_FLEXS.PROTECTED_FLAG%TYPE,
46 default_context FND_DESCRIPTIVE_FLEXS.DEFAULT_CONTEXT_VALUE%TYPE,
47 reference_field FND_DESCRIPTIVE_FLEXS.DEFAULT_CONTEXT_FIELD_NAME%TYPE,
48 context_override_value_set_id FND_DESCRIPTIVE_FLEXS.context_override_value_set_id%TYPE,
49 context_default_type FND_DESCRIPTIVE_FLEXS.context_default_type%TYPE,
50 context_default_value FND_DESCRIPTIVE_FLEXS.context_default_value%TYPE,
51 context_runtime_property_funct FND_DESCRIPTIVE_FLEXS.context_runtime_property_funct%TYPE);
52
53 TYPE CombTblInfo IS RECORD
54 (table_application_id NUMBER,
55 combination_table_id NUMBER,
56 application_table_name VARCHAR2(30),
57 select_comb_from VARCHAR2(30),
58 application_table_type VARCHAR2(1),
59 unique_id_column_name VARCHAR2(30),
60 set_defining_column_name VARCHAR2(30));
61
62 TYPE FlexStructInfo IS RECORD
63 (maximum_concatenation_len NUMBER(15),
64 concatenation_len_warning VARCHAR2(240),
65 enabled_flag VARCHAR2(1),
66 concatenated_segment_delimiter VARCHAR2(1),
67 cross_segment_validation_flag VARCHAR2(1),
68 dynamic_inserts_feasible_flag VARCHAR2(1),
69 dynamic_inserts_allowed_flag VARCHAR2(1));
70
71 TYPE FlexQualTable IS RECORD
72 (nentries NUMBER,
73 seg_indexes NumberArray,
74 fq_names QualNameArray);
75
76 TYPE DerivedVals IS RECORD
77 (enabled_flag VARCHAR2(1),
78 summary_flag VARCHAR2(1),
79 start_valid DATE,
80 end_valid DATE);
81
82 TYPE Qualifiers IS RECORD
83 (nquals NUMBER,
84 fq_names QualNameArray,
85 sq_names QualNameArray,
86 sq_values ValAttribArray,
87 derived_cols TabColArray);
88
89 TYPE Vrules IS RECORD
90 (nvrules NUMBER,
91 fq_names QualNameArray,
92 sq_names QualNameArray,
93 ie_flags CharArray,
94 cat_vals ValAttribArray,
95 app_names AppNameArray,
96 err_names ErrMsgArray);
97
98 TYPE SegFormats IS RECORD
99 (nsegs NUMBER,
100 vs_format CharArray,
101 vs_maxsize NumberArray);
102
103 TYPE ColumnValues IS RECORD
104 (ncolumns NUMBER,
105 column_names TabColArray,
106 column_types CharArray,
107 column_values StringArray);
108
109 TYPE ColumnDefinitions IS RECORD
110 (context_value_set BOOLEAN,
111 context_value VARCHAR2(80),
112 colvals ColumnValues);
113
114 -- Parsed version of the DISPLAYED token.
115 -- If no_segs_displayed is TRUE then no segments are displayed.
116 -- If some segments are displayed, then segflags[] array
117 -- determines whether each segment is displayed. Segflags has one entry
118 -- for each enabled segment in the order of segment_num.
119 --
120 TYPE DisplayedSegs IS RECORD
121 (n_segflags NUMBER,
122 segflags BooleanArray);
123
124 -- Explanation of value validation flags:
125 --
126 -- values_not_ids is TRUE if values rather than ids passed to validate_struct()
127 -- Defaults all displayed values which are null if default_all_displayed.
128 -- Defaults all required values which are null if default_all_required.
129 -- Defaults only those required values which are not displayed if
130 -- default_non_displayed.
131 -- If allow_nulls is TRUE then null required segments are valid.
132 -- message_on_null means set value error message if required segment is null.
133 -- If all_orphans_valid is TRUE should consider all possible dependent
134 -- values to be valid if the parent value is null.
135 -- stop_on_value_error means stop segment validation on non-security value
136 -- error. Validation always stops if error is more serious than
137 -- that the user enterd a bad value.
138 -- stop_on_security means stop segment validation if a security violated.
139 -- message_on_security means set value error message if security violated.
140 -- Does not check value security rules if ignore_security is TRUE
141 -- Does not check if value is disabled or expired if ignore_disabled TRUE.
142 --
143 /* bug872437 : invoking_mode is added. */
144
145 TYPE ValueValidationFlags IS RECORD
146 (values_not_ids BOOLEAN,
147 default_all_displayed BOOLEAN,
148 default_all_required BOOLEAN,
149 default_non_displayed BOOLEAN,
150 allow_nulls BOOLEAN,
151 message_on_null BOOLEAN,
152 all_orphans_valid BOOLEAN,
153 exact_nsegs_required BOOLEAN,
154 stop_on_value_error BOOLEAN,
155 stop_on_security BOOLEAN,
156 message_on_security BOOLEAN,
157 ignore_security BOOLEAN,
158 ignore_expired BOOLEAN,
159 ignore_disabled BOOLEAN,
160 invoking_mode VARCHAR2(10));
161
162
163 ----------------------------------
164 -- PRIVATE INTER-PACKAGE CONSTANTS
165 --
166
167 -- These are used for TOKEN separator characters in parsing
168 -- and returning VRULES, DERIVED and VALATT. And they are also
169 -- used to separate multiple messages, and in checking Vrules
170
171 SEPARATOR CONSTANT VARCHAR2(4) := '\n';
172 TERMINATOR CONSTANT VARCHAR2(4) := '\0';
173 DATETIME_FMT CONSTANT VARCHAR2(21) := 'YYYY/MM/DD HH24:MI:SS';
174
175 -- Components of a value
176
177 VC_DISPVAL CONSTANT BINARY_INTEGER := 1;
178 VC_VALUE CONSTANT BINARY_INTEGER := 2;
179 VC_ID CONSTANT BINARY_INTEGER := 3;
180 VC_DESCRIPTION CONSTANT BINARY_INTEGER := 4;
181
182
183 -- Return status for validate_struct() and combination validation
184 -- Everything but UNSUPPORTED and ERROR represent errors in what the
185 -- user typed in as the combination.
186 --
187 -- VALID means everything ok.
188 -- SECURED means one or more values violate security rules, but otherwise
189 -- everything else is ok.
190 -- VALUES means there is one or more non-security value error.
191 -- COMBNOTFOUND means dynamic inserts off and combination does not exist.
192 -- Note that if combination not found by LOADID() this is ERROR.
193 -- WHERECLAUSEFAILURE
194 -- Combination exists but additonal where clause fails.
195 -- CROSSVAL means values violate cross-validation rules.
196 -- UNSUPPORTED means server cannot validate because of :block.field or
197 -- value validation type not supported by server.
198 -- ERROR means inconsistincy in the flex definition or structure. This is
199 -- not returned merely for errors in what the user typed in.
200
201 VV_VALID CONSTANT NUMBER := 0;
202 VV_SECURED CONSTANT NUMBER := 30;
203 VV_VALUES CONSTANT NUMBER := 100;
204 VV_COMBNOTFOUND CONSTANT NUMBER := 200;
205 VV_WHEREFAILURE CONSTANT NUMBER := 250;
206 VV_CROSSVAL CONSTANT NUMBER := 300;
207 VV_COMBEXISTS CONSTANT NUMBER := 400;
208 VV_UNSUPPORTED CONSTANT NUMBER := 900;
209 VV_ERROR CONSTANT NUMBER := 1000;
210 VV_CTXTNOSEG CONSTANT NUMBER := 1100;
211
212
213 -- Flexfield value validation status codes
214 -- These codes are used to indicate the status of each segment value.
215
216 /* Non-value error validating segment */
217 FF_VERROR CONSTANT VARCHAR2(1) := '*';
218
219 -- The following codes are compatible with the client definitions.
220 --
221
222 /* Value is valid */
223 FF_VVALID CONSTANT VARCHAR2(1) := 'V';
224
225 /* validated and not in RDBMS */
226 FF_VNOTFOUND CONSTANT VARCHAR2(1) := 'N';
227
228 /* valid, but not sdate <= vdate <= edate */
229 FF_VEXPIRED CONSTANT VARCHAR2(1) := 'E';
230
231 /* valid, but not enabled */
232 FF_VDISABLED CONSTANT VARCHAR2(1) := 'D';
233
234 /* Valid, but Security violation */
235 FF_VSECURED CONSTANT VARCHAR2(1) := 'S';
236
237 /* Indicates we don't know whether or not value is valid. */
238 FF_VUNKNOWN CONSTANT VARCHAR2(1) := 'U';
239
240 /* Mandatory segment is null */
241 FF_VREQUIRED CONSTANT VARCHAR2(1) := 'M';
242
243 /* Format violation */
244 FF_VFORMAT CONSTANT VARCHAR2(1) := 'F';
245
246 /* Segment violates Vrule. Value Rule - qualifiers */
247 FF_VVRULE CONSTANT VARCHAR2(1) := 'Q';
248
249 /* valu out of bounds not BETWEEN min AND max */
250 FF_VBOUNDS CONSTANT VARCHAR2(1) := 'B';
251
252 /* Orphaned - parent is null... */
253 FF_VORPHAN CONSTANT VARCHAR2(1) := 'O';
254
255 /* Inter segment range. Range flex min is larger than max. Not used here. */
256 FF_VSRANGE CONSTANT VARCHAR2(1) := 'R';
257
258 /* Key flex column range. Not used. */
259 FF_VCRANGE CONSTANT VARCHAR2(1) := 'r';
260
261 /* Descr context valid but no segs defined */
262 FF_CTXTNOSEG CONSTANT VARCHAR2(1) := 'T';
263
264 -- The following codes indicate errors at the combination level
265
266 /* Duplicate combination */
267 FF_VDUPLICATE CONSTANT VARCHAR2(1) := '2';
268
269 /* New combination, and inserts not allowed */
270 FF_VNOINSERT CONSTANT VARCHAR2(1) := '0';
271
272 /* Cross validation failure */
273 FF_VXVAL CONSTANT VARCHAR2(1) := 'X';
274
275
276 -- The following is used at both the segment and the combination level.
277
278 /* Generic Invalid - used by user-exit. Not used yet in PLSQL package. */
279 FF_VINVALID CONSTANT VARCHAR2(1) := 'I';
280
281 /* This debug level must be changed through set_debugging() procedure. */
282 -- Debugging information
283 g_debug_level NUMBER := 0;
284 --
285 -- 'OFF' : 0
286 -- 'ERROR' : 1
287 -- 'EXCEPTION' : 2
288 -- 'EVENT' : 3
289 -- 'PROCEDURE' : 4
290 -- 'STATEMENT' : 5
291 -- 'ALL' : 6
292 --
293
294 /* ------------------------------------------------------------------------ */
295
296 -- -- Breaks concatenated segments into separate columns
297 -- -- in rule-lines table.
298 -- --
299 -- -- Use ONLY for FND_FLEX_VALIDATION_RULE_LINES_T1 trigger.
300 -- --
301 --
302 -- FUNCTION breakup_segs(appid IN NUMBER,
303 -- flex_code IN VARCHAR2, flex_num IN NUMBER,
304 -- catsegs IN VARCHAR2, nsegs OUT NUMBER,
305 -- seg1 OUT VARCHAR2, seg2 OUT VARCHAR2,
309 -- seg9 OUT VARCHAR2, seg10 OUT VARCHAR2,
306 -- seg3 OUT VARCHAR2, seg4 OUT VARCHAR2,
307 -- seg5 OUT VARCHAR2, seg6 OUT VARCHAR2,
308 -- seg7 OUT VARCHAR2, seg8 OUT VARCHAR2,
310 -- seg11 OUT VARCHAR2, seg12 OUT VARCHAR2,
311 -- seg13 OUT VARCHAR2, seg14 OUT VARCHAR2,
312 -- seg15 OUT VARCHAR2, seg16 OUT VARCHAR2,
313 -- seg17 OUT VARCHAR2, seg18 OUT VARCHAR2,
314 -- seg19 OUT VARCHAR2, seg20 OUT VARCHAR2,
315 -- seg21 OUT VARCHAR2, seg22 OUT VARCHAR2,
316 -- seg23 OUT VARCHAR2, seg24 OUT VARCHAR2,
317 -- seg25 OUT VARCHAR2, seg26 OUT VARCHAR2,
318 -- seg27 OUT VARCHAR2, seg28 OUT VARCHAR2,
319 -- seg29 OUT VARCHAR2, seg30 OUT VARCHAR2) RETURN NUMBER;
320
321 /* -------------------------------------------------------------------- */
322 /* Private definitions */
323 /* */
324 /* The following functions are for use internal only by the */
325 /* FND_FLEX_SERVER package. They are externalized only because */
326 /* the flexfield validation routines cannot be put into a single */
327 /* package. They are not supported for any other purpose and */
328 /* will almost certainly change without notice. */
329 /* -------------------------------------------------------------------- */
330
331 FUNCTION parse_where_token(clause_in IN VARCHAR2,
332 clause_out OUT nocopy VARCHAR2) RETURN NUMBER;
333
334 FUNCTION check_comb_vrules(vrs IN Vrules,
335 sqs IN Qualifiers,
336 sumflg IN VARCHAR2) RETURN NUMBER;
337
338 FUNCTION vals_secured(fstruct IN FlexStructId,
339 nsegs IN NUMBER,
340 segs IN StringArray,
341 displ IN DisplayedSegs,
342 uappid IN NUMBER,
343 respid IN NUMBER) RETURN NUMBER;
344
345 FUNCTION validate_struct(fstruct IN FlexStructId,
346 tbl_apid IN NUMBER,
347 tbl_id IN NUMBER,
348 nsegs_in IN NUMBER,
349 segs IN StringArray,
350 dispsegs IN DisplayedSegs,
351 vflags IN ValueValidationFlags,
352 v_date IN DATE,
353 v_ruls IN Vrules,
354 uappid IN NUMBER,
355 respid IN NUMBER,
356 nsegs_out OUT nocopy NUMBER,
357 segfmts OUT nocopy SegFormats,
358 segstats OUT nocopy VARCHAR2,
359 tabcols OUT nocopy TabColArray,
360 tabcoltypes OUT nocopy CharArray,
361 v_dispvals OUT nocopy ValueArray,
362 v_vals OUT nocopy ValueArray,
363 v_ids OUT nocopy ValueIdArray,
364 v_descs OUT nocopy ValueDescArray,
365 desc_lens OUT nocopy NumberArray,
366 dvals OUT nocopy DerivedVals,
367 dquals OUT nocopy Qualifiers,
368 errsegn OUT nocopy NUMBER) RETURN NUMBER;
369
370 /* ------------------------------------------------------------------------ */
371 -- General utility functions
372 --
373
374 FUNCTION to_stringarray(catsegs IN VARCHAR2,
375 sepchar in VARCHAR2,
376 segs OUT nocopy StringArray) RETURN NUMBER;
377
378 FUNCTION from_stringarray(nsegs IN NUMBER,
379 segs IN StringArray,
380 sepchar IN VARCHAR2) RETURN VARCHAR2;
381
382 FUNCTION to_stringarray2(catsegs IN VARCHAR2,
383 sepchar in VARCHAR2,
384 segs OUT nocopy StringArray) RETURN NUMBER;
385
386 FUNCTION from_stringarray2(nsegs IN NUMBER,
387 segs IN StringArray,
388 sepchar IN VARCHAR2) RETURN VARCHAR2;
389
390 PROCEDURE x_inrange_clause(valstr IN VARCHAR2,
391 valtype IN VARCHAR2,
392 mincol IN VARCHAR2,
393 maxcol IN VARCHAR2);
394
395 FUNCTION select_clause(colname IN VARCHAR2,
396 coltype IN VARCHAR2,
397 v_component IN BINARY_INTEGER,
398 vs_fmt IN VARCHAR2,
399 vs_len IN NUMBER) RETURN VARCHAR2;
400
401 PROCEDURE x_compare_clause(coltype IN VARCHAR2,
402 colname IN VARCHAR2,
403 char_val IN VARCHAR2,
404 v_component IN BINARY_INTEGER,
405 vs_fmt IN VARCHAR2,
406 vs_len IN NUMBER);
407
408 FUNCTION x_dsql_execute RETURN NUMBER;
409
410 FUNCTION x_dsql_select_one(returned_column OUT nocopy VARCHAR2) RETURN NUMBER;
411
412 FUNCTION x_dsql_select(n_selected_cols IN NUMBER,
413 returned_columns OUT nocopy StringArray) RETURN NUMBER;
414
415 FUNCTION stored_date_format(flex_data_type IN VARCHAR2,
416 string_length IN NUMBER) RETURN VARCHAR2;
417
418 FUNCTION isa_number(teststr IN VARCHAR2,
419 outnum OUT nocopy NUMBER) RETURN BOOLEAN;
420
421 FUNCTION init_globals RETURN BOOLEAN;
422
423
424 /* ------------------------------------------------------------------------ */
425
426 PROCEDURE add_sql_string(sql_statement IN VARCHAR2);
427
428 -- For debugging. Gets sql strings used for dynamic sql statements.
429 --
430 FUNCTION get_nsql_internal RETURN NUMBER;
431
432 FUNCTION get_sql_internal(statement_number IN NUMBER,
433 statement_portion IN NUMBER DEFAULT 1) RETURN VARCHAR2;
434
435 -- Gets and adds to additional string of debug information.
436 --
437 PROCEDURE add_debug(p_debug_string IN VARCHAR2,
438 p_debug_mode IN VARCHAR2 DEFAULT 'STATEMENT');
439
440 PROCEDURE set_debugging(p_debug_mode IN VARCHAR2);
441
442 FUNCTION get_debug_internal(string_n IN NUMBER) RETURN VARCHAR2;
443
444 /* New client side debug functions */
445 PROCEDURE x_get_nsql(x_nsql OUT nocopy NUMBER);
446
447 PROCEDURE x_get_sql_npiece(p_sql_num IN NUMBER,
448 x_npiece OUT nocopy NUMBER);
449
450 PROCEDURE x_get_sql_piece(p_sql_num IN NUMBER,
454 PROCEDURE x_get_ndebug(x_ndebug OUT nocopy NUMBER);
451 p_piece_num IN NUMBER,
452 x_sql_piece OUT nocopy VARCHAR2);
453
455
456 PROCEDURE x_get_debug(p_debug_num IN NUMBER,
457 x_debug OUT nocopy VARCHAR2);
458
459
460 END fnd_flex_server1;
[Home] [Help]