[Home] [Help]
7: G_INTL_TEXT_TYPE CONSTANT VARCHAR2(10) := 'T';
8: G_UI_ACTION_TYPE CONSTANT VARCHAR2(10) := 'A';
9: modelReportRun BOOLEAN := FALSE; -- vsingava: 24-Nov-2008; Bug 7297669
10: mINCREMENT INTEGER:=20;
11: currPSSeqVal CZ_PS_NODES.ps_node_id%TYPE:=0;
12: currentPSNode CZ_PS_NODES.ps_node_id%TYPE:=mINCREMENT;
13:
14: -- FOR MODEL REPORT, storing default seeded data in static data 5404051
15: TYPE tRuleName IS TABLE OF cz_rules.name%TYPE INDEX BY BINARY_INTEGER;
8: G_UI_ACTION_TYPE CONSTANT VARCHAR2(10) := 'A';
9: modelReportRun BOOLEAN := FALSE; -- vsingava: 24-Nov-2008; Bug 7297669
10: mINCREMENT INTEGER:=20;
11: currPSSeqVal CZ_PS_NODES.ps_node_id%TYPE:=0;
12: currentPSNode CZ_PS_NODES.ps_node_id%TYPE:=mINCREMENT;
13:
14: -- FOR MODEL REPORT, storing default seeded data in static data 5404051
15: TYPE tRuleName IS TABLE OF cz_rules.name%TYPE INDEX BY BINARY_INTEGER;
16: TYPE tTemplateToken IS TABLE OF cz_rules.template_token%TYPE INDEX BY BINARY_INTEGER;
69: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
70:
71: PROCEDURE Initialize IS
72: BEGIN
73: SELECT CZ_PS_NODES_S.NEXTVAL INTO currentPSNode FROM dual;
74: currPSSeqVal:=currentPSNode;
75:
76: SELECT TO_NUMBER(value) INTO mINCREMENT FROM cz_db_settings
77: WHERE UPPER(setting_id)=UPPER('OracleSequenceIncr') AND section_name='SCHEMA';
90:
91: IF currentPSNode
93: ELSE
94: SELECT CZ_PS_NODES_S.nextval INTO currPSSeqVal FROM dual;
95: currentPSNode:=currPSSeqVal;
96: END IF;
97: RETURN currentPSNode;
98: END getPSSeqVal;
243: IS
244:
245: TYPE t_checkout_user_tbl IS TABLE OF cz_devl_projects.checkout_user%TYPE INDEX BY BINARY_INTEGER;
246: TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
247: TYPE name_tbl IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
248:
249: l_user_name cz_ui_templates.checkout_user%type;
250: l_rp_folder NUMBER;
251: l_rp_model_tbl number_type_tbl;
455: l_depth NUMBER;
456: l_path VARCHAR2(32000);
457: l_full_path VARCHAR2(32000);
458: l_substr VARCHAR2(32000);
459: l_name CZ_PS_NODES.name%TYPE;
460: l_to NUMBER;
461: l_from NUMBER;
462: l_index NUMBER;
463: l_shift NUMBER;
786: l_node_depth NUMBER;
787: l_parent_id NUMBER;
788: l_tree_seq NUMBER;
789: l_index NUMBER;
790: l_name CZ_PS_NODES.name%TYPE;
791: l_virtual_flag VARCHAR2(1);
792:
793: l_ps_nodes_tbl t_int_array_tbl_type_idx_vc2;
794: l_persistent_tbl t_int_array_tbl_type_idx_vc2;
815:
816: -- get the info from the node to be copied
817: SELECT devl_project_id,parent_id,reference_id,component_id,virtual_flag,ps_node_type
818: INTO l_model_id,l_parent_id,l_reference_id,l_component_id,l_virtual_flag,l_ps_node_type
819: FROM CZ_PS_NODES
820: WHERE ps_node_id=p_node_id;
821:
822: -- if not a reference node, get the model_refexpl_id record in model ref expls
823: IF l_reference_id IS NULL THEN
840: -- get the ref expl id and node depth of the destination node in ref expls
841: SELECT model_ref_expl_id,node_depth INTO l_new_pr_expl_id, l_node_depth
842: FROM CZ_MODEL_REF_EXPLS
843: WHERE model_id=l_model_id AND component_id=(SELECT component_id
844: FROM CZ_PS_NODES WHERE ps_node_id = p_new_parent_id) AND referring_node_id IS NULL AND
845: child_model_expl_id IS NULL AND deleted_flag='0';
846:
847: FOR i IN (SELECT ps_node_id, ps_node_type, component_id, persistent_node_id, name FROM CZ_PS_NODES
848: START WITH ps_node_id=p_node_id AND deleted_flag='0'
843: WHERE model_id=l_model_id AND component_id=(SELECT component_id
844: FROM CZ_PS_NODES WHERE ps_node_id = p_new_parent_id) AND referring_node_id IS NULL AND
845: child_model_expl_id IS NULL AND deleted_flag='0';
846:
847: FOR i IN (SELECT ps_node_id, ps_node_type, component_id, persistent_node_id, name FROM CZ_PS_NODES
848: START WITH ps_node_id=p_node_id AND deleted_flag='0'
849: CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
850: LOOP
851: l_ps_nodes_tbl(i.ps_node_id) := getPSSeqVal;
859: IF l_index IS NULL THEN
860: EXIT;
861: END IF;
862:
863: FOR i IN (SELECT * FROM CZ_PS_NODES
864: WHERE ps_node_id=l_index AND deleted_flag='0')
865: LOOP
866: IF i.ps_node_id=p_node_id THEN
867: l_new_parent_id := p_new_parent_id;
865: LOOP
866: IF i.ps_node_id=p_node_id THEN
867: l_new_parent_id := p_new_parent_id;
868: SELECT NVL(MAX(tree_seq),0) + 1 INTO l_tree_seq
869: FROM cz_ps_nodes
870: WHERE parent_id=p_new_parent_id;
871: ELSE
872: l_new_parent_id := l_ps_nodes_tbl(i.parent_id);
873: l_tree_seq := i.TREE_SEQ;
895: l_new_violation_text_id:=NULL;
896: END IF;
897:
898: IF l_new_parent_id=p_new_parent_id THEN
899: SELECT COUNT(*) INTO l_node_counter FROM CZ_PS_NODES
900: WHERE parent_id=p_new_parent_id AND
901: (name=i.NAME OR name LIKE 'Copy (%) of '||i.NAME) AND deleted_flag='0';
902: IF l_node_counter > 0 THEN
903: l_name := 'Copy ('||TO_CHAR(l_node_counter)||') of '||i.NAME;
907: ELSE
908: l_name := i.NAME;
909: END IF;
910:
911: INSERT INTO CZ_PS_NODES
912: (
913: PS_NODE_ID
914: ,DEVL_PROJECT_ID
915: ,FROM_POPULATOR_ID
1116: IF (l_virtual_flag='1' OR l_virtual_flag IS NULL) AND l_parent_id IS NOT NULL AND l_ps_node_type IN(COMPONENT_TYPE) THEN
1117: FOR i IN(SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
1118: WHERE parent_expl_node_id=l_expl_id AND
1119: (referring_node_id IS NULL AND component_id IN
1120: (SELECT ps_node_id FROM CZ_PS_NODES
1121: START WITH ps_node_id=p_node_id
1122: CONNECT BY PRIOR ps_node_id=parent_id AND
1123: deleted_flag='0' AND PRIOR deleted_flag='0'))
1124: UNION
1124: UNION
1125: SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
1126: WHERE parent_expl_node_id=l_expl_id AND
1127: (referring_node_id IS NOT NULL AND referring_node_id IN
1128: (SELECT ps_node_id FROM CZ_PS_NODES
1129: START WITH ps_node_id=p_node_id
1130: CONNECT BY PRIOR ps_node_id=parent_id AND
1131: deleted_flag='0' AND PRIOR deleted_flag='0')))
1132:
1208: -- cursor to load all participating ps nodes in a expression or CX rule
1209: ------------------------------------------------------------------------
1210: CURSOR l_rule_nodes_csr (iRuleId NUMBER) IS
1211: SELECT DISTINCT e.ps_node_id, p.devl_project_id, e.model_ref_expl_id
1212: FROM cz_expression_nodes e, cz_rules r, cz_ps_nodes p
1213: WHERE e.deleted_flag = '0'
1214: AND r.deleted_flag = '0'
1215: AND p.deleted_flag = '0'
1216: AND p.ps_node_id = e.ps_node_id
1221: -- cursor to load all participating ps nodes in design chart or explicit comp rule
1222: ----------------------------------------------------------------------------------
1223: CURSOR l_rule_nodes_csr2 (iRuleId NUMBER) IS
1224: SELECT DISTINCT f.feature_id, p.devl_project_id, f.model_ref_expl_id
1225: FROM cz_des_chart_features f, cz_rules r, cz_ps_nodes p
1226: WHERE f.deleted_flag = '0'
1227: AND r.deleted_flag = '0'
1228: AND p.deleted_flag = '0'
1229: AND p.ps_node_id = f.feature_id
1249: END IF;
1250:
1251: IF (l_ps_node_type_tbl(l_index) IN (263, 264)) THEN
1252: SELECT model_ref_expl_id INTO l_new_expl_id
1253: FROM cz_model_ref_expls a, cz_ps_nodes b
1254: WHERE a.referring_node_id = l_ps_nodes_tbl(l_index)
1255: AND a.referring_node_id = b.ps_node_id
1256: AND b.devl_project_id = l_model_id
1257: AND a.model_id = l_model_id
1259: AND a.deleted_flag='0'
1260: AND b.deleted_flag='0';
1261: ELSE
1262: SELECT component_id INTO l_new_component_id
1263: FROM cz_ps_nodes
1264: WHERE ps_node_id=l_ps_nodes_tbl(l_index);
1265:
1266: SELECT model_ref_expl_id INTO l_new_expl_id
1267: FROM cz_model_ref_expls a, cz_ps_nodes b
1263: FROM cz_ps_nodes
1264: WHERE ps_node_id=l_ps_nodes_tbl(l_index);
1265:
1266: SELECT model_ref_expl_id INTO l_new_expl_id
1267: FROM cz_model_ref_expls a, cz_ps_nodes b
1268: WHERE a.component_id = b.ps_node_id
1269: AND b.ps_node_id = l_new_component_id
1270: AND b.devl_project_id = l_model_id
1271: AND a.model_id = b.devl_project_id
1279:
1280: IF (l_rule_type_tbl(i) = RULE_TYPE_JAVA_METHOD AND NOT l_ps_nodes_tbl.EXISTS(l_rule_compid_tbl(i))) THEN
1281:
1282: SELECT devl_project_id INTO l_referenced_model_id
1283: FROM cz_ps_nodes
1284: WHERE ps_node_id = l_rule_compid_tbl(i)
1285: AND deleted_flag = '0';
1286:
1287: l_node_found := FALSE;
1919: --
1920: -- first - try to get ps_node_id from the current mode,
1921: -- because in most cases ps node is under the current model
1922: --
1923: SELECT ps_node_id INTO l_component_id FROM CZ_PS_NODES
1924: WHERE devl_project_id=l_devl_project_id AND
1925: persistent_node_id=l_persistent_node_id AND
1926: deleted_flag='0';
1927: EXCEPTION
1941: l_reference_ps_node_id := p.referring_node_id;
1942: END LOOP;
1943:
1944: -- we need to use l_model_ref_expl_id in this case
1945: SELECT ps_node_id INTO l_component_id FROM CZ_PS_NODES
1946: WHERE devl_project_id=
1947: (
1948: SELECT devl_project_id FROM CZ_PS_NODES
1949: WHERE ps_node_id=l_reference_ps_node_id
1944: -- we need to use l_model_ref_expl_id in this case
1945: SELECT ps_node_id INTO l_component_id FROM CZ_PS_NODES
1946: WHERE devl_project_id=
1947: (
1948: SELECT devl_project_id FROM CZ_PS_NODES
1949: WHERE ps_node_id=l_reference_ps_node_id
1950: ) AND
1951: persistent_node_id=l_persistent_node_id AND
1952: deleted_flag='0';
2739: l_model_path VARCHAR2(32000);
2740: l_name VARCHAR2(32000);
2741: BEGIN
2742:
2743: FOR i IN(SELECT NAME FROM CZ_PS_NODES
2744: START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
2745: CONNECT BY PRIOR parent_id=ps_node_id AND
2746: PRIOR deleted_flag='0' AND deleted_flag='0')
2747: LOOP
2802: BEGIN
2803:
2804: BEGIN
2805: SELECT ps_node_id INTO l_ps_node_id
2806: FROM CZ_PS_NODES
2807: WHERE ps_node_id=p_ps_node_id AND devl_project_id=p_model_id;
2808: EXCEPTION
2809: WHEN NO_DATA_FOUND THEN
2810: NULL;
2873: p_ps_node_id IN NUMBER,
2874: px_model_path OUT NOCOPY VARCHAR2
2875: ) IS
2876:
2877: l_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2878: l_name CZ_PS_NODES.name%TYPE;
2879: l_model_path VARCHAR2(32000);
2880:
2881: BEGIN
2874: px_model_path OUT NOCOPY VARCHAR2
2875: ) IS
2876:
2877: l_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2878: l_name CZ_PS_NODES.name%TYPE;
2879: l_model_path VARCHAR2(32000);
2880:
2881: BEGIN
2882:
2879: l_model_path VARCHAR2(32000);
2880:
2881: BEGIN
2882:
2883: FOR i IN(SELECT parent_id,name FROM CZ_PS_NODES
2884: START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
2885: CONNECT BY PRIOR parent_id=ps_node_id AND
2886: PRIOR deleted_flag='0' AND deleted_flag='0' AND NVL(virtual_flag,'1')='1')
2887: LOOP
2896: l_ps_node_id := i.parent_id;
2897: END LOOP;
2898:
2899: IF l_ps_node_id IS NOT NULL THEN
2900: SELECT name INTO l_name FROM CZ_PS_NODES
2901: WHERE ps_node_id=l_ps_node_id;
2902:
2903: l_name := set_name_entity(l_name);
2904: l_model_path := l_name||'.'||l_model_path;
2916: (
2917: p_ps_node_id IN NUMBER
2918: ) RETURN VARCHAR2 IS
2919:
2920: l_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2921: l_name CZ_PS_NODES.name%TYPE;
2922: l_model_path VARCHAR2(32000);
2923:
2924: BEGIN
2917: p_ps_node_id IN NUMBER
2918: ) RETURN VARCHAR2 IS
2919:
2920: l_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2921: l_name CZ_PS_NODES.name%TYPE;
2922: l_model_path VARCHAR2(32000);
2923:
2924: BEGIN
2925:
2922: l_model_path VARCHAR2(32000);
2923:
2924: BEGIN
2925:
2926: FOR i IN(SELECT parent_id,name FROM CZ_PS_NODES
2927: START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
2928: CONNECT BY PRIOR parent_id=ps_node_id AND
2929: PRIOR deleted_flag='0' AND deleted_flag='0' AND NVL(virtual_flag,'1')='1')
2930: LOOP
2939: l_ps_node_id := i.parent_id;
2940: END LOOP;
2941:
2942: IF l_ps_node_id IS NOT NULL THEN
2943: SELECT name INTO l_name FROM CZ_PS_NODES
2944: WHERE ps_node_id=l_ps_node_id;
2945:
2946: l_name := set_name_entity(l_name);
2947: l_model_path := l_name||'.'||l_model_path;
2956: IS
2957: l_model_path VARCHAR2(32000);
2958: l_name VARCHAR2(32000);
2959: BEGIN
2960: FOR i IN (SELECT name, intl_text_id, ps_node_type FROM cz_ps_nodes
2961: START WITH ps_node_id = p_ps_node_id AND deleted_flag='0'
2962: CONNECT BY PRIOR parent_id = ps_node_id AND
2963: PRIOR deleted_flag='0' AND deleted_flag='0') LOOP
2964:
3027: v_intl_text_id NUMBER;
3028: BEGIN
3029:
3030: SELECT ps_node_type, intl_text_id, name INTO v_ps_node_type, v_intl_text_id, v_name
3031: FROM cz_ps_nodes WHERE ps_node_id = p_ps_node_id;
3032:
3033: v_description := v_name;
3034:
3035: IF(v_intl_text_id IS NOT NULL)THEN
3070: v_label_nonbom := NVL(fnd_profile.value_wnps(PROFILE_OPTION_LABEL_NONBOM), OPTION_VALUE_LABEL_NAME);
3071:
3072: BEGIN
3073: SELECT ps_node_id INTO l_ps_node_id
3074: FROM CZ_PS_NODES
3075: WHERE ps_node_id=p_ps_node_id AND devl_project_id=p_model_id;
3076: EXCEPTION
3077: WHEN NO_DATA_FOUND THEN
3078: NULL;
3572: l_model_id_tbl t_num_array_tbl_type;
3573: l_model_name_tbl t_varchar_array_tbl_type;
3574: l_node_name_tbl t_varchar_array_tbl_type;
3575:
3576: l_devl_project_id cz_ps_nodes.devl_project_id%TYPE;
3577: l_name_tbl l_tName;
3578: l_priv VARCHAR2(1) := 'F';
3579:
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3576: l_devl_project_id cz_ps_nodes.devl_project_id%TYPE;
3577: l_name_tbl l_tName;
3578: l_priv VARCHAR2(1) := 'F';
3579:
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3577: l_name_tbl l_tName;
3578: l_priv VARCHAR2(1) := 'F';
3579:
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3585: l_node_name cz_ps_nodes.name%TYPE;
3578: l_priv VARCHAR2(1) := 'F';
3579:
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3585: l_node_name cz_ps_nodes.name%TYPE;
3586:
3579:
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3585: l_node_name cz_ps_nodes.name%TYPE;
3586:
3587: p_out_err INTEGER;
3580: l_node_type cz_ps_nodes.ps_node_type%TYPE;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3585: l_node_name cz_ps_nodes.name%TYPE;
3586:
3587: p_out_err INTEGER;
3588: p_del_logically INTEGER:=1;
3581: l_feature_type cz_ps_nodes.feature_type%TYPE;
3582: l_vf cz_ps_nodes.virtual_flag%TYPE;
3583: l_parent_id cz_ps_nodes.parent_id%TYPE;
3584: l_tree_seq cz_ps_nodes.tree_seq%TYPE;
3585: l_node_name cz_ps_nodes.name%TYPE;
3586:
3587: p_out_err INTEGER;
3588: p_del_logically INTEGER:=1;
3589:
3592:
3593: -- child nodes
3594: CURSOR l_children_csr (inPsNodeId NUMBER) IS
3595: SELECT ps_node_id, ps_node_type, virtual_flag
3596: FROM cz_ps_nodes
3597: WHERE parent_id = inPsNodeId
3598: AND deleted_flag = '0';
3599:
3600: ---------------------------------------------------------
3614: p_del_logically INTEGER:=1;
3615:
3616: CURSOR l_children_csr IS
3617: SELECT ps_node_id, ps_node_type, virtual_flag, feature_type
3618: FROM cz_ps_nodes
3619: WHERE parent_id = p_ps_node_id
3620: AND deleted_flag = '0';
3621:
3622: BEGIN
3647: --------------------------
3648: -- finally delete this child node
3649: --------------------------
3650:
3651: UPDATE cz_ps_nodes
3652: SET deleted_flag = '1'
3653: WHERE ps_node_id = l_ps_node_tbl(i)
3654: AND ps_node_type NOT IN (436,437,438);
3655:
3677: SELECT virtual_flag, ps_node_type, feature_type,
3678: parent_id, tree_seq, devl_project_id, name
3679: INTO l_vf, l_node_type, l_feature_type,
3680: l_parent_id, l_tree_seq, l_devl_project_id, l_node_name
3681: FROM cz_ps_nodes
3682: WHERE ps_node_id = p_ps_node_id
3683: AND deleted_flag = '0';
3684:
3685: IF l_node_type IN (436,437,438) THEN
3733: ------------------------------------------
3734: -- finally delete the node if not BOM node
3735: ------------------------------------------
3736:
3737: UPDATE cz_ps_nodes
3738: SET deleted_flag = '1'
3739: WHERE ps_node_id = p_ps_node_id
3740: AND ps_node_type NOT IN (436,437,438);
3741:
3742: --------------------
3743: -- Update tree_seq
3744: --------------------
3745:
3746: UPDATE cz_ps_nodes
3747: SET tree_seq = tree_seq - 1
3748: WHERE parent_id = l_parent_id
3749: AND devl_project_id = l_devl_project_id
3750: AND tree_seq > l_tree_seq
3842: l_ui_def_tbl t_num_array_tbl_type;
3843: l_name_tbl l_tName;
3844:
3845: l_model_name_tbl t_varchar_array_tbl_type;
3846: l_devl_project_id cz_ps_nodes.devl_project_id%TYPE;
3847: l_ui_style cz_ui_defs.ui_style%TYPE;
3848: l_seeded_flag cz_ui_defs.seeded_flag%TYPE;
3849: l_uidef_name cz_ui_defs.name%TYPE;
3850:
4323: l_user_name VARCHAR2(40);
4324: l_checkout_user cz_devl_projects.checkout_user%TYPE;
4325: l_ui_def_tbl t_num_array_tbl_type; -- the referencing models
4326: l_model_name_tbl t_varchar_array_tbl_type;
4327: l_devl_project_id cz_ps_nodes.devl_project_id%TYPE;
4328:
4329: l_rsq_name cz_rule_folders.name%TYPE;
4330:
4331: p_out_err INTEGER;
4675: BEGIN
4676:
4677: SELECT d.devl_project_id, d.name
4678: BULK COLLECT INTO x_ref_model_ids_tbl, x_ref_model_names_tbl
4679: FROM cz_ps_nodes p, cz_devl_projects d
4680: WHERE p.reference_id = p_model_id
4681: AND p.ps_node_type IN (263, 264)
4682: AND p.deleted_flag = '0'
4683: AND p.devl_project_id = d.devl_project_id
6453: (SELECT devl_project_id FROM cz_rules
6454: WHERE effectivity_set_id = p_eff_set_id
6455: AND deleted_flag = '0'
6456: UNION ALL
6457: SELECT devl_project_id FROM cz_ps_nodes
6458: WHERE effectivity_set_id = p_eff_set_id
6459: AND deleted_flag = '0');
6460:
6461: IF l_prj_name_tbl.COUNT > 0 THEN
6711: SELECT 1
6712: INTO l_prop_count
6713: FROM CZ_PSNODE_PROPVAL_V
6714: WHERE ps_node_id IN (select ps_node_id
6715: from cz_ps_nodes
6716: where cz_ps_nodes.deleted_flag = '0')
6717: AND property_id = p_property_id
6718: AND rownum < 2;
6719: EXCEPTION
6712: INTO l_prop_count
6713: FROM CZ_PSNODE_PROPVAL_V
6714: WHERE ps_node_id IN (select ps_node_id
6715: from cz_ps_nodes
6716: where cz_ps_nodes.deleted_flag = '0')
6717: AND property_id = p_property_id
6718: AND rownum < 2;
6719: EXCEPTION
6720: WHEN NO_DATA_FOUND THEN
7279: x_msg_count OUT NOCOPY NUMBER,
7280: x_msg_data OUT NOCOPY VARCHAR2)
7281: IS
7282: TYPE t_indexes IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7283: TYPE t_mask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
7284:
7285: NO_USG_EXISTS EXCEPTION;
7286: USG_IN_USE EXCEPTION;
7287: l_usage_count NUMBER := 0;
7336: BULK
7337: COLLECT
7338: INTO v_nodes,
7339: v_masks_nodes
7340: FROM cz_ps_nodes
7341: WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
7342: AND deleted_flag = '0'
7343: ORDER BY effective_usage_mask;
7344: EXCEPTION
7412: PROCEDURE DELETE_USAGE(usageId IN NUMBER, delete_status IN OUT NOCOPY VARCHAR2)
7413: AS
7414:
7415: TYPE t_indexes IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7416: TYPE t_mask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
7417: v_nodes t_indexes;
7418: v_rules t_indexes;
7419: v_masks_nodes t_mask;
7420: v_masks_rules t_mask;
7435: BULK
7436: COLLECT
7437: INTO v_nodes,
7438: v_masks_nodes
7439: FROM cz_ps_nodes
7440: WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
7441: ORDER BY effective_usage_mask;
7442: EXCEPTION
7443: WHEN OTHERS THEN
7488: END IF;
7489: END LOOP;
7490:
7491: FORALL i IN v_nodes.FIRST..v_nodes.LAST
7492: UPDATE cz_ps_nodes
7493: SET effective_usage_mask = v_masks_nodes(i)
7494: WHERE ps_node_id = v_nodes(i);
7495: END IF;
7496:
7580: IS
7581:
7582: TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7583: TYPE model_names_tbl IS TABLE OF cz_devl_projects.name%TYPE INDEX BY BINARY_INTEGER;
7584: TYPE t_mask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
7585: TYPE name_tbl IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
7586: TYPE t_checkout_user_tbl IS TABLE OF cz_devl_projects.checkout_user%TYPE INDEX BY BINARY_INTEGER;
7587: l_rp_folder NUMBER;
7588: l_rp_fld_tbl number_type_tbl;
7581:
7582: TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7583: TYPE model_names_tbl IS TABLE OF cz_devl_projects.name%TYPE INDEX BY BINARY_INTEGER;
7584: TYPE t_mask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
7585: TYPE name_tbl IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
7586: TYPE t_checkout_user_tbl IS TABLE OF cz_devl_projects.checkout_user%TYPE INDEX BY BINARY_INTEGER;
7587: l_rp_folder NUMBER;
7588: l_rp_fld_tbl number_type_tbl;
7589: l_rp_model_tbl number_type_tbl;
7868: SELECT d.devl_project_id,d.name, rp.enclosing_folder
7869: BULK
7870: COLLECT
7871: INTO l_ref_model_ids_tbl,l_ref_model_names_tbl,l_encl_devl_tbl
7872: FROM cz_ps_nodes p,
7873: cz_devl_projects d,
7874: cz_rp_entries rp
7875: WHERE p.reference_id = l_rp_model_ref(modelId)
7876: AND p.ps_node_type IN (263, 264)
7959: SELECT distinct devl_project_id
7960: BULK
7961: COLLECT
7962: INTO l_eff_tbl
7963: FROM cz_ps_nodes
7964: WHERE cz_ps_nodes.effectivity_set_id = l_eff_set_ref(i)
7965: AND cz_ps_nodes.deleted_flag = '0';
7966:
7967: rec_count := l_eff_ref.COUNT;
7960: BULK
7961: COLLECT
7962: INTO l_eff_tbl
7963: FROM cz_ps_nodes
7964: WHERE cz_ps_nodes.effectivity_set_id = l_eff_set_ref(i)
7965: AND cz_ps_nodes.deleted_flag = '0';
7966:
7967: rec_count := l_eff_ref.COUNT;
7968: IF (l_eff_tbl.COUNT > 0) THEN
7961: COLLECT
7962: INTO l_eff_tbl
7963: FROM cz_ps_nodes
7964: WHERE cz_ps_nodes.effectivity_set_id = l_eff_set_ref(i)
7965: AND cz_ps_nodes.deleted_flag = '0';
7966:
7967: rec_count := l_eff_ref.COUNT;
7968: IF (l_eff_tbl.COUNT > 0) THEN
7969: FOR I IN l_eff_tbl.FIRST..l_eff_tbl.LAST
8069:
8070: SELECT devl_project_id BULK COLLECT INTO l_devl_project_tbl
8071: FROM (SELECT devl_project_id
8072: FROM cz_ps_prop_vals psp,
8073: cz_ps_nodes ps
8074: WHERE psp.deleted_flag = '0'
8075: AND ps.deleted_flag = '0'
8076: AND ps.ps_node_id = psp.ps_node_id
8077: AND property_id = l_prp_ref(modelId)
8078: UNION
8079: SELECT devl_project_id
8080: FROM cz_item_type_properties itypr,
8081: cz_item_masters itm,
8082: cz_ps_nodes psnd
8083: WHERE itypr.deleted_flag = '0'
8084: AND itypr.item_type_id = itm.item_type_id
8085: AND itm.deleted_flag = '0'
8086: AND psnd.item_id = itm.item_id
8321: SELECT ps_node_id,effective_usage_mask,devl_project_id,name
8322: BULK
8323: COLLECT
8324: INTO v_nodes,v_masks_nodes,l_nodes_project_tbl,l_nodes_name_tbl
8325: FROM cz_ps_nodes
8326: WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
8327: ORDER BY effective_usage_mask;
8328: EXCEPTION
8329: WHEN OTHERS THEN
8649: TYPE tStringTable IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(15);
8650: TYPE tIntegerTable IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(15);
8651: TYPE tNumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8652:
8653: TYPE tPsNodeName IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
8654: TYPE tPropertyName IS TABLE OF cz_properties.name%TYPE INDEX BY BINARY_INTEGER;
8655: TYPE tExprId IS TABLE OF cz_expression_nodes.expr_node_id%TYPE INDEX BY BINARY_INTEGER;
8656: TYPE tExprParentId IS TABLE OF cz_expression_nodes.expr_parent_id%TYPE INDEX BY BINARY_INTEGER;
8657: TYPE tExprType IS TABLE OF cz_expression_nodes.expr_type%TYPE INDEX BY BINARY_INTEGER;
8729: nDebug := 40;
8730:
8731: IF(h_ModelPath.EXISTS(p_ps_node_id))THEN RETURN h_ModelPath(p_ps_node_id); END IF;
8732:
8733: FOR c_name IN (SELECT name, parent_id FROM cz_ps_nodes
8734: START WITH ps_node_id = p_ps_node_id
8735: CONNECT BY PRIOR parent_id = ps_node_id) LOOP
8736:
8737: IF(v_Name IS NULL)THEN
8738:
8739: v_Name := '''' || REPLACE(c_name.name, '''', '\''') || '''';
8740: h_NodeName(p_ps_node_id) := v_Name;
8741:
8742: FOR c_node IN (SELECT NULL FROM cz_ps_nodes WHERE deleted_flag = '0'
8743: AND devl_project_id = v_devl_project_id
8744: AND name = c_name.name
8745: AND ps_node_id <> p_ps_node_id)LOOP
8746: h_FullName(p_ps_node_id) := 1;
8745: AND ps_node_id <> p_ps_node_id)LOOP
8746: h_FullName(p_ps_node_id) := 1;
8747: EXIT;
8748: END LOOP;
8749: FOR c_node IN (SELECT NULL FROM cz_ps_nodes WHERE deleted_flag = '0'
8750: AND devl_project_id IN
8751: (SELECT component_id FROM cz_model_ref_expls
8752: WHERE deleted_flag = '0'
8753: AND model_id = v_devl_project_id
8794: h_ComponentId(v_Node) <> v_ExprPsNodeId(j))THEN
8795:
8796: BEGIN
8797:
8798: SELECT name INTO v_ModelName FROM cz_ps_nodes
8799: WHERE ps_node_id = h_ComponentId(v_Node);
8800: EXCEPTION
8801: WHEN OTHERS THEN
8802: NULL;
9903: IF((p_start_node_id IS NULL AND p_end_node_id IS NULL) OR (p_start_node_id = p_end_node_id))THEN RETURN NULL; END IF;
9904:
9905: SELECT ps_node_id, parent_id, persistent_node_id, virtual_flag
9906: BULK COLLECT INTO v_start_node_id_tab, v_start_parent_id_tab, v_start_persist_id_tab, v_start_virtual_tab
9907: FROM cz_ps_nodes
9908: WHERE deleted_flag = '0'
9909: START WITH ps_node_id = p_start_node_id
9910: CONNECT BY PRIOR parent_id = ps_node_id;
9911:
9910: CONNECT BY PRIOR parent_id = ps_node_id;
9911:
9912: SELECT ps_node_id, parent_id, persistent_node_id, virtual_flag
9913: BULK COLLECT INTO v_end_node_id_tab, v_end_parent_id_tab, v_end_persist_id_tab, v_end_virtual_tab
9914: FROM cz_ps_nodes
9915: WHERE deleted_flag = '0'
9916: START WITH ps_node_id = p_end_node_id
9917: CONNECT BY PRIOR parent_id = ps_node_id;
9918:
10001: INTO l_root_model_id, l_component_id, l_referring_node_id
10002: FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id=p_node_expl_id;
10003:
10004: IF l_referring_node_id IS NULL THEN
10005: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10006: WHERE ps_node_id=l_component_id;
10007:
10008: SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
10009: WHERE devl_project_id=l_model_id AND
10004: IF l_referring_node_id IS NULL THEN
10005: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10006: WHERE ps_node_id=l_component_id;
10007:
10008: SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
10009: WHERE devl_project_id=l_model_id AND
10010: persistent_node_id=p_node_persistent_node_id AND
10011: deleted_flag='0';
10012:
10015: END IF;
10016:
10017: ELSE
10018:
10019: SELECT persistent_node_id INTO l_persistent_node_id FROM CZ_PS_NODES
10020: WHERE ps_node_id=l_referring_node_id;
10021:
10022: IF p_node_persistent_node_id=l_persistent_node_id THEN
10023: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10019: SELECT persistent_node_id INTO l_persistent_node_id FROM CZ_PS_NODES
10020: WHERE ps_node_id=l_referring_node_id;
10021:
10022: IF p_node_persistent_node_id=l_persistent_node_id THEN
10023: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10024: WHERE ps_node_id=l_referring_node_id;
10025: ELSE
10026: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10027: WHERE ps_node_id=l_component_id;
10022: IF p_node_persistent_node_id=l_persistent_node_id THEN
10023: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10024: WHERE ps_node_id=l_referring_node_id;
10025: ELSE
10026: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10027: WHERE ps_node_id=l_component_id;
10028: END IF;
10029:
10030: SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
10026: SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
10027: WHERE ps_node_id=l_component_id;
10028: END IF;
10029:
10030: SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
10031: WHERE devl_project_id=l_model_id AND
10032: persistent_node_id=p_node_persistent_node_id AND
10033: deleted_flag='0';
10034:
10279: BEGIN
10280:
10281: IF(p_base_node_id = p_node_node_id)THEN RETURN NULL; END IF;
10282:
10283: SELECT devl_project_id INTO v_base_project_id FROM cz_ps_nodes
10284: WHERE ps_node_id = p_base_node_id;
10285:
10286: SELECT devl_project_id INTO v_node_project_id FROM cz_ps_nodes
10287: WHERE ps_node_id = p_node_node_id;
10282:
10283: SELECT devl_project_id INTO v_base_project_id FROM cz_ps_nodes
10284: WHERE ps_node_id = p_base_node_id;
10285:
10286: SELECT devl_project_id INTO v_node_project_id FROM cz_ps_nodes
10287: WHERE ps_node_id = p_node_node_id;
10288:
10289: IF(v_base_project_id = v_node_project_id)THEN
10290:
10499: v_rule_type NUMBER;
10500: v_component_id NUMBER;
10501: v_virtual_flag VARCHAR2(1);
10502: v_persistent_id NUMBER;
10503: v_base_name cz_ps_nodes.name%TYPE;
10504: v_node_name cz_ps_nodes.name%TYPE;
10505: v_expl_id NUMBER;
10506: v_class_name VARCHAR2(2000);
10507: v_model_id NUMBER;
10500: v_component_id NUMBER;
10501: v_virtual_flag VARCHAR2(1);
10502: v_persistent_id NUMBER;
10503: v_base_name cz_ps_nodes.name%TYPE;
10504: v_node_name cz_ps_nodes.name%TYPE;
10505: v_expl_id NUMBER;
10506: v_class_name VARCHAR2(2000);
10507: v_model_id NUMBER;
10508: v_rule_folder_id NUMBER;
10512: v_seeded_flag VARCHAR2(1);
10513: v_arg_count NUMBER;
10514: v_property_value VARCHAR2(4000);
10515: v_property_name cz_properties.name%TYPE;
10516: v_item_name cz_ps_nodes.name%TYPE;
10517: v_parent_name cz_ps_nodes.name%TYPE;
10518: v_parent_id cz_ps_nodes.parent_id%TYPE;
10519: v_inst_scope cz_rules.instantiation_scope%TYPE;
10520:
10513: v_arg_count NUMBER;
10514: v_property_value VARCHAR2(4000);
10515: v_property_name cz_properties.name%TYPE;
10516: v_item_name cz_ps_nodes.name%TYPE;
10517: v_parent_name cz_ps_nodes.name%TYPE;
10518: v_parent_id cz_ps_nodes.parent_id%TYPE;
10519: v_inst_scope cz_rules.instantiation_scope%TYPE;
10520:
10521: v_tExprId t_num_array_tbl_type;
10514: v_property_value VARCHAR2(4000);
10515: v_property_name cz_properties.name%TYPE;
10516: v_item_name cz_ps_nodes.name%TYPE;
10517: v_parent_name cz_ps_nodes.name%TYPE;
10518: v_parent_id cz_ps_nodes.parent_id%TYPE;
10519: v_inst_scope cz_rules.instantiation_scope%TYPE;
10520:
10521: v_tExprId t_num_array_tbl_type;
10522: v_tExprParentId t_num_array_tbl_type;
10606: x_name OUT NOCOPY VARCHAR2, x_virtual_flag OUT NOCOPY VARCHAR2) IS
10607: BEGIN
10608: SELECT persistent_node_id, name, virtual_flag
10609: INTO x_persistent_id, x_name, x_virtual_flag
10610: FROM cz_ps_nodes
10611: WHERE deleted_flag = '0' AND ps_node_id = p_node_id;
10612: EXCEPTION
10613: WHEN OTHERS THEN
10614: RAISE CZ_R_WRONG_EXPRESSION_NODE;
10923: WHERE deleted_flag = '0'
10924: AND property_id = v_tPropertyId(i);
10925:
10926: SELECT parent_id, name INTO v_parent_id, v_item_name
10927: FROM cz_ps_nodes
10928: WHERE deleted_flag = '0'
10929: AND ps_node_id = v_tExprPsNodeId(ii);
10930:
10931: SELECT name INTO v_parent_name
10928: WHERE deleted_flag = '0'
10929: AND ps_node_id = v_tExprPsNodeId(ii);
10930:
10931: SELECT name INTO v_parent_name
10932: FROM cz_ps_nodes
10933: WHERE deleted_flag = '0'
10934: AND ps_node_id = v_parent_id;
10935: EXCEPTION
10936: WHEN OTHERS THEN
11131: IF((p_start_node_id IS NULL AND p_end_node_id IS NULL) OR (p_start_node_id = p_end_node_id))THEN RETURN NULL; END IF;
11132:
11133: SELECT ps_node_id, parent_id, persistent_node_id
11134: BULK COLLECT INTO v_start_node_id_tab, v_start_parent_id_tab, v_start_persist_id_tab
11135: FROM cz_ps_nodes
11136: WHERE deleted_flag = '0'
11137: START WITH ps_node_id = p_start_node_id
11138: CONNECT BY PRIOR parent_id = ps_node_id;
11139:
11138: CONNECT BY PRIOR parent_id = ps_node_id;
11139:
11140: SELECT ps_node_id, parent_id, persistent_node_id
11141: BULK COLLECT INTO v_end_node_id_tab, v_end_parent_id_tab, v_end_persist_id_tab
11142: FROM cz_ps_nodes
11143: WHERE deleted_flag = '0'
11144: START WITH ps_node_id = p_end_node_id
11145: CONNECT BY PRIOR parent_id = ps_node_id;
11146:
11249: START WITH model_ref_expl_id = p_node_expl_id
11250: CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
11251:
11252: SELECT ps_node_type INTO v_base_node_type
11253: FROM cz_ps_nodes
11254: WHERE deleted_flag = '0'
11255: AND persistent_node_id = p_base_pers_id
11256: AND ROWNUM = 1;
11257:
11255: AND persistent_node_id = p_base_pers_id
11256: AND ROWNUM = 1;
11257:
11258: SELECT ps_node_type INTO v_node_node_type
11259: FROM cz_ps_nodes
11260: WHERE deleted_flag = '0'
11261: AND persistent_node_id = p_node_pers_id
11262: AND ROWNUM = 1;
11263:
11287: --If the node is a reference, it must be specified together with its own explosion.
11288:
11289: p_base_node_id := v_base_ref_id_tab(v_base_ref_expl_index);
11290:
11291: SELECT devl_project_id INTO v_base_devl_project_id FROM cz_ps_nodes
11292: WHERE deleted_flag = '0' AND ps_node_id = p_base_node_id;
11293: ELSE
11294: BEGIN
11295: SELECT ps_node_id, devl_project_id INTO p_base_node_id, v_base_devl_project_id FROM cz_ps_nodes
11291: SELECT devl_project_id INTO v_base_devl_project_id FROM cz_ps_nodes
11292: WHERE deleted_flag = '0' AND ps_node_id = p_base_node_id;
11293: ELSE
11294: BEGIN
11295: SELECT ps_node_id, devl_project_id INTO p_base_node_id, v_base_devl_project_id FROM cz_ps_nodes
11296: WHERE deleted_flag = '0'
11297: AND persistent_node_id = p_base_pers_id
11298: AND devl_project_id =
11299: (SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_base_component_id_tab(v_base_ref_expl_index));
11295: SELECT ps_node_id, devl_project_id INTO p_base_node_id, v_base_devl_project_id FROM cz_ps_nodes
11296: WHERE deleted_flag = '0'
11297: AND persistent_node_id = p_base_pers_id
11298: AND devl_project_id =
11299: (SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_base_component_id_tab(v_base_ref_expl_index));
11300: -- Bug 11706076 jquagent
11301: EXCEPTION WHEN NO_DATA_FOUND THEN
11302: NULL;
11303: END;
11308: --If the node is a reference, it must be specified together with its own explosion.
11309:
11310: p_node_node_id := v_node_ref_id_tab(v_node_ref_expl_index);
11311:
11312: SELECT devl_project_id INTO v_node_devl_project_id FROM cz_ps_nodes
11313: WHERE deleted_flag = '0' AND ps_node_id = p_node_node_id;
11314: ELSE
11315: BEGIN
11316: SELECT ps_node_id, devl_project_id INTO p_node_node_id, v_node_devl_project_id FROM cz_ps_nodes
11312: SELECT devl_project_id INTO v_node_devl_project_id FROM cz_ps_nodes
11313: WHERE deleted_flag = '0' AND ps_node_id = p_node_node_id;
11314: ELSE
11315: BEGIN
11316: SELECT ps_node_id, devl_project_id INTO p_node_node_id, v_node_devl_project_id FROM cz_ps_nodes
11317: WHERE deleted_flag = '0'
11318: AND persistent_node_id = p_node_pers_id
11319: AND devl_project_id =
11320: (SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_node_component_id_tab(v_node_ref_expl_index));
11316: SELECT ps_node_id, devl_project_id INTO p_node_node_id, v_node_devl_project_id FROM cz_ps_nodes
11317: WHERE deleted_flag = '0'
11318: AND persistent_node_id = p_node_pers_id
11319: AND devl_project_id =
11320: (SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_node_component_id_tab(v_node_ref_expl_index));
11321: -- Bug 10386050 and Bug 10407649 jquagent
11322: EXCEPTION WHEN NO_DATA_FOUND THEN
11323: NULL;
11324: END;
11759: WHILE(v_parent_id IS NOT NULL)LOOP
11760:
11761: SELECT parent_id, effective_from, effective_until, effectivity_set_id
11762: INTO v_parent_id, v_eff_from, v_eff_until, v_eff_set_id
11763: FROM cz_ps_nodes
11764: WHERE deleted_flag = '0'
11765: AND ps_node_id = v_parent_id;
11766:
11767: IF(v_eff_set_id IS NOT NULL)THEN