View: CZ_PSNODE_PROPVAL_V

Product:	CZ - Configurator
Description:	Lists all properties and values associated with psnodes
Implementation/DBA Data:	APPS.CZ_PSNODE_PROPVAL_V

View Text

SELECT PSN_PS_NODE_ID AS PS_NODE_ID
, PSN_PARENT_ID AS PARENT_ID
, PSN_PS_NODE_NAME AS PS_NODE_NAME
, PROP.PROPERTY_ID
, PROP.NAME AS PROPERTY_NAME
, PROP.DATA_TYPE
, PROP.SRC_APPLICATION_ID
, NVL ( DECODE (PSP.PROPERTY_ID
, NULL
, IPV.PROPERTY_VALUE
, PSP.DATA_VALUE)
, PROP.DEF_VALUE ) AS PROPERTY_VALUE
, NVL ( DECODE (PSP.PROPERTY_ID
, NULL
, IPV.PROPERTY_NUM_VALUE
, PSP.DATA_NUM_VALUE)
, PROP.DEF_NUM_VALUE ) AS PROPERTY_NUM_VALUE
, ITM_ITEM_ID AS ITEM_ID
, ITP.ITEM_TYPE_ID
, DECODE (PROP.DATA_TYPE
, 4
, DECODE (PSP.PROPERTY_ID
, NULL
, DECODE (IPV.PROPERTY_ID
, NULL
, 'ITMTYP'
, DECODE (IPV.PROPERTY_VALUE
, NULL
, 'ITEMDFLT'
, 'ITEM') )
, DECODE (PSP.DATA_VALUE
, NULL
, 'PSDFLT'
, 'PSVALUE') )
, DECODE (PSP.PROPERTY_ID
, NULL
, DECODE (IPV.PROPERTY_ID
, NULL
, 'ITMTYP'
, DECODE (IPV.PROPERTY_NUM_VALUE
, NULL
, 'ITEMDFLT'
, 'ITEM') )
, DECODE (PSP.DATA_NUM_VALUE
, NULL
, 'PSDFLT'
, 'PSVALUE') ) ) AS VALUESOURCE
, PSN_DEVL_PROJECT_ID AS DEVL_PROJECT_ID
, PROP.DEF_VALUE AS DEFAULT_VALUE
, PROP.DEF_NUM_VALUE
, DECODE ((DECODE (PSP.PROPERTY_ID
, NULL
, 0
, 1) + DECODE (ITP.PROPERTY_ID
, NULL
, 0
, 1) + DECODE (IPV.PROPERTY_ID
, NULL
, 0
, 1) )
, 1
, 'SINGLE'
, 'OVERLAPPED' ) AS PROP_ATTACHES
, DECODE (PSP.PROPERTY_ID
, NULL
, DECODE (IPV.PROPERTY_ID
, NULL
, ITP.ORIG_SYS_REF
, IPV.ORIG_SYS_REF )
, PSP.ORIG_SYS_REF ) AS ORIG_SYS_REF
, DECODE (PSP.PROPERTY_ID
, NULL
, DECODE (IPV.PROPERTY_ID
, NULL
, ITP.ORIG_SYS_REF
, IPV.ORIG_SYS_REF )
, PSP.ORIG_SYS_REF ) AS VALUE_ORIG_SYS_REF
, PROP.ORIG_SYS_REF AS PROPERTY_ORIG_SYS_REF
, '2005-04-28' AS VIEWREV
, PSN_PS_NODE_TYPE AS PS_NODE_TYPE
, DECODE (ITP.PROPERTY_ID
, NULL
, '0'
, '1') AS INHERITED_FLAG
FROM CZ_PROPERTIES PROP
, CZ_ITEM_TYPE_PROPERTIES ITP
, CZ_ITEM_PROPERTY_VALUES IPV
, CZ_PS_PROP_VALS PSP
, ( SELECT PSN.PS_NODE_ID AS PSN_PS_NODE_ID
, PSN.PARENT_ID AS PSN_PARENT_ID
, PSN.NAME AS PSN_PS_NODE_NAME
, PSN.DEVL_PROJECT_ID AS PSN_DEVL_PROJECT_ID
, PSN.PS_NODE_TYPE AS PSN_PS_NODE_TYPE
, ITM.ITEM_ID AS ITM_ITEM_ID
, ITM.ITEM_TYPE_ID AS ITM_ITEM_TYPE_ID
, ITM.ORIG_SYS_REF AS ITM_ORIG_SYS_REF
, ITM.REF_PART_NBR AS ITM_REF_PART_NBR
, NDPROP.PROPERTY_ID FROM CZ_ITEM_MASTERS ITM
, CZ_PS_NODES PSN
, ( SELECT PS_NODE_ID
, PROPERTY_ID FROM CZ_PS_PROP_VALS PSP WHERE DELETED_FLAG = '0' UNION SELECT PSND.PS_NODE_ID
, ITYPR.PROPERTY_ID FROM CZ_ITEM_TYPE_PROPERTIES ITYPR
, CZ_ITEM_MASTERS ITM
, CZ_PS_NODES PSND WHERE ITYPR.DELETED_FLAG = '0'
AND ITYPR.ITEM_TYPE_ID = ITM.ITEM_TYPE_ID
AND ITM.DELETED_FLAG = '0'
AND PSND.ITEM_ID = ITM.ITEM_ID
AND PSND.DELETED_FLAG = '0' ) NDPROP WHERE PSN.PS_NODE_TYPE != 263
AND PSN.ITEM_ID = ITM.ITEM_ID (+)
AND '0' = ITM.DELETED_FLAG (+)
AND PSN.DELETED_FLAG = '0'
AND NDPROP.PS_NODE_ID = PSN.PS_NODE_ID ) NDXPROP
WHERE PROP.PROPERTY_ID = NDXPROP.PROPERTY_ID
AND PROP.DELETED_FLAG = '0'
AND '0' = IPV.DELETED_FLAG (+)
AND NDXPROP.ITM_ITEM_ID = IPV.ITEM_ID (+)
AND NDXPROP.PROPERTY_ID = IPV.PROPERTY_ID (+)
AND '0' = ITP.DELETED_FLAG (+)
AND NDXPROP.ITM_ITEM_TYPE_ID = ITP.ITEM_TYPE_ID (+)
AND NDXPROP.PROPERTY_ID = ITP.PROPERTY_ID (+)
AND '0' = PSP.DELETED_FLAG (+)
AND NDXPROP.PSN_PS_NODE_ID = PSP.PS_NODE_ID (+)
AND NDXPROP.PROPERTY_ID = PSP.PROPERTY_ID (+) UNION ALL SELECT PSN_PS_NODE_ID AS PS_NODE_ID
, PSN_PARENT_ID AS PARENT_ID
, PSN_PS_NODE_NAME AS PS_NODE_NAME
, RPROP.PROPERTY_ID
, RPROP.NAME AS PROPERTY_NAME
, RPROP.DATA_TYPE
, RPROP.SRC_APPLICATION_ID
, NVL ( DECODE (RPSP.PROPERTY_ID
, NULL
, RIPV.PROPERTY_VALUE
, RPSP.DATA_VALUE)
, RPROP.DEF_VALUE ) AS PROPERTY_VALUE
, NVL ( DECODE (RPSP.PROPERTY_ID
, NULL
, RIPV.PROPERTY_NUM_VALUE
, RPSP.DATA_NUM_VALUE)
, RPROP.DEF_NUM_VALUE ) AS PROPERTY_NUM_VALUE
, ITM_ITEM_ID AS ITEM_ID
, RITP.ITEM_TYPE_ID
, DECODE (RPROP.DATA_TYPE
, 4
, DECODE (RPSP.PROPERTY_ID
, NULL
, DECODE (RIPV.PROPERTY_ID
, NULL
, 'ITMTYP'
, DECODE (RIPV.PROPERTY_VALUE
, NULL
, 'ITEMDFLT'
, 'ITEM') )
, DECODE (RPSP.DATA_VALUE
, NULL
, 'PSDFLT'
, 'PSVALUE') )
, DECODE (RPSP.PROPERTY_ID
, NULL
, DECODE (RIPV.PROPERTY_ID
, NULL
, 'ITMTYP'
, DECODE (RIPV.PROPERTY_NUM_VALUE
, NULL
, 'ITEMDFLT'
, 'ITEM') )
, DECODE (RPSP.DATA_NUM_VALUE
, NULL
, 'PSDFLT'
, 'PSVALUE') ) ) AS VALUESOURCE
, PSN_DEVL_PROJECT_ID AS DEVL_PROJECT_ID
, RPROP.DEF_VALUE AS DEFAULT_VALUE
, RPROP.DEF_NUM_VALUE
, DECODE ((DECODE (RPSP.PROPERTY_ID
, NULL
, 0
, 1) + DECODE (RITP.PROPERTY_ID
, NULL
, 0
, 1) + DECODE (RIPV.PROPERTY_ID
, NULL
, 0
, 1) )
, 1
, 'SINGLE'
, 'OVERLAPPED' ) AS PROP_ATTACHES
, DECODE (RPSP.PROPERTY_ID
, NULL
, DECODE (RIPV.PROPERTY_ID
, NULL
, RITP.ORIG_SYS_REF
, RIPV.ORIG_SYS_REF )
, RPSP.ORIG_SYS_REF ) AS ORIG_SYS_REF
, DECODE (RPSP.PROPERTY_ID
, NULL
, DECODE (RIPV.PROPERTY_ID
, NULL
, RITP.ORIG_SYS_REF
, RIPV.ORIG_SYS_REF )
, RPSP.ORIG_SYS_REF ) AS VALUE_ORIG_SYS_REF
, RPROP.ORIG_SYS_REF AS PROPERTY_ORIG_SYS_REF
, '2005-04-28' AS VIEWREV
, PSN_PS_NODE_TYPE AS PS_NODE_TYPE
, DECODE (RITP.PROPERTY_ID
, NULL
, '0'
, '1') AS INHERITED_FLAG
FROM CZ_PROPERTIES RPROP
, CZ_ITEM_TYPE_PROPERTIES RITP
, CZ_ITEM_PROPERTY_VALUES RIPV
, CZ_PS_PROP_VALS RPSP
, ( SELECT ZPSN.PS_NODE_ID AS PSN_PS_NODE_ID
, ZPSN.PARENT_ID AS PSN_PARENT_ID
, ZPSN.NAME AS PSN_PS_NODE_NAME
, ZPSN.DEVL_PROJECT_ID AS PSN_DEVL_PROJECT_ID
, ZPSN.PS_NODE_TYPE AS PSN_PS_NODE_TYPE
, ZPSN.REFERENCE_ID AS PSN_REFERENCE_ID
, ZITM.ITEM_ID AS ITM_ITEM_ID
, ZITM.ITEM_TYPE_ID AS ITM_ITEM_TYPE_ID
, ZITM.ORIG_SYS_REF AS ITM_ORIG_SYS_REF
, ZITM.REF_PART_NBR AS ITM_REF_PART_NBR
, RNDPROP.PROPERTY_ID FROM CZ_ITEM_MASTERS ZITM
, CZ_PS_NODES ZPSN
, ( SELECT RZPSN.PS_NODE_ID
, ZPSP.PROPERTY_ID FROM CZ_PS_NODES RZPSN
, CZ_PS_PROP_VALS ZPSP WHERE RZPSN.PS_NODE_TYPE = 263
AND RZPSN.DELETED_FLAG = '0'
AND RZPSN.REFERENCE_ID = ZPSP.PS_NODE_ID
AND ZPSP.DELETED_FLAG = '0' UNION SELECT REFPSND.PS_NODE_ID
, RZITYPR.PROPERTY_ID FROM CZ_ITEM_TYPE_PROPERTIES RZITYPR
, CZ_ITEM_MASTERS RZITM
, CZ_PS_NODES RZPSND
, CZ_PS_NODES REFPSND WHERE REFPSND.DELETED_FLAG = '0'
AND REFPSND.PS_NODE_TYPE = 263
AND REFPSND.REFERENCE_ID = RZPSND.PS_NODE_ID
AND RZITYPR.DELETED_FLAG = '0'
AND RZITYPR.ITEM_TYPE_ID = RZITM.ITEM_TYPE_ID
AND RZITM.DELETED_FLAG = '0'
AND RZPSND.ITEM_ID = RZITM.ITEM_ID
AND RZPSND.DELETED_FLAG = '0' ) RNDPROP
, CZ_PS_NODES RTPSN WHERE ZPSN.DELETED_FLAG = '0'
AND ZPSN.PS_NODE_TYPE = 263
AND ZPSN.REFERENCE_ID = RTPSN.PS_NODE_ID
AND RTPSN.ITEM_ID = ZITM.ITEM_ID (+)
AND '0' = ZITM.DELETED_FLAG (+)
AND RTPSN.DELETED_FLAG = '0'
AND RNDPROP.PS_NODE_ID = ZPSN.PS_NODE_ID ) RNDXPROP
WHERE RPROP.PROPERTY_ID = RNDXPROP.PROPERTY_ID
AND RPROP.DELETED_FLAG = '0'
AND '0' = RIPV.DELETED_FLAG (+)
AND RNDXPROP.ITM_ITEM_ID = RIPV.ITEM_ID (+)
AND RNDXPROP.PROPERTY_ID = RIPV.PROPERTY_ID (+)
AND '0' = RITP.DELETED_FLAG (+)
AND RNDXPROP.ITM_ITEM_TYPE_ID = RITP.ITEM_TYPE_ID (+)
AND RNDXPROP.PROPERTY_ID = RITP.PROPERTY_ID (+)
AND '0' = RPSP.DELETED_FLAG (+)
AND RNDXPROP.PSN_REFERENCE_ID = RPSP.PS_NODE_ID (+)
AND RNDXPROP.PROPERTY_ID = RPSP.PROPERTY_ID (+)

Columns