VIEW: APPS.CZ_PSNODE_PROPVAL_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)