1 PACKAGE fnd_flex_keyval AUTHID CURRENT_USER AS
2 /* $Header: AFFFKVLS.pls 120.1.12010000.1 2008/07/25 14:14:08 appldev ship $ */
3
4
5 /* ------------------------------------------------------------------------ */
6 /* OVERVIEW */
7 /* */
8 /* These key flexfields server validation API functions are a */
9 /* low level interface to key flexfields validation. They are */
10 /* designed to allow access to all the flexfields functionality, */
11 /* and to allow the user to get only the information they need in */
12 /* return. Because of their generality, these functions are more */
13 /* difficult to use than those in the FND_FLEX_EXT package. We */
14 /* strongly suggest using the functions in FND_FLEX_EXT if at */
15 /* all possible. */
16 /* */
17 /* The main functions in this package are validate_segs() and */
18 /* validate_ccid(). These functions are called with either the */
19 /* key flexfield segments or combination id, respecitvely. They */
20 /* look up or create the desired combination and return TRUE if */
21 /* everything is ok, or FALSE on error. The results and/or error */
22 /* messages are not returned directly, but rather are stored in */
23 /* PLSQL package globals whose contents can be accessed by the */
24 /* remaining functions in this package. The global variables are */
25 /* reset upon each call to validate_segs() or validate_ccid() so */
26 /* the calling function must get all needed results before passing */
27 /* in the next combination. */
28 /* */
29 /* The global variable access functions can be grouped into status */
30 /* functions (is_valid, is_secured, value_error, unsupported_error, */
31 /* and serious_error), error message functions (error_message, */
32 /* encoded_error_message, and error_segment), and combination */
33 /* information functions (the remaining ones). The status functions */
34 /* are allways set after each call to validate_segs() or */
35 /* validate_ccid(). The error messages are null unless the */
36 /* combination is invalid. The error_segment is null unless the */
37 /* error can be traced to a specific segment of the combination. */
38 /* */
39 /* Because each product typically only uses a subset of the */
40 /* complete flexfields functionality, we recommend that each */
41 /* application team code a server-side PLSQL function over the */
42 /* routines in this package. This allows the application to */
43 /* use only the functionality they need, and can be used to make */
44 /* a function that returns all of its output as OUT parameters */
45 /* in a single call rather than having to make multiple calls to */
46 /* retrieve the desired results. */
47 /* */
48 /* Also note that the breakup_segments(), get_delimiter() and */
49 /* concatenate_segments() functions of the FND_FLEX_EXT package */
50 /* can be used to convert between concatenated segments and a */
51 /* PLSQL table of segment values. */
52 /* ------------------------------------------------------------------------ */
53
54
55 /* ------------------------------------------------------------------------ */
56 /* VALIDATE_SEGS: */
57 /* Finds combination from given segment values. */
58 /* Segments are passed in as a concatenated string in increasing */
59 /* order of segment_number (display order). */
60 /* Operation is one of: */
61 /* 'FIND_COMBINATION' - Combination must already exist. */
62 /* 'CREATE_COMBINATION' - Combination is created if doesn't exist. */
63 /* 'CREATE_COMB_NO_AT' - same as create_combination but does not */
64 /* use an autonomous transaction. */
65 /* 'CHECK_COMBINATION' - Checks if combination valid, doesn't create.*/
66 /* 'DEFAULT_COMBINATION' - Returns minimal default combination. */
67 /* 'CHECK_SEGMENTS' - Validates segments individually. */
68 /* */
69 /* If validation date is NULL checks all cross-validation rules. */
70 /* Returns TRUE if combination valid, or FALSE and sets error message */
71 /* on server if invalid. */
72 /* */
73 /* The defaulted arguments listed after combination_id are all */
74 /* optional. Use the default values if you do not want any special */
75 /* functionality. Defaulted argument descriptions: */
76 /* - user_id, resp_id, and resp_appl_id identify the user and */
77 /* responsibility. They default to the values from FND_GLOBAL */
78 /* which are set by the navigator form if this database session */
79 /* underlies a form on the client or by the concurrent manager if */
80 /* this package is in a database session started through the */
81 /* concurrent manager. */
82 /* - Values_or_ids indicates whether input segments are values ('V') */
83 /* hidden ids ('I'). If values are input the function expects one */
84 /* value for every displayed segment, whereas if ids are input the */
85 /* function expects one id for each enabled segment whether or not */
86 /* the segment is displayed. */
87 /* - displayable is used to specify which segments are displayed. */
88 /* This argument allows the user to not display segments that would */
89 /* otherwise be displayed based on the flexfield definition. */
90 /* - data_set specifies the effective flexfield structure number to */
91 /* use when selecting or inserting into the combinations table. */
92 /* - vrule can be used to impose additional validation constraints */
93 /* based on flexfield qualifier values. */
94 /* - where_clause limits existing combinations based on a user- */
95 /* specified SQL where clause expression. Only use with dinsert */
96 /* FALSE. */
97 /* - get_columns specifies additional columns from the combinations */
98 /* table that are to be retrieved when a combination is found. */
99 /* - allow_nulls and allow_orphans are only checked if the operation */
100 /* is 'CHECK_SEGMENTS'. In this case, if allow_nulls is TRUE then */
101 /* required segments that are null will be considered valid. If */
102 /* allow_orphans is TRUE, then all possible dependent segment */
103 /* values be valid if the parent segment is null. */
104 /* ------------------------------------------------------------------------ */
105 FUNCTION validate_segs(operation IN VARCHAR2,
106 appl_short_name IN VARCHAR2,
107 key_flex_code IN VARCHAR2,
108 structure_number IN NUMBER,
109 concat_segments IN VARCHAR2,
110 values_or_ids IN VARCHAR2 DEFAULT 'V',
111 validation_date IN DATE DEFAULT SYSDATE,
112 displayable IN VARCHAR2 DEFAULT 'ALL',
113 data_set IN NUMBER DEFAULT NULL,
114 vrule IN VARCHAR2 DEFAULT NULL,
115 where_clause IN VARCHAR2 DEFAULT NULL,
116 get_columns IN VARCHAR2 DEFAULT NULL,
117 allow_nulls IN BOOLEAN DEFAULT FALSE,
118 allow_orphans IN BOOLEAN DEFAULT FALSE,
119 resp_appl_id IN NUMBER DEFAULT NULL,
120 resp_id IN NUMBER DEFAULT NULL,
121 user_id IN NUMBER DEFAULT NULL,
122 select_comb_from_view IN VARCHAR2 DEFAULT NULL,
123 no_combmsg IN VARCHAR2 DEFAULT NULL,
124 where_clause_msg IN VARCHAR2 DEFAULT NULL)
125 RETURN BOOLEAN;
126
127 /* ------------------------------------------------------------------------ */
128 /* VALIDATE_CCID: */
129 /* Looks up flexfield combination by its combination id. */
130 /* Returns TRUE if combination found, otherwise returns FALSE */
131 /* and sets error on the server. Checks value security rules, */
132 /* but violations do not invalidate the combination. */
133 /* The defaulted arguments listed after combination_id are all */
134 /* optional. Use the default values if you do not want any special */
135 /* functionality. Defaulted argument descriptions: */
136 /* - user_id, resp_id, and resp_appl_id identify the user and */
137 /* responsibility. They default to the values from FND_GLOBAL */
138 /* which are set by the navigator form if this database session */
139 /* underlies a form on the client or by the concurrent manager if */
140 /* this package is in a database session started through the */
141 /* concurrent manager. These parameters are used to determine if */
142 /* values are secured. Combinations with secrued values can still */
143 /* be looked up using this function without returning an error, */
144 /* but this function will note if any segments violate security. */
145 /* Use is_secured to check if security violated. */
146 /* - displayable is used to specify which segments are displayed. */
147 /* This argument allows the user to not display segments that would */
148 /* otherwise be displayed based on the flexfield definition. */
149 /* Only the displayed segments are returned. */
150 /* - data_set specifies the effective flexfield structure number to */
151 /* use when selecting or inserting into the combinations table. */
152 /* - vrule can be used to impose additional validation constraints */
153 /* based on flexfield qualifier values. */
154 /* - Get_columns specifies additional columns from the combinations */
155 /* table that are to be retrieved when a combination is found. */
156 /* - security determines whether or not to check value security. */
157 /* IGNORE - ignores value security altogether. */
158 /* CHECK - checks security, but violation is not an error. */
159 /* ENFORCE - Stop validating and return error ifs security violated.*/
160 /* ------------------------------------------------------------------------ */
161
162 FUNCTION validate_ccid(appl_short_name IN VARCHAR2,
163 key_flex_code IN VARCHAR2,
164 structure_number IN NUMBER,
165 combination_id IN NUMBER,
166 displayable IN VARCHAR2 DEFAULT 'ALL',
167 data_set IN NUMBER DEFAULT NULL,
168 vrule IN VARCHAR2 DEFAULT NULL,
169 security IN VARCHAR2 DEFAULT 'IGNORE',
170 get_columns IN VARCHAR2 DEFAULT NULL,
171 resp_appl_id IN NUMBER DEFAULT NULL,
172 resp_id IN NUMBER DEFAULT NULL,
173 user_id IN NUMBER DEFAULT NULL,
174 select_comb_from_view IN VARCHAR2 DEFAULT NULL)
175 RETURN BOOLEAN;
176
177 /* ------------------------------------------------------------------------ */
178 /* Functions for getting more details about the most recently */
179 /* validated combination. These typically do not trap errors */
180 /* related to the user not leaving enough room in destination */
181 /* strings to store the result. */
182 /* ------------------------------------------------------------------------ */
183
184 FUNCTION is_valid RETURN BOOLEAN;
185 FUNCTION is_secured RETURN BOOLEAN;
186 FUNCTION value_error RETURN BOOLEAN;
187 FUNCTION unsupported_error RETURN BOOLEAN;
188 FUNCTION serious_error RETURN BOOLEAN;
189
190 FUNCTION error_segment RETURN NUMBER;
191 FUNCTION error_message RETURN VARCHAR2;
192 FUNCTION encoded_error_message RETURN VARCHAR2;
193
194 FUNCTION new_combination RETURN BOOLEAN;
195 FUNCTION combination_id RETURN NUMBER;
196 FUNCTION segment_delimiter RETURN VARCHAR2;
197
198 /* ------------------------------------------------------------------------ */
199 /* Concatenated segment values and descriptions are only those */
200 /* displayed, and descriptions are truncated to catdesc_len. Ids are */
201 /* returned for all enabled segments whether or not they are displayed.*/
202 /* ------------------------------------------------------------------------ */
203 FUNCTION concatenated_values RETURN VARCHAR2;
204 FUNCTION concatenated_ids RETURN VARCHAR2;
205 FUNCTION concatenated_descriptions RETURN VARCHAR2;
206
207
208 FUNCTION enabled_flag RETURN BOOLEAN;
209 FUNCTION summary_flag RETURN BOOLEAN;
210
211 /* ------------------------------------------------------------------------ */
212 /* If start or end date is null => no limit. */
213 /* ------------------------------------------------------------------------ */
214 FUNCTION start_date RETURN DATE;
215 FUNCTION end_date RETURN DATE;
216
217 /* ------------------------------------------------------------------------ */
218 /* Segnum indexes all enabled segments whether or not they are displayed */
219 /* ------------------------------------------------------------------------ */
220 FUNCTION segment_count RETURN NUMBER;
221 FUNCTION segment_value(segnum IN NUMBER) RETURN VARCHAR2;
222 FUNCTION segment_id(segnum IN NUMBER) RETURN VARCHAR2;
223 FUNCTION segment_description(segnum IN NUMBER) RETURN VARCHAR2;
224 FUNCTION segment_concat_desc_length(segnum IN NUMBER) RETURN NUMBER;
225 FUNCTION segment_displayed(segnum IN NUMBER) RETURN BOOLEAN;
226 FUNCTION segment_valid(segnum IN NUMBER) RETURN BOOLEAN;
227 FUNCTION segment_column_name(segnum IN NUMBER) RETURN VARCHAR2;
228 FUNCTION segment_column_type(segnum IN NUMBER) RETURN VARCHAR2;
229
230 /* ------------------------------------------------------------------------ */
231 /* Colnum indexes column requested with the get_columns token in */
232 /* the order in which the columns were requested. (1 to N) */
233 /* ------------------------------------------------------------------------ */
234 FUNCTION column_count RETURN NUMBER;
235 FUNCTION column_value(colnum IN NUMBER) RETURN VARCHAR2;
236
237 FUNCTION qualifier_value(segqual_name IN VARCHAR2,
238 table_or_derived IN VARCHAR2 DEFAULT 'D')
239 RETURN VARCHAR2;
240
241 /* ------------------------------------------------------------------------ */
242
243 END fnd_flex_keyval;