View: CZ_ITEM_PROPERTIES_V

Product:	CZ - Configurator
Description:
Implementation/DBA Data:	APPS.CZ_ITEM_PROPERTIES_V

View Text

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'

Columns