1 PACKAGE BODY CZ_TYPES AS
2 /* $Header: cztypesb.pls 120.5 2011/05/25 13:57:23 skandhuk ship $ */
3
4 FUNCTION is_Mutex
5 (p_ps_node_id IN NUMBER,
6 p_max_selected IN NUMBER) RETURN VARCHAR2 IS
7
8 l_req_nodes_counter NUMBER;
9 l_ps_counter NUMBER;
10
11 BEGIN
12
13 IF p_max_selected=1 THEN
14 RETURN '1'; -- it's mutex
15 ELSIF p_max_selected=-1 THEN
16 RETURN '0'; -- it's not mutex
17 END IF;
18
19 -- check for the case when node with p_ps_node_id has only one child node
20 SELECT COUNT(ps_node_id) INTO l_ps_counter FROM CZ_PS_NODES
21 WHERE parent_id=p_ps_node_id AND deleted_flag='0' AND rownum<3;
22
23 -- if there is only one child node then return "mutex"
24 IF l_ps_counter=0 THEN
25
26 RETURN '0'; -- it's not mutex
27
28 -- if there is only one child node then return "mutex"
29 ELSIF l_ps_counter=1 THEN
30
31 RETURN '1'; -- it's mutex
32
33 END IF;
34
35 SELECT COUNT(ps_node_id) INTO l_req_nodes_counter FROM CZ_PS_NODES
36 WHERE parent_id=p_ps_node_id AND deleted_flag='0' AND
37 bom_required_flag='1';
38
39 IF ( l_req_nodes_counter+1 = p_max_selected ) THEN
40 RETURN '1'; -- it's mutex
41 ELSE
42 RETURN '0'; -- it's not mutex
43 END IF;
44
45 END is_Mutex;
46
47 FUNCTION is_Mutex
48 (p_ps_node_id IN NUMBER) RETURN VARCHAR2 IS
49
50 l_max_selected NUMBER;
51
52 BEGIN
53
54 SELECT maximum_selected INTO l_max_selected FROM CZ_PS_NODES
55 WHERE ps_node_id=p_ps_node_id AND deleted_flag='0';
56
57 RETURN is_Mutex(p_ps_node_id, l_max_selected);
58
59 END is_Mutex;
60
61 PROCEDURE is_Mutex
62 (p_ps_node_id IN NUMBER,
63 p_max_selected IN NUMBER,
64 x_mutex_flag OUT NOCOPY VARCHAR2) IS
65
66 BEGIN
67
68 x_mutex_flag := is_Mutex(p_ps_node_id,p_max_selected);
69
70 END is_Mutex;
71
72 PROCEDURE is_Mutex
73 (p_ps_node_id IN NUMBER,
74 x_mutex_flag OUT NOCOPY VARCHAR2) IS
75
76 l_max_selected NUMBER;
77
78 BEGIN
79
80 SELECT maximum_selected INTO l_max_selected FROM CZ_PS_NODES
81 WHERE ps_node_id=p_ps_node_id AND deleted_flag='0';
82
83 x_mutex_flag := is_Mutex(p_ps_node_id, l_max_selected);
84
85 END is_Mutex;
86
87
88 FUNCTION contains_Nested_BOM(p_ps_node_id IN NUMBER) RETURN BOOLEAN IS
89 l_flag VARCHAR2(1);
90 BEGIN
91 SELECT '1' INTO l_flag FROM CZ_PS_NODES a
92 WHERE a.parent_id=p_ps_node_id AND a.deleted_flag='0' AND
93 (
94 (a.ps_node_type IN(PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS))
95 OR
96 (a.ps_node_type=PS_NODE_TYPE_REFERENCE AND a.instantiable_flag=MANDATORY_EXPL_TYPE AND
97 EXISTS(SELECT NULL FROM CZ_PS_NODES b
98 WHERE b.devl_project_id=a.reference_id AND
99 b.deleted_flag='0' AND b.ps_node_type=PS_NODE_TYPE_BOM_MODEL))
100 )
101 AND rownum<2 ;
102 RETURN TRUE;
103 EXCEPTION
104 WHEN OTHERS THEN
105 RETURN FALSE;
106 END contains_Nested_BOM;
107
108 FUNCTION child_Nodes_Have_Quantities(p_ps_node_id IN NUMBER) RETURN BOOLEAN IS
109 l_flag VARCHAR2(1);
110 v_ps_node_id NUMBER;
111 BEGIN
112 FOR l IN(SELECT ps_node_id,reference_id,ps_node_type,maximum,maximum_selected FROM CZ_PS_NODES
113 WHERE parent_id=p_ps_node_id AND deleted_flag='0')
114 LOOP
115 IF (l.maximum<>1 AND
116 l.ps_node_type IN(PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS,
117 PS_NODE_TYPE_BOM_STANDART_ITEM)) THEN
118 RETURN TRUE;
119
120 ELSIF (l.ps_node_type IN(PS_NODE_TYPE_REFERENCE) AND l.maximum_selected<>1) THEN
121 BEGIN
122 SELECT '1' INTO l_flag FROM dual
123 WHERE EXISTS(SELECT NULL FROM CZ_PS_NODES
124 WHERE ps_node_id=l.reference_id AND
125 deleted_flag='0' AND ps_node_type=PS_NODE_TYPE_BOM_MODEL);
126 RETURN TRUE;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 NULL;
130 END;
131 END IF;
132
133 /*
134 IF l.ps_node_type IN(PS_NODE_TYPE_REFERENCE) THEN
135 FOR k IN(SELECT ps_node_id FROM CZ_PS_NODES
136 WHERE parent_id=l.reference_id AND deleted_flag='0' AND
137 ps_node_type IN(PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS,
138 PS_NODE_TYPE_BOM_STANDART_ITEM) AND
139 maximum<>1 AND rownum<2)
140 LOOP
141 RETURN TRUE;
142 END LOOP;
143 END IF;
144 */
145 END LOOP;
146
147 RETURN FALSE;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 RETURN TRUE;
152 END child_Nodes_Have_Quantities;
153
154
155 FUNCTION get_UI_Signature_Id
156 (
157 p_ps_node_id IN NUMBER,
158 p_instantiable_flag IN VARCHAR2,
159 p_feature_type IN NUMBER,
160 p_counted_options_flag IN VARCHAR2,
161 p_maximum IN NUMBER,
162 p_minimum IN NUMBER,
163 p_ps_node_type IN NUMBER,
164 p_reference_id IN NUMBER,
165 p_max_selected IN NUMBER)
166 RETURN NUMBER IS
167
168 v_model_type CZ_DEVL_PROJECTS.model_type%TYPE;
169 v_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
170
171 BEGIN
172
173 IF p_ps_node_type IN(PS_NODE_TYPE_PRODUCT,PS_NODE_TYPE_COMPONENT) AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
174 RETURN UMANDATORY_COMPONENT_TYPEID;
175
176 ELSIF p_ps_node_type IN(PS_NODE_TYPE_PRODUCT,PS_NODE_TYPE_COMPONENT) AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
177 RETURN UOPTIONAL_COMPONENT_TYPEID;
178
179 ELSIF p_ps_node_type IN(PS_NODE_TYPE_PRODUCT,PS_NODE_TYPE_COMPONENT) AND p_instantiable_flag=MINMAX_EXPL_TYPE THEN
180 RETURN UMINMAX_COMPONENT_TYPEID;
181
182 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('0','N')
183 AND p_maximum=1 THEN
184 RETURN UNON_COUNT_FEATURE_TYPEID;
185
186 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('1','Y') AND p_maximum=1 THEN
187 RETURN UCOUNT_FEATURE01_TYPEID;
188
189 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('1','Y') THEN
190 RETURN UCOUNT_FEATURE_TYPEID;
191
192 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('0','N') AND
193 NOT(p_minimum=1 AND NVL(p_maximum,-1)=1) THEN
194 RETURN UMINMAX_FEATURE_TYPEID;
195
196 ELSIF p_ps_node_type=PS_NODE_TYPE_OPTION THEN
197 RETURN UOPTION_TYPEID;
198
199 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=1 THEN
200 RETURN UINTEGER_FEATURE_TYPEID;
201
202 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=2 THEN
203 RETURN UDECIMAL_FEATURE_TYPEID;
204
205 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=3 THEN
206 RETURN UBOOLEAN_FEATURE_TYPEID;
207
208 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=4 THEN
209 RETURN UTEXT_FEATURE_TYPEID;
210
211 ELSIF p_ps_node_type=PS_NODE_TYPE_TOTAL THEN
212 RETURN UTOTAL_TYPEID;
213
214 ELSIF p_ps_node_type=PS_NODE_TYPE_RESOURCE THEN
215 RETURN URESOURCE_TYPEID;
216
217 ELSIF p_ps_node_type=PS_NODE_TYPE_INT_TOTAL THEN
218 RETURN UINT_TOTAL_TYPEID;
219
220 ELSIF p_ps_node_type=PS_NODE_TYPE_INT_RESOURCE THEN
221 RETURN UINT_RESOURCE_TYPEID;
222
223 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
224 RETURN UMANDATORY_REF_TYPEID;
225
226 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
227
228 SELECT model_type INTO v_model_type FROM CZ_DEVL_PROJECTS
229 WHERE devl_project_id=p_reference_id AND deleted_flag='0';
230
231 SELECT ps_node_type INTO v_ps_node_type FROM CZ_PS_NODES
232 WHERE ps_node_id=p_reference_id AND deleted_flag='0';
233
234 IF v_ps_node_type=PS_NODE_TYPE_BOM_MODEL AND v_model_type IN (MODEL_TYPE_ATO, MODEL_TYPE_PTO) THEN
235 RETURN UOPTIONAL_BOM_REF_TYPEID;
236 ELSE
237 RETURN UOPTIONAL_COMPONENT_TYPEID;
238 END IF;
239
240 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=MINMAX_EXPL_TYPE THEN
241
242 SELECT model_type INTO v_model_type FROM CZ_DEVL_PROJECTS
243 WHERE devl_project_id=p_reference_id AND deleted_flag='0';
244
245 SELECT ps_node_type INTO v_ps_node_type FROM CZ_PS_NODES
246 WHERE ps_node_id=p_reference_id AND deleted_flag='0';
247
248 IF v_ps_node_type=PS_NODE_TYPE_BOM_MODEL AND v_model_type IN (MODEL_TYPE_ATO, MODEL_TYPE_PTO) THEN
249 RETURN UMINMAX_BOM_REF_TYPEID;
250 ELSE
251 RETURN UMINMAX_COMPONENT_TYPEID;
252 END IF;
253
254 ELSIF p_ps_node_type=PS_NODE_TYPE_CONNECTOR THEN
255 RETURN UCONNECTOR_TYPEID;
256 /**
257 *--vsingava bug9888589 23rd Jul '10.
258 * We don't have all pieces to support multi-instantiable connector,
259 * which is supposedly for FCE type models
260 * IF p_minimum=1 AND p_maximum=1 THEN
261 * RETURN UCONNECTOR_TYPEID;
262 * ELSE
263 * RETURN UMINMAX_CONNECTOR;
264 * END IF;
265 */
266
267 ELSIF p_ps_node_type IN(PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS) AND
268 is_Mutex(p_ps_node_id,p_max_selected)='1' THEN
269
270 IF child_Nodes_Have_Quantities(p_ps_node_id) THEN
271 IF contains_Nested_BOM(p_ps_node_id) THEN
272 RETURN UBOM_NSTBOM_QMTX_TYPEID;
273 ELSE
274 RETURN UBOM_STIO_QMTX_TYPEID;
275 END IF;
276 ELSE
277 IF contains_Nested_BOM(p_ps_node_id) THEN
278 RETURN UBOM_NSTBOM_NQMTX_TYPEID;
279 ELSE
280 RETURN UBOM_STIO_NQMTX_TYPEID;
281 END IF;
282 END IF;
283
284 ELSIF p_ps_node_type IN(PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS) AND
285 is_Mutex(p_ps_node_id,p_max_selected)='0' THEN
286
287 IF child_Nodes_Have_Quantities(p_ps_node_id) THEN
288 IF contains_Nested_BOM(p_ps_node_id) THEN
289 RETURN UBOM_NSTBOM_QNMTX_TYPEID;
290 ELSE
291 RETURN UBOM_STIO_QNMTX_TYPEID;
292 END IF;
293 ELSE
294 IF contains_Nested_BOM(p_ps_node_id) THEN
295 RETURN UBOM_NSTBOM_NQNMTX_TYPEID;
296 ELSE
297 RETURN UBOM_STIO_NQNMTX_TYPEID;
298 END IF;
299 END IF;
300
301 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_STANDART_ITEM THEN
302 RETURN UBOM_STANDART_ITEM_TYPEID;
303
304 ELSE
305 RETURN DATA_TYPE_NODE;
306 END IF;
307
308 END get_UI_Signature_Id;
309
310 FUNCTION is_It_Region
311 ( p_ps_node_type IN NUMBER) RETURN VARCHAR2 IS
312
313 v_is_it_region VARCHAR2(1);
314
315 BEGIN
316 IF p_ps_node_type IN(PS_NODE_TYPE_PRODUCT,PS_NODE_TYPE_COMPONENT,PS_NODE_TYPE_BOM_MODEL,PS_NODE_TYPE_BOM_OPTION_CLASS) THEN
317 RETURN '1';
318 ELSE
319 RETURN '0';
320 END IF;
321 END is_It_Region;
322
323 FUNCTION get_Persistent_Node_Id(p_ps_node_id IN NUMBER) RETURN NUMBER IS
324 l_persistent_node_id NUMBER;
325 BEGIN
326 SELECT persistent_node_id INTO l_persistent_node_id FROM CZ_PS_NODES
327 WHERE ps_node_id=p_ps_node_id;
328 RETURN l_persistent_node_id;
329 END get_Persistent_Node_Id;
330
331 FUNCTION get_Rule_Signature_Id
332 (
333 p_instantiable_flag IN VARCHAR2,
334 p_feature_type IN NUMBER,
335 p_counted_options_flag IN VARCHAR2,
336 p_maximum IN NUMBER,
337 p_minimum IN NUMBER,
338 p_ps_node_type IN NUMBER,
339 p_reference_id IN NUMBER,
340 p_max_selected IN NUMBER,
341 p_decimal_qty_flag IN VARCHAR2,
342 p_ib_trackable IN VARCHAR2,
343 p_devl_project_id IN NUMBER)
344 RETURN NUMBER IS
345
346 v_model_type CZ_DEVL_PROJECTS.model_type%TYPE;
347 v_config_engine_type CZ_DEVL_PROJECTS.config_engine_type%TYPE;
348
349 BEGIN
350
351 SELECT NVL(config_engine_type,'L') INTO v_config_engine_type FROM CZ_DEVL_PROJECTS
352 WHERE devl_project_id=p_devl_project_id AND deleted_flag='0';
353
354 IF p_ps_node_type=PS_NODE_TYPE_PRODUCT AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
355 RETURN MANDATORY_PRODUCT_TYPEID;
356
357 ELSIF p_ps_node_type=PS_NODE_TYPE_PRODUCT AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
358 RETURN OPTIONAL_PRODUCT_TYPEID;
359
360 ELSIF p_ps_node_type=PS_NODE_TYPE_PRODUCT AND p_instantiable_flag=MINMAX_EXPL_TYPE THEN
361 RETURN MINMAX_PRODUCT_TYPEID;
362
363 ELSIF p_ps_node_type=PS_NODE_TYPE_COMPONENT AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
364 RETURN MANDATORY_COMPONENT_TYPEID;
365
366 ELSIF p_ps_node_type=PS_NODE_TYPE_COMPONENT AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
367 RETURN OPTIONAL_COMPONENT_TYPEID;
368
369 ELSIF p_ps_node_type=PS_NODE_TYPE_COMPONENT AND p_instantiable_flag=MINMAX_EXPL_TYPE THEN
370 RETURN MINMAX_COMPONENT_TYPEID;
371
372 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('0','N') AND p_minimum=1
373 AND p_maximum=1 THEN
374 RETURN NON_COUNT_FEATURE_TYPEID;
375
376 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('1','Y') AND p_maximum=1 THEN
377 RETURN COUNT_FEATURE01_TYPEID;
378
379 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('1','Y')
380 AND (p_maximum>1 OR p_maximum=-1 OR p_maximum IS NULL) THEN
381 RETURN COUNT_FEATURE_TYPEID;
382
383 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('0','N') AND p_minimum=0 AND p_maximum=1 THEN
384 RETURN MINMAX_FEATURE01_TYPEID;
385
386 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND NVL(p_feature_type,0)=0 AND p_counted_options_flag IN('0','N') AND
387 NOT(p_minimum=1 AND NVL(p_maximum,-1)=1) THEN
388 RETURN MINMAX_FEATURE_TYPEID;
389
390 ELSIF p_ps_node_type=PS_NODE_TYPE_OPTION THEN
391 RETURN OPTION_TYPEID;
392
393 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=1 THEN
394
395 IF NVL(p_minimum, -1)>=0 THEN
396 IF v_config_engine_type='F' THEN
397 RETURN INTEGER_FEATURE_TYPEID;
398 ELSE
399 RETURN INTEGER_COUNT_FEATURE_TYPEID;
400 END IF;
401 ELSE
402 RETURN INTEGER_FEATURE_TYPEID;
403 END IF;
404
405 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=2 THEN
406 RETURN DECIMAL_FEATURE_TYPEID;
407
408 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=3 THEN
409 RETURN BOOLEAN_FEATURE_TYPEID;
410
411 ELSIF p_ps_node_type=PS_NODE_TYPE_FEATURE AND p_feature_type=4 THEN
412 RETURN TEXT_FEATURE_TYPEID;
413
414 ELSIF p_ps_node_type=PS_NODE_TYPE_TOTAL THEN
415 RETURN TOTAL_TYPEID;
416
417 ELSIF p_ps_node_type=PS_NODE_TYPE_RESOURCE THEN
418 RETURN RESOURCE_TYPEID;
419
420 ELSIF p_ps_node_type=PS_NODE_TYPE_INT_TOTAL THEN
421 RETURN INT_TOTAL_TYPEID;
422
423 ELSIF p_ps_node_type=PS_NODE_TYPE_INT_RESOURCE THEN
424 RETURN INT_RESOURCE_TYPEID;
425
426 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
427
428 SELECT model_type INTO v_model_type FROM CZ_DEVL_PROJECTS
429 WHERE devl_project_id=p_reference_id AND deleted_flag='0';
430
431 IF v_model_type IN (MODEL_TYPE_ATO, MODEL_TYPE_PTO) THEN
432 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_MBOM_REF_TYPEID;
433 ELSE RETURN MANDATORY_BOM_REF_TYPEID; END IF;
434 ELSE
435 RETURN MANDATORY_NONBOM_REF_TYPEID;
436 END IF;
437
438 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
439
440 SELECT model_type INTO v_model_type FROM CZ_DEVL_PROJECTS
441 WHERE devl_project_id=p_reference_id AND deleted_flag='0';
442
443
444 IF v_model_type IN (MODEL_TYPE_ATO, MODEL_TYPE_PTO) THEN
445 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_OPTBOM_REF_TYPEID;
446 ELSE RETURN OPTIONAL_BOM_REF_TYPEID; END IF;
447 ELSE
448 RETURN OPTIONAL_NONBOM_REF_TYPEID;
449 END IF;
450
451 ELSIF p_ps_node_type=PS_NODE_TYPE_REFERENCE AND p_instantiable_flag=MINMAX_EXPL_TYPE THEN
452
453 SELECT model_type INTO v_model_type FROM CZ_DEVL_PROJECTS
454 WHERE devl_project_id=p_reference_id AND deleted_flag='0';
455
456 IF v_model_type IN (MODEL_TYPE_ATO, MODEL_TYPE_PTO) THEN
457 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_MMBOM_REF_TYPEID;
458 ELSE RETURN MINMAX_BOM_REF_TYPEID; END IF;
459 ELSE
460 RETURN MINMAX_NONBOM_REF_TYPEID;
461 END IF;
462
463 ELSIF p_ps_node_type=PS_NODE_TYPE_CONNECTOR THEN
464 RETURN CONNECTOR_TYPEID;
465
466 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_MODEL AND NVL(p_max_selected,-1)=1 THEN
467 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_MODEL_MTX_TYPEID;
468 ELSE RETURN BOM_MODEL_MTX_TYPEID; END IF;
469
470 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_MODEL AND NVL(p_max_selected,-1)<>1 THEN
471 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_MODEL_NMTX_TYPEID;
472 ELSE RETURN BOM_MODEL_NMTX_TYPEID; END IF;
473
474 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_OPTION_CLASS AND NVL(p_max_selected,-1)=1 THEN
475 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_OC_MTX_TYPEID;
476 ELSE RETURN BOM_OPTION_CLASS_MTX_TYPEID; END IF;
477
478 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_OPTION_CLASS AND NVL(p_max_selected,-1)<>1 THEN
479 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_OC_NMTX_TYPEID;
480 ELSE RETURN BOM_OPTION_CLASS_NMTX_TYPEID; END IF;
481
482 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_STANDART_ITEM AND
483 (p_decimal_qty_flag IS NULL OR p_decimal_qty_flag='0') THEN
484 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_STD_ITEM_TYPEID;
485 ELSE RETURN BOM_STANDART_ITEM_TYPEID; END IF;
486
487 ELSIF p_ps_node_type=PS_NODE_TYPE_BOM_STANDART_ITEM AND p_decimal_qty_flag='1' THEN
488 IF(p_ib_trackable = '1')THEN RETURN TRACKABLE_DECQ_STD_ITEM_TYPEID;
489 ELSE RETURN BOM_DECQ_STANDART_ITEM_TYPEID; END IF;
490
491 ELSE
492 RETURN DATA_TYPE_NODE;
493 END IF;
494
495 END get_Rule_Signature_Id;
496
497 PROCEDURE get_Ps_Node_Type
498 (p_signature_id IN NUMBER,
499 x_ps_node_type OUT NOCOPY NUMBER,
500 x_ps_node_subtype OUT NOCOPY NUMBER) IS
501
502 BEGIN
503 x_ps_node_type := NULL;
504 x_ps_node_subtype := NULL;
505
506 IF p_signature_id IN
507 (PS_NODE_TYPE_PRODUCT
508 ,PS_NODE_TYPE_COMPONENT
509 ,PS_NODE_TYPE_FEATURE
510 ,PS_NODE_TYPE_OPTION
511 ,PS_NODE_TYPE_TOTAL
512 ,PS_NODE_TYPE_RESOURCE
513 ,PS_NODE_TYPE_INT_TOTAL
514 ,PS_NODE_TYPE_INT_RESOURCE
515 ,PS_NODE_TYPE_BOM_MODEL
516 ,PS_NODE_TYPE_BOM_OPTION_CLASS
517 ,PS_NODE_TYPE_BOM_STANDART_ITEM
518 ,PS_NODE_TYPE_REFERENCE
519 ,PS_NODE_TYPE_CONNECTOR) THEN
520
521 x_ps_node_type := p_signature_id;
522
523 IF p_signature_id=PS_NODE_TYPE_FEATURE THEN
524 x_ps_node_subtype := 0;
525 END IF;
526 RETURN;
527 END IF;
528
529 IF p_signature_id IN(MANDATORY_PRODUCT_TYPEID,OPTIONAL_PRODUCT_TYPEID,MINMAX_PRODUCT_TYPEID) THEN
530 x_ps_node_type := PS_NODE_TYPE_PRODUCT;
531 ELSIF p_signature_id IN(MANDATORY_COMPONENT_TYPEID,OPTIONAL_COMPONENT_TYPEID,MINMAX_COMPONENT_TYPEID,
532 UMANDATORY_COMPONENT_TYPEID,UOPTIONAL_COMPONENT_TYPEID,UMINMAX_COMPONENT_TYPEID) THEN
533 x_ps_node_type := PS_NODE_TYPE_COMPONENT;
534 ELSIF p_signature_id IN(NON_COUNT_FEATURE_TYPEID,COUNT_FEATURE_TYPEID,COUNT_FEATURE01_TYPEID,
535 MINMAX_FEATURE_TYPEID,MINMAX_FEATURE01_TYPEID,
536 UNON_COUNT_FEATURE_TYPEID,UCOUNT_FEATURE_TYPEID,UCOUNT_FEATURE01_TYPEID,
537 UMINMAX_FEATURE_TYPEID,CNON_COUNT_FEATURE_TYPEID) THEN
538 x_ps_node_type := PS_NODE_TYPE_FEATURE;
539 x_ps_node_subtype := 0;
540 ELSIF p_signature_id IN(OPTION_TYPEID,UOPTION_TYPEID) THEN
541 x_ps_node_type := PS_NODE_TYPE_OPTION;
542 ELSIF p_signature_id IN(INTEGER_FEATURE_TYPEID,UINTEGER_FEATURE_TYPEID) THEN
543 x_ps_node_type := PS_NODE_TYPE_FEATURE;
544 x_ps_node_subtype := 1;
545 ELSIF p_signature_id IN(DECIMAL_FEATURE_TYPEID,UDECIMAL_FEATURE_TYPEID) THEN
546 x_ps_node_type := PS_NODE_TYPE_FEATURE;
547 x_ps_node_subtype := 2;
548 ELSIF p_signature_id IN(BOOLEAN_FEATURE_TYPEID,UBOOLEAN_FEATURE_TYPEID) THEN
549 x_ps_node_type := PS_NODE_TYPE_FEATURE;
550 x_ps_node_subtype := 3;
551 ELSIF p_signature_id IN(TEXT_FEATURE_TYPEID,UTEXT_FEATURE_TYPEID) THEN
552 x_ps_node_type := PS_NODE_TYPE_FEATURE;
553 x_ps_node_subtype := 4;
554 ELSIF p_signature_id IN(TOTAL_TYPEID,UTOTAL_TYPEID) THEN
555 x_ps_node_type := PS_NODE_TYPE_TOTAL;
556 ELSIF p_signature_id IN(RESOURCE_TYPEID,URESOURCE_TYPEID) THEN
557 x_ps_node_type := PS_NODE_TYPE_RESOURCE;
558 ELSIF p_signature_id IN(INT_TOTAL_TYPEID,UINT_TOTAL_TYPEID) THEN
559 x_ps_node_type := PS_NODE_TYPE_INT_TOTAL;
560 ELSIF p_signature_id IN(INT_RESOURCE_TYPEID,UINT_RESOURCE_TYPEID) THEN
561 x_ps_node_type := PS_NODE_TYPE_INT_RESOURCE;
562 ELSIF p_signature_id IN(MANDATORY_NONBOM_REF_TYPEID,OPTIONAL_NONBOM_REF_TYPEID,MINMAX_NONBOM_REF_TYPEID,
563 MANDATORY_BOM_REF_TYPEID,OPTIONAL_BOM_REF_TYPEID,MINMAX_BOM_REF_TYPEID,
564 UMANDATORY_REF_TYPEID,UOPTIONAL_BOM_REF_TYPEID,UMINMAX_BOM_REF_TYPEID) THEN
565 x_ps_node_type := PS_NODE_TYPE_REFERENCE;
566 ELSIF p_signature_id IN(CONNECTOR_TYPEID,UCONNECTOR_TYPEID) THEN
567 x_ps_node_type := PS_NODE_TYPE_CONNECTOR;
568 ELSIF p_signature_id IN(BOM_MODEL_MTX_TYPEID,BOM_MODEL_NMTX_TYPEID) THEN
569 x_ps_node_type := PS_NODE_TYPE_BOM_MODEL;
570 ELSIF p_signature_id IN(BOM_OPTION_CLASS_MTX_TYPEID,BOM_OPTION_CLASS_NMTX_TYPEID) THEN
571 x_ps_node_type := PS_NODE_TYPE_BOM_OPTION_CLASS;
572 ELSIF p_signature_id IN(BOM_STANDART_ITEM_TYPEID,UBOM_STANDART_ITEM_TYPEID) THEN
573 x_ps_node_type := PS_NODE_TYPE_BOM_STANDART_ITEM;
574 ELSE
575 NULL;
576 END IF;
577
578 END get_Ps_Node_Type;
579
580
581 FUNCTION NODE_TYPE_AVAILABLE(p_ps_node_id IN NUMBER,
582 p_exp_data_type IN NUMBER,
583 p_exp_mutable_flag IN NUMBER,
584 p_exp_collection_flag IN NUMBER)
585 RETURN NUMBER IS
586 retval NUMBER;
587 BEGIN
588 SELECT 1
589 INTO retval
590 FROM dual
591 WHERE EXISTS (
592 SELECT *
593 FROM CZ_NODE_NO_PROPERTIES_V lov
594 WHERE lov.ps_node_id = p_ps_node_id
595 AND lov.data_type = p_exp_data_type
596 AND lov.mutable_flag >= Nvl(p_exp_mutable_flag,0)
597 AND lov.collection_flag <= Nvl(p_exp_collection_flag,0)
598 );
599 RETURN retval;
600 END node_type_available;
601
602
603 FUNCTION NODE_USER_PROPS_AVAILABLE(p_ps_node_id IN NUMBER,
604 p_exp_data_type IN NUMBER,
605 p_exp_mutable_flag IN NUMBER,
606 p_exp_collection_flag IN NUMBER)
607 RETURN NUMBER IS
608 retval NUMBER;
609 BEGIN
610 SELECT 1
611 INTO retval
612 FROM dual
613 WHERE EXISTS (
614 SELECT *
615 FROM CZ_NODE_USER_PROPERTIES_V lov
616 WHERE lov.ps_node_id = p_ps_node_id
617 AND lov.data_type = p_exp_data_type
618 AND lov.mutable_flag >= Nvl(p_exp_mutable_flag,0)
619 AND lov.collection_flag <= Nvl(p_exp_collection_flag,0)
620 );
621 RETURN retval;
622 END NODE_USER_PROPS_AVAILABLE;
623
624
625
626 FUNCTION NODE_CAPTION_PROPS_AVAILABLE(p_ps_node_id IN NUMBER,
627 p_exp_data_type IN NUMBER,
628 p_exp_mutable_flag IN NUMBER,
629 p_exp_collection_flag IN NUMBER)
633 SELECT 1
630 RETURN NUMBER IS
631 retval NUMBER;
632 BEGIN
634 INTO retval
635 FROM dual
636 WHERE EXISTS (
637 SELECT *
638 FROM CZ_NODE_CAPTION_PROPERTIES_V lov
639 WHERE lov.ps_node_id = p_ps_node_id
640 AND lov.data_type = p_exp_data_type
641 AND lov.mutable_flag >= Nvl(p_exp_mutable_flag,0)
642 AND lov.collection_flag <= Nvl(p_exp_collection_flag,0)
643 AND lov.property_type <> -1
644 );
645 RETURN retval;
646 END NODE_CAPTION_PROPS_AVAILABLE;
647
648
649 FUNCTION IS_TEMPLATE_SIGNATURE(p_data_type IN NUMBER)
650 RETURN NUMBER IS
651 sig_type NUMBER;
652 BEGIN
653 SELECT signature_type
654 INTO sig_type
655 FROM cz_data_types_v
656 WHERE signature_id = p_data_type;
657
658 IF sig_type = 'TPL' THEN
659 RETURN 1;
660 END IF;
661 RETURN NULL;
662 END IS_TEMPLATE_SIGNATURE;
663
664
665 END CZ_TYPES;
[Home] [Help]