VIEW: APPS.CZ_ITEM_PROPERTIES_V

Source

View Text - Preformatted

SELECT itmprp.property_id ,itmprp.item_id ,NVL(ipv.property_value, ITMPRP.def_value) as property_value ,NVL(ipv.property_num_value, ITMPRP.def_num_value) as property_num_value ,ipv.deleted_flag ,property_name ,itmprp.item_type_id ,itmprp.data_type ,itmprp.desc_text ,itmprp.orig_sys_ref ,itmprp.def_value ,itmprp.def_num_value ,NULL as DEF_TRANSLATED_TEXT ,NULL as TRANSLATED_TEXT ,DECODE ( (select count(*) from cz_item_property_values where item_id = itmprp.item_id and property_id = itmprp.property_id and deleted_flag = '0' and rownum < 2), 0,1, 1,0) as INHERITED_FLAG ,itmprp.SRC_APPLICATION_ID FROM (select prp.name as property_name ,prp.def_value ,prp.def_num_value ,prp.data_type ,prp.desc_text ,itp.orig_sys_ref ,prp.property_id ,itp.item_type_id ,itm.item_id ,itm.ref_part_nbr ,itm.SRC_APPLICATION_ID from cz_properties prp ,cz_item_type_properties itp ,cz_item_masters itm where prp.deleted_flag = '0' and prp.property_id = itp.property_id and itp.deleted_flag = '0' and itp.ITEM_TYPE_ID = itm.ITEM_TYPE_ID and itm.DELETED_FLAG = '0' ) ITMPRP ,cz_item_property_values ipv WHERE itmprp.data_type <> 8 and ipv.ITEM_ID(+) = itmPRP.item_id and ipv.PROPERTY_ID(+) = ITMprp.property_id and ipv.DELETED_FLAG(+) = '0' UNION ALL SELECT itmprp.property_id ,itmprp.item_id ,NVL(ipv.property_value, ITMPRP.def_value) as property_value ,NVL(ipv.property_num_value, ITMPRP.def_num_value) as property_num_value ,ipv.deleted_flag, property_name ,itmprp.item_type_id ,itmprp.data_type ,itmprp.desc_text ,itmprp.orig_sys_ref ,itmprp.def_value ,itmprp.def_num_value ,(SELECT TEXT_STR FROM CZ_INTL_TEXTS WHERE intl_text_id = itmprp.def_num_value )as DEF_TRANSLATED_TEXT ,(SELECT TEXT_STR FROM CZ_INTL_TEXTS WHERE intl_text_id = nvl(ipv.property_num_value, ITMPRP.def_num_value)) as TRANSLATED_TEXT ,DECODE ( (select count(*) from cz_item_property_values where item_id = itmprp.item_id and property_id = itmprp.property_id and deleted_flag = '0' and rownum < 2), 0,1, 1,0) as INHERITED_FLAG ,itmprp.SRC_APPLICATION_ID FROM (select prp.name as property_name ,prp.def_value ,prp.def_num_value ,prp.data_type ,prp.desc_text ,itp.orig_sys_ref ,prp.property_id ,itp.item_type_id ,itm.item_id ,itm.ref_part_nbr ,itm.SRC_APPLICATION_ID from cz_properties prp ,cz_item_type_properties itp ,cz_item_masters itm where prp.deleted_flag = '0' and prp.property_id = itp.property_id and itp.deleted_flag = '0' and itp.ITEM_TYPE_ID = itm.ITEM_TYPE_ID and itm.DELETED_FLAG = '0' ) ITMPRP ,cz_item_property_values ipv WHERE itmprp.data_type = 8 and ipv.ITEM_ID(+) = itmPRP.item_id and ipv.PROPERTY_ID(+) = ITMprp.property_id and ipv.DELETED_FLAG(+) = '0'

View Text - HTML Formatted

SELECT ITMPRP.PROPERTY_ID
, ITMPRP.ITEM_ID
, NVL(IPV.PROPERTY_VALUE
, ITMPRP.DEF_VALUE) AS PROPERTY_VALUE
, NVL(IPV.PROPERTY_NUM_VALUE
, ITMPRP.DEF_NUM_VALUE) AS PROPERTY_NUM_VALUE
, IPV.DELETED_FLAG
, PROPERTY_NAME
, ITMPRP.ITEM_TYPE_ID
, ITMPRP.DATA_TYPE
, ITMPRP.DESC_TEXT
, ITMPRP.ORIG_SYS_REF
, ITMPRP.DEF_VALUE
, ITMPRP.DEF_NUM_VALUE
, NULL AS DEF_TRANSLATED_TEXT
, NULL AS TRANSLATED_TEXT
, DECODE ( (SELECT COUNT(*)
FROM CZ_ITEM_PROPERTY_VALUES
WHERE ITEM_ID = ITMPRP.ITEM_ID
AND PROPERTY_ID = ITMPRP.PROPERTY_ID
AND DELETED_FLAG = '0'
AND ROWNUM < 2)
, 0
, 1
, 1
, 0) AS INHERITED_FLAG
, ITMPRP.SRC_APPLICATION_ID
FROM (SELECT PRP.NAME AS PROPERTY_NAME
, PRP.DEF_VALUE
, PRP.DEF_NUM_VALUE
, PRP.DATA_TYPE
, PRP.DESC_TEXT
, ITP.ORIG_SYS_REF
, PRP.PROPERTY_ID
, ITP.ITEM_TYPE_ID
, ITM.ITEM_ID
, ITM.REF_PART_NBR
, ITM.SRC_APPLICATION_ID
FROM CZ_PROPERTIES PRP
, CZ_ITEM_TYPE_PROPERTIES ITP
, CZ_ITEM_MASTERS ITM
WHERE PRP.DELETED_FLAG = '0'
AND PRP.PROPERTY_ID = ITP.PROPERTY_ID
AND ITP.DELETED_FLAG = '0'
AND ITP.ITEM_TYPE_ID = ITM.ITEM_TYPE_ID
AND ITM.DELETED_FLAG = '0' ) ITMPRP
, CZ_ITEM_PROPERTY_VALUES IPV
WHERE ITMPRP.DATA_TYPE <> 8
AND IPV.ITEM_ID(+) = ITMPRP.ITEM_ID
AND IPV.PROPERTY_ID(+) = ITMPRP.PROPERTY_ID
AND IPV.DELETED_FLAG(+) = '0' UNION ALL SELECT ITMPRP.PROPERTY_ID
, ITMPRP.ITEM_ID
, NVL(IPV.PROPERTY_VALUE
, ITMPRP.DEF_VALUE) AS PROPERTY_VALUE
, NVL(IPV.PROPERTY_NUM_VALUE
, ITMPRP.DEF_NUM_VALUE) AS PROPERTY_NUM_VALUE
, IPV.DELETED_FLAG
, PROPERTY_NAME
, ITMPRP.ITEM_TYPE_ID
, ITMPRP.DATA_TYPE
, ITMPRP.DESC_TEXT
, ITMPRP.ORIG_SYS_REF
, ITMPRP.DEF_VALUE
, ITMPRP.DEF_NUM_VALUE
, (SELECT TEXT_STR
FROM CZ_INTL_TEXTS
WHERE INTL_TEXT_ID = ITMPRP.DEF_NUM_VALUE )AS DEF_TRANSLATED_TEXT
, (SELECT TEXT_STR
FROM CZ_INTL_TEXTS
WHERE INTL_TEXT_ID = NVL(IPV.PROPERTY_NUM_VALUE
, ITMPRP.DEF_NUM_VALUE)) AS TRANSLATED_TEXT
, DECODE ( (SELECT COUNT(*)
FROM CZ_ITEM_PROPERTY_VALUES
WHERE ITEM_ID = ITMPRP.ITEM_ID
AND PROPERTY_ID = ITMPRP.PROPERTY_ID
AND DELETED_FLAG = '0'
AND ROWNUM < 2)
, 0
, 1
, 1
, 0) AS INHERITED_FLAG
, ITMPRP.SRC_APPLICATION_ID
FROM (SELECT PRP.NAME AS PROPERTY_NAME
, PRP.DEF_VALUE
, PRP.DEF_NUM_VALUE
, PRP.DATA_TYPE
, PRP.DESC_TEXT
, ITP.ORIG_SYS_REF
, PRP.PROPERTY_ID
, ITP.ITEM_TYPE_ID
, ITM.ITEM_ID
, ITM.REF_PART_NBR
, ITM.SRC_APPLICATION_ID
FROM CZ_PROPERTIES PRP
, CZ_ITEM_TYPE_PROPERTIES ITP
, CZ_ITEM_MASTERS ITM
WHERE PRP.DELETED_FLAG = '0'
AND PRP.PROPERTY_ID = ITP.PROPERTY_ID
AND ITP.DELETED_FLAG = '0'
AND ITP.ITEM_TYPE_ID = ITM.ITEM_TYPE_ID
AND ITM.DELETED_FLAG = '0' ) ITMPRP
, CZ_ITEM_PROPERTY_VALUES IPV
WHERE ITMPRP.DATA_TYPE = 8
AND IPV.ITEM_ID(+) = ITMPRP.ITEM_ID
AND IPV.PROPERTY_ID(+) = ITMPRP.PROPERTY_ID
AND IPV.DELETED_FLAG(+) = '0'