eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CZ_EXPLMODEL_NODES_V

Source

View Text - Preformatted


SELECT ( select decode (count (*), 0, '0', '1') from dual where ( xpl.ps_node_type = 263 and psn.parent_id is null ) or ( psn.ps_node_type = 264 and psn.reference_id = xpl.model_id and xpl.parent_expl_node_id is null ) ) as SUPPRESS_FLAG, nvl (psn.parent_id, xpl.referring_node_id) as STRUCTURAL_PARENT_ID, decode (psn.parent_id, xpl.model_id, psn.parent_id, null, xpl.referring_node_id, psn.devl_project_id, decode (psn.component_id, psn.reference_id, decode (pxpl.ps_node_type, 263, pxpl.referring_node_id, psn.parent_id ), psn.ps_node_id, decode (pxpl.ps_node_type, 263, pxpl.referring_node_id, psn.parent_id ), decode (xpl.ps_node_type, 263, xpl.referring_node_id, psn.parent_id ) ), psn.parent_id ) as effective_parent_id, decode (psn.ps_node_id, xpl.model_id, decode (xpl.referring_node_id, null, to_number (null), xpl.model_ref_expl_id), xpl.referring_node_id, xpl.parent_expl_node_id, psn.devl_project_id, xpl.model_ref_expl_id, psn.component_id, xpl.parent_expl_node_id, xpl.model_ref_expl_id) as PARENT_PSNODE_EXPL_ID, psn.PS_NODE_ID, psn.DEVL_PROJECT_ID, psn.INTL_TEXT_ID, psn.NAME, psn.RESOURCE_FLAG, psn.INITIAL_VALUE, psn.PARENT_ID, psn.MINIMUM, psn.MAXIMUM, psn.FEATURE_TYPE, psn.REFERENCE_ID, psn.MULTI_CONFIG_FLAG, psn.ORDER_SEQ_FLAG, psn.TREE_SEQ, psn.COUNTED_OPTIONS_FLAG, psn.UI_OMIT, psn.UI_SECTION, psn.BOM_TREATMENT, psn.COMPONENT_SEQUENCE_ID, psn.BOM_REQUIRED_FLAG, psn.SO_ITEM_TYPE_CODE, psn.MINIMUM_SELECTED, psn.MAXIMUM_SELECTED, psn.EFFECTIVE_USAGE_MASK, psn.EFFECTIVE_FROM, psn.EFFECTIVE_UNTIL, psn.DECIMAL_QTY_FLAG, psn.PERSISTENT_NODE_ID, psn.COMPONENT_SEQUENCE_PATH, psn.VIOLATION_TEXT_ID, psn.EFFECTIVITY_SET_ID, psn.QUOTEABLE_FLAG, psn.PRIMARY_UOM_CODE, psn.BOM_SORT_ORDER, psn.IB_TRACKABLE, psn.ACCUMULATOR_FLAG, psn.NOTES_TEXT_ID, psn.INSTANTIABLE_FLAG, psn.FROM_POPULATOR_ID, psn.PROPERTY_BACKPTR, psn.ITEM_TYPE_BACKPTR, psn.ITEM_ID, psn.ORIG_SYS_REF, psn.CHECKOUT_USER, psn.USER_NUM01, psn.USER_NUM02, psn.USER_NUM03, psn.USER_NUM04, psn.USER_STR01, psn.USER_STR02, psn.USER_STR03, psn.USER_STR04, xpl.MODEL_REF_EXPL_ID, xpl.PARENT_EXPL_NODE_ID, xpl.MODEL_ID, xpl.NODE_DEPTH, xpl.REFERRING_NODE_ID, xpl.CHILD_MODEL_EXPL_ID, xpl.EXPL_NODE_TYPE, xpl.HAS_TRACKABLE_CHILDREN, '2004-03-09' as viewrev, psn.COMPONENT_ID, psn.COMPONENT_ID as psn_component_id, xpl.COMPONENT_ID as xpl_component_id, psn.VIRTUAL_FLAG as psn_virtual_flag, xpl.VIRTUAL_FLAG as xpl_virtual_flag, psn.PS_NODE_TYPE as psn_ps_node_type, xpl.PS_NODE_TYPE as xpl_ps_node_type, psn.DELETED_FLAG as psn_deleted_flag, xpl.DELETED_FLAG as xpl_deleted_flag, psn.CREATION_DATE as psn_creation_date, xpl.CREATION_DATE as xpl_creation_date, psn.LAST_UPDATE_DATE as psn_last_update_date, xpl.LAST_UPDATE_DATE as xpl_last_update_date, psn.CREATED_BY as psn_created_by, xpl.CREATED_BY as xpl_created_by, psn.LAST_UPDATED_BY as psn_last_updated_by, xpl.LAST_UPDATED_BY as xpl_last_updated_by, psn.LAST_UPDATE_LOGIN as psn_last_update_login, xpl.LAST_UPDATE_LOGIN as xpl_last_update_login, PSN.INITIAL_NUM_VALUE, prj.INVENTORY_ITEM_ID as top_item_id, DECODE(DECODE (PSN.PS_NODE_TYPE, 436, 'BOM', 437, 'BOM', 438, 'BOM', 263, decode ((SELECT model_type FROM cz_devl_projects WHERE devl_project_id =psn.reference_id), 'A', 'BOM', 'P', 'BOM', 'nbRef'), 'non-BOM'), 'BOM', prj.ORGANIZATION_ID, to_number (null) ) AS ORGANIZATION_ID, DECODE (DECODE (PSN.PS_NODE_TYPE, 436, 'BOM', 437, 'BOM', 438, 'BOM', 263, decode (prj.model_type, 'A', 'BOM', 'P', 'BOM', 'nbRef'), 'non-BOM'), 'BOM', CZ_UTILS.CONV_NUM (SUBSTR ( SUBSTR (PSN.ORIG_SYS_REF, 1, (INSTR (PSN.ORIG_SYS_REF, ':') - 1)), (1 + INSTR (SUBSTR (PSN.ORIG_SYS_REF, 1, (INSTR (PSN.ORIG_SYS_REF, ':', -1) - 1)), '-', -1)) ) ), to_number (null) ) AS INVENTORY_ITEM_ID FROM CZ_PS_NODES psn, CZ_DEVL_PROJECTS prj, CZ_MODEL_REF_EXPLS xpl, cz_model_ref_expls pxpl WHERE pxpl.model_ref_expl_id (+) = xpl.parent_expl_node_id AND PXPL.DELETED_FLAG (+) = '0' AND PSN.component_id = XPL.component_id and PSN.deleted_flag = '0' and XPL.deleted_flag = '0' and PSN.devl_project_id = PRJ.devl_project_id and PRJ.deleted_flag = '0' and exists ( select /*+ index(inxpl cz_model_ref_expls_n8) */ 1 from cz_model_ref_expls inxpl where inxpl.deleted_flag = '0' and inxpl.model_id = xpl.model_id and component_id = PRJ.devl_project_id ) and ( nvl (xpl.referring_node_id, psn.ps_node_id) = psn.ps_node_id or psn.reference_id is null ) UNION ALL SELECT '0' AS SUPPRESS_FLAG, CONND.PARENT_ID AS STRUCTURAL_PARENT_ID, CONND.PARENT_ID AS EFFECTIVE_PARENT_ID, PARXPL.MODEL_REF_EXPL_ID as parent_psnode_expl_id, CONND.PS_NODE_ID, CONND.DEVL_PROJECT_ID, CONND.INTL_TEXT_ID, CONND.NAME, CONND.RESOURCE_FLAG, CONND.INITIAL_VALUE, CONND.PARENT_ID, CONND.MINIMUM, CONND.MAXIMUM, CONND.FEATURE_TYPE, CONND.REFERENCE_ID, CONND.MULTI_CONFIG_FLAG, CONND.ORDER_SEQ_FLAG, CONND.TREE_SEQ, CONND.COUNTED_OPTIONS_FLAG, CONND.UI_OMIT, CONND.UI_SECTION, CONND.BOM_TREATMENT, CONND.COMPONENT_SEQUENCE_ID, CONND.BOM_REQUIRED_FLAG, CONND.SO_ITEM_TYPE_CODE, CONND.MINIMUM_SELECTED, CONND.MAXIMUM_SELECTED, CONND.EFFECTIVE_USAGE_MASK, CONND.EFFECTIVE_FROM, CONND.EFFECTIVE_UNTIL, CONND.DECIMAL_QTY_FLAG, CONND.PERSISTENT_NODE_ID, CONND.COMPONENT_SEQUENCE_PATH, CONND.VIOLATION_TEXT_ID, CONND.EFFECTIVITY_SET_ID, CONND.QUOTEABLE_FLAG, CONND.PRIMARY_UOM_CODE, CONND.BOM_SORT_ORDER, CONND.IB_TRACKABLE, CONND.ACCUMULATOR_FLAG, CONND.NOTES_TEXT_ID, CONND.INSTANTIABLE_FLAG, CONND.FROM_POPULATOR_ID, CONND.PROPERTY_BACKPTR, CONND.ITEM_TYPE_BACKPTR, CONND.ITEM_ID, CONND.ORIG_SYS_REF, CONND.CHECKOUT_USER, CONND.USER_NUM01, CONND.USER_NUM02, CONND.USER_NUM03, CONND.USER_NUM04, CONND.USER_STR01, CONND.USER_STR02, CONND.USER_STR03, CONND.USER_STR04, -1 AS MODEL_REF_EXPL_ID, PARXPL.MODEL_REF_EXPL_ID AS PARENT_EXPL_NODE_ID, PARXPL.MODEL_ID, PARXPL.NODE_DEPTH + 1 AS NODE_DEPTH, TO_NUMBER (NULL) AS REFERRING_NODE_ID, TO_NUMBER (NULL) AS CHILD_MODEL_EXPL_ID, -1 AS EXPL_NODE_TYPE, '0' AS HAS_TRACKABLE_CHILDREN, '2004-03-09' AS VIEWREV, CONND.PS_NODE_ID AS COMPONENT_ID, CONND.COMPONENT_ID AS PSN_COMPONENT_ID, PARXPL.COMPONENT_ID AS XPL_COMPONENT_ID, CONND.VIRTUAL_FLAG AS PSN_VIRTUAL_FLAG, PARXPL.VIRTUAL_FLAG AS XPL_VIRTUAL_FLAG, CONND.PS_NODE_TYPE AS PSN_PS_NODE_TYPE, PARXPL.PS_NODE_TYPE AS XPL_PS_NODE_TYPE, CONND.DELETED_FLAG AS PSN_DELETED_FLAG, PARXPL.DELETED_FLAG AS XPL_DELETED_FLAG, CONND.CREATION_DATE AS PSN_CREATION_DATE, PARXPL.CREATION_DATE AS XPL_CREATION_DATE, CONND.LAST_UPDATE_DATE AS PSN_LAST_UPDATE_DATE, PARXPL.LAST_UPDATE_DATE AS XPL_LAST_UPDATE_DATE, CONND.CREATED_BY AS PSN_CREATED_BY, PARXPL.CREATED_BY AS XPL_CREATED_BY, CONND.LAST_UPDATED_BY AS PSN_LAST_UPDATED_BY, PARXPL.LAST_UPDATED_BY AS XPL_LAST_UPDATED_BY, CONND.LAST_UPDATE_LOGIN AS PSN_LAST_UPDATE_LOGIN, PARXPL.LAST_UPDATE_LOGIN AS XPL_LAST_UPDATE_LOGIN, CONND.INITIAL_NUM_VALUE, cprj.INVENTORY_ITEM_ID as top_item_id, to_number (null) as ORGANIZATION_ID, to_number (null) AS INVENTORY_ITEM_ID FROM cz_devl_projects cprj, cz_ps_nodes connd, cz_ps_nodes conpar, cz_model_ref_expls parxpl WHERE cprj.deleted_flag = '0' and cprj.devl_project_id = conpar.devl_project_id and parxpl.deleted_flag = '0' and parxpl.component_id = conpar.component_id and parxpl.component_id = connd.component_id and not exists ( select 1 from cz_model_ref_expls chkxp where chkxp.parent_expl_node_id = parxpl.model_ref_expl_id and chkxp.model_id = parxpl.model_id and chkxp.deleted_flag = '0' and chkxp.referring_node_id = connd.ps_node_id ) and conpar.deleted_flag = '0' and conpar.ps_node_id = connd.parent_id and connd.deleted_flag = '0' and connd.ps_node_type = 264 and exists ( select 1 from dual where parxpl.model_id = connd.reference_id union select 1 from cz_model_ref_expls circ where connd.reference_id = circ.component_id and circ.model_id = parxpl.model_id and circ.deleted_flag = '0' and circ.component_id = connd.component_id and circ.referring_node_id != connd.ps_node_id )

View Text - HTML Formatted

SELECT ( SELECT DECODE (COUNT (*)
, 0
, '0'
, '1')
FROM DUAL
WHERE ( XPL.PS_NODE_TYPE = 263
AND PSN.PARENT_ID IS NULL ) OR ( PSN.PS_NODE_TYPE = 264
AND PSN.REFERENCE_ID = XPL.MODEL_ID
AND XPL.PARENT_EXPL_NODE_ID IS NULL ) ) AS SUPPRESS_FLAG
, NVL (PSN.PARENT_ID
, XPL.REFERRING_NODE_ID) AS STRUCTURAL_PARENT_ID
, DECODE (PSN.PARENT_ID
, XPL.MODEL_ID
, PSN.PARENT_ID
, NULL
, XPL.REFERRING_NODE_ID
, PSN.DEVL_PROJECT_ID
, DECODE (PSN.COMPONENT_ID
, PSN.REFERENCE_ID
, DECODE (PXPL.PS_NODE_TYPE
, 263
, PXPL.REFERRING_NODE_ID
, PSN.PARENT_ID )
, PSN.PS_NODE_ID
, DECODE (PXPL.PS_NODE_TYPE
, 263
, PXPL.REFERRING_NODE_ID
, PSN.PARENT_ID )
, DECODE (XPL.PS_NODE_TYPE
, 263
, XPL.REFERRING_NODE_ID
, PSN.PARENT_ID ) )
, PSN.PARENT_ID ) AS EFFECTIVE_PARENT_ID
, DECODE (PSN.PS_NODE_ID
, XPL.MODEL_ID
, DECODE (XPL.REFERRING_NODE_ID
, NULL
, TO_NUMBER (NULL)
, XPL.MODEL_REF_EXPL_ID)
, XPL.REFERRING_NODE_ID
, XPL.PARENT_EXPL_NODE_ID
, PSN.DEVL_PROJECT_ID
, XPL.MODEL_REF_EXPL_ID
, PSN.COMPONENT_ID
, XPL.PARENT_EXPL_NODE_ID
, XPL.MODEL_REF_EXPL_ID) AS PARENT_PSNODE_EXPL_ID
, PSN.PS_NODE_ID
, PSN.DEVL_PROJECT_ID
, PSN.INTL_TEXT_ID
, PSN.NAME
, PSN.RESOURCE_FLAG
, PSN.INITIAL_VALUE
, PSN.PARENT_ID
, PSN.MINIMUM
, PSN.MAXIMUM
, PSN.FEATURE_TYPE
, PSN.REFERENCE_ID
, PSN.MULTI_CONFIG_FLAG
, PSN.ORDER_SEQ_FLAG
, PSN.TREE_SEQ
, PSN.COUNTED_OPTIONS_FLAG
, PSN.UI_OMIT
, PSN.UI_SECTION
, PSN.BOM_TREATMENT
, PSN.COMPONENT_SEQUENCE_ID
, PSN.BOM_REQUIRED_FLAG
, PSN.SO_ITEM_TYPE_CODE
, PSN.MINIMUM_SELECTED
, PSN.MAXIMUM_SELECTED
, PSN.EFFECTIVE_USAGE_MASK
, PSN.EFFECTIVE_FROM
, PSN.EFFECTIVE_UNTIL
, PSN.DECIMAL_QTY_FLAG
, PSN.PERSISTENT_NODE_ID
, PSN.COMPONENT_SEQUENCE_PATH
, PSN.VIOLATION_TEXT_ID
, PSN.EFFECTIVITY_SET_ID
, PSN.QUOTEABLE_FLAG
, PSN.PRIMARY_UOM_CODE
, PSN.BOM_SORT_ORDER
, PSN.IB_TRACKABLE
, PSN.ACCUMULATOR_FLAG
, PSN.NOTES_TEXT_ID
, PSN.INSTANTIABLE_FLAG
, PSN.FROM_POPULATOR_ID
, PSN.PROPERTY_BACKPTR
, PSN.ITEM_TYPE_BACKPTR
, PSN.ITEM_ID
, PSN.ORIG_SYS_REF
, PSN.CHECKOUT_USER
, PSN.USER_NUM01
, PSN.USER_NUM02
, PSN.USER_NUM03
, PSN.USER_NUM04
, PSN.USER_STR01
, PSN.USER_STR02
, PSN.USER_STR03
, PSN.USER_STR04
, XPL.MODEL_REF_EXPL_ID
, XPL.PARENT_EXPL_NODE_ID
, XPL.MODEL_ID
, XPL.NODE_DEPTH
, XPL.REFERRING_NODE_ID
, XPL.CHILD_MODEL_EXPL_ID
, XPL.EXPL_NODE_TYPE
, XPL.HAS_TRACKABLE_CHILDREN
, '2004-03-09' AS VIEWREV
, PSN.COMPONENT_ID
, PSN.COMPONENT_ID AS PSN_COMPONENT_ID
, XPL.COMPONENT_ID AS XPL_COMPONENT_ID
, PSN.VIRTUAL_FLAG AS PSN_VIRTUAL_FLAG
, XPL.VIRTUAL_FLAG AS XPL_VIRTUAL_FLAG
, PSN.PS_NODE_TYPE AS PSN_PS_NODE_TYPE
, XPL.PS_NODE_TYPE AS XPL_PS_NODE_TYPE
, PSN.DELETED_FLAG AS PSN_DELETED_FLAG
, XPL.DELETED_FLAG AS XPL_DELETED_FLAG
, PSN.CREATION_DATE AS PSN_CREATION_DATE
, XPL.CREATION_DATE AS XPL_CREATION_DATE
, PSN.LAST_UPDATE_DATE AS PSN_LAST_UPDATE_DATE
, XPL.LAST_UPDATE_DATE AS XPL_LAST_UPDATE_DATE
, PSN.CREATED_BY AS PSN_CREATED_BY
, XPL.CREATED_BY AS XPL_CREATED_BY
, PSN.LAST_UPDATED_BY AS PSN_LAST_UPDATED_BY
, XPL.LAST_UPDATED_BY AS XPL_LAST_UPDATED_BY
, PSN.LAST_UPDATE_LOGIN AS PSN_LAST_UPDATE_LOGIN
, XPL.LAST_UPDATE_LOGIN AS XPL_LAST_UPDATE_LOGIN
, PSN.INITIAL_NUM_VALUE
, PRJ.INVENTORY_ITEM_ID AS TOP_ITEM_ID
, DECODE(DECODE (PSN.PS_NODE_TYPE
, 436
, 'BOM'
, 437
, 'BOM'
, 438
, 'BOM'
, 263
, DECODE ((SELECT MODEL_TYPE
FROM CZ_DEVL_PROJECTS
WHERE DEVL_PROJECT_ID =PSN.REFERENCE_ID)
, 'A'
, 'BOM'
, 'P'
, 'BOM'
, 'NBREF')
, 'NON-BOM')
, 'BOM'
, PRJ.ORGANIZATION_ID
, TO_NUMBER (NULL) ) AS ORGANIZATION_ID
, DECODE (DECODE (PSN.PS_NODE_TYPE
, 436
, 'BOM'
, 437
, 'BOM'
, 438
, 'BOM'
, 263
, DECODE (PRJ.MODEL_TYPE
, 'A'
, 'BOM'
, 'P'
, 'BOM'
, 'NBREF')
, 'NON-BOM')
, 'BOM'
, CZ_UTILS.CONV_NUM (SUBSTR ( SUBSTR (PSN.ORIG_SYS_REF
, 1
, (INSTR (PSN.ORIG_SYS_REF
, ':') - 1))
, (1 + INSTR (SUBSTR (PSN.ORIG_SYS_REF
, 1
, (INSTR (PSN.ORIG_SYS_REF
, ':'
, -1) - 1))
, '-'
, -1)) ) )
, TO_NUMBER (NULL) ) AS INVENTORY_ITEM_ID
FROM CZ_PS_NODES PSN
, CZ_DEVL_PROJECTS PRJ
, CZ_MODEL_REF_EXPLS XPL
, CZ_MODEL_REF_EXPLS PXPL
WHERE PXPL.MODEL_REF_EXPL_ID (+) = XPL.PARENT_EXPL_NODE_ID
AND PXPL.DELETED_FLAG (+) = '0'
AND PSN.COMPONENT_ID = XPL.COMPONENT_ID
AND PSN.DELETED_FLAG = '0'
AND XPL.DELETED_FLAG = '0'
AND PSN.DEVL_PROJECT_ID = PRJ.DEVL_PROJECT_ID
AND PRJ.DELETED_FLAG = '0'
AND EXISTS ( SELECT /*+ INDEX(INXPL CZ_MODEL_REF_EXPLS_N8) */ 1
FROM CZ_MODEL_REF_EXPLS INXPL
WHERE INXPL.DELETED_FLAG = '0'
AND INXPL.MODEL_ID = XPL.MODEL_ID
AND COMPONENT_ID = PRJ.DEVL_PROJECT_ID )
AND ( NVL (XPL.REFERRING_NODE_ID
, PSN.PS_NODE_ID) = PSN.PS_NODE_ID OR PSN.REFERENCE_ID IS NULL ) UNION ALL SELECT '0' AS SUPPRESS_FLAG
, CONND.PARENT_ID AS STRUCTURAL_PARENT_ID
, CONND.PARENT_ID AS EFFECTIVE_PARENT_ID
, PARXPL.MODEL_REF_EXPL_ID AS PARENT_PSNODE_EXPL_ID
, CONND.PS_NODE_ID
, CONND.DEVL_PROJECT_ID
, CONND.INTL_TEXT_ID
, CONND.NAME
, CONND.RESOURCE_FLAG
, CONND.INITIAL_VALUE
, CONND.PARENT_ID
, CONND.MINIMUM
, CONND.MAXIMUM
, CONND.FEATURE_TYPE
, CONND.REFERENCE_ID
, CONND.MULTI_CONFIG_FLAG
, CONND.ORDER_SEQ_FLAG
, CONND.TREE_SEQ
, CONND.COUNTED_OPTIONS_FLAG
, CONND.UI_OMIT
, CONND.UI_SECTION
, CONND.BOM_TREATMENT
, CONND.COMPONENT_SEQUENCE_ID
, CONND.BOM_REQUIRED_FLAG
, CONND.SO_ITEM_TYPE_CODE
, CONND.MINIMUM_SELECTED
, CONND.MAXIMUM_SELECTED
, CONND.EFFECTIVE_USAGE_MASK
, CONND.EFFECTIVE_FROM
, CONND.EFFECTIVE_UNTIL
, CONND.DECIMAL_QTY_FLAG
, CONND.PERSISTENT_NODE_ID
, CONND.COMPONENT_SEQUENCE_PATH
, CONND.VIOLATION_TEXT_ID
, CONND.EFFECTIVITY_SET_ID
, CONND.QUOTEABLE_FLAG
, CONND.PRIMARY_UOM_CODE
, CONND.BOM_SORT_ORDER
, CONND.IB_TRACKABLE
, CONND.ACCUMULATOR_FLAG
, CONND.NOTES_TEXT_ID
, CONND.INSTANTIABLE_FLAG
, CONND.FROM_POPULATOR_ID
, CONND.PROPERTY_BACKPTR
, CONND.ITEM_TYPE_BACKPTR
, CONND.ITEM_ID
, CONND.ORIG_SYS_REF
, CONND.CHECKOUT_USER
, CONND.USER_NUM01
, CONND.USER_NUM02
, CONND.USER_NUM03
, CONND.USER_NUM04
, CONND.USER_STR01
, CONND.USER_STR02
, CONND.USER_STR03
, CONND.USER_STR04
, -1 AS MODEL_REF_EXPL_ID
, PARXPL.MODEL_REF_EXPL_ID AS PARENT_EXPL_NODE_ID
, PARXPL.MODEL_ID
, PARXPL.NODE_DEPTH + 1 AS NODE_DEPTH
, TO_NUMBER (NULL) AS REFERRING_NODE_ID
, TO_NUMBER (NULL) AS CHILD_MODEL_EXPL_ID
, -1 AS EXPL_NODE_TYPE
, '0' AS HAS_TRACKABLE_CHILDREN
, '2004-03-09' AS VIEWREV
, CONND.PS_NODE_ID AS COMPONENT_ID
, CONND.COMPONENT_ID AS PSN_COMPONENT_ID
, PARXPL.COMPONENT_ID AS XPL_COMPONENT_ID
, CONND.VIRTUAL_FLAG AS PSN_VIRTUAL_FLAG
, PARXPL.VIRTUAL_FLAG AS XPL_VIRTUAL_FLAG
, CONND.PS_NODE_TYPE AS PSN_PS_NODE_TYPE
, PARXPL.PS_NODE_TYPE AS XPL_PS_NODE_TYPE
, CONND.DELETED_FLAG AS PSN_DELETED_FLAG
, PARXPL.DELETED_FLAG AS XPL_DELETED_FLAG
, CONND.CREATION_DATE AS PSN_CREATION_DATE
, PARXPL.CREATION_DATE AS XPL_CREATION_DATE
, CONND.LAST_UPDATE_DATE AS PSN_LAST_UPDATE_DATE
, PARXPL.LAST_UPDATE_DATE AS XPL_LAST_UPDATE_DATE
, CONND.CREATED_BY AS PSN_CREATED_BY
, PARXPL.CREATED_BY AS XPL_CREATED_BY
, CONND.LAST_UPDATED_BY AS PSN_LAST_UPDATED_BY
, PARXPL.LAST_UPDATED_BY AS XPL_LAST_UPDATED_BY
, CONND.LAST_UPDATE_LOGIN AS PSN_LAST_UPDATE_LOGIN
, PARXPL.LAST_UPDATE_LOGIN AS XPL_LAST_UPDATE_LOGIN
, CONND.INITIAL_NUM_VALUE
, CPRJ.INVENTORY_ITEM_ID AS TOP_ITEM_ID
, TO_NUMBER (NULL) AS ORGANIZATION_ID
, TO_NUMBER (NULL) AS INVENTORY_ITEM_ID
FROM CZ_DEVL_PROJECTS CPRJ
, CZ_PS_NODES CONND
, CZ_PS_NODES CONPAR
, CZ_MODEL_REF_EXPLS PARXPL
WHERE CPRJ.DELETED_FLAG = '0'
AND CPRJ.DEVL_PROJECT_ID = CONPAR.DEVL_PROJECT_ID
AND PARXPL.DELETED_FLAG = '0'
AND PARXPL.COMPONENT_ID = CONPAR.COMPONENT_ID
AND PARXPL.COMPONENT_ID = CONND.COMPONENT_ID
AND NOT EXISTS ( SELECT 1
FROM CZ_MODEL_REF_EXPLS CHKXP
WHERE CHKXP.PARENT_EXPL_NODE_ID = PARXPL.MODEL_REF_EXPL_ID
AND CHKXP.MODEL_ID = PARXPL.MODEL_ID
AND CHKXP.DELETED_FLAG = '0'
AND CHKXP.REFERRING_NODE_ID = CONND.PS_NODE_ID )
AND CONPAR.DELETED_FLAG = '0'
AND CONPAR.PS_NODE_ID = CONND.PARENT_ID
AND CONND.DELETED_FLAG = '0'
AND CONND.PS_NODE_TYPE = 264
AND EXISTS ( SELECT 1
FROM DUAL
WHERE PARXPL.MODEL_ID = CONND.REFERENCE_ID UNION SELECT 1
FROM CZ_MODEL_REF_EXPLS CIRC
WHERE CONND.REFERENCE_ID = CIRC.COMPONENT_ID
AND CIRC.MODEL_ID = PARXPL.MODEL_ID
AND CIRC.DELETED_FLAG = '0'
AND CIRC.COMPONENT_ID = CONND.COMPONENT_ID
AND CIRC.REFERRING_NODE_ID != CONND.PS_NODE_ID )