VIEW: APPS.CZ_COMMON_CHILDND_PROPS_V

Source

View Text - Preformatted

SELECT parent_id, property_id, property_name, data_type FROM (SELECT psnd.parent_id, psnd.ps_node_id, prop.property_id, prop.NAME AS property_name, prop.data_type FROM cz_properties prop, cz_ps_prop_vals vlue, cz_ps_nodes psnd WHERE prop.deleted_flag = '0' AND vlue.deleted_flag = '0' AND psnd.deleted_flag = '0' AND prop.property_id = vlue.property_id AND vlue.ps_node_id = psnd.ps_node_id UNION SELECT pref.parent_id, pref.ps_node_id, prop.property_id, prop.NAME AS property_name, prop.data_type FROM cz_properties prop, cz_ps_prop_vals vlue, cz_ps_nodes psnd, cz_ps_nodes pref WHERE prop.deleted_flag = '0' AND vlue.deleted_flag = '0' AND psnd.deleted_flag = '0' AND pref.deleted_flag = '0' AND pref.ps_node_type = 263 AND prop.property_id = vlue.property_id AND vlue.ps_node_id = psnd.ps_node_id AND pref.reference_id = psnd.ps_node_id UNION SELECT psnd.parent_id, psnd.ps_node_id, prop.property_id, prop.NAME AS property_name, prop.data_type FROM cz_properties prop, cz_item_type_properties itpr, cz_item_masters item, cz_ps_nodes psnd WHERE prop.deleted_flag = '0' AND item.deleted_flag = '0' AND itpr.deleted_flag = '0' AND psnd.deleted_flag = '0' AND prop.property_id = itpr.property_id AND itpr.item_type_id = item.item_type_id AND item.item_id = psnd.item_id UNION SELECT pref.parent_id, pref.ps_node_id, prop.property_id, prop.NAME AS property_name, prop.data_type FROM cz_properties prop, cz_item_type_properties itpr, cz_item_masters item, cz_ps_nodes psnd, cz_ps_nodes pref WHERE prop.deleted_flag = '0' AND item.deleted_flag = '0' AND itpr.deleted_flag = '0' AND psnd.deleted_flag = '0' AND pref.deleted_flag = '0' AND pref.ps_node_type = 263 AND prop.property_id = itpr.property_id AND itpr.item_type_id = item.item_type_id AND item.item_id = psnd.item_id AND pref.reference_id = psnd.ps_node_id) prpt WHERE 1=1 GROUP BY parent_id, property_id, property_name, data_type HAVING COUNT(*) = (SELECT COUNT(*) FROM cz_ps_nodes psn WHERE parent_id = prpt.parent_id AND deleted_flag = '0')

View Text - HTML Formatted

SELECT PARENT_ID
, PROPERTY_ID
, PROPERTY_NAME
, DATA_TYPE
FROM (SELECT PSND.PARENT_ID
, PSND.PS_NODE_ID
, PROP.PROPERTY_ID
, PROP.NAME AS PROPERTY_NAME
, PROP.DATA_TYPE
FROM CZ_PROPERTIES PROP
, CZ_PS_PROP_VALS VLUE
, CZ_PS_NODES PSND
WHERE PROP.DELETED_FLAG = '0'
AND VLUE.DELETED_FLAG = '0'
AND PSND.DELETED_FLAG = '0'
AND PROP.PROPERTY_ID = VLUE.PROPERTY_ID
AND VLUE.PS_NODE_ID = PSND.PS_NODE_ID UNION SELECT PREF.PARENT_ID
, PREF.PS_NODE_ID
, PROP.PROPERTY_ID
, PROP.NAME AS PROPERTY_NAME
, PROP.DATA_TYPE
FROM CZ_PROPERTIES PROP
, CZ_PS_PROP_VALS VLUE
, CZ_PS_NODES PSND
, CZ_PS_NODES PREF
WHERE PROP.DELETED_FLAG = '0'
AND VLUE.DELETED_FLAG = '0'
AND PSND.DELETED_FLAG = '0'
AND PREF.DELETED_FLAG = '0'
AND PREF.PS_NODE_TYPE = 263
AND PROP.PROPERTY_ID = VLUE.PROPERTY_ID
AND VLUE.PS_NODE_ID = PSND.PS_NODE_ID
AND PREF.REFERENCE_ID = PSND.PS_NODE_ID UNION SELECT PSND.PARENT_ID
, PSND.PS_NODE_ID
, PROP.PROPERTY_ID
, PROP.NAME AS PROPERTY_NAME
, PROP.DATA_TYPE
FROM CZ_PROPERTIES PROP
, CZ_ITEM_TYPE_PROPERTIES ITPR
, CZ_ITEM_MASTERS ITEM
, CZ_PS_NODES PSND
WHERE PROP.DELETED_FLAG = '0'
AND ITEM.DELETED_FLAG = '0'
AND ITPR.DELETED_FLAG = '0'
AND PSND.DELETED_FLAG = '0'
AND PROP.PROPERTY_ID = ITPR.PROPERTY_ID
AND ITPR.ITEM_TYPE_ID = ITEM.ITEM_TYPE_ID
AND ITEM.ITEM_ID = PSND.ITEM_ID UNION SELECT PREF.PARENT_ID
, PREF.PS_NODE_ID
, PROP.PROPERTY_ID
, PROP.NAME AS PROPERTY_NAME
, PROP.DATA_TYPE
FROM CZ_PROPERTIES PROP
, CZ_ITEM_TYPE_PROPERTIES ITPR
, CZ_ITEM_MASTERS ITEM
, CZ_PS_NODES PSND
, CZ_PS_NODES PREF
WHERE PROP.DELETED_FLAG = '0'
AND ITEM.DELETED_FLAG = '0'
AND ITPR.DELETED_FLAG = '0'
AND PSND.DELETED_FLAG = '0'
AND PREF.DELETED_FLAG = '0'
AND PREF.PS_NODE_TYPE = 263
AND PROP.PROPERTY_ID = ITPR.PROPERTY_ID
AND ITPR.ITEM_TYPE_ID = ITEM.ITEM_TYPE_ID
AND ITEM.ITEM_ID = PSND.ITEM_ID
AND PREF.REFERENCE_ID = PSND.PS_NODE_ID) PRPT
WHERE 1=1 GROUP BY PARENT_ID
, PROPERTY_ID
, PROPERTY_NAME
, DATA_TYPE HAVING COUNT(*) = (SELECT COUNT(*)
FROM CZ_PS_NODES PSN
WHERE PARENT_ID = PRPT.PARENT_ID
AND DELETED_FLAG = '0')