View: CSS_DEF_DEFECTS_ALL_NO_RES_V

View Text

SELECT DEF.DEFECT_ID
, DEF.OBJECT_VERSION_NUMBER
, DEF.DEFECT_NUMBER
, TO_NUMBER(NULL) ORG_ID /* OBSOLETED 11.5.4+ */
, DEF.TERRITORY_ID
, TER.NAME TERRITORY_NAME
, DEF.CATEGORY_TYPE
, PCA.PROBLEM_CATEGORY_ID
, PCA.NAME PROBLEM_CATEGORY_NAME
, PTY.PROBLEM_TYPE_ID
, PTY.NAME PROBLEM_TYPE_NAME
, PHA.PHASE_ID
, PHA.NAME PHASE_NAME
, PTP.PROB_TYPE_PHASE_ID PROBLEM_TYPE_PHASE_ID
, TO_NUMBER(NULL) REASON_ID /* OBSOLETED 11.5.4+ */
, NULL REASON_NAME /* OBSOLETED 11.5.4+ */
, DEF.STATUS_ID
, STA.NAME STATUS_NAME
, DEF.OPEN_FLAG
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('FND'
, 'YES_NO'
, OPEN_FLAG)
, 1
, 80) OPEN_MEANING
, TO_NUMBER(NULL) STATUS_REASON_ID /* OBSOLETED 11.5.3 */
, NULL STATUS_REASON_NAME /* OBSOLETED 11.5.3 */
, DEF.SEVERITY_ID
, SEV.NAME SEVERITY_NAME
, NULL DESC_STATUS_REASON /* OBSOLETED 11.5.3 */
, DEF.PRIORITY_ID
, PRI.NAME PRIORITY_NAME
, PROD.ORGANIZATION_ID INV_ORGANIZATION_ID
, DEF.PRODUCT_INV_ITEM_ID
, PROD.DESCRIPTION PRODUCT_DESCRIPTION
, PROD.CONCATENATED_SEGMENTS PRODUCT_CONC_SEGMENTS
, DEF.PRODUCT_REVISION PRODUCT_REVISION
, DECODE(DEF.PRODUCT_REVISION
, NULL
, DEF.PRODUCT_REVISION_DESC
, PROD_REV.REVISION_DESCRIPTION) PRODUCT_REVISION_DESC
, DECODE(DEF.FIRST_FOUND_IN_REVISION
, NULL
, DECODE(DEF.FIRST_FOUND_IN_REVISION_DESC
, NULL
, TO_NUMBER(NULL)
, DEF.PRODUCT_INV_ITEM_ID)
, DEF.PRODUCT_INV_ITEM_ID) FIRST_FOUND_IN_INV_ITEM_ID
, DECODE(DEF.FIRST_FOUND_IN_REVISION
, NULL
, DECODE(DEF.FIRST_FOUND_IN_REVISION_DESC
, NULL
, NULL
, PROD.DESCRIPTION)
, PROD.DESCRIPTION) FIRST_FOUND_IN_DESCRIPTION
, DECODE(DEF.FIRST_FOUND_IN_REVISION
, NULL
, DECODE(DEF.FIRST_FOUND_IN_REVISION_DESC
, NULL
, NULL
, PROD.CONCATENATED_SEGMENTS)
, PROD.CONCATENATED_SEGMENTS) FIRST_FOUND_IN_CONC_SEGMENTS
, DEF.FIRST_FOUND_IN_REVISION FIRST_FOUND_IN_REVISION
, DECODE(DEF.FIRST_FOUND_IN_REVISION
, NULL
, DEF.FIRST_FOUND_IN_REVISION_DESC
, FFI_REV.REVISION_DESCRIPTION) FIRST_FOUND_IN_REVISION_DESC
, DEF.COMPONENT_INV_ITEM_ID
, COMP.DESCRIPTION COMPONENT_DESCRIPTION
, COMP.CONCATENATED_SEGMENTS COMPONENT_CONC_SEGMENTS
, DEF.COMPONENT_REVISION COMPONENT_REVISION
, DECODE(DEF.COMPONENT_REVISION
, NULL
, DEF.COMPONENT_REVISION_DESC
, COMP_REV.REVISION_DESCRIPTION) COMPONENT_REVISION_DESC
, DEF.SUB_COMPONENT_INV_ITEM_ID SUB_COMP_INV_ITEM_ID
, SCOM.DESCRIPTION SUB_COMP_DESCRIPTION
, SCOM.CONCATENATED_SEGMENTS SUB_COMPONENT_CONC_SEGMENTS
, DEF.SUB_COMPONENT_REVISION SUB_COMPONENT_REVISION
, DECODE(DEF.SUB_COMPONENT_REVISION
, NULL
, DEF.SUB_COMPONENT_REVISION_DESC
, SCOM_REV.REVISION_DESCRIPTION) SUB_COMPONENT_REVISION_DESC
, DEF.FREQUENCY_CODE
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('CSS'
, 'CSS_DEF_FREQUENCY_CODE'
, DEF.FREQUENCY_CODE)
, 1
, 80) FREQUENCY_MEANING
, NULL TESTCASE /* OBSOLETED 11.5.4+ */
, DEF.PUBLISH_EXT_FLAG
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('FND'
, 'YES_NO'
, DEF.PUBLISH_EXT_FLAG)
, 1
, 80) PUBLISH_EXT_MEANING
, DEF.LIFE_CYCLE_CODE
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('CSS'
, 'CSS_DEF_LIFE_CYCLE_CODE'
, DEF.LIFE_CYCLE_CODE)
, 1
, 80) LIFE_CYCLE_MEANING
, NULL TIER_CODE /* OBSOLETED 11.5.4+ */
, NULL TIER_MEANING /* OBSOLETED 11.5.4+ */
, DEF.SUMMARY
, NULL KEYWORDS /* OBSOLETED 11.5.3 */
, NULL TEST_TYPE /* OBSOLETED 11.5.4+ */
, NULL TEST_TYPE_MEANING /* OBSOLETED 11.5.4+ */
, NULL TEST_NAME /* OBSOLETED 11.5.4+ */
, NULL COMMITMENT_CODE /* OBSOLETED 11.5.3 */
, NULL COMMITMENT_MEANING /* OBSOLETED 11.5.3 */
, DEF.COMMIT_TO_FIX_FLAG
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('FND'
, 'YES_NO'
, DEF.COMMIT_TO_FIX_FLAG)
, 1
, 80) COMMIT_TO_FIX_MEANING
, NULL RESOLUTION_CODE /* OBSOLETED 11.5.3 */
, NULL RESOLUTION_MEANING /* OBSOLETED 11.5.3 */
, NULL UI_DEFECT_FLAG /* OBSOLETED 11.5.4+ */
, NULL UI_DEFECT_MEANING /* OBSOLETED 11.5.4+ */
, NULL FUNC_DEFECT_FLAG /* OBSOLETED 11.5.4+ */
, NULL FUNC_DEFECT_MEANING /* OBSOLETED 11.5.4+ */
, DEF.PLATFORM_SPECIFIC_FLAG
, SUBSTR(CSS_DEF_UTIL_PVT.GET_LOOKUP_MEANING('FND'
, 'YES_NO'
, DEF.PLATFORM_SPECIFIC_FLAG)
, 1
, 80) PLATFORM_SPECIFIC_MEANING
, ESC.ESCALATION_LEVEL_CODE
, ESC.ESCALATION_LEVEL_MEANING
, ESC.ESCALATION_OWNER_ID
, ESC.ESCALATION_OWNER_TYPE_CODE ESCALATION_OWNER_RESOURCE_TYPE
, ESC.ESCALATION_OWNER_NAME
, DEF.ESTIMATED_COST
, DEF.ESTIMATED_COST_UOM
, SUBSTR(CSS_DEF_UTIL_PVT.GET_UOM_DESC(DEF.ESTIMATED_COST_UOM)
, 1
, 50) ESTIMATED_COST_UOM_DESC
, DEF.ACTUAL_COST
, DEF.ACTUAL_COST_UOM
, SUBSTR(CSS_DEF_UTIL_PVT.GET_UOM_DESC(DEF.ACTUAL_COST_UOM)
, 1
, 50) ACTUAL_COST_UOM_DESC
, DECODE(DEF.FIX_BY_LEVEL
, 1
, DEF.PRODUCT_INV_ITEM_ID
, 2
, DEF.COMPONENT_INV_ITEM_ID
, 3
, DEF.SUB_COMPONENT_INV_ITEM_ID) FIX_BY_INV_ITEM_ID
, DECODE(DEF.FIX_BY_LEVEL
, 1
, PROD.DESCRIPTION
, 2
, COMP.DESCRIPTION
, 3
, SCOM.DESCRIPTION) FIX_BY_DESCRIPTION
, DECODE(DEF.FIX_BY_LEVEL
, 1
, PROD.CONCATENATED_SEGMENTS
, 2
, COMP.CONCATENATED_SEGMENTS
, 3
, SCOM.CONCATENATED_SEGMENTS) FIX_BY_CONC_SEGMENTS
, DEF.FIX_BY_REVISION
, DECODE(DEF.FIX_BY_REVISION
, NULL
, DEF.FIX_BY_REVISION_DESC
, FIXB_REV.REVISION_DESCRIPTION) FIX_BY_REVISION_DESC
, DECODE(DEF.FIXED_IN_LEVEL
, 1
, DEF.PRODUCT_INV_ITEM_ID
, 2
, DEF.COMPONENT_INV_ITEM_ID
, 3
, DEF.SUB_COMPONENT_INV_ITEM_ID) FIXED_IN_INV_ITEM_ID
, DECODE(DEF.FIXED_IN_LEVEL
, 1
, PROD.DESCRIPTION
, 2
, COMP.DESCRIPTION
, 3
, SCOM.DESCRIPTION) FIXED_IN_DESCRIPTION
, DECODE(DEF.FIXED_IN_LEVEL
, 1
, PROD.CONCATENATED_SEGMENTS
, 2
, COMP.CONCATENATED_SEGMENTS
, 3
, SCOM.CONCATENATED_SEGMENTS) FIXED_IN_CONC_SEGMENTS
, DEF.FIXED_IN_REVISION
, DECODE(DEF.FIXED_IN_REVISION
, NULL
, DEF.FIXED_IN_REVISION_DESC
, FIXI_REV.REVISION_DESCRIPTION) FIXED_IN_REVISION_DESC
, DEF.FILER_ID
, NULL FILER_RESOURCE_TYPE /* OBSOLETED 11.5.4+ */
, DEF.FILED_DATE
, DEF.UPDATED_DATE
, DEF.ESTIMATED_RESOLUTION_DATE
, DEF.RESOLVED_DATE
, DEF.ESTIMATED_CLOSURE_DATE
, DEF.CLOSED_DATE
, TO_NUMBER(NULL) SYS_RESPONSE_TIME /* OBSOLETED 11.5.3 */
, NULL SYS_RESPONSE_UOM /* OBSOLETED 11.5.3 */
, TO_NUMBER(NULL) SYS_RESOLUTION_TIME /* OBSOLETED 11.5.4+ */
, NULL SYS_RESOLUTION_UOM /* OBSOLETED 11.5.4+ */
, NULL SYS_RESOLUTION_UOM_DESC /* OBSOLETED 11.5.4+ */
, DEF.SYSTEM_RESOLUTION_DATE
, TO_NUMBER(NULL) SYS_CLOSURE_TIME /* OBSOLETED 11.5.4+ */
, NULL SYS_CLOSURE_UOM /* OBSOLETED 11.5.4+ */
, NULL SYS_CLOSURE_UOM_DESC /* OBSOLETED 11.5.4+ */
, DEF.SYSTEM_CLOSURE_DATE
, TO_NUMBER(NULL) DEV_OWNER_ID /* OBSOLETED 11.5.4+ */
, NULL DEV_OWNER_RESOURCE_TYPE /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) DEV_TERRITORY_ID /* OBSOLETED 11.5.4+ */
, NULL DEV_TERRITORY_NAME /* OBSOLETED 11.5.4+ */
, DEF.PHASE_OWNER_ID
, DEF.PHASE_OWNER_RESOURCE_TYPE
, TO_NUMBER(NULL) LAST_UPDATED_BY_RES_ID /* OBSOLETED 11.5.4+ */
, NULL LAST_UPDATED_BY_RESOURCE_TYPE /* OBSOLETED 11.5.4+ */
, DEF.UPDATED_BY_ID
, TO_NUMBER(NULL) TEMPLATE_ID /* OBSOLETED 11.5.3 */
, NULL CONTRIBUTED_TO_KB_FLAG /* OBSOLETED 11.5.4+ */
, NULL CONTRIBUTED_TO_KB_MEANING /* OBSOLETED 11.5.4+ */
, DEF.DELETED_FLAG
, DEF.CREATED_BY
, DEF.CREATION_DATE
, DEF.LAST_UPDATED_BY
, DEF.LAST_UPDATE_DATE
, DEF.LAST_UPDATE_LOGIN
, DEF.ATTRIBUTE1
, DEF.ATTRIBUTE2
, DEF.ATTRIBUTE3
, DEF.ATTRIBUTE4
, DEF.ATTRIBUTE5
, DEF.ATTRIBUTE6
, DEF.ATTRIBUTE7
, DEF.ATTRIBUTE8
, DEF.ATTRIBUTE9
, DEF.ATTRIBUTE10
, DEF.ATTRIBUTE11
, DEF.ATTRIBUTE12
, DEF.ATTRIBUTE13
, DEF.ATTRIBUTE14
, DEF.ATTRIBUTE15
, DEF.CONTEXT
, TO_NUMBER(NULL) PRODUCT_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL PRODUCT_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) PRODUCT_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL PRODUCT_ITEM_VERSION /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) COMPONENT_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL COMPONENT_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) COMPONENT_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL COMPONENT_ITEM_VERSION /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) SUB_COMPONENT_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL SUB_COMPONENT_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) SUB_COMP_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL SUB_COMP_ITEM_VERSION /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) EARLIEST_PROD_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL EARLIEST_PROD_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) EARLIEST_PROD_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL EARLIEST_PROD_ITEM_VERSION /* OBSOLETED 11.5.4+ */
, NULL PLANNED_BY_ITEM_TYPE /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) PLANNED_BY_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL PLANNED_BY_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) PLANNED_BY_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL PLANNED_BY_ITEM_VERSION /* OBSOLETED 11.5.4+ */
, NULL FIXED_IN_ITEM_TYPE /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) FIXED_IN_ITEM_ID /* OBSOLETED 11.5.4+ */
, NULL FIXED_IN_ITEM_NAME /* OBSOLETED 11.5.4+ */
, TO_NUMBER(NULL) FIXED_IN_ITEM_VERSION_ID /* OBSOLETED 11.5.4+ */
, NULL FIXED_IN_ITEM_VERSION /* OBSOLETED 11.5.4+ */
FROM CSS_DEF_DEFECTS_VL DEF
, JTF_TERR_V TER
, CSS_DEF_PROB_TYPE_PHASES PTP
, CSS_DEF_PHASES_VL PHA
, CSS_DEF_PROB_TYPES_VL PTY
, CSS_DEF_PROB_CATEGORIES_VL PCA
, CSS_DEF_SEVERITIES_VL SEV
, CSS_DEF_PRIORITIES_VL PRI
, CSS_DEF_ESCALATED_DEFECTS_V ESC
, CSS_DEF_STATUSES_VL STA
, MTL_SYSTEM_ITEMS_VL PROD
, MTL_SYSTEM_ITEMS_VL COMP
, MTL_SYSTEM_ITEMS_VL SCOM
, CSS_DEF_ITEM_REVISIONS_V PROD_REV
, CSS_DEF_ITEM_REVISIONS_V FFI_REV
, CSS_DEF_ITEM_REVISIONS_V COMP_REV
, CSS_DEF_ITEM_REVISIONS_V SCOM_REV
, CSS_DEF_ITEM_REVISIONS_V FIXI_REV
, CSS_DEF_ITEM_REVISIONS_V FIXB_REV
WHERE DEF.TERRITORY_ID = TER.TERR_ID
AND PTP.PHASE_ID = DEF.PHASE_ID
AND PTP.PROBLEM_TYPE_ID = DEF.PROBLEM_TYPE_ID
AND DEF.PHASE_ID = PHA.PHASE_ID
AND DEF.PROBLEM_TYPE_ID = PTY.PROBLEM_TYPE_ID
AND PTY.PROBLEM_CATEGORY_ID = PCA.PROBLEM_CATEGORY_ID
AND DEF.SEVERITY_ID = SEV.SEVERITY_ID
AND DEF.PRIORITY_ID = PRI.PRIORITY_ID(+)
AND DEF.DEFECT_ID = ESC.DEFECT_ID(+)
AND DEF.STATUS_ID = STA.STATUS_ID(+)
AND DEF.PRODUCT_INV_ITEM_ID = PROD.INVENTORY_ITEM_ID
AND PROD.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL)
AND DEF.COMPONENT_INV_ITEM_ID = COMP.INVENTORY_ITEM_ID(+)
AND (DEF.COMPONENT_INV_ITEM_ID IS NULL OR COMP.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL))
AND DEF.SUB_COMPONENT_INV_ITEM_ID = SCOM.INVENTORY_ITEM_ID(+)
AND (DEF.SUB_COMPONENT_INV_ITEM_ID IS NULL OR SCOM.ORGANIZATION_ID = (SELECT CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID
FROM DUAL))
AND DEF.PRODUCT_INV_ITEM_ID = PROD_REV.INVENTORY_ITEM_ID(+)
AND DEF.PRODUCT_REVISION = PROD_REV.REVISION(+)
AND DEF.PRODUCT_INV_ITEM_ID = FFI_REV.INVENTORY_ITEM_ID(+)
AND DEF.FIRST_FOUND_IN_REVISION = FFI_REV.REVISION(+)
AND DEF.COMPONENT_INV_ITEM_ID = COMP_REV.INVENTORY_ITEM_ID(+)
AND DEF.COMPONENT_REVISION = COMP_REV.REVISION(+)
AND DEF.SUB_COMPONENT_INV_ITEM_ID = SCOM_REV.INVENTORY_ITEM_ID(+)
AND DEF.SUB_COMPONENT_REVISION = SCOM_REV.REVISION(+)
AND DECODE(DEF.FIXED_IN_LEVEL
, 1
, DEF.PRODUCT_INV_ITEM_ID
, 2
, DEF.COMPONENT_INV_ITEM_ID
, 3
, DEF.SUB_COMPONENT_INV_ITEM_ID) = FIXI_REV.INVENTORY_ITEM_ID(+)
AND DEF.FIXED_IN_REVISION = FIXI_REV.REVISION(+)
AND DECODE(DEF.FIX_BY_LEVEL
, 1
, DEF.PRODUCT_INV_ITEM_ID
, 2
, DEF.COMPONENT_INV_ITEM_ID
, 3
, DEF.SUB_COMPONENT_INV_ITEM_ID) = FIXB_REV.INVENTORY_ITEM_ID(+)
AND DEF.FIX_BY_REVISION = FIXB_REV.REVISION(+)

Columns