[Home] [Help]
1383: v_insertstring := v_insertstring || ' IF (UPPER(cz_pb_mgr.v_insert_table_name) = ''CZ_ITEM_TYPE_PROPERTIES'') THEN ';
1384: v_insertstring := v_insertstring || ' UPDATE cz_item_type_properties' || cz_pb_mgr.v_db_link || ' SET DELETED_FLAG = ''0'' ';
1385: v_insertstring := v_insertstring || ' WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1386: v_insertstring := v_insertstring || ' AND ITEM_TYPE_ID = ' || plsql_table_name2 || '(m); ';
1387: v_insertstring := v_insertstring || ' ELSIF (UPPER(cz_pb_mgr.v_insert_table_name) = ''CZ_ITEM_PROPERTY_VALUES'') THEN ';
1388: v_insertstring := v_insertstring || ' UPDATE cz_item_property_values' || cz_pb_mgr.v_db_link || ' t SET ( PROPERTY_VALUE )= (SELECT PROPERTY_VALUE ';
1389: v_insertstring := v_insertstring || ' FROM cz_item_property_values WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1390: v_insertstring := v_insertstring || ' AND ITEM_ID = ' || plsql_table_name2 || '(m) ) WHERE t.PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1391: v_insertstring := v_insertstring || ' AND t.ITEM_ID = ' || plsql_table_name2 || '(m); ';
1384: v_insertstring := v_insertstring || ' UPDATE cz_item_type_properties' || cz_pb_mgr.v_db_link || ' SET DELETED_FLAG = ''0'' ';
1385: v_insertstring := v_insertstring || ' WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1386: v_insertstring := v_insertstring || ' AND ITEM_TYPE_ID = ' || plsql_table_name2 || '(m); ';
1387: v_insertstring := v_insertstring || ' ELSIF (UPPER(cz_pb_mgr.v_insert_table_name) = ''CZ_ITEM_PROPERTY_VALUES'') THEN ';
1388: v_insertstring := v_insertstring || ' UPDATE cz_item_property_values' || cz_pb_mgr.v_db_link || ' t SET ( PROPERTY_VALUE )= (SELECT PROPERTY_VALUE ';
1389: v_insertstring := v_insertstring || ' FROM cz_item_property_values WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1390: v_insertstring := v_insertstring || ' AND ITEM_ID = ' || plsql_table_name2 || '(m) ) WHERE t.PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1391: v_insertstring := v_insertstring || ' AND t.ITEM_ID = ' || plsql_table_name2 || '(m); ';
1392: v_insertstring := v_insertstring || ' END IF; END; END LOOP; COMMIT; END; ';
1385: v_insertstring := v_insertstring || ' WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1386: v_insertstring := v_insertstring || ' AND ITEM_TYPE_ID = ' || plsql_table_name2 || '(m); ';
1387: v_insertstring := v_insertstring || ' ELSIF (UPPER(cz_pb_mgr.v_insert_table_name) = ''CZ_ITEM_PROPERTY_VALUES'') THEN ';
1388: v_insertstring := v_insertstring || ' UPDATE cz_item_property_values' || cz_pb_mgr.v_db_link || ' t SET ( PROPERTY_VALUE )= (SELECT PROPERTY_VALUE ';
1389: v_insertstring := v_insertstring || ' FROM cz_item_property_values WHERE PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1390: v_insertstring := v_insertstring || ' AND ITEM_ID = ' || plsql_table_name2 || '(m) ) WHERE t.PROPERTY_ID = ' || plsql_table_name1 || '(m) ';
1391: v_insertstring := v_insertstring || ' AND t.ITEM_ID = ' || plsql_table_name2 || '(m); ';
1392: v_insertstring := v_insertstring || ' END IF; END; END LOOP; COMMIT; END; ';
1393:
1984: plsql_table_list(rec_count).table_name := 'cz_pb_mgr.mm_insert_array2';
1985: rec_count := rec_count + 1;
1986: plsql_table_list(rec_count).col_name := 'orig_sys_ref';
1987: plsql_table_list(rec_count).table_name := 'cz_pb_mgr.mm_insert_array3';
1988: insert_item_tables('cz_item_property_values'
1989: ,'property_id'
1990: ,'item_id'
1991: ,cz_pb_mgr.v_db_link
1992: ,plsql_table_list
2004: || 'FOR I IN cz_pb_mgr.mm_insert_array2.FIRST..cz_pb_mgr.mm_insert_array2.LAST'
2005: || ' '
2006: || 'LOOP'
2007: || ' '
2008: || 'UPDATE cz_item_property_values'
2009: || cz_pb_mgr.v_db_link
2010: || ' t'
2011: || ' '
2012: || 'SET t.orig_sys_ref = NULL'
17668: and psn.deleted_flag = '0' AND psp.deleted_Flag = '0' and psp.property_id = p.property_id
17669: and p.deleted_flag = '0'
17670: UNION
17671: SELECT ipv.property_id, p.data_type, p.name, nvl(p.src_application_id, 0) AS src_application_id
17672: from cz_ps_nodes psn, cz_item_property_values ipv, cz_properties p
17673: where psn.devl_project_id = cz_pb_mgr.v_models_to_be_exported(i) and psn.item_id = ipv.item_id
17674: and psn.deleted_flag = '0' and ipv.deleted_flag = '0' and ipv.property_id = p.property_id
17675: and p.deleted_flag = '0');
17676:
17786: , NVL(orig_sys_ref, cz_pb_mgr.null_string)
17787: ),NVL(orig_sys_ref, cz_pb_mgr.null_string)
17788: BULK COLLECT INTO cz_pb_mgr.v_it_prop_vals_id_ref, cz_pb_mgr.v_it_prop_vals_item_ref
17789: ,v_tgt_item_prop_val_orig_ref,v_src_item_prop_val_orig_ref
17790: FROM cz_item_property_values
17791: WHERE item_id = cz_pb_mgr.v_item_masters_tbl(i) AND deleted_flag = '0';
17792: EXCEPTION
17793: WHEN NO_DATA_FOUND
17794: THEN
24076:
24077: s_item_id := v_prop_vals_item_id_ref(j);
24078:
24079: --If s_item_id is not null, than the value is defined on the item, not on the ps node.
24080: --If the value exists on the source in cz_item_property_values, than the value is defined
24081: --on the item directly, not through item type.
24082:
24083: IF((s_item_id IS NOT NULL) AND (mm_get_ifexists_on_source(s_prop_id, 'ITPROPVAL') = '1'))THEN
24084:
24119:
24120: --Now we deal with properties associated with Item Types.
24121:
24122: --This is the same algorithm as above, but the sources of property_id and property_num_value are different arrays and
24123: --a different query must be used (against cz_item_property_values) when the property and the item exist on the target.
24124: --For item_id, the second part of the above block must be useful.
24125:
24126: IF(cz_pb_mgr.v_item_prop_type_tbl.COUNT > 0)THEN
24127:
24281: SELECT '1'
24282: INTO record_exists
24283: FROM DUAL
24284: WHERE EXISTS(SELECT property_id
24285: FROM cz_item_property_values
24286: WHERE property_id = source_id);
24287: END IF;
24288:
24289: RETURN record_exists;