1 PACKAGE BODY qa_mqa_plan_elements AS
2 /* $Header: qacharb.pls 120.2 2006/02/05 18:06:27 bso noship $ */
3
4
5 --
6 -- Removed DEFAULT clause for GSCC compliance
7 -- Before removal
8 -- x_spec_id IN NUMBER DEFAULT -1
9 -- After removal
10 -- x_spec_id IN NUMBER
11 -- rkunchal
12 --
13
14 --
15 -- changed x_spec_id default to null instead of -1
16 -- for performance purpose
17 -- jezheng
18 -- Wed Nov 27 13:48:52 PST 2002
19 --
20
21 FUNCTION get_elements(x_plan_id IN NUMBER, x_spec_id IN NUMBER)
22 RETURN PlanElementRefCursor IS
23 --
24 -- Get all plan elements info for a particular collection plan.
25 -- bso Fri May 5 17:29:39 PDT 2000
26 --
27 x_ref PlanElementRefCursor;
28
29 BEGIN
30 -- When user doesnt enter any spec, a -1 is passed in..........not a null.
31
32 -- rkaza bugs 2753703, 2767550. 01/27/2003. Refer to bug texts for more
33 -- info. Previously the where clause used to have 3 outer joins qa_chars,
34 -- qa_spec_chars, qa_specs and the user entered spec value. It gave
35 -- incorrect results. For Eg: 1) Either the char is removed if no spec matches. 2) More
36 -- than one row results for the same char if the char is present in some
37 -- other spec other than the one specified by user. 3) The char is retained
38 -- if no spec matches but some spec limits found in qa_spec_chars are also
39 -- retained when they have to be made null. In order to correct this
40 -- behavior the qa_spec_chars and qa_specs need to combined into one without
41 -- an outer join between them based on common_spec. Since we didnt want to
42 -- create a new view for a bug fix, we have combined them in the from clause
43 -- itself. Also we put the filtering condition for the user entered spec
44 -- value in the same.
45
46 --
47 -- See Bug 2624112
48 -- The decimal precision for a number type collection
49 -- element is to be configured at plan level.
50 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
51 --
52 -- Modified the query again for Global Specifications Enhancements
53 --
54 -- rkunchal
55 --
56
57 -- Modified to include Read Only Flag for Collection Plan Element
58 -- saugupta
59
60
61 --
62 -- Bug 3257220
63 -- Corrected precision problem. The previous SQL decode
64 -- for decimal_precision is not correct. Changed to nvl.
65 -- bso Thu Nov 13 15:59:14 PST 2003
66 --
67
68 --
69 -- Bug 4958730. SQL Repository fix for SQL ID 15007756
70 -- Rewrite SQL by using a more streamlined get_spec_limit
71 -- PL/SQL function. Also reformatted.
72 -- bso Sun Feb 5 17:48:31 PST 2006
73 --
74 OPEN x_ref FOR
75
76 SELECT
77 qc.char_id,
78 qc.name,
79 qp.prompt,
80 qc.data_entry_hint,
81 qc.datatype,
82 qc.display_length,
83 nvl(qp.decimal_precision,
84 nvl(qc.decimal_precision, 12)) decimal_precision,
85 qp.default_value,
86 decode(qp.values_exist_flag,
87 1, 1,
88 decode(qc.sql_validation_string, null, 2, 1)) lov_flag,
89 qp.mandatory_flag,
90 qp.displayed_flag,
91 qp.read_only_flag,
92 qa_mqa_plan_elements.get_spec_limit(
93 qc.char_id,
94 qc.datatype,
95 qscqs.char_id,
96 qc.uom_code,
97 qp.uom_code,
98 qscqs.uom_code,
99 qc.decimal_precision,
100 qp.decimal_precision,
101 qc.target_value,
102 qscqs.target_value) target_value,
103 qa_mqa_plan_elements.get_spec_limit(
104 qc.char_id,
105 qc.datatype,
106 qscqs.char_id,
107 qc.uom_code,
108 qp.uom_code,
109 qscqs.uom_code,
110 qc.decimal_precision,
111 qp.decimal_precision,
112 qc.upper_spec_limit,
113 qscqs.upper_spec_limit) upper_spec_limit,
114 qa_mqa_plan_elements.get_spec_limit(
115 qc.char_id,
116 qc.datatype,
117 qscqs.char_id,
121 qc.decimal_precision,
118 qc.uom_code,
119 qp.uom_code,
120 qscqs.uom_code,
122 qp.decimal_precision,
123 qc.lower_spec_limit,
124 qscqs.lower_spec_limit) lower_spec_limit,
125 qa_mqa_plan_elements.get_spec_limit(
126 qc.char_id,
127 qc.datatype,
128 qscqs.char_id,
129 qc.uom_code,
130 qp.uom_code,
131 qscqs.uom_code,
132 qc.decimal_precision,
133 qp.decimal_precision,
134 qc.upper_reasonable_limit,
135 qscqs.upper_reasonable_limit) upper_reasonable_limit,
136 qa_mqa_plan_elements.get_spec_limit(
137 qc.char_id,
138 qc.datatype,
139 qscqs.char_id,
140 qc.uom_code,
141 qp.uom_code,
142 qscqs.uom_code,
143 qc.decimal_precision,
144 qp.decimal_precision,
145 qc.lower_reasonable_limit,
146 qscqs.lower_reasonable_limit) lower_reasonable_limit,
147 qa_mqa_plan_elements.get_spec_limit(
148 qc.char_id,
149 qc.datatype,
150 qscqs.char_id,
151 qc.uom_code,
152 qp.uom_code,
153 qscqs.uom_code,
154 qc.decimal_precision,
155 qp.decimal_precision,
156 qc.upper_user_defined_limit,
157 qscqs.upper_user_defined_limit) upper_user_defined_limit,
158 qa_mqa_plan_elements.get_spec_limit(
159 qc.char_id,
160 qc.datatype,
161 qscqs.char_id,
162 qc.uom_code,
163 qp.uom_code,
164 qscqs.uom_code,
165 qc.decimal_precision,
166 qp.decimal_precision,
167 qc.lower_user_defined_limit,
168 qscqs.lower_user_defined_limit) lower_user_defined_limit
169 FROM
170 qa_chars qc,
171 qa_plan_chars qp,
172 (SELECT
173 qsc.CHAR_ID,
174 qsc.ENABLED_FLAG,
175 qsc.TARGET_VALUE,
176 qsc.UPPER_SPEC_LIMIT,
177 qsc.LOWER_SPEC_LIMIT,
178 qsc.UPPER_REASONABLE_LIMIT,
179 qsc.LOWER_REASONABLE_LIMIT,
180 qsc.UPPER_USER_DEFINED_LIMIT,
181 qsc.LOWER_USER_DEFINED_LIMIT,
182 qsc.UOM_CODE
183 FROM
184 qa_spec_chars qsc,
185 qa_specs qs
186 WHERE
187 qsc.spec_id = qs.common_spec_id AND
188 qs.spec_id = x_spec_id) QscQs
189 WHERE
190 qp.plan_id = x_plan_id AND
191 qp.enabled_flag = 1 AND
192 qc.char_id = qp.char_id AND
193 qc.char_id = QscQs.char_id (+)
194 ORDER BY qp.prompt_sequence;
195
196 RETURN x_ref;
197
198 END get_elements;
199
200
201 FUNCTION no_action_triggers(x_plan_id IN NUMBER, x_char_id IN NUMBER)
202 RETURN BOOLEAN IS
203 --
204 -- Simple function to decide whether there will be some action triggers.
205 --
206 result BOOLEAN;
207 dummy NUMBER;
208 CURSOR c IS
209 SELECT plan_char_action_trigger_id
210 FROM qa_plan_char_action_triggers
211 WHERE plan_id = x_plan_id AND
212 char_id = x_char_id;
213 BEGIN
214 OPEN c;
215 FETCH c INTO dummy;
216 result := c%NOTFOUND;
217 CLOSE c;
218
219 RETURN result;
220 END no_action_triggers;
221
222
223 FUNCTION get_online_action_triggers(x_plan_id IN NUMBER, x_char_id IN NUMBER)
224 RETURN ActionTriggerRefCursor IS
225 --
226 -- Return a ref cursor that loops through all online (aka immediate)
227 -- action triggers of a plan element.
228 -- bso Fri May 5 17:29:30 PDT 2000
229 --
230 x_ref ActionTriggerRefCursor;
231 BEGIN
232 --
233 -- Since this SQL is extremely complicated. We may as well do a
234 -- simple existence query to find out if we need to really do it.
235 -- Return a no-row-selected query if there is nothing to be found.
236 --
237 IF no_action_triggers(x_plan_id, x_char_id) THEN
238 OPEN x_ref FOR
239 SELECT
240 null plan_char_action_trigger_id,
241 null plan_char_action_id,
242 null trigger_sequence,
243 null operator,
244 null low_value_lookup,
245 null high_value_lookup,
246 null low_value_other,
247 null high_value_other,
248 null action_id,
249 null message,
250 null assigned_char_id,
251 null assign_type,
252 null online_flag
253 FROM dual
254 WHERE 1 = 2;
255 RETURN x_ref;
256 END IF;
257
258 OPEN x_ref FOR
259 --
260 -- The first select selects all triggers with online actions eliminating
261 -- all deferred actions. Unfortunately, this also eliminates those
262 -- triggers that have no online actions, which we still need, hence the
263 -- union all statement. May Hari Seldon help me find a more elegant
264 -- solution through the force of psychohistory. (This SQL actually has
265 -- a pretty good performance. All accesses are done by indices.)
266 -- bso
267 --
268 SELECT
269 qpcat.plan_char_action_trigger_id,
270 qpca.plan_char_action_id,
274 qpcat.high_value_lookup,
271 qpcat.trigger_sequence,
272 qpcat.operator,
273 qpcat.low_value_lookup,
275 qpcat.low_value_other,
276 qpcat.high_value_other,
277 qpca.action_id,
278 qpca.message,
279 qpca.assigned_char_id,
280 qpca.assign_type,
281 1 online_flag
282 FROM
283 qa_plan_char_action_triggers qpcat,
284 qa_plan_char_actions qpca,
285 qa_actions qa
286 WHERE
287 qpcat.plan_id = x_plan_id AND
288 qpcat.char_id = x_char_id AND
289 qpcat.plan_char_action_trigger_id =
290 qpca.plan_char_action_trigger_id AND
291 qpca.action_id = qa.action_id AND
292 qa.online_flag = 1
293 UNION ALL
294 --
295 -- This select gets all the triggers with either no actions or only
296 -- deferred actions.
297 --
298 SELECT
299 qpcat.plan_char_action_trigger_id,
300 -1, -- no action in this part
301 qpcat.trigger_sequence,
302 qpcat.operator,
303 qpcat.low_value_lookup,
304 qpcat.high_value_lookup,
305 qpcat.low_value_other,
306 qpcat.high_value_other,
307 -1 action_id,
308 null, -- message, null in this case
309 -1, -- action details, -1 in this case
310 null, -- action details, null in this case
311 1 -- online flag, always 1 in this case
312 FROM
313 qa_plan_char_action_triggers qpcat
314 WHERE
315 qpcat.plan_id = x_plan_id AND
316 qpcat.char_id = x_char_id AND
317 NOT EXISTS
318 (SELECT
319 1
320 FROM
321 qa_plan_char_actions qpca,
322 qa_actions qa
323 WHERE
324 qpca.plan_char_action_trigger_id =
325 qpcat.plan_char_action_trigger_id AND
326 qpca.action_id = qa.action_id AND
327 qa.online_flag = 1)
328 ORDER BY 3, 1;
329 --
330 -- A PL/SQL bug prevents me from using the following. It
331 -- complains that plan_char_action_trigger_id appears in more
332 -- than one table and should use qualifier. This is allowed
333 -- in direct SQL/Plus. Since order by always refers to the
334 -- output column position, there should not be any need for
335 -- explicit qualifiers. Using integer column position.
336 -- bso
337 --
338 -- order by trigger_sequence, plan_char_action_trigger_id;
339 RETURN x_ref;
340 END get_online_action_triggers;
341
342
343 FUNCTION get_action_tokens(x_plan_char_action_id IN NUMBER)
344 RETURN ActionTokenRefCursor IS
345 --
346 -- Return a ref cursor that loops through all action tokens
347 -- given an action of an action trigger.
348 -- bso Fri May 5 17:29:57 PDT 2000
349 --
350 x_ref ActionTokenRefCursor;
351 BEGIN
352 OPEN x_ref FOR
353 SELECT token_name, char_id
354 FROM qa_plan_char_action_outputs
355 WHERE plan_char_action_id = x_plan_char_action_id;
356 RETURN x_ref;
357 END get_action_tokens;
358
359
360 FUNCTION get_plan_name(x_plan_id IN NUMBER) RETURN VARCHAR2 IS
361 --
362 -- Return a plan name given a plan ID.
363 --
364 x_name qa_plans.name%TYPE;
365 BEGIN
366 SELECT name INTO x_name
367 FROM qa_plans
368 WHERE plan_id = x_plan_id;
369
370 RETURN x_name;
371 END get_plan_name;
372
373
374 PROCEDURE get_spec_type (p_plan_id IN NUMBER, x_spec_type OUT NOCOPY VARCHAR2,
375 x_spec_type_name OUT NOCOPY VARCHAR2) IS
376
377 --
378 -- Return a spec type and spec type name given a plan ID.
379 --
380 x_type qa_plans.spec_assignment_type%TYPE;
381 x_type_name VARCHAR2(30);
382
383 BEGIN
384 SELECT spec_assignment_type INTO x_type
385 FROM qa_plans
386 WHERE plan_id = p_plan_id;
387
388 SELECT meaning into x_spec_type_name
389 FROM mfg_lookups
390 WHERE lookup_type = 'QA_SPEC_TYPE'
391 AND lookup_code = x_type;
392
393 x_spec_type := to_char(x_type);
394
395 END get_spec_type;
396
397 --
398 -- For Specifications Enhancements
399 -- rkunchal
400 --
401
402 FUNCTION get_spec_limit(plan_char_uom VARCHAR2, spec_char_uom VARCHAR2,
403 decimal_precision NUMBER, value NUMBER)
404 RETURN NUMBER IS
405 return_val NUMBER;
406 BEGIN
407
408 IF value IS NULL THEN
409 RETURN NULL;
410 END IF;
411
412 IF plan_char_uom <> spec_char_uom THEN
413 return_val := INV_CONVERT.INV_UM_CONVERT(null,
414 decimal_precision,
415 value,
416 spec_char_uom,
417 plan_char_uom,
418 null,
419 null);
420 ELSE
421 return_val := value;
422 END IF;
423
424 RETURN return_val;
425 END get_spec_limit;
426
427 -- Bug 3288391. The function added for returning specification limits for
428 -- date and datetime elements
429 -- saugupta Thu, 11 Dec 2003 22:07:25 -0800 PDT
430 FUNCTION get_spec_limit(plan_char_uom VARCHAR2, spec_char_uom VARCHAR2,
431 decimal_precision NUMBER, x_value VARCHAR2)
432 RETURN VARCHAR2 IS
433 BEGIN
434 RETURN x_value;
435 END get_spec_limit;
436
437
438 --
439 -- Bug 4958730. SQL Repository fix for SQL ID 15007756
440 -- requires a rewrite of the above "get_spec_limit" function.
441 -- We will take in raw IDs and data from the SQL (see
442 -- java/util/ContextElementTable.java) and perform the
443 -- decode and nvl here instead.
444 --
445 -- bso Sun Feb 5 17:30:58 PST 2006
446 --
447 FUNCTION get_spec_limit(
448 p_char_id NUMBER,
449 p_datatype NUMBER,
450 p_spec_char_id NUMBER,
451 p_qc_uom_code VARCHAR2,
452 p_qpc_uom_code VARCHAR2,
453 p_qsc_uom_code VARCHAR2,
454 p_qc_dec_prec NUMBER,
455 p_qpc_dec_prec NUMBER,
456 p_qc_spec_limit VARCHAR2,
457 p_qsc_spec_limit VARCHAR2) RETURN VARCHAR2 IS
458
459 l_spec_limit qa_chars.target_value%TYPE;
460
461 BEGIN
462 IF p_spec_char_id IS NULL THEN
463 l_spec_limit := p_qc_spec_limit;
464 ELSE
465 l_spec_limit := p_qsc_spec_limit;
466 END IF;
467
468 IF p_datatype <> qa_ss_const.number_datatype THEN
469 --
470 -- No UOM Conversion needed. Simply return the spec limit.
471 --
472 RETURN l_spec_limit;
473 END IF;
474
475 --
476 -- Perform UOM Conversion by calling the original function.
477 --
478 RETURN get_spec_limit(
479 plan_char_uom => nvl(p_qpc_uom_code, p_qc_uom_code),
480 spec_char_uom => nvl(p_qsc_uom_code, p_qc_uom_code),
481 decimal_precision => nvl(p_qpc_dec_prec, nvl(p_qc_dec_prec, 12)),
482 value => to_number(l_spec_limit));
483
484 END get_spec_limit;
485
486
487 END qa_mqa_plan_elements;
[Home] [Help]