1 PACKAGE qa_validation_api AS
2 /* $Header: qltvalb.pls 120.6.12010000.2 2008/10/17 06:32:15 skolluku ship $ */
3
4 --
5 -- Type Definitions
6 --
7
8 --
9 -- removed all the default values for record elements
10 -- per coding standard for better performance.
11 -- jezheng
12 -- Wed Nov 27 15:15:43 PST 2002
13 --
14
15 TYPE InfoRecord IS RECORD (
16 id NUMBER,
17 validation_flag VARCHAR2(100) ,
18 treated BOOLEAN);
19
20 TYPE ErrorRecord IS RECORD (
21 element_id NUMBER,
22 error_code NUMBER);
23
24 TYPE DependencyRecord IS RECORD (
25 element_id NUMBER,
26 parent NUMBER);
27
28 TYPE ElementRecord IS RECORD (
29 id NUMBER,
30 value VARCHAR2(2000),
31 validation_flag VARCHAR2(100));
32
33 TYPE RowRecord IS RECORD (
34 plan_id NUMBER,
35 org_id NUMBER,
36 spec_id NUMBER,
37 user_id NUMBER);
38
39 TYPE ConditionRecord IS RECORD (
40 operator NUMBER,
41 low_value_other VARCHAR2(150),
42 high_value_other VARCHAR2(150),
43 low_value_lookup NUMBER,
44 high_value_lookup NUMBER);
45
46 TYPE ResultRecord IS RECORD (
47 element_id NUMBER,
48 canonical_value VARCHAR2(2000),
49 id NUMBER,
50 actual_datatype NUMBER,
51 message VARCHAR2(2000));
52
53 -- Bug 5150287. SHKALYAN 02-Mar-2006.
54 -- Increased the column width of message from 500 to 2500.
55
56 Type MessageRecord IS RECORD (
57 element_id NUMBER,
58 action_type NUMBER,
59 message VARCHAR2(2500));
60
61 TYPE ElementInfoArray IS TABLE OF InfoRecord INDEX BY BINARY_INTEGER;
62 TYPE ElementsArray IS TABLE OF ElementRecord INDEX BY BINARY_INTEGER;
63 TYPE ErrorArray IS TABLE OF ErrorRecord INDEX BY BINARY_INTEGER;
64 TYPE ResultRecordArray IS TABLE OF ResultRecord INDEX BY BINARY_INTEGER;
65 TYPE DependencyArray IS TABLE OF DependencyRecord INDEX BY BINARY_INTEGER;
66 TYPE MessageArray IS TABLE OF MessageRecord INDEX BY BINARY_INTEGER;
67
68 --
69 -- Constant Definition
70 --
71
72 not_enabled_error NUMBER := -1;
73 no_value_error NUMBER := -2;
74 mandatory_error NUMBER := -3;
75 not_revision_controlled_error NUMBER := -4;
76 mandatory_revision_error NUMBER := -5;
77 no_values_error NUMBER := -6;
78 keyflex_error NUMBER := -7;
79 id_not_found_error NUMBER := -8;
80 spec_limit_error NUMBER := -9;
81 immediate_action_error NUMBER := -10;
82 lower_limit_error NUMBER := -11;
83 upper_limit_error NUMBER := -12;
84 value_not_in_sql_error NUMBER := -13;
85 sql_validation_error NUMBER := -14;
86 date_conversion_error NUMBER := -15;
87 data_type_error NUMBER := -16;
88 number_conversion_error NUMBER := -17;
89 not_locator_controlled_error NUMBER := -18;
90 no_data_found_error NUMBER := -19;
91 item_keyflex_error NUMBER := -20;
92 comp_item_keyflex_error NUMBER := -21;
93 locator_keyflex_error NUMBER := -22;
94 comp_locator_keyflex_error NUMBER := -23;
95 invalid_number_error NUMBER := -24;
96 invalid_date_error NUMBER := -25;
97 spec_error NUMBER := -26;
98 reject_an_entry_error NUMBER := -27;
99
100 -- Added the following fields to be used for Bill_Reference,Routing_Reference,To_locator
101 -- Key FlexField error messages. Bug 2686970.suramasw Wed Nov 27 05:12:52 PST 2002.
102
103 bill_reference_keyflex_error NUMBER := -28;
104 rtg_reference_keyflex_error NUMBER := -29;
105 to_locator_keyflex_error NUMBER := -30;
106
107 -- End Bug 2686970.
108
109
110 -- Bug 3679762.Added the following field to be used for "missing assign a value target column"
111 -- error message.This constant will be used in qa_ss_results.populate_message_table(qltssreb.plb),
112 -- qa_results_pub.populate_message_table (qltpresb.plb) and qa_validation_api.init_message_map(qltvalb.plb)
113 -- procedures as index to message array.It is also used in qa_validation_api.perform_immediate_actions
114 -- (qltvalb.plb) as error code.
115 -- srhariha. Wed Jun 16 06:54:06 PDT 2004.
116
117 missing_assign_column NUMBER := -31;
118
119 ok NUMBER := 0;
120 unknown_error NUMBER := -9999;
121
122
123 -- The following constants are defined to let the caller
124 -- of validate_row to sxpecify what level of validation
125 -- must be performed for an element.
126 --
127 -- For example:
128 --
129 -- row_elements_array(element id).validation_flag :=
130 -- background_element || action_fired;
131 --
132 -- This says that this element is an invalid element but is a
133 -- part of a background transaction and all the immediate actions
134 -- are already fired.
135 --
136 -- As a result, the validation routine will validate the element
137 -- but will not perform any mandatory check on it, also will not
138 -- not do any immediate actions processing.
139 --
140 -- Please note that by default all elements are invalid.
141 -- In other words the caller does not have to set any flag
142 -- if he wants is a full validation on every element.
143
144
145 valid_element VARCHAR2(100) := 'context';
146 invalid_element VARCHAR2(100) := 'invalid';
147 background_element VARCHAR2(100) := 'background';
148 action_fired VARCHAR2(100) := 'fired';
149 id_derived VARCHAR2(100) := 'id_given';
150
151
152
153 -- rkaza. bug 3220767. 10/29/2003. Commenting the following block.
154 -- When coming from ss, we have to do the tz conversion in the middle tier.
155 -- because server side initializations required for tz conversion to work
156 -- on server side would not be done by ss tech stack as in forms.
157
158 /*
159 client_timezone VARCHAR2(100) := 'client_tz';
160 server_timezone VARCHAR2(100) := 'server_tz';
161 */
162
163
164 --
165 -- Subroutines
166 --
167
168 INVALID_DATE EXCEPTION;
169 INVALID_DATE_FORMAT EXCEPTION;
170
171 PRAGMA EXCEPTION_INIT (INVALID_DATE, -1858);
172 PRAGMA EXCEPTION_INIT (INVALID_DATE_FORMAT, -1861);
173
174 --
175 -- 12.1 QWB Usability Improvements
176 -- Added a new parameter p_ssqr_operation
177 -- to indicate if the method is called through
178 -- the QWB application, in which case the
179 -- Online actions are not to be fired
180 --
181 FUNCTION validate_row (
182 plan_id IN NUMBER,
183 spec_id IN NUMBER,
184 org_id IN NUMBER,
185 user_id IN NUMBER,
186 transaction_number IN NUMBER,
187 transaction_id IN NUMBER,
188 return_results_array OUT NOCOPY ResultRecordArray,
189 message_array OUT NOCOPY MessageArray,
190 row_elements IN OUT NOCOPY ElementsArray,
191 p_ssqr_operation IN NUMBER DEFAULT NULL)
192 RETURN ErrorArray;
193
194
195 --
196 -- Bug 3402251. To fix this bug, it is required the row_elements
197 -- changed to an IN OUT NOCOPY param.
198 -- bso Mon Feb 9 21:38:43 PST 2004
199 --
200 --
201 -- 12.1 QWB Usabiltiy Improvements
202 -- Added a new parameter org_id for
203 -- online validations
204 --
205 FUNCTION validate_element (
206 row_elements IN OUT NOCOPY ElementsArray,
207 row_record IN RowRecord,
208 element_id IN NUMBER,
209 org_id IN NUMBER,
210 result_holder IN OUT NOCOPY ResultRecord)
211 RETURN ErrorArray;
212
213 FUNCTION no_errors (error_Array IN ErrorArray)
214 RETURN BOOLEAN;
215
216 FUNCTION get_error_message(error_code IN NUMBER)
217 RETURN VARCHAR2;
218
219 -- Bug 2427337. new function introduced
220 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
221 FUNCTION validate_comment (value IN VARCHAR2, result_holder IN OUT NOCOPY ResultRecord)
222 RETURN NUMBER;
223
224
225 --
226 -- R12 Project MOAC 4637896
227 -- Exposing several useful procedures for modularization.
228 -- bso Sat Oct 1 16:03:53 PDT 2005
229 --
230
231 --
232 -- Convert a canonical @-separated result string into the
233 -- internal validation API ElementsArray format.
234 --
235 FUNCTION result_to_array(
236 p_result IN VARCHAR2)
237 RETURN qa_validation_api.ElementsArray;
238
239
240 --
241 -- Convert a canonical @-separated ID string and update
242 -- an existing validation API ElementsArray with the new
243 -- data. ID strings are simply IDs for certain hardcoded
244 -- elements where the ID value is already known so that
245 -- it is more efficient to not re-validate.
246 --
247 FUNCTION id_to_array(
248 p_result IN VARCHAR2,
249 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray)
250 RETURN qa_validation_api.ElementsArray;
251
252
253 --
254 -- Set every element to have action fired flag on
255 -- indicating online action has already been fired
256 -- by the UI and no need to be refired during validation.
257 -- In addition transaction type element is set to valid.
258 --
259 PROCEDURE set_validation_flag(
260 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray);
261
262
263 --
264 -- If validation is to be done for a transaction, then
265 -- set the context elements to valid. In addition, if
266 -- the transaction is a background transaction, then
267 -- set each element to have background_element flag.
268 -- Finally, these elements are set to valid by default:
269 --
270 -- transaction type, lot number, serial number
271 --
272 -- Caller chould supply p_plan_transaction_id if possible.
273 -- For backward compatibility, one can also specify
274 -- p_plan_id + p_transaction_number, which is not 100%
275 -- accurate because one be certain a plan is a background
276 -- plan only by a given plan_transaction_id.
277 --
278 PROCEDURE set_validation_flag_txn(
279 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
280 p_plan_id NUMBER,
281 p_transaction_number NUMBER,
282 p_plan_transaction_id NUMBER);
283
284 -- End R12 Project MOAC 4637896
285
286 -- 12.1 QWB Usability Improvements
287 -- Procedure to De-reference the values for the HC elements
288 -- that depended on Non Quality tables for their values
289 PROCEDURE build_deref_string(p_plan_id IN NUMBER,
290 p_collection_id IN NUMBER,
291 p_occurrence IN NUMBER,
292 p_charid_string OUT NOCOPY VARCHAR2,
293 p_values_string OUT NOCOPY VARCHAR2);
294
295 -- 12.1 QWB Usability Improvements
296 -- Method to do the online validations
297 -- This method would also make a call to the API
298 -- process_dependent_elements to do the dependent
299 -- elelemts processing
300 --
301 PROCEDURE perform_ssqr_validation (p_plan_id IN VARCHAR2,
302 p_org_id IN VARCHAR2,
303 p_spec_id IN VARCHAR2,
304 p_user_id IN VARCHAR2 DEFAULT NULL,
305 p_element_id IN VARCHAR2,
306 p_input_value IN VARCHAR2,
307 result_string IN VARCHAR2,
308 id_string IN VARCHAR2,
309 normalized_attr OUT NOCOPY VARCHAR2,
310 normalized_id_val OUT NOCOPY VARCHAR2,
311 message OUT NOCOPY VARCHAR2,
312 dependent_elements OUT NOCOPY VARCHAR2,
313 disable_enable_flag_list OUT NOCOPY VARCHAR2,
314 disabled_dep_elem_vo_attr_lst OUT NOCOPY VARCHAR2);
315
316 -- 12.1 QWB Usability Improvements
317 -- method to get the sql string for ResultExportVO
318 FUNCTION get_export_vo_sql (p_plan_id in NUMBER) Return VARCHAR2;
319
320 -- 12.1 QWB Usability Improvements
321 -- Procedure to fire the online actions
322 -- on elements that have trigers defined
323 -- for the value not entered conditition
324 --
325 FUNCTION processNotEnteredActions (p_plan_id IN NUMBER,
326 p_spec_id IN NUMBER,
327 p_ssqr_operation IN NUMBER DEFAULT NULL,
328 p_row_elements IN OUT NOCOPY ElementsArray,
329 p_return_results_array IN OUT NOCOPY ResultRecordArray,
330 message_array OUT NOCOPY MessageArray)
331 RETURN ErrorArray;
332
333 --
334 -- Bug 7491253. 12.1.1 FP for Bug 6599571.Made this procedure public to
335 -- access it from qltdactb
336 -- skolluku
337 --
338 FUNCTION get_normalized_id (element_id IN NUMBER, value IN VARCHAR2, x_org_id IN NUMBER)
339 RETURN NUMBER;
340 END qa_validation_api;
[Home] [Help]