View: CZ_EXPLMODEL_NODES_V

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

View Text

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

Columns